File: //home/cafsindia/.trash/application.1/controllers/Bank_template_setting.php
<?php
/**********************************************************
Filename:Bank Template Setting
Description: Bank Template Setting for creating template for all bank.
Author: Jaffer
Created on: 25 April 2019
Reviewed by:
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Secure_Controller.php");
class Bank_template_setting extends Secure_Controller{
public function __construct(){
parent::__construct('bank_template_setting');
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$data['encKey'] = $this->generateKey();
$data['table_headers']=$this->xss_clean(get_bank_template_setting_headers());
$this->load->view('bank_template_setting/manage',$data);
}
/* ==============================================================*/
/* ================== COMMON OPEARTION - START ==================*/
/* ==============================================================*/
//BANK TEMPLATE 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 = "prime_bank_template_setting_id";
}
if(!$order){
$order = "asc";
}
$this->db->select('prime_bank_template_setting_id,template_name');
$this->db->from('bank_template_setting');
if($search){
$this->db->group_start();
$this->db->like('template_name',$search);
$this->db->group_end();
}
$this->db->where('bank_template_setting.trans_status',1);
$this->db->order_by($sort,$order);
if($rows>0){
$this->db->limit($rows, $limit_from);
}
$template_info = $this->db->get();
$template_details = $template_info->result();
$data_rows = array();
foreach ($template_details as $bank_template_setting){
$data_rows[]=get_bank_template_setting_datarows($bank_template_setting,$this);
}
$data_rows=$this->xss_clean($data_rows);
$num_rows = $template_info->num_rows();
echo json_encode(array('total'=>$num_rows,'rows'=>$data_rows));
}
//BANK TEMPLATE VIEW OPEARTION
public function view($view_id =-1){
$data['view_id'] = $view_id;
$template_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM cw_bank_template_setting where trans_status = 1 and prime_bank_template_setting_id = $view_id')");
$template_result = $template_data->result();
$template_data->next_result();
$data['template_data'] = $template_result[0];
$table_info = $template_result[0]->select_table;
$prime_in = '"'.str_replace(",",'","', $table_info);
$custom_in = str_replace(",",'_cf","', $table_info).'_cf"';
$table_in = $prime_in.'","'.$custom_in;
$data['columns_list'] = $this->get_columns($table_in);
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1')");
$role_result = $role_info->result();
$role_info->next_result();
$template_for_list[""] = "---- Template For ----";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$template_for_list[$role_id] = $category_name;
}
$data['template_for_list'] = $template_for_list;
//table list for all table without setting tables
$table_info = $this->db->query("CALL sp_a_run ('SELECT','SHOW TABLES')");
$table_result = $table_info->result();
$table_info->next_result();
$tab_array = array("cw_app_config","cw_form_bind_input","cw_form_condition_formula","cw_form_for_input","cw_form_setting","cw_form_table_cond_for","cw_form_table_search","cw_form_view_setting","cw_grants","cw_import","cw_main_menu","cw_payroll_formula","cw_payroll_function","cw_permissions","cw_print_block","cw_print_design","cw_print_info","cw_print_map","cw_print_table","cw_print_table_where","cw_sub_menu","cw_month_day","cw_statutory","cw_professional_tax","cw_professional_tax_tax_range","cw_sessions","cw_util_excel_format","cw_session_value","cw_util_excel_format_line","cw_modules","cw_report_setting","cw_salary_check","cw_state","cw_statutory_field","cw_statutory_function","cw_month","cw_category","cw_country","cw_report_add_column","cw_report_table","cw_report_table_view","cw_report_tot_column","cw_report_where","cw_bank_template_setting","cw_bank_template_table","dailyunpunch","monthlyattdata");
$table_list[""] = "---- Select Table ----";
foreach($table_result as $table){
$db_name = "Tables_in_".$this->config->item('db_name');
$table_name = $table->$db_name;
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;
//join table view
$data['join_list'] = $this->get_table_join($view_id);
//table column view
$data['template_tab_view'] = $this->template_table_view($view_id);
$get_colums_info = 'select cw_bank_template_tab_view.table_sort,cw_bank_template_tab_view.prime_bank_template_tab_id,cw_bank_template_tab_view.bank_template_id,cw_bank_template_tab_view.table_column,cw_form_setting.view_name from cw_bank_template_tab_view join cw_form_setting on cw_form_setting.label_name = SUBSTRING_INDEX(SUBSTRING_INDEX(cw_bank_template_tab_view.table_column, ".", 2), ".", -1) WHERE cw_bank_template_tab_view.bank_template_id = "'.$view_id.'" and cw_bank_template_tab_view.trans_status = 1 group by label_name order by abs(cw_bank_template_tab_view.table_sort)';
$colums_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums_info')");
$colums_result = $colums_info->result();
$colums_info->next_result();
$template_column_list[""] = "---- Template Column ----";
foreach($colums_result as $column){
$tab_column = $column->table_column;
$view_name = $column->view_name;
$template_column_list[$tab_column] = $view_name;
}
$data['template_column_list'] = $template_column_list;
//template character setting table view
$data['column_content'] = $this->template_char_tab_view($view_id);
$this->load->view("bank_template_setting/form",$data);
}
//collect table name
public function get_table_column(){
$table_info = ltrim(implode(",",$this->input->post('select_table')),",");
$prime_in = '"'.str_replace(",",'","', $table_info);
$custom_in = str_replace(",",'_cf","', $table_info).'_cf"';
$table_in = $prime_in.'","'.$custom_in;
$table_column = $this->get_columns($table_in);
echo json_encode(array('success' => true,'table_column'=>$table_column));
}
public function get_columns($table_in){
$module_name = str_replace("cw_","",$table_in);
$get_colums = 'select `table_name`,`column_name` from `information_schema`.`columns` where `table_schema`="'.$this->config->item("db_name").'" and `table_name` in ('.$table_in.')';
$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 ----";
foreach($column_result as $column){
$table_value = $column->table_name;
$column_value = $column->column_name;
$view_name_qry = 'select view_name from cw_form_setting where prime_module_id in ('.$module_name.') and label_name = "'.$column_value.'" and trans_status = "1"';
$view_name_data = $this->db->query("CALL sp_a_run ('SELECT','$view_name_qry')");
$view_name_result = $view_name_data->result();
$view_name_data->next_result();
$column_name = strtoupper($view_name_result[0]->view_name);
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_name = strtoupper($table_name);
if($column_name){
$table_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
}
return $table_column;
}
//SAVE BANK TEMPLATE
public function bank_template_save(){
$bank_template_id = $this->input->post('bank_template_id');
$template_name = $this->input->post('template_name');
$template_for = ltrim(implode(",",$this->input->post('template_for[]')),",");
$select_table = ltrim(implode(",",$this->input->post('select_table[]')),",");
$table_column = ltrim(implode(",",$this->input->post('table_column[]')),",");
$sort_column = ltrim(implode(",",$this->input->post('sort_column[]')),",");
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d H:i:s");
if((int)$bank_template_id === 0){
$is_exist_qry = 'select * from cw_bank_template_setting where template_name = "'.$template_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){
$template_qry = 'INSERT INTO cw_bank_template_setting (template_name, template_for,select_table,table_column,sort_column,trans_created_by, trans_created_date) VALUES ("'.$template_name.'","'.$template_for.'","'.$select_table.'","'.$table_column.'","'.$sort_column.'","'.$logged_id.'","'.$date.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$template_qry')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$this->save_table_view($insert_id);
echo json_encode(array('success' => true, 'message' => "Bank template is successfully added"));
}else{
echo json_encode(array('success' => false, 'message' => "Already bank template is exits"));
}
}else{
$upd_qry = 'UPDATE cw_bank_template_setting SET template_name = "'.$template_name.'",template_for = "'.$template_for.'",select_table = "'.$select_table.'",table_column = "'.$table_column.'",sort_column = "'.$sort_column.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_bank_template_setting_id = "'.$bank_template_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$this->save_table_view($bank_template_id);
echo json_encode(array('success' => true, 'message' => "Bank template successfully updated"));
}
}
/* ==============================================================*/
/* ================== COMMON OPEARTION - END ==================*/
/* ==============================================================*/
/* ==============================================================*/
/* ================ TABLE JOIN OPEARTION - START ================*/
/* ==============================================================*/
public function get_table_join($bank_template_id){
$template_qry = 'SELECT * FROM cw_bank_template_setting where prime_bank_template_setting_id = "'.$bank_template_id.'" and trans_status = 1';
$template_data = $this->db->query("CALL sp_a_run ('SELECT','$template_qry')");
$template_rslt = $template_data->result();
$template_data->next_result();
foreach($template_rslt as $rslt){
$bank_template_id = $rslt->prime_bank_template_setting_id;
$table_info = explode(",",$rslt->select_table);
$table_list = array();
$table_list[""] = "---- Select Table ----";
foreach($table_info as $table_value){
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_list[$table_value] = $table_name;
}
$prime_in = '"'.str_replace(",",'","', $rslt->select_table);
$custom_in = str_replace(",",'_cf","', $rslt->select_table).'_cf"';
$table_in = $prime_in.'","'.$custom_in;
$get_colums = 'SELECT `TABLE_NAME`,`COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="'.$this->config->item("db_name").'" AND `TABLE_NAME` IN ('.$table_in.')';
$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;
if(strpos($column_value, 'trans_') !== false) {
//echo "check future reference";
}else{
$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_bank_template_table WHERE join_for = "'.$bank_template_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($table_info) - 1; //round(count($condition_table)/2);
for($i=1;$i<= $condition_table_count;$i++){
$prime_bank_template_table_id = 0;
$line_prime_table = "";
$line_prime_col = "";
$line_join_type = "";
$line_join_table = "";
$line_join_col = "";
if($join_result){
$prime_bank_template_table_id = $join_result[$table_count]->prime_bank_template_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_bank_template_table_id[]",'class' => 'form-control input-sm','value' =>$prime_bank_template_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'=>$bank_template_id,'type'=>'Hidden'));
$table_content = "$join_for
<table class='table table-bordered table-stripted'>
<tr class='inline_head'>
<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;
}
public function save_join_table(){
$join_for = $this->input->post('join_for');
$prime_bank_template_table_id = $this->input->post('prime_bank_template_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_bank_template_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_bank_template_table_id_val = $prime_bank_template_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_bank_template_table_id_val === 0){
$table_query = 'insert into cw_bank_template_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_bank_template_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_bank_template_table_id = "'.$prime_bank_template_table_id_val.'"';
}
$this->db->query("CALL sp_a_run ('RUN','$table_query')");
$tab_count++;
}
$delete_query = 'delete from cw_bank_template_table WHERE trans_status = 0';
$this->db->query("CALL sp_a_run ('RUN','$delete_query')");
echo json_encode(array('success' => true, 'message'=>"Table Join successfully Updated"));
}
/* ==============================================================*/
/* ================ TABLE JOIN OPEARTION - END ================*/
/* ==============================================================*/
//TEMPLATE COLUMN SAVED INDIVIDUALLY IN TABLE VIEW /*SVK EDIT*/
public function save_table_view($bank_template_id){
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d H:i:s");
$get_colums_info = 'select * from cw_bank_template_setting WHERE prime_bank_template_setting_id = "'.$bank_template_id.'" and trans_status = 1';
$colums_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums_info')");
$colums_result = $colums_info->result();
$colums_info->next_result();
$template_name = $colums_result[0]->template_name;
$template_for = $colums_result[0]->template_for;
$table_column = $colums_result[0]->table_column;
$table_column = explode(",",$table_column);
$count = count($table_column);
$exits_count_qry = 'select GROUP_CONCAT(table_column) as colum_name from cw_bank_template_tab_view where bank_template_id = "'.$bank_template_id.'" and trans_status = 1';
$exits_count_info = $this->db->query("CALL sp_a_run ('SELECT','$exits_count_qry')");
$exits_count_result = $exits_count_info->row();
$exits_count_info->next_result();
$exit_col = $exits_count_result->colum_name;
if(!empty($exit_col)){
$exit_col = explode(",",$exit_col);
}else{
$exit_col = array();
}
$check_column = '';
$j = 1;
if(empty($exit_col)){
for($i=0;$i<$count;$i++){
$col_name = $table_column[$i];
$report_qry = 'INSERT INTO cw_bank_template_tab_view (bank_template_id,bank_template_name, template_for,table_column,table_sort,trans_created_by, trans_created_date) VALUES ("'.$bank_template_id.'","'.$template_name.'","'.$template_for.'","'.$col_name.'","'.$j.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$report_qry')");
$j++;
$check_column .= "\"$col_name\",";
}
}else{
for($i=0;$i<$count;$i++){
$col_name = $table_column[$i];
if(!in_array($col_name, $exit_col)){
$exits_count_qry = 'select count(*) as rlst_count from cw_bank_template_tab_view where bank_template_id = "'.$bank_template_id.'" and trans_status = 1';
$exits_count_info = $this->db->query("CALL sp_a_run ('SELECT','$exits_count_qry')");
$exits_count_result = $exits_count_info->row();
$exits_count_info->next_result();
$rlst_count = (int)$exits_count_result->rlst_count + 1;
$report_qry = 'INSERT INTO cw_bank_template_tab_view (bank_template_id,bank_template_name, template_for,table_column,table_sort,trans_created_by, trans_created_date) VALUES ("'.$bank_template_id.'","'.$template_name.'","'.$template_for.'","'.$col_name.'","'.$rlst_count.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$report_qry')");
}
$check_column .= "\"$col_name\",";
}
}
$check_column = rtrim($check_column,',');
$upd_qry = "DELETE FROM `cw_bank_template_tab_view` where bank_template_id = \"$bank_template_id\" and table_column NOT IN ($check_column)";
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$upd_qry = "ALTER TABLE cw_bank_template_tab_view AUTO_INCREMENT = $count";
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
}
//SHOW TABLE VIEW FOR TEMPLATE ID /*SVK EDIT*/
public function template_table_view($bank_template_id){
$get_colums_info = 'select cw_bank_template_tab_view.table_sort,cw_bank_template_tab_view.prime_bank_template_tab_id,cw_bank_template_tab_view.bank_template_id,cw_form_setting.view_name as table_column from cw_bank_template_tab_view join cw_form_setting on cw_form_setting.label_name = SUBSTRING_INDEX(SUBSTRING_INDEX(cw_bank_template_tab_view.table_column, ".", 2), ".", -1) WHERE cw_bank_template_tab_view.bank_template_id = "'.$bank_template_id.'" and cw_bank_template_tab_view.trans_status = 1 group by label_name order by abs(cw_bank_template_tab_view.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 class='inline_topic'><i class='fa fa-hand-rock-o fa-2x' aria-hidden='true'></i> Drag and drop for align field position</p>";
$input_td = "";
$i = 1;
$tr_line = '';
foreach($colums_result as $template){
$sort_id = $template->table_sort;
$bank_template_tab_id = $template->prime_bank_template_tab_id;
$bank_template_id = $template->bank_template_id;
$colum_name = $template->table_column;
$th_id = "th_".$bank_template_tab_id;
$input_th .= "<th class='ui-state-default inner_th' id='$th_id' style='background-color: #4dc147; color:#FFFFFF;cursor:pointer;border-right: 1px solid #CCCCCC;'>
$colum_name
</th>";
$input_td .= "<td style='border-right:1px solid #CCCCCC;'></td>";
if((int)$i === 8)
{
$tr_line .="<tr class='sortable default_table'>$input_th </tr><tr>$input_td</tr>";
$i = 0;
$input_th = '';
$input_td = '';
}
$i++;
}
if($input_th){
$tr_line .="<tr class='sortable default_table' > $input_th </tr><tr>$input_td</tr>";
}
$ul_li = "<table class='table table-hover table-striped' id='template_sortable'>
<tbody>
$tr_line
</tbody>
</table>";
return json_encode(array('table_content' => $ul_li));
}
//GET DEFAULT TABLE UI
public function get_table_view_data(){
$bank_template_id = $this->input->post('bank_template_id');
if($bank_template_id){
echo $this->template_table_view($bank_template_id);
}else{
echo json_encode(array('success' => False,'message' => 'Please Contact Admin..!'));
}
}
//table sort order is update function
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);//prime table id for order update
$upd_qry = 'UPDATE cw_bank_template_tab_view SET table_sort = "'.$sort_order.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_bank_template_tab_id = "'.$table_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
}
}
echo json_encode(array('success' => TRUE, 'message' => "Components order successfully sorted."));
}
//Template setting save function
public function template_setting_save(){
$bank_template_id = $this->input->post('bank_template_id');
$template_id = $this->input->post('template_id');
$template_column = $this->input->post('template_column');
$char_length = $this->input->post('char_length');
$char_space = $this->input->post('char_space');
$char_align = $this->input->post('char_align');
$prefix = $this->input->post('prefix');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d H:i:s");
if((int)$template_id === 0){
if(!$this->check_template_column_already_exists($bank_template_id,$template_column)){
$char_set_qry = 'INSERT INTO cw_bank_template_char_setting (bank_template_id, template_column,char_length,char_space,char_align,prefix,trans_created_by, trans_created_date) VALUES ("'.$bank_template_id.'","'.$template_column.'","'.$char_length.'","'.$char_space.'","'.$char_align.'","'.$prefix.'","'.$logged_id.'","'.$date.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$char_set_qry')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$column_content = $this->template_char_tab_view($bank_template_id);
echo json_encode(array('success' => True, 'message'=>"Successfully added character settings","column_content"=>$column_content));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Template column already exist"));
}
}else{
if(!$this->check_template_column_already_exists($bank_template_id,$template_column,$template_id)){
$upd_qry = 'UPDATE cw_bank_template_char_setting SET bank_template_id = "'.$bank_template_id.'",template_column = "'.$template_column.'",char_length = "'.$char_length.'",char_space = "'.$char_space.'",char_align = "'.$char_align.'",prefix = "'.$prefix.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_bank_template_tab_id = "'.$template_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$column_content = $this->template_char_tab_view($bank_template_id);
echo json_encode(array('success' => true, 'message'=>"Successfully update the character settings","column_content"=>$column_content));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Template column already exist"));
}
}
}
//CHECK TEMPLATE COLUMN ALREADY EXISTS
public function check_template_column_already_exists($bank_template_id,$template_column,$template_id = -1){
$is_exist_qry = 'SELECT * FROM cw_bank_template_char_setting where bank_template_id = "'.$bank_template_id.'" and template_column = "'.$template_column.'" and trans_status = 1 ';
if((int)$template_id > 0){
$is_exist_qry .= " and prime_bank_template_tab_id != $template_id";
}
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_count = $is_exist_data->num_rows();
$is_exist_data->next_result();
if((int)$exist_count > 0){
return TRUE;
}else{
return FALSE;
}
}
//TEMPLATE CHAR TABLE VIEW /*SVK EDIT*/
public function template_char_tab_view($bank_template_id){
$select_qry = 'select cw_bank_template_char_setting.char_length,cw_bank_template_char_setting.prime_bank_template_tab_id,cw_bank_template_char_setting.char_space,cw_bank_template_char_setting.char_align,cw_bank_template_char_setting.prefix,cw_form_setting.view_name as template_column from cw_bank_template_char_setting join cw_form_setting on cw_form_setting.label_name = SUBSTRING_INDEX(SUBSTRING_INDEX(cw_bank_template_char_setting.template_column, ".", 2), ".", -1) WHERE cw_bank_template_char_setting.bank_template_id = "'.$bank_template_id.'" and cw_bank_template_char_setting.trans_status = 1 group by label_name';
$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){
$template_char_id = $rslt->prime_bank_template_tab_id;
$template_column = $rslt->template_column;
$char_length = $rslt->char_length;
$char_space = $rslt->char_space;
$char_align = $rslt->char_align;
$prefix = $rslt->prefix;
if((int)$char_align === 1){
$char_align ="Left";
}else{
$char_align ="Right";
}
if($char_length >= 0){
$char_length = $char_length + 1;
}
if($char_space >= 0){
$char_space = $char_space + 1;
}
$tr_line .= "<tr>
<td>$template_column</td>
<td>$char_length</td>
<td>$char_space</td>
<td>$char_align</td>
<td>$prefix</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=get_char_edit('$template_char_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=remove_char_column('$template_char_id','$bank_template_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$column_content = "<table class='table table-bordered table-stripted' id='template_char_list'>
<thead>
<tr class='inline_head'>
<th style='text-align: center;'>Column Name</th>
<th style='text-align: center;'>Character Length</th>
<th style='text-align: center;'>Character Space</th>
<th style='text-align: center;'>Character Align</th>
<th style='text-align: center;'>Prefix</th>
<th style='text-align: center;'>Edit</th>
<th style='text-align: center;'>Delete</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $column_content;
}
public function get_char_edit(){
$template_char_id = $this->input->post('template_char_id');
$edit_char_qry = 'SELECT * FROM cw_bank_template_char_setting WHERE prime_bank_template_tab_id = "'.$template_char_id.'" and trans_status = 1';
$edit_char_info = $this->db->query("CALL sp_a_run ('SELECT','$edit_char_qry')");
$edit_result = $edit_char_info->result();
$edit_char_info->next_result();
echo json_encode(array('success' => true,'edit_result'=>$edit_result[0]));
}
public function remove_char_column(){
$bank_template_id = $this->input->post('bank_template_id');
$template_char_id = $this->input->post('template_char_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_bank_template_char_setting SET trans_status = 0 ,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_bank_template_tab_id = "'.$template_char_id.'"';
$this->db->query("CALL sp_a_run ('SELECT','$remove_qry')");
$column_content = $this->template_char_tab_view($bank_template_id);
echo json_encode(array('success' => true,'message'=>'Successfully column is deleted!!!','column_content'=>$column_content));
}
}
?>