iao_team/routes/web.php

<?php

use Illuminate\Support\Facades\Route;
use Barryvdh\DomPDF\Facade\Pdf;

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    return view('login');
    // return Redirect::to('/dashboard');
});
//Route::get('/', \App\Http\Livewire\Login::class);
Route::get('/dashboard', \App\Http\Livewire\Dashboard::class);
Route::get('/settings', \App\Http\Livewire\Setting::class);
Route::get('/courses', \App\Http\Livewire\Course::class);
Route::get('/categories', \App\Http\Livewire\Category::class);
Route::get('/nations_list', \App\Http\Livewire\Nation::class);
Route::get('/provinces', \App\Http\Livewire\Province::class);
Route::get('/cities', \App\Http\Livewire\City::class);
Route::get('/banks', \App\Http\Livewire\Bank::class);
Route::get('/vats', \App\Http\Livewire\Vat::class);
Route::get('/disciplines', \App\Http\Livewire\Discipline::class);
Route::get('/course_types', \App\Http\Livewire\CourseType::class);
Route::get('/course_subscriptions', \App\Http\Livewire\CourseSubscription::class);
Route::get('/course_durations', \App\Http\Livewire\CourseDuration::class);
Route::get('/course_levels', \App\Http\Livewire\CourseLevel::class);
Route::get('/course_frequencies', \App\Http\Livewire\CourseFrequency::class);
Route::get('/course_list', \App\Http\Livewire\CourseList::class);
Route::get('/course_member', \App\Http\Livewire\CourseMember::class);
Route::get('/receipts', \App\Http\Livewire\Receipt::class);
Route::get('/cards', \App\Http\Livewire\Card::class);
Route::get('/causals', \App\Http\Livewire\Causal::class);
Route::get('/payment_methods', \App\Http\Livewire\PaymentMethod::class);
Route::get('/members', \App\Http\Livewire\Member::class);
Route::get('/suppliers', \App\Http\Livewire\Supplier::class);
Route::get('/sponsors', \App\Http\Livewire\Sponsor::class);
Route::get('/records', \App\Http\Livewire\Record::class);
Route::get('/reminders', \App\Http\Livewire\Reminder::class);
Route::get('/in', \App\Http\Livewire\RecordIN::class);
Route::get('/out', \App\Http\Livewire\RecordOUT::class);
Route::get('/records_in_out', \App\Http\Livewire\RecordINOUT::class);

Route::get('/receipt/{id}', function($id){
    $receipt = \App\Models\Receipt::findOrFail($id);
    $pdf = PDF::loadView('receipt', array('receipt' => $receipt));
    $pdfName = "Ricevuta_" . $receipt->member->last_name . "_" . $receipt->number . "_" . $receipt->year . ".pdf";
    return $pdf->stream($pdfName);
    /*return response()->streamDownload(
        fn () => print($pdf),
        "ricevuta_" . $receipt->number . "_" . $receipt->year . ".pdf"
    );*/
});

Route::get('/receipt/mail/{id}', function($id){
    $receipt = \App\Models\Receipt::findOrFail($id);
    if ($receipt->status == 99)
        sendReceiptDeleteEmail($receipt);
    else
        sendReceiptEmail($receipt);
    /*
    $pdf = PDF::loadView('receipt', array('receipt' => $receipt));
    $pdfName = "ricevuta_" . $receipt->number . "_" . $receipt->year . ".pdf";
    Storage::put('public/pdf/' . $pdfName, $pdf->output());
    $email = \App\Models\Member::findOrFail($receipt->member_id)->email;
    if ($email != '')
    {
        Mail::to($email)->send(new \App\Mail\ReceipEmail([
            'name' => 'Luca',
            'pdf' => 'public/pdf/' . $pdfName,
            'number' => $receipt->number . "/" . $receipt->year
        ]));
    }
        */

    return true;
    //return $pdf->stream();
    /*return response()->streamDownload(
        fn () => print($pdf),
        "ricevuta_" . $receipt->number . "_" . $receipt->year . ".pdf"
    );*/
});



Route::get('/nations', function(){
    if (isset($_GET["q"]))
        $datas = \App\Models\Nation::where('name', 'like', $_GET["q"] . '%')->orderBy('name')->get();
    else
        $datas = \App\Models\Nation::orderBy('name')->get();
    $data = array();
    foreach($datas as $d)
    {
        $data[] = array("id" => $d->id, "text" => $d->name);
    }
    return array("results" => $data);
});

Route::get('/provinces/{nation_id}', function($nation_id){
    if (isset($_GET["q"]))
        $datas = \App\Models\Province::where('nation_id', $nation_id)->where('name', 'like', $_GET["q"] . '%')->orderBy('name')->get();
    else
        $datas = \App\Models\Province::where('nation_id', $nation_id)->orderBy('name')->get();
    $data = array();
    foreach($datas as $d)
    {
        $data[] = array("id" => $d->id, "text" => $d->name);
    }
    return array("results" => $data);
});

Route::get('/cities/{province_id}', function($province_id){
    if (isset($_GET["q"]))
        $datas = \App\Models\City::where('province_id', $province_id)->where('name', 'like', $_GET["q"] . '%')->orderBy('name')->get();
    else
        $datas = \App\Models\City::where('province_id', $province_id)->orderBy('name')->get();
    $data = array();
    foreach($datas as $d)
    {
        $data[] = array("id" => $d->id, "text" => $d->name);
    }
    return array("results" => $data);
});

Route::get('/get_members', function(){

    $datas = [];

    // $datas = \App\Models\Member::select('members.*')->where('id', '>', 0);
    $x = \App\Models\Member::select('id', 'first_name', 'last_name', 'phone', 'birth_date', 'to_complete')->where('id', '>', 0);

    if (isset($_GET["search"]["value"]))
    {
        $v = str_replace("'", "''", stripcslashes($_GET["search"]["value"]));
        $x = $x->where(function ($query) use ($v) {
            $query->whereRaw("CONCAT(first_name, ' ', last_name) like '%" . $v . "%'")
                  ->orWhereRaw("CONCAT(last_name, ' ', first_name) like '%" . $v . "%'");
        });
        //where('first_name', 'like', '%' . $_GET["search"]["value"] . '%');
    }
    if ($_GET["cards"] != "")
    {
        $card_ids = \App\Models\MemberCard::whereIn('card_id', explode(",", $_GET["cards"]))->pluck('member_id');
        $x = $x->whereIn('id', $card_ids);
    }
    if ($_GET["categories"] != "")
    {
        $cats_ids = \App\Models\MemberCategory::whereIn('category_id', explode(",", $_GET["categories"]))->pluck('member_id');
        $x = $x->whereIn('id', $cats_ids);
    }
    if ($_GET["fromYear"] != "")
    {
        $x = $x->where('birth_date', '<', date("Y-m-d", strtotime("-" . $_GET["fromYear"] . " year", time())));
    }
    if ($_GET["toYear"] != "")
    {
        $x = $x->where('birth_date', '>', date("Y-m-d", strtotime("-" . $_GET["toYear"] . " year", time())));
    }
    if ($_GET["fromYearYear"] != "")
    {
        $x = $x->whereYear('birth_date', '>=', $_GET["fromYearYear"]);
    }
    if ($_GET["toYearYear"] != "")
    {
        $x = $x->whereYear('birth_date', '<=', $_GET["toYearYear"]);
    }

    $ids = [];

    if ($_GET["chkCertificateType"] != "")
    {
        $types = \App\Models\MemberCertificate::where('type', $_GET["chkCertificateType"])->pluck('member_id');
        $x = $x->whereIn('id', $types->toArray());;
        //$ids = array_merge($ids, $types->toArray());
    }
    /*
    if ($_GET["chkCertificateNormal"] != "")
    {
        $normal = \App\Models\MemberCertificate::where('type', 'N')->pluck('member_id');
        $ids = array_merge($ids, $normal->toArray());
        //$x = $x->whereIn('id', $normal);;
    }
    if ($_GET["chkCertificateAgonistico"] != "")
    {
        $agonistic = \App\Models\MemberCertificate::where('type', 'A')->pluck('member_id');
        $ids = array_merge($ids, $agonistic->toArray());
        //$x = $x->whereIn('id', $agonistic);
    }
    if ($_GET["chkCertificateScaduti"] != "")
    {
        $scaduto = \App\Models\MemberCertificate::where('expire_date', '<', date("Y-m-d"))->pluck('member_id');
        $ids = array_merge($ids, $scaduto->toArray());
        //$x = $x->whereIn('id', $scaduto);
    }*/
    if ($_GET["chkCertificateScadenza"] != "")
    {
        if ($_GET["chkCertificateScadenza"] == "1")
            $scad = \App\Models\MemberCertificate::where('expire_date', '<', date("Y-m-d"))->pluck('member_id');
        if ($_GET["chkCertificateScadenza"] == "2")
            $scad = \App\Models\MemberCertificate::whereBetween('expire_date', [date("Y-m-d"), date("Y-m-d", strtotime("+1 month"))])->pluck('member_id');
        //$ids = array_merge($ids, $scad->toArray());
        $x = $x->whereIn('id', $scad->toArray());;
        //$x = $x->whereIn('id', $scadenza);
    }

    //$filterStatus = isset($_GET["status"]) ? $_GET["status"] : -1;
    $chkStatus = [];
    $chkStatus0 = isset($_GET["chkStatus0"]) ? $_GET["chkStatus0"] : 0;
    if($chkStatus0 > 0)
        $chkStatus[] = 0;
    $chkStatus1 = isset($_GET["chkStatus1"]) ? $_GET["chkStatus1"] : 0;
    if($chkStatus1 > 0)
        $chkStatus[] = 1;
    $chkStatus2 = isset($_GET["chkStatus2"]) ? $_GET["chkStatus2"] : 0;
    if($chkStatus2 > 0)
        $chkStatus[] = 2;

    if (sizeof($chkStatus) > 0)
    {
        $members = \App\Models\Member::all();
        foreach($members as $m)
        {

            $state = $m->isActive();

            if (in_array($state["status"], $chkStatus))
                $ids[] = $m->id;

        }
    }

    if (sizeof($ids) > 0)
    {
        $x = $x->whereIn('id', $ids);
    }
    else
    {
        if (sizeof($chkStatus) > 0)
            $x = $x->whereIn('id', [-1]);
    }

    $count = $x->count();

    $x = $x->orderBy('to_complete', 'DESC');

    if (isset($_GET["order"]))
    {
        $column = '';
        if ($_GET["order"][0]["column"] == 0)
            $column = 'last_name';
        if ($_GET["order"][0]["column"] == 1)
            $column = 'first_name';
        if ($_GET["order"][0]["column"] == 2)
            $column = 'phone';
        if ($_GET["order"][0]["column"] == 3)
            $column = 'birth_date';
        if ($_GET["order"][0]["column"] == 4)
            $column = 'birth_date';
        if ($column != '')
            $x = $x->orderBy($column, $_GET["order"][0]["dir"]);
        else
            $x = $x->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');
    }
    else
        $x = $x->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');

    if (isset($_GET["start"]))
        $x = $x->offset($_GET["start"])->limit($_GET["length"])->get();
    else
        $x = $x->get();

    foreach($x as $idx => $r)
    {

        $status = $r->getStatus();
        $status = $status["status"];

        $class = $status > 0 ? ($status == 2 ? 'active' : 'due') : 'suspended';
        $text = $status > 0 ? ($status == 2 ? 'Tesserato' : 'Sospeso') : 'Non tesserato';

        if ($r->to_complete)
        {
            $text = 'Da completare';
            $class = "complete";
        }

        $has_certificate = $r->hasCertificate();
        $y = '';
        if($has_certificate["date"] != '')
        {
            if($has_certificate["date"] < date("Y-m-d"))
                $y .= '0';

            if($has_certificate["date"] >= date("Y-m-d") && $has_certificate["date"] < date("Y-m-d", strtotime("+1 month")))
                $y .= '1';

            if($has_certificate["date"] >= date("Y-m-d", strtotime("+1 month")))
                $y .= '2';
            $y .= '|';
            $y .= $has_certificate["date"] != '' ? date("d/m/Y", strtotime($has_certificate["date"])) : '';
        }

        $datas[] = array(
            //'c' => $idx + 1,
            //'id' => "ID" . str_pad($r->id, 5, "0", STR_PAD_LEFT),
            'last_name' => $r->last_name . "|" . $r->id,
            'first_name' => $r->first_name . "|" . $r->id,
            'phone' => $r->phone,
            'age' => $r->getAge(),
            'year' => date("Y", strtotime($r->birth_date)),
            'status' => $class . "|" . $text,
            // 'state' => $x,
            'certificate' => $y,
            'action' => $r->id
        );
    }

    return json_encode(array("data" => $datas,  "recordsTotal" => $count, "recordsFiltered" => $count));

});

Route::get('/get_record_in', function(){

    $datas = [];

    $x = \App\Models\Record::select('records.*', \DB::raw('members.first_name as first_name'), \DB::raw('members.last_name as last_name'), \DB::raw('payment_methods.name as payment')) // , \DB::raw('SUM(records.id) As total'))
            ->leftJoin('members', 'records.member_id', '=', 'members.id')
            ->leftJoin('payment_methods', 'records.payment_method_id', '=', 'payment_methods.id')
            ->where('records.type', 'IN');

    if (isset($_GET["search"]["value"]))
    {
        $v = str_replace("'", "''", stripcslashes($_GET["search"]["value"]));
        $x = $x->where(function ($query) use ($v) {
            $query->where('first_name', 'like', '%' . $v . '%')
                    ->orWhere('last_name', 'like', '%' . $v . '%');
        });
        //where('first_name', 'like', '%' . $_GET["search"]["value"] . '%');
    }

    //$x = $x->where(function ($query) use ($v) {

        // $datas = \App\Models\Record::where('type', 'IN')->with('member', 'payment_method');
        if ($_GET["filterCommercial"] > 0)
        {
            $x = $x->where('commercial', $_GET["filterCommercial"] == 1 ? true : false);
        }
        if ($_GET["filterMember"] > 0)
        {
            $x = $x->where('member_id', $_GET["filterMember"]);
        }
        if ($_GET["filterPaymentMethod"] != "null")
        {
            $payments = explode(",", $_GET["filterPaymentMethod"]);
            $x = $x->whereIn('payment_method_id', $payments);
        }
        if ($_GET["filterCausals"] != "null")
        {
            $causals = explode(",", $_GET["filterCausals"]);
            //$causals = \App\Models\RecordRow::where('causal_id', $_GET["filterCausals"])->pluck('record_id');
            $causals = \App\Models\RecordRow::whereIn('causal_id', $causals)->pluck('record_id');
            $x = $x->whereIn('records.id', $causals);
        }
        if ($_GET["filterFrom"] != '')
        {
            $x = $x->where('date', '>=', $_GET["filterFrom"]);
        }
        if ($_GET["filterTo"] != '')
        {
            $x = $x->where('date', '<=', $_GET["filterTo"]);
        }

    //});

    $start = 0;
    $limit = 100000;

    if (isset($_GET["start"]))
    {
        $start = $_GET["start"];
        $limit = $_GET["length"];
    }

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

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

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

    // Pagamento money
    $moneys = \App\Models\PaymentMethod::where('money', true)->pluck('id')->toArray();

    // Causale money
    $moneysCausal = \App\Models\Causal::where('money', true)->pluck('id')->toArray();

    $total = 0;
    /*
    foreach($x->get() as $r)
    {

        if (!in_array($r->payment_method_id, $moneys))
        {
            foreach($r->rows as $rr)
            {
                if ((!in_array($rr->member_id, $exclude_from_records) || in_array($r->causal_id, $moneysCausal)) && (!$r->deleted || $r->deleted == null) && (!in_array($rr->causal_id, $excludeCausals) || in_array($r->causal_id, $moneysCausal)) && (!$r->financial_movement || $r->financial_movement == null) && (!$r->corrispettivo_fiscale || $r->corrispettivo_fiscale == null))
                {
                    $total += $rr->amount;
                    if ($rr->vat_id > 0)
                        $total += getVatValue($rr->amount, $rr->vat_id);
                }
            }
        }
    }
    */
    $count = $x->count();



    if (isset($_GET["order"]))
    {
        $column = '';
        if ($_GET["order"][0]["column"] == 0)
            $column = 'date';
        if ($_GET["order"][0]["column"] == 1)
            $column = 'records.amount';
        if ($_GET["order"][0]["column"] == 2)
            $column = 'last_name';
        if ($_GET["order"][0]["column"] == 3)
            $column = 'first_name';
        if ($_GET["order"][0]["column"] == 4)
            $column = 'commercial';
        if ($column != '')
            $x = $x->orderBy($column, $_GET["order"][0]["dir"])->orderBy('records.id', 'DESC');
        else
            $x = $x->orderBy('records.id', 'DESC');
    }
    else
        $x = $x->orderBy('records.date', 'DESC')->orderBy('records.id', 'DESC');

    $x = $x->offset($start)->limit($limit)->get();

    foreach($x as $idx => $r)
    {

        $causals = '';
        foreach($r->rows as $row)
        {
            $causals .= $row->causal->getTree() . "<br>";
        }

        $datas[] = array(
            //'id' => $r->id,
            'date' => $r->date,
            'total' => formatPrice($r->getTotal()),
            'first_name' => $r->first_name,
            'last_name' => $r->last_name,
            'commercial' => $r->financial_movement ? 'Movimento finanziario' : ($r->commercial ? 'SI' : 'NO'),
            'causals' => $causals,
            'payment' => $r->payment_method->name,
            'status' => $r->deleted ? 'Annullato' : '',
            'action' => $r->id . "|" . formatPrice($total) . "|" . ($r->deleted ? 'x' : '')
        );


    }

    /*$datas[] = array(
        //'id' => $r->id,
        'date' => '',
        'total' => formatPrice($total),
        'first_name' => '',
        'last_name' => '',
        'commercial' => '',
        'causals' => '',
        'payment' => '',
        'status' => '',
        'action' => ''
    );*/

    return json_encode(array("data" => $datas,  "recordsTotal" => $count, "recordsFiltered" => $count));

});

Route::get('/get_record_out', function(){

    $datas = [];

    $x = \App\Models\Record::where('type', 'OUT')->with('supplier', 'payment_method');

    if ($_GET["filterSupplier"] > 0)
    {
        $x = $x->where('supplier_id', $_GET["filterSupplier"]);
    }
    /*if ($_GET["filterPaymentMethod"] > 0)
    {
        $x = $x->where('payment_method_id', $_GET["filterPaymentMethod"]);
    }
    if ($_GET["filterCausals"] > 0)
    {
        $causals = \App\Models\RecordRow::where('causal_id', $_GET["filterCausals"])->pluck('record_id');
        $x = $x->whereIn('records.id', $causals);
    }*/
    if ($_GET["filterPaymentMethod"] != "null")
    {
        $payments = explode(",", $_GET["filterPaymentMethod"]);
        $x = $x->whereIn('payment_method_id', $payments);
    }
    if ($_GET["filterCausals"] != "null")
    {
        $causals = explode(",", $_GET["filterCausals"]);
        //$causals = \App\Models\RecordRow::where('causal_id', $_GET["filterCausals"])->pluck('record_id');
        $causals = \App\Models\RecordRow::whereIn('causal_id', $causals)->pluck('record_id');
        $x = $x->whereIn('records.id', $causals);
    }
    if ($_GET["filterFrom"] != '')
    {
        $x = $x->where('date', '>=', $_GET["filterFrom"]);
    }
    if ($_GET["filterTo"] != '')
    {
        $x = $x->where('date', '<=', $_GET["filterTo"]);
    }

    $total = 0;
    foreach($x->get() as $r)
    {
        foreach($r->rows as $rr)
        {
            $total += $rr->amount;
            if ($rr->vat_id > 0)
                $total += getVatValue($rr->amount, $rr->vat_id);
        }
    }

    $x = $x->get();

    foreach($x as $idx => $r)
    {

        $causals = '';
        foreach($r->rows as $row)
        {
            $causals .= $row->causal->getTree() . "<br>";
        }

        $datas[] = array(
            //'id' => $r->id,
            'date' => $r->date,
            'total' => formatPrice($r->getTotal()),
            'supplier' => $r->supplier->name,
            'causals' => $causals,
            'payment' => $r->payment_method->name,
            'action' => $r->id . "|" . formatPrice($total)
        );

    }
    /*
    $datas[] = array(
        //'id' => $r->id,
        'date' => '',
        'total' => formatPrice($total),
        'supplier' => '',
        'causals' => '',
        'payment' => '',
        'action' => ''
    );
    */
    return json_encode(array("data" => $datas));

});

Route::get('/get_course_list', function(){

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

    if (isset($_GET["search"]["value"]))
    {
        $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 ($_GET["filterCourse"] != "null")
    {
        $course_ids = [];
        $courses = explode(",", $_GET["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 ($_GET["filterLevel"] != "null")
    {
        $levels = explode(",", $_GET["filterLevel"]);
        $course_ids = \App\Models\Course::whereIn('course_level_id', $levels)->pluck('id');
        $member_course = $member_course->whereIn('course_id', $course_ids);
    }
    if ($_GET["filterFrequency"] != "null")
    {
        $frequencies = explode(",", $_GET["filterFrequency"]);
        $course_ids = \App\Models\Course::whereIn('course_type_id', $frequencies)->pluck('id');
        $member_course = $member_course->whereIn('course_id', $course_ids);
    }
    if ($_GET["filterType"] != "null")
    {
        $types = explode(",", $_GET["filterType"]);
        $course_ids = \App\Models\Course::whereIn('course_frequency_id', $types)->pluck('id');
        $member_course = $member_course->whereIn('course_id', $course_ids);
    }
    if ($_GET["filterDuration"] != "null")
    {
        $durations = explode(",", $_GET["filterDuration"]);
        $course_ids = \App\Models\Course::whereIn('course_duration_id', $durations)->pluck('id');
        $member_course = $member_course->whereIn('course_id', $course_ids);
    }

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

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

        $price = 0;
        $price = $x->course->price;
        $subPrice = $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)
                    {
                        $prices[$m->month] = $row->amount / $tot;
                    }
                }
            }
        }

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

            $cls = getColor($x->months, $i);
            if ($cls != 'grey')
            {

                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;
                    $totals[$i][$cls] += $p;
                }
            }
        }

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

    }

    $js = '';
    $xx = 2;
    $str = '';
    $str .= "<a class=green><small>" . (isset($totalIsc["Y"]) ? formatPrice($totalIsc["Y"]) : 0) . "</small></a><br>";
    $str .= "<a class=orange><small>" . (isset($totalIsc["N"]) ? formatPrice($totalIsc["N"]) : 0) . "</small></a><br>";
    $str .= "<a class=yellow><small>0</small></a><br>";
    $js .= "2§" . $str . "_";
    $str = "";
    foreach($totals as $z => $t)
    {
        if ($z == 1) $xx = 7;
        if ($z == 2) $xx = 8;
        if ($z == 3) $xx = 9;
        if ($z == 4) $xx = 10;
        if ($z == 5) $xx = 11;
        if ($z == 6) $xx = 12;
        if ($z == 7) $xx = 13;
        if ($z == 8) $xx = 14;
        if ($z == 9) $xx = 3;
        if ($z == 10) $xx = 4;
        if ($z == 11) $xx = 5;
        if ($z == 12) $xx = 6;
        $str = '';
        foreach($t as $x => $c)
        {
            $y = $x == 'yellow' ? $c : formatPrice($c);
            $str .= "<a class=" . $x . "><small>" . $y . "</small></a><br>";
        }
        $js .= $xx . "§" . $str . "_";
        $xx += 1;
    }

    $count = $member_course->count();
    //$member_course = $member_course->where('course_id', 999999);

    $start = 0;
    $limit = 100000;

    if (isset($_GET["start"]))
    {
        $start = $_GET["start"];
        $limit = $_GET["length"];
    }

    $member_course = $member_course->offset($start)->limit($limit)->get();

    $datas = [];

    foreach($member_course as $x)
    {

        $price = 0;
        $price = $x->course->price;
        $subPrice = $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)
                    {
                        $prices[$m->month] = $row->amount / $tot;
                    }
                }
            }
        }

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

            $cls = getColor($x->months, $i);
            if ($cls != 'grey')
            {

                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;
                    $totals[$i][$cls] += $p;
                }
            }
        }

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

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

    // Sort data
    /*
    if (isset($_GET["order"]))
        array_multisort(array_column($datas, 'column_' . $_GET["order"][0]["column"]), $_GET["order"][0]["dir"] == "asc" ? SORT_ASC : SORT_DESC, SORT_NATURAL|SORT_FLAG_CASE, $datas);
    else
        array_multisort(array_column($datas, 'column_0'), SORT_DESC, SORT_NATURAL|SORT_FLAG_CASE, $datas);
        //$x = $x->orderBy('records.date', 'DESC')->orderBy('records.id', 'DESC');
    */


    return json_encode(array("data" => $datas, "recordsTotal" => $count, "recordsFiltered" => $count, "totals" => $js));

});

Route::get('/get_course_members', function(){

    //$datas = \App\Models\MemberCourse::with('member');

    $datas = \App\Models\MemberCourse::select('member_courses.*', 'members.first_name', 'members.last_name', 'members.email', 'members.phone', 'members.birth_date')->leftJoin('members', 'member_courses.member_id', '=', 'members.id');

    if ($_GET["filterCourse"] != "null")
    {
        $course_ids = [];
        $courses = explode(",", $_GET["filterCourse"]);
        foreach($courses as $c)
        {
            $all = \App\Models\Course::where('name', 'like', '%' . $c . "%")->get();
            foreach($all as $a)
            {
                $course_ids[] = $a->id;
            }
        }
        $datas = $datas->whereIn('course_id', $course_ids);
    }
    if ($_GET["filterLevel"] != "null")
    {
        $levels = explode(",", $_GET["filterLevel"]);
        $course_ids = \App\Models\Course::whereIn('course_level_id', $levels)->pluck('id');
        $datas = $datas->whereIn('course_id', $course_ids);
    }
    if ($_GET["filterFrequency"] != "null")
    {
        $frequencies = explode(",", $_GET["filterFrequency"]);
        $course_ids = \App\Models\Course::whereIn('course_type_id', $frequencies)->pluck('id');
        $datas = $datas->whereIn('course_id', $course_ids);
    }
    if ($_GET["filterType"] != "null")
    {
        $types = explode(",", $_GET["filterType"]);
        $course_ids = \App\Models\Course::whereIn('course_frequency_id', $types)->pluck('id');
        $datas = $datas->whereIn('course_id', $course_ids);
    }
    if ($_GET["filterDuration"] != "null")
    {
        $durations = explode(",", $_GET["filterDuration"]);
        $course_ids = \App\Models\Course::whereIn('course_duration_id', $durations)->pluck('id');
        $datas = $datas->whereIn('course_id', $course_ids);
    }
    if ($_GET["filterDays"] != "null")
    {
        $ids = [];
        $days = explode(",", $_GET["filterDays"]);
        foreach($days as $d)
        {
            $all = \App\Models\MemberCourse::where('when', 'like', "%" . $d . "%")->get();
            foreach($all as $a)
            {
                $ids[] = $a->id;
            }
        }
        $datas = $datas->whereIn('id', $ids);
    }
    if ($_GET["filterHours"] != "null")
    {
        $ids = [];
        $hours = explode(",", $_GET["filterHours"]);
        foreach($hours as $h)
        {
            $all = \App\Models\MemberCourse::where('when', 'like', '%"from":"' . $h . "%")->get();
            foreach($all as $a)
            {
                $ids[] = $a->id;
            }
        }
        $datas = $datas->whereIn('member_courses.id', $ids);
    }

    if ($_GET["filterSubscription"] != "")
    {
        $ids = \App\Models\MemberCourse::where('subscribed', $_GET["filterSubscription"] == 1 ? true : false)->pluck('id');
        $datas = $datas->whereIn('member_courses.id', $ids);
        //$this->filter .= $this->filter != '' ? ', ' : '';
        //$this->filter .= "Pagata sottoscrizione : " . ($this->filterSubscription == 1 ? "SI" : "NO") . " ";
    }

    if ($_GET["chkCertificateType"] != "null")
    {
        $types = \App\Models\MemberCertificate::where('type', $_GET["chkCertificateType"])->pluck('member_id');
        $datas = $datas->whereIn('member_id', $types);
    }
    if ($_GET["chkCertificateScadenza"] != "null")
    {
        if ($_GET["chkCertificateScadenza"] == "1")
            $scad = \App\Models\MemberCertificate::where('expire_date', '<', date("Y-m-d"))->pluck('member_id');
        if ($_GET["chkCertificateScadenza"] == "2")
            $scad = \App\Models\MemberCertificate::whereBetween('expire_date', [date("Y-m-d"), date("Y-m-d", strtotime("+1 month"))])->pluck('member_id');
        $datas = $datas->whereIn('member_id', $scad);
    }
    /*
    if ($_GET["fromYear"] != "null")
    {
        $m_ids = \App\Models\Member::where('birth_date', '<', date("Y-m-d", strtotime("-" . $_GET["fromYear"] . " year", time())))->pluck('id');
        $datas = $datas->whereIn('member_id', $m_ids);
    }
    if ($_GET["toYear"] != "null")
    {
        $m_ids = \App\Models\Member::where('birth_date', '>', date("Y-m-d", strtotime("-" . $_GET["toYear"] . " year", time())))->pluck('id');
        $datas = $datas->whereIn('member_id', $m_ids);
    }
    if ($_GET["fromFromYear"] != "null")
    {
        $m_ids = \App\Models\Member::whereYear('birth_date', '>=', $_GET["fromFromYear"])->pluck('id');
        $datas = $datas->whereIn('member_id', $m_ids);
    }
    if ($_GET["toToYear"] != "null")
    {
        $m_ids = \App\Models\Member::whereYear('birth_date', '<=', $_GET["toToYear"])->pluck('id');
        $datas = $datas->whereIn('member_id', $m_ids);
    }
    */
    if ($_GET["filterCards"] != "null")
    {
        $card_ids = \App\Models\MemberCard::whereIn('card_id', $_GET["filterCards"])->pluck('member_id');
        $datas = $datas->whereIn('member_id', $card_ids);
        //$this->filter .= $this->filter != '' ? ', ' : '';
        //$this->filter .= "Tessera : ";
        //foreach($this->chkCard as $card)
        //{
        //    $this->filter .= \App\Models\Card::findOrFail($card)->name . " ";
        //}

    }
    /*
    if ($_GET["filterYear"] != "null")
    {
        $course_ids = \App\Models\Course::where('year', $_GET["filterYear"])->pluck('id');
        $datas = $datas->whereIn('course_id', $course_ids);
        //$this->filter .= $this->filter != '' ? ', ' : '';
        //$this->filter .= "Anno : " . $this->filterYear . " ";
    }
    */
    $aRet = [];
    if ($_GET["filterStatus"] != "null")
    {
        $status = explode(",", $_GET["filterStatus"]);
        foreach($status as $s)
        {
            foreach($datas->get() as $aaa)
            {

                $state = \App\Models\Member::findOrFail($aaa->member_id)->isActive();
                if ($state["status"] == $s)
                    $aRet[] = $aaa;
            }
        }
    }
    else
        $aRet = $datas->get();

    $ret = [];
    foreach($aRet as $idx => $r)
    {
        $date1 = new DateTime($r->birth_date);
        $date2 = new DateTime("now");
        $interval = $date1->diff($date2);

        $ret[] = array(

            "column_0" => $idx,
            "column_1" => $r->last_name,
            "column_2" => $r->first_name,
            "column_3" => strval($interval->y),
            "column_4" => date("Y", strtotime($r->birth_date)),
            "column_5" => $r->phone,
            "column_6" => $r->email,
            "column_7" => $r->member_id
        );
    }

    return json_encode(array("data" => $ret, "recordsTotal" => sizeof($aRet), "recordsFiltered" => sizeof($aRet)));

});

function getColor($months, $m)
{
    $class = "grey";
    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";
            }
        }
    }
    return $class;
}

Route::get('/migrate', function(){
    \Artisan::call('migrate');
    dd('migrated!');
});