<?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.SysLocationSettingMapper">

    <resultMap type="com.vctgo.system.api.domain.SysLocationSetting" id="SysLocationSettingResult">
        <result property="id" column="id"/>
        <result property="code" column="code"/>
        <result property="name" column="name"/>
        <result property="type" column="type"/>
        <result property="remark" column="remark"/>
        <result property="attribute" column="attribute"/>
        <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>

    <resultMap type="com.vctgo.system.api.domain.dto.SysLocationReClassDto" id="SysLocationReClassResult">
        <result property="id" column="id"/>
        <result property="locationId" column="location_id"/>
        <result property="locationName" column="location_name"/>
        <result property="classId" column="class_id"/>
        <result property="className" column="class_name"/>
        <result property="isDeleted" column="is_deleted"/>
        <result property="tenantId" column="tenant_id"/>
    </resultMap>

    <resultMap type="com.vctgo.system.api.domain.SysLocationSetting" id="sysLocationResult"
               extends="SysLocationSettingResult">
        <collection property="assiciationPutArea" ofType="java.lang.String" javaType="java.util.List"
                    column="{id=id}" select="selectAssiciationPutArea"
                    fetchType="eager"/>
        <collection property="assiciationClass" ofType="java.lang.String" javaType="java.util.List"
                    column="{id=id}" select="selectAssiciationClass"
                    fetchType="eager"/>
    </resultMap>

    <resultMap type="com.vctgo.system.api.domain.SysLocationSetting" id="distinguishAreaResult"
               extends="SysLocationSettingResult">
        <collection property="assiciationClass" ofType="java.lang.String" javaType="java.util.List"
                    column="{id=id,gradeIn=gradeIn,classNumber=classNumber}" select="distinguishAreaAssiciationClass"
                    fetchType="eager"/>
    </resultMap>

    <sql id="selectSysLocationSettingVo">
        select id, code, name, type, remark,attribute, is_deleted, create_by_id, create_by, create_time, update_by_id, update_by, update_time, tenant_id from sys_location_setting
    </sql>

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

    <select id="getSysLocationList" resultMap="SysLocationSettingResult">
        <include refid="selectSysLocationSettingVo"/>
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="query.code != null  and query.code != ''">and s.code = #{query.code}</if>
            <if test="query.name != null  and query.name != ''">and s.name like concat('%', #{query.name}, '%')</if>
            <if test="query.type != null  and query.type != ''">and s.type = #{query.type}</if>
            <if test="query.isDeleted != null  and query.isDeleted != ''">and s.is_deleted = #{query.isDeleted}</if>
            <if test="query.tenantId != null ">and s.tenant_id = #{query.tenantId}</if>
        </where>
    </select>

    <select id="selectAssiciationPutArea" resultType="com.vctgo.system.api.domain.SysAreaSetting">
        select s.* from sys_re_location_area a
        INNER JOIN sys_area_setting s ON s.area_id = a.area_id
        AND a.is_deleted = '0'
        AND a.location_id = #{id}
        AND s.is_deleted = '0'
    </select>

    <select id="selectAssiciationClass" resultType="com.vctgo.system.api.domain.SysClassSetting">
        select s.* from sys_re_location_class c
        INNER JOIN sys_class_setting s on s.id = c.class_id
        AND s.is_deleted='0'
        AND c.is_deleted='0'
        AND c.location_id = #{id}
    </select>

    <select id="distinguishAreaAssiciationClass" resultType="com.vctgo.system.api.domain.SysClassSetting">
        SELECT
        cs.*
        FROM
        sys_re_location_class c
        INNER JOIN sys_class_setting cs on cs.id = c.class_id and cs.is_deleted='0'
        <if test="gradeIn != null  and gradeIn != ''">and cs.grade_in = #{gradeIn}</if>
        <if test="classNumber != null  and classNumber != ''">and cs.class_number = #{classNumber}</if>
        WHERE c.is_deleted = '0' AND c.location_id = #{id}
    </select>

    <select id="selectSysLocationSettingList" resultMap="sysLocationResult">
        SELECT
        s.id,
        s.code,
        s.name,
        s.type,
        s.remark,
        s.attribute,
        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_location_setting s

        <if test="query.areaId != null  and query.areaId != ''">
            INNER JOIN sys_re_location_area a on s.id = a.location_id
        </if>
        <if test="(query.gradeIn != null  and query.gradeIn != '')
        || (query.classNumber != null  and query.classNumber != '')">
            INNER JOIN sys_re_location_class lc on s.id = lc.location_id
            INNER JOIN sys_class_setting cs on cs.id = lc.class_id
        </if>

        <where>
            <if test="(query.gradeIn != null  and query.gradeIn != '') || (query.classNumber != null  and query.classNumber != '')">
                and cs.is_deleted='0' and lc.is_deleted='0'
            </if>
            <if test="query.gradeIn != null  and query.gradeIn != ''">and cs.grade_in = #{query.gradeIn}</if>
            <if test="query.id != null  and query.id != ''">and s.id = #{query.id}</if>
            <if test="query.classNumber != null  and query.classNumber != ''">and cs.class_number =
                #{query.classNumber}
            </if>
            <if test="query.areaId != null  and query.areaId != ''">and a.area_id = #{query.areaId}</if>
            <if test="query.code != null  and query.code != ''">and s.code = #{query.code}</if>
            <if test="query.name != null  and query.name != ''">and s.name like concat('%', #{query.name}, '%')</if>
            <if test="query.type != null  and query.type != ''">and s.type = #{query.type}</if>
            <if test="query.isDeleted != null  and query.isDeleted != ''">and s.is_deleted = #{query.isDeleted}</if>
            <if test="query.createById != null ">and s.create_by_id = #{query.createById}</if>
            <if test="query.updateById != null ">and s.update_by_id = #{query.updateById}</if>
            <if test="query.tenantId != null ">and s.tenant_id = #{query.tenantId}</if>
            <if test="query.attribute != null ">and s.attribute = #{query.attribute}</if>
        </where>
        order by s.create_time desc
    </select>

    <select id="selectBookSysLocationSettingList" resultMap="sysLocationResult">
        SELECT
        s.id,
        s.code,
        s.name,
        s.type,
        s.remark,
        s.attribute,
        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_location_setting s

        <if test="query.areaId != null  and query.areaId != ''">
            INNER JOIN sys_re_location_area a on s.id = a.location_id
        </if>
        <if test="(query.gradeIn != null  and query.gradeIn != '')
        || (query.classNumber != null  and query.classNumber != '')">
            INNER JOIN sys_re_location_class lc on s.id = lc.location_id
            INNER JOIN sys_class_setting cs on cs.id = lc.class_id
        </if>

        <where>
            <if test="(query.gradeIn != null  and query.gradeIn != '') || (query.classNumber != null  and query.classNumber != '')">
                and cs.is_deleted='0' and lc.is_deleted='0'
            </if>
            <if test="query.gradeIn != null  and query.gradeIn != ''">and cs.grade_in = #{query.gradeIn}</if>
            <if test="query.attribute !=1 and query.id != null  and query.id != ''">and s.id = #{query.id}</if>
            <if test="query.classNumber != null  and query.classNumber != ''">and cs.class_number =
                #{query.classNumber}
            </if>
            <if test="query.areaId != null  and query.areaId != ''">and a.area_id = #{query.areaId}</if>
            <if test="query.code != null  and query.code != ''">and s.code = #{query.code}</if>
            <if test="query.name != null  and query.name != ''">and s.name like concat('%', #{query.name}, '%')</if>
            <if test="query.type != null  and query.type != ''">and s.type = #{query.type}</if>
            <if test="query.isDeleted != null  and query.isDeleted != ''">and s.is_deleted = #{query.isDeleted}</if>
            <if test="query.createById != null ">and s.create_by_id = #{query.createById}</if>
            <if test="query.updateById != null ">and s.update_by_id = #{query.updateById}</if>
            <if test="query.tenantId != null ">and s.tenant_id = #{query.tenantId}</if>
            <if test="query.attribute != null ">and s.attribute = #{query.attribute}</if>
            <if test="query.attribute ==1 and query.id != null and query.id != '' ">
                and s.id in (SELECT DISTINCT lc.location_id FROM sys_re_location_class lc  WHERE lc.is_deleted = '0' and lc.class_id = #{query.id})
            </if>
        </where>
        order by s.create_time desc
    </select>

    <select id="listDistinguishArea" resultMap="distinguishAreaResult">
        SELECT
        s.id,
        s.code,
        s.name,
        s.type,
        s.remark,
        s.attribute,
        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,
        a.area_name areaName,
        a.area_id areaId,
        #{query.gradeIn} gradeIn,
        #{query.classNumber} classNumber
        FROM
        sys_location_setting s
        <choose>
            <when test="query.areaId != null  and query.areaId != ''">
                INNER JOIN (select a.location_id,sas.* from sys_re_location_area a INNER JOIN sys_area_setting sas
                ON sas.area_id = a.area_id
                AND a.is_deleted = '0'
                AND sas.is_deleted = '0'
                AND sas.area_id=#{query.areaId}) a on s.id = a.location_id
            </when>
            <otherwise>
                LEFT JOIN (select a.location_id,sas.* from sys_re_location_area a INNER JOIN sys_area_setting sas
                ON sas.area_id = a.area_id
                AND a.is_deleted = '0'
                AND sas.is_deleted = '0') a on s.id = a.location_id
            </otherwise>
        </choose>
        left join (select location_id,COUNT(cs.id) as num from sys_re_location_class rlc left join sys_class_setting cs
        on rlc.class_id = cs.id where cs.is_deleted ='0' and rlc.is_deleted ='0'
        GROUP BY rlc.location_id ) tem on tem.location_id = s.id
        <where>
            <if test="query.gradeIn != null and query.gradeIn != ''">
                AND s.id in (select location_id from sys_re_location_class lc
                INNER JOIN sys_class_setting cs on cs.id = lc.class_id and cs.is_deleted='0' and lc.is_deleted='0'
                AND cs.grade_in = #{query.gradeIn})
            </if>
            <if test="query.classNumber != null and query.classNumber != ''">
                AND s.id in (select location_id from sys_re_location_class lc
                INNER JOIN sys_class_setting cs on cs.id = lc.class_id and cs.is_deleted='0' and lc.is_deleted='0'
                AND cs.class_number = #{query.classNumber})
            </if>
            <if test="query.id != null  and query.id != ''">and s.id = #{query.id}</if>
            <if test="query.code != null  and query.code != ''">and s.code = #{query.code}</if>
            <if test="query.name != null  and query.name != ''">and s.name like concat('%', #{query.name}, '%')</if>
            <if test="query.type != null  and query.type != ''">and s.type = #{query.type}</if>
            <if test="query.attribute != null  and query.attribute != ''">and s.attribute = #{query.attribute}</if>
            <if test="query.isDeleted != null  and query.isDeleted != ''">and s.is_deleted = #{query.isDeleted}</if>
            <if test="query.createById != null ">and s.create_by_id = #{query.createById}</if>
            <if test="query.updateById != null ">and s.update_by_id = #{query.updateById}</if>
            <if test="query.tenantId != null ">and s.tenant_id = #{query.tenantId}</if>
            and s.is_deleted = '0' and (s.attribute!=1 or s.attribute is null or (s.attribute=1 and tem.num>0))
        </where>
    </select>

    <select id="locationIdList" resultMap="sysLocationResult">
        SELECT
        s.id,
        s.name
        FROM
        sys_location_setting s
        <where>
            <if test="query.areaId != null  and query.areaId != ''">
                AND s.id in (select a.location_id from sys_re_location_area a
                INNER JOIN sys_area_setting sas
                ON sas.area_id = a.area_id
                AND a.is_deleted = '0'
                AND sas.is_deleted = '0'
                AND sas.area_id=#{query.areaId})
            </if>
            <if test="query.gradeIn != null and query.gradeIn != ''">
                AND s.id in (select location_id from sys_re_location_class lc
                INNER JOIN sys_class_setting cs
                on cs.id = lc.class_id
                and cs.is_deleted='0'
                and lc.is_deleted='0'
                AND cs.grade_in = #{query.gradeIn})
            </if>
            <if test="query.classNumber != null and query.classNumber != ''">
                AND s.id in (select location_id from sys_re_location_class lc
                INNER JOIN sys_class_setting cs
                on cs.id = lc.class_id
                and cs.is_deleted='0'
                and lc.is_deleted='0'
                AND cs.class_number = #{query.classNumber})
            </if>
            <if test="query.code != null  and query.code != ''">and s.code = #{query.code}</if>
            <if test="query.name != null  and query.name != ''">and s.name like concat('%', #{query.name}, '%')</if>
            <if test="query.type != null  and query.type != ''">and s.type = #{query.type}</if>
            <if test="query.isDeleted != null  and query.isDeleted != ''">and s.is_deleted = #{query.isDeleted}</if>
            <if test="query.createById != null ">and s.create_by_id = #{query.createById}</if>
            <if test="query.updateById != null ">and s.update_by_id = #{query.updateById}</if>
            <if test="query.tenantId != null ">
                AND s.id in (select location_id from sys_re_location_class lc
                INNER JOIN sys_class_setting cs
                on cs.id = lc.class_id
                and cs.is_deleted='0'
                and lc.is_deleted='0'
                and cs.tenant_id = #{query.tenantId}
            </if>
        </where>
    </select>

    <select id="getRelatedClassList" resultMap="SysLocationReClassResult">
        select rlc.location_id,max(ls.name) location_name,GROUP_CONCAT(rlc.class_id) class_id1,
        GROUP_CONCAT(CONCAT(
        case cs.grade_in when 1 then '小' when 2 then '中' when 3 then '大' end,cs.class_number),'班') class_name,
        ls.tenant_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 rlc.is_deleted=#{query.isDeleted} and cs.is_deleted='0'
        <if test="query.classId != null ">and cs.id = #{query.classId}</if>
        <if test="query.tenantId != null ">and rlc.tenant_id = #{query.tenantId}</if>
        group by rlc.location_id
    </select>

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

    <select id="locationAndAreaList" resultType="com.vctgo.system.api.domain.SysLocationSetting">
        SELECT
            id,
            name,
            '1' type,
            attribute
        FROM
            sys_location_setting WHERE tenant_id = #{tenantId}
        UNION
        SELECT
            area_id id,
            area_name name,
            '2' type,
            'attribute' attribute
        FROM
            sys_area_setting  WHERE tenant_id = #{tenantId}
    </select>

    <select id="getRelatedLocationList" resultType="com.vctgo.system.api.domain.dto.SysLocationReClassDto">
        select rlc.location_id,ls.name location_name,rlc.class_id class_id,
        ls.tenant_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 rlc.is_deleted=0 AND ls.attribute = 1
        AND cs.is_deleted='0'
        AND rlc.class_id in
        <foreach item="id" collection="query.classIdList" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="getDownLocationList" resultType="com.vctgo.system.domain.vo.SysDownLocation">
        SELECT
            id as id,
            name as name
        FROM
            sys_location_setting  WHERE is_deleted = 0 AND  tenant_id = #{query.tenantId}
    </select>

    <select id="getLocationSettingInfo" resultType="com.vctgo.system.api.domain.SysLocationSetting">
        select * from sys_location_setting where is_deleted = 0
        <if test="attribute != null and attribute != '' ">and attribute = #{attribute}</if>
    </select>
</mapper>
