DELIMITER //
DROP PROCEDURE IF EXISTS `pr_Mes_LinkagePlan`//
CREATE PROCEDURE `pr_Mes_LinkagePlan`(IN Params VARCHAR(1000))
BEGIN
    DECLARE RowCnt INT DEFAULT 0;
    
    -- 清空表
    DELETE FROM LinkagePlan;
    
    -- 第一部分：插入计划数据和实际数据
    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
    )
    SELECT * 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
        LEFT JOIN ItemMaster c ON b.item_number = c.ItemNum
        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 
                    a.sentry_id,
                    b.kitting_time,
                    ROW_NUMBER() OVER (PARTITION BY a.sentry_id ORDER BY a.create_time DESC) AS rn
                FROM b_examine_result a
                JOIN b_bom_child_examine b ON a.id = b.examine_id 
                WHERE a.sorderid IS NOT NULL AND b.erp_cls IN ('2','3')
            ) 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
        LEFT JOIN mes_morder h ON f.moentry_mono = h.morder_no AND h.parent_id IS NULL
        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
        
        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
        LEFT JOIN ItemMaster c ON b.item_number = c.ItemNum
        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
            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 
            GROUP BY pr.sentry_id
        ) e ON e.sentry_id = b.id
        LEFT JOIN mes_moentry f ON b.id = f.soentry_id
        LEFT JOIN mes_morder h ON f.moentry_mono = h.morder_no AND h.parent_id IS NULL
        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
        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
    ) 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
    )
    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
    FROM (SELECT * FROM LinkagePlan WHERE `type` = '计划') a
    LEFT JOIN (SELECT * FROM LinkagePlan WHERE `type` = '实际') b ON a.id = b.id;
    
    -- 统计行数
    SELECT COUNT(*) INTO RowCnt FROM LinkagePlan WHERE isuse = 1;
    
    -- 第三部分：更新背景色标记
    UPDATE LinkagePlan a
    LEFT JOIN (
        SELECT 
            a.id,
            a.`type`,
            CONCAT(
                -- bomstart背景色
                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,
                -- bomend背景色
                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,
                -- linestart背景色
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.linestart IS NOT NULL AND b.linestart IS NULL AND DATEDIFF(NOW(), a.linestart) > 7 THEN 'linestart:red;'
                            WHEN a.linestart IS NOT NULL AND b.linestart IS NULL AND DATEDIFF(NOW(), a.linestart) > 0 THEN 'linestart:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.linestart AS SIGNED), 0) > 7 THEN 'linestart:red;'
                            WHEN IFNULL(CAST(a.linestart AS SIGNED), 0) > 0 THEN 'linestart:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                -- lineend背景色
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.lineend IS NOT NULL AND b.lineend IS NULL AND DATEDIFF(NOW(), a.lineend) > 7 THEN 'lineend:red;'
                            WHEN a.lineend IS NOT NULL AND b.lineend IS NULL AND DATEDIFF(NOW(), a.lineend) > 0 THEN 'lineend:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.lineend AS SIGNED), 0) > 7 THEN 'lineend:red;'
                            WHEN IFNULL(CAST(a.lineend AS SIGNED), 0) > 0 THEN 'lineend:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                -- productstart背景色
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.productstart IS NOT NULL AND b.productstart IS NULL AND DATEDIFF(NOW(), a.productstart) > 7 THEN 'productstart:red;'
                            WHEN a.productstart IS NOT NULL AND b.productstart IS NULL AND DATEDIFF(NOW(), a.productstart) > 0 THEN 'productstart:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.productstart AS SIGNED), 0) > 7 THEN 'productstart:red;'
                            WHEN IFNULL(CAST(a.productstart AS SIGNED), 0) > 0 THEN 'productstart:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                -- productend背景色
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.productend IS NOT NULL AND b.productend IS NULL AND DATEDIFF(NOW(), a.productend) > 7 THEN 'productend:red;'
                            WHEN a.productend IS NOT NULL AND b.productend IS NULL AND DATEDIFF(NOW(), a.productend) > 0 THEN 'productend:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.productend AS SIGNED), 0) > 7 THEN 'productend:red;'
                            WHEN IFNULL(CAST(a.productend AS SIGNED), 0) > 0 THEN 'productend:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                -- needtime, sys_material_date, cgneedtime, cgend背景色
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.needtime IS NOT NULL AND b.needtime IS NULL AND DATEDIFF(NOW(), a.needtime) > 7 THEN 'needtime:red;'
                            WHEN a.needtime IS NOT NULL AND b.needtime IS NULL AND DATEDIFF(NOW(), a.needtime) > 0 THEN 'needtime:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.needtime AS SIGNED), 0) > 7 THEN 'needtime:red;'
                            WHEN IFNULL(CAST(a.needtime AS SIGNED), 0) > 0 THEN 'needtime:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.sys_material_date IS NOT NULL AND b.sys_material_date IS NULL AND DATEDIFF(NOW(), a.sys_material_date) > 7 THEN 'sys_material_date:red;'
                            WHEN a.sys_material_date IS NOT NULL AND b.sys_material_date IS NULL AND DATEDIFF(NOW(), a.sys_material_date) > 0 THEN 'sys_material_date:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.sys_material_date AS SIGNED), 0) > 7 THEN 'sys_material_date:red;'
                            WHEN IFNULL(CAST(a.sys_material_date AS SIGNED), 0) > 0 THEN 'sys_material_date:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.cgneedtime IS NOT NULL AND b.cgneedtime IS NULL AND DATEDIFF(NOW(), a.cgneedtime) > 7 THEN 'cgneedtime:red;'
                            WHEN a.cgneedtime IS NOT NULL AND b.cgneedtime IS NULL AND DATEDIFF(NOW(), a.cgneedtime) > 0 THEN 'cgneedtime:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.cgneedtime AS SIGNED), 0) > 7 THEN 'cgneedtime:red;'
                            WHEN IFNULL(CAST(a.cgneedtime AS SIGNED), 0) > 0 THEN 'cgneedtime:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.cgend IS NOT NULL AND b.cgend IS NULL AND DATEDIFF(NOW(), a.cgend) > 7 THEN 'cgend:red;'
                            WHEN a.cgend IS NOT NULL AND b.cgend IS NULL AND DATEDIFF(NOW(), a.cgend) > 0 THEN 'cgend:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.cgend AS SIGNED), 0) > 7 THEN 'cgend:red;'
                            WHEN IFNULL(CAST(a.cgend AS SIGNED), 0) > 0 THEN 'cgend:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                -- blstart, starttime, endtime背景色
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.blstart IS NOT NULL AND b.blstart IS NULL AND DATEDIFF(NOW(), a.blstart) > 7 THEN 'blstart:red;'
                            WHEN a.blstart IS NOT NULL AND b.blstart IS NULL AND DATEDIFF(NOW(), a.blstart) > 0 THEN 'blstart:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.blstart AS SIGNED), 0) > 7 THEN 'blstart:red;'
                            WHEN IFNULL(CAST(a.blstart AS SIGNED), 0) > 0 THEN 'blstart: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,
                -- ipqcjystart, ipqcjyend, fqcjystart, fqcjyend背景色
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.ipqcjystart IS NOT NULL AND b.ipqcjystart IS NULL AND DATEDIFF(NOW(), a.ipqcjystart) > 7 THEN 'ipqcjystart:red;'
                            WHEN a.ipqcjystart IS NOT NULL AND b.ipqcjystart IS NULL AND DATEDIFF(NOW(), a.ipqcjystart) > 0 THEN 'ipqcjystart:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.ipqcjystart AS SIGNED), 0) > 7 THEN 'ipqcjystart:red;'
                            WHEN IFNULL(CAST(a.ipqcjystart AS SIGNED), 0) > 0 THEN 'ipqcjystart:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.ipqcjyend IS NOT NULL AND b.ipqcjyend IS NULL AND DATEDIFF(NOW(), a.ipqcjyend) > 7 THEN 'ipqcjyend:red;'
                            WHEN a.ipqcjyend IS NOT NULL AND b.ipqcjyend IS NULL AND DATEDIFF(NOW(), a.ipqcjyend) > 0 THEN 'ipqcjyend:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.ipqcjyend AS SIGNED), 0) > 7 THEN 'ipqcjyend:red;'
                            WHEN IFNULL(CAST(a.ipqcjyend AS SIGNED), 0) > 0 THEN 'ipqcjyend:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.fqcjystart IS NOT NULL AND b.fqcjystart IS NULL AND DATEDIFF(NOW(), a.fqcjystart) > 7 THEN 'fqcjystart:red;'
                            WHEN a.fqcjystart IS NOT NULL AND b.fqcjystart IS NULL AND DATEDIFF(NOW(), a.fqcjystart) > 0 THEN 'fqcjystart:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.fqcjystart AS SIGNED), 0) > 7 THEN 'fqcjystart:red;'
                            WHEN IFNULL(CAST(a.fqcjystart AS SIGNED), 0) > 0 THEN 'fqcjystart:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.fqcjyend IS NOT NULL AND b.fqcjyend IS NULL AND DATEDIFF(NOW(), a.fqcjyend) > 7 THEN 'fqcjyend:red;'
                            WHEN a.fqcjyend IS NOT NULL AND b.fqcjyend IS NULL AND DATEDIFF(NOW(), a.fqcjyend) > 0 THEN 'fqcjyend:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.fqcjyend AS SIGNED), 0) > 7 THEN 'fqcjyend:red;'
                            WHEN IFNULL(CAST(a.fqcjyend AS SIGNED), 0) > 0 THEN 'fqcjyend:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                -- rkstart, rkend, fystarttime, fyendtime背景色
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.rkstart IS NOT NULL AND b.rkstart IS NULL AND DATEDIFF(NOW(), a.rkstart) > 7 THEN 'rkstart:red;'
                            WHEN a.rkstart IS NOT NULL AND b.rkstart IS NULL AND DATEDIFF(NOW(), a.rkstart) > 0 THEN 'rkstart:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.rkstart AS SIGNED), 0) > 7 THEN 'rkstart:red;'
                            WHEN IFNULL(CAST(a.rkstart AS SIGNED), 0) > 0 THEN 'rkstart:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.rkend IS NOT NULL AND b.rkend IS NULL AND DATEDIFF(NOW(), a.rkend) > 7 THEN 'rkend:red;'
                            WHEN a.rkend IS NOT NULL AND b.rkend IS NULL AND DATEDIFF(NOW(), a.rkend) > 0 THEN 'rkend:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.rkend AS SIGNED), 0) > 7 THEN 'rkend:red;'
                            WHEN IFNULL(CAST(a.rkend AS SIGNED), 0) > 0 THEN 'rkend:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.fystarttime IS NOT NULL AND b.fystarttime IS NULL AND DATEDIFF(NOW(), a.fystarttime) > 7 THEN 'fystarttime:red;'
                            WHEN a.fystarttime IS NOT NULL AND b.fystarttime IS NULL AND DATEDIFF(NOW(), a.fystarttime) > 0 THEN 'fystarttime:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.fystarttime AS SIGNED), 0) > 7 THEN 'fystarttime:red;'
                            WHEN IFNULL(CAST(a.fystarttime AS SIGNED), 0) > 0 THEN 'fystarttime:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END,
                CASE
                    WHEN a.`type` = '计划' THEN 
                        CASE 
                            WHEN a.fyendtime IS NOT NULL AND b.fyendtime IS NULL AND DATEDIFF(NOW(), a.fyendtime) > 7 THEN 'fyendtime:red;'
                            WHEN a.fyendtime IS NOT NULL AND b.fyendtime IS NULL AND DATEDIFF(NOW(), a.fyendtime) > 0 THEN 'fyendtime:yellow;'
                            ELSE ''
                        END
                    WHEN a.`type` = '偏差' THEN 
                        CASE 
                            WHEN IFNULL(CAST(a.fyendtime AS SIGNED), 0) > 7 THEN 'fyendtime:red;'
                            WHEN IFNULL(CAST(a.fyendtime AS SIGNED), 0) > 0 THEN 'fyendtime:yellow;'
                            ELSE ''
                        END
                    ELSE ''
                END
            ) AS background
        FROM LinkagePlan a 
        LEFT JOIN (SELECT * FROM LinkagePlan WHERE `type` = '实际') b ON a.id = b.id
        LEFT JOIN (SELECT * FROM LinkagePlan WHERE `type` = '偏差') c ON a.id = c.id
    ) AS bg ON a.id = bg.id AND a.`type` = bg.`type`
    SET a.background = bg.background;

END//
DELIMITER ;