Issue with Night Shift Calculation in Laravel Export - inconsistency Between Display and Exported Data

[noelmbewe]

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 code for the display

`<?php

namespace App\Orchid\Layouts;

use App\Models\DailySummary;
use App\Models\Shift;
use App\Models\PersonShift;
use App\Models\ShiftLocation;
use App\Models\Record;
use App\Models\Person;
use Carbon\Carbon;
use Orchid\Screen\Layouts\Table;
use Orchid\Screen\TD;

class RecordListLayout extends Table
{
protected $target = 'records';

protected function columns(): iterable
{
    return [
        TD::make('person.name', 'Name')
            ->render(function ($record) {
                return $record->person->name ?? 'Unknown';
            })->sort(),

        TD::make('shift')->render(function ($record) {
            $personId = $record->person_id ?? null;
            $checkInTime = $record->checkin ? Carbon::parse($record->checkin) : null;

            if (!$checkInTime) {
                return ''; // Return empty string for absent people
            }

            if ($this->isNightShift($personId, $checkInTime)) {
                $nightShift = Shift::find(4); // Assuming night shift ID is 4
                return $nightShift ? $nightShift->name : '';
            }

            $locationId = $record->location_id;
            $shiftLocation = ShiftLocation::where('location_id', $locationId)
                ->whereHas('shift', function ($query) use ($checkInTime) {
                    $query->where(function ($q) use ($checkInTime) {
                        $q->whereBetween('start', [
                            $checkInTime->copy()->subHours(2)->format('H:i'),
                            $checkInTime->format('H:i')
                        ])->orWhereBetween('start', [
                            $checkInTime->format('H:i'),
                            $checkInTime->copy()->addHours(8)->format('H:i')
                        ]);
                    })->where('shift_id', '!=', 4); // Exclude night shift
                })
                ->first();

            return $shiftLocation ? $shiftLocation->shift->name : '...';
        }),

        TD::make('checkin')->render(function ($record) {
            if (!$record->checkin) {
                return 'Absent'; // Return 'Absent' if checkin is null
            }

            $checkInTime = Carbon::parse($record->checkin);
            $personId = $record->person_id;
            $locationId = $record->location_id;
            $date = $checkInTime->toDateString(); // Get the date part for querying records

            // Check if it's a night shift
            if ($this->isNightShift($personId, $checkInTime)) {
                $checkInTime = $this->getCheckInAfter16($personId, $locationId, $date);
                if (!$checkInTime) {
                    return 'Absent'; // No check-in found after 16:00
                }
            }

            if ($this->isAppCheckpoint($record)) {
                $checkInTime->addHours(2); // Add 2 hours if it's an app checkpoint
            }

            return $checkInTime->format('d M, H:i');
        })->sort(),

        TD::make('checkout')->render(function (DailySummary $item) {
            return $this->getCheckOutTime($item);
        }),

        TD::make('duration')->render(function (DailySummary $item) {
            return $this->getDuration($item);
        })->sort(),

        TD::make('count')->render(function (DailySummary $item) {
            return $this->getCount($item);
        })->sort(),

        TD::make('overtime')->render(function (DailySummary $item) {
            return $this->getOvertime($item);
        })->sort(),

        TD::make('overtime 2')->sort(),

        TD::make('Position')->render(function ($s) {
            $person = Person::find($s['person']['id']);
            return $person ? $person->position : 'Unknown';
        })->sort(),
    ];
}

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 isAppCheckpoint(DailySummary $item): bool
{
    $firstRecord = Record::where('person_id', $item->person_id)
        ->where('location_id', $item->location_id)
        ->whereNotNull('status')
        ->orderBy('time')
        ->first();

    return $firstRecord && $firstRecord->checkpoint === 'App';
}
private function isNightShift($personId, $checkInTime): bool
{
    if (!$personId || !$checkInTime) {
        return false;
    }

    // Define the night shift ID
    $nightShiftId = 4;

    // Get the night shift start and end times
    $nightShiftStartTime = Carbon::createFromTimeString('16:00:00');
    $nightShiftEndTime = Carbon::createFromTimeString('09:00:00')->addDay();

    // Retrieve the person shift record for the exact date
    $personShift = PersonShift::where('person_id', $personId)
        ->where('shift_id', $nightShiftId)
        ->whereDate('date', $checkInTime->toDateString())
        ->first();

    if ($personShift) {
        // Determine if the check-in time is within the night shift period
        if ($checkInTime->between($nightShiftStartTime, $nightShiftEndTime) || $checkInTime->lessThan($nightShiftStartTime)) {
            return true;
        }
    }

    return false;
}



private function getCheckOutTime(DailySummary $item)
{
    $personId = $item->person_id;
    $checkInTime = $item->checkin ? Carbon::parse($item->checkin) : null;
    $checkOutTime = $item->checkout ? Carbon::parse($item->checkout) : null;

    if ($this->isNightShift($personId, $checkInTime) && $checkInTime) {
        $nightShiftEndTime = Carbon::createFromTimeString('09:00:00')->addDay();

        // Find the next day's check-in time if it exists
        $nextDayCheckIn = DailySummary::where('person_id', $personId)
            ->where('checkin', '>', $checkInTime)
            ->where('checkin', '<', $nightShiftEndTime)
            ->orderBy('checkin')
            ->first();

        if ($nextDayCheckIn) {
            $nextDayCheckInTime = Carbon::parse($nextDayCheckIn->checkin);
            if ($nextDayCheckInTime->diffInDays($checkInTime) > 1) {
                return 'No checkout recorded (More than 2 days)';
            }
            return $nextDayCheckInTime->format('d M, H:i') . ' (Night shift)';
        } else {
            return 'No checkout recorded';
        }
    }


    if ($checkOutTime && $this->isAppCheckpoint($item)) {
        $checkOutTime->addHours(2);
    }

    return $checkOutTime ? $checkOutTime->format('d M, H:i') : '';
}

private function getDuration(DailySummary $item)
{
    $personId = $item->person_id;
    $checkInTime = $item->checkin ? Carbon::parse($item->checkin) : null;
    $checkOutTime = $item->checkout ? Carbon::parse($item->checkout) : null;

    if ($this->isNightShift($personId, $checkInTime) && $checkInTime) {
        $nightShiftEndTime = Carbon::createFromTimeString('09:00:00')->addDay();
        $checkInAfter16 = $this->getCheckInAfter16($personId, $item->location_id, $checkInTime->toDateString());

        if ($checkInAfter16) {
            // Calculate duration between check-in after 16:00 and the next day's check-in
            $nextDayCheckIn = DailySummary::where('person_id', $personId)
                ->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($item)) {
            $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(DailySummary $item)
{
    $personId = $item->person_id;
    $checkInTime = $item->checkin ? Carbon::parse($item->checkin) : null;
    $checkOutTime = $item->checkout ? Carbon::parse($item->checkout) : null;

    if ($this->isNightShift($personId, $checkInTime) && $checkInTime) {
        $nightShiftEndTime = Carbon::createFromTimeString('09:00:00')->addDay();
        $checkInAfter16 = $this->getCheckInAfter16($personId, $item->location_id, $checkInTime->toDateString());

        if ($checkInAfter16) {
            // Calculate duration between check-in after 16:00 and the next day's check-in
            $nextDayCheckIn = DailySummary::where('person_id', $personId)
                ->where('checkin', '>', $checkInAfter16)
                ->where('checkin', '<', $nightShiftEndTime)
                ->orderBy('checkin')
                ->first();

            if ($nextDayCheckIn) {
                $nextDayCheckInTime = Carbon::parse($nextDayCheckIn->checkin);
                if ($nextDayCheckInTime->diffInDays($checkInAfter16) > 1) {
                    return '0.00'; // More than 2 days
                }

                $duration = $checkInAfter16->diff($nextDayCheckInTime);
                $hours = $duration->h + ($duration->days * 24);
                $minutes = $duration->i;

                $totalCount = $hours + ($minutes / 60.0);

                return number_format($totalCount, 2); // Format to 2 decimal places
            } else {
                return '0.00'; // No checkout recorded
            }
        } else {
            return '0.00'; // No check-in after 16:00
        }
    }

    if ($checkInTime && $checkOutTime) {
        if ($this->isAppCheckpoint($item)) {
            $checkOutTime->addHours(2);
        }

        $duration = $checkInTime->diff($checkOutTime);
        $hours = $duration->h + ($duration->days * 24);
        $minutes = $duration->i;

        $totalCount = $hours + ($minutes / 60.0);

        return number_format($totalCount, 2); // Format to 2 decimal places
    }

    return '0.00'; // No check-in or check-out recorded
}

private function getOvertime(DailySummary $item)
{
    $personId = $item->person_id;
    $checkInTime = $item->checkin ? Carbon::parse($item->checkin) : null;
    $checkOutTime = $item->checkout ? Carbon::parse($item->checkout) : null;

    if ($this->isNightShift($personId, $checkInTime) && $checkInTime) {
        $nightShiftEndTime = Carbon::createFromTimeString('09:00:00')->addDay();

        // Find the next day's check-in time if it exists
        $nextDayCheckIn = DailySummary::where('person_id', $personId)
            ->where('checkin', '>', $checkInTime)
            ->where('checkin', '<', $nightShiftEndTime)
            ->orderBy('checkin')
            ->first();

        if ($nextDayCheckIn) {
            $nextDayCheckInTime = Carbon::parse($nextDayCheckIn->checkin);
            if ($nextDayCheckInTime->diffInDays($checkInTime) > 1) {
                return 'No checkout recorded (More than 2 days)';
            }

            $fixedSixAM = $checkInTime->copy()->addDay()->startOfDay()->addHours(6);

            // Calculate overtime if checkout time is after 06:00 AM
            if ($nextDayCheckInTime->greaterThan($fixedSixAM)) {
                $overtimeMinutes = $nextDayCheckInTime->diffInMinutes($fixedSixAM);
                $overtime = $overtimeMinutes / 60.0; // Convert minutes to hours
                return number_format(max($overtime, 0), 2); // Show both times and overtime
            } else {
                return number_format(0, 2); // Show both times and zero overtime
            }
        } else {
            return 'No checkout recorded';
        }
    }

    // Return the overtime from the database if not a night shift or no valid check-out time is found
    return number_format($item->overtime, 2);
}

}
`

and this a code for exporting

` <?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;
}

}`

[anxgstadler]

I would strongly advise you to move all of the calculation to one single service class. There is a lot of code duplication going on between those two files, which makes it hard to find the mistake. I'm sure during this process you will find the offending part of the code.

(I just compared the two getDuration() methods line by line and they seem to be ok.)