strprfcrm/app/Services/ContractorPriceService.php

<?php

namespace App\Services;

use App\Models\Contractor;
use App\Models\ContractorInstallationPrice;
use App\Models\Product;
use App\Models\Scopes\YearScope;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Collection;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ContractorPriceService
{
    public function rowsForContractor(Contractor $contractor, int $year): Collection
    {
        $currentProducts = Product::query()
            ->where('year', $year)
            ->orderBy('article')
            ->get()
            ->keyBy('id');

        $prices = $contractor->prices()
            ->where('catalog_year', $year)
            ->with('product')
            ->get()
            ->keyBy('product_id');

        $rows = collect();

        foreach ($currentProducts as $product) {
            $rows->push($this->buildRow($product, $prices->get($product->id), true));
        }

        foreach ($prices as $price) {
            if ($currentProducts->has($price->product_id) || !$price->product) {
                continue;
            }

            $rows->push($this->buildRow($price->product, $price, false));
        }

        return $rows;
    }

    public function updatePrice(Contractor $contractor, Product $product, int $year, ?string $nameInSpec, float $price): ContractorInstallationPrice
    {
        return ContractorInstallationPrice::query()->updateOrCreate(
            [
                'contractor_id' => $contractor->id,
                'product_id' => $product->id,
                'catalog_year' => $year,
            ],
            [
                'name_in_spec' => $nameInSpec ?: null,
                'price' => max(0, $price),
            ],
        );
    }

    public function import(Contractor $contractor, UploadedFile $file, int $year): array
    {
        $spreadsheet = IOFactory::load($file->getRealPath());
        $sheet = $spreadsheet->getActiveSheet();
        $highestRow = $sheet->getHighestDataRow();

        $updated = 0;
        $unchanged = 0;
        $errors = [];

        for ($row = 2; $row <= $highestRow; $row++) {
            $article = trim((string) $sheet->getCell('B' . $row)->getValue());

            if ($article === '') {
                continue;
            }

            $product = Product::query()
                ->where('year', $year)
                ->where('article', $article)
                ->first();

            if (!$product) {
                $errors[] = "Строка {$row}: артикул {$article} не найден в каталоге {$year} года";
                continue;
            }

            $nameInSpec = trim((string) $sheet->getCell('D' . $row)->getValue());
            $price = $this->parsePrice($sheet->getCell('E' . $row)->getCalculatedValue());

            $current = ContractorInstallationPrice::query()
                ->where('contractor_id', $contractor->id)
                ->where('product_id', $product->id)
                ->where('catalog_year', $year)
                ->first();

            $currentName = (string) ($current?->name_in_spec ?? '');
            $currentPrice = (float) ($current?->price ?? 0);

            if ($current && $currentName === $nameInSpec && abs($currentPrice - $price) < 0.001) {
                $unchanged++;
                continue;
            }

            $this->updatePrice($contractor, $product, $year, $nameInSpec, $price);
            $updated++;
        }

        return [
            'updated' => $updated,
            'unchanged' => $unchanged,
            'errors' => $errors,
        ];
    }

    public function export(Contractor $contractor, int $year): string
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Цены монтажа');

        $headers = [
            'A' => 'Картинка МАФ',
            'B' => 'Артикул МАФ',
            'C' => 'Номер номенклатуры',
            'D' => 'Наименование по спецификации',
            'E' => 'Цена монтажа',
        ];

        foreach ($headers as $column => $title) {
            $sheet->setCellValue($column . '1', $title);
        }

        $rowNumber = 2;
        foreach ($this->rowsForContractor($contractor, $year) as $row) {
            $this->insertProductImage($sheet, $row['product'], $rowNumber);
            $sheet->setCellValue('B' . $rowNumber, $row['product']->article);
            $sheet->setCellValue('C' . $rowNumber, $row['product']->nomenclature_number);
            $sheet->setCellValue('D' . $rowNumber, $row['price']?->name_in_spec ?? '');
            $sheet->setCellValue('E' . $rowNumber, $row['price']?->price ?? 0);
            $sheet->getRowDimension($rowNumber)->setRowHeight(55);
            $rowNumber++;
        }

        $sheet->getStyle('A1:E1')->getFont()->setBold(true);
        $sheet->getStyle('A1:E' . max(1, $rowNumber - 1))->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $sheet->getStyle('E2:E' . max(2, $rowNumber))->getNumberFormat()->setFormatCode('#,##0.00');

        foreach (range('A', 'E') as $column) {
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }
        $sheet->getColumnDimension('A')->setWidth(18);

        $path = storage_path('app/contractor-prices-' . $contractor->id . '-' . $year . '-' . time() . '.xlsx');
        (new Xlsx($spreadsheet))->save($path);

        return $path;
    }

    private function buildRow(Product $product, ?ContractorInstallationPrice $price, bool $available): array
    {
        return [
            'product' => $product,
            'price' => $price,
            'available' => $available && is_null($product->deleted_at),
        ];
    }

    private function parsePrice(mixed $value): float
    {
        if ($value === null || $value === '') {
            return 0;
        }

        $normalized = str_replace([' ', "\xc2\xa0", '₽'], '', (string) $value);
        $normalized = str_replace(',', '.', $normalized);

        return is_numeric($normalized) ? (float) $normalized : 0;
    }

    private function insertProductImage($sheet, Product $product, int $row): void
    {
        $path = public_path('images/main/' . $product->article . '.0000.0000.jpg');
        if (!file_exists($path)) {
            return;
        }

        $drawing = new Drawing();
        $drawing->setPath($path);
        $drawing->setCoordinates('A' . $row);
        $drawing->setHeight(60);
        $drawing->setOffsetX(4);
        $drawing->setOffsetY(4);
        $drawing->setWorksheet($sheet);
    }
}