<?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.ruoyi.system.user.mapper.UserMapper">

    <resultMap type="UserVO" id="SysUserResult" autoMapping="true">
        <id property="userId" column="user_id"/>
        <result property="deptName" column="dept_name"/>
        <association property="dept" javaType="Dept" resultMap="deptResult"/>
        <collection property="roles" javaType="java.util.List" column="user_id" select="selectUserRoleByUserId"/>
    </resultMap>

    <resultMap id="deptResult" type="Dept" autoMapping="true">
        <id property="deptId" column="dept_id"/>
        <result property="parentId" column="parent_id"/>
        <result property="deptName" column="dept_name"/>
        <result property="ancestors" column="ancestors"/>
        <result property="orderNum" column="order_num"/>
        <result property="leader" column="leader"/>
        <result property="status" column="dept_status"/>
    </resultMap>

    <resultMap id="RoleResult" type="Role" autoMapping="true">
        <id property="roleId" column="role_id"/>
        <result property="roleName" column="role_name"/>
        <result property="roleKey" column="role_key"/>
        <result property="roleSort" column="role_sort"/>
        <result property="dataScope" column="data_scope"/>
        <result property="status" column="role_status"/>
    </resultMap>

    <select id="selectUserRoleByUserId" resultMap="RoleResult">
        select r.role_id, r.role_name, r.role_key, r.role_sort, r.data_scope, r.status as role_status
        from sys_user_role ur
                 left join sys_role r on ur.role_id = r.role_id
        where ur.user_id = #{user_id}
    </select>

    <sql id="selectUserVo">
        select
        u.user_id,
        u.dept_id,
        u.user_name,
        u.nick_name,
        u.user_type,
        u.email,
        u.avatar,
        u.phonenumber,
        u.password,
        u.sex,
        u.status,
        u.del_flag,
        u.login_ip,
        u.login_date,
        u.create_by,
        u.create_time,
        u.remark,
        u.is_real,
        u.real_name,
        u.real_id_card,
        u.real_phone,
        u.balance,
        u.withdraw_service_type,
        u.withdraw_service_value,
        u.bonus_delay,
        u.declaration,
        d.dept_id,
        d.parent_id,
        d.ancestors,
        d.dept_name,
        d.order_num,
        d.leader,
        d.status as dept_status,
        mch.user_name as mch_name,
        mch.nick_name as mch_nick_name,
        mch.user_id as mch_id
        <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        from sys_user u
			left join sys_dept d on u.dept_id = d.dept_id
			left join bst_order bo on u.user_id = bo.user_id
			left join bst_store bs on bo.store_id = bs.store_id
			left join sys_user mch on bs.user_id = mch.user_id
    </sql>


    <sql id="searchCondition">
        and u.del_flag = '0'
        <if test="withdrawServiceType != null  and withdrawServiceType != ''">and u.withdraw_service_type =
            #{withdrawServiceType}
        </if>
        <if test="areaId != null">and u.area_id = #{areaId}</if>
        <if test="userId != null and userId != 0">
            AND u.user_id = #{userId}
        </if>
        <if test="userName != null and userName != ''">
            AND u.user_name like concat('%', #{userName}, '%')
        </if>
        <if test="status != null and status != ''">
            AND u.status = #{status}
        </if>
        <if test="phonenumber != null and phonenumber != ''">
            AND u.phonenumber like concat('%', #{phonenumber}, '%')
        </if>
        <if test="eqPhonenumber != null and eqPhonenumber != ''">
            AND u.phonenumber = #{eqPhonenumber}
        </if>
        <if test="email != null and email != ''">
            AND u.email like concat('%', #{email}, '%')
        </if>
        <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
            AND date_format(u.create_time,'%Y%m%d') &gt;= date_format(#{params.beginTime},'%Y%m%d')
        </if>
        <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
            AND date_format(u.create_time,'%Y%m%d') &lt;= date_format(#{params.endTime},'%Y%m%d')
        </if>
        <if test="deptId != null and deptId != 0">
            AND (u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(#{deptId},
            ancestors) ))
        </if>
        <if test="excludeUserId != null">
            and u.user_id != #{excludeUserId}
        </if>
        <if test="isReal != null">
            and u.is_real = #{isReal}
        </if>
        <if test="userType != null and userType != ''">
            and u.user_type = #{userType}
        </if>
        <if test="realName != null and realName != ''">
            and u.real_name like concat('%', #{realName}, '%')
        </if>
        <if test="realIdCard != null and realIdCard != ''">
            and u.real_id_card like concat('%', #{realIdCard}, '%')
        </if>
        <if test="realPhone != null and realPhone != ''">
            and u.real_phone like concat('%', #{realPhone}, '%')
        </if>
        <if test="appId != null">
            and u.user_id in (
            select user_id from bst_user_app where app_id = #{appId}
            )
        </if>
        <if test="eqOpenId != null and eqOpenId != ''">
            and u.user_id in (
            select user_id from bst_user_app where open_id = #{eqOpenId}
            )
        </if>
        <if test="roleKey != null and roleKey != ''">
            and u.user_id in (
            select user_id from sys_user_role where role_id in (select role_id from sys_role where role_key =
            #{roleKey})
            )
        </if>
        <if test="userIds != null and userIds.size() > 0">
            AND u.user_id IN
            <foreach collection="userIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="userNames != null and userNames.size() > 0">
            AND u.user_name IN
            <foreach collection="userNames" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="nickNames != null and nickNames.size() > 0">
            AND u.nick_name IN
            <foreach collection="nickNames" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="excludeUserIds != null and excludeUserIds.size() > 0">
            AND u.user_id not in
            <foreach collection="excludeUserIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="deptIds != null and deptIds.size() > 0">
            AND u.dept_id IN
            <foreach collection="deptIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="createDate != null">
            AND date(u.create_time) = #{createDate}
        </if>
        <if test="createDateRange != null and createDateRange.size() > 0">
            AND date(u.create_time) &gt;= #{createDateRange[0]}
            AND date(u.create_time) &lt;= #{createDateRange[1]}
        </if>
        ${params.dataScope}
    </sql>

    <select id="selectUserList" parameterType="UserQuery" resultMap="SysUserResult">
        <include refid="selectUserVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectAllocatedList" parameterType="User" resultMap="SysUserResult">
        select distinct u.user_id, u.dept_id, u.user_name, u.email, u.phonenumber, u.status, u.create_time
        from sys_user u
        left join sys_dept d on u.dept_id = d.dept_id
        left join sys_user_role ur on u.user_id = ur.user_id
        left join sys_role r on r.role_id = ur.role_id
        where u.del_flag = '0' and r.role_id = #{roleId}
        <if test="userName != null and userName != ''">
            AND u.user_name like concat('%', #{userName}, '%')
        </if>
        <if test="phonenumber != null and phonenumber != ''">
            AND u.phonenumber like concat('%', #{phonenumber}, '%')
        </if>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
    </select>

    <select id="selectUnallocatedList" parameterType="User" resultMap="SysUserResult">
        select distinct u.user_id, u.dept_id, u.user_name, u.email, u.phonenumber, u.status, u.create_time
        from sys_user u
        left join sys_dept d on u.dept_id = d.dept_id
        left join sys_user_role ur on u.user_id = ur.user_id
        left join sys_role r on r.role_id = ur.role_id
        where u.del_flag = '0' and (r.role_id != #{roleId} or r.role_id IS NULL)
        and u.user_id not in (select u.user_id from sys_user u inner join sys_user_role ur on u.user_id = ur.user_id and
        ur.role_id = #{roleId})
        <if test="userName != null and userName != ''">
            AND u.user_name like concat('%', #{userName}, '%')
        </if>
        <if test="phonenumber != null and phonenumber != ''">
            AND u.phonenumber like concat('%', #{phonenumber}, '%')
        </if>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
    </select>

    <select id="selectUserByUserName" parameterType="String" resultMap="SysUserResult">
        <include refid="selectUserVo"/>
        where u.user_name = #{userName} and u.del_flag = '0'
    </select>

    <select id="selectUserById" parameterType="Long" resultMap="SysUserResult">
        <include refid="selectUserVo"/>
        where u.user_id = #{userId}
    </select>

    <select id="checkUserNameUnique" parameterType="String" resultMap="SysUserResult">
        select user_id, user_name
        from sys_user
        where user_name = #{userName}
          and del_flag = '0' limit 1
    </select>

    <select id="checkPhoneUnique" parameterType="String" resultMap="SysUserResult">
        select user_id, phonenumber
        from sys_user
        where phonenumber = #{phonenumber}
          and del_flag = '0' limit 1
    </select>

    <select id="checkEmailUnique" parameterType="String" resultMap="SysUserResult">
        select user_id, email
        from sys_user
        where email = #{email}
          and del_flag = '0' limit 1
    </select>

    <select id="selectSimpleById" resultMap="SysUserResult">
        select *
        from sys_user
        where user_id = #{userId}
          and del_flag = '0'
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select count(u.user_id)
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="userId">
        insert into sys_user(
        <if test="userId != null and userId != 0">user_id,</if>
        <if test="deptId != null and deptId != 0">dept_id,</if>
        <if test="userName != null and userName != ''">user_name,</if>
        <if test="nickName != null and nickName != ''">nick_name,</if>
        <if test="userType != null and userType != ''">user_type,</if>
        <if test="email != null and email != ''">email,</if>
        <if test="avatar != null and avatar != ''">avatar,</if>
        <if test="phonenumber != null and phonenumber != ''">phonenumber,</if>
        <if test="sex != null and sex != ''">sex,</if>
        <if test="password != null and password != ''">password,</if>
        <if test="status != null and status != ''">status,</if>
        <if test="createBy != null and createBy != ''">create_by,</if>
        <if test="remark != null and remark != ''">remark,</if>
        <if test="isReal != null">is_real,</if>
        <if test="realName != null and realName != ''">real_name,</if>
        <if test="realIdCard != null and realIdCard != ''">real_id_card,</if>
        <if test="realPhone != null and realPhone != ''">real_phone,</if>
        <if test="withdrawServiceType != null">withdraw_service_type,</if>
        <if test="withdrawServiceValue != null">withdraw_service_value,</if>
        <if test="areaId != null">area_id,</if>
        <if test="bonusDelay != null">bonus_delay,</if>
        create_time
        )values(
        <if test="userId != null and userId != ''">#{userId},</if>
        <if test="deptId != null and deptId != ''">#{deptId},</if>
        <if test="userName != null and userName != ''">#{userName},</if>
        <if test="nickName != null and nickName != ''">#{nickName},</if>
        <if test="userType != null and userType != ''">#{userType},</if>
        <if test="email != null and email != ''">#{email},</if>
        <if test="avatar != null and avatar != ''">#{avatar},</if>
        <if test="phonenumber != null and phonenumber != ''">#{phonenumber},</if>
        <if test="sex != null and sex != ''">#{sex},</if>
        <if test="password != null and password != ''">#{password},</if>
        <if test="status != null and status != ''">#{status},</if>
        <if test="createBy != null and createBy != ''">#{createBy},</if>
        <if test="remark != null and remark != ''">#{remark},</if>
        <if test="isReal != null">#{isReal},</if>
        <if test="realName != null and realName != ''">#{realName},</if>
        <if test="realIdCard != null and realIdCard != ''">#{realIdCard},</if>
        <if test="realPhone != null and realPhone != ''">#{realPhone},</if>
        <if test="withdrawServiceType != null">#{withdrawServiceType},</if>
        <if test="withdrawServiceValue != null">#{withdrawServiceValue},</if>
        <if test="areaId != null">#{areaId},</if>
        <if test="bonusDelay != null">#{bonusDelay},</if>
        sysdate()
        )
    </insert>

    <update id="updateUser" parameterType="User">
        update sys_user
        <set>
            <if test="point != null">point = #{point},</if>
            <if test="deptId != null and deptId != 0">dept_id = #{deptId},</if>
            <if test="userName != null and userName != ''">user_name = #{userName},</if>
            <if test="nickName != null and nickName != ''">nick_name = #{nickName},</if>
            <if test="userType != null and userType != ''">user_type = #{userType},</if>
            <if test="email != null ">email = #{email},</if>
            <if test="declaration != null ">declaration = #{declaration},</if>
            <if test="phonenumber != null ">phonenumber = #{phonenumber},</if>
            <if test="sex != null and sex != ''">sex = #{sex},</if>
            <if test="avatar != null and avatar != ''">avatar = #{avatar},</if>
            <if test="password != null and password != ''">`password` = #{password},</if>
            <if test="declaration != null and declaration != ''">`declaration` = #{declaration},</if>
            <if test="status != null and status != ''">`status` = #{status},</if>
            <if test="loginIp != null and loginIp != ''">login_ip = #{loginIp},</if>
            <if test="loginDate != null">login_date = #{loginDate},</if>
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="isReal != null">is_real = #{isReal},</if>
            <if test="realName != null and realName != ''">real_name = #{realName},</if>
            <if test="realIdCard != null and realIdCard != ''">real_id_card = #{realIdCard},</if>
            <if test="realPhone != null and realPhone != ''">real_phone = #{realPhone},</if>
            <if test="withdrawServiceType != null">withdraw_service_type = #{withdrawServiceType},</if>
            <if test="withdrawServiceValue != null">withdraw_service_value = #{withdrawServiceValue},</if>
            <if test="areaId != null">area_id = #{areaId},</if>
            <if test="bonusDelay != null">bonus_delay = #{bonusDelay},</if>
            <if test="declaration != null">declaration = #{declaration},</if>
            update_time = sysdate()
        </set>
        where user_id = #{userId}
    </update>

    <update id="updateUserStatus" parameterType="User">
        update sys_user
        set status = #{status}
        where user_id = #{userId}
    </update>

    <update id="updateUserAvatar" parameterType="User">
        update sys_user
        set avatar = #{avatar}
        where user_name = #{userName}
    </update>

    <update id="resetUserPwd" parameterType="User">
        update sys_user
        set password = #{password}
        where user_name = #{userName}
    </update>

    <delete id="deleteUserById" parameterType="Long">
        update sys_user
        set del_flag = '2'
        where user_id = #{userId}
    </delete>

    <delete id="deleteUserByIds" parameterType="Long">
        update sys_user set del_flag = '2' where user_id in
        <foreach collection="array" item="userId" open="(" separator="," close=")">
            #{userId}
        </foreach>
    </delete>

    <!-- selectNameList -->

    <select id="selectNameList" resultType="UserNameVO">
        select
        u.user_id,
        u.nick_name,
        u.avatar
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectUserIds" resultType="Long">
        select u.user_id
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>


    <select id="selectBalanceForUpdate" resultType="BigDecimal">
        select u.balance
        from sys_user u
        where u.user_id = #{userId}
            for update
    </select>

    <!-- addBalance -->

    <update id="addBalance">
        update sys_user u
        set u.balance = u.balance + #{amount}
        where u.user_id = #{userId}
          and u.del_flag = '0'
    </update>

    <!-- subtractBalance -->

    <update id="subtractBalance">
        update sys_user u
        set u.balance = u.balance - #{amount}
        where u.user_id = #{userId}
        and u.del_flag = '0'
        <if test="checkBalance">
            and u.balance >= #{amount}
        </if>
    </update>

    <!-- selectIdList -->

    <select id="selectIdList" resultType="Long">
        select u.user_id
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- selectSumOfBalance -->

    <select id="selectSumOfBalance">
        select sum(u.balance)
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!--selectUserIdsByStoreId-->
    <select id="selectUserIdsByStoreId" resultType="com.ruoyi.system.user.domain.vo.UserConsumeVO">
        select su.user_id,
               su.nick_name,
               su.phonenumber,
               su.status,
               su.create_time
        from sys_user su
        where su.user_id in (select distinct user_id
                             from bst_order bo
                             where bo.store_id = #{query.storeId})
    </select>

</mapper>
