File: //home/cafsindia/hrms_allyindian_com/application_bk/controllers/Bank_template_controller.php
<?php
/**********************************************************
Filename: Bank_template_controller.php
Description: Bank Template Controller is generate the report of bank template for all employees
Author: Jaffer Sathik
Created on: 02 May 2019
Reviewed by:
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once("Secure_Controller.php");
abstract class Bank_template_controller extends Secure_Controller{
public $control_name;
public $logged_id;
public $logged_role;
public $template_name;
public function __construct($module_id = NULL){
parent::__construct($module_id);
$this->control_name = strtolower($this->router->fetch_class());
$this->logged_id = $this->session->userdata('logged_id');
$this->logged_role = $this->session->userdata('logged_role');
}
//USER ACCESS THE REPORT CHECKING
public function isvalid($template_id){
$this->template_id = $template_id;
$valid_user_qry = 'select * from cw_bank_template_setting where trans_status = 1 and prime_bank_template_setting_id = "'.$template_id.'" and FIND_IN_SET("'.$this->logged_role.'",template_for)';
$valid_user_info = $this->db->query("CALL sp_a_run ('SELECT','$valid_user_qry')");
$valid_user_result = $valid_user_info->result();
$user_count = $valid_user_info->num_rows();
$valid_user_info->next_result();
if($user_count){
$this->collect_base_info();
return true;
}else{
return false;
}
}
public function collect_base_info(){
$this->get_table_filter_info();
$this->get_base_qry();
}
//table header, filter search and search data info
public function get_table_filter_info(){
$table_qry = 'select * from cw_bank_template_tab_view where trans_status = 1 and bank_template_id="'.$this->template_id.'" order by table_sort asc';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_qry')");
$tab_result = $table_info->result();
$table_info->next_result();
$table_array = array();
foreach($tab_result as $rslt){
$table_column = $rslt->table_column;
$column_name = explode(".",$table_column);
$module_name = $column_name[0];
$label_name = $column_name[1];
$module_id = str_replace("cw_","",$module_name);
if($module_id === "transactions"){
$module_id = "employees";
}
$find_label_qry = 'select * from cw_form_setting where trans_status = 1 and prime_module_id = "'.$module_id.'" and label_name = "'.$label_name.'"';
$find_label_info = $this->db->query("CALL sp_a_run ('SELECT','$find_label_qry')");
$label_result = $find_label_info->result();
$find_label_info->next_result();
$prime_form_id = (int)$label_result[0]->prime_form_id;
$prime_module_id = $label_result[0]->prime_module_id;
$input_view_type = (int)$label_result[0]->input_view_type;
$input_for = (int)$label_result[0]->input_for;
$field_type = (int)$label_result[0]->field_type;
$label_id = $label_result[0]->label_name;
$label_name = ucwords($label_result[0]->view_name);
$pick_list_type = (int)$label_result[0]->pick_list_type;
$pick_list = $label_result[0]->pick_list;
$pick_table = $label_result[0]->pick_table;
$auto_prime_id = $label_result[0]->auto_prime_id;
$auto_dispaly_value = $label_result[0]->auto_dispaly_value;
$field_isdefault = (int)$label_result[0]->field_isdefault;
$table_name = "cw_".$prime_module_id;
$cf_table_name = "cw_".$prime_module_id."_cf";
if($field_isdefault === 1){
$pick_sel_table = "$table_name";
}else
if($field_isdefault === 2){
$pick_sel_table = "$cf_table_name";
}
//TABLE HEADER
$table_array[] = array('label_name'=>$label_id,'view_name'=>$label_name);
//SEARCH FILTERS
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
$pick_query = "select $pick_list from $pick_table where trans_status = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
$this->all_pick[$prime_form_id] = $array_list;
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$this->select_query .= "$pick_query_as.$pick_list_val_2 as $label_id,";
$this->pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $pick_sel_table.$label_id ";
}
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
$this->all_pick[$prime_form_id] = $array_list;
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$this->select_query .= "$pick_query_as.$pick_list_val_2 as $label_id,";
$this->pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $pick_sel_table.$label_id ";
}
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$this->select_query .= "$pick_query_as.$auto_dispaly_value as $label_id,";
$this->pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $pick_sel_table.$label_id ";
}
}else{
if(($input_view_type === 1) || ($input_view_type === 2)){
if($field_isdefault === 1){
$this->select_query .= "$pick_sel_table.$label_id,";
}else
if($field_isdefault === 2){
$this->select_query .= "$pick_sel_table.$label_id,";
}
}
}
$this->fliter_list[] = array('label_id'=> $label_id, 'field_isdefault'=> $field_isdefault, 'array_list'=> $array_list, 'field_type'=> $field_type);
$this->form_info[] = array('prime_form_id'=>$prime_form_id,'prime_module_id'=>$prime_module_id,'field_type'=>$field_type,'pick_list'=>$pick_list,'pick_table'=>$pick_table,'input_view_type'=>$input_view_type,'auto_prime_id'=>$auto_prime_id,'auto_dispaly_value'=>$auto_dispaly_value,'label_id'=>$label_id, 'field_isdefault'=> $field_isdefault,'pick_list_type'=>$pick_list_type);
}
$this->select_query = rtrim($this->select_query,',');
$this->table_info = json_decode(json_encode($table_array));
$this->form_info = json_decode(json_encode($this->form_info));
}
public function get_base_qry(){
$template_tab_query = 'select * from cw_bank_template_setting where prime_bank_template_setting_id = "'.$this->template_id.'" and trans_status = "1"';
$template_tab_data = $this->db->query("CALL sp_a_run ('SELECT','$template_tab_query')");
$template_tab_result = $template_tab_data->result();
$template_tab_data->next_result();
$base_query = "";
if($template_tab_result){
$template_name = $template_tab_result[0]->template_name;
$this->template_name = $template_name;
$template_for = $template_tab_result[0]->template_for;
$table_info = $template_tab_result[0]->select_table;
$table_column = $template_tab_result[0]->table_column;
$table_count = explode(",",$table_info);
$tab_count = count($table_count);
if((int)$tab_count > 1){
//WHERE TABLE JOIN DATA
$table_query = 'select * from cw_bank_template_table where trans_status = 1 and join_for = "'.$this->template_id.'" ORDER BY abs(line_sort) asc';
$table_data = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$table_result = $table_data->result();
$table_data->next_result();
foreach($table_result as $table){
$line_prime_table = $table->line_prime_table;
$line_prime_col = $table->line_prime_col;
$line_join_type = $table->line_join_type;
$line_join_table = $table->line_join_table;
$line_join_col = $table->line_join_col;
$line_sort = $table->line_sort;
$prime_table = $table->line_prime_table;
$module_name = str_replace("cw_","",$line_prime_table);
$prime_id = "prime_".$module_name."_id";
$cf_id = "prime_".$module_name."_cf_id";
$cf_table_name = $this->db->dbprefix($module_name."_cf");
$join_module_name = str_replace("cw_","",$line_join_table);
$join_prime_id = "prime_".$join_module_name."_id";
$join_cf_id = "prime_".$join_module_name."_cf_id";
$join_cf_table_name = $this->db->dbprefix($join_module_name."_cf");
if((int)$line_sort === 1){
if($join_module_name === "transactions"){
$line_table_query .= " $line_prime_table inner join $cf_table_name on $line_prime_table.$prime_id = $cf_table_name.$prime_id $line_join_type join $line_join_table on $line_join_col = $line_prime_col";
}else{
$line_table_query .= " $line_prime_table inner join $cf_table_name on $line_prime_table.$prime_id = $cf_table_name.$prime_id $line_join_type join $line_join_table on $line_join_col = $line_prime_col inner join $join_cf_table_name on $line_join_table.$join_prime_id = $join_cf_table_name.$join_prime_id";
}
}else{
$line_table_query .= " $line_join_type join $line_join_table on $line_join_col = $line_prime_col";
}
}
}else{
$module_name = str_replace("cw_","",$table_info);
$prime_id = "prime_".$module_name."_id";
$cf_id = "prime_".$module_name."_cf_id";
$cf_table_name = $this->db->dbprefix($module_name."_cf");
if(($module_name === "transactions") || ($module_name === "loan_installment")){
$line_table_query = " $table_info";
}else{
$line_table_query = " $table_info inner join $cf_table_name on $table_info.$prime_id = $cf_table_name.$prime_id";
}
}
}
$base_query = "select @SELECT from $line_table_query";
$this->base_query = $base_query;
$this->prime_table = $prime_table;
}
}
?>