<?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.manage.dao.Power_MenuMapper">
  <resultMap id="BaseResultMap" type="com.manage.entity.Power_Menu">
    <id column="menu_id" jdbcType="INTEGER" property="menuId" />
    <result column="menu_name" jdbcType="VARCHAR" property="menuName" />
    <result column="menu_icon" jdbcType="VARCHAR" property="menuIcon" />
    <result column="menu_url" jdbcType="VARCHAR" property="menuUrl" />
    <result column="menu_desc" jdbcType="VARCHAR" property="menuDesc" />
    <result column="method" jdbcType="VARCHAR" property="method" />
    <result column="function_type" jdbcType="VARCHAR" property="functionType" />
    <result column="method_type" jdbcType="VARCHAR" property="methodType" />
    <result column="sys_id" jdbcType="INTEGER" property="sysId" />
    <result column="sys_flag" jdbcType="VARCHAR" property="sysFlag" />
    <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    <result column="sort" jdbcType="INTEGER" property="sort" />
    <result column="effective" jdbcType="INTEGER" property="effective" />
    <result column="create_date" jdbcType="CHAR" property="createDate" />
    <result column="creater" jdbcType="VARCHAR" property="creater" />
    <result column="update_date" jdbcType="CHAR" property="updateDate" />
    <result column="updater" jdbcType="VARCHAR" property="updater" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
  </resultMap>


  <resultMap id="ResultMap" type="com.manage.entity.Power_Menu">
    <id column="menu_id" jdbcType="INTEGER" property="menuId" />
    <result column="menu_name" jdbcType="VARCHAR" property="menuName" />
    <result column="menu_icon" jdbcType="VARCHAR" property="menuIcon" />
    <result column="menu_url" jdbcType="VARCHAR" property="menuUrl" />
    <result column="menu_desc" jdbcType="VARCHAR" property="menuDesc" />
    <result column="method" jdbcType="VARCHAR" property="method" />
    <result column="method_type" jdbcType="VARCHAR" property="methodType" />
    <result column="sys_id" jdbcType="INTEGER" property="sysId" />
    <result column="sys_flag" jdbcType="VARCHAR" property="sysFlag" />
    <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    <result column="sort" jdbcType="INTEGER" property="sort" />
    <result column="effective" jdbcType="INTEGER" property="effective" />
    <result column="create_date" jdbcType="CHAR" property="createDate" />
    <result column="creater" jdbcType="VARCHAR" property="creater" />
    <result column="update_date" jdbcType="CHAR" property="updateDate" />
    <result column="updater" jdbcType="VARCHAR" property="updater" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />

    <result column="flag" jdbcType="INTEGER" property="flag" />
  </resultMap>

  <sql id="Base_Column_List">
    menu_id, menu_name, menu_icon, menu_url, menu_desc, method, function_type,method_type, sys_id, sys_flag,
    parent_id, sort,effective,create_date, creater, update_date, updater, remark
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from power_menu
    where menu_id = #{menuId,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from power_menu
    where menu_id = #{menuId,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.manage.entity.Power_Menu">
    insert into power_menu (menu_id, menu_name, menu_icon, 
      menu_url, menu_desc, method, function_type,
      method_type, sys_id, sys_flag,parent_id,
      sort,effective,create_date, creater,
      update_date, updater, remark
      )
    values (#{menuId,jdbcType=INTEGER}, #{menuName,jdbcType=VARCHAR}, #{menuIcon,jdbcType=VARCHAR}, 
      #{menuUrl,jdbcType=VARCHAR}, #{menuDesc,jdbcType=VARCHAR}, #{method,jdbcType=VARCHAR},#{functionType,jdbcType=VARCHAR},
      #{methodType,jdbcType=VARCHAR}, #{sysId,jdbcType=INTEGER},#{sysFlag,jdbcType=VARCHAR}, #{parentId,jdbcType=INTEGER},
      #{sort,jdbcType=INTEGER},#{effective,jdbcType=INTEGER}, #{createDate,jdbcType=CHAR}, #{creater,jdbcType=VARCHAR},
      #{updateDate,jdbcType=CHAR}, #{updater,jdbcType=VARCHAR}, #{remark,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.manage.entity.Power_Menu">
    insert into power_menu
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="menuId != null">
        menu_id,
      </if>
      <if test="menuName != null">
        menu_name,
      </if>
      <if test="menuIcon != null">
        menu_icon,
      </if>
      <if test="menuUrl != null">
        menu_url,
      </if>
      <if test="menuDesc != null">
        menu_desc,
      </if>
      <if test="method != null">
        method,
      </if>
      <if test="functionType != null">
        function_type,
      </if>
      <if test="methodType != null">
        method_type,
      </if>
      <if test="sysId != null">
        sys_id,
      </if>
      <if test="sysFlag != null">
        sys_flag,
      </if>
      <if test="parentId != null">
        parent_id,
      </if>
      <if test="sort != null">
        sort,
      </if>
      <if test="effective != null">
        effective,
      </if>
      <if test="createDate != null">
        create_date,
      </if>
      <if test="creater != null">
        creater,
      </if>
      <if test="updateDate != null">
        update_date,
      </if>
      <if test="updater != null">
        updater,
      </if>
      <if test="remark != null">
        remark,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="menuId != null">
        #{menuId,jdbcType=INTEGER},
      </if>
      <if test="menuName != null">
        #{menuName,jdbcType=VARCHAR},
      </if>
      <if test="menuIcon != null">
        #{menuIcon,jdbcType=VARCHAR},
      </if>
      <if test="menuUrl != null">
        #{menuUrl,jdbcType=VARCHAR},
      </if>
      <if test="menuDesc != null">
        #{menuDesc,jdbcType=VARCHAR},
      </if>
      <if test="method != null">
        #{method,jdbcType=VARCHAR},
      </if>
      <if test="functionType != null">
        #{functionType,jdbcType=VARCHAR},
      </if>
      <if test="methodType != null">
        #{methodType,jdbcType=VARCHAR},
      </if>
      <if test="sysId != null">
        #{sysId,jdbcType=INTEGER},
      </if>
      <if test="sysFlag != null">
        #{sysFlag,jdbcType=VARCHAR},
      </if>
      <if test="parentId != null">
        #{parentId,jdbcType=INTEGER},
      </if>
      <if test="sort != null">
        #{sort,jdbcType=INTEGER},
      </if>
      <if test="effective != null">
        #{effective,jdbcType=INTEGER},
      </if>
      <if test="createDate != null">
        #{createDate,jdbcType=CHAR},
      </if>
      <if test="creater != null">
        #{creater,jdbcType=VARCHAR},
      </if>
      <if test="updateDate != null">
        #{updateDate,jdbcType=CHAR},
      </if>
      <if test="updater != null">
        #{updater,jdbcType=VARCHAR},
      </if>
      <if test="remark != null">
        #{remark,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.manage.entity.Power_Menu">
    update power_menu
    <set>
      <if test="menuName != null">
        menu_name = #{menuName,jdbcType=VARCHAR},
      </if>
      <if test="menuIcon != null">
        menu_icon = #{menuIcon,jdbcType=VARCHAR},
      </if>
      <if test="menuUrl != null">
        menu_url = #{menuUrl,jdbcType=VARCHAR},
      </if>
      <if test="menuDesc != null">
        menu_desc = #{menuDesc,jdbcType=VARCHAR},
      </if>
      <if test="method != null">
        method = #{method,jdbcType=VARCHAR},
      </if>
      <if test="methodType != null">
        method_type = #{methodType,jdbcType=VARCHAR},
      </if>
      <if test="functionType != null">
        function_type = #{functionType,jdbcType=VARCHAR},
      </if>
      <if test="sysId != null">
        sys_id = #{sysId,jdbcType=INTEGER},
      </if>
      <if test="sysFlag != null">
        sys_flag = #{sysFlag,jdbcType=VARCHAR},
      </if>
      <if test="parentId != null">
        parent_id = #{parentId,jdbcType=INTEGER},
      </if>
      <if test="sort != null">
        sort = #{sort,jdbcType=INTEGER},
      </if>
      <if test="effective != null">
        effective = #{effective,jdbcType=INTEGER},
      </if>
      <if test="createDate != null">
        create_date = #{createDate,jdbcType=CHAR},
      </if>
      <if test="creater != null">
        creater = #{creater,jdbcType=VARCHAR},
      </if>
      <if test="updateDate != null">
        update_date = #{updateDate,jdbcType=CHAR},
      </if>
      <if test="updater != null">
        updater = #{updater,jdbcType=VARCHAR},
      </if>
      <if test="remark != null">
        remark = #{remark,jdbcType=VARCHAR},
      </if>
    </set>
    where menu_id = #{menuId,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.manage.entity.Power_Menu">
    update power_menu
    set menu_name = #{menuName,jdbcType=VARCHAR},
      menu_icon = #{menuIcon,jdbcType=VARCHAR},
      menu_url = #{menuUrl,jdbcType=VARCHAR},
      menu_desc = #{menuDesc,jdbcType=VARCHAR},
      method = #{method,jdbcType=VARCHAR},
      function_type = #{functionType,jdbcType=VARCHAR},
      method_type = #{methodType,jdbcType=VARCHAR},
      sys_id = #{sysId,jdbcType=INTEGER},
      sys_flag = #{sysFlag,jdbcType=VARCHAR},
      parent_id = #{parentId,jdbcType=INTEGER},
      sort = #{sort,jdbcType=INTEGER},
      effective = #{effective,jdbcType=INTEGER},
      create_date = #{createDate,jdbcType=CHAR},
      creater = #{creater,jdbcType=VARCHAR},
      update_date = #{updateDate,jdbcType=CHAR},
      updater = #{updater,jdbcType=VARCHAR},
      remark = #{remark,jdbcType=VARCHAR}
    where menu_id = #{menuId,jdbcType=INTEGER}
  </update>

  <select id="queryAllPowerMenu" resultMap="BaseResultMap">
    select DISTINCT sys_id,menu_id,menu_name,menu_icon,method,menu_url,parent_id,sort,sys_flag
    from power_menu
    where effective=1 and parent_id != 0
    <if test="roleId != null and roleId != 0">
      AND menu_name != '菜单管理'
      AND menu_name != '归属管理'
      AND menu_name != '自定义模板'
    </if>
    <if test="sysFlag != '' and sysFlag != null">
      and sys_flag=#{sysFlag}
    </if>
    order by sys_id,menu_id,sort
  </select>
  <!--<select id="queryMenuViewByUserId" parameterType="java.lang.Integer"
          resultType="com.manage.entity.Power_Detailed_Menu">
      select null as dict_id,null as hospital_name,m.sys_id,null as sys_flag,null as sys_name,null as sysParent,cc.menu_id, m.menu_name,m.menu_icon,m.menu_url,m.method,m.parent_id,m.sort as menu_sort from(
      select u.user_id,u2.role_id,u.menu_id,u.flag
      from power_user_menu u
      left join power_user u2
      on u2.user_id=u.user_id
      where u2.role_id!=0 and u2.role_id!=-100
      union
      select u2.user_id ,r.role_id,r.menu_id,0 as flag from power_role_menu r
      left join power_user u2
      on u2.role_id=r.role_id
      where u2.role_id!=0 and u2.role_id!=-100
      ) cc
      left join power_menu m
      on m.menu_id=cc.menu_id
      where m.effective=1 and cc.user_id=#{userId,jdbcType=INTEGER}
    union
      select d.dict_id,d.hospital_name,null as sys_id,d.sys_flag,d.sys_name,d.parent_id as sysParent,null as menu_id,null as menu_name,null as menu_icon,null as menu_url,null as method,null as parent_id,null as menu_sort
      from power_sys_dict d
      where d.dict_status=1
      and d.dict_id=(select dict_id from power_dept where dept_id =(select substring_index(dept_id,',', 1) from power_user where effective=1 and user_id=#{userId,jdbcType=INTEGER}))
      or (d.parent_id=(select dict_id from power_dept where dept_id =(select substring_index(dept_id,',', 1) from power_user where effective=1 and user_id=#{userId,jdbcType=INTEGER}))
       and d.sys_flag='power')
  </select>-->
  <select id="queryPoswerMenuByUserId" parameterType="java.lang.Integer"  resultType="com.manage.entity.Power_Menu_User">
    SELECT
        distinct
        sys_id dict_id,
        hospital_name,
        sys_flag,
        sys_name,
        hospital_id AS sys_parent,
        menu_id,
        menu_name,
        menu_icon,
        menu_url,
        method,
        sort menu_sort,
        method_parent parent_id
    FROM
        user_dept_menu
    WHERE
        user_id = #{userId,jdbcType=INTEGER}
    AND menu_name != '菜单管理'
    AND menu_name != '归属管理'
    AND menu_name != '自定义模板'
    AND method_parent != 0
    <if test="sysFlag != '' and sysFlag != null">
      and sys_flag=#{sysFlag}
    </if>
    order by sys_id,menu_id,sort
 </select>
  <!--/*    select * from(
      select d.dict_id,d.hospital_name,d.sys_flag,d.sys_name,d.parent_id as sys_parent ,null as menu_id,null as menu_name,null as menu_icon,null as menu_url,null as method,null as sys_id,null as parent_id,null as menu_sort
      from power_sys_dict d
      where d.dict_status=1
      union
      select d.dict_id,d.hospital_name,d.sys_flag,d.sys_name,d.parent_id as sys_parent,m.menu_id,m.menu_name,m.menu_icon,m.menu_url,m.method,m.sys_id,m.parent_id,m.sort as menu_sort
      from power_sys_dict d
      left join power_menu m
      on m.sys_id=d.dict_id
      where d.dict_status=1
    ) cc
    where dict_id=(select dict_id from power_dept where dept_id =(select substring_index(dept_id,',', 1) from power_user where effective=1 and user_id=#{userId,jdbcType=INTEGER}))
    or (sys_parent=(select dict_id from power_dept where dept_id =(select substring_index(dept_id,',', 1) from power_user where effective=1 and user_id=#{userId,jdbcType=INTEGER}))
    and sys_flag='power')
*/ -->
  <!--2019-04-29zengwh-->
  <!--查询全部-->
  <select id="selectAll"  resultMap="BaseResultMap" parameterType="string">
    SELECT
      *,
      menu_name newName
    FROM power_menu
    <where>
      <if test="sysFlag != null and sysFlag != ''">
        sys_flag = #{sysFlag}
      </if>
      <if test="isEffective == 1">
        AND effective = 1
      </if>
      <if test="roleId != null and roleId != 0">
        AND menu_name != '菜单管理'
        AND menu_name != '归属管理'
        AND menu_name != '自定义模板'
      </if>
    </where>
    ORDER BY parent_id,sort
  </select>
  <!--2019-04-29zengwh-->
  <!--根据菜单id查询功能方法集合-->
  <select id="selectMethodByMenuIdPowerTree"  resultMap="BaseResultMap" parameterType="java.lang.Integer">
    SELECT
        *
    FROM
        power_menu
    WHERE
        parent_id = ${parentId}
    AND effective = 1
    AND method IS NOT NULL
    ORDER BY
        sort
  </select>





  <!--2019-06-28 ljx-->
  <!--根据系统id和角色id查出角色菜单-->
  <select id="selectRoleMenuByUserIdAndDictId"  resultMap="BaseResultMap" parameterType="java.lang.Integer">
    SELECT
	   a.*
    FROM
        power_menu a,
        power_role_menu b
    WHERE
        a.sys_id = ${dictId}
    AND b.role_id in (SELECT role_id FROM power_user WHERE user_id = ${userId} and effective = 1)
    AND a.menu_id = b.menu_id
  </select>

  <!--2019-06-28 ljx-->
  <!--根据系统id和角色id查出用户菜单-->
  <select id="selectUserMenuByUserIdAndDictId"  resultMap="ResultMap" parameterType="java.lang.Integer">
    SELECT
	   a.*,b.flag
    FROM
        power_menu a,
        power_user_menu b
    WHERE
        a.sys_id = ${dictId}
    AND b.user_id = ${userId}
    AND a.menu_id = b.menu_id
  </select>

  <!--验证同一系统菜单名不能重复-->
  <select id="checkMenuNameBySysId"  resultMap="ResultMap" parameterType="java.lang.String">
    SELECT
        menu_id
    FROM
        power_menu
    WHERE
        sys_flag = #{sysFlag}
    AND menu_name = #{menuName}
  </select>
  <!--验证同一菜单功能名或功能方法不能重复-->
  <select id="checkMethodByParentId"  resultMap="ResultMap" parameterType="java.lang.Integer">
    SELECT
        menu_id,sys_id,sys_flag
    FROM
        power_menu
    WHERE
        parent_id = ${parentId}
        <if test="menuName != null and menuName != ''">
            AND menu_name = #{menuName}
        </if>
        <if test="method != null and method != ''">
            AND method = #{method}
        </if>
  </select>
  <!--根据系统id和用户id查出对应该系统在用户菜单表和角色菜单表的综合表-->
  <select id="selectUserAndRoleMenuListPower" parameterType="string" resultMap="BaseResultMap">
    SELECT DISTINCT power_menu.menu_id, menu_name, menu_icon, menu_url, menu_desc, method, function_type,method_type, sys_id,
    sys_flag,
    parent_id, sort,effective,create_date, creater, update_date, updater, remark FROM power_menu
    INNER JOIN (
    SELECT
    m.menu_id
    FROM
    power_menu m
    WHERE
    m.menu_id IN (
    SELECT
    r.menu_id
    FROM
    power_role_menu r
    WHERE
    r.role_id = (
    SELECT
    u.role_id
    FROM
    power_user u
    WHERE
    u.user_id = #{userId}
    )
    AND r.menu_id NOT IN (
    SELECT
    m.menu_id
    FROM
    power_menu m
    WHERE
    m.menu_id IN (
    SELECT
    pu.menu_id
    FROM
    power_user_menu pu
    WHERE
    pu.user_id = #{userId}
    AND pu.flag = 0
    )
    <if test="sysFlag != null and sysFlag != ''">AND m.sys_flag = #{sysFlag}
    </if>
        )
    )
    <if test="sysFlag != null and sysFlag != ''">
      AND m.sys_flag = #{sysFlag}
    </if>
    UNION ALL
    SELECT
    m.menu_id
    FROM
    power_menu
    m
    WHERE
    m.menu_id IN (
    SELECT
    pu.menu_id menuId1
    FROM
    power_user_menu pu
    WHERE
    pu.user_id = #{userId}
    AND pu.flag = 1
    )
    <if test="sysFlag != null and sysFlag != ''">
           AND  m.sys_flag = #{sysFlag}
        </if>) a
        on a.menu_id = power_menu.menu_id AND power_menu.effective=1 and power_menu.parent_id != 0
        order by power_menu.sort
  </select>
  <update id="updateChildSysFlagAndSysId">
      update power_menu set sys_id = #{sysId},sys_flag = #{sysFlag} where parent_id = #{menuId}
  </update>

</mapper>