Laravel 7: How to Filter time from CSV file

I'm in the learning period of Laravel.

I'm trying to filter the first and last time from a column of CSV file and store it in two different column in database.

How to write the code for this logic.

I have user table where no, name, check-in, check-out column exist.

Now I will upload a csv file and there will be a column of time. A user may multiple time.

I want to store the first time and last time. Here is the csv file example:

id	name	time
1	Jhon	7/5/2022 10:01:00
1	Jhon	7/5/2022 12:01:00
2	Doe	7/5/2022 10:08:00
3	Smith	7/5/2022 10:12:00
1	Jhon	7/5/2022 17:05:00

Suppose, Jhon (id=1) has multiple times. Here I want to store the first time in check-in and the last time in check-out. How can I do that in Laravel 7?

Solution

You can take a look at laravel excel import https://docs.laravel-excel.com/3.1/imports/basics.html. I will give you a rough idea:

$collection = Excel::toCollection(new UsersImport, $request->input('file'));

the above line should be written on controller, and you can found details about above on https://docs.laravel-excel.com/3.1/imports/basics.html#importing-to-array-or-collection

Next, you have to create an import class, since we are exporting to toCollection, you can use something like this:

namespace App\Imports;

use App\User;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;

class UsersImport implements ToCollection
{
    public function collection(Collection $rows)
    {
        foreach ($rows as $row) 
        {
            //you can filter data from here and insert into database
        }
    }
}

On the above foreach loop, you will have access to CSV data on PHP on $row variable, from there you could manipulate and store it in the database. Details about this could be found at: https://docs.laravel-excel.com/3.1/imports/collection.html

#PS: we are loading all those data to memory on this method, in case if your csv file is huge, you might have to think about queuing it.

Update:

Here, is the rough code; here I am storing the smallest timestamp in check_in and the highest timestamp in check_out. To find these values, we set the first time in both check_in and check_out, and if the new value is smaller than check_in update check_in with the new value, and in the case of check_out we check if the new value is greater than old value, if yes replace it with the new value.

foreach($rows as $row){
    if(!isset( $result[$row['id']])){
        $result[$row['id']]['check_in'] = Carbon::parse($row['time']);
        $result[$row['id']]['check_out'] = Carbon::parse($row['time']);
    }else{
        $new_time = Carbon::parse($row['time']);
        $isSmallerCheckIn =  $new_time->lt($result[$row['id']]['check_in']);
        if($isSmallerCheckIn){
            $result[$row['id']]['check_in'] = $new_time;
        }
         $isGreaterCheckOut =  $new_time->gt($result[$row['id']]['check_out']);
        if($isGreaterCheckOut){
            $result[$row['id']]['check_out'] = $new_time;
        }
    }

}
dd($result);