File: /home/cafsindia/allyindian_com/sbltt/application/models/Br_report_model.php
<?php
class Br_report_model extends CI_Model
{
public function search($search, $filters, $rows = 0, $limit_from = 0, $sort = '', $order = 'asc'){
$start_date = $filters['start_date'];
$end_date = $filters['end_date'];
if(!$sort){
$sort = "from_date";
}
$this->db->select('booking_id,tour_no,customer_name,customers.phone_number,cust_sts,trip_days,bus_count,booking.report_address,billing_name,first_name,avg_km,booking_type_name,frm.city_name as frm_city_name, to.city_name as to_city_name,from_date,to_date,reporting_time,trip_type_name,veh_type,booking_sts,trip_details,ncd,booking_amount,advance_amount,discount_amount,balance_amount,payment_type,operation_type,catering,tp_info');
$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('employees', 'employees.id = booking.referral','left');
$this->db->join('people', 'people.person_id = employees.person_id','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('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));
$this->db->group_end();
$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();
//echo $this->db->last_query();
}
public function get_found_rows($search, $filters){
return $this->search($search, $filters)->num_rows();
}
public function vehicle_type_report_chart($start_date,$end_date){
$qry = $this->db->query("SELECT count(*) as tot,veh_type from sblt_booking inner join sblt_operation on sblt_operation.booking_id = sblt_booking.booking_id inner join sblt_veh_type on sblt_operation.vehicle_type = sblt_veh_type.veh_type_id where sblt_booking.status = '1' and sblt_booking.booking_sts = '2' and (DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date' or DATE_FORMAT(sblt_operation.to_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date') group by veh_type");
$result = $qry->result_array();
return $result;
}
public function vehicle_type_report($filters){
$start_date = $filters['start_date'];
$end_date = $filters['end_date'];
$start = date('d-M-Y',strtotime($start_date));
$end = date('d-M-Y',strtotime($end_date));
$book_qry = $this->db->query("SELECT count(*) as tot,veh_type from sblt_booking inner join sblt_operation on sblt_operation.booking_id = sblt_booking.booking_id inner join sblt_veh_type on sblt_operation.vehicle_type = sblt_veh_type.veh_type_id where sblt_booking.status = '1' and sblt_booking.booking_sts = '2' and (DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date' or DATE_FORMAT(sblt_operation.to_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date') group by veh_type ");
$book_row = $book_qry->result_array();
$tot = 0;
foreach ($book_row as $key => $value) {
$tot = $value['tot'];
$veh_type = $value['veh_type'];
$tr_line .="<tr>
<td style ='color:#00000 !important;text-align:center !important;'>$veh_type</td>
<td style ='color:#00000 !important;text-align:center !important;'>$tot</td></tr>";
}
echo "<table id='detail_list' class='table table-hover table-striped col-style'>
<thead>
<tr><h5 style='font-weight:bold;text-align:center !important;'>$start to $end</h5></tr>
<tr style='font-weight:bold;text-align:center !important;background:#3369E6;'>
<th style='text-align:center !important;padding:4px;'>Vehicle Type</th>
<th style='text-align:center !important;padding:4px;'>Count</th>
</tr>
</thead>
<tbody>$tr_line</tbody>
</table>";
}
public function get_value($ids,$cat){
$this->get_value($ids,$cat);
$ids = $row[0]['ids'];
$this->get_value($ids);
}
public function get_excel_data($start_date, $end_date){
$this->db->select('booking_id,tour_no,customer_name,customers.phone_number,alt_number,cust_email,customers.includ_gst,cust_sts,trip_days,bus_count,toll,parking,driver_batta,state_tax,booking.report_address,billing_name,first_name,avg_km,booking_type_name,frm.city_name as frm_city_name, to.city_name as to_city_name,from_date,to_date,reporting_time,trip_type_name,veh_type,booking_sts,trip_details,ncd,booking_amount,advance_amount,discount_amount,balance_amount,payment_type,operation_type,catering,tp_info,remark');
$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('permit_type', 'permit_type.permit_type_id = booking.permit_type','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = booking.cust_type','left');
$this->db->join('employees', 'employees.id = booking.referral','left');
$this->db->join('people', 'people.person_id = employees.person_id','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->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->group_end();
$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);
}
$booking = $this->db->get();
return $booking->result_array();
}
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_mode($booking_id,'Toll');
}
if($parking === "1"){
$parking_amt = $this->get_suspence_amount_mode($booking_id,'Parking');
}
if($driver_batta === "1"){
$driver_batta_amt = $this->get_suspence_amount_mode($booking_id,'driver_batta');
}
if($state_tax === "1"){
$state_tax_amt = $this->get_suspence_amount_mode($booking_id,'TAX');
}
$kickbacks_amt = $this->get_suspence_amount_mode($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_mode($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');
$this->db->from('operation');
$this->db->join('customers', 'customers.cust_id = operation.cust_id','left');
$this->db->join('trip_type', 'trip_type.trip_type_id = operation.trip_type','left');
$this->db->join('veh_type', 'veh_type.veh_type_id = operation.vehicle_type','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = operation.cust_type','left');
$this->db->join('city frm', 'frm.city_id = operation.orgin','left');
$this->db->join('city to', 'to.city_id = operation.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_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_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_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();
}
}
?>