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": "不满勤","absenteeism":"旷工"}'::jsonb as clock_in),
clock_out as (select'{"leave_early": "早退","normal": "正常","no_clock_in": "未打卡","not_full": "不满勤","absenteeism":"旷工"}'::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,'/') 部门全路径,
x_builder_zj.x_zj 职级,
hr_attendance_details.attendance_date 日期,
hr_attendance_details.clock_in_time 打卡开始时间,
hr_attendance_details.clock_out_time 打卡结束时间,
(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
出勤状态,
daily_model.name 班次名称,
coalesce(hr_holiday_type_trans.value,hr_holiday_type.name) 休假类型,
leave_sub.event_date 休假开始时间,
leave_sub.event_date 休假结束时间,
case when business_trip_detail.holiday_type='workday' then '工作日' when business_trip_detail.holiday_type='weekend' then '休息日' else '' end 公出类型,
business_trip_detail.event_date 公出开始日期,
business_trip_detail.event_date 公出结束日期
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,
hr_holiday_leave_detail.event_date,
hr_holiday_leave_detail.holiday_type_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 hr_holiday_type on hr_holiday_type.id=leave_sub.holiday_type_id
left join ir_translation hr_holiday_type_trans on hr_holiday_type_trans.res_id=hr_holiday_type.id
and hr_holiday_type_trans.name ='hr.holiday.type,name'
and hr_holiday_type_trans.lang='zh_CN'
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'
left join business_trip_detail on business_trip_detail.trip_id=business_trip.id
and business_trip_detail.event_date=hr_attendance_details.attendance_date
left join x_builder_zj on emp_job.x_zj=x_builder_zj.id
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
and (clock_in_state<>'normal' or clock_out_state<>'normal' )
Change
High
杨光磊
夏海南
