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\Support\Arr;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Collection;
use stdClass;
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,
        array $filters = [],
        string $search = '',
        string $sortBy = 'article',
        string $orderBy = 'asc'
    ): Collection
    {
        return $this->sortRows(
            $this->applySearch($this->applyFilters($this->baseRowsForContractor($contractor, $year), $filters), $search),
            $sortBy,
            $orderBy,
        )->values();
    }

    public function filterOptionsForRows(Collection $rows, array $header): array
    {
        return [
            'article' => [
                'title' => $header['article'],
                'values' => $this->uniqueFilterValues($rows, 'article'),
            ],
            'nomenclature_number' => [
                'title' => $header['nomenclature_number'],
                'values' => $this->uniqueFilterValues($rows, 'nomenclature_number'),
            ],
            'name_in_spec' => [
                'title' => $header['name_in_spec'],
                'values' => $this->uniqueFilterValues($rows, 'name_in_spec'),
            ],
            'installation_price_txt' => [
                'title' => $header['installation_price_txt'],
                'values' => $this->uniqueFilterValues($rows, 'installation_price_filter'),
            ],
            'status_name' => [
                'title' => $header['status_name'],
                'values' => [
                    'Доступен' => 'Доступен',
                    'МАФ недоступен' => 'МАФ недоступен',
                    'С ценой' => 'С ценой',
                    'Без цены' => 'Без цены',
                ],
            ],
        ];
    }

    private function baseRowsForContractor(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;
    }

    private function uniqueFilterValues(Collection $rows, string $field): array
    {
        return $rows
            ->pluck($field)
            ->map(fn($value) => $value === null || $value === '' ? '-пусто-' : (string) $value)
            ->unique()
            ->sort(SORT_NATURAL)
            ->mapWithKeys(fn($value) => [$value => $value])
            ->all();
    }

    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): stdClass
    {
        $row = new stdClass();
        $row->id = $product->id;
        $row->product = $product;
        $row->price = $price;
        $row->product_id = $product->id;
        $row->image = $product->image;
        $row->article = $product->article;
        $row->nomenclature_number = $product->nomenclature_number;
        $row->name_in_spec = $price?->name_in_spec ?? '';
        $row->installation_price = (float) ($price?->price ?? 0);
        $row->installation_price_txt = $price?->price_txt ?? '0.00₽';
        $row->installation_price_filter = str_replace('&nbsp;', ' ', $row->installation_price_txt);
        $row->available = $available && is_null($product->deleted_at);
        $row->status_name = $row->available ? 'Доступен' : 'МАФ недоступен';

        return $row;
    }

    private function applyFilters(Collection $rows, array $filters): Collection
    {
        $article = trim((string) ($filters['article'] ?? ''));
        $nomenclatureNumber = trim((string) ($filters['nomenclature_number'] ?? ''));
        $nameInSpec = trim((string) ($filters['name_in_spec'] ?? ''));
        $installationPrice = trim((string) ($filters['installation_price_txt'] ?? ''));
        $status = (string) ($filters['status_name'] ?? '');

        return $rows->filter(function (stdClass $row) use ($article, $nomenclatureNumber, $nameInSpec, $installationPrice, $status): bool {
            if (!$this->matchesListFilter((string) $row->article, $article)) {
                return false;
            }

            if (!$this->matchesListFilter((string) $row->nomenclature_number, $nomenclatureNumber)) {
                return false;
            }

            if (!$this->matchesListFilter((string) $row->name_in_spec, $nameInSpec)) {
                return false;
            }

            if (!$this->matchesListFilter((string) $row->installation_price_filter, $installationPrice)) {
                return false;
            }

            return match ($status) {
                'С ценой' => $row->installation_price > 0,
                'Без цены' => $row->installation_price <= 0,
                'Доступен' => (bool) $row->available,
                'МАФ недоступен' => !$row->available,
                default => true,
            };
        });
    }

    private function matchesListFilter(string $value, string $filter): bool
    {
        if ($filter === '') {
            return true;
        }

        $normalizedValue = $value === '' ? '-пусто-' : $value;
        $values = explode('||', $filter);

        return in_array($normalizedValue, $values, true);
    }

    private function applySearch(Collection $rows, string $search): Collection
    {
        $search = trim(mb_strtolower($search));
        if ($search === '') {
            return $rows;
        }

        return $rows->filter(fn(stdClass $row) => str_contains(mb_strtolower((string) $row->article), $search)
            || str_contains(mb_strtolower((string) $row->nomenclature_number), $search)
            || str_contains(mb_strtolower((string) $row->name_in_spec), $search));
    }

    private function sortRows(Collection $rows, string $sortBy, string $orderBy): Collection
    {
        $sortField = match ($sortBy) {
            'installation_price_txt' => 'installation_price',
            'status_name' => 'status_name',
            'nomenclature_number' => 'nomenclature_number',
            'name_in_spec' => 'name_in_spec',
            default => 'article',
        };

        return $rows->sortBy(
            fn(stdClass $row) => Arr::get((array) $row, $sortField),
            SORT_REGULAR,
            $orderBy === 'desc',
        );
    }

    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);
    }
}