iao_team/app/Jobs/ExportPrimaNota.php

<?php

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Facades\Log;
use App\Mail\ExportNotification;
use App\Events\ExportCompleted;
use App\Events\ExportFailed;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportPrimaNota implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public $timeout = 600;
    public $tries = 3;
    public $maxExceptions = 3;

    protected $exportData;
    protected $exportTotals;
    protected $emailAddress;
    protected $emailSubject;
    protected $dateRange;
    protected $userId;
    protected $payments;
    protected $filters;

    /**
     * Create a new job instance.
     */
    public function __construct($exportData, $exportTotals, $emailAddress, $emailSubject, $dateRange, $userId, $payments, $filters = [])
    {
        $this->exportData = $exportData;
        $this->exportTotals = $exportTotals;
        $this->emailAddress = $emailAddress;
        $this->emailSubject = $emailSubject;
        $this->dateRange = $dateRange;
        $this->userId = $userId;
        $this->payments = $payments;
        $this->filters = $filters;

        $this->onQueue('exports');
    }

    /**
     * Execute the job.
     */
    public function handle()
    {
        try {
            Log::info('Starting background export', [
                'user_id' => $this->userId,
                'email' => $this->emailAddress,
                'date_range' => $this->dateRange,
                'total_records' => count($this->exportData)
            ]);

            ini_set('memory_limit', '1024M');

            $filename = 'prima_nota_' . date("Ymd_His") . '_' . $this->userId . '.xlsx';
            $tempPath = sys_get_temp_dir() . '/' . $filename;

            $this->createExcelFile($tempPath);

            if (!file_exists($tempPath) || filesize($tempPath) === 0) {
                throw new \Exception('Excel file creation failed');
            }

            $fileSize = filesize($tempPath);
            $maxSize = 25 * 1024 * 1024;

            if ($fileSize > $maxSize) {
                throw new \Exception('File too large for email attachment (' . round($fileSize / 1024 / 1024, 2) . 'MB > 25MB)');
            }

            $user = \App\Models\User::find($this->userId);

            $emailData = [
                'subject' => $this->emailSubject,
                'from_date' => $this->dateRange['from'],
                'to_date' => $this->dateRange['to'],
                'total_records' => count($this->exportData),
                'user_name' => $user ? $user->name : 'Utente',
                'generated_at' => now()->format('d/m/Y H:i:s'),
                'filters_applied' => $this->getFiltersDescription(),
                'file_size' => round($fileSize / 1024 / 1024, 2) . ' MB'
            ];

            Mail::to($this->emailAddress)->send(new ExportNotification($emailData, $tempPath, $filename));

            if (class_exists(ExportCompleted::class)) {
                broadcast(new ExportCompleted($this->userId, $filename, $this->emailAddress));
            }

            Log::info('Background export completed successfully', [
                'user_id' => $this->userId,
                'email' => $this->emailAddress,
                'filename' => $filename,
                'file_size' => $fileSize,
                'processing_time' => microtime(true) - LARAVEL_START ?? 0
            ]);

        } catch (\Exception $e) {
            Log::error('Background export failed', [
                'user_id' => $this->userId,
                'email' => $this->emailAddress,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);

            if (class_exists(ExportFailed::class)) {
                broadcast(new ExportFailed($this->userId, $e->getMessage()));
            }

            throw $e;
        } finally {
            if (isset($tempPath) && file_exists($tempPath)) {
                unlink($tempPath);
            }

            gc_collect_cycles();
        }
    }

    /**
     * Handle a job failure.
     */
    public function failed(\Throwable $exception)
    {
        Log::error('Export job failed permanently', [
            'user_id' => $this->userId,
            'email' => $this->emailAddress,
            'attempts' => $this->attempts(),
            'error' => $exception->getMessage()
        ]);

        try {
            Mail::raw(
                "Il tuo export della Prima Nota non è riuscito dopo {$this->tries} tentativi.\n\n" .
                "Errore: {$exception->getMessage()}\n\n" .
                "Contatta il supporto tecnico se il problema persiste.",
                function ($message) {
                    $message->to($this->emailAddress)
                           ->subject('Export Prima Nota - Errore');
                }
            );
        } catch (\Exception $e) {
            Log::error('Failed to send failure notification email', [
                'user_id' => $this->userId,
                'error' => $e->getMessage()
            ]);
        }
    }

    /**
     * Create Excel file with export data
     */
    private function createExcelFile($filePath)
    {
        $letters = array('F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA');

        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet();

        $activeWorksheet->setTitle('Prima Nota');

        $spreadsheet->getProperties()
            ->setCreator('Prima Nota System')
            ->setLastModifiedBy('Sistema')
            ->setTitle('Prima Nota Export')
            ->setSubject('Export Prima Nota')
            ->setDescription('Export dei dati Prima Nota dal ' . $this->dateRange['from'] . ' al ' . $this->dateRange['to']);

        $activeWorksheet->setCellValue('A1', "Data");
        $activeWorksheet->setCellValue('B1', "Causale");
        $activeWorksheet->setCellValue('C1', "Dettaglio Causale");
        $activeWorksheet->setCellValue('D1', "Nominativo");
        $activeWorksheet->setCellValue('E1', "Stato");

        $idx = 0;
        foreach ($this->payments as $p) {
            if ($idx >= count($letters)) break;

            $activeWorksheet->setCellValue($letters[$idx] . '1', $p['name']);
            $activeWorksheet->mergeCells($letters[$idx] . '1:' . $letters[$idx + 1] . '1');
            $idx += 2;
        }

        $activeWorksheet->setCellValue('A2', "");
        $activeWorksheet->setCellValue('B2', "");
        $activeWorksheet->setCellValue('C2', "");
        $activeWorksheet->setCellValue('D2', "");
        $activeWorksheet->setCellValue('E2', "");

        $idx = 0;
        foreach ($this->payments as $p) {
            if ($idx >= count($letters) - 1) break;

            if ($p['type'] == 'ALL') {
                $activeWorksheet->setCellValue($letters[$idx] . '2', "Entrate");
                $idx++;
                $activeWorksheet->setCellValue($letters[$idx] . '2', "Uscite");
                $idx++;
            } elseif ($p['type'] == 'IN') {
                $activeWorksheet->setCellValue($letters[$idx] . '2', "Entrate");
                $idx++;
                $activeWorksheet->setCellValue($letters[$idx] . '2', "");
                $idx++;
            } elseif ($p['type'] == 'OUT') {
                $activeWorksheet->setCellValue($letters[$idx] . '2', "");
                $idx++;
                $activeWorksheet->setCellValue($letters[$idx] . '2', "Uscite");
                $idx++;
            }
        }

        $activeWorksheet->getStyle('A1:' . $letters[min(count($letters) - 1, count($this->payments) * 2 + 4)] . '2')
                       ->getFont()->setBold(true);

        $activeWorksheet->getStyle('A1:' . $letters[min(count($letters) - 1, count($this->payments) * 2 + 4)] . '1')
                       ->getFill()
                       ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                       ->getStartColor()->setARGB('FF0C6197');

        $activeWorksheet->getStyle('A1:' . $letters[min(count($letters) - 1, count($this->payments) * 2 + 4)] . '1')
                       ->getFont()->getColor()->setARGB('FFFFFFFF');

        $count = 3;
        $batchSize = 500;
        $processed = 0;

        foreach ($this->exportData as $causal => $record) {
            $parts = explode("§", $causal);
            $d = $parts[0] ?? '';
            $c = $parts[1] ?? '';
            $j = $parts[2] ?? '';
            $det = $parts[3] ?? '';
            $deleted = $parts[4] ?? '';

            $detailParts = explode('|', $det);
            $exportDetail = count($detailParts) > 1 ? implode(', ', array_slice($detailParts, 1)) : $det;

            $activeWorksheet->setCellValue('A' . $count, !empty($d) ? date("d/m/Y", strtotime($d)) : '');
            $activeWorksheet->setCellValue('B' . $count, $c);
            $activeWorksheet->setCellValue('C' . $count, $exportDetail);
            $activeWorksheet->setCellValue('D' . $count, $j);

            $stato = ($deleted === 'DELETED') ? 'ANNULLATA' : '';
            $activeWorksheet->setCellValue('E' . $count, $stato);

            if ($stato === 'ANNULLATA') {
                $activeWorksheet->getStyle('E' . $count)->getFont()->getColor()->setARGB('FFFF0000');
            }

            $idx = 0;
            foreach ($this->payments as $p) {
                if ($idx >= count($letters) - 1) break;

                if (isset($record[$p['name']])) {
                    $inValue = isset($record[$p['name']]["IN"]) ? $this->formatPrice($record[$p['name']]["IN"]) : "";
                    $outValue = isset($record[$p['name']]["OUT"]) ? $this->formatPrice($record[$p['name']]["OUT"]) : "";

                    $activeWorksheet->setCellValue($letters[$idx] . $count, $inValue);
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, $outValue);
                    $idx++;
                } else {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    $idx++;
                }
            }

            $count++;
            $processed++;

            if ($processed % $batchSize === 0) {
                gc_collect_cycles();
            }
        }

        $count++;
        $activeWorksheet->setCellValue('A' . $count, 'TOTALE');
        $activeWorksheet->setCellValue('B' . $count, '');
        $activeWorksheet->setCellValue('C' . $count, '');
        $activeWorksheet->setCellValue('D' . $count, '');
        $activeWorksheet->setCellValue('E' . $count, '');

        $idx = 0;
        foreach ($this->payments as $p) {
            if ($idx >= count($letters) - 1) break;

            if (isset($this->exportTotals[$p['name']])) {
                if ($p['type'] == 'ALL') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, $this->formatPrice($this->exportTotals[$p['name']]["IN"] ?? 0));
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, $this->formatPrice($this->exportTotals[$p['name']]["OUT"] ?? 0));
                    $idx++;
                } elseif ($p['type'] == 'IN') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, $this->formatPrice($this->exportTotals[$p['name']]["IN"] ?? 0));
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    $idx++;
                } elseif ($p['type'] == 'OUT') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, $this->formatPrice($this->exportTotals[$p['name']]["OUT"] ?? 0));
                    $idx++;
                }
            } else {
                $activeWorksheet->setCellValue($letters[$idx] . $count, "0,00");
                $idx++;
                $activeWorksheet->setCellValue($letters[$idx] . $count, "0,00");
                $idx++;
            }
        }

        $activeWorksheet->getStyle('A' . $count . ':' . $letters[min(count($letters) - 1, count($this->payments) * 2 + 4)] . $count)
                       ->getFont()->setBold(true);

        $activeWorksheet->getStyle('A' . $count . ':' . $letters[min(count($letters) - 1, count($this->payments) * 2 + 4)] . $count)
                       ->getFill()
                       ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                       ->getStartColor()->setARGB('FFF0F0F0');

        $activeWorksheet->getColumnDimension('A')->setWidth(15);
        $activeWorksheet->getColumnDimension('B')->setWidth(25);
        $activeWorksheet->getColumnDimension('C')->setWidth(30);
        $activeWorksheet->getColumnDimension('D')->setWidth(25);
        $activeWorksheet->getColumnDimension('E')->setWidth(15);

        foreach ($letters as $l) {
            $activeWorksheet->getColumnDimension($l)->setWidth(15);
        }

        $activeWorksheet->freezePane('A3');

        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);

        unset($spreadsheet, $activeWorksheet, $writer);
        gc_collect_cycles();
    }

    /**
     * Format price for display
     */
    private function formatPrice($amount)
    {
        return number_format($amount, 2, ',', '.');
    }

    /**
     * Get description of applied filters
     */
    private function getFiltersDescription()
    {
        $descriptions = [];

        if (!empty($this->filters['member'])) {
            $descriptions[] = "Utente: {$this->filters['member']}";
        }

        if (!empty($this->filters['causals'])) {
            $descriptions[] = "Causali: " . (is_array($this->filters['causals']) ? implode(', ', $this->filters['causals']) : $this->filters['causals']);
        }

        return empty($descriptions) ? 'Nessun filtro applicato' : implode(' | ', $descriptions);
    }
}