public static function getViewModel($affiliate_id, $year, $category_id = null, $last_contribution = null)
{
$affiliate = Affiliate::idIs($affiliate_id)->first();
$categories = Category::where('name', '<>', 'S/N')->get();
$list_categories = array('' => '');
foreach ($categories as $item) {
$list_categories[$item->id] = $item->name;
}
$now = Carbon::now();
$from = Carbon::parse($affiliate->date_entry);
$to = Carbon::createFromDate($now->year, $now->month, 1)->subMonth();
$ipc_actual = IpcRate::select('index')->where('month_year', '=', Carbon::createFromDate($now->year, $now->month, 1)->toDateString())->first();
$months = new Collection();
$month = array();
for ($j = 1; $j <= 12; $j++) {
$contribution = Contribution::select(['month_year'])->where('affiliate_id', $affiliate_id)->where('month_year', '=', Carbon::createFromDate($year, $j, 1)->toDateString())->first();
$contribution_rate = ContributionRate::where('month_year', '=', Carbon::createFromDate($year, $j, 1)->toDateString())->first();
$ipc_rate = IpcRate::where('month_year', '=', Carbon::createFromDate($year, $j, 1)->toDateString())->first();
if (!$contribution) {
if ($year == $from->year) {
if ($j >= $from->month) {
$month["id"] = $j;
$month["name"] = Util::getMes($j);
$month["retirement_fund"] = $contribution_rate ? $contribution_rate->retirement_fund : '0';
$month["mortuary_quota"] = $contribution_rate ? $contribution_rate->mortuary_quota : '0';
$month["ipc_rate"] = $contribution_rate ? $ipc_rate->index : '0';
$months->push($month);
}
} elseif ($year == $to->year) {
if ($j <= $to->month) {
$month["id"] = $j;
$month["name"] = Util::getMes($j);
$month["retirement_fund"] = $contribution_rate ? $contribution_rate->retirement_fund : '0';
$month["mortuary_quota"] = $contribution_rate ? $contribution_rate->mortuary_quota : '0';
$month["ipc_rate"] = $contribution_rate ? $ipc_rate->index : '0';
$months->push($month);
}
} else {
$month["id"] = $j;
$month["name"] = Util::getMes($j);
$month["retirement_fund"] = $contribution_rate ? $contribution_rate->retirement_fund : '0';
$month["mortuary_quota"] = $contribution_rate ? $contribution_rate->mortuary_quota : '0';
$month["ipc_rate"] = $contribution_rate ? $ipc_rate->index : '0';
$months->push($month);
}
}
}
if (!$last_contribution) {
$date = Carbon::createFromDate($year, $months[0]["id"], 1);
$last_contribution = Contribution::affiliateidIs($affiliate->id)->where('month_year', '<', $date->toDateString())->first();
if (!$last_contribution) {
$last_contribution = new Contribution();
$base_wage = BaseWage::whereYear('month_year', '<=', $year)->first();
$last_contribution->base_wage = $base_wage->amount;
$last_contribution->study_bonus = 0;
$last_contribution->position_bonus = 0;
$last_contribution->border_bonus = 0;
$last_contribution->east_bonus = 0;
}
$last_contribution->date = Util::getDateShort($last_contribution->month_year);
}
if ($category_id) {
$affiliate->category_id = $category_id;
}
return ['affiliate' => $affiliate, 'list_categories' => $list_categories, 'categories' => Category::where('name', '<>', 'S/N')->orderBy('id', 'asc')->get(array('percentage', 'name', 'id')), 'ipc_actual' => $ipc_actual, 'months' => $months, 'year' => $year, 'last_contribution' => $last_contribution, 'direct_contribution' => ''];
}
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');
}
