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;


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

    public $in;
    public $out;

    public $payments = [];

    public $fromDate;
    public $toDate;

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

    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->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 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.*') // Ensure all columns are selected
                    ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
                    ->whereBetween('date', [$this->fromDate, $this->toDate])
                    ->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') // Important to maintain row order
                    ->get();

        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;
                }

                $prefix = '';
                if (!$data->commercial)
                    $prefix = $idx . "$";


                // aggiungere il nome * * *
                $causal = $prefix . $data->date . "§" . $causalCheck->getTree() . "§" .
                ($data->type == "IN" ? ($data->member ? ($data->member->last_name . " " . $data->member->first_name) : "") :
                $data->supplier->name ?? "") . "§" . $data->note . "§" . ($data->deleted ? 'DELETED' : '') .
                "§" . $data->numero_linea;

                if (!isset($this->records[$causal][$data->payment_method->name][$data->type])) {
                    $this->records[$causal][$data->payment_method->name][$data->type] = 0;
                }

                // Add to the records array
                $this->records[$causal][$data->payment_method->name][$data->type] += $amount;

                // Initialize totals if needed
                if (!isset($this->totals[$data->payment_method->name])) {
                    $this->totals[$data->payment_method->name]["IN"] = 0;
                    $this->totals[$data->payment_method->name]["OUT"] = 0;
                }

                // Update totals if not deleted
                if (!$data->deleted)
                    $this->totals[$data->payment_method->name][$data->type] += $amount;// $data->amount;//$this->records[$causal][$data->payment_method->name][$data->type];

            }


        }

        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()
    {
        $letters = array('E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');

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

        //$activeWorksheet->setCellValue('A1', 'PrimaNota');
        $activeWorksheet->setCellValue('A1', "Data");
        $activeWorksheet->setCellValue('B1', "Causale");
        $activeWorksheet->setCellValue('C1', "Dettaglio");
        $activeWorksheet->setCellValue('D1', "Nominativo");
        $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', "");
        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:P1')->getFont()->setBold(true);
        $activeWorksheet->getStyle('A2:P2')->getFont()->setBold(true);
        // $activeWorksheet->getStyle('A1:N1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00ff00');

        $count = 3;

        $totals = [];

        foreach($this->records as $causal => $record)
        {

            $check = strpos($causal, "$") ? explode("$", $causal)[1] : $causal;
            list($d, $c, $j, $k) = explode("§", $check);

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

            $idx = 0;
            foreach($this->payments as $p)
            {
                if(isset($record[$p->name]))
                {

                    if(isset($record[$p->name]["IN"]))
                    {
                        $activeWorksheet->setCellValue($letters[$idx] . $count, formatPrice($record[$p->name]["IN"]));
                    }
                    else
                    {
                        $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    }
                    $idx++;
                    if(isset($record[$p->name]["OUT"]))
                    {
                        $activeWorksheet->setCellValue($letters[$idx] . $count, formatPrice($record[$p->name]["OUT"]));
                    }
                    else
                    {
                        $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    }
                    $idx++;

                }
                else
                {
                    if ($idx >= count($letters)) {
                        break;
                    }

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

            $count += 1;

        }

        $count += 1;

        $idx = 0;

        $activeWorksheet->setCellValue('A' . $count, 'Totale');
        $activeWorksheet->setCellValue('B' . $count, '');
        $activeWorksheet->setCellValue('C' . $count, '');
        $activeWorksheet->setCellValue('D' . $count, '');
        foreach($this->payments as $p)
        {
            if(isset($this->totals[$p->name]))
            {
                if($p->type == 'ALL'){
                    $activeWorksheet->setCellValue($letters[$idx] . $count, formatPrice($this->totals[$p->name]["IN"]));
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, formatPrice($this->totals[$p->name]["OUT"]));
                    $idx++;
                }
                elseif($p->type == 'IN'){
                    $activeWorksheet->setCellValue($letters[$idx] . $count, formatPrice($this->totals[$p->name]["IN"]));
                    $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"]));
                    $idx++;
                }
            }
            else
            {
                if($p->type == 'ALL'){
                    if ($idx >= count($letters)) {
                        break;
                    }

                    $activeWorksheet->setCellValue($letters[$idx] . $count, "0");
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "0");
                    $idx++;
                }
                elseif($p->type == 'IN'){
                    if ($idx >= count($letters)) {
                        break;
                    }

                    $activeWorksheet->setCellValue($letters[$idx] . $count, "0");
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    $idx++;
                }
                elseif($p->type == 'OUT'){
                    if ($idx >= count($letters)) {
                        break;
                    }

                    $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "0");
                    $idx++;
                }
            }
        }

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

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

        $writer = new Xlsx($spreadsheet);
        $writer->save($path = storage_path('prima_nota_' . date("YmdHis") . '.xlsx'));

        return response()->download($path)->deleteFileAfterSend();

    }

}