In my Laravel application, I have functionality to handle night shifts and calculate durations, counts, and overtime based on these shifts. This data is correctly displayed on the web page. However, when exporting this data to Excel using the RecordsExport class, the night shift calculations appear to be incorrect.
Display Page Output:
Name: john Doe
Shift: Night Shift
Check-In: 16 Jul, 16:19
Check-Out: 17 Jul, 05:57
Duration: 13 hr 38 min
Count: 13.63
Overtime: 0.00
Exported Data Output:
Name: john Doe
Check-In: 16 Jul, 05:40
Check-Out: 16 Jul, 17:07
Duration: 11 hr 26 min
Count: 11.43
Overtime: 3.43
this is the coe
<?php
namespace AppExports;
use AppModelsPerson;
use AppModelsRecord;
use CarbonCarbon;
use MaatwebsiteExcelConcernsExportable;
use MaatwebsiteExcelConcernsFromQuery;
use MaatwebsiteExcelConcernsShouldAutoSize;
use MaatwebsiteExcelConcernsWithColumnFormatting;
use MaatwebsiteExcelConcernsWithHeadings;
use MaatwebsiteExcelConcernsWithMapping;
use MaatwebsiteExcelConcernsWithStyles;
use PhpOfficePhpSpreadsheetSharedDate;
use PhpOfficePhpSpreadsheetWorksheetWorksheet;
class RecordsExport implements FromQuery, WithHeadings, WithMapping, WithColumnFormatting, ShouldAutoSize, WithStyles
{
use Exportable;
public $location;
public $name;
public $date;
public $locationName;
public $locationId;
public function query()
{
$locationId = request()->get('location_id');
$dateFrom = request()->get('date');
$dateTo = request()->get('date_to') ?: request()->get('date');
return Person::selectRaw('people.name as name,
daily_summaries.checkin,
daily_summaries.checkout,
daily_summaries.duration,
daily_summaries.count,
daily_summaries.overtime,
daily_summaries.overtime2')
->leftJoin('daily_summaries', function($join) use ($dateFrom, $dateTo, $locationId) {
$join->on('people.id', '=', 'daily_summaries.person_id')
->where('daily_summaries.location_id', '=', $locationId)
->whereBetween('daily_summaries.date', [$dateFrom, $dateTo]);
})
->where('people.location_id', $locationId)
->orderBy('people.name');
}
public function map($record): array
{
$checkin = $record->checkin ? Carbon::parse($record->checkin) : null;
$checkout = $record->checkout ? Carbon::parse($record->checkout) : null;
// Adjust check-in time if it's a night shift
if ($this->isNightShift($record->id, $checkin)) {
$checkin = $this->getCheckInAfter16($record->id, $record->location_id, $checkin->toDateString());
}
// Calculate duration and other metrics
$duration = $this->getDuration($record);
$count = $this->getCount($record);
$overtime = $this->getOvertime($record);
$status = $checkin ? '' : 'Absent';
return [
$record->name,
$checkin ? Date::dateTimeToExcel($checkin) : $status,
$checkout ? Date::dateTimeToExcel($checkout) : '',
$duration,
$count,
$overtime,
$record->overtime2 ?? 0,
];
}
public function headings(): array
{
$locationId = request()->get('location_id');
$date = request()->get('date');
$dateTo = request()->get('date_to') ?: request()->get('date');
$name = request()->get('name');
return [
['BioForce Report'],
["Date: $date - $dateTo", "Location: $locationId"],
[$name ? "Filtered For: $name" : ""],
['Name', 'Check In', 'Check Out', 'Duration', 'Count', 'Overtime', 'Overtime 2'],
];
}
public function columnFormats(): array
{
return [
'B' => 'd mmm, h:mm',
'C' => 'd mmm, h:mm'
];
}
public function styles(Worksheet $sheet)
{
$sheet->mergeCells('A1:D1');
$sheet->getStyle('3')
->getFont()->getColor()->setARGB(PhpOfficePhpSpreadsheetStyleColor::COLOR_DARKYELLOW);
return [
1 => [
'font' => ['bold' => true, 'size' => 16],
'alignment' => [
],
],
2 => ['font' => ['bold' => true]],
3 => ['font' => ['bold' => true, 'italics' => true]],
4 => ['font' => ['bold' => true]],
'D' => [
'font' => ['bold' => true],
'alignment' => [
'horizontal' => PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER,
],
],
];
}
private function getCheckInAfter16($personId, $locationId, $date)
{
$dateStart = Carbon::parse($date)->startOfDay()->addHours(16);
$checkInRecord = Record::where('person_id', $personId)
->where('location_id', $locationId)
->where('time', '>=', $dateStart)
->orderBy('time')
->first();
return $checkInRecord ? Carbon::parse($checkInRecord->time) : null;
}
private function isNightShift($personId, $checkInTime): bool
{
if (!$personId || !$checkInTime) {
return false;
}
$nightShiftStartTime = Carbon::createFromTimeString('16:00:00');
$nightShiftEndTime = Carbon::createFromTimeString('09:00:00')->addDay();
$personShift = PersonShift::where('person_id', $personId)
->where('shift_id', 4) // Assuming night shift ID is 4
->whereDate('created_at', '<=', $checkInTime->toDateString())
->first();
if ($personShift) {
if ($checkInTime->between($nightShiftStartTime, $nightShiftEndTime) || $checkInTime->lessThan($nightShiftStartTime)) {
return true;
}
}
return false;
}
private function getDuration(Person $person)
{
$checkInTime = $person->checkin ? Carbon::parse($person->checkin) : null;
$checkOutTime = $person->checkout ? Carbon::parse($person->checkout) : null;
if ($this->isNightShift($person->id, $checkInTime) && $checkInTime) {
$nightShiftEndTime = Carbon::createFromTimeString('09:00:00')->addDay();
$checkInAfter16 = $this->getCheckInAfter16($person->id, $person->location_id, $checkInTime->toDateString());
if ($checkInAfter16) {
$nextDayCheckIn = DailySummary::where('person_id', $person->id)
->where('checkin', '>', $checkInAfter16)
->where('checkin', '<', $nightShiftEndTime)
->orderBy('checkin')
->first();
if ($nextDayCheckIn) {
$nextDayCheckInTime = Carbon::parse($nextDayCheckIn->checkin);
if ($nextDayCheckInTime->diffInDays($checkInAfter16) > 1) {
return 'No duration recorded (More than 2 days)';
}
$duration = $checkInAfter16->diff($nextDayCheckInTime);
$hours = $duration->h + ($duration->days * 24);
$minutes = $duration->i;
return sprintf('%d hr %d min', $hours, $minutes);
} else {
return 'No checkout recorded';
}
} else {
return 'No check-in after 16:00';
}
}
if ($checkInTime && $checkOutTime) {
if ($this->isAppCheckpoint($person)) {
$checkOutTime->addHours(2);
}
$duration = $checkInTime->diff($checkOutTime);
$hours = $duration->h + ($duration->days * 24);
$minutes = $duration->i;
return sprintf('%d hr %d min', $hours, $minutes);
}
return '';
}
private function getCount(Person $person)
{
$checkInTime = $person->checkin ? Carbon::parse($person->checkin) : null;
$checkOutTime = $person->checkout ? Carbon::parse($person->checkout) : null;
if ($this->isNightShift($person->id, $checkInTime) && $checkInTime) {
$nightShiftEndTime = Carbon::createFromTimeString('09:00:00')->addDay();
$checkInAfter16 = $this->getCheckInAfter16($person->id, $person->location_id, $checkInTime->toDateString());
if ($checkInAfter16) {
$nextDayCheckIn = DailySummary::where('person_id', $person->id)
->where('checkin', '>', $checkInAfter16)
->where('checkin', '<', $nightShiftEndTime)
->orderBy('checkin')
->first();
if ($nextDayCheckIn) {
$nextDayCheckInTime = Carbon::parse($nextDayCheckIn->checkin);
if ($nextDayCheckInTime->diffInDays($checkInAfter16) > 1) {
return 'No count recorded (More than 2 days)';
}
return '1';
} else {
return 'No count recorded';
}
} else {
return 'No count recorded';
}
}
return $person->count ?? '0';
}
private function getOvertime(Person $person)
{
$checkInTime = $person->checkin ? Carbon::parse($person->checkin) : null;
$checkOutTime = $person->checkout ? Carbon::parse($person->checkout) : null;
if ($this->isNightShift($person->id, $checkInTime) && $checkInTime) {
$nightShiftEndTime = Carbon::createFromTimeString('09:00:00')->addDay();
$checkInAfter16 = $this->getCheckInAfter16($person->id, $person->location_id, $checkInTime->toDateString());
if ($checkInAfter16) {
$nextDayCheckIn = DailySummary::where('person_id', $person->id)
->where('checkin', '>', $checkInAfter16)
->where('checkin', '<', $nightShiftEndTime)
->orderBy('checkin')
->first();
if ($nextDayCheckIn) {
$nextDayCheckInTime = Carbon::parse($nextDayCheckIn->checkin);
if ($nextDayCheckInTime->diffInDays($checkInAfter16) > 1) {
return 'No overtime recorded (More than 2 days)';
}
return $this->calculateOvertime($checkInAfter16, $nextDayCheckInTime);
} else {
return 'No overtime recorded';
}
} else {
return 'No overtime recorded';
}
}
return $person->overtime ?? '0';
}
private function calculateOvertime($checkInAfter16, $nextDayCheckIn)
{
$overtime = $checkInAfter16->diffInMinutes($nextDayCheckIn);
return sprintf('%d min', $overtime);
}
private function isAppCheckpoint(Person $person): bool
{
// Define your logic for app checkpoints
return false;
}
}
Reviewed Calculation Methods: I reviewed the methods used for calculating durations, counts, and overtime in both the display page and the RecordsExport class. I made sure the logic for determining night shifts, handling check-in/check-out times, and calculating durations is consistent.
Debugged Export Logic: I added debug statements to verify the values being processed during the export. I checked whether the night shift logic was being applied correctly and whether the calculations matched what was displayed on the page.
Verified Data Consistency: I compared the data from the display page with the exported Excel file, paying close attention to the check-in/check-out times, durations, counts, and overtime. I also ensured that the RecordsExport class used the same logic for night shift calculations as the display page
What I Was Expecting:
Consistent Results: I expected the data displayed on the web page and the data exported to Excel to match exactly, especially in terms of night shift calculations, check-in/check-out times, durations, counts, and overtime.
Accurate Calculations: I expected the calculations for night shifts to be accurately reflected in both the web display and the exported Excel file, ensuring that all durations, counts, and overtime are calculated consistently.
Correct Export Logic: I anticipated that the export logic would correctly apply night shift adjustments and calculations, resulting in an Excel file that accurately reflects the data as seen on the display page.
New contributor
Noel Mbewe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.