<?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.RSchoolAnalyzeMapper">
    <!--获取学校列表-->
    <select id="listSchool" resultType="com.xhkjedu.sapi.vo.region.SchoolVo">
        select s.schoollevel,s.schoolname,s.shortname,s.coordinate,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 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.schoollevel,s.schoolid
    </select>
    <!--教师性别统计-->
    <select id="listTeacherSex" resultType="com.xhkjedu.sapi.vo.region.RSTeacherVo">
        select ifnull(u.usersex,0)usersex,count(*)num,s.schoolid,s.schoolname,s.shortname,s.schoollevel level
        from t_school s left join t_user u on u.schoolid=s.schoolid
        where s.schoolstate=1 and u.usertype=1 and u.userstate=1
        group by s.schoolid,u.usersex;
    </select>
    <!--教师科目统计-->
    <select id="listTeacherSubject" resultType="com.xhkjedu.sapi.vo.region.RSTeacherVo">
        select sb.level,sb.subjectid,ts.subjectname,ts.subjectorder
        from t_school s left join t_teacher_powersb tp on tp.schoolid=s.schoolid
        left join t_subject_book sb on sb.lsbid=tp.lsbid
        left join t_subject ts on sb.subjectid=ts.subjectid
        where s.schoolstate=1 and tp.teacherid is not null
        group by tp.teacherid,sb.subjectid
    </select>
    <!--学生统计-->
    <select id="listStu" resultType="com.xhkjedu.sapi.vo.region.RSStuVo">
        select ifnull(u.usersex,0)usersex,c.gradeid,s.schoollevel level,s.schoolid,s.schoolname,s.shortname
        from t_class_student cs left join t_user u on cs.studentid=u.userid
        left join t_class c on cs.classid=c.classid
        left join t_school s on c.schoolid=s.schoolid
        where s.schoolstate=1 and c.classtype=1 and c.classstate=1 and c.year=s.year
    </select>
    <!--活跃度统计-->
    <select id="listAccess" resultType="java.util.Map">
        select s.schoolid,s.schoolname,s.shortname,ceil(ifnull(count(l.ulid)/count(distinct u.userid),0))num,s.schoollevel level
        from t_school s left join t_user u on u.schoolid=s.schoolid and u.usertype in(1,2)
        left join t_user_log l on u.userid=l.userid and l.logtime between #{begintime} and #{endtime}
        where s.schoolstate=1
        group by s.schoolid order by num desc
    </select>
</mapper>
