strprfcrm/app/Services/ExportMafService.php

<?php

namespace App\Services;

use App\Helpers\DateHelper;
use App\Models\File;
use App\Models\MafView;
use App\Models\Scopes\YearScope;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportMafService
{
    public function handle(int $userId, array $filters = []): string
    {

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

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

        $year = (int) ($filters['year'] ?? date('Y'));
        $query = MafView::query()
            ->withoutGlobalScope(YearScope::class)
            ->where('year', $year);

        $this->applyFilters($query, $filters);
        $mafs = $query->get();
        $i = 2;
        foreach ($mafs as $maf) {
            $sheet->setCellValue('A' . $i, $maf->id);
            $sheet->setCellValue('B' . $i, $maf->year);
            $sheet->setCellValue('C' . $i, $maf->district_name);
            $sheet->setCellValue('D' . $i, $maf->area_name);
            $sheet->setCellValue('E' . $i, $maf->object_address);
            $sheet->setCellValue('F' . $i, $maf->order_number);
            $sheet->setCellValue('G' . $i, $maf->status);
            $sheet->setCellValue('H' . $i, $maf->rfid);
            $sheet->setCellValue('I' . $i, $maf->factory_number);
            $sheet->setCellValue('J' . $i, (is_string($maf->manufacture_date)) ? DateHelper::ISODateToExcelDate($maf->manufacture_date) : '');
            $sheet->setCellValue('K' . $i, $maf->statement_number);
            $sheet->setCellValue('L' . $i, (is_string($maf->statement_date)) ? DateHelper::ISODateToExcelDate($maf->statement_date) : '');
            $sheet->setCellValue('M' . $i, $maf->upd_number);
            $sheet->setCellValue('N' . $i, $maf->nomenclature_number);
            $sheet->setCellValue('O' . $i, $maf->article);
            $sheet->setCellValue('P' . $i, $maf->name_tz);
            $sheet->setCellValue('Q' . $i, $maf->type_tz);
            $sheet->setCellValue('R' . $i, $maf->type);
            $sheet->setCellValue('S' . $i, $maf->manufacturer_name);
            $sheet->setCellValue('T' . $i, $maf->comment);
            $sheet->getStyle('J' . $i)
                ->getNumberFormat()
                ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);
            $sheet->getStyle('L' . $i)
                ->getNumberFormat()
                ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);
            $i++;
        }
        $sheet->getStyle('A1:T' . $i - 1)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN)->setColor(new Color('777777'));

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

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


        // return link
        return $fileName;

    }

    private function applyFilters(Builder $query, array $filters): void
    {
        $allowedColumns = array_flip((new MafView())->getFillable());

        foreach ($filters as $filterName => $filterValue) {
            if ($filterName === 'year' || $filterValue === null || $filterValue === '') {
                continue;
            }

            if ($filterName === 's') {
                $searchFields = [
                    'rfid',
                    'factory_number',
                    'statement_number',
                    'upd_number',
                    'object_address',
                    'nomenclature_number',
                    'article',
                ];
                $query->where(function ($subQuery) use ($searchFields, $filterValue) {
                    foreach ($searchFields as $searchField) {
                        $subQuery->orWhere($searchField, 'LIKE', '%' . $filterValue . '%');
                    }
                });
                continue;
            }

            if (Str::endsWith($filterName, '_from') || Str::endsWith($filterName, '_to')) {
                $operator = Str::endsWith($filterName, '_from') ? '>=' : '<=';
                $column = Str::replace(['_from', '_to'], '', $filterName);
                if (!isset($allowedColumns[$column])) {
                    continue;
                }
                $query->where($column, $operator, $filterValue);
                continue;
            }

            if (!isset($allowedColumns[$filterName])) {
                continue;
            }

            if (is_string($filterValue) && Str::contains($filterValue, '||')) {
                $values = explode('||', $filterValue);
                $query->where(function ($subQuery) use ($filterName, $values) {
                    $nonNullValues = [];
                    foreach ($values as $value) {
                        if ($value === '-пусто-') {
                            $subQuery->orWhereNull($filterName);
                        } else {
                            $nonNullValues[] = $value;
                        }
                    }
                    if (!empty($nonNullValues)) {
                        $subQuery->orWhereIn($filterName, $nonNullValues);
                    }
                });
                continue;
            }

            if ($filterValue === '-пусто-') {
                $query->whereNull($filterName);
            } else {
                $query->where($filterName, $filterValue);
            }
        }
    }
}