<?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.dormitory.mapper.DmsStuDailyMapper">

    <resultMap type="DmsStuDaily" id="DmsStuDailyResult">
        <result property="id"    column="id"    />
        <result property="stuNo"    column="stu_no"    />
        <result property="dormitoryId"    column="dormitory_id"    />
        <result property="submitTime"    column="submit_time"    />
        <result property="photo"    column="photo"    />
    </resultMap>

    <sql id="selectDmsStuDailyVo">
        select id, stu_no, dormitory_id, submit_time, photo from dms_stu_daily
    </sql>

    <select id="selectDmsStuDailyList" parameterType="DmsStuDaily" resultMap="DmsStuDailyResult">
        <include refid="selectDmsStuDailyVo"/>
        <where>
            <if test="stuNo != null  and stuNo != ''"> and stu_no = #{stuNo}</if>
            <if test="dormitoryId != null "> and dormitory_id = #{dormitoryId}</if>
            <if test="submitTime != null "> and submit_time = #{submitTime}</if>
            <if test="photo != null  and photo != ''"> and photo = #{photo}</if>
        </where>
    </select>

    <select id="selectDmsStuDailyById" parameterType="Long" resultMap="DmsStuDailyResult">
        <include refid="selectDmsStuDailyVo"/>
        where id = #{id}
    </select>

    <insert id="insertDmsStuDaily" parameterType="DmsStuDaily" useGeneratedKeys="true" keyProperty="id">
        insert into dms_stu_daily
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="stuNo != null">stu_no,</if>
            <if test="dormitoryId != null">dormitory_id,</if>
            <if test="submitTime != null">submit_time,</if>
            <if test="photo != null">photo,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="stuNo != null">#{stuNo},</if>
            <if test="dormitoryId != null">#{dormitoryId},</if>
            <if test="submitTime != null">#{submitTime},</if>
            <if test="photo != null">#{photo},</if>
        </trim>
    </insert>

    <update id="updateDmsStuDaily" parameterType="DmsStuDaily">
        update dms_stu_daily
        <trim prefix="SET" suffixOverrides=",">
            <if test="stuNo != null">stu_no = #{stuNo},</if>
            <if test="dormitoryId != null">dormitory_id = #{dormitoryId},</if>
            <if test="submitTime != null">submit_time = #{submitTime},</if>
            <if test="photo != null">photo = #{photo},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteDmsStuDailyById" parameterType="Long">
        delete from dms_stu_daily where id = #{id}
    </delete>

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

    <select id="getStuDorm" resultType="Map" parameterType="String">
        select a.stu_no,b.park_name,b.building_name,b.room_no
        from srs_dormitory_student as a
                 left join view_dorm_info as b on a.dormitory_id = b.id
        where a.stu_no = #{stuNo}
    </select>

    <select id="listToday" parameterType="DmsStuDaily" resultType="DmsStuDaily">
        select a.*,b.dept_name,b.major_name,b.class_name,b.stu_name,c.park_name,c.building_name,c.room_no
        from dms_stu_daily as a
        left join view_stu_info as b on a.stu_no = b.stu_no
        left join view_dorm_info as c on a.dormitory_id = c.id
        <where>
            <if test="stuNo != null  and stuNo != ''"> and a.stu_no = #{stuNo}</if>
            <if test="dormitoryId != null "> and a.dormitory_id = #{dormitoryId}</if>
            <if test="submitTime != null ">and a.submit_time like concat( date_format(#{submitTime}, '%Y-%m-%d'),'%')</if>
            <if test="campusId != null "> and c.campus_id = #{campusId}</if>
            <if test="parkId != null "> and c.park_id = #{parkId}</if>
            <if test="buildingId != null "> and c.building_id = #{buildingId}</if>
            <if test="floorId != null "> and c.floor_id = #{floorId}</if>
            <if test="deptId != null "> and b.dept_id = #{deptId}</if>
            <if test="majorId != null "> and b.major_id = #{majorId}</if>
            <if test="classId != null "> and b.class_id = #{classId}</if>
            <if test="gradeId != null "> and b.grade_id = #{gradeId}</if>
            <if test="stuName != null  and stuName != ''"> and b.stu_name like concat('%',#{stuName},'%')</if>
            <if test="teacherName != null  and teacherName != ''"> and b.teacher_name like concat('%',#{teacherName},'%')</if>
            <if test="idCard != null and idCard != ''"> and AES_DECRYPT(UNHEX(b.id_card),'zhxg') = #{idCard}</if>
        </where>
        order by a.id desc
    </select>

    <select id="listStudent" resultType="Map">
        select * from view_stu_info where  dept_name is not null and stu_no in
        <foreach item='item' index='index' collection='list' open='(' separator=',' close=')'>
            #{item}
        </foreach>
    </select>

    <select id="listAllXw" resultType="Map">
        select a.*,c.user_name,d.dept_name
        from sys_user_role as a
                 left join sys_role as b on a.role_id = b.role_id
                 left join sys_user as c on a.user_id = c.user_id
                 left join sys_dept as d on c.dept_id = d.dept_id
        where b.role_key = "stumanger"
    </select>
    <select id="checkedIn" resultType="java.lang.Integer">
        select count(1) as yiqianDaoCount  from dms_stu_daily as a
        left join view_stu_info as b on a.stu_no = b.stu_no
        left join view_dorm_info as c on a.dormitory_id = c.id
    </select>
    <select id="notCheckedIn" resultType="java.lang.Integer">
        select count(1) as weiqiandaoCount
        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
        and  d.stu_no not in (select stu_no from dms_stu_daily)
    </select>
    <select id="leavScholl" resultType="java.lang.Integer">
        select count(1) as leavSchoolCount from rt_stu_leave_application where status=1
    </select>
    <select id="goLesson" resultType="java.lang.Integer">
        select count(1)  as lessonCount from srs_student where status in('05','06');
    </select>
    <select id="getDeptNames" resultType="java.util.Map">
        select b.dept_name as deptName,count(1) as NCount
        from dms_stu_daily as a
                 left join view_stu_info as b on a.stu_no = b.stu_no
                 left join view_dorm_info as c on a.dormitory_id = c.id
        group by b.dept_name
        ORDER BY b.dept_name
    </select>

    <select id="countDeptLeave" resultType="map">
        select b.dept_name as `name` , count(1) as `value`
        from rt_stu_leave_application as a
                 left join view_stu_info as b on a.stu_no = b.stu_no
        where a.`status` = "1" and b.dept_name is not null
        group by b.dept_name
    </select>

    <select id="countDeptShixi" resultType="map">
        select a.dept_name as `name`, count(1) as `value`
        from view_stu_info as a
        where a.`status` in ("05","06","04") and a.dept_name is not null
        GROUP BY a.dept_name
    </select>

    <select id="listLeaveLog" resultType="String">
        select t1.* from
            (
                select concat(b.stu_name ,' ',DATE_FORMAT(a.submit_time, '%Y-%m-%d'),' 完成宿舍打卡') as `log`
                from dms_stu_daily as a
                         left join view_stu_info as b on a.stu_no =  b.stu_no
                order by a.id desc
                    limit 5
            ) as t1
        union
        select t2.*
        FROM
            (
                SELECT
                    CONCAT(
                            c.stu_name,
                            ' 于 ',
                            DATE_FORMAT(a.create_time, '%Y-%m-%d %H:%i:%s'),
                            ' ',
                            CASE a.operation_type
                                WHEN 0 THEN '请假申请'
                                WHEN 1 THEN '辅导员审批'
                                WHEN 2 THEN '学生销假申请'
                                WHEN 3 THEN '辅导员核实销假'
                                ELSE '结束流程'
                                END,
                            ' ',
                        -- 操作结果描述
                            CASE a.operation_type
                                WHEN 0 THEN -- 请假申请
                                    CASE a.operation_result
                                        WHEN 0 THEN '成功'
                                        WHEN 1 THEN '失败'
                                        ELSE ''
                                        END
                                WHEN 1 THEN -- 辅导员审批
                                    CASE a.operation_result
                                        WHEN 0 THEN '同意'
                                        WHEN 1 THEN '不同意'
                                        WHEN 2 THEN '驳回'
                                        ELSE ''
                                        END
                                WHEN 2 THEN -- 学生销假申请
                                    CASE a.operation_result
                                        WHEN 0 THEN '成功'
                                        WHEN 1 THEN '失败'
                                        ELSE ''
                                        END
                                WHEN 3 THEN -- 辅导员核实销假
                                    CASE a.operation_result
                                        WHEN 0 THEN '同意'
                                        WHEN 1 THEN '不同意'
                                        ELSE ''
                                        END
                                ELSE ''
                                END
                    ) AS `log`
                FROM rt_stu_leave_operation_records AS a
                         LEFT JOIN rt_stu_leave_application AS b ON a.leave_application_id = b.leave_application_id
                         LEFT JOIN view_stu_info AS c ON b.stu_no = c.stu_no
                ORDER BY a.operation_records_id DESC
                    LIMIT 5
            ) as t2

        ;
    </select>

    <select id="countDeptCard" resultType="map">
        select a.dept_name,count(1) as `sum`,
               (
                   select count(1)
                   from dms_stu_daily as t1
                            left join view_stu_info as b on t1.stu_no = b.stu_no
                   where b.dept_name = a.dept_name and DATE(submit_time) = CURDATE()
                ) as card
        from view_stu_info as a
        where a.status in ("05","06","04","01") and a.dept_name is not null
        group by a.dept_name
    </select>


</mapper>
