<?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="im.zhaojun.system.mapper.MenuMapper">

    <resultMap id="BaseResultMap" type="im.zhaojun.system.model.Menu">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        <id column="menu_id" jdbcType="INTEGER" property="menuId"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="menu_name" jdbcType="VARCHAR" property="menuName"/>
        <result column="url" jdbcType="VARCHAR" property="url"/>
        <result column="perms" jdbcType="VARCHAR" property="perms"/>
        <result column="order_num" jdbcType="INTEGER" property="orderNum"/>
        <result column="create_time" jdbcType="VARCHAR" property="createTime"/>
        <result column="modify_time" jdbcType="VARCHAR" property="modifyTime"/>
        <result column="icon" jdbcType="VARCHAR" property="icon"/>
    </resultMap>

    <resultMap id="TreeBaseResultMap" type="im.zhaojun.system.model.Menu">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        <id column="menu_id" jdbcType="INTEGER" property="menuId"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="menu_name" jdbcType="VARCHAR" property="menuName"/>
        <result column="url" jdbcType="VARCHAR" property="url"/>
        <result column="perms" jdbcType="VARCHAR" property="perms"/>
        <result column="order_num" jdbcType="INTEGER" property="orderNum"/>
        <result column="create_time" jdbcType="VARCHAR" property="createTime"/>
        <result column="modify_time" jdbcType="VARCHAR" property="modifyTime"/>
        <result column="icon" jdbcType="VARCHAR" property="icon"/>
        <collection property="children" ofType="im.zhaojun.system.model.Menu" select="selectAllTree" column="{parent_id = menu_id}"/>
    </resultMap>

    <resultMap id="TreeBaseResultMap2" type="im.zhaojun.system.model.Menu">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        <id column="menu_id" jdbcType="INTEGER" property="menuId"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="menu_name" jdbcType="VARCHAR" property="menuName"/>
        <result column="url" jdbcType="VARCHAR" property="url"/>
        <result column="perms" jdbcType="VARCHAR" property="perms"/>
        <result column="order_num" jdbcType="INTEGER" property="orderNum"/>
        <result column="create_time" jdbcType="VARCHAR" property="createTime"/>
        <result column="modify_time" jdbcType="VARCHAR" property="modifyTime"/>
        <result column="icon" jdbcType="VARCHAR" property="icon"/>
        <collection property="children" ofType="im.zhaojun.system.model.Menu" select="selectAllMenuAndCountOperator" column="{parent_id = menu_id}"/>
    </resultMap>

    <sql id="Base_Column_List">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
    </sql>

    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        select
        <include refid="Base_Column_List"/>
        from menu
        where menu_id = #{menuId,jdbcType=INTEGER}
    </select>

    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        delete from menu
        where menu_id = #{menuId,jdbcType=INTEGER}
    </delete>

    <insert id="insert" keyColumn="menu_id" keyProperty="menuId" parameterType="im.zhaojun.system.model.Menu"
            >
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        insert into menu (menu_id,parent_id, menu_name, url,
        perms, order_num, icon,create_time,modify_time
        )
        select max(menu_id)+1,#{parentId,jdbcType=INTEGER},#{menuName,jdbcType=VARCHAR},#{url,jdbcType=VARCHAR},#{perms,jdbcType=VARCHAR},#{orderNum},#{icon},#{createTime},#{modifyTime} from  menu
    </insert>

    <update id="updateByPrimaryKey" parameterType="im.zhaojun.system.model.Menu">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        update menu
        set parent_id = #{parentId,jdbcType=INTEGER},
        menu_name = #{menuName,jdbcType=VARCHAR},
        url = #{url,jdbcType=VARCHAR},
        perms = #{perms,jdbcType=VARCHAR},
        order_num = #{orderNum,jdbcType=INTEGER},
        icon = #{icon,jdbcType=VARCHAR},
        modify_time  = #{modifyTime ,jdbcType=VARCHAR}
        where menu_id = #{menuId,jdbcType=INTEGER}
    </update>

    <select id="selectAll" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List"/>
        from menu
        order by order_num
    </select>

    <select id="selectByParentId" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List"/>
        from menu
        <if test="_parameter != null">
            where parent_id = #{parentId}
        </if>
        order by order_num
    </select>

    <select id="selectAllMenuAndCountOperator" resultMap="TreeBaseResultMap2">
        select menu_id,
        parent_id,
        concat(menu_name, (select decode(count(*),
        0,
        '',
        concat(concat('(', count(*)), ')' ）) from
        operator where
        operator.menu_id = menu.menu_id)) as menu_name, url, perms, order_num, create_time, modify_time, icon
        from menu
        <where>
            <choose>
                <when test="parent_id != null">
                    and parent_id = #{parent_id}
                </when>
                <otherwise>
                    and parent_id = 0
                </otherwise>
            </choose>
        </where>
        order by order_num
    </select>

    <delete id="deleteByParentId" parameterType="java.lang.Integer">
        delete
        from menu
        where parent_id = #{parentId,jdbcType=INTEGER}
    </delete>

    <select id="selectChildrenIDByPrimaryKey" parameterType="java.lang.Integer" resultType="int">
        select menu_id
        from menu
        where parent_id = #{menuId, jdbcType=INTEGER}
    </select>

    <select id="selectMenuByUserName" resultMap="BaseResultMap">
        select distinct menu.menu_id,
                        menu.parent_id,
                        menu.menu_name,
                        menu.url,
                        menu.perms,
                        menu.order_num,
                        menu.icon
        from t_user t,
             role,
             user_role,
             menu,
             role_menu
        where t.user_id = user_role.user_id
          and role.role_id = user_role.role_id
          and role.role_id = role_menu.role_id
          and menu.menu_id = role_menu.menu_id
          and t.username = #{userName}
    </select>

    <select id="count" resultType="int">
        select count(*)
        from menu
    </select>

    <update id="swapSort">
        UPDATE
            menu AS per
                JOIN
                menu AS next
                ON
                        (per.`menu_id` = #{currentId} AND next.`menu_id` = #{swapId})
                        OR
                        (per.`menu_id` = #{swapId} AND next.`menu_id` = #{currentId})
        SET per.`order_num`  = next.`order_num`,
            next.`order_num` = per.`order_num`
    </update>

    <select id="selectMaxOrderNum" resultType="java.lang.Integer">
        select nvl(max(order_num), 0)
        from menu
    </select>


    <select id="selectAllTree" resultMap="TreeBaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from menu
        <where>
            <choose>
                <when test="parent_id != null">
                    and parent_id = #{parent_id}
                </when>
                <otherwise>
                    and parent_id = 0
                </otherwise>
            </choose>
        </where>
        order by order_num
    </select>

</mapper>