File: //home/cafsindia/hrms_allyindian_com/application_bk/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(){
$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;
$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();
$category_list = [];
foreach ($role_result as $for) {
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
public function get_table_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$personal_code = $this->input->post('personal_code');
$search_by = $this->input->post('search_by');
$employee_code = $this->input->post('employee_code');
$loan_type = $this->input->post('loan_type');
$report_type = $this->input->post('report_type');
$process_month = $this->input->post('process_month');
$category = implode(',', (array) $this->input->post('category'));
$frm_date = $this->db->escape(date('Y-m-d',strtotime($this->input->post('frm_date'))));
$to_date = $this->db->escape(date('Y-m-d',strtotime($this->input->post('to_date'))));
if($loan_type){
$loan_type_query = 'and cw_loan.loan_type ="'.$loan_type.'" ';
}
if((int)$report_type === 1){
if($search_by == '2'){
$loan_where_query = 'and cw_loan.emp_code ="'.$employee_code.'" ';
}
//EMPLOYEE NAME AND CO
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT cw_form_setting.view_name AS loan_type_view,cw_form_setting.label_name AS loan_type_label,cw_employees.employee_code,cw_employees.emp_name,cw_per_area.perarea,cw_position.position_name AS designation,cw_employees.date_of_joining,cw_loan.loan_date,cw_loan.loan_remarks,cw_loan.loan_amount,cw_loan.loan_amount*(interest_rate/100) AS interest_amt,cw_loan.apply_year,cw_loan.foreclose_sts,cw_loan.number_of_installment,per_month,loan_foreclose_amount,loan_foreclose_date FROM cw_employees INNER JOIN cw_loan ON cw_loan.emp_code = cw_employees.employee_code INNER JOIN cw_position ON cw_position.prime_position_id = cw_employees.designation INNER JOIN cw_per_area ON cw_employees.perarea = cw_per_area.perarea INNER JOIN cw_form_setting ON cw_form_setting.label_name = cw_loan.loan_type WHERE DATE_FORMAT(str_to_date(concat(\"01-\",cw_loan.apply_year),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$frm_date' AND '$to_date' AND cw_loan.trans_status = 1 $loan_where_query $loan_type_query ')");
$emp_rslt = $emp_info->result_array();
$emp_info->next_result();
//TABLE DATA
$paid_loan_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT cw_loan.emp_code,cw_loan.loan_type,cw_loan.apply_year,COUNT(*) as paid_months,SUM(cw_loan_installment.install_amount) as recovery_amt FROM cw_loan JOIN cw_loan_installment join cw_employees ON cw_loan_installment.loan_id = cw_loan.prime_loan_id and cw_loan.emp_code = cw_employees.employee_code WHERE DATE_FORMAT(str_to_date(concat(\"01-\",cw_loan.apply_year),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$frm_date' AND '$to_date' $loan_where_query and cw_loan.trans_status = 1 and cw_loan_installment.trans_status = 1 and paid_status != 0 group by prime_loan_id,cw_loan.apply_year order by cw_loan.loan_type')");
$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->loan_type][$value->apply_year][$value->emp_code] = $value;
}
$tr_line = "";
foreach ($emp_rslt as $key => $val){
$emp_code = $val['employee_code'];
$designation = $val['designation'];
$personal_code = $val['perarea'];
$date_of_joining = date('d-m-Y',strtotime($val['date_of_joining']));
$emp_name = $val['emp_name'];
$remarks = $val['loan_remarks'];
$foreclose_sts = $val['foreclose_sts'];
$loan_date = date('d-m-Y',strtotime($val['loan_date']));
$loan_type_view = $val['loan_type_view'];
$loan_type_label = $val['loan_type_label'];
$apply_year = $val['apply_year'];
$loan_amount = $val['loan_amount'];
$no_of_install = $val['number_of_installment'];
$paid_months = $paid_arr[$loan_type_label][$apply_year][$emp_code]->paid_months;
$recovery_amt = $paid_arr[$loan_type_label][$apply_year][$emp_code]->recovery_amt;
$foreclose_dt = $val['loan_foreclose_date'];
$foreclose_amt = $val['loan_foreclose_amount'];
$interest_amt = $val['interest_amt'];
$balance_amt = $loan_amount - $recovery_amt;
if($foreclose_sts == 1) { $foreclose_sts = 'Yes'; }else{ $foreclose_sts = 'No';}
$tr_line .= "<tr><td>$emp_code</td><td>$emp_name</td><td>$designation</td><td>$date_of_joining</td><td>$personal_code</td><td>$loan_type_view</td><td>$loan_date</td><td>$loan_amount</td><td>$no_of_install</td><td>$recovery_amt</td><td>$balance_amt</td> <td>$interest_amt</td><td>$foreclose_sts</td><td>$foreclose_dt</td><td>$foreclose_amt</td><td>$remarks</td></tr>";
}
$loan_entry = "<table class='table table-striped table-bordered' id='role'><thead><tr><th scope='col'>Employee Code</th><th scope='col'>Employee Name</th><th scope='col'>Designation</th><th scope='col'>Date Of Joining</th><th scope='col'>Personal Area</th><th scope='col'>Loan Type</th><th scope='col'>Loan Date</th><th scope='col'>Loan Amount</th> <th scope='col'>Loan Installment</th><th scope='col'>Total Recovered</th> <th scope='col'>Total Loan Balance</th><th scope='col'>Interest Amount</th><th scope='col'>Foreclosed Status</th> <th scope='col'>Foreclosed On</th><th scope='col'>Foreclosed Amt</th><th scope='col'>Remarks</th></tr></thead><tbody>$tr_line</tbody></table>";
echo json_encode(array("success" => TRUE,'loan_rslt'=>$loan_entry));
}else
if((int)$report_type === 2){
//LOAN BALANCE REPORT SEARCH BY 1=> CATEGORY 2=> EMPLOYEE CODE
$loan_bal_info = $this->db->query("CALL sp_loan_report('$category''$employee_code','$process_month','$personal_code','','','$search_by','$loan_type')");
$result = $loan_bal_info->result();
$loan_bal_info->next_result();
$table = $this->generateTable($result);
echo json_encode(array("success" => TRUE,'loan_rslt'=>$table));
}
}
//GENERATING TABLE FOR LOAN BALANCE REPORT !
public function generateTable($data){
$table = '<table class="table table-striped table-bordered" id="role">';
$headerrow = '<thead><tr>';
foreach ($data[0] as $key => $value){
$headerrow .= '<th scope="col">' . htmlspecialchars($key) . '</th>';
}
$headerrow .= '</tr></thead><tbody>';
$table .= $headerrow;
foreach ($data as $item){
$row = '<tr>';
foreach ($item as $value){
$row .= '<td>' . htmlspecialchars($value) . '</td>';
}
$row .= '</tr>';
$table .= $row;
}
$table .= '</tbody></table>';
return $table;
}
public function emp_suggest(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$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);
}
}
?>