onboard/app/Services/MetrikaService.php

<?php

namespace App\Services;

use App\Models\MedicalHistory;
use App\Models\MedicalHistorySnapshot;
use App\Models\Report;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class MetrikaService
{
    /**
     * Рассчитать предоперационный койко-день из снапшотов
     */
    public function calculatePreoperativeDaysFromSnapshots(
        array $departmentIds,
        string $startDate,
        string $endDate
    ): array {
        if (empty($departmentIds)) {
            return [];
        }

        try {
            $reports = Report::query()
                ->whereIn('rf_department_id', $departmentIds)
                ->where('period_start', '>=', $startDate)
                ->where('period_end', '<', $endDate)
                ->get(['report_id', 'rf_department_id'])
                ->groupBy('rf_department_id');

            $preoperativeDays = [];
            foreach ($departmentIds as $deptId) {
                $reportIds = $reports->get($deptId)?->pluck('report_id')->all() ?? [];

                if ($reportIds === []) {
                    $preoperativeDays[$deptId] = 0;

                    continue;
                }

                $historyIds = MedicalHistorySnapshot::query()
                    ->whereIn('rf_report_id', $reportIds)
                    ->whereIn('patient_type', ['discharged', 'deceased'])
                    ->pluck('rf_medicalhistory_id')
                    ->filter()
                    ->unique()
                    ->values();

                if ($historyIds->isEmpty()) {
                    $preoperativeDays[$deptId] = 0;

                    continue;
                }

                $histories = MedicalHistory::query()
                    ->whereIn('original_id', $historyIds)
                    ->with(['operations'])
                    ->get();

                $totalDays = 0;
                $count = 0;

                foreach ($histories as $history) {
                    $operationDate = $history->operations
                        ->pluck('operation_date')
                        ->filter()
                        ->sort()
                        ->first();

                    if (! $history->recipient_date || ! $operationDate) {
                        continue;
                    }

                    $admission = Carbon::parse($history->recipient_date);
                    $operation = Carbon::parse($operationDate);
                    $days = $admission->diffInDays($operation);

                    if ($days >= 0) {
                        $totalDays += $days;
                        $count++;
                    }
                }

                $preoperativeDays[$deptId] = $count > 0 ? round($totalDays / $count, 1) : 0;
            }

            return $preoperativeDays;

        } catch (\Exception $e) {
            \Log::error('Error in calculatePreoperativeDaysFromSnapshots: '.$e->getMessage());

            return array_fill_keys($departmentIds, 0);
        }
    }

    public function getMetricsForReport(Report $report): array
    {
        $metrics = [];

        foreach ($report->metrikaResults as $metrikaResult) {
            $metrikaResult->load('metrikaItem');
            $metrics[] = [
                'metrika_id' => $metrikaResult->rf_metrika_item_id,
                'value_id' => $metrikaResult->metrika_result_id,
                'name' => $metrikaResult->metrikaItem->name,
                'value' => $metrikaResult->value,
            ];
        }

        return $metrics;
    }

    /**
     * Подсчитывает базовую статистику для отчетов дежурного врача
     * Поступило, выбыло, состоит и мед. персонал
     * @param Collection $reportsDuty
     * @return array
     */
    public function calculateBaseStatistics(Collection $reportsDuty): array
    {
        $reportIds = $reportsDuty->pluck('id'); // получаем ID всех отчётов

        // Один запрос, который сразу возвращает суммы по каждому типу
        $totals = DB::table('duty_report_metric_results')
            ->whereIn('rf_report_id', $reportIds) // предполагаем внешний ключ `report_id`
            ->whereIn('rf_metrika_item_id', [3, 15, 17])
            ->select('rf_metrika_item_id', DB::raw('SUM(value::integer) as total'))
            ->groupBy('rf_metrika_item_id')
            ->pluck('total', 'rf_metrika_item_id')
            ->toArray();

        // Получаем последний отчёт из коллекции (по полю report_date)
        $lastReport = $reportsDuty->sortByDesc('report_date')->first();
        $currentValue = 0;

        if ($lastReport) {
            $currentValue = DB::table('duty_report_metric_results')
                ->where('rf_report_id', $lastReport->id)
                ->where('rf_metrika_item_id', 8)
                ->value('value');
        }

        // Формируем итоговый массив с ключами, как у вас
        return [
            'recipient' => $totals[3] ?? 0,
            'outcome'   => $totals[7] ?? 0,
            'current'   => intval($currentValue),
            'staff'     => $totals[17] ?? 0,
        ];
    }
}