File: /home/cafsindia/hrms_cafsinfotech_in/OLD/application/controllers/Lop_credit.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Lop_credit extends Action_controller{
public function __construct(){
parent::__construct('lop_credit');
$this->column_mapping();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//category list
$cat_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id!=1')");
$cat_result = $cat_info->result();
$cat_info->next_result();
$category_list[""] = "---- Select Category ----";
foreach($cat_result as $cat_for){
$role_id = $cat_for->prime_category_id;
$category_name = $cat_for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
// AUTOCOMPLETE FOR SERACH EMPLOYEE
public function emp_suggest(){
$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);
}
$term = $this->input->post('term');
$category = $this->input->post('category');
$apply_month = $this->input->post('apply_month');
$firstDay = new DateTime("01-$apply_month");
$first_date = $firstDay->format('Y-m-d'); // Output: 2024-12-01
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and (employee_code like "%'.$term.'%" or emp_name like "%'.$term.'%") and role = "'.$category.'" and date_of_joining < "'.$first_date.'" and termination_status = 0';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
public function search_lop_details(){
$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);
}
$category = $this->input->post('category');
$apply_month = $this->input->post('apply_month');
$employee_code = $this->input->post('employee_code');
//Check column mapped or not
$lop_days_column = $this->get_map_arr['lop_days'];
if(!$lop_days_column){
echo json_encode(array('success' => False, 'msg' => "Lop days not Mapped in Module settings. Please map and try aftersometime.."));
exit(0);
}
$exist_trans_qry = 'select employee_code from cw_transactions where transactions_month = "'.$apply_month.'" and employee_code = "'.$employee_code.'" and trans_status = 1';
$exist_trans_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_trans_qry')");
$exist_trans_result = $exist_trans_data->result();
$exist_trans_data->next_result();
if($exist_trans_result){
echo json_encode(array('success' => False, 'msg' => "Payroll Already processed for this month.."));
exit(0);
}
$exist_qry = 'select prime_lop_credit_id from cw_lop_credit where trans_status = 1 and apply_month = "'.$apply_month.'" and employee_code = "'.$employee_code.'"';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$exist_data->next_result();
if($exist_result){
$prime_id = $exist_result[0]->prime_lop_credit_id;
$rslt = $this->get_data($prime_id);
echo json_encode(array('success' => TRUE, 'msg' => "Already Transaction Exist..", 'trans_result' => $rslt));
exit(0);
}
//settings checking
$check_lop_set_qry = 'select back_month from cw_lop_setting where trans_status = 1 and FIND_IN_SET("'.$category.'",category)';
$check_lop_set_data = $this->db->query("CALL sp_a_run ('SELECT','$check_lop_set_qry')");
$check_lop_set_result = $check_lop_set_data->result();
$check_lop_set_data->next_result();
$back_month = $check_lop_set_result[0]->back_month;
if($back_month){ //do work after Leave Modules
$datetime = new DateTime("01-$apply_month");
$datetime->modify("-$back_month month");
$start_date = $datetime->format('Y-m-d'); // Output: 2024-12-01
$end_date = date('Y-m-d',strtotime("01-$apply_month"));
$prev_trans_qry = 'select transactions_month,employee_code,'.$lop_days_column.' as ld from cw_transactions where employee_code = "'.$employee_code.'" and trans_status = 1 and DATE_FORMAT(str_to_date(concat("01-",transactions_month), "%d-%m-%Y") , "%Y-%m-%d") between "'.$start_date.'" AND "'.$end_date.'"';
$prev_trans_data = $this->db->query("CALL sp_a_run ('SELECT','$prev_trans_qry')");
$prev_trans_result = $prev_trans_data->result();
$prev_trans_data->next_result();
if($prev_trans_result){
//Get Prev Lop Credits for this employee
$prev_lop_credit_qry = 'select process_month,employee_code,SUM(credit_days) as prev_credit from cw_lop_credit_trans where employee_code = "'.$employee_code.'" and trans_status = 1 and DATE_FORMAT(str_to_date(concat("01-",process_month), "%d-%m-%Y") , "%Y-%m-%d") between "'.$start_date.'" AND "'.$end_date.'" group by process_month';
$prev_lop_credit_data = $this->db->query("CALL sp_a_run ('SELECT','$prev_lop_credit_qry')");
$prev_lop_credit_result = $prev_lop_credit_data->result();
$prev_lop_credit_data->next_result();
$prev_lop_arr = array();
foreach ($prev_lop_credit_result as $key => $value) {
$prev_lop_arr[$value->process_month] = $value->prev_credit;
}
$created_on = date("Y-m-d H:i:s");
$head_ins_query = 'INSERT INTO cw_lop_credit(category,apply_month,employee_code,back_month,trans_created_by,trans_created_date) values ("'.$category.'","'.$apply_month.'","'.$employee_code.'","'.$back_month.'","'.$this->logged_id.'","'.$created_on.'")';
$head_ins = $this->db->query("CALL sp_a_run ('INSERT','$head_ins_query')");
$head_ins_result = $head_ins->result();
$head_ins->next_result();
$insert_id = $head_ins_result[0]->ins_id;
$emp_data = array();
foreach ($prev_trans_result as $key => $value) {
$lop_days = $value->ld;
$month = $value->transactions_month;
$prev_credit = $prev_lop_arr[$month];
$emp_data[] = "('".$insert_id."','".$employee_code."','".$month."','".$lop_days."','".$prev_credit."','".$this->logged_id."','".$created_on."')";
}
//prime_lop_credit_id
$trail_ins_query = '"INSERT INTO cw_lop_credit_trans(prime_lop_credit_id,employee_code,process_month,ld,prev_credit,trans_created_by,trans_created_date) values '.implode(',',$emp_data).'"';
$trail_info = $this->db->query("CALL sp_a_run ('RUN',$trail_ins_query)");
if($trail_info){
$rslt = $this->get_data($insert_id);
echo json_encode(array('success' => TRUE, 'trans_result' => $rslt));
}else{
echo json_encode(array('success' => False, 'msg' => "Please try after sometime.."));
}
}else{
echo json_encode(array('success' => False, 'msg' => "No data Available.."));
}
}else{
echo json_encode(array('success' => False, 'msg' => "Please set the lop credit settings first?"));
}
}
//Get common get data for trail table
public function get_data($prime_id){
$select_qry = 'select *,ld-prev_credit as cur_bal from cw_lop_credit_trans where prime_lop_credit_id = "'.$prime_id.'" and trans_status = 1';
$select_info = $this->db->query("CALL sp_a_run ('SELECT','$select_qry')");
$select_result = $select_info->result();
$select_info->next_result();
return $select_result;
}
//Update changed data by user
public function update_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);
}
$prime_id = $this->input->post('prime_id');
$cr_ld = $this->input->post('cr_ld');
$bal_ld = $this->input->post('bal_ld');
$prime_update_query = 'UPDATE cw_lop_credit_trans SET credit_days = "'.$cr_ld.'",balance_days = "'.$bal_ld.'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.date("Y-m-d H:i:s").'" WHERE prime_lop_credit_trans_id = "'. $prime_id.'" ';
$upd_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')");
if($upd_rslt){
echo json_encode(array('success' => TRUE, 'msg' => "Updated Successfully..."));
}else{
echo json_encode(array('success' => False, 'msg' => "Please try after sometime.."));
}
}
//Submit the data and process the lop credit amount
public function submit_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'msg' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$prime_id = $this->input->post('prime_id');
$trans_rslt = $this->get_data($prime_id);
foreach ($trans_rslt as $key => $value) {
$employee_code = $value->employee_code;
$process_month = $value->process_month;
$info = $this->db->query("CALL itsp_lop_cr_calc('$employee_code','$process_month')");
$result = $info->result();
$info->next_result();
}
//Select Lop credit columns
$select_qry = 'select lop_component from cw_lop_credit_matching where trans_status = 1';
$select_info = $this->db->query("CALL sp_a_run ('SELECT','$select_qry')");
$select_result = $select_info->result();
$select_info->next_result();
$lop_components = explode(",",$select_result[0]->lop_component);
$upd_qry = "";
$tot_qry = "";
foreach ($lop_components as $key => $column){
$upd_qry .= "cw_lop_credit.$column = agg.total_$column,";
$tot_qry .= "SUM($column) AS total_$column,";
}
$prime_update_query = 'UPDATE cw_lop_credit JOIN ( SELECT '.$tot_qry.' prime_lop_credit_id,sum(credit_days) as total_credit_days FROM cw_lop_credit_trans WHERE prime_lop_credit_id = "'. $prime_id.'" GROUP BY prime_lop_credit_id ) AS agg ON cw_lop_credit.prime_lop_credit_id = agg.prime_lop_credit_id SET '.$upd_qry.' credit_days = total_credit_days,trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.date("Y-m-d H:i:s").'" WHERE cw_lop_credit.prime_lop_credit_id = "'. $prime_id.'"';
$upd_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')");
if($upd_rslt){
echo json_encode(array('success' => TRUE, 'msg' => "Successfully Submitted"));
}
}
//UPDATE STATUS TO DELETE
public function delete_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'msg' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$delete_id = $this->input->post('delete_id');
if($delete_id){
$created_on = date("Y-m-d h:i:s");
$prime_query = 'UPDATE cw_lop_credit SET trans_status = 0,trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$created_on.'" WHERE prime_lop_credit_id = "'. $delete_id.'" ';
if($this->db->query("CALL sp_a_run ('UPDATE','$prime_query')")){
$prime_update_query = 'UPDATE cw_lop_credit_trans SET trans_status = 0,trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$created_on.'" WHERE prime_lop_credit_id = "'. $delete_id.'" ';
if($this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')")){
$rslt = $this->get_data($delete_id);
echo json_encode(array('success' => TRUE, 'msg' => "Successfully Deleted", 'trans_result' => $rslt));
}else{
echo json_encode(array('success' => FALSE, 'msg' => "Unable to delete"));
}
}
}
}
}
?>