File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Pay_structure_static.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Pay_structure extends Action_controller{
public function __construct(){
parent::__construct('pay_structure');
// $this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['quick_link'] = $this->quick_link;
$data['table_head'] = $this->table_head;
$data['master_pick'] = $this->master_pick;
$data['fliter_list'] = $this->fliter_list;
$data['freeze_list'] = $this->freeze_list;
$select_columns = '*';
$table_name = 'cw_sap_personal_area';
$table_join = '';
$table_where = ' cw_sap_personal_area.trans_status = 1';
$personal_area_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$per_area_list[""] = "---- Personal Area List ----";
foreach($personal_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;
$select_columns = '*';
$table_name = 'cw_sap_wbs';
$table_join = '';
$table_where = ' cw_sap_wbs.trans_status = 1';
$personal_area_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$wbs_list[""] = "---- WBS List ----";
foreach($personal_area_rslt as $for){
$prime_wbs_id = $for['prime_sap_wbs_id'];
$wbs_id = $for['wbs_id'];
$wbs_list[$prime_wbs_id] = $wbs_id;
}
$data['wbs_element_list'] = $wbs_list;
//select query for get a position
$select_columns = 'cw_sap_position.position_code,cw_sap_position.position_name';
$table_name = 'cw_sap_position';
$table_join = '';
$table_where = 'cw_sap_position.trans_status = 1';
$position_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$position_list[""] = "---- Personal Area List ----";
foreach($position_rslt as $for){
$position_code = $for['position_code'];
$position_name = $for['position_name'];
$position_list[$position_code] = $position_code.'-'.$position_name;
}
$data['position_list'] = $position_list;
$select_columns = '*';
$table_name = 'cw_category';
$table_join = '';
$table_where = ' cw_category.trans_status = 1';
$category_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$category_list = "<option value = ''>---- Category List ----</option>";
$category_arr = array();
foreach($category_rslt as $for){
$prime_id = $for['prime_category_id'];
$category_name = $for['category_name'];
$category_list .= "<option value = '$prime_id'>$category_name</option>";
$category_arr[$category_name] = $prime_id;
}
$data['category_list'] = $category_list;
$data['category_arr'] = $category_arr;
//select query for get a day condition
$select_columns = 'cw_day_condition.prime_day_condition_id,cw_day_condition.day_condition';
$table_name = 'cw_day_condition';
$table_join = '';
$table_where = 'cw_day_condition.trans_status = 1';
$day_cond_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$day_cond_list = "<option value = ''>---- Day Condition List ----</option>";
$day_cond_arr = array();
foreach($day_cond_rslt as $for){
$prime_day_id = $for['prime_day_condition_id'];
$day_condition = $for['day_condition'];
$day_cond_list .= "<option value = '$prime_day_id'>$day_condition</option>";
$day_cond_arr[$day_condition] = $prime_day_id;
}
$data['day_cond_list'] = $day_cond_list;
$data['day_cond_arr'] = $day_cond_arr;
//select query for get a day count
$select_columns = 'cw_days.day_id,cw_days.day';
$table_name = 'cw_days';
$table_join = '';
$table_where = 'cw_days.trans_status = 1';
$day_count_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$day_count_list = "<option value = ''>---- Day Count List ----</option>";
$day_count_arr = array();
foreach($day_count_rslt as $for){
$prime_day_id = $for['day_id'];
$day = $for['day'];
$day_count_list .= "<option value = '$prime_day_id'>$day</option>";
$day_count_arr[$day] = $prime_day_id;
}
$data['day_count_list'] = $day_count_list;
$data['day_count_arr'] = $day_count_arr;
$select_columns = '*';
$table_name = 'cw_status_mode';
$table_join = '';
$table_where = ' cw_status_mode.trans_status = 1';
$status_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$status_list = "<option value = ''>----Select Status ----</option>";
$status_arr = array();
foreach($status_rslt as $for){
$prime_id = $for['prime_status_mode_id'];
$status_mode = $for['status_mode_value'];
$status_list .= "<option value = '$prime_id'>$status_mode</option>";
$status_arr[$status_mode] = $prime_id;
}
$data['status_list'] = $status_list;
$data['status_arr'] = $status_arr;
$this->load->view("$this->control_name/manage",$data);
}
//PROVIDE ALL SINGLE BOX AUTOCOMPLETE DROP DOWN
public function auto_search(){
$search_term = $this->input->post_get('term');
$select_columns = '*';
$table_name = 'cw_sap_wbs';
$table_join = '';
$table_where = ' cw_sap_wbs.wbs_id like "'.$search_term.'%" and cw_sap_wbs.trans_status = 1';
$suggest_result = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
foreach($suggest_result as $result){
$suggest_prime_id = $result['prime_sap_wbs_id'];
$suggest_dispaly = $result['wbs_id'];
$suggest_obj_id = $result['wbs_network_id'];
$suggestions[] = array('value' => $suggest_prime_id, 'label' => $suggest_dispaly, 'display_name' => $suggest_dispaly, 'display_obj_id' => $suggest_obj_id);
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
public function search_pay_structure(){
$personal_code = $this->input->post('personal_code');
$wbs_element = $this->input->post('wbs_element');
$position = $this->input->post('position');
//select query for show a pay structure details
$select_columns = 'prime_pay_structure_id as prime_id,cw_category.category_name as category,cw_pay_structure.from_date,cw_pay_structure.to_date,cw_pay_structure.effective_date,cw_day_condition.day_condition as day_condition,cw_pay_structure.day_count,status1.status_mode_value as pf_limit,status2.status_mode_value as esi_limit';
$table_name = 'cw_pay_structure';
$table_join = 'inner join cw_category on cw_category.prime_category_id = cw_pay_structure.category inner join cw_day_condition on cw_day_condition.prime_day_condition_id = cw_pay_structure.day_condition inner join cw_status_mode status1 on status1.prime_status_mode_id = cw_pay_structure.pf_limit inner join cw_status_mode status2 on status2.prime_status_mode_id = cw_pay_structure.esi_limit';
$table_where = 'cw_pay_structure.personal_code = "'.$personal_code.'" and cw_pay_structure.wbs_element = "'.$wbs_element.'" and cw_pay_structure.position = "'.$position.'" and cw_pay_structure.trans_status = 1 and cw_day_condition.trans_status = 1';
$search_pay_struct_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
if($search_pay_struct_rslt[0]){
echo json_encode(array('success' => true, 'table_rslt' => $search_pay_struct_rslt, 'message' => "Proceed..!"));
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "No Records Found..!"));
}
}
public function save_pay_structure(){
$action = $this->input->post('action');
$prime_id = (int)$this->input->post('prime_id');
$personal_code = $this->input->post('personal_code');
$wbs_element = $this->input->post('wbs_element');
$category = $this->input->post('category');
$from_date = date("Y-m-d",strtotime($this->input->post('from_date')));
$to_date = date("Y-m-d",strtotime($this->input->post('to_date')));
$effective_date = date("Y-m-d",strtotime($this->input->post('effective_date')));
$day_condition = $this->input->post('day_condition');
$day_count = $this->input->post('day_count');
$pf_limit = $this->input->post('pf_limit');
$esi_limit = $this->input->post('esi_limit');
$position = $this->input->post('position');
$logged_id = $this->logged_id;
$date = date("Y-m-d H:i:s");
// if($effective_date){
// }
//for insert a new pay structure
if($prime_id === 0){
//same pay structure exist check if exist only show a exist data should not add
$select_columns = 'cw_pay_structure.prime_pay_structure_id as prime_id';
$table_name = 'cw_pay_structure';
$table_join = '';
$table_where = 'cw_pay_structure.personal_code = "'.$personal_code.'" and cw_pay_structure.wbs_element = "'.$wbs_element.'" and cw_pay_structure.position = "'.$position.'" and cw_pay_structure.category = "'.$category.'" and cw_pay_structure.from_date = "'.$from_date.'" and cw_pay_structure.to_date = "'.$to_date.'" and cw_pay_structure.trans_status = 1';
$exist_pay_struct_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$prime_id = (int)$exist_pay_struct_rslt[0]->prime_id;
if(!$prime_id){
$table_name = 'cw_pay_structure';
$table_col = 'personal_code,wbs_element,position,category,from_date,to_date,effective_date,day_condition,day_count,pf_limit,esi_limit,trans_created_by,trans_created_date';
$table_val = '("'.$personal_code.'","'.$wbs_element.'","'.$position.'","'.$category.'","'.$from_date.'","'.$to_date.'","'.$effective_date.'","'.$day_condition.'","'.$day_count.'","'.$pf_limit.'","'.$esi_limit.'","'.$logged_id.'","'.$date.'")';
$table_where = '';
$ins_pay_struct_rslt = $this->query_ins_function($table_name,$table_col,$table_val,$table_where);
$prime_id = (int)$ins_pay_struct_rslt[0]->ins_id;
}
}else{
if($action !== "edit"){
// $upd_pay_struct_rslt = $this->query_upd_function($table_name,$table_key_val,$table_where);
$upd_pay_struct_qry = 'UPDATE cw_pay_structure SET personal_code = "'.$personal_code.'",wbs_element = "'.$wbs_element.'",position = "'.$position.'",category = "'.$category.'",from_date = "'.$from_date.'",to_date = "'.$to_date.'",effective_date = "'.$effective_date.'",day_condition = "'.$day_condition.'",day_count = "'.$day_count.'",pf_limit = "'.$pf_limit.'",esi_limit = "'.$esi_limit.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$prime_id.'" and cw_pay_structure.trans_status = 1';
$upd_pay_struct_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_pay_struct_qry')");
}
}
//if insert or update as success then show a pay structure wage details
if($prime_id){
//FUNCTION FOR CREATE A EARNINGS AND DEDUCTION TABLE FORMAT
$ear_ded_tab_info = $this->pay_struct_line_table_create($prime_id,$category);
// print_r($ear_ded_tab_info);die;
// ,cw_pay_structure_line.field_type
if(!empty($ear_ded_tab_info)){
echo json_encode(array('success' => true, 'ear_table_rslt' => $ear_ded_tab_info['earnings'], 'ded_table_rslt' => $ear_ded_tab_info['deduction'],'drop_select_arr' => $ear_ded_tab_info['drop_select'],"prime_id" => $prime_id, 'message' => "Pay Structure Earnings and Deduction Details..!"));
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Payroll Formula or Paystrcuture check Process not Added for this Category..!"));
}
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Insert Error..!"));
}
}
public function save_pay_structure_line(){
$json_ear_arr = json_decode($this->input->post('json_ear_obj'),true);
$json_ded_arr = json_decode($this->input->post('json_ded_obj'),true);
$logged_id = $this->logged_id;
$date = date("Y-m-d H:i:s");
$prime_pay_struct_id = (int)$json_ear_arr[0]['prime_pay_struct_id'];
if(!$prime_pay_struct_id){
$prime_pay_struct_id = (int)$json_ded_arr[0]['prime_pay_struct_id'];
}
$pay_line_arr = array();
$ear_arr = array();
$ded_arr = array();
$ear_exist_arr = array();
$ded_exist_arr = array();
// echo "<Pre>";
// print_r($json_ear_arr);die;
//for calculate a total earnings gross amount for update to prime paystructure table
$tot_ear_gross_amt = array_sum(array_column($json_ear_arr, 'amount'));
$tot_ear_gross_amt = number_format((float)$tot_ear_gross_amt, 2, '.', '');
if($prime_pay_struct_id){
$select_columns = 'cw_pay_structure.personal_code,cw_pay_structure.wbs_element,cw_pay_structure.position,cw_pay_structure.category';
$table_name = 'cw_pay_structure';
$table_join = '';
$table_where = 'cw_pay_structure.prime_pay_structure_id = "'.$prime_pay_struct_id.'" and cw_pay_structure.trans_status = 1';
$get_pay_struct_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$personal_code = $get_pay_struct_rslt[0]['personal_code'];
$wbs_element = $get_pay_struct_rslt[0]['wbs_element'];
$position = $get_pay_struct_rslt[0]['position'];
$category = $get_pay_struct_rslt[0]['category'];
//SELECT QUERY TO GET A PAY STRUCTURE LINE TABLE DATA FOR EXIST CHECK
$select_columns = '*';
$table_name = 'cw_pay_structure_line';
$table_join = '';
$table_where = ' cw_pay_structure_line.prime_pay_structure_id = "'.$prime_pay_struct_id.'" and cw_pay_structure_line.trans_status = 1';
$pay_line_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
if($pay_line_rslt[0]){
$pay_line_arr = array_reduce($pay_line_rslt, function($result, $arr){
$result[$arr['wage_code']] = $arr['wage_name'];
return $result;
}, array());
$ear_arr = array_reduce($json_ear_arr, function($result, $arr){
$result[$arr['wage_code']] = $arr;
return $result;
}, array());
$ded_arr = array_reduce($json_ded_arr, function($result, $arr){
$result[$arr['wage_code']] = $arr;
return $result;
}, array());
$ear_exist_arr = array_diff_key($ear_arr,$pay_line_arr);
$ded_exist_arr = array_diff_key($ded_arr,$pay_line_arr);
//IF EDIT FUNCTION BUT NEW WAGES ADD SO WE ALSO CREATE A INSERT FUNCTION AT THE TIME OF EDIT
if($ear_exist_arr || $ded_exist_arr){
$action = "insert";
$pay_line_ins_qry_val = $this->ins_upd_qry_build_from_array($action,$ear_exist_arr,$ded_exist_arr,$logged_id,$date);
if($pay_line_ins_qry_val){
$table_name = 'cw_pay_structure_line';
$table_col = 'prime_pay_structure_id,field_type,wage_code,wage_name,amount,pf_applicable,esi_applicable,pt_applicable,trans_created_by,trans_created_date';
$table_val = ''.$pay_line_ins_qry_val.'';
$table_where = '';
$ins_pay_line_rslt = $this->query_ins_function($table_name,$table_col,$table_val,$table_where);
$prime_line_id = (int)$ins_pay_line_rslt[0]->ins_id;
}
}
//UPDATE FUCNTION
$action = "update";
$pay_line_qry_val = $this->ins_upd_qry_build_from_array($action,$json_ear_arr,$json_ded_arr,$logged_id,$date);
if($pay_line_qry_val){
$pay_struct_line_upd_qry = 'INSERT INTO cw_pay_structure_line (prime_pay_structure_line_id,prime_pay_structure_id,field_type,wage_code,wage_name,hrms_field_name,amount,pf_applicable,esi_applicable,pt_applicable,trans_updated_by,trans_updated_date) VALUES '.$pay_line_qry_val.' ON DUPLICATE KEY UPDATE prime_pay_structure_id = VALUES(prime_pay_structure_id),field_type = VALUES(field_type),wage_code = VALUES(wage_code),wage_name = VALUES(wage_name),hrms_field_name = VALUES(hrms_field_name),amount = VALUES(amount),pf_applicable = VALUES(pf_applicable),esi_applicable = VALUES(esi_applicable),pt_applicable = VALUES(pt_applicable),trans_updated_by = VALUES(trans_updated_by),trans_updated_date = VALUES(trans_updated_date)';
$pay_struct_line_upd_info = $this->db->query("CALL sp_a_run ('RUN','$pay_struct_line_upd_qry')");
if($pay_struct_line_upd_info){
//FUNCTION FOR CREATE A EARNINGS AND DEDUCTION TABLE FORMAT
$ear_ded_tab_info = $this->pay_struct_line_table_create($prime_pay_struct_id,$category);
// print_r($ear_ded_tab_info);die;
// ,cw_pay_structure_line.field_type
if(!empty($ear_ded_tab_info)){
//UPDATE QRY FOR UPDATE A TOTAL GROSS AMOUNT TO PAYSTRUCTURE TABLE
$upd_gross_amt_qry = 'UPDATE cw_pay_structure SET ear_gross_amt = "'.$tot_ear_gross_amt.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$prime_pay_struct_id.'" and cw_pay_structure.trans_status = 1';
$upd_gross_amt_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_gross_amt_qry')");
echo json_encode(array('success' => true, 'ear_table_rslt' => $ear_ded_tab_info['earnings'], 'ded_table_rslt' => $ear_ded_tab_info['deduction'],'drop_select_arr' => $ear_ded_tab_info['drop_select'], 'message' => "Date Updated Successfully..!"));
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Please Check Category and Employee Master Paystructure Check Validation..!"));
}
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Update Error..!"));
}
}
}else{
//PAY STRUCTURE LINE INSERT
$action = "insert";
$pay_line_ins_qry_val = $this->ins_upd_qry_build_from_array($action,$json_ear_arr,$json_ded_arr,$logged_id,$date);
if($pay_line_ins_qry_val){
$table_name = 'cw_pay_structure_line';
$table_col = 'prime_pay_structure_id,field_type,wage_code,wage_name,hrms_field_name,amount,pf_applicable,esi_applicable,pt_applicable,trans_created_by,trans_created_date';
$table_val = ''.$pay_line_ins_qry_val.'';
$table_where = '';
$ins_pay_line_rslt = $this->query_ins_function($table_name,$table_col,$table_val,$table_where);
$prime_line_id = (int)$ins_pay_line_rslt[0]->ins_id;
if($prime_line_id){
$ear_ded_tab_info = $this->pay_struct_line_table_create($prime_pay_struct_id,$category);
if(!empty($ear_ded_tab_info)){
//UPDATE QRY FOR UPDATE A TOTAL GROSS AMOUNT TO PAYSTRUCTURE TABLE
$upd_gross_amt_qry = 'UPDATE cw_pay_structure SET ear_gross_amt = "'.$tot_ear_gross_amt.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" WHERE cw_pay_structure.prime_pay_structure_id = "'.$prime_pay_struct_id.'" and cw_pay_structure.trans_status = 1';
$upd_gross_amt_rslt = $this->db->query("CALL sp_a_run ('UPDATE','$upd_gross_amt_qry')");
echo json_encode(array('success' => true, 'ear_table_rslt' => $ear_ded_tab_info['earnings'], 'ded_table_rslt' => $ear_ded_tab_info['deduction'],'drop_select_arr' => $ear_ded_tab_info['drop_select'], 'message' => "Data Inserted Successfully..!"));
}else{
echo json_encode(array('success' => false, 'table_rslt' => '', 'message' => "Please Check Category and Employee Master Paystructure Check Validation..!"));
}
}else{
echo json_encode(array('success' => false, 'message' => "Insert Error..!"));
}
}else{
echo json_encode(array('success' => false, 'message' => "Insert Error..!"));
}
}
}
}
//AFTER INSERT OR UPDATE THEN SHOW A ALL DATA THROUGH IN TABLE
public function pay_struct_line_table_create($prime_id,$category){
$return_array = array();
// inner join cw_payroll_formula on cw_payroll_formula.
$select_columns = 'cw_wage_map_hrms.wage_code,cw_wage_map_hrms.wage_name,cw_wage_map_hrms.hrms_field_name,(CASE WHEN cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" THEN cw_pay_structure_line.amount ELSE "0" END) as amount,(CASE WHEN cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" THEN cw_pay_structure_line.pf_applicable ELSE "" END) as pf_applicable,(CASE WHEN cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" THEN cw_pay_structure_line.esi_applicable ELSE "" END) as esi_applicable,(CASE WHEN cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" THEN cw_pay_structure_line.pt_applicable ELSE "" END) as pt_applicable,(CASE WHEN cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" THEN cw_pay_structure_line.prime_pay_structure_line_id ELSE "" END) as prime_pay_line_id,cw_payroll_formula.formula_type';
$table_name = 'cw_pay_structure_line';
$table_join = 'right join cw_wage_map_hrms on cw_wage_map_hrms.wage_code = cw_pay_structure_line.wage_code inner join cw_form_setting on cw_form_setting.label_name = cw_wage_map_hrms.hrms_field_name inner join cw_payroll_formula on cw_payroll_formula.out_column = cw_wage_map_hrms.hrms_field_name';
$table_where = 'cw_form_setting.prime_module_id = "employees" and cw_pay_structure_line.prime_pay_structure_id = "'.$prime_id.'" and cw_form_setting.paystructure_check = 1 and cw_payroll_formula.formula_for = "'.$category.'" and cw_payroll_formula.formula_mode = 1 and cw_pay_structure_line.trans_status = 1 and cw_form_setting.trans_status = 1 and cw_payroll_formula.trans_status = 1 and cw_wage_map_hrms.trans_status = 1 ORDER BY cw_payroll_formula.order_by ASC';
$wage_detail_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
// echo "<pre>";
// print_r($wage_detail_rslt);
if(empty($wage_detail_rslt)){
$select_columns = 'cw_wage_map_hrms.wage_code,cw_wage_map_hrms.wage_name,cw_wage_map_hrms.hrms_field_name,0 as amount,NULL as pf_applicable,NULL as esi_applicable,NULL as pt_applicable,cw_payroll_formula.formula_type,0 as prime_pay_line_id';
$table_name = 'cw_wage_map_hrms';
$table_join = ' inner join cw_form_setting on cw_form_setting.label_name = cw_wage_map_hrms.hrms_field_name inner join cw_payroll_formula on cw_payroll_formula.out_column = cw_wage_map_hrms.hrms_field_name';
$table_where = 'cw_form_setting.prime_module_id = "employees" and cw_form_setting.paystructure_check = 1 and cw_payroll_formula.formula_for = "'.$category.'" and cw_payroll_formula.formula_mode = 1 and cw_form_setting.trans_status = 1 and cw_payroll_formula.trans_status = 1 and cw_wage_map_hrms.trans_status = 1 ORDER BY cw_payroll_formula.order_by ASC';
$wage_detail_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
}
// echo "<pre>";
// print_r($wage_detail_rslt);die;
if($wage_detail_rslt[0]){
//select query for get a employee yes no dropdown get
/*$select_columns = '*';
$table_name = 'cw_status_mode';
$table_join = '';
$table_where = ' cw_status_mode.trans_status = 1';
$status_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$status_list = "<option value = ''>--Select Status--</option>";
foreach($status_rslt as $for){
$prime_mode_id = $for['prime_status_mode_id'];
$status_mode = $for['status_mode_value'];
$status_list .= "<option value = '$prime_mode_id'>$status_mode</option>";
}
$status_arr[0] = $status_list; */
// print_r($status_arr);
//EARNING TABLE STRUCUTRE CREATE
$ear_table_struct = '<table id="ear_pay_struct_table" class="table table-hover">
<thead class="thead-dark">
<tr>
<th></th><th></th><th scope="col">EARNINGS</th><th colspan = "2"></th><th></th>
</tr>
<tr>
<th scope="col">Wage Code</th>
<th scope="col">Wage Name</th>
<th scope="col">Amount</th>
<th scope="col">PF Applicable</th>
<th scope="col">ESI Applicable</th>
<th scope="col">PT Applicable</th>
<th scope="col" style = "display:none;">Field Name</th>
<th scope="col" style = "display:none;">Prime Pay Line Id</th>
<th scope="col" style = "display:none;">Prime Pay Id</th>
</tr>
</thead>
<tbody>';
$ear_table_data = implode('</tr>', array_map(function ($v) use($prime_id) {
//CONDITIONS ONLY FOR CREATE AND ALTER QUERY FORMAT
if((int)$v['formula_type'] === 1){
$ear_table_body = '<tr><td><input type="text" class="form-control" id="wage_code" name="wage_code" value ="'.$v['wage_code'].'" class="form-control" readonly = "readonly"></td><td><input type="text" class="form-control" id="wage_name" name="wage_name" value ="'.$v['wage_name'].'" class="form-control" readonly = "readonly"></td><td><input type="decimals" class="form-control" id="amount" name="amount" value ="'.$v['amount'].'" size="6"></td><td><select id="pf_applicable" name="pf_applicable" data-val = "'.$v['pf_applicable'].'" class="form-control select2"></select></td><td><select id="esi_applicable" name="esi_applicable" data-val = "'.$v['esi_applicable'].'" class="form-control select2"></select></td><td><select id="pt_applicable" name="pt_applicable" data-val = "'.$v['pt_applicable'].'" class="form-control select2"></select></td><td style = "display:none;"><input type="hidden" class="form-control" id="hrms_field_name" name="hrms_field_name" value = "'.$v['hrms_field_name'].'" class="form-control"></td><td style = "display:none;"><input type="hidden" class="form-control" id="prime_pay_line_id" name="prime_pay_line_id" value = "'.$v['prime_pay_line_id'].'" class="form-control"></td><td style = "display:none;"><input type="hidden" class="form-control" id="prime_pay_struct_id" name="prime_pay_struct_id" value = "'.$prime_id.'"></td>';
return $ear_table_body;
}
}, $wage_detail_rslt));
// if($ear_table_data){
// $ear_table_data .= '<td style = "display:none;"><input type="hidden" class="form-control" id="prime_pay_struct_id" name="prime_pay_struct_id" value = "'.$prime_id.'" class="form-control"></td></tr>';
// };
$ded_table_struct = '<table id="ded_pay_struct_table" class="table table-hover">
<thead class="thead-dark">
<tr>
<th></th><th scope="col" style = " text-align: center;">DEDUCTION</th><th></th>
</tr>
<tr>
<th scope="col">Wage Code</th>
<th scope="col">Wage Name</th>
<th scope="col">Amount</th>
<th scope="col" style = "display:none;">Prime Pay Line Id</th>
<th scope="col" style = "display:none;">Prime Pay Id</th>
</tr>
</thead>
<tbody>';
$ded_table_data = implode('</tr>', array_map(function ($v) use($prime_id) {
//CONDITIONS ONLY FOR CREATE AND ALTER QUERY FORMAT
if((int)$v['formula_type'] === 2){
$ded_table_body = '<tr><td><input type="text" class="form-control" id="wage_code" name="wage_code" value ="'.$v['wage_code'].'" class="form-control" readonly = "readonly"></td><td><input type="text" class="form-control" id="wage_name" name="wage_name" value ="'.$v['wage_name'].'" class="form-control" readonly = "readonly"></td><td><input type="decimals" class="form-control" id="amount" name="amount" value ="'.$v['amount'].'" size="5"></td><td style = "display:none;"><input type="hidden" class="form-control" id="hrms_field_name" name="hrms_field_name" value = "'.$v['hrms_field_name'].'" class="form-control"></td><td style = "display:none;"><input type="hidden" class="form-control" id="prime_pay_line_id" name="prime_pay_line_id" value = "'.$v['prime_pay_line_id'].'" class="form-control"></td><td style = "display:none;"><input type="hidden" class="form-control" id="prime_pay_struct_id" name="prime_pay_struct_id" value = "'.$prime_id.'"></td>';
return $ded_table_body;
}
}, $wage_detail_rslt));
// if($ded_table_data){
// $ded_table_data .= '<td style = "display:none;"><input type="hidden" class="form-control" id="prime_pay_struct_id" name="prime_pay_struct_id" value = "'.$prime_id.'" class="form-control"></td></tr>';
// }
//ARRAY FOR SELECT A DROPDOWN VALUE FOR PF,ESI AND PT
$drop_selct_arr = array_reduce($wage_detail_rslt, function($result, $arr){
$result[$arr['prime_pay_line_id']] = $arr;
return $result;
}, array());
if($ear_table_data && $ded_table_data){
$ear_table_details = $ear_table_struct.$ear_table_data;
$ded_table_details = $ded_table_struct.$ded_table_data;
$return_array["earnings"] = $ear_table_details;
$return_array["deduction"] = $ded_table_details;
$return_array["drop_select"] = $drop_selct_arr;
return $return_array;
}else
if($ear_table_data){
$ear_table_details = $ear_table_struct.$ear_table_data;
$return_array["earnings"] = $ear_table_details;
$return_array["drop_select"] = $drop_selct_arr;
return $return_array;
}else
if($ded_table_data){
$ded_table_details = $ded_table_struct.$ded_table_data;
$return_array["deduction"] = $ded_table_details;
$return_array["drop_select"] = $drop_selct_arr;
return $return_array;
}else{
return $return_array();
}
}
}
public function ins_upd_qry_build_from_array($action,$json_ear_arr,$json_ded_arr,$logged_id,$date){
$ear_upd_qry = "";
$ded_upd_qry = "";
//earning ins query build function
if(!empty($json_ear_arr)){
$ear_upd_qry = implode(',"'.$logged_id.'","'.$date.'"),(', array_map(function ($ear) {
if((int)$ear['prime_pay_line_id']){
$ear_qry = '"'.$ear['prime_pay_line_id'].'","'.$ear['prime_pay_struct_id'].'","earnings","'.$ear['wage_code'].'","'.$ear['wage_name'].'","'.$ear['hrms_field_name'].'","'.$ear['amount'].'","'.$ear['pf_applicable'].'","'.$ear['esi_applicable'].'","'.$ear['pt_applicable'].'"';
}else{
$ear_qry = '"'.$ear['prime_pay_struct_id'].'","earnings","'.$ear['wage_code'].'","'.$ear['wage_name'].'","'.$ear['hrms_field_name'].'","'.$ear['amount'].'","'.$ear['pf_applicable'].'","'.$ear['esi_applicable'].'","'.$ear['pt_applicable'].'"';
}
return $ear_qry;
}, $json_ear_arr));
if($ear_upd_qry){
$ear_upd_qry = '('.$ear_upd_qry.',"'.$logged_id.'","'.$date.'")';
}
}
//deduction ins query build function
if(!empty($json_ded_arr)){
$ded_upd_qry = implode(',"'.$logged_id.'","'.$date.'"),(', array_map(function ($ded) {
if((int)$ded['prime_pay_line_id']){
$ded_qry = '"'.$ded['prime_pay_line_id'].'","'.$ded['prime_pay_struct_id'].'","deduction","'.$ded['wage_code'].'","'.$ded['wage_name'].'","'.$ded['hrms_field_name'].'","'.$ded['amount'].'","'.$ded['pf_applicable'].'","'.$ded['esi_applicable'].'","'.$ded['pt_applicable'].'"';
}else{
$ded_qry = '"'.$ded['prime_pay_struct_id'].'","deduction","'.$ded['wage_code'].'","'.$ded['wage_name'].'","'.$ded['hrms_field_name'].'","'.$ded['amount'].'","'.$ded['pf_applicable'].'","'.$ded['esi_applicable'].'","'.$ded['pt_applicable'].'"';
}
return $ded_qry;
}, $json_ded_arr));
if($ded_upd_qry){
$ded_upd_qry = '('.$ded_upd_qry.',"'.$logged_id.'","'.$date.'")';
}
}
// if($action === "insert"){
if($ear_upd_qry && $ded_upd_qry){
return $ear_upd_qry.','.$ded_upd_qry;
}else
if($ear_upd_qry){
return $ear_upd_qry;
}else
if($ded_upd_qry){
return $ded_upd_qry;
}else{
return "";
}
/*}
else
if($action === "update"){
if($ear_upd_qry && $ded_upd_qry){
return $ear_upd_qry.','.$ded_upd_qry. 'ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2)';
}else
if($ear_upd_qry){
return $ear_upd_qry;
}else
if($ded_upd_qry){
return $ded_upd_qry;
}else{
return "";
}
}*/
}
//select qry build function
/*public function query_build_function($select_columns,$table_name,$table_join,$table_where){
$table_qry_detail = 'select '.$select_columns.' from '.$table_name.' '.$table_join.' where '.$table_where.'';
$table_qry_info = $this->db->query("CALL sp_a_run ('SELECT','$table_qry_detail')");
$table_qry_rslt = $table_qry_info->result_array();
$table_qry_info->next_result();
// echo "table_qry_detail::$table_qry_detail";
return $table_qry_rslt;
}
public function query_ins_function($table_name,$table_col,$table_val,$table_where){
$ins_qry_detail = 'INSERT INTO '.$table_name.'('.$table_col.') VALUES '.$table_val.' '.$table_where.'';
$ins_qry_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_qry_detail')");
$ins_qry_rslt = $ins_qry_info->result();
$ins_qry_info->next_result();
return $ins_qry_rslt;
} */
//IMPORT FILE VIEW INFORMATION
public function import(){
$this->load->view("$this->control_name/import",$data);
}
public function excel(){
require_once APPPATH."/third_party/PHPExcel.php";
$obj = new PHPExcel();
$excel_name = "pay_structure_import_details";
$db_name = $this->config->item("db_name");
$row_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_pay_structure' and COLUMN_NAME not like '%trans%' and COLUMN_NAME not in ('prime_pay_structure_id','ear_gross_amt')");
$row_table_col = $row_table_query->result();
$col_map_name = 'A';
$obj->getActiveSheet()->setCellValue($col_map_name."1", "Sl No");
$col_map_name = 'B';
foreach($row_table_col as $excel){
$excel_line_column_name = ucwords(str_replace("_"," ",$excel->col_name));
$obj->getActiveSheet()->setCellValue($col_map_name."1", $excel_line_column_name);
$col_map_name++;
}
//QRY FOR PAY STRUCTURE LINE COLUMN ADD TO EXCEL
$pay_struct_line_qry = 'SELECT label_name,view_name,cw_wage_map_hrms.wage_name FROM `cw_payroll_formula` INNER JOIN cw_form_setting on cw_form_setting.label_name = cw_payroll_formula.out_column inner join cw_wage_map_hrms on cw_wage_map_hrms.hrms_field_name = cw_form_setting.label_name WHERE cw_payroll_formula.order_by != 0 and cw_payroll_formula.formula_mode = 1 and cw_form_setting.paystructure_check = 1 and cw_payroll_formula.trans_status = 1 and cw_form_setting.trans_status = 1 ORDER BY order_by ASC';
$pay_struct_line_info = $this->db->query("CALL sp_a_run ('SELECT','$pay_struct_line_qry')");
$pay_struct_line_rslt = $pay_struct_line_info->result();
for($i = 0; $i <= 3; $i++){
if($i === 0){
$content = " Amount";
}else
if($i === 1){
$content = " PF Applicable";
}else
if($i === 2){
$content = " ESI Applicable";
}else
if($i === 3){
$content = " PT Applicable";
}
foreach($pay_struct_line_rslt as $pay_struct_line){
$excel_line_column_name = ucwords(str_replace("_"," ",$pay_struct_line->wage_name));
$obj->getActiveSheet()->setCellValue($col_map_name."1", $excel_line_column_name.$content);
$col_map_name++;
}
}
// Rename worksheet name
$filename= $excel_name.".xls"; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($obj, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
echo json_encode(array('success' => TRUE, 'output' => $excelOutput));
}
//FUNCTION FOR INSERT A PAYSTRUCTURE DETAILS TO TABLE
public function excel_file_import(){
$file_path = $this->input->post("file_path");
// echo "file_path::$file_path";die;
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
$db_name = $this->config->item("db_name");
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
include($filename);
try{
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_path);
}catch(Exception $e){
die('Error loading file "' . pathinfo($file_path, PATHINFO_BASENAME). '": ' . $e->getMessage());
return json_encode(array('success' => false, 'message' => "Invalid file or path"));
}
//FUNCTION FOR BUILD A CATEGORY TABLE QRY and THEN GET QUERY VALUE BUILD IN ARRAY
// $cat_tab_arr = $this->cat_qry_struct_function();
//FUNCTION FOR BUILD A CATEGORY TABLE QRY and THEN GET QUERY VALUE BUILD IN ARRAY
// $per_code_arr = $this->per_code_qry_struct_function();
//FUNCTION FOR BUILD A CATEGORY TABLE QRY and THEN GET QUERY VALUE BUILD IN ARRAY
// $wbs_elem_arr = $this->wbs_elem_qry_struct_function();
//FUNCTION FOR BUILD A CATEGORY TABLE QRY and THEN GET QUERY VALUE BUILD IN ARRAY
// $position_arr = $this->position_qry_struct_function();
//FUNCTION FOR BUILD A CATEGORY TABLE QRY and THEN GET QUERY VALUE BUILD IN ARRAY
// $day_con_arr = $this->day_con_qry_struct_function();
$prime_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_pay_structure' and COLUMN_NAME not like '%trans%' and COLUMN_NAME not in ('day_condition','day_count','pf_limit','esi_limit','ear_gross_amt')");
$prime_table_col = $prime_table_query->result_array();
//function for get a pay structure details
$get_pay_str_qry = 'select * from cw_pay_structure where cw_pay_structure.trans_status = 1';
$get_pay_str_info = $this->db->query("CALL sp_a_run ('SELECT','$get_pay_str_qry')");
$get_pay_str_rslt = $get_pay_str_info->result_array();
$get_pay_str_info->next_result();
$get_pay_str_arr = array_reduce($get_pay_str_rslt, function($result, $arr) {
$result[$arr['category']][$arr['personal_code']][$arr['wbs_element']][$arr['position']][$arr['effective_date']] = $arr['prime_pay_structure_id'];
return $result;
}, array());
$sheet = $excel_obj->getActiveSheet();
// $sheet = $excel_obj->getSheet(1);//
// $sheet = $excel_obj->getSheet($excel_sheet_name);
$highestRow = $sheet->getHighestRow();
$highest_column = $sheet->getHighestColumn();
$worksheetTitle = $sheet->getTitle();
// $highestColumnIndex = PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highest_column);
$key_data = array();
$status_array = array();
$err_column_array = array();
$sts = true;
$key_val_arr = array();
$pay_struct_val = "";
// $array = $sheet->rangeToArray('A1:'.$highest_column.$highestRow);
for ($row = 1; $row <= $highestRow; $row++) {
// $table_data = array();
$key_val_data = array();
$col_data = array();
$status_info = array();
$status_info["Excel Row"] = $row;
$error_info = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$data = $sheet->getCellByColumnAndRow($col, $row)->getValue();
// echo "data::$data";
if(($col === 5 || $col === 6 || $col === 7) && $data !== null && $row !== 1){
// echo "data::$data";
$unix_date = ($data - 25569) * 86400;
$excel_date = 25569 + ($unix_date / 86400);
$unix_date = ($excel_date - 25569) * 86400;
$data = gmdate("Y-m-d", $unix_date);
}else
if(is_numeric($data)) {
$data = strval($data);
}
$col_data[] = $data;
}
if($row === 1) {
// Header row. Save it in "$keys".
$key_data = $col_data;
}
// This is not the first row; so it is a data row.
// Transform $riga into a dictionary and add it to $data.
if($row !== 1 && $key_data){
$key_val_data = array_combine($key_data, $col_data);
$key_val_arr[$row] = $key_val_data;
}
}
//JSON FORMAT CREATE CODE
$key_val_json = json_encode($key_val_arr);
echo "key_val_json::$key_val_json";
die;
/*-----------------------------------------------------------------------------------*/
}
//FUNCTION FOR BUILD A CATEGORY TABLE QRY and THEN GET QUERY VALUE IN ARRAY
public function cat_qry_struct_function(){
$cat_tab_arr = array();
$select_columns = 'cw_category.prime_category_id,cw_category.category_name';
$table_name = 'cw_category';
$table_join = '';
$table_where = 'cw_category.trans_status = 1';
//FUNCTION USED FOR CREATE A QRY
$cat_tab_rslt = $this->query_build_function($select_columns,$table_name,$table_join,$table_where);
$cat_tab_arr = array_reduce($cat_tab_rslt, function($result, $arr) {
$result[$arr['category_name']] = $arr['prime_category_id'];
return $result;
}, array());
return $cat_tab_arr;
}
}
?>