strprfcrm/app/Services/ExportMafRegistryService.php

<?php

namespace App\Services;

use App\Helpers\DateHelper;
use App\Models\File;
use App\Models\ProductSKU;
use App\Models\Scopes\YearScope;
use Exception;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportMafRegistryService
{
    private const TEMPLATE = './templates/MafRegistry.xlsx';
    private const SHEET_TITLE = 'НАШ ДВОР';
    private const FIRST_DATA_ROW = 4;

    /**
     * @throws Exception
     */
    public function handle(int $userId, string $updNumber, int $year): string
    {
        $updNumber = trim($updNumber);

        if ($updNumber === '') {
            throw new Exception('Укажите номер УПД.');
        }

        return DB::transaction(function () use ($userId, $updNumber, $year): string {
            $mafs = $this->getMafsForRegistry($year);

            if ($mafs->isEmpty()) {
                throw new Exception('Нет МАФ с заполненным № ведомости и пустым № УПД.');
            }

            $reader = IOFactory::createReader('Xlsx');
            $spreadsheet = $reader->load(self::TEMPLATE);
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle(self::SHEET_TITLE);

            $this->prepareTemplateRows($sheet, $mafs->count());
            $this->fillHeader($sheet, $updNumber, $mafs->count());
            $this->fillRows($sheet, $mafs, $updNumber);

            $lastRow = self::FIRST_DATA_ROW + $mafs->count() - 1;
            $sheet->getStyle('A1:P' . $lastRow)
                ->getBorders()
                ->getAllBorders()
                ->setBorderStyle(Border::BORDER_THIN)
                ->setColor(new Color('777777'));

            $sheet->getStyle('H' . self::FIRST_DATA_ROW . ':H' . $lastRow)
                ->getNumberFormat()
                ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);
            $sheet->getStyle('O' . self::FIRST_DATA_ROW . ':O' . $lastRow)
                ->getNumberFormat()
                ->setFormatCode(NumberFormat::FORMAT_NUMBER_00);

            $fileName = fileName('Реестр на оплату УПД ' . $updNumber . ' ' . date('Y-m-d H-i-s') . '.xlsx');
            $directory = 'export/maf-registry';
            $path = $directory . '/' . $fileName;

            Storage::disk('public')->makeDirectory($directory);
            Storage::disk('public')->delete($path);

            (new Xlsx($spreadsheet))->save(Storage::disk('public')->path($path));

            ProductSKU::query()
                ->withoutGlobalScope(YearScope::class)
                ->whereIn('id', $mafs->pluck('id'))
                ->update(['upd_number' => $updNumber]);

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

            return url('/storage/' . $path);
        });
    }

    private function getMafsForRegistry(int $year): Collection
    {
        return ProductSKU::query()
            ->withoutGlobalScope(YearScope::class)
            ->where('year', $year)
            ->whereNotNull('statement_number')
            ->whereRaw("TRIM(statement_number) <> ''")
            ->where(function ($query): void {
                $query->whereNull('upd_number')
                    ->orWhereRaw("TRIM(upd_number) = ''");
            })
            ->with([
                'product' => fn ($query) => $query->withoutGlobalScope(YearScope::class),
                'order' => fn ($query) => $query->withoutGlobalScope(YearScope::class)->with(['district', 'area', 'objectType']),
            ])
            ->orderBy('statement_number')
            ->orderBy('id')
            ->lockForUpdate()
            ->get();
    }

    private function prepareTemplateRows(Worksheet $sheet, int $rowsCount): void
    {
        $highestRow = $sheet->getHighestRow();
        if ($highestRow > self::FIRST_DATA_ROW) {
            $sheet->removeRow(self::FIRST_DATA_ROW + 1, $highestRow - self::FIRST_DATA_ROW);
        }

        if ($rowsCount > 1) {
            $sheet->insertNewRowBefore(self::FIRST_DATA_ROW + 1, $rowsCount - 1);
        }

        $templateStyle = $sheet->getStyle('A' . self::FIRST_DATA_ROW . ':P' . self::FIRST_DATA_ROW);
        for ($row = self::FIRST_DATA_ROW; $row < self::FIRST_DATA_ROW + $rowsCount; $row++) {
            $sheet->duplicateStyle($templateStyle, 'A' . $row . ':P' . $row);
        }
    }

    private function fillHeader(Worksheet $sheet, string $updNumber, int $rowsCount): void
    {
        $lastRow = self::FIRST_DATA_ROW + $rowsCount - 1;

        $sheet->setCellValue('M1', '=SUM(M' . self::FIRST_DATA_ROW . ':M' . $lastRow . ')');
        $sheet->setCellValue('N1', 0);
        $sheet->setCellValue('O1', '=SUM(O' . self::FIRST_DATA_ROW . ':O' . $lastRow . ')');
        $sheet->setCellValue('C2', 'Реестр оборудования и ведомостей "технической приемки" к УПД ' . $updNumber);
    }

    private function fillRows(Worksheet $sheet, Collection $mafs, string $updNumber): void
    {
        $row = self::FIRST_DATA_ROW;

        foreach ($mafs as $maf) {
            $product = $maf->product;
            $order = $maf->order;

            $sheet->setCellValue('A' . $row, self::SHEET_TITLE);
            $sheet->setCellValue('B' . $row, '4');
            $sheet->setCellValue('C' . $row, $updNumber);
            $sheet->setCellValue('D' . $row, $product?->nomenclature_number);
            $sheet->setCellValue('E' . $row, $product?->statement_name);
            $sheet->setCellValue('F' . $row, $product?->article);
            $sheet->setCellValue('G' . $row, $maf->statement_number);
            $sheet->setCellValue('H' . $row, $maf->statement_date ? DateHelper::ISODateToExcelDate((string) $maf->statement_date) : '');
            $sheet->setCellValue('I' . $row, $order?->name);
            $sheet->setCellValue('J' . $row, $order?->objectType?->name);
            $sheet->setCellValue('K' . $row, $order?->area?->name);
            $sheet->setCellValue('L' . $row, $order?->district?->shortname);
            $sheet->setCellValue('M' . $row, 1);
            $sheet->setCellValue('N' . $row, 0);
            $sheet->setCellValue('O' . $row, $product?->total_price ?? 0);
            $sheet->setCellValue('P' . $row, 'С бетоном');

            $row++;
        }
    }
}