<?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.vctgo.system.mapper.SysClassSettingMapper">

    <resultMap type="com.vctgo.system.api.domain.SysClassSetting" id="SysClassSettingResult">
        <result property="id" column="id"/>
        <result property="enrollmentYear" column="enrollment_year"/>
        <result property="gradeIn" column="grade_in"/>
        <result property="classNumber" column="class_number"/>
        <result property="className" column="class_name"/>
        <result property="classAlias" column="class_alias"/>
        <result property="remark" column="remark"/>
        <result property="isDeleted" column="is_deleted"/>
        <result property="createById" column="create_by_id"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateById" column="update_by_id"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="tenantId" column="tenant_id"/>
    </resultMap>

    <sql id="selectSysClassSettingVo">
        select id, enrollment_year, grade_in, class_number, class_name, class_alias, remark, is_deleted, create_by_id, create_by, create_time, update_by_id, update_by, update_time, tenant_id from sys_class_setting
    </sql>

    <sql id="selectClassAndTeacherVo">
        select c.id, c.enrollment_year, c.grade_in, c.class_number, c.class_name, c.class_alias,
               c.remark, c.is_deleted, c.create_by_id, c.create_by, c.create_time,
               c.update_by_id, c.update_by, c.update_time, c.tenant_id,
               rel_1.teacher_name as teacherName, IFNULL(rel_2.childhood_count,0) as childhoodCount,
               rel_3.location_name as locationName,rel_3.location_id locationIdList
        from sys_class_setting c
        left join (select cu.class_id, GROUP_CONCAT(cu.bind_user_name) teacher_name,
                   GROUP_CONCAT(DISTINCT cu.tenant_id) tenant_id from sys_re_class_user cu
                   where cu.bind_user_position in ('1', '2', '3') and cu.is_deleted=0
                   <if test="query.params.teacherType != null and query.params.teacherType != ''">
                    and cu.bind_user_position = #{query.params.teacherType} </if>
                   group by cu.class_id) rel_1 on c.id= rel_1.class_id and c.tenant_id = rel_1.tenant_id
        left join (select class_id, count(*) childhood_count, GROUP_CONCAT(DISTINCT tenant_id) tenant_id
                   from sys_re_class_user
                   where bind_user_position = '4' and is_deleted=0
                   group by class_id) rel_2 on c.id= rel_2.class_id and c.tenant_id = rel_2.tenant_id
        left join (select c.id class_id, GROUP_CONCAT(ls.id) location_id, GROUP_CONCAT(ls.name) location_name
                  from sys_class_setting c
                  inner join sys_re_location_class rlc on rlc.class_id=c.id and rlc.is_deleted = 0
                  left join sys_location_setting ls on ls.id = rlc.location_id
                  where c.is_deleted = 0
                  group by c.id) rel_3 on c.id= rel_3.class_id
    </sql>

    <sql id="defaultDeleteCondition">
        is_deleted = 0
    </sql>

<!--    <resultMap type="com.vctgo.system.api.domain.SysClassSetting" id="sysClassResult"
               extends="SysClassSettingResult">
        <collection property="locationList" ofType="java.lang.String" javaType="java.util.List"
                    column="{id=id}" select="selectLocation"
                    fetchType="eager"/>
    </resultMap>-->

    <select id="selectLocation" resultType="java.lang.String">
        SELECT
            s.NAME
        FROM
            sys_re_location_class c
            LEFT JOIN sys_location_setting s ON c.location_id = s.id
        WHERE
            c.is_deleted = '0'
            AND c.class_id = #{id}
    </select>

    <select id="selectSysClassSettingList" resultMap="SysClassSettingResult">
        <include refid="selectSysClassSettingVo"/>
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="query.enrollmentYear != null  and query.enrollmentYear != ''">and enrollment_year = #{query.enrollmentYear}</if>
            <if test="query.gradeIn != null  and query.gradeIn != ''">and grade_in = #{query.gradeIn}</if>
            <if test="query.classNumber != null  and query.classNumber != ''">and class_number = #{query.classNumber}</if>
            <if test="query.classAlias != null  and query.classAlias != ''">and class_alias like concat('%', #{query.classAlias}, '%')</if>
            <if test="query.isDeleted != null  and query.isDeleted != ''">and is_deleted = #{query.isDeleted}</if>
            <if test="query.createById != null ">and create_by_id = #{query.createById}</if>
            <if test="query.updateById != null ">and update_by_id = #{query.updateById}</if>
            <if test="query.tenantId != null ">and tenant_id = #{query.tenantId}</if>
            <if test="query.params.useClass != null  and query.params.useClass != ''">
            and grade_in in
            <foreach item="gradeIn" collection="query.params.useClass.split(',')" open="(" separator="," close=")">
                #{gradeIn}
            </foreach>
            </if>
        </where>
        order by enrollment_year desc, grade_in asc, CAST(class_number AS UNSIGNED) asc
    </select>

    <select id="selectClassReInfoList" resultMap="SysClassSettingResult">
        <include refid="selectClassAndTeacherVo"/>
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="query.enrollmentYear != null  and query.enrollmentYear != ''">and c.enrollment_year = #{query.enrollmentYear}</if>
            <if test="query.gradeIn != null  and query.gradeIn != ''">and c.grade_in = #{query.gradeIn}</if>
            <if test="query.classNumber != null  and query.classNumber != ''">and c.class_number = #{query.classNumber}</if>
            <if test="query.classAlias != null  and query.classAlias != ''">and c.class_alias like concat('%', #{query.classAlias}, '%')</if>
            <if test="query.isDeleted != null  and query.isDeleted != ''">and c.is_deleted = #{query.isDeleted}</if>
            <if test="query.createById != null ">and c.create_by_id = #{query.createById}</if>
            <if test="query.params.beginCreateTime != null and query.params.beginCreateTime != ''">
                and date_format(c.create_time,'%y%m%d') &gt;= date_format(#{query.params.beginCreateTime},'%y%m%d')
            </if>
            <if test="query.params.endCreateTime != null and query.params.endCreateTime != ''">
                and date_format(c.create_time,'%y%m%d') &lt;= date_format(#{query.params.endCreateTime},'%y%m%d')
            </if>
            <if test="query.updateById != null ">and c.update_by_id = #{query.updateById}</if>
            <if test="query.tenantId != null ">and c.tenant_id = #{query.tenantId}</if>
            <if test="query.teacherName != null and query.teacherName != ''">and rel_1.teacher_name like concat('%', #{query.teacherName}, '%')</if>
            <if test="query.locationName != null and query.locationName != ''">and rel_3.location_name like concat('%', #{query.locationName}, '%')</if>
            <if test="query.params.useClass != null  and query.params.useClass != ''">
                and c.grade_in in
                <foreach item="gradeIn" collection="query.params.useClass.split(',')" open="(" separator="," close=")">
                    #{gradeIn}
                </foreach>
            </if>
        </where>
        <choose>
            <when test="query.params.sortType == '1'.toString()">
                order by enrollment_year desc, grade_in asc, CAST(class_number AS UNSIGNED) asc
            </when>
            <otherwise >
                order by create_time desc
            </otherwise>
        </choose>
    </select>

    <update id="updateDeletedStatusByIds">
        update sys_class_setting set is_deleted = #{query.isDeleted},
        update_by_id = #{query.updateById}, update_by = #{query.updateBy},
        update_time = #{query.updateTime} where id in
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <delete id="deleteSysClassSettingByIds" parameterType="String">
        delete from sys_class_setting where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectSysClassSettingBind" resultMap="SysClassSettingResult">
        SELECT
            s.*
        FROM
            sys_class_setting s
            LEFT JOIN sys_re_location_class c ON s.id = c.class_id
        WHERE
            c.location_id = #{locationId} AND c.is_deleted = '0' AND s.is_deleted = '0'
        order by s.enrollment_year desc, s.grade_in asc, CAST(s.class_number AS UNSIGNED) asc
    </select>

    <select id="selectSysClassSettingUnBind" resultMap="SysClassSettingResult">
        SELECT
            s.*
        FROM
            sys_class_setting s
        WHERE
            s.is_deleted = '0'
            AND s.id NOT IN (
            SELECT
                class_id
            FROM
                sys_re_location_class
        WHERE
            location_id = #{locationId} AND is_deleted = '0' GROUP BY class_id)
        and enrollment_year > YEAR(SYSDATE())-3
        order by s.enrollment_year desc, s.grade_in asc, CAST(s.class_number AS UNSIGNED) asc
    </select>

    <select id="selectSysReClassInfoListByParams" resultType="com.vctgo.system.domain.dto.SysClassInfoDto">
        select c.id, c.class_name className, c.enrollment_year enrollmentYear, c.grade_in gradeIn,
               c.class_number classNumber, c.is_deleted isDeleted,
               rel_2.main_teacher_name mainTeacherName, rel_2.assistant_teacher_name assistantTeacherName,
               rel_2.nurse_teacher_name nurseTeacherName, rel_2.childhood_name childhoodName
        from sys_class_setting c
                 left join (select rel_1.class_id,
                                   max(case when rel_1.bind_user_position = 1 then bind_user_name else null end) 'main_teacher_name',
                                   max(case when bind_user_position = 2 then bind_user_name else null end) 'assistant_teacher_name',
                                   max(case when bind_user_position = 3 then bind_user_name else null end) 'nurse_teacher_name',
                                   max(case when bind_user_position = 4 then bind_user_name else null end) 'childhood_name'
                            from
                                (select class_id, bind_user_position, GROUP_CONCAT(bind_user_name) bind_user_name
                                 from sys_re_class_user cu
                                 where cu.is_deleted = 0
                                <if test="params.userType != null  and params.userType != ''"> and cu.bind_user_position = #{params.userType}</if>
                                <if test="params.tenantId != null  and params.tenantId != ''"> and cu.tenant_id = #{params.tenantId}</if>
                                 group by class_id,bind_user_position) rel_1
                            group by class_id) rel_2 on rel_2.class_id= c.id
        where c.is_deleted = 0
        <if test="params.tenantId != null  and params.tenantId != ''"> and c.tenant_id = #{params.tenantId}</if>
        <if test="params.className != null  and params.className != ''"> and c.class_name like concat('%', #{params.className}, '%')</if>
        <if test="params.gradeIn != null  and params.gradeIn != ''"> and c.grade_in = #{params.gradeIn}</if>
        <if test="params.gradeIn != null  and params.gradeIn != ''"> and c.grade_in = #{params.gradeIn}</if>
        <if test="params.classNumber != null  and params.classNumber != ''">and c.class_number = #{params.classNumber}</if>
    </select>

    <select id="selectSysTreeClassList" resultType="com.vctgo.system.domain.dto.SysTreeClassInfo">
        select id code, tenant_name name, 1 level, '' enrollmentYear, '' gradeIn
        from sys_tenant where id = #{tenantId} and del_flag=0
        union all
        select DISTINCT CONCAT(grade_in,enrollment_year) code,
                        case grade_in
                            when 1 then CONCAT('小班' ,' ', enrollment_year)
                            when 2 then CONCAT('中班' ,' ', enrollment_year)
                            when 3 then CONCAT('大班' ,' ', enrollment_year)
                            else null end as name, 2 level,
        enrollment_year enrollmentYear, grade_in gradeIn
        from sys_class_setting
        where tenant_id = #{tenantId} and is_deleted=0
        union all
        select id code, class_name as name, 3 level, enrollment_year enrollmentYear, grade_in gradeIn
        from sys_class_setting
        where tenant_id = #{tenantId} and is_deleted=0
        order by gradeIn, enrollmentYear desc
    </select>

    <select id="selectClassTreeLevelOneList" resultType="com.vctgo.system.domain.dto.SysTreeClassInfo">
        select id code, tenant_name name, 1 level, '' enrollmentYear, '' gradeIn
        from sys_tenant where id = #{tenantId} and del_flag=0
    </select>

    <select id="selectClassTreeLevelTwoList" resultType="com.vctgo.system.domain.dto.SysTreeClassInfo">
        select DISTINCT CONCAT(grade_in,enrollment_year) code,
                        case grade_in
                            when 1 then CONCAT('小班' ,' ', enrollment_year)
                            when 2 then CONCAT('中班' ,' ', enrollment_year)
                            when 3 then CONCAT('大班' ,' ', enrollment_year)
                            else null end as name, 2 level,
                        enrollment_year enrollmentYear, grade_in gradeIn
        from sys_class_setting
        where tenant_id = #{tenantId} and is_deleted=0
        order by enrollmentYear desc, gradeIn
    </select>

    <select id="selectTreeClassChildList" resultType="com.vctgo.system.api.domain.dto.ObserveChildhoodNewDto">
        select DISTINCT CONCAT(c.grade_in,c.enrollment_year) code,
                        case c.grade_in
                            when 1 then CONCAT('小班' ,' ', c.enrollment_year)
                            when 2 then CONCAT('中班' ,' ', c.enrollment_year)
                            when 3 then CONCAT('大班' ,' ', c.enrollment_year)
                            else null end as gradeName,3 level,
                        c.enrollment_year enrollmentYear, c.grade_in gradeIn,
						rel_2.childhoodId,rel_2.childhoodName,
                       	c.class_name className,c.id classId,c.class_number classNumber
        from sys_class_setting c
         join (select class_id, bind_user_id childhoodId, bind_user_name childhoodName
                   from sys_re_class_user
                   where bind_user_position = '4' and is_deleted=0)rel_2 on c.id= rel_2.class_id
        where c.tenant_id = #{query.tenantId} and is_deleted=0
        <if test="query.gradeIn != null and query.gradeIn != ''">
            AND c.grade_in = #{query.gradeIn}
        </if>
        <if test="query.classNumber != null and query.classNumber != ''">
            AND c.class_number = #{query.classNumber}
        </if>
        <if test="query.childhoodName != null and query.childhoodName != ''">
            and rel_2.childhoodName like concat('%', #{query.childhoodName}, '%')
        </if>
        order by enrollmentYear desc, gradeIn
    </select>

    <select id="selectClassTreeLevelThreeList" resultType="com.vctgo.system.domain.dto.SysTreeClassInfo">
        select id code, class_name as name, 3 level, enrollment_year enrollmentYear, grade_in gradeIn
        from sys_class_setting
        where tenant_id = #{tenantId} and is_deleted=0
        order by enrollment_year desc, grade_in asc, CAST(class_number AS UNSIGNED) asc
    </select>

    <select id="getLocationIdList" resultType="java.lang.Long">
        select location_id from sys_re_location_class
        where is_deleted = 0 AND class_id in
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="getDownLocationList" resultType="com.vctgo.system.domain.vo.SysDownLocation">
        select
            class.id,
            CONCAT( CASE class.grade_in WHEN 1 THEN '小' WHEN 2 THEN '中' WHEN 3 THEN '大' END,  class.class_number, '班'
                ) name from sys_class_setting class
            left join sys_re_location_class lc on class.id =lc.class_id
            left join sys_location_setting location on location.id = lc.location_id
        where class.is_deleted = 0 and location.attribute =1 AND class.tenant_id = #{query.tenantId}
    </select>
    <select id="getClassListByLocationId" resultType="com.vctgo.system.api.domain.SysClassSetting">
        SELECT
            cs.*
        FROM
            sys_re_location_class rlc
                LEFT JOIN sys_class_setting cs ON cs.id = rlc.class_id
        WHERE
            rlc.is_deleted = 0
          AND rlc.is_deleted = 0
          AND rlc.location_id = #{locationId}
        ORDER BY grade_in,class_number
    </select>

    <select id="getClassListByLocationIdList" resultType="com.vctgo.system.domain.dto.SysClassSettingDto">
        select relo.location_id,cla.id cid,cla.enrollment_year,cla.grade_in
        from sys_re_location_class relo
        inner join sys_class_setting cla on cla.id=relo.class_id
        where cla.is_deleted=0 and relo.is_deleted=0
        AND relo.location_id IN
        <foreach collection="locationIds" item="id" index="i" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>

    <select id="getLocationByGradeIn" resultType="com.vctgo.system.api.domain.dto.ToySysClassSettingDto">
        select rlc.location_id
        from sys_re_location_class rlc
                 left join sys_location_setting ls on ls.id=rlc.location_id
                 left join sys_class_setting cs on cs.id=rlc.class_id
        where  cs.is_deleted='0' AND rlc.tenant_id=#{query.tenantId}
        <if test="query.gradeIn != null and query.gradeIn != ''">
            AND cs.grade_in = #{query.gradeIn}
        </if>

    </select>

    <select id="selectSysClassUserByUserId" resultType="com.vctgo.system.api.domain.SysClassSetting">
        SELECT
            s.id,
            s.enrollment_year,
            s.grade_in,
            s.class_number,
            s.class_name,
            s.class_alias,
            s.remark,
            s.is_deleted,
            s.create_by_id,
            s.create_by,
            s.create_time,
            s.update_by_id,
            s.update_by,
            s.update_time,
            s.tenant_id
        FROM
            sys_class_setting s
                INNER JOIN sys_re_class_user cu ON s.id = cu.class_id
        WHERE
            s.is_deleted = '0'
          AND cu.is_deleted = '0'
          AND cu.bind_user_id = #{userId}
          ORDER BY s.grade_in,s.class_number
    </select>

    <select id="selectLocationAndAreaByClassId" resultType="com.vctgo.system.api.domain.SysReLocationArea">
        SELECT
            ls.name locationName,
            la.location_id,
            la.area_id,
            s.area_name areaName
        FROM
            sys_re_location_class lc
            LEFT JOIN sys_re_location_area la ON lc.location_id = la.location_id
            LEFT JOIN sys_area_setting s ON la.area_id = s.area_id
            LEFT JOIN sys_location_setting ls ON lc.location_id = ls.id
        WHERE
            lc.is_deleted = '0'
            AND ls.attribute ='1'
            AND la.is_deleted = '0'
            <if test="classId != null and classId != ''">
               AND lc.class_id = #{classId}
            </if>
    </select>

    <select id="selectLocationAndAreaByGradeInAndClass" resultType="com.vctgo.system.api.domain.SysReLocationArea">
        SELECT
            ls.NAME locationName,
            la.location_id,
            la.area_id,
            s.area_name areaName
        FROM
        sys_re_location_class lc
        LEFT JOIN sys_re_location_area la ON lc.location_id = la.location_id AND la.is_deleted = '0'
        LEFT JOIN sys_area_setting s ON la.area_id = s.area_id AND s.is_deleted = '0'
        LEFT JOIN sys_location_setting ls ON lc.location_id = ls.id AND ls.is_deleted = '0'
        LEFT JOIN sys_class_setting cs ON cs.id = lc.class_id AND cs.is_deleted = '0'
        WHERE
        lc.is_deleted = '0'
        <if test="gradeIn != null and gradeIn != '' and gradeIn.length >0 ">
            AND cs.grade_in in
            <foreach item="grade" collection="gradeIn" open="(" separator="," close=")">
                #{grade}
            </foreach>
        </if>

        <if test="classIds != null and classIds != '' and classIds.length >0 ">
            AND lc.class_id in
            <foreach item="classId" collection="classIds" open="(" separator="," close=")">
                #{classId}
            </foreach>
        </if>
    </select>

    <select id="selectLocationAndAreaByLocationId" resultType="com.vctgo.system.api.domain.SysReLocationArea">
        SELECT
            s.name locationName,
            s.id location_id,
            s.attribute,
            sa.area_id ,
            sa.area_name areaName
        FROM
            sys_location_setting s
                INNER JOIN sys_re_location_area la ON s.id = la.location_id AND la.is_deleted = '0'
                INNER JOIN sys_area_setting sa ON la.area_id = sa.area_id AND sa.is_deleted = '0'
        WHERE s.is_deleted = '0'
        <if test="locationIds != null and locationIds != '' and locationIds.length >0 ">
            AND s.id in
            <foreach item="locationId" collection="locationIds" open="(" separator="," close=")">
                #{locationId}
            </foreach>
        </if>
    </select>

    <select id="selectAreaList" resultType="com.vctgo.system.api.domain.SysAreaSetting">
        SELECT
            s.*
        FROM
            sys_area_setting s
                LEFT JOIN sys_re_location_area la ON s.area_id = la.area_id AND la.is_deleted = '0'
                LEFT JOIN sys_re_location_class lc ON la.location_id = lc.location_id AND lc.is_deleted = '0'
        WHERE
            s.is_deleted = '0'
        <if test="classIds != null and classIds != '' and classIds.length >0 ">
            and lc.class_id in
            <foreach item="classId" collection="classIds" open="(" separator="," close=")">
                #{classId}
            </foreach>
        </if>
        <if test="locationIds != null and locationIds != '' and locationIds.length >0 ">
            and la.location_id in
            <foreach item="locationId" collection="locationIds" open="(" separator="," close=")">
                #{locationId}
            </foreach>
        </if>
    </select>

    <select id="selectClassListByLocationIds" resultType="com.vctgo.system.api.domain.SysClassSetting">
        SELECT
            cs.grade_in,
            cs.class_number,
            lc.location_id bindLocationString
        FROM
            sys_re_location_class lc
        INNER JOIN sys_class_setting cs ON lc.class_id = cs.id
        WHERE
            lc.is_deleted = '0' AND cs.is_deleted = '0'
        <if test="locationIds != null and locationIds != '' and locationIds.length >0 ">
            and lc.location_id in
            <foreach item="locationId" collection="locationIds" open="(" separator="," close=")">
                #{locationId}
            </foreach>
        </if>
    </select>

    <select id="selectLocationAndAreaList" resultType="com.vctgo.system.api.domain.SysReLocationArea">
        SELECT
            ls.name locationName,
            ls.id location_id,
            s.area_id,
            s.area_name areaName
        FROM
            sys_re_location_area la
                INNER JOIN sys_location_setting ls on la.location_id = ls.id
                INNER JOIN sys_area_setting s on la.area_id = s.area_id
        WHERE la.is_deleted ='0' and ls.is_deleted = '0' and s.is_deleted = '0'
    </select>

    <select id="getClassSetting" resultType="com.vctgo.system.api.domain.SysClassSetting">
        SELECT
            CONCAT( CASE grade_in WHEN 1 THEN '小' WHEN 2 THEN '中' WHEN 3 THEN '大' END,  class_number, '班') class_name,
        MAX(cs.create_time) as create_time
        FROM
            sys_re_location_class rlc
                LEFT JOIN sys_class_setting cs ON cs.id = rlc.class_id
        WHERE
        cs.is_deleted = 0
          AND rlc.is_deleted = 0
          AND rlc.location_id in
        <foreach item="locationId" collection="locationIds" open="(" separator="," close=")">
            #{locationId}
        </foreach> GROUP BY cs.id ORDER BY grade_in,class_number
    </select>

    <select id="selectClassSettingAndLocation"  resultType="com.vctgo.system.api.domain.SysClassSetting">
        SELECT
            cs.class_name,
            cs.id,
            cs.grade_in
        FROM
            sys_class_setting cs
        WHERE cs.is_deleted ='0'
        ORDER BY
            cs.grade_in,
            cs.class_number,
            cs.enrollment_year DESC
    </select>

    <select id="selectLocationList" resultType="com.vctgo.system.api.domain.SysLocationSetting">
        SELECT
            lc.class_id classId,
            ls.id,
            ls.NAME
        FROM
            sys_re_location_class lc
        LEFT JOIN sys_location_setting ls ON lc.location_id = ls.id AND ls.is_deleted = '0'
        WHERE
        lc.is_deleted = '0'
        and lc.class_id in
        <foreach item="classId" collection="classIds" open="(" separator="," close=")">
            #{classId}
        </foreach>
    </select>
    <select id="getClassSettingInfo" resultType="com.vctgo.system.api.domain.SysClassSetting">
        select * from sys_class_setting where id = #{id}
    </select>
    <select id="getClassSettingInfo" resultType="com.vctgo.system.api.domain.SysClassSetting">
        select * from sys_class_setting where id = #{id}
    </select>

    <select id="getAreaSetting" resultType="com.vctgo.system.api.domain.SysClassSetting">
        SELECT
            *
        FROM
            sys_class_setting
        WHERE
            is_deleted = '0'
    </select>
    <select id="getSysReLocationClassByClassId" resultType="com.vctgo.system.api.domain.SysReLocationClass">
        SELECT
        srlc.id,
        srlc.location_id,
        srlc.class_id,
        sls.attribute
        FROM
        sys_re_location_class srlc
        left join sys_location_setting sls
        on srlc.location_id = sls.id
        WHERE srlc.is_deleted = '0' and sls.is_deleted = '0' and sls.attribute = '1'
            AND srlc.class_id IN
            <foreach item="id" collection="classIds.split(',')" open="(" separator="," close=")">
                #{id}
            </foreach>
    </select>

    <select id="selectAreaByLocationIdList" resultType="com.vctgo.system.api.domain.SysAreaSetting">
        select s.area_id,s.area_name from sys_re_location_area a
        INNER JOIN sys_area_setting s ON s.area_id = a.area_id
        left join sys_location_setting l on a.location_id=l.id
        where 1=1
        AND a.is_deleted = '0'
        and l.attribute=1
        AND a.location_id in
        <foreach item="id" collection="locationIdList.split(',')" open="(" separator="," close=")">
            #{id}
        </foreach>
        AND s.is_deleted = '0'
        group by s.area_id,s.area_name

    </select>

    <select id="selectOthreArea" resultType="com.vctgo.system.api.domain.SysAreaSetting">
        select s.area_id,s.area_name from sys_re_location_area a
        INNER JOIN sys_area_setting s ON s.area_id = a.area_id
        left join sys_location_setting l on a.location_id=l.id
        where 1=1
        AND a.is_deleted = '0'
        and l.attribute != 1
        AND s.is_deleted = '0'
        and l.tenant_id=#{query.tenantId}
		group by s.area_id,s.area_name

    </select>

</mapper>
