meidu2021/daju/target/classes/cn/com/lzt/userstats/sql/UserStatsMinidaoDao_deptUserStatsData.sql

SELECT *,earlymonthTotal+entryTotal-leaveTotal endmonthTotal,IFNULL(CONVERT(e.leaveTotal/e.earlymonthTotal*100,decimal(10,2)),0) flowProportion ,:param.yearmonth yearmonth
from (
	SELECT  dept.pjtid,dept.pmid,emt.deptId,IFNULL(dus.endmonth_total,IFNULL(emt.earlymonthTotal,0)) as earlymonthTotal,IFNULL(et.entryTotal,0) entryTotal,IFNULL(lt.leaveTotal,0) leaveTotal
	from (
		SELECT uo.org_id deptId ,COUNT(*) as earlymonthTotal
		from t_bus_user_personnel up
		LEFT JOIN t_s_user_org uo
		ON up.userid = uo.user_id and uo.`status`=0 and uo.ifpluralism = 0
		LEFT JOIN t_s_depart d
		ON d.ID = uo.org_id
		LEFT JOIN t_s_base_user u
		ON up.userid = u.ID
		WHERE 
		(up.position_status = 1 or up.position_status = 2) and up.contract_stime < :param.earlymonth and uo.org_id != '' and u.delete_flag = 0 
		GROUP BY uo.org_id			
	) emt
	LEFT JOIN 
	(
    	SELECT uw.belong_unitid deptId, uw.pjt_id pjtid,d.dept_proj_managerid pmid
		from t_bus_user_personnel bup
		LEFT JOIN t_bus_user_wage uw
		ON bup.userid = uw.userid  and uw.monthly =:param.yearmonth
		LEFT JOIN t_s_depart d
		ON uw.pjt_id = d.ID 
		WHERE uw.belong_unitid != '' and uw.pjt_id !='' and (d.org_type = 3 or d.org_type = 5)
		GROUP BY uw.pjt_id,uw.belong_unitid
	) dept ON dept.deptId = emt.deptId 
	LEFT JOIN 
	(
		SELECT up.userid,uo.org_id deptId ,COUNT(*) as entryTotal
		from t_bus_user_personnel up
		LEFT JOIN t_s_user_org uo
		ON up.userid = uo.user_id and uo.`status`=0 
		WHERE 
		(up.position_status = 1 or up.position_status = 2) and (up.contract_stime >= :param.earlymonth and up.contract_stime <=:param.endmonth)
		GROUP BY uo.org_id
	) et ON emt.deptId = et.deptId
	LEFT JOIN 
	(
		SELECT up.userid,uo.org_id deptId ,COUNT(*) as leaveTotal
		FROM t_bus_user_personnel up
		LEFT JOIN t_bus_leave l
		ON up.userid = l.userid
		LEFT JOIN t_s_user_org uo
		ON up.userid = uo.user_id and uo.`status`=0 
		WHERE l.leave_date >= :param.earlymonth and l.leave_date <=:param.endmonth and l.delete_flag =0
		GROUP BY uo.org_id
	) lt ON emt.deptId = lt.deptId
	LEFT JOIN t_bus_dept_user_statistics dus
	ON emt.deptId = dus.dept_id and dus.yearmonth=:param.beforemotn
) e

order by flowProportion desc