Laravel group by clause

I have a very hard situation and I been trying to find a solution either with query builder or eloquent but had no luck for days.

I have a database table/eloquent model "Attendance" with the following fields:

id, employee_id, timesheet_id, attendance_time 'datetime'

this table represents fingerprint log for every employee in particular branch.

timesheet_id: represents employee work shift during the day and I have two of those:

from 6 am to 3 pm
from 3 pm to 12 am next day "which causing the problem"

now I want to make a monthly report that grab all data in this table in particular period of time let say from 01-09-2020 to 30-09-2020 which is very easy for employees whom in work shift one, but for those who work in shift two it's kinda hard because when grouping records by date - they logout record which more likely would be in the next day - will not be grouped with that date if it makes sense.

let's take employee #3 records at 26-09-2020 for example:

see the (b) record is a logout record of day 2020-09-25 and not login record of 2020-09-26, which means if this employee had only one record in the day I must check the time if it's less than or equal to 6 am it means it's logout record for previous day otherwise it's login record for this day.

what I tried is I grab all records from 01-09-2020 00:00:00 to 01-10-2020 06:00:00 so I can catch logout for 30-09 and then I group by attendance_time but how do I check that employee has logout record in the next day when grouping by date.

$start = "2020/09/01 00:00:00";
$end = "2020/10/01 06:00:00";
$branch_id = 1;
$timesheet = 2; // employees who work from 3pm to 00 am

$attendances = Attendance::oldest()
                ->where([
                    'timesheet_id' => $timesheet
                ])
                ->whereHas('employee', function ($q) use ($branch_id) {
                    $q->where('branch_id', $branch_id);
                })
                ->whereBetween('attendance_time', [$start, $end])
                ->where('employee_id', 3)
                ->get(['employee_id', 'attendance_time']);

$monthly = $attendances->groupBy(function ($item) {
               return $item->attendance_time->toDateString();
            });

I know it's complicated, if anyone can help I would appreciate that so much.

spyworld

5 years ago

Level 4

since you grouping by date and using laravel timestamp format, you can direct use

similar to:

$hour =$item->attendance_time->format('H');

if($hour >= 15) {
	$datetime = $item->attendance_time->modify('+1 day');
	return $datetime->toDateString();
}
return $item->attendance_time->toDateString();

1 like

spyworld

5 years ago

Level 4

I'm still using laravel 4.2. This will work with latest laravel version.

$datetime = new DateTime( $item->attendance_time->toDateString() );

if($datetime->format('H')  >= 15) {
	$datetime = $datetime->modify('+1 day');
}
return $datetime->format('Y-m-d');

uniqueginun

5 years ago

Level 9

why did you check if hour >= 3pm ?

Please or to participate in this conversation.