iao_team/app/Http/Livewire/RecordINOUT.php

<?php

namespace App\Http\Livewire;

use Livewire\Component;

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

class RecordINOUT extends Component
{
    //public $records_in, $records_out;
    public $total_in = 0;
    public $total_out = 0;

    public $datas = [];
    public $month;
    public $year;

    public $months_1 = [];
    public $year_1;

    public $months_2 = [];
    public $year_2;
    public $show_block_2;

    //public $borsellino_id = 0;

    public $columns = array();
    public $rows_in = array();
    public $rows_out = array();
    public $records_in = array();
    public $records_out = array();

    public $showData = true;

    public $hasFilter = false;

    public $total = 0;

    public $selectedFilter = '';

    public $causalsIn = array();
    public $causalsOut = array();
    public $payments = array();
    public $members = array();
    public $filterCausalsIn = null;
    public $filterCausalsOut = null;

    public $excludeCausals = array();

    public function mount()
    {

        if (Auth::user()->level != env('LEVEL_ADMIN', 0))
            return redirect()->to('/dashboard');

        $borsellino = \App\Models\Causal::where('money', true)->first();
        if ($borsellino)
            $this->excludeCausals[] = $borsellino->id;
        //$this->borsellino_id = $borsellino->id;

        // Aggiungo
        $excludes = \App\Models\Causal::where('no_records', true)->get();
        foreach ($excludes as $e) {
            $this->excludeCausals[] = $e->id;
        }


        $this->month = date("m");
        $this->year = date("Y");
        $this->year_1 = date("Y");
        $this->year_2 = date("Y");
        $this->show_block_2 = false;

        $this->getCausale(\App\Models\Causal::select('id', 'name')->where('parent_id', null)->where('type', 'IN')->whereNotIn('id', $this->excludeCausals)->get(), 'IN', 0);
        $this->getCausale(\App\Models\Causal::select('id', 'name')->where('parent_id', null)->where('type', 'OUT')->whereNotIn('id', $this->excludeCausals)->get(), 'OUT', 0);

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

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

        //$this->causalsIn = \App\Models\Causal::where('parent_id', null)->where('type', 'IN')->whereNotIn('id', $this->excludeCausals)->get();
        //$this->causalsOut = \App\Models\Causal::where('parent_id', null)->where('type', 'OUT')->whereNotIn('id', $this->excludeCausals)->get();
    }

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

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

    public function getCausale($records, $type, $indentation)
    {
        foreach ($records as $record) {
            $first_parent_id = null;
            if ($record->parent_id != null) {
                $first_parent_id = \App\Models\Causal::where('id', $record->parent_id)->first()->parent_id;
            }
            if ($type == 'IN')
                $this->rows_in[] = array('id' => $record->id, 'name' => $record->name, 'level' => $indentation, 'parent_id' => $record->parent_id, 'parent_name' => $this->getCausalName($record->parent_id), 'first_parent_id' => $first_parent_id, 'first_parent_name' => $this->getCausalName($first_parent_id), 'all_childs' => $this->getAllChild($record->id));
            if ($type == 'OUT')
                $this->rows_out[] = array('id' => $record->id, 'name' => $record->name, 'level' => $indentation, 'parent_id' => $record->parent_id, 'parent_name' => $this->getCausalName($record->parent_id), 'first_parent_id' => $first_parent_id, 'first_parent_name' => $this->getCausalName($first_parent_id), 'all_childs' => $this->getAllChild($record->id));
            if (count($record->childs))
                $this->getCausale($record->childs, $type, $indentation + 1);
        }
    }

    public function getAllChild($id)
    {
        $aChilds = array();
        $aChilds[] = $id;


        $record = \App\Models\Causal::findOrFail($id);
        $aChilds[] = $record->parent_id;
        if ($record->parent_id != null) {
            $first_parent_id = \App\Models\Causal::where('id', $record->parent_id)->first()->parent_id;
            $aChilds[] = $first_parent_id;
        }

        $childs = \App\Models\Causal::where('parent_id', $id)->get();
        foreach ($childs as $child) {
            $aChilds[] = $child->id;
            $childs2 = \App\Models\Causal::where('parent_id', $child->id)->get();
            foreach ($childs2 as $child2) {
                $aChilds[] = $child2->id;
                $childs3 = \App\Models\Causal::where('parent_id', $child2->id)->get();
                foreach ($childs3 as $child3) {
                    $aChilds[] = $child3->id;
                }
            }
        }
        return $aChilds;
    }


    public function getCausalName($id)
    {
        if ($id > 0)
            return \App\Models\Causal::findOrFail($id)->name;
        else
            return "";
    }

    public function render()
    {
        return view('livewire.records_in_out');
    }

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

    public function show($m, $y)
    {
        if ($m != "" && $y != "" && !in_array($m . "-" . $y, $this->datas))
            $this->datas[] = $m . "-" . $y;

        $this->columns = array();
        $this->records_in = [];
        $this->records_out = [];

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

        if (sizeof($this->datas) > 0) {
            foreach ($this->datas as $filter) {
                $this->columns[] = $filter;

                $f = $filter;
                if ($m == 'x')
                    $f = str_replace("x-", "", $filter);
                $records = \App\Models\Record::where('type', 'IN')
                    ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
                    ->whereNotIn('records_rows.causal_id', $this->excludeCausals)
                    ->where(function ($query) {
                        $query->where('deleted', false)->orWhere('deleted', null);
                    })
                    ->where(function ($query) {
                        $query->where('financial_movement', false)->orWhere('financial_movement', null);
                    })
                    ->whereNotIn('member_id', $exclude_from_records)
                    ->where('records_rows.when', 'like', '%"' . $f . '"%')
                    ->get();

                //Log::info('Record In' . $records);
                foreach ($records as $record) {
                    $amount = $record->amount;
                    $amount += getVatValue($amount, $record->vat_id);
                    $when = sizeof(json_decode($record->when));
                    if ($when > 1) {
                        $amount = $amount / $when;
                        $record->amount = $amount;
                    }
                    // Aggiungo/aggiorno i dati
                    if (isset($this->records_in[$filter][$record->causal_id]))
                        $this->records_in[$filter][$record->causal_id] += $amount;
                    else
                        $this->records_in[$filter][$record->causal_id] = $amount;
                    // Aggiorno i dati del padre
                    $this->updateParent("IN", $record->causal_id, $amount, $filter);
                }
                $records = \App\Models\Record::where('type', 'OUT')
                    ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
                    ->whereNotIn('records_rows.causal_id', $this->excludeCausals)
                    ->where(function ($query) {
                        $query->where('deleted', false)->orWhere('deleted', null);
                    })

                    ->where(function ($query) use ($exclude_from_records) {
                        $query->whereNull('member_id')
                            ->orWhereNotIn('member_id', $exclude_from_records);
                    })
                    ->where('records_rows.when', 'like', '%"' . $f . '"%')->get();
                Log::info('Record Out' . $records);
                foreach ($records as $record) {
                    $amount = $record->amount;
                    $amount += getVatValue($amount, $record->vat_id);
                    $when = sizeof(json_decode($record->when));
                    if ($when > 1) {
                        $amount = $amount / $when;
                        $record->amount = $amount;
                    }
                    // Aggiungo/aggiorno i dati
                    if (isset($this->records_out[$filter][$record->causal_id]))
                        $this->records_out[$filter][$record->causal_id] += $amount;
                    else
                        $this->records_out[$filter][$record->causal_id] = $amount;
                    $this->updateParent("OUT", $record->causal_id, $amount, $filter);
                }
            }
        }

        //$this->showData = true;
        $this->emit('load-table');
    }

    public function updateParent($type, $causal_id, $amount, $filter)
    {
        if ($type == "IN") {
            foreach ($this->rows_in as $r) {
                if ($r["id"] == $causal_id) {
                    if (isset($this->records_in[$filter][$r["parent_id"]]))
                        $this->records_in[$filter][$r["parent_id"]] += $amount;
                    else
                        $this->records_in[$filter][$r["parent_id"]] = $amount;
                    if ($r["parent_id"] > 0)
                        $this->updateParent("IN", $r["parent_id"], $amount, $filter);
                }
            }
        }
        if ($type == "OUT") {
            foreach ($this->rows_out as $r) {
                if ($r["id"] == $causal_id) {
                    if (isset($this->records_out[$filter][$r["parent_id"]]))
                        $this->records_out[$filter][$r["parent_id"]] += $amount;
                    else
                        $this->records_out[$filter][$r["parent_id"]] = $amount;
                    if ($r["parent_id"] > 0)
                        $this->updateParent("OUT", $r["parent_id"], $amount, $filter);
                }
            }
        }
    }

    public function updateParentYear($type, $causal_id, $amount, $filter, &$records_in, &$records_out)
    {
        if ($type == "IN") {
            foreach ($this->rows_in as $r) {
                if ($r["id"] == $causal_id) {
                    if (isset($records_in[$filter][$r["parent_id"]]))
                        $records_in[$filter][$r["parent_id"]] += $amount;
                    else
                        $records_in[$filter][$r["parent_id"]] = $amount;
                    if ($r["parent_id"] > 0)
                        $this->updateParentYear("IN", $r["parent_id"], $amount, $filter, $records_in, $records_out);
                }
            }
        }
        if ($type == "OUT") {
            foreach ($this->rows_out as $r) {
                if ($r["id"] == $causal_id) {
                    if (isset($records_out[$filter][$r["parent_id"]]))
                        $records_out[$filter][$r["parent_id"]] += $amount;
                    else
                        $records_out[$filter][$r["parent_id"]] = $amount;
                    if ($r["parent_id"] > 0)
                        $this->updateParentYear("OUT", $r["parent_id"], $amount, $filter, $records_in, $records_out);
                }
            }
        }
    }

    public function getCausal($causal)
    {
        $ret = '';
        if ($causal > 0) {
            $ret = \App\Models\Causal::findOrFail($causal)->getTree();
        }
        return $ret;
    }

    public function getMonth($str)
    {
        $ret = '';
        list($m, $y) = explode("-", $str);
        switch ($m) {
            case 'x':
                $ret = '';
                break;
            case '01':
                $ret = 'Gennaio ';
                break;
            case '02':
                $ret = 'Febbraio ';
                break;
            case '03':
                $ret = 'Marzo ';
                break;
            case '04':
                $ret = 'Aprile ';
                break;
            case '05':
                $ret = 'Maggio ';
                break;
            case '06':
                $ret = 'Giugno ';
                break;
            case '07':
                $ret = 'Luglio ';
                break;
            case '08':
                $ret = 'Agosto ';
                break;
            case '09':
                $ret = 'Settembre ';
                break;
            case '10':
                $ret = 'Ottobre ';
                break;
            case '11':
                $ret = 'Novembre ';
                break;
            case '12':
                $ret = 'Dicembre ';
                break;
            default:
                $ret = '';
                break;
        }
        $ret .= $y;
        return $ret;
    }

    public function clear()
    {
        $this->columns = [];
        $this->datas = [];
        $this->records_out = [];
        $this->records_in = [];
        $this->emit('load-select');
        $this->emit('reset-collapse');
        //$this->showData = false;
    }

    public function remove($idx)
    {

        if (sizeof($this->datas) > 1)
            array_splice($this->datas, $idx, 1);
        //unset($this->datas[$idx]);
        else
            $this->datas = array();

        $this->show('', '');
    }

    public function export()
    {

        $rows_in = array();

        if ($this->filterCausalsIn != null && sizeof($this->filterCausalsIn) > 0) {
            foreach ($this->rows_in as $r) {
                if (in_array($r["id"], $this->filterCausalsIn) || in_array($r["parent_id"], $this->filterCausalsIn) || in_array($r["first_parent_id"], $this->filterCausalsIn)) {
                    $rows_in[] = $r;
                }
            }
        } else {
            $rows_in = $this->rows_in;
        }
        $result = $this->generateExcel($this->columns, $rows_in, $this->records_in, $this->rows_out, $this->records_out, false);

        if ($result['storage_type'] === 's3') {
            try {
                $disk = Storage::disk('wasabi');
                $downloadUrl = $disk->temporaryUrl($result['path'], now()->addHour());

                return redirect($downloadUrl);
            } catch (\Exception $e) {
                Log::error('Error generating S3 download URL for export', [
                    'error' => $e->getMessage(),
                    'path' => $result['path']
                ]);
                session()->flash('error', 'Errore durante la generazione del link di download. Riprova.');
                return back();
            }
        } else {
            return response()->download($result['path'])->deleteFileAfterSend();
        }
    }

    public function exportYear($year)
    {
        $records_in = [];
        $records_out = [];
        $datas = [];
        if (env('FISCAL_YEAR_MONTH_FROM', 1) > 1) {
            for ($m = env('FISCAL_YEAR_MONTH_FROM', 1); $m <= 12; $m++) {
                $datas[] = $m . "-" . $year;
            }
            for ($m = 1; $m <= env('FISCAL_YEAR_MONTH_TO', 12); $m++) {
                $datas[] = $m . "-" . ($year + 1);
            }
        } else {
            for ($m = 1; $m <= 12; $m++) {
                $datas[] = $m . "-" . $year;
            }
        }

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

        foreach ($datas as $filter) {

            $columns[] = $filter;

            $records = \App\Models\Record::where('type', 'IN')
                ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
                ->whereNotIn('records_rows.causal_id', $this->excludeCausals)
                ->where(function ($query) {
                    $query->where('deleted', false)->orWhere('deleted', null);
                })
                ->where(function ($query) {
                    $query->where('financial_movement', false)->orWhere('financial_movement', null);
                })
                ->whereNotIn('member_id', $exclude_from_records)
                ->where('records_rows.when', 'like', '%"' . $filter . '"%')->get();
            //$records = $records->orderBy('date', 'DESC')->get();

            foreach ($records as $record) {
                $amount = $record->amount;
                $amount += getVatValue($amount, $record->vat_id);
                $when = sizeof(json_decode($record->when));
                if ($when > 1) {
                    $amount = $amount / $when;
                    $record->amount = $amount;
                }
                // Aggiungo/aggiorno i dati
                if (isset($records_in[$filter][$record->causal_id]))
                    $records_in[$filter][$record->causal_id] += $amount;
                else
                    $records_in[$filter][$record->causal_id] = $amount;
                // Aggiorno i dati del padre
                $this->updateParentYear("IN", $record->causal_id, $amount, $filter, $records_in, $records_out);
            }

            $records = \App\Models\Record::where('type', 'OUT')
                ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
                ->whereNotIn('records_rows.causal_id', $this->excludeCausals)
                ->where(function ($query) {
                    $query->where('deleted', false)->orWhere('deleted', null);
                })
                ->where(function ($query) use ($exclude_from_records) {
                    $query->whereNull('member_id')
                        ->orWhereNotIn('member_id', $exclude_from_records);
                })
                ->where('records_rows.when', 'like', '%"' . $filter . '"%')->get();
            //$records = $records->orderBy('date', 'DESC')->get();
            foreach ($records as $record) {
                $amount = $record->amount;
                $when = sizeof(json_decode($record->when));
                if ($when > 1) {
                    $amount = $amount / $when;
                    $record->amount = $amount;
                }
                // Aggiungo/aggiorno i dati
                if (isset($records_out[$filter][$record->causal_id]))
                    $records_out[$filter][$record->causal_id] += $amount;
                else
                    $records_out[$filter][$record->causal_id] = $amount;
                $this->updateParentYear("OUT", $record->causal_id, $amount, $filter, $records_in, $records_out);
            }
        }

        $result = $this->generateExcel($columns, $this->rows_in, $records_in, $this->rows_out, $records_out, true);

        if ($result['storage_type'] === 's3') {
            $disk = Storage::disk('wasabi');
            $downloadUrl = $disk->temporaryUrl($result['path'], now()->addHour());
            return redirect($downloadUrl);
        } else {
            return response()->download($result['path'])->deleteFileAfterSend();
        }
    }

    public function generateExcel($columns, $rows_in, $records_in, $rows_out, $records_out, $isYearExport)
    {
        $letters = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N');

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

        $activeWorksheet->setCellValue('A1', 'Entrate');
        foreach ($columns as $idx => $column) {
            $activeWorksheet->setCellValue($letters[$idx + 1] . '1', $this->getMonth($column));
        }

        $activeWorksheet->getStyle('A1:N1')->getFont()->setBold(true);
        $activeWorksheet->getStyle('A1:N1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('C6E0B4'); // Lighter green

        $count = 2;

        $totals = [];

        foreach ($rows_in as $in) {
            $activeWorksheet->setCellValue('A' . $count, str_repeat("  ", $in["level"]) . $in["name"]);

            foreach ($columns as $idx => $column) {
                if (isset($records_in[$column][$in["id"]])) {
                    $activeWorksheet->setCellValue($letters[$idx + 1] . $count, formatPrice($records_in[$column][$in["id"]]));
                    if ($in["level"] == 0) {
                        if (isset($totals[$idx]))
                            $totals[$idx] += $records_in[$column][$in["id"]];
                        else
                            $totals[$idx] = $records_in[$column][$in["id"]];
                    }
                }
            }

            if ($in["level"] == 0) {
                $activeWorksheet->getStyle('A' . $count . ':N' . $count)->getFont()->setBold(true);
            }

            $count += 1;
        }

        $activeWorksheet->setCellValue('A' . $count, 'Totale');
        foreach ($totals as $idx => $total) {
            $activeWorksheet->setCellValue($letters[$idx + 1] . $count, formatPrice($total));
            $activeWorksheet->getStyle('A' . $count . ':N' . $count)->getFont()->setBold(true);
            $activeWorksheet->getStyle('A' . $count . ':N' . $count)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('C6E0B4'); // Lighter green
        }

        $count += 2;
        $activeWorksheet->setCellValue('A' . $count, "Uscite");
        foreach ($columns as $idx => $column) {
            $activeWorksheet->setCellValue($letters[$idx + 1] . $count, $this->getMonth($column));
        }

        $activeWorksheet->getStyle('A' . $count . ':N' . $count)->getFont()->setBold(true);
        $activeWorksheet->getStyle('A' . $count . ':N' . $count)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('F8CBAD'); // Lighter red

        $count += 1;

        $totals = [];

        foreach ($rows_out as $out) {
            $activeWorksheet->setCellValue('A' . $count, str_repeat("  ", $out["level"]) . $out["name"]);

            foreach ($columns as $idx => $column) {
                if (isset($records_out[$column][$out["id"]])) {
                    $activeWorksheet->setCellValue($letters[$idx + 1] . $count, formatPrice($records_out[$column][$out["id"]]));
                    if ($out["level"] == 0) {
                        if (isset($totals[$idx]))
                            $totals[$idx] += $records_out[$column][$out["id"]];
                        else
                            $totals[$idx] = $records_out[$column][$out["id"]];
                    }
                }
            }

            if ($out["level"] == 0) {
                $activeWorksheet->getStyle('A' . $count . ':N' . $count)->getFont()->setBold(true);
            }

            $count += 1;
        }

        $activeWorksheet->setCellValue('A' . $count, 'Totale');
        foreach ($totals as $idx => $total) {
            $activeWorksheet->setCellValue($letters[$idx + 1] . $count, formatPrice($total));
            $activeWorksheet->getStyle('A' . $count . ':N' . $count)->getFont()->setBold(true);
            $activeWorksheet->getStyle('A' . $count . ':N' . $count)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('F8CBAD'); // Lighter red
        }

        $activeWorksheet->getColumnDimension('A')->setWidth(35);

        for ($i = 1; $i < count($letters); $i++) {
            $activeWorksheet->getColumnDimension($letters[$i])->setWidth(20);
        }

        $fileSuffix = $isYearExport ? 'AnnoFiscale' : 'Selezione';
        $filename = date("Ymd") . '_Gestionale_' . $fileSuffix . '.xlsx';

        try {
            // Get current client for folder organization
            $currentClient = session('currentClient', 'default');

            // Create temporary file to write Excel data
            $tempPath = sys_get_temp_dir() . '/' . $filename;

            // Write to temporary file
            $writer = new Xlsx($spreadsheet);
            $writer->save($tempPath);

            // Get Wasabi disk
            $disk = Storage::disk('wasabi');

            // Create the S3 path (client folder + reports subfolder)
            $s3Path = $currentClient . '/reports/' . $filename;

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

            // Upload file to Wasabi S3
            $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("Management report uploaded to Wasabi", [
                'client' => $currentClient,
                'path' => $s3Path,
                'size' => filesize($tempPath),
                'type' => $fileSuffix
            ]);

            // Clean up temporary file
            if (file_exists($tempPath)) {
                unlink($tempPath);
            }

            // Return S3 path for further processing or download URL generation
            return [
                'success' => true,
                'path' => $s3Path,
                'local_path' => null,
                'storage_type' => 's3'
            ];
        } catch (\Exception $e) {
            Log::error('Error uploading management report to Wasabi S3', [
                'error' => $e->getMessage(),
                'client' => session('currentClient', 'unknown'),
                'filename' => $filename
            ]);

            // Fallback to local storage if S3 fails
            $currentClient = session('currentClient', 'default');
            $clientFolder = storage_path('app/reports/' . $currentClient);

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

            $localPath = $clientFolder . '/' . $filename;
            $writer = new Xlsx($spreadsheet);
            $writer->save($localPath);

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

            // Return local path for backward compatibility
            return [
                'success' => true,
                'path' => $localPath,
                'local_path' => $localPath,
                'storage_type' => 'local'
            ];
        }
    }
}