File: /home/cafsindia/login_cafsindia_com/application_bk10feb2023/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');
$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(){
$data['table_headers'] = $this->xss_clean(get_report_headers($this->table_info));
$report_data = $this->report_data;
$data['report_data'] = $report_data[0];
$data['fliter_list'] = $this->fliter_list;
$this->load->view("$this->control_name/manage",$data);
}
public function search(){
$search = $this->input->get('search');
$limit = $this->input->get('limit');
$offset = $this->input->get('offset');
$sort = $this->input->get('sort');
$order = $this->input->get('order');
$report_data = $this->report_data;
//SEARCH FILTERS QUERY
$fliter_query = "";
if((int)$report_data[0]->search_filter === 1){
foreach($this->fliter_list as $fliter){
$label_id = $fliter->label_id;
$label_name = $fliter->label_name;
$field_isdefault = (int)$fliter->field_isdefault;
$field_type = (int)$fliter->field_type;
$filter_label_id = $fliter->filter_label_id;
$condition_id = $label_id."_cond";
$label_id = "search_".$label_id."_cond";
$column_name = $filter_label_id;
if($field_type === 4){
$search_val = $_GET[$label_id];
$search_val = date('Y-m-d',strtotime($search_val));
$condition = trim(implode(",",$_GET[$condition_id]));
}else
if(((int)$field_type === 5) || ((int)$field_type === 7)){
$search_val = trim(implode(",",$_GET[$label_id]));
$condition = "in";
}else{
$search_val = trim(implode(",",$_GET[$label_id]));
$condition = trim(implode(",",$_GET[$condition_id]));
}
if(($condition) && ($search_val)){
if($condition === "LIKE"){ $search_val = "$search_val%"; }
if(((int)$field_type === 5) || ((int)$field_type === 7)){
$search_val = str_replace(",",'","',$search_val);
$fliter_query .= ' and '.$column_name." ". $condition .' ("'.$search_val.'")';
}else{
$fliter_query .= ' and '.$column_name." ". $condition .' "'.$search_val.'"';
}
}
}
}
//DATE FILTERS QUERY
$date_search = "";
if((int)$report_data[0]->date_filter === 1){
$date_filter_column = $report_data[0]->date_filter_column;
$filter_start_date = $this->input->get('filter_start_date');
$filter_end_date = $this->input->get('filter_end_date');
$date_filter_column = explode(",",$date_filter_column);
foreach($date_filter_column as $filter_column){
$date_search .= ' and (DATE_FORMAT('.$filter_column.', "%Y-%m-%d") BETWEEN "'.$filter_start_date.'" and "'.$filter_end_date.'")';
}
}
//COMMON SEARCH QUERY
$common_search = "";
if($search){
$count=0;
foreach($this->form_info as $setting){
$prime_form_id = $setting->prime_form_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 = strtolower(str_replace(" ","_",$setting->label_id));
$field_isdefault = (int)$setting->field_isdefault;
$prime_module_id = $setting->prime_module_id;
$table_name = "cw_".$prime_module_id;
//$cf_table_name = "cw_".$prime_module_id."_cf";
if($field_isdefault === 1){
$pick_sel_table = $table_name;
}else
if($field_isdefault === 2){
$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)";
}
}
//SELECT,PICK LIST,WHERE QUERY JOIN WITH BASE QUERY
if($report_data[0]->report_name === "Insurance Details"){
$this->select_query .= ",cw_portfolio.trans_created_date,cw_portfolio.trans_updated_date";
}
if($report_data[0]->report_name === "Health report"){
$this->select_query .= ",cw_health.trans_created_date,cw_health.trans_updated_date";
}
if($report_data[0]->report_name === "Health Renewals Report"){
$this->select_query .= ",cw_health_renewals.trans_created_date,cw_health_renewals.trans_updated_date";
}
if($report_data[0]->report_name === "Life Renewals Report"){
$this->select_query .= ",cw_life_renewals.trans_created_date,cw_life_renewals.trans_updated_date";
}
$select_info = str_replace("@SELECT",$this->select_query,$this->base_query);
$search_query = $select_info. $this->pick_query;
//echo $search_query; die;
//echo $this->where_query; die;
$basic_query = "";
if($this->where_query){
if($report_data[0]->report_name === "Insurance Details"){
if((int)$this->logged_role !== 1 && (int)$this->logged_role !== 2 && (int)$this->logged_role !== 4){
$basic_query = $this->where_query;
}else{
$basic_query = ' and cw_portfolio.trans_status = 1';
}
}else{
$basic_query = $this->where_query;
}
}
$search_query .= " where $this->prime_table.trans_status = 1 $basic_query $fliter_query $common_search $date_search";
$group_by = "";
if($report_data[0]->report_group_by){
$group_by = "group by ".$report_data[0]->report_group_by;
}
$search_query .= " $group_by";
$sort_by = "";
if($report_data[0]->report_prime_column){
$sort_by = "order by ".$report_data[0]->report_prime_column;
}
$search_query .= " $sort_by";
//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();
foreach ($search_result as $search){
$data_rows[]=get_report_row($search,$this->table_info,$this->report_data,$this);
}
echo json_encode(array('total'=>$num_rows,'rows'=>$data_rows));
}
public function report_view($view_id){
$input_vals = array();
$report_data = $this->report_data;
$report_prime_column = $report_data[0]->report_prime_column;
$report_prime_column = explode(",",$report_prime_column);
foreach($report_prime_column as $input_columns_info){
$input_columns = explode(".",$input_columns_info);
$input_columns = $input_columns[1];
$input_value = $this->input->post_get("$input_columns");
$input_vals[$input_columns_info] = $input_value;
}
$data["control_name"] = $this->control_name;
$data["view_id"] = $view_id;
$data["input_vals"] = $input_vals;
$report_view = $this->get_report_view($input_vals);
$data['final_content'] = $report_view['final_content'];
$data['pdf_file_name'] = $report_view['pdf_file_name'];
$data['file_path'] = $report_view['file_path'];
$this->load->view("report/report_view",$data);
}
public function render_pdf(){
$pdf_file_name = $this->input->post('pdf_file_name');
$page_content = $this->input->post('page_content');
$page_content = "<!DOCTYPE html>
<html>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'/>
<meta name='viewport' content='width=device-width, initial-scale=1.0'/>
</head>
<body style='font-family: sans-serif;font-size:12px;background-color: #FFFFFF;'>
<style>
@page {margin:10px;margin-top:20px; }
table{ max-width: 2480px; width:100%; }
.pdf_table { width: 100%; max-width: 100%; text-align: center; margin-bottom:20px; }
.pdf_table>thead>tr>th, .pdf_table>tbody>tr>th{ border: 1px dashed gray; padding:4px; font-weight: bold; text-align: center;background-color: #FFFFFF; color: #000000; }
.pdf_table>thead>tr>td, .pdf_table>tbody>tr>td{ border-bottom: 1px dashed gray; padding:4px; }
.pdf_table>tbody>tr>td:first-child{ text-align: left;border-left: 1px dashed gray;}
.pdf_table>tbody>tr>td:last-child{border-right: 1px dashed gray; }
tr.tot_tr{color:#CC3366;font-weight: bold;}
#footer{position:fixed;left:0px;bottom:-15px;right:0px;height:50px;background-color: lightblue;}
#footer .page:after{content:counter(page,upper-roman); }
</style>
$page_content
</body>
</html>";
if(!file_exists("./download_pdf")) {
mkdir("./download_pdf", 0777, true);
}
$report_name = str_replace(" ","_",strtolower($this->report_data[0]->report_name));
if(!file_exists("./download_pdf/$report_name")) {
mkdir("./download_pdf/$report_name", 0777, true);
}
$pdf_file_name = "./download_pdf/$report_name/$pdf_file_name";
unlink("$pdf_file_name");
$this->load->library('Pdf');
$this->dompdf->setPaper("letter",'portrait');
//$this->dompdf->setPaper("letter",'landscape');
$this->dompdf->loadHtml($page_content);
$this->dompdf->render();
$output = $this->dompdf->output();
file_put_contents($pdf_file_name, $output);
echo json_encode(array('success'=>true,'message'=>"PDF Downloaded !!!"));
}
}
?>