strprfcrm/app/Services/ExportOrdersService.php

<?php

namespace App\Services;

use App\Helpers\DateHelper;
use App\Models\ProductSKU;
use App\Models\Product;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportOrdersService
{


    /**
     * @throws \Exception
     */
    public function handle(Collection $orders, int $userId): string
    {
        $orderIds = $orders->pluck('id')->filter()->values();
        $orderYears = $orders->pluck('year')->filter()->unique()->values();

        $productsSkuByOrder = collect();
        if ($orderIds->isNotEmpty() && $orderYears->isNotEmpty()) {
            $productsSkuByOrder = ProductSKU::query()
                ->withoutGlobalScopes()
                ->whereIn('order_id', $orderIds)
                ->whereIn('year', $orderYears)
                ->get()
                ->groupBy('order_id');
        }

        $inputFileType = 'Xlsx'; // Xlsx - Xml - Ods - Slk - Gnumeric - Csv
        $inputFileName = './templates/Orders.xlsx';

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

        $i = 3;
        $first = true;
        $from = '-';
        $prevInstDate = '';
        $j = 1;
        foreach ($orders as $order) {
            $orderProductsSku = ($productsSkuByOrder->get($order->id) ?? collect())
                ->where('year', (int) $order->year)
                ->values();
            $order->setRelation('products_sku', $orderProductsSku);

            $instDate = ($order->installation_date) ? DateHelper::getHumanDate($order->installation_date, true) : '';
            $readyDate = ($order->ready_date) ? DateHelper::getHumanDate($order->ready_date, true) : '';
            $mafs = Str::replace('<div>', '', $order->productsWithCount);
            $mafs = Str::replace('</div>', "\n", $mafs);

            $sheet->setCellValue('A' . $i, $order->id);
            $sheet->setCellValue('B' . $i, $order->name);
            $sheet->setCellValue('C' . $i, $order?->user?->name);
            $sheet->setCellValue('D' . $i, $order->district->shortname);
            $sheet->setCellValue('E' . $i, $order->area->name);
            $sheet->setCellValue('F' . $i, $order->object_address);
            $sheet->setCellValue('G' . $i, $order->objectType->name);
            $sheet->setCellValue('H' . $i, $order->comment);
            $sheet->setCellValue('I' . $i, $instDate);
            $sheet->setCellValue('J' . $i, $readyDate);
            $sheet->setCellValue('K' . $i, $order->brigadier?->name);
            $sheet->setCellValue('L' . $i, $order->orderStatus->name);
            $sheet->setCellValue('M' . $i, $order->tg_group_name);
            $sheet->setCellValue('N' . $i, $order->tg_group_link);
            $sheet->setCellValue('O' . $i, $mafs);
            $sheet->setCellValue('P' . $i, $order->ready_to_mount);

            $i++;
        }


        $sheet->getStyle('A1:P' . $i - 1)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN)->setColor(new Color('777777'));
        $sheet->getStyle('A1:P' . $i - 1)->getAlignment()->setWrapText(true);
        $sheet->getStyle('A1:P' . $i - 1)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $fileName = fileName('Площадки ' . date('Y-m-d H-i-s') . '.xlsx');

        $writer = new Xlsx($spreadsheet);
        $fd = 'export/orders/tmp';
        Storage::disk('public')->makeDirectory($fd);
        $fp = storage_path('app/public/export/orders/') . '/tmp/' . $fileName;
        Storage::disk('public')->delete($fd . '/' . $fileName);
        $writer->save($fp);
        PdfConverterClient::convert($fp);

        // create zip archive
        $fileModel = (new FileService())->createZipArchive($fd, Str::replace('.xlsx', '.zip', $fileName), $userId);

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

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

    }
}