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

    <!--资源统计——资源数量-->
    <select id="getRResourceNum" resultType="java.util.Map">
        select count(r.resourceid) resourcenum,sum(r.resourcesize)resourcesize,sum(r.downcount) downcount
         from t_resource r where r.resourcestate=1 and r.belong!=4 and (r.level=1 or r.level=4)
    </select>
    <select id="getRResourceCollectNum" resultType="java.lang.Integer">
        select count(*) from t_resource_collect rc
        left join t_resource r on rc.resourceid=r.resourceid where r.resourcestate=1 and r.belong!=4 and (r.level=1 or r.level=4)
    </select>
    <!--资源统计——试题数量-->
    <select id="getRQuestionNum" resultType="java.lang.Integer">
        select count(*) from t_question where qstate=1 and belong!=4
    </select>

    <!--学段资源占比-->
    <select id="listRResourceLevelTypeNum" resultType="com.xhkjedu.sapi.vo.region.ResourceTypeVo">
         select s.level,r.resourcetype rtype,count(r.resourceid)rnum
          from t_resource r left join t_subject s on r.subjectid=s.subjectid
          where r.resourcestate=1 and r.belong!=4 and (r.level=1 or r.level=4)
          group by s.level,r.resourcetype order by s.level,r.resourcetype
    </select>
    <!--资源格式统计-->
    <select id="listRResourceClassNum" resultType="com.xhkjedu.sapi.vo.region.ResourceTypeVo">
         select r.resourceclass rtype,count(r.resourceid)rnum from t_resource r
          where r.resourcestate=1 and r.belong!=4 and (r.level=1 or r.level=4)
          group by r.resourceclass order by r.resourceclass
    </select>
    <!--试题学段占比-->
    <select id="listRQuestionLevelNum" resultType="com.xhkjedu.sapi.vo.region.RLevelVo">
        select s.level,count(q.questionid) levelnum
        from t_question q left join t_subject s on q.subjectid=s.subjectid
        where q.qstate=1 and q.belong!=4 group by s.level order by s.level
    </select>
    <!--试题使用统计-->
    <select id="listRQuestionUse" resultType="com.xhkjedu.sapi.vo.region.RSchoolVo">
        select count(distinct pqq.questionid) snum,pqq.schoolid,s.schoolname,s.shortname
        from t_paper_qtype_question pqq left join t_paper p on pqq.paperid=p.paperid
        left join t_school s on pqq.schoolid=s.schoolid
        left join t_question q on pqq.questionid=q.questionid
        where p.paperstate=22 and q.qstate=1 and q.belong!=4 and s.schoolstate=1
         group by pqq.schoolid order by s.schoolid
    </select>
    <!--校本资源统计-->
    <select id="listRResourceXbType" resultType="com.xhkjedu.sapi.vo.region.RSResourceVo">
         select ifnull(r.resourcetype,0) rtype,count(r.resourceid)rnum,s.schoolid,s.schoolname,s.shortname,s.schoollevel level
          from t_resource r right join t_school s on r.schoolid=s.schoolid
          and r.resourcestate=1 and r.belong=3 and (r.level=1 or r.level=4)
          where s.schoolstate=1
          group by r.schoolid,r.resourcetype order by r.schoolid,r.resourcetype
    </select>

</mapper>
