<?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.RoleDao">

    <insert id="addRole">
        INSERT INTO sys_role (role_name, role_code)
            VALUE (#{roleName}, #{roleCode})
    </insert>

    <insert id="addRoleMenuList">
        INSERT INTO sys_role_menu (role_id,menu_id) VALUES
        <foreach collection="menuList" item="menu" index="index" separator=",">
            (#{roleId},#{menu.id})
        </foreach>
    </insert>

    <update id="updateRole">
        update sys_role
        set role_name=#{roleName},
            role_code=#{roleCode}
        where id = #{id}
    </update>
    <update id="deleteRoleById">
        update sys_role
        set is_delete=#{publicIsDelete}
        where id = #{roleId}
    </update>

    <delete id="deleteRoleMenuByRoleId">
        delete
        from sys_role_menu
        where role_id = #{roleId}
    </delete>

    <delete id="deleteRoleMenu">
        delete from sys_role_menu where role_id=#{roleId}
        AND menu_id in (
        <foreach collection="menuList" item="menu" index="index" separator=",">
            #{menu.id}
        </foreach>)
    </delete>

    <select id="getRoleByName" resultType="com.judong.chuanyiserver.entity.Role">
        SELECT id,
               role_name,
               role_code
        FROM sys_role
        WHERE role_name = #{roleName}
    </select>

    <select id="getRoleByNoIdName" resultType="com.judong.chuanyiserver.entity.Role">
        select id, role_name
        from sys_role
        where id != #{id}
          and role_name = #{roleName}
    </select>

    <select id="getRoleCount" resultType="java.lang.Long">
        select count(*)
        from sys_role
        where is_delete=#{noDelete}
        <if test="roleName != null">
            AND role_name LIKE CONCAT('%', #{roleName}, '%')
        </if>
        <if test="roleCode != null">
            AND role_code LIKE CONCAT('%', #{roleCode}, '%')
        </if>
    </select>

    <select id="getRoleListByPage" resultType="com.judong.chuanyiserver.entity.Role">
        select id, role_name, role_code
        from sys_role
        where is_delete=#{noDelete}
        <if test="roleName != null">
            AND role_name LIKE CONCAT('%', #{roleName}, '%')
        </if>
        <if test="roleCode != null">
            AND role_code LIKE CONCAT('%', #{roleCode}, '%')
        </if>
        limit #{startNum},#{limitNum}
    </select>

    <select id="getRoleById" resultType="com.judong.chuanyiserver.entity.Role">
        select id, role_name
        from sys_role
        where id = #{id}
    </select>

    <select id="getMenuListByroleList" resultType="com.judong.chuanyiserver.entity.Menu">
        SELECT sm.id, sm.parent_id, sm.menu_name, sm.menu_url,sm.menu_icon, sm.sort_num, sm.menu_status, sm.create_time,
        sm.update_time FROM
        sys_menu sm INNER JOIN sys_role_menu srm ON sm.id=srm.menu_id where srm.role_id in (
        <foreach collection="roleList" item="role" index="index" separator=",">
            #{role.id}
        </foreach>
        )
    </select>

    <select id="getShareMenuList" resultType="com.judong.chuanyiserver.entity.Menu">
        select sm.id, sm.parent_id, sm.menu_name, sm.menu_url, sm.menu_icon, sm.sort_num, sm.menu_status,
        sm.create_time,
        sm.update_time
        from sys_menu sm inner join
        ( SELECT menu_id FROM sys_role_menu where role_id=#{roleId} and menu_id in
        <foreach collection="menuList" item="menu" index="index" open="(" close=")" separator=",">
            #{menu.id}
        </foreach>) srm on sm.id=srm.menu_id
    </select>

    <select id="getNoShareOldMenuList" resultType="com.judong.chuanyiserver.entity.Menu">
        select sm.id, sm.parent_id, sm.menu_name, sm.menu_url, sm.menu_icon, sm.sort_num, sm.menu_status,
        sm.create_time,
        sm.update_time from
        sys_menu sm inner join (
        SELECT menu_id FROM sys_role_menu WHERE role_id=#{roleId} AND menu_id NOT IN
        (
        <foreach collection="menuList" item="menu" index="index" separator=",">
            #{menu.id}
        </foreach>
        )) srm on sm.id=srm.menu_id
    </select>

    <select id="getMenuByRoleIdList" resultType="com.judong.chuanyiserver.entity.Menu">
        SELECT sm.id,
        sm.parent_id,
        sm.menu_name,
        sm.menu_url,
        sm.menu_icon,
        sm.sort_num,
        sm.menu_status,
        sm.create_time,
        sm.update_time
        FROM sys_menu sm inner join sys_role_menu srm on sm.id=srm.menu_id
        where sm.menu_status = #{menuAvailable}
        and srm.role_id in (
        <foreach collection="roleIdList" item="roleId" index="index" separator=",">
            #{roleId}
        </foreach>
        )
        order by sm.sort_num
    </select>

</mapper>