strprfcrm/app/Services/Export/ExportProductionOrdersService.php

<?php

declare(strict_types=1);

namespace App\Services\Export;

use App\Models\File;
use App\Models\ProductionOrder;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
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\Writer\Xlsx;

class ExportProductionOrdersService
{
    public const ORDERS_SHEET = 'График заказов';

    public const ITEMS_SHEET = 'МАФ';

    public const HEADERS = [
        'ID',
        'Номер заказа',
        'Год',
        'Заказчик',
        'Адрес объекта',
        'Номер счёта',
        'Дата счёта',
        'Номер договора',
        'Дата договора',
        'Дата оплаты',
        'Срок поставки, раб. дней',
        'Дата отгрузки по договору',
        'Дата отгрузки по заявке',
        'Статус',
        'Тип исполнения',
        'Менеджер',
        'Оборудование',
        'Примечание',
    ];

    public const ITEM_HEADERS = [
        'Строка заказа',
        'ID МАФ',
        'ID позиции каталога',
        'Артикул',
        'Наименование',
        'Номер заказа МАФ',
        'Заводской номер',
        'Дата производства',
    ];

    /** @param list<int> $orderIds */
    public function handle(array $orderIds, int $userId): File
    {
        $spreadsheet = new Spreadsheet;
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle(self::ORDERS_SHEET);
        $sheet->fromArray(self::HEADERS, null, 'A1');
        $sheet->getStyle('A1:R1')->getFont()->setBold(true);
        $sheet->getStyle('A1:R1')->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FFD9EAF7');

        $itemsSheet = $spreadsheet->createSheet();
        $itemsSheet->setTitle(self::ITEMS_SHEET);
        $itemsSheet->fromArray(self::ITEM_HEADERS, null, 'A1');
        $itemsSheet->getStyle('A1:H1')->getFont()->setBold(true);
        $itemsSheet->getStyle('A1:H1')->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FFE2F0D9');

        $row = 2;
        $itemRow = 2;
        ProductionOrder::query()
            ->with(['manager', 'items.catalogItem'])
            ->whereKey($orderIds)
            ->orderByDesc('created_at')
            ->orderByDesc('id')
            ->chunk(200, function ($orders) use ($sheet, $itemsSheet, &$row, &$itemRow): void {
                foreach ($orders as $order) {
                    $orderRow = $row;
                    $sheet->setCellValue("A{$row}", $order->id);
                    $sheet->setCellValueExplicit("B{$row}", $order->order_number, DataType::TYPE_STRING);
                    $sheet->setCellValue("C{$row}", $order->order_year);
                    $sheet->setCellValue("D{$row}", $order->customer_name);
                    $sheet->setCellValue("E{$row}", $order->object_address);
                    $sheet->setCellValueExplicit("F{$row}", $order->invoice_number, DataType::TYPE_STRING);
                    $sheet->setCellValue("G{$row}", $order->invoice_date?->format('d.m.Y'));
                    $sheet->setCellValueExplicit("H{$row}", (string) $order->contract_number, DataType::TYPE_STRING);
                    $sheet->setCellValue("I{$row}", $order->contract_date?->format('d.m.Y'));
                    $sheet->setCellValue("J{$row}", $order->payment_date?->format('d.m.Y'));
                    $sheet->setCellValue("K{$row}", $order->supply_working_days);
                    $sheet->setCellValue("L{$row}", $order->contract_shipment_date?->format('d.m.Y'));
                    $sheet->setCellValue("M{$row}", $order->application_shipment_date?->format('d.m.Y'));
                    $sheet->setCellValue("N{$row}", $order->statusLabel());
                    $sheet->setCellValue("O{$row}", $order->executionTypeLabel());
                    $sheet->setCellValue("P{$row}", $order->manager?->name);
                    $sheet->setCellValue("Q{$row}", $this->equipmentSummary($order));
                    $sheet->setCellValue("R{$row}", $order->note);

                    foreach ($order->items as $item) {
                        $itemsSheet->setCellValue("A{$itemRow}", $orderRow);
                        $itemsSheet->setCellValue("B{$itemRow}", $item->id);
                        $itemsSheet->setCellValue("C{$itemRow}", $item->common_catalog_item_id);
                        $itemsSheet->setCellValueExplicit(
                            "D{$itemRow}",
                            (string) $item->catalogItem?->article,
                            DataType::TYPE_STRING,
                        );
                        $itemsSheet->setCellValue(
                            "E{$itemRow}",
                            $item->catalogItem?->calculator_name ?: $item->catalogItem?->print_name,
                        );
                        $itemsSheet->setCellValueExplicit(
                            "F{$itemRow}",
                            $item->order_item_number,
                            DataType::TYPE_STRING,
                        );
                        $itemsSheet->setCellValueExplicit(
                            "G{$itemRow}",
                            (string) $item->factory_number,
                            DataType::TYPE_STRING,
                        );
                        $itemsSheet->setCellValue(
                            "H{$itemRow}",
                            $item->manufacture_date?->format('d.m.Y'),
                        );
                        $itemRow++;
                    }

                    $row++;
                }
            });

        $lastRow = max(1, $row - 1);
        $sheet->getStyle("A1:R{$lastRow}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $sheet->getStyle("A1:R{$lastRow}")->getAlignment()->setWrapText(true)->setVertical(Alignment::VERTICAL_TOP);
        $sheet->freezePane('A2');
        $sheet->setAutoFilter("A1:R{$lastRow}");
        foreach (range('A', 'R') as $column) {
            $sheet->getColumnDimension($column)->setWidth(in_array($column, ['D', 'E', 'Q', 'R'], true) ? 36 : 18);
        }

        $lastItemRow = max(1, $itemRow - 1);
        $itemsSheet->getStyle("A1:H{$lastItemRow}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $itemsSheet->getStyle("A1:H{$lastItemRow}")->getAlignment()->setWrapText(true)->setVertical(Alignment::VERTICAL_TOP);
        $itemsSheet->freezePane('A2');
        $itemsSheet->setAutoFilter("A1:H{$lastItemRow}");
        foreach (range('A', 'H') as $column) {
            $itemsSheet->getColumnDimension($column)->setWidth(in_array($column, ['D', 'E', 'F'], true) ? 30 : 18);
        }

        $spreadsheet->setActiveSheetIndex(0);

        $filename = 'График_заказов_'.now()->format('Y-m-d_H-i-s').'.xlsx';
        $path = 'generated/production-orders/'.$userId.'/'.Str::uuid().'/'.$filename;
        Storage::disk('local')->makeDirectory(dirname($path));
        (new Xlsx($spreadsheet))->save(Storage::disk('local')->path($path));
        $spreadsheet->disconnectWorksheets();

        $file = File::query()->create([
            'user_id' => $userId,
            'original_name' => $filename,
            'mime_type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'path' => $path,
            'link' => '',
            'is_generated' => true,
        ]);
        $file->update(['link' => route('schedule.orders.files.download', $file)]);

        return $file;
    }

    private function equipmentSummary(ProductionOrder $order): string
    {
        return $order->items
            ->groupBy(static fn ($item): string => implode('|', [
                $item->catalogItem?->article,
                $item->catalogItem?->calculator_name ?: $item->catalogItem?->print_name,
            ]))
            ->map(static function ($items): string {
                $item = $items->first();
                $name = $item->catalogItem?->calculator_name ?: $item->catalogItem?->print_name;
                $label = collect([$item->catalogItem?->article, $name])
                    ->filter(static fn ($value): bool => filled($value))
                    ->implode(' — ');

                return ($label !== '' ? $label.' — ' : '').$items->count().' шт.';
            })
            ->implode("\n");
    }
}