<?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.DmsNewTaskMapper">
    
    <resultMap type="DmsNewTask" id="DmsNewTaskResult">
        <result property="id"    column="id"    />
        <result property="oldDormId"    column="old_dorm_id"    />
        <result property="newDormId"    column="new_dorm_id"    />
        <result property="stuNo"    column="stu_no"    />
        <result property="taskType"    column="task_type"    />
        <result property="taskWant"    column="task_want"    />
        <result property="taskRun"    column="task_run"    />
        <result property="taskStatus"    column="task_status"    />
        <result property="oldDorm"    column="old_dorm"    />
        <result property="newDorm"    column="new_dorm"    />
    </resultMap>

    <sql id="selectDmsNewTaskVo">
        select a.id, a.old_dorm_id, a.new_dorm_id, a.stu_no, a.task_type, a.task_want, a.task_run, a.task_status,
               concat(b.campus_name,' ',b.park_name,' ',b.building_name,' ',b.room_no) as old_dorm,
               concat(c.campus_name,' ',c.park_name,' ',c.building_name,' ',c.room_no) as new_dorm
        from dms_new_task as a
        left join view_dorm_info as b on a.old_dorm_id = b.id
        left join view_dorm_info as c on a.new_dorm_id = c.id
    </sql>

    <select id="selectDmsNewTaskList" parameterType="DmsNewTask" resultMap="DmsNewTaskResult">
        <include refid="selectDmsNewTaskVo"/>
        <where>  
            <if test="oldDormId != null "> and a.old_dorm_id = #{oldDormId}</if>
            <if test="newDormId != null "> and a.new_dorm_id = #{newDormId}</if>
            <if test="stuNo != null  and stuNo != ''"> and a.stu_no = #{stuNo}</if>
            <if test="taskType != null  and taskType != ''"> and a.task_type = #{taskType}</if>
            <if test="taskWant != null "> and a.task_want = #{taskWant}</if>
            <if test="taskRun != null "> and a.task_run = #{taskRun}</if>
            <if test="taskStatus != null  and taskStatus != ''"> and a.task_status = #{taskStatus}</if>
        </where>
    </select>


    <select id="listView" resultType="DmsNewTaskVo" parameterType="CphSearch">
        select a.id,a.task_run,a.task_want,a.task_status,a.task_type,a.old_dorm_id,a.new_dorm_id,
               CONCAT(a5.`name`,' ',a4.`name`,' ',a3.`name`,' ',a2.floor,'层',a1.room_no) as old_dorm,
               CONCAT(b5.`name`,' ',b4.`name`,' ',b3.`name`,' ',b2.floor,'层',b1.room_no) as new_dorm,
               a.stu_no,c.`name` as stu_name,d.class_name,e.major_name,f.dept_name
        from dms_new_task as a
                 left join dms_dormitory as a1 on a.old_dorm_id = a1.id
                 left join dms_dormitory_floor as a2 on a1.floor_id = a2.id
                 left join dms_dormitory_building as a3 on a2.building_id = a3.id
                 left join dms_dormitory_park as a4 on a3.park_id = a4.id
                 left join dms_campus as a5 on a4.campus_id = a5.id
                 left join dms_dormitory as b1 on a.new_dorm_id = b1.id
                 left join dms_dormitory_floor as b2 on b1.floor_id = b2.id
                 left join dms_dormitory_building as b3 on b2.building_id = b3.id
                 left join dms_dormitory_park as b4 on b3.park_id = b4.id
                 left join dms_campus as b5 on b4.campus_id = b5.id
                 left join srs_student as c on a.stu_no = c.stu_no
                 left join srs_class as d on c.class_id = d.class_id
                 left join srs_majors as e on d.major_id = e.major_id
                 left join sys_dept as f on e.college_id = f.dept_id
        <where>
            <if test="stuNo != null  and stuNo != ''"> and a.stu_no = #{stuNo}</if>
        </where>
        ORDER BY a.id desc
    </select>

    
    <select id="selectDmsNewTaskById" parameterType="Long" resultMap="DmsNewTaskResult">
        <include refid="selectDmsNewTaskVo"/>
        where a.id = #{id}
    </select>
        
    <insert id="insertDmsNewTask" parameterType="DmsNewTask" useGeneratedKeys="true" keyProperty="id">
        insert into dms_new_task
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="oldDormId != null">old_dorm_id,</if>
            <if test="newDormId != null">new_dorm_id,</if>
            <if test="stuNo != null">stu_no,</if>
            <if test="taskType != null">task_type,</if>
            <if test="taskWant != null">task_want,</if>
            <if test="taskRun != null">task_run,</if>
            <if test="taskStatus != null">task_status,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="oldDormId != null">#{oldDormId},</if>
            <if test="newDormId != null">#{newDormId},</if>
            <if test="stuNo != null">#{stuNo},</if>
            <if test="taskType != null">#{taskType},</if>
            <if test="taskWant != null">#{taskWant},</if>
            <if test="taskRun != null">#{taskRun},</if>
            <if test="taskStatus != null">#{taskStatus},</if>
         </trim>
    </insert>

    <update id="updateDmsNewTask" parameterType="DmsNewTask">
        update dms_new_task
        <trim prefix="SET" suffixOverrides=",">
            <if test="oldDormId != null">old_dorm_id = #{oldDormId},</if>
            <if test="newDormId != null">new_dorm_id = #{newDormId},</if>
            <if test="stuNo != null">stu_no = #{stuNo},</if>
            <if test="taskType != null">task_type = #{taskType},</if>
            <if test="taskWant != null">task_want = #{taskWant},</if>
            <if test="taskRun != null">task_run = #{taskRun},</if>
            <if test="taskStatus != null">task_status = #{taskStatus},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteDmsNewTaskById" parameterType="Long">
        delete from dms_new_task where id = #{id}
    </delete>

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