<?xml version="1.0" encoding="UTF-8"?>
<!--
  ~
  ~      Copyright (c) 2018-2025, avenue All rights reserved.
  ~
  ~  Redistribution and use in source and binary forms, with or without
  ~  modification, are permitted provided that the following conditions are met:
  ~
  ~ Redistributions of source code must retain the above copyright notice,
  ~  this list of conditions and the following disclaimer.
  ~  Redistributions in binary form must reproduce the above copyright
  ~  notice, this list of conditions and the following disclaimer in the
  ~  documentation and/or other materials provided with the distribution.
  ~  Neither the name of the pig4cloud.com developer nor the names of its
  ~  contributors may be used to endorse or promote products derived from
  ~  this software without specific prior written permission.
  ~  Author: avenue
  ~
  -->

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.emergency.avenue.admin.mapper.SysUserMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="baseResultMap" type="com.emergency.avenue.admin.api.vo.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="ucreate_time" property="createTime"/>
        <result column="uupdate_time" property="updateTime"/>
        <result column="lock_flag" property="lockFlag"/>
		<result column="del_flag" property="delFlag"/>
        <result column="dept_id" property="deptId"/>
        <result column="tenant_id" property="tenantId"/>
        <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.emergency.avenue.admin.api.entity.SysRole"
                    select="com.emergency.avenue.admin.mapper.SysRoleMapper.listRolesByUserId" column="user_id">
        </collection>
        <collection property="postList" ofType="com.emergency.avenue.admin.api.entity.SysPost"
                    select="com.emergency.avenue.admin.mapper.SysPostMapper.listPostsByUserId" column="user_id">
        </collection>
    </resultMap>

    <!-- userVo结果集 -->
    <resultMap id="userVoResultMap" type="com.emergency.avenue.admin.api.vo.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="ucreate_time" property="createTime"/>
        <result column="uupdate_time" property="updateTime"/>
        <result column="lock_flag" property="lockFlag"/>
        <result column="del_flag" property="delFlag"/>
        <result column="dept_id" property="deptId"/>
        <result column="tenant_id" property="tenantId"/>
        <result column="dept_name" property="deptName"/>
        <collection property="roleList" ofType="com.emergency.avenue.admin.api.entity.SysRole">
            <id column="role_id" property="roleId"/>
            <result column="role_name" property="roleName"/>
            <result column="role_code" property="roleCode"/>
            <result column="role_desc" property="roleDesc"/>
            <result column="rcreate_time" property="createTime"/>
            <result column="rupdate_time" property="updateTime"/>
        </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.tenant_id,
		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.del_flag,
		u.lock_flag,
		u.tenant_id,
        u.nickname,
        u.name,
        u.email,
		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,
		d.name  dept_name,
		d.dept_id
    </sql>

    <select id="getUserVoByUsername" resultMap="userVoResultMap">
        SELECT
        <include refid="userRoleSql"/>
        FROM
        sys_user u
        LEFT JOIN sys_user_role ur1 ON ur1.user_id = u.user_id
        LEFT JOIN sys_role r ON r.role_id = ur1.role_id
        WHERE u.username = #{username}
    </select>

    <select id="getUserVoById" resultMap="userVoResultMap">
        SELECT
        <include refid="userRoleDeptSql"/>
        FROM
        sys_user u
        LEFT JOIN sys_user_role ur1 ON ur1.user_id = u.user_id
        LEFT JOIN sys_role r ON r.role_id = ur1.role_id
        LEFT JOIN sys_dept d ON d.dept_id = u.dept_id
        WHERE
        u.user_id = #{id}
    </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_time ucreate_time,
        u.update_time uupdate_time,
        u.del_flag,
        u.lock_flag,
        u.tenant_id,
        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' and d.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.delFlag != null and query.delFlag != ''">
				AND u.del_flag = #{query.delFlag}
			</if>
			<if test="query.phone != null and query.phone != ''">
				<bind name="phone" value="'%'+query.phone+'%'"/>
				AND u.phone LIKE #{phone}
			</if>
        </where>

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

    <select id="selectVoListByScope" 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_time ucreate_time,
        u.update_time uupdate_time,
        u.del_flag,
        u.lock_flag,
        u.tenant_id,
        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>

    <select id="getListUserByDeptId" resultType="com.emergency.avenue.admin.api.vo.UserVO">
		select t_user.user_id,t_user.username,t_user.dept_Id,t_user.phone
		from sys_user t_user
		where t_user.dept_Id = #{deptId} and `t_user`.del_flag = 0
	</select>
	<select id="getListUserIdByDeptIdAndRoleCode" resultType="java.lang.Long">
		select sys_user.user_id
		from sys_user
		join sys_user_role sur
		on sys_user.user_id = sur.user_id
		and sys_user.del_flag = '0'
		and sys_user.dept_id  in
		<foreach collection="deptIds" separator="," item="deptId" open="(" close=")">
			#{deptId}
		</foreach>
		and sur.role_id in (select sys_role.role_id from sys_role where sys_role.role_code
		in
		<foreach collection="roles" separator="," item="roleCode" open="(" close=")">
			#{roleCode}
		</foreach>
		)
	</select>
</mapper>
