<#-- 员工排班上报页面数据展示 -->
SELECT
	bu.id as userid,
	bu.realname as userName,
	tbpd.id,
	tbpd.pid,
	tbpd.pjt_postid as pjtPostId,
	postdetail.ptj_post_name as pjtPostName,
	tbpd.projarrange_generalid as projarrangeGeneralid,
	tbpd.working_days as workingDays,
	tbpd.timeout_overtime_hours as timeOutOvertimeHours,
	tbpd.country_overtime_hours as countryOvertimeHours,
	tbpd.onduty_days as ondutyDays,
	tbpd.yearmonth as yearmonth,
	tbpd.remark as remark,
	tbpd.create_name as createName,
	tbpd.create_by as createBy,
	tbpd.create_date as createDate,
	tbpd.update_name as updateName,
	tbpd.update_by as updateBy,
	tbpd.update_date as updateDate,
	tbpd.sys_org_code as sysOrgCode,
	tbpd.sys_company_code as sysCompanyCode 
FROM
	t_bus_projarrange_detail tbpd
LEFT JOIN t_s_base_user bu ON bu.id = tbpd.userid
LEFT JOIN t_bus_user_personnel up ON up.userid = tbpd.userid
LEFT JOIN t_s_user_org uo ON uo.user_id = tbpd.userid
LEFT JOIN t_bus_project_post_detail postdetail on postdetail.id = tbpd.pjt_postid
WHERE
1 = 1
<#if ( projArrangeDetailAndUserDto.childDepartIdList )?? && projArrangeDetailAndUserDto.childDepartIdList ?size gt 0>
	 AND uo.org_id IN(
	<#list projArrangeDetailAndUserDto.childDepartIdList as item>
		'${item}'
	<#if item_has_next>,</#if>
	</#list>
	)
<#else >
	and uo.org_id=null
</#if>
<#if (projArrangeDetailAndUserDto.yearmonth )?? && projArrangeDetailAndUserDto.yearmonth ?length gt 0>
	and tbpd.yearmonth = :projArrangeDetailAndUserDto.yearmonth 
<#else >
	and tbpd.yearmonth=null
</#if>
<#if (projArrangeDetailAndUserDto.pid )?? && projArrangeDetailAndUserDto.pid ?length gt 0>
	and tbpd.pid = :projArrangeDetailAndUserDto.pid 
<#else >
	and tbpd.pid=null
</#if>
<#if (projArrangeDetailAndUserDto.userName )?? && projArrangeDetailAndUserDto.userName ?length gt 0>
			and bu.realname like CONCAT('%', :projArrangeDetailAndUserDto.userName ,'%') 
		<#else >
			and 1=1
		</#if>
<#--在职状态 已入职1，已转正2,已离职3,已辞退4-->
and up.position_status in ('1','2','3','4')
AND bu.delete_flag = '0'
AND uo. STATUS = '0'
AND uo.ifpluralism = '0'
<#--order by pd.create_date desc-->
<#--order by pd.create_date desc,substring(bu.realname, 2, 2) asc-->
<#--order by pd.pjt_postid desc,pd.userid desc-->
order by tbpd.pjt_postid desc, convert(bu.realname using gbk) collate gbk_Chinese_ci asc