/**
* 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;
}
}