<?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="lingtao.net.dao.SysRoleMapper">
	<resultMap id="BaseResultMap" type="lingtao.net.bean.SysRole">
		<id column="roleId" jdbcType="INTEGER" property="roleId" />
		<result column="roleName" jdbcType="VARCHAR" property="roleName" />
		<result column="isRegist" jdbcType="VARCHAR" property="isRegist" />
		<result column="remark" jdbcType="VARCHAR" property="remark" />
		<result column="createBy" jdbcType="VARCHAR" property="createBy" />
		<result column="createDate" jdbcType="TIMESTAMP" property="createDate" />
		<result column="updateBy" jdbcType="VARCHAR" property="updateBy" />
		<result column="updateDate" jdbcType="TIMESTAMP" property="updateDate" />
	</resultMap>
  
  	<!-- 查询角色拥有的权限 -->
  	<select id="getPerIdsByRoleId" parameterType="int" resultType="int">
        select perId from tbl_sys_role_permission where roleId = #{roleId}
    </select>
    
    <!-- 删除角色拥有的权限 -->
  	<delete id="deleteRolePermissions" parameterType="int">
        delete from tbl_sys_role_permission where roleId = #{roleId}
    </delete>
    
	<!-- 给角色授权 -->
    <insert id="addRolePermissions">
        insert into tbl_sys_role_permission(roleId,perId)
          values
          <foreach collection="perIds" item="item" separator=",">
              (#{roleId},#{item})
          </foreach>
    </insert>

  	<!-- 获取所有角色 -->
  	<select id="getRoles" parameterType="SysRole" resultMap="BaseResultMap">
  		select * from tbl_sys_role
  		<where>
  			<if test="roleName != null and roleName != ''">
  				and roleName like '%${roleName}%'
  			</if>
  		</where>
  		order by isRegist
  	</select>
  	
  	<!-- 改变与用户状态 -->
  	<update id="changeRoleStatus" parameterType="String">
		UPDATE 
	  		tbl_sys_role 
		SET
		  isRegist = 
		  	CASE
		    WHEN 
		    	isRegist = '1' 
		    THEN 
		    	'0' 
		    ELSE 
		    	'1' 
		  END 
		WHERE roleId = #{roleId} 
   	</update>
  
   	<insert id="insertSelective" parameterType="lingtao.net.bean.SysRole">
	    INSERT INTO tbl_sys_role
	      (
		  roleId,
		  roleName,
		  isRegist,
		  remark,
		  createBy,
		  createDate
		  ) 
		VALUES
		  (
		  NULL,
		  #{roleName},
		  #{isRegist},
		  #{remark},
		  #{createBy},
		  now()
		  ) 
  	</insert>
  
  	<update id="updateByPrimaryKeySelective" parameterType="lingtao.net.bean.SysRole">
	    update 
	    	tbl_sys_role 
	    set 
	   	 	roleName = #{roleName,jdbcType=VARCHAR},
	   	 	remark = #{remark,jdbcType=VARCHAR},
	    	updateDate = now()
	    where 
	    	roleId = #{roleId,jdbcType=INTEGER}
  	</update>
  
  	<!-- 删除角色 -->
  	<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    	delete from tbl_sys_role
    	where roleId = #{roleId,jdbcType=INTEGER}
  	</delete>
  
  
   	<!-- 查询角色，把角色ID转成文字 -->
   	<!-- 查询可以被注册的角色 -->
   	<select id="getAllRoleName" resultMap="BaseResultMap">
  		SELECT 
  			roleId,roleName
  		FROM 
  			tbl_sys_role 
  		<where>
  			<if test="isRegist != null and isRegist != '' ">
  				isRegist = #{isRegist}
  			</if>
  		</where>	
  		ORDER BY 
  			roleId ASC
  	</select>
  	
  	<!-- 查询出用户所拥有的且允许被创建的角色 -->	
    <select id="getRolesByUserId" parameterType="int" resultType="SysRole">
		select * from tbl_sys_role where isRegist != '0' and roleId in (
      		<!-- 查询用户拥有的角色 -->
			select roleId from tbl_sys_user_role where userId = #{userId}
      	)
	</select>
</mapper>