<?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.srs.comprehensive.mapper.SrsStudentMapper">

    <resultMap type="SrsStudent" id="SrsStudentResult">
        <result property="stuId"    column="stu_id"    />
        <result property="name"    column="name"    />
        <result property="stuNo"    column="stu_no"    />
        <result property="gender"    column="gender"    />
        <result property="birthday"    column="birthday"    />
        <result property="idCard"    column="id_card"    />
        <result property="deptId"    column="dept_id"    />
        <result property="majorId"    column="major_id"    />
        <result property="classId"    column="class_id"    />
        <result property="phone"    column="phone"    />
        <result property="address"    column="address"    />
        <result property="status"    column="status"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="xhk"    column="xhk"    />
        <result property="bankAddr"    column="bank_addr"    />
        <result property="teacherId"    column="teacher_id"    />
        <result property="cphName" column="cphName"  />
        <result property="gradeName" column="grade_name" />
        <result property="status2" column="status2" />
        <association property="dept"    column="dept_id" javaType="SysDept" resultMap="deptResult" />
        <association property="srsMajors"    column="major_id" javaType="srsMajors" resultMap="SrsMajorsResult" />
        <association property="srsClass"    column="class_id" javaType="srsClass" resultMap="SrsClassResult" />
        <association property="cphTeacher"    column="teacher_id" javaType="cphTeacher" resultMap="CphTeacherResult" />
    </resultMap>
    <resultMap id="CphTeacherResult" type="CphTeacher">
        <result property="teacherId" column="teacher_id"/>
        <result property="name" column="name"/>
    </resultMap>
    <resultMap type="SrsGrade" id="SrsGradeResult">
        <result property="gradeId"    column="grade_id"    />
        <result property="gradeName"    column="grade_name"    />
    </resultMap>
    <resultMap type="SrsClass" id="SrsClassResult">
        <result property="classId"    column="class_id"    />
        <result property="majorId"    column="major_id"    />
        <result property="className"    column="class_name"    />
    </resultMap>
    <resultMap type="SrsMajors" id="SrsMajorsResult">
        <result property="majorId"    column="major_id"    />
        <result property="majorName"    column="major_name"    />
        <result property="collegeId"    column="college_id"    />
        <result property="majorDescription"    column="major_description"    />
    </resultMap>
    <resultMap id="deptResult" type="SysDept">
        <id     property="deptId"    column="dept_id"     />
        <result property="parentId"  column="parent_id"   />
        <result property="deptName"  column="dept_name"   />
        <result property="ancestors" column="ancestors"   />
        <result property="orderNum"  column="order_num"   />
        <result property="leader"    column="leader"      />
        <result property="status"    column="dept_status" />
    </resultMap>

    <resultMap id="studentName" type="CascaderEntity">
        <id     property="value"    column="stu_id"     />
        <result property="label"  column="name"   />
    </resultMap>


    <sql id="selectSrsStudentVo">
        select s.stu_id,s.stu_no, s.name, s.gender, s.birthday, AES_DECRYPT(UNHEX(s.id_card),'zhxg') as id_card , s.dept_id, major_id, class_id, s.phone, s.address,
               s.status, s.create_by, s.create_time, s.update_by, s.update_time, s.del_flag,s.xhk,s.bank_addr
        from srs_student s

    </sql>
    <sql id="selectSrsStudentJoin">
        select s.stu_id,s.stu_no, s.name, s.gender, s.birthday,AES_DECRYPT(UNHEX(s.id_card),'zhxg') as id_card , d.dept_id, m.major_id, c.class_id, s.phone, s.address,
               s.status, s.status as status2,s.create_by, s.create_time, s.update_by, s.update_time, s.del_flag,s.xhk,s.bank_addr,e.name as cphName,f.grade_name,
               d.*,m.*,c.*
        from srs_student s
                 left JOIN srs_class c ON s.class_id = c.class_id
                 left JOIN srs_majors m ON c.major_id = m.major_id
                 left JOIN sys_dept d ON m.college_id = d.dept_id
                 left join cph_teacher as e on c.teacher_id = e.teacher_id
                 left join srs_grade as f on f.grade_id=c.grade_id
    </sql>
    <select id="selectSrsStudentList" parameterType="SrsStudent" resultMap="SrsStudentResult">
        <include refid="selectSrsStudentVo"/>
        <where>
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="gender != null  and gender != ''"> and gender = #{gender}</if>
            <if test="birthday != null "> and birthday = #{birthday}</if>
            <if test="idCard != null  and idCard != ''"> and id_card = #{idCard}</if>
            <if test="deptId != null "> and dept_id = #{deptId}</if>
            <if test="majorId != null "> and major_id = #{majorId}</if>
            <if test="classId != null "> and class_id = #{classId}</if>
            <if test="phone != null  and phone != ''"> and phone like concat('%',  #{phone}  , '%')</if>
            <if test="address != null  and address != ''"> and address like concat('%',  #{address} , '%')</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
        </where>
    </select>
    <select id="selectSrsStudentNameList" parameterType="Long" resultMap="studentName">
        select  stu_id,name from srs_student where class_id = #{id}
    </select>

    <select id="selectSrsStudentListJoin" parameterType="SrsStudent" resultMap="SrsStudentResult">
        <include refid="selectSrsStudentJoin"/>
        <where>
            <if test="stuNo != null  and stuNo != ''"> and s.stu_no like concat('%', #{stuNo}, '%')</if>
            <if test="name != null  and name != ''"> and s.name like concat('%', #{name}, '%')</if>
            <if test="gender != null  and gender != ''"> and s.gender = #{gender}</if>
            <if test="birthday != null "> and s.birthday = #{birthday}</if>
            <if test="idCard != null  and idCard != ''"> and s.id_card = #{idCard}</if>
            <if test="deptId != null "> and d.dept_id = #{deptId}</if>
            <if test="majorId != null "> and m.major_id = #{majorId}</if>
            <if test="classId != null "> and s.class_id = #{classId}</if>
            <if test="phone != null  and phone != ''"> and s.phone like concat('%',  #{phone}  , '%')</if>
            <if test="address != null  and address != ''"> and s.address like concat('%',  #{address} , '%')</if>
            <if test="status != null  and status != ''"> and s.status = #{status}</if>
            <if test="tNo != null  and tNo != ''"> and e.employee_id = #{tNo}</if>
            <if test="gradeId != null"> and c.grade_id = #{gradeId}</if>
            <if test="cphName != null and cphName!=''"> and e.name = #{cphName}</if>
        </where>
        order by s.stu_no desc
    </select>

    <select id="getOwnClass" resultType="com.srs.comprehensive.domain.Vo.StuClass" >
        select a.stu_no,a.`name` as stu_name,b.class_name from srs_student as a
        left join srs_class as b on a.class_id = b.class_id
        where a.stu_no = #{stuNo}
    </select>

    <select id="listEnableStu" resultType="SrsStudent">
        select * from srs_student where `status` = '01'
    </select>

    <select id="selectStuNoNameByClass" resultType="com.srs.comprehensive.domain.Vo.StuNoName">
        select stu_no,`name` as stu_name from srs_student
        where class_id = #{id}
    </select>

    <select id="selectSrsStudentByStuId" parameterType="Long" resultMap="SrsStudentResult">
        <include refid="selectSrsStudentJoin"/>
        where stu_id = #{stuId}
    </select>
    <select id="selectSrsStudentByStuNo" parameterType="string" resultMap="SrsStudentResult">
        <include refid="selectSrsStudentVo"/>
        where stu_no = #{stuNo}
    </select>
    <select id="selectClassId" resultType="java.lang.Long">
        select class_id from srs_class where class_code=#{bjdm}
    </select>
    <insert id="insertSrsStudent" parameterType="SrsStudent" useGeneratedKeys="true" keyProperty="stuId">
        insert into srs_student
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">name,</if>
            <if test="stuNo != null">stu_no,</if>
            <if test="gender != null">gender,</if>
            <if test="birthday != null">birthday,</if>
            <if test="idCard != null">id_card,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="majorId != null">major_id,</if>
            <if test="classId != null">class_id,</if>
            <if test="phone != null">phone,</if>
            <if test="address != null">address,</if>
            <if test="status != null and status != ''">status,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="delFlag != null and delFlag != ''">del_flag,</if>
            <if test="roomId != null">room_id,</if>

         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">#{name},</if>
            <if test="stuNo != null">#{stuNo},</if>
            <if test="gender != null">#{gender},</if>
            <if test="birthday != null">#{birthday},</if>
            <if test="idCard != null">HEX(AES_ENCRYPT(#{idCard},'zhxg')),</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="majorId != null">#{majorId},</if>
            <if test="classId != null">#{classId},</if>
            <if test="phone != null">#{phone},</if>
            <if test="address != null">#{address},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="delFlag != null and delFlag != ''">#{delFlag},</if>
            <if test="roomId != null">#{roomId},</if>
         </trim>
    </insert>

    <update id="updateSrsStudent" parameterType="SrsStudent">
        update srs_student
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null">name = #{name},</if>
            <if test="stuNo != null">stu_no = #{stuNo},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="birthday != null">birthday = #{birthday},</if>
            <if test="idCard != null">id_card = HEX(AES_ENCRYPT(#{idCard},'zhxg')),</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="majorId != null">major_id = #{majorId},</if>
            <if test="classId != null">class_id = #{classId},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="address != null">address = #{address},</if>
            <if test="status != null and status != ''">status = #{status},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="delFlag != null and delFlag != ''">del_flag = #{delFlag},</if>
            <if test="roomId != null">room_id = #{roomId},</if>
        </trim>
        where stu_id = #{stuId}
    </update>

    <delete id="deleteSrsStudentByStuId" parameterType="Long">
        delete from srs_student where stu_id = #{stuId}
    </delete>

    <delete id="deleteSrsStudentByStuIds" parameterType="String">
        delete from srs_student where stu_id in
        <foreach item="stuId" collection="array" open="(" separator="," close=")">
            #{stuId}
        </foreach>
    </delete>

    <!-- 批量更新 -->
    <update id="batchUpdateSrsStudent" parameterType="java.util.List">
        <foreach collection="list" item="student" separator=";">
            UPDATE srs_student
            SET name = #{student.name},
            gender = #{student.gender},
            status = #{student.status},
            class_id = #{student.classId}
            WHERE stu_no = #{student.stuNo}
        </foreach>
    </update>
<!--    批量更新代码-->
    <update id="updateSrsStudentList" parameterType="java.util.List">
        update srs_student
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="name=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.name != null ">
                        when stu_no=#{i.stuNo} then #{i.name}
                    </if>
                </foreach>
            </trim>
            <trim prefix="class_id=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.classId != null ">
                        when stu_no=#{i.stuNo} then #{i.classId}
                    </if>
                </foreach>
            </trim>
            <trim prefix="gender=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.gender != null ">
                        when stu_no=#{i.stuNo} then #{i.gender}
                    </if>
                </foreach>
            </trim>
            <trim prefix="id_card=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.idCard != null ">
                        when stu_no=#{i.stuNo} then #{i.idCard}
                    </if>
                </foreach>
            </trim>
            <trim prefix="class_code=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.classCode != null ">
                        when stu_no=#{i.stuNo} then #{i.classCode}
                    </if>
                </foreach>
            </trim>
            <trim prefix="status=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.status != null ">
                        when stu_no=#{i.stuNo} then #{i.status}
                    </if>
                </foreach>
            </trim>
            <trim prefix="update_time=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.updateTime != null ">
                        when stu_no=#{i.stuNo} then #{i.updateTime}
                    </if>
                </foreach>
            </trim>
            <trim prefix="address=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.address != null ">
                        when stu_no=#{i.stuNo} then #{i.address}
                    </if>
                </foreach>
            </trim>
        </trim>
        where stu_no in
        <foreach collection="studentList" separator="," item="i" index="index" open="(" close=")">
            #{i.stuNo}
        </foreach>
    </update>
    <update id="updateSrsStudent2">
        update srs_student
        <trim prefix="SET" suffixOverrides=",">
            <if test="status != null and status != ''">status = #{status}</if>
        </trim>
        where stu_no = #{stuNo}
    </update>

    <!-- 批量插入 -->
    <insert id="batchInsertSrsStudent" parameterType="java.util.List">
        INSERT INTO srs_student (name, gender, stu_no, status, class_id)
        VALUES
        <foreach collection="list" item="student" separator=",">
            (#{student.name}, #{student.gender}, #{student.stuNo}, #{student.status}, #{student.classId})
        </foreach>
    </insert>

    <select id="countDeptGradeStu" resultType="StuCount">
        select count(a.stu_no) as stu_count,
               case a.dept_name is NULL
                   when true then "无学院"
                   else a.dept_name end as dept_name,
               case a.grade_name is NULL
                   when true then "无年级"
                   else a.grade_name end as grade_name
        from view_stu_info as a
        where a.`status` = "01" and a.grade_name is not NULL
             and a.dept_name is not NULL
        GROUP BY a.dept_id,a.grade_id
        order by a.dept_id
    </select>

    <select id="countDeptStu" resultType="StuCount">
        select count(a.stu_no) as stu_count,
               case a.dept_name is null
                   when true then "无学院"
                   else a.dept_name end as dept_name
        from view_stu_info as a
        where a.`status` = "01" and a.dept_name is not null
        GROUP BY a.dept_id
        order by a.dept_id
    </select>


    <select id="countGoodBiyeGradeStu"  resultType="StuCount">
        select count(a.stu_no) as stu_count,b.grade_name
        from cph_biye_apply as a
                 left join view_stu_info as b on a.stu_no = b.stu_no
        where a.apply_status = 6
        GROUP BY b.grade_id
    </select>

    <select id="countGoodGradeStu" resultType="StuCount" >
        select count(a.stu_no) as stu_count,
               case b.grade_name is null
                   when true then "无年级"
                   else b.grade_name end as grade_name,
               b.gender
        from cph_good_apply as a
                 left join view_stu_info as b on a.stu_no = b.stu_no
        where b.grade_name is not null
        GROUP BY b.grade_id, b.gender
        order by b.grade_id
    </select>

    <select id="countLakeGradeStu" resultType="StuCount" >
        select count(a.stu_no) as stu_count,
               case b.grade_name is null
                   when true then "无年级"
                   else b.grade_name end as grade_name
        from cph_lake_apply as a
                 left join view_stu_info as b on a.stu_no = b.stu_no
        GROUP BY b.grade_id
        order by b.grade_id
    </select>

    <select id="countParkStu" resultType="StuCount" >
        select sum(b.occupancy) as all_in,a.park_name,a.campus_name,sum(a.bed_num) as `all`,
               (
                   select count(t1.stu_no) from view_stu_info as t1 where t1.`status` = '01'
               ) as stu_count
        from view_dorm_info as a
                 left join view_dorm_stu_count as b on a.id = b.dormitory_id
        GROUP BY a.park_id
    </select>

    <select id="selectDeptLeveaDataCount" resultType="com.srs.comprehensive.domain.Vo.DeptLeaveDataVo">
        SELECT
            c.dept_id AS deptId,
            h.grade_name AS gradeName,
            c.dept_name AS departmentName,
            COUNT(CASE WHEN a.status = '1' THEN 1 END) leaveCount
        FROM rt_stu_leave_application a
                 LEFT JOIN srs_student b ON a.stu_id=b.stu_id
                 LEFT JOIN srs_class e ON b.class_id = e.class_id
                 LEFT JOIN srs_majors d ON e.major_id = d.major_id
                 LEFT JOIN sys_dept c ON d.college_id = c.dept_id
                 LEFT JOIN srs_grade h ON e.grade_id = h.grade_id
        GROUP BY c.dept_id,h.grade_name,c.dept_name
    </select>
    <select id="selectStuQuitSchoolCount" resultType="com.srs.comprehensive.domain.Vo.DeptStuQuitPunisDataVo">
        SELECT
            department_Name deptName,
            COUNT(*) AS number
        FROM
            rt_stu_quit_school
        WHERE
            apply_status = 1
        GROUP BY
            department_Name
    </select>

    <select id="selectStuPunishCount" resultType="com.srs.comprehensive.domain.Vo.DeptStuQuitPunisDataVo">
        SELECT
            department_Name AS deptName,
            COUNT(*) AS number
        FROM
            rt_stu_disciplinary_application
        WHERE
            penalty_status = 0
        GROUP BY
            department_Name
    </select>

    <select id="selectKnrdCount" resultType="com.srs.comprehensive.domain.Vo.DeptStuQuitPunisDataVo">
        SELECT xy AS deptName, COUNT(id) AS number FROM `srs_knrd_apply`
        where  stu_year_id =  (SELECT id FROM srs_stu_year WHERE `status`=1)  and step >= 5 and xsqmyj !=4  group by  xy
    </select>

    <select id="selectZxjCount" resultType="com.srs.comprehensive.domain.Vo.DeptStuQuitPunisDataVo">
        SELECT xy AS deptName, COUNT(id) AS number FROM `srs_zxj_apply`
        where  stu_year_id =  (SELECT id FROM srs_stu_year WHERE `status`=1)  and step =4  group by  xy
    </select>

    <select id="selectDeptCountByCampusLJ" resultType="com.srs.comprehensive.domain.Vo.DeptStuQuitPunisDataVo">
        SELECT  a.dept_name deptName,count(*) number  FROM view_stu_info a
       INNER JOIN srs_grade b ON a.grade_code = b.grade_code
        WHERE b.grade_status in (1,3) AND dept_name in ('自动化工程学院','水利工程学院','电力工程学院','水利工程学院','土木建筑工程学院','机电工程学院','交通工程学院','合作分校')
        GROUP BY dept_name
    </select>
    <select id="selectDeptCountByCampusCG" resultType="com.srs.comprehensive.domain.Vo.DeptStuQuitPunisDataVo">
        SELECT  a.dept_name deptName,count(*) number  FROM view_stu_info a
       INNER JOIN srs_grade b ON a.grade_code = b.grade_code
        WHERE b.grade_status in (1,3) AND dept_name in ('信息工程学院','经济管理学院')
        GROUP BY dept_name
    </select>

    <select id="selectGradeCount" resultType="com.srs.comprehensive.domain.Vo.GradeStuDataVo">
        SELECT b.grade_name grade,count(*) number FROM view_stu_info a
       INNER JOIN srs_grade b ON a.grade_code = b.grade_code
        WHERE b.grade_status in (1,3)
        GROUP BY b.grade_name
    </select>

    <select id="countDeptGradeStuNew" resultType="Map">
        select  a.dept_name,a.grade_name,count(1) as num
        from view_stu_info as a
                 left join srs_grade as b on b.grade_id = a.grade_id
        where b.grade_status in (1,3) and dept_name is not null and a.`status` = '01'
        group by a.dept_name,a.grade_name
        order by dept_name,grade_name
    </select>

    <select id="countMajorStuByDept" resultType="Map" parameterType="CphSearch">
        select t1.*,t2.major_name,t3.dept_name
        from
            (
                select count(1) as num,a.major_code
                from view_stu_info as a
                where a.dept_name = #{deptName} and a.`status` = '01'
                GROUP BY a.major_code
            ) as t1
                left join srs_majors as t2 on t1.major_code = t2.major_code
                left join sys_dept as t3 on t2.college_id = t3.dept_id
        order by t1.major_code
    </select>

    <select id="countDeptStuNew" resultType="Map">
        select count(1) as `value`,a.dept_name as `name`
        from view_stu_info as a
        left join srs_grade as b on a.grade_id = b.grade_id
        where a.`status` = '01' and a.dept_name is not null and grade_status in (1,3)
        group by a.dept_name
    </select>

    <select id="countGradeStuNew" resultType="Map">
        select count(1) as `value`,a.grade_name as `name`
        from view_stu_info as a
                 left join srs_grade as b on a.grade_id = b.grade_id
        where a.`status` = '01' and a.grade_name is not null and b.grade_status in (1,3)
        group by a.grade_name
    </select>

    <select id="selectViewStuInfo" parameterType="CphSearch" resultType="Map">
        select a.* from view_stu_info as a
        <where>
            <if test="deptId != null">and a.dept_id = #{deptId}</if>
            <if test="deptName != null">and a.dept_name = #{deptName}</if>
            <if test="gradeId != null">and a.grade_id = #{gradeId}</if>
        </where>
    </select>
    <select id="getRtStuQuitSchool" resultType="com.srs.comprehensive.domain.Vo.RtStuQuitSchoolVo" parameterType="stuDataSelcetDto">
        select quit_school_id,stu_no,stu_name,applicant_name,department_name,grade_name,class_name,jg,mz,quit_type,quit_category,quit_year,process_instance_id,deploy_id from rt_stu_quit_school
        <where>
            apply_status = 1
            <if test="departmentName != null and departmentName!= '' ">and department_name=#{departmentName} </if>
            <if test="stuNo != null and stuNo!= '' ">and stu_no=#{stuNo} </if>
            <if test="stuName != null and stuName!= '' ">and stu_name=#{stuName} </if>
        </where>
        order by grade_name,class_name
    </select>
    <select id="getRtStuDisciplinaryApplication" resultType="com.srs.comprehensive.domain.Vo.RtStuDisciplinaryApplicationVo" parameterType="stuDataSelcetDto">
        select application_id,stu_no,stu_name,applicant_name,department_name,grade_name,class_name,jg,mz,penalty_type,process_instance_id,deploy_id  from rt_stu_disciplinary_application
        <where>
            penalty_status = 0
            <if test="departmentName != null and departmentName!= '' ">and department_name=#{departmentName} </if>
            <if test="stuNo != null and stuNo!= '' ">and stu_no=#{stuNo} </if>
            <if test="stuName != null and stuName!= '' ">and stu_name=#{stuName} </if>
        </where>
        order by grade_name,class_name
    </select>
    <select id="getRtStuDropOutSchool" resultType="com.srs.comprehensive.domain.Vo.RtStuDropOutSchoolVo" parameterType="stuDataSelcetDto">
        select drop_out_school_id,stu_no,stu_name,applicant_name,department_name,grade_name,class_name,jg,mz,quit_year,drop_out_type,drop_out_category,process_instance_id,deploy_id from rt_stu_drop_out_school
        <where>
            apply_status = 1
            <if test="departmentName != null and departmentName!= '' ">and department_name=#{departmentName} </if>
            <if test="stuNo != null and stuNo!= '' ">and stu_no=#{stuNo} </if>
            <if test="stuName != null and stuName!= '' ">and stu_name=#{stuName} </if>
        </where>
        order by grade_name,class_name
    </select>
    <select id="getSrsKnrdApply" resultType="com.srs.comprehensive.domain.Vo.SrsKnrdApplyVo" parameterType="stuDataSelcetDto">
        select id,xh,xm,xb,mz,hjszd,bj,xy,nj,xsqmyj,apply_year from srs_knrd_apply a
        where  stu_year_id =  (select id from srs_stu_year where status=1)  and step >= 5 and xsqmyj !=4
        <if test="departmentName != null and departmentName!= '' ">and xy=#{departmentName} </if>
        <if test="stuNo != null and stuNo!= '' ">and xh=#{stuNo} </if>
        <if test="stuName != null and stuName!= '' ">and xm=#{stuName} </if>
        order by NJ desc,bj desc
    </select>
    <select id="getSrsZxjApply" resultType="com.srs.comprehensive.domain.Vo.SrsZxjApplyVo" parameterType="stuDataSelcetDto">
        select id,xh,xm,xb,mz,dz,bj,xy,nj, kndj,apply_year  from srs_zxj_apply
        where  stu_year_id =  (select id FROM srs_stu_year where status=1)  and step =4
        <if test="departmentName != null and departmentName!= '' ">and xy=#{departmentName} </if>
        <if test="stuNo != null and stuNo!= '' ">and xh=#{stuNo} </if>
        <if test="stuName != null and stuName!= '' ">and xm=#{stuName} </if>
            order by NJ desc,bj desc
    </select>
    <select id="selectStuDropOutSchoolCount"
            resultType="com.srs.comprehensive.domain.Vo.DeptStuQuitPunisDataVo">
        SELECT
            department_Name AS deptName,
            COUNT(*) AS number
        FROM
            rt_stu_drop_out_school
        WHERE
            apply_status = 1
        GROUP BY
            department_Name
    </select>
    <select id="getSrsHZCount" resultType="java.util.Map">
        select hkxz ,count(1) as quantity  from  cph_stu_extra_info  where hkxz is not null  group by hkxz
    </select>
    <select id="getSrsZZCount" resultType="java.util.Map">
        select zzmm ,count(1) as quantity  from  cph_stu_extra_info  where zzmm is not null and zzmm in('群众','中共党员','共青团员')  group by zzmm
    </select>
    <select id="getSrsGenderCount" resultType="java.util.Map">
        select gender,count(1) as  quantity from srs_student a left join srs_class b on a.class_id=b.class_id
        left join srs_grade c on c.grade_id=b.grade_id where c.grade_status in(1,3) and gender is not null   group by gender
    </select>
    <select id="getSrsknrdCount" resultType="java.util.Map">
        select  (case
                     when xsqmyj=1 then'特别困难'
                     when  xsqmyj=2 then '比较困难'
                     when  xsqmyj=3 then '一般困难'  end
                    ) as xsqmyj,count(1) as quantity
        from srs_knrd_apply  where  stu_year_id =  (select id from srs_stu_year where status=1)  and step >= 5 and xsqmyj !=4
        group by xsqmyj
    </select>
    <select id="getSrsAgeCount" resultType="java.util.Map">
        SELECT
            CASE
                WHEN FLOOR(YEAR(CURDATE()) - YEAR(birthday)) BETWEEN 0 AND 17 THEN '低于18岁'
                WHEN FLOOR(YEAR(CURDATE()) - YEAR(birthday)) BETWEEN 18 AND 20 THEN '18岁-20岁'
                ELSE '超过20岁'
                END AS age_group,
            COUNT(1) AS quantity,quantity/sum(quantity)*100 as percen
        FROM srs_student where birthday is not null
        GROUP BY age_group
    </select>
    <select id="countStuMZ" resultType="java.util.Map">
        select mz ,count(1) as quantity  from  cph_stu_extra_info  where mz is not null group by  mz order by quantity desc limit 4
    </select>
    <select id="getCountbyCity" resultType="java.util.Map">
        select * from (select (case when b.id_card=4501 then '南宁市'
                                    when b.id_card=4502 then '柳州市'
                                    when b.id_card=4503 then '桂林市'
                                    when b.id_card=4504 then '梧州市'
                                    when b.id_card=4505 then '北海市'
                                    when b.id_card=4506 then '防城港市'
                                    when b.id_card=4507 then '钦州市'
                                    when b.id_card=4508 then '贵港市'
                                    when b.id_card=4509 then '玉林市'
                                    when b.id_card=4510 then '百色市'
                                    when b.id_card=4511 then '贺州市'
                                    when b.id_card=4512 then '河池市'
                                    when b.id_card=4513 then '来宾市'
                                    when b.id_card=4514 then '崇左市'
                                end) as city,total_count,male_count,female_count
                             from (select  left(AES_DECRYPT(UNHEX(id_card),'zhxg'),4) as id_card,count(1) as total_count,
                                 sum(case when gender='男' then 1 else 0 end) as male_count,
                                 sum(case when gender='女' then 1 else 0 end) as female_count
                             from srs_student d left join srs_class e on d.class_id=e.class_id
                                 left join srs_grade f on f.grade_id=e.grade_id
                             where f.grade_status in (1,3)  GROUP BY left(AES_DECRYPT(UNHEX(id_card),'zhxg'),4)) b) c where c.city is not null;

    </select>
    <select id="getSchoolCountbyGrade" resultType="java.util.Map">
        select c.grade_code,count(1) as number from srs_student a
         left join srs_class b on a.class_id=b.class_id
        left join srs_grade c on c.grade_id=b.grade_id
        where c.grade_status in(1,3) and gender is not null
        group by c.grade_code order by grade_code desc
    </select>
    <select id="getSchoolTotalCount" resultType="java.util.Map">
        SELECT
            COUNT(1) AS TotalCount,
            SUM(CASE WHEN gender = '男' THEN 1 ELSE 0 END) AS NaCount,
            SUM(CASE WHEN gender = '女' THEN 1 ELSE 0 END) AS NvCount,
            CONCAT(ROUND(SUM(CASE WHEN gender = '男' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2), '%') AS NaZB,
            CONCAT(ROUND(SUM(CASE WHEN gender = '女' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2), '%') AS NvZB
        FROM srs_student d left join srs_class e on d.class_id=e.class_id
            left join srs_grade f on f.grade_id=e.grade_id where f.grade_status in (1,3) and gender is not null
    </select>
    <select id="getNotSchoolTotalCount" resultType="java.util.Map">
        select sum(total_count) as number from (select (case when b.id_card=4501 then '南宁市'
                                                             when b.id_card=4502 then '柳州市'
                                                             when b.id_card=4503 then '桂林市'
                                                             when b.id_card=4504 then '梧州市'
                                                             when b.id_card=4505 then '北海市'
                                                             when b.id_card=4506 then '防城港市'
                                                             when b.id_card=4507 then '钦州市'
                                                             when b.id_card=4508 then '贵港市'
                                                             when b.id_card=4509 then '玉林市'
                                                             when b.id_card=4510 then '百色市'
                                                             when b.id_card=4511 then '贺州市'
                                                             when b.id_card=4512 then '河池市'
                                                             when b.id_card=4513 then '来宾市'
                                                             when b.id_card=4514 then '崇左市'
            end) as city,total_count,male_count,female_count
            from (select  left(AES_DECRYPT(UNHEX(id_card),'zhxg'),4) as id_card,count(1) as total_count,
            sum(case when gender='男' then 1 else 0 end) as male_count,
            sum(case when gender='女' then 1 else 0 end) as female_count
            from srs_student d left join srs_class e on d.class_id=e.class_id
            left join srs_grade f on f.grade_id=e.grade_id
            where f.grade_status in (1,3)  GROUP BY left(AES_DECRYPT(UNHEX(id_card),'zhxg'),4)) b) c where c.city is null
    </select>
    <select id="getMajorsCount" resultType="java.util.Map">
        select d.dept_name as deptName,
               count(case when year(CURDATE())-f.grade_code=1 then '大一' end) as firstCount,
               count(case when year(CURDATE())-f.grade_code=2 then '大二' end) as thirdCount,
               count(case when year(CURDATE())-f.grade_code=3 then '大三' end) as secondCount from srs_student s
               left JOIN srs_class c ON s.class_id = c.class_id
              left JOIN srs_majors m ON c.major_id = m.major_id
              left JOIN sys_dept d ON m.college_id = d.dept_id
              left join cph_teacher as e on c.teacher_id = e.teacher_id
              left join srs_grade as f on f.grade_id=c.grade_id
        where f.grade_status in (1,3) group by d.dept_name
        order by d.dept_name
    </select>
    <select id="getFiveSchoolTotalCount"  resultType="java.util.Map">
        select f.grade_code as gradeCode,count(1) as number from  srs_student s
        left join srs_class c on s.class_id=c.class_id
        left join srs_majors m on c.major_id=m.major_id
        left join sys_dept d on m.college_id=d.dept_id
        left join cph_teacher as e on c.teacher_id=e.teacher_id
        left join srs_grade as f on f.grade_id=c.grade_id
        where f.grade_code>=year(date_sub(curdate(),interval 5 year))
        group by f.grade_code order by f.grade_code
    </select>
    <select id="statusList" resultType="com.srs.system.domain.vo.StuStatus">
        select dict_label, dict_value from sys_dict_data where dict_type='srs_stu_status'
    </select>
</mapper>
