php - SQL语句/Codeigniter预约约会

我正在使用带有每日日历的预订软件。我有四个表，如下所示：

Services:
ID | EmployeeID| ServiceID| RoomID| StartDate        | EndDate
1  |     1     |     1    |   2   | 2017-06-18 01:00 |2017-06-18 02:00
2  |     2     |     1    |   1   | 2017-06-18 03:00 |2017-06-18 04:00

Employees:
EmployeeID | Name
    1      |  A
    2      |  B
    3      |  C

Rooms:
RoomID | Name
  1    |  X
  2    |  Y
  3    |  Z

EmpBreaks:
ID | EmployeeID | BreakStart       | BreakEnd
1  |     1      | 2017-06-18 02:00 | 2017-06-18 03:00

在“服务”表存储预订的约会详细信息的位置，“雇员”表包含所有“雇员”的列表，“房间”表包含所有“房间”的列表，而EmpBreaks则存储“雇员”不可用的所有时间。

我想执行查询以在指定的开始和结束日期搜索所有可用的雇员和房间。

例如：

开始日期'2017-06-18 01:30'
结束日期'2017-06-18 02:30'

结果：

员工编号{3}

RoomID {1,3}

这是更新的工作代码。

function check_available_rooms_therapists() {
        $this->data['message'] = array();
        $this->data['success'] = FALSE;

    $post = $this->input->post();

    $EmployeeFields = array(
        'Employees.EmployeeID',
        'Employees.Name'
    );

    $sDate = $post['StartDate'];
    $eDate = $post['EndDate'];

    $whereEmp = "Employees.EmployeeID NOT IN (SELECT Services.EmployeeID FROM Services where
        (( '" . $sDate . "' BETWEEN Services.StartDate AND Services.EndDate) or
        ( '" . $eDate . "' BETWEEN Services.StartDate AND Services.EndDate) or
        (Services.StartDate BETWEEN '". $sDate ."' AND '" . $eDate ."') or
        (Services.EndDate BETWEEN '". $sDate ."' AND '" . $eDate ."')) union
        (SELECT EMPBREAKS.EMPID FROM EMPBREAKS WHERE(
        ('" . $sDate . "' BETWEEN EMPBREAKS.StartB AND EMPBREAKS.EndB) or
        ('" . $eDate . "' BETWEEN EMPBREAKS.StartB AND EMPBREAKS.EndB) or
        (EMPBREAKS.StartB BETWEEN '" . $eDate . "' AND '" . $sDate ."') or
        (EMPBREAKS.EndB BETWEEN '" . $eDate . "' AND '" . $sDate ."'))))";

    $this->db->select($EmployeeFields);
    $listing = $this->db->get_where('Employees', $whereEmp, 50 , 0);
    $result = $listing -> result();

    $avEmpName = array();
    $avEmpID = array();

    foreach ($result as $row) {
        $avEmpID[] = $row->EmployeeID;
        $avEmpName[] = $row->Name;
    }

    $RoomFields = array(
        'Rooms.RoomID',
        'Rooms.Name'
    );

     $whereRoom = "Rooms.RoomID not in (SELECT Services.RoomID FROM Services WHERE
        ((Services.startdate BETWEEN '" . $sDate . "' AND '" . $eDate. "') OR
            (Services.enddate BETWEEN '" . $sDate . "' AND '" . $eDate. "') OR
            ('" .$sDate . "' BETWEEN  Services.startdate AND Services.enddate) OR
            ('" .$eDate . "' BETWEEN  Services.startdate AND Services.enddate)))";

    $this->db->select($RoomFields);
    // $this->db->from('Rooms');
    $Rooms = $this->db->get_where('Rooms', $whereRoom, 50 ,0);
    $Rresult = $Rooms -> result();

    $avRoomName = array();
    $avRoomID = array();

    foreach ($Rresult as $r) {
        $avRoomID[] = $r->RoomID;
        $avRoomName[] = $r->Name;
    }

        $this->data['EmployeeID'] =  $avEmpID;
        $this->data['RoomID'] = $avRoomID;
        $this->data['RoomName'] = $avRoomName;
        $this->data['Name'] = $avEmpName;

    $this->data['success'] = TRUE;
    echo json_encode($this->data);
    die;
}

最佳答案

也许您可以轻松地将问题分为两个不同的查询，

首先，您可以编写一个与您要排除的所有内容都匹配的查询，
我们称其为错误结果，
之后，您可以从Employee and Rooms表中获得所有没有错误结果的雇员和房间

select * from Employees
where Employees.EmployeeID not in (
SELECT a.EmployeeID
    FROM Appointments a
    where  ((a.startdate BETWEEN yourstartdate AND yourenddate ) or  (a.enddate BETWEEN yourstartdate AND yourenddate)));

select * from Rooms
where Rooms.RoomID not in (
    SELECT a.RoomID
    FROM Appointments a
    where  ((a.startdate BETWEEN yourstartdate AND yourenddate ) or  (a.enddate BETWEEN yourstartdate AND yourenddate)));

关于php - SQL语句/Codeigniter预约约会，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44619714/