iao_team/app/Http/Livewire/CourseList.php

<?php

namespace App\Http\Livewire;

use Livewire\Component;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use App\Models\MemberCourse;
use Illuminate\Support\Facades\Log;

class CourseList extends Component
{

    public $records = array();
    public $recordsNoPaginate = array();
    public $courses = array();

    public $start = 0;
    public $totalRecords = 0;
    public $pages = 0;
    public $currentPage = 1;
    public $pageLength = 10;

    public $totS = [];
    public $totSExcel = [];

    public $sort = '';
    public $dir = '';

    public $hasFilter = false;

    public $courseId = 0;

    public $filterCourse = [];
    public $filterLevel = [];
    public $filterFrequency = [];
    public $filterType = [];
    public $filterDuration = [];

    public $course_durations = [];
    public $course_types = [];
    public $course_frequencies = [];
    public $course_levels = [];
    public $course_years = [];

    public $totals = [];
    public $totalIsc = [];

    public $aaa;

    public $months = array('Set', 'Ott', 'Nov', 'Dic', 'Gen', 'Feb', 'Mar', 'Apr', 'Mag', 'Giu', 'Lug', 'Ago');

    public function mount()
    {

        $this->selectedCourseId = 0;
        $this->selectedMemberId = 0;

        $this->course_types = \App\Models\CourseType::select('*')->where('enabled', true)->get();
        $this->course_durations = \App\Models\CourseDuration::select('*')->where('enabled', true)->get();
        $this->course_levels = \App\Models\CourseLevel::select('*')->where('enabled', true)->get();
        $this->course_frequencies = \App\Models\CourseFrequency::select('*')->where('enabled', true)->get();
        $this->course_years = \App\Models\Course::select('year')->where('year', '<>', '')->groupBy('year')->pluck('year');

        $this->courses = \App\Models\Course::orderBy('name')->groupBy('name')->pluck('name');

        //if (sizeof($this->courses) > 0)
        //    $this->courseId = $this->courses[0]->id;

    }

    public function updatedfilterCourse($value){
        $this->emit('load-data-table');
    }

    public function updatedfilterLevel($value){
        $this->emit('load-data-table');
    }

    public function updatedfilterFrequency($value){
        $this->emit('load-data-table');
    }

    public function updatedfilterType($value){
        $this->emit('load-data-table');
    }

    public function updatedfilterDuration($value){
        $this->emit('load-data-table');
    }

    public function updatedpageLength($value){
        $this->emit('load-data-table');
    }


    public function render()
    {

        $member_course = \App\Models\MemberCourse::with('member')->with('course');

        if ($this->hasFilter)
        {
            if (isset($_GET["search"]["value"]))
            {

                if ($_GET["search"]["value"] != '')
                {
                    $v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));
                    $member_ids = \App\Models\Member::where(function ($query) use ($v) {
                        $query->whereRaw("CONCAT(first_name, ' ', last_name) like '%" . $v . "%'")
                            ->orWhereRaw("CONCAT(last_name, ' ', first_name) like '%" . $v . "%'");
                    })->pluck('id');
                    /*
                    $v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));
                    $member_ids = \App\Models\Member::where(function ($query) use ($v) {
                        $query->where('first_name', 'like', '%' . $v . '%')
                                ->orWhere('last_name', 'like', '%' . $v . '%');
                    })->pluck('id');*/
                    $member_course = $member_course->whereIn('member_id', $member_ids);
                }
            }

            if (sizeof($this->filterCourse) > 0)
            {
                $course_ids = [];
                $courses = $this->filterCourse;
                foreach($courses as $c)
                {
                    $all = \App\Models\Course::where('name', 'like', '%' . $c . "%")->get();
                    foreach($all as $a)
                    {
                        $course_ids[] = $a->id;
                    }
                }
                $member_course = $member_course->whereIn('course_id', $course_ids);
            }

            if (sizeof($this->filterLevel) > 0)
            {
                $course_ids = \App\Models\Course::whereIn('course_level_id', $this->filterLevel)->pluck('id');
                $member_course = $member_course->whereIn('course_id', $course_ids);
            }

            if (sizeof($this->filterFrequency) > 0)
            {
                $course_ids = \App\Models\Course::whereIn('course_frequency_id', $this->filterFrequency)->pluck('id');
                $member_course = $member_course->whereIn('course_id', $course_ids);
            }
            if (sizeof($this->filterType) > 0)
            {
                $course_ids = \App\Models\Course::whereIn('course_type_id', $this->filterType)->pluck('id');
                $member_course = $member_course->whereIn('course_id', $course_ids);
            }
            if (sizeof($this->filterDuration) > 0)
            {
                $course_ids = \App\Models\Course::whereIn('course_duration_id', $this->filterDuration)->pluck('id');
                $member_course = $member_course->whereIn('course_id', $course_ids);
            }
        }

        $totals = [];
        $totalIsc = [];

        $datas = [];

        $xxx = 1;

        /*
        $sortColumn = '';
        if (isset($_GET["order"]))
        {

        }
        */

        $column = '';
        $sort_value = 0;
        if ($this->sort != '')
        {
            $f = $this->sort;
            $d = $this->dir;
            if ($f >= 5 && $f <= 16)
            {
                $column = 'column_' . ($f - 2);
                if (session()->get('sort_column'))
                {
                    if (session()->get('sort_column') != $f)
                    {
                        session()->put('sort_column', $f);
                        session()->put('sort_order', $d);
                        session()->put('sort_value', 0);
                        $sort_value = 0;
                    }
                    else
                    {
                        if (session()->get('sort_order') == $d)
                        {
                            //session()->put('sort_value', 0);
                            $sort_value = session()->get('sort_value', 0);
                        }
                        else
                        {
                            if (session()->get('sort_value', 0) == 0)
                            {
                                $sort_value = 1;
                            }
                            if (session()->get('sort_value', 0) == 1)
                            {
                                $sort_value = 2;
                            }
                            if (session()->get('sort_value', 0) == 2)
                            {
                                $sort_value = 3;
                            }
                            if (session()->get('sort_value', 0) == 3)
                            {
                                $sort_value = 0;
                            }
                            session()->put('sort_value', $sort_value);

                        }
                        session()->put('sort_order', $d);
                    }
                }
                else
                {
                    session()->put('sort_column', $f);
                    session()->put('sort_order', $d);
                    session()->put('sort_value', 0);
                    $sort_value = 0;
                }

            }
        }

        //print $sort_value;

        $totals = [];
        $prices = [];

        $member_course_totals = $member_course->get();
        foreach($member_course_totals as $x)
        {

            $price = 0;
            $price = $x->price; // $x->course->price;
            $subPrice = $x->subscription_price; // $x->course->subscription_price;
            $records = \App\Models\Record::where('member_course_id', $x->id)->where('deleted', 0)->get();
            $prices = [];
            foreach ($records as $record)
            {
                foreach ($record->rows as $row)
                {

                    if ($row->causal_id == $x->course->sub_causal_id) //  || str_contains(strtolower($row->note), 'iscrizione'))
                    //if (str_contains(strtolower($row->note), 'iscrizione'))
                    {
                        $subPrice = $row->amount;
                    }
                    if ($row->causal_id == $x->course->causal_id && !str_contains(strtolower($row->note), 'iscrizione'))
                    {
                        $tot = sizeof(json_decode($row->when));
                        foreach(json_decode($row->when) as $m)
                        {
                            if (isset($prices[$m->month]))
                                $prices[$m->month] += $row->amount / $tot;
                            else
                                $prices[$m->month] = $row->amount / $tot;
                        }
                    }
                }
            }

            for($i=1; $i<=12; $i++)
            {

                $cls = $this->getColor($x->months, $i, isset($prices[$i]) && $prices[$i] == $price);
                if ($cls != 'wgrey')
                {

                    if (!isset($totals[$i]))
                    {
                        $totals[$i]['green'] = 0;
                        $totals[$i]['orange'] = 0;
                        $totals[$i]['yellow'] = 0;
                    }
                    if ($cls == 'yellow')
                    {
                        $totals[$i][$cls] += 1;
                    }
                    else
                    {
                        $p = isset($prices[$i]) ? $prices[$i] : $price;
                        //if (isset($totals[$i][$cls]))
                            $totals[$i][$cls] += $p;
                        //else
                            //$totals[$i][$cls] = $p;
                    }
                }
            }

            $sub = $x->subscribed ? "Y" : "N";
            if (isset($totalIsc[$sub]))
                $totalIsc[$sub] += $subPrice;
            else
                $totalIsc[$sub] = $subPrice;

            $s = 0;
            if ($column != '')
            {
                $z = 0;
                switch ($column) {
                    case 'column_3':
                        $z = 9;
                        break;
                    case 'column_4':
                        $z = 10;
                        break;
                    case 'column_5':
                        $z = 11;
                        break;
                    case 'column_6':
                        $z = 12;
                        break;
                    case 'column_7':
                        $z = 1;
                        break;
                    case 'column_8':
                        $z = 2;
                        break;
                    case 'column_9':
                        $z = 3;
                        break;
                    case 'column_10':
                        $z = 4;
                        break;
                    case 'column_11':
                        $z = 5;
                        break;
                    case 'column_12':
                        $z = 6;
                        break;
                    case 'column_13':
                        $z = 7;
                        break;
                    case 'column_14':
                        $z = 8;
                        break;
                    default:
                        $z = 0;
                        break;
                }
                $c = getColor($x->months, $z);
                if ($sort_value == 0)
                {
                    switch ($c) {
                        case 'wgrey':
                            $s = 0;
                            break;
                        case 'orange':
                            $s = 1;
                            break;
                        case 'green':
                            $s = 2;
                            break;
                        case 'yellow':
                            $s = 3;
                            break;
                        default:
                            $s = 0;
                            break;
                    }
                }
                if ($sort_value == 1)
                {
                    switch ($c) {
                        case 'wgrey':
                            $s = 3;
                            break;
                        case 'orange':
                            $s = 0;
                            break;
                        case 'green':
                            $s = 1;
                            break;
                        case 'yellow':
                            $s = 2;
                            break;
                        default:
                            $s = 0;
                            break;
                    }
                }
                if ($sort_value == 2)
                {
                    switch ($c) {
                        case 'wgrey':
                            $s = 2;
                            break;
                        case 'orange':
                            $s = 3;
                            break;
                        case 'green':
                            $s = 0;
                            break;
                        case 'yellow':
                            $s = 1;
                            break;
                        default:
                            $s = 0;
                            break;
                    }
                }
                if ($sort_value == 3)
                {
                    switch ($c) {
                        case 'wgrey':
                            $s = 1;
                            break;
                        case 'orange':
                            $s = 2;
                            break;
                        case 'green':
                            $s = 3;
                            break;
                        case 'yellow':
                            $s = 0;
                            break;
                        default:
                            $s = 0;
                            break;
                    }
                }

            }

            $datas[] = array(
                "column_19" => $x->course->name,
                "column_0" => $x->member->last_name,
                "column_1" => $x->member->first_name,
                "column_2" => $x->subscribed . "§" . formatPrice($subPrice),
                "column_3" => $this->getColor($x->months, 9, isset($prices[9]) && $prices[9] >= $price) . "§" . formatPrice(isset($prices[9]) ? $prices[9] : $price) . "§" . (isset($prices[9]) && $prices[9] <= $price ? 'X' : ''),
                "column_4" => $this->getColor($x->months, 10, isset($prices[10]) && $prices[10] >= $price) . "§" . formatPrice(isset($prices[10]) ? $prices[10] : $price) . "§" . (isset($prices[10]) && $prices[10] <= $price ? 'X' : ''),
                "column_5" => $this->getColor($x->months, 11, isset($prices[11]) && $prices[11] >= $price) . "§" . formatPrice(isset($prices[11]) ? $prices[11] : $price) . "§" . (isset($prices[11]) && $prices[11] <= $price ? 'X' : ''),
                "column_6" => $this->getColor($x->months, 12, isset($prices[12]) && $prices[12] >= $price) . "§" . formatPrice(isset($prices[12]) ? $prices[12] : $price) . "§" . (isset($prices[12]) && $prices[12] <= $price ? 'X' : ''),
                "column_7" => $this->getColor($x->months, 1, isset($prices[1]) && $prices[1] >= $price) . "§" . formatPrice(isset($prices[1]) ? $prices[1] : $price) . "§" . (isset($prices[1]) && $prices[1] <= $price ? 'X' : ''),
                "column_8" => $this->getColor($x->months, 2, isset($prices[2]) && $prices[2] >= $price) . "§" . formatPrice(isset($prices[2]) ? $prices[2] : $price) . "§" . (isset($prices[2]) && $prices[2] <= $price ? 'X' : ''),
                "column_9" => $this->getColor($x->months, 3, isset($prices[3]) && $prices[3] >= $price) . "§" . formatPrice(isset($prices[3]) ? $prices[3] : $price) . "§" . (isset($prices[3]) && $prices[3] <= $price ? 'X' : ''),
                "column_10" => $this->getColor($x->months, 4, isset($prices[4]) && $prices[4] >= $price) . "§" . formatPrice(isset($prices[4]) ? $prices[4] : $price) . "§" . (isset($prices[4]) && $prices[4] <= $price ? 'X' : ''),
                "column_11" => $this->getColor($x->months, 5, isset($prices[5]) && $prices[5] >= $price) . "§" . formatPrice(isset($prices[5]) ? $prices[5] : $price) . "§" . (isset($prices[5]) && $prices[5] <= $price ? 'X' : ''),
                "column_12" => $this->getColor($x->months, 6, isset($prices[6]) && $prices[6] >= $price) . "§" . formatPrice(isset($prices[6]) ? $prices[6] : $price) . "§" . (isset($prices[6]) && $prices[6] <= $price ? 'X' : ''),
                "column_13" => $this->getColor($x->months, 7, isset($prices[7]) && $prices[7] >= $price) . "§" . formatPrice(isset($prices[7]) ? $prices[7] : $price) . "§" . (isset($prices[7]) && $prices[7] <= $price ? 'X' : ''),
                "column_14" => $this->getColor($x->months, 8, isset($prices[8]) && $prices[8] >= $price) . "§" . formatPrice(isset($prices[8]) ? $prices[8] : $price) . "§" . (isset($prices[8]) && $prices[8] <= $price ? 'X' : ''),
                "column_15" => $x->course_id,
                "column_16" => $x->id,
                "column_17" => $x->member_id,
                "column_18" => $xxx++,
                "column_20" => $s
            );

        }

        $count = $member_course->count();

        $this->totSExcel = [];
        $this->totS = [];

        //$js = '';
        $xx = 4;
        $str = '';
        if ($count > 0)
        {
            $str .= "<a style='width:100%;float:right; text-align:right; display:block;' class=green><small>" . (isset($totalIsc["Y"]) ? formatPrice($totalIsc["Y"]) : 0) . "</small></a><br>";
            $str .= "<a style='width:100%;float:right; text-align:right; display:block;' class=orange><small>" . (isset($totalIsc["N"]) ? formatPrice($totalIsc["N"]) : 0) . "</small></a><br>";
            $str .= "<a style='width:100%;float:right; text-align:right; display:block;' class=yellow><small>0</small></a><br>";

            $this->totSExcel[] = array('green' => (isset($totalIsc["Y"]) ? formatPrice($totalIsc["Y"]) : 0), 'orange' => (isset($totalIsc["N"]) ? formatPrice($totalIsc["N"]) : 0), 'yellow' => 0);

        }
        $this->totS[] = $str;

        $str = "";
        foreach($totals as $z => $t)
        {
            if ($z == 1) $xx = 5;
            if ($z == 2) $xx = 6;
            if ($z == 3) $xx = 7;
            if ($z == 4) $xx = 8;
            if ($z == 5) $xx = 9;
            if ($z == 6) $xx = 10;
            if ($z == 7) $xx = 11;
            if ($z == 8) $xx = 12;
            if ($z == 9) $xx = 1;
            if ($z == 10) $xx = 2;
            if ($z == 11) $xx = 3;
            if ($z == 12) $xx = 4;
            $str = '';
            $aaa = [];
            foreach($t as $x => $c)
            {
                $y = $x == 'yellow' ? $c : formatPrice($c);
                $str .= "<a style='width:100%;float:right; text-align:right; display:block;' class=" . $x . "><small>" . $y . "</small></a><br>";
                $aaa[$x] = $y;
            }

            $this->totSExcel[$xx] = $aaa;
            $this->totS[$xx] = $str;
            //$js .= $xx . "§" . $str . "_";

            $xx += 1;
        }

        for($e=sizeof($this->totS);$e<=12;$e++)
        {
            $this->totS[] = '';
        }

        if ($this->sort != '')
        {
            $s = $this->sort;
            if ($s == 1) $s = 21;
            if ($column != '')
                array_multisort(array_column($datas, 'column_20'), SORT_ASC, SORT_NATURAL|SORT_FLAG_CASE, $datas);
            else
                array_multisort(array_column($datas, 'column_' . ($s - 2)), $this->dir == "ASC" ? SORT_ASC : SORT_DESC, SORT_NATURAL|SORT_FLAG_CASE, $datas);
        }

        $xxx = 1;
        foreach($datas as $yyy => $d)
        {
            $datas[$yyy]["column_18"] = $xxx++;
        }

        $this->totalRecords = sizeof($datas);

        //$start = 0;

        $this->recordsNoPaginate = [];
        $this->recordsNoPaginate = $datas;
        //if (isset($_GET["start"]))
        $datas = array_slice($datas, $this->start, $this->pageLength);

        $this->pages = ceil($this->totalRecords / $this->pageLength);
        $this->records = [];
        $this->records = $datas;

        //$this->totS = $js;

        return view('livewire.course_list');

    }

    public function setPage($page)
    {
        $this->currentPage = $page;
        $this->start = $this->pageLength * ($page - 1);
        $this->emit('load-data-table');
    }

    public function setSort($sort)
    {
        $this->sort = $sort;
        if ($this->dir == '')
            $this->dir = 'ASC';
        else
            $this->dir = $this->dir == 'ASC' ? 'DESC' : 'ASC';
        $this->emit('load-data-table');
    }

    public function search()
    {
        $this->currentPage = 1;
        $this->start = 0;
        $this->hasFilter = true;
        $this->emit('load-data-table');
    }

    public function getColor($months, $m, $all)
    {
        $class = "wgrey";
        foreach(json_decode($months) as $mm)
        {
            if ($mm->m == $m)
            {
                if ($mm->status == "")
                {
                    $class = "orange";
                }
                if ($mm->status == "1")
                {
                    $class = "green";
                }
                if ($mm->status == "2")
                {
                    $class = "yellow";
                }
                if (!$all && $class == "green")
                {
                    $class = "orange";
                }
            }
        }
        return $class;
    }

    public function newPayment($course_id, $months, $member_id, $id, $subscription)
    {

        $newMonths = array();
        if ($months != '')
        {
            $mm = explode(",", $months);
            foreach($mm as $month)
            {
                if ($month < 5) $month += 12;
                if ($month >= 5) $month -= 4;
                $newMonths[] = $month;
            }
        }

        $c = \App\Models\Course::findOrFail($course_id);
        $m = \App\Models\MemberCourse::findOrFail($id);

        $price = $m->price;
        $subscription_price = $m->subscription_price;

        $records = \App\Models\Record::where('member_course_id', $m->id)->where('deleted', 0)->get();
        foreach ($records as $record)
        {

            if (in_array($month, json_decode($record->months)))
            {

                foreach ($record->rows as $row)
                {

                    
                    if ($row->causal_id == $c->causal_id && !str_contains(strtolower($row->note), 'iscrizione'))
                    {
                        $tot = sizeof(json_decode($row->when));
                        foreach(json_decode($row->when) as $m)
                        {
                            $price -= $row->amount / $tot;
                        }
                    }
                }
            }
        }

        return redirect()->to('/in?new=1&memberId=' . $member_id . (sizeof($newMonths) > 0 ? '&causalId=' . $c->causal_id : '') . '&subCausalId=' . $c->sub_causal_id . '&createSubscription=' . ($subscription ? 1 : 0) . (sizeof($newMonths) > 0 ? '&months=' . implode("|", $newMonths) : '') . (sizeof($newMonths) > 0 ? ('&price=' . $price) : '') . '&subscription_price=' . $subscription_price . "&courseId=" . $id);

    }

    public function suspendPayment($course_id, $month, $member_id, $id, $subscription)
    {

        $monthMap = [
            1 => 9,  // September
            2 => 10, // October
            3 => 11, // November
            4 => 12, // December
            5 => 1,  // January
            6 => 2,  // February
            7 => 3,  // March
            8 => 4,  // April
            9 => 5,  // May
            10 => 6, // June
            11 => 7, // July
            12 => 8  // August
        ];

        $dbMonth = isset($monthMap[$month]) ? $monthMap[$month] : $month;


        $memberCourse = MemberCourse::where('id', $id)
            ->where('member_id', $member_id)
            ->where('course_id', $course_id)
            ->first();


        if (!$memberCourse) {
            return response()->json(['error' => 'Non Trovato'], 404);
        }

        $monthsData = json_decode($memberCourse->months, true);
        if (!is_array($monthsData)) {
            return response()->json(['error' => 'Invalid months data format'], 400);
        }

        $monthUpdated = false;
        foreach ($monthsData as &$monthData) {
            if ($monthData['m'] == $dbMonth) {
                $monthData['status'] = 2;
                $monthUpdated = true;
            }
        }

        if (!$monthUpdated) {
            return response()->json(['error' => 'Month not found in data'], 404);
        }

        $memberCourse->months = json_encode($monthsData);
        $memberCourse->save();
        session()->flash('success', 'Payment suspended successfully');
        return redirect()->to('/course_list');
    }

    public function resumePayment($course_id, $month, $member_id, $id, $subscription)
    {
        Log::info('resumePayment');
        Log::info($course_id);
        Log::info($month);
        Log::info($member_id);
        Log::info($id);

        $monthMap = [
            1 => 9,  // September
            2 => 10, // October
            3 => 11, // November
            4 => 12, // December
            5 => 1,  // January
            6 => 2,  // February
            7 => 3,  // March
            8 => 4,  // April
            9 => 5,  // May
            10 => 6, // June
            11 => 7, // July
            12 => 8  // August
        ];

        $dbMonth = isset($monthMap[$month]) ? $monthMap[$month] : $month;


        $memberCourse = MemberCourse::where('id', $id)
            ->where('member_id', $member_id)
            ->where('course_id', $course_id)
            ->first();

        if (!$memberCourse) {
            return response()->json(['error' => 'Non Trovato'], 404);
        }

        $monthsData = json_decode($memberCourse->months, true);
        Log::info('data mese',$monthsData);
        if (!is_array($monthsData)) {
            return response()->json(['error' => 'Invalid months data format'], 400);
        }

        $monthUpdated = false;
        foreach ($monthsData as &$monthData) {
            if ($monthData['m'] == $dbMonth) {
                $monthData['status'] = "";
                $monthUpdated = true;
            }
        }
        Log::info($monthUpdated);

        if (!$monthUpdated) {
            return response()->json(['error' => 'Month not found in data'], 404);
        }

        $memberCourse->months = json_encode($monthsData);
        $memberCourse->save();
        session()->flash('success', 'Payment resumed successfully');
        return redirect()->to('/course_list');
    }
    /*
    public function newPayment()
    {
        $c = \App\Models\Course::findOrFail($this->selectedCourseId);
        return redirect()->to('/in?new=1&memberId=' . $this->selectedMemberId . '&causalId=' . $c->causal_id . '&subCausalId=' . $c->sub_causal_id . '&createSubscription=0' . (sizeof($this->payMonths) > 0 ? '&months=' . implode("|", $this->payMonths) : "") . '&price=' . $c->price . '&subscription_price=' . $c->subscription_price . "&courseId=" . $this->selectedCourseId);
    }
    */
    public function newSubscription($course_id, $member_id, $id)
    {
        $c = \App\Models\Course::findOrFail($course_id);
        return redirect()->to('/in?new=1&memberId=' . $member_id . '&causalId=' . $c->causal_id . '&subCausalId=' . $c->sub_causal_id . '&createSubscription=1&price=0.00&subscription_price=' . $c->subscription_price . "&courseId=" . $id);

    }

    public function disableSearch()
    {
        $this->filterCourse = [];
        $this->filterLevel = [];
        $this->filterType = [];
        $this->filterDuration = [];
        $this->filterFrequency = [];
        $this->hasFilter = false;

    }

    public function export()
    {

        $letters = array('A', 'B', 'C', 'D', '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', "Corso");
        $activeWorksheet->setCellValue('B1', "Cognome");
        $activeWorksheet->setCellValue('C1', "Nome");
        $activeWorksheet->setCellValue('D1', "Iscrizione");
        $activeWorksheet->setCellValue('E1', "Settembre");
        $activeWorksheet->setCellValue('F1', "Ottobre");
        $activeWorksheet->setCellValue('G1', "Novembre");
        $activeWorksheet->setCellValue('H1', "Dicembre");
        $activeWorksheet->setCellValue('I1', "Gennaio");
        $activeWorksheet->setCellValue('J1', "Febbraio");
        $activeWorksheet->setCellValue('K1', "Marzo");
        $activeWorksheet->setCellValue('L1', "Aprile");
        $activeWorksheet->setCellValue('M1', "Maggio");
        $activeWorksheet->setCellValue('N1', "Giugno");
        $activeWorksheet->setCellValue('O1', "Luglio");
        $activeWorksheet->setCellValue('P1', "Agosto");

        $count = 2;
        foreach($this->recordsNoPaginate as $idx => $record)
        {

            $activeWorksheet->setCellValue('A' . $count, $record["column_19"]);
            $activeWorksheet->setCellValue('B' . $count, $record["column_0"]);
            $activeWorksheet->setCellValue('C' . $count, $record["column_1"]);
            list($color, $value) = explode("§", $record["column_2"]);
            $activeWorksheet->setCellValue('D' . $count, $value);
            $c = '#FFFFFF';
            if($color == 0)
                $c = 'ffa500';
            if($color == 1)
                $c = '00ff00';

            $activeWorksheet->getStyle('D' . $count . ':D' . $count)
                ->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()
                ->setARGB($c);

            for($ii=3; $ii<=14; $ii++)
            {
                list($color, $value) = explode("§", $record["column_" . $ii]);
                $c = 'FFFFFF';
                if($color == 'orange')
                    $c = 'ffa500';
                if($color == 'green')
                    $c = '00ff00';
                if($color == 'yellow')
                {
                    $c = '5088bf';
                    $activeWorksheet->getStyle($letters[$ii + 1] . $count . ':' . $letters[$ii + 1] . $count)
                    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);;
                }
                if($color == 'wgrey')
                    $value = '';

                $activeWorksheet->setCellValue($letters[$ii + 1] . $count, $value);

                $activeWorksheet->getStyle($letters[$ii + 1] . $count . ':' . $letters[$ii + 1] . $count)
                    ->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()
                    ->setARGB($c);
                /*$activeWorksheet->cells($letters[$ii + 1] . $count . ':' . $letters[$ii + 1] . $count, function ($cells) use ($c)
                {
                    $cells->setBackground($c);
                    //$cells->setAlignment('center');
                });*/
            }

            $activeWorksheet->getStyle("A1:P1")->getFont()->setBold( true );

            $count++;
        }

        // Totali
        $activeWorksheet->setCellValue('A' . (1 + $count), '');
        $activeWorksheet->setCellValue('B' . (1 + $count), '');
        $activeWorksheet->setCellValue('C' . (1 + $count), '');
        for($x=0; $x<=sizeof($this->totSExcel); $x++)
        {

            if (isset($this->totSExcel[$x]))
            {

                $activeWorksheet->setCellValue($letters[$x + 3] . (1 + $count), isset($this->totSExcel[$x]['green']) ? $this->totSExcel[$x]['green'] : 0);
                $activeWorksheet->getStyle($letters[$x + 3] . (1 + $count) . ':' . $letters[$x + 3] . (1 + $count))
                        ->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()
                        ->setARGB('00ff00');

                $activeWorksheet->setCellValue($letters[$x + 3] . (2 + $count), isset($this->totSExcel[$x]['orange']) ? $this->totSExcel[$x]['orange'] : 0);
                $activeWorksheet->getStyle($letters[$x + 3] . (2 + $count) . ':' . $letters[$x + 3] . (2 + $count))
                        ->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()
                        ->setARGB('ffa500');

                $activeWorksheet->setCellValue($letters[$x + 3] . (3 + $count), isset($this->totSExcel[$x]['yellow']) ? $this->totSExcel[$x]['yellow'] : 0);
                $activeWorksheet->getStyle($letters[$x + 3] . (3 + $count) . ':' . $letters[$x + 3] . (3 + $count))
                        ->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()
                        ->setARGB('5088bf');
                $activeWorksheet->getStyle($letters[$x + 3] . (3 + $count) . ':' . $letters[$x + 3] . (3 + $count))
                        ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);;
            }

        }

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

        $this->emit('load-data-table');

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

    }
}