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

    <resultMap type="com.vctgo.system.domain.SysPlatformTeachStaff" id="SysTeachStaffResult">
        <result property="id" column="id"/>
        <result property="kindergartenId" column="kindergarten_id"/>
        <result property="kindergartenType" column="kindergarten_type"/>
        <result property="userName" column="user_name"/>
        <result property="phone" column="phone"/>
        <result property="sex" column="sex"/>
        <result property="email" column="email"/>
        <result property="inchPhotoOne" column="inch_photo_one"/>
        <result property="inchPhotoTwo" column="inch_photo_two"/>
        <result property="politicalStatus" column="political_status"/>
        <result property="mainPost" column="main_post"/>
        <result property="otherPost" column="other_post"/>
        <result property="teacherEducationId" column="teacher_education_id"/>
        <result property="jobStatus" column="job_status"/>
        <result property="professionalTitleGrade" column="professional_title_grade"/>
        <result property="qualifyCertificate" column="qualify_certificate"/>
        <result property="isBackbone" column="is_backbone"/>
        <result property="backboneStartTime" column="backbone_start_time"/>
        <result property="backboneEndTime" column="backbone_end_time"/>
        <result property="backboneType" column="backbone_type"/>
        <result property="isDisciplineLeader" column="is_discipline_leader"/>
        <result property="discipline" column="discipline"/>
        <result property="actualTeachingAge" column="actual_teaching_age"/>
        <result property="informationSource" column="information_source"/>
        <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>

    <resultMap id="selectAccountNumberResult" type="com.vctgo.system.domain.SysPlatformTeachStaff" extends="SysTeachStaffResult">
        <association property="saasAccountNumber" javaType="java.lang.String" column="{phone = phone}" select="selectAccountNumber"
                fetchType="eager"/>
    </resultMap>

    <sql id="selectSysTeachStaffVo">
        select id,
               kindergarten_id,
               kindergarten_type,
               user_name,
               phone,
               sex,
               email,
               inch_photo_one,
               inch_photo_two,
               political_status,
               main_post,
               other_post,
               teacher_education_id,
               job_status,
               professional_title_grade,
               is_backbone,
               backbone_start_time,
               backbone_end_time,
               backbone_type,
               is_discipline_leader,
               discipline,
               actual_teaching_age,
               information_source,
               qualify_certificate,
               remark,
               is_deleted,
               create_by_id,
               create_by,
               create_time,
               update_by_id,
               update_by,
               update_time,
               tenant_id
        from sys_platform_teach_staff
    </sql>

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

    <select id="selectAccountNumber" resultType="java.lang.String">
        SELECT
            count(*)
        FROM
            (
                SELECT DISTINCT
                    s.tenant_id,
                    s.phonenumber
                FROM
                    sys_user s
                WHERE
                    s.phonenumber = #{phone} GROUP BY s.tenant_id ,s.phonenumber) u
    </select>

    <select id="selectSysTeachStaffList" resultMap="selectAccountNumberResult">
        SELECT
            ts.id,
            ts.kindergarten_id,
            IFNULL( bk.kindergarten_type, "3" ) AS kindergarten_type,
            ts.user_name,
            ts.phone,
            ts.sex,
            ts.email,
            ts.inch_photo_one,
            ts.inch_photo_two,
            ts.political_status,
            ts.main_post,
            ts.other_post,
            ts.teacher_education_id,
            ts.job_status,
            ts.professional_title_grade,
            ts.is_backbone,
            ts.backbone_start_time,
            ts.backbone_end_time,
            ts.backbone_type,
            ts.is_discipline_leader,
            ts.discipline,
            ts.actual_teaching_age,
            ts.information_source,
            ts.qualify_certificate,
            ts.remark,
            ts.create_time,
            pu.nick_name create_by,
            IFNULL( gm.NAME, bk.kindergarten_name ) AS kindergarten_name
        FROM
            sys_platform_teach_staff ts
        LEFT JOIN sys_garden_manage gm ON ts.kindergarten_id = gm.id
        LEFT JOIN sys_branch_kindergarten bk ON ts.kindergarten_id = bk.id
        LEFT JOIN sys_platform_user pu ON ts.create_by_id = pu.user_id
        <where>
            ts.is_deleted = '0'
            <if test="query.kindergartenType != null ">and ts.kindergarten_type = #{query.kindergartenType}</if>
            <if test="query.kindergartenId != null ">and ts.kindergarten_id = #{query.kindergartenId}</if>
            <if test="query.userName != null  and query.userName != ''">and ts.user_name like concat('%',#{query.userName}, '%')</if>
            <if test="query.phone != null  and query.phone != ''">and ts.phone  like concat('%',#{query.phone}, '%')</if>
            <if test="query.sex != null  and query.sex != ''">and ts.sex = #{query.sex}</if>
            <if test="query.email != null  and query.email != ''">and ts.email = #{query.email}</if>
            <if test="query.inchPhotoOne != null  and query.inchPhotoOne != ''">and ts.inch_photo_one = #{query.inchPhotoOne}</if>
            <if test="query.inchPhotoTwo != null  and query.inchPhotoTwo != ''">and ts.inch_photo_two = #{query.inchPhotoTwo}</if>
            <if test="query.politicalStatus != null  and query.politicalStatus != ''">and ts.political_status =#{query.politicalStatus}</if>
            <if test="query.mainPost != null  and query.mainPost != ''">and ts.main_post = #{query.mainPost}</if>
            <if test="query.otherPost != null  and query.otherPost != ''">and ts.other_post = #{query.otherPost}</if>
            <if test="query.teacherEducationId != null  and query.teacherEducationId != ''">and ts.teacher_education_id = #{query.teacherEducationId}</if>
            <if test="query.jobStatus != null  and query.jobStatus != ''">and ts.job_status = #{query.jobStatus}</if>
            <if test="query.professionalTitleGrade != null  and query.professionalTitleGrade != ''">and ts.professional_title_grade = #{query.professionalTitleGrade}</if>
            <if test="query.isBackbone != null  and query.isBackbone != ''">and ts.is_backbone = #{query.isBackbone}</if>
            <if test="query.backboneStartTime != null ">and ts.backbone_start_time = #{query.backboneStartTime}</if>
            <if test="query.backboneEndTime != null ">and ts.backbone_end_time = #{query.backboneEndTime}</if>
            <if test="query.backboneType != null  and query.backboneType != ''">and ts.backbone_type = #{query.backboneType}</if>
            <if test="query.isDisciplineLeader != null  and query.isDisciplineLeader != ''">and ts.is_discipline_leader = #{query.isDisciplineLeader} </if>
            <if test="query.discipline != null  and query.discipline != ''">and ts.discipline = #{query.discipline}</if>
            <if test="query.actualTeachingAge != null  and query.actualTeachingAge != ''">and ts.actual_teaching_age = #{query.actualTeachingAge} </if>
            <if test="query.informationSource != null  and query.informationSource != ''">and ts.information_source = #{query.informationSource}</if>
            <if test="query.createById != null ">and ts.create_by_id = #{query.createById}</if>
            <if test="query.updateById != null ">and ts.update_by_id = #{query.updateById}</if>
            <if test="query.tenantId != null ">and ts.tenant_id = #{query.tenantId}</if>
            <if test="query.params.beginTime != null and query.params.beginTime != ''">
                and date_format(ts.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(ts.create_time,'%y%m%d') &lt;= date_format(#{query.params.endTime},'%y%m%d')
            </if>
        </where>
        ORDER BY ts.create_time DESC
    </select>

    <update id="logicalDeleteSysTeachStaffByIds" parameterType="String">
        update sys_platform_teach_staff set is_deleted = #{query.isDeleted},
        update_by_id = #{query.updateById}, update_by = #{query.updateBy},
        update_time = #{query.updateTime} where kindergarten_id = #{query.kindergartenId} and id in
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

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


    <update id="updateSysPlatTeachStaff" >
        update sys_platform_teach_staff
        <set>
            <if test="kindergartenType != null and kindergartenType != ''">kindergarten_type = #{kindergartenType},</if>
            <if test="kindergartenId != null and kindergartenId != ''">kindergarten_id = #{kindergartenId},</if>
            <if test="verifyStatus != null and verifyStatus != ''">verify_status = #{verifyStatus},</if>
            <if test="sex != null and sex != ''">sex = #{sex},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
            <if test="userName != null and userName != ''">user_name = #{userName},</if>

            <if test="updateById != null and updateById != ''">update_by_id = #{updateById},</if>

            update_time = sysdate()
        </set>
        where id = #{id}
    </update>
</mapper>
