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