<?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.sz.admin.system.mapper.SysUserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.sz.admin.system.pojo.po.SysUser">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="pwd" property="pwd"/>
        <result column="phone" property="phone"/>
        <result column="nickname" property="nickname"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="logo" property="logo"/>
        <result column="age" property="age"/>
        <result column="id_card" property="idCard"/>
        <result column="email" property="email"/>
        <result column="account_status_cd" property="accountStatusCd"/>
        <result column="user_tag_cd" property="userTagCd"/>
        <result column="last_login_time" property="lastLoginTime"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="del_flag" property="delFlag"/>
        <result column="create_id" property="createId"/>
        <result column="update_id" property="updateId"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, username, pwd, phone, nickname, sex, birthday, logo, age, id_card, email, account_status_cd, user_tag_cd, last_login_time, create_time, update_time, del_flag, create_id, update_id
    </sql>

    <!--查询（部门）用户列表-->
    <select id="querySysUserListByDept" resultType="com.sz.admin.system.pojo.vo.sysuser.SysUserVO">
        SELECT su.*
        FROM sys_user su
        JOIN sys_user_dept sud ON su.id = sud.user_id
        JOIN sys_dept_closure sdc ON sud.dept_id = sdc.ancestor_id
        JOIN sys_dept sd ON sud.dept_id = sd.id
        WHERE su.del_flag = 'F'
        AND sd.del_flag = 'F'
        AND sdc.descendant_id IS NOT NULL
        <if test="deptId != null and deptId != -1 ">
            <choose>
                <when test="isThisDeep">
                    AND sud.dept_id = #{deptId}
                </when>
                <otherwise>
                    AND sud.dept_id IN (
                    SELECT descendant_id
                    FROM sys_dept_closure
                    WHERE ancestor_id = #{deptId}
                    )
                </otherwise>
            </choose>
        </if>
        <if test="nickname != null and nickname != ''">
            AND su.nickname LIKE CONCAT('%', #{nickname}, '%')
        </if>
        <if test="username != null and username != ''">
            AND su.username = #{username}
        </if>
        <if test="phone != null and phone != ''">
            AND su.phone = #{phone}
        </if>
        GROUP BY su.id
        ORDER BY su.create_time ASC
    </select>
    <!--查询（未分配部门）用户列表-->
    <select id="querySysUserListNotDept" resultType="com.sz.admin.system.pojo.vo.sysuser.SysUserVO">
        SELECT su.*
        FROM sys_user su
        LEFT JOIN sys_user_dept sud ON su.id = sud.user_id
        LEFT JOIN sys_dept_closure sdc ON sud.dept_id = sdc.ancestor_id
        LEFT JOIN sys_dept sd ON sud.dept_id = sd.id
        WHERE su.del_flag = 'F'
        AND (sdc.descendant_id IS NULL OR sd.del_flag = 'T')
        <if test="nickname != null and nickname != ''">
            AND su.nickname LIKE CONCAT('%', #{nickname}, '%')
        </if>
        <if test="username != null and username != ''">
            AND su.username = #{username}
        </if>
        <if test="phone != null and phone != ''">
            AND su.phone = #{phone}
        </if>
        GROUP BY su.id
        ORDER BY su.create_time ASC
    </select>
    <select id="queryAllSysUserList" resultType="com.sz.admin.system.pojo.vo.sysuser.SysUserVO">
        SELECT * FROM sys_user su
        <where>
            su.del_flag = 'F'
            <if test="nickname != null and nickname != ''">AND su.nickname like concat('%', #{nickname}, '%')</if>
            <if test="username != null and username != ''">AND su.username = #{username}</if>
            <if test="phone != null and phone != ''">AND su.phone = #{phone}</if>
        </where>
        ORDER BY su.create_time ASC
    </select>
    <select id="queryAllSysUserNameList" resultType="com.sz.admin.system.pojo.vo.sysuser.SysUserVO">
        SELECT username FROM sys_user su
        <where>
            su.del_flag = 'F'
            AND id IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </where>
        ORDER BY su.create_time ASC
    </select>
    <select id="countSysUserListNotDept" resultType="java.lang.Integer">
        SELECT
            COUNT(DISTINCT su.id  )
        FROM
            sys_user su
                LEFT JOIN sys_user_dept sud ON su.id = sud.user_id
                LEFT JOIN sys_dept_closure sdc ON sud.dept_id = sdc.ancestor_id
                LEFT JOIN sys_dept sd ON sud.dept_id = sd.id
        WHERE
            su.del_flag = 'F'
          AND ( sdc.descendant_id IS NULL OR sd.del_flag = 'T' )
    </select>
</mapper>
