File: //home/cafsindia/hrms_allyindian_com/application_bk/controllers/Loan.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
/**********************************************************
Filename: loan
Description: Dynamic form input is created and static installment,deviation is function is created.
Author: Jagufer Sathik
Created on: 04-FEB-2019
Reviewed by: Udhayakumar Anandhan (REVIEW PENDING)
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
require_once("Action_controller.php");
require('./application/libraries/PHPSpreadsheet/autoload.php');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class Loan extends Action_controller{
public function __construct(){
parent::__construct('loan');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->load->model('Hr_methods_model');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//PAGE INFO FUNCTION
$this->page_info();
$data['encKey'] = $this->generateKey();
$data['module_sts'] = (int)$this->module_sts;
$data['quick_link'] = $this->quick_link;
$data['pick_list'] = $this->pick_list;
$data['form_info'] = $this->form_info;
$data['table_head'] = $this->table_head;
$data['fliter_list'] = $this->fliter_list;
$data['freeze_list'] = $this->freeze_list;
$this->load->view("$this->control_name/manage",$data);
}
//LOAD TABEL WITH FILTERS
public function search(){
$dec_data = $this->cryptoDecrypt($_POST['Payload']);
$_POST = $dec_data['data'];
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....'));
exit(0);
}
//SEARCH INFO FUNCTION
$this->search_info();
$module_sts = (int)$this->module_sts;
if(!$module_sts){
echo json_encode(array('success' => FALSE, 'message' => "Search Info Query Process Error..!"));
exit(0);
}else{
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col = $this->input->post('columns');
$search = $this->input->post('search');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_col = $order_col[$column]['data'];
$search = trim($search['value']);
$search_query = str_replace("@SELECT@",$this->select_query,$this->base_query);
$search_result = array();
//ADDED BASIC,FILTER,COMMON QUERY HERE
$role_condition = "";
if($this->role_condition){
$role_condition = $this->role_condition;
}
$fliter_query = "";
foreach($this->fliter_list as $fliter){
$label_id = $fliter->label_name;
$field_isdefault = (int)$fliter->field_isdefault;
$field_type = (int)$fliter->field_type;
$prime_form_id = $fliter->prime_form_id;
if($field_isdefault === 1){
$column_name = $this->prime_table .".$label_id";
$search_val = $this->input->post("$label_id");
if($search_val){
if($field_type === 4){
$search_val = date('Y-m-d',strtotime($search_val));
$fliter_query .= ' and '.$column_name.' = "'.$search_val.'"';
}else
if(($field_type === 5) || ($field_type === 7)){
$search_val = trim(implode('","',$search_val ?? []));
$fliter_query .= ' and '.$column_name.' in ("'.$search_val.'")';
}else
if($field_type === 9){
$search_id = 'filter_'.$label_id.'_hidden_'.$prime_form_id;
$search_val = $this->input->post("$search_id");
$fliter_query .= ' and '.$column_name.' in ("'.$search_val.'")';
}else
if($field_type === 13){
$search_val = date('Y-m-d H:i:s',strtotime($search_val));
$fliter_query .= ' and '.$column_name.' = "'.$search_val.'"';
}else{
$fliter_query .= ' and '.$column_name.' LIKE "'.$search_val.'%"';
}
}
}
}
$common_search = "";
if($search){
foreach($this->table_head as $setting){
$prime_form_id = $setting->prime_form_id;
$field_type = (int)$setting->field_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$pick_list_type = $setting->pick_list_type;
$input_view_type = (int)$setting->input_view_type;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = (int)$setting->field_isdefault;
if($field_isdefault === 1){
if(($input_view_type === 1) || ($input_view_type === 2)){
$search_label = "$this->prime_table.$label_id";
$search_val = "";
if($field_type === 4){ // having issues in date search
if(strtotime($search)){
$search_val = date('Y-m-d',strtotime($search));
$common_search .= ' or '. $search_label .' like "'.$search_val.'%"';
}
}else
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$result = array_filter($this->pick_list[$label_id]['array_list'] ?? [], function ($item) use ($search) {
if (stripos($item, $search) !== false) {
return true;
}
return false;
});
if($result){
$pick_key = implode('", "', array_keys($result ?? []) ?? []);
$common_search .= ' or '. $search_label .' in("'.$pick_key.'")';
}
}else{
$common_search .= ' or '. $search_label .' like "%'.$search.'%"';
}
}
}
}
if($common_search){
$common_search = ltrim($common_search,' or ');
$common_search = " and ($common_search)";
$common_search = str_replace("(,","(",$common_search);
$common_search = str_replace("()","(0)",$common_search);
}
}
$count_all_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query = $count_all_query.' where '.$this->prime_table.'.trans_status = 1 '.$role_condition.$fliter_query.$common_search;
$search_query .= " where $this->prime_table.trans_status = 1 $role_condition $fliter_query $common_search";
$search_query .= " ORDER BY $order_col $order_sor";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_pro_qry = [];
$search_pro_qry[] = array("return"=>"total_count","qry"=>$count_all_query);
$search_pro_qry[] = array("return"=>"filtered_count","qry"=>$count_query);
$search_pro_qry[] = array("return"=>"search_result","qry"=>$search_query);
$search_info_rslt = $this->run_multi_qry($search_pro_qry);
$total_count = $search_info_rslt->rslt->total_count[0]->allcount;
$filtered_count = $search_info_rslt->rslt->filtered_count[0]->allcount;
$search_result = json_decode(json_encode($search_info_rslt->rslt->search_result),true);
if($search_result === null || $search_result === ''){
$search_result = [];
}
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
}
//LOAD MODEL PAGE VIEW WITH DATA
public function view($form_view_id=-1){
$data['primeId'] = $form_view_id;
if($form_view_id !== -1){
//Decrypt prime id from URL
$decRslt = $this->cryptoDecrypt(base64_decode(urldecode($form_view_id)));
$form_view_id = $decRslt['prime_id'];
if(!$form_view_id){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
}
//VIEW INFO FUNCTION
$this->view_info($form_view_id);
$data['encKey'] = $this->generateKey();
$data['module_sts'] = (int)$this->module_sts;
//VIEW, FORM INPUT
$data['view_info'] = $this->view_info;
$data['form_info'] = $this->form_info;
$data['formula_result'] = $this->formula_result;//for label color change
$data['role_based_condition'] = $this->role_based_condition;//for label name readonly options
$data['all_pick'] = $this->pick_list;//all pick result
//DEPENDENT AUTO PICKLIST
$data['auto_pick'] = $this->depen_auto_list;//dependent auto result
$data['condition_list'] = $this->form_condition_list;
//VIEW DATA
$base_query = str_replace("@SELECT@",$this->view_select,$this->base_query);
$form_view_qry = $base_query .' where '.$this->prime_table.'.'.$this->prime_id.' = '.$form_view_id.' and '.$this->prime_table.'.trans_status = 1';
$row_view_qry = 'select * from cw_form_view_setting where prime_view_module_id = "'.$this->control_name.'" and form_view_type = "3" and trans_status = 1';
$view_pro_qry = [];
$view_pro_qry[] = array("return"=>"form_view","qry"=>$form_view_qry);
$view_pro_qry[] = array("return"=>"row_view_list","qry"=>$row_view_qry);
$view_info_rslt = $this->run_multi_qry($view_pro_qry);
$form_view_rslt = $view_info_rslt->rslt->form_view[0];
$row_view_rslt = $view_info_rslt->rslt->row_view_list;
$data['form_view'] = $form_view_rslt;
$row_view_list = array();
foreach($row_view_rslt as $view){
$prime_form_view_id = $view->prime_form_view_id;
$row_set_data = $this->get_row_set_data($prime_form_view_id,$form_view_id);
$row_view_list[$prime_form_view_id] = $row_set_data;
}
$data['row_view_list'] = $row_view_list;
/*formula label name */
$formula_qry = 'select * from cw_form_bind_input where input_cond_module_id = "'.$this->control_name.'" and trans_status = 1';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$data['formula_result'] = $formula_result;
//Custom Code START
$loan_type_query = 'select label_name,view_name from cw_form_setting where prime_module_id = "employees" and loan_check = 1 ORDER BY input_for,field_sort asc';
$loan_type_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_type_query')");
$loan_type_result = $loan_type_data->result();
$loan_type_data->next_result();
if($loan_type_result){
$pick_key = array_column($loan_type_result, "label_name");
$pick_val = array_column($loan_type_result, "view_name");
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- Loan Type ----") + $final_pick;
}
$data['loan_type'] = $final_pick;
//Custom Code END
//get Loan Paid Details
$paid_qry = 'select count(*) as paid_count from cw_loan_installment where paid_status = 1 and trans_status=1 and loan_id = '.$form_view_id;
$paid_data = $this->db->query("CALL sp_a_run ('SELECT','$paid_qry')");
$paid_result = $paid_data->result();
$paid_data->next_result();
$paid_count = (int)$paid_result[0]->paid_count;
$data['paid_count'] = $paid_count;
//FOR DEPENDENT
$data['get_depend_prime_id'] = $this->get_depend_fun();
$data['depend_label_id'] = $this->depend_label_fun();
$this->load->view("$this->control_name/form",$data);
}
//SAVE MODEL DATA TO DATA BASE
public function save(){
//Encryption
$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);
}
//SAVE INFO FUNCTION
$this->save_info();
// $module_sts = (int)$this->module_sts;
// if(!$module_sts){
// echo json_encode(array('success' => FALSE, 'message' => "Save Info Query Process Error...!"));
// exit(0);
// }else{
$unq_chk = array();
$prime_qry_key = "";
$prime_qry_value = "";
$prime_upd_query = "";
$emp_code = $this->input->post('emp_code');
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Employee Code"=>$emp_code];
$form_id = $this->input->post($this->prime_id);
if($form_id !== '-1'){ //Decryption
$decRslt = $this->cryptoDecrypt(base64_decode(urldecode($form_id)));
$form_id = (int)$decRslt['prime_id'];
if(!$form_id){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
}else{
$form_id = 0;
}
$previous_data = $this->get_previous_view($form_id);
$previous_data = $previous_data[0];
$form_post_data = array();
foreach($this->form_info as $setting){
$field_type = $setting->field_type;
$input_view_type = (int)$setting->input_view_type;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = $setting->field_isdefault;
$unique_field = (int)$setting->unique_field;
$view_name = $setting->view_name;
$date_type = $setting->date_type;
$loan_amount = $this->input->post('loan_amount');
$loan_type = $this->input->post('loan_type');
$apply_month = $this->input->post('apply_year');
$loan_date = date('Y-m-d',strtotime($this->input->post('loan_date')));
if((int)$field_type === 5){
$value = trim($this->input->post($label_id));
$pick_array[$label_id] = $value;
}else
if((int)$field_type === 7){
$multi_name = $label_id."[]";
$value = trim(implode(",",$this->input->post($multi_name) ?? []));
}else{
$value = trim($this->input->post($label_id));
}
if((int)$field_type === 4){
if((int)$date_type === 1){
$value = date('Y-m-d',strtotime($value));
}else{
$value = $value;
}
}else
if((int)$field_type === 13){
$value = date('Y-m-d H:i:s',strtotime($value));
}else
if((int)$field_type === 10){//File Upload
$value = $value;
$remove_upload_fname = "old_".$label_id;
$remove_upload_file = $this->input->post($remove_upload_fname);
if($value !== $remove_upload_file){
unlink($remove_upload_file);
}
}
//For replace special character
$value = str_replace('"',"~",$value);
$value = str_replace("'","`",$value);
//Sanitize SQL InJection chars
$value = $this->sanitize_input($value, $field_type);
if(($input_view_type === 1) || ($input_view_type === 2)){
if((int)$field_isdefault === 1){
$prime_qry_key .= $label_id.",";
$prime_qry_value .= '"'.$value.'",';
$prime_upd_query .= $label_id.' = "'.$value.'",';
if($unique_field === 1){
$prime_unq_chk = $label_id.'= "'.$value.'"';
$query = "select count(*) as rslt_count from $this->prime_table where $this->prime_id != $form_id and $prime_unq_chk";
$unq_chk[] = array('label_id'=>$label_id,'view_name'=>$view_name,'query'=>$query,);
}
}
}
}
$rslt_count = 0;
$can_process = array();
foreach($unq_chk as $unq_rslt){
$query = $unq_rslt['query'];
$label_id = $unq_rslt['label_id'];
$view_name = $unq_rslt['view_name'];
$unq_info = $this->db->query("CALL sp_a_run ('RUN','$query')");
$unq_result = $unq_info->result();
$unq_info->next_result();
if($unq_result){
$rslt_count = (int)$unq_result[0]->rslt_count;
if($rslt_count !== 0){
$can_process[] = $view_name." already exist";
}
}
}
//neha edit 27 MARCH2020
if($emp_code && $loan_type){
$based_on_qry = 'SELECT eligibility_based_on,eligibility_check from cw_loan_eligibility_settings where trans_status = 1';
$based_on_data = $this->db->query("CALL sp_a_run ('SELECT','$based_on_qry')");
$based_on_result = $based_on_data->result();
$based_on_data->next_result();
$eligibility_based_on = $based_on_result[0]->eligibility_based_on;
$eligibility_check = $based_on_result[0]->eligibility_check;
if((int)$eligibility_check === 1){
$loan_elig = $this->check_loan_eligibility($emp_code,$loan_type,$loan_date,$loan_amount,$eligibility_based_on);
if(!$loan_elig['success']){
$message = $loan_elig['message'];
if(count($message ?? []) > 1){
$message = implode(' and ',$message ?? []);
}else{
$message = $message[0];
}
echo json_encode(array('success' => FALSE, 'message' => $message));
exit(0);
}
}
}else{
$message = "Your employee code or loan type is empty";
echo json_encode(array('success' => FALSE, 'message' => $message));
exit(0);
}
$payroll_exit_qry = 'select count(prime_transactions_id) as payroll_rslt from cw_transactions where transactions_month = "'.$apply_month.'" and employee_code = "'.$emp_code.'" and trans_status=1';
$payroll_exit_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exit_qry')");
$payroll_exit_result = $payroll_exit_data->result();
$payroll_exit_data->next_result();
$payroll_count = (int)$payroll_exit_result[0]->payroll_rslt;
if($payroll_count > 0){
echo json_encode(array('success' => FALSE, 'message' => "Payroll Already Processed For this Apply Month.."));
exit(0);
}
if(count($can_process ?? []) > 0){
$can_process = array_values($can_process);
$can_process = implode(",<br/>", $can_process ?? []);
echo json_encode(array('success' => false, 'message' => $can_process,));
}else{
$created_on = date("Y-m-d H:i:s");
if((int)$form_id === 0){
$prime_qry_key .= "trans_created_by,trans_created_date";
$prime_qry_value .= '"'.$this->logged_id.'",'.'"'.$created_on.'"';
$prime_insert_query = "insert into $this->prime_table ($prime_qry_key) values ($prime_qry_value)";
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan DATA ADD",'user');
$insert_info = $this->db->query($prime_insert_query);
$loan_id = $this->db->insert_id();
$message = "Successfully added";
}else{
$prime_upd_query .= 'trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$prime_update_query = 'UPDATE '. $this->prime_table .' SET '. $prime_upd_query .' WHERE '. $this->prime_id .' = "'. $form_id .'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan DATA UPDATE",'user');
$this->db->query($prime_update_query);
//echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'insert_id' => $form_id));
$loan_id = $form_id;
$message = "Successfully updated";
}
$install_save = $this->installment_save($loan_id,$filter_keys);
if($install_save){
echo json_encode(array('success' => TRUE, 'message' => $message,'insert_id' => $loan_id));
}
}
// }
}
//MRJ START 04-FEB-2019
/* ==============================================================*/
/* =================== STATIC LOAN OPERATION - START ============*/
/* ==============================================================*/
//LOAD LOAN VIEW WITH DATA
public function installment_save($loan_id,$filter_keys){
$date = date("Y-m-d H:i:s");
$logged_id = $this->logged_id;
$loan_data_exit_qry = 'select count(*) as count_val from cw_loan_installment where trans_status = 1 and loan_id ='.$loan_id;
$loan_data_exit = $this->db->query("CALL sp_a_run ('RUN','$loan_data_exit_qry')");
$loan_data_rslt = $loan_data_exit->result();
$loan_data_exit->next_result();
$count = $loan_data_rslt[0]->count_val;
if((int)$count === 0){
$loan_qry = 'select * from cw_loan where trans_status = 1 and prime_loan_id ='.$loan_id;
$loan_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_qry')");
$loan_result = $loan_data->result();
$loan_data->next_result();
foreach($loan_result as $loan){
$loan_id = $loan->prime_loan_id;
$category = $loan->category;
$loan_type = $loan->loan_type;
$loan_date = $loan->loan_date;
$install = $loan->number_of_installment;
$loan_amount = $loan->loan_amount;
$interest_rate = $loan->interest_rate;
$per_month = $loan->per_month;
$apply_year = $loan->apply_year;
$emp_code = $loan->emp_code;
$total_amount = $loan->total_amount;
$install_year = $loan->apply_year;
$this_month = explode("-",$apply_year);
$this_month = mktime(0, 0, 0, date($this_month[0] - 1), 1, date($this_month[1]));
for ($i = 1; $i <= $install; ++$i) {
$pay_month = date('m-Y', strtotime($i.' month', $this_month));
$total_pay += $per_month;
if((int)$i === (int)$install){
$balance_amt = $total_amount - $total_pay;
$per_month = $per_month + $balance_amt;
}
$install_query = 'insert into cw_loan_installment (loan_id,category,emp_code,loan_type,loan_date,apply_year,loan_amount,interest_rate,number_of_installment,per_month,total_amount,installment_count,install_year,install_amount,trans_created_by,trans_created_date) values ("'.$loan_id.'","'.$category.'","'.$emp_code.'","'.$loan_type.'","'.$loan_date.'","'.$apply_year.'","'.$loan_amount.'","'.$interest_rate.'","'.$install.'","'.$per_month.'","'.$total_amount.'","'.$i.'","'.$pay_month.'","'.$per_month.'","'.$logged_id.'","'.$date.'")';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan Installment Added In Loan DATA ADD",'');
$install_info = $this->db->query($install_query);
}
}
}else{
$upd_qry = 'UPDATE cw_loan_installment SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where loan_id = "'.$loan_id.'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan Installment Updated In Loan DATA ADD",'');
$this->db->query($upd_qry);
$sts = 1;
if($sts){
//insert again
$loan_qry = 'select * from cw_loan where trans_status = 1 and prime_loan_id ='.$loan_id;
$loan_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_qry')");
$loan_result = $loan_data->result();
$loan_data->next_result();
foreach($loan_result as $loan){
$loan_id = $loan->prime_loan_id;
$category = $loan->category;
$loan_type = $loan->loan_type;
$loan_date = $loan->loan_date;
$install = $loan->number_of_installment;
$loan_amount = $loan->loan_amount;
$interest_rate = $loan->interest_rate;
$per_month = $loan->per_month;
$apply_year = $loan->apply_year;
$emp_code = $loan->emp_code;
$total_amount = $loan->total_amount;
$install_year = $loan->apply_year;
$this_month = explode("-",$apply_year);
$this_month = mktime(0, 0, 0, date($this_month[0] - 1), 1, date($this_month[1]));
for ($i = 1; $i <= $install; ++$i){
$pay_month = date('m-Y', strtotime($i.' month', $this_month));
$total_pay += $per_month;
if((int)$i === (int)$install){
$balance_amt = $total_amount - $total_pay;
$per_month = $per_month + $balance_amt;
}
$install_query = 'insert into cw_loan_installment (loan_id,category,emp_code,loan_type,loan_date,apply_year,loan_amount,interest_rate,number_of_installment,per_month,total_amount,installment_count,install_year,install_amount,trans_created_by,trans_created_date) values ("'.$loan_id.'","'.$category.'","'.$emp_code.'","'.$loan_type.'","'.$loan_date.'","'.$apply_year.'","'.$loan_amount.'","'.$interest_rate.'","'.$install.'","'.$per_month.'","'.$total_amount.'","'.$i.'","'.$pay_month.'","'.$per_month.'","'.$logged_id.'","'.$date.'")';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan Installment ReAdded In Loan DATA UPDATE",'');
$install_info = $this->db->query($install_query);
}
}
}
}
return true;
}
//LOAN EDIT DATA UPDATE(Previous code)
// public function update_data(){
// $loan_id = $this->input->post('loan_id');
// $installment_id = $this->input->post('installment_id');
// $install_amount = $this->input->post('install_amount');
// $loan_amount = $this->input->post('loan_amount');
// $per_month = $this->input->post('per_month');
// $installment_count = $this->input->post('installment_count');
// $installments = $this->input->post('installments');
// $install_year = $this->input->post('install_year');
// $loan_type = $this->input->post('loan_type');
// $date = date("Y-m-d H:i:s");
// $logged_id = $this->logged_id;
// //Get Paid Amount
// $paid_qry = 'select IFNULL(sum(install_amount),0) as paid from cw_loan_installment where trans_status = 1 and paid_status = 1 and loan_id ='.$loan_id;
// $paid_data = $this->db->query("CALL sp_a_run ('SELECT','$paid_qry')");
// $paid_result = $paid_data->result();
// $paid_data->next_result();
// $paid = $paid_result[0]->paid;
// $total_pending_amt = $loan_amount - $paid;
// $new_installment = 0;
// if((int)$installment_count === 1){
// $actual_pending = $loan_amount;
// }else{
// $actual_pending = $loan_amount - ($paid + $install_amount);
// }
// if($install_amount === $per_month){
// echo json_encode(array('success' => false, 'message' => "Installment amount and Per month amount should not be same...!!!"));
// exit(0);
// }
// if($install_amount > $total_pending_amt){
// echo json_encode(array('success' => false, 'message' => "Amount Greater than Loan Pending Amount!!!"));
// exit(0);
// }
// //echo "BSK $loan_id,$install_amount,$loop,$balance,$install_year,$per_month :: $actual_pending"; die;
// if($per_month > $total_pending_amt){
// $balance = $per_month - $install_amount;
// $this->insert_installment($loan_id,$install_amount,'0',$balance,$install_year);
// }else{
// if($per_month < $install_amount){ //IF Current month amt greater
// if((int)$installment_count === 1){ //If first month pay
// $count = $install_amount/$per_month;
// $balance_amt = $loan_amount - $install_amount;
// $bal_count = $balance_amt/$per_month;
// $final_bal = 0;
// $count_bal = 0;
// if(is_numeric($bal_count) && floor( $bal_count ) != $bal_count){
// $bal_count = (int)$bal_count;
// $bal_value = $bal_count * $per_month;
// $final_bal = $balance_amt - $bal_value;
// $loop = $bal_count + 1;
// }else{
// $loop = $bal_count;
// }
// $balance = $final_bal;
// }else{ // If Middle payments
// $balance_amt = $loan_amount - ($install_amount + $paid);
// $count = $balance_amt/$per_month;
// $count_value = 0;
// $count_bal = 0;
// if(is_numeric($count) && floor($count) != $count){
// $count = (int)$count;
// $count_value = $count * $per_month;
// $count_bal = $balance_amt - $count_value;
// $loop = $count + 1;
// }else{
// $loop = $count;
// }
// $balance = $count_bal;
// }
// }else
// if($per_month > $install_amount){
// if((int)$installment_count === 1){
// $loop = $installments;
// $balance = $per_month - $install_amount;
// }else{ // If Middle payments
// $balance_amt = $loan_amount - ($install_amount + $paid);
// $count = $balance_amt/$per_month;
// $count_value = 0;
// $count_bal = 0;
// if(is_numeric($count) && floor($count) != $count){
// $count = (int)$count;
// $count_value = $count * $per_month;
// $count_bal = $balance_amt - $count_value;
// $loop = $count + 1;
// }else{
// $loop = $count;
// }
// $balance = $count_bal;
// }
// }else{
// if((int)$installment_count === 1){
// $loop = $installments - 1;
// }
// }
// if($loop >= 0){
// $upd_query = 'UPDATE cw_loan_installment SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where paid_status = 0 and loan_id = "'.$loan_id.'" and prime_loan_installment_id > "'.$installment_id.'"';
// $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
// $this->insert_installment($loan_id,$install_amount,$loop,$balance,$install_year);
// }
// }
// //die;
// if($installment_id > 0){
// $upd_query = 'UPDATE cw_loan_installment SET install_amount = "'.$install_amount.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_loan_installment_id = "'.$installment_id.'"';
// $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
// echo json_encode(array('success' => true, 'message' => "Updated successfully!!!"));
// }
// }
//LOAN EDIT DATA UPDATE (New code Ragu Changed)
public function update_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);
}
$loan_id = $this->input->post('loan_id');
$installment_id = $this->input->post('installment_id');
$install_amount = $this->input->post('install_amount');
$loan_amount = $this->input->post('loan_amount');
$per_month = $this->input->post('per_month');
$installment_count = $this->input->post('installment_count');
$installments = $this->input->post('installments');
$install_year = $this->input->post('install_year');
$loan_type = $this->input->post('loan_type');
$emp_code = $this->input->post('emp_code');
$category = $this->input->post('category');
$loan_date = $this->input->post('loan_date');
$apply_year = $this->input->post('apply_year');
$total_amount = $this->input->post('total_amount');
$interest_rate = $this->input->post('interest_rate');
$date = date("Y-m-d H:i:s");
$logged_id = $this->logged_id;
$sum_install_amt = array_sum($install_amount);
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Employee code" =>$emp_code];
$entity = '';
if((int)$loan_amount !== (int)$sum_install_amt){
echo json_encode(array('success' => false, 'message' => "Installment amount should be same as Loan Amount...!"));
exit(0);
}
for($i=0;$i<= count($installment_count ?? []);$i++){
$id = $installment_id[$i];
$month_year = $install_year[$i];
$amount = $install_amount[$i];
$inst_count = $installment_count[$i];
if($amount === ""){
$amount = 0;
}
if($id > 0){
$update_query = 'UPDATE cw_loan_installment SET install_amount='.$amount.',trans_updated_by = '. $this->logged_id .',trans_updated_date = "'.$date.'" WHERE prime_loan_installment_id = '.$id .'';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan Installment DATA UPDATE",'user');
$update_info = $this->db->query($update_query);
}else
if($id === "0"){
$ins_qry ='insert into cw_loan_installment (loan_id,category,emp_code,loan_type,loan_date,apply_year,loan_amount,interest_rate,total_amount,installment_count,install_year,install_amount,per_month,number_of_installment,trans_created_by,trans_created_date) values ("'.$loan_id.'","'.$category.'","'.$emp_code.'","'.$loan_type.'","'.$loan_date.'","'.$apply_year.'","'.$loan_amount.'","'.$interest_rate.'","'.$total_amount.'","'.$inst_count.'","'.$month_year.'","'.$amount.'","'.$per_month.'","'.$installment_count.'","'.$logged_id.'","'.$date.'")';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan Installment DATA ADD",'user');
$insert_info = $this->db->query($ins_qry);
}
}
if($ins_qry || $update_query){
echo json_encode(array('success' => true, 'message' => "Installment is Updated...!"));
}
}
public function insert_installment($loan_id,$install_amount,$loop,$balance,$install_year){
$loan_qry = 'select * from cw_loan where trans_status = 1 and prime_loan_id ='.$loan_id;
$loan_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_qry')");
$loan_result = $loan_data->result();
$loan_data->next_result();
$category = $loan_result[0]->category;
$emp_code = $loan_result[0]->emp_code;
$loan_type = $loan_result[0]->loan_type;
$loan_date = $loan_result[0]->loan_date;
$apply_year = $loan_result[0]->apply_year;
$loan_amount = $loan_result[0]->loan_amount;
$interest_rate = $loan_result[0]->interest_rate;
$number_of_installment = $loan_result[0]->number_of_installment;
$total_amount = $loan_result[0]->total_amount;
$per_month = $loan_result[0]->per_month;
$apply_date = date("Y-m-d",strtotime("01-".$install_year));
$date = date("Y-m-d H:i:s");
$logged_id = $this->logged_id;
if((int)$loop >= 0){
for($i=1;$i<=$loop;$i++){
$installment_count = $installment_count + 1;
if((int)$balance !== 0 && $balance !== $per_month && $i === $loop){
$per_month = $balance;
}
if((int)$balance !== 0 && ($install_amount < $per_month) && ((int)$i === (int)$loop)){
$per_month = $balance;
}
$next_date = date("m-Y", strtotime("+$installment_count month", strtotime($apply_date)));
$install_query = 'insert into cw_loan_installment (loan_id,category,emp_code,loan_type,loan_date,apply_year,loan_amount,interest_rate,number_of_installment,per_month,total_amount,installment_count,install_year,install_amount,trans_created_by,trans_created_date) values ("'.$loan_id.'","'.$category.'","'.$emp_code.'","'.$loan_type.'","'.$loan_date.'","'.$apply_year.'","'.$loan_amount.'","'.$interest_rate.'","'.$number_of_installment.'","'.$per_month.'","'.$total_amount.'","'.$installment_count.'","'.$next_date.'","'.$per_month.'","'.$logged_id.'","'.$date.'")';
$install_info = $this->db->query("CALL sp_a_run ('INSERT','$install_query')");
$install_result = $install_info->result();
$install_info->next_result();
}
}else{
if((int)$balance !== 0){
$per_month = $balance;
}
$installment_count = (int)$number_of_installment;
//echo "BSK $installment_count :: $apply_date"; die;
$next_date = date("m-Y", strtotime("+$installment_count month", strtotime($apply_date)));
$install_query = 'insert into cw_loan_installment (loan_id,category,emp_code,loan_type,loan_date,apply_year,loan_amount,interest_rate,number_of_installment,per_month,total_amount,installment_count,install_year,install_amount,trans_created_by,trans_created_date) values ("'.$loan_id.'","'.$category.'","'.$emp_code.'","'.$loan_type.'","'.$loan_date.'","'.$apply_year.'","'.$loan_amount.'","'.$interest_rate.'","'.$number_of_installment.'","'.$per_month.'","'.$total_amount.'","'.$installment_count.'","'.$next_date.'","'.$per_month.'","'.$logged_id.'","'.$date.'")';
$install_info = $this->db->query("CALL sp_a_run ('INSERT','$install_query')");
$install_result = $install_info->result();
$install_info->next_result();
}
return true;
}
//ADD NEW DATA IN INSTALLMENT
/*public function add_install(){
$loan_id = $this->input->post('loan_id');
$next_installment = $this->input->post('next_installment');
$install_amount = $this->input->post('rslt_amt');
$date = date("Y-m-d H:i:s");
$logged_id = $this->logged_id;
$loan_qry = 'select * from cw_loan where trans_status = 1 and prime_loan_id ='.$loan_id;
$loan_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_qry')");
$loan_result = $loan_data->result();
$loan_data->next_result();
$category = $loan_result[0]->category;
$emp_code = $loan_result[0]->emp_code;
$loan_type = $loan_result[0]->loan_type;
$loan_date = $loan_result[0]->loan_date;
$apply_year = $loan_result[0]->apply_year;
$loan_amount = $loan_result[0]->loan_amount;
$interest_rate = $loan_result[0]->interest_rate;
$number_of_installment = $loan_result[0]->number_of_installment;
$total_amount = $loan_result[0]->total_amount;
$per_month = $loan_result[0]->per_month;
$installment_count = 1 + (int)$number_of_installment;
$install_query = 'insert into cw_loan_installment (loan_id,category,emp_code,loan_type,loan_date,apply_year,loan_amount,interest_rate,number_of_installment,per_month,total_amount,installment_count,install_year,install_amount,trans_created_by,trans_created_date) values ("'.$loan_id.'","'.$category.'","'.$emp_code.'","'.$loan_type.'","'.$loan_date.'","'.$apply_year.'","'.$loan_amount.'","'.$interest_rate.'","'.$number_of_installment.'","'.$per_month.'","'.$total_amount.'","'.$installment_count.'","'.$next_installment.'","'.$install_amount.'","'.$logged_id.'","'.$date.'")';
$install_info = $this->db->query("CALL sp_a_run ('INSERT','$install_query')");
$install_result = $install_info->result();
$install_info->next_result();
$last_id = $install_result[0]->ins_id;
if($last_id){
echo json_encode(array('success' => true, 'message' => "Installment amount is successfully added!!!"));
}else{
echo json_encode(array('success' => false, 'message' => "Unable to process your request"));
}
}*/
//LOAD DATA TO PREVIOUS ROW
public function add_prev(){
$installment_id = (int)$this->input->post('installment_id');
$last_amt = $this->input->post('last_amt');
$updated_on = date("Y-m-d H:i:s");
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Installment Id" =>$installment_id];
if($last_amt > 0){
$update_query = 'UPDATE cw_loan_installment SET install_amount="'.$last_amt.'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$updated_on.'" WHERE prime_loan_installment_id = "'. $installment_id .'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan Installment DATA UPDATE",'');
$this->db->query($update_query);
echo json_encode(array('success' => TRUE, 'msg' => "Successfully Update your installment amount!"));
}else{
echo json_encode(array('success' => FALSE, 'msg' => "Server timeout!"));
}
}
//LOAN INSTALLMENT AMOUNT
public function installment($loan_id){
$decRslt = $this->cryptoDecrypt(base64_decode(urldecode($loan_id)));
$loan_id = $decRslt['prime_id'];
$install_qry = 'select * from cw_loan_installment where trans_status = 1 and loan_id ='.$loan_id;
$install_data = $this->db->query("CALL sp_a_run ('SELECT','$install_qry')");
$install_result = $install_data->result();
$install_data->next_result();
$emp_code = $install_result[0]->emp_code;
$data['install_result'] = $install_result;
//current payroll month
$last_trans_month_qry = 'select transactions_month from cw_transactions where employee_code="'.$emp_code.'" and trans_status = 1 order by transactions_month desc limit 1';
$last_trans_month_data = $this->db->query("CALL sp_a_run ('SELECT','$last_trans_month_qry')");
$last_trans_month_result = $last_trans_month_data->result();
$last_trans_month_data->next_result();
$transactions_month = $last_trans_month_result[0]->transactions_month;
$transactions_month = date("Y-m-d",strtotime("01-".$transactions_month));
$next_transactions = date('m-Y', strtotime('+1 month', strtotime($transactions_month)));
$data['encKey'] = $this->generateKey();
$data['payroll_month'] = $next_transactions;
$this->load->view("$this->control_name/installment",$data);
}
//LOAD FORECLOSE LOAN VIEW WITH DATA
public function loan_data($loan_id){
$decRslt = $this->cryptoDecrypt(base64_decode(urldecode($loan_id)));
$loan_id = $decRslt['prime_id'];
$data['prime_loan_id'] = $loan_id;
$paid_qry = 'select (SELECT loan_amount from cw_loan where prime_loan_id = "'.$loan_id.'") as loan_amount,IFNULL(sum(install_amount),0) as paid from cw_loan_installment where paid_status = 1 and trans_status=1 and loan_id = "'.$loan_id.'"';
$paid_data = $this->db->query("CALL sp_a_run ('SELECT','$paid_qry')");
$paid_result = $paid_data->result();
$paid_data->next_result();
$paid = $paid_result[0]->paid;
$loan_amount = $paid_result[0]->loan_amount;
if($paid > 0){
$balance = $loan_amount - $paid;
}else{
$balance = $loan_amount;
}
$data['bal_amt'] = $balance;
$foreclose_qry = 'select IFNULL(sum(install_amount),0) as pay_amount,IFNULL(total_amount,0) as tot_amt,loan_date from cw_loan_installment where trans_status = 1 and install_year < curdate() and loan_id ='.$loan_id;
$foreclose_data = $this->db->query("CALL sp_a_run ('SELECT','$foreclose_qry')");
$foreclose_result = $foreclose_data->result();
$foreclose_data->next_result();
$data['foreclose_result'] = $foreclose_result;
$foreclose_list = $this->foreclose_loan_detail($loan_id);
$data['foreclose_list'] = $foreclose_list;
$foreclose_details = 'select * from cw_loan where trans_status = 1 and prime_loan_id ='.$loan_id;
$foreclose_details_data = $this->db->query("CALL sp_a_run ('SELECT','$foreclose_details')");
$foreclose_details_data_rslt = $foreclose_details_data->result();
$foreclose_details_data->next_result();
$foreclose_sts = $foreclose_details_data_rslt[0]->foreclose_sts;
$data['foreclose_sts'] = $foreclose_sts;
$data['loan_date'] = $foreclose_details_data_rslt[0]->loan_date;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/loan_data",$data);
}
//LOAD FORECLOSE DATE AND AMOUNT SAVE
public function save_foreclose_loan(){
$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);
}
$prime_loan_id = (int)$this->input->post('prime_loan_id');
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Loan Id" =>$prime_loan_id];
$loan_foreclose_date = date('Y-m-d',strtotime($this->input->post('loan_foreclose_date')));
$loan_foreclose_amount = $this->input->post('loan_foreclose_amount');
$updated_on = date("Y-m-d H:i:s");
if($loan_foreclose_amount){
$update_query = 'UPDATE cw_loan SET loan_foreclose_date="'.$loan_foreclose_date.'", loan_foreclose_amount="'.$loan_foreclose_amount.'", foreclose_sts= "1", trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$updated_on.'" WHERE prime_loan_id = "'. $prime_loan_id.'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan Foreclose DATA UPDATE",'user');
$update_res = $this->db->query($update_query);
if($update_res){
//paid status 2 is foreclose
$upd_query = 'UPDATE cw_loan_installment SET paid_status = 2 where loan_id = "'. $prime_loan_id.'" and paid_status = 0 and trans_status = 1';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan Installment Foreclose DATA UPDATE",'');
$upd_result = $this->db->query($upd_query);
}
if($upd_result){
$msg ="Successfully your loan is closed!";
}
}else{
$msg ="Server Timeout";
}
$foreclose_list = $this->foreclose_loan_detail($prime_loan_id);
echo json_encode(array('success' => TRUE, 'message' => $msg,'foreclose_list' => $foreclose_list));
}
public function foreclose_loan_detail($prime_loan_id){
$foreclose_loan_list = 'select prime_loan_id,loan_foreclose_date, loan_foreclose_amount,foreclose_sts from cw_loan where trans_status = 1 and foreclose_sts = 1 and prime_loan_id ='.$prime_loan_id;
$foreclose_loan_info = $this->db->query("CALL sp_a_run ('SELECT','$foreclose_loan_list')");
$foreclose_loan_result = $foreclose_loan_info->result();
$foreclose_loan_info->next_result();
foreach($foreclose_loan_result as $rslt){
$loan_foreclose_date = date('d-m-Y',strtotime($rslt->loan_foreclose_date));
$loan_foreclose_amount = $rslt->loan_foreclose_amount;
$foreclose_sts = $rslt->foreclose_sts;
$prime_loan_id = $rslt->prime_loan_id;
// <td style='text-align:center;'><a class='btn btn-xs btn-danger' onclick=remove_foreclose('$prime_loan_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
$tr_line .= "<tr>
<td style='text-align:center;'>$loan_foreclose_date</td>
<td style='text-align:center;'>$loan_foreclose_amount</td>
</tr>";
}
if((int)$foreclose_sts > 0){
// <th style='text-align:center;'>Delete</th>
$foreclose_content = "<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th style='text-align:center;'>Foreclose Date</th>
<th style='text-align:center;'>Foreclose Amount</th>
</tr>
$tr_line
</table>";
}
return $foreclose_content;
}
public function remove_foreclose(){
$prime_loan_id = (int)$this->input->post('loan_id');
$updated_on = date("Y-m-d H:i:s");
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Loan Id" =>$prime_loan_id ];
if($prime_loan_id){
$update_query = 'UPDATE cw_loan SET foreclose_sts= "0", trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$updated_on.'" WHERE prime_loan_id = "'. $prime_loan_id.'"';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan Foreclose Remove DATA UPDATE",'user');
$this->db->query($update_query);
echo json_encode(array('success' => TRUE, 'msg' => "Your loan is reopened!"));
}else{
echo json_encode(array('success' => FALSE, 'msg' => "Server Timeout"));
}
}
public function get_mapped_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);
}
$role = $this->input->post('role');
$suggest_query = 'select CONCAT("<option value =",employee_code," >",emp_name," - ",employee_code,"</option>") as result_data from cw_employees where role="'.$role.'" and trans_status = 1 and cw_employees.termination_status = 0';
$suggest_data = $this->db->query("CALL sp_a_run ('RUN','$suggest_query')");
$suggest_result = $suggest_data->result();
$suggest_data->next_result();
$result_data = array_column($suggest_result,'result_data');
$option = "<option value =''>--- Select Client ---</option>".implode('',$result_data ?? []);
if(empty($suggest_result)){
$option = "<option value ='0'>No data Found</option>";
}
echo json_encode(array('success' => TRUE, 'data' => $option));
}
public function check_process_month(){
$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);
}
$loan_date = $this->input->post('loan_date');
$category = $this->input->post('category');
$loan_date_month = date('m-Y',strtotime($loan_date));
$loan_dob_date = date('Y-m-d',strtotime($loan_date));
$emp_code = $this->input->post('emp_code');
if(!$emp_code){
echo json_encode(array('success' => False, 'msg' => "Please Select Employee..."));
exit(0);
}
/*if(strtotime($loan_dob_date) < strtotime(date('Y-m-d'))){
echo json_encode(array('success' => False, 'msg' => "Loan Date Should not be a Past Date..."));
exit(0);
}*/
$find_emp_doj_qry = 'select count(*) as rslt_count from cw_employees where trans_status = 1 and employee_code ="'.$emp_code.'" and date_of_joining <= "'.$loan_dob_date.'"';
$find_emp_doj_info = $this->db->query("CALL sp_a_run ('SELECT','$find_emp_doj_qry')");
$find_emp_doj_result = $find_emp_doj_info->result();
$find_emp_doj_info->next_result();
$find_emp_doj = $find_emp_doj_result[0]->rslt_count;
//Get Month Days start and end date
$month_day_qry = 'select category,day_conditions,day_count,day_start,day_end from cw_month_day where cw_month_day.trans_status = 1 and category ="'.$category.'"';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result();
$month_day_data->next_result();
if($month_day_result){
$day_conditions = $month_day_result[0]->day_conditions;
$day_count = $month_day_result[0]->day_count;
$day_start = $month_day_result[0]->day_start;
$day_end = $month_day_result[0]->day_end;
if((int)$day_conditions === 3){
$prev_month = date("Y-m-".$day_start,strtotime("-1 month", strtotime($loan_date)));
$end_month = date("Y-m-".$day_end, strtotime($loan_date));
}else{
$sal_start = '01';
if((int)$day_conditions === 2){
$day_end = date("t");
}
$prev_month = date("Y-m-".$sal_start,strtotime($loan_date));
$end_month = date("Y-m-".$day_end,strtotime($loan_date));
}
}
$end_date = strtotime($end_month);
$start_date = strtotime($prev_month);
$loan_date = strtotime($loan_date);
$process_month = date("m-Y",strtotime($end_month));
$payroll_count = '';
if(($loan_date >= $start_date) && ($loan_date <= $end_date)){
$payroll_exit_qry = 'select count(prime_transactions_id) as payroll_rslt from cw_transactions where transactions_month= "'.$process_month.'" and trans_status=1';
$payroll_exit_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exit_qry')");
$payroll_exit_result = $payroll_exit_data->result();
$payroll_exit_data->next_result();
$payroll_count = $payroll_exit_result[0]->payroll_rslt;
}
if((int)$payroll_count > 0){
echo json_encode(array('success' => False, 'msg' => "Payroll Processed For this month..."));
}else
if((int)$find_emp_doj === 0){
echo json_encode(array('success' => False, 'msg' => "Date of Joining Greater than this Month..."));
}else{
echo json_encode(array('success' => TRUE, 'msg' => "Loan date is Proceed!"));
}
}
/* ==============================================================*/
/* =================== STATIC LOAN OPERATION - END ============*/
/* ==============================================================*/
//UPDATE STATUS TO DELETE IN MODULE PRIMARY TABLE
public function delete(){
//Encryption
$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);
}
$delete_ids = implode(",",$this->input->post('delete_ids') ?? []);
$can_process = TRUE;
$delete_status = FALSE;
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Delete Id" => $delete_ids];
if($this->check_delete_status()){
$delete_status = TRUE;
$check_table_query = 'SELECT GROUP_CONCAT(prime_module_id) as prime_module_id,GROUP_CONCAT(label_name) as label_name from cw_form_setting WHERE pick_table = "'. $this->prime_table .'" and trans_status = 1 ';
$check_table_info = $this->db->query("CALL sp_a_run ('SELECT','$check_table_query')");
$check_table_rlst = $check_table_info->row();
$check_table_info->next_result();
if($check_table_rlst->prime_module_id){
$prime_module_id = explode(",",$check_table_rlst->prime_module_id);
$label_name = explode(",",$check_table_rlst->label_name);
$i = 0;
foreach($prime_module_id as $check_modules){
$table_name = "cw_".$check_modules;
$select_table = "$table_name.$label_name[$i]";
$select_where = " and $table_name.$label_name[$i] in ($delete_ids)";
$check_module_query = 'SELECT '.$select_table.' from '.$table_name.' WHERE '.$table_name.'.trans_status = 1 '.$select_where.' LIMIT 0,1';
$check_module_info = $this->db->query("CALL sp_a_run ('SELECT','$check_module_query')");
$values_count = $check_module_info->num_rows();
$check_module_info->next_result();
if((int)$values_count > 0){
$table_names .= "$check_modules,";
$can_process = False;
$delete_status = False;
}
$i++;
}
}
$table_names = rtrim($table_names,",");
if($delete_status){
$delete_query = 'DELETE FROM '. $this->prime_table .' WHERE '. $this->prime_id .' in ('. $delete_ids .')';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan DATA DELETE",'user');
if($this->db->query($delete_query)){
$row_set_query = 'SELECT form_view_label_name from cw_form_view_setting where form_view_type = "3" and prime_view_module_id = "'. $this->control_name .'" and trans_status = 1';
$row_set_info = $this->db->query("CALL sp_a_run ('SELECT','$row_set_query')");
$row_count = (int)$row_set_info->num_rows();
$row_set_info->next_result();
if($row_count !== 0){
$row_set_result = $row_set_info->result();
$delete_table_name = '';
$delete_table_condition = '';
foreach($row_set_result as $row_set){
$row_set_table_name = "cw_".$this->control_name."_".$row_set->form_view_label_name;
$delete_table_name .= "$row_set_table_name,";
$delete_table_condition .= " $row_set_table_name.$this->prime_id in ('$delete_ids') and";
}
$delete_table_name = rtrim($delete_table_name,',');
$delete_table_condition = rtrim($delete_table_condition,'and');
$delete_row_set_query = 'DELETE FROM '. $delete_table_name .' WHERE '. $delete_table_condition.'';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Loan Depended DATA DELETE",'');
$this->db->query($delete_row_set_query);
}
$can_process = False;
}
}
}
if($can_process){
$created_on = date("Y-m-d h:i:s");
$delete_query = 'UPDATE ' . $this->prime_table . ' LEFT JOIN cw_loan_installment ON cw_loan_installment.loan_id = ' . $this->prime_table . '.' . $this->prime_id . ' AND cw_loan_installment.paid_status IN (1,2) SET ' . $this->prime_table . '.trans_status = 0, ' . $this->prime_table . '.trans_deleted_by = "' . $this->logged_id . '",' . $this->prime_table . '.trans_deleted_date = "' . $created_on . '" WHERE ' . $this->prime_table . '.' . $this->prime_id . ' IN (' . $delete_ids . ') AND cw_loan_installment.loan_id IS NULL';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys, "Loan Delete DATA UPDATE", "user");
if($this->db->query($delete_query)){
echo json_encode(array('success' => TRUE,'message' => 'Eligible loans deleted successfully'));
}else{
echo json_encode(array('success' => FALSE,'message' => 'Unable to delete loans'
));
}
}else
if($delete_status){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete, This value is already used in $table_names modules"));
}
}
//CHECK UNIQUE FIELD STATUS
public function check_delete_status(){
$check_delete_query = 'SELECT GROUP_CONCAT(unique_field) as unique_field from cw_form_setting WHERE prime_module_id = "'. $this->control_name .'" and trans_status = 1 ';
$check_delete_info = $this->db->query("CALL sp_a_run ('SELECT','$check_delete_query')");
$check_delete_rlst = $check_delete_info->row();
$check_delete_info->next_result();
$unique_info = explode(",",$check_delete_rlst->unique_field);
if(in_array('1', $unique_info ?? [])){
return TRUE;
}else{
return FALSE;
}
}
//UPDATE STATUS TO DELETE FOR UPLOAD FILES or DOCUMENTS
public function remove_file(){
//Encryption
$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);
}
$prime_id_val = $this->input->post('prime_id_val');
$is_defult = (int)$this->input->post('is_defult');
$input_name = $this->input->post('input_name');
$table_name = '';
if($is_defult === 1){
$table_name = $this->prime_table;
}else
if($is_defult === 2){
$table_name = $this->cf_table;
}
if($table_name){
$created_on = date("Y-m-d h:i:s");
$set_query = $input_name .' = "" ,trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE '.$table_name .' SET '. $set_query .' WHERE '. $this->prime_id .' = "'. $prime_id_val .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
//IMPORT FILE VIEW INFORMATION
public function import(){
$data['encKey'] = $this->generateKey();
$data['module_id'] = $this->control_name;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "'.$this->control_name.'" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach($excel_result as $excel){
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
$this->load->view("$this->control_name/import",$data);
}
public function loan_excel($Payload){
//Decryption
$_POST = $this->cryptoDecrypt(base64_decode(urldecode($Payload)));
$module_id = $this->input->post('module_id');
$excel_format = $this->input->post('excel_format');
$excel_format_qry = 'select excel_name,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_util_excel_format on cw_util_excel_format.prime_excel_format_id = cw_util_excel_format_line.prime_excel_format_id where excel_line_module_id = "'.$module_id.'" and cw_util_excel_format_line.prime_excel_format_id ="'.$excel_format.'" and cw_util_excel_format.trans_status = 1 and cw_util_excel_format_line.trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_name = str_replace(' ', '_', $excel_result[0]->excel_name);
$obj = new Spreadsheet();
//Set the first row as the header row
foreach($excel_result as $excel){
$excel_line_column_name = $excel->excel_line_column_name;
$excel_line_value = $excel->excel_line_value;
$obj->getActiveSheet()->setCellValue($excel_line_value."1", $excel_line_column_name);
}
// Rename worksheet name
$filename= $excel_name.".xls"; //save our workbook as this file name
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename= "'.$filename.'"');
header('Cache-Control: max-age=0');
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($obj, 'Xls');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
echo json_encode(array('success' => TRUE, 'output' => $obj));
}
//NEHA EDIT 27MARCH2020 (CHECK LOAN ELIGIBLE BEFORE INSERTING)
public function check_loan_eligibility($emp_code,$loan_type,$loan_date,$loan_amount,$eligibility_based_on){
$mapping_data = $this->Hr_methods_model->get_mapped_db_column(7);
$fixed_gross_db = $mapping_data['fixed_gross'];
if($fixed_gross_db != ''){
$based_on = str_replace("cw_","",$eligibility_based_on);
if($based_on === 'category'){
$based_on = 'role';
}else{
$based_on = str_replace("cw_","",$eligibility_based_on);
}
$loan_qry = 'SELECT '.$based_on.','.$fixed_gross_db.',date_of_joining,loan_date,IFNULL(count(emp_code),0) as number_of_loan,IFNULL(sum(loan_amount),0) as taken_loan_amount from cw_employees inner join cw_loan on cw_employees.employee_code = cw_loan.emp_code where employee_code = "'.$emp_code.'" and cw_employees.trans_status = 1';
$loan_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_qry')");
$loan_result = $loan_data->result();
$loan_data->next_result();
$fixed_gross = $loan_result[0]->$fixed_gross_db;
$based_on_type = $loan_result[0]->$based_on;
$date_of_joining = $loan_result[0]->date_of_joining;
$already_taken_loan = $loan_result[0]->number_of_loan;
$taken_loan_amount = $loan_result[0]->taken_loan_amount;
$total_gross = $fixed_gross * 12;
$doj = strtotime($date_of_joining);
$loan_date = strtotime($loan_date);
$loan_apply_amt = $loan_amount + $taken_loan_amount;
//total year working years
$tot_month = ((($loan_date - $doj)/60/60/24)/365)*12;
$month_val = round($tot_month,2);
$tot_month = floor($month_val);
$eligibility_qry = 'SELECT gross_field,less_than_six,one_to_two,two_to_three,greater_than_three,number_of_loan from cw_loan_eligibility where eligibility_name = "'.$based_on_type.'" and cw_loan_eligibility.trans_status = 1';
$eligibility_data = $this->db->query("CALL sp_a_run ('SELECT','$eligibility_qry')");
$eligibility_result = $eligibility_data->result();
$eligibility_data->next_result();
$gross_field = $eligibility_result[0]->gross_field;
$less_than_six = $eligibility_result[0]->less_than_six;
$one_to_two = $eligibility_result[0]->one_to_two;
$two_to_three = $eligibility_result[0]->two_to_three;
$greater_than_three = $eligibility_result[0]->greater_than_three;
$number_of_loan = $eligibility_result[0]->number_of_loan;
$sts_gross = TRUE;
$message = array();
if($tot_month < 6){
$sts_gross = FALSE;
$message[] = "Your Joining should be atleast Six month for loan apply";
return array('success' => $sts_gross, 'message' => $message);
}else
if($tot_month >= 6 && $tot_month < 12){
$sts_gross = TRUE;
$message = array();
$loan_eligible_amt = ($fixed_gross * $less_than_six)/100;
if($loan_apply_amt > $loan_eligible_amt){
$sts_gross = FALSE;
$message[] = "Your already take $taken_loan_amount and you can take total of less then or eqaul to $loan_eligible_amt";
}
if($total_gross < $gross_field){
$sts_gross = FALSE;
$message[] = "Your CTC is lower for loan eligibility";
}
if($number_of_loan <= $already_taken_loan){
$sts_gross = FALSE;
$message[] = "you are eligible for only $number_of_loan times loans";
}
return array('success' => $sts_gross, 'message' => $message);
}else
if($tot_month >= 12 && $tot_month < 24){
$sts_gross = TRUE;
$message = array();
$loan_eligible_amt = ($fixed_gross * $one_to_two)/100;
if($loan_apply_amt > $loan_eligible_amt){
$sts_gross = FALSE;
$message[] = "Your already take $taken_loan_amount and you can take total of less then or eqaul to $loan_eligible_amt";
}
if($total_gross < $gross_field){
$sts_gross = FALSE;
$message[] = "Your CTC is lower for loan eligibility";
}
if($number_of_loan <= $already_taken_loan){
$sts_gross = FALSE;
$message[] = "you are eligible for only $number_of_loan times loans";
}
return array('success' => $sts_gross, 'message' => $message);
}else
if($tot_month >= 24 && $tot_month < 36){
$sts_gross = TRUE;
$message = array();
$loan_eligible_amt = ($fixed_gross * $two_to_three)/100;
if($loan_apply_amt > $loan_eligible_amt){
$sts_gross = FALSE;
$message[] = "Your already take $taken_loan_amount and you can take total of less then or eqaul to $loan_eligible_amt";
}
if($total_gross < $gross_field){
$sts_gross = FALSE;
$message[] = "Your CTC is lower for loan eligibility";
}
if($number_of_loan <= $already_taken_loan){
$sts_gross = FALSE;
$message[] = "you are eligible for only $number_of_loan times loans";
}
return array('success' => $sts_gross, 'message' => $message);
}else
if($tot_month >= 36){
$sts_gross = TRUE;
$message = array();
$loan_eligible_amt = ($fixed_gross * $greater_than_three)/100;
if($loan_apply_amt > $loan_eligible_amt){
$sts_gross = FALSE;
$message[] = "Your already take $taken_loan_amount and you can take total of less then or eqaul to $loan_eligible_amt";
}
if($total_gross < $gross_field){
$sts_gross = FALSE;
$message[] = "Your CTC is lower for loan eligibility";
}
if($number_of_loan <= $already_taken_loan){
$sts_gross = FALSE;
$message[] = "you are eligible for only $number_of_loan times loans";
}
return array('success' => $sts_gross, 'message' => $message);
}
}else{
$message = array();
$message[] = "function colmumn is not mapping";
return array('success' => FALSE, 'message' => $message);
}
}
//NEHA EDIT END 28MARCH2020
// AUTO COMPLETE [MS 06-03-2025]
public function auto_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....'));
exit(0);
}
$search_term = $this->input->post_get('term');
$category = $this->input->post_get('category');
$search_query = 'SELECT employee_code, CONCAT(employee_code," - ",emp_name) as label FROM cw_employees WHERE (employee_code LIKE "%'.$search_term.'%" OR emp_name LIKE "%'.$search_term.'%") AND trans_status = 1 AND role = "'.$category.'" LIMIT 10';
$search_data = $this->db->query("CALL sp_a_run ('SELECT','$search_query')");
$search_result = $search_data->result();
$search_data->next_result();
$suggestion = [];
foreach ($search_result as $row) {
$suggestion[] = ['value' => $row->employee_code , 'label' => $row->label , 'display_name' => $row->label];
}
if(empty($suggestion)){
$suggestion[] = array('value' => "", 'label' => "No data found for this search" ,'display_name' => 'No data found for this search');
}
echo json_encode($suggestion);
}
}
?>