<?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.xunwang.education.mapper.TeacherCoursesMapper">

    <update id="batchupdate" parameterType="list">
        update teacher_courses set
        viewed_count =
        <foreach collection="list" item="course" index="index"
                 separator=" " open="case course_id" close="end">
            when #{course.id} then
            #{course.count}
        </foreach>
        where course_id in
        <foreach collection="list" item="course" index="index"
                 separator="," open="(" close=")">
            #{course.id}
        </foreach>
    </update>

    <select id="selectTeachers" resultType="com.xunwang.education.entity.dto.MyTeacherCoursesDto">
        select tcou.course_id, tcou.teach_age, tcou.subject, tcou.experience, tcou.location, tcou.location_al, tc.name, tc.profile_photo, tc.school, tc.education, tc.sex
        from teacher_courses tcou join teacher_certifications tc
        on tcou.uid = tc.uid
        <where>
            <if test="obj.course != null and obj.course != '1-1'">
                and tcou.subject = #{obj.course}
            </if>
            <if test="obj.identify != null and obj.identify != ''">
                and tcou.teacher_type = #{obj.identify}
            </if>
            <if test="obj.education != null and obj.education != ''">
                and tc.education = #{obj.education}
            </if>
            <if test="obj.sex != null and obj.sex != ''">
                and tc.sex = #{obj.sex}
            </if>
            <if test="obj.school != null and obj.school != ''">
                and tc.school = #{obj.school}
            </if>
            <if test="obj.mode != null and obj.mode != ''">
                and tcou.mode = #{obj.mode}
            </if>
            <if test="obj.time != null and obj.time != ''">
                and tcou.teach_time like concat("%", #{obj.time}, "%")
            </if>
                and tcou.display = "上架" and tcou.locked = "未锁"
        </where>
        limit #{obj.pageNum}, #{obj.pageSize}
    </select>

    <select id="getTeacherViewCount" resultType="java.lang.Integer">
        SELECT IFNULL(SUM(viewed_count),0) FROM `teacher_courses` WHERE uid=#{uid}
    </select>
    <select id="getTeacherCourseId" resultType="com.xunwang.education.entity.TeacherCourses">
        SELECT course_id FROM `teacher_courses` WHERE uid=#{uid}
    </select>

    <select id="selectByBatch" resultType="com.xunwang.education.entity.dto.MyTeacherCoursesDto" parameterType="list">
        select tcou.course_id, tcou.teach_age, tcou.subject, tcou.viewed_count, tcou.location_al, tc.name, tc.profile_photo, tc.school, tc.education, tc.sex
        from teacher_courses tcou join teacher_certifications tc
        on tcou.uid = tc.uid
        where
            tcou.course_id in
                <foreach collection="list" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
    </select>

    <select id="selectPublishCourseList" resultType="com.xunwang.education.entity.dto.MyPublishTeacherCoursesDto">
        select course_id, subject, verify_status, display, locked, deal
        from teacher_courses
        where uid = #{uid} and deleted = 0
        order by datetime desc
    </select>

    <select id="getPersonCourseDetailById" resultType="com.xunwang.education.entity.dto.MyTeacherCourseDetailDto">
        select tcou.course_id, tcou.uid, tcou.introduce, tcou.teach_age, tcou.teach_time, tcou.datetime, tcou.subject, tcou.teacher_type, tcou.experience, tcou.viewed_count, tcou.location, tcou.location_al, tcou.mode, tcou.teach_time, tcou.verify_status, tcou.verify_refuse_reason, tc.name, tc.profile_photo, tc.school, tc.education, tc.sex
        from teacher_courses tcou join teacher_certifications tc
        on tcou.uid = tc.uid
        where
            tcou.course_id = #{courseId}
    </select>

</mapper>
