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

    <resultMap type="com.vctgo.system.domain.SysChildhoodManage" id="SysChildhoodManageResult">
        <result property="id"    column="id"    />
        <result property="name"    column="name"    />
        <result property="sex"    column="sex"    />
        <result property="birthdayDate"    column="birthday_date"    />
        <result property="identityNumber"    column="identity_number"    />
        <result property="nation"    column="nation"    />
        <result property="area"    column="area"    />
        <result property="address"    column="address"    />
        <result property="healthStatus"    column="health_status"    />
        <result property="isSpecial"    column="is_special"    />
        <result property="inKindergartenStatus"    column="in_kindergarten_status"    />
        <result property="fileId"    column="file_id"    />
        <result property="profilePhotoUrl"    column="profile_photo_url"    />
        <result property="classId"    column="class_id"    />
        <result property="admissionDate"    column="admission_date"    />
        <result property="leavingDate"    column="leaving_date"    />
        <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"    />
        <result property="areaName"    column="area_name"    />
    </resultMap>

    <sql id="selectSysChildhoodManageVo">
        select id, name, sex, birthday_date, identity_number, nation, area, address, health_status, is_special, in_kindergarten_status, file_id, profile_photo_url, class_id, admission_date, leaving_date, remark, is_deleted, create_by_id, create_by, create_time, update_by_id, update_by, update_time, tenant_id from sys_childhood_manage
    </sql>

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

    <select id="selectSysChildhoodManageById" resultMap="SysChildhoodManageResult">
        SELECT
            cm.id,
            cm.NAME,
            cm.sex,
            cm.birthday_date,
            cm.identity_number,
            cm.nation,
            cm.area,
            (SELECT REPLACE( GROUP_CONCAT( pd.province ), ',', '/' ) FROM sys_provincial_division pd WHERE FIND_IN_SET( pd.province_id, cm.area )) area_name,
            cm.address,
            cm.health_status,
            cm.is_special,
            cm.in_kindergarten_status,
            cm.file_id,
            cm.profile_photo_url,
            cm.class_id,
            cm.admission_date,
            cm.leaving_date,
            cm.remark,
            cm.is_deleted,
            cm.create_by_id,
            cm.create_by,
            cm.create_time,
            cm.update_by_id,
            cm.update_by,
            cm.update_time,
            cm.tenant_id,
            CONCAT(cs.class_name,'(',cs.enrollment_year,')') className
        FROM
            sys_childhood_manage cm
        LEFT JOIN sys_class_setting cs on cm.class_id = cs.id
        WHERE cm.id = #{id}  and cm.is_deleted='0'
    </select>

    <select id="selectSysChildhoodManageList"   resultMap="SysChildhoodManageResult">
        <include refid="selectSysChildhoodManageVo"/>
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="query.name != null  and query.name != ''"> and name like concat('%', #{query.name}, '%')</if>
            <if test="query.sex != null  and query.sex != ''"> and sex = #{query.sex}</if>
            <if test="query.identityNumber != null  and query.identityNumber != ''"> and identity_number = #{query.identityNumber}</if>
            <if test="query.nation != null  and query.nation != ''"> and nation = #{query.nation}</if>
            <if test="query.area != null  and query.area != ''"> and area = #{query.area}</if>
            <if test="query.address != null  and query.address != ''"> and address = #{query.address}</if>
            <if test="query.healthStatus != null  and query.healthStatus != ''"> and health_status = #{query.healthStatus}</if>
            <if test="query.isSpecial != null  and query.isSpecial != ''"> and is_special = #{query.isSpecial}</if>
            <if test="query.inKindergartenStatus != null  and query.inKindergartenStatus != ''"> and in_kindergarten_status = #{query.inKindergartenStatus}</if>
            <if test="query.fileId != null "> and file_id = #{query.fileId}</if>
            <if test="query.classId != null "> and class_id = #{query.classId}</if>
            <if test="query.createById != null "> and create_by_id = #{query.createById}</if>
            <if test="query.tenantId != null "> and tenant_id = #{query.tenantId}</if>
            <if test="query.params.childStatus != null  and query.params.childStatus != ''">
                and IFNULL(in_kindergarten_status,99) <![CDATA[ <> ]]> #{query.params.childStatus}
            </if>
            <if test="query.params.beginBirthdayDate != null and query.params.beginBirthdayDate != ''">
                AND date_format(birthday_date,'%y%m%d') &gt;= date_format(#{query.params.beginBirthdayDate},'%y%m%d')
            </if>
            <if test="query.params.beginBirthdayDate != null and query.params.beginBirthdayDate != ''">
                AND date_format(birthday_date,'%y%m%d') &gt;= date_format(#{query.params.beginBirthdayDate},'%y%m%d')
            </if>
            <if test="query.params.endBirthdayDate != null and query.params.endBirthdayDate != ''">
                AND date_format(birthday_date,'%y%m%d') &lt;= date_format(#{query.params.endBirthdayDate},'%y%m%d')
            </if>
            <if test="query.params.beginAdmissionDate != null and query.params.beginAdmissionDate != ''">
                AND date_format(admission_date,'%y%m%d') &gt;= date_format(#{query.params.beginAdmissionDate},'%y%m%d')
            </if>
            <if test="query.params.endAdmissionDate != null and query.params.endAdmissionDate != ''">
                AND date_format(admission_date,'%y%m%d') &lt;= date_format(#{query.params.endAdmissionDate},'%y%m%d')
            </if>
            <if test="query.params.beginCreateTime != null and query.params.beginCreateTime != ''">
                AND date_format(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(create_time,'%y%m%d') &lt;= date_format(#{query.params.endCreateTime},'%y%m%d')
            </if>
        </where>
        order by create_time desc
    </select>

    <update id="logicalDeleteSysChildhoodManageByIds" parameterType="String">
        update sys_childhood_manage 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>

    <update id="resetClassChildhoodInfo" parameterType="Long">
        update sys_childhood_manage set class_id = null, in_kindergarten_status = null
        where class_id = #{classId}
    </update>

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