File: /home/cafsindia/cpaqua.cafsinfotech.in/dump/ZRM/application/models/Process_payroll_model.php
<?php
/**********************************************************
Filename: Process_payroll_model
Description: Process payroll model for statutory law based pf,esi,pt value and total working day calculations.
Author: Jagufer Sathik
Created on: 05 March 2019
Reviewed by:
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
class Process_payroll_model extends CI_Model{
public $month_day_res;
public function __construct(){
parent::__construct();
$this->categorywise_days();
}
public function categorywise_days(){
$month_day_qry = 'SELECT category,day_conditions,day_count,day_start,day_end from cw_month_day where cw_month_day.trans_status = 1 ';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result_array();
$month_day_data->next_result();
$this->month_day_res = array_reduce($month_day_result, function ($result, $arr) {
$result[$arr['category']] = $arr;
return $result;
}, array());
}
//GET TOTAL LOAN AMOUNT FOR PER EMPLOYEE
public function get_loan_value($employee_code,$process_month){
$get_terminate_qry = 'select termination_status from cw_employees where trans_status=1 and employee_code ="'.$employee_code.'"';
$get_terminate_data = $this->db->query("CALL sp_a_run ('SELECT','$get_terminate_qry')");
$get_terminate_result = $get_terminate_data->result();
$get_terminate_data->next_result();
$terminate_sts = $get_terminate_result[0]->termination_status;
$loan_amt = 0;
if((int)$terminate_sts === 1){
$loan_qry = 'select IFNULL(sum(install_amount),0) as install_amt from cw_loan_installment where trans_status = 1 and emp_code ="'.$employee_code.'" and date_format(str_to_date(install_year, "%m-%Y") , "%Y-%m") >= date_format(str_to_date("'.$process_month.'", "%m-%Y"), "%Y-%m")';
$loan_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_qry')");
$loan_result = $loan_data->result();
$loan_data->next_result();
if(!empty($loan_result)){
$loan_amt = $loan_result[0]->install_amt;
}
}else{
$loan_qry = 'select IFNULL(sum(install_amount),0) as install_amt from cw_loan_installment where trans_status = 1 and emp_code ="'.$employee_code.'" and install_year ="'.$process_month.'"';
$loan_data = $this->db->query("CALL sp_a_run ('SELECT','$loan_qry')");
$loan_result = $loan_data->result();
$loan_data->next_result();
$loan_amt = 0;
if(!empty($loan_result)){
$loan_amt = $loan_result[0]->install_amt;
if($loan_amt > 0){
$upd_query = 'UPDATE cw_loan_installment SET paid_status = 1 WHERE trans_status = 1 and install_year ="'.$process_month.'" and emp_code="'.$employee_code.'"';
$update_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
}
}
}
return $loan_amt;
}
//STATUTORY INFORMATION
public function get_statutory_details($role){
$get_statutory = 'SELECT * FROM cw_statutory WHERE trans_status = 1 and category='.$role;
$statutory_data = $this->db->query("CALL sp_a_run ('SELECT','$get_statutory')");
$statutory_result = $statutory_data->result();
$statutory_data->next_result();
$result = false;
if($statutory_result){
$result = $statutory_result[0];
}
return $result;
}
//ESI UPDATE APRIL AND OCTOBER --16MAY2019
public function get_esi_status($earned_gross,$employee_code,$role,$process_month){
$statutory_rslt = $this->get_statutory_details($role);
$esi_limit = (int)$statutory_rslt->esi_limit;
$esi_conditions = (int)$statutory_rslt->esi_conditions;
$esi_start_check_month = (int)$statutory_rslt->esi_start_check_month;
$esi_end_check_month = (int)$statutory_rslt->esi_end_check_month;
$process_month = explode("-",$process_month);
$process_month_val = $process_month[0];
$get_column_qry = 'select * from cw_payroll_function_map where map_statutory_name= 2 and trans_status=1';
$get_column_data = $this->db->query("CALL sp_a_run ('SELECT','$get_column_qry')");
$get_column_result = $get_column_data->result();
$get_column_data->next_result();
$loc_column_list = array("earned_gross"=>"Earned Gross","paid_days"=>"Paid Days","month_days"=>"Month Days","lop_days"=>"Lop Days","fixed_basic"=>"Fixed Basic","fixed_gross"=>"Fixed Gross","professional_tax_amount"=>"Professional Tax","esi_loc"=>"ESI Location","esi_elig"=>"ESI Eligibility","supp_month_days"=>"Supplementary Month days","supp_paid_days"=>"Supplementary Paid days","arrear_gross"=>"Arrear Gross","arrear_pf_gross"=>"Arrear PF Gross","earned_basic"=>"Earned Basic");
foreach($get_column_result as $column_name){
$loc_name = $column_name->loc_name;
$db_column = $column_name->db_column;
if($loc_name == "esi_loc"){
$esi_loc_db = $db_column;
}
if($loc_name == "esi_elig"){
$esi_elig_db = $db_column;
}
}
//location nil is default
//1 -- Nil default no esi sts 2
$select_loc_qry = 'select '.$esi_loc_db.' as esi_loc from cw_employees where trans_status = 1 and role="'.$role.'" and employee_code="'.$employee_code.'"';
$select_loc_data = $this->db->query("CALL sp_a_run ('SELECT','$select_loc_qry')");
$select_loc_result = $select_loc_data->result();
$select_loc_data->next_result();
if(!empty($select_loc_result)){
$esi_location = $select_loc_result[0]->esi_loc;
if((int)$esi_location === 1){
$upd_query = 'UPDATE cw_employees SET '.$esi_elig_db.' = 2 WHERE trans_status = 1 and role="'.$role.'" and employee_code="'.$employee_code.'"';
$update_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
$esi_sts = 2;
return $esi_sts;
}else{
//esi eligibility 1= yes 2 = no;
if(($esi_start_check_month == $process_month_val) || ($esi_end_check_month == $process_month_val)){
if((int)$earned_gross > (int)$esi_limit){
$upd_query = 'UPDATE cw_employees SET '.$esi_elig_db.' = 2 WHERE trans_status = 1 and role="'.$role.'" and employee_code="'.$employee_code.'"';
$update_info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
$esi_sts = 2;//no esi
return $esi_sts;
}else{
$esi_sts = 1; //yes esi
return $esi_sts;
}
}else{
$select_esi_elig_qry = 'select '.$esi_elig_db.' as esi_elig from cw_employees where trans_status = 1 and role="'.$role.'" and employee_code="'.$employee_code.'"';
$select_esi_elig_data = $this->db->query("CALL sp_a_run ('SELECT','$select_esi_elig_qry')");
$select_esi_elig_result = $select_esi_elig_data->result();
$select_esi_elig_data->next_result();
$esi_sts =$select_esi_elig_result[0]->esi_elig;
return $esi_sts; //actual esi status from DB//
}
}
}
}
//Professional Tax Start
//CHECK PTAX FOR EVERY EMPLOYEE FOR THIS MONTH AND YEAR -- 11MAY2019 --Updates
public function get_professional_tax_value($employee_code,$professional_tax_location,$pt_projection,$total_earnings,$process_month){
$get_emp_sts_qry = 'select termination_status,date_of_joining from cw_employees where employee_code ="'.$employee_code.'" and trans_status=1';
$get_emp_sts_data = $this->db->query("CALL sp_a_run ('SELECT','$get_emp_sts_qry')");
$get_emp_sts_result = $get_emp_sts_data->result();
$get_emp_sts_data->next_result();
$term_sts = $get_emp_sts_result[0]->termination_status;
$doj = $get_emp_sts_result[0]->date_of_joining;
if($term_sts === 1){
$prof_tax = $this->get_fandf_professional_tax_value($employee_code,$professional_tax_location,$pt_projection,$total_earnings,$process_month);
}else{
$get_column_qry = 'select * from cw_payroll_function_map where map_statutory_name= 3 and trans_status=1';
$get_column_data = $this->db->query("CALL sp_a_run ('SELECT','$get_column_qry')");
$get_column_result = $get_column_data->result();
$get_column_data->next_result();
$loc_column_list = array("earned_gross"=>"Earned Gross","paid_days"=>"Paid Days","month_days"=>"Month Days","lop_days"=>"Lop Days","fixed_basic"=>"Fixed Basic","fixed_gross"=>"Fixed Gross","professional_tax_amount"=>"Professional Tax","esi_loc"=>"ESI Location","esi_elig"=>"ESI Eligibility","supp_month_days"=>"Supplementary Month days","supp_paid_days"=>"Supplementary Paid days","arrear_gross"=>"Arrear Gross","arrear_pf_gross"=>"Arrear PF Gross","earned_basic"=>"Earned Basic");
foreach($get_column_result as $column_name){
$loc_name = $column_name->loc_name;
$db_column = $column_name->db_column;
if($loc_name == "professional_tax_amount"){
$professional_tax_amount_db = $db_column;
}
if($loc_name == "earned_gross"){
$earned_gross_db = $db_column;
}
}
$ptax_qry = 'select cw_professional_tax.prime_professional_tax_id,location,calculation_period,ptax_deduction_month_first as first_period,ptax_deduction_month_second as second_period, osm_first_end,osm_second_end,osm_first_start,osm_second_start from cw_professional_tax_tax_range inner join cw_professional_tax on cw_professional_tax.prime_professional_tax_id = cw_professional_tax_tax_range.prime_professional_tax_id where cw_professional_tax.trans_status = 1 and location ='.$professional_tax_location;
$ptax_data = $this->db->query("CALL sp_a_run ('SELECT','$ptax_qry')");
$ptax_result = $ptax_data->result();
$ptax_data->next_result();
$prof_tax_id = $ptax_result[0]->prime_professional_tax_id;
$location = $ptax_result[0]->location;
$calculation_mode = $ptax_result[0]->calculation_period;
$osm_first_st_val = $ptax_result[0]->osm_first_start;
$osm_first_ed_val = $ptax_result[0]->osm_first_end;
$osm_second_st_val = $ptax_result[0]->osm_second_start;
$osm_second_ed_val = $ptax_result[0]->osm_second_end;
$osm_second_end_val = $ptax_result[0]->osm_second_end;
$ptax_deduct_first = $ptax_result[0]->first_period;
$ptax_deduct_second = $ptax_result[0]->second_period;
$trans_month = $process_month;
$process_month = explode("-",$process_month);
$process_month_val = $process_month[0];
$process_year_val = $process_month[1];
$process_month_date = $process_month[1]."-".$process_month[0]."-01";
//By Formula every month calculated ptax amount based on earn gross
$first_period = array();
for($osm_first_st_val;$osm_first_st_val<=$osm_first_ed_val;$osm_first_st_val++){
$first_period[] = $osm_first_st_val;
}
//array bulid based on months start
$second_period_part_i = array();
for($osm_second_st_val;$osm_second_st_val<=12;$osm_second_st_val++){
$second_period_part_i[] = $osm_second_st_val;
}
$second_period_part_ii = array();
for($osm_second_ed_val;$osm_second_ed_val>=1;$osm_second_ed_val--){
$second_period_part_ii[] = $osm_second_ed_val;
}
sort($second_period_part_ii);
$second_period = array_merge($second_period_part_i,$second_period_part_ii);
//array bulid based on months End
//Payroll month fall in first period and second priod start
//Payroll month fall in first period find process and proj count
if(in_array($process_month_val,$first_period)){
$act_count = array_search($osm_first_ed_val, array_values($first_period));
$cut_off_count = array_search($ptax_deduct_first, array_values($first_period));
$cur_count = array_search($process_month_val, array_values($first_period));
$cut_off_count++;
$process_count = (int)$cut_off_count - (int)$cur_count;
$proj_count = (int)$act_count - (int)$cur_count;
}else
if(in_array($process_month_val,$second_period)){
//Payroll month fall in second period find process and proj count
$act_count = array_search($osm_second_end_val, array_values($second_period));
$cut_off_count = array_search($ptax_deduct_second, array_values($second_period));
$cur_count = array_search($process_month_val, array_values($second_period));
$cut_off_count++;
$process_count = (int)$cut_off_count - (int)$cur_count;
$proj_count = (int)$act_count - (int)$cur_count;
}
if((int)$process_count < 0){
$process_count=0;
}
$prof_tax = 0;
if((int)$calculation_mode === 1){
//By Formula -- First Method
$every_month_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status=1 and prime_professional_tax_id ="'.$prof_tax_id.'" and earning_range_from <= "'.floor($total_earnings).'" and earning_range_to >= '.floor($total_earnings);
$every_month_data = $this->db->query("CALL sp_a_run ('SELECT','$every_month_qry')");
$every_month_result = $every_month_data->result();
$every_month_data->next_result();
$prof_tax = $every_month_result[0]->ptax_amt;
}else
if((int)$calculation_mode === 2){
$projection_total = $pt_projection * (int)$proj_count; //2 month count
//Find last total earned gross amount
$last_month_date = date("Y-m-d", strtotime(date("Y-m-d", strtotime($process_month_date)) . " - ".$cur_count." months"));
$previous_tax_qry= 'select ifnull(sum(total_earnings),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = "'.$employee_code.'" and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") BETWEEN date_format("'.$last_month_date.'", "%Y-%m") and date_format("'.$process_month_date.'", "%Y-%m")';
$previous_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$previous_tax_qry')");
$previous_tax_result = $previous_tax_data->result();
$previous_tax_data->next_result();
$previous_earned = $previous_tax_result[0]->total_earnings; //upto this month sum earned gross
$total_earn = $previous_earned + $projection_total + $total_earnings;
//Find current professional tax pay amount
$find_tax_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status = 1 and prime_professional_tax_id ="'.$prof_tax_id.'" and earning_range_from <= "'.floor($total_earn).'" and earning_range_to >= '.floor($total_earn);
$find_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$find_tax_qry')");
$find_tax_result = $find_tax_data->result();
$find_tax_data->next_result();
$find_tax_val = $find_tax_result[0]->ptax_amt;
//Find last paying professional tax amount and find final professional tax values and return to ptax column
$last_tax_qry= 'select ifnull(sum('.$professional_tax_amount_db.'),0) as professional_tax from cw_transactions where trans_status =1 and employee_code = "'.$employee_code.'" and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") BETWEEN date_format("'.$last_month_date.'", "%Y-%m") and date_format("'.$process_month_date.'", "%Y-%m")';
$last_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$last_tax_qry')");
$last_tax_result = $last_tax_data->result();
$last_tax_data->next_result();
$last_tax_val = $last_tax_result[0]->professional_tax;
$prof_tax_amt = $find_tax_val - $last_tax_val;
$prof_tax = $prof_tax_amt/$process_count;
}else
if((int)$calculation_mode === 3){
//By Once in six month -- Last Method
if(((int)$process_month_val === (int)$ptax_deduct_first) || ((int)$process_month_val === (int)$ptax_deduct_second)){
//Find Projection amount
//projection amount
$projection_total = $pt_projection * (int)$proj_count; //2 month count
//calculate sum of actual and projection amount and six month amount value
$last_month_date = date("Y-m-d", strtotime(date("Y-m-d", strtotime($process_month_date)) . " - ".$cur_count." months"));
$previous_tax_qry= 'select ifnull(sum(total_earnings),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = "'.$employee_code.'" and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") BETWEEN date_format("'.$last_month_date.'", "%Y-%m") and date_format("'.$process_month_date.'", "%Y-%m")';
$previous_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$previous_tax_qry')");
$previous_tax_result = $previous_tax_data->result();
$previous_tax_data->next_result();
$previous_earned = $previous_tax_result[0]->total_earnings; //find curr to pre earned gross sum value
$total_earn = $previous_earned + $projection_total + $total_earnings; // total sum (earned + project)
//Find current professional tax pay amount
$find_tax_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status=1 and prime_professional_tax_id ="'.$prof_tax_id.'" and earning_range_from <= "'.floor($total_earn).'" and earning_range_to >= '.floor($total_earn);
$find_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$find_tax_qry')");
$find_tax_result = $find_tax_data->result();
$find_tax_data->next_result();
$prof_tax = $find_tax_result[0]->ptax_amt;
}
}
}
return $this->rounding_value($prof_tax, 1);
}
//GET LWF EMPLOYER (COMPANY AMOUNT) FUNCTION
public function get_lwf_employer($lwf_location,$process_month){
$process_month = explode("-",$process_month);
$process_month_val = $process_month[0];
//$process_month_val = 12;
$select_lwf_qry = 'select pay_month,company_amount FROM cw_lwf_setting inner join cw_lwf_setting_lwf_pay_month on cw_lwf_setting_lwf_pay_month.prime_lwf_setting_id=cw_lwf_setting.prime_lwf_setting_id WHERE cw_lwf_setting.trans_status =1 and lwf_location = "'.$lwf_location.'"';
$select_lwf_data = $this->db->query("CALL sp_a_run ('SELECT','$select_lwf_qry')");
$select_lwf_result = $select_lwf_data->result();
$select_lwf_data->next_result();
$pay_month = $select_lwf_result[0]->pay_month;
$company_amount = $select_lwf_result[0]->company_amount;
$lwf_comp_amt = 0;
if($pay_month == $process_month_val){
$lwf_comp_amt = $company_amount;
}
return $lwf_comp_amt;
}
//GET LWF EMPLOYEE (EMPLOYEE AMOUNT) FUNCTION
public function get_lwf_employee($lwf_location,$process_month){
$process_month = explode("-",$process_month);
$process_month_val = $process_month[0];
//$process_month_val = 12;
$select_lwf_qry = 'select pay_month,employee_amount FROM cw_lwf_setting inner join cw_lwf_setting_lwf_pay_month on cw_lwf_setting_lwf_pay_month.prime_lwf_setting_id=cw_lwf_setting.prime_lwf_setting_id WHERE cw_lwf_setting.trans_status =1 and lwf_location = "'.$lwf_location.'"';
$select_lwf_data = $this->db->query("CALL sp_a_run ('SELECT','$select_lwf_qry')");
$select_lwf_result = $select_lwf_data->result();
$select_lwf_data->next_result();
$pay_month = $select_lwf_result[0]->pay_month;
$employee_amount = $select_lwf_result[0]->employee_amount;
$lwf_emp_amt = 0;
if($pay_month == $process_month_val){
$lwf_emp_amt = $employee_amount;
}
return $lwf_emp_amt;
}
//GET DIFFERENTIAL DAY COUNT FOR NEW JOINING details
//intermediate joining date and month days count differentiate day count
public function get_differential_day($employee_code,$role,$process_month){
$month_day_result = $this->month_day_res[$role];
$tot_month_day = $this->get_total_work_days($role,$process_month);
if($month_day_result){
$day_conditions = $month_day_result['day_conditions'];
$day_count = $month_day_result['day_count'];
$day_start = $month_day_result['day_start'];
$day_end = $month_day_result['day_end'];
if((int)$day_conditions === 1){//STATIC DAYS
$diff_day_qry = 'select date_of_joining from cw_employees where trans_status = 1 and employee_code = "'.$employee_code.'" and DATE_FORMAT(date_of_joining, "%m-%Y") like "'.$process_month.'"';
$diff_day_data = $this->db->query("CALL sp_a_run ('SELECT','$diff_day_qry')");
$diff_day_result = $diff_day_data->result();
$diff_day_data->next_result();
$doj = $diff_day_result[0]->date_of_joining;
$diff_day_count = $diff_day_data->num_rows();
if((int)$diff_day_count === 0){
$final_dif_day = 0;
}else{
$trans_month = explode("-",$process_month);
$month_val = $trans_month[0];
$year_val = $trans_month[1];
$day_count = cal_days_in_month(CAL_GREGORIAN, $month_val, $year_val);
$process_date = $day_count."-".$process_month;
$process_date = date("d-m-Y", strtotime($process_date));
$process_date = strtotime($process_date);
$doj = strtotime($doj);
$diff = $process_date - $doj;
$differ_day = round($diff / 86400);
$final_dif_day = $tot_month_day - $differ_day - 1;
}
}else
if((int)$day_conditions === 2){ //CALENDER DAYS
$diff_day_qry = 'select date_of_joining from cw_employees where trans_status = 1 and employee_code = "'.$employee_code.'" and DATE_FORMAT(date_of_joining, "%m-%Y") like "'.$process_month.'"';
$diff_day_data = $this->db->query("CALL sp_a_run ('SELECT','$diff_day_qry')");
$diff_day_result = $diff_day_data->result();
$diff_day_data->next_result();
$diff_day_count = $diff_day_data->num_rows();
$doj = $diff_day_result[0]->date_of_joining;
if((int)$diff_day_count === 0){
$final_dif_day = 0;
}else{
$trans_month = explode("-",$process_month);
$month_val = $trans_month[0];
$year_val = $trans_month[1];
$day_count = cal_days_in_month(CAL_GREGORIAN, $month_val, $year_val);
$process_date = $day_count."-".$process_month;
$process_date = date("d-m-Y", strtotime($process_date));
$process_date = strtotime($process_date);
$doj = strtotime($doj);
$diff = $process_date - $doj;
$differ_day = round($diff / 86400);
$final_dif_day = $tot_month_day - $differ_day - 1;
}
}else
if((int)$day_conditions === 3){//CUTOFF DAYS
$trans_month = explode("-",$process_month);
$month_val = $trans_month[0];
$year_val = $trans_month[1];
$process_end = $day_end."-".$month_val."-".$year_val;
$process_end = date('Y-m-d',strtotime($process_end));
$pre_month = ($month_val - 1) % 12;
$process_start = $day_start."-".$pre_month."-".$year_val;
$process_start = date('Y-m-d',strtotime($process_start));
$diff_day_qry = 'select date_of_joining from cw_employees where trans_status = 1 and employee_code = "'.$employee_code.'" and DATE_FORMAT(date_of_joining, "%Y-%m-%d") between "'.$process_start.'" and "'.$process_end.'"';
$diff_day_data = $this->db->query("CALL sp_a_run ('SELECT','$diff_day_qry')");
$diff_day_result = $diff_day_data->result();
$diff_day_data->next_result();
$diff_day_count = $diff_day_data->num_rows();
$doj = $diff_day_result[0]->date_of_joining;
if((int)$diff_day_count === 0){
$final_dif_day = 0;
}else{
$doj = strtotime($doj);
$process_end = strtotime($process_end);
$diff = $process_end - $doj;
$differ_day = round($diff / 86400);
$final_dif_day = $tot_month_day - $differ_day - 1;
}
}
}
return $final_dif_day;
}
//Get Documentation Fee
public function get_document_fees($category,$employee_code,$process_month){
//get termination status
$emp_qry = 'select termination_status from cw_employees where trans_status = 1 and employee_code = "'.$employee_code.'" ';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_data->result();
$emp_data->next_result();
$termination_status = $emp_result[0]->termination_status;
$payroll_exist_query = 'select count(employee_code) as pay_count from cw_transactions where employee_code ="'.$employee_code.'" and trans_status = 1';
//echo "BSK $payroll_exist_query"; die;
$payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_query')");
$payroll_exist_result = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$payroll_exist_count = $payroll_exist_result[0]->pay_count;
if((int)$payroll_exist_count === 0 && $process_month !== "03-2020" && (int)$termination_status === 0){
return 500;
}else{
return 0;
}
}
//Get DRA Fee
public function get_dra_fees($dra,$employee_code,$date_of_join,$branch,$process_month,$dra_completion_status){
if((int)$dra_completion_status === 1){
$emp_qry = 'select count(*) as rslt_count from cw_dra_fees where trans_status = 1 and branch = "'.$branch.'" and employee_code = "'.$employee_code.'" and process_month = "'.$process_month.'" and ("'.$date_of_join.'" BETWEEN "2010-01-30 14:15:55" AND "2010-09-29 10:15:55")';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_data->result();
$emp_data->next_result();
$emp_qry = 'select supplementary_status from cw_monthly_input where trans_status = 1 and employee_code = "'.$employee_code.'" and process_month = "'.$process_month.'"';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_data->result();
$emp_data->next_result();
$supplementary_status = (int)$emp_result[0]->supplementary_status;
$payroll_exist_query = 'select count(employee_code) as pay_count from cw_transactions where employee_code = "'.$employee_code.'" and trans_status = 1 ';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_query')");
$payroll_exist_result = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$payroll_exist_count = (int)$payroll_exist_result[0]->pay_count;
$payroll_check_list_1 = array(1,2,3);
$payroll_check_list_2 = array(2,3,4);
if($supplementary_status === 1 && in_array($payroll_exist_count, $payroll_check_list_1)){
return $dra;
}else
if(in_array($payroll_exist_count, $payroll_check_list_1)){
return $dra;
}else{
return 0;
}
}else{
return 0;
}
}
//formula PD = MD-LD-DD-S_DD;
//find re-leaving date for resigning employee details check this values 29/11/2019
public function get_seperation_day($employee_code,$role,$process_month){
$sep_day_qry = 'select last_working_date from cw_employees where trans_status = 1 and employee_code = "'.$employee_code.'" and termination_status =1';
$sep_day_data = $this->db->query("CALL sp_a_run ('SELECT','$sep_day_qry')");
$sep_day_result = $sep_day_data->result();
$sep_day_data->next_result();
$sep_day = $sep_day_result[0]->last_working_date;
if($sep_day){
$sep_day = date("d-m-Y", strtotime($sep_day));
$final_sep_day = 0;
$month_day_qry = 'select category,day_conditions,day_count,day_start,day_end from cw_month_day where cw_month_day.trans_status = 1 and category ="'.$role.'"';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result();
$month_day_data->next_result();
$tot_month_day = $this->get_total_work_days($role,$process_month);
if($month_day_result){
$day_conditions = $month_day_result[0]->day_conditions;
$day_count = $month_day_result[0]->day_count;
$day_start = $month_day_result[0]->day_start;
$day_end = $month_day_result[0]->day_end;
if((int)$day_conditions === 1){
//STATIC DAYS
$process_date = $day_count."-".$process_month;
$process_date = date("d-m-Y", strtotime($process_date));
$process_date = strtotime($process_date);
$sep_day = strtotime($sep_day);
$diff = $process_date - $sep_day;
$separation_day = round($diff / 86400);
//$final_sep_day = $tot_month_day - $separation_day;
}else
if((int)$day_conditions === 2){
//CALENDER DAYS
$trans_month = explode("-",$process_month);
$month_val = $trans_month[0];
$year_val = $trans_month[1];
$day_count = cal_days_in_month(CAL_GREGORIAN, $month_val, $year_val);
$process_date = $day_count."-".$process_month;
$process_date = date("d-m-Y", strtotime($process_date));
$process_date = strtotime($process_date);
$sep_day = strtotime($sep_day);
$diff = $process_date - $sep_day;
$separation_day = round($diff / 86400);
//$final_sep_day = $tot_month_day - $separation_day;
}else
if((int)$day_conditions === 3){
//CUTOFF DAYS
$trans_month = explode("-",$process_month);
$month_val = $trans_month[0];
$year_val = $trans_month[1];
$process_date = $day_end."-".$month_val."-".$year_val;
$process_date = strtotime($process_date);
$sep_day = strtotime($sep_day);
$diff = $process_date - $sep_day;
$separation_day = round($diff / 86400);
//$final_sep_day = $tot_month_day - $separation_day;
}
}
}else{
$separation_day = 0;
}
return $separation_day;
}
//Gratuity alteration query changed-- 08JAN2020
//get gratuity details
public function get_gratuity($employee_code,$role){
//get gratuity settings
$gratuity_formula_qry = 'select fandf_only from cw_gratuity where trans_status = 1 and formula_for = "'.$role.'" and out_column = "gratuity"';
$gratuity_formula_data = $this->db->query("CALL sp_a_run ('SELECT','$gratuity_formula_qry')");
$gratuity_formula_result = $gratuity_formula_data->result();
$gratuity_formula_data->next_result();
$fandf_only = (int)$gratuity_formula_result[0]->fandf_only;
if($fandf_only === 0){
//get gratuity settings
$check_eligibilty_qry = 'select working_days,number_of_years,pay_days,year_rounding,formula_detail,formula_rounding from cw_gratuity where trans_status = 1 and category="'.$role.'"';
$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();
$working_days = $check_eligibilty_result[0]->working_days;
$number_of_years = $check_eligibilty_result[0]->number_of_years;
$pay_days = $check_eligibilty_result[0]->pay_days;
$year_rounding = $check_eligibilty_result[0]->year_rounding;
$gratuity_formula = $check_eligibilty_result[0]->formula_detail;
$formula_rounding = $check_eligibilty_result[0]->formula_rounding;
$gratuity_formula = str_replace("@","",$gratuity_formula);
$gratuity_formula = "(".$gratuity_formula.") as gratuity";
//get emp details
$gratutity_emp_details_qry = 'select date_of_joining,last_working_date from cw_employees where trans_status =1 and employee_code="'.$employee_code.'"';
$gratutity_emp_data = $this->db->query("CALL sp_a_run ('SELECT','$gratutity_emp_details_qry')");
$gratutity_emp_result = $gratutity_emp_data->result();
$gratutity_emp_data->next_result();
$doj = $gratutity_emp_result[0]->date_of_joining;
$last_day = $gratutity_emp_result[0]->last_working_date;
$doj = strtotime($doj);
$last_day = strtotime($last_day);
//total year working values
$tot_year = (($last_day - $doj)/60/60/24)/365;
$year_val = round($tot_year,2);
$year_rounding_list = array(1=>"Actual",2=>"Normal",3=>"Greater",4=>"Lesser");
//year rounding base working year is rounded
if((int)$year_rounding === 1){
$tot_year = $year_val;
}else
if((int)$year_rounding === 2){
$tot_year = round($year_val);
}else
if((int)$year_rounding === 3){
$tot_year = ceil($year_val);
}else
if((int)$year_rounding === 4){
$tot_year = floor($year_val);
}
//gratuity calculations for given formula and employee code
if($tot_year >= $number_of_years){
$grat_formula_amt_qry = 'select '.$gratuity_formula.' from cw_employees where trans_status = 1 and employee_code = "'.$employee_code.'" ';
$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_formula_amt = $grat_formula_amt_result[0]->gratuity;
$gratuity_value = (($grat_formula_amt * $pay_days)/$working_days) * $tot_year;
$gratutity_amt = $this->rounding_value($gratuity_value,$formula_rounding);
}else{
$gratuity_amt = 0;
}
}else{
$gratuity_amt = 0;
}
return $gratutity_amt;
}
//rounding value for gratuity only
public function rounding_value($result, $round_mode){
$result = round($result,2);
$final_result =0;
if(($round_mode == 0.5) || ($round_mode == 1)){
$final_result = round($result/$round_mode, 0)* $round_mode;
}elseif($round_mode == '>1'){
$final_result = ceil($result);
}elseif($round_mode == '<1'){
$final_result = floor($result);
}elseif($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);
}
}elseif($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';
}
}elseif(((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;
}elseif($round_mode == 0.1){
$final_result = round($result, 2);
}
return $final_result;
}
//Gratuity End -- 20MAY2019
//Get salary Start date and End date
public function get_salary_date($category){
$month_day_qry = 'select category,day_conditions,day_count,day_start,day_end from cw_month_day where cw_month_day.trans_status = 1 and category ="'.$category.'"';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result();
$month_day_data->next_result();
//Get Salary Start Date
if($month_day_result){
$role = $month_day_result[0]->category;
$day_conditions = $month_day_result[0]->day_conditions;
$day_count = $month_day_result[0]->day_count;
$day_start = $month_day_result[0]->day_start;
$day_end = $month_day_result[0]->day_end;
if((int)$day_conditions === 3){
return array('day_start'=> $day_start, 'day_end' => $day_end,'day_conditions'=>(int)$day_conditions);
}else{
$day_start = '01';
if((int)$day_conditions === 2){
$day_end = date("t",strtotime($day_start."-".$timecard_month));
}else{
$day_end = $day_count;
}
return array('day_start'=> $day_start, 'day_end' => $day_end,'day_conditions'=>(int)$day_conditions);
}
}
}
public function get_fandf_professional_tax_value($employee_code,$professional_tax_location,$pt_projection,$total_earnings,$process_month){
$get_column_qry = 'select * from cw_payroll_function_map where map_statutory_name= 3 and trans_status=1';
$get_column_data = $this->db->query("CALL sp_a_run ('SELECT','$get_column_qry')");
$get_column_result = $get_column_data->result();
$get_column_data->next_result();
$loc_column_list = array("earned_gross"=>"Earned Gross","paid_days"=>"Paid Days","month_days"=>"Month Days","lop_days"=>"Lop Days","fixed_basic"=>"Fixed Basic","fixed_gross"=>"Fixed Gross","professional_tax_amount"=>"Professional Tax","esi_loc"=>"ESI Location","esi_elig"=>"ESI Eligibility","supp_month_days"=>"Supplementary Month days","supp_paid_days"=>"Supplementary Paid days","arrear_gross"=>"Arrear Gross","arrear_pf_gross"=>"Arrear PF Gross","earned_basic"=>"Earned Basic");
foreach($get_column_result as $column_name){
$loc_name = $column_name->loc_name;
$db_column = $column_name->db_column;
if($loc_name == "professional_tax_amount"){
$professional_tax_amount_db = $db_column;
}
}
$ptax_qry = 'select cw_professional_tax.prime_professional_tax_id,location,calculation_period,ptax_deduction_month_first as first_period,ptax_deduction_month_second as second_period, osm_first_end,osm_second_end from cw_professional_tax_tax_range inner join cw_professional_tax on cw_professional_tax.prime_professional_tax_id = cw_professional_tax_tax_range.prime_professional_tax_id where cw_professional_tax.trans_status = 1 and location ='.$professional_tax_location;
$ptax_data = $this->db->query("CALL sp_a_run ('SELECT','$ptax_qry')");
$ptax_result = $ptax_data->result();
$ptax_data->next_result();
$prof_tax_id = $ptax_result[0]->prime_professional_tax_id;
$location = $ptax_result[0]->location;
$calculation_mode = $ptax_result[0]->calculation_period;
$ptax_first_period = $ptax_result[0]->osm_first_end;
$ptax_second_period = $ptax_result[0]->osm_second_end;
$ptax_deduct_first = $ptax_result[0]->first_period;
$ptax_deduct_second = $ptax_result[0]->second_period;
$trans_month = $process_month;
$process_month = explode("-",$process_month);
$process_month_val = $process_month[0];
$process_year_val = $process_month[1];
$process_month_date = $process_month[1]."-".$process_month[0]."-01";
//tax area Tamil Nadu tax calculation
//By Formula every month calculated ptax amount based on earn gross
$prof_tax = 0;
if((int)$calculation_mode === 1){
//By Formula -- First Method
$every_month_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status=1 and prime_professional_tax_id ="'.$prof_tax_id.'" and earning_range_from <= "'.floor($total_earnings).'" and earning_range_to >= '.floor($total_earnings);
$every_month_data = $this->db->query("CALL sp_a_run ('SELECT','$every_month_qry')");
$every_month_result = $every_month_data->result();
$every_month_data->next_result();
$prof_tax = $every_month_result[0]->ptax_amt;
}else
if((int)$calculation_mode === 2){
//By Monthly -- Second Method -- values
//create first and second tax pay period array
$period_qry = 'select osm_first_start,osm_first_end,osm_second_start,osm_second_end from cw_professional_tax where trans_status =1 and location ="'.$professional_tax_location.'"';
$period_data = $this->db->query("CALL sp_a_run ('SELECT','$period_qry')");
$period_result = $period_data->result();
$period_data->next_result();
$osm_first_st_val = $period_result[0]->osm_first_start;
$osm_first_ed_val = $period_result[0]->osm_first_end;
$osm_second_st_val = $period_result[0]->osm_second_start;
$osm_second_ed_val = $period_result[0]->osm_second_end;
$first_period = array();
for($osm_first_st_val;$osm_first_st_val<=$osm_first_ed_val;$osm_first_st_val++){
$first_period[] = $osm_first_st_val;
}
$second_period_part_i = array();
for($osm_second_st_val;$osm_second_st_val<=12;$osm_second_st_val++){
$second_period_part_i[] = $osm_second_st_val;
}
$second_period_part_ii = array();
for($osm_second_ed_val;$osm_second_ed_val>=1;$osm_second_ed_val--){
$second_period_part_ii[] = $osm_second_ed_val;
}
sort($second_period_part_ii);
$second_period = array_merge($second_period_part_i,$second_period_part_ii);
$loop_count = 5;
$tot_count = 0;
//calculating pending projection count loop
if(in_array($process_month_val,$first_period)){
$cur_count = array_search($process_month_val, array_values($first_period));
$tot_count = (int)$loop_count - (int)$cur_count;
}else
if(in_array($process_month_val,$second_period)){
$cur_count = array_search($process_month_val, array_values($second_period));
$tot_count = (int)$loop_count - (int)$cur_count;
}
//projection amount
$projection_total = $pt_projection * (int)$tot_count; //2 month count
//Find last total earned gross amount
$last_month_date = date("Y-m-d", strtotime(date("Y-m-d", strtotime($process_month_date)) . " - ".$cur_count." months"));
$previous_tax_qry= 'select ifnull(sum(total_earnings),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = "'.$employee_code.'" and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") BETWEEN date_format("'.$last_month_date.'", "%Y-%m") and date_format("'.$process_month_date.'", "%Y-%m")';
$previous_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$previous_tax_qry')");
$previous_tax_result = $previous_tax_data->result();
$previous_tax_data->next_result();
$previous_earned = $previous_tax_result[0]->total_earnings; //upto this month sum earned gross
$total_earn = $previous_earned + $projection_total + $total_earnings;
//loop processing count
$process_count = (int)$tot_count + 1;
//Find current professional tax pay amount
$find_tax_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status = 1 and prime_professional_tax_id ="'.$prof_tax_id.'" and earning_range_from <= "'.floor($total_earn).'" and earning_range_to >= '.floor($total_earn);
$find_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$find_tax_qry')");
$find_tax_result = $find_tax_data->result();
$find_tax_data->next_result();
$find_tax_val = $find_tax_result[0]->ptax_amt;
//Find last paying professional tax amount and find final professional tax values and return to ptax column
$last_tax_qry= 'select ifnull(sum('.$professional_tax_amount_db.'),0) as professional_tax from cw_transactions where trans_status =1 and employee_code = "'.$employee_code.'" and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") BETWEEN date_format("'.$last_month_date.'", "%Y-%m") and date_format("'.$process_month_date.'", "%Y-%m")';
$last_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$last_tax_qry')");
$last_tax_result = $last_tax_data->result();
$last_tax_data->next_result();
$last_tax_val = $last_tax_result[0]->professional_tax;
$prof_tax = $find_tax_val - $last_tax_val;
}else
if((int)$calculation_mode === 3){
//By Once in six month -- Last Method
$period_qry = 'select osm_first_start,osm_first_end,osm_second_start,osm_second_end from cw_professional_tax where trans_status =1 and location ="'.$professional_tax_location.'" ';
$period_data = $this->db->query("CALL sp_a_run ('SELECT','$period_qry')");
$period_result = $period_data->result();
$period_data->next_result();
$osm_first_st_val = $period_result[0]->osm_first_start;
$osm_first_ed_val = $period_result[0]->osm_first_end;
$osm_second_st_val = $period_result[0]->osm_second_start;
$osm_second_ed_val = $period_result[0]->osm_second_end;
$first_period = array();
for($osm_first_st_val;$osm_first_st_val<=$osm_first_ed_val;$osm_first_st_val++){
$first_period[] = $osm_first_st_val;
}
$second_period_part_i = array();
for($osm_second_st_val;$osm_second_st_val<=12;$osm_second_st_val++){
$second_period_part_i[] = $osm_second_st_val;
}
$second_period_part_ii = array();
for($osm_second_ed_val;$osm_second_ed_val>=1;$osm_second_ed_val--){
$second_period_part_ii[] = $osm_second_ed_val;
}
sort($second_period_part_ii);
$second_period = array_merge($second_period_part_i,$second_period_part_ii);
$loop_count = 5;
$tot_count = 0;
//calculating pending projection count loop
if(in_array($process_month_val,$first_period)){
$cur_count = array_search($process_month_val, array_values($first_period));
$tot_count = (int)$loop_count - (int)$cur_count;
}else
if(in_array($process_month_val,$second_period)){
$cur_count = array_search($process_month_val, array_values($second_period));
$tot_count = (int)$loop_count - (int)$cur_count;
}
//Find Projection amount
//projection amount
$projection_total = $pt_projection * (int)$tot_count; //2 month count
//calculate sum of actual and projection amount and six month amount value
$last_month_date = date("Y-m-d", strtotime(date("Y-m-d", strtotime($process_month_date)) . " - ".$cur_count." months"));
$previous_tax_qry= 'select ifnull(sum(total_earnings),0) as total_earnings from cw_transactions where trans_status =1 and employee_code = "'.$employee_code.'" and date_format(str_to_date(transactions_month, "%m-%Y") , "%Y-%m") BETWEEN date_format("'.$last_month_date.'", "%Y-%m") and date_format("'.$process_month_date.'", "%Y-%m")';
$previous_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$previous_tax_qry')");
$previous_tax_result = $previous_tax_data->result();
$previous_tax_data->next_result();
$previous_earned = $previous_tax_result[0]->total_earnings; //find curr to pre earned gross sum value
$total_earn = $previous_earned + $projection_total + $total_earnings; // total sum (earned + project)
//Find current professional tax pay amount
$find_tax_qry = 'select professional_tax_amount as ptax_amt from cw_professional_tax_tax_range where trans_status=1 and prime_professional_tax_id ="'.$prof_tax_id.'" and earning_range_from <= "'.floor($total_earn).'" and earning_range_to >= '.floor($total_earn);
$find_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$find_tax_qry')");
$find_tax_result = $find_tax_data->result();
$find_tax_data->next_result();
$prof_tax = $find_tax_result[0]->ptax_amt;
}
return $this->rounding_value($prof_tax,1);
}
//GET TOTAL WORKING DAYS
public function get_total_work_days($role,$process_month){
$month_day_result = $this->month_day_res[$role];
if($month_day_result){
$role = $month_day_result['category'];
$day_conditions = $month_day_result['day_conditions'];
$day_count = $month_day_result['day_count'];
$day_start = $month_day_result['day_start'];
$day_end = $month_day_result['day_end'];
$month_day = array();
if((int)$day_conditions === 1){
//STATIC DAYS
$month_day = $day_count;
}else
if((int)$day_conditions === 2){
//CALENDER DAYS
$trans_month = explode("-",$process_month);
$month_val = $trans_month[0];
$year_val = $trans_month[1];
//echo "BSK $month_val :: $year_val :: $process_month"; die;
$day_count = cal_days_in_month(CAL_GREGORIAN, $month_val, $year_val);
$month_day = $day_count;
}else
if((int)$day_conditions === 3){
//CUTOFF DAYS
$trans_month = explode("-",$process_month);
$month_val = $trans_month[0];
$year_val = $trans_month[1];
$pre_month = (int)$month_val - 1;
$pre_year_val = $year_val;
if((int)$pre_month === 0){
$pre_month = 12;
$pre_year_val = (int)$year_val - 1;
}
$start_date = $day_start."-".$pre_month."-".$pre_year_val;
$end_date = $day_end."-".$month_val."-".$year_val;
$start_date = strtotime($start_date);
$end_date = strtotime($end_date);
$datediff = $end_date - $start_date;
$day_count = round($datediff/86400);//60 * 60 * 24
$month_day = (int)$day_count + 1;
}
}else{
$trans_month = explode("-",$process_month);
$month_val = $trans_month[0];
$year_val = $trans_month[1];
$day_count = cal_days_in_month(CAL_GREGORIAN, $month_val, $year_val);
$month_day = $day_count;
}
return $month_day;
}
}
?>