File: //home/cafsindia/hrms_patroniss_com/application/controllers/Increment_bk04DEC2020.php
<?php
/**********************************************************
Filename: Increment
Description: Increment.
Author: Sathish
Created on: 06 June 2019
Reviewed by:
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Base_controller.php");
class Increment extends Base_controller{
public function __construct(){
parent::__construct('increment');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->collect_base_info();
$this->load->model('Process_payroll_model');
$this->load->model('Hr_methods_model');
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$valid_array = $this->page_validation();
if($valid_array){
$data['valid_array'] = $this->validation_ui($valid_array);
}
//GET Monthly Input Columns
$data['table_headers'] = '';
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1')");
$role_result = $role_info->result();
$role_info->next_result();
if($role_result){
$pick_key = array_column($role_result, "prime_category_id");
$pick_val = array_column($role_result, "category_name");
$category_list = array_combine( $pick_key, $pick_val);
}
array_unshift($category_list,"---- Select Category ----");
$data['category_list'] = $category_list;
$this->load->view('increment/manage',$data);
}
//Check Page Validation
public function page_validation(){
//Check arrear column mapped or not
$valid_array = array();
$check_arr_qry = 'SELECT COUNT(prime_form_id) as rslt_count FROM cw_form_setting LEFT OUTER JOIN cw_arrear_column_mapping ON cw_arrear_column_mapping.transaction_column=cw_form_setting.label_name WHERE increment_check=1 AND transaction_column IS NULL';
$check_arr_data = $this->db->query("CALL sp_a_run ('SELECT','$check_arr_qry')");
$check_arr_result = $check_arr_data->result();
$check_arr_data->next_result();
$arr_count = $check_arr_result[0]->rslt_count;
if((int)$arr_count > 0){
$valid_array['Arrear Column not Mapped'] = "Check All the Increment Column mapped with Arrear Columns in Arrear Column Mapping";
}
$error_data = array('paid_days'=>'Paid Days','month_days'=>'Month Days','fixed_basic'=>'Fixed Basic','earned_basic'=>'Earned Basic','earned_gross'=>'Earned Gross','arrear_gross'=>'Arrear Gross','arrear_pf_gross'=>'Arrear PF Gross');
$mapping_data = $this->Hr_methods_model->get_mapped_db_column(12);
$result = array_diff_key($error_data, $mapping_data);
if($result){
$result = implode(",",$result);
$valid_array['Function Not Mapped'] = "Check All the Function Column Mapping $result";
}
return $valid_array;
}
public function validation_ui($valid_array){
$tr_line = "";
foreach($valid_array as $type => $msg){
$tr_line .= "<tr $color><td>$type</td><td>$msg</td></tr>";
}
$table_info = "<table class='table table-bordered' style='text-align:center;'>
<thead>
<tr>
<th>Type</th>
<th>Validation Message</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $table_info;
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search_increment_data(){
$effective_date = date("Y-m-d",strtotime($this->input->post('effective_date')));
$apply_month = $this->input->post('apply_month');
$employee_code = $this->input->post('employee_code');
$category = $this->input->post('category');
$before_days = $this->input->post('before');
$after_day = $this->input->post('after');
$category = $this->input->post('category');
$process_by = $this->input->post('process_by');
if($category){
$inc_column_qry = 'select GROUP_CONCAT(label_name) as label_names from cw_form_setting where prime_module_id = "employees" and increment_check = 1 and trans_status = 1';
$inc_column_data = $this->db->query("CALL sp_a_run ('SELECT','$inc_column_qry')");
$inc_column_result = $inc_column_data->result();
$inc_column_data->next_result();
if(!$inc_column_result[0]->label_names){
echo json_encode(array('success' => false, 'message' => "Increment Columns Not Created"));
exit(0);
}
//GET Values
$value_qry = 'SELECT '.$inc_column_result[0]->label_names.' from cw_employees where employee_code = "'.$employee_code.'" and trans_status = 1';
$value_data = $this->db->query("CALL sp_a_run ('SELECT','$value_qry')");
$value_result = $value_data->result();
$value_data->next_result();
if($value_result){
foreach($value_result[0] as $column_name => $value){
$current_value = $value;
$new_value = $value;
$diff_value = $new_value - $current_value;
$exist_qry = 'SELECT count(*) as count from cw_increment where apply_on = "'.$apply_month.'" and employee_code = "'.$employee_code.'" and column_name = "'.$column_name.'" and trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$exist_data->next_result();
$exist_rows = $exist_result[0]->count;
if((int)$exist_rows === 0){
$insert_query = 'INSERT into cw_increment (apply_on,category,employee_code,effective_date,process_by,before_day,after_day,column_name,current_value,new_value,difference_value,trans_created_by,trans_created_date) values ("'.$apply_month.'","'.$category.'","'.$employee_code.'","'.$effective_date.'","'.$process_by.'","'.$before_days.'","'.$after_day.'","'.$column_name.'","'.$current_value.'","'.$new_value.'","'.$diff_value.'","'.$this->logged_id.'","'.date('Y-m-d H:i:s').'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
}else{
$update_query = 'UPDATE cw_increment SET effective_date = "'. $effective_date .'",process_by = "'. $process_by .'",before_day = "'. $before_days .'",after_day = "'. $after_day .'",new_value = "'. $new_value .'",difference_value = "'. $diff_value .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE employee_code = "'. $employee_code .'" and apply_on = "'.$apply_month.'" and column_name = "'.$column_name.'"';
$update_result = $this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
}
}
}else{
echo json_encode(array('success' => false, 'message' => "Increment Columns Not Mapped"));
exit(0);
}
return $this->get_increment_data($category,$apply_month,$employee_code);
}else{
echo json_encode(array('total'=>0,'rows'=>0));
}
}
public function get_increment_data($category,$apply_month,$employee_code){
//Get DB Columns
$mapping_data = $this->Hr_methods_model->get_mapped_db_column(12);
$fixed_basic_db = $mapping_data['fixed_basic'];
$list_qry = 'SELECT prime_increment_id,column_name,current_value,difference_value,new_value,employee_code,apply_on,view_name,effective_date,after_day,process_by from cw_increment inner join cw_form_setting on cw_form_setting.label_name = cw_increment.column_name where apply_on = "'.$apply_month.'" and employee_code = "'.$employee_code.'" and cw_increment.trans_status = 1 group by label_name';
$list_data = $this->db->query("CALL sp_a_run ('SELECT','$list_qry')");
$list_result = $list_data->result();
$list_data->next_result();
$num_rows = $list_data->num_rows();
$tr_line = "";
$arrear_gross = 0;
$arrear_basic = 0;
foreach ($list_result as $table_info){
$diff_value = $table_info->difference_value;
$new_value = $table_info->new_value;
$arrear_gross = $arrear_gross + $new_value;
if($table_info->column_name === $fixed_basic_db){
$arrear_basic = $new_value;
}
$hid_column = "<input type='hidden' name = 'column_name[]' id='column_name' value='$table_info->column_name'/>";
$diff_hid = "<input type='hidden' name = 'diff_value[]' id='diff_value' value='$diff_value'/>";
$current_hid = "<input type='hidden' name = 'current_value[]' id='current_value' value='$table_info->current_value'/>";
$check_has_formula = $this->check_has_formula($table_info->column_name);
if((int)$check_has_formula === 0){
$readonly = "";
}else{
$readonly = "readonly";
}
$tr_line.= "<tr>$current_hid $hid_column $diff_hid<td>$table_info->employee_code</td><td>$table_info->apply_on</td><td>$table_info->view_name</td><td>$table_info->current_value</td><td style='width: 15%;'><input type='text' class='form-control input-sm increment_cal number' name = 'new_value[]' id='$table_info->column_name' onchange=update_table($category,$table_info->prime_increment_id,'$table_info->current_value','$table_info->employee_code','$table_info->apply_on','$table_info->column_name','$table_info->effective_date','$table_info->after_day') value='$table_info->new_value' $readonly></td><td>$diff_value</td></tr>";
$btn = "<button class='btn btn-primary btn-sm' id='save_increment' onclick=save_increment($category,$table_info->prime_increment_id,'$table_info->employee_code','$table_info->apply_on','$table_info->effective_date','$table_info->after_day','$arrear_basic','$arrear_gross',$table_info->process_by) >Save</button>";
}
$table_data = "<div class='row' style='padding: 10px;'><div class='form-group'>
$btn
</div><table id='inc_table' class='table table hover' style='width:100% !important;'>
<thead>
<th>Employee Code</th>
<th>Apply Month</th>
<th>Column Name</th>
<th> Current Value</th>
<th>New Value</th>
<th>Differences</th>
</thead>
<tbody>$tr_line</tbody>
</table></div>";
echo json_encode(array('success'=>true,'table_data'=>$table_data));
}
public function update_table(){
$prime_id = $this->input->post('prime_id');
$new_value = $this->input->post('new_value');
$diff_value = $this->input->post('diff_value');
$apply_month = $this->input->post('apply_month');
$employee_code = $this->input->post('employee_code');
$category = $this->input->post('category');
$created_on = date("Y-m-d h:i:s");
$upd_query = 'UPDATE cw_increment SET new_value = "'. $new_value .'",difference_value = "'. $diff_value .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE prime_increment_id = "'. $prime_id .'"';
$upd_result = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
if($upd_result){
return $this->get_increment_data($category,$apply_month,$employee_code);
}
}
//Calculate Arrear values and store database
public function save_increment(){
$trans_array=array();
$prime_id = $this->input->post('prime_id');
$new_array = $this->input->post('new_value');
$current_array = $this->input->post('current_value');
$diff_array = $this->input->post('diff_value');
$employee_code = $this->input->post('employee_code');
$apply_month = $this->input->post('apply_on');
$trans_column = $this->input->post('column_name');
$effective_date = $this->input->post('effective_date');
$after_day = $this->input->post('after_day');
$category = $this->input->post('category');
$process_by = $this->input->post('process_by');
$created_on = date("Y-m-d h:i:s");
$effective = $this->input->post('effective_date'); //used for insert
//Employee Data
$emp_qry = 'SELECT metro FROM `cw_employees` WHERE employee_code ="'.$employee_code.'"';
$emp_qry_data = $this->db->query("CALL sp_a_run ('RUN','$emp_qry')");
$emp_qry_result = $emp_qry_data->result();
$emp_qry_data->next_result();
$metro = $emp_qry_result->metro;
$formula_exist_qry = 'SELECT count(*) as count FROM `cw_form_bind_input`
INNER JOIN `cw_form_condition_formula` ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id
WHERE input_cond_module_id ="employees" AND line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1 and cond_order != 0';
$formula_exist_data = $this->db->query("CALL sp_a_run ('RUN','$formula_exist_qry')");
$formula_exist_result = $formula_exist_data->result();
$formula_exist_data->next_result();
$formula_exist = $formula_exist_result[0]->count;
if((int)$formula_exist > 0){
$trans_array = $this->input->post('arr_for');
$trans_array['metro'] = $metro;
$new_array = $this->increment_formula($trans_array);
}
//print_r($new_array); die;
// Arrear Insert Data
$apply_date = date("d",strtotime($effective_date))."-".$apply_month;
$date = strtotime($apply_date);
$apply_date = date('Y-m-d', $date);
$arrear_value = 0;
$cumulate_arrear_value = 0;
$arrear_pf = 0;
$cumulate_arrear_pf = 0;
//Developer Understanding
/*$loc_column_list = array("earned_gross"=>"Earned Gross","paid_days"=>"Paid Days","month_days"=>"Month Days","fixed_basic"=>"Fixed Basic","fixed_gross"=>"Fixed Gross","professional_tax_amount"=>"Professional Tax","esi_loc"=>"ESI Location","esi_elig"=>"ESI Eligibility","arrear_gross"=>"Arrear Gross","arrear_pf_gross"=>"Arrear PF Gross");*/
$mapping_data = $this->Hr_methods_model->get_mapped_db_column(12);
$paid_days_db = $mapping_data['paid_days'];
$month_days_db = $mapping_data['month_days'];
$fixed_basic_db = $mapping_data['fixed_basic'];
$earned_basic_db = $mapping_data['earned_basic'];
$earned_gross_db = $mapping_data['earned_gross'];
$arrear_gross_db = $mapping_data['arrear_gross'];
$arrear_pf_gross_db = $mapping_data['arrear_pf_gross'];
//Arrear PF Gross Formula
$get_arrear_pf_gross_qry = 'select payroll_formula from cw_payroll_formula where formula_for = "'.$category.'" and out_column = "'.$arrear_pf_gross_db.'" and trans_status =1';
$arrear_pf_gross_data = $this->db->query("CALL sp_a_run ('SELECT','$get_arrear_pf_gross_qry')");
$arrear_pf_gross_result = $arrear_pf_gross_data->result();
$arrear_pf_gross_data->next_result();
$arrear_pf_gross_formula = $arrear_pf_gross_result[0]->payroll_formula;
//Get Transaction Columns
$get_trans_colums = 'SELECT GROUP_CONCAT(label_name) as label_name FROM cw_form_setting where arrear_pf_check = 1';
$trans_colums_data = $this->db->query("CALL sp_a_run ('SELECT','$get_trans_colums')");
$trans_colums_result = $trans_colums_data->result();
$trans_colums_data->next_result();
$trans_columns = explode(",",$trans_colums_result = $trans_colums_result[0]->label_name);
if($arrear_pf_gross_formula){
if(!$trans_colums_result){
echo json_encode(array('success' => false, 'message' => "Arrear PF Columns Not Mapped"));
exit(0);
}
}
//Get Last Month
$eff_date_format = date("Y-m-d",strtotime($effective_date));
$datestring= "$eff_date_format first day of last month";
$dt=date_create($datestring);
$prev_month = $dt->format('Y-m');
$eff_date = date("d",strtotime($effective_date));
$eff_month = date("m-Y",strtotime($effective_date));
$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){
$sal_start = $day_start;
/*if($eff_date >= $sal_start){
$date_next_month = "$eff_date_format first day of next month";
$dt_next_month=date_create($date_next_month);
$next_month = $dt_next_month->format('Y-m');
//$effective_date = $next_month."-".$eff_date;
}*/
//For Current month between days increment
$date = new DateTime("01-$apply_month 00:00:00");
$date->modify('-1 month');
$salary_start_date = $date->format("Y-m-$sal_start");
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$apply_month));
}else{
$sal_start = '01';
$prev_month = date("Y-m",strtotime($eff_date_format));
$date = new DateTime("01-$apply_month 00:00:00");
$salary_start_date = $date->format("Y-m-$sal_start");
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$apply_month));
}
}
$diff_check = FALSE;
if($effective_date >= $salary_start_date && $effective_date < $salary_end_date){
$date = new DateTime("01-$apply_month 00:00:00");
$date->modify('+1 month');
$apply_date = $date->format("Y-m-$sal_start");
if($eff_month !== $apply_month){
//Effective Date
$prev_month = date("Y-m",strtotime($eff_date_format));
$date_next_month = "$eff_date_format first day of next month";
$dt_next_month=date_create($date_next_month);
$next_month = $dt_next_month->format('Y-m');
$effective_date = $next_month."-".$eff_date;
}
$diff_check = TRUE;
}
$months = $this->get_months($apply_date,$effective_date);
//if($eff_month === $apply_month){
$prev_date = $prev_month."-".$sal_start;
$array_cum = array();
$i = 0;
$arr = array();
//column and month wise insert and update
foreach ($trans_column as $column_name){
$cumulate_arrear_value = 0;
$cumulate_arrear_pf = 0;
$arrear_basic = 0;
$arrear_gross = 0;
foreach ($months as $dt){
$current_value = $current_array[$i];
if((int)$formula_exist > 0){
$new_value = $new_array[$column_name];
$diff_value = ($new_value) - ($current_value);
}else{
$new_value = $new_array[$i];
$diff_value = $diff_array[$i];
}
$month = $dt->format("m-Y");
$arr_date = $eff_date."-".$month;
//,'.$month_days_db.'
$transaction_qry = 'select '.$paid_days_db.','.$earned_gross_db.','.$earned_basic_db.' from cw_transactions where transactions_month = "'.$month.'" and employee_code = "'.$employee_code.'" and trans_status = 1';
$transaction_data = $this->db->query("CALL sp_a_run ('SELECT','$transaction_qry')");
$transaction_result = $transaction_data->result();
$transaction_data->next_result();
$paid_days = $transaction_result[0]->$paid_days_db;
$month_days = $this->Process_payroll_model->get_total_work_days($category,$month);
//echo "BSK $eff_date :: $eff_month === $month :: $prev_date, $eff_date_format"; die;
if($eff_date !== "01"){
if($eff_month === $month){
if((int)$process_by === 1){
$lop_days = $this->dateDiff($prev_date, $eff_date_format);
if($sal_start === $eff_date){
$lop_days = $after_day;
}else{
$lop_days = $after_day + $lop_days;
}
$paid_days = $month_days - $lop_days;
}else{
$paid_days = $after_day;
}
}else{
if($diff_check){
$diff_days = $this->dateDiff($prev_date, $effective);
$paid_days = $month_days - $diff_days;
}else{
$paid_days = $paid_days;
}
}
}else{
if($eff_month === $month){
if((int)$process_by === 1){
$lop_days = $this->dateDiff($prev_date, $eff_date_format);
if($sal_start === $eff_date){
$lop_days = $after_day;
}else{
$lop_days = $after_day + $lop_days;
}
$paid_days = $month_days - $lop_days;
}else{
$paid_days = $after_day;
}
}else{
$paid_days = $paid_days;
}
}
$arrear_column_qry = 'select arrear_column from cw_arrear_column_mapping where transaction_column = "'.$column_name.'" and trans_status = 1';
$arrear_column_data = $this->db->query("CALL sp_a_run ('SELECT','$arrear_column_qry')");
$arrear_column_result = $arrear_column_data->result();
$arrear_column_data->next_result();
$arrear_column = $arrear_column_result[0]->arrear_column;
if($column_name === $fixed_basic_db){
$arrear_basic_db = $arrear_column;
}
$arrear_value = ((($diff_value * $paid_days) / $month_days));
//echo "BSK ($diff_value :: $paid_days :: $month_days :: $arrear_value"; die;
$arrear_date = date("Y-m-d",strtotime($arr_date));
$cumulate_arrear_value = (($cumulate_arrear_value + $arrear_value));
$arr[$month][$arrear_column] = $arrear_value;
if($arrear_column){
$exist_qry = 'SELECT * from cw_arrears where increment_apply_month = "'.$apply_month.'" and employee_code = "'.$employee_code.'" and transaction_column = "'.$column_name.'" and arrear_month = "'.$month.'"';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$exist_data->next_result();
$exist_rows = $exist_data->num_rows();
if((int)$exist_rows === 0){
$insert_query = 'insert into cw_arrears(increment_apply_month,category,employee_code,effective_date,transaction_column,arrear_column,arrear_month,arrear_value,paid_days,arrear_pf,new_value,old_value,arrear_date,trans_created_by,trans_created_date) value ("'.$apply_month.'","'.$category.'","'.$employee_code.'","'.$effective.'","'.$column_name.'","'.$arrear_column.'","'.$month.'","'.$arrear_value.'","'. $paid_days .'","'.$arrear_pf.'","'.$new_value.'","'.$current_value.'","'.$arrear_date.'","'.$this->logged_id.'","'.date('Y-m-d H:i:s').'")';
$insert_result = $this->db->query("CALL sp_a_run ('RUN','$insert_query')");
}else{
$upd_arrears_query = 'UPDATE cw_arrears SET paid_days = "'. $paid_days .'",arrear_pf = "'. $arrear_pf .'",new_value = "'. $new_value .'",arrear_value = "'. $arrear_value .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE employee_code = "'. $employee_code .'" and increment_apply_month = "'.$apply_month.'" and transaction_column = "'.$column_name.'" and arrear_date = "'.$arrear_date.'"';
$upd_arrears_result = $this->db->query("CALL sp_a_run ('UPDATE','$upd_arrears_query')");
}
}
}
if($arrear_column){
if((int)$exist_rows === 0){
$cumulate_query = 'insert into cw_arrear_cumulative(increment_apply_month,category,employee_code,effective_date,transaction_column,arrear_column,arrear_value,arrear_pf,payroll_done,trans_created_by,trans_created_date) value ("'.$apply_month.'","'.$category.'","'.$employee_code.'","'.$effective.'","'.$column_name.'","'.$arrear_column.'","'.$cumulate_arrear_value.'","'.$cumulate_arrear_pf.'","Y","'.$this->logged_id.'","'.date('Y-m-d H:i:s').'")';
$cumulate_result = $this->db->query("CALL sp_a_run ('RUN','$cumulate_query')");
}else{
$upd_cumulative_query = 'UPDATE cw_arrear_cumulative SET arrear_value = "'. $cumulate_arrear_value .'",arrear_pf = "'. $cumulate_arrear_pf .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE employee_code = "'. $employee_code .'" and increment_apply_month = "'.$apply_month.'" and transaction_column = "'.$column_name.'"';
$upd_cumulative_result = $this->db->query("CALL sp_a_run ('UPDATE','$upd_cumulative_query')");
}
}
$i++;
}
if($arrear_column){
//$result = $this->calculate_arrear_pf($category,$employee_code,$apply_month,$arrear_gross_db,$arrear_pf_gross_db,$new_value,$arr,$arrear_basic_db,$earned_basic_db,$earned_gross_db,$earned_hra_db,$trans_columns,$arrear_pf_gross_formula);
//if($result){
echo json_encode(array('success' => true, 'message' => "Increment Data Successfully Saved"));
//}else{
//echo json_encode(array('success' => false, 'message' => "Transaction Columns Not Mapped Correctly"));
//}
}
//echo json_encode(array('success' => true, 'message' => "Increment Data Successfully Saved"));
/*}else{
echo json_encode(array('success' => true, 'message' => "Increment Data Successfully Saved"));
} */
}
public function get_employee_list(){
$category = $this->input->post('category');
$emp_query = 'SELECT employee_code,emp_name FROM cw_employees WHERE role = "'.$category.'" and trans_status =1';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_query')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$emp_list = "<option value=''>---- Select Employee ----</option>";
foreach($emp_result as $result){
$id = $result->employee_code;
$name = ucwords($result->emp_name);
$emp_code = $result->employee_code;
$emp_list .= "<option value='$id'>$emp_code - $name</option>";
}
echo $emp_list;
}
public function get_lop_days(){
$category = $this->input->post('category');
$employee = $this->input->post('employee');
$process_by = $this->input->post('process_by');
$apply_month = $this->input->post('apply_month');
if((int)$process_by === 1){
$days_column = "lop_days";
}else{
$days_column = "paid_days";
}
$effective_date = date("Y-m-d",strtotime($this->input->post('effective_date')));
$eff_date = date("d",strtotime($this->input->post('effective_date')));
$salary_dates = $this->Process_payroll_model->get_salary_date($category);
$day_start = $salary_dates['day_start'];
$day_end = $salary_dates['day_end'];
$day_conditions = $salary_dates['day_conditions'];
if((int)$day_conditions === 3 && $eff_date >= $day_start){
$effective_month = date("m-Y", strtotime("+1 month", strtotime($this->input->post('effective_date'))));
}else{
$effective_month = date("m-Y",strtotime($this->input->post('effective_date')));
}
//Payroll Process Check AS Discussed with Radhika Mam on 03Sep2020 Removed this condition
/*$trans_check_qry = 'select * from cw_monthly_input where employee_code= "'.$employee.'" and trans_status=1 and process_month = "'.$effective_month.'"';
$trans_check_data = $this->db->query("CALL sp_a_run ('SELECT','$trans_check_qry')");
$trans_check_result = $trans_check_data->result();
$trans_check_data->next_result();
$trans_rslt_count = $trans_check_data->num_rows(); */
//Date Of Joining Check
$doj_check_qry = 'select count(*) as rslt_count from cw_employees where employee_code= "'.$employee.'" and trans_status=1 and date_of_joining <="'.$effective_date.'"';
$doj_check_data = $this->db->query("CALL sp_a_run ('SELECT','$doj_check_qry')");
$doj_check_result = $doj_check_data->result();
$doj_check_data->next_result();
$rslt_count = $doj_check_result[0]->rslt_count;
//Dynamic column list array
$get_column_qry = 'select db_column from cw_payroll_function_map where map_statutory_name= 12 and loc_name = "'.$days_column.'" 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();
$lop_days_db = $get_column_result[0]->db_column;
if(!$lop_days_db){
echo json_encode(array('success' => false, 'message' => "Lop Days / Paid Days Not Mapped in function column mapping in employee module"));
exit(0);
}
$lop_qry = 'SELECT '.$lop_days_db.' from cw_transactions where process_month = "'.$effective_month.'" and employee_code = "'.$employee.'" and trans_status = 1';
$lop_data = $this->db->query("CALL sp_a_run ('SELECT','$lop_qry')");
$lop_result = $lop_data->result();
$lop_data->next_result();
$lop_days = $lop_result[0]->$lop_days_db;
if(empty($lop_result)){
$lop_days = 0;
}
//if((int)$trans_rslt_count > 0){ AS Discussed with Radhika Mam on 03Sep2020 Removed this condition
if((int)$rslt_count === 1 ){
echo json_encode(array('success' => true, 'lop_days' => $lop_days));
}else{
echo json_encode(array('success' => false, 'message' => "Date of joining not greater than effective date "));
}
/*}else{
echo json_encode(array('success' => false, 'message' => "Month Input Not Processed for this Employee"));
} */
}
public function check_has_formula($column){
$input_query = 'SELECT * FROM cw_form_bind_input
INNER JOIN cw_form_condition_formula ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id
WHERE input_cond_module_id ="employees" AND cond_drop_down ="role" AND line_input_bind_to ="'.$column.'" AND cw_form_condition_formula.trans_status=1';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$num_rows = $input_data->num_rows();
return $num_rows;
}
/* incremrt formula start recheck by GS on 22july2019*/
public function increment_formula($trans_array){
$input_query = 'SELECT * FROM `cw_form_bind_input`
INNER JOIN `cw_form_condition_formula` ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id
WHERE input_cond_module_id ="employees" AND cond_order != 0 AND line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$input_colum = "";
foreach($input_result as $input){
$role = $input->line_input_bind_table;
$out_colum = $input->line_input_bind_to;
$input_colum = $input->line_input_bind_col;
$condition_check_form = $input->condition_check_form;
$condition_check_form = explode(",",$condition_check_form);
if($input_colum){
foreach($condition_check_form as $check_form){
if(strpos($input_colum,"@$check_form@") !== false){
$value = "\$trans_array['".$check_form."']";
$input_colum = str_replace("@$check_form@",$value, $input_colum);
$input_colum = str_replace("return","\$trans_array['".$out_colum."'] = ", $input_colum);
$data .= "'$check_form' => $value,";
}
}
}
//$formula_code .= "\n\t\t\t".'if((int)$trans_array["role"] === '.$role."){\n\t\t\t\t$input_colum\n\t\t\t}";
$formula_code .= "\n\t\t\t $input_colum \n\t\t\t";
}
$formula_code = "\n\t\t".' foreach($trans_array as $trans){ '.$formula_code."\n\t\t } return \$trans_array; \n\t\t";
$dynamic_file_name= "increment_".$this->logged_id.".php";
unlink("$dynamic_file_name");
$fname = "increment(\$trans_array)";
$code = "<?php function $fname{ $formula_code }?>";
fopen("$dynamic_file_name", "w");
file_put_contents("$dynamic_file_name",$code);
chmod($dynamic_file_name, 0777);
require_once("$dynamic_file_name");
$final_result_array = increment($trans_array);
unlink("$dynamic_file_name");
return $final_result_array;
}
public function get_employees_delete(){
$category = $this->input->post('category');
$apply_month = $this->input->post('apply_month');
$payroll_qry = 'SELECT * from cw_transactions where transactions_month = "'.$apply_month.'" and role = "'.$category.'" and trans_status = 1';
$payroll_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_qry')");
$payroll_result = $payroll_data->result();
$payroll_data->next_result();
$num_rows = $payroll_data->num_rows();
if((int)$num_rows > 0){
echo json_encode(array('success'=>false,'message'=>'Payroll Processed For this Month'));
}else{
$employee_qry = 'SELECT DISTINCT(cw_increment.employee_code) as employee_code,emp_name from cw_increment inner join cw_employees on cw_employees.employee_code = cw_increment.employee_code where apply_on = "'.$apply_month.'" and category = "'.$category.'" and cw_increment.trans_status = 1';
$list_data = $this->db->query("CALL sp_a_run ('SELECT','$employee_qry')");
$list_result = $list_data->result();
$list_data->next_result();
$tr_line = "";
$arrear_gross = 0;
$arrear_basic = 0;
foreach ($list_result as $table_info){
$employee_code = $table_info->employee_code;
$emp_name = $table_info->emp_name;
$tr_line.= "<tr><td width='140'><input type='checkbox' name = 'employees[]' id='employees' value='$employee_code'></td><td>$table_info->employee_code</td><td>$table_info->emp_name</td></tr>";
$btn = "<button class='btn btn-primary btn-sm' id='delete_increment' onclick=delete_increment('$category','$apply_month') >Delete</button>";
}
$table_data = "<div class='row' style='padding: 20px;'><div class='form-group'>
$btn
</div><table id='inc_table' class='table table hover' style='width:100% !important;'>
<thead>
<th><input type='checkbox' id='select_all'> Select All</th>
<th>Employee Code</th>
<th>Employee Name</th>
</thead>
<tbody>$tr_line</tbody>
</table></div>";
echo json_encode(array('success'=>true,'table_data'=>$table_data));
}
}
public function delete_increment(){
$category = $this->input->post('category');
$apply_month = $this->input->post('apply_month');
$employees = $this->input->post('employees');
foreach ($employees as $employee){
$qry = 'SELECT column_name,current_value from cw_increment where apply_on = "'.$apply_month.'" and category = "'.$category.'" and employee_code = "'.$employee.'" and cw_increment.trans_status = 1';
$increment_data = $this->db->query("CALL sp_a_run ('SELECT','$qry')");
$increment_result = $increment_data->result();
$increment_data->next_result();
foreach ($increment_result as $key => $value){
$update_qry = 'UPDATE cw_employees SET '.$value->column_name.' ="'.$value->current_value.'" where employee_code = "'.$employee.'" and trans_status = 1';
$update_result = $this->db->query("CALL sp_a_run ('RUN','$update_qry')");
}
$delete_qry = 'DELETE FROM cw_increment where apply_on = "'.$apply_month.'" and category = "'.$category.'" and employee_code = "'.$employee.'" and trans_status = 1';
$delete_result = $this->db->query("CALL sp_a_run ('RUN','$delete_qry')");
if($delete_result){
$arr_delete_qry = 'DELETE FROM cw_arrears where increment_apply_month = "'.$apply_month.'" and employee_code = "'.$employee.'" ';
$arr_delete_result = $this->db->query("CALL sp_a_run ('RUN','$arr_delete_qry')");
if($arr_delete_result){
$arrc_delete_qry = 'DELETE FROM cw_arrear_cumulative where increment_apply_month = "'.$apply_month.'" and employee_code = "'.$employee.'"';
$arrc_delete_result = $this->db->query("CALL sp_a_run ('RUN','$arrc_delete_qry')");
}
}
}
if($arrc_delete_qry){
echo json_encode(array('success'=>true,'message'=>'Successfully Deleted'));
}
}
/*public function check_payroll_exist(){
$category_del = $this->input->post('category_del');
$apply_month_del = $this->input->post('apply_month_del');
$payroll_qry = 'SELECT * from cw_transactions where transactions_month = "'.$apply_month_del.'" and role = "'.$category_del.'" and trans_status = 1';
$payroll_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_qry')");
$payroll_result = $payroll_data->result();
$payroll_data->next_result();
$num_rows = $payroll_data->num_rows();
if((int)$num_rows > 0){
echo json_encode(array('success'=>true,'message'=>'Payroll Processed For this Month'));
}else{
echo json_encode(array('success'=>false));
}
}*/
//IMPORT FILE VIEW INFORMATION
public function import(){
$data['module_id'] = $this->control_name;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$data['excel_cell_value'] = $this->get_excel_value(100);
$temp_format_qry = 'select prime_inc_temp_setting_id,template_name from cw_inc_temp_setting where trans_status = 1 and module_id="increment"';
$temp_format = $this->db->query("CALL sp_a_run ('SELECT','$temp_format_qry')");
$temp_result = $temp_format->result();
$temp_format->next_result();
$temp_format_drop[""] = "---- Template Format ----";
foreach($temp_result as $template){
$temp_format_id = $template->prime_inc_temp_setting_id;
$template_name = $template->template_name;
$temp_format_drop[$temp_format_id] = $template_name;
}
$data['temp_format_drop'] = $temp_format_drop;
$this->load->view("$this->control_name/import",$data);
}
//Excel ABC Generator Function
public function get_excel_value($tot_cell){
$excel = array(''=>'--- Excel cell value ---');
if((int)$tot_cell > 0){
for($i=0;$i<=$tot_cell;$i++){
$letter = $this->getNameFromNumber($i);
$excel[$letter] = $letter;
}
}
return $excel;
}
public function getNameFromNumber($num) {
$numeric = $num % 26;
$letter = chr(65 + $numeric);
$num2 = intval($num / 26);
if ($num2 > 0) {
return $this->getNameFromNumber($num2 - 1) . $letter;
} else {
return $letter;
}
}
//Sheet Name display in import page
public function sheet_name(){
$file_path = $this->input->post('file_path');
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
include($filename);
$excel_obj = PHPExcel_IOFactory::load($file_path);
$sheet_count = $excel_obj->getSheetCount();
$sheet_name = array();
for($i= 0; $i< $sheet_count; $i++){
$sheet = $excel_obj->getSheet($i);
$sheet_name[] = $sheet->getTitle();
}
echo json_encode(array('sheet_name' =>$sheet_name));
}
//Get Columns Data
public function get_columns_data(){
$inc_column_qry = 'select prime_form_id,label_name,view_name from cw_form_setting where prime_module_id = "employees" and increment_check = 1 and trans_status = 1';
$inc_column_data = $this->db->query("CALL sp_a_run ('SELECT','$inc_column_qry')");
$inc_column_result = $inc_column_data->result();
$inc_column_data->next_result();
foreach($inc_column_result as $column){
$label_name = $column->label_name;
$view_name = $column->view_name;
$excel_cell_value = $this->get_excel_value(100);
$column_name = form_input(array( 'name' =>"excel_line_column_name[]",'class' => 'form-control input-sm','value' =>$label_name,'type'=>'Hidden'));
$excel_cell_input = form_dropdown(array('onchange = map_check(this); name' =>$label_name,'id'=>'excel_line_value[]','class' => 'form-control input-sm map_check'), $excel_cell_value,$excel_line_value);
$tr_line .= "<tr>
<td>$column_name $view_name</td>
<td>$excel_cell_input</td>
</tr>";
}
echo "<table class='table table-bordered table-stripted'>
<tr class='inline_head'>
<th>Label Name</th>
<th>Excel Column</th>
</tr>
$tr_line
</table>";
}
//Get all the months from the effective date.
public function get_months($apply_date,$effective_date){
$start = new DateTime($effective_date);
$start->modify('first day of this month');
$end = new DateTime($apply_date);
$end->modify('first day of this month');
$interval = DateInterval::createFromDateString('1 month');
$period = new DatePeriod($start, $interval, $end);
return $period;
}
//get date difference_value
public function dateDiff($date1, $date2){
$date1_ts = strtotime($date1);
$date2_ts = strtotime($date2);
$diff = $date2_ts - $date1_ts;
return round($diff / 86400);
}
//Check Payroll is processed
public function check_payroll_process(){
$month = $this->input->post('apply_month');
$category = $this->input->post('category');
$employee = $this->input->post('employee');
$payroll_qry = 'SELECT * from cw_transactions where transactions_month = "'.$month.'" and role = "'.$category.'" and employee_code = "'.$employee.'" and trans_status = 1';
$payroll_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_qry')");
$payroll_result = $payroll_data->result();
$payroll_data->next_result();
$num_rows = $payroll_data->num_rows();
if((int)$num_rows >= 1){
echo json_encode(array('success' => true, 'message' => "Payroll Processed For this Month"));
}else{
echo json_encode(array('success' => false));
}
}
public function check_template_details(){
$category = $this->input->post('category');
$sel_format_qry = 'select prime_inc_temp_setting_id,template_name from cw_inc_temp_setting inner join cw_increment_template on cw_increment_template.temp_name=cw_inc_temp_setting.prime_inc_temp_setting_id where category = "'.$category.'" and cw_increment_template.trans_status= 1 group by template_name';
$sel_format = $this->db->query("CALL sp_a_run ('SELECT','$sel_format_qry')");
$sel_result = $sel_format->result();
$sel_format->next_result();
echo json_encode(array('success' => true, 'sel_result' => $sel_result));
}
public function save_inc_import(){
$category = $this->input->post('category');
$template_name = $this->input->post('template_name');
$apply_month = $this->input->post('apply_month');
$process_by = $this->input->post('process_by');
$excel_file_path = $this->input->post('excel_file_path');
$excel_sheet_name = $this->input->post('excel_sheet_name');
$excel_start_row = $this->input->post('excel_start_row');
$excel_end_row = $this->input->post('excel_end_row');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$import_query = 'insert into cw_inc_import (category,template_name,apply_month,excel_file_path,process_by,excel_sheet_name,excel_start_row,excel_end_row,trans_created_by,trans_created_date) value ("'.$category.'","'.$template_name.'","'.$apply_month.'","'.$excel_file_path.'","'.$process_by.'","'.$excel_sheet_name.'","'.$excel_start_row.'","'.$excel_end_row.'","'.$logged_id.'","'.$today_date.'")';
$import_info = $this->db->query("CALL sp_a_run ('INSERT','$import_query')");
$import_result = $import_info->result();
$import_info->next_result();
$import_id = $import_result[0]->ins_id;
echo $this->do_excel_import($import_id);
}
//need to updates
public function do_excel_import($import_id){
$logged_id = $this->session->userdata('logged_id');
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
include($filename);
if($import_id < 0){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}
$excel_path_qry = 'select * from cw_inc_import where import_id = "'.$import_id.'"';
$excel_path_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_path_qry')");
$excel_path_result = $excel_path_info->result();
$excel_path_info->next_result();
if(!$excel_path_result){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}else{
$category = $excel_path_result[0]->category;
$excel_format = $excel_path_result[0]->template_name;
$apply_month = $excel_path_result[0]->apply_month;
$process_by = $excel_path_result[0]->process_by;
$excel_file_path = $excel_path_result[0]->excel_file_path;
$excel_sheet_name = (int)$excel_path_result[0]->excel_sheet_name;
$excel_row_start = (int)$excel_path_result[0]->excel_start_row;
$excel_row_end = (int)$excel_path_result[0]->excel_end_row;
$formula_exist_qry = 'SELECT count(*) as formula_count FROM `cw_form_bind_input` INNER JOIN `cw_form_condition_formula` ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id WHERE input_cond_module_id ="employees" AND cond_drop_down ="role" AND line_input_bind_table = "'.$category.'" AND line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1';
$formula_exist_data = $this->db->query("CALL sp_a_run ('RUN','$formula_exist_qry')");
$formula_exist_result = $formula_exist_data->result();
$formula_exist_data->next_result();
$formula_exist = $formula_exist_result[0]->formula_count;
//Increment Columns
$inc_column_qry = 'select GROUP_CONCAT(label_name) as column_name from cw_form_setting where prime_module_id = "employees" and increment_check = 1 and trans_status = 1';
$inc_column_data = $this->db->query("CALL sp_a_run ('SELECT','$inc_column_qry')");
$inc_column_result = $inc_column_data->result();
$inc_column_data->next_result();
$inc_columns = $inc_column_result[0]->column_name;
$inc_count = explode(",",$inc_columns);
$inc_count = count($inc_count);
//arrear mapping count
$arrear_column_qry = 'select count(*) as arrear_count from cw_arrear_column_mapping where trans_status = 1';
$arrear_column_data = $this->db->query("CALL sp_a_run ('SELECT','$arrear_column_qry')");
$arrear_column_result = $arrear_column_data->result();
$arrear_column_data->next_result();
$arrear_count = $arrear_column_result[0]->arrear_count;
/*if($inc_count !== $arrear_count){
echo json_encode(array('success' => false, 'message' => "Please Map the Columns in Settings Arrear Column Mapping"));
exit(0);
}*/
//Arrear PF Gross Formula
$get_arrear_pf_gross_qry = 'select payroll_formula from cw_payroll_formula where formula_for = "'.$category.'" and out_column = "'.$arrear_pf_gross_db.'" and trans_status =1';
$arrear_pf_gross_data = $this->db->query("CALL sp_a_run ('SELECT','$get_arrear_pf_gross_qry')");
$arrear_pf_gross_result = $arrear_pf_gross_data->result();
$arrear_pf_gross_data->next_result();
$arrear_pf_gross_formula = $arrear_pf_gross_result[0]->payroll_formula;
//Get Transaction Columns
$get_trans_colums = 'SELECT GROUP_CONCAT(label_name) as label_name FROM cw_form_setting where arrear_pf_check = 1';
$trans_colums_data = $this->db->query("CALL sp_a_run ('SELECT','$get_trans_colums')");
$trans_colums_result = $trans_colums_data->result();
$trans_colums_data->next_result();
$trans_columns = explode(",",$trans_colums_result = $trans_colums_result[0]->label_name);
$error_data = array('paid_days'=>'Paid Days','month_days'=>'Month Days','fixed_basic'=>'Fixed Basic','earned_basic'=>'Earned Basic','earned_gross'=>'Earned Gross','arrear_gross'=>'Arrear Gross','arrear_pf_gross'=>'Arrear PF Gross');
//Dynamic column list array to get column name
$get_column_qry = 'select loc_name,db_column from cw_payroll_function_map where map_statutory_name= 12 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();
$error_data = array();
if($get_column_result){
foreach($get_column_result as $column_name){
$loc_name = $column_name->loc_name;
$db_column = $column_name->db_column;
if($loc_name == "paid_days"){
$paid_days_db = $db_column;
unset($error_data[$loc_name]);
}
if($loc_name == "month_days"){
$month_days_db = $db_column;
unset($error_data[$loc_name]);
}
if($loc_name == "fixed_basic"){
$fixed_basic_db = $db_column;
unset($error_data[$loc_name]);
}
if($loc_name == "earned_basic"){
$earned_basic_db = $db_column;
unset($error_data[$loc_name]);
}
if($loc_name == "earned_gross"){
$earned_gross_db = $db_column;
unset($error_data[$loc_name]);
}
if($loc_name == "arrear_gross"){
$arrear_gross_db = $db_column;
unset($error_data[$loc_name]);
}
if($loc_name == "arrear_pf_gross"){
$arrear_pf_gross_db = $db_column;
unset($error_data[$loc_name]);
}
}
}
if(count($error_data) > 0){
$error_data = implode(",",$error_data);
echo json_encode(array('success' => false, 'message' => "Please Map the Columns '$error_data' in Module Settings Function Column Mapping"));
exit(0);
}
if($arrear_pf_gross_formula){
if(!$trans_colums_result){
echo json_encode(array('success' => false, 'message' => "Please Map the Arrear PF Columns for Form Inputs"));
exit(0);
}
}
//Get Salary 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 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();
//Excel Query
$format_qry = 'select employee_code,effective_date,before_day,after_day,GROUP_CONCAT(column_name) as column_name, GROUP_CONCAT(column_map) as column_map from cw_increment_template where temp_name = "'.$excel_format.'" and category = "'.$category.'" and trans_status = 1';
$format_info = $this->db->query("CALL sp_a_run ('SELECT','$format_qry')");
$format_rslt = $format_info->result();
$format_info->next_result();
$employee_code_column = $format_rslt[0]->employee_code;
$effective_date_column = $format_rslt[0]->effective_date;
$before_day_column = $format_rslt[0]->before_day;
$after_day_column = $format_rslt[0]->after_day;
$column_name = explode(",",$format_rslt[0]->column_name);
$column_map = explode(",",$format_rslt[0]->column_map);
$column_value = array_combine($column_name,$column_map);
if(!$format_rslt){
return json_encode(array('success' => false, 'message' => "Please add excel format before import"));
}else{
try{
$excel_obj = PHPExcel_IOFactory::load($excel_file_path);
}catch(Exception $e){
die('Error loading file "' . pathinfo($excel_file_path, PATHINFO_BASENAME). '": ' . $e->getMessage());
return json_encode(array('success' => false, 'message' => "Invalid file or path"));
}
$sheet = $excel_obj->getSheet($excel_sheet_name);
if($excel_row_end){
$total_rows = $excel_row_end;
}else{
$total_rows = $sheet->getHighestRow();
}
$imp_start = 1;
for($row = $excel_row_start; $row <= $total_rows; $row++){
$employee_code = trim($sheet->getCell("$employee_code_column$row")->getCalculatedValue());
$effective_date = trim(date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell("$effective_date_column$row")->getCalculatedValue())));
$before_days = trim($sheet->getCell("$before_day_column$row")->getCalculatedValue());
$after_days = trim($sheet->getCell("$after_day_column$row")->getCalculatedValue());
$effective_month = date("m-Y",strtotime($effective_date));
//Table Data
$table_data = array(
'apply_on' => $apply_month,
'category' => $category,
'employee_code' => $employee_code,
'effective_date' => $effective_date,
'before_day' => $before_days,
'process_by' => $process_by,
'after_day' => $after_days
);
//DOJ Check
$doj_check_qry = 'select count(*) as rslt_count from cw_employees where employee_code= "'.$employee_code.'" and trans_status= 1 and date_of_joining <="'.$effective_date.'"';
$doj_check_data = $this->db->query("CALL sp_a_run ('SELECT','$doj_check_qry')");
$doj_check_result = $doj_check_data->result();
$doj_check_data->next_result();
$rslt_count = $doj_check_result[0]->rslt_count;
//$column_name
if((int)$rslt_count === 1){
$value_qry = 'SELECT '.$inc_columns.' from cw_employees where employee_code = "'.$employee_code.'" and trans_status = 1';
$value_data = $this->db->query("CALL sp_a_run ('SELECT','$value_qry')");
$value_result = $value_data->result();
$value_data->next_result();
$numrows = $value_data->num_rows();
$column_array = array();
$new_array = array();
$current_array = array();
$diff_array = array();
$trans_array = array();
$invalid_effective_date = array();
$current_value = 0;
$new_value = 0;
$diff_value = 0;
$tbl_data = "";
if((int)$numrows !== 0){
foreach($value_result[0] as $colum_key => $current_value){
$colum_val = $column_value[$colum_key];
$diff_value = 0;
if($colum_val){
$new_value = trim($sheet->getCell("$colum_val$row")->getCalculatedValue());
if($new_value > 0){
$diff_value = $new_value - $current_value;
}else{
$diff_value = 0;
}
}else{
$new_value = 0;
}
$column_array[] = $colum_key;
$new_array[$colum_key] = $new_value;
$current_array[$colum_key] = $current_value;
$diff_array[$colum_key] = $diff_value;
if($new_value){
$trans_array[$employee_code][$colum_key] = $new_value;
}else{
$trans_array[$employee_code][$colum_key] = $current_value;
}
$table_data['column_name'] = $colum_key;
$table_data['current_value'] = $current_value;
$table_data['new_value'] = $new_value;
$table_data['difference_value'] = $diff_value;
$tbl_data = json_encode($table_data);
//SAVE Increment
$insert_result = $this->db->query("CALL sp_increment ('SAVE_INCREMENT', '$tbl_data','$logged_id')");
$result = $insert_result->result();
$insert_result->next_result();
}
if((int)$formula_exist > 0){
$trans_array[$employee_code]['role'] = $category;
//print_r($trans_array); die;
$new_array = $this->Increment_calculation_model->increment_calculation($trans_array,$employee_code);
$new_array = $new_array[$employee_code];
$unset_array = array_diff_key($new_array,$current_array);
foreach($unset_array as $unset => $val){
unset($new_array[$unset]);
}
$subtracted = array_map(function ($x, $y){
return $y-$x; } , $current_array, $new_array);
$diff_array = array_combine(array_keys($current_array), $subtracted);
}
if($insert_result){
$rslt = $this->arrear_insert($trans_columns,$month_day_result,$get_column_result,$category,$apply_month,$employee_code,$effective_date,$column_array,$arrear_column,$diff_array,$current_array,$new_array,$after_days,$process_by,$arrear_pf_gross_formula);
}
}
}else{
$invalid_effective_date[] = $employee_code;
$inv_effective_code = implode(",",$invalid_effective_date);
}
$imp_start++;
}
}
}
if(!empty($inv_effective_code)){
echo json_encode(array('success' => true, 'message' => "Increment Data Successfully Saved and this employee code $inv_effective_code people date of joining is not greater than effective date"));
}else{
echo json_encode(array('success' => true, 'message' => "Increment Data Successfully Saved"));
}
}
//Update increment values
public function arrear_insert($trans_columns,$month_day_result,$get_column_result,$category,$apply_month,$employee_code,$effective_date,$column_array,$arrear_column,$diff_array,$current_array,$new_array,$after_days,$process_by,$arrear_pf_gross_formula){
$logged_id = $this->session->userdata('logged_id');
$created_on = date("Y-m-d h:i:s");
// Arrear Insert Data
$apply_date = date("d",strtotime($effective_date))."-".$apply_month;
$date = strtotime($apply_date);
$apply_date = date('Y-m-d', $date);
$arrear_value = 0;
$cumulate_arrear_value = 0;
$arrear_pf = 0;
$cumulate_arrear_pf = 0;
//Get Last Month
$eff_date_format = date("Y-m-d",strtotime($effective_date));
$datestring= "$eff_date_format first day of last month";
$dt=date_create($datestring);
$prev_month = $dt->format('Y-m');
$eff_date = date("d",strtotime($effective_date));
$eff_month = date("m-Y",strtotime($effective_date));
//Dynamic column list array to get column name
//$loc_column_list = array("earned_gross"=>"Earned Gross","paid_days"=>"Paid Days","month_days"=>"Month Days","fixed_basic"=>"Fixed Basic","fixed_gross"=>"Fixed Gross","professional_tax_amount"=>"Professional Tax","esi_loc"=>"ESI Location","esi_elig"=>"ESI Eligibility","arrear_gross"=>"Arrear Gross","arrear_pf_gross"=>"Arrear PF Gross");
if($get_column_result){
foreach($get_column_result as $column_name){
$loc_name = $column_name->loc_name;
$db_column = $column_name->db_column;
if($loc_name == "paid_days"){
$paid_days_db = $db_column;
}
if($loc_name == "month_days"){
$month_days_db = $db_column;
}
if($loc_name == "fixed_basic"){
$fixed_basic_db = $db_column;
}
if($loc_name == "earned_basic"){
$earned_basic_db = $db_column;
}
if($loc_name == "earned_gross"){
$earned_gross_db = $db_column;
}
if($loc_name == "arrear_gross"){
$arrear_gross_db = $db_column;
}
if($loc_name == "arrear_pf_gross"){
$arrear_pf_gross_db = $db_column;
}
}
}
//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){
$sal_start = $day_start;
if($eff_date >= $sal_start){
$date_next_month = "$eff_date_format first day of next month";
$dt_next_month=date_create($date_next_month);
$next_month = $dt_next_month->format('Y-m');
$effective_date = $next_month."-".$eff_date;
}
}else{
$sal_start = '01';
$prev_month = date("Y-m",strtotime($eff_date_format));
}
if($eff_month !== $apply_month){
$months = $this->get_months($apply_date,$effective_date);
$prev_date = $prev_month."-".$sal_start;
//Table Data
$table_data = array(
'apply_on' => $apply_month,
'employee_code' => $employee_code,
'effective_date' => $effective_date
);
$array_cum = array();
$i = 0;
$arr = array();
$tbl_data = "";
//column and month wise insert and update
foreach ($column_array as $column_name){
$cumulate_arrear_value = 0;
$cumulate_arrear_pf = 0;
$arrear_basic = 0;
$arrear_gross = 0;
$paid_days = 0;
foreach ($months as $dt){
$diff_value = $diff_array[$column_name];
$new_value = $new_array[$column_name];
$current_value = $current_array[$column_name];
$month = $dt->format("m-Y");
$arr_date = $eff_date."-".$month;
//Get Transaction Data from every month
$transaction_qry = 'select '.$paid_days_db.','.$month_days_db.','.$earned_gross_db.','.$earned_basic_db.' from cw_transactions where transactions_month = "'.$month.'" and employee_code = "'.$employee_code.'" and trans_status = 1';
$transaction_data = $this->db->query("CALL sp_a_run ('SELECT','$transaction_qry')");
$transaction_result = $transaction_data->result();
$transaction_data->next_result();
$paid_days = $transaction_result[0]->$paid_days_db;
$month_days = $transaction_result[0]->$month_days_db;
if($eff_date !== "01"){
if($eff_month === $month){
if((int)$process_by === 1){
$lop_days = $this->dateDiff($prev_date, $eff_date_format);
if($sal_start === $eff_date){
$lop_days = $after_days;
}else{
$lop_days = $after_days + $lop_days;
}
$paid_days = $month_days - $lop_days;
}else{
$paid_days = $after_days;
}
}else{
$paid_days = $paid_days;
}
}else{
if($eff_month === $month){
if((int)$process_by === 1){
$lop_days = $this->dateDiff($prev_date, $eff_date_format);
if($sal_start === $eff_date){
$lop_days = $after_days;
}else{
$lop_days = $after_days + $lop_days;
}
$paid_days = $month_days - $lop_days;
}else{
$paid_days = $after_days;
}
}else{
$paid_days = $paid_days;
}
}
$arrear_column_qry = 'select arrear_column from cw_arrear_column_mapping where transaction_column = "'.$column_name.'" and trans_status = 1';
$arrear_column_data = $this->db->query("CALL sp_a_run ('SELECT','$arrear_column_qry')");
$arrear_column_result = $arrear_column_data->result();
$arrear_column_data->next_result();
$arrear_column = $arrear_column_result[0]->arrear_column;
if($column_name === $fixed_basic_db){
$arrear_basic_db = $arrear_column;
}
if($arrear_column){
$arrear_value = (($diff_value * $paid_days) / $month_days);
$arrear_date = date("Y-m-d",strtotime($arr_date));
$cumulate_arrear_value = $cumulate_arrear_value + $arrear_value;
$arr[$month][$arrear_column] = $arrear_value;
$table_data['category'] = $category;
$table_data['transaction_column'] = $column_name;
$table_data['arrear_column'] = $arrear_column;
$table_data['arrear_month'] = $month;
$table_data['arrear_value'] = $arrear_value;
$table_data['paid_days'] = $paid_days;
$table_data['new_value'] = $new_value;
$table_data['arrear_pf'] = $arrear_pf;
$table_data['current_value'] = $current_value;
$table_data['arrear_date'] = $arrear_date;
$tbl_data = json_encode($table_data);
//SAVE Increment
$arrear_qry = $this->db->query("CALL sp_increment ('SAVE_ARREARS', '$tbl_data','$logged_id')");
$arrear_result = $arrear_qry->result();
$arrear_qry->next_result();
}
}
if($arrear_column){
$table_data['payroll_done'] = "Y";
$table_data['cumulate_arrear_value'] = $cumulate_arrear_value;
$table_data['cumulate_arrear_pf'] = $cumulate_arrear_pf;
$tbl_data = json_encode($table_data);
//SAVE Increment
$arrear_cumulate_qry = $this->db->query("CALL sp_increment ('SAVE_CUMULATIVE', '$tbl_data','$logged_id')");
$arrear_cumulate_result = $arrear_cumulate_qry->result();
$arrear_cumulate_qry->next_result();
}
$i++;
}
$result = $this->calculate_arrear_pf($trans_columns,$category,$employee_code,$apply_month,$arrear_gross_db,$arrear_pf_gross_db,$arr,$arrear_basic_db,$arrear_pf_gross_formula);
if($result){
return $result;
}
}else{
return true;
}
}
//Arrear PF Calculation
public function calculate_arrear_pf($trans_columns,$category,$employee_code,$apply_month,$arrear_gross_db,$arrear_pf_gross_db,$arr,$arrear_basic_db,$arrear_pf_gross_formula){
$created_on = date("Y-m-d h:i:s");
//Arrear Gross Formula
$get_arrear_gross_qry = 'select payroll_formula from cw_payroll_formula where formula_for = "'.$category.'" and out_column = "'.$arrear_gross_db.'" and trans_status =1';
$arrear_gross_data = $this->db->query("CALL sp_a_run ('SELECT','$get_arrear_gross_qry')");
$arrear_gross_result = $arrear_gross_data->result();
$arrear_gross_data->next_result();
$arrear_gross_formula = $arrear_gross_result[0]->payroll_formula;
$i = 1; //for generate dynamic file name and function name
$cumulate_pf = 0;
$cumulate_arrear_gross = 0;
$arrear_gr = 0;
foreach ($arr as $arrear_month => $value){
$pf_array = array();
$code = "";
$pf_calculation = $arrear_pf_gross_formula;
$arrear_gross = $arrear_gross_formula;
if($trans_columns){
foreach ($trans_columns as $trans_column){
if($trans_column){
$transaction_qry = 'SELECT '.$trans_column.' from cw_transactions where transactions_month = "'.$arrear_month.'" and employee_code = "'.$employee_code.'" and trans_status = 1';
$transaction_data = $this->db->query("CALL sp_a_run ('SELECT','$transaction_qry')");
$transaction_result = $transaction_data->result();
$transaction_data->next_result();
$trans_value = $transaction_result[0]->$trans_column;
$pf_calculation = str_replace("@".$trans_column."@", $trans_value, $pf_calculation);
}
}
}else{
return false;
}
foreach ($value as $column => $arrear_value){
if($column === $arrear_basic_db){
$arrear_basic = $arrear_value;
}
$change_column = "@".$column."@";
if($column !== $arrear_gross_db){
$arrear_gr = $arrear_value;
}
//$arrear_gross = str_replace($change_column,$arrear_value,$arrear_gross);
$pf_calculation = str_replace($change_column, $arrear_value, $pf_calculation);
}
$rand = (rand(1,100));
$pf_calculation = str_replace("@".$arrear_gross_db."@",$arrear_gross,$pf_calculation);
//$arrear_gross = "return $arrear_gross;";
$filename = "pf_calculation_file_".$rand.".php";
$pf_calculation_func = "calculation_".$rand;
//$arrear_gross_func = "gross_func_".$rand;
$code = "<?php function $pf_calculation_func(){ $pf_calculation } function $arrear_gross_func(){ $arrear_gross } ?>";
$code = "<?php function $pf_calculation_func(){ $pf_calculation } ?>";
fopen($filename, "w");
file_put_contents($filename,$code);
chmod($filename, 0777);
require_once($filename);
//$arrear_gr = 0;
$cumulate_arrear_gross = 0;
$pf_value = $pf_calculation_func();
//$arrear_gr = $arrear_gross_func();
$cumulate_pf = $cumulate_pf + $pf_value;
$cumulate_arrear_gross = $cumulate_arrear_gross + $arrear_gr;
$upd_arrears_query = 'UPDATE cw_arrears SET arrear_value = "'. $arrear_gr .'",arrear_pf = "'. $pf_value .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE employee_code = "'. $employee_code .'" and increment_apply_month = "'.$apply_month.'" and arrear_column = "'.$arrear_gross_db.'" and arrear_month = "'.$arrear_month.'"';
$upd_arrears_result = $this->db->query("CALL sp_a_run ('UPDATE','$upd_arrears_query')");
$i++;
unlink("$filename");
}
if($upd_arrears_result){
$upd_cumulative_query = 'UPDATE cw_arrear_cumulative SET arrear_value = "'. $cumulate_arrear_gross .'",arrear_pf = "'. $cumulate_pf .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'. $created_on .'" WHERE employee_code = "'. $employee_code .'" and increment_apply_month = "'.$apply_month.'" and arrear_column = "'.$arrear_gross_db.'"';
$upd_cumulative_result = $this->db->query("CALL sp_a_run ('UPDATE','$upd_cumulative_query')");
return true;
}
return true;
}
//CREATE_FORMULA_FILE
public function create_formula_file(){
//file path with server
$filename = dirname(__FILE__)."/"."Increment_calculation_model.php";
$filename = str_replace('controllers','models',$filename);
$can_process = false;
$input_query = 'SELECT line_input_bind_table,line_input_bind_to,line_input_bind_col,condition_check_form FROM `cw_form_bind_input` INNER JOIN `cw_form_condition_formula` ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id
WHERE input_cond_module_id ="employees" AND cond_order != 0 AND line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$input_colum = "";
foreach($input_result as $input){
$role = $input->line_input_bind_table;
$out_colum = $input->line_input_bind_to;
$input_colum = $input->line_input_bind_col;
$condition_check_form = $input->condition_check_form;
$condition_check_form = explode(",",$condition_check_form);
if($input_colum){
foreach($condition_check_form as $check_form){
if(strpos($input_colum,"@$check_form@") !== false){
$value = "\$trans['".$check_form."']";
$input_colum = str_replace("@$check_form@",$value, $input_colum);
$input_colum = str_replace("return","\$trans['".$out_colum."'] = ", $input_colum);
$data .= "'$check_form' => $value,";
}
}
}
$formula_code .= "\n\t\t\t $input_colum \n\t\t\t";
}
$fname = 'increment_calculation($trans_array,$employee_code){';
//$emp_code = "\n\t\t\t".'$employee_code = "'.$employee_code.'";';
$formula_code = "\n<?php class Increment_calculation_model extends CI_Model{\n\tpublic function $fname \n\t\t".' foreach($trans_array as $trans){ '.$formula_code."\n\t\t \$trans_array[\$employee_code] = \$trans;\n\t\t } return \$trans_array; \n\t\t }\n}?>";
$formula_temp_file = dirname(__FILE__)."/"."Increment_calculation_model.php";
$formula_temp_file = str_replace('controllers','models',$formula_temp_file);
fopen("$formula_temp_file", "w");
file_put_contents("$formula_temp_file",$formula_code);
chmod($formula_temp_file, 0777);
}
/*public function increment_formula($trans_array,$category,$employee_code){
$input_query = 'SELECT * FROM `cw_form_bind_input`
INNER JOIN `cw_form_condition_formula` ON cw_form_condition_formula.prime_cond_id = cw_form_bind_input.input_cond_id
WHERE input_cond_module_id ="employees" AND cond_drop_down ="role" AND line_input_bind_col !="" AND cw_form_condition_formula.trans_status=1';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$input_colum = "";
foreach($input_result as $input){
$role = $input->line_input_bind_table;
$out_colum = $input->line_input_bind_to;
$input_colum = $input->line_input_bind_col;
$condition_check_form = $input->condition_check_form;
$condition_check_form = explode(",",$condition_check_form);
if($input_colum){
foreach($condition_check_form as $check_form){
if(strpos($input_colum,"@$check_form@") !== false){
$value = "\$trans['".$check_form."']";
$input_colum = str_replace("@$check_form@",$value, $input_colum);
$input_colum = str_replace("return","\$trans['".$out_colum."'] = ", $input_colum);
$data .= "'$check_form' => $value,";
}
}
}
$formula_code .= "\n\t\t\t".'if((int)$trans["role"] === '.$category."){\n\t\t\t\t$input_colum\n\t\t\t}";
}
$emp_code = "\n\t\t\t".'$employee_code = "'.$employee_code.'";';
$formula_code = "\n\t\t".' foreach($trans_array as $trans){ '.$emp_code.$formula_code."\n\t\t \$trans_array[\$employee_code] = \$trans;\n\t\t } return \$trans_array; \n\t\t";
$dynamic_file_name = "increment_".$employee_code.".php";
unlink("$dynamic_file_name");
$fname = "increment_$employee_code";
$code = "<?php function $fname(\$trans_array){ $formula_code }?>";
fopen("$dynamic_file_name", "w");
file_put_contents("$dynamic_file_name",$code);
require_once("$dynamic_file_name");
$final_result_array = $fname($trans_array);
unlink("$dynamic_file_name");
return $final_result_array;
}*/
}
?>