strprfcrm/app/Services/ExportService.php

<?php

namespace App\Services;

use App\Helpers\DateHelper;
use App\Models\Product;
use App\Models\Scopes\YearScope;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportService
{


    public function handle(array $filters = []): string
    {

        $q = Product::query()->withoutGlobalScopes([YearScope::class]);
        $q->where('year', $filters['year'] ?? year());
        foreach ($filters as $filterName => $filterValue) {
            if(!$filterValue) continue;

            if($filterName === 's') {
                // accept search
                $searchFields = ['nomenclature_number', 'article', 'name_tz', 'manufacturer_name', 'note'];
                $q->where(function ($query) use ($searchFields, $filterValue) {
                    foreach ($searchFields as $searchField) {
                        $query->orWhere($searchField, 'LIKE', '%' . $filterValue . '%');
                    }
                });

                continue;
            }

            if(Str::contains($filterName, 'price')) {
                $filterValue = $filterValue * 100;
            }

            if(Str::endsWith($filterName, '_from')) {
                if(is_string($filterValue) && DateHelper::isDate($filterValue)) {
                    $filterValue .= ' 00:00:00';
                }
                $q->where(Str::replace('_from', '', $filterName), '>=', $filterValue);
            } elseif(Str::endsWith($filterName, '_to')) {
                if(is_string($filterValue) && DateHelper::isDate($filterValue)) {
                    $filterValue .= ' 23:59:59';
                }
                $q->where(Str::replace('_to', '', $filterName), '<=', $filterValue);
            } else {
                $q->where($filterName, '=', $filterValue);
            }
        }

        $products = $q->cursor();
        $inputFileType = 'Xlsx'; // Xlsx - Xml - Ods - Slk - Gnumeric - Csv
        $inputFileName = './templates/ExportCatalogTemplate.xlsx';

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

        $i = 2;
        $sum_format = '#,##0.00\ "₽";[Red]\-#,##0.00\ "₽"';

        foreach ($products as $product) {
            $sheet->setCellValue('B' . $i, $product->article);
            $sheet->setCellValue('C' . $i, $product->name_tz);
            $sheet->setCellValue('D' . $i, $product->type_tz);
            $sheet->setCellValue('E' . $i, $product->nomenclature_number);
            $sheet->setCellValue('F' . $i, $product->sizes);
            $sheet->setCellValue('G' . $i, $product->manufacturer);
            $sheet->setCellValue('H' . $i, $product->unit);
            $sheet->setCellValue('I' . $i, $product->type);
            $sheet->setCellValue('J' . $i, $product->product_price);
            $sheet->setCellValue('K' . $i, $product->installation_price);
            $sheet->setCellValue('L' . $i, $product->total_price);
            $sheet->setCellValue('M' . $i, $product->manufacturer_name);
            $sheet->setCellValue('N' . $i, $product->note);
            $sheet->setCellValue('O' . $i, $product->passport_name);
            $sheet->setCellValue('P' . $i, $product->statement_name);
            $sheet->setCellValue('Q' . $i, $product->service_life);
            $sheet->setCellValue('R' . $i, $product->certificate_number);
            $sheet->setCellValue('S' . $i, $product->certificate_date);
            $sheet->setCellValue('T' . $i, $product->certificate_issuer);
            $sheet->setCellValue('U' . $i, $product->certificate_type);
            $sheet->setCellValue('V' . $i, $product->weight);
            $sheet->setCellValue('W' . $i, $product->volume);
            $sheet->setCellValue('X' . $i, $product->places);
            $sheet->getStyle("J{$i}:L{$i}")->getNumberFormat()->setFormatCode($sum_format);
            $i++;
        }

        $fileName = 'product_export_' . date('Y-m-d_H-i-s') . '.xlsx';
        $writer = new Xlsx($spreadsheet);
        Storage::disk('public')->makeDirectory('export');
        $writer->save(storage_path('app/public/export') . '/' . $fileName);

        return $fileName;
    }
}