File: /home/cafsindia/login_cafsindia_com/application/controllers/Base_controller.php
<?php
/**********************************************************
Filename: Base Controller
Description: Base Controller for all dynamic module controller.
Author: udhayakumar Anandhan
Created on: 12 December 2018
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 Base_controller extends Secure_Controller{
public $control_name;
public $table_info;
public $view_info;
public $form_info;
public $table_search_info;
public $table_search_qry;
public $prime_id;
public $cf_id;
public $prime_table;
public $cf_table;
public $pro_name;
public $logged_id;
public $logged_role;
public $select_query = "";
public $view_select = "";
public $base_query = "" ;
public $pick_query = "";
public $all_pick = array();
public $fliter_list = array();
public $quick_link = array();
public $condition_list = array();
public function __construct($module_id = NULL){
parent::__construct($module_id);
}
//PROVIDE BASE DATA FOR MODULE
public function collect_base_info(){
$this->control_name = strtolower($this->router->fetch_class());
$this->logged_id = $this->session->userdata('logged_id');
$this->logged_role = $this->session->userdata('logged_role');
$this->prime_id = "prime_".$this->control_name."_id";
$this->cf_id = "prime_".$this->control_name."_cf_id";
$this->prime_table = $this->db->dbprefix($this->control_name);
$cf_table_name = $this->control_name."_cf";
$this->cf_table = $this->db->dbprefix($cf_table_name);
$this->pro_name = "sp_".$this->control_name."_search";
$this->base_query = "select @SELECT from $this->prime_table inner join $this->cf_table on $this->prime_table.$this->prime_id = $this->cf_table.$this->prime_id";
$this->get_quick_link();
$this->get_table_info();
$this->get_view_info();
$this->get_form_info();
$this->get_table_search_info();
$this->get_query_and_drop();
$this->get_condition();
}
/* ==============================================================*/
/* =================== BASE FUNCTIONS - START ===================*/
/* ==============================================================*/
// PROVIDE QUICK LINK LIST VIEW
public function get_quick_link(){
$link_query = 'select quicklink from cw_modules where module_id = "'.$this->control_name.'"';
$link_info = $this->db->query("CALL sp_a_run ('SELECT','$link_query')");
$link_result = $link_info->result();
$link_info->next_result();
$this->quick_link = $link_result;
}
// PROVIDE TABLE VIEW
public function get_table_info(){
$table_query = 'select label_name,view_name,field_type from cw_form_setting where prime_module_id = "'.$this->control_name.'" and input_view_type IN (1,2) and table_show = "1" and FIND_IN_SET("'.$this->logged_role.'",field_for) ORDER BY table_sort asc';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$result = $table_info->result();
$table_info->next_result();
$this->table_info = $result;
}
// PROVIDE MODLE VIEWS
public function get_view_info(){
$view_query = 'select * from cw_form_view_setting where prime_view_module_id = "'.$this->control_name.'" and form_view_show = "1" and FIND_IN_SET("'.$this->logged_role.'",form_view_for) ORDER BY form_view_sort asc';
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_query')");
$view_result = $view_data->result();
$view_data->next_result();
$this->view_info = $view_result;
}
// PROVIDE MODLE FORM INPUT VIEWS
public function get_form_info(){
$from_query = 'select * from cw_form_setting where prime_module_id = "'.$this->control_name.'" and field_show = "1" and FIND_IN_SET("'.$this->logged_role.'",field_for) ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
$this->form_info = $form_result;
}
// PROVIDE MODLE TABLE DEFAULT SEARCH
public function get_table_search_info(){
$table_search_query = 'select where_condition from cw_form_table_search where query_module_id = "'.$this->control_name.'" and query_for = "'.$this->logged_role.'" and trans_status = "1"';
$table_search_data = $this->db->query("CALL sp_a_run ('SELECT','$table_search_query')");
$table_search_result = $table_search_data->result();
$table_search_data->next_result();
if($table_search_result){
$where_condition = str_replace('^','"',$table_search_result[0]->where_condition);
$session_query = 'select session_value from cw_session_value where session_for = 1 and trans_status = "1"';
$session_data = $this->db->query("CALL sp_a_run ('SELECT','$session_query')");
$session_result = $session_data->result();
$session_data->next_result();
foreach($session_result as $rslt){
$session_value = $rslt->session_value;
if($session_value !== "access_data"){
$saved_session_val = $this->session->userdata($session_value);
$exist_val = "@".$session_value."@";
$where_condition = str_replace($exist_val,$saved_session_val,$where_condition);
}
}
$this->table_search_info = $where_condition;
}
}
//PROVIDE QUERY AND DROPDOWN VALUES
public function get_query_and_drop(){
$this->select_query = "$this->prime_table.$this->prime_id,";
$this->view_select = "$this->prime_table.$this->prime_id,";
foreach($this->form_info as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$field_isdefault = (int)$setting->field_isdefault;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_field;
$search_show = (int)$setting->search_show;
$array_list = array();
if($field_isdefault === 1){
$pick_sel_table = "$this->prime_table";
}else
if($field_isdefault === 2){
$pick_sel_table = "$this->cf_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];
if($pick_table === "cw_role"){
$pick_query = "select $pick_list from $pick_table where trans_status = 1 and prime_role_id != 1";
}else
if(($pick_table === "cw_login_status") && ((int)$this->logged_role === 3)){
$pick_query = 'select '.$pick_list.' from '.$pick_table.' where trans_status = 1 and FIND_IN_SET("'.$this->logged_role.'",login_role)';
}else{
$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 .= "$this->prime_table.$label_id,";
}else
if($field_isdefault === 2){
$this->select_query .= "$this->cf_table.$label_id,";
}
}
}
if(($input_view_type === 1) || ($input_view_type === 2)){
if($field_isdefault === 1){
$this->view_select .= "$this->prime_table.$label_id,";
}else
if($field_isdefault === 2){
$this->view_select .= "$this->cf_table.$label_id,";
}
if($search_show === 1){
$this->fliter_list[] = array('label_id'=> $label_id, 'label_name'=> $label_name, 'field_isdefault'=> $field_isdefault, 'array_list'=> $array_list, 'field_type'=> $field_type);
}
}
}
$this->select_query = rtrim($this->select_query,',');
$this->view_select = rtrim($this->view_select,',');
// ONLY FOR EMPLOYEE
if($this->control_name === "employees"){
$this->view_select = $this->view_select .',user_name,password';
}
}
// PROVIDE MODLE ONLOAD CONDITION & FORMULA
public function get_condition(){
$condition_query = 'select * from cw_form_condition_formula where cond_module_id = "'.$this->control_name.'" and trans_status = "1" and FIND_IN_SET("'.$this->logged_role.'",condition_for)';
$condition_data = $this->db->query("CALL sp_a_run ('SELECT','$condition_query')");
$condition_result = $condition_data->result();
$condition_data->next_result();
$load_script = "";
foreach($condition_result as $condition){
$prime_cond_id = $condition->prime_cond_id;
$cond_module_id = $condition->cond_module_id;
$condition_label_name = strtolower(str_replace(" ","_",$condition->condition_label_name));
$condition_type = (int)$condition->condition_type;
$condition_check_form = explode(",",$condition->condition_check_form);
$condition_bind_to = explode(",",$condition->condition_bind_to);
$on_bind_input = "";
$on_change_input = "";
$on_blur_input = "";
foreach($condition_check_form as $label_name){
$cond_from_query = 'select field_type,label_name from cw_form_setting where prime_module_id = "'.$this->control_name.'" and label_name in ("'.$label_name.'") ORDER BY input_for,field_sort asc';
$cond_form_data = $this->db->query("CALL sp_a_run ('SELECT','$cond_from_query')");
$cond_form_result = $cond_form_data->result();
$cond_form_data->next_result();
$field_type = (int)$cond_form_result[0]->field_type;
/*============
NOTE: AUTO COMPLETE BOX & FILE UPLOAD BOX ARE NOT INCLUDED IN ON LOAD SCRIPT
============*/
if(($field_type === 1) || ($field_type === 2) || ($field_type === 3) || ($field_type === 11) || ($field_type === 12)){
$on_bind_input .= "$label_name,";
}else
if(($field_type === 5) || ($field_type === 6) || ($field_type === 7)){
$on_change_input .= "$label_name,";
}
if($condition_type === 2){
if(($field_type === 4) || ($field_type === 13)){
$on_blur_input .= "$label_name,";
}
}
}
$check_input = "";
if($on_bind_input){
$on_bind_input = rtrim($on_bind_input,',');
$on_bind_input = str_replace(",",",#",$on_bind_input);
$on_bind_input = "#".$on_bind_input;
$check_input .= "$on_bind_input";
}
if($on_change_input){
$on_change_input = rtrim($on_change_input,',');
$on_change_input = str_replace(",",",#",$on_change_input);
$on_change_input = "#".$on_change_input;
if($check_input){
$check_input .= ",$on_change_input";
}else{
$check_input .= "$on_change_input";
}
}
if($on_blur_input){
$on_blur_input = rtrim($on_blur_input,',');
$on_blur_input = str_replace(",",",#",$on_blur_input);
$on_blur_input = "#".$on_blur_input;
if($check_input){
$check_input .= ",$on_blur_input";
}else{
$check_input .= "$on_blur_input";
}
}
$send_for = implode(",",$condition_check_form);
$send_data = "prime_cond_id:$prime_cond_id,for_input:'$send_for',";
foreach($condition_check_form as $check_form){
$send_data .= "$check_form:$('#$check_form').val(),";
}
$send_data = "{".rtrim($send_data,',')."}";
//BIND VALUE FROM DATABASE TABLE
if($condition_type === 1){
if($on_bind_input){
$fill_input = "";
foreach($condition_bind_to as $bind_to){
$fill_val = "ui.item.".$bind_to;
$fill_input .= "$('#$bind_to').val($fill_val);\n";
}
$send_url = site_url("$this->control_name/bind_autocomplete_suggest");
$load_script .= "$('$on_bind_input').autocomplete({
source: function(request, response) {
$.getJSON('$send_url',{term:request.term, prime_cond_id:$prime_cond_id },response);
},
minChars:2,
autoFocus: true,
delay:10,
appendTo: '.modal-content',
select: function(e, ui) {
$fill_input
return false;
}
});\n";
}
if($on_change_input){
$fill_input = "";
foreach($condition_bind_to as $bind_to){
$fill_val = "rslt[0].".$bind_to;
$fill_input .= "$('#$bind_to').val($fill_val);\n";
}
$send_url = site_url("$this->control_name/bind_change_suggest");
$load_script .= "$('$on_change_input').change(function(){
var isValid = true;
$('$on_change_input').each(function() {
if ($(this).val() === '') {
isValid = false;
$(this).addClass('error');
}else{
$(this).removeClass('error');
}
});
if(isValid){
$.ajax({
type: 'POST',
url: '$send_url',
data:$send_data,
success: function(data) {
var rslt = JSON.parse(data);
$fill_input
}
});
}
});\n";
}
}else
if($condition_type === 2){
$fill_input = "";
foreach($condition_bind_to as $bind_to){
$fill_val = "rslt.".$bind_to;
$fill_input .= "$('#$bind_to').val($fill_val);\n $('#$bind_to').trigger('change');";
}
$send_url = site_url("$this->control_name/calculation_suggest");
$function_info = "function $condition_label_name(){
var isValid = true;
$('$check_input').each(function() {
if ($(this).val() === '') {
isValid = false;
$(this).addClass('error');
}else{
$(this).removeClass('error');
}
});
if(isValid){
$.ajax({
type: 'POST',
url: '$send_url',
data:$send_data,
success: function(data) {
var rslt = JSON.parse(data);
$fill_input
}
});
}
}";
if($on_bind_input){
$load_script .= "$('$on_bind_input').bind('keyup blur change', function(e) {
$condition_label_name();
});\n";
}
if($on_change_input){
$load_script .= "$('$on_change_input').change(function(){
$condition_label_name();
});\n";
}
if($on_blur_input){
$load_script .= "$('$on_blur_input').blur(function(){
$condition_label_name();
});\n";
}
$load_script .= "\n$function_info\n";
}
}
if($load_script){
$this->condition_list[] = $load_script;
}
}
/* ==============================================================*/
/* ==================== BASE FUNCTIONS - END ====================*/
/* ==============================================================*/
/* ==============================================================*/
/* ================= CONDITION OPERATION - START ================*/
/* ==============================================================*/
//PROVIDE ALL SINGLE BOX AUTOCOMPLETE DROP DOWN
public function suggest(){
$search_term = $this->input->post_get('term');
$prime_form_id = $this->input->get('prime_form_id');
$form_query = 'select * from cw_form_setting where prime_form_id = "'.$prime_form_id.'"';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_query')");
$form_result = $form_data->result();
$form_data->next_result();
if($form_result){
$pick_table = $form_result[0]->pick_table;
$pick_list = $form_result[0]->pick_list;
$auto_prime_id = $form_result[0]->auto_prime_id;
$auto_dispaly_value = $form_result[0]->auto_dispaly_value;
$auto_list = "CONCAT(".str_replace(",",'," - ",',$pick_list).") as auto_list";
$suggest_query = "select $auto_prime_id,$auto_dispaly_value,$auto_list from $pick_table where ";
$col_list = explode(",",$pick_list);
$where_query = "";
foreach($col_list as $col){
$where_query .= $col.' like "'.$search_term.'%" or ';
}
$where_query = rtrim($where_query," or ");
$suggest_query .= $where_query;
$suggest_data = $this->db->query("CALL sp_a_run ('SELECT','$suggest_query')");
$suggest_result = $suggest_data->result();
$suggest_data->next_result();
foreach($suggest_result as $result){
$suggest_prime_id = $result->$auto_prime_id;
$suggest_dispaly = $result->$auto_dispaly_value;
$suggest_list = $result->auto_list;
$suggestions[] = array('value' => $suggest_prime_id, 'label' => $suggest_list, 'display_name' => $suggest_dispaly);
}
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
//PROVIDE AUTOCOMPLETE DROP DOWN TO FILL MULTI INPUT BOX
public function bind_autocomplete_suggest(){
$search_term = $this->input->post_get('term');
$prime_cond_id = $this->input->post_get('prime_cond_id');
echo $this->get_bind_final_query("autocomplete",$search_term,$prime_cond_id);
}
//PROVIDE DATA WHILE ON CHANGE EVENT TO FILL MULTI INPUT BOX
public function bind_change_suggest(){
$for_input = $this->input->post_get('for_input');
$prime_cond_id = $this->input->post_get('prime_cond_id');
$search_term = $this->input->post_get($for_input);
echo $this->get_bind_final_query("change",$search_term,$prime_cond_id);
}
//PROVIDE QUERY AND RESULT ARRAY TO BIND INPUT
public function get_bind_final_query($from,$search_term,$prime_cond_id){
/*============
NOTE: VAR FROM NOT USED MAY CAN USE IN FURTURE PROCESS
============*/
$cond_query = 'select * from cw_form_condition_formula where prime_cond_id = "'.$prime_cond_id.'" and trans_status = 1';
$cond_data = $this->db->query("CALL sp_a_run ('SELECT','$cond_query')");
$cond_result = $cond_data->result();
$cond_data->next_result();
$final_qry = "";
if($cond_result){
$cond_module_id = $cond_result[0]->cond_module_id;
$condition_check_form = $cond_result[0]->condition_check_form;
$condition_bind_to = $cond_result[0]->condition_bind_to;
$condition_table = $cond_result[0]->condition_table;
$table_query = 'select * from cw_form_table_cond_for where table_cond_id = "'.$prime_cond_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();
$line_table_query = "";
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;
$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){
$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 inner join $join_cf_table_name on $line_join_table.$join_prime_id = $join_cf_table_name.$join_prime_id";
}
}
if(!$line_table_query){
$module_name = str_replace("cw_","",$condition_table);
$prime_id = "prime_".$module_name."_id";
$cf_id = "prime_".$module_name."_cf_id";
$cf_table_name = $this->db->dbprefix($module_name."_cf");
$line_table_query = " $condition_table inner join $cf_table_name on $condition_table.$prime_id = $cf_table_name.$prime_id ";
}
$for_input_query = 'select * from cw_form_for_input where input_for_cond_id = "'.$prime_cond_id.'" and trans_status = 1';
$for_input_data = $this->db->query("CALL sp_a_run ('SELECT','$for_input_query')");
$for_input_result = $for_input_data->result();
$for_input_data->next_result();
$for_line_input_query = "";
foreach($for_input_result as $for_input){
$line_input_for = $for_input->line_input_for;
$line_input_for_table = $for_input->line_input_for_table;
$line_input_for_col = $for_input->line_input_for_col;
$for_line_input_query .= $line_input_for_col.' like "'.$search_term.'%" or ';
}
if($for_line_input_query){
$for_line_input_query = " where ". rtrim($for_line_input_query," or ");
}
$input_query = 'select * from cw_form_bind_input where input_cond_id = "'.$prime_cond_id.'" and trans_status = 1';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$line_input_query = "";
foreach($input_result as $input){
$line_input_bind_to = $input->line_input_bind_to;
$line_input_bind_table = $input->line_input_bind_table;
$line_input_bind_col = $input->line_input_bind_col;
$select_query = 'select field_type from cw_form_setting where prime_module_id = "'.$cond_module_id.'" and label_name = "'.$line_input_bind_to.'"';
$select_data = $this->db->query("CALL sp_a_run ('SELECT','$select_query')");
$select_result = $select_data->result();
$select_data->next_result();
$field_type = (int)$select_result[0]->field_type;
if(($field_type === 4) || ($field_type === 13)){
$line_input_query .= 'DATE_FORMAT('.$line_input_bind_col.',"%d-%m-%Y") as '.$line_input_bind_to.',';
}else{
$line_input_query .= "$line_input_bind_col as $line_input_bind_to,";
}
}
if($line_input_query){
$line_input_query = rtrim($line_input_query,',');
}else{
$line_input_query = " * ";
}
$final_qry = "select $line_input_query from $line_table_query $for_line_input_query";
}
if($final_qry){
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$line = array();
$lable = "";
foreach($input_result as $input){
$line_input_bind_to = $input->line_input_bind_to;
$rslt_val = $rslt->$line_input_bind_to;
$line[$line_input_bind_to] = $rslt_val;
if($rslt_val){
$lable .= "$rslt_val - ";
}
}
$lable = rtrim($lable," - ");
$line['value'] = '';
$line['label'] = $lable;
$suggestions[] = $line;
}
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
return json_encode($suggestions);
}
//PROVIDE DATA FOR ONCHANGE CALCUATION
public function calculation_suggest(){
$for_input = $this->input->post_get('for_input');
$prime_cond_id = $this->input->post_get('prime_cond_id');
$cond_query = 'select * from cw_form_condition_formula where prime_cond_id = "'.$prime_cond_id.'" and trans_status = 1';
$cond_data = $this->db->query("CALL sp_a_run ('SELECT','$cond_query')");
$cond_result = $cond_data->result();
$cond_data->next_result();
$condition_check_form = explode(",",$cond_result[0]->condition_check_form);
$condition_bind_to = $cond_result[0]->condition_bind_to;
$condition_table = $cond_result[0]->condition_table;
$condition_type = $cond_result[0]->condition_type;
$is_drop_down = (int)$cond_result[0]->is_drop_down;
$cond_drop_down = $cond_result[0]->cond_drop_down;
$fianl_result_array = array();
if($is_drop_down === 1){
$search_term = $this->input->post_get($cond_drop_down);
$input_query = 'select * from cw_form_bind_input where input_cond_id = "'.$prime_cond_id.'" and line_input_bind_table = "'.$search_term.'"';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$line_input_bind_col = "";
foreach($input_result as $input){
$line_input_bind_to = $input->line_input_bind_to;
$line_input_bind_col = $input->line_input_bind_col;
$line_input_bind_col = str_replace("~","'",$line_input_bind_col);
$line_input_bind_col = str_replace("!",'"',$line_input_bind_col);
if($line_input_bind_col){
foreach($condition_check_form as $check_form){
if(strpos($line_input_bind_col,"@$check_form@") !== false) {
$value = $this->input->post_get($check_form);
if(strpos($check_form,"date") !== false) {
$value = new DateTime($value);
$value = $value->format("Y-m-d");
$value = "'$value'";
}
$line_input_bind_col = str_replace("@$check_form@",$value, $line_input_bind_col);
}
}
$dynamic_file_name= $line_input_bind_to."_".$this->logged_id.".php";
unlink("$dynamic_file_name");
$fname = $line_input_bind_to."(){";
$code = "<?php function $fname $line_input_bind_col }?>";
fopen("$dynamic_file_name", "w");
file_put_contents("$dynamic_file_name",$code);
require_once("$dynamic_file_name");
$fianl_result_array[$line_input_bind_to] = $line_input_bind_to();
unlink("$dynamic_file_name");
}
}
}else{
$input_query = 'select * from cw_form_bind_input where input_cond_id = "'.$prime_cond_id.'"';
$input_data = $this->db->query("CALL sp_a_run ('SELECT','$input_query')");
$input_result = $input_data->result();
$input_data->next_result();
$line_input_bind_col = "";
foreach($input_result as $input){
$line_input_bind_to = $input->line_input_bind_to;
$line_input_bind_col = $input->line_input_bind_col;
$line_input_bind_col = str_replace("~","'",$line_input_bind_col);
$line_input_bind_col = str_replace("!",'"',$line_input_bind_col);
if($line_input_bind_col){
foreach($condition_check_form as $check_form){
if(strpos($line_input_bind_col,"@$check_form@") !== false) {
$value = $this->input->post_get($check_form);
if(strpos($check_form,"date") !== false) {
$value = new DateTime($value);
$value = $value->format("Y-m-d");
$value = "'$value'";
}
$line_input_bind_col = str_replace("@$check_form@",$value, $line_input_bind_col);
}
}
$dynamic_file_name= $line_input_bind_to."_".$this->logged_id.".php";
unlink("$dynamic_file_name");
$fname = $line_input_bind_to."(){";
$code = "<?php function $fname $line_input_bind_col }?>";
fopen("$dynamic_file_name", "w");
file_put_contents("$dynamic_file_name",$code);
require_once("$dynamic_file_name");
$fianl_result_array[$line_input_bind_to] = $line_input_bind_to();
unlink("$dynamic_file_name");
}
}
}
echo json_encode($fianl_result_array);
}
/* ==============================================================*/
/* ================== CONDITION OPERATION - END =================*/
/* ==============================================================*/
/* ==============================================================*/
/* ================== ROWSET OPERATION - START ==================*/
/* ==============================================================*/
// ROWSET SAVE
public function rowset_save(){
$view_id = $this->input->post('view_id');
$module_id = $this->input->post('module_id');
$row_prime_id = (int)$this->input->post('row_prime_id');
$row_label_name = $this->input->post('row_label_name');
$prime_id = (int)$this->input->post('prime_id');
$table_name = $module_id."_".$row_label_name;
$table_prime = "prime_".$table_name."_id";
$table_name = $this->db->dbprefix($table_name);
$prime_qry_key = "prime_".$module_id."_id,";
$prime_qry_value = '"'.$prime_id.'",';
$prime_upd_query = "";
$form_qry = 'select * from cw_form_setting where prime_module_id = "'.$module_id.'" and input_for = "'.$view_id.'" and field_show = "1" and trans_status = 1';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
foreach($form_result as $setting){
$field_type = $setting->field_type;
$input_view_type = (int)$setting->input_view_type;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = $setting->field_isdefault;
if((int)$field_type === 7){
$multi_name = $label_id."[]";
$value = implode(",",$this->input->post($multi_name));
}else{
$value = $this->input->post($label_id);
}
if((int)$field_type === 4){
$value = date('Y-m-d',strtotime($value));
}else
if((int)$field_type === 13){
$value = date('Y-m-d H:i:s',strtotime($value));
}
$prime_qry_key .= $label_id.",";
$prime_qry_value .= '"'.$value.'",';
$prime_upd_query .= $label_id.' = "'.$value.'",';
$exist_qry .= $label_id.' = "'.$value.'" and ';
}
$created_on = date("Y-m-d h:i:s");
$exist_count = 0;
if((int)$row_prime_id === 0){
$prime_qry_key .= "trans_created_by,trans_created_date";
$prime_qry_value .= '"'.$this->logged_id.'",'.'"'.$created_on.'"';
$prime_insert_query = "insert into $table_name ($prime_qry_key) values ($prime_qry_value)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$row_set_data = $this->get_row_set_data($view_id,$prime_id);
if($table_name === "cw_vehicle_vehicle_documents"){
$this-> update_vehicle_information($prime_id);
}
echo json_encode(array('success' => TRUE, 'message' => "Successfully added", 'insert_id' => $insert_id, 'row_set_data' => $row_set_data));
}else{
$prime_upd_query .= 'trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$prime_update_query = "UPDATE $table_name SET ". $prime_upd_query .' WHERE '. $table_prime .' = "'. $row_prime_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')");
$row_set_data = $this->get_row_set_data($view_id,$prime_id);
if($table_name === "cw_vehicle_vehicle_documents"){
$this-> update_vehicle_information($prime_id);
}
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'insert_id' => $row_prime_id,'row_set_data' => $row_set_data));
}
}
//ROW SET EDIT DATA
public function row_set_edit(){
$row_id = (int)$this->input->post('row_id');
$view_id = (int)$this->input->post('view_id');
$table_name = $this->input->post('table_name');
$table_prime_id = "prime_".$table_name."_id";
$table_name = $this->db->dbprefix($table_name);
$final_qry = "select * from $table_name " .' where '.$table_prime_id.' = "'.$row_id.'" and trans_status = "1"';
$row_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$row_result = $row_data->result();
$row_data->next_result();
$form_qry = 'select * from cw_form_setting where prime_module_id = "'.$this->control_name.'" and input_for = "'.$view_id.'" and input_view_type = "3" and trans_status = "1"';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
$rslt_info = array();
$rslt_info[$table_prime_id] = array('input_value'=>$row_result[0]->$table_prime_id,'field_type'=>1); ;
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$label_name = $form->label_name;
$field_type = $form->field_type;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;;
$input_value = $row_result[0]->$label_name;
if((int)$field_type === 4){
$input_value = date('d-m-Y',strtotime($input_value));
if($input_value === "01-01-1970"){
$input_value = date('d-m-Y');
}
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type);
}else
if((int)$field_type === 13){
$input_value = date('d-m-Y H:i:s',strtotime($input_value));
if(strpos($input_value, '01-01-1970') !== false) {
$input_value = date("d-m-Y H:i:s");
}
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type);
}else
if((int)$field_type === 9){
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type);
$pick_query = 'select '.$auto_dispaly_value.' from '.$pick_table.' where '.$auto_prime_id.' = "'.$input_value.'" and trans_status = 1';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$input_value = $pick_result[0]->$auto_dispaly_value;
$label_name = $label_name."_hidden_".$prime_form_id;
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type);
}else{
$rslt_info[$label_name] = array('input_value'=>$input_value,'field_type'=>$field_type);
}
}
echo json_encode(array('success' => TRUE, 'row_result' => $rslt_info));
}
//ROW SET REMOVE DATA
public function row_set_remove(){
$row_id = (int)$this->input->post('row_id');
$view_id = (int)$this->input->post('view_id');
$table_name = $this->input->post('table_name');
$prime_id = $this->input->post('prime_id');
$table_prime_id = "prime_".$table_name."_id";
$table_name = $this->db->dbprefix($table_name);
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$final_qry = 'UPDATE '.$table_name.' SET trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$today_date.'" , trans_status = 0 WHERE '.$table_prime_id.' = "'.$row_id.'"';
$this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$row_set_data = $this->get_row_set_data($view_id,$prime_id);
echo json_encode(array('success' => TRUE, 'msg' => "Remove Successfully",'row_set_data' => $row_set_data));
}
//PROVIDE ROWSET DATA BY ID
public function get_row_set_data($view_id,$prime_id){
$view_qry = 'select * from cw_form_view_setting where prime_form_view_id = "'.$view_id.'" and prime_view_module_id = "'.$this->control_name.'" and form_view_type = "3" and trans_status = 1';
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_qry')");
$view_result = $view_data->result();
$view_data->next_result();
$prime_form_view_id = $view_result[0]->prime_form_view_id;
$prime_view_module_id = $view_result[0]->prime_view_module_id;
$form_view_label_name = $view_result[0]->form_view_label_name;
$div_id = $form_view_label_name."_div_".$prime_form_view_id;
$table_id = $form_view_label_name."_tbl_".$prime_form_view_id;
$table_name = $this->control_name."_".$form_view_label_name;
$row_prime_id = "prime_".$table_name."_id";
$table_name = $this->db->dbprefix($table_name);
$table_prime_id = "prime_".$this->control_name."_id";
$form_qry = 'select prime_form_id,view_name,label_name,field_type,pick_list_type,pick_list,pick_table,auto_prime_id,auto_dispaly_value from cw_form_setting where prime_module_id = "'.$this->control_name.'" and input_for = "'.$prime_form_view_id.'" and input_view_type = "3" and table_show = "1" and trans_status = "1" order by abs(field_sort)';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
$table_head = array();
$thead_line = "";
$select_query = "$table_name.$row_prime_id,$table_name.$table_prime_id,";
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
if((int)$field_type === 13){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") as '.$label_name.' , ';
}else
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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else{
$select_query .= "$table_name.$label_name , ";
}
$table_head[] = $label_name;
$thead_line .= "<th>$view_name</th>";
}
$thead = "<tr>$thead_line<th>Option</th></tr>";
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$final_qry = "select $select_query from $table_name $pick_query " .' where '.$table_name.'.'.$table_prime_id.' = "'.$prime_id.'" and '.$table_name.'.trans_status = "1" order by abs('.$table_name.'.'.$row_prime_id.') desc';
$row_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$row_result = $row_data->result();
$row_data->next_result();
$tr_line = "";
foreach($row_result as $data){
$td_line = "";
foreach($table_head as $label){
$value = $data->$label;
if($value === '01-01-1970'){
$value = '';
}
$td_line .= "<td>$value</td>";
}
$row_id = $data->$row_prime_id;
$tab_name = $this->control_name."_".$form_view_label_name;
$illustration_btn = "";
if($form_view_label_name === "eligibility_information"){
$illustration_btn = "<a class='btn btn-edit btn-xs row_btn' onclick = add_illustration('$row_id','$tab_name','$prime_form_view_id');>Illustration</a>";
}
$edit_btn = "<a class='btn btn-edit btn-xs row_btn' onclick = row_set_edit('$row_id','$tab_name','$prime_form_view_id');>Edit</a>";
if($this->control_name === 'portfolio' && (int)$this->logged_role === 3){
$remove_btn = '';
}else{
$remove_btn = "<a class='btn btn-danger btn-xs row_btn' onclick = row_set_remove('$row_id','$tab_name','$prime_form_view_id','$prime_id');>Delete</a>";
}
$tr_line .= "<tr>$td_line<td>$illustration_btn $edit_btn $remove_btn</td></tr>";
}
$row_set_view = "<table id='$table_id' class='table table-bordered' style='background-color: #FFFFFF; box-shadow: none;'>
<thead>$thead</thead>
<tbody>$tr_line</tbody>
</table>";
return array('div_id' => $div_id, 'table_id' => $table_id,'row_set_view'=>$row_set_view);
}
/* ==============================================================*/
/* =================== ROWSET OPERATION - END ===================*/
/* ==============================================================*/
/* ==============================================================*/
/* ================== IMPORT OPERATION - START ==================*/
/* ==============================================================*/
//SAVE IMPORT FILE PATH
public function save_import(){
$module_id = $this->input->post('module_id');
$excel_format = $this->input->post('excel_format');
$excel_file_path = $this->input->post('excel_file_path');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$import_query = 'insert into cw_import (module_id,excel_format,excel_file_path,trans_created_by,trans_created_date) value ("'.$module_id.'","'.$excel_format.'","'.$excel_file_path.'","'.$logged_id.'","'.$today_date.'")';
$import_info = $this->db->query("CALL sp_a_run ('INSERT','$import_query')");
$import_result = $import_info->result();
$import_info->next_result();
$import_id = $import_result[0]->ins_id;
echo $this->do_excel_import($import_id);
}
//IMPORT DATA FROM FILE PATH
public function do_excel_import($import_id){
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
include($filename);
if($import_id < 0){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}
$excel_path_qry = 'select * from cw_import where import_id = "'.$import_id.'"';
$excel_path_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_path_qry')");
$excel_path_result = $excel_path_info->result();
$excel_path_info->next_result();
if(!$excel_path_result){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}else{
$excel_file_path = $excel_path_result[0]->excel_file_path;
$module_id = $excel_path_result[0]->module_id;
$excel_format = $excel_path_result[0]->excel_format;
$format_qry = 'select * from cw_util_excel_format where prime_excel_format_id = "'.$excel_format.'" and cw_util_excel_format.trans_status = 1';
$format_info = $this->db->query("CALL sp_a_run ('SELECT','$format_qry')");
$format_rslt = $format_info->result();
$format_info->next_result();
if(!$format_rslt){
return json_encode(array('success' => false, 'message' => "Please add excel format before import"));
}else{
$excel_row_start = (int)$format_rslt[0]->excel_row_start;
$exist_column_name = explode(",",$format_rslt[0]->exist_column_name);
$excel_format_qry = 'select field_type,pick_table,pick_list_type,pick_list,mandatory_field,field_isdefault,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on label_name = excel_line_column_name where excel_line_module_id = "'.$module_id.'" and prime_excel_format_id = "'.$excel_format.'" and cw_form_setting.prime_module_id = "'.$module_id.'" and cw_util_excel_format_line.trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_format_result = $excel_format->result();
$excel_format->next_result();
if(!$excel_format_result){
return json_encode(array('success' => false, 'message' => "Please map excel cell column before import"));
}else{
try{
$excel_obj = PHPExcel_IOFactory::load($excel_file_path);
}catch(Exception $e){
die('Error loading file "' . pathinfo($excel_file_path, PATHINFO_BASENAME). '": ' . $e->getMessage());
return json_encode(array('success' => false, 'message' => "Invalid file or path"));
}
$sheet = $excel_obj->getSheet(0);
$total_rows = $sheet->getHighestRow();
$highest_column = $sheet->getHighestColumn();
$worksheetTitle = $sheet->getTitle();
$status_array = array();
for($row =$excel_row_start; $row <= $total_rows; $row++) {
$prime_column_val = "";
$prime_cell_val = "";
$cf_column_val = "";
$cf_cell_val = "";
$exist_val = "";
$status_info = array();
$status_info["Excel Row"] = $row;
foreach($excel_format_result as $excel_info){
$field_isdefault = (int)$excel_info->field_isdefault;
$mandatory_field = (int)$excel_info->mandatory_field;
$field_type = (int)$excel_info->field_type;
$pick_table = $excel_info->pick_table;
$pick_list_type = (int)$excel_info->pick_list_type;
$pick_list = $excel_info->pick_list;
$excel_line_column_name = $excel_info->excel_line_column_name;
$excel_line_value = $excel_info->excel_line_value;
$get_cell_value = ucwords(trim($sheet->getCell("$excel_line_value$row")->getValue()));
// FOR DATE
if($field_type === 4){
$get_cell_value = trim(date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell("$excel_line_value$row")->getValue())));
}else
if($field_type === 13){
$get_cell_value = trim(date('Y-m-d H:i:s',PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell("$excel_line_value$row")->getValue())));
}else
// FOR PICKLIST CHECK
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 '.$pick_list_val_2.' = "'.$get_cell_value.'"';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_count = $pick_data->num_rows();
$pick_data->next_result();
if((int)$pick_count === 0){
$ins_query = 'insert into '.$pick_table.'('.$pick_list_val_2.') VALUES ("'.$get_cell_value.'")';
$ins_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_query')");
$ins_result = $ins_info->result();
$ins_info->next_result();
$get_cell_value = $ins_result[0]->ins_id;
}else
if((int)$pick_count === 1){
$pick_id = (int)$pick_result[0]->$pick_list_val_1;
$pick_status = (int)$pick_result[0]->trans_status;
if($pick_status === 0){
$upd_query = 'update '.$pick_table.' set trans_status = 1 where '.$pick_list_val_1.' = '.$pick_id;
$this->db->query("CALL sp_a_run ('RUN','$upd_query')");
}
$get_cell_value = $pick_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 * from '.$pick_table.' where '.$pick_list_val_2.' = "'.$get_cell_value.'"';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_count = $pick_data->num_rows();
$pick_data->next_result();
if((int)$pick_count === 0){
$ins_query = 'insert into '.$pick_table.'('.$pick_list_val_2.') VALUES ("'.$get_cell_value.'")';
$ins_info = $this->db->query("CALL sp_a_run ('INSERT','$ins_query')");
$ins_result = $ins_info->result();
$ins_info->next_result();
$get_cell_value = $ins_result[0]->ins_id;
}else
if((int)$pick_count === 1){
$pick_id = (int)$pick_result[0]->$pick_list_val_1;
$pick_status = (int)$pick_result[0]->$pick_list_val_3;
if($pick_status === 0){
$upd_query = 'update '.$pick_table.' set '.$pick_list_val_3.' = 1 where '.$pick_list_val_1.' = '.$pick_id;
$this->db->query("CALL sp_a_run ('RUN','$upd_query')");
}
$get_cell_value = $pick_id;
}
}
}
if($field_isdefault === 1){
//$status_info[$excel_line_column_name] = $get_cell_value;
$prime_column_val .= $excel_line_column_name.",";
$prime_cell_val .= '"'.$get_cell_value.'",';
if(empty($exist_column_name)) {
if($mandatory_field === 1){
$exist_val .= $this->prime_table .'.'.$excel_line_column_name.' = "'.$get_cell_value.'" and ';
}
}else{
if(in_array($excel_line_column_name,$exist_column_name)){
$exist_val .= $this->prime_table .'.'.$excel_line_column_name.' = "'.$get_cell_value.'" and ';
}
}
}else
if($field_isdefault === 2){
//$status_info[$excel_line_column_name] = $get_cell_value;
$cf_column_val .= $excel_line_column_name.",";
$cf_cell_val .= '"'.$get_cell_value.'",';
if(empty($exist_column_name)) {
if($mandatory_field === 1){
$exist_val .= $this->cf_table .'.'.$excel_line_column_name.' = "'.$get_cell_value.'" and ';
}
}else{
if(in_array($excel_line_column_name,$exist_column_name)){
$exist_val .= $this->cf_table .'.'.$excel_line_column_name.' = "'.$get_cell_value.'" and ';
}
}
}
}
if($prime_column_val){
$prime_id = "prime_".$module_id."_id";
$exist_val = rtrim($exist_val," and ");
$exist_query = "select count(*) exist_count,$this->prime_table.trans_status,$this->prime_table.$prime_id from $this->prime_table inner join $this->cf_table on $this->cf_table.$this->prime_id = $this->prime_table.$this->prime_id where $exist_val";
$exist_info = $this->db->query("CALL sp_a_run ('RUN','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
$exist_count = $exist_result[0]->exist_count;
$created_on = date("Y-m-d h:i:s");
if((int)$exist_count === 0){
$prime_column_val .= "trans_created_by,trans_created_date";
$prime_cell_val .= '"'.$this->logged_id.'",'.'"'.$created_on.'"';
$prime_column_val = rtrim($prime_column_val,",");
$prime_cell_val = rtrim($prime_cell_val,",");
$prime_query = "insert into $this->prime_table ($prime_column_val) VALUES ($prime_cell_val)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$cf_column_val .= "$prime_id,";
$cf_cell_val .= "$insert_id,";
$cf_column_val .= "trans_created_by,trans_created_date";
$cf_cell_val .= '"'.$this->logged_id.'",'.'"'.$created_on.'"';
$cf_column_val = rtrim($cf_column_val,",");
$cf_cell_val = rtrim($cf_cell_val,",");
$cf_query = "insert into $this->cf_table ($cf_column_val) VALUES ($cf_cell_val)";
$this->db->query("CALL sp_a_run ('RUN','$cf_query')");
$status_info['Status'] = "Inserted to DB";
}else
if((int)$exist_count === 1){
$trans_status = (int)$exist_result[0]->trans_status;
$upd_prime_id = (int)$exist_result[0]->$prime_id;
if($trans_status === 0){
$upd_query = 'UPDATE '.$this->prime_table.' SET trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$created_on.'" , trans_status = 1 WHERE '.$prime_id.' = "'.$upd_prime_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_query')");
$status_info['status'] = "Changed to active";
}else{
$status_info['status'] = "Already Exist in DB";
}
}else{
$status_info['status'] = "Already Exist in DB";
}
$status_array[] = $status_info;
}
}
$table_info = $this->get_excel_import_ui($status_array);
return json_encode(array('success'=>true,'message'=>"Successfully file imported",'table_info'=>$table_info));
}
}
}
}
public function get_excel_import_ui($status_array){
$table_info = "";
$th_line = "";
$tr_line = "";
$count = 0;
foreach($status_array as $status){
$count++;
$status_array_count = count($status);
$status_count = 0;
foreach($status as $key => $value){
$status_count++;
if((int)$count === 1){
$th_line .= "<th style='text-align:center !important;'>$key</th>";
}
$td_line .= "<td>$value</td>";
if((int)$status_count === (int)$status_array_count){
$color = "style='color:#15da15 !important;'";
if($value === "Already Exist in DB"){
$color = "style='color:#ff0303 !important;'";
}
$tr_line .= "<tr $color>$td_line</tr>";
$td_line = "";
}
}
}
if($th_line !== ""){
$table_info = "<table class='table table-bordered' style='text-align:center;'>
<thead>
<tr>
$th_line
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
}
return $table_info;
}
/* ==============================================================*/
/* =================== IMPORT OPERATION - END ===================*/
/* ==============================================================*/
/* ==============================================================
COMMON OPERATION OTHER MODULES- END
==============================================================*/
//BOOKING EXIST
public function booking_exists($prime_booking_id){
$this->db->from('booking');
$this->db->where('prime_booking_id', $prime_booking_id);
return ($this->db->get()->num_rows() == 1);
}
//OPERATION EXIST
public function operation_exists($prime_booking_id){
$this->db->from('operation');
$this->db->where('prime_booking_id', $prime_booking_id);
return ($this->db->get()->num_rows() == 1);
}
//ACCOUNTS EXIST
public function accounts_exists($prime_booking_id){
$this->db->from('accounts');
$this->db->where('prime_booking_id', $prime_booking_id);
return ($this->db->get()->num_rows() == 1);
}
//MOVE TO OPERATION TABLE
function move_to_operation($prime_booking_id){
if($this->booking_exists($prime_booking_id, TRUE)){
$this->db->select('prime_booking_id,tour_no,from_date,to_date,origin,destination,avg_km,total_tolls,toll,parking,driver_batta,state_tax,route_information,booking_remarks,booking_sts,cancel_reason,vehicle_count,trip_amount,balance_amount');
$this->db->from('booking');
$this->db->where('booking.trans_status', 1);
$this->db->where('booking.prime_booking_id', (int)$prime_booking_id);
$booking_rslt = $this->db->get();
$booking_count = $booking_rslt->num_rows();
$booking_info = $booking_rslt->row();
$booking_sts = (int)$booking_info->booking_sts;
if($booking_sts === 2){
$operation_data = json_decode(json_encode($booking_info), true);
$operation_data['operation_sts'] = 2;
if($this->operation_exists($prime_booking_id)){
$this->db->from('operation');
$this->db->where('operation.trans_status', 1);
$this->db->where('operation.prime_booking_id', (int)$prime_booking_id);
$operation_rslt = $this->db->get();
$operation_count = $operation_rslt->num_rows();
if($operation_count === 1){
$operation_info = $operation_rslt->row();
$operation_data['trans_updated_by'] = $this->session->userdata('logged_id');
$operation_data['trans_updated_date'] = date("Y-m-d H:i:s");
$prime_operation_id = $operation_info->prime_operation_id;
$this->db->where('prime_operation_id', $prime_operation_id);
$this->db->update('operation', $operation_data);
$this->move_to_operation_line($prime_booking_id,$prime_operation_id);
$this->move_to_accounts($prime_booking_id);
}
}else{
$operation_data['trans_created_by'] = $this->session->userdata('logged_id');
$operation_data['trans_created_date'] = date("Y-m-d H:i:s");
if($this->db->insert('operation', $operation_data)){
$prime_operation_id = $this->db->insert_id();
$this->move_to_operation_line($prime_booking_id,$prime_operation_id);
$this->move_to_accounts($prime_booking_id);
}
}
}
}
}
//MOVE OPEARTION LINE
function move_to_operation_line($prime_booking_id,$prime_operation_id){
if(($prime_booking_id > 0) && ($prime_operation_id > 0)){
$this->db->select('prime_booking_id,vehicle_type,preferred_vehicle');
$this->db->from('booking_line');
$this->db->where('booking_line.trans_status', 1);
$this->db->where('booking_line.prime_booking_id', (int)$prime_booking_id);
$this->db->group_by('booking_line.preferred_vehicle');
$booking_line_rslt = $this->db->get();
$booking_line_info = $booking_line_rslt->result();
$operation_line_data = array();
foreach($booking_line_info as $booking_line){
$prime_booking_id = $booking_line->prime_booking_id;
$preferred_vehicle = $booking_line->preferred_vehicle;
$operation_line_data = json_decode(json_encode($booking_line), true);
$operation_line_data['prime_operation_id'] = $prime_operation_id;
$this->db->from('operation_line');
$this->db->where('operation_line.trans_status', 1);
$this->db->where('operation_line.prime_booking_id', (int)$prime_booking_id);
$this->db->where('operation_line.preferred_vehicle', (int)$preferred_vehicle);
$operation_line_rslt = $this->db->get();
$operation_line_count = $operation_line_rslt->num_rows();
if($operation_line_count === 0){
$operation_line_data['trans_created_by'] = $this->session->userdata('logged_id');
$operation_line_data['trans_created_date'] = date("Y-m-d H:i:s");
$this->db->insert('operation_line', $operation_line_data);
}else
if($operation_line_count === 1){
$operation_line_info = $operation_line_rslt->row();
$operation_line_data['trans_updated_by'] = $this->session->userdata('logged_id');
$operation_line_data['trans_updated_date'] = date("Y-m-d H:i:s");
$prime_operation_line_id = $operation_line_info->prime_operation_line_id;
$this->db->where('prime_operation_line_id', $prime_operation_line_id);
$this->db->update('operation_line', $operation_line_data);
}
}
$this->db->from('operation_line');
$this->db->where('operation_line.prime_booking_id', (int)$prime_booking_id);
$opeartion_info = $this->db->get()->result();
foreach($opeartion_info as $opeartion){
$prime_operation_line_id = $opeartion->prime_operation_line_id;
$preferred_vehicle = $opeartion->preferred_vehicle;
$this->db->from('booking_line');
$this->db->where('booking_line.trans_status', 1);
$this->db->where('booking_line.prime_booking_id', (int)$prime_booking_id);
$this->db->where('booking_line.preferred_vehicle', (int)$preferred_vehicle);
$validate_count = $this->db->get()->num_rows();
if($validate_count === 0){
$operation_line_data = array(
'trans_status' => 0,
'trans_deleted_by' => $this->session->userdata('logged_id'),
'trans_deleted_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_operation_line_id', $prime_operation_line_id);
$this->db->update('operation_line', $operation_line_data);
}
}
}
}
public function call_rollback_check($prime_operation_id){
if((int)$prime_operation_id > 0){
$this->db->from('operation_line');
$this->db->where('trans_status', 1);
$this->db->where('prime_operation_id', (int)$prime_operation_id);
$operation_line_rslt = $this->db->get();
$operation_line_info = $operation_line_rslt->result();
foreach($operation_line_info as $opeartion){
$prime_operation_line_id = $opeartion->prime_operation_line_id;
$prime_operation_id = $opeartion->prime_operation_id;
$assign_vehicle = $opeartion->assign_vehicle;
$this->db->select('IFNULL(sum(suspense_amount),0) as tot_suspense_amount');
$this->db->from('operation_suspense');
$this->db->where('trans_status', 1);
$this->db->where('suspense_vehicle_id', (int)$assign_vehicle);
$this->db->where('suspense_operation_id', (int)$prime_operation_id);
$this->db->where('suspense_type',1);
$total_suspense_rslt = $this->db->get()->row();
$tot_suspense_amount = $total_suspense_rslt->tot_suspense_amount;
$this->db->select('IFNULL(sum(suspense_amount),0) as expence_suspense_amount');
$this->db->from('operation_suspense');
$this->db->where('trans_status', 1);
$this->db->where('suspense_vehicle_id', (int)$assign_vehicle);
$this->db->where('suspense_operation_id', (int)$prime_operation_id);
$this->db->where('suspense_type !=',1);
$expence_suspense_rslt = $this->db->get()->row();
$expence_suspense_amount = ltrim($expence_suspense_rslt->expence_suspense_amount,"-");
$suspense_balance_amount = (int)$tot_suspense_amount - (int)$expence_suspense_amount;
$this->db->select('IFNULL(total_km,0) as total_km,IFNULL(extra_km,0) as extra_km,IFNULL(per_km_amount,0) as per_km_amount,IFNULL(extra_amount,0) as extra_amount');
$this->db->from('operation_km');
$this->db->where('trans_status', 1);
$this->db->where('km_vehicle_id', (int)$assign_vehicle);
$this->db->where('km_operation_id', (int)$prime_operation_id);
$km_rslt = $this->db->get()->row();
$this->db->select('IFNULL(sum(total_km),0) as diesel_total_km,IFNULL(sum(diesel_quantity),0) as diesel_quantity,IFNULL(sum(diesel_amount),0) as diesel_amount');
$this->db->from('operation_diesel');
$this->db->where('trans_status', 1);
$this->db->where('diesel_vehicle_id', (int)$assign_vehicle);
$this->db->where('diesel_operation_id', (int)$prime_operation_id);
$diesel_rslt = $this->db->get()->row();
$operation_line_data = array(
'tot_suspense_amount' => $tot_suspense_amount,
'expence_suspense_amount' => $expence_suspense_amount,
'suspense_balance_amount' => $suspense_balance_amount,
'total_km' => $km_rslt->total_km,
'extra_km' => $km_rslt->extra_km,
'per_km_amount' => $km_rslt->per_km_amount,
'extra_amount' => $km_rslt->extra_amount,
'diesel_total_km' => $diesel_rslt->diesel_total_km,
'diesel_quantity' => $diesel_rslt->diesel_quantity,
'diesel_amount' => $diesel_rslt->diesel_amount,
'trans_updated_by' =>$this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_operation_line_id', $prime_operation_line_id);
$this->db->update('operation_line', $operation_line_data);
}
$this->db->from('operation');
$this->db->where('trans_status', 1);
$this->db->where('prime_operation_id', (int)$prime_operation_id);
$operation_line_rslt = $this->db->get();
$operation_count = $operation_line_rslt->num_rows();
if((int)$operation_count === 1){
$operation_info = $operation_line_rslt->row();
$prime_booking_id = $operation_info->prime_booking_id;
$this->db->select('IFNULL(sum(tot_suspense_amount),0) as all_tot_suspense,IFNULL(sum(suspense_balance_amount),0) as all_tot_suspense_bal,IFNULL(sum(extra_km),0) as all_extra_km,IFNULL(sum(extra_amount),0) as all_extra_amount,IFNULL(sum(diesel_quantity),0) as all_diesel_quantity,IFNULL(sum(diesel_amount),0) as all_diesel_amount');
$this->db->from('operation_line');
$this->db->where('trans_status', 1);
$this->db->where('prime_operation_id', (int)$prime_operation_id);
$operation_rslt = $this->db->get()->row();
$operation_data = array(
'all_tot_suspense' => $operation_rslt->all_tot_suspense,
'all_tot_suspense_bal' => $operation_rslt->all_tot_suspense_bal,
'all_extra_km' => $operation_rslt->all_extra_km,
'all_extra_amount' => $operation_rslt->all_extra_amount,
'all_diesel_quantity' => $operation_rslt->all_diesel_quantity,
'all_diesel_amount' => $operation_rslt->all_diesel_amount,
'trans_updated_by' =>$this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_operation_id', $prime_operation_id);
$this->db->update('operation', $operation_data);
$extra_km_amount = (int)$operation_rslt->all_extra_amount;
if($extra_km_amount > 0){
$this->db->from('balance');
$this->db->where('trans_status', 1);
$this->db->where('prime_balance_booking_id', (int)$prime_booking_id);
$balance_info = $this->db->get();
$balance_count = (int)$balance_info->num_rows();
$balance_rslt = $balance_info->result();
$split_extra_amount = $extra_km_amount / $balance_count;
foreach($balance_rslt as $balance){
$prime_balance_id = $balance->prime_balance_id;
$balance_data = array(
'extra_km_amount' => $split_extra_amount,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_balance_id', $prime_balance_id);
$this->db->update('balance', $balance_data);
}
$this->update_balance_head($prime_booking_id);
$this->update_booking_head($prime_booking_id);
}
$this->move_to_accounts($prime_booking_id);
}
}
}
//MOVE TO ACCOUNTS
function move_to_accounts($prime_booking_id){
if($this->booking_exists($prime_booking_id, TRUE)){
if($this->operation_exists($prime_booking_id)){
$this->db->select('prime_booking_id,prime_operation_id,tour_no,from_date,to_date,origin,destination,avg_km,total_tolls,toll,parking,driver_batta,state_tax,route_information,booking_remarks,booking_sts,operation_sts,cancel_reason,vehicle_count,trip_amount,all_tot_suspense,all_tot_suspense_bal,all_extra_km,all_extra_amount,all_diesel_quantity,all_diesel_amount,balance_amount');
$this->db->from('operation');
$this->db->where('operation.trans_status', 1);
$this->db->where('operation.prime_booking_id', (int)$prime_booking_id);
$operation_rslt = $this->db->get();
$operation_count = $operation_rslt->num_rows();
$operation_info = $operation_rslt->row();
$accounts_data = json_decode(json_encode($operation_info), true);
$accounts_data['accounts_sts'] = 2;
if($this->accounts_exists($prime_booking_id)){
$this->db->from('accounts');
$this->db->where('accounts.trans_status', 1);
$this->db->where('accounts.prime_booking_id', (int)$prime_booking_id);
$accounts_rslt = $this->db->get()->row();
$accounts_data['trans_updated_by'] = $this->session->userdata('logged_id');
$accounts_data['trans_updated_date'] = date("Y-m-d H:i:s");
$prime_accounts_id = $accounts_rslt->prime_accounts_id;
$this->db->where('prime_accounts_id', $prime_accounts_id);
$this->db->update('accounts', $accounts_data);
$this->move_to_accounts_line($prime_booking_id,$prime_accounts_id);
}else{
$accounts_data['trans_created_by'] = $this->session->userdata('logged_id');
$accounts_data['trans_created_date'] = date("Y-m-d H:i:s");
if($this->db->insert('accounts', $accounts_data)){
$prime_accounts_id = $this->db->insert_id();
$this->move_to_accounts_line($prime_booking_id,$prime_accounts_id);
}
}
}
}
}
// MOVE TO ACCOUNTS LINE
function move_to_accounts_line($prime_booking_id,$prime_accounts_id){
if(($prime_booking_id > 0) && ($prime_accounts_id > 0)){
$upd_line_data['trans_status'] = 0;
$upd_line_data['trans_updated_by'] = $this->session->userdata('logged_id');
$upd_line_data['trans_updated_date'] = date("Y-m-d H:i:s");
$this->db->where('prime_accounts_id', $prime_accounts_id);
$this->db->update('accounts_line', $upd_line_data);
$this->db->select('prime_operation_line_id,prime_booking_id,prime_operation_id,vehicle_type,preferred_vehicle,assign_vehicle,review_status,primary_driver,primary_driver_name,primary_driver_mobile,secondary_driver,secondary_driver_name,secondary_driver_mobile,cleaner_from,cleaner_name,cleaner_mobile,rout_information,special_instructions,tot_suspense_amount,expence_suspense_amount,suspense_balance_amount,total_km,extra_km,per_km_amount,extra_amount,diesel_total_km,diesel_quantity,diesel_amount');
$this->db->from('operation_line');
$this->db->where('operation_line.trans_status', 1);
$this->db->where('operation_line.prime_booking_id', (int)$prime_booking_id);
$operation_line_rslt = $this->db->get();
$operation_line_info = $operation_line_rslt->result();
foreach($operation_line_info as $opeartion){
$prime_operation_line_id = $opeartion->prime_operation_line_id;
$prime_operation_id = $opeartion->prime_operation_id;
$assign_vehicle = $opeartion->assign_vehicle;
$accounts_line_data = json_decode(json_encode($opeartion), true);
$accounts_line_data['prime_accounts_id'] = $prime_accounts_id;
$this->db->from('accounts_line');
$this->db->where('accounts_line.prime_operation_line_id', (int)$prime_operation_line_id);
$accounts_line_rslt = $this->db->get();
$accounts_line_count = $accounts_line_rslt->num_rows();
if((int)$accounts_line_count === 0){
$accounts_line_data['trans_created_by'] = $this->session->userdata('logged_id');
$accounts_line_data['trans_created_date'] = date("Y-m-d H:i:s");
$this->db->insert('accounts_line', $accounts_line_data);
}else
if((int)$accounts_line_count === 1){
$accounts_line_info = $accounts_line_rslt->row();
$prime_accounts_line_id = $accounts_line_info->prime_accounts_line_id;
$accounts_line_data['trans_status'] = 1;
$accounts_line_data['trans_updated_by'] = $this->session->userdata('logged_id');
$accounts_line_data['trans_updated_date'] = date("Y-m-d H:i:s");
$this->db->where('prime_accounts_line_id', $prime_accounts_line_id);
$this->db->update('accounts_line', $accounts_line_data);
}
}
}
}
//UPDATE BALANCE LOG
public function update_balance_head($prime_booking_id){
$this->db->select('prime_booking_line_id,prime_booking_id,booking_line.prime_customer_id,IFNULL(sum(vech_total_amount), 0) as trip_amount');
$this->db->from('booking_line');
$this->db->join('customer', 'booking_line.prime_customer_id = customer.prime_customer_id','inner');
$this->db->where('booking_line.trans_status', 1);
$this->db->where('booking_line.prime_booking_id', $prime_booking_id);
$this->db->group_by('booking_line.prime_customer_id');
$balance_list_info = $this->db->get()->result();
foreach($balance_list_info as $balance_line){
$prime_booking_line_id = $balance_line->prime_booking_line_id;
$prime_booking_id = $balance_line->prime_booking_id;
$prime_customer_id = $balance_line->prime_customer_id;
$trip_amount = $balance_line->trip_amount;
$this->db->from('balance');
$this->db->where('customer_id', $prime_customer_id);
$this->db->where('prime_balance_booking_id', $prime_booking_id);
$this->db->where('trans_status', 1);
$balance_exist_info = $this->db->get();
$balance_count = $balance_exist_info->num_rows();
if($balance_count === 0){
$balance_data = array(
'customer_id' => $prime_customer_id,
'prime_balance_booking_id' => $prime_booking_id,
'booked_total' => $trip_amount,
'extra_km_amount' => 0,
'paid_total' => 0,
'deductions' => 0,
'balance_amount' => $trip_amount,
'trans_created_by' => $this->session->userdata('logged_id'),
'trans_created_date' => date("Y-m-d H:i:s"),
);
$this->db->insert('balance', $balance_data);
}else
if($balance_count === 1){
$balance_rslt = $balance_exist_info->row();
$prime_balance_id = $balance_rslt->prime_balance_id;
$extra_km_amount = (int)$balance_rslt->extra_km_amount;
$this->db->select('IFNULL(sum(paid_amount), 0) as paid_amount');
$this->db->from('balance_line');
$this->db->where('prime_balance_booking_id', $prime_booking_id);
$this->db->where('prime_balance_id', $prime_balance_id);
$this->db->where('customer_id', $prime_customer_id);
$this->db->where('trans_status', 1);
$balance_line_exist_info = $this->db->get();
$balance_line_rslt = $balance_line_exist_info->row();
$paid_amount = $balance_line_rslt->paid_amount;
if($extra_km_amount > 0){
$balance_amount = ($trip_amount+ $extra_km_amount) - $paid_amount;
}else{
$balance_amount = $trip_amount - $paid_amount;
}
$this->db->select('IFNULL(sum(deductions_amount), 0) as deductions_amount');
$this->db->from('deductions');
$this->db->where('prime_deductions_booking_id', $prime_booking_id);
$this->db->where('deductions_customer_id', $prime_customer_id);
$this->db->where('trans_status', 1);
$deductions_info = $this->db->get();
$deductions_rlst = $deductions_info->row();
$deductions_amount = $deductions_rlst->deductions_amount;
$balance_amount = $balance_amount - $deductions_amount;
$balance_data = array(
'customer_id' => $prime_customer_id,
'prime_balance_booking_id' => $prime_booking_id,
'booked_total' => $trip_amount,
'paid_total' => $paid_amount,
'deductions' => $deductions_amount,
'balance_amount' => $balance_amount,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_balance_id', $prime_balance_id);
$this->db->update('balance', $balance_data);
}
}
}
//UPDATE BOOKING HEAD FOR VEHICLE COUNT AND TOTAL AMOUNT
public function update_booking_head($prime_booking_id){
if((int)$prime_booking_id > 0){
$this->db->select('IFNULL(sum(vech_total_amount), 0) as trip_amount,COUNT(DISTINCT(preferred_vehicle)) as vehicle_count');
$this->db->from('booking_line');
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->where('booking_line.trans_status', 1);
$booking_line_info = $this->db->get()->row();
$this->db->select('IFNULL(sum(balance_amount), 0) as balance_amount');
$this->db->from('balance');
$this->db->where('prime_balance_booking_id', $prime_booking_id);
$this->db->where('balance.trans_status', 1);
$balance_line_info = $this->db->get()->row();
$booking_data = array(
'vehicle_count' => $booking_line_info->vehicle_count,
'trip_amount' => $booking_line_info->trip_amount,
'balance_amount' => $balance_line_info->balance_amount,
'trans_updated_by' =>$this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('booking', $booking_data);
if($this->operation_exists($prime_booking_id)){
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('operation', $booking_data);
}
if($this->accounts_exists($prime_booking_id)){
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('accounts', $booking_data);
}
}
}
//UPDATE STATUS TO BOOKING, OPERATION AND ACCOUNTS
public function update_status($status,$from,$prime_booking_id){
if(($from === "BOOKING") && ((int)$status === 1)){
//BOOKING CANCELLED
$booking_data = array(
'booking_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('booking', $booking_data);
$operation_data = array(
'booking_sts' => $status,
'operation_sts' => 3,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('operation', $operation_data);
$accounts_data = array(
'booking_sts' => $status,
'operation_sts' => 3,
'accounts_sts' => 3,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('accounts', $accounts_data);
$upd_data = array(
'trans_deleted_by' => $this->session->userdata('logged_id'),
'trans_deleted_date' => date("Y-m-d H:i:s"),
'trans_status' => 0,
);
$this->db->where('booking_id', $prime_booking_id);
$this->db->update('availability', $upd_data);
}else
if(($from === "BOOKING") && ((int)$status === 3)){
//BOOKING CANCELLED
$booking_data = array(
'booking_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('booking', $booking_data);
$operation_data = array(
'booking_sts' => $status,
'operation_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('operation', $operation_data);
$accounts_data = array(
'booking_sts' => $status,
'operation_sts' => $status,
'accounts_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('accounts', $accounts_data);
$upd_data = array(
'trans_deleted_by' => $this->session->userdata('logged_id'),
'trans_deleted_date' => date("Y-m-d H:i:s"),
'trans_status' => 0,
);
$this->db->where('booking_id', $prime_booking_id);
$this->db->update('availability', $upd_data);
}else
if(($from === "OPERATION") && ((int)$status === 4)){
//OPERATION ONPROGRESS
$booking_data = array(
'booking_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('booking', $booking_data);
$operation_data = array(
'booking_sts' => $status,
'operation_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('operation', $operation_data);
$accounts_data = array(
'booking_sts' => $status,
'operation_sts' => $status,
'accounts_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('accounts', $accounts_data);
}else
if(($from === "OPERATION") && ((int)$status === 5)){
//OPERATION COMPLETED
$operation_data = array(
'booking_sts' => 4,
'operation_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('operation', $operation_data);
$accounts_data = array(
'booking_sts' => 4,
'operation_sts' => $status,
'accounts_sts' => 4,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('accounts', $accounts_data);
}else
if(($from === "ACCOUNTS") && ((int)$status === 5)){
//ACCOUNTS COMPLETED
$booking_data = array(
'booking_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('booking', $booking_data);
$operation_data = array(
'booking_sts' => $status,
'operation_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('operation', $operation_data);
$accounts_data = array(
'booking_sts' => $status,
'operation_sts' => $status,
'accounts_sts' => $status,
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('accounts', $accounts_data);
}
}
//USED TO CANCELLED ALL TABLE AND REMOVE FORM AVILABITY
public function remove_all($prime_booking_id){
$this->db->from('booking');
$this->db->where('prime_booking_id',$prime_booking_id);
$this->db->where('trans_status',1);
$booking_data = $this->db->get();
$booking_count = (int)$booking_data->num_rows();
$upd_data = array(
'trans_deleted_by' => $this->session->userdata('logged_id'),
'trans_deleted_date' => date("Y-m-d H:i:s"),
'trans_status' => 0,
);
if($booking_count === 1){
$booking_rslt = $booking_data->row();
$prime_booking_id = (int)$booking_rslt->prime_booking_id;
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('booking', $upd_data);
$this->db->where('prime_booking_id', $prime_booking_id);
$this->db->update('booking_line', $upd_data);
$this->db->from('operation');
$this->db->where('prime_booking_id',$prime_booking_id);
$this->db->where('trans_status',1);
$operation_data = $this->db->get();
$operation_count = (int)$operation_data->num_rows();
if($operation_count === 1){
$operation_rslt = $operation_data->row();
$prime_operation_id = (int)$operation_rslt->prime_operation_id;
$this->db->where('prime_operation_id', $prime_operation_id);
$this->db->update('operation', $upd_data);
$this->db->where('prime_operation_id', $prime_operation_id);
$this->db->update('operation_line', $upd_data);
$this->db->where('diesel_operation_id', $prime_operation_id);
$this->db->update('operation_diesel', $upd_data);
$this->db->where('km_operation_id', $prime_operation_id);
$this->db->update('operation_km', $upd_data);
$this->db->where('suspense_operation_id', $prime_operation_id);
$this->db->update('operation_suspense', $upd_data);
$this->db->from('accounts');
$this->db->where('prime_booking_id',$prime_booking_id);
$this->db->where('trans_status',1);
$accounts_data = $this->db->get();
$accounts_count = (int)$accounts_data->num_rows();
if($accounts_count === 1){
$accounts_rslt = $accounts_data->row();
$prime_accounts_id = (int)$accounts_rslt->prime_accounts_id;
$this->db->where('prime_accounts_id', $prime_accounts_id);
$this->db->update('accounts', $upd_data);
$this->db->where('prime_accounts_id', $prime_accounts_id);
$this->db->update('accounts_line', $upd_data);
$this->db->where('prime_accounts_id', $prime_accounts_id);
$this->db->update('accounts_invoice', $upd_data);
$this->db->where('prime_balance_booking_id', $prime_booking_id);
$this->db->update('balance', $upd_data);
$this->db->where('prime_balance_booking_id', $prime_booking_id);
$this->db->update('balance_line', $upd_data);
$this->db->where('booking_id', $prime_booking_id);
$this->db->update('availability', $upd_data);
}
}
}
}
// UPDATE VEHICLE INFORMATION ON DOCUMENT SAVE
public function update_vehicle_information($prime_vehicle_id){
$vehicle_data = array(
'trans_updated_by' => $this->session->userdata('logged_id'),
'trans_updated_date' => date("Y-m-d H:i:s"),
);
$fc_query = $this->db->query("SELECT * FROM cw_vehicle_vehicle_documents where document_type = '1' and prime_vehicle_id = '$prime_vehicle_id' ORDER BY `cw_vehicle_vehicle_documents`.`to_date` DESC limit 1");
$fc_info = $fc_query->row();
if($fc_info){
$fc_to_date = $fc_info->to_date;
$vehicle_data['fc_date'] = $fc_to_date;
}
$tp_query = $this->db->query("SELECT * FROM cw_vehicle_vehicle_documents where document_type = '2' and prime_vehicle_id = '$prime_vehicle_id' ORDER BY `cw_vehicle_vehicle_documents`.`to_date` DESC limit 1");
$tp_info = $tp_query->row();
if($tp_info){
$tp_to_date = $tp_info->to_date;
$vehicle_data['tp_date'] = $tp_to_date;
}
$tax_query = $this->db->query("SELECT * FROM cw_vehicle_vehicle_documents where document_type = '3' and prime_vehicle_id = '$prime_vehicle_id' ORDER BY `cw_vehicle_vehicle_documents`.`to_date` DESC limit 1");
$tax_info = $tax_query->row();
if($tax_info){
$tax_to_date = $tax_info->to_date;
$vehicle_data['tax_date'] = $tax_to_date;
}
$permit_query = $this->db->query("SELECT * FROM cw_vehicle_vehicle_documents where document_type = '4' and prime_vehicle_id = '$prime_vehicle_id' ORDER BY `cw_vehicle_vehicle_documents`.`to_date` DESC limit 1");
$permit_info = $permit_query->row();
if($permit_info){
$permit_to_date = $permit_info->to_date;
$vehicle_data['permit_valid_date'] = $permit_to_date;
}
$pollution_query = $this->db->query("SELECT * FROM cw_vehicle_vehicle_documents where document_type = '5' and prime_vehicle_id = '$prime_vehicle_id' ORDER BY `cw_vehicle_vehicle_documents`.`to_date` DESC limit 1");
$pollution_info = $pollution_query->row();
if($pollution_info){
$pollution_to_date = $pollution_info->to_date;
$vehicle_data['pollution_valid_date'] = $pollution_to_date;
}
$insurance_query = $this->db->query("SELECT * FROM cw_vehicle_vehicle_documents where document_type = '6' and prime_vehicle_id = '$prime_vehicle_id' ORDER BY `cw_vehicle_vehicle_documents`.`to_date` DESC limit 1");
$insurance_info = $insurance_query->row();
if($insurance_info){
$insurance_to_date = $insurance_info->to_date;
$vehicle_data['insurance_valid_date'] = $insurance_to_date;
}
$this->db->where('prime_vehicle_id', $prime_vehicle_id);
$this->db->update('vehicle', $vehicle_data);
}
/* ==============================================================
COMMON OPERATION OTHER MODULES- END
==============================================================*/
}
?>