-- 修复计划联动看板存储过程：增加租户隔离
-- 执行方式：CALL pr_Mes_LinkagePlan(租户ID)

DELIMITER //
DROP PROCEDURE IF EXISTS `pr_Mes_LinkagePlan`//
CREATE PROCEDURE `pr_Mes_LinkagePlan`(IN p_tenant_id BIGINT)
BEGIN
    DECLARE RowCnt INT DEFAULT 0;
    
    -- 只清空当前租户数据
    DELETE FROM LinkagePlan WHERE tenant_id = p_tenant_id OR tenant_id IS NULL;
    
    -- 第一部分：插入计划数据和实际数据
    INSERT INTO LinkagePlan(
        id, bill_no, custom_no, order_type, item_number, Descr, Descr1, ItemType, 
        qty, update_time, sys_capacity_date, bomstart, bomend, linestart, lineend, 
        productstart, productend, needtime, sys_material_date, cgneedtime, cgend, 
        blstart, blend, starttime, endtime, ipqcjystart, ipqcjyend, fqcjystart, 
        fqcjyend, rkstart, rkend, fystarttime, fyendtime, `type`, levelnum, isuse, tenant_id
    )
    SELECT 
        id, bill_no, custom_no, order_type, item_number, Descr, Descr1, ItemType,
        qty, update_time, sys_capacity_date, bomstart, bomend, linestart, lineend,
        productstart, productend, needtime, sys_material_date, cgneedtime, cgend,
        blstart, blend, starttime, endtime, ipqcjystart, ipqcjyend, fqcjystart,
        fqcjyend, rkstart, rkend, fystarttime, fyendtime, `type`, levelnum, isuse, p_tenant_id
    FROM (
        -- 计划数据查询
        SELECT 
            b.id,
            a.bill_no,
            a.custom_no,
            IF(a.order_type='1', '销售', '计划') as order_type,
            b.item_number,
            c.Descr,
            c.Descr1,
            c.ownerapplication as ItemType,
            b.qty,
            h.create_time as update_time,
            b.sys_capacity_date,
            h.create_time as bomstart,
            DATE_ADD(h.create_time, INTERVAL IFNULL(c.BOMDesignTime, 0) DAY) as bomend,
            DATE_ADD(h.create_time, INTERVAL IFNULL(c.BOMDesignTime, 0) DAY) as linestart,
            DATE_ADD(h.create_time, INTERVAL (IFNULL(c.BOMDesignTime, 0) + IFNULL(c.RoutingDesignTime, 0)) DAY) as lineend,
            DATE_ADD(h.create_time, INTERVAL (IFNULL(c.BOMDesignTime, 0) + IFNULL(c.RoutingDesignTime, 0)) DAY) as productstart,
            DATE_ADD(h.create_time, INTERVAL (IFNULL(c.BOMDesignTime, 0) + IFNULL(c.RoutingDesignTime, 0) + 
                CEILING(IFNULL(((b.qty-1)*d.maxruntime+d.runtime)/8, 0))) DAY) as productend,
            h.create_time as needtime,
            b.sys_material_date,
            h.create_time as cgneedtime,
            e.kitting_time as cgend,
            e.kitting_time as blstart,
            DATE_ADD(e.kitting_time, INTERVAL IFNULL(c.mfgmttr, 0) DAY) as blend,
            g.starttime,
            g.endtime,
            g.endtime as ipqcjystart,
            DATE_ADD(g.endtime, INTERVAL IFNULL(c.InsLT, 0) DAY) as ipqcjyend,
            g.endtime as fqcjystart,
            DATE_ADD(g.endtime, INTERVAL IFNULL(c.InsLT, 0) DAY) as fqcjyend,
            DATE_ADD(g.endtime, INTERVAL IFNULL(c.InsLT, 0) DAY) as rkstart,
            DATE_ADD(g.endtime, INTERVAL (IFNULL(c.InsLT, 0) + 1) DAY) as rkend,
            DATE_ADD(g.endtime, INTERVAL (IFNULL(c.InsLT, 0) + 1) DAY) as fystarttime,
            DATE_ADD(g.endtime, INTERVAL (IFNULL(c.InsLT, 0) + 2) DAY) as fyendtime,
            '计划' as `type`,
            2 as levelnum,
            0 as isuse
        FROM crm_seorder a
        LEFT JOIN crm_seorderentry b ON a.bill_no = b.bill_no AND b.tenant_id = a.tenant_id
        LEFT JOIN ItemMaster c ON b.item_number = c.ItemNum AND c.tenant_id = a.tenant_id
        LEFT JOIN (
            SELECT RoutingCode, SUM(runtime) as runtime, MAX(runtime) as maxruntime 
            FROM RoutingOpDetail 
            GROUP BY RoutingCode
        ) d ON c.ItemNum = d.RoutingCode
        LEFT JOIN (
            SELECT sentry_id, MAX(kitting_time) as kitting_time 
            FROM (
                SELECT 
                    ar.sentry_id,
                    bce.kitting_time,
                    ROW_NUMBER() OVER (PARTITION BY ar.sentry_id ORDER BY ar.create_time DESC) AS rn
                FROM b_examine_result ar
                JOIN b_bom_child_examine bce ON ar.id = bce.examine_id 
                WHERE ar.sorderid IS NOT NULL AND bce.erp_cls IN ('2','3')
                  AND ar.tenant_id = p_tenant_id
            ) ex 
            WHERE rn = 1  
            GROUP BY sentry_id
        ) e ON b.id = e.sentry_id
        LEFT JOIN mes_moentry f ON b.id = f.soentry_id AND f.tenant_id = a.tenant_id
        LEFT JOIN mes_morder h ON f.moentry_mono = h.morder_no AND h.parent_id IS NULL AND h.tenant_id = a.tenant_id
        LEFT JOIN (
            SELECT workords, MIN(plandate) as starttime, MAX(plandate) as endtime 
            FROM PeriodSequenceDet 
            GROUP BY workords
        ) g ON f.moentry_mono = g.workords
        WHERE a.IsDeleted = 0 AND h.create_time IS NOT NULL
          AND a.tenant_id = p_tenant_id
        
        UNION ALL
        
        -- 实际数据查询
        SELECT 
            b.id,
            a.bill_no,
            a.custom_no,
            IF(a.order_type='1', '销售', '计划') as order_type,
            b.item_number,
            c.Descr,
            c.Descr1,
            c.ownerapplication as ItemType,
            b.qty,
            h.create_time as update_time,
            b.sys_capacity_date,
            h.create_time as bomstart,
            nb.CreateTime as bomend,
            h.create_time as linestart,
            nb.CreateTime as lineend,
            d.starttime as productstart,
            d.endtime as productend,
            h.create_time as needtime,
            b.sys_material_date,
            e.cgneedtime as cgneedtime,
            e.kitting_time as cgend,
            nb.CreateTime as blstart,
            nd.blend as blend,
            g.starttime,
            g.endtime,
            e.FAPPLYTIME as ipqcjystart,
            e.FINSPESTARTDATE as ipqcjyend,
            g.endtime as fqcjystart,
            fqc.fqcDate as fqcjyend,
            i.starttime as rkstart,
            i.endtime as rkend,
            asn.starttime as fystarttime,
            asn.endtime as fyendtime,
            '实际' as `type`,
            1 as levelnum,
            0 as isuse
        FROM crm_seorder a
        LEFT JOIN crm_seorderentry b ON a.bill_no = b.bill_no AND b.tenant_id = a.tenant_id
        LEFT JOIN ItemMaster c ON b.item_number = c.ItemNum AND c.tenant_id = a.tenant_id
        LEFT JOIN (
            SELECT 
                pr.sentry_id,
                MAX(sh.createtime) as kitting_time,
                MIN(jq.jqhf) as cgneedtime,
                MIN(iqcjy.FINSPESTARTDATE) as FINSPESTARTDATE,
                MAX(iqcjy.FAPPLYTIME) as FAPPLYTIME 
            FROM srm_pr_main pr
            LEFT JOIN PurOrdDetail pod ON pr.pr_billno = pod.Req AND pod.tenant_id = pr.tenant_id
            LEFT JOIN vscm_cgshrk sh ON pod.PurOrd = sh.OrdNbr AND pod.Line = sh.OrdLine
            LEFT JOIN scm_jhjh_jq jq ON pod.PurOrd = jq.cgdd AND pod.Line = jq.ddhh
            LEFT JOIN (
                SELECT 
                    f.po_bill,
                    f.po_billline,
                    MIN(a.FINSPESTARTDATE) as FINSPESTARTDATE,
                    MAX(b.FAPPLYTIME) as FAPPLYTIME  
                FROM qms_qcp_inspbill a
                LEFT JOIN qms_qcp_inspecapplyn b ON a.lydjbh = b.FBILLNO
                LEFT JOIN qms_qcp_insappnentry c ON b.id = c.glid
                JOIN scm_shd cd ON c.shdh = cd.shddh
                LEFT JOIN scm_shdzb f ON f.shpc = c.FLOTNUMBER 
                    AND f.sh_material_code = c.FMATERIALCFG  
                    AND f.glid = cd.id 
                GROUP BY f.po_bill, f.po_billline
            ) iqcjy ON pod.PurOrd = iqcjy.po_bill AND pod.Line = iqcjy.po_billline  
            WHERE jq.flag = '1' AND pr.sentry_id IS NOT NULL
              AND pr.tenant_id = p_tenant_id
            GROUP BY pr.sentry_id
        ) e ON e.sentry_id = b.id
        LEFT JOIN mes_moentry f ON b.id = f.soentry_id AND f.tenant_id = a.tenant_id
        LEFT JOIN mes_morder h ON f.moentry_mono = h.morder_no AND h.parent_id IS NULL AND h.tenant_id = a.tenant_id
        LEFT JOIN (
            SELECT workords, MIN(plandate) as starttime, MAX(plandate) as endtime 
            FROM PeriodSequenceDet 
            GROUP BY workords
        ) d ON h.morder_no = d.workords
        LEFT JOIN (
            SELECT WorkOrd, MIN(CreateTime) as CreateTime 
            FROM NbrMaster 
            WHERE Type = 'SM' 
            GROUP BY WorkOrd
        ) nb ON h.morder_no = nb.WorkOrd
        LEFT JOIN (
            SELECT WorkOrd, MIN(UpdateTime) as blend 
            FROM NbrDetail 
            GROUP BY WorkOrd
        ) nd ON h.morder_no = nd.WorkOrd
        LEFT JOIN (
            SELECT workords, MIN(plandate) as starttime, MAX(plandate) as endtime 
            FROM PeriodSequenceDet 
            WHERE CompQty > 0 
            GROUP BY workords
        ) g ON f.moentry_mono = g.workords
        LEFT JOIN (
            SELECT WorkOrd, MIN(CreateTime) as starttime, MAX(CreateTime) as endtime 
            FROM InvTransHist 
            WHERE TransType = 'rct-wo' AND QtyChange > 0 
            GROUP BY WorkOrd
        ) i ON h.morder_no = i.WorkOrd
        LEFT JOIN (
            SELECT 
                ad.OrdNbr,
                ad.ContainerItem,
                ad.OrdLine,
                MIN(ad.CreateTime) as starttime,
                MAX(ad.UpdateTime) as endtime 
            FROM ASNBOLShipperDetail ad 
            INNER JOIN ASNBOLShipperMaster am ON ad.ASNBOLShipperRecID = am.RecID 
            WHERE am.shtype = 'SH' AND am.typed <> 'S' 
            GROUP BY ad.OrdNbr, ad.ContainerItem, ad.OrdLine
        ) asn ON b.bill_no = asn.OrdNbr AND b.item_number = asn.ContainerItem
        LEFT JOIN WorkOrdMaster wm ON h.morder_no = wm.WorkOrd AND wm.tenant_id = a.tenant_id
        LEFT JOIN (
            SELECT sczld, MAX(FINSPEENDDATE) as fqcDate 
            FROM qms_qcpp_inspbill 
            GROUP BY sczld
        ) fqc ON wm.Batch = fqc.sczld
        WHERE a.IsDeleted = 0 AND h.create_time IS NOT NULL
          AND a.tenant_id = p_tenant_id
    ) as viewall;
    
    -- 第二部分：插入偏差数据
    INSERT INTO LinkagePlan(
        id, bill_no, custom_no, order_type, item_number, Descr, Descr1, ItemType, 
        qty, update_time, sys_capacity_date, bomstart, bomend, linestart, lineend, 
        productstart, productend, needtime, sys_material_date, cgneedtime, cgend, 
        blstart, blend, starttime, endtime, ipqcjystart, ipqcjyend, fqcjystart, 
        fqcjyend, rkstart, rkend, fystarttime, fyendtime, `type`, levelnum, isuse, tenant_id
    )
    SELECT 
        a.id,
        a.bill_no,
        a.custom_no,
        a.order_type,
        b.item_number,
        a.Descr,
        a.Descr1,
        a.ItemType,
        b.qty,
        a.update_time,
        b.sys_capacity_date,
        DATEDIFF(b.bomstart, a.bomstart) as bomstart,
        DATEDIFF(b.bomend, a.bomend) as bomend,
        DATEDIFF(b.linestart, a.linestart) as linestart,
        DATEDIFF(b.lineend, a.lineend) as lineend,
        DATEDIFF(b.productstart, a.productstart) as productstart,
        DATEDIFF(b.productend, a.productend) as productend,
        DATEDIFF(b.needtime, a.needtime) as needtime,
        DATEDIFF(b.sys_material_date, a.sys_material_date) as sys_material_date,
        DATEDIFF(b.cgneedtime, a.cgneedtime) as cgneedtime,
        DATEDIFF(b.cgend, a.cgend) as cgend,
        DATEDIFF(b.blstart, a.blstart) as blstart,
        DATEDIFF(b.blend, a.blend) as blend,
        DATEDIFF(b.starttime, a.starttime) as starttime,
        DATEDIFF(b.endtime, a.endtime) as endtime,
        DATEDIFF(b.ipqcjystart, a.ipqcjystart) as ipqcjystart,
        DATEDIFF(b.ipqcjyend, a.ipqcjyend) as ipqcjyend,
        DATEDIFF(b.fqcjystart, a.fqcjystart) as fqcjystart,
        DATEDIFF(b.fqcjyend, a.fqcjyend) as fqcjyend,
        DATEDIFF(b.rkstart, a.rkstart) as rkstart,
        DATEDIFF(b.rkend, a.rkend) as rkend,
        DATEDIFF(b.fystarttime, a.fystarttime) as fystarttime,
        DATEDIFF(b.fyendtime, a.fyendtime) as fyendtime,
        '偏差' as `type`,
        0 as levelnum,
        0 as isuse,
        p_tenant_id
    FROM (SELECT * FROM LinkagePlan WHERE `type` = '计划' AND tenant_id = p_tenant_id) a
    LEFT JOIN (SELECT * FROM LinkagePlan WHERE `type` = '实际' AND tenant_id = p_tenant_id) b ON a.id = b.id;
    
    -- 第三部分：更新背景色标记
    UPDATE LinkagePlan a
    LEFT JOIN (
        SELECT 
            a.id,
            a.`type`,
            CONCAT(
                CASE WHEN a.`type` = '计划' THEN 
                    CASE WHEN a.bomstart IS NOT NULL AND b.bomstart IS NULL AND DATEDIFF(NOW(), a.bomstart) > 7 THEN 'bomstart:red;'
                         WHEN a.bomstart IS NOT NULL AND b.bomstart IS NULL AND DATEDIFF(NOW(), a.bomstart) > 0 THEN 'bomstart:yellow;'
                         ELSE '' END
                    WHEN a.`type` = '偏差' THEN 
                    CASE WHEN IFNULL(CAST(a.bomstart AS SIGNED), 0) > 7 THEN 'bomstart:red;'
                         WHEN IFNULL(CAST(a.bomstart AS SIGNED), 0) > 0 THEN 'bomstart:yellow;'
                         ELSE '' END
                    ELSE '' END,
                CASE WHEN a.`type` = '计划' THEN 
                    CASE WHEN a.bomend IS NOT NULL AND b.bomend IS NULL AND DATEDIFF(NOW(), a.bomend) > 7 THEN 'bomend:red;'
                         WHEN a.bomend IS NOT NULL AND b.bomend IS NULL AND DATEDIFF(NOW(), a.bomend) > 0 THEN 'bomend:yellow;'
                         ELSE '' END
                    WHEN a.`type` = '偏差' THEN 
                    CASE WHEN IFNULL(CAST(a.bomend AS SIGNED), 0) > 7 THEN 'bomend:red;'
                         WHEN IFNULL(CAST(a.bomend AS SIGNED), 0) > 0 THEN 'bomend:yellow;'
                         ELSE '' END
                    ELSE '' END,
                CASE WHEN a.`type` = '计划' THEN 
                    CASE WHEN a.starttime IS NOT NULL AND b.starttime IS NULL AND DATEDIFF(NOW(), a.starttime) > 7 THEN 'starttime:red;'
                         WHEN a.starttime IS NOT NULL AND b.starttime IS NULL AND DATEDIFF(NOW(), a.starttime) > 0 THEN 'starttime:yellow;'
                         ELSE '' END
                    WHEN a.`type` = '偏差' THEN 
                    CASE WHEN IFNULL(CAST(a.starttime AS SIGNED), 0) > 7 THEN 'starttime:red;'
                         WHEN IFNULL(CAST(a.starttime AS SIGNED), 0) > 0 THEN 'starttime:yellow;'
                         ELSE '' END
                    ELSE '' END,
                CASE WHEN a.`type` = '计划' THEN 
                    CASE WHEN a.endtime IS NOT NULL AND b.endtime IS NULL AND DATEDIFF(NOW(), a.endtime) > 7 THEN 'endtime:red;'
                         WHEN a.endtime IS NOT NULL AND b.endtime IS NULL AND DATEDIFF(NOW(), a.endtime) > 0 THEN 'endtime:yellow;'
                         ELSE '' END
                    WHEN a.`type` = '偏差' THEN 
                    CASE WHEN IFNULL(CAST(a.endtime AS SIGNED), 0) > 7 THEN 'endtime:red;'
                         WHEN IFNULL(CAST(a.endtime AS SIGNED), 0) > 0 THEN 'endtime:yellow;'
                         ELSE '' END
                    ELSE '' END
            ) AS background
        FROM LinkagePlan a 
        LEFT JOIN (SELECT * FROM LinkagePlan WHERE `type` = '实际' AND tenant_id = p_tenant_id) b ON a.id = b.id
        LEFT JOIN (SELECT * FROM LinkagePlan WHERE `type` = '偏差' AND tenant_id = p_tenant_id) c ON a.id = c.id
        WHERE a.tenant_id = p_tenant_id
    ) AS bg ON a.id = bg.id AND a.`type` = bg.`type`
    SET a.background = bg.background
    WHERE a.tenant_id = p_tenant_id;

END//
DELIMITER ;