Помощь с оптимизацией

Прошу помочь с mysql процедурой. Он долго грузится и вылетает таймаут соедининения.

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetNFReport`()
BEGIN
  SELECT
    l.id,
    f.name AS filial,
    r.Name AS typeName,
    s1.name AS segment,
    l.startDateTime,
    IF(l.statusId IN (13, 27), tempfinish.finishedDateTime, NULL) AS finishedDateTime,
    alltotal.totalMinute AS totalMinute,
    -- ROUND(CalcWorkTime(l.startDateTime, tempfinish.finishedDateTime)) AS totalMinute,
    temp28total.totalMinute + temp26total.totalMinute AS totalConfirmMinute,
    alltotal.totalMinute - IF(temp4total.KMTotalMinute IS NOT NULL, temp4total.KMTotalMinute, 0) - ROUND(CalcWorkTime(l.startDateTime, temp1.docWaitDateTime)) - IF(temp28total.totalMinute IS NOT NULL, temp28total.totalMinute, 0) - IF(temp26total.totalMinute IS NOT NULL, temp26total.totalMinute, 0) AS totalDKAMinute,
    -- ROUND(CalcWorkTime(l.startDateTime, temp26.signDateTime)) AS totalConfirmMinute,
    l.companyName,
    l.bin,
    s.name AS status,
    ka1.name AS ka1,
    ka2.name AS ka2,
    checker.name AS checker,
    monitoring.name AS monitoring,
    l.sokl_num + l.sokl_num1 + l.sokl_new_num + l.sokl_new_damu_num AS sokl_nums,
    l.sokl_num AS sokl_num,
    l.sokl_num1 AS sokl_num1,
    l.sokl_new_num,
    l.sokl_new_damu_num,
    l.dpsokl,
    l.dz_num + l.dz_num1 + l.dz_num2 + l.dz_num3 AS dz_nums,
    l.dzdp_num + l.dzdp_num1 + l.dzdp_num2 + l.dzdp_num3 AS dzdp_nums,
    l.dg_num,
    l.dgdp_num,
    Normatives.normativMaker AS normativMaker,
    Normatives.normativChecker AS normativChecker,
    l.comments,
    temp29.waitDateTime,
    -- ROUND(CalcWorkTime(temp29.waitDateTime, temp5.toWorkDateTime)) + temp12total.waitReWorkMinute AS waitMinute,
    temp29total.waitAllDateTime + IF(temp12total.waitReWorkMinute IS NOT NULL, temp12total.waitReWorkMinute, 0) AS waitMinute,
    temp5.toWorkDateTime,
    -- ROUND(CalcWorkTime(temp5.toWorkDateTime, temp7.workDateTime)) AS toWorkMinute,
    temp5total.toWorkTotalMinute AS toWorkMinute,
    temp7min.workDateTime,
    -- temp7.workDateTime,
    -- ROUND(CalcWorkTime(temp7.workDateTime, temp9.waitCheckerDateTime)) AS lastworkMinute,
    temp7total.makerWaitTotalMinute AS totalworkMinute,
    -- ROUND(CalcWorkTime(temp7min.workDateTime, temp9.waitCheckerDateTime)) AS totalworkMinute,

    temp10.acceptedDateTime,
    temp12.reWorkDateTime,
    IF(tempReworkKMCnt.reworkCnt IS NOT NULL, tempReworkKMCnt.reworkCnt, '0') AS reworkKMCnt,
    IF(tempReworkCnt.reworkCnt IS NOT NULL, tempReworkCnt.reworkCnt, '0') AS reworkCnt,

    -- ROUND(CalcWorkTime(temp10.acceptedDateTime, temp28.signedDateTime)) AS signedMinute,
    temp26total.TotalMinute AS signedMinute,
    temp28.signedDateTime,
    tempCommentWorker.commentWorker,
    tempCommentChecker.commentChecker,
    tempCommentDocs.commentDocs,
    temp1.docWaitDateTime,

    ROUND(CalcWorkTime(l.startDateTime, temp1.docWaitDateTime)) AS waitDocMinute,

    temp2.doccheckDateTime,
    temp2total.TotalMinute AS docCheckMinute,
    -- ROUND(CalcWorkTime(temp2.doccheckDateTime, temp3.scankDateTime)) AS docCheckMinute,
    temp3.scankDateTime,

    temp3total.TotalMinute AS docScanMinute,
    -- ROUND(CalcWorkTime(temp3.scankDateTime, temp29.waitDateTime)) AS docScanMinute,

    temp9.waitCheckerDateTime,
    -- ROUND(CalcWorkTime(temp9.waitCheckerDateTime, temp11.acceptCheckerDateTime)) AS waitCheckerMinute,
    temp9total.checkerWaitTotalMinute + IF(temp42total.totalMinute IS NOT NULL, temp42total.totalMinute, 0) AS waitCheckerMinute,
    temp11.acceptCheckerDateTime,
    ROUND(CalcWorkTime(temp11.acceptCheckerDateTime, temp10.acceptedDateTime)) AS workCheckerMinute,

    ROUND(CalcWorkTime(temp9.waitCheckerDateTime, temp10.acceptedDateTime)) AS lastCheckerMinute,
    temp11total.checkerTotalMinute AS totalCheckerMinute,
    temp11reWorktotal.checkerTotalMinute AS reworkCheckerTotalMinute,

    -- IF(ROUND(CalcWorkTime(temp7min.workDateTime, temp7min.finishedDateTime)) - Normatives.normativMaker < 0, 0, ROUND(CalcWorkTime(temp7min.workDateTime, temp7min.finishedDateTime)) - Normatives.normativMaker) AS diviationWorkMinute,
    -- IF(temp7reWorktotal.makerWaitTotalMinute - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums* 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num* 10) < 0, 0, temp7reWorktotal.makerWaitTotalMinute - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums* 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num* 10)) AS diviationReworkMinute,
    -- IF(temp7total.makerWaitTotalMinute - Normatives.normativMaker - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums* 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num* 10) < 0, 0, temp7total.makerWaitTotalMinute - Normatives.normativMaker - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums* 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num* 10)) AS diviationTotalMinute,
    ROUND(CalcWorkTime(temp7min.workDateTime, temp7min.finishedDateTime)) - Normatives.normativMaker AS diviationWorkMinute,
    temp7reWorktotal.makerWaitTotalMinute - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums * 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num * 10) AS diviationReworkMinute,
    temp7total.makerWaitTotalMinute - Normatives.normativMaker - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums * 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num * 10) AS diviationTotalMinute,

    -- IF(ROUND(CalcWorkTime(temp11min.createDateTime, temp11min.finishedDateTime)) - Normatives.normativChecker < 0, 0, ROUND(CalcWorkTime(temp11min.createDateTime, temp11min.finishedDateTime)) - Normatives.normativChecker)  AS diviationCheckerWorkMinute,
    -- IF(temp11reWorktotal.checkerTotalMinute - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums* 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num* 10)<0,0,temp11reWorktotal.checkerTotalMinute - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums* 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num* 10)) AS diviationCheckerReworkMinute,
    -- IF(temp11total.checkerTotalMinute - Normatives.normativChecker - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums* 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num* 10) < 0,0,temp11total.checkerTotalMinute - Normatives.normativChecker - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums* 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num* 10)) AS diviationCheckerTotalMinute,
    -- 0 AS diviationTotalMinute,

    ROUND(CalcWorkTime(temp11min.createDateTime, temp11min.finishedDateTime)) - Normatives.normativChecker AS diviationCheckerWorkMinute,
    temp11reWorktotal.checkerTotalMinute - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums * 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num * 10) AS diviationCheckerReworkMinute,
    temp11total.checkerTotalMinute - Normatives.normativChecker - (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums * 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num * 10) AS diviationCheckerTotalMinute,

    temp4total.KMTotalMinute AS reworkKMTotalMinute,
    temp8total.Ka1ReworkTotalMinute AS reworkKa1TotalMinute,
    temp7reWorktotal.makerWaitTotalMinute AS reworkMakerTotalMinute,

    0 AS waitKa1ReworkMinute,
    temp12total.waitReWorkMinute AS waitMakerReworkMinute,
    temp42total.totalMinute AS waitChekerReworkMinute,
    temp8total.cnt AS ka1ReworkCnt,

    reWorkCnt.sokl_rework_nums,
    reWorkCnt.dp_rework_sokl,
    reWorkCnt.dz_rework_nums,
    reWorkCnt.dzdp_rework_nums,
    reWorkCnt.dg_rework_num,
    reWorkCnt.dgdp_rework_num,

    reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums * 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num * 10 AS normativReworkMaker,
    (reWorkCnt.sokl_rework_nums * 15 + reWorkCnt.dp_rework_sokl * 15 + reWorkCnt.dz_rework_nums * 15 + reWorkCnt.dzdp_rework_nums * 15 + reWorkCnt.dg_rework_num * 10 + reWorkCnt.dgdp_rework_num * 10) AS normativReworkChecker,
    30 AS normativDecision,
    IF(temp5total.toWorkTotalMinute - 30 < 0, 0, temp5total.toWorkTotalMinute - 30) AS diviationDecisionMinute,
    temp40.waitDateTime AS waitCheckListDateTime,
    temp40.TotalMinute AS waitCheckListMinute,
    temp10total.TotalMinute AS downloadKodMinute,
    temp10total.downloanDateTime AS downloadKodDateTime
  -- 0 AS normativReworkMaker

  FROM loanrequest l
    INNER JOIN segments s1
      ON s1.id = l.segmentId
    INNER JOIN filials f
      ON l.filialId = f.id
    INNER JOIN statuses s
      ON l.statusId = s.id

    INNER JOIN requesttype r
      ON r.Id = l.typeId

    LEFT JOIN (SELECT
        lu11.finishedDateTime,
        lu11.loanId
      FROM loanusers lu11
      WHERE lu11.id = (SELECT
          lu2.id
        FROM loanusers lu2
        WHERE lu2.loanId = lu11.loanId
        AND lu2.isFinished = 1
        ORDER BY lu2.id DESC LIMIT 1)) AS tempfinish
      ON tempfinish.loanId = l.id

    LEFT JOIN users ka1
      ON ka1.id = l.ka1Id
    LEFT JOIN users ka2
      ON ka2.id = l.ka2Id
    LEFT JOIN users checker
      ON checker.id = l.checkerId
    LEFT JOIN users monitoring
      ON monitoring.id = l.monitoringId

    LEFT JOIN (SELECT
        lu1.loanId,
        MIN(lu1.createDateTime) AS waitDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 29
      GROUP BY lu1.loanId) AS temp29
      ON temp29.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        -- MAX(lu1.createDateTime) AS waitDateTime
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) waitAllDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 29
      GROUP BY lu1.loanId) AS temp29total
      ON temp29total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) waitReWorkMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 12
      GROUP BY lu1.loanId) AS temp12total
      ON temp12total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) toWorkTotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 5
      GROUP BY lu1.loanId) AS temp5total
      ON temp5total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MIN(lu1.createDateTime) AS toWorkDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 5
      GROUP BY lu1.loanId) AS temp5
      ON temp5.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MIN(lu1.createDateTime) AS workDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 7
      GROUP BY lu1.loanId) AS temp7
      ON temp7.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MIN(lu1.createDateTime) AS workDateTime,
        MIN(lu1.finishedDateTime) AS finishedDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 7
      GROUP BY lu1.loanId) AS temp7min
      ON temp7min.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) makerWaitTotalMinute
      -- SUM(ROUND(CalcWorkTimeWithDayFinish(lu1.createDateTime, lu1.finishedDateTime, lu1.id ))) AS makerWaitTotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 7
      GROUP BY lu1.loanId) AS temp7total
      ON temp7total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) makerWaitTotalMinute
      -- SUM(ROUND(CalcWorkTimeWithDayFinish(lu1.createDateTime, lu1.finishedDateTime, lu1.id ))) AS makerWaitTotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 7
      AND lu1.id NOT IN (SELECT
          MIN(l1.id)
        FROM loanusers l1
        WHERE l1.statusId = 7
        AND l1.loanId = lu1.loanId
        GROUP BY l1.loanId)
      GROUP BY lu1.loanId) AS temp7reWorktotal
      ON temp7reWorktotal.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MIN(lu1.createDateTime) AS createDateTime,
        MIN(lu1.finishedDateTime) AS finishedDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 11
      GROUP BY lu1.loanId) AS temp11min
      ON temp11min.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) checkerTotalMinute
      -- SUM(ROUND(CalcWorkTimeWithDayFinish(lu1.createDateTime, lu1.finishedDateTime, lu1.id ))) AS makerWaitTotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 11
      AND lu1.id NOT IN (SELECT
          MIN(l1.id)
        FROM loanusers l1
        WHERE l1.statusId = 11
        AND l1.loanId = lu1.loanId
        GROUP BY l1.loanId)
      GROUP BY lu1.loanId) AS temp11reWorktotal
      ON temp11reWorktotal.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) checkerTotalMinute
      -- SUM(ROUND(CalcWorkTimeWithDayFinish(lu1.createDateTime, lu1.finishedDateTime, lu1.id ))) AS makerWaitTotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 20
      GROUP BY lu1.loanId) AS temp20total
      ON temp20total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MAX(lu1.createDateTime) AS acceptedDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 10
      GROUP BY lu1.loanId) AS temp10
      ON temp10.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MAX(lu1.createDateTime) AS reWorkDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 12
      GROUP BY lu1.loanId) AS temp12
      ON temp12.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        COUNT(lu1.loanId) AS reworkCnt
      FROM loanusers lu1
      WHERE lu1.statusId = 4
      GROUP BY lu1.loanId) AS tempReworkKMCnt
      ON tempReworkKMCnt.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        COUNT(lu1.loanId) AS reworkCnt
      FROM loanusers lu1
      WHERE lu1.statusId = 12
      GROUP BY lu1.loanId) AS tempReworkCnt
      ON tempReworkCnt.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MAX(lu1.createDateTime) AS signedDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 28
      GROUP BY lu1.loanId) AS temp28
      ON temp28.loanId = l.id


    LEFT JOIN (SELECT
        lu1.loanId,
        MAX(lu1.finishedDateTime) AS downloanDateTime,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) totalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 28
      GROUP BY lu1.loanId) AS temp28total
      ON temp28total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MAX(lu1.finishedDateTime) AS downloanDateTime,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) totalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 10
      GROUP BY lu1.loanId) AS temp10total
      ON temp10total.loanId = l.id

    LEFT JOIN (SELECT
        t.loanId,
        GROUP_CONCAT(t.comment) AS commentWorker
      FROM (SELECT
          lu1.comment,
          lu1.loanId
        FROM loanusers lu1
          INNER JOIN users u1
            ON u1.id = lu1.userId
        WHERE u1.positionId = 9) AS t
      GROUP BY t.loanId) AS tempCommentWorker
      ON tempCommentWorker.loanId = l.id


    LEFT JOIN (SELECT
        t.loanId,
        GROUP_CONCAT(t.comment) AS commentChecker
      FROM (SELECT
          lu1.comment,
          lu1.loanId
        FROM loanusers lu1
          INNER JOIN users u1
            ON u1.id = lu1.userId
        WHERE u1.positionId = 10) AS t
      GROUP BY t.loanId) AS tempCommentChecker
      ON tempCommentChecker.loanId = l.id

    LEFT JOIN (SELECT
        t.loanId,
        GROUP_CONCAT(t.comment) AS commentDocs
      FROM (SELECT
          lu1.comment,
          lu1.loanId
        FROM loanusers lu1
          INNER JOIN users u1
            ON u1.id = lu1.userId
        WHERE u1.positionId = 3) AS t
      GROUP BY t.loanId) AS tempCommentDocs
      ON tempCommentDocs.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MAX(lu1.finishedDateTime) AS docWaitDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 1
      GROUP BY lu1.loanId) AS temp1
      ON temp1.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MIN(lu1.createDateTime) AS doccheckDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 2
      GROUP BY lu1.loanId) AS temp2
      ON temp2.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) TotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 2
      GROUP BY lu1.loanId) AS temp2total
      ON temp2total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MIN(lu1.createDateTime) AS scankDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 3
      GROUP BY lu1.loanId) AS temp3
      ON temp3.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) TotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 3
      GROUP BY lu1.loanId) AS temp3total
      ON temp3total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) checkerWaitTotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 9
      GROUP BY lu1.loanId) AS temp9total
      ON temp9total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MIN(lu1.createDateTime) AS waitCheckerDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 9
      GROUP BY lu1.loanId) AS temp9
      ON temp9.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MIN(lu1.createDateTime) AS waitDateTime,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) TotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 40
      GROUP BY lu1.loanId) AS temp40
      ON temp40.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        MIN(lu1.createDateTime) AS acceptCheckerDateTime
      FROM loanusers lu1
      WHERE lu1.statusId = 11
      GROUP BY lu1.loanId) AS temp11
      ON temp11.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) checkerTotalMinute
      -- SUM(ROUND(CalcWorkTimeWithDayFinish(lu1.createDateTime, lu1.finishedDateTime, lu1.id ))) AS checkerTotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 11
      GROUP BY lu1.loanId) AS temp11total
      ON temp11total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) KMTotalMinute
      -- SUM(ROUND(CalcWorkTimeWithDayFinish(lu1.createDateTime, lu1.finishedDateTime, lu1.id ))) AS checkerTotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 4
      GROUP BY lu1.loanId) AS temp4total
      ON temp4total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) Ka1ReworkTotalMinute,
        COUNT(lu1.id) AS cnt
      -- SUM(ROUND(CalcWorkTimeWithDayFinish(lu1.createDateTime, lu1.finishedDateTime, lu1.id ))) AS checkerTotalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 8
      GROUP BY lu1.loanId) AS temp8total
      ON temp8total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) totalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 26
      GROUP BY lu1.loanId) AS temp26total
      ON temp26total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) totalMinute
      FROM loanusers lu1
      WHERE lu1.statusId = 42
      GROUP BY lu1.loanId) AS temp42total
      ON temp42total.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) totalMinute
      FROM loanusers lu1
      GROUP BY lu1.loanId) AS alltotal
      ON alltotal.loanId = l.id

    LEFT JOIN (SELECT
        lu1.loanId,
        SUM(ROUND(CalcWorkTime(lu1.createDateTime, lu1.finishedDateTime))) totalMinuteTo26
      FROM loanusers lu1
      WHERE lu1.statusId NOT IN (26, 27, 28)
      GROUP BY lu1.loanId) AS alltotalto26
      ON alltotalto26.loanId = l.id

    LEFT JOIN (SELECT
        t.id,
        IF(t.sokl_rework_nums IS NOT NULL, t.sokl_rework_nums, 0) AS sokl_rework_nums,
        IF(t.dp_rework_sokl IS NOT NULL, t.dp_rework_sokl, 0) AS dp_rework_sokl,
        IF(t.dz_rework_nums IS NOT NULL, t.dz_rework_nums, 0) AS dz_rework_nums,
        IF(t.dzdp_rework_nums IS NOT NULL, t.dzdp_rework_nums, 0) AS dzdp_rework_nums,
        IF(t.dg_rework_num IS NOT NULL, t.dg_rework_num, 0) AS dg_rework_num,
        IF(t.dgdp_rework_num IS NOT NULL, t.dgdp_rework_num, 0) AS dgdp_rework_num
      FROM (SELECT
          l.id,
          (SELECT
              (COUNT(ld.id))
            FROM loandocs ld
            WHERE ld.subtype IN ('SoklNum1', 'SoklNum', 'SoklNewNum', 'SoklNewDamuNum')
            AND ld.loanId = l.id
            AND ld.isNeedRework = 1
            GROUP BY ld.loanId) AS sokl_rework_nums,
          (SELECT
              (COUNT(ld.id))
            FROM loandocs ld
            WHERE ld.subtype = 'Dpsokl'
            AND ld.loanId = l.id
            AND ld.isNeedRework = 1
            GROUP BY ld.loanId) AS dp_rework_sokl,
          (SELECT
              (COUNT(ld.id))
            FROM loandocs ld
            WHERE ld.subtype IN ('DzNum', 'DzNum1', 'DzNum2', 'DzNum3')
            AND ld.loanId = l.id
            AND ld.isNeedRework = 1
            GROUP BY ld.loanId) AS dz_rework_nums,
          (SELECT
              (COUNT(ld.id))
            FROM loandocs ld
            WHERE ld.subtype IN ('DzdpNum1', 'DzdpNum2', 'DzdpNum3', 'DzdpNum4')
            AND ld.loanId = l.id
            AND ld.isNeedRework = 1
            GROUP BY ld.loanId) AS dzdp_rework_nums,
          (SELECT
              (COUNT(ld.id))
            FROM loandocs ld
            WHERE ld.subtype IN ('DgNum')
            AND ld.loanId = l.id
            AND ld.isNeedRework = 1
            GROUP BY ld.loanId) AS dg_rework_num,
          (SELECT
              (COUNT(ld.id))
            FROM loandocs ld
            WHERE ld.subtype IN ('DgdpNum')
            AND ld.loanId = l.id
            AND ld.isNeedRework = 1
            GROUP BY ld.loanId) AS dgdp_rework_num
        FROM loanrequest l) AS t) AS reWorkCnt
      ON reWorkCnt.id = l.id

    LEFT JOIN (SELECT
        l.id AS LoanId,
        (l.sokl_num + l.sokl_num1 + l.sokl_new_damu_num) * 150 + l.sokl_new_num * 60 + dpsokl * 60 + (dzdp_num + l.dz_num) * 60 + (dzdp_num1 + l.dz_num1) * 90 + (dzdp_num2 + l.dz_num2) * 120 + (dzdp_num3 + l.dz_num3) * 180 + l.dg_num * 30
        + l.dgdp_num * 15 + l.dstermination_num * 10 + l.dsorder_num * 30 + l.dzfuture_num * 40 AS normativMaker,
        (l.sokl_num + l.sokl_num1 + l.sokl_new_damu_num) * 60 + l.sokl_new_num * 30 + dpsokl * 30 + (dzdp_num + l.dz_num) * 20 + (dzdp_num1 + l.dz_num1) * 40 + (dzdp_num2 + l.dz_num2) * 60 + (dzdp_num3 + l.dz_num3) * 80 + l.dg_num * 10
        + l.dgdp_num * 10 + l.dstermination_num * 5 + l.dsorder_num * 10 + l.dzfuture_num * 20 AS normativChecker
      FROM loanrequest l
      WHERE l.typeId IN (1, 2)) AS Normatives
      ON Normatives.LoanId = l.id

  WHERE l.typeId IN (1, 2)
  ORDER BY l.id;
END