sql

SELECT t20.OPERATOR_ID,t20.OPERATOR_CODE,t20.OPERATOR_NAME,t20.deptname,
            CAST(max(case WHEN t20._day_of_week=1 then t20.in_time else null end) AS CHAR) in_time1,
            CAST(max(case WHEN t20._day_of_week=1 then t20.out_time else null end) AS CHAR) out_time1,
            CAST(max(case WHEN t20._day_of_week=2 then t20.in_time else null end) AS CHAR) in_time2,
            CAST(max(case WHEN t20._day_of_week=2 then t20.out_time else null end) AS CHAR) out_time2,
            CAST(max(case WHEN t20._day_of_week=3 then t20.in_time else null end) AS CHAR) in_time3,
            CAST(max(case WHEN t20._day_of_week=3 then t20.out_time else null end) AS CHAR) out_time3,
            CAST(max(case WHEN t20._day_of_week=4 then t20.in_time else null end) AS CHAR) in_time4,
            CAST(max(case WHEN t20._day_of_week=4 then t20.out_time else null end) AS CHAR) out_time4,
            CAST(max(case WHEN t20._day_of_week=5 then t20.in_time else null end) AS CHAR) in_time5,
            CAST(max(case WHEN t20._day_of_week=5 then t20.out_time else null end) AS CHAR) out_time5,
            CAST(max(case WHEN t20._day_of_week=6 then t20.in_time else null end) AS CHAR) in_time6,
            CAST(max(case WHEN t20._day_of_week=6 then t20.out_time else null end) AS CHAR) out_time6,
            CAST(max(case WHEN t20._day_of_week=7 then t20.in_time else null end) AS CHAR) in_time7,
            CAST(max(case WHEN t20._day_of_week=7 then t20.out_time else null end) AS CHAR) out_time7

            from (
            SELECT t8.OPERATOR_ID,t8.OPERATOR_CODE,t8.OPERATOR_NAME,t9.deptname,t8._date,
            CASE WHEN DAYOFWEEK(t8._date)= 1 THEN 7 ELSE DAYOFWEEK(t8._date)- 1 END _day_of_week,
            IFNULL(IFNULL(IFNULL(t8.jb_in_time,t8.qj_in_time),t7.in_time),t10.in_time) in_time,
            IFNULL(IFNULL(IFNULL(t8.jb_out_time,t8.qj_out_time),t7.out_time),t10.out_time) out_time
            from (
            SELECT t1.OPERATOR_ID,t1.OPERATOR_CODE,t1.OPERATOR_NAME,t1.deptid,date(t0._date) _date,
            -- 上午请假调休
            (
              SELECT IF(count(*)=0,null,t5.leavetype)
FROM t_leave_record t5
              WHERE IF(t5.starttime < DATE(t0._date),DATE(t0._date),t5.starttime ) <= DATE_ADD(DATE(t0._date),INTERVAL 12 HOUR)
              and DATE(t0._date) BETWEEN DATE(t5.starttime ) and DATE(t5.endtime)
              and t5.operatorid=t1.OPERATOR_ID
              and t5.checkstatus = '4'
            ) qj_in_time,

            -- 下午请假调休
            (
              SELECT IF(count(*)=0,null,t5.leavetype)
FROM t_leave_record t5
              WHERE  IF(t5.starttime < DATE(t0._date),DATE_ADD(DATE(t0._date),INTERVAL 13 HOUR),t5.starttime ) >= DATE_ADD(DATE(t0._date),INTERVAL 13 HOUR)
              and DATE(t0._date) BETWEEN DATE(t5.starttime ) and DATE(t5.endtime)
and t5.operatorid=t1.OPERATOR_ID
and t5.checkstatus = '4'
            ) qj_out_time,


            -- 上午加班
            (
              SELECT IF(count(*)=0,null,'加班')
FROM t_work_record t5
              WHERE IF(t5.starttime < DATE(t0._date),DATE(t0._date),t5.starttime ) <= DATE_ADD(DATE(t0._date),INTERVAL 12 HOUR)
              and DATE(t0._date) BETWEEN DATE(t5.starttime ) and DATE(t5.endtime)
              and t5.operatorid=t1.OPERATOR_ID
              and t5.checkstatus = '4'
            ) jb_in_time,

            -- 下午加班--
            (
              SELECT IF(count(*)=0,null,'加班')
FROM t_work_record t5
              WHERE  IF(t5.starttime < DATE(t0._date),DATE_ADD(DATE(t0._date),INTERVAL 13 HOUR),t5.starttime ) >= DATE_ADD(DATE(t0._date),INTERVAL 13 HOUR)
              and DATE(t0._date) BETWEEN DATE(t5.starttime ) and DATE(t5.endtime)
and t5.operatorid=t1.OPERATOR_ID
and t5.checkstatus = '4'
            ) jb_out_time

            FROM
            (
SELECT :date _date
UNION ALL
SELECT DATE_ADD(:date,INTERVAL 1 DAY)
UNION ALL
SELECT DATE_ADD(:date,INTERVAL 2 DAY)
UNION ALL
SELECT DATE_ADD(:date,INTERVAL 3 DAY)
UNION ALL
SELECT DATE_ADD(:date,INTERVAL 4 DAY)
UNION ALL
SELECT DATE_ADD(:date,INTERVAL 5 DAY)
UNION ALL
SELECT DATE_ADD(:date,INTERVAL 6 DAY)
) t0,t_operator t1
ORDER BY t1.OPERATOR_ID,t0._date
            ) t8
            LEFT JOIN(
            -- 打卡记录
SELECT
t2.operator_code,
DATE(t2.dates) _date,
t2.in_time,
t2.out_time
FROM
t_calendar t2
            )t7 on t8.operator_code=t7.operator_code and t8._date=t7._date

            -- 补签记录
            LEFT JOIN(
            SELECT t3.operatorid, date(t3.filldate) _date,
                CASE WHEN t3.filltype = 1 THEN CONCAT('补签时间:',t3.updatedtime) ELSE NULL END in_time,
                CASE WHEN t3.filltype = 2 THEN CONCAT('补签时间:',t3.updatedtime) ELSE NULL END out_time
            FROM t_fill_calendar t3
            GROUP BY t3.operatorid,date(t3.filldate)
            ) t10 on t8.operator_id=t10.operatorid and t8._date=t10._date
            LEFT JOIN t_dept t9 on t8.deptid=t9.id
            where t8.deptid in(:deptIds)
            ) t20
            GROUP BY t20.OPERATOR_ID,t20.OPERATOR_CODE,t20.OPERATOR_NAME,t20.deptname