月报： select data_main.employee_id, to_char(data_main.日期,'yyyy-mm') 日期, data_main.员工工号, data_main.姓名, data_main.所属业务单元, data_main.部门全路径, data_main.职级, avg(data_main.司龄) 司龄, sum(data_main.早退次数) 早退次数, sum(data_main.迟到次数) 迟到次数, sum(data_main.忘打卡次数) 忘打卡次数, sum(data_main.累计早退时长) 累计早退时长, sum(data_main.累计迟到时长) 累计迟到时长, sum(data_main.哺乳假) 哺乳假, sum(data_main.产假) 产假, sum(data_main.产检假) 产检假, sum(data_main.陪产假) 陪产假, sum(data_main.年假) 年假, sum(data_main.福利年假) 福利年假, sum(data_main.事假) 事假, sum(data_main.病假) 病假, sum(data_main.全薪病假) 全薪病假, sum(data_main.工伤假) 工伤假, sum(data_main.婚假) 婚假, sum(data_main.丧假) 丧假, sum(data_main.带薪假) 带薪假, sum(data_main.普通加班) 普通加班, sum(data_main.公休加班) 公休加班, sum(data_main.法定假日加班) 法定假日加班, sum(data_main.普通加班计发加班费时长) 普通加班计发加班费时长, sum(data_main.公休加班计发加班费时长) 公休加班计发加班费时长, sum(data_main.法定加班计发加班费时长) 法定加班计发加班费时长, sum(data_main.加班转调休) 加班转调休, sum(data_main.公出时间) 公出时间, sum(data_main.假期时长) 假期时长, sum(data_main.餐补次数) 餐补次数, sum(data_main.出勤班数) 出勤班数, sum(data_main.应出勤班数) 应出勤班数, sum(data_main.出勤工时) 出勤工时, sum(data_main.应出勤工时) 应出勤工时, sum(data_main.上班时长) 上班时长, round((case when sum(data_main.出勤班数)=0 then 0 else sum(data_main.上班时长)/sum(data_main.出勤班数) end)::numeric,2) 平均工作时长, coalesce(sum(leave_sub_outer.事假),0) 当年事假, coalesce(sum(leave_sub_outer.病假),0)当年病假, coalesce(sum(leave_sub_outer.全薪病假),0) 当年全薪病假, min(data_main.离职日期) 离职日期 from (WITH RECURSIVE dep_level_view AS ( select eroad_index_id as cid, dep_trans.value,code, ARRAY [hr_department.code] as code_list, ARRAY [case when dep_trans.value is not null then dep_trans.value else hr_department.name end] as name_list, ARRAY [eroad_index_id] as path_ids, FALSE as is_cycle from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' where eroad_index_id is not null and eroad_start_date <= current_date and (eroad_end_date >= current_date or eroad_end_date is null) and not is_virtual_org and status = 'active' and active and parent_department_id is NULL UNION SELECT dep_table.eroad_index_id as cid, dep_transt.value, dep_table.code, dep_level_view.code_list || dep_table.code as code_list, dep_level_view.name_list || case when dep_transt.value is not null then dep_transt.value else dep_table.name end as name_list, dep_level_view.path_ids || dep_table.eroad_index_id as path_ids, dep_table.eroad_index_id = any(dep_level_view.path_ids) as is_cycle FROM hr_department dep_table left join ir_translation dep_transt on dep_transt.res_id=dep_table.id and dep_transt.name ='hr.department,name' and dep_transt.lang='zh_CN' JOIN dep_level_view ON dep_table.parent_department_id = dep_level_view.cid and not dep_level_view.is_cycle and dep_table.eroad_index_id is not null and dep_table.eroad_start_date <= current_date and (dep_table.eroad_end_date >= current_date or dep_table.eroad_end_date is null) and not dep_table.is_virtual_org and dep_table.status = 'active' and dep_table.active ) select attendance_count.employee_id, attendance_count.name 日期, emp.employee_number 员工工号, emp.name 姓名, coalesce(bu_trans.value,hr_business_unit.name) 所属业务单元, array_to_string(dep_level_view.name_list,'/') 部门全路径, x_builder_zj.x_zj 职级, emp.inner_working_age 司龄, attendance_count.leave_early_count 早退次数, attendance_count.late_count 迟到次数, attendance_count.no_clock_in_count 忘打卡次数, attendance_count.display_leave_early_time 累计早退时长, attendance_count.display_late_time 累计迟到时长, coalesce(leave_sub.哺乳假,0) 哺乳假, coalesce(leave_sub.产假,0) 产假, coalesce(leave_sub.产检假,0) 产检假, coalesce(leave_sub.陪产假,0) 陪产假, coalesce(leave_sub.年假,0) 年假, coalesce(leave_sub.福利年假,0) 福利年假, coalesce(leave_sub.事假,0) 事假, coalesce(leave_sub.病假,0) 病假, coalesce(leave_sub.全薪病假,0) 全薪病假, coalesce(leave_sub.工伤假,0) 工伤假, coalesce(leave_sub.婚假,0) 婚假, coalesce(leave_sub.丧假,0) 丧假, coalesce(leave_sub.带薪假,0) 带薪假, coalesce(over_time_sub.普通加班,0) 普通加班, coalesce(over_time_sub.公休加班,0) 公休加班, coalesce(over_time_sub.法定假日加班,0) 法定假日加班, coalesce(over_time_sub.普通加班计发加班费时长,0) 普通加班计发加班费时长, coalesce(over_time_sub.公休加班计发加班费时长,0) 公休加班计发加班费时长, coalesce(over_time_sub.法定加班计发加班费时长,0) 法定加班计发加班费时长, coalesce(over_time_sub.加班转调休,0) 加班转调休, coalesce(bt_sub.duration,0) 公出时间, coalesce(leave_sub.总假期,0) 假期时长, case when (COALESCE(extract(hour from age(attendance_count.clock_out_time,attendance_count.clock_in_time)),0)::float+ COALESCE((extract(minute from age(attendance_count.clock_out_time,attendance_count.clock_in_time))/60),0)::float+ COALESCE((extract(second from age(attendance_count.clock_out_time,attendance_count.clock_in_time))/3600),0)::float -coalesce(leave_sub.总假期,0) )>=5 then 1 else 0 end 餐补次数, case when (COALESCE(extract(hour from age(day_calendar.work_off_start,day_calendar.work_on_start)),0)::float+ COALESCE((extract(minute from age(day_calendar.work_off_start,day_calendar.work_on_start))/60),0)::float+ COALESCE((extract(second from age(day_calendar.work_off_start,day_calendar.work_on_start))/3600),0)::float) -coalesce(day_calendar.rest_length,0)::float -coalesce(leave_sub.总假期,0)::float-coalesce(bt_sub.duration,0)::float<0 then 0 else round((((COALESCE(extract(hour from age(day_calendar.work_off_start,day_calendar.work_on_start)),0)::float+ COALESCE((extract(minute from age(day_calendar.work_off_start,day_calendar.work_on_start))/60),0)::float+ COALESCE((extract(second from age(day_calendar.work_off_start,day_calendar.work_on_start))/3600),0)::float) -coalesce(day_calendar.rest_length,0)::float -coalesce(leave_sub.总假期,0)::float-coalesce(bt_sub.duration,0)::float)/8.0)::numeric,2) end 出勤班数, case when attendance_count.day_type='work_day' then 1 else 0 end 应出勤班数, case when (COALESCE(extract(hour from age(day_calendar.work_off_start,day_calendar.work_on_start)),0)::float+ COALESCE((extract(minute from age(day_calendar.work_off_start,day_calendar.work_on_start))/60),0)::float+ COALESCE((extract(second from age(day_calendar.work_off_start,day_calendar.work_on_start))/3600),0)::float) -coalesce(day_calendar.rest_length,0)::float -coalesce(leave_sub.总假期,0)::float-coalesce(bt_sub.duration,0)::float<0 then 0 else (((COALESCE(extract(hour from age(day_calendar.work_off_start,day_calendar.work_on_start)),0)::float+ COALESCE((extract(minute from age(day_calendar.work_off_start,day_calendar.work_on_start))/60),0)::float+ COALESCE((extract(second from age(day_calendar.work_off_start,day_calendar.work_on_start))/3600),0)::float) – -coalesce(day_calendar.rest_length,0)::float -coalesce(leave_sub.总假期,0)::float-coalesce(bt_sub.duration,0)::float))::numeric end 出勤工时, (COALESCE(extract(hour from age(day_calendar.work_off_start,day_calendar.work_on_start)),0)::float+ COALESCE((extract(minute from age(day_calendar.work_off_start,day_calendar.work_on_start))/60),0)::float+ COALESCE((extract(second from age(day_calendar.work_off_start,day_calendar.work_on_start))/3600),0)::float) 应出勤工时, case when (COALESCE(extract(hour from age(attendance_count.clock_out_time,attendance_count.clock_in_time)),0)::float+ COALESCE((extract(minute from age(attendance_count.clock_out_time,attendance_count.clock_in_time))/60),0)::float+ COALESCE((extract(second from age(attendance_count.clock_out_time,attendance_count.clock_in_time))/3600),0)::float -COALESCE(day_calendar.rest_length,0)::float-COALESCE(over_time_sub.rest_duration,0)::float)<0 then 0 else (COALESCE(extract(hour from age(attendance_count.clock_out_time,attendance_count.clock_in_time)),0)::float+ COALESCE((extract(minute from age(attendance_count.clock_out_time,attendance_count.clock_in_time))/60),0)::float+ COALESCE((extract(second from age(attendance_count.clock_out_time,attendance_count.clock_in_time))/3600),0)::float -COALESCE(day_calendar.rest_length,0)::float-COALESCE(over_time_sub.rest_duration,0)::float) end 上班时长, emp.termination_date 离职日期 from attendance_count left join hr_employee emp on attendance_count.employee_id=emp.id left join emp_job on emp.id=emp_job.employee_id left join hr_business_unit on emp_job.business_unit_id=hr_business_unit.id left join ir_translation bu_trans on bu_trans.res_id=hr_business_unit.id and bu_trans.name ='hr.business.unit,name' and bu_trans.lang='zh_CN' left join dep_level_view on dep_level_view.cid = emp_job.department_id left join x_builder_zj on emp_job.x_zj=x_builder_zj.id – 年假 left join (select hr_holiday_leave.employee_id, hr_holiday_leave_detail.leave_date, sum(hr_holiday_leave_detail.standard_unit_duration) 总假期, sum(case when hr_holiday_type.code='L013' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 哺乳假, sum(case when hr_holiday_type.code='L011' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 产假, sum(case when hr_holiday_type.code='L010' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 产检假, sum(case when hr_holiday_type.code='L014' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 陪产假, sum(case when hr_holiday_type.code in ('L001','L004') then hr_holiday_leave_detail.standard_unit_duration else 0 end) 年假, sum(case when hr_holiday_type.code='L005' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 福利年假, sum(case when hr_holiday_type.code='L006' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 事假, sum(case when hr_holiday_type.code in ('L007','L027') then hr_holiday_leave_detail.standard_unit_duration else 0 end) 病假, sum(case when hr_holiday_type.code in ('L012','L026') then hr_holiday_leave_detail.standard_unit_duration else 0 end) 全薪病假, sum(case when hr_holiday_type.code='L023' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 工伤假, sum(case when hr_holiday_type.code='L009' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 婚假, sum(case when hr_holiday_type.code='L016' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 丧假, sum(case when hr_holiday_type.code='L020' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 带薪假 from hr_holiday_leave left join hr_holiday_leave_detail on hr_holiday_leave_detail.leave_id=hr_holiday_leave.id left join hr_holiday_type on hr_holiday_leave.holiday_type_id=hr_holiday_type.id where hr_holiday_leave.x_bp_state='done' group by hr_holiday_leave.employee_id,hr_holiday_leave_detail.leave_date) leave_sub on leave_sub.employee_id=attendance_count.employee_id and attendance_count.name=leave_sub.leave_date – 加班 left join (select hr_overtime.employee_id, overtime_date, sum(hr_overtime_line.flexible_rest_duration) rest_duration, sum(case when hr_overtime_type.name='Workday Overtime' then hr_overtime_line.active_time else 0 end) 普通加班, sum(case when hr_overtime_type.name='Weekend Overtime' then hr_overtime_line.active_time else 0 end) 公休加班, sum(case when hr_overtime_type.name='Holiday Overtime' then hr_overtime_line.active_time else 0 end) 法定假日加班, sum(case when hr_overtime_type.name='Workday Overtime' and hr_overtime_line.compensation_way=2 then hr_overtime_line.active_time else 0 end) 普通加班计发加班费时长, sum(case when hr_overtime_type.name='Weekend Overtime' and hr_overtime_line.compensation_way=2 then hr_overtime_line.active_time else 0 end) 公休加班计发加班费时长, sum(case when hr_overtime_type.name='Holiday Overtime' and hr_overtime_line.compensation_way=2 then hr_overtime_line.active_time else 0 end) 法定加班计发加班费时长, sum(case when hr_overtime_line.compensation_way=1 then hr_overtime_line.active_time else 0 end) 加班转调休 from hr_overtime left join hr_overtime_line on hr_overtime_line.overtime_id=hr_overtime.id left join compensation_way on hr_overtime_line.compensation_way=compensation_way.id left join ir_translation compensation_way_trans on compensation_way_trans.res_id=compensation_way.id and compensation_way_trans.name ='compensation.way,name' and compensation_way_trans.lang='zh_CN' left join hr_overtime_type on hr_overtime_line.overtime_type_id=hr_overtime_type.id where hr_overtime_line.active=True and hr_overtime.x_bp_state='done' group by hr_overtime.employee_id,overtime_date) over_time_sub on over_time_sub.employee_id=attendance_count.employee_id and attendance_count.name=over_time_sub.overtime_date – 公出 left join (select business_trip_detail.employee_id, business_trip_detail.trip_date, sum(duration) duration from business_trip_detail left join business_trip on business_trip_detail.trip_id=business_trip.id where business_trip.x_bp_state='done' group by business_trip_detail.employee_id, business_trip_detail.trip_date) bt_sub on bt_sub.employee_id=attendance_count.employee_id and attendance_count.name=bt_sub.trip_date – 排班 left join day_calendar on attendance_count.day_calendar_id=day_calendar.id WHERE emp_job.eroad_index_id IS NOT NULL AND emp_job.eroad_start_date <= attendance_count.name AND (emp_job.eroad_end_date >= attendance_count.name OR emp_job.eroad_end_date IS NULL) AND emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL and to_char(attendance_count.name,'yyyy-mm')='${effective_date:2021-11}') data_main left join (select hr_holiday_leave.employee_id, to_char(hr_holiday_leave_detail.leave_date,'yyyy'), sum(hr_holiday_leave_detail.standard_unit_duration) 总假期, sum(case when hr_holiday_type.code='L013' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 哺乳假, sum(case when hr_holiday_type.code='L011' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 产假, sum(case when hr_holiday_type.code='L010' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 产检假, sum(case when hr_holiday_type.code='L014' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 陪产假, sum(case when hr_holiday_type.code in ('L001','L004') then hr_holiday_leave_detail.standard_unit_duration else 0 end) 年假, sum(case when hr_holiday_type.code='L005' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 福利年假, sum(case when hr_holiday_type.code='L006' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 事假, sum(case when hr_holiday_type.code in ('L007','L027') then hr_holiday_leave_detail.standard_unit_duration else 0 end) 病假, sum(case when hr_holiday_type.code in ('L012','L026') then hr_holiday_leave_detail.standard_unit_duration else 0 end) 全薪病假, sum(case when hr_holiday_type.code='L023' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 工伤假, sum(case when hr_holiday_type.code='L009' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 婚假, sum(case when hr_holiday_type.code='L016' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 丧假, sum(case when hr_holiday_type.code='L020' then hr_holiday_leave_detail.standard_unit_duration else 0 end) 带薪假 from hr_holiday_leave left join hr_holiday_leave_detail on hr_holiday_leave_detail.leave_id=hr_holiday_leave.id left join hr_holiday_type on hr_holiday_leave.holiday_type_id=hr_holiday_type.id where hr_holiday_leave.x_bp_state='done' and to_char(hr_holiday_leave_detail.leave_date,'yyyy')= substring('${effective_date:2021-11}' from 0 for 4) and to_char(hr_holiday_leave_detail.leave_date,'yyyy-mm')<='${effective_date:2021-11}' group by hr_holiday_leave.employee_id,to_char(hr_holiday_leave_detail.leave_date,'yyyy')) leave_sub_outer on data_main.employee_id=leave_sub_outer.employee_id group by to_char(data_main.日期,'yyyy-mm'), data_main.员工工号, data_main.姓名, data_main.所属业务单元, data_main.部门全路径, data_main.employee_id, data_main.职级 日报： WITH RECURSIVE dep_level_view AS ( select eroad_index_id as cid, dep_trans.value,code, ARRAY [hr_department.code] as code_list, ARRAY [case when dep_trans.value is not null then dep_trans.value else hr_department.name end] as name_list, ARRAY [eroad_index_id] as path_ids, FALSE as is_cycle from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' where eroad_index_id is not null and eroad_start_date <= current_date and (eroad_end_date >= current_date or eroad_end_date is null) and not is_virtual_org and status = 'active' and active and parent_department_id is NULL UNION SELECT dep_table.eroad_index_id as cid, dep_transt.value, dep_table.code, dep_level_view.code_list || dep_table.code as code_list, dep_level_view.name_list || case when dep_transt.value is not null then dep_transt.value else dep_table.name end as name_list, dep_level_view.path_ids || dep_table.eroad_index_id as path_ids, dep_table.eroad_index_id = any(dep_level_view.path_ids) as is_cycle FROM hr_department dep_table left join ir_translation dep_transt on dep_transt.res_id=dep_table.id and dep_transt.name ='hr.department,name' and dep_transt.lang='zh_CN' JOIN dep_level_view ON dep_table.parent_department_id = dep_level_view.cid and not dep_level_view.is_cycle and dep_table.eroad_index_id is not null and dep_table.eroad_start_date <= current_date and (dep_table.eroad_end_date >= current_date or dep_table.eroad_end_date is null) and not dep_table.is_virtual_org and dep_table.status = 'active' and dep_table.active ), clock_in as (select ' {"late": "迟到", "normal": "正常", "no_clock_in": "未打卡", "not_full": "不满勤"} '::jsonb as clock_in), clock_out as (select' {"leave_early": "早退","normal": "正常","no_clock_in": "未打卡","not_full": "不满勤"} '::jsonb as clock_out) select emp.employee_number 员工工号, emp.name 姓名, coalesce(bu_trans.value,hr_business_unit.name) 所属业务单元, array_to_string(dep_level_view.name_list,'/') 部门全路径, hr_attendance_details.attendance_date 日期, hr_attendance_details.clock_in_time 上班刷卡, hr_attendance_details.clock_out_time 下班刷卡, case when hr_attendance_details.day_calendar_id is null then 0 else 1 end 上班天数, case when (COALESCE(extract(hour from age(hr_attendance_details.clock_out_time,hr_attendance_details.clock_in_time)),0)::float+ COALESCE((extract(minute from age(hr_attendance_details.clock_out_time,hr_attendance_details.clock_in_time))/60),0)::float+ COALESCE((extract(second from age(hr_attendance_details.clock_out_time,hr_attendance_details.clock_in_time))/3600),0)::float -COALESCE(day_calendar.rest_length,0)::float-COALESCE(overtime_sub.rest_duration,0)::float)<0 then 0 else (COALESCE(extract(hour from age(hr_attendance_details.clock_out_time,hr_attendance_details.clock_in_time)),0)::float+ COALESCE((extract(minute from age(hr_attendance_details.clock_out_time,hr_attendance_details.clock_in_time))/60),0)::float+ COALESCE((extract(second from age(hr_attendance_details.clock_out_time,hr_attendance_details.clock_in_time))/3600),0)::float -COALESCE(day_calendar.rest_length,0)::float-COALESCE(overtime_sub.rest_duration,0)::float) end 上班时长, daily_model.name 班次名称, (clock_in->>clock_in_state::varchar) ||'-'|| (clock_out->>clock_out_state::varchar) ||'-'|| case when overtime_sub.overtime_date is not null then '加班' else '无加班' end ||'-'|| COALESCE(leave_sub.flags,'无事假') ||'-'|| case when business_trip.employee_id is null then '无公出' else '公出' end 状态 from hr_attendance_details join clock_in on 1=1 join clock_out on 1=1 left join hr_employee emp on hr_attendance_details.employee_id=emp.id left join emp_job on emp.id=emp_job.employee_id left join hr_business_unit on emp_job.business_unit_id=hr_business_unit.id left join ir_translation bu_trans on bu_trans.res_id=hr_business_unit.id and bu_trans.name ='hr.business.unit,name' and bu_trans.lang='zh_CN' left join dep_level_view on dep_level_view.cid = emp_job.department_id left join day_calendar on hr_attendance_details.day_calendar_id=day_calendar.id left join daily_model on day_calendar.daily_model_id=daily_model.id left join (select hr_overtime.employee_id, overtime_date, sum(hr_overtime_line.flexible_rest_duration) rest_duration, array_agg(compensation_way_trans.value) from hr_overtime left join hr_overtime_line on hr_overtime_line.overtime_id=hr_overtime.id left join compensation_way on hr_overtime_line.compensation_way=compensation_way.id left join ir_translation compensation_way_trans on compensation_way_trans.res_id=compensation_way.id and compensation_way_trans.name ='compensation.way,name' and compensation_way_trans.lang='zh_CN' where hr_overtime_line.active=True and hr_overtime.x_bp_state='done' group by hr_overtime.employee_id,overtime_date) overtime_sub on overtime_sub.employee_id=emp.id and overtime_sub.overtime_date=hr_attendance_details.attendance_date left join (select hr_holiday_leave_detail.leave_date, hr_holiday_leave.employee_id, case when hr_holiday_leave_detail.holiday_type_id=4 then '事假' when hr_holiday_leave_detail.holiday_type_id is null then '无事假' else '无事假' end flags from hr_holiday_leave_detail left join hr_holiday_leave on hr_holiday_leave_detail.leave_id=hr_holiday_leave.id and hr_holiday_leave.x_bp_state='done') leave_sub on leave_sub.employee_id=emp.id and hr_attendance_details.attendance_date=leave_sub.leave_date left join business_trip on business_trip.employee_id=emp.id and hr_attendance_details.attendance_date between business_trip.start_date and business_trip.end_date and business_trip.x_bp_state='done' WHERE emp_job.eroad_index_id IS NOT NULL AND emp_job.eroad_start_date <= hr_attendance_details.attendance_date AND (emp_job.eroad_end_date >= hr_attendance_details.attendance_date OR emp_job.eroad_end_date IS NULL) AND emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL