csapi/application/index/model/hotel/RoomModel.php

<?php
namespace app\index\model\hotel;
use app\index\model\BaseModel;

class RoomModel extends BaseModel
{
    protected $table='room';
    protected $connection = 'db_hotel';

    /**
     * 获取当前日期可预约的房间列表
     * @param $hotel_id
     * @param $date
     * @return false|\PDOStatement|string|\think\Collection
     */
    public function getReservedRoomList($hotel_id,$date){
        $roomList = $this->field('room.room_id,room.room_name,room_imgs,room_price,room.total_number,COALESCE(sum(total_room_num),0) as sum_num')
            ->join('order',"(room.hotel_id = order.hotel_id and room.room_id = order.room_id and (order.status =1 or order.status =6) and start_date <= '{$date}' and end_date > '{$date}')",'LEFT')
            ->where([
                'room.hotel_id'=>$hotel_id,
                'room.status'=>0
            ])->group('room.room_id')->select();

        return $roomList;
    }


    /**
     * 获取当前房间的信息
     * @param $hotel_id
     * @param $room_id
     * @return mixed
     */
    public function getRoomInfo($hotel_id,$room_id){
        return $this->where(['hotel_id'=>$hotel_id,'room_id'=>$room_id])->find();
    }
}