app/Helpers/ReportHelper.php
<?php

namespace App\Helpers;

use App\Models\Project;
use App\Models\Task;
use App\Models\TimeInterval;
use App\Models\User;
use Carbon\Carbon;
use Carbon\CarbonPeriod;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use JsonException;
use Maatwebsite\Excel\Excel;
use RuntimeException;

class ReportHelper
{
    public static string $dateFormat = 'Y-m-d';

    public static function getReportFormat(Request $request)
    {
        return array_flip(self::getAvailableReportFormats())[$request->header('accept')] ?? null;
    }

    public static function getAvailableReportFormats(): array
    {
        return [
            'csv' => 'text/csv',
            'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'pdf' => 'application/pdf',
            'xls' => 'application/vnd.ms-excel',
            'ods' => 'application/vnd.oasis.opendocument.spreadsheet',
            'html' => 'text/html',
        ];
    }

    /**
     * @param int[] $users
     * @param Carbon $startAt
     * @param Carbon $endAt
     * @param string[] $select
     * @return Builder
     */
    public static function getBaseQuery(
        array  $users,
        Carbon $startAt,
        Carbon $endAt,
        array  $select = []
    ): Builder
    {
        return Project::join('tasks', 'tasks.project_id', '=', 'projects.id')
            ->join('time_intervals', 'time_intervals.task_id', '=', 'tasks.id')
            ->join('users', 'time_intervals.user_id', '=', 'users.id')
            ->select(array_unique(
                array_merge($select, [
                    'time_intervals.id',
                    'projects.id as project_id',
                    'projects.name as project_name',
                    'tasks.id as task_id',
                    'tasks.task_name as task_name',
                    'users.id as user_id',
                    'users.full_name as full_name',
                    'time_intervals.start_at',
                ])
            ))
            ->where(fn($query) => $query->whereBetween('time_intervals.start_at', [$startAt, $endAt])
                ->orWhereBetween('time_intervals.end_at', [$startAt, $endAt]))
            ->whereNull('time_intervals.deleted_at')
            ->whereIn('users.id', $users)
            ->groupBy(['tasks.id', 'users.id', 'time_intervals.start_at'])
            ->orderBy('time_intervals.start_at');
    }

    /**
     * Calculate interval duration in period
     * @param CarbonPeriod $period
     * @param array $durationByDay
     * @return int
     */
    public static function getIntervalDurationInPeriod(CarbonPeriod $period, array $durationByDay): int
    {
        $durationInPeriod = 0;
        foreach ($durationByDay as $date => $duration) {
            $period->contains($date) && $durationInPeriod += $duration;
        }
        return $durationInPeriod;
    }

    /**
     * Splits interval by days on which it exists on. Considering timezone of a user if provided.
     * @param $interval
     * @param $companyTimezone
     * @param $userTimezone
     * @return array
     */
    public static function getIntervalDurationByDay($interval, $companyTimezone, $userTimezone = null): array
    {
        if ($userTimezone === null) {
            $userTimezone = $companyTimezone;
        }

        $startAt = Carbon::parse($interval->start_at)->shiftTimezone($companyTimezone)->setTimezone($userTimezone);
        $endAt = Carbon::parse($interval->end_at)->shiftTimezone($companyTimezone)->setTimezone($userTimezone);

        $startDate = $startAt->format(self::$dateFormat);
        $endDate = $endAt->format(self::$dateFormat);

        $durationByDay = [];
        if ($startDate === $endDate) {
            $durationByDay[$startDate] = $interval->duration;
        } else {
//              If interval spans over midnight, divide it at midnight
            $startOfDay = $endAt->copy()->startOfDay();
            $startDateDuration = $startOfDay->diffInSeconds($startAt);

            $durationByDay[$startDate] = $startDateDuration;

            $endDateDuration = $endAt->diffInSeconds($startOfDay);

            $durationByDay[$endDate] = $endDateDuration;
        }
        return $durationByDay;
    }
}
