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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.srs.dormitory.domain.DmsCheckoutApplication">
        <id column="id" property="id" />
        <result column="stu_no" property="stuNo" />
        <result column="current_dorm_id" property="currentDormId" />
        <result column="current_bed_id" property="currentBedId" />
        <result column="reason" property="reason" />
        <result column="create_time" property="createTime" />
        <result column="status" property="status" />
        <result column="del_flag" property="delFlag" />
    </resultMap>
    <select id="getApplicationList" resultType="java.util.Map">
        SELECT ds.id,ds.reason,ds.create_time,ds.`status`,ds.update_time createTime,ds.update_by updateBy,s.stu_no stuNo,s.`name` stuName,s.gender,
        de.dept_name deptName,c.class_name className,g.grade_name gradeName,
        m.major_name majorName,bed.bed_code bedCode,d.room_no roomNo,floor.floor,
        building.`name` AS buildingName,ds.out_time outTime
        FROM `dms_checkout_application` ds
        LEFT JOIN srs_student s ON ds.stu_no = s.stu_no
        LEFT JOIN sys_dept de ON s.dept_id = de.dept_id
        LEFT JOIN srs_class c ON s.class_id = c.class_id
        LEFT JOIN srs_majors m ON s.major_id = m.major_id
        LEFT JOIN srs_grade g ON c.grade_id = g.grade_id
        LEFT JOIN dms_dormitory_bed bed ON bed.id = ds.current_bed_id
        LEFT JOIN dms_dormitory d ON d.id = ds.current_dorm_id
        LEFT JOIN dms_dormitory_floor floor ON floor.id = d.floor_id
        LEFT JOIN dms_dormitory_building building ON floor.building_id = building.id
        <where>
            <if test="buildingId != null">and building.id = #{buildingId}</if>
            <if test="floorId != null">and floor.id = #{floorId}</if>
            <if test="dormitoryId != null">and d.id = #{dormitoryId}</if>
            <if test="studentNo != null">and ds.stu_no = #{studentNo}</if>
        </where>
        order by ds.id desc
    </select>
    <select id="selectInfo" resultType="java.util.Map">
        SELECT apply.id,apply.stu_no stuNo,d.room_no roomNo,b.`name` buildingName,apply.out_time outTime,
               apply.reason,apply.create_time createTime,s.`name` stuName,'OUT' AS applyType,
               app.fdy_status fdyStatus,app.fdy_comment fdyComment,app.work_status workStatus,
               app.work_comment workComment,app.final_status finalStatus,app.final_comment statusComment
        FROM dms_checkout_application apply
                 LEFT JOIN dms_dormitory d ON d.id = apply.current_dorm_id
                 LEFT JOIN dms_dormitory_floor f ON f.id = d.floor_id
                 LEFT JOIN dms_dormitory_building b ON b.id = f.building_id
                 LEFT JOIN srs_student s ON s.stu_no = apply.stu_no
                 LEFT JOIN dms_approval_process app ON app.apply_type = 'OUT' AND app.apply_id = apply.id

        where apply.id = #{id}
    </select>
    <select id="getMyApply" resultType="java.util.Map">
        SELECT apply.id,apply.stu_no stuNo,cd.room_no cRoomNo,cb.`name` cBuilding,apply.out_time outTime,
        apply.reason,apply.create_time createTime,s.`name` stuName,apply.`status`,'OUT' AS applyType
        FROM `dms_checkout_application` apply
        LEFT JOIN srs_student s ON s.stu_no = apply.stu_no
        LEFT JOIN dms_dormitory cd ON apply.current_dorm_id = cd.id
        LEFT JOIN dms_dormitory_floor cf ON cf.id = cd.floor_id
        LEFT JOIN dms_dormitory_building cb ON cb.id = cf.building_id
        <where>
            <if test="status == 0">and apply.status = #{status}</if>
            <if test="status == 1">and apply.status != 0</if>
        </where>
        ORDER BY apply.id DESC
    </select>
    <select id="workApply" resultType="java.util.Map">
        SELECT apply.id,apply.stu_no stuNo,cd.room_no cRoomNo,cb.`name` cBuilding,
        apply.reason,apply.create_time createTime,s.`name` stuName,apply.`status`,'OUT' AS applyType
        FROM `dms_checkout_application` apply
        LEFT JOIN dms_approval_process app ON app.apply_type = 'OUT' AND app.apply_id = apply.id
        LEFT JOIN srs_student s ON s.stu_no = apply.stu_no
        LEFT JOIN dms_dormitory cd ON apply.current_dorm_id = cd.id
        LEFT JOIN dms_dormitory_floor cf ON cf.id = cd.floor_id
        LEFT JOIN dms_dormitory_building cb ON cb.id = cf.building_id
        <where>
            <if test="status == 0">and app.work_status = #{status}</if>
            <if test="status == 1">and app.work_status != 0</if>
            and apply.status != 0
        </where>
        ORDER BY apply.id DESC
    </select>
    <select id="finalApply" resultType="java.util.Map">
        SELECT apply.id,apply.stu_no stuNo,cd.room_no cRoomNo,cb.`name` cBuilding,
        apply.reason,apply.create_time createTime,s.`name` stuName,apply.`status`,'OUT' AS applyType
        FROM `dms_checkout_application` apply
        LEFT JOIN dms_approval_process app ON app.apply_type = 'OUT' AND app.apply_id = apply.id
        LEFT JOIN srs_student s ON s.stu_no = apply.stu_no
        LEFT JOIN dms_dormitory cd ON apply.current_dorm_id = cd.id
        LEFT JOIN dms_dormitory_floor cf ON cf.id = cd.floor_id
        LEFT JOIN dms_dormitory_building cb ON cb.id = cf.building_id
        <where>
            <if test="status == 0">and app.work_status = #{status}</if>
            <if test="status == 1">and app.work_status != 0</if>
            and apply.status != 1
            and apply.status != 0
        </where>
        ORDER BY apply.id DESC
    </select>
</mapper>
