File: /home/cafsindia/hrms_patroniss_com/application/models/Payroll_calculation_model.php
<?php
class Payroll_calculation_model extends CI_Model{
private $payroll_count;
private $month_day_res;
private $sup_emp;
private $ptax_rslt;
public function Payroll_calculation($trans_array){
$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 tax settings info
$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 where cw_professional_tax.trans_status = 1';
$ptax_data = $this->db->query("CALL sp_a_run ('SELECT','$ptax_qry')");
$ptax_result = $ptax_data->result_array();
$ptax_data->next_result();
$this->ptax_rslt = array_reduce($ptax_result, function ($result, $arr) {
$result[$arr['location']] = $arr;
return $result;
}, array());
// get_document_fees
$payroll_exist_query = 'SELECT employee_code,count(employee_code) as pay_count from cw_transactions where termination_status = 0 and trans_status = 1 group by employee_code';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_query')");
$payroll_exist_result = $payroll_exist_info->result_array();
$payroll_exist_info->next_result();
$this->payroll_count = array_reduce($payroll_exist_result, function ($result, $arr){
$result[$arr['employee_code']] = $arr['pay_count'];
return $result;
}, array());
$emp_qry = 'select GROUP_CONCAT(employee_code) as sup_emp from cw_monthly_input where trans_status = 1 and supplementary_status = 1';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_data->result();
$emp_data->next_result();
$this->sup_emp = $emp_result[0]->sup_emp;
$logged_id = $this->session->userdata("logged_id");
$date = date("Y-m-d H:i:s");
$payroll_array = array();
foreach($trans_array as $key => $trans){
$employee_code = $trans["employee_code"]; $trans["transactions_month"] = $trans["process_month"];
if((int)$trans["role"] === 2){
$trans["lop_days"] = $trans["lop_days"];
$trans["month_days"] = $this->get_total_work_days($trans["role"],$trans["transactions_month"]);
$trans["tds_percent"] = $trans["tds_percent"];
$trans["professional_tax_location"] = $trans["professional_tax_location"];
$trans["differential_day"] = $this->get_differential_day($trans["employee_code"],$trans["role"],$trans["transactions_month"]);
$trans["fixed_gross"] = $trans["fixed_gross"];
$trans["other_deduction"] = $trans["other_deduction"];
$trans["salary_loan"] = $trans["salary_loan"];
$trans["marketing_percentage"] = $trans["marketing_percentage"];
$trans["pf_eligibility"] = $trans["pf_eligibility"];
$trans["esi_eligibility"] = $trans["esi_eligibility"];
$trans["salary_advance"] = $trans["salary_advance"];
$trans["pre_count"] = $trans["pre_count"];
$trans["trainee_deduction"] = $trans["trainee_deduction"];
$trans["date_of_joining"] = $trans["date_of_joining"];
$trans["a_da"] = $trans["a_da"];
$trans["a_basic"] = $trans["a_basic"];
$trans["a_hra"] = $trans["a_hra"];
$trans["a_conveyance"] = $trans["a_conveyance"];
$trans["a_other_allowance"] = $trans["a_other_allowance"];
$trans["fixed_basic"] = $this->rounding_value(($trans["fixed_gross"]*(40/100)),'1');
$trans["fixed_hra"] = $this->rounding_value(($trans["fixed_gross"]*(30/100)),'1');
$trans["fixed_da"] = $this->rounding_value(($trans["fixed_basic"]*(50/100)),'1');
$trans["fo_allowance"] = $this->rounding_value(($trans["fixed_da"]*(50/100)),'1');
$trans["paid_days"] = $trans["month_days"]-$trans["lop_days"]-$trans["differential_day"];
$trans["basic"] = $this->rounding_value((($trans["fixed_basic"]/$trans["month_days"])*$trans["paid_days"]),'1');
$trans["hra"] = $this->rounding_value((($trans["fixed_hra"]/$trans["month_days"])*$trans["paid_days"]),'1');
$trans["da"] = $this->rounding_value((($trans["fixed_da"]/$trans["month_days"])*$trans["paid_days"]),'1');
$trans["other_allowance"] = $this->rounding_value((($trans["fo_allowance"]/$trans["month_days"])*$trans["paid_days"]),'1');
$trans["gross"] = $trans["basic"]+$trans["hra"]+$trans["da"]+$trans["other_allowance"];
if($trans["pf_eligibility"]==1){$a=$trans["basic"]+$trans["da"]+$trans["other_allowance"];if($a>=15000){$trans["pf_gross"] = 15000;}elseif($a<15000){$trans["pf_gross"] = $a;}}else{$trans["pf_gross"] = 0;}
$trans["tds_amount"] = $this->rounding_value(($trans["gross"]*($trans["tds_percent"]/100)),'1');
if($trans["pf_eligibility"]==1){$trans["pf"] = round($trans["pf_gross"]*(12/100));}else{$trans["pf"] = 0;}
if($trans["esi_eligibility"]==1){$trans["esi_gross"] = $trans["gross"];}else{$trans["esi_gross"] = 0;}
if($trans["esi_eligibility"]==1){$trans["esi"] = ceil($trans["esi_gross"]*(0.75/100));}else{$trans["esi"] = 0;}
if($trans["esi_eligibility"]==1){$trans["emp_esi"] = ceil($trans["esi_gross"]*(3.25/100));}else{$trans["emp_esi"] = 0;}
$trans["pt_projection"] = $trans["fixed_gross"];
$trans["payroll_count"] = $trans["pre_count"]+1;
$trans["perday"] = $this->rounding_value(($trans["fixed_gross"]/$trans["month_days"]),'1');
if(($trans["trainee_deduction"]==1)&&($trans["payroll_count"]==1)){$trans["salary_deduction"] = round($trans["perday"]*7);}else{$trans["salary_deduction"] = 0;}
if(($trans["trainee_deduction"]==1)&&($trans["payroll_count"]==4)){$x=round($trans["perday"]*7);$y=round($x*(20/100));$z=$x+$y;$trans["emp_incentive"] = $z;}else{$trans["emp_incentive"] = 0;}
$trans["a_gross"] = $trans["a_basic"]+$trans["a_da"]+$trans["a_hra"]+$trans["a_conveyance"]+$trans["a_other_allowance"];
$trans["total_earnings"] = $trans["gross"]+$trans["emp_incentive"]+$trans["a_gross"];
$trans["professional_tax"] = $this->get_professional_tax_value($trans["employee_code"],$trans["professional_tax_location"],$trans["pt_projection"],$trans["total_earnings"],$trans["transactions_month"]);
$trans["marketing_amount"] = $this->rounding_value(($trans["total_earnings"]*($trans["marketing_percentage"]/100)),'1');
if($trans["pf_eligibility"]==1){$trans["employer_pf_con"] = $trans["pf_gross"]*(8.33/100);}else{$trans["employer_pf_con"] = 0;}
if($trans["pf_eligibility"]==1){$trans["epf_eps_diff"] = $trans["pf_gross"]*(3.67/100);}else{$trans["epf_eps_diff"] = 0;}
$trans["employer_pf"] = $trans["employer_pf_con"]+$trans["epf_eps_diff"];
$trans["stat_deductions"] = $trans["employer_pf"]+$trans["emp_esi"];
$trans["total_deductions"] = $trans["pf"]+$trans["esi"]+$trans["professional_tax"]+$trans["tds_amount"]+$trans["marketing_amount"]+$trans["salary_advance"]+$trans["salary_deduction"]+$trans["salary_loan"]+$trans["stat_deductions"];
$trans["net_pay"] = $trans["total_earnings"]-$trans["total_deductions"];
$payroll_array[2][] ="('".$trans["employees_id"]."','".$trans["employee_code"]."','".$trans["emp_name"]."','".$trans["transactions_month"]."','".$trans["role"]."','".$trans["transactions_month"]."',\"$logged_id\",\"$date\",'".$trans["lop_days"]."','".$trans["month_days"]."','".$trans["tds_percent"]."','".$trans["professional_tax_location"]."','".$trans["differential_day"]."','".$trans["fixed_gross"]."','".$trans["other_deduction"]."','".$trans["salary_loan"]."','".$trans["marketing_percentage"]."','".$trans["pf_eligibility"]."','".$trans["esi_eligibility"]."','".$trans["salary_advance"]."','".$trans["pre_count"]."','".$trans["trainee_deduction"]."','".$trans["date_of_joining"]."','".$trans["a_da"]."','".$trans["a_basic"]."','".$trans["a_hra"]."','".$trans["a_conveyance"]."','".$trans["a_other_allowance"]."','".$trans["fixed_basic"]."','".$trans["fixed_hra"]."','".$trans["fixed_da"]."','".$trans["fo_allowance"]."','".$trans["paid_days"]."','".$trans["basic"]."','".$trans["hra"]."','".$trans["da"]."','".$trans["other_allowance"]."','".$trans["gross"]."','".$trans["pf_gross"]."','".$trans["tds_amount"]."','".$trans["pf"]."','".$trans["esi_gross"]."','".$trans["esi"]."','".$trans["emp_esi"]."','".$trans["pt_projection"]."','".$trans["payroll_count"]."','".$trans["perday"]."','".$trans["salary_deduction"]."','".$trans["emp_incentive"]."','".$trans["a_gross"]."','".$trans["total_earnings"]."','".$trans["professional_tax"]."','".$trans["marketing_amount"]."','".$trans["employer_pf_con"]."','".$trans["epf_eps_diff"]."','".$trans["employer_pf"]."','".$trans["stat_deductions"]."','".$trans["total_deductions"]."','".$trans["net_pay"]."','".$trans["salary_loan_total"]."','".$trans["salary_loan_installments"]."','".$trans["salary_loan_instal_count"]."','".$trans["salary_loan_balance"]."')";
$query_key = 'employees_id,employee_code,emp_name,transactions_month,role,process_month,trans_created_by,trans_created_date,lop_days,month_days,tds_percent,professional_tax_location,differential_day,fixed_gross,other_deduction,salary_loan,marketing_percentage,pf_eligibility,esi_eligibility,salary_advance,pre_count,trainee_deduction,date_of_joining,a_da,a_basic,a_hra,a_conveyance,a_other_allowance,fixed_basic,fixed_hra,fixed_da,fo_allowance,paid_days,basic,hra,da,other_allowance,gross,pf_gross,tds_amount,pf,esi_gross,esi,emp_esi,pt_projection,payroll_count,perday,salary_deduction,emp_incentive,a_gross,total_earnings,professional_tax,marketing_amount,employer_pf_con,epf_eps_diff,employer_pf,stat_deductions,total_deductions,net_pay,salary_loan_total,salary_loan_installments,salary_loan_instal_count,salary_loan_balance';
$qry_2 = 'INSERT INTO cw_transactions(employees_id,employee_code,emp_name,transactions_month,role,process_month,trans_created_by,trans_created_date,lop_days,month_days,tds_percent,professional_tax_location,differential_day,fixed_gross,other_deduction,salary_loan,marketing_percentage,pf_eligibility,esi_eligibility,salary_advance,pre_count,trainee_deduction,date_of_joining,a_da,a_basic,a_hra,a_conveyance,a_other_allowance,fixed_basic,fixed_hra,fixed_da,fo_allowance,paid_days,basic,hra,da,other_allowance,gross,pf_gross,tds_amount,pf,esi_gross,esi,emp_esi,pt_projection,payroll_count,perday,salary_deduction,emp_incentive,a_gross,total_earnings,professional_tax,marketing_amount,employer_pf_con,epf_eps_diff,employer_pf,stat_deductions,total_deductions,net_pay,salary_loan_total,salary_loan_installments,salary_loan_instal_count,salary_loan_balance'.$trans_key_array.') VALUES '.implode(',',$payroll_array[2]);
}else
if((int)$trans["role"] === 3){
$trans["lop_days"] = $trans["lop_days"];
$trans["month_days"] = $this->get_total_work_days($trans["role"],$trans["transactions_month"]);
$trans["tds_percent"] = $trans["tds_percent"];
$trans["professional_tax_location"] = $trans["professional_tax_location"];
$trans["differential_day"] = $this->get_differential_day($trans["employee_code"],$trans["role"],$trans["transactions_month"]);
$trans["fixed_gross"] = $trans["fixed_gross"];
$trans["other_deduction"] = $trans["other_deduction"];
$trans["salary_loan"] = $trans["salary_loan"];
$trans["marketing_percentage"] = $trans["marketing_percentage"];
$trans["pf_eligibility"] = $trans["pf_eligibility"];
$trans["esi_eligibility"] = $trans["esi_eligibility"];
$trans["salary_advance"] = $trans["salary_advance"];
$trans["pre_count"] = $trans["pre_count"];
$trans["date_of_joining"] = $trans["date_of_joining"];
$trans["trainee_deduction"] = $trans["trainee_deduction"];
$trans["supplementary_month"] = $trans["supplementary_month"];
$trans["supplementary_day"] = $trans["supplementary_day"];
$trans["a_basic"] = $trans["a_basic"];
$trans["a_da"] = $trans["a_da"];
$trans["a_hra"] = $trans["a_hra"];
$trans["a_conveyance"] = $trans["a_conveyance"];
$trans["a_other_allowance"] = $trans["a_other_allowance"];
$trans["fixed_basic"] = $this->rounding_value(($trans["fixed_gross"]*(40/100)),'1');
$trans["fixed_hra"] = $this->rounding_value(($trans["fixed_gross"]*(30/100)),'1');
$trans["fixed_da"] = $this->rounding_value(($trans["fixed_basic"]*(50/100)),'1');
$trans["fo_allowance"] = $this->rounding_value(($trans["fixed_da"]*(50/100)),'1');
$trans["paid_days"] = $trans["month_days"]-$trans["lop_days"]-$trans["differential_day"];
$trans["basic"] = $this->rounding_value((($trans["fixed_basic"]/$trans["month_days"])*$trans["paid_days"]),'1');
$trans["hra"] = $this->rounding_value((($trans["fixed_hra"]/$trans["month_days"])*$trans["paid_days"]),'1');
$trans["da"] = $this->rounding_value((($trans["fixed_da"]/$trans["month_days"])*$trans["paid_days"]),'1');
$trans["other_allowance"] = $this->rounding_value((($trans["fo_allowance"]/$trans["month_days"])*$trans["paid_days"]),'1');
if($trans["pf_eligibility"]==1){$trans["pf_gross"] = ($trans["basic"]+$trans["da"]+$trans["other_allowance"]);}else{$trans["pf_gross"] = 0;}
if($trans["pf_eligibility"]==1){$trans["pf"] = round($trans["pf_gross"]*(12/100));}else{$trans["pf"] = 0;}
if($trans["pf_eligibility"]==1){$trans["employer_pf"] = round($trans["pf_gross"]*(12/100));}else{$trans["employer_pf"] = 0;}
$trans["pt_projection"] = $trans["fixed_gross"];
$trans["payroll_count"] = $trans["pre_count"]+1;
$trans["perday"] = $this->rounding_value(($trans["fixed_gross"]/$trans["month_days"]),'1');
if(($trans["trainee_deduction"]==1)&&($trans["payroll_count"]==1)){$trans["salary_deduction"] = round($trans["perday"]*7);}else{$trans["salary_deduction"] = 0;}
if(($trans["trainee_deduction"]==1)&&($trans["payroll_count"]==4)){$x=round($trans["perday"]*7);$y=round($x*(20/100));$z=$x+$y;$trans["emp_incentive"] = $z;}else{$trans["emp_incentive"] = 0;}
$trans["s_basic"] = $this->rounding_value(($this->dz(($trans["fixed_basic"]/$trans["supplementary_month"])*$trans["supplementary_day"])),'1');
$trans["s_hra"] = $this->rounding_value(($this->dz(($trans["fixed_hra"]/$trans["supplementary_month"])*$trans["supplementary_day"])),'1');
$trans["s_conveyance"] = $this->rounding_value(($this->dz(($trans["fixed_da"]/$trans["supplementary_month"])*$trans["supplementary_day"])),'1');
$trans["s_other_allowance"] = $this->rounding_value(($this->dz(($trans["fo_allowance"]/$trans["supplementary_month"])*$trans["supplementary_day"])),'1');
$trans["s_gross"] = $trans["s_basic"]+$trans["s_conveyance"]+$trans["s_hra"]+$trans["s_other_allowance"];
$trans["gross"] = $trans["basic"]+$trans["hra"]+$trans["da"]+$trans["other_allowance"]+$trans["s_gross"];
$trans["a_gross"] = $trans["a_basic"]+$trans["a_da"]+$trans["a_hra"]+$trans["a_conveyance"]+$trans["a_other_allowance"];
$trans["total_earnings"] = $trans["gross"]+$trans["emp_incentive"]+$trans["a_gross"];
$trans["professional_tax"] = $this->get_professional_tax_value($trans["employee_code"],$trans["professional_tax_location"],$trans["pt_projection"],$trans["total_earnings"],$trans["transactions_month"]);
if($trans["esi_eligibility"]==1){$trans["esi_gross"] = $trans["gross"];}else{$trans["esi_gross"] = 0;}
if($trans["esi_eligibility"]==1){$trans["emp_esi"] = ceil($trans["esi_gross"]*(3.25/100));}else{$trans["emp_esi"] = 0;}
if($trans["esi_eligibility"]==1){$trans["esi"] = ceil($trans["esi_gross"]*(0.75/100));}else{$trans["esi"] = 0;}
$trans["tds_amount"] = $this->rounding_value(($trans["gross"]*($trans["tds_percent"]/100)),'1');
$trans["marketing_amount"] = $this->rounding_value((($trans["total_earnings"]-$trans["salary_deduction"])*(5/100)),'1');
$trans["total_deductions"] = $trans["pf"]+$trans["esi"]+$trans["professional_tax"]+$trans["tds_amount"]+$trans["marketing_amount"]+$trans["salary_advance"]+$trans["salary_deduction"]+$trans["salary_loan"]+$trans["other_deduction"];
$trans["net_pay"] = $trans["total_earnings"]-$trans["total_deductions"];
$payroll_array[3][] ="('".$trans["employees_id"]."','".$trans["employee_code"]."','".$trans["emp_name"]."','".$trans["transactions_month"]."','".$trans["role"]."','".$trans["transactions_month"]."',\"$logged_id\",\"$date\",'".$trans["lop_days"]."','".$trans["month_days"]."','".$trans["tds_percent"]."','".$trans["professional_tax_location"]."','".$trans["differential_day"]."','".$trans["fixed_gross"]."','".$trans["other_deduction"]."','".$trans["salary_loan"]."','".$trans["marketing_percentage"]."','".$trans["pf_eligibility"]."','".$trans["esi_eligibility"]."','".$trans["salary_advance"]."','".$trans["pre_count"]."','".$trans["date_of_joining"]."','".$trans["trainee_deduction"]."','".$trans["supplementary_month"]."','".$trans["supplementary_day"]."','".$trans["a_basic"]."','".$trans["a_da"]."','".$trans["a_hra"]."','".$trans["a_conveyance"]."','".$trans["a_other_allowance"]."','".$trans["fixed_basic"]."','".$trans["fixed_hra"]."','".$trans["fixed_da"]."','".$trans["fo_allowance"]."','".$trans["paid_days"]."','".$trans["basic"]."','".$trans["hra"]."','".$trans["da"]."','".$trans["other_allowance"]."','".$trans["pf_gross"]."','".$trans["pf"]."','".$trans["employer_pf"]."','".$trans["pt_projection"]."','".$trans["payroll_count"]."','".$trans["perday"]."','".$trans["salary_deduction"]."','".$trans["emp_incentive"]."','".$trans["s_basic"]."','".$trans["s_hra"]."','".$trans["s_conveyance"]."','".$trans["s_other_allowance"]."','".$trans["s_gross"]."','".$trans["gross"]."','".$trans["a_gross"]."','".$trans["total_earnings"]."','".$trans["professional_tax"]."','".$trans["esi_gross"]."','".$trans["emp_esi"]."','".$trans["esi"]."','".$trans["tds_amount"]."','".$trans["marketing_amount"]."','".$trans["total_deductions"]."','".$trans["net_pay"]."','".$trans["salary_loan_total"]."','".$trans["salary_loan_installments"]."','".$trans["salary_loan_instal_count"]."','".$trans["salary_loan_balance"]."')";
$query_key = 'employees_id,employee_code,emp_name,transactions_month,role,process_month,trans_created_by,trans_created_date,lop_days,month_days,tds_percent,professional_tax_location,differential_day,fixed_gross,other_deduction,salary_loan,marketing_percentage,pf_eligibility,esi_eligibility,salary_advance,pre_count,date_of_joining,trainee_deduction,supplementary_month,supplementary_day,a_basic,a_da,a_hra,a_conveyance,a_other_allowance,fixed_basic,fixed_hra,fixed_da,fo_allowance,paid_days,basic,hra,da,other_allowance,pf_gross,pf,employer_pf,pt_projection,payroll_count,perday,salary_deduction,emp_incentive,s_basic,s_hra,s_conveyance,s_other_allowance,s_gross,gross,a_gross,total_earnings,professional_tax,esi_gross,emp_esi,esi,tds_amount,marketing_amount,total_deductions,net_pay,salary_loan_total,salary_loan_installments,salary_loan_instal_count,salary_loan_balance';
$qry_3 = 'INSERT INTO cw_transactions(employees_id,employee_code,emp_name,transactions_month,role,process_month,trans_created_by,trans_created_date,lop_days,month_days,tds_percent,professional_tax_location,differential_day,fixed_gross,other_deduction,salary_loan,marketing_percentage,pf_eligibility,esi_eligibility,salary_advance,pre_count,date_of_joining,trainee_deduction,supplementary_month,supplementary_day,a_basic,a_da,a_hra,a_conveyance,a_other_allowance,fixed_basic,fixed_hra,fixed_da,fo_allowance,paid_days,basic,hra,da,other_allowance,pf_gross,pf,employer_pf,pt_projection,payroll_count,perday,salary_deduction,emp_incentive,s_basic,s_hra,s_conveyance,s_other_allowance,s_gross,gross,a_gross,total_earnings,professional_tax,esi_gross,emp_esi,esi,tds_amount,marketing_amount,total_deductions,net_pay,salary_loan_total,salary_loan_installments,salary_loan_instal_count,salary_loan_balance'.$trans_key_array.') VALUES '.implode(',',$payroll_array[3]);
}
}
if($payroll_array[2]){
$this->db->query($qry_2);
}
if($payroll_array[3]){
$this->db->query($qry_3);
} return true;
}
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;
}
public function dz($result){
if(is_nan($result) || is_infinite($result)){
$result = 0;
}else{
$result;
}
return $result;
} //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];
$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;
}
//Get Documentation Fee
public function get_document_fees($category,$employee_code,$process_month){
$payroll_exist_count = $this->payroll_count[$employee_code];
if((int)$payroll_exist_count === 0 && $process_month !== "03-2020"){
return 500;
}else{
return 0;
}
}
//Get DRA Fee
public function get_dra_amount($branch,$date_of_joining,$dra_prev,$dra_completion_status,$employee_code){
if((int)$dra_completion_status === 2){
// Get Branch wise DRA Amount
$dra_amount_qry = 'SELECT dra_amount,actual_amount from cw_dra_amount where cw_dra_amount.trans_status = 1 and cw_dra_amount.branch = "'.$branch.'" and start_date <= "'.$date_of_joining.'" and end_date >= "'.$date_of_joining.'"';
$dra_amount_data = $this->db->query("CALL sp_a_run ('SELECT','$dra_amount_qry')");
$dra_amount_result = $dra_amount_data->result();
$dra_amount_data->next_result();
$dra_amount = $dra_amount_result[0]->dra_amount;
$actual_amount = $dra_amount/3;
$payroll_exist = (int)$this->payroll_count[$employee_code];
$payroll_exist_count = $payroll_exist + 1;
if($dra_prev >= $dra_amount){
return 0;
}else{
$payroll_check_list_1 = array(1,2,3);
$payroll_check_list_2 = array(2,3,4);
if(in_array($employee_code, $this->sup_emp)){
if(in_array($payroll_exist_count, $payroll_check_list_1)){
return $actual_amount;
}else{
return 0;
}
}else{
if(in_array($payroll_exist_count, $payroll_check_list_2)){
return $actual_amount;
}else{
return 0;
}
}
}
}else{
return 0;
}
}
//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'];
$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)$day_conditions === 1) || ((int)$day_conditions === 2)){//STATIC DAYS AND CALENDER DAYS
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_cut_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_cut_day_data = $this->db->query("CALL sp_a_run ('SELECT','$diff_cut_day_qry')");
$diff_cut_day_result = $diff_cut_day_data->result();
$diff_cut_day_data->next_result();
$diff_cut_day_count = $diff_cut_day_data->num_rows();
$doj = $diff_cut_day_result[0]->date_of_joining;
if((int)$diff_cut_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 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 paid_status = 0 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(install_amount,0) as install_amt from cw_loan_installment where trans_status = 1 and paid_status = 0 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;
}
//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 date_of_joining from cw_employees where employee_code ="'.$employee_code.'" and trans_status=1 and termination_status=0';
$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();
$doj = $get_emp_sts_result[0]->date_of_joining;
$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_result = $this->ptax_rslt[$professional_tax_location];
$prof_tax_id = $ptax_result['prime_professional_tax_id'];
$location = $ptax_result['location'];
$calculation_mode = $ptax_result['calculation_period'];
$osm_first_st_val = $ptax_result['osm_first_start'];
$osm_first_ed_val = $ptax_result['osm_first_end'];
$osm_second_st_val = $ptax_result['osm_second_start'];
$osm_second_ed_val = $ptax_result['osm_second_end'];
$osm_second_end_val = $ptax_result['osm_second_end'];
$ptax_deduct_first = $ptax_result['first_period'];
$ptax_deduct_second = $ptax_result['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);
}
}
?>