File: /home/cafsindia/allyindian_com/sbltt/application/models/Accounts_model.php
<?php
class Accounts_model extends CI_Model{
public function search($search,$filters,$rows = 0,$limit_from= 0,$sort='accounts_id',$order='asc'){
$start_date = $filters['start_date'];
$end_date = $filters['end_date'];
if(!$sort){
$sort = "from_date";
}
$this->db->select('accounts.accounts_id,accounts.tour_no,customer_name,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.balance_amount,accounts.booking_id,operation_sts,inv_number,booking.catering_amount as foodamt,booking.stay_amount as stayamt,includ_gst,accounts.bus_count,phone_number');
$this->db->from('accounts');
$this->db->join('booking', 'booking.booking_id = accounts.booking_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('booking_type', 'booking_type.booking_type_id = accounts.cust_type','left');
$this->db->join('city frm', 'frm.city_id = accounts.orgin','left');
$this->db->join('city to', 'to.city_id = accounts.destination','left');
if($search){
$this->db->group_start();
$this->db->like('accounts.operation_id',$search);
$this->db->or_like('accounts.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('accounts.from_date', date('Y-m-d',strtotime($search)));
$this->db->or_like('accounts.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))->or_where('DATE_FORMAT(sblt_accounts.to_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND
'.$this->db->escape($end_date));
*/
$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();
$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('accounts_sts', $sts);
}else{
$this->db->where('accounts_sts !=', '3');
}
$this->db->where('accounts.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_info($accounts_id){
$this->db->select("*,frm.city_name as frm_city_name, to.city_name as to_city_name,(SELECT IFNULL(SUM(stay_tot_amt), 0) FROM sblt_stay INNER JOIN sblt_accounts ON
sblt_stay.booking_id = sblt_accounts.booking_id WHERE accounts_id = '$accounts_id' ) as stay_tot,IFNULL(sum(sblt_catering_orders.amount),0) as food_tot,(SELECT IFNULL(sum(sblt_balance_log.amount), 0) from sblt_accounts left join sblt_balance_log on sblt_balance_log.booking_id = sblt_accounts.booking_id where accounts_id = '$accounts_id') as paid_tot");
$this->db->from('accounts');
$this->db->join('operation', 'operation.operation_id = accounts.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('booking_type', 'booking_type.booking_type_id = accounts.cust_type','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('state', 'state_code = customers.state','left');
$this->db->join('veh_category', 'veh_category.veh_cat_id = veh_type.veh_category','left');
$this->db->join('stay', 'stay.booking_id = accounts.booking_id','left');
$this->db->join('catering_orders', 'catering_orders.booking_id = accounts.booking_id','left');
//$this->db->join('balance_log', 'balance_log.booking_id = accounts.booking_id','left');
$this->db->where('accounts_id',$accounts_id);
$a=$this->db->get();
if($a->num_rows() === 1){
return $a->row();
}else{
foreach ($this->db->list_fields('accounts') as $field){
$PersonObj->field= '';
}
return $PersonObj;
}
}
public function trip_summary($operation_id){
$this->db->from('operation_line');
$this->db->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
$this->db->where('operation_id',$operation_id);
$this->db->where('operation_line.status',1);
$op_line_info = $this->db->get()->result();
$trip_data = array();
$trip_info = array();
foreach($op_line_info as $op_line){
$operation_id = $op_line->operation_id;
$op_vehicle_id = $op_line->op_vehicle_id;
$vehicle_no = $op_line->vehicle_no;
$op_driver_name = $op_line->op_driver_name;
$op_suspence_amt = $op_line->op_suspence_amt;
$driver_sts = $op_line->driver_sts;
$trip_info['op_vehicle_id'] = $op_vehicle_id;
$trip_info['vehicle_no'] = $vehicle_no;
$trip_info['op_driver_name'] = $op_driver_name;
$this->db->select('IFNULL(sum(driver_suspence_amount), 0) as driver_susp_amt');
$this->db->from('driver_suspence');
$this->db->where('driver_suspence_op_id',$operation_id);
$this->db->where('driver_suspence_vch_id',$op_vehicle_id);
$this->db->where('status',1);
$driver_suspence_info = $this->db->get()->row();
$driver_susp_amt = $driver_suspence_info->driver_susp_amt;
$trip_info['op_suspence_amt'] = (int)$op_suspence_amt + (int)$driver_susp_amt;
$this->db->select('IFNULL(sum(diesel_amt), 0) as diesel_amt');
$this->db->from('diesel');
$this->db->where('diesel_op_id',$operation_id);
$this->db->where('diesel_vch_id',$op_vehicle_id);
$this->db->where('status',1);
$diesel_info = $this->db->get()->row();
$diesel_amt = $diesel_info->diesel_amt;
$trip_info['diesel_amt'] = $diesel_amt;
$this->db->select('suspence_info,IFNULL(sum(suspence_amt), 0) as suspence_amt');
$this->db->from('suspence');
$this->db->where('suspence_op_id',$operation_id);
$this->db->where('suspence_vch_id',$op_vehicle_id);
$this->db->where('status',1);
$this->db->group_by('suspence_info');
$suspence_info = $this->db->get()->result();
$trip_info['toll'] = 0;
$trip_info['parking'] = 0;
$trip_info['kickbacks'] = 0;
$trip_info['permit'] = 0;
$trip_info['rto'] = 0;
$trip_info['tax'] = 0;
$trip_info['driver'] = 0;
$trip_info['cleaner'] = 0;
$trip_info['balance_received'] = 0;
$trip_info['others'] = 0;
foreach($suspence_info as $suspence){
$suspence_info = $suspence->suspence_info;
$suspence_amt = $suspence->suspence_amt;
if(strtoupper($suspence_info) === "TOLL"){
$trip_info['toll'] = $suspence_amt;
}
if(strtoupper($suspence_info) === "PARKING"){
$trip_info['parking'] = $suspence_amt;
}
if(strtoupper($suspence_info) === "KICKBACKS"){
$trip_info['kickbacks'] = $suspence_amt;
}
if(strtoupper($suspence_info) === "PERMIT"){
$trip_info['permit'] = $suspence_amt;
}
if(strtoupper($suspence_info) === "RTO"){
$trip_info['rto'] = $suspence_amt;
}
if(strtoupper($suspence_info) === "TAX"){
$trip_info['tax'] = $suspence_amt;
}
if(strtoupper($suspence_info) === "DRIVER"){
$trip_info['driver'] = $suspence_amt;
}
if(strtoupper($suspence_info) === "CLEANER"){
$trip_info['cleaner'] = $suspence_amt;
}
if(strtoupper($suspence_info) === "BALANCE_RECEIVED"){
$trip_info['balance_received'] = $suspence_amt;
}
if(strtoupper($suspence_info) === "OTHERS"){
$trip_info['others'] = $suspence_amt;
}
}
$trip_data[] = $trip_info;
}
return $trip_data;
}
public function get_op_line_list($operation_id){
$this->db->from('operation_line');
$this->db->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
$this->db->join('veh_type', 'veh_type.veh_type_id = operation_line.op_vehicle_type','left');
$this->db->where('operation_id',$operation_id);
$this->db->where('operation_line.status',1);
return $this->db->get()->result();
}
public function update_accounts(&$accounts_data, $accounts_id = FALSE){
$this->db->where('accounts_id', $accounts_id);
return $this->db->update('accounts', $accounts_data);
}
public function get_balance_info($booking_id){
$this->db->from('accounts');
$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('accounts') as $field){
$PersonObj->field= '';
}
return $PersonObj;
}
}
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 balance_amount($booking_id) {
$this->db->from('accounts');
$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));
*/
return $this->update_total_trip_amount($booking_id);
}else{
$this->db->insert('balance_log', $balance_data);
return $this->update_total_trip_amount($booking_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));
$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 update_total_trip_amount($booking_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));
$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_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_amount;
$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_list($operation_id){
$this->db->from('suspence');
$this->db->where('suspence.status',1);
$this->db->where('suspence.suspence_op_id',$operation_id);
return $this->db->get()->result();
}
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('accounts');
$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('veh_category', 'veh_category.veh_cat_id = veh_type.veh_category','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = accounts.cust_type','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('state', 'state_code = customers.state','left');
$this->db->where('booking_id',$booking_id);
return $this->db->get()->result();
}
public function get_load_data_print($booking_id){
$group_id = $this->get_group_id($booking_id);
$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name');
$this->db->from('accounts');
$this->db->join('operation', 'operation.operation_id = accounts.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('veh_category', 'veh_category.veh_cat_id = veh_type.veh_category','left');
$this->db->join('booking_type', 'booking_type.booking_type_id = accounts.cust_type','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('state', 'state_code = customers.state','left');
if($group_id){
$this->db->where('group_id',$group_id);
}else{
$this->db->where('accounts.booking_id',$booking_id);
}
return $this->db->get()->result();
}
public function get_group_id($booking_id) {
$this->db->from('accounts');
$this->db->where('booking_id',$booking_id);
$result = $this->db->get()->row();
$group_id = $result->group_id;
if($group_id !== 0){
return $group_id;
}
return false;
}
public function get_booking_ids($booking_id){
$group_id = $this->get_group_id($booking_id);
if($group_id){
$qry = $this->db->query("SELECT GROUP_CONCAT(booking_id) as booking_ids FROM sblt_accounts where group_id = '$group_id'");
$qry_rslt = $qry->row();
return $qry_rslt->booking_ids;
}
return $booking_id;
}
public function get_catering_order($booking_id) {
$this->db->from('catering_orders');
$this->db->where("booking_id IN (".$booking_id.")",NULL, false);
$this->db->order_by('order_date', 'asc');
return $this->db->get()->result();
}
public function get_stay_list($booking_id) {
$this->db->from('stay');
$this->db->where("booking_id IN (".$booking_id.")",NULL, false);
$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_extra_amount($booking_id) {
$this->db->select('IFNULL(extra_amt, 0) as extra_amount,extra_km');
$this->db->from('operation');
$this->db->where('booking_id', $booking_id);
return $this->db->get()->row();
}
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 vehicle_list($operation_id){
$this->db->select('GROUP_CONCAT(DISTINCT vehicle_no SEPARATOR ",") as vch_list');
$this->db->from('operation_line');
$this->db->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
$this->db->where('operation_id',$operation_id);
$this->db->group_by('operation_line.operation_id');
return $this->db->get()->result();
}
public function delete_list($accounts_id){
$this->db->where_in('accounts_id',$accounts_id);
return $this->db->update('accounts',array('status'=>0,'deleted_by'=>$this->session->userdata('emp_id'),'deleted_date'=>date('Y-m-d H:i:s')));
}
public function get_hsn($tax_precentage) {
$this->db->from('hsn');
$this->db->where('igst',$tax_precentage);
return $this->db->get()->row();
}
public function group_inv($inv_date,$inv_number,$ids) {
$string_ids = implode(",",$ids);
$qry = $this->db->query("SELECT group_id FROM sblt_accounts where accounts_id in ($string_ids) GROUP by group_id");
$qry_rslt = $qry->result();
foreach($qry_rslt as $rslt){
$group_id = $rslt->group_id;
if($group_id !== 0){
$this->db->where('group_id', $group_id);
$this->db->update('accounts', array('group_id'=>"",'inv_date'=>"",'inv_number'=>""));
}
}
$group_id_qry = $this->db->query("SELECT DISTINCT(group_id) FROM sblt_accounts where group_id != '0' GROUP by group_id");
$group_count = $group_id_qry->num_rows();
if(!$group_count){
$group_count = 1;
}else{
$group_count = $group_count + 1;
}
$this->db->where("accounts_id IN (".$string_ids.")",NULL, false);
return $this->db->update('accounts', array('group_id'=>$group_count,'inv_date'=>$inv_date,'inv_number'=>$inv_number));
}
public function get_multiple_info($cat_ids){
$this->db->from('accounts');
$this->db->where_in('accounts_id', $cat_ids);
return $this->db->get()->result_array();
}
}
?>