File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Pre_audit_salary.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Pre_audit_salary extends Action_controller{
public function __construct(){
parent::__construct('pre_audit_salary');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//Form Data - START BSK
//PERSONAL AREA ACCESS ONLY EXCEPT SUPERADMIN USER
$area_control_where = '';
if((int)$this->logged_user_role !== 1){
$area_control_where = 'and cw_sap_personal_area.personal_code in ('.$this->logged_area_access.') ';
}
$per_area_qry = 'SELECT personal_code,personal_name FROM `cw_sap_personal_area` where trans_status = 1 '.$area_control_where;
$per_area_info = $this->db->query("CALL sp_a_run ('SELECT','$per_area_qry')");
$per_area_rslt = $per_area_info->result();
$per_area_info->next_result();
$per_area_list[""] = "---- Select Personal Area ----";
foreach ($per_area_rslt as $for) {
$personal_code = $for->personal_code;
$personal_name = $for->personal_name;
$per_area_list[$personal_code] = $personal_code.' - '.$personal_name;
}
$data['per_area_list'] = $per_area_list;
$project_qry = 'SELECT pro_id,pro_desc FROM `cw_sap_project` where trans_status = 1';
$project_info = $this->db->query("CALL sp_a_run ('SELECT','$project_qry')");
$project_rslt = $project_info->result();
$project_info->next_result();
$project_list[""] = "---- Select Project Id ----";
foreach ($project_rslt as $for){
$pro_id = $for->pro_id;
$pro_desc = $for->pro_desc;
$project_list[$pro_id] = $pro_id.' - '.$pro_desc;
}
$data['project_list'] = $project_list;
//Form Data - END BSK
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//SAVE MODEL DATA TO DATA BASE
public function save_pre_audit_salary(){
$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);
}
$search_month = $this->input->post('search_month');
$project_id = $this->input->post('project');
$process_mode = $this->input->post('process_mode');
$filter_wbs = implode(",",$this->input->post('fil_wbs_element') ?? []);
$prev_month = date("m-Y", strtotime( '-1 month' , strtotime ("01-$search_month")));
if($filter_wbs){
$fil_wbs_element = '"'.str_replace(',','","',$filter_wbs).'"';
$wbs_qry = ' and cw_pre_audit_salary.wbs_element IN ('.$fil_wbs_element.')';
}else{
$fil_wbs_element = 'NULL';
$wbs_qry = "";
}
$process_qry = "";
if((int)$process_mode === 1){
$process_qry = ' and audit_status !=2 ';
}
//Save calculated data to pre Audit table
$save_info = $this->db->query("CALL itsp_pre_audit_salary('$project_id','$fil_wbs_element','$search_month','$prev_month')");
$save_result = $save_info->result();
$save_info->next_result();
if($save_result){
if((int)$process_mode === 1){
//Check Data Exist or Not
$count_query = 'SELECT COUNT(*) as pending_count FROM `cw_pre_audit_salary` where cw_pre_audit_salary.project_id = "'.$project_id.'" and audit_status = 0 and cw_pre_audit_salary.process_month = "'.$search_month.'" and cw_pre_audit_salary.trans_status = 1 '.$wbs_qry;
$count_data = $this->db->query($count_query);
$count_result = $count_data->result();
$count_data->next_result();
if((int)$count_result[0]->pending_count === 0){
echo json_encode(array("status" => FALSE,"message" => "No data Available..","result_data" =>[]));
exit(0);
}
}
$result_data = $this->get_processed_data($search_month,$project_id,$wbs_qry,$process_qry,$sort_qry);
// $result_count = count($result_data['salary_result'] ?? []);
if($result_data['salary_result'] ){
echo json_encode(array("status" => TRUE,"message" => "Processed Successfully","result_data" =>$result_data));
}else{
echo json_encode(array("status" => FALSE,"message" => "No data Available..."));
}
}else{
echo json_encode(array("status" => FALSE,"message" => "Try After Sometime..."));
}
}
public function get_sort_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);
}
$search_month = $this->input->post('search_month');
$project_id = $this->input->post('project_id');
$fil_emp_code = $this->input->post('fil_emp_code');
$filter_wbs = implode(",",$this->input->post('fil_wbs_element') ?? []);
$mode = $this->input->post('mode');
if($filter_wbs){
$fil_wbs_element = '"'.str_replace(',','","',$filter_wbs).'"';
$wbs_qry = ' and cw_pre_audit_salary.wbs_element IN ('.$fil_wbs_element.')';
}else{
$fil_wbs_element = 'NULL';
$wbs_qry = "";
}
$default_sort = 'order by cw_pre_audit_salary.wbs_element,cw_pre_audit_salary.activity_id' ;
if($mode === 'sort'){
if((int)$fil_emp_code === 1){
$sort_qry = 'order by abs(cw_pre_audit_salary.employee_code) Asc ';
}else if((int)$fil_emp_code === 2){
$sort_qry = 'order by abs(cw_pre_audit_salary.employee_code) Desc';
}
}else{
$sort_qry = $default_sort;
}
$process_qry = 'AND audit_status != 2 ';
$result_data = $this->get_processed_data($search_month,$project_id,$wbs_qry,$process_qry,$sort_qry);
$result_count = count($result_data['salary_result'] ?? []);
// print_r($result_data); die;
if($result_count > 0){
echo json_encode(array("status" => TRUE,"message" => "Filtered Successfully","result_data" =>$result_data));
}else{
echo json_encode(array("status" => FALSE,"message" => "No data Available..."));
}
}
//Get Data Based on Audit Status Filter
public function get_audit_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);
}
$search_month = $this->input->post('search_month');
$project_id = $this->input->post('project_id');
$audit_status = $this->input->post('audit_status');
$filter_employee_code = $this->input->post('filter_employee_code');
$filter_wbs = implode(",",$this->input->post('fil_wbs_element') ?? []);
$sort_qry = 'order by cw_pre_audit_salary.wbs_element,cw_pre_audit_salary.activity_id' ;
if($filter_wbs){
$fil_wbs_element = '"'.str_replace(',','","',$filter_wbs).'"';
$wbs_qry = ' and cw_pre_audit_salary.wbs_element IN ('.$fil_wbs_element.')';
}else{
$fil_wbs_element = 'NULL';
$wbs_qry = "";
}
//Based user inputs -> retrieving qry.
if($filter_employee_code && $audit_status ){
if((int)$audit_status === 1 ) {
$process_qry = ' and audit_status = 1 and cw_pre_audit_salary.employee_code = '.$filter_employee_code.' ';
}else if((int)$audit_status === 2 ) {
$process_qry = ' and audit_status = 0 and cw_pre_audit_salary.employee_code = '.$filter_employee_code.' ';
}
}else if($audit_status){
if((int)$audit_status === 1 ) {
$process_qry = ' and audit_status = 1 ';
}else if((int)$audit_status === 2 ) {
$process_qry = ' and audit_status = 0 ';
}
}else if($filter_employee_code){
$process_qry = ' and cw_pre_audit_salary.employee_code = '.$filter_employee_code.' ';
}
$result_data = $this->get_processed_data($search_month,$project_id,$wbs_qry,$process_qry,$sort_qry);
$result_count = count($result_data['salary_result'] ?? []);
if($result_count > 0){
echo json_encode(array("status" => TRUE,"message" => "Filtered Successfully","result_data" =>$result_data));
}else{
echo json_encode(array("status" => FALSE,"message" => "No data Available..."));
}
}
//Get Processed data from table
public function get_processed_data($search_month,$project_id,$wbs_qry,$process_qry,$sort_qry){
$prev_month = date("m-Y", strtotime( '-1 month' , strtotime ("01-$search_month")));
//Get Monthly Input Columns
$mi_inp_sel_qry = 'select label_name,view_name,deduction_check,gross_check from cw_form_setting where prime_module_id = "employees" and (earn_month_check = 1 or deduction_month_check = 1) and trans_status = "1" and label_name not in ("stop_pay_code","role","position","project_id","personal_code","wbs_element","employee_code","activity_no","hold_reason","termination_status","emp_name","process_month","date_of_joining","date_of_birth") ORDER BY monthly_input_sort asc';
$mi_inp_sel_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_inp_sel_qry')");
$mi_inp_sel_rslt = $mi_inp_sel_data->result();
$mi_inp_sel_data->next_result();
$mi_qry = "";
$mi_sum_qry = "";
$mi_labels = array();
$th_line = "";
$mi_inp_qry = "";
$mi_inp_sum_qry = "";
$mi_inp_labels = array();
$th_inp_line = "";
foreach($mi_inp_sel_rslt as $key => $value){
$label_name = $value->label_name;
$view_name = $value->view_name;
$gross_check = $value->gross_check;
$ded_check = $value->deduction_check;
if($label_name !== 'checker_date' && $label_name !== 'sub_date' && $label_name !== 'msal_apr_date' && $label_name !== 'msal_rej_date' && $label_name !== 'chk_date'){
$mi_qry .= ",mi.$label_name as $label_name";
$mi_sum_qry .= ",SUM(mi.$label_name) as $label_name";
if((int)$gross_check === 1 || (int)$ded_check === 1){
$th_line .= "<th>Prev $view_name</th><th>$view_name</th>";
$mi_labels[$label_name] = $view_name;
}else{
$th_inp_line .= "<th>$view_name</th>";
$mi_inp_labels[$label_name] = $view_name;
}
}
}
$month_start = date('Y-m-01', strtotime('01-' . $search_month));
$month_end = date('Y-m-t', strtotime('01-' . $search_month));
$wbs_desc_qry = 'SELECT wbs_id, wbs_desc FROM cw_sap_wbs WHERE wbs_start_date <= "'.$month_start.'" AND wbs_end_date >= "'.$month_end.'" AND trans_status=1 ';
$wbs_desc_info = $this->db->query($wbs_desc_qry);
$wbs_desc_rslt = $wbs_desc_info->result_array();
$wbs_desc_info->next_result();
$wbs_desc_arr = array_column($wbs_desc_rslt ?? [],'wbs_desc','wbs_id');
//Get Sum of Allowance and Deduction Based Previous Month
$prev_mi_sum_query = 'SELECT cw_pre_audit_salary.wbs_element,cw_pre_audit_salary.employee_code '.$mi_sum_qry.' FROM `cw_pre_audit_salary` inner join cw_monthly_input_fms mi on mi.prime_monthly_input_fms_id = cw_pre_audit_salary.prime_monthly_input_fms_id where cw_pre_audit_salary.project_id = "'.$project_id.'" and cw_pre_audit_salary.process_month = "'.$prev_month.'" '.$wbs_qry.' '.$process_qry.' and cw_pre_audit_salary.trans_status = 1 GROUP BY cw_pre_audit_salary.wbs_element,cw_pre_audit_salary.employee_code';
$prev_mi_sum_data = $this->db->query($prev_mi_sum_query);
$prev_mi_sum_result = $prev_mi_sum_data->result_array();
$prev_mi_sum_data->next_result();
$prev_mi_sum_arr = array();
foreach ($prev_mi_sum_result as $key => $value) {
$prev_mi_sum_arr[$value['wbs_element']][$value['employee_code']] = $value;
}
//Allowance and deduction data previous month
$prev_mi_query = 'SELECT cw_pre_audit_salary.wbs_element,cw_pre_audit_salary.employee_code '.$mi_sum_qry.' FROM `cw_pre_audit_salary` inner join cw_monthly_input_fms mi on mi.prime_monthly_input_fms_id = cw_pre_audit_salary.prime_monthly_input_fms_id where cw_pre_audit_salary.project_id = "'.$project_id.'" and cw_pre_audit_salary.process_month = "'.$prev_month.'" '.$wbs_qry.' '.$process_qry.' and cw_pre_audit_salary.trans_status = 1 GROUP BY cw_pre_audit_salary.wbs_element,cw_pre_audit_salary.employee_code';
$prev_mi_data = $this->db->query($prev_mi_query);
$prev_mi_result = $prev_mi_data->result_array();
$prev_mi_data->next_result();
$prev_mi_arr = array();
foreach ($prev_mi_result as $key => $value) {
$prev_mi_arr[$value['wbs_element']][$value['employee_code']] = $value;
}
//Get Sum of Salary Based Checking data //'.$wbs_qry.'
$salary_sum_query = 'SELECT COUNT(cw_pre_audit_salary.employee_code) as emp_count,sal_audit_status,cw_pre_audit_salary.network,cw_pre_audit_salary.wbs_element,activity_id,SUM(cur_gross) as cur_gross,SUM(prev_gross) as prev_gross,SUM(rev_gross) as rev_gross,SUM(cur_prev_gross) as cur_prev_gross,SUM(rev_cur_gross) as rev_cur_gross,SUM(plan_mandays) as plan_mandays,SUM(act_mandays) as act_mandays,SUM(rev_tel_deduction) as rev_tel_deduction,SUM(rev_food_deduction) as rev_food_deduction,SUM(act_tel_deduction) as act_tel_deduction,SUM(act_salary_advance) as act_salary_advance,SUM(act_food_deduction) as act_food_deduction,SUM(rev_pf) as rev_pf,SUM(rev_esi) as rev_esi,SUM(cur_pf) as cur_pf,SUM(cur_esi) as cur_esi,SUM(rev_bonus) as rev_bonus,SUM(cur_bonus) as cur_bonus,SUM(rev_lwages) as rev_lwages,SUM(cur_lwages) as cur_lwages,SUM(rev_basic_da) as rev_basic_da,SUM(cur_basic_da) as cur_basic_da,SUM(rev_pf-cur_pf) as rev_cur_pf,SUM(rev_ot_rate) as rev_ot_rate,SUM(cur_ot_rate) as cur_ot_rate,SUM(rev_act_ot_rate) as rev_act_ot_rate '.$mi_sum_qry.' FROM `cw_pre_audit_salary` inner join cw_monthly_input_fms mi on mi.prime_monthly_input_fms_id = cw_pre_audit_salary.prime_monthly_input_fms_id where cw_pre_audit_salary.project_id = "'.$project_id.'" and cw_pre_audit_salary.process_month = "'.$search_month.'" '.$wbs_qry.' '.$process_qry.' and cw_pre_audit_salary.trans_status = 1 GROUP BY cw_pre_audit_salary.wbs_element,cw_pre_audit_salary.activity_id';
$salary_sum_data = $this->db->query($salary_sum_query);
$salary_sum_result = $salary_sum_data->result_array();
$salary_sum_data->next_result();
$salary_sum_arr = array();
foreach ($salary_sum_result as $key => $value){
$salary_sum_arr[$value['wbs_element']][$value['activity_id']] = $value;
}
//Get Salary Based Checking data //'.$wbs_qry.'
$salary_query = 'SELECT cw_pre_audit_salary.prime_monthly_input_fms_id,plan_act_deploy,prime_pre_audit_salary_id,cw_pre_audit_salary.employee_code,cw_pre_audit_salary.emp_name,cw_pre_audit_salary.position,cw_pre_audit_salary.wbs_element,category_name,cw_sap_activity.act_no,rev_activity,cw_pre_audit_salary.activity_id,act_desc,position_name,network,cur_gross,prev_gross,rev_gross,rev_cur_gross,cur_prev_gross,plan_mandays,act_mandays,rev_tel_deduction,rev_food_deduction,act_tel_deduction,act_salary_advance,act_food_deduction,sal_status,mandays_status,deploy_status,allowance_status,sal_audit_status,mandays_audit_status,deploy_audit_status,allowance_audit_status,rev_pf,rev_esi,cur_pf,cur_esi,rev_bonus,cur_bonus,rev_lwages,cur_lwages,rev_basic_da,cur_basic_da,IFNULL(rev_pf-cur_pf,0) as rev_cur_pf,rev_ot_rate,cur_ot_rate,rev_act_ot_rate,mi.date_of_joining '.$mi_qry.' FROM `cw_pre_audit_salary` inner join cw_monthly_input_fms mi on mi.prime_monthly_input_fms_id = cw_pre_audit_salary.prime_monthly_input_fms_id JOIN cw_category ON cw_category.prime_category_id = cw_pre_audit_salary.category JOIN cw_sap_position on cw_sap_position.position_code = cw_pre_audit_salary.position inner join cw_sap_activity on cw_sap_activity.prime_sap_activity_id = cw_pre_audit_salary.activity_id where cw_pre_audit_salary.project_id = "'.$project_id.'" and cw_pre_audit_salary.process_month = "'.$search_month.'" '.$wbs_qry.' '.$process_qry.' and cw_pre_audit_salary.trans_status = 1 '.$sort_qry.'';
$salary_data = $this->db->query($salary_query);
$salary_result = $salary_data->result_array();
// print_r($salary_result);
$salary_data->next_result();
if($salary_result){
$salary_arr = array();
$row_count = 0;
foreach($salary_result as $key => $value){
$salary_arr[$value['wbs_element']][$value['activity_id']][] = $value;
$row_count++;
}
//print_r($salary_arr); die;
$salary_line = "";
$deploy_line = "";
$mandays_line = "";
$fin_cur_gross_sum = 0;
$fin_prev_gross_sum = 0;
$fin_cur_prev_gross_sum = 0;
$fin_rev_cur_gross_sum = 0;
$fin_rev_gross_sum = 0;
$fin_plan_mandays = 0;
$fin_act_mandays = 0;
$fin_emp_count = 0;
$fin_plan_act_deploy = 0;
$fin_rev_pf_sum = 0;
$fin_cur_pf_sum = 0;
$fin_rev_bonus_sum = 0;
$fin_cur_bonus_sum = 0;
$fin_rev_lwages_sum = 0;
$fin_cur_lwages_sum = 0;
$fin_rev_basic_da = 0;
$fin_cur_basic_da = 0;
$fin_rev_cur_pf_sum = 0;
$act_vs_plan = 0;
$fin_act_vs_plan = 0;
$fin_rev_ot_rate_sum = 0;
$fin_cur_ot_rate_sum = 0;
$fin_rev_act_ot_rate_sum = 0;
$final_sum = array();
$prev_final_sum = array();
foreach ($salary_arr as $wbs_element => $act_arr){
$plan_deploy = 0;
$act_deploy = 0;
$wbs_description = $wbs_desc_arr[$wbs_element];
foreach ($act_arr as $activity_id => $final_arr){
$emp_count = 0;
foreach ($final_arr as $key => $value){
//Common Data
$prime_id = $value['prime_monthly_input_fms_id'];
$act_no = $value['act_no'];
$rev_activity = $value['rev_activity'];
$network = $value['network'];
$employee_code = $value['employee_code'];
$emp_name = $value['emp_name'];
$position = $value['position_name'];
$category_name = $value['category_name'];
//Salary Comparison
$cur_gross = $value['cur_gross'];
$prev_gross = $value['prev_gross'];
$rev_gross = $value['rev_gross'];
$rev_cur_gross = $value['rev_cur_gross'];
$cur_prev_gross = $value['cur_prev_gross'];
$sal_status = $value['sal_status'];
$sal_audit = $value['sal_audit_status'];
$rev_pf = $value['rev_pf'];
$cur_pf = $value['cur_pf'];
$rev_bonus = $value['rev_bonus'];
$cur_bonus = $value['cur_bonus'];
$rev_plan_bonus = $this->numformat($rev_bonus - $cur_bonus);
$rev_lwages = $value['rev_lwages'];
$cur_lwages = $value['cur_lwages'];
$rev_plan_lw = $this->numformat($rev_lwages - $cur_lwages);
$rev_basic_da = $value['rev_basic_da'];
$cur_basic_da = $value['cur_basic_da'];
$rev_plan_basic = $this->numformat($rev_basic_da - $cur_basic_da);
$rev_cur_pf = $value['rev_cur_pf'];
$rev_ot_rate = $value['rev_ot_rate'];
$cur_ot_rate = $value['cur_ot_rate'];
$rev_act_ot_rate = $value['rev_act_ot_rate'];
//Deployment and Mandays
$plan_act_deploy = $value['plan_act_deploy'];
$deploy_audit = $value['deploy_audit_status'];
$deploy_status = $value['deploy_status'];
$activity_desc = $value['act_desc'];
//Mandays
$plan_mandays = $value['plan_mandays'];
$act_mandays = $value['act_mandays'];
$mandays_audit = $value['mandays_audit_status'];
$mandays_status = $value['mandays_status'];
//Allowance and Deductions
$date_of_joining = date("d-m-Y",strtotime($value['date_of_joining']));
$allowance_audit = $value['allowance_audit_status'];
$allowance_status = $value['allowance_status'];
$emp_count = $emp_count + 1;
$stl = "";
$sal_check = "";
if((int)$sal_status === 2){
$stl = "style='background-color:#f79595';";
}
if((int)$sal_audit === 1){
$stl = "style='background-color:#a2f1a2';";
$sal_check = "disabled";
}
$mandays_style = "";
$mandays_check = "";
if((int)$mandays_status === 2){
$mandays_style = "style='background-color:#f79595';";
}
if((int)$mandays_audit === 1){
$mandays_style = "style='background-color:#a2f1a2';";
$mandays_check = "disabled";
}
$allow_style = "";
$allow_check = "";
if((int)$allowance_status === 2){
$allow_style = "style='background-color:#f79595';";
}
if((int)$allowance_audit === 1){
$allow_style = "style='background-color:#a2f1a2';";
$allow_check = "disabled";
}
//MI Allowances
$td_line = "";
foreach ($mi_labels as $mi_lable => $mi_view){
$prev_val = $prev_mi_arr[$wbs_element][$employee_code][$mi_lable];
$val = $value[$mi_lable];
if(!$prev_val){
$prev_val = '0.00';
}
if(!$val){
$val = '0.00';
}
$td_line .= "<td>$prev_val</td><td>$val</td>";
}
//MI Table inputs
$inp_line = "";
foreach ($mi_inp_labels as $mi_lable => $mi_view){
$prev_val = $prev_mi_arr[$wbs_element][$employee_code][$mi_lable];
$val = $value[$mi_lable];
if(!$prev_val){
$prev_val = '0.00';
}
if(!$val){
$val = '0.00';
}
$inp_line .= "<td>$val</td>";
}
//Entry Line
$salary_line .= "<tr $stl><td>$wbs_element</td><td>$act_no</td><td>$employee_code</td><td>$emp_name</td><td style='display:none;' calss='wbs_desc'>$wbs_description</td><td>$position</td><td>$category_name</td><td>$cur_gross</td><td>$rev_gross</td><td>$prev_gross</td><td>$cur_prev_gross</td><td>$rev_cur_gross</td><td>$rev_pf</td><td>$cur_pf</td><td>$rev_cur_pf</td><td>$rev_bonus</td><td>$cur_bonus</td><td>$rev_plan_bonus</td><td>$rev_lwages</td><td>$cur_lwages</td><td>$rev_plan_lw</td><td>$rev_basic_da</td><td>$cur_basic_da</td><td>$rev_plan_basic</td><td>$rev_ot_rate</td><td>$cur_ot_rate</td><td>$rev_act_ot_rate</td><td style='text-align: center;'><input type='checkbox' class='select_box' name = 'salary[]' id='salary' value='$prime_id' $sal_check></td></tr>";
$mandays_line .= "<tr $mandays_style><td>$wbs_element</td><td>$rev_activity</td><td>$activity_desc</td><td>$act_no</td><td>$plan_mandays</td><td>$act_mandays</td><td>$employee_code</td><td>$emp_name</td><td style='display:none;' calss='wbs_desc'>$wbs_description</td><td>$position</td>$inp_line<td style='text-align: center;'><input type='checkbox' name = 'mandays[]' class='select_box' id='mandays' value='$prime_id' $mandays_check></td></tr>";
$allowance_line .= "<tr $allow_style><td>$wbs_element</td><td>$act_no</td><td>$employee_code</td><td>$emp_name</td><td style='display:none;' class='wbs_desc'></td><td>$position</td><td>$date_of_joining</td>$td_line<td style='text-align: center;'><input type='checkbox' class='select_box' name = 'allowance[]' id='allowance' value='$prime_id' $allow_check></td></tr>";
}
//Get all the sum data based on Activity no.
$cur_gross_sum = $salary_sum_arr[$wbs_element][$activity_id]['cur_gross'];
$prev_gross_sum = $salary_sum_arr[$wbs_element][$activity_id]['prev_gross'];
$cur_prev_gross_sum = $salary_sum_arr[$wbs_element][$activity_id]['cur_prev_gross'];
$rev_cur_gross_sum = $salary_sum_arr[$wbs_element][$activity_id]['rev_cur_gross'];
$rev_gross_sum = $salary_sum_arr[$wbs_element][$activity_id]['rev_gross'];
$plan_mandays = $plan_mandays;
//$salary_sum_arr[$wbs_element][$activity_id]['plan_mandays'];
$act_mandays = $salary_sum_arr[$wbs_element][$activity_id]['act_mandays'];
$rev_pf_sum = $salary_sum_arr[$wbs_element][$activity_id]['rev_pf'];
$cur_pf_sum = $salary_sum_arr[$wbs_element][$activity_id]['cur_pf'];
$rev_cur_pf_sum = $salary_sum_arr[$wbs_element][$activity_id]['rev_cur_pf'];
$rev_bonus_sum = $salary_sum_arr[$wbs_element][$activity_id]['rev_bonus'];
$cur_bonus_sum = $salary_sum_arr[$wbs_element][$activity_id]['cur_bonus'];
$rev_cur_bonus_sum = $this->numformat($rev_bonus_sum - $cur_bonus_sum);
$rev_lwages_sum = $salary_sum_arr[$wbs_element][$activity_id]['rev_lwages'];
$cur_lwages_sum = $salary_sum_arr[$wbs_element][$activity_id]['cur_lwages'];
$rev_cur_lw_sum = $this->numformat($rev_lwages_sum - $cur_lwages_sum);
$rev_basic_da_sum = $salary_sum_arr[$wbs_element][$activity_id]['rev_basic_da'];
$cur_basic_da_sum = $salary_sum_arr[$wbs_element][$activity_id]['cur_basic_da'];
$rev_cur_basic_sum = $this->numformat($rev_basic_da_sum - $cur_basic_da_sum);
$rev_ot_rate_sum = $salary_sum_arr[$wbs_element][$activity_id]['rev_ot_rate'];
$cur_ot_rate_sum = $salary_sum_arr[$wbs_element][$activity_id]['cur_ot_rate'];
$rev_act_ot_rate_sum = $salary_sum_arr[$wbs_element][$activity_id]['rev_act_ot_rate'];
//MI Table SUM inputs
$sum_inp_line = "";
foreach($mi_inp_labels as $mi_lable => $mi_view){
$val_sum = $salary_sum_arr[$wbs_element][$activity_id][$mi_lable];
if(!$val_sum){
$val_sum = '0.00';
}
$final_sum[$mi_lable] += $val_sum;
if($mi_lable !== "md"){
$sum_inp_line .= "<td>$val_sum</td>";
}
}
//MI Table Allowance SUM
$sum_td_line = "";
foreach ($mi_labels as $mi_lable => $mi_view){
$prev_sum_val = $prev_mi_sum_arr[$wbs_element][$employee_code][$mi_lable];
$val_sum = $salary_sum_arr[$wbs_element][$activity_id][$mi_lable];
if(!$prev_sum_val){
$prev_sum_val = '0.00';
}
if(!$val_sum){
$val_sum = '0.00';
}
$final_sum[$mi_lable] += $val_sum;
$prev_final_sum[$mi_lable] += $prev_sum_val;
$sum_td_line .= "<td>$prev_sum_val</td><td>$val_sum</td>";
}
//Get final Sum Data - START
$fin_cur_gross_sum = $this->numformat($fin_cur_gross_sum + $cur_gross_sum);
$fin_prev_gross_sum = $this->numformat($fin_prev_gross_sum + $prev_gross_sum);
$fin_cur_prev_gross_sum = $this->numformat($fin_cur_prev_gross_sum + $cur_prev_gross_sum);
$fin_rev_cur_gross_sum = $this->numformat($fin_rev_cur_gross_sum + $rev_cur_gross_sum);
$fin_rev_gross_sum = $this->numformat($fin_rev_gross_sum + $rev_gross_sum);
$fin_plan_mandays = $this->numformat($fin_plan_mandays + $plan_mandays);
$fin_act_mandays = $this->numformat($fin_act_mandays + $act_mandays);
$fin_emp_count = $this->numformat($fin_emp_count + $emp_count);
$fin_plan_act_deploy = $this->numformat($fin_plan_act_deploy + $plan_act_deploy);
$fin_rev_pf_sum = $this->numformat($fin_rev_pf_sum + $rev_pf_sum);
$fin_cur_pf_sum = $this->numformat($fin_cur_pf_sum + $cur_pf_sum);
$fin_rev_cur_pf_sum = $this->numformat($fin_rev_cur_pf_sum + $rev_cur_pf_sum);
$fin_rev_bonus_sum = $this->numformat($fin_rev_bonus_sum + $rev_bonus_sum);
$fin_cur_bonus_sum = $this->numformat($fin_cur_bonus_sum + $cur_bonus_sum);
$fin_rev_cur_bonus_sum = $this->numformat($fin_rev_bonus_sum - $fin_cur_bonus_sum);
$fin_rev_lwages_sum = $this->numformat($fin_rev_lwages_sum + $rev_lwages_sum);
$fin_cur_lwages_sum = $this->numformat($fin_cur_lwages_sum + $cur_lwages_sum);
$fin_rev_cur_lw_sum = $this->numformat($fin_rev_lwages_sum - $fin_cur_lwages_sum);
$fin_rev_basic_da_sum = $this->numformat($fin_rev_basic_da_sum + $rev_basic_da_sum);
$fin_cur_basic_da_sum = $this->numformat($fin_cur_basic_da_sum + $cur_basic_da_sum);
$fin_rev_cur_basic_sum = $this->numformat($fin_rev_basic_da_sum - $fin_cur_basic_da_sum);
$fin_rev_ot_rate_sum = $this->numformat($fin_rev_ot_rate_sum + $rev_ot_rate_sum);
$fin_cur_ot_rate_sum = $this->numformat($fin_cur_ot_rate_sum + $cur_ot_rate_sum);
$fin_rev_act_ot_rate_sum = $this->numformat($fin_rev_act_ot_rate_sum + $rev_act_ot_rate_sum);
//MI Input Final Sum
$fin_sum_inp_line = "";
foreach ($mi_inp_labels as $mi_lable => $mi_view){
$fin_sum = $this->numformat($final_sum[$mi_lable]);
if($mi_lable !== "md"){
$fin_sum_inp_line .= "<td>$fin_sum</td>";
}
}
//MI Allowance Final Sum
$fin_sum_td_line = "";
foreach ($mi_labels as $mi_lable => $mi_view){
$fin_sum = $this->numformat($final_sum[$mi_lable]);
$prev_fin_sum = $this->numformat($prev_final_sum[$mi_lable]);
$fin_sum_td_line .= "<td>$prev_fin_sum</td><td>$fin_sum</td>";
}
//Get final Sum Data - END
//Activity Wise Data
$salary_line .= "<tr style='font-weight: bold;background-color:#f7f49e'><td></td><td></td><td></td><td></td><td style='display:none;' class='wbs_desc'></td><td></td><td></td><td>$cur_gross_sum</td><td>$rev_gross_sum</td><td>$prev_gross_sum</td><td>$cur_prev_gross_sum</td><td>$rev_cur_gross_sum</td><td>$rev_pf_sum</td><td>$cur_pf_sum</td><td>$rev_cur_pf_sum</td><td>$rev_bonus_sum</td><td>$cur_bonus_sum</td><td>$rev_cur_bonus_sum</td><td>$rev_lwages_sum</td><td>$cur_lwages_sum</td><td>$rev_cur_lw_sum</td><td>$rev_basic_da_sum</td><td>$cur_basic_da_sum</td><td>$rev_cur_basic_sum</td><td>$rev_ot_rate_sum</td><td>$cur_ot_rate_sum</td><td>$rev_act_ot_rate_sum</td><td></td></tr>";
//Deployment Tab Activities
$deploy_style = "";
if((int)$deploy_audit === 1){
$deploy_style = "style='background-color:#a2f1a2';";
}else{
if((int)$plan_act_deploy !== (int)$emp_count || $rev_activity !== (int)$act_no){
$deploy_style = "style='background-color:#f79595';";
}
}
$act_vs_plan = $plan_act_deploy - $emp_count;
$deploy_line .= "<tr $deploy_style><td>$wbs_element</td><td>$rev_activity</td><td>$activity_desc</td><td>$act_no</td><td style='display:none;' class='wbs_desc'>$wbs_description</td><td>$plan_act_deploy</td><td>$emp_count</td><td>$act_vs_plan</td></tr>";
$mandays_line .= "<tr style='font-weight: bold;background-color:#f7f49e;'><td></td><td></td><td></td><td></td><td>$plan_mandays</td><td>$act_mandays</td><td></td><td></td><td style='display:none;' class='wbs_desc'>$wbs_description</td><td></td><td></td>$sum_inp_line</tr>";
$allowance_line .= "<tr style='font-weight: bold;background-color:#f7f49e'><td></td><td></td><td></td><td></td><td style='display:none;' class='wbs_desc'></td><td></td><td></td>$sum_td_line<td style='text-align: center;'></td></tr>";
$plan_deploy = $plan_deploy + $plan_act_deploy;
$act_deploy = $act_deploy + $emp_count;
}
$fin_act_vs_plan = $fin_act_vs_plan + $act_vs_plan;
$deploy_line .= "<tr style='font-weight: bold;background-color:#f7f49e;'><td></td><td></td><td></td><td></td><td style='display:none;' class='wbs_desc'></td><td>$plan_deploy</td><td>$act_deploy</td><td>$act_vs_plan</td></tr>";
}
//Final Sum Line
$salary_tfoot = "<tr style='font-weight: bold;background-color:#f2f2f2;color: blue;' class='foot_sum'><td>Grand Total : $row_count</td><td></td><td></td><td></td><td style='display:none;' class='wbs_desc'></td><td></td><td></td><td>$fin_cur_gross_sum</td><td>$fin_rev_gross_sum</td><td>$fin_prev_gross_sum</td><td>$fin_cur_prev_gross_sum</td><td>$fin_rev_cur_gross_sum</td><td>$fin_rev_pf_sum</td><td>$fin_cur_pf_sum</td><td>$fin_rev_cur_pf_sum</td><td>$fin_rev_bonus_sum</td><td>$fin_cur_bonus_sum</td><td>$fin_rev_cur_bonus_sum</td><td>$fin_rev_lwages_sum</td><td>$fin_cur_lwages_sum</td><td>$fin_rev_cur_lw_sum</td><td>$fin_rev_basic_da_sum</td><td>$fin_cur_basic_da_sum</td><td>$fin_rev_cur_basic_sum</td><td>$fin_rev_ot_rate_sum</td><td>$fin_cur_ot_rate_sum</td><td>$fin_rev_act_ot_rate_sum</td><td></td></tr>";
$deploy_tfoot = "<tr style='font-weight: bold;background-color:#f2f2f2;color: blue;' class='foot_sum'><td>Grand Total</td><td></td><td></td><td></td><td>$fin_plan_act_deploy</td><td>$fin_emp_count</td><td>$fin_act_vs_plan</td></tr>";
//manday tfoot
$mandays_tfoot = "<tr style='font-weight: bold;background-color:#f2f2f2;color: blue;' class='foot_sum'><td>Grand Total</td><td></td><td></td><td></td><td>$fin_plan_mandays</td><td></td><td></td><td></td><td style='display:none;' class='wbs_desc'></td><td></td><td></td>$fin_sum_inp_line<td></td></tr>";
//allowance tfoot
$allowance_tfoot = "<tr style='font-weight: bold;background-color:#f2f2f2;color: blue;' class='foot_sum'><td>Grand Total</td><td></td><td></td><td></td><td style='display:none;' class='wbs_desc'></td><td></td><td></td>$fin_sum_td_line<td style='text-align: center;'></td></tr>";
}
$allowance_data = "<table id='allowance_table' class='table dis_table' style='width:100% !important;'>
<thead>
<th>WBS Element</th><th>Actual Activity No</th><th>Employee Code</th><th>Employee Name</th><th style='display:none;' class='wbs_desc'>Wbs Description</th><th>Designation</th><th>Emp Start Date</th>
$th_line
<th><input type='checkbox' class='select_all_box' id='allowance_all'></th>
</thead>
<tbody>$allowance_line</tbody>
<tfoot>$allowance_tfoot</tfoot>
</table>";
$mandays_data = "<table id='mandays_table' class='table dis_table mandays_tbl' style='width:100% !important;'><thead><th>WBS Element</th><th>Planned Activity</th><th>Activity Description</th><th>Actual Activity</th><th>Planned Mandays</th><th>Actual Mandays</th><th>Employee Id</th><th>Employee Name</th><th style='display:none;' calss='wbs_desc'>Wbs Description</th><th>Designation</th>$th_inp_line<th><input type='checkbox' class='select_all_box' id='mandays_all'></th>
</thead>
<tbody>$mandays_line</tbody>
<tfoot>$mandays_tfoot</tfoot>
</table>";
$count_query = 'SELECT SUM(CASE WHEN sal_audit_status != 1 THEN 1 else 0 END) as sal_count,SUM(CASE WHEN mandays_audit_status != 1 THEN 1 else 0 END) as mandays_count,SUM(CASE WHEN deploy_audit_status != 1 THEN 1 else 0 END) as deploy_count,SUM(CASE WHEN allowance_audit_status != 1 THEN 1 else 0 END) as allowance_count FROM `cw_pre_audit_salary` where cw_pre_audit_salary.project_id = "'.$project_id.'" '.$process_qry.' and sal_audit_status !=2 AND mandays_audit_status != 2 AND allowance_audit_status != 2 and deploy_audit_status != 2 and cw_pre_audit_salary.trans_status = 1 and cw_pre_audit_salary.process_month = "'.$search_month.'" '.$wbs_qry;
$count_data = $this->db->query($count_query);
$count_result = $count_data->result_array();
$count_data->next_result();
return array("salary_result"=>$salary_line,"salary_tfoot"=>$salary_tfoot,"deploy_result"=>$deploy_line,"deploy_tfoot"=>$deploy_tfoot,"mandays_result"=>$mandays_data,"allowance_result"=>$allowance_data,"count_result"=>$count_result);
}
//BSK START FOR PROJECT ID FETCH FROM MI FMS BASED ON PERSONAL CODE
public function project_id_fetch(){
$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);
}
$personal_code = $this->input->post('personal_code');
$search_month = $this->input->post('search_month');
$area_access = $this->logged_area_access;
$pro_id_qry = 'SELECT pro_id,pro_desc FROM `cw_sap_project` inner join cw_monthly_input_fms on cw_monthly_input_fms.project_id = cw_sap_project.pro_id where cw_monthly_input_fms.process_month = "'.$search_month.'" and cw_monthly_input_fms.trans_status = 1 and entry_status = 1 and pro_personal_area_id = "'.$personal_code.'" GROUP BY cw_sap_project.pro_id';
$pro_id_info = $this->db->query("CALL sp_a_run ('SELECT','$pro_id_qry')");
$pro_id_rslt = $pro_id_info->result_array();
$pro_id_info->next_result();
echo json_encode($pro_id_rslt);
}
//BSK END FOR PROJECT ID FETCH FROM MI FMS BASED ON PERSONAL CODE
//GET WBS FROM MONTHLY INPUT FMS BSK START
public function fil_wbs_fetch(){
$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);
}
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$search_month = $this->input->post('search_month');
$fil_wbs_qry = 'select cw_sap_wbs.prime_sap_wbs_id,cw_sap_wbs.wbs_id,cw_sap_wbs.wbs_desc from cw_sap_wbs inner join cw_monthly_input_fms on cw_monthly_input_fms.wbs_element = cw_sap_wbs.wbs_id where cw_monthly_input_fms.process_month = "'.$search_month.'" and wbs_project_id = "'.$project.'" and cw_monthly_input_fms.trans_status = 1 GROUP BY cw_sap_wbs.prime_sap_wbs_id';
$fil_wbs_info = $this->db->query("CALL sp_a_run ('SELECT','$fil_wbs_qry')");
$fil_wbs_rslt = $fil_wbs_info->result_array();
$fil_wbs_info->next_result();
echo json_encode($fil_wbs_rslt);
}
//GET WBS FROM MONTHLY INPUT FMS BSK END
public function update_status(){
$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);
}
$table_id = $this->input->post('table_id');
$ids = implode('","',$this->input->post('ids') ?? []);
$remarks = $this->input->post('remarks');
$type = $this->input->post('type');
$search_month = $this->input->post('search_month');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$audit_status = $this->input->post('audit_status');
$filter_wbs = implode('","',$this->input->post('fil_wbs_element') ?? []);
if($filter_wbs){
$wbs_qry = ' and cw_pre_audit_salary.wbs_element IN ("'.$filter_wbs.'")';
}else{
$wbs_qry = "";
}
$status_ids = array('salary'=>'sal_audit_status','mandays'=>'mandays_audit_status','deploy'=>'deploy_audit_status','allowance'=>'allowance_audit_status');
$column_name = $status_ids[$table_id];
$qry = "";
if($type === 'reject'){
// $upd = 'audit_status = 2,'.$column_name.' = 2';
$upd = 'audit_status = 2 , sal_audit_status = 2 , mandays_audit_status = 2 , deploy_audit_status = 2 , allowance_audit_status = 2 ';
$mi_upd = 'check_status = 2,entry_status=2';
if($ids){ // [MS 07-10-2024]
$qry = ' and prime_monthly_input_fms_id in ("'.$ids.'")';
}
}else{
$upd = ''.$column_name.' = 1';
$mi_upd = 'check_status = 1,entry_status=1';
if($ids){
$qry = ' and prime_monthly_input_fms_id in ("'.$ids.'")';
}
}
$process_qry = " AND audit_status != 2 "; //only for process submit
if((int)$audit_status === 1){
$process_qry = ' and audit_status = 1 ';
}else
if((int)$audit_status === 2){
$process_qry = ' and audit_status = 0 ';
}
//$process_qry = ' AND audit_status != 2';
$created_on = date("Y-m-d H:i:s");
$upd .= ',trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '"';
$upd_query = 'UPDATE ' . $this->prime_table . ' SET '.$upd.' WHERE cw_pre_audit_salary.project_id = "'.$project.'" and cw_pre_audit_salary.process_month = "'.$search_month.'" and '.$column_name.' not in (1,2) and cw_pre_audit_salary.trans_status = 1 and audit_status not in (1,2) '.$wbs_qry.' '.$qry;
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
if($type === 'reject'){
//Update Checker Status in timesheet
$upd_mi_query .= 'trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '"';
$upd_mi_query = 'UPDATE cw_monthly_input_fms SET '.$mi_upd.',remarks="'.$remarks.'", '.$upd_mi_query. ' WHERE prime_monthly_input_fms_id in ("' . $ids . '")';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_mi_query')");
}
if($upd_query){
$result_data = $this->get_processed_data($search_month,$project,$wbs_qry,$process_qry,$sort_qry);
echo json_encode(array("status" => TRUE,"message" => "Updated Successfully","result_data" =>$result_data));
}else{
echo json_encode(array("status" => FALSE,"message" => "Try After Sometime..."));
}
}
//Update overall approve final
public function update_final_status(){
$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);
}
$type = $this->input->post('type');
$ids = implode('","',$this->input->post('ids') ?? []);
$search_month = $this->input->post('search_month');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$wbs_select = implode(",",$this->input->post('wbs_select') ?? []);
$wbs_arr = $this->input->post('wbs_select');
$wbs_count = count($wbs_arr ?? []);
$created_on = date("Y-m-d H:i:s");
if($wbs_select){
$wbs_select = '"'.str_replace(',','","',$wbs_select).'"';
$wbs_qry = ' AND cw_pre_audit_salary.wbs_element IN ('.$wbs_select.')';
}else{
$wbs_qry = "";
}
if($ids){
$qry = ' prime_monthly_input_fms_id in ("'.$ids.'")';
}
$process_qry = ' '; //only for process submit
if($type === 'audit_approve'){
$upd_query = 'UPDATE '.$this->prime_table.' SET audit_status = 1 WHERE cw_pre_audit_salary.project_id = "'.$project.'" AND cw_pre_audit_salary.process_month = "'.$search_month.'" '.$wbs_qry.' AND sal_audit_status = 1 AND deploy_audit_status = 1 AND mandays_audit_status = 1 AND allowance_audit_status = 1 AND cw_pre_audit_salary.trans_status = 1 AND audit_status NOT IN (1,2) and '.$qry;
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
foreach($wbs_arr as $key => $wbs){
//Select Count from pre_audit_salary table
$wbs_count_qry = 'SELECT COUNT(*) AS emp_count,cw_pre_audit_salary.prime_trans_process_id,cw_pre_audit_salary.project_id FROM cw_pre_audit_salary WHERE cw_pre_audit_salary.project_id = "'.$project.'" AND cw_pre_audit_salary.process_month = "'.$search_month.'" AND cw_pre_audit_salary.wbs_element = "'.$wbs.'" AND audit_status = 1 AND trans_status = 1 GROUP BY cw_pre_audit_salary.prime_trans_process_id';
$wbs_count_info = $this->db->query("CALL sp_a_run ('SELECT','$wbs_count_qry')");
$wbs_count_rslt = $wbs_count_info->result();
$wbs_count_info->next_result();
foreach($wbs_count_rslt as $countkey => $countvalue){
$trans_id = $countvalue->prime_trans_process_id;
$emp_count = $countvalue->emp_count;
$project_id = $countvalue->project_id;
//Check Transaction Exist
$trans_exist_qry = 'SELECT COUNT(*) as trans_count FROM cw_trans_process_list WHERE cw_trans_process_list.prime_trans_process_id = "'.$trans_id.'" AND trans_status = 1';
$trans_exist_info = $this->db->query("CALL sp_a_run ('SELECT','$trans_exist_qry')");
$trans_exist_rslt = $trans_exist_info->result();
$trans_exist_info->next_result();
$trans_count = $trans_exist_rslt[0]->trans_count;
if($trans_count){ //Update as Yet to start
$upd_query = 'UPDATE cw_trans_process_list SET process_status = "0",sap_trans_status = "0",payslip_status = "0" WHERE prime_trans_process_id = "'.$trans_id.'" and trans_status = 1';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}else{
//insert the data to trans scheduler
$insert_query = 'INSERT INTO cw_trans_process_list(process_month,personal_code,project_id,wbs_element,process_status,sap_trans_status,total,trans_created_by,trans_created_date) VALUES ("'.$search_month.'","'.$personal_code.'","'.$project.'","'.$wbs.'","0","0","'.$emp_count.'","'.$this->logged_id.'","'.$created_on.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
if($insert_id){
$upd_query = 'UPDATE '.$this->prime_table.' SET prime_trans_process_id = "'.$insert_id.'" WHERE cw_pre_audit_salary.project_id = "'.$project.'" and cw_pre_audit_salary.process_month = "'.$search_month.'" and cw_pre_audit_salary.wbs_element = "'.$wbs.'" and cw_pre_audit_salary.trans_status = 1 and audit_status = 1 and (prime_trans_process_id IS NULL or prime_trans_process_id = 0) and '.$qry;
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}
}
}
}
//MI FMS UPDATE QUERY
$upd_mi_query = 'UPDATE cw_monthly_input_fms inner join cw_pre_audit_salary on cw_pre_audit_salary.prime_monthly_input_fms_id = cw_monthly_input_fms.prime_monthly_input_fms_id SET check_status = 1,cw_monthly_input_fms.prime_trans_process_id = cw_pre_audit_salary.prime_trans_process_id,cw_monthly_input_fms.trans_updated_by = "' . $this->logged_id . '",cw_monthly_input_fms.trans_updated_date = "' . $created_on . '",cw_monthly_input_fms.chk_date = "' . $created_on . '" WHERE cw_monthly_input_fms.project_id = "'.$project.'" and cw_monthly_input_fms.process_month = "'.$search_month.'" and entry_status = 1 and audit_status = 1 '.$wbs_qry .' and cw_monthly_input_fms.'.$qry;
$rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_mi_query')");
}else{
$upd_query = 'UPDATE ' . $this->prime_table . ' SET audit_status = 2,sal_audit_status=2,mandays_audit_status=2,allowance_audit_status=2,deploy_audit_status=2 WHERE cw_pre_audit_salary.project_id = "'.$project.'" and cw_pre_audit_salary.process_month = "'.$search_month.'" '.$wbs_qry.' and sal_audit_status = 1 and deploy_audit_status = 1 and mandays_audit_status = 1 and allowance_audit_status = 1 and audit_status != 0 and cw_pre_audit_salary.trans_status = 1 and '.$qry;
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
$upd_mi_query .= 'cw_monthly_input_fms.trans_updated_by = "' . $this->logged_id . '",cw_monthly_input_fms.trans_updated_date = "' . $created_on . '",cw_monthly_input_fms.chk_date = "' . $created_on . '"';
$upd_mi_query = 'UPDATE cw_monthly_input_fms inner join cw_pre_audit_salary on cw_pre_audit_salary.prime_monthly_input_fms_id = cw_monthly_input_fms.prime_monthly_input_fms_id SET check_status = 2,entry_status=2,'.$upd_mi_query.' WHERE cw_monthly_input_fms.project_id = "'.$project.'" and cw_monthly_input_fms.process_month = "'.$search_month.'" and entry_status = 1 and audit_status = 2 '.$wbs_qry .' and cw_pre_audit_salary.'.$qry;
$this->db->query("CALL sp_a_run ('UPDATE','$upd_mi_query')");
}
//Get Sum of Salary Based Checking data
$approve_data_query = 'SELECT cw_pre_audit_salary.wbs_element,wbs_desc FROM `cw_pre_audit_salary` inner join cw_sap_wbs on cw_sap_wbs.wbs_id = cw_pre_audit_salary.wbs_element where cw_pre_audit_salary.project_id = "'.$project.'" and cw_pre_audit_salary.process_month = "'.$search_month.'" and sal_audit_status =1 AND mandays_audit_status = 1 AND allowance_audit_status = 1 and deploy_audit_status = 1 and audit_status != 1 GROUP BY cw_pre_audit_salary.wbs_element';
$approve_data_data = $this->db->query($approve_data_query);
$approve_data_result = $approve_data_data->result();
$approve_data_data->next_result();
if($upd_mi_query){
$result_data = $this->get_processed_data($search_month,$project,$wbs_qry,$process_qry,$sort_qry);
echo json_encode(array("status" => TRUE,"message" => "Status Updated...","approved_data" =>$approve_data_result,"result_data" =>$result_data));
}else{
echo json_encode(array("status" => FALSE,"message" => "No Data Available to Update..."));
}
}
//Get final approve Authorization Data
public function audit_authorization(){
$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);
}
$search_month = $this->input->post('search_month');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$filter_wbs = implode('","',$this->input->post('fil_wbs_element') ?? []);
$ids = implode('","',$this->input->post('ids') ?? []);
if($ids){ // [MS 07-10-2024]
$qry = ' and prime_monthly_input_fms_id in ("'.$ids.'")';
}
if($filter_wbs){
$wbs_qry = ' and cw_pre_audit_salary.wbs_element IN ("'.$filter_wbs.'")';
}else{
$wbs_qry = "";
}
//Get Sum of Salary Based Checking data
$approve_data_query = 'SELECT cw_pre_audit_salary.wbs_element,wbs_desc FROM `cw_pre_audit_salary` inner join cw_sap_wbs on cw_sap_wbs.wbs_id = cw_pre_audit_salary.wbs_element where cw_pre_audit_salary.project_id = "'.$project.'" and cw_pre_audit_salary.process_month = "'.$search_month.'" and sal_audit_status =1 AND mandays_audit_status = 1 AND allowance_audit_status = 1 and deploy_audit_status = 1 and audit_status != 1 and cw_pre_audit_salary.trans_status = 1 '.$wbs_qry.' '.$qry.' GROUP BY cw_pre_audit_salary.wbs_element';
$approve_data_data = $this->db->query($approve_data_query);
$approve_data_result = $approve_data_data->result();
$approve_data_data->next_result();
//Get data if tab having the non approved data
$count_qry = 'select COUNT(employee_code) as count from cw_pre_audit_salary where cw_pre_audit_salary.project_id = "'.$project.'" and cw_pre_audit_salary.process_month = "'.$search_month.'" and audit_status = 0 and (sal_audit_status != 1 or mandays_audit_status != 1 or allowance_audit_status != 1 or deploy_audit_status != 1) and trans_status = 1 '.$wbs_qry. $qry.' ';
$count_info = $this->db->query("CALL sp_a_run ('SELECT','$count_qry')");
$count_rslt = $count_info->result();
$count_info->next_result();
$count = (int)$count_rslt[0]->count;
if($count > 0){
echo json_encode(array("status" => FALSE,"message" => "Please Approve all the TAB wise data..."));
}else{
echo json_encode(array("status" => TRUE,"result_data" =>$approve_data_result));
}
}
public function numformat($num){
return number_format((float)$num, 2, '.', '');
}
}
?>