php - 具有重叠时间的大量 PHP/MySQL 插入

Question

我正在为一个体育馆开发这个软件，该体育馆的时间表有重叠时间，如果类(class)结束时间和类(class)开始时间之间的时间超过 35 分钟，该时间就会被删除。

示例:我的工作时间为上午 8:00 至 9:00、上午 8:30 至 9:30、上午 10:10 至 11:10。任何超过 35 分钟的间隔都被视为不同的类次。

我目前只处理每位员工的薪酬报告。

问题是他们想在假期对每个人进行大规模签到，我的大脑有点疯狂，试图弄清楚如何对单个员工 ID 进行数组和循环，弄清楚重叠，放弃 >35 分钟并将该类次插入到 check_ins 表中。

此时，我已经隔离了给定假期的 worker ，并安排了他们的轮类，我有一个数组中的员工 ID 总数和一个数组中的时间表总数。

任何人都知道如何在不让我喝醉的情况下最容易地实现这一点？ (如果健身房要求你编写日程安排软件，那就跑吧!)

谢谢!

编辑:假期工资条件像该死的风一样多变，所以没有帮助。

我的单个员工代码:

    //init our arrays
    $timeStart = array();
    $timeEnd = array();
    $x = 0;
    $maxInterval = new DateTime('00:35');
    $classIDArray = array();

    do {
//Get our start times for Monday
$p = new DateTime($row_GetMonSchedule2['Start_time']);
$q = new DateTime($row_GetMonSchedule2['End_time']);
$timeStart[$x] = $p->format('H:i');
$timeEnd[$x] =  $q->format('H:i');
//Get Class IDs
$classIDArray[$x] = $row_GetMonSchedule2['Class_ID'];

$totalEndTimes = count($timeEnd);
$x++;

} while ($row_GetMonSchedule2 = mysql_fetch_assoc($GetMonSchedule2));

//echo $totalRows_GetMonSchedule2;

//Now let's check the durations and make our shifts

//echo "Class ID Array: "; print_r($classIDArray);

$x = 0;
$s = 1;

do {
///First Time Start 
if ($x == 0) {
$TimeDayStart = $timeStart[$x];
}

if ($x < $totalEndTimes) {
///Last Time End
$TimeDayEnd = $timeEnd[$x]; 
}


  $x++; $s++;
} while ($x < $totalEndTimes);

$x = 0;
$s = 1;

/////Get Durations

$interval = array();


do {
 if ($x == 0) {
  //The very first end time
  //First end time of the first time block
  $firstEndTime = new DateTime($timeEnd[$x]); 
  $startTime = new DateTime($timeStart[$s]);
  $timeDiff = $startTime->diff($firstEndTime);
  $interval[$x] = $timeDiff->format('%H:%i');
 } else {
  //We don't need the last record because there isn't anything to calculate it aginst, so increase $x by one
  if ($x + 1 > $totalEndTimes) {



  } else {
    $endTime = new DateTime($timeEnd[$x]);
    $startTime = new DateTime($timeStart[$s]);
    $timeDiff = $endTime->diff($startTime);
    $interval[$x] = $timeDiff->format('%H:%i');

  }

 }



  $x++; $s++;

} while ($x < $totalEndTimes);

//Let's try to break this up into shifts now...
$x =0;
$y =0;
$zz = 1;
$s = 1;
$timeBreak =0;
$durCount = count($interval) - 1;
$BreakStartTimes = array();
$BreakEndTimes = array();
$xx3 = 0;
$yy3 = 0;

//Setup table
do {


  if ($x == 0) {
//echo $timeStart[$x]." - ";
$BreakStartTimes[$yy3] = $timeStart[$x];
$yy3++;
  if (new DateTime($interval[$x]) > new DateTime('00:35')) {
  //echo $timeEnd[$x]." ";
  $BreakEndTimes[$xx3] = $timeEnd[$x];
  $xx3++;
  $timeBreak = 1;
  //echo "Time Break";
  }

  } 
  if (new DateTime($interval[$x]) > new DateTime('00:35') && $timeBreak != 1) {
  //echo $timeEnd[$x]." ";
  $BreakEndTimes[$xx3] = $timeEnd[$x];
  $xx3++;
  $timeBreak = 1;
  //echo "Time Break";
  }

  if ($timeBreak == 1 && $x + 1 < $totalEndTimes) {
  //echo $timeStart[$s]." - ";
    $BreakStartTimes[$yy3] = $timeStart[$s];
    $yy3++;
  $timeBreak = 0;
  }


  $x++; $s++;

} while ($x < $totalEndTimes);

if (empty($BreakEndTimes)) {
  //echo "empty";
  $ttt = count($timeEnd) - 1;
  //echo $ttt;
}

Answer 1

就像一些评论者在我之前所说的那样，嵌套一些循环应该可以解决问题。尝试用伪代码写下它并从那里填写详细信息。

例子:

//connect to database
$conn = new mysqli("host","user","pass","db");

// check for connection error
if ($mysqli->connect_error) 
{
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

//query for all employees working the holiday
// I might be way off with the query seeing as I don't know the complexity
$sql = "SELECT e.ID, count(s.Start_Time) FROM employee e, schedule s 
        WHERE count(s.Start_Time) >= 1 AND e.ID = s.Employee_ID
            AND s.Start_Time >= '2013-12-24 00:00:00.0000' 
            AND s.End_Time <= '2014-01-02 00:00:00.0000'
        GROUP BY e.ID;";

//get the result from database like you do already

//then do the loop
for each employee 
{
    execute the code you have for the current employee
}
do clean up or other tasks after the loop is done

现在这很容易开始扩展，希望能提供一个您可以以此为基础的想法。