<?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.backendsys.modules.system.dao.SysUserRoleDao">

    <sql id="includeUserRole">
        ur.id id,
        ur.id role_id,
        ur.role_name role_name,
        COALESCE(ur.role_description, '') role_description,
        ur.sort sort,
        ur.status status
    </sql>
    <resultMap id="resultMapUserRole" type="java.util.LinkedHashMap">
        <id property="id" column="id" jdbcType="BIGINT" />
        <result property="role_id" column="role_id" javaType="java.lang.Long"/>
        <result property="role_name" column="role_name" />
        <result property="role_description" column="role_description" />
        <result property="user_count" column="user_count" />
        <result property="sort" column="sort" javaType="java.lang.Integer" />
        <result property="status" column="status" javaType="java.lang.Integer" />
    </resultMap>
    <select id="selectUserRoleList" resultMap="resultMapUserRole">
        SELECT <include refid="includeUserRole" />, COUNT(us.id) user_count
        FROM sys_user_role ur
        LEFT JOIN sys_user_role_relation urr ON ur.id = urr.role_id
        LEFT JOIN sys_user us ON us.id = urr.user_id
        <where>
            <if test="role_name != null and role_name != ''">
                AND ur.role_name LIKE CONCAT('%', #{role_name}, '%')
            </if>
            <if test="status != null and status != ''">
                AND ur.status = #{status}
            </if>
        </where>
        GROUP BY ur.id, ur.role_name
        ORDER BY ur.sort ASC
    </select>

    <!-- 获得用户角色 (部分字段) -->
    <resultMap id="resultMapUserRoleSimple" type="java.util.LinkedHashMap">
        <result property="role_id" column="role_id" javaType="java.lang.Long" />
        <result property="role_name" column="role_name" />
    </resultMap>
    <select id="selectRoleByUserId" resultMap="resultMapUserRoleSimple">
        SELECT
            surr.role_id role_id,
            sur.role_name role_name
        FROM sys_user_role_relation surr
        LEFT JOIN sys_user_role sur ON sur.id = surr.role_id
        WHERE user_id = #{user_id}
    </select>


    <!-- 创建 用户角色 -->
    <insert id="insertUserRole" parameterType="com.backendsys.entity.System.SysUserRoleDTO" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO sys_user_role (role_name
            <if test="role_description != null">, role_description</if>
            <if test="sort != null">, sort</if>
            <if test="status != null">, status</if>
        ) VALUES (#{role_name}
            <if test="role_description != null">, #{role_description}</if>
            <if test="sort != null">, #{sort}</if>
            <if test="status != null">, #{status}</if>
        )
    </insert>
    <!-- 创建 用户角色 对应的 权限关系 -->
    <insert id="insertUserRolePermissionRelation" parameterType="com.backendsys.entity.System.SysUserRoleDTO">
        INSERT INTO sys_user_role_permission_relation (role_id, permission_id)
        VALUES
        <foreach collection="permission_ids" item="permission_id" separator=",">
            (#{id}, #{permission_id})
        </foreach>
    </insert>

    <!-- 编辑 用户角色 -->
    <update id="updateUserRole" parameterType="com.backendsys.entity.System.SysUserRoleDTO">
        UPDATE sys_user_role SET
        <trim suffixOverrides="," suffix=" ">
            <if test="role_name != null and role_name != ''">role_name = #{role_name},</if>
            <if test="role_description != null and role_description != ''">role_description = #{role_description},</if>
            <if test="sort != null and sort != ''">sort = #{sort},</if>
            <if test="status != null and status != ''">status = #{status},</if>
        </trim>
        WHERE id = #{role_id}
    </update>
    <!-- 编辑 用户角色 对应的 权限关系-->
    <update id="updateUserRolePermissionRelation" parameterType="com.backendsys.entity.System.SysUserRoleDTO">
        <!--删除符合条件的数据-->
        DELETE FROM sys_user_role_permission_relation WHERE role_id = #{role_id};
        <!--判断是否有新的数据需要插入-->
        <if test="permission_ids != null and permission_ids.size() > 0">
            INSERT INTO sys_user_role_permission_relation (role_id, permission_id)
            VALUES
            <foreach collection="permission_ids" item="permission_id" separator=",">
                (#{role_id}, #{permission_id})
            </foreach>
        </if>
    </update>

</mapper>