File: //home/cafsindia/hrms_patroniss_com/application/controllers/Increment_import.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Base_controller.php");
class Increment_import extends Base_controller{
public function __construct(){
parent::__construct('increment_import');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->collect_base_info();
$this->create_formula_file();
$this->load->model('Increment_calculation_model');
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$data['table_headers']= $this->xss_clean(get_dbtable_headers($this->table_info));
$data['fliter_list'] = $this->fliter_list;
$data['link_info'] = $this->quick_link;
$this->load->view("$this->control_name/manage",$data);
}
//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);
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search(){
echo json_encode(array('total'=>0,'rows'=>0));
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
//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');
$payroll_qry = 'SELECT * from cw_transactions where transactions_month = "'.$month.'" 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_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"] === '.$role."){\n\t\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;
}*/
}
?>