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

    <resultMap type="com.vctgo.system.api.domain.SysTenant" id="SysTenantResult">
        <result property="id"    column="id"    />
        <result property="tenantType"    column="tenant_type"    />
        <result property="tenantName"    column="tenant_name"    />
        <result property="gardenId"    column="garden_id"    />
        <result property="branchId"    column="branch_id"    />
        <result property="userName"    column="user_name"    />
        <result property="userPhone"    column="user_phone"    />
        <result property="userEmail"    column="user_email"    />
        <result property="tenantPackage"    column="tenant_package"    />
        <result property="status"    column="status"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="tenantStartTime"    column="tenant_start_time"    />
        <result property="tenantEndTime"    column="tenant_end_time"    />
        <result property="longTerm"    column="long_term"    />
        <result property="sysService"    column="sysService"    />
    </resultMap>

    <sql id="selectSysTenantVo">
        select id, tenant_type, tenant_name, garden_id, branch_id, user_name, user_phone, user_email,
               tenant_package, status, tenant_start_time, tenant_end_time, long_term, del_flag,
               create_by, create_time, update_by, update_time, remark from sys_tenant
    </sql>

    <select id="selectSysTenantList"   resultMap="SysTenantResult">
        SELECT
            st.*,
            su.nick_name createNickName, (
        SELECT
            GROUP_CONCAT( DISTINCT ss.service_name )
        FROM
            sys_service ss
            INNER JOIN sys_re_service_tenant srst ON srst.service_id = ss.id
        WHERE
            srst.tenant_id = st.id and srst.is_deleted='0' AND ss.is_deleted = '0'
        ) sysService
        FROM sys_tenant st
        LEFT JOIN sys_platform_user su ON st.create_by_id = su.user_id
        <where>
            <if test="query.tenantType != null  and query.tenantType != ''"> and st.tenant_type = #{query.tenantType}</if>
            <if test="query.tenantName != null  and query.tenantName != ''"> and st.tenant_name like concat('%', #{query.tenantName}, '%')</if>
            <if test="query.gardenId != null  and query.gardenId != ''"> and st.garden_id = #{query.gardenId}</if>
            <if test="query.branchId != null  and query.branchId != ''"> and st.branch_id = #{query.branchId}</if>
            <if test="query.userName != null  and query.userName != ''"> and st.user_name like concat('%', #{query.userName}, '%')</if>
            <if test="query.userPhone != null  and query.userPhone != ''"> and st.user_phone = #{query.userPhone}</if>
            <if test="query.status != null"> and st.status = #{query.status}</if>
            <if test="query.params.beginCreateTime != null and query.params.beginCreateTime != ''">
                and date_format(st.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(st.create_time,'%y%m%d') &lt;= date_format(#{query.params.endCreateTime},'%y%m%d')
            </if>
            <if test="query.params.tenantLevel != null and query.params.tenantLevel == '1'.toString()">
                and garden_id in (SELECT garden_id FROM sys_tenant where id = #{query.params.tenantId} and tenant_type = 1)
            </if>
            <if test="query.params.tenantTypeRange != null and query.params.tenantTypeRange.size() > 0">
                and st.tenant_type in (1, 3)
            </if>
            <choose>
                <when test="query.params.tenantLevel == '1'.toString()">
                    <if test="query.params.tenantId != null  and query.params.tenantId">
                        and st.garden_id in (SELECT garden_id FROM sys_tenant where id = #{query.params.tenantId})
                        order by tenant_type
                    </if>
                </when>
                <when test="query.params.tenantLevel == '2'.toString()">
                    <if test="query.params.tenantId != null  and query.params.tenantId">
                        and st.id = #{query.params.tenantId}
                    </if>
                </when>
                <otherwise >

                </otherwise>
            </choose>
        </where>
        order by st.create_time desc
    </select>

    <select id="selectSysTenantTree" resultType="com.vctgo.system.api.domain.SysTenant">
        SELECT
            t.id,t.tenant_name,gb.parent_id
        FROM
            sys_tenant t
                INNER JOIN sys_re_garden_branch gb ON t.id = gb.id and t.del_flag='0' and gb.is_deleted='0'
    </select>

    <select id="selectBranchTenantList" resultMap="SysTenantResult">
        select * from sys_tenant
        where garden_id = #{gardenId} and tenant_type = '2' and del_flag = '0' and status = '0'
    </select>

    <update id="updateSysTenantRelaData">
        update sys_tenant set garden_id = #{gardenId}, tenant_type = #{tenantType}
        where branch_id = #{branchId} and del_flag = '0'
    </update>

    <update id="updateSysGardenTenantName">
        update sys_tenant set tenant_name = #{tenantName}
        where garden_id = #{gardenId} and tenant_type = '1' and del_flag = '0'
    </update>

    <select id="getMaxTenantId" resultType="java.lang.Long" >
        select max(id) id from sys_tenant
    </select>

    <select id="getParentTenantInfo" parameterType="Long" resultMap="SysTenantResult">
        <include refid="selectSysTenantVo"/>
        <where>
            (garden_id in (select garden_id from sys_tenant where id= #{tenantId}) and tenant_type =1)
            or (branch_id in (select branch_id from sys_tenant where id = #{tenantId}) and tenant_type =3)
            and del_flag = '0'
        </where>
    </select>

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

    <select id="selectTenantInfoById" resultType="com.vctgo.system.api.domain.SysTenant">
        SELECT
            t.*,
            bk.logo_url
        FROM
            sys_tenant t
                LEFT JOIN sys_branch_kindergarten bk ON t.branch_id = bk.id where t.id = #{tenantId}
    </select>

    <select id="selectTenantByPhone" resultType="com.vctgo.system.api.domain.SysTenant">
        SELECT
            t.tenant_type,
            t.garden_id,
            t.branch_id,
            gm.NAME gardenName,
            bk.kindergarten_name kindergartenName
        FROM
            sys_tenant t
                INNER JOIN sys_user u ON t.id = u.tenant_id
                LEFT JOIN sys_garden_manage gm ON t.garden_id = gm.id
                LEFT JOIN sys_branch_kindergarten bk ON t.branch_id = bk.id
        WHERE
            u.del_flag = '0'
          AND u.user_status = '0'
          AND u.phonenumber = #{phone}
    </select>

    <select id="slectTenantByPhoneList" resultType="com.vctgo.system.api.domain.SysTenant">
        SELECT
        t.tenant_type,
        t.garden_id,
        t.branch_id,
        t.status,
        u.phonenumber,
        u.account_status
        FROM
        sys_user u
        INNER JOIN sys_tenant t ON u.tenant_id = t.id AND t.del_flag = '0'
        WHERE
        u.del_flag = '0'
        AND u.phonenumber in
        <foreach item="phone" collection="array" open="(" separator="," close=")">
            #{phone}
        </foreach>
    </select>

    <select id="selectTenantList" resultType="com.vctgo.system.api.domain.SysTenant">
        SELECT
        t.id,
        bk.kindergarten_name kindergartenName,
        st.create_time
        FROM
        sys_tenant t
        INNER JOIN sys_branch_kindergarten bk ON t.branch_id = bk.id
        INNER JOIN sys_re_service_tenant st ON t.id = st.tenant_id
        WHERE
        t.del_flag = '0'
        AND bk.is_deleted = '0'
        AND st.is_deleted = '0'
        AND st.service_id = (SELECT  id FROM sys_service WHERE service_name = '玩具服务')
        <if test="query.kindergartenName != null  and query.kindergartenName != ''">
            AND bk.kindergarten_name LIKE concat('%', #{query.kindergartenName}, '%')
        </if>
        <if test="query.tenantStartTime != null">
            AND date_format(st.create_time,'%y%m%d') &gt;= date_format(#{query.tenantStartTime},'%y%m%d')
        </if>
        <if test="query.tenantEndTime != null">
            AND date_format(st.create_time,'%y%m%d') &lt;= date_format(#{query.tenantEndTime},'%y%m%d')
        </if>
    </select>
</mapper>
