meidu2021/daju/target/classes/cn/com/lzt/overtimestats/sql/OvertimeStatsMinidaoDao_deptOvertimeStatsData.sql

SELECT 
	yearmonth,
	pjtId,
	deptId,
	pmId,
	overtimeDuration - Atimeout - Anational - Aspecial + Btimeout + Bnational + Bspecial overtimeDuration,
	deptPeopleTotal,
	CONVERT(overtimeFee - AtimeoutPrice - AnationalPrice - AspecialPrice + BtimeoutPrice + BnationalPrice + BspecialPrice, DECIMAL (10, 2)) overtimeFee
FROM
	(SELECT 
		c.*,
		(
		SELECT 
			SUM(IFNULL(datum_date, 0)) 
		FROM
			t_bus_user_attendance_detail 
		WHERE
			borrowstatus = '1' AND 
			borrowspid = c.pjtId AND 
			borrowsdepartid = c.deptId AND
			DATE_FORMAT(ymd_date, "%Y-%m") = :params.yearmonth
		) Bdatum,
		(
		SELECT 
			IFNULL(SUM(timeout_overtime_duration), 0) 
		FROM
			t_bus_user_attendance_detail 
		WHERE
			borrowstatus = '1' AND
			borrowspid = c.pjtId AND
			borrowsdepartid = c.deptId AND
			DATE_FORMAT(ymd_date, "%Y-%m") = :params.yearmonth
		) Btimeout,
		(
		SELECT 
			IFNULL(SUM(national_overtime_duration), 0) 
		FROM
			t_bus_user_attendance_detail 
		WHERE
			borrowstatus = '1' AND
			borrowspid = c.pjtId AND
			borrowsdepartid = c.deptId AND
			DATE_FORMAT(ymd_date, "%Y-%m") = :params.yearmonth
		) Bnational,
		(
		SELECT 
			IFNULL(SUM(special_overtime), 0) 
		FROM
			t_bus_user_attendance_detail 
		WHERE
			borrowstatus = '1' AND
			borrowspid = c.pjtId AND
			borrowsdepartid = c.deptId AND
			DATE_FORMAT(ymd_date, "%Y-%m") = :params.yearmonth
		) Bspecial,
		(
		SELECT 
			IFNULL(SUM(CONVERT(:params.basepay / (8 * 21.75), DECIMAL (10, 3)) * IFNULL(bos.multiple, 0) * (IFNULL(uad.timeout_overtime_duration, 0) / 60)), 0) 
		FROM
			t_bus_user_attendance_detail uad
		LEFT JOIN t_bus_user_attendance_total uat ON  uad.attendance_id = uat.id
		LEFT JOIN t_bus_user_wagestrategy uws ON uat.user_id = uws.userid 
		LEFT JOIN t_bus_overtimepay_strategy bos ON uws.overtimepay_strategyid = bos.id 
		WHERE
			uad.borrowstatus = '1' AND
			uad.borrowspid = c.pjtId AND
			borrowsdepartid = c.deptId AND
			DATE_FORMAT(uad.ymd_date, "%Y-%m") = :params.yearmonth
		) BtimeoutPrice,
		(
		SELECT 
			IFNULL(SUM(CONVERT(:params.basepay / (8 * 21.75), DECIMAL (10, 3)) * 3 * (IFNULL(uad.national_overtime_duration, 0) / 60)), 0)
		FROM
			t_bus_user_attendance_detail uad
		LEFT JOIN t_bus_user_attendance_total uat ON uad.attendance_id = uat.id
		LEFT JOIN t_bus_user_wagestrategy uws ON uat.user_id = uws.userid 
		LEFT JOIN t_bus_overtimepay_strategy bos ON uws.overtimepay_strategyid = bos.id 
		WHERE
			uad.borrowstatus = '1' AND
			uad.borrowspid = c.pjtId AND
			borrowsdepartid = c.deptId AND
			DATE_FORMAT(uad.ymd_date, "%Y-%m") = :params.yearmonth
		) BnationalPrice,
		(
		SELECT 
			IFNULL(SUM(CONVERT(:params.basepay / (8 * 21.75), DECIMAL (10, 3)) * IFNULL(bos.multiple, 0) * (uad.special_overtime / 60)), 0)
		FROM
			t_bus_user_attendance_detail uad
		LEFT JOIN t_bus_user_attendance_total uat ON uad.attendance_id = uat.id
		LEFT JOIN t_bus_user_wagestrategy uws ON uat.user_id = uws.userid 
		LEFT JOIN t_bus_overtimepay_strategy bos ON uws.overtimepay_strategyid = bos.id 
		WHERE 
			uad.borrowstatus = '1' AND
			uad.borrowspid = c.pjtId AND
			borrowsdepartid = c.deptId AND
			DATE_FORMAT(uad.ymd_date, "%Y-%m") = :params.yearmonth
		) BspecialPrice 
	FROM
		(
		SELECT 
			b.yearmonth yearmonth,
			b.pid pjtId,
			b.departid deptId,
			b.pmId pmId,
			SUM(overtime_total_time) overtimeDuration,
			COUNT(*) deptPeopleTotal,
			SUM(overtimeFee) overtimeFee,
			SUM(Atimeout) Atimeout,
			SUM(Anational) Anational,
			SUM(Aspecial) Aspecial,
			SUM(AtimeoutPrice) AtimeoutPrice,
			SUM(AnationalPrice) AnationalPrice,
			SUM(AspecialPrice) AspecialPrice 
		FROM
			(
			SELECT 
				a.*,
				CONVERT(:params.basepay / (8 * 21.75), DECIMAL (10, 3)) * IFNULL(multiple, 0) * (Atimeout / 60) AtimeoutPrice,
				CONVERT(:params.basepay / (8 * 21.75), DECIMAL (10, 3)) * 3 * (Anational / 60) AnationalPrice,
				CONVERT(:params.basepay / (8 * 21.75), DECIMAL (10, 3)) * IFNULL(multiple, 0) * (Aspecial / 60) AspecialPrice
			FROM
				(
				SELECT 
					uat.*,
					(
					SELECT 
						IFNULL(SUM(IFNULL(datum_date, 0)), 0) 
					FROM
						t_bus_user_attendance_detail 
					WHERE 
						attendance_id = uat.id AND
						borrowstatus = '1'
					) Adatum,
					(
					SELECT 
						IFNULL(SUM(IFNULL(timeout_overtime_duration, 0)),0) 
					FROM
						t_bus_user_attendance_detail 
					WHERE 
						attendance_id = uat.id AND
						borrowstatus = '1'
					) Atimeout,
					(
					SELECT 
						IFNULL(SUM(IFNULL(national_overtime_duration, 0)),0) 
					FROM
						t_bus_user_attendance_detail 
					WHERE attendance_id = uat.id AND
					borrowstatus = '1'
					) Anational,
					(
					SELECT 
						IFNULL(SUM(IFNULL(special_overtime, 0)), 0) 
					FROM
						t_bus_user_attendance_detail 
					WHERE 
						attendance_id = uat.id AND
						borrowstatus = '1'
					) Aspecial 
				FROM
					(
					SELECT 
						uat.id,
						bos.multiple,
						uat.user_id,
						uat.pid pid,
						uat.departid,
						uat.yearmonth,
						d.dept_proj_managerid pmId,
						IFNULL(uat.overtime_total_time, 0) overtime_total_time,
						IFNULL(uat.datum_attendance_date, 0) datum_attendance_date,
						(IFNULL(uw.overtimepay_fixed, 0) + IFNULL(uw.overtimepay_timeout, 0) + IFNULL(uw.special_overtime, 0)) overtimeFee 
					FROM
						t_bus_user_attendance_total uat 
					LEFT JOIN t_bus_user_wage uw ON uat.user_id = uw.userid AND uat.yearmonth = uw.monthly 
					LEFT JOIN t_bus_user_wagestrategy uws ON uat.user_id = uws.userid 
					LEFT JOIN t_bus_overtimepay_strategy bos ON uws.overtimepay_strategyid = bos.id 
					LEFT JOIN t_s_depart d ON uat.pid = d.id
					) uat 
				WHERE 
					uat.yearmonth = :params.yearmonth AND
					uat.pid != '' AND
					uat.departid != ''
				) a
			) b 
			GROUP BY pid,departid
		) c
	) d
  WHERE overtimeDuration != 0 
  OR Bdatum != 0 
  OR Btimeout != 0 
  OR Bnational != 0 
  OR Bspecial != 0 
  
  order by overtimeDuration desc