strprfcrm/app/Services/ExportReclamationsService.php

<?php

namespace App\Services;

use App\Models\File;
use App\Models\PricingCode;
use App\Models\Product;
use App\Models\Reclamation;
use App\Models\SparePart;
use App\Helpers\DateHelper;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportReclamationsService
{
    private const TEMPLATE = './templates/ReclamationsExport.xlsx';
    private const BASE_PRICING_CODE = '14.20-38-1';

    public function handle(array $reclamationIds, int $userId): string
    {
        $reader = IOFactory::createReader('Xlsx');
        $spreadsheet = $reader->load(self::TEMPLATE);
        $sheet = $spreadsheet->getActiveSheet();
        $templateLastRow = $sheet->getHighestRow();
        $templateStyle = $sheet->getStyle('A2:X2');

        $reclamations = Reclamation::query()
            ->whereIn('id', $reclamationIds)
            ->with([
                'order.district',
                'order.area',
                'skus.product' => fn($q) => $q->withoutGlobalScopes(),
                'spareParts.pricingCodes',
            ])
            ->get()
            ->keyBy('id');

        $row = 2;
        foreach ($reclamationIds as $reclamationId) {
            $reclamation = $reclamations->get($reclamationId);
            if (!$reclamation) {
                continue;
            }

            $base = $this->buildBaseRow($reclamation);

            $groupRows = $this->buildGroupRows($reclamation, $base);
            foreach ($groupRows as $group) {
                $count = max(count($group), 1);
                for ($i = 0; $i < $count; $i++) {
                    $data = $group[$i] ?? $base;
                    $sheet->duplicateStyle($templateStyle, 'A' . $row . ':X' . $row);
                    $this->writeRow($sheet, $row, $data);
                    $row++;
                }
            }
        }

        if ($row <= $templateLastRow) {
            $sheet->removeRow($row, $templateLastRow - $row + 1);
        }

        $this->applyBorders($sheet, 1, $row - 1);
        $this->applyDefaultStyles($sheet, 2, $row - 1);

        $fileName = fileName('Рекламации ' . date('Y-m-d H-i-s') . '.xlsx');
        $fd = 'export/reclamations';
        Storage::disk('public')->makeDirectory($fd);
        $fp = storage_path('app/public/' . $fd . '/') . $fileName;
        Storage::disk('public')->delete($fd . '/' . $fileName);

        (new Xlsx($spreadsheet))->save($fp);

        $link = url('/storage/' . $fd . '/' . $fileName);

        File::query()->create([
            'link' => $link,
            'path' => $fp,
            'user_id' => $userId,
            'original_name' => $fileName,
            'mime_type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);

        return $link;
    }

    private function buildBaseRow(Reclamation $reclamation): array
    {
        $order = $reclamation->order;

        return [
            'A' => null, // № папки
            'B' => $order?->district?->shortname ?? $order?->district_name ?? null, // Округ
            'C' => $order?->area?->name ?? $order?->area_name ?? null, // Район
            'D' => $order?->object_address ?? null, // Адрес
            'E' => null, // Артикул
            'F' => null, // Тип
            'G' => null, // Наименование МАФ по паспорту
            'H' => $reclamation->whats_done ?? null, // Что сделали
            'I' => $reclamation->create_date ?? null, // Дата заявки
            'J' => $order?->year ?? null, // Год поставки МАФ
            'K' => $reclamation->reason ?? null, // Тип обращения
            'L' => null, // Применяемая деталь
            'M' => null, // Кол-во
            'N' => null, // Цена по КС
            'O' => null, // Лимит по МАФ
            'P' => null, // Цена детали
            'Q' => null, // Подтверждение цены
            'R' => null, // Шифр 1
            'S' => null, // Наименование 1
            'T' => null, // Шифр 2
            'U' => null, // Наименование 2
            'V' => null, // Шифр 3
            'W' => null, // Наименование 3
            'X' => $reclamation->comment ?? null, // Комментарий
        ];
    }

    private function buildGroupRows(Reclamation $reclamation, array $base): array
    {
        $skus = $reclamation->skus;
        $spareParts = $reclamation->spareParts;
        $details = $reclamation->details;

        $groups = [];

        if ($skus->isEmpty()) {
            $baseDescription = PricingCode::getPricingCodeDescription(self::BASE_PRICING_CODE);
            $codes = [
                [self::BASE_PRICING_CODE, $baseDescription],
            ];
            $groups[] = [$this->applyPricingCodes($this->buildRowForMaf($base, null), $codes)];
            return $groups;
        }

        foreach ($skus as $idx => $sku) {
            $mafRow = $this->buildRowForMaf($base, $sku);
            if ($idx > 0) {
                $this->blankColumns($mafRow, ['A', 'B', 'C', 'D']);
            }
            $detailRows = $this->buildDetailRows($mafRow, $spareParts, $details);
            $groups[] = $detailRows;
        }

        return $groups;
    }

    private function buildRowForMaf(array $base, $sku): array
    {
        if (!$sku || !$sku->product) {
            return $base;
        }

        $product = $sku->product;
        $year = $sku->year ?? $product->year;

        $passportName = null;
        if ($product->article && $year) {
            $productForYear = Product::withoutGlobalScopes()
                ->where('year', $year)
                ->where('article', $product->article)
                ->first();
            $passportName = $productForYear?->passport_name;
        }

        $row = $base;
        $row['E'] = $product->article;
        $row['F'] = $product->article;
        $row['G'] = $passportName;

        return $row;
    }

    private function buildDetailRows(array $mafRow, $spareParts, $details): array
    {
        $rows = [];

        $items = [];
        foreach ($spareParts as $sp) {
            $items[] = ['type' => 'spare', 'data' => $sp];
        }
        foreach ($details as $detail) {
            $items[] = ['type' => 'detail', 'data' => $detail];
        }

        if (empty($items)) {
            $baseDescription = PricingCode::getPricingCodeDescription(self::BASE_PRICING_CODE);
            $codes = [
                [self::BASE_PRICING_CODE, $baseDescription],
            ];
            $rows[] = $this->applyPricingCodes($mafRow, $codes);
            return $rows;
        }

        foreach ($items as $index => $item) {
            $row = $mafRow;
            if ($index > 0) {
                $this->blankColumns($row, ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'X']);
            }
            if ($item['type'] === 'spare') {
                $rows[] = $this->buildRowsForSparePart($row, $item['data']);
            } else {
                $rows[] = $this->buildRowForDetail($row, $item['data']);
            }
        }

        return $rows;
    }

    private function buildRowsForSparePart(array $mafRow, SparePart $sp): array
    {
        $row = $mafRow;
        $row['L'] = $sp->article;
        $row['M'] = $sp->pivot?->quantity ?? null;
        $row['P'] = $sp->expertise_price;
        $row['Q'] = $sp->tsn_number;

        $codes = [];
        $baseDescription = PricingCode::getPricingCodeDescription(self::BASE_PRICING_CODE);
        $codes[] = [self::BASE_PRICING_CODE, $baseDescription];

        foreach ($sp->pricingCodes as $pc) {
            if ($pc->type !== PricingCode::TYPE_PRICING_CODE) {
                continue;
            }
            if ($pc->code === self::BASE_PRICING_CODE) {
                continue;
            }
            $codes[] = [$pc->code, $pc->description];
        }

        return $this->applyPricingCodes($row, $codes);
    }

    private function buildRowForDetail(array $mafRow, $detail): array
    {
        $row = $mafRow;
        $row['L'] = $detail->name;
        $row['M'] = $detail->quantity;
        $row['P'] = null;
        $row['Q'] = null;

        $baseDescription = PricingCode::getPricingCodeDescription(self::BASE_PRICING_CODE);
        $codes = [
            [self::BASE_PRICING_CODE, $baseDescription],
        ];

        return $this->applyPricingCodes($row, $codes);
    }

    private function applyPricingCodes(array $row, array $codes): array
    {
        $slots = [
            ['R', 'S'],
            ['T', 'U'],
            ['V', 'W'],
        ];

        for ($i = 0; $i < count($slots); $i++) {
            $row[$slots[$i][0]] = null;
            $row[$slots[$i][1]] = null;
        }

        $idx = 0;
        foreach ($codes as $code) {
            if ($idx >= count($slots)) {
                break;
            }
            $row[$slots[$idx][0]] = $code[0] ?? null;
            $row[$slots[$idx][1]] = $code[1] ?? null;
            $idx++;
        }

        return $row;
    }

    private function blankColumns(array &$row, array $cols): void
    {
        foreach ($cols as $col) {
            $row[$col] = null;
        }
    }

    private function writeRow(Worksheet $sheet, int $row, array $data): void
    {
        $rowRange = 'A' . $row . ':X' . $row;
        $sheet->getStyle($rowRange)->getAlignment()->setWrapText(true);
        $sheet->getStyle($rowRange)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle($rowRange)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

        foreach ($data as $col => $value) {
            if ($col === 'I') {
                if (!empty($value) && DateHelper::isDate($value)) {
                    $excelDate = DateHelper::ISODateToExcelDate($value);
                    $sheet->setCellValue('I' . $row, $excelDate);
                    $sheet->getStyle('I' . $row)->getNumberFormat()->setFormatCode('m/d/yyyy');
                } else {
                    $sheet->setCellValueExplicit('I' . $row, (string)$value, DataType::TYPE_STRING);
                    $sheet->getStyle('I' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
                }
                continue;
            }

            if ($col === 'P') {
                if(isset($value) && is_numeric($value)) {
                    $sheet->setCellValue('P' . $row, (float)$value);
                }
                $sheet->getStyle('P' . $row)->getNumberFormat()->setFormatCode('#,##0.00\\₽');
                continue;
            }

            if ($value === null || $value === '') {
                $sheet->setCellValue($col . $row, null);
                continue;
            }

            $sheet->setCellValueExplicit($col . $row, (string)$value, DataType::TYPE_STRING);
            $sheet->getStyle($col . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
        }
    }

    private function applyBorders(Worksheet $sheet, int $fromRow, int $toRow): void
    {
        $highestCol = $sheet->getHighestColumn();
        $range = 'A' . $fromRow . ':' . $highestCol . $toRow;
        $sheet->getStyle($range)
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN)
            ->setColor(new Color('777777'));
    }

    private function applyDefaultStyles(Worksheet $sheet, int $fromRow, int $toRow): void
    {
        if ($toRow < $fromRow) {
            return;
        }

        $highestCol = $sheet->getHighestColumn();
        $range = 'A' . $fromRow . ':' . $highestCol . $toRow;
        $sheet->getStyle($range)->getAlignment()->setWrapText(true);
        $sheet->getStyle($range)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle($range)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
    }
}