meidu2021/daju/target/classes/cn/com/lzt/wageStats/sql/WageStatsMinidaoDao_deptWageStatsData.sql

SELECT yearmonth,pjtId,deptId,unitPeopleTotal
,convert(pre_tax_mone - IFNULL(Apay,Acount/:params.daycount * (pre_tax_mone + compro + comsoc)) + IFNULL(Bpay,Bcount/:params.daycount * (pre_tax_mone + compro + comsoc)),decimal(10,2)) wagePayTotal
from (
		SELECT o.*,
			 (
					SELECT COUNT(*) from t_bus_user_attendance_detail WHERE   borrowstatus ='1' and borrowspid = o.pjtId and borrowsdepartid = o.deptId and DATE_FORMAT(ymd_date,"%Y-%m")=:params.yearmonth
				) Bcount,
				(
					SELECT sum(day_pay) from t_bus_user_attendance_detail WHERE  borrowstatus ='1' and borrowspid = o.pjtId and borrowsdepartid = o.deptId and DATE_FORMAT(ymd_date,"%Y-%m")=:params.yearmonth
				) Bpay
		from 
		(
			SELECT a.monthly yearmonth,a.pid pjtId,a.departid deptId,a.pmId,COUNT(*) unitPeopleTotal,SUM(a.Acount) Acount,SUM(a.Apay) Apay,SUM(pre_tax_mone) pre_tax_mone,SUM(compro) compro,SUM(comsoc) comsoc
			FROM 
			( 
				SELECT uat.*,
					(
						SELECT COUNT(*) from t_bus_user_attendance_detail WHERE attendance_id=uat.id and borrowstatus ='1'
					) Acount,
					(
						SELECT sum(day_pay) from t_bus_user_attendance_detail WHERE attendance_id=uat.id and borrowstatus ='1'
					) Apay
					FROM(
						SELECT uw.userid,uw.monthly,uw.pre_tax_mone,IFNULL(uw.com_provident_fund_debit,0) compro ,IFNULL(uw.com_social_security_debit,0)  comsoc ,uw.pjt_id pid,uw.belong_unitid departId,uat.id,d.dept_proj_managerid pmId
						from t_bus_user_wage uw
						LEFT JOIN t_s_depart d
						ON uw.pjt_id = d.ID 
						LEFT JOIN t_bus_user_attendance_total uat
						ON uw.userid = uat.user_id and uat.yearmonth =:params.yearmonth 
						WHERE uw.monthly =:params.yearmonth and (d.org_type = 3 or d.org_type = 5)
					) uat
				 WHERE uat.monthly  =:params.yearmonth  AND uat.pid != '' 
			)a
			GROUP BY pid,departid
		) o
)re

order by wagePayTotal desc