<?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.xhkjedu.slive.mapper.liveplay.CourseMapper">
    <select id="listAll" resultType="com.xhkjedu.slive.vo.liveplay.CourseVo">
        select
        c.courseid,c.coursename,c.gradeid,c.begindate,c.enddate,c.coursenum,c.begintime,c.endtime,c.duration,c.limitstamp
        ,c.limitstu,c.stunum,c.coursestate,c.courseselected,c.coursetype,c.comm,c.teacherid,c.classid,c.starttime,c.createid
        ,c.subjectid,s.subjectname,ct.typename,c.ctypeid
        ,count(distinct case when cs.sectionstate=3 then cs.sectionid else null end)sectionnum
        ,count(distinct case when lcs.sectionstate=2 then lcs.sectionid else null end)insectionnum
        ,concat('[',group_concat(distinct json_object('teachername',u.username,'headpic',u.headpic)),']')teachername
        ,group_concat( date_format(cs.sectiondate ,'%m月%d日') separator '、')sectiondate
        from l_course c left join l_course_section cs on cs.courseid=c.courseid
        left join t_user u on cs.teacherid=u.userid
        left join t_subject s on c.subjectid = s.subjectid
        left join l_course_section lcs on lcs.courseid=c.courseid
        left join l_course_type ct on c.ctypeid = ct.ctypeid
        <if test="course.classid!=null and course.classid!=0">
            left join l_course_class cc on cc.courseid=c.courseid
        </if>
        where c.schoolid=#{course.schoolid} and c.deleted=1 and c.courseclass=0
        <if test="course.gradeid!=null and course.gradeid!=0">
            and c.gradeid=#{course.gradeid}
        </if>
        <if test="course.subjectid!=null and course.subjectid!='0'.toString() and course.subjectid!=''">
            and c.subjectid=#{course.subjectid}
        </if>
        <if test="course.teacherid!=null and course.teacherid!=0">
            and lcs.teacherid=#{course.teacherid}
        </if>
        <if test="course.coursename!=null and course.coursename!=''">
            and c.coursename like '%${course.coursename}%'
        </if>
        <if test="course.coursestate!=null">
            and c.coursestate=#{course.coursestate}
        </if>
        <if test="course.today!=null and course.today==1">
            and lcs.sectiondate=date_format(now(),'%Y-%m-%d')
        </if>
        <if test="course.coursetype!=null">
            and c.coursetype=#{course.coursetype}
        </if>
        <if test="course.ctypeid!=null and course.ctypeid!=0">
            and ct.ctypeid=#{course.ctypeid}
        </if>
        <if test="course.createid!=null and course.createid!=0">
            and c.createid=#{course.createid}
        </if>
        <if test="course.classid!=null and course.classid!=0">
            and cc.classid=#{course.classid}
        </if>
        <if test="course.teachername!=null and course.teachername!=''">
            and u.username like '%${course.teachername}%'
        </if>
        group by c.courseid order by c.courseid desc
    </select>
    <!--更新已报学生数量-->
    <update id="updateStuNum">
        update l_course c set c.stunum=(select tc.classnum from t_class tc where tc.classid=c.classid)
        where c.courseid=#{courseid}
    </update>
    <resultMap id="courseResult" type="com.xhkjedu.slive.vo.liveplay.CourseVo">
        <result property="courseid" column="courseid"></result>
        <result property="coursename" column="coursename"></result>
        <result property="teacherid" column="teacherid"></result>
        <result property="teachername" column="teachername"></result>
        <result property="headpic" column="headpic"></result>
        <result property="begindate" column="begindate"></result>
        <result property="enddate" column="enddate"></result>
        <result property="begintime" column="begintime"></result>
        <result property="endtime" column="endtime"></result>
        <result property="limitstamp" column="limitstamp"></result>
        <result property="limitstu" column="limitstu"></result>
        <result property="stunum" column="stunum"></result>
        <result property="classid" column="classid"></result>
        <result property="coursestate" column="coursestate"></result>
        <result property="courseselected" column="courseselected"></result>
        <result property="coursetype" column="coursetype"></result>
        <result property="comm" column="comm"></result>
        <result property="subjectid" column="subjectid"></result>
        <result property="gradeid" column="gradeid"></result>
        <result property="sectionnum" column="sectionnum"></result>
        <result property="starttime" column="starttime"></result>
        <collection property="sections" ofType="com.xhkjedu.slive.vo.liveplay.CourseSectionVo" javaType="java.util.List"
                    select="listSection" column="{courseid=courseid}">
        </collection>
    </resultMap>
    <select id="listSection" resultType="com.xhkjedu.slive.vo.liveplay.CourseSectionVo">
        select cs.sectionid,cs.sectionname,cs.sectiondate,cs.planbegintime,cs.planendtime,cs.begintime,cs.endtime,cs.sectionstate
        ,cs.teacherid,cs.createid,cs.stunum,cs.sectionorder
        ,(UNIX_TIMESTAMP(CONCAT(cs.sectiondate,' ',cs.planendtime))-UNIX_TIMESTAMP(CONCAT(cs.sectiondate,' ',cs.planbegintime))) totaltime
        ,(select count(*) from l_course_section_playback p where p.sectionid=cs.sectionid and p.pb is not null)playnum
        ,(select u.username from t_user u where u.userid=cs.teacherid)teachername
        from l_course_section cs where cs.courseid=#{courseid}
        order by UNIX_TIMESTAMP(concat(cs.sectiondate,' ',cs.planbegintime))
    </select>
    <!--学生选课详情-->
    <select id="findById" resultMap="courseResult">
        select c.courseid,c.coursename,c.begindate,c.enddate,c.coursenum,c.begintime,c.endtime,c.starttime
        ,c.limitstamp,c.limitstu,c.stunum,c.classid,c.coursestate,c.courseselected,c.coursetype,c.comm,c.teacherid,
        c.subjectid,c.gradeid,count(distinct case when lcs.sectionstate=3 then lcs.sectionid else null end)sectionnum,c.begindate,c.enddate
        <if test="course.createid!=null and course.createid!=0">
            ,(select count(*) from t_class_student cs where cs.classid=c.classid and
            cs.studentid=#{course.createid})selected
        </if>
        ,concat('[',group_concat(distinct json_object('teachername',u.username,'headpic',u.headpic)),']')teachername
        ,(select s.subjectname from t_subject s where s.subjectid=c.subjectid)subjectname,c.createid
        ,group_concat( date_format(lcs.sectiondate ,'%m月%d日') separator '、')sectiondate
        ,u2.usertype createtype,u2.username createname
        ,(select t.typename from l_course_type t where t.ctypeid=c.ctypeid)typename
        from l_course c left join l_course_section lcs on lcs.courseid=c.courseid
        left join t_user u on lcs.teacherid=u.userid
        left join t_user u2 on u2.userid=c.createid
        where c.courseid=#{course.courseid}
    </select>
    <!--获取学生课程列表-->
    <select id="listStu" resultType="com.xhkjedu.slive.vo.liveplay.CourseVo">
        select
        c.courseid,c.coursename,c.gradeid,c.begindate,c.enddate,c.coursenum,c.begintime,c.endtime,c.duration,c.limitstamp
        ,c.limitstu,c.stunum,c.coursestate,c.courseselected,c.coursetype,c.comm,c.teacherid,c.starttime
        ,s.subjectname,ct.typename,group_concat( date_format(lcs.sectiondate ,'%m月%d日') separator '、')sectiondate
        ,count(distinct case when lcs.sectionstate=3 then lcs.sectionid else null end)sectionnum
        ,count(distinct case when lcs.sectionstate=2 then lcs.sectionid else null end)insectionnum
        ,concat('[',group_concat(distinct json_object('teachername',u.username,'headpic',u.headpic)),']')teachername
        from l_course c left join l_course_section lcs on lcs.courseid=c.courseid
        left join t_user u on lcs.teacherid=u.userid
        left join t_class_student cs on c.classid = cs.classid
        left join t_subject s on c.subjectid = s.subjectid
        <if test="course.today!=null and course.today==1">
            left join l_course_section lcs2 on lcs2.courseid=c.courseid
        </if>
        left join l_course_type ct on c.ctypeid = ct.ctypeid
        where cs.studentid=#{course.createid} and c.deleted=1
        <if test="course.subjectid!=null and course.subjectid!='0'.toString() and course.subjectid!=''">
            and c.subjectid=#{course.subjectid}
        </if>
        <if test="course.coursename!=null and course.coursename!=''">
            and c.coursename like '%${course.coursename}%'
        </if>
        <if test="course.coursestate!=null">
            and c.coursestate=#{course.coursestate}
        </if>
        <if test="course.today!=null and course.today==1">
            and lcs2.sectiondate=date_format(now(),'%Y-%m-%d')
        </if>
        <if test="course.ctypeid!=null and course.ctypeid!=0">
            and ct.ctypeid=#{course.ctypeid}
        </if>
        group by c.courseid order by c.courseid desc
    </select>
    <!--获取进行中的课节id-->
    <select id="findOnSection" resultType="java.lang.Integer">
        select cs.sectionid from l_course_section cs where cs.courseid=#{courseid} and cs.sectionstate=2 limit 1
    </select>
    <!--修改课程状态-->
    <update id="updateState">
        update l_course l
        <if test="course.coursestate==3">
            left join l_course_section ls on l.courseid=ls.courseid
        </if>
        set l.coursestate=#{course.coursestate}
        <if test="course.coursestate==3">
            ,ls.sectionstate=(case sectionstate when 0 then 4 else 3 end)
        </if>
        where l.courseid=#{course.courseid}

    </update>
    <!--修改课节数量-->
    <update id="updateCourseNum">
        update l_course c set coursenum=(select count(*) from l_course_section cs where cs.courseid=c.courseid)
        where c.courseid=#{courseid}
    </update>

    <update id="updateCourseDeleted">
        update l_course set deleted=#{deleted} where courseid=#{courseid}
    </update>

    <select id="getCourseByIdAndDeleted" resultType="com.xhkjedu.slive.model.liveplay.LCourse">
        select * from l_course where courseid=#{courseid} and deleted=#{deleted}
    </select>

    <!--获取课程学生集合-->
    <select id="listUid" resultType="java.util.Map">
        select u.userid from t_class_student cs left join t_user u on cs.studentid=u.userid
        where u.userid is not null and cs.classid in
        <foreach item="classid" collection="classids" open="(" separator="," close=")">
            #{classid}
        </foreach>
        group by u.userid
    </select>
    <!--获取学生选课信息-->
    <select id="findBySchoolIdCourseIdStudentId" resultType="java.lang.String">
        select c2.coursename
        from l_course c2,l_course_section cs,l_course_section cs2,t_class_student s
        where c2.courseid = cs2.courseid and c2.classid=s.classid and c2.deleted=1 and cs.sectiondate=cs2.sectiondate
        and ((cs.planbegintime between cs2.planbegintime and cs2.planendtime)
        or (cs.planendtime between cs2.planbegintime and cs2.planendtime))
        and c2.schoolid=#{schoolid} and c2.courseid!=#{courseid} and c2.coursetype=0 AND cs.courseid=#{courseid}
        and s.studentid=#{studentid} and cs2.sectionid is not null
        and cs2.sectionstate in(0,2) and cs.sectionstate in(0,2)
        group by c2.courseid limit 1
    </select>

    <!--学生网页端选课中心-->
    <select id="listAllForStu" resultType="com.xhkjedu.slive.vo.liveplay.CourseVo">
        select
        c.courseid,c.coursename,c.gradeid,c.begindate,c.enddate,c.coursenum,c.begintime,c.endtime,c.duration,c.limitstamp
        ,c.limitstu,c.stunum,c.coursestate,c.courseselected,c.coursetype,c.comm,c.teacherid,c.classid,c.starttime
        ,s.subjectname,c.ctypeid,ct.typename
        <if test="course.createid!=null and course.createid!=0">
            ,(select count(*) from t_class_student cs where cs.classid=c.classid and
            cs.studentid=#{course.createid})selected
        </if>
        ,count(distinct case when lcs.sectionstate=3 then lcs.sectionid else null end)sectionnum
        ,count(distinct case when lcs.sectionstate=2 then lcs.sectionid else null end)insectionnum
        ,concat('[',group_concat(distinct json_object('teachername',u.username,'headpic',u.headpic)),']')teachername
        ,group_concat( date_format(lcs.sectiondate ,'%m月%d日') separator '、')sectiondate
        from l_course c left join l_course_section lcs on lcs.courseid=c.courseid
        left join t_user u on lcs.teacherid=u.userid
        left join t_subject s on c.subjectid = s.subjectid
        left join l_course_type ct on c.ctypeid = ct.ctypeid
        where c.schoolid=#{course.schoolid} and c.coursetype=0 and c.deleted=1 and c.coursestate!=3
        and c.limitstamp>=unix_timestamp(now())
        <if test="course.gradeid!=null and course.gradeid!=0">
            and c.gradeid=#{course.gradeid}
        </if>
        <if test="course.subjectid!=null and course.subjectid!='0'.toString() and course.subjectid!=''">
            and c.subjectid=#{course.subjectid}
        </if>
        group by c.courseid order by ct.typeorder asc, c.courseid
    </select>
    <select id="listType" resultType="com.xhkjedu.slive.vo.liveplay.CTypeVo">
        select c.ctypeid,ct.typename
        from l_course c left join l_course_type ct on c.ctypeid = ct.ctypeid
        where c.schoolid=#{course.schoolid} and c.coursetype=0 and ct.ctstate=1
        <if test="course.gradeid!=null and course.gradeid!=0">
            and c.gradeid=#{course.gradeid}
        </if>
        <if test="course.subjectid!=null and course.subjectid!='0'.toString() and course.subjectid!=''">
            and c.subjectid=#{course.subjectid}
        </if>
        group by c.ctypeid order by ct.typeorder asc
    </select>
    <select id="listAllForStu2" resultType="com.xhkjedu.slive.vo.liveplay.CourseVo">
        select
        c.courseid,c.coursename,c.gradeid,c.begindate,c.enddate,c.coursenum,c.begintime,c.endtime,c.duration,c.limitstamp
        ,c.limitstu,c.stunum,c.coursestate,c.courseselected,c.coursetype,c.comm,c.teacherid,c.classid,c.starttime
        ,s.subjectname,c.ctypeid
        <if test="course.createid!=null and course.createid!=0">
            ,(select count(*) from t_class_student cs where cs.classid=c.classid and
            cs.studentid=#{course.createid})selected
        </if>
        ,count(distinct case when lcs.sectionstate=3 then lcs.sectionid else null end)sectionnum
        ,count(distinct case when lcs.sectionstate=2 then lcs.sectionid else null end)insectionnum
        ,concat('[',group_concat(distinct json_object('teachername',u.username,'headpic',u.headpic)),']')teachername
        ,group_concat( date_format(lcs.sectiondate ,'%m月%d日') separator '、')sectiondate
        from l_course c left join l_course_section lcs on lcs.courseid=c.courseid
        left join t_user u on lcs.teacherid=u.userid
        left join t_subject s on c.subjectid = s.subjectid
        where c.schoolid=#{course.schoolid} and c.coursetype=0 and c.ctypeid=#{course.ctypeid} and c.deleted=1 and c.coursestate!=3
        and c.limitstamp>=unix_timestamp(now())
        <if test="course.gradeid!=null and course.gradeid!=0">
            and c.gradeid=#{course.gradeid}
        </if>
        <if test="course.subjectid!=null and course.subjectid!='0'.toString() and course.subjectid!=''">
            and c.subjectid=#{course.subjectid}
        </if>
        group by c.courseid order by c.courseid
    </select>

    <!--某月份下日期课程数量-->
    <select id="listSectionDateForStu" resultType="com.xhkjedu.slive.vo.liveplay.SectionDateVo">
        select cs.sectiondate as timestr,count(*)as num
         from l_course_section cs
        left join l_course c on cs.courseid=c.courseid
        left join t_class_student s on c.classid=s.classid
        where s.studentid=#{cp.studentid} and c.deleted=1 and DATE_FORMAT(cs.sectiondate, '%Y-%m')=#{cp.timestr}
        <if test="cp.subjectid!=null and cp.subjectid!='0'.toString() and cp.subjectid!=''">
            and c.subjectid=#{cp.subjectid}
        </if>
        <if test="cp.cstate!=null and cp.cstate!=-1">
            and cs.sectionstate=#{cp.cstate}
        </if>
        group by cs.sectiondate order by cs.planbegintime asc
    </select>

    <!--学生指定日期下课程-->
    <select id="listSectionsByDate" resultType="com.xhkjedu.slive.vo.liveplay.CourseSectionVo">
        select cs.courseid,cs.sectionid,c.coursename,c.classid,cs.sectionname,cs.sectiondate,cs.planbegintime,
        cs.planendtime,cs.begintime,cs.endtime,cs.teacherid,u.username as teachername,cs.sectionstate,
        c.subjectid,st.subjectname
        ,(UNIX_TIMESTAMP(CONCAT(cs.sectiondate,' ',cs.planendtime))-UNIX_TIMESTAMP(CONCAT(cs.sectiondate,' ',cs.planbegintime))) totaltime
        ,cs.stunum,cs.sectionorder
        ,c.coursenum
        ,(select count(*) from l_course_section_playback p where p.sectionid=cs.sectionid and p.pb is not null)playnum
         from l_course_section cs
        left join l_course c on cs.courseid=c.courseid
        left join t_class_student s on c.classid=s.classid
        left join t_user u on u.userid=cs.teacherid
        left join t_subject st on c.subjectid=st.subjectid
        where s.studentid=#{cp.studentid} and c.deleted=1
        <if test="cp.subjectid!=null and cp.subjectid!='0'.toString() and cp.subjectid!=''">
            and c.subjectid=#{cp.subjectid}
        </if>
        <if test="cp.timestr!=null and cp.timestr!=''">
            and cs.sectiondate=#{cp.timestr}
        </if>
        <if test="cp.cstate!=null and cp.cstate!=-1">
            and cs.sectionstate=#{cp.cstate}
        </if>
        group by cs.sectionid
        order by cs.sectionstate,abs(UNIX_TIMESTAMP(concat(cs.sectiondate,' ',cs.planbegintime))-UNIX_TIMESTAMP(now())),cs.sectionid
    </select>

    <!--教师某月份下日期课程数量-->
    <select id="listSectionDateForTeacher" resultType="com.xhkjedu.slive.vo.liveplay.SectionDateVo">
        select cs.sectiondate as timestr,count(*)as num
         from l_course_section cs
        left join l_course c on cs.courseid=c.courseid
        <if test="course.classid!=null and course.classid!=0">
            left join l_course_class cc on cc.courseid=c.courseid
        </if>
        where c.deleted=1 and DATE_FORMAT(cs.sectiondate, '%Y-%m')=#{course.timestr}
        <if test="course.teacherid!=null and course.teacherid!=0">
            and cs.teacherid=#{course.teacherid}
        </if>
        <if test="course.schoolid!=null and course.schoolid!=0">
            and c.schoolid=#{course.schoolid}
        </if>
        <if test="course.gradeid!=null and course.gradeid!=0">
            and c.gradeid=#{course.gradeid}
        </if>
        <if test="course.subjectid!=null and course.subjectid!='0'.toString() and course.subjectid!=''">
            and c.subjectid=#{course.subjectid}
        </if>
        <if test="course.classid!=null and course.classid!=0">
            and cc.classid=#{course.classid}
        </if>
        group by cs.sectiondate order by cs.planbegintime asc
    </select>
    <!--巡课某月份下日期课程数量-->
    <select id="listSectionDateForXk2" resultType="java.util.Map">
        select cs.sectiondate as timestr,count(distinct cs.sectionid)as num
        ,count(distinct case when cs.sectionstate=0 then cs.sectionid else null end)wksnum
        ,count(distinct case when cs.sectionstate=2 then cs.sectionid else null end)jxznum
        ,count(distinct case when cs.sectionstate=3 then cs.sectionid else null end)yjsnum
        ,count(distinct case when cs.sectionstate=4 then cs.sectionid else null end)ygqnum
        from l_course_section cs
        left join l_course c on cs.courseid=c.courseid
        <if test="(course.classid!=null and course.classid!=0)
        or (course.classids!=null and course.classids!='')">
            left join l_course_class cc on cc.courseid=c.courseid
        </if>
        <if test="course.teachername!=null and course.teachername!=''">
            left join t_user u on cs.teacherid=u.userid
        </if>
        where c.deleted=1 and c.schoolid=#{course.schoolid}
        and DATE_FORMAT(cs.sectiondate, '%Y-%m')=#{course.timestr}
        <if test="course.teachername!=null and course.teachername!=''">
            and u.username like '%${course.teachername}%'
        </if>
        <if test="course.cstate!=null and course.cstate!=-1">
            and cs.sectionstate=#{course.cstate}
        </if>
        <if test="course.gradeid!=null and course.gradeid!=0">
            and c.gradeid=#{course.gradeid}
        </if>
        <if test="course.subjectid!=null and course.subjectid!='0'.toString() and course.subjectid!=''">
            and c.subjectid=#{course.subjectid}
        </if>
        <if test="course.classid!=null and course.classid!=0">
            and cc.classid=#{course.classid}
        </if>
        <if test="course.classids!=null and course.classids!=''">
            and cc.classid in (${course.classids})
        </if>
        group by cs.sectiondate order by cs.sectiondate asc
    </select>
    <!--课节未结束的课程列表-->
    <select id="listCourse" resultType="com.xhkjedu.slive.model.liveplay.LCourse">
        select c.courseid,c.classid,c.createid
        from l_course_section cs left join l_course c on cs.courseid=c.courseid
        where c.schoolid=#{schoolid} and cs.sectionstate between 0 and 2
        group by c.courseid
    </select>
    <!--今天以后行政和提高班的课程列表-->
    <select id="listCourse2" resultType="com.xhkjedu.slive.model.liveplay.LCourse">
        select c.courseid,c.classid,c.createid
        from l_course_section cs left join l_course c on cs.courseid=c.courseid
        left join l_course_class cc on cc.courseid=c.courseid
        where c.coursetype=1 and cc.classid in(${classids}) and cs.sectiondate>=DATE_FORMAT(now(),'%Y-%m-%d')
        and cs.sectionstate between 0 and 2
        group by c.courseid
    </select>
    <!--学生所有的直播课-->
    <select id="listCourse3" resultType="com.xhkjedu.slive.model.liveplay.LCourse">
        select c.courseid,c.classid,c.createid
        from t_class_student cs right join l_course c on cs.classid=c.classid
        where cs.studentid=#{studentid}
    </select>
    <!--学生指定班级的直播课-->
    <select id="listCourse4" resultType="com.xhkjedu.slive.model.liveplay.LCourse">
        select c.courseid,c.classid,c.createid
        from t_class_student cs right join l_course c on cs.classid=c.classid
        right join l_course_class cc on cc.courseid=c.courseid
        where cs.studentid=#{studentid} and cc.classid=#{classid}
    </select>
    <!--行政班的所有直播课-->
    <select id="listXzCourse" resultType="com.xhkjedu.slive.model.liveplay.LCourse">
        select c.courseid,c.classid,c.createid,c.schoolid
        from l_course c left join l_course_class cc on cc.courseid=c.courseid
        where c.coursetype=1 and cc.classid=#{classid}
        group by c.courseid
    </select>
    <!--删除直播课指定班级下的所有学生和班级-->
    <delete id="delClass">
        delete cs2.*,cc.* from l_course_class cc left join t_class_student cs on cc.classid=cs.classid
        left join t_class_student cs2 on cs.studentid=cs2.studentid
        where cc.courseid=#{courseid} and cc.classid in(${classids}) and cs2.classid=#{classid}
    </delete>
    <!--删除直播课指定班级下的所有学生-->
    <delete id="delClass2">
        delete cs2.*,lc.* from t_class cc left join t_class_student cs on cc.classid=cs.classid
        left join t_class_student cs2 on cs.studentid=cs2.studentid
        left join l_course_class lc on cc.classid=lc.classid and lc.courseid=#{courseid}
        where cc.classid in(${classids}) and cs2.classid=#{classid}
    </delete>
    <!--获取需要处理的直播课-->
    <select id="listNeedHandleCourse" resultType="com.xhkjedu.slive.vo.liveplay.CourseStudentVo">
        SELECT a.*
        FROM(SELECT l.courseid,l.classid,l.createid,l.createtime,GROUP_CONCAT(DISTINCT cc.classid) classids
        ,l.stunum,sum(c.classnum)classnum
        FROM l_course l LEFT JOIN l_course_class cc ON cc.courseid=l.courseid
        LEFT JOIN t_class c ON cc.classid=c.classid
        WHERE l.deleted=1 AND l.coursetype=1 AND l.schoolid=#{course.schoolid}
        <if test="course.subjectid!=null and course.subjectid!='0'.toString() and course.subjectid!=''">
            and l.subjectid=#{course.subjectid}
        </if>
        <if test="course.timestr!=null and course.timestr!=''">
            and l.begindate>#{course.timestr}
        </if>
        GROUP BY l.courseid ORDER BY l.createtime DESC)a WHERE a.stunum!=a.classnum
    </select>
    <!--获取要删除的班级学生ids-->
    <select id="getCsids" resultType="java.lang.String">
        SELECT GROUP_CONCAT(cs.csid)
        FROM t_class_student cs
        WHERE cs.classid=#{classid} AND cs.studentid NOT IN(SELECT cs2.studentid FROM t_class_student cs2
        WHERE cs2.classid in(${classids}))
    </select>
    <!--删除直播学生-->
    <delete id="delStuByCsids">
        DELETE cs.* FROM t_class_student cs WHERE cs.csid in(${csids})
    </delete>
    <!--添加直播学生-->
    <insert id="addCourseStu">
        insert ignore into t_class_student (classid,studentid,createid,createtime,schoolid)
        SELECT #{classid},studentid,#{createid}, #{createtime},#{schoolid} FROM t_class_student WHERE classid
        in(${classids})
    </insert>

    <!--修改课程课节名称-->
    <update id="updateCoursename">
        update l_course c ,l_course_section s set c.coursename=#{course.coursename},s.sectionname=#{course.coursename}
        where c.courseid=s.courseid and c.courseid=#{course.courseid}
    </update>
    <!--修改课程课节备注-->
    <update id="updateCoursecomm">
        update l_course c ,l_course_section s set c.comm=#{course.comm},s.comm=#{course.comm}
        where c.courseid=s.courseid and c.courseid=#{course.courseid}
    </update>

    <!--根据课程id删除关联课程关联信息-->
    <delete id="deleteCourseClassByCourseid">
        delete c,tc from l_course c,t_class tc where c.classid=tc.classid and c.courseid=#{courseid}
    </delete>

    <!--获取指定课节班级日期下的课程信息-->
    <select id="getCourseByKjidAndClassid" resultType="com.xhkjedu.slive.vo.liveplay.CourseVo">
        select c.courseid,c.classid from l_course c
        left join l_course_class cc on c.courseid=cc.courseid
        WHERE c.kjid=#{kjid} and cc.classid=#{classid}	 and c.begindate=#{seldate}
    </select>

    <!--获取指定指定日期教师课节下教师的直播课-->
    <select id="getCourseByDateKjTeacher" resultType="com.xhkjedu.slive.vo.liveplay.CourseVo">
        select courseid,subjectid,classid,teacherid,kjid from l_course
        where kjid=#{kjTeaVo.kjid} and teacherid=#{kjTeaVo.teacherid} and begindate=#{kjTeaVo.seldate}
    </select>

    <!--修改未开课课程课节时间-->
    <update id="updateBatchCourseSectionTime">
        <foreach collection="list" item="kj" index="index" separator=";">
            update l_course c,l_course_section s set c.begintime=#{kj.begintime},c.endtime=#{kj.endtime},
            s.planbegintime=#{kj.begintime},s.planendtime=#{kj.endtime} where c.courseid=s.courseid
            and c.kjid=#{kj.kjid} and c.coursestate=0 and s.sectionstate=0
        </foreach>
    </update>
    <!--批量删除课程-->
    <delete id="batchDelCourse">
        delete lc,c from l_course lc left join t_class c on lc.classid = c.classid
        where lc.courseid in
        <foreach collection="courseids" item="courseid" index="index" open="(" separator="," close=")">
            ${courseid}
        </foreach>
    </delete>
    <!--批量更改课程学生数量-->
    <update id="updateStuNumBatch">
        update l_course c set c.stunum=(select tc.classnum from t_class tc where tc.classid=c.classid)
        where c.courseid in (${courseids})
    </update>
</mapper>
