iao_team/routes/web.php

<?php

use Illuminate\Support\Facades\Route;
use Barryvdh\DomPDF\Facade\Pdf;
use Illuminate\Support\Facades\Redirect;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\Log;


/*
|--------------------------------------------------------------------------
| 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');
})->name('login');

Route::get('/login', function () {
    return Redirect::to('/');
    // return Redirect::to('/dashboard');
});

Route::post('/login', function () {

    if(Auth::attempt(array('email' => $_POST["email"], 'password' => $_POST["password"])))
    {
        return Redirect::to('/dashboard');
    }
    else
    {
        return Redirect::to('/?error=1');
    }

})->name('login');

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

    Auth::logout();
    return redirect('/');

});

Route::group(['middleware' => 'auth'],function(){

    //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('/users', \App\Http\Livewire\User::class);
    Route::get('/profile', \App\Http\Livewire\Profile::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', 'current_status', 'certificate', 'certificate_date')->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["filterCategories"] != "null")
        {

            $categories = [];
            $cats = explode(",", $_GET["filterCategories"]);

            // Per ogni causale, se ha dei figli allora aggiungo le causali figlio
            foreach($cats as $c)
            {
                $categories[] = $c;
                $childs = \App\Models\Category::where('parent_id', $c)->get();
                foreach($childs as $_cc)
                {
                    $categories[] = $_cc->id;
                    $childss = \App\Models\Category::where('parent_id', $_cc->id)->get();
                    foreach($childss as $ccc)
                    {
                        $categories[] = $ccc->id;
                    }
                }
            }

            $cc = array();
            foreach($categories as $c)
            {
                $m_ids = \App\Models\MemberCategory::where('category_id', $c)->pluck('member_id')->toArray();
                /*if (sizeof($cc) > 0)
                    $cc = array_intersect($cc, $m_ids);
                else
                    $cc = $m_ids;
                */
                $cc = array_merge($cc, $m_ids);
            }

            $x = $x->whereIn('id', $cc);
            //$cats_ids = \App\Models\MemberCategory::whereIn('category_id', explode(",", $_GET["filterCategories"]))->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["filterCertificateType"] != "null")
        {
            $types = \App\Models\MemberCertificate::where('type', $_GET["filterCertificateType"])->where('expire_date', '>', date("Y-m-d"))->pluck('member_id');
            $x = $x->whereIn('id', $types->toArray());;
            //$ids = array_merge($ids, $types->toArray());
        }

        if ($_GET["filterScadenza"] != "null") {
            $scadenzaValues = explode(",", $_GET["filterScadenza"]);
            $allScadIds = [];

            foreach ($scadenzaValues as $filterValue) {
                if ($filterValue == "1") {
                    $memberLatestCerts = DB::table('member_certificates')
                        ->select('member_id', DB::raw('MAX(expire_date) as latest_expire_date'))
                        ->groupBy('member_id')
                        ->get();

                    $expiredMemberIds = [];
                    foreach ($memberLatestCerts as $cert) {
                        if (date('Y-m-d', strtotime($cert->latest_expire_date)) < date('Y-m-d')) {
                            $expiredMemberIds[] = $cert->member_id;
                        }
                    }
                    $scadIds = $expiredMemberIds;

                    Log::info("Expired certificates filter - member count: " . count($scadIds));
                    Log::info("Expired certificates member IDs: " . implode(", ", $scadIds));
                } else if ($filterValue == "2") {
                    $memberLatestCerts = DB::table('member_certificates')
                        ->select('member_id', DB::raw('MAX(expire_date) as latest_expire_date'))
                        ->groupBy('member_id')
                        ->get();

                    $expiringMemberIds = [];
                    foreach ($memberLatestCerts as $cert) {
                        $expireDate = date('Y-m-d', strtotime($cert->latest_expire_date));
                        $today = date('Y-m-d');
                        $oneMonthLater = date('Y-m-d', strtotime("+1 month"));

                        if ($expireDate >= $today && $expireDate <= $oneMonthLater) {
                            $expiringMemberIds[] = $cert->member_id;
                        }
                    }
                    $scadIds = $expiringMemberIds;
                } else if ($filterValue == "3") {
                    $scadIds = \App\Models\Member::whereNotIn('id', \App\Models\MemberCertificate::pluck('member_id'))->pluck('id')->toArray();
                } else if ($filterValue == "4") {
                    $memberLatestCerts = DB::table('member_certificates')
                        ->select('member_id', DB::raw('MAX(expire_date) as latest_expire_date'))
                        ->groupBy('member_id')
                        ->get();

                    $validMemberIds = [];
                    foreach ($memberLatestCerts as $cert) {
                        $expireDate = date('Y-m-d', strtotime($cert->latest_expire_date));
                        $oneMonthLater = date('Y-m-d', strtotime("+1 month"));

                        if ($expireDate > $oneMonthLater) {
                            $validMemberIds[] = $cert->member_id;
                        }
                    }
                    $scadIds = $validMemberIds;
                }

                $allScadIds = array_merge($allScadIds, $scadIds);
            }

            $allScadIds = array_unique($allScadIds);

            Log::info("Total members after filterScadenza: " . count($allScadIds));

            $x = $x->whereIn('id', $allScadIds);
        }
        if ($_GET["filterStatus"] != "null")
        {
            $status = explode(",", $_GET["filterStatus"]);
            $members = \App\Models\Member::all();
            foreach($status as $s)
            {
                foreach($members as $m)
                {

                    $state = $m->isActive();

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

                }

            }
        }

        if (sizeof($ids) > 0)
        {
            $x = $x->whereIn('id', $ids);
        }
        else
        {
            if ($_GET["filterStatus"] != "null")
                $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 ($_GET["order"][0]["column"] == 5)
                $column = 'current_status';
            if ($_GET["order"][0]["column"] == 6)
                $column = 'certificate';
            if ($column != '')
            {
                $x = $x->orderBy($column, $_GET["order"][0]["dir"]);
                if ($column == 'certificate')
                    $x = $x->orderBy('certificate_date', $_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();

        $latestCertificates = [];
        foreach ($x as $member) {
            $latestCert = \App\Models\MemberCertificate::where('member_id', $member->id)
                ->orderBy('expire_date', 'desc')
                ->first();

            if ($latestCert) {
                $latestCertificates[$member->id] = $latestCert->expire_date;
            }
        }
        Log::info($latestCertificates);

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

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

            $status = $r->current_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 (isset($latestCertificates[$r->id])) {
                Log::info("Using latest certificate for member " . $r->id);
                $latest_date = $latestCertificates[$r->id];
                $status = '';

                if ($latest_date < date("Y-m-d")) {
                    $status = "0"; // Expired
                } else if ($latest_date <= date("Y-m-d", strtotime("+1 month"))) {
                    $status = "1"; // Expiring soon
                } else {
                    $status = "2"; // Valid
                }

                $y = $status . "|" . date("d/m/Y", strtotime($latest_date));
            } else if ($r->certificate_date != '') {
                Log::info("Member " . $r->id . " not found in latest certificates array");

                $status = '';

                if ($r->certificate_date < date("Y-m-d")) {
                    $status = "0";
                } else if ($r->certificate_date >= date("Y-m-d") && $r->certificate_date <= date("Y-m-d", strtotime("+1 month"))) {
                    $status = "1";
                } else if ($r->certificate_date > date("Y-m-d", strtotime("+1 month"))) {
                    $status = "2";
                }

                $y = $status . "|" . date("d/m/Y", strtotime($r->certificate_date));
            }

            /*
            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('receipts.created_at as receipt_date')) // , \DB::raw('SUM(records.id) As total'))
                ->leftJoin('members', 'records.member_id', '=', 'members.id')
                ->leftJoin('payment_methods', 'records.payment_method_id', '=', 'payment_methods.id')
                ->leftJoin('receipts', 'records.id', '=', 'receipts.record_id')
                ->where('records.type', 'IN');

        $y = \App\Models\Record::select('records_rows.amount', 'records.member_id', 'records.corrispettivo_fiscale', 'records.deleted', 'records.financial_movement', 'records_rows.causal_id', DB::raw('members.first_name as first_name'), DB::raw('members.last_name as last_name')) // , \DB::raw('SUM(records.id) As total'))
        ->leftJoin('members', 'records.member_id', '=', 'members.id')
        ->leftJoin('records_rows', 'records.id', '=', 'records_rows.record_id')
        //->leftJoin('receipts', 'records.id', '=', 'receipts.record_id')
        ->where('records.type', 'IN');

        $hasFilter = false;

        if (isset($_GET["search"]["value"]))
        {
            if ($_GET["search"]["value"] != '')
            {
                $hasFilter = true;

                $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 . "%'");
                });

                $y = $y->where(function ($query) use ($v) {
                    $query->whereRaw("CONCAT(first_name, ' ', last_name) like '%" . $v . "%'")
                        ->orWhereRaw("CONCAT(last_name, ' ', first_name) like '%" . $v . "%'");
                });
            }

                /*$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 . '%');
                });
                $y = $y->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"] == 1)
            {
                $hasFilter = true;
                $x = $x->where('records.commercial', true );
                $y = $y->where('records.commercial', true );
            }
            if ($_GET["filterCommercial"] == 2)
            {
                $hasFilter = true;
                $x = $x->where('records.commercial', false);
                $y = $y->where('records.commercial', false);
            }
            if ($_GET["filterMember"] > 0)
            {
                $hasFilter = true;
                $x = $x->where('records.member_id', $_GET["filterMember"]);
                $y = $y->where('records.member_id', $_GET["filterMember"]);
            }
            if ($_GET["filterPaymentMethod"] != "null")
            {
                $hasFilter = true;
                $payments = explode(",", $_GET["filterPaymentMethod"]);
                $x = $x->whereIn('records.payment_method_id', $payments);
                $y = $y->whereIn('records.payment_method_id', $payments);
            }
            if ($_GET["filterCausals"] != "null")
            {
                $hasFilter = true;
                $causals = explode(",", $_GET["filterCausals"]);

                // Per ogni causale, se ha dei figli allora aggiungo le causali figlio
                foreach($causals as $c)
                {
                    $childs = \App\Models\Causal::where('parent_id', $c)->get();
                    foreach($childs as $cc)
                    {
                        $causals[] = $cc->id;
                        $childss = \App\Models\Causal::where('parent_id', $cc->id)->get();
                        foreach($childss as $ccc)
                        {
                            $causals[] = $ccc->id;
                        }
                    }
                }

                //$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);
                $y = $y->whereIn('records.id', $causals);
            }
            if ($_GET["filterFrom"] != '')
            {
                $hasFilter = true;
                $x = $x->where('records.date', '>=', $_GET["filterFrom"]);
                $y = $y->where('date', '>=', $_GET["filterFrom"]);
            }
            if ($_GET["filterTo"] != '')
            {
                $hasFilter = true;
                $x = $x->where('records.date', '<=', $_GET["filterTo"]);
                $y = $y->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;

        $causals = [];
        if ($_GET["filterCausals"] != "null")
            $causals = explode(",", $_GET["filterCausals"]);

        foreach($y->get() as $r)
        {

            if (!in_array($r->payment_method_id, $moneys))
            {
                if ((!in_array($r->member_id, $exclude_from_records) || in_array($r->causal_id, $moneysCausal)) && (!$r->deleted || $r->deleted == null) && (!in_array($r->causal_id, $excludeCausals) || in_array($r->causal_id, $moneysCausal)) && (!$r->financial_movement || $r->financial_movement == null) && (!$r->corrispettivo_fiscale || $r->corrispettivo_fiscale == null))
                {
                    if (sizeof($causals) == 0 || in_array($r->causal_id, $causals))
                    {
                        $total += $r->amount;
                        if ($r->vat_id > 0)
                            $total += getVatValue($r->amount, $r->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,
                //'date' => $r->receipt_date != null ? $r->receipt_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,
                //'payment_date' => date("d/m/Y", strtotime($r->date)),
                'status' => $r->deleted ? 'Annullato' : '',
                'action' => $r->id . "||" . ($r->deleted ? 'x' : '')
            );


        }

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

        if ($hasFilter)
            return json_encode(array("data" => $datas,  "recordsTotal" => $count, "recordsFiltered" => $count, "totals" => formatPrice($total)));
        else
            return json_encode(array("data" => $datas,  "recordsTotal" => $count, "recordsFiltered" => $count));

    });

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

        $datas = [];

        $hasFilter = false;

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

        if ($_GET["filterSupplier"] > 0)
        {
            $x = $x->where('supplier_id', $_GET["filterSupplier"]);
            $hasFilter = true;
        }
        /*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);
            $hasFilter = true;
        }
        if ($_GET["filterCausals"] != "null")
        {
            $causals = explode(",", $_GET["filterCausals"]);

            // Per ogni causale, se ha dei figli allora aggiungo le causali figlio
            foreach($causals as $c)
            {
                $childs = \App\Models\Causal::where('parent_id', $c)->get();
                foreach($childs as $cc)
                {
                    $causals[] = $cc->id;
                    $childss = \App\Models\Causal::where('parent_id', $cc->id)->get();
                    foreach($childss as $ccc)
                    {
                        $causals[] = $ccc->id;
                    }
                }
            }

            //$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);
            $hasFilter = true;
        }
        if ($_GET["filterFrom"] != '')
        {
            $x = $x->where('date', '>=', $_GET["filterFrom"]);
            $hasFilter = true;
        }
        if ($_GET["filterTo"] != '')
        {
            $x = $x->where('date', '<=', $_GET["filterTo"]);
            $hasFilter = true;
        }

        $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 . "|"
            );

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

    });

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

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

        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 ($_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["filterYear"] != "")
        {
            $course_ids = \App\Models\Course::where('year', $_GET["filterYear"])->pluck('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_frequency_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_type_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 = [];

        $datas = [];

        $xxx = 1;

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

        }
        */

        $column = '';
        $sort_value = 0;
        if (isset($_GET["order"]))
        {
            $f = $_GET["order"][0]["column"];
            $d = $_GET["order"][0]["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;

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

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

                $cls = getColor($x->months, $i);
                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;
                        $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" => 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,
                "column_18" => $xxx++,
                "column_20" => $s
            );

        }

        $count = $member_course->count();

        $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>";
        }
        $js .= $xx . "§" . $str . "_";
        $str = "";
        foreach($totals as $z => $t)
        {
            if ($z == 1) $xx = 9;
            if ($z == 2) $xx = 10;
            if ($z == 3) $xx = 11;
            if ($z == 4) $xx = 12;
            if ($z == 5) $xx = 13;
            if ($z == 6) $xx = 14;
            if ($z == 7) $xx = 15;
            if ($z == 8) $xx = 16;
            if ($z == 9) $xx = 5;
            if ($z == 10) $xx = 6;
            if ($z == 11) $xx = 7;
            if ($z == 12) $xx = 8;
            $str = '';
            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>";
            }
            $js .= $xx . "§" . $str . "_";
            $xx += 1;
        }

        if (isset($_GET["order"]))
        {
            $s = $_GET["order"][0]["column"];
            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)), $_GET["order"][0]["dir"] == "asc" ? SORT_ASC : SORT_DESC, SORT_NATURAL|SORT_FLAG_CASE, $datas);
        }

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

        if (isset($_GET["start"]))
            $datas = array_slice($datas, $_GET["start"], $_GET["length"]);

        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.*', 'courses.name as course_name', 'members.first_name', 'members.last_name', 'members.email', 'members.phone', 'members.birth_date')
        ->leftJoin('courses', 'member_courses.course_id', '=', 'courses.id')
        ->leftJoin('members', 'member_courses.member_id', '=', 'members.id');

        if (isset($_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');

            /*$member_ids = \App\Models\Member::where(function ($query) use ($v) {
                $query->where('first_name', 'like', '%' . $v . '%')
                        ->orWhere('last_name', 'like', '%' . $v . '%');
            })->pluck('id');*/
            $datas = $datas->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;
                }
            }
            $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_frequency_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_type_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('member_courses.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["filterCertificateScadenza"] != "null") {
            $scadenzaValues = explode(",", $_GET["filterCertificateScadenza"]);
            $allScadIds = [];

            foreach ($scadenzaValues as $filterValue) {
                if ($filterValue == "1") {
                    $memberLatestCerts = DB::table('member_certificates')
                        ->select('member_id', DB::raw('MAX(expire_date) as latest_expire_date'))
                        ->groupBy('member_id')
                        ->get();

                    $expiredMemberIds = [];
                    foreach ($memberLatestCerts as $cert) {
                        if (date('Y-m-d', strtotime($cert->latest_expire_date)) < date('Y-m-d')) {
                            $expiredMemberIds[] = $cert->member_id;
                        }
                    }
                    $allScadIds = array_merge($allScadIds, $expiredMemberIds);

                    Log::info("Course members - Expired certificates filter - member count: " . count($expiredMemberIds));
                } else if ($filterValue == "2") {
                    $memberLatestCerts = DB::table('member_certificates')
                        ->select('member_id', DB::raw('MAX(expire_date) as latest_expire_date'))
                        ->groupBy('member_id')
                        ->get();

                    $expiringMemberIds = [];
                    foreach ($memberLatestCerts as $cert) {
                        $expireDate = date('Y-m-d', strtotime($cert->latest_expire_date));
                        $today = date('Y-m-d');
                        $oneMonthLater = date('Y-m-d', strtotime("+1 month"));

                        if ($expireDate >= $today && $expireDate <= $oneMonthLater) {
                            $expiringMemberIds[] = $cert->member_id;
                        }
                    }
                    $allScadIds = array_merge($allScadIds, $expiringMemberIds);
                } else if ($filterValue == "3") {
                    $scadIds = \App\Models\Member::whereNotIn('id', \App\Models\MemberCertificate::pluck('member_id'))
                        ->pluck('id')
                        ->toArray();
                    $allScadIds = array_merge($allScadIds, $scadIds);
                } else if ($filterValue == "4") {
                    $memberLatestCerts = DB::table('member_certificates')
                        ->select('member_id', DB::raw('MAX(expire_date) as latest_expire_date'))
                        ->groupBy('member_id')
                        ->get();

                    $validMemberIds = [];
                    foreach ($memberLatestCerts as $cert) {
                        $expireDate = date('Y-m-d', strtotime($cert->latest_expire_date));
                        $oneMonthLater = date('Y-m-d', strtotime("+1 month"));

                        if ($expireDate > $oneMonthLater) {
                            $validMemberIds[] = $cert->member_id;
                        }
                    }
                    $allScadIds = array_merge($allScadIds, $validMemberIds);
                }
            }

            // Remove duplicates
            $allScadIds = array_unique($allScadIds);
            Log::info("Course members - Total members after filterCertificateScadenza: " . count($allScadIds));

            // Apply filter using the collected IDs
            $datas = $datas->whereIn('member_id', $allScadIds);
        }
        if ($_GET["fromYear"] != "")
        {
            $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"] != "")
        {
            $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"] != "")
        {
            $m_ids = \App\Models\Member::whereYear('birth_date', '>=', $_GET["fromFromYear"])->pluck('id');
            $datas = $datas->whereIn('member_id', $m_ids);
        }
        if ($_GET["toToYear"] != "")
        {
            $m_ids = \App\Models\Member::whereYear('birth_date', '<=', $_GET["toToYear"])->pluck('id');
            $datas = $datas->whereIn('member_id', $m_ids);
        }
        if ($_GET["filterCards"] != "null")
        {
            $cards = explode(",", $_GET["filterCards"]);
            $card_ids = \App\Models\MemberCard::whereIn('card_id', $cards)->pluck('member_id');
            $datas = $datas->whereIn('member_id', $card_ids);
        }

        if ($_GET["filterYear"] != "")
        {
            $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 (isset($_GET["order"]))
        {
            $column = '';
            if ($_GET["order"][0]["column"] == 1)
                $column = 'course_name';
            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 = 'birth_date';
            if ($_GET["order"][0]["column"] == 5)
                $column = 'birth_date';
            if ($_GET["order"][0]["column"] == 6)
                $column = 'birth_date';
            if ($_GET["order"][0]["column"] == 7)
                $column = 'phone';
            if ($_GET["order"][0]["column"] == 8)
                $column = 'certificate_expire_date';
            if ($column != '')
                $datas = $datas->orderBy($column, $_GET["order"][0]["dir"]);
            else
                $datas = $datas->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');
        }
        else
            $datas = $datas->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');

        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);
            $certificate = \App\Models\MemberCertificate::where('member_id', $r->member_id)
            ->orderBy('expire_date', 'desc')
            ->first();

            $certificateInfo = "";
            if ($certificate) {
                // Format: status|expire_date|type
                // Status: 0 = expired, 1 = expiring soon, 2 = valid
                $today = new DateTime();
                $expireDate = new DateTime($certificate->expire_date);
                $oneMonthFromNow = (new DateTime())->modify('+1 month');

                $status = "2"; // Default to valid
                if ($expireDate < $today) {
                    $status = "0"; // Expired
                } elseif ($expireDate < $oneMonthFromNow) {
                    $status = "1"; // Expiring soon
                }

                $certificateInfo = $status . "|" . date("d/m/Y", strtotime($certificate->expire_date)) . "|" . $certificate->type;
            }


            $ret[] = array(

                "column_0" => $idx + 1,
                "column_8" => $r->course_name,
                "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" => $certificateInfo,
                "column_7" => $r->member_id
            );
        }

        if (isset($_GET["start"]))
            $ret = array_slice($ret, $_GET["start"], $_GET["length"]);

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

    });

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


        $x = \App\Models\Receipt::select('receipts.*', 'members.first_name', 'members.last_name')->leftJoin('members', 'receipts.member_id', '=', 'members.id');
        if (isset($_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');
            /*$member_ids = \App\Models\Member::where(function ($query) use ($v) {
                $query->where('first_name', 'like', '%' . $v . '%')
                        ->orWhere('last_name', 'like', '%' . $v . '%');
            })->pluck('id');*/
            $x = $x->whereIn('member_id', $member_ids);
        }
        if ($_GET["filterStatus"] != '')
            $x = $x->where('receipts.status', $_GET["filterStatus"]);
        if ($_GET["filterFrom"] != "")
            $x = $x->where('date', '>=', $_GET["filterFrom"]);
        if ($_GET["filterTo"] != "")
            $x = $x->where('date', '<=', $_GET["filterTo"]);

        $count = $x->count();

        if (isset($_GET["order"]))
        {
            $column = '';
            if ($_GET["order"][0]["column"] == 0)
                $column = 'year';
            if ($_GET["order"][0]["column"] == 1)
                $column = 'number';
            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 = 'status';
            if ($_GET["order"][0]["column"] == 5)
                $column = 'date';
            if ($column != '')
                $x = $x->orderBy($column, $_GET["order"][0]["dir"])->orderBy('id', 'DESC');
            else
                $x = $x->orderBy('id', 'DESC');
        }
        else
            $x = $x->orderBy('id', 'DESC');

        if (isset($_GET["start"]))
            $x = $x->offset($_GET["start"])->limit($_GET["length"])->get();
        else
            $x = $x->get();
        $datas = [];
        foreach($x as $idx => $r)
        {
            $ids = $r->id . "|" . $r->record_id;
            $datas[] = array(
                'year' => $r->year,
                'number' => $r->number,
                'last_name' => $r->member->last_name,
                'first_name' => $r->member->first_name,
                'status' => $r->status,
                'date' => date("d/m/Y", strtotime($r->date)),
                'totals' => formatPrice($r->rows->sum('amount')),
                'action' => $ids
            );

        }

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

    });


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

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

Route::get('/updateData', function()
{
    // Call and Artisan command from within your application.
    Artisan::call('update:data');
});

Route::get('/seed', function()
{
    // Call and Artisan command from within your application.
    Artisan::call('db:seed');
});


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

    $member_courses = \App\Models\MemberCourse::all();
    foreach($member_courses as $x)
    {

        $records = \App\Models\Record::where('member_course_id', $x->id)->get();

        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'))
                {
                    $row->causal_id = $x->course->sub_causal_id;
                    $row->save();
                }
            }
        }
    }
});

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

    $expire_date = date("Y-m-d", strtotime("+1 month"));
    $expire_date_it = date("d/m/Y", strtotime("+1 month"));
    $certificates = \App\Models\MemberCertificate::where('expire_date', $expire_date)->get();
    foreach($certificates as $certificate)
    {
        //$phone = $certificate->member->phone;
        $phone = '3893163265';
        $params = array(
            'to'            => '+39' . $phone,
            'from'          => env('SMS_FROM', 'Test'),
            'message'       => $certificate->member->first_name . ', il tuo certificato medico scadrà il ' . $expire_date_it,
            'format'        => 'json',
        );

        sms_send($params);
        print "Inviato";
    }

    /*

    $params = array(
        'to'            => '+393893163265',
        'from'          => env('SMS_FROM', 'Test'),
        'message'       => 'Prova SMS da IAO',
        'format'        => 'json',
    );

    echo sms_send($params);

    */

});