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;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Config;

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

function setDatabase()
{

    // Recupero i dati di connessione
    $database = '';
    $username = '';
    $password = '';

    $connectionName = 'mysql';
    $config = Config::get('database.connections.' . $connectionName);
    $config = [
        'driver' => 'mysql',
        'host' => '127.0.0.1',
        'port' => '3306',
        'database' => $database,
        'username' => $username,
        'password' => $password
    ];
    config()->set('database.connections.' . $connectionName, $config);
    DB::purge($connectionName);
    //DB::setDefaultConnection('mysql');

}

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

    // setDatabase();

    //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_member_one', \App\Http\Livewire\CourseMemberOne::class);
    Route::get('/course_member_two', \App\Http\Livewire\CourseMemberTwo::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('/rates', \App\Http\Livewire\Rate::class);
    Route::get('/reports', \App\Http\Livewire\Reports::class);
    Route::get('/azienda', \App\Http\Livewire\Azienda::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->getStatus();
                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";
        }

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

    $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"] != "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::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 = '';
        $causalCount = 0;
        foreach ($r->rows as $row) {
            $causals .= $row->causal->getTree();
            $causalCount++;
            if ($causalCount % 2 == 0) {
                $causals .= "<br>";
            } else if ($causalCount < count($r->rows)) {
                $causals .= " / ";
            }
        }


        $datas[] = array(
            'date' => $r->date,
            'total' => formatPrice($r->getTotal()),
            'numero_fattura' => $r->numero_fattura,
            'supplier' => $r->supplier->name,
            'causals' => $causals,
            'payment' => $r->payment_method->name,
            'is_paid' => $r->is_paid ? 'Pagato' : 'Non Pagato',
            'action' => $r->id . "|"
        );
    }
    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','members.gender')
        ->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 (isset($_GET["filterFromPrevious"]) && $_GET["filterFromPrevious"] != "") {
        $datas = $datas->whereIn('course_id', [$_GET["filterFromPrevious"]]);
    }

    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 ($_GET["order"][0]["column"] == 9)
            $column = 'gender';
        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;
        }

        $genderDisplay = "";
        if ($r->gender == 'M' || $r->gender == 'U') {
            $genderDisplay = "Uomo";
        } elseif ($r->gender == 'F' || $r->gender == 'D') {
            $genderDisplay = "Donna";
        } elseif ($r->gender == 'O') {
            $genderDisplay = "Altro";
        }elseif ($r->gender == null || $r->gender == "") {
            $genderDisplay = "N/A";
        }

        $status = 0;
        $rates = \App\Models\Rate::where('member_course_id', $r->id)->orderBy('date')->get();
        foreach($rates as $rate)
        {
            if ($rate->date > date("Y-m-d"))
            {
                break;
            }
            $status = $rate->status;
        }

        $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_9" => $genderDisplay,
            "column_5" => $r->phone,
            "column_6" => $certificateInfo,
            "column_7" => $r->member_id,
            "column_10" => $r->id,
            "column_11" => $status,
        );
    }

    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 () {
    // Start with a base query - but without the GROUP BY for counting
    $baseQuery = \App\Models\Receipt::select('receipts.*')
        ->leftJoin('members', 'receipts.member_id', '=', 'members.id');

    // Apply filters
    if (isset($_GET["search"]["value"]) && !empty($_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');
        $baseQuery = $baseQuery->whereIn('receipts.member_id', $member_ids);
    }

    if (isset($_GET["filterStatus"]) && $_GET["filterStatus"] != '')
        $baseQuery = $baseQuery->where('receipts.status', $_GET["filterStatus"]);

    if (isset($_GET["filterFrom"]) && $_GET["filterFrom"] != "")
        $baseQuery = $baseQuery->where('receipts.date', '>=', $_GET["filterFrom"]);

    if (isset($_GET["filterTo"]) && $_GET["filterTo"] != "")
        $baseQuery = $baseQuery->where('receipts.date', '<=', $_GET["filterTo"]);

    if (isset($_GET["filterMember"]) && $_GET["filterMember"] != "")
        $baseQuery = $baseQuery->where('receipts.member_id', $_GET["filterMember"]);

    // Count BEFORE applying the GROUP BY
    $count = $baseQuery->distinct()->count('receipts.id');

    // Clone the base query and add the group by and joins for the data
    $dataQuery = clone $baseQuery;
    $dataQuery = $dataQuery->select(
        'receipts.*',
        'members.first_name',
        'members.last_name',
        DB::raw('SUM(receipts_rows.amount) AS totals')
    )
    ->leftJoin('receipts_rows', 'receipts.id', '=', 'receipts_rows.receip_id')
    ->groupBy('receipts.id');

    // Apply sorting
    if (isset($_GET["order"])) {
        $column = '';
        if ($_GET["order"][0]["column"] == 0)
            $column = 'receipts.year';
        if ($_GET["order"][0]["column"] == 1)
            $column = 'receipts.number';
        if ($_GET["order"][0]["column"] == 2)
            $column = 'members.last_name';
        if ($_GET["order"][0]["column"] == 3)
            $column = 'members.first_name';
        if ($_GET["order"][0]["column"] == 4)
            $column = 'receipts.status';
        if ($_GET["order"][0]["column"] == 5)
            $column = 'receipts.date';
        if ($_GET["order"][0]["column"] == 6)
            $column = 'totals';

        if ($column != '')
            $dataQuery = $dataQuery->orderBy($column, $_GET["order"][0]["dir"])->orderBy('receipts.id', 'DESC');
        else
            $dataQuery = $dataQuery->orderBy('receipts.id', 'DESC');
    } else {
        $dataQuery = $dataQuery->orderBy('receipts.id', 'DESC');
    }

    // Apply pagination
    if (isset($_GET["start"]) && isset($_GET["length"]))
        $dataQuery = $dataQuery->offset($_GET["start"])->limit($_GET["length"]);

    // Get the results
    $results = $dataQuery->get();

    // Format the data
    $datas = [];
    foreach ($results 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->created_at)),
            'totals' => formatPrice($r->totals),
            'action' => $ids
        );
    }

    // Return the proper format with correct counts
    return response()->json([
        "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('/send_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;
        $message = 'Ciao ' . $certificate->member->first_name . ', ci risulta che il tuo certificato medico scade il ' . $expire_date_it . '. Per continuare ad allenarti senza problemi, ricordati di rinnovarlo in tempo. Ti aspettiamo in campo! Centro Sportivo La Madonnella';
        $params = array(
            'to'            => '+39' . $phone,
            'from'          => env('SMS_FROM', 'Test'),
            'message'       => $message,
            'format'        => 'json',
        );
        sms_send($params);
    }

    $expire_date = date("Y-m-d", strtotime("+15 days"));
    $expire_date_it = date("d/m/Y", strtotime("+15 days"));
    $certificates = \App\Models\MemberCertificate::where('expire_date', $expire_date)->get();
    foreach ($certificates as $certificate) {
        $phone = $certificate->member->phone;
        $message = 'Ciao ' . $certificate->member->first_name . ', ci risulta che il tuo certificato medico scade il ' . $expire_date_it . '. Per continuare ad allenarti senza problemi, ricordati di rinnovarlo in tempo. Ti aspettiamo in campo! Centro Sportivo La Madonnella';
        $params = array(
            'to'            => '+39' . $phone,
            'from'          => env('SMS_FROM', 'Test'),
            'message'       => $message,
            'format'        => 'json',
        );
        sms_send($params);
    }

});

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

    $receipt = \App\Models\Receipt::first();
    $pdf = PDF::loadView('receipt', array('receipt' => $receipt));
    $pdfName = "ricevuta_" . $receipt->number . "_" . $receipt->year . ".pdf";
    Storage::put('public/pdf/' . $pdfName, $pdf->output());
    $email = "l.parisio@webmagistri.biz"; // \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
        ]));
    }

});