WITH RECURSIVE dep_level_view AS ( select eroad_index_id as cid, name,code, ARRAY [name] as name_list, ARRAY [eroad_index_id] as path_ids, FALSE as is_cycle from hr_department 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_table.name, dep_table.code, dep_level_view.name_list || dep_table.name 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 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 data ->>'c1' as 主键, data ->>'c2' as 员工工号, data ->>'c3' as 员工姓名, data ->>'c4' as 扣缴义务人公司, data ->>'c5' as 身份证号码, data ->>'c6' as 职位, data ->>'c7' as 部门, dep_level_view.name_list [1] AS "一级部门", dep_level_view.name_list [2] AS "二级部门", dep_level_view.name_list [3] AS "三级部门", dep_level_view.name_list [4] AS "四级部门", dep_level_view.name_list [5] AS "五级部门", data ->>'c1z' as 薪资组, data ->>'c1r' as 扣缴义务人, data ->>'c1s' as 账号, data ->>'c1t' as 户名, data ->>'c1u' as 开户行, data ->>'c1v' as 开户地, coalesce(nullif((data ->>'c8'),''),'0.00')::numeric(10,2) as 计薪天数, coalesce(nullif((data ->>'c9'),''),'0.00')::numeric(10,2) as 应出勤天数, coalesce(nullif((data ->>'ca'),''),'0.00')::numeric(10,2) as 实际天数, coalesce(nullif((data ->>'cb'),''),'0.00')::numeric(10,2) as 基本工资, coalesce(nullif((data ->>'cc'),''),'0.00')::numeric(10,2) as 岗位津贴, coalesce(nullif((data ->>'cd'),''),'0.00')::numeric(10,2) as 保密费, coalesce(nullif((data ->>'ce'),''),'0.00')::numeric(10,2) as 入离职扣款, coalesce(nullif((data ->>'ch'),''),'0.00')::numeric(10,2) as 福利费, coalesce(nullif((data ->>'ci'),''),'0.00')::numeric(10,2) as 浮动底薪, coalesce(nullif((data ->>'c1x'),''),'0.00')::numeric(10,2) as 有责底薪, coalesce(nullif((data ->>'cj'),''),'0.00')::numeric(10,2) as 加班费, coalesce(nullif((data ->>'ck'),''),'0.00')::numeric(10,2) as 离职补偿金, coalesce(nullif((data ->>'cl'),''),'0.00')::numeric(10,2) as 津贴, coalesce(nullif((data ->>'cm'),''),'0.00')::numeric(10,2) as 伯乐奖金, coalesce(nullif((data ->>'cn'),''),'0.00')::numeric(10,2) as 其他应发, coalesce(nullif((data ->>'co'),''),'0.00')::numeric(10,2) as 补发工资, coalesce(nullif((data ->>'cp'),''),'0.00')::numeric(10,2) as 绩效奖金, coalesce(nullif((data ->>'cq'),''),'0.00')::numeric(10,2) as 考勤扣款, coalesce(nullif((data ->>'cr'),''),'0.00')::numeric(10,2) as 年终奖金, coalesce(nullif((data ->>'ct'),''),'0.00')::numeric(10,2) as 平账, coalesce(nullif((data ->>'cu'),''),'0.00')::numeric(10,2) as 社保个人部分, coalesce(nullif((data ->>'cv'),''),'0.00')::numeric(10,2) as 公积金个人部分, coalesce(nullif((data ->>'cw'),''),'0.00')::numeric(10,2) as 扣薪不发薪项, coalesce(nullif((data ->>'cx'),''),'0.00')::numeric(10,2) as 其他税后扣款, coalesce(nullif((data ->>'cy'),''),'0.00')::numeric(10,2) as 个税调整, coalesce(nullif((data ->>'cz'),''),'0.00')::numeric(10,2) as 代扣税基数, coalesce(nullif((data ->>'c10'),''),'0.00')::numeric(10,2) as 应纳税所得额, coalesce(nullif((data ->>'c11'),''),'0.00')::numeric(10,2) as 代扣税, coalesce(nullif((data ->>'c12'),''),'0.00')::numeric(10,2) as 年终奖税金, coalesce(nullif((data ->>'c13'),''),'0.00')::numeric(10,2) as 离职补偿金税金, coalesce(nullif((data ->>'c14'),''),'0.00')::numeric(10,2) as 实发合计, coalesce(nullif((data ->>'c15'),''),'0.00')::numeric(10,2) as 社保企业部分, coalesce(nullif((data ->>'c16'),''),'0.00')::numeric(10,2) as 公积金企业部分, coalesce(nullif((data ->>'c1n'),''),'0.00')::numeric(10,2) as 专项抵扣, coalesce(nullif((data ->>'c1o'),''),'0.00')::numeric(10,2) as 考勤工资, coalesce(nullif((data ->>'c1m'),''),'0.00')::numeric(10,2) as 其他, coalesce(nullif((data ->>'c1w'),''),'0.00')::numeric(10,2) as 奖金, coalesce(nullif((data ->>'c1y'),''),'0.00')::numeric(10,2) as 人力成本 from global_table_rows left join dep_level_view on dep_level_view.code=data ->>'c21' where table_id = (select id from global_tables where code ='payroll_report') and deleted_at is null