File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Report.php
<?php
/**********************************************************
Filename: Report.php
Description: Report controller for all modules.
Author: Jaffer Sathik
Created on: 13 March 2019
Reviewed by:
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
if ( ! defined('BASEPATH')) exit('No direct script is allowed');
set_include_path( get_include_path().PATH_SEPARATOR."..");
include_once('./application/libraries/xlsxwriter.class.php'); //include the class file.
require_once("Report_controller.php");
class Report extends Report_controller{
public function __construct(){
parent::__construct('report');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$method = $this->uri->segment(2);
if($method === "index"){
$report_id = $this->uri->segment(3);
$this->session->set_userdata('report_id',$report_id);
}else{
$report_id = $this->session->userdata('report_id');
}
if(!$this->isvalid($report_id)){
redirect('home');
}
}
// LOAD PAGE WITH TABLE DATA
public function index($view_id = -1){
$data['table_headers'] = $this->table_info;
$data['fliter_list'] = $this->fliter_list;
$data['date_filter'] = $this->date_filter;
$data['date_column'] = $this->date_column;
/* $filter_info = $this->get_presaved_filter($view_id);
$data['filter_info'] = $filter_info;*/
$data['form_id'] = $view_id;
$data['company_information'] = $this->company_info();
$get_date_qry = 'SELECT prime_report_setting_id,date_filter_type,filter_start_date,filter_end_date,export_type,report_name,date_filter,date_column FROM cw_report_setting WHERE trans_status = 1 and prime_report_setting_id = "'.$view_id.'"';
$get_date_info = $this->db->query("CALL sp_a_run ('SELECT','$get_date_qry')");
$get_date_rslt = $get_date_info->result();
$get_date_info->next_result();
$date_filter_type = $get_date_rslt[0]->date_filter_type;
$data['report_name'] = $get_date_rslt[0]->report_name;
$data['date_filter'] = $get_date_rslt[0]->date_filter;
$date_column = $get_date_rslt[0]->date_column;
$data['date_column'] = $date_column;
if((int)$date_filter_type === 1){
$data['start_date'] = date('Y-m-d', strtotime('today - 30 days'));
$data['end_date'] = date('Y-m-d');
}else
if((int)$date_filter_type === 2){
$data['start_date'] = date('Y-m-01');
$data['end_date'] = date('Y-m-t');
}else
if((int)$date_filter_type === 3){
$data['start_date'] = date('Y-m-01');
$data['end_date'] = date('Y-m-d');
}else
if((int)$date_filter_type === 4){
$data['start_date'] = date('Y-m-d');
$data['end_date'] = date('Y-m-d');
}else
if((int)$date_filter_type === 5){
$data['start_date'] = date('Y-m-d',strtotime("-1 days"));
$data['end_date'] = date('Y-m-d',strtotime("-1 days"));
}else
if((int)$date_filter_type === 6){
$filter_start_date = $get_date_rslt[0]->filter_start_date;
$filter_end_date = $get_date_rslt[0]->filter_end_date;
$data['start_date'] = date("Y-m-$filter_start_date",strtotime('last month'));
$data['end_date'] = date("Y-m-$filter_end_date");
}
$data['total_tbl_count'] = (int)$this->total_tbl_count;
$data['join_tbl_count'] = (int)$this->join_tbl_count;
$data['date_filter_type'] = $date_filter_type;
$data['filter_start_date'] = $filter_start_date;
$data['filter_end_date'] = $filter_end_date;
$data['export_type'] = $get_date_rslt[0]->export_type;
$label_name_arr = explode(".", $date_column ?? "");
$module_id = str_replace("cw_", "", $label_name_arr[0]);
if($module_id === "transactions_fms" || $module_id === "monthly_input_fms" || $module_id === "transactions_fms_man"){
$module_id = "employees";
$where_cond = ' and prime_module_id="employees"';
}else
if($module_id === "misc_input_fms"){
$where_cond = ' and prime_module_id in("employees","misc_input_fms")';
}else{
$where_cond = ' and prime_module_id="'.$module_id.'"';
}
$date_label_name = $label_name_arr[1];
$form_view_name_qry = 'select view_name from cw_form_setting where label_name="'.$date_label_name.'" and trans_status=1'.$where_cond;
$label_name_data = $this->db->query("CALL sp_a_run ('SELECT','$form_view_name_qry')");
$view_name_rslt = $label_name_data->result();
$label_name_data->next_result();
$data['label_view_name'] = $view_name_rslt[0]->view_name;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
public function get_page_info(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$report_id = $this->input->post('report_id');
$this->page_info($report_id);
$module_sts = (int)$this->module_sts;
if(!$module_sts){
echo json_encode(array('success' => FALSE, 'message' => "Page Info Query Process Error..!"));
exit(0);
}else{
//Generate Filter Info
$filter_tr_line = "";
$table_map_list = "";
$input_ids = "";
$date_ids = "";
$filter_cond_array = array('' => '--- Select ---','=' => '=','LIKE' => 'LIKE','IN' => 'IN()');
$filter_line = "";
foreach($this->filter_info as $key => $fliter){
$label_id = $fliter['label_name'];
$view_name = $fliter['view_name'];
$array_list = $fliter['array_list'];
$field_type = $fliter['field_type'];
$pick_list = $fliter['pick_list'];
$pick_table = $fliter['pick_table'];
$auto_prime_id = $fliter['auto_prime_id'];
$auto_display_value = $fliter['auto_display_value'];
$filter_tbl_val = $fliter['tbl_name'];
//CUSTOM
if($label_id === 'wbs_element'||$label_id === 'position'||$label_id === 'emp_name'||$label_id === 'project_id'|| $label_id === 'personal_code'|| $label_id === 'employee_code' || (int)$field_type === 1){
$filter_cond_array = array('' => '--- Select ---','=' => '=','LIKE' => 'LIKE','IN' => 'IN()');
}else
if((int)$field_type === 2){
$filter_cond_array = array('' => '--- Select ---','=' => '=','>' => '>','<' => '<','LIKE' => 'LIKE');
}
else{
$filter_cond_array = array('' => '--- Select ---','=' => '=','LIKE' => 'LIKE');
}
//GETTING FILTER VALUES !
$label_name = ucwords(strtolower(str_replace("_"," ",$label_id)));
$filter_tbl_val = form_input(array('type'=>'hidden','name' => 'filter_tbl_val[]', 'class' => 'form-control input-sm','value' => $filter_tbl_val));
$field_type_input = form_input(array('type'=>'hidden','name' => 'field_type[]', 'class' => 'form-control input-sm','value' => $field_type));
$filter_cond = form_dropdown(array('name' => 'filter_cond[]', "id"=>$label_id."_con",'class' => 'form-control input-sm'), $filter_cond_array);
$filter_label = form_input(array('type'=>'hidden','name' => 'filter_label[]', 'class' => 'form-control input-sm','value' => $label_id));
if((int)$field_type === 4){
$filter_val = form_input(array( 'name' => 'filter_val[]', "id"=>$label_id, 'class' => 'form-control input-sm hidden_input','placeholder'=>"Search $view_name",'value' => '','type' => 'date','autocomplete' =>'off'));
}else
if((int)$field_type === 5 || (int)$field_type === 7 || (int)$field_type === 9){
$hid_input_name = "hid_".$label_id;
$hid_dl_id = "dl_".$label_id;
$form_input = "<input list='list_$label_id' name='$hid_input_name' id='$hid_dl_id' value='$def_text' class='form-control input-sm datalist' autocomplete='off' placeholder='Search $view_name' oninput='datalist_pick(this, \"$pick_list\", \"$pick_table\")'><datalist id='list_$label_id'>$option</datalist>"; //FUNCTION DATALIST_PICK
$hidden_input = form_input( array("name"=>"filter_val[]", "id"=>$label_id,"value"=>"",'class' => 'form-control input-sm','placeholder'=>"Search $view_name","type"=>"hidden","autocomplete" => "off"));
$filter_val = "$hidden_input $form_input";
$readonly = '';
}elseif($label_id === 'posting_month'){
$filter_val = form_input(array('name' => 'filter_val[]', "id"=>$label_id, 'class' => 'form-control input-sm monthpicker','placeholder'=>"Search $view_name", 'value'=>'','autocomplete' =>'off'));
$readonly = '';
}else{
$filter_val = form_input(array( 'name' => 'filter_val[]', "id"=>$label_id, 'class' => 'form-control input-sm hidden_input datalist', 'placeholder'=>"Search $view_name",'value' => '','autocomplete' =>'off'));
$readonly = '';
}
$filter_cond = form_dropdown(array('name' => 'filter_cond[]','id' => $label_id."_con",'oninput' => 'condition_pick(this, \''.$field_type.'\')', 'class' => 'form-control input-sm hidden_val ',$readonly => true),$filter_cond_array); //FUNCTION CONDITION_PICK
$filter_line .= "<tr>
<td class='search_td'>$field_type_input $view_name $filter_label $filter_tbl_val</td>
<td> $filter_cond </td>
<td> $filter_val </td>
</tr>";
}
$report_filter = form_input(array('type'=>'hidden','name' => 'report_filter_id','id' => 'report_filter_id', 'class' => 'form-control input-sm','value' => ''));
$filter_name = form_input(array('type'=>'hidden','id' => 'filter_name', 'class' => 'form-control input-sm','value' => ''));
$form_id_input = form_input(array( 'name' => 'form_id','id' => 'form_id','type'=>'hidden','class' => 'form-control input-sm','value' => "$report_id"));
$filter_table = "$filter_name $report_filter $report_id_input<table class='fliter_table' style='width:100%;'>$filter_line</table>";
echo json_encode(array('success' => TRUE, 'table_head' => $this->table_head, 'filter_table' => $filter_table, 'pick_list' => $this->pick_list));
}
}
public function search(){
$dec_data = $this->cryptoDecrypt($_POST['encrypted_data']);
$_POST = $dec_data['data'];
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..'));
exit(0);
}
$this->search_info($this->report_id);
$module_sts = (int)$this->module_sts;
if(!$module_sts){
echo json_encode(array('success' => FALSE, 'message' => "Search Info Query Process Error..!"));
exit(0);
}
$report_name = $this->input->post('report_name');
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col = $this->input->post('columns');
$search = $this->input->post('search');
$search = trim($search['value']);
//FOR SEARCH DATATABLE
$filter_label = $this->input->post('filter_label');
$field_type_list = $this->input->post('field_type');
$filter_cond = $this->input->post('filter_cond');
$filter_val = $this->input->post('filter_val');
$filter_tbl_val = $this->input->post('filter_tbl_val');
$order_col = $this->input->post('columns');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_tbl = $order_col[$column]['name'];
$order_col = $order_col[$column]['data'];
$start_date = str_replace("/","-",$this->input->post('start_date'));
$end_date = str_replace("/","-",$this->input->post('end_date'));
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$fliter_query = "";
$filter_count = count($filter_cond ?? []);
$date_time_arr = array("punch_in","punch_out","permission_in","permission_out","manual_in","manual_out");
for($i=0;$i<=(int)$filter_count;$i++){
$db_name = $filter_label[$i];
$db_cond = $filter_cond[$i];
$db_value = $filter_val[$i];
$db_tbl = $filter_tbl_val[$i];
$field_type = (int)$field_type_list[$i];
//BASED ON CONDITION LIKE,=,IN FILTER
if($db_cond === 'IN'){
$db_value= str_replace('"','',$db_value);
$value = '("' . implode('","', explode(',', $db_value ?? "") ?? []) . '")';
}else if($db_cond === 'LIKE'){
$value = "'%" . $db_value . "%'";
}else if($db_cond === '='){
$value = '"'.$db_value.'"';
}
if(($db_cond) && ($db_value || $db_value === '0')){
$search_count++;
if($field_type === 4){
$search_val = '"'.date('Y-m-d',strtotime($db_value)).'"';
}else
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$search_val = $value;
}else
if($field_type === 13){
if(in_array($db_name, $date_time_arr)){
$search_val = ' and date_format('.$db_tbl.'.'.$db_name.', "%H:%i")='.'"'.$db_value.'"';
}else{
$search_val = '"'.date('Y-m-d H:i:s',strtotime($db_value)).'"';
}
}else{
$search_val = $value;
}
if(in_array($db_name, $date_time_arr)){
$fliter_query .= $search_val;
}else{
$fliter_query .= ' and '. $db_tbl .".". $db_name ." ". $db_cond .' '.$search_val.'';
}
}
}
//echo $fliter_query; die;
//DATA SEARCH IN DATATABLE SEARCH INPUT
$common_search = "";
if($search){
foreach($this->search_info_arr as $setting){
$prime_form_id = $setting['prime_form_id'];
$field_type = (int)$setting['field_type'];
$pick_list = $setting['pick_list'];
$pick_table = $setting['pick_table'];
$pick_list_type = $setting['pick_list_type'];
$input_view_type = (int)$setting['input_view_type'];
$auto_prime_id = $setting['auto_prime_id'];
$auto_dispaly_value = $setting['auto_dispaly_value'];
$label_id = strtolower(str_replace(" ","_",$setting['label_name']));
$field_isdefault = (int)$setting['field_isdefault'];
$tbl_name = $setting['tbl_name'];
if($field_isdefault === 1){
if(($input_view_type === 1) || ($input_view_type === 2)){
$search_label = "$tbl_name.$label_id";
$search_val = "";
if($field_type === 4){ // having issues in date search
if(strtotime($search)){
$search_val = date('Y-m-d',strtotime($search));
$common_search .= ' or '. $search_label .' like "'.$search_val.'%"';
}
}else{
$common_search .= ' or '. $search_label .' like "'.$search.'%"';
}
}
}
}
if($common_search){
$common_search = ltrim($common_search,' or ');
$common_search = " and ($common_search)";
$common_search = str_replace("(,","(",$common_search);
$common_search = str_replace("()","(0)",$common_search);
}
}
$expect_id = "";
$prime_tbl_id = str_replace('cw_', '', $this->prime_table);
$sort_code = $this->prime_table."."."prime_".$prime_tbl_id."_id";
if($order_col === 'prime_report_id'){
$order_col = $sort_code;
$order_tbl = $this->prime_table;
}
if(!$order_sor){$order = "asc";}
$basic_query = "";
if($this->table_search_info){
$basic_query = $this->table_search_info;
}
$sort_column = "";
if($this->sort_column){
$order_col = $this->sort_column;
}else{
$order_col = $order_col;
}
//building date filter query record
$date_filter = $this->date_filter;
if((int)$date_filter === 1){
$date_column = explode(",",$this->date_column ?? "");
$date_column_count = count($date_column ?? []);
$date_search = "";
for($i = 0;$i < $date_column_count; $i++){
$date_column_search = $date_column[$i];
$column_name = explode(".",$date_column_search ?? "");
$column_name = $column_name[1];
if($column_name == "transactions_month" || $column_name == "process_month" || $column_name == "posting_month"){
$date_column_search = '(date_format(str_to_date(CONCAT("01-", '.$date_column_search.'), "%d-%m-%Y") , "%Y-%m-%d")';
}else{
$date_column_search = '(DATE_FORMAT('.$date_column_search.', "%Y-%m-%d")';
}
$date_search .= ' and '.$date_column_search.' BETWEEN "'.$start_date.'" and "'.$end_date.'")';
}
}else{
$date_search = "";
$transaction_sts = "";
}
//COMMON QUERY FOR SERACH AND FILTERS
$transaction_sts = "";
$group_by = "";
if($this->group_column){
$this->select_query = $this->select_sum_query;
$group_by = "GROUP BY ".$this->group_column;
}
$tbl_name = str_replace('cw_', '', $this->prime_table);
$current_tbl = $this->prime_table.'.prime_'.$tbl_name.'_id';
if($order_col === $current_tbl){
$order_col = $order_col;
}else{
$order_col = $order_col;
if($order_tbl){
$order_col = "$order_tbl.$order_col";
}
}
$count_query = str_replace("@SELECT",$this->select_query,$this->base_query);
$count_query .= " where $expect_id $this->prime_table.trans_status = 1 $transaction_sts $basic_query $fliter_query $common_search $date_search";
$count_query .= " $group_by ORDER BY $order_col $order_sor";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_count->num_rows();
$search_count->next_result();
//NEW JOINERS REPORT
if($report_name === 'New Joiners'){
$report_cond = " and cw_employees.sap_status = 5";
}
//TRANS FAILURE LOG
if($report_name === 'Transaction failure report'){
$report_cond = " and cw_trans_failure_log.trans_status = 1";
}
$search_query = str_replace("@SELECT",$this->select_query,$this->base_query);
$search_query .= " where $expect_id $this->prime_table.trans_status = 1 $transaction_sts $basic_query $fliter_query $common_search $date_search $report_cond";
$search_query .= " $group_by ORDER BY $order_col $order_sor";
if($per_page > 0){
$search_query .= " LIMIT $start,$per_page";
}else{
$all_count = (int)$filtered_count-2;
$search_query .= " LIMIT $start,$filtered_count";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
$search_data->next_result();
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $filtered_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
function multi_array_search($array, $search){
$array = json_decode(json_encode($array),True);
$result = array();
foreach ($array as $key => $value){
foreach ($search as $k => $v){
if (!isset($value[$k]) || $value[$k] != $v){
continue 2;
}
}
$result[] = $key;
}
return $result;
}
public function edit_filter_report(){
$report_filter_id = $this->input->post('report_id');
$select_qry = 'select cw_report_filter.report_filter_id,cw_report_filter.filter_name,`filter_id`, `filter_con`, `filter_con`,`field_type`, `filter_val` from cw_report_filter_line join cw_report_filter on cw_report_filter.report_filter_id = cw_report_filter_line.report_filter_id where cw_report_filter_line.trans_status = 1 and cw_report_filter.report_filter_id = "'.$report_filter_id.'"';
$select_info = $this->db->query("CALL sp_a_run ('SELECT','$select_qry')");
$select_result = $select_info->result();
$select_info->next_result();
echo json_encode(array('success' => true,'edit_data'=>$select_result));
}
public function get_presaved_filter($view_id){
// PRESAVED FILTER
$pre_filter_qry = 'select report_filter_id,filter_name from cw_report_filter where trans_status = 1 and prime_report_id = "'.$view_id.'"';
$pre_filter_qry = $this->db->query("CALL sp_a_run ('SELECT','$pre_filter_qry')");
$pre_filter_data = $pre_filter_qry->result();
$pre_filter_qry->next_result();
$filter_info = array(''=>'--select--');
foreach($pre_filter_data as $filter){
$filter_info[$filter->report_filter_id] = $filter->filter_name;
}
return $filter_info;
}
public function company_info(){
$company_info = $this->db->query("CALL sp_a_run ('SELECT','select company_name,company_short_name,mobile_number from cw_company_information')");
$company_infomation = $company_info->result();
$company_info->next_result();
return $company_infomation[0];
}
public function export_to_excel(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$form_id = $this->input->post('form_id');
$this->page_info($form_id);
$this->search_info($form_id);
$module_sts = (int)$this->module_sts;
if(!$module_sts){
echo json_encode(array('success' => FALSE, 'message' => "Search Info Query Process Error..!"));
exit(0);
}
$filter_label = $this->input->post('filter_label');
$filter_type = $this->input->post('filter_type');
$field_type_list = $this->input->post('field_type');
$filter_cond = $this->input->post('filter_cond');
$filter_val = $this->input->post('filter_val');
$filter_tbl_val = $this->input->post('filter_tbl_val');
$order_col = $this->input->post('columns');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_tbl = $order_col[$column]['name'];
$order_col = $order_col[$column]['data'];
$start_date = str_replace("/","-",$this->input->post('start_date'));
$end_date = str_replace("/","-",$this->input->post('end_date'));
$start_date = date('Y-m-d',strtotime($start_date));
$end_date = date('Y-m-d',strtotime($end_date));
$fliter_query = "";
$filter_count = count($filter_label ?? []);
$date_time_arr = array("punch_in","punch_out","permission_in","permission_out","manual_in","manual_out");
for($i=0;$i<=(int)$filter_count;$i++){
$db_name = $filter_label[$i];
$table_name = $filter_type[$i];
$db_cond = $filter_cond[$i];
$db_value = $filter_val[$i];
$db_tbl = $filter_tbl_val[$i];
$field_type = (int)$field_type_list[$i];
if($db_cond === 'IN'){
$db_value = str_replace('"','',$db_value);
$value = '("' . implode('","', explode(',', $db_value ?? "") ?? []) . '")';
}else if($db_cond === 'LIKE'){
$value = "'%" . $db_value . "%'";
}else if($db_cond === '='){
$value = '"'.$db_value.'"';
}
if(($db_cond) && ($db_value || $db_value === '0')){
//$search_count++;
if($field_type === 4){
$search_val = '"'.date('Y-m-d',strtotime($db_value)).'"';
}else
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$search_val = $value;
}else
if($field_type === 13){
if(in_array($db_name, $date_time_arr)){
$search_val = ' and date_format('.$db_tbl.'.'.$db_name.', "%H:%i")='.'"'.$db_value.'"';
}else{
$search_val = '"'.date('Y-m-d H:i:s',strtotime($db_value)).'"';
}
}else{
$search_val = $value;
}
if(in_array($db_name, $date_time_arr)){
$fliter_query .= $search_val;
}else{
$fliter_query .= ' and '. $db_tbl .".". $db_name ." ". $db_cond .' '.$search_val.'';
}
}
}
//DATA SEARCH IN DATATABLE SEARCH INPUT
$common_search = "";
if($search){
foreach($this->form_info as $setting){
$field_type = (int)$setting->field_type;
$input_view_type = (int)$setting->input_view_type;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = (int)$setting->field_isdefault;
if($field_isdefault === 1){
if(($input_view_type === 1) || ($input_view_type === 2)){
$search_label = "$this->prime_table.$label_id";
$search_val = "";
if($field_type === 4){ // having issues in date search
if(strtotime($search)){
$search_val = date('Y-m-d',strtotime($search));
$common_search .= ' or '. $search_label .' like "'.$search_val.'%"';
}
}else{
$common_search .= ' or '. $search_label .' like "'.$search.'%"';
}
}
}
}
if($common_search){
$common_search = ltrim($common_search,' or ');
$common_search = " and ($common_search)";
$common_search = str_replace("(,","(",$common_search);
$common_search = str_replace("()","(0)",$common_search);
}
}
$expect_id = "";
$prime_tbl_id = str_replace('cw_', '', $this->prime_table);
$sort_code = $this->prime_table."."."prime_".$prime_tbl_id."_id";
if($order_col === 'prime_report_id'){
$order_col = $sort_code;
$order_tbl = $this->prime_table;
}
if(!$order_sor){$order = "asc";}
$basic_query = "";
if($this->table_search_info){
$basic_query = $this->table_search_info;
}
$sort_column = "";
if($this->sort_column){
$order_col = $this->sort_column;
}else{
$order_col = $order_col;
}
//building date filter query record
$date_filter = $this->date_filter;
if((int)$date_filter === 1){
$date_column = explode(",",$this->date_column ?? "");
$date_column_count = count($date_column ?? []);
$date_search = "";
for($i = 0;$i < $date_column_count; $i++){
$date_column_search = $date_column[$i];
$column_name = explode(".",$date_column_search ?? "");
$column_name = $column_name[1];
if($column_name == "transactions_month" || $column_name == "process_month" || $column_name == "posting_month"){
$date_column_search = '(date_format(str_to_date(CONCAT("01-", '.$date_column_search.'), "%d-%m-%Y") , "%Y-%m-%d")';
}else{
$date_column_search = '(DATE_FORMAT('.$date_column_search.', "%Y-%m-%d")';
}
$date_search .= ' and '.$date_column_search.' BETWEEN "'.$start_date.'" and "'.$end_date.'")';
}
}else{
$date_search = "";
$transaction_sts = "";
}
//COMMON QUERY FOR SERACH AND FILTERS
$transaction_sts = "";
$group_by = "";
if($this->group_column){
$this->select_query = $this->select_sum_query;
$group_by = "GROUP BY ".$this->group_column;
}
$tbl_name = str_replace('cw_', '', $this->prime_table);
$current_tbl = $this->prime_table.'.prime_'.$tbl_name.'_id';
if($order_col === $current_tbl){
$order_col = $order_col;
}else{
$order_col = $order_col;
if($order_tbl){
$order_col = "$order_tbl.$order_col";
}
}
$search_query = str_replace("@SELECT",$this->select_query,$this->base_query);
$search_query .= " where $expect_id $this->prime_table.trans_status = 1 $transaction_sts $basic_query $fliter_query $common_search $date_search";
$search_query .= " $group_by ORDER BY $sort_code ASC";
//$search_query .= " LIMIT $offset,$limit";
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$search_data->next_result();
$form_rslt_data = array();
foreach ($this->form_info as $key => $value){
$form_rslt_data[$value->label_name] = $value;
}
$writer = new XLSXWriter(); //Create an object
$header = array();
$head = true; // Header should write only 1st row
foreach ($search_result as $res_data) {
$row = array();
foreach ($res_data as $sc_key => $value){
$field_type = (int)$form_rslt_data[$sc_key]->field_type;
$label_name = $form_rslt_data[$sc_key]->label_name;
$view_name = $form_rslt_data[$sc_key]->view_name;
if($field_type === 2){ //TWO DECIMAL
$header[$view_name] = '0.00';
}else
if($field_type === 3){ //TWO DECIMAL
$header[$view_name] = 'integer';
}else
if($field_type === 4){ //DATE
$header[$view_name] = 'DD/MM/YYYY';
}else
if($field_type === 13){ //DATE & TIME
$header[$view_name] = 'DD/MM/YYYY HH:MM:SS';
}else
if($field_type === 5 || $field_type === 9){ //PICKLIST
$header[$view_name] = 'string';
if($this->pick_list[$sc_key]['array_list'][$value]){
if(($value === '0' || $value === '') && $sc_key !== 'termination_status' && $sc_key !== 'marital_code'){
$value = '';
}else{
$value = $this->pick_list[$sc_key]['array_list'][$value];
if(!$value){
$value = '';
}
}
}else{
if($value === '0' || $value === ''){
$value = '';
}
}
}else{
$header[$view_name] = 'string';
}
$row[$label_name] = $value;
}
if($head){
$writer->writeSheetHeader('BasicFormats',$header); //write header
$head = false;
}
$writer->writeSheetRow('BasicFormats',$row ); //write rows
}
$folder = "./report_excel";
if (!file_exists($folder)){ //Create folder if not exist
mkdir($folder, 0777, true);
}
$filename = "export_".$this->logged_id.".xlsx"; //Create file with current user id
unlink($folder.'/'.$filename); //remove the file
$writer->writeToFile($folder.'/'.$filename);
echo json_encode(array('success' => true,'message'=>"Excel Exported Successfully...",'file_name'=>$filename));
}
//FUNCTION FOR RETRIEVING DATA BASED ON FILTER CONDITION
public function filter_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$search_term = $this->input->post('term');
$pick_list = $this->input->post('pick_list');
$pick_table = $this->input->post('pick_table');
$search_info = $this->db->query("CALL sp_report ('$search_term','$pick_list','$pick_table')");
$search_rslt = $search_info->result();
$search_info->next_result();
if($search_rslt[0]){
echo json_encode(array('success' => true, 'search_rslt' => $search_rslt));
}else{
echo json_encode(array('success' => false, 'message' => "No Data Found..!"));
}
}
}
?>