Reputation: 1
Issue with Night Shift Calculation in Laravel Export - Discrepancies Between Display and Exported Data
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 App\Exports;
use App\Models\Person;
use App\Models\Record;
use Carbon\Carbon;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
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(\PhpOffice\PhpSpreadsheet\Style\Color::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' => \PhpOffice\PhpSpreadsheet\Style\Alignment::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.
Upvotes: 0
Views: 30
Answers (0)
Related Questions
- export large data with chunk in Laravel-Excel
- Laravel Eloquent Query: Using WHERE with OR AND OR?
- How to query between two dates using Laravel and Eloquent?
- Differences and Similarities Between Lumen and Laravel
- How to export csv file with shift-jis encoding in laravel?
- Laravel-Excel export relationship data
- Laravel Excel 3.1 export large data
- Export Relationship data using Laravel Excel