File: /home/cafsindia/uds.cafsinfotech.in/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(){
$per_area_rslt = $this->query_build_function('personal_code,personal_name','cw_sap_personal_area','','trans_status = 1 and FIND_IN_SET(personal_code, "'.$this->logged_area_access.'") ');
//Generate list for Datalist
$per_area_list = "";
foreach($per_area_rslt as $for){
$personal_code = $for['personal_code'];
$personal_name = $for['personal_name'];
if($personal_code !== ""){
$per_area_list .= "<option data-value='".$personal_code."' value='".trim($personal_code)."' >".trim($personal_name)."</option>";
}
}
$data['per_area_list'] = $per_area_list;
$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['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' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$personal_code = $this->input->post('personal_code');
$project_id = $this->input->post('project_id');
$wbs_element = $this->input->post('wbs_element');
$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');
$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 == '1'){
if($project_id){
$project_query = 'and cw_employees.project_id ="'.$project_id.'" ';
}else
if($wbs_element){
$wbs_query = 'and cw_employees.wbs_element ="'.$wbs_element.'" ';
}else
if($personal_code){
$percode_qry = 'and cw_employees.personal_code ="'.$personal_code.'" ';
}
}else
if($search_by == '2'){
$loan_where_query = 'and cw_loan.employee_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_loan.employee_code,cw_employees.emp_name,cw_sap_position.position_name as designation,cw_employees.date_of_joining,cw_employees.personal_code,cw_employees.wbs_element,cw_employees.project_id,cw_loan.loan_date,cw_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 INNER JOIN cw_form_setting INNER JOIN cw_sap_position ON cw_loan.employee_code = cw_employees.employee_code AND cw_form_setting.label_name = cw_loan.loan_type AND cw_employees.position = cw_sap_position.position_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' AND cw_loan.trans_status = 1 $loan_where_query $percode_qry $project_query $wbs_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.employee_code,cw_loan.loan_type,cw_loan.apply_year,COUNT(*) as paid_months,SUM(cw_loan_installment.recovery_amount) as recovery_amt,cw_loan_installment.prev_pending as prev_pending,cw_loan_installment.balance_amount as balance_amount FROM cw_loan JOIN cw_loan_installment join cw_employees ON cw_loan_installment.loan_id = cw_loan.prime_loan_id and cw_loan.employee_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 $percode_qry $project_query $wbs_query $loan_type_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->employee_code] = $value;
}
$tr_line = "";
$tn_line = "";
foreach ($emp_rslt as $key => $val){
$emp_code = $val['employee_code'];
$designation = $val['designation'];
$personal_code = $val['personal_code'];
$wbs_element = $val['wbs_element'];
$date_of_joining = date('d-m-Y',strtotime($val['date_of_joining']));
$emp_name = $val['emp_name'];
$project_id = $val['project_id'];
$remarks = $val['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>$project_id</td><td>$wbs_element</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>";
}
// die;
$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'>Project No</th><th scope='col'>WBS Element</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
$loan_bal_info = $this->db->query("CALL sp_loan_report('$employee_code','$process_month','$personal_code','$project_id','$wbs_element','$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;
}
//SEARCH TERM FUNC -> PERSONAL AREA,PROJECT,WBS ELEMENT,EMPLOYEE CODE
public function get_wbs(){
$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);
}
$search_term = $this->input->post_get('term');
$personal_code = $this->input->post('personal_code');
$pick_list = $this->input->post('pick_list');
$search_info = $this->db->query("CALL sp_sap_picks ('$search_term','$personal_code','$pick_list')");
$search_rslt = $search_info->result();
$search_info->next_result();
if($search_rslt[0]){
echo json_encode(array('success' => true, 'search_rslt' => $search_rslt));
}else{
echo json_encode(array('success' => false, 'message' => "No Data Found..!"));
}
}
}
?>