File: /home/cafsindia/cpaqua.cafsinfotech.in/application/controllers/Print_setting.php
<?php
/**********************************************************
Filename: Print Setting
Description: Print Setting for creating new Dynamic Print 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 Print_setting extends Secure_Controller{
public function __construct(){
parent::__construct('print_setting');
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
// DATATABLE HEADERS DEFINED _ARN FOR (8.3)
$db_name = $this->config->item("db_name");
$prime_table_query = $this->db->query("SELECT CASE WHEN COLUMN_NAME = 'menu_id' THEN 'menu_name' WHEN COLUMN_NAME = 'show_module' THEN 'module_status'WHEN COLUMN_NAME = 'temp_mand' THEN 'mandatory_status' ELSE COLUMN_NAME END AS label_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$db_name' AND TABLE_NAME = 'cw_modules' AND COLUMN_NAME IN ('module_name', 'menu_id', 'module_type', 'show_module','temp_mand')");
$prime_table_col = $prime_table_query->result();
$data['table_head'] = $prime_table_col; ;
$this->load->view('print_setting/manage',$data);
}
/* ==============================================================*/
/* ================== PRINT COMMON OPEARTION - START ============*/
/* ==============================================================*/
//PRINT SEARCH OPEARTION FOR 8.3 BY _ARN
public function search(){
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$search = $this->input->post('search');
$search = trim($search['value']);
$common_search = '';
$searchterm = $this->db->escape_like_str($search);
if($search){
$common_search .= " AND cw_modules.module_name LIKE '%" . $searchterm . "%'";
}
//FETCH RECORDS DATA
$search_total = $this->db->query("SELECT count(*) as allcount from cw_modules where trans_status = 1 and pdf_module = 1");
$search_total_info = $search_total->result();
$total_count = $search_total_info[0]->allcount;
$search_count = $this->db->query("SELECT count(*) as allcount from cw_modules where trans_status = 1 and pdf_module = 1 $common_search");
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query = "SELECT module_id,module_name,cw_main_menu.menu_name,module_type,CASE WHEN show_module = 1 THEN 'Active' WHEN show_module = 0 THEN 'In Active' END AS module_status,CASE WHEN temp_mand = 1 THEN 'Added' WHEN temp_mand = 0 THEN 'Removed'
END AS mandatory_status FROM cw_modules INNER JOIN cw_main_menu ON cw_main_menu.prime_menu_id = cw_modules.menu_id WHERE cw_modules.trans_status = 1 and pdf_module = 1 $common_search";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$search_data->next_result();
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
// MODEL VIEW OPEARTION
// WILL CHANGE ON DEFINE NULL FOR 8.3 _ARN
public function view($print_info_module_id = null){
if(!$print_info_module_id){
$print_info_module_id = 0;
}
$data['print_info_module_id'] = $print_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();
$print_for[""] = "---- Module For ----";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$print_for[$role_id] = $category_name;
}
$data['print_for'] = $print_for;
$payslip_based_on = $this->company_info();
$payslip_based_on = $payslip_based_on[0]->payslip_based_on;
$form_setting_query = 'select label_name,view_name,pick_table,pick_list from cw_form_setting where prime_module_id = "employees" and label_name="'.$payslip_based_on.'" ORDER BY input_for,field_sort asc';
$form_setting_info = $this->db->query("CALL sp_a_run ('SELECT','$form_setting_query')");
$form_setting_rslt = $form_setting_info->result_array();
$form_setting_info->next_result();
$payslip_pick_table = $form_setting_rslt[0]['pick_table'];
$payslip_pick_list = $form_setting_rslt[0]['pick_list'];
$payslip_pick_arr = explode(',', $payslip_pick_list);
$payslip_pick_list1 = $payslip_pick_arr[0];
$payslip_pick_list2 = $payslip_pick_arr[1];
$filter_qry = "";
if($payslip_pick_table === "cw_category"){
$filter_qry = " and prime_category_id != 1";
}
$column_qry = 'SELECT '.$payslip_pick_list.' from '.$payslip_pick_table.' where trans_status = 1 '.$filter_qry.'';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$column_qry')");
$column_rslt = $column_info->result();
$column_info->next_result();
$pick_key = array_column($column_rslt, $payslip_pick_list1);
$pick_val = array_column($column_rslt, $payslip_pick_list2);
$pdf_design_for = array_combine( $pick_key, $pick_val);
$column_name = $form_setting_rslt[$payslip_based_on]['view_name'];
$data['pdf_design_for'] = array("" => "---- $column_name ----") + $pdf_design_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_print_block","cw_print_design","cw_print_info","cw_print_map","cw_print_table","cw_print_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;
$print_type_query = 'select prime_print_type_id,print_type from cw_print_type inner join cw_modules on cw_modules.module_id = cw_print_type.module inner join cw_main_menu on cw_main_menu.prime_menu_id = cw_modules.menu_id where menu_status = 1 and cw_print_type.trans_status=1 and module = "'.$print_info_module_id.'"';
$print_type_info = $this->db->query("CALL sp_a_run ('SELECT','$print_type_query')");
$print_type_result = $print_type_info->result();
$print_type_info->next_result();
$print_type_name = [""=>"-- Select Print Type --"];
foreach ($print_type_result as $key => $print_types) {
$print_id = $print_types->prime_print_type_id;
$print_type = $print_types->print_type;
$print_type_name[$print_id] = $print_type;
}
$data['print_type_name'] = $print_type_name;
$print_info = $this->get_print_info_list($print_info_module_id);
$print_info_rslt = json_decode($print_info);
$data['print_info_list'] = $print_info_rslt->print_info_list;
$data['print_block_for'] = json_decode(json_encode($print_info_rslt->print_block_for), True);
$print_block = $this->get_print_block_list($print_info_module_id);
$print_block_rslt = json_decode($print_block);
$data['print_block_list'] = $print_block_rslt->print_block_list;
$data['print_table_list'] = json_decode(json_encode($print_block_rslt->print_table_list), True);
$data['split_table_list'] = $this->get_split_list($print_info_module_id);
$data['password_column'] = $this->get_password_column();
$this->load->view("print_setting/print_info",$data);
}
/* ==============================================================*/
/* ================== PRINT COMMON OPEARTION - END ============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================== PRINT BASIC OPEARTION - START ============*/
/* ==============================================================*/
// PRINT BASIC SAVE
public function save_print_info(){
$prime_print_info_id = (int)$this->input->post('prime_print_info_id');
$print_info_module_id = $this->input->post('print_info_module_id');
$print_info_name = $this->input->post('print_info_name');
$print_info_for = ltrim(implode(",",$this->input->post('print_info_for[]') ?? []),",");
$pdf_design_for = ltrim(implode(",",$this->input->post('pdf_design_for[]') ?? []),",");
$print_type = $this->input->post('print_type');
$print_based_on = $this->input->post('print_based_on');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$print_info_qry = "";
if((int)$prime_print_info_id > 0){
$print_info_qry = ' and prime_print_info_id != "'.$prime_print_info_id.'"';
}
$pdf_design_for_qry = 'SELECT count(*) as print_count FROM cw_print_info where trans_status = 1 and print_info_module_id = "'.$print_info_module_id.'" and print_type ="'.$print_type.'" and pdf_design_for in('.$pdf_design_for.') '.$print_info_qry.'';
$pdf_design_for_info = $this->db->query("CALL sp_a_run ('SELECT','$pdf_design_for_qry')");
$pdf_design_for_rslt = $pdf_design_for_info->result();
$pdf_design_for_info->next_result();
if((int)$pdf_design_for_rslt[0]->print_count > 0){
echo json_encode(array('success' => FALSE, 'message' => "Print Design For already exist"));
exit(0);
}
if($prime_print_info_id === 0){
//if(!$this->check_print_info_for_already_exists($print_info_for,$print_type,$print_info_module_id)){
$is_exist_qry = 'SELECT count(*) as rslt_count FROM cw_print_info where print_info_module_id = "'.$print_info_module_id.'" and print_info_name = "'.$print_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){
$print_qry = 'INSERT INTO cw_print_info (print_info_module_id, print_info_name,print_info_for,pdf_design_for,print_type,print_based_on,trans_created_by, trans_created_date) VALUES ("'.$print_info_module_id.'","'.$print_info_name.'","'.$print_info_for.'","'.$pdf_design_for.'","'.$print_type.'","'.$print_based_on.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$print_qry')");
$print_info = $this->get_print_info_list($print_info_module_id);
$print_info_rslt = json_decode($print_info);
$print_info_list = $print_info_rslt->print_info_list;
$print_block_for = $print_info_rslt->print_block_for;
echo json_encode(array('success' => true, 'message' => "Print info successfully added", 'print_info_list' => $print_info_list, 'print_block_for' => $print_block_for));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Print info already exist"));
}
/*}else{
echo json_encode(array('success' => FALSE, 'message' => "Print info already created for this Print For Check it"));
}*/
}else{
//if(!$this->check_print_info_for_already_exists($print_info_for,$print_type,$print_info_module_id,$prime_print_info_id)){
$upd_qry = 'UPDATE cw_print_info SET print_info_module_id = "'.$print_info_module_id.'",print_info_name = "'.$print_info_name.'",print_info_for = "'.$print_info_for.'",pdf_design_for = "'.$pdf_design_for.'",print_type = "'.$print_type.'",print_based_on = "'.$print_based_on.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_print_info_id = "'.$prime_print_info_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$print_info = $this->get_print_info_list($print_info_module_id);
$print_info_rslt = json_decode($print_info);
$print_info_list = $print_info_rslt->print_info_list;
$print_block_for = $print_info_rslt->print_block_for;
echo json_encode(array('success' => true, 'message' => "Print info successfully updated", 'print_info_list' => $print_info_list, 'print_block_for' => $print_block_for));
/*}else{
echo json_encode(array('success' => FALSE, 'message' => "Print info already created for this Print For Check it"));
} */
}
}
//CHECKING PRINT INFO FOR ALREADY EXISTS
public function check_print_info_for_already_exists($print_info_for,$print_type,$print_info_module_id,$prime_print_info_id = -1){
$print_info_for = explode(',', $print_info_for);
$query ='';
foreach($print_info_for as $rslt){
$query .= "print_info_for LIKE \"%".$rslt."%\" OR print_info_for LIKE \"".$rslt."%\" OR print_info_for LIKE \"%".$rslt."\" OR ";
}
$query = rtrim($query,"OR ");
$exist_query = 'SELECT * FROM cw_print_info where ('.$query.') and print_info_module_id = "'.$print_info_module_id.'" and print_type = "'.$print_type.'" and trans_status = 1';
if((int)$prime_print_info_id > 0){
$exist_query .=' and prime_print_info_id != "'.$prime_print_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 PRINT BASIC LIST
public function get_print_info_list($print_info_module_id){
$print_qry = 'SELECT * FROM cw_print_info inner join cw_print_type on cw_print_type.prime_print_type_id=cw_print_info.print_type where print_info_module_id = "'.$print_info_module_id.'" and cw_print_info.trans_status = 1';
$print_data = $this->db->query("CALL sp_a_run ('SELECT','$print_qry')");
$print_rslt = $print_data->result();
$print_data->next_result();
$print_for_qry = 'SELECT GROUP_CONCAT(category_name) as category_name,prime_print_info_id FROM cw_category INNER JOIN cw_print_info ON FIND_IN_SET(cw_category.prime_category_id,cw_print_info.print_info_for) where cw_print_info.trans_status = 1 GROUP BY prime_print_info_id';
$print_for_data = $this->db->query("CALL sp_a_run ('SELECT','$print_for_qry')");
$print_for_rslt = $print_for_data->result_array();
$print_for_data->next_result();
$print_for_rslt = array_reduce($print_for_rslt, function ($result, $arr) {
$result[$arr['prime_print_info_id']] = $arr;
return $result;
}, array());
$tr_line = "";
$print_block_for[] = "--- Choose one ---";
foreach($print_rslt as $rslt){
$prime_print_info_id = $rslt->prime_print_info_id;
$print_info_module_id = $rslt->print_info_module_id;
$print_info_name = ucwords($rslt->print_info_name);
$print_info_for = $rslt->print_info_for;
$pdf_design_for = $rslt->pdf_design_for;
$print_type = $rslt->print_type;
$print_based_on = $rslt->print_based_on;
$print_based_list = array(1=>"Print",2=>"Email");
$print_type_val = $rslt->print_type;
$print_based_val = "";
if((int)$print_based_on === 1){
$print_based_val = "Print";
}else
if((int)$print_based_on === 2){
$print_based_val = "Email";
}
$print_block_for[$prime_print_info_id] = $print_info_name;
$category_name = $print_for_rslt[$prime_print_info_id]['category_name'];
$tr_line .= "<tr>
<td>$print_info_name</td>
<td>$category_name</td>
<td>$print_type_val</td>
<td>$print_based_val</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=edit_print_info('$prime_print_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_print_info('$prime_print_info_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$print_info_list = "<table class='table table-bordered table-stripted' id='print_info_table'>
<tr class='inline_head'>
<th>Print Name</th>
<th>Print For</th>
<th>Print Type</th>
<th>Print Based On</th>
<th>Edit</th>
<th>Delete</th>
</tr>
$tr_line
</table>";
return json_encode(array('success' => TRUE,'print_info_list' => $print_info_list,'print_block_for'=>$print_block_for));
}
//EDIT PRINT INFO OPERATION
public function edit_print_info(){
$prime_print_info_id = (int)$this->input->post('prime_print_info_id');
$print_qry = 'SELECT * FROM cw_print_info where prime_print_info_id = "'.$prime_print_info_id.'" and trans_status = 1';
$print_data = $this->db->query("CALL sp_a_run ('SELECT','$print_qry')");
$print_rslt = $print_data->result();
$print_data->next_result();
echo json_encode(array('success' => TRUE,'print_info' => $print_rslt[0]));
}
//REMOVE PRINT INFO OPERATION
public function remove_print_info(){
$prime_print_info_id = (int)$this->input->post('prime_print_info_id');
$print_info_module_id = $this->input->post('print_info_module_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_print_info SET trans_status = 0,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_print_info_id = "'.$prime_print_info_id.'"';
$delete = $this->db->query("CALL sp_a_run ('UPDATE','$remove_qry')");
$delete->next_result();
$print_info = $this->get_print_info_list($print_info_module_id);
$print_info_rslt = json_decode($print_info);
$print_info_list = $print_info_rslt->print_info_list;
$print_block_for = $print_info_rslt->print_block_for;
echo json_encode(array('success' => true, 'message'=>'Print Info deleted Successfully!!!','print_info_list'=>$print_info_list,'print_block_for'=>$print_block_for));
}
/* ==============================================================*/
/* =================== PRINT BASIC OPEARTION - END =============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================ PRINT BLOCK OPEARTION - START ==============*/
/* ==============================================================*/
// PRINT BLOCK SAVE
public function save_print_block(){
$prime_print_block_id = (int)$this->input->post('prime_print_block_id');
$print_block_module_id = $this->input->post('print_block_module_id');
$print_block_name = $this->input->post('print_block_name');
$print_block_for = $this->input->post('print_block_for');
$print_block_type = $this->input->post('print_block_type');
$print_block_table = ltrim(implode(",",$this->input->post('print_block_table[]') ?? []),",");
$print_block_column = ltrim(implode(",",$this->input->post('print_block_column[]') ?? []),",");
$month_column = ltrim(implode(",",$this->input->post('month_column[]') ?? []),",");
$suppressed_data = $this->input->post('suppressed_data');
$cumulative_data = $this->input->post('cumulative_data');
$set_password = $this->input->post('set_password');
$pdf_paper_size = $this->input->post('pdf_paper_size');
$pdf_sheet_type = $this->input->post('pdf_sheet_type');
$password_column = ltrim(implode(",",$this->input->post('password_column[]') ?? []),",");
$folder_column = $this->input->post('folder_column');
$suppressed_data_val = 0;
if($suppressed_data === "on"){
$suppressed_data_val = 1;
}
$cumulative_data_val = 0;
if($cumulative_data === "on"){
$cumulative_data_val = 1;
}
if($prime_print_block_id === 0){
$is_exist_qry = 'SELECT count(*) as rslt_count FROM cw_print_block where print_block_for = "'.$print_block_for.'" and print_block_type = "'.$print_block_type.'" and print_block_table = "'.$print_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_print_block_already_exists($print_block_for)){
$print_qry = 'INSERT INTO cw_print_block (print_block_module_id, print_block_name,print_block_for,print_block_type,print_block_table,print_block_column,month_column,suppressed_data,cumulative_data,set_password,pdf_paper_size,pdf_sheet_type,password_column,folder_column,trans_created_by, trans_created_date) VALUES ("'.$print_block_module_id.'","'.$print_block_name.'","'.$print_block_for.'","'.$print_block_type.'","'.$print_block_table.'","'.$print_block_column.'","'.$month_column.'","'.$suppressed_data_val.'","'.$cumulative_data_val.'","'.$set_password.'","'.$pdf_paper_size.'","'.$pdf_sheet_type.'","'.$password_column.'","'.$folder_column.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$print_qry')");
$print_block = $this->get_print_block_list($print_block_module_id);
$print_block_rslt = json_decode($print_block);
$print_block_list = $print_block_rslt->print_block_list;
$print_table_list = $print_block_rslt->print_table_list;
echo json_encode(array('success' => true, 'message' => "Print block successfully added", 'print_block_list' => $print_block_list, 'print_table_list' => $print_table_list));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Print block already exist"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Print block already exist"));
}
}else{
if(!$this->check_print_block_already_exists($print_block_for,$prime_print_block_id)){
$upd_qry = 'UPDATE cw_print_block SET print_block_for = "'.$print_block_for.'",print_block_type = "'.$print_block_type.'",print_block_name = "'.$print_block_name.'",print_block_for = "'.$print_block_for.'",print_block_table = "'.$print_block_table.'",print_block_column = "'.$print_block_column.'",month_column = "'.$month_column.'",suppressed_data = "'.$suppressed_data_val.'",cumulative_data = "'.$cumulative_data_val.'",set_password = "'.$set_password.'",pdf_paper_size = "'.$pdf_paper_size.'",pdf_sheet_type = "'.$pdf_sheet_type.'",password_column = "'.$password_column.'",folder_column = "'.$folder_column.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_print_block_id = "'.$prime_print_block_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$print_block = $this->get_print_block_list($print_block_module_id);
$print_block_rslt = json_decode($print_block);
$print_block_list = $print_block_rslt->print_block_list;
$print_table_list = $print_block_rslt->print_table_list;
echo json_encode(array('success' => true, 'message' => "Print block successfully updated", 'print_block_list' => $print_block_list, 'print_table_list' => $print_table_list));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Print block already exist"));
}
}
}
public function check_print_block_already_exists($print_block_for,$prime_print_block_id = -1){
$is_exist_qry = 'SELECT * FROM cw_print_block where print_block_for = "'.$print_block_for.'" and trans_status = 1 ';
if((int)$prime_print_block_id > 0){
$is_exist_qry .= " and prime_print_block_id != $prime_print_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;
}
}
//GET PRINT BLOCK LIST
public function get_print_block_list($print_block_module_id){
$view_name_qry = 'select label_name,view_name from cw_form_setting where trans_status = 1 and input_view_type in(1,2)';
$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());
$print_qry = 'SELECT * FROM cw_print_info left join cw_print_block on cw_print_info.prime_print_info_id = cw_print_block.print_block_for where cw_print_block.print_block_module_id = "'.$print_block_module_id.'" and cw_print_info.trans_status = 1 and cw_print_block.trans_status = 1';
$print_data = $this->db->query("CALL sp_a_run ('SELECT','$print_qry')");
$print_rslt = $print_data->result();
$print_data->next_result();
$tr_line = "";
$print_table_list[] = "--- Choose one ---";
$print_block_type_list = array("1"=>"Normal View","2"=>"List View",);
foreach($print_rslt as $rslt){
$print_info_name = ucwords($rslt->print_info_name);
$prime_print_block_id = $rslt->prime_print_block_id;
$print_block_name = ucwords($rslt->print_block_name);
$print_block_type = $rslt->print_block_type;
$print_block_table = explode(",",$rslt->print_block_table);
$print_block_column = explode(",",$rslt->print_block_column);
$month_column = explode(",",$rslt->month_column);
$table_td = "";
foreach($print_block_table as $table){
$table_td .= "<tr><td>$table</td><tr>";
}
$table_list = "<table> $table_td </table>";
$column_td = "";
foreach($print_block_column as $column){
$column_arr = explode('.', $column);
$column_key = $column_arr[0];
$column_val = $column_arr[1];
$block_col_name = $view_name_rslt[$column_val];
if(!$block_col_name){
$block_col_name = str_replace('_', ' ', $column_val);
}
$block_column_name = ucwords($column_key).'.'.ucwords($block_col_name);
$column_td .= "<tr><td>$block_column_name</td><tr>";
}
$column_list = "<table class='block_table_view'> $column_td </table>";
$month_td = "";
foreach($month_column as $column){
$column_arr = explode('.', $column);
$column_key = $column_arr[0];
$column_val = $column_arr[1];
$month_col_name = $view_name_rslt[$column_val];
if(!$month_col_name){
$month_col_name = str_replace('_', ' ', $column_val);
}
$month_column_name = ucwords($column_key).'.'.ucwords($month_col_name);
$month_td .= "<tr><td>$month_column_name</td><tr>";
}
$month_list = "<table> $month_td </table>";
$print_block_type = $print_block_type_list[$print_block_type];
$print_table_list[$prime_print_block_id] = $print_info_name ." - ".$print_block_name;
$tr_line .= "<tr>
<td>$print_info_name</td>
<td>$print_block_name</td>
<td>$print_block_type</td>
<td>$table_list</td>
<td>$column_list</td>
<td>$month_list</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=edit_print_block('$prime_print_block_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_print_block('$prime_print_block_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$print_block_list = "<table class='table table-bordered table-stripted' id='print_info_table'>
<tr class='inline_head'>
<th>Print Info Name</th>
<th>Block Name</th>
<th>Block Type</th>
<th>Block Table</th>
<th>Block Columns</th>
<th>Month Column</th>
<th>Edit</th>
<th>Delete</th>
</tr>
$tr_line
</table>";
return json_encode(array('success' => TRUE,'print_block_list' => $print_block_list,'print_table_list' => $print_table_list));
}
//PRINT BLOCK COLUMNS
public function get_print_block_table(){
$print_block_table = ltrim(implode(",",$this->input->post('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();
$payslip_based_on = $this->company_info();
$payslip_based_on = $payslip_based_on[0]->payslip_based_on;
$print_block_column[""] = "---- Select Column ----";
$pwd_block_column[""] = "---- Select Column ----";
$folder_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,field_type 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);
$field_type = $view_name_result[0]->field_type;
$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($payslip_based_on === $column_value){
$folder_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
if((int)$field_type === 1 || (int)$field_type === 4){
$pwd_block_column[$table_value.".".$column_value] = $table_name . " - ". strtoupper(str_replace("_"," ",$column_value));
}
if(!$column_name){
$print_block_column[$table_value.".".$column_value] = $table_name . " - ". strtoupper(str_replace("_"," ",$column_value));
}
}
echo json_encode(array('success' => true,'print_block_column'=>$print_block_column,'pwd_block_column'=>$pwd_block_column,'folder_block_column'=>$folder_block_column));
}
//EDIT PRINT BLOCK OPERATION
public function edit_print_block(){
$prime_print_block_id = (int)$this->input->post('prime_print_block_id');
$print_qry = 'SELECT * FROM cw_print_block where prime_print_block_id = "'.$prime_print_block_id.'" and trans_status = 1';
$print_data = $this->db->query("CALL sp_a_run ('SELECT','$print_qry')");
$print_rslt = $print_data->result();
$print_data->next_result();
//Get View Name
$in_module_name = str_replace('cw_', '', $print_rslt[0]->print_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());
$print_block_table = $print_rslt[0]->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") ';
$column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_colums')");
$column_result = $column_info->result();
$column_info->next_result();
$payslip_based_on = $this->company_info();
$payslip_based_on = $payslip_based_on[0]->payslip_based_on;
$print_block_column[""] = "---- Select Column ----";
$pwd_block_column[""] = "---- Select Column ----";
$folder_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);
$print_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
$pwd_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
if($payslip_based_on === $column_value){
$folder_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
// }
}
echo json_encode(array('success' => TRUE,'print_block_column' => $print_block_column,'print_info' => $print_rslt[0],'pwd_block_column'=>$pwd_block_column,'folder_block_column'=>$folder_block_column));
}
//REMOVE PRINT INFO OPERATION
public function remove_print_block(){
$prime_print_block_id = (int)$this->input->post('prime_print_block_id');
$print_block_module_id = $this->input->post('print_block_module_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_print_block SET trans_status = 0,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_print_block_id = "'.$prime_print_block_id.'"';
$delete = $this->db->query("CALL sp_a_run ('UPDATE','$remove_qry')");
$delete->next_result();
$print_block = $this->get_print_block_list($print_block_module_id);
$print_block_rslt = json_decode($print_block);
$print_block_list = $print_block_rslt->print_block_list;
$print_table_list = $print_block_rslt->print_table_list;
echo json_encode(array('success' => true, 'message'=>'Print block deleted Successfully!!!','print_block_list'=>$print_block_list,'print_table_list'=>$print_table_list));
}
/* ==============================================================*/
/* ================== PRINT BLOCK OPEARTION - END ==============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================ PRINT TABLE OPEARTION - START ==============*/
/* ==============================================================*/
public function get_print_table_info(){
$print_table_list = (int)$this->input->post('print_table_list');
$print_qry = 'SELECT * FROM cw_print_block where prime_print_block_id = "'.$print_table_list.'" and trans_status = 1';
$print_data = $this->db->query("CALL sp_a_run ('SELECT','$print_qry')");
$print_rslt = $print_data->result();
$print_data->next_result();
// foreach($print_rslt as $rslt){
$prime_print_block_id = $print_rslt[0]->prime_print_block_id;
$print_block_module_id = $print_rslt[0]->print_block_module_id;
$print_block_for = $print_rslt[0]->print_block_for;
$print_block_name = $print_rslt[0]->print_block_name;
$print_block_type = $print_rslt[0]->print_block_type;
$print_block_table = explode(",",$print_rslt[0]->print_block_table);
$print_block_column = $print_rslt[0]->print_block_column;
$table_list = array();
$table_list[""] = "---- Select Table ----";
foreach($print_block_table as $table_value){
$table_name = substr((ucwords(str_replace("_"," ",$table_value))),3);
$table_list[$table_value] = $table_name;
}
$prime_in = '"'.str_replace(",",'","', $print_rslt[0]->print_block_table);
$custom_in = str_replace(",",'_cf","', $print_rslt[0]->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.')';
$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");
$print_tab_query = 'SELECT * FROM cw_print_table WHERE print_table_for_id = "'.$prime_print_block_id.'" AND print_table_module_id = "'.$print_block_module_id.'" order by abs(line_sort)';
$print_tab_info = $this->db->query("CALL sp_a_run ('SELECT','$print_tab_query')");
$print_tab_result = $print_tab_info->result();
$print_tab_info->next_result();
//print_r($print_tab_result);
$print_table_for_id = form_input(array( 'name' =>'print_table_for_id','id' =>'print_table_for_id', 'class' => 'form-control input-sm','value' =>$prime_print_block_id,'type'=>'Hidden'));
$print_table_module_id = form_input(array( 'name' =>'print_table_module_id','id' =>'print_table_module_id', 'class' => 'form-control input-sm','value' =>$print_block_module_id,'type'=>'Hidden'));
$table_tr_line = "";
$table_count = 0;
$condition_table_count = count($print_block_table ?? []) - 1; //round(count($condition_table)/2);
for($i=1;$i<= $condition_table_count;$i++){
$prime_print_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($print_tab_result){
$prime_print_table_id = $print_tab_result[$table_count]->prime_print_table_id;
$line_prime_table = $print_tab_result[$table_count]->line_prime_table;
$line_prime_col = $print_tab_result[$table_count]->line_prime_col;
$line_join_type = $print_tab_result[$table_count]->line_join_type;
$line_join_table = $print_tab_result[$table_count]->line_join_table;
$line_join_col = $print_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_print_table_id[]",'class' => 'form-control input-sm','value' =>$prime_print_table_id,'type'=>'Hidden','id'=>'prime_print_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_data(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_list1,$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_data(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 = "$print_table_for_id $print_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_print_table'>Save</button>
</div>";
// }
$where_query = 'SELECT * FROM cw_print_table_where WHERE where_module_id = "'.$print_block_module_id.'" and where_for_id = "'.$prime_print_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_print_block_id'=>$prime_print_block_id, 'print_block_module_id'=>$print_block_module_id,'print_table_block'=>$table_content, 'column_list'=>$column_list,'where_condition'=>$where_condition,));
}
public function save_print_table(){
$print_table_for_id = $this->input->post('print_table_for_id');
$print_table_module_id = $this->input->post('print_table_module_id');
$prime_print_table_id = $this->input->post('prime_print_table_id[]');
$line_prime_table = $this->input->post('line_prime_table[]');
$line_prime_col = $this->input->post('line_prime_col[]');
$line_join_type = $this->input->post('line_join_type[]');
$line_join_table = $this->input->post('line_join_table[]');
$line_join_col = $this->input->post('line_join_col[]');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$tab_count = 0;
$remove_query = 'UPDATE cw_print_table SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE print_table_for_id = "'.$print_table_for_id.'" and print_table_module_id = "'.$print_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_print_table_id_val = $prime_print_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_print_table_id_val === 0){
$table_query = 'insert into cw_print_table (print_table_for_id,print_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 ("'.$print_table_for_id.'","'.$print_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_print_table SET trans_status = 1, line_prime_table = "'.$line_prime_table_val.'",line_prime_col = "'.$line_prime_col_val.'",line_join_type = "'.$line_join_type_val.'",line_join_table = "'.$line_join_table_val.'",line_join_col = "'.$line_join_col_val.'",line_sort = "'.$i.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" WHERE prime_print_table_id = "'.$prime_print_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_print_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_print_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_print_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_print_where_id = (int)$exist_result[0]->prime_print_where_id;
$upd_qry = 'UPDATE cw_print_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_print_where_id = "'.$prime_print_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(){
$where_module_id = $this->input->post('where_module_id');
$query_column = $this->input->post('query_column');
$label_name = explode(".",$query_column);
$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";
}
}
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));
}else{
echo json_encode(array('success' => true,'type'=>'session_list','msg'=>"Session list","session_list"=>$session_list));
}
}else{
echo json_encode(array('success' => true,'type'=>'session_list','msg'=>"Session list","session_list"=>$session_list));
}
}
/* ==============================================================*/
/* ================== PRINT TABLE OPEARTION - END ==============*/
/* ==============================================================*/
/* ==============================================================*/
/* ================ PRINT DESIGN OPEARTION - START ==============*/
/* ==============================================================*/
public function save_print_design(){
$print_design = $this->input->post('content');
$print_design = str_replace('"',"~",$print_design);
$print_design_for = $this->input->post('assign_table_info');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$exist_query = 'SELECT * FROM cw_print_design WHERE print_design_for = "'.$print_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_print_design (print_design_for, print_design,trans_created_by, trans_created_date) VALUES ("'.$print_design_for.'","'.$print_design.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$search_qry')");
echo json_encode(array('success' => true,'message'=>"Print design added successfully !!!"));
}else{
$prime_print_design_id = (int)$exist_result[0]->prime_print_design_id;
$upd_qry = 'UPDATE cw_print_design SET print_design_for = "'.$print_design_for.'",print_design = "'.$print_design.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_print_design_id = "'.$prime_print_design_id.'"';
$this->db->query($upd_qry);
echo json_encode(array('success' => true,'message'=>"Print design updated successfully !!!"));
}
}
public function assign_table_info(){
$assign_table_info = $this->input->post('assign_table_info');
$print_qry = 'SELECT * FROM cw_print_block where print_block_for = "'.$assign_table_info.'" and trans_status = 1';
$print_data = $this->db->query("CALL sp_a_run ('SELECT','$print_qry')");
$print_rslt = $print_data->result();
$print_data->next_result();
$assign_table_block[] = "--- Select Print block ---";
$list_view_value[""] = "--- Select list View ---";
foreach($print_rslt as $rslt){
$prime_print_block_id = $rslt->prime_print_block_id;
$print_block_type = $rslt->print_block_type;
$print_block_name = ucwords($rslt->print_block_name);
$assign_table_block[$prime_print_block_id] = $print_block_name;
if((int)$print_block_type === 2){
$replce_block = "@".strtolower(str_replace(" ","_",$print_block_name))."@";
$list_view_value[$replce_block] = $print_block_name;
}
}
$design_query = 'SELECT * FROM cw_print_design WHERE print_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();
$print_design = "";
if($design_result){
$print_design = $design_result[0]->print_design;
$print_design = str_replace('~','"',$print_design);
}
echo json_encode(array('success' => TRUE,'assign_table_block' => $assign_table_block,'print_design' => $print_design,'list_view_value' => $list_view_value));
}
public function assign_table_block(){
$assign_table_block = $this->input->post('assign_table_block');
$print_table_list = (int)$this->input->post('print_table_list');
$print_qry = 'SELECT * FROM cw_print_block where prime_print_block_id = "'.$assign_table_block.'" and trans_status = 1';
$print_data = $this->db->query("CALL sp_a_run ('SELECT','$print_qry')");
$print_rslt = $print_data->result();
$print_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($print_rslt as $rslt){
$prime_print_block_id = $print_rslt[0]->prime_print_block_id;
$print_block_module_id = $print_rslt[0]->print_block_module_id;
$print_block_for = $print_rslt[0]->print_block_for;
$print_block_name = $print_rslt[0]->print_block_name;
$print_block_type = $print_rslt[0]->print_block_type;
$print_block_table = $print_rslt[0]->print_block_table;
$print_block_column = $print_rslt[0]->print_block_column;
$new_table = explode(",",$print_block_table);
if((int)$print_block_type === 2){
$assign_type = array(0=>"--- Select assign ---",5=>"List view value");
}else{
$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.".";
if(($table === "cw_transactions") || ($table === "cw_custom_employees")){//newly added
$table_list = "employees";
$column_list = str_replace($replce_table,"",$print_block_column);
}else{
$table_list = str_replace("cw_","",$table);
$column_list = str_replace($replce_table,"",$print_block_column);
}
$column_list = str_replace(",",'","', $column_list);
$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();
if(strpos($print_block_column, 'transactions_month') == true) {
if($table === "cw_transactions"){
$assign_label["Transactions Month"] = "Transactions Month";
$assign_short_label["Transactions Month"] = "Transactions Month";
$replce_column = "@transactions_month@";
$assign_value_for[$replce_column] = "Transactions Month - ". $view_name;
}
}
//amount number is changed to in words for net pays
if(strpos($print_block_column, 'net_pay') == true) {
$assign_value_for['@net_pay_words@'] = "net_pay_words - Net Pay Words";
}else
if(strpos($print_block_column, 'salary') == true) {
$assign_value_for['@salary_words@'] = "salary_words - Salary Words";
}
foreach($colums_result as $colums){
$label_name = $colums->label_name;
$view_name = $colums->view_name;
if($label_name === "process_month"){
$label_name = "transactions_month";
}
if($view_name === "Process Month"){
$view_name = "Transactions Month";
}
$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));
}
public function split_table_info(){
$split_table_info = $this->input->post('split_table_info');
$split_colum = $this->split_table_info_rslt($split_table_info);
echo json_encode(array('success' => true,'split_colum'=>$split_colum));
}
public function split_table_info_rslt($split_table_info){
$print_qry = 'SELECT * FROM cw_print_block where prime_print_block_id = "'.$split_table_info.'" and trans_status = 1';
$print_data = $this->db->query("CALL sp_a_run ('SELECT','$print_qry')");
$print_rslt = $print_data->result();
$print_data->next_result();
$split_colum[""] = "--- Select Data Base value ---";
// foreach($print_rslt as $rslt){
$prime_print_block_id = $print_rslt[0]->prime_print_block_id;
$print_block_module_id = $print_rslt[0]->print_block_module_id;
$print_block_for = $print_rslt[0]->print_block_for;
$print_block_name = $print_rslt[0]->print_block_name;
$print_block_type = $print_rslt[0]->print_block_type;
$print_block_table = $print_rslt[0]->print_block_table;
$print_block_column = $print_rslt[0]->print_block_column;
$new_table = explode(",",$print_block_table);
foreach($new_table as $table){
$replce_table = $table.".";
if($table === "cw_transactions"){
$table_list = "employees";
$column_list = str_replace($replce_table,"",$print_block_column);
}else{
$table_list = str_replace("cw_","",$table);
$column_list = str_replace($replce_table,"",$print_block_column);
}
$column_list = str_replace(",",'","', $column_list);
$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();
if($table === "cw_transactions"){
$replce_column = "@transactions_month@";
$split_colum[$replce_column] = "Transactions Month - ". $view_name;
}
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;
}
$replce_column = "@".$label_name."@";
$split_colum[$replce_column] = $label_name." - ".$view_name ;
}
}
// }
ksort($split_colum);
return $split_colum;
}
public function split_save(){
$split_table_info = $this->input->post('split_table_info');
$split_info = $this->input->post('split_info');
$split_colum = $this->input->post('split_colum');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$print_qry = 'SELECT * FROM cw_print_block where prime_print_block_id = "'.$split_table_info.'" and trans_status = 1';
$print_data = $this->db->query("CALL sp_a_run ('SELECT','$print_qry')");
$print_rslt = $print_data->result();
$print_data->next_result();
$print_block_module_id = $print_rslt[0]->print_block_module_id;
$exist_query = 'SELECT * FROM cw_print_split WHERE split_table_info = "'.$split_table_info.'" and split_info = "'.$split_info.'" 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){
$split_qry = 'INSERT INTO cw_print_split (split_table_info, split_info,split_colum,trans_created_by, trans_created_date) VALUES ("'.$split_table_info.'","'.$split_info.'","'.$split_colum.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$split_qry')");
$table_info = $this->get_split_list($print_block_module_id);
echo json_encode(array('success' => true,'message'=>"Split added successfully !!!",'table_info'=>$table_info));
}else{
$prime_print_split_id = (int)$exist_result[0]->prime_print_split_id;
$split_upd_qry = 'UPDATE cw_print_split SET split_table_info = "'.$split_table_info.'",split_info = "'.$split_info.'",split_colum = "'.$split_colum.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_print_split_id = "'.$prime_print_split_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$split_upd_qry')");
$table_info = $this->get_split_list($print_block_module_id);
echo json_encode(array('success' => true,'message'=>"Split updated successfully !!!",'table_info'=>$table_info));
}
}
public function get_split_list($print_block_module_id){
$get_split_list = 'SELECT * FROM cw_print_block left join cw_print_split on prime_print_block_id = split_table_info WHERE cw_print_block.trans_status = 1 and cw_print_split.trans_status = 1 and print_block_module_id = "'.$print_block_module_id.'"';
$get_split_info = $this->db->query("CALL sp_a_run ('SELECT','$get_split_list')");
$split_result = $get_split_info->result();
$get_split_info->next_result();
$tr_line = "";
$split_info_array = array(1=>"Loan Amount");
foreach($split_result as $split){
$prime_print_split_id = $split->prime_print_split_id;
$split_table_info = $split->split_table_info;
$split_info = $split->split_info;
$split_colum = $split->split_colum;
$print_block_name = $split->print_block_name;
$split_info = $split_info_array[$split_info];
$tr_line .="<tr>
<td>$print_block_name</td>
<td>$split_info</td>
<td>$split_colum</td>
<td style='text-align:center;'><a class='btn btn-xs btn-edit' onclick=edit_split_info('$prime_print_split_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_split_info('$prime_print_split_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td>
</tr>";
}
$table_info = "<table class='table table-bordered table-stripted'>
<thead>
<tr>
<th>Block Name</th>
<th>Split up info</th>
<th>Split column</th>
<th>Edit</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $table_info;
}
//EDIT PRINT BLOCK OPERATION
public function edit_split_info(){
$prime_print_split_id = (int)$this->input->post('prime_print_split_id');
$split_qry = 'SELECT split_table_info,split_info,split_colum FROM cw_print_split where prime_print_split_id = "'.$prime_print_split_id.'" and trans_status = 1';
$split_data = $this->db->query("CALL sp_a_run ('SELECT','$split_qry')");
$split_rslt = $split_data->result();
$split_data->next_result();
$split_table_info = $split_rslt[0]->split_table_info;
$split_column = $this->split_table_info_rslt($split_table_info);
echo json_encode(array('success' => TRUE,'split_rslt' => $split_rslt[0],'split_column_rslt' => $split_column));
}
public function remove_split_info(){
$prime_print_split_id = (int)$this->input->post('prime_print_split_id');
$print_block_module_id = $this->input->post('print_block_module_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$remove_qry = 'UPDATE cw_print_split SET trans_status = 0,trans_deleted_by = "'.$logged_id.'",trans_deleted_date = "'.$date.'" where prime_print_split_id = "'.$prime_print_split_id.'"';
$delete = $this->db->query("CALL sp_a_run ('UPDATE','$remove_qry')");
$delete->next_result();
$table_info = $this->get_split_list($print_block_module_id);
echo json_encode(array('success' => true, 'message'=>'Print block deleted Successfully!!!','table_info'=>$table_info));
}
/* ==============================================================*/
/* ================ PRINT DESIGN OPEARTION - END ==============*/
/* ==============================================================*/
public function get_block_table(){
$print_block_for = (int)$this->input->post('print_block_for');
$print_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_print_info` where prime_print_info_id = \'$print_block_for\' and trans_status = 1')");
$print_info_rlst = $print_info->result();
$print_info->next_result();
if($print_info_rlst){
$print_type = $print_info_rlst[0]->print_type;
$print_block_column[""] = "---- Select Column ----";
// $print_type = 2;
if((int)$print_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") 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();
$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){
$print_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));
$print_block_column[$table_value.".".$column_value] = $table_name . " - ". $column_name;
}
}
}
echo json_encode(array('success' => TRUE ,'print_type'=>$print_type,'print_block_column'=>$print_block_column));
}else{
echo json_encode(array('success' => FALSE ,'message'=>'Contact Admin...!'));
}
}
public function email_assign_table_info(){
$assign_table_info = $this->input->post('assign_table_info');
$print_qry = 'SELECT * FROM cw_print_block where print_block_for = "'.$assign_table_info.'" and trans_status = 1';
$print_data = $this->db->query("CALL sp_a_run ('SELECT','$print_qry')");
$print_rslt = $print_data->result();
$print_data->next_result();
$assign_table_block[] = "--- Select Print block ---";
$list_view_value[""] = "--- Select list View ---";
foreach($print_rslt as $rslt){
$prime_print_block_id = $rslt->prime_print_block_id;
$print_block_type = $rslt->print_block_type;
$print_block_name = ucwords($rslt->print_block_name);
$assign_table_block[$prime_print_block_id] = $print_block_name;
if((int)$print_block_type === 2){
$replce_block = "@".strtolower(str_replace(" ","_",$print_block_name))."@";
$list_view_value[$replce_block] = $print_block_name;
}
}
$design_query = 'SELECT * FROM cw_email_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_bcc = $design_result[0]->email_bcc;
$to_email = $design_result[0]->to_email;
}
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,'email_bcc'=>$email_bcc,'to_email'=>$to_email));
}
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_bcc = $this->input->post('email_bcc');
$to_email = $this->input->post('to_email');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$exist_query = 'SELECT * FROM cw_email_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_email_design (email_design_for, email_design,email_subject,to_email,email_bcc,trans_created_by, trans_created_date) VALUES ("'.$email_design_for.'","'.$email_design.'","'.$email_subject.'","'.$to_email.'","'.$email_bcc.'","'.$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_email_design SET email_design_for = "'.$email_design_for.'",email_design = "'.$email_design.'",email_subject = "'.$email_subject.'",to_email = "'.$to_email.'",email_bcc = "'.$email_bcc.'",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 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_print_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_print_where_id = (int)$exist_result[0]->prime_print_where_id;
$upd_qry = 'UPDATE cw_print_table_where SET trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'",trans_status = "0" where prime_print_where_id = "'.$prime_print_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");
if($email_info_module_id){
$module_qry = 'AND prime_module_id = "'.$email_info_module_id.'"';
}
$exist_query = 'SELECT field_type FROM cw_form_setting WHERE label_name = "'.$assign_value_for.'" '.$module_qry.' 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;
// echo json_encode(array('success' => true,'print_block_column'=>$print_block_column));
}
public function company_info(){
$company = 'select * from cw_company_information where cw_company_information.trans_status = 1';
$comp_info = $this->db->query("CALL sp_a_run ('SELECT','$company')");
$comp_result = $comp_info->result();
$comp_info->next_result();
return $comp_result;
}
public function get_password_column(){
$form_qry = 'select label_name,view_name from cw_form_setting where trans_status = 1 and field_type in(1,4) and field_show = 1 and input_view_type !=3';
$form_info = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_rslt = $form_info->result();
$form_info->next_result();
$pick_key = array_column($form_rslt, 'label_name');
$pick_val = array_column($form_rslt, 'view_name');
$final_pick = array_combine( $pick_key, $pick_val);
return $final_pick;
}
}
?>