<?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.judong.chuanyiserver.dao.UserDao">

    <!--通过账号名和密码查询用户-->
    <select id="getUserByNamePass" resultType="com.judong.chuanyiserver.entity.User">
        SELECT user_id,
               user_name,
               PASSWORD,
               user_type,
               create_time,
               last_login_time,
               state
        FROM sys_user
        WHERE user_name = #{userName}
          and password = #{password}
    </select>

    <select id="getUserById" resultType="com.judong.chuanyiserver.entity.User">
        SELECT user_id,
               user_name,
               PASSWORD,
               user_type,
               create_time,
               last_login_time,
               state
        FROM sys_user
        WHERE user_id = #{userId}
    </select>

    <select id="getUserByName" resultType="com.judong.chuanyiserver.entity.User">
        SELECT user_id,
               user_name,
               PASSWORD,
               user_type,
               create_time,
               last_login_time,
               state
        FROM sys_user
        WHERE user_name = #{userName}
    </select>

    <insert id="addUser">
        INSERT INTO sys_user (user_id, user_name, PASSWORD, user_type, create_time, state)
            VALUE (#{userId}, #{userName}, #{password}, #{userType}, now(), 0)
    </insert>

    <insert id="addUserRole">
        INSERT INTO sys_user_role (user_id, role_id)
            VALUE (#{userId}, #{roleId})
    </insert>

    <insert id="addUserRoleList">
        INSERT INTO sys_user_role (user_id, role_id) VALUES
        <foreach collection="roleList" item="role" index="index" separator=",">
            (#{userId},#{role.id})
        </foreach>
    </insert>

    <select id="getShareRoleList" resultType="com.judong.chuanyiserver.entity.Role">
        select sr.id, sr.role_name, sr.role_code
        from sys_role sr inner join
        ( SELECT role_id FROM sys_user_role where user_id=#{userId} and role_id in
        <foreach collection="roleList" item="role" index="index" open="(" close=")" separator=",">
            #{role.id}
        </foreach>) sur on sr.id=sur.role_id
    </select>

    <delete id="deleteUserRole">
        delete from sys_user_role where user_id=#{userId}
        AND role_id in (
        <foreach collection="roleList" item="role" index="index" separator=",">
            #{role.id}
        </foreach>)
    </delete>
    <delete id="deleteUserRoleByUserId">
        delete
        from sys_user_role
        where user_id = #{userId}
    </delete>

    <select id="getRoleListByUserId" resultType="com.judong.chuanyiserver.entity.Role">
        select sr.id, sr.role_name, sr.role_code
        from sys_role sr
                 inner join sys_user_role sur on sr.id = sur.role_id
        where sur.user_id = #{userId}
    </select>
    <select id="getUserCount" resultType="java.lang.Long">
        select count(*)
        from sys_user
        WHERE state != #{tombstoneUser}
        <if test="userName != null">
            AND user_name LIKE CONCAT('%', #{userName}, '%')
        </if>
        <if test="state != null">
            and state = #{state}
        </if>
        <if test="userType != null">
            and user_type = #{userType}
        </if>
    </select>
    <select id="getUserPage" resultType="com.judong.chuanyiserver.entity.User">
        select user_id, user_name, password, user_type, create_time, last_login_time, state
        from sys_user
        WHERE state != #{tombstoneUser}
        <if test="userName != null">
            AND user_name LIKE CONCAT('%', #{userName}, '%')
        </if>
        <if test="state != null">
            and state = #{state}
        </if>
        <if test="userType != null">
            and user_type = #{userType}
        </if>
        order by create_time DESC
        limit #{startNum},#{limitNum}
    </select>
    <select id="getNoShareOldRoleList" resultType="java.lang.Integer">
        select sr.id, sr.role_name, sr.role_code from sys_role sr inner join (
        SELECT role_id FROM sys_user_role WHERE user_id=#{userId} AND role_id NOT IN
        (
        <foreach collection="roleList" item="role" index="index" separator=",">
            #{role.id}
        </foreach>
        )) sur on sr.id=sur.role_id
    </select>

    <select id="getUserByNameNoId" resultType="com.judong.chuanyiserver.entity.User">
        select user_id, user_name, password, user_type, create_time, last_login_time, state
        from sys_user
        where user_id != #{userId}
          and user_name = #{userName}
    </select>

    <update id="updateUserState">
        update sys_user
        set state=#{userState}
        where user_id = #{userId}
    </update>
    <update id="updateLoginState">
        update sys_user
        set last_login_time=now()
        where user_id = #{userId}
    </update>
    <update id="updateUser">
        update sys_user
        set user_name=#{userName},
            state=#{state},
            user_type=#{userType}
        where user_id = #{userId}
    </update>

</mapper>