File: //home/cafsindia/allyindian_com/sbltt/application/models/Acc_report_model.php
<?php
class Acc_report_model extends CI_Model
{
public function search($search, $filters, $rows = 0, $limit_from = 0, $sort = '', $order = 'asc'){
//echo "sat $search";
$start_date = $filters['start_date'];
$end_date = $filters['end_date'];
if(!$sort){
$sort = "accounts.from_date";
}
$this->db->select('accounts.booking_id,accounts.accounts_id,accounts.tour_no,GROUP_CONCAT(DISTINCT vehicle_no SEPARATOR ",") as vehicle_no,customer_name,customers.phone_number,cust_sts,accounts.trip_days,accounts.bus_count,operation.report_address,accounts.reporting_time,accounts.billing_name,first_name,GROUP_CONCAT(DISTINCT op_driver_name SEPARATOR ",") as op_driver_name,GROUP_CONCAT(trip_end_km - trip_start_km SEPARATOR ",") as tk,GROUP_CONCAT(DISTINCT op_driver_number SEPARATOR ",") as op_driver_number,operation.avg_km,booking_type_name,frm.city_name as frm_city_name,to.city_name as to_city_name,accounts.from_date,accounts.to_date,trip_type_name,veh_type,accounts_sts,accounts.trip_details,accounts.booking_amount,accounts.advance_amount,accounts.discount_amount,accounts.payment_type,accounts.operation_type,accounts.catering,operation.tp_info,GROUP_CONCAT(DISTINCT trip_start_km SEPARATOR ",") as trip_start_km,GROUP_CONCAT(DISTINCT trip_end_km SEPARATOR ",") as trip_end_km,extra_km,extra_amt,op_suspence_amt,diesel_amt,vendor_name');
$this->db->from('accounts');
$this->db->join('operation', 'operation.operation_id = accounts.operation_id','left');
$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.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('vendor', 'vendor.vendor_id = vehicle.owned_by','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('accounts.tour_no',$search);
//$this->db->or_like('accounts.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);
$this->db->or_like('accounts.billing_name',$search);
$this->db->or_like('accounts.from_date',$search);
$this->db->or_like('accounts.to_date',$search);
//$this->db->or_like('accounts_sts',$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(sblt_accounts.from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$this->db->group_end();
$this->db->where('accounts_sts !=', '3');
$this->db->where('accounts.status',1);
$this->db->group_by('accounts_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_account_type_report($start_date, $end_date){
$this->db->select('accounts.booking_id,accounts.accounts_id,accounts.tour_no,vehicle_no,customer_name,customers.phone_number,cust_sts,accounts.trip_days,accounts.bus_count,operation.report_address,accounts.reporting_time,accounts.billing_name,first_name,operation.pax,operation.operation_sts,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,accounts.from_date,accounts.to_date,trip_type_name,veh_type,accounts_sts,accounts.trip_details,accounts.booking_amount,accounts.advance_amount,accounts.tax_precentage,accounts.discount_amount,accounts.payment_type,accounts.operation_type,accounts.catering,operation.tp_info,IFNULL(sum(stay_tot_amt), 0) as stay_tot_amt,IFNULL(amount, 0) as catering_amount, trip_start_km,trip_end_km,extra_km,extra_amt,op_suspence_amt,diesel_amt,hire_charges,vendor_name,accounts.balance_amount,accounts.tax_precentage');
$this->db->from('accounts');
$this->db->join('operation', 'operation.operation_id = accounts.operation_id','left');
$this->db->join('booking', 'booking.booking_id = accounts.booking_id','left');
$this->db->join('operation_line', 'operation_line.operation_id = operation.operation_id','left');
$this->db->join('customers', 'customers.cust_id = accounts.cust_id','left');
$this->db->join('trip_type', 'trip_type.trip_type_id = accounts.trip_type','left');
$this->db->join('veh_type', 'veh_type.veh_type_id = accounts.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 = accounts.cust_type','left');
$this->db->join('employees', 'employees.id = accounts.referral','left');
$this->db->join('people', 'people.person_id = employees.person_id','left');
$this->db->join('stay', 'stay.booking_id = accounts.booking_id','left');
$this->db->join('diesel', 'diesel.diesel_op_id = accounts.operation_id','left');
$this->db->join('catering_orders', 'catering_orders.booking_id = accounts.booking_id','left');
$this->db->join('city frm', 'frm.city_id = accounts.orgin','left');
$this->db->join('city to', 'to.city_id = accounts.destination','left');
// $this->db->join('balance_log', 'balance_log.booking_id = operation.booking_id');
$this->db->join('vendor', 'vendor.vendor_id = vehicle.owned_by','left');
$this->db->where('DATE_FORMAT(sblt_accounts.from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$this->db->where('accounts_sts !=', '3');
$this->db->where('accounts.status',1);
//$this->db->group_by('accounts_id');
$this->db->group_by('accounts.booking_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_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_catering($booking_id) {
$this->db->select('IFNULL(sum(amount), 0) as amount');
$this->db->from('catering_orders');
$this->db->where('booking_id', $booking_id);
$this->db->order_by('order_date', 'asc');
return $this->db->get()->row();
}
public function get_stay($booking_id) {
$this->db->select('IFNULL(sum(stay_tot_amt), 0) as stay_tot_amt');
$this->db->from('stay');
$this->db->where('booking_id', $booking_id);
$this->db->order_by('stay_date', 'asc');
return $this->db->get()->row();
}
public function get_tax_percent($booking_id) {
$this->db->select('tax_precentage');
$this->db->from('operation');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
public function get_booking_amount($booking_id) {
$this->db->select('IFNULL(sum(booking_amount), 0) as booking_amount');
$this->db->from('operation');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
}
?>