社保合规================== select data->>'c2' 工号, data->>'c1' 月份, data->>'c3' 姓名, ci_trans.value 城市, lo_trans.value 地区, le_trans.value 工资所属公司, data->>'c6' 部门, cc_trans.value 成本中心, mbu_trans.value mbu, emp.hire_date 入职日期, to_number(data->>'c38','999999999999')+to_number(data->>'c39','999999999999') 社保公司承担费用, (case when data->>'c1v'='' then 0 else to_number(data->>'c1v','999999999999') end+case when data->>'c1w'='' then 0 else to_number(data->>'c1w','999999999999') end) 社保个人承担费用, (case when data->>'c1p'='' then 0 else to_number(data->>'c1p','999999999999') end) 个人失业, (case when data->>'c2l' is null then 0 else to_number(data->>'c2l','999999999999') end) 企业失业, (case when data->>'c1n'='' then 0 else to_number(data->>'c1n','999999999999') end) 个人养老, (case when data->>'c2j'='' then 0 else to_number(data->>'c2j','999999999999') end) 企业养老, (case when data->>'c2n'='' then 0 else to_number(data->>'c2n','999999999999') end) 企业工伤, (case when data->>'c1o'='' then 0 else to_number(data->>'c1o','999999999999') end) 个人医疗, (case when data->>'c2k'='' then 0 else to_number(data->>'c2k','999999999999') end) 企业医疗, (case when data->>'c2m'='' then 0 else to_number(data->>'c2m','999999999999') end) 企业生育, ins_basis.公司养老基数, ins_basis.公司医疗保险基数, ins_basis.公司失业保险基数, ins_basis.公司工伤保险基数, ins_basis.公司公积金基数, shebao_sub.养老个人比例+shebao_sub.医疗个人比例+shebao_sub.失业个人比例+shebao_sub.工伤个人比例+shebao_sub.生育个人比例+gongjijin_sub.公积金公司比例 个人比例, shebao_sub.养老公司比例+shebao_sub.医疗公司比例+shebao_sub.失业公司比例+shebao_sub.工伤公司比例+shebao_sub.生育公司比例+gongjijin_sub.公积金个人比例 公司比例, shebao_sub.name 社保规则, gongjijin_sub.name 公积金规则 from global_table_rows left join hr_employee emp on data->>'c2'=emp.employee_number left join emp_job on emp_job.employee_id=emp.id left join res_city on emp_job.x_work_location_test=res_city.id left join ir_translation ci_trans on ci_trans.res_id=res_city.id and ci_trans.name ='res.city,name' and ci_trans.lang='zh_CN' left join hr_location on emp_job.location_id = hr_location.id left join ir_translation lo_trans on lo_trans.res_id=hr_location.id and lo_trans.name ='hr.location,name' and lo_trans.lang='zh_CN' left join legal_entity on emp_job.legal_entity_id=legal_entity.id left join ir_translation le_trans on le_trans.res_id=legal_entity.id and le_trans.name ='legal.entity,name' and le_trans.lang='zh_CN' left join cost_center on emp_job.cost_center_id=cost_center.id left join ir_translation cc_trans on cc_trans.res_id=cost_center.id and cc_trans.name ='cost.center,name' and cc_trans.lang='zh_CN' left join cost_center mbu on emp_job.x_mbu_id = mbu.id left join ir_translation mbu_trans on mbu_trans.res_id=mbu.id and mbu_trans.name ='cost.center,name' and mbu_trans.lang='zh_CN' left join (select benefits_employee_rule.employee_id, benefits_employee_rule.effective_start_date, benefits_employee_rule.effective_end_date, sum(case when insurance_type_name='养老保险' then employee_basis else 0 end) 个人养老基数, sum(case when insurance_type_name='养老保险' then company_basis else 0 end) 公司养老基数, sum(case when insurance_type_name='医疗保险' then employee_basis else 0 end) 个人医疗保险基数, sum(case when insurance_type_name='医疗保险' then company_basis else 0 end) 公司医疗保险基数, sum(case when insurance_type_name='失业保险' then employee_basis else 0 end) 个人失业保险基数, sum(case when insurance_type_name='失业保险' then company_basis else 0 end) 公司失业保险基数, sum(case when insurance_type_name='工伤保险' then employee_basis else 0 end) 个人工伤保险基数, sum(case when insurance_type_name='工伤保险' then company_basis else 0 end) 公司工伤保险基数, sum(case when insurance_type_name='生育保险' then employee_basis else 0 end) 个人生育保险基数, sum(case when insurance_type_name='生育保险' then company_basis else 0 end) 公司生育保险基数, sum(case when insurance_type_name='公积金' then employee_basis else 0 end) 个人公积金基数, sum(case when insurance_type_name='公积金' then company_basis else 0 end) 公司公积金基数 from benefits_employee_rule inner join benefits_employee_insurance on benefits_employee_insurance.benefits_employee_rule_id = benefits_employee_rule.id and benefits_employee_rule.is_delete = 0 where benefits_employee_insurance.is_delete = 0 group by benefits_employee_rule.employee_id,benefits_employee_rule.effective_start_date,benefits_employee_rule.effective_end_date) ins_basis on emp.id=ins_basis.employee_id and to_date(substr(data->>'c1',1,4)||' '||substr(data >>'c1',5,2)||'-'||'01','YYYY-mm-dd') between ins_basis.effective_start_date and ins_basis.effective_end_date left join (select ber.employee_id, bcr.code, bcr.name, ber.effective_start_date ber_effective_start_date, ber.effective_end_date ber_effective_end_date, bcr.effective_start_date bcr_effective_start_date, bcr.effective_end_date bcr_effective_end_date, sum(case when bci.insurance_type_name='养老保险' then bci.company_upper_limit else 0 end) 养老上限, sum(case when bci.insurance_type_name='养老保险' then bci.company_floor_limit else 0 end) 养老下限, sum(case when bci.insurance_type_name='医疗保险' then bci.company_upper_limit else 0 end) 医疗保险上限, sum(case when bci.insurance_type_name='医疗保险' then bci.company_floor_limit else 0 end) 医疗保险下限, sum(case when bci.insurance_type_name='失业保险' then bci.company_upper_limit else 0 end) 失业保险上限, sum(case when bci.insurance_type_name='失业保险' then bci.company_floor_limit else 0 end) 失业保险下限, sum(case when bci.insurance_type_name='工伤保险' then bci.company_upper_limit else 0 end) 工伤保险上限, sum(case when bci.insurance_type_name='工伤保险' then bci.company_floor_limit else 0 end) 工伤保险下限, sum(case when bci.insurance_type_name='生育保险' then bci.company_upper_limit else 0 end) 生育保险上限, sum(case when bci.insurance_type_name='生育保险' then bci.company_floor_limit else 0 end) 生育保险下限, sum(case when bci.insurance_type_name='公积金' then bci.company_upper_limit else 0 end) 公积金上限, sum(case when bci.insurance_type_name='公积金' then bci.company_floor_limit else 0 end) 公积金下限, sum(case when bci.insurance_type_name='养老保险' then bci.individual_payment_percent else 0 end) 养老个人比例, sum(case when bci.insurance_type_name='养老保险' then bci.company_payment_percent else 0 end) 养老公司比例, sum(case when bci.insurance_type_name='医疗保险' then bci.individual_payment_percent else 0 end) 医疗个人比例, sum(case when bci.insurance_type_name='医疗保险' then bci.company_payment_percent else 0 end) 医疗公司比例, sum(case when bci.insurance_type_name='失业保险' then bci.individual_payment_percent else 0 end) 失业个人比例, sum(case when bci.insurance_type_name='失业保险' then bci.company_payment_percent else 0 end) 失业公司比例, sum(case when bci.insurance_type_name='工伤保险' then bci.individual_payment_percent else 0 end) 工伤个人比例, sum(case when bci.insurance_type_name='工伤保险' then bci.company_payment_percent else 0 end) 工伤公司比例, sum(case when bci.insurance_type_name='生育保险' then bci.individual_payment_percent else 0 end) 生育个人比例, sum(case when bci.insurance_type_name='生育保险' then bci.company_payment_percent else 0 end) 生育公司比例 from benefits_employee_rule ber inner join benefits_employee_insurance bei on bei.benefits_employee_rule_id = ber.id and bei.is_delete = 0 inner join benefits_insurance bi on bei.insurance_type_id = bi.id and bi.category = 1 and bi.is_delete=0 inner join benefits_company_rule bcr on bcr.de_id = ber.social_insurance_rule_de_id and bcr.insurance_category =1 and bcr.is_delete = 0 inner join benefits_employee_rule_information beri on beri.group_id = ber.benefits_employee_rule_information_group_id and beri.information_type = 1 and beri.is_delete = 0 inner join user_benefits_view ubv on ber.employee_id = ubv.employee_id inner join benefits_company_account bca on bca.id = beri.social_insurance_account_unit_id and bca.is_delete = 0 left join benefits_company_insurance bci on bci.benefits_company_rule_id = bcr.id and bci.is_delete=0 and bci.insurance_type_id=bei.insurance_type_id where ber.is_delete = 0 group by ber.employee_id, bcr.de_id, bcr.code, bcr.name, ber.effective_start_date, ber.effective_end_date, bcr.effective_start_date, bcr.effective_end_date) shebao_sub on emp.id=shebao_sub.employee_id and to_date(substr(data->>'c1',1,4)||' '||substr(data >>'c1',5,2)||'-'||'01','YYYY-mm-dd') between shebao_sub.ber_effective_start_date and shebao_sub.ber_effective_end_date and to_date(substr(data->>'c1',1,4)||' '||substr(data >>'c1',5,2)||'-'||'01','YYYY-mm-dd') between shebao_sub.bcr_effective_start_date and shebao_sub.bcr_effective_end_date left join(select ber.employee_id, bcr.code, bcr.name, ber.effective_start_date ber_effective_start_date, ber.effective_end_date ber_effective_end_date, bcr.effective_start_date bcr_effective_start_date, bcr.effective_end_date bcr_effective_end_date, sum(case when bci.insurance_type_name='公积金' then bci.individual_payment_percent else 0 end) 公积金个人比例, sum(case when bci.insurance_type_name='公积金' then bci.company_payment_percent else 0 end) 公积金公司比例 from benefits_employee_rule ber inner join benefits_employee_insurance bei on bei.benefits_employee_rule_id = ber.id and bei.is_delete = 0 inner join benefits_insurance bi on bei.insurance_type_id = bi.id and bi.category = 2 and sub_category = 1 and bi.is_delete=0 inner join benefits_company_rule bcr on bcr.de_id = ber.houseing_fund_rule_de_id and bcr.insurance_category =2 and bcr.is_delete = 0 inner join benefits_employee_rule_information beri on beri.group_id = ber.benefits_employee_rule_information_group_id and beri.information_type = 2 and beri.is_delete = 0 inner join user_benefits_view ubv on ber.employee_id = ubv.employee_id inner join benefits_company_account bca on bca.id = beri.houseing_fund_account_unit_id and bca.is_delete = 0 left join benefits_company_insurance bci on bci.benefits_company_rule_id = bcr.id and bci.is_delete=0 and bci.insurance_type_id=bei.insurance_type_id where ber.is_delete = 0 group by ber.employee_id, bcr.de_id, bcr.code, bcr.name, ber.effective_start_date, ber.effective_end_date, bcr.effective_start_date, bcr.effective_end_date) gongjijin_sub on emp.id=gongjijin_sub.employee_id and to_date(substr(data->>'c1',1,4)||' '||substr(data >>'c1',5,2)||'-'||'01','YYYY-mm-dd') between gongjijin_sub.ber_effective_start_date and gongjijin_sub.ber_effective_end_date and to_date(substr(data->>'c1',1,4)||' '||substr(data >>'c1',5,2)||'-'||'01','YYYY-mm-dd') between gongjijin_sub.bcr_effective_start_date and gongjijin_sub.bcr_effective_end_date where global_table_rows.table_id = (select id from global_tables where code ='payroll_deatil_dsm' and deleted_at is null) and global_table_rows.deleted_at is null and emp_job.eroad_index_id IS NOT NULL AND emp_job.eroad_start_date <= current_date AND (emp_job.eroad_end_date >= current_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 离职率================== today = datetime.date.today() the_day = today - dateutil.relativedelta.relativedelta(months=1) last_begin = the_day.replace(day=1) the_month = the_day.strftime('%Y-%m') last_begin = last_begin.strftime('%Y-%m-%d') sql_delete = """delete from x_builder_lizhlv where x_year_month='{}'""".format(the_month) sql = """ select ' {the_month}' x_year_month, begin_active.location_name x_dq, begin_active.city_name x_cs, begin_active.legal_entity x_frgs, begin_active.dep_name x_bmmc, begin_active.cost_center x_cbbm, begin_active.mbu x_mbu, begin_active.employee_type, begin_active.counts x_qirs, period_hire.counts x_rzrs, period_leave.counts x_lirs, – case when (x_qirs+x_rzrs) = 0 then 0 else x_lirs/(x_qirs+x_rzrs) end x_lzl case when (COALESCE(begin_active.counts,0)+COALESCE(period_hire.counts,0))=0 then 0 else COALESCE(period_leave.counts,0)/(COALESCE(begin_active.counts,0)+COALESCE(period_hire.counts,0))::numeric*100 end x_lzl from – 起初在职人数 (select count(distinct emp.id) counts, dep_trans.value dep_name, lo_trans.value location_name, res_city.name city_name, le_trans.value legal_entity, cc_trans.value cost_center, mbu_trans.value mbu, employee_type_trans.value employee_type from hr_employee emp left join emp_job on emp_job.employee_id=emp.id left join hr_department on emp_job.department_id=hr_department.id 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' left join hr_location on emp_job.location_id = hr_location.id left join ir_translation lo_trans on lo_trans.res_id=hr_location.id and lo_trans.name ='hr.location,name' and lo_trans.lang='zh_CN' left join res_city on emp_job.x_work_location_test=res_city.id left join ir_translation ci_trans on ci_trans.res_id=res_city.id and ci_trans.name ='res.city,name' and ci_trans.lang='zh_CN' left join legal_entity on emp_job.legal_entity_id=legal_entity.id left join ir_translation le_trans on le_trans.res_id=legal_entity.id and le_trans.name ='legal.entity,name' and le_trans.lang='zh_CN' left join cost_center on emp_job.cost_center_id=cost_center.id left join ir_translation cc_trans on cc_trans.res_id=cost_center.id and cc_trans.name ='cost.center,name' and cc_trans.lang='zh_CN' left join cost_center mbu on emp_job.x_mbu_id = mbu.id left join ir_translation mbu_trans on mbu_trans.res_id=mbu.id and mbu_trans.name ='cost.center,name' and mbu_trans.lang='zh_CN' left join employee_type on emp_job.employee_type=employee_type.id left join ir_translation employee_type_trans on employee_type_trans.res_id=employee_type.id and employee_type_trans.name ='employee.type,name' and employee_type_trans.lang='zh_CN' where emp_job.eroad_start_date<='{last_begin}' and (emp_job.eroad_end_date is null or emp_job.eroad_end_date>='{last_begin}') and emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL and emp_job.eroad_index_id IS NOT NULL and emp_job.department_id is not null and (emp.termination_date is null or emp.termination_date>='{last_begin}') group by dep_trans.value,lo_trans.value,res_city.name,le_trans.value,cc_trans.value,mbu_trans.value,employee_type_trans.value) begin_active left join – 期间入职人数 (select count(distinct emp.id) counts, dep_trans.value dep_name, lo_trans.value location_name, res_city.name city_name, le_trans.value legal_entity, cc_trans.value cost_center, mbu_trans.value mbu, employee_type_trans.value employee_type from hr_employee emp left join emp_job on emp_job.employee_id=emp.id left join hr_department on emp_job.department_id=hr_department.id 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' left join hr_location on emp_job.location_id = hr_location.id left join ir_translation lo_trans on lo_trans.res_id=hr_location.id and lo_trans.name ='hr.location,name' and lo_trans.lang='zh_CN' left join res_city on emp_job.x_work_location_test=res_city.id left join ir_translation ci_trans on ci_trans.res_id=res_city.id and ci_trans.name ='res.city,name' and ci_trans.lang='zh_CN' left join legal_entity on emp_job.legal_entity_id=legal_entity.id left join ir_translation le_trans on le_trans.res_id=legal_entity.id and le_trans.name ='legal.entity,name' and le_trans.lang='zh_CN' left join cost_center on emp_job.cost_center_id=cost_center.id left join ir_translation cc_trans on cc_trans.res_id=cost_center.id and cc_trans.name ='cost.center,name' and cc_trans.lang='zh_CN' left join cost_center mbu on emp_job.x_mbu_id = mbu.id left join ir_translation mbu_trans on mbu_trans.res_id=mbu.id and mbu_trans.name ='cost.center,name' and mbu_trans.lang='zh_CN' left join employee_type on emp_job.employee_type=employee_type.id left join ir_translation employee_type_trans on employee_type_trans.res_id=employee_type.id and employee_type_trans.name ='employee.type,name' and employee_type_trans.lang='zh_CN' where to_char(emp_job.eroad_start_date,'YYYY-mm')<='{the_month} ' and (emp_job.eroad_end_date is null or to_char(emp_job.eroad_end_date,'YYYY-mm')>=' {the_month}') and emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL and emp_job.eroad_index_id IS NOT NULL and emp_job.department_id is not null and to_char(emp.hire_date,'YYYY-mm')='{the_month} ' group by dep_trans.value,lo_trans.value,res_city.name,le_trans.value,cc_trans.value,mbu_trans.value,employee_type_trans.value) period_hire on begin_active.dep_name=period_hire.dep_name and begin_active.location_name=period_hire.location_name and begin_active.city_name=period_hire.city_name and begin_active.legal_entity=period_hire.legal_entity and begin_active.cost_center=period_hire.cost_center and begin_active.mbu=period_hire.mbu left join – 期间离职人数 (select count(distinct emp.id) counts, dep_trans.value dep_name, lo_trans.value location_name, res_city.name city_name, le_trans.value legal_entity, cc_trans.value cost_center, mbu_trans.value mbu, employee_type_trans.value employee_type from hr_employee emp left join emp_job on emp_job.employee_id=emp.id left join hr_department on emp_job.department_id=hr_department.id 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' left join hr_location on emp_job.location_id = hr_location.id left join ir_translation lo_trans on lo_trans.res_id=hr_location.id and lo_trans.name ='hr.location,name' and lo_trans.lang='zh_CN' left join res_city on emp_job.x_work_location_test=res_city.id left join ir_translation ci_trans on ci_trans.res_id=res_city.id and ci_trans.name ='res.city,name' and ci_trans.lang='zh_CN' left join legal_entity on emp_job.legal_entity_id=legal_entity.id left join ir_translation le_trans on le_trans.res_id=legal_entity.id and le_trans.name ='legal.entity,name' and le_trans.lang='zh_CN' left join cost_center on emp_job.cost_center_id=cost_center.id left join ir_translation cc_trans on cc_trans.res_id=cost_center.id and cc_trans.name ='cost.center,name' and cc_trans.lang='zh_CN' left join cost_center mbu on emp_job.x_mbu_id = mbu.id left join ir_translation mbu_trans on mbu_trans.res_id=mbu.id and mbu_trans.name ='cost.center,name' and mbu_trans.lang='zh_CN' left join employee_type on emp_job.employee_type=employee_type.id left join ir_translation employee_type_trans on employee_type_trans.res_id=employee_type.id and employee_type_trans.name ='employee.type,name' and employee_type_trans.lang='zh_CN' where to_char(emp_job.eroad_start_date,'YYYY-mm')<=' {the_month}' and (emp_job.eroad_end_date is null or to_char(emp_job.eroad_end_date,'YYYY-mm')>='{the_month} ') and emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL and emp_job.eroad_index_id IS NOT NULL and emp_job.department_id is not null and to_char(emp.termination_date,'YYYY-mm')=' {the_month} ' group by dep_trans.value,lo_trans.value,res_city.name,le_trans.value,cc_trans.value,mbu_trans.value,employee_type_trans.value) period_leave on begin_active.dep_name=period_leave.dep_name and begin_active.location_name=period_leave.location_name and begin_active.city_name=period_leave.city_name and begin_active.legal_entity=period_leave.legal_entity and begin_active.cost_center=period_leave.cost_center and begin_active.mbu=period_leave.mbu""".format(the_month=the_month,last_begin=last_begin) env.cr.execute(sql_delete) env.cr.execute('select * from x_builder_lizhilv') the_month_recs = env ['x_builder_lizhilv'] .search( [('x_year_month','=',the_month)] ) for rec in the_month_recs: rec.write( {'active':False} ) env.cr.execute(sql) all_data = env.cr.fetchall() for data in all_data: raise Warning(str(data)) (x_year_month,x_dq,x_cs,x_frgs,x_bmmc,x_cbbm,x_mbu,employee_type,x_qirs,x_rzrs,x_lirs,x_lzl) ('2021-07', '华北区域', '北京市', '上海安锐盟企业服务有限公司', 'IFM北区事业部', 'IFM北区事业部', None, '全日制', 1, None, None, 0) env ['x_builder_lizhilv'] .create( {'x_year_month':data[0], 'x_dq':data[1], 'x_cs':data[2], 'x_frgs':data[3], 'x_bmmc':data[4], 'x_cbbm':data[5], 'x_mbu':data[6], 'x_yglx':data[7], 'x_qirs':data[8], 'x_rzrs':data[9], 'x_lirs':data[10], 'x_lzl':data[11], 'active':True } )