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

    <!--资源统计——资源数量-->
    <select id="getSchoolResourceNum" resultType="java.util.Map">
        select count(r.resourceid) resourcenum,sum(r.resourcesize)resourcesize,sum(r.downcount) downcount
         from t_resource r left join t_subject s on r.subjectid=s.subjectid
         left join t_school l on s.level=l.schoollevel
          where r.resourcestate=1 and (r.belong &lt; 3 or (r.belong=3 and r.schoolid=#{schoolid}))
          and l.schoolid=#{schoolid}
          and (r.level=1 or r.level=4)
    </select>
    <select id="getSchoolResourceCollectNum" resultType="java.lang.Integer">
        select count(distinct rc.resourceid) from t_resource_collect rc
        left join t_resource r on rc.resourceid=r.resourceid
        left join t_subject s on r.subjectid=s.subjectid
        left join t_school l on s.level=l.schoollevel
        where r.resourcestate=1
        and (r.belong &lt; 3 or (r.belong=3 and r.schoolid=#{schoolid})) and (r.level=1 or r.level=4) and l.schoolid=#{schoolid}
    </select>
    <select id="getSchoolResourceTotalNum" resultType="java.lang.Integer">
        select count(*) from t_resource
        where resourcestate=1 and belong=3 and schoolid=#{schoolid} and (level=1 or level=4)
    </select>
    <!--资源统计——资源分类统计-->
    <select id="listSchoolResourceSubjectTypenum" resultType="com.xhkjedu.sapi.vo.region.ResourceTypeVo">
        select r.subjectid,s.subjectname,r.resourcetype rtype,count(r.resourceid)rnum
        from t_resource r left join t_subject s on r.subjectid=s.subjectid
        left join t_school l on s.level=l.schoollevel
        where r.resourcestate=1 and (r.belong &lt; 3 or (r.belong=3 and r.schoolid=#{schoolid})) and (r.level=1 or r.level=4)
        and l.schoolid=#{schoolid}
        group by r.subjectid,r.resourcetype order by s.subjectorder,r.resourcetype
    </select>
    <!--资源统计——资源格式统计-->
    <select id="listSchoolResourceSubjectClassnum" resultType="com.xhkjedu.sapi.vo.region.ResourceTypeVo">
        select r.resourceclass rtype,count(r.resourceid)rnum
        from t_resource r left join t_subject s on r.subjectid=s.subjectid
        left join t_school l on s.level=l.schoollevel
        where r.resourcestate=1 and (r.belong &lt; 3 or (r.belong=3 and r.schoolid=#{schoolid})) and (r.level=1 or r.level=4)
        and l.schoolid=#{schoolid}
        group by r.resourceclass order by r.resourceclass
    </select>

    <!--试题统计——学科数量-->
    <select id="listSchoolQuestionSubjectnum" resultType="com.xhkjedu.sapi.vo.region.RSubjectVo">
       select s.subjectname,count(q.questionid) snum
        from t_question q left join t_subject s on q.subjectid=s.subjectid
        left join t_school l on s.level=l.schoollevel
        where q.qstate=1 and (q.belong &lt; 3 or (q.belong=3 and q.schoolid=#{schoolid}))
        and l.schoolid=#{schoolid}
        group by q.subjectid order by s.subjectorder
    </select>
    <!--试题统计——校本试题学科数量-->
    <select id="getSchoolQuestionXbSubjectnum" resultType="com.xhkjedu.sapi.vo.region.RSubjectVo">
        select s.subjectname,count(q.questionid) snum
        from t_question q left join t_subject s on q.subjectid=s.subjectid
        where q.qstate=1 and q.belong=3 and q.schoolid=#{schoolid}
        group by q.subjectid order by snum
    </select>


    <!--资源统计——校本资源统计-->
    <select id="listSchoolResourceSchool" resultType="com.xhkjedu.sapi.vo.region.RSubjectVo">
        select r.subjectid,s.subjectname,count(r.resourceid)snum
        from t_resource r left join t_subject s on r.subjectid=s.subjectid
        where r.resourcestate=1 and r.belong=3 and r.schoolid=#{schoolid} and (r.level=1 or r.level=4)
        group by r.subjectid order by s.subjectorder
    </select>


    <!--热门资源统计-->
    <select id="listSchoolResourceHot" resultType="java.util.Map">
      select resourcename,readcount from t_resource r
      left join t_subject s on r.subjectid=s.subjectid
      left join t_school l on s.level=l.schoollevel
      where r.resourcestate=1
      and (r.belong &lt; 3 or (r.belong=3 and r.schoolid=#{schoolid})) and (r.level=1 or r.level=4)
      and l.schoolid=#{schoolid}
        order by readcount desc limit 40
    </select>

    <!--资源统计——近一个月校本资源贡献统计-->
    <select id="listSchoolResourceForSchool" resultType="com.xhkjedu.sapi.vo.region.ResourceTypeVo">
        select r.subjectid,s.subjectname,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=3 and r.schoolid=#{schoolid} and (r.level=1 or r.level=4)
				and r.createtime between #{begintime} and #{endtime}
        group by r.subjectid,r.resourcetype order by s.subjectorder,r.resourcetype
    </select>


</mapper>
