File: //home/cafsindia/groups_cafsindia_com/application/controllers/Report.php
<?php 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;
/*echo "<pre>";
print_r($this->table_info); die;*/
$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();
$this->load->view("$this->control_name/manage",$data);
}
//LOAD TABEL WITH FILTERS
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');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_col = $order_col[$column]['data'];
$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');
$order_col = $this->input->post('columns');
$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);
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 = (int)$field_type_list[$i];
if(($db_cond !== "") && ($db_value !== "")){
$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 = '("'.rtrim($db_value,',').'")';
$db_cond = 'IN';
}else
if($field_type === 13){
$search_val = '"'.date('Y-m-d H:i:s',strtotime($db_value)).'"';
}else{
$search_val = '"'.$db_value.'"';
}
if((int)$table_name === 1){
$fliter_query .= ' and '. $this->prime_table .".". $db_name ." ". $db_cond .' '.$search_val.'';
}
}
}
$common_search = "";
if($search){
foreach($this->form_info 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;
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
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$result = array_filter($this->master_pick[$label_id], function ($item) use ($search) {
if (stripos($item, $search) !== false) {
return true;
}
return false;
});
if($result){
$pick_key = implode(",",array_keys($result));
$common_search .= ' or '. $search_label .' in('.$pick_key.')';
}
}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 = "";
if($this->prime_table === "cw_employees"){
$sort_code = "cw_employees.employee_code";
$expect_id = " cw_employees.prime_employees_id !=1 and ";
}else
if($this->prime_table === "cw_monthly_input"){
$sort_code = "cw_employees.employee_code";
//$expect_id = " cw_monthly_input.role !=1 and ";
}else{
//$sort_code = $this->prime_table.".".$prime_id;
$sort_code = $this->prime_table."."."prime_".$this->form_info[0]->prime_module_id."_id";
}
if($order_col === 'prime_report_id'){
$order_col = $sort_code;
}
if(!$order_sor){$order = "asc";}
$basic_query = "";
if($this->table_search_info){
$basic_query = $this->table_search_info;
}
//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 = "";
}
$select_info = str_replace("@SELECT",$this->select_query,$this->base_query);
$search_query = $select_info. $this->pick_query;
$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;
$count_query = str_replace("@SELECT","count(*) as allcount",$this->base_query);
$count_query .= " where $expect_id $this->prime_table.trans_status = 1 $transaction_sts $role_condition $fliter_query $common_search $date_search";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " where $expect_id $this->prime_table.trans_status = 1 $transaction_sts $role_condition $fliter_query $common_search $date_search";
//$search_query .= " ORDER BY $order_col $order_sor";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
//echo $search_query; die;
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_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)){
$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 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');
$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'));
//echo "BSK $start_date :: $end_date"; die;
$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);
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 = (int)$field_type_list[$i];
if(($db_cond !== "") && ($db_value !== "")){
$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 = '("'.rtrim($db_value,',').'")';
$db_cond = 'IN';
}else
if($field_type === 13){
$search_val = '"'.date('Y-m-d H:i:s',strtotime($db_value)).'"';
}else{
$search_val = '"'.$db_value.'"';
}
if((int)$table_name === 1){
$fliter_query .= ' and '. $this->prime_table .".". $db_name ." ". $db_cond .' '.$search_val.'';
}
}
}
$common_search = "";
if($search){
foreach($this->form_info 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;
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
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$result = array_filter($this->master_pick[$label_id], function ($item) use ($search) {
if (stripos($item, $search) !== false) {
return true;
}
return false;
});
if($result){
$pick_key = implode(",",array_keys($result));
$common_search .= ' or '. $search_label .' in('.$pick_key.')';
}
}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 = "";
if($this->prime_table === "cw_employees"){
$sort_code = "cw_employees.employee_code";
$expect_id = " cw_employees.prime_employees_id !=1 and ";
}else
if($this->prime_table === "cw_monthly_input"){
$sort_code = "cw_employees.employee_code";
//$expect_id = " cw_monthly_input.role !=1 and ";
}else{
//$sort_code = $this->prime_table.".".$prime_id;
$sort_code = $this->prime_table."."."prime_".$this->form_info[0]->prime_module_id."_id";
}
if($order_col === 'prime_report_id'){
$order_col = $sort_code;
}
if(!$order_sor){$order = "asc";}
$basic_query = "";
if($this->table_search_info){
$basic_query = $this->table_search_info;
}
//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->report_select_query,'cw_transactions')){
$transaction_sts = "and cw_transactions.trans_status = 1 ";
}else{
$transaction_sts = "";
}
$select_info = str_replace("@SELECT",$this->report_select_query,$this->base_query);
$search_query = $select_info. $this->pick_query;
$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;
$count_query = str_replace("@SELECT","count(*) as allcount",$this->base_query);
$count_query .= " where $expect_id $this->prime_table.trans_status = 1 $transaction_sts $role_condition $fliter_query $common_search $date_search";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " where $expect_id $this->prime_table.trans_status = 1 $transaction_sts $role_condition $fliter_query $common_search $date_search";
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
echo json_encode(array('success' => true, 'search_result'=>$search_result));
}
public function leaveEntryData(&$str) {
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
}
}
?>