<?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">
<!-- 不使用namespace的话sql搜索定位会比较方便 -->
<mapper namespace="com.blueland.dao.MenuDao">
	<resultMap id="RM_MenuEntity" type="com.blueland.bo.MenuEntity">
        <result property="menuId" column="MENU_ID"/>
        <result property="appId" column="APP_ID"/>
        <result property="menuParentId" column="MENU_PARENT_ID"/>
        <result property="menuCode" column="MENU_CODE"/>
        <result property="menuName" column="MENU_NAME"/>
        <result property="menuType" column="MENU_TYPE"/>
        <result property="menuIcon" column="MENU_ICON"/>
        <result property="menuUrl" column="MENU_URL"/>
        <result property="sort" column="SORT"/>
        <result property="remark" column="REMARK"/>
        <result property="status" column="STATUS"/>
        <result property="creatorId" column="CREATOR_ID"/>
        <result property="createDate" column="CREATE_DATE"/>
        <result property="updatorId" column="UPDATOR_ID"/>
        <result property="updateDate" column="UPDATE_DATE"/>
        <result property="menuLevel" column="MENU_LEVEL"/>
        <result property="openType" column="OPEN_TYPE"/>
        <result property="pageCode" column="PAGE_CODE"/>
        <result property="openStatus" column="OPEN_STATUS"/>
	</resultMap>

	<sql id="MenuEntityColumns">
        	MENU_ID,
        	APP_ID,
        	MENU_PARENT_ID,
        	MENU_CODE,
        	MENU_NAME,
        	MENU_TYPE,
        	MENU_ICON,
        	MENU_URL,
        	SORT,
        	REMARK,
        	STATUS,
        	CREATOR_ID,
        	CREATE_DATE,
        	UPDATOR_ID,
        	UPDATE_DATE,
        	MENU_LEVEL,
        	OPEN_TYPE,
        	PAGE_CODE,
        	OPEN_STATUS
	</sql>
	
	<sql id="MenuEntityQueryColumns">
        	MENU_ID,
        	APP_ID,
        	MENU_PARENT_ID,
        	MENU_CODE,
        	MENU_NAME,
        	MENU_TYPE,
        	MENU_ICON,
        	MENU_URL,
        	SORT,
        	REMARK,
        	STATUS,
        	CREATOR_ID,
        	CREATE_DATE,
        	UPDATOR_ID,
        	UPDATE_DATE,
        	MENU_LEVEL,
        	OPEN_TYPE,
        	PAGE_CODE,
        	OPEN_STATUS
	</sql>
	<!-- useGeneratedKeys="true" keyProperty="xxx" for sqlserver and mysql -->
	<insert id="insert" useGeneratedKeys="false" keyProperty="menuId">
        INSERT INTO t_pf_menu (<include refid="MenuEntityColumns"/>) VALUES (
        	#{menuId,jdbcType=VARCHAR},
        	#{appId,jdbcType=VARCHAR},
        	#{menuParentId,jdbcType=VARCHAR},
        	#{menuCode,jdbcType=VARCHAR},
        	#{menuName,jdbcType=VARCHAR},
        	#{menuType,jdbcType=VARCHAR},
        	#{menuIcon,jdbcType=VARCHAR},
        	#{menuUrl,jdbcType=VARCHAR},
        	#{sort,jdbcType=INTEGER},
        	#{remark,jdbcType=VARCHAR},
        	#{status,jdbcType=VARCHAR},
        	#{creatorId,jdbcType=VARCHAR},
		    #{createDate,jdbcType=TIMESTAMP},
        	#{updatorId,jdbcType=VARCHAR},
		    #{updateDate,jdbcType=TIMESTAMP},
        	#{menuLevel,jdbcType=INTEGER},
        	#{openType,jdbcType=VARCHAR},
        	#{pageCode,jdbcType=VARCHAR},
        	#{openStatus,jdbcType=VARCHAR}
        )
	</insert>

    <select id="get" resultMap="RM_MenuEntity">
		SELECT <include refid="MenuEntityQueryColumns"/>
	    FROM t_pf_menu 
        WHERE 
	        MENU_ID = #{menuId,jdbcType=VARCHAR} 
	</select>
	
	
	
	<select id="getMenuLevel" resultType="Integer">
		select menu_level from t_pf_menu where menu_id=#{menuParentId}
	</select>
	
	
	<select id="findAppIdMenuList" resultMap="RM_MenuEntity">
        SELECT T1.MENU_ID,T1.APP_ID,T1.MENU_PARENT_ID,T1.MENU_CODE,T1.MENU_NAME,T1.MENU_TYPE,T1.MENU_ICON,T1.MENU_URL,
        	   T1.SORT,T1.REMARK,T1.STATUS,T1.CREATOR_ID,T1.CREATE_DATE,T1.UPDATOR_ID,T1.UPDATE_DATE,T1.MENU_LEVEL,
        	   T1.OPEN_TYPE,T1.PAGE_CODE,T1.OPEN_STATUS
		FROM t_pf_menu T1
		WHERE T1.APP_ID=#{appId} and T1.MENU_PARENT_ID = #{parentId}
		ORDER BY T1.SORT
	</select>
	
	<select id="listRoleMenu" resultMap="RM_MenuEntity">
      		select
	t1.menu_id,
	t1.app_id,
	t1.menu_parent_id,
	t1.menu_code,
	t1.menu_name,
	t1.menu_type,
	t1.menu_icon,
	t1.menu_url,
	t1.sort,
	t1.remark,
	t1. status,
	t1.creator_id,
	t1.create_date,
	t1.updator_id,
	t1.update_date,
	t1.menu_level,
	t1.open_type,
	t1.page_code,
	t1.open_status,
	t2.menu_name as parent_name
from
	t_pf_menu t1
left join t_pf_menu t2 on (
	t1.menu_parent_id = t2.menu_id
)
where
	t1.app_id =#{appId}
and t1. status = 'y'
and t1.menu_id != '1'
and t1.menu_level='1'
order by
	t1.sort
	</select>
	
	<select id="getParent" resultMap="RM_MenuEntity">
      		select t1.menu_id,t1.app_id,t1.menu_parent_id,t1.menu_code,t1.menu_name,t1.menu_type,t1.menu_icon,t1.menu_url,t1.sort,t1.remark,
		t1.status,t1.creator_id,t1.create_date,t1.updator_id,t1.update_date,t1.menu_level,t1.open_type,t1.page_code,t1.open_status,t2.menu_name as parent_name 
from t_pf_menu t1
		left join t_pf_menu t2 on (t1.menu_parent_id=t2.menu_id)
where t1.menu_parent_id=#{menuId} and t1.status='Y' and t1.app_id=#{appId}
       order by t1.sort
	</select>
	
	<select id="getParentRole" resultMap="RM_MenuEntity">
      		select t1.menu_id,t1.app_id,t1.menu_parent_id,t1.menu_code,t1.menu_name,t1.menu_type,t1.menu_icon,t1.menu_url,t1.sort,t1.remark,
		t1.status,t1.creator_id,t1.create_date,t1.updator_id,t1.update_date,t1.menu_level,t1.open_type,t1.page_code,t1.open_status,t2.menu_name as parent_name 
from t_pf_menu t1
		left join t_pf_menu t2 on (t1.menu_parent_id=t2.menu_id)
		left join t_pf_auth t3 on (
t1.menu_id = t3.menu_id
)
where t1.menu_parent_id=#{menuId} and t1.status='Y' and t1.app_id=#{appId} 
and t3.role_id=#{roleId}
       order by t1.sort
	</select>
	
	<select id="getMenuTop" resultMap="RM_MenuEntity">
      	select t1.menu_id,t1.app_id,t1.menu_parent_id,t1.menu_code,t1.menu_name,t1.menu_type,t1.menu_icon,t1.menu_url,t1.sort,t1.remark,
		t1.status,t1.creator_id,t1.create_date,t1.updator_id,t1.update_date,t1.menu_level,t1.open_type,t1.page_code,t1.open_status,t2.menu_name as parent_name 
		from t_pf_menu t1
		left join t_pf_menu t2 on (t1.menu_parent_id=t2.menu_id)
		where t1.app_id=#{appId} and t1.status='Y' and t1.menu_parent_id=''
       order by t1.sort
	</select>
	<select id="getMenuNoTop" resultMap="RM_MenuEntity">
     	select t1.menu_id,t1.app_id,t1.menu_parent_id,t1.menu_code,t1.menu_name,t1.menu_type,t1.menu_icon,t1.menu_url,t1.sort,t1.remark,
		t1.status,t1.creator_id,t1.create_date,t1.updator_id,t1.update_date,t1.menu_level,t1.open_type,t1.page_code,t1.open_status,t2.menu_name as parent_name 
		from t_pf_menu t1
				left join t_pf_menu t2 on (t1.menu_parent_id=t2.menu_id)
				
		where t1.app_id=#{appId} and t1.status='Y' 
		<if test="@Ognl@isNotBlank(menuName)">
		          and t1.menu_name like CONCAT('%',#{menuName,jdbcType=VARCHAR},'%')
		</if>
		<if test="@Ognl@isNotBlank(parentName)">
		          and t2.menu_name like  CONCAT('%',#{parentName,jdbcType=VARCHAR},'%')
		</if>
		<if test="@Ognl@isNotBlank(menuType)">
		          and t1.menu_type = #{menuType}
		</if>
       order by t1.sort
	</select>
	
	
		<select id="getMenuAll" resultMap="RM_MenuEntity">
         	select t1.menu_id,t1.app_id,t1.menu_parent_id,t1.menu_code,t1.menu_name,t1.menu_type,t1.menu_icon,t1.menu_url,t1.sort,t1.remark,
		t1.status,t1.creator_id,t1.create_date,t1.updator_id,t1.update_date,t1.menu_level,t1.open_type,t1.page_code,t1.open_status,t2.menu_name as parent_name 
from t_pf_menu t1
		left join t_pf_menu t2 on (t1.menu_parent_id=t2.menu_id)
		
where t1.app_id=#{appId} and t1.status='Y'
<if test="@Ognl@isNotBlank(menuName)">
          and t1.menu_name like CONCAT('%',#{menuName,jdbcType=VARCHAR},'%')
</if>
<if test="@Ognl@isNotBlank(parentName)">
          and t2.menu_name like  CONCAT('%',#{parentName,jdbcType=VARCHAR},'%')
</if>
<if test="@Ognl@isNotBlank(menuType)">
          and t1.menu_type = #{menuType}
</if>

       order by t1.sort
	</select>
	
	<select id="getMenu" resultMap="RM_MenuEntity">
    select t2.menu_id ,t2.menu_name ,t2.menu_parent_id ,t2.sort ,t2.menu_url ,t2.menu_icon ,t2.menu_type ,    t2.menu_code ,
       t2.open_type ,
       t2.open_status ,
        t2.status ,
          t2.app_id  from t_pf_auth t1,t_pf_menu t2 where t1.status = 'y' and t2.status = 'y' 
          and t1.app_id=#{appId}
          and t1.role_id in (
	     			select role.role_id
	                  from t_pf_role      role,
	                       t_uc_staff       u,
	                       t_pf_role2user ra,
	                       t_pf_application       app
	                 where role.role_id = ra.role_id
	                   and u.staff_code = ra.user_id
	                   and role.role_id = ra.role_id
	                   and role.app_id = app.app_id
	                   and role.app_id = ra.app_id
	                   and ra.status = 'y'
	                   and app.app_status = 'QY'
	                   and role.status = 'y'
	                   and role.app_id = #{appId}
	                   and ra.user_id = #{userId}
	    ) and t2.menu_id = t1.menu_id 
	    <if test="@Ognl@isNotBlank(menuType)"> and t2.menu_type = #{menuType} </if>
	    <if test="@Ognl@isNotBlank(menuId)">  and (t2.menu_parent_id =  #{menuId} or t2.menu_id=  #{menuId}) </if>
	    group by t2.menu_id,t2.menu_name,t2.menu_parent_id,t2.sort,t2.menu_url,t2.menu_icon,t2.menu_type,t2.menu_code,
          t2.open_type,
          t2.open_status,
          t2.status,
          t2.app_id
	    order by t2.sort
	</select>
	
	<select id="getMenuCode" resultMap="RM_MenuEntity">
    select t2.menu_id ,t2.menu_name ,t2.menu_parent_id ,t2.sort ,t2.menu_url ,t2.menu_icon ,t2.menu_type ,    t2.menu_code ,
       t2.open_type ,
       t2.open_status ,
        t2.status ,
          t2.app_id  from t_pf_auth t1,t_pf_menu t2 where t1.status = 'y' and t2.status = 'y' and t1.role_id in (
	     select role.role_id
	                  from t_pf_role      role,
	                       t_uc_staff       u,
	                       t_pf_role2user ra,
	                       t_pf_application       app
	                 where role.role_id = ra.role_id
	                   and u.staff_code = ra.user_id
	                   and role.role_id = ra.role_id
	                   and role.app_id = app.app_id
	                   and role.app_id = ra.app_id
	                   and ra.status = 'y'
	                   and app.app_status = 'QY'
	                   and role.status = 'y'
	                   and role.app_id = #{appId}
	                   and ra.user_id = #{userId}
and t2.menu_parent_id IN(
select menu_id from t_pf_menu where menu_code=#{menuCode} and app_id=#{appId}
)
	    ) and t2.menu_id = t1.menu_id 
	    <if test="@Ognl@isNotBlank(menuType)">and t2.menu_type = #{menuType}</if>
	    group by t2.menu_id,t2.menu_name,t2.menu_parent_id,t2.sort,t2.menu_url,t2.menu_icon,t2.menu_type,t2.menu_code,
          t2.open_type,
          t2.open_status,
           t2.status,
          t2.app_id
	    order by t2.sort
	</select>
	
	
	<select id="getMenuIsOpenStatus" resultMap="RM_MenuEntity">
      select t2.menu_id        ,
       t2.menu_name      ,
       t2.menu_parent_id ,
       t2.sort           ,
       t2.menu_url       ,
       t2.menu_icon      ,
       t2.menu_type      ,
       t2.menu_code      ,
       t2.open_type      ,
       t2.open_status    ,
       t2.status         ,
       t2.app_id         
  from t_pf_auth t1, t_pf_menu t2
 where t1.status = 'y'
   and t2.status = 'y'
   and t2.menu_id = t1.menu_id
  and t2.app_id=#{appId}
   and t2.open_status='1'
 group by t2.menu_id,
          t2.menu_name,
          t2.menu_parent_id,
          t2.sort,
          t2.menu_url,
          t2.menu_icon,
          t2.menu_type,
          t2.menu_code,
          t2.open_type,
          t2.open_status,
          t2.status,
          t2.app_id
 order by t2.sort
	</select>
	
	
    <!--
    	分页查询已经使用Dialect进行分页,也可以不使用Dialect直接编写分页
    	因为分页查询将传 offset,pageSize,lastRows 三个参数,不同的数据库可以根于此三个参数属性应用不同的分页实现
    -->
    <select id="findList" resultMap="RM_MenuEntity">
    	SELECT <include refid="MenuEntityQueryColumns"/>
	    FROM t_pf_menu 
		<include refid="findList_where"/>
		
		<if test="@Ognl@isNotEmpty(bo.sortColumns)">
			ORDER BY ${bo.sortColumns}
		</if>
    </select>

	<select id="findPage" resultMap="RM_MenuEntity">
		SELECT <include refid="MenuEntityQueryColumns"/>
		FROM t_pf_menu
		<include refid="findList_where"/>

		<if test="@Ognl@isNotEmpty(bo.sortColumns)">
			ORDER BY ${bo.sortColumns}
		</if>
	</select>

    <select id="getButtonByMenuCode" resultMap="RM_MenuEntity">
        SELECT <include refid="MenuEntityQueryColumns"/>
        from t_pf_menu mt
        where mt.MENU_PARENT_ID
        in (SELECT MENU_ID FROM t_pf_menu dt where dt.MENU_CODE = #{menuCode})
    </select>

	<sql id="findList_where">
		<!-- ognl访问静态方法的表达式 为@class@method(args),以下为调用rapid中的Ognl.isNotEmpty()方法,还有其它方法如isNotBlank()可以使用，具体请查看Ognl类 -->
		<where>	      				
	       <if test="@Ognl@isNotEmpty(bo.menuId)">
				AND MENU_ID = #{bo.menuId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.appId)">
				AND APP_ID = #{bo.appId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.menuParentId)">
				AND MENU_PARENT_ID = #{bo.menuParentId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.menuCode)">
				AND MENU_CODE = #{bo.menuCode,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.menuName)">
				AND MENU_NAME = #{bo.menuName,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.menuType)">
				AND MENU_TYPE = #{bo.menuType,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.menuIcon)">
				AND MENU_ICON = #{bo.menuIcon,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.menuUrl)">
				AND MENU_URL = #{bo.menuUrl,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.sort)">
				AND SORT = #{bo.sort,jdbcType=INTEGER}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.remark)">
				AND REMARK = #{bo.remark,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.status)">
				AND STATUS = #{bo.status,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.creatorId)">
				AND CREATOR_ID = #{bo.creatorId,jdbcType=VARCHAR}
			</if>
	       
	       <if test="@Ognl@isNotEmpty(bo.updatorId)">
				AND UPDATOR_ID = #{bo.updatorId,jdbcType=VARCHAR}
			</if>
	       
	       <if test="@Ognl@isNotEmpty(bo.menuLevel)">
				AND MENU_LEVEL = #{bo.menuLevel,jdbcType=INTEGER}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.openType)">
				AND OPEN_TYPE = #{bo.openType,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.pageCode)">
				AND PAGE_CODE = #{bo.pageCode,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.openStatus)">
				AND OPEN_STATUS = #{bo.openStatus,jdbcType=VARCHAR}
			</if>
		</where>
	</sql>
    
    <!-- 根据角色ID 和 应用ID获取菜单 -->
    <select id="getMenuByRoleIdAndAppId" resultMap="RM_MenuEntity">
    	select t2.* from t_pf_auth t1, t_pf_menu t2
		where t1.MENU_ID = t2.MENU_ID and t1.status = 'y' and t2.status = 'y'
		and t1.app_Id = #{appId} and t1.ROLE_ID= #{roleId}
    </select>
    
    <select id="getMenuTopByStaffCode" resultMap="RM_MenuEntity">
      	SELECT
			t1.menu_id,
			t1.app_id,
			t1.menu_parent_id,
			t1.menu_code,
			t1.menu_name,
			t1.menu_type,
			t1.menu_icon,
			t1.menu_url,
			t1.sort,
			t1.remark,
			t1.STATUS,
			t1.creator_id,
			t1.create_date,
			t1.updator_id,
			t1.update_date,
			t1.menu_level,
			t1.open_type,
			t1.page_code,
			t1.open_status
		FROM
			t_pf_menu t1
			JOIN t_pf_auth t2 ON t2.menu_id = t1.menu_id
		WHERE
			t1.STATUS = 'Y' 
			AND t1.menu_parent_id = '' 
			AND t1.app_id = #{appId}
			AND t2.role_id IN (
			SELECT
				role.role_id 
			FROM
				t_pf_role role,
				t_uc_staff u,
				t_pf_role2user ra,
				t_pf_application app 
			WHERE
				role.role_id = ra.role_id 
				AND u.staff_code = ra.user_id 
				AND role.role_id = ra.role_id 
				AND role.app_id = app.app_id 
				AND role.app_id = ra.app_id 
				AND ra.STATUS = 'y' 
				AND app.app_status = 'QY' 
				AND role.STATUS = 'y' 
				AND role.app_id = #{appId}
				AND ra.user_id = #{staffCode}
			) 
		GROUP BY
 			t1.menu_id
		ORDER BY
			t1.sort;
	</select>
	
	<select id="getParentByStaffCode" resultMap="RM_MenuEntity">
      	SELECT
			t1.menu_id,
			t1.app_id,
			t1.menu_parent_id,
			t1.menu_code,
			t1.menu_name,
			t1.menu_type,
			t1.menu_icon,
			t1.menu_url,
			t1.sort,
			t1.remark,
			t1.STATUS,
			t1.creator_id,
			t1.create_date,
			t1.updator_id,
			t1.update_date,
			t1.menu_level,
			t1.open_type,
			t1.page_code,
			t1.open_status
		FROM
			t_pf_menu t1
			JOIN t_pf_auth t2 ON t2.menu_id = t1.menu_id
		WHERE
			t1.menu_parent_id = #{menuId}
			AND t1.STATUS = 'Y' 
			AND t1.app_id = #{appId}
			AND t2.role_id IN (
			SELECT
				role.role_id 
			FROM
				t_pf_role role,
				t_uc_staff u,
				t_pf_role2user ra,
				t_pf_application app 
			WHERE
				role.role_id = ra.role_id 
				AND u.staff_code = ra.user_id 
				AND role.role_id = ra.role_id 
				AND role.app_id = app.app_id 
				AND role.app_id = ra.app_id 
				AND ra.STATUS = 'y' 
				AND app.app_status = 'QY' 
				AND role.STATUS = 'y' 
				AND role.app_id = #{appId}
				AND ra.user_id = #{staffCode}
			) 
		GROUP BY
 			t1.menu_id
		ORDER BY
			t1.sort;
	</select>

    <resultMap id="RM_RoleMenu" type="com.blueland.bo.RoleMenuRedisDTO">
        <result column="APP_ID" property="appId"/>
        <result column="ROLE_ID" property="roleId"/>
        <collection property="list" ofType="java.lang.String">
            <result column="APP_ID_MENU_TYPE_MENU_ID"/>
        </collection>
    </resultMap>

    <select id="findListRoleMenu" resultMap="RM_RoleMenu">
        SELECT t1.APP_ID,t1.ROLE_ID,CONCAT(t2.MENU_TYPE,':',t2.MENU_ID) APP_ID_MENU_TYPE_MENU_ID
        FROM t_pf_auth t1
        LEFT JOIN t_pf_menu t2
        ON t1.MENU_ID = t2.MENU_ID
        <where>
			<if test="appId != null and appId != ''">
				t2.APP_ID = #{appId}
			</if>
		</where>
        ORDER BY t1.ROLE_ID
    </select>

    <select id="findMenuList" resultMap="RM_MenuEntity">
        SELECT
            MENU_ID,
        	APP_ID,
        	MENU_PARENT_ID,
        	MENU_CODE,
        	MENU_NAME,
        	MENU_TYPE,
        	MENU_ICON,
        	MENU_URL,
        	IFNULL(SORT,-1) SORT,
        	REMARK,
        	STATUS,
        	CREATOR_ID,
        	CREATE_DATE,
        	UPDATOR_ID,
        	UPDATE_DATE,
        	MENU_LEVEL,
        	OPEN_TYPE,
        	PAGE_CODE,
        	OPEN_STATUS
        FROM t_pf_menu
		<where>
			<if test="appId != null and appId != ''">
				APP_ID = #{appId}
			</if>
		</where>
		order by SORT
    </select>

	<select id="getMenuByPostCode" resultMap="RM_MenuEntity">
		select t2.menu_id ,t2.menu_name ,t2.menu_parent_id ,t2.sort ,t2.menu_url ,t2.menu_icon ,t2.menu_type ,    t2.menu_code ,
		t2.open_type ,
		t2.open_status ,
		t2.status ,
		t2.app_id  from t_pf_auth t1,t_pf_menu t2 where t1.status = 'y' and t2.status = 'y'
		and t1.app_id=#{appId}
		and t1.role_id in (
		select role.role_id
		from t_pf_role      role,
		t_uc_staff       u,
		t_pf_role2user ra,
		t_pf_application       app
		where role.role_id = ra.role_id
		and u.staff_code = ra.user_id
		and role.role_id = ra.role_id
		and role.app_id = app.app_id
		and role.app_id = ra.app_id
		and ra.status = 'y'
		and app.app_status = 'QY'
		and role.status = 'y'
		and role.app_id = #{appId}
		and ra.staff_post_code = #{postCode}
		) and t2.menu_id = t1.menu_id
		<if test="@Ognl@isNotBlank(menuType)"> and t2.menu_type = #{menuType} </if>
		<if test="@Ognl@isNotBlank(menuId)">  and (t2.menu_parent_id =  #{menuId} or t2.menu_id=  #{menuId}) </if>
		group by t2.menu_id,t2.menu_name,t2.menu_parent_id,t2.sort,t2.menu_url,t2.menu_icon,t2.menu_type,t2.menu_code,
		t2.open_type,
		t2.open_status,
		t2.status,
		t2.app_id
		order by t2.sort
	</select>

	<select id="getMenuBuCode" resultMap="RM_MenuEntity">
		select t2.menu_id ,t2.menu_name ,t2.menu_parent_id ,t2.sort ,t2.menu_url ,t2.menu_icon ,t2.menu_type ,    t2.menu_code ,
		t2.open_type ,
		t2.open_status ,
		t2.status ,
		t2.app_id  from t_pf_auth t1,t_pf_menu t2 where t1.status = 'y' and t2.status = 'y' and t1.role_id in (
		select role.role_id
		from t_pf_role      role,
		t_uc_staff       u,
		t_pf_role2user ra,
		t_pf_application       app
		where role.role_id = ra.role_id
		and u.staff_code = ra.user_id
		and role.role_id = ra.role_id
		and role.app_id = app.app_id
		and role.app_id = ra.app_id
		and ra.status = 'y'
		and app.app_status = 'QY'
		and role.status = 'y'
		and role.app_id = #{appId}
		and ra.staff_post_code = #{postCode}
		and t2.menu_parent_id IN(
		select menu_id from t_pf_menu where menu_code=#{menuCode} and app_id=#{appId}
		)
		) and t2.menu_id = t1.menu_id
		<if test="@Ognl@isNotBlank(menuType)">and t2.menu_type = #{menuType}</if>
		group by t2.menu_id,t2.menu_name,t2.menu_parent_id,t2.sort,t2.menu_url,t2.menu_icon,t2.menu_type,t2.menu_code,
		t2.open_type,
		t2.open_status,
		t2.status,
		t2.app_id
		order by t2.sort
	</select>

	<delete id="deleteMenuByAppId" >
		delete from t_pf_menu where app_id = #{appId}
	</delete>
</mapper>

