<?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.RoleDao">
	<resultMap id="RM_RoleEntity" type="com.blueland.bo.RoleEntity">
        <result property="roleId" column="ROLE_ID"/>
        <result property="appId" column="APP_ID"/>
        <result property="roleName" column="ROLE_NAME"/>
        <result property="roleType" column="ROLE_TYPE"/>
        <result property="roleModule" column="ROLE_MODULE"/>
        <result property="orgId" column="ORG_ID"/>
        <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"/>
	</resultMap>

	<sql id="RoleEntityColumns">
        	ROLE_ID,
        	APP_ID,
        	ROLE_NAME,
        	ROLE_TYPE,
        	ROLE_MODULE,
        	ORG_ID,
        	SORT,
        	REMARK,
        	STATUS,
        	CREATOR_ID,
        	CREATE_DATE,
        	UPDATOR_ID,
        	UPDATE_DATE
	</sql>
	
	<sql id="RoleEntityQueryColumns">
        	role_id,
        	app_id,
        	role_name,
        	role_type,
        	role_module,
        	org_id,
        	sort,
        	remark,
        	status,
        	creator_id,
        	create_date,
        	updator_id,
        	update_date
	</sql>
	<!-- useGeneratedKeys="true" keyProperty="xxx" for sqlserver and mysql -->
	<insert id="insert" useGeneratedKeys="false" keyProperty="roleId">
        INSERT INTO t_pf_role (<include refid="RoleEntityColumns"/>) VALUES (
        	#{roleId,jdbcType=VARCHAR},
        	#{appId,jdbcType=VARCHAR},
        	#{roleName,jdbcType=VARCHAR},
        	#{roleType,jdbcType=VARCHAR},
        	#{roleModule,jdbcType=VARCHAR},
        	#{orgId,jdbcType=VARCHAR},
        	#{sort,jdbcType=INTEGER},
        	#{remark,jdbcType=VARCHAR},
        	#{status,jdbcType=VARCHAR},
        	#{creatorId,jdbcType=VARCHAR},
		    #{createDate,jdbcType=TIMESTAMP},
        	#{updatorId,jdbcType=VARCHAR},
		    #{updateDate,jdbcType=TIMESTAMP}
        )
	</insert>
    
    <delete id="deluser">
        DELETE FROM t_pf_role2user WHERE
        role_id = #{roleId,jdbcType=VARCHAR}  AND 
        app_id = #{appId,jdbcType=VARCHAR} 
    </delete>
    
    <delete id="deleteRole">
        DELETE FROM t_pf_role WHERE
        role_id = #{roleId,jdbcType=VARCHAR}  AND 
        app_id = #{appId,jdbcType=VARCHAR} 
    </delete>
    
     <select id="getAppRole" resultMap="RM_RoleEntity">
		  select t1.role_id ,
                       t1.app_id ,
                       t1.role_name ,
                       t1.role_type ,
                       t1.sort sort,
                       t1.remark remark,
                       t2.app_name appName,
                       t1.status status
                  from t_pf_role t1
                  left join t_pf_application t2
                    on (t1.app_id = t2.app_id)
                  left join t_uc_org t3
                    on t3.org_id = t1.org_id
                 where t1.app_id = #{bo.appId}
                    <if test="@Ognl@isNotEmpty(roleId)">
                   	 and t1.role_id like CONCAT('%',#{roleId,jdbcType=VARCHAR},'%') 
                    </if>
                   <if test="@Ognl@isNotEmpty(roleName)">
                   	 and t1.role_name like CONCAT('%',#{roleName,jdbcType=VARCHAR},'%')  
                    </if>
                    <if test="@Ognl@isNotEmpty(roleType)">
                   	 and t1.role_type = #{roleType}
                    </if>
                   and t1.status = 'y'
                   order by t1.sort
  		</select>

	<select id="getAppRolePage" resultMap="RM_RoleEntity">
		select t1.role_id ,
		t1.app_id ,
		t1.role_name ,
		t1.role_type ,
		t1.sort sort,
		t1.remark remark,
		t2.app_name appName,
		t1.status status
		from t_pf_role t1
		left join t_pf_application t2
		on (t1.app_id = t2.app_id)
		left join t_uc_org t3
		on t3.org_id = t1.org_id
		where t1.app_id = #{bo.appId}
		<if test="@Ognl@isNotEmpty(bo.roleId)">
			and t1.role_id like CONCAT('%',#{bo.roleId,jdbcType=VARCHAR},'%')
		</if>
		<if test="@Ognl@isNotEmpty(bo.roleName)">
			and t1.role_name like CONCAT('%',#{bo.roleName,jdbcType=VARCHAR},'%')
		</if>
		<if test="@Ognl@isNotEmpty(bo.roleType)">
			and t1.role_type = #{bo.roleType}
		</if>
		and t1.status = 'y'
		order by t1.sort
	</select>
    
    <select id="get" resultMap="RM_RoleEntity">
		SELECT <include refid="RoleEntityQueryColumns"/>
	    FROM t_pf_role 
        WHERE 
	        role_id = #{roleId,jdbcType=VARCHAR} 
	</select>
	
	<select id="findRolesByUser" resultMap="RM_RoleEntity">
		SELECT 
			t1.role_id,
			t1.app_id,
			t1.role_name,
			t1.role_type,
			t1.role_module,
			t1.org_id,
			t1.sort,
			t1.remark,
			t1.STATUS,
			t1.creator_id,
			t1.create_date,
			t1.updator_id,
			t1.update_date
		FROM
			t_pf_role t1,t_pf_role2user t2
		where t1.ROLE_ID = t2.ROLE_ID and t1.APP_ID = t2.APP_ID 
		and t1. STATUS = 'y' and t2. STATUS = 'y'
		and t2.APP_ID = #{appId}
		and t2.USER_ID = #{userId}
	</select>
	
	
	<select id="findRoleUserBy" resultMap="RM_RoleEntity">
	<!-- 	select t1.staff_id ,
		t1.staff_name ,
       t1.org_id ,
       t1.org_name ,
       t3.app_id ,
       t3.role_id ,
        t1.company_id ,
                       t1.company_name ,
                       t1.dept_id ,
                       t1.dept_name 
	  from t_uc_staff t1, t_pf_role2user t2, t_pf_role t3
	 where t1.staff_id = t2.user_id
	   and t2.status = 'y'
	   and t3.role_id = t2.role_id
	   and t3.status = 'y'
	   and t3.app_id = #{appId}
	   and t2.role_id = #{roleId}
		 and t1.staff_id=#{userId} -->
		 
		 SELECT
	t1.staff_code,
	t1.staff_name,
	t1.org_id,
	t1.org_name,
	t3.app_id,
	t3.role_id,
	t1.company_id,
	t1.company_name,
	t1.dept_id,
	t1.dept_name
FROM
	t_uc_staff t1
left join t_pf_role2user t2
on t1.staff_code = t2.user_id and  t2. STATUS = 'y' and t2.APP_ID=#{appId}
left join t_pf_role t3
on t3.role_id = t2.role_id and  t3. STATUS = 'y' and t3.APP_ID=#{appId}
WHERE t3.app_id = #{appId} and t2.role_id = #{roleId}
and t1.staff_code=#{userId}
	</select>
	
	
	<select id="findUserId" resultType="java.lang.Integer">	
		select count(*) from t_pf_role2user t1 where t1.app_id=#{appId} 
and t1.role_id=#{roleId} and t1.user_id=#{userId}
	</select>
	
	<select id="findAppidByRoleId" resultType="java.lang.Integer">	
		SELECT  count(*) FROM t_pf_role WHERE app_id = #{appId} and role_id=#{roleId}
	</select>
	
<insert id="saveUser" >
    insert into t_pf_role2user (role_id, user_id,status,creator_id,create_date,updator_id,update_date,org_id,org_name,title_id,app_id) values (
    #{roleId,jdbcType=VARCHAR}, 
    #{userId,jdbcType=VARCHAR},
    'Y',
    #{creatorId,jdbcType=VARCHAR},
	#{createDate,jdbcType=TIMESTAMP},
    #{updatorId,jdbcType=VARCHAR},
	#{updateDate,jdbcType=TIMESTAMP},
     #{orgId,jdbcType=VARCHAR},
      #{orgName,jdbcType=VARCHAR},
       #{titleId,jdbcType=VARCHAR},
        #{appId,jdbcType=VARCHAR}
    )
  </insert>
   <delete id="deleteUserId" >
    delete from t_pf_role2user  where app_id =#{appId,jdbcType=VARCHAR}
	and role_id=#{roleId,jdbcType=VARCHAR} 
	and user_id=#{userId,jdbcType=VARCHAR}
  </delete>
    <!--
    	分页查询已经使用Dialect进行分页,也可以不使用Dialect直接编写分页
    	因为分页查询将传 offset,pageSize,lastRows 三个参数,不同的数据库可以根于此三个参数属性应用不同的分页实现
    -->
    <select id="findList" resultMap="RM_RoleEntity">
    	SELECT <include refid="RoleEntityQueryColumns"/>
	    FROM T_PMSN_ROLE 
		<include refid="findList_where"/>
		
		<if test="@Ognl@isNotEmpty(bo.sortColumns)">
			ORDER BY ${bo.sortColumns}
		</if>
    </select>

	<select id="findPage" resultMap="RM_RoleEntity">
		SELECT <include refid="RoleEntityQueryColumns"/>
		FROM T_PMSN_ROLE
		<include refid="findList_where"/>

		<if test="@Ognl@isNotEmpty(bo.sortColumns)">
			ORDER BY ${bo.sortColumns}
		</if>
	</select>
	
	
	<sql id="findList_where">
		<!-- ognl访问静态方法的表达式 为@class@method(args),以下为调用rapid中的Ognl.isNotEmpty()方法,还有其它方法如isNotBlank()可以使用，具体请查看Ognl类 -->
		<where>	      				
	       <if test="@Ognl@isNotEmpty(bo.roleId)">
				AND ROLE_ID = #{bo.roleId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.appId)">
				AND APP_ID = #{bo.appId,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.roleName)">
				AND ROLE_NAME = #{bo.roleName,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.roleType)">
				AND ROLE_TYPE = #{bo.roleType,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.roleModule)">
				AND ROLE_MODULE = #{bo.roleModule,jdbcType=VARCHAR}
			</if>
	       <if test="@Ognl@isNotEmpty(bo.orgId)">
				AND ORG_ID = #{bo.orgId,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>
	       
		</where>
	</sql>
    
    <!-- 根据用户ID获取用户租户ID -->
	<select id="findRoleTenantidByUserid" resultMap="RM_RoleEntity">
		SELECT 
			t1.role_id,
			t1.app_id,
			t1.role_name,
			t1.role_type,
			t1.role_module,
			t1.org_id,
			t1.sort,
			t1.remark,
			t1.STATUS,
			t1.creator_id,
			t1.create_date,
			t1.updator_id,
			t1.update_date
		FROM
			t_pf_role t1,t_pf_role2user t2
		where t1.ROLE_ID = t2.ROLE_ID and t1.APP_ID = t2.APP_ID
		and t1.APP_ID != 'sys' 
		and t1. STATUS = 'y' and t2. STATUS = 'y'
		and t2.USER_ID = #{userId}
		group by t1.APP_ID
		order by t1.APP_ID
	</select>

	<select id="findRolesByPostCode" resultMap="RM_RoleEntity">
		SELECT
			t1.role_id,
			t1.app_id,
			t1.role_name,
			t1.role_type,
			t1.role_module,
			t1.org_id,
			t1.sort,
			t1.remark,
			t1.STATUS,
			t1.creator_id,
			t1.create_date,
			t1.updator_id,
			t1.update_date
		FROM
			t_pf_role t1,t_pf_role2user t2
		where t1.ROLE_ID = t2.ROLE_ID and t1.APP_ID = t2.APP_ID
		  and t1. STATUS = 'y' and t2. STATUS = 'y'
		  and t2.APP_ID = #{appId}
		  and t2.staff_post_code = #{postCode}
	</select>

	<select id="findRoleAllByPostCode" resultMap="RM_RoleEntity">
		SELECT
			t1.role_id,
			t1.app_id,
			t1.role_name,
			t1.role_type,
			t1.role_module,
			t1.org_id,
			t1.sort,
			t1.remark,
			t1.STATUS,
			t1.creator_id,
			t1.create_date,
			t1.updator_id,
			t1.update_date
		FROM
			t_pf_role t1,t_pf_role2user t2
		where t1.ROLE_ID = t2.ROLE_ID and t1.APP_ID = t2.APP_ID
		  and t1.APP_ID != 'sys'
		and t1. STATUS = 'y' and t2. STATUS = 'y'
		and t2.staff_post_code = #{postCode}
		group by t1.APP_ID
		order by t1.APP_ID
	</select>

	<delete id="deleteByPostCode" >
		delete from t_pf_role2user
		where app_id =#{appId,jdbcType=VARCHAR}
		  and role_id=#{roleId,jdbcType=VARCHAR}
		  and staff_post_code=#{postCode,jdbcType=VARCHAR}
	</delete>

	<select id="findRoleUserByCode" resultMap="RM_RoleEntity">
		SELECT
		t1.staff_code,
		t1.staff_name,
		t1.org_id,
		t1.org_name,
		t3.app_id,
		t3.role_id,
		t1.company_id,
		t1.company_name,
		t1.dept_id,
		t1.dept_name
		FROM
		t_uc_staff t1
		left join t_pf_role2user t2
		on t1.staff_code = t2.user_id and  t2. STATUS = 'y' and t2.APP_ID=#{appId}
		left join t_pf_role t3
		on t3.role_id = t2.role_id and  t3. STATUS = 'y' and t3.APP_ID=#{appId}
		WHERE t3.app_id = #{appId} and t2.role_id = #{roleId}
		and t1.staff_post_code=#{postCode}
	</select>

	<delete id="deleteByAppId">
		delete from t_pf_role where app_id = #{appId}
	</delete>

	<select id="findListByAppId" resultMap="RM_RoleEntity">
		SELECT <include refid="RoleEntityQueryColumns"/>
		FROM t_pf_role
		where STATUS = "Y" and APP_ID = #{appId}
	</select>
</mapper>

