剩余年假时长计算

一、业务需求

计算用户的剩余年假时长

二、demo脚本

DROP TRIGGER IF EXISTS `ldcrm_insertsykq_byaddjobbill`;
delimiter ||
CREATE TRIGGER `ldcrm_insertsykq_byaddjobbill` BEFORE INSERT ON `ld_addjobbillscf` FOR EACH ROW 
BEGIN 

    SET @total_sy_tx=0;
    SET @total_sy_nj=0;
    
    SELECT
        c.total_sy_tx,
        CONCAT(IFNULL(c.total_nj_all,0)-IFNULL(c.totqal_nj_use,0),IFNULL(CONCAT('(',c.this_njstart,')'),'')) AS total_sh_nj
    INTO
        @total_sy_tx,
        @total_sy_nj
    FROM (
        SELECT
            a.id,
            IFNULL(b.add_hours,0)-IFNULL(c.min_hours,0) AS total_sy_tx,
            CASE
                WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)>CURDATE() THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)>CURDATE() THEN 40
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)>CURDATE() THEN 80
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)<=CURDATE() THEN 15*8
                ELSE 120
            END AS total_nj_all,
            (SELECT
                SUM(y.costtime)
            FROM ld_minusjobbill x
            LEFT JOIN ld_minusjobbilldetail y ON y.id=x.minusjobbillid
            WHERE x.deleted=0
                AND x.approvestatus='已批准'
                AND y.minusjobtype='年假'
                AND IFNULL(y.description,'') NOT LIKE '%兑换%'
                AND x.smownerid=a.id
                AND y.starttime>a.this_njstart) AS totqal_nj_use,
            a.this_njstart
        FROM (
            SELECT
                a.id,
                a.last_name,
                a.`status`,
                a.hire_date,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    WHEN DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)<=CURDATE() THEN
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date)-1 YEAR)
                END AS this_njstart,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL 2017-YEAR(a.hire_date) YEAR)
                END AS extra_njend
            FROM ld_users a
            WHERE a.id=(SELECT smownerid FROM ld_addjobbill WHERE addjobbillid=NEW.addjobbillid)
        ) a
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS add_hours
            FROM ld_addjobbill a
            LEFT JOIN ld_addjobbilldetail b ON b.id=a.addjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
                AND a.smownerid=(SELECT smownerid FROM ld_addjobbill WHERE addjobbillid=NEW.addjobbillid)
            GROUP BY a.smownerid
        ) b ON b.smownerid=a.id
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS min_hours
            FROM ld_minusjobbill a
            LEFT JOIN ld_minusjobbilldetail b ON b.id=a.minusjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
                AND b.minusjobtype='调休'
                AND a.smownerid=(SELECT smownerid FROM ld_addjobbill WHERE addjobbillid=NEW.addjobbillid)
            GROUP BY a.smownerid
        ) c ON c.smownerid=a.id
    ) c 
    WHERE c.id=(SELECT smownerid FROM ld_addjobbill WHERE addjobbillid=NEW.addjobbillid);

    SET NEW.sytx=@total_sy_tx;
    SET NEW.synj=@total_sy_nj;    
    
END ||
delimiter;

DROP TRIGGER IF EXISTS `ldcrm_insertsykq_byminusjobbill`;
delimiter ||
CREATE TRIGGER `ldcrm_insertsykq_byminusjobbill` BEFORE INSERT ON `ld_minusjobbillscf` FOR EACH ROW 
BEGIN 

    SET @total_sy_tx=0;
    SET @total_sy_nj=0;
    
    SELECT
        c.total_sy_tx,
        CONCAT(IFNULL(c.total_nj_all,0)-IFNULL(c.totqal_nj_use,0),IFNULL(CONCAT('(',c.this_njstart,')'),'')) AS total_sh_nj
    INTO
        @total_sy_tx,
        @total_sy_nj
    FROM (
        SELECT
            a.id,
            IFNULL(b.add_hours,0)-IFNULL(c.min_hours,0) AS total_sy_tx,
            CASE
                WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)>CURDATE() THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)>CURDATE() THEN 40
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)>CURDATE() THEN 80
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)<=CURDATE() THEN 15*8
                ELSE 120
            END AS total_nj_all,
            (SELECT
                SUM(y.costtime)
            FROM ld_minusjobbill x
            LEFT JOIN ld_minusjobbilldetail y ON y.id=x.minusjobbillid
            WHERE x.deleted=0
                AND x.approvestatus='已批准'
                AND y.minusjobtype='年假'
                AND IFNULL(y.description,'') NOT LIKE '%兑换%'
                AND x.smownerid=a.id
                AND y.starttime>a.this_njstart) AS totqal_nj_use,
            a.this_njstart
        FROM (
            SELECT
                a.id,
                a.last_name,
                a.`status`,
                a.hire_date,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    WHEN DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)<=CURDATE() THEN
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date)-1 YEAR)
                END AS this_njstart,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL 2017-YEAR(a.hire_date) YEAR)
                END AS extra_njend
            FROM ld_users a
            WHERE a.id=(SELECT smownerid FROM ld_minusjobbill WHERE minusjobbillid=NEW.minusjobbillid)
        ) a
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS add_hours
            FROM ld_addjobbill a
            LEFT JOIN ld_addjobbilldetail b ON b.id=a.addjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
                AND a.smownerid=(SELECT smownerid FROM ld_minusjobbill WHERE minusjobbillid=NEW.minusjobbillid)
            GROUP BY a.smownerid
        ) b ON b.smownerid=a.id
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS min_hours
            FROM ld_minusjobbill a
            LEFT JOIN ld_minusjobbilldetail b ON b.id=a.minusjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
                AND b.minusjobtype='调休'
                AND a.smownerid=(SELECT smownerid FROM ld_minusjobbill WHERE minusjobbillid=NEW.minusjobbillid)
            GROUP BY a.smownerid
        ) c ON c.smownerid=a.id
    ) c 
    WHERE c.id=(SELECT smownerid FROM ld_minusjobbill WHERE minusjobbillid=NEW.minusjobbillid);

    SET NEW.sytx=@total_sy_tx;
    SET NEW.synj=@total_sy_nj;    
    
END ||
delimiter;

 

 

 

-- 根据用户计算其可用年假的存储过程
DROP PROCEDURE IF EXISTS `ldcrm_update_sykq_byuserid`;
delimiter ||
CREATE  PROCEDURE `ldcrm_update_sykq_byuserid`(
    tmp_id int(19)
)
BEGIN

    SET @disable_triggers='abcdefg';
    UPDATE ld_minusjobbill a
    LEFT JOIN ld_minusjobbillscf b ON b.minusjobbillid=a.minusjobbillid
    LEFT JOIN (
        SELECT
            a.id,
            IFNULL(b.add_hours,0)-IFNULL(c.min_hours,0) AS total_sy_tx,
            CASE
                WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)>CURDATE() THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)>CURDATE() THEN 40
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)>CURDATE() THEN 80
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)<=CURDATE() THEN 15*8
                ELSE 120
            END AS total_nj_all,
            (SELECT
                SUM(y.costtime)
            FROM ld_minusjobbill x
            LEFT JOIN ld_minusjobbilldetail y ON y.id=x.minusjobbillid
            WHERE x.deleted=0
                AND x.approvestatus='已批准'
                AND y.minusjobtype='年假'
                AND IFNULL(y.description,'') NOT LIKE '%兑换%'
                AND x.smownerid=a.id
                AND y.starttime>a.this_njstart) AS totqal_nj_use,
            a.this_njstart
        FROM (
            SELECT
                a.id,
                a.last_name,
                a.`status`,
                a.hire_date,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    WHEN DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)<=CURDATE() THEN
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date)-1 YEAR)
                END AS this_njstart,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL 2017-YEAR(a.hire_date) YEAR)
                END AS extra_njend
            FROM ld_users a
            WHERE a.id=tmp_id
        ) a
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS add_hours
            FROM ld_addjobbill a
            LEFT JOIN ld_addjobbilldetail b ON b.id=a.addjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
                AND a.smownerid=tmp_id
            GROUP BY a.smownerid
        ) b ON b.smownerid=a.id
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS min_hours
            FROM ld_minusjobbill a
            LEFT JOIN ld_minusjobbilldetail b ON b.id=a.minusjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
                AND b.minusjobtype='调休'
                AND a.smownerid=tmp_id
            GROUP BY a.smownerid
        ) c ON c.smownerid=a.id
    ) c ON c.id=a.smownerid
    SET 
        b.sytx=c.total_sy_tx,
        b.synj=CONCAT(IFNULL(c.total_nj_all,0)-IFNULL(c.totqal_nj_use,0),IFNULL(CONCAT('(',c.this_njstart,')'),''))
    WHERE a.smownerid=tmp_id;

    UPDATE ld_addjobbill a
    LEFT JOIN ld_addjobbillscf b ON b.addjobbillid=a.addjobbillid
    LEFT JOIN (
        SELECT
            a.id,
            IFNULL(b.add_hours,0)-IFNULL(c.min_hours,0) AS total_sy_tx,
            CASE
                WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)>CURDATE() THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)>CURDATE() THEN 40
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)>CURDATE() THEN 80
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)<=CURDATE() THEN 15*8
                ELSE 120
            END AS total_nj_all,
            (SELECT
                SUM(y.costtime)
            FROM ld_minusjobbill x
            LEFT JOIN ld_minusjobbilldetail y ON y.id=x.minusjobbillid
            WHERE x.deleted=0
                AND x.approvestatus='已批准'
                AND y.minusjobtype='年假'
                AND IFNULL(y.description,'') NOT LIKE '%兑换%'
                AND x.smownerid=a.id
                AND y.starttime>a.this_njstart) AS totqal_nj_use,
            a.this_njstart
        FROM (
            SELECT
                a.id,
                a.last_name,
                a.`status`,
                a.hire_date,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    WHEN DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)<=CURDATE() THEN
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date)-1 YEAR)
                END AS this_njstart,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL 2017-YEAR(a.hire_date) YEAR)
                END AS extra_njend
            FROM ld_users a
            WHERE a.id=tmp_id
        ) a
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS add_hours
            FROM ld_addjobbill a
            LEFT JOIN ld_addjobbilldetail b ON b.id=a.addjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
                AND a.smownerid=tmp_id
            GROUP BY a.smownerid
        ) b ON b.smownerid=a.id
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS min_hours
            FROM ld_minusjobbill a
            LEFT JOIN ld_minusjobbilldetail b ON b.id=a.minusjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
                AND b.minusjobtype='调休'
                AND a.smownerid=tmp_id
            GROUP BY a.smownerid
        ) c ON c.smownerid=a.id
    ) c ON c.id=a.smownerid
    SET 
        b.sytx=c.total_sy_tx,
        b.synj=concat(IFNULL(c.total_nj_all,0)-IFNULL(c.totqal_nj_use,0),IFNULL(CONCAT('(',c.this_njstart,')'),''))
    WHERE a.smownerid=tmp_id;
    SET @disable_triggers=NULL;
end ||
delimiter;

 

-- 重算所有历史数据
DROP PROCEDURE IF EXISTS `ldcrm_update_sykq_all`;
delimiter ||
CREATE  PROCEDURE `ldcrm_update_sykq_all`(
)
BEGIN
    
    SET @disable_triggers='abcdefg';
    UPDATE ld_minusjobbill a
    LEFT JOIN ld_minusjobbillscf b ON b.minusjobbillid=a.minusjobbillid
    LEFT JOIN (
        SELECT
            a.id,
            IFNULL(b.add_hours,0)-IFNULL(c.min_hours,0) AS total_sy_tx,
            CASE
                WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)>CURDATE() THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)>CURDATE() THEN 40
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)>CURDATE() THEN 80
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)<=CURDATE() THEN 15*8
                ELSE 120
            END AS total_nj_all,
            (SELECT
                SUM(y.costtime)
            FROM ld_minusjobbill x
            LEFT JOIN ld_minusjobbilldetail y ON y.id=x.minusjobbillid
            WHERE x.deleted=0
                AND x.approvestatus='已批准'
                AND y.minusjobtype='年假'
                AND IFNULL(y.description,'') NOT LIKE '%兑换%'
                AND x.smownerid=a.id
                AND y.starttime>a.this_njstart) AS totqal_nj_use,
            a.this_njstart
        FROM (
            SELECT
                a.id,
                a.last_name,
                a.`status`,
                a.hire_date,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    WHEN DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)<=CURDATE() THEN
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date)-1 YEAR)
                END AS this_njstart,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL 2017-YEAR(a.hire_date) YEAR)
                END AS extra_njend
            FROM ld_users a
        ) a
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS add_hours
            FROM ld_addjobbill a
            LEFT JOIN ld_addjobbilldetail b ON b.id=a.addjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
            GROUP BY a.smownerid
        ) b ON b.smownerid=a.id
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS min_hours
            FROM ld_minusjobbill a
            LEFT JOIN ld_minusjobbilldetail b ON b.id=a.minusjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
                AND b.minusjobtype='调休'
            GROUP BY a.smownerid
        ) c ON c.smownerid=a.id
    ) c ON c.id=a.smownerid
    SET 
        b.sytx=c.total_sy_tx,
        b.synj=CONCAT(IFNULL(c.total_nj_all,0)-IFNULL(c.totqal_nj_use,0),IFNULL(CONCAT('(',c.this_njstart,')'),''));

    UPDATE ld_addjobbill a
    LEFT JOIN ld_addjobbillscf b ON b.addjobbillid=a.addjobbillid
    LEFT JOIN (
        SELECT
            a.id,
            IFNULL(b.add_hours,0)-IFNULL(c.min_hours,0) AS total_sy_tx,
            CASE
                WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)>CURDATE() THEN 0
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 1 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)>CURDATE() THEN 40
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 10 YEAR)<=CURDATE() AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)>CURDATE() THEN 80
                WHEN a.hire_date>'1990-01-01' AND DATE_ADD(a.hire_date,INTERVAL 20 YEAR)<=CURDATE() THEN 15*8
                ELSE 120
            END AS total_nj_all,
            (SELECT
                SUM(y.costtime)
            FROM ld_minusjobbill x
            LEFT JOIN ld_minusjobbilldetail y ON y.id=x.minusjobbillid
            WHERE x.deleted=0
                AND x.approvestatus='已批准'
                AND y.minusjobtype='年假'
                AND IFNULL(y.description,'') NOT LIKE '%兑换%'
                AND x.smownerid=a.id
                AND y.starttime>a.this_njstart) AS totqal_nj_use,
            a.this_njstart
        FROM (
            SELECT
                a.id,
                a.last_name,
                a.`status`,
                a.hire_date,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    WHEN DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)<=CURDATE() THEN
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date) YEAR)
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL YEAR(CURDATE())-YEAR(a.hire_date)-1 YEAR)
                END AS this_njstart,
                CASE
                    WHEN LENGTH(IFNULL(a.hire_date,''))<1 OR a.hire_date='0000-00-00' THEN NULL
                    ELSE
                        DATE_ADD(a.hire_date,INTERVAL 2017-YEAR(a.hire_date) YEAR)
                END AS extra_njend
            FROM ld_users a
        ) a
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS add_hours
            FROM ld_addjobbill a
            LEFT JOIN ld_addjobbilldetail b ON b.id=a.addjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
            GROUP BY a.smownerid
        ) b ON b.smownerid=a.id
        LEFT JOIN (
            SELECT
                a.smownerid,
                SUM(b.costtime) AS min_hours
            FROM ld_minusjobbill a
            LEFT JOIN ld_minusjobbilldetail b ON b.id=a.minusjobbillid
            WHERE a.deleted=0
                AND a.approvestatus='已批准'
                AND b.minusjobtype='调休'
            GROUP BY a.smownerid
        ) c ON c.smownerid=a.id
    ) c ON c.id=a.smownerid
    SET 
        b.sytx=c.total_sy_tx,
        b.synj=concat(IFNULL(c.total_nj_all,0)-IFNULL(c.totqal_nj_use,0),IFNULL(CONCAT('(',c.this_njstart,')'),''));
    SET @disable_triggers=NULL;

END ||
delimiter;

CALL ldcrm_update_sykq_all();
DROP PROCEDURE IF EXISTS `ldcrm_update_sykq_all`;

剩余年假时长计算的触发器.sql