public function Data(Request $request)
{
$retirement_funds = RetirementFund::select(['id', 'affiliate_id', 'retirement_fund_modality_id', 'code', 'created_at', 'total']);
if ($request->has('code')) {
$retirement_funds->where(function ($retirement_funds) use($request) {
$code = trim($request->get('code'));
$retirement_funds->where('code', 'like', "%{$code}%");
});
}
if ($request->has('affiliate_name')) {
$retirement_funds->where(function ($retirement_funds) use($request) {
$affiliate_name = trim($request->get('affiliate_name'));
$retirement_funds->where('affiliate_name', 'like', "%{$affiliate_name}%");
});
}
if ($request->has('retirement_fund_modality_id')) {
$retirement_funds->where(function ($retirement_funds) use($request) {
$retirement_fund_modality_id = trim($request->get('retirement_fund_modality_id'));
$retirement_funds->where('retirement_fund_modality_id', 'like', "%{$retirement_fund_modality_id}%");
});
}
return Datatables::of($retirement_funds)->addColumn('affiliate_name', function ($retirement_fund) {
return $retirement_fund->affiliate->getTittleName();
})->addColumn('total', function ($retirement_fund) {
return Util::formatMoney($retirement_fund->total);
})->addColumn('action', function ($retirement_fund) {
return '<div class="btn-group" style="margin:-3px 0;">
<a href="retirement_fund/' . $retirement_fund->id . '" class="btn btn-primary btn-raised btn-sm"> <i class="glyphicon glyphicon-eye-open"></i> </a>
<a href="" class="btn btn-primary btn-raised btn-sm dropdown-toggle" data-toggle="dropdown"> <span class="caret"></span> </a>
<ul class="dropdown-menu">
<li><a href="retirement_fund/delete/ ' . $retirement_fund->id . ' " style="padding:3px 10px;"><i class="glyphicon glyphicon-ban-circle"></i> Anular</a></li>
</ul>
</div>';
})->make(true);
}
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 Data()
{
$ipc_rates = IpcRate::select(['month_year', 'index']);
return Datatables::of($ipc_rates)->editColumn('year', function ($ipc_rate) {
return Carbon::parse($ipc_rate->month_year)->year;
})->addColumn('month', function ($ipc_rate) {
return Util::getMes(Carbon::parse($ipc_rate->month_year)->month);
})->editColumn('index', function ($ipc_rate) {
return Util::formatMoney($ipc_rate->index);
})->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 Data(Request $request)
{
$direct_contributions = DirectContribution::select(['id', 'affiliate_id', 'type', 'code', 'created_at', 'total']);
if ($request->has('code')) {
$direct_contributions->where(function ($direct_contributions) use($request) {
$code = trim($request->get('code'));
$direct_contributions->where('code', 'like', "%{$code}%");
});
}
if ($request->has('affiliate_name')) {
$direct_contributions->where(function ($direct_contributions) use($request) {
$affiliate_name = trim($request->get('affiliate_name'));
$direct_contributions->where('affiliate_name', 'like', "%{$affiliate_name}%");
});
}
if ($request->has('date')) {
$direct_contributions->where(function ($direct_contributions) use($request) {
$date = Util::datePick($request->get('date'));
$direct_contributions->where('created_at', 'like', "%{$date}%");
});
}
return Datatables::of($direct_contributions)->addColumn('affiliate_name', function ($direct_contribution) {
return $direct_contribution->affiliate->getTittleName();
})->addColumn('period', function ($direct_contribution) {
return $direct_contribution->period();
})->addColumn('total', function ($direct_contribution) {
return Util::formatMoney($direct_contribution->total);
})->editColumn('created_at', function ($direct_contribution) {
return Util::getDateShort($direct_contribution->created_at);
})->addColumn('action', function ($direct_contribution) {
return '<div class="btn-group" style="margin:-3px 0;">
<a href="direct_contribution/' . $direct_contribution->id . '" class="btn btn-primary btn-raised btn-sm"> <i class="glyphicon glyphicon-eye-open"></i> </a>
<a href="" class="btn btn-primary btn-raised btn-sm dropdown-toggle" data-toggle="dropdown"> <span class="caret"></span> </a>
<ul class="dropdown-menu">
<li><a href="direct_contribution/delete/ ' . $direct_contribution->id . ' " style="padding:3px 10px;"><i class="glyphicon glyphicon-ban-circle"></i> Anular</a></li>
</ul>
</div>';
})->make(true);
}
/**
* Display the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function getData($affiliate)
{
$affiliate = Affiliate::idIs($affiliate)->first();
$spouse = Spouse::affiliateidIs($affiliate->id)->first();
if (!$spouse) {
$spouse = new Spouse();
}
if ($affiliate->gender == 'M') {
$gender_list = ['' => '', 'C' => 'CASADO', 'S' => 'SOLTERO', 'V' => 'VIUDO', 'D' => 'DIVORCIADO'];
} elseif ($affiliate->gender == 'F') {
$gender_list = ['' => '', 'C' => 'CASADA', 'S' => 'SOLTERA', 'V' => 'VIUDA', 'D' => 'DIVORCIADA'];
}
if ($affiliate->city_identity_card_id) {
$affiliate->city_identity_card = City::idIs($affiliate->city_identity_card_id)->first()->shortened;
} else {
$affiliate->city_identity_card = '';
}
if ($affiliate->city_birth_id) {
$affiliate->city_birth = City::idIs($affiliate->city_birth_id)->first()->name;
} else {
$affiliate->city_birth = '';
}
if ($affiliate->city_address_id) {
$affiliate->city_address = City::idIs($affiliate->city_address_id)->first()->name;
} else {
$affiliate->city_address = '';
}
if ($affiliate->city_address_id || $affiliate->zone || $affiliate->Street || $affiliate->number_address || $affiliate->phone || $affiliate->cell_phone) {
$info_address = TRUE;
} else {
$info_address = FALSE;
}
if ($spouse->identity_card) {
$info_spouse = TRUE;
} else {
$info_spouse = FALSE;
}
$last_contribution = Contribution::affiliateidIs($affiliate->id)->orderBy('month_year', 'desc')->first();
$consulta = DB::table('affiliates')->select(DB::raw('SUM(contributions.gain) as gain, SUM(contributions.public_security_bonus) as public_security_bonus,
SUM(contributions.quotable) as quotable, SUM(contributions.total) as total,
SUM(contributions.retirement_fund) as retirement_fund, SUM(contributions.mortuary_quota) as mortuary_quota'))->leftJoin('contributions', 'affiliates.id', '=', 'contributions.affiliate_id')->where('affiliates.id', '=', $affiliate->id)->get();
foreach ($consulta as $item) {
$total_gain = Util::formatMoney($item->gain);
$total_public_security_bonus = Util::formatMoney($item->public_security_bonus);
$total_quotable = Util::formatMoney($item->quotable);
$total_retirement_fund = Util::formatMoney($item->retirement_fund);
$total_mortuary_quota = Util::formatMoney($item->mortuary_quota);
$total = Util::formatMoney($item->total);
}
$data = ['affiliate' => $affiliate, 'spouse' => $spouse, 'gender_list' => $gender_list, 'info_address' => $info_address, 'info_spouse' => $info_spouse, 'last_contribution' => $last_contribution, 'total_gain' => $total_gain, 'total_public_security_bonus' => $total_public_security_bonus, 'total_quotable' => $total_quotable, 'total_retirement_fund' => $total_retirement_fund, 'total_mortuary_quota' => $total_mortuary_quota, 'total' => $total];
$data = array_merge($data, self::getViewModel());
return $data;
}
public function FourthLevelData()
{
$select = DB::raw('base_wages.month_year as month_year, c27.amount as c27, c28.amount as c28, c29.amount as c29, c30.amount as c30, c31.amount as c31, c32.amount as c32, c33.amount as c33, c34.amount as c34');
$base_wages = DB::table('base_wages')->select($select)->leftJoin('base_wages as c27', 'c27.month_year', '=', 'base_wages.month_year')->leftJoin('base_wages as c28', 'c28.month_year', '=', 'base_wages.month_year')->leftJoin('base_wages as c29', 'c29.month_year', '=', 'base_wages.month_year')->leftJoin('base_wages as c30', 'c30.month_year', '=', 'base_wages.month_year')->leftJoin('base_wages as c31', 'c31.month_year', '=', 'base_wages.month_year')->leftJoin('base_wages as c32', 'c32.month_year', '=', 'base_wages.month_year')->leftJoin('base_wages as c33', 'c33.month_year', '=', 'base_wages.month_year')->leftJoin('base_wages as c34', 'c34.month_year', '=', 'base_wages.month_year')->where('c27.degree_id', '=', '27')->where('c28.degree_id', '=', '28')->where('c29.degree_id', '=', '29')->where('c30.degree_id', '=', '30')->where('c31.degree_id', '=', '31')->where('c32.degree_id', '=', '32')->where('c33.degree_id', '=', '33')->where('c34.degree_id', '=', '34')->groupBy('base_wages.month_year');
return Datatables::of($base_wages)->editColumn('year', function ($base_wage) {
return Carbon::parse($base_wage->month_year)->year;
})->editColumn('c27', function ($base_wage) {
return Util::formatMoney($base_wage->c27);
})->editColumn('c28', function ($base_wage) {
return Util::formatMoney($base_wage->c28);
})->editColumn('c29', function ($base_wage) {
return Util::formatMoney($base_wage->c29);
})->editColumn('c30', function ($base_wage) {
return Util::formatMoney($base_wage->c30);
})->editColumn('c31', function ($base_wage) {
return Util::formatMoney($base_wage->c31);
})->editColumn('c32', function ($base_wage) {
return Util::formatMoney($base_wage->c32);
})->editColumn('c33', function ($base_wage) {
return Util::formatMoney($base_wage->c33);
})->editColumn('c34', function ($base_wage) {
return Util::formatMoney($base_wage->c34);
})->make(true);
}
/**
* Display the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function show($voucher)
{
$affiliate = Affiliate::IdIs($voucher->affiliate_id)->first();
$voucher->total = Util::formatMoney($voucher->total);
$data = ['voucher' => $voucher, 'affiliate' => $affiliate];
return view('vouchers.show', $data);
}
