File: /home/cafsindia/hrms_cafsinfotech_in/OLD/application_8.3/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');
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['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;
// echo ($data['fin_start_date'] );
// echo ($data['fin_end_date']);
// die;
$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;
$this->load->view("$this->control_name/manage",$data);
}
public function search(){
$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){
$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{
// $sort_code = $this->prime_table."."."prime_".$this->form_info[0]->prime_module_id."_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"){
$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;
}
// $count_query = str_replace("@SELECT","count(*) as allcount",$this->base_query);
// $count_query .= " where $expect_id $this->prime_table.trans_status = 1 $transaction_sts $basic_query $fliter_query $common_search $date_search";
// $search_count = $this->db->query($count_query);
// $search_info = $search_count->result();
// $filtered_count = $search_info[0]->allcount;
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{
// $current_tbl = $this->prime_table."."."prime_".$this->form_info[0]->prime_module_id."_id";
$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_query .= " LIMIT $offset,$limit";
//echo $search_query; die;
$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_query .= " LIMIT $start,$per_page";
$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_query .= " LIMIT $start,$per_page";
$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(){
$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(){
$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(){
$excel_name = $this->input->post('excel_name');
$form_id = $this->input->post('form_id');
$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');
$search = $this->input->post('search');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$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{
$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(($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{
$column_name = $pick_table."_value";
}
$pick_query_as = $pick_table."_".$prime_form_id;
$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;
$label_id = "$pick_query_as.$auto_dispaly_value";
$common_search .= ' or '. $label_id .' like "'.$search.'%"';
}else{
if(!strtotime($search)){
$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{
// $sort_code = $this->prime_table."."."prime_".$this->form_info[0]->prime_module_id."_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"){
$date_column_search = '(DATE_FORMAT(str_to_date(CONCAT("01-",'.$date_column_search.'), "%d-%m-%Y") , "%Y-%m-%01")';
$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-%01")';
$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{
// $current_tbl = $this->prime_table."."."prime_".$this->form_info[0]->prime_module_id."_id";
$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 $sort_code ASC";
$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();
$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("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 $sort_code ASC";
//$group_query .= " LIMIT $start,$per_page";
$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 ORDER BY $sort_code ASC";
//$final_sum_query .= " LIMIT $start,$per_page";
$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);
$emp_column_name = explode('.', $emp_column_name);
$emp_column_name = $emp_column_name[1];
}
}
if($filtered_count){
$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);
}
$report_qry = "select table_info from cw_report_setting where prime_report_setting_id = \"$form_id\"";
$report_info = $this->db->query("CALL sp_a_run ('SELECT','$report_qry')");
$report_rslt = $report_info->result();
$report_info->next_result();
$table_info_data = $report_rslt[0]->table_info;
$table_info_data = str_replace('cw_', '', $table_info_data);
$table_info_data = str_replace('custom_employees_log', 'employees', $table_info_data);
$table_info_data = str_replace('custom_employees', 'employees', $table_info_data);
$table_info_data = str_replace('transactions', 'employees', $table_info_data);
$table_info_data = str_replace('monthly_input', 'employees', $table_info_data);
$table_info_array= explode(',', $table_info_data);
$table_info_array= array_unique($table_info_array);
$table_info_value= '"'.implode('","', $table_info_array).'"';
$form_qry = "select label_name,view_name,field_type from cw_form_setting inner join cw_report_table_view on cw_report_table_view.table_column = cw_form_setting.label_name and report_id = ".$form_id." and cw_form_setting.trans_status = 1 where prime_module_id in ($table_info_value) order by cw_report_table_view.table_sort ASC";
$form_info = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_rslt = $form_info->result_array();
$form_info->next_result();
$form_rslt_data = array();
foreach ($form_rslt as $key => $value) {
$form_rslt_data[$value['label_name']] = $value;
}
echo json_encode(array('success' => true, 'search_result'=>$data_rows, 'emp_export_arr' => $data_rows,'form_rslt_data' => $form_rslt_data));
}
}
?>