<#-- 员工排班上报页面数据展示 -->
SELECT
	bu.id as userid,
	pd.id,
	pd.pid,
	pd.pjt_postid as pjtPostId,
	pd.projarrange_generalid as projarrangeGeneralid,
	pd.working_days as workingDays,
	pd.timeout_overtime_hours as timeOutOvertimeHours,
	pd.country_overtime_hours as countryOvertimeHours,
	pd.onduty_days as ondutyDays,
	pd.yearmonth as yearmonth,
	pd.remark as remark,
	pd.create_name as createName,
	pd.create_by as createBy,
	pd.create_date as createDate,
	pd.update_name as updateName,
	pd.update_by as updateBy,
	pd.update_date as updateDate,
	pd.sys_org_code as sysOrgCode,
	pd.sys_company_code as sysCompanyCode 
 from t_s_base_user bu 
 LEFT JOIN t_bus_user_personnel up  ON bu.ID=up.userid
LEFT JOIN t_s_user_org uo ON uo.user_id = bu.id
LEFT JOIN (
	SELECT
		*
	FROM
		t_bus_projarrange_detail tbpd
	WHERE
		<#-- tbpd.yearmonth = '2017-11'-->
		<#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>
) pd ON pd.userid = bu.id
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>
<#--在职状态 已入职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 pd.pjt_postid desc, bu.username desc