SELECT dep1.name AS x_dep1_name, dep2.name AS x_dep2_name, dep3.name AS x_dep3_name, emp.employee_number AS x_employee_number, emp.name AS x_name, detail.title AS x_attendance_date, – 考勤日期 CASE WHEN detail.title = any(calendar_line.date_info) THEN '是' ELSE '否' END AS x_sffdjr, – 是否法定假日 加了自定义字段，但是不管用（考勤日期 在表tmp中存在，且为法定、假期） COALESCE(daily_model.name, resource_calendar.name, '') AS x_banbie, – 班别 （有排班的取排班，无排班的取“协议工作制”） CASE WHEN detail.day_calendar_id IS NOT NULL THEN COALESCE(to_char(day_calendar.work_on_start,'hh24:mi:ss'),'') || ','|| COALESCE(to_char(day_calendar.work_off_start,'hh24:mi:ss'),'') ELSE COALESCE(res_partner.worktime_start_str, '') ||','|| COALESCE(res_partner.worktime_end_str,'') END AS x_work_time, – 上班时间（有排班取排班，没有排班取考勤点的上班时间） COALESCE(to_char(detail.clock_in_time,'hh24:mi:ss'), '') AS x_clock_in_time, – 签入时间 可能要看下格式 datetime 改成 时间 COALESCE(to_char(detail.clock_out_time,'hh24:mi:ss'), '') AS x_clock_out_time, – 签入时间 – 旷工时数 大于30分钟旷工，PS当天出勤小于7.5小时 CASE WHEN detail.day_calendar_id IS NOT NULL THEN – 有排班：打卡间隔小时数 >= 排班间隔小时数 CASE WHEN date_part('hour',detail.clock_out_time::timestamp - detail.clock_in_time::timestamp) >= date_part('hour',day_calendar.work_off_start::timestamp - day_calendar.work_on_start::timestamp) THEN 0 ELSE – 打卡间隔小时数 < 排班间隔小时数 – 如果 排班间隔小时数 - 打卡间隔数 + 分钟数/60 结果 >0.5小时 算旷工。 CASE WHEN CAST(date_part('hour',day_calendar.work_off_start::timestamp - day_calendar.work_on_start::timestamp)-date_part('hour',detail.clock_out_time::timestamp - detail.clock_in_time::timestamp) - date_part('minute',detail.clock_out_time::timestamp - detail.clock_in_time::timestamp)/60 as decimal(18, 1)) > 0.5 THEN CAST(date_part('hour',day_calendar.work_off_start::timestamp - day_calendar.work_on_start::timestamp)-date_part('hour',detail.clock_out_time::timestamp - detail.clock_in_time::timestamp) - date_part('minute',detail.clock_out_time::timestamp - detail.clock_in_time::timestamp)/60 as decimal(18, 1)) ELSE 0 END END ELSE – 没有排班，打卡间隔小时数 < 考勤点的间隔小时数 CASE WHEN date_part('hour',detail.clock_out_time::timestamp - detail.clock_in_time::timestamp) >= date_part('hour',to_date(res_partner.worktime_start_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp - to_date(res_partner.worktime_end_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp) THEN 0 ELSE CASE WHEN CAST(date_part('hour',to_date(res_partner.worktime_start_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp - to_date(res_partner.worktime_end_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp)-date_part('hour',detail.clock_out_time::timestamp - detail.clock_in_time::timestamp) - date_part('minute',detail.clock_out_time::timestamp - detail.clock_in_time::timestamp)/60 as decimal(18, 1))>0.5 THEN CAST(date_part('hour',to_date(res_partner.worktime_start_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp - to_date(res_partner.worktime_end_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp)-date_part('hour',detail.clock_out_time::timestamp - detail.clock_in_time::timestamp) - date_part('minute',detail.clock_out_time::timestamp - detail.clock_in_time::timestamp)/60 as decimal(18, 1)) ELSE 0 END END END AS x_kg_hours, – 迟到分钟数 迟到小于等于30分钟 CASE WHEN detail.day_calendar_id IS NOT NULL THEN CASE WHEN date_part('minute',detail.clock_in_time::timestamp - day_calendar.work_on_start::timestamp) > 0 AND date_part('hour',detail.clock_in_time::timestamp - day_calendar.work_on_start::timestamp) < 1 THEN date_part('minute',detail.clock_in_time::timestamp - day_calendar.work_on_start::timestamp) ELSE 0 END ELSE CASE WHEN date_part('minute',detail.clock_in_time::timestamp - to_date(res_partner.worktime_start_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp) > 0 AND date_part('hour',detail.clock_in_time::timestamp - to_date(res_partner.worktime_start_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp) < 1 THEN date_part('minute',detail.clock_in_time::timestamp - to_date(res_partner.worktime_start_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp) ELSE 0 END END AS x_late_hours, – 早退分钟数 迟到或早退小于等于30分钟 CASE WHEN detail.day_calendar_id IS NOT NULL THEN CASE WHEN date_part('minute',detail.clock_in_time::timestamp - day_calendar.work_off_start::timestamp) > 0 AND date_part('hour',detail.clock_in_time::timestamp - day_calendar.work_off_start::timestamp) < 1 THEN date_part('minute',detail.clock_in_time::timestamp - day_calendar.work_off_start::timestamp) ELSE 0 END ELSE CASE WHEN date_part('minute',to_date(res_partner.worktime_end_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp - detail.clock_out_time::timestamp) > 0 AND date_part('hour',to_date(res_partner.worktime_end_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp - detail.clock_out_time::timestamp) < 1 THEN date_part('minute',to_date(res_partner.worktime_end_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp - detail.clock_out_time::timestamp) ELSE 0 END END AS x_leave_early_hours, – 补卡次数 当天上下班补卡的次数，数值为0，1或2 COALESCE(buka.num, 0) AS x_buka_num, – 迟到/早退次数 当天迟到或早退次数，数值为0，1或2 CASE WHEN detail.day_calendar_id IS NOT NULL THEN CASE WHEN date_part('minute',detail.clock_in_time::timestamp - day_calendar.work_on_start::timestamp) > 0 AND date_part('hour',detail.clock_in_time::timestamp - day_calendar.work_on_start::timestamp) < 1 THEN CASE WHEN date_part('minute',detail.clock_in_time::timestamp - day_calendar.work_off_start::timestamp) > 0 AND date_part('hour',detail.clock_in_time::timestamp - day_calendar.work_off_start::timestamp) < 1 THEN 2 ELSE 1 END ELSE CASE WHEN date_part('minute',detail.clock_in_time::timestamp - day_calendar.work_off_start::timestamp) > 0 AND date_part('hour',detail.clock_in_time::timestamp - day_calendar.work_off_start::timestamp) < 1 THEN 1 ELSE 0 END END ELSE CASE WHEN date_part('minute',detail.clock_in_time::timestamp - to_date(res_partner.worktime_start_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp) > 0 AND date_part('hour',detail.clock_in_time::timestamp - to_date(res_partner.worktime_start_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp) < 1 THEN CASE WHEN date_part('minute',to_date(res_partner.worktime_end_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp - detail.clock_out_time::timestamp) > 0 AND date_part('hour',to_date(res_partner.worktime_end_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp - detail.clock_out_time::timestamp) < 1 THEN 2 ELSE 1 END ELSE CASE WHEN date_part('minute',to_date(res_partner.worktime_end_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp - detail.clock_out_time::timestamp) > 0 AND date_part('hour',to_date(res_partner.worktime_end_str,'%%Y-%%m-%%d %%H:%%M:%%S')::timestamp - detail.clock_out_time::timestamp) < 1 THEN 1 ELSE 0 END END END x_cdzt_times, – 缺勤 请假缺勤、出差 COALESCE(holiday.holiday_info, '') AS x_queqin, – 出勤 公出出勤 COALESCE(holiday2.holiday_info, '') AS x_chuqin FROM hr_attendance_details detail LEFT JOIN hr_department dep ON detail.department_id = dep.id LEFT JOIN hr_department dep1 ON dep1.id = dep.department_id_1 LEFT JOIN hr_department dep2 ON dep2.id = dep.department_id_2 LEFT JOIN hr_department dep3 ON dep3.id = dep.department_id_3 LEFT JOIN hr_employee emp ON detail.employee_id=emp.id LEFT JOIN day_calendar ON day_calendar.id=detail.day_calendar_id LEFT JOIN daily_model ON daily_model.id = day_calendar.daily_model_id LEFT JOIN resource_calendar ON resource_calendar.id = emp.working_hours – 协议工时制 LEFT JOIN hr_attendance_area area ON emp.attendance_area_id = area.id – 考勤区域 LEFT JOIN res_partner ON area.defaults_attendance_point = res_partner.id – 默认考勤点 LEFT JOIN ( SELECT employee_id,attendance_date,count(id) as num FROM hr_attendance_adjustment WHERE state='done' AND active=TRUE GROUP BY employee_id,attendance_date ) buka ON buka.employee_id = emp.id AND detail.title = buka.attendance_date – 补卡记录 – 工作日历：是否法定假期相关 LEFT JOIN ( SELECT work_calendar_id AS calendar_id, year, array_agg(day) as date_info FROM work_calendar_line WHERE legal=TRUE GROUP BY work_calendar_id, year ) calendar_line ON calendar_line.calendar_id=emp.work_calendar_id AND calendar_line.year=to_char(detail.title,'YYYY') – 休假明细 LEFT JOIN ( SELECT leave.employee_id, leave.schedule_date, string_agg(COALESCE(ht_ir.value, holiday_type.name,'') || COALESCE(cast(leave.duration AS char(12)),'') || COALESCE(pu_ir.value, product_uom.name,''),',') as holiday_info FROM leave_detail_view leave LEFT JOIN hr_holiday_type holiday_type ON holiday_type.id = leave.leave_type LEFT JOIN product_uom ON product_uom.id=leave.product_uom_id LEFT JOIN ir_translation ht_ir ON ht_ir.res_id=holiday_type.id AND ht_ir.lang='zh_CN' and ht_ir.name='hr.holiday.type,name' LEFT JOIN ir_translation pu_ir ON pu_ir.res_id=product_uom.id AND pu_ir.lang='zh_CN' and pu_ir.name='product.uom,name' WHERE leave.state='done' and holiday_type.name not ilike '%%出%%' GROUP BY leave.employee_id, leave.schedule_date ) holiday ON holiday.employee_id=emp.id AND holiday.schedule_date=detail.title – 非 外出，出差 之外的休假 LEFT JOIN ( SELECT leave.employee_id, leave.schedule_date, string_agg(COALESCE(ht_ir.value, holiday_type.name,'') || COALESCE(cast(leave.duration AS char(12)),'') || COALESCE(pu_ir.value, product_uom.name,''),',') as holiday_info FROM leave_detail_view leave LEFT JOIN hr_holiday_type holiday_type ON holiday_type.id = leave.leave_type LEFT JOIN product_uom ON product_uom.id=leave.product_uom_id LEFT JOIN ir_translation ht_ir ON ht_ir.res_id=holiday_type.id AND ht_ir.lang='zh_CN' and ht_ir.name='hr.holiday.type,name' LEFT JOIN ir_translation pu_ir ON pu_ir.res_id=product_uom.id AND pu_ir.lang='zh_CN' and pu_ir.name='product.uom,name' WHERE leave.state='done' and holiday_type.name ilike '%%出%%' GROUP BY leave.employee_id, leave.schedule_date ) holiday2 ON holiday2.employee_id=emp.id AND holiday2.schedule_date=detail.title;