iao_team/app/Http/Livewire/Record.php

<?php

namespace App\Http\Livewire;

use Livewire\Component;
use DateInterval;
use DatePeriod;
use DateTime;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Log;


class Record extends Component
{
    public $records, $dataId, $totals;

    public $in;
    public $out;

    public $payments = [];

    public $fromDate;
    public $toDate;

    public $appliedFromDate;
    public $appliedToDate;

    public $selectedPeriod = 'OGGI';

    public $filterCausals = null;
    public $filterMember = null;

    public $isFiltering = false;

    public array $recordDatas = [];
    public array $labels = [];

    public array $causals = [];
    public $members = array();

    public function hydrate()
    {
        $this->emit('load-select');
    }

    public function mount()
    {
        $this->fromDate = date("Y-m-d");
        $this->toDate = date("Y-m-d");

        $this->appliedFromDate = date("Y-m-d");
        $this->appliedToDate = date("Y-m-d");

        $this->getCausals(\App\Models\Causal::select('id', 'name')->where('parent_id', null)->get(), 0);

        $this->members = \App\Models\Member::select(['id', 'first_name', 'last_name', 'fiscal_code'])->orderBy('last_name')->orderBy('first_name')->get();

        $this->payments = \App\Models\PaymentMethod::select('id', 'name', 'type')->where('enabled', true)->where('money', false)->get();
    }

    public function resetFilters()
    {
        $this->selectedPeriod = 'OGGI';
        $this->filterCausals = [];
        $this->filterMember = null;

        $today = date("Y-m-d");
        $this->fromDate = $today;
        $this->toDate = $today;
        $this->appliedFromDate = $today;
        $this->appliedToDate = $today;

        $this->emit('filters-reset');
    }

    public function applyFilters()
    {
        $this->isFiltering = true;

        $this->setPeriodDates();

        $this->appliedFromDate = $this->fromDate;
        $this->appliedToDate = $this->toDate;

        $this->render();

        $this->isFiltering = false;

        $this->emit('filters-applied');
    }

    private function setPeriodDates()
    {
        $today = now();

        switch ($this->selectedPeriod) {
            case 'OGGI':
                $this->fromDate = $today->format('Y-m-d');
                $this->toDate = $today->format('Y-m-d');
                break;

            case 'IERI':
                $yesterday = $today->copy()->subDay();
                $this->fromDate = $yesterday->format('Y-m-d');
                $this->toDate = $yesterday->format('Y-m-d');
                break;

            case 'MESE CORRENTE':
                $this->fromDate = $today->copy()->startOfMonth()->format('Y-m-d');
                $this->toDate = $today->copy()->endOfMonth()->format('Y-m-d');
                break;

            case 'MESE PRECEDENTE':
                $lastMonth = $today->copy()->subMonth();
                $this->fromDate = $lastMonth->startOfMonth()->format('Y-m-d');
                $this->toDate = $lastMonth->endOfMonth()->format('Y-m-d');
                break;

            case 'ULTIMO TRIMESTRE':
                $this->fromDate = $today->copy()->subMonths(3)->startOfMonth()->format('Y-m-d');
                $this->toDate = $today->copy()->subMonth()->endOfMonth()->format('Y-m-d');
                break;

            case 'ULTIMO QUADRIMESTRE':
                $this->fromDate = $today->copy()->subMonths(4)->startOfMonth()->format('Y-m-d');
                $this->toDate = $today->copy()->subMonth()->endOfMonth()->format('Y-m-d');
                break;
        }
    }

    public function getCausals($records, $indentation)
    {
        foreach ($records as $record) {
            $this->causals[] = array('id' => $record->id, 'name' => $record->getTree(), 'text' => $record->getTree(), 'level' => $indentation);
            if (count($record->childs))
                $this->getCausals($record->childs, $indentation + 1);
        }
    }

    public function getMonth($m)
    {
        $ret = '';
        switch ($m) {
            case 1:
                $ret = 'Gennaio';
                break;
            case 2:
                $ret = 'Febbraio';
                break;
            case 3:
                $ret = 'Marzo';
                break;
            case 4:
                $ret = 'Aprile';
                break;
            case 5:
                $ret = 'Maggio';
                break;
            case 6:
                $ret = 'Giugno';
                break;
            case 7:
                $ret = 'Luglio';
                break;
            case 8:
                $ret = 'Agosto';
                break;
            case 9:
                $ret = 'Settembre';
                break;
            case 10:
                $ret = 'Ottobre';
                break;
            case 11:
                $ret = 'Novembre';
                break;
            case 12:
                $ret = 'Dicembre';
                break;
            default:
                $ret = '';
                break;
        }
        return $ret;
    }

    public function render()
    {
        $month = 0;
        $year = 0;

        $this->records = array();
        $this->totals = array();

        $exclude_from_records = \App\Models\Member::where('exclude_from_records', true)->pluck('id')->toArray();

        $datas = \App\Models\Record::with('member', 'supplier', 'payment_method')
            ->select('records.*', 'records_rows.*')
            ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
            ->whereBetween('date', [$this->appliedFromDate, $this->appliedToDate])
            ->where(function ($query) {
                $query->where('type', 'OUT')
                    ->orWhere(function ($query) {
                        $query->where('records.corrispettivo_fiscale', true)
                            ->orWhere('records.commercial', false);
                    });
            })
            ->where(function ($query) use ($exclude_from_records) {
                $query->where('type', 'OUT')
                    ->orWhere(function ($subquery) use ($exclude_from_records) {
                        $subquery->whereNotIn('member_id', $exclude_from_records);
                    });
            });

        if ($this->filterCausals != null && sizeof($this->filterCausals) > 0) {
            $causals = array();
            foreach ($this->filterCausals as $z) {
                $causals[] = $z;
                $childs = \App\Models\Causal::where('parent_id', $z)->get();
                foreach ($childs as $c) {
                    $causals[] = $c->id;
                    $childsX = \App\Models\Causal::where('parent_id', $c->id)->get();
                    foreach ($childsX as $cX) {
                        $causals[] = $cX->id;
                    }
                }
            }
            $datas->whereIn('causal_id', $causals);
        }
        if ($this->filterMember != null && $this->filterMember > 0) {
            $datas->where('member_id', $this->filterMember);
        }
        $datas = $datas->orderBy('date', 'ASC')
            ->orderBy('records.created_at', 'ASC')
            ->orderBy('records_rows.id', 'ASC')
            ->get();

        $groupedData = [];
        $causalsCount = [];
        $nominativi = [];

        foreach ($datas as $idx => $data) {

            $causalCheck = \App\Models\Causal::findOrFail($data->causal_id);
            $paymentCheck = $data->payment_method->money;

            if (!$paymentCheck && ($causalCheck->no_first == null || !$causalCheck->no_first)) {

                if (!$data->deleted) {
                    $amount = $data->amount;
                    $amount += getVatValue($amount, $data->vat_id);
                } else {
                    $amount = $data->amount;
                }

                // Determine the type (commercial or non-commercial)
                $typeLabel = $data->commercial ? 'Commerciale' : 'Non Commerciale';

                // Get nominativo
                $nominativo = '';
                if ($data->type == "IN") {
                    if ($data->member) {
                        $nominativo = $data->member->last_name . " " . $data->member->first_name;
                    }
                } else {
                    if ($data->supplier) {
                        $nominativo = $data->supplier->name;
                    }
                }

                // Create grouping key: date + type + payment_method + IN/OUT + nominativo
                $groupKey = $data->date . '|' . $typeLabel . '|' . $data->payment_method->name . '|' . $data->type . '|' . $nominativo;

                // Initialize group if not exists
                if (!isset($groupedData[$groupKey])) {
                    $groupedData[$groupKey] = [
                        'date' => $data->date,
                        'type_label' => $typeLabel,
                        'payment_method' => $data->payment_method->name,
                        'transaction_type' => $data->type,
                        'nominativo' => $nominativo,
                        'amount' => 0,
                        'deleted' => false,
                        'causals' => [],
                        'notes' => []
                    ];
                    $causalsCount[$groupKey] = [];
                    $nominativi[$groupKey] = $nominativo;
                }

                $groupedData[$groupKey]['amount'] += $amount;

                $causalsCount[$groupKey][$causalCheck->getTree()] = true;

                if (!empty($data->note)) {
                    $groupedData[$groupKey]['notes'][] = $data->note;
                }

                if ($data->deleted) {
                    $groupedData[$groupKey]['deleted'] = true;
                }
            }
        }

        foreach ($groupedData as $groupKey => $group) {
            $causalsInGroup = array_keys($causalsCount[$groupKey]);

            $causalDisplay = $group['type_label'];

            if (count($causalsInGroup) > 1) {
                $detailDisplay = 'Varie|' . implode('|', $causalsInGroup);
            } else {
                $detailDisplay = $causalsInGroup[0];
            }

            $recordKey = $group['date'] . "§" . $causalDisplay . "§" . $group['nominativo'] . "§" . $detailDisplay . "§" . ($group['deleted'] ? 'DELETED' : '') . "§";

            if (!isset($this->records[$recordKey][$group['payment_method']][$group['transaction_type']])) {
                $this->records[$recordKey][$group['payment_method']][$group['transaction_type']] = 0;
            }

            $this->records[$recordKey][$group['payment_method']][$group['transaction_type']] += $group['amount'];

            if (!isset($this->totals[$group['payment_method']])) {
                $this->totals[$group['payment_method']]["IN"] = 0;
                $this->totals[$group['payment_method']]["OUT"] = 0;
            }

            if (!$group['deleted'])
                $this->totals[$group['payment_method']][$group['transaction_type']] += $group['amount'];
        }

        return view('livewire.records');
    }

    private function getLabels($fromDate, $toDate)
    {
        $begin = new DateTime($fromDate);
        $end = new DateTime($toDate);

        $interval = DateInterval::createFromDateString('1 day');
        $date_range = new DatePeriod($begin, $interval, $end);
        foreach ($date_range as $date) {
            $labels[] = $date->format('d/M');
        }

        return $labels;
    }

    private function getRecordData($type, $fromDate, $toDate)
    {
        $data = [];
        $begin = new DateTime($fromDate);
        $end = new DateTime($toDate);

        $interval = DateInterval::createFromDateString('1 day');
        $date_range = new DatePeriod($begin, $interval, $end);

        foreach ($date_range as $date) {
            if ($type == 'IN') {
                $found = false;
                foreach ($this->in as $in) {
                    if (date("Y-m-d", strtotime($in->date)) == $date->format('Y-m-d')) {
                        $data[] = number_format($in->total, 0, "", "");
                        $found = true;
                    }
                }
                if (!$found)
                    $data[] = 0;
            }
            if ($type == 'OUT') {
                $found = false;
                foreach ($this->out as $out) {
                    if (date("Y-m-d", strtotime($out->date)) == $date->format('Y-m-d')) {
                        $data[] = number_format($out->total, 0, "", "");
                        $found = true;
                    }
                }
                if (!$found)
                    $data[] = 0;
            }
        }

        return $data;
    }

    public function export()
    {
        ini_set('memory_limit', '512M');
        gc_enable();

        $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->setCellValue('A1', "Data");
        $activeWorksheet->setCellValue('B1', "Tipo");
        $activeWorksheet->setCellValue('C1', "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);
            $idx++;
            if ($idx >= count($letters)) {
                break;
            }
            $activeWorksheet->mergeCells($letters[$idx] . '1:' . $letters[$idx] . '1');
            $idx++;
        }

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

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

        $activeWorksheet->getStyle('A1:Q1')->getFont()->setBold(true);
        $activeWorksheet->getStyle('A2:Q2')->getFont()->setBold(true);

        $count = 3;
        $batchSize = 1000;
        $recordsProcessed = 0;

        $totalRecords = count($this->records);
        $recordsArray = array_chunk($this->records, $batchSize, true);

        foreach ($recordsArray as $recordsBatch) {
            foreach ($recordsBatch as $causal => $record) {
                $check = $causal;

                $parts = explode("§", $check);
                $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"]) ? formatPrice($record[$p->name]["IN"]) : "";
                        $outValue = isset($record[$p->name]["OUT"]) ? 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++;
                $recordsProcessed++;

                if ($recordsProcessed % 500 === 0) {
                    gc_collect_cycles();
                }
            }

            unset($recordsBatch);
            gc_collect_cycles();
        }

        $count++;
        $idx = 0;

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

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

            if (isset($this->totals[$p->name])) {
                if ($p->type == 'ALL') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, formatPrice($this->totals[$p->name]["IN"] ?? 0));
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, formatPrice($this->totals[$p->name]["OUT"] ?? 0));
                    $idx++;
                } elseif ($p->type == 'IN') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, formatPrice($this->totals[$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, formatPrice($this->totals[$p->name]["OUT"] ?? 0));
                    $idx++;
                }
            } else {
                if ($p->type == 'ALL') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "0");
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "0");
                    $idx++;
                } elseif ($p->type == 'IN') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "0");
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    $idx++;
                } elseif ($p->type == 'OUT') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "0");
                    $idx++;
                }
            }
        }

        $activeWorksheet->getStyle('A' . $count . ':Q' . $count)->getFont()->setBold(true);

        $activeWorksheet->getColumnDimension('A')->setWidth(20);
        $activeWorksheet->getColumnDimension('B')->setWidth(40);
        $activeWorksheet->getColumnDimension('C')->setWidth(40);
        $activeWorksheet->getColumnDimension('D')->setWidth(40);
        $activeWorksheet->getColumnDimension('E')->setWidth(20);
        foreach ($letters as $l) {
            $activeWorksheet->getColumnDimension($l)->setWidth(20);
        }

        $filename = 'prima_nota_' . date("YmdHis") . '.xlsx';

        try {
            $currentClient = session('currentClient', 'default');

            $tempPath = sys_get_temp_dir() . '/' . $filename;

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

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

            $disk = Storage::disk('s3');

            $s3Path = $currentClient . '/prima_nota/' . $filename;

            $primaNotaFolderPath = $currentClient . '/prima_nota/.gitkeep';
            if (!$disk->exists($primaNotaFolderPath)) {
                $disk->put($primaNotaFolderPath, '');
                Log::info("Created prima_nota folder for client: {$currentClient}");
            }

            $fileContent = file_get_contents($tempPath);
            $uploaded = $disk->put($s3Path, $fileContent, 'private');

            if (!$uploaded) {
                throw new \Exception('Failed to upload file to Wasabi S3');
            }

            Log::info("Prima Nota exported to Wasabi", [
                'client' => $currentClient,
                'path' => $s3Path,
                'size' => filesize($tempPath),
                'records_processed' => $recordsProcessed
            ]);

            if (file_exists($tempPath)) {
                unlink($tempPath);
            }

            $downloadUrl = $disk->temporaryUrl($s3Path, now()->addHour());
            return redirect($downloadUrl);
        } catch (\Exception $e) {
            Log::error('Error exporting Prima Nota to Wasabi S3', [
                'error' => $e->getMessage(),
                'client' => session('currentClient', 'unknown'),
                'filename' => $filename,
                'records_processed' => $recordsProcessed ?? 0
            ]);

            $currentClient = session('currentClient', 'default');
            $clientFolder = storage_path('app/prima_nota/' . $currentClient);

            if (!is_dir($clientFolder)) {
                mkdir($clientFolder, 0755, true);
                Log::info("Created local client prima_nota folder: {$clientFolder}");
            }

            $localPath = $clientFolder . '/' . $filename;

            if (isset($tempPath) && file_exists($tempPath)) {
                rename($tempPath, $localPath);
            } else {
                $writer = new Xlsx($spreadsheet);
                $writer->save($localPath);
                unset($spreadsheet, $activeWorksheet, $writer);
            }

            gc_collect_cycles();

            Log::warning("Prima Nota saved locally due to S3 error", [
                'client' => $currentClient,
                'local_path' => $localPath,
                'error' => $e->getMessage()
            ]);

            session()->flash('warning', 'File salvato localmente a causa di un errore del cloud storage.');

            return response()->download($localPath)->deleteFileAfterSend();
        }
    }
}