Determine the duration of each PhaseId

Question

I have the following array:

array (size=8)
  0 => 
    array (size=5)
      'entity_id' => int 571962
      'time' => int 1671101084788
      'time to datetime' => string '2022-12-15 11:44:44' (length=19)
      'PhaseId_new' => string 'Close' (length=5)
      'PhaseId_old' => string 'Accept' (length=6)
  1 => 
    array (size=5)
      'entity_id' => int 571962
      'time' => int 1671100537178
      'time to datetime' => string '2022-12-15 11:35:37' (length=19)
      'PhaseId_new' => string 'Accept' (length=6)
      'PhaseId_old' => string 'Fulfill' (length=7)
  2 => 
    array (size=5)
      'entity_id' => int 571962
      'time' => int 1671100012012
      'time to datetime' => string '2022-12-15 11:26:52' (length=19)
      'PhaseId_new' => string 'Fulfill' (length=7)
      'PhaseId_old' => string 'Review' (length=6)
  3 => 
    array (size=5)
      'entity_id' => int 571962
      'time' => int 1671099984979
      'time to datetime' => string '2022-12-15 11:26:24' (length=19)
      'PhaseId_new' => string 'Review' (length=6)
      'PhaseId_old' => string 'Accept' (length=6)
  4 => 
    array (size=5)
      'entity_id' => int 571962
      'time' => int 1671099802675
      'time to datetime' => string '2022-12-15 11:23:22' (length=19)
      'PhaseId_new' => string 'Accept' (length=6)
      'PhaseId_old' => string 'Fulfill' (length=7)
  5 => 
    array (size=5)
      'entity_id' => int 571962
      'time' => int 1671027321749
      'time to datetime' => string '2022-12-14 15:15:21' (length=19)
      'PhaseId_new' => string 'Fulfill' (length=7)
      'PhaseId_old' => string 'Approve' (length=7)
  6 => 
    array (size=5)
      'entity_id' => int 571962
      'time' => int 1671011168777
      'time to datetime' => string '2022-12-14 10:46:08' (length=19)
      'PhaseId_new' => string 'Approve' (length=7)
      'PhaseId_old' => string 'Log' (length=3)
  7 => 
    array (size=5)
      'entity_id' => int 571962
      'time' => int 1671011166077
      'time to datetime' => string '2022-12-14 10:46:06' (length=19)
      'PhaseId_new' => string 'Log' (length=3)
      'PhaseId_old' => null

I regrouped each sub-array using entity_id:

$result = array();
foreach ($data as $element) {
    //var_dump($element);
    $result[$element['entity_id']][] = $element;
}

Which outputs me:

array (size=1)
  571962 => 
    array (size=8)
      0 => 
        array (size=5)
          'entity_id' => int 571962
          'time' => int 1671101084788
          'time to datetime' => string '2022-12-15 11:44:44' (length=19)
          'PhaseId_new' => string 'Close' (length=5)
          'PhaseId_old' => string 'Accept' (length=6)
      1 => 
        array (size=5)
          'entity_id' => int 571962
          'time' => int 1671100537178
          'time to datetime' => string '2022-12-15 11:35:37' (length=19)
          'PhaseId_new' => string 'Accept' (length=6)
          'PhaseId_old' => string 'Fulfill' (length=7)
      2 => 
        array (size=5)
          'entity_id' => int 571962
          'time' => int 1671100012012
          'time to datetime' => string '2022-12-15 11:26:52' (length=19)
          'PhaseId_new' => string 'Fulfill' (length=7)
          'PhaseId_old' => string 'Review' (length=6)
      3 => 
        array (size=5)
          'entity_id' => int 571962
          'time' => int 1671099984979
          'time to datetime' => string '2022-12-15 11:26:24' (length=19)
          'PhaseId_new' => string 'Review' (length=6)
          'PhaseId_old' => string 'Accept' (length=6)
      4 => 
        array (size=5)
          'entity_id' => int 571962
          'time' => int 1671099802675
          'time to datetime' => string '2022-12-15 11:23:22' (length=19)
          'PhaseId_new' => string 'Accept' (length=6)
          'PhaseId_old' => string 'Fulfill' (length=7)
      5 => 
        array (size=5)
          'entity_id' => int 571962
          'time' => int 1671027321749
          'time to datetime' => string '2022-12-14 15:15:21' (length=19)
          'PhaseId_new' => string 'Fulfill' (length=7)
          'PhaseId_old' => string 'Approve' (length=7)
      6 => 
        array (size=5)
          'entity_id' => int 571962
          'time' => int 1671011168777
          'time to datetime' => string '2022-12-14 10:46:08' (length=19)
          'PhaseId_new' => string 'Approve' (length=7)
          'PhaseId_old' => string 'Log' (length=3)
      7 => 
        array (size=5)
          'entity_id' => int 571962
          'time' => int 1671011166077
          'time to datetime' => string '2022-12-14 10:46:06' (length=19)
          'PhaseId_new' => string 'Log' (length=3)
          'PhaseId_old' => null

Now I need to calculate the duration of each stage (record, approve, fulfill, accept, review).

For example:

Log: 1671011168777 - 1671011166077 = 2700

Approved: 1671027321749 - 1671011168777 = 16152972

Implementation: (1671100537178 - 1671100012012) (1671099802675 - 1671027321749) = 73006092

Accepted: (1671101084788 - 1671100537178) (1671099984979 - 1671099802675) = 729914

Comments: 1671100012012 - 1671099984979 = 27033

I was able to parse each stage using:

foreach($result as $key => $val){
    //var_dump($key);
    foreach($val as $key2 => $val2){
        if($val2['PhaseId_new'] == 'Fulfill' or $val2['PhaseId_old'] == 'Fulfill'){

           // var_dump($val2);
        }
    }
}

But I'm not clear on how to calculate the duration of each stage.

The expected results are as follows:

array (size=1)
  571962 => 
    array (size=8)
          'Log' => int 2700
          'Approve' => int 16152972
          'Fulfill' => int 73006092
          'Accept' => int 729914
          'Review' => int 27033

PHP Online: https://onlinephp.io/c/2270e

work process:

Answer 1

Try something like this.

//group by entity_id
$data_grouped = [];
foreach($data as $element) {
  $data_grouped[$element['entity_id']][] = $element;
}

$entity_phases = [];
//get all phases and their times
foreach ($data_grouped as $entity_id => $elements) {
  foreach ($elements as $element) {
    if ($element['PhaseId_new']) {
      $entity_phases[$entity_id][$element['PhaseId_new']][] = $element['time'];
    }
    if ($element['PhaseId_old']) {
      $entity_phases[$entity_id][$element['PhaseId_old']][] = $element['time'];
    }
  }
}

$result = [];
//iterate all phases and calculate time diffs
foreach ($entity_phases as $entity_id => $phases) {
  foreach ($phases as $key => $values) {
    if (!isset($result[$entity_id][$key])) {
      $result[$entity_id][$key] = 0;
    }
    //iterate in chunks of two elements
    foreach (array_chunk($values, 2) as $value) {
      //continue if only one value is found (e.g. for "Close")
      if (!isset($value[1])) {
        continue;
      }
      $result[$entity_id][$key] = $result[$entity_id][$key] + $value[0] - $value[1];
    }
  }
}
var_dump($result);

Here you go:

array(1) {
  [571962]=>
  array(6) {
    ["Close"]=>
    int(0)
    ["Accept"]=>
    int(729914)
    ["Fulfill"]=>
    int(73006092)
    ["Review"]=>
    int(27033)
    ["Approve"]=>
    int(16152972)
    ["Log"]=>
    int(2700)
  }
}

Answer 2

Okay, this took me longer than I wanted, but I got the result. First is the code:

$entityPhases = [];
foreach ($data as $element) {
    $entityPhases[$element['entity_id']][] = $element;
}

$durations = [];
$oldPhases = [];
foreach ($entityPhases as $phases) {
    foreach(array_reverse($phases) as $phase) {
        if ($phase['PhaseId_old']) {
            $oldPhaseName = $phase['PhaseId_old'];
            $duration = $phase['time'] - $oldPhases[$oldPhaseName]['time'];
            $durations[$oldPhaseName] = ($durations[$oldPhaseName] ?? 0) + $duration;
        }   
        $oldPhases[$phase['PhaseId_new']] = $phase;
    }
}

print_r($durations);

See: https://onlinephp.io/c/92d7f

turn out:

Array
(
    [Log] => 2700
    [Approve] => 16152972
    [Fulfill] => 73006092
    [Accept] => 729914
    [Review] => 27033
)

Now explain:

First, your data array looks upside down, so I used array_reverse() to fix it. The here assumes that there is a logical order.

Since I may need to add multiple periods, I use an array called $durations to add them together.

Then in the inner loop, if there is an old stage ID, I can calculate the duration and add it up. Finally I remember the old stage because I need it in the next iteration of the loop.

I also renamed a lot of things so that their names convey the contents of the variables.