File: /home/cafsindia/uds.cafsinfotech.in/Dump/ZRM/application_bk28jun2024/controllers/Email_setting.php
<?php
/**********************************************************
Filename: email Setting
Description: email Setting for creating new Dynamic email 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 email_setting extends Secure_Controller{
public function __construct(){
parent::__construct('email_setting');
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$data['table_headers']=$this->xss_clean(get_form_setting_headers());
$this->load->view('email_setting/manage',$data);
}
/* ==============================================================*/
/* ================== email COMMON OPEARTION - START ============*/
/* ==============================================================*/
//email SEARCH OPEARTION
public function search(){
$search = $this->input->get('search');
$limit = $this->input->get('limit');
$offset = $this->input->get('offset');
$sort = $this->input->get('sort');
$order = $this->input->get('order');
if(!$sort){
$sort = "abs(menu_sort)";
}
if(!$order){
$order = "asc";
}
// Fetch Records
$info = $this->db->query("CALL sp_email_setting_search ('SEARCH')");
$result = $info->result();
$info->next_result();
$data_rows = array();
foreach ($result as $form_setting){
$data_rows[]=get_form_setting_datarows($form_setting,$this);
}
$data_rows=$this->xss_clean($data_rows);
// Fetch Records Count
$count_info = $this->db->query("CALL sp_email_setting_search ('COUNT')");
$count_result = $count_info->result();
$count_info->next_result();
$num_rows = $count_result[0]->data_count;
echo json_encode(array('total'=>$num_rows,'rows'=>$data_rows));
}
// MODEL VIEW OPEARTION
public function view($email_info_module_id){
if(!$email_info_module_id){
$email_info_module_id = 0;
}
$data['email_info_module_id'] = $email_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();
$email_for[""] = "---- Module For ----";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$email_for[$role_id] = $category_name;
}
$data['email_for'] = $email_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_email_block","cw_mail_design","cw_email_info","cw_email_map","cw_email_table","cw_email_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;
$email_info = $this->get_email_info_list($email_info_module_id);
$email_info_rslt = json_decode($email_info);
$data['email_info_list'] = $email_info_rslt->email_info_list;
$data['email_block_for'] = json_decode(json_encode($email_info_rslt->email_block_for), True);
$email_block = $this->get_email_block_list($email_info_module_id);
$email_block_rslt = json_decode($email_block);
$data['email_block_list'] = $email_block_rslt->email_block_list;
$data['email_table_list'] = json_decode(json_encode($email_block_rslt->email_table_list), True);
// $data['split_table_list'] = $this->get_split_list($email_info_module_id);
$email_tabl_data = $this->get_email_table_list($email_info_module_id);
$email_tabl_rlst = json_decode($email_tabl_data);
$data['email_table_info_list'] = $email_tabl_rlst->email_info_list;
if($email_info_module_id === 'custom_approval'){
$mail_module_id = 'employees';
}else{
$mail_module_id = $email_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("email_setting/email_info",$data);
}
/* ==============================================================*/
/* ================== email COMMON OPEARTION - END ============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================== email BASIC OPEARTION - START ============*/
/* ==============================================================*/
// email BASIC SAVE
public function save_email_info(){
$prime_email_info_id = (int)$this->input->post('prime_email_info_id');
$email_info_module_id = $this->input->post('email_info_module_id');
$email_info_name = $this->input->post('email_info_name');
$email_info_for = ltrim(implode(",",$this->input->post('email_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');
$email_attach_type = (int)$this->input->post('email_attach_type');
$email_attach_column = $this->input->post('email_attach_column');
$process_type = $this->input->post('process_type');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
if($prime_email_info_id === 0){
if(!$this->check_email_info_for_already_exists($email_info_for,$email_info_module_id,$query_list_id,$table_values)){
$is_exist_qry = 'SELECT count(*) as rslt_count FROM cw_email_info where email_info_module_id = "'.$email_info_module_id.'" and email_info_name = "'.$email_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){
$email_qry = 'INSERT INTO cw_email_info (email_info_module_id, email_info_name,email_info_for,query_list_id,query_column_list,table_values,email_attach_type,email_attach_column,process_type,trans_created_by, trans_created_date) VALUES ("'.$email_info_module_id.'","'.$email_info_name.'","'.$email_info_for.'","'.$query_list_id.'","'.$query_column_list.'","'.$table_values.'","'.$email_attach_type.'","'.$email_attach_column.'","'.$process_type.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$email_qry')");
$email_info = $this->get_email_info_list($email_info_module_id);
$email_info_rslt = json_decode($email_info);
$email_info_list = $email_info_rslt->email_info_list;
$email_block_for = $email_info_rslt->email_block_for;
echo json_encode(array('success' => true, 'message' => "email info successfully added", 'email_info_list' => $email_info_list, 'email_block_for' => $email_block_for));
}else{
echo json_encode(array('success' => FALSE, 'message' => "email info already exist"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "email info already created for this email For Check it"));
}
}else{
if(!$this->check_email_info_for_already_exists($email_info_for,$email_info_module_id,$query_list_id,$table_values,$prime_email_info_id)){
$upd_qry = 'UPDATE cw_email_info SET email_info_module_id = "'.$email_info_module_id.'",email_info_name = "'.$email_info_name.'",email_info_for = "'.$email_info_for.'",query_list_id = "'.$query_list_id.'",query_column_list = "'.$query_column_list.'",table_values = "'.$table_values.'",email_attach_type = "'.$email_attach_type.'",email_attach_column = "'.$email_attach_column.'",process_type = "'.$process_type.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_email_info_id = "'.$prime_email_info_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$email_info = $this->get_email_info_list($email_info_module_id);
$email_info_rslt = json_decode($email_info);
$email_info_list = $email_info_rslt->email_info_list;
$email_block_for = $email_info_rslt->email_block_for;
echo json_encode(array('success' => true, 'message' => "email info successfully updated", 'email_info_list' => $email_info_list, 'email_block_for' => $email_block_for));
}else{
echo json_encode(array('success' => FALSE, 'message' => "email info already created for this email For Check it"));
}
}
}
//CHECKING email INFO FOR ALREADY EXISTS
public function check_email_info_for_already_exists($email_info_for,$email_info_module_id,$query_list_id,$table_values,$prime_email_info_id = -1){
$email_info_for = explode(',', $email_info_for);
$query ='';
foreach($email_info_for as $rslt){
$query .= "email_info_for LIKE \"%".$rslt."%\" OR email_info_for LIKE \"".$rslt."%\" OR email_info_for LIKE \"%".$rslt."\" OR ";
}
$query = rtrim($query,"OR ");
$exist_query = 'SELECT * FROM cw_email_info where ('.$query.') and query_list_id = "'.$query_list_id.'" and table_values = "'.$table_values.'" and email_info_module_id = "'.$email_info_module_id.'" and trans_status = 1';
if((int)$prime_email_info_id > 0){
$exist_query .=' and prime_email_info_id != "'.$prime_email_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 email BASIC LIST
public function get_email_info_list($email_info_module_id){
if($email_info_module_id === 'custom_approval'){
$mail_module_id = 'employees';
}else{
$mail_module_id = $email_info_module_id;
}
$email_qry = 'SELECT * FROM cw_email_info left join cw_form_setting on cw_form_setting.label_name = cw_email_info.query_list_id where email_info_module_id = "'.$email_info_module_id.'" and prime_module_id in ("'.$mail_module_id.'","null") and cw_email_info.trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_data->next_result();
$tr_line = "";
$email_block_for[] = "--- Choose one ---";
foreach($email_rslt as $rslt){
$prime_email_info_id = $rslt->prime_email_info_id;
$email_info_module_id = $rslt->email_info_module_id;
$email_info_name = ucwords($rslt->email_info_name);
$email_info_for = $rslt->email_info_for;
$email_type = $rslt->email_type;
$email_based_on = $rslt->email_based_on;
$label_name = $rslt->label_name;
$table_values = $rslt->table_values;
$email_attach_type = $rslt->email_attach_type;
$email_process_type = (int)$rslt->process_type;
$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];
$email_block_for[$prime_email_info_id] = $email_info_name;
if((int)$email_attach_type === 1){
$email_attach_types = "Yes";
}else
if((int)$email_attach_type === 2){
$email_attach_types = "No";
}else{
$email_attach_types = "";
}
//FOR EMAIL TYPE MANUAL
if($email_process_type === 1){
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;
}
$email_for_qry = 'SELECT GROUP_CONCAT(category_name) as category_name FROM cw_category where prime_category_id in ('.$email_info_for.')';
$email_for_data = $this->db->query("CALL sp_a_run ('SELECT','$email_for_qry')");
$email_for_rslt = $email_for_data->result();
$email_for_data->next_result();
$category_name = $email_for_rslt[0]->category_name;
$tr_line .= "<tr>
<td>$email_info_name</td>
<td>$category_name</td>
<td>$query_list_id</td>
<td>$query_column_list</td>
<td>$table_values</td>
<td>$email_attach_types</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=edit_email_info('$prime_email_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_email_info('$prime_email_info_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$email_info_list = "<table class='table table-bordered table-stripted' id='email_info_table'>
<tr class='inline_head'>
<th>email Name</th>
<th>email For</th>
<th>Pick List</th>
<th>Pick List Column</th>
<th>Table Value</th>
<th>Attach Type</th>
<th>Edit</th>
<th>Delete</th>
</tr>
$tr_line
</table>";
return json_encode(array('success' => TRUE,'email_info_list' => $email_info_list,'email_block_for'=>$email_block_for));
}
public function get_email_table_list($email_info_module_id){
$email_qry = 'SELECT prime_email_table_id,email_table_for_id,email_info_name,email_table_column,email_table_email_column,email_name_table,cw_email_table.email_block_module_id,cw_email_block.email_block_name FROM cw_email_table inner join cw_email_info on cw_email_info.prime_email_info_id = cw_email_table.email_table_list inner join cw_email_block on cw_email_block.prime_email_block_id = cw_email_table.email_name_table where cw_email_table.email_block_module_id = "'.$email_info_module_id.'" and cw_email_table.trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_data->next_result();
$tr_line = "";
$email_block_for[] = "--- Choose one ---";
foreach($email_rslt as $rslt){
$prime_email_table_id = $rslt->prime_email_table_id;
$email_table_for_id = $rslt->email_table_for_id;
$email_name_table = $rslt->email_info_name;
$email_table_column = $rslt->email_table_column;
$email_table_email_column = $rslt->email_table_email_column;
$email_block_module_id = $rslt->email_block_module_id;
$email_block_name = $rslt->email_block_name;
$tr_line .= "<tr>
<td>$email_name_table</td>
<td>$email_block_name</td>
<td>$email_table_column</td>
<td>$email_table_email_column</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=edit_email_table_info('$prime_email_table_id','$email_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_email_table_info('$prime_email_table_id','$email_info_module_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$email_info_list = "<table class='table table-bordered table-stripted' id='email_table_list_info'>
<tr class='inline_head'>
<th>Email Info</th>
<th>Email Name</th>
<th>Table Column</th>
<th>Email Column</th>
<th>Edit</th>
<th>Delete</th>
</tr>
$tr_line
</table>";
return json_encode(array('success' => TRUE,'email_info_list' => $email_info_list,'email_block_for'=>$email_block_for));
}
//EDIT email INFO OPERATION
public function edit_email_info(){
$prime_email_info_id = (int)$this->input->post('prime_email_info_id');
$email_info_module_id = $this->input->post('email_info_module_id');
$email_qry = 'SELECT * FROM cw_email_info where prime_email_info_id = "'.$prime_email_info_id.'" and trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_data->next_result();
$query_list_id = $email_rslt[0]->query_list_id;
$query_column_list = $email_rslt[0]->query_column_list;
$query_list_id = $email_rslt[0]->query_list_id;
if($email_info_module_id === 'custom_approval'){
$email_info_module_id = 'employees';
}
$pick_base_qry = 'select * from cw_form_setting where label_name = "'.$query_list_id.'" and prime_module_id = "'.$email_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 = "'.$email_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 = "'.$email_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,'email_info' => $email_rslt[0],"column_list"=>$column_list,"value_list"=>$final_pick,'print_column'=>$print_column));
}
//REMOVE email INFO OPERATION
public function remove_email_info(){
$prime_email_info_id = (int)$this->input->post('prime_email_info_id');
$email_info_module_id = $this->input->post('email_info_module_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_email_info SET trans_status = 0,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_email_info_id = "'.$prime_email_info_id.'"';
$delete = $this->db->query("CALL sp_a_run ('UPDATE','$remove_qry')");
$delete->next_result();
$email_info = $this->get_email_info_list($email_info_module_id);
$email_info_rslt = json_decode($email_info);
$email_info_list = $email_info_rslt->email_info_list;
$email_block_for = $email_info_rslt->email_block_for;
echo json_encode(array('success' => true, 'message'=>'email Info deleted Successfully!!!','email_info_list'=>$email_info_list,'email_block_for'=>$email_block_for));
}
/* ==============================================================*/
/* =================== email BASIC OPEARTION - END =============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================ email BLOCK OPEARTION - START ==============*/
/* ==============================================================*/
// email BLOCK SAVE
public function save_email_block(){
$prime_email_block_id = (int)$this->input->post('prime_email_block_id');
$email_block_module_id = $this->input->post('email_block_module_id');
$email_block_name = $this->input->post('email_block_name');
$email_block_for = $this->input->post('email_block_for');
$email_block_type = $this->input->post('email_block_type');
$email_block_table = ltrim(implode(",",$this->input->post('email_block_table[]')),",");
$email_block_column = $this->input->post('email_block_column');
$suppressed_data = $this->input->post('suppressed_data');
$cumulative_data = $this->input->post('cumulative_data');
if($prime_email_block_id === 0){
$is_exist_qry = 'SELECT count(*) as rslt_count FROM cw_email_block where email_block_for = "'.$email_block_for.'" and email_block_type = "'.$email_block_type.'" and email_block_table = "'.$email_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_email_block_primary_already_exists($email_block_for,$email_block_type,$email_block_name,$prime_email_block_id)){
if(!$this->check_email_block_already_exists($email_block_for,$email_block_type,$email_block_name,$prime_email_block_id)){
$email_qry = 'INSERT INTO cw_email_block (email_block_module_id, email_block_name,email_block_for,email_block_type,email_block_table,email_block_column,trans_created_by, trans_created_date) VALUES ("'.$email_block_module_id.'","'.$email_block_name.'","'.$email_block_for.'","'.$email_block_type.'","'.$email_block_table.'","'.$email_block_column.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$email_qry')");
$email_block = $this->get_email_block_list($email_block_module_id);
$email_block_rslt = json_decode($email_block);
$email_block_list = $email_block_rslt->email_block_list;
$email_table_list = $email_block_rslt->email_table_list;
echo json_encode(array('success' => true, 'message' => "email block successfully added", 'email_block_list' => $email_block_list, 'email_table_list' => $email_table_list));
}else{
echo json_encode(array('success' => FALSE, 'message' => "email block already exist"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Primary Mandatory"));
}
}else{
if($this->check_email_block_primary_already_exists($email_block_for,$email_block_type,$email_block_name,$prime_email_block_id)){
if(!$this->check_email_block_already_exists($email_block_for,$email_block_type,$email_block_name,$prime_email_block_id)){
$upd_qry = 'UPDATE cw_email_block SET email_block_for = "'.$email_block_for.'",email_block_type = "'.$email_block_type.'",email_block_name = "'.$email_block_name.'",email_block_for = "'.$email_block_for.'",email_block_table = "'.$email_block_table.'",email_block_column = "'.$email_block_column.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_email_block_id = "'.$prime_email_block_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$email_block = $this->get_email_block_list($email_block_module_id);
$email_block_rslt = json_decode($email_block);
$email_block_list = $email_block_rslt->email_block_list;
$email_table_list = $email_block_rslt->email_table_list;
echo json_encode(array('success' => true, 'message' => "email block successfully updated", 'email_block_list' => $email_block_list, 'email_table_list' => $email_table_list));
}
else{
echo json_encode(array('success' => FALSE, 'message' => "email block already exist"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Primary Mandatory"));
}
}
}
public function check_email_block_already_exists($email_block_for,$email_block_type,$email_block_name,$prime_email_block_id = -1){
$is_exist_qry = 'SELECT * FROM cw_email_block where email_block_for = "'.$email_block_for.'" and trans_status = 1 ';
if((int)$email_block_type === 1){
$is_exist_qry .= " and email_block_type = $email_block_type";
}else{
$is_exist_qry .= ' and email_block_name = "'.$email_block_name.'"';
}
if((int)$prime_email_block_id > 0){
$is_exist_qry .= " and prime_email_block_id != $prime_email_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_email_block_primary_already_exists($email_block_for,$email_block_type,$email_block_name,$prime_email_block_id = -1){
if((int)$email_block_type === 2){
$is_exist_qry = 'SELECT * FROM cw_email_block where email_block_for = "'.$email_block_for.'" and trans_status = 1 ';
if((int)$email_block_type === 2){
$is_exist_qry .= "and email_block_type =1";
}
if((int)$prime_email_block_id > 0){
$is_exist_qry .= " and prime_email_block_id != $prime_email_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 email BLOCK LIST
public function get_email_block_list($email_block_module_id){
$email_qry = 'SELECT * FROM cw_email_info left join cw_email_block on cw_email_info.prime_email_info_id = cw_email_block.email_block_for where cw_email_block.email_block_module_id = "'.$email_block_module_id.'" and cw_email_info.trans_status = 1 and cw_email_block.trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_data->next_result();
$tr_line = "";
$email_table_list[] = "--- Choose one ---";
$email_block_type_list = array("1"=>"Primary","2"=>"Secondary",);
foreach($email_rslt as $rslt){
$email_info_name = ucwords($rslt->email_info_name);
$prime_email_block_id = $rslt->prime_email_block_id;
$email_block_name = ucwords($rslt->email_block_name);
$email_block_type = $rslt->email_block_type;
$email_block_table = explode(",",$rslt->email_block_table);
$email_block_column = explode(",",$rslt->email_block_column);
$table_td = "";
foreach($email_block_table as $table){
$table_td .= "<tr><td>$table</td><tr>";
}
$table_list = "<table> $table_td </table>";
$column_td = "";
foreach($email_block_column as $column){
$column_td .= "<tr><td>$column</td><tr>";
}
$column_list = "<table> $column_td </table>";
$email_block_type_name = $email_block_type_list[$email_block_type];
$email_table_list[$prime_email_block_id] = $email_info_name ." - ".$email_block_name;
$tr_line .= "<tr>
<td>$email_info_name</td>
<td>$email_block_name</td>
<td>$table_list</td>
<td>$column_list</td>
<td>$email_block_type_name</td><td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=edit_email_block('$prime_email_block_id')> <i class='fa fa-pencil-square-o' aria-hidden='true'></i> Edit</a></td>";
if((int)$email_block_type === 1){
$tr_line .= "<td></td>";
}else{
$tr_line .= "<td style='text-align:center;'><a class='btn btn-xs btn-danger' onclick=remove_email_block('$prime_email_block_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
}
$email_block_list = "<table class='table table-bordered table-stripted' id='email_info_table'>
<tr class='inline_head'>
<th>Email Info Name</th>
<th>Email Name</th>
<th>Email 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,'email_block_list' => $email_block_list,'email_table_list' => $email_table_list));
}
//email BLOCK COLUMNS
public function get_email_block_table(){
$email_block_table = ltrim(implode(",",$this->input->post('email_block_table')),",");
$prime_in = '"'.str_replace(",",'","', $email_block_table);
$custom_in = str_replace(",",'_cf","', $email_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();
$get_module_column = str_replace('cw_','', $table_in);
$get_module_column = str_replace('transactions','employees',$get_module_column);
$get_module_column = str_replace('monthly_input','employees',$get_module_column);
$view_name_qry = 'select view_name,label_name,prime_module_id from cw_form_setting where prime_module_id in ('.$get_module_column.') and trans_status = "1" group by prime_module_id,label_name';
$view_name_data = $this->db->query("CALL sp_a_run ('SELECT','$view_name_qry')");
$view_name_result = $view_name_data->result_array();
$view_name_data->next_result();
$view_name_result = array_reduce($view_name_result, function($result, $arr){
$result[$arr['prime_module_id']][$arr['label_name']] = $arr;
return $result;
}, array());
$email_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) {
}else{
$tbl_name = str_replace('cw_', '', $table_value);
if($tbl_name === "transactions" || $tbl_name === "monthly_input"){
$tbl_name = "employees";
}
$column_name = strtoupper($view_name_result[$tbl_name][$column_value]['view_name']);
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_name = strtoupper($table_name);
$email_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
if(!$column_name){
$email_block_column[$table_value.".".$column_value] = $table_name . " - ". strtoupper(str_replace("_"," ",$column_value));
}
}
}
echo json_encode(array('success' => true,'email_block_column'=>$email_block_column));
}
//EDIT email BLOCK OPERATION
public function edit_email_block(){
$prime_email_block_id = (int)$this->input->post('prime_email_block_id');
$email_qry = 'SELECT * FROM cw_email_block where prime_email_block_id = "'.$prime_email_block_id.'" and trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_data->next_result();
$email_block_table = $email_rslt[0]->email_block_table;
$prime_in = '"'.str_replace(",",'","', $email_block_table);
$custom_in = str_replace(",",'_cf","', $email_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();
//Get View Name
$in_module_name = str_replace('cw_', '', $email_rslt[0]->email_block_table);
$in_module_name = explode(',', $in_module_name);
$get_module_name = array_map(function($value){
if($value === "transactions"){
$value = "employees";
}else
if($value === "monthly_input"){
$value = "employees";
}
$return_array = array();
$return_array = $value;
return $return_array;
}, $in_module_name);
$get_module_name = implode('","', $get_module_name);
$view_name_qry = 'select label_name,view_name from cw_form_setting where trans_status = 1 and input_view_type in(1,2) and prime_module_id in("'.$get_module_name.'")';
$view_name_info = $this->db->query("CALL sp_a_run ('SELECT','$view_name_qry')");
$view_name_rslt = $view_name_info->result_array();
$view_name_info->next_result();
$view_name_rslt = array_reduce($view_name_rslt, function ($result, $arr) {
$result[$arr['label_name']] = $arr['view_name'];
return $result;
}, array());
$email_block_column[""] = "---- Select Column ----";
foreach($column_result as $column){
$table_value = $column->TABLE_NAME;
$column_value = $column->COLUMN_NAME;
$column_name = $view_name_rslt[$column_value];
if($column_value === "transactions_month" || $column_value === "user_name" || $column_value === "password"){
$column_name = ucwords(str_replace('_',' ', $column_value));
}
if(strpos($column_value, 'trans_') !== false) {
//echo "UDY :: $table_value - $column_value<br/>"; // UDY CHECK FOR ALTER
}else{
if(!$column_name){
$column_name = ucwords(str_replace("_"," ",$column_value));
}
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$email_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
}
echo json_encode(array('success' => TRUE,'email_block_column' => $email_block_column,'email_info' => $email_rslt[0]));
}
//REMOVE email INFO OPERATION
public function remove_email_block(){
$prime_email_block_id = (int)$this->input->post('prime_email_block_id');
$email_block_module_id = $this->input->post('email_block_module_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_email_block SET trans_status = 0,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_email_block_id = "'.$prime_email_block_id.'"';
$delete = $this->db->query("CALL sp_a_run ('UPDATE','$remove_qry')");
$delete->next_result();
$email_block = $this->get_email_block_list($email_block_module_id);
$email_block_rslt = json_decode($email_block);
$email_block_list = $email_block_rslt->email_block_list;
$email_table_list = $email_block_rslt->email_table_list;
echo json_encode(array('success' => true, 'message'=>'email block deleted Successfully!!!','email_block_list'=>$email_block_list,'email_table_list'=>$email_table_list));
}
/* ==============================================================*/
/* ================== email BLOCK OPEARTION - END ==============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================ email TABLE OPEARTION - START ==============*/
/* ==============================================================*/
public function get_email_table_info(){
$email_table_for_id = $this->input->post("prime_email_table_id");
$email_block_module_id = $this->input->post("email_block_module_id");
$email_table_list = $this->input->post("email_table_list");
$email_name_table = $this->input->post("email_name_table");
$email_table_column = ltrim(implode(",",$this->input->post('email_table_column[]')),",");
$email_table_email_column = ltrim(implode(",",$this->input->post('email_table_email_column[]')),",");
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
if((int)$email_table_for_id === 0){
$email_qry = 'INSERT INTO cw_email_table (email_table_for_id, email_table_list,email_name_table,email_table_column,email_table_email_column,email_block_module_id,trans_created_by, trans_created_date) VALUES ("'.$email_table_for_id.'","'.$email_table_list.'","'.$email_name_table.'","'.$email_table_column.'","'.$email_table_email_column.'","'.$email_block_module_id.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$email_qry')");
$email_info = $this->get_email_table_list($email_block_module_id);
$email_tabl_rlst = json_decode($email_info);
$email_table_info_list = $email_tabl_rlst->email_info_list;
echo json_encode(array('success' => TRUE, 'message' => "Added Successfully",'email_table_info_list' => $email_table_info_list));
}else{
$upd_qry = 'UPDATE cw_email_table SET email_table_for_id = "'.$email_table_for_id.'",email_table_list = "'.$email_table_list.'",email_name_table = "'.$email_name_table.'",email_table_column = "'.$email_table_column.'",email_table_email_column = "'.$email_table_email_column.'",email_block_module_id = "'.$email_block_module_id.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_email_table_id = "'.$email_table_for_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$email_info = $this->get_email_table_list($email_block_module_id);
$email_tabl_rlst = json_decode($email_info);
$email_table_info_list = $email_tabl_rlst->email_info_list;
// print_r($email_table_info_list);die;
echo json_encode(array('success' => TRUE, 'message' => "Update Successfully",'email_table_info_list' => $email_table_info_list));
}
}
public function save_email_table(){
$email_table_for_id = $this->input->post('email_table_for_id');
$email_table_module_id = $this->input->post('email_table_module_id');
$prime_email_table_id = $this->input->post('prime_email_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_email_table_join SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE email_table_for_id = "'.$email_table_for_id.'" and email_table_module_id = "'.$email_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_email_table_id_val = $prime_email_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_email_table_id_val === 0){
$table_query = 'insert into cw_email_table_join (email_table_for_id,email_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 ("'.$email_table_for_id.'","'.$email_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_email_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_email_table_id = "'.$prime_email_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_email_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_email_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_email_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_email_where_id = (int)$exist_result[0]->prime_email_where_id;
$upd_qry = 'UPDATE cw_email_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_email_where_id = "'.$prime_email_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(){
$email_info_module_id = $this->input->post('email_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($email_info_module_id === 'custom_approval'){
$email_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 = "'.$email_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){
$column1 = $colum[0];
$column2 = $colum[1];
$col_id = $col->$column1;
$col_value = $col->$column2;
$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));
}
}
/* ==============================================================*/
/* ================== email TABLE OPEARTION - END ==============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================ email DESIGN OPEARTION - START ==============*/
/* ==============================================================*/
public function assign_table_info(){
$assign_table_info = $this->input->post('assign_table_info');
$email_qry = 'SELECT * FROM cw_email_block where email_block_for = "'.$assign_table_info.'" and trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_data->next_result();
$assign_table_block[] = "--- Select email block ---";
$list_view_value[""] = "--- Select list View ---";
foreach($email_rslt as $rslt){
$prime_email_block_id = $rslt->prime_email_block_id;
$email_block_type = $rslt->email_block_type;
$email_block_name = ucwords($rslt->email_block_name);
$assign_table_block[$prime_email_block_id] = $email_block_name;
if((int)$email_block_type === 2){
$replce_block = "@".strtolower(str_replace(" ","_",$email_block_name))."@";
$list_view_value[$replce_block] = $email_block_name;
}
}
$design_query = 'SELECT * FROM cw_mail_design WHERE email_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();
$email_design = "";
if($design_result){
$email_design = $design_result[0]->email_design;
$email_design = str_replace('~','"',$email_design);
}
echo json_encode(array('success' => TRUE,'assign_table_block' => $assign_table_block,'email_design' => $email_design,'list_view_value' => $list_view_value));
}
public function assign_table_block(){
$assign_table_block = $this->input->post('assign_table_block');
$email_table_list = (int)$this->input->post('email_table_list');
$email_qry = 'SELECT * FROM cw_email_table inner join cw_email_block on cw_email_block.prime_email_block_id = cw_email_table.email_name_table inner join cw_email_info on cw_email_info.prime_email_info_id = cw_email_block.email_block_for where prime_email_info_id = "'.$assign_table_block.'" and cw_email_table.trans_status = 1 and cw_email_block.trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_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($email_rslt as $rslt){
$prime_email_block_id = $rslt->prime_email_table_id;
$email_block_module_id = $rslt->email_block_module_id;
$email_block_for = $rslt->email_table_list;
$email_block_table = $rslt->email_block_table;
$email_block_column = $rslt->email_table_column;
$new_table = explode(",",$email_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,"",$email_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));
}
/* ==============================================================*/
/* ================ email DESIGN OPEARTION - END ==============*/
/* ==============================================================*/
public function get_block_table(){
$email_block_for = (int)$this->input->post('email_block_for');
$email_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_email_info` where prime_email_info_id = \'$email_block_for\' and trans_status = 1')");
$email_info_rlst = $email_info->result();
$email_info->next_result();
if($email_info_rlst){
$email_type = $email_info_rlst[0]->email_type;
$email_block_column[""] = "---- Select Column ----";
if((int)$email_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();
$view_name_qry = 'select view_name,label_name,prime_module_id from cw_form_setting where trans_status = "1"';
$view_name_data = $this->db->query("CALL sp_a_run ('SELECT','$view_name_qry')");
$view_name_result = $view_name_data->result_array();
$view_name_data->next_result();
$view_name_result = array_reduce($view_name_result, function ($result, $arr) {
$result[$arr['prime_module_id']][$arr['label_name']] = $arr;
return $result;
}, array());
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'){
$column_name = strtoupper($view_name_result[$module_name][$column_value]['view_name']);
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_name = strtoupper($table_name);
if($column_name){
$email_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));
$email_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
}
}
echo json_encode(array('success' => TRUE ,'email_type'=>$email_type,'email_block_column'=>$email_block_column));
}else{
echo json_encode(array('success' => FALSE ,'message'=>'Contact Admin...!'));
}
}
public function save_email_design(){
$email_design = $this->input->post('content');
$email_design = str_replace('"',"~",$email_design);
$email_design_for = $this->input->post('assign_table_info');
$email_subject = $this->input->post('email_subject');
$email_attach_type = $this->input->post('email_attach_type');
$email_attach_column = $this->input->post('email_attach_column');
$bcc_mail = $this->input->post('bcc_mail');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$exist_query = 'SELECT * FROM cw_mail_design WHERE email_design_for = "'.$email_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_mail_design (email_design_for, email_design,email_subject,bcc_mail,email_attach_type,email_attach_column,trans_created_by, trans_created_date) VALUES ("'.$email_design_for.'","'.$email_design.'","'.$email_subject.'","'.$bcc_mail.'","'.$email_attach_type.'","'.$email_attach_column.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$search_qry')");
echo json_encode(array('success' => true,'message'=>"email design added successfully !!!"));
}else{
$prime_email_design_id = (int)$exist_result[0]->prime_email_design_id;
$upd_qry = 'UPDATE cw_mail_design SET email_design_for = "'.$email_design_for.'",email_design = "'.$email_design.'",email_subject = "'.$email_subject.'",bcc_mail = "'.$bcc_mail.'",email_attach_type = "'.$email_attach_type.'",email_attach_column = "'.$email_attach_column.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_email_design_id = "'.$prime_email_design_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
echo json_encode(array('success' => true,'message'=>"email design updated successfully !!!"));
}
}
public function email_assign_table_info(){
$assign_table_info = $this->input->post('assign_table_info');
$email_qry = 'SELECT * FROM cw_email_block where prime_email_block_id = "'.$assign_table_info.'" and trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_data->next_result();
$assign_table_block[] = "--- Select email block ---";
$list_view_value[""] = "--- Select list View ---";
foreach($email_rslt as $rslt){
$prime_email_block_id = $rslt->prime_email_block_id;
$email_block_type = $rslt->email_block_type;
$email_block_name = ucwords($rslt->email_block_name);
$assign_table_block[$prime_email_block_id] = $email_block_name;
if((int)$email_block_type === 2){
$replce_block = "@".strtolower(str_replace(" ","_",$email_block_name))."@";
$list_view_value[$replce_block] = $email_block_name;
}
}
$design_query = 'SELECT * FROM cw_mail_design WHERE email_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();
$email_design = "";
if($design_result){
$email_design = $design_result[0]->email_design;
$email_design = str_replace('~','"',$email_design);
$email_subject = $design_result[0]->email_subject;
$email_attach_type = $design_result[0]->email_attach_type;
$bcc_mail = $design_result[0]->bcc_mail;
}
echo json_encode(array('success' => TRUE,'assign_table_block' => $assign_table_block,'email_design' => $email_design,'list_view_value' => $list_view_value,'email_subject' => $email_subject,'bcc_mail'=>$bcc_mail,'email_attach_type'=>$email_attach_type));
}
public function get_query_column_list(){
$query_list_id = $this->input->post('query_list_id');
$email_info_module_id = $this->input->post('email_info_module_id');
if($email_info_module_id === 'custom_approval'){
$email_info_module_id = 'employees';
}
$pick_base_qry = 'select * from cw_form_setting where prime_module_id = "'.$email_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(){
$email_table_list = $this->input->post("email_table_list");
$email_info_module_id = $this->input->post("email_info_module_id");
$email_block_qry = 'select prime_email_block_id,email_block_name from cw_email_block where email_block_for = "'.$email_table_list.'" and trans_status = 1';
$email_block_info = $this->db->query("CALL sp_a_run ('SELECT','$email_block_qry')");
$email_block_result = $email_block_info->result();
$email_block_info->next_result();
$email_block_name[] = "--- Select Column---";
foreach ($email_block_result as $key => $value) {
$block_id = $value->prime_email_block_id;
$block_name = $value->email_block_name;
$email_block_name[$block_id] = $block_name;
}
echo json_encode(array('success'=> true,'msg'=>"Column list","column_list"=>$email_block_name));
}
public function get_query_table_column_list(){
$email_name_table = $this->input->post("email_name_table");
$email_info_module_id = $this->input->post("email_info_module_id");
$email_block_qry = 'select * from cw_email_block where prime_email_block_id = "'.$email_name_table.'" and trans_status = 1';
$email_block_info = $this->db->query("CALL sp_a_run ('SELECT','$email_block_qry')");
$email_block_result = $email_block_info->result();
$email_block_info->next_result();
$email_block_table = $email_block_result[0]->email_block_table;
$bcc_email_type = $email_block_result[0]->email_block_type;
// echo "email_block_table :: $email_block_table<br>";die;
// $email_block_table = ltrim(implode(",",$this->input->post('email_block_table')),",");
$prime_in = '"'.str_replace(",",'","', $email_block_table);
$custom_in = str_replace(",",'_cf","', $email_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();
$email_block_column[""] = "---- Select Column ----";
$email_block_column_email[""] = "---- 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){
$email_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
$view_name_email_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_email_info = $this->db->query("CALL sp_a_run ('SELECT','$view_name_email_qry')");
$view_name_email_rlst = $view_name_email_info->result();
$view_name_email_info->next_result();
$column_name_email = strtoupper($view_name_email_rlst[0]->view_name);
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_name = strtoupper($table_name);
if($column_name_email){
$email_block_column_email[$table_value.".".$column_value] = $table_name . " - ". $column_name_email;
}
}
echo json_encode(array('success' => true,'email_block_column'=>$email_block_column,'bcc_email_type'=>$bcc_email_type,'email_block_column_email'=>$email_block_column_email));
}
public function remove_email_table_info(){
$prime_email_table_id = (int)$this->input->post('prime_email_table_id');
$email_info_module_id = $this->input->post('email_info_module_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_email_table SET trans_status = 0,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_email_table_id = "'.$prime_email_table_id.'"';
$delete = $this->db->query("CALL sp_a_run ('UPDATE','$remove_qry')");
$delete->next_result();
$email_info = $this->get_email_table_list($email_info_module_id);
$email_tabl_rlst = json_decode($email_info);
$email_table_info_list = $email_tabl_rlst->email_info_list;
echo json_encode(array('success' => true, 'message'=>'email Table deleted Successfully!!!','email_table_info_list'=>$email_table_info_list));
}
public function edit_email_table_info(){
$prime_email_table_id = $this->input->post("prime_email_table_id");
$email_info_module_id = $this->input->post("email_info_module_id");
$email_block_qry = 'select cw_email_table.email_table_column,cw_email_table.email_name_table,cw_email_table.email_table_list from cw_email_table inner join cw_email_info on cw_email_info.prime_email_info_id = cw_email_table.email_table_list inner join cw_email_block on cw_email_block.prime_email_block_id = cw_email_table.email_name_table where cw_email_table.prime_email_table_id = "'.$prime_email_table_id.'" and cw_email_table.email_block_module_id = "'.$email_info_module_id.'" and cw_email_table.trans_status = 1 and cw_email_table.trans_status = 1';
$email_block_info = $this->db->query("CALL sp_a_run ('SELECT','$email_block_qry')");
$email_block_result = $email_block_info->result();
$email_block_info->next_result();
$email_info_select = $email_block_result[0]->email_table_list;
$email_block_select = $email_block_result[0]->email_name_table;
$email_block_table = $email_block_result[0]->email_table_column;
if($email_info_module_id === 'custom_approval'){
$mail_module_id = 'employees';
}else{
$mail_module_id = $email_info_module_id;
}
$email_qry = 'SELECT cw_email_info.prime_email_info_id,cw_email_info.email_info_name FROM cw_email_info left join cw_form_setting on cw_form_setting.label_name = cw_email_info.query_list_id where email_info_module_id = "'.$email_info_module_id.'" and prime_module_id in ("'.$mail_module_id.'","null") and cw_email_info.trans_status = 1';
$email_info = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_info_rslt = $email_info->result();
$email_info->next_result();
$email_info_name[""] = "--- Select ---";
foreach ($email_info_rslt as $key => $value) {
$email_info_id = $value->prime_email_info_id;
$email_name = $value->email_info_name;
$email_info_name[$email_info_id] = $email_name;
}
$email_table_block_qry = 'select prime_email_block_id,email_block_name from cw_email_block where email_block_module_id = "'.$email_info_module_id.'" and trans_status = 1';
$email_table_block_info = $this->db->query("CALL sp_a_run ('SELECT','$email_table_block_qry')");
$email_table_block_rlst = $email_table_block_info->result();
$email_table_block_info->next_result();
$email_block_list[""] = "--- Select ---";
foreach ($email_table_block_rlst as $key => $value) {
$email_info_id = $value->prime_email_block_id;
$email_name = $value->email_block_name;
$email_block_list[$email_info_id] = $email_name;
}
$email_qry = 'SELECT * FROM cw_email_table inner join cw_email_block on cw_email_block.prime_email_block_id = cw_email_table.email_name_table where prime_email_table_id = "'.$prime_email_table_id.'" and cw_email_table.trans_status = 1 and cw_email_block.trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_data->next_result();
$email_block_table = $email_rslt[0]->email_block_table;
$prime_in = '"'.str_replace(",",'","', $email_block_table);
$custom_in = str_replace(",",'_cf","', $email_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();
$email_block_column_email[""] = "---- 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_email_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_email_info = $this->db->query("CALL sp_a_run ('SELECT','$view_name_email_qry')");
$view_name_email_rlst = $view_name_email_info->result();
$view_name_email_info->next_result();
$column_name_email = strtoupper($view_name_email_rlst[0]->view_name);
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_name = strtoupper($table_name);
if($column_name_email){
$email_block_column_email[$table_value.".".$column_value] = $table_name . " - ". $column_name_email;
}
}
$email_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));
$email_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
}
echo json_encode(array('success' => true,'email_info_name'=>$email_info_name, 'email_info_select'=> $email_info_select , 'email_block_list' => $email_block_list , 'email_block_select' => $email_block_select , 'email_block_column' => $email_block_column , 'email_block_table' => $email_block_table,'email_info' => $email_rslt[0],'email_block_column_email'=> $email_block_column_email));
}
public function get_join_table_info(){
$email_table_list = (int)$this->input->post('email_table_join_list');
$email_qry = 'SELECT * FROM cw_email_block where prime_email_block_id = "'.$email_table_list.'" and trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_data->next_result();
foreach($email_rslt as $rslt){
$prime_email_block_id = $rslt->prime_email_block_id;
$email_block_module_id = $rslt->email_block_module_id;
$email_block_for = $rslt->email_block_for;
$email_block_name = $rslt->email_block_name;
$email_block_type = $rslt->email_block_type;
$email_block_table = explode(",",$rslt->email_block_table);
$email_block_column = $rslt->email_block_column;
$table_list = array();
$table_list[""] = "---- Select Table ----";
foreach($email_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->email_block_table);
$custom_in = str_replace(",",'_cf","', $rslt->email_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");
$email_tab_query = 'SELECT * FROM cw_email_table_join WHERE email_table_for_id = "'.$prime_email_block_id.'" AND email_table_module_id = "'.$email_block_module_id.'" order by abs(line_sort)';
$email_tab_info = $this->db->query("CALL sp_a_run ('SELECT','$email_tab_query')");
$email_tab_result = $email_tab_info->result();
$email_tab_info->next_result();
$email_table_for_id = form_input(array( 'name' =>'email_table_for_id','id' =>'email_table_for_id', 'class' => 'form-control input-sm','value' =>$prime_email_block_id,'type'=>'Hidden'));
$email_table_module_id = form_input(array( 'name' =>'email_table_module_id','id' =>'email_table_module_id', 'class' => 'form-control input-sm','value' =>$email_block_module_id,'type'=>'Hidden'));
$table_tr_line = "";
$table_count = 0;
$condition_table_count = count($email_block_table) - 1; //round(count($condition_table)/2);
for($i=1;$i<= $condition_table_count;$i++){
$prime_email_table_id = 0;
$line_prime_table = "";
$line_prime_col = "";
$line_join_type = "";
$line_join_table = "";
$line_join_col = "";
$line_prime_col1 = array();
$line_join_col2 = array();
if($email_tab_result){
$prime_email_table_id = $email_tab_result[$table_count]->prime_email_table_id;
$line_prime_table = $email_tab_result[$table_count]->line_prime_table;
$line_prime_col = $email_tab_result[$table_count]->line_prime_col;
$line_join_type = $email_tab_result[$table_count]->line_join_type;
$line_join_table = $email_tab_result[$table_count]->line_join_table;
$line_join_col = $email_tab_result[$table_count]->line_join_col;
$line_join_col2[] = $line_join_table;
$line_prime_col1[] = $line_prime_table;
$column_list1 = $this->get_tbl_join_table('1',$line_prime_col1);
$column_list2 = $this->get_tbl_join_table('2',$line_join_col2);
}
$table_cond_for_id = form_input(array( 'name' =>"prime_email_table_id[]",'class' => 'form-control input-sm','value' =>$prime_email_table_id,'type'=>'Hidden','id'=>'prime_email_table_id_'.$i.''));
$prime_table_data = form_dropdown(array('name' =>"line_prime_table[]",'class' => 'form-control input-sm select2','id'=>'line_prime_table_'.$i.'','onchange'=>'get_tbl_data2(1,'.$i.')'), $table_list,$line_prime_table);
$prime_col_data = form_dropdown(array('name' =>"line_prime_col[]",'class' => 'form-control input-sm select2','id'=>'line_prime_col_'.$i.''),$column_list2,$line_prime_col);
$join_data = form_dropdown(array('name' =>"line_join_type[]",'class' => 'form-control input-sm select2','id'=>'line_join_type_'.$i.''),$join_array,$line_join_type);
$join_table_data = form_dropdown(array('name' =>"line_join_table[]",'class' => 'form-control input-sm select2','id'=>'line_join_table_'.$i.'','onchange'=>'get_tbl_data2(2,'.$i.')'), $table_list,$line_join_table);
$join_col_data = form_dropdown(array('name' =>"line_join_col[]",'class' => 'form-control input-sm select2','id'=>'line_join_col_'.$i.''),$column_list2,$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 = "$email_table_for_id $email_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_email_table'>Save</button>
</div>";
}
$where_query = 'SELECT * FROM cw_email_table_where WHERE where_module_id = "'.$email_block_module_id.'" and where_for_id = "'.$prime_email_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_email_block_id'=>$prime_email_block_id, 'email_block_module_id'=>$email_block_module_id,'email_table_block'=>$table_content, 'column_list'=>$column_list,'where_condition'=>$where_condition,));
}
public function get_attachment_column(){
$email_attach_type = $this->input->post("email_attach_type");
$email_info_module_id = $this->input->post("email_info_module_id");
if($email_info_module_id === 'employees'){
$email_info_module_id = 'custom_approval';
}
if((int)$email_attach_type === 1){
$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 = "'.$email_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 = array();
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, 'print_column'=>$print_column_rlst));
}
}
public function get_content_join_table_info(){
$email_content_table_list = (int)$this->input->post('email_content_table_join_list');
$email_qry = 'SELECT * FROM cw_email_block where prime_email_block_id = "'.$email_content_table_list.'" and trans_status = 1';
$email_data = $this->db->query("CALL sp_a_run ('SELECT','$email_qry')");
$email_rslt = $email_data->result();
$email_data->next_result();
foreach($email_rslt as $rslt){
$prime_email_block_id = $rslt->prime_email_block_id;
$email_block_module_id = $rslt->email_block_module_id;
$email_block_for = $rslt->email_block_for;
$email_block_name = $rslt->email_block_name;
$email_block_type = $rslt->email_block_type;
$email_block_table = explode(",",$rslt->email_block_table);
$email_block_column = $rslt->email_block_column;
$table_list = array();
$table_list[""] = "---- Select Table ----";
foreach($email_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->email_block_table);
$custom_in = str_replace(",",'_cf","', $rslt->email_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");
$email_tab_query = 'SELECT * FROM cw_email_content_table_join WHERE email_content_table_for_id = "'.$prime_email_block_id.'" AND email_content_table_module_id = "'.$email_block_module_id.'" and trans_status = 1 order by abs(line_sort_content)';
$email_tab_info = $this->db->query("CALL sp_a_run ('SELECT','$email_tab_query')");
$email_tab_result = $email_tab_info->result();
$email_tab_info->next_result();
$email_table_for_id = form_input(array( 'name' =>'email_content_table_for_id','id' =>'email_content_table_for_id', 'class' => 'form-control input-sm','value' =>$prime_email_block_id,'type'=>'Hidden'));
$email_table_module_id = form_input(array( 'name' =>'email_content_table_module_id','id' =>'email_content_table_module_id', 'class' => 'form-control input-sm','value' =>$email_block_module_id,'type'=>'Hidden'));
$table_tr_line = "";
$table_count = 0;
$condition_table_count = count($email_block_table) - 1; //round(count($condition_table)/2);
for($i=1;$i<= $condition_table_count;$i++){
$prime_email_table_id = 0;
$line_prime_table = "";
$line_prime_col = "";
$line_join_type = "";
$line_join_table = "";
$line_join_col = "";
$line_prime_col1 = array();
$line_join_col2 = array();
if($email_tab_result){
$prime_email_table_id = $email_tab_result[$table_count]->prime_email_content_table_id;
$line_prime_table = $email_tab_result[$table_count]->line_prime_table_content;
$line_prime_col = $email_tab_result[$table_count]->line_prime_col_content;
$line_join_type = $email_tab_result[$table_count]->line_join_type_content;
$line_join_table = $email_tab_result[$table_count]->line_join_table_content;
$line_join_col = $email_tab_result[$table_count]->line_join_col_content;
$line_join_col2[] = $line_join_table;
$line_prime_col1[] = $line_prime_table;
$column_list1 = $this->get_tbl_join_table('1',$line_prime_col1);
$column_list2 = $this->get_tbl_join_table('2',$line_join_col2);
}
$table_cond_for_id = form_input(array( 'name' =>"prime_email_content_table_id[]",'class' => 'form-control input-sm','value' =>$prime_email_table_id,'type'=>'Hidden','id'=>'prime_email_content_table_id_'.$i.''));
$prime_table_data = form_dropdown(array('name' =>"line_prime_table_content[]",'class' => 'form-control input-sm select2','id'=>'line_prime_table_content_'.$i.'','onchange'=>'get_tbl_data(1,'.$i.')'), $table_list,$line_prime_table);
$prime_col_data = form_dropdown(array('name' =>"line_prime_col_content[]",'class' => 'form-control input-sm select2','id'=>'line_prime_col_content_'.$i.''),$column_list1,$line_prime_col);
$join_data = form_dropdown(array('name' =>"line_join_type_content[]",'class' => 'form-control input-sm select2','id'=>'line_join_type_content_'.$i.''),$join_array,$line_join_type);
$join_table_data = form_dropdown(array('name' =>"line_join_table_content[]",'class' => 'form-control input-sm select2','id'=>'line_join_table_content_'.$i.'','onchange'=>'get_tbl_data(2,'.$i.')'), $table_list,$line_join_table);
$join_col_data = form_dropdown(array('name' =>"line_join_col_content[]",'class' => 'form-control input-sm select2','id'=>'line_join_col_content_'.$i.''),$column_list2,$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 = "$email_table_for_id $email_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_email_table'>Save</button>
</div>";
}
$where_query = 'SELECT * FROM cw_email_content_table_where WHERE content_where_module_id = "'.$email_block_module_id.'" and content_where_for_id = "'.$prime_email_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_email_block_id'=>$prime_email_block_id, 'email_block_module_id'=>$email_block_module_id,'email_table_block'=>$table_content, 'column_list'=>$column_list,'where_condition'=>$where_condition,));
}
public function save_email_content_table(){
$email_table_for_id = $this->input->post('email_content_table_for_id');
$email_table_module_id = $this->input->post('email_content_table_module_id');
$prime_email_table_id = $this->input->post('prime_email_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_email_content_table_join SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE email_content_table_for_id = "'.$email_table_for_id.'" and email_content_table_module_id = "'.$email_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_email_table_id_val = $prime_email_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_email_table_id_val === 0){
$table_query = 'insert into cw_email_content_table_join (email_content_table_for_id,email_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 ("'.$email_table_for_id.'","'.$email_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_email_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_email_content_table_id = "'.$prime_email_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(){
$email_info_module_id = $this->input->post('email_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($email_info_module_id === 'custom_approval'){
$email_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 = "'.$email_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){
$column1 = $colum[0];
$column2 = $colum[1];
$col_id = $col->$column1;
$col_value = $col->$column2;
$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_email_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_email_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_email_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_email_where_id = (int)$exist_result[0]->prime_email_where_id;
$upd_qry = 'UPDATE cw_email_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_email_content_where_id = "'.$prime_email_where_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
echo json_encode(array('success' => true,'message'=>"Where updated successfully !!!"));
}
}
public function dlt_where_table_query(){
$where_for_id = (int)$this->input->post("where_for_id");
$where_module_id = $this->input->post("where_module_id");
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$exist_query = 'SELECT * FROM cw_email_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();
$prime_email_where_id = (int)$exist_result[0]->prime_email_where_id;
$upd_qry = 'UPDATE cw_email_table_where SET trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'",trans_status = "0" where prime_email_where_id = "'.$prime_email_where_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
echo json_encode(array('success' => true,'message'=>"Deleted successfully !!!"));
}
public function select_date_format_data(){
$assign_value_for = $this->input->post("assign_value_for");
$assign_value_for = str_replace('@', '', $assign_value_for);
$email_info_module_id = $this->input->post("email_info_module_id");
$exist_query = 'SELECT field_type FROM cw_form_setting WHERE label_name = "'.$assign_value_for.'" and prime_module_id = "'.$email_info_module_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();
$field_type = $exist_result[0]->field_type;
if((int)$field_type === 4){
echo json_encode(array('success' => true,'message'=>"successfully !!!"));
}else{
echo json_encode(array('success' => false,'message'=>"successfully !!!"));
}
}
public function get_tbl_join_table($tbl_id,$print_block_table){
$print_block_table = ltrim(implode(",",$print_block_table),",");
$prime_in = '"'.str_replace(",",'","', $print_block_table);
$custom_in = str_replace(",",'_cf","', $print_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") AND COLUMN_NAME NOT LIKE "%prime%"';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$column_result = $column_info->result();
$column_info->next_result();
$print_block_column[""] = "---- 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
if($table_value === "cw_transactions"){
$module_name = str_replace("cw_transactions","employees",$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);
$print_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
if(!$column_name){
$print_block_column[$table_value.".".$column_value] = $table_name . " - ". strtoupper(str_replace("_"," ",$column_value));
}
}
return $print_block_column;
}
}
?>