File: /home/cafsindia/allyindian_com/sbltt/application/models/Booking_model.php
<?php
class Booking_model extends CI_Model{
public function get_customer_suggestions($search){
$this->db->from('customers');
$this->db->where('deleted', 0);
$this->db->group_start();
$this->db->like('customer_name', $search);
$this->db->or_like('phone_number',$search);
$this->db->or_like('cust_email',$search);
$this->db->group_end();
$this->db->order_by('customer_name', 'asc');
$suggestions = array();
foreach($this->db->get()->result() as $row){
$suggestions[] = array('cust_id' => $row->cust_id,'customer_name' => $row->customer_name,'phone_number' => $row->phone_number,'cust_email' => $row->cust_email,'cust_address' => $row->cust_address);
}
return $suggestions;
}
public function get_customer_info($search){
$this->db->from('customers');
$this->db->where('cust_id', $search);
$suggestions = array();
foreach($this->db->get()->result() as $row){
$suggestions[] = array('cust_id' => $row->cust_id,'customer_name' => $row->customer_name,'phone_number' => $row->phone_number,'alt_number' => $row->alt_number,'land_line' => $row->land_line,'cust_email' => $row->cust_email,'cust_address' => $row->cust_address,'city' => $row->city,'state' => $row->state,'dob' => date('d-m-Y',strtotime($row->dob)),'referral_type' => $row->referral_type,'allow_contract' => $row->allow_contract,'cust_sts' => $row->cust_sts,'includ_gst' => $row->includ_gst,'cust_zone' => $row->cust_zone,'csut_gst' => $row->csut_gst,'csut_pan' => $row->csut_pan);
}
return $suggestions;
}
public function get_state() {
$this->db->from('state');
$this->db->order_by('state_name', 'asc');
return $this->db->get();
}
public function get_city() {
$this->db->from('city');
$this->db->where('status ',1);
$this->db->order_by('city_name', 'asc');
return $this->db->get();
}
public function get_catering_menu() {
$this->db->from('catering_menu');
$this->db->order_by('name', 'asc');
return $this->db->get();
}
public function get_vehicle_type() {
$this->db->select('veh_type_id,veh_type,veh_category.veh_category');
$this->db->from('veh_type');
$this->db->join('veh_category', 'veh_category.veh_cat_id = veh_type.veh_category','left');
$this->db->where('veh_type.status', 1);
$this->db->order_by('veh_type', 'asc');
return $this->db->get();
}
public function get_referral() {
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->join('role', 'employees.role = role.role_id');
return $this->db->get();
}
public function get_booking_type() {
$this->db->from('booking_type');
$this->db->order_by('booking_type_name', 'asc');
return $this->db->get();
}
public function get_trip_type() {
$this->db->from('trip_type');
$this->db->where('status', 1);
$this->db->order_by('trip_type_name', 'asc');
return $this->db->get();
}
public function get_tariff_amt($trip_type,$vehicle_type,$avg_km) {
$query = $this->db->query("select * from sblt_rate where rate_vendor = '1' and rate_trip_type = '$trip_type' and rate_vehicle_type = '$vehicle_type' order by abs(upto_km - $avg_km) limit 1");
return $query->row();
}
public function get_hsn() {
$query = $this->db->query("select * from sblt_hsn limit 1");
return $query->row();
}
public function get_average_km($orgin,$destination){
$query = $this->db->query("SELECT * FROM `sblt_average_km` where (frm_city = '$orgin' and to_city = '$destination') or (frm_city = '$destination' and to_city = '$orgin') limit 1");
return $query->row();
}
public function get_tariff($trip_type,$vehicle_type) {
$this->db->from('rate');
$this->db->join('veh_type', 'veh_type.veh_type_id = rate.rate_vehicle_type','left');
$this->db->join('vendor', 'vendor.vendor_id = rate.rate_vendor','left');
$this->db->where('rate_trip_type', $trip_type);
$this->db->where('rate_vehicle_type', $vehicle_type);
$this->db->where('rate_vendor',1);
$this->db->order_by('vendor_name', 'ACS');
$suggestions = array();
foreach($this->db->get()->result() as $row){
$suggestions[] = array('vendor_name' => $row->vendor_name,'veh_type' => $row->veh_type,'upto_hrs' => $row->upto_hrs,'upto_km' => $row->upto_km,'upto_amt' => $row->upto_amt,'extra_km_amt' => $row->extra_km_amt,'extra_amt' => $row->extra_amt);
}
return $suggestions;
}
public function get_fy_count($trip_type,$booking_id){
$this->db->from('booking');
$this->db->where('booking_id', $booking_id);
$booking_info = $this->db->get()->row();
if($booking_info){
$tour_no = $booking_info->tour_no;
$created_date = $booking_info->created_date;
if($tour_no){
return $tour_no;
}else{
return $this->get_trip_id($trip_type,$created_date);
}
}else{
return $this->get_trip_id($trip_type,"");
}
}
public function get_trip_id($trip_type,$created_date){
//echo "e";
/*if (date('m') >= 4) {
$fy_frm = date('Y')."-04-01";
$fy_to = (date('Y') +1)."-03-31";
}else{
$fy_frm = (date('Y')-1)."-04-01";
$fy_to = date('Y')."-03-31";
}*/
$trip_name = $this->get_trip_name($trip_type);
$words = explode(" ", $trip_name);
$words_count = count($words);
if((int)$words_count > 1){
$trip_short = "";
foreach ($words as $w) {
$trip_short .= $w[0];
}
}else{
$trip_short = substr($trip_name, 0, 2);
}
if($created_date){
$year = date("Y",strtotime($created_date));
}else{
$year = date('Y');
}
$fy_frm = $year."-01-01";
$fy_to = $year."-12-31";
$this->db->select('MAX(tour_no) as tour_no');
$this->db->from('booking');
$this->db->where('DATE_FORMAT(created_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($fy_frm).' AND '.$this->db->escape($fy_to));
$this->db->where('trip_type',$trip_type);
$this->db->where('tour_no !=',"");
$this->db->like('tour_no', strtoupper($trip_short));
$rslt = $this->db->get()->result();
$db_tour_no = $rslt[0]->tour_no;
$count = substr($db_tour_no, -4);
$count = (int)$count + 1;
$count = str_pad($count, 4, '0', STR_PAD_LEFT);
$y = date('y');
$tour_no = strtoupper($trip_short.$y.$count);
return $tour_no;
$this->db->from('booking');
$this->db->where('tour_no',$tour_no);
$count = $this->db->get()->num_rows();
if((int)$count===0)
{
return $tour_no;
}
else
{
/*if (date('m') >= 4) {
$fy_frm = date('Y')."-04-01";
$fy_to = (date('Y') +1)."-03-31";
}else{
$fy_frm = (date('Y')-1)."-04-01";
$fy_to = date('Y')."-03-31";
}*/
$trip_name = $this->get_trip_name($trip_type);
$words = explode(" ", $trip_name);
$words_count = count($words);
if((int)$words_count > 1){
$trip_short = "";
foreach ($words as $w) {
$trip_short .= $w[0];
}
}else{
$trip_short = substr($trip_name, 0, 2);
}
$year = date('Y');
$fy_frm = $year."-01-01";
$fy_to = $year."-12-31";
$this->db->select('MAX(tour_no) as tour_no');
$this->db->from('booking');
$this->db->where('DATE_FORMAT(created_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($fy_frm).' AND '.$this->db->escape($fy_to));
$this->db->where('trip_type',$trip_type);
$this->db->where('tour_no !=',"");
$this->db->like('tour_no', strtoupper($trip_short));
$rslt = $this->db->get()->result();
$db_tour_no = $rslt[0]->tour_no;
$count = substr($db_tour_no, -4);
$count = (int)$count + 1;
$count = str_pad($count, 4, '0', STR_PAD_LEFT);
$y = date('y');
$tour_no = strtoupper($trip_short.$y.$count);
return $tour_no;
}
}
public function update_average_km($orgin,$destination,$avg_km){
$query = $this->db->query("SELECT * FROM `sblt_average_km` where (frm_city = '$orgin' and to_city = '$destination') or (frm_city = '$destination' and to_city = '$orgin')");
$count = $query->num_rows();
if((int)$count === 0){
$average_km_data = array(
'frm_city' => $orgin,
'to_city' => $destination,
'km' => $avg_km,
'created_by' => $this->session->userdata('emp_id'),
'created_date' => date("Y-m-d H:i:s"),
);
$this->db->insert('average_km', $average_km_data);
}
return;
}
public function get_trip_name($trip_type){
$this->db->from('trip_type');
$this->db->where('trip_type_id',$trip_type);
$rslt = $this->db->get()->row();
$trip_name = $rslt->trip_type_name;
return $trip_name;
}
public function get_demand_date($demand_month){
$this->db->from('demand_date');
$this->db->where('demand_month',$demand_month);
return $this->db->get()->row();
}
public function get_menu($catering_menu_id){
$this->db->from('catering_menu');
$this->db->where('catering_menu_id',$catering_menu_id);
$this->db->where('status',1);
return $this->db->get()->result();
}
public function get_vehicle($vehicle_type){
$this->db->from('vehicle');
$this->db->where('vehicle_type',$vehicle_type);
$this->db->where('status',1);
return $this->db->get()->result();
}
public function get_vehicle_live($vehicle_type){
$this->db->from('vehicle');
$this->db->where('vehicle_type',$vehicle_type);
$this->db->where('vehicle.status',1);
$this->db->where('vehicle.live_chart',1);
return $this->db->get()->result();
}
public function booking_chart_exist($chart_month,$vehicle_id){
$this->db->from('booking_chart');
$this->db->where('chart_month',$chart_month);
$this->db->where('chart_vehicle_id',$vehicle_id);
return $this->db->get()->num_rows();
}
public function get_booking_chart($chart_month,$vehicle_id,$vehicle_type){
$count = $this->booking_chart_exist($chart_month,$vehicle_id);
if((int)$count === 0){
$this->db->insert('booking_chart', array('chart_month'=>$chart_month,'chart_vehicle_id'=>$vehicle_id,'chart_vehicle_type'=>$vehicle_type,'mode'=>"SALES"));
$this->db->insert('booking_chart', array('chart_month'=>$chart_month,'chart_vehicle_id'=>$vehicle_id,'chart_vehicle_type'=>$vehicle_type,'mode'=>"OPERATION"));
}
$this->db->from('booking_chart');
$this->db->where('chart_month',$chart_month);
$this->db->where('chart_vehicle_id',$vehicle_id);
return $this->db->get()->result();
}
public function update_booking_chart_by_id($booking_id,$mode){
$booking_info = $this->get_info($booking_id);
$from_date = $booking_info->from_date;
$from_date = explode('-', $from_date);
$from_year = $from_date[0];
$from_month = $from_date[1];
$frm_mon_year = $from_year."-".$from_month;
$to_date = $booking_info->to_date;
$to_date = explode('-', $to_date);
$to_year = $to_date[0];
$to_month = $to_date[1];
$to_mon_year = $to_year."-".$to_month;
$db_from = $booking_info->from_date;
$db_to = $booking_info->to_date;
$vehicle = array();
if($booking_info->preferred_vehicle){
if($booking_info->preferred_vehicle !== "all"){
$vehicle = explode(',', $booking_info->preferred_vehicle);
}
}
if(!empty($vehicle)) {
$vehicle_count = count($vehicle) - 1;
for($j = 0; $j <= $vehicle_count; $j++){
$vehicle_id = $vehicle[$j];
$from = strtotime($db_from);
$to = strtotime($db_to);
$upd_qry = "";
$upd_qry1 = "";
for($i=$from; $i<=$to; $i+=86400) {
$between = date("Y-m-d", $i);
$between = explode('-', $between);
$between_year = $between[0];
$between_month = $between[1];
$between_date = $between[2];
$between_date = ltrim($between_date, '0');
$between_mon_year = $between_year."-".$between_month;
$col = "day_".$between_date;
if($mode === "UNCHK"){
$chk = "0";
}else
if($mode === "CHK"){
$chk = "1";
}
if(($frm_mon_year !== $between_mon_year) && ($between_mon_year !== date('Y-m'))){
$upd_qry1 .= "$col = '$chk',";
}else{
$upd_qry .= "$col = '$chk',";
}
}
if($upd_qry !== ""){
$upd_qry = rtrim($upd_qry, ',');
$this->db->query("update sblt_booking_chart set $upd_qry where chart_month = '$frm_mon_year' and mode = 'SALES' and chart_vehicle_id = $vehicle_id");
}
if($upd_qry1 !== ""){
$upd_qry1 = rtrim($upd_qry1, ',');
$this->db->query("update sblt_booking_chart set $upd_qry1 where chart_month = '$between_mon_year' and mode = 'SALES' and chart_vehicle_id = $vehicle_id");
}
}
}
return;
}
/* uncheck and check booking
public function update_booking_chart($booking_data){
$from_date = $booking_data['from_date'];
$from_date = explode('-', $from_date);
$from_year = $from_date[0];
$from_month = $from_date[1];
$frm_mon_year = $from_year."-".$from_month;
$to_date = $booking_data['to_date'];
$to_date = explode('-', $to_date);
$to_year = $to_date[0];
$to_month = $to_date[1];
$to_mon_year = $to_year."-".$to_month;
$from_tot_days =cal_days_in_month(CAL_GREGORIAN,$from_month,$from_year);
for($i = 1; $i <= $from_tot_days; $i++){
$col = "day_".$i;
$frm_qry .= "$col = '0',";
}
$frm_qry = rtrim($frm_qry, ',');
$this->db->query("update sblt_booking_chart set $frm_qry where chart_month = '$frm_mon_year' and mode = 'SALES'");
if(($frm_mon_year !== $to_mon_year) && ($to_mon_year !== date('Y-m'))){
$to_tot_days =cal_days_in_month(CAL_GREGORIAN,$to_month,$to_year);
for($i = 1; $i <= $to_tot_days; $i++){
$col = "day_".$i;
$to_qry .= "$col = '0',";
}
$to_qry = rtrim($to_qry, ',');
$this->db->query("update sblt_booking_chart set $to_qry where chart_month = '$to_mon_year' and mode = 'SALES'");
}
$booking_info = $this->db->query("SELECT * FROM `sblt_booking` where booking_sts = '2' and (from_date like '$frm_mon_year%' or to_date like '$to_mon_year%')");
$booking_rslt = $booking_info->result();
foreach($booking_rslt as $booking){
$date_from = $booking->from_date;
$date_to = $booking->to_date;
$vehicle = explode(',', $booking->preferred_vehicle);
$vehicle_count = count($vehicle) - 1;
for($j = 0; $j <= $vehicle_count; $j++){
$vehicle_id = $vehicle[$j];
$from = strtotime($date_from);
$to = strtotime($date_to);
$upd_qry = "";
$upd_qry1 = "";
for($i=$from; $i<=$to; $i+=86400) {
$between = date("Y-m-d", $i);
$between = explode('-', $between);
$between_year = $between[0];
$between_month = $between[1];
$between_date = $between[2];
$between_date = ltrim($between_date, '0');
$between_mon_year = $between_year."-".$between_month;
$col = "day_".$between_date;
if(($frm_mon_year !== $between_mon_year) && ($between_mon_year !== date('Y-m'))){
$upd_qry1 .= "$col = '1',";
}else{
$upd_qry .= "$col = '1',";
}
}
if($upd_qry !== ""){
$upd_qry = rtrim($upd_qry, ',');
$this->db->query("update sblt_booking_chart set $upd_qry where chart_month = '$frm_mon_year' and mode = 'SALES' and chart_vehicle_id = $vehicle_id");
}
if($upd_qry1 !== ""){
$upd_qry1 = rtrim($upd_qry1, ',');
$this->db->query("update sblt_booking_chart set $upd_qry1 where chart_month = '$between_mon_year' and mode = 'SALES' and chart_vehicle_id = $vehicle_id");
}
}
}
return;
}
*/
public function get_booking_log($booking_id){
$this->db->select('booking_id,tour_no,booking_type_name,frm.city_name as frm_city_name, to.city_name as to_city_name,from_date,to_date,trip_type_name,veh_type,booking_sts,trip_details,bus_count,reporting_time,booking_log.created_by,booking_log.created_date,booking_log.updated_by,booking_log.updated_date,booking_log.deleted_by,booking_log.deleted_date');
$this->db->from('booking_log');
$this->db->join('trip_type', 'trip_type.trip_type_id = booking_log.trip_type','left');
$this->db->join('veh_type', 'veh_type.veh_type_id = booking_log.vehicle_type','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = booking_log.cust_type','left');
$this->db->join('city frm', 'frm.city_id = booking_log.orgin','left');
$this->db->join('city to', 'to.city_id = booking_log.destination','left');
$this->db->where('booking_id',$booking_id);
$booking_log = $this->db->get()->result();
foreach($booking_log as $booking_info){
$created_by = (int)$booking_info->created_by;
$updated_by = (int)$booking_info->updated_by;
$deleted_by = (int)$booking_info->deleted_by;
if((int)$created_by>0){
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id','left');
$this->db->where('id',$created_by);
$created_info = $this->db->get()->result();
if($created_info){
$booking_info->emp_id = $created_info[0]->username;
$booking_info->emp_name = $created_info[0]->first_name;
$booking_info->log_date = date('d-m-Y',strtotime($booking_info->created_date));
}
}else
if((int)$updated_by>0){
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id','left');
$this->db->where('id',$updated_by);
$created_info = $this->db->get()->result();
if($created_info){
$booking_info->emp_id = $created_info[0]->username;
$booking_info->emp_name = $created_info[0]->first_name;
$booking_info->log_date = date('d-m-Y',strtotime($booking_info->updated_date));
}
}else
if((int)$deleted_by>0){
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id','left');
$this->db->where('id',$deleted_by);
$created_info = $this->db->get()->result();
if($created_info){
$booking_info->emp_id = $created_info[0]->username;
$booking_info->emp_name = $created_info[0]->first_name;
$booking_info->log_date = date('d-m-Y',strtotime($booking_info->deleted_date));
}
}
}
return $booking_log;
}
public function search($search,$filters,$rows = 0,$limit_from= 0,$sort='',$order='desc'){
$start_date = $filters['start_date'];
$end_date = $filters['end_date'];
if(!$sort){
$sort = "from_date";
}
$this->db->select('booking_id,tour_no,customer_name,booking_type_name,frm.city_name as frm_city_name, to.city_name as to_city_name,from_date,to_date,trip_type_name,veh_type,booking_sts,trip_details,catering,balance_amount,bus_count,phone_number,reporting_time');
$this->db->from('booking');
$this->db->join('customers', 'customers.cust_id = booking.cust_id','left');
$this->db->join('trip_type', 'trip_type.trip_type_id = booking.trip_type','left');
$this->db->join('veh_type', 'veh_type.veh_type_id = booking.vehicle_type','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = booking.cust_type','left');
$this->db->join('city frm', 'frm.city_id = booking.orgin','left');
$this->db->join('city to', 'to.city_id = booking.destination','left');
if($search){
$this->db->group_start();
$this->db->like('booking_id',$search);
$this->db->or_like('tour_no',$search);
$this->db->or_like('customer_name',$search);
$this->db->or_like('phone_number',$search);
$this->db->or_like('booking_type_name',$search);
$this->db->or_like('trip_type_name',$search);
$this->db->or_like('frm.city_name',$search);
$this->db->or_like('to.city_name',$search);
$this->db->or_like('veh_type',$search);
if(strpos($search, '-') !== false){
$this->db->or_like('from_date', date('Y-m-d',strtotime($search)));
$this->db->or_like('to_date', date('Y-m-d',strtotime($search)));
}
$this->db->group_end();
}
$this->db->group_start();
/*
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date))->or_where('DATE_FORMAT(to_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
*/
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$this->db->group_end();
$sts = array();
if($filters['Follow_Up'] != FALSE){ $sts[] = 1; }
if($filters['Confirm'] != FALSE){ $sts[] = 2; }
if($filters['Cancelled'] != FALSE){ $sts[] = 3; }
if($filters['Completed'] != FALSE){ $sts[] = 4; }
if($filters['On_progress'] != FALSE){ $sts[] = 5; }
if(!empty($sts)) {
$this->db->where_in('booking_sts', $sts);
}else{
$this->db->where('booking_sts !=', '3');
}
$this->db->where('booking.status',1);
$this->db->order_by($sort,$order);
if($rows>0){
$this->db->limit($rows, $limit_from);
}
return $this->db->get();
}
public function get_found_rows($search,$filters){
return $this->search($search, $filters)->num_rows();
}
public function get_total_trip_amount($booking_id){
$booking_info = $this->get_load_data($booking_id);
$catering_order = $this->get_catering_order($booking_id);
$stay_info = $this->get_stay_list($booking_id);
$balance_log = $this->get_bal_log_amount($booking_id);
$extra_log = $this->get_extra_amount($booking_id);
foreach($booking_info as $booking){
$bus_count = $booking->bus_count;
$trip_days = $booking->trip_days;
$avg_km = $booking->avg_km;
$booking_amount = $booking->booking_amount;
$advance_amount = $booking->advance_amount;
$tax_precentage = $booking->tax_precentage;
$payment_type = $booking->payment_type;
$discount_amount = $booking->discount_amount;
$state = $booking->state;
$includ_gst = $booking->includ_gst;
$toll = $booking->toll;
$parking = $booking->parking;
$driver_batta = $booking->driver_batta;
$state_tax = $booking->state_tax;
}
$igst = 0;
$sgst = 0;
$cgst = 0;
if($tax_precentage > 0){
$igst = $tax_precentage;
$sgst = $tax_precentage /2;
$cgst = $tax_precentage /2;
}
$sub_total = 0;
foreach($catering_order as $order){
$amount = $order->amount;
$sub_total += $amount;
}
foreach($stay_info as $stay){
$stay_tot_amt = $stay->stay_tot_amt;
$sub_total += $stay_tot_amt;
}
$discount_total = $booking_amount - $discount_amount;
$sub_total = $discount_total + $sub_total + (int)$extra_log->extra_amt;
$igst_amt = $sub_total * $igst/100;
$sgst_amt = $sub_total * $sgst/100;
$cgst_amt = $sub_total * $cgst/100;
$total_amt = $sub_total;
if($includ_gst === "1"){
if($state === "33"){
$total_amt = $sub_total + $cgst_amt + $sgst_amt;
$cgst_amt = number_format($cgst_amt,2);
$sgst_amt = number_format($sgst_amt,2);
}else{
$total_amt = $sub_total + $igst_amt;
$igst_amt = number_format($igst_amt,2);
}
}else{
if($toll === "1"){
$toll_amt = $this->get_suspence_amount($booking_id,'Toll');
}
if($parking === "1"){
$parking_amt = $this->get_suspence_amount($booking_id,'Parking');
}
if($driver_batta === "1"){
$driver_batta_amt = $this->get_suspence_amount($booking_id,'driver_batta');
}
if($state_tax === "1"){
$state_tax_amt = $this->get_suspence_amount($booking_id,'TAX');
}
$kickbacks_amt = $this->get_suspence_amount($booking_id,'Kickbacks');
$extra = (int)$toll_amt + (int)$parking_amt + (int)$driver_batta_amt + (int)$state_tax_amt + (int)$kickbacks_amt;
$total_amt = $total_amt + $extra;
}
$advance = (int)$advance_amount + (int)$balance_log->bal_log_amt;
$balance_amount = (int)$total_amt - (int)$advance;
return $balance_amount;
}
public function get_suspence_amount($booking_id,$mode){
$this->db->select('IFNULL(sum(suspence_amt), 0) as amt');
$this->db->from('operation');
$this->db->join('suspence', 'suspence.suspence_op_id = operation.operation_id','left');
$this->db->where('operation.status',1);
$this->db->where('operation.booking_id',$booking_id);
$this->db->where('suspence.suspence_info',$mode);
$result = $this->db->get()->row();
$suspence_amt = $result->amt;
return $suspence_amt;
}
public function get_load_data($booking_id){
$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name,booking.created_date as booking_date');
$this->db->from('booking');
$this->db->join('customers', 'customers.cust_id = booking.cust_id','left');
$this->db->join('trip_type', 'trip_type.trip_type_id = booking.trip_type','left');
$this->db->join('veh_type', 'veh_type.veh_type_id = booking.vehicle_type','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = booking.cust_type','left');
$this->db->join('city frm', 'frm.city_id = booking.orgin','left');
$this->db->join('city to', 'to.city_id = booking.destination','left');
$this->db->join('state', 'state_code = customers.state','left');
$this->db->where('booking_id',$booking_id);
return $this->db->get()->result();
}
public function get_info($booking_id){
$this->db->from('booking');
$this->db->where('booking_id',$booking_id);
$a=$this->db->get();
if($a->num_rows() === 1){
return $a->row();
}else{
foreach ($this->db->list_fields('booking') as $field){
$PersonObj->field= '';
}
return $PersonObj;
}
}
public function get_customer($cust_id) {
$this->db->from('customers');
$this->db->where('cust_id', $cust_id);
$a=$this->db->get();
if($a->num_rows() === 1){
return $a->row();
}else{
foreach ($this->db->list_fields('booking') as $field){
$PersonObj->field= '';
}
return $PersonObj;
}
}
public function exists($booking_id){
$this->db->from('booking');
$this->db->where('booking_id', $booking_id);
return ($this->db->get()->num_rows() == 1);
}
public function operation_exists($booking_id){
$this->db->from('operation');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->num_rows();
}
public function save_booking(&$booking_data,$operation_data,$accounts_data, $booking_id = FALSE){
if(!$booking_id || !$this->exists($booking_id, TRUE)){
$booking_data['created_by'] = $this->session->userdata('emp_id');
$booking_data['created_date'] = date("Y-m-d H:i:s");
if($this->db->insert('booking', $booking_data)){
$booking_data['booking_id'] = $this->db->insert_id();
$booking_id = $this->db->insert_id();
$balance_amount = $this->get_total_trip_amount($booking_id);
$this->db->where('booking_id', $booking_id);
$this->db->update('booking', array('balance_amount'=>$balance_amount));
$operation_count = $this->operation_exists($booking_id);
if(((int)$operation_count === 0) && ($booking_data['booking_sts'] === "2")){
$operation_data['booking_id'] = $booking_id;
$operation_data['created_by'] = $this->session->userdata('emp_id');
$operation_data['created_date'] = date("Y-m-d H:i:s");
$operation_data['balance_amount'] = $balance_amount;
$this->db->insert('operation', $operation_data);
$operation_id = $this->db->insert_id();
$accounts_data['operation_id'] = $operation_id;
$accounts_data['booking_id'] = $booking_id;
$accounts_data['created_by'] = $this->session->userdata('emp_id');
$accounts_data['created_date'] = date("Y-m-d H:i:s");
$accounts_data['balance_amount'] = $balance_amount;
$this->db->insert('accounts', $accounts_data);
}
$this->db->insert('booking_log', $booking_data);
$this->update_booking_chart_by_id($booking_id,"CHK");
return TRUE;
}
return FALSE;
}else{
$this->update_booking_chart_by_id($booking_id,"UNCHK");
$booking_data['updated_by'] = $this->session->userdata('emp_id');
$booking_data['updated_date'] = date("Y-m-d H:i:s");
$this->db->where('booking_id', $booking_id);
$this->db->update('booking', $booking_data);
$balance_amount = $this->get_total_trip_amount($booking_id);
$this->db->where('booking_id', $booking_id);
$this->db->update('booking', array('balance_amount'=>$balance_amount));
if(($booking_data['booking_sts'] === "2") || ($booking_data['booking_sts'] === "3")){
$operation_data['created_by'] = $this->session->userdata('emp_id');
$operation_data['created_date'] = date("Y-m-d H:i:s");
$operation_data['booking_id'] = $booking_id;
$operation_data['updated_by'] = $this->session->userdata('emp_id');
$operation_data['updated_date'] = date("Y-m-d H:i:s");
$operation_data['balance_amount'] = $balance_amount;
$accounts_data['booking_id'] = $booking_id;
$accounts_data['created_by'] = $this->session->userdata('emp_id');
$accounts_data['created_date'] = date("Y-m-d H:i:s");
$accounts_data['updated_by'] = $this->session->userdata('emp_id');
$accounts_data['updated_date'] = date("Y-m-d H:i:s");
$accounts_data['balance_amount'] = $balance_amount;
$operation_count = $this->operation_exists($booking_id);
if((int)$operation_count === 0){
$this->db->insert('operation', $operation_data);
$operation_id = $this->db->insert_id();
$accounts_data['operation_id'] = $operation_id;
$this->db->insert('accounts', $accounts_data);
}else{
$this->db->where('booking_id', $booking_id);
$this->db->update('operation', $operation_data);
$this->db->where('booking_id', $booking_id);
$this->db->update('accounts', $accounts_data);
}
}
$this->update_booking_chart_by_id($booking_id,"CHK");
return $this->db->insert('booking_log', $booking_data);
}
}
public function catering_exists($booking_id,$date){
$this->db->from('catering_orders');
$this->db->where('booking_id', $booking_id);
$this->db->where('order_date', $date);
return $this->db->get()->num_rows();
}
public function catering_save($catering_date,$booking_id, $date){
$count = $this->catering_exists($booking_id,$date);
if($count === 0){
$this->db->insert('catering_orders', $catering_date);
return $this->update_food_stay_amount($booking_id);
}else{
$this->db->where('booking_id', $booking_id);
$this->db->where('order_date', $date);
$this->db->update('catering_orders', $catering_date);
return $this->update_food_stay_amount($booking_id);
}
}
public function update_food_stay_amount($booking_id){
$catering = $this->get_catering_amount($booking_id);
$catering_amount = $catering->catering_amount;
$stay = $this->get_stay_amount($booking_id);
$stay_amount = $stay->stay_amount;
$this->db->where('booking_id', $booking_id);
$this->db->update('booking',array('catering_amount'=>$catering_amount,'stay_amount'=>$stay_amount));
$this->db->where('booking_id', $booking_id);
$this->db->update('booking_log',array('catering_amount'=>$catering_amount,'stay_amount'=>$stay_amount));
$balance_amount = $this->get_total_trip_amount($booking_id);
$this->db->where('booking_id', $booking_id);
$this->db->update('booking', array('balance_amount'=>$balance_amount));
$this->db->where('booking_id', $booking_id);
$this->db->update('operation', array('balance_amount'=>$balance_amount));
$this->db->where('booking_id', $booking_id);
return $this->db->update('accounts', array('balance_amount'=>$balance_amount));
}
public function get_order($order_id){
$this->db->from('catering_orders');
$this->db->where('order_id',$order_id);
$this->db->where('status',1);
return $this->db->get()->result();
}
public function get_catering_order($booking_id) {
$this->db->from('catering_orders');
$this->db->where('booking_id', $booking_id);
$this->db->order_by('order_date', 'asc');
return $this->db->get()->result();
}
public function get_extra_amount($booking_id) {
$this->db->select('IFNULL(sum(extra_amt), 0) as extra_amt');
$this->db->from('operation');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
public function get_catering_amount($booking_id) {
$this->db->select('IFNULL(sum(amount), 0) as catering_amount');
$this->db->from('catering_orders');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
public function stay_exists($booking_id,$stay_date,$sharing){
$this->db->from('stay');
$this->db->where('booking_id', $booking_id);
$this->db->where('stay_date', $stay_date);
$this->db->where('sharing', $sharing);
return $this->db->get()->num_rows();
}
public function stay_save($stay_data,$booking_id, $stay_date, $sharing){
$count = $this->stay_exists($booking_id,$stay_date,$sharing);
if($count === 0){
$this->db->insert('stay', $stay_data);
return $this->update_food_stay_amount($booking_id);
}else{
$this->db->where('booking_id', $booking_id);
$this->db->where('stay_date', $stay_date);
$this->db->where('sharing', $sharing);
$this->db->update('stay', $stay_data);
return $this->update_food_stay_amount($booking_id);
}
}
public function get_stay($stay_id){
$this->db->from('stay');
$this->db->where('stay_id',$stay_id);
$this->db->where('status',1);
return $this->db->get()->result();
}
public function get_stay_list($booking_id) {
$this->db->from('stay');
$this->db->where('booking_id', $booking_id);
$this->db->order_by('stay_date', 'asc');
return $this->db->get()->result();
}
public function get_stay_amount($booking_id) {
$this->db->select('IFNULL(sum(stay_tot_amt), 0) as stay_amount');
$this->db->from('stay');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
public function balance_amount($booking_id) {
$this->db->from('booking');
$this->db->where('booking_id', $booking_id);
$balance_info = $this->db->get()->row();
return $balance_info->balance_amount;
}
public function save_balance_log($balance_data,$bal_log_id){
$booking_id = $balance_data['booking_id'];
if($bal_log_id){
$this->db->where('balance_log_id', $bal_log_id);
$this->db->update('balance_log', $balance_data);
$balance_amount = $this->get_total_trip_amount($booking_id);
$this->db->where('booking_id', $booking_id);
$this->db->update('booking', array('balance_amount'=>$balance_amount));
$this->db->where('booking_id', $booking_id);
$this->db->update('operation', array('balance_amount'=>$balance_amount));
$this->db->where('booking_id', $booking_id);
return $this->db->update('accounts', array('balance_amount'=>$balance_amount));
}else{
$this->db->insert('balance_log', $balance_data);
$balance_amount = $this->get_total_trip_amount($booking_id);
$this->db->where('booking_id', $booking_id);
$this->db->update('booking', array('balance_amount'=>$balance_amount));
$this->db->where('booking_id', $booking_id);
$this->db->update('operation', array('balance_amount'=>$balance_amount));
$this->db->where('booking_id', $booking_id);
return $this->db->update('accounts', array('balance_amount'=>$balance_amount));
}
}
public function balance_log_list($booking_id) {
$this->db->from('balance_log');
$this->db->where('booking_id', $booking_id);
$this->db->order_by('balance_date', 'asc');
return $this->db->get()->result();
}
public function get_balance_log($bal_log_id){
$this->db->from('balance_log');
$this->db->where('balance_log_id',$bal_log_id);
return $this->db->get()->result();
}
public function get_bal_log_amount($booking_id) {
$this->db->select('IFNULL(sum(amount), 0) as bal_log_amt');
$this->db->from('balance_log');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
public function pack_exists($booking_id,$pack_date){
$this->db->from('package');
$this->db->where('booking_id', $booking_id);
$this->db->where('pack_date', $pack_date);
return $this->db->get()->num_rows();
}
public function pack_save($pack_data,$booking_id, $pack_date){
$count = $this->pack_exists($booking_id,$pack_date);
if($count === 0){
return $this->db->insert('package', $pack_data);
}else{
$this->db->where('booking_id', $booking_id);
$this->db->where('pack_date', $pack_date);
return $this->db->update('package', $pack_data);
}
}
public function get_pack($pack_id){
$this->db->from('package');
$this->db->where('pack_id',$pack_id);
$this->db->where('status',1);
return $this->db->get()->result();
}
public function get_pack_list($booking_id) {
$this->db->from('package');
$this->db->where('booking_id', $booking_id);
$this->db->order_by('pack_date', 'asc');
return $this->db->get()->result();
}
public function delete_list($booking_id){
$this->db->where_in('booking_id',$booking_id);
return $this->db->update('booking',array('status'=>0,'deleted_by'=>$this->session->userdata('emp_id'),'deleted_date'=>date('Y-m-d H:i:s')));
}
public function get_multiple_info($cat_ids){
$this->db->from('booking');
$this->db->where_in('booking_id', $cat_ids);
return $this->db->get()->result_array();
}
}
?>