File: /home/cafsindia/hrms_cafsinfotech_in/application/controllers/Payroll_wizard.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Secure_Controller.php");
include_once('./application/libraries/xlsxwriter.class.php');
class Payroll_wizard extends Secure_Controller{
public function __construct(){
parent::__construct('payroll_wizard');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$report_list[""] = "---- Report List ----";
$report_list["NEW"] = "New";
$report_wizard_qry = 'select prime_report_wizard_id,report_title from cw_report_wizard where report_for = "payroll_wizard" and trans_status = 1 ORDER BY report_title ASC';
$report_wizard_info = $this->db->query("CALL sp_a_run ('SELECT','$report_wizard_qry')");
$report_wizard_result = $report_wizard_info->result();
$report_wizard_info->next_result();
foreach($report_wizard_result as $report_wizard){
$prime_report_wizard_id = $report_wizard->prime_report_wizard_id;
$report_title = $report_wizard->report_title;
$report_list[$prime_report_wizard_id] = ucfirst($report_title);
}
$data['report_list'] = $report_list;
$category_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1')");
$category_result = $category_info->result();
$category_info->next_result();
$category_for = array();
foreach($category_result as $cat_info){
$prime_category_id = $cat_info->prime_category_id;
$category_name = $cat_info->category_name;
$category_for[$prime_category_id] = $category_name;
}
$data['category_for'] = $category_for;
$select_from_qry = 'select field_isdefault,prime_form_id,label_name,view_name,transaction_type,field_type,gross_check,deduction_check,search_show,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value,pick_display_value from cw_form_setting where prime_module_id = "employees" and trans_status = 1 AND input_view_type in (1,2) ORDER BY transaction_type,gross_check,deduction_check,view_name';
$select_from_info = $this->db->query("CALL sp_a_run ('SELECT','$select_from_qry')");
$select_from_result = $select_from_info->result();
$select_from_info->next_result();
$master = [];
$earning = [];
$deduction = [];
$formula_master[""] = "---- Master ----";
$formula_earning[""] = "---- Earning ----";
$formula_deduction[""] = "---- Deduction ----";
//$group_by[""] = "---- Group by ----";
$group_by = array();
$group_list = [];
foreach($select_from_result as $select_from){
$field_isdefault = $select_from->field_isdefault;
$prime_form_id = $select_from->prime_form_id;
$label_name = $select_from->label_name;
$view_name = ucwords($select_from->view_name);
$transaction_type = (int)$select_from->transaction_type;
$field_type = (int)$select_from->field_type;
$gross_check = (int)$select_from->gross_check;
$deduction_check = (int)$select_from->deduction_check;
$search_show = (int)$select_from->search_show;
$pick_list_type = (int)$select_from->pick_list_type;
$pick_list = $select_from->pick_list;
$pick_table = $select_from->pick_table;
$auto_prime_id = $select_from->auto_prime_id;
$auto_dispaly_value = $select_from->auto_dispaly_value;
$pick_display_value = $select_from->pick_display_value;
$trans_name = "Basic";
if($transaction_type === 2){
$trans_name = "Earning";
}else
if($transaction_type === 3){
$trans_name = "Deduction";
}else
if($transaction_type === 4){
$trans_name = "Others";
}
$final_list = array("prime_form_id"=>$prime_form_id,"label_name"=>$label_name,"view_name"=>$view_name,"transaction_type"=>$transaction_type,"trans_name"=>$trans_name,"field_type"=>$field_type,"gross_check"=>$gross_check,"deduction_check"=>$deduction_check,'pick_list_type'=>$pick_list_type,'pick_list'=>$pick_list,'pick_table'=>$pick_table,'auto_prime_id'=>$auto_prime_id,'auto_dispaly_value'=>$auto_dispaly_value,'pick_display_value'=>$pick_display_value);
$master[$prime_form_id] = $final_list;
if($field_type === 2){
$formula_master["cw_employees.".$label_name] = $view_name;
}
if($transaction_type === 2){
$earning[$prime_form_id] = $final_list;
if($field_type === 2){
$formula_earning["cw_transactions.".$label_name] = $view_name;
}
}else
if($transaction_type === 3){
$deduction[$prime_form_id] = $final_list;
if($field_type === 2){
$formula_deduction["cw_transactions.".$label_name] = $view_name;
}
}
if(($field_type === 5) || ($field_type === 9)){
$group_by["cw_employees.".$label_name] = $view_name;
$group_list[$label_name] = array('prime_form_id'=>$prime_form_id,'label_name'=> $label_name, 'pick_list_type'=> $pick_list_type, 'pick_list'=> $pick_list, 'pick_table'=> $pick_table, 'auto_prime_id'=> $auto_prime_id, 'auto_dispaly_value'=> $auto_dispaly_value, 'pick_display_value'=> $pick_display_value);
}
}
$data['master'] = $master;
$data['earning'] = $earning;
$data['deduction'] = $deduction;
$data['formula_master'] = $formula_master;
$data['formula_earning'] = $formula_earning;
$data['formula_deduction'] = $formula_deduction;
$data['group_by'] = $group_by;
$data['group_list'] = $group_list;
$data['encKey'] = $this->generateKey();
$this->load->view("payroll_wizard/manage",$data);
}
function get_report_list($por_mode=false){
$report_list[""] = "---- Report List ----";
$report_list["NEW"] = "New";
$report_wizard_qry = 'select prime_report_wizard_id,report_title from cw_report_wizard where report_for = "payroll_wizard" and trans_status = 1 ORDER BY report_title ASC';
$report_wizard_info = $this->db->query("CALL sp_a_run ('SELECT','$report_wizard_qry')");
$report_wizard_result = $report_wizard_info->result();
$report_wizard_info->next_result();
foreach($report_wizard_result as $report_wizard){
$prime_report_wizard_id = $report_wizard->prime_report_wizard_id;
$report_title = $report_wizard->report_title;
$report_list[$prime_report_wizard_id] = ucfirst($report_title);
}
if($por_mode){
return array('success'=>true,'message'=>"Report list refreshed",'rslt'=>$report_list);
}else{
echo json_encode(array('success'=>true,'message'=>"Report list refreshed",'rslt'=>$report_list));
}
}
function get_report_list_view($por_mode=false){
$duration_type_array = array(1=>"Single Month",2=>"Multi Month");
$report_type_array = array(1=>"Consolidate",2=>"Detail",3=>"Only Master");
$display_format_array = array(1=>"Horizonal",2=>"Vertical");
$report_wizard_qry = 'SELECT prime_report_wizard_id,report_for,report_title,GROUP_CONCAT(DISTINCT cw_category.category_name) AS category_for,duration_type,report_type,GROUP_CONCAT(DISTINCT cw_form_setting.view_name) AS group_by,sub_total,display_format,from_month,to_month FROM `cw_report_wizard` LEFT JOIN cw_form_setting ON FIND_IN_SET(CONCAT("cw_",cw_form_setting.prime_module_id,".",cw_form_setting.label_name),
cw_report_wizard.group_by) LEFT JOIN cw_category ON FIND_IN_SET(cw_category.prime_category_id,cw_report_wizard.category_for)
GROUP BY cw_report_wizard.prime_report_wizard_id';
$report_wizard_info = $this->db->query("CALL sp_a_run ('SELECT','$report_wizard_qry')");
$report_wizard_result = $report_wizard_info->result();
$report_wizard_info->next_result();
$tr_list = "";
$count = 0;
foreach($report_wizard_result as $report_wizard){
$count++;
$prime_report_wizard_id = $report_wizard->prime_report_wizard_id;
$report_for = $report_wizard->report_for;
$report_title = $report_wizard->report_title;
$category_for = str_replace(","," , ",$report_wizard->category_for);
$duration_type = (int)$report_wizard->duration_type;
$report_type = (int)$report_wizard->report_type;
$group_by = str_replace(","," , ",$report_wizard->group_by ?? []);
$sub_total = $report_wizard->sub_total;
$display_format = $report_wizard->display_format;
$from_month = $report_wizard->from_month;
$to_month = $report_wizard->to_month;
$duration_type = $duration_type_array[$duration_type];
$report_type = $report_type_array[$report_type];
$display_format = $display_format_array[$display_format];
$sub_total = (int)$report_wizard->sub_total;
if($sub_total === 1){
$report_wizard->sub_total = "Yes";
}else{
$report_wizard->sub_total = "-";
}
$tr_list .= "<tr>
<td>$count</td>
<td>$report_title</td>
<td>$category_for</td>
<td>$duration_type</td>
<td>$report_type</td>
<td>$group_by</td>
<td>$sub_total</td>
<td>$display_format</td>
<td>$from_month</td>
<td>$to_month</td>
<td><button class='btn btn-xs btn-edit delete' id='$prime_report_wizard_id'>Delete</button></td>
</tr>";
}
if(count($report_wizard_result) === 0){
$tr_list .= "<tr style='text-align:center'>
<td colspan='11'>No data found</td>
</tr>";
}
if($por_mode){
return array('success'=>true,'message'=>"Report list",'rslt'=>$tr_list);
}else{
echo json_encode(array('success'=>true,'message'=>"Report list",'rslt'=>$tr_list));
}
}
function delete_report_list(){
$form_data = json_decode($this->input->post('form_data'));
$delete_id = $form_data->delete_id;
$report_wizard_qry = 'select prime_report_wizard_id,report_title from cw_report_wizard where report_for = "payroll_wizard" and trans_status = 1 and prime_report_wizard_id = "'.$delete_id.'"';
$report_wizard_info = $this->db->query("CALL sp_a_run ('SELECT','$report_wizard_qry')");
$report_wizard_result = $report_wizard_info->result();
$report_wizard_info->next_result();
if(count($report_wizard_result) === 1){
$remove_filter = 'DELETE FROM cw_report_wizard_filter WHERE report_wizard_id = "'.$delete_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_filter')");
$remove_col = 'DELETE FROM cw_report_wizard_col WHERE report_wizard_id = "'.$delete_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_col')");
$remove_head = 'DELETE FROM cw_report_wizard WHERE prime_report_wizard_id = "'.$delete_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_head')");
$report_list_view = $this->get_report_list_view(true);
$report_list = $this->get_report_list(true);
echo json_encode(array('success'=>true,'message'=>"Record Deleted",'rslt'=>$report_list_view["rslt"],"report_list"=>$report_list["rslt"]));
}else{
echo json_encode(array('success'=>false,'message'=>"Unable to process delete, please contact admin",'rslt'=>[]));
}
}
function get_picklist(){
$form_data = json_decode($this->input->post('form_data'));
$picklist_info = $form_data->picklist_id;
$pick_list = $this->pro_picklist($picklist_info);
echo json_encode($pick_list);
}
function pro_picklist($picklist_info){
if($picklist_info){
$picklist_info = explode("_",$picklist_info);
$prime_form_id = $picklist_info[1];
$select_from_qry = "select field_isdefault,prime_form_id,label_name,view_name,transaction_type,field_type,gross_check,deduction_check,search_show,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value,pick_display_value from cw_form_setting where prime_form_id = $prime_form_id and trans_status = 1";
$select_from_info = $this->db->query("CALL sp_a_run ('SELECT','$select_from_qry')");
$select_form_rslt = $select_from_info->result();
$select_from_info->next_result();
if($select_form_rslt){
$select_from = $select_form_rslt[0];
$field_isdefault = $select_from->field_isdefault;
$prime_form_id = $select_from->prime_form_id;
$label_name = $select_from->label_name;
$view_name = ucwords($select_from->view_name);
$transaction_type = (int)$select_from->transaction_type;
$field_type = (int)$select_from->field_type;
$gross_check = (int)$select_from->gross_check;
$deduction_check = (int)$select_from->deduction_check;
$search_show = (int)$select_from->search_show;
$pick_list_type = (int)$select_from->pick_list_type;
$pick_list = $select_from->pick_list;
$pick_table = $select_from->pick_table;
$auto_prime_id = $select_from->auto_prime_id;
$auto_dispaly_value = $select_from->auto_dispaly_value;
$pick_display_value = $select_from->pick_display_value;
$final_pick = array();
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($pick_display_value){
$pick_list = "$pick_list_val_1,CONCAT_WS(\" - \", $pick_display_value) as $pick_list_val_2";
}
$pick_query = "select $pick_list from $pick_table where trans_status = 1";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1";
}
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
if($pick_result){
$pick_key = array_column($pick_result, $pick_list_val_1);
$pick_val = array_column($pick_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
}
}else
if($field_type === 9){
$pick_query = "select $auto_prime_id,$auto_dispaly_value from $pick_table where trans_status = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
if($pick_result){
$pick_key = array_column($pick_result, $auto_prime_id);
$pick_val = array_column($pick_result, $auto_dispaly_value);
$final_pick = array_combine( $pick_key, $pick_val);
}
}
return array('success'=>true,'message'=>"Picklist info",'rslt'=>$final_pick);
}else{
return array('success'=>false,'message'=>"unable to fetch form data",'rslt'=>[]);
}
}else{
return array('success'=>false,'message'=>"Invalid picklist information",'rslt'=>[]);
}
}
function get_report_info(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$report_list = $this->input->post('report_list');
$report_query = 'SELECT report_for,report_title,category_for,duration_type,report_type,group_by,sub_total,display_format,from_month,to_month,save_upd FROM `cw_report_wizard` where trans_status = 1 and report_for = "payroll_wizard" and prime_report_wizard_id = "'.$report_list.'"';
$report_info = $this->db->query("CALL sp_a_run ('SELECT','$report_query')");
$report_result = $report_info->result();
$report_info->next_result();
if(count($report_result) === 1){
$col_query = 'SELECT report_wizard_id,prime_form_id,label_name,view_name,transaction_type,trans_name,field_type,gross_check,deduction_check,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value,pick_display_value,total,formula_info,pro_key FROM `cw_report_wizard_col` where report_wizard_id = "'.$report_list.'"';
$col_info = $this->db->query("CALL sp_a_run ('SELECT','$col_query')");
$col_result = $col_info->result();
$col_info->next_result();
$filter_query = 'SELECT report_wizard_id,prime_form_id,label_name,view_name,transaction_type,trans_name,field_type,gross_check,deduction_check,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value,pick_display_value,total,where_cond,where_val,where_val_from,where_val_to,where_sel_list,pro_key FROM `cw_report_wizard_filter` where report_wizard_id = "'.$report_list.'"';
$filter_info = $this->db->query("CALL sp_a_run ('SELECT','$filter_query')");
$filter_rslt = $filter_info->result();
$filter_info->next_result();
$report_result = $report_result[0];
$report_result->category_for = explode(",",$report_result->category_for);
$report_result->group_by = explode(",",$report_result->group_by ?? []);
$report_result->save_upd = "";
foreach($filter_rslt as $filter){
$field_type = (int)$filter->field_type;
$pro_key = $filter->pro_key;
$where_sel_list = [];
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$filter->where_val = explode(",",$filter->where_val);
$where_sel_list_rslt = $this->pro_picklist($pro_key);
if($where_sel_list_rslt["success"]){
$where_sel_list = $where_sel_list_rslt["rslt"];
}
}
$filter->where_sel_list = $where_sel_list;
}
$rslt_info = [];
$rslt_info["head"] = $report_result;
$rslt_info["column"] = $col_result;
$rslt_info["filter"] = $filter_rslt;
echo json_encode(array('success'=>true,'message'=>"Report Information",'rslt'=>$rslt_info));
}else{
echo json_encode(array('success'=>false,'message'=>"Unable to fetch report information",'rslt'=>[]));
}
}
function process_report(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$form_data = json_decode($this->input->post('form_data'));
$duration_type = (int)$form_data->duration_type;
$report_type = (int)$form_data->report_type;
$group_by = array_filter($form_data->group_by ?? []);
$display_format = (int)$form_data->display_format;
$select_info = $form_data->select_info;
$can_process = true;
if($display_format === 1){
$from_month = date("Y-m-d",strtotime('01-'.$form_data->from_month))." 00:00:00";
$to_month = date("Y-m-d",strtotime('31-'.$form_data->to_month))." 24:00:00"; // all month consider as 31
$start = new DateTime($from_month);
$end = new DateTime($to_month);
$diff = $start->diff($end);
$yearsInMonths = $diff->format('%r%y') * 12;
$months = $diff->format('%r%m');
$totalMonths = $yearsInMonths + $months +1; // 1 add for total column
$group_count = count($group_by);
$select_count = count($select_info);
if($duration_type === 2){
$select_count = (count($select_info) *$totalMonths);
}
$tot_col = $group_count+$select_count;
if($tot_col > 200){
$can_process = false;
echo json_encode(array('success'=>false,'message'=>'Allowed only 200 column in line'));
}
}
if((($duration_type === 1) && ($report_type === 1)) || (($duration_type === 2) && ($report_type === 2))){
$from_month = date("Y-m-d",strtotime('01-'.$form_data->from_month));
$to_month = date("Y-m-d",strtotime('01-'.$form_data->to_month));
if($from_month > $to_month){
$can_process = false;
echo json_encode(array('success'=>false,'message'=>'To Month should be Greater than From Month.!'));
}
}
if($can_process){
$add_upd_rslt = $this->add_upd_report($form_data);
if(!$add_upd_rslt["success"]){
echo json_encode($add_upd_rslt);
}else{
$final_qry = $this->build_query($form_data);
$report_info = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$report_rslt = $report_info->result();
$report_info->next_result();
if($report_rslt){
$pro_excel_info = [];
$pro_excel_info["form_data"] = $form_data;
$pro_excel_info["rslt"] = $report_rslt;
$this->process_rslt($pro_excel_info);
}else{
$save_upd = (int)$form_data->save_upd;
if($save_upd === 1){
if(count($report_rslt) === 0){
echo json_encode(array('success'=>true,'message'=>"Template saved, No record found for given filter",'rslt'=>[]));
}else{
echo json_encode(array('success'=>false,'message'=>"Template saved, Unable to prrocess given condtion, please check and retry",'rslt'=>[]));
}
}else
if($save_upd === 2){
if(count($report_rslt) === 0){
echo json_encode(array('success'=>true,'message'=>"Template Duplicated, No record found for given filter",'rslt'=>[]));
}else{
echo json_encode(array('success'=>false,'message'=>"Template Duplicated Unable to prrocess given condtion, please check and retry",'rslt'=>[]));
}
}else{
if(count($report_rslt) === 0){
echo json_encode(array('success'=>false,'message'=>"No record found for given filter",'rslt'=>[]));
}else{
echo json_encode(array('success'=>false,'message'=>"Unable to prrocess given condtion, please check and retry",'rslt'=>[]));
}
}
}
}
}
}
function add_upd_report($form_data){
/*Head Information - START*/
$report_list = (int)$form_data->report_list;
$report_for = "payroll_wizard";
$report_title = $form_data->report_title;
$category_for = implode(",",array_filter($form_data->category_for));
$duration_type = $form_data->duration_type;
$report_type = $form_data->report_type;
$group_by = implode(",",array_filter($form_data->group_by ?? []));
$sub_total = $form_data->sub_total;
$display_format = $form_data->display_format;
$from_month = $form_data->from_month;
$to_month = $form_data->to_month;
$save_upd = (int)$form_data->save_upd;
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
/*Head Information - END*/
$select_info = $form_data->select_info; // REPORT COLUMN
$filter_info = $form_data->filter_info; // FILTER COLUMN
$exist_query = 'SELECT prime_report_wizard_id FROM `cw_report_wizard` where report_for = "'.$report_for.'" and report_title = "'.$report_title.'"';
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
$exit_count = count($exist_result);
if(count($select_info) === 0){
$pro_rslt = array('success'=>false,'message'=>"Select atleast one column","rslt"=>[]);
}else
if($save_upd === 0){ //NO SAVE / UPDATE
$pro_rslt = array('success'=>true,'message'=>"No Save / Update process given","rslt"=>[]);
}else
if($save_upd === 1){ //SAVE
if($exit_count > 0){
$pro_rslt = array('success'=>false,'message'=>"Report name already exist","rslt"=>[]);
}else{
$pro_qry = 'insert into cw_report_wizard (report_for,report_title,category_for,duration_type,report_type,group_by,sub_total,display_format,from_month,to_month,save_upd,trans_created_by,trans_created_date) value ("'.$report_for.'","'.$report_title.'","'.$category_for.'","'.$duration_type.'","'.$report_type.'","'.$group_by.'","'.$sub_total.'","'.$display_format.'","'.$from_month.'","'.$to_month.'","'.$save_upd.'","'.$logged_id.'","'.$today_date.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$pro_qry')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$pro_rslt = array('success'=>true,'message'=>"Template save","rslt"=>$insert_id);
}
}else
if($save_upd === 2){ //DUPLICATE
$exit_id = (int)$exist_result[0]->prime_report_wizard_id;
if($exit_count > 0){
$pro_rslt = array('success'=>false,'message'=>"Report name already exist","rslt"=>[]);
}else{
$pro_qry = 'insert into cw_report_wizard (report_for,report_title,category_for,duration_type,report_type,group_by,sub_total,display_format,from_month,to_month,save_upd,trans_created_by,trans_created_date) value ("'.$report_for.'","'.$report_title.'","'.$category_for.'","'.$duration_type.'","'.$report_type.'","'.$group_by.'","'.$sub_total.'","'.$display_format.'","'.$from_month.'","'.$to_month.'","'.$save_upd.'","'.$logged_id.'","'.$today_date.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$pro_qry')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$pro_rslt = array('success'=>true,'message'=>"Template Duplicated","rslt"=>$insert_id);
}
}else
if($save_upd === 3){ //ONLY UPDATE
$exit_id = (int)$exist_result[0]->prime_report_wizard_id;
if(($exit_count === 1) && ($report_list === $exit_id)){
$pro_qry = 'UPDATE cw_report_wizard SET report_for = "'.$report_for.'",report_title = "'.$report_title.'" , category_for = "'.$category_for.'" ,duration_type = "'.$duration_type.'" ,report_type = "'.$report_type.'" ,group_by = "'.$group_by.'" ,sub_total = "'.$sub_total.'" ,display_format = "'.$display_format.'" ,from_month = "'.$from_month.'" ,to_month = "'.$to_month.'" ,save_upd = "'.$save_upd.'",trans_updated_by = "'.$logged_id.'" ,trans_updated_date = "'.$today_date.'" WHERE prime_report_wizard_id = "'.$report_list.'"';
$this->db->query("CALL sp_a_run ('RUN','$pro_qry')");
$pro_rslt = array('success'=>true,'message'=>"Template updated","rslt"=>$report_list);
}else{
$pro_rslt = array('success'=>false,'message'=>"unable to update","rslt"=>[]);
}
}else{
$pro_rslt = array('success'=>false,'message'=>"Unable to process","rslt"=>[]);
}
if(!$pro_rslt["success"]){
return $pro_rslt;
}else{
$report_wizard_id = $pro_rslt["rslt"];
$col_value = "";
foreach($select_info as $sel_info){
$prime_form_id = $sel_info->prime_form_id;
$label_name = $sel_info->label_name;
$view_name = $sel_info->view_name;
$transaction_type = $sel_info->transaction_type;
$trans_name = $sel_info->trans_name;
$field_type = $sel_info->field_type;
$gross_check = $sel_info->gross_check;
$deduction_check = $sel_info->deduction_check;
$pick_list_type = $sel_info->pick_list_type;
$pick_list = $sel_info->pick_list;
$pick_table = $sel_info->pick_table;
$auto_prime_id = $sel_info->auto_prime_id;
$auto_dispaly_value = $sel_info->auto_dispaly_value;
$pick_display_value = $sel_info->pick_display_value;
$total = $sel_info->total;
$formula_info = $sel_info->formula_info;
$pro_key = $sel_info->pro_key;
$col_value .= "(\"$report_wizard_id\",\"$prime_form_id\",\"$label_name\",\"$view_name\",\"$transaction_type\",\"$trans_name\",\"$field_type\",\"$gross_check\",\"$deduction_check\",\"$pick_list_type\",\"$pick_list\",\"$pick_table\",\"$auto_prime_id\",\"$auto_dispaly_value\",\"$pick_display_value\",\"$total\",\"$formula_info\",\"$pro_key\",\"$logged_id\",\"$today_date\"),";
}
if($col_value !== ''){
$remove_col = 'DELETE FROM cw_report_wizard_col WHERE report_wizard_id = "'.$report_wizard_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_col')");
$col_value = rtrim($col_value,',');
$col_query = "insert into cw_report_wizard_col (report_wizard_id,prime_form_id,label_name,view_name,transaction_type,trans_name,field_type,gross_check,deduction_check,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value,pick_display_value,total,formula_info,pro_key,trans_created_by,trans_created_date) values $col_value";
$col_info = $this->db->query("CALL sp_a_run ('INSERT','$col_query')");
$col_result = $col_info->result();
$col_info->next_result();
}
$filter_value = "";
foreach($filter_info as $fil_info){
$prime_form_id = $fil_info->prime_form_id;
$label_name = $fil_info->label_name;
$view_name = $fil_info->view_name;
$transaction_type = $fil_info->transaction_type;
$trans_name = $fil_info->trans_name;
$field_type = (int)$fil_info->field_type;
$gross_check = $fil_info->gross_check;
$deduction_check = $fil_info->deduction_check;
$pick_list_type = $fil_info->pick_list_type;
$pick_list = $fil_info->pick_list;
$pick_table = $fil_info->pick_table;
$auto_prime_id = $fil_info->auto_prime_id;
$auto_dispaly_value = $fil_info->auto_dispaly_value;
$pick_display_value = $fil_info->pick_display_value;
$total = $fil_info->total;
$where_cond = $fil_info->where_cond;
$where_val_from = $fil_info->where_val_from;
$where_val_to = $fil_info->where_val_to;
$where_sel_list = "";
$pro_key = $fil_info->pro_key;
$where_val = $fil_info->where_val;
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$where_val = implode(",",$fil_info->where_val);
}
$filter_value .= "(\"$report_wizard_id\",\"$prime_form_id\",\"$label_name\",\"$view_name\",\"$transaction_type\",\"$trans_name\",\"$field_type\",\"$gross_check\",\"$deduction_check\",\"$pick_list_type\",\"$pick_list\",\"$pick_table\",\"$auto_prime_id\",\"$auto_dispaly_value\",\"$pick_display_value\",\"$total\",\"$where_cond\",\"$where_val\",\"$where_val_from\",\"$where_val_to\",\"$where_sel_list\",\"$pro_key\",\"$logged_id\",\"$today_date\"),";
}
if($filter_value !== ''){
$remove_col = 'DELETE FROM cw_report_wizard_filter WHERE report_wizard_id = "'.$report_wizard_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_col')");
$filter_value = rtrim($filter_value,',');
$filter_query = " INSERT INTO cw_report_wizard_filter (report_wizard_id,prime_form_id,label_name,view_name,transaction_type,trans_name,field_type,gross_check,deduction_check,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value,pick_display_value,total,where_cond,where_val,where_val_from,where_val_to,where_sel_list,pro_key,trans_created_by,trans_created_date) VALUES $filter_value";
$filter_qry_info = $this->db->query("CALL sp_a_run ('INSERT','$filter_query')");
$filter_qry_result = $filter_qry_info->result();
$filter_qry_info->next_result();
}
return $pro_rslt;
}
}
function build_query($form_data){
$report_list = $form_data->report_list;
$category_for = implode(",",array_filter($form_data->category_for));
$duration_type = $form_data->duration_type;
$report_type = $form_data->report_type;
$group_by = array_filter($form_data->group_by ?? []);
$from_month = date("Y-m-d",strtotime('01-'.$form_data->from_month));
$to_month = date("Y-m-d",strtotime('01-'.$form_data->to_month));
$report_title = $form_data->report_title;
$select_info = $form_data->select_info;
$filter_info = $form_data->filter_info;
$group_list = $form_data->group_list;
$select_qry_info = [];
$group_qry_info = [];
$select_qry = "";
$pick_qry = "";
$group_qry = [];
if($group_by){
$group_val = array_filter($form_data->group_by ?? []);
$group_list = json_decode(json_encode($group_list), true);
foreach($group_val as $group_col){
$group_col_info = explode(".",$group_col);
$group_by_col = $group_col_info[1];
if($group_list[$group_by_col]){
$join_info = $group_list[$group_by_col];
$prime_form_id = $join_info["prime_form_id"];
$label_name = $join_info["label_name"];
$pick_list_type = $join_info["pick_list_type"];
$pick_list = $join_info["pick_list"];
$pick_table = $join_info["pick_table"];
$auto_prime_id = $join_info["auto_prime_id"];
$auto_dispaly_value = $join_info["auto_dispaly_value"];
$pick_display_value = $join_info["pick_display_value"];
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
}
$pick_query_as = $pick_table."_".$prime_form_id;
$select_qry .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_qry .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $group_col ";
$select_qry_info[$group_col] = $label_name;
$group_qry[] = "ABS($group_col)";
$group_qry_info[$group_col] = $label_name;
}
}
}
if(($duration_type === 1) && ($report_type === 2)){
if(!$select_qry_info["cw_employees.employee_code"]){
$select_qry .= "cw_employees.employee_code,";
$select_qry_info["cw_employees.employee_code"] = "cw_employees.employee_code";
}
if(!$group_qry_info["cw_employees.employee_code"]){
$group_qry[] = "ABS(cw_employees.employee_code)";
$group_qry_info["cw_employees.employee_code"] = "cw_employees.employee_code";
}
if(!$select_qry_info["cw_employees.emp_name"]){
$select_qry .= "cw_employees.emp_name,";
$select_qry_info["cw_employees.emp_name"] = "cw_employees.emp_name";
}
if(!$group_qry_info["cw_employees.emp_name"]){
$group_qry[] = "ABS(cw_employees.emp_name)";
$group_qry_info["cw_employees.emp_name"] = "cw_employees.emp_name";
}
}
if(($duration_type === 1) && ($report_type === 3)){
$select_qry .= "cw_employees.employee_code,cw_employees.emp_name,";
$select_qry_info["cw_employees.employee_code"] = "cw_employees.employee_code";
$select_qry_info["cw_employees.emp_name"] = "cw_employees.emp_name";
}
if(($duration_type === 2) && ($report_type === 2)){
if(!$select_qry_info["cw_employees.employee_code"]){
$select_qry .= "cw_employees.employee_code,";
$select_qry_info["cw_employees.employee_code"] = "cw_employees.employee_code";
}
if(!$group_qry_info["cw_employees.employee_code"]){
$group_qry[] = "ABS(cw_employees.employee_code)";
$group_qry_info["cw_employees.employee_code"] = "cw_employees.employee_code";
}
if(!$select_qry_info["cw_employees.emp_name"]){
$select_qry .= "cw_employees.emp_name,";
$select_qry_info["cw_employees.emp_name"] = "cw_employees.emp_name";
}
if(!$group_qry_info["cw_employees.emp_name"]){
$group_qry[] = "ABS(cw_employees.emp_name)";
$group_qry_info["cw_employees.emp_name"] = "cw_employees.emp_name";
}
if(!$select_qry_info["cw_transactions.transactions_month"]){
$select_qry .= "cw_transactions.transactions_month,";
$select_qry_info["cw_transactions.transactions_month"] = "cw_transactions.transactions_month";
}
if(!$group_qry_info["cw_transactions.transactions_month"]){
$group_qry[] = "ABS(cw_transactions.transactions_month)";
$group_qry_info["cw_transactions.transactions_month"] = "cw_transactions.transactions_month";
}
}
if(count($select_info) > 0){
foreach($select_info as $select){
$form_info = explode("_",$select->pro_key);
$type = $form_info[0];
$prime_form_id = $select->prime_form_id;
$label_name = strtolower($select->label_name);
$field_type = (int)$select->field_type;
$formula_info = $select->formula_info;
$view_name = $select->view_name;
$total = $select->total;
$pick_list_type = (int)$select->pick_list_type;
$pick_list = $select->pick_list;
$pick_table = $select->pick_table;
$auto_prime_id = $select->auto_prime_id;
$auto_dispaly_value = $select->auto_dispaly_value;
$pick_display_value = $select->pick_display_value;
$table_name = "";
if($type === "MASTER"){
$table_name = "cw_employees.";
}else
if(($type === "EARNING") || ($type === "DEDUCTION")){
$table_name = "cw_transactions.";
}
$col_name = $table_name."$label_name";
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
}
if($report_type === 1){
if($type === "NEW"){
$label_name = str_replace(" ","_",$label_name);
$select_val = "SUM($formula_info) as $label_name";
}else{
if($field_type === 2){
$select_val = "SUM($col_name) as $label_name";
}else{
$select_val = $col_name;
}
}
}else{
if($type === "NEW"){
$label_name = str_replace(" ","_",$label_name);
$select_val = "($formula_info) as $label_name";
}else{
if($field_type === 2){
$select_val = "$col_name as $label_name";
}else{
$select_val = $col_name;
}
}
}
$select_join_qry = "";
$pick_query_as = $pick_table."_".$prime_form_id;
if(($field_type === 5) || ($field_type === 7)){
$select_val = "$pick_query_as.$pick_list_val_2 as $label_name";
$select_join_qry .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $col_name ";
}else
if($field_type === 9){
$select_val = "$pick_query_as.$auto_dispaly_value as $label_name";
$select_join_qry .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $col_name ";
}
if(!$select_qry_info[$col_name]){
$select_qry .= $select_val.",";
$select_qry_info[$col_name] = $col_name;
if($select_join_qry){
$pick_qry .= $select_join_qry;
}
}
}
}
if($report_type === 1){
$select_qry .= "COUNT(*) AS emp_count,";
}
if($select_qry === ""){
$select_qry = "*";
}
$select_qry = rtrim($select_qry,",");
$final_qry = "select $select_qry from cw_employees inner join cw_transactions on cw_transactions.employee_code = cw_employees.employee_code $pick_qry where cw_transactions.trans_status = 1 and cw_employees.trans_status = 1";
if(($duration_type === 1) && ($report_type === 3)){ // Single Month & Only Master
$final_qry = "select $select_qry from cw_employees $pick_qry where cw_employees.trans_status = 1";
}
if($category_for !== ""){
$final_qry .= " and cw_employees.role in ($category_for) and cw_employees.role not in (1)";
}
if(($duration_type === 1) && ($report_type === 1)){ // Single Month & Consolidate
$final_qry .= ' and date_format(str_to_date(concat("01-",cw_transactions.transactions_month), "%d-%m-%Y") , "%Y-%m-%d") >= "'.$from_month.'" and date_format(str_to_date(concat("01-",cw_transactions.transactions_month), "%d-%m-%Y") , "%Y-%m-%d") <= "'.$to_month.'"';
}
if(($duration_type === 1) && ($report_type === 2)){ // Single Month & Detail
$final_qry .= ' and cw_transactions.transactions_month = "'.$form_data->from_month.'"';
}
if(($duration_type === 2) && ($report_type === 2)){ // Multi Month & Detail
$final_qry .= ' and date_format(str_to_date(concat("01-",cw_transactions.transactions_month), "%d-%m-%Y") , "%Y-%m-%d") >= "'.$from_month.'" and date_format(str_to_date(concat("01-",cw_transactions.transactions_month), "%d-%m-%Y") , "%Y-%m-%d") <= "'.$to_month.'"';
}
$filter_query = "";
foreach($filter_info as $filter){
$pro_key = $filter->pro_key;
$label_name = $filter->label_name;
$field_type = (int)$filter->field_type;
$where_cond = $filter->where_cond;
$where_val = $filter->where_val;
$where_val_from = $filter->where_val_from;
$where_val_to = $filter->where_val_to;
$pro_key_info = explode("_",$pro_key);
$table_info = $pro_key_info[0];
$column_name = "cw_transactions.".$label_name;
if($table_info === "MASTER"){
$column_name = "cw_employees.".$label_name;
}
if(($field_type === 1) || ($field_type === 11)|| ($field_type === 12) || ($field_type === 14)){ // text
$filter_query .= " and $column_name $where_cond '$where_val'";
}else
if(($field_type === 2) || ($field_type === 3)){ // number
$filter_query .= " and $column_name $where_cond $where_val";
}else
if(($field_type === 4) || ($field_type === 13) || ($field_type === 15)){ //DATE
if($where_cond === "between"){
if($field_type === 4){ //DATE
$where_val_from = date('Y-m-d',strtotime($where_val_from));
$where_val_to = date('Y-m-d',strtotime($where_val_to));
$filter_query .= ' and '.$column_name.' '.$where_cond .'"'.$where_val_from.'" and "'.$where_val_to.'"';
}else
if($field_type === 13){ // Date & Time
$where_val_from = date('Y-m-d H:i:s',strtotime($where_val_from));
$where_val_to = date('Y-m-d H:i:s',strtotime($where_val_to));
$filter_query .= ' and '.$column_name.' '.$where_cond .'"'.$where_val_from.'" and "'.$where_val_to.'"';
}else{
//$filter_query = 'and DATE_FORMAT('.$column_name.', "%H:%i") as '.$label_name;
}
}else{
if($field_type === 4){ //DATE
$where_val = date('Y-m-d',strtotime($where_val));
$filter_query .= ' and '.$column_name.' '.$where_cond .'"'.$where_val.'"';
}else
if($field_type === 13){ // Date & Time
$where_val = date('Y-m-d H:i:s',strtotime($where_val));
$filter_query .= ' and '.$column_name.' '.$where_cond .'"'.$where_val.'"';
}else{
//$filter_query = 'and DATE_FORMAT('.$column_name.', "%H:%i") as '.$label_name;
}
}
}else
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){//PICKLIST
if(count($where_val) > 0){
$where_val = trim(implode('","',$where_val));
$filter_query .= ' and '.$column_name.' in ("'.$where_val.'")';
}
}
}
if($filter_query !== ""){
$final_qry .= $filter_query;
}
$group_qry = implode(",", $group_qry);
if(count($group_qry_info) > 0){
if($report_type === 1){
$final_qry .= " GROUP BY $group_qry ORDER BY $group_qry";
}else{
$final_qry .= " ORDER BY $group_qry";
}
}else{
if(($duration_type === 2) && ($report_type === 2)){
$final_qry .= " ORDER BY cw_employees.employee_code";
}
}
return $final_qry;
}
function process_rslt($pro_rslt){
$report_rslt = json_decode(json_encode($pro_rslt["rslt"]), true);
$form_data = $pro_rslt["form_data"];
$duration_type = (int)$form_data->duration_type;
$report_type = (int)$form_data->report_type;
$group_by = array_filter($form_data->group_by ?? []);
$sub_total = $form_data->sub_total;
$display_format = (int)$form_data->display_format;
$select_info = $form_data->select_info;
$final_rslt = [];
$pro_head = [];
$pro_head_2 = [];
$componet_list = [];
$total_row = [];
$total_list = [];
$pro_head_field = [];
$pro_head_field_2 = [];
$componet_list_filed = [];
//PROCESS HEAD - START
foreach($select_info as $select){
$label_name = strtolower($select->label_name);
$view_name = $select->view_name;
$field_type = $select->field_type;
$componet_list[$label_name] = $view_name;
$componet_list_filed[$label_name] = $field_type;
if((int)$select->total === 1){
$total_list[$label_name] = true;
}else{
$total_list[$label_name] = false;
}
}
if($report_type === 1){
if(!$componet_list["emp_count"]){
$componet_list["emp_count"] = "Employee count";
}
}
if($display_format === 1){ //Horizonal format
foreach($group_by as $group_col){
$group_col_info = explode(".",$group_col);
$pro_head[$group_col_info[1]] = ucfirst(str_replace("_"," ",$group_col_info[1]));
$pro_head_field[$group_col_info[1]] = 1;
$total_row[$group_col_info[1]] = " ";
if(($display_format === 1) && ($duration_type === 2)){
$pro_head_2[$group_col_info[1]] = " ";
$pro_head_field_2[$group_col_info[1]] = 1;
}
}
if($duration_type === 2){
$pro_head["employee_code"] = "Employee Code";
$pro_head["emp_name"] = "Employee Name";
// $pro_head["date_of_joining"] = "Date Of Joining"; // [MS 12-09-2024]
$pro_head_field["employee_code"] = 1;
$pro_head_field["emp_name"] = 1;
// $pro_head_field["date_of_joining"] = 4;
$pro_head_2["employee_code"] = " ";
$pro_head_2["emp_name"] = " ";
// $pro_head_2["date_of_joining"] = " ";
$pro_head_field_2["employee_code"] = 1;
$pro_head_field_2["emp_name"] = 1;
// $pro_head_field_2["date_of_joining"]= 4;
$pro_month = [];
$month_loop = [];
foreach($report_rslt as $row){
$transactions_month = $row["transactions_month"];
if(!$month_loop[$transactions_month]){
$month_loop[$transactions_month] = $transactions_month;
}
}
$sortOrder = true;
uksort($month_loop, function($a, $b) use($sortOrder){
$a = date("Y-m",strtotime('01-'.$a));
$b = date("Y-m",strtotime('01-'.$b));
$timeA = strtotime($a);
$timeB = strtotime($b);
return $sortOrder ? ($timeA - $timeB) : ($timeB - $timeA);
});
foreach($month_loop as $month){
if(!$pro_month[$month]){
$pro_month[$month] = $month;
$pro_head_field[$month] = 1;
$count = 0;
foreach($componet_list as $comp_key => $comp_val){
if($count > 0){
$pro_month[] = " ";
$pro_head_field[] = 0;
}
$pro_head_2[] = $comp_val;
if($componet_list_filed[$comp_key]){
$field_type = $componet_list_filed[$comp_key];
$pro_head_field_2[] = (int)$field_type;
}else{ // ELSE CONDITION BY [MS 11-09-2024]
$pro_head_field_2[] = '';
}
$count++;
}
}
}
$pro_head = array_merge($pro_head,$pro_month);
}else{
if(($duration_type === 1) && ($report_type === 2)){
if(!$pro_head["employee_code"]){
$pro_head["employee_code"] = "Employee Code";
$pro_head_field["employee_code"] = 1;
$total_row["employee_code"] = " ";
}
if(!$pro_head["emp_name"]){
$pro_head["emp_name"] = "Employee Name";
$pro_head_field["emp_name"] = 1;
$total_row["emp_name"] = " ";
}
}
if(($duration_type === 1) && ($report_type === 3)){
if(!$pro_head["employee_code"]){
$pro_head["employee_code"] = "Employee Code";
$pro_head_field["employee_code"] = 1;
$total_row["employee_code"] = " ";
}
if(!$pro_head["emp_name"]){
$pro_head["emp_name"] = "Employee Name";
$pro_head_field["employee_code"] = 1;
$total_row["emp_name"] = " ";
}
}
foreach($select_info as $select){
$form_info = explode("_",$select->pro_key);
$type = $form_info[0];
$label_name = strtolower($select->label_name);
$view_name = $select->view_name;
$field_type = $select->field_type;
$componet_list[$label_name] = $view_name;
if(!$pro_head[$label_name]){
$pro_head[$label_name] = $view_name;
$pro_head_field[$label_name] = (int)$field_type;
}
if((int)$select->total === 1){
$sum_col = $label_name;
if($type === "NEW"){
$sum_col = str_replace(" ","_",$label_name);
}
$total_row[$label_name] = array_sum(array_column($report_rslt,$sum_col));
}else{
$total_row[$label_name] = " ";
}
}
if($report_type === 1){
if(!$pro_head["emp_count"]){
$pro_head["emp_count"] = "Employee count";
$pro_head_field["emp_count"] = 3;
$total_row["emp_count"] = array_sum(array_column($report_rslt,"emp_count"));
}
}
}
}else
if($display_format === 2){ //Vertical format
$pro_group = [];
if($duration_type === 2){
foreach($group_by as $group_col){
$group_col_info = explode(".",$group_col);
$pro_group[$group_col_info[1]] = ucfirst(str_replace("_"," ",$group_col_info[1]));
$pro_head[$group_col_info[1]] = ucfirst(str_replace("_"," ",$group_col_info[1]));
$pro_head_field[$group_col_info[1]] = 1;
}
$pro_head["employee_code"] = "Employee Code";
$pro_head_field["employee_code"] = 1;
$pro_head["emp_name"] = "Employee Name";
$pro_head_field["emp_name"] = 1;
}
$pro_head["components"] = "Components";
$pro_head_field["components"] = 1;
$pro_month = [];
foreach($report_rslt as $row){
if($duration_type === 1){
foreach($group_by as $group_col){
$group_col_info = explode(".",$group_col);
$col_name = $group_col_info[1];
$col_val = $row[$col_name];
if(!$pro_head[$col_val]){
$pro_head[$col_val] = ucfirst(str_replace("_"," ",$col_val));
$pro_head_field[$col_val] = 3;
}
}
}else
if($duration_type === 2){
$transactions_month = $row["transactions_month"];
if(!$pro_month[$transactions_month]){
$pro_month[$transactions_month] = $transactions_month;
$pro_head_field[$transactions_month] = 2;
}
}
}
$sortOrder = true;
uksort($pro_month, function($a, $b) use($sortOrder){
$a = date("Y-m",strtotime('01-'.$a));
$b = date("Y-m",strtotime('01-'.$b));
$timeA = strtotime($a);
$timeB = strtotime($b);
return $sortOrder ? ($timeA - $timeB) : ($timeB - $timeA);
});
$pro_head = array_merge($pro_head,$pro_month);
}
if(($display_format === 2) || (($display_format === 1) && ($duration_type === 2))){
$pro_head["total"] = "Total";
$pro_head_field["total"] = 3;
}
$final_rslt[] = array_values($pro_head);
if(($display_format === 1) && ($duration_type === 2)){
/* Add only for total */
foreach($componet_list as $comp_key => $comp_val){
$pro_head_2[] = $comp_val;
}
/* Add only for total */
$final_rslt[] = array_values($pro_head_2);
}
//PROCESS HEAD - END
// PROCESS SUB TOTAL - START
if(($duration_type === 1) && ($report_type === 2) && (count($group_by) > 0) && ($sub_total)){
$group_by_form = $form_data;
$group_by_form->report_type = 1;
$group_by_form->to_month = $form_data->from_month;
$group_qry = $this->build_query($group_by_form);
$sub_tot_info = $this->db->query("CALL sp_a_run ('SELECT','$group_qry')");
$sub_tot_rslt = $sub_tot_info->result();
$sub_tot_info->next_result();
$final_sub_total = [];
foreach($sub_tot_rslt as $sub_tot_row){
$tot_row_info = json_decode(json_encode($sub_tot_row), true);
$chk_val = [];
$insert_data = [];
foreach ($group_by as $k => $v){
$col_info = explode(".",$v);
$col_key = $col_info[1];
$col_val = $tot_row_info[$col_key];
if($col_val === ""){
$col_val = 0;
}
$chk_val[] = $col_val;
$insert_data[$col_key] = " ";
unset($tot_row_info[$col_key]);
}
if(!$insert_data["employee_code"]){
$insert_data["employee_code"] = " ";
}
if(!$insert_data["emp_name"]){
$insert_data["emp_name"] = " ";
}
$tot_row_info = array_merge($insert_data,$tot_row_info);
$group_id = trim(implode("_",$chk_val)," ");
$final_sub_total[$group_id] = $tot_row_info;
}
$pro_rslt = $report_rslt;
$prev_val = "";
$pro_count = 0;
$tot_emp = 0;
foreach($pro_rslt as $push_key => $value){
$value = json_decode(json_encode($value), true);
$group_chk_val = [];
foreach ($group_by as $k => $v){
$col_info = explode(".",$v);
$col_key = $col_info[1];
$col_val = $value[$col_key];
if($col_val === ""){
$col_val = 0;
}
$group_chk_val[] = $col_val;
}
$group_key = trim(implode("_",$group_chk_val)," ");
if(($prev_val) && ($prev_val !== $group_key)){
$sub_tot_val = $final_sub_total[$prev_val];
$emp_count = $sub_tot_val["emp_count"];
$tot_emp += $emp_count;
unset($sub_tot_val["emp_count"]);
$push_info = array_values($sub_tot_val);
$push_info[0] = "Total Employees : $emp_count";
$push_info[1] = "Sub Total";
$push_key = $push_key+$pro_count;
$push_array = array($push_key =>$push_info);
array_splice($report_rslt, $push_key, 0, $push_array);
$pro_count++;
}
$prev_val = $group_key;
}
$sub_tot_val = $final_sub_total[$prev_val];
$emp_count = $sub_tot_val["emp_count"];
$tot_emp += $emp_count;
unset($sub_tot_val["emp_count"]);
$push_info = array_values($sub_tot_val);
$push_info[0] = "Total Employees : $emp_count";
$push_info[1] = "Sub Total";
$push_key = $push_key+$pro_count+1;
$push_array = array($push_key =>$push_info);
array_splice($report_rslt, $push_key, 0, $push_array);
}
// PROCESS SUB TOTAL - END
// PROCESS TOTAL - START
if(($display_format === 1) && ($duration_type === 1)){
if(($duration_type === 1) && ($report_type === 1) && (count($group_by) === 0)){
$total_row = [];
}else{
if($duration_type !== 2){
$total_row = array_values($total_row);
$total_row[0] = "Total";
}
}
if(count($total_row) > 0){
$report_rslt[] = $total_row;
}
}
$dummy_array = [];
for($x = 0; $x <= count($pro_head); $x++){
$dummy_array[] = " ";
}
if(($duration_type === 1) && ($report_type === 2)){
if($sub_total){
$emp_total_row = $dummy_array;
$emp_total_row[0] = "Total NO. OF Employees : $tot_emp";
$report_rslt[] = $dummy_array;
$report_rslt[] = $emp_total_row;
}else{
$tot_emp = count($report_rslt) - 1;
$emp_total_row = $dummy_array;
$emp_total_row[0] = "Total NO. OF Employees : ".$tot_emp;
$report_rslt[] = $dummy_array;
$report_rslt[] = $emp_total_row;
}
}
// PROCESS TOTAL - END
if($display_format === 1){ // Horizonal format
if((($duration_type === 1) && ($report_type === 1)) || ($duration_type === 1) && ($report_type === 2) || ($duration_type === 1) && ($report_type === 3)){
foreach($report_rslt as $row){
$final_rslt[] = array_values($row);
}
}else
if($duration_type === 2){
$process_emp = [];
$group_emp_month = [];
foreach($report_rslt as $row){
$employee_code = $row["employee_code"];
$transactions_month = $row["transactions_month"];
$process_emp[$employee_code] = $employee_code;
$group_emp_month[$employee_code][$transactions_month] = $row;
}
$process_emp_rslt = [];
foreach($process_emp as $employee_code){
$group_emp_rslt = $group_emp_month[$employee_code];
$base_info = array_values($group_emp_rslt);
foreach($group_by as $group_col){
$group_col_info = explode(".",$group_col);
$col_name = $group_col_info[1];
$col_val = $base_info[0][$col_name];
$process_emp_rslt[$employee_code][] = $col_val;
}
$process_emp_rslt[$employee_code][] = $base_info[0]["employee_code"];
$process_emp_rslt[$employee_code][] = $base_info[0]["emp_name"];
// $process_emp_rslt[$employee_code][] = $base_info[0]["date_of_joining"]; // [MS 12-09-2024]
foreach($month_loop as $month){
if($group_emp_rslt[$month]){
$group_emp_info = $group_emp_rslt[$month];
foreach($componet_list as $comp_id => $comp_name){
$process_emp_rslt[$employee_code][] = $group_emp_info[$comp_id];
}
}else{
foreach($componet_list as $comp_id => $comp_name){
$process_emp_rslt[$employee_code][] = " ";
}
}
}
foreach($componet_list as $comp_id => $comp_name){
$process_emp_rslt[$employee_code][] = array_sum(array_column($base_info,$comp_id));
}
}
foreach($process_emp_rslt as $row){
$final_rslt[] = array_values($row);
}
}
}else
if($display_format === 2){//Vertical format
if($duration_type === 1){
array_unshift($report_rslt, null);
$vertical_info = call_user_func_array('array_map', $report_rslt);
unset($vertical_info[0]);
$componet_val = array_values($componet_list);
$ver_pro_count = 0;
foreach($vertical_info as $row){
$col_name = $componet_val[$ver_pro_count];
$inser_col = array("name"=>$col_name);
$ver_rslt = array_merge($inser_col,$row);
$ver_rslt[] = array_sum($ver_rslt);
$final_rslt[] = array_values($ver_rslt);
$ver_pro_count ++;
}
}else
if($duration_type === 2){
//BASIC INFO TO BUILT INFO - START
$pro_emp_rslt = [];
$pro_grop_info = [];
foreach($report_rslt as $row){
$employee_code = $row["employee_code"];
$transactions_month = $row["transactions_month"];
if(!$pro_emp_rslt[$employee_code]){
$pro_emp_rslt[$employee_code] = "";
}
if(!$pro_grop_info[$employee_code]){
$group_info = [];
foreach($pro_group as $group_id => $grop_name){
$group_info[$group_id] = $row[$group_id];
}
$group_info["employee_code"] = $row["employee_code"];
$group_info["emp_name"] = $row["emp_name"];
$group_info["components"] = $componet_list;
$pro_grop_info[$employee_code] = $group_info;
}
}
//BASIC INFO TO BUILT INFO - END
// ORDER BY BASED ON GIVEN REQ - START
$build_group_rslt = [];
foreach($pro_emp_rslt as $emp_code => $comp_list){
foreach($pro_month as $trans_id => $trans_name){
foreach($componet_list as $comp_id => $comp_name){
$build_group_rslt[$emp_code][$trans_id][$comp_id]= "";
}
}
$filter = array($emp_code);
$filter_month = array_filter($report_rslt, function($e) use ($filter){
return in_array($e['employee_code'], $filter);
});
foreach($filter_month as $filter_info){
$transactions_month = $filter_info['transactions_month'];
foreach($componet_list as $comp_id => $comp_name){
$build_group_rslt[$emp_code][$transactions_month][$comp_id] = $filter_info[$comp_id];
}
}
}
$build_group_rslt = array_replace_recursive($pro_grop_info,$build_group_rslt);
// ORDER BY BASED ON GIVEN REQ - END
// PROCESS FINAL OUTPUT FOR EXCEL - START
$loop_info = array_merge(array("components"=>"components"),$pro_month);
$final_rslt = [];
$pro_head["total"] = "Total";
$final_rslt[] = array_values($pro_head);
foreach($build_group_rslt as $row){
$first_row = [];
$innner = [];
foreach($pro_head as $pro_head_id => $pro_head_name){
if($pro_head_id !== "total"){
if(!$loop_info[$pro_head_id]){
$first_row[$pro_head_id] = $row[$pro_head_id];
}else{
$innner[$pro_head_id] = $row[$pro_head_id];
}
}
}
// array_unshift($innner, null);
$innner_trans = [];
if (!empty($innner)) {
$innner_trans = array_map(null, ...array_values($innner));
}
$final_inner = [];
foreach ($innner_trans as $trans) {
$comp_name = $trans[0] ?? null;
$get_key = array_search($comp_name, $componet_list);
if ($get_key !== false && !empty($total_list[$get_key])) {
$trans[] = array_sum($trans);
} else {
$trans[] = " ";
}
$final_inner[] = $trans;
}
$push_empty = array();
for($i = 0; $i <= count($first_row)-1; $i++){
$push_empty[] = " ";
}
$count = 0;
foreach($final_inner as $inner_info){
if($count === 0){
$user_final = array_merge($first_row,$inner_info);
$final_rslt[] = array_values($user_final);
}else{
$user_final = array_merge($push_empty,$inner_info);
$final_rslt[] = array_values($user_final);
}
$count++;
}
}
}
}
// PROCESS FINAL OUTPUT FOR EXCEL - END
$field_info = [];
if(($duration_type === 2) && ($report_type === 2) && ($display_format === 1)){ //Multi month, deatil ,Horizonal format
$field_info = $pro_head_field_2;
}else{
$field_info = $pro_head_field;
}
if(count($field_info) === 0){
echo json_encode(array('success'=>false,'message'=>"Unable to get field to download",'rslt'=>$rslt));
}else{
$rslt = [];
foreach($field_info as $key=>$value){
$rslt["field_info"][] = $value;
}
$rslt["final_rslt"] = $final_rslt;
echo json_encode(array('success'=>true,'message'=>"Result for given input is",'rslt'=>$rslt));
}
}
}
?>