我真的陷入了这种境地.
我有这两个表：
> employee_working_schedule(存储员工在特定日期工作的开始和结束时间)
> employee_appointments
假设我们存储了那些行
employee_working_schedule：
start | end
10:00 | 18:00
employee_appointments：
start | end
10:10 | 11:00
11:20 | 12:00
14:30 | 15:20
在这种情况下,我想表明可用时间是：
10:00 | 10:10
11:00 | 11:20
12:00 | 14:30
15:20 | 18:00
有没有办法通过sql执行此操作？我试图用PHP实现,但到目前为止没有成功.
任何帮助将不胜感激.
这是一种在纯PHP中执行此操作的方法：
class TimeSpan {
function __construct($start,$end) {
$this->start = $start;
$this->end = $end;
}
function starttime() {
list($hour,$minute) = explode(":",$this->start);
return (int)$hour * 60 + (int)$minute;
}
function endtime() {
list($hour,$this->end);
return (int)$hour * 60 + (int)$minute;
}
}
function convert_to_time($minutes) {
$hour = (int) ($minutes / 60);
$minutes = $minutes % 60;
return str_pad($hour,2,'0',STR_PAD_LEFT) . ':' . str_pad($minutes,STR_PAD_LEFT);
}
function open_times($shift,$appointments) {
$alltimes = array_fill_keys(range($shift->starttime(),$shift->endtime()),1);
foreach ($appointments as $appt) {
$alltimes = array_diff_key($alltimes,array_fill_keys(range($appt->starttime() + 1,$appt->endtime() - 1),1));
}
$groups = array();
$active_group = 0;
$output = array();
$output_counter = 0;
$nums = array_keys($alltimes);
foreach( $nums as $k => $num ) {
if( $k !== 0 && $nums[$k] !== $nums[$k-1]+1 ) $active_group ++;
$groups[ $active_group ][] = $num;
}
foreach( $groups as $group ) {
$first = array_shift( array_values($group) );
$output[$output_counter][] = $first;
$last = array_pop( array_values($group) );
if( $first !== $last )
$output[$output_counter][] = $last;
$output_counter++;
}
foreach ($output as &$span) {
$span[0] = convert_to_time($span[0]);
$span[1] = convert_to_time($span[1]);
}
return $output;
}
$shift = new TimeSpan("10:00","18:00");
$appointments = array(
new TimeSpan("10:10","11:00"),new TimeSpan("11:20","12:00"),new TimeSpan("14:30","15:20"),);
print_r(open_times($shift,$appointments));
OUTPUT
Array
(
[0] => Array
(
[0] => 10:00
[1] => 10:10
)
[1] => Array
(
[0] => 11:00
[1] => 11:20
)
[2] => Array
(
[0] => 12:00
[1] => 14:30
)
[3] => Array
(
[0] => 15:20
[1] => 18:00
)
)
服务热线 19970861797
