我正在做一个项目,用户可以在餐厅预订,但只能在餐厅允许的日期预订。餐厅还可以设置某个时间的座位数量。
因此,餐厅可以提供每周格式化的时间表,但也可以提供例外情况(如假期)。
我需要的是在给定的时间范围内提供可用范围和座位的完整表示。
例如,一家餐厅有一周的时间表,上面写着每个工作日他们都有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 |
+---------------------+---------------------+-----------+
我们需要两组数据:可用时间和一次可用座位;以及现有保留的清单。
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>';
}
}
?>