File: /var/www/vhost/disk-apps/qas.sports-crowd.com/app/Http/Controllers/Imports/AcademyImport.php
<?php
namespace App\Http\Controllers\Imports;
use DB;
use App\User;
use App\AcademyUser;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;
use App\DocumentType;
use App\AcademyParameter;
use App\Address;
use App\AcademyCategory;
use App\Http\Controllers\AcademyStateController;
use App\AcademySchedule;
use App\AcademyLocation;
use App\Core\Academy\Application\AcademyUserService;
use App\Http\Controllers\Controller;
use App\Http\Controllers\UserController;
use App\Module;
use Exception;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
class AcademyImport implements ToCollection, WithHeadingRow, WithValidation, WithCalculatedFormulas
{
private $academyUserService;
private $error;
public $request;
public $cnt;
public $messages = [];
public $edit = [
'creados' => 0,
];
public function __construct()
{
$this->academyUserService = new AcademyUserService();
$this->messages = [];
$this->error = false;
}
public function rules(): array
{
return [
'*.correo_responsable_alumno' => 'required'
];
}
public function collection(Collection $rows)
{
$user_id = "";
$responsible_document = "";
$address_id = "";
$parameters = AcademyParameter::all()->toArray();
$academyState = new AcademyStateController();
$userController = new UserController();
$registrationDate = date('Y-m-d');
DB::beginTransaction();
$this->cnt = 0;
foreach ($rows as $key => $row) {
$this->cnt++;
if (!$row->filter()->isNotEmpty()) {
$this->addError(trans('messages.academy_users.tag65', ['position' => $this->cnt]));
break;
}
if (!$this->validateFields($row, [
'nombres_alumno' => 'El nombre del alumno es obligatorio',
'apellidos_alumno' => 'El apellido del alumno es obligatorio',
'nombres_responsable' => 'El nombre del responsable es obligatorio',
'apellidos_responsable' => 'El apellido del responsable es obligatorio',
])) {
continue;
}
$app_user_mail = $row['correo_responsable_app'];
$student_mail = $row['correo_responsable_alumno'];
// Validar que los correos tengan estructura válida
if (!isset($app_user_mail) || !$this->validateStructureMail($app_user_mail)) {
$this->addError('Validar la información de la plantilla, correo responsable app inválido, fila: ' . $this->cnt);
continue;
}
if (!isset($student_mail) || !$this->validateStructureMail($student_mail)) {
$this->addError('Validar la información de la plantilla, correo responsable alumno inválido, fila: ' . $this->cnt);
continue;
}
// Validamos que el documento alumno sea alfanumerico y no esté vacío
$document = $row["documento_alumno"];
if (!$this->isValidIdentification($document)) {
$this->addError('El documento del alumno está vacío o contiene caracteres no válidos. Solo se permiten letras y números. Fila: ' . $this->cnt);
continue;
}
// Validamos que el alumno ya existe
$student_exists = AcademyUser::where('identification', $row["documento_alumno"])->first();
if (isset($student_exists)) {
$this->addError('Validar la información de la plantilla, alumno con documento ' . $row["documento_alumno"] . ' ya registrado, fila: ' . $this->cnt);
continue;
}
// Validamos que el codigo de alumno ya existe
if (!is_null($row["codigo_estudiante"])) {
$student_exists = AcademyUser::where('student_academy_code', $row["codigo_estudiante"])->first();
if (isset($student_exists)) {
$this->addError('Validar la información de la plantilla, alumno con código ' . $row["codigo_estudiante"] . ' ya registrado, fila: ' . $this->cnt);
continue;
}
}
// Validamos el grado de escolaridad del alumno
$schoolGrade = AcademyParameter::where('key', 'school_grades')
->where('type_academy', 'children')
->value('value');
$schoolOptions = array_map('trim', explode(',', $schoolGrade));
if (!in_array($row['grado_de_escolaridad_alumno'], $schoolOptions)) {
$this->addError('Validar la información de la plantilla, grado de escolaridad ' . $row['grado_de_escolaridad_alumno'] . ' no encontrado, fila: ' . $this->cnt);
continue;
}
// Validamos que la informacion del correo del alumno existe
if (!isset($student_mail)) {
$this->addError('Validar la información de la plantilla, correo responsable alumno, fila: ' . $this->cnt);
continue;
}
// Validamos que el documento responsable alumno sea alfanumerico y no esté vacío
$advisorDocument = $row["documento_responsable"];
if (!$this->isValidIdentification($advisorDocument)) {
$this->addError('El documento del responsable está vacío o contiene caracteres no válidos. Solo se permiten letras y números. Fila: ' . $this->cnt);
continue;
}
// Validamos la relacion del alumno con el responsable
$advisorRelation = AcademyParameter::where('key', 'relationship')
->where('type_academy', 'children')
->value('value');
$advisorOptions = array_map('trim', explode(',', $advisorRelation));
if (!in_array($row['relacion_responsable'], $advisorOptions)) {
$this->addError('Validar la información de la plantilla, relación del alumno con el responsable ' . $row['relacion_responsable'] . ' no encontrado, fila: ' . $this->cnt);
continue;
}
// Validamo la ocupación del responsable
$advisorOcupation = AcademyParameter::where('key', 'ocupation')
->where('type_academy', 'children')
->value('value');
$advisorOptions = array_map('trim', explode(',', $advisorOcupation));
if (!in_array($row['ocupacion_responsable'], $advisorOptions)) {
$this->addError('Validar la información de la plantilla, ocupación del responsable ' . $row['ocupacion_responsable'] . ' no encontrado, fila: ' . $this->cnt);
continue;
}
// Validamos que el correo exista
if (isset($app_user_mail)) {
$user = User::where('email', $app_user_mail)->first();
$responsible_document = '';
$address_id = '';
if (isset($user)) {
$user_id = intval($user->id);
$responsible_document = intval($user->document);
// Validamos que tiene direccion
$address = Address::where('user_id', $user_id)->first();
if (true == (isset($address) ? $address : null)) {
$address_id = intval($address->id);
}
} else {
// Se envia a crear el usuario en las BD para que quede como usuario APP encargado del alumno
$password = strtoupper(hash("md5", $row["documento_responsable"]));
$data = [
'first_name' => $row['nombres_responsable'],
'last_name' => $row['apellidos_responsable'],
'phone' => trim($row["telefono_responsable_alumno"]),
'email' => $app_user_mail,
'password' => $password,
'document' => $row["documento_responsable"],
'rol_id' => 4
];
$request = new Request($data);
$user = $userController->createClient($request);
if ($user && $user['r']) {
$user_id = $user['d']['id'];
$responsible_document = $row["documento_responsable"];
}
}
}
// Validamos que la informacion del responsable existe
if ($user_id == "") {
$this->addError('Validar la información de la plantilla, no se pudo crear/obtener el usuario responsable, fila: ' . $this->cnt);
continue;
}
// consultamos el tipo de categoría
$studentCategory = '';
$studentCategory = trim($row['categoria']);
$categoryAcademyLocationId = '';
$academy_category_id = false;
$academy_headquarters = '';
try {
$academy_headquarters = trim($row[strtolower(config('app.location')) . '_de_la_academia']);
} catch (Exception $e) {
$this->addError('Validar la información de la plantilla, ' . config('app.location') . ' de la academia, fila: ' . $this->cnt);
continue;
}
// consultamos el horario
$scheduleInformation = '';
$scheduleInformation = trim($row['horario']);
$academyScheduleId = '';
$academyCategoryId = '';
// if para administradores
if (is_numeric($scheduleInformation)) {
$academySchedule = AcademySchedule::where([['id', '=', $scheduleInformation], ['active', true]])->with('academy_categories_schedules')->first();
if (isset($academySchedule)) {
$academyScheduleId = $academySchedule->id;
if (isset($academySchedule->academy_categories_schedules)) {
foreach ($academySchedule->academy_categories_schedules as $key => $academy_categorie_schedule) {
$id_category = AcademyCategory::where([['id', $academy_categorie_schedule->academy_category_id], ['name', $studentCategory], ['active', true]])->first();
if (isset($id_category)) {
$academyCategoryId = $id_category->id;
$categoryAcademyLocationId = $id_category->academy_location_id;
$academy_category_id = true;
}
}
} else {
$this->addError('Aviso: No se encuentra el vínculo del siguiente horario ' . $row['horario'] . ' a una categoría, fila: ' . $this->cnt);
continue;
}
} else {
$this->addError('Validar la información de la plantilla, horario ' . $row['horario'] . ' no encontrado o inactivo, fila: ' . $this->cnt);
continue;
}
} else {
// else para clientes
$academySchedule = AcademySchedule::where([['name', 'like', '%' . $scheduleInformation . '%'], ['active', true]])->with('academy_categories_schedules')->first();
if (isset($academySchedule)) {
$academyScheduleId = $academySchedule->id;
if (isset($academySchedule->academy_categories_schedules)) {
foreach ($academySchedule->academy_categories_schedules as $key => $academy_categorie_schedule) {
$id_category = AcademyCategory::where([['id', $academy_categorie_schedule->academy_category_id], ['name', $studentCategory], ['active', true]])->first();
if (isset($id_category)) {
$academyCategoryId = $id_category->id;
$categoryAcademyLocationId = $id_category->academy_location_id;
$academy_category_id = true;
}
}
} else {
$this->addError('Aviso: No se encuentra el vínculo del siguiente horario ' . $row['horario'] . ' a una categoría, fila: ' . $this->cnt);
continue;
}
} else {
$this->addError('Validar la información de la plantilla, horario ' . $row['horario'] . ' no encontrado o inactivo, fila: ' . $this->cnt);
continue;
}
}
// consultamos el tipo de documento al alumno
$documentType = DocumentType::where('name', '=', $row['tipo_de_documento_alumno'])->first();
if (isset($documentType)) {
$student_document_type_id = $documentType->id;
$student_document_type_id = intval($student_document_type_id);
} else {
$this->addError('Validar la información de la plantilla, tipo documento del alumno ' . $row['tipo_de_documento_alumno'] . ' no encontrado, fila: ' . $this->cnt);
continue;
}
// consultamos el tipo de documento responsable
$documentTypeAdvisor = DocumentType::where('name', '=', $row['tipo_de_documento_responsable'])->first();
if (isset($documentTypeAdvisor)) {
$advisor_document_type_id = $documentTypeAdvisor->id ? $documentTypeAdvisor->id : $user->document_type_id;
$advisor_document_type_id = intval($advisor_document_type_id);
} else {
$this->addError('Validar la información de la plantilla, tipo documento del responsable ' . $row['tipo_de_documento_responsable'] . ' no encontrado, fila: ' . $this->cnt);
continue;
}
$academyLocationId = '';
$academyLocationName = '';
$academyLocation = AcademyLocation::where([['name', '=', $academy_headquarters], ['active', true]])->first();
if (isset($academyLocation)) {
$academyLocationId = $academyLocation->id;
$academyLocationName = $academyLocation->name;
} else {
$this->addError('Validar la información de la plantilla, sede ' . $academy_headquarters . ' no encontrada, fila: ' . $this->cnt);
continue;
}
$userId = Auth::user()->id;
$userFranchiseNames = $this->getFranchiseNames($userId);
// Validar franquicia de users vs franquicia de academy_users
if(isset($userFranchiseNames) && count($userFranchiseNames) > 0){
if(!in_array($academyLocationName, $userFranchiseNames)){
$this->addError('Validar la información de la plantilla, ' . $academy_headquarters . ' no coincide con la franquicia del usuario, fila: ' . $this->cnt);
continue;
}
}
// Validar horarios y categorias
if ($academy_category_id !== true) {
$this->addError('Aviso: No se encuentra el vínculo entre sede, categoría y horario, fila: ' . $this->cnt);
continue;
}
// Validar sedes y categorias
if ($categoryAcademyLocationId !== $academyLocationId) {
$this->addError('Aviso: La categoria no esta asociada con la sede, fila: ' . $this->cnt);
continue;
}
if ($this->validar_fecha_espanol(trim($row['fecha_de_nacimiento_alumno']))) {
$dateOfBirth = explode('/', trim($row['fecha_de_nacimiento_alumno']));
$birthdate = $dateOfBirth[2] . '-' . $dateOfBirth[1] . '-' . $dateOfBirth[0];
} else if (is_numeric(trim($row['fecha_de_nacimiento_alumno']))) {
$excel_date = $row['fecha_de_nacimiento_alumno'];
$date_operation = ($excel_date - 25569) * 86400;
$excel_date = 25569 + ($date_operation / 86400);
$date_operation = ($excel_date - 25569) * 86400;
$timestamp = strtotime("+1 day", trim($date_operation));
$birthdate = date("Y-m-d", $timestamp);
} else {
$this->addError('La fecha de nacimiento en la fila ' . $this->cnt . ' no es válida. Asegúrate de que esté en el formato correcto (dd/mm/yyyy) y que contenga un año válido de 4 dígitos.');
continue;
}
$IFoundBloodType = false;
foreach (explode(',', array_column($parameters, null, 'key')['blood_type']['value']) as $item) {
if ($item == $row['tipo_de_sangre']) {
$blood_type = $item;
$IFoundBloodType = true;
}
}
if ($IFoundBloodType == false) {
$this->addError('Validar la información de la plantilla, tipo de sangre ' . $row['tipo_de_sangre'] . ' no encontrado, fila: ' . $this->cnt);
continue;
}
$findSize = false;
$uniformSize = trim($row['talla_uniforme']);
$availableSizes = explode(',', array_column($parameters, null, 'key')['tshirt_sizes']['value']);
foreach ($availableSizes as $item) {
if (trim($item) == $uniformSize) {
$size = trim($item);
$findSize = true;
break;
}
}
if (!$findSize) {
$this->addError('Validar la información de la plantilla, talla del uniforme "' . $row['talla_uniforme'] . '" no encontrada, fila: ' . $this->cnt);
continue;
}
$newStudent = AcademyUser::create([
'student_name' => $row['nombres_alumno'],
'student_last_name' => $row['apellidos_alumno'],
'student_document_type_id' => $student_document_type_id ? $student_document_type_id : 4,
'identification' => $row["documento_alumno"],
'student_academy_code' => $row["codigo_estudiante"],
'school_name' => $row['institucion_educativa_alumno'],
'school_grade' => $row['grado_de_escolaridad_alumno'],
'birthdate' => $birthdate,
'age' => date_diff(new \DateTime(), date_create($birthdate))->y,
'mail' => $student_mail,
'phone' => trim($row["telefono_responsable_alumno"]),
'advisor_name' => $row['nombres_responsable'],
'advisor_last_name' => $row['apellidos_responsable'],
'advisor_document_type_id' => $advisor_document_type_id ? $advisor_document_type_id : 1,
'advisor_identification' => $responsible_document ? $responsible_document : $row["documento_responsable"],
'advisor_relation' => $row['relacion_responsable'],
'advisor_ocupation' => $row['ocupacion_responsable'],
'date' => $registrationDate,
'address_id' => $address_id ? $address_id : null,
'blood_type' => $blood_type,
'academy_place' => $academyLocationName,
'last_inscription_year' => trim($row['ultimo_ano_academia']),
'active' => false
]);
$newStudent->save();
$this->edit['creados'] += 1;
if (is_null($row['codigo_estudiante'])) {
$this->academyUserService->generateCode($newStudent);
}
if ($newStudent) {
$newStudent->user_id = $user_id ?? null;
$newStudent->eps = $row['tipo_de_seguro'];
$newStudent->tshirt_size = $size;
$newStudent->academy_category_id = $academyCategoryId;
$newStudent->academy_schedule_id = $academyScheduleId;
$newStudent->academy_state_id = 1;
$newStudent->update();
if ($newStudent->user_id) {
$academyState->assignStateAcademy($newStudent);
}
}
}
if ($this->error || sizeof($rows) <= 0) {
DB::rollback();
return;
} else {
DB::commit();
$academyModule = Module::where("route", "academy")->first();
$controller = new Controller;
$controller->registerLog(Auth::user()->id, 'Importar alumnos', json_encode($rows), "Create", $academyModule->id);
}
}
function validar_fecha_espanol($date)
{
// Validar que el separador sea "/"
if (strpos($date, '/') === false) {
return false;
}
$values = explode('/', $date);
if (count($values) === 3) {
$day = $values[1];
$month = $values[1];
$year = $values[2];
// Validar que el año tenga 4 dígitos
if (strlen($year) !== 4 || !ctype_digit($year)) {
return false;
}
// Validar la fecha con checkdate
if (checkdate($month, $day, $year)) {
return true;
}
}
return false;
}
private function addError($message): void
{
$this->error = true;
$this->messages[] = [
'message' => $message,
'state' => false,
];
}
function validateStructureMail($mail)
{
//eliminar espacios en blanco
$mail = trim($mail);
$pattern = '/^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/';
return preg_match($pattern, $mail) === 1;
}
function getFranchiseNames($userId){
$academyLocationNames = DB::table('academy_locations')
->leftJoin('academy_location_users', 'academy_locations.id', '=', 'academy_location_users.academy_location_id')
->where('academy_location_users.user_id', $userId)
->pluck('academy_locations.name')
->toArray();
return $academyLocationNames;
}
private function isValidIdentification($string)
{
return !empty($string) && preg_match('/^[a-zA-Z0-9]+$/', $string);
}
protected function validateFields($row, array $fields)
{
foreach ($fields as $campo => $mensaje) {
if (empty($row[$campo])) {
$this->addError($mensaje . ' Fila: ' . $this->cnt);
return false;
}
}
return true;
}
}