File: /home/cafsindia/hrms_allyindian_com/application_bk/controllers/Lop_credit.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
require('./application/libraries/PHPSpreadsheet/autoload.php');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
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' => 'Url Expired.. Please refresh the page and try again....','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.'"';
$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' => 'Url Expired.. Please refresh the page and try again....','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'];
// MONTH DAYS ADDED BY _ARN 03-09-2025
$month_days_column = $this->get_map_arr['month_days'];
$paid_days_column = $this->get_map_arr['paid_days'];
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Employee Code"=>$emp_code ,"Category" => $category,"Apply Month" =>$apply_month ];
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,'.$month_days_column.' as md ,'.$paid_days_column.' as pd 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.'")';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Lop Credit DATA ADD",'');
$this->db->query($head_ins_query);
$insert_id = $this->db->insert_id();
$emp_data = array();
foreach ($prev_trans_result as $key => $value) {
$lop_days = $value->ld;
$month = $value->transactions_month;
$month_days = $value->md;
$paid_days = $value->pd;
$prev_credit = $prev_lop_arr[$month];
$emp_data[] = "('".$insert_id."','".$employee_code."','".$month."','".$lop_days."','".$month_days."','".$paid_days."','".$prev_credit."','".$this->logged_id."','".$created_on."')";
}
$trail_ins_query = "INSERT INTO cw_lop_credit_trans (prime_lop_credit_id, employee_code, process_month, ld, md, pd, prev_credit, trans_created_by, trans_created_date) VALUES " . implode(',', $emp_data);
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Lop Credit Trans DATA ADD",'');
$trail_info = $this->db->query($trail_ins_query);
if($trail_info){
$rslt = $this->get_data($insert_id);
echo json_encode(array('success' => TRUE, 'trans_result' => $rslt,'msg' => "Lop Days Exists"));
}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' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$employee_code = $this->input->post('employee_code');
$filter_keys = ["Employee Code" => $employee_code];
$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.'" ';
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Lop Credit Trans DATA ADD",'user');
$upd_rslt = $this->db->query($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' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$prime_id = $this->input->post('prime_id');
// $trans_rslt = $this->get_data($prime_id);
$select_qry = 'SELECT employee_code,apply_month FROM cw_lop_credit WHERE prime_lop_credit_id="'.$prime_id.'"';
$select_info = $this->db->query("CALL sp_a_run ('SELECT','$select_qry')");
$select_result = $select_info->result();
$select_info->next_result();
$employee_code = $select_result[0]->employee_code;
$apply_month = $select_result[0]->apply_month;
$logged_id = $this->logged_id;
$lop_cr_info = $this->db->query("CALL itsp_lop_cr_calc('$employee_code','$apply_month','$logged_id')");
$result = $lop_cr_info->result();
$lop_cr_info->next_result();
// COMMENTED BY _ARN FOR THE REASON THERE PROCEDURE DO THIS
// if($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,";
// }
// $tot_qry .= "SUM(CASE WHEN md = ld THEN 1 ELSE 0 END) AS total_lop_check,";
// $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.' lop_check = total_lop_check,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($lop_cr_info){
echo json_encode(array('success' => TRUE, 'msg' => "Successfully Submitted"));
}else{
echo json_encode(array('success' => False, 'msg' => "No Data Available.."));
}
}
//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' => 'Url Expired.. Please refresh the page and try again....','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"));
}
}
}
}
// IMPORT FUNCTIONALITY ADDED ON LOP _ARN 03-09-2025
public function import(){
$data['encKey'] = $this->generateKey();
$data['module_id'] = $this->control_name;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
foreach ($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$excel_format_drop = ["" => "---- Excel Format ----","1" => "Lop credit import"];
$data['excel_format_drop'] = $excel_format_drop;
$this->load->view("$this->control_name/import",$data);
}
// LOP CREDIT EXCEL SHEET MAKED MANUAL _ARN 03-09-2025
public function lop_excel($Payload){
// Decrypt payload
$_POST = $this->cryptoDecrypt(base64_decode(urldecode($Payload)));
$module_id = $this->input->post('module_id');
$excel_format = $this->input->post('excel_format');
if(!$_POST){
echo json_encode(array('success' => false,'msg' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
if($module_id === 'lop_credit' && (int)$excel_format === 1){
$excel_name = "Lop_Credit_Import";
$columns = ['A' => 'Employee Code','B' => 'Month & Year','C' => 'Credit Days'];
// Create spreadsheet
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
// Set headers
foreach ($columns as $col => $header) {
$worksheet->getCell($col . '1')->setValue($header);
}
// Output Excel file
$filename = $excel_name . ".xls";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');
exit(0);
}else{
// If condition not met, return a response or handle gracefully
echo json_encode(['success' => false, 'message' => 'Invalid module or excel format']);
exit(0);
}
}
// SAVE THE DATA IMPORT EXCEL
public function save_import(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if (!$_POST) {
echo json_encode(['success' => false, 'message' => 'Url Expired.. Please refresh the page and try again....']);
exit;
}
$excel_file_path = $this->input->post('excel_file_path');
$excel_sheet_name = $this->input->post('excel_sheet_name');
$excel_start_row = (int)$this->input->post('excel_start_row');
$excel_end_row = (int)$this->input->post('excel_end_row');
$apply_month = $this->input->post('apply_month');
$role = $this->input->post('category_id');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
$lop_days_column = $this->get_map_arr['lop_days'] ?? null;
$month_days_column = $this->get_map_arr['month_days'] ?? null;
$paid_days_column = $this->get_map_arr['paid_days'] ?? null;
// FILTER KEY AND ENTITY SET AUDIT LOG _ARN 07-10-2025
$filter_keys = ["Sheet Name"=>$excel_sheet_name ,"Category" => $role,"Apply Month" =>$apply_month ];
if(!$lop_days_column || !$month_days_column || !$paid_days_column){
echo json_encode(['success' => false, 'message' => "Mapping for Lop Days/ Paid Days /Month Days is missing.",'error_info' =>true]);
exit(0);
}
if($excel_start_row <= 1){
echo json_encode(['success' => false,'message' => 'Invalid Start Row: Data cannot start at row 1 (header row). Please select a valid start row.','error_info' =>true]);
exit(0);
}
// Load Excel
try{
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($excel_file_path);
}catch (Exception $e){
echo json_encode(['success' => false, 'message' => "Invalid Excel File"]);
exit(0);
}
$sheet = $excel_obj->getSheet((int)$excel_sheet_name);
$total_rows = $excel_end_row ?: $sheet->getHighestRow();
$category_check = "SELECT employee_code FROM cw_employees WHERE role = '$role' AND trans_status = 1";
$category_data = $this->db->query($category_check);
$category_res = $category_data->result();
$cate_emp_codes = [];
$cate_emp_codes = array_column($category_res, 'employee_code');
// Fetch back_month
$lop_setting_qry = "SELECT back_month FROM cw_lop_setting WHERE trans_status = 1 AND FIND_IN_SET('$role', category)";
$lop_setting = $this->db->query($lop_setting_qry);
$lop_setting_res = $lop_setting->result();
$lop_setting->next_result();
if(!$lop_setting_res){
echo json_encode(['success' => false, 'message' => "LOP Setting Missing"]);
exit(0);
}
$back_month = (int)$lop_setting_res[0]->back_month;
// Date range
$datetime = new DateTime("01-$apply_month");
$datetime->modify("-$back_month month");
$start_date = $datetime->format('Y-m-d');
$end_date = date('Y-m-d', strtotime("01-$apply_month"));
// Prefetch transactions
$trans_qry = "SELECT employee_code, transactions_month, $lop_days_column AS ld, $month_days_column AS md ,$paid_days_column as pd FROM cw_transactions WHERE 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'";
$trans_data = $this->db->query($trans_qry);
$trans_rows = $trans_data->result();
$trans_data->next_result();
$trans_map = [];
foreach($trans_rows as $t){
$trans_map[$t->employee_code][$t->transactions_month] = [
'ld' => (float)$t->ld,
'md' => (int)$t->md,
'pd' => (int)$t->pd
];
}
// Payroll check
$payroll_qry = "SELECT employee_code FROM cw_transactions WHERE transactions_month = '$apply_month' AND trans_status = 1";
$payroll_data = $this->db->query($payroll_qry);
$payroll_rows = $payroll_data->result();
$payroll_data->next_result();
$payroll_set = array_column($payroll_rows, 'employee_code');
$status_array = [];
$lop_check = $this->db->query("CALL sp_a_run('SELECT', \"SELECT employee_code, prime_lop_credit_id FROM cw_lop_credit WHERE apply_month != '$apply_month' AND trans_status = 1\")");
$lop_rows = $lop_check->result();
$lop_check->next_result();
$lop_map = [];
foreach ($lop_rows as $row){
$lop_map[$row->employee_code][] = (int) $row->prime_lop_credit_id;
}
for($row = $excel_start_row; $row <= $total_rows; $row++){
$employee_code = trim((string)$sheet->getCell("A{$row}")->getValue());
$cell = $sheet->getCell("B{$row}");
$cellValue = $cell->getValue();
// Convert to month-year (MM-YYYY)
if(\PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell)){
$dateObj = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($cellValue);
$process_month = $dateObj->format('m-Y');
}else{
$process_month = date('m-Y', strtotime($cellValue));
}
$credit_days = (float)trim($sheet->getCell("C{$row}")->getValue());
if(empty($employee_code) || empty($process_month) || $credit_days === '') {
$status_array[] = ['Row'=>$row,'Status'=>"Excel Cells Empty Please Check" ];
continue;
}
if(!in_array($employee_code,$cate_emp_codes)){
$status_array[] = ['Row'=>$row,'Status'=>"Employee not In this category" ];
continue;
}
if(in_array($employee_code, $payroll_set)){
$status_array[] = ['Row'=>$row,'Status'=>"Payroll Already Processed" ];
continue;
}
$trans = $trans_map[$employee_code][$process_month] ?? null;
if(!$trans){
$status_array[] = ['Row'=>$row,'Status'=>"No Transaction Data"];
continue;
}
$ld = $trans['ld'];
$md = $trans['md'];
$pd = $trans['pd'];
if(!empty($lop_map[$employee_code]) && is_array($lop_map[$employee_code])){
$id_list = implode(',', $lop_map[$employee_code]);
}
$check_qry = '';
if($id_list){
$check_qry = " AND prime_lop_credit_id IN ($id_list)";
}else{
$check_qry = " AND prime_lop_credit_id IN (0)";
}
$credit_qry = "SELECT employee_code, process_month,SUM(credit_days) AS prev_credit FROM cw_lop_credit_trans WHERE trans_status = 1 AND STR_TO_DATE(CONCAT('01-', process_month), '%d-%m-%Y') BETWEEN '$start_date' AND '$end_date' $check_qry GROUP BY employee_code, process_month";
$credit_data = $this->db->query($credit_qry);
$credit_rows = $credit_data->result();
$credit_data->next_result();
$credit_array = [];
foreach ($credit_rows as $rows) {
$credit_array[$rows->employee_code][$rows->process_month] = (float)$rows->prev_credit;
}
$prev_credit = isset($credit_array[$employee_code][$process_month]) ? (float)$credit_array[$employee_code][$process_month] : 0;
$total_days = $prev_credit + $credit_days;
$bal_days = $ld - $total_days;
$err_bal_days = $ld - $prev_credit;
$head_check = $this->db->query("CALL sp_a_run('SELECT', \"SELECT prime_lop_credit_id FROM cw_lop_credit WHERE employee_code='$employee_code' AND apply_month='$apply_month' AND trans_status=1\")");
$head_rows = $head_check->result();
$head_check->next_result();
if(!empty($head_rows)){
$lop_id = $head_rows[0]->prime_lop_credit_id;
}else{
$head_qry = "INSERT INTO cw_lop_credit(category,apply_month,employee_code,back_month,trans_created_by,trans_created_date)VALUES('$role','$apply_month','$employee_code','$back_month','$logged_id','$today_date')";
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Lop Credit DATA ADD",'');
$this->db->query($head_qry);
$lop_id = $this->db->insert_id();
}
if(!empty($lop_id)){
if($lop_id){
$exist_lop_trans_qry = "SELECT COUNT(*) AS cnt FROM cw_lop_credit_trans WHERE prime_lop_credit_id = '$lop_id' AND process_month = '$process_month' AND employee_code = '$employee_code' AND trans_status=1 ";
$exist_lop_data = $this->db->query($exist_lop_trans_qry);
$exist_rows = $exist_lop_data->row(); // get single row as object
$count = $exist_rows->cnt; // access the count
$exist_lop_data->next_result();
if($count > 0){
if($total_days <= $ld){
$upd_q = "UPDATE cw_lop_credit_trans SET credit_days='$credit_days', balance_days='$bal_days', trans_updated_by='$logged_id', trans_updated_date='$today_date'WHERE prime_lop_credit_id='$lop_id' AND process_month = '$process_month'AND trans_status=1";
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Lop Credit Trans DATA UPDATE",'user');
$this->db->query($upd_q);
$status = "Updated to DB";
}else{
$status_array[] = ['Row'=>$row,'Status'=>"Credit Exceeds Allowable ($err_bal_days)"];
continue;
}
}else{
if($ld< $total_days){
$trail_qry = "INSERT INTO cw_lop_credit_trans (prime_lop_credit_id,employee_code,process_month,ld,md,pd,credit_days,prev_credit,balance_days,trans_created_by,trans_created_date)VALUES ('$lop_id','$employee_code','$process_month','$ld','$md','$pd','0','$prev_credit','$err_bal_days','$logged_id','$today_date')";
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Lop Credit Trans DATA ADD",'user');
$this->db->query($trail_qry);
$status_array[] = ['Row'=>$row,'Status'=>"Credit Exceeds Allowable ($err_bal_days)"];
continue;
}else{
$trail_qry = "INSERT INTO cw_lop_credit_trans (prime_lop_credit_id,employee_code,process_month,ld,md,pd,credit_days,prev_credit,balance_days,trans_created_by,trans_created_date)VALUES ('$lop_id','$employee_code','$process_month','$ld','$md','$pd','$credit_days','$prev_credit','$bal_days','$logged_id','$today_date')";
// SESSION SET AUDIT LOG _ARN 10-10-2025
$this->session_setter($filter_keys,"Lop Credit Trans DATA ADD",'user');
$this->db->query($trail_qry);
$status = "Inserted to DB";
}
}
}
}
$lop_cr_info = $this->db->query("CALL itsp_lop_cr_calc('$employee_code','$apply_month','$logged_id')");
$result = $lop_cr_info->result();
$lop_cr_info->next_result();
$status_array[] = ['Row'=>$row,'status'=>$status];
}
$table_info = $this->get_excel_import_ui($status_array);
if($lop_cr_info){
echo json_encode(array('success'=>true,'message'=>"Successfully File imported",'table_info'=>$table_info));
}else{
echo json_encode(array('success'=>false,'message'=>"File Not imported",'table_info'=>$table_info));
}
}
// public function recalc_lop_credit($prime_id,$lop_components){
// $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,";
// }
// $tot_qry .= "SUM(CASE WHEN md = ld THEN 1 ELSE 0 END) AS total_lop_check,";
// $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.' lop_check = total_lop_check,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){
// return true;
// }
// }
}
?>