File: /home/cafsindia/uds.cafsinfotech.in/application/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");
class Loan extends Action_controller{
public function __construct(){
parent::__construct('loan');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
// $this->collect_base_info();
$this->load->model('Hr_methods_model');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$this->page_info();
$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;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
public function get_page_info(){
$this->page_info();
echo json_encode(array('success' => TRUE,'pick_list' => $this->pick_list));
}
//LOAD TABEL WITH FILTERS
//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' => 'Invalid Request..'));
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']);
$this->select_query = str_replace("employee_code", "cw_loan.employee_code,wbs_element,position,cw_loan.trans_created_date", $this->select_query); // because join employee table also
$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 = "";
//DR COMMAND CODE FOR WAITING TO ADD A NEW CHANGES
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.'%"';
}
}
}
}
$common_search .= ' or wbs_element like "'.$search.'%" or position like "'.$search.'%"'; //Added for Employee Master
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.' inner join cw_employees on cw_employees.employee_code = '.$this->prime_table.'.employee_code and FIND_IN_SET(cw_employees.personal_code, "'.$this->logged_area_access.'") where '.$this->prime_table.'.trans_status = 1 '.$role_condition.$fliter_query.$common_search;
$search_query .= " inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code where $this->prime_table.trans_status = 1 and FIND_IN_SET(cw_employees.personal_code, '$this->logged_area_access') $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' => 'Invalid Request..','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;//all pick result
//PENDING TO ADD
$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;
//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();
$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' => 'Invalid Request..','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{
$previous_pick_value= $this->input->post("previous_pick_value");
$previous_data = json_decode($previous_pick_value, true);
$unq_chk = array();
$prime_qry_key = "";
$prime_qry_value = "";
$prime_upd_query = "";
$cf_qry_key = "";
$cf_qry_value = "";
$cf_upd_query = "";
$cf_has = false;
//Decrypt Form id
$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' => 'Invalid Request..','table_data' => ""));
exit(0);
}
}else{
$form_id = 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;
$emp_code = $this->input->post('employee_code');
$loan_amount = $this->input->post('loan_amount');
$loan_type = $this->input->post('loan_type');
$no_of_install = $this->input->post('number_of_installment');
$loan_date = date('Y-m-d',strtotime($this->input->post('loan_date')));
if($label_id === 'apply_year'){
$apply_year = $this->input->post('apply_year');
}
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 === 8){//textbox only
$value = str_replace('"',"~",$value);
$value = str_replace("'","`",$value);
$value = str_replace("&","^",$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);
}
}
//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);
}
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{
//ALREADY LOAN EXIST BASED ON EMPLOYEE CODE AND LOAN TYPE
if($emp_code){
$in_act_emp_qry = 'select count(prime_employees_id) as count from cw_employees where cw_employees.trans_status = 1 and cw_employees.termination_status = 1 and cw_employees.employee_code = "'.$emp_code.'"';
$in_act_emp_data = $this->db->query("CALL sp_a_run ('SELECT','$in_act_emp_qry')");
$in_act_emp_rslt = $in_act_emp_data->result();
$in_act_emp_data->next_result();
$count = (int)$in_act_emp_rslt[0]->count;
if($count){
echo json_encode(array('success' => FALSE, 'message' => "Loan should Create for In Active Employee.!"));
exit(0);
}
}
$loan_id_exist_chk = 'select count(prime_loan_id) as prime_loan_count from cw_loan where prime_loan_id="'.$form_id.'" and loan_date = "'.$loan_date.'" and trans_status = 1';
$loan_id_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_id_exist_chk')");
$loan_id_rslt = $loan_id_data->result();
$loan_id_data->next_result();
$loan_id = (int)$loan_id_rslt[0]->prime_loan_count;
//CHECK ALREADY EXIST THE LOAN DATE FOR THE EMPLOYEE
if($form_id === 0 || $loan_id === 0){
$loan_date_chk='select count(prime_loan_id)AS loan_count from cw_loan where employee_code="'.$emp_code.'" and loan_date="'.$loan_date.'" and trans_status=1';
$loan_date_info = $this->db->query("CALL sp_a_run ('SELECT','$loan_date_chk')");
$loan_date_rslt = $loan_date_info->result();
$loan_date_info->next_result();
$loan_count = $loan_date_rslt[0]->loan_count;
if((int)$loan_count > 0){
echo json_encode(array('success' => False, 'message' => "Loan Already exist for this loan date...!"));
exit(0);
}
}
$where_cond = ' and date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y") , "%Y-%m") >= date_format(str_to_date("01-'.$apply_year.'", "%d-%m-%Y"), "%Y-%m")';
$payroll_exit_qry = 'select count(prime_transactions_fms_id) as rslt_count from cw_transactions_fms where trans_status = 1 and employee_code = "'.$emp_code.'" '.$where_cond.'';
$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]->rslt_count;
if((int)$payroll_count > 0){
echo json_encode(array('success' => False, 'message' => "Payroll already processed for this Month or Future Month...!"));
exit(0);
}
$loan_dob_date = date('Y-m-d',strtotime($loan_date));
$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;
if((int)$find_emp_doj === 0){
echo json_encode(array('success' => False, 'message' => "Date of Joining Greater than Loan Date..."));
exit(0);
}
// ------ INACIVE EMPLOYEE CHECK END -----
$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)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$loan_id = $insert_id;
$message = "Successfully updated";
}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 .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')");
$loan_id = $form_id;
$message = "Successfully updated";
}
$install_save = $this->installment_save($loan_id);
if($install_save){
echo json_encode(array('success' => TRUE, 'message' => $message,'insert_id' => $loan_id));
}
}
}
}
//FUNCTION FOR CHECK LOAN EXIST
public function check_loan_closed($emp_code,$loan_type,$form_id){//need to check
$exit_loan_qry = 'select count(*) as rslt_count from cw_loan inner join cw_loan_installment on cw_loan_installment.loan_id = cw_loan.prime_loan_id where cw_loan.trans_status = 1 and cw_loan_installment.trans_status = 1 and cw_loan.employee_code ="'.$emp_code.'" and cw_loan_installment.loan_type="'.$loan_type.'" and (recovery_amount > 0 or paid_status in (1,3))';
$exit_loan_info = $this->db->query("CALL sp_a_run ('SELECT','$exit_loan_qry')");
$exit_loan_result = $exit_loan_info->result();
$exit_loan_info->next_result();
$exit_loan_count = $exit_loan_result[0]->rslt_count;
$close_date = date("Y-m-d");
if((int)$exit_loan_count >= 1){
return false;
}else{
return true;
}
}
//MRJ START 04-FEB-2019
/* ==============================================================*/
/* =================== STATIC LOAN OPERATION - START ============*/
/* ==============================================================*/
//LOAD LOAN VIEW WITH DATA
public function installment_save($loan_id){
$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->employee_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,employee_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.'")';
$install_info = $this->db->query("CALL sp_a_run ('INSERT','$install_query')");
$install_info->next_result();
}
}
}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.'"';
$this->db->query("CALL sp_a_run ('RUN','$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->employee_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,employee_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.'")';
$install_info = $this->db->query("CALL sp_a_run ('INSERT','$install_query')");
$install_info->next_result();
}
}
}
}
return true;
}
//LOAN EDIT DATA UPDATE
// 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);
// }
// $total_paid = $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' => "Installment Amount Should not Greater than Loan Pending Amount.!"));
// exit(0);
// }
// if($per_month > $total_pending_amt){
// //DOUBT(GET TOTAL PENDING AMOUNT OR INSTALL AMOUNT)
// $balance = $per_month - $install_amount;
// $this->insert_installment($loan_id,$install_amount,'0',$balance,$install_year,$paid,$installment_count);
// }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){
// //echo "BSK $per_month < $install_amount :: $installment_count"; die;
// 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;
// //echo "BSK $count :: $balance_amt "; die;
// 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,$paid,$installment_count);
// }
// }
// 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 INSTALLMENT INSERT PROCESS
public function insert_installment($loan_id,$install_amount,$loop,$balance,$install_year,$paid,$installment_count){
$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]->employee_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;
$remaining_balance = $total_amount - ($paid+$install_amount);
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;
}
if($remaining_balance < $per_month){
$per_month = $remaining_balance;
}
$next_date = date("m-Y", strtotime("+$installment_count month", strtotime($apply_date)));
if($per_month > 0){
$install_query = 'insert into cw_loan_installment (loan_id,category,employee_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();
}
$remaining_balance = $remaining_balance - $per_month;
}
}else{
if((int)$balance !== 0){
$per_month = $balance;
}
$installment_count = (int)$number_of_installment;
$next_date = date("m-Y", strtotime("+$installment_count month", strtotime($apply_date)));
$install_query = 'insert into cw_loan_installment (loan_id,category,employee_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;
}
public function save_installment(){
$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);
}
$installment_count = $this->input->post('installment_count');
$install_year = $this->input->post('install_year');
$install_amt = $this->input->post('install_amt');
$install_id = $this->input->post('install_id');
$loan_id = $this->input->post('loan_id');
$category = $this->input->post('category');
$emp_code = $this->input->post('emp_code');
$loan_type = $this->input->post('loan_type');
$loan_date = $this->input->post('loan_date');
$apply_year = $this->input->post('apply_year');
$loan_amount = $this->input->post('loan_amount');
$total_amount = $this->input->post('total_amount');
$tot_installment = $this->input->post('tot_installment');
$interest_rate = $this->input->post('interest_rate');
$logged_id = $this->logged_id;
$date = date("Y-m-d H:i:s");
$sum_install_amt = array_sum($install_amt ?? []);
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 = $install_id[$i];
$month_year = $install_year[$i];
$amount = $install_amt[$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 .'';
$update_info = $this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
}else
if($id === "0"){
$ins_qry ='insert into cw_loan_installment (loan_id,category,employee_code,loan_type,loan_date,apply_year,loan_amount,interest_rate,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.'","'.$total_amount.'","'.$inst_count.'","'.$month_year.'","'.$amount.'","'.$logged_id.'","'.$date.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_qry')");
$ins_result = $insert_info->result();
$insert_info->next_result();
}
}
if($ins_qry || $update_query){
echo json_encode(array('success' => true, 'message' => "Installment is Updated...!"));
}
}
//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");
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 .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$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){
$bulk_arr = array();
$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();
$data['install_result'] = $install_result;
$employee_code = $install_result[0]->employee_code;
$next_transactions = '';
$max_installment_data = 'select max(date_format(str_to_date(CONCAT("01-", install_year), "%d-%m-%Y") , "%Y-%m")) as max_install_year from cw_loan_installment where trans_status = 1 and paid_status not in (0) and employee_code = "'.$employee_code.'" and loan_id = "'.$loan_id.'"';
// echo $max_installment_data; die;
$max_install_data = $this->db->query("CALL sp_a_run ('SELECT','$max_installment_data')");
$max_install_result = $max_install_data->result();
$max_install_data->next_result();
$max_install_year = $max_install_result[0]->max_install_year;
$data['max_install_year'] = $max_install_year;
// echo $max_install_year; die;
//current payroll month
$last_trans_month_qry = 'select max(date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y") , "%Y-%m")) as transactions_month from cw_transactions_fms where trans_status = 1 and employee_code = "'.$employee_code.'"';
$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;
$data['last_pay_process_month'] = $transactions_month;
//PAYROLL NOT PROCESSED
if(!$transactions_month){
$get_loan_mon_qry = 'select apply_year from cw_loan where trans_status = 1 and prime_loan_id ="'.$loan_id.'" and foreclose_sts = 0';
$get_loan_mon_info = $this->db->query("CALL sp_a_run ('SELECT','$get_loan_mon_qry')");
$get_loan_mon_rslt = $get_loan_mon_info->result();
$get_loan_mon_info->next_result();
if($get_loan_mon_rslt[0]){
$apply_year = $get_loan_mon_rslt[0]->apply_year;
$transactions_month = date('Y-m-d',strtotime("01-".$apply_year));
$next_transactions = date('m-Y', strtotime($transactions_month));
}
}else{
$next_transactions = date('m-Y', strtotime('+1 month', strtotime($transactions_month)));
}
$data['payroll_month'] = $next_transactions;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/installment",$data);
return $bulk_arr;
}
//LOAD FORECLOSE LOAN VIEW WITH DATA
public function loan_data($loan_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;
//LOAN FORECLOSE TABLE DETAILS
$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;
$loan_date = $foreclose_details_data_rslt[0]->loan_date;
$data['foreclose_sts'] = $foreclose_sts;
$data['loan_date'] = $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' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$prime_loan_id = (int)$this->input->post('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');
$loan_foreclose_remark = $this->input->post('loan_foreclose_remark');
$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.'",loan_foreclose_remark = "'.$loan_foreclose_remark.'",foreclose_sts = "1", trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$updated_on.'" WHERE prime_loan_id = "'. $prime_loan_id.'"';
$update_res = $this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
if($update_res){
$upd_query = 'UPDATE cw_loan_installment SET paid_status = 3,loan_foreclose_remark = "'.$loan_foreclose_remark.'" where loan_id = "'. $prime_loan_id.'" and trans_status = 1 and cw_loan_installment.paid_status in (0,3)';
$upd_result = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}
if($upd_result){
$msg = "Successfully Your Loan is Closed.!";
//LOAN FORECLOSE TABLE DETAILS
$foreclose_list = $this->foreclose_loan_detail($prime_loan_id);
echo json_encode(array('success' => TRUE, 'msg' => $msg,'foreclose_list' => $foreclose_list));
}
}else{
$msg = "Foreclose Amount should not Zero.!";
echo json_encode(array('success' => FALSE, 'msg' => $msg,'foreclose_list' => ""));
}
}
//FORECLOSE TABLE DATA CREATE
public function foreclose_loan_detail($prime_loan_id){
$foreclose_loan_list = 'select prime_loan_id,loan_foreclose_date, loan_foreclose_amount,foreclose_sts,loan_foreclose_remark 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;
$foreclose_remark = $rslt->loan_foreclose_remark;
$prime_loan_id = $rslt->prime_loan_id;
$tr_line .= "<tr>
<td style='text-align:center;'>$loan_foreclose_date</td>
<td style='text-align:center;'>$loan_foreclose_amount</td>
<td style='text-align:center;'>$foreclose_remark</td>
<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>";
}
if((int)$foreclose_sts > 0){
$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>
<th style='text-align:center;'>Foreclose Remark</th>
<th style='text-align:center;'>Delete</th>
</tr>
$tr_line
</table>";
}
return $foreclose_content;
}
//REOPEN BY A FORECLOSE ENTRY
public function remove_foreclose(){
$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);
}
$prime_loan_id = (int)$this->input->post('loan_id');
$updated_on = date("Y-m-d H:i:s");
if($prime_loan_id){
$loan_upd_qry = 'UPDATE cw_loan SET loan_foreclose_date = NULL,loan_foreclose_amount = NULL,loan_foreclose_remark = NULL,foreclose_sts = "0", trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$updated_on.'" WHERE prime_loan_id = "'. $prime_loan_id.'"';
$loan_upd_data = $this->db->query("CALL sp_a_run ('UPDATE','$loan_upd_qry')");
if($loan_upd_data){
$loan_install_upd_qry = 'UPDATE cw_loan_installment SET paid_status = 0,loan_foreclose_remark = NULL where loan_id = "'. $prime_loan_id.'" and cw_loan_installment.trans_status = 1 and cw_loan_installment.paid_status = 3';
$loan_install_upd_data = $this->db->query("CALL sp_a_run ('UPDATE','$loan_install_upd_qry')");
echo json_encode(array('success' => TRUE, 'msg' => "Your Loan is Reopened.!"));
}
}else{
echo json_encode(array('success' => FALSE, 'msg' => "No Data Available to Delete.!"));
}
}
public function get_mapped_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);
}
$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(){
$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('employee_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 the 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;
$payroll_exit_qry = 'select count(prime_transactions_fms_id) as payroll_rslt from cw_transactions_fms where trans_status = 1 and date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y") , "%Y-%m") >= date_format(str_to_date("01-'.$loan_date_month.'", "%d-%m-%Y"), "%Y-%m") and employee_code = "'.$emp_code.'"';
$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' => "Your payroll has already been processed.!"));
}else
if((int)$find_emp_doj === 0){
echo json_encode(array('success' => False, 'msg' => "Date of Joining Greater than Loan Date..."));
}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' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$delete_ids = implode(",",$this->input->post('delete_ids') ?? []);
$can_process = TRUE;
$delete_status = FALSE;
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 .')';
if($this->db->query("CALL sp_a_run ('RUN','$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.'';
$this->db->query("CALL sp_a_run ('RUN','$delete_row_set_query')");
}
$can_process = False;
}
}
}
if($can_process){
$created_on = date("Y-m-d h:i:s");
$recover_amt_qry = 'SELECT COUNT(prime_loan_installment_id) as count from cw_loan_installment where cw_loan_installment.loan_id in ('. $delete_ids .') and cw_loan_installment.recovery_amount != "0" and trans_status = 1 GROUP BY cw_loan_installment.loan_id';
$recover_amt_info = $this->db->query("CALL sp_a_run ('SELECT','$recover_amt_qry')");
$recover_amt_rslt = $recover_amt_info->result();
$recover_amt_info->next_result();
$recove_amt_count = (int)$recover_amt_rslt[0]->count;
if($recove_amt_count){
echo json_encode(array('success' => FALSE, 'message' => "Recovery Amount Zero Entry Only Allowed to Delete.!"));
exit(0);
}else{
//DELETE QUERY PROCESS
$prime_upd_query = 'trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$created_on.'"';
$prime_update_query = 'UPDATE '. $this->prime_table .' SET trans_status = 0,'. $prime_upd_query .' WHERE '. $this->prime_id .' in ('. $delete_ids .')';
if($this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')")){
//LOAN INSTALLMENT DELETE QRY
$loan_install_del_qry = 'UPDATE cw_loan_installment SET trans_status = 0,'. $prime_upd_query .' WHERE cw_loan_installment.loan_id in ('. $delete_ids .')';
if($this->db->query("CALL sp_a_run ('UPDATE','$loan_install_del_qry')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete"));
}
}
}else
if($delete_status){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
$modules = ucwords($check_table_rlst->prime_module_id);
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;
}
}
//function for delete installment (if delete amount will add into before month)
public function delete_installment(){
$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);
}
$loan_amount = $this->input->post('installment_id');
$install_amount = $this->input->post('install_amount');
$install_month = $this->input->post('install_month');
$loan_id = $this->input->post('loan_id');
$emp_code = $this->input->post('emp_code');
$month = '01-' . $install_month;
$previous_date = DateTime::createFromFormat('d-m-Y', $month);
$previous_date->modify('last day of previous month');
$previous_month = $previous_date->format('m-Y');
//getting previous month data. ->NB[31-10-23]
$prev_month = 'select * from cw_loan_installment where install_year="'.$previous_month.'" and loan_id="'.$loan_id.'" and paid_status = 1 and trans_status = 1';
$prev_info = $this->db->query("CALL sp_a_run ('SELECT','$prev_month')");
$prev_rslt = $prev_info->result();
$prev_info->next_result();
$prev_bal_amt = (int)$prev_rslt[0]->balance_amount;
//getting current month data.
$curr_month = 'select * from cw_loan_installment where install_year = "'.$install_month.'" and loan_id="'.$loan_id.'" and trans_status = 1';
$curr_info = $this->db->query("CALL sp_a_run ('SELECT','$curr_month')");
$curr_rslt = $curr_info->result();
$curr_info->next_result();
$curr_paid_status = (int)$curr_rslt[0]->paid_status;
$curr_prev_pend = (int)$curr_rslt[0]->prev_pending;
$curr_inst_amt = (int)$curr_rslt[0]->install_amount;
$curr_reco_amt = (int)$curr_rslt[0]->recovery_amount;
//getting current & future month data.
// $next_month = 'select * from cw_loan_installment where install_year >= "'.$install_month.'" and loan_id="'.$loan_id.'" and paid_status in (1,2,5) and trans_status = 1';
$next_info = $this->db->query("CALL sp_a_run ('SELECT','select * from cw_loan_installment where install_year >= date_format(str_to_date(concat(\"01-\",$install_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") and loan_id=$loan_id and paid_status in (1,2,5) and trans_status = 1')");
$next_rslt = $next_info->result();
$next_info->next_result();
//getting single row
$installment_exist_qry = 'select * from cw_loan_installment where loan_id="'.$loan_id.'" and trans_status = 1';
$install_info = $this->db->query("CALL sp_a_run ('SELECT','$installment_exist_qry')");
$install_rlst = $install_info->result();
$install_info->next_result();
$install_count = (int)count($install_rlst ?? []);
//If there is no previous installment(not allow to delete)
$prev_install_qry = 'select * from cw_loan_installment where install_year="'.$previous_month.'" and loan_id="'.$loan_id.'"';
$install_info = $this->db->query("CALL sp_a_run ('SELECT','$prev_install_qry')");
$install_rlst = $install_info->result();
$install_info->next_result();
$prev_install_count = (int)count($install_rlst ?? []);
$prev_paid_sts = (int)$install_rlst[0]->paid_status;
//Both rslt are empty then allow to delete.
if(empty($prev_rslt) && empty($next_rslt)){
# update carry forward to not paid(if previous month installment not available and current month carry forward
if($prev_install_count === 0){
if($paid_status === 4){
$sts_upd_qry = 'update cw_loan_installment set paid_status = 0 where loan_id="'.$loan_id.'" and install_year ="'.$install_month.'"';
$sts_upd_data = $this->db->query("CALL sp_a_run ('UPDATE','$sts_upd_qry')");
$rslt_data = $this->installment_details($loan_id);
}
echo json_encode(array('success' => FALSE, 'message' => "Previous month Installment Not available you can't delete this record ...!",'all_data'=>$rslt_data));
}else{
#updating paid status.
$paid_upd_qry = 'UPDATE cw_loan_installment SET paid_status = CASE WHEN paid_status = 4 THEN 0 WHEN paid_status = 5 THEN 2 WHEN paid_status = 2 THEN 2 ELSE paid_status END WHERE loan_id = "'.$loan_id.'" AND install_year = "'.$previous_month.'"';
$paid_upd_data = $this->db->query("CALL sp_a_run ('UPDATE','$paid_upd_qry')");
#updating install amount.
$upd_install_amt = 'UPDATE cw_loan_installment SET install_amount = install_amount+'.$install_amount.' WHERE loan_id= "'.$loan_id.'" and install_year = "'.$previous_month.'"';
$upd_inst_amt_data = $this->db->query("CALL sp_a_run ('UPDATE','$upd_install_amt')");
#current month prev pending + previous month balance amount should not be equal.
$prev_pending = $curr_inst_amt - $curr_prev_pend;
if($curr_prev_pend !== $prev_bal_amt){
$upd_pre_qry = 'UPDATE cw_loan_installment SET prev_pending = "'.$prev_pending.'" WHERE loan_id = "'.$loan_id.'" AND install_year = "'.$previous_month.'"';
$upd_pre_data = $this->db->query("CALL sp_a_run ('UPDATE','$upd_pre_qry')");
}
//Delete Installment.
$delete_install_qry = 'UPDATE cw_loan_installment SET trans_status = 0 WHERE loan_id="'.$loan_id.'" and install_year = "'.$install_month.'"';
$install_upd_data = $this->db->query("CALL sp_a_run ('UPDATE','$delete_install_qry')");
$rslt_data = $this->installment_details($loan_id); //for table
echo json_encode(array('success' => true,'message'=>"Successfully Deleted...!",'all_data'=>$rslt_data));
//single row previous pending updation.
if($install_count === 0){
$upd_qry = 'UPDATE cw_loan_installment SET prev_pending = 0 WHERE loan_id = "'.$loan_id.'" ';
$upd_data = $this->db->query("CALL sp_a_run ('UPDATE','$upd_qry')");
}
//final updation.
$bal_amt = $curr_inst_amt + $curr_prev_pend - $curr_reco_amt;
$final_upd = 'UPDATE cw_loan_installment SET balance_amount = "'.$bal_amt.'" WHERE loan_id = "'.$loan_id.'" AND install_year = "'.$previous_month.'"';
$final_upd_data = $this->db->query("CALL sp_a_run ('UPDATE','$final_upd')");
}
}else{
echo json_encode(array('success' => false, 'message' => "Cannot delete installment. Payment has been done by employee either fully paid, partially paid, or partially paid with carry-forward.!"));
}
}
public function installment_details($loan_id){
$bulk_arr = array();
$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();
$employee_code = $install_result[0]->employee_code;
$next_transactions = '';
//current payroll month
// $last_trans_month_qry = 'select max(date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y") , "%Y-%m")) as transactions_month from cw_transactions_fms where trans_status = 1 and employee_code = "'.$employee_code.'"';
// $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();
// $last_pay_process_month = $last_trans_month_result[0]->transactions_month;
$max_installment_data = 'select max(date_format(str_to_date(CONCAT("01-", install_year), "%d-%m-%Y") , "%Y-%m")) as max_install_year from cw_loan_installment where trans_status = 1 and paid_status not in (0) and employee_code = "'.$employee_code.'" and loan_id = "'.$loan_id.'"';
// echo $max_installment_data; die;
$max_install_data = $this->db->query("CALL sp_a_run ('SELECT','$max_installment_data')");
$max_install_result = $max_install_data->result();
$max_install_data->next_result();
$max_install_year = $max_install_result[0]->max_install_year;
$data['max_install_year'] = $max_install_year;
$tr_line = "";
$i = 1;
$check_sts_arr = array(1 => "1",3 => "3");
$result_count = count($install_result ?? []);
foreach($install_result as $loan){
$installment_id = $loan->prime_loan_installment_id;
$loan_id = $loan->loan_id;
$category = $loan->category;
$emp_code = $loan->employee_code;
$loan_type = $loan->loan_type;
$loan_date = $loan->loan_date;
$apply_year = $loan->apply_year;
$loan_amount = $loan->loan_amount;
$interest_rate = $loan->interest_rate;
$number_of_installment = $loan->number_of_installment;
$per_month = $loan->per_month;
$total_amount = $loan->total_amount;
$installment_count = $loan->installment_count;
$install_year = $loan->install_year;
$install_month = $loan->install_month;
$install_amount = $loan->install_amount;
$recovery_amount = $loan->recovery_amount;
$balance_amount = $loan->balance_amount;
$prev_pending = $loan->prev_pending;
$paid_status = (int)$loan->paid_status;
$foreclose_remark = $loan->loan_foreclose_remark;
$read = '';
$delete_btn = "";
$tot_installment += $install_amount;
$format_loan_date = date("d-m-Y", strtotime($loan_date));
$view_name_query = 'SELECT view_name FROM cw_form_setting WHERE label_name="'.$loan_type.'" AND prime_module_id="employees" AND trans_status="1"';
// echo $view_name_query; die;
$view_name_info = $this->db->query("CALL sp_a_run ('SELECT','$view_name_query')");
$view_name_result = $view_name_info->result();
$view_name_info->next_result();
$loan_type_view_name =$view_name_result[0]->view_name;
//GET PAID STATUS
// echo $last_pay_process_month; die;
$delete_btn = "<td style = 'text-align:center;' class='delete_btn_td'></td>";
if($max_install_year){
$installment_year = date('Y-m',strtotime("01-$install_year"));
if($installment_year <= $max_install_year){ //IF LAST PAYROLL MONTH BEFORE RECORD ALL SHOULD BE READONLY
$read = 'readonly';
}
// else
if($result_count === $i){ //(NOT PAID,CARRY FORWARD AND LAST DATA)ENABLE DELETE BUTTON
$delete_btn = "<td style='text-align:center;' class='delete_btn_td'><button Onclick=delete_install('$installment_id','$install_amount','$install_year','$loan_id','$emp_code') class='btn btn-xs btn-danger' id='delete_btn'><i class='fa fa-trash' aria-hidden='true'></i>Delete</button></td>";
}
// else{
// $delete_btn ="<td style = 'text-align:center;'></td>";
// }
if($installment_year !== $max_install_year && $result_count === $i && $paid_status === 0){
$read = "";
}
}else{
if($result_count === $i){
$read = "";
if(($paid_status === 0 || $paid_status === 4) && $result_count === $i){
$delete_btn = "<td style='text-align:center;' class='delete_btn_td'><button Onclick=delete_install('$installment_id','$install_amount','$install_year','$loan_id','$emp_code') class='btn btn-xs btn-danger' id='delete_btn'><i class='fa fa-trash' aria-hidden='true'></i>Delete</button></td>";
}else{
$delete_btn ="<td style = 'text-align:center;'></td>";
}
}
}
if($paid_status === 0){
$paid_status = 'Not Paid';
}else
if($paid_status === 1){
$paid_status = 'Paid';
$read = 'readonly';
}else
if($paid_status === 2){
$read = 'readonly';
$paid_status = 'Partially Paid';
}else
if($paid_status === 3){
$paid_status = 'Foreclosed';
}else
if($paid_status === 4){
$paid_status = "Carry Forward";
}else
if($paid_status === 5){
$paid_status = "Partially paid & Carry Forward";
}
//TABLE DATA CREATION
$value_id = 'install_amount_'.$installment_id;
$tr_line .= "<tr id='install_row_$installment_id'><input type='hidden' name='hid_install_id[]' value='$installment_id'>
$delete_btn
<td style = 'text-align:center;'>$installment_count<input type='hidden' name='hid_installment[]' value='$installment_count'></td>
<td style = 'text-align:center;'>$install_year <input type='hidden' name='hid_install_year[]' value='$install_year'></td>
<td><input type='text' name='install_amount[]' class='numbersOnly form-control' id='$value_id' value='$install_amount' $read '/></td>
<td style = 'text-align:center;'>$recovery_amount</td>
<td style = 'text-align:center;'>$balance_amount</td>
<!--<td style = 'text-align:center;'>$prev_pending</td> -->
<td style = 'text-align:center;'>$paid_status</td>
<td style = 'text-align:center;'>$foreclose_remark</td>";
$i++;
}
$last_install_year = date('Y-m-d',strtotime('01-'.$install_year));
$next_installment = date('m-Y',strtotime('+1 month',strtotime($last_install_year)));
$tr_line .= "<td style='text-align:center;'><button Onclick=add_new_row($installment_count,$installment_id,'$next_installment','$total_amount') class='btn btn-xs btn-info'><i class='fa fa-plus' aria-hidden='true'></i>Add New</button></td></tr>";
$rslt_data = "<div style='padding:10px;' id='loan_modal'>
<div style='background-color: #FFFFFF !important; 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); border-radius: 3px; margin: 8px; padding: 8px;overflow: auto;'>
<table style='width:100%;'>
<tr>
<td style='white-space:nowrap;padding-right:10px;font-weight:bold;text-align: center;'>
<span style='color:#00b0eb !important;font-size:12px;'>Employee Code</span><br/>
$emp_code
</td>
<td style='white-space:nowrap;padding-right:10px;font-weight:bold;text-align: center;'>
<span style='color:#00b0eb !important;font-size:12px;'>Loan Type</span><br/>
$loan_type_view_name
</td>
<td style='white-space:nowrap;padding-right:10px;font-weight:bold;text-align: center;'>
<span style='color:#00b0eb !important;font-size:12px;'>Loan Date</span><br/>
$format_loan_date
</td>
<td style='white-space:nowrap;padding-right:10px;font-weight:bold;text-align: center;'>
<span style='color:#00b0eb !important;font-size:12px;'>Apply Month Year</span><br/>
$apply_year
</td>
<td style='white-space:nowrap;padding-right:10px;font-weight:bold;text-align: center;'>
<span style='color:#00b0eb !important;font-size:12px;'>Loan Amount</span><br/>
$loan_amount
</td>
<td style='white-space:nowrap;padding-right:10px;font-weight:bold;text-align: center;'>
<span style='color:#00b0eb !important;font-size:12px;'>Total Amount</span><br/>
$total_amount
</td>
</tr>
</table>
</div>
<div id='installment' style='max-height:450px !important;overflow:auto !important;'>
<table id='installment_data' class='table table-striped table-hover' style='width:100% !important;'>
<thead>
<tr>
<th style = 'text-align:center;'></th>
<th style = 'text-align:center;'>Installment</th>
<th style = 'text-align:center;'>Installment Month and Year</th>
<th style = 'text-align:center;'>Monthly Amount Deduct</th>
<th style = 'text-align:center;'>Recovery Amount</th>
<th style = 'text-align:center;'>Balance Amount</th>
<!--<th style = 'text-align:center;'>Prevoius Pending</th> -->
<th style = 'text-align:center;'>Paid Status</th>
<th style = 'text-align:center;'>Foreclose Remark</th>
<th style = 'text-align:center;'>Options</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody >
<tfoot><tr><td></td><td style='font-weight:bold; font-size:15px;'></td><td><h5 style='font-weight:bold; text-align:center;'>Total</h5></td><td><h5 id='footer' style='font-weight:bold;'>$tot_installment</h5></td><td></td><td></td><td></td><td></td><td><button id='installment_submit' OnClick =save_data('$loan_id','$category','$emp_code','$loan_type','$loan_date','$apply_year','$loan_amount','$total_amount','$tot_installment','$interest_rate') class='btn btn-sm btn-primary'>Submit</button><button type='button' class='btn btn-sm btn-primary' style='margin-left:10px;' data-dismiss='modal' aria-label='Close' onclick='close_formfun()'><span aria-hidden='true'>Close</span></button></td>
</tr></tfoot>
</table>
<div></div>
</div>
</div>";
return $rslt_data;
}
//UPDATE STATUS TO DELETE FOR UPLOAD FILES or DOCUMENTS
public function remove_file(){
$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['module_id'] = $this->control_name;
$data['encKey'] = $this->generateKey();
$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($module_id,$excel_format){
$excel_format_qry = 'select excel_name,view_name,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on cw_form_setting.label_name = excel_line_column_name 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_form_setting.prime_module_id = "'.$module_id.'" and cw_util_excel_format_line.prime_excel_format_id ="'.$excel_format.'" and cw_util_excel_format_line.trans_status = 1 and cw_util_excel_format.trans_status = 1 GROUP BY cw_form_setting.label_name';
$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);
require_once APPPATH."/third_party/PHPExcel.php";
$obj = new PHPExcel();
//Set the first row as the header row
foreach($excel_result as $excel){
// $excel_line_column_name = $excel->excel_line_column_name;
$excel_line_column_name = $excel->view_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.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
//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, 'Excel5');
//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' => $excelOutput));
}
//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(employee_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.employee_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
}
?>