strprfcrm/app/Services/ContractorSpecificationService.php

<?php

namespace App\Services;

use App\Models\Contractor;
use App\Models\ContractorInstallationPrice;
use App\Models\Order;
use App\Models\ProductSKU;
use Illuminate\Support\Carbon;
use Illuminate\Validation\ValidationException;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ContractorSpecificationService
{
    public function generate(Order $order, Contractor $contractor, array $data): string
    {
        $skus = ProductSKU::query()
            ->with('product')
            ->where('order_id', $order->id)
            ->whereIn('id', $data['skus'])
            ->get();

        if ($skus->isEmpty()) {
            throw ValidationException::withMessages([
                'skus' => 'Выберите хотя бы один МАФ.',
            ]);
        }

        $items = $this->buildItems($skus, $contractor);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Спецификация');

        $specDate = Carbon::parse($data['specification_date']);
        $workStart = !empty($data['work_start_date']) ? Carbon::parse($data['work_start_date']) : null;
        $workEnd = !empty($data['work_end_date']) ? Carbon::parse($data['work_end_date']) : null;

        $sheet->setCellValue('A2', 'Договор №' . $contractor->contract_number . ' от ' . $contractor->contract_date->format('d.m.Y') . ' г.');
        $sheet->setCellValue('A5', 'Спецификация №' . $data['specification_number'] . ' от ' . $specDate->format('d.m.Y') . ' г.');
        $sheet->setCellValue('A9', $contractor->contract_header);
        $sheet->setCellValue('A12', 'г. Москва, ' . $order->common_name);

        $headers = ['№ п/п', 'Наименование МАФ', 'Цена', 'Ед. изм', 'Кол-во', 'Стоимость'];
        foreach ($headers as $index => $header) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($index + 1) . '14', $header);
        }

        $row = 15;
        $total = 0.0;
        foreach ($items as $index => $item) {
            $sum = $item['price'] * $item['quantity'];
            $total += $sum;

            $sheet->setCellValue('A' . $row, $index + 1);
            $sheet->setCellValue('B' . $row, $item['name']);
            $sheet->setCellValue('C' . $row, $item['price']);
            $sheet->setCellValue('D' . $row, $item['unit']);
            $sheet->setCellValue('E' . $row, $item['quantity']);
            $sheet->setCellValue('F' . $row, $sum);
            $row++;
        }

        $summaryRow = max(21, $row + 1);
        $vatRow = $summaryRow + 1;
        $totalWordsRow = $summaryRow + 2;
        $vatTextRow = $summaryRow + 3;
        $workStartRow = $summaryRow + 4;
        $workEndRow = $summaryRow + 5;
        $contractRow = $summaryRow + 6;
        $legalNameRow = $summaryRow + 9;
        $signerTitleRow = $summaryRow + 10;
        $directorRow = $summaryRow + 13;

        $vat = $this->calculateVat($total, $contractor->tax_rate);

        $sheet->setCellValue('E' . $summaryRow, 'Итого');
        $sheet->setCellValue('F' . $summaryRow, $total);

        if ($vat !== null) {
            $sheet->setCellValue('E' . $vatRow, 'НДС');
            $sheet->setCellValue('F' . $vatRow, $vat);
        }

        $sheet->setCellValue('A' . $totalWordsRow, $this->formatAmountWithWords($total));
        $sheet->mergeCells('A' . $totalWordsRow . ':F' . $totalWordsRow);

        if ($vat === null) {
            $sheet->setCellValue('A' . $vatTextRow, 'Без НДС');
        } else {
            $sheet->setCellValue('A' . $vatTextRow, 'В т.ч. НДС ' . $contractor->tax_rate . '% ' . number_format($vat, 2, ',', ' ') . ' руб.');
        }

        $sheet->setCellValue('A' . $workStartRow, 'Начало работ: ' . ($workStart?->format('d.m.Y') ?? ''));
        $sheet->setCellValue('A' . $workEndRow, 'Окончание работ: ' . ($workEnd?->format('d.m.Y') ?? ''));
        $sheet->setCellValue('A' . $contractRow, 'Договор №' . $contractor->contract_number . ' от ' . $contractor->contract_date->format('d.m.Y') . ' г.');
        $sheet->setCellValue('A' . $legalNameRow, $contractor->legal_name);
        $sheet->setCellValue('A' . $signerTitleRow, $contractor->signer_title);
        $sheet->setCellValue('A' . $directorRow, $contractor->director_name);

        $lastRow = $directorRow;
        $sheet->getStyle('A14:F' . max(14, $row - 1))->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $sheet->getStyle('A14:F14')->getFont()->setBold(true);
        $sheet->getStyle('A14:F14')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('C15:C' . $lastRow)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_00);
        $sheet->getStyle('F15:F' . $lastRow)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_00);
        $sheet->getStyle('A9:A12')->getAlignment()->setWrapText(true);

        foreach (['A' => 10, 'B' => 50, 'C' => 14, 'D' => 12, 'E' => 12, 'F' => 16] as $column => $width) {
            $sheet->getColumnDimension($column)->setWidth($width);
        }

        $safeNumber = preg_replace('/[^0-9A-Za-zА-Яа-я_-]+/u', '_', (string) $data['specification_number']);
        $path = storage_path('app/specification-' . $order->id . '-' . $contractor->id . '-' . $safeNumber . '.xlsx');
        (new Xlsx($spreadsheet))->save($path);

        return $path;
    }

    private function buildItems($skus, Contractor $contractor): array
    {
        $grouped = [];
        foreach ($skus as $sku) {
            if (!$sku->product) {
                continue;
            }

            $article = $sku->product->article;
            $grouped[$article] ??= [
                'product' => $sku->product,
                'quantity' => 0,
            ];
            $grouped[$article]['quantity']++;
        }

        $items = [];
        $missing = [];

        foreach ($grouped as $article => $group) {
            $product = $group['product'];
            $price = ContractorInstallationPrice::query()
                ->where('contractor_id', $contractor->id)
                ->where('product_id', $product->id)
                ->where('catalog_year', $product->year)
                ->first();

            if (!$price || $price->price <= 0) {
                $missing[] = $article;
                continue;
            }

            $items[] = [
                'article' => $article,
                'name' => $price->name_in_spec ?: $product->name_tz,
                'price' => $price->price,
                'unit' => $product->unit,
                'quantity' => $group['quantity'],
            ];
        }

        if ($missing) {
            throw ValidationException::withMessages([
                'skus' => 'Нет цены монтажа у подрядчика для артикулов: ' . implode(', ', array_unique($missing)),
            ]);
        }

        return $items;
    }

    private function calculateVat(float $total, string $taxRate): ?float
    {
        if ($taxRate === Contractor::TAX_WITHOUT_VAT) {
            return null;
        }

        $rate = (float) $taxRate;

        return round($total / (1 + $rate / 100) * ($rate / 100), 2);
    }

    private function formatAmountWithWords(float $amount): string
    {
        $rubles = (int) floor($amount);
        $kopecks = (int) round(($amount - $rubles) * 100);

        return number_format($rubles, 0, ',', ' ') . ' рублей ' . sprintf('%02d', $kopecks) . ' копеек (' .
            $this->numberToWords($rubles) . ' рублей ' . sprintf('%02d', $kopecks) . ' копеек)';
    }

    private function numberToWords(int $number): string
    {
        if ($number === 0) {
            return 'Ноль';
        }

        $ones = [
            ['', 'один', 'два', 'три', 'четыре', 'пять', 'шесть', 'семь', 'восемь', 'девять'],
            ['', 'одна', 'две', 'три', 'четыре', 'пять', 'шесть', 'семь', 'восемь', 'девять'],
        ];
        $teens = ['десять', 'одиннадцать', 'двенадцать', 'тринадцать', 'четырнадцать', 'пятнадцать', 'шестнадцать', 'семнадцать', 'восемнадцать', 'девятнадцать'];
        $tens = ['', '', 'двадцать', 'тридцать', 'сорок', 'пятьдесят', 'шестьдесят', 'семьдесят', 'восемьдесят', 'девяносто'];
        $hundreds = ['', 'сто', 'двести', 'триста', 'четыреста', 'пятьсот', 'шестьсот', 'семьсот', 'восемьсот', 'девятьсот'];
        $units = [
            ['', '', '', 0],
            ['тысяча', 'тысячи', 'тысяч', 1],
            ['миллион', 'миллиона', 'миллионов', 0],
            ['миллиард', 'миллиарда', 'миллиардов', 0],
        ];

        $parts = [];
        $groups = array_reverse(str_split(str_pad((string) $number, (int) ceil(strlen((string) $number) / 3) * 3, '0', STR_PAD_LEFT), 3));

        foreach ($groups as $index => $group) {
            $value = (int) $group;
            if ($value === 0) {
                continue;
            }

            $gender = $units[$index][3] ?? 0;
            $hundred = intdiv($value, 100);
            $ten = intdiv($value % 100, 10);
            $one = $value % 10;
            $words = [];

            if ($hundred > 0) {
                $words[] = $hundreds[$hundred];
            }

            if ($ten === 1) {
                $words[] = $teens[$one];
            } else {
                if ($ten > 1) {
                    $words[] = $tens[$ten];
                }
                if ($one > 0) {
                    $words[] = $ones[$gender][$one];
                }
            }

            if ($index > 0) {
                $words[] = $this->plural($value, $units[$index][0], $units[$index][1], $units[$index][2]);
            }

            array_unshift($parts, implode(' ', $words));
        }

        $result = implode(' ', $parts);

        return mb_strtoupper(mb_substr($result, 0, 1)) . mb_substr($result, 1);
    }

    private function plural(int $number, string $one, string $two, string $many): string
    {
        $number = abs($number) % 100;
        $last = $number % 10;

        if ($number > 10 && $number < 20) {
            return $many;
        }

        return match ($last) {
            1 => $one,
            2, 3, 4 => $two,
            default => $many,
        };
    }
}