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

    <resultMap type="com.vctgo.system.domain.SysBranchKindergarten" id="SysBranchKindergartenResult">
        <result property="id" column="id"/>
        <result property="kindergartenName" column="kindergarten_name"/>
        <result property="kindergartenType" column="kindergarten_type"/>
        <result property="isGarden" column="is_garden"/>
        <result property="gardenId" column="garden_id"/>
        <result property="simpleName" column="simple_name"/>
        <result property="englishName" column="english_name"/>
        <result property="principal" column="principal"/>
        <result property="principalPhone" column="principal_phone"/>
        <result property="legalPerson" column="legal_person"/>
        <result property="fileId" column="file_id"/>
        <result property="logoUrl" column="logo_url"/>
        <result property="belongRegionCode" column="belong_region_code"/>
        <result property="provinceId" column="province_id"/>
        <result property="cityId" column="city_id"/>
        <result property="districtId" column="district_id"/>
        <result property="belongRegion" column="belong_region"/>
        <result property="detailAddress" column="detail_address"/>
        <result property="locationAddress" column="location_address"/>
        <result property="latitudeLongitude" column="latitude_longitude"/>
        <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="selectSysBranchKindergartenVo">
        select id, kindergarten_name, kindergarten_type, is_garden, garden_id,
               simple_name, english_name, principal, principal_phone, legal_person,
               file_id, logo_url, belong_region_code, province_id, city_id, district_id,
               belong_region, detail_address, location_address, latitude_longitude,
               remark, is_deleted, create_by_id, create_by, create_time,
               update_by_id, update_by, update_time, tenant_id from sys_branch_kindergarten
    </sql>

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

    <select id="selectSysBranchKindergartenList" resultMap="SysBranchKindergartenResult">
        SELECT
            k.id,
            k.kindergarten_name,
            k.kindergarten_type,
            k.is_garden,
            k.garden_id,
            k.simple_name,
            k.english_name,
            k.principal,
            k.principal_phone,
            k.legal_person,
            k.file_id,
            k.logo_url,
            k.belong_region_code,
            k.province_id,
            k.city_id,
            k.district_id,
            k.belong_region,
            k.detail_address,
            k.location_address,
            k.latitude_longitude,
            k.remark,
            k.is_deleted,
            k.create_by_id,
            k.create_time,
            k.update_by_id,
            k.update_by,
            k.update_time,
            k.tenant_id,
            u.nick_name create_by
        FROM
        sys_branch_kindergarten k
        LEFT JOIN sys_platform_user u on k.create_by_id = u.user_id
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="query.belongRegion != null  and query.belongRegion != ''">
                and k.belong_region like concat('%', #{query.belongRegion}, '%')
            </if>
            <if test="query.kindergartenName != null  and query.kindergartenName != ''">
                and k.kindergarten_name like concat('%', #{query.kindergartenName}, '%')
            </if>
            <if test="query.kindergartenType != null  and query.kindergartenType != ''">
                and k.kindergarten_type = #{query.kindergartenType}
            </if>
            <if test="query.gardenId != null  and query.gardenId != ''">
                and k.garden_id = #{query.gardenId}
            </if>
            <if test="query.createBy != null and query.createBy != ''">
                and k.create_by_id in (select pu.user_id from sys_platform_user pu where pu.nick_name like concat('%',
                #{query.createBy}, '%'))
            </if>
            <if test="query.params.excludeBranchIdList != null  and query.params.excludeBranchIdList.size >0 ">
                and k.id not in
                <foreach collection="query.params.excludeBranchIdList" item="branchId" index="i" open="(" close=")"
                         separator=",">
                    #{branchId}
                </foreach>
            </if>
            <if test="query.params.includeBranchIdList != null  and query.params.includeBranchIdList.size >0 ">
                and k.id in
                <foreach collection="query.params.includeBranchIdList" item="branchId" index="i" open="(" close=")"
                         separator=",">
                    #{branchId}
                </foreach>
            </if>
            <if test="query.params.beginTime != null and query.params.beginTime != ''">
                AND date_format(k.create_time,'%y%m%d') &gt;= date_format(#{query.params.beginTime},'%y%m%d')
            </if>
            <if test="query.params.endTime != null and query.params.endTime != ''">
                AND date_format(k.create_time,'%y%m%d') &lt;= date_format(#{query.params.endTime},'%y%m%d')
            </if>
        </where>
        order by k.create_time desc
    </select>

    <select id="selectBranchListByParams" resultMap="SysBranchKindergartenResult">
        <include refid="selectSysBranchKindergartenVo"/>
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="params.gardenId != null  and params.gardenId != ''">and garden_id = #{params.gardenId}</if>
            <if test="params.isGarden != null  and params.isGarden != ''">and is_garden = #{params.isGarden}</if>
            <if test="params.name != null  and params.name != ''">and kindergarten_name = #{params.name}</if>
            <if test="params.id != null  and params.id != ''">and id != #{params.id}</if>
            <if test="params.verifyStatus!= null  and params.verifyStatus != ''">and verify_status = #{params.verifyStatus}</if>

            <if test="params.idColl != null  and params.idColl != ''">and garden_id in
                <foreach item="gardenId" collection="params.idColl" open="(" separator="," close=")">
                    #{gardenId}
                </foreach>
            </if>
            <if test="params.type != null  and params.type != ''">and kindergarten_type = #{params.type}</if>
        </where>
    </select>

    <update id="updateDeletedStatusByIds">
        update sys_branch_kindergarten 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="deleteSysBranchKindergartenByIds" parameterType="String">
        delete from sys_branch_kindergarten where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectKindergartenList" resultType="com.vctgo.system.domain.SysBranchKindergarten">
       SELECT
           *
       FROM
           ((
                SELECT
                    bk.id,
                    bk.kindergarten_name,
                    bk.kindergarten_type,
                    bk.province_id,
                    bk.city_id,
                    bk.district_id,
                    ( CASE WHEN t.id IS NULL THEN '1' ELSE '2' END ) AS accountStatus,t.create_by_id
                FROM
                    sys_branch_kindergarten bk
                        LEFT JOIN ( SELECT * FROM sys_tenant WHERE del_flag = '0' AND (tenant_type = '2' or tenant_type = '3')) t ON bk.id = t.branch_id
                WHERE
                    bk.is_deleted = '0'
            ) UNION
            (
                SELECT
                    gm.id,
                    gm.NAME kindergarten_name,
                    '3' kindergarten_type,
                    gm.province_id,
                    gm.city_id,
                    gm.district_id,
                    ( CASE WHEN t.id IS NULL THEN '1' ELSE '2' END ) AS accountStatus,t.create_by_id
                FROM
                    sys_garden_manage gm
                        LEFT JOIN ( SELECT * FROM sys_tenant WHERE del_flag = '0' AND tenant_type = '1' ) t ON gm.id = t.garden_id
                WHERE
                    gm.is_deleted = '0'
            )
           ) sys_kindergarten
       ORDER BY
           kindergarten_type DESC
    </select>

    <select id="selectBranchKindergartenList" resultType="com.vctgo.system.domain.SysBranchKindergarten">
        SELECT bk.id, bk.kindergarten_name, bk.kindergarten_type,bk.province_id,bk.city_id,bk.district_id
        FROM sys_branch_kindergarten bk
        WHERE bk.is_deleted = '0'
    </select>

    <select id="selectGardenKindergartenList" resultType="com.vctgo.system.domain.SysBranchKindergarten">
        SELECT gm.id,gm.NAME kindergarten_name,'3' kindergarten_type,gm.province_id,gm.city_id,gm.district_id
        FROM sys_garden_manage gm
        WHERE gm.is_deleted = '0'
    </select>
</mapper>
