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'); function generateRandomNumber($length = 6) { $number = '1234567890'; $numberLength = strlen($number); $randomNumber = ''; for ($i = 0; $i < $length; $i++) { $randomNumber .= $number[rand(0, $numberLength - 1)]; } $randomNumber='101'.$randomNumber; $count = DB::table('savingaccount')->where('savingAccount', $randomNumber)->get()->count(); if($count>0){ generateRandomNumber(); }else{ return $randomNumber; } } } /** * Show the application dashboard. * * @return \Illuminate\Contracts\Support\Renderable */ public function update_loans(){ ## updating loans which already paid foreach(DB::table('loans')->where('status','granted')->get() as $loans){ $schedule1 = count(DB::table('repaymentschedule')->where('loan_id',$loans->id)->where('status','wait')->get()); $schedule2 = count(DB::table('repaymentschedule')->where('loan_id',$loans->id)->where('status','open')->get()); if(($schedule1+$schedule2) == 0){ # Paid DB::table('loans')->where('id',$loans->id)->update(array('status'=>'paid')); } } } public function import_loans(){ foreach(DB::table('loans')->where('branch_id',Session::get('user')['branch_id'])->get() as $loan){ $data = DB::table('repaymentschedule') ->select(DB::raw("SUM(principal_paid) as principal_paid"),DB::raw("SUM(interest_paid) as interest_paid")) ->where('loan_id',$loan->id) ->get(); foreach(DB::table('loans')->where('id',$loan->id)->get() as $lo){ foreach($data as $repay){ $amount = $repay->principal_paid + $repay->interest_paid; $repayments = array( 'branch_id'=>Session::get('user')['branch_id'], 'loan_id'=>$loan->id, 'loanproduct_id'=>$lo->loanProduct, 'borrower_id'=>$lo->borrower_id, 'amount'=>$amount, 'method'=>6, 'collectionDate'=>date("Y-m-d"), 'collectedBy'=>Auth::id(), 'description'=>'', 'registered_by'=>Auth::id(), 'date'=>date('Y-m-d'), 'status'=>1, 'approve_status'=>'approved', 'approved_by'=>Auth::id(), 'approved_date'=>date('Y-m-d') ); print_r($repayments); echo "
"; // DB::table('loanrepayments')->insert($repayments); echo $repay->principal_paid + $repay->interest_paid; echo "
"; } } echo "***********
"; } } public function import_loans_update_Customer(){ $inputFileName = './public/Customer-APRIL loan-list (10).xlsx'; $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $reader->setReadDataOnly(true); $worksheet = $reader->load($inputFileName); $values = $worksheet->getActiveSheet(); $rows = $values->toArray(); foreach($rows as $key => $value){ $loanNumber = $value[1]; $amount = $value[3]; $onePercent=0; if($amount > 0){ $onePercent = $amount*1/100; echo "".$loanNumber." Total Amount:".number_format($amount,2)." 1%= ".number_format($onePercent,2)."
"; $loanId = "Not Found"; // get load details foreach(DB::table('loans')->where('loanNumber',$loanNumber)->get() as $loan){ $loanId = $loan->id; echo "Loan ID: ".$loanId."
"; // get first repayment amount from schedule foreach(DB::table('repaymentschedule')->orderBy('id','asc')->limit(1)->where('loan_id',$loanId)->get() as $schedule){ $principal = $schedule->principal; $amount = $schedule->amount; $principal_paid = $schedule->principal_paid; $status = $schedule->status; echo "Principal: ".$principal."
"; echo "Principal Paid: ".$principal_paid."
"; echo "Status: ".$status."
"; // // updating new principal required // $newPrincipal = $principal + $onePercent; // $newAmount = $amount + $onePercent; // $newStatus = 'wait'; // DB::table('repaymentschedule')->where('id',$schedule->id)->where('loan_id',$loanId)->update(array('amount'=>$newAmount,'status'=>$newStatus,'principal'=>$newPrincipal)); } } }else{ $amount = 0; } } } public function import_loansMMM(){ $inputFileName = './public/salarized_zamani.xlsx'; $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $reader->setReadDataOnly(true); $worksheet = $reader->load($inputFileName); $values = $worksheet->getActiveSheet(); $rows = $values->toArray(); foreach($rows as $key => $value){ ## Hapa tunaingiza values za loans pamoja na shcedule $name = $value[0]; $first_date = date('d-m-Y',strtotime($value[1])); $duration = strtoupper($value[2]); $loan_amount = $value[4]; $remained = $value[6]; $interest=13; $product = 4; $borrower_id=$value[7]; $n=$value[8]; $years = 0; $repayment_number = 0; echo $name."

"; $factor = substr($duration,-1); if($factor == 'M'){ $years = 1; $repayment_number = str_replace('M','',$duration); if($repayment_number == 6){ $interest = 10; $product = 10; }elseif($repayment_number == 3){ $interest = 5; $product = 5; }else{ $interest = 13; $product = 4; } }else{ $years = str_replace('Y','',$duration); $interest = 13; $repayment_number = 12; } if($n == 'k'){ $product = 11; } $data = array( 'loan_amount' => $loan_amount, 'term_years' => $years, 'interest' => $interest, 'terms' => $repayment_number ); $amortization = new Amortization($data); $inputs = $amortization->results['inputs']; $summary = $amortization->results['summary']; $schedule = $amortization->results['schedule']; $i=0; ?> where('id',$borrower_id)->get(); foreach($borrowers as $user){ ############################################################################### $loan_id = 0; foreach(DB::table('loans')->where('loanProduct',$product)->where('borrower_id',$borrower_id)->where('principalAmount','LIKE',"%".floor($remained)."%")->get() as $loan){ $loan_id=$loan->id; } if($loan_id > 0){ $style=""; $insert=$deleted=false; foreach($schedule as $term_detail){ $i++; $time = strtotime($first_date); $final = date("Y-m-d", strtotime("+".$i." month", $time)); $deleting_date = $final; if(abs(floor($term_detail['balance']) - floor($remained)) < 10 ){ $style = "style='background:red;color:black;'"; $insert=true; } ?> > $term_detail['principal'], 'interest'=>$term_detail['interest'], 'amount'=>($term_detail['payment']), ); #deleting the first columns, wrong inserting... if(!$deleted){ echo "Deleted
"; $deleted = true; DB::table('repaymentschedule')->where('dueDate',$deleting_date)->where('loanProduct',$product)->where('borrower_id',$borrower_id)->where('loan_id',$loan_id)->delete(); } $updated = DB::table('repaymentschedule')->where('dueDate',$final)->where('loanProduct',$product)->where('borrower_id',$borrower_id)->where('loan_id',$loan_id)->update($repaymentData); } } }else{ echo "Not Found
"; } ?>

S/N	Date	Principal	Interest	Total Payment	Outstanding Balance	Inserted
".$name." ".$first_date." ".$years." ".$factor." ".number_format($loan_amount,2)." ".number_format($remained,2).""; ?>
	>	>	>	>	>

S/N	Date	Principal	Interest	Total Payment	Outstanding Balance	Inserted
".$name." ".$first_date." ".$years." ".$factor." ".number_format($loan_amount,2)." ".number_format($remained,2).""; ?>
	>	>	>	>	>

#######################################################################################################

setReadDataOnly(true); $worksheet = $reader->load($inputFileName); $values = $worksheet->getActiveSheet(); $rows = $values->toArray(); foreach($rows as $key => $value){ ## Hapa tunaingiza values za loans pamoja na shcedule $name = $value[0]; $first_date = date('d-m-Y',strtotime($value[1])); $duration = strtoupper($value[2]); $loan_amount = $value[4]; $remained = $value[6]; $paidInExcel = $value[5]; $interest=13; $product = 4; $borrower_id=$value[7]; $n=$value[8]; $years = 0; $repayment_number = 0; $method=""; $factor = substr($duration,-1); if($factor == 'M'){ $years = 1; $repayment_number = str_replace('M','',$duration); if($repayment_number == 6){ $interest = 10; $product = 10; $method = "normal_rating"; }elseif($repayment_number == 3){ $interest = 5; $product = 5; $method = "normal_rating"; }else{ $interest = 13; $product = 4; $method = "reducing_balance"; } }else{ $years = str_replace('Y','',$duration); $interest = 13; $repayment_number = 12; } $borrowers = DB::table('borrowers')->where('id',$borrower_id)->get(); foreach($borrowers as $user){ echo "*************************************************

"; $loan = array( 'branch_id'=>Session::get('user')['branch_id'], 'loanProduct'=>$product, 'borrower_id'=>$borrower_id, 'borrowerType'=>'person', 'disbursed_by'=>'Cheque', 'principalAmount'=>$remained, 'dueAmount'=>0, 'loanReleaseDate'=>date('Y-m-d',strtotime($first_date)), 'matuarityDate'=>'', 'interestMethod'=>$method, 'loanInterest'=>$interest, 'loanDuration'=>$years, 'repaymentsNumber'=>$repayment_number, 'description'=>'uploaded', 'registered_by'=>Auth::id(), 'date'=>date('Y-m-d'), 'loanNumber'=>generateRandomNumber() ); ############################################################################### DB::table('loans')->insert($loan); $loanID = DB::getPdo()->lastInsertId(); $principal = $loan_amount; $total_payment = 3; if($product == 10){ $repaymentsNumber = 6; }else{ $repaymentsNumber = 3; } $interestAmount = ($loan_amount*$interest/100)/$repaymentsNumber; $balance = $principal; $check=0; $cc=0; $dueAmount=$insert=0; $difference=$loan_amount; $proncipal = $loan_amount/$repaymentsNumber; for($i=1;$i<=$repaymentsNumber;$i++){ echo $difference." ".$remained." "; if(($difference - 100) < $remained || ($difference+100) < $remained){ $insert = 1; echo " Insert ..."; }else{ $insert = 0; } echo "
"; $payment = ($interestAmount) + $principal; $dueDate = date("Y-m-d", strtotime('+'.$i.' month', strtotime($first_date))); $feeData=0; $dueAmount = $dueAmount + $payment; $repaymentData = array( 'branch_id'=>Session::get('user')['branch_id'], 'loan_id'=>$loanID, 'loanProduct'=>$product, 'borrower_id'=>$borrower_id, 'principal'=>$proncipal, 'interest'=>$interestAmount, 'fee'=>$feeData, 'dueDate'=> $dueDate, 'amount'=>($feeData+$proncipal+($interestAmount)), 'registered_by'=>Auth::id(), 'date'=>date('Y-m-d'), 'status'=>'wait', ); $check = $check + $loan_amount/$repaymentsNumber; if($insert){ DB::table('repaymentschedule')->insert($repaymentData); $loanData = array( 'dueFees'=>0, 'dueAmount'=>$dueAmount, 'matuarityDate'=> date('Y-m-d',strtotime($dueDate)), 'status'=>'granted', ); DB::table('loans')->where('id',$loanID)->update($loanData); } $difference = $difference - floor($proncipal); } echo "

"; if($insert > 0){ #check payment methods for the ledger queries $ledger_account=18; #end #debit cash/bank account of the total amount repaid $ledgerBc = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_account, 'amount'=>$remained, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), ); DB::table('daily_ledger')->insert($ledgerBc); #debit Customer loan account foreach(DB::table('loanproducts')->where('id',$product)->get() as $ledger){ $legderID=$ledger->ledger_id; $ledgerCl = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$legderID, 'amount'=>$remained, 'cr_dr'=>'debit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), ); DB::table('daily_ledger')->insert($ledgerCl); } #end } ?>

#######################################################################################################

setReadDataOnly(true); $worksheet = $reader->load($inputFileName); $values = $worksheet->getActiveSheet(); $rows = $values->toArray(); foreach($rows as $key => $value){ // key is the row count(starts from 0) // array of values $loanNumber = $value[1]; $amount = $value[3]; ## Inserting to repayments; foreach(DB::table('loans')->where('loanNumber',$loanNumber)->where('status','granted')->get() as $loan){ $borrower_id = $loan->borrower_id; $loan_id = $loan->id; $loanproduct_id = $loan->loanProduct; ## Validate if $amount in int if($amount > 0){ $loan_data = array( 'branch_id'=>Session::get('user')['branch_id'], 'loan_id'=>$loan_id, 'loanproduct_id'=>$loanproduct_id, 'borrower_id'=>$borrower_id, 'amount'=>$amount, 'method'=>4, 'collectionDate'=>date("Y-m-d"), 'collectedBy'=>Auth::id(), 'description'=>'uploaded from excel', 'registered_by'=>Auth::id(), 'date'=>date('Y-m-d'), 'approve_status'=>'wait_approve', 'status'=>0, 'batch_id'=>$batch, ); DB::table('loanrepayments')->insert($loan_data); } } } return redirect($_SERVER['HTTP_REFERER'].'?batch='.$batch); }else{ return redirect($_SERVER['HTTP_REFERER']); } } public function shares(Request $request){ $filename = $_FILES['file']['name']; $ext = pathinfo($filename, PATHINFO_EXTENSION); $valid_excel = array('xlsm','xlsx','xls','xlt'); if(in_array($ext,$valid_excel)){ $k=0; $inputFileName = $_FILES["file"]["tmp_name"]; $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $reader->setReadDataOnly(true); $worksheet = $reader->load($inputFileName); $values = $worksheet->getActiveSheet(); $rows = $values->toArray(); foreach($rows as $key => $value) { // key is the row count(starts from 0) // array of values $account = str_replace(' ','',$value[1]); $amount = $value[3]; foreach(DB::table('shareSettings')->where('allowWithdrawal','No')->get() as $set){ foreach(DB::table('shareaccount')->where('account',$account)->get() as $acc){ if($amount > 0){ $account_id=$acc->id; $shareprice=$set->nominalprice; $member_id=$acc->borrower_id; $share_type=$set->id; $shares=$amount/$shareprice; $data = array( 'branch_id'=>Session::get('user')['branch_id'], 'member_id'=>$member_id, 'account_id'=>$account_id, 'share_type'=>$share_type, 'amount'=>$amount, 'shareprice'=>$shareprice, 'shares'=>$shares, 'purchase_sell'=>'purchase', 'method'=>4, 'registeredBy'=>Auth::id(), 'date'=>date('Y-m-d'), ); DB::table('shareholders')->insert($data);$k++; #check payment methods for the ledger queries $ledger_account=18; #debit cash/bank account of the total amount repaid $ledgerBc = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_account, 'amount'=>$amount, 'cr_dr'=>'debit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), ); DB::table('daily_ledger')->insert($ledgerBc); #credit member shares account $ledgerMc = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>29, 'amount'=>$amount, 'cr_dr'=>'credit', 'time'=>date('H:i:s'), 'date'=>date('Y-m-d'), ); DB::table('daily_ledger')->insert($ledgerMc); #end } } } } return redirect($_SERVER['HTTP_REFERER'].'?uploaded&count='.$k); }else{ redirect($_SERVER['HTTP_REFERER']); } } public function transactions(Request $request){ $filename = $_FILES['file']['name']; $ext = pathinfo($filename, PATHINFO_EXTENSION); $k=0; $valid_excel = array('xlsm','xlsx','xls','xlt'); if(in_array($ext,$valid_excel)){ $inputFileName = $_FILES["file"]["tmp_name"]; $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $reader->setReadDataOnly(true); $worksheet = $reader->load($inputFileName); $values = $worksheet->getActiveSheet(); $rows = $values->toArray(); $type='deposit'; $debitCredit='credit'; $datetime=$request->date.' '.date("H:i:s"); $batch_id = 'SV-'.rand().'-'.time(); foreach($rows as $key => $value) { $account = trim(str_replace(' ','',$value[1])); $amount = $value[3]; if($key > 0){ // key is the row count(starts from 0) // array of values //get borrower and account foreach(DB::table('savingaccount')->where('savingAccount',$account)->get() as $acc){ if($amount > 0){ $account_id=$acc->id; $borrower_id=$acc->borrower_id; $data = array( 'branch_id'=>Session::get('user')['branch_id'], 'borrower_id'=>$borrower_id, 'account_id'=>$account_id, 'amount'=>$amount, 'debitCredit'=>$debitCredit, 'type'=>$type, 'datetime'=>$datetime, 'description'=>'uploaded from excel', 'batch'=>$batch_id, 'registered_by'=>Auth::id(), 'date'=>date('Y-m-d'), ); DB::table('savingtransactions')->insert($data); $source_id=DB::getPdo()->lastInsertId(); $k++; ##daily ledger####### //get product_id $p = DB::table('savingaccount')->where('id',$account_id)->where('branch_id',Session::get('user')['branch_id'])->get(); foreach($p as $pr){ $product=$pr->savingProduct_id; } ##daily ledger####### $ledger_account_fetch = DB::table('savingproduct')->where('savingproduct.id',$product) ->join('ledger_accounts','savingproduct.ledger_id','=','ledger_accounts.id')->get(); foreach($ledger_account_fetch as $ss){ $ledger_product_id = $ss->ledger_id; $ledger_account = $ss->bank; } $method = 4; 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; } } if($debitCredit=='credit'){ $ledger_crdr='debit'; }else{ $ledger_crdr='credit'; } $ledger1 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_account, 'amount'=>$amount, 'batch_id'=>$batch_id, 'ledger_source_id'=>$source_id, 'cr_dr'=>$ledger_crdr, 'time'=>$datetime, 'date'=>date('Y-m-d'), ); DB::table('daily_ledger')->insert($ledger1); $ledger2 = array( 'branch_id'=>Session::get('user')['branch_id'], 'ledger_account_id'=>$ledger_product_id, 'amount'=>$amount, 'batch_id'=>$batch_id, 'ledger_source_id'=>$source_id, 'cr_dr'=>$debitCredit, 'time'=>$datetime, 'date'=>date('Y-m-d'), ); DB::table('daily_ledger')->insert($ledger2); } } } } return redirect($_SERVER['HTTP_REFERER'].'?uploaded&count='.$k); }else{ return redirect($_SERVER['HTTP_REFERER']); } } public function download(){ $data = array(); $loans = DB::table('loans')->where('branch_id',Session::get('user')['branch_id'])->where('status','granted')->get(); foreach($loans as $loan){ $lname=""; foreach(DB::table('loanproducts')->where('id',$loan->loanProduct)->get() as $lp){ $lname = $lp->loan_product_name; } $borrower = DB::table('borrowers')->where('id',$loan->borrower_id)->get(); foreach($borrower as $user){ $data[] = array( 'name'=>$user->firstName.' '.$user->middleName.' '.$user->lastName, 'loanNumber'=>$loan->loanNumber, 'loanName'=>$lname, ); } } foreach ($data as $key => $row) { $sort[$key] = $row['name']; } array_multisort($sort, SORT_ASC, $data); $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('./public/template.xlsx'); $worksheet = $spreadsheet->getSheet(0); $i=1; foreach($data as $value){ $i++; $worksheet->getCell('A'.$i)->setValue($value['name']); $worksheet->getCell('B'.$i)->setValue($value['loanNumber']); $worksheet->getCell('C'.$i)->setValue($value['loanName']); $spreadsheet->getActiveSheet()->getProtection()->setSheet(true); $spreadsheet->getDefaultStyle()->getProtection()->setLocked(false); $worksheet->getStyle('A'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('B'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('C'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); } $worksheet->getStyle('A1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('B1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('C1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('D1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $styleArrayFirstRow = [ 'font' => [ 'bold' => true, ] ]; $worksheet->getStyle('A1:D1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('4082f5'); $worksheet->getStyle('A1:D1')->applyFromArray($styleArrayFirstRow); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $file_name='Customer-loan-list'; $writer->save($file_name); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="'.$file_name.'.xlsx"'); $writer->save("php://output"); } public function excel_tx(){ $data = array(); foreach(DB::table('savingproduct')->where('compulsory','yes')->get() as $lp){ $savings = DB::table('savingaccount')->where('status',0)->where('savingProduct_id',$lp->id)->where('branch_id',Session::get('user')['branch_id'])->get(); foreach($savings as $loan){ $lname=""; $lname = $lp->name; $borrower = DB::table('borrowers')->where('id',$loan->borrower_id)->get(); foreach($borrower as $user){ $data[] = array( 'name'=>$user->firstName.' '.$user->middleName.' '.$user->lastName, 'loanNumber'=>$loan->savingAccount, 'loanName'=>$lname, ); } } } foreach ($data as $key => $row) { $sort[$key] = $row['name']; } array_multisort($sort, SORT_ASC, $data); $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('./public/template_saving.xlsx'); $worksheet = $spreadsheet->getSheet(0); $i=1; foreach($data as $value){ $i++; $worksheet->getCell('A'.$i)->setValue($value['name']); $worksheet->getStyle('B'.$i)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT); $worksheet->getCell('B'.$i)->setValue(' '.$value['loanNumber'].' '); $worksheet->getCell('C'.$i)->setValue($value['loanName']); $spreadsheet->getActiveSheet()->getProtection()->setSheet(true); $spreadsheet->getDefaultStyle()->getProtection()->setLocked(false); $worksheet->getStyle('A'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('B'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('C'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); } $worksheet->getStyle('A1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('B1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('C1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('D1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $styleArrayFirstRow = [ 'font' => [ 'bold' => true, ] ]; $worksheet->getStyle('A1:D1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('4082f5'); $worksheet->getStyle('A1:D1')->applyFromArray($styleArrayFirstRow); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $file_name='Customer-saving-list'; $writer->save($file_name); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="'.$file_name.'.xlsx"'); $writer->save("php://output"); } public function excel_tx_deposit(Request $request){ $id = $request->id; $data = array(); foreach(DB::table('savingproduct')->where('id',$id)->get() as $lp){ $savings = DB::table('savingaccount')->where('status',0)->where('savingProduct_id',$lp->id)->where('branch_id',Session::get('user')['branch_id'])->get(); foreach($savings as $loan){ $lname=""; $lname = $lp->name; $borrower = DB::table('borrowers')->where('id',$loan->borrower_id)->get(); foreach($borrower as $user){ $data[] = array( 'name'=>$user->firstName.' '.$user->middleName.' '.$user->lastName, 'loanNumber'=>$loan->savingAccount, 'loanName'=>$lname, ); } } } foreach ($data as $key => $row) { $sort[$key] = $row['name']; } array_multisort($sort, SORT_ASC, $data); $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('./public/template_saving.xlsx'); $worksheet = $spreadsheet->getSheet(0); $i=1; foreach($data as $value){ $i++; $worksheet->getCell('A'.$i)->setValue($value['name']); $worksheet->getStyle('B'.$i)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT); $worksheet->getCell('B'.$i)->setValue(' '.$value['loanNumber'].' '); $worksheet->getCell('C'.$i)->setValue($value['loanName']); $spreadsheet->getActiveSheet()->getProtection()->setSheet(true); $spreadsheet->getDefaultStyle()->getProtection()->setLocked(false); $worksheet->getStyle('A'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('B'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('C'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); } $worksheet->getStyle('A1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('B1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('C1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('D1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $styleArrayFirstRow = [ 'font' => [ 'bold' => true, ] ]; $worksheet->getStyle('A1:D1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('4082f5'); $worksheet->getStyle('A1:D1')->applyFromArray($styleArrayFirstRow); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $file_name='Deposit-account-list'; $writer->save($file_name); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="'.$file_name.'.xlsx"'); $writer->save("php://output"); } public function excel_share(){ $data = array(); $shares = DB::table('shareaccount')->where('branch_id',Session::get('user')['branch_id'])->where('status',0)->get(); foreach($shares as $loan){ $lname=""; foreach(DB::table('shareSettings')->where('id',$loan->shareType)->get() as $lp){ $lname = $lp->shareName; } $borrower = DB::table('borrowers')->where('id',$loan->borrower_id)->get(); foreach($borrower as $user){ $data[] = array( 'name'=>$user->firstName.' '.$user->middleName.' '.$user->lastName, 'loanNumber'=>$loan->account, 'loanName'=>$lname, ); } } foreach ($data as $key => $row) { $sort[$key] = $row['name']; } array_multisort($sort, SORT_ASC, $data); $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('./public/template_share.xlsx'); $worksheet = $spreadsheet->getSheet(0); $i=1; foreach($data as $value){ $i++; $worksheet->getCell('A'.$i)->setValue($value['name']); $worksheet->getCell('B'.$i)->setValue($value['loanNumber']); $worksheet->getCell('C'.$i)->setValue($value['loanName']); $spreadsheet->getActiveSheet()->getProtection()->setSheet(true); $spreadsheet->getDefaultStyle()->getProtection()->setLocked(false); $worksheet->getStyle('A'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('B'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('C'.$i)->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); } $worksheet->getStyle('A1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('B1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('C1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $worksheet->getStyle('D1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED); $styleArrayFirstRow = [ 'font' => [ 'bold' => true, ] ]; $worksheet->getStyle('A1:D1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('4082f5'); $worksheet->getStyle('A1:D1')->applyFromArray($styleArrayFirstRow); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $file_name='Share-account-list'; $writer->save($file_name); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="'.$file_name.'.xlsx"'); $writer->save("php://output"); } }