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;

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();

                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)  {
                        $query->where('financial_movement', false)->orWhere('financial_movement', null);
                    })
                    ->orWhere(function ($subquery) use ($exclude_from_records) {
                        $subquery->whereNotIn('member_id', $exclude_from_records);
                    })
                    ->where('records_rows.when', 'like', '%"' . $f . '"%')->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($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;
        }

        $path = $this->generateExcel($this->columns, $rows_in, $this->records_in, $this->rows_out, $this->records_out,false);

        return response()->download($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)  {
                    $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;
                $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);
            }

        }

        $path = $this->generateExcel($columns, $this->rows_in, $records_in, $this->rows_out, $records_out, true);
        return response()->download($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);
                //$activeWorksheet->getStyle('A' . $count . ':N' . $count)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('b1ed5c');
            }

            $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);
                //$activeWorksheet->getStyle('A' . $count . ':N' . $count)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('ed6d61');
            }

            $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';

        $writer = new Xlsx($spreadsheet);
        $writer->save($path = storage_path(date("Ymd") .'_Gestionale_' . $fileSuffix .  '.xlsx'));

        return $path;

    }
}