<?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.persagy.dao.ReservationMapper">
    <select id="reservationCount" resultType="int">
        SELECT count(*) FROM reservation a WHERE date>DATE_SUB(curdate(),INTERVAL 1 DAY) AND a.mark='待审核'
    </select>

    <select id="reserveCount" resultType="int">
        SELECT count(*) FROM reservation a WHERE date>DATE_SUB(curdate(),INTERVAL 1 DAY) AND mark!='取消申请'
    </select>

    <select id="findByPaging" parameterType="com.persagy.domain.PagingVO" resultType="com.persagy.domain.ReservationVo">
        SELECT a.id,b.`name` ,a.date,a.begintime,a.endtime,a.`user`,a.mobile,a.mark,a.enterUsers,a.department,a.theme,a.alarmminute FROM reservation a
        LEFT JOIN room b on a.room_id=b.id
        WHERE date>DATE_SUB(curdate(),INTERVAL 1 DAY) AND a.mark='待审核' ORDER BY id
        limit #{toPageNo}, #{pageSize}
    </select>

    <select id="findAllByPaging" parameterType="com.persagy.domain.PagingVO" resultType="com.persagy.domain.ReservationVo">
        SELECT a.id,b.`name` ,a.date,a.begintime,a.endtime,a.`user`,a.mobile,a.mark,a.enterUsers,a.department,a.theme,a.alarmminute FROM reservation a
        LEFT JOIN room b on a.room_id=b.id
        WHERE date>DATE_SUB(curdate(),INTERVAL 1 DAY) AND mark!='取消申请' ORDER BY date DESC
        limit #{toPageNo}, #{pageSize}
    </select>

    <select id="findByName" parameterType="string" resultType="com.persagy.domain.ReservationVo">
        SELECT a.id,b.`name` ,a.date,a.begintime,a.endtime,a.`user`,a.mobile,a.mark,a.enterUsers,a.department,a.theme,a.alarmminute FROM reservation a
        LEFT JOIN room b on a.room_id=b.id
        WHERE date>DATE_SUB(curdate(),INTERVAL 1 DAY) AND a.mark='待审核' AND a.user LIKE '%${value}%' ORDER BY id
    </select>

    <select id="findByRoomId" parameterType="int" resultType="com.persagy.domain.ReservationVo">
        SELECT a.id,b.`name` ,a.date,a.begintime,a.endtime,a.`user`,a.mobile,a.mark,a.enterUsers,a.department,a.theme,a.alarmminute FROM reservation a
        LEFT JOIN room b on a.room_id=b.id
        WHERE date>DATE_SUB(curdate(),INTERVAL 1 DAY) AND mark!='取消申请' AND a.room_id=#{id} ORDER BY id
    </select>

    <select id="findByReservationId" parameterType="int" resultType="com.persagy.domain.ReservationVo">
        SELECT a.id ,a.date,a.begintime,a.endtime,a.`user`,a.mobile,a.mark,a.enterUsers,a.department,a.theme,a.alarmminute FROM reservation a
        WHERE a.id=#{id} ORDER BY id
    </select>

    <select id="reservationPassCount" resultType="int">
        SELECT count(*) FROM reservation a WHERE date BETWEEN CURDATE()-30 AND CURDATE() AND a.mark='审核通过' ORDER BY date DESC
    </select>

    <select id="findRecord" parameterType="com.persagy.domain.PagingVO" resultType="com.persagy.domain.ReservationVo">
        SELECT a.id,b.`name` ,a.date,a.begintime,a.endtime,a.`user`,a.mobile,a.mark,a.enterUsers,a.department,a.theme,a.alarmminute FROM reservation a
        LEFT JOIN room b on a.room_id=b.id
        WHERE date BETWEEN CURDATE()-30 AND CURDATE() AND a.mark!='取消申请' ORDER BY id
        limit #{toPageNo}, #{pageSize}
    </select>

    <select id="queryByUser" parameterType="string" resultType="com.persagy.domain.ReservationVo">
        SELECT a.id,b.`name` ,a.date,a.begintime,a.endtime,a.`user`,a.mobile,a.mark,a.enterUsers,a.department,a.theme,a.alarmminute FROM reservation a
        LEFT JOIN room b on a.room_id=b.id
        WHERE date>DATE_SUB(curdate(),INTERVAL 1 DAY) AND a.user LIKE '%${value}%' ORDER BY date DESC
    </select>

    <update id="reviewReservation" parameterType="int">
        UPDATE reservation SET mark='审核通过' WHERE id=#{id}
    </update>

    <insert id="addReservation" parameterType="com.persagy.domain.ReservationCustom">
        INSERT INTO reservation(user,mobile,date,begintime,endtime,mark,room_id,enterUsers,department,theme,alarmminute)
        SELECT #{user},u.phone,#{date},#{beginTime},#{endTime},'待审核',r.id,#{enterUsers},#{department},#{theme},#{alarmminute}
        FROM room r
        LEFT JOIN user u on u.id=#{user}
        WHERE name=#{name}
    </insert>

    <select id="findByUser" parameterType="string" resultType="com.persagy.domain.ReservationCustom">
        SELECT a.id,b.name,a.user,a.date,a.begintime,a.endtime,a.mark,a.enterUsers,a.department,a.theme,a.alarmminute FROM reservation a
        LEFT JOIN room b ON a.room_id=b.id
        WHERE a.date > CURDATE()-15  AND a.mark='待审核' AND a.user=#{value} ORDER BY a.date DESC
    </select>

    <update id="cancelApplication" parameterType="int">
        UPDATE reservation SET mark='取消申请' where id=#{id}
    </update>
</mapper>