<?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.qmth.teachcloud.common.mapper.SysRoleMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.qmth.teachcloud.common.entity.SysRole">
    <result column="id" property="id" />
        <result column="school_id" property="schoolId" />
        <result column="name" property="name" />
        <result column="enable" property="enable" />
        <result column="type" property="type" />
        <result column="create_id" property="createId" />
        <result column="create_time" property="createTime" />
        <result column="update_id" property="updateId" />
        <result column="update_time" property="updateTime" />
        <result column="default_role" property="defaultRole" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        select t.id, t.school_id, t.name, t.enable, t.type, t.create_id, t.create_time, t.update_id, t.update_time, t.default_role, t.interpret from sys_role t
    </sql>

    <select id="listRolesByUserId" resultMap="BaseResultMap">
        SELECT distinct
            a.id,
            a.name,
            a.type,
            a.default_role
        FROM
            sys_role a
                JOIN
            sys_user_role b ON a.id = b.role_id
        WHERE
            b.user_id = #{id}
    </select>

    <select id="listPage" resultMap="BaseResultMap">
        select * from(<include refid="Base_Column_List" />
        <where>
            <if test="schoolId != null and schoolId != ''">
                and t.school_id = #{schoolId}
            </if>
            <if test="name != null and name != ''">
                and t.name like concat('%', #{name}, '%')
            </if>
            <if test="enable != null and enable != '' or enable == 0">
                and t.enable = #{enable}
            </if>
        </where>
        union
        <include refid="Base_Column_List" /> join t_school_role tsr on tsr.role_id = t.id
        where t.default_role = 1 and (t.type <![CDATA[ <> ]]> 'ADMIN' or t.type is null)
            <if test="schoolId != null and schoolId != ''">
                and tsr.school_id = #{schoolId}
            </if>
            <if test="name != null and name != ''">
                and t.name like concat('%', #{name}, '%')
            </if>
            <if test="enable != null and enable != '' or enable == 0">
                and t.enable = #{enable}
            </if>
        union
        <include refid="Base_Column_List" />
        where t.default_role = 1 and (t.type = 'SCHOOL_ADMIN' or t.type = 'SCHOOL_TEACHER' or t.type = 'ASSISTANT')
            <if test="name != null and name != ''">
                and t.name like concat('%', #{name}, '%')
            </if>
            <if test="enable != null and enable != '' or enable == 0">
                and t.enable = #{enable}
            </if>
            ) t
        ORDER BY t.create_time
    </select>

    <select id="listRolesBySchoolId" resultMap="BaseResultMap">
        <include refid="Base_Column_List" />
        <where>
            t.enable = 1
            <if test="schoolId != null and schoolId != ''">
                and t.school_id = #{schoolId}
            </if>
        </where>
        union
        <include refid="Base_Column_List" /> join t_school_role tsr on tsr.role_id = t.id
        where t.default_role = 1 and (t.type <![CDATA[ <> ]]> 'ADMIN' or t.type is null)
        <if test="schoolId != null and schoolId != ''">
            and tsr.school_id = #{schoolId}
        </if>
        union
        <include refid="Base_Column_List" />
        where t.default_role = 1 and (t.type = 'SCHOOL_ADMIN' or t.type = 'SCHOOL_TEACHER' or t.type = 'ASSISTANT')
    </select>
</mapper>
