onboard/app/Services/BedDayService.php

<?php
// app/Services/BedDayService.php

namespace App\Services;

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

class BedDayService
{
    /**
     * ID метрики для среднего койко-дня
     */
    const METRIC_BED_DAYS_ID = 18;

    /**
     * Кэш для хранения результатов запросов
     */
    protected array $cache = [];

    /**
     * Получить средний койко-день для отделения из снапшотов за период
     */
    public function getAverageBedDaysFromSnapshots(int $departmentId, string $startDate, string $endDate, bool $isRangeOneDay): float
    {
        $cacheKey = "snapshots_{$departmentId}_{$startDate}_{$endDate}_" . ($isRangeOneDay ? '1day' : 'range');

        if (isset($this->cache[$cacheKey])) {
            return $this->cache[$cacheKey];
        }

        // Для одного дня берем последние 30 дней для статистической значимости
        $actualStartDate = $isRangeOneDay
            ? Carbon::now('Asia/Yakutsk')->startOfYear()->format('Y-m-d')
            : $startDate;

        // Находим отчеты за период
        $reports = Report::where('rf_department_id', $departmentId)
//            ->whereBetween('created_at', [$actualStartDate, $endDate])
            ->where('sent_at', '>=', $actualStartDate)
            ->where('sent_at', '<=', $endDate)
            ->pluck('report_id');

        if ($reports->isEmpty()) {
            $this->cache[$cacheKey] = 0;
            return 0;
        }

        // Получаем все снапшоты выписанных пациентов из этих отчетов
        $snapshots = MedicalHistorySnapshot::whereIn('rf_report_id', $reports)
            ->where('patient_type', MedicalHistorySnapshot::PATIENT_TYPE_DISCHARGED)
            ->distinct()
            ->with('medicalHistory')
            ->get();

        if ($snapshots->isEmpty()) {
            $this->cache[$cacheKey] = 0;
            return 0;
        }

        // Рассчитываем средний койко-день
        $totalDays = 0;
        $validCount = 0;

        foreach ($snapshots as $snapshot) {
            $history = $snapshot->medicalHistory;
            if ($history && $history->DateRecipient && $history->DateExtract) {
                $start = Carbon::parse($history->DateRecipient);
                $end = Carbon::parse($history->DateExtract);

                // Проверяем, что дата выписки входит в период отчета
                if ($end->between($actualStartDate, $endDate)) {
                    $days = $start->diffInDays($end);
                    $totalDays += $days;
                    $validCount++;
                }
            }
        }

        $avgDays = $validCount > 0 ? round($totalDays / $validCount, 1) : 0;
        $this->cache[$cacheKey] = $avgDays;

        return $avgDays;
    }

    /**
     * Получить средние койко-дни для всех отделений из снапшотов
     */
    public function getAverageBedDaysByDepartmentsFromSnapshots(array $departmentIds, string $startDate, string $endDate, bool $isRangeOneDay): array
    {
        if (empty($departmentIds)) {
            return [];
        }

        $cacheKey = 'all_snapshots_' . md5(implode(',', $departmentIds) . $startDate . $endDate . ($isRangeOneDay ? '1day' : 'range'));

        if (isset($this->cache[$cacheKey])) {
            return $this->cache[$cacheKey];
        }

        $actualStartDate = $isRangeOneDay
            ? Carbon::parse($endDate)->subDays(30)->format('Y-m-d')
            : $startDate;

        // Находим все отчеты за период по отделениям
        $reportsByDepartment = Report::whereIn('rf_department_id', $departmentIds)
//            ->whereBetween('created_at', [$actualStartDate, $endDate])
            ->where('sent_at', '>=', $actualStartDate)
            ->where('sent_at', '<=', $endDate)
            ->select('report_id', 'rf_department_id')
            ->get()
            ->groupBy('rf_department_id');

        $averages = [];

        foreach ($departmentIds as $departmentId) {
            $departmentReports = $reportsByDepartment->get($departmentId, collect());

            if ($departmentReports->isEmpty()) {
                $averages[$departmentId] = 0;
                continue;
            }

            $reportIds = $departmentReports->pluck('report_id')->toArray();

            // Получаем снапшоты для отчетов отделения
            $snapshots = MedicalHistorySnapshot::whereIn('rf_report_id', $reportIds)
                ->where('patient_type', MedicalHistorySnapshot::PATIENT_TYPE_DISCHARGED)
                ->distinct()
                ->with('medicalHistory')
                ->get();

            if ($snapshots->isEmpty()) {
                $averages[$departmentId] = 0;
                continue;
            }

            $totalDays = 0;
            $validCount = 0;

            foreach ($snapshots as $snapshot) {
                $history = $snapshot->medicalHistory;
                if ($history && $history->DateRecipient && $history->DateExtract) {
                    $end = Carbon::parse($history->DateExtract);
                    if ($end->between($actualStartDate, $endDate)) {
                        $start = Carbon::parse($history->DateRecipient);
                        $days = $start->diffInDays($end);
                        $totalDays += $days;
                        $validCount++;
                    }
                }
            }

            $averages[$departmentId] = $validCount > 0 ? round($totalDays / $validCount, 1) : 0;
        }

        $this->cache[$cacheKey] = $averages;

        return $averages;
    }

    /**
     * Получить общий средний койко-день из всех снапшотов
     */
    public function getOverallAverageBedDaysFromSnapshots(array $departmentIds, string $startDate, string $endDate, bool $isRangeOneDay): float
    {
        $averages = $this->getAverageBedDaysByDepartmentsFromSnapshots($departmentIds, $startDate, $endDate, $isRangeOneDay);

        $total = 0;
        $count = 0;

        foreach ($averages as $avg) {
            if ($avg > 0) {
                $total += $avg;
                $count++;
            }
        }

        return $count > 0 ? round($total / $count, 1) : 0;
    }

    /**
     * Получить детальную статистику по койко-дням из снапшотов
     */
    public function getDetailedStatsFromSnapshots(int $departmentId, string $startDate, string $endDate): array
    {
        $reports = Report::where('rf_department_id', $departmentId)
//            ->whereBetween('created_at', [$startDate, $endDate])
            ->where('sent_at', '>', $startDate)
            ->where('sent_at', '<=', $endDate)
            ->pluck('report_id');

        if ($reports->isEmpty()) {
            return [
                'department_id' => $departmentId,
                'period' => ['start' => $startDate, 'end' => $endDate],
                'total_patients' => 0,
                'average_bed_days' => 0,
                'distribution' => [],
                'by_month' => []
            ];
        }

        $snapshots = MedicalHistorySnapshot::whereIn('rf_report_id', $reports)
            ->where('patient_type', MedicalHistorySnapshot::PATIENT_TYPE_DISCHARGED)
            ->distinct()
            ->with('medicalHistory')
            ->get();

        if ($snapshots->isEmpty()) {
            return [
                'department_id' => $departmentId,
                'period' => ['start' => $startDate, 'end' => $endDate],
                'total_patients' => 0,
                'average_bed_days' => 0,
                'distribution' => [],
                'by_month' => []
            ];
        }

        $distribution = [
            '1-3' => 0,
            '4-7' => 0,
            '8-14' => 0,
            '15-21' => 0,
            '22-30' => 0,
            '30+' => 0
        ];

        $byMonth = [];
        $totalDays = 0;
        $validCount = 0;

        foreach ($snapshots as $snapshot) {
            $history = $snapshot->medicalHistory;
            if ($history && $history->DateRecipient && $history->DateExtract) {
                $end = Carbon::parse($history->DateExtract);
                if ($end->between($startDate, $endDate)) {
                    $start = Carbon::parse($history->DateRecipient);
                    $days = $start->diffInDays($end);

                    $totalDays += $days;
                    $validCount++;

                    // Распределение
                    if ($days <= 3) $distribution['1-3']++;
                    elseif ($days <= 7) $distribution['4-7']++;
                    elseif ($days <= 14) $distribution['8-14']++;
                    elseif ($days <= 21) $distribution['15-21']++;
                    elseif ($days <= 30) $distribution['22-30']++;
                    else $distribution['30+']++;

                    // По месяцам
                    $month = $end->format('Y-m');
                    if (!isset($byMonth[$month])) {
                        $byMonth[$month] = ['total' => 0, 'count' => 0];
                    }
                    $byMonth[$month]['total'] += $days;
                    $byMonth[$month]['count']++;
                }
            }
        }

        // Рассчитываем средние по месяцам
        $monthlyStats = [];
        foreach ($byMonth as $month => $data) {
            $monthlyStats[] = [
                'month' => $month,
                'avg_days' => round($data['total'] / $data['count'], 1),
                'count' => $data['count']
            ];
        }

        return [
            'department_id' => $departmentId,
            'period' => ['start' => $startDate, 'end' => $endDate],
            'total_patients' => $validCount,
            'average_bed_days' => $validCount > 0 ? round($totalDays / $validCount, 1) : 0,
            'distribution' => $distribution,
            'by_month' => $monthlyStats
        ];
    }

    /**
     * Обновить метрики для всех отчетов на основе снапшотов
     */
    public function updateAllMetricsFromSnapshots(): array
    {
        $reports = Report::all();
        $results = [];

        foreach ($reports as $report) {
            // Для каждого отчета считаем средний койко-день за последние 30 дней до даты отчета
            $endDate = $report->created_at;
            $startDate = Carbon::startOfYear();

            $avg = $this->getAverageBedDaysFromSnapshots(
                $report->rf_department_id,
                $startDate,
                $endDate,
                false
            );

            // Сохраняем в метрики
            MetrikaResult::updateOrCreate(
                [
                    'rf_report_id' => $report->report_id,
                    'rf_metrika_item_id' => self::METRIC_BED_DAYS_ID,
                ],
                ['value' => $avg]
            );

            $results[$report->report_id] = $avg;
        }

        return $results;
    }

    /**
     * Очистить кэш памяти
     */
    public function clearMemoryCache(): void
    {
        $this->cache = [];
    }
}