File: /home/cafsindia/hrms_cafsinfotech_in/OLD/application/controllers/Loan_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Loan_report extends Action_controller{
public function __construct(){
parent::__construct('loan_report');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$process_role = array();
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$process_role[$role_id] = $category_name;
}
$type_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT label_name,view_name FROM cw_form_setting WHERE loan_check = 1;')");
$type_result = $type_info->result();
$type_info->next_result();
$loan_role = array();
$loan_role[""] = "---select---";
foreach($type_result as $for){
$label_name = $for->label_name;
$loan_type = $for->view_name;
$loan_role[$label_name] = $loan_type;
}
$data['loan_role'] = $loan_role;
$data['process_role'] = $process_role;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//AUTO COMPLETE FUNCTION
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 prime_employees_id !=1 and employee_code like "'.$search_term.'%" OR emp_name 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_table_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$process_type = $this->input->post('process_type');
$process_emp_id = $this->input->post('process_emp_id');
$process_role = $this->input->post('process_role');
$process_role = implode(',', $this->input->post('process_role'));
$loan_type = $this->input->post('loan_type');
$report_type = $this->input->post('report_type');
if($process_type == '1'){
$loan_where_query = 'cw_loan.emp_code ="'.$process_emp_id.'"';
}else
if($process_type == '2'){
$loan_where_query = 'cw_loan.category in ('.$process_role.')';
}
//EMPLOYEE NAME AND CO
$emp_qry = 'select employee_code,emp_name,loan_date,loan_amount,cw_loan.loan_amount*(interest_rate/100) as interest_amt,cw_loan.loan_type,cw_loan.apply_year,number_of_installment,per_month,loan_foreclose_amount,loan_foreclose_date,prime_loan_id from cw_employees inner join cw_loan on cw_loan.emp_code = cw_employees.employee_code where '.$loan_where_query.' and cw_loan.trans_status = 1';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_rslt = $emp_info->result_array();
$emp_info->next_result();
//TABLE DATAS
$paid_loan_qry = 'SELECT prime_loan_id,cw_loan.emp_code,cw_loan.loan_type,cw_loan.apply_year,COUNT(*) as paid_months,SUM(cw_loan_installment.install_amount) as paid_amt FROM cw_loan JOIN cw_loan_installment ON cw_loan_installment.loan_id = cw_loan.prime_loan_id WHERE cw_loan.loan_type = "'.$loan_type.'" and '.$loan_where_query.' and cw_loan.trans_status = 1 and cw_loan_installment.trans_status = 1 and paid_status = 1 group by prime_loan_id order by cw_loan.loan_type';
$paid_loan_info = $this->db->query("CALL sp_a_run ('SELECT','$paid_loan_qry')");
$paid_loan_rslt = $paid_loan_info->result();
$paid_loan_info->next_result();
$loan_amt_tot = 0;
$paid_amt_tot = 0;
$paid_arr = array();
foreach($paid_loan_rslt as $key => $value){
$paid_arr[$value->prime_loan_id] = $value;
}
$tr_line = "";
$tn_line = "";
foreach ($emp_rslt as $key => $val){
$emp_code = $val['employee_code'];
$emp_name = $val['emp_name'];
$loan_date = $val['loan_date'];
$loan_type = $val['loan_type'];
$apply_year = $val['apply_year'];
$loan_amount = $val['loan_amount'];
$loan_amt_tot = $loan_amt_tot + $loan_amount;
$no_of_install = $val['number_of_installment'];
$per_month = $val['per_month'];
$prime_loan_id = $val['prime_loan_id'];
$paid_months = $paid_arr[$prime_loan_id]->paid_months;
$paid_amt = $paid_arr[$prime_loan_id]->paid_amt;
$paid_amt_tot = $paid_amt_tot + $paid_amt;
$foreclose_amt = $val['loan_foreclose_amount'];
$foreclose_dt = $val['loan_foreclose_date'];
$interest_amt = $val['interest_amt'];
$balance_month = $no_of_install - $paid_months;
$balance_amt = $loan_amount - $paid_amt;
$tr_line .= "<tr><td>$loan_date</td><td>$loan_type</td><td>$emp_code</td><td>$emp_name</td><td>$loan_amount</td><td>$no_of_install</td><td>$per_month</td><td>$paid_months</td><td>$paid_amt</td><td>$foreclose_dt</td><td>$foreclose_amt</td><td>$interest_amt</td><td>$balance_month</td><td>$balance_amt</td></tr>";
$tn_line = "<tr><td></td><td></td><td></td><td></td><td style='color: red;'>$loan_amt_tot</td><td></td><td></td><td></td><td style='color: red;'>$paid_amt_tot</td><td></td><td></td><td></td><td></td><td></td></tr>";
}
$view_content = "<table class='table table-striped table-bordered' id='role'>
<thead>
<tr>
<th>Loan Date</th>
<th>Loan Type</th>
<th scope='col'>Employee Code</th>
<th scope='col'>Employee Name</th>
<th scope='col'>Loan Amount</th>
<th scope='col'>Total Months</th>
<th scope='col'>Amount Per Month</th>
<th scope='col'>Paid Months</th>
<th scope='col'>Paid Amount</th>
<th scope='col'>F.Close Months</th>
<th scope='col'>F.Close Amount</th>
<th scope='col'>Interest Amount</th>
<th scope='col'>Balance Months</th>
<th scope='col'>Balance Amount</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
<tfoot>$tn_line</tfoot>
</table>";
echo json_encode(array("success" => TRUE,'loan_rslt'=>$view_content));
}
}
?>