strprfcrm/app/Services/Export/ExportCommonCatalogService.php

<?php

declare(strict_types=1);

namespace App\Services\Export;

use App\Models\CommonCatalogItem;
use App\Models\File;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportCommonCatalogService
{
    public const HEADERS = [
        'Артикул',
        'Наименование',
        'Вид',
        'Габариты',
        null,
        null,
        'Размер участка',
        null,
        'Высота падения',
        'Дополнительные сведения',
        "Единица измерения\nгабаритов",
        'Вес, кг.',
        'Объем, м3',
        'Места',
        'Состав',
        'Возрастная группа',
        'Макс.кол-во пользователей',
        'Ед.',
        'Внешний вид',
        'Серия',
        'ТМ',
        'Калькулятор',
        'строители',
        'опт',
        'рек',
        'розница',
        'проект',
        'проект+м',
        'пик',
        'рек+10',
    ];

    public const SUBHEADERS = [
        null,
        null,
        null,
        'Длина',
        'Ширина',
        'Высота',
        'Длина',
        'Ширина',
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        1.05,
        1.1,
        1.5,
        2,
        1.2,
        'инд.',
        1.21,
    ];

    public function handle(int $userId): string
    {
        $spreadsheet = new Spreadsheet;
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Общие сведения');
        $sheet->fromArray(self::HEADERS, null, 'A1');
        $sheet->fromArray(self::SUBHEADERS, null, 'A2');
        $this->mergeHeaders($sheet);
        $this->styleHeaders($sheet);

        $row = 3;
        CommonCatalogItem::query()
            ->with('imageFile')
            ->orderBy('article')
            ->orderBy('calculator_name')
            ->chunk(200, function ($items) use ($sheet, &$row): void {
                foreach ($items as $item) {
                    $sheet->setCellValueExplicit("A{$row}", $item->article, DataType::TYPE_STRING);
                    $sheet->setCellValue("B{$row}", $item->calculator_name);
                    $sheet->setCellValue("C{$row}", $item->kind);
                    $sheet->setCellValue("D{$row}", $item->dimension_length);
                    $sheet->setCellValue("E{$row}", $item->dimension_width);
                    $sheet->setCellValue("F{$row}", $item->dimension_height);
                    $sheet->setCellValue("G{$row}", $item->site_length);
                    $sheet->setCellValue("H{$row}", $item->site_width);
                    $sheet->setCellValue("I{$row}", $item->fall_height);
                    $sheet->setCellValue("J{$row}", $item->additional_info);
                    $sheet->setCellValue("K{$row}", $item->dimension_unit);
                    $sheet->setCellValue("L{$row}", $item->weight);
                    $sheet->setCellValue("M{$row}", $item->volume);
                    $sheet->setCellValue("N{$row}", $item->places);
                    $sheet->setCellValue("O{$row}", $item->composition);
                    $sheet->setCellValue("P{$row}", $item->age_group);
                    $sheet->setCellValue("Q{$row}", $item->max_users);
                    $sheet->setCellValue("R{$row}", $item->unit);
                    $sheet->setCellValue("T{$row}", $item->series);
                    $sheet->setCellValue("U{$row}", $item->trademark);
                    $sheet->setCellValue("V{$row}", match ($item->calculator_enabled) {
                        true => 'да',
                        false => 'нет',
                        null => null,
                    });
                    $sheet->setCellValue("W{$row}", $item->builders_price);
                    $sheet->setCellValue("X{$row}", $item->wholesale_price);
                    $sheet->setCellValue("Y{$row}", $item->recommended_price);
                    $sheet->setCellValue("Z{$row}", $item->retail_price);
                    $sheet->setCellValue("AA{$row}", $item->project_price);
                    $sheet->setCellValue("AB{$row}", $item->project_with_installation_price);
                    $sheet->setCellValue("AC{$row}", $item->pik_price);
                    $sheet->setCellValue("AD{$row}", $item->recommended_plus_10_price);

                    $this->addImage($sheet, $item, $row);
                    $row++;
                }
            });

        if ($row > 3) {
            $lastRow = $row - 1;
            $sheet->getStyle("A1:AD{$lastRow}")
                ->getBorders()
                ->getAllBorders()
                ->setBorderStyle(Border::BORDER_THIN);
            $sheet->getStyle("A3:AD{$lastRow}")->getAlignment()
                ->setVertical(Alignment::VERTICAL_TOP)
                ->setWrapText(true);
            $sheet->getStyle("W3:AD{$lastRow}")
                ->getNumberFormat()
                ->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        }

        $this->setColumnWidths($sheet);

        $directory = 'export/common_catalog';
        $filename = 'common_catalog_'.now()->format('Y-m-d_H-i-s').'.xlsx';
        $relativePath = "{$directory}/{$filename}";
        Storage::disk('public')->makeDirectory($directory);
        (new Xlsx($spreadsheet))->save(Storage::disk('public')->path($relativePath));
        $spreadsheet->disconnectWorksheets();

        $link = url('/storage/'.$relativePath);
        File::query()->create([
            'link' => $link,
            'path' => $relativePath,
            'user_id' => $userId,
            'original_name' => $filename,
            'mime_type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'is_generated' => true,
        ]);

        return $link;
    }

    private function mergeHeaders(Worksheet $sheet): void
    {
        foreach (['A', 'B', 'C', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W'] as $column) {
            $sheet->mergeCells("{$column}1:{$column}2");
        }
        $sheet->mergeCells('D1:F1');
        $sheet->mergeCells('G1:H1');
    }

    private function styleHeaders(Worksheet $sheet): void
    {
        $sheet->getStyle('A1:AD2')->applyFromArray([
            'font' => ['bold' => true],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'D9EAF7'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
        ]);
        $sheet->getRowDimension(1)->setRowHeight(36);
        $sheet->getRowDimension(2)->setRowHeight(24);
        $sheet->freezePane('A3');
    }

    private function setColumnWidths(Worksheet $sheet): void
    {
        foreach ([
            'A' => 14,
            'B' => 38,
            'C' => 24,
            'D' => 13,
            'E' => 13,
            'F' => 13,
            'G' => 13,
            'H' => 13,
            'I' => 15,
            'J' => 28,
            'K' => 18,
            'L' => 14,
            'M' => 14,
            'N' => 11,
            'O' => 28,
            'P' => 18,
            'Q' => 18,
            'R' => 10,
            'S' => 18,
            'T' => 20,
            'U' => 18,
            'V' => 14,
        ] as $column => $width) {
            $sheet->getColumnDimension($column)->setWidth($width);
        }

        foreach (['W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD'] as $column) {
            $sheet->getColumnDimension($column)->setWidth(16);
        }
    }

    private function addImage(Worksheet $sheet, CommonCatalogItem $item, int $row): void
    {
        $path = $item->imageFile?->path;
        if (! $path || ! Storage::disk('public')->exists($path)) {
            return;
        }

        $drawing = new Drawing;
        $drawing->setName($item->article);
        $drawing->setPath(Storage::disk('public')->path($path));
        $drawing->setCoordinates("S{$row}");
        $drawing->setHeight(68);
        $drawing->setOffsetX(4);
        $drawing->setOffsetY(4);
        $drawing->setWorksheet($sheet);
        $sheet->getRowDimension($row)->setRowHeight(56);
    }
}