File: //home/cafsindia/wealth_cafsindia_com/application/controllers/Utilities_settings.php
<?php
/**********************************************************
Filename: Utilities Setting
Description: Utilities Setting for adding new excel formate,print layout and other operation.
Author: Jaffer Sathik
Created on: 26 November 2018
Reviewed by: Udhayakumar Anandhan (REVIEW PENDING)
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 Utilities_settings extends Secure_Controller{
public function __construct(){
parent::__construct('utilities_settings');
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$data['table_headers']=$this->xss_clean(get_form_setting_headers());
$this->load->view('utilities_settings/manage',$data);
}
/* ==============================================================*/
/* ================== COMMON OPEARTION - START ==================*/
/* ==============================================================*/
//MODULE SEARCH OPEARTION
public function search(){ /*=== UDY REVIEW DONE ===*/
$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 = "abs(menu_sort),abs(sort)";
}
if(!$order){
$order = "asc";
}
$admin_module = array("employees"=>true);
// Fetch Records
$info = $this->db->query("CALL sp_form_setting_search ('SEARCH','$search','$offset','$limit','$sort','$order')");
$result = $info->result();
$info->next_result();
$data_rows = array();
foreach ($result as $form_setting){
$prime_module_id = $form_setting->module_id;
if(!$admin_module[$form_setting->module_id]){
$data_rows[]=get_form_setting_datarows($form_setting,$this);
}
}
$data_rows=$this->xss_clean($data_rows);
// Fetch Records Count
$count_info = $this->db->query("CALL sp_form_setting_search ('COUNT','$search','$offset','$limit','$sort','$order')");
$count_result = $count_info->result();
$count_info->next_result();
$num_rows = $count_result[0]->data_count;
echo json_encode(array('total'=>$num_rows,'rows'=>$data_rows));
}
//MODULE VIEW OPEARTION
public function view($prime_module_id =-1){ /*=== UDY REVIEW DONE ===*/
$data['prime_module_id'] = $prime_module_id;
$table_prime = "cw_".$prime_module_id; //
$table_prime_id = "prime_".$prime_module_id."_id";
$table_cf = "cw_".$prime_module_id."_cf";//
$table_cf_id = "prime_".$prime_module_id."_cf_id";
$table_name = ucwords(str_replace("_"," ",$table_prime));
$table_cf_name = ucwords(str_replace("_"," ",$table_cf));
$table_list = array(''=>'---- Select Table ----',$table_prime=>$table_name,$table_cf=>$table_cf_name);
$data['table_list'] = $table_list;
$table_mand_list = array($table_prime,$table_cf);//
$data['table_mand_list'] = $table_mand_list;
$get_colums = 'SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="cafs_wealth" AND `TABLE_NAME` IN ("'.$table_prime.'","'.$table_cf.'") AND COLUMN_NAME NOT LIKE "%trans%" AND COLUMN_NAME NOT IN ("'.$table_prime_id.'","'.$table_cf_id.'")';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$column_result = $column_info->result();
$column_info->next_result();
$column_list[""] = "---- Select Column ----";
foreach($column_result as $column){
$column_value = $column->COLUMN_NAME;
$column_name = ucwords(str_replace("_"," ",$column_value));
$column_list[$column_value] = $column_name;
}
$data['column_list'] = $column_list;
$get_mandatory_colums = 'SELECT prime_form_id,prime_module_id,label_name FROM `cw_form_setting` WHERE prime_module_id = "'.$prime_module_id.'" and mandatory_field = 1';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_mandatory_colums')");
$column_result = $column_info->result();
$column_info->next_result();
foreach($column_result as $column){
$column_value = $column->label_name;
$column_name = strtolower(str_replace(" ","_",$column_value));
$mandatory_list[] = $column_name;
}
$data['mandatory_list'] = $mandatory_list;
$get_excel_name = 'SELECT prime_excel_format_id,excel_name FROM `cw_util_excel_format` WHERE excel_module_id = "'.$prime_module_id.'" AND trans_status = 1';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_excel_name')");
$column_result = $column_info->result();
$column_info->next_result();
$excel_format_list[""] = "-- Select Format --";
foreach($column_result as $column){
$column_value = $column->excel_name;
$key = $column->prime_excel_format_id;
$excel_format_list[$key] = $column_value;
}
$data['excel_format_list'] = $excel_format_list;
$excel_view = $this->excel_view($prime_module_id);
$excel_content_rslt = json_decode($excel_view);
$data['excel_content'] = $excel_content_rslt->excel_content;
$this->load->view("utilities_settings/form",$data);
}
/* ==============================================================*/
/* ================== COMMON OPEARTION - END ====================*/
/* ==============================================================*/
/* ==============================================================*/
/* =============== EXCEL FORMAT OPEARTION START ===============*/
/* ==============================================================*/
//FORM INPUT SAVE OPEARTION
public function save(){ /*=== UDY REVIEW DONE ===*/
//prime_module_id name only changed excel_module_id
$prime_module_id = $this->input->post('excel_module_id');
$prime_excel_format_id = $this->input->post('prime_excel_format_id');
$excel_name = $this->input->post('excel_name');
$excel_table_name = ltrim(implode(",",$this->input->post('excel_table_name')),",");
$excel_column_name = ltrim(implode(",",$this->input->post('excel_column_name')),",");
$excel_row_start = $this->input->post('excel_row_start');
$exist_column_name = ltrim(implode(",",$this->input->post('exist_column_name')),",");;
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$excel_name_qry = 'SELECT COUNT(*) as counts FROM cw_util_excel_format WHERE excel_module_id = "'. $prime_module_id .'" AND excel_name = "'. $excel_name .'" AND trans_status = 1';
$excel_tab_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_name_qry')");
$excel_tab_result = $excel_tab_info->result();
$excel_tab_info->next_result();
$count = $excel_tab_result[0]->counts;
if((int)$count === 0){
if((int)$prime_excel_format_id === 0){
$table_query = 'insert into cw_util_excel_format (excel_module_id,excel_name,excel_table_name,excel_column_name,excel_row_start,exist_column_name,trans_created_by,trans_created_date) value ("'.$prime_module_id.'","'.$excel_name.'","'.$excel_table_name.'","'.$excel_column_name.'","'.$excel_row_start.'","'.$exist_column_name.'","'.$logged_id.'","'.$today_date.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$table_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
}else{
$upd_excel_qry = 'UPDATE cw_util_excel_format SET excel_name = "'.$excel_name.'", excel_table_name = "'.$excel_table_name.'", excel_column_name = "'.$excel_column_name.'", excel_row_start = "'.$excel_row_start.'", exist_column_name = "'.$exist_column_name.'", trans_updated_by = "'.$logged_id.'", trans_updated_date = "'.$today_date.'" WHERE prime_excel_format_id = "'. $prime_excel_format_id .'"';
$info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_excel_qry')");
$info->next_result();
}
$excel_view = $this->excel_view($prime_module_id);
$excel_content_rslt = json_decode($excel_view);
$excel_content = $excel_content_rslt->excel_content;
echo json_encode(array('success' => TRUE, 'msg' => "Excel Format Saved Successfully!",'excel_content'=>$excel_content));
}else
if((int)$count === 1){
$upd_excel_qry = 'UPDATE cw_util_excel_format SET excel_name = "'.$excel_name.'", excel_table_name = "'.$excel_table_name.'", excel_column_name = "'.$excel_column_name.'", excel_row_start = "'.$excel_row_start.'", exist_column_name = "'.$exist_column_name.'", trans_updated_by = "'.$logged_id.'", trans_updated_date = "'.$today_date.'" WHERE prime_excel_format_id = "'. $prime_excel_format_id .'"';
$info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_excel_qry')");
$info->next_result();
echo json_encode(array('success' => TRUE, 'msg' => "Update Successfully Your Format!"));
}
}
//Bottom Table View Settings
public function excel_view($prime_module_id){ /*=== UDY REVIEW DONE ===*/
if(!$prime_module_id){
return json_encode(array('success' => false,'msg' => "Invalid module information"));
}
$excel_view_qry = 'SELECT prime_excel_format_id,excel_name FROM cw_util_excel_format WHERE excel_module_id = "'. $prime_module_id .'" and trans_status = 1';
$excel_tab_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_view_qry')");
$excel_tab_result = $excel_tab_info->result();
$excel_tab_info->next_result();
$tr_line = "";
foreach($excel_tab_result as $rslt){
$prime_excel_format_id = $rslt->prime_excel_format_id;
$excel_name = $rslt->excel_name;
$excel_name = ucwords(str_replace("_"," ",$excel_name));
$tr_line .= "<tr>
<td>$excel_name</td>
<td><a class='btn btn-xs btn-edit' onclick=get_excel_info('$prime_excel_format_id')> <i class='fa fa-pencil-square-o' aria-hidden='true'></i> Edit</a></td>
<td><a class='btn btn-xs btn-danger' onclick=get_delete_info('$prime_excel_format_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$excel_content = "<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th>Excel Name</th>
<th>Edit</th>
<th>Delete</th>
</tr>
$tr_line
</table>";
return json_encode(array('success' => TRUE,'excel_content' => $excel_content));
}
//Edit call function no needed module id
public function get_excel_info(){ /*=== UDY REVIEW DONE ===*/
$logged_id = $this->session->userdata('logged_id');
$prime_excel_format_id = $this->input->post('prime_excel_format_id');
$excel_view_qry = 'SELECT * FROM cw_util_excel_format WHERE prime_excel_format_id = "'.$prime_excel_format_id.'" AND trans_status = 1';
$excel_tab_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_view_qry')");
$result = $excel_tab_info->result();
$excel_tab_info->next_result();
echo json_encode(array('success' => TRUE,'excel_info' => $result[0]));
}
//Delete call function module id for excel view settings
public function get_delete_info(){/*=== UDY REVIEW DONE ===*/
$today_date = date("Y-m-d h:i:s");
$logged_id = $this->session->userdata('logged_id');
$prime_excel_format_id = $this->input->post('prime_excel_format_id');
$prime_module_id = $this->input->post('excel_module_id');
$del_excel_qry = 'UPDATE cw_util_excel_format SET trans_deleted_by = "'.$logged_id.'", trans_deleted_date = "'.$today_date.'", trans_status = "0" WHERE prime_excel_format_id = "'. $prime_excel_format_id .'"';
$excel_info = $this->db->query("CALL sp_a_run ('UPDATE','$del_excel_qry')");
$excel_info->next_result();
$excel_view = $this->excel_view($prime_module_id);
$excel_content_rslt = json_decode($excel_view);
$excel_content = $excel_content_rslt->excel_content;
echo json_encode(array('success' => TRUE, 'msg' => "Deleted Your Excel Format!",'excel_content'=>$excel_content));
}
/* ==============================================================*/
/* ============== EXCEL FORMAT OPEARTION - END =================*/
/* ==============================================================*/
/* ==============================================================*/
/* ================== EXCEL MAPPING OPEARTION - START ==========*/
/* ==============================================================*/
public function format_mapping(){ /*=== UDY REVIEW DONE ===*/
$excel_format_id = $this->input->post('excel_format');
$excel_view_qry = 'SELECT * FROM cw_util_excel_format WHERE prime_excel_format_id = "'.$excel_format_id.'" AND trans_status = 1';
$excel_tab_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_view_qry')");
$result = $excel_tab_info->result();
$excel_tab_info->next_result();
$prime_excel_format_id = $result[0]->prime_excel_format_id;
$excel_module_id = $result[0]->excel_module_id;
$tab_col = explode(",",$result[0]->excel_column_name);
$excel_cell_value = $this->get_excel_value(100);
$excel_format = form_input(array( 'name' =>"prime_excel_id",'class' => 'form-control input-sm','value' =>$prime_excel_format_id,'type'=>'Hidden'));
$excel_line_module_id = form_input(array( 'name' =>"excel_line_module_id",'class' => 'form-control input-sm','value' =>$excel_module_id,'type'=>'Hidden'));
foreach($tab_col as $name){
$cell_value_query = 'SELECT * FROM cw_util_excel_format_line WHERE excel_line_module_id = "'.$excel_module_id.'" AND excel_line_column_name = "'.$name.'"';
$cell_value_info = $this->db->query("CALL sp_a_run ('SELECT','$cell_value_query')");
$cell_value_result = $cell_value_info->result();
$cell_value_info->next_result();
$prime_excel_format_line_id = 0;
$excel_line_value = "";
if($cell_value_result){
$prime_excel_format_line_id = $cell_value_result[0]->prime_excel_format_line_id;
$excel_line_value = $cell_value_result[0]->excel_line_value;
}
$excel_line_column_name = ucwords(str_replace("_"," ",$name));
$line_id = form_input(array( 'name' =>"prime_excel_format_line_id[]",'class' => 'form-control input-sm','value' =>$prime_excel_format_line_id,'type'=>'Hidden'));
$column_name = form_input(array( 'name' =>"excel_line_column_name[]",'class' => 'form-control input-sm','value' =>$name,'type'=>'Hidden'));
$excel_cell_input = form_dropdown(array('onchange = map_check(this); name' =>"excel_line_value[]",'class' => 'form-control input-sm map_check'), $excel_cell_value,$excel_line_value);
$tr_line .= "<tr>
<td>$column_name $line_id $excel_line_column_name $excel_line_module_id</td>
<td>$excel_cell_input</td>
</tr>";
}
$mapping_screen = "<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th>Table Column</th>
<th>Excel Column</th>
</tr>
$tr_line
</table>";
$mapping_form_details = "<div style='padding:8px;'>
$excel_format
$mapping_screen
</div>
<div style='text-align:right;padding: 20px 15px;padding-top:0px;'>
<button class='btn btn-primary btn-sm' id='save_map_submit'>Submit</button>
</div>";
echo json_encode(array('success' => TRUE,'mapping_form_details' => $mapping_form_details));
}
/* ==============================================================*/
/* ================== SAVE MAPPING OPEARTION - START ===========*/
/* ==============================================================*/
public function save_map(){
$prime_excel_format_line_id = $this->input->post('prime_excel_format_line_id[]');
$prime_excel_format_id = $this->input->post('prime_excel_id');
$excel_line_module_id = $this->input->post('excel_line_module_id');
$excel_line_column_name = $this->input->post('excel_line_column_name[]');
$excel_line_value = $this->input->post('excel_line_value[]');
$col_count = count($excel_line_column_name);
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$count = 0;
for($i=1;$i<= $col_count;$i++){
$prime_excel_format_line_id_val = $prime_excel_format_line_id[$count];
$excel_line_column_name_val = $excel_line_column_name[$count];
$excel_line_value_val = $excel_line_value[$count];
if((int)$prime_excel_format_line_id_val === 0){
$for_map_query = 'insert into cw_util_excel_format_line (prime_excel_format_id,excel_line_module_id,excel_line_column_name,excel_line_value,trans_created_by,trans_created_date) value ("'.$prime_excel_format_id.'","'.$excel_line_module_id.'","'.$excel_line_column_name_val.'","'.$excel_line_value_val.'","'.$logged_id.'","'.$today_date.'")';
}else{
$for_map_query = 'UPDATE cw_util_excel_format_line SET excel_line_value = "'.$excel_line_value_val.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE prime_excel_format_line_id = "'.$prime_excel_format_line_id_val.'"';
}
$this->db->query("CALL sp_a_run ('RUN','$for_map_query')");
$count++;
}
echo json_encode(array('success' => TRUE,'msg' =>"Mapping successfully Added"));
}
/* ==============================================================*/
/* ================== SAVE MAPPING OPEARTION - END =============*/
/* ==============================================================*/
public function cancel_value(){
$prime_module_id = $this->input->post('excel_module_id');
$table_prime = "cw_".$prime_module_id;
$table_cf = "cw_".$prime_module_id."_cf";
$table_mand_list = array($table_prime,$table_cf);
$get_mandatory_colums = 'SELECT prime_form_id,prime_module_id,label_name FROM `cw_form_setting` WHERE prime_module_id = "'.$prime_module_id.'" and mandatory_field = 1';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_mandatory_colums')");
$column_result = $column_info->result();
$column_info->next_result();
foreach($column_result as $column){
$column_value = $column->label_name;
$column_name = strtolower(str_replace(" ","_",$column_value));
$col_mandatory_list[] = $column_name;
}
echo json_encode(array('success' => TRUE,'table_mand_list' =>$table_mand_list, 'col_mandatory_list' => $col_mandatory_list));
}
//Excel ABC Generator Function
public function get_excel_value($tot_cell){
$excel = array(''=>'--- Excel cell value ---');
if((int)$tot_cell > 0){
for($i=0;$i<=$tot_cell;$i++){
$letter = $this->getNameFromNumber($i);
$excel[$letter] = $letter;
}
}
return $excel;
}
public function getNameFromNumber($num) {
$numeric = $num % 26;
$letter = chr(65 + $numeric);
$num2 = intval($num / 26);
if ($num2 > 0) {
return $this->getNameFromNumber($num2 - 1) . $letter;
} else {
return $letter;
}
}
}
?>