File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Pre_audit_manual_salary.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Pre_audit_manual_salary extends Action_controller{
public function __construct(){
parent::__construct('pre_audit_manual_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_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT pro_id,pro_desc FROM `cw_sap_project` where trans_status = 1')");
$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;
//manual type
$pay_type_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT prime_manual_pay_type_id,pay_type FROM `cw_manual_pay_type` where trans_status = 1')");
$pay_type_rslt = $pay_type_info->result_array();
$pay_type_info->next_result();
$pay_type_list[""] = "---- Select Pay Type ----";
foreach ($pay_type_rslt as $for) {
$pay_type_id = $for['prime_manual_pay_type_id'];
$pay_type = $for['pay_type'];
$pay_type_list[$pay_type_id] = $pay_type;
}
$data['pay_type_list'] = $pay_type_list;
$data["pay_type_arr"] = array_column($pay_type_rslt ?? [],"pay_type","prime_manual_pay_type_id");
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//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');
$pro_id_qry = 'SELECT pro_id,pro_desc FROM `cw_sap_project` inner join cw_transactions_fms_man on cw_transactions_fms_man.project_id = cw_sap_project.pro_id where cw_transactions_fms_man.process_month = "'.$search_month.'" and pro_personal_area_id = "'.$personal_code.'" and cw_transactions_fms_man.trans_status = 1 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_transactions_fms_man on cw_transactions_fms_man.wbs_element = cw_sap_wbs.wbs_id where cw_transactions_fms_man.process_month = "'.$search_month.'" and wbs_project_id = "'.$project.'" and cw_transactions_fms_man.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
//View Transaction Data
public function view_report(){
$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');
$project = $this->input->post('project');
$process_mode = (int)$this->input->post('process_mode');
$pay_type = (int)$this->input->post('pay_type');
$filter_wbs = implode(",",$this->input->post('fil_wbs_element') ?? []);
echo $this->get_transaction_data($personal_code,$search_month,$project,$process_mode,$filter_wbs,$pay_type);
}
public function get_transaction_data($personal_code,$search_month,$project,$process_mode,$filter_wbs,$pay_type){
// $prev_month = date("m-Y", strtotime( '-1 month' , strtotime ("01-$search_month")));
//WBS BASED WHERE CONDITION
$wbs_qry = "";
if($filter_wbs){
$fil_wbs_element = str_replace(',','","',$filter_wbs);
$wbs_qry = ' and cw_transactions_fms_man.wbs_element IN ("'.$fil_wbs_element.'")';
}
//PROCESS MODE BASED WHERE CONDITION
$process_qry = "";
if((int)$process_mode === 1){
$process_qry = ' and audit_status = 0 ';
}else
if((int)$process_mode === 2){
$process_qry = ' and audit_status != 2 ';
}
//PAY TYPE BASED WYHERE CONDITION
$pay_type_qry = "";
if((int)$pay_type){
$pay_type_qry = ' and man_pay_type = "'.$pay_type.'"';
}
$select_query = "";
$pick_query = "";
$table_head = array("man_pay_type" => "Pay Type","employee_code" => "Employee code","emp_name" => "Employee Name");
$final_qry = "";
$form_qry = 'SELECT prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "employees" and transaction_type in (1,2,3) and input_view_type in (1,2) and ((earn_payroll_check = "1" or ded_payroll_check = "1") or (label_name in ("employee_code"))) and trans_status = "1" order by payroll_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
$table_name = "cw_transactions_fms_man";
$i = 1;
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
$table_head[$label_name] = $view_name;
if($label_name == "role"){
$view_name = "Category";
}
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list ?? "");
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else{
$select_query .= "$table_name.$label_name , ";
}
$i++;
}
$table_head["total_earnings"] = "Total Earnings";
$table_head["net_pay"] = "Net Pay";
$table_head["posting_month"] = "Posting Month";
$table_head["trans_created_date"] = "Created On";
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$trans_status = " left join cw_employees on cw_employees.employee_code = cw_transactions_fms_man.employee_code where ".$table_name.".trans_status = 1 and transactions_month = \"".$search_month."\" and cw_transactions_fms_man.personal_code = \"".$personal_code."\" and cw_transactions_fms_man.project_id = \"".$project."\" ";
// $query_key
$final_qry = "select cw_transactions_fms_man.employee_code,cw_transactions_fms_man.emp_name,cw_transactions_fms_man.man_pay_type,cw_transactions_fms_man.posting_month,cw_transactions_fms_man.audit_status,cw_transactions_fms_man.total_earnings,cw_transactions_fms_man.net_pay,$select_query,cw_transactions_fms_man.trans_created_date from $table_name $pick_query $trans_status $wbs_qry $process_qry $pay_type_qry";
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
if(empty($final_result)){
echo json_encode(array("status" => FALSE,"message" => "No Records Found.!","datatable" => FALSE,"table_head" => $table_head,"result_data" => $final_result));
}else{
echo json_encode(array("status" => TRUE,"message" => "Records are Displayed.!","datatable" => TRUE,"table_head" =>$table_head,"result_data" => $final_result));
}
}
//Update Posting Status and Approve reject status
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);
}
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$search_month = $this->input->post('search_month');
//$posting_month = $this->input->post('posting_month');
$remarks = $this->input->post('remarks');
$type = $this->input->post('type');
$process_mode = (int)$this->input->post('process_mode');
$pay_type = (int)$this->input->post('pay_type');
$filter_wbs = implode(",",$this->input->post('fil_wbs_element') ?? []);
$created_on = date("Y-m-d h:i:s");
$wbs_qry = "";
if($filter_wbs){
$fil_wbs_element = str_replace(',','","',$filter_wbs);
$wbs_qry = ' and cw_transactions_fms_man.wbs_element IN ("'.$fil_wbs_element.'") ';
}
//PAY TYPE BASED WYHERE CONDITION
$pay_type_qry = "";
if((int)$pay_type){
$pay_type_qry = ' and man_pay_type = "'.$pay_type.'" ';
}
//APPROVE PROCESS
if($type === 'approve'){ //,posting_month = "'.$posting_month.'"
$qry = 'audit_status = 1,remarks = "'.$remarks.'",msal_apr_date = "'.$created_on.'"';
}else{//REJECT PROCESS
$qry = 'audit_status = 2,remarks = "'.$remarks.'",msal_rej_date = "'.$created_on.'"';
}
$upd_qry = 'UPDATE cw_transactions_fms_man SET '.$qry.' where cw_transactions_fms_man.project_id = "'.$project.'" and personal_code = "'.$personal_code.'" and cw_transactions_fms_man.process_month = "'.$search_month.'" and audit_status = 0'.$wbs_qry.$pay_type_qry;
$update_info = $this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
if($update_info){
// $this->get_transaction_data($personal_code,$search_month,$project,$process_mode,$filter_wbs,$pay_type);
echo json_encode(array("status" => TRUE,"message" => "Audit Updated Successfully.!","datatable" => FALSE,"table_head" => array(),"result_data" => array()));
}else{
echo json_encode(array("status" => FALSE,"message" => "Please try After sometime..","datatable" => FALSE,"table_head" => array(),"result_data" => array()));
}
}
}
?>