How to get same day values and calculate working hours for that day from php array

Question

I got a big associative array I pull from that array user's full name and time. Now in that array, I don't have only one day but I got a full month date and time, it looks something like this

John Doe : 2022-04-01 08:00:00
John Doe : 2022-04-01 10:00:00
John Doe : 2022-04-01 14:00:00
John Doe : 2022-04-23 08:00:00
John Doe : 2022-04-23 10:00:00
John Doe : 2022-04-23 14:00:00

And now I want to pull from that array one single day for that user for example I want to pull just 2022-04-01 for John Doe and calculate his working hours, how much time he worked on that particular day and I want this for every day in a month to do that here is my script right now nothing much but it can help someone

foreach($xlsArr as $row){
   // Getting user work single day
    $fullName = $row['first-name'] . ' ' .$row['last-name']; // Example John Doe
    $date = $row['time']; // Example 2022-04-01 08:00:00
    echo $fullName . ' ' . $date . '<br>';
 }

Answer 1

so basic steps are:

1. group data by user
2. group each user data by day
3. calculations on grouped by day timestamps

/**
 *  helper function for data sample
 *  credits https://stackoverflow.com/a/24431558/6330083
 * @param DateTime $start 
 * @param DateTime $end 
 * @return DateTime
 */
function randomDateInRange(DateTime $start, DateTime $end)
{
  $randomTimestamp = mt_rand($start->getTimestamp(), $end->getTimestamp());
  $randomDate = new DateTime();
  $randomDate->setTimestamp($randomTimestamp);
  return $randomDate;
}

/**
 * provides basic raw data sample
 * example output
 * [
 *    [
 *      "first-name" => "0",
 *      "last-name" => "00",
 *      "time" => "2022-05-01 15:43:22",
 *    ],
 *    [
 *      "first-name" => "0",
 *      "last-name" => "00",
 *      "time" => "2022-05-02 13:43:06",
 *    ],
 *    [
 *      "first-name" => "1",
 *      "last-name" => "11",
 *      "time" => "2022-05-01 16:15:27",
 *    ],
 *  ...
 *  ]
 * @return array
 */
function sampleData()
{
  $dateStart = new DateTime('2022-05-01');
  $dateEnd = (new DateTime())->setTimestamp($dateStart->getTimestamp());
  $dateEnd->add(DateInterval::createFromDateString('2 days'));

  $sample = [];
  for ($i = 0; $i < 10; $i++) {
    for ($k = 0; $k < random_int(0, 6); $k++) {
      $time = randomDateInRange($dateStart, $dateEnd)->format('Y-m-d H:i:s');
      array_push($sample, [
        'first-name' => "$i",
        'last-name' => "$i$i",
        'time' => $time
      ]);
    }
  }
  return $sample;
}

/**
 * @param array
 * @return array
 */
function calculateWorkingHours(array $data = [])
{
  if (count($data) == 0) {
    $data = sampleData();
  }
  /* at this step we're grouping data by user with subarray contains time column
  sample output
  [
     "1 11" => [
       "2022-05-02 13:44:10",
     ],
     "2 22" => [
       "2022-05-02 03:59:20",
       "2022-05-02 17:50:01",
     ],
    ...
  ]
  */
  $grouped = [];
  foreach ($data as $row) {
    $groupName = $row['first-name'] . " " . $row['last-name'];
    $grouped[$groupName][] = $row['time'];
  }


  /*
    next groping by day
    actually this step probably might be changed to make final 
    summary instead of grouping,
    but you didn't mention the way you should calculate working 
    hours from provided timestamps
    sample output
    [
     "0 00" => [
       "2022-05-01" => [
         "2022-05-01 13:01:14",
         "2022-05-01 19:02:24",
       ],
       "2022-05-02" => [
         "2022-05-02 07:54:44",
       ],
     ],
     "1 11" => [
       "2022-05-02" => [
         "2022-05-02 05:10:19",
         "2022-05-02 14:40:41",
       ],
       "2022-05-01" => [
         "2022-05-01 12:15:36",
         "2022-05-01 23:26:19",
       ],
     ],
     ...
    ]
   */
  $withGroupedTimes = array_map(function ($ungroupedTimes) {
    $gropedTimes = [];
    foreach ($ungroupedTimes as $time) {
      $day = (new DateTime($time))->format('Y-m-d');
      $gropedTimes[$day][] = $time;
    }
    return $gropedTimes;
  }, $grouped);

  // now we have structure grouped by user with subroups by day for 
  // each user and can provide calculations
  $calculated = array_map(function ($user) {
    return array_map(function ($day) {
      // $day is array of timestamps
      // here you should provide logic to sum hours and return it int $result variable
      // as a basic example i return intervals count of that day
      $result = count($day);
      return $result;
    }, $user);
  }, $withGroupedTimes);

  return $calculated;
}

Answer 2

I found some solutions by grouping them by card number, sorry because I didn't post the whole array of data but I will show a solution

$xlsArr = excel_to_array($fileTmpName); // Converted XLS to Associative Array

        $employees = [];
        $employees_daily_checks = [];

        // Looping through array and pushing all employees to array sorting by card_number
        foreach($xlsArr as $row){
            $card_number = $row['card-number'];

            if(isset($card_number) && !empty($card_number)){
                if(!empty($employees[$card_number])){
                    array_push($employees[$card_number], $row);
                }else{
                    $employees[$card_number] = [$row];
                }
            }else{
                $errors[] = "Došlo je do greške prilikom u?itavanja podataka, nije mogu?e na?i 'Card Number' u dokumentu!";
                break;
            }
        }

        // Gettng employees daily checks
        foreach($employees as $key => $checks_arr){
            foreach($checks_arr as $value){
                $date = new DateTime($value['time']);
                $day = $date->format('d');

                if(!empty($employees_daily_checks[$key][(int)$day])){
                    array_push($employees_daily_checks[$key][(int)$day], $value);
                }else{
                    $employees_daily_checks[$key][(int)$day] = [$value];
                }
            }
        }
       
        $time_in_out_checks = [];

        // Time check for each emplyee day
        foreach($employees_daily_checks as $key => $value){
            // Go inside of day array
            foreach($value as $day => $data){
                // Go inside of data array, and push all time in/out checks
                foreach($data as $row){
                    if(isset($row['time']) && !empty($row['time']) && isset($row['first-name']) && !empty($row['first-name']) && isset($row['last-name']) && !empty($row['last-name'])){
                        // Calculate the hours of working
                        $first_check_in = min(array_column($data, 'time'));
                        $last_check_out = max(array_column($data, 'time'));
                        $hours_worked = (strtotime($last_check_out) - strtotime($first_check_in)) / 3600;
                        $hours_worked = round($hours_worked, 2);
                        $dayNumber = date('d', strtotime($row['time']));
                        $dayFullName = date('l', strtotime($row['time']));
                        $monthNumber = date('m', strtotime($row['time']));
                        $monthFullName = date('F', strtotime($row['time']));

                        $time_in_out_checks[$key][$day] = [
                            'first_check_in' => $first_check_in,
                            'last_check_out' => $last_check_out,
                            'hours_worked' => $hours_worked,
                            'day' => $dayFullName,
                            'dayNumber' => $dayNumber,
                            'month' => $monthFullName,
                            'monthNumber' => $monthNumber,
                        ];

                        $time_in_out_checks[$key]['employee_data'] = [
                            'fullName' => $row['first-name'] . ' ' . $row['last-name'],
                            'firstName' => $row['first-name'],
                            'lastName' => $row['last-name'],
                            'cardNumber' => $row['card-number'],
                        ];
                    }else{
                        $errors[] = "Došlo je do greške prilikom u?itavanja podataka, iz dokumenta nije mogu?e na?i potrebne podatke (Card Number, First Name, Last Name, Time) podatke!";
                        break 3;
                    }
                }
            }
        }

        // Check return values from array
        if(empty($errors)){
            if(isset($time_in_out_checks) && empty($time_in_out_checks)){
                $errors[] = "Došlo je do greške prilikom u?itavanja podataka, iz dokumenta nije mogu?e na?i potrebne podatke (Card Number, First Name, Last Name, Time) podatke!";
            }
        }