<?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.xinyilu.system.mapper.teacherInfo.TeacherInfoMapper">

    <resultMap type="teacherInfoVO" id="TeacherInfoResult">
        <result property="id" column="id"/>
        <result property="kindergartenId" column="kindergarten_id"/>
        <result property="userId" column="user_id"/>
        <result property="teacherName" column="teacher_name"/>
        <result property="cardNo" column="card_no"/>
        <result property="teacherPhone" column="teacher_phone"/>
        <result property="teacherSex" column="teacher_sex"/>
        <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="kindergartenName" column="kindergarten_name"/>
    </resultMap>

    <sql id="selectTeacherInfoVo">
        select id, kindergarten_id, user_id, teacher_name, card_no, teacher_phone, teacher_sex, del_flag, create_by, create_time, update_by, update_time from teacher_info
    </sql>

    <select id="selectTeacherInfoList" parameterType="teacherInfoDTO" resultMap="TeacherInfoResult">
        SELECT
        ti.id,
        ti.kindergarten_id,
        ki.kindergarten_name,
        ti.user_id,
        ti.teacher_name,
        ti.card_no,
        ti.teacher_phone,
        ti.teacher_sex,
        ti.del_flag,
        ti.create_by,
        ti.create_time,
        ti.update_by,
        ti.update_time
        FROM
        teacher_info ti
        LEFT JOIN kindergarten_info ki ON ti.kindergarten_id = ki.id
        <where>
            ti.del_flag = 0
            <if test="id != null ">
                and ti.id = #{id}
            </if>
            <if test="kindergartenId != null ">
                and ti.kindergarten_id = #{kindergartenId}
            </if>
            <if test="userId != null ">
                and ti.user_id = #{userId}
            </if>
            <if test="teacherName != null  and teacherName != ''">
                and ti.teacher_name like concat('%', #{teacherName}, '%')
            </if>
            <if test="kindergartenName != null  and kindergartenName != ''">
                and ki.kindergarten_name like concat('%', #{kindergartenName}, '%')
            </if>
            <if test="cardNo != null  and cardNo != ''">
                and ti.card_no = #{cardNo}
            </if>
            <if test="teacherPhone != null  and teacherPhone != ''">
                and ti.teacher_phone = #{teacherPhone}
            </if>
            <if test="teacherSex != null  and teacherSex != ''">
                and ti.teacher_sex = #{teacherSex}
            </if>
            <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                and ti.kindergarten_id in
                <foreach collection="kindergartenIdList" item="kindergartenId" open="(" separator="," close=")">
                    #{kindergartenId}
                </foreach>
            </if>
            <if test="teacherIdList != null and teacherIdList.size() > 0">
                and ti.id in
                <foreach collection="teacherIdList" item="teacherId" open="(" separator="," close=")">
                    #{teacherId}
                </foreach>
            </if>
            <if test="roleUserId != null and roleTeacherIdList != null and roleTeacherIdList.size() > 0">
                and (ti.user_id = #{roleUserId}
                or ti.id in
                <foreach collection="roleTeacherIdList" item="roleTeacherId" open="(" separator="," close=")">
                    #{roleTeacherId}
                </foreach>
                )
            </if>
        </where>
        order by ti.create_time desc
    </select>

    <select id="selectTeacherInfoById" parameterType="Long"
            resultMap="TeacherInfoResult">
        SELECT
        ti.id,
        ti.kindergarten_id,
        ki.kindergarten_name,
        ti.user_id,
        ti.teacher_name,
        ti.card_no,
        ti.teacher_phone,
        ti.teacher_sex,
        ti.del_flag,
        ti.create_by,
        ti.create_time,
        ti.update_by,
        ti.update_time
        FROM
        teacher_info ti
        LEFT JOIN kindergarten_info ki ON ti.kindergarten_id = ki.id
        where ti.id = #{id} and ti.del_flag = 0
    </select>

    <insert id="insertTeacherInfo" parameterType="TeacherInfo" useGeneratedKeys="true"
            keyProperty="id">
        insert into teacher_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="kindergartenId != null">kindergarten_id,
            </if>
            <if test="userId != null">user_id,
            </if>
            <if test="teacherName != null">teacher_name,
            </if>
            <if test="cardNo != null">card_no,
            </if>
            <if test="teacherPhone != null">teacher_phone,
            </if>
            <if test="teacherSex != null">teacher_sex,
            </if>
            <if test="delFlag != null">del_flag,
            </if>
            <if test="createBy != null">create_by,
            </if>
            <if test="createTime != null">create_time,
            </if>
            <if test="updateBy != null">update_by,
            </if>
            <if test="updateTime != null">update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="kindergartenId != null">#{kindergartenId},
            </if>
            <if test="userId != null">#{userId},
            </if>
            <if test="teacherName != null">#{teacherName},
            </if>
            <if test="cardNo != null">#{cardNo},
            </if>
            <if test="teacherPhone != null">#{teacherPhone},
            </if>
            <if test="teacherSex != null">#{teacherSex},
            </if>
            <if test="delFlag != null">#{delFlag},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
            <if test="updateBy != null">#{updateBy},
            </if>
            <if test="updateTime != null">#{updateTime},
            </if>
        </trim>
    </insert>

    <update id="updateTeacherInfo" parameterType="TeacherInfo">
        update teacher_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="kindergartenId != null">kindergarten_id =
                #{kindergartenId},
            </if>
            <if test="userId != null">user_id =
                #{userId},
            </if>
            <if test="teacherName != null">teacher_name =
                #{teacherName},
            </if>
            <if test="cardNo != null">card_no =
                #{cardNo},
            </if>
            <if test="teacherPhone != null">teacher_phone =
                #{teacherPhone},
            </if>
            <if test="teacherSex != null">teacher_sex =
                #{teacherSex},
            </if>
            <if test="delFlag != null">del_flag =
                #{delFlag},
            </if>
            <if test="createBy != null">create_by =
                #{createBy},
            </if>
            <if test="createTime != null">create_time =
                #{createTime},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteTeacherInfoById" parameterType="Long">
        delete from teacher_info where id = #{id}
    </delete>

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

    <select id="selectTeacherInfo" parameterType="teacherInfo" resultType="int">
        select count(1) as teacherCount from teacher_info
        <where>
            <if test="cardNo != null  and cardNo != ''">
                and card_no = #{cardNo}
            </if>
            <if test="teacherPhone != null  and teacherPhone != ''">
                and teacher_phone = #{teacherPhone}
            </if>
        </where>
    </select>

    <update id="updateTeacherInfoByIds" parameterType="long">
        update teacher_info set del_flag = 1, update_time = now() where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <select id="getKindergartenIdByUserId" parameterType="long" resultType="long">
        select DISTINCT kindergarten_id from teacher_info where user_id = #{userId}
    </select>

    <select id="getKindergartenNameByUserId"
            resultType="com.xinyilu.system.domain.kindergarteninfo.KindergartenInfo">
        select DISTINCT ki.id, ki.kindergarten_name
        from teacher_info ti
        inner join kindergarten_info ki on ti.kindergarten_id = ki.id
        where ti.user_id = #{userId} and ti.del_flag = 0
    </select>

    <select id="getTeacherIdByUserId" parameterType="String" resultType="long">
        SELECT
            cti.teacher_id
        FROM
            parent_info pi
        LEFT JOIN parent_student_info psi ON pi.id = psi.parent_id
        LEFT JOIN student_info si ON psi.student_id = si.id
        LEFT JOIN class_teacher_info cti ON si.class_id = cti.class_id
        WHERE pi.phone = #{phone} and cti.del_flag = 0
        GROUP BY cti.teacher_id
    </select>


    <select id="getUserTeacher"
            resultType="com.xinyilu.system.domain.vo.teacherInfo.UserTeacherVO">
        SELECT
        ki.id kindergartenId,
        ki.kindergarten_name,
        ci.id classId,
        ci.class_name,
        cti.id classTeacherInfoId,
        ti.id teacherId,
        ti.teacher_name
        FROM
        teacher_info ti
        LEFT JOIN class_teacher_info cti ON ti.id = cti.teacher_id
        LEFT JOIN class_info ci ON  cti.class_id = ci.id
        LEFT JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        <where>
            AND cti.del_flag = 0
            AND ki.del_flag = 0
            AND ci.del_flag = 0
            AND ti.del_flag = 0
            <if test="teacherName != null  and teacherName != ''">
                and ti.teacher_name = #{teacherName}
            </if>
            <if test="kindergartenId != null ">
                and ki.id = #{kindergartenId}
            </if>
        </where>
        GROUP BY ci.id
        LIMIT 1
    </select>

    <update id="updateTeacherInfoByUserId" parameterType="long">
        update teacher_info set user_id = #{userId}, update_time = now() where id = #{id}
    </update>
</mapper>
