meidu2021/daju/target/classes/com/xcgl/weixin/sql/WXDao_getProjectUsers.sql

SELECT 
baseuser.id userid,
baseuser.username userCode,
baseuser.realname userName,
depart.id projectid,
depart.description projectname,
suser.openid openid,
group_concat(jzdepart.id) jgprojectids,
group_concat(jzdepart.description) jgprojectnames,
group_concat(role.rolecode) roles,
suser.mobilePhone mobilePhone,
sum( case  when IFNULL(file_url,0)=0  then 0 else 1 end) acccount,
case when sum( case  when IFNULL(file_url,0)=0  then 0 else 1 end) = 7 then 1 else 0 end completed,
post.post_name post
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_s_role_user urole on baseuser.id = urole.userid
left join t_s_role role on urole.roleid = role.id  
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_user_org AS userjzorg ON baseuser.id = userjzorg.user_id AND userjzorg.status = '0' AND userjzorg.ifpluralism = '1' 
LEFT JOIN t_s_depart AS depart ON userorg.org_id = depart.id AND depart.status <> '1'
LEFT JOIN t_s_depart AS jzdepart ON userjzorg.org_id = jzdepart.id AND jzdepart.status <> '1'
left join t_bus_accessory acc on acc.userid = baseuser.id 
left join t_sys_file file on acc.id = file.busi_id 
left join t_bus_post post on userpersonnel.in_postid = post.id
WHERE 
	baseuser.delete_flag <> '1'
	AND userpersonnel.leave_date is null
<#if projectid ?exists && projectid ?length gt 0>
	AND (depart.id = :projectid or depart.parentdepartid = :projectid )
</#if>
GROUP BY baseuser.id