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 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 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' => '']; }
/** * 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); }