<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.jeecg.modules.system.mapper.SysUserDepartMapper">
    <select id="getUserDepartByUid" parameterType="String" resultType="org.jeecg.modules.system.entity.SysUserDepart">
		SELECT *
		FROM sys_user_depart
		WHERE user_id = #{userId, jdbcType=VARCHAR}
    </select>

	<!-- 查询指定部门下的用户 并且支持用户账号模糊查询 -->
	<select id="queryDepartUserList" resultType="org.jeecg.modules.system.entity.SysUser">
		select a.* from sys_user a
		join sys_user_depart b on b.user_id = a.id
		join sys_depart c on b.dep_id = c.id
		<bind name="bindOrgCode" value="orgCode+'%'"/>
		where a.del_flag = 0 and c.org_code like #{bindOrgCode}
		<if test="realname!=null and realname!=''">
			<!-- update by sunjianlei 20220119【#3348】SQL injection exists in /sys/user/queryUserByDepId；  -->
			<bind name="bindRealname" value="'%'+realname+'%'"/>
			and a.realname like #{bindRealname}
		</if>
	</select>

	<!-- 根据部门查询部门用户 分页 -->
	<select id="queryDepartUserPageList" resultType="org.jeecg.modules.system.entity.SysUser">
		<!-- update by wangshuai 2024-07-03【issues/6342】部门人员选择组件出现人员重复-->
		select DISTINCT a.* from sys_user a
		left join sys_user_depart b on b.user_id = a.id
		left join sys_depart c on b.dep_id = c.id
		<bind name="bindOrgCode" value="orgCode+'%'"/>
		where a.del_flag = 0 and a.status = 1 and c.org_code like #{bindOrgCode} and a.username!='_reserve_user_external'
		<if test="username!=null and username!=''">
			<bind name="bindUsername" value="'%'+username+'%'"/>
			and a.username like #{bindUsername}
		</if>
		<if test="realname!=null and realname!=''">
		    <bind name="bindRealname" value="'%'+realname+'%'"/>
			and a.realname like #{bindRealname}
		</if>
		ORDER BY
		a.sort,a.create_time desc
	</select>

    <!--获取用户信息（聊天专用）-->
    <select id="getUserInformation" resultType="org.jeecg.modules.system.entity.SysUser">
        select DISTINCT a.* from sys_user a
        left join sys_user_depart b on b.user_id = a.id
        left join sys_depart c on b.dep_id = c.id
        <bind name="bindOrgCode" value="orgCode+'%'"/>
        where
          a.del_flag = 0 and a.status = 1
          and c.org_code like #{bindOrgCode}
          and a.username!='_reserve_user_external'
          and a.id !=#{userId}
        <if test="keyword!=null and keyword!=''">
            <bind name="bindKeyword" value="'%'+keyword+'%'"/>
            and (a.username like #{bindKeyword} or a.realname like #{bindKeyword})
        </if>
    </select>

	<!--获取用户信息（简版流程专用）-->
	<select id="getProcessUserList" resultType="org.jeecg.modules.system.entity.SysUser">
		select DISTINCT a.* from sys_user a
		left join sys_user_depart b on b.user_id = a.id
		left join sys_depart c on b.dep_id = c.id
		<bind name="bindOrgCode" value="orgCode+'%'"/>
		where
		a.del_flag = 0 and a.status = 1
		and c.org_code like #{bindOrgCode}
		and a.username!='_reserve_user_external'
		<if test="keyword!=null and keyword!=''">
			<bind name="bindKeyword" value="'%'+keyword+'%'"/>
			and (a.username like #{bindKeyword} or a.realname like #{bindKeyword})
		</if>
		<if test="tenantId!=null">
			and a.id in (
				select user_id from sys_user_tenant where tenant_id = #{tenantId} and status = '1'
			)
		</if>
		<!--【QQYUN-8239】用户角色，添加用户 返回2页数据，实际只显示一页 需要将不符合的用户id排除--> 
		<if test="excludeUserIdList!=null and excludeUserIdList.size()>0">
			and a.id not in
			<foreach collection="excludeUserIdList" item="userId" open="(" close=")" separator=",">
				#{userId}
			</foreach>
		</if>
		order by a.sort, a.create_time desc
	</select>
	
	<!--获取租户下的部门-通过前台传过来的部门id-->
    <select id="getTenantDepart" resultType="java.lang.String">
		select id from sys_depart
		where
		id in
		<foreach collection="departIds" item="departId" open="(" close=")" separator="," >
			#{departId}
		</foreach>
		and tenant_id = #{tenantId}
	</select>
	
	<!--根据当前租户和用户id查询用户部门数据-->
	<select id="getTenantUserDepart" resultType="org.jeecg.modules.system.entity.SysUserDepart">
		select * from sys_user_depart
		where
		user_id = #{userId}
		and dep_id in(
			select id from sys_depart where tenant_id = #{tenantId}
		)
	</select>

	<!--通过部门id和租户id获取用户-->
	<select id="getUsersByDepartTenantId" resultType="org.jeecg.modules.system.entity.SysUser">
		select su.id,su.realname from sys_user_depart sud
		INNER JOIN sys_user su ON sud.user_id = su.id
		INNER JOIN sys_user_tenant sut ON su.id = sut.user_id
		where
		sud.dep_id = #{departId}
		AND sut.tenant_id = #{tenantId}
		AND sut.status = '1'
	</select>

	<!--根据用户id和租户id,删除用户部门数据-->
    <delete id="deleteUserDepart">
		delete from sys_user_depart
		where
		user_id = #{userId}
		and dep_id in(
			select id from sys_depart where tenant_id = #{tenantId}
		)
	</delete>
    
    <!-- 通过用户id集合获取用户id和部门code -->
    <select id="getUserDepPostByUserIds" resultType="org.jeecg.modules.system.model.SysUserSysDepPostModel">
        select su.id,sd.org_code from sys_user_depart sud
        JOIN sys_user su ON sud.user_id = su.id
        JOIN sys_depart sd ON sud.dep_id = sd.id
        where
        sud.user_id in
        <foreach collection="userIdList" item="userId" open="(" close=")" separator=",">
            #{userId}
        </foreach>
    </select>
</mapper>