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('/financial_movements', \App\Http\Livewire\FinancialMovements::class);
Route::get('/motivations', \App\Http\Livewire\Motivation::class);
Route::get('/calendar', \App\Http\Livewire\Calendar::class);
Route::get('/calendar_settings', \App\Http\Livewire\CalendarSettings::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() . "
";
if ($row->course)
$courses .= $row->course->getDetailsName() . "
";
if ($row->subscription)
$subscriptions .= $row->subscription->name . "
";
}
$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 .= "
";
} 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_financial_movements', function () {
$filterFrom = request('filterFrom');
$filterTo = request('filterTo');
$filterOrigins = request('filterOrigins');
$filterDestinations = request('filterDestinations');
$base = \App\Models\FinancialMovement::query()
->leftJoin('banks as origins', 'origins.id', '=', 'financial_movements.origin_id')
->leftJoin('banks as destinations', 'destinations.id', '=', 'financial_movements.destination_id')
->leftJoin('causals as causals', 'causals.id', '=', 'financial_movements.causal_id')
->where('financial_movements.deleted', false);
$hasFilter = false;
if (!empty($filterFrom)) {
$hasFilter = true;
$base->whereDate('financial_movements.date', '>=', $filterFrom);
}
if (!empty($filterTo)) {
$hasFilter = true;
$base->whereDate('financial_movements.date', '<=', $filterTo);
}
if ($filterOrigins && $filterOrigins !== "null") {
$hasFilter = true;
$origins = array_filter(explode(",", $filterOrigins));
if (!empty($origins)) {
$base->whereIn('financial_movements.origin_id', $origins);
}
}
if ($filterDestinations && $filterDestinations !== "null") {
$hasFilter = true;
$destinations = array_filter(explode(",", $filterDestinations));
if (!empty($destinations)) {
$base->whereIn('financial_movements.destination_id', $destinations);
}
}
$recordsFiltered = (clone $base)->count();
$recordsTotal = \App\Models\FinancialMovement::where('deleted', false)->count();
$totalAmount = (clone $base)->sum('financial_movements.amount');
$start = (int) request('start', 0);
$limit = (int) request('length', 100000);
$order = request('order');
if ($order) {
$colIdx = (int) ($order[0]['column'] ?? 0);
$dir = ($order[0]['dir'] ?? 'desc') === 'asc' ? 'asc' : 'desc';
$column = match ($colIdx) {
0 => 'financial_movements.date',
1 => 'financial_movements.amount',
2 => 'origins.name',
3 => 'destinations.name',
default => 'financial_movements.id',
};
$base->orderBy($column, $dir)->orderBy('financial_movements.id', 'DESC');
} else {
$base->orderBy('financial_movements.date', 'DESC')->orderBy('financial_movements.id', 'DESC');
}
$rows = $base
->select([
'financial_movements.id',
'financial_movements.date',
'financial_movements.amount',
'financial_movements.deleted',
'origins.name as origin_name',
'destinations.name as destination_name',
'causals.name as causal_name',
])
->offset($start)
->limit($limit)
->get();
$data = [];
foreach ($rows as $r) {
$data[] = [
'id' => $r->id,
'date' => $r->date,
'origin' => $r->origin_name ?? '',
'destination' => $r->destination_name ?? '',
'total' => formatPrice($r->amount),
'causal' => $r->causal_name ?? '',
'action' => $r->id . "|0|" . ($r->deleted ? 'x' : '')
];
}
$draw = (int) request('draw', 0);
$data = [
"draw" => $draw,
"data" => $data,
"recordsTotal" => $recordsTotal,
"recordsFiltered" => $recordsFiltered,
"totals" => formatPrice($totalAmount),
];
if (!$hasFilter) {
unset($data['totals']);
}
return response()->json($data);
});
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 .= "" . (isset($totalIsc["Y"]) ? formatPrice($totalIsc["Y"]) : 0) . "
";
$str .= "" . (isset($totalIsc["N"]) ? formatPrice($totalIsc["N"]) : 0) . "
";
$str .= "0
";
}
$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 .= "" . $y . "
";
}
$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');
});