File: /home/cafsindia/hrms_patroniss_com/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(){
$data['quick_link'] = $this->quick_link;
$data['table_head'] = $this->table_head;
$data['master_pick'] = $this->master_pick;
$data['fliter_list'] = $this->fliter_list;
$this->load->view("$this->control_name/manage",$data);
}
//LOAD TABEL WITH FILTERS
public function search(){
$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);
//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_id'];
$label_name = $fliter['label_name'];
$field_isdefault = (int)$fliter['field_isdefault'];
$array_list = $fliter['array_list'];
$field_type = (int)$fliter['field_type'];
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 === 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->form_info 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->master_pick[$label_id], 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);
$search_total = $this->db->query($count_all_query);
$search_total_info = $search_total->result();
$total_count = $search_total_info[0]->allcount;
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.trans_status = 1 $role_condition $fliter_query $common_search";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$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_data = $this->db->query($search_query);
$search_result = $search_data->result();
//echo "search_query :: \n$search_query\n";
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){
//VIEW, FORM INPUT
$data['view_info'] = $this->view_info;
$data['form_info'] = $this->form_info;
//VIEW DATA
$base_query = str_replace("@SELECT@",$this->view_select,$this->base_query);
$view_query = $base_query ." where $this->prime_table.$this->prime_id = $form_view_id and $this->prime_table.trans_status = 1";
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_query')");
$view_result = $view_data->result();
$view_data->next_result();
$data['form_view'] = $view_result[0];
//AUTO COMPLTE,PICK LIST AND CONDITION
foreach($this->form_info as $from){
$prime_form_id = (int)$from->prime_form_id;
$field_type = (int)$from->field_type;
$pick_table = $from->pick_table;
$auto_prime_id = $from->auto_prime_id;
$auto_dispaly_value = $from->auto_dispaly_value;
$label_id = $from->label_name;
if($field_type === 9){
if($view_result[0]){
$get_value = $view_result[0]->$label_id;
if($get_value){
$pick_query = 'select '.$auto_dispaly_value.' from '.$pick_table.' where '.$auto_prime_id.' = "'.$get_value.'" and trans_status = 1';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$this->all_pick[$prime_form_id] = $pick_result[0]->$auto_dispaly_value;
}
}
}
}
$data['all_pick'] = $this->all_pick;
$data['condition_list'] = $this->condition_list;
$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_data = $this->db->query("CALL sp_a_run ('SELECT','$view_qry')");
$view_result = $view_data->result();
$view_data->next_result();
$row_view_list = array();
foreach($view_result 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;
$this->load->view("$this->control_name/form",$data);
}
//SAVE MODEL DATA TO DATA BASE
public function save(){
$unq_chk = array();
$prime_qry_key = "";
$prime_qry_value = "";
$prime_upd_query = "";
$cf_qry_key = "";
$cf_qry_value = "";
$cf_upd_query = "";
$cf_has = false;
$form_id = (int)$this->input->post($this->prime_id);
$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;
$emp_code = $this->input->post('emp_code');
$loan_amount = $this->input->post('loan_amount');
$loan_type = $this->input->post('loan_type');
$loan_date = date('Y-m-d',strtotime($this->input->post('loan_date')));
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){
$value = date('Y-m-d',strtotime($value));
}else
if((int)$field_type === 13){
$value = date('Y-m-d H:i:s',strtotime($value));
}
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{
$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 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 .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$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);
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){
$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.'")';
$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->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.'")';
$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);
}
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!!!"));
}
}
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");
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){
$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;
//current payroll month
$last_trans_month_qry = 'select transactions_month from cw_transactions where 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['payroll_month'] = $next_transactions;
$this->load->view("$this->control_name/installment",$data);
}
//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;
$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;
$this->load->view("$this->control_name/loan_data",$data);
}
//LOAD FORECLOSE DATE AND AMOUNT SAVE
public function save_foreclose_loan(){
$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');
$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.'"';
$update_res = $this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
if($update_res){
$upd_query = 'UPDATE cw_loan_installment SET paid_status = 1 where loan_id = "'. $prime_loan_id.'" and trans_status = 1';
$upd_result = $this->db->query("CALL sp_a_run ('UPDATE','$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, 'msg' => $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;
$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;'><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;'>Delete</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");
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.'"';
$this->db->query("CALL sp_a_run ('UPDATE','$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(){
$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('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(){
$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;
$select_table = '';
$select_label = '';
$select_trans_status = '';
$select_where = '';
foreach($prime_module_id as $check_modules){
$table_name = "cw_".$check_modules;
$table_rename = $table_name."_$i";
$select_table .= "$table_rename.$label_name[$i],";
$select_label .= " $table_name $table_rename,";
if((int)$i === 0){
$select_trans_status .= "( $table_rename.trans_status = 1";
$select_where .= " and ($table_rename.$label_name[$i] in ($delete_ids)";
}else{
$select_trans_status .= " and $table_rename.trans_status = 1";
$select_where .= " or $table_rename.$label_name[$i] in ($delete_ids)";
}
$i++;
}
$select_trans_status .= ")";
$select_where .= ")";
$select_table = rtrim($select_table,',');
$select_label = rtrim($select_label,',');
$check_module_query .= 'SELECT '.$select_table.' from '.$select_label.' WHERE '.$select_trans_status.' '.$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){
$can_process = False;
$delete_status = False;
}
}
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");
$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')")){
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 $modules 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(){
$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;
$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 rowset_remove_file(){
$table_name = $this->input->post('table_name_set');
$row_id = (int)$this->input->post('row_id');
$input_name = $this->input->post('input_name');
$view_id = (int)$this->input->post('view_id');
$primes_id = (int)$this->input->post('primes_id');
$remove_cw = str_replace("cw_","",$table_name);
$primary_id = "prime_".$remove_cw."_id";
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 '.$primary_id.' = "'. $row_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
$row_set_edit = $this->get_row_set_data($view_id,$primes_id);
$div_id = $row_set_edit['div_id'];
$table_id = $row_set_edit['table_id'];
$row_set_view = $row_set_edit['row_set_view'];
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'div_id' => $div_id, 'table_id' => $table_id,'row_set_view'=>$row_set_view));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
public function excel($module_id,$excel_format){
$excel_format_qry = 'select excel_line_column_name,excel_line_value from cw_util_excel_format_line where excel_line_module_id = "'.$module_id.'" and prime_excel_format_id ="'.$excel_format.'" 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();
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_value = $excel->excel_line_value;
$obj->getActiveSheet()->setCellValue($excel_line_value."1", $excel_line_column_name);
}
// Rename worksheet name
$filename= $module_id.".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 = PHPExcel_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(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
}
?>