<?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.kxs.system.biz.mapper.SysUserMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="baseResultMap" type="com.kxs.system.api.vo.admin.UserVO">
        <id column="user_id" property="userId"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="salt" property="salt"/>
        <result column="phone" property="phone"/>
        <result column="avatar" property="avatar"/>
        <result column="wx_openid" property="wxOpenid"/>
        <result column="qq_openid" property="qqOpenid"/>
        <result column="gitee_login" property="giteeOpenId"/>
        <result column="osc_id" property="oscOpenId"/>
        <result column="ucreate_time" property="createTime"/>
        <result column="uupdate_time" property="updateTime"/>
        <result column="lock_flag" property="lockFlag"/>
        <result column="udel_flag" property="delFlag"/>
        <result column="dept_id" property="deptId"/>
        <result column="dept_name" property="deptName"/>
        <result column="nickname" property="nickname"/>
        <result column="name" property="name"/>
        <result column="email" property="email"/>
        <collection property="roleList" ofType="com.kxs.system.api.model.SysRole"
                    select="com.kxs.system.biz.mapper.SysRoleMapper.listRolesByUserId" column="user_id">
        </collection>
        <collection property="postList" ofType="com.kxs.system.api.model.SysPost"
                    select="com.kxs.system.biz.mapper.SysPostMapper.listPostsByUserId" column="user_id">
        </collection>
    </resultMap>

    <sql id="userRoleSql">
        u.user_id,
        u.username,
        u.password,
        u.salt,
        u.phone,
        u.avatar,
        u.wx_openid,
        u.qq_openid,
        u.dept_id,
        u.del_flag,
        u.lock_flag,
        u.create_by,
        u.create_time  ucreate_time,
        u.update_time  uupdate_time,
        r.role_id,
        r.role_name,
        r.role_code,
        r.role_desc,
        r.create_time  rcreate_time,
        r.update_time  rupdate_time
    </sql>

    <sql id="userRoleDeptSql">
        u.user_id,
        u.username,
        u.password,
        u.salt,
        u.phone,
        u.avatar,
        u.wx_openid,
        u.qq_openid,
        u.gitee_login,
        u.osc_id,
        u.del_flag,
        u.lock_flag,
        u.nickname,
        u.name,
        u.email,
        u.create_by,
        u.create_time  ucreate_time,
        u.update_time  uupdate_time,
        d.name  dept_name,
        d.dept_id
    </sql>

    <select id="getUserVoByUsername" resultMap="baseResultMap">
        SELECT
        <include refid="userRoleSql"/>
        FROM
        sys_user u
        LEFT JOIN sys_user_role urole ON urole.user_id = u.user_id
        LEFT JOIN sys_role r ON r.role_id = urole.role_id and r.del_flag = '0'
        WHERE u.username = #{username} and u.del_flag = '0'
    </select>

    <select id="getUserVoById" resultMap="baseResultMap">
        SELECT
        <include refid="userRoleDeptSql"/>
        FROM
        sys_user u
        LEFT JOIN sys_dept d ON d.dept_id = u.dept_id and d.del_flag = '0'
        WHERE
        u.user_id = #{id} and u.del_flag = '0'
    </select>

    <select id="getUserVosPage" resultMap="baseResultMap">
        SELECT
        u.user_id,
        u.username,
        u.password,
        u.salt,
        u.phone,
        u.avatar,
        u.wx_openid,
        u.qq_openid,
        u.dept_id,
        u.create_by,
        u.create_time ucreate_time,
        u.update_time uupdate_time,
        u.del_flag,
        u.lock_flag,
        u.nickname,
        u.name,
        u.email,
        d.name dept_name
        FROM
        sys_user u
        LEFT JOIN sys_dept d ON d.dept_id = u.dept_id
        <where>
            u.del_flag = '0'
            <if test="query.username != null and query.username != ''">
                <bind name="usernameLike" value="'%'+query.username+'%'"/>
                AND u.username LIKE #{usernameLike}
            </if>
            <if test="query.deptId != null and query.deptId != ''">
                AND u.dept_id = #{query.deptId}
            </if>
            <if test="query.phone != null and query.phone != ''">
                <bind name="phoneLike" value="'%'+query.phone+'%'"/>
                AND u.phone LIKE #{phoneLike}
            </if>
        </where>

        <if test="_databaseId != 'mssql'">
            ORDER BY u.create_time DESC
        </if>
    </select>

    <select id="selectVoList" resultMap="baseResultMap">
        SELECT
        u.user_id,
        u.username,
        u.password,
        u.salt,
        u.phone,
        u.avatar,
        u.wx_openid,
        u.qq_openid,
        u.dept_id,
        u.create_by,
        u.create_time ucreate_time,
        u.update_time uupdate_time,
        u.del_flag,
        u.lock_flag,
        u.nickname,
        u.name,
        u.email,
        d.name dept_name
        FROM
        sys_user u
        LEFT JOIN sys_dept d ON d.dept_id = u.dept_id
        <where>
            u.del_flag = '0'
            <if test="query.username != null and query.username != ''">
                <bind name="usernameLike" value="'%'+query.username+'%'"/>
                AND u.username LIKE #{usernameLike}
            </if>
            <if test="query.deptId != null and query.deptId != ''">
                AND u.dept_id = #{query.deptId}
            </if>
        </where>

        <if test="_databaseId != 'mssql'">
            ORDER BY u.create_time DESC
        </if>
    </select>
</mapper>
