File: /home/cafsindia/hrms_allyindian_com/application_bk/controllers/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');
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');
}
$report_id = null;
$urlValue = $this->uri->segment(3); // or wherever the encrypted ID is
$base64 = strtr($urlValue, '-_', '+/');
$base64 = str_pad($base64, strlen($base64) % 4 ? strlen($base64) + 4 - strlen($base64) % 4 : strlen($base64), '=', STR_PAD_RIGHT);
$encString = base64_decode($base64);
if($encString){
$decrypted = $this->cryptoDecrypt($encString);
if($decrypted && is_numeric($decrypted)){
$report_id = $decrypted;
$this->session->set_userdata('report_id', $report_id);
}
}
// 2. If no valid URI value, try session
if(!$report_id){
$session_report = $this->session->userdata('report_id');
if($session_report && is_numeric($session_report)){
$report_id = $session_report;
}
}
// 3. Validate
if(!$report_id || !$this->isvalid($report_id)){
redirect('home');
exit;
}
}
// LOAD PAGE WITH TABLE DATA
public function index($view_id = -1){
$view_id = $this->cryptoDecrypt($view_id);
$data['table_headers'] = $this->table_info;
$data['fliter_list'] = $this->fliter_list;
$data['report_name'] = $this->report_name;
$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 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;
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;
list($year, $month, $day) = explode('-', $filter_start_date);
$data['start_date'] = date('Y-m-d', strtotime("last month", strtotime("$year-$month-$day")));
$data['end_date'] = date("Y-m-$filter_end_date");
}else
if((int)$date_filter_type === 7){
$filter_start_date = $get_date_rslt[0]->filter_start_date;
$filter_end_date = $get_date_rslt[0]->filter_end_date;
if($get_date_rslt[0]->filter_end_date > date('d')){
$filter_end_date = date('d');
}
$data['start_date'] = date("Y-m-$filter_start_date",strtotime('last month'));
$data['end_date'] = date("Y-m-d");
}
else
if((int)$date_filter_type === 8){
$financial_info = $this->get_leave_financial_details();
//current financial yr!
$fin_start_date = $financial_info[0]->starting_date;
$fin_end_date = $financial_info[0]->ending_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'] = $get_date_rslt[0]->filter_start_date;
$data['filter_end_date'] = $filter_end_date;
$data['fin_start_date'] = $fin_start_date;
$data['fin_end_date'] = $fin_end_date;
$data['export_type'] = $get_date_rslt[0]->export_type;
$table_head_arr = json_decode(json_encode($data['table_headers']),true);
$data['table_head_arr'] = array_reduce($table_head_arr, function($result, $arr){
$result[$arr['label_name']] = $arr['view_name'];
return $result;
}, array());
//FOR DATE INPUT LABEL NAME
$label_name_arr = explode(".", $this->date_column);
$module_id = str_replace("cw_", "", $label_name_arr[0]);
if($module_id === "transactions" || $module_id === "monthly_input"){
$module_id = "employees";
$where_cond = ' and prime_module_id="employees"';
}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['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
public function search(){
$dec_data = $this->cryptoDecrypt($_POST['Payload']);
$_POST = $dec_data['data'];
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....'));
exit(0);
}
$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']);
$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'));
$from_date = date('Y-m-d',strtotime($start_date));
$to_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) &&($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)){
$search_val = '("'.implode('","',explode(',',$db_value ?? "") ?? []).'")';
$db_cond = 'IN';
}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 = '"'.$db_value.'"';
}
if((int)$table_name === 1){
if(in_array($db_name, $date_time_arr)){
$fliter_query .= $search_val;
}else{
if($this->add_info[$db_name]){
$cond_qry = $this->add_info[$db_name];
$cond_qry = str_replace("as", "", $cond_qry);
$cond_qry = str_replace("@", "", $cond_qry);
$cond_qry = str_replace('~','"', $cond_qry);
$cond_qry = str_replace("$db_name", " ", $cond_qry);
$fliter_query .= ' and '.$cond_qry." ". $db_cond .' '.$search_val.'';
}else{
$fliter_query .= ' and '. $db_tbl .".". $db_name ." ". $db_cond .' '.$search_val.'';
}
}
}
}
}
$common_search = "";
if($search){
$count=0;
foreach($this->form_info as $setting){
$prime_form_id = $setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$field_type = $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 = $setting->label_id;
$field_isdefault =(int)$setting->field_isdefault;
if(!empty($prime_module_id)){
$table_name = "cw_".$prime_module_id;
$prime_id = "prime_".$prime_module_id."_id";
}
$pick_sel_table = $table_name;
$other_label_id = $pick_sel_table.".".$label_id;
if($pick_table === "cw_custom_employees"){
if($this->add_info[$label_id]){
$cond_qry = str_replace("as", "", $cond_qry);
$cond_qry = str_replace("@", "", $cond_qry);
$cond_qry = str_replace('~','"', $cond_qry);
$cond_qry = str_replace(" $label_id", "", $cond_qry);
$other_label_id = $cond_qry;
}else{
$other_label_id = $pick_table.".".$label_id;
}
}
if(($input_view_type === 1) ||($input_view_type === 2)){
if((int)$field_type === 4){
if(strtotime($search)){
$search_val = date('Y-m-d',strtotime($search));
$common_search .= ' or '. $other_label_id .' like "%'.$search_val.'%"';
}
}else
if(((int)$field_type === 5) ||((int)$field_type === 7)){
if((int)$pick_list_type === 1){
$column_name = explode(",",$pick_list);
$column_name = $column_name[1];
}else
if((int)$pick_list_type === 2){
$column_name = $pick_table."_value";
}
$pick_query_as = $pick_table."_".$prime_form_id;
if($pick_table === "cw_custom_employees"){
$pick_query_as = $pick_table;
}
$label_id = "$pick_query_as.$column_name";
$common_search .= ' or '. $label_id .' like "%'.$search.'%"';
}else
if((int)$field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
if($pick_table === "cw_custom_employees"){
$pick_query_as = $pick_table;
}
$label_id = "$pick_query_as.$auto_dispaly_value";
$common_search .= ' or '. $label_id .' like "%'.$search.'%"';
}else{
if($search){
if($this->add_info[$label_id]){
continue;
}
$common_search .= ' or '. $other_label_id .' like "%'.$search.'%"';
}
}
}
}
if($common_search){
$common_search = ltrim($common_search,' or ');
$common_search = " and($common_search)";
}
}
$expect_id = "";
if($this->prime_table === "cw_employees"){
if(strstr($this->select_query,'cw_transactions')){
$sort_code = "cw_transactions.role,cw_transactions.employee_code ";
}else{
$sort_code = "cw_employees.role,cw_employees.employee_code ";
}
$expect_id = " cw_employees.prime_employees_id !=1 and ";
}else
if($this->prime_table === "cw_transactions"){
$sort_code = "prime_transactions_id";
}else
if($this->prime_table === "cw_monthly_input"){
$sort_code = $this->prime_table.".prime_monthly_input_id";
}else
if($this->prime_table === "cw_time_entry"){
$sort_code = $this->prime_table.".prime_time_entry_id";
}else
if($this->prime_table === "cw_live_attendance"){
$sort_code = $this->prime_table.".prime_live_attendance_id";
}else{
$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"){
$date_column_search = '(date_format(str_to_date(CONCAT("01-", '.$date_column_search.'), "%d-%m-%Y") , "%Y-%m-%d")';
$transaction_sts = "and cw_transactions.trans_status = 1 ";
}else
if($column_name == "process_month"){
$date_column_search = '(date_format(str_to_date(CONCAT("01-", '.$date_column_search.'), "%d-%m-%Y") , "%Y-%m-%d")';
$transaction_sts = "and cw_transactions.trans_status = 1 ";
}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
if(strstr($this->select_query,'cw_transactions')){
$transaction_sts = "and cw_transactions.trans_status = 1 ";
}else{
$transaction_sts = "";
}
$group_by = "";
if($this->group_column){
$this->select_query = $this->select_sum_query;
$group_by = "GROUP BY ".$this->group_column;
}
if($this->prime_table === "cw_employees"){
if(strstr($this->select_query,'cw_transactions')){
$current_tbl = "cw_transactions.role,cw_transactions.employee_code ";
}else{
$current_tbl = "cw_employees.role,cw_employees.employee_code ";
}
}else
if($this->prime_table === "cw_monthly_input"){
$current_tbl = $this->prime_table.".prime_monthly_input_id";
}else
if($this->prime_table === "cw_time_entry"){
$current_tbl = $this->prime_table.".prime_time_entry_id";
}else
if($this->prime_table === "cw_live_attendance"){
$current_tbl = $this->prime_table.".prime_live_attendance_id";
}else{
$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 = $count_query. $this->pick_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("CALL sp_a_run('SELECT','$count_query')");
$search_info = $search_count->result();
$filtered_count = $search_count->num_rows();
$search_count->next_result();
$count_all_query = str_replace("@SELECT","count(*) as allcount",$this->base_query);
$search_total = $this->db->query($count_all_query);
$search_total_info = $search_total->result();
$total_count = $search_total_info[0]->allcount;
$select_info = str_replace("@SELECT",$this->select_query,$this->base_query);
$search_query = $select_info. $this->pick_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 $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("CALL sp_a_run('SELECT','$search_query')");
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
$search_data->next_result();
// QUERY RESULT FOR SUB TOTAL
$group_by = "";
if($this->group_column){
$group_colum_search = $this->group_column;
$search_table_name = 'cw_transactions';
$pos = strpos($group_colum_search, $search_table_name);
if($pos !== false){
$transaction_sts = "and cw_transactions.trans_status = 1 ";
}else{
$transaction_sts ="";
}
$group_by = "group by ".$this->group_column;
$is_exit = strstr($this->group_column,$sort);
if($is_exit){
$sort = str_replace("$sort,","",$this->group_column);
}else{
$sort = $this->group_column;
}
if((int)$this->sub_tot_show === 1){
$replace_select = $this->select_query .",".$this->sum_qry_column;
}else{
$replace_select = $this->select_query;
}
$sub_tot_info = str_replace("@SELECT",$replace_select,$this->base_query);
$group_query = $sub_tot_info. $this->pick_query;
$group_query .= " where $this->prime_table.trans_status = 1 $transaction_sts $basic_query $fliter_query $common_search $date_search";
$group_query .= " $group_by ORDER BY $order_col $order_sor";
$group_data = $this->db->query("CALL sp_a_run('SELECT','$group_query')");
$group_result = $group_data->result();
$group_data->next_result();
if((int)$this->sub_tot_show === 1){
foreach(explode(",",$this->sum_column) as $sum){
$sum_column[$sum] = $sum;
}
$exist_array = explode(",",$this->group_column);
foreach($exist_array as $exist){
$column_split = explode(".",$exist);
$exist_column[] = $column_split[1];
}
foreach($group_result as $group_info){
foreach($exist_column as $column){
$value = $group_info->$column;
$check_array[$column] = $value;
}
foreach($group_info as $key=>$value){
if(!$sum_column[$key]){
$group_info->$key = "-";
}
}
$group_info->sub_total_exist = true;
$push_keys = $this->multi_array_search($search_result, $check_array);
$check_array = array();
$push_keys = end($push_keys)+1;
$push_arrya = array($push_keys =>$group_info);
array_splice( $search_result, $push_keys, 0, $push_arrya);
}
}
}
// QUERY RESULT FOR FINAL TOTAL
if($this->sum_qry_column !== ""){
$sum_colum_search = $this->sum_qry_column;
$search_table = 'cw_transactions';
$pos = strpos($sum_colum_search, $search_table);
if($pos !== false){
$transaction_sts = "and cw_transactions.trans_status = 1 ";
}else{
$transaction_sts ="";
}
$final_sum = str_replace("@SELECT",$this->sum_qry_column,$this->base_query);
$final_sum_query = $final_sum. $this->pick_query;
$final_sum_query .= " where $this->prime_table.trans_status = 1 $transaction_sts $basic_query $fliter_query $common_search $date_search";
$final_sum_query .= " ORDER BY $order_col $order_sor";
$final_sum_data = $this->db->query("CALL sp_a_run('SELECT','$final_sum_query')");
$final_sum_result = $final_sum_data->result();
$final_sum_data->next_result();
if(count($final_sum_result ?? [])>0){
$push_keys = count($search_result ?? [])+1;
$final_sum_result[0]->total_exist = true;
$push_arrya = array($push_keys =>$final_sum_result[0]);
array_splice($search_result, $push_keys, 0, $push_arrya);
}
}
//Total row count data details
if($this->select_query){
$emp_column_name = explode(",",$this->select_query ?? "");
$emp_column_name = $emp_column_name[1];
if(strstr($emp_column_name, ' as ' ) ) {
$emp_column_name = explode(".",$emp_column_name);
$pattern = '/([a-z]+)\w as /';
$replacement = '';
$emp_column_name = preg_replace($pattern, $replacement, $emp_column_name[1]);
}else{
$pattern = '/^cw_([a-z]+)\.\b/';
$replacement = '';
$emp_column_name = preg_replace($pattern, $replacement, $emp_column_name);
}
}
if($filtered_count){
$emp_column_count = explode(".",$emp_column_name ?? "");
if((int)count($emp_column_count ?? [])>1){
$emp_column_name = $emp_column_count[1];
}
$push_emp_count = count($search_result ?? [])+1;
$emp_count =(object)[$emp_column_name =>$filtered_count,'emp_tot_count'=>1];
$push_emp_array = array($push_emp_count =>$emp_count);
array_splice($search_result, $push_emp_count, 0, $push_emp_array);
}
$emp_pick_query = 'select employee_code,emp_name,prime_employees_id from cw_employees where trans_status = 1';
$emp_pick_info = $this->db->query("CALL sp_a_run('SELECT','$emp_pick_query')");
$emp_pick_rlst = $emp_pick_info->result_array();
$emp_pick_info->next_result();
$emp_pick_rlst_arr = array();
foreach($emp_pick_rlst as $key => $value) {
$emp_pick_rlst_arr[$value['prime_employees_id']] = $value;
}
$data_rows = array();
foreach($search_result as $search){
$data_rows[] = get_report_row($search,$this->table_info,$this,$emp_pick_rlst_arr);
}
$recordsFiltered = count($data_rows ?? []);
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $data_rows));
}
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(){
$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_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 filter_save(){
$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);
}
$created_on = date("Y-m-d H:i:s");
$logged_id = $this->logged_id;
$report_filter_id =(int)$this->input->post('report_filter_id');
$filter_name = $this->input->post('filter_name');
$form_id = $this->input->post('form_id');
$field_type_list = $this->input->post('field_type');
$filter_label = $this->input->post('filter_label');
$filter_type = $this->input->post('filter_type');
$filter_cond = $this->input->post('filter_cond');
$filter_val = $this->input->post('filter_val');
$filter_count = count($filter_label ?? []);
$insert_count = 0;
$insert_val_query = "";
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];
$field_type = $field_type_list[$i];
if(($db_cond) &&($db_value || $db_value === "0")){
$insert_val_query .= "(\"@report_filter_id@\",\"$db_name\",\"$db_cond\",\"$db_value\",\"$field_type\",\"$logged_id\",\"$created_on\"),";
$insert_count++;
}
}
$insert_col_query = "report_filter_id,filter_id,filter_con,filter_val,field_type,trans_created_by,trans_created_date";
if($report_filter_id === 0){
if((int)$insert_count > 0){
if(!$this->check_filter_exists($form_id,$filter_name)){
$insert_val_query = rtrim($insert_val_query,',');
$prime_insert_query = "insert into cw_report_filter(prime_report_id,filter_name,trans_created_by,trans_created_date) values(\"$form_id\",\"$filter_name\",\"$logged_id\",\"$created_on\")";
$insert_info = $this->db->query("CALL sp_a_run('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$insert_val_query = str_replace("@report_filter_id@","$insert_id","$insert_val_query");
$filter_line_query = "insert into cw_report_filter_line($insert_col_query) values $insert_val_query";
$filter_line_info = $this->db->query("CALL sp_a_run('INSERT','$filter_line_query')");
$insert_result = $filter_line_info->result();
$filter_line_info->next_result();
$filter_list = $this->get_presaved_filter($form_id);
echo json_encode(array('success' => true, 'message' => "Report Filter successfully added",'filter_list'=>$filter_list));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Filter Report Name already"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Filter conditions is not equal to filter values"));
}
}else{
if((int)$insert_count > 0){
if(!$this->check_filter_exists($form_id,$filter_name,$report_filter_id)){
$insert_val_query = rtrim($insert_val_query,',');
$update_qry = 'UPDATE cw_report_filter SET filter_name = "'.$filter_name.'" ,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where report_filter_id = "'.$report_filter_id.'"';
$this->db->query("CALL sp_a_run('SELECT','$update_qry')");
$delete_query = 'DELETE FROM cw_report_filter_line WHERE report_filter_id = "'.$report_filter_id.'"';
$this->db->query("CALL sp_a_run('RUN','$delete_query')");
$insert_val_query = str_replace("@report_filter_id@","$report_filter_id","$insert_val_query");
$filter_line_query = "insert into cw_report_filter_line($insert_col_query) values $insert_val_query";
$filter_line_info = $this->db->query("CALL sp_a_run('INSERT','$filter_line_query')");
$insert_result = $filter_line_info->result();
$filter_line_info->next_result();
$filter_list = $this->get_presaved_filter($form_id);
echo json_encode(array('success' => true, 'message' => "Report Filter successfully Updated",'filter_list'=>$filter_list));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Filter Name Already Exists..!"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Filter conditions is not equal to filter values"));
}
}
}
//CHECK FAULT ALREADY EXISTS
public function check_filter_exists($prime_report_setting_id,$filter_name,$report_filter_id = -1){
$search_qry = 'select count(*) as counts from cw_report_filter where prime_report_id = "'.$prime_report_setting_id.'" and filter_name = "'.$filter_name.'" and trans_status = 1';
if((int)$report_filter_id > 0){
$search_qry .= ' and report_filter_id != "'.$report_filter_id.'"';
}
$select_info = $this->db->query("CALL sp_a_run('SELECT','$search_qry')");
$select_result = $select_info->result();
$select_info->next_result();
if((int)($select_result[0]->counts) > 0){
return TRUE;
}else{
return FALSE;
}
}
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,address 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' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
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')){
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";
}
}
$select_info = str_replace("@SELECT",$this->select_query,$this->base_query);
$search_query = $select_info. $this->pick_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_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_id] = $value;
}
$writer = new XLSXWriter(); //Create an object
$header = array();
$head = true; // Header should write only 1st row
// FOR ARRANGE HEADER COLUMNS [MS 26-10-2024]
$emp_pick_query = 'select employee_code,emp_name,prime_employees_id from cw_employees where trans_status = 1';
$emp_pick_info = $this->db->query("CALL sp_a_run('SELECT','$emp_pick_query')");
$emp_pick_rlst = $emp_pick_info->result_array();
$emp_pick_info->next_result();
$emp_pick_rlst_arr = array();
foreach($emp_pick_rlst as $key => $value) {
$emp_pick_rlst_arr[$value['prime_employees_id']] = $value;
}
$data_rows = array();
foreach($search_result as $search){
$data_rows[] = get_report_row($search,$this->table_info,$this,$emp_pick_rlst_arr);
}
foreach($data_rows 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_id;
$view_name = $form_rslt_data[$sc_key]->view_name;
$value = str_replace('~','"',$value);
$value = str_replace("`","'",$value);
$value = str_replace("^","&",$value);
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
if($value && $value !== '-'){
$value = date("Y-m-d",strtotime($value));
}else{
$value ='';
}
$header[$view_name] = 'DD-MM-YYYY';
}else
if($field_type === 13){ //DATE & TIME
if($value && $value !== '0000-00-00 00:00:00'){
$value = date("Y-m-d H:i:s",strtotime($value));
}else{
$value ='';
}
$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 === '')){
$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){
$column_widths = array_fill(0, count($header), 20); // set all column widths to 20
$columnStyles = ['widths' => $column_widths];
$writer->writeSheetHeader('WorkSheet',$header,$columnStyles); //write header
$head = false;
}
$writer->writeSheetRow('WorkSheet',$row ); //write rows
}
$folder = "./report_excel";
if(!file_exists($folder)){ //Create folder if not exist
mkdir($folder, 0777, true);
}
$filename = "export_".$this->logged_id."_".date('Ymd_His').".xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'.$filename.'"');
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Pragma: no-cache');
header('Expires: 0');
$writer->writeToStdOut();
exit;
// $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));
}
}
?>