File: /home/cafsindia/hrms_cafsinfotech_in/OLD/ZRM/application/controllers/Bank_template.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Bank_template extends Action_controller{
public function __construct(){
parent::__construct('bank_template');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$data['table_headers']=$this->xss_clean(get_bank_template_setting_headers());
$this->load->view('bank_template/manage',$data);
}
//LOAD MODEL PAGE VIEW WITH DATA
public function view($form_view_id=-1){
$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();
$template_for_list[""] = "---- Template For ----";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$template_for_list[$role_id] = $category_name;
}
$data['template_for_list'] = $template_for_list;
$data['form_view_id'] = $form_view_id;
$this->load->view("$this->control_name/form",$data);
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
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 = "prime_bank_template_setting_id";
}
if(!$order){
$order = "asc";
}
$this->db->select('prime_bank_template_setting_id,template_name');
$this->db->from('bank_template_setting');
if($search){
$this->db->group_start();
$this->db->like('template_name',$search);
$this->db->group_end();
}
$this->db->where('bank_template_setting.trans_status',1);
$this->db->order_by($sort,$order);
if($rows>0){
$this->db->limit($rows, $limit_from);
}
$template_info = $this->db->get();
$template_details = $template_info->result();
$data_rows = array();
foreach ($template_details as $bank_template_setting){
$data_rows[]=get_bank_template_setting_datarows($bank_template_setting,$this);
}
$data_rows=$this->xss_clean($data_rows);
$num_rows = $template_info->num_rows();
echo json_encode(array('total'=>$num_rows,'rows'=>$data_rows));
}
//Get Search By List
public function get_search_by_list(){
$search_by = $this->input->post('search_by');
if($search_by === "1"){
$table = "cw_category";
$prime_id = "prime_category_id";
$columns = "category_name";
$search_by_list = "<option value=''>---- Select Category ----</option>";
}else
if($search_by === "2"){
$table = "cw_department";
$prime_id = "prime_department_id";
$columns = "department";
$search_by_list = "<option value=''>---- Select Department ----</option>";
}else
if($search_by === "3"){
$table = "cw_designation";
$prime_id = "prime_designation_id";
$columns = "designation";
$search_by_list = "<option value=''>---- Select Designation ----</option>";
}else
if($search_by === "4"){
$table = "cw_bank_name";
$prime_id = "prime_bank_name_id";
$columns = "bank_name";
$search_by_list = "<option value=''>---- Select Bank ----</option>";
}else
if($search_by === "5"){
$table = "cw_cost_centre";
$prime_id = "prime_cost_centre_id";
$columns = "cost_centre";
$search_by_list = "<option value=''>---- Select Cost Centre ----</option>";
}else
if($search_by === "6"){
$table = "cw_grade";
$prime_id = "prime_grade_id";
$columns = "grade";
$search_by_list = "<option value=''>---- Select Grade ----</option>";
}else
if($search_by === "7"){
$table = "cw_location";
$prime_id = "prime_location_id";
$columns = "location";
$search_by_list = "<option value=''>---- Select Location ----</option>";
}
$search_by_query = 'SELECT * FROM '.$table.' WHERE trans_status =1';
$search_by_info = $this->db->query("CALL sp_a_run ('SELECT','$search_by_query')");
$search_by_result = $search_by_info->result();
$search_by_info->next_result();
//print_r($search_by_result); die;
foreach($search_by_result as $result){
$id = $result->$prime_id;
$name = $result->$columns;
$search_by_list .= "<option value='$id'>$name</option>";
}
echo $search_by_list;
}
//VIEW TEMPLATE /*EDIT BY SVK*/
public function view_template($bank_template_id){
$search_by = $this->input->post('search_by');
$bank_template_for = $this->input->post('bank_template_for');
$month = $this->input->post('month');
$company_add = $this->input->post('company_address');
$separate_emp = $this->input->post('separate_emp');
if((int)$company_add === 1){
$sel_comp_add_qry = 'select * from cw_company_information where trans_status =1';
$sel_comp_add_data = $this->db->query("CALL sp_a_run ('SELECT','$sel_comp_add_qry')");
$sel_comp_add_result = $sel_comp_add_data->result();
$sel_comp_add_data->next_result();
$comp_name = $sel_comp_add_result[0]->company_name;
$comp_add = $sel_comp_add_result[0]->address;
$comp_city = $sel_comp_add_result[0]->city;
$comp_state = $sel_comp_add_result[0]->state;
$comp_country = $sel_comp_add_result[0]->country;
$bank_data_write = $comp_name.",\n".$comp_add.",\n".$comp_city.",\n".$comp_state.",\n".$comp_country."\n\n";
}else{
$bank_data_write = "";
}
if((int)$separate_emp === 1){
$term_qry = " and termination_status = 1";
}else{
$term_qry = " and termination_status = 0";
}
$stop_exit_qry = 'select GROUP_CONCAT(prime_employees_id) as ids from cw_employees_stop_payment where stop_pay_month = "'.$month.'" and stop_payment_type != 3 and trans_status = 1';
$stop_exit_data = $this->db->query("CALL sp_a_run ('SELECT','$stop_exit_qry')");
$stop_exit_result = $stop_exit_data->result();
$stop_exit_data->next_result();
$stop_ids = $stop_exit_result[0]->ids;
$stop_qry = "";
if($stop_ids){
$stop_qry = " and cw_employees.prime_employees_id not in ($stop_ids)";
}
$payroll_exit_qry = 'select transactions_month from cw_transactions where transactions_month = "'.$month.'" and trans_status = 1 limit 1';
$payroll_exit_data = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exit_qry')");
$payroll_exit_result = $payroll_exit_data->result();
$payroll_exit_data->next_result();
$payroll_exit_count = $payroll_exit_data->num_rows();
$arr = array('1'=>"role",'2'=>"department",'3'=>"designation",'4'=>"bank_name",'5'=>"cost_centre",'6'=>"grade",'7'=>"location",'8'=>"Template");
$field_name = "cw_employees.".$arr[$search_by];
if($search_by === "8"){
$emp_query = "";
}else{
$emp_query = ' and '.$field_name.' = '.$bank_template_for.'';
}
$prime_table_qry = 'select select_table,table_column from cw_bank_template_setting where prime_bank_template_setting_id = "'.$bank_template_id.'" and cw_bank_template_setting.trans_status = 1';
$prime_table_data = $this->db->query("CALL sp_a_run ('SELECT','$prime_table_qry')");
$prime_table_count = $prime_table_data->num_rows();
$prime_table_data->next_result();
if((int)$prime_table_count > 0){
$prime_table_result = $prime_table_data->result();
$tables_count = count(explode(",",$prime_table_result[0]->select_table));
$table_column_count = count(explode(",",$prime_table_result[0]->table_column));
$tables_count = (int)$tables_count - 1;
$join_table_qry = 'select * from cw_bank_template_table where join_for = "'.$bank_template_id.'" and cw_bank_template_table.trans_status = 1 order by line_sort ASC';
$join_table_data = $this->db->query("CALL sp_a_run ('SELECT','$join_table_qry')");
$join_table_count = $join_table_data->num_rows();
$join_table_data->next_result();
$join_table_result = $join_table_data->result();
$check_val = $join_table_count - 1;
if((int)$join_table_count === (int)$tables_count){
$bank_char_table_qry = 'select * from cw_bank_template_char_setting where bank_template_id = "'.$bank_template_id.'" and cw_bank_template_char_setting.trans_status = 1 ';
$bank_char_table_data = $this->db->query("CALL sp_a_run ('SELECT','$bank_char_table_qry')");
$bank_char_table_count = $bank_char_table_data->num_rows();
$bank_char_table_data->next_result();
if((int)$bank_char_table_count === (int)$table_column_count){
$column_qry = 'select * from cw_bank_template_tab_view inner join cw_bank_template_char_setting on template_column = table_column left join cw_bank_template_setting on cw_bank_template_setting.prime_bank_template_setting_id = cw_bank_template_tab_view.bank_template_id where cw_bank_template_tab_view.bank_template_id = "'.$bank_template_id.'" and cw_bank_template_tab_view.trans_status = 1 order by table_sort ASC';
$column_data = $this->db->query("CALL sp_a_run ('SELECT','$column_qry')");
$check_count = $column_data->num_rows();
$column_data->next_result();
$column_result = $column_data->result();
$sort_column = $column_result[0]->sort_column;
if($sort_column){
$sort_qry = "order by $sort_column";
}else{
$sort_qry = "";
}
$template_column = $column_result[0]->table_column;
$template_column = explode(",",$template_column);
$check_module = "";
$check_column = "";
foreach($template_column as $check_col){
$column_name = explode(".",$check_col);
$module_name = str_replace("cw_","",$column_name[0]);
$check_module .= "\"$module_name\",";
$check_column .= "\"$column_name[1]\",";
}
$check_module = rtrim($check_module,',');
$check_column = rtrim($check_column,',');
$view_name_qry = 'select field_type,prime_module_id,label_name,pick_list_type,pick_list,pick_table from cw_form_setting where prime_module_id IN ('.$check_module.') and label_name IN ('.$check_column.') and trans_status = "1"';
$view_name_data = $this->db->query("CALL sp_a_run ('SELECT','$view_name_qry')");
$form_info = $view_name_data->result();
$view_name_data->next_result();
$pick_join_qry = '';
$check_pick_array = array();
$pick_count = 0;
foreach($form_info as $setting){
$field_type = (int)$setting->field_type;
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$label_name = $setting->label_name;
$prime_module_id = $setting->prime_module_id;
$pick_table = $setting->pick_table;
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_join_qry .= " left join $pick_table on cw_$prime_module_id.$label_name = $pick_table.$pick_list_val_1";
$check_pick_array["cw_$prime_module_id.$label_name"] = "$pick_table.$pick_list_val_2";
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_join_qry .= " join $pick_table on cw_$prime_module_id.$label_name = $pick_table.$pick_list_val_1";
$check_pick_array["cw_$prime_module_id.$label_name"] = "$pick_table.$pick_list_val_2";
}$pick_count++;
}
}
foreach($column_result as $column){
$template_column = $column->template_column;
$column_name = substr($template_column, strpos($template_column, ".") + 1);
$char_length = $column->char_length;
$char_space = $column->char_space;
$char_align = $column->char_align;
$prefix = $column->prefix;
if($pick_count > 0){
foreach($check_pick_array as $key => $value){
if($key === $template_column){
$template_column = $value;
}
}
}
$column_select_qry .= "CONCAT('$prefix ',RPAD($template_column,$char_length+1,' '),' ',REPEAT(' ',$char_space)) as $column_name,";
}
$column_select_qry = "SELECT ".rtrim($column_select_qry,',')." from";
//Build Join Query
$join_table_qry = 'select * from cw_bank_template_table where join_for = "'.$bank_template_id.'" and cw_bank_template_table.trans_status = 1 order by line_sort ASC';
$join_table_data = $this->db->query("CALL sp_a_run ('SELECT','$join_table_qry')");
$join_table_result = $join_table_data->result();
$join_table_data->next_result();
$where_trans = "";
$line_qry = "";
foreach($join_table_result as $join){
$line_prime_table = $join->line_prime_table;
$line_prime_col = $join->line_prime_col;
$line_join_type = $join->line_join_type;
$line_join_table = $join->line_join_table;
$line_join_col = $join->line_join_col;
$line_qry .= " $line_join_type join $line_join_table on $line_prime_col = $line_join_col";
$where_trans .= "$line_join_table.trans_status = 1 and ";
}
$final_qry = "$column_select_qry $line_prime_table $line_qry $pick_join_qry where cw_transactions.transactions_month = '$month' and $where_trans $line_prime_table.trans_status = 1 $stop_qry $emp_query $sort_qry";
$final_data = $this->db->query($final_qry);
$final_data_result = $final_data->result_array();
$count = count($final_data_result);
for($i=0; $i<= $count; $i++){
foreach ($final_data_result[$i] as $key => $value) {
$bank_data_write .= $value;
}
$bank_data_write = $bank_data_write."\n";
}
$folder = "Bank_templates";
if (!file_exists($folder)){
mkdir($folder, 0777, true);
}
$file_name = $field_name."_".$month.".txt";
$myfile = fopen($folder."/".$file_name, "w") or die("Unable to open file!");
fwrite($myfile, $bank_data_write);
fclose($myfile);
echo json_encode(array('success' => TRUE, 'message' => "Successfully Saved",'data'=>$bank_data_write,'file_name'=>$file_name));
}else{
echo json_encode(array('success' => False, 'message' => "Please Add Template Column information in bank template setting.!"));
}
}else{
echo json_encode(array('success' => False, 'message' => "Please Add Join table information in bank template setting.!"));
}
}else{
echo json_encode(array('success' => False, 'message' => "Please Contact Admin..!"));
}
}
}
?>