File: /home/cafsindia/hrms_cafsinfotech_in/OLD/application_bk/controllers/Gratuity_view.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Gratuity_view extends Action_controller{
public function __construct(){
parent::__construct('gratuity_view');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$process_role[""] = "---- Select Role ----";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$process_role[$role_id] = $category_name;
}
$data['process_role'] = $process_role;
//get all the date columns without tax column
$get_match_columns = 'SELECT prime_form_id,prime_module_id,label_name,view_name FROM `cw_form_setting` WHERE prime_module_id = "employees" and field_type = 4 and date_type = 1';
$match_column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_match_columns')");
$match_column_result = $match_column_info->result();
$match_column_info->next_result();
$match_columns[""] = "---- Select Column ----";
foreach($match_column_result as $match_column){
$prime_form_id = $match_column->prime_form_id;
$column_value = $match_column->label_name;
$view_name = $match_column->view_name;
$match_columns[$this->xss_clean($column_value)] = $this->xss_clean($view_name);
}
$data['date_list'] = $match_columns;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//employee code view
public function emp_suggest(){
$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_term = $this->input->post_get('term');
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and employee_code like "'.$search_term.'%"';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
//06-12-2019 gratuity;
public function check_gratuity(){
$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);
}
$process_type = $this->input->post('process_type');
$employee_code = $this->input->post('process_emp_id');
$process_role = $this->input->post('process_role');
$start_date_col = $this->input->post('start_date_col');
$gratuity_date = date('Y-m-d' ,strtotime($this->input->post('gratuity_date')));
$qry = "";
if((int)$process_type === 1){
$qry = ' and employee_code = "'.$employee_code.'"';
}else
if((int)$process_type === 2){
$qry = ' and role = "'.$process_role.'"';
}
if(!$start_date_col){
echo json_encode(array("success" => FALSE,'message' => 'Please choose Start date Column..'));
exit(0);
}
//get gratuity settings
$check_eligibilty_qry = 'select cw_employees.employee_code,cw_employees.role,REPLACE(formula_detail,"@","") as formula_detail,cw_employees.date_of_joining,cw_employees.last_working_date,working_days,number_of_years,pay_days,year_rounding,formula_rounding,DATEDIFF("'.$gratuity_date.'", cw_employees.'.$start_date_col.')/365 AS tot_years from cw_gratuity inner join cw_employees on cw_employees.role = cw_gratuity.category where cw_gratuity.trans_status = 1 '.$qry.' ';
$check_eligibilty_data = $this->db->query("CALL sp_a_run ('SELECT','$check_eligibilty_qry')");
$check_eligibilty_result = $check_eligibilty_data->result();
$check_eligibilty_data->next_result();
if($check_eligibilty_result){
$tble_line = '';
$prime_ins_query_value = '';
$year_rounding_list = array();
foreach ($check_eligibilty_result as $key => $eligibilty_result){
$role = $eligibilty_result->role;
$working_days = $eligibilty_result->working_days;
$number_of_years = $eligibilty_result->number_of_years;
$pay_days = $eligibilty_result->pay_days;
$year_rounding = $eligibilty_result->year_rounding;
$formula_detail = $eligibilty_result->formula_detail;
$formula_rounding = $eligibilty_result->formula_rounding;
$employee_code = $eligibilty_result->employee_code;
$tot_year = $eligibilty_result->tot_years;
$gratuity_formula = '(('.$formula_detail.' * tbl_gratuity_temp.pay_days)/tbl_gratuity_temp.working_days)*tbl_gratuity_temp.tot_year';
if((int)$tot_year >= 5){
if($year_rounding){
$year_rounding_list = array(1=>$tot_year,2=>round($tot_year),3=>ceil($tot_year),4=>floor($tot_year));
if((int)$tot_year >= 5){
$tot_year = $year_rounding_list[$year_rounding];
}
}
$prime_ins_query_value .= '("'.$employee_code.'","'.$pay_days.'","'.$working_days.'",'.'"'.$tot_year.'",'.'"'.$number_of_years.'"),';
}
}
//Drop and create Temp Table
$drop_qry = 'DROP TABLE IF EXISTS tbl_gratuity_temp';
$drop_info = $this->db->query("CALL sp_a_run ('RUN','$drop_qry')");
$temp_qry = 'CREATE TABLE tbl_gratuity_temp (employee_code varchar(100),pay_days decimal(15,2),working_days decimal(15,2),tot_year decimal(15,2),number_of_years decimal(15,2))';
$temp_info = $this->db->query("CALL sp_a_run ('RUN','$temp_qry')");
//Insert Data into temp table
if($prime_ins_query_value){
$insert_qry = 'INSERT INTO tbl_gratuity_temp(employee_code,pay_days,working_days,tot_year,number_of_years) VALUES '.rtrim($prime_ins_query_value,",");
$insert_info = $this->db->query($insert_qry);
}
$sel_qry = 'SELECT cw_employees.employee_code,cw_employees.emp_name,cw_category.category_name,'.$start_date_col.' as date_of_joining,tot_year,pay_days,'.$gratuity_formula.' as gratuity_amt from tbl_gratuity_temp inner join cw_employees on cw_employees.employee_code = tbl_gratuity_temp.employee_code inner join cw_category on cw_category.prime_category_id = cw_employees.role';
// echo $sel_qry; die;
$gratuity_data = $this->db->query("CALL sp_a_run ('SELECT','$sel_qry')");
$gratuity_result = $gratuity_data->result();
$gratuity_data->next_result();
$tble_line = '';
foreach ($gratuity_result as $key => $gratuity) {
$employee_code = $gratuity->employee_code;
$emp_name = $gratuity->emp_name;
$category_name = $gratuity->category_name;
$tot_year = $gratuity->tot_year;
$pay_days = $gratuity->pay_days;
$gratuity_amt = $gratuity->gratuity_amt;
$date_of_joining = date("d-m-Y",strtotime($gratuity->date_of_joining));
if((int)$tot_year >= 5){
//year rounding base working year is rounded
if($year_rounding){
$year_rounding_list = array(1=>$tot_year,2=>round($tot_year),3=>ceil($tot_year),4 =>floor($tot_year));
$tot_year = $year_rounding_list[$year_rounding];
}
$gratuity_amt = $this->rounding_amount($gratuity_amt,$formula_rounding);
$tble_line .= "<tr>
<td>$category_name</td>
<td>$employee_code</td>
<td>$emp_name</td>
<td>$date_of_joining</td>
<td>$tot_year</td>
<td>$gratuity_amt</td>
</tr>";
}
}
}
if($tble_line === ''){
$tble_line .= "<tr><td colspan='6'> No Data Found</td>";
}
$table_data = " <h4 class='m-t-0 header-title'><b>Gratuity Information List</b></h4>
<table class='table table-striped table-bordered' id='gratuity_info'>
<thead>
<tr>
<th>Employee Role</th>
<th>Employee Code</th>
<th>Employee Name</th>
<th>DOJ</th>
<th>No. of years</th>
<th>Gratuity Amount</th>
</tr>
</thead>
<tbody>
$tble_line
</tbody>
</table>";
echo json_encode(array("success" => TRUE,'message' => $table_data));
}
//get gratuity details
public function get_gratuity($employee_code,$emp_result,$gratuity_date){
$employe_code_str = '"'.implode('","',$employee_code).'"';
//get gratuity settings
$check_eligibilty_qry = 'select working_days,number_of_years,pay_days,year_rounding,formula_detail,formula_rounding,category from cw_gratuity where trans_status = 1';
$check_eligibilty_data = $this->db->query("CALL sp_a_run ('SELECT','$check_eligibilty_qry')");
$check_eligibilty_result = $check_eligibilty_data->result();
$check_eligibilty_data->next_result();
$eligibilty_result = array_map(function($result){
$return_data['eligibilty_result']['working_days'] = $result->working_days;
$return_data['eligibilty_result']['number_of_years'] = $result->number_of_years;
$return_data['eligibilty_result']['pay_days'] = $result->pay_days;
$return_data['eligibilty_result']['year_rounding'] = $result->year_rounding;
$return_data['eligibilty_result']['formula_rounding'] = $result->formula_rounding;
$return_data['eligibilty_result']['category'] = $result->category;
$gratuity_formula = $result->formula_detail;
$gratuity_formula = str_replace("@","",$gratuity_formula);
$return_data['eligibilty_result']['gratuity_formula'] = "(".$gratuity_formula.") as gratuity";
$return_data['eligibilty_result']['gratuity_formula'] = $gratuity_formula;
$return_data['gratuity_formula'] = $gratuity_formula;
$return_data['category'] = $result->category;
return $return_data;
}, $check_eligibilty_result);
$gratuity_formula = implode(',',array_column($eligibilty_result,'gratuity_formula'));
if($gratuity_formula){
$gratuity_formula = ",".$gratuity_formula;
}
//echo $gratuity_formula; die;
$eligibilty_result = array_column($eligibilty_result,'eligibilty_result','category');
//$grat_formula_amt_qry = 'select employee_code'.$gratuity_formula.' from cw_transactions where trans_status =1 and employee_code IN ('.$employe_code_str.') group by employee_code order by transactions_month desc';
$grat_formula_amt_qry = 'SELECT a.employee_code,DATEDIFF("'.$gratuity_date.'", date_of_joining)/365 as tot_year'.$gratuity_formula.' FROM cw_transactions as a INNER JOIN (SELECT employee_code, date_format(MAX(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_transactions where date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y"), "%Y-%m-%d") <= "'.$gratuity_date.'" GROUP BY employee_code) as groupedtt ON a.employee_code = groupedtt.employee_code AND a.transactions_month = groupedtt.max_date';
$grat_formula_amt_data = $this->db->query("CALL sp_a_run ('SELECT','$grat_formula_amt_qry')");
$grat_formula_amt_result = $grat_formula_amt_data->result();
$grat_formula_amt_data->next_result();
$grat_amt_result = array_map(function($result){
$return_data['grat_formula_amt_result'] = $result;
$return_data['employee_code'] = $result->employee_code;
return $return_data;
}, $grat_formula_amt_result);
$grat_amt_result = array_column($grat_amt_result,'grat_formula_amt_result','employee_code');
$return_grt_data = array();
foreach($employee_code as $code){
$emp_rlst = $emp_result[$code];
$role_id = $emp_rlst->prime_category_id;
$date_of_joining = date('d-m-Y' ,strtotime($emp_rlst->date_of_joining));
$doj = $emp_rlst->date_of_joining;
$doj = strtotime($doj);
$last_day = strtotime($gratuity_date);
$eligib_rslt = $eligibilty_result[$role_id];
$year_rounding = $eligib_rslt['year_rounding'];
$working_days = $eligib_rslt['working_days'];
$number_of_years = $eligib_rslt['number_of_years'];
$pay_days = $eligib_rslt['pay_days'];
$formula_rounding = $eligib_rslt['formula_rounding'];
$gratuity_formula = $eligib_rslt['gratuity_formula'];
$tot_year = $eligib_rslt['tot_year'];
//find year difference
//$tot_year = (($last_day - $doj)/60/60/24)/365;
//$year_val = round($tot_year,2);
$gratuity_list = array();
$year_rounding_list = array(1=>"Actual",2=>"Normal",3=>"Greater",4=>"Lesser");
if((int)$year_rounding === 1){
$tot_year = $tot_year;
}else
if((int)$year_rounding === 2){
$tot_year = round($tot_year);
}else
if((int)$year_rounding === 3){
$tot_year = ceil($tot_year);
}else
if((int)$year_rounding === 4){
$tot_year = floor($tot_year);
}
//gratuity calculations for given formula and employee code
if($tot_year >= $number_of_years){
$grat_formula_amt = $grat_amt_result[$code]->$gratuity_formula;
$gratuity_value = (($grat_formula_amt * $pay_days)/$working_days) * $tot_year;
$gratuity_amt = $this->rounding_amount($gratuity_value,$formula_rounding);
}else{
$gratuity_amt = 0;
}
$return_grt_data[$code] = array('tot_year' => $tot_year,'gratuity_amt' => $gratuity_amt);
}
return $return_grt_data;
}
///rounding value for default functions
public function rounding_amount($result, $round_mode){
$result = round($result,2);
if(($round_mode == 0.5) || ($round_mode == 1)){
$final_result = round($result/$round_mode, 0)* $round_mode;
}else
if($round_mode == '>1'){
$final_result = ceil($result);
}else
if($round_mode == '<1'){
$final_result = floor($result);
}else
if($round_mode == '<0.5'){
$rslt = explode('.', $result);
$int_value = $rslt[0];
$point_value = $rslt[1];
if((int)$point_value > 50){
$final_result = $int_value.'.50';
}else{
$final_result = ceil($result);
}
}else
if($round_mode == '>0.5'){
$rslt = explode('.', $result);
$int_value = $rslt[0];
$point_value = $rslt[1];
if((int)$point_value >= 50){
$final_result = round($result);
}else{
$final_result = $int_value.'.50';
}
}else
if(((int)$round_mode == 5) || ((int)$round_mode == 10) || ((int)$round_mode == 50) || ((int)$round_mode == 100)){
$final_result = (ceil($result)% $round_mode === 0) ? ceil($result) : round(($result+ $round_mode/2)/ $round_mode)*$round_mode;
}else
if($round_mode == 0.1){
$final_result = round($result, 2);
}
return $final_result;
}
}
?>