strprfcrm/app/Services/GenerateDocumentsService.php

<?php

namespace App\Services;

use App\Helpers\CountHelper;
use App\Helpers\DateHelper;
use App\Helpers\ExcelHelper;
use App\Models\Contract;
use App\Models\File;
use App\Models\Order;
use App\Models\ProductSKU;
use App\Models\Reclamation;
use App\Models\Setting;
use App\Models\Ttn;
use App\Models\User;
use Exception;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class GenerateDocumentsService
{
    const INSTALL_FILENAME = 'Монтаж ';
    const HANDOVER_FILENAME = 'Сдача ';
    const RECLAMATION_FILENAME = 'Рекламация ';

    /**
     * @param Order $order
     * @param int $userId
     * @return string
     * @throws Exception
     */
    public function generateInstallationPack(Order $order, int $userId): string
    {
        $techDocsPath = base_path('/tech-docs/');
        $products_sku = $order->products_sku;
        $articles = [];
        Storage::disk('public')->makeDirectory('orders/' . $order->id . '/tmp/Схемы сборки/');

        foreach ($products_sku as $sku) {
            if (!in_array($sku->product->article, $articles)) {
                $articles[] = $sku->product->article;
                // find and copy scheme files to installation directory
                if (file_exists($techDocsPath . $sku->product->article . '/')) {
                    foreach (Storage::disk('base')->allFiles('tech-docs/' . $sku->product->article) as $p) {
                        $content = Storage::disk('base')->get($p);
                        Storage::disk('public')->put('orders/' . $order->id . '/tmp/Схемы сборки/' . basename($p), $content);
                    }
                }
            }
        }

        // generate xlsx order file
        $this->generateOrderForMount($order);

        // create zip archive
        $fileModel = (new FileService())->createZipArchive('orders/' . $order->id . '/tmp', self::INSTALL_FILENAME . fileName($order->common_name) . '.zip', $userId);

        // remove temp files
        Storage::disk('public')->deleteDirectory('orders/' . $order->id . '/tmp');
        $order->documents()->syncWithoutDetaching($fileModel);

        // return link
        return $fileModel?->link ?? '';
    }

    private function generateOrderForMount(Order $order): void
    {
        $inputFileType = 'Xlsx'; // Xlsx - Xml - Ods - Slk - Gnumeric - Csv
        $inputFileName = './templates/OrderForMount.xlsx';

        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheet = $spreadsheet->getActiveSheet();

        // менеджер
        $sheet->setCellValue('F8', $order->user->name);
        $sheet->setCellValue('X8', $order->user->phone);

        // округ и район
        $sheet->setCellValue('L10', $order->district->shortname);
        $sheet->setCellValue('W10', $order->area->name);

        if ($order->area->responsible) {
            // ответственный
            $sheet->setCellValue('C12', $order->area->responsible?->name
                . ', ' . $order->area->responsible?->phone . ', ' . $order->area->responsible?->post);
        } else {
            $sheet->setCellValue('C12', '');
        }
        // адрес
        $sheet->setCellValue('L14', $order->object_address);
        $str = Str::replace('<div>', '', $order->products_with_count);
        $str = Str::replace('</div>', "\n", $str);

        // мафы
        $sheet->setCellValue('G33', Str::trim($str));

        //
        $fileName = 'Заявка на монтаж - ' . fileName($order->object_address) . '.xlsx';
        $writer = new Xlsx($spreadsheet);
        Storage::disk('public')->makeDirectory('orders/' . $order->id . '/tmp');

        $writer->save(storage_path('app/public/orders/') . $order->id . '/tmp/' . $fileName);
    }

    /**
     * @throws Exception
     */
    public function generateHandoverPack(Order $order, int $userId): string
    {
        $articles = [];
        Storage::disk('public')->makeDirectory('orders/' . $order->id . '/tmp/ПАСПОРТ/');
        Storage::disk('public')->makeDirectory('orders/' . $order->id . '/tmp/СЕРТИФИКАТ/');
        Storage::disk('public')->makeDirectory('orders/' . $order->id . '/tmp/ФОТО ПСТ/');
        Storage::disk('public')->makeDirectory('orders/' . $order->id . '/tmp/ФОТО ТН/');

        // copy app photos
        foreach ($order->photos as $photo) {
            $from = $photo->path;
            $to = 'orders/' . $order->id . '/tmp/ФОТО ПСТ/' . $photo->original_name;
            if (!Storage::disk('public')->exists($to)) {
                Storage::disk('public')->copy($from, $to);
            }
        }

        foreach ($order->products_sku as $sku) {
            // copy certificates
            if ($sku->product->certificate_id) {
                $from = $sku->product->certificate->path;
                $to = 'orders/' . $order->id . '/tmp/СЕРТИФИКАТ/' . $sku->product->certificate->original_name;
                if (!Storage::disk('public')->exists($to)) {
                    Storage::disk('public')->copy($from, $to);
                }
            }

            // copy passport
            if ($sku->passport_id) {
                $from = $sku->passport->path;
                $to = 'orders/' . $order->id . '/tmp/ПАСПОРТ/';
                if (!Storage::disk('public')->exists($to . $sku->passport->original_name)) {
                    $f = Storage::disk('public')->get($from);
                    $ext = \File::extension($sku->passport->original_name);
                    $targetName = $to . 'Паспорт ' . $sku->factory_number . ' арт. ' . $sku->product->article . '.' . $ext;
                    Storage::disk('public')->put($targetName, $f);
                }
            }

        }

        // generate xlsx order files
        $this->generateStatement($order);
        $this->generateQualityDeclaration($order);
        $this->generateInventory($order);
        $this->generatePassport($order);


        // create zip archive
        $fileModel = (new FileService())->createZipArchive('orders/' . $order->id . '/tmp', self::HANDOVER_FILENAME . fileName($order->common_name) . '.zip', $userId);

        // remove temp files
        Storage::disk('public')->deleteDirectory('orders/' . $order->id . '/tmp');
        $order->documents()->syncWithoutDetaching($fileModel);

        // return link
        return $fileModel?->link ?? '';
    }

    private function generateStatement(Order $order): void
    {
        $inputFileType = 'Xlsx';
        $inputFileName = './templates/Statement.xlsx';

        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheet = $spreadsheet->getActiveSheet();

        $contract = Contract::query()->where('contracts.year', $order->year)->first();
        $contract_number = $contract->contract_number ?? 'заполнить договор за ' . $order->year . ' год!';
        $contract_date = DateHelper::getHumanDate($contract->contract_date ?? '1970-01-01', true);
        $s = 'по Договору №' . $contract_number . ' от ' . $contract_date . ' г. Между ГБУ "Мосремонт" и ООО "НАШ ДВОР-СТ"';

        $sheet->setCellValue('B5', $s);

        // менеджер
        $sheet->setCellValue('G21', $order->user->name);

        // округ и район
        $sheet->setCellValue('C6', $order->district->shortname);
        $sheet->setCellValue('F6', $order->area->name);

        $i = 9; // start of table
        $nn = 1; // string number
        foreach ($order->products_sku as $sku) {
            if ($nn > 1) { // inset row
                $sheet->insertNewRowBefore($i);
            }
            $sheet->setCellValue('A' . $i, $nn++);
            $sheet->setCellValue('B' . $i, $sku->product->statement_name);
            $sheet->setCellValue('C' . $i, $sku->product->passport_name);
            $sheet->setCellValue('D' . $i, 'шт');
            $sheet->setCellValue('E' . $i, '1');
            $sheet->setCellValue('F' . $i, $order->name);
            $sheet->setCellValue('G' . $i, $sku->factory_number);
            $sheet->setCellValue('H' . $i++, $sku->rfid);
        }

        // save file
        $fileName = '1.Ведомость.xlsx';
        $writer = new Xlsx($spreadsheet);
        Storage::disk('public')->makeDirectory('orders/' . $order->id . '/tmp');

        $writer->save(storage_path('app/public/orders/') . $order->id . '/tmp/' . $fileName);
    }

    private function generateQualityDeclaration(Order $order): void
    {
        $inputFileType = 'Xlsx';
        $inputFileName = './templates/QualityDeclaration.xlsx';

        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheet = $spreadsheet->getActiveSheet();

        $address = 'г. Москва, ' . $order->district->shortname . ', район ' . $order->area->name . ', по адресу: ' . $order->object_address;
        $i = 1; // start of table
        $n = 1;
        foreach ($order->products_sku as $sku) {
            if ($n++ > 1) {
                $range = 'A' . $i . ':I' . $i + 56;
                $i = $i + 57;
                ExcelHelper::copyRows($sheet, $range, 'A' . $i);

                // add header img
                $drawing = new Drawing();
                $drawing->setName('Header');
                $drawing->setDescription('Header');
                $drawing->setPath('templates/header.png');
                $drawing->setCoordinates('A' . $i);
                $drawing->setOffsetX(16);
                $drawing->setOffsetY(8);
                $drawing->setResizeProportional(true);
                $drawing->setWidth(680);
                $drawing->setWorksheet($sheet);

            }

            // document date?
            $sheet->setCellValue('A' . $i + 7, DateHelper::getHumanDate(date('Y-m-d'), true));

            $sheet->setCellValue('B' . $i + 16, $sku->product->passport_name);
            $sheet->setCellValue('C' . $i + 18, $sku->rfid);
            $sheet->setCellValue('C' . $i + 20, $address);

            // add page break and copy prev page


        }

        // save file
        $fileName = '2.Декларация качества.xlsx';
        $writer = new Xlsx($spreadsheet);
        Storage::disk('public')->makeDirectory('orders/' . $order->id . '/tmp');

        $writer->save(storage_path('app/public/orders/') . $order->id . '/tmp/' . $fileName);
    }

    private function generateInventory(Order $order): void
    {
        $inputFileType = 'Xlsx';
        $inputFileName = './templates/Inventory.xlsx';

        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheet = $spreadsheet->getActiveSheet();

        $address = 'Округ: ' . $order->district->shortname . ' Район ' . $order->area->name;


        $sheet->setCellValue('C4', $order->name);
        $sheet->setCellValue('C5', $address);
        $sheet->setCellValue('C13', $order->user->name);

        $i = 8; // start of table
        $n = 1;
        foreach ($order->products_sku as $sku) {
            if ($n++ > 1) {
                $sheet->insertNewRowBefore($i + 3, 3);
                $range = 'A' . $i . ':E' . $i + 2;
                $i = $i + 3;
                ExcelHelper::copyRows($sheet, $range, 'A' . $i);
            }
            $sheet->setCellValue('A' . $i, $n - 1);
            $sheet->setCellValue('B' . $i, $sku->product->passport_name);
            $sheet->setCellValue('B' . $i + 2, $sku->rfid);
        }

        // save file
        $fileName = '3.Опись.xlsx';
        $writer = new Xlsx($spreadsheet);
        Storage::disk('public')->makeDirectory('orders/' . $order->id . '/tmp');

        $writer->save(storage_path('app/public/orders/') . $order->id . '/tmp/' . $fileName);
    }

    private function generatePassport(Order $order): void
    {
        $inputFileType = 'Xlsx';
        $inputFileName = './templates/Passport.xlsx';

        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheet = $spreadsheet->getActiveSheet();

        $i = 3; // start of table
        $n = 1;
        foreach ($order->products_sku as $sku) {
            if ($n++ > 1) {
                $sheet->insertNewRowBefore($i + 1, 1);
                $range = 'A' . $i . ':J' . $i;
                $i++;
                ExcelHelper::copyRows($sheet, $range, 'A' . $i);
            }
            $sheet->setCellValue('A' . $i, $n - 1);
            $sheet->setCellValue('B' . $i, $sku->product->passport_name);
            $sheet->setCellValue('C' . $i, $sku->product->type_tz);
            $sheet->setCellValue('D' . $i, $sku->rfid);
            $sheet->setCellValue('E' . $i, $sku->product->certificate_number);
            $sheet->setCellValue('G' . $i, $sku->factory_number);
            $sheet->setCellValue('H' . $i, DateHelper::getHumanDate($sku->manufacture_date, true));
        }

        // save file
        $fileName = '4.Паспорт объекта - ' . fileName($order->object_address) . '.xlsx';
        $writer = new Xlsx($spreadsheet);
        Storage::disk('public')->makeDirectory('orders/' . $order->id . '/tmp');

        $writer->save(storage_path('app/public/orders/') . $order->id . '/tmp/' . $fileName);
    }

    /**
     * @param Reclamation $reclamation
     * @param int $userId
     * @return string
     * @throws Exception
     */
    public function generateReclamationPack(Reclamation $reclamation, int $userId): string
    {
        Storage::disk('public')->makeDirectory('reclamations/' . $reclamation->id . '/tmp/' . fileName($reclamation->order->object_address) . '/ФОТО НАРУШЕНИЯ/');
        // copy photos
        foreach ($reclamation->photos_before as $photo) {
            $from = $photo->path;
            $to = 'reclamations/' . $reclamation->id . '/tmp/' . fileName($reclamation->order->object_address) . '/ФОТО НАРУШЕНИЯ/' . $photo->original_name;
            if (!Storage::disk('public')->exists($to)) {
                Storage::disk('public')->copy($from, $to);
            }
        }

        // create xls and pdf
        $this->generateReclamationOrder($reclamation);
        $this->generateReclamationAct($reclamation);
        $this->generateReclamationGuarantee($reclamation);

        // create zip archive
        $fileModel = (new FileService())->createZipArchive('reclamations/' . $reclamation->id . '/tmp', self::RECLAMATION_FILENAME . fileName($reclamation->order->object_address) . '.zip', $userId);

        // remove temp files
        Storage::disk('public')->deleteDirectory('reclamations/' . $reclamation->id . '/tmp');
        $reclamation->documents()->syncWithoutDetaching($fileModel);

        // return link
        return $fileModel?->link ?? '';
    }

    /**
     * @throws Exception
     */
    public function generateReclamationPaymentPack(Reclamation $reclamation, int $userId): string
    {
        $reclamation->loadMissing([
            'order.statements',
            'documents',
            'acts',
            'photos_before',
            'photos_after',
        ]);

        $tmpRoot = 'reclamations/' . $reclamation->id . '/tmp';
        $baseDir = $tmpRoot . '/Пакет документов на оплату - ' . fileName($reclamation->order->object_address);
        $beforeDir = $baseDir . '/Фотографии проблемы';
        $afterDir = $baseDir . '/Фотографии после устранения';

        Storage::disk('public')->makeDirectory($beforeDir);
        Storage::disk('public')->makeDirectory($afterDir);

        $this->copyPhotosWithEmptyFallback($reclamation->photos_before, $beforeDir);
        $this->copyPhotosWithEmptyFallback($reclamation->photos_after, $afterDir);

        foreach ($reclamation->documents as $document) {
            if ($this->shouldSkipDocumentForPaymentPack($document)) {
                continue;
            }
            $this->copyFileToDir($document->path, $baseDir, $document->original_name);
        }

        foreach ($reclamation->acts as $act) {
            $this->copyFileToDir($act->path, $baseDir, $act->original_name);
        }

        foreach ($reclamation->order?->statements ?? [] as $statement) {
            $this->copyFileToDir($statement->path, $baseDir, $statement->original_name);
        }

        $archiveName = 'Пакет документов на оплату - ' . fileName($reclamation->order->object_address) . '.zip';
        $fileModel = (new FileService())->createZipArchive($tmpRoot, $archiveName, $userId);

        Storage::disk('public')->deleteDirectory($tmpRoot);
        $reclamation->documents()->syncWithoutDetaching($fileModel);

        return $fileModel?->link ?? '';
    }

    /**
     * @throws Exception
     */
    private function generateReclamationOrder(Reclamation $reclamation): void
    {
        $inputFileType = 'Xlsx';
        $inputFileName = './templates/ReclamationOrder.xlsx';

        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheet = $spreadsheet->getActiveSheet();

        $articles = [];
        foreach ($reclamation->skus as $p) {
            $articles[] = $p->product->article;
        }

        $sheet->setCellValue('J4', DateHelper::getHumanDate($reclamation->create_date, true));
        $sheet->setCellValue('L10', $reclamation->order->common_name);
        $sheet->setCellValue('L11', $reclamation->order->area?->responsible?->name);
        $sheet->setCellValue('W11', $reclamation->order->area?->responsible?->phone);
        $sheet->setCellValue('L12', $reclamation->order->year);
        $sheet->setCellValue('G13', $reclamation->guarantee);
        $sheet->setCellValue('G14', implode(', ', $articles));
        $sheet->setCellValue('Y15', DateHelper::getHumanDate($reclamation->finish_date, true));
        $sheet->setCellValue('U20', DateHelper::getHumanDate($reclamation->create_date, true));

        // save file
        $fileName = 'Монтажная заявка - ' . fileName($reclamation->order->object_address) . '.xlsx';
        $writer = new Xlsx($spreadsheet);
        $fd = 'reclamations/' . $reclamation->id . '/tmp/' . fileName($reclamation->order->object_address);
        Storage::disk('public')->makeDirectory($fd);
        $fp = storage_path('app/public/reclamations/') . $reclamation->id . '/tmp/' . fileName($reclamation->order->object_address) . '/' . $fileName;
        Storage::disk('public')->delete($fd . '/' . $fileName);
        $writer->save($fp);
        PdfConverterClient::convert($fp);

    }

    /**
     * @throws Exception
     */
    private function generateReclamationAct(Reclamation $reclamation): void
    {
        $inputFileType = 'Xlsx';
        $inputFileName = './templates/ReclamationAct.xlsx';

        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheet = $spreadsheet->getActiveSheet();

        $representativeId = Setting::getInt(Setting::KEY_RECLAMATION_ACT_REPRESENTATIVE_USER_ID);
        if ($representativeId) {
            $representative = User::query()->withTrashed()->find($representativeId);
            if ($representative) {
                $sheet->setCellValue('A14', 'службы сервиса ' . $representative->name);
            }
        }

        $mafs = [];
        foreach ($reclamation->skus as $p) {
            $mafs[] = $p->product->passport_name . ', тип ' . $p->product->nomenclature_number;
        }

        $sheet->setCellValue('A17', $reclamation->order->object_address);
        $sheet->setCellValue('A22', implode('; ', $mafs));
        $sheet->setCellValue('A27', $reclamation->whats_done);

        $i = 24;
        $n = 1;
        foreach ($reclamation->skus as $p) {
            if ($n++ > 1) {
                $i++;
                $sheet->insertNewRowBefore($i, 1);
                $range = 'D' . $i . ':I' . $i;
                $sheet->mergeCells($range);
            }
            $sheet->setCellValue('D' . $i, $p->rfid);
        }

        // save file
        $fileName = 'Акт - ' . fileName($reclamation->order->object_address) . '.xlsx';
        $writer = new Xlsx($spreadsheet);
        $fd = 'reclamations/' . $reclamation->id . '/tmp/' . fileName($reclamation->order->object_address);
        Storage::disk('public')->makeDirectory($fd);
        $fp = storage_path('app/public/reclamations/') . $reclamation->id . '/tmp/' . fileName($reclamation->order->object_address) . '/' . $fileName;
        Storage::disk('public')->delete($fd . '/' . $fileName);
        $writer->save($fp);
        PdfConverterClient::convert($fp);
    }

    /**
     * @throws Exception
     */
    private function generateReclamationGuarantee(Reclamation $reclamation): void
    {
        $inputFileType = 'Xlsx';
        $inputFileName = './templates/ReclamationGuarantee.xlsx';

        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheet = $spreadsheet->getActiveSheet();

        $mafs = [];
        foreach ($reclamation->skus as $p) {
            $mafs[] = 'Тип ' . $p->product->nomenclature_number . ' (' . $p->product->passport_name . ')' ;
        }
        $contract = Contract::query()->where('contracts.year', $reclamation->order->year)->first();

        $text = "ООО «НАШ ДВОР-СТ» в рамках обязательств по Договору №{$contract?->contract_number}" .
            " от " . DateHelper::getHumanDate($contract?->contract_date ?? '1970-01-01', true) .
            " г. на выполнение комплекса работ по поставке, монтажу устанавливаемых на городских территориях малых архитектурных форм гарантирует " .
            $reclamation->guarantee . " на оборудовании «" . implode('; ', $mafs) .
            "» установленному по адресу г. Москва, " . $reclamation->order->object_address . " в срок до " .
            DateHelper::getHumanDate($reclamation->finish_date, true). " г. в связи с отсутствием детали в наличии и ее производством.";


        $sheet->setCellValue('B9', $reclamation->id);
        $sheet->setCellValue('D9', DateHelper::getHumanDate($reclamation->create_date, true));
        $sheet->setCellValue('A19', $text);

        // save file
        $fileName = 'Гарантийное письмо - ' . fileName($reclamation->order->object_address) . '.xlsx';
        $writer = new Xlsx($spreadsheet);
        $fd = 'reclamations/' . $reclamation->id . '/tmp/' . fileName($reclamation->order->object_address);
        Storage::disk('public')->makeDirectory($fd);
        $fp = storage_path('app/public/reclamations/') . $reclamation->id . '/tmp/' . fileName($reclamation->order->object_address) . '/' . $fileName;
        Storage::disk('public')->delete($fd . '/' . $fileName);
        $writer->save($fp);
        PdfConverterClient::convert($fp);

    }


    public function generateFilePack(Collection $files, int $userId, string $name = 'files'): \App\Models\File
    {
        $dir = Str::random(2);
        Storage::disk('public')->makeDirectory('files/' . $dir . '/tmp/');

        // copy files
        foreach ($files as $file) {
            $from = $file->path;
            $to = 'files/' . $dir . '/tmp/' . $file->original_name;
            if (!Storage::disk('public')->exists($to)) {
                Storage::disk('public')->copy($from, $to);
            }
        }

        // create zip archive
        $fileModel = (new FileService())->createZipArchive('files/' . $dir . '/tmp', $name .'_' . date('Y-m-d_h-i-s') . '.zip', $userId);

        // remove temp files
        Storage::disk('public')->deleteDirectory('files/' . $dir . '/tmp');

        // return link
        return $fileModel;
    }

    private function copyPhotosWithEmptyFallback(Collection $photos, string $targetDir): void
    {
        if ($photos->isEmpty()) {
            Storage::disk('public')->put(
                $targetDir . '/empty.txt',
                'Данный файл создан для возможности создания пустой папки в архиве'
            );
            return;
        }

        foreach ($photos as $photo) {
            $this->copyFileToDir($photo->path, $targetDir, $photo->original_name);
        }
    }

    private function shouldSkipDocumentForPaymentPack($file): bool
    {
        if (!$file) {
            return true;
        }

        if ($file->mime_type === 'application/zip') {
            return true;
        }

        if (Str::endsWith((string)$file->original_name, '.zip')) {
            return true;
        }

        return false;
    }

    private function copyFileToDir(string $fromPath, string $targetDir, string $originalName): void
    {
        if (!Storage::disk('public')->exists($fromPath)) {
            return;
        }

        $safeName = $this->uniqueFileName($targetDir, $originalName);
        $to = $targetDir . '/' . $safeName;

        if (!Storage::disk('public')->exists($to)) {
            Storage::disk('public')->copy($fromPath, $to);
        }
    }

    private function uniqueFileName(string $dir, string $name): string
    {
        $base = pathinfo($name, PATHINFO_FILENAME);
        $ext = pathinfo($name, PATHINFO_EXTENSION);
        $extPart = $ext ? '.' . $ext : '';

        $candidate = $base . $extPart;
        $counter = 1;
        while (Storage::disk('public')->exists($dir . '/' . $candidate)) {
            $candidate = $base . ' (' . $counter . ')' . $extPart;
            $counter++;
        }

        return $candidate;
    }

    public function generateTtnPack(Ttn $ttn, int $userId): string
    {
        $skus = ProductSKU::query()->withoutGlobalScopes()->whereIn('id', json_decode($ttn->skus))->get();
        $volume = $weight = $places = 0;
        foreach ($skus as $sku) {
            if(!isset($order)) {
                $order = $sku->order;
            }
            $volume += $sku->product->volume;
            $weight += $sku->product->weight;
            $places += $sku->product->places;
        }

        $installationDate = ($order->installation_date) ? DateHelper::getHumanDate($order->installation_date, true) : '-';
        $ttnNumber = ($ttn->ttn_number_suffix) ? $ttn->ttn_number . '-' . $ttn->ttn_number_suffix : $ttn->ttn_number;

        $inputFileType = 'Xlsx';
        $inputFileName = './templates/Ttn.xlsx';

        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setCellValue('D8', $installationDate);
        $sheet->setCellValue('R8', $ttnNumber);
        $sheet->setCellValue('AF8', DateHelper::getHumanDate($ttn->order_date, true));
        $sheet->setCellValue('AT8', $ttn->order_number);

        $sheet->setCellValue('B19', $order->object_address ?? '');

        $sheet->setCellValue('AD22', CountHelper::humanCount($places, 'место', 'места', 'мест') . ', способ упаковки: поддон, ящик, картон, полиэтилен');

        $sheet->setCellValue('B24', $weight . ' кг, ' . $volume . ' м.куб');

        $sheet->setCellValue('B36',  $installationDate);

        $sheet->setCellValue('AB57', $installationDate . ' 8-00');
        $sheet->setCellValue('B59',  $installationDate . ' 8-10');
        $sheet->setCellValue('AB59', $installationDate . ' 8-40');

        $sheet->setCellValue('B61', $weight . ' кг');

        $sheet->setCellValue('B63', CountHelper::humanCount($places, 'место', 'места', 'мест'));

        $sheet->setCellValue('B75', $order->object_address ?? '');
        $sheet->setCellValue('AB75',  $installationDate);
        $sheet->setCellValue('B77',   $installationDate);
        $sheet->setCellValue('AB77',  $installationDate);

        $sheet->setCellValue('AB79', CountHelper::humanCount($places, 'место', 'места', 'мест'));
        $sheet->setCellValue('B81', $weight . ' кг');
        $sheet->setCellValue('B83', $order->brigadier?->name ?? '');

        $sheet->setCellValue('B89', $ttn->order_sum);
        $sheet->setCellValue('AD89', $ttn->order_sum);
        $sheet->setCellValue('AS89', $ttn->order_sum);

        // save file
        $fileName = 'ТН №' . $ttn->ttn_number . ' от ' . DateHelper::getHumanDate($ttn->order_date) . '.xlsx';
        $writer = new Xlsx($spreadsheet);
        $fd = 'ttn/' . $ttn->year;
        Storage::disk('public')->makeDirectory($fd);
        $fp = storage_path('app/public/ttn/') . $ttn->year . '/' . $fileName;
        Storage::disk('public')->delete($fd . '/' . $fileName);
        $writer->save($fp);


        $fileModel = File::query()->updateOrCreate([
            'link' => url('/storage/') . '/ttn/' . $ttn->year . '/' .$fileName,
            'path' => $fp . '/' .$fileName,
            'user_id' => $userId,
            'original_name' => $fileName,
            'mime_type' => 'application/xlsx',
        ]);
        $ttn->file_id = $fileModel->id;
        $ttn->save();
        $order->documents()->attach($fileModel->id);
        return $fileModel->link ?? '';
    }

}