iao_team/app/Http/Livewire/RecordINOUT.php

<?php

namespace App\Http\Livewire;

use Illuminate\Support\Carbon;
use Livewire\Component;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
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 = [];

        $vats = getVatMap();

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

                list($m, $y) = explode("-", $filter);

                $pairs = [];
                if ($m == 'x') {
                    $year = $y;
                    $next_year = $y + 1;
                    $start = Carbon::createFromFormat("Y-m-d", "$year-09-01")->startOfMonth();
                    $end = Carbon::createFromFormat("Y-m-d", "$next_year-08-31")->startOfMonth();
                    foreach (\Carbon\CarbonPeriod::create($start, '1 month', $end) as $d) {
                        $pairs[] = '"' . (string)$d->month . "-" . (string)$d->year . '"';
                    }
                    $pairs = implode("|", $pairs);
                } else {
                    $pairs = '"' . $filter . '"';
                }

                $incomeQuery = \App\Models\Record::where('records.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)
                    ->whereRaw('records_rows.when REGEXP ?', [$pairs]);
                $incomeRecords = $incomeQuery->get();

                foreach ($incomeRecords as $record) {
                    $total_months = count(json_decode($record->when, true));
                    $matching_months = 0;
                    $matches = [];
                    if (!preg_match_all("/$pairs/", $record->when, $matches)) continue;

                    $matching_months = count($matches[0]);

                    $amount = $record->amount;
                    if (isset($vats[$record->vat_id]) && $vats[$record->vat_id] > 0) {
                        $vat = $vats[$record->vat_id];
                        $amount += $amount * $vat;
                    }
                    $amount *= ($matching_months / $total_months);

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

                $expenseQuery = \App\Models\Record::where('records.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)
                    ->whereRaw('records_rows.when REGEXP ?', [$pairs]);
                $expenseRecords = $expenseQuery->get();

                foreach ($expenseRecords as $record) {
                    $when_array = json_decode($record->when, true);
                    if (!is_array($when_array) || empty($when_array) || count($when_array) <= 0) continue;

                    $total_months = count($when_array);

                    $matching_months = 0;
                    $matches = [];
                    if (!preg_match_all("/$pairs/", $record->when, $matches)) continue;

                    $matching_months = count($matches[0]);

                    $amount = $record->amount;
                    if (isset($vats[$record->vat_id]) && $vats[$record->vat_id] > 0) {
                        $vat = $vats[$record->vat_id];
                        $amount += $amount * $vat;
                    }
                    $amount *= ($matching_months / $total_months);

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

                    // Aggiorno i dati del padre
                    $this->updateParent("OUT", $record->causal_id, $amount, $filter);
                }
            }
        }

        //print $ccc."-";
        //print $ids;
        //$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;
        }
        if ($m == 'x') {
            $nextY = $y + 1;
            $ret = "{$y}/{$nextY}";
        } else {
            $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();
        $rows_out = 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;
        }

        if ($this->filterCausalsOut != null && sizeof($this->filterCausalsOut) > 0) {
            foreach ($this->rows_out as $r) {
                if (in_array($r["id"], $this->filterCausalsOut) || in_array($r["parent_id"], $this->filterCausalsOut) || in_array($r["first_parent_id"], $this->filterCausalsOut)) {
                    $rows_out[] = $r;
                }
            }
        } else {
            $rows_out = $this->rows_out;
        }

        $path = $this->generateExcel($this->columns, $rows_in, $this->records_in, $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) {

            if (date("m") < env('FISCAL_YEAR_MONTH_FROM', 1))
                $year -= 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;
    }
}