SELECT
        hr_department.x_orgesbname                  AS 费用承担部门,
        emp_cost_distribution.x_display_name1       AS 项目,
--         x_builder_item.x_item                       AS 研发项目,
        res_selection.name                          AS 研发项目,
        MAX(first_department.name)                  AS 一级部门,
        MAX(second_department.name)                 AS 二级部门,
        MAX(thirdly_department.name)                AS 三级部门,
        MAX(fourthly_department.name)               AS 四级部门,
        MAX(fifth_department.name)                  AS 五级部门,
        MAX(payroll_ledger.solution_name)           AS 薪资方案名称,
        MAX(payroll_ledger.batch_name)              AS 薪资批次名称,
        COUNT (issue_pay_order_detail.emp_number)   AS 人数,
SUM(
    COALESCE(x_p13,0)+
    COALESCE(x_p15,0)+
    COALESCE(x_p17,0)+
    COALESCE(x_p19,0)+
    COALESCE(x_p21,0)+
    COALESCE(x_p23,0)+
    COALESCE(x_p25,0)+
    COALESCE(x_p27,0)+
    COALESCE(x_p29,0)+
    COALESCE(x_p30,0)+
    COALESCE(x_p31,0)+
    COALESCE(x_p33,0)+
    COALESCE(x_p35,0)+
    COALESCE(x_p39,0)+
    COALESCE(x_p40,0)+
    COALESCE(x_p43,0)+
    COALESCE(x_p45,0)+
    COALESCE(x_p47,0)+
    COALESCE(x_p49,0)+
    COALESCE(x_p51,0)+
    COALESCE(x_p53,0)+
    COALESCE(x_p56,0)+
    COALESCE(x_p58,0)+
    COALESCE(x_p60,0)+
    COALESCE(x_p62,0)+
    COALESCE(x_p64,0)+
    COALESCE(x_p65,0)+
    COALESCE(x_p69,0)+
    COALESCE(x_p221,0)+
    COALESCE(x_p91,0)+
    COALESCE(x_p92,0)+
    COALESCE(x_p94,0)+
    COALESCE(x_p103,0)+
    COALESCE(x_p104,0)+
    COALESCE(x_p105,0)+
    COALESCE(x_p106,0)+
    COALESCE(x_p108,0)+
    COALESCE(x_p109,0)+
    COALESCE(x_p110,0)+
    COALESCE(x_p215,0)+
    COALESCE(x_p67,0)
    ) AS 基本工资,
SUM(
    COALESCE(x_p72,0)+
    COALESCE(x_p75,0)+
    COALESCE(x_p82,0)+
    COALESCE(x_p85,0)+
    COALESCE(x_p88,0)+
    COALESCE(x_p89,0)
    ) AS 绩效奖金,
SUM(
    COALESCE(x_p118,0)+
    COALESCE(x_p120,0)+
    COALESCE(x_p122,0)
    ) AS 加班工资,
SUM(
COALESCE(x_p90,0)
    ) AS 节日福利（按工资形式发放）,
SUM(
    COALESCE(x_p95,0)+
    COALESCE(x_p96,0)+
    COALESCE(x_p99,0)+
    COALESCE(x_p100,0)+
    COALESCE(x_p101,0)+
    COALESCE(x_p111,0)
    ) AS 其它提成奖金,
SUM(
    COALESCE(x_p123,0)+
    COALESCE(x_p125,0)+
    COALESCE(x_p126,0)+
    COALESCE(x_p124,0)+
    COALESCE(x_p128,0)+
    COALESCE(x_p127,0)+
    COALESCE(x_p129,0)+
    COALESCE(x_p130,0)+
    COALESCE(x_p131,0)+
    COALESCE(x_p132,0)+
    COALESCE(x_p133,0)+
    COALESCE(x_p134,0)+
    COALESCE(x_p135,0)+
    COALESCE(x_p137,0)+
    COALESCE(x_p136,0)+
    COALESCE(x_p138,0)+
    COALESCE(x_p139,0)+
    COALESCE(x_p140,0)+
    COALESCE(x_p141,0)+
    COALESCE(x_p142,0)+
    COALESCE(x_p144,0)+
    COALESCE(x_p145,0)+
    COALESCE(x_p146,0)+
    COALESCE(x_p143,0)+
    COALESCE(x_p147,0)+
    COALESCE(x_p148,0)+
    COALESCE(x_p152,0)+
    COALESCE(x_p155,0)+
    COALESCE(x_p157,0)+
    COALESCE(x_p159,0)+
    COALESCE(x_p161,0)+
    COALESCE(x_p168,0)+
    COALESCE(x_p169,0)
    ) AS 工资扣款,
SUM(
    COALESCE(x_p98,0)
    ) AS 销售提成,
SUM(
    COALESCE(x_p102,0)
    ) AS 项目奖金,
SUM(
    COALESCE(severance_payments,0)
    ) AS 离职补偿金,
SUM(
    COALESCE(x_p36,0)
    ) AS 内部讲师费,
SUM(
    COALESCE(x_p97,0)
    ) AS 内部推荐,
SUM(
    COALESCE(x_p114,0)+
    COALESCE(x_p115,0)
    ) AS 年终奖,
SUM(
COALESCE(x_p107,0)
    ) AS 代收付业务成本（按工资形式发放）,
SUM(
    COALESCE(x_p175,0)
    ) AS 应发工资总额,
SUM(
    COALESCE(BASIC_PENSION_INS_EMPLOYEE_CONT,0)+
    COALESCE(BASIC_MED_INS_EMPLOYEE_CONT,0)+
    COALESCE(UNEMPLOYMENT_INS_EMPLOYEE_CONT,0)+
    COALESCE(SERIOUS_DISEASE_INS_EMPLOYEE_CONT,0)+
    COALESCE(x_p54,0)
    ) AS 社保（个人承担）,
SUM(
    COALESCE(PHF_ENPLOYEE_CONT,0)
    ) AS 公积金（个人承担）,

SUM(
    COALESCE(REGULAR_TAX,0)+
    COALESCE(x_p195,0)
    ) AS 代扣代缴个税,
SUM(
    COALESCE(x_p112,0)+
    COALESCE(x_p113,0)
    ) AS 代付生育津贴,
SUM(
    COALESCE(x_p172,0)+
    COALESCE(x_p173,0)+
    COALESCE(x_p213,0)
    ) AS 代扣代缴其他费用,
SUM(
    COALESCE(x_p170,0)
    ) AS 代扣代缴职工房租,
SUM(
    COALESCE(x_p171,0)
    ) AS 工会费（个人承担）,
SUM(
    COALESCE(x_shifa,0)
    ) AS 实发工资总额
FROM issue_pay_order_detail
        LEFT JOIN payroll_ledger ON issue_pay_order_detail.emp_number = payroll_ledger.employee_number
        LEFT JOIN emp_job ON emp_job.employee_number = issue_pay_order_detail.emp_number
        LEFT JOIN hr_department ON hr_department.id = emp_job.department_id
        -- x_first_department
        LEFT JOIN hr_department first_department ON first_department.id = emp_job.x_first_department
        -- x_second_department
        LEFT JOIN hr_department second_department ON second_department.id = emp_job.x_second_department
        -- x_thirdly_department
        LEFT JOIN hr_department thirdly_department ON thirdly_department.id = emp_job.x_thirdly_department
        -- x_fourthly_department
        LEFT JOIN hr_department fourthly_department ON fourthly_department.id = emp_job.x_fourthly_department
        -- x_fifth_department
        LEFT JOIN hr_department fifth_department ON fifth_department.id = emp_job.x_fifth_department

        LEFT JOIN hr_employee ON hr_employee.employee_number = issue_pay_order_detail.emp_number
        LEFT JOIN emp_cost_distribution ON emp_cost_distribution.employee_id = hr_employee.id
        LEFT JOIN x_builder_item ON x_builder_item.x_employee_no = issue_pay_order_detail.emp_number
        LEFT JOIN res_selection ON res_selection.id = x_builder_item.x_item
GROUP BY hr_department.x_orgesbname, emp_cost_distribution.x_display_name1, res_selection.name;
