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

    <resultMap type="com.vctgo.system.domain.SysGardenManage" id="SysGardenManageResult">
        <result property="id"    column="id"    />
        <result property="name"    column="name"    />
        <result property="branchCount"    column="branch_count"    />
        <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="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="selectSysGardenManageVo">
        select id, name, branch_count, province_id, city_id, district_id,belong_region, remark, is_deleted, create_by_id, create_by, create_time, update_by_id, update_by, update_time, tenant_id from sys_garden_manage
    </sql>

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

    <select id="selectSysGardenManageList"   resultMap="SysGardenManageResult">
        SELECT
            gm.id,
            gm.NAME,
            gm.branch_count,
            gm.province_id,
            gm.city_id,
            gm.district_id,
            gm.belong_region,
            gm.remark,
            gm.is_deleted,
            gm.create_by_id,
            gm.create_by,
            gm.create_time,
            gm.update_by_id,
            gm.update_by,
            gm.update_time,
            gm.tenant_id,
            t.status accountStatus
        FROM
            sys_garden_manage gm
        LEFT JOIN sys_tenant t on gm.id = t.garden_id and t.del_flag='0' and t.tenant_type='1'
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="query.name != null  and query.name != ''"> and gm.name like concat('%', #{query.name}, '%')</if>
            <if test="query.provinceId != null  and query.provinceId != ''"> and gm.province_id = #{query.provinceId}</if>
            <if test="query.cityId != null  and query.cityId != ''"> and gm.city_id = #{query.cityId}</if>
            <if test="query.districtId != null  and query.districtId != ''"> and gm.district_id = #{query.districtId}</if>
            <if test="query.accountStatus == 0 or query.accountStatus == 1 "> and t.status = #{query.accountStatus}</if>
            <if test="query.accountStatus == 2 "> and t.status is null </if>
            <if test="query.params.beginTime != null and query.params.beginTime != ''">
                AND date_format(gm.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(gm.create_time,'%y%m%d') &lt;= date_format(#{query.params.endTime},'%y%m%d')
            </if>
        </where>
        order by gm.create_time desc
    </select>

    <select id="selectGardenListByParams"   resultMap="SysGardenManageResult">
        <include refid="selectSysGardenManageVo"/>
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="params.name != null  and params.name != ''"> and name = #{params.name}</if>
            <if test="params.id != null  and params.id != ''"> and id != #{params.id}</if>
        </where>
    </select>

    <update id="updateBranchCount">
        update sys_garden_manage set
        <choose>
            <when test = "plus != null and plus == '1'.toString()">
                branch_count = branch_count+1
            </when>
            <otherwise>
                branch_count = branch_count-1
            </otherwise>
        </choose>
        where id in
        <foreach collection="ids" item="id" index="index" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <update id="multiUpdateBranchCount">
        update sys_garden_manage g,
            (select gm.id, IFNULL(rel.count,0) count
            from sys_garden_manage gm
            left join
            (select garden_id,count(id) count from sys_branch_kindergarten
            where is_deleted='0' group by garden_id) rel
            on gm.id = rel.garden_id) tmp
        set g.branch_count = tmp.count
        where g.is_deleted = '0' and g.id = tmp.id

    </update>

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

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