meidu2021/daju/target/classes/cn/com/lzt/personnelbasearchivesmanage/sql/PersonnelBaseArchivesManageDao_getTiredperson.sql

SELECT suser.userNum userNum,
suser.id userid,
baseuser.realname userName,
suser.idcard certNum,
suser.gender gender,
SUBSTRING(suser.idcard,7,8) birth,
suser.mobilePhone mobile,
userpersonnel.contract_stime entryTime,
userpersonnel.leave_date leaveTime,
depart.glcname departName,
duties.duties_name dutiesName,
social.social_security_unit payUnit,
CONCAT(:toDate,'前退休') description
FROM t_s_base_user AS baseuser
LEFT JOIN t_s_user AS suser ON baseuser.id = suser.id
LEFT JOIN t_bus_user_personnel AS userpersonnel ON baseuser.id = userpersonnel.userid
LEFT JOIN t_bus_duties AS duties ON userpersonnel.belong_dutiesid = duties.id AND duties.delete_flag <> '1' AND duties.status <> '1'
LEFT JOIN t_s_user_org AS userorg ON baseuser.id = userorg.user_id AND userorg.status = '0' AND userorg.ifpluralism <> '1' 
LEFT JOIN t_s_depart AS depart ON userorg.org_id = depart.id AND depart.status <> '1'
LEFT JOIN t_bus_user_wagestrategy wagestrat on wagestrat.userid = baseuser.id and wagestrat.delete_flag <> '1'
LEFT JOIN t_bus_social_security_strategy social on social.id = wagestrat.social_security_strategyid and social.delete_flag <> '1'
WHERE 
baseuser.delete_flag <> '1' and userpersonnel.leave_date is null
and (
 (suser.gender = 0 and TIMESTAMPDIFF(YEAR,CONCAT(SUBSTRING(suser.idcard,7,4),'-',SUBSTRING(suser.idcard,11,2),'-',SUBSTRING(suser.idcard,13,2),' 00:00:00'),CONCAT(:toDate,' 00:00:00'))>=60)
 or
 (suser.gender = 1 and TIMESTAMPDIFF(YEAR,CONCAT(SUBSTRING(suser.idcard,7,4),'-',SUBSTRING(suser.idcard,11,2),'-',SUBSTRING(suser.idcard,13,2),' 00:00:00'),CONCAT(:toDate,' 00:00:00'))>=50)
)

<#if inDto.userName ?exists && inDto.userName ?length gt 0>
	AND baseuser.realname LIKE CONCAT('%', :inDto.userName ,'%')
</#if>
<#if inDto.orgIds ?exists && inDto.orgIds ?length gt 0>
	AND depart.id IN (${inDto.orgIds})
</#if>

<#if authSql ?exists && authSql ?length gt 0>
	${authSql}
</#if>

order by social.social_security_unit desc