File: //home/cafsindia/wealth_cafsindia_com/application/controllers/Form_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 Form_setting extends Secure_Controller{
public function __construct(){
parent::__construct('form_setting');
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$data['table_headers']=$this->xss_clean(get_form_setting_headers());
$this->load->view('form_setting/manage',$data);
}
/* ==============================================================*/
/* ================== COMMON OPEARTION - START ==================*/
/* ==============================================================*/
//MODULE 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 = "abs(menu_sort),abs(sort)";
}
if(!$order){
$order = "asc";
}
// 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){
$data['prime_module_id'] = $prime_module_id;
$logged_id = $this->session->userdata('logged_id');
$view_table_data = array(
'prime_view_module_id' => $prime_module_id,
);
$view_table_data = json_encode($view_table_data);
$view_info = $this->db->query("CALL sp_form_view_setting_crud ('VIEW', '$view_table_data',$logged_id)");
$view_setting = $view_info->result();
$view_info->next_result();
$data['view_setting'] = $view_setting;
$data['update_form_viewui'] = $this->update_form_viewui($prime_module_id);
$data['update_table_viewui'] = $this->update_table_viewui($prime_module_id);
$input_for[""] = "---- Input For ----";
foreach($view_setting as $for){
$prime_form_view_id = $for->prime_form_view_id;
$form_view_heading = $for->form_view_heading;
$input_for[$prime_form_view_id] = $form_view_heading;
}
$data['input_for'] = $input_for;
$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();
$field_for[""] = "---- Field For ----";
foreach($role_result as $for){
$role_id = $for->prime_role_id;
$role_name = $for->role_name;
$field_for[$role_id] = $role_name;
}
$data['field_for'] = $field_for;
$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_view_setting","cw_grants","cw_import","cw_import","cw_main_menu","cw_page_setting","cw_people","cw_permissions","cw_sessions","cw_util_excel_format","cw_util_excel_format_line");
$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;
$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_names = "$table_prime,$table_cf,";
$prime_ids = "$table_prime_id,$table_cf_id,";
$view_qry = 'select * from cw_form_view_setting where prime_view_module_id = "'.$prime_module_id.'" and form_view_type = "3" and trans_status = 1';
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_qry')");
$view_result = $view_data->result();
$view_data->next_result();
foreach($view_result as $view){
$form_view_label_name = $view->form_view_label_name;
$table_names .= "cw_".$prime_module_id."_".$form_view_label_name.",";
$prime_ids .= "prime_".$prime_module_id."_".$form_view_label_name."_id,";
}
$table_names = rtrim($table_names,',');
$table_names = '"'.str_replace(",",'","',$table_names).'"';
$prime_ids = rtrim($prime_ids,',');
$prime_ids = '"'.str_replace(",",'","',$prime_ids).'"';
$get_colums = 'SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="cafs_wealth" AND `TABLE_NAME` IN ('.$table_names.') AND COLUMN_NAME NOT LIKE "%trans%" AND COLUMN_NAME NOT IN ('.$prime_ids.')';
$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;
$cond_view = $this->condition_formula_view($prime_module_id);
$cond_content_rslt = json_decode($cond_view);
$cond_content = $cond_content_rslt->cond_content;
$data['cond_content'] = $cond_content;
$cond_table_data = array( 'cond_module_id' => $prime_module_id);
$cond_table_data = json_encode($cond_table_data);
$cond_info = $this->db->query("CALL sp_cond_crud ('VIEW', '$cond_table_data',$logged_id)");
$cond_rslt = $cond_info->result();
$cond_info->next_result();
$add_cond_content[""] = "---- Select Condition / Formula ----";
foreach($cond_rslt as $rslt){
$prime_cond_id = $rslt->prime_cond_id;
$cond_module_id = $rslt->cond_module_id;
$condition_label_name = $rslt->condition_label_name;
$add_cond_content[$prime_cond_id] = $condition_label_name;
}
$data['add_cond_content'] = $add_cond_content;
$this->load->view("form_setting/form",$data);
}
/* ==============================================================*/
/* =================== COMMON OPEARTION - END ===================*/
/* ==============================================================*/
/* ==============================================================*/
/* =================== FORM OPEARTION - START ===================*/
/* ==============================================================*/
//FORM INPUT SAVE OPEARTION
public function save(){
$prime_form_id = $this->input->post('prime_form_id');
$prime_module_id = $this->input->post('prime_module_id');
$mandatory_field = $this->input->post('mandatory_field');
$unique_field = $this->input->post('unique_field');
$field_show = $this->input->post('field_show');
$table_show = $this->input->post('table_show');
$search_show = $this->input->post('search_show');
$field_type = $this->input->post('field_type');
$pick_list_type = $this->input->post('pick_list_type');
$label_name = strtolower(str_replace(" ","_",trim($this->input->post('label_name'))));
$view_name = ucwords(trim($this->input->post('view_name')));
$short_name = trim($this->input->post('short_name'));
$logged_id = $this->session->userdata('logged_id');
$mandatory_value = 0;
$unique_value = 0;
$show_value = 0;
$table_show_val = 0;
$search_show_val = 0;
if($mandatory_field === "on"){ $mandatory_value = 1; }
if($unique_field === "on"){ $unique_value = 1; }
if($field_show === "on"){ $show_value = 1; }
if($table_show === "on"){ $table_show_val = 1; }
if($search_show === "on"){ $search_show_val = 1; }
$input_for = $this->input->post('input_for');
$view_query = 'SELECT form_view_type,form_view_label_name FROM `cw_form_view_setting` where prime_form_view_id = "'.$input_for.'"';
$view_info = $this->db->query("CALL sp_a_run ('SELECT','$view_query')");
$view_result = $view_info->result();
$view_info->next_result();
$input_view_type = $view_result[0]->form_view_type;
$form_view_label_name = $view_result[0]->form_view_label_name;
$table_data = array(
'prime_form_id' => $this->input->post('prime_form_id'),
'prime_module_id' => $this->input->post('prime_module_id'),
'input_view_type' => $input_view_type,
'input_for' => $this->input->post('input_for'),
'field_type' => $this->input->post('field_type'),
'label_name' => $label_name,
'view_name' => $view_name,
'short_name' => $short_name,
'field_length' => $this->input->post('field_length'),
'field_decimals' => $this->input->post('field_decimals'),
'pick_list_type' => $pick_list_type,
'field_isdefault' => $this->input->post('field_isdefault'),
'default_value' => $this->input->post('default_value'),
'mandatory_field' => $mandatory_value,
'unique_field' => $unique_value,
'field_show' => $show_value,
'table_show' => $table_show_val,
'search_show' => $search_show_val,
'field_for' => ltrim(implode(",",$this->input->post('field_for[]')),","),
'file_type' => $this->input->post('file_type'),
);
if((((int)$field_type === 5) && ((int)$pick_list_type === 1)) || (((int)$field_type === 7) && ((int)$pick_list_type === 1))){
$table_data['pick_list'] = ltrim(implode(",",$this->input->post('pick_table_col[]')),",");
$table_data['pick_table'] = $this->input->post('pick_table');
}else
if((((int)$field_type === 5) && ((int)$pick_list_type === 2))|| (((int)$field_type === 7) && ((int)$pick_list_type === 2))){
$table_data['pick_list'] = ltrim(implode(",",$this->input->post('pick_list[]')),",");
$table_data['pick_table'] = $this->input->post('common_table');
}
if((int)$field_type === 9){
$table_data['pick_list'] = ltrim(implode(",",$this->input->post('pick_table_col[]')),",");
$table_data['pick_table'] = $this->input->post('pick_table');
$table_data['auto_prime_id'] = $this->input->post('auto_prime_id');
$table_data['auto_dispaly_value'] = $this->input->post('auto_dispaly_value');
}
$table_data = json_encode($table_data);
if((int)$prime_form_id === 0){
$count_info = $this->db->query("CALL sp_form_setting_crud ('SORT_COUNT', '$table_data','$logged_id')");
$count_result = $count_info->result();
$count_info->next_result();
$table_data = json_decode($table_data,true);
$field_sort = (int)$count_result[0]->sort_count + 1;
$table_data['field_sort'] = $field_sort;
$table_data = json_encode($table_data);
}
$can_process = true;
if((int)$prime_form_id === 0){
$exist_query = 'SELECT count(*) as exist_rslt FROM `cw_form_setting` where prime_module_id = "'.$prime_module_id.'" and label_name = "'.$label_name.'"';
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
if((int)$exist_result[0]->exist_rslt !== 0){
$can_process = false;
}
}
if($can_process){
$info = $this->db->query("CALL sp_form_setting_crud ('SAVE', '$table_data','$logged_id')");
$result = $info->result();
$info->next_result();
$form_setting = $this->update_form_viewui($prime_module_id);
$table_setting = $this->update_table_viewui($prime_module_id);
if($this->save_table($prime_module_id,$input_view_type,$input_for,$form_view_label_name)){
echo json_encode(array('success' => TRUE,'form_setting'=>$form_setting,'table_setting'=>$table_setting,'msg' => "Input added Successfully"));
}else{
echo json_encode(array('success' => false,'msg'=>"Unable to process your request"));
}
}else{
echo json_encode(array('success' => false, 'frm'=>'exist','msg' => "Input name already exist"));
}
}
//FORM INPUT GET OPEARTION FOR EDIT
public function get_field_info(){
$table_data = array(
'prime_module_id' => $this->input->post('prime_module_id'),
'prime_form_id' => $this->input->post('prime_form_id'),
);
$table_data = json_encode($table_data);
$info = $this->db->query("CALL sp_form_setting_crud ('EDIT', '$table_data',null)");
$result = $info->result();
$info->next_result();
$colums_list[] = array("key"=>"","value"=>"-- Pick list value ---");
$field_type = $result[0]->field_type;
$pick_list_type = $result[0]->pick_list_type;
$pick_table = $result[0]->pick_table;
if(((int)$pick_list_type === 1) || ((int)$field_type === 9)){
$get_colums = 'SELECT COLUMN_NAME AS col_name FROM information_schema.COLUMNS WHERE `TABLE_SCHEMA`="cafs_wealth" AND TABLE_NAME = "'.$pick_table.'" and COLUMN_NAME not like "%trans%"';
$colums_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$colums_result = $colums_info->result();
$colums_info->next_result();
foreach($colums_result as $colums){
$key = $colums->col_name;
$value = ucwords(str_replace("_"," ",$colums->col_name));
$colums_list[] = array("key"=>"$key","value"=>"$value");
}
}
echo json_encode(array('success' => TRUE, 'field_info' => $result[0], 'colums_list' => $colums_list ,'field_type' => $field_type ,'pick_list_type' => $pick_list_type));
}
//FORM INPUT ONCHANGE OPERATION
public function get_table_info(){
$pick_table = $this->input->post('pick_table');
$get_colums = 'SELECT COLUMN_NAME AS col_name FROM information_schema.COLUMNS WHERE `TABLE_SCHEMA`="cafs_wealth" AND TABLE_NAME = "'.$pick_table.'" and COLUMN_NAME not like "%trans%"';
$colums_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$colums_result = $colums_info->result();
$colums_info->next_result();
$colums_list[] = array("key"=>"","value"=>"-- Pick list value ---");
foreach($colums_result as $colums){
$key = $colums->col_name;
$value = ucwords(str_replace("_"," ",$colums->col_name));
$colums_list[] = array("key"=>"$key","value"=>"$value");
}
echo json_encode(array('success' => TRUE, 'colums_list' => $colums_list));
}
//FORM INPUT SORT OPEARTION
public function update_sortorder(){
$idsInOrder = $this->input->post('idsInOrder');
$id_info = $this->input->post('id_info');
$logged_id = $this->session->userdata('logged_id');
$id_info = explode("-",$id_info);
$prime_module_id = $id_info[0];
$input_for = $id_info[1];
$sort_order = 0;
foreach($idsInOrder as $order){
if($order){
$sort_order++;
$order = explode("_",$order);
$prime_form_id = $order[1];
$table_data = array(
'prime_form_id' => $prime_form_id,
'prime_module_id' => $prime_module_id,
'input_for' => $input_for,
'sort_order' => $sort_order,
);
$table_data = json_encode($table_data);
$info = $this->db->query("CALL sp_form_setting_crud ('UPD_SORT', '$table_data','$logged_id')");
$result = $info->result();
$info->next_result();
}
}
echo json_encode(array('success' => TRUE, 'message' => "Sort position updated to database"));
}
//TABLE SORT OPEARTION
public function update_table_sortorder(){
$table_idsInOrder = $this->input->post('table_idsInOrder');
$prime_module_id = $this->input->post('prime_module_id');
$logged_id = $this->session->userdata('logged_id');
$sort_order = 0;
$upd_query = 'UPDATE cw_form_setting SET table_sort = 0 WHERE prime_module_id = "'. $prime_module_id .'"';
$info = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
$info->next_result();
foreach($table_idsInOrder as $order){
if($order){
$sort_order++;
$order = explode("_",$order);
$prime_form_id = $order[1];
$table_data = array(
'prime_form_id' => $prime_form_id,
'prime_module_id' => $prime_module_id,
'table_sort' => $sort_order,
);
$table_data = json_encode($table_data);
$info = $this->db->query("CALL sp_form_setting_crud ('UPD_TABLE_SORT', '$table_data','$logged_id')");
$result = $info->result();
$info->next_result();
}
}
echo json_encode(array('success' => TRUE, 'message' => "Sort position updated to database"));
}
public function update_form_viewui($prime_module_id){
$view_table_data = array( 'prime_view_module_id' => $prime_module_id,);
$view_table_data = json_encode($view_table_data);
$logged_id = $this->session->userdata('logged_id');
$view_info = $this->db->query("CALL sp_form_view_setting_crud ('VIEW', '$view_table_data',$logged_id)");
$view_setting = $view_info->result();
$view_info->next_result();
$view_content = "<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>";
$count = 0;
$id_array = array();
$view_input_count = 0;
foreach($view_setting as $view){
$prime_form_view_id = $view->prime_form_view_id;
$prime_view_module_id = $view->prime_view_module_id;
$form_view_type = (int)$view->form_view_type;
$form_view_label_name = $view->form_view_label_name;
$form_view_heading = ucwords($view->form_view_heading);
$table_data = array(
'prime_module_id' => $prime_module_id,
'input_for' => $prime_form_view_id,
);
$table_data = json_encode($table_data);
$input_info = $this->db->query("CALL sp_form_setting_crud ('VIEW_INPUT', '$table_data',$logged_id)");
$input_result = $input_info->result();
$view_input_count += $input_info->num_rows();
$input_info->next_result();
$input_li = "";
$input_count = 0;
$field_type_array = array(1=>"Text",2=>"Decimals",3=>"Integer",4=>"Date",5=>"Picklist",6=>"Checkbox",7=>"Multi Picklist",8=>"summary box",9=>"Auto complete box",10=>"File upload box");
foreach($input_result as $setting){
$input_count++;
$prime_form_id = $setting->prime_form_id;
$field_type = $setting->field_type;
$view_name = ucwords($setting->view_name);
$short_name = $setting->short_name;
$mandatory_field = $setting->mandatory_field;
$unique_field = $setting->unique_field;
$field_show = $setting->field_show;
$table_show = $setting->table_show;
$search_show = $setting->search_show;
$field_for = $setting->field_for;
$li_id = "li_".$prime_form_id;
$a_id = "a_".$prime_form_id."_$input_count";
$field_type_name = $field_type_array[$field_type];
$mandatory = "";
if((int)$mandatory_field === 1){
$mandatory = "required";
}
$show_icon = "<i class='fa fa-eye-slash' aria-hidden='true'></i>";
if((int)$field_show === 1){
$show_icon = "<i class='fa fa-eye' aria-hidden='true'></i>";
}
$table_icon = "";
if((int)$table_show === 1){
$table_icon = "<i class='fa fa-table' aria-hidden='true'></i>";
}
$search_icon = "";
if((int)$search_show === 1){
$search_icon = "<i class='fa fa-filter' aria-hidden='true'></i>";
}
$unique_icon = "";
if((int)$unique_field === 1){
$unique_icon = "<i class='fa fa-key' aria-hidden='true'></i>";
}
$input_li .= "<li class='ui-state-default' id='$li_id'>
<table style='width:100%;'>
<tr>
<td style='font-weight:bold'>
<label class='$mandatory'>$view_name</label><br/>
<span style='font-size:13px;font-weight:normal;color:#999999;'>
$field_type_name $show_icon $unique_icon $search_icon $table_icon
</span>
</td>
<td style='text-align:right;'>
<a id='$a_id' style='color:#1883E9;' onclick=get_field_info('$prime_form_id','$a_id');><i class='fa fa-pencil-square-o fa-2x' aria-hidden='true'></i></a>
</td>
</tr>
</table>
</li>";
}
$ui_id = $prime_module_id."-".$prime_form_view_id;
$id_array[] = $ui_id;
$ul_li = "<ul id='$ui_id' class='sortable'>$input_li</ul>";
if($form_view_type === 1){
$view_content .= "<div id='$form_view_label_name' style='font-size: inherit; box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2); background-color: #FFFFFF; border: 0px; border-radius: 2px;padding:8px;margin-bottom:10px;'>
<h4 style='color:#1883E9;'>$form_view_heading</h4>
$ul_li
</div>";
}else
if($form_view_type === 2){
$count++;
$tab_active = "";
$content_active = "";
if((int)$count === 1){
$tab_active = "active";
$content_active = "in active";
$view_content .= "<div style='font-size: inherit; box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2); background-color: #FFFFFF; border: 0px; border-radius: 2px;margin-bottom:10px;'>
<ul class='nav nav-tabs' data-tabs='tabs'>
@TABLI
</ul>
<div class='tab-content'>
@TABCONTENT
</div>
</div>";
}
$tab_li .= "<li role='presentation' class='$tab_active'>
<a data-toggle='tab' href='#$form_view_label_name'>$form_view_heading</a>
</li>";
$tab_content .= "<div class='tab-pane fade $content_active' id='$form_view_label_name' style='padding:8px;'>
<h4 style='color:#1883E9;'>$form_view_heading</h4>
$ul_li
</div>";
}else
if($form_view_type === 3){
$view_content .= "<div id='$form_view_label_name' style='font-size: inherit; box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2); background-color: #FFFFFF; border: 0px; border-radius: 2px;padding:8px;margin-bottom:10px;'>
<h4 style='color:#1883E9;'>$form_view_heading</h4>
$ul_li
</div>";
}
}
$ul_li = "";
$view_content = str_replace("@TABLI",$tab_li,$view_content);
$view_content = str_replace("@TABCONTENT",$tab_content,$view_content);
return json_encode(array('success' => TRUE,'view_content' => $view_content,'id_array'=>$id_array,'view_input_count'=>$view_input_count));
}
public function update_table_viewui($prime_module_id){
$logged_id = $this->session->userdata('logged_id');
$table_data = array( 'prime_module_id' => $prime_module_id);
$table_data = json_encode($table_data);
$input_info = $this->db->query("CALL sp_form_setting_crud ('TAB_VIEW', '$table_data',$logged_id)");
$input_result = $input_info->result();
$input_info->next_result();
$input_count = 0;
$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($input_result as $setting){
$input_count++;
$prime_form_id = $setting->prime_form_id;
$view_name = ucwords($setting->view_name);
$short_name = $setting->short_name;
$table_show = $setting->table_show;
$th_id = "th_".$prime_form_id;
$input_th .= "<th class='ui-state-default' id='$th_id' style='background-color: #1883E9; color:#FFFFFF;cursor:pointer;border-right: 1px solid #CCCCCC;'>
$view_name
</th>";
$input_td .= "<td style='border-right:1px solid #CCCCCC;'>-</td>";
}
$ul_li = "<table class='table table-hover table-striped'>
<tr id='table_sortable' class='sortable'>
$input_th
</tr>
<tr>
$input_td
</tr>
<tr>
$input_td
</tr>
</table>";
return json_encode(array('success' => TRUE,'table_content' => $ul_li));
}
// ,table_show
/* ==============================================================*/
/* ==================== FORM OPEARTION - END ====================*/
/* ==============================================================*/
/* ==============================================================*/
/* =================== VIEW OPEARTION - START ===================*/
/* ==============================================================*/
//FORM VIEW SAVE OPEARTION
public function add_ui(){
$logged_id = $this->session->userdata('logged_id');
$prime_form_view_id = $this->input->post('prime_form_view_id');
$form_view_show = $this->input->post('form_view_show');
$prime_view_module_id = trim($this->input->post('prime_view_module_id'));
$form_view_label_name = strtolower(str_replace(" ","_",trim($this->input->post('form_view_label_name'))));
$show_value = 0;
if($form_view_show === "on"){
$show_value = 1;
}
$table_data = array(
'prime_form_view_id' => $this->input->post('prime_form_view_id'),
'prime_view_module_id' => $prime_view_module_id,
'form_view_type' => $this->input->post('form_view_type'),
'form_view_type_mode' => $this->input->post('form_view_type_mode'),
'form_view_label_name' => $form_view_label_name,
'form_view_heading' => $this->input->post('form_view_heading'),
'form_view_for' => ltrim(implode(",",$this->input->post('form_view_for[]')),","),
'form_view_show' => $show_value,
);
$table_data = json_encode($table_data);
if((int)$prime_form_view_id === 0){
$count_info = $this->db->query("CALL sp_form_view_setting_crud ('SORT_COUNT', '$table_data','$logged_id')");
$count_result = $count_info->result();
$count_info->next_result();
$table_data = json_decode($table_data,true);
$field_sort = (int)$count_result[0]->sort_count + 1;
$table_data['form_view_sort'] = $field_sort;
$table_data = json_encode($table_data);
}
$table_data = json_encode($table_data);
$can_process = true;
if((int)$prime_form_view_id === 0){
$viewexist_query = 'SELECT count(*) as exist_rslt FROM `cw_form_view_setting` where prime_view_module_id = "'.$prime_view_module_id.'" and form_view_label_name = "'.$form_view_label_name.'"';
$viewexist_info = $this->db->query("CALL sp_a_run ('SELECT','$viewexist_query')");
$viewexist_result = $viewexist_info->result();
$viewexist_info->next_result();
if((int)$viewexist_result[0]->exist_rslt !== 0){
$can_process = false;
}
}
if($can_process){
$info = $this->db->query("CALL sp_form_view_setting_crud ('SAVE', '$table_data','$logged_id')");
$result = $info->result();
$info->next_result();
$view_info = $this->db->query("CALL sp_form_view_setting_crud ('VIEW', '$table_data',$logged_id)");
$view_setting = $view_info->result();
$view_info->next_result();
$li_list = "";
$form_view_type_array = array(""=>"---- Form view type ----",1=>"Block",2=>"Tab");
$count = 0;
foreach($view_setting as $setting){
$count++;
$prime_form_view_id = $setting->prime_form_view_id;
$prime_view_module_id = $setting->prime_view_module_id;
$form_view_type = $setting->form_view_type;
$form_view_label_name = $setting->form_view_label_name;
$form_view_heading = $setting->form_view_heading;
$form_view_sort = $setting->form_view_sort;
$form_view_show = $setting->form_view_show;
$form_view_for = $setting->form_view_for;
$form_view_type = $form_view_type_array[$form_view_type];
$li_id = "li_".$prime_form_view_id;
$a_id = "a_".$prime_form_view_id."_$count";
$show_icon = "<i class='fa fa-eye-slash' aria-hidden='true'></i>";
if((int)$form_view_show === 1){
$show_icon = "<i class='fa fa-eye' aria-hidden='true'></i>";
}
$li_list .= "<li class='ui-state-default' id='$li_id'>
<table style='width:100%;'>
<tr>
<td style='font-weight:bold'>
<label>$form_view_heading</label><br/>
<span style='font-size:13px;font-weight:normal;color:#999999;'> $show_icon $form_view_type </span>
</td>
<td style='text-align:right;'>
<a id='$a_id' style='color:#1883E9;' onclick=get_view_info('$prime_form_view_id','$a_id');><i class='fa fa-pencil-square-o fa-2x' aria-hidden='true'></i></a>
</td>
</tr>
</table>
</li>";
/* UDY ONLY FOR FORM WITH TABLE DB CREATION - START*/
$form_view_type = (int)$this->input->post('form_view_type');
if($form_view_type === 3){
$prime_view_module_id = trim($this->input->post('prime_view_module_id'));
$row_table_name = strtolower(str_replace(" ","_",trim($this->input->post('form_view_label_name'))));
$prime_id = "prime_".$prime_view_module_id."_".$row_table_name."_id";
$module_id = "prime_".$prime_view_module_id."_id";
$row_table_name = $this->db->dbprefix($prime_view_module_id."_".$row_table_name);
$prime_line = "$prime_id int(11) NOT NULL AUTO_INCREMENT,$module_id INT(11) NULL DEFAULT '0',trans_created_by INT(11) NULL DEFAULT '0', trans_created_date DATETIME DEFAULT NULL, trans_updated_by INT(11) NULL DEFAULT '0', trans_updated_date DATETIME NULL DEFAULT NULL, trans_deleted_by INT(11) NULL DEFAULT '0', trans_deleted_date DATETIME NULL DEFAULT NULL,trans_status INT(11) NULL DEFAULT '1',PRIMARY KEY (`$prime_id`)";
$prime_table_query = "CREATE TABLE IF NOT EXISTS $row_table_name($prime_line)";
$this->db->query($prime_table_query);
}
/* UDY ONLY FOR FORM WITH TABLE DB CREATION - START*/
}
$view_data = "<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><ul id='view_sortable' class='sortable'>$li_list</ul>";
echo json_encode(array('success' => TRUE, 'view_setting' => $view_data, 'msg' => "View setting successfully added"));
}else{
echo json_encode(array('success' => false, 'msg' => "View already exist for this module"));
}
}
// VIEW SORT ORDER UPDATE
public function update_view_sortorder(){
$view_idsInOrder = $this->input->post('view_idsInOrder');
$prime_view_module_id = $this->input->post('prime_view_module_id');
$logged_id = $this->session->userdata('logged_id');
$sort_order = 0;
foreach($view_idsInOrder as $order){
if($order){
$sort_order++;
$order = explode("_",$order);
$prime_form_view_id = $order[1];
$table_data = array(
'prime_form_view_id' => $prime_form_view_id,
'prime_view_module_id' => $prime_view_module_id,
'form_view_sort' => $sort_order,
);
$table_data = json_encode($table_data);
$info = $this->db->query("CALL sp_form_view_setting_crud ('UPD_SORT', '$table_data','$logged_id')");
$result = $info->result();
$info->next_result();
}
}
echo json_encode(array('success' => TRUE, 'message' => "Sort position updated to database"));
}
//VIEW FORM EDIT OPERATION
public function get_view_info(){
$table_data = array(
'prime_view_module_id' => $this->input->post('prime_view_module_id'),
'prime_form_view_id' => $this->input->post('prime_form_view_id'),
);
$table_data = json_encode($table_data);
$info = $this->db->query("CALL sp_form_view_setting_crud ('EDIT', '$table_data',null)");
$result = $info->result();
$info->next_result();
echo json_encode(array('success' => TRUE, 'view_info' => $result[0]));
}
/* ==============================================================*/
/* ==================== VIEW OPEARTION - END ====================*/
/* ==============================================================*/
/* ==============================================================*/
/* ==================== FORMULA TBALE - START ===================*/
/* ==============================================================*/
public function condition_formula(){
$logged_id = $this->session->userdata('logged_id');
$cond_module_id = $this->input->post('cond_module_id');
$is_drop_down = $this->input->post('is_drop_down');
$prime_cond_id = (int)$this->input->post('prime_cond_id');
$condition_label_name = $this->input->post('condition_label_name');
$is_drop_down_value = 0;
if($is_drop_down === "on"){
$is_drop_down_value = 1;
}
$table_data = array(
'prime_cond_id' => $prime_cond_id,
'cond_module_id' => $cond_module_id,
'condition_label_name' => $condition_label_name,
'condition_type' => $this->input->post('condition_type'),
'condition_for' => ltrim(implode(",",$this->input->post('condition_for[]')),","),
'condition_check_form' => ltrim(implode(",",$this->input->post('condition_check_form[]')),","),
'condition_bind_to' => ltrim(implode(",",$this->input->post('condition_bind_to[]')),","),
'condition_table' => ltrim(implode(",",$this->input->post('condition_table[]')),","),
'is_drop_down' => $is_drop_down_value,
'cond_drop_down' => $this->input->post('cond_drop_down'),
);
$table_data = json_encode($table_data);
$can_process = true;
if((int)$prime_cond_id === 0){
$exist_query = 'SELECT count(*) as exist_rslt FROM `cw_form_condition_formula` where cond_module_id = "'.$cond_module_id.'" and condition_label_name = "'.$condition_label_name.'"';
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
if((int)$exist_result[0]->exist_rslt !== 0){
$can_process = false;
}
}
if($can_process){
$info = $this->db->query("CALL sp_cond_crud ('SAVE', '$table_data','$logged_id')");
$result = $info->result();
$info->next_result();
if((int)$prime_cond_id === 0){
$prime_cond_id = $result[0]->ins_id;
}
$from_remove = ltrim(implode(",",$this->input->post('condition_check_form[]')),",");
$from_remove = '"'.str_replace(",",'","',$from_remove).'"';
$from_query = 'UPDATE cw_form_for_input SET trans_status = 0 , trans_deleted_by = "'.$logged_id.'",trans_deleted_date = DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%S") WHERE input_for_cond_id = "'.$prime_cond_id.'" and input_for_cond_module_id = "'.$cond_module_id.'" and line_input_for NOT IN('.$from_remove.')';
$this->db->query("CALL sp_a_run ('RUN','$from_query')");
$to_remove = ltrim(implode(",",$this->input->post('condition_bind_to[]')),",");
$to_remove = '"'.str_replace(",",'","',$to_remove).'"';
$to_query = 'UPDATE cw_form_bind_input SET trans_status = 0 , trans_deleted_by = "'.$logged_id.'",trans_deleted_date = DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%S") WHERE input_cond_id = "'.$prime_cond_id.'" and input_cond_module_id = "'.$cond_module_id.'" and line_input_bind_to NOT IN('.$to_remove.')';
$this->db->query("CALL sp_a_run ('RUN','$to_query')");
$cond_view = $this->condition_formula_view($cond_module_id);
$cond_content_rslt = json_decode($cond_view);
$cond_content = $cond_content_rslt->cond_content;
echo json_encode(array('success' => TRUE,'cond_content' => $cond_content,'msg'=>"Condition & Formula added successfully"));
}else{
echo json_encode(array('success' =>false,'msg'=>"Condition & Formula name already exist"));
}
}
public function get_cond_info(){
$logged_id = $this->session->userdata('logged_id');
$table_data = array(
'prime_cond_id' => (int)$this->input->post('prime_cond_id'),
'cond_module_id' => $this->input->post('cond_module_id'),
);
$table_data = json_encode($table_data);
$info = $this->db->query("CALL sp_cond_crud ('EDIT', '$table_data','$logged_id')");
$result = $info->result();
$info->next_result();
echo json_encode(array('success' => TRUE,'cond_info' => $result[0]));
}
public function remove_cond(){
$logged_id = $this->session->userdata('logged_id');
$cond_module_id = $this->input->post('cond_module_id');
$table_data = array(
'prime_cond_id' => (int)$this->input->post('prime_cond_id'),
'cond_module_id' => $this->input->post('cond_module_id'),
);
$table_data = json_encode($table_data);
$info = $this->db->query("CALL sp_cond_crud ('REMOVE', '$table_data','$logged_id')");
$cond_view = $this->condition_formula_view($cond_module_id);
$cond_content_rslt = json_decode($cond_view);
$cond_content = $cond_content_rslt->cond_content;
echo json_encode(array('success' => TRUE,'cond_content' => $cond_content));
}
public function condition_formula_view($prime_module_id){
$logged_id = $this->session->userdata('logged_id');
$cond_table_data = array( 'cond_module_id' => $prime_module_id);
$cond_table_data = json_encode($cond_table_data);
$cond_info = $this->db->query("CALL sp_cond_crud ('VIEW', '$cond_table_data',$logged_id)");
$cond_rslt = $cond_info->result();
$cond_info->next_result();
$tr_line = "";
foreach($cond_rslt as $rslt){
$prime_cond_id = $rslt->prime_cond_id;
$cond_module_id = $rslt->cond_module_id;
$condition_label_name = $rslt->condition_label_name;
$tr_line .= "<tr>
<td>$condition_label_name</td>
<td><a class='btn btn-xs btn-edit' onclick=get_cond_info('$prime_cond_id')> <i class='fa fa-pencil-square-o' aria-hidden='true'></i> Edit</a></td>
<td><a class='btn btn-xs btn-danger' onclick=remove_cond('$prime_cond_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$cond_content = "<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th>Condition / Formula Name</th>
<th>Edit</th>
<th>Delete</th>
</tr>
$tr_line
</table>";
return json_encode(array('success' => TRUE,'cond_content' => $cond_content));
}
public function get_add_cond_info(){
$logged_id = $this->session->userdata('logged_id');
$table_data = array(
'prime_cond_id' => (int)$this->input->post('add_cond_content'),
'cond_module_id' => $this->input->post('add_cond_module_id'),
);
$table_data = json_encode($table_data);
$info = $this->db->query("CALL sp_cond_crud ('EDIT', '$table_data','$logged_id')");
$result = $info->result();
$info->next_result();
$prime_cond_id = $result[0]->prime_cond_id;
$cond_module_id = $result[0]->cond_module_id;
$condition_label_name = $result[0]->condition_label_name;
$condition_type = (int)$result[0]->condition_type;
$condition_for = explode(",",$result[0]->condition_for);
$condition_check_form = explode(",",$result[0]->condition_check_form);
$condition_bind_to = explode(",",$result[0]->condition_bind_to);
$condition_table = explode(",",$result[0]->condition_table);
$is_drop_down = (int)$result[0]->is_drop_down;
$cond_drop_down = $result[0]->cond_drop_down;
$line_prime_cond_id = form_input(array( 'name' =>'line_prime_cond_id','id' =>'line_prime_cond_id', 'class' => 'form-control input-sm','value' =>$prime_cond_id,'type'=>'Hidden'));
$line_cond_module_id = form_input(array( 'name' =>'line_cond_module_id','id' =>'line_cond_module_id', 'class' => 'form-control input-sm','value' =>$cond_module_id,'type'=>'Hidden'));
$line_cond_type = form_input(array( 'name' =>'line_cond_type','id' =>'line_cond_type', 'class' => 'form-control input-sm','value' =>$condition_type,'type'=>'Hidden'));
$final_content = "";
//BIND FORM TABLE
if($condition_type === 1){
$table_list[""] = "---- Select Table ----";
foreach($condition_table as $table_value){
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_list[$table_value] = $table_name;
}
$prime_in = '"'.str_replace(",",'","', $result[0]->condition_table);
$custom_in = str_replace(",",'_cf","', $result[0]->condition_table).'_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[""] = "---- 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;
}
$condition_tab_query = 'SELECT * FROM cw_form_table_cond_for WHERE table_cond_id = "'.$prime_cond_id.'" AND table_cond_module_id = "'.$cond_module_id.'" order by abs(line_sort)';
$condition_tab_info = $this->db->query("CALL sp_a_run ('SELECT','$condition_tab_query')");
$condition_tab_result = $condition_tab_info->result();
$condition_tab_info->next_result();
$join_array = array(""=>"--- Select join type ---","inner" => "inner","left" => "left","right" => "right");
$table_content = "";
$table_tr_line = "";
$table_count = 0;
$condition_table_count = count($condition_table) - 1; //round(count($condition_table)/2);
for($i=1;$i<= $condition_table_count;$i++){
$prime_table_cond_for_id = 0;
$line_prime_table = "";
$line_prime_col = "";
$line_join_type = "";
$line_join_table = "";
$line_join_col = "";
if($condition_tab_result){
$prime_table_cond_for_id = $condition_tab_result[$table_count]->prime_table_cond_for_id;
$line_prime_table = $condition_tab_result[$table_count]->line_prime_table;
$line_prime_col = $condition_tab_result[$table_count]->line_prime_col;
$line_join_type = $condition_tab_result[$table_count]->line_join_type;
$line_join_table = $condition_tab_result[$table_count]->line_join_table;
$line_join_col = $condition_tab_result[$table_count]->line_join_col;
}
$table_cond_for_id = form_input(array( 'name' =>"prime_table_cond_for_id[]",'class' => 'form-control input-sm','value' =>$prime_table_cond_for_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++;
}
$table_content = "<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>";
$for_input_content = "";
$for_input_tr_line = "";
foreach($condition_check_form as $check_form){
$condition_for_input_query = 'SELECT * FROM cw_form_for_input WHERE input_for_cond_id = "'.$prime_cond_id.'" AND input_for_cond_module_id = "'.$cond_module_id.'" AND line_input_for = "'.$check_form.'"';
$condition_for_input_info = $this->db->query("CALL sp_a_run ('SELECT','$condition_for_input_query')");
$condition_for_input_result = $condition_for_input_info->result();
$condition_for_input_info->next_result();
$prime_for_input_id = 0;
$line_input_for_table = "";
$line_input_for_col = "";
if($condition_for_input_result){
$prime_for_input_id = $condition_for_input_result[0]->prime_for_input_id;
$line_input_for_table = $condition_for_input_result[0]->line_input_for_table;
$line_input_for_col = $condition_for_input_result[0]->line_input_for_col;
}
$for_col_name = ucwords(str_replace("_"," ",$check_form));
$input_for_id = form_input(array( 'name' =>"prime_for_input_id[]",'class' => 'form-control input-sm','value' =>$prime_for_input_id,'type'=>'Hidden'));
$line_for_col = form_input(array( 'name' =>"line_input_for[]",'class' => 'form-control input-sm','value' =>$check_form,'type'=>'Hidden'));
$for_table_data = form_dropdown(array('name' =>"line_input_for_table[]",'class' => 'form-control input-sm'), $table_list,$line_input_for_table);
$for_col_data = form_dropdown(array('name' =>"line_input_for_col[]",'class' => 'form-control input-sm'),$column_list,$line_input_for_col);
$for_input_tr_line .= "<tr>
<td>$for_col_name $line_for_col $input_for_id</td>
<td>$for_table_data</td>
<td>$for_col_data</td>
</tr>";
}
$for_input_content = "<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th>From input</th>
<th>From table</th>
<th>From table column</th>
</tr>
$for_input_tr_line
</table>";
$input_content = "";
$input_tr_line = "";
foreach($condition_bind_to as $bind_col){
$condition_input_query = 'SELECT * FROM cw_form_bind_input WHERE input_cond_id = "'.$prime_cond_id.'" AND input_cond_module_id = "'.$cond_module_id.'" AND line_input_bind_to = "'.$bind_col.'"';
$condition_input_info = $this->db->query("CALL sp_a_run ('SELECT','$condition_input_query')");
$condition_input_result = $condition_input_info->result();
$condition_input_info->next_result();
$prime_input_cond_for_id = 0;
$line_input_bind_table = "";
$line_input_bind_col = "";
if($condition_input_result){
$prime_input_cond_for_id = $condition_input_result[0]->prime_input_cond_for_id;
$line_input_bind_table = $condition_input_result[0]->line_input_bind_table;
$line_input_bind_col = $condition_input_result[0]->line_input_bind_col;
}
$bind_col_name = ucwords(str_replace("_"," ",$bind_col));
$input_cond_for_id = form_input(array( 'name' =>"prime_input_cond_for_id[]",'class' => 'form-control input-sm','value' =>$prime_input_cond_for_id,'type'=>'Hidden'));
$line_bind_col = form_input(array( 'name' =>"line_input_bind_to[]",'class' => 'form-control input-sm','value' =>$bind_col,'type'=>'Hidden'));
$table_data = form_dropdown(array('name' =>"line_input_bind_table[]",'class' => 'form-control input-sm'), $table_list,$line_input_bind_table);
$col_data = form_dropdown(array('name' =>"line_input_bind_col[]",'class' => 'form-control input-sm'),$column_list,$line_input_bind_col);
$input_tr_line .= "<tr>
<td>$bind_col_name $line_bind_col $input_cond_for_id $line_cond_type</td>
<td>$table_data</td>
<td>$col_data</td>
</tr>";
}
$input_content = "<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th>Bind input</th>
<th>From table</th>
<th>From table column</th>
</tr>
$input_tr_line
</table>";
$final_content = "$line_prime_cond_id $line_cond_module_id
<ul class='nav nav-tabs' data-tabs='tabs'>
<li class='active' role='presentation'>
<a data-toggle='tab' href='#table_details'>Table details</a>
</li>
<li role='presentation'>
<a data-toggle='tab' href='#for_input_details'>From input details</a>
</li>
<li role='presentation'>
<a data-toggle='tab' href='#bind_input_details'>Bind input details</a>
</li>
</ul>
<div class='tab-content' style='padding:20px 15px;'>
<div class='tab-pane fade in active' id='table_details'>
$table_content
</div>
<div class='tab-pane fade' id='for_input_details'>
$for_input_content
</div>
<div class='tab-pane fade' id='bind_input_details'>
$input_content
</div>
</div>
<div style='text-align:right;padding: 20px 15px;padding-top:0px;'>
<button class='btn btn-primary btn-sm' id='add_cond_submit'>Submit</button>
</div>";
}else
//ONLY CONDITION
if($condition_type === 2){
//CONDITION BASED ON DROP DOWN
$drop_tr_line = "";
$drop_input_content = "";
$con_column_list[""] = "---- Select column ----";
foreach($condition_check_form as $check_form){
$check_form_label = ucwords(str_replace("_"," ",$check_form));
$con_column_list[$check_form] = $check_form_label;
}
$drop_count = 0;
$drop_line_count = 0;
if($is_drop_down === 1){
$drop_down_list = $this->get_drop_down_info($cond_drop_down,$cond_module_id);
foreach($drop_down_list as $key=>$value){
if($key){
$drop_count++;
$cond_drop_down = form_dropdown(array('name' =>"cond_drop_down[]",'class' => 'form-control input-sm'),$drop_down_list,$key);
$drop_in_line = "";
foreach($condition_bind_to as $bind_col){
$drop_line_count++;
$con_column_input = "con_column_input_".$drop_line_count;
$line_input_bind_cond = "line_input_bind_col_".$drop_line_count;
$condition_input_query = 'SELECT * FROM cw_form_bind_input WHERE input_cond_id = "'.$prime_cond_id.'" AND input_cond_module_id = "'.$cond_module_id.'" AND line_input_bind_to = "'.$bind_col.'"AND line_input_bind_table = "'.$key.'"';
$condition_input_info = $this->db->query("CALL sp_a_run ('SELECT','$condition_input_query')");
$condition_input_result = $condition_input_info->result();
$condition_input_info->next_result();
$prime_input_cond_for_id = 0;
$line_input_bind_table = "";
$line_input_bind_col = "";
if($condition_input_result){
$prime_input_cond_for_id = $condition_input_result[0]->prime_input_cond_for_id;
$line_input_bind_table = $condition_input_result[0]->line_input_bind_table;
$line_input_bind_col = $condition_input_result[0]->line_input_bind_col;
}
$bind_col_name = ucwords(str_replace("_"," ",$bind_col));
$input_cond_for_id = form_input(array( 'name' =>"prime_input_cond_for_id[]",'class' => 'form-control input-sm','value' =>$prime_input_cond_for_id,'type'=>'Hidden'));
$line_input_bind_table = form_input(array( 'name' =>"line_input_bind_table[]",'class' => 'form-control input-sm','value' =>$key,'type'=>'Hidden'));
$line_bind_col = form_input(array( 'name' =>"line_input_bind_to[]",'class' => 'form-control input-sm','value' =>$bind_col,'type'=>'Hidden'));
$con_column_input = form_dropdown(array("onchange = get_id('$drop_line_count') id" =>$con_column_input,' class' => 'form-control input-sm'), $con_column_list);
$drop_in_line .= "<tr>
<td style='vertical-align:middle;text-align: center;'>$bind_col_name $input_cond_for_id $line_input_bind_table $line_bind_col </td>
<td style='vertical-align:middle;'>$con_column_input</td>
<td>
<textarea placeholder='Write Condition' name='line_input_bind_col[]' class='form-control' rows='6' id='$line_input_bind_cond'>$line_input_bind_col</textarea>
</td>
</tr>";
}
$drop_tr_line .= "<tr>
<td style='vertical-align:middle;background-color: #f9f9f9;'>
<h4 style='text-align:center;font-size:16px;font-weight:bold;margin:4px;'>$value</h4>
$cond_drop_down
</td>
<td colspan='4' style='padding:8px!important;'>
<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th style='width:15%;'>Bind input</th>
<th style='width:20%;'>Pick columns</th>
<th>Condition</th>
</tr>
$drop_in_line
</table>
</td>
</tr>";
}
}
$drop_input_content = "<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th style='width:16%;text-align: center;'>Condition drop down value</th>
<th colspan='4' style='text-align:center;'>Condition Info</th>
</tr>
$drop_tr_line
</table>";
}else{
//NORMAL CONDITION
foreach($condition_bind_to as $bind_col){
$drop_line_count++;
$con_column_input = "con_column_input_".$drop_line_count;
$line_input_bind_cond = "line_input_bind_col_".$drop_line_count;
$condition_input_query = 'SELECT * FROM cw_form_bind_input WHERE input_cond_id = "'.$prime_cond_id.'" AND input_cond_module_id = "'.$cond_module_id.'" AND line_input_bind_to = "'.$bind_col.'"AND line_input_bind_table = "'.$key.'"';
$condition_input_info = $this->db->query("CALL sp_a_run ('SELECT','$condition_input_query')");
$condition_input_result = $condition_input_info->result();
$condition_input_info->next_result();
$prime_input_cond_for_id = 0;
$line_input_bind_table = "";
$line_input_bind_col = "";
if($condition_input_result){
$prime_input_cond_for_id = $condition_input_result[0]->prime_input_cond_for_id;
$line_input_bind_table = $condition_input_result[0]->line_input_bind_table;
$line_input_bind_col = $condition_input_result[0]->line_input_bind_col;
}
$bind_col_name = ucwords(str_replace("_"," ",$bind_col));
$input_cond_for_id = form_input(array( 'name' =>"prime_input_cond_for_id[]",'class' => 'form-control input-sm','value' =>$prime_input_cond_for_id,'type'=>'Hidden'));
$line_input_bind_table = form_input(array( 'name' =>"line_input_bind_table[]",'class' => 'form-control input-sm','value' =>$key,'type'=>'Hidden'));
$line_bind_col = form_input(array( 'name' =>"line_input_bind_to[]",'class' => 'form-control input-sm','value' =>$bind_col,'type'=>'Hidden'));
$con_column_input = form_dropdown(array("onchange = get_id('$drop_line_count') id" =>$con_column_input,' class' => 'form-control input-sm'), $con_column_list);
$drop_in_line .= "<tr>
<td style='vertical-align:middle;text-align: center;'>$bind_col_name $input_cond_for_id $line_input_bind_table $line_bind_col </td>
<td style='vertical-align:middle;'>$con_column_input</td>
<td>
<textarea placeholder='Write Condition' name='line_input_bind_col[]' class='form-control' rows='6' id='$line_input_bind_cond'>$line_input_bind_col</textarea>
</td>
</tr>";
}
$drop_input_content .= "<table class='table table-bordered table-stripted'>
<tr style='background-color:#1883E9;color:#FFFFFF;'>
<th style='width:15%;'>Bind input</th>
<th style='width:20%;'>Pick columns</th>
<th>Condition</th>
</tr>
$drop_in_line
</table>";
}
$final_content = "$line_prime_cond_id $line_cond_module_id $line_cond_type
<div style='padding:8px;'>
$drop_input_content
</div>
<div style='text-align:right;padding: 20px 15px;padding-top:0px;'>
<button class='btn btn-primary btn-sm' id='add_cond_submit'>Submit</button>
</div>";
}
echo json_encode(array('success' => TRUE,'load_content' => $final_content));
}
public function add_condition_formula(){
$line_prime_cond_id = $this->input->post('line_prime_cond_id');
$line_cond_module_id = $this->input->post('line_cond_module_id');
$line_cond_type = (int)$this->input->post('line_cond_type');
$table_cond_for_id = $this->input->post('prime_table_cond_for_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[]');
$prime_for_input_id = $this->input->post('prime_for_input_id[]');
$line_input_for = $this->input->post('line_input_for[]');
$line_input_for_table = $this->input->post('line_input_for_table[]');
$line_input_for_col = $this->input->post('line_input_for_col[]');
$input_cond_for_id = $this->input->post('prime_input_cond_for_id[]');
$line_input_bind_to = $this->input->post('line_input_bind_to[]');
$line_input_bind_table = $this->input->post('line_input_bind_table[]');
//$line_input_bind_col = $this->input->post('line_input_bind_col[]');
if($line_cond_type === 2){
$line_input_bind_col = $this->input->post('line_input_bind_col[]');
$line_input_bind_col = str_replace("'","~",$line_input_bind_col);
$line_input_bind_col = str_replace('"',"!",$line_input_bind_col);
}else{
$line_input_bind_col = $this->input->post('line_input_bind_col[]');
}
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$tab_count = 0;
$table_count = count($line_prime_table);
for($i=1;$i<= $table_count;$i++){
$table_cond_for_id_val = $table_cond_for_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)$table_cond_for_id_val === 0){
$table_query = 'insert into cw_form_table_cond_for (table_cond_id,table_cond_module_id,line_prime_table,line_prime_col,line_join_type,line_join_table,line_join_col,line_sort,trans_created_by,trans_created_date) value ("'.$line_prime_cond_id.'","'.$line_cond_module_id.'","'.$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_form_table_cond_for SET 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_table_cond_for_id = "'.$table_cond_for_id_val.'"';
}
$this->db->query("CALL sp_a_run ('RUN','$table_query')");
$tab_count++;
}
$for_in_count = 0;
$for_input_count = count($line_input_for);
for($i=1;$i<= $for_input_count;$i++){
$prime_for_input_id_val = $prime_for_input_id[$for_in_count];
$line_input_for_val = $line_input_for[$for_in_count];
$line_input_for_table_val = $line_input_for_table[$for_in_count];
$line_input_for_col_val = $line_input_for_col[$for_in_count];
if((int)$prime_for_input_id_val === 0){
$for_input_query = 'insert into cw_form_for_input (input_for_cond_id,input_for_cond_module_id,line_input_for,line_input_for_table,line_input_for_col,trans_created_by,trans_created_date) value ("'.$line_prime_cond_id.'","'.$line_cond_module_id.'","'.$line_input_for_val.'","'.$line_input_for_table_val.'","'.$line_input_for_col_val.'","'.$logged_id.'","'.$today_date.'")';
}else{
$for_input_query = 'UPDATE cw_form_for_input SET line_input_for = "'.$line_input_for_val.'",line_input_for_table = "'.$line_input_for_table_val.'",line_input_for_col = "'.$line_input_for_col_val.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE prime_for_input_id = "'.$prime_for_input_id_val.'"';
}
$this->db->query("CALL sp_a_run ('RUN','$for_input_query')");
$for_in_count++;
}
$in_count = 0;
$input_count = count($line_input_bind_to);
for($i=1;$i<= $input_count;$i++){
$input_cond_for_id_val = $input_cond_for_id[$in_count];
$line_input_bind_to_val = $line_input_bind_to[$in_count];
$line_input_bind_table_val = $line_input_bind_table[$in_count];
$line_input_bind_col_val = $line_input_bind_col[$in_count];
if((int)$input_cond_for_id_val === 0){
$input_query = 'insert into cw_form_bind_input (input_cond_id,input_cond_module_id,line_input_bind_to,line_input_bind_table,line_input_bind_col,trans_created_by,trans_created_date) value ("'.$line_prime_cond_id.'","'.$line_cond_module_id.'","'.$line_input_bind_to_val.'","'.$line_input_bind_table_val.'","'.$line_input_bind_col_val.'","'.$logged_id.'","'.$today_date.'")';
}else{
$input_query = 'UPDATE cw_form_bind_input SET line_input_bind_to = "'.$line_input_bind_to_val.'",line_input_bind_table = "'.$line_input_bind_table_val.'",line_input_bind_col = "'.$line_input_bind_col_val.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE prime_input_cond_for_id = "'.$input_cond_for_id_val.'"';
}
$this->db->query("CALL sp_a_run ('RUN','$input_query')");
$in_count++;
}
echo json_encode(array('success' => TRUE,'msg' =>"Mapping successfully Added"));
}
public function get_drop_down_info($cond_drop_down,$cond_module_id){
//$cond_drop_down = ucwords(str_replace("_"," ",$cond_drop_down));
$get_colums = 'SELECT * FROM `cw_form_setting` where prime_module_id = "'.$cond_module_id.'" and label_name = "'.$cond_drop_down.'"';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$column_result = $column_info->result();
$column_info->next_result();
$pick_list_type = $column_result[0]->pick_list_type;
$pick_list = $column_result[0]->pick_list;
$pick_table = $column_result[0]->pick_table;
$drop_down_list = array();
if((int)$pick_list_type === 1){
$table_query = 'SELECT '.$pick_list.' FROM '.$pick_table.' where trans_status = 1';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$table_result = $table_info->result();
$table_info->next_result();
$pick_list = explode(",",$pick_list);
$pick_id = $pick_list[0];
$pick_value = $pick_list[1];
$drop_down_list[""] = "---- Select Drop Down Value ----";
foreach($table_result as $rslt){
$drop_down_list[$rslt->$pick_id] = $rslt->$pick_value;
}
}
if((int)$pick_list_type === 2){
$table_query = 'SELECT * FROM '.$pick_table.' where '.$pick_table.'_status = 1';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$table_result = $table_info->result();
$table_info->next_result();
$pick_id = $pick_table."_id";
$pick_value = $pick_table."_value";
$drop_down_list[""] = "---- Select Drop Down Value ----";
foreach($table_result as $rslt){
$drop_down_list[$rslt->$pick_id] = $rslt->$pick_value;
}
}
return $drop_down_list;
}
/* ==============================================================*/
/* ===================== FORMULA TBALE - END ====================*/
/* ==============================================================*/
/* ==============================================================*/
/* ==================== DYNAMIC TBALE - START ===================*/
/* ==============================================================*/
//SAVE
public function save_table($prime_module_id,$input_view_type,$input_for,$form_view_label_name){
if(!$prime_module_id){
return false;
}
if((int)$input_view_type === 3){
$table_data = array( 'prime_module_id' => $prime_module_id ,'input_view_type' => '3');
$table_data = json_encode($table_data);
$table_name = $prime_module_id."_".$form_view_label_name;
return $this->save_rowset_table($table_data,$input_for,$table_name);
}else{
$table_data = array( 'prime_module_id' => $prime_module_id ,'input_view_type' => '1');
$table_data = json_encode($table_data);
return $this->save_common_table($table_data);
}
}
// SAVE COMMON PRIME AND CUSTOM TABLE
public function save_common_table($table_data){
$data_info = json_decode($table_data);
$prime_module_id = $data_info->prime_module_id;
$info = $this->db->query("CALL sp_form_setting_crud ('QUERY_VIEW', '$table_data',null)");
$form_setting = $info->result();
$info->next_result();
$field_type_array = array(1=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",2=>"decimal(15,@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",3=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",4=>"date NULL DEFAULT NULL",5=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",6=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",7=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",8=>"TEXT NULL",9=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",10=>"TEXT NULL");
$prime_table_name = $this->db->dbprefix($prime_module_id);
$cf_table_name = $prime_module_id."_cf";
$cf_table_name = $this->db->dbprefix($cf_table_name);
$query = $this->db->query("SELECT COUNT(*)AS data_count FROM information_schema.tables WHERE TABLE_SCHEMA ='cafs_wealth' AND table_name = '$prime_table_name'");
$result_info = $query->result();
$table_count = $result_info[0]->data_count;
$prime_table_col = "";
$cf_table_col = "";
if((int)$table_count === 1){
$prime_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='cafs_wealth' and TABLE_NAME = '$prime_table_name' and COLUMN_NAME not like '%trans%'");
$prime_table_col = $prime_table_query->result_array();
$cf_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='cafs_wealth' and TABLE_NAME = '$cf_table_name' and COLUMN_NAME not like '%trans%'");
$cf_table_col = $cf_table_query->result_array();
}
$prime_id = "prime_".$prime_module_id."_id";
$cf_id = "prime_".$prime_module_id."_cf_id";
$prime_line = "$prime_id int(11) NOT NULL AUTO_INCREMENT,";
$cf_line = "$cf_id int(11) NOT NULL AUTO_INCREMENT,$prime_id INT(11) NULL DEFAULT '0',";
$prime_line_alt = "CHANGE $prime_id $prime_id int(11) NOT NULL AUTO_INCREMENT,";
$cf_line_alt = "CHANGE $cf_id $cf_id int(11) NOT NULL AUTO_INCREMENT,CHANGE $prime_id $prime_id INT(11) NULL DEFAULT '0',";
foreach($form_setting as $setting){
$prime_form_id = $setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$field_type = $setting->field_type;
$label_name = $setting->label_name;
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list = $setting->pick_list;
$field_isdefault = $setting->field_isdefault;
$default_value = $setting->default_value;
$mandatory_field = $setting->mandatory_field;
$unique_field = $setting->unique_field;
$field_sort = $setting->field_sort;
$field_show = $setting->field_show;
if((int)$field_type === 2){
$field_length = $field_decimals;
}
if(!$field_length){
$field_length = 100;
}
if($default_value === ""){
$default_value = null;
}
$data_type = $field_type_array[$field_type];
$data_type = str_replace("@LENGTH",$field_length,$data_type);
$data_type = str_replace("@DEFAULTVALUE",$default_value,$data_type);
if((int)$field_isdefault === 1){
if($prime_table_col){
$prime_uniq = "";
if((int)$unique_field === 1){
$result_key = array_keys($prime_table_col,[ 'col_name' => $label_name,'key_exist'=>'UNI']);
if(empty($result_key)){
$prime_uniq = " ADD UNIQUE($label_name),";
}
}
if(array_search($label_name, array_column($prime_table_col, 'col_name'))){
$prime_line_alt .= "CHANGE $label_name $label_name $data_type, $prime_uniq";
}else{
$last_col = end($prime_table_col);
$prime_last_col = $last_col['col_name'];
$prime_line_alt .= "ADD $label_name $data_type AFTER $prime_last_col, $prime_uniq";
}
}else{
$prime_uniq = "";
if((int)$unique_field === 1){
$prime_uniq = " UNIQUE($label_name),";
}
$prime_line .= "$label_name $data_type, $prime_uniq ";
}
}else{
if($cf_table_col){
$cf_uniq = "";
if((int)$unique_field === 1){
$result_key = array_keys($prime_table_col,[ 'col_name' => $label_name,'key_exist'=>'UNI']);
if(empty($result_key)){
$cf_uniq = " ADD UNIQUE($label_name),";
}
}
if(array_search($label_name, array_column($cf_table_col, 'col_name'))){
$cf_line_alt .= "CHANGE $label_name $label_name $data_type, $cf_uniq";
}else{
$last_col = end($cf_table_col);
$cf_last_col = $last_col['col_name'];
$cf_line_alt .= "ADD $label_name $data_type AFTER $cf_last_col, $cf_uniq";
}
}else{
$cf_uniq = "";
if((int)$unique_field === 1){
$cf_uniq = " UNIQUE($label_name),";
}
$cf_line .= "$label_name $data_type, $cf_uniq ";
}
}
}
$prime_line_alt = rtrim($prime_line_alt,", ");
$prime_line_alt = rtrim($prime_line_alt,",");
$cf_line_alt = rtrim($cf_line_alt,", ");
$cf_line_alt = rtrim($cf_line_alt,",");
$prime_line .= "trans_created_by INT(11) NULL DEFAULT '0', trans_created_date DATETIME NULL DEFAULT NULL, trans_updated_by INT(11) NULL DEFAULT '0', trans_updated_date DATETIME NULL DEFAULT NULL, trans_deleted_by INT(11) NULL DEFAULT '0', trans_deleted_date DATETIME NULL DEFAULT NULL,trans_status INT(11) NULL DEFAULT '1',PRIMARY KEY (`$prime_id`)";
$cf_line .= "trans_created_by INT(11) NULL DEFAULT '0', trans_created_date DATETIME NULL DEFAULT NULL, trans_updated_by INT(11) NULL DEFAULT '0', trans_updated_date DATETIME NULL DEFAULT NULL, trans_deleted_by INT(11) NULL DEFAULT '0', trans_deleted_date DATETIME NULL DEFAULT NULL,trans_status INT(11) NULL DEFAULT '1',PRIMARY KEY (`$cf_id`)";
//CREATE AND ALTER TABLE
if((int)$table_count === 0){
$prime_table_query = "CREATE TABLE IF NOT EXISTS $prime_table_name($prime_line)";
$this->db->query($prime_table_query);
$cf_table_query = "CREATE TABLE IF NOT EXISTS $cf_table_name($cf_line)";
$this->db->query($cf_table_query);
$this->update_picklist($prime_module_id);
return true;
}else{
$prime_table_query_alt = "ALTER TABLE $prime_table_name $prime_line_alt";
$this->db->query($prime_table_query_alt);
$cf_table_query_alt = "ALTER TABLE $cf_table_name $cf_line_alt";
$this->db->query($cf_table_query_alt);
$this->update_picklist($prime_module_id);
return true;
}
}
// SAVE ROWSET TABLE
public function save_rowset_table($table_data,$view_input_for,$table_name){
$info = $this->db->query("CALL sp_form_setting_crud ('QUERY_VIEW', '$table_data',null)");
$form_setting = $info->result();
$info->next_result();
$field_type_array = array(1=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",2=>"decimal(15,@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",3=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",4=>"date NULL DEFAULT NULL",5=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",6=>"int(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",7=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",8=>"TEXT NULL",9=>"varchar(@LENGTH) NULL DEFAULT '@DEFAULTVALUE'",10=>"TEXT NULL");
$row_table_name = $this->db->dbprefix($table_name);
$query = $this->db->query("SELECT COUNT(*)AS data_count FROM information_schema.tables WHERE TABLE_SCHEMA ='cafs_wealth' AND table_name = '$row_table_name'");
$result_info = $query->result();
$table_count = $result_info[0]->data_count;
$row_table_col = "";
if((int)$table_count === 1){
$row_table_query = $this->db->query("SELECT COLUMN_NAME AS col_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='cafs_wealth' and TABLE_NAME = '$row_table_name' and COLUMN_NAME not like '%trans%'");
$row_table_col = $row_table_query->result_array();
}
$rowset_line = "";
$rowset_line_alt = "";
foreach($form_setting as $setting){
$prime_form_id = $setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_for = (int)$setting->input_for;
$field_type = $setting->field_type;
$label_name = $setting->label_name;
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list = $setting->pick_list;
$field_isdefault = $setting->field_isdefault;
$default_value = $setting->default_value;
$mandatory_field = $setting->mandatory_field;
$unique_field = $setting->unique_field;
$field_sort = $setting->field_sort;
$field_show = $setting->field_show;
if((int)$view_input_for === (int)$input_for){
if((int)$field_type === 2){
$field_length = $field_decimals;
}
if(!$field_length){
$field_length = 100;
}
if($default_value === ""){
$default_value = null;
}
$data_type = $field_type_array[$field_type];
$data_type = str_replace("@LENGTH",$field_length,$data_type);
$data_type = str_replace("@DEFAULTVALUE",$default_value,$data_type);
if($row_table_col){
$prime_uniq = "";
if((int)$unique_field === 1){
$result_key = array_keys($row_table_col,[ 'col_name' => $label_name,'key_exist'=>'UNI']);
if(empty($result_key)){
$prime_uniq = " ADD UNIQUE($label_name),";
}
}
if(array_search($label_name, array_column($row_table_col, 'col_name'))){
$rowset_line_alt .= "CHANGE $label_name $label_name $data_type, $prime_uniq";
}else{
$last_col = end($row_table_col);
$prime_last_col = $last_col['col_name'];
$rowset_line_alt .= "ADD $label_name $data_type AFTER $prime_last_col, $prime_uniq";
}
}else{
$prime_uniq = "";
if((int)$unique_field === 1){
$prime_uniq = " UNIQUE($label_name),";
}
$rowset_line .= "$label_name $data_type, $prime_uniq ";
}
}
}
$rowset_line = rtrim($rowset_line,", ");
$rowset_line = rtrim($rowset_line,",");
$rowset_line_alt = rtrim($rowset_line_alt,", ");
$rowset_line_alt = rtrim($rowset_line_alt,",");
//CREATE AND ALTER TABLE
if((int)$table_count === 0){
if($rowset_line){
$row_table_query = "CREATE TABLE IF NOT EXISTS $row_table_name($rowset_line)";
$this->db->query($row_table_query);
$this->update_picklist($prime_module_id);
}
return true;
}else{
$row_table_query_alt = "ALTER TABLE $row_table_name $rowset_line_alt";
if($rowset_line_alt){
$this->db->query($row_table_query_alt);
$this->update_picklist($prime_module_id);
}
return true;
}
}
// CUSTOM PICK LIST TABLE
public function update_picklist($prime_module_id){
if(!$prime_module_id){
return false;
}
$pick_query = $this->db->query("SELECT * FROM cw_form_setting where FIND_IN_SET (field_type,'5,7') and pick_list_type = '2' and prime_module_id = '$prime_module_id'");
$pick_rslt = $pick_query->result();
foreach($pick_rslt as $pick){
$label_name = $pick->label_name;
$prime_form_id = $pick->prime_form_id;
$pick_list = $pick->pick_list;
$pick_table = $pick->pick_table;
$pick_list_array = explode(",",$pick_list);
$count_query = $this->db->query("SELECT count(*) as ct_count FROM cw_form_setting where FIND_IN_SET (field_type,'5,7') and pick_list_type = '2' and pick_table != ''");
$count_rslt = $count_query->result();
$ct_count = $count_rslt[0]->ct_count;
if($ct_count > 0){
$ct_count = $ct_count+ 1;
}else{
$ct_count = 1;
}
if($pick_table){
$table_value = $pick_table."_value";
$table_status = $pick_table."_status";
$check_name = $this->db->dbprefix("zct_");
// Update required only for custom tabel
if(strpos($pick_table, $check_name) !== false) {
$this->db->query("UPDATE $pick_table SET $table_status = '0'");
foreach($pick_list_array as $list){
$exist_query = $this->db->query("SELECT count(*) as exist_count FROM $pick_table where $table_value = '$list'");
$exist_rslt = $exist_query->result();
if((int)$exist_rslt[0]->exist_count === 0){
$this->db->query("INSERT INTO $pick_table ($table_value) VALUES ('$list')");
}else{
$this->db->query("UPDATE $pick_table SET $table_status = '1' WHERE FIND_IN_SET($table_value, '$pick_list')");
}
}
}
}else{
$table_name = "zct_$ct_count";
$table_name = $this->db->dbprefix($table_name);
$table_id = $table_name."_id";
$table_value = $table_name."_value";
$table_status = $table_name."_status";
$tabel_col = "$table_id int(11) NOT NULL AUTO_INCREMENT,$table_value varchar(150) NULL DEFAULT '0',$table_status INT(11) NULL DEFAULT '1', PRIMARY KEY (`$table_id`)";
$table_query = "CREATE TABLE IF NOT EXISTS $table_name($tabel_col)";
if($this->db->query($table_query)){
foreach($pick_list_array as $list){
$this->db->query("INSERT INTO $table_name ($table_value) VALUES ('$list')");
}
}
$this->db->query("UPDATE cw_form_setting SET pick_table = '$table_name' WHERE prime_form_id = '$prime_form_id'");
}
}
}
/* ==============================================================*/
/* ===================== DYNAMIC TBALE - END ====================*/
/* ==============================================================*/
}
?>