<?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.analyze.SchoolPeopleMapper">
    <!-- 获取全校当前年份在校学生数量 -->
    <select id="getSchoolClassNum" resultType="com.xhkjedu.sapi.vo.analyze.SchoolPeopleNumVo">
        SELECT COUNT(DISTINCT c.classid) AS classnum,count(DISTINCT s.studentid) studentnum,
        CONVERT (count(DISTINCT s.studentid)/COUNT(DISTINCT c.classid),DECIMAL (10,0)) AS avgstudentnum,
        (SELECT COUNT(*) FROM t_user WHERE schoolid=c.schoolid AND usertype=1 AND userstate=1) AS teachernum
        FROM t_class c LEFT JOIN t_class_student s ON c.classid=s.classid
        WHERE c.schoolid=#{schoolid} AND c.year=#{year} and c.classtype=1 and c.classstate=1
    </select>
    <!-- 当前在校学生男女生数量 -->
    <select id="listSchoolStudentSexnum" resultType="com.xhkjedu.sapi.vo.analyze.SchoolPeopleStuSexVo">
        SELECT ifnull(u.usersex,0)usersex,COUNT(DISTINCT cs.studentid) sexnum 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
        WHERE c.schoolid=#{schoolid} AND c.year=#{year} and c.classtype=1 and c.classstate=1 GROUP BY u.usersex
    </select>

    <!-- 获取各年级下班级老师情况 -->
    <select id="listSchoolGrades" resultType="com.xhkjedu.sapi.vo.analyze.SchoolPeopleGradeVo">
       SELECT c.gradeid,COUNT(DISTINCT c.classid) classnum,count(DISTINCT s.studentid) studentnum,
       COUNT(DISTINCT u.userid) teachernum
       FROM t_class c LEFT JOIN t_class_student s ON c.classid=s.classid
       LEFT JOIN t_class_teacher ct ON c.classid=ct.classid
       LEFT JOIN t_user u ON ct.teacherid=u.userid AND u.usertype=1 AND u.userstate=1
       WHERE c.schoolid=#{schoolid} AND c.year=#{year} and c.classtype=1 and c.classstate=1 GROUP BY c.gradeid
    </select>

    <!--年级下各班级学生数量各类统计-->
    <resultMap id="schoolGradeClass" type="com.xhkjedu.sapi.vo.analyze.SchoolPeopleGradeVo">
        <result property="gradeid" column="gradeid"></result>
        <collection property="classlst" ofType="com.xhkjedu.sapi.vo.analyze.SchoolPeopleGradeClassVo">
            <result property="classid" column="classid"></result>
            <result property="classname" column="classname"></result>
            <result property="classnum" column="classnum"></result>
            <result property="boynum" column="boynum"></result>
            <result property="girlnum" column="girlnum"></result>
            <result property="wzxbnum" column="wzxbnum"></result>
        </collection>
    </resultMap>
    <select id="listSchoolGradeClasses" resultMap="schoolGradeClass">
        SELECT DISTINCT c.classid,c.classname,c.gradeid,c.classnum
        ,SUM(CASE WHEN u.usersex=1 THEN 1 ELSE 0 END)boynum
        ,SUM(CASE WHEN u.usersex=2 THEN 1 ELSE 0 END)girlnum
        ,SUM(CASE WHEN u.usersex=0 THEN 1 ELSE 0 END)wzxbnum
        FROM t_class_student cs RIGHT JOIN t_class c ON cs.classid=c.classid
        LEFT JOIN t_user u ON cs.studentid=u.userid
        WHERE c.schoolid=#{schoolid} AND c.year=#{year} and c.classtype=1 and c.classstate=1
        GROUP BY c.gradeid,c.classid ORDER BY c.gradeid,c.classorder
    </select>


</mapper>
