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

    <insert id="addMenu">
        INSERT INTO sys_menu (parent_id,
                              menu_name,
                              menu_url,
                              menu_icon,
                              sort_num,
                              menu_status,
                              create_time)
            VALUE (#{parentId}, #{menuName}, #{menuUrl}, #{menuIcon}, #{sortNum}, #{menuStatus}, now())
    </insert>

    <insert id="addMenuPermissionList">
        insert into sys_menu_permission (menu_id, permission_id) VALUES
        <foreach collection="permissionList" item="permission" index="index" separator=",">
            (#{menuId},#{permission.id})
        </foreach>
    </insert>

    <update id="updateMenu">
        update sys_menu
        set menu_name=#{menuName},
            menu_url=#{menuUrl},
            menu_icon=#{menuIcon},
            sort_num=#{sortNum},
            menu_status=#{menuStatus},
            update_time=now()
        where id = #{id}
    </update>
    <delete id="deleteMenuById">
        delete
        from sys_menu
        where id = #{id}
    </delete>
    <delete id="deleteRoleMenuById">
        delete
        from sys_role_menu
        where menu_id = #{menuId}
    </delete>

    <delete id="deleteMenuPermissionByMenuId">
        delete
        from sys_menu_permission
        where menu_id = #{menuId}
    </delete>

    <delete id="deleteMenuPermission">
        delete from sys_menu_permission where menu_id=#{menuId}
        and permission_id in(
        <foreach collection="permissionList" item="permission" index="index" separator=",">
            #{permission.id}
        </foreach>)
    </delete>

    <select id="getMenuByUrl" resultType="com.judong.chuanyiserver.entity.Menu">
        select id,
               parent_id,
               menu_name,
               menu_url,
               menu_icon,
               sort_num,
               menu_status,
               create_time,
               update_time
        from sys_menu
        where menu_url = #{menuUrl}
    </select>

    <select id="getMenuByNoIdUrl" resultType="com.judong.chuanyiserver.entity.Menu">
        select id,
               parent_id,
               menu_name,
               menu_url,
               menu_icon,
               sort_num,
               menu_status,
               create_time,
               update_time
        from sys_menu
        where id != #{id}
          and menu_url = #{menuUrl}
    </select>

    <select id="getMenuCount" resultType="java.lang.Long">
        select count(*)
        from sys_menu
        where 1=1
        <if test="menuName != null">
            AND menu_name LIKE CONCAT('%', #{menuName}, '%')
        </if>
        <if test="menuUrl != null">
            AND menu_url LIKE CONCAT('%', #{menuUrl}, '%')
        </if>
        <if test="menuUrl != null">
            AND menu_url=#{menuStatus}
        </if>
    </select>

    <select id="getMenuListByPage" resultType="com.judong.chuanyiserver.entity.Menu">
        select id,
               parent_id,
               menu_name,
               menu_url,
               menu_icon,
               sort_num,
               menu_status,
               create_time,
               update_time
        from sys_menu
        where 1=1
        <if test="menuName != null">
            AND menu_name LIKE CONCAT('%', #{menuName}, '%')
        </if>
        <if test="menuUrl != null">
            AND menu_url LIKE CONCAT('%', #{menuUrl}, '%')
        </if>
        <if test="menuUrl != null">
            AND menu_url=#{menuStatus}
        </if>
        limit #{startNum},#{limitNum}
    </select>
    <select id="getMenuById" resultType="com.judong.chuanyiserver.entity.Menu">
        select id,
               parent_id,
               menu_name,
               menu_url,
               menu_icon,
               sort_num,
               menu_status,
               create_time,
               update_time
        from sys_menu
        where id = #{id}
    </select>

    <select id="getMenuByName" resultType="com.judong.chuanyiserver.entity.Menu">
        select id,
               parent_id,
               menu_name,
               menu_url,
               menu_icon,
               sort_num,
               menu_status,
               create_time,
               update_time
        from sys_menu
        where menu_name = #{menuName}
    </select>

    <select id="getMenuByNoIdName" resultType="com.judong.chuanyiserver.entity.Menu">
        select id,
               parent_id,
               menu_name,
               menu_url,
               menu_icon,
               sort_num,
               menu_status,
               create_time,
               update_time
        from sys_menu
        where id != #{id}
          and menu_name = #{menuName}
    </select>

    <select id="getPermissionListByMenuList" resultType="com.judong.chuanyiserver.entity.Permission">
        select sp.id, sp.permission_url, sp.permission_name
        from sys_permission sp
        inner join sys_menu_permission smp on sp.id = smp.permission_id
        where smp.menu_id in (
        <foreach collection="menuList" item="menu" index="index" separator=",">
            #{menu.id}
        </foreach>
        )
    </select>

    <select id="getSharePermissionList" resultType="com.judong.chuanyiserver.entity.Permission">
        select sp.id, sp.permission_url, sp.permission_name
        from sys_permission sp
        inner join (select permission_id
        from sys_menu_permission
        where menu_id = #{menuId} and permission_id in
        <foreach collection="permissionList" item="permission" index="index" open="(" close=")" separator=",">
            #{permission.id}
        </foreach>) smp on sp.id=smp.permission_id
    </select>

    <select id="getNoShareOldPermissionList" resultType="com.judong.chuanyiserver.entity.Permission">
        select sp.id, permission_url, permission_name
        from sys_permission sp
        inner join (
        select permission_id from sys_menu_permission where menu_id=#{menuId} and permission_id not in(
        <foreach collection="permissionList" item="permission" index="index" separator=",">
            #{permission.id}
        </foreach>
        )) smp on sp.id = smp.permission_id
    </select>

    <select id="getAllMenu" resultType="com.judong.chuanyiserver.entity.Menu">
        select id,
               parent_id,
               menu_name,
               menu_url,
               menu_icon,
               sort_num,
               menu_status,
               create_time,
               update_time
        from sys_menu
        where menu_status = #{menuAvailable}
    </select>

</mapper>