我有一个名为bookings的表,其中包含以下列和数据:
ID | Client | Start At | End At
1 103 2020-12-17 14:15 2020-12-17 14:30
2 202 2020-12-17 14:35 2020-12-17 15:00
3 204 2020-12-17 16:00 2020-12-17 16:20
4 158 2020-12-17 17:00 2020-12-17 18:00
5 157 2020-12-19 10:00 2020-12-19 10:15
6 142 2020-12-21 10:00 2020-12-21 10:15
预订的创建具有以下规则:
到目前为止,我的代码仅检查特定日期是否被占用。比方说,我要检查,如果日期2020-12-17 14:20
以2020-12-17 14:30
可用。
SELECT *
FROM bookings
WHERE
(
(start_at <= '2020-12-17 14:20' AND end_at >= '2020-12-17 14:20') OR
(start_at <= '2020-12-17 14:30' AND end_at >= '2020-12-17 14:30') OR
(start_at >= '2020-12-17 14:20' AND end_at <= '2020-12-17 14:30')
)
这样效果很好,并且会返回该日期不可用(如中所示ID 1
)。
因此,让我们看下面的例子:
2020-12-17 14:40
为2020-12-17 15:00
[ 20分钟] [占用] [建议:2020-12-17 15:00
]2020-12-17 18:00
为2020-12-17 19:15
[ 1h15分钟] [占用] [建议:2020-12-18 10:00
]2020-12-21 10:10
为2020-12-21 10:20
[ 10分钟] [占用] [建议:2020-12-21 10:15
]2020-12-21 12:00
为2020-12-21 12:20
[ 20分钟] [免费]我可以使用普通的SQL来实现此功能,还是需要在其后添加一些PHP逻辑?
解决了。
获取建议日期的算法基本上是一个无限循环,直到我们在预订中找到可用空间为止。
就我而言,有一些规则适用:
考虑以下规则:
$suggestedDate = '';
$startAt = '2020-12-18 10:00';
$endAt = '2020-12-18 10:45';
do
{
// The SQL code is in the original post
$isAvailable = SQL('...');
if ($isAvailable)
$suggestedDate = $startAt;
else
{
$_startAt = new DateTime($startAt);
$_endAt = new DateTime($endAt);
$diff = $_endAt->diff($startAt);
// Calculates the time difference in minutes (converts the hours in minutes as well, so 1h15 = 75min)
$minutes = $diff->h * 60;
$minutes += $diff->i;
/**
* Input:
* - Start At: 2020-12-18 10:00 End At: 2020-12-18 10:45 Minutes: 45
*
* Output:
* - Start At: 2020-12-18 10:45 End At: 2020-12-18 11:30 Available? No
* - Start At: 2020-12-18 11:30 End At: 2020-12-18 12:15 Available? Yes!
*/
$startAt = date('Y-m-d H:i', strtotime("+{$minutes} minutes", strtotime($startAt)));
$endAt = date('Y-m-d H:i', strtotime("+{$minutes} minutes", strtotime($endAt)));
$dayOfWeek = date('l', strtotime($startAt));
$isMaxTime = false;
// Grabs for the days of the week the max time
if (in_array($dayOfWeek, ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']))
$maxTime = date('Y-m-d 19:00', strtotime($startAt));
else if ($dayOfWeek == 'Saturday')
$maxTime = date('Y-m-d 20:00', strtotime($startAt));
$_endAt = new \DateTime($endAt);
$_maxTime = new \DateTime($maxTime);
// Will check if the `endAt` is bigger than the maximum allowed time
if ($_endAt > $_maxTime)
$isMaxTime = true;
/**
* Here we just check if the day is Sunday or if the maximum time has been reached. In both situations
* all we have to do is increment one day & start all over again, but starting at 10am!
*/
if ($dayOfWeek == 'Sunday' || $isMaxTime)
{
$startAt = date('Y-m-d 10:00', strtotime("+1 day", strtotime($startAt)));
$endAt = date('Y-m-d 10:00', strtotime("+1 day", strtotime($endAt)));
$endAt = date('Y-m-d H:i', strtotime("+{$minutes} minutes", strtotime($endAt)));
}
}
} while (empty($suggestedDate));
echo $suggestedDate;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句