php - 检查多个日期范围内更广泛的差距

Question

我有一个在线成员(member)系统，本地执法部门要求我们为所有成员(member)提供 5 年的地址历史记录。在这 5 年内，允许有重叠，但不得有任何差距。成员输入他们的地址，存储在 mysql 数据库中，其中包含地址和每个地址的起始日期和截止日期。数据由成员(member)输入为月份和年份。这存储为“开始”日期的第一个月和“结束”日期的最后一个月。

我有我的 $array，它是从数据库的 mysqli 查询中编译的，用于为特定成员提取他们提供的地址的所有日期范围。这个数组很好地填充了我要制作的循环。

$array[] = array("from"=>$row['date_from'],"to"=>$row['date_to']);

我已经尝试将这些范围中的每一个转换为天数长度并将它们全部相加，但地址重叠的地方(允许和必要)它可能超过 5 年，即使地址历史记录中存在间隙也是如此。

我只需要 $complete 为 TRUE 或 FALSE，我不需要间隔天数。

愿意将其作为 mysql 查询或 php。

很抱歉没有提供半工作代码 - 我唯一的想法是循环 1825 天(5 年)以查看是否每一天都被代表。

Answer 1

此递归函数将遍历您的范围数组，组合重叠的范围，直到它们尽可能多地组合在一起。如果结果超过一个范围，则存在差距。我还使用了一个辅助函数来更清晰/更容易地查找重叠，并且我假设您的日期看起来像 'Y-m-d'。

<?php

// First, here's how to use it. If we can't combine all the ranges, and the 
// final combined range doesn't reach back 5 years or to the present,
// we have a gap.
$mergedRanges = combineRanges($myArray);
if (count($mergedRanges) > 1
    || $mergedRanges[0]['from'] > date('Y-m-d', time() - 157680000) // 5 years ago
    || $mergedRanges[0]['to'] < date('Y-m-d')) // present
    echo 'Gaps found';

/**
 * Recursive function to combine ranges.
 *
 * @param array $ranges
 * @return array Array of combined ranges (has only 1 element if no gaps)
 */
function combineRanges(array $ranges)
{
    $mergedRanges = array();
    $usedKeys = array();

    // Nested foreach compares each unique pair of ranges for overlap.
    // If the a range has already been accounted for, it can be skipped.
    foreach ($ranges as $k1 => $range1) {
        if (!in_array($k1, $usedKeys)) {
            foreach ($ranges as $k2 => $range2) {
                if (!in_array($k1, $usedKeys) && $k1 > $k2) {

                    // If ranges overlap, combine them and make a note that
                    // they've already been included
                    if (rangesOverlap($range1, $range2)) {
                        $newRange = array(
                            'from' => min($range1['from'], $range2['from']),
                            'to' => max($range1['to'], $range2['to'])
                        );
                        // It's possible the resulting range could already
                        // be accounted for by a different combo of ranges,
                        // so check first
                        if (!in_array($newRange, $mergedRanges))
                            $mergedRanges[] = $newRange;
                        $usedKeys[] = $k1;
                        $usedKeys[] = $k2;

                    // Otherwise, add the 2nd range to $mergedRanges
                    } elseif (!in_array($k2, $usedKeys)) {
                        $mergedRanges[] = $range2;
                        $usedKeys[] = $k2;
                    }

                    // If $range1 didn't have any overlaps, add it here
                    if (!in_array($k1, $usedKeys)) {
                        $mergedRanges[] = $range1;
                        $usedKeys[] = $k1;
                    }
                }
            }
        }
    }

    // If $ranges and $mergedRanges have the same # of elements,
    // or if $ranges only had 1 element to begin with,
    // that means we couldn't merge any more. Otherwise, recurse!
    if (count($ranges) == 1)
        return $ranges;
    return count($mergedRanges) == 1 || (count($ranges) == count($mergedRanges))
        ? $mergedRanges
        : combineRanges($mergedRanges);
}

/**
 * Helper function to see if 2 ranges overlap.
 *
 * @param array $range1
 * @param array $range2
 * @return boolean
 */
function rangesOverlap(array $range1, array $range2)
{
    // Find the day before each range in order to combine ranges
    // that don't overlap but are right next to each other.
    $overlap = false;
    $range1Before = date('Y-m-d', strtotime('-1 day', strtotime($range1['from'])));
    $range2Before = date('Y-m-d', strtotime('-1 day', strtotime($range2['from'])));

    // Account for when $range1 is first or when $range 2 is first
    if ($range1['from'] <= $range2['from'] && $range1['to'] >= $range2Before
        || $range2['from'] <= $range1['from'] && $range2['to'] >= $range1Before)
       $overlap = true;

    return $overlap;
}