CODEWITHSUNDEEP
php
dean2020
dean2020

Reputation: 665

Pad missing dates in array with value previous day

I have an array generated from a database showing continuous date ranges for products in stock.

The problem I'm having is that when there are missing dates the date range gets broken in 2 parts as shown in a segment of the array below where there are no records for dec 31.

I can tell it is a missing date because the elements [0],[1],[2] in the row before the missing date are equal to the elements [0],[1],[2] in the row after the missing date. Element[3] is the number of days in the [4], [5] date range.

[20] => Array ( [0] => 745637 [1] => 24759 [2] => 6.00 [3] => 23 [4] => 2016-12-08 [5] => 2016-12-30 ) 
[21] => Array ( [0] => 745637 [1] => 24759 [2] => 6.00 [3] => 2 [4] => 2017-01-01 [5] => 2017-01-02 ) 
[22] => Array ( [0] => 745637 [1] => 26400 [2] => 5.70 [3] => 23 [4] => 2016-12-08 [5] => 2016-12-30 ) 
[23] => Array ( [0] => 745637 [1] => 26400 [2] => 5.70 [3] => 2 [4] => 2017-01-01 [5] => 2017-01-02 ) 
[24] => Array ( [0] => 745637 [1] => 29882 [2] => 7.00 [3] => 23 [4] => 2016-12-08 [5] => 2016-12-30 ) 
[25] => Array ( [0] => 745637 [1] => 29882 [2] => 7.00 [3] => 2 [4] => 2017-01-01 [5] => 2017-01-02 )

So what I would like to do is merge these rows IF there is only a 1 day gap AND elements [0],[1],[2] in the row before the missing date are equal to the elements [0],[1],[2] after the missing date.

[20] => Array ( [0] => 745637 [1] => 24759 [2] => 6.00 [3] => 23 [4] => 2016-12-08 [5] => 2016-12-30 ) 
[21] => Array ( [0] => 745637 [1] => 24759 [2] => 6.00 [3] => 2 [4] => 2017-01-01 [5] => 2017-01-02 )

would become:

[20] => Array ( [0] => 745637 [1] => 24759 [2] => 6.00 [3] => 26 [4] => 2016-12-08 [5] => 2017-01-02 )

UPDATE:

This is what I came up with, and it actually works but I'm wondering if there is a cleaner way of doing this

foreach ($data as $rows) {

 $current = $rows[0].$rows[1].$rows[2];
 $cur_days = $rows[3];
 $cur_begin = $rows[4];
 $cur_end = $rows[5];

 // check if we have a 1-day gap in the date range    
 $comp_date = date_add(date_create($prev_end),   date_interval_create_from_date_string('2 days'));
 $comp_date = date_format($comp_date, 'Y-m-d');

 if (($current == $previous) && ($comp_date == $cur_begin))  {

     $data[$i-1][5] = $cur_end; // update [5] 'end date range'

     // calculate new range in days
     $date_prev_begin = date_create("$prev_begin");
     $date_cur_end = date_create("$cur_end");
     $interval = date_diff($date_prev_begin, $date_cur_end);
     $new_days=$interval->format('%a'); 

     $data[$i-1][3] = $new_days+1; // update [3] 'days' 

     unset($data[$i]); // remove row
 }


// store current row to use for comparison in next iteration

$previous = $current;
$prev_days = $cur_days;    
$prev_begin = $cur_begin;
$prev_end = $cur_end;
$i++;    
}
$data=array_values($data); // rearrange array

Upvotes: 0

Views: 93

Answers (1)

CUGreen
CUGreen

Reputation: 3184

Not sure if it is more efficient but it contains a few less lines of code:

$new_data = array();

foreach($data as $row) {

    $cur_begin = new DateTime($row[4]);
    $new_row = $row;
    $days = 0;

    $last_index = !empty($new_data) ? count($new_data)-1 : -1;
    $prev_row = $last_index >= 0 ? $new_data[$last_index] : null;

    if(!empty($prev_row)) {
        $prev_end = new DateTime($prev_row[5]);
        $interval = $prev_end->diff($cur_begin);
        $days = $interval->days;

        $isdiff = $row[0].$row[1].$row[2] != $prev_row[0].$prev_row[1].$prev_row[2];
    }

    if(!empty($prev_row) && $days > 0 && !$isdiff) {
        $new_data[$last_index][3] += $days+1;
        $new_data[$last_index][5] = $row[5];
    } else {
        $new_data[] = $row;
    }

}

var_dump($new_data);

Hope it helps.

Upvotes: 1

Related Questions