File: /home/cafsindia/hrms_allyindian_com/application_bk/controllers/Sms_setting.php
<?php
/**********************************************************
Filename: sms Setting
Description: sms Setting for creating new Dynamic sms template.
Author: udhayakumar Anandhan
Created on: 19 FEB 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 Sms_setting extends Secure_Controller{
public function __construct(){
parent::__construct('sms_setting');
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
// DATATABLE HEADERS DEFINED _ARN FOR (8.3)
$db_name = $this->config->item("db_name");
$prime_table_query = $this->db->query("SELECT CASE WHEN COLUMN_NAME = 'menu_id' THEN 'menu_name' WHEN COLUMN_NAME = 'show_module' THEN 'module_status'WHEN COLUMN_NAME = 'temp_mand' THEN 'mandatory_status' ELSE COLUMN_NAME END AS label_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$db_name' AND TABLE_NAME = 'cw_modules' AND COLUMN_NAME IN ('module_name', 'menu_id', 'module_type', 'show_module','temp_mand')");
$prime_table_col = $prime_table_query->result();
$data['table_head'] = $prime_table_col; ;
$this->load->view('sms_setting/manage',$data);
}
/* ==============================================================*/
/* ================== sms COMMON OPEARTION - START ============*/
/* ==============================================================*/
//sms SEARCH OPEARTION
public function search(){
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$search = $this->input->post('search');
$search = trim($search['value']);
$common_search = '';
$searchterm = $this->db->escape_like_str($search);
if($search){
$common_search .= " AND cw_modules.module_name LIKE '%" . $searchterm . "%'";
}
//FETCH RECORDS DATA
$search_total = $this->db->query("SELECT count(*) as allcount from cw_modules where trans_status = 1 and cw_modules.sms_setting = 1 ");
$search_total_info = $search_total->result();
$total_count = $search_total_info[0]->allcount;
$search_count = $this->db->query("SELECT count(*) as allcount from cw_modules where trans_status = 1 and cw_modules.sms_setting = 1 $common_search");
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query = "SELECT module_id,module_name,cw_main_menu.menu_name,module_type,CASE WHEN show_module = 1 THEN 'Active' WHEN show_module = 0 THEN 'In Active' END AS module_status,CASE WHEN temp_mand = 1 THEN 'Added' WHEN temp_mand = 0 THEN 'Removed'
END AS mandatory_status FROM cw_modules INNER JOIN cw_main_menu ON cw_main_menu.prime_menu_id = cw_modules.menu_id WHERE cw_modules.trans_status = 1 and cw_modules.sms_setting = 1 $common_search";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$search_data->next_result();
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
// MODEL VIEW OPEARTION
public function view($sms_info_module_id=null){
if(!$sms_info_module_id){
$sms_info_module_id = 0;
}
$data['sms_info_module_id'] = $sms_info_module_id;
$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();
$sms_for[""] = "---- Module For ----";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$sms_for[$role_id] = $category_name;
}
$data['sms_for'] = $sms_for;
$table_query = 'SELECT table_name as table_name FROM information_schema.tables WHERE table_schema ="'.$this->config->item("db_name").'" and table_name not like "cw_zct%" and table_name not like "%_cf"';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$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_monthly_input","cw_payroll_formula","cw_payroll_function","cw_permissions","cw_sms_block","cw_sms_design","cw_sms_info","cw_sms_map","cw_sms_table","cw_sms_table_where","cw_salary_check","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","dailyunpunch","monthlyattdata");
$table_list[""] = "---- Select Table ----";
foreach($table_result as $table){
$table_name = $table->table_name;
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;
$sms_info = $this->get_sms_info_list($sms_info_module_id);
$sms_info_rslt = json_decode($sms_info);
$data['sms_info_list'] = $sms_info_rslt->sms_info_list;
$data['sms_block_for'] = json_decode(json_encode($sms_info_rslt->sms_block_for), True);
$sms_block = $this->get_sms_block_list($sms_info_module_id);
$sms_block_rslt = json_decode($sms_block);
$data['sms_block_list'] = $sms_block_rslt->sms_block_list;
$data['sms_table_list'] = json_decode(json_encode($sms_block_rslt->sms_table_list), True);
// $data['split_table_list'] = $this->get_split_list($sms_info_module_id);
$sms_tabl_data = $this->get_sms_table_list($sms_info_module_id);
$sms_tabl_rlst = json_decode($sms_tabl_data);
$data['sms_table_info_list'] = $sms_tabl_rlst->sms_info_list;
if($sms_info_module_id === 'custom_approval'){
$mail_module_id = 'employees';
}else{
$mail_module_id = $sms_info_module_id;
}
$pick_base_qry = 'select prime_form_id,label_name,view_name,prime_module_id,pick_list_type,pick_list,pick_table from cw_form_setting where prime_module_id = "'.$mail_module_id.'" and field_isdefault = 1 and field_show = 1 and input_view_type in("1","2") and field_type = 5 and trans_status = 1';
$pick_base_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_base_qry')");
$pick_base_result = $pick_base_data->result();
$pick_base_data->next_result();
$pick_key = array_column($pick_base_result, "label_name");
$pick_val = array_column($pick_base_result, "view_name");
$query_list_id = array_combine($pick_key, $pick_val);
$query_list_id = array("" => "---- Select Pick list ----") + $query_list_id;
$data['query_list_id'] = $query_list_id;
$mobile_column_qry = 'select prime_form_id,label_name,view_name,prime_module_id,pick_list_type,pick_list,pick_table from cw_form_setting where prime_module_id = "'.$mail_module_id.'" and field_show = 1 and field_type = 11 and trans_status = 1';
$mobile_column_info = $this->db->query("CALL sp_a_run ('SELECT','$mobile_column_qry')");
$mobile_column_result = $mobile_column_info->result();
$mobile_column_info->next_result();
$mobile_column[""] = "---- Mobile No Column ----";
foreach ($mobile_column_result as $key => $value) {
$label_name = $value->label_name;
$view_name = $value->view_name;
$table_name = $value->prime_module_id;
$mobile_column[$label_name] = $table_name." - ".$view_name;
}
$emp_column_qry = 'select prime_form_id,label_name,view_name,prime_module_id,pick_list_type,pick_list,pick_table from cw_form_setting where prime_module_id = "employees" and field_show = 1 and field_type = 11 and trans_status = 1';
$emp_column_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_column_qry')");
$emp_column_result = $emp_column_info->result();
$emp_column_info->next_result();
$emp_mobile_column[""] = "---- Mobile No Column ----";
foreach ($emp_column_result as $key => $value) {
$label_name = $value->label_name;
$view_name = $value->view_name;
$table_name = $value->prime_module_id;
$emp_mobile_column[$label_name] = $table_name." - ".$view_name;
}
if(count($mobile_column) > 1){
$mobileno_column = $mobile_column;
}else{
$mobileno_column = $emp_mobile_column;
}
$all_column_qry = 'select prime_form_id,label_name,view_name,prime_module_id,pick_list_type,pick_list,pick_table from cw_form_setting where prime_module_id = "'.$mail_module_id.'" and field_show = 1 and trans_status = 1';
$all_column_info = $this->db->query("CALL sp_a_run ('SELECT','$all_column_qry')");
$all_column_result = $all_column_info->result();
$all_column_info->next_result();
$all_column_key = array_column($all_column_result, "label_name");
$all_column_val = array_column($all_column_result, "view_name");
$all_column = array_combine($all_column_key, $all_column_val);
$all_column = array("" => "---- Select Column ----") + $all_column;
$data['all_column'] = $all_column;
$data['mobileno_column'] = $mobileno_column;
$data['mobile_column'] = $mobile_column;
$this->load->view("sms_setting/sms_info",$data);
}
/* ==============================================================*/
/* ================== sms COMMON OPEARTION - END ============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================== sms BASIC OPEARTION - START ============*/
/* ==============================================================*/
// sms BASIC SAVE
public function save_sms_info(){
$prime_sms_info_id = (int)$this->input->post('prime_sms_info_id');
$sms_info_module_id = $this->input->post('sms_info_module_id');
$sms_info_name = $this->input->post('sms_info_name');
$sms_info_for = ltrim(implode(",",$this->input->post('sms_info_for[]')),",");
$query_list_id = $this->input->post('query_list_id');
$query_column_list = $this->input->post('query_column_list');
$table_values = $this->input->post('table_values');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
if($prime_sms_info_id === 0){
if(!$this->check_sms_info_for_already_exists($sms_info_for,$sms_info_module_id,$query_list_id,$table_values)){
$is_exist_qry = 'SELECT count(*) as rslt_count FROM cw_sms_info where sms_info_module_id = "'.$sms_info_module_id.'" and sms_info_name = "'.$sms_info_name.'" and trans_status = 1 ';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_rslt = $is_exist_data->result();
$is_exist_data->next_result();
if((int)$exist_rslt[0]->rslt_count === 0){
$sms_qry = 'INSERT INTO cw_sms_info (sms_info_module_id, sms_info_name,sms_info_for,query_list_id,query_column_list,table_values,trans_created_by, trans_created_date) VALUES ("'.$sms_info_module_id.'","'.$sms_info_name.'","'.$sms_info_for.'","'.$query_list_id.'","'.$query_column_list.'","'.$table_values.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$sms_qry')");
$sms_info = $this->get_sms_info_list($sms_info_module_id);
$sms_info_rslt = json_decode($sms_info);
$sms_info_list = $sms_info_rslt->sms_info_list;
$sms_block_for = $sms_info_rslt->sms_block_for;
echo json_encode(array('success' => true, 'message' => "sms info successfully added", 'sms_info_list' => $sms_info_list, 'sms_block_for' => $sms_block_for));
}else{
echo json_encode(array('success' => FALSE, 'message' => "sms info already exist"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "sms info already created for this sms For Check it"));
}
}else{
if(!$this->check_sms_info_for_already_exists($sms_info_for,$sms_info_module_id,$query_list_id,$table_values,$prime_sms_info_id)){
$upd_qry = 'UPDATE cw_sms_info SET sms_info_module_id = "'.$sms_info_module_id.'",sms_info_name = "'.$sms_info_name.'",sms_info_for = "'.$sms_info_for.'",query_list_id = "'.$query_list_id.'",query_column_list = "'.$query_column_list.'",table_values = "'.$table_values.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_sms_info_id = "'.$prime_sms_info_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$sms_info = $this->get_sms_info_list($sms_info_module_id);
$sms_info_rslt = json_decode($sms_info);
$sms_info_list = $sms_info_rslt->sms_info_list;
$sms_block_for = $sms_info_rslt->sms_block_for;
echo json_encode(array('success' => true, 'message' => "sms info successfully updated", 'sms_info_list' => $sms_info_list, 'sms_block_for' => $sms_block_for));
}else{
echo json_encode(array('success' => FALSE, 'message' => "sms info already created for this sms For Check it"));
}
}
}
//CHECKING sms INFO FOR ALREADY EXISTS
public function check_sms_info_for_already_exists($sms_info_for,$sms_info_module_id,$query_list_id,$table_values,$prime_sms_info_id = -1){
$sms_info_for = explode(',', $sms_info_for);
$query ='';
foreach($sms_info_for as $rslt){
$query .= "sms_info_for LIKE \"%".$rslt."%\" OR sms_info_for LIKE \"".$rslt."%\" OR sms_info_for LIKE \"%".$rslt."\" OR ";
}
$query = rtrim($query,"OR ");
$exist_query = 'SELECT * FROM cw_sms_info where ('.$query.') and query_list_id = "'.$query_list_id.'" and table_values = "'.$table_values.'" and sms_info_module_id = "'.$sms_info_module_id.'" and trans_status = 1';
if((int)$prime_sms_info_id > 0){
$exist_query .=' and prime_sms_info_id != "'.$prime_sms_info_id.'"';
}
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_rslt = $exist_data->num_rows();
$exist_data->next_result();
if((int)$exist_rslt > 0){
return TRUE;
}else{
return FALSE;
}
}
//GET sms BASIC LIST
public function get_sms_info_list($sms_info_module_id){
if($sms_info_module_id === 'custom_approval'){
$mail_module_id = 'employees';
}else{
$mail_module_id = $sms_info_module_id;
}
$sms_qry = 'SELECT * FROM cw_sms_info inner join cw_form_setting on cw_form_setting.label_name = cw_sms_info.query_list_id where sms_info_module_id = "'.$sms_info_module_id.'" and prime_module_id = "'.$mail_module_id.'" and cw_sms_info.trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
$tr_line = "";
$sms_block_for[] = "--- Choose one ---";
foreach($sms_rslt as $rslt){
$prime_sms_info_id = $rslt->prime_sms_info_id;
$sms_info_module_id = $rslt->sms_info_module_id;
$sms_info_name = ucwords($rslt->sms_info_name);
$sms_info_for = $rslt->sms_info_for;
$sms_type = $rslt->sms_type;
$sms_based_on = $rslt->sms_based_on;
$label_name = $rslt->label_name;
$table_values = $rslt->table_values;
$pick_table = $rslt->pick_table;
$pick_list_type = $rslt->pick_list_type;
$query_list_id = $rslt->view_name;
$query_column_list = $rslt->query_column_list;
$pick_list = $rslt->pick_list;
$get_first_pick_value = explode(',', $pick_list);
$get_first_pick_value = $get_first_pick_value[0];
$sms_block_for[$prime_sms_info_id] = $sms_info_name;
if((int)$pick_list_type === 2){
$get_first_pick_value = $pick_table."_id";
$query_column_list = $pick_table."_value";
$status = $pick_table."_status";
}else
if((int)$pick_list_type === 1){
$get_first_pick_value = $get_first_pick_value;
$status = "trans_status";
}
$table_name_qry = 'SELECT '.$query_column_list.' FROM '.$pick_table.' where '.$get_first_pick_value.' = "'.$table_values.'" and '.$status.' = 1';
$table_name_info = $this->db->query("CALL sp_a_run ('SELECT','$table_name_qry')");
$table_name_result = $table_name_info->result();
$table_name_info->next_result();
$table_values = $table_name_result[0]->$query_column_list;
$sms_for_qry = 'SELECT GROUP_CONCAT(category_name) as category_name FROM cw_category where prime_category_id in ('.$sms_info_for.')';
$sms_for_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_for_qry')");
$sms_for_rslt = $sms_for_data->result();
$sms_for_data->next_result();
$category_name = $sms_for_rslt[0]->category_name;
$tr_line .= "<tr>
<td>$sms_info_name</td>
<td>$category_name</td>
<td>$query_list_id</td>
<td>$query_column_list</td>
<td>$table_values</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=edit_sms_info('$prime_sms_info_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_sms_info('$prime_sms_info_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$sms_info_list = "<table class='table table-bordered table-stripted' id='sms_info_table'>
<tr class='inline_head'>
<th>sms Name</th>
<th>sms For</th>
<th>Pick List</th>
<th>Pick List Column</th>
<th>Table Value</th>
<th>Edit</th>
<th>Delete</th>
</tr>
$tr_line
</table>";
return json_encode(array('success' => TRUE,'sms_info_list' => $sms_info_list,'sms_block_for'=>$sms_block_for));
}
public function get_sms_table_list($sms_info_module_id){
$sms_qry = 'SELECT prime_sms_table_id,sms_table_for_id,sms_info_name,sms_table_column,sms_table_sms_column,sms_name_table,cw_sms_table.sms_block_module_id,cw_sms_block.sms_block_name FROM cw_sms_table inner join cw_sms_info on cw_sms_info.prime_sms_info_id = cw_sms_table.sms_table_list inner join cw_sms_block on cw_sms_block.prime_sms_block_id = cw_sms_table.sms_name_table where cw_sms_table.sms_block_module_id = "'.$sms_info_module_id.'" and cw_sms_table.trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
$tr_line = "";
$sms_block_for[] = "--- Choose one ---";
foreach($sms_rslt as $rslt){
$prime_sms_table_id = $rslt->prime_sms_table_id;
$sms_table_for_id = $rslt->sms_table_for_id;
$sms_name_table = $rslt->sms_info_name;
$sms_table_column = $rslt->sms_table_column;
$sms_table_sms_column = $rslt->sms_table_sms_column;
$sms_block_module_id = $rslt->sms_block_module_id;
$sms_block_name = $rslt->sms_block_name;
$tr_line .= "<tr>
<td>$sms_name_table</td>
<td>$sms_block_name</td>
<td>$sms_table_column</td>
<td>$sms_table_sms_column</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=edit_sms_table_info('$prime_sms_table_id','$sms_info_module_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_sms_table_info('$prime_sms_table_id','$sms_info_module_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$sms_info_list = "<table class='table table-bordered table-stripted' id='sms_table_list_info'>
<tr class='inline_head'>
<th>sms Info</th>
<th>sms Name</th>
<th>Table Column</th>
<th>sms Column</th>
<th>Edit</th>
<th>Delete</th>
</tr>
$tr_line
</table>";
return json_encode(array('success' => TRUE,'sms_info_list' => $sms_info_list,'sms_block_for'=>$sms_block_for));
}
//EDIT sms INFO OPERATION
public function edit_sms_info(){
$prime_sms_info_id = (int)$this->input->post('prime_sms_info_id');
$sms_info_module_id = $this->input->post('sms_info_module_id');
$sms_qry = 'SELECT * FROM cw_sms_info where prime_sms_info_id = "'.$prime_sms_info_id.'" and trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
$query_list_id = $sms_rslt[0]->query_list_id;
$query_column_list = $sms_rslt[0]->query_column_list;
$query_list_id = $sms_rslt[0]->query_list_id;
if($sms_info_module_id === 'custom_approval'){
$sms_info_module_id = 'employees';
}
$pick_base_qry = 'select * from cw_form_setting where label_name = "'.$query_list_id.'" and prime_module_id = "'.$sms_info_module_id.'"';
$pick_base_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_base_qry')");
$pick_base_result = $pick_base_data->result();
$pick_base_data->next_result();
if($pick_base_result){
$pick_table = $pick_base_result[0]->pick_table;
$get_colums = 'SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="'.$this->config->item("db_name").'" AND `TABLE_NAME` = "'.$pick_table.'" AND COLUMN_NAME NOT IN ("trans_created_by","trans_created_date","trans_updated_by","trans_updated_date","trans_deleted_by","trans_deleted_date","trans_status")';
$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;
}
}
$pick_base_qry = 'select * from cw_form_setting where label_name = "'.$query_list_id.'" and prime_module_id = "'.$sms_info_module_id.'"';
$pick_base_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_base_qry')");
$pick_base_result = $pick_base_data->result();
$pick_base_data->next_result();
if($pick_base_result){
$prime_module_id = $pick_base_result[0]->prime_module_id;
$view_name = $pick_base_result[0]->view_name;
$field_type = (int)$pick_base_result[0]->field_type;
$pick_list_type = (int)$pick_base_result[0]->pick_list_type;
$pick_list = $pick_base_result[0]->pick_list;
$pick_table = $pick_base_result[0]->pick_table;
$same_table = "cw_".$prime_module_id;
if($pick_table === $same_table){
$get_qry = 'select * from cw_form_setting where label_name = "'.$query_column_list.'" and prime_module_id = "'.$prime_module_id.'"';
$get_data = $this->db->query("CALL sp_a_run ('SELECT','$get_qry')");
$get_result = $get_data->result();
$get_data->next_result();
if(count($get_result) === 1){
$view_name = $get_result[0]->view_name;
$field_type = (int)$get_result[0]->field_type;
$pick_list_type = (int)$get_result[0]->pick_list_type;
$pick_list = $get_result[0]->pick_list;
$pick_table = $get_result[0]->pick_table;
$pick_list_info = array();
if(($field_type === 5) || ($field_type === 7)){
$final_pick = array();
if($pick_list_type === 1){
$pick_colum_qry = 'SELECT '.$pick_list.' FROM '.$pick_table.' WHERE trans_status = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_key = array_column($pick_colum_result, $pick_list_val_1);
$pick_val = array_column($pick_colum_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}else
if($pick_list_type === 2){
$id = $pick_table."_id";
$value = $pick_table."_value";
$status = $pick_table."_status";
$select_info = "$id,$value";
$pick_colum_qry = 'SELECT '.$select_info.' FROM '.$pick_table.' WHERE '.$status.' = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_key = array_column($pick_colum_result, $id);
$pick_val = array_column($pick_colum_result, $value);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}
}
}
}else{
$pick_list_info = array();
if(($field_type === 5) || ($field_type === 7)){
$final_pick = array();
if($pick_list_type === 1){
$pick_colum_qry = 'SELECT '.$pick_list.' FROM '.$pick_table.' WHERE trans_status = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_key = array_column($pick_colum_result, $pick_list_val_1);
$pick_val = array_column($pick_colum_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}else
if($pick_list_type === 2){
$id = $pick_table."_id";
$value = $pick_table."_value";
$status = $pick_table."_status";
$select_info = "$id,$value";
$pick_colum_qry = 'SELECT '.$select_info.' FROM '.$pick_table.' WHERE '.$status.' = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_key = array_column($pick_colum_result, $id);
$pick_val = array_column($pick_colum_result, $value);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}
}
}
}
$print_column_qry = 'select cw_print_design.prime_print_design_id,cw_print_info.print_info_name from cw_print_design inner join cw_print_info on cw_print_info.prime_print_info_id = cw_print_design.print_design_for where cw_print_info.print_info_module_id = "'.$sms_info_module_id.'" and cw_print_design.trans_status = 1 and cw_print_info.trans_status = 1';
$print_column_info = $this->db->query("CALL sp_a_run ('SELECT','$print_column_qry')");
$print_column_rlst = $print_column_info->result();
$print_column_info->next_result();
$print_column[""] = "---- Select Attachment ----";
foreach($print_column_rlst as $prints){
$print_id = $prints->prime_print_design_id;
$print_name = $prints->print_info_name;
$print_column[$print_id] = $print_name;
}
echo json_encode(array('success' => TRUE,'sms_info' => $sms_rslt[0],"column_list"=>$column_list,"value_list"=>$final_pick,'print_column'=>$print_column));
}
//REMOVE sms INFO OPERATION
public function remove_sms_info(){
$prime_sms_info_id = (int)$this->input->post('prime_sms_info_id');
$sms_info_module_id = $this->input->post('sms_info_module_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_sms_info SET trans_status = 0,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_sms_info_id = "'.$prime_sms_info_id.'"';
$delete = $this->db->query("CALL sp_a_run ('UPDATE','$remove_qry')");
$delete->next_result();
$sms_info = $this->get_sms_info_list($sms_info_module_id);
$sms_info_rslt = json_decode($sms_info);
$sms_info_list = $sms_info_rslt->sms_info_list;
$sms_block_for = $sms_info_rslt->sms_block_for;
echo json_encode(array('success' => true, 'message'=>'sms Info deleted Successfully!!!','sms_info_list'=>$sms_info_list,'sms_block_for'=>$sms_block_for));
}
/* ==============================================================*/
/* =================== sms BASIC OPEARTION - END =============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================ sms BLOCK OPEARTION - START ==============*/
/* ==============================================================*/
// sms BLOCK SAVE
public function save_sms_block(){
$prime_sms_block_id = (int)$this->input->post('prime_sms_block_id');
$sms_block_module_id = $this->input->post('sms_block_module_id');
$sms_block_name = $this->input->post('sms_block_name');
$sms_block_for = $this->input->post('sms_block_for');
$sms_block_type = $this->input->post('sms_block_type');
$sms_block_table = ltrim(implode(",",$this->input->post('sms_block_table[]')),",");
$sms_block_column = $this->input->post('sms_block_column');
$suppressed_data = $this->input->post('suppressed_data');
$cumulative_data = $this->input->post('cumulative_data');
if($prime_sms_block_id === 0){
$is_exist_qry = 'SELECT count(*) as rslt_count FROM cw_sms_block where sms_block_for = "'.$sms_block_for.'" and sms_block_type = "'.$sms_block_type.'" and sms_block_table = "'.$sms_block_table.'" and trans_status = 1 ';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_rslt = $is_exist_data->result();
$is_exist_data->next_result();
// if((int)$exist_rslt[0]->rslt_count === 0){
if($this->check_sms_block_primary_already_exists($sms_block_for,$sms_block_type,$sms_block_name,$prime_sms_block_id)){
if(!$this->check_sms_block_already_exists($sms_block_for,$sms_block_type,$sms_block_name,$prime_sms_block_id)){
$sms_qry = 'INSERT INTO cw_sms_block (sms_block_module_id, sms_block_name,sms_block_for,sms_block_type,sms_block_table,sms_block_column,trans_created_by, trans_created_date) VALUES ("'.$sms_block_module_id.'","'.$sms_block_name.'","'.$sms_block_for.'","'.$sms_block_type.'","'.$sms_block_table.'","'.$sms_block_column.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$sms_qry')");
$sms_block = $this->get_sms_block_list($sms_block_module_id);
$sms_block_rslt = json_decode($sms_block);
$sms_block_list = $sms_block_rslt->sms_block_list;
$sms_table_list = $sms_block_rslt->sms_table_list;
echo json_encode(array('success' => true, 'message' => "sms block successfully added", 'sms_block_list' => $sms_block_list, 'sms_table_list' => $sms_table_list));
}else{
echo json_encode(array('success' => FALSE, 'message' => "sms block already exist"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Primary Mandatory"));
}
}else{
if($this->check_sms_block_primary_already_exists($sms_block_for,$sms_block_type,$sms_block_name,$prime_sms_block_id)){
if(!$this->check_sms_block_already_exists($sms_block_for,$sms_block_type,$sms_block_name,$prime_sms_block_id)){
$upd_qry = 'UPDATE cw_sms_block SET sms_block_for = "'.$sms_block_for.'",sms_block_type = "'.$sms_block_type.'",sms_block_name = "'.$sms_block_name.'",sms_block_for = "'.$sms_block_for.'",sms_block_table = "'.$sms_block_table.'",sms_block_column = "'.$sms_block_column.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_sms_block_id = "'.$prime_sms_block_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$sms_block = $this->get_sms_block_list($sms_block_module_id);
$sms_block_rslt = json_decode($sms_block);
$sms_block_list = $sms_block_rslt->sms_block_list;
$sms_table_list = $sms_block_rslt->sms_table_list;
echo json_encode(array('success' => true, 'message' => "sms block successfully updated", 'sms_block_list' => $sms_block_list, 'sms_table_list' => $sms_table_list));
}
else{
echo json_encode(array('success' => FALSE, 'message' => "sms block already exist"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Primary Mandatory"));
}
}
}
public function check_sms_block_already_exists($sms_block_for,$sms_block_type,$sms_block_name,$prime_sms_block_id = -1){
$is_exist_qry = 'SELECT * FROM cw_sms_block where sms_block_for = "'.$sms_block_for.'" and trans_status = 1 ';
if((int)$sms_block_type === 1){
$is_exist_qry .= " and sms_block_type = $sms_block_type";
}else{
$is_exist_qry .= ' and sms_block_name = "'.$sms_block_name.'"';
}
if((int)$prime_sms_block_id > 0){
$is_exist_qry .= " and prime_sms_block_id != $prime_sms_block_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;
}
}
public function check_sms_block_primary_already_exists($sms_block_for,$sms_block_type,$sms_block_name,$prime_sms_block_id = -1){
if((int)$sms_block_type === 2){
$is_exist_qry = 'SELECT * FROM cw_sms_block where sms_block_for = "'.$sms_block_for.'" and trans_status = 1 ';
if((int)$sms_block_type === 2){
$is_exist_qry .= "and sms_block_type =1";
}
if((int)$prime_sms_block_id > 0){
$is_exist_qry .= " and prime_sms_block_id != $prime_sms_block_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;
}
}else{
return TRUE;
}
}
//GET sms BLOCK LIST
public function get_sms_block_list($sms_block_module_id){
$sms_qry = 'SELECT * FROM cw_sms_info left join cw_sms_block on cw_sms_info.prime_sms_info_id = cw_sms_block.sms_block_for where cw_sms_block.sms_block_module_id = "'.$sms_block_module_id.'" and cw_sms_info.trans_status = 1 and cw_sms_block.trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
$tr_line = "";
$sms_table_list[] = "--- Choose one ---";
$sms_block_type_list = array("1"=>"Primary","2"=>"Secondary",);
foreach($sms_rslt as $rslt){
$sms_info_name = ucwords($rslt->sms_info_name);
$prime_sms_block_id = $rslt->prime_sms_block_id;
$sms_block_name = ucwords($rslt->sms_block_name);
$sms_block_type = $rslt->sms_block_type;
$sms_block_table = explode(",",$rslt->sms_block_table);
$sms_block_column = explode(",",$rslt->sms_block_column);
$table_td = "";
foreach($sms_block_table as $table){
$table_td .= "<tr><td>$table</td><tr>";
}
$table_list = "<table> $table_td </table>";
$column_td = "";
foreach($sms_block_column as $column){
$column_td .= "<tr><td>$column</td><tr>";
}
$column_list = "<table> $column_td </table>";
$sms_block_type_name = $sms_block_type_list[$sms_block_type];
$sms_table_list[$prime_sms_block_id] = $sms_info_name ." - ".$sms_block_name;
$tr_line .= "<tr>
<td>$sms_info_name</td>
<td>$sms_block_name</td>
<td>$table_list</td>
<td>$column_list</td>
<td>$sms_block_type_name</td><td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=edit_sms_block('$prime_sms_block_id')> <i class='fa fa-pencil-square-o' aria-hidden='true'></i> Edit</a></td>";
if((int)$sms_block_type === 1){
$tr_line .= "<td></td>";
}else{
$tr_line .= "<td style='text-align:center;'><a class='btn btn-xs btn-danger' onclick=remove_sms_block('$prime_sms_block_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
}
$sms_block_list = "<table class='table table-bordered table-stripted' id='sms_info_table'>
<tr class='inline_head'>
<th>sms Info Name</th>
<th>sms Name</th>
<th>sms Table</th>
<th>Primary Columns</th>
<th>Table Type</th>
<th>Edit</th>
<th>Delete</th>
</tr>
$tr_line
</table>";
return json_encode(array('success' => TRUE,'sms_block_list' => $sms_block_list,'sms_table_list' => $sms_table_list));
}
//sms BLOCK COLUMNS
public function get_sms_block_table(){
$sms_block_table = ltrim(implode(",",$this->input->post('sms_block_table')),",");
$prime_in = '"'.str_replace(",",'","', $sms_block_table);
$custom_in = str_replace(",",'_cf","', $sms_block_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.') AND COLUMN_NAME NOT IN ("trans_created_by","trans_created_date","trans_updated_by","trans_updated_date","trans_deleted_by","trans_deleted_date","trans_status")';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$column_result = $column_info->result();
$column_info->next_result();
$sms_block_column[""] = "---- Select Column ----";
foreach($column_result as $column){
$table_value = $column->TABLE_NAME;
$column_value = $column->COLUMN_NAME;
if(strpos($column_value, 'trans_') !== false) {
//echo "UDY :: $table_value - $column_value<br/>"; // UDY CHECK FOR ALTER
}else{
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$column_name = ucwords(str_replace("_"," ",$column_value));
$sms_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
}
echo json_encode(array('success' => true,'sms_block_column'=>$sms_block_column));
}
//EDIT sms BLOCK OPERATION
public function edit_sms_block(){
$prime_sms_block_id = (int)$this->input->post('prime_sms_block_id');
$sms_qry = 'SELECT * FROM cw_sms_block where prime_sms_block_id = "'.$prime_sms_block_id.'" and trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
$sms_block_table = $sms_rslt[0]->sms_block_table;
$prime_in = '"'.str_replace(",",'","', $sms_block_table);
$custom_in = str_replace(",",'_cf","', $sms_block_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();
$sms_block_column[""] = "---- Select Column ----";
foreach($column_result as $column){
$table_value = $column->TABLE_NAME;
$column_value = $column->COLUMN_NAME;
if(strpos($column_value, 'trans_') !== false) {
//echo "UDY :: $table_value - $column_value<br/>"; // UDY CHECK FOR ALTER
}else{
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$column_name = ucwords(str_replace("_"," ",$column_value));
$sms_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
}
echo json_encode(array('success' => TRUE,'sms_block_column' => $sms_block_column,'sms_info' => $sms_rslt[0]));
}
//REMOVE sms INFO OPERATION
public function remove_sms_block(){
$prime_sms_block_id = (int)$this->input->post('prime_sms_block_id');
$sms_block_module_id = $this->input->post('sms_block_module_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_sms_block SET trans_status = 0,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_sms_block_id = "'.$prime_sms_block_id.'"';
$delete = $this->db->query("CALL sp_a_run ('UPDATE','$remove_qry')");
$delete->next_result();
$sms_block = $this->get_sms_block_list($sms_block_module_id);
$sms_block_rslt = json_decode($sms_block);
$sms_block_list = $sms_block_rslt->sms_block_list;
$sms_table_list = $sms_block_rslt->sms_table_list;
echo json_encode(array('success' => true, 'message'=>'sms block deleted Successfully!!!','sms_block_list'=>$sms_block_list,'sms_table_list'=>$sms_table_list));
}
/* ==============================================================*/
/* ================== sms BLOCK OPEARTION - END ==============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================ sms TABLE OPEARTION - START ==============*/
/* ==============================================================*/
public function get_sms_table_info(){
$sms_table_for_id = $this->input->post("prime_sms_table_id");
$sms_block_module_id = $this->input->post("sms_block_module_id");
$sms_table_list = $this->input->post("sms_table_list");
$sms_name_table = $this->input->post("sms_name_table");
$sms_table_column = ltrim(implode(",",$this->input->post('sms_table_column[]')),",");
$sms_table_sms_column = ltrim(implode(",",$this->input->post('sms_table_sms_column[]')),",");
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
if((int)$sms_table_for_id === 0){
$sms_qry = 'INSERT INTO cw_sms_table (sms_table_for_id, sms_table_list,sms_name_table,sms_table_column,sms_table_sms_column,sms_block_module_id,trans_created_by, trans_created_date) VALUES ("'.$sms_table_for_id.'","'.$sms_table_list.'","'.$sms_name_table.'","'.$sms_table_column.'","'.$sms_table_sms_column.'","'.$sms_block_module_id.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$sms_qry')");
$sms_info = $this->get_sms_table_list($sms_block_module_id);
$sms_tabl_rlst = json_decode($sms_info);
$sms_table_info_list = $sms_tabl_rlst->sms_info_list;
echo json_encode(array('success' => TRUE, 'message' => "Added Successfully",'sms_table_info_list' => $sms_table_info_list));
}else{
$upd_qry = 'UPDATE cw_sms_table SET sms_table_for_id = "'.$sms_table_for_id.'",sms_table_list = "'.$sms_table_list.'",sms_name_table = "'.$sms_name_table.'",sms_table_column = "'.$sms_table_column.'",sms_table_sms_column = "'.$sms_table_sms_column.'",sms_block_module_id = "'.$sms_block_module_id.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_sms_table_id = "'.$sms_table_for_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$sms_info = $this->get_sms_table_list($sms_block_module_id);
$sms_tabl_rlst = json_decode($sms_info);
$sms_table_info_list = $sms_tabl_rlst->sms_info_list;
// print_r($sms_table_info_list);die;
echo json_encode(array('success' => TRUE, 'message' => "Update Successfully",'sms_table_info_list' => $sms_table_info_list));
}
}
public function save_sms_table(){
$sms_table_for_id = $this->input->post('sms_table_for_id');
$sms_table_module_id = $this->input->post('sms_table_module_id');
$prime_sms_table_id = $this->input->post('prime_sms_table_join_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_sms_table_join SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE sms_table_for_id = "'.$sms_table_for_id.'" and sms_table_module_id = "'.$sms_table_module_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_query')");
$table_count = count($line_prime_table);
for($i=1;$i<= $table_count;$i++){
$prime_sms_table_id_val = $prime_sms_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_sms_table_id_val === 0){
$table_query = 'insert into cw_sms_table_join (sms_table_for_id,sms_table_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 ("'.$sms_table_for_id.'","'.$sms_table_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_sms_table_join 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_sms_table_id = "'.$prime_sms_table_id_val.'"';
}
$this->db->query("CALL sp_a_run ('RUN','$table_query')");
$tab_count++;
}
echo json_encode(array('success' => true, 'message'=>"Table Join successfully Updated"));
}
function save_sms_where(){
$where_for_id = (int)$this->input->post('where_for_id');
$where_module_id = $this->input->post('where_module_id');
$where_condition = $this->input->post('where_condition');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$exist_query = 'SELECT * FROM cw_sms_table_where WHERE where_module_id = "'.$where_module_id.'" and where_for_id = "'.$where_for_id.'" and trans_status = 1';
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_count = (int)$exist_info->num_rows();
$exist_result = $exist_info->result();
$exist_info->next_result();
if($exist_count === 0){
$search_qry = 'INSERT INTO cw_sms_table_where (where_module_id, where_for_id,where_condition,trans_created_by, trans_created_date) VALUES ("'.$where_module_id.'","'.$where_for_id.'","'.$where_condition.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$search_qry')");
echo json_encode(array('success' => true,'message'=>"Where added successfully !!!"));
}else{
$prime_sms_where_id = (int)$exist_result[0]->prime_sms_where_id;
$upd_qry = 'UPDATE cw_sms_table_where SET where_module_id = "'.$where_module_id.'",where_for_id = "'.$where_for_id.'",where_condition = "'.$where_condition.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_sms_where_id = "'.$prime_sms_where_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
echo json_encode(array('success' => true,'message'=>"Where updated successfully !!!"));
}
}
// PROVIDE PICKLIST AND SESSION VALUES
function get_column_info(){
$sms_info_module_id = $this->input->post('sms_info_module_id');
$query_list_id = $this->input->post('query_list_id');
$query_column_list = $this->input->post('query_column_list');
$where_module_id = $this->input->post('where_module_id');
$query_column = $this->input->post('query_column');
$label_name = explode(".",$query_column);
if($sms_info_module_id === 'custom_approval'){
$sms_info_module_id = 'employees';
}
$get_colums_info = 'SELECT * FROM cw_form_setting WHERE prime_module_id = "'.$where_module_id.'" and label_name = "'.$label_name[1].'"';
$colums_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums_info')");
$colums_result = $colums_info->result();
$colums_info->next_result();
$session_val_qry = 'SELECT * FROM cw_session_value WHERE trans_status = 1 order by abs(session_for)';
$get_session_val = $this->db->query("CALL sp_a_run ('SELECT','$session_val_qry')");
$session_val_result = $get_session_val->result();
$get_session_val->next_result();
$session_list[""] = "--- Select Session Value ---";
if($session_val_result){
foreach($session_val_result as $col){
$col_id = (int)$col->session_for;
$session_for = "Employee";
if($col_id === 2){
$session_for = "Customer";
}
$col_value = $col->session_value;
$key_value = $col_id."|".$col_value;
$session_list[$key_value] = "$session_for - $col_value";
}
}
$pick_base_qry = 'select * from cw_form_setting where prime_module_id = "'.$sms_info_module_id.'" and label_name = "'.$query_list_id.'"';
$pick_base_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_base_qry')");
$pick_base_result = $pick_base_data->result();
$pick_base_data->next_result();
if($pick_base_result){
$prime_module_id = $pick_base_result[0]->prime_module_id;
$view_name = $pick_base_result[0]->view_name;
$field_type = (int)$pick_base_result[0]->field_type;
$pick_list_type = (int)$pick_base_result[0]->pick_list_type;
$pick_list = $pick_base_result[0]->pick_list;
$pick_table = $pick_base_result[0]->pick_table;
$same_table = "cw_".$prime_module_id;
if($pick_table === $same_table){
$get_qry = 'select * from cw_form_setting where label_name = "'.$query_column_list.'" and prime_module_id = "'.$prime_module_id.'"';
$get_data = $this->db->query("CALL sp_a_run ('SELECT','$get_qry')");
$get_result = $get_data->result();
$get_data->next_result();
if(count($get_result) === 1){
$view_name = $get_result[0]->view_name;
$field_type = (int)$get_result[0]->field_type;
$pick_list_type = (int)$get_result[0]->pick_list_type;
$pick_list = $get_result[0]->pick_list;
$pick_table = $get_result[0]->pick_table;
$pick_list_info = array();
if(($field_type === 5) || ($field_type === 7)){
$final_pick = array();
if($pick_list_type === 1){
$pick_colum_qry = 'SELECT '.$pick_list.' FROM '.$pick_table.' WHERE trans_status = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_key = array_column($pick_colum_result, $pick_list_val_1);
$pick_val = array_column($pick_colum_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}else
if($pick_list_type === 2){
$id = $pick_table."_id";
$value = $pick_table."_value";
$status = $pick_table."_status";
$select_info = "$id,$value";
$pick_colum_qry = 'SELECT '.$select_info.' FROM '.$pick_table.' WHERE '.$status.' = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_key = array_column($pick_colum_result, $id);
$pick_val = array_column($pick_colum_result, $value);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}
$value_list = $final_pick;
$status = true;
// echo json_encode(array('success'=> true,'msg'=>"Session list","value_list"=>$final_pick));
}else{
$status = false;
$value_list = "";
// echo json_encode(array('success'=> false,'msg'=>"Not Picklist column","value_list"=>""));
}
}else{
$status = false;
$value_list = "";
// echo json_encode(array('success'=> false,'msg'=>"label_name found - ".count($get_result),"value_list"=>""));
}
}else{
$pick_list_info = array();
if(($field_type === 5) || ($field_type === 7)){
$final_pick = array();
if($pick_list_type === 1){
$pick_colum_qry = 'SELECT '.$pick_list.' FROM '.$pick_table.' WHERE trans_status = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_key = array_column($pick_colum_result, $pick_list_val_1);
$pick_val = array_column($pick_colum_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}else
if($pick_list_type === 2){
$id = $pick_table."_id";
$value = $pick_table."_value";
$status = $pick_table."_status";
$select_info = "$id,$value";
$pick_colum_qry = 'SELECT '.$select_info.' FROM '.$pick_table.' WHERE '.$status.' = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_key = array_column($pick_colum_result, $id);
$pick_val = array_column($pick_colum_result, $value);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}
$status = true;
$value_list = $final_pick;
}else{
$status = false;
$value_list = "";
}
}
}else{
$status = false;
$value_list = "";
}
if($colums_result){
$field_type = (int)$colums_result[0]->field_type;
$pick_list_type = (int)$colums_result[0]->pick_list_type;
$pick_list = $colums_result[0]->pick_list;
$pick_table = $colums_result[0]->pick_table;
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_colum_qry = 'SELECT '.$pick_list.' FROM '.$pick_table.' WHERE trans_status = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$colum = explode(",",$pick_list);
foreach($pick_colum_result as $col){
$col_id = $col->$colum[0];
$col_value = $col->$colum[1];
$pick_list_info[$col_id] = "$col_id - $col_value";
}
}
}else
if($pick_list_type === 2){
$id = $pick_table."_id";
$value = $pick_table."_value";
$status = $pick_table."_status";
$select_info = "$id,$value";
$pick_colum_qry = 'SELECT '.$select_info.' FROM '.$pick_table.' WHERE '.$status.' = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
foreach($pick_colum_result as $col){
$col_id = $col->$id;
$col_value = $col->$value;
$pick_list_info[$col_id] = "$col_id - $col_value";
}
}
}
echo json_encode(array('success' => true,'type'=>'pick_list','msg'=>"Pick list value","pick_list"=>$pick_list_info,"session_list"=>$session_list,"value_list"=>$final_pick,'status' => $status));
}else{
echo json_encode(array('success' => true,'type'=>'session_list','msg'=>"Session list","session_list"=>$session_list,"value_list"=>$final_pick,'status' => $status));
}
}else{
echo json_encode(array('success' => true,'type'=>'session_list','msg'=>"Session list","session_list"=>$session_list,"value_list"=>$final_pick,'status' => $status));
}
}
/* ==============================================================*/
/* ================== sms TABLE OPEARTION - END ==============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================ sms DESIGN OPEARTION - START ==============*/
/* ==============================================================*/
public function assign_table_info(){
$assign_table_info = $this->input->post('assign_table_info');
$sms_qry = 'SELECT * FROM cw_sms_block where sms_block_for = "'.$assign_table_info.'" and trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
$assign_table_block[] = "--- Select sms block ---";
$list_view_value[""] = "--- Select list View ---";
foreach($sms_rslt as $rslt){
$prime_sms_block_id = $rslt->prime_sms_block_id;
$sms_block_type = $rslt->sms_block_type;
$sms_block_name = ucwords($rslt->sms_block_name);
$assign_table_block[$prime_sms_block_id] = $sms_block_name;
if((int)$sms_block_type === 2){
$replce_block = "@".strtolower(str_replace(" ","_",$sms_block_name))."@";
$list_view_value[$replce_block] = $sms_block_name;
}
}
$design_query = 'SELECT * FROM cw_sms_design WHERE sms_design_for = "'.$assign_table_info.'" and trans_status = 1';
$design_info = $this->db->query("CALL sp_a_run ('SELECT','$design_query')");
$design_result = $design_info->result();
$design_info->next_result();
$sms_design = "";
if($design_result){
$sms_design = $design_result[0]->sms_design;
$sms_design = str_replace('~','"',$sms_design);
}
echo json_encode(array('success' => TRUE,'assign_table_block' => $assign_table_block,'sms_design' => $sms_design,'list_view_value' => $list_view_value));
}
public function assign_table_block(){
$assign_table_block = $this->input->post('assign_table_block');
$sms_table_list = (int)$this->input->post('sms_table_list');
$sms_qry = 'SELECT * FROM cw_sms_table inner join cw_sms_block on cw_sms_block.prime_sms_block_id = cw_sms_table.sms_name_table inner join cw_sms_info on cw_sms_info.prime_sms_info_id = cw_sms_block.sms_block_for where prime_sms_info_id = "'.$assign_table_block.'" and cw_sms_table.trans_status = 1 and cw_sms_block.trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
$assign_label[""] = "--- Select label ---";
$assign_short_label[""] = "--- Select Short label ---";
$assign_value_for[""] = "--- Select Data Base value ---";
$assign_ytd_label[""] = "--- Select Cumulative value ---";
foreach($sms_rslt as $rslt){
$prime_sms_block_id = $rslt->prime_sms_table_id;
$sms_block_module_id = $rslt->sms_block_module_id;
$sms_block_for = $rslt->sms_table_list;
$sms_block_table = $rslt->sms_block_table;
$sms_block_column = $rslt->sms_table_column;
$new_table = explode(",",$sms_block_table);
$assign_type = array(0=>"--- Select assign ---",1=>"Label Name",2=>"Short Label Name",3=>"Database value",4=>"Cumulative value");
foreach($new_table as $table){
$replce_table = $table.".";
$table_list = str_replace("cw_","",$table);
$column_list = str_replace($replce_table,"",$sms_block_column);
$column_list = str_replace(",",'","', $column_list);
if($table_list === 'custom_employees'){
$table_list = 'employees';
}
$get_colums_info = 'SELECT label_name,view_name,short_name FROM cw_form_setting WHERE prime_module_id = "'.$table_list.'" and label_name in ("'.$column_list.'") and trans_status = 1 order by field_sort';
$colums_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums_info')");
$colums_result = $colums_info->result();
$colums_info->next_result();
foreach($colums_result as $colums){
$label_name = $colums->label_name;
$view_name = $colums->view_name;
$short_name = "";
if($colums->short_name){
$short_name = $colums->short_name;
}
$assign_label[$view_name] = $view_name;
$assign_short_label[$short_name] = $short_name;
$replce_column = "@".$label_name."@";
$assign_value_for[$replce_column] = $label_name." - ".$view_name ;
$replce_column_ytd = "@".$label_name."_ytd@";
$assign_ytd_label[$replce_column_ytd] = $view_name;
}
}
}
ksort($assign_type);
ksort($assign_label);
ksort($assign_short_label);
ksort($assign_value_for);
ksort($assign_ytd_label);
echo json_encode(array('success' => true,'assign_type'=>$assign_type,'assign_label'=>$assign_label,'assign_short_label'=>$assign_short_label,'assign_value_for'=>$assign_value_for,'assign_ytd_label'=>$assign_ytd_label));
}
/* ==============================================================*/
/* ================ sms DESIGN OPEARTION - END ==============*/
/* ==============================================================*/
public function get_block_table(){
$sms_block_for = (int)$this->input->post('sms_block_for');
$sms_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_sms_info` where prime_sms_info_id = \'$sms_block_for\' and trans_status = 1')");
$sms_info_rlst = $sms_info->result();
$sms_info->next_result();
if($sms_info_rlst){
$sms_type = $sms_info_rlst[0]->sms_type;
$sms_block_column[""] = "---- Select Column ----";
if((int)$sms_type === 2){
$get_colums = 'SELECT `TABLE_NAME`,`COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="'.$this->config->item("db_name").'" AND `TABLE_NAME` IN ("cw_employees","cw_transactions") AND COLUMN_NAME NOT IN ("trans_created_by","trans_created_date","trans_updated_by","trans_updated_date","trans_deleted_by","trans_deleted_date","trans_status")';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$column_result = $column_info->result();
$column_info->next_result();
foreach($column_result as $column){
$table_value = $column->TABLE_NAME;
$column_value = $column->COLUMN_NAME;
$module_name = str_replace("cw_","",$table_value);
if($table_value !== 'cw_transactions'){
$view_name_qry = 'select view_name from cw_form_setting where prime_module_id = "'.$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){
$sms_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
}else{
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_name = strtoupper($table_name);
$column_name = strtoupper(str_replace("_"," ",$column_value));
$sms_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
}
}
echo json_encode(array('success' => TRUE ,'sms_type'=>$sms_type,'sms_block_column'=>$sms_block_column));
}else{
echo json_encode(array('success' => FALSE ,'message'=>'Contact Admin...!'));
}
}
public function save_sms_design(){
$sms_design = $this->input->post('content');
$sms_design = str_replace('"',"~",$sms_design);
$sms_design_for = $this->input->post('assign_table_info');
$template_id = $this->input->post('template_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$exist_query = 'SELECT * FROM cw_sms_design WHERE sms_design_for = "'.$sms_design_for.'" and trans_status = 1';
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_count = (int)$exist_info->num_rows();
$exist_result = $exist_info->result();
$exist_info->next_result();
if($exist_count === 0){
$search_qry = 'INSERT INTO cw_sms_design (sms_design_for, sms_design,template_id,trans_created_by, trans_created_date) VALUES ("'.$sms_design_for.'","'.$sms_design.'","'.$template_id.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$search_qry')");
echo json_encode(array('success' => true,'message'=>"sms design added successfully !!!"));
}else{
$prime_sms_design_id = (int)$exist_result[0]->prime_sms_design_id;
$upd_qry = 'UPDATE cw_sms_design SET sms_design_for = "'.$sms_design_for.'",sms_design = "'.$sms_design.'",template_id = "'.$template_id.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_sms_design_id = "'.$prime_sms_design_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
echo json_encode(array('success' => true,'message'=>"sms design updated successfully !!!"));
}
}
public function sms_assign_table_info(){
$assign_table_info = $this->input->post('assign_table_info');
$sms_qry = 'SELECT * FROM cw_sms_block where prime_sms_block_id = "'.$assign_table_info.'" and trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
$assign_table_block[] = "--- Select sms block ---";
$list_view_value[""] = "--- Select list View ---";
foreach($sms_rslt as $rslt){
$prime_sms_block_id = $rslt->prime_sms_block_id;
$sms_block_type = $rslt->sms_block_type;
$sms_block_name = ucwords($rslt->sms_block_name);
$assign_table_block[$prime_sms_block_id] = $sms_block_name;
if((int)$sms_block_type === 2){
$replce_block = "@".strtolower(str_replace(" ","_",$sms_block_name))."@";
$list_view_value[$replce_block] = $sms_block_name;
}
}
$design_query = 'SELECT * FROM cw_sms_design WHERE sms_design_for = "'.$assign_table_info.'" and trans_status = 1';
$design_info = $this->db->query("CALL sp_a_run ('SELECT','$design_query')");
$design_result = $design_info->result();
$design_info->next_result();
$sms_design = "";
if($design_result){
$sms_design = $design_result[0]->sms_design;
$sms_design = str_replace('~','"',$sms_design);
$template_id = $design_result[0]->template_id;
}
echo json_encode(array('success' => TRUE,'assign_table_block' => $assign_table_block,'sms_design' => $sms_design,'list_view_value' => $list_view_value,'template_id' => $template_id));
}
public function get_query_column_list(){
$query_list_id = $this->input->post('query_list_id');
$sms_info_module_id = $this->input->post('sms_info_module_id');
if($sms_info_module_id === 'custom_approval'){
$sms_info_module_id = 'employees';
}
$pick_base_qry = 'select * from cw_form_setting where prime_module_id = "'.$sms_info_module_id.'" and label_name = "'.$query_list_id.'"';
$pick_base_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_base_qry')");
$pick_base_result = $pick_base_data->result();
$pick_base_data->next_result();
if($pick_base_result){
$pick_table = $pick_base_result[0]->pick_table;
$get_colums = 'SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="'.$this->config->item("db_name").'" AND `TABLE_NAME` = "'.$pick_table.'" AND COLUMN_NAME NOT IN ("trans_created_by","trans_created_date","trans_updated_by","trans_updated_date","trans_deleted_by","trans_deleted_date","trans_status")';
$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;
}
echo json_encode(array('success'=> true,'msg'=>"Column list","column_list"=>$column_list));
}else{
echo json_encode(array('success'=> false,'msg'=>"Unable to get Column list","column_list"=>""));
}
}
public function get_query_block_list(){
$sms_table_list = $this->input->post("sms_table_list");
$sms_info_module_id = $this->input->post("sms_info_module_id");
$sms_block_qry = 'select prime_sms_block_id,sms_block_name from cw_sms_block where sms_block_for = "'.$sms_table_list.'" and trans_status = 1';
$sms_block_info = $this->db->query("CALL sp_a_run ('SELECT','$sms_block_qry')");
$sms_block_result = $sms_block_info->result();
$sms_block_info->next_result();
$sms_block_name[] = "--- Select Column---";
foreach ($sms_block_result as $key => $value) {
$block_id = $value->prime_sms_block_id;
$block_name = $value->sms_block_name;
$sms_block_name[$block_id] = $block_name;
}
echo json_encode(array('success'=> true,'msg'=>"Column list","column_list"=>$sms_block_name));
}
public function get_query_table_column_list(){
$sms_name_table = $this->input->post("sms_name_table");
$sms_info_module_id = $this->input->post("sms_info_module_id");
$sms_block_qry = 'select * from cw_sms_block where prime_sms_block_id = "'.$sms_name_table.'" and trans_status = 1';
$sms_block_info = $this->db->query("CALL sp_a_run ('SELECT','$sms_block_qry')");
$sms_block_result = $sms_block_info->result();
$sms_block_info->next_result();
$sms_block_table = $sms_block_result[0]->sms_block_table;
$bcc_sms_type = $sms_block_result[0]->sms_block_type;
// $sms_block_table = ltrim(implode(",",$this->input->post('sms_block_table')),",");
$prime_in = '"'.str_replace(",",'","', $sms_block_table);
$custom_in = str_replace(",",'_cf","', $sms_block_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.') AND COLUMN_NAME NOT IN ("trans_created_by","trans_created_date","trans_updated_by","trans_updated_date","trans_deleted_by","trans_deleted_date","trans_status")';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$column_result = $column_info->result();
$column_info->next_result();
$sms_block_column[""] = "---- Select Column ----";
$sms_block_column_sms[""] = "---- Select Column ----";
foreach($column_result as $column){
$table_value = $column->TABLE_NAME;
$column_value = $column->COLUMN_NAME;
if($table_value === "cw_custom_employees"){//newly added
$module_name = str_replace("cw_custom_","",$table_value);
}else{
$module_name = str_replace("cw_","",$table_value);
}
$view_name_qry = 'select view_name from cw_form_setting where prime_module_id = "'.$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){
$sms_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
$view_name_sms_qry = 'select view_name from cw_form_setting where prime_module_id = "'.$module_name.'" and label_name = "'.$column_value.'" and field_type = 11 and trans_status = "1"';
$view_name_sms_info = $this->db->query("CALL sp_a_run ('SELECT','$view_name_sms_qry')");
$view_name_sms_rlst = $view_name_sms_info->result();
$view_name_sms_info->next_result();
$column_name_sms = strtoupper($view_name_sms_rlst[0]->view_name);
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_name = strtoupper($table_name);
if($column_name_sms){
$sms_block_column_sms[$table_value.".".$column_value] = $table_name . " - ". $column_name_sms;
}
}
echo json_encode(array('success' => true,'sms_block_column'=>$sms_block_column,'bcc_sms_type'=>$bcc_sms_type,'sms_block_column_sms'=>$sms_block_column_sms));
}
public function remove_sms_table_info(){
$prime_sms_table_id = (int)$this->input->post('prime_sms_table_id');
$sms_info_module_id = $this->input->post('sms_info_module_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_sms_table SET trans_status = 0,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_sms_table_id = "'.$prime_sms_table_id.'"';
$delete = $this->db->query("CALL sp_a_run ('UPDATE','$remove_qry')");
$delete->next_result();
$sms_info = $this->get_sms_table_list($sms_info_module_id);
$sms_tabl_rlst = json_decode($sms_info);
$sms_table_info_list = $sms_tabl_rlst->sms_info_list;
echo json_encode(array('success' => true, 'message'=>'sms Table deleted Successfully!!!','sms_table_info_list'=>$sms_table_info_list));
}
public function edit_sms_table_info(){
$prime_sms_table_id = $this->input->post("prime_sms_table_id");
$sms_info_module_id = $this->input->post("sms_info_module_id");
$sms_block_qry = 'select cw_sms_table.sms_table_column,cw_sms_table.sms_name_table,cw_sms_table.sms_table_list from cw_sms_table inner join cw_sms_info on cw_sms_info.prime_sms_info_id = cw_sms_table.sms_table_list inner join cw_sms_block on cw_sms_block.prime_sms_block_id = cw_sms_table.sms_name_table where cw_sms_table.prime_sms_table_id = "'.$prime_sms_table_id.'" and cw_sms_table.sms_block_module_id = "'.$sms_info_module_id.'" and cw_sms_table.trans_status = 1 and cw_sms_table.trans_status = 1';
$sms_block_info = $this->db->query("CALL sp_a_run ('SELECT','$sms_block_qry')");
$sms_block_result = $sms_block_info->result();
$sms_block_info->next_result();
$sms_info_select = $sms_block_result[0]->sms_table_list;
$sms_block_select = $sms_block_result[0]->sms_name_table;
$sms_block_table = $sms_block_result[0]->sms_table_column;
if($sms_info_module_id === 'custom_approval'){
$mail_module_id = 'employees';
}else{
$mail_module_id = $sms_info_module_id;
}
$sms_qry = 'SELECT cw_sms_info.prime_sms_info_id,cw_sms_info.sms_info_name FROM cw_sms_info inner join cw_form_setting on cw_form_setting.label_name = cw_sms_info.query_list_id where sms_info_module_id = "'.$sms_info_module_id.'" and prime_module_id = "'.$mail_module_id.'" and cw_sms_info.trans_status = 1';
$sms_info = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_info_rslt = $sms_info->result();
$sms_info->next_result();
$sms_info_name[""] = "--- Select ---";
foreach ($sms_info_rslt as $key => $value) {
$sms_info_id = $value->prime_sms_info_id;
$sms_name = $value->sms_info_name;
$sms_info_name[$sms_info_id] = $sms_name;
}
$sms_table_block_qry = 'select prime_sms_block_id,sms_block_name from cw_sms_block where sms_block_module_id = "'.$sms_info_module_id.'" and trans_status = 1';
$sms_table_block_info = $this->db->query("CALL sp_a_run ('SELECT','$sms_table_block_qry')");
$sms_table_block_rlst = $sms_table_block_info->result();
$sms_table_block_info->next_result();
$sms_block_list[""] = "--- Select ---";
foreach ($sms_table_block_rlst as $key => $value) {
$sms_info_id = $value->prime_sms_block_id;
$sms_name = $value->sms_block_name;
$sms_block_list[$sms_info_id] = $sms_name;
}
$sms_qry = 'SELECT * FROM cw_sms_table inner join cw_sms_block on cw_sms_block.prime_sms_block_id = cw_sms_table.sms_name_table where prime_sms_table_id = "'.$prime_sms_table_id.'" and cw_sms_table.trans_status = 1 and cw_sms_block.trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
$sms_block_table = $sms_rslt[0]->sms_block_table;
$prime_in = '"'.str_replace(",",'","', $sms_block_table);
$custom_in = str_replace(",",'_cf","', $sms_block_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();
$sms_block_column_sms[""] = "---- Select Column ----";
foreach($column_result as $column){
$table_value = $column->TABLE_NAME;
$column_value = $column->COLUMN_NAME;
if($table_value === "cw_custom_employees"){//newly added
$module_name = str_replace("cw_custom_","",$table_value);
}else{
$module_name = str_replace("cw_","",$table_value);
}
$view_name_sms_qry = 'select view_name from cw_form_setting where prime_module_id = "'.$module_name.'" and label_name = "'.$column_value.'" and field_type = 12 and trans_status = "1"';
$view_name_sms_info = $this->db->query("CALL sp_a_run ('SELECT','$view_name_sms_qry')");
$view_name_sms_rlst = $view_name_sms_info->result();
$view_name_sms_info->next_result();
$column_name_sms = strtoupper($view_name_sms_rlst[0]->view_name);
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_name = strtoupper($table_name);
if($column_name_sms){
$sms_block_column_sms[$table_value.".".$column_value] = $table_name . " - ". $column_name_sms;
}
}
$sms_block_column[""] = "---- Select Column ----";
foreach($column_result as $column){
$table_value = $column->TABLE_NAME;
$column_value = $column->COLUMN_NAME;
if(strpos($column_value, 'trans_') !== false) {
//echo "UDY :: $table_value - $column_value<br/>"; // UDY CHECK FOR ALTER
}else{
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$column_name = ucwords(str_replace("_"," ",$column_value));
$sms_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
}
echo json_encode(array('success' => true,'sms_info_name'=>$sms_info_name, 'sms_info_select'=> $sms_info_select , 'sms_block_list' => $sms_block_list , 'sms_block_select' => $sms_block_select , 'sms_block_column' => $sms_block_column , 'sms_block_table' => $sms_block_table,'sms_info' => $sms_rslt[0],'sms_block_column_sms'=> $sms_block_column_sms));
}
public function get_join_table_info(){
$sms_table_list = (int)$this->input->post('sms_table_join_list');
$sms_qry = 'SELECT * FROM cw_sms_block where prime_sms_block_id = "'.$sms_table_list.'" and trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
foreach($sms_rslt as $rslt){
$prime_sms_block_id = $rslt->prime_sms_block_id;
$sms_block_module_id = $rslt->sms_block_module_id;
$sms_block_for = $rslt->sms_block_for;
$sms_block_name = $rslt->sms_block_name;
$sms_block_type = $rslt->sms_block_type;
$sms_block_table = explode(",",$rslt->sms_block_table);
$sms_block_column = $rslt->sms_block_column;
$table_list = array();
$table_list[""] = "---- Select Table ----";
foreach($sms_block_table as $table_value){
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_list[$table_value] = $table_name;
}
$prime_in = '"'.str_replace(",",'","', $rslt->sms_block_table);
$custom_in = str_replace(",",'_cf","', $rslt->sms_block_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 "UDY :: $table_value - $column_value<br/>"; // UDY CHECK FOR ALTER
}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");
$sms_tab_query = 'SELECT * FROM cw_sms_table_join WHERE sms_table_for_id = "'.$prime_sms_block_id.'" AND sms_table_module_id = "'.$sms_block_module_id.'" and trans_status = 1 order by abs(line_sort)';
$sms_tab_info = $this->db->query("CALL sp_a_run ('SELECT','$sms_tab_query')");
$sms_tab_result = $sms_tab_info->result();
$sms_tab_info->next_result();
$sms_table_for_id = form_input(array( 'name' =>'sms_table_for_id','id' =>'sms_table_for_id', 'class' => 'form-control input-sm','value' =>$prime_sms_block_id,'type'=>'Hidden'));
$sms_table_module_id = form_input(array( 'name' =>'sms_table_module_id','id' =>'sms_table_module_id', 'class' => 'form-control input-sm','value' =>$sms_block_module_id,'type'=>'Hidden'));
$table_tr_line = "";
$table_count = 0;
$condition_table_count = count($sms_block_table) - 1; //round(count($condition_table)/2);
for($i=1;$i<= $condition_table_count;$i++){
$prime_sms_table_id = 0;
$line_prime_table = "";
$line_prime_col = "";
$line_join_type = "";
$line_join_table = "";
$line_join_col = "";
if($sms_tab_result){
$prime_sms_table_id = $sms_tab_result[$table_count]->prime_sms_table_id;
$line_prime_table = $sms_tab_result[$table_count]->line_prime_table;
$line_prime_col = $sms_tab_result[$table_count]->line_prime_col;
$line_join_type = $sms_tab_result[$table_count]->line_join_type;
$line_join_table = $sms_tab_result[$table_count]->line_join_table;
$line_join_col = $sms_tab_result[$table_count]->line_join_col;
}
$table_cond_for_id = form_input(array( 'name' =>"prime_sms_table_id[]",'class' => 'form-control input-sm','value' =>$prime_sms_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++;
}
$table_content = "$sms_table_for_id $sms_table_module_id
<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_sms_table'>Save</button>
</div>";
}
$where_query = 'SELECT * FROM cw_sms_table_where WHERE where_module_id = "'.$sms_block_module_id.'" and where_for_id = "'.$prime_sms_block_id.'" and trans_status = 1';
$where_info = $this->db->query("CALL sp_a_run ('SELECT','$where_query')");
$where_result = $where_info->result();
$where_info->next_result();
if($where_result){
$where_condition = $where_result[0]->where_condition;
}else{
$where_condition = "and";
}
echo json_encode(array('success' => true, 'prime_sms_block_id'=>$prime_sms_block_id, 'sms_block_module_id'=>$sms_block_module_id,'sms_table_block'=>$table_content, 'column_list'=>$column_list,'where_condition'=>$where_condition,));
}
public function get_content_join_table_info(){
$sms_content_table_list = (int)$this->input->post('sms_content_table_join_list');
$sms_qry = 'SELECT * FROM cw_sms_block where prime_sms_block_id = "'.$sms_content_table_list.'" and trans_status = 1';
$sms_data = $this->db->query("CALL sp_a_run ('SELECT','$sms_qry')");
$sms_rslt = $sms_data->result();
$sms_data->next_result();
foreach($sms_rslt as $rslt){
$prime_sms_block_id = $rslt->prime_sms_block_id;
$sms_block_module_id = $rslt->sms_block_module_id;
$sms_block_for = $rslt->sms_block_for;
$sms_block_name = $rslt->sms_block_name;
$sms_block_type = $rslt->sms_block_type;
$sms_block_table = explode(",",$rslt->sms_block_table);
$sms_block_column = $rslt->sms_block_column;
$table_list = array();
$table_list[""] = "---- Select Table ----";
foreach($sms_block_table as $table_value){
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_list[$table_value] = $table_name;
}
$prime_in = '"'.str_replace(",",'","', $rslt->sms_block_table);
$custom_in = str_replace(",",'_cf","', $rslt->sms_block_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 "UDY :: $table_value - $column_value<br/>"; // UDY CHECK FOR ALTER
}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");
$sms_tab_query = 'SELECT * FROM cw_sms_content_table_join WHERE sms_content_table_for_id = "'.$prime_sms_block_id.'" AND sms_content_table_module_id = "'.$sms_block_module_id.'" and trans_status = 1 order by abs(line_sort_content)';
$sms_tab_info = $this->db->query("CALL sp_a_run ('SELECT','$sms_tab_query')");
$sms_tab_result = $sms_tab_info->result();
$sms_tab_info->next_result();
$sms_table_for_id = form_input(array( 'name' =>'sms_content_table_for_id','id' =>'sms_content_table_for_id', 'class' => 'form-control input-sm','value' =>$prime_sms_block_id,'type'=>'Hidden'));
$sms_table_module_id = form_input(array( 'name' =>'sms_content_table_module_id','id' =>'sms_content_table_module_id', 'class' => 'form-control input-sm','value' =>$sms_block_module_id,'type'=>'Hidden'));
$table_tr_line = "";
$table_count = 0;
$condition_table_count = count($sms_block_table) - 1; //round(count($condition_table)/2);
for($i=1;$i<= $condition_table_count;$i++){
$prime_sms_table_id = 0;
$line_prime_table = "";
$line_prime_col = "";
$line_join_type = "";
$line_join_table = "";
$line_join_col = "";
if($sms_tab_result){
$prime_sms_table_id = $sms_tab_result[$table_count]->prime_sms_content_table_id;
$line_prime_table = $sms_tab_result[$table_count]->line_prime_table_content;
$line_prime_col = $sms_tab_result[$table_count]->line_prime_col_content;
$line_join_type = $sms_tab_result[$table_count]->line_join_type_content;
$line_join_table = $sms_tab_result[$table_count]->line_join_table_content;
$line_join_col = $sms_tab_result[$table_count]->line_join_col_content;
}
$table_cond_for_id = form_input(array( 'name' =>"prime_sms_content_table_id[]",'class' => 'form-control input-sm','value' =>$prime_sms_table_id,'type'=>'Hidden'));
$prime_table_data = form_dropdown(array('name' =>"line_prime_table_content[]",'class' => 'form-control input-sm'), $table_list,$line_prime_table);
$prime_col_data = form_dropdown(array('name' =>"line_prime_col_content[]",'class' => 'form-control input-sm'),$column_list,$line_prime_col);
$join_data = form_dropdown(array('name' =>"line_join_type_content[]",'class' => 'form-control input-sm'),$join_array,$line_join_type);
$join_table_data = form_dropdown(array('name' =>"line_join_table_content[]",'class' => 'form-control input-sm'), $table_list,$line_join_table);
$join_col_data = form_dropdown(array('name' =>"line_join_col_content[]",'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 = "$sms_table_for_id $sms_table_module_id
<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_sms_table'>Save</button>
</div>";
}
$where_query = 'SELECT * FROM cw_sms_content_table_where WHERE content_where_module_id = "'.$sms_block_module_id.'" and content_where_for_id = "'.$prime_sms_block_id.'" and trans_status = 1';
$where_info = $this->db->query("CALL sp_a_run ('SELECT','$where_query')");
$where_result = $where_info->result();
$where_info->next_result();
if($where_result){
$where_condition = $where_result[0]->where_condition;
}else{
$where_condition = "and";
}
echo json_encode(array('success' => true, 'prime_sms_block_id'=>$prime_sms_block_id, 'sms_block_module_id'=>$sms_block_module_id,'sms_table_block'=>$table_content, 'column_list'=>$column_list,'where_condition'=>$where_condition,));
}
public function save_sms_content_table(){
$sms_table_for_id = $this->input->post('sms_content_table_for_id');
$sms_table_module_id = $this->input->post('sms_content_table_module_id');
$prime_sms_table_id = $this->input->post('prime_sms_content_table_join_id[]');
$line_prime_table = $this->input->post('line_prime_table_content[]');
$line_prime_col = $this->input->post('line_prime_col_content[]');
$line_join_type = $this->input->post('line_join_type_content[]');
$line_join_table = $this->input->post('line_join_table_content[]');
$line_join_col = $this->input->post('line_join_col_content[]');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$tab_count = 0;
$remove_query = 'UPDATE cw_sms_content_table_join SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE sms_content_table_for_id = "'.$sms_table_for_id.'" and sms_content_table_module_id = "'.$sms_table_module_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$remove_query')");
$table_count = count($line_prime_table);
for($i=1;$i<= $table_count;$i++){
$prime_sms_table_id_val = $prime_sms_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_sms_table_id_val === 0){
$table_query = 'insert into cw_sms_content_table_join (sms_content_table_for_id,sms_content_table_module_id,line_prime_table_content,line_prime_col_content,line_join_type_content,line_join_table_content,line_join_col_content,line_sort_content,trans_created_by,trans_created_date) value ("'.$sms_table_for_id.'","'.$sms_table_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_sms_content_table_join SET trans_status = 1, line_prime_table_content = "'.$line_prime_table_val.'",line_prime_col_content = "'.$line_prime_col_val.'",line_join_type_content = "'.$line_join_type_val.'",line_join_table_content = "'.$line_join_table_val.'",line_join_col_content = "'.$line_join_col_val.'",line_sort_content = "'.$i.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE prime_sms_content_table_id = "'.$prime_sms_table_id_val.'"';
}
$this->db->query("CALL sp_a_run ('RUN','$table_query')");
$tab_count++;
}
echo json_encode(array('success' => true, 'message'=>"Table Join successfully Updated"));
}
function get_content_column_info(){
$sms_info_module_id = $this->input->post('sms_info_module_id');
$query_list_id = $this->input->post('query_list_id');
$query_column_list = $this->input->post('content_query_column_list');
$where_module_id = $this->input->post('content_where_module_id');
$query_column = $this->input->post('content_query_column');
$label_name = explode(".",$query_column);
if($sms_info_module_id === 'custom_approval'){
$sms_info_module_id = 'employees';
}
$get_colums_info = 'SELECT * FROM cw_form_setting WHERE prime_module_id = "'.$where_module_id.'" and label_name = "'.$label_name[1].'"';
$colums_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums_info')");
$colums_result = $colums_info->result();
$colums_info->next_result();
$session_val_qry = 'SELECT * FROM cw_session_value WHERE trans_status = 1 order by abs(session_for)';
$get_session_val = $this->db->query("CALL sp_a_run ('SELECT','$session_val_qry')");
$session_val_result = $get_session_val->result();
$get_session_val->next_result();
$session_list[""] = "--- Select Session Value ---";
if($session_val_result){
foreach($session_val_result as $col){
$col_id = (int)$col->session_for;
$session_for = "Employee";
if($col_id === 2){
$session_for = "Customer";
}
$col_value = $col->session_value;
$key_value = $col_id."|".$col_value;
$session_list[$key_value] = "$session_for - $col_value";
}
}
$pick_base_qry = 'select * from cw_form_setting where prime_module_id = "'.$sms_info_module_id.'" and label_name = "'.$query_list_id.'"';
$pick_base_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_base_qry')");
$pick_base_result = $pick_base_data->result();
$pick_base_data->next_result();
if($pick_base_result){
$prime_module_id = $pick_base_result[0]->prime_module_id;
$view_name = $pick_base_result[0]->view_name;
$field_type = (int)$pick_base_result[0]->field_type;
$pick_list_type = (int)$pick_base_result[0]->pick_list_type;
$pick_list = $pick_base_result[0]->pick_list;
$pick_table = $pick_base_result[0]->pick_table;
$same_table = "cw_".$prime_module_id;
if($pick_table === $same_table){
$get_qry = 'select * from cw_form_setting where label_name = "'.$query_column_list.'" and prime_module_id = "'.$prime_module_id.'"';
$get_data = $this->db->query("CALL sp_a_run ('SELECT','$get_qry')");
$get_result = $get_data->result();
$get_data->next_result();
if(count($get_result) === 1){
$view_name = $get_result[0]->view_name;
$field_type = (int)$get_result[0]->field_type;
$pick_list_type = (int)$get_result[0]->pick_list_type;
$pick_list = $get_result[0]->pick_list;
$pick_table = $get_result[0]->pick_table;
$pick_list_info = array();
if(($field_type === 5) || ($field_type === 7)){
$final_pick = array();
if($pick_list_type === 1){
$pick_colum_qry = 'SELECT '.$pick_list.' FROM '.$pick_table.' WHERE trans_status = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_key = array_column($pick_colum_result, $pick_list_val_1);
$pick_val = array_column($pick_colum_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}else
if($pick_list_type === 2){
$id = $pick_table."_id";
$value = $pick_table."_value";
$status = $pick_table."_status";
$select_info = "$id,$value";
$pick_colum_qry = 'SELECT '.$select_info.' FROM '.$pick_table.' WHERE '.$status.' = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_key = array_column($pick_colum_result, $id);
$pick_val = array_column($pick_colum_result, $value);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}
$value_list = $final_pick;
$status = true;
}else{
$status = false;
$value_list = "";
}
}else{
$status = false;
$value_list = "";
}
}else{
$pick_list_info = array();
if(($field_type === 5) || ($field_type === 7)){
$final_pick = array();
if($pick_list_type === 1){
$pick_colum_qry = 'SELECT '.$pick_list.' FROM '.$pick_table.' WHERE trans_status = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_key = array_column($pick_colum_result, $pick_list_val_1);
$pick_val = array_column($pick_colum_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}else
if($pick_list_type === 2){
$id = $pick_table."_id";
$value = $pick_table."_value";
$status = $pick_table."_status";
$select_info = "$id,$value";
$pick_colum_qry = 'SELECT '.$select_info.' FROM '.$pick_table.' WHERE '.$status.' = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$pick_key = array_column($pick_colum_result, $id);
$pick_val = array_column($pick_colum_result, $value);
$final_pick = array_combine( $pick_key, $pick_val);
$final_pick = array("" => "---- $view_name ----") + $final_pick;
}
}
$status = true;
$value_list = $final_pick;
}else{
$status = false;
$value_list = "";
}
}
}else{
$status = false;
$value_list = "";
}
if($colums_result){
$field_type = (int)$colums_result[0]->field_type;
$pick_list_type = (int)$colums_result[0]->pick_list_type;
$pick_list = $colums_result[0]->pick_list;
$pick_table = $colums_result[0]->pick_table;
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_colum_qry = 'SELECT '.$pick_list.' FROM '.$pick_table.' WHERE trans_status = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
$colum = explode(",",$pick_list);
foreach($pick_colum_result as $col){
$col_id = $col->$colum[0];
$col_value = $col->$colum[1];
$pick_list_info[$col_id] = "$col_id - $col_value";
}
}
}else
if($pick_list_type === 2){
$id = $pick_table."_id";
$value = $pick_table."_value";
$status = $pick_table."_status";
$select_info = "$id,$value";
$pick_colum_qry = 'SELECT '.$select_info.' FROM '.$pick_table.' WHERE '.$status.' = 1';
$get_pick_colum = $this->db->query("CALL sp_a_run ('SELECT','$pick_colum_qry')");
$pick_colum_result = $get_pick_colum->result();
$get_pick_colum->next_result();
if($pick_colum_result){
foreach($pick_colum_result as $col){
$col_id = $col->$id;
$col_value = $col->$value;
$pick_list_info[$col_id] = "$col_id - $col_value";
}
}
}
echo json_encode(array('success' => true,'type'=>'pick_list','msg'=>"Pick list value","pick_list"=>$pick_list_info,"session_list"=>$session_list,"value_list"=>$final_pick,'status' => $status));
}else{
echo json_encode(array('success' => true,'type'=>'session_list','msg'=>"Session list","session_list"=>$session_list,"value_list"=>$final_pick,'status' => $status));
}
}else{
echo json_encode(array('success' => true,'type'=>'session_list','msg'=>"Session list","session_list"=>$session_list,"value_list"=>$final_pick,'status' => $status));
}
}
function save_sms_content_where(){
$where_for_id = (int)$this->input->post('content_where_for_id');
$where_module_id = $this->input->post('content_where_module_id');
$where_condition = $this->input->post('content_where_condition');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$exist_query = 'SELECT * FROM cw_sms_content_table_where WHERE content_where_module_id = "'.$where_module_id.'" and content_where_for_id = "'.$where_for_id.'" and trans_status = 1';
$exist_info = $this->db->query("CALL sp_a_run ('SELECT','$exist_query')");
$exist_count = (int)$exist_info->num_rows();
$exist_result = $exist_info->result();
$exist_info->next_result();
if($exist_count === 0){
$search_qry = 'INSERT INTO cw_sms_content_table_where (content_where_module_id, content_where_for_id,content_where_condition,trans_created_by, trans_created_date) VALUES ("'.$where_module_id.'","'.$where_for_id.'","'.$where_condition.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$search_qry')");
echo json_encode(array('success' => true,'message'=>"Where added successfully !!!"));
}else{
$prime_sms_where_id = (int)$exist_result[0]->prime_sms_where_id;
$upd_qry = 'UPDATE cw_sms_content_table_where SET content_where_module_id = "'.$where_module_id.'",content_where_for_id = "'.$where_for_id.'",content_where_condition = "'.$where_condition.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_sms_content_where_id = "'.$prime_sms_where_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
echo json_encode(array('success' => true,'message'=>"Where updated successfully !!!"));
}
}
}
?>