File: /var/www/vhost/disk-apps/sigedo-dev.allup.com.co/app/Http/Controllers/UsersController.php
<?php
namespace App\Http\Controllers;
use App\Role;
use App\User;
use App\Gender;
use App\Service;
use App\Userdocument;
use App\DocumentType;
use App\Userprogram;
use App\Covenantprogram;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Hash;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Box\Spout\Common\Entity\Style\CellAlignment;
use Box\Spout\Common\Entity\Style\Color;
use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Common\Entity\Row;
class UsersController extends Controller
{
public $msj_rollback_import = "";
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$users = User::gates('show')->with(['gender','roles'])->orderBy('id', 'desc')->paginate(100);
return view('users.list', compact('users'));
}
public function filterUser(Request $request)
{
abort_unless(\Gate::allows('user_access'), 403);
if($request->ajax()){
$query = $request->get('query');
$query = str_replace(" ", "%", $query);
if($query != ''){
if(env('DB_CONNECTION') == 'pgsql'){
$users = User::gates('show')->with(['gender','roles'])->orderBy('id', 'desc')
->where('name', 'ilike', "%".$query."%")
->orWhere('last_name', 'ilike', "%".$query."%")
->orWhere('email', 'ilike', "%".$query."%")
->orWhere('document', 'ilike', "%".$query."%")
->orWhere('phone', 'ilike', "%".$query."%")
->paginate(100);
}else{
$users = User::gates('show')->with(['gender','roles'])->orderBy('id', 'desc')
->where('name', 'like', "%".$query."%")
->orWhere('last_name', 'like', "%".$query."%")
->orWhere('email', 'like', "%".$query."%")
->orWhere('document', 'like', "%".$query."%")
->orWhere('phone', 'like', "%".$query."%")
->paginate(100);
}
}else{
$users = User::gates('show')->with(['gender','roles'])->orderBy('id', 'desc')->paginate(100);
}
$out = '';
foreach ($users as $key => $user) {
$list_documents = DB::table('filetype_role')->where('role_id', $user->roles[0]->id)->count();
$list_upload = Userdocument::where('user_id', $user->id)->where('filestatus_id',3)->count();
if($list_documents != $list_upload){
$user->color_badge = "danger";
$user->state = "Se requiere revisión";
}else{
$user->color_badge = "success";
$user->state = "Documentos cargados";
}
$rol = '';
foreach ($user->roles->pluck('display_name') as $key => $item){
$rol = $rol . $item;
}
$out .= '
<tr>
<td>'.$user->name.' '.$user->last_name.'</td>
<td>'.$user->email.'</td>
<td>'.$user->document.'</td>
<td><a href="tel:'.$user->phone.'">'.$user->phone.'</a></td>
<td>'.$rol.'</td>
<td>'.$user->last_logon.'</td>
<td>
<a href="'.route('userdocuments.index', ['user_id' => $user->id]).'"
class="badge badge-'.$user->color_badge.' m-2" data-toggle="tooltip"
data-placement="top" title="'.__('userdocuments').'">'.$user->state.'
<i class="i-File-Cloud"></i>
</a>
<a href="'.route('userprograms.index', ['user_id' => $user->id]).'"
class="ul-link-action text-info" data-toggle="tooltip"
data-placement="top" title="'.__('userprograms').'">
<i class="i-Diploma-2"></i>
</a>
<a href="'.route('users.edit', ['user_id' => $user->id]).'"
class="ul-link-action text-success" data-toggle="tooltip"
data-placement="top" title="'.__('edit').'">
<i class="i-Edit"></i>
</a>
<span class="ul-link-action text-danger mr-1" data-toggle="tooltip"
data-placement="top" title="'.__('delete').'"
onclick="deleteRegistry('.$user->id.')">
<i class="i-Eraser-2"></i>
</span>
</td>
</tr>';
}
$data = array(
'table_data' => $out,
'pagination' => $users->links()->render());
return json_encode($data);
}
}
/**
* Show the form for creating a new resource.
*
* @return \Illuminate\Http\Response
*/
public function create()
{
abort_unless(\Gate::allows('user_create'), 403);
$genders = Gender::where('active', 1)->get();
$roles = Role::gates('create')->where('active', 1)->get();
$documentTypes = DocumentType::where('active', 1)->get();
return view('users.create', compact('genders','roles','documentTypes'));
}
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
abort_unless(\Gate::allows('user_create'), 403);
if(User::where([['email', $request->input('email')]])->first()){
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => __('already_exists', ['name' => $request->input('email')]), "data" => null));
}
if(User::where([['document', $request->input('document')]])->first()){
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => __('already_exists', ['name' => $request->input('document')]), "data" => null));
}
if ($user = User::create(array_slice($request->all(), 1))) {
if (count($request->input('user_roles')) && $this->validateGateByRole('role','create',$request->input('user_roles'))) {
$user->roles()->attach($request->input('user_roles'));
}
$this->registerLogData(json_encode($request->all()), $user->id, 3, 1, Auth::user()->id);
return response(array("status" => true, "type" => "success", "title" => "", "message" => __('created_successfully'), "data" => array("user_id" => $user->id)));
} else {
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => __('error_creating'), "data" => null));
}
}
/**
* Display the specified resource.
*
* @param int $user_id
* @return \Illuminate\Http\Response
*/
public function show($user_id)
{
abort_unless(\Gate::allows('user_show'), 403);
return redirect()->back();
}
/**
* Show the form for editing the specified resource.
*
* @param int $user_id
* @return \Illuminate\Http\Response
*/
public function edit($user_id)
{
abort_unless(\Gate::allows('user_edit'), 403);
$user = User::findOrFail($user_id);
$genders = Gender::where('active', 1)->get();
$documentTypes = DocumentType::where('active', 1)->get();
$roles = Role::gates('edit')->where('active', 1)->get();
return view('users.edit', compact('user','genders','roles','documentTypes'));
}
/**
* Update the specified resource in storage.
*
* @param \Illuminate\Http\Request $request
* @param int $user_id
* @return \Illuminate\Http\Response
*/
public function update(Request $request, $user_id)
{
abort_unless(\Gate::allows('user_edit'), 403);
if(User::where([['id', '!=', $user_id], ['email', $request->input('email')]])->first()){
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => __('already_exists', ['name' => $request->input('email')]), "data" => null));
}
if(User::where([['id', '!=', $user_id], ['document', $request->input('document')]])->first()){
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => __('already_exists', ['name' => $request->input('document')]), "data" => null));
}
if (User::where('id', $user_id)->update(array_slice($request->all(), 2))) {
$user = User::findOrFail($user_id);
if(isset($request->password)){
$user->fill([
'password' => $request->password,
'first_login' =>false,
])->save();
}
if (count($request->input('user_roles')) && $this->validateGateByRole('role','edit',$request->input('user_roles'))) {
$user->roles()->sync($request->input('user_roles'));
}
$this->registerLogData(json_encode(array_slice($request->all(), 1)), $user_id, 3, 3, Auth::user()->id);
return response(array("status" => true, "type" => "success", "title" => "", "message" => __('updated_successfully'), "data" => null));
} else {
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => __('error_updating'), "data" => null));
}
}
/**
* Remove the specified resource from storage.
*
* @param int $user_id
* @return \Illuminate\Http\Response
*/
public function destroy($user_id)
{
abort_unless(\Gate::allows('user_destroy'), 403);
try {
if (User::where('id', $user_id)->delete()) {
$this->registerLogData('', $user_id, 3, 4, Auth::user()->id);
return response(array("status" => true, "type" => "success", "title" => "", "message" => __('deleted_successfully'), "data" => null));
} else {
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => __('error_removing'), "data" => null));
}
} catch (\Illuminate\Database\QueryException $e) {
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => __('delete_relation_data'), "data" => null));
}
}
function is_digits($element) {
return preg_match('/^[0-9]*$/', $element);
}
public function getValueRow($cells,$index = false)
{
if($index !== false){
return $cells[$index]->getValue();
}
return $cells->getValue();
}
public function validateDate($date)
{
try {
$current = explode("/", $date);
if(count($current) != 3){
return;
}
$year = $current[2];
$month = $current[1];
$day = $current[0];
if(strlen($year) != 4){
return;
}
if(strlen($month) != 2){
return;
}
if(strlen($day) != 2){
return;
}
if($month[0] == "0"){
$month = $month[1];
}
if($day[0] == "0"){
$day = $day[1];
}
if(!Carbon::create($year,$month,$day)){
return;
}
return true;
} catch (\Throwable $th) {
return;
}
}
public function getDateCarbon($date)
{
try {
$current = explode("/", $date);
$year = $current[2];
$month = $current[1];
$day = $current[0];
return Carbon::create($year,$month,$day);
} catch (\Throwable $th) {
return Carbon::now();
}
}
public function validateCell($cells,$cnt)
{
try {
foreach ($cells as $key => $cell) {
$value = $this->getValueRow($cell);
if ($key == 5 && !$this->validateDate($value)) {
$this->msj_rollback_import = __('cell_empty', ['column' => $key + 1, 'cell' => $cnt]);
return false;
break;
}
if($key == 6 && !Gender::where('id', $value)->first()){
$this->msj_rollback_import = __('cell_ids', ['field' => __('gender_id'), 'cell' => $cnt]);
return false;
break;
}
if($key == 7 && !DocumentType::where('id', $value)->first()){
$this->msj_rollback_import = __('cell_ids', ['field' => __('document_type_id'), 'cell' => $cnt]);
return false;
break;
}
if($key == 8 && $value == '1' || $key == 8 && $value == '11'){
$this->msj_rollback_import = __('cell_ids', ['field' => __('role_id'), 'cell' => $cnt]);
return false;
break;
}
if($key == 8 && !Role::where('id', $value)->first()){
$this->msj_rollback_import = __('cell_ids', ['field' => __('role_id'), 'cell' => $cnt]);
return false;
break;
}
if($key == 9 && $value != "" && !$this->validateDate($value)){
$this->msj_rollback_import = __('cell_date', ['column' => __('start_date_validity'), 'cell' => $cnt]);
return false;
break;
}
if($key == 10 && $value != "" && !$this->validateDate($value)){
$this->msj_rollback_import = __('cell_date', ['column' => __('end_date_validity'), 'cell' => $cnt]);
return false;
break;
}
if($key < 9 && $key != 5 && trim($value) == ""){
$this->msj_rollback_import = __('cell_empty', ['column' => $key + 1, 'cell' => $cnt]);
return false;
break;
}
if($key > 5 && $key < 9 && !$this->is_digits($value)){
$this->msj_rollback_import = __('cell_int', ['column' => $key + 1, 'cell' => $cnt]);
return false;
break;
}
// if($key == 2 && User::where('email', $value)->first()){
// $this->msj_rollback_import = __('already_exists', ['name' => $value]);
// return false;
// break;
// }
}
$user = new \stdClass();
$user->name = $this->getValueRow($cells,0);
$user->last_name = $this->getValueRow($cells,1);
$user->email = trim($this->getValueRow($cells,2));
$user->password = trim($this->getValueRow($cells,4));
$user->phone = $this->getValueRow($cells,3);
$user->document = $this->getValueRow($cells,4);
$user->birthdate = Carbon::instance($this->getDateCarbon($this->getValueRow($cells,5)))->format('Y-m-d');
$user->gender_id = $this->getValueRow($cells,6);
$user->document_type_id = $this->getValueRow($cells,7);
$user->start_date_validity = $this->getValueRow($cells,9) != "" ? Carbon::instance($this->getDateCarbon($this->getValueRow($cells,9)))->format('Y-m-d') : null;
$user->end_date_validity = $this->getValueRow($cells,10) != "" ? Carbon::instance($this->getDateCarbon($this->getValueRow($cells,10)))->format('Y-m-d') : null;
$user->active = true;
return json_decode(json_encode($user), true);
} catch (\Throwable $e) {
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => $e->getMessage(), "data" => null));
}
}
public function import_template(Request $request)
{
try {
DB::beginTransaction();
$reader = ReaderEntityFactory::createXLSXReader();
$reader->open($request->template_users);
$cnt = 0;
foreach ($reader->getSheetIterator() as $key_sheet => $sheet) {
if($key_sheet == 1){
foreach ($sheet->getRowIterator() as $row) {
if($cnt > 0){
$cells = $row->getCells();
if($user = $this->validateCell($cells,$cnt+1)){
$current_user;
$roles = [$this->getValueRow($cells,8)];
if($current_user = User::where('document', trim($this->getValueRow($cells,4)))->first()){
User::where('id', $current_user->id)->update($user);
$current_user->roles()->sync($roles);
}else{
$current_user = User::create($user);
$current_user->roles()->attach($roles);
}
// se valida si ingresaron programas asignados al usuario
$covenanprograms = $this->getValueRow($cells,11);
$services = $this->getValueRow($cells,12);
$start_dates = $this->getValueRow($cells,13);
$end_dates = $this->getValueRow($cells,14);
try {
$list_programs = explode(",", $covenanprograms);
$list_services = explode(",", $services);
$list_starts = explode(",", $start_dates);
$list_ends = explode(",", $end_dates);
if(count($list_programs) == count($list_services) && count($list_programs) == count($list_starts) && count($list_programs) == count($list_ends)){
foreach ($list_programs as $key => $program) {
$current_program = Covenantprogram::where('id', $program)->with('covenant')->first();
$current_service = Service::where('id', $list_services[$key])->first();
if(!$this->validateDate($list_starts[$key])){
$this->msj_rollback_import = __('cell_error_date', ['column' => 'Fecha Inicio Rotación', 'cell' => $cnt+1]);
DB::rollback();
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => $this->msj_rollback_import, "data" => $th->getMessage()));
break;
}
if(!$this->validateDate($list_ends[$key])){
$this->msj_rollback_import = __('cell_error_date', ['column' => 'Fecha Fin Rotación', 'cell' => $cnt+1]);
DB::rollback();
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => $this->msj_rollback_import, "data" => $th->getMessage()));
break;
}
if($current_program && $current_service){
Userprogram::create([
'user_id' => $current_user->id,
'program_id' => $current_program->program_id,
'rotation_id' => $current_program->rotation_id,
'covenant_id' => $current_program->covenant_id,
'institution_id' => $current_program->covenant->institution_id,
'service_id' => $current_service->id,
'covenantprogram_id' => $program,
'rotation_start_date' => Carbon::instance($this->getDateCarbon($list_starts[$key]))->format('Y-m-d'),
'rotation_end_date' => Carbon::instance($this->getDateCarbon($list_ends[$key]))->format('Y-m-d'),
]);
}
}
}else{
$this->msj_rollback_import = __('cell_error', ['column' => '12 y 15', 'cell' => $cnt+1]);
DB::rollback();
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => $this->msj_rollback_import, "data" => $th->getMessage()));
break;
}
} catch (\Throwable $th) {
if($this->msj_rollback_import == ""){
$this->msj_rollback_import = __('cell_error', ['column' => '12 y 15', 'cell' => $cnt+1]);
}
DB::rollback();
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => $this->msj_rollback_import, "data" => $th->getMessage()));
break;
}
}else{
DB::rollback();
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => $this->msj_rollback_import, "data" => null));
break;
}
}
$cnt++;
}
}
}
DB::commit();
$reader->close();
return response(array("status" => true, "type" => "success", "title" => "", "message" => __('import_successfully', ["cnt" => $cnt-1]), "data" => null));
} catch (\Throwable $e) {
return response(array("status" => false, "type" => "error", "title" => "Oops...", "message" => $e->getMessage(), "data" => null));
}
}
public function export_template()
{
$writer = WriterEntityFactory::createXLSXWriter();
$current_date = Carbon::instance(Carbon::now())->format('Y-m-d');
$writer->openToBrowser("plantilla_carga_usuarios_".$current_date.".xlsx");
$sheet = $writer->getCurrentSheet();
$sheet->setName(__('users'));
$style = (new StyleBuilder())
->setFontBold()
->setFontSize(10)
->setFontColor(Color::BLACK)
->setShouldWrapText()
->setCellAlignment(CellAlignment::CENTER)
->setBackgroundColor(Color::rgb(245, 245, 245))
->build();
$defaultStyle = (new StyleBuilder())
->setFormat('@')
->build();
$cells = [
WriterEntityFactory::createCell(__('name'), $defaultStyle),
WriterEntityFactory::createCell(__('last_name'), $defaultStyle),
WriterEntityFactory::createCell(__('email'), $defaultStyle),
WriterEntityFactory::createCell(__('phone'), $defaultStyle),
WriterEntityFactory::createCell(__('document'), $defaultStyle),
WriterEntityFactory::createCell(__('birthdate_format'), $defaultStyle),
WriterEntityFactory::createCell(__('gender_id'), $defaultStyle),
WriterEntityFactory::createCell(__('document_type_id'), $defaultStyle),
WriterEntityFactory::createCell(__('role_id'), $defaultStyle),
WriterEntityFactory::createCell(__('start_date_validity_format'), $defaultStyle),
WriterEntityFactory::createCell(__('end_date_validity_format'), $defaultStyle),
WriterEntityFactory::createCell(__('covenantprogram_id_format'), $defaultStyle),
WriterEntityFactory::createCell(__('service_id_format'), $defaultStyle),
WriterEntityFactory::createCell(__('rotation_start_date_format'), $defaultStyle),
WriterEntityFactory::createCell(__('rotation_end_date_format'), $defaultStyle),
];
$singleRow = WriterEntityFactory::createRow($cells,$style);
$writer->addRow($singleRow);
$values = ['Pepito', 'Perez', 'pepito@perez.com','3151234567','1094123456','25/08/1998','1','1','3','01/01/2021','28/02/2021','1,2','1,2','01/01/2021,01/01/2021','28/02/2021,28/02/2021'];
$rowFromValues = WriterEntityFactory::createRowFromArray($values, $defaultStyle);
$writer->addRow($rowFromValues);
// add sheet ids genders
$writer->addNewSheetAndMakeItCurrent();
$sheet = $writer->getCurrentSheet();
$sheet->setName(__('gender'));
$cells_gender = [
WriterEntityFactory::createCell(__('id')),
WriterEntityFactory::createCell(__('name')),
];
$singleRow_gender = WriterEntityFactory::createRow($cells_gender);
$writer->addRow($singleRow_gender);
$genders = Gender::where('active', true)->orderBy('id','asc')->get();
foreach ($genders as $gender) {
$value = [$gender->id,$gender->name];
$rowFromValue_genders = WriterEntityFactory::createRowFromArray($value);
$writer->addRow($rowFromValue_genders);
}
// end sheet ids genders
// add sheet ids document_types
$writer->addNewSheetAndMakeItCurrent();
$sheet = $writer->getCurrentSheet();
$sheet->setName(__('document_types'));
$cells_documents = [
WriterEntityFactory::createCell(__('id')),
WriterEntityFactory::createCell(__('name')),
];
$singleRow_documents = WriterEntityFactory::createRow($cells_documents);
$writer->addRow($singleRow_documents);
$documents = DocumentType::where('active', true)->orderBy('id','asc')->get();
foreach ($documents as $document) {
$value = [$document->id,$document->name];
$rowFromValue_documents = WriterEntityFactory::createRowFromArray($value);
$writer->addRow($rowFromValue_documents);
}
// end sheet ids document_types
// add sheet ids role
$writer->addNewSheetAndMakeItCurrent();
$sheet = $writer->getCurrentSheet();
$sheet->setName(__('roles'));
$cells_role = [
WriterEntityFactory::createCell(__('id')),
WriterEntityFactory::createCell(__('name')),
];
$singleRow_role = WriterEntityFactory::createRow($cells_role);
$writer->addRow($singleRow_role);
$roles = Role::where([['active', true],['id', '!=', 1]])->orderBy('id','asc')->get();
foreach ($roles as $role) {
$value = [$role->id,$role->display_name];
$rowFromValue_role = WriterEntityFactory::createRowFromArray($value);
$writer->addRow($rowFromValue_role);
}
// end sheet ids role
// add sheet ids Covenantprograms
$writer->addNewSheetAndMakeItCurrent();
$sheet = $writer->getCurrentSheet();
$sheet->setName(__('programs'));
$cells_prgrams = [
WriterEntityFactory::createCell(__('id')),
WriterEntityFactory::createCell(__('covenant')),
WriterEntityFactory::createCell(__('program')),
WriterEntityFactory::createCell(__('rotation')),
WriterEntityFactory::createCell(__('service_id')),
];
$singleRow_prgram = WriterEntityFactory::createRow($cells_prgrams);
$writer->addRow($singleRow_prgram);
$programs = Covenantprogram::whereHas('covenant', function (Builder $query) {
$query->where('active', true);
})
->with('covenant','program','rotation','services')
->get();
foreach ($programs as $program) {
$services = "";
foreach ($program->services as $value) {
$services = $services . $value->id . " " . $value->name. " | ";
}
$services = substr($services, 0, -2);
$value = [$program->id,$program->covenant->name,$program->program->name,$program->rotation->name,$services];
$rowFromValue_program = WriterEntityFactory::createRowFromArray($value);
$writer->addRow($rowFromValue_program);
}
// end sheet ids program
$writer->close();
}
}