File: /home/cafsindia/uds.cafsinfotech.in/application/controllers/Misc_input_fms.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Misc_input_fms extends Action_controller{
public function __construct(){
parent::__construct('misc_input_fms');
$this->prime_table = "cw_misc_input_fms";
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//VIEW INFO FUNCTION
// $this->page_info();
//PERSONAL AREAR 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;
$data['module_id'] = $this->control_name;
//FIND LABEL NAME OFR A WITH AND WITHOUT ESI BASED 21OCT22 START
// and field_show = "1"
$mi_inp_sel_qry = 'select label_name,with_esi from cw_form_setting where prime_module_id in ("misc_input_fms") and table_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) ORDER BY input_for,field_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_inp_arr = array();
foreach($mi_inp_sel_rslt as $arr){
$label_name = $arr->label_name;
$with_esi = $arr->with_esi;
$mi_inp_arr[$with_esi][$label_name] = $label_name;
}
$data['misc_esi_arr'] = $mi_inp_arr;
//FIND LABEL NAME OFR A WITH AND WITHOUT ESI BASED 21OCT22 START
$data['encKey'] = $this->generateKey();
$this->load->view('misc_input_fms/manage', $data);
}
//CHECK MONTHLY INPUT LOCKED OR NOT
public function check_misc_payment(){
$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');
$with_esi = $this->input->post('with_esi');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
if(!$this->table_head){
$this->misc_payment_info();
}
$data['table_head'] = $this->table_head;
if($search_month && $personal_code && $project){
echo json_encode(array('success' => true,'message' => "Proceed.!",'table_head'=>$this->table_head));
}else{
echo json_encode(array('success' => true,'message' => "Please Choose All Fields.!"));
}
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search(){
$dec_data = $this->cryptoDecrypt($_POST['encrypted_data']);
$_POST = $dec_data['data'];
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..'));
exit(0);
}
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col = $this->input->post('columns');
$search = $this->input->post('search');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_col = $order_col[$column]['data'];
$search = trim($search['value']);
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$access_data = $this->session->userdata('access_data');
$table = $this->input->post('table');
$action = $this->input->post('action');
$search_month = $this->input->post('search_month');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$with_esi = $this->input->post('with_esi');
$with_pf = $this->input->post('with_pf');
$filter_wbs_arr = $this->input->post('filter_wbs');
if($filter_wbs_arr <= 0 || $filter_wbs_arr == null){
$filter_wbs_arr = [];
}
$filter_wbs_str = implode(",",$filter_wbs_arr ?? []);
$filter_wbs = str_replace(',','","',$filter_wbs_str);
$filter_emp_code = $this->input->post('filter_emp_code');
$search_mon_date = date("Y-m-d",strtotime("01-".$search_month));
$add_query = "";
$emp_qry = "";
$add_column = "";
$label_name_arr = array();
$this->prime_table = "cw_misc_input_fms";
$filter_qry = "";
$start_date = date("Y-m-t",strtotime('01-'.$search_month));
$end_date = date("Y-m-d",strtotime('01-'.$search_month));
//MISC PAYMENT ESI BASED SELECT QRY COLUMNS GET CONDITIONS
$misc_esi_whr_qry = '';
if($table === "input_table"){ //MAKER TABLE SELECT COLUMNS QRY
$misc_esi_whr_qry = ' and with_esi in ("'.$with_esi.'","3")';
}
/*else //CHECKER TABLE SELECT QRY
if($table === "checker_table"){ //CHECKER TABLE SELECT COLUMNS QRY
$misc_esi_whr_qry = '';
}*/
//MISC PAYMENT INSERT SELECT FORMAT
$un_select_cols = '"employee_esi","employer_esi","employee_pf","employer_pf","eps","total_earnings","total_deductions","net_pay"';
// and field_show = "1"
$mi_inp_sel_qry = 'select label_name from cw_form_setting where prime_module_id = "misc_input_fms" and table_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for)'.$misc_esi_whr_qry.' and label_name not in ('.$un_select_cols.') ORDER BY input_for,table_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_inp_arr = array();
foreach($mi_inp_sel_rslt as $arr){
$label_name = $arr->label_name;
$mi_inp_arr[$label_name] = $label_name;
}
// $mi_ins_list = implode(',',$mi_inp_arr);
$mi_sel_list = 'cw_misc_input_fms.'.implode(',cw_misc_input_fms.',$mi_inp_arr ?? []);
$emp_inp_sel_qry = 'select label_name from cw_form_setting where prime_module_id in ("employees") and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and (earn_month_check = "1" OR deduction_month_check = "1") and label_name in ("employee_code","emp_name","personal_code","project_id","wbs_element","position","role","activity_no","termination_status","date_of_joining","date_of_birth") ORDER BY monthly_input_sort asc';
$emp_inp_sel_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_inp_sel_qry')");
$emp_inp_sel_rslt = $emp_inp_sel_data->result();
$emp_inp_sel_data->next_result();
$emp_inp_arr = array();
foreach($emp_inp_sel_rslt as $arr){
$label_name = $arr->label_name;
$emp_inp_arr[$label_name] = $label_name;
}
$emp_ins_list = implode(',',$emp_inp_arr ?? []);
$emp_sel_list = 'cw_misc_input_fms.'.implode(',cw_misc_input_fms.',$emp_inp_arr ?? []).',cw_misc_input_fms.payroll,cw_misc_input_fms.entry_status,cw_misc_input_fms.check_status,cw_misc_input_fms.input_status';
//FUNCTION FOR GET A PAY STRUCUTRE DATA BASED ON PERSONAL AREA AND PROJECT ID
$pay_struct_rslt = $this->pay_structure_qry_fun($search_month,$personal_code,$project,$start_date,$end_date);
$grp_wbs = str_replace(',','","',$pay_struct_rslt[0]->wbs_element);
$grp_position = str_replace(',','","',$pay_struct_rslt[0]->position);
//$grp_act_no = str_replace(',','","',$pay_struct_rslt[0]->activity_no);
//MISC PAYMENT INSERT CONDITION
if($action === "save" && $table === "input_table"){
if($search_month && $personal_code && $project) {
if($grp_wbs && $grp_position){
//Check misc input data exist Start
$misc_input_exist_qry = 'select employee_code from cw_misc_input_fms where cw_misc_input_fms.trans_status = 1 and process_month = "'.$search_month.'" and cw_misc_input_fms.personal_code = "'.$personal_code.'" and cw_misc_input_fms.project_id = "'.$project.'" and cw_misc_input_fms.with_esi = "'.$with_esi.'" and cw_misc_input_fms.with_pf = "'.$with_pf.'" and cw_misc_input_fms.check_status in (0,2) limit 0,1';
// and (cw_misc_input_fms.termination_status = 0 or cw_misc_input_fms.termination_status = 1)
$misc_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$misc_input_exist_qry')");
$misc_input_exist_rslt = $misc_input_exist_data->result();
$misc_input_exist_data->next_result();
$misc_input_exist_count = (int)$misc_input_exist_data->num_rows();
//Check misc input data exist END
// Zero Entry in misc input then insert data Start
//and cw_employees.activity_no in ("'.$grp_act_no.'")
if ($misc_input_exist_count === 0){
$save_month_fms_qry = 'INSERT INTO cw_misc_input_fms(employees_id,'.$emp_ins_list.',process_month,payroll,entry_status,check_status,with_esi,with_pf,trans_created_by,trans_created_date) SELECT prime_employees_id,'.$emp_ins_list.',"'.$search_month.'",1,0,0,"'.$with_esi.'","'.$with_pf.'","'.$logged_id.'","'.$today_date.'" FROM cw_employees WHERE cw_employees.trans_status = 1 and cw_employees.personal_code = "'.$personal_code.'" and cw_employees.project_id = "'.$project.'" and cw_employees.wbs_element in ("'.$grp_wbs.'") and cw_employees.position in ("'.$grp_position.'") and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m") <= DATE_FORMAT(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m") >= DATE_FORMAT(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m"))) and cw_employees.payroll = 1 and cw_employees.sap_status = 5';
$save_month_fms_info = $this->db->query("CALL sp_a_run ('INSERT','$save_month_fms_qry')");
$save_month_fms_rslt = $save_month_fms_info->result();
$save_month_fms_info->next_result();
}else{
//TO UPDATE ALL MI COLUMNS BASED ON EMP MASTER TABLE DATA
// echo "<pre>";
$mi_emp_upd_arr = explode(",",$emp_ins_list ?? "");
foreach($mi_emp_upd_arr as $val){
if($val !== "payroll"){
$mi_emp_upd_qry .= "misc_fms.".$val." = "."emp.".$val.",";
}
}
if($mi_emp_upd_qry){
$mi_emp_upd_qry = rtrim($mi_emp_upd_qry,",");
}
//----------- DON'T DELETE ------
//UPDATE QRY FOR IF ANY EMPLOYEE HAS CHANGED IN EMP MASTER IN WHICH CASE MISC TABLE ALSO UPDATE
$all_upd_mi_qry = 'UPDATE cw_misc_input_fms misc_fms INNER JOIN cw_employees emp ON (emp.employee_code = misc_fms.employee_code and emp.wbs_element = misc_fms.wbs_element and emp.position = misc_fms.position and emp.activity_no = misc_fms.activity_no) SET '.$mi_emp_upd_qry.',misc_fms.trans_updated_by = "'.$this->logged_id.'",misc_fms.trans_updated_date = "'.$today_date.'"
WHERE misc_fms.trans_status = 1 and misc_fms.process_month = "'.$search_month.'" and misc_fms.personal_code = "'.$personal_code.'" and misc_fms.project_id = "'.$project.'" and misc_fms.with_esi = "'.$with_esi.'" and misc_fms.with_pf = "'.$with_pf.'" and misc_fms.entry_status = "0"';
$all_upd_mi_info = $this->db->query("CALL sp_a_run ('UPDATE','$all_upd_mi_qry')");
//Insert and Update missing employees or new joiners
// and cw_employees.activity_no in ("'.$grp_act_no.'")
$missed_emp_qry = 'select GROUP_CONCAT(employee_code) as employee_codes from cw_employees where cw_employees.trans_status = 1 and cw_employees.personal_code = "'.$personal_code.'" and cw_employees.project_id = "'.$project.'" and cw_employees.wbs_element in ("'.$grp_wbs.'") and cw_employees.position in ("'.$grp_position.'") and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m") <= DATE_FORMAT(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m") >= DATE_FORMAT(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m"))) and cw_employees.payroll = 1 and cw_employees.sap_status = 5 and employee_code not in(SELECT cw_employees.employee_code from cw_employees INNER JOIN cw_misc_input_fms ON (cw_employees.employee_code = cw_misc_input_fms.employee_code and cw_employees.wbs_element = cw_misc_input_fms.wbs_element and cw_employees.position = cw_misc_input_fms.position and cw_employees.activity_no = cw_misc_input_fms.activity_no) WHERE cw_misc_input_fms.trans_status = 1 and cw_misc_input_fms.process_month = "'.$search_month.'" and cw_misc_input_fms.personal_code = "'.$personal_code.'" and cw_misc_input_fms.project_id = "'.$project.'" and cw_misc_input_fms.with_esi = "'.$with_esi.'" and cw_misc_input_fms.with_pf = "'.$with_pf.'" and cw_misc_input_fms.check_status in (0,2))';
// and cw_misc_input_fms.entry_status in ("0","2")
$missed_emp_data = $this->db->query("CALL sp_a_run ('SELECT','$missed_emp_qry')");
$missed_emp_rslt = $missed_emp_data->result();
$missed_emp_data->next_result();
$employee_codes = str_replace(',', '","', $missed_emp_rslt[0]->employee_codes);
if($missed_emp_rslt){
$save_month_fms_qry = 'INSERT INTO cw_misc_input_fms(employees_id,'.$emp_ins_list.',process_month,payroll,entry_status,check_status,with_esi,with_pf,trans_created_by,trans_created_date) SELECT prime_employees_id,'.$emp_ins_list.',"'.$search_month.'",1,0,0,"'.$with_esi.'","'.$with_pf.'","'.$logged_id.'","'.$today_date.'" FROM cw_employees WHERE cw_employees.trans_status = 1 and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m") <= DATE_FORMAT(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, "%Y-%m") >= DATE_FORMAT(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m"))) and cw_employees.payroll = 1 and employee_code in ("'.$employee_codes.'") and cw_employees.sap_status = 5';
$save_month_fms_info = $this->db->query("CALL sp_a_run ('INSERT','$save_month_fms_qry')");
$save_month_fms_rslt = $save_month_fms_info->result();
$save_month_fms_info->next_result();
}
}
}
}
}
//FOR FILTER BASED DATA GET WHERE QRY
if($filter_wbs){
$filter_qry = 'and '.$this->prime_table.'.wbs_element in ("'.$filter_wbs.'")';
}
$common_search = "";
if($search){
$common_search .= ' and (cw_misc_input_fms.personal_code like "'.$search.'%" or cw_misc_input_fms.project_id like "'.$search.'%" or cw_misc_input_fms.wbs_element like "'.$search.'%" or cw_misc_input_fms.activity_no like "'.$search.'%" or cw_misc_input_fms.employee_code like "'.$search.'%" or cw_misc_input_fms.emp_name like "'.$search.'%")';
}
//MAKER AND CHECKER BASED SELECT QRY BUILD
$tab_base_whr_qry = '';
$sel_esi_pf_qry = '';
if($table === "input_table"){ //MAKER TABLE SELECT QRY
$tab_base_whr_qry = ' and '.$this->prime_table.'.entry_status in (0,2) and '.$this->prime_table.'.with_esi = "'.$with_esi.'" and '.$this->prime_table.'.with_pf = "'.$with_pf.'"';
}else //CHECKER TABLE SELECT QRY
if($table === "checker_table"){
$tab_base_whr_qry = ' and '.$this->prime_table.'.entry_status = 1 and '.$this->prime_table.'.check_status in (0,1)';
}
// else
// if($table === 'calc_table'){
// if($with_esi === 1){
// $sel_esi_pf_qry = ',cw_misc_input_fms.employee_esi,cw_misc_input_fms.employer_esi';
// }
// if($with_pf === 1){
// $sel_esi_pf_qry .= ',cw_misc_input_fms.employee_pf,cw_misc_input_fms.employer_pf';
// }
// }
//and $this->prime_table.activity_no in (\"".$grp_act_no."\")
$count_query = "select count(*) as allcount from $this->prime_table inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code where $this->prime_table.trans_status = 1 and $this->prime_table.process_month =\"".$search_month."\" ".$filter_qry.$common_search.$tab_base_whr_qry." and $this->prime_table.personal_code = \"".$personal_code."\" and $this->prime_table.project_id = \"".$project."\" and $this->prime_table.wbs_element in (\"".$grp_wbs."\") and $this->prime_table.position in (\"".$grp_position."\") and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, '%Y-%m') >= DATE_FORMAT(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m'))) order by $this->prime_table.$order_col $order_sor";
// and $this->prime_table.with_esi = \"".$with_esi."\"
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
// ,cw_misc_input_fms.with_esi,cw_misc_input_fms.with_pf,cw_misc_input_fms.total_earnings,cw_misc_input_fms.total_deductions,cw_misc_input_fms.net_pay,cw_misc_input_fms.remarks
//As discussed with porul on 28sep2023 and cw_pay_structure.activity_no = $this->prime_table.activity_no
//and $this->prime_table.activity_no in (\"".$grp_act_no."\")
$search_query = "SELECT cw_misc_input_fms.prime_misc_input_fms_id,$emp_sel_list,$mi_sel_list,cw_misc_input_fms.total_earnings,cw_misc_input_fms.total_deductions,cw_misc_input_fms.net_pay,cw_pay_structure.gross,CONCAT(cw_pay_structure.position,'~',cw_sap_position.position_name,'~',cw_pay_structure.gross) as position FROM $this->prime_table inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code inner join cw_pay_structure on (cw_pay_structure.personal_code = $this->prime_table.personal_code and cw_pay_structure.wbs_element = $this->prime_table.wbs_element and cw_pay_structure.position = $this->prime_table.position and date_format(cw_pay_structure.from_date, '%Y-%m') <= date_format(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m') and date_format(cw_pay_structure.to_date, '%Y-%m') >= date_format(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m') and cw_pay_structure.trans_status = 1) inner join cw_sap_position ON cw_pay_structure.position = cw_sap_position.position_code where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" ".$filter_qry. $common_search.$tab_base_whr_qry." and $this->prime_table.personal_code = \"".$personal_code."\" and $this->prime_table.project_id = \"".$project."\" and $this->prime_table.wbs_element in (\"".$grp_wbs."\") and $this->prime_table.position in (\"".$grp_position."\") and (cw_employees.termination_status = 0 or (cw_employees.termination_status = 1 and DATE_FORMAT(cw_employees.resignation_date, '%Y-%m') >= DATE_FORMAT(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m'))) order by $this->prime_table.$order_col $order_sor";
// and $this->prime_table.with_esi = \"".$with_esi."\"
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
// echo "<pre> mi_sel_list => $mi_sel_list <br/> search_query => $search_query <br/>";
// print_r($search_result);die;
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $num_rows,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
//PAY STRUCTURE QRY FUNCTION
public function pay_structure_qry_fun($search_month,$personal_code,$project,$start_date,$end_date){
$pay_struct_qry = 'select GROUP_CONCAT(DISTINCT cw_pay_structure.wbs_element) as wbs_element,GROUP_CONCAT(DISTINCT cw_pay_structure.position) as position,GROUP_CONCAT(DISTINCT cw_pay_structure.activity_no) as activity_no from cw_pay_structure inner join cw_sap_wbs on cw_pay_structure.wbs_element = cw_sap_wbs.wbs_id inner join cw_sap_activity on cw_pay_structure.activity_no = cw_sap_activity.prime_sap_activity_id where cw_pay_structure.trans_status = 1 and date_format(cw_pay_structure.from_date, "%Y-%m") <= date_format(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and date_format(cw_pay_structure.to_date, "%Y-%m") >= date_format(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and cw_pay_structure.personal_code = "'.$personal_code.'" and cw_sap_wbs.wbs_start_date <= "'.$start_date.'" and cw_sap_wbs.wbs_end_date >= "'.$end_date.'" and cw_sap_wbs.wbs_project_id = "'.$project.'" and cw_sap_activity.act_start_date <= "'.$start_date.'" and cw_sap_activity.act_end_date >= "'.$end_date.'"';
$pay_struct_data = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_qry')");
$pay_struct_rslt = $pay_struct_data->result();
$pay_struct_data->next_result();
return $pay_struct_rslt;
}
public function clear_table_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');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$wbs_element = $this->input->post('wbs_element');
$with_esi = $this->input->post('with_esi');
$with_pf = $this->input->post('with_pf');
$created_on = date("Y-m-d H:i:s");
$sel_whre_qry = '';
if(!empty($wbs_element)){
$wbs_element = implode('","',$wbs_element ?? []);
$sel_whre_qry = ' and cw_misc_input_fms.wbs_element in ("'.$wbs_element.'")';
}
//MISC PAYMENT INSERT SELECT FORMAT
$mi_inp_sel_qry = 'select label_name from cw_form_setting where prime_module_id = "misc_input_fms" and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and with_esi in ("'.$with_esi.'","3")';
$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_array();
$mi_inp_sel_data->next_result();
$mi_inp_upd_col = implode(',', array_map(function ($column) {
$col_add_qry .= 'cw_misc_input_fms.'.$column["label_name"].' = "0.00"';
return $col_add_qry;
}, $mi_inp_sel_rslt ?? []) ?? []);
$mi_inp_upd_col = $mi_inp_upd_col.',entry_status = 0,trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'"';
$mi_check_qry = 'SELECT count(cw_misc_input_fms.prime_misc_input_fms_id) as count FROM cw_misc_input_fms WHERE cw_misc_input_fms.trans_status = 1 and cw_misc_input_fms.process_month = "' .$search_month. '" and cw_misc_input_fms.personal_code = "'.$personal_code.'" and cw_misc_input_fms.project_id = "'.$project.'" and cw_misc_input_fms.entry_status != 1 and cw_misc_input_fms.with_esi = "'.$with_esi.'" and cw_misc_input_fms.with_pf = "'.$with_pf.'" '.$sel_whre_qry;
// and cw_misc_input_fms.entry_status = 0
$mi_check_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_check_qry')");
$mi_check_rslt = $mi_check_data->result();
$mi_check_data->next_result();
$mi_inp_count = (int)$mi_check_rslt[0]->count;
//UPDATE QUERY FOR ALL DECIMAL INPUT ARE UPDATE TO ZERO
if($mi_inp_count > 0){
$upd_query = 'UPDATE cw_misc_input_fms SET '.$mi_inp_upd_col.' WHERE cw_misc_input_fms.trans_status = 1 and cw_misc_input_fms.process_month = "' .$search_month. '" and cw_misc_input_fms.personal_code = "'.$personal_code.'" and cw_misc_input_fms.project_id = "'.$project.'" and cw_misc_input_fms.entry_status = 2 and cw_misc_input_fms.with_esi = "'.$with_esi.'" and cw_misc_input_fms.with_pf = "'.$with_pf.'" '.$sel_whre_qry;
// and cw_misc_input_fms.entry_status = 0
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
echo json_encode(array('success' => TRUE,'message' => "Updated successfully..!"));
}else{
echo json_encode(array('success' => FALSE,'message' => "No Data Available to Clear..!"));
}
}
//DR CODE FOR MISC CALCULATION TAB FUNCTION 22NOV22 START
public function calc_entry_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);
}
$created_on = date('Y-m-d H:i:s');
$search_month = $this->input->post('process_month');
$table_data = $this->input->post('results');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$with_esi = (int)$this->input->post('with_esi');
$with_pf = (int)$this->input->post('with_pf');
$filter_wbs_str = implode(",",$this->input->post('filter_wbs') ?? []);
$filter_wbs = str_replace(',','","',$filter_wbs_str);
$filter_qry = '';
$sel_esi_pf_qry = '';
//TABLE UPDATE ONINPUT ->NB[05-10-23]
$success_count = 0;
$keys = '';
unset($table_data[0]); // FOR REMOVE FIRST
unset($table_data[count($table_data)]); // FOR REMOVE LAST
$first_array_key = reset($table_data);
$keys = implode(',', array_keys($first_array_key ?? []) ?? []);
$value_ins = "";
$value_upd = "";
//FOR KEY CONCATATION -> UPDATE
foreach($first_array_key as $key => $val){
if($key !== 'prime_misc_input_fms_id'){
$value_upd .= ''.$key.' = '.'IF(check_status = 0 or check_status = 2,VALUES'.'('.$key.')'.','.$key.')'.',';
}
}
//GENERATING QRY FOR UPDATE !
foreach($table_data as $value){
$all_zero = true;
$value_ins .= '(';
foreach($value as $subkey => $subval){
$value_ins .= '"'.$subval.'",';
if($subval != 0 && $subkey !== 'prime_misc_input_fms_id'){
$all_zero = false;
}
}
$entry_status = $all_zero ? 0 : 2;
$check_status = $all_zero ? 0 : 0;
$value_ins .= '"' . $entry_status . '","' . $check_status . '","' . $this->logged_id . '","' . $created_on . '"';
$value_ins = rtrim($value_ins,",");
$value_ins .= '),';
}
$value_upd .= "entry_status = IF(check_status =0 or check_status = 2,VALUES(entry_status),entry_status),check_status = IF(check_status =0 or check_status = 2,VALUES(check_status),check_status),trans_updated_by = IF(check_status = 0 or check_status = 2,VALUES(trans_updated_by),trans_created_by),trans_updated_date = IF(check_status = 0 or check_status = 2,VALUES(trans_created_date),trans_created_by),";
$value_ins = rtrim($value_ins,",");
$value_upd = rtrim($value_upd,",");
if($value_upd){
if($keys){
$bulk_upd_qry = 'INSERT INTO '.$this->prime_table.'('.$keys.',entry_status,check_status,trans_created_by,trans_created_date) VALUES '.$value_ins.' ON DUPLICATE KEY UPDATE '.$value_upd.' ';
$bulk_upd_info = $this->db->query($bulk_upd_qry);
}
if(!$bulk_upd_info){
echo json_encode(array("success" => FALSE,"message" => "Records Not Updated.!","table_info" => '','export_pick' => ''));
exit(0);
}
}
//MISC INFO CALL FUNCTION
$this->misc_payment_info();
$table_head_rslt = $this->table_head;
$pick_list_arr = $this->pick_list;
$export_pick = $this->export_pick_arr;
$table_head_arr = array();
$ear_ded_col_arr = array();
foreach($table_head_rslt as $arr){
$table_head_arr[$arr->label_name] = $arr->view_name;
$transaction_type = (int)$arr->transaction_type;
//EARNINGS AND DEDUCTIONS COLUMNS ONLY PUSH
if($transaction_type === 2 || $transaction_type === 3){
$ear_ded_col_arr[$arr->label_name] = $arr->transaction_type;
}
}
//IF WE FILTER A WBS THEN WE SHOULD ADD A WBS WHERE QRY
if($filter_wbs){
$filter_qry = 'and '.$this->prime_table.'.wbs_element in ("'.$filter_wbs.'")';
}
if($with_esi === 1){
$sel_esi_pf_qry = ',cw_misc_input_fms.employee_esi,cw_misc_input_fms.employer_esi';
}
if($with_pf === 1){
$sel_esi_pf_qry .= ',cw_misc_input_fms.employee_pf,cw_misc_input_fms.employer_pf,cw_misc_input_fms.eps';
}
$save_info = $this->db->query("CALL itsp_misc_payment('$personal_code','$project','$search_month')");
$save_result = $save_info->result();
$save_info->next_result();
if($save_result){
//this default columns only show for calculation entry table not to all so we should not take in query
$un_select_cols = '"employee_esi","employer_esi","employee_pf","employer_pf","eps","total_earnings","total_deductions","net_pay"';
// and field_show = "1"
$mi_inp_sel_qry = 'select label_name from cw_form_setting where prime_module_id = "misc_input_fms" and table_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and with_esi in ("'.$with_esi.'","3") and label_name not in ('.$un_select_cols.') ORDER BY input_for,table_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_inp_arr = array();
foreach($mi_inp_sel_rslt as $arr){
$label_name = $arr->label_name;
$mi_inp_arr[$label_name] = $label_name;
}
$mi_sel_list = 'cw_misc_input_fms.'.implode(',cw_misc_input_fms.',$mi_inp_arr ?? []);
$emp_inp_sel_qry = 'select label_name from cw_form_setting where prime_module_id in ("employees") and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and label_name in ("employee_code","emp_name","personal_code","project_id","wbs_element","position","role","activity_no","termination_status") ORDER BY monthly_input_sort asc';
// ,"date_of_joining","date_of_birth"
$emp_inp_sel_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_inp_sel_qry')");
$emp_inp_sel_rslt = $emp_inp_sel_data->result();
$emp_inp_sel_data->next_result();
$column_arr = array();
foreach($emp_inp_sel_rslt as $arr){
$label_name = $arr->label_name;
$column_arr[$label_name] = $label_name;
}
$emp_sel_list = 'cw_misc_input_fms.'.implode(',cw_misc_input_fms.',$column_arr ?? []).',cw_misc_input_fms.payroll,cw_misc_input_fms.entry_status,cw_misc_input_fms.check_status,cw_misc_input_fms.input_status';
// ,cw_misc_input_fms.with_esi,cw_misc_input_fms.with_pf,cw_misc_input_fms.total_earnings,cw_misc_input_fms.total_deductions,cw_misc_input_fms.net_pay,cw_misc_input_fms.remarks
//As discussed with porul on 28sep2023 and cw_pay_structure.activity_no = $this->prime_table.activity_no
$search_query = "SELECT cw_misc_input_fms.prime_misc_input_fms_id,$emp_sel_list,$mi_sel_list"."$sel_esi_pf_qry,cw_misc_input_fms.total_earnings,cw_misc_input_fms.total_deductions,cw_misc_input_fms.net_pay,cw_pay_structure.gross FROM $this->prime_table inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code inner join cw_pay_structure on (cw_pay_structure.personal_code = $this->prime_table.personal_code and cw_pay_structure.wbs_element = $this->prime_table.wbs_element and cw_pay_structure.position = $this->prime_table.position and date_format(cw_pay_structure.from_date, '%Y-%m') <= date_format(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m') and date_format(cw_pay_structure.to_date, '%Y-%m') >= date_format(str_to_date('01-".$search_month."', '%d-%m-%Y') , '%Y-%m') and cw_pay_structure.trans_status = 1) where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" ".$filter_qry." and $this->prime_table.personal_code = \"".$personal_code."\" and $this->prime_table.project_id = \"".$project."\" and $this->prime_table.entry_status = 2 and $this->prime_table.with_esi = \"".$with_esi."\" and cw_misc_input_fms.with_pf = \"".$with_pf."\" order by $this->prime_table.prime_misc_input_fms_id ASC";
$search_data = $this->db->query($search_query);
$search_result = $search_data->result_array();
$search_data->next_result();
if(!count($search_result[0] ?? [])){
echo json_encode(array("success" => FALSE,"message" => "Calculations Data not Available..!","table_info" => '','export_pick' => ''));
}else{
//TABLE CREATION START
//without decimal inputs get from misc input fms module
// and field_show = "1
$mi_pick_inp_qry = 'select label_name,field_type from cw_form_setting where prime_module_id = "misc_input_fms" and table_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) and with_esi in ("'.$with_esi.'","3") and field_type <> 2 ORDER BY input_for,table_sort asc';
$mi_pick_inp_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_pick_inp_qry')");
$mi_pick_inp_rslt = $mi_pick_inp_data->result_array();
$mi_pick_inp_data->next_result();
$mi_pick_inp_arr = array();
foreach($mi_pick_inp_rslt as $pick_inp_key => $pick_inp_value){
$mi_field_type = $pick_inp_value['field_type'];
$mi_inp_name = $pick_inp_value['label_name'];
$mi_pick_inp_arr[$mi_inp_name] = $mi_inp_name;
}
//for table header and footer arrays
$mi_cal_inp_arr = array();
$footer_sum_arr = array();
foreach($search_result as $key => $value){
foreach($mi_inp_arr as $mi_key => $mi_label){
//condition for only decimal fields get
if(!in_array($mi_label,$mi_pick_inp_arr)){
$mi_val = number_format((float)$value[$mi_label], 2, '.', '');
if($mi_val > 0.00 && !$column_arr[$mi_label]){
$mi_cal_inp_arr[$mi_label] = $mi_label;
}
//for footer sum array
$footer_sum_arr[$mi_label] += $mi_val;
}
}
//for footer sum array
if($with_esi === 1){
$footer_sum_arr['employee_esi'] += $value['employee_esi'];
$footer_sum_arr['employer_esi'] += $value['employer_esi'];
}
if($with_pf === 1){
$footer_sum_arr['employee_pf'] += $value['employee_pf'];
$footer_sum_arr['employer_pf'] += $value['employer_pf'];
$footer_sum_arr['eps'] += $value['eps'];
}
$footer_sum_arr['total_earnings'] += $value['total_earnings'];
$footer_sum_arr['total_deductions'] += $value['total_deductions'];
$footer_sum_arr['net_pay'] += $value['net_pay'];
}
// GET TABLE ORDER BASED DECIMAL ARRAY (NOT 0 VALUE DECIMAL COLUMNS(only get calculated inputs))
$mi_cal_inp_arr = array_intersect($mi_inp_arr,$mi_cal_inp_arr);
//0 VALUE COLUMN GET ARRAY
$mi_remove_arr = array_diff($mi_inp_arr,$mi_cal_inp_arr,$mi_pick_inp_arr,$column_arr);
//In search result data to remove a mi remove arr columns
$search_result_arr = array();
foreach($search_result as $arr){
$filtered_arr = array_diff_key($arr, $mi_remove_arr);
$search_result_arr[] = $filtered_arr;
}
//FOOTER ARRAY
$footer_col_arr = array_diff($mi_inp_arr,$mi_remove_arr);
//for in footer to remove without decimal fields
$footer_col_arr = array_diff($footer_col_arr,$mi_pick_inp_arr);
$status = array(1=>"Yes",2=>"No",);
// $front_heads = array('prime_misc_input_fms_id' => 'Prime ID','with_esi'=>"With ESI",'with_pf'=>"With PF",);
$front_heads = array('prime_misc_input_fms_id' => 'Prime ID');
// $back_heads = array('with_esi'=>"With ESI",'with_pf'=>"With PF",);
$esi_heads = array();
if($with_esi === 1){
$esi_heads = array('employee_esi'=>"Employee Esi",'employer_esi'=>"Employer Esi",);
}
$pf_heads = array();
if($with_pf === 1){
$pf_heads = array('employee_pf'=>"Employee Pf",'employer_pf'=>"Employer Pf",'eps'=>"FPF",);
}
$static_heads = array('total_earnings'=>"Total Earnings",'total_deductions'=>"Total Deductions",'net_pay'=>"Net Pay",);
// ,$back_heads
$display_labels = array_merge($front_heads,$column_arr,$mi_cal_inp_arr,$esi_heads,$pf_heads,$static_heads,$mi_pick_inp_arr);
$footer_col_arr = array_merge($footer_col_arr,$esi_heads,$pf_heads,$static_heads);
$hide_column = ['role' => 'role','date_of_birth' => 'date_of_birth','date_of_joining' => 'date_of_joining','process_month' => 'process_month','termination_status' => 'termination_status','entry_status' => 'entry_status','personal_code' => 'personal_code','project_id' => 'project_id','activity_no' => 'activity_no'];
$head_sts = 1;
$esi_sts = 1;
$pf_sts = array_column($search_result_arr ?? [],'with_pf');
//Generate table data
$tr_line = "";
//LOOP START FOR CREATE A TABLE STRUCTURE
foreach ($search_result_arr as $key => $value_data){
$tr_line .= "<tr>";
$foot_line = "";
foreach($display_labels as $label_name => $val){
$value = $value_data[$label_name];
// if($label_name === 'with_esi' || $label_name === 'with_pf'){
// $value = $status[$value];
// }
if(!$hide_column[$label_name]){
if($pick_list_arr[$label_name] && $label_name !== 'wbs_element'){ //Fill Picklist Data
$value = $pick_list_arr[$label_name]['array_list'][$value];
//for position field
if($label_name === 'position'){
$gross = " ~ ".$value_data['gross'];
$value = $value.$gross;
}
}
if($table_head_arr[$label_name]){ //Replace View Name
$view_name = $table_head_arr[$label_name];
if($head_sts === 1){
//class name add for only to ear and ded cols
$class = '';
if((int)$ear_ded_col_arr[$label_name] === 2){
$class = "class = 'earn'";
}else
if((int)$ear_ded_col_arr[$label_name] === 3){
$class = "class = 'deduct'";
}
$tr_head .= "<th $class>$view_name</th>";
}
$tr_line .= "<td>$value</td>";
}else
if($front_heads[$label_name]){ //Replace View Name
$view_name = $front_heads[$label_name];
if($head_sts === 1){
if($label_name === 'prime_misc_input_fms_id'){
$tr_head .= "<th></th>";
}else{
$tr_head .= "<th>$view_name</th>";
}
}
if($label_name === 'prime_misc_input_fms_id'){
$tr_line .= "<td><input type='checkbox' value='".$value."' name='select_one' class='select_one' id = 'calc_check' data-id = 'check_$i'></td>";
}else{
$tr_line .= "<td>$value</td>";
}
}
/*else
if($back_heads[$label_name]){ //Replace View Name
$view_name = $back_heads[$label_name];
if($head_sts === 1){
$tr_head .= "<th>$view_name</th>";
}
$tr_line .= "<td>$value</td>";
}*/
else
if($static_heads[$label_name]){ //Replace View Name
$view_name = $static_heads[$label_name];
if($head_sts === 1){
$tr_head .= "<th>$view_name</th>";
}
$tr_line .= "<td>$value</td>";
}else
if($esi_heads[$label_name]){ //Replace View Name
if($with_esi === 1){
if($esi_sts === 1){
$view_name = $esi_heads[$label_name];
$tr_head .= "<th>$view_name</th>";
}
if(!$value){
$value = '0.00';
}
$tr_line .= "<td>$value</td>";
}
}else
if($pf_heads[$label_name]){ //Replace View Name
if(in_array("1",$pf_sts)){
if($head_sts === 1){
$view_name = $pf_heads[$label_name];
$tr_head .= "<th>$view_name</th>";
}
if(!$value){
$value = '0.00';
}
$tr_line .= "<td>$value</td>";
}
}
if($footer_col_arr[$label_name]){
if($esi_heads[$label_name]){
if($with_esi === 1){
$sum_value = $footer_sum_arr[$label_name];
$foot_line .= "<td>".number_format((float)$sum_value, 2, '.', '')."</td>";
}
}else
if($pf_heads[$label_name]){
if(in_array("1",$pf_sts)){
$sum_value = $footer_sum_arr[$label_name];
$foot_line .= "<td>".number_format((float)$sum_value, 2, '.', '')."</td>";
}
}else{
$sum_value = $footer_sum_arr[$label_name];
$foot_line .= "<td>".number_format((float)$sum_value, 2, '.', '')."</td>";
}
}else{
if($label_name === 'emp_name'){
$foot_line .= "<td style='text-align:right;'>Grand Total :</td>";
}else{
$foot_line .= "<td></td>";
}
}
}
}
$tr_line .= "</tr>";
$head_sts++;
$esi_sts++;
}
//TABLE CREATE
$table_info = "<table class='table-hover display' id='calc_table'><thead><tr>$tr_head</tr></thead><tbody>$tr_line <tr style='background-color:#e4e3e3;font-weight:bold;color:blue;'>$foot_line</tr></tbody></table>";
echo json_encode(array("success" => TRUE,"message" => "Calculations Added Successfully","table_info" => $table_info,'export_pick' => $export_pick));
}
}else{
echo json_encode(array("success" => FALSE,"message" => "Try After Sometime..!","table_info" => '','export_pick' => ''));
}
}
//DR CODE FOR MISC CALCULATION TAB FUNCTION 22NOV22 END
//DR CODE START FOR SAVE ENTRY(MAKER) STATUS TO MISC FMS TABLE 31AUG22
public function save_entry_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);
}
$created_on = date('Y-m-d H:i:s');
$personal_code = $this->input->post('personal_code');
$project = $this->input->post('project');
$process_month = $this->input->post('process_month');
$filter_wbs_str = implode(",",$this->input->post('filter_wbs') ?? []);
$filter_wbs = str_replace(',','","',$filter_wbs_str);
$filter_qry = '';
$prime_ids = implode(",",$this->input->post('prime_id_arr') ?? []);
//IF WE FILTER A WBS THEN WE SHOULD ADD A WBS WHERE QRY
if($filter_wbs){
$filter_qry = 'and '.$this->prime_table.'.wbs_element in ("'.$filter_wbs.'")';
}
//UPDATE QRY BASED ON ENTRY(MAKER) STATUS 2 TO ENTRY(MAKER) STATUS SHOULD 1
$upd_fms_query = 'UPDATE cw_misc_input_fms SET cw_misc_input_fms.entry_status = "1",cw_misc_input_fms.check_status = "0",cw_misc_input_fms.trans_updated_by = "'.$this->logged_id.'",cw_misc_input_fms.trans_updated_date = "'.$created_on.'",cw_misc_input_fms.sub_date = "'.$created_on.'" WHERE cw_misc_input_fms.personal_code = "'.$personal_code.'" and cw_misc_input_fms.project_id = "'.$project.'" and cw_misc_input_fms.process_month = "'.$process_month.'"'.$filter_qry.' and cw_misc_input_fms.entry_status = 2 and (cw_misc_input_fms.net_pay > 0 or cw_misc_input_fms.net_pay > 0.00) and cw_misc_input_fms.prime_misc_input_fms_id in ('.$prime_ids.') and cw_misc_input_fms.trans_status = 1';
$upd_fms_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_fms_query')");
if($upd_fms_info){
echo json_encode(array('success' => TRUE,'message' => 'Successfully Data are Moved to Checker...!'));
}else{
echo json_encode(array('success' => TRUE,'message' => 'Data are not Moved to Checker...!'));
}
}
//DR CODE END FOR SAVE ENTRY(MAKER) STATUS TO MI FMS TABLE 31AUG22
//UPDATE STATUS TO DELETE FOR UPLOAD FILES or DOCUMENTS
public function remove_file(){
//Encryption
$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_val = $this->input->post('prime_id_val');
$is_defult = (int)$this->input->post('is_defult');
$input_name = $this->input->post('input_name');
$input_val = $this->input->post('input_val');
if($input_val){
chmod($input_val, 0777);
unlink($input_val);
}
$table_name = '';
if($is_defult === 1){
$table_name = $this->prime_table;
}else
if($is_defult === 2){
$table_name = $this->cf_table;
}
if($table_name){
$created_on = date("Y-m-d h:i:s");
$set_query = $input_name .' = "" ,trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE '.$table_name .' SET '. $set_query .' WHERE '. $this->prime_id .' = "'. $prime_id_val .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
/* ==============================================================*/
/* ============ MONHTLY IMPORT OPERATION - START ================*/
/* ==============================================================*/
//DR CODE START FOR PROJECT ID FETCH 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');
//FOR ACTIVE PROJECT ONLY SHOULD SHOW IN PICKLIST
$start_date = date("Y-m-t",strtotime('01-'.$search_month));
$end_date = date("Y-m-d",strtotime('01-'.$search_month));
$pro_id_qry = 'SELECT pro_id,pro_desc FROM `cw_sap_project` where cw_sap_project.pro_personal_area_id = "'.$personal_code.'" and cw_sap_project.pro_start_date <= "'.$start_date.'" and cw_sap_project.pro_end_date >= "'.$end_date.'" and cw_sap_project.trans_status = 1';
$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);
}
//DR CODE END FOR PROJECT ID FETCH BASED ON PERSONAL CODE
//DR CODE START FOR WBS ELEMENT FILTER FETCH 29AUG22
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');
//FOR ACTIVE PROJECT ONLY SHOULD SHOW IN PICKLIST
$start_date = date("Y-m-t",strtotime('01-'.$search_month));
$end_date = date("Y-m-d",strtotime('01-'.$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_pay_structure on cw_pay_structure.wbs_element = cw_sap_wbs.wbs_id where cw_pay_structure.personal_code = "'.$personal_code.'" and cw_sap_wbs.wbs_personal_area_id = "'.$personal_code.'" and cw_sap_wbs.wbs_project_id = "'.$project.'" and date_format(cw_pay_structure.from_date, "%Y-%m") <= date_format(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and date_format(cw_pay_structure.to_date, "%Y-%m") >= date_format(str_to_date("01-'.$search_month.'", "%d-%m-%Y") , "%Y-%m") and cw_sap_wbs.trans_status = 1 and cw_pay_structure.trans_status = 1 and cw_sap_wbs.wbs_start_date <= "'.$start_date.'" and cw_sap_wbs.wbs_end_date >= "'.$end_date.'" GROUP BY cw_sap_wbs.prime_sap_wbs_id';
// and cw_pay_structure.status = 1
$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);
}
public function update_table(){
$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);
}
$label_name = $this->input->post('label_name');
$value = $this->input->post('value');
$prime_id = $this->input->post('prime_id');
$process_month = $this->input->post('hid_process_month');
$hid_process_month = date("Y-m-d", strtotime("01-" . $this->input->post('hid_process_month')));
$employee_code = $this->input->post('hid_emp_code');
$tab_name = $this->input->post('tab_name');
$payroll = (int)$this->input->post('payroll');
$this->prime_table = "cw_misc_input_fms";
$this->prime_id = "prime_misc_input_fms_id";
//EXIST CHECK CONDITION
if($prime_id){
// and '.$this->prime_table.'.check_status = 2
// and '.$this->prime_table.'.payroll_status in (0,2)
// and '.$this->prime_table.'.check_status in (0,2)
$month_input_qry = 'select * from '.$this->prime_table.' where '.$this->prime_table.'.trans_status = 1 and '.$this->prime_table.'.process_month = "' . $process_month . '" and '.$this->prime_table.'.entry_status in (0,2) and '.$this->prime_table.'.input_status = 2 and '.$this->prime_table.'.prime_misc_input_fms_id = "'.$prime_id.'"';
$month_input_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_qry')");
$month_input_rslt = $month_input_data->result_array();
$month_input_data->next_result();
if(!$month_input_rslt[0]){
echo json_encode(array("success" => FALSE,'message' => 'Should not Update..!Because Maker Status was Already Approved..!'));
exit(0);
}else{
$created_on = date("Y-m-d H:i:s");
$upd_data = '';
$upd_data = ',entry_status = 2,input_status = 2,trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'"';
$upd_query = 'UPDATE ' . $this->prime_table . ' SET ' . $label_name . ' = "' . $value . '" '.$upd_data.' WHERE ' . $this->prime_id . ' = "' . $prime_id . '"';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
echo json_encode(array(
'success' => true,
'message' => "Updated successfully!!!"
));
}
}
}
}
?>