Level 104
What determines lateness?
Jun 20, 2021
6
Level 9
Collection where date between
Guys. I have attendance system and I want to generate a report that show employees and if they are late in particular date.
- I get employees and eager load their attendances in that date.
$date = '2021-05-01';
$closure = fn ($query) => $query->whereDate('date', $date);
Employee::where('branch_id', 82)->whereHas('attendances', $closure)
->with(['attendances' => $closure])
->get()
->map(function ($employee) use ($date) {
$calc = new Calculator(
$employee->attendances ,
Carbon::create($date)
);
$employee['state'] = $calc->getResults();
return $employee;
});
- In calculator class I receive a set of attendances for that employee
// this just return a range to compare valid attendance
// each timesheet has start_time, end_time like from 09:00 to 17:00
// so the will return 2021-05-01 09:00:00 & 2021-05-01 17:00:00
[$firstRange, $secondRange] = $first->timesheet->ranges($this->date);
//after I get ranges I want to get all attendance in this range and ignore others because they're not valid
// it's hard to do it in query level because each employee has diff timesheet and each timesheet has diff start/end time
$attendances->filter(function ($item) {
// how to do the filter here by $firstRange, $secondRange
})->values();
Level 9
let say his timesheet range is:
2021-05-01 09:00:00 to 2021-05-01 17:00:00
I filter his attendances and retrieve all record in this range. THEN I order them and take first one as his login and last one as his logout.
After that I get difference in minutes between his login and 09:00 and that would be his login lateness and difference in minutes between his logout and 17:00 and that would be his logout lateness
Level 122
difficult to help when you can't describe the business logic
Level 9
basically I have three models
- Employee
- Timesheet
(start_time, end_time) - Attendance
(timesheet_id, employee_id, date)
each employee may have many/zero attendance records at any given day.
let's say employee with id 100 has 5 records today (06/20/2021 06:45:00) and (06/20/2021 09:59:00) and (06/20/2021 10:45:00) and (06/20/2021 18:00:00) and his timesheet range from 08:00 to 18:00.
what I want to do is to filter down these 5 records and exclude everything before 08:00 and after 18:00 because these are the times that company building open/close at. so they have hour before 09 and hour after 17. and anything outside this range is not valid.
in this case this employee will have only 4 valid records which are:
(06/20/2021 09:59:00) and (06/20/2021 10:45:00) and (06/20/2021 18:00:00)
now 09:59 will be his login time with one hour lateness and 18:00 will be his logout time with 0 min lateness.
Level 122
(06/20/2021 18:00:00) and not (06/20/2021 18:00:01) ?
its convenient when times exactly match, but life is not like that
Level 9
yes. even though he could have records after 18:00:00 but according to policy they must be ignored.
Please or to participate in this conversation.