File: /var/www/vhost/disk-apps/comfama.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\Http\Controllers\Controller;
use App\Http\Controllers\OtherUsersController;
use App\Module;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
class AcademyImport implements ToCollection, WithHeadingRow, WithValidation, WithCalculatedFormulas
{
public $request;
public $cnt;
public $messages = [];
public function rules(): array
{
return [
'*.nombres_alumno' => 'required',
'*.apellidos_alumno' => 'required',
'*.documento_alumno' => 'required',
'*.correo_responsable_alumno' => 'required'
];
}
public function collection(Collection $rows)
{
$user_id = "";
$responsible_document = "";
$address_id = "";
$parameters = AcademyParameter::all()->toArray();
$academyState = new AcademyStateController();
$userController = new OtherUsersController();
$registrationDate = date('Y-m-d');
DB::beginTransaction();
$this->cnt = 0;
$is_error = false;
foreach ($rows as $key => $row) {
$this->cnt++;
if (!$row->filter()->isNotEmpty()) {
$is_error = true;
$this->messages[] = [
'message' => trans('messages.academy_users.tag65', ['position' => $this->cnt]),
'state' => false,
];
break;
}
$app_user_mail = $row['correo_responsable_app'];
$student_mail = $row['correo_responsable_alumno'];
// Validamos que el alumno ya existe
$student_exists = AcademyUser::where('identification', $row["documento_alumno"])->first();
if (isset($student_exists)) {
$is_error = true;
$this->messages[] = [
'message' => 'Validar la información de la plantilla, alumno con documento ' . $row["documento_alumno"] . ' ya registrado, fila: ' . $this->cnt,
'state' => false,
];
continue;
}
// Validamos que la informacion del correo del alumno existe
if (!isset($student_mail)) {
$is_error = true;
$this->messages[] = [
'message' => 'Validar la información de la plantilla, correo responsable alumno, fila: ' . $this->cnt,
'state' => false,
];
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->create($request);
if ($user && $user['r']) {
$user_id = $user['d'];
$responsible_document = $row["documento_responsable"];
}
}
}
// consultamos el tipo de categoría
$categoriaAlumno = '';
$categoriaAlumno = trim($row['categoria']);
$categoryAcademyLocationId = '';
$academy_category_id = false;
$academy_headquarters = '';
$academy_headquarters = trim($row['sede_de_la_academia']);
// 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', $categoriaAlumno], ['active', true]])->first();
if (isset($id_category)) {
$academyCategoryId = $id_category->id;
$categoryAcademyLocationId = $id_category->academy_location_id;
$academy_category_id = true;
}
}
} else {
$is_error = true;
$this->messages[] = [
'message' => 'Aviso: No se encuentra el vínculo del siguiente horario ' . $row['horario'] . ' a una categoría, fila: ' . $this->cnt,
'state' => false,
];
continue;
}
} else {
$is_error = true;
$this->messages[] = [
'message' => 'Validar la información de la plantilla, horario ' . $row['horario'] . ' no encontrado o inactivo, fila: ' . $this->cnt,
'state' => false,
];
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', $categoriaAlumno], ['active', true]])->first();
if (isset($id_category)) {
$academyCategoryId = $id_category->id;
$categoryAcademyLocationId = $id_category->academy_location_id;
$academy_category_id = true;
}
}
} else {
$is_error = true;
$this->messages[] = [
'message' => 'Aviso: No se encuentra el vínculo del siguiente horario ' . $row['horario'] . ' a una categoría, fila: ' . $this->cnt,
'state' => false,
];
continue;
}
} else {
$is_error = true;
$this->messages[] = [
'message' => 'Validar la información de la plantilla, horario ' . $row['horario'] . ' no encontrado o inactivo, fila: ' . $this->cnt,
'state' => false,
];
continue;
}
}
// consultamos el tipo de documento al alumno
$documentType = DocumentType::where('name', 'like', '%' . $row['tipo_de_documento_alumno'] . '%')->first();
if (isset($documentType)) {
$student_document_type_id = $documentType->id;
$student_document_type_id = intval($student_document_type_id);
}
// consultamos el tipo de documento responsable
$documentTypeAdvisor = DocumentType::where('name', 'like', '%' . $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 {
$is_error = true;
$this->messages[] = [
'message' => 'Validar la información de la plantilla, tipo documento del responsable ' . $row['tipo_de_documento_responsable'] . ' no encontrado, fila: ' . $this->cnt,
'state' => false,
];
continue;
}
$academyLocationId = '';
$academyLocationName = '';
$academyLocation = AcademyLocation::where([['name', '=', $academy_headquarters], ['active', true]])->first();
if (isset($academyLocation)) {
$academyLocationId = $academyLocation->id;
$academyLocationName = $academyLocation->name;
} else {
$is_error = true;
$this->messages[] = [
'message' => 'Validar la información de la plantilla, sede ' . $academy_headquarters . ' no encontrada, fila: ' . $this->cnt,
'state' => false,
];
continue;
}
// Validar horarios y categorias
if ($academy_category_id !== true) {
$is_error = true;
$this->messages[] = [
'message' => 'Aviso: No se encuentra el vínculo entre sede, categoría y horario, fila: ' . $this->cnt,
'state' => false,
];
continue;
}
// Validar sedes y categorias
if ($categoryAcademyLocationId !== $academyLocationId) {
$is_error = true;
$this->messages[] = [
'message' => 'Aviso: La categoria no esta asociada con la sede, fila: ' . $this->cnt,
'state' => false,
];
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 {
$is_error = true;
$this->messages[] = [
'message' => 'Validar la información de la fecha, que sea de tipo (date/fecha corta) en la plantilla, fila: ' . $this->cnt,
'state' => false,
];
continue;
}
// $IFoundHeadquarters = false;
// foreach (explode(',', array_column($parameters, null, 'key')['academy_places']['value']) as $item){
// if($item == $row['sede_de_la_academia']){
// $academy_places = $item;
// $IFoundHeadquarters = true;
// }
// }
// if ($IFoundHeadquarters == false) {
// $is_error = true;
// $this->messages = [
// 'message' => 'Validar la información de la plantilla, sede ' . $row['sede_de_la_academia'] . ' no encontrada, fila: ' . $this->cnt,
// 'state' => false,
// ];
// break;
// }
$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) {
$is_error = true;
$this->messages[] = [
'message' => 'Validar la información de la plantilla, tipo de sangre ' . $row['tipo_de_sangre'] . ' no encontrado, fila: ' . $this->cnt,
'state' => false,
];
continue;
}
$findSize = false;
foreach (explode(',', array_column($parameters, null, 'key')['tshirt_sizes']['value']) as $item) {
if ($item == $row['talla_uniforme']) {
$size = $item;
$findSize = true;
}
}
if ($findSize == false) {
$is_error = true;
$this->messages[] = [
'message' => 'Validar la información de la plantilla, talla del uniforme ' . $row['talla_uniforme'] . ' no encontrado, fila: ' . $this->cnt,
'state' => false,
];
continue;
}
$new_students = 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"],
'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
]);
$new_students->save();
$academyState->assignStateAcademy($new_students);
if ($new_students->id) {
$current_students = AcademyUser::where('id', $new_students->id)->first();
$current_students->user_id = $user_id ? $user_id : null;
$current_students->eps = $row['tipo_de_seguro'];
$current_students->tshirt_size = $size;
$current_students->academy_category_id = $academyCategoryId;
$current_students->academy_schedule_id = $academyScheduleId;
$current_students->update();
}
}
if ($is_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($fecha)
{
$valores = explode('/', $fecha);
if (count($valores) == 3 && checkdate($valores[1], $valores[0], $valores[2])) {
return true;
}
return false;
}
}