File: /home/cafsindia/allyindian_com/sbltt/application/models/Op_report_model.php
<?php
class Op_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'];
$this->db->select('operation.operation_id,tour_no,GROUP_CONCAT(DISTINCT vehicle_no SEPARATOR ",") as vehicle_no,customer_name,customers.phone_number,cust_sts,trip_days,bus_count,operation.report_address,reporting_time,billing_name,first_name,GROUP_CONCAT(DISTINCT op_driver_name SEPARATOR ",") as op_driver_name,op_driver_number,operation.avg_km,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,operation_sts,trip_details,booking_amount,advance_amount,discount_amount,balance_amount,payment_type,operation_type,catering,operation.tp_info,catering_orders.amount,stay_tot_amt,trip_start_km,trip_end_km,extra_km,extra_amt,op_suspence_amt,diesel_amt,op_extra_km,op_extra_amt');
$this->db->from('operation');
$this->db->join('operation_line', 'operation_line.operation_id = operation.operation_id','left');
$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_line.op_vehicle_type','left');
$this->db->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = operation.cust_type','left');
$this->db->join('employees', 'employees.id = operation.referral','left');
$this->db->join('people', 'people.person_id = employees.person_id','left');
$this->db->join('stay', 'stay.booking_id = operation.booking_id','left');
$this->db->join('diesel', 'diesel.diesel_op_id = operation.operation_id','left');
$this->db->join('catering_orders', 'catering_orders.booking_id = operation.booking_id','left');
$this->db->join('city frm', 'frm.city_id = operation.orgin','left');
$this->db->join('city to', 'to.city_id = operation.destination','left');
if($search){
$this->db->group_start();
$this->db->like('operation.operation_id',$search);
$this->db->or_like('tour_no',$search);
$this->db->or_like('customer_name',$search);
$this->db->or_like('customers.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));
$this->db->group_end();
$this->db->where('operation.operation_sts !=', '3');
$this->db->where('operation.status',1);
$this->db->where('operation_line.status',1);
$this->db->group_by('operation.operation_id');
$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 excel_operation_type_report($start_date, $end_date){
$this->db->select('operation.booking_id,operation.operation_id,tour_no,vehicle_id,pax,vehicle_no,customer_name,customers.phone_number,cust_sts,trip_days,bus_count,operation.report_address,reporting_time,billing_name,first_name, op_driver_name,op_driver_number,operation.avg_km,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,operation_sts,hire_charges,trip_details,booking_amount,advance_amount,discount_amount,balance_amount,payment_type,operation_type,catering,operation.tp_info,catering_orders.amount,stay_tot_amt,trip_start_km,trip_end_km,op_extra_pre_km,op_suspence_amt,diesel_amt,op_extra_km,op_extra_amt,reporting_time,vendor_name,op_driver_number,cust_type,toll,parking,remark,suspence_info');
$this->db->from('operation_line');
$this->db->join('operation', 'operation_line.operation_id = operation.operation_id','left');
$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_line.op_vehicle_type','left');
$this->db->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = operation.cust_type','left');
$this->db->join('employees', 'employees.id = operation.referral','left');
$this->db->join('people', 'people.person_id = employees.person_id','left');
$this->db->join('stay', 'stay.booking_id = operation.booking_id','left');
$this->db->join('diesel', 'diesel.diesel_op_id = operation.operation_id','left');
$this->db->join('catering_orders', 'catering_orders.booking_id = operation.booking_id','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('vendor', 'vendor.vendor_id = vehicle.owned_by','left');
$this->db->join('suspence', 'suspence.suspence_op_id = operation.operation_id','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));
$this->db->group_end();
$this->db->where('operation.operation_sts !=', '3');
$this->db->where('operation.status',1);
$this->db->where('operation_line.status',1);
$this->db->group_by('op_line_id');
$excel = $this->db->get();
return $excel->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_suspence_by_vech($op_id,$vech_id,$mode){
$this->db->select('IFNULL(sum(suspence_amt), 0) as amt');
$this->db->from('suspence');
$this->db->where('suspence_op_id',$op_id);
$this->db->where('suspence_vch_id',$vech_id);
$this->db->where('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();
}
public function get_driver_by_vech($op_id,$vech_id){
$this->db->select('IFNULL(sum(driver_suspence_amount), 0) as amt');
$this->db->from('driver_suspence');
$this->db->where('driver_suspence_op_id',$op_id);
$this->db->where('driver_suspence_vch_id',$vech_id);
$result = $this->db->get()->row();
$driver_suspence_amount = $result->amt;
return $driver_suspence_amount;
}
}
?>