<?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="com.zhyc.module.common.mapper.UserMapper">
    
    <resultMap type="User" id="UserResult">
        <result property="userId"      column="user_id"      />
        <result property="nickName"    column="nick_name"    />
        <result property="postName" column="post_name"/>
        <result property="postCode"    column="post_code"    />
    </resultMap>

    <select id="getUserListByCode" resultMap="UserResult">
        <choose>
            <!-- deptId = 0：查询所有用户，不限制部门 -->
            <when test="deptId == 0">
                SELECT
                MIN(u.user_id) as user_id,
                u.nick_name,
                MIN(p.post_name) as post_name,
                MIN(p.post_code) as post_code
                FROM sys_user u
                JOIN sys_user_post up ON u.user_id = up.user_id
                JOIN sys_post p ON up.post_id = p.post_id
                <where>
                    <if test="postCode!=null and postCode!=''">
                        AND p.post_code LIKE CONCAT('%', #{postCode}, '%')
                    </if>
                </where>
                AND u.status = 0
                AND u.del_flag = 0
                GROUP BY u.nick_name
            </when>

            <!-- deptId != 0：递归查询该部门及所有子部门 -->
            <otherwise>
                WITH RECURSIVE dept_tree AS (
                SELECT dept_id FROM sys_dept WHERE dept_id = #{deptId}
                UNION ALL
                SELECT d.dept_id
                FROM sys_dept d
                INNER JOIN dept_tree dt ON d.parent_id = dt.dept_id
                )
                SELECT
                MIN(u.user_id) as user_id,
                u.nick_name,
                MIN(p.post_name) as post_name,
                MIN(p.post_code) as post_code
                FROM dept_tree dt
                JOIN sys_user u ON u.dept_id = dt.dept_id
                JOIN sys_user_post up ON u.user_id = up.user_id
                JOIN sys_post p ON up.post_id = p.post_id
                <where>
                    <if test="postCode!=null and postCode!=''">
                        AND p.post_code LIKE CONCAT('%', #{postCode}, '%')
                    </if>
                </where>
                AND u.status = 0
                AND u.del_flag = 0
                GROUP BY u.nick_name
            </otherwise>
        </choose>
    </select>
</mapper>