File: /home/cafsindia/hrms_cafsindia_com/application/controllers/Loan_ledger.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Loan_ledger extends Action_controller{
public function __construct(){
parent::__construct('loan_ledger');
$this->collect_base_info();
// Load pdf library
$this->load->library('pdf');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['quick_link'] = $this->quick_link;
$data['table_head'] = $this->table_head;
$data['master_pick'] = $this->master_pick;
$data['fliter_list'] = $this->fliter_list;
$this->load->view("$this->control_name/manage",$data);
}
public function emp_suggest(){
$search_term = $this->input->post_get('term');
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and employee_code like "'.$search_term.'%"';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
public function get_loan_details(){
$process_emp_code = $this->input->post("process_emp_code");
$loan_type_query = 'select label_name,view_name,apply_year,loan_type,install_year,loan_amount,install_amount,paid_status,installment_count from cw_form_setting inner join cw_loan_installment on cw_loan_installment.loan_type = cw_form_setting.label_name where prime_module_id = "employees" and loan_check = 1 and cw_loan_installment.trans_status = 1 and emp_code = "'.$process_emp_code.'" and paid_status = 1 ORDER BY loan_type asc';
$loan_type_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_type_query')");
$loan_type_result = $loan_type_data->result_array();
$loan_type_data->next_result();
$loan_month_arr = array_reduce($loan_type_result, function ($result, $arr) {
$result[$arr['apply_year']][] = $arr;
return $result;
}, array());
if($loan_month_arr){
foreach($loan_month_arr as $loan_month => $loan_type_rslt){
$sum_balance_amount = 0;
$sum_install_amount = 0;
foreach ($loan_type_rslt as $key => $loan_data) {
$apply_year = $loan_data['apply_year'];
$loan_type = $loan_data['loan_type'];
$install_year = $loan_data['install_year'];
$loan_amount = $loan_data['loan_amount'];
$install_amount = $loan_data['install_amount'];
$paid_status = $loan_data['paid_status'];
$installment_count = $loan_data['installment_count'];
if($key === 0){
$balance_amount = $loan_amount;
}
$balance_amount = (int)$balance_amount - (int)$install_amount;
$sum_balance_amount += (int)$balance_amount;
$sum_install_amount += (int)$install_amount;
$balance_loan_amount = (int)$loan_amount - (int)$sum_install_amount;
if((int)$paid_status === 1){
$paid_status = "PAID";
}else{
$paid_status = "NOT PAID";
}
$tr_line .= "<tr><td>$loan_type</td><td>$apply_year</td><td>$installment_count</td><td>$loan_amount</td><td>$install_amount</td><td>$balance_amount</td><td>$paid_status</td></tr>";
}
$total_loan_amount += (int)$loan_amount;
$total_install_amount += (int)$sum_install_amount;
// echo "total_loan_amount :: $total_loan_amount";
// echo "sum_balance_amount :: $sum_balance_amount";
$total_loan_balance += (int)$balance_loan_amount;
$tr_line .= "<tr><td></td><td></td><td></td><td style='font-weight: bold;color:red;'>$loan_amount</td><td style='font-weight: bold;color:red;'>$sum_install_amount</td><td style='font-weight: bold;color:red;'>$balance_loan_amount</td><td></td></tr>";
}
$tr_line .= "<tr style='border: solid;'><td></td><td></td><td></td><td style='font-weight: bold;color:red;'>$total_loan_amount</td><td style='font-weight: bold;color:red;'>$total_install_amount</td><td style='font-weight: bold;color:red;'>$total_loan_balance</td><td></td></tr>";
}else{
$tr_line = "<tr><td/><td>No data available</td><td/><td/></tr>";
}
$table_content = "<div style='background-color: #f2f2f2;'>
<table class='table table-striped table-bordered' id='details_list'>
<thead>
<tr>
<th>Loan Type</th>
<th>Apply Year</th>
<th>Installment Count</th>
<th>Loan Amount</th>
<th>Installment Amount</th>
<th>Balance</th>
<th>Paid Staus</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>
</div>";
echo json_encode(array('success' => TRUE, 'message' => "See Loan Report",'table_content'=>$table_content));
}
public function get_pdf(){
$process_emp_code = $this->input->post("process_emp_code");
$company_result = $this->company_info;
$company_name = $company_result[0]->company_name;
$company_address = $company_result[0]->address;
$company_city = $company_result[0]->city;
$company_state = $company_result[0]->state;
$company_country = $company_result[0]->country;
$primary_color = $company_result[0]->primary_color;
$emp_qry = 'SELECT emp_name,cw_designation.designation FROM cw_employees inner join cw_designation on cw_designation.prime_designation_id = cw_employees.designation where employee_code = "'.$process_emp_code.'" and cw_employees.trans_status = 1 and cw_designation.trans_status = 1';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$emp_name = $emp_result[0]->emp_name;
$designation = $emp_result[0]->designation;
$loan_type_query = 'select label_name,view_name,apply_year,loan_type,install_year,loan_amount,install_amount,paid_status,installment_count from cw_form_setting inner join cw_loan_installment on cw_loan_installment.loan_type = cw_form_setting.label_name where prime_module_id = "employees" and loan_check = 1 and cw_loan_installment.trans_status = 1 and emp_code = "'.$process_emp_code.'" and paid_status = 1 ORDER BY loan_type asc';
$loan_type_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_type_query')");
$loan_type_result = $loan_type_data->result_array();
$loan_type_data->next_result();
$loan_month_arr = array_reduce($loan_type_result, function ($result, $arr) {
$result[$arr['apply_year']][] = $arr;
return $result;
}, array());
if($loan_month_arr){
foreach($loan_month_arr as $loan_month => $loan_type_rslt){
$sum_balance_amount = 0;
$sum_install_amount = 0;
foreach ($loan_type_rslt as $key => $loan_data) {
$apply_year = $loan_data['apply_year'];
$loan_type = $loan_data['loan_type'];
$install_year = $loan_data['install_year'];
$loan_amount = $loan_data['loan_amount'];
$install_amount = $loan_data['install_amount'];
$paid_status = $loan_data['paid_status'];
$installment_count = $loan_data['installment_count'];
if($key === 0){
$balance_amount = $loan_amount;
$fixed_apply_year = $apply_year;
$fixed_loan_type = $loan_type;
}else{
$fixed_apply_year = "";
$fixed_loan_type = "";
}
$balance_amount = (int)$balance_amount - (int)$install_amount;
$sum_balance_amount += (int)$balance_amount;
$sum_install_amount += (int)$install_amount;
$balance_loan_amount = (int)$loan_amount - (int)$sum_install_amount;
if((int)$paid_status === 1){
$paid_status = "PAID";
}else{
$paid_status = "NOT PAID";
}
$tr_line .= "<tr><td>$fixed_loan_type</td><td>$fixed_apply_year</td><td>$installment_count</td><td>$loan_amount</td><td>$install_amount</td><td>$balance_amount</td><td>$paid_status</td></tr>";
}
$total_loan_amount += (int)$loan_amount;
$total_install_amount += (int)$sum_install_amount;
$total_loan_balance += (int)$balance_loan_amount;
$tr_line .= "<tr><td style='border-top: 2px solid black;border-bottom: 2px solid black;'></td><td style='border-top: 2px solid black;border-bottom: 2px solid black;'></td><td style='border-top: 2px solid black;border-bottom: 2px solid black;'></td><td style='font-weight: bold;color:red;border-top: 2px solid black;border-bottom: 2px solid black;'>$loan_amount</td><td style='font-weight: bold;color:red;border-top: 2px solid black;border-bottom: 2px solid black;'>$sum_install_amount</td><td style='font-weight: bold;color:red;border-top: 2px solid black;border-bottom: 2px solid black;'>$balance_loan_amount</td><td style='border-top: 2px solid black;border-bottom: 2px solid black;'></td></tr>";
}
$tr_line .= "<tr style='border: solid;'><td></td><td></td><td></td><td style='font-weight: bold;color:red;border-top: 2px solid black;'>$total_loan_amount</td><td style='font-weight: bold;color:red;border-top: 2px solid black;'>$total_install_amount</td><td style='font-weight: bold;color:red;border-top: 2px solid black;'>$total_loan_balance</td><td></td></tr>";
}else{
$tr_line = "<tr><td/><td>No data available</td><td/><td/></tr>";
}
$table_data = "<div style='width:700px; margin-left: auto; margin-right: auto; padding: 15px; background-color: #EEEEEE; border-radius: 3px; box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2);'>
<table class='table-bordered' style='width:100%;border-collapse: collapse;border-spacing: 0;border-color: #CCCCCC !important;border: 0px;text-align: left;font-size: 14px;background-color: #FFFFFF;' border='1' cellspacing='0' cellpadding='4'>
<tbody>
<tr>
<td colspan='2'>
<h3 style='color:".$primary_color.";margin:10px;font-size:21px;text-align: center;'>TDS WORK SHEET</h3>
</td>
</tr>
<tr>
<td>Name and address of the employer</td>
<td>Code,name and designation of the employee</td>
</tr>
<tr>
<td style='font-weight:bold;'>$company_name<br/>$company_address<br/>$company_city<br/>$company_state<br/>$company_country</td>
<td>$process_emp_code<br/>$emp_name<br/>$designation<br/></td>
</tr>
<tr>
<td colspan='2'><h3 style='color:".$primary_color.";margin:10px;font-size:16px;text-align: center;'>LOAN LEDGER</h3></td>
</tr>
</tbody>
</table>
<br>
<table style='width:100%;font-size: 14px;background-color:#FFFFFF;' border-collapse: collapse; cellspacing='0' cellpadding='4' class='table-bordered'>
<tbody>
<tr style='background-color:".$primary_color.";color: #FFFFFF;font-weight:bold;'>
<th>Loan Type</th>
<th>Apply Year</th>
<th>Installment Count</th>
<th>Loan Amount</th>
<th>Installment Amount</th>
<th>Balance</th>
<th>Paid Staus</th>
</tr>
$tr_line
</tbody>
</table>
</div>";
$table_data = "<!DOCTYPE html><html> <body>" . $table_data . "</body></html>";
$this->generate_pdf($table_data,$process_emp_code);
}
public function generate_pdf($table_data,$process_emp_code){
$employee_code = $process_emp_code;
$content = $table_data;
$process_month = "Loan_ledger";
// Load HTML content
$this->dompdf->loadHtml($content);
// (Optional) Setup the paper size and orientation
$this->dompdf->setPaper('A4', 'portrait');
// Render the HTML as PDF
$this->dompdf->render();
// Output the generated PDF (1 = download and 0 = preview)
$output = $this->dompdf->output();
$folder = "./loan_ledger/" . $process_emp_code;
//Check Folder Exist
if (!file_exists($folder)) {
mkdir($folder, 0777, true);
}
//Check File Exist
if (file_exists($folder . "/" . $employee_code . ".pdf")) {
unlink($folder . "/" . $employee_code . ".pdf");
}
file_put_contents($folder . "/" . $employee_code . ".pdf", $output);
$file_names = $folder . "/" . $employee_code . ".pdf";
echo json_encode(array('success' => TRUE, 'message' => "See Loan Ledger",'table_content'=>$file_names));
}
}
?>