<?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.cftech.sys.dao.RoleMapper">

    <resultMap id="roleResult" type="com.cftech.sys.model.Role">
		<id property="id" column="id" />
		 <result column="role_name" property="roleName" jdbcType="VARCHAR" />
        <result column="role_sign" property="roleSign" jdbcType="VARCHAR" />
		 <result column="description" property="description" jdbcType="VARCHAR" />
		<collection property="permission" ofType="com.cftech.sys.model.Permission">
			<id property="id" column="permission.id" />
		</collection>
	</resultMap>



    <resultMap id="BaseResultMap" type="com.cftech.sys.model.Role">
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="role_name" property="roleName" jdbcType="VARCHAR" />
        <result column="role_sign" property="roleSign" jdbcType="VARCHAR" />
        <result column="description" property="description" jdbcType="VARCHAR" />
    </resultMap>
    
      <sql id="roleColumns">
       id, role_name, role_sign, description
    </sql>
    
    <select id="getByPrimaryKey" resultMap="roleResult">
		SELECT
			a.id, role_name, role_sign, description,
			rm.permission_id AS "permission.id"
		FROM role a
		LEFT JOIN role_permission rm ON rm.role_id = a.id
		WHERE a.id = #{id}
	</select>
	
	
	<select id="selectAll" resultMap="BaseResultMap" parameterType="java.util.Map">
		SELECT
	 <include refid="Base_Column_List" />
		FROM role 
		where delflag = 0
		<if test="roleName!=null">
		and role_name like '%${roleName}%' 
		</if> 
	</select> 
	
	<select id="selectAllSign" resultMap="BaseResultMap" parameterType="java.util.Map">
		SELECT
	 <include refid="Base_Column_List" />
		FROM role 
		where delflag = 0
		<if test="roleSign!=null">
		and role_sign like '%${roleSign}%'
		</if>
		and role_sign != 'sys:dealer' 
	</select>
	
	
	
	

    
    
    <sql id="Example_Where_Clause">
        <where>
            <foreach collection="oredCriteria" item="criteria" separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" suffix=")" prefixOverrides="and">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach collection="criterion.value" item="listItem" open="(" close=")" separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>
    <sql id="Update_By_Example_Where_Clause">
        <where>
            <foreach collection="example.oredCriteria" item="criteria" separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" suffix=")" prefixOverrides="and">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach collection="criterion.value" item="listItem" open="(" close=")" separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>
    <sql id="Base_Column_List">
        id, role_name, role_sign, description
    </sql>
    <select id="selectByExample" resultMap="BaseResultMap" parameterType="com.cftech.sys.model.RoleExample">
        select
        <if test="distinct">
            distinct
        </if>
        <include refid="Base_Column_List" />
        from role
        <if test="_parameter != null">
            <include refid="Example_Where_Clause" />
        </if>
        <if test="orderByClause != null">
            order by ${orderByClause}
        </if>
    </select>
    <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long">
        select
        <include refid="Base_Column_List" />
        from role
        where id = #{id,jdbcType=BIGINT}
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
        delete from role
        where id = #{id,jdbcType=BIGINT}
    </delete>
    <delete id="deleteByExample" parameterType="com.cftech.sys.model.RoleExample">
        delete from role
        <if test="_parameter != null">
            <include refid="Example_Where_Clause" />
        </if>
    </delete>
    <insert id="insert" parameterType="com.cftech.sys.model.Role">
        insert into role (id, role_name, role_sign,
        description)
        values (#{id,jdbcType=BIGINT}, #{roleName,jdbcType=VARCHAR}, #{roleSign,jdbcType=VARCHAR},
        #{description,jdbcType=VARCHAR})
    </insert>
    
   <insert id="insertRole" useGeneratedKeys="true" keyProperty="id" parameterType="com.cftech.sys.model.Role">
        insert into role
        <trim prefix="(" suffix=")" suffixOverrides=",">
       
            <if test="roleName != null">
                role_name,
            </if>
            <if test="roleSign != null">
                role_sign,
            </if>
            <if test="description != null">
                description,
            </if>
            createtime,timestamp,updatetime,delflag
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
        
            <if test="roleName != null">
                #{roleName,jdbcType=VARCHAR},
            </if>
            <if test="roleSign != null">
                #{roleSign,jdbcType=VARCHAR},
            </if>
            <if test="description != null">
                #{description,jdbcType=VARCHAR},
            </if>
            
              #{createtime,jdbcType=TIMESTAMP},
         	#{timestamp,jdbcType=BIGINT},
         	#{updatetime,jdbcType=TIMESTAMP},
         	  #{delflag,jdbcType=INTEGER}
        </trim>
    </insert>
    
   <delete id="deleteRolePermission">
		DELETE FROM role_permission WHERE role_id = #{id}
	</delete>
	
	<insert id="insertRolePermission">
		INSERT INTO role_permission(role_id, permission_id)
		<foreach collection="permission" item="permission" separator=" union all ">
			SELECT #{id}, #{permission.id}
			FROM dual
		</foreach>
	</insert>
    
    
    
    <insert id="insertSelective" parameterType="com.cftech.sys.model.Role">
        insert into role
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="roleName != null">
                role_name,
            </if>
            <if test="roleSign != null">
                role_sign,
            </if>
            <if test="description != null">
                description,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=BIGINT},
            </if>
            <if test="roleName != null">
                #{roleName,jdbcType=VARCHAR},
            </if>
            <if test="roleSign != null">
                #{roleSign,jdbcType=VARCHAR},
            </if>
            <if test="description != null">
                #{description,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>
    <select id="countByExample" parameterType="com.cftech.sys.model.RoleExample" resultType="java.lang.Integer">
        select count(*) from role
        <if test="_parameter != null">
            <include refid="Example_Where_Clause" />
        </if>
    </select>
    <update id="updateByExampleSelective" parameterType="map">
        update role
        <set>
            <if test="record.id != null">
                id = #{record.id,jdbcType=BIGINT},
            </if>
            <if test="record.roleName != null">
                role_name = #{record.roleName,jdbcType=VARCHAR},
            </if>
            <if test="record.roleSign != null">
                role_sign = #{record.roleSign,jdbcType=VARCHAR},
            </if>
            <if test="record.delflag != null">
                delflag = #{record.delflag,jdbcType=TINYINT},
            </if>
            <if test="record.description != null">
                description = #{record.description,jdbcType=VARCHAR},
            </if>
        </set>
        <if test="_parameter != null">
            <include refid="Update_By_Example_Where_Clause" />
        </if>
    </update>
    
    
       <update id="updateRole" parameterType="com.cftech.sys.model.Role">
        update role
        <set>
       	  <if test="roleName != null">
               role_name = #{roleName,jdbcType=VARCHAR},
            </if>
            <if test="roleSign != null">
               role_sign =  #{roleSign,jdbcType=VARCHAR},
            </if>
            <if test="delflag != null">
                delflag = #{delflag,jdbcType=TINYINT},
            </if>
            <if test="description != null">
              description =  #{description,jdbcType=VARCHAR},
            </if>
         	updatetime = #{updatetime,jdbcType=TIMESTAMP}
         	where id = #{id,jdbcType=BIGINT}
        </set>
   
    </update>
    
    
    
    <update id="updateByExample" parameterType="map">
        update role
        set id = #{record.id,jdbcType=BIGINT},
        role_name = #{record.roleName,jdbcType=VARCHAR},
        role_sign = #{record.roleSign,jdbcType=VARCHAR},
         delflag = #{record.delflag,jdbcType=TINYINT},
        description =
        #{record.description,jdbcType=VARCHAR}
        <if test="_parameter != null">
            <include refid="Update_By_Example_Where_Clause" />
        </if>
    </update>
    <update id="updateByPrimaryKeySelective" parameterType="com.cftech.sys.model.Role">
        update role
        <set>
            <if test="roleName != null">
                role_name = #{roleName,jdbcType=VARCHAR},
            </if>
            <if test="roleSign != null">
                role_sign = #{roleSign,jdbcType=VARCHAR},
            </if>
            <if test="description != null">
                description = #{description,jdbcType=VARCHAR},
            </if>
             <if test="delflag != null">
                delflag = #{delflag,jdbcType=TINYINT},
            </if>
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>
    <update id="updateByPrimaryKey" parameterType="com.cftech.sys.model.Role">
        update role
        set role_name = #{roleName,jdbcType=VARCHAR},
        role_sign = #{roleSign,jdbcType=VARCHAR},
        delflag = #{delflag,jdbcType=TINYINT},
        description = #{description,jdbcType=VARCHAR}
        where id = #{id,jdbcType=BIGINT}
    </update>

    <!-- 通过用户id 查询用户 拥有的角色 -->
    <select id="selectRolesByUserId" resultType="com.cftech.sys.model.UserRole" parameterType="java.lang.Long">
        SELECT * FROM user_role where user_id=#{userId,jdbcType=BIGINT}
    </select>
    
    <!-- 通过用户id,公众号id 查询 拥有的角色 -->
    <select id="selectRolesByUserAccountsId" resultType="com.cftech.sys.model.UserRole" parameterType="java.util.Map">
        SELECT * FROM user_role where user_id=#{userId,jdbcType=BIGINT} and accounts_id=#{accountsId,jdbcType=BIGINT}
    </select>
    
    <!-- 通过公众号id 查询拥有的角色 -->
    <select id="selectRolesByAccountsId" resultType="com.cftech.sys.model.UserRole" parameterType="java.lang.Long">
        SELECT * FROM user_role where user_id=#{userId,jdbcType=BIGINT}
    </select>
    
    
    <!--<select id="getRoleByIds" resultMap="BaseResultMap" parameterType="string">-->
    	 <!--SELECT r.* FROM role r where delflag= 0-->
          <!--<if test="_parameter != null and _parameter!=''">-->
    	    <!--and r.id in ( ${ids} )-->
          <!--</if>-->
    <!--</select>-->
    
</mapper>