File: /home/cafsindia/wealth_cafsindia_com/application/controllers/Report_setting.php
<?php
/**********************************************************
Filename: Form Setting / Screen Setting
Description: Form Setting / Screen Setting for creating new view,input and manage conditions.
Author: udhayakumar Anandhan
Created on: 26 November 2018
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("Secure_Controller.php");
class Report_setting extends Secure_Controller{
public function __construct(){
parent::__construct('report_setting');
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$data['table_headers']=$this->xss_clean(get_report_setting_headers());
$this->load->view('report_setting/manage',$data);
}
//REPORT SEARCH OPEARTION
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');
if(!$sort){
$sort = "report_name";
}
if(!$order){
$order = "asc";
}
$this->db->select('prime_report_setting_id,report_name');
$this->db->from('report_setting');
if($search){
$this->db->group_start();
$this->db->like('report_name',$search);
$this->db->group_end();
}
$this->db->where('report_setting.trans_status',1);
$this->db->order_by($sort,$order);
if($rows>0){
$this->db->limit($rows, $limit_from);
}
$report_info = $this->db->get();
$xx = $report_info->result();
$data_rows = array();
foreach ($report_info->result() as $report_setting){
$data_rows[]=get_report_setting_datarows($report_setting,$this);
}
$data_rows=$this->xss_clean($data_rows);
$num_rows = $report_info->num_rows();
echo json_encode(array('total'=>$num_rows,'rows'=>$data_rows));
}
//REPORT VIEW OPEARTION
public function view($view_id =-1){
/*================== COMMON SETTING ==================*/
$data['view_id'] = $view_id;
$data['filter_list'] = array(""=>"-- Select Type--",1=>"Required",2=>"Not Required");
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_role` where trans_status = 1')");
$role_result = $role_info->result();
$role_info->next_result();
$report_for_list[""] = "---- Report For ----";
foreach($role_result as $for){
$role_id = $for->prime_role_id;
$role_name = $for->role_name;
$report_for_list[$role_id] = $role_name;
}
$data['report_for_list'] = $report_for_list;
$table_info = $this->db->query("CALL sp_a_run ('SELECT','SHOW TABLES')");
$table_result = $table_info->result();
$table_info->next_result();
$table_list[""] = "---- Select Table ----";
foreach($table_result as $table){
$table_name = $table->Tables_in_cafs_wealth;
if((strpos($table_name, "cw_zct_") === false)&&(strpos($table_name, "_cf") === false)){
if(!in_array($table_name, $tab_array)){
$str = substr($str, 1);
$table_value = substr((ucwords(str_replace("_"," ",$table_name))),3);
$table_list[$table_name] = $table_value;
}
}
}
$data['table_list'] = $table_list;
/*================== COMMON SETTING ==================*/
/*================== REPORT SETTING ==================*/
$report_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM cw_report_setting where trans_status = 1 and prime_report_setting_id = $view_id')");
$report_result = $report_data->result();
$report_data->next_result();
$data['report_data'] = $report_result[0];
$table_info = $this->get_columns($report_result[0]->report_tables);
$table_info = json_decode($table_info,true);
$data['table_column'] = $table_info['table_column'];
$data['date_column'] = $table_info['date_column'];
$data['custom_select_list'] = $this->custom_select_list($view_id);
$data['table_view_list'] = $this->table_view_list($view_id);
$data['total_subtotal_list'] = $this->get_total_subtotal_column($view_id);
$data['join_list'] = $this->get_table_join($view_id);
$where_query = 'SELECT * FROM cw_report_where WHERE where_for_id = "'.$view_id.'" and trans_status = 1';
$where_info = $this->db->query("CALL sp_a_run ('SELECT','$where_query')");
$where_count = (int)$where_info->num_rows();
$where_result = $where_info->result();
$where_info->next_result();
$where_condition = trim($where_result[0]->where_condition);
if(!$where_condition){
$where_condition = "and";
}
$data['where_condition'] = $where_condition;
/*================== REPORT SETTING ==================*/
/*================== VIEW SETTING ==================*/
$data['view_type_list'] = array(""=>"--- Select Type ---",1=>"Table With Header",2=>"Table No Header");
$data['view_size_list'] = array(""=>"--- Select size ---",1=>"col-md-3",2=>"col-md-4",3=>"col-md-6",4=>"col-md-12");
$data['view_block_list'] = $this->view_block_list($view_id);
$view_block_qry = 'select * from cw_report_view where trans_status = 1 and report_setting_id = "'.$view_id.'"';
$view_block_info = $this->db->query("CALL sp_a_run ('SELECT','$view_block_qry')");
$view_block_result = $view_block_info->result();
$view_block_info->next_result();
$view_block_drop[""] = "---- Select Block ----";
foreach($view_block_result as $block){
$prime_view_id = $block->prime_view_id;
$view_name = ucwords($block->view_name);
$view_block_drop[$prime_view_id] = $view_name;
}
$data['view_block_drop'] = $view_block_drop;
$data['view_select_list'] = $this->view_select_list($view_id);
$data['view_where_list'] = $this->view_table_where_list($view_id);
$data['view_block_sort_list'] = $this->get_view_block_sort_list($view_id);
$view_table_sort_info = $this->view_table_sort($view_id);
$data['table_sort_content'] = $view_table_sort_info['table_sort_content'];
$data['table_sort_script'] = $view_table_sort_info['table_sort_script'];
/*================== VIEW SETTING ==================*/
$this->load->view("report_setting/form",$data);
}
/* ==============================================================*/
/* ================== REPORT SETTING - START ==================*/
/* ==============================================================*/
/* ================== REPORT SETTING BASE INFO - END ==================*/
// SAVE REPORT BASE INFO
public function report_save(){
$prime_report_setting_id = (int)$this->input->post('prime_report_setting_id');
$report_name = $this->input->post('report_name');
$report_for = ltrim(implode(",",$this->input->post('report_for[]')),",");
$report_tables = ltrim(implode(",",$this->input->post('report_tables[]')),",");
$report_columns = ltrim(implode(",",$this->input->post('report_columns[]')),",");
$report_prime_column = ltrim(implode(",",$this->input->post('report_prime_column[]')),",");
$search_filter = $this->input->post('search_filter');
$date_filter = $this->input->post('date_filter');
$date_filter_column = ltrim(implode(",",$this->input->post('date_filter_column[]')),",");
$report_group_by = ltrim(implode(",",$this->input->post('report_group_by[]')),",");
$subtotal_filter = $this->input->post('subtotal_filter');
$subtotal_column = ltrim(implode(",",$this->input->post('subtotal_column[]')),",");
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
if($prime_report_setting_id === 0){
$is_exist_qry = 'SELECT * FROM cw_report_setting where report_name = "'.$report_name.'" and trans_status = 1 ';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_count = $is_exist_data->num_rows();
$exist_rslt = $is_exist_data->result();
$is_exist_data->next_result();
if((int)$exist_count === 0){
$report_qry = 'INSERT INTO cw_report_setting (report_name, report_for,report_tables,report_columns,report_prime_column,report_group_by,subtotal_filter,search_filter,date_filter,date_filter_column,subtotal_column,trans_created_by, trans_created_date) VALUES ("'.$report_name.'","'.$report_for.'","'.$report_tables.'","'.$report_columns.'","'.$report_prime_column.'","'.$report_group_by.'","'.$subtotal_filter.'","'.$search_filter.'","'.$date_filter.'","'.$date_filter_column.'","'.$subtotal_column.'","'.$logged_id.'","'.$date.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$report_qry')");
$insert_result = $insert_info->result();
$insert_id = $insert_result[0]->ins_id;
$insert_info->next_result();
$this->save_table_view($insert_id);
echo json_encode(array('success' => true, 'message' => "Report successfully added"));
}else{
echo json_encode(array('success' => false, 'message' => "Report Name Already Exist"));
}
}else{
$upd_qry = 'UPDATE cw_report_setting SET report_name = "'.$report_name.'",report_for = "'.$report_for.'",report_tables = "'.$report_tables.'",report_columns = "'.$report_columns.'",report_prime_column = "'.$report_prime_column.'",report_group_by = "'.$report_group_by.'",subtotal_filter = "'.$subtotal_filter.'",search_filter = "'.$search_filter.'",date_filter = "'.$date_filter.'",date_filter_column = "'.$date_filter_column.'",subtotal_column = "'.$subtotal_column.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_report_setting_id = "'.$prime_report_setting_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$this->save_table_view($prime_report_setting_id);
echo json_encode(array('success' => true, 'message' => "Report successfully updated"));
}
}
//ONCHANGE GET COLUMNS BASED ON SELECTED TBALE
public function get_table_column(){
$report_tables = ltrim(implode(",",$this->input->post('report_tables')),",");
$table_info = $this->get_columns($report_tables);
$table_info = json_decode($table_info);
$table_column = $table_info->table_column;
$date_column = $table_info->date_column;
echo json_encode(array('success'=>true,'table_column'=>$table_column,'date_column'=>$date_column));
}
//GET COLUMNS BASED ON SELECTED TBALE
public function get_columns($report_tables){
if($report_tables !== ""){
$prime_in = '"'.str_replace(",",'","', $report_tables);
$custom_in = str_replace(",",'_cf","', $report_tables).'_cf"';
$table_in = $prime_in.'","'.$custom_in;
$get_colums = 'SELECT TABLE_NAME as table_name,COLUMN_NAME as col_name,DATA_TYPE as data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA="cafs_wealth" AND TABLE_NAME IN ('.$table_in.') AND COLUMN_NAME NOT LIKE "%trans%"';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$column_result = $column_info->result();
$column_info->next_result();
$table_column[""] = "---- Select Column ----";
$date_column[""] = "---- Select Column ----";
foreach($column_result as $column){
$table_name = $column->table_name;
$col_name = $column->col_name;
$data_type = $column->data_type;
$table_name_view = substr((ucwords(str_replace("_"," ",$table_name))),3);
$col_name_view = ucwords(str_replace("_"," ",$col_name));
if(strtoupper($data_type) === "DATE"){
$date_column[$table_name.".".$col_name] = $table_name_view." - ".$col_name_view;
}
$table_column[$table_name.".".$col_name] = $table_name_view." - ".$col_name_view;
}
return json_encode(array('success'=>true,'table_column'=>$table_column,'date_column'=>$date_column));
}else{
$table_column[""] = "---- Select Column ----";
$date_column[""] = "---- Select Column ----";
return json_encode(array('success'=>true,'table_column'=>$table_column,'date_column'=>$date_column));
}
}
//ONCHANGE GET TOTAL AND SUB TOTAL COLUMNS BASED ON SELECT INFO
public function update_total_subtotal_column(){
$prime_report_setting_id = (int)$this->input->post('prime_report_setting_id');
$report_columns = ltrim(implode(",",$this->input->post('report_columns')),",");
$total_column = $this->get_total_subtotal_column($prime_report_setting_id,$report_columns);
echo json_encode(array('success' => true, 'total_column' =>$total_column));
}
// GET TOTAL AND SUB TOTAL COLUMNS BASED ON SELECT INFO
public function get_total_subtotal_column($prime_report_setting_id,$columns_list=null){
$total_column[""] = "---- Select Column ----";
if((int)$prime_report_setting_id === 0){
$report_columns = explode(",",$columns_list);
foreach($report_columns as $report){
$total_column[$report] = $report;
}
}else{
$select_query = 'SELECT * FROM cw_report_setting where prime_report_setting_id = "'.$prime_report_setting_id.'" and trans_status = 1 ';
$select_data = $this->db->query("CALL sp_a_run ('SELECT','$select_query')");
$select_rslt = $select_data->result();
$select_data->next_result();
if($select_rslt){
$report_columns = explode(",",$select_rslt[0]->report_columns);
foreach($report_columns as $columns){
$total_column[$columns] = $columns;
}
}
$custom_select_query = 'SELECT * FROM cw_report_custom_select where custom_select_report_id = "'.$prime_report_setting_id.'" and trans_status = 1 ';
$custom_select_data = $this->db->query("CALL sp_a_run ('SELECT','$custom_select_query')");
$custom_select_rslt = $custom_select_data->result();
$custom_select_data->next_result();
foreach($custom_select_rslt as $select_rslt){
$custom_column_name = $select_rslt->custom_column_name;
$total_column[$custom_column_name] = $custom_column_name;
}
$report_columns = explode(",",$columns_list);
foreach($report_columns as $report){
if(!$total_column[$report]){
$total_column[$report] = $report;
}
}
}
return $total_column;
}
/* ================== REPORT SETTING BASE INFO - END ==================*/
/* ================== CUSTOM SELECT - START ==================*/
// SAVE CUSTOM SELECT
public function save_custom_select(){
$prime_custom_select_id = (int)$this->input->post('prime_custom_select_id');
$custom_select_report_id = (int)$this->input->post('custom_select_report_id');
$custom_column_name = str_replace(" ","_",strtolower($this->input->post('custom_column_name')));
$select_column_query = str_replace('"',"^",$this->input->post('select_column_query'));
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
if($prime_custom_select_id === 0){
$is_exist_qry = 'SELECT * FROM cw_report_custom_select where custom_select_report_id = "'.$custom_select_report_id.'" and custom_column_name = "'.$custom_column_name.'" and trans_status = 1 ';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_count = $is_exist_data->num_rows();
$exist_rslt = $is_exist_data->result();
$is_exist_data->next_result();
if((int)$exist_count === 0){
$report_qry = 'INSERT INTO cw_report_custom_select (custom_select_report_id, custom_column_name,select_column_query,trans_created_by, trans_created_date) VALUES ("'.$custom_select_report_id.'","'.$custom_column_name.'","'.$select_column_query.'","'.$logged_id.'","'.$date.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$report_qry')");
$insert_result = $insert_info->result();
$insert_id = $insert_result[0]->ins_id;
$insert_info->next_result();
$custom_select_list = $this->custom_select_list($custom_select_report_id);
$this->save_table_view($custom_select_report_id);
$table_view_list = $this->table_view_list($custom_select_report_id);
$subtotal_column = $this->get_total_subtotal_column($custom_select_report_id);
echo json_encode(array('success' => true, 'message' => "Custom Column Name successfully added",'custom_select_list'=>$custom_select_list,'table_view_list'=>$table_view_list,'subtotal_column'=>$subtotal_column));
}else{
echo json_encode(array('success' => false, 'message' => "Custom Column Name Already Exist"));
}
}else{
$upd_qry = 'UPDATE cw_report_custom_select SET custom_select_report_id = "'.$custom_select_report_id.'",custom_column_name = "'.$custom_column_name.'",select_column_query = "'.$select_column_query.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_custom_select_id = "'.$prime_custom_select_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$custom_select_list = $this->custom_select_list($custom_select_report_id);
$this->save_table_view($custom_select_report_id);
$table_view_list = $this->table_view_list($custom_select_report_id);
$subtotal_column = $this->get_total_subtotal_column($custom_select_report_id);
echo json_encode(array('success' => true, 'message' => "Custom Column Name successfully updated",'custom_select_list'=>$custom_select_list,'table_view_list'=>$table_view_list,'subtotal_column'=>$subtotal_column));
}
}
// GET CUSTOM SELECT LIST
public function custom_select_list($custom_select_report_id){
$select_qry = 'select * from cw_report_custom_select where trans_status = 1 and custom_select_report_id = "'.$custom_select_report_id.'"';
$select_info = $this->db->query("CALL sp_a_run ('SELECT','$select_qry')");
$select_result = $select_info->result();
$select_info->next_result();
foreach($select_result as $rslt){
$prime_custom_select_id = $rslt->prime_custom_select_id;
$custom_select_report_id = $rslt->custom_select_report_id;
$custom_column_name = $rslt->custom_column_name;
$select_column_query = $rslt->select_column_query;
$tr_line .= "<tr>
<td>$custom_column_name</td>
<td>$select_column_query</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=custom_select_edit('$prime_custom_select_id')> <i class='fa fa-pencil-square-o' aria-hidden='true'></i> Edit</a></td>
<td style='text-align:center;'><a class='btn btn-xs btn-danger' onclick=custom_select_remove('$prime_custom_select_id','$custom_select_report_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$custom_select_list = "<table class='table table-bordered table-stripted' id='custom_select_table'>
<thead>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th style='text-align: center;'>Custom Column Name</th>
<th style='text-align: center;'>Select Query</th>
<th style='text-align:center;'>Edit</th>
<th style='text-align:center;'>Delete</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $custom_select_list;
}
//CUSTOM SELECT EDIT INFO
public function custom_select_edit(){
$prime_custom_select_id = $this->input->post('prime_custom_select_id');
$edit_formula = 'SELECT * FROM cw_report_custom_select WHERE prime_custom_select_id = "'.$prime_custom_select_id.'" and trans_status = 1';
$edit_info = $this->db->query("CALL sp_a_run ('SELECT','$edit_formula')");
$edit_result = $edit_info->result();
$edit_info->next_result();
echo json_encode(array('success' => true,'edit_result'=>$edit_result[0]));
}
//CUSTOM SELECT LIST REMOVE
public function custom_select_remove(){
$prime_custom_select_id = $this->input->post('prime_custom_select_id');
$custom_select_report_id = $this->input->post('custom_select_report_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_report_custom_select SET trans_status = 0 ,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_custom_select_id = "'.$prime_custom_select_id.'"';
$this->db->query("CALL sp_a_run ('SELECT','$remove_qry')");
$custom_select_list = $this->custom_select_list($custom_select_report_id);
$this->save_table_view($custom_select_report_id);
$table_view_list = $this->table_view_list($custom_select_report_id);
$subtotal_column = $this->get_total_subtotal_column($custom_select_report_id);
echo json_encode(array('success' => true,'message'=>'Table Query Remove Successfully !!!','custom_select_list'=>$custom_select_list,'table_view_list'=>$table_view_list,'subtotal_column'=>$subtotal_column));
}
/* ================== CUSTOM SELECT - END ==================*/
/* ================== TABLE VIEW - START ==================*/
//SAVE TABLE VIEW
public function save_table_view($report_id){
$select_query = 'SELECT * FROM cw_report_setting where prime_report_setting_id = "'.$report_id.'" and trans_status = 1 ';
$select_data = $this->db->query("CALL sp_a_run ('SELECT','$select_query')");
$select_rslt = $select_data->result();
$select_data->next_result();
$table_view_list = array();
if($select_rslt){
$report_columns = explode(",",$select_rslt[0]->report_columns);
foreach($report_columns as $columns){
$table_view_list[] = array('columns'=>$columns,'mode'=>1);
}
}
$custom_select_query = 'SELECT * FROM cw_report_custom_select where custom_select_report_id = "'.$report_id.'" and trans_status = 1 ';
$custom_select_data = $this->db->query("CALL sp_a_run ('SELECT','$custom_select_query')");
$custom_select_rslt = $custom_select_data->result();
$custom_select_data->next_result();
foreach($custom_select_rslt as $select_rslt){
$custom_column_name = $select_rslt->custom_column_name;
$table_view_list[] = array('columns'=>$custom_column_name,'mode'=>2);
}
$logged_id = $this->session->userdata('logged_id');
$remove_all = 'UPDATE cw_report_table_view SET trans_status = 0 where custom_select_report_id = "'.$report_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_all')");
$table_sort = 0;
foreach($table_view_list as $view_list){
$select_column = $view_list['columns'];
$table_mode = $view_list['mode'];
$table_sort++;
$exits_count_qry = 'select * from cw_report_table_view where custom_select_report_id = "'.$report_id.'" and select_column = "'.$select_column.'"';
$exits_count_info = $this->db->query("CALL sp_a_run ('SELECT','$exits_count_qry')");
$exits_count_result = $exits_count_info->result();
$rslt_count = (int)$exits_count_info->num_rows();
$exits_count_info->next_result();
$prime_table_view_id = (int)$exits_count_result[0]->prime_table_view_id;
$date = date("Y-m-d h:i:s");
if($rslt_count === 0){
$table_view_qry = 'INSERT INTO cw_report_table_view (custom_select_report_id,select_column, table_sort,table_mode,trans_created_by, trans_created_date) VALUES ("'.$report_id.'","'.$select_column.'","'.$table_sort.'","'.$table_mode.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$table_view_qry')");
}else
if($rslt_count === 1){
$table_view_upd_qry = 'UPDATE cw_report_table_view SET trans_status = 1,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_table_view_id = "'.$prime_table_view_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$table_view_upd_qry')");
}
}
$report_delete_qry = 'delete FROM cw_report_table_view WHERE trans_status = 0';
$this->db->query("CALL sp_a_run ('RUN','$report_delete_qry')");
}
// SAVE TBALE VIEW LIST
public function table_view_list($report_id){
$get_colums_info = 'select * from cw_report_table_view WHERE custom_select_report_id = "'.$report_id.'" and trans_status = 1 order by abs(table_sort)';
$colums_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums_info')");
$colums_result = $colums_info->result();
$colums_info->next_result();
$input_th = "<p style='color:#1883E9;text-align:center;'><i class='fa fa-hand-rock-o fa-2x' aria-hidden='true'></i> Drag and drop for align field postion</p>";
$input_td = "";
foreach($colums_result as $report){
$table_sort = $report->table_sort;
$prime_table_view_id = $report->prime_table_view_id;
$select_column = $report->select_column;
$table_mode = (int)$report->table_mode;
$th_id = "th_".$prime_table_view_id;
if($table_mode === 1){
$select_column = explode('.',$select_column);
$colum_name = $select_column[1];
}else{
$colum_name = $select_column;
}
$colum_name = ucwords(str_replace("_"," ",$colum_name));
$input_th .= "<th class='ui-state-default' id='$th_id' style='background-color: #1883E9; color:#FFFFFF;cursor:pointer;border-right: 1px solid #CCCCCC;'>$colum_name</th>";
$input_td .= "<td style='border-right:1px solid #CCCCCC;'>-</td>";
}
$table_content = "<table class='table table-hover table-striped'>
<tr id='report_sortable' class='sortable'> $input_th </tr>
<tr> $input_td </tr>
</table>";
return $table_content;
}
//SAVE TABLE SORT
public function table_sort_update(){
$table_idsInOrder = $this->input->post('table_idsInOrder');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d H:i:s");
$sort_order = 0;
foreach($table_idsInOrder as $order){
if($order){
$sort_order++;
$table_id = str_replace("th_","",$order);
$upd_qry = 'UPDATE cw_report_table_view SET table_sort = "'.$sort_order.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_table_view_id = "'.$table_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
}
}
echo json_encode(array('success' => TRUE, 'message' => "Column Name is successfully sorted."));
}
/* ================== TABLE VIEW - END ==================*/
/* ================== TABLE JOIN - SATRT ==================*/
//GET TABLE JOIN INFO
public function get_table_join($prime_report_setting_id){
$report_qry = 'SELECT * FROM cw_report_setting where prime_report_setting_id = "'.$prime_report_setting_id.'" and trans_status = 1';
$report_data = $this->db->query("CALL sp_a_run ('SELECT','$report_qry')");
$report_rslt = $report_data->result();
$report_data->next_result();
foreach($report_rslt as $rslt){
$prime_report_setting_id = $rslt->prime_report_setting_id;
$report_tables = explode(",",$rslt->report_tables);
$table_list = array();
$table_list[""] = "---- Select Table ----";
foreach($report_tables as $table_value){
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_list[$table_value] = $table_name;
}
$prime_in = '"'.str_replace(",",'","', $rslt->report_tables);
$custom_in = str_replace(",",'_cf","', $rslt->report_tables).'_cf"';
$table_in = $prime_in.'","'.$custom_in;
$get_colums = 'SELECT `TABLE_NAME`,`COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="cafs_wealth" AND `TABLE_NAME` IN ('.$table_in.') AND COLUMN_NAME NOT LIKE "%trans%"';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$column_result = $column_info->result();
$column_info->next_result();
$column_list = array();
$column_list[""] = "---- Select Column ----";
foreach($column_result as $column){
$table_value = $column->TABLE_NAME;
$column_value = $column->COLUMN_NAME;
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$column_name = ucwords(str_replace("_"," ",$column_value));
$column_list[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
$join_array = array(""=>"--- Select join type ---","inner" => "inner","left" => "left","right" => "right");
$join_query = 'SELECT * FROM cw_report_table WHERE trans_status = 1 and join_for = "'.$prime_report_setting_id.'" order by abs(line_sort)';
$join_info = $this->db->query("CALL sp_a_run ('SELECT','$join_query')");
$join_result = $join_info->result();
$join_info->next_result();
$table_tr_line = "";
$table_count = 0;
$condition_table_count = count($report_tables) - 1; //round(count($condition_table)/2);
for($i=1;$i<= $condition_table_count;$i++){
$prime_print_table_id = 0;
$line_prime_table = "";
$line_prime_col = "";
$line_join_type = "";
$line_join_table = "";
$line_join_col = "";
if($join_result){
$prime_report_table_id = $join_result[$table_count]->prime_report_table_id;
$line_prime_table = $join_result[$table_count]->line_prime_table;
$line_prime_col = $join_result[$table_count]->line_prime_col;
$line_join_type = $join_result[$table_count]->line_join_type;
$line_join_table = $join_result[$table_count]->line_join_table;
$line_join_col = $join_result[$table_count]->line_join_col;
}
$table_cond_for_id = form_input(array( 'name' =>"prime_report_table_id[]",'class' => 'form-control input-sm','value' =>$prime_report_table_id,'type'=>'Hidden'));
$prime_table_data = form_dropdown(array('name' =>"line_prime_table[]",'class' => 'form-control input-sm'), $table_list,$line_prime_table);
$prime_col_data = form_dropdown(array('name' =>"line_prime_col[]",'class' => 'form-control input-sm'),$column_list,$line_prime_col);
$join_data = form_dropdown(array('name' =>"line_join_type[]",'class' => 'form-control input-sm'),$join_array,$line_join_type);
$join_table_data = form_dropdown(array('name' =>"line_join_table[]",'class' => 'form-control input-sm'), $table_list,$line_join_table);
$join_col_data = form_dropdown(array('name' =>"line_join_col[]",'class' => 'form-control input-sm'),$column_list,$line_join_col);
$table_tr_line .= "<tr>
<td>$table_cond_for_id $prime_table_data</td>
<td>$prime_col_data</td>
<td>$join_data</td>
<td>$join_table_data</td>
<td>$join_col_data</td>
</tr>";
$table_count++;
}
$join_for = form_input(array('name'=>'join_for','id'=>'join_for','class'=>'form-control input-sm','value'=>$prime_report_setting_id,'type'=>'Hidden'));
$table_content = "$join_for
<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th>Primary table</th>
<th>Primary column</th>
<th>Join type</th>
<th>Join table</th>
<th>Join primary column</th>
</tr>
$table_tr_line
</table>
<div style='text-align:right;padding:8px 0px;'>
<button class='btn btn-primary btn-sm' id='save_join_table_btn'>Save</button>
</div>";
}
return $table_content;
}
// SAVE TABLE JOIN
public function save_join_table(){
$join_for = $this->input->post('join_for');
$prime_report_table_id = $this->input->post('prime_report_table_id');
$prime_print_table_id = $this->input->post('prime_print_table_id[]');
$line_prime_table = $this->input->post('line_prime_table[]');
$line_prime_col = $this->input->post('line_prime_col[]');
$line_join_type = $this->input->post('line_join_type[]');
$line_join_table = $this->input->post('line_join_table[]');
$line_join_col = $this->input->post('line_join_col[]');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$tab_count = 0;
$remove_query = 'UPDATE cw_report_table SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE join_for = "'.$join_for.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_query')");
$table_count = count($line_prime_table);
for($i=1;$i<= $table_count;$i++){
$prime_report_table_id_val = $prime_report_table_id[$tab_count];
$line_prime_table_val = $line_prime_table[$tab_count];
$line_prime_col_val = $line_prime_col[$tab_count];
$line_join_type_val = $line_join_type[$tab_count];
$line_join_table_val = $line_join_table[$tab_count];
$line_join_col_val = $line_join_col[$tab_count];
if((int)$prime_report_table_id_val === 0){
$table_query = 'insert into cw_report_table (join_for,line_prime_table,line_prime_col,line_join_type,line_join_table,line_join_col,line_sort,trans_created_by,trans_created_date) value ("'.$join_for.'","'.$line_prime_table_val.'","'.$line_prime_col_val.'","'.$line_join_type_val.'","'.$line_join_table_val.'","'.$line_join_col_val.'","'.$i.'","'.$logged_id.'","'.$today_date.'")';
}else{
$table_query = 'UPDATE cw_report_table SET trans_status = 1, line_prime_table = "'.$line_prime_table_val.'",line_prime_col = "'.$line_prime_col_val.'",line_join_type = "'.$line_join_type_val.'",line_join_table = "'.$line_join_table_val.'",line_join_col = "'.$line_join_col_val.'",line_sort = "'.$i.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE prime_report_table_id = "'.$prime_report_table_id_val.'"';
}
$this->db->query("CALL sp_a_run ('RUN','$table_query')");
$tab_count++;
}
$report_delete_qry = 'delete FROM cw_report_table WHERE trans_status = 0';
$this->db->query("CALL sp_a_run ('RUN','$report_delete_qry')");
echo json_encode(array('success' => true, 'message'=>"Table Join successfully Updated"));
}
/* ================== TABLE JOIN - END ==================*/
/* ================== TABLE WHERE - START ==================*/
// PROVIDE PICKLIST AND SESSION VALUES
function get_column_info(){
$query_column = $this->input->post('query_column');
$label_name = explode(".",$query_column);
$where_module_id = str_replace("cw_","",$label_name[0]);
$get_colums_info = 'SELECT * FROM cw_form_setting WHERE prime_module_id = "'.$where_module_id.'" and label_name = "'.$label_name[1].'"';
$colums_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums_info')");
$colums_result = $colums_info->result();
$colums_info->next_result();
$session_val_qry = 'SELECT * FROM cw_session_value WHERE trans_status = 1 order by abs(session_for)';
$get_session_val = $this->db->query("CALL sp_a_run ('SELECT','$session_val_qry')");
$session_val_result = $get_session_val->result();
$get_session_val->next_result();
$session_list[""] = "--- Select Session Value ---";
if($session_val_result){
foreach($session_val_result as $col){
$col_id = (int)$col->session_for;
$session_for = "Employee";
if($col_id === 2){
$session_for = "Customer";
}
$col_value = $col->session_value;
$key_value = $col_id."|".$col_value;
$session_list[$key_value] = "$session_for - $col_value";
}
}
if($colums_result){
$field_type = (int)$colums_result[0]->field_type;
$pick_list_type = (int)$colums_result[0]->pick_list_type;
$pick_list = $colums_result[0]->pick_list;
$pick_table = $colums_result[0]->pick_table;
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_colum_qry = 'SELECT '.$pick_list.' FROM '.$pick_table.' WHERE trans_status = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$colum = explode(",",$pick_list);
foreach($pick_colum_result as $col){
$col_id = $col->$colum[0];
$col_value = $col->$colum[1];
$pick_list_info[$col_id] = "$col_id - $col_value";
}
}
}else
if($pick_list_type === 2){
$id = $pick_table."_id";
$value = $pick_table."_value";
$status = $pick_table."_status";
$select_info = "$id,$value";
$pick_colum_qry = 'SELECT '.$select_info.' FROM '.$pick_table.' WHERE '.$status.' = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
foreach($pick_colum_result as $col){
$col_id = $col->$id;
$col_value = $col->$value;
$pick_list_info[$col_id] = "$col_id - $col_value";
}
}
}
echo json_encode(array('success' => true,'type'=>'pick_list','msg'=>"Pick list value","pick_list"=>$pick_list_info,"session_list"=>$session_list));
}else{
echo json_encode(array('success' => true,'type'=>'session_list','msg'=>"Session list","session_list"=>$session_list));
}
}else{
echo json_encode(array('success' => true,'type'=>'session_list','msg'=>"Session list","session_list"=>$session_list));
}
}
// SAVE WHERE CONDITION
function save_table_where(){
$where_for_id = (int)$this->input->post('where_for_id');
$where_condition = trim(strtolower($this->input->post('where_condition')));
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$exist_query = 'SELECT * FROM cw_report_where WHERE where_for_id = "'.$where_for_id.'" and trans_status = 1';
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_count = (int)$exist_info->num_rows();
$exist_result = $exist_info->result();
$exist_info->next_result();
if($exist_count === 0){
$search_qry = 'INSERT INTO cw_report_where (where_for_id,where_condition,trans_created_by, trans_created_date) VALUES ("'.$where_for_id.'","'.$where_condition.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$search_qry')");
echo json_encode(array('success' => true,'message'=>"Where added successfully !!!"));
}else{
$prime_report_where_id = (int)$exist_result[0]->prime_report_where_id;
$upd_qry = 'UPDATE cw_report_where SET where_for_id = "'.$where_for_id.'",where_condition = "'.$where_condition.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_report_where_id = "'.$prime_report_where_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
echo json_encode(array('success' => true,'message'=>"Where updated successfully !!!"));
}
}
/* ================== TABLE WHERE - END ==================*/
/* ==============================================================*/
/* ================== REPORT SETTING - END ==================*/
/* ==============================================================*/
/* ==============================================================*/
/* ================== VIEW SETTING - START ==================*/
/* ==============================================================*/
function view_save(){
$report_setting_id = (int)$this->input->post('report_setting_id');
$prime_view_id = (int)$this->input->post('prime_view_id');
$view_for = ltrim(implode(",",$this->input->post('view_for[]')),",");
$view_name = trim($this->input->post('view_name'));
$view_type = $this->input->post('view_type');
$view_size = $this->input->post('view_size');
$view_chart = $this->input->post('view_chart');
$view_chart_size = $this->input->post('view_chart_size');
$view_tables = ltrim(implode(",",$this->input->post('view_tables[]')),",");
$view_columns = ltrim(implode(",",$this->input->post('view_columns[]')),",");
$view_total_columns = ltrim(implode(",",$this->input->post('view_total_columns[]')),",");
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
if($prime_view_id === 0){
$is_exist_qry = 'SELECT * FROM cw_report_view where view_name = "'.$view_name.'" and report_setting_id = "'.$report_setting_id.'" and trans_status = 1 ';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_count = $is_exist_data->num_rows();
$exist_rslt = $is_exist_data->result();
$is_exist_data->next_result();
if((int)$exist_count === 0){
$report_qry = 'INSERT INTO cw_report_view (report_setting_id, view_for,view_name,view_type,view_size,view_chart,view_chart_size,view_tables,view_columns,view_total_columns,trans_created_by, trans_created_date) VALUES ("'.$report_setting_id.'","'.$view_for.'","'.$view_name.'","'.$view_type.'","'.$view_size.'","'.$view_chart.'","'.$view_chart_size.'","'.$view_tables.'","'.$view_columns.'","'.$view_total_columns.'","'.$logged_id.'","'.$date.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$report_qry')");
$insert_result = $insert_info->result();
$insert_id = $insert_result[0]->ins_id;
$insert_info->next_result();
$this->save_view_table_sort($insert_id);
echo json_encode(array('success' => true, 'message' => "View successfully added"));
}else{
echo json_encode(array('success' => false, 'message' => "View Name Already Exist"));
}
}else{
$upd_qry = 'UPDATE cw_report_view SET report_setting_id = "'.$report_setting_id.'",view_for = "'.$view_for.'",view_name = "'.$view_name.'",view_type = "'.$view_type.'",view_size = "'.$view_size.'",view_chart = "'.$view_chart.'",view_chart_size = "'.$view_chart_size.'",view_tables = "'.$view_tables.'",view_columns = "'.$view_columns.'",view_total_columns = "'.$view_total_columns.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_view_id = "'.$prime_view_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$this->save_view_table_sort($prime_view_id);
echo json_encode(array('success' => true, 'message' => "View successfully updated"));
}
}
public function get_view_block_sort_list($report_setting_id){
$view_block_qry = 'select * from cw_report_view where trans_status = 1 and report_setting_id = "'.$report_setting_id.'" order by abs(view_sort)';
$view_block_info = $this->db->query("CALL sp_a_run ('SELECT','$view_block_qry')");
$view_block_result = $view_block_info->result();
$view_block_info->next_result();
$li_list = "";
foreach($view_block_result as $rslt){
$prime_view_id = $rslt->prime_view_id;
$report_setting_id = $rslt->report_setting_id;
$view_name = ucwords($rslt->view_name);
$li_id = "li_".$prime_view_id;
$a_id = "a_".$prime_view_id."_$count";
$li_list .= "<li class='ui-state-default' id='$li_id'>
<table style='width:100%;'>
<tr>
<td style='font-weight:bold'>
<label>$view_name</label><br/>
<span style='font-size:13px;font-weight:normal;color:#999999;'> $show_icon $form_view_type </span>
</td>
</tr>
</table>
</li>";
}
return $li_list;
}
// VIEW SORT ORDER UPDATE
public function update_view_sortorder(){
$view_idsInOrder = $this->input->post('view_idsInOrder');
$sort_order = 0;
foreach($view_idsInOrder as $order){
if($order){
$sort_order++;
$order = explode("_",$order);
$prime_form_view_id = $order[1];
$upd_qry = 'UPDATE cw_report_view SET view_sort = "'.$sort_order.'" where prime_view_id = "'.$prime_form_view_id.'"';
$info = $this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
}
}
echo json_encode(array('success' => TRUE, 'message' => "Sort position updated to database"));
}
//GET VIEW BLOCK LIST
public function view_block_list($report_setting_id){
$view_block_qry = 'select * from cw_report_view where trans_status = 1 and report_setting_id = "'.$report_setting_id.'" order by abs(view_sort)';
$view_block_info = $this->db->query("CALL sp_a_run ('SELECT','$view_block_qry')");
$view_block_result = $view_block_info->result();
$filter_list = array(1=>"Required",2=>"Not Required");
$view_type_list = array(1=>"Table With Header",2=>"Table No Header");
$view_size_list = array(1=>"col-md-3",2=>"col-md-4",3=>"col-md-6",4=>"col-md-12");
$view_block_info->next_result();
foreach($view_block_result as $rslt){
$prime_view_id = $rslt->prime_view_id;
$report_setting_id = $rslt->report_setting_id;
$view_name = ucwords($rslt->view_name);
$view_for = $rslt->view_for;
$view_type = $rslt->view_type;
$view_size = $rslt->view_size;
$view_chart = $rslt->view_chart;
$view_chart_size = $rslt->view_chart_size;
$view_tables = $rslt->view_tables;
$view_columns = $rslt->view_columns;
$view_total_columns = $rslt->view_total_columns;
$view_type = $view_type_list[$view_type];
$view_size = $view_size_list[$view_size];
$view_chart = $filter_list[$view_chart];
$view_chart_size = $view_size_list[$view_chart_size];
$view_tables = str_replace(",","<br/>",$view_tables);
$view_columns = str_replace(",","<br/>",$view_columns);
$view_total_columns = str_replace(",","<br/>",$view_total_columns);
$tr_line .= "<tr>
<td>$view_name</td>
<td style='white-space: nowrap;'>$view_type</td>
<td style='white-space: nowrap;'>$view_size</td>
<td style='white-space: nowrap;'>$view_chart</td>
<td style='white-space: nowrap;'>$view_chart_size</td>
<td>$view_tables</td>
<td>$view_columns</td>
<td>$view_total_columns</td>
<td style='text-align:center;vertical-align: middle'><a class='btn btn-xs btn-edit' onclick=view_edit('$prime_view_id')> <i class='fa fa-pencil-square-o' aria-hidden='true'></i> Edit</a></td>
<td style='text-align:center;vertical-align: middle'><a class='btn btn-xs btn-danger' onclick=view_remove('$prime_view_id','$report_setting_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$view_block_list = "<table class='table table-bordered table-stripted' id='view_block_table'>
<thead>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th style='text-align: center;'>View Name</th>
<th style='text-align: center;'>View Type</th>
<th style='text-align: center;'>View Size</th>
<th style='text-align: center;'>View Chart</th>
<th style='text-align: center;'>View Chart Size</th>
<th style='text-align: center;'>View Tables</th>
<th style='text-align: center;'>View columns</th>
<th style='text-align: center;'>Total columns</th>
<th style='text-align:center;'>Edit</th>
<th style='text-align:center;'>Delete</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $view_block_list;
}
//VIEW EDIT INFO
public function view_edit(){
$prime_view_id = $this->input->post('prime_view_id');
$view_edit = 'SELECT prime_view_id,report_setting_id,view_name,view_for,view_type,view_size,view_chart,view_chart_size,view_tables,view_columns,view_total_columns FROM cw_report_view WHERE prime_view_id = "'.$prime_view_id.'" and trans_status = 1';
$edit_info = $this->db->query("CALL sp_a_run ('SELECT','$view_edit')");
$edit_result = $edit_info->result();
$edit_info->next_result();
$table_info = $this->get_columns($edit_result[0]->view_tables);
$table_info = json_decode($table_info,true);
$view_columns = $table_info['table_column'];
echo json_encode(array('success' => true,'view_columns'=>$view_columns,'edit_result'=>$edit_result[0]));
}
//VIEW LIST REMOVE
public function view_remove(){
$prime_view_id = $this->input->post('prime_view_id');
$report_setting_id = $this->input->post('report_setting_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_report_view SET trans_status = 0 ,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_view_id = "'.$prime_view_id.'"';
$this->db->query("CALL sp_a_run ('SELECT','$remove_qry')");
$view_block_list = $this->view_block_list($report_setting_id);
echo json_encode(array('success' => true,'message'=>'View Remove Successfully !!!','view_block_list'=>$view_block_list,'table_view_list'=>$table_view_list));
}
//VIEW CUSTOM SELECT COLUMNS
public function update_map_block_column(){
$view_map_block_id = $this->input->post('view_map_block_id');
$view_edit = 'SELECT prime_view_id,report_setting_id,view_name,view_for,view_type,view_size,view_chart,view_chart_size,view_tables,view_columns,view_total_columns FROM cw_report_view WHERE prime_view_id = "'.$view_map_block_id.'" and trans_status = 1';
$edit_info = $this->db->query("CALL sp_a_run ('SELECT','$view_edit')");
$edit_result = $edit_info->result();
$edit_info->next_result();
if($edit_result){
$report_setting_id = $edit_result[0]->report_setting_id;
$view_tables = $edit_result[0]->view_tables;
$table_info = $this->get_columns($view_tables);
$table_info = json_decode($table_info,true);
$table_column = $table_info['table_column'];
$select_query = 'SELECT * FROM cw_report_setting where prime_report_setting_id = "'.$report_setting_id.'" and trans_status = 1 ';
$select_data = $this->db->query("CALL sp_a_run ('SELECT','$select_query')");
$select_rslt = $select_data->result();
$select_data->next_result();
if($select_rslt){
$report_prime_column = explode(",",$select_rslt[0]->report_prime_column);
$form_dropdown = form_dropdown(array("name" =>"","id" =>"","class" =>'form-control input-sm'),$table_column);
$tr_line = "";
foreach($report_prime_column as $prime_column){
$column_name = explode(".",$prime_column);
$column_name = ucwords($column_name[1]);
$map_column = $prime_column."_map";
$map_query = 'SELECT * FROM cw_report_view_map where report_view_id = "'.$view_map_block_id.'" and prime_column = "'.$prime_column.'" and trans_status = 1 ';
$map_data = $this->db->query("CALL sp_a_run ('SELECT','$map_query')");
$map_rslt = $map_data->result();
$map_data->next_result();
$map_column_val = "";
if($map_rslt){
$map_column_val = $map_rslt[0]->map_column;
}
$form_input = form_input(array("name"=>"prime_column[]", "id"=>"prime_column","value"=>"$prime_column","class"=>"form-control input-sm","readonly" => true));
$form_dropdown = form_dropdown(array("name" =>"map_column[]","id" =>"map_column","class"=>'form-control input-sm'),$table_column,$map_column_val);
$tr_line .= "<tr><td>$column_name</td><td>$form_input</td><td>$form_dropdown</td></tr>";
}
}
}
$map_report_setting_id = form_input(array('name'=>'map_report_setting_id','id'=>'map_report_setting_id','class'=>'form-control input-sm','value'=>$report_setting_id,'type'=>'Hidden'));
$table_info = "$map_report_setting_id
<table class='table table-bordered'>
<thead>
<tr>
<td>Column Name</td>
<td>Column</td>
<td>Map Column</td>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>
<div style='text-align:right;padding:8px 0px;'>
<button class='btn btn-primary btn-sm' id='save_view_map_block_btn'>Save</button>
</div>";
echo json_encode(array('success' => true,'view_map_columns_list'=>$table_info));
}
public function save_view_map_columns(){
$report_view_id = $this->input->post('view_map_block_id');
$report_setting_id = $this->input->post('map_report_setting_id');
$prime_column = $this->input->post('prime_column[]');
$map_column = $this->input->post('map_column[]');
$remove_query = 'UPDATE cw_report_view_map SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE report_view_id = "'.$report_view_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_query')");
$prime_count = count($prime_column)-1;
$sts = false;
for($i=0;$i<=$prime_count;$i++){
$prime_column_val = $prime_column[$i];
$map_column_val = $map_column[$i];
if($map_column_val){
$sts = true;
$table_query = 'insert into cw_report_view_map (report_view_id,report_setting_id,prime_column,map_column,trans_created_by,trans_created_date) value ("'.$report_view_id.'","'.$report_setting_id.'","'.$prime_column_val.'","'.$map_column_val.'","'.$logged_id.'","'.$today_date.'")';
$this->db->query("CALL sp_a_run ('RUN','$table_query')");
}
}
$report_delete_qry = 'delete FROM cw_report_view_map WHERE trans_status = 0';
$this->db->query("CALL sp_a_run ('RUN','$report_delete_qry')");
if($sts){
$msg = "View mapped successfully Updated";
}else{
$msg = "No Inputs mapped";
}
echo json_encode(array('success' => $sts, 'message'=>$msg));
}
//VIEW CUSTOM SELECT COLUMNS
public function update_view_select_column(){
$view_block_id = $this->input->post('view_block_id');
$view_edit = 'SELECT prime_view_id,report_setting_id,view_name,view_for,view_type,view_size,view_chart,view_chart_size,view_tables,view_columns,view_total_columns FROM cw_report_view WHERE prime_view_id = "'.$view_block_id.'" and trans_status = 1';
$edit_info = $this->db->query("CALL sp_a_run ('SELECT','$view_edit')");
$edit_result = $edit_info->result();
$edit_info->next_result();
$table_info = $this->get_columns($edit_result[0]->view_tables);
$table_info = json_decode($table_info,true);
$view_select_column = $table_info['table_column'];
echo json_encode(array('success' => true,'view_select_column'=>$view_select_column));
}
//VIEW CUSTOM SELECT SAVE
public function save_view_select(){
$prime_view_select_id = (int)$this->input->post('prime_view_select_id');
$view_report_setting_id = (int)$this->input->post('view_report_setting_id');
$view_block_id = (int)$this->input->post('view_block_id');
$view_column_name = str_replace(" ","_",strtolower($this->input->post('view_column_name')));
$view_select_column_query = str_replace('"',"^",strtolower($this->input->post('view_select_column_query')));
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
if($prime_view_select_id === 0){
$is_exist_qry = 'SELECT * FROM cw_report_view_select where view_block_id = "'.$view_block_id.'" and view_column_name = "'.$view_column_name.'" and trans_status = 1 ';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_count = $is_exist_data->num_rows();
$exist_rslt = $is_exist_data->result();
$is_exist_data->next_result();
if((int)$exist_count === 0){
$report_qry = 'INSERT INTO cw_report_view_select (view_report_setting_id,view_block_id,view_column_name,view_select_column_query,trans_created_by, trans_created_date) VALUES ("'.$view_report_setting_id.'","'.$view_block_id.'","'.$view_column_name.'","'.$view_select_column_query.'","'.$logged_id.'","'.$date.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$report_qry')");
$insert_result = $insert_info->result();
$insert_id = $insert_result[0]->ins_id;
$insert_info->next_result();
$view_select_list = $this->view_select_list($view_report_setting_id);
$this->save_view_table_sort($view_block_id);
echo json_encode(array('success' => true, 'message' => "Custom Column Name successfully added", 'view_select_list' => $view_select_list));
}else{
echo json_encode(array('success' => false, 'message' => "Custom Column Name Already Exist"));
}
}else{
$upd_qry = 'UPDATE cw_report_view_select SET view_report_setting_id = "'.$view_report_setting_id.'",view_block_id = "'.$view_block_id.'",view_column_name = "'.$view_column_name.'",view_select_column_query = "'.$view_select_column_query.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_view_select_id = "'.$prime_view_select_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$view_select_list = $this->view_select_list($view_report_setting_id);
$this->save_view_table_sort($view_block_id);
echo json_encode(array('success' => true, 'message' => "Custom Column Name successfully updated", 'view_select_list' => $view_select_list));
}
}
//VIEW SELECT LIST
public function view_select_list($view_report_setting_id){
$select_qry = 'select * from cw_report_view_select inner join cw_report_view on view_block_id = prime_view_id where cw_report_view_select.trans_status = 1 and view_report_setting_id = "'.$view_report_setting_id.'"';
$select_info = $this->db->query("CALL sp_a_run ('SELECT','$select_qry')");
$select_result = $select_info->result();
$select_info->next_result();
foreach($select_result as $rslt){
$prime_view_select_id = $rslt->prime_view_select_id;
$view_report_setting_id = $rslt->view_report_setting_id;
$view_block_id = $rslt->view_block_id;
$view_name = $rslt->view_name;
$view_column_name = $rslt->view_column_name;
$view_select_column_query = $rslt->view_select_column_query;
$tr_line .= "<tr>
<td>$view_name</td>
<td>$view_column_name</td>
<td style='width:590px;overflow:auto;'>$view_select_column_query</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=view_select_edit('$prime_view_select_id')> <i class='fa fa-pencil-square-o' aria-hidden='true'></i> Edit</a></td>
<td style='text-align:center;'><a class='btn btn-xs btn-danger' onclick=view_select_remove('$prime_view_select_id','$view_report_setting_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$view_select_list = "<table class='table table-bordered table-stripted' id='view_select_table'>
<thead>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th style='text-align: center;'>View Block Name</th>
<th style='text-align: center;'>Column Name</th>
<th style='text-align: center;'>Select Query</th>
<th style='text-align:center;'>Edit</th>
<th style='text-align:center;'>Delete</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $view_select_list;
}
//VIEW SELECT EDIT INFO
public function view_select_edit(){
$prime_view_select_id = $this->input->post('prime_view_select_id');
$view_edit = 'SELECT prime_view_select_id,view_report_setting_id,view_block_id,view_column_name,view_select_column_query FROM cw_report_view_select WHERE prime_view_select_id = "'.$prime_view_select_id.'" and trans_status = 1';
$edit_info = $this->db->query("CALL sp_a_run ('SELECT','$view_edit')");
$edit_result = $edit_info->result();
$edit_info->next_result();
$view_block_query = 'SELECT prime_view_id,report_setting_id,view_name,view_for,view_type,view_size,view_chart,view_chart_size,view_tables,view_columns,view_total_columns FROM cw_report_view WHERE prime_view_id = "'.$edit_result[0]->view_block_id.'" and trans_status = 1';
$view_block_info = $this->db->query("CALL sp_a_run ('SELECT','$view_block_query')");
$view_block_result = $view_block_info->result();
$view_block_info->next_result();
$table_info = $this->get_columns($view_block_result[0]->view_tables);
$table_info = json_decode($table_info,true);
$view_select_column = $table_info['table_column'];
echo json_encode(array('success' => true,'view_select_column'=>$view_select_column,'edit_result'=>$edit_result[0]));
}
//VIEW SELECT LIST REMOVE
public function view_select_remove(){
$prime_view_select_id = $this->input->post('prime_view_select_id');
$view_report_setting_id = $this->input->post('view_report_setting_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_report_view_select SET trans_status = 0 ,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_view_select_id = "'.$prime_view_select_id.'"';
$this->db->query("CALL sp_a_run ('SELECT','$remove_qry')");
$view_select_list = $this->view_select_list($view_report_setting_id);
echo json_encode(array('success' => true,'message'=>'View Remove Successfully !!!','view_select_list'=>$view_select_list));
}
//ONCAHNGE UPDATE TABLE JOIN BLOCK
public function update_table_join_block(){
$table_block_id = $this->input->post('table_block_id');
$report_qry = 'SELECT * FROM cw_report_view where prime_view_id = "'.$table_block_id.'" and trans_status = 1';
$report_data = $this->db->query("CALL sp_a_run ('SELECT','$report_qry')");
$report_rslt = $report_data->result();
$report_data->next_result();
foreach($report_rslt as $rslt){
$prime_view_id = $rslt->prime_view_id;
$view_tables = explode(",",$rslt->view_tables);
$table_list = array();
$table_list[""] = "---- Select Table ----";
foreach($view_tables as $table_value){
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_list[$table_value] = $table_name;
}
$prime_in = '"'.str_replace(",",'","', $rslt->view_tables);
$custom_in = str_replace(",",'_cf","', $rslt->view_tables).'_cf"';
$table_in = $prime_in.'","'.$custom_in;
$get_colums = 'SELECT `TABLE_NAME`,`COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="cafs_wealth" AND `TABLE_NAME` IN ('.$table_in.') AND COLUMN_NAME NOT LIKE "%trans%"';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$column_result = $column_info->result();
$column_info->next_result();
$column_list = array();
$column_list[""] = "---- Select Column ----";
foreach($column_result as $column){
$table_value = $column->TABLE_NAME;
$column_value = $column->COLUMN_NAME;
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$column_name = ucwords(str_replace("_"," ",$column_value));
$column_list[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
$join_array = array(""=>"--- Select join type ---","inner" => "inner","left" => "left","right" => "right");
$join_query = 'SELECT * FROM cw_report_view_table WHERE trans_status = 1 and join_for = "'.$prime_view_id.'" order by abs(line_sort)';
$join_info = $this->db->query("CALL sp_a_run ('SELECT','$join_query')");
$join_result = $join_info->result();
$join_info->next_result();
$table_tr_line = "";
$table_count = 0;
$condition_table_count = count($view_tables) - 1; //round(count($condition_table)/2);
for($i=1;$i<= $condition_table_count;$i++){
$prime_print_table_id = 0;
$line_prime_table = "";
$line_prime_col = "";
$line_join_type = "";
$line_join_table = "";
$line_join_col = "";
if($join_result){
$prime_report_table_id = $join_result[$table_count]->prime_report_table_id;
$line_prime_table = $join_result[$table_count]->line_prime_table;
$line_prime_col = $join_result[$table_count]->line_prime_col;
$line_join_type = $join_result[$table_count]->line_join_type;
$line_join_table = $join_result[$table_count]->line_join_table;
$line_join_col = $join_result[$table_count]->line_join_col;
}
$table_cond_for_id = form_input(array( 'name' =>"prime_report_table_id[]",'class' => 'form-control input-sm','value' =>$prime_report_table_id,'type'=>'Hidden'));
$prime_table_data = form_dropdown(array('name' =>"view_line_prime_table[]",'class' => 'form-control input-sm'), $table_list,$line_prime_table);
$prime_col_data = form_dropdown(array('name' =>"view_line_prime_col[]",'class' => 'form-control input-sm'),$column_list,$line_prime_col);
$join_data = form_dropdown(array('name' =>"view_line_join_type[]",'class' => 'form-control input-sm'),$join_array,$line_join_type);
$join_table_data = form_dropdown(array('name' =>"view_line_join_table[]",'class' => 'form-control input-sm'), $table_list,$line_join_table);
$join_col_data = form_dropdown(array('name' =>"view_line_join_col[]",'class' => 'form-control input-sm'),$column_list,$line_join_col);
$table_tr_line .= "<tr>
<td>$table_cond_for_id $prime_table_data</td>
<td>$prime_col_data</td>
<td>$join_data</td>
<td>$join_table_data</td>
<td>$join_col_data</td>
</tr>";
$table_count++;
}
$join_for = form_input(array('name'=>'join_for','id'=>'join_for','class'=>'form-control input-sm','value'=>$prime_view_id,'type'=>'Hidden'));
$table_content = "$join_for
<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th>Primary table</th>
<th>Primary column</th>
<th>Join type</th>
<th>Join table</th>
<th>Join primary column</th>
</tr>
$table_tr_line
</table>
<div style='text-align:right;padding:8px 0px;'>
<button class='btn btn-primary btn-sm' id='save_view_join_table_btn'>Save</button>
</div>";
}
echo json_encode(array('success' => true,'table_content'=>$table_content));
}
// SAVE VIEW TABLE JOIN
public function save_view_join_table(){
$join_for = $this->input->post('join_for');
$prime_report_table_id = $this->input->post('prime_report_table_id');
$prime_print_table_id = $this->input->post('prime_print_table_id[]');
$line_prime_table = $this->input->post('view_line_prime_table[]');
$line_prime_col = $this->input->post('view_line_prime_col[]');
$line_join_type = $this->input->post('view_line_join_type[]');
$line_join_table = $this->input->post('view_line_join_table[]');
$line_join_col = $this->input->post('view_line_join_col[]');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$tab_count = 0;
$remove_query = 'UPDATE cw_report_view_table SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE join_for = "'.$join_for.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_query')");
$table_count = count($line_prime_table);
for($i=1;$i<= $table_count;$i++){
$prime_report_table_id_val = $prime_report_table_id[$tab_count];
$line_prime_table_val = $line_prime_table[$tab_count];
$line_prime_col_val = $line_prime_col[$tab_count];
$line_join_type_val = $line_join_type[$tab_count];
$line_join_table_val = $line_join_table[$tab_count];
$line_join_col_val = $line_join_col[$tab_count];
if((int)$prime_report_table_id_val === 0){
$table_query = 'insert into cw_report_view_table (join_for,line_prime_table,line_prime_col,line_join_type,line_join_table,line_join_col,line_sort,trans_created_by,trans_created_date) value ("'.$join_for.'","'.$line_prime_table_val.'","'.$line_prime_col_val.'","'.$line_join_type_val.'","'.$line_join_table_val.'","'.$line_join_col_val.'","'.$i.'","'.$logged_id.'","'.$today_date.'")';
}else{
$table_query = 'UPDATE cw_report_view_table SET trans_status = 1, line_prime_table = "'.$line_prime_table_val.'",line_prime_col = "'.$line_prime_col_val.'",line_join_type = "'.$line_join_type_val.'",line_join_table = "'.$line_join_table_val.'",line_join_col = "'.$line_join_col_val.'",line_sort = "'.$i.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE prime_report_table_id = "'.$prime_report_table_id_val.'"';
}
$this->db->query("CALL sp_a_run ('RUN','$table_query')");
$tab_count++;
}
$report_delete_qry = 'delete FROM cw_report_view_table WHERE trans_status = 0';
$this->db->query("CALL sp_a_run ('RUN','$report_delete_qry')");
echo json_encode(array('success' => true, 'message'=>"Table Join successfully Updated"));
}
// SAVE WHERE CONDITION
public function save_view_table_where(){
$where_report_id = (int)$this->input->post('where_report_id');
$where_for_id = (int)$this->input->post('view_where_block_id');
$where_condition = trim(strtolower($this->input->post('view_where_condition')));
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$exist_query = 'SELECT * FROM cw_report_view_where WHERE where_for_id = "'.$where_for_id.'" and trans_status = 1';
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_count = (int)$exist_info->num_rows();
$exist_result = $exist_info->result();
$exist_info->next_result();
if($exist_count === 0){
$search_qry = 'INSERT INTO cw_report_view_where (where_report_id,where_for_id,where_condition,trans_created_by, trans_created_date) VALUES ("'.$where_report_id.'","'.$where_for_id.'","'.$where_condition.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$search_qry')");
$view_table_where_list = $this->view_table_where_list($where_report_id);
echo json_encode(array('success' => true,'message'=>"Where added successfully !!!",'view_table_where_list'=> $view_table_where_list));
}else{
$prime_report_where_id = (int)$exist_result[0]->prime_report_where_id;
$upd_qry = 'UPDATE cw_report_view_where SET where_report_id = "'.$where_report_id.'",where_for_id = "'.$where_for_id.'",where_condition = "'.$where_condition.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_report_where_id = "'.$prime_report_where_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$view_table_where_list = $this->view_table_where_list($where_report_id);
echo json_encode(array('success' => true,'message'=>"Where updated successfully !!!",'view_table_where_list'=> $view_table_where_list));
}
}
public function view_table_where_list($where_report_id){
$where_query = 'SELECT prime_report_where_id,where_report_id,view_name,where_condition FROM cw_report_view_where inner join cw_report_view on where_for_id = prime_view_id WHERE where_report_id = "'.$where_report_id.'" and cw_report_view_where.trans_status = 1';
$where_info = $this->db->query("CALL sp_a_run ('SELECT','$where_query')");
$where_result = $where_info->result();
$where_info->next_result();
foreach($where_result as $rslt){
$prime_report_where_id = $rslt->prime_report_where_id;
$where_report_id = $rslt->where_report_id;
$view_name = $rslt->view_name;
$where_condition = $rslt->where_condition;
$tr_line .= "<tr>
<td>$view_name</td>
<td style='width:590px;overflow:auto;'>$where_condition</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=view_where_edit('$prime_report_where_id')> <i class='fa fa-pencil-square-o' aria-hidden='true'></i> Edit</a></td>
<td style='text-align:center;'><a class='btn btn-xs btn-danger' onclick=view_where_remove('$prime_report_where_id','$where_report_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$view_where_list = "<table class='table table-bordered table-stripted' id='view_where_table'>
<thead>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th style='text-align: center;'>View Block Name</th>
<th style='text-align: center;'>Where Query</th>
<th style='text-align:center;'>Edit</th>
<th style='text-align:center;'>Delete</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $view_where_list;
}
//VIEW WHERE EDIT INFO
public function view_where_edit(){
$prime_report_where_id = $this->input->post('prime_report_where_id');
$view_edit = 'SELECT where_for_id,where_report_id,where_condition FROM cw_report_view_where WHERE prime_report_where_id = "'.$prime_report_where_id.'" and trans_status = 1';
$edit_info = $this->db->query("CALL sp_a_run ('SELECT','$view_edit')");
$edit_result = $edit_info->result();
$edit_info->next_result();
$view_block_query = 'SELECT prime_view_id,report_setting_id,view_name,view_for,view_type,view_size,view_chart,view_chart_size,view_tables,view_columns,view_total_columns FROM cw_report_view WHERE prime_view_id = "'.$edit_result[0]->where_for_id.'" and trans_status = 1';
$view_block_info = $this->db->query("CALL sp_a_run ('SELECT','$view_block_query')");
$view_block_result = $view_block_info->result();
$view_block_info->next_result();
$table_info = $this->get_columns($view_block_result[0]->view_tables);
$table_info = json_decode($table_info,true);
$view_select_column = $table_info['table_column'];
echo json_encode(array('success' => true,'view_select_column'=>$view_select_column,'edit_result'=>$edit_result[0]));
}
//VIEW WHERE LIST REMOVE
public function view_where_remove(){
$prime_report_where_id = $this->input->post('prime_report_where_id');
$where_report_id = $this->input->post('where_report_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_report_view_where SET trans_status = 0 ,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_report_where_id = "'.$prime_report_where_id.'"';
$this->db->query("CALL sp_a_run ('SELECT','$remove_qry')");
$view_table_where_list = $this->view_table_where_list($where_report_id);
echo json_encode(array('success' => true,'message'=>'where Remove Successfully !!!','view_table_where_list'=>$view_table_where_list));
}
public function save_view_table_sort($view_id){
$view_block_query = 'SELECT prime_view_id,report_setting_id,view_name,view_for,view_type,view_size,view_chart,view_chart_size,view_tables,view_columns,view_total_columns FROM cw_report_view WHERE prime_view_id = "'.$view_id.'" and trans_status = 1';
$view_block_info = $this->db->query("CALL sp_a_run ('SELECT','$view_block_query')");
$view_block_result = $view_block_info->result();
$view_block_info->next_result();
if($view_block_result){
$prime_view_id = $view_block_result[0]->prime_view_id;
$report_setting_id = $view_block_result[0]->report_setting_id;
$view_type = (int)$view_block_result[0]->view_type;
$view_columns = $view_block_result[0]->view_columns;
if($view_type ===1){
$view_columns = explode(",",$view_columns);
foreach($view_columns as $columns){
$view_table_list[] = array('columns'=>$columns,'mode'=>1);
}
$custom_select_query = 'SELECT * FROM cw_report_view_select where view_block_id = "'.$view_id.'" and trans_status = 1 ';
$custom_select_data = $this->db->query("CALL sp_a_run ('SELECT','$custom_select_query')");
$custom_select_rslt = $custom_select_data->result();
$custom_select_data->next_result();
foreach($custom_select_rslt as $select_rslt){
$view_column_name = $select_rslt->view_column_name;
$view_table_list[] = array('columns'=>$view_column_name,'mode'=>2);
}
$logged_id = $this->session->userdata('logged_id');
$remove_all = 'UPDATE cw_report_view_table_sort SET trans_status = 0 where custom_view_id = "'.$view_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_all')");
$table_sort = 0;
foreach($view_table_list as $view_list){
$view_column = $view_list['columns'];
$table_mode = $view_list['mode'];
$table_sort++;
$exits_count_qry = 'select * from cw_report_view_table_sort where custom_view_id = "'.$view_id.'" and view_column = "'.$view_column.'"';
$exits_count_info = $this->db->query("CALL sp_a_run ('SELECT','$exits_count_qry')");
$exits_count_result = $exits_count_info->result();
$rslt_count = (int)$exits_count_info->num_rows();
$exits_count_info->next_result();
$prime_view_table_id = (int)$exits_count_result[0]->prime_view_table_id;
$date = date("Y-m-d h:i:s");
if($rslt_count === 0){
$table_view_qry = 'INSERT INTO cw_report_view_table_sort (custom_view_id,view_column, table_sort,table_mode,trans_created_by, trans_created_date) VALUES ("'.$view_id.'","'.$view_column.'","'.$table_sort.'","'.$table_mode.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$table_view_qry')");
}else
if($rslt_count === 1){
$table_view_upd_qry = 'UPDATE cw_report_view_table_sort SET trans_status = 1,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_view_table_id = "'.$prime_view_table_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$table_view_upd_qry')");
}
}
$report_delete_qry = 'delete FROM cw_report_view_table_sort WHERE trans_status = 0';
$this->db->query("CALL sp_a_run ('RUN','$report_delete_qry')");
}
}
}
public function view_table_sort($view_id){
$view_block_qry = 'select * from cw_report_view where trans_status = 1 and report_setting_id = "'.$view_id.'" order by abs(view_sort)';
$view_block_info = $this->db->query("CALL sp_a_run ('SELECT','$view_block_qry')");
$view_block_result = $view_block_info->result();
$view_block_info->next_result();
$table_sort = "";
foreach($view_block_result as $rslt){
$prime_view_id = $rslt->prime_view_id;
$view_name = ucwords($rslt->view_name);
$view_type = $rslt->view_type;
$input_th = "";
$input_td = "";
$table_content = "";
if((int)$view_type === 1){
$column_qry = 'select * from cw_report_view_table_sort where custom_view_id = "'.$prime_view_id.'" and trans_status = 1 order by abs(table_sort)';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$column_qry')");
$column_result = $column_info->result();
$column_info->next_result();
$tr_id = "tr_".$prime_view_id;
foreach($column_result as $report){
$prime_view_table_id = $report->prime_view_table_id;
$custom_view_id = $report->custom_view_id;
$view_column = $report->view_column;
$table_sort = (int)$report->table_sort;
$table_mode = (int)$report->table_mode;
$th_id = "th_".$prime_view_table_id."::".$custom_view_id;
if($table_mode === 1){
$view_column = explode('.',$view_column);
$colum_name = $view_column[1];
}else{
$colum_name = $view_column;
}
$colum_name = ucwords(str_replace("_"," ",$colum_name));
//echo "$colum_name :: $th_id<br/>";
$input_th .= "<th class='ui-state-default' id='$th_id' style='background-color: #1883E9; color:#FFFFFF;cursor:pointer;border-right: 1px solid #CCCCCC;'>$colum_name</th>";
$input_td .= "<td style='border-right:1px solid #CCCCCC;'>-</td>";
}
$table_sort_content .= "<div class='col-md-12' style='padding:0px'>
<h5 class='tab_head' style='margin:8px !important;'>$view_name</h5>
<div style='padding: 8px; background-color: #ffffff; box-shadow: 0 2px 2px 0 rgba(0, 0, 0, 0.14), 0 1px 5px 0 rgba(0, 0, 0, 0.12), 0 3px 1px -2px rgba(0, 0, 0, 0.2); border-radius: 3px;'>
<table class='table table-hover table-striped'>
<tr id='$tr_id' class='sortable'> $input_th </tr>
<tr> $input_td </tr>
</table>
</div>
</div>";
$table_sort_script .= "update_table_sort_script('$tr_id');\n";
}
}
$table_sort_content = "<div class='row' style='padding:10px;background-color: #f2f2f2;margin:0px;'>
$table_sort_content
<div>";
return array("table_sort_content"=>$table_sort_content,'table_sort_script'=>$table_sort_script);
}
public function update_view_table_sort(){
$table_idsInOrder = $this->input->post('table_idsInOrder');
$logged_id = $this->session->userdata('logged_id');
$sort_order = 0;
$prim_custom_view_id = 0;
$prime_view_table_ids = array();
foreach($table_idsInOrder as $order){
if($order){
$sort_order++;
$order = explode("_",$order);
$order = explode("::",$order[1]);
$prime_view_table_id = $order[0];
$custom_view_id = $order[1];
$prime_view_table_ids[] = $prime_view_table_id;
$prim_custom_view_id = $custom_view_id;
}
}
if((int)$prim_custom_view_id > 0){
$upd_query = 'UPDATE cw_report_view_table_sort SET table_sort = 0 WHERE custom_view_id = "'. $prim_custom_view_id .'"';
$info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
$info->next_result();
$table_sort = 0;
foreach($prime_view_table_ids as $ids){
$table_sort++;
$upd_query = 'UPDATE cw_report_view_table_sort SET table_sort = "'.$table_sort.'" WHERE prime_view_table_id = "'. $ids .'"';
$info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
$info->next_result();
}
echo json_encode(array('success' => TRUE, 'message' => "Sort position updated to database"));
}else{
echo json_encode(array('success' => False, 'message' => "Invalid ids"));
}
}
/* ==============================================================*/
/* ================== VIEW SETTING - END ==================*/
/* ==============================================================*/
}
?>