php - 餐厅 SQL 预订，包含基本时间表可用性和异常(exception)情况

Question

我正在开发一个项目，用户可以在餐厅进行预订，但仅限于餐厅允许的日期。餐厅还可以设置特定时间有多少个座位。

因此，餐厅可以提供每周格式的时间表，但也可以提供异常(exception)情况(例如假期)。

我需要的是给定时间范围内可用范围和座位的完整表示。

例如，一家餐厅有一个周时间表，规定每个工作日都有 10 个座位可用，但也有几个预订，我需要这样的输出这样我就可以生成一个日历，用户可以在其中查看餐厅的空闲情况。

+---------------------+---------------------+-----------+
|        start        |         end         | available |
+---------------------+---------------------+-----------+
| 2013-02-14 08:00:00 | 2013-02-14 17:00:00 |        10 |
| 2013-02-15 08:00:00 | 2013-02-15 12:00:00 |         8 | <= 2 reservations
| 2013-02-15 12:00:00 | 2013-02-16 15:00:00 |         4 | <= 6 reservations
| 2013-02-15 15:00:00 | 2013-02-16 15:00:00 |         7 | etc...
| 2013-02-16 15:00:00 | 2013-02-16 17:00:00 |         4 |
| 2013-02-17 08:00:00 | 2013-02-17 17:00:00 |        10 |
| 2013-02-18 12:00:00 | 2013-02-18 18:00:00 |        10 |
+---------------------+---------------------+-----------+

Answer 1

我们需要 2 组数据:可用时间和一次可用的座位；以及现有预订列表。

CREATE TABLE IF NOT EXISTS `availability` (
`dayOpen` date NOT NULL,
`open` time NOT NULL,
`close` time NOT NULL,
`seats` tinyint unsigned NOT NULL,
PRIMARY KEY (`dayOpen`)
) ;

CREATE TABLE IF NOT EXISTS `reservations` (
`reservationsid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`dayOpen` date NOT NULL,
`start` time NOT NULL,
`seatsUsed` tinyint unsigned NOT NULL,
PRIMARY KEY (`reservationsid`)
) ;

我假设您的预订必须在整点开始，并持续 59 分钟。这意味着我们的结果集将是包含所用座位数的小时列表。然后拉出可用的日期并循环列出每小时以及有多少个可用座位。

<?php

  $pdoHandle = new PDO($dsn,$user,$password);

  // Get all the used seats. Add WHERE with date range to limit results
  $query = 'SELECT dayOpen, start, SUM(seatsUsed) as seatsBooked FROM reservations GROUP BY dayOpen, start';
  $results = $pdoHandle->query($query);
  $allSeatsUsed = array();
  while ( $row = $results->fetch(PDO::FETCH_OBJ) )
  {  $allSeatsUsed[ $row->dayOpen ][ $row->start ] = $row->seatsBooked;  }

  // GET all possibile seats. Add WHERE with date range to limit results
  $query = 'SELECT * FROM availability ORDER BY dayOpen';
  $results = $pdoHandle->query($query);
  $allSeatsPossible = $results->fetchall(PDO::FETCH_OBJ);

  // got through each day to limit results to only open seats
  $finalOpenList = array();
  foreach ( $allSeatsPossible as $dayOpen )
  {
    $finalOpenList[ $dayOpen->dayOpen ] = array();
    $open = new DateTime($dayOpen->open);
    $close = new DateTime($dayOpen-close);
    $interval = new DateInterval('P1H');
    $todaysSlots = new DatePeriod($open,$interval,$close);
    foreach ( $todaysSlots as $slot )
    {
      if ( $allSeatsUsed[ $dayOpen->dayOpen ][ $slot->format('H:i:s') ] < $dayOpen->seats )
      {  $finalOpenList[ $dayOpen->dayOpen ][ $slot->format('H:i:s') ] = 
            $dayOpen->seats - 
            $allSeatsUsed[ $dayOpen->dayOpen ][ $slot->format('H:i:s') ];  }
    }
  }

  // $finalOpenList contains a list of days, with each day 
  // holding a list of hours and the number of slots left per hour
  foreach ( $finalOpenList as $day => $hours )
  {
    $day = new DateTime($day);
    print '<p>'.$day->format('M j, Y').': ';
    if ( sizeof($hours) == 0 )
    {  print 'All seats booked.</p>';  }
    else
    {
      print '</p><ul>';
      foreach ( $hours as $hour=>$seatsLeft )
      {  print '<li>'.$hour.': '.$seatsLeft.' seats available.</li>';  }
      print '</ul>';
    }
  }

?>