<?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.sapi.mapper.region.RRoomAnalyzeMapper">
    <!--活跃度统计-->
    <select id="listRoom" resultType="com.xhkjedu.sapi.vo.region.RRoomVo">
        select r.schoolid,s.schoolname,s.shortname,count(distinct r.roomid)ktnum
        ,count(distinct case when rt.asktype=20 then rt.asktid else null end)qdnum
        ,count(distinct case when rt.asktype=21 then rt.asktid else null end)dmnum
        ,count(distinct case when rt.asktype between 30 and 33 then rt.asktid else null end)twnum
        ,count(distinct case when rt.asktype=40 then rt.asktid else null end)tcnum
        ,count(distinct rf.rfid)jtnum
        ,count(distinct case when rt.asktype=20 and rs.asksid is not null then rs.asksid else null end)qdstunum
        ,count(distinct case when rt.asktype between 30 and 33 and rs.asksid is not null then rs.asksid else null end)twstunum
        ,count(distinct case when rt.asktype between 30 and 33 and rs.radiocorrect=1 then rs.asksid else null end)twzqnum
        ,count(distinct pss.pssid)tcstunum
        ,count(distinct case when pss.answered=1 then pss.pssid else null end)tctjnum
        ,count(distinct case when pss.qscore!=0 and pss.qscore=pss.stuscore then pss.pssid else null end)tczqnum
        ,(r.endtime-r.createtime)totaltime,r.classnum,r.realstunum
        from t_school s right join t_classroom r on r.schoolid=s.schoolid
        left join t_class c on c.classid=r.classid
        left join t_classroom_askteacher rt on rt.roomid=r.roomid
        left join t_classroom_file rf on rf.roomid=r.roomid
        left join t_classroom_askstudent rs on rt.asktid=rs.asktid
        left join t_classroom_paper p on p.paperid=rt.paperid
        left join t_classroom_paper_student ps on p.paperid=ps.paperid
        left join t_classroom_paper_student_scantron pss on ps.psid=pss.psid
        where s.schoollevel=#{level} and s.schoolstate=1 and c.year=s.year and r.roomstate=2 and r.endtime is not null
        and r.createtime between #{begintime} and #{endtime}
        group by r.roomid
    </select>
    <!--获取学校课堂用时人数统计-->
    <select id="getRoom" resultType="com.xhkjedu.sapi.vo.region.RRoomVo">
        select sum(r.endtime-r.createtime)totaltime,sum(r.classnum)classnum,sum(r.realstunum)realstunum
        from t_classroom r left join t_class c on c.classid=r.classid
        where r.schoolid=#{schoolid} and c.year=#{year} and r.roomstate=2 and r.endtime is not null
        and r.createtime between #{begintime} and #{endtime}
    </select>
</mapper>
