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

    <!--获取区平台信息-->
    <select id="getCloudInfo" resultType="com.xhkjedu.sapi.vo.region.CloudVo">
        select * from t_cloud order by cloudid limit 1
    </select>

    <!--获取资源、试题总量-->
    <select id="getResourceQuestionNum" resultType="java.util.Map">
        select (select count(*) from t_resource where resourcestate=1 and (level=1 or level=4) and belong!=4)resourcenum
        ,(select count(*) from t_question where qstate=1 and belong!=4)questionnum
    </select>
    <!--获取资源类型统计-->
    <select id="listResourceTypeNum" resultType="com.xhkjedu.sapi.vo.region.ResourceTypeVo">
        select s.level,r.resourcetype rtype,count(r.resourceid)rnum
        ,sum(case when r.belong in(1,2) then 1 else 0 end)ptnum
        ,sum(case when r.belong=3 then 1 else 0 end)xbnum
        from t_resource r left join t_subject s on r.subjectid=s.subjectid
        where r.resourcestate=1 and (r.level=1 or r.level=4) and r.belong!=4
        group by s.level,r.resourcetype order by s.level
    </select>
    <!--获取试题科目统计-->
    <select id="listQuestionSubjectNum" resultType="com.xhkjedu.sapi.vo.region.RSubjectVo">
        select s.level,s.subjectname,s.subjectid,count(q.questionid)snum
        ,sum(case when q.belong in(1,2) then 1 else 0 end)ptnum
        ,sum(case when q.belong=3 then 1 else 0 end)xbnum
        from t_question q left join t_subject s on q.subjectid=s.subjectid
        where q.qstate=1 and q.belong!=4
        group by q.subjectid order by s.level
    </select>
    <!--获取学校列表-->
    <select id="listSchool" resultType="java.util.Map">
        select s.schoollevel,s.schoolname,s.shortname,s.coordinate,count(c.classid)bjnum
        ,(select cloudmap from t_cloud limit 1)cloudmap
        ,(select count(*) from t_user u where u.schoolid=c.schoolid and u.usertype=1 and u.userstate=1)jsnum
        ,sum((select count(distinct cs.studentid) from t_class_student cs where cs.classid=c.classid))xsnum
        from t_school s left join t_class c on c.schoolid=s.schoolid and c.classstate=1 and c.year=s.year and c.classtype=1
        where s.schoolstate=1
        group by s.schoolid order by s.schoolid
    </select>
    <!--获取学段班级、教师、学生总量-->
    <select id="listClassTeacherStudentNum" resultType="java.util.Map">
        select s.schoollevel,count(c.classid)bjnum
        ,(select count(*) from t_user u where u.schoolid=c.schoolid and u.usertype=1 and u.userstate=1)jsnum
        ,sum((select count(distinct cs.studentid) from t_class_student cs where cs.classid=c.classid))xsnum
        from t_school s right join t_class c on s.schoolid=c.schoolid
        where s.schoolstate=1 and c.classstate=1 and c.year=s.year and c.classtype=1
        group by s.schoollevel
    </select>

    <!--教学统计-->
    <resultMap id="workMap" type="com.xhkjedu.sapi.vo.region.WorkVo">
        <result property="schoolid" column="schoolid"></result>
        <result property="schoolname" column="schoolname"></result>
        <result property="shortname" column="shortname"></result>
        <collection property="details" ofType="com.xhkjedu.sapi.vo.region.WorkDetailVo">
            <result property="wid" column="wid"></result>
            <result property="rid" column="rid"></result>
            <result property="stunum" column="stunum"></result>
            <result property="commitnum" column="commitnum"></result>
            <result property="checknum" column="checknum"></result>
        </collection>
    </resultMap>
    <!--作业提交批阅-->
    <select id="listPaperCommitMonth" resultMap="workMap">
        select s.schoolid,s.schoolname,s.shortname, pg.paperid as wid,
        count(distinct ps.psid)stunum,
        count(distinct case when ps.psstate!=0 then ps.psid else null end)commitnum,
        count(distinct case when ps.psstate!=0 and ps.pschecked=1 then ps.psid else null end)checknum
        from t_paper_group pg
        right join t_school s on pg.schoolid=s.schoolid and pg.pgstate=22 and pg.starttime between #{begintime} and #{endtime}
        left join t_paper_student ps on ps.paperid=pg.paperid
        left join t_class c on c.classid=pg.classid
        where s.schoolstate=1 and c.year=s.year
        group by s.schoolid,pg.paperid order by s.schoolid
    </select>
    <!--预习-->
    <select id="listGuideCommitMonth" resultMap="workMap">
        select s.schoolid,s.schoolname,s.shortname,g.guideid as rid,
        count(distinct gs.rgsid) stunum,
        count(distinct case when gs.readtime>0 then gs.rgsid else null end) commitnum
        from t_guide g right join t_school s on g.schoolid=s.schoolid and g.createtime between #{begintime} and #{endtime}
        left join t_guide_group_student gs on gs.guideid=g.guideid
        left join t_class c on c.classid=gs.classid
        where s.schoolstate=1 and c.year=s.year
        group by s.schoolid,g.guideid order by g.schoolid
    </select>

    <!--背诵-->
    <select id="listReciteCommitMonth" resultMap="workMap">
        select s.schoolid,s.schoolname,s.shortname,rg.reciteid as wid,
        count(distinct rs.rsid) stunum,
        count(distinct case when rs.rsstate!=0 then rs.rsid else null end) commitnum
        from t_recite_group rg right join t_school s on rg.schoolid=s.schoolid and rg.starttime between #{begintime} and #{endtime}
        left join t_recite_student rs on rs.reciteid=rg.reciteid and rs.classid=rg.classid
        left join t_class c on c.classid=rg.classid
        where s.schoolstate=1 and c.year=s.year
        group by s.schoolid,rg.reciteid order by s.schoolid
    </select>
    <!--课堂出勤率-->
    <select id="listRoomCommitMonth" resultType="java.util.Map">
        select s.schoolid,s.schoolname,s.shortname,ifnull(SUM(c.classnum),0) stunum,ifnull(SUM(c.realstunum),0) commitnum
        from t_classroom c left join t_class c2 on c.classid=c2.classid
        right join t_school s on c.schoolid=s.schoolid and c.roomstate=2 and c.createtime between #{begintime} and #{endtime}
        where s.schoolstate=1 and c2.year=s.year
        group by s.schoolid order by s.schoolid
    </select>

    <!--布置作业统计_布置作业次数-->
    <select id="listPapernumMonth" resultType="com.xhkjedu.sapi.vo.region.WorkVo">
        select s.schoolid,s.schoolname,s.shortname,count(distinct pg.paperid) num
        from t_paper_group pg left join t_class c on c.classid=pg.classid
        right join t_school s on pg.schoolid=s.schoolid and pg.pgstate=22 and pg.createtime between #{begintime} and #{endtime}
        where s.schoolstate=1 and c.year=s.year
        group by s.schoolid order by s.schoolid

    </select>

    <!--课堂使用情况统计——使用次数-->
    <select id="listRoomNumMonth" resultType="com.xhkjedu.sapi.vo.region.WorkVo">
        select s.schoolid,s.schoolname,s.shortname,count(c.roomid) num from t_classroom c
        right join t_school s on c.schoolid=s.schoolid and c.createtime between #{begintime} and #{endtime}
        left join t_class c2 on c.classid=c2.classid
        where s.schoolstate=1 and c2.year=s.year
        group by s.schoolid order by s.schoolid
    </select>
    <resultMap id="roomAsk" type="com.xhkjedu.sapi.vo.region.WorkVo">
        <result property="schoolid" column="schoolid"></result>
        <result property="schoolname" column="schoolname"></result>
        <result property="shortname" column="shortname"></result>
        <collection property="asks" ofType="com.xhkjedu.sapi.vo.region.WorkTypeVo">
            <result property="asktype" column="asktype"></result>
            <result property="asknum" column="asknum"></result>
        </collection>
    </resultMap>

    <!--课堂使用情况统计——课堂互动-->
    <select id="listRoomAskNumMonth" resultMap="roomAsk">
      select s.schoolid,s.schoolname,s.shortname,ca.asktype,count(ca.asktid) asknum from t_classroom_askteacher ca
        right join t_school s on ca.schoolid=s.schoolid
        left join t_classroom c on ca.roomid=c.roomid and c.createtime between #{begintime} and #{endtime}
        left join t_class c2 on c.classid=c2.classid
        where s.schoolstate=1 and c2.year=s.year
        group by s.schoolid,ca.asktype order by s.schoolid
    </select>
    <select id="listRoomAskNumMonth2" resultType="java.util.Map">
        select s.schoolname,s.shortname,count(distinct c.roomid)num
        ,sum(case when ca.asktype between 30 and 33 then 1 else 0 end)twnum
        ,sum(case when ca.asktype =40 then 1 else 0 end)tcnum
        ,sum(case when ca.asktype =21 then 1 else 0 end)dmnum
        ,sum(case when ca.asktype =20 then 1 else 0 end)qdnum
        from t_classroom c right join t_school s on c.schoolid=s.schoolid and c.createtime between #{begintime} and #{endtime}
        left join t_classroom_askteacher ca on ca.roomid=c.roomid
        left join t_class c2 on c.classid=c2.classid
        where s.schoolstate=1 and c2.year=s.year
        group by s.schoolid order by s.schoolid
    </select>
</mapper>
