File: /var/www/vhost/disk-apps/teamdemo.sports-crowd.com/app/Http/Controllers/AcademyController.php
<?php
namespace App\Http\Controllers;
use App\Parameter;
use App\AcademyCategory;
use App\AcademyDiscount;
use App\AcademyUser;
use App\AcademyState;
use App\AcademyPurchase;
use App\DocumentType;
use App\AcademyParameter;
use App\Address;
use App\AcademyCategoriesSchedule;
use App\City;
use App\State;
use App\AcademyDocument;
use App\Tag;
use App\UserTag;
use App\Http\Controllers\AcademyStateController;
use App\AcademyDocumentUser;
use App\AcademyLocation;
use App\AcademyLocationUser;
use App\AcademySchedule;
use App\AcademyStateUser;
use App\AcademyUsersDiscount;
use App\Core\Academy\Application\AcademyUserService;
use App\Core\Address\Application\AddressService;
use App\Core\Carnet\Application\CarnetService;
use App\Core\CorporateIdentity\Application\CorporateIdentityService;
use App\Http\Controllers\Api\AcademyPeriodApiController;
use Illuminate\Http\Request;
use App\Http\Controllers\UtilController;
use App\Http\Controllers\Exports\AcademyUsersExport;
use App\Http\Controllers\Imports\AcademyImport;
use App\Http\Controllers\Imports\AcademyTagsImport;
use App\Http\Controllers\Imports\AcademyTagsTemplate;
use App\Http\Controllers\Imports\AcademyTemplate;
use App\Http\Controllers\Imports\StudentScheduleImport;
use App\Http\Controllers\Imports\StudentScheduleTemplate;
use App\Mail\RegistrationRenewalNotificationMail;
use App\Models\AcademyReport;
use App\Module;
use App\PaymentTransaction;
use App\User;
use App\Services\AcademyLocationsService;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Facades\Storage;
use Yajra\DataTables\Facades\DataTables;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Validation\Rule;
class AcademyController extends Controller
{
const DATETIME_FORMAT = 'Y-m-d h:i:s A';
const SUPERADMIN_ROL = 1;
const ADMIN_ROL = 15;
private $util;
private $academyUserService;
private $addressService;
private $carnetService;
public function __construct()
{
$this->util = new UtilController();
$this->academyUserService = new AcademyUserService();
$this->addressService = new AddressService();
$this->carnetService = new CarnetService();
}
public function academyUsers($type_academy = 'children')
{
$states = AcademyState::select('id', 'name')->where('active', 1)->orderBy('name', 'ASC')->get();
$textTermsColumn = AcademyParameter::select('value')->where('key', 'terms_and_conditions_text')->first();
if (!$textTermsColumn) {
$textTermsColumn = new AcademyParameter();
$textTermsColumn->key = 'terms_and_conditions_text';
$textTermsColumn->value = 'Terminos y Condiciones';
$textTermsColumn->type_academy = $type_academy;
$textTermsColumn->save();
}
$textTermsColumn = $textTermsColumn->value;
$academyLocationsService = new AcademyLocationsService;
if (!$academyLocationsService->validateAuthorizedLocations()) {
$authorizedLocations = $academyLocationsService->getAuthorizedLocations();
$franchises = AcademyLocation::select('id', 'name')->where('active', 1)->whereIn('id', $authorizedLocations)->orderBy('name', 'ASC')->get();
} else {
$franchises = AcademyLocation::select('id', 'name')->where('active', 1)->orderBy('name', 'ASC')->get();
}
$status = array();
$status[] = 'AL DÍA';
$status[] = 'EN MORA';
$status[] = 'Vacio';
return view('academy.academy_users')
->with('states', $states)
->with('status', $status)
->with('type_academy', $type_academy)
->with('textTermsColumn', $textTermsColumn)
->with('franchises', $franchises);
}
public function addAcademyUsers($type_academy)
{
$user = Auth::user();
$academyUserLocation = AcademyLocationUser::where('user_id', $user->id)->get();
$academyLocationsService = new AcademyLocationsService;
$enableFranchises = $academyLocationsService->enableFranchises() == 'true';
$academyCategoriesQuery = AcademyCategory::select('academy_categories.id', 'academy_categories.name', 'academy_categories.academy_location_id')
->join('academy_locations', 'academy_locations.id', '=', 'academy_categories.academy_location_id')
->with('academy_location')
->where('academy_locations.type_academy', $type_academy)
->where('academy_categories.active', true);
if ($academyUserLocation->isNotEmpty() && $enableFranchises && !in_array(Auth::user()->rol->id, [self::SUPERADMIN_ROL, self::ADMIN_ROL])) {
$academyCategoriesQuery->whereIn('academy_location_id', $academyUserLocation->pluck('academy_location_id'));
}
$academyCategories = $academyCategoriesQuery->get();
$academyCategoriesSchedule = AcademyCategoriesSchedule::select('id', 'academy_schedule_id', 'academy_category_id')
->whereIn('academy_category_id', $academyCategories->pluck('id'))->get();
$academySchedules = AcademySchedule::select('id', 'name', 'current_capacity')
->where('active', true)
->whereIn('id', $academyCategoriesSchedule->pluck('academy_schedule_id'))
->orderBy('name', 'ASC')->get();
foreach ($academySchedules as $schedule) {
$categories = $academyCategoriesSchedule->where('academy_schedule_id', $schedule->id)->pluck('academy_category_id');
$schedule->categories = $categories;
}
$parameters = AcademyParameter::where('type_academy', $type_academy)->get()->toArray();
$documentsTypes = DocumentType::select('id', 'name')->where('active', 1)->get();
$cities = City::where('active', 1)->orderBy('name', 'ASC')->get();
$statesCountry = State::where('active', 1)->orderBy('name', 'ASC')->get();
$academyDocuments = AcademyDocument::where('active', 1)->where('type_academy', $type_academy)->get();
$discounts = AcademyDiscount::select('id', 'name')->where('active', true)->orderBy('name', 'ASC')->get();
$enrollmentFields = collect($parameters)
->where('key', 'enrollment_fields')
->pluck('value')
->flatMap(fn($value) => explode(',', $value))
->toArray();
$fullEnrollmentFields = collect($parameters)
->where('key', 'full_enrollment_fields')
->pluck('value')
->flatMap(fn($value) => explode(',', $value))
->toArray();
return view('academy.add_academy_users')->with('parameters', $parameters)->with('documentsTypes', $documentsTypes)
->with('cities', $cities)
->with('academyDocuments', $academyDocuments)
->with('academyCategories', $academyCategories)
->with('academySchedules', $academySchedules)
->with('statesCountry', $statesCountry)
->with('type_academy', $type_academy)
->with('discounts', $discounts)
->with('enrollmentFields', $enrollmentFields)
->with('fullEnrollmentFields', $fullEnrollmentFields);
}
public function academyPayments($academy_user_id)
{
$academyUser = AcademyUser::find($academy_user_id);
$corporateIdentity = CorporateIdentityService::get();
return view('academy.payments.list')->with('academyUser', $academyUser)->with('corporateIdentity', $corporateIdentity);
}
public function academyUsersTable(Request $request)
{
$typeAcademy = $request['type_academy'];
$obj = $this->academyUsersTableQuery($request);
DB::enableQueryLog();
$dataTable = DataTables::of($obj);
$dataTable->filterColumn('coaches', function ($query, $keyword) {
$query->havingRaw("GROUP_CONCAT(DISTINCT(CONCAT_WS(' ', users1.first_name, users1.last_name, CONCAT('(',users1.email,')'))) LIKE ?", ['%' . $keyword . '%']);
});
$this->academyUsersTableCustomColumns($dataTable, $typeAcademy);
$response = $dataTable->make(true);
$data = $response->getData();
$data = json_decode(json_encode($data), true);
$queries = DB::getQueryLog();
$newQueries = [];
foreach ($queries as $query) {
if (str_contains($query['query'], 'group by') && str_contains($query['query'], 'academy_users')) {
$newQueries[] = $query;
}
}
$data['queries'] = $newQueries;
return $data;
}
public function indexEditUser($id, $type_academy = 'children')
{
$academyLocationsService = new AcademyLocationsService;
$enableFranchises = $academyLocationsService->enableFranchises() == 'true';
$academyUserLocation = AcademyUser::select('academy_categories.academy_location_id')
->join('academy_categories', 'academy_categories.id', '=', 'academy_users.academy_category_id')
->where('academy_users.id', $id)
->where('academy_categories.active', true)
->get();
$userId = Auth::user()->id;
$userLocations = AcademyLocationUser::where('user_id', $userId)->get();
$academyDocument = new AcademyDocumentController;
$academyCategoriesQuery = AcademyCategory::select('academy_categories.id', 'academy_categories.name', 'academy_categories.academy_location_id')
->join('academy_locations', 'academy_locations.id', '=', 'academy_categories.academy_location_id')
->with('academy_location')
->where('academy_locations.type_academy', $type_academy)
->where('academy_categories.active', true);
if ($academyUserLocation->isNotEmpty() && $enableFranchises && !in_array(Auth::user()->rol->id, [self::SUPERADMIN_ROL, self::ADMIN_ROL])) {
$academyCategoriesQuery->whereIn('academy_location_id', $userLocations->pluck('academy_location_id'));
}
$academyCategories = $academyCategoriesQuery->get();
$allCategories = AcademyCategory::where('active', 1)->pluck('id');
$academySchedules = AcademySchedule::select('id', 'name', 'current_capacity')
->where('active', true)
->orderBy('name', 'ASC')
->get();
foreach ($academySchedules as $schedule) {
$categories = AcademyCategoriesSchedule::where('academy_schedule_id', $schedule->id)
->pluck('academy_category_id');
if ($categories->isEmpty()) {
$schedule->categories = $allCategories;
} else {
$schedule->categories = $categories;
}
}
$states = AcademyState::select('id', 'name')->where('active', 1)->get();
$cities = City::select('id', 'name', 'state_id')->where('active', 1)->orderBy('name', 'ASC')->get();
$statesCountry = State::select('id', 'name')->where('active', 1)->orderBy('name', 'ASC')->get();
$documentsTypes = DocumentType::select('id', 'name')->where('active', 1)->get();
$academyUser = AcademyUser::where('id', $id)->with('user', 'academy_state', 'academy_schedule', 'academy_users_discounts')->first();
$academyDocuments = $academyDocument->getDocuments($id, $academyUser->type_academy, 'no', $showDocumentInProcess = true);
foreach ($academyDocuments as $document) {
if ($document->link)
$document->link = $document->link . ('?_dc=' . Carbon::now()->getTimestamp());
}
$signAcademyDocuments = $academyDocument->getDocuments($id, $academyUser->type_academy, 'yes', $showDocumentInProcess = true);
foreach ($signAcademyDocuments as $document) {
if ($document->link)
$document->link = $document->link . ('?_dc=' . Carbon::now()->getTimestamp());
}
// DIRECCION ESTUDIANTE
$academy_address = Address::where([['id', $academyUser->address_id], ['active', 1]])->with('city')->first();
// DIRECCION RESPONSABLE
$academy_advisor_address = Address::where([['id', $academyUser->advisor_address_id], ['active', 1]])->with('city')->first();
$parameters = AcademyParameter::where('type_academy', $academyUser->type_academy)->get()->toArray();
$academy_purchase = AcademyPurchase::where('academy_user_id', $academyUser->id)
->where('term_type', 'Inscripción')
->orderBy('id', 'DESC')->first();
$multiselectItems = Tag::where('active', true)->get();
$userTags = UserTag::where('user_id', $academyUser->user_id)->where('academy_user_id', $academyUser->id)->pluck('tag_id')->toArray();
$discounts = AcademyDiscount::select('id', 'name')
->with('academy_location_discount')
->where(function ($query) use ($academyUserLocation) {
$query->whereHas('academy_location_discount', function ($subQuery) use ($academyUserLocation) {
$subQuery->whereIn('academy_location_id', $academyUserLocation->pluck('academy_location_id'));
})
->orDoesntHave('academy_location_discount');
})
->where('active', true)
->orderBy('name', 'ASC')
->get();
$academyUsersDiscounts = array();
foreach ($academyUser->academy_users_discounts as $item) {
$academyUsersDiscounts[] = $item->academy_discount_id;
}
$academyUser->discounts = $academyUsersDiscounts;
if (!$academyCategories->where('id', $academyUser->academy_category_id)->first()) {
$academyUser->academy_category_id = null;
}
if (!$academySchedules->where('id', $academyUser->academy_schedule_id)->first()) {
$academyUser->academy_schedule_id = null;
}
$enrollmentFields = collect($parameters)
->where('key', 'enrollment_fields')
->pluck('value')
->flatMap(fn($value) => explode(',', $value))
->toArray();
$fullEnrollmentFields = collect($parameters)
->where('key', 'full_enrollment_fields')
->pluck('value')
->flatMap(fn($value) => explode(',', $value))
->toArray();
$academyPeriodApiController = new AcademyPeriodApiController;
$requestAcademyUserId = new Request(['academyUserId' => $id]);
$academyPeriods = $academyPeriodApiController->getPeriodsByMonthsQuantity($requestAcademyUserId);
return view('academy.edit_academy_user')
->with('academy_user', $academyUser)
->with('parameters', $parameters)
->with('documentsTypes', $documentsTypes)
->with('cities', $cities)
->with('academy_address', $academy_address)
->with('academy_advisor_address', $academy_advisor_address)
->with('academyDocuments', $academyDocuments)
->with('signAcademyDocuments', $signAcademyDocuments)
->with('academyCategories', $academyCategories)
->with('academySchedules', $academySchedules)
->with('states', $states)
->with('academy_purchase', $academy_purchase)
->with('statesCountry', $statesCountry)
->with('multiselectItems', $multiselectItems)
->with('userTags', $userTags)
->with('discounts', $discounts)
->with('enrollmentFields', $enrollmentFields)
->with('fullEnrollmentFields', $fullEnrollmentFields)
->with('academyPeriods', $academyPeriods);
}
public function openPhoto($photo)
{
$pathToFile = config('filesystems.disks.s3.url') . '/academy/' . $photo;
$filename = $photo;
$tempImage = tempnam(sys_get_temp_dir(), $filename);
copy($pathToFile, $tempImage);
return response()->download($tempImage, $filename)->deleteFileAfterSend(true);
}
public function openDocument($document)
{
$pathToFile = config('filesystems.disks.s3.url') . '/academy/' . $document;
$filename = $document;
$tempImage = tempnam(sys_get_temp_dir(), $filename);
copy($pathToFile, $tempImage);
return response()->download($tempImage, $filename)->deleteFileAfterSend(true);
}
public function updateAcademyUser(Request $request)
{
$request->validate([
"type_person" => "required",
'academy_category_id' => 'required',
], [
'academy_category_id.required' => 'Por favor, selecciona una categoría.',
]);
if ($request->has('student_academy_code') && !is_null($request['student_academy_code'])) {
$request->validate([
'student_academy_code' => [
Rule::unique('academy_users', 'student_academy_code')
->ignore($request['id'])
->whereNull('deleted_at')
],
]);
}
// DIRECCION RESPONSABLE
$IdAdvisorAddress = null;
$advisor_academy_address = Address::find($request["advisor_address_id"]);
if ($request["type_person"] == 'NATURAL_PERSON') {
$request["advisor_address"] = $request["advisor_address_natural"];
$request["advisor_address_lat"] = $request["advisor_address_natural_lat"];
$request["advisor_address_lng"] = $request["advisor_address_natural_lng"];
}
if (isset($advisor_academy_address)) {
$advisor_academy_address->direction = $request["advisor_address"];
$advisor_academy_address->lat = $request["advisor_address_lat"];
$advisor_academy_address->long = $request["advisor_address_lng"];
$advisor_academy_address->update();
} else if ($request['advisor_address']) {
$userIdApp = $request["id"];
if (isset($userIdApp)) {
$advisor_academy_address = Address::updateOrCreate(['user_id' => $userIdApp, 'tag' => 'advisor'], [
'direction' => $request['advisor_address'],
'lat' => $request['advisor_address_lat'],
'long' => $request['advisor_address_lng'],
'user_id' => $request["user_id"],
'tag' => 'advisor',
'active' => true
]);
$IdAdvisorAddress = $advisor_academy_address->id;
}
}
// DIRECCION ALUMNO
$IdAddress = null;
$academy_address = Address::find($request["address_id"]);
if (isset($academy_address)) {
$academy_address->direction = $request["address"];
$academy_address->lat = $request["address_lat"];
$academy_address->long = $request["address_lng"];
$academy_address->update();
} else if ($request['address']) {
$userIdApp = $request["id"];
if (isset($userIdApp)) {
$academy_address = Address::updateOrCreate(['user_id' => $userIdApp, 'tag' => 'student'], [
'direction' => $request['address'],
'lat' => $request['address_lat'],
'long' => $request['address_lng'],
'user_id' => $request["user_id"],
'tag' => 'student',
'active' => true
]);
$IdAddress = $academy_address->id;
}
}
$id = $request["id"];
$previousUser = AcademyUser::where('identification', $request['identification'])->first();
if ($previousUser && $previousUser->id != $id) {
return array(
'r' => false,
'data' => $previousUser,
'm' => trans('messages.academy_users.student_already_registered')
);
}
$academy_user = AcademyUser::find($id);
$previousAcademyUser = clone $academy_user;
$previousAcademyUser = json_encode($previousAcademyUser);
$academy_user->student_name = $request["student_name"];
$academy_user->student_last_name = $request["student_last_name"];
$academy_user->student_document_type_id = $request["student_document_type_id"];
$academy_user->identification = $request["identification"];
$academy_user->student_academy_code = $request["student_academy_code"];
$academy_user->school_name = $request["school_name"];
$academy_user->school_grade = $request["school_grade"];
$academy_user->age = $request["age"];
$academy_user->birthdate = $request["birthdate"];
$academy_user->mail = $request["mail"];
$academy_user->phone = $request["phone"];
$academy_user->observations = $request["observation"];
$academy_user->type_person = $request["type_person"];
if ($request["type_person"] == 'NATURAL_PERSON') {
$request->validate([
'advisor_name' => 'required',
'advisor_last_name' => 'required',
'advisor_document_type_id' => 'required',
'advisor_identification' => 'required',
'advisor_relation' => 'required',
'advisor_ocupation' => 'required',
]);
$academy_user->advisor_name = $request["advisor_name"];
$academy_user->advisor_last_name = $request["advisor_last_name"];
$academy_user->advisor_document_type_id = $request["advisor_document_type_id"];
$academy_user->advisor_identification = $request["advisor_identification"];
$academy_user->advisor_relation = $request["advisor_relation"];
$academy_user->advisor_ocupation = $request["advisor_ocupation"];
} else {
$request->validate([
'advisor_nit' => 'required',
'advisor_business_name' => 'required',
'advisor_phone' => 'required',
'advisor_mail' => 'required',
]);
$academy_user->advisor_nit = $request["advisor_nit"];
$academy_user->advisor_business_name = $request["advisor_business_name"];
$academy_user->advisor_property_name = $request["advisor_property_name"];
$academy_user->advisor_isic_code = $request["advisor_isic_code"];
$academy_user->advisor_phone = $request["advisor_phone"];
$academy_user->advisor_mail = $request["advisor_mail"];
$academy_user->advisor_name = $request["advisor_name_juridic"];
$academy_user->advisor_last_name = $request["advisor_last_name_juridic"];
}
$academy_user->last_inscription_year = $request["last_inscription_year"];
if ($academy_user->academy_state_id != $request['academy_state_id']) {
$controllerAcademyState = new AcademyStateController;
$controllerAcademyState->updateAcademyStateUser($id, $request['academy_state_id'], false, Auth::user()->id);
}
$academy_user->academy_state_id = $request['academy_state_id'];
$academy_user->ocupation = $request['ocupation'];
// DIRECCION ALUMNO
$academy_user->address_id = $request["address_id"] ? $request["address_id"] : $IdAddress;
// DIRECCION RESPONSABLE
$academy_user->advisor_address_id = $request["advisor_address_id"] ? $request["advisor_address_id"] : $IdAdvisorAddress;
if (isset($request["blood_type"])) {
$academy_user->blood_type = $request["blood_type"];
$academy_user->eps = $request["eps"];
}
if (isset($request["academy_schedule"]) && $academy_user->academy_schedule_id != $request["academy_schedule"]) {
$status = $this->changeSchedule($id, $request["academy_schedule"]);
$status = json_decode(json_encode($status), true);
if (!$status['original']['r']) {
return response(array('r' => false, 'm' => $status['original']['m']));
}
}
if (isset($request["academy_category_id"])) {
$academy_user->academy_category_id = $request["academy_category_id"];
}
if (isset($request["tshirt_size"])) {
$academy_user->tshirt_size = $request["tshirt_size"];
$academy_user->shorts_size = $request["shorts_size"];
$academy_user->uniform_type = $request["uniform_type"];
}
if (isset($request["guardian_information"])) {
$academy_user->guardian_information = $request["guardian_information"];
}
AcademyUsersDiscount::where('academy_user_id', $id)->delete();
if (isset($request["discounts"]) && $request["discounts"]) {
foreach ($request["discounts"] as $discount) {
$data = new AcademyUsersDiscount();
$data->academy_discount_id = $discount;
$data->academy_user_id = $id;
$data->save();
}
}
$academy_user->academy_period_id = $request["academy_period_id"];
$academy_user->update();
$this->validateAcademyLocation($id);
UserTag::where('user_id', $academy_user->user_id)
->where('academy_user_id', $academy_user->id)
->delete();
if (isset($request["tags"]) && $request["tags"]) {
foreach ($request["tags"] as $tagId) {
UserTag::updateOrCreate(
['tag_id' => $tagId, 'user_id' => $academy_user->user_id, 'academy_user_id' => $academy_user->id],
['tag_id' => $tagId, 'user_id' => $academy_user->user_id, 'academy_user_id' => $academy_user->id]
);
}
}
if ($request["validation_payment"] == 2) {
$price_academy_monthl = Parameter::first();
$price = $price_academy_monthl->academy_monthly_value;
$this->saveMonthlyPaymentValue($request, $price);
}
// Se guarda el estado de error documentos
$documentErrorState = AcademyState::where('name', 'LIKE', '%Error%')->value('id');
if (isset($request["message"]) && $request["message"] != null && $request['academy_state_id'] == $documentErrorState) {
$message = $request["message"];
$when_send = Carbon::parse($request["when_send"])->toDateTimeString();
$this->notification($message, $id, $when_send);
}
// Se guarda el estado de pagar inscripción
$payEnrollmentState = AcademyState::find(5)->id;
if ($request['academy_state_id'] == $payEnrollmentState) {
$academyPurchaseController = new AcademyPurchaseController;
$academyPurchaseController->validateStudentsWithoutRegistrationPaymentGenerated($academy_user->id);
}
$logObj = array(
'previousAcademyUser' => json_decode($previousAcademyUser, true),
'newAcademyUser' => $academy_user
);
$module = Module::where('route', 'academy')->first();
$this->registerLog(Auth::user()->id, 'Editar Alumno', json_encode($logObj), "Update", $module->id);
return array('r' => true, 'd' => array('id' => $id), 'm' => trans('messages.academy_users.tag30'));
}
public function saveAcademyStudentDocument(Request $request)
{
$academyUserId = $request['userId'];
$inputAmount = intval($request['inputAmount']);
for ($i = 0; $i < $inputAmount; $i++) {
$file = $request['file' . $i];
if (!$file) {
continue;
} else {
$academyDocumentId = $request['documentId' . $i];
$extension = $request->file('file' . $i)->getClientOriginalExtension();
$filenametostore = 'document' . $academyUserId . '_' . $academyDocumentId . '.' . $extension;
if (Storage::disk('s3')->exists(config('s3.academy_documents') . $filenametostore)) {
Storage::disk('s3')->delete(config('s3.academy_documents') . $filenametostore);
}
Storage::disk('s3')->put(config('s3.academy_documents') . $filenametostore, fopen($request->file('file' . $i), 'r+'), 'public');
$url = config('filesystems.disks.s3.url') . '/academy/academy_documents/' . $filenametostore;
$data = [
'academy_user_id' => $academyUserId,
'academy_document_id' => $academyDocumentId,
'link' => $url,
];
AcademyDocumentUser::updateOrCreate(
['academy_user_id' => $academyUserId, 'academy_document_id' => $academyDocumentId],
$data
);
}
}
return array('r' => true, 'data' => null, 'm' => __('messages.academy_document.document_uploaded_successfully'));
}
public function saveAcademyUser(Request $request)
{
$userId = Auth::user()->id;
$request->validate([
"type_person" => "required"
]);
if ($request["type_person"] == 'NATURAL_PERSON') {
$request["advisor_address"] = $request["advisor_address_natural"];
$request["advisor_address_lat"] = $request["advisor_address_natural_lat"];
$request["advisor_address_lng"] = $request["advisor_address_natural_lng"];
}
$previousUser = AcademyUser::where('identification', $request['identification'])->first();
if ($previousUser) {
return array(
'r' => false,
'data' => $previousUser,
'm' => trans('messages.academy_users.student_already_registered')
);
}
$academy_user = new AcademyUser();
$academy_user->student_name = $request["student_name"];
$academy_user->student_last_name = $request["student_last_name"];
$academy_user->student_document_type_id = $request["student_document_type_id"];
$academy_user->identification = $request["identification"];
$academy_user->school_name = $request["school_name"];
$academy_user->school_grade = $request["school_grade"];
$academy_user->age = $request["age"];
$academy_user->birthdate = $request["birthdate"];
$academy_user->mail = $request["mail"];
$academy_user->phone = $request["phone"];
$academy_user->last_inscription_year = $request["last_inscription_year"];
$academy_user->type_academy = $request["type_academy"];
$academy_user->type_person = $request["type_person"];
$academy_user->user_id = $userId;
// DIRECCION ESTUDIANTE
$address = $this->addressService->create(
$userId,
$request["address"],
$request["address_lat"],
$request["address_lng"],
'student'
);
$academy_user->address_id = $address->id;
$academy_user->observations = $request["observation"];
if ($request["type_person"] == 'NATURAL_PERSON') {
$request->validate([
'advisor_name' => 'required',
'advisor_last_name' => 'required',
'advisor_document_type_id' => 'required',
'advisor_identification' => 'required',
'advisor_relation' => 'required',
'advisor_ocupation' => 'required',
]);
$academy_user->advisor_name = $request["advisor_name"];
$academy_user->advisor_last_name = $request["advisor_last_name"];
$academy_user->advisor_document_type_id = $request["advisor_document_type_id"];
$academy_user->advisor_identification = $request["advisor_identification"];
$academy_user->advisor_relation = $request["advisor_relation"];
$academy_user->advisor_ocupation = $request["advisor_ocupation"];
// DIRECCION RESPONSABLE
$advisor_address = $this->addressService->create(
Auth::user()->id,
$request["advisor_address"],
$request["advisor_address_lat"],
$request["advisor_address_lng"],
'advisor'
);
$academy_user->advisor_address_id = $advisor_address->id;
} else {
$request->validate([
'advisor_nit' => 'required',
'advisor_business_name' => 'required',
'advisor_property_name' => 'required',
'advisor_isic_code' => 'required',
'advisor_phone' => 'required',
'advisor_mail' => 'required',
]);
$academy_user->advisor_nit = $request["advisor_nit"];
$academy_user->advisor_business_name = $request["advisor_business_name"];
$academy_user->advisor_property_name = $request["advisor_property_name"];
$academy_user->advisor_isic_code = $request["advisor_isic_code"];
$academy_user->advisor_phone = $request["advisor_phone"];
$academy_user->advisor_mail = $request["advisor_mail"];
$academy_user->advisor_name = $request["advisor_name_juridic"];
$academy_user->advisor_last_name = $request["advisor_last_name_juridic"];
// DIRECCION RESPONSABLE
$advisor_address = $this->addressService->create(
Auth::user()->id,
$request["advisor_address"],
$request["advisor_address_lat"],
$request["advisor_address_lng"],
'advisor'
);
$academy_user->advisor_address_id = $advisor_address->id;
}
$academy_user->ocupation = $request["advisor_ocupation"];
$academy_user->date = $request["date"];
if (isset($request["blood_type"])) {
$academy_user->blood_type = $request["blood_type"];
$academy_user->eps = $request["eps"];
}
if (isset($request["academy_category_id"])) {
$academy_user->academy_category_id = $request["academy_category_id"];
}
if (isset($request["tshirt_size"])) {
$academy_user->tshirt_size = $request["tshirt_size"];
$academy_user->shorts_size = $request["shorts_size"];
$academy_user->uniform_type = $request["uniform_type"];
}
if (isset($request["guardian_information"])) {
$academy_user->guardian_information = $request["guardian_information"];
}
$academy_user->save();
$this->academyUserService->generateCode($academy_user);
$academyState = new AcademyStateController();
$academyState->assignStateAcademy($academy_user);
if (isset($request["academy_schedule"])) {
$status = $this->changeSchedule($academy_user->id, $request["academy_schedule"]);
$status = json_decode(json_encode($status), true);
if (!$status['original']['r']) {
return array('r' => false, 'm' => $status['original']['m']);
}
}
$module = Module::where('route', 'academy')->first();
$this->registerLog(Auth::user()->id, 'Crear Alumno', json_encode($academy_user), "Create", $module->id);
return array('r' => true, 'd' => array('id' => $academy_user->id), 'm' => trans('messages.academy_users.tag57'));
}
public function saveMonthlyPaymentValue($request, $price)
{
$academy_user = AcademyPurchase::where('id', $request["id"])->update([
'monthly_academy_price' => $price
]);
return response()->json($academy_user, 200);
}
public function deleteAcademyUser(Request $request)
{
try {
$academyUser = AcademyUser::find($request['user_id']);
if (AcademyUser::where('id', $request['user_id'])->delete()) {
$module = Module::where('route', 'academy')->first();
$this->registerLog(Auth::user()->id, 'Eliminar Alumno', json_encode($academyUser), "Delete", $module->id);
return response(array("r" => true, "type" => "success", "title" => "", "m" => __('messages.academy_users.tag33'), "data" => null));
} else {
return response(array("r" => false, "type" => "error", "title" => "Oops...", "m" => __('messages.error_removing'), "data" => null));
}
} catch (\Illuminate\Database\QueryException $e) {
return response(array("r" => false, "type" => "error", "title" => "Oops...", "m" => __('messages.delete_relation_data'), "data" => null));
}
}
public function activate(Request $request)
{
try {
$id = $request['id'];
$state = $request['state'];
$academyUser = AcademyUser::find($id);
$academyUser->active = $state;
$academyUser->update();
return array('r' => true, 'd' => $academyUser, 'm' => __('messages.updated_successfully'));
} catch (\Throwable $th) {
return array('r' => false, 'd' => null, 'm' => __('messages.error_updating'));
}
}
public function assignAppUserToStudent(Request $request)
{
try {
$newUserId = $request['id'];
$academyUserId = $request['academy_userId'];
$address = Address::where([['user_id', $newUserId], ['active', 1]])->first();
$addressId = null;
if ($address) {
$addressId = $address->id;
}
$academyUser = AcademyUser::find($academyUserId);
$previousUser = $academyUser->user_id;
$academyUser->user_id = $newUserId;
$academyUser->address_id = $addressId;
$academyUser->update();
AcademyPurchase::where('academy_user_id', $academyUserId)->update(['user_id' => $newUserId]);
AcademyStateUser::where([['academy_user_id', $academyUserId], ['approving_user_id', $previousUser]])->update(['approving_user_id' => $newUserId]);
$logObj = array(
'previousUser' => $previousUser,
'newUser' => $newUserId,
'academyUser' => $academyUser
);
$this->registerLog(Auth::user()->id, 'Asignar contacto app', json_encode($logObj), "Update", 5);
return array('r' => true, 'd' => $academyUser, 'm' => __('messages.updated_successfully'));
} catch (\Throwable $th) {
return array('r' => false, 'd' => null, 'm' => $th->getMessage());
}
}
public function validateExport(Request $request)
{
if ($request['query']) {
$results = $this->util->getGenericData($request["query"], $request["bindings"]);
if (count($results) > 0) {
$name = 'ReporteAcademiaMonitor' . time() . '.xlsx';
Excel::store(new AcademyUsersExport($results, $request["type_academy"]), $name, 'public');
return response()->json(['success' => true, 'message' => 'Validación OK', 'data' => $name]);
}
}
return response()->json(['success' => false, 'message' => 'No existen datos a exportar']);
}
public function export($name)
{
return $this->util->export($name);
}
public function exportTemplate()
{
return Excel::download(new AcademyTemplate, trans('messages.academy_users.tag67') . '.xlsx');
}
public function importAcademyUsers(Request $request)
{
try {
$file = new AcademyImport;
$file->request = $request;
Excel::import($file, $request->importAcademyStudents);
return array('r' => true, 'd' => $file->messages, 'm' => trans('messages.clients.import_success') . ' ' . $file->edit['creados'] . ' horario/s asignado/s,');
} catch (\Exception $e) {
$error = [
'function' => 'importAcademyUsers',
'request' => $request->all(),
'message' => $e->getMessage(),
'getFile' => $e->getFile(),
'getLine' => $e->getLine(),
];
$this->util->logFile(json_encode($error));
return array('r' => false, 'm' => trans('messages.clients.import_error') . "'" . $request->importAcademyStudents->getClientOriginalName() . "', el archivo no cumple con el formato requerido.");
}
}
public function getCity(Request $request)
{
$state_id = $request['state_id'];
$user_id = $request['user_id'];
$searchCity = City::select('id', 'name', 'state_id')->where([['state_id', $state_id], ['active', 1]])->orderBy('name', 'ASC')->get();
if ($searchCity) {
return array('r' => true, 'd' => $searchCity, 'user' => $user_id);
} else {
return array('r' => false, 'd' => null, 'm' => 'Ciudades no encontradas en el departamento seleccionado');
}
}
public function validateCityAndDepartment(Request $request)
{
$citie_id = $request['citie_id'];
$state_id = $request['state_id'];
$select_cities = City::select('id', 'name', 'state_id')->where([['id', $citie_id], ['state_id', $state_id], ['active', 1]])->orderBy('name', 'ASC')->first();
if ($select_cities) {
return array('r' => true, 'd' => $select_cities, 'state' => $state_id);
} else {
return array('r' => false, 'd' => null, 'm' => 'La ciudad seleccionada no pertenece al departamento');
}
}
public function exportScheduleTemplate()
{
return Excel::download(new StudentScheduleTemplate, trans('messages.academy_users.tag67') . '.xlsx');
}
public function importSchedule(Request $request)
{
try {
$file = new StudentScheduleImport;
$file->request = $request;
Excel::import($file, $request->importStudentSchedule);
return array('r' => true, 'd' => $file->messages, 'm' => trans('messages.clients.import_success') . ' ' . $file->edit['creados'] . ' alumno/s creado/s,');
} catch (\Exception $e) {
$error = [
'function' => 'importSchedule',
'request' => $request->all(),
'message' => $e->getMessage(),
'getFile' => $e->getFile(),
'getLine' => $e->getLine(),
];
$this->util->logFile(json_encode($error));
return array('r' => false, 'm' => trans('messages.clients.import_error') . "'" . $request->importStudentSchedule->getClientOriginalName() . "', el archivo no cumple con el formato requerido.");
}
}
public function changeSchedule($academyUserId, $academyScheduleId)
{
$controller = new AcademyScheduleController();
$status = $controller->validateScheduleAvailability($academyScheduleId);
$status = json_decode(json_encode($status), true);
if (!$status['original']['r']) {
return response(array('r' => false, 'm' => $status['original']['m']));
}
AcademyUser::where('id', $academyUserId)->update(['academy_schedule_id' => $academyScheduleId]);
$controller->adjustAvailableSlotsSchedules();
return response(array('r' => true, 'm' => 'Cambio de horario modificado'));
}
public function validatePaymentStatus($academyUserId = null)
{
// Proceso encargado de validar estado de pago
AcademyUser::join('academy_states', 'academy_states.id', '=', 'academy_users.academy_state_id')
->where(function ($q) {
$q->where('academy_states.app_components', 'LIKE', '%Botón pagos%')
->orWhere('academy_states.app_components', 'LIKE', '%Botón carné%')
->orWhere('academy_states.app_components', 'LIKE', '%Ocultar botones%');
})
->where(function ($query) use ($academyUserId) {
if ($academyUserId) {
$query->where('academy_users.id', $academyUserId);
}
})
->whereNull('academy_users.deleted_at')->whereNotNull('academy_period_id')
->update(['payment_status' => 'AL DÍA']);
AcademyUser::join('academy_states', 'academy_states.id', '=', 'academy_users.academy_state_id')
->join('academy_purchases', 'academy_purchases.academy_user_id', '=', 'academy_users.id')
->leftjoin('payment_transactions', 'payment_transactions.id', '=', 'academy_purchases.payment_transaction_id')
->where([
['academy_states.validate_payments', 1],
['academy_purchases.state', 1]
])
->where(function ($q) {
$q->whereNotIn('payment_transactions.state', ['CONFIRMED'])->orWhereNull('payment_transactions.id');
})
->where(function ($query) use ($academyUserId) {
if ($academyUserId) {
$query->where('academy_users.id', $academyUserId);
}
})
->whereNull('academy_users.deleted_at')->whereNull('academy_purchases.deleted_at')->whereNotNull('academy_period_id')
->update(['payment_status' => 'EN MORA']);
}
public function validateReviewState($academyUserId = null)
{
// Proceso encargado de validar estado de revisión
AcademyUser::leftjoin('academy_states', 'academy_states.id', '=', 'academy_users.academy_state_id')
->where([
['academy_states.app_components', 'LIKE', '%Botón carné%']
])
->where(function ($query) use ($academyUserId) {
if ($academyUserId) {
$query->where('academy_users.id', $academyUserId);
}
})
->whereNull('academy_users.deleted_at')
->update(['review_state' => 'CONFIRMADA']);
}
public function validateRegisteredUsers($academyUserId = null)
{
// Se inactivan todos los niños para que inicien el proceso de renovación
AcademyUser::whereNull('deleted_at')->update(['active' => false]);
// Proceso encargado de validar los niños con pagos ya confirmados de mensualidad
$year = date('Y');
AcademyUser::join('academy_states', 'academy_states.id', '=', 'academy_users.academy_state_id')
->join('academy_purchases', 'academy_purchases.academy_user_id', '=', 'academy_users.id')
->leftjoin('payment_transactions', 'payment_transactions.id', '=', 'academy_purchases.payment_transaction_id')
->where([
['academy_states.validate_payments', 1],
['academy_purchases.term', 'LIKE', '%' . $year . '%']
])
->whereNull('academy_purchases.enrollment_academy_price')
->where(function ($q) {
$q->whereIn('payment_transactions.state', ['CONFIRMED']);
})
->where(function ($query) use ($academyUserId) {
if ($academyUserId) {
$query->where('academy_users.id', $academyUserId);
}
})
->whereNull('academy_users.deleted_at')->whereNull('academy_purchases.deleted_at')
->update(['academy_users.active' => true]);
}
// Envia email a un usuario si se encuentra matriculado y no tiene pagos pendientes
public function notificateEmailRegistrationRenewal($academyUserId = null, $type_academy = 'children')
{
$enableSendEmailRegistrationRenewal = AcademyParameter::where(['key' => 'enable_send_email_registration_renewal', 'value' => 'true', 'type_academy' => $type_academy])->first();
if (!$enableSendEmailRegistrationRenewal) {
return;
}
$year = date('Y');
$tag = Tag::where('name', '=', 'Paz_y_salvo_academia_' . $year)->first();
if (!$tag) {
$tag = Tag::create(['name' => 'Paz_y_salvo_academia_' . $year]);
}
$usersToNotificate = AcademyUser::select('academy_users.id AS academy_user_id', 'academy_users.user_id AS id', 'academy_users.student_name', 'academy_users.mail', 'payment_transactions.state')
->distinct()
->join('academy_purchases', 'academy_purchases.academy_user_id', '=', 'academy_users.id')
->leftJoin('payment_transactions', 'payment_transactions.id', '=', 'academy_purchases.payment_transaction_id')
->leftJoin('user_tags', 'user_tags.user_id', '=', 'academy_users.id')
->leftjoin('tags', function ($join) {
$join->on('tags.id', '=', 'user_tags.tag_id')->where('tags.active', 1);
})
->leftJoin('academy_states', 'academy_states.id', '=', 'academy_users.academy_state_id')
->where('payment_transactions.state', '=', 'CONFIRMED')
->where('type_academy', '=', $type_academy)
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('user_tags as ut')
->whereColumn('ut.academy_user_id', 'academy_users.id');
})
->whereNotExists(function ($query) use ($tag) {
$query->select(DB::raw(1))
->from('user_tags as ut')
->whereColumn('ut.academy_user_id', 'academy_users.id')
->where('ut.tag_id', $tag);
})
->where('academy_states.validate_payments', '=', 1)
->whereNotIn('academy_users.id', function ($query) {
$query->select('academy_users.id')
->from('academy_users')
->join('academy_purchases', 'academy_purchases.academy_user_id', '=', 'academy_users.id')
->leftJoin('payment_transactions', 'payment_transactions.id', '=', 'academy_purchases.payment_transaction_id')
->where(function ($query) {
$query->where('payment_transactions.state', '!=', 'CONFIRMED')
->orWhereNull('payment_transactions.state');
})
->whereNull('academy_purchases.deleted_at')
->distinct();
})
->whereNull('academy_purchases.deleted_at');
if ($academyUserId) {
$usersToNotificate->where('academy_users.id', '=', $academyUserId);
}
$usersToNotificate = $usersToNotificate->distinct()->get();
foreach ($usersToNotificate as $userTN) {
UserTag::updateOrCreate(
['tag_id' => $tag->id, 'user_id' => $userTN->id, 'academy_user_id' => $userTN->academy_user_id],
['tag_id' => $tag->id, 'user_id' => $userTN->id, 'academy_user_id' => $userTN->academy_user_id]
);
try {
Mail::to($userTN->mail)->send(new RegistrationRenewalNotificationMail($userTN->student_name));
} catch (\Throwable $e) {
$error = [
'function' => 'notificateEmailRegistrationRenewal',
'user' => $userTN,
'message' => $e->getMessage(),
'getFile' => $e->getFile(),
'getLine' => $e->getLine(),
];
$this->util->logFile(json_encode($error));
return array('s' => false, 'm' => 'Mail no enviado');
}
}
}
public function notification($message, $academyUserId, $when_send)
{
$academyUser = AcademyUser::with('user')->where('id', $academyUserId)->first();
if ($academyUser && $academyUser->user && $academyUser->user->pns_id) {
$notificationsController = new NotificationsController;
$notificationsController->createSystemNotification($message, $academyUser->user->id, $when_send);
}
}
public function validateActive($academyUserId = null)
{
$year = date('Y');
// Proceso encargado de validar estado activo
AcademyUser::join('academy_states', 'academy_states.id', '=', 'academy_users.academy_state_id')->where('academy_states.validate_payments', 1)
->join('academy_purchases', 'academy_purchases.academy_user_id', '=', 'academy_users.id')->whereNotNull('academy_purchases.enrollment_academy_price')->where('academy_purchases.term', $year)
->join('payment_transactions', 'payment_transactions.id', '=', 'academy_purchases.payment_transaction_id')->where('payment_transactions.state', 'CONFIRMED')
->where(function ($query) use ($academyUserId) {
if ($academyUserId) {
$query->where('academy_users.id', $academyUserId);
}
})
->whereNull('academy_users.deleted_at')->whereNull('academy_purchases.deleted_at')->where('academy_users.active', 0)
->update(['academy_users.active' => 1]);
}
public function validateAcademyLocation($academyUserId = null)
{
// Proceso encargado de validar la sede del niño
AcademyUser::join(DB::raw('(SELECT au.id, ac.academy_location_id, al.name AS academy_place
FROM academy_users au
JOIN academy_categories ac ON ac.id = au.academy_category_id
JOIN academy_locations al ON al.id = ac.academy_location_id
WHERE au.deleted_at IS NULL'
. ($academyUserId ? ' AND au.id = ' . intval($academyUserId) : '') . '
GROUP BY au.id) ob1'), 'ob1.id', '=', 'academy_users.id')
->update([
'academy_users.academy_location_id' => DB::raw('ob1.academy_location_id'),
'academy_users.academy_place' => DB::raw('ob1.academy_place'),
]);
}
public function exportAcademyTagsTemplate()
{
return Excel::download(new AcademyTagsTemplate, trans('messages.academy_users.tag67') . '.xlsx');
}
public function importAcademyTags(Request $request)
{
try {
$file = new AcademyTagsImport;
$file->request = $request;
Excel::import($file, $request->importAcademyTags);
return array('r' => true, 'd' => $file->messages, 'm' => trans('messages.clients.import_success') . ' ' . $file->edit['editados'] . ' alumno/s actualizado/s,');
} catch (\Exception $e) {
$error = [
'function' => 'importAcademyTags',
'request' => $request->all(),
'message' => $e->getMessage(),
'getFile' => $e->getFile(),
'getLine' => $e->getLine(),
];
$this->util->logFile(json_encode($error));
return array('r' => false, 'm' => trans('messages.clients.import_error') . "'" . $request->importAcademyTags->getClientOriginalName() . "', el archivo no cumple con el formato requerido.");
}
}
private function academyUsersTableQuery($request)
{
$states = $request->states;
$status = $request->status;
$from_date = $request->from_date;
$to_date = $request->to_date;
$type_academy = $request->type_academy;
DB::statement("SET sql_mode = ''");
$query = DB::table('academy_users')
->select([
'academy_users.id',
'academy_users.student_name',
'academy_users.student_last_name',
'academy_users.photo',
'document_types.name AS studentDocumentType',
'document_types.alias AS aliasStudentDocumentType',
'academy_users.identification',
'academy_users.student_academy_code',
'academy_users.school_name',
'academy_users.school_grade',
'academy_users.birthdate',
'academy_users.age',
'academy_users.mail',
'academy_users.phone',
'addresses_student.direction AS direction_student',
'appUser.id AS academy_user_id',
'appUser.first_name AS academy_users.user.first_name',
'appUser.last_name AS academy_users.user.last_name',
'appUser.email AS academy_users.user.email',
'document_types_contact.name AS contactDocumentType',
'appUser.document AS academy_users.user.document',
'academy_users.advisor_name',
'academy_users.advisor_last_name',
'document_types_advisor.name AS advisorDocumentType',
'academy_users.advisor_identification',
'academy_users.advisor_relation',
'academy_users.advisor_ocupation',
'academy_users.blood_type',
'academy_users.eps',
'academy_users.academy_place',
'academy_locations.name AS locationName',
'academy_categories.name AS categoryName',
'academy_users.last_inscription_year',
DB::raw('GROUP_CONCAT(DISTINCT(CONCAT(academy_discounts.name, " (", academy_discounts.discount, "%)"))) AS discounts'),
'academy_periods.name AS periodName',
'academy_periods.active AS periodActive',
'academy_schedules.name AS scheduleName',
'academy_users.tshirt_size',
'academy_users.uniform_type',
DB::raw('GROUP_CONCAT(DISTINCT(tags.name)) AS segmentation'),
'academy_state.name AS academy_state.name',
'academy_state.color AS academy_state.color',
'academy_state.id AS academyState',
'academy_users.created_at',
'academy_users.review_state',
'academy_users.payment_status',
'academy_users.active',
DB::raw("GROUP_CONCAT(DISTINCT(CONCAT_WS(' ', users1.first_name, users1.last_name, CONCAT('(',users1.email,')'))) ORDER BY users1.id ASC) AS coaches"),
'academy_users.type_person',
'advisor_nit',
'advisor_business_name',
'advisor_property_name',
'advisor_isic_code',
'advisor_phone',
'addresses_advisor.direction AS direction_advisor',
'advisor_cell_phone',
'advisor_mail',
'academy_users.observations',
'academy_users.user_id as user_id',
'appUser.deleted_at as user_deleted_at',
])
->join('document_types', 'document_types.id', '=', 'academy_users.student_document_type_id')
->leftJoin('document_types AS document_types_advisor', 'document_types_advisor.id', '=', 'academy_users.advisor_document_type_id')
->leftJoin('addresses AS addresses_student', 'addresses_student.id', '=', 'academy_users.address_id')
->leftJoin('addresses AS addresses_advisor', 'addresses_advisor.id', '=', 'academy_users.advisor_address_id')
->join('academy_states AS academy_state', 'academy_state.id', '=', 'academy_users.academy_state_id')
->leftJoin('academy_categories', function ($join) {
$join->on('academy_categories.id', '=', 'academy_users.academy_category_id');
// ->where('academy_categories.active', true);
})
->leftJoin('academy_categories_schedules', 'academy_categories_schedules.academy_category_id', '=', 'academy_categories.id')
->leftJoin('academy_schedules', function ($join) {
$join->on('academy_schedules.id', '=', 'academy_users.academy_schedule_id')
->where('academy_schedules.active', true);
})
->leftJoin('academy_schedules_coaches', 'academy_schedules_coaches.academy_schedule_id', '=', 'academy_schedules.id')
->leftJoin('users AS users1', function ($join) {
$join->on('users1.id', '=', 'academy_schedules_coaches.user_id')->whereNull('users1.deleted_at');
})
->leftJoin('academy_locations', function ($join) {
$join->on('academy_locations.id', '=', 'academy_categories.academy_location_id');
// ->where('academy_locations.active', true);
})
->leftJoin('academy_users_discounts', 'academy_users_discounts.academy_user_id', '=', 'academy_users.id')
->leftJoin('academy_discounts', function ($join) {
$join->on('academy_discounts.id', '=', 'academy_users_discounts.academy_discount_id');
// ->where('academy_discounts.active', true);
})
->leftJoin('academy_periods', function ($join) {
$join->on('academy_periods.id', '=', 'academy_users.academy_period_id');
// ->where('academy_periods.active', true);
})
->leftJoin('users AS appUser', 'appUser.id', '=', 'academy_users.user_id')
->leftJoin('document_types AS document_types_contact', 'document_types_contact.id', '=', 'appUser.document_type_id')
->leftJoin('user_tags', 'academy_users.id', '=', 'user_tags.academy_user_id')
->leftjoin('tags', function ($join) {
$join->on('tags.id', '=', 'user_tags.tag_id')->where('tags.active', 1);
})
->whereNull('academy_users.deleted_at')
->where('academy_users.type_academy', $type_academy)
->groupBy('academy_users.id');
if ($states && $states != 'null') {
$query->whereIn('academy_users.academy_state_id', $states);
}
if ($status && $status != 'null') {
$options = $status;
$status = array();
$emptyOption = false;
foreach ($options as $option) {
if ($option == 'Vacio') {
$query->whereNull('academy_users.payment_status');
$emptyOption = true;
} else
$status[] = $option;
}
if ($emptyOption) {
$query->where(function ($q) use ($status) {
$q->whereNull('academy_users.payment_status')
->orWhereIn('academy_users.payment_status', $status);
});
} else {
$query->whereIn('academy_users.payment_status', $status);
}
}
if ($from_date && $from_date != 'null') {
$query->whereDate('academy_users.created_at', '>=', $from_date);
}
if ($to_date && $to_date != 'null') {
$query->whereDate('academy_users.created_at', '<=', $to_date);
}
//new filters
$academyLocationsService = new AcademyLocationsService;
if (!$academyLocationsService->validateAuthorizedLocations()) {
$authorizedLocations = $academyLocationsService->getAuthorizedLocations();
$query->whereIn('academy_locations.id', $authorizedLocations);
}
if ($request['categories'] || $request['locations']) {
$query->join('academy_categories_schedules as acs_filter', 'acs_filter.academy_schedule_id', '=', 'academy_schedules.id');
if ($request['locations']) {
$query->join('academy_categories as ac_filter_category', 'ac_filter_category.id', '=', 'acs_filter.academy_category_id')
->whereIn('ac_filter_category.academy_location_id', $request['locations'])
->whereIn('academy_categories.academy_location_id', $request['locations']);
}
if ($request['categories']) {
$query->whereIn('acs_filter.academy_category_id', $request['categories'])
->whereIn('academy_categories_schedules.academy_category_id', $request['categories']);
}
}
if ($request['schedules']) {
$query->join('academy_schedules_coaches as asc_filter', 'asc_filter.academy_schedule_id', '=', 'academy_schedules.id')
->whereIn('asc_filter.academy_schedule_id', $request['schedules']);
}
if ($request['coaches']) {
$query->join('academy_schedules_coaches as asc_main', 'asc_main.academy_schedule_id', '=', 'academy_schedules.id')
->whereIn('asc_main.user_id', $request['coaches']);
}
//end new filters
DB::statement("SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
return $query;
}
private function academyUsersTableCustomColumns($dataTable, $typeAcademy)
{
$dataTable->addColumn('actions', function ($academyUsers) use ($typeAcademy) {
return $this->academyUsersTableActionsColumn($academyUsers, $typeAcademy);
})
->editColumn('active', function ($academyUsers) {
return $this->academyUsersTableActiveColumn($academyUsers);
})
->editColumn('created_at', function ($academyUsers) {
return Carbon::parse($academyUsers->created_at)->format(self::DATETIME_FORMAT);
})
->editColumn('photo', function ($academyUsers) {
if (!$academyUsers->photo) {
return $this->util->generateEmptyImageColumn();
} else {
return $this->util->generateImageColumnRound(
$academyUsers->photo,
$academyUsers->student_name,
'student' . $academyUsers->id
);
}
})
->addColumn('last_payment_date', function ($academyUsers) {
$lastPaymentDate = PaymentTransaction::select(
DB::raw('MAX(payment_transactions.payment_date) AS last_payment_date')
)
->join('academy_purchases', 'academy_purchases.payment_transaction_id', '=', 'payment_transactions.id')
->join('academy_users', 'academy_users.id', '=', 'academy_purchases.academy_user_id')
->where('academy_users.id', $academyUsers->id)
->first();
return $lastPaymentDate && $lastPaymentDate->last_payment_date ?
Carbon::parse($lastPaymentDate->last_payment_date)->format(self::DATETIME_FORMAT) : '';
})
->addColumn('last_status_date', function ($academyUsers) {
$lastStatusDate = AcademyStateUser::select(
DB::raw('MAX(academy_state_users.updated_at) AS last_status_date')
)
->where('academy_user_id', $academyUsers->id)
->first();
return $lastStatusDate && $lastStatusDate->last_status_date ?
Carbon::parse($lastStatusDate->last_status_date)->format(self::DATETIME_FORMAT) : '';
})
->addColumn('terms_conditions', function ($academyUsers) {
$data = User::select(DB::raw('GROUP_CONCAT(DISTINCT(terms.version)) AS terms_conditions'))
->leftjoin('term_clients', 'term_clients.user_id', '=', 'users.id')
->leftjoin('terms', 'terms.id', '=', 'term_clients.term_id')
->where('users.id', $academyUsers->user_id)
->first();
return $data->terms_conditions;
})
->addColumn('academy_documents1', function ($academyUsers) {
$data = AcademyUser::select(DB::raw('GROUP_CONCAT(DISTINCT(academy_documents.name)) AS academy_documents1'))
->leftJoin('academy_document_users', 'academy_document_users.academy_user_id', '=', 'academy_users.id')
->leftJoin('academy_documents', 'academy_documents.id', '=', 'academy_document_users.academy_document_id')
->where('academy_users.id', $academyUsers->id)
->first();
return $data->academy_documents1;
})
->addColumn('academy_reports', function ($academyUsers) {
$data = AcademyUser::select(DB::raw('GROUP_CONCAT(DISTINCT(academy_reports.period)) AS academy_reports'))
->leftJoin('academy_reports', 'academy_reports.academy_user_id', '=', 'academy_users.id')
->where('academy_users.id', $academyUsers->id)
->first();
return $data->academy_reports;
})
->rawColumns(['actions', 'active', 'created_at', 'photo', 'last_payment_date', 'last_status_date', 'terms_conditions', 'academy_documents1', 'academy_reports']);
}
private function academyUsersTableActionsColumn($academyUsers, $typeAcademy)
{
if (
$this->validateRolePermissions('academy', 'administrator') ||
$this->validateRolePermissions('academy', 'supervisor')
) {
$actions = '<i class="fa fa-pencil iconMini" onClick="clickEditUser(' . $academyUsers->id . ', \'' . $typeAcademy . '\')")" data-id="' . $academyUsers->id . ' " data-type-academy="' . $typeAcademy . '" title="Editar"></i>
<i class="fa fa-trash iconMini" onClick="clickDeleteUser(' . $academyUsers->id . ')" data-id="' . $academyUsers->id . '" title="Eliminar"></i>';
$payments = AcademyPurchase::where('academy_user_id', $academyUsers->id)->first();
if ($payments) {
$actions .= '<i class="fa fa-list iconMini" onClick="getUserPayments(' . $academyUsers->id . ')" data-id="' . $academyUsers->id . '" title="Pagos"></i>';
}
$reports = AcademyReport::where('academy_user_id', $academyUsers->id)->first();
if ($reports) {
$actions .= '<i class="fa fa-file-pdf-o iconMini" onClick="viewReport(' . $academyUsers->id . ')" data-id="' . $academyUsers->id . '" title="Ver informes"></i>';
}
if ($academyUsers->active) {
$carnets = $this->carnetService->getCarnets(new Request(), 'academy', $academyUsers->academy_user_id, $academyUsers->id);
if (count($carnets) > 0) {
$student_name = "'$academyUsers->student_name'";
$student_last_name = "'$academyUsers->student_last_name'";
$academy_place = "'$academyUsers->locationName'";
$blood_type = "'$academyUsers->blood_type'";
$studentDocumentType = "'$academyUsers->aliasStudentDocumentType'";
$categoryName = "'$academyUsers->categoryName'";
$identification = "'" . $academyUsers->identification . "'";
$userPhoto = $academyUsers->photo ? "'$academyUsers->photo'" : "null";
$actions .= '<i class="fa fa-id-card-o iconMini" onClick="loadCard(' . $academyUsers->id . ', ' . $student_name . ', ' . $student_last_name . ', ' . $identification . ', ' . $academy_place . ', ' . $blood_type . ', ' . $studentDocumentType . ', ' . $categoryName . ', ' . $userPhoto . ')" data-id="' . $academyUsers->id . '" title="Carné"></i>';
}
}
return $actions;
} elseif ($this->validateRolePermissions('academy', 'assistant')) {
$actions = '';
$payments = AcademyPurchase::where('academy_user_id', $academyUsers->id)->first();
if ($payments) {
$actions .= '<i class="fa fa-list iconMini" onClick="getUserPayments(' . $academyUsers->id . ')" data-id="' . $academyUsers->id . '" title="Pagos"></i>';
}
$reports = AcademyReport::where('academy_user_id', $academyUsers->id)->first();
if ($reports) {
$actions .= '<i class="fa fa-file-pdf-o iconMini" onClick="viewReport(' . $academyUsers->id . ')" data-id="' . $academyUsers->id . '" title="Ver informes"></i>';
}
return $actions;
} else {
return '';
}
}
private function academyUsersTableActiveColumn($academyUsers)
{
if (
$this->validateRolePermissions('academy', 'administrator') ||
$this->validateRolePermissions('academy', 'supervisor')
) {
if ($academyUsers->active == 0) {
return '<div class="switch"><label><div class="checkbox checbox-switch switch-success"> <label> No <input type="checkbox" class="chk" onChange="chk(' . $academyUsers->id . ')" data-id="' . $academyUsers->id . '" id="Checkactive' . $academyUsers->id . '" name="Checkactivo" /> <span></span>Si </label></div> </label> </div>';
} else {
return '<div class="switch"><label> <div class="checkbox checbox-switch switch-success"> <label> No <input type="checkbox" class="chk" onChange="chk(' . $academyUsers->id . ')" data-id="' . $academyUsers->id . '" id="Checkactive' . $academyUsers->id . '" name="Checkactivo" checked="" />
<span></span> Si </label> </div> </label> </div>';
}
} else {
return $academyUsers->active ? 'SI' : 'NO';
}
}
}