public function save($request, $id = false)
{
$rules = ['last_name' => 'min:3|regex:/^[a-záéíóúàèìòùäëïöüñ\\s]+$/i', 'mothers_last_name' => 'min:3|regex:/^[a-záéíóúàèìòùäëïöüñ\\s]+$/i', 'first_name' => 'min:3|regex:/^[a-záéíóúàèìòùäëïöüñ\\s]+$/i', 'second_name' => 'min:3|regex:/^[a-záéíóúàèìòùäëïöüñ\\s]+$/i'];
$messages = ['last_name.min' => 'El mínimo de caracteres permitidos para apellido paterno es 3', 'last_name.regex' => 'Sólo se aceptan letras para apellido paterno', 'mothers_last_name.min' => 'El mínimo de caracteres permitidos para apellido materno es 3', 'mothers_last_name.regex' => 'Sólo se aceptan letras para apellido materno', 'first_name.min' => 'El mínimo de caracteres permitidos para primer nombre es 3', 'first_name.regex' => 'Sólo se aceptan letras para primer nombre', 'second_name.min' => 'El mínimo de caracteres permitidos para teléfono de usuario es 3', 'second_name.regex' => 'Sólo se aceptan letras para segundo nombre'];
$validator = Validator::make($request->all(), $rules, $messages);
if ($validator->fails()) {
return redirect('affiliate/' . $id)->withErrors($validator)->withInput();
} else {
$spouse = Spouse::affiliateidIs($id)->first();
if (!$spouse) {
$spouse = new Spouse();
}
$spouse->user_id = Auth::user()->id;
$spouse->affiliate_id = $id;
$spouse->identity_card = trim($request->identity_card);
$spouse->last_name = trim($request->last_name);
$spouse->mothers_last_name = trim($request->mothers_last_name);
$spouse->first_name = trim($request->first_name);
$spouse->second_name = trim($request->second_name);
$spouse->birth_date = Util::datePick($request->birth_date);
if ($request->DateDeathSpouseCheck == "on") {
$spouse->date_death = Util::datePick($request->date_death);
$spouse->reason_death = trim($request->reason_death);
} else {
$spouse->date_death = null;
$spouse->reason_death = null;
}
$spouse->save();
$message = "Información de Conyuge actualizado con éxito";
Session::flash('message', $message);
}
return redirect('affiliate/' . $id);
}
public function ShowData(Request $request)
{
$contributions = Contribution::select(['id', 'month_year', 'degree_id', 'unit_id', 'item', 'base_wage', 'seniority_bonus', 'study_bonus', 'position_bonus', 'border_bonus', 'east_bonus', 'public_security_bonus', 'gain', 'quotable', 'retirement_fund', 'mortuary_quota', 'total'])->where('affiliate_id', $request->affiliate_id);
return Datatables::of($contributions)->editColumn('month_year', function ($contribution) {
return Carbon::parse($contribution->month_year)->month . "-" . Carbon::parse($contribution->month_year)->year;
})->editColumn('degree_id', function ($contribution) {
return $contribution->degree_id ? $contribution->degree->code_level . "-" . $contribution->degree->code_degree : '';
})->editColumn('unit_id', function ($contribution) {
return $contribution->unit_id ? $contribution->unit->code : '';
})->editColumn('base_wage', function ($contribution) {
return Util::formatMoney($contribution->base_wage);
})->editColumn('seniority_bonus', function ($contribution) {
return Util::formatMoney($contribution->seniority_bonus);
})->editColumn('study_bonus', function ($contribution) {
return Util::formatMoney($contribution->study_bonus);
})->editColumn('position_bonus', function ($contribution) {
return Util::formatMoney($contribution->position_bonus);
})->editColumn('border_bonus', function ($contribution) {
return Util::formatMoney($contribution->border_bonus);
})->editColumn('east_bonus', function ($contribution) {
return Util::formatMoney($contribution->east_bonus);
})->editColumn('public_security_bonus', function ($contribution) {
return Util::formatMoney($contribution->public_security_bonus);
})->editColumn('gain', function ($contribution) {
return Util::formatMoney($contribution->gain);
})->editColumn('quotable', function ($contribution) {
return Util::formatMoney($contribution->quotable);
})->editColumn('retirement_fund', function ($contribution) {
return Util::formatMoney($contribution->retirement_fund);
})->editColumn('mortuary_quota', function ($contribution) {
return Util::formatMoney($contribution->mortuary_quota);
})->editColumn('total', function ($contribution) {
return Util::formatMoney($contribution->total);
})->make(true);
}
public function GenerateMonthlyReport(Request $request)
{
$totalSumC = DB::select('call sum_contributionsC(' . $request->month . ',' . $request->year . ')');
foreach ($totalSumC as $item) {
$count_idC = $item->count_id;
$salaryC = $item->salary;
$seniority_bonusC = $item->seniority_bonus;
$study_bonusC = $item->study_bonus;
$position_bonusC = $item->position_bonus;
$border_bonusC = $item->border_bonus;
$east_bonusC = $item->east_bonus;
$public_security_bonusC = $item->public_security_bonus;
$gainC = $item->gain;
$quotableC = $item->quotable;
$retirement_fundC = $item->retirement_fund;
$mortuary_quotaC = $item->mortuary_quota;
$totalC = $item->total;
}
$totalSumB = DB::select('call sum_contributionsB(' . $request->month . ',' . $request->year . ')');
foreach ($totalSumB as $item) {
$count_idB = $item->count_id;
$salaryB = $item->salary;
$seniority_bonusB = $item->seniority_bonus;
$study_bonusB = $item->study_bonus;
$position_bonusB = $item->position_bonus;
$border_bonusB = $item->border_bonus;
$east_bonusB = $item->east_bonus;
$public_security_bonusB = $item->public_security_bonus;
$gainB = $item->gain;
$quotableB = $item->quotable;
$retirement_fundB = $item->retirement_fund;
$mortuary_quotaB = $item->mortuary_quota;
$totalB = $item->total;
}
$total_count_id = $count_idC + $count_idB;
$total_salary = $salaryC + $salaryB;
$total_seniority_bonus = $seniority_bonusC + $seniority_bonusB;
$total_study_bonus = $study_bonusC + $study_bonusB;
$total_position_bonus = $position_bonusC + $position_bonusB;
$total_border_bonus = $border_bonusC + $border_bonusB;
$total_east_bonus = $east_bonusC + $east_bonusB;
$total_public_security_bonus = $public_security_bonusC + $public_security_bonusB;
$total_gain = $gainC + $gainB;
$total_quotable = $quotableC + $quotableB;
$total_retirement_fund = $retirement_fundC + $retirement_fundB;
$total_mortuary_quota = $mortuary_quotaC + $mortuary_quotaB;
$total = $totalC + $totalB;
$data = ['count_idC' => $count_idC, 'count_idB' => $count_idB, 'total_count_id' => $total_count_id, 'salaryC' => Util::formatMoney($salaryC), 'salaryB' => Util::formatMoney($salaryB), 'total_salary' => Util::formatMoney($total_salary), 'seniority_bonusC' => Util::formatMoney($seniority_bonusC), 'seniority_bonusB' => Util::formatMoney($seniority_bonusB), 'total_seniority_bonus' => Util::formatMoney($total_seniority_bonus), 'study_bonusC' => Util::formatMoney($study_bonusC), 'study_bonusB' => Util::formatMoney($study_bonusB), 'total_study_bonus' => Util::formatMoney($total_study_bonus), 'position_bonusC' => Util::formatMoney($position_bonusC), 'position_bonusB' => Util::formatMoney($position_bonusB), 'total_position_bonus' => Util::formatMoney($total_position_bonus), 'border_bonusC' => Util::formatMoney($border_bonusC), 'border_bonusB' => Util::formatMoney($border_bonusB), 'total_border_bonus' => Util::formatMoney($total_border_bonus), 'east_bonusC' => Util::formatMoney($east_bonusC), 'east_bonusB' => Util::formatMoney($east_bonusB), 'total_east_bonus' => Util::formatMoney($total_east_bonus), 'public_security_bonusC' => Util::formatMoney($public_security_bonusC), 'public_security_bonusB' => Util::formatMoney($public_security_bonusB), 'total_public_security_bonus' => Util::formatMoney($total_public_security_bonus), 'gainC' => Util::formatMoney($gainC), 'gainB' => Util::formatMoney($gainB), 'total_gain' => Util::formatMoney($total_gain), 'quotableC' => Util::formatMoney($quotableC), 'quotableB' => Util::formatMoney($quotableB), 'total_quotable' => Util::formatMoney($total_quotable), 'retirement_fundC' => Util::formatMoney($retirement_fundC), 'retirement_fundB' => Util::formatMoney($retirement_fundB), 'total_retirement_fund' => Util::formatMoney($total_retirement_fund), 'mortuary_quotaC' => Util::formatMoney($mortuary_quotaC), 'mortuary_quotaB' => Util::formatMoney($mortuary_quotaB), 'total_mortuary_quota' => Util::formatMoney($total_mortuary_quota), 'totalC' => Util::formatMoney($totalC), 'totalB' => Util::formatMoney($totalB), 'total' => Util::formatMoney($total), 'year' => $request->year, 'month' => $request->month, 'result' => true];
$data = array_merge($data, self::getViewModel());
return view('reports.monthly_report.index', $data);
}
public function Data()
{
$contribution_rates = ContributionRate::select(['month_year', 'rate_active', 'retirement_fund', 'mortuary_quota', 'mortuary_aid']);
return Datatables::of($contribution_rates)->addColumn('year', function ($contribution_rate) {
return Carbon::parse($contribution_rate->month_year)->year;
})->addColumn('month', function ($contribution_rate) {
return Util::getMes(Carbon::parse($contribution_rate->month_year)->month);
})->editColumn('retirement_fund', function ($contribution_rate) {
return Util::formatMoney($contribution_rate->retirement_fund);
})->editColumn('mortuary_quota', function ($contribution_rate) {
return Util::formatMoney($contribution_rate->mortuary_quota);
})->editColumn('rate_active', function ($contribution_rate) {
return Util::formatMoney($contribution_rate->rate_active);
})->editColumn('mortuary_aid', function ($contribution_rate) {
return Util::formatMoney($contribution_rate->mortuary_aid);
})->addColumn('rate_passive', function ($contribution_rate) {
return Util::formatMoney($contribution_rate->mortuary_aid);
})->make(true);
}
public function save($request, $ipc_rate = false)
{
$rules = ['month_year' => 'required', 'index' => 'required|numeric'];
$messages = ['month_year.required' => 'El campo Año no puede ser vacío', 'index.required' => 'El campo IPC no puede ser vacío', 'index.numeric' => 'El campo IPC sólo se aceptan números'];
$validator = Validator::make($request->all(), $rules, $messages);
if ($validator->fails()) {
return redirect('ipc_rate')->withErrors($validator)->withInput();
} else {
$ipcTasa = IpcRate::where('month_year', '=', Util::datePickPeriod($request->month_year))->first();
if ($ipcTasa) {
$ipcTasa->user_id = Auth::user()->id;
$ipcTasa->index = trim($request->index);
$ipcTasa->save();
$message = "Índice de Precios al Consumidor actualizado con éxito";
Session::flash('message', $message);
} else {
$message = "Fecha no disponible";
Session::flash('message', $message);
}
}
return redirect('ipc_rate');
}
public function getCreationDate()
{
return Util::getDateShort($this->created_at);
}
public function save($request, $base_wage = false)
{
global $month_year, $results;
$reader = $request->file('archive');
$filename = $reader->getRealPath();
$month_year = $request->month_year;
Excel::load($filename, function ($reader) {
global $month_year, $results;
ini_set('upload_max_filesize', '9999M');
ini_set('post_max_size', '9999M');
ini_set('max_execution_time', '-1');
ini_set('max_input_time', '-1');
ini_set('memory_limit', '-1');
set_time_limit(36000);
$results = collect($reader->select(array('niv', 'gra', 'sue'))->get());
});
$degrees = DB::table('degrees')->orderBy('id', 'asc')->get();
foreach ($degrees as $item) {
foreach ($results as $datos) {
if ($datos['niv'] == $item->niv && $datos['gra'] == $item->grad && Util::decimal($datos['sue']) != 0) {
$date = Util::datePickPeriod($month_year);
$year = Carbon::parse($date)->year;
$month = Carbon::parse($date)->month;
$base_wage = DB::table('base_wages')->select(DB::raw('base_wages.degree_id, degrees.code_level, degrees.code_degree, base_wages.month_year'))->leftJoin('degrees', 'base_wages.degree_id', '=', 'degrees.id')->where('code_level', '=', $item->niv)->where('code_degree', '=', $item->grad)->whereMonth('month_year', '=', $month)->whereYear('month_year', '=', $year)->first();
if (!$base_wage) {
$base_wage = new BaseWage();
$base_wage->user_id = Auth::user()->id;
$base_wage->degree_id = $item->id;
$base_wage->month_year = Util::datePickPeriod($month_year);
$base_wage->amount = Util::decimal($datos['sue']);
$base_wage->save();
}
break;
}
}
}
Session::flash('message', "Sueldos importados exitosamente");
return redirect('base_wage');
}
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
global $NotFounf, $NewReimbursement, $Progress, $FolderName, $Date;
$password = $this->ask('Enter the password');
if ($password == ACCESS) {
$FolderName = $this->ask('Enter the name of the folder you want to import');
if ($this->confirm('Are you sure to import the folder ' . $FolderName . '? [y|N]') && $FolderName) {
// ini_set('upload_max_filesize', '99999M');
// ini_set('post_max_size', '99999M');
ini_set('memory_limit', '-1');
ini_set('max_execution_time', '-1');
ini_set('max_input_time', '-1');
set_time_limit('-1');
$time_start = microtime(true);
$this->info("Importing...\n");
$Progress = $this->output->createProgressBar();
$Progress->setFormat("%current%/%max% [%bar%] %percent:3s%%");
Excel::batch('public/file_to_import/' . $FolderName . '/', function ($rows, $file) {
$rows->each(function ($result) {
global $NotFounf, $NewReimbursement, $Progress, $FolderName, $Date;
// ini_set('upload_max_filesize', '9999M');
// ini_set('post_max_size', '9999M');
ini_set('memory_limit', '-1');
ini_set('max_execution_time', '-1');
ini_set('max_input_time', '-1');
set_time_limit('-1');
$month_year = Carbon::createFromDate(Util::formatYear($result->a_o), Util::zero($result->mes), 1)->toDateString();
$Date = Util::zero($result->mes) . "-" . Util::formatYear($result->a_o);
$affiliate = Affiliate::where('identity_card', '=', Util::zero($result->car))->first();
if (!$affiliate) {
$affiliate = Affiliate::where('last_name', '=', $result->pat)->where('mothers_last_name', '=', $result->mat)->where('birth_date', '=', $result->nac)->where('date_entry', '=', $result->ing)->where('identity_card', '=', Util::RepeatedIdentityCard($result->car))->first();
}
if ($affiliate) {
if (Util::decimal($result->sue) != 0) {
$reimbursement = Reimbursement::where('month_year', '=', $month_year)->where('affiliate_id', '=', $affiliate->id)->first();
if (!$reimbursement) {
$reimbursement = new Reimbursement();
$reimbursement->user_id = 1;
$reimbursement->affiliate_id = $affiliate->id;
$reimbursement->month_year = $month_year;
$reimbursement->base_wage = Util::decimal($result->sue);
$reimbursement->seniority_bonus = Util::decimal($result->cat);
$reimbursement->study_bonus = Util::decimal($result->est);
$reimbursement->position_bonus = Util::decimal($result->carg);
$reimbursement->border_bonus = Util::decimal($result->fro);
$reimbursement->east_bonus = Util::decimal($result->ori);
$reimbursement->gain = Util::decimal($result->gan);
$reimbursement->payable_liquid = Util::decimal($result->pag);
$reimbursement->quotable = (double) $reimbursement->base_wage + (double) $reimbursement->seniority_bonus + (double) $reimbursement->study_bonus + (double) $reimbursement->position_bonus + (double) $reimbursement->border_bonus + (double) $reimbursement->east_bonus;
$reimbursement->total = Util::decimal($result->mus);
$percentage = round($reimbursement->total / $reimbursement->quotable * 100, 1);
if ($percentage == 2.5) {
$reimbursement->retirement_fund = $reimbursement->total * 1.85 / $percentage;
$reimbursement->mortuary_quota = $reimbursement->total * 0.65 / $percentage;
}
$reimbursement->save();
$NewReimbursement++;
}
}
} else {
$NotFounf++;
}
$Progress->advance();
});
});
$time_end = microtime(true);
$execution_time = ($time_end - $time_start) / 60;
$NewReimbursement = $NewReimbursement ? $NewReimbursement : "0";
$NotFounf = $NotFounf ? $NotFounf : "0";
$Progress->finish();
$this->info("\n\nReport {$Date}:\n\n\n {$NewReimbursement} new reimbursements.\n\n {$NotFounf} affiliate not found .\n\n Execution time {$execution_time} [minutes].\n");
\Storage::disk('local')->put($Date . '.txt', "\n\nReport:\n\n\n {$NewReimbursement} new reimbursements.\n\n {$NotFounf} affiliate not found .\n\n Execution time {$execution_time} [minutes].\n");
}
} else {
$this->error('Incorrect password!');
}
}
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
global $NewAffi, $UpdateAffi, $NewContri, $Progress, $FolderName, $Date;
$password = $this->ask('Enter the password');
if ($password == ACCESS) {
$FolderName = $this->ask('Enter the name of the folder you want to import');
if ($this->confirm('Are you sure to import the folder "' . $FolderName . '" ? [y|N]') && $FolderName) {
$time_start = microtime(true);
$this->info("Working...\n");
$Progress = $this->output->createProgressBar();
$Progress->setFormat("%current%/%max% [%bar%] %percent:3s%%");
Excel::batch('public/file_to_import/' . $FolderName . '/', function ($rows, $file) {
$rows->each(function ($result) {
global $NewAffi, $UpdateAffi, $NewContri, $Progress, $FolderName, $Date;
ini_set('memory_limit', '-1');
ini_set('max_execution_time', '-1');
ini_set('max_input_time', '-1');
set_time_limit('-1');
if (!isset($result->car) or !isset($result->pat) or !isset($result->mat) or !isset($result->nom) or !isset($result->nom2) or !isset($result->apes) or !isset($result->eciv) or !isset($result->sex) or !isset($result->nac) or !isset($result->ing) or !isset($result->mes) or !isset($result->a_o) or !isset($result->uni) or !isset($result->desg) or !isset($result->niv) or !isset($result->gra) or !isset($result->item) or !isset($result->sue) or !isset($result->cat) or !isset($result->est) or !isset($result->carg) or !isset($result->fro) or !isset($result->ori) or !isset($result->bseg) or !isset($result->dfu) or !isset($result->nat) or !isset($result->lac) or !isset($result->pre) or !isset($result->sub) or !isset($result->gan) or !isset($result->afp) or !isset($result->pag) or !isset($result->nua) or !isset($result->mus)) {
$this->error('Missing columns in the file!');
exit;
}
switch ($FolderName) {
case 'c1':
$first_name = Util::FirstName($result->nom);
$second_name = Util::SecondName($result->nom);
$birth_date = Util::dateDDMMAA($result->nac);
$date_entry = Util::dateDDMMAA($result->ing);
break;
case 'c2':
$first_name = Util::FirstName($result->nom);
$second_name = Util::SecondName($result->nom);
$birth_date = Util::dateAAMMDD($result->nac);
$date_entry = Util::dateAAMMDD($result->ing);
break;
case 'c3':
$first_name = Util::FirstName($result->nom);
$second_name = Util::SecondName($result->nom);
$birth_date = Util::dateAADDMM($result->nac);
$date_entry = Util::dateAADDMM($result->ing);
break;
case 'c4':
$first_name = $result->nom;
$second_name = $result->nom2;
$birth_date = Util::dateAAMMDD($result->nac);
$date_entry = Util::dateAAMMDD($result->ing);
break;
case 'c5':
$first_name = $result->nom;
$second_name = $result->nom2;
$birth_date = Util::dateDDMMAA($result->nac);
$date_entry = Util::dateDDMMAA($result->ing);
break;
default:
$first_name = $result->nom;
$second_name = $result->nom2;
$birth_date = Util::date($result->nac);
$date_entry = Util::date($result->ing);
}
$Date = Util::zero($result->mes) . "-" . Util::formatYear($result->a_o);
$month_year = Carbon::createFromDate(Util::formatYear($result->a_o), Util::zero($result->mes), 1)->toDateString();
if (is_null($result->desg)) {
$result->desg = 0;
}
$breakdown_id = Breakdown::select('id')->where('code', $result->desg)->first()->id;
if ($breakdown_id == 1) {
$unit_id = Unit::select('id')->where('breakdown_id', 1)->where('code', '20190')->first()->id;
} elseif ($breakdown_id == 2) {
$unit_id = Unit::select('id')->where('breakdown_id', 2)->where('code', '20190')->first()->id;
} elseif ($breakdown_id == 3) {
$unit_id = Unit::select('id')->where('breakdown_id', 3)->where('code', '20190')->first()->id;
} else {
if (Unit::select('id')->where('breakdown_id', $breakdown_id)->where('code', $result->uni)->first()) {
$unit_id = Unit::select('id')->where('breakdown_id', $breakdown_id)->where('code', $result->uni)->first()->id;
} else {
$unit_id = Unit::select('id')->where('code', $result->uni)->first()->id;
}
}
if ($result->niv && $result->gra) {
if ($result->niv == '04' && $result->gra == '15') {
$result->niv = '03';
}
$degree_id = Degree::select('id')->where('code_level', $result->niv)->where('code_degree', $result->gra)->first()->id;
}
$category_id = Category::select('id')->where('percentage', Util::CalcCategory(Util::decimal($result->cat), Util::decimal($result->sue)))->first()->id;
$affiliate = Affiliate::where('identity_card', '=', Util::zero($result->car))->first();
if (!$affiliate) {
$affiliate = Affiliate::where('last_name', '=', $result->pat)->where('mothers_last_name', '=', $result->mat)->where('birth_date', '=', $birth_date)->where('date_entry', '=', $date_entry)->where('identity_card', '=', Util::RepeatedIdentityCard($result->car))->first();
if (!$affiliate) {
$affiliate = new Affiliate();
$affiliate->identity_card = Util::zero($result->car);
$affiliate->gender = $result->sex;
$NewAffi++;
} else {
$UpdateAffi++;
}
} else {
$UpdateAffi++;
}
$affiliate->change_date = $month_year;
switch ($result->desg) {
case '1':
//Disponibilidad
$affiliate->affiliate_state_id = 3;
break;
case '3':
//Comisión
$affiliate->affiliate_state_id = 2;
break;
default:
//Servicio
$affiliate->affiliate_state_id = 1;
}
switch ($result->desg) {
case '5':
//Batallón
$affiliate->affiliate_type_id = 2;
break;
default:
//Comando
$affiliate->affiliate_type_id = 1;
}
if ($result->uni) {
$affiliate->unit_id = $unit_id;
}
if ($result->gra) {
$affiliate->degree_id = $degree_id;
}
$affiliate->category_id = $category_id;
$affiliate->user_id = 1;
$affiliate->last_name = Util::replaceCharacter($result->pat);
$affiliate->mothers_last_name = Util::replaceCharacter($result->mat);
$affiliate->first_name = Util::replaceCharacter($first_name);
$affiliate->second_name = Util::replaceCharacter($second_name);
$affiliate->surname_husband = Util::replaceCharacter($result->apes);
$affiliate->civil_status = $result->eciv;
$affiliate->nua = $result->nua;
$affiliate->afp = Util::getAfp($result->afp);
$affiliate->item = $result->item;
$affiliate->birth_date = $birth_date;
$affiliate->date_entry = $date_entry;
$affiliate->registration = Util::CalcRegistration($birth_date, $affiliate->last_name, $affiliate->mothers_last_name, $affiliate->first_name, $affiliate->gender);
$affiliate->save();
if (Util::decimal($result->sue) != 0) {
$contribution = Contribution::where('month_year', '=', $month_year)->where('affiliate_id', '=', $affiliate->id)->first();
if (!$contribution) {
$contribution = new Contribution();
$contribution->user_id = 1;
$contribution->contribution_type_id = 1;
$contribution->affiliate_id = $affiliate->id;
$contribution->month_year = $month_year;
if ($result->uni) {
$contribution->unit_id = $unit_id;
}
$contribution->breakdown_id = $breakdown_id;
if ($result->gra) {
$contribution->degree_id = $degree_id;
}
$contribution->category_id = $category_id;
$contribution->item = $result->item;
$contribution->base_wage = Util::decimal($result->sue);
$contribution->seniority_bonus = Util::decimal($result->cat);
$contribution->study_bonus = Util::decimal($result->est);
$contribution->position_bonus = Util::decimal($result->carg);
$contribution->border_bonus = Util::decimal($result->fro);
$contribution->east_bonus = Util::decimal($result->ori);
$contribution->public_security_bonus = Util::decimal($result->bseg);
$contribution->deceased = $result->dfu;
$contribution->natality = $result->nat;
$contribution->lactation = $result->lac;
$contribution->prenatal = $result->pre;
$contribution->subsidy = Util::decimal($result->sub);
$contribution->gain = Util::decimal($result->gan);
$contribution->payable_liquid = Util::decimal($result->pag);
$contribution->quotable = (double) $contribution->base_wage + (double) $contribution->seniority_bonus + (double) $contribution->study_bonus + (double) $contribution->position_bonus + (double) $contribution->border_bonus + (double) $contribution->east_bonus;
$contribution->total = Util::decimal($result->mus);
$percentage = round($contribution->total / $contribution->quotable * 100, 1);
if ($percentage == 2.5) {
$contribution->retirement_fund = $contribution->total * 1.85 / $percentage;
$contribution->mortuary_quota = $contribution->total * 0.65 / $percentage;
} else {
$this->error('Unknown percentage of contribution!');
exit;
}
$contribution->save();
$NewContri++;
}
}
$Progress->advance();
});
});
$time_end = microtime(true);
$execution_time = ($time_end - $time_start) / 60;
$TotalAffi = $NewAffi + $UpdateAffi;
$TotalNewAffi = $NewAffi ? $NewAffi : "0";
$TotalUpdateAffi = $UpdateAffi ? $UpdateAffi : "0";
$TotalAffi = $TotalAffi ? $TotalAffi : "0";
$TotalNewContri = $NewContri ? $NewContri : "0";
$Progress->finish();
$this->info("\n\nReport {$Date}:\n\n {$TotalNewAffi} new affiliates.\n\n {$TotalUpdateAffi} affiliates successfully updated.\n\n Total {$TotalAffi} affiliates.\n\n Total {$TotalNewContri} entered contributions.\n\n Execution time {$execution_time} [minutes].\n");
\Storage::disk('local')->put('ImportPayroll_' . $Date . '.txt', "\n\nReport:\n\n\n {$TotalNewAffi} new affiliates.\n\n {$TotalUpdateAffi} affiliates successfully updated.\n\n Total {$TotalAffi} affiliates.\n\n Total {$TotalNewContri} entered contributions.\n\n Execution time {$execution_time} [minutes].\n");
}
} else {
$this->error('Incorrect password!');
exit;
}
}
public function getEditDateDeath()
{
return Util::getDateEdit($this->birth_date);
}
public function getMonthYearEdit()
{
return Util::getdateforEditPeriod($this->month_year);
}
public function getData_fech_bajatoPrint()
{
return Util::getfulldate($this->date_decommissioned);
}
public function getAllDate()
{
return Util::getAllDate($this->date);
}
public function getFullDateNactoPrint()
{
return Util::getfulldate($this->fech_nac);
}
public function PrintDirectContribution($id)
{
$header1 = "DIRECCIÓN DE BENEFICIOS ECONÓMICOS";
$header2 = "UNIDAD DE FONDO DE RETIRO POLICIAL INDIVIDUAL";
$title = "PAGO DE APORTES DIRECTOS";
$direct_contribution = DirectContribution::idIs($id)->first();
$affiliate = Affiliate::IdIs($direct_contribution->affiliate_id)->first();
$date = Util::getDateEdit(date('Y-m-d'));
$current_date = Carbon::now();
$hour = Carbon::parse($current_date)->toTimeString();
$view = \View::make('direct_contributions.print.show', compact('header1', 'header2', 'title', 'date', 'hour', 'direct_contribution', 'affiliate'))->render();
$pdf = \App::make('dompdf.wrapper');
$pdf->loadHTML($view)->setPaper('letter');
return $pdf->stream();
}
public function getDataEdit()
{
return Util::getdateforEdit($this->fecha);
}
/**
* Show the application dashboard to the user.
*
* @return Response
*/
public function showIndex()
{
$AfiServ = DB::table('affiliates')->select(DB::raw('COUNT(*) totalAfiS'))->where('affiliates.affiliate_state_id', '=', 1)->get();
foreach ($AfiServ as $item) {
$totalAfiServ = $item->totalAfiS;
}
$AfiComi = DB::table('affiliates')->select(DB::raw('COUNT(*) totalAfiC'))->where('affiliates.affiliate_state_id', '=', 2)->get();
foreach ($AfiComi as $item) {
$totalAfiComi = $item->totalAfiC;
}
$affiliateState = AffiliateState::all();
$affiliateType = AffiliateType::all();
$district = DB::table('units')->select(DB::raw('DISTINCT (units.district) as district'))->get();
$year = Carbon::now()->year;
foreach ($affiliateState as $item) {
$TotalAffiliates = Affiliate::afibyState($item->id, $year)->first();
$Total_AffiliatebyState[$item->id] = $TotalAffiliates->total;
}
foreach ($affiliateType as $item) {
$Afitype = Affiliate::afibyType($item->id, $year)->first();
$Total_AffiliatebyType[$item->id] = $Afitype->type;
}
//Total Contributions of last five year, disaggregated by year.
$lastContribution = DB::table('contributions')->orderBy('month_year', 'desc')->first();
if ($lastContribution) {
$date_contribution = Carbon::parse($lastContribution->month_year)->subYears(5);
$yearc = Carbon::parse($date_contribution)->year;
$listYear = DB::table('contributions')->select(DB::raw('DISTINCT(year(contributions.month_year)) as year'))->whereYear('contributions.month_year', '>', $yearc)->orderBy('contributions.month_year', 'asc')->get();
foreach ($listYear as $item) {
$totalContribution = Contribution::afiContribution($item->year)->first();
$list_totalcontribution[] = $totalContribution->total;
$list_year[] = $totalContribution->month_year;
}
$totalContributionByYear = array($list_year, $list_totalcontribution);
} else {
$list_totalcontribution[] = 0;
$list_year[] = 0;
$totalContributionByYear = array($list_year, $list_totalcontribution);
}
// Total Affiliates disaggregated by district.
foreach ($district as $item) {
$afiDistrict = Affiliate::afiDistrict($item->district, $year)->first();
$list_affiliateByDisctrict[$item->district] = $afiDistrict->district;
}
//Total Retirement Fund of current year, disaggregated by month.
$current_date = Carbon::now();
$current_year = Carbon::parse($current_date)->year;
$monthRetirementFund = DB::table('retirement_funds')->select(DB::raw('DISTINCT(month(retirement_funds.reception_date)) as month'))->whereYear('retirement_funds.reception_date', '=', $current_year)->orderBy('retirement_funds.reception_date', 'asc')->get();
if ($monthRetirementFund) {
foreach ($monthRetirementFund as $item) {
$totalRetirementFundByMonth = RetirementFund::totalRetirementFund($item->month, $current_year)->first();
$list_month[] = Util::getMes($totalRetirementFundByMonth->month);
$list_totalRetirementFundByMonth[] = $totalRetirementFundByMonth->total;
}
$total_retirementFundByMonth = array($list_month, $list_totalRetirementFundByMonth);
} else {
$list_month[] = 0;
$list_totalRetirementFundByMonth[] = 0;
$total_retirementFundByMonth = array($list_month, $list_totalRetirementFundByMonth);
}
//voluntary contribution by current year
$monthVoluntaryContribution = DB::table('contributions')->select(DB::raw('DISTINCT(month(contributions.month_year)) as month'))->where('contributions.contribution_type_id', '=', 2)->whereYear('contributions.month_year', '=', $current_year)->orderBy('contributions.month_year', 'asc')->get();
if ($monthVoluntaryContribution) {
foreach ($monthVoluntaryContribution as $item) {
$totalVoluntaryContributionByMonth = Contribution::voluntaryContribution($item->month, $current_year)->first();
$list_monthVoluntaryContribution[] = Util::getMes($totalVoluntaryContributionByMonth->month);
$list_amountVoluntaryContribution[] = $totalVoluntaryContributionByMonth->total;
}
$total_voluntayContributionByMonth = array($list_monthVoluntaryContribution, $list_amountVoluntaryContribution);
} else {
$list_monthVoluntaryContribution[] = 0;
$list_amountVoluntaryContribution[] = 0;
$total_voluntayContributionByMonth = array($list_monthVoluntaryContribution, $list_amountVoluntaryContribution);
}
$activities = Activity::orderBy('created_at', 'desc')->take(10)->get();
$totalAfi = $totalAfiServ + $totalAfiComi;
$data = ['activities' => $activities, 'totalAfiServ' => $totalAfiServ, 'totalAfiComi' => $totalAfiComi, 'totalAfi' => $totalAfi, 'Total_AffiliatebyState' => $Total_AffiliatebyState, 'Total_AffiliatebyType' => $Total_AffiliatebyType, 'totalContributionByYear' => $totalContributionByYear, 'list_affiliateByDisctrict' => $list_affiliateByDisctrict, 'total_retirementFundByMonth' => $total_retirementFundByMonth, 'total_voluntayContributionByMonth' => $total_voluntayContributionByMonth, 'current_year' => $current_year];
return view('dashboard.index', $data);
//return response()->json($totalAfiServ);
}
public function save($request, $id = false)
{
$rules = ['affiliate_id' => 'numeric', 'retirement_fund_modality_id' => 'numeric'];
$messages = ['affiliate_id.numeric' => 'Solo se aceptan números para id afiliado', 'retirement_fund_modality_id.numeric' => 'Solo se aceptan números para id modalidad'];
$validator = Validator::make($request->all(), $rules, $messages);
if ($validator->fails()) {
return redirect('tramite_fondo_retiro/' . $id)->withErrors($validator)->withInput();
} else {
$retirement_fund = RetirementFund::afiIs($id)->first();
$affiliate = Affiliate::idIs($id)->first();
switch ($request->type) {
case 'form_modality':
if ($request->modality == 4 && $affiliate->date_decommissioned == null) {
$message = "Ingrese Fecha de deceso de Afiliado";
} else {
if ($request->modality) {
$retirement_fund->retirement_fund_modality_id = trim($request->modality);
}
$retirement_fund->city_id = trim($request->city);
$retirement_fund->save();
switch ($request->modality) {
case '1':
$affiliate->affiliate_state_id = 8;
$affiliate->save();
break;
case '2':
$affiliate->affiliate_state_id = 7;
$affiliate->save();
break;
case '3':
$affiliate->affiliate_state_id = 5;
$affiliate->save();
break;
case '4':
$affiliate->affiliate_state_id = 4;
$affiliate->save();
break;
}
$message = "Información General de Fondo de Retiro actualizado con éxito";
}
break;
case 'form_document':
if ($retirement_fund->retirement_fund_modality_id) {
foreach (json_decode($request->data) as $item) {
$Document = Document::where('retirement_fund_id', '=', $retirement_fund->id)->where('requirement_id', '=', $item->requirement_id)->first();
if (!$Document) {
$Document = new Document();
$Document->retirement_fund_id = $retirement_fund->id;
$Document->requirement_id = $item->requirement_id;
}
$Document->status = $item->booleanValue;
$Document->reception_date = date('Y-m-d');
$Document->save();
$retirement_fund->reception_date = date('Y-m-d');
$retirement_fund->save();
}
$message = "Información de requisitos de Fondo de Retiro actualizado con éxito";
} else {
$message = "Seleccione la modalidad y la ciudad";
}
break;
case 'antec':
foreach (json_decode($request->data) as $item) {
$antecedent = Antecedent::where('retirement_fund_id', '=', $retirement_fund->id)->where('antecedent_file_id', '=', $item->prestacion_id)->first();
if (!$antecedent) {
$antecedent = new Antecedent();
$antecedent->retirement_fund_id = $retirement_fund->id;
$antecedent->antecedent_file_id = $item->prestacion_id;
}
$antecedent->status = $item->booleanValue;
$antecedent->save();
}
$retirement_fund->check_file_date = date('Y-m-d');
$retirement_fund->save();
$message = "Información de requisitos de Fondo de Retiro actualizado con éxito";
break;
case 'periods':
$affiliate->service_start_date = Util::datePickPeriod($request->fech_ini_serv);
$affiliate->service_end_date = Util::datePickPeriod($request->fech_fin_serv);
$affiliate->save();
$retirement_fund->anticipation_start_date = Util::datePickPeriod($request->fech_ini_anti);
$retirement_fund->anticipation_end_date = Util::datePickPeriod($request->fech_fin_anti);
$retirement_fund->recognized_start_date = Util::datePickPeriod($request->fech_ini_reco);
$retirement_fund->recognized_end_date = Util::datePickPeriod($request->fech_fin_reco);
$retirement_fund->save();
$retirement_fund->qualification_date = date('Y-m-d');
$retirement_fund->save();
$message = "Información de Periodos de Aporte actualizado con éxito";
break;
}
Session::flash('message', $message);
}
return redirect('retirement_fund/' . $id);
}
public function print_affiliate($affiliate)
{
$header1 = "DIRECCIÓN DE BENEFICIOS ECONÓMICOS";
$header2 = "UNIDAD DE FONDO DE RETIRO POLICIAL INDIVIDUAL";
$title = "REPORTE DE AFILIADO";
$date = Util::getDateEdit(date('Y-m-d'));
$current_date = Carbon::now();
$hour = Carbon::parse($current_date)->toTimeString();
$data = $this->getData($affiliate);
$affiliate = $data['affiliate'];
$spouse = $data['spouse'];
$total_gain = $data['total_gain'];
$total_public_security_bonus = $data['total_public_security_bonus'];
$total_quotable = $data['total_quotable'];
$total_retirement_fund = $data['total_retirement_fund'];
$total_mortuary_quota = $data['total_mortuary_quota'];
$total = $data['total'];
$contributions = Contribution::select(['id', 'month_year', 'degree_id', 'unit_id', 'item', 'base_wage', 'seniority_bonus', 'study_bonus', 'position_bonus', 'border_bonus', 'east_bonus', 'public_security_bonus', 'gain', 'quotable', 'retirement_fund', 'mortuary_quota', 'total'])->where('affiliate_id', $affiliate->id)->get();
$date = Util::getfulldate(date('Y-m-d'));
$view = \View::make('affiliates.print.show', compact('header1', 'header2', 'title', 'date', 'hour', 'affiliate', 'spouse', 'total_gain', 'total_public_security_bonus', 'total_quotable', 'total_retirement_fund', 'total_mortuary_quota', 'total', 'contributions'))->render();
$pdf = \App::make('dompdf.wrapper');
$pdf->loadHTML($view)->setPaper('legal', 'landscape');
return $pdf->stream();
}
public function getYearsAndMonths_fech_ini_reco()
{
return Util::getYearsAndMonths($this->fech_ini_reco, $this->fech_fin_reco);
}
public function period()
{
$second = Contribution::select('month_year')->where('contributions.direct_contribution_id', '=', $this->id)->orderBy('id', 'desc')->first();
$first = Contribution::select('month_year')->where('contributions.direct_contribution_id', '=', $this->id)->orderBy('id', 'asc')->first();
return "De " . Util::getDateAbrePeriod($first->month_year) . " a " . Util::getDateAbrePeriod($second->month_year);
}
public static function createdApplicant($applicant)
{
if (Auth::user()) {
$RetirementFund = RetirementFund::findOrFail($applicant->retirement_fund_id);
$affiliate = Affiliate::findOrFail($RetirementFund->affiliate_id);
$activity = new Activity();
$activity->user_id = Auth::user()->id;
$activity->affiliate_id = $affiliate->id;
$activity->applicant_id = $applicant->id;
$activity->retirement_fund_id = $applicant->retirement_fund_id;
$activity->activity_type_id = ACTIVITY_TYPE_CREATE_APPLICANT;
$activity->message = Util::encodeActivity(Auth::user(), 'Creó al Solicitante', $applicant);
$activity->save();
}
}
