leezard/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\Mail;
use Illuminate\Support\Facades\Storage;
use App\Services\MigrationService;
use App\Services\MultiTenantAuthService;
use App\Http\Controllers\PasswordResetController;
/*
|--------------------------------------------------------------------------
| 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');
})->name('login');

Route::post('/login', function () {
    $email = request('email');
    $password = request('password');

    if (Auth::attempt(['email' => $email, 'password' => $password])) {
        $user = Auth::user();

        if (!$user->first_login_completed) {
            $user->first_login_at = now();
            $user->save();

            return redirect('/first-login');
        }

        return redirect('/dashboard');
    } else {
        return redirect('/?error=1');
    }
})->name('login.submit');

Route::get('/password-reset-request', [PasswordResetController::class, 'showResetRequestForm'])->name('password.request');
Route::post('/password-reset-request', [PasswordResetController::class, 'sendResetEmail'])->name('password.email');
Route::get('/password-reset/{token}', [PasswordResetController::class, 'showResetForm'])->name('password.reset');
Route::post('/password-reset', [PasswordResetController::class, 'resetPassword'])->name('password.update');



Route::get('/first-login', [App\Http\Controllers\FirstLoginController::class, 'show'])
    ->middleware('auth')
    ->name('first-login');

Route::post('/first-login/complete', [App\Http\Controllers\FirstLoginController::class, 'complete'])
    ->middleware('auth')
    ->name('first-login.complete');

Route::get('/profile', function () {
    return view('profile.index');
})->middleware(['auth', 'first.login.completed'])->name('profile');

Route::middleware(['auth', 'first.login.completed'])->group(function () {
    Route::get('/dashboard', function () {
        return view('dashboard');
    })->name('dashboard');

    Route::get('/users', function () {
        return view('users.index');
    })->name('users');

});

Route::get('/logout', function () {
    session()->forget('currentClient');
    Auth::logout();
    return redirect('/');
})->name('logout');


//Route::group(['middleware' => 'auth'], function () {
Route::group(['middleware' => 'tenant'], 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_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('/records_old', \App\Http\Livewire\RecordOld::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('/sms_comunications', \App\Http\Livewire\SmsComunications::class);
    Route::get('/mail_comunications', \App\Http\Livewire\EmailComunications::class);
    Route::get('/members_archive', \App\Http\Livewire\MemberArchive::class);
    Route::get('/subscriptions', \App\Http\Livewire\Subscription::class);
    Route::get('/subscription_member', \App\Http\Livewire\SubscriptionMembers::class);
    Route::get('/subscription_member/{id}', \App\Http\Livewire\SubscriptionMember::class);

    Route::get('/calendar', \App\Http\Livewire\Calendar::class);
    Route::get('/presences', \App\Http\Livewire\Presence::class);
    Route::get('/presence_reports', \App\Http\Livewire\PresenceReport::class);
    Route::get('/absence_reports', \App\Http\Livewire\AbsenceReport::class);

    Route::get('/receipt/{id}', function ($id) {
        $receipt = \App\Models\Receipt::findOrFail($id);
        $pdf = PDF::loadView('receipt', array('receipt' => $receipt));

        $lastName = Illuminate\Support\Str::slug($receipt->member->last_name ?? 'sconosciuto', '_');
        $firstName = Illuminate\Support\Str::slug($receipt->member->first_name ?? 'sconosciuto', '_');

        $pdfName = "Ricevuta_{$receipt->number}_{$lastName}_{$firstName}.pdf";

        return $pdf->stream($pdfName);
    });

    Route::get('/export_receipts', [App\Http\Controllers\ReceiptExportController::class, 'exportZip']);
    // Route::get('/all_receipts', function () {
    //     $receipts = \App\Models\Receipt::with('member')->get();

    //     if ($receipts->isEmpty()) {
    //         abort(404, 'Nessuna ricevuta trovata.');
    //     }

    //     $zipFileName = 'ricevute_' . now()->format('Ymd_His') . '.zip';
    //     $zipPath = storage_path('app/' . $zipFileName);

    //     $zip = new ZipArchive();
    //     if ($zip->open($zipPath, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
    //         abort(500, 'Impossibile creare lo ZIP.');
    //     }

    //     foreach ($receipts as $receipt) {
    //         $lastName = Illuminate\Support\Str::slug($receipt->member->last_name ?? 'sconosciuto', '_');
    //         $firstName = Illuminate\Support\Str::slug($receipt->member->first_name ?? 'sconosciuto', '_');

    //         $pdfName = "Ricevuta_{$receipt->number}_{$lastName}_{$firstName}.pdf";

    //         $pdf = PDF::loadView('receipt', ['receipt' => $receipt]);

    //         $zip->addFromString($pdfName, $pdf->output());
    //     }

    //     $zip->close();

    //     return response()->download($zipPath)->deleteFileAfterSend(true);
    // });

    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) {
        $q = request('q');

        $query = \App\Models\Province::where('nation_id', $nation_id);
        if ($q !== null && $q !== '') {
            $query->where('name', 'like', $q.'%');
        }

        $datas = $query->orderBy('name')->get(['id','name']);

        $results = $datas->map(function ($d) {
            $name = $d->name;
            $name = mb_convert_encoding($name, 'UTF-8', 'UTF-8, ISO-8859-1, Windows-1252');
            $name = iconv('UTF-8', 'UTF-8//IGNORE', $name);

            return ['id' => $d->id, 'text' => $name];
        })->all();

        return response()->json(['results' => $results], 200, [], JSON_UNESCAPED_UNICODE);
    });

    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 () {
        app(\App\Http\Middleware\TenantMiddleware::class)->setupTenantConnection();

        $datas = [];

        $archived = (isset($_GET['archived']) && $_GET['archived'] == 1) ? true : false;

        $x = \App\Models\Member::select('id', 'first_name', 'last_name', 'phone', 'birth_date', 'to_complete', 'current_status', 'certificate', 'certificate_date')
            ->where(function($query) use($archived) {
                $query->where('is_archived', $archived);
                if (!$archived)
                    $query->orWhereNull('is_archived');
            })
            ->where(function($query) {
                $query->where('is_deleted', false)
                    ->orWhereNull('is_deleted');
            });

        if (isset($_GET["search"]["value"]) && $_GET["search"]["value"] != "") {
            $v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));
            $x = $x->where(function ($query) use ($v) {
                $query->whereRaw("CONCAT(TRIM(first_name), ' ', TRIM(last_name)) like ?", ["%{$v}%"])
                    ->orWhereRaw("CONCAT(TRIM(last_name), ' ', TRIM(first_name)) like ?", ["%{$v}%"]);
            });
        }

        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"]);

            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();
                $cc = array_merge($cc, $m_ids);
            }

            $x = $x->whereIn('id', $cc);
        }

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

        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;
                } 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") {
                    // Only non-archived members without certificates
                    $scadIds = \App\Models\Member::whereNotIn('id', \App\Models\MemberCertificate::pluck('member_id'))
                        ->where(function($query) use($archived) {
                            $query->where('is_archived', $archived);
                            if (!$archived)
                                $query->orWhereNull('is_archived');
                        })
                        ->where(function($query) {
                            $query->where('is_deleted', false)
                                ->orWhereNull('is_deleted');
                        })
                        ->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);
            $x = $x->whereIn('id', $allScadIds);
        }

        if ($_GET["filterStatus"] != "null") {
            $status = explode(",", $_GET["filterStatus"]);
            // Only get non-archived members for status filtering
            $members = \App\Models\Member::where(function($query) use($archived) {
                $query->where(function($q) use($archived) {
                    $q->where('is_archived', $archived);
                    if (!$archived)
                        $q->orWhereNull('is_archived');
                })
                ->where(function($q) {
                    $q->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                });
            })->get();

            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();

        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 = 'years';
            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 != '') {
                if ($column == 'last_name') {
                    $x = $x->orderBy('to_complete', 'DESC');
                }
                if ($column == 'certificate')
                    $x = $x->orderBy('certificate_date', $_GET["order"][0]["dir"]);
                elseif ($column == 'current_status') {
                    $x = $x->orderBy('to_complete', 'DESC');
                    $x = $x->orderBy($column, $_GET["order"][0]["dir"]);
                } elseif ($column == 'years') {
                    $column = 'birth_date';
                    $dirs = ['asc' => 'desc', 'desc' => 'asc'];
                    $x = $x->orderByRaw($column . ' IS NULL ' . $dirs[$_GET["order"][0]["dir"]])
                        ->orderBy($column, $dirs[$_GET["order"][0]["dir"]]);
                } elseif ($column == 'birth_date') {
                    $x = $x->orderByRaw($column . ' IS NULL ' . $_GET["order"][0]["dir"])
                        ->orderBy($column, $_GET["order"][0]["dir"]);
                } else {
                    $x = $x->orderBy($column, $_GET["order"][0]["dir"]);
                }
            } else {
                $x = $x->orderBy('to_complete', 'DESC');
                $x = $x->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');
            }
        } else {
            $x = $x->orderBy('to_complete', 'DESC');
            $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();

        // Process results exactly as before
        $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;
            }
        }

        foreach ($x as $idx => $r) {
            $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])) {
                $latest_date = $latestCertificates[$r->id];
                $certStatus = '';

                if ($latest_date < date("Y-m-d")) {
                    $certStatus = "0";
                } else if ($latest_date <= date("Y-m-d", strtotime("+1 month"))) {
                    $certStatus = "1";
                } else {
                    $certStatus = "2";
                }

                $y = $certStatus . "|" . date("d/m/Y", strtotime($latest_date));
            } else if ($r->certificate_date != '') {
                $certStatus = '';

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

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

            $datas[] = array(
                'id' => $r->id,
                'last_name' => $r->last_name . "|" . $r->id,
                'first_name' => $r->first_name . "|" . $r->id,
                'phone' => $r->phone,
                'age' => $r->getAge(),
                'year' => $r->birth_date ? date("Y", strtotime($r->birth_date)) : 'N/A',
                'status' => $class . "|" . $text,
                'certificate' => $y,
                'action' => $r->id
            );
        }

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

    Route::get('/get_recipients', function () {
        app(\App\Http\Middleware\TenantMiddleware::class)->setupTenantConnection();

        $datas = [];

        $x = \App\Models\Member::select('id', 'first_name', 'last_name', 'email', 'phone', 'birth_date', 'to_complete', 'current_status', 'certificate', 'certificate_date')
            ->where(function($query) {
                $query->where('is_archived', false)
                    ->orWhereNull('is_archived');
            })
            ->where(function($query) {
                $query->where('is_deleted', false)
                    ->orWhereNull('is_deleted');
            });

        if (isset($_GET["search"]["value"]) && $_GET["search"]["value"] != "") {
            $v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));
            $x = $x->where(function ($query) use ($v) {
                $query->whereRaw("CONCAT(TRIM(first_name), ' ', TRIM(last_name)) like ?", ["%{$v}%"])
                    ->orWhereRaw("CONCAT(TRIM(last_name), ' ', TRIM(first_name)) like ?", ["%{$v}%"]);
            });
        }

        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"]);

            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();
                $cc = array_merge($cc, $m_ids);
            }

            $x = $x->whereIn('id', $cc);
        }

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

        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;
                } 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'))
                        ->where(function($query) {
                            $query->where('is_archived', false)
                                ->orWhereNull('is_archived');
                        })
                        ->where(function($query) {
                            $query->where('is_deleted', false)
                                ->orWhereNull('is_deleted');
                        })
                        ->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);
            $x = $x->whereIn('id', $allScadIds);
        }

        if ($_GET["filterStatus"] != "null") {
            $status = explode(",", $_GET["filterStatus"]);
            $members = \App\Models\Member::where(function($query) {
                $query->where(function($q) {
                    $q->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($q) {
                    $q->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                });
            })->get();

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

        if (isset($_GET["filterCourses"]) && $_GET["filterCourses"] !== "" && $_GET["filterCourses"] !== "null") {
            $requested = array_filter(explode(",", $_GET["filterCourses"]), fn($v) => $v !== "");

            $courseIds = [];
            foreach ($requested as $cId) {
                $courseIds[] = (int)$cId;

                $children = \App\Models\Course::where('parent_id', $cId)->pluck('id')->all();
                foreach ($children as $_cid) {
                    $courseIds[] = (int)$_cid;

                    $grand = \App\Models\Course::where('parent_id', $_cid)->pluck('id')->all();
                    foreach ($grand as $__cid) {
                        $courseIds[] = (int)$__cid;
                    }
                }
            }
            $courseIds = array_values(array_unique($courseIds));

            if (!empty($courseIds)) {
                $memberIdsByCourse = \App\Models\MemberCourse::whereIn('course_id', $courseIds)
                    ->pluck('member_id')->unique()->values()->all();

                $x = !empty($memberIdsByCourse)
                    ? $x->whereIn('id', $memberIdsByCourse)
                    : $x->whereIn('id', [-1]);
            }
        }

        $count = $x->count();

        if (isset($_GET["order"])) {
            $column = '';
            if ($_GET["order"][0]["column"] == 1)
                $column = 'last_name';
            if ($_GET["order"][0]["column"] == 2)
                $column = 'first_name';
            if ($_GET["order"][0]["column"] == 3)
                $column = 'email';
            if ($_GET["order"][0]["column"] == 4)
                $column = 'phone';
            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 = 'current_status';
            if ($_GET["order"][0]["column"] == 8)
                $column = 'certificate';

            if ($column != '') {
                if ($column == 'certificate')
                    $x = $x->orderBy('certificate_date', $_GET["order"][0]["dir"]);
                elseif ($column == 'current_status') {
                    $x = $x->orderBy($column, $_GET["order"][0]["dir"]);
                } else {
                    $x = $x->orderBy($column, $_GET["order"][0]["dir"]);
                }
            } else {
                $x = $x->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');
            }
        } else {
            $x = $x->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');
        }

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

        $memberIds = $x->pluck('id')->all();
        $catsByMember = \App\Models\MemberCategory::with('category')
            ->whereIn('member_id', $memberIds)
            ->get()
            ->groupBy('member_id')
            ->map(function($rows){
                $names = $rows->pluck('category.name')->filter()->unique()->sort()->values()->all();
                return implode(', ', $names);
            });

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

        foreach ($x as $idx => $r) {
            $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])) {
                $latest_date = $latestCertificates[$r->id];
                $certStatus = '';

                if ($latest_date < date("Y-m-d")) {
                    $certStatus = "0";
                } else if ($latest_date <= date("Y-m-d", strtotime("+1 month"))) {
                    $certStatus = "1";
                } else {
                    $certStatus = "2";
                }

                $y = $certStatus . "|" . date("d/m/Y", strtotime($latest_date));
            } else if ($r->certificate_date != '') {
                $certStatus = '';

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

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

            $datas[] = array(
                'id' => $r->id,
                'last_name' => $r->last_name . "|" . $r->id,
                'first_name' => $r->first_name . "|" . $r->id,
                'email' => $r->email . "|" . $r->id,
                'phone' => $r->phone,
                'age' => $r->getAge(),
                'year' => $r->birth_date ? date("Y", strtotime($r->birth_date)) : 'N/A',
                'status' => $class . "|" . $text,
                'certificate' => $y,
                'categories' => $catsByMember[$r->id] ?? '',
                'action' => $r->id
            );
        }

        return response()->json([
            "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', 'records_rows.course_id', 'records_rows.subscription_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["filterCourses"] != "null") {
            $hasFilter = true;
            $courses = explode(",", $_GET["filterCourses"]);

            // Per ogni corso, se ha dei figli allora aggiungo i corsi figli
            foreach ($courses as $c) {
                $childs = \App\Models\Course::where('parent_id', $c)->get();
                foreach ($childs as $cc) {
                    $courses[] = $cc->id;
                    $childss = \App\Models\Course::where('parent_id', $cc->id)->get();
                    foreach ($childss as $ccc) {
                        $courses[] = $ccc->id;
                    }
                }
            }

            //$courses = \App\Models\RecordRow::where('course_id', $_GET["filtercourses"])->pluck('record_id');
            $courses = \App\Models\RecordRow::whereIn('course_id', $courses)->pluck('record_id');
            $x = $x->whereIn('records.id', $courses);
            $y = $y->whereIn('records.id', $courses);
        }
        if ($_GET["filterSubscriptions"] != "null") {
            $hasFilter = true;
            $subscriptions = explode(",", $_GET["filterSubscriptions"]);

            $subscriptions = \App\Models\RecordRow::whereIn('subscription_id', $subscriptions)->pluck('record_id');
            $x = $x->whereIn('records.id', $subscriptions);
            $y = $y->whereIn('records.id', $subscriptions);
        }
        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)
            ->where(function($query) {
                $query->where('is_archived', false)
                    ->orWhereNull('is_archived');
            })
            ->where(function($query) {
                $query->where('is_deleted', false)
                    ->orWhereNull('is_deleted');
            })->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 = '';
            $courses = '';
            $subscriptions = '';
            foreach ($r->rows as $row) {
                $causals .= $row->causal->getTree() . "<br>";

                if ($row->course)
                    $courses .= $row->course->getDetailsName() . "<br>";
                if ($row->subscription)
                    $subscriptions .= $row->subscription->name . "<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,
                'courses' => $subscriptions . $courses,
                '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 . "%'");
                    })
                    ->where(function($query) {
                        $query->where('is_archived', false)
                            ->orWhereNull('is_archived');
                    })
                    ->where(function($query) {
                        $query->where('is_deleted', false)
                            ->orWhereNull('is_deleted');
                    })->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" && $_GET["filterType"] != "undefined") {
            $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"]) && trim($_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 . "%'");
                })
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->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" && $_GET["filterType"] != "undefined") {
            $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'))
                        ->where(function($query) {
                            $query->where('is_archived', false)
                                ->orWhereNull('is_archived');
                        })
                        ->where(function($query) {
                            $query->where('is_deleted', false)
                                ->orWhereNull('is_deleted');
                        })
                        ->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())))
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->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())))
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->pluck('id');
            $datas = $datas->whereIn('member_id', $m_ids);
        }

        if ($_GET["fromFromYear"] != "") {
            $m_ids = \App\Models\Member::whereYear('birth_date', '>=', $_GET["fromFromYear"])
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->pluck('id');
            $datas = $datas->whereIn('member_id', $m_ids);
        }
        if ($_GET["toToYear"] != "") {
            $m_ids = \App\Models\Member::whereYear('birth_date', '<=', $_GET["toToYear"])
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->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;
            $payed = 0;
            $rates = \App\Models\Rate::where('member_course_id', $r->id)->where('date', '<', date("Y-m-d"))->get();
            foreach($rates as $rate)
            {
                if ($rate->date > date("Y-m-d"))
                {
                    break;
                }
                $status = $rate->status;
                if ($rate->status == 1)
                    $payed += 1;
            }
            if ($status == 1 && $payed != sizeof($rates))
                $status = 0;

            $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_subscription_members', function () {
        $subscription_id = 0;
        if (!isset($_GET['subscription_id']) || is_null($_GET['subscription_id']) || $_GET['subscription_id'] <= 0) {
            return json_encode(array("data" => [], "recordsTotal" => 0, "recordsFiltered" => 0));
        }
        $subscription_id = $_GET['subscription_id'];

        $datas = \App\Models\MemberSubscription::select('member_subscriptions.*', 'subscriptions.name as course_name', 'members.first_name', 'members.last_name', 'members.birth_date')
            ->leftJoin('subscriptions', 'member_subscriptions.subscription_id', '=', 'subscriptions.id')
            ->leftJoin('members', 'member_subscriptions.member_id', '=', 'members.id')
            ->where('member_subscriptions.subscription_id', $subscription_id);

        if (isset($_GET["search"]["value"]) && trim($_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 . "%'");
                })
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->pluck('id');

            $datas = $datas->whereIn('member_id', $member_ids);
        }

        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'))
                        ->where(function($query) {
                            $query->where('is_archived', false)
                                ->orWhereNull('is_archived');
                        })
                        ->where(function($query) {
                            $query->where('is_deleted', false)
                                ->orWhereNull('is_deleted');
                        })
                        ->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())))
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->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())))
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->pluck('id');
            $datas = $datas->whereIn('member_id', $m_ids);
        }

        if ($_GET["fromFromYear"] != "") {
            $m_ids = \App\Models\Member::whereYear('birth_date', '>=', $_GET["fromFromYear"])
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->pluck('id');
            $datas = $datas->whereIn('member_id', $m_ids);
        }

        if ($_GET["toToYear"] != "") {
            $m_ids = \App\Models\Member::whereYear('birth_date', '<=', $_GET["toToYear"])
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->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);
        }

        $aRet = [];
        if (isset($_GET["order"])) {
            $column = '';
            if ($_GET["order"][0]["column"] == 1)
                $column = 'last_name';
            if ($_GET["order"][0]["column"] == 2)
                $column = 'first_name';
            if ($_GET["order"][0]["column"] == 3)
                $column = 'certificate_expire_date';
            // if ($_GET["order"][0]["column"] == 4)
            //     $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) {
            $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;
            }

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

            $ret[] = array(
                "column_0" => $idx + 1,
                "column_1" => $r->last_name,
                "column_2" => $r->first_name,
                "column_3" => $certificateInfo,
                "column_4" => $status,
                "column_5" => $r->id,
                "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 () {
        $baseQuery = \App\Models\Receipt::select('receipts.id')
            ->leftJoin('members', 'receipts.member_id', '=', 'members.id');

        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 . "%'");
                })
                ->where(function($query) {
                    $query->where('is_archived', false)
                        ->orWhereNull('is_archived');
                })
                ->where(function($query) {
                    $query->where('is_deleted', false)
                        ->orWhereNull('is_deleted');
                })->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 = $baseQuery->count();

        $receiptIds = $baseQuery->pluck('receipts.id');

        $dataQuery = \App\Models\Receipt::select(
                'receipts.id',
                'receipts.year',
                'receipts.number',
                'receipts.status',
                'receipts.date',
                'receipts.created_at',
                'receipts.record_id',
                'members.first_name',
                'members.last_name',
                DB::raw('SUM(receipts_rows.amount) AS totals')
            )
            ->leftJoin('members', 'receipts.member_id', '=', 'members.id')
            ->leftJoin('receipts_rows', 'receipts.id', '=', 'receipts_rows.receip_id')
            ->whereIn('receipts.id', $receiptIds)
            ->groupBy(
                'receipts.id',
                'receipts.year',
                'receipts.number',
                'receipts.status',
                'receipts.created_at',
                'receipts.record_id',
                'members.first_name',
                'members.last_name'
            );

        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.created_at';
            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');
        }

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

        $results = $dataQuery->get();

        $datas = [];
        foreach ($results as $idx => $r) {
            $ids = $r->id . "|" . $r->record_id;
            $datas[] = array(
                'id' => $r->id,
                'year' => $r->year,
                'number' => $r->number,
                'last_name' => $r->last_name ?? '',
                'first_name' => $r->first_name ?? '',
                'status' => $r->status,
                'date' => date("d/m/Y", strtotime($r->date)),
                'created_at' => date("d/m/Y", strtotime($r->created_at)),
                'totals' => formatPrice($r->totals),
                'action' => $ids
            );
        }

        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/master', function (MigrationService $migrationService) {
    try {
        $result = $migrationService->runMasterMigrations();
        return response()->json($result);
    } catch (\Exception $e) {
        return response()->json([
            'message' => 'Master migration failed: ' . $e->getMessage(),
            'success' => false
        ], 500);
    }
});

Route::get('/migrate/tenants', function (MigrationService $migrationService) {
    try {
        $result = $migrationService->runTenantMigrations();
        return response()->json($result);
    } catch (\Exception $e) {
        return response()->json([
            'message' => 'Tenant migrations failed: ' . $e->getMessage(),
            'success' => false
        ], 500);
    }
});
Route::get('/migrate/master/rollback', function (MigrationService $migrationService) {
    $steps = request('steps', 1);
    try {
        $result = $migrationService->rollbackMasterMigrations($steps);
        return response()->json($result);
    } catch (\Exception $e) {
        return response()->json([
            'message' => 'Master rollback failed: ' . $e->getMessage(),
            'success' => false
        ], 500);
    }
});

// Tenant rollback
Route::get('/migrate/tenants/rollback', function (MigrationService $migrationService) {
    $steps = request('steps', 1);
    try {
        $result = $migrationService->rollbackTenantMigrations($steps);
        return response()->json($result);
    } catch (\Exception $e) {
        return response()->json([
            'message' => 'Tenant rollback failed: ' . $e->getMessage(),
            'success' => false
        ], 500);
    }
});

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) {
//         $new = \App\Models\MemberCertificate::where('expire_date', '>', $expire_date)->count();
//         if ($new == 0)
//         {
//             $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',
//             );
//             $r = sms_send($params);
//             Log::info("SMS");
//             Log::info($r);
//             sleep(1);
//         }
//     }

//     $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) {
//         $new = \App\Models\MemberCertificate::where('expire_date', '>', $expire_date)->count();
//         if ($new == 0)
//         {
//             $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',
//             );
//             $r = sms_send($params);
//             Log::info("SMS");
//             Log::info($r);
//             sleep(1);
//         }
//     }

// });

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

});
Route::middleware(['auth'])->group(function () {
    Route::get('/sms', [App\Http\Controllers\SmsTemplateController::class, 'index'])->name('sms.index');
    Route::get('/sms/templates/json', [App\Http\Controllers\SmsTemplateController::class, 'getTemplatesJson']);
    Route::get('/sms/categories', [App\Http\Controllers\SmsTemplateController::class, 'getCategories']);
    Route::get('/sms/templates/{id}', [App\Http\Controllers\SmsTemplateController::class, 'getTemplate']);
    Route::post('/sms/templates', [App\Http\Controllers\SmsTemplateController::class, 'store']);
    Route::delete('/sms/templates/{id}', [App\Http\Controllers\SmsTemplateController::class, 'destroy']);
    Route::get('/email_comunications', \App\Http\Livewire\EmailComunications::class)->name('email.communications');
    Route::post('/upload', [App\Http\Controllers\FileUpload::class, 'upload'])->name('ckeditor.upload');
});