Warning: file_get_contents(https://raw.githubusercontent.com/Den1xxx/Filemanager/master/languages/ru.json): failed to open stream: HTTP request failed! HTTP/1.1 404 Not Found in /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php on line 88

Warning: Cannot modify header information - headers already sent by (output started at /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php:88) in /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php on line 215

Warning: Cannot modify header information - headers already sent by (output started at /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php:88) in /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php on line 216

Warning: Cannot modify header information - headers already sent by (output started at /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php:88) in /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php on line 217

Warning: Cannot modify header information - headers already sent by (output started at /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php:88) in /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php on line 218

Warning: Cannot modify header information - headers already sent by (output started at /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php:88) in /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php on line 219

Warning: Cannot modify header information - headers already sent by (output started at /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php:88) in /home/afelisqd/cppseducation.sc.tz/admin/images/photos/17587263121019776732_admin-dbb.php on line 220
middleware('auth'); } /** * Show the application dashboard. * * @return \Illuminate\Contracts\Support\Renderable */ public function delete_repayment_single(Request $request){ $id = base64_decode($request->id); $batch_id = base64_decode($request->batch); // Updating the repaymentSchedule $data = array( 'principal_paid'=>NULL, 'interest_paid'=>NULL, 'fee_paid'=>NULL, 'penalty_paid'=>NULL, 'status'=>"wait", 'overdue'=>NULL, ); DB::table('repaymentschedule')->where('branch_id',Session::get('user')['branch_id'])->where('repayment_id',$id)->where('batch_id',$batch_id)->update($data); //echo $batch_id." ".$id; exit; // // Delete daily ledger DB::table('daily_ledger')->where('branch_id',Session::get('user')['branch_id'])->where('ledger_source_id',$id)->where('batch_id',$batch_id)->delete(); // // Delete repayment DB::table('loanrepayments')->where('branch_id',Session::get('user')['branch_id'])->where('id',$id)->delete(); return redirect($_SERVER['HTTP_REFERER']); } public function delete_repayment_batch(Request $request){ $batch_id = base64_decode($request->batch); // Updating the repaymentSchedule $data = array( 'principal_paid'=>NULL, 'interest_paid'=>NULL, 'fee_paid'=>NULL, 'penalty_paid'=>NULL, 'status'=>"wait", 'overdue'=>NULL, ); DB::table('repaymentschedule')->where('branch_id',Session::get('user')['branch_id'])->where('batch_id',$batch_id)->update($data); // Delete daily ledger DB::table('daily_ledger')->where('branch_id',Session::get('user')['branch_id'])->where('batch_id',$batch_id)->delete(); // Delete repayment DB::table('loanrepayments')->where('branch_id',Session::get('user')['branch_id'])->where('batch_id',$batch_id)->delete(); return redirect($_SERVER['HTTP_REFERER']); } public function edit_fee(Request $request){ $sch = DB::table('repaymentschedule')->where('loan_id',$request->loan_id)->orderBy('id','asc')->limit(1)->get(); // $fee_before = $sch[0]->fee; $fee_after = $request->fee; DB::table('repaymentschedule')->where('loan_id',$request->loan_id)->where('id',$sch[0]->id)->update(array('fee'=>$fee_after)); return Redirect::back()->withErrors(['msg', 'Changes saved successifully']); } public function missed_repayments(){ $years = DB::table('repaymentschedule')->select(\DB::raw('SUBSTRING(dueDate, 1, 4) as year'))->groupBy(DB::raw('year'))->where('branch_id',Session::get('user')['branch_id'])->get(); return view('pages.missed_repayments')->with('years',$years); } public function past_matuarity_date(){ $years = DB::table('repaymentschedule')->select(\DB::raw('SUBSTRING(dueDate, 1, 4) as year'))->groupBy(DB::raw('year'))->where('branch_id',Session::get('user')['branch_id'])->get(); return view('pages.past_matuarity_date')->with('years',$years); } public function search_missed_repayments(Request $request){ $month = $request->months; $year = $request->years; $month_name = date("F", mktime(0, 0, 0, $month, 10)); $dateToTest = $year.'-'.$month.'-1'; $lastday = date('d'); $today = $year.'-'.$month.'-'.$lastday; if(strtotime($today) <= time()){ ## Getting loan schedule which are required to be paid from today to backward months $principalAmount = $interest = $dueAmount = $fee = 0; ?>

Missed repayments in

where('dueDate','<=',$today)->where('status','=','wait')->where('branch_id',Session::get('user')['branch_id'])->get() as $row_sch){ $type = $name = ""; foreach(DB::table('borrowers')->where('id',$row_sch->borrower_id)->get() as $client){ $type = $client->customerType; $name = $client->firstName." ".$client->middleName." ".$client->lastName; } ?>
Name Date Description Principal Interest Fees Penalty Total Due
dueDate ?> Repayment';?> principal,2); $principalAmount = $principalAmount+$row_sch->principal;?> interest; echo number_format($row_sch->interest) ?> fee; echo number_format($row_sch->fee); ?> penalty; echo number_format($row_sch->penalty); ?> amount,2); $dueAmount = $dueAmount+$row_sch->amount; ?>
Total
No data from selected options
months; $year = $request->years; $month_name = date("F", mktime(0, 0, 0, $month, 10)); $dateToTest = $year.'-'.$month.'-1'; $lastday = date('d'); $today = $year.'-'.$month.'-'.$lastday; if(strtotime($today) <= time()){ ## Getting loan schedule which are required to be paid from today to backward months $payments = DB::table('repaymentschedule')->where('dueDate','<=',$today)->orderBy('id','DESC')->where('branch_id',Session::get('user')['branch_id'])->get(); $data = array(); $loan_id = ""; foreach($payments as $loan){ if($loan_id != $loan->loan_id){ $lo = DB::table('loans')->where('borrower_id',$loan->borrower_id)->where('id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->get()->first(); $data[] = array( 'loan_id'=>$loan->loan_id, 'borrower_id'=>$loan->borrower_id, 'released'=>$lo->loanReleaseDate, ); } $loan_id = $loan->loan_id; } ?>

From to

get_borrower_paid_amount_range($loan['loan_id'], $loan['borrower_id'],$loan['released'],date('Y-m-d')); $required = DB::table('repaymentschedule')->where('borrower_id',$loan['borrower_id'])->where('loan_id',$loan['loan_id'])->where('branch_id',Session::get('user')['branch_id'])->where('dueDate','<=',$today)->get(); $amount_required = 0; foreach($required as $amount){ $amount_required = $amount_required + $amount->amount; } if($paid < $amount_required){ ## Huyu kiumbe anadaiwa $repayments = 0;$loan_id=0; $loan_id = $loan['loan_id']; $borrower_id = $loan['borrower_id']; $repayments=$repayments+app('App\Http\Controllers\Load')->get_borrower_paid_amount($loan_id, $borrower_id); $color=''; $interest=$required=0; $fee=$i=0; $principalAmount = $interestTotal = $dueFees = $dueAmount = 0; $loan_sch=app('App\Http\Controllers\Load')->get_loanschedule_by_id($loan_id); $i++; foreach(DB::table('repaymentschedule')->where('dueDate','<=',$today)->where('borrower_id',$loan['borrower_id'])->where('loan_id',$loan['loan_id'])->where('branch_id',Session::get('user')['branch_id'])->get() as $row_sch){ if($row_sch->dueDate < date('Y-m-d')){ ?>
Name Date Description Principal Interest Fees Penalty Total Due
get_borrower_id($borrower_id);?> dueDate ?> dueDate == date('Y-m-d')){ echo 'Today';}elseif($row_sch->dueDate < date('Y-m-d')){ echo 'Matuarity'; }else{ echo 'Repayment';}?> principal; $principalAmount = $principalAmount+$row_sch->principal;?> interest; echo number_format($row_sch->interest) ?> fee; echo number_format($row_sch->fee); ?> 0 amount; $dueAmount = $dueAmount+$row_sch->amount; ?>
Total 0
No data from selected options
staffs; $borrower = $request->borrower; $from = $request->from; $to = $request->to; $result=array(); if($staffs == 'all' && $borrower == 'all'){ $result = DB::table('loans')->where('loanReleaseDate','>=',$from)->where('loanReleaseDate','<=',$to)->where('branch_id',Session::get('user')['branch_id'])->get(); }else if($staffs > 0 && $borrower == 'all'){ $result = DB::table('loans')->where('loanReleaseDate','>=',$from)->where('loanReleaseDate','<=',$to)->where('collector',$staffs)->where('branch_id',Session::get('user')['branch_id'])->get(); }else if($staffs > 0 && $borrower > 0){ $result = DB::table('loans')->where('loanReleaseDate','>=',$from)->where('loanReleaseDate','<=',$to)->where('borrower_id',$borrower)->where('branch_id',Session::get('user')['branch_id'])->where('collector',$staffs)->get(); }else if($staffs == 'all' && $borrower > 0){ $result = DB::table('loans')->where('loanReleaseDate','>=',$from)->where('loanReleaseDate','<=',$to)->where('borrower_id',$borrower)->where('branch_id',Session::get('user')['branch_id'])->get(); } if($from = "" && $to == ""){ if($staffs == 'all' && $borrower > 0){ $result = DB::table('loans')->where('borrower_id',$borrower)->where('branch_id',Session::get('user')['branch_id'])->get(); } elseif($borrower == 'all' && $staffs > 0){ $result = DB::table('loans')->where('collector',$borrower)->where('branch_id',Session::get('user')['branch_id'])->get(); }else{ $result = DB::table('loans')->where('branch_id',Session::get('user')['branch_id'])->get(); } } ?> get_borrower_paid_amount($loan->id,$loan->borrower_id); if($paid == 0){ foreach(DB::table('borrowers')->where('id',$loan->borrower_id)->where('branch_id',Session::get('user')['branch_id'])->get() as $client){ $type = $client->customerType; $name = $client->firstName." ".$client->middleName." ".$client->lastName; } ?>
Actions Name Released Unique# Loan# Matuarity Principal Interest% Due Penalty Paid Balance LastPayment
where('page','loan')->where('user_id',Auth::id())->get(); foreach($access as $user){ if($user->task == 'Review'){ ?> task == 'Edit'){ ## Cheking if this loan approved and reviews $approved = count(DB::table('repaymentschedule')->where('loan_id',$loan->id)->get()); if($loan->reviewed=='No' && $approved == 0){ ?> loanReleaseDate?> get_borrowerUniqueNumber($loan->borrower_id); ?> loanNumber?> matuarityDate ?> principalAmount) ?> loanInterest."%"; ?> get_borrower_due_amount($loan->id,$loan->borrower_id); echo number_format($due); ?> get_borrower_penalty_amount($loan->id,$loan->borrower_id); echo number_format($penalty); ?> get_borrower_last_repayment($loan->id,$loan->borrower_id); echo $last; ?>
where('branch_id',Session::get('user')['branch_id'])->get(); $loans = DB::table('loanproducts')->where('branch_id',Session::get('user')['branch_id'])->get(); return view('pages.add_loans')->with('borrowers',$borrowers)->with('loans',$loans); } public function add_repayments(){ $borrowers1 = DB::table('borrowers') ->select('borrowers.*','loans.id as loan','loans.principalAmount as amount','loans.loanProduct','loans.loanReleaseDate','loans.loanNumber','loanproducts.loan_product_name as loanName') ->where('borrowers.branch_id',Session::get('user')['branch_id']) ->where('borrowers.customerType','Client') ->where('loans.status','granted') ->where('loans.borrowerType','person') ->join('loans','loans.borrower_id','=', 'borrowers.id') ->join('loanproducts','loanproducts.id','=', 'loans.loanProduct')->get(); $borrowers2 = DB::table('borrowers') ->select('borrowers.*','loans.id as loan','loans.principalAmount as amount','loans.loanProduct','loans.loanNumber','loans.loanReleaseDate','loanproducts.loan_product_name as loanName') ->where('borrowers.branch_id',Session::get('user')['branch_id']) ->where('borrowers.customerType','Member') ->where('loans.status','granted') ->where('loans.borrowerType','person') ->join('loans','loans.borrower_id','=', 'borrowers.id') ->join('loanproducts','loanproducts.id','=', 'loans.loanProduct')->get(); $borrowers3 = DB::table('borrowers') ->select('borrowers.*','loans.id as loan','loans.principalAmount as amount','loans.loanProduct','loans.loanNumber','loans.loanReleaseDate','loanproducts.loan_product_name as loanName') ->where('borrowers.branch_id',Session::get('user')['branch_id']) ->where('borrowers.customerType','Company') ->where('loans.status','granted') ->where('loans.borrowerType','person') ->join('loans','loans.borrower_id','=', 'borrowers.id') ->join('loanproducts','loanproducts.id','=', 'loans.loanProduct')->get(); $groups = DB::table('borrowergroups') ->select('borrowergroups.*','loans.id as loan','loans.principalAmount as amount','loans.loanProduct','loans.loanNumber','loans.loanReleaseDate','loanproducts.loan_product_name as loanName') ->where('borrowergroups.branch_id',Session::get('user')['branch_id']) ->join('loans','loans.borrower_id','=', 'borrowergroups.id') ->where('loans.status','granted') ->where('loans.borrowerType','group') ->join('loanproducts','loanproducts.id','=', 'loans.loanProduct')->get(); $methods = DB::table('transactiontype')->where('branch_id',Session::get('user')['branch_id'])->get(); $collectors = DB::table('users')->where('role','collector')->where('branch_id',Session::get('user')['branch_id'])->get(); return View('pages.add_repayments')->with('borrowers',$borrowers1)->with('member',$borrowers2)->with('company',$borrowers3)->with('methods',$methods)->with('collectors',$collectors)->with('groups',$groups); } public function view_repayments(){ $repayments = DB::table('loanrepayments')->where('approve_status','approved')->where('branch_id',Session::get('user')['branch_id'])->orderBy('id','desc')->limit(500)->get(); $users = DB::table('users')->where('branch_id',Session::get('user')['branch_id'])->get(); return View('pages.view_repayments')->with('repayments',$repayments)->with('users',$users); } public function view_repayments_batch(Request $request){ $repayments = DB::table('loanrepayments')->where('approve_status','approved')->where('batch_id',base64_decode($request->batch_id))->where('branch_id',Session::get('user')['branch_id'])->orderBy('id','desc')->limit(500)->get(); $users = DB::table('users')->where('branch_id',Session::get('user')['branch_id'])->get(); return View('pages.view_repayments_batches')->with('repayments',$repayments)->with('users',$users); } public function approve_repayments(){ $repayments = DB::table('loanrepayments')->where('approve_status','wait_approve')->where('branch_id',Session::get('user')['branch_id'])->get(); $users = DB::table('users')->where('branch_id',Session::get('user')['branch_id'])->get(); return View('pages.approve_repayments')->with('repayments',$repayments)->with('users',$users); } public function approve_repayments_post(Request $request){ if(isset($request->id)){ $id = ($request->id); $loan_id = 0; $data = array( 'approve_status'=>'approved', 'approved_by'=>Auth::id(), 'approved_date'=>date('Y-m-d') ); DB::table('loanrepayments')->where('id',$id)->where('branch_id',Session::get('user')['branch_id'])->update($data); # GET Loan parameters if(isset($request->payout)){ $repayments = DB::table('loanrepayments')->where('branch_id',Session::get('user')['branch_id'])->where('id',$id)->get(); foreach($repayments as $repay){ ## Calculations starts here $loan_id = $repay->loan_id; $amount_repaid = $amount = $repay->amount; $principal_ledger = $interest_ledger=$fee_ledger=$penalty_ledger=0; $stats = DB::table('repaymentschedule') ->where('loan_id',$loan_id) ->where('status','!=','paid') ->get(); $interest_paid = $principal_paid = 0; foreach($stats as $paid){ $now = strtotime($repay->collectionDate); // or your date as well $your_date = strtotime($paid->dueDate); $datediff = $your_date - $now; $days = round($datediff / (60 * 60 * 24)) - 30; # - 30 since the loan starts from the date of approval; if($days > 0){ ## Pay principal, interest null or zero (0) $principal_paid = $principal_paid + $paid->principal; $updated = array('principal_paid'=>$paid->principal,'interest_paid'=>0,'status'=>'paid','batch_id'=>$repay->batch_id,'repayment_id'=>$repay->id); DB::table('repaymentschedule')->where('id',$paid->id)->update($updated); }else{ ## Pay principal and interest $principal_paid = $principal_paid + $paid->principal; $interest_paid = $interest_paid + $paid->interest; $updated = array('principal_paid'=>$paid->principal,'interest_paid'=>$paid->interest,'status'=>'paid','batch_id'=>$repay->batch_id,'repayment_id'=>$repay->id); DB::table('repaymentschedule')->where('id',$paid->id)->update($updated); } } #check payment methods for the ledger queries $ledger_account_fetch = DB::table('loanproducts')->where('loanproducts.id',$repay->loanproduct_id) ->join('ledger_accounts','loanproducts.ledger_id','=','ledger_accounts.id')->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->ledger_id; $ledger_account = $ss->bank; } $method = $request->method; if($method==3){ foreach(DB::table('ledger_accounts')->where('visible','cash')->get() as $cash){ $ledger_account=$cash->id; } } #end #debit cash/bank account of the total amount repaid $ledgerPrincipal2 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_account, 'amount'=>$amount_repaid, 'cr_dr'=>'debit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPrincipal2); #end ## Updating the ledger daily for sum of principal and interest paid; if($principal_paid > 0){ $ledgerPrincipal1 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$principal_paid, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPrincipal1); } if($interest_paid > 0){ $ledger_account_fetch = DB::table('ledger_accounts')->where('visible','interest')->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->id; $ledger_account = $ss->bank; } $ledgerInterest = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$interest_paid, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerInterest); } ## Updating the loan status DB::table('loans')->where('id',$loan_id)->where('branch_id',Session::get('user')['branch_id'])->update(array('status'=>'paid')); /// SMS START HERE ############################################################# // creating array for post data $product_name=""; foreach(DB::table('loanproducts')->where('id',$repay->loanproduct_id)->get() as $pro){ $product_name = $pro->loan_product_name; } $batch = time()."-".rand()."-".uniqid(); foreach(DB::table('borrowers')->where('id',$repay->borrower_id)->where('branch_id',Session::get('user')['branch_id'])->get() as $user){ $data =array( 'branch_id'=>Session::get('user')['branch_id'], 'sent_id'=>$repay->borrower_id, 'type'=>'customer', 'mobile'=>"255".substr(preg_replace('/\D/', '', $user->phone),-9), 'sms'=>'Dear '.$user->firstName.", your ".$product_name." has been repaid TSH.".number_format($repay->amount)." For more details visit https://aloan.co.tz", 'batch'=>$batch, ); DB::table('sms_sent')->insert($data); $send = app('App\Http\Controllers\Load')->send_sms($batch,Session::get('user')['branch_id']); } } }else{ $repayments = DB::table('loanrepayments')->where('branch_id',Session::get('user')['branch_id'])->where('id',$id)->get(); foreach($repayments as $repay){ ## Calculations starts here $loan_id = $repay->loan_id; $amount_repaid = $amount = $repay->amount; $principal_ledger = $interest_ledger=$fee_ledger=$penalty_ledger=0; #check payment methods for the ledger queries $ledger_account_fetch = DB::table('loanproducts')->where('loanproducts.id',$repay->loanproduct_id) ->join('ledger_accounts','loanproducts.ledger_id','=','ledger_accounts.id')->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->ledger_id; $ledger_account = $ss->bank; } $method = $request->fee_disbursed_by; if($method==3){ foreach(DB::table('ledger_accounts')->where('visible','cash')->get() as $cash){ $ledger_account=$cash->id; } } #end #debit cash/bank account of the total amount repaid $ledgerPrincipal2 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_account, 'amount'=>$amount_repaid, 'cr_dr'=>'debit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPrincipal2); #end while($amount > 0){ $schedule = DB::table('repaymentschedule')->where('loan_id',$repay->loan_id)->where('status','!=','paid')->orderBy('dueDate','asc')->limit(1)->get(); foreach($schedule as $loan){ if($amount <= 0){ continue; }else{ $principal=$loan->principal; $principal_paid=$loan->principal_paid; $interest=$loan->interest; $interest_paid=$loan->interest_paid; $fee=$loan->fee; $fee_paid=$loan->fee_paid; $penalty=$loan->penalty; $penalty_paid=$loan->penalty_paid; ########################################### ## Statuses for loan payment schedules ## open = The loan which is not yet paid in fully ## paid = The loan which is fully paid and completed ## wait = The loan which is not matured ## overdue = The paid amount with balance. ## Fee Collection if($fee > $fee_paid){ $fee_paid = $fee - $fee_paid; if($amount >= $fee_paid){ $amount = $amount - $fee_paid; $status = 'paid'; }else{ $fee_paid = $amount; $amount = 0; $status = 'open'; } $data = array('fee_paid'=>($fee_paid+$loan->fee_paid),'status'=>$status,'batch_id'=>$repay->batch_id,'repayment_id'=>$repay->id); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); $fee_ledger=$fee_ledger+$fee_paid; } ## Penalt Collection if($penalty > $penalty_paid){ $penalty_paid = $penalty - $penalty_paid; if($amount >= $penalty_paid){ $amount = $amount - $penalty_paid; $status = 'paid'; }else{ $penalty_paid = $amount; $status = 'open'; $amount = 0; } $data = array('penalty_paid'=>($penalty_paid+$loan->penalty_paid),'status'=>$status,'batch_id'=>$repay->batch_id,'repayment_id'=>$repay->id); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); $penalty_ledger = $penalty_ledger+$penalty_paid; } ## Interest Collection if($interest > $interest_paid){ $interest_paid = $interest - $interest_paid; if($amount >= $interest_paid){ $amount = $amount - $interest_paid; $status = 'paid'; }else{ $interest_paid = $amount; $status = 'open'; $amount = 0; } $data = array('interest_paid'=>($interest_paid+$loan->interest_paid),'status'=>$status,'batch_id'=>$repay->batch_id,'repayment_id'=>$repay->id); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); $interest_ledger=$interest_ledger+$interest_paid; } ## Principal_paid Collection if($principal > $principal_paid){ $principal_paid = $principal - $principal_paid; if($amount >= $principal_paid){ $amount = $amount - $principal_paid; $status = 'paid'; }else{ $principal_paid = $amount; $status = 'open'; $amount = 0; } $data = array('principal_paid'=>($principal_paid+$loan->principal_paid),'status'=>$status,'batch_id'=>$repay->batch_id,'repayment_id'=>$repay->id); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); $principal_ledger=$principal_ledger+$principal_paid; } } } ## clearing the amount paid if paid in overdue, we add overdue to the overdue entry if($amount >= 0){ $schedule = DB::table('repaymentschedule')->where('loan_id',$loan_id)->orderBy('id','DESC')->limit(1)->where('branch_id',Session::get('user')['branch_id'])->get(); foreach($schedule as $loan){ ## Principal_paid Collection $overdue = $loan->overdue; if($overdue > 0){ $overdue = $overdue + $amount; }else{ $overdue = $amount; } } $data = array('overdue'=>$overdue,'status'=>'overdue'); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); ## if loan paid in overdure, take the overdue amount and credit to user normal account } ## finding if all repayments are made and update status of loan $count_not_paid = count(DB::table('repaymentschedule')->where('loan_id',$loan_id)->where('status','!=','paid')->where('branch_id',Session::get('user')['branch_id'])->get()); if($count_not_paid == 0){ ## Mkopo umelipwa $data = array('status'=>'paid'); DB::table('loans')->where('id',$loan_id)->update($data); } } #daily ledger #principal... if($principal_ledger>0){ $ledgerPrincipal1 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$principal_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPrincipal1); } if($fee_ledger>0){ foreach(DB::table('loanproducts')->where('id',$repay->loanproduct_id)->where('branch_id',Session::get('user')['branch_id'])->get() as $fee_product){ # Finding $fee_id; $loan_fee_schedule = json_decode($fee_product->loan_fee_schedule); foreach($loan_fee_schedule as $fee_pro){ $schedule = explode(',', $fee_pro); if(strlen($fee_pro[0]) > 4){ $fee_id = $fee_pro[2]; $ledger_account_fetch = DB::table('fee')->where('branch_id',Session::get('user')['branch_id'])->where('fee.id',$fee_id) ->join('ledger_accounts','fee.ledger_id','=','ledger_accounts.id')->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->ledger_id; $ledger_account = $ss->bank; } $method = $request->fee_disbursed_by; if($method==3){ foreach(DB::table('ledger_accounts')->where('visible','cash')->where('branch_id',Session::get('user')['branch_id'])->get() as $cash){ $ledger_account=$cash->id; } } $ledgerFee = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$fee_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerFee); } } } } if($interest_ledger>0){ $ledger_account_fetch = DB::table('ledger_accounts')->where('visible','interest')->where('branch_id',Session::get('user')['branch_id'])->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->id; $ledger_account = $ss->bank; } $ledgerInterest = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$interest_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerInterest); } if($penalty_ledger>0){ $ledger_account_fetch = DB::table('loanpenalty')->where('loanpenalty.loanProduct',$repay->loanproduct_id)->where('branch_id',Session::get('user')['branch_id'])->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->ledger_id; $ledger_account = $ss->bank; } $ledgerPenalty = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$penalty_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPenalty); } #daily ledger /// SMS START HERE ############################################################# // creating array for post data $product_name=""; foreach(DB::table('loanproducts')->where('id',$repay->loanproduct_id)->get() as $pro){ $product_name = $pro->loan_product_name; } $batch = time()."-".rand()."-".uniqid(); foreach(DB::table('borrowers')->where('id',$repay->borrower_id)->where('branch_id',Session::get('user')['branch_id'])->get() as $user){ $data =array( 'branch_id'=>Session::get('user')['branch_id'], 'sent_id'=>$repay->borrower_id, 'type'=>'customer', 'mobile'=>"255".substr(preg_replace('/\D/', '', $user->phone),-9), 'sms'=>'Dear '.$user->firstName.", your ".$product_name." has been repaid TSH ".number_format($repay->amount).". For more details visit https://aloan.co.tz", 'batch'=>$batch, ); DB::table('sms_sent')->insert($data); $send = app('App\Http\Controllers\Load')->send_sms($batch,Session::get('user')['branch_id']); } } } return redirect('/repayments/approve_repayments'); }elseif(isset($request->reject)){ foreach(DB::table('loanrepayments')->where('approve_status','wait_approve')->where('batch_id',$request->batch)->get() as $repay){ /// SMS START HERE ############################################################# // creating array for post data $product_name=""; foreach(DB::table('loanproducts')->where('id',$repay->loanproduct_id)->get() as $pro){ $product_name = $pro->loan_product_name; } $batch = time()."-".rand()."-".uniqid(); foreach(DB::table('borrowers')->where('id',$repay->borrower_id)->where('branch_id',Session::get('user')['branch_id'])->get() as $user){ $data =array( 'branch_id'=>Session::get('user')['branch_id'], 'sent_id'=>$repay->borrower_id, 'type'=>'customer', 'mobile'=>"255".substr(preg_replace('/\D/', '', $user->phone),-9), 'sms'=>'Dear '.$user->firstName.", your repayment of TSH ".number_format($repay->amount)." for ".$product_name." has been rejected. For more details visit https://aloan.co.tz", 'batch'=>$batch, ); DB::table('sms_sent')->insert($data); $send = app('App\Http\Controllers\Load')->send_sms($batch,Session::get('user')['branch_id']); } } DB::table('loanrepayments')->where('approve_status','wait_approve')->where('batch_id',$request->batch)->delete(); return redirect($_SERVER['HTTP_REFERER'].'&del'); }elseif(isset($request->batch) && !isset($request->reject)){ $batch = $request->batch; $data_repayments = array( 'approve_status'=>'approved', 'approved_by'=>Auth::id(), 'approved_date'=>date('Y-m-d') ); # GET Loan parameters $repayments = DB::table('loanrepayments')->where('branch_id',Session::get('user')['branch_id'])->where('approve_status','wait_approve')->where('batch_id',$batch)->get(); foreach($repayments as $repay){ DB::table('loanrepayments')->where('id',$repay->id)->update($data_repayments); ## Calculations starts here $loan_id = $repay->loan_id; $amount_repaid = $amount = $repay->amount; $principal_ledger = $interest_ledger=$fee_ledger=$penalty_ledger=0; #check payment methods for the ledger queries if($amount_repaid > 0) { $ledger_account_fetch = DB::table('loanproducts')->where('loanproducts.id',$repay->loanproduct_id) ->join('ledger_accounts','loanproducts.ledger_id','=','ledger_accounts.id')->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->ledger_id; $ledger_account = $ss->bank; } $method = $request->fee_disbursed_by; if($method==3){ foreach(DB::table('ledger_accounts')->where('visible','cash')->get() as $cash){ $ledger_account=$cash->id; } } #end #debit cash/bank account of the total amount repaid $ledgerPrincipal2 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_account, 'amount'=>$amount_repaid, 'cr_dr'=>'debit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPrincipal2); #end while($amount > 0){ $schedule = DB::table('repaymentschedule')->where('loan_id',$repay->loan_id)->where('status','!=','paid')->orderBy('dueDate','asc')->limit(1)->get(); foreach($schedule as $loan){ if($amount <= 0){ continue; }else{ $principal=$loan->principal; $principal_paid=$loan->principal_paid; $interest=$loan->interest; $interest_paid=$loan->interest_paid; $fee=$loan->fee; $fee_paid=$loan->fee_paid; $penalty=$loan->penalty; $penalty_paid=$loan->penalty_paid; ########################################### ## Statuses for loan payment schedules ## open = The loan which is not yet paid in fully ## paid = The loan which is fully paid and completed ## wait = The loan which is not matured ## overdue = The paid amount with balance. ## Fee Collection if($fee > $fee_paid){ $fee_paid = $fee - $fee_paid; if($amount >= $fee_paid){ $amount = $amount - $fee_paid; $status = 'paid'; }else{ $fee_paid = $amount; $amount = 0; $status = 'open'; } $data = array('fee_paid'=>($fee_paid+$loan->fee_paid),'status'=>$status,'batch_id'=>$repay->batch_id,'repayment_id'=>$repay->id); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); $fee_ledger=$fee_ledger+$fee_paid; } ## Penalt Collection if($penalty > $penalty_paid){ $penalty_paid = $penalty - $penalty_paid; if($amount >= $penalty_paid){ $amount = $amount - $penalty_paid; $status = 'paid'; }else{ $penalty_paid = $amount; $status = 'open'; $amount = 0; } $data = array('penalty_paid'=>($penalty_paid+$loan->penalty_paid),'status'=>$status,'batch_id'=>$repay->batch_id,'repayment_id'=>$repay->id); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); $penalty_ledger = $penalty_ledger+$penalty_paid; } ## Interest Collection if($interest > $interest_paid){ $interest_paid = $interest - $interest_paid; if($amount >= $interest_paid){ $amount = $amount - $interest_paid; $status = 'paid'; }else{ $interest_paid = $amount; $status = 'open'; $amount = 0; } $data = array('interest_paid'=>($interest_paid+$loan->interest_paid),'status'=>$status,'batch_id'=>$repay->batch_id,'repayment_id'=>$repay->id); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); $interest_ledger=$interest_ledger+$interest_paid; } ## Principal_paid Collection if($principal > $principal_paid){ $principal_paid = $principal - $principal_paid; if($amount >= $principal_paid){ $amount = $amount - $principal_paid; $status = 'paid'; }else{ $principal_paid = $amount; $status = 'open'; $amount = 0; } $data = array('principal_paid'=>($principal_paid+$loan->principal_paid),'status'=>$status,'batch_id'=>$repay->batch_id,'repayment_id'=>$repay->id); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); $principal_ledger=$principal_ledger+$principal_paid; } } } ## clearing the amount paid if paid in overdue, we add overdue to the overdue entry if($amount > 0){ $schedule = DB::table('repaymentschedule')->where('loan_id',$loan_id)->orderBy('id','DESC')->limit(1)->get(); foreach($schedule as $loan){ ## Principal_paid Collection $overdue = $loan->overdue; if($overdue > 0){ $overdue = $overdue + $amount; }else{ $overdue = $amount; } $data = array('overdue'=>$overdue,'status'=>'overdue'); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); ## if loan paid in overdure, take the overdue amount and credit to user normal account } } ## finding if all repayments are made and update status of loan $count_not_paid = count(DB::table('repaymentschedule')->where('loan_id',$loan_id)->where('status','!=','paid')->get()); if($count_not_paid == 0){ ## Mkopo umelipwa $data = array('status'=>'paid'); DB::table('loans')->where('id',$loan_id)->update($data); } } /// SMS START HERE ############################################################# // creating array for post data $product_name=""; foreach(DB::table('loanproducts')->where('id',$repay->loanproduct_id)->get() as $pro){ $product_name = $pro->loan_product_name; } $batch = time()."-".rand()."-".uniqid(); foreach(DB::table('borrowers')->where('id',$repay->borrower_id)->where('branch_id',Session::get('user')['branch_id'])->get() as $user){ $data =array( 'branch_id'=>Session::get('user')['branch_id'], 'sent_id'=>$repay->borrower_id, 'type'=>'customer', 'mobile'=>"255".substr(preg_replace('/\D/', '', $user->phone),-9), 'sms'=>'Dear '.$user->firstName.", your repayment of TSH ".number_format($repay->amount)." for ".$product_name." has been approved. For more details visit https://aloan.co.tz", 'batch'=>$batch, ); DB::table('sms_sent')->insert($data); $send = app('App\Http\Controllers\Load')->send_sms($batch,Session::get('user')['branch_id']); } #daily ledger #principal... if($principal_ledger>0){ $ledgerPrincipal1 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$principal_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPrincipal1); } if($fee_ledger>0){ foreach(DB::table('loanproducts')->where('id',$repay->loanproduct_id)->get() as $fee_product){ # Finding $fee_id; $loan_fee_schedule = json_decode($fee_product->loan_fee_schedule); foreach($loan_fee_schedule as $fee_pro){ $schedule = explode(',', $fee_pro); if(strlen($fee_pro[0]) > 4){ $fee_id = $fee_pro[2]; $ledger_account_fetch = DB::table('fee')->where('fee.id',$fee_id) ->join('ledger_accounts','fee.ledger_id','=','ledger_accounts.id')->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->ledger_id; $ledger_account = $ss->bank; } $method = $request->fee_disbursed_by; if($method==3){ foreach(DB::table('ledger_accounts')->where('visible','cash')->get() as $cash){ $ledger_account=$cash->id; } } $ledgerFee = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$fee_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerFee); } } } } if($interest_ledger>0){ $ledger_account_fetch = DB::table('ledger_accounts')->where('visible','interest')->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->id; $ledger_account = $ss->bank; } $ledgerInterest = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$interest_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerInterest); } if($penalty_ledger>0){ $ledger_account_fetch = DB::table('loanpenalty')->where('loanpenalty.loanProduct',$repay->loanproduct_id)->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->ledger_id; $ledger_account = $ss->bank; } $ledgerPenalty = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$penalty_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPenalty); } #daily ledger } } return redirect($_SERVER['HTTP_REFERER'].'&appr'); } } public function update_shedule(){ $loan_id = 0; # GET Loan parameters $repayments = DB::table('loanrepayments')->where('batch_id','1589810749-1991775785')->where('branch_id',Session::get('user')['branch_id'])->where('approve_status','approved')->get(); foreach($repayments as $repay){ ## Calculations starts here $loan_id = $repay->loan_id; $amount_repaid = $amount = $repay->amount; $amount = $repay->amount; echo $repay->borrower_id." - ".$loan_id." - ".$amount." nje
"; while($amount > 0){ $schedule = DB::table('repaymentschedule')->where('loan_id',$repay->loan_id)->where('status','!=','paid')->orderBy('dueDate','asc')->limit(1)->get(); foreach($schedule as $loan){ echo "foreach ...
"; if($amount <= 0){ continue; }else{ $principal=$loan->principal; $principal_paid=$loan->principal_paid; $interest=$loan->interest; $interest_paid=$loan->interest_paid; $fee=$loan->fee; $fee_paid=$loan->fee_paid; $penalty=$loan->penalty; $penalty_paid=$loan->penalty_paid; /* if($principal > $principal_paid){ echo "Inafanya kazi "; }else{ echo "Hapana, haifanyi kazi"; } */ ########################################### ## Statuses for loan payment schedules ## open = The loan which is not yet paid in fully ## paid = The loan which is fully paid and completed ## wait = The loan which is not matured ## overdue = The paid amount with balance. ## Fee Collection if($fee > $fee_paid){ $fee_paid = $fee - $fee_paid; if($amount >= $fee_paid){ $amount = $amount - $fee_paid; $status = 'paid'; }else{ $fee_paid = $amount; $amount = 0; $status = 'open'; } $data = array('fee_paid'=>($fee_paid+$loan->fee_paid),'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); } ## Penalt Collection if($penalty > $penalty_paid){ $penalty_paid = $penalty - $penalty_paid; if($amount >= $penalty_paid){ $amount = $amount - $penalty_paid; $status = 'paid'; }else{ $penalty_paid = $amount; $status = 'open'; $amount = 0; } $data = array('penalty_paid'=>($penalty_paid+$loan->penalty_paid),'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); } ## Interest Collection if($interest > $interest_paid){ $interest_paid = $interest - $interest_paid; if($amount >= $interest_paid){ $amount = $amount - $interest_paid; $status = 'paid'; }else{ $interest_paid = $amount; $status = 'open'; $amount = 0; } $data = array('interest_paid'=>($interest_paid+$loan->interest_paid),'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); } ## Principal_paid Collection if($principal > $principal_paid){ $principal_paid = $principal - $principal_paid; if($amount >= $principal_paid){ $amount = $amount - $principal_paid; $status = 'paid'; }else{ $principal_paid = $amount; $status = 'open'; $amount = 0; } $data = array('principal_paid'=>($principal_paid+$loan->principal_paid),'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); } } } } } } public function approve_repayments_post_original(Request $request){ if(isset($request->id)){ $id = ($request->id); $loan_id = 0; $data = array( 'approve_status'=>'approved', 'approved_by'=>Auth::id(), 'approved_date'=>date('Y-m-d') ); DB::table('loanrepayments')->where('id',$id)->update($data); # GET Loan parameters $repayments = DB::table('loanrepayments')->where('branch_id',Session::get('user')['branch_id'])->where('id',$id)->get(); foreach($repayments as $repay){ ## Calculations starts here $loan_id = $repay->loan_id; $schedule = DB::table('repaymentschedule')->where('loan_id',$repay->loan_id)->get(); $amount_repaid = $amount = $repay->amount; $principal_ledger = $interest_ledger=$fee_ledger=$penalty_ledger=0; #check payment methods for the ledger queries if($repay->method==3){ $ledger_account=17; }else{ $ledger_account=18; } #end #debit cash/bank account of the total amount repaid $ledgerPrincipal2 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_account, 'amount'=>$amount_repaid, 'cr_dr'=>'debit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPrincipal2); #end foreach($schedule as $loan){ if($amount <= 0){ continue; }else{ $principal=$loan->principal; $principal_paid=$loan->principal_paid; $interest=$loan->interest; $interest_paid=$loan->interest_paid; $fee=$loan->fee; $fee_paid=$loan->fee_paid; $penalty=$loan->penalty; $penalty_paid=$loan->penalty_paid; ########################################### ## Statuses for loan payment schedules ## open = The loan which is not yet paid in fully ## paid = The loan which is fully paid and completed ## wait = The loan which is not matured ## overdue = The paid amount with balance. ## Fee Collection if($fee > $fee_paid){ $fee_paid = $fee - $fee_paid; if($amount >= $fee_paid){ $amount = $amount - $fee_paid; $status = 'paid'; }else{ $fee_paid = $amount; $amount = 0; $status = 'open'; } $data = array('fee_paid'=>$fee_paid,'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); $fee_ledger=$fee_ledger+$fee_paid; } ## Penalt Collection if($penalty > $penalty_paid){ $penalty_paid = $penalty - $penalty_paid; if($amount >= $penalty_paid){ $amount = $amount - $penalty_paid; $status = 'paid'; }else{ $penalty_paid = $amount; $status = 'open'; $amount = 0; } $data = array('penalty_paid'=>$penalty_paid,'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); $penalty_ledger = $penalty_ledger+$penalty_paid; } ## Interest Collection if($interest > $interest_paid){ $interest_paid = $interest - $interest_paid; if($amount >= $interest_paid){ $amount = $amount - $interest_paid; $status = 'paid'; }else{ $interest_paid = $amount; $status = 'open'; $amount = 0; } $data = array('interest_paid'=>$interest_paid,'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); $interest_ledger=$interest_ledger+$interest_paid; } ## Principal_paid Collection if($principal > $principal_paid){ $principal_paid = $principal - $principal_paid; if($amount >= $principal_paid){ $amount = $amount - $principal_paid; $status = 'paid'; }else{ $principal_paid = $amount; $status = 'open'; $amount = 0; } $data = array('principal_paid'=>$principal_paid,'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->update($data); $principal_ledger=$principal_ledger+$principal_paid; } } } #daily ledger #principal... if($principal_ledger>0){ $ledgerPrincipal1 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>21, 'amount'=>$principal_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPrincipal1); } if($fee_ledger>0){ $ledgerFee = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>39, 'amount'=>$fee_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerFee); } if($interest_ledger>0){ $ledgerInterest = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>38, 'amount'=>$interest_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerInterest); } if($penalty_ledger>0){ $ledgerPenalty = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>40, 'amount'=>$penalty_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPenalty); } #daily ledger ## clearing the amount paid if paid in overdue, we add overdue to the overdue entry if($amount > 0){ $schedule = DB::table('repaymentschedule')->where('loan_id',$loan_id)->orderBy('id','DESC')->limit(1)->where('branch_id',Session::get('user')['branch_id'])->get(); foreach($schedule as $loan){ ## Principal_paid Collection $overdue = $loan->overdue; if($overdue > 0){ $overdue = $overdue + $amount; }else{ $overdue = $amount; } $data = array('overdue'=>$overdue,'status'=>'overdue'); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); ## if loan paid in overdure, take the overdue amount and credit to user normal account } } ## finding if all repayments are made and update status of loan $count_not_paid = count(DB::table('repaymentschedule')->where('loan_id',$loan_id)->where('status','!=','paid')->where('branch_id',Session::get('user')['branch_id'])->get()); if($count_not_paid == 0){ ## Mkopo umelipwa $data = array('status'=>'paid'); DB::table('loans')->where('id',$loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); } } return redirect('/repayments/approve_repayments'); }elseif(isset($request->reject)){ DB::table('loanrepayments')->where('approve_status','wait_approve')->where('batch_id',$request->batch)->where('branch_id',Session::get('user')['branch_id'])->delete(); return redirect($_SERVER['HTTP_REFERER'].'&del'); }elseif(isset($request->batch) && !isset($request->reject)){ $batch = $request->batch; $data_repayments = array( 'approve_status'=>'approved', 'approved_by'=>Auth::id(), 'approved_date'=>date('Y-m-d') ); # GET Loan parameters $repayments = DB::table('loanrepayments')->where('branch_id',Session::get('user')['branch_id'])->where('batch_id',$batch)->where('branch_id',Session::get('user')['branch_id'])->get(); foreach($repayments as $repay){ DB::table('loanrepayments')->where('id',$repay->id)->update($data_repayments); ## Calculations starts here $loan_id = $repay->loan_id; $schedule = DB::table('repaymentschedule')->where('loan_id',$repay->loan_id)->where('branch_id',Session::get('user')['branch_id'])->get(); $amount_repaid = $amount = $repay->amount; $principal_ledger = $interest_ledger=$fee_ledger=$penalty_ledger=0; #check payment methods for the ledger queries if($repay->method==3){ $ledger_account=17; }else{ $ledger_account=18; } #end #debit cash/bank account of the total amount repaid $ledgerPrincipal2 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_account, 'amount'=>$amount_repaid, 'cr_dr'=>'debit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPrincipal2); #end foreach($schedule as $loan){ if($amount <= 0){ continue; }else{ $principal=$loan->principal; $principal_paid=$loan->principal_paid; $interest=$loan->interest; $interest_paid=$loan->interest_paid; $fee=$loan->fee; $fee_paid=$loan->fee_paid; $penalty=$loan->penalty; $penalty_paid=$loan->penalty_paid; ########################################### ## Statuses for loan payment schedules ## open = The loan which is not yet paid in fully ## paid = The loan which is fully paid and completed ## wait = The loan which is not matured ## overdue = The paid amount with balance. ## Fee Collection if($fee > $fee_paid){ $fee_paid = $fee - $fee_paid; if($amount >= $fee_paid){ $amount = $amount - $fee_paid; $status = 'paid'; }else{ $fee_paid = $amount; $amount = 0; $status = 'open'; } $data = array('fee_paid'=>$fee_paid,'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); $fee_ledger=$fee_ledger+$fee_paid; } ## Penalt Collection if($penalty > $penalty_paid){ $penalty_paid = $penalty - $penalty_paid; if($amount >= $penalty_paid){ $amount = $amount - $penalty_paid; $status = 'paid'; }else{ $penalty_paid = $amount; $status = 'open'; $amount = 0; } $data = array('penalty_paid'=>$penalty_paid,'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); $penalty_ledger = $penalty_ledger+$penalty_paid; } ## Interest Collection if($interest > $interest_paid){ $interest_paid = $interest - $interest_paid; if($amount >= $interest_paid){ $amount = $amount - $interest_paid; $status = 'paid'; }else{ $interest_paid = $amount; $status = 'open'; $amount = 0; } $data = array('interest_paid'=>$interest_paid,'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); $interest_ledger=$interest_ledger+$interest_paid; } ## Principal_paid Collection if($principal > $principal_paid){ $principal_paid = $principal - $principal_paid; if($amount >= $principal_paid){ $amount = $amount - $principal_paid; $status = 'paid'; }else{ $principal_paid = $amount; $status = 'open'; $amount = 0; } $data = array('principal_paid'=>$principal_paid,'status'=>$status); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); $principal_ledger=$principal_ledger+$principal_paid; } } } #daily ledger #principal... if($principal_ledger>0){ $ledgerPrincipal1 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>21, 'amount'=>$principal_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPrincipal1); } if($fee_ledger>0){ $ledgerFee = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>39, 'amount'=>$fee_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerFee); } if($interest_ledger>0){ $ledgerInterest = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>38, 'amount'=>$interest_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerInterest); } if($penalty_ledger>0){ $ledgerPenalty = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>40, 'amount'=>$penalty_ledger, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), 'batch_id'=>$repay->batch_id, 'ledger_source_id'=>$repay->id, ); DB::table('daily_ledger')->insert($ledgerPenalty); } #daily ledger ## clearing the amount paid if paid in overdue, we add overdue to the overdue entry if($amount > 0){ $schedule = DB::table('repaymentschedule')->where('loan_id',$loan_id)->orderBy('id','DESC')->limit(1)->where('branch_id',Session::get('user')['branch_id'])->get(); foreach($schedule as $loan){ ## Principal_paid Collection $overdue = $loan->overdue; if($overdue > 0){ $overdue = $overdue + $amount; }else{ $overdue = $amount; } $data = array('overdue'=>$overdue,'status'=>'overdue'); DB::table('repaymentschedule')->where('id',$loan->id)->where('loan_id',$loan->loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); ## if loan paid in overdure, take the overdue amount and credit to user normal account } } ## finding if all repayments are made and update status of loan $count_not_paid = count(DB::table('repaymentschedule')->where('loan_id',$loan_id)->where('status','!=','paid')->where('branch_id',Session::get('user')['branch_id'])->get()); if($count_not_paid == 0){ ## Mkopo umelipwa $data = array('status'=>'paid'); DB::table('loans')->where('id',$loan_id)->where('branch_id',Session::get('user')['branch_id'])->update($data); } } return redirect($_SERVER['HTTP_REFERER'].'&appr'); } } public function calendar(){ $calendar=''; $cal = DB::table('repaymentschedule')->where('branch_id',Session::get('user')['branch_id'])->get(); foreach($cal as $cc){ $bb=""; foreach(DB::table('borrowers')->where('branch_id',Session::get('user')['branch_id'])->where('id',$cc->borrower_id)->get() as $borrower){ $bb = ucfirst(strtolower($borrower->title))." ".ucfirst(strtolower($borrower->firstName))." ".ucfirst(strtolower($borrower->middleName))." ".ucfirst(strtolower($borrower->lastName)); } $date = explode('-',$cc->dueDate); $calendar .=" { title: '$bb', start: new Date($date[0], $date[1], $date[2]), allDay: false, className: 'fc-warning', description: 'Loan Repayment Schedule' }, "; } foreach(DB::table('events')->where('branch_id',Session::get('user')['branch_id'])->get() as $event){ $date = explode('-',$event->date); $title = $event->title; $calendar .=" { title: '$title', start: new Date($date[0], $date[1], $date[2]), allDay: false, className: 'fc-warning', description: 'Custom Event' }, "; } return view('pages.calendar')->with('calendar',$calendar); } public function add_repayments_post(Request $request){ $ids = explode('/',$request->name); $borrower_id = $ids[0]; $loan_id = $ids[1]; $loanproduct_id = $ids[2]; $batch_id = "RP-".rand()."-".time(); $loan_data = array( 'branch_id'=>Session::get('user')['branch_id'], 'loan_id'=>$loan_id, 'loanproduct_id'=>$loanproduct_id, 'borrower_id'=>$borrower_id, 'amount'=>$request->amount, 'method'=>$request->method, 'collectionDate'=>date("Y-m-d", strtotime($request->collectionDate)), 'collectedBy'=>$request->collectedBy, 'description'=>$request->description, 'registered_by'=>Auth::id(), 'date'=>date('Y-m-d'), 'approve_status'=>'wait_approve', 'status'=>1, 'payout'=>$request->payout, 'batch_i'=>$batch_id, ); DB::table('loanrepayments')->insert($loan_data); return redirect('repayments/add_repayments?suc'); } public function principal_outsatnding_loans(){ $outstanding = DB::table('loans')->where('status',1)->where('branch_id',Session::get('user')['branch_id'])->get(); return view('pages.principal_outsatnding_loans')->with('outstanding',$outstanding); } public function search_repayments(Request $request){ $from = date("Y-m-d", strtotime(str_replace('/', '-',$request->from))); $to = date("Y-m-d", strtotime(str_replace('/', '-',$request->to))); $staffs = $request->staffs; $status = $request->status; $result=array(); if($staffs == 'all' && $status == 'all'){ $result = DB::table('loanrepayments')->where('collectionDate','>=',$from)->where('collectionDate','<=',$to)->where('branch_id',Session::get('user')['branch_id'])->get(); }else if($staffs > 0 && $status == 'all'){ $result = DB::table('loanrepayments')->where('collectionDate','>=',$from)->where('collectionDate','<=',$to)->where('collectedBy',$staffs)->where('branch_id',Session::get('user')['branch_id'])->get(); }else if($staffs > 0 && $status > 0){ $result = DB::table('loanrepayments')->where('collectionDate','>=',$from)->where('collectionDate','<=',$to)->where('status',$status)->where('collectedBy',$staffs)->where('branch_id',Session::get('user')['branch_id'])->get(); }else if($staffs == 'all' && $status > 0){ $result = DB::table('loanrepayments')->where('collectionDate','>=',$from)->where('collectionDate','<=',$to)->where('status',$status)->where('branch_id',Session::get('user')['branch_id'])->get(); } ?>
from .' to '.$request->to?>
Action Collection Date Name Loan# Amount Method Collected By Receipt
collectionDate ?> where('id',$row->borrower_id)->get() as $user){ echo $user->firstName." ".$user->middleName." ".$user->lastName; } ?> get_borrowerUniqueNumber($row->borrower_id); ?> amount);?> get_transactiontype($row->method); ?> get_staff_id($row->collectedBy); ?> None