middleware('auth'); } public function index(Request $request) { $clinicLeader = Auth::user()->name ?? 'Clinic Leader Dr.'; $clinicOptions = [ 'ALL' => 'ALL', 'MAM_A' => 'MAM_A', 'MAM_B' => 'MAM_B', 'MAM_C1' => 'MAM_C1', 'MAM_A+B+C1' => 'MAM_A+B+C1', 'MAM_SPT' => 'MAM_SPT', 'MAM_SDG' => 'MAM_SDG', 'MAM_TL' => 'MAM_TL', 'MAM_TBZY' => 'MAM_TBZY', ]; $requestedClinic = strtoupper(trim((string) $request->input('clinic', ''))); $isAggregateClinicScope = in_array($requestedClinic, ['ALL', 'MAM_A+B+C1'], true); $aggregateScopeClinic = $isAggregateClinicScope ? ($requestedClinic === '' ? 'ALL' : $requestedClinic) : null; $aggregateScopeLabel = $aggregateScopeClinic === 'ALL' ? 'ALL clinics' : $aggregateScopeClinic; $isAllClinics = $isAggregateClinicScope; $clinicConnection = $isAggregateClinicScope ? $aggregateScopeClinic : $this->resolveClinicConnection($requestedClinic); $clinicWarning = null; if (!$isAggregateClinicScope && !$this->connectionIsUsable($clinicConnection)) { $clinicWarning = "Clinic database '{$clinicConnection}' is not available. Please choose another clinic."; $clinicConnection = config('database.default'); } view()->share('clinicWarning', $clinicWarning); $registryConnection = $this->resolveRegistryConnection(); $modeProvided = $request->has('mode'); $currentYear = Carbon::today()->year; $uiQuarterYears = range($currentYear - 5, $currentYear + 1); view()->share('quarterCacheMeta', $isAggregateClinicScope ? [] : $this->buildQuarterCacheMeta($clinicConnection, $uiQuarterYears)); $selectedDateInput = $request->input('date'); $selectedDate = $selectedDateInput ? Carbon::parse($selectedDateInput)->toDateString() : Carbon::today()->toDateString(); $viewMode = $request->input('mode', null); $selectedYearInput = $request->input('year'); $selectedYear = $selectedYearInput ? (int) $selectedYearInput : Carbon::parse($selectedDate)->year; $useLive = $request->boolean('live', false); $internalCacheRefresh = $request->boolean('_cache_refresh', false); $persistYearly = $request->boolean('persist_yearly', false); $cacheUpdatedAt = null; view()->share('cacheUpdatedAt', $cacheUpdatedAt); $selectedMonthInput = $request->input('month'); $selectedQuarterInput = $request->input('quarter'); $selectedQuarter = null; $monthInputValue = null; $quarterStartMap = [1 => 1, 2 => 4, 3 => 7, 4 => 10]; // Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec $selectedMonthDate = $selectedMonthInput ? Carbon::parse($selectedMonthInput . '-01') : Carbon::parse($selectedDate)->startOfMonth(); $selectedMonth = $selectedMonthDate->format('Y-m'); $selectedMonthLabel = $selectedMonthDate->format('F Y'); $dateLabel = $selectedMonthLabel; $startOfMonth = $selectedMonthDate->copy()->startOfMonth(); $endOfMonth = $selectedMonthDate->copy()->endOfMonth(); $monthInputValue = $selectedMonthDate->format('Y-m'); if ($viewMode === 'quarterly') { if ($selectedQuarterInput && preg_match('/^(\\d{4})-Q([1-4])$/', $selectedQuarterInput, $m)) { $year = (int) $m[1]; $q = (int) $m[2]; $startMonth = $quarterStartMap[$q] ?? 1; $selectedMonthDate = Carbon::create($year, $startMonth, 1); $selectedMonth = $selectedQuarterInput; $selectedQuarter = $selectedQuarterInput; } else { $candidate = Carbon::parse($selectedDate); $monthNum = $candidate->month; $q = $monthNum <= 3 ? 1 : ($monthNum <= 6 ? 2 : ($monthNum <= 9 ? 3 : 4)); $year = $candidate->year; $startMonth = $quarterStartMap[$q]; $selectedMonthDate = Carbon::create($year, $startMonth, 1); $selectedMonth = sprintf('%d-Q%d', $year, $q); $selectedQuarter = $selectedMonth; } $endOfSpan = $selectedMonthDate->copy()->addMonths(3)->subDay(); // 3 months window $selectedMonthLabel = 'Q' . ($selectedQuarter ? explode('-Q', $selectedQuarter)[1] : $selectedMonthDate->quarter) . ' ' . $selectedMonthDate->year . ' (' . $selectedMonthDate->format('M') . '–' . $endOfSpan->format('M') . ')'; $dateLabel = $selectedMonthLabel; $startOfMonth = $selectedMonthDate->copy(); $endOfMonth = $endOfSpan; $monthInputValue = $selectedMonthDate->format('Y-m'); } if ($viewMode === 'yearly') { $selectedMonth = (string) $selectedYear; $selectedMonthLabel = 'Year ' . $selectedYear; $dateLabel = $selectedMonthLabel; $startOfMonth = Carbon::create($selectedYear, 1, 1); $endOfMonth = Carbon::create($selectedYear, 12, 31); $monthInputValue = $startOfMonth->format('Y-m'); } $periodMode = in_array($viewMode, ['monthly', 'quarterly', 'yearly'], true); $periodIsPartial = false; $periodThroughLabel = null; $periodFullEndLabel = null; $periodRemainingDays = null; if ($periodMode) { $today = Carbon::today(); $fullEndOfPeriod = $endOfMonth->copy(); if ($today->betweenIncluded($startOfMonth, $fullEndOfPeriod) && $today->lt($fullEndOfPeriod)) { $periodIsPartial = true; $periodThroughLabel = $today->toFormattedDateString(); $periodFullEndLabel = $fullEndOfPeriod->toFormattedDateString(); $periodRemainingDays = $today->diffInDays($fullEndOfPeriod); $endOfMonth = $today->copy(); $selectedMonthLabel = $selectedMonthLabel . ' • to ' . $today->format('M d'); $dateLabel = $selectedMonthLabel; } } view()->share('periodIsPartial', $periodIsPartial); view()->share('periodThroughLabel', $periodThroughLabel); view()->share('periodFullEndLabel', $periodFullEndLabel); view()->share('periodRemainingDays', $periodRemainingDays); $periodStartDateTime = $startOfMonth->copy()->startOfDay(); $periodEndDateTime = $endOfMonth->copy()->endOfDay(); $liveTimeInClinicDailyBands = ['labels' => [], 'datasets' => []]; if ($periodMode && !$isAllClinics) { $liveTimeInClinicDailyBands = $this->buildLiveTimeInClinicDailyBands( $clinicConnection, $periodStartDateTime, $periodEndDateTime ); } $periodKey = $viewMode === 'quarterly' ? ($selectedQuarter ?? $selectedMonth) : ($viewMode === 'yearly' ? (string) $selectedYear : $selectedMonth); $cachePeriodKey = $this->buildCacheKey($clinicConnection, $periodKey); $cacheExpiry = $periodIsPartial ? Carbon::now()->subHours(12) : Carbon::now()->subMonths(6); if ($viewMode === 'yearly' && !$isAllClinics) { $yearFromQuarters = $this->aggregateYearFromQuarterCaches($clinicConnection, (string) $selectedYear, $persistYearly); if ($yearFromQuarters) { view()->share('cacheUpdatedAt', now()->toDateTimeString()); return view('clinic_dashboard', array_merge([ 'clinicLeader' => $clinicLeader, 'clinicConnection' => $clinicConnection, 'clinicOptions' => $clinicOptions, 'viewMode' => $viewMode, 'selectedDate' => $selectedDate, 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'selectedQuarter' => $selectedQuarter, 'monthInputValue' => $monthInputValue, 'periodKey' => $periodKey, 'cachePeriodKey' => $cachePeriodKey, 'periodMode' => $periodMode, 'useLive' => $useLive, 'dailyChecks' => [], 'dailyGenderBreakdown' => [], 'dailyAgeSummary' => [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ], 'dailyKpBreakdown' => [], 'dailyDurationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'dailyUnplanned' => 0, 'dailyPlanned' => 0, 'mdStatsDaily' => collect(), 'appointmentsToday' => collect(), 'upcomingAppointments' => collect(), 'focusItems' => [], 'dailyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyClerkSummary' => [], 'dailyClerkHours' => ['labels' => [], 'series' => []], ], $yearFromQuarters)); } // Fall back to direct yearly calculation below if quarter caches are not available. } if ($isAggregateClinicScope && $periodMode) { if ($useLive) { return view('clinic_dashboard', [ 'clinicLeader' => $clinicLeader, 'clinicConnection' => $aggregateScopeClinic, 'clinicOptions' => $clinicOptions, 'viewMode' => $viewMode, 'selectedDate' => $selectedDate, 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'selectedQuarter' => $selectedQuarter, 'monthInputValue' => $monthInputValue, 'periodKey' => $periodKey, 'cachePeriodKey' => $cachePeriodKey, 'periodMode' => $periodMode, 'useLive' => $useLive, 'dailyChecks' => [], 'dailyGenderBreakdown' => [], 'dailyAgeSummary' => [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ], 'dailyKpBreakdown' => [], 'dailyDurationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'dailyUnplanned' => 0, 'dailyPlanned' => 0, 'mdStatsDaily' => collect(), 'appointmentsToday' => collect(), 'upcomingAppointments' => collect(), 'focusItems' => [], 'dailyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyClerkSummary' => [], 'dailyClerkHours' => ['labels' => [], 'series' => []], 'monthlyChecks' => [], 'periodKpBreakdown' => [], 'durationSummary' => [], 'unplan' => 0, 'planned' => 0, 'consultationSummary' => [], 'programCategories' => [], 'diseaseCategories' => [], 'monthlyAgeSummary' => [], 'monthlyHourlyLoad' => ['labels' => [], 'series' => []], 'monthlyClerkSummary' => [], 'monthlyClerkHours' => ['labels' => [], 'series' => []], 'monthlyMdTrend' => ['labels' => [], 'datasets' => []], 'mdStats' => [], 'message' => "Live mode does not use cached reports. For {$aggregateScopeLabel}, live aggregation is disabled to reduce CPU. Please choose a clinic or turn off Live.", ]); } $aggregated = $this->aggregatePeriodAcrossClinics( $this->resolveAggregateScopeClinics($aggregateScopeClinic, $clinicOptions), $viewMode, $periodKey, $periodStartDateTime, $periodEndDateTime, [ 'scope_clinic' => $aggregateScopeClinic, 'selectedDate' => $selectedDate, 'selectedMonth' => $selectedMonth, 'selectedQuarter' => $selectedQuarter, 'selectedYear' => $selectedYear, 'allow_current_period_cache' => $internalCacheRefresh, ] ); if ($aggregated) { view()->share('cacheUpdatedAt', now()->toDateTimeString()); return view('clinic_dashboard', array_merge([ 'clinicLeader' => $clinicLeader, 'clinicConnection' => $aggregateScopeClinic, 'clinicOptions' => $clinicOptions, 'viewMode' => $viewMode, 'selectedDate' => $selectedDate, 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'selectedQuarter' => $selectedQuarter, 'monthInputValue' => $monthInputValue, 'periodKey' => $periodKey, 'cachePeriodKey' => $cachePeriodKey, 'periodMode' => $periodMode, 'useLive' => $useLive, 'dailyChecks' => [], 'dailyGenderBreakdown' => [], 'dailyAgeSummary' => [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ], 'dailyKpBreakdown' => [], 'dailyDurationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'dailyUnplanned' => 0, 'dailyPlanned' => 0, 'mdStatsDaily' => collect(), 'appointmentsToday' => collect(), 'upcomingAppointments' => collect(), 'focusItems' => [], 'dailyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyClerkSummary' => [], 'dailyClerkHours' => ['labels' => [], 'series' => []], ], $aggregated)); } // No aggregate cache available; avoid live multi-clinic run to keep CPU low return view('clinic_dashboard', [ 'clinicLeader' => $clinicLeader, 'clinicConnection' => $aggregateScopeClinic, 'clinicOptions' => $clinicOptions, 'viewMode' => $viewMode, 'selectedDate' => $selectedDate, 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'selectedQuarter' => $selectedQuarter, 'monthInputValue' => $monthInputValue, 'periodKey' => $periodKey, 'cachePeriodKey' => $cachePeriodKey, 'periodMode' => $periodMode, 'useLive' => $useLive, 'dailyChecks' => [], 'dailyGenderBreakdown' => [], 'dailyAgeSummary' => [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ], 'dailyKpBreakdown' => [], 'dailyDurationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'dailyUnplanned' => 0, 'dailyPlanned' => 0, 'mdStatsDaily' => collect(), 'appointmentsToday' => collect(), 'upcomingAppointments' => collect(), 'focusItems' => [], 'dailyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyClerkSummary' => [], 'dailyClerkHours' => ['labels' => [], 'series' => []], 'monthlyChecks' => [], 'periodKpBreakdown' => [], 'durationSummary' => [], 'unplan' => 0, 'planned' => 0, 'consultationSummary' => [], 'programCategories' => [], 'diseaseCategories' => [], 'monthlyAgeSummary' => [], 'monthlyHourlyLoad' => ['labels' => [], 'series' => []], 'monthlyClerkSummary' => [], 'monthlyClerkHours' => ['labels' => [], 'series' => []], 'monthlyMdTrend' => ['labels' => [], 'datasets' => []], 'mdStats' => [], 'message' => 'Aggregate view uses cached clinic reports; open each clinic first to populate cache.', ]); } if ($periodMode && !$useLive) { $shouldRecalculateLive = $this->periodIncludesCurrentMonth($periodStartDateTime, $periodEndDateTime); $cached = ReportCache::where('period_type', $viewMode) ->where('period_key', $cachePeriodKey) ->first(); if ($cached && is_array($cached->data) && $cached->updated_at && Carbon::parse($cached->updated_at)->greaterThanOrEqualTo($cacheExpiry)) { $payload = $cached->data; if ($viewMode === 'yearly' && !in_array(($payload['yearlyStrategy'] ?? null), ['direct', 'quarter_sum'], true)) { $payload = null; } if ($payload !== null && !$this->canUseCachedPayloadForDisplay($payload, $clinicConnection, $viewMode, $periodKey, $periodStartDateTime, $periodEndDateTime)) { $payload = null; } if ($payload !== null) { $cacheUpdatedAt = $cached->updated_at ? $cached->updated_at->toDateTimeString() : null; view()->share('cacheUpdatedAt', $cacheUpdatedAt); return view('clinic_dashboard', array_merge([ 'clinicLeader' => $clinicLeader, 'clinicConnection' => $clinicConnection, 'clinicOptions' => $clinicOptions, 'viewMode' => $viewMode, 'selectedDate' => $selectedDate, 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'selectedQuarter' => $selectedQuarter, 'monthInputValue' => $monthInputValue, 'periodKey' => $periodKey, 'cachePeriodKey' => $cachePeriodKey, 'periodMode' => $periodMode, 'useLive' => $useLive, 'dailyChecks' => [], 'dailyGenderBreakdown' => [], 'dailyAgeSummary' => [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ], 'dailyKpBreakdown' => [], 'dailyDurationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'dailyUnplanned' => 0, 'dailyPlanned' => 0, 'mdStatsDaily' => collect(), 'appointmentsToday' => collect(), 'upcomingAppointments' => collect(), 'focusItems' => [], 'dailyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyClerkSummary' => [], 'dailyClerkHours' => ['labels' => [], 'series' => []], 'monthlyChecks' => [], 'periodKpBreakdown' => [], 'programCategories' => [], 'diseaseCategories' => [], 'monthlyAgeSummary' => [], 'monthlyHourlyLoad' => ['labels' => [], 'series' => []], 'monthlyClerkSummary' => [], 'monthlyClerkHours' => ['labels' => [], 'series' => []], ], $payload, [ 'timeInClinicDailyBands' => $liveTimeInClinicDailyBands, ])); } } if (!$shouldRecalculateLive && !$internalCacheRefresh) { $refreshParams = [ 'clinic' => $clinicConnection, 'mode' => $viewMode, 'date' => $periodStartDateTime->toDateString(), 'live' => 1, '_cache_refresh' => 1, ]; if ($viewMode === 'monthly') { $refreshParams['month'] = $periodKey; } elseif ($viewMode === 'quarterly') { $refreshParams['quarter'] = $periodKey; } elseif ($viewMode === 'yearly') { $refreshParams['year'] = (int) $periodKey; $refreshParams['persist_yearly'] = 0; } $this->index(Request::create('/clinic_dashboard', 'GET', $refreshParams)); $refreshed = ReportCache::where('period_type', $viewMode) ->where('period_key', $cachePeriodKey) ->orderByDesc('updated_at') ->first(); $refreshedPayload = ($refreshed && is_array($refreshed->data)) ? $refreshed->data : null; if ($refreshedPayload && $this->payloadCoversPeriod($refreshedPayload, $clinicConnection, $viewMode, $periodKey, $periodStartDateTime, $periodEndDateTime)) { $cacheUpdatedAt = $refreshed->updated_at ? $refreshed->updated_at->toDateTimeString() : null; view()->share('cacheUpdatedAt', $cacheUpdatedAt); return view('clinic_dashboard', array_merge([ 'clinicLeader' => $clinicLeader, 'clinicConnection' => $clinicConnection, 'clinicOptions' => $clinicOptions, 'viewMode' => $viewMode, 'selectedDate' => $selectedDate, 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'selectedQuarter' => $selectedQuarter, 'monthInputValue' => $monthInputValue, 'periodKey' => $periodKey, 'cachePeriodKey' => $cachePeriodKey, 'periodMode' => $periodMode, 'useLive' => $useLive, 'dailyChecks' => [], 'dailyGenderBreakdown' => [], 'dailyAgeSummary' => [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ], 'dailyKpBreakdown' => [], 'dailyDurationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'dailyUnplanned' => 0, 'dailyPlanned' => 0, 'mdStatsDaily' => collect(), 'appointmentsToday' => collect(), 'upcomingAppointments' => collect(), 'focusItems' => [], 'dailyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyClerkSummary' => [], 'dailyClerkHours' => ['labels' => [], 'series' => []], ], $refreshedPayload, [ 'timeInClinicDailyBands' => $liveTimeInClinicDailyBands, ])); } return view('clinic_dashboard', [ 'clinicLeader' => $clinicLeader, 'clinicConnection' => $clinicConnection, 'clinicOptions' => $clinicOptions, 'viewMode' => $viewMode, 'selectedDate' => $selectedDate, 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'selectedQuarter' => $selectedQuarter, 'monthInputValue' => $monthInputValue, 'periodKey' => $periodKey, 'cachePeriodKey' => $cachePeriodKey, 'periodMode' => $periodMode, 'useLive' => $useLive, 'dailyChecks' => [], 'dailyGenderBreakdown' => [], 'dailyAgeSummary' => [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ], 'dailyKpBreakdown' => [], 'dailyDurationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'dailyUnplanned' => 0, 'dailyPlanned' => 0, 'mdStatsDaily' => collect(), 'appointmentsToday' => collect(), 'upcomingAppointments' => collect(), 'focusItems' => [], 'dailyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyClerkSummary' => [], 'dailyClerkHours' => ['labels' => [], 'series' => []], 'monthlyChecks' => [], 'periodKpBreakdown' => [], 'programCategories' => [], 'diseaseCategories' => [], 'monthlyAgeSummary' => [], 'monthlyHourlyLoad' => ['labels' => [], 'series' => []], 'monthlyClerkSummary' => [], 'monthlyClerkHours' => ['labels' => [], 'series' => []], 'timeInClinicDailyBands' => $liveTimeInClinicDailyBands, 'message' => 'Generated report not found. It will be available after the scheduled cache generation runs.', ]); } } if (!$modeProvided) { if ($isAggregateClinicScope) { return view('clinic_dashboard', [ 'clinicLeader' => $clinicLeader, 'clinicConnection' => $aggregateScopeClinic, 'clinicOptions' => $clinicOptions, 'viewMode' => null, 'dailyChecks' => [], 'dailyGenderBreakdown' => [], 'dailyAgeSummary' => [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ], 'dailyKpBreakdown' => [], 'monthlyChecks' => [], 'dailyDurationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'durationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'mdStats' => collect(), 'mdStatsDaily' => collect(), 'monthlyMdTrend' => ['labels' => [], 'datasets' => []], 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'monthlyReport' => ['labels' => [], 'consultations' => [], 'followUps' => [], 'labs' => []], 'focusItems' => [], 'appointmentsToday' => collect(), 'upcomingAppointments' => collect(), 'unplan' => 0, 'planned' => 0, 'selectedDate' => $selectedDate, 'programCategories' => [], 'diseaseCategories' => [], 'monthlyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyClerkSummary' => [], 'monthlyClerkSummary' => [], 'dailyClerkHours' => ['labels' => [], 'series' => []], 'monthlyClerkHours' => ['labels' => [], 'series' => []], 'selectedQuarter' => null, 'monthInputValue' => $selectedMonth, 'periodKey' => null, 'cachePeriodKey' => null, 'periodMode' => false, 'message' => "Select monthly or quarterly to view {$aggregateScopeLabel} combined (uses cached clinic reports).", ]); } return view('clinic_dashboard', [ 'clinicLeader' => $clinicLeader, 'clinicConnection' => $clinicConnection, 'clinicOptions' => $clinicOptions, 'viewMode' => null, 'dailyChecks' => [], 'dailyGenderBreakdown' => [], 'dailyAgeSummary' => [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ], 'dailyKpBreakdown' => [], 'monthlyChecks' => [], 'dailyDurationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'durationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'mdStats' => collect(), 'mdStatsDaily' => collect(), 'monthlyMdTrend' => ['labels' => [], 'datasets' => []], 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'monthlyReport' => ['labels' => [], 'consultations' => [], 'followUps' => [], 'labs' => []], 'focusItems' => [], 'appointmentsToday' => collect(), 'upcomingAppointments' => collect(), 'unplan' => 0, 'planned' => 0, 'selectedDate' => $selectedDate, 'programCategories' => [], 'diseaseCategories' => [], 'monthlyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyClerkSummary' => [], 'monthlyClerkSummary' => [], 'dailyClerkHours' => ['labels' => [], 'series' => []], 'monthlyClerkHours' => ['labels' => [], 'series' => []], 'selectedQuarter' => null, 'monthInputValue' => $selectedMonth, 'periodKey' => null, 'cachePeriodKey' => null, 'periodMode' => false, ]); } if ($isAggregateClinicScope && !$periodMode) { return view('clinic_dashboard', [ 'clinicLeader' => $clinicLeader, 'clinicConnection' => $aggregateScopeClinic, 'clinicOptions' => $clinicOptions, 'viewMode' => $viewMode, 'selectedDate' => $selectedDate, 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'selectedQuarter' => $selectedQuarter, 'monthInputValue' => $monthInputValue, 'periodKey' => $periodKey, 'cachePeriodKey' => $cachePeriodKey, 'periodMode' => $periodMode, 'useLive' => $useLive, 'dailyChecks' => [], 'dailyGenderBreakdown' => [], 'dailyAgeSummary' => [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ], 'dailyKpBreakdown' => [], 'monthlyChecks' => [], 'dailyDurationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'durationSummary' => [ 'average' => 0, 'median' => 0, 'p90' => 0, 'longWaiters' => 0, 'longWaitRate' => 0, 'dateLabel' => '', ], 'mdStats' => collect(), 'mdStatsDaily' => collect(), 'monthlyMdTrend' => ['labels' => [], 'datasets' => []], 'monthlyReport' => ['labels' => [], 'consultations' => [], 'followUps' => [], 'labs' => []], 'focusItems' => [], 'appointmentsToday' => collect(), 'upcomingAppointments' => collect(), 'unplan' => 0, 'planned' => 0, 'programCategories' => [], 'diseaseCategories' => [], 'monthlyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyHourlyLoad' => ['labels' => [], 'series' => []], 'dailyClerkSummary' => [], 'monthlyClerkSummary' => [], 'dailyClerkHours' => ['labels' => [], 'series' => []], 'monthlyClerkHours' => ['labels' => [], 'series' => []], 'message' => 'All clinics view is available only for monthly/quarterly cached periods. Please choose a clinic.', ]); } $receptionBase = Followup_general::on($clinicConnection)->whereBetween('Visit Date', [$startOfMonth->toDateString(), $endOfMonth->toDateString()]); $totalCheckIns = (clone $receptionBase)->count(); $receptionPids = (clone $receptionBase)->pluck('Pid')->filter()->unique(); $dailyBase = Followup_general::on($clinicConnection)->whereDate('Visit Date', $selectedDate); $dailyTotal = (clone $dailyBase)->count(); $dailyPids = (clone $dailyBase)->pluck('Pid')->filter()->unique(); $newOldGroups = (clone $receptionBase) ->select('New_Old', DB::raw('count(*) as total')) ->groupBy('New_Old') ->get(); $newCount = $newOldGroups ->whereIn('New_Old', ['New', 'NEW', 'new', '1', 1, 'N']) ->sum('total'); $returningCount = $newOldGroups ->whereIn('New_Old', ['Old', 'OLD', 'old', '0', 0, 'R']) ->sum('total'); if ($newCount === 0 && $returningCount === 0) { $returningCount = $newOldGroups->sum('total'); } $dailyNewOld = (clone $dailyBase) ->select('New_Old', DB::raw('count(*) as total')) ->groupBy('New_Old') ->get(); $newCountDaily = $dailyNewOld ->whereIn('New_Old', ['New', 'NEW', 'new', '1', 1, 'N']) ->sum('total'); $returningCountDaily = $dailyNewOld ->whereIn('New_Old', ['Old', 'OLD', 'old', '0', 0, 'R']) ->sum('total'); if ($newCountDaily === 0 && $returningCountDaily === 0) { $returningCountDaily = $dailyNewOld->sum('total'); } $registryNewPids = collect(); $registryNewPids = $registryNewPids->merge( Patients::on($clinicConnection)->whereIn('Pid', $receptionPids) ->whereBetween('created_at', [$periodStartDateTime, $periodEndDateTime]) ->pluck('Pid') ); $registryNewPids = $registryNewPids->merge( PtConfig::on($registryConnection)->whereIn('Pid', $receptionPids) ->whereBetween(DB::raw("DATE(`Reg Date`)"), [$startOfMonth->toDateString(), $endOfMonth->toDateString()]) ->pluck('Pid') ); $registryNewPids = $registryNewPids->merge( Patients::on($clinicConnection)->whereIn('Pid', $receptionPids) ->whereBetween(DB::raw("DATE(`Reg Date`)"), [$startOfMonth->toDateString(), $endOfMonth->toDateString()]) ->pluck('Pid') )->filter()->unique(); if ($registryNewPids->isNotEmpty()) { $registryNewVisits = (clone $receptionBase)->whereIn('Pid', $registryNewPids)->count(); if ($registryNewVisits > 0) { $newCount = $registryNewVisits; $returningCount = max($totalCheckIns - $newCount, 0); } } $dailyRegistryNewPids = collect(); $dailyRegistryNewPids = $dailyRegistryNewPids->merge( Patients::on($clinicConnection)->whereIn('Pid', $dailyPids) ->whereDate('created_at', $selectedDate) ->pluck('Pid') ); $dailyRegistryNewPids = $dailyRegistryNewPids->merge( PtConfig::on($registryConnection)->whereIn('Pid', $dailyPids) ->whereDate(DB::raw("DATE(`Reg Date`)"), $selectedDate) ->pluck('Pid') ); $dailyRegistryNewPids = $dailyRegistryNewPids->merge( Patients::on($clinicConnection)->whereIn('Pid', $dailyPids) ->whereDate(DB::raw("DATE(`Reg Date`)"), $selectedDate) ->pluck('Pid') )->filter()->unique(); if ($dailyRegistryNewPids->isNotEmpty()) { $dailyRegistryNewVisits = (clone $dailyBase)->whereIn('Pid', $dailyRegistryNewPids)->count(); if ($dailyRegistryNewVisits > 0) { $newCountDaily = $dailyRegistryNewVisits; $returningCountDaily = max($dailyTotal - $newCountDaily, 0); } } // Pull patient demographics from the selected clinic DB $patientDemographics = Patients::on($clinicConnection) ->whereIn('Pid', $receptionPids) ->select( 'Pid', DB::raw('`Date Of Birth` as dob'), DB::raw('`Reg Date` as reg_date'), 'Agey', 'Agem', 'Gender' ) ->get() ->keyBy('Pid'); $visitRecords = (clone $receptionBase)->get(['Pid', 'Visit Date', 'created_at']); $pids = $visitRecords->pluck('Pid')->filter()->unique(); $dailyVisitRecords = (clone $dailyBase)->get(['Pid', 'Visit Date', 'created_at']); $dailyPidsList = $dailyVisitRecords->pluck('Pid')->filter()->unique(); $dailyGenderBreakdown = []; try { if ($clinicConnection !== 'ALL' && $dailyPidsList->isNotEmpty()) { $dailyGenderByPid = Patients::on($clinicConnection) ->whereIn('Pid', $dailyPidsList) ->pluck('Gender', 'Pid'); $genderCounts = []; foreach ($dailyPidsList as $pid) { $rawGender = $dailyGenderByPid[$pid] ?? null; $decrypted = $this->decryptGeneralValue($rawGender); $label = $this->normalizeGender($decrypted ?? $rawGender); $genderCounts[$label] = ($genderCounts[$label] ?? 0) + 1; } foreach (['Male', 'Female'] as $label) { $count = (int) ($genderCounts[$label] ?? 0); if ($count > 0) { $dailyGenderBreakdown[] = ['title' => $label, 'value' => $count]; } } } } catch (\Throwable $e) { Log::warning('Daily gender breakdown unavailable', ['error' => $e->getMessage()]); } $dailyAgeSummary = [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ]; try { if ($clinicConnection !== 'ALL') { $dailyAgeRecords = (clone $dailyBase) ->orderBy('created_at') ->get(['Pid', 'Visit Date', 'New_Old', 'Agey', 'Agem', 'created_at']); $dailyNewFlagLookup = []; foreach ($dailyAgeRecords as $record) { if (!$record->Pid) { continue; } $isNewFlag = in_array($record->New_Old, ['New', 'NEW', 'new', '1', 1, 'N'], true); if (!isset($dailyNewFlagLookup[$record->Pid])) { $dailyNewFlagLookup[$record->Pid] = $isNewFlag; } elseif ($isNewFlag) { $dailyNewFlagLookup[$record->Pid] = true; } } $dailyMonthStart = Carbon::parse($selectedDate)->startOfMonth(); $dailyMonthEnd = Carbon::parse($selectedDate)->endOfMonth(); foreach ($dailyAgeRecords as $rec) { if (!$rec->Pid) { continue; } $patientRow = $patientDemographics->get($rec->Pid); $agey = $patientRow->Agey ?? $rec->Agey ?? 13; if ($patientRow && !empty($patientRow->dob)) { try { $dob = Carbon::parse($patientRow->dob); $visitDate = Carbon::parse($rec->{'Visit Date'}); $agey = $visitDate->diffInYears($dob); } catch (\Throwable $e) { // fallback to Agey } } $isUnder12 = intval($agey) < 12; $isNew = null; $regDateRaw = $patientRow->reg_date ?? null; if (!empty($regDateRaw)) { try { $parsedReg = Carbon::parse($regDateRaw); $isNew = $parsedReg->betweenIncluded($dailyMonthStart, $dailyMonthEnd); } catch (\Throwable $e) { $isNew = null; } } if ($isNew === null) { $isNew = in_array($rec->New_Old, ['New', 'NEW', 'new', '1', 1, 'N'], true); if (!$isNew) { $isNew = $dailyNewFlagLookup[$rec->Pid] ?? false; } } if ($isUnder12 && $isNew) { $dailyAgeSummary['u12_new']++; } elseif ($isUnder12) { $dailyAgeSummary['u12_old']++; } elseif ($isNew) { $dailyAgeSummary['o12_new']++; } else { $dailyAgeSummary['o12_old']++; } } } } catch (\Throwable $e) { Log::warning('Daily age summary unavailable', ['error' => $e->getMessage()]); $dailyAgeSummary = [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ]; } $dailyKpBreakdown = []; try { if ($clinicConnection !== 'ALL' && $dailyVisitRecords->isNotEmpty()) { $dailyVisitPids = $dailyVisitRecords->pluck('Pid')->filter()->unique(); $dailyRiskByPid = Patients::on($clinicConnection) ->whereIn('Pid', $dailyVisitPids) ->pluck('Main Risk', 'Pid'); $counts = []; foreach ($dailyVisitRecords as $visit) { $pid = $visit->Pid; if (!$pid) { continue; } $rawRisk = $dailyRiskByPid[$pid] ?? null; $decrypted = $this->decryptGeneralValue($rawRisk); $category = $this->normalizeRiskCategory($decrypted ?? $rawRisk); $counts[$category] = ($counts[$category] ?? 0) + 1; } $order = [ 'FSW', 'Client of FSW', 'MSM', 'PWUD', 'PWID', 'TG', 'Partner of KP', 'Partner of PLHIV', 'Special Groups', 'Migrant Population', 'General patients', ]; foreach ($order as $label) { $val = (int) ($counts[$label] ?? 0); if ($val > 0) { $dailyKpBreakdown[] = ['title' => $label, 'value' => $val]; } } } } catch (\Throwable $e) { Log::warning('Daily KP breakdown unavailable', ['error' => $e->getMessage()]); $dailyKpBreakdown = []; } $periodKpBreakdown = []; try { if ($clinicConnection !== 'ALL' && $visitRecords->isNotEmpty()) { $visitPids = $visitRecords->pluck('Pid')->filter()->unique(); $riskByPid = Patients::on($clinicConnection) ->whereIn('Pid', $visitPids) ->pluck('Main Risk', 'Pid'); $counts = []; foreach ($visitRecords as $visit) { $pid = $visit->Pid; if (!$pid) { continue; } $rawRisk = $riskByPid[$pid] ?? null; $decrypted = $this->decryptGeneralValue($rawRisk); $category = $this->normalizeRiskCategory($decrypted ?? $rawRisk); $counts[$category] = ($counts[$category] ?? 0) + 1; } $order = [ 'FSW', 'Client of FSW', 'MSM', 'PWUD', 'PWID', 'TG', 'Partner of KP', 'Partner of PLHIV', 'Special Groups', 'Migrant Population', 'General patients', ]; foreach ($order as $label) { $val = (int) ($counts[$label] ?? 0); if ($val > 0) { $periodKpBreakdown[] = ['title' => $label, 'value' => $val]; } } } } catch (\Throwable $e) { Log::warning('Period KP breakdown unavailable', ['error' => $e->getMessage()]); $periodKpBreakdown = []; } $periodGenderBreakdown = []; try { if ($clinicConnection !== 'ALL' && $receptionPids->isNotEmpty()) { $genderByPid = Patients::on($clinicConnection) ->whereIn('Pid', $receptionPids) ->pluck('Gender', 'Pid'); $counts = []; foreach ($receptionPids as $pid) { $rawGender = $genderByPid[$pid] ?? null; $decrypted = $this->decryptGeneralValue($rawGender); $label = $this->normalizeGender($decrypted ?? $rawGender); $counts[$label] = ($counts[$label] ?? 0) + 1; } foreach (['Male', 'Female'] as $label) { $count = (int) ($counts[$label] ?? 0); if ($count > 0) { $periodGenderBreakdown[] = ['title' => $label, 'value' => $count]; } } } } catch (\Throwable $e) { Log::warning('Period gender breakdown unavailable', ['error' => $e->getMessage()]); $periodGenderBreakdown = []; } $dailyProgramWorkloads = []; try { if ($clinicConnection !== 'ALL') { $countVisitsOnDay = function (string $table, string $idColumn, string $dateColumn) use ($clinicConnection, $selectedDate): int { return (int) DB::connection($clinicConnection) ->table($table) ->whereNotNull($idColumn) ->whereDate($dateColumn, $selectedDate) ->count($idColumn); }; $countProgram = function (array $sources) use ($countVisitsOnDay): int { $sum = 0; foreach ($sources as $source) { [$table, $idColumn, $dateColumn] = $source; try { $sum += $countVisitsOnDay($table, $idColumn, $dateColumn); } catch (\Throwable $e) { // ignore missing tables/columns in some clinic DBs } } return $sum; }; $countProgramFirstAvailable = function (array $sources) use ($countVisitsOnDay): int { foreach ($sources as $source) { [$table, $idColumn, $dateColumn] = $source; try { return $countVisitsOnDay($table, $idColumn, $dateColumn); } catch (\Throwable $e) { // try next source } } return 0; }; $dailyProgramWorkloads = [ ['title' => 'NCD', 'value' => $countProgram([['ncd_followups', 'Pid', 'Visit_date']])], ['title' => 'STI', 'value' => $countProgram([ ['stimales', 'CID', 'Visit_date'], ['stifemales', 'CID', 'Visit_date'], ])], ['title' => 'HTS', 'value' => $countProgram([['coulsellings', 'Pid', 'Counselling_Date']])], ['title' => 'ANC', 'value' => $countProgram([['anc_follow_ups', 'Pid', 'Visitdate']])], ['title' => 'Feeding Center Follow-ups', 'value' => $countProgram([['feeding_centerfups', 'Pid', 'Visitdate']])], ['title' => 'Cervical Cancer', 'value' => $countProgramFirstAvailable([ ['cervicalcancer1s', 'General ID', 'Visit_date'], ['cervicalcancers', 'General ID', 'Visit_date'], ])], ['title' => 'CMV', 'value' => $countProgram([['cmvs', 'Pid_cmv', 'Visit_date']])], ['title' => 'Mental Health Screening', 'value' => $countProgram([['mental__healths', 'Pid', 'Counselling_Date']])], ['title' => 'Prevention Logsheet', 'value' => $countProgram([['prevention_logsheets', 'Pid', 'Visit_Date']])], ['title' => 'Prevention CBS', 'value' => $countProgram([['prevention_c_b_s', 'Pid', 'Visit_Date']])], ['title' => 'PreTB', 'value' => $countProgramFirstAvailable([ ['pre_tb_records', 'cid', 'date_of_screening'], ['pre_t_b_s', 'Pid_preTB', 'TBscreenDate_preTB'], ])], ['title' => 'TB03', 'value' => $countProgram([['tb_register_o3_s', 'Pid_TB03', 'TreDate_TB03']])], ['title' => 'TB IPT', 'value' => $countProgram([['tbipts', 'Pid_iptTB', 'IPT_regDate']])], ]; $dailyProgramWorkloads = array_values(array_filter($dailyProgramWorkloads, fn ($row) => (int) ($row['value'] ?? 0) > 0)); } } catch (\Throwable $e) { Log::warning('Daily program workloads unavailable', ['error' => $e->getMessage()]); $dailyProgramWorkloads = []; } $dailyDiseaseCategories = []; try { if ($clinicConnection !== 'ALL') { $consultationService = app(ConsultationReportService::class); $dailyConsultData = $consultationService->calculate( $selectedDate, $selectedDate, $clinicConnection, $registryConnection ); $sumFields = function (array $keys) use ($dailyConsultData) { return collect($keys)->map(fn ($k) => $dailyConsultData[$k] ?? 0)->sum(); }; $dailyDiseaseCategories = [ ['title' => 'FUO', 'value' => $sumFields(['ugen_fuo_new', 'ogen_fuo_new', 'ugen_fuo_old', 'ogen_fuo_old'])], ['title' => 'Diarrhea', 'value' => $sumFields(['diarrhoea_u12_new', 'diarrhoea_o12_new', 'diarrhoea_u12_old', 'diarrhoea_o12_old'])], ['title' => 'Dengue Fever', 'value' => $sumFields(['ugen_dengue_fever_new', 'ogen_dengue_fever_new', 'ugen_dengue_fever_old', 'ogen_dengue_fever_old'])], ['title' => 'URTI-1 (Covid Suspect)', 'value' => $sumFields(['ugen_Covid_relate_new', 'ogen_Covid_relate_new', 'ugen_Covid_relate_old', 'ogen_Covid_relate_old'])], ['title' => 'URTI-2 (Others)', 'value' => $sumFields(['urti2_other_u12_new', 'urti2_other_o12_new', 'urti2_other_u12_old', 'urti2_other_o12_old'])], ['title' => 'LRTI-1 (Pneumonia)', 'value' => $sumFields(['lrti1_pneumonia_u12_new', 'lrti1_pneumonia_o12_new', 'lrti1_pneumonia_u12_old', 'lrti1_pneumonia_o12_old'])], ['title' => 'LRTI-2 (TB Suspect)', 'value' => $sumFields(['lrti2_TBsuspect_u12_new', 'lrti2_TBsuspect_o12_new', 'lrti2_TBsuspect_u12_old', 'lrti2_TBsuspect_o12_old'])], ['title' => 'LRTI-3 (Bronchiolitis & Others)', 'value' => $sumFields(['lrti3_Bronchi_u12_new', 'lrti3_Bronchi_o12_new', 'lrti3_Bronchi_u12_old', 'lrti3_Bronchi_o12_old'])], ['title' => 'COPD', 'value' => $sumFields(['copd_u12_new', 'copd_o12_new', 'copd_u12_old', 'copd_o12_old'])], ['title' => 'Trauma', 'value' => $sumFields(['ugen_trauma_new', 'ogen_trauma_new', 'ugen_trauma_old', 'ogen_trauma_old'])], ['title' => 'Gynaecological diseases', 'value' => $sumFields(['ugen_Gynaecology_new', 'ogen_Gynaecology_new', 'ugen_Gynaecology_old', 'ogen_Gynaecology_old'])], ['title' => 'Breast Diseases', 'value' => $sumFields(['ugen_skin_infect_new', 'ogen_skin_infect_new', 'ugen_skin_infect_old', 'ogen_skin_infect_old'])], ['title' => 'Mental illness', 'value' => $sumFields(['ugen_mentalill_new', 'ogen_mentalill_new', 'ugen_mentalill_old', 'ogen_mentalill_old'])], ['title' => 'Reproductive Tract infection/STI', 'value' => $sumFields(['ugen_STI_new', 'ogen_STI_new', 'ugen_STI_old', 'ogen_STI_old'])], ['title' => 'Malnourished', 'value' => $sumFields(['ugen_malnourish_new', 'ogen_malnourish_new', 'ugen_malnourish_old', 'ogen_malnourish_old'])], ['title' => 'Child abuse / sexual abuse', 'value' => $sumFields(['ugen_child_abuse_new', 'ogen_child_abuse_new', 'ugen_child_abuse_old', 'ogen_child_abuse_old'])], ['title' => 'Others', 'value' => $sumFields(['ugen_others_new', 'ogen_others_new', 'ugen_others_old', 'ogen_others_old'])], ]; $dailyDiseaseCategories = array_values(array_filter($dailyDiseaseCategories, fn ($row) => (int) ($row['value'] ?? 0) > 0)); $dailyDiseaseSum = collect($dailyDiseaseCategories)->sum(fn ($row) => (int) ($row['value'] ?? 0)); $dailyDiseaseMissing = max(((int) $dailyTotal) - (int) $dailyDiseaseSum, 0); if ($dailyDiseaseMissing > 0) { $dailyDiseaseCategories[] = ['title' => 'Unclassified', 'value' => $dailyDiseaseMissing]; } } } catch (\Throwable $e) { Log::warning('Daily disease breakdown unavailable', ['error' => $e->getMessage()]); $dailyDiseaseCategories = []; } $counsellorTimes = CounsellorRecords::on($clinicConnection)->whereBetween('Counselling_Date', [$startOfMonth->toDateString(), $endOfMonth->toDateString()]) ->whereIn('Pid', $pids) ->get(['Pid', 'created_at']); $dailyCounsellorTimes = CounsellorRecords::on($clinicConnection)->whereDate('Counselling_Date', $selectedDate) ->whereIn('Pid', $dailyPidsList) ->get(['Pid', 'created_at']); $dispenseTimes = Consumption::on($clinicConnection)->whereBetween('Given_Date', [$startOfMonth->toDateString(), $endOfMonth->toDateString()]) ->whereIn('Pid', $pids) ->get(['Pid', 'created_at']); $dailyDispenseTimes = Consumption::on($clinicConnection)->whereDate('Given_Date', $selectedDate) ->whereIn('Pid', $dailyPidsList) ->get(['Pid', 'created_at']); $labModels = [ ['model' => Lab::class, 'pidColumn' => 'CID'], ['model' => Rprtest::class, 'pidColumn' => 'pid'], ['model' => LabGeneralTest::class, 'pidColumn' => 'CID'], ['model' => LabHbcTest::class, 'pidColumn' => 'CID'], ['model' => Urine::class, 'pidColumn' => 'CID'], ['model' => Labstitest::class, 'pidColumn' => 'CID'], ['model' => Lab_oi::class, 'pidColumn' => 'CID'], ]; $labTouches = collect(); foreach ($labModels as $lm) { $records = ($lm['model'])::on($clinicConnection)->whereBetween('created_at', [$periodStartDateTime, $periodEndDateTime]) ->whereIn($lm['pidColumn'], $pids) ->get([$lm['pidColumn'], 'created_at']); foreach ($records as $row) { $pidVal = $row->{$lm['pidColumn']}; if ($pidVal) { $labTouches->push([ 'Pid' => $pidVal, 'created_at' => $row->created_at, ]); } } } $labPatientCount = null; $dailyLabTouches = collect(); foreach ($labModels as $lm) { $records = ($lm['model'])::on($clinicConnection)->whereDate('created_at', $selectedDate) ->whereIn($lm['pidColumn'], $dailyPidsList) ->get([$lm['pidColumn'], 'created_at']); foreach ($records as $row) { $pidVal = $row->{$lm['pidColumn']}; if ($pidVal) { $dailyLabTouches->push([ 'Pid' => $pidVal, 'created_at' => $row->created_at, ]); } } } $dailyLabPatientCount = null; $countVisitTouches = function ($rows): int { $keys = []; foreach ($rows as $row) { $pid = is_array($row) ? ($row['Pid'] ?? null) : ($row->Pid ?? null); $createdAt = is_array($row) ? ($row['created_at'] ?? null) : ($row->created_at ?? null); if (!$pid || !$createdAt) { continue; } $dateKey = Carbon::parse($createdAt)->toDateString(); $keys[$pid . '|' . $dateKey] = true; } return count($keys); }; $labPatientCount = $countVisitTouches($labTouches); $dailyLabPatientCount = $countVisitTouches($dailyLabTouches); $counsellorVisitCount = $countVisitTouches($counsellorTimes); $dispenseVisitCount = $countVisitTouches($dispenseTimes); $dailyCounsellorVisitCount = $countVisitTouches($dailyCounsellorTimes); $dailyDispenseVisitCount = $countVisitTouches($dailyDispenseTimes); $latestTouch = []; foreach ($counsellorTimes as $row) { if (!$row->Pid || !$row->created_at) { continue; } $ts = Carbon::parse($row->created_at); $dateKey = $ts->toDateString(); $key = $row->Pid . '|' . $dateKey; if (!isset($latestTouch[$key]) || $ts->gt($latestTouch[$key])) { $latestTouch[$key] = $ts; } } foreach ($dispenseTimes as $row) { if (!$row->Pid || !$row->created_at) { continue; } $ts = Carbon::parse($row->created_at); $dateKey = $ts->toDateString(); $key = $row->Pid . '|' . $dateKey; if (!isset($latestTouch[$key]) || $ts->gt($latestTouch[$key])) { $latestTouch[$key] = $ts; } } foreach ($labTouches as $row) { $pid = $row['Pid'] ?? null; $createdAt = $row['created_at'] ?? null; if (!$pid || !$createdAt) { continue; } $ts = Carbon::parse($createdAt); $dateKey = $ts->toDateString(); $key = $pid . '|' . $dateKey; if (!isset($latestTouch[$key]) || $ts->gt($latestTouch[$key])) { $latestTouch[$key] = $ts; } } $durations = []; foreach ($visitRecords as $visit) { if (!$visit->Pid) { continue; } $start = $visit->created_at ? Carbon::parse($visit->created_at) : $startOfMonth; $visitDate = $visit->{'Visit Date'} ? Carbon::parse($visit->{'Visit Date'})->toDateString() : $start->toDateString(); $key = $visit->Pid . '|' . $visitDate; $end = $latestTouch[$key] ?? $start; $durations[] = $start->diffInMinutes($end); } $dailyLatestTouch = []; foreach ($dailyCounsellorTimes as $row) { if (!$row->Pid || !$row->created_at) { continue; } $ts = Carbon::parse($row->created_at); $dateKey = $ts->toDateString(); $key = $row->Pid . '|' . $dateKey; if (!isset($dailyLatestTouch[$key]) || $ts->gt($dailyLatestTouch[$key])) { $dailyLatestTouch[$key] = $ts; } } foreach ($dailyDispenseTimes as $row) { if (!$row->Pid || !$row->created_at) { continue; } $ts = Carbon::parse($row->created_at); $dateKey = $ts->toDateString(); $key = $row->Pid . '|' . $dateKey; if (!isset($dailyLatestTouch[$key]) || $ts->gt($dailyLatestTouch[$key])) { $dailyLatestTouch[$key] = $ts; } } foreach ($dailyLabTouches as $row) { $pid = $row['Pid'] ?? null; $createdAt = $row['created_at'] ?? null; if (!$pid || !$createdAt) { continue; } $ts = Carbon::parse($createdAt); $dateKey = $ts->toDateString(); $key = $pid . '|' . $dateKey; if (!isset($dailyLatestTouch[$key]) || $ts->gt($dailyLatestTouch[$key])) { $dailyLatestTouch[$key] = $ts; } } $dailyDurations = []; foreach ($dailyVisitRecords as $visit) { if (!$visit->Pid) { continue; } $start = $visit->created_at ? Carbon::parse($visit->created_at) : Carbon::parse($selectedDate); $visitDate = $visit->{'Visit Date'} ? Carbon::parse($visit->{'Visit Date'})->toDateString() : $start->toDateString(); $key = $visit->Pid . '|' . $visitDate; $end = $dailyLatestTouch[$key] ?? $start; $dailyDurations[] = $start->diffInMinutes($end); } $durationCollection = collect($durations); $averageStay = (int) round($durationCollection->avg() ?? 0); $medianStay = (int) round($durationCollection->median() ?? 0); $p90Stay = 0; if ($durationCollection->count() > 0) { $sorted = $durationCollection->sort()->values(); $index = (int) ceil($sorted->count() * 0.9) - 1; $p90Stay = $sorted->get(max($index, 0)); } $longWaiters = $durationCollection->filter(fn ($m) => $m > 90)->count(); $longWaitRate = $totalCheckIns > 0 ? round(($longWaiters / $totalCheckIns) * 100) : 0; $dailyDurationCollection = collect($dailyDurations); $dailyAverageStay = (int) round($dailyDurationCollection->avg() ?? 0); $dailyMedianStay = (int) round($dailyDurationCollection->median() ?? 0); $dailyP90Stay = 0; if ($dailyDurationCollection->count() > 0) { $sorted = $dailyDurationCollection->sort()->values(); $index = (int) ceil($sorted->count() * 0.9) - 1; $dailyP90Stay = $sorted->get(max($index, 0)); } $dailyLongWaiters = $dailyDurationCollection->filter(fn ($m) => $m > 90)->count(); $dailyLongWaitRate = $dailyTotal > 0 ? round(($dailyLongWaiters / $dailyTotal) * 100) : 0; // Always use live period data for Time-in-clinic charts, even when other cards come from cache. $timeInClinicDailyBands = $liveTimeInClinicDailyBands; if (empty($timeInClinicDailyBands['labels'])) { $timeInClinicDailyBands = $this->buildLiveTimeInClinicDailyBands( $clinicConnection, $periodStartDateTime, $periodEndDateTime ); } $mdStats = (clone $receptionBase) ->select('Current_MD', DB::raw('count(*) as total')) ->groupBy('Current_MD') ->orderByDesc('total') ->get(); $mdStatsDaily = (clone $dailyBase) ->select('Current_MD', DB::raw('count(*) as total')) ->groupBy('Current_MD') ->orderByDesc('total') ->get(); $unplanCounts = (clone $receptionBase) ->select('Unplan', DB::raw('count(*) as total')) ->groupBy('Unplan') ->get() ->keyBy('Unplan'); $unplan = $unplanCounts->get('1')->total ?? 0; $planned = max($totalCheckIns - $unplan, 0); $dailyUnplanned = $dailyBase->where('Unplan', 1)->count(); $dailyPlanned = max($dailyTotal - $dailyUnplanned, 0); $monthlyChecks = [ [ 'title' => 'Total check-ins', 'value' => $totalCheckIns, 'change' => $dateLabel, 'accent' => '#0ea5e9', 'note' => 'Total reception visits', ], [ 'title' => 'New patients', 'value' => $newCount, 'change' => "Returning: {$returningCount}", 'accent' => '#22c55e', 'note' => 'Based on New/Old flag', ], [ 'title' => 'Reached lab', 'value' => $labPatientCount, 'change' => 'Any lab module', 'accent' => '#8b5cf6', 'note' => 'labs, rprtests, general, hep, urine, STI, OI', ], [ 'title' => 'Reached counsellor', 'value' => $counsellorVisitCount, 'change' => 'Same-day counselling', 'accent' => '#6366f1', 'note' => 'counsellor_records', ], [ 'title' => 'Reached dispensing', 'value' => $dispenseVisitCount, 'change' => 'Medication given', 'accent' => '#f59e0b', 'note' => 'consumptions', ], [ 'title' => 'Unplanned visits', 'value' => $unplan, 'change' => "Planned: {$planned}", 'accent' => '#ef4444', 'note' => 'Unplan=1 in followup_generals', ], ]; $durationSummary = [ 'average' => $averageStay, 'median' => $medianStay, 'p90' => $p90Stay, 'longWaiters' => $longWaiters, 'longWaitRate' => $longWaitRate, 'dateLabel' => $dateLabel, ]; $dailyUnplanned = (clone $dailyBase)->where('Unplan', 1)->count(); $dailyChecks = [ [ 'title' => 'Total check-ins', 'value' => $dailyTotal, 'change' => Carbon::parse($selectedDate)->toFormattedDateString(), 'accent' => '#0ea5e9', 'note' => 'Total reception visits', ], [ 'title' => 'New patients', 'value' => $newCountDaily, 'change' => "Returning: {$returningCountDaily}", 'accent' => '#22c55e', 'note' => 'Based on New/Old flag or registry', ], [ 'title' => 'Reached lab', 'value' => $dailyLabPatientCount, 'change' => 'Any lab module', 'accent' => '#8b5cf6', 'note' => 'labs, rprtests, general, hep, urine, STI, OI', ], [ 'title' => 'Reached counsellor', 'value' => $dailyCounsellorVisitCount, 'change' => 'Same-day counselling', 'accent' => '#6366f1', 'note' => 'counsellor_records', ], [ 'title' => 'Reached dispensing', 'value' => $dailyDispenseVisitCount, 'change' => 'Medication given', 'accent' => '#f59e0b', 'note' => 'consumptions', ], [ 'title' => 'Unplanned visits', 'value' => $dailyUnplanned, 'change' => "Planned: " . max($dailyTotal - $dailyUnplanned, 0), 'accent' => '#ef4444', 'note' => 'Unplan=1 in followup_generals', ], ]; $dailyDurationSummary = [ 'average' => $dailyAverageStay, 'median' => $dailyMedianStay, 'p90' => $dailyP90Stay, 'longWaiters' => $dailyLongWaiters, 'longWaitRate' => $dailyLongWaitRate, 'dateLabel' => Carbon::parse($selectedDate)->toFormattedDateString(), ]; $appointmentsToday = collect(); $upcomingAppointments = collect(); $palette = ['#0ea5e9', '#22c55e', '#f59e0b', '#6366f1', '#ef4444', '#14b8a6']; $mdLabelsMonth = $mdStats->pluck('Current_MD')->map(fn ($md) => $md ?: 'N/A')->values(); $mdValuesMonth = $mdStats->pluck('total')->values(); $monthColors = $mdLabelsMonth->map(function ($_, $idx) use ($palette) { return $palette[$idx % count($palette)]; })->values(); if ($mdLabelsMonth->isEmpty()) { $mdLabelsMonth = collect(['Unknown']); $mdValuesMonth = collect([$totalCheckIns]); $monthColors = collect([$palette[0]]); } $monthlyMdTrend = [ 'labels' => $mdLabelsMonth->values()->all(), 'datasets' => [ [ 'label' => 'Visits', 'data' => $mdValuesMonth->values()->all(), 'backgroundColor' => $monthColors->values()->all(), 'borderRadius' => 8, ], ], ]; Log::info('Dashboard monthly MD workload', [ 'mode' => $viewMode, 'month' => $selectedMonth, 'labels' => $monthlyMdTrend['labels'], 'data' => $monthlyMdTrend['datasets'][0]['data'] ?? [], 'total_check_ins' => $totalCheckIns, ]); $monthlyAgeSummary = [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, ]; $ageRecords = (clone $receptionBase) ->with(['ptconfig' => function ($q) { $q->select('Pid', 'Date of Birth', 'Agey', 'Agem', 'Gender', 'Reg Date'); }]) ->orderBy('Visit Date') ->get(['Pid', 'Visit Date', 'New_Old', 'Agey', 'Agem']); $registryNewLookup = $registryNewPids->keyBy(fn ($pid) => $pid); $newFlagLookup = []; foreach ($ageRecords as $record) { if (!$record->Pid) { continue; } $isNewFlag = in_array($record->New_Old, ['New', 'NEW', 'new', '1', 1, 'N'], true); if (!isset($newFlagLookup[$record->Pid])) { $newFlagLookup[$record->Pid] = $isNewFlag; } elseif ($isNewFlag) { $newFlagLookup[$record->Pid] = true; } } foreach ($ageRecords as $rec) { if (!$rec->Pid) { continue; } $agey = null; $regDate = null; $ptconfigRow = $rec->ptconfig; $patientRow = $patientDemographics->get($rec->Pid); if ($ptconfigRow) { $regDate = $ptconfigRow->{'Reg Date'} ?? null; $augmented = Export_age::Export_general( $ptconfigRow, $rec->{'Visit Date'}, $ptconfigRow->{'Date of Birth'}, $rec ); $agey = $augmented['Current Agey'] ?? null; } elseif ($patientRow) { $regDate = $patientRow->reg_date ?? null; $agey = $patientRow->Agey ?? $rec->Agey ?? 13; } if ($agey === null) { $agey = $rec->Agey ?? 13; } $isUnder12 = intval($agey) < 12; $isNewByFlag = in_array($rec->New_Old, ['New', 'NEW', 'new', '1', 1, 'N'], true); if (!$isNewByFlag) { $isNewByFlag = $newFlagLookup[$rec->Pid] ?? false; } $isNewByRegistry = $registryNewLookup->has($rec->Pid); $isNewByRegDate = false; if ($regDate) { try { $parsedReg = Carbon::parse($regDate); $isNewByRegDate = $parsedReg->betweenIncluded($startOfMonth, $endOfMonth); } catch (\Throwable $e) { $isNewByRegDate = false; } } $isNew = $isNewByRegDate || $isNewByRegistry || $isNewByFlag; if ($isUnder12 && $isNew) { $monthlyAgeSummary['u12_new']++; } elseif ($isUnder12) { $monthlyAgeSummary['u12_old']++; } elseif ($isNew) { $monthlyAgeSummary['o12_new']++; } else { $monthlyAgeSummary['o12_old']++; } } $programCategories = []; try { if ($clinicConnection !== 'ALL') { $countVisitsInRange = function (string $table, string $idColumn, string $dateColumn) use ($clinicConnection, $startOfMonth, $endOfMonth): int { return (int) DB::connection($clinicConnection) ->table($table) ->whereNotNull($idColumn) ->whereDate($dateColumn, '>=', $startOfMonth->toDateString()) ->whereDate($dateColumn, '<=', $endOfMonth->toDateString()) ->count($idColumn); }; $countProgramInRange = function (array $sources) use ($countVisitsInRange): int { $sum = 0; foreach ($sources as $source) { [$table, $idColumn, $dateColumn] = $source; try { $sum += $countVisitsInRange($table, $idColumn, $dateColumn); } catch (\Throwable $e) { // ignore missing tables/columns in some clinic DBs } } return $sum; }; $countProgramFirstAvailableInRange = function (array $sources) use ($countVisitsInRange): int { foreach ($sources as $source) { [$table, $idColumn, $dateColumn] = $source; try { return $countVisitsInRange($table, $idColumn, $dateColumn); } catch (\Throwable $e) { // try next source } } return 0; }; $programCategories = [ ['label' => 'NCD', 'value' => $countProgramInRange([['ncd_followups', 'Pid', 'Visit_date']])], ['label' => 'STI', 'value' => $countProgramInRange([ ['stimales', 'CID', 'Visit_date'], ['stifemales', 'CID', 'Visit_date'], ])], ['label' => 'HTS', 'value' => $countProgramInRange([['coulsellings', 'Pid', 'Counselling_Date']])], ['label' => 'ANC', 'value' => $countProgramInRange([['anc_follow_ups', 'Pid', 'Visitdate']])], ['label' => 'Feeding Center Follow-ups', 'value' => $countProgramInRange([['feeding_centerfups', 'Pid', 'Visitdate']])], ['label' => 'Cervical Cancer', 'value' => $countProgramFirstAvailableInRange([ ['cervicalcancer1s', 'General ID', 'Visit_date'], ['cervicalcancers', 'General ID', 'Visit_date'], ])], ['label' => 'CMV', 'value' => $countProgramInRange([['cmvs', 'Pid_cmv', 'Visit_date']])], ['label' => 'Mental Health Screening', 'value' => $countProgramInRange([['mental__healths', 'Pid', 'Counselling_Date']])], ['label' => 'Prevention Logsheet', 'value' => $countProgramInRange([['prevention_logsheets', 'Pid', 'Visit_Date']])], ['label' => 'Prevention CBS', 'value' => $countProgramInRange([['prevention_c_b_s', 'Pid', 'Visit_Date']])], ['label' => 'PreTB', 'value' => $countProgramFirstAvailableInRange([ ['pre_tb_records', 'cid', 'date_of_screening'], ['pre_t_b_s', 'Pid_preTB', 'TBscreenDate_preTB'], ])], ['label' => 'TB03', 'value' => $countProgramInRange([['tb_register_o3_s', 'Pid_TB03', 'TreDate_TB03']])], ['label' => 'TB IPT', 'value' => $countProgramInRange([['tbipts', 'Pid_iptTB', 'IPT_regDate']])], ]; $programCategories = array_values(array_filter($programCategories, fn ($row) => (int) ($row['value'] ?? 0) > 0)); } } catch (\Throwable $e) { Log::warning('Period program workloads unavailable', ['error' => $e->getMessage()]); $programCategories = []; } $consultationSummary = []; $diseaseCategories = []; try { $consultationService = app(ConsultationReportService::class); $consultData = $consultationService->calculate( $startOfMonth->toDateString(), $endOfMonth->toDateString(), $clinicConnection, $registryConnection ); $consultationSummary = [ 'u12_new' => $consultData['total_u12_new'] ?? 0, 'o12_new' => $consultData['total_o12_new'] ?? 0, 'u12_old' => $consultData['total_u12_old'] ?? 0, 'o12_old' => $consultData['total_o12_old'] ?? 0, 'fsw' => $consultData['fsw'] ?? 0, 'client_fsw' => $consultData['client_fsw'] ?? 0, 'msm' => $consultData['msm'] ?? 0, 'tg' => $consultData['tg'] ?? 0, 'pwud' => $consultData['pwud'] ?? 0, 'idu' => $consultData['idu'] ?? 0, 'preg_mother' => $consultData['preg_mother'] ?? 0, 'spouse_preg' => $consultData['spouse_preg'] ?? 0, 'exposed_children' => $consultData['exposed_children'] ?? 0, 'low_risk' => $consultData['low_risk'] ?? 0, 'partner_kp' => $consultData['partner_kp'] ?? 0, 'partner_plhiv' => $consultData['partner_plhiv'] ?? 0, 'special_groups' => $consultData['special_groups'] ?? 0, 'migrant_population' => $consultData['migrant_population'] ?? 0, 'non_kp' => $consultData['non_kp'] ?? 0, ]; $sumFields = function (array $keys) use ($consultData) { return collect($keys)->map(fn ($k) => $consultData[$k] ?? 0)->sum(); }; $diseaseCategories[] = ['label' => 'FUO', 'value' => $sumFields(['ugen_fuo_new', 'ogen_fuo_new', 'ugen_fuo_old', 'ogen_fuo_old'])]; $diseaseCategories[] = ['label' => 'Diarrhea', 'value' => $sumFields(['diarrhoea_u12_new', 'diarrhoea_o12_new', 'diarrhoea_u12_old', 'diarrhoea_o12_old'])]; $diseaseCategories[] = ['label' => 'Dengue Fever', 'value' => $sumFields(['ugen_dengue_fever_new', 'ogen_dengue_fever_new', 'ugen_dengue_fever_old', 'ogen_dengue_fever_old'])]; $diseaseCategories[] = ['label' => 'URTI-1 (Covid Suspect)', 'value' => $sumFields(['ugen_Covid_relate_new', 'ogen_Covid_relate_new', 'ugen_Covid_relate_old', 'ogen_Covid_relate_old'])]; $diseaseCategories[] = ['label' => 'URTI-2 (Others)', 'value' => $sumFields(['urti2_other_u12_new', 'urti2_other_o12_new', 'urti2_other_u12_old', 'urti2_other_o12_old'])]; $diseaseCategories[] = ['label' => 'LRTI-1 (Pneumonia)', 'value' => $sumFields(['lrti1_pneumonia_u12_new', 'lrti1_pneumonia_o12_new', 'lrti1_pneumonia_u12_old', 'lrti1_pneumonia_o12_old'])]; $diseaseCategories[] = ['label' => 'LRTI-2 (TB Suspect)', 'value' => $sumFields(['lrti2_TBsuspect_u12_new', 'lrti2_TBsuspect_o12_new', 'lrti2_TBsuspect_u12_old', 'lrti2_TBsuspect_o12_old'])]; $diseaseCategories[] = ['label' => 'LRTI-3 (Bronchiolitis & Others)', 'value' => $sumFields(['lrti3_Bronchi_u12_new', 'lrti3_Bronchi_o12_new', 'lrti3_Bronchi_u12_old', 'lrti3_Bronchi_o12_old'])]; $diseaseCategories[] = ['label' => 'COPD', 'value' => $sumFields(['copd_u12_new', 'copd_o12_new', 'copd_u12_old', 'copd_o12_old'])]; $diseaseCategories[] = ['label' => 'Trauma', 'value' => $sumFields(['ugen_trauma_new', 'ogen_trauma_new', 'ugen_trauma_old', 'ogen_trauma_old'])]; $diseaseCategories[] = ['label' => 'Gynaecological diseases', 'value' => $sumFields(['ugen_Gynaecology_new', 'ogen_Gynaecology_new', 'ugen_Gynaecology_old', 'ogen_Gynaecology_old'])]; $diseaseCategories[] = ['label' => 'Breast Diseases', 'value' => $sumFields(['ugen_skin_infect_new', 'ogen_skin_infect_new', 'ugen_skin_infect_old', 'ogen_skin_infect_old'])]; $diseaseCategories[] = ['label' => 'Mental illness', 'value' => $sumFields(['ugen_mentalill_new', 'ogen_mentalill_new', 'ugen_mentalill_old', 'ogen_mentalill_old'])]; $diseaseCategories[] = ['label' => 'Reproductive Tract infection/STI', 'value' => $sumFields(['ugen_STI_new', 'ogen_STI_new', 'ugen_STI_old', 'ogen_STI_old'])]; $diseaseCategories[] = ['label' => 'Malnourished', 'value' => $sumFields(['ugen_malnourish_new', 'ogen_malnourish_new', 'ugen_malnourish_old', 'ogen_malnourish_old'])]; $diseaseCategories[] = ['label' => 'Child Abuse including sexual abuse', 'value' => $sumFields(['ugen_child_abuse_new', 'ogen_child_abuse_new', 'ugen_child_abuse_old', 'ogen_child_abuse_old'])]; $diseaseCategories[] = ['label' => 'Others', 'value' => $sumFields(['ugen_others_new', 'ogen_others_new', 'ugen_others_old', 'ogen_others_old'])]; $diseaseCategories = array_values(array_filter($diseaseCategories, fn ($row) => (int) ($row['value'] ?? 0) > 0)); $periodDiseaseSum = collect($diseaseCategories)->sum(fn ($row) => (int) ($row['value'] ?? 0)); $periodDiseaseMissing = max(((int) $totalCheckIns) - (int) $periodDiseaseSum, 0); if ($periodDiseaseMissing > 0) { $diseaseCategories[] = ['label' => 'Unclassified', 'value' => $periodDiseaseMissing]; } } catch (\Throwable $e) { Log::warning('Consultation summary unavailable', ['error' => $e->getMessage()]); $consultationSummary = [ 'u12_new' => 0, 'o12_new' => 0, 'u12_old' => 0, 'o12_old' => 0, 'fsw' => 0, 'client_fsw' => 0, 'msm' => 0, 'tg' => 0, 'idu' => 0, 'pwud' => 0, 'preg_mother' => 0, 'spouse_preg' => 0, 'exposed_children' => 0, 'low_risk' => 0, 'partner_kp' => 0, 'partner_plhiv' => 0, 'special_groups' => 0, 'migrant_population' => 0, 'non_kp' => 0, ]; $diseaseCategories = []; } $monthlyReport = [ 'labels' => ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun'], 'consultations' => [420, 460, 502, 530, 580, 610], 'followUps' => [180, 190, 210, 230, 240, 260], 'labs' => [150, 170, 190, 205, 220, 240], ]; $focusItems = [ [ 'title' => 'Missed appointments', 'value' => 6, 'note' => 'Call back before noon', ], [ 'title' => 'Fast movers', 'value' => 18, 'note' => 'Ready for quick consult today', ], [ 'title' => 'High priority labs', 'value' => 4, 'note' => 'Flag for STAT processing', ], ]; if ($periodMode) { $cacheMonthlyHourlyLoad = $monthlyHourlyLoad ?? ['labels' => [], 'series' => []]; $cacheMonthlyClerkSummary = $monthlyClerkSummary ?? []; $cacheMonthlyClerkHours = $monthlyClerkHours ?? ['labels' => [], 'series' => []]; $cacheMonthlyChecks = $monthlyChecks ?? []; $cacheMonthlyAge = $monthlyAgeSummary ?? []; $cacheMdStats = $mdStats ?? collect(); try { ReportCache::updateOrCreate( ['period_type' => $viewMode, 'period_key' => $cachePeriodKey], [ 'data' => [ 'monthlyChecks' => $cacheMonthlyChecks, 'durationSummary' => $durationSummary, 'dateLabel' => $dateLabel, 'unplan' => $unplan, 'planned' => $planned, 'mdStats' => $cacheMdStats ? $cacheMdStats->toArray() : [], 'monthlyMdTrend' => $monthlyMdTrend, 'timeInClinicDailyBands' => $timeInClinicDailyBands, 'consultationSummary' => $consultationSummary, 'periodGenderBreakdown' => $periodGenderBreakdown, 'periodKpBreakdown' => $periodKpBreakdown, 'programCategories' => $programCategories, 'diseaseCategories' => $diseaseCategories, 'monthlyAgeSummary' => $cacheMonthlyAge, 'monthlyHourlyLoad' => $cacheMonthlyHourlyLoad, 'monthlyClerkSummary' => $cacheMonthlyClerkSummary, 'monthlyClerkHours' => $cacheMonthlyClerkHours, 'selectedMonth' => $selectedMonth, 'selectedMonthLabel' => $selectedMonthLabel, 'selectedQuarter' => $selectedQuarter, 'monthInputValue' => $monthInputValue, 'monthlyReport' => $monthlyReport, 'focusItems' => $focusItems, 'yearlyStrategy' => $viewMode === 'yearly' ? 'direct' : null, 'viewMode' => $viewMode, 'cacheMeta' => [ 'clinic' => $clinicConnection, 'period_type' => $viewMode, 'period_key' => $periodKey, 'covered_from' => $periodStartDateTime->toDateString(), 'covered_to' => $periodEndDateTime->toDateString(), 'is_complete' => true, 'generated_at' => now()->toDateTimeString(), ], ], ] ); $cacheUpdatedAt = now()->toDateTimeString(); view()->share('cacheUpdatedAt', $cacheUpdatedAt); } catch (\Throwable $e) { Log::warning('Failed to cache report', ['error' => $e->getMessage()]); } } $hourLabels = collect(range(0, 23))->map(fn ($h) => str_pad($h, 2, '0', STR_PAD_LEFT)); $baseHourCounts = array_fill(0, 24, 0); $receptionHour = Followup_general::on($clinicConnection)->whereBetween('Visit Date', [$startOfMonth->toDateString(), $endOfMonth->toDateString()]) ->select(DB::raw('HOUR(created_at) as hr'), DB::raw('count(*) as total')) ->groupBy('hr') ->pluck('total', 'hr') ->toArray(); $counsellorHour = CounsellorRecords::on($clinicConnection)->whereBetween('Counselling_Date', [$startOfMonth->toDateString(), $endOfMonth->toDateString()]) ->select(DB::raw('HOUR(created_at) as hr'), DB::raw('count(*) as total')) ->groupBy('hr') ->pluck('total', 'hr') ->toArray(); $dispenseHour = Consumption::on($clinicConnection)->whereBetween('Given_Date', [$startOfMonth->toDateString(), $endOfMonth->toDateString()]) ->select(DB::raw('HOUR(created_at) as hr'), DB::raw('count(*) as total')) ->groupBy('hr') ->pluck('total', 'hr') ->toArray(); $labHourTotals = $baseHourCounts; foreach ($labModels as $lm) { $labCounts = ($lm['model'])::on($clinicConnection)->whereBetween('created_at', [$periodStartDateTime, $periodEndDateTime]) ->select(DB::raw('HOUR(created_at) as hr'), DB::raw('count(*) as total')) ->groupBy('hr') ->pluck('total', 'hr') ->toArray(); foreach ($labCounts as $hr => $count) { $idx = intval($hr); if ($idx >= 0 && $idx < 24) { $labHourTotals[$idx] += $count; } } } $fillHours = function ($source) use ($baseHourCounts) { $filled = $baseHourCounts; foreach ($source as $hr => $count) { $idx = intval($hr); if ($idx >= 0 && $idx < 24) { $filled[$idx] = $count; } } return array_values($filled); }; $monthlyHourlyLoad = [ 'labels' => $hourLabels->all(), 'series' => [ ['label' => 'Reception', 'data' => $fillHours($receptionHour), 'color' => '#0ea5e9'], ['label' => 'Labs', 'data' => array_values($labHourTotals), 'color' => '#8b5cf6'], ['label' => 'Counsellor', 'data' => $fillHours($counsellorHour), 'color' => '#6366f1'], ['label' => 'Dispensing', 'data' => $fillHours($dispenseHour), 'color' => '#f59e0b'], ], ]; $dailyReceptionHour = Followup_general::on($clinicConnection)->whereDate('Visit Date', $selectedDate) ->select(DB::raw('HOUR(created_at) as hr'), DB::raw('count(*) as total')) ->groupBy('hr') ->pluck('total', 'hr') ->toArray(); $dailyCounsellorHour = CounsellorRecords::on($clinicConnection)->whereDate('Counselling_Date', $selectedDate) ->select(DB::raw('HOUR(created_at) as hr'), DB::raw('count(*) as total')) ->groupBy('hr') ->pluck('total', 'hr') ->toArray(); $dailyDispenseHour = Consumption::on($clinicConnection)->whereDate('Given_Date', $selectedDate) ->select(DB::raw('HOUR(created_at) as hr'), DB::raw('count(*) as total')) ->groupBy('hr') ->pluck('total', 'hr') ->toArray(); $dailyLabHourTotals = $baseHourCounts; foreach ($labModels as $lm) { $labCounts = ($lm['model'])::on($clinicConnection)->whereDate('created_at', $selectedDate) ->select(DB::raw('HOUR(created_at) as hr'), DB::raw('count(*) as total')) ->groupBy('hr') ->pluck('total', 'hr') ->toArray(); foreach ($labCounts as $hr => $count) { $idx = intval($hr); if ($idx >= 0 && $idx < 24) { $dailyLabHourTotals[$idx] += $count; } } } $dailyHourlyLoad = [ 'labels' => $hourLabels->all(), 'series' => [ ['label' => 'Reception', 'data' => $fillHours($dailyReceptionHour), 'color' => '#0ea5e9'], ['label' => 'Labs', 'data' => array_values($dailyLabHourTotals), 'color' => '#8b5cf6'], ['label' => 'Counsellor', 'data' => $fillHours($dailyCounsellorHour), 'color' => '#6366f1'], ['label' => 'Dispensing', 'data' => $fillHours($dailyDispenseHour), 'color' => '#f59e0b'], ], ]; $clerkTables = [ 'anc_registers' => 'ANC Registers', 'anc_follow_ups' => 'ANC Follow Ups', 'cervicalcancer1s' => 'Cervical Cancer', 'cmvs' => 'CMV', 'h_b_v__registers' => 'HBV Registers', 'mental_registers' => 'Mental Registers', 'mental_follows' => 'Mental Follows', 'mental__healths' => 'Mental Health', 'ncd_pt_registers' => 'NCD Registers', 'ncd_followups' => 'NCD Followups', 'prevention_logsheets' => 'Prevention Logsheets', 'pre_t_b_s' => 'Pre TB', 'stimales' => 'STI Males', 'stifemales' => 'STI Females', 'tb_register_o3_s' => 'TB Register O3', 'tbipts' => 'TBI PTS', ]; $dailyClerkSummary = []; $monthlyClerkSummary = []; $dailyClerkHourTotals = $baseHourCounts; $monthlyClerkHourTotals = $baseHourCounts; foreach ($clerkTables as $table => $label) { try { $dailyCount = DB::connection($clinicConnection)->table($table)->whereDate('created_at', $selectedDate)->count(); $dailyClerkSummary[] = ['title' => $label, 'value' => $dailyCount]; $monthlyCount = DB::connection($clinicConnection)->table($table) ->whereBetween('created_at', [$periodStartDateTime, $periodEndDateTime]) ->count(); $monthlyClerkSummary[] = ['title' => $label, 'value' => $monthlyCount]; $dailyHourCounts = DB::connection($clinicConnection)->table($table) ->whereDate('created_at', $selectedDate) ->select(DB::raw('HOUR(created_at) as hr'), DB::raw('count(*) as total')) ->groupBy('hr') ->pluck('total', 'hr') ->toArray(); foreach ($dailyHourCounts as $hr => $count) { $idx = intval($hr); if ($idx >= 0 && $idx < 24) { $dailyClerkHourTotals[$idx] += $count; } } $monthlyHourCounts = DB::connection($clinicConnection)->table($table) ->whereBetween('created_at', [$periodStartDateTime, $periodEndDateTime]) ->select(DB::raw('HOUR(created_at) as hr'), DB::raw('count(*) as total')) ->groupBy('hr') ->pluck('total', 'hr') ->toArray(); foreach ($monthlyHourCounts as $hr => $count) { $idx = intval($hr); if ($idx >= 0 && $idx < 24) { $monthlyClerkHourTotals[$idx] += $count; } } } catch (\Throwable $e) { // ignore missing tables } } $dailyClerkHours = [ 'labels' => $hourLabels->all(), 'series' => [ ['label' => 'Data entry', 'data' => array_values($dailyClerkHourTotals), 'color' => '#0ea5e9'], ], ]; $monthlyClerkHours = [ 'labels' => $hourLabels->all(), 'series' => [ ['label' => 'Data entry', 'data' => array_values($monthlyClerkHourTotals), 'color' => '#0ea5e9'], ], ]; $clinicTimeInClinicComparison = []; $ncdAnalytics = null; return view( 'clinic_dashboard', compact( 'clinicLeader', 'clinicConnection', 'clinicOptions', 'dailyChecks', 'dailyGenderBreakdown', 'dailyAgeSummary', 'dailyKpBreakdown', 'dailyProgramWorkloads', 'dailyDiseaseCategories', 'periodGenderBreakdown', 'periodKpBreakdown', 'monthlyReport', 'focusItems', 'selectedDate', 'durationSummary', 'dateLabel', 'unplan', 'planned', 'mdStats', 'mdStatsDaily', 'appointmentsToday', 'upcomingAppointments', 'monthlyMdTrend', 'selectedMonth', 'selectedMonthLabel', 'monthlyChecks', 'dailyDurationSummary', 'viewMode', 'dailyUnplanned', 'dailyPlanned', 'consultationSummary', 'programCategories', 'diseaseCategories', 'monthlyAgeSummary', 'monthlyHourlyLoad', 'dailyHourlyLoad', 'dailyClerkSummary', 'monthlyClerkSummary', 'dailyClerkHours', 'monthlyClerkHours', 'selectedQuarter', 'monthInputValue', 'cachePeriodKey', 'periodKey', 'periodMode', 'useLive', 'timeInClinicDailyBands', 'clinicTimeInClinicComparison', 'ncdAnalytics' ) ); } public function export(Request $request): StreamedResponse { $periodType = $request->query('period_type', 'monthly'); $periodKey = $request->query('period_key'); $clinicConnection = $this->resolveClinicConnection($request->query('clinic')); if (!$periodKey || !in_array($periodType, ['monthly', 'quarterly'], true)) { abort(400, 'Missing or invalid period.'); } $cachePeriodKey = $this->buildCacheKey($clinicConnection, $periodKey); $cache = ReportCache::where('period_type', $periodType) ->where('period_key', $cachePeriodKey) ->first(); $cacheExpiry = Carbon::now()->subMonths(6); if ( !$cache || !is_array($cache->data) || !$cache->updated_at || Carbon::parse($cache->updated_at)->lt($cacheExpiry) ) { abort(404, 'Cached report not found. Run live calculation first.'); } $data = $cache->data; $filename = "report-{$periodType}-{$clinicConnection}-{$periodKey}.csv"; $rows = []; $appendSection = function (string $section, array $entries) use (&$rows) { foreach ($entries as $entry) { if (!isset($entry['title'], $entry['value'])) { continue; } $rows[] = [$section, $entry['title'], $entry['value']]; } }; // Service summary $appendSection('Service summary', $data['monthlyChecks'] ?? []); // Planned vs unplanned $rows[] = ['Planned vs unplanned', 'Planned', $data['planned'] ?? 0]; $rows[] = ['Planned vs unplanned', 'Unplanned', $data['unplan'] ?? 0]; // Service populated hours if (!empty($data['monthlyHourlyLoad']['series'])) { foreach ($data['monthlyHourlyLoad']['series'] as $series) { $label = $series['label'] ?? 'Service'; $hourData = $series['data'] ?? []; foreach ($hourData as $idx => $val) { $hourLabel = $data['monthlyHourlyLoad']['labels'][$idx] ?? $idx; $rows[] = ['Service populated hours', "{$label} hour {$hourLabel}", $val]; } } } // Data entry workload (month) $appendSection('Data entry workload', $data['monthlyClerkSummary'] ?? []); // Data entry populated hours (month) if (!empty($data['monthlyClerkHours']['series'])) { foreach ($data['monthlyClerkHours']['series'] as $series) { $label = $series['label'] ?? 'Data entry'; $hourData = $series['data'] ?? []; foreach ($hourData as $idx => $val) { $hourLabel = $data['monthlyClerkHours']['labels'][$idx] ?? $idx; $rows[] = ['Data entry populated hours', "{$label} hour {$hourLabel}", $val]; } } } // Visits by age & status if (!empty($data['monthlyAgeSummary'])) { $age = $data['monthlyAgeSummary']; $rows[] = ['Visits by age & status', 'New <12', $age['u12_new'] ?? 0]; $rows[] = ['Visits by age & status', 'New ≥12', $age['o12_new'] ?? 0]; $rows[] = ['Visits by age & status', 'Old <12', $age['u12_old'] ?? 0]; $rows[] = ['Visits by age & status', 'Old ≥12', $age['o12_old'] ?? 0]; } // Key populations if (!empty($data['consultationSummary'])) { $kp = $data['consultationSummary']; $rows[] = ['Key populations', 'Pregnant Mother', $kp['preg_mother'] ?? 0]; $rows[] = ['Key populations', 'Spouse of pregnant mother', $kp['spouse_preg'] ?? 0]; $rows[] = ['Key populations', 'Exposed Children', $kp['exposed_children'] ?? 0]; $rows[] = ['Key populations', 'Low Risk', $kp['low_risk'] ?? 0]; $rows[] = ['Key populations', 'FSW', $kp['fsw'] ?? 0]; $rows[] = ['Key populations', 'Client of FSW', $kp['client_fsw'] ?? 0]; $rows[] = ['Key populations', 'MSM', $kp['msm'] ?? 0]; $rows[] = ['Key populations', 'TG', $kp['tg'] ?? 0]; $rows[] = ['Key populations', 'PWUD', $kp['pwud'] ?? 0]; $rows[] = ['Key populations', 'PWID', $kp['idu'] ?? 0]; $rows[] = ['Key populations', 'Partner of KP', $kp['partner_kp'] ?? 0]; $rows[] = ['Key populations', 'Partner of PLHIV', $kp['partner_plhiv'] ?? 0]; $rows[] = ['Key populations', 'Special Groups', $kp['special_groups'] ?? 0]; $rows[] = ['Key populations', 'Migrant Population', $kp['migrant_population'] ?? 0]; $rows[] = ['Key populations', 'General patients', $kp['non_kp'] ?? 0]; } // Program workloads $appendSection('Program workloads', $data['programCategories'] ?? []); // General diseases $appendSection('General diseases', $data['diseaseCategories'] ?? []); // MD workload if (!empty($data['mdStats'])) { foreach ($data['mdStats'] as $md) { $rows[] = ['MD workload', $md['Current_MD'] ?? 'N/A', $md['total'] ?? 0]; } } $rows[] = ['Meta', 'Clinic', $clinicConnection]; $rows[] = ['Meta', 'Period', $periodKey]; $rows[] = ['Meta', 'Generated_at', now()->toDateTimeString()]; return response()->streamDownload(function () use ($rows) { $out = fopen('php://output', 'w'); fputcsv($out, ['Section', 'Label', 'Value']); foreach ($rows as $row) { fputcsv($out, $row); } fclose($out); }, $filename, [ 'Content-Type' => 'text/csv', ]); } private function buildLiveTimeInClinicDailyBands(string $clinicConnection, Carbon $periodStartDateTime, Carbon $periodEndDateTime): array { $timeInClinicBandDefs = [ ['label' => '1-90 min', 'min' => 1, 'max' => 90, 'color' => '#22c55e'], ['label' => '91-150 min', 'min' => 91, 'max' => 150, 'color' => '#f59e0b'], ['label' => '151-210 min', 'min' => 151, 'max' => 210, 'color' => '#f97316'], ['label' => '211-270 min', 'min' => 211, 'max' => 270, 'color' => '#ef4444'], ['label' => '271-330 min', 'min' => 271, 'max' => 330, 'color' => '#8b5cf6'], ]; $excludedBand = ['label' => 'Excluded (no dispensing)', 'color' => '#d1d5db']; $dateKeys = []; $labels = []; $cursor = $periodStartDateTime->copy()->startOfDay(); $periodLastDay = $periodEndDateTime->copy()->startOfDay(); while ($cursor->lte($periodLastDay)) { $dateKeys[] = $cursor->toDateString(); $labels[] = $cursor->format('D d M'); $cursor->addDay(); } $zeroDatasets = array_map(function ($band) use ($dateKeys) { return [ 'label' => $band['label'], 'data' => array_fill(0, count($dateKeys), 0), 'backgroundColor' => $band['color'], 'borderColor' => $band['color'], ]; }, $timeInClinicBandDefs); $zeroDatasets[] = [ 'label' => $excludedBand['label'], 'data' => array_fill(0, count($dateKeys), 0), 'backgroundColor' => $excludedBand['color'], 'borderColor' => $excludedBand['color'], ]; if ($clinicConnection === 'ALL' || !$this->connectionIsUsable($clinicConnection)) { return ['labels' => $labels, 'datasets' => $zeroDatasets]; } try { $visitRecords = Followup_general::on($clinicConnection) ->whereBetween('Visit Date', [$periodStartDateTime->toDateString(), $periodEndDateTime->toDateString()]) ->get(['Pid', 'Visit Date', 'created_at']); if ($visitRecords->isEmpty()) { return ['labels' => $labels, 'datasets' => $zeroDatasets]; } $periodPids = $visitRecords->pluck('Pid')->filter()->unique()->values(); $latestDispense = []; $pushLatestDispense = function ($pid, $serviceDate, $createdAt) use (&$latestDispense) { if (!$pid || !$serviceDate || !$createdAt) { return; } $ts = Carbon::parse($createdAt); $dateKey = Carbon::parse($serviceDate)->toDateString(); if ($ts->toDateString() !== $dateKey) { $ts = Carbon::parse($dateKey . ' ' . $ts->format('H:i:s')); } $key = $pid . '|' . $dateKey; if (!isset($latestDispense[$key]) || $ts->gt($latestDispense[$key])) { $latestDispense[$key] = $ts; } }; if ($periodPids->isNotEmpty()) { try { $dispenseTimes = Consumption::on($clinicConnection) ->whereBetween('Given_Date', [$periodStartDateTime->toDateString(), $periodEndDateTime->toDateString()]) ->whereIn('Pid', $periodPids) ->get(['Pid', 'Given_Date', 'created_at']); foreach ($dispenseTimes as $row) { $pushLatestDispense($row->Pid ?? null, $row->Given_Date ?? null, $row->created_at ?? null); } } catch (\Throwable $e) { // Ignore unavailable table/schema differences per clinic. } } $periodReceptionStart = []; foreach ($visitRecords as $visit) { $pid = $visit->Pid ?? null; if (!$pid) { continue; } $visitDate = $visit->{'Visit Date'} ? Carbon::parse($visit->{'Visit Date'})->toDateString() : null; if (!$visitDate) { continue; } $start = $visit->created_at ? Carbon::parse($visit->created_at) : Carbon::parse($visitDate)->startOfDay(); if ($start->toDateString() !== $visitDate) { $start = Carbon::parse($visitDate . ' ' . $start->format('H:i:s')); } $dateKey = $visitDate; $key = $pid . '|' . $dateKey; if (!isset($periodReceptionStart[$key]) || $start->lt($periodReceptionStart[$key])) { $periodReceptionStart[$key] = $start; } } $timeInClinicBandCountsByDate = []; $excludedCountsByDate = []; foreach ($periodReceptionStart as $key => $start) { $dateKey = explode('|', $key, 2)[1] ?? null; if (!$dateKey) { continue; } $end = $latestDispense[$key] ?? null; if (!$end) { $excludedCountsByDate[$dateKey] = ($excludedCountsByDate[$dateKey] ?? 0) + 1; continue; } if ($end->lt($start)) { $end = $start; } $minutes = max(1, $start->diffInMinutes($end)); if ($minutes > 330) { $minutes = 330; } foreach ($timeInClinicBandDefs as $band) { if ($minutes >= $band['min'] && $minutes <= $band['max']) { $label = $band['label']; $timeInClinicBandCountsByDate[$dateKey][$label] = ($timeInClinicBandCountsByDate[$dateKey][$label] ?? 0) + 1; break; } } } $datasets = array_map(function ($band) use ($dateKeys, $timeInClinicBandCountsByDate) { $label = $band['label']; $data = []; foreach ($dateKeys as $dateKey) { $data[] = (int) ($timeInClinicBandCountsByDate[$dateKey][$label] ?? 0); } return [ 'label' => $label, 'data' => $data, 'backgroundColor' => $band['color'], 'borderColor' => $band['color'], ]; }, $timeInClinicBandDefs); $excludedData = []; foreach ($dateKeys as $dateKey) { $excludedData[] = (int) ($excludedCountsByDate[$dateKey] ?? 0); } $datasets[] = [ 'label' => $excludedBand['label'], 'data' => $excludedData, 'backgroundColor' => $excludedBand['color'], 'borderColor' => $excludedBand['color'], ]; return [ 'labels' => $labels, 'datasets' => $datasets, ]; } catch (\Throwable $e) { Log::warning('Live time-in-clinic calculation failed', [ 'clinic' => $clinicConnection, 'start' => $periodStartDateTime->toDateString(), 'end' => $periodEndDateTime->toDateString(), 'error' => $e->getMessage(), ]); return ['labels' => $labels, 'datasets' => $zeroDatasets]; } } private function resolveClinicConnection(?string $input): string { $normalized = strtoupper(trim((string) $input)); $allowed = [ 'ALL' => 'ALL', 'MAM_A' => 'MAM_A', 'MAM_B' => 'MAM_B', 'MAM_C1' => 'MAM_C1', 'MAM_A+B+C1' => 'MAM_A+B+C1', 'MAM_SPT' => 'MAM_SPT', 'MAM_SDG' => 'MAM_SDG', 'MAM_TL' => 'MAM_TL', 'MAM_TBZY' => 'MAM_TBZY', ]; $default = config('database.default'); return ($normalized !== '' && in_array($normalized, $allowed, true)) ? $normalized : $default; } private function resolveAggregateScopeClinics(string $scopeClinic, array $clinicOptions): array { $scopeClinic = strtoupper(trim($scopeClinic)); if ($scopeClinic === 'MAM_A+B+C1') { return ['MAM_A', 'MAM_B', 'MAM_C1']; } return array_values(array_filter(array_keys($clinicOptions), fn ($c) => !in_array($c, ['ALL', 'MAM_A+B+C1'], true))); } private function resolveRegistryConnection(): string { return (new PtConfig())->getConnectionName() ?? 'mysql2'; } private function payloadCoversPeriod( array $payload, string $clinicConnection, string $viewMode, string $periodKey, Carbon $periodStartDateTime, Carbon $periodEndDateTime ): bool { $meta = $payload['cacheMeta'] ?? null; if (!is_array($meta)) { return false; } if (($meta['clinic'] ?? null) !== $clinicConnection) { return false; } if (($meta['period_type'] ?? null) !== $viewMode) { return false; } if (($meta['period_key'] ?? null) !== $periodKey) { return false; } if (!($meta['is_complete'] ?? false)) { return false; } try { $coveredFrom = Carbon::parse((string) ($meta['covered_from'] ?? '')); $coveredTo = Carbon::parse((string) ($meta['covered_to'] ?? '')); } catch (\Throwable $e) { return false; } return $coveredFrom->lte($periodStartDateTime->copy()->startOfDay()) && $coveredTo->gte($periodEndDateTime->copy()->startOfDay()); } private function canUseCachedPayloadForDisplay( array $payload, string $clinicConnection, string $viewMode, string $periodKey, Carbon $periodStartDateTime, Carbon $periodEndDateTime ): bool { if ($this->periodIncludesCurrentMonth($periodStartDateTime, $periodEndDateTime)) { return false; } return $this->payloadCoversPeriod( $payload, $clinicConnection, $viewMode, $periodKey, $periodStartDateTime, $periodEndDateTime ); } private function periodIncludesCurrentMonth(Carbon $periodStartDateTime, Carbon $periodEndDateTime): bool { $today = Carbon::today(); return $today->betweenIncluded( $periodStartDateTime->copy()->startOfDay(), $periodEndDateTime->copy()->endOfDay() ); } private function buildClinicPeriodRequestParams( string $clinic, string $viewMode, string $periodKey, Carbon $periodStartDateTime ): array { $params = [ 'clinic' => $clinic, 'mode' => $viewMode, 'date' => $periodStartDateTime->toDateString(), 'live' => 1, '_cache_refresh' => 1, ]; if ($viewMode === 'monthly') { $params['month'] = $periodKey; } elseif ($viewMode === 'quarterly') { $params['quarter'] = $periodKey; } elseif ($viewMode === 'yearly') { $params['year'] = (int) $periodKey; $params['persist_yearly'] = 0; } return $params; } private function aggregatePeriodAcrossClinics( array $clinics, string $viewMode, string $periodKey, Carbon $periodStartDateTime, Carbon $periodEndDateTime, array $context = [] ): ?array { $targetClinics = array_values(array_filter($clinics, fn ($c) => $c !== 'ALL')); if (empty($targetClinics)) { return null; } $keys = collect($targetClinics) ->map(fn ($c) => $this->buildCacheKey($c, $periodKey)) ->all(); $caches = ReportCache::where('period_type', $viewMode) ->whereIn('period_key', $keys) ->get(); $latestByKey = $caches ->sortByDesc(function ($row) { return $row->updated_at ? $row->updated_at->timestamp : 0; }) ->groupBy('period_key') ->map(fn ($rows) => $rows->first()); $payloads = []; $missingClinics = []; foreach ($targetClinics as $clinic) { $key = $this->buildCacheKey($clinic, $periodKey); $row = $latestByKey->get($key); $payload = ($row && is_array($row->data)) ? $row->data : null; if ($payload && $viewMode === 'yearly') { $strategy = $payload['yearlyStrategy'] ?? null; if (!in_array($strategy, ['direct', 'quarter_sum'], true)) { $payload = null; } } $cacheUsable = !empty($context['allow_current_period_cache']) ? $this->payloadCoversPeriod($payload ?? [], $clinic, $viewMode, $periodKey, $periodStartDateTime, $periodEndDateTime) : $this->canUseCachedPayloadForDisplay($payload ?? [], $clinic, $viewMode, $periodKey, $periodStartDateTime, $periodEndDateTime); if ($payload && $cacheUsable) { $payloads[] = $payload; continue; } $missingClinics[] = $clinic; } foreach ($missingClinics as $clinic) { if (!$this->connectionIsUsable($clinic)) { return null; } $requestParams = $this->buildClinicPeriodRequestParams($clinic, $viewMode, $periodKey, $periodStartDateTime); $response = $this->index(Request::create('/clinic_dashboard', 'GET', $requestParams)); if (!($response instanceof \Illuminate\View\View)) { return null; } $refreshed = ReportCache::where('period_type', $viewMode) ->where('period_key', $this->buildCacheKey($clinic, $periodKey)) ->orderByDesc('updated_at') ->first(); if (!$refreshed || !is_array($refreshed->data)) { return null; } $payload = $refreshed->data; if ($viewMode === 'yearly') { $strategy = $payload['yearlyStrategy'] ?? null; if (!in_array($strategy, ['direct', 'quarter_sum'], true)) { return null; } } if (!$this->payloadCoversPeriod($payload, $clinic, $viewMode, $periodKey, $periodStartDateTime, $periodEndDateTime)) { return null; } $payloads[] = $payload; } if (count($payloads) !== count($targetClinics)) { return null; } $agg = $this->combineCachedPayloads($payloads); if (!$agg) { return null; } $scopeClinic = strtoupper(trim((string) ($context['scope_clinic'] ?? 'ALL'))); if ($scopeClinic === '') { $scopeClinic = 'ALL'; } $agg['clinicLabel'] = $scopeClinic; $agg['clinicConnection'] = $scopeClinic; $agg['clinicTimeInClinicComparison'] = $this->buildClinicTimeInClinicComparison($payloads); $agg['cachePeriodKey'] = $this->buildCacheKey($scopeClinic, $periodKey); $agg['cacheMeta'] = [ 'clinic' => $scopeClinic, 'period_type' => $viewMode, 'period_key' => $periodKey, 'covered_from' => $periodStartDateTime->toDateString(), 'covered_to' => $periodEndDateTime->toDateString(), 'is_complete' => true, 'source_clinics' => $targetClinics, 'generated_at' => now()->toDateTimeString(), ]; try { ReportCache::updateOrCreate( ['period_type' => $viewMode, 'period_key' => $agg['cachePeriodKey']], ['data' => $agg] ); } catch (\Throwable $e) { Log::warning('Failed to cache aggregate report', ['error' => $e->getMessage()]); } return $agg; } private function buildClinicTimeInClinicComparison(array $payloads): array { $rows = []; foreach ($payloads as $idx => $payload) { $clinic = $payload['cacheMeta']['clinic'] ?? $payload['clinicConnection'] ?? 'Clinic ' . ($idx + 1); if ($clinic === 'ALL') { continue; } $consultations = 0; foreach ($payload['monthlyChecks'] ?? [] as $card) { if (($card['title'] ?? null) === 'Total check-ins') { $consultations = (int) ($card['value'] ?? 0); break; } } $mdVisitTotal = 0; $mdProviderCount = 0; foreach ($payload['mdStats'] ?? [] as $row) { $total = (int) ($row['total'] ?? 0); if ($total <= 0) { continue; } $mdVisitTotal += $total; $mdProviderCount++; } $bands = []; $bandTotal = 0; foreach ($payload['timeInClinicDailyBands']['datasets'] ?? [] as $dataset) { $value = array_sum(array_map('intval', $dataset['data'] ?? [])); $bandTotal += $value; $bands[] = [ 'label' => $dataset['label'] ?? 'Unknown', 'value' => $value, 'color' => $dataset['backgroundColor'] ?? '#64748b', ]; } if ($bandTotal <= 0) { continue; } foreach ($bands as &$band) { $band['percent'] = round(($band['value'] / $bandTotal) * 100, 1); } unset($band); $rows[] = [ 'clinic' => $clinic, 'consultations' => $consultations, 'mdVisits' => $mdVisitTotal, 'mdProviders' => $mdProviderCount, 'timeInClinicTotal' => $bandTotal, 'bands' => $bands, ]; } return $rows; } private function aggregateYearFromQuarterCaches(string $clinicConnection, string $year, bool $persist = true): ?array { if ($clinicConnection === 'ALL') { return null; } // Pick the latest available cache for each quarter (supports legacy dash-v* keys). $quarterPeriods = [ "{$year}-Q1", "{$year}-Q2", "{$year}-Q3", "{$year}-Q4", ]; $caches = ReportCache::where('period_type', 'quarterly') ->where('period_key', 'like', '%|' . $clinicConnection . '|' . $year . '-Q%') ->get(['period_key', 'data', 'updated_at']); $bestByQuarter = []; foreach ($caches as $cache) { $key = (string) ($cache->period_key ?? ''); $parts = explode('|', $key); if (count($parts) < 3) { continue; } $periodKey = $parts[2]; if (!in_array($periodKey, $quarterPeriods, true)) { continue; } if (!is_array($cache->data)) { continue; } $updatedAt = $cache->updated_at ? Carbon::parse($cache->updated_at) : null; if ($updatedAt === null) { continue; } if (!isset($bestByQuarter[$periodKey]) || $bestByQuarter[$periodKey]['updated_at']->lt($updatedAt)) { $bestByQuarter[$periodKey] = [ 'updated_at' => $updatedAt, 'data' => $cache->data, ]; } } $payloads = []; foreach ($quarterPeriods as $p) { if (!isset($bestByQuarter[$p])) { return null; } $payloads[] = $bestByQuarter[$p]['data']; } $agg = $this->combineCachedPayloads($payloads); if (!$agg) { return null; } $agg['selectedMonth'] = (string) $year; $agg['selectedMonthLabel'] = 'Year ' . $year; $agg['selectedQuarter'] = null; $agg['monthInputValue'] = sprintf('%d-01', $year); $agg['periodKey'] = (string) $year; $agg['cachePeriodKey'] = $this->buildCacheKey($clinicConnection, (string) $year); $agg['viewMode'] = 'yearly'; $agg['yearlyStrategy'] = 'quarter_sum'; if ($persist) { try { ReportCache::updateOrCreate( ['period_type' => 'yearly', 'period_key' => $agg['cachePeriodKey']], ['data' => $agg] ); } catch (\Throwable $e) { Log::warning('Failed to cache yearly aggregate', ['error' => $e->getMessage()]); } } return $agg; } private function combineCachedPayloads(array $payloads): ?array { if (empty($payloads)) { return null; } $agg = $payloads[0]; $parseCardChangeNumber = function ($change): ?array { if (!is_string($change)) { return null; } if (!preg_match('/^(Returning|Planned):\s*([0-9,]+)/', trim($change), $matches)) { return null; } return [ 'label' => $matches[1], 'value' => (int) str_replace(',', '', $matches[2]), ]; }; $sumCards = function (array $base, array $incoming) use ($parseCardChangeNumber): array { $map = []; foreach ($base as $card) { if (empty($card['title'])) continue; $map[$card['title']] = $card; } foreach ($incoming as $card) { if (empty($card['title'])) continue; $title = $card['title']; $value = (float) ($card['value'] ?? 0); if (!isset($map[$title])) { $map[$title] = $card; $map[$title]['value'] = $value; } else { $map[$title]['value'] = ((float) ($map[$title]['value'] ?? 0)) + $value; $existingChange = $parseCardChangeNumber($map[$title]['change'] ?? null); $incomingChange = $parseCardChangeNumber($card['change'] ?? null); if ($existingChange && $incomingChange && $existingChange['label'] === $incomingChange['label']) { $map[$title]['change'] = $existingChange['label'] . ': ' . ($existingChange['value'] + $incomingChange['value']); } } } return array_values($map); }; $sumCategories = function (array $base, array $incoming): array { $map = []; foreach ($base as $row) { if (empty($row['label'])) continue; $map[$row['label']] = (float) ($row['value'] ?? 0); } foreach ($incoming as $row) { if (empty($row['label'])) continue; $map[$row['label']] = ($map[$row['label']] ?? 0) + (float) ($row['value'] ?? 0); } return collect($map)->map(fn ($v, $k) => ['label' => $k, 'value' => $v])->values()->all(); }; $sumAssoc = function (array $base, array $incoming): array { $result = $base; foreach ($incoming as $k => $v) { if (is_numeric($v)) { $result[$k] = ($result[$k] ?? 0) + $v; } } return $result; }; $sumMdStats = function (array $base, array $incoming): array { $map = []; foreach ($base as $row) { $name = $row['Current_MD'] ?? 'N/A'; $map[$name] = ($map[$name] ?? 0) + (int) ($row['total'] ?? 0); } foreach ($incoming as $row) { $name = $row['Current_MD'] ?? 'N/A'; $map[$name] = ($map[$name] ?? 0) + (int) ($row['total'] ?? 0); } return collect($map) ->map(fn ($v, $k) => ['Current_MD' => $k, 'total' => $v]) ->values() ->all(); }; $sumDatasetByAxisLabels = function (array $base, array $incoming, string $datasetKey): array { $baseLabels = array_values($base['labels'] ?? []); $incomingLabels = array_values($incoming['labels'] ?? []); if (empty($baseLabels)) { return $incoming; } if (empty($incomingLabels)) { return $base; } $labels = $baseLabels; foreach ($incomingLabels as $label) { if (!in_array($label, $labels, true)) { $labels[] = $label; } } $datasetMap = []; $pushDataset = function (array $source, array $axisLabels) use (&$datasetMap, $datasetKey) { foreach ($source[$datasetKey] ?? [] as $dataset) { $datasetLabel = (string) ($dataset['label'] ?? 'Series'); if (!isset($datasetMap[$datasetLabel])) { $datasetMap[$datasetLabel] = $dataset; $datasetMap[$datasetLabel]['dataByLabel'] = []; } foreach (($dataset['data'] ?? []) as $idx => $value) { $axisLabel = $axisLabels[$idx] ?? (string) $idx; $datasetMap[$datasetLabel]['dataByLabel'][$axisLabel] = ($datasetMap[$datasetLabel]['dataByLabel'][$axisLabel] ?? 0) + (int) $value; } } }; $pushDataset($base, $baseLabels); $pushDataset($incoming, $incomingLabels); $datasets = []; foreach ($datasetMap as $dataset) { $dataByLabel = $dataset['dataByLabel'] ?? []; unset($dataset['dataByLabel']); $dataset['data'] = array_map(fn ($label) => (int) ($dataByLabel[$label] ?? 0), $labels); $datasets[] = $dataset; } return [ 'labels' => $labels, $datasetKey => $datasets, ]; }; $sumSeries = fn (array $base, array $incoming): array => $sumDatasetByAxisLabels($base, $incoming, 'series'); $sumChartDatasets = fn (array $base, array $incoming): array => $sumDatasetByAxisLabels($base, $incoming, 'datasets'); $sumDurationSummary = function (array $basePayload, array $incomingPayload): array { $base = $basePayload['durationSummary'] ?? []; $incoming = $incomingPayload['durationSummary'] ?? []; if (empty($base)) return $incoming; if (empty($incoming)) return $base; $cardTotal = function (array $payload): int { foreach ($payload['monthlyChecks'] ?? [] as $card) { if (($card['title'] ?? null) === 'Total check-ins') { return (int) ($card['value'] ?? 0); } } return 0; }; $baseWeight = $cardTotal($basePayload); $incomingWeight = $cardTotal($incomingPayload); $totalWeight = $baseWeight + $incomingWeight; $average = $totalWeight > 0 ? (int) round((((int) ($base['average'] ?? 0) * $baseWeight) + ((int) ($incoming['average'] ?? 0) * $incomingWeight)) / $totalWeight) : 0; $longWaiters = (int) ($base['longWaiters'] ?? 0) + (int) ($incoming['longWaiters'] ?? 0); $base['average'] = $average; $base['longWaiters'] = $longWaiters; $base['longWaitRate'] = $totalWeight > 0 ? (int) round(($longWaiters / $totalWeight) * 100) : 0; $base['median'] = null; $base['p90'] = null; $base['aggregateNote'] = 'Median and p90 are not aggregated across clinics from cache.'; return $base; }; foreach (array_slice($payloads, 1) as $data) { $agg['monthlyChecks'] = $sumCards($agg['monthlyChecks'] ?? [], $data['monthlyChecks'] ?? []); $agg['durationSummary'] = $sumDurationSummary($agg, $data); $agg['unplan'] = ($agg['unplan'] ?? 0) + ($data['unplan'] ?? 0); $agg['planned'] = ($agg['planned'] ?? 0) + ($data['planned'] ?? 0); $agg['consultationSummary'] = $sumAssoc($agg['consultationSummary'] ?? [], $data['consultationSummary'] ?? []); $agg['periodGenderBreakdown'] = $sumCards($agg['periodGenderBreakdown'] ?? [], $data['periodGenderBreakdown'] ?? []); $agg['periodKpBreakdown'] = $sumCards($agg['periodKpBreakdown'] ?? [], $data['periodKpBreakdown'] ?? []); $agg['programCategories'] = $sumCategories($agg['programCategories'] ?? [], $data['programCategories'] ?? []); $agg['diseaseCategories'] = $sumCategories($agg['diseaseCategories'] ?? [], $data['diseaseCategories'] ?? []); $agg['monthlyAgeSummary'] = $sumAssoc($agg['monthlyAgeSummary'] ?? [], $data['monthlyAgeSummary'] ?? []); $agg['monthlyClerkSummary'] = $sumCards($agg['monthlyClerkSummary'] ?? [], $data['monthlyClerkSummary'] ?? []); $agg['monthlyClerkHours'] = $sumSeries($agg['monthlyClerkHours'] ?? [], $data['monthlyClerkHours'] ?? []); $agg['monthlyHourlyLoad'] = $sumSeries($agg['monthlyHourlyLoad'] ?? [], $data['monthlyHourlyLoad'] ?? []); $agg['mdStats'] = $sumMdStats($agg['mdStats'] ?? [], $data['mdStats'] ?? []); $agg['monthlyMdTrend'] = $sumChartDatasets($agg['monthlyMdTrend'] ?? [], $data['monthlyMdTrend'] ?? []); $agg['timeInClinicDailyBands'] = $sumChartDatasets($agg['timeInClinicDailyBands'] ?? [], $data['timeInClinicDailyBands'] ?? []); } return $agg; } private function buildCacheKey(string $clinicConnection, string $periodKey): string { return 'dash-v5|' . $clinicConnection . '|' . $periodKey; } private function decryptGeneralValue($raw): ?string { $value = is_string($raw) ? trim($raw) : null; if ($value === null || $value === '') { return null; } try { return Crypt::decrypt_light($value, 'General'); } catch (\Throwable $e) { return $value; } } private function buildQuarterCacheMeta(string $clinicConnection, array $years): array { if ($clinicConnection === 'ALL') { return []; } $quarterStartMap = [1 => 1, 2 => 4, 3 => 7, 4 => 10]; $today = Carbon::today(); $periodToCacheKey = []; foreach ($years as $year) { for ($q = 1; $q <= 4; $q++) { $periodKey = sprintf('%d-Q%d', (int) $year, $q); $periodToCacheKey[$periodKey] = $this->buildCacheKey($clinicConnection, $periodKey); } } if (empty($periodToCacheKey)) { return []; } $cacheQuery = ReportCache::where('period_type', 'quarterly') ->where(function ($q) use ($clinicConnection, $years) { foreach ($years as $year) { $q->orWhere('period_key', 'like', '%|' . $clinicConnection . '|' . ((int) $year) . '-Q%'); } }); $caches = $cacheQuery->get(['period_key', 'updated_at']); $byPeriod = []; foreach ($caches as $row) { $key = (string) ($row->period_key ?? ''); $parts = explode('|', $key); if (count($parts) < 3) { continue; } $periodKey = $parts[2]; if (!isset($periodToCacheKey[$periodKey])) { continue; } $expectedKey = $periodToCacheKey[$periodKey]; $bucket = &$byPeriod[$periodKey]; if (!isset($bucket)) { $bucket = ['current' => null, 'legacy' => null]; } $updatedAt = $row->updated_at ? Carbon::parse($row->updated_at) : null; if ($updatedAt === null) { continue; } $slot = ($key === $expectedKey) ? 'current' : 'legacy'; if (!isset($bucket[$slot]) || $bucket[$slot]->lt($updatedAt)) { $bucket[$slot] = $updatedAt; } } $meta = []; foreach ($periodToCacheKey as $periodKey => $cacheKey) { $bucket = $byPeriod[$periodKey] ?? null; $updatedAt = $bucket['current'] ?? null; $isLegacyOnly = false; if ($updatedAt === null) { $updatedAt = $bucket['legacy'] ?? null; $isLegacyOnly = $updatedAt !== null; } if ($updatedAt === null) { $meta[$periodKey] = ['status' => 'missing', 'updated_at' => null]; continue; } $year = (int) strtok($periodKey, '-'); $q = (int) (explode('-Q', $periodKey)[1] ?? 0); $startMonth = $quarterStartMap[$q] ?? 1; $start = Carbon::create($year, $startMonth, 1); $end = $start->copy()->addMonths(3)->subDay(); $isOngoing = $today->betweenIncluded($start, $end) && $today->lt($end); $expiry = $isOngoing ? Carbon::now()->subHours(12) : Carbon::now()->subMonths(6); $meta[$periodKey] = [ 'status' => $isLegacyOnly ? 'legacy' : ($updatedAt->greaterThanOrEqualTo($expiry) ? 'fresh' : 'stale'), 'updated_at' => $updatedAt->toDateTimeString(), ]; } return $meta; } private function normalizeGender($raw): string { $value = strtolower(trim((string) ($raw ?? ''))); if ($value === '') { return 'Unknown'; } // IMPORTANT: check Female before Male because "female" contains "male" as a substring. if (in_array($value, ['f', 'female', 'woman', 'girl'], true) || str_contains($value, 'female')) { return 'Female'; } if (in_array($value, ['m', 'male', 'man', 'boy'], true) || preg_match('/\\bmale\\b/', $value)) { return 'Male'; } return 'Unknown'; } private function normalizeRiskCategory($raw): string { $value = strtolower(trim((string) ($raw ?? ''))); if ($value === '') { return 'General patients'; } $normalized = preg_replace('/[^a-z0-9]+/', '', $value) ?? ''; if ($normalized === '' || in_array($normalized, ['nonkp', 'nonkeypopulation', 'nonkpclients'], true)) { return 'General patients'; } if (str_contains($normalized, 'client') && str_contains($normalized, 'fsw')) { return 'Client of FSW'; } if ($normalized === 'fsw' || str_contains($normalized, 'fsw')) { return 'FSW'; } if ($normalized === 'msm' || str_contains($normalized, 'msm')) { return 'MSM'; } if ($normalized === 'tg' || str_contains($normalized, 'transgender') || str_contains($normalized, 'tg')) { return 'TG'; } if (str_contains($normalized, 'pwid') || str_contains($normalized, 'idu')) { return 'PWID'; } if (str_contains($normalized, 'pwud') || str_contains($normalized, 'druguser')) { return 'PWUD'; } if (str_contains($normalized, 'partnerofkp') || (str_contains($normalized, 'partner') && str_contains($normalized, 'kp'))) { return 'Partner of KP'; } if (str_contains($normalized, 'partnerofplhiv') || (str_contains($normalized, 'partner') && str_contains($normalized, 'plhiv'))) { return 'Partner of PLHIV'; } if (str_contains($normalized, 'specialgroup')) { return 'Special Groups'; } if (str_contains($normalized, 'migrant')) { return 'Migrant Population'; } return 'General patients'; } public function yearlyMapPoints(Request $request) { $clinic = strtoupper(trim((string) $request->query('clinic', ''))); $mode = strtolower(trim((string) $request->query('mode', ''))); $year = (string) $request->query('year', ''); $selectedYear = preg_match('/^\d{4}$/', $year) ? (int) $year : (int) Carbon::today()->year; $sharedMapSource = 'data_source_fuchiaDatabase/Map_Source/HTY/HTY_combined_deduplicated-columnD-cleaned-rules12-noHTY-township-cleaned-village-validated.csv'; $mapSources = [ 'MAM_SPT' => 'data_source_fuchiaDatabase/Map_Source/SPT/SPT_registration_export-03-06-26-cleaned-village-validated.csv', 'MAM_SDG' => 'data_source_fuchiaDatabase/Map_Source/SDG/SDG_registration_export-30-07-26-cleaned-step2-map-source.csv', 'MAM_TL' => 'data_source_fuchiaDatabase/Map_Source/TL/TL_registration_export-30-07-26-cleaned-step2-map-source.csv', ]; $clinicScopes = [ 'MAM_A' => ['MAM_A'], 'MAM_B' => ['MAM_B'], 'MAM_C1' => ['MAM_C1'], 'MAM_SPT' => ['MAM_SPT'], 'MAM_SDG' => ['MAM_SDG'], 'MAM_TL' => ['MAM_TL'], 'MAM_A+B+C1' => ['MAM_A', 'MAM_B', 'MAM_C1'], ]; if (!isset($clinicScopes[$clinic]) || $mode !== 'yearly') { return response()->json([ 'meta' => [ 'enabled' => false, 'reason' => 'Mapping points are enabled only for supported clinics in yearly mode.', 'year' => $year, ], 'records' => [], ]); } $scopeClinics = $clinicScopes[$clinic]; $startDate = Carbon::create($selectedYear, 1, 1)->toDateString(); $endDate = Carbon::create($selectedYear, 12, 31)->toDateString(); $includedPidLookupByClinic = []; $includedPidTotal = 0; foreach ($scopeClinics as $scopeClinic) { $clinicConnection = $this->resolveClinicConnection($scopeClinic); if (!$this->connectionIsUsable($clinicConnection)) { return response()->json([ 'meta' => [ 'enabled' => false, 'reason' => "Clinic connection '{$clinicConnection}' is not available.", 'year' => (string) $selectedYear, ], 'records' => [], ], 503); } try { $includedPids = Followup_general::on($clinicConnection) ->whereBetween('Visit Date', [$startDate, $endDate]) ->whereNotNull('Pid') ->distinct() ->pluck('Pid') ->map(fn ($pid) => trim((string) $pid)) ->filter(fn ($pid) => $pid !== '') ->values() ->all(); } catch (\Throwable $e) { return response()->json([ 'meta' => [ 'enabled' => false, 'reason' => "Unable to load yearly PID list from clinic '{$scopeClinic}'.", 'year' => (string) $selectedYear, ], 'records' => [], ], 503); } $includedPidLookupByClinic[$scopeClinic] = array_fill_keys($includedPids, true); $includedPidTotal += count($includedPids); } $mapSource = $mapSources[$clinic] ?? $sharedMapSource; $sourcePath = base_path($mapSource); if (!is_readable($sourcePath)) { return response()->json([ 'meta' => [ 'enabled' => false, 'reason' => 'Shared source registration file is not readable.', 'year' => $year, ], 'records' => [], ], 404); } $buildPayload = function () use ($clinic, $scopeClinics, $sourcePath, $selectedYear, $includedPidLookupByClinic, $includedPidTotal) { $records = []; $matched = 0; $skippedNotInYear = 0; $resolveRowClinic = function (string $pid) use ($scopeClinics, $includedPidLookupByClinic): ?string { foreach ($scopeClinics as $scopeClinic) { $lookup = $includedPidLookupByClinic[$scopeClinic] ?? []; if (isset($lookup[$pid])) { return $scopeClinic; } } return null; }; $handle = fopen($sourcePath, 'r'); if ($handle === false) { return [ 'meta' => [ 'enabled' => false, 'clinic' => $clinic, 'year' => (string) $selectedYear, 'source' => basename($sourcePath), 'records' => 0, 'included_yearly_pids' => $includedPidTotal, 'matched_excel_pids' => 0, 'excluded_not_in_year' => 0, 'reason' => 'Unable to open shared CSV source.', ], 'records' => [], ]; } fgetcsv($handle); // header while (($rowData = fgetcsv($handle)) !== false) { $pid = trim((string) ($rowData[0] ?? '')); $region = trim((string) ($rowData[1] ?? '')); $township = trim((string) ($rowData[2] ?? '')); $wardRaw = trim((string) ($rowData[3] ?? '')); $latRaw = trim((string) ($rowData[4] ?? '')); $lngRaw = trim((string) ($rowData[5] ?? '')); if ($pid === '' || $wardRaw === '') { continue; } $rowClinic = $resolveRowClinic($pid); if ($rowClinic === null) { $skippedNotInYear++; continue; } $lat = is_numeric($latRaw) ? (float) $latRaw : null; $lng = is_numeric($lngRaw) ? (float) $lngRaw : null; $wardCode = null; $villageName = null; if (preg_match('/(\d+)\s*w/i', $wardRaw, $match)) { $wardCode = ((int) $match[1]) . 'w'; } elseif ($lat === null || $lng === null) { continue; } else { $villageName = $wardRaw; } $records[] = [ 'id' => $pid, 'clinic' => $rowClinic, 'ward' => $wardCode, 'village' => $villageName, 'township' => $township, 'region' => $region, 'lat' => $lat, 'lng' => $lng, ]; $matched++; } fclose($handle); return [ 'meta' => [ 'enabled' => true, 'clinic' => $clinic, 'year' => (string) $selectedYear, 'source' => basename($sourcePath), 'records' => count($records), 'included_yearly_pids' => $includedPidTotal, 'matched_excel_pids' => $matched, 'excluded_not_in_year' => $skippedNotInYear, ], 'records' => $records, ]; }; $payload = null; $mtime = (string) (@filemtime($sourcePath) ?: '0'); $cacheKey = 'clinic_map_points_yearly_v3_' . md5($clinic . '|' . $selectedYear . '|' . $sourcePath . '|' . $mtime); try { $payload = Cache::remember($cacheKey, now()->addMinutes(20), $buildPayload); } catch (\Throwable $e) { // Fallback path when cache storage is not writable. $payload = $buildPayload(); } return response()->json($payload); } private function connectionIsUsable(string $connection): bool { try { DB::connection($connection)->getPdo(); return true; } catch (\Throwable $e) { return !$this->isUnknownDatabaseThrowable($e); } } private function isUnknownDatabaseThrowable(\Throwable $e): bool { if ($e instanceof \Illuminate\Database\QueryException) { $errorInfo = $e->errorInfo; if (is_array($errorInfo) && isset($errorInfo[1]) && (int) $errorInfo[1] === 1049) { return true; } } return str_contains($e->getMessage(), 'Unknown database'); } }