File: /home/cafsindia/allyindian_com/sbltt/application/models/Operation_model.php
<?php
class Operation_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('operation.operation_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,operation_sts,trip_details,balance_amount,booking_id,bus_count,GROUP_CONCAT(DISTINCT vehicle_no SEPARATOR ",") as vehicle_list,customers.phone_number,reporting_time');
$this->db->from('operation');
$this->db->join('operation_line', "operation_line.operation_id = operation.operation_id and operation_line.status = '1'",'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('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('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('vehicle.vehicle_no',$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();
/* GS changes
$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('operation.operation_sts', $sts);
}else{
$this->db->where('operation.operation_sts !=', '3');
}
$this->db->group_by('operation.operation_id');
$this->db->where('operation.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($operation_id){
$this->db->from('operation');
$this->db->where('operation_id',$operation_id);
$a=$this->db->get();
if($a->num_rows() === 1){
return $a->row();
}else{
foreach ($this->db->list_fields('operation') as $field){
$PersonObj->field= '';
}
return $PersonObj;
}
}
public function view_data($operation_id){
/* UDY changes form total trip amount
$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('operation_id',$operation_id);
*/
$this->db->select("*,frm.city_name as frm_city_name, to.city_name as to_city_name,IFNULL(sum(stay_tot_amt),0) as stay_tot,IFNULL(sum(sblt_catering_orders.amount),0) as food_tot,(SELECT IFNULL(sum(sblt_balance_log.amount), 0) from sblt_operation left join sblt_balance_log on sblt_balance_log.booking_id = sblt_operation.booking_id where operation_id = '$operation_id') as paid_tot");
$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->join('veh_category', 'veh_category.veh_cat_id = veh_type.veh_category','left');
$this->db->join('stay', 'stay.booking_id = operation.booking_id','left');
$this->db->join('catering_orders', 'catering_orders.booking_id = operation.booking_id','left');
//$this->db->join('balance_log', 'balance_log.booking_id = operation.booking_id','left');
$this->db->where('operation_id',$operation_id);
return $this->db->get()->row();
//echo $this->db->last_query();
}
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 get_demand_date($demand_month){
$this->db->from('demand_date');
$this->db->where('demand_month',$demand_month);
return $this->db->get()->row();
}
public function preferred_vehicle_list($vehicle_ids){
$this->db->from('vehicle');
$this->db->where_in('vehicle_id',$vehicle_ids);
return $this->db->get()->result();
}
public function get_driver() {
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->join('role', 'employees.role = role.role_id');
$this->db->where('status', 1);
$this->db->where('role', 7);
return $this->db->get()->result();
}
public function get_cleaner() {
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->join('role', 'employees.role = role.role_id');
$this->db->where('status', 1);
$this->db->where('role', 11);
return $this->db->get()->result();
}
public function get_driver_info($driver_id) {
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->join('role', 'employees.role = role.role_id');
$this->db->where('status', 1);
$this->db->where('employees.id', $driver_id);
return $this->db->get()->row();
}
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_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 remove_op_line($op_line_id){
$update_data = array(
'status' => '0',
'deleted_by' => $this->session->userdata('emp_id'),
'deleted_date' => date("Y-m-d H:i:s"),
);
$this->db->where('op_line_id', $op_line_id);
return $this->db->update('operation_line', $update_data);
}
public function update_sts($mode,$operation_id,$booking_id){
if($mode === "CHK"){
$update_data = array(
'operation_sts' => '4',
'updated_by' => $this->session->userdata('emp_id'),
'updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('booking_id', $booking_id);
$this->db->update('booking', array('booking_sts'=>'4'));
$this->db->where('operation_id', $operation_id);
$this->db->update('operation', $update_data);
$this->db->where('operation_id', $operation_id);
$this->db->update('operation_line', array('driver_sts'=>'3','updated_by' => $this->session->userdata('emp_id'),'updated_date' => date("Y-m-d H:i:s")));
$this->db->where('booking_id', $booking_id);
$this->db->update('accounts', $update_data);
}else
if($mode === "UNCHK"){
$update_data = array(
'operation_sts' => '5',
'updated_by' => $this->session->userdata('emp_id'),
'updated_date' => date("Y-m-d H:i:s"),
);
$this->db->where('booking_id', $booking_id);
$this->db->update('booking', array('booking_sts'=>'5'));
$this->db->where('operation_id', $operation_id);
$this->db->update('operation', $update_data);
$this->db->where('booking_id', $booking_id);
$this->db->update('accounts', $update_data);
}
}
public function get_vehicle_infromation($vehicle_id){
$this->db->from('vehicle');
$this->db->join('employees', 'employees.id = vehicle.driver_name');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->where('vehicle_id',$vehicle_id);
$this->db->where('status',1);
return $this->db->get()->row();
}
public function get_vehicle_tax($vehicle_id){
$query = $this->db->query("SELECT * FROM `sblt_doc` where doc_type = 'tax' and vehicle_id = '$vehicle_id' ORDER BY `sblt_doc`.`doc_to` DESC limit 1");
return $query->row();
}
public function get_vehicle_tp($vehicle_id){
$query = $this->db->query("SELECT * FROM `sblt_doc` where doc_type = 'TP' and vehicle_id = '$vehicle_id' ORDER BY `sblt_doc`.`doc_to` DESC limit 1");
return $query->row();
}
public function exists($op_line_data){
$operation_id = $op_line_data['operation_id'];
$op_vehicle_id = $op_line_data['op_vehicle_id'];
$this->db->from('operation_line');
$this->db->where('operation_id', $operation_id);
$this->db->where('op_vehicle_id', $op_vehicle_id);
$this->db->where('operation_line.status',1);
return $this->db->get()->num_rows();
}
public function get_upd_info($op_line_data){
$operation_id = $op_line_data['operation_id'];
$op_vehicle_id = $op_line_data['op_vehicle_id'];
$this->db->from('operation_line');
$this->db->where('operation_id', $operation_id);
$this->db->where('op_vehicle_id', $op_vehicle_id);
$this->db->where('operation_line.status',1);
$line_info = $this->db->get()->row();
$op_line_id = $line_info->op_line_id;
return $op_line_id;
}
public function save_op_line($op_line_data,$op_line_id){
$operation_id = $op_line_data['operation_id'];
$count = $this->exists($op_line_data);
if($count === 0){
$this->db->insert('operation_line', $op_line_data);
$op_line_id = $this->db->insert_id();
$operation_data['op_line_id'] = $op_line_id;
$this->update_operation_chart_by_id($op_line_id,"CHK");
}else{
$op_line_id = $this->get_upd_info($op_line_data);
$this->update_operation_chart_by_id($op_line_id,"UNCHK");
$this->db->where('op_line_id', $op_line_id);
$this->db->update('operation_line', $op_line_data);
$this->update_operation_chart_by_id($op_line_id,"CHK");
}
return $op_line_id;
}
public function save_driver_suspence($driver_suspence_data, $driver_suspence_id){
if($driver_suspence_id === ""){
$this->db->insert('driver_suspence', $driver_suspence_data);
return $operation_data['driver_suspence_id'] = $this->db->insert_id();
}else{
$this->db->where('driver_suspence_id', $driver_suspence_id);
return $this->db->update('driver_suspence', $driver_suspence_data);
}
}
public function get_driver_suspence($driver_suspence_id){
$this->db->from('driver_suspence');
$this->db->where('driver_suspence_id',$driver_suspence_id);
$this->db->where('driver_suspence.status',1);
return $this->db->get()->result();
}
public function driver_suspence_list($driver_suspence_op_id){
$this->db->from('driver_suspence');
$this->db->join('vehicle', 'vehicle.vehicle_id = driver_suspence.driver_suspence_vch_id','left');
$this->db->where('driver_suspence_op_id',$driver_suspence_op_id);
$this->db->where('driver_suspence.status',1);
$this->db->order_by('driver_suspence.driver_suspence_vch_id','asc');
return $this->db->get()->result();
}
public function save_suspence($suspence_data, $suspence_id,$suspence_book_id){
if($suspence_id === ""){
$this->db->insert('suspence', $suspence_data);
$operation_data['suspence_id'] = $this->db->insert_id();
$this->update_total_trip_amount($suspence_book_id);
return $operation_data;
}else{
$this->db->where('suspence_id', $suspence_id);
$this->db->update('suspence', $suspence_data);
return $this->update_total_trip_amount($suspence_book_id);
}
}
public function suspence_list($suspence_op_id){
$this->db->from('suspence');
$this->db->join('vehicle', 'vehicle.vehicle_id = suspence.suspence_vch_id','left');
$this->db->where('suspence_op_id',$suspence_op_id);
$this->db->where('suspence.status',1);
$this->db->order_by('suspence.suspence_vch_id','asc');
return $this->db->get()->result();
}
public function suspence_subtotal($suspence_op_id){
$this->db->select('vehicle_no,IFNULL(sum(suspence_amt), 0) as amt');
$this->db->from('suspence');
$this->db->join('vehicle', 'vehicle.vehicle_id = suspence.suspence_vch_id','left');
$this->db->where('suspence_op_id',$suspence_op_id);
$this->db->where('suspence.status',1);
$this->db->group_by('suspence.suspence_vch_id');
$this->db->order_by('suspence.suspence_vch_id','asc');
return $this->db->get()->result();
}
public function get_suspence_amount($suspence_op_id) {
$this->db->select('IFNULL(sum(suspence_amt), 0) as suspence_amount');
$this->db->from('suspence');
$this->db->where('suspence_op_id', $suspence_op_id);
return $this->db->get()->row();
}
public function get_diesel_amount($diesel_op_id) {
$this->db->select('IFNULL(sum(diesel_amt), 0) as diesel_amount');
$this->db->from('diesel');
$this->db->where('diesel_op_id', $diesel_op_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 get_suspence($suspence_id){
$this->db->from('suspence');
$this->db->where('suspence_id',$suspence_id);
$this->db->where('suspence.status',1);
return $this->db->get()->result();
}
public function save_diesel($diesel_data, $diesel_id){
if($diesel_id === ""){
$this->db->insert('diesel', $diesel_data);
return $operation_data['diesel_id'] = $this->db->insert_id();
}else{
$this->db->where('diesel_id', $diesel_id);
return $this->db->update('diesel', $diesel_data);
}
}
public function diesel_list($diesel_op_id){
$this->db->from('diesel');
$this->db->join('vehicle', 'vehicle.vehicle_id = diesel.diesel_vch_id','left');
$this->db->where('diesel_op_id',$diesel_op_id);
$this->db->where('diesel.status',1);
$this->db->order_by('diesel.diesel_vch_id','asc');
return $this->db->get()->result();
}
public function diesel_subtotal($diesel_op_id){
$this->db->select('vehicle_no,IFNULL(sum(diesel_amt), 0) as amt');
$this->db->from('diesel');
$this->db->join('vehicle', 'vehicle.vehicle_id = diesel.diesel_vch_id','left');
$this->db->where('diesel_op_id',$diesel_op_id);
$this->db->where('diesel.status',1);
$this->db->group_by('diesel.diesel_vch_id');
$this->db->order_by('diesel.diesel_vch_id','asc');
return $this->db->get()->result();
}
public function get_diesel($diesel_id){
$this->db->from('diesel');
$this->db->where('diesel_id',$diesel_id);
$this->db->where('diesel.status',1);
return $this->db->get()->result();
}
public function maintenance_exists($maintenance_op_id){
$this->db->from('maintenance');
$this->db->where('op_id', $maintenance_op_id);
return $this->db->get()->num_rows();
}
public function save_maintenance($maintenance_data,$maintenance_op_id){
$count = $this->maintenance_exists($maintenance_op_id);
if($count === 0){
$this->db->insert('maintenance', $maintenance_data);
return $operation_data['maintenance_data'] = $this->db->insert_id();
}else{
$this->db->where('op_id', $maintenance_op_id);
return $this->db->update('maintenance', $maintenance_data);
}
}
public function save_extra_km($extra_data){
$extra_vch_id = $extra_data['extra_vch_id'];
$op_id = $extra_data['extra_op_id'];
$booking_id = $extra_data['extra_booking_id'];
$op_line_data = array(
'hire_charges' => $extra_data['hire_charges'],
'trip_start_km' => $extra_data['trip_start_km'],
'trip_end_km' => $extra_data['trip_end_km'],
'op_extra_km' => $extra_data['op_extra_km'],
'op_extra_pre_km' => $extra_data['op_extra_pre_km'],
'op_extra_amt' => $extra_data['op_extra_amt'],
);
//print_r($op_line_data);
$this->db->where('operation_id', $op_id);
$this->db->where('op_vehicle_id', $extra_vch_id);
$this->db->update('operation_line', $op_line_data);
$op_line_info = $this->get_op_line_extra_amount($op_id);
$extra_km = $op_line_info->extra_km;
$extra_amt = $op_line_info->extra_amt;
$op_data = array(
'extra_amt' => $extra_amt,
'extra_km' => $extra_km,
);
$this->db->where('operation_id', $op_id);
$this->db->update('operation', $op_data);
$balance_amount = $this->get_total_trip_amount($booking_id);
//$new_balance = (int)$extra_amt + (int)$balance_amount; // UDY COMMENTED DUE TO BOOKING ID MISSING
$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_op_line_extra_amount($op_id) {
$this->db->select('IFNULL(sum(op_extra_km), 0) as extra_km,IFNULL(sum(op_extra_amt), 0) as extra_amt');
$this->db->from('operation_line');
$this->db->where('operation_id', $op_id);
$this->db->where('status', 1);
return $this->db->get()->row();
}
public function maintenance_list($maintenance_op_id){
$this->db->from('maintenance');
$this->db->join('vehicle', 'vehicle.vehicle_id = maintenance.vehicle_id','left');
$this->db->where('op_id',$maintenance_op_id);
$this->db->where('maintenance.status',1);
return $this->db->get()->result();
}
public function get_maintenance($maintenance_id){
$this->db->from('maintenance');
$this->db->where('maintenance_id',$maintenance_id);
$this->db->where('maintenance.status',1);
return $this->db->get()->result();
}
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 feedback_list($operation_id){
$this->db->from('feed_back');
$this->db->join('vehicle', 'vehicle.vehicle_id = feed_back.feed_vch_id','left');
$this->db->where('feed_op_id',$operation_id);
$this->db->where('feed_back.status',1);
return $this->db->get()->result();
}
public function update_operation_chart_by_id($op_line_id,$mode){
$trip_info = $this->get_trip_info($op_line_id);
$from_date = $trip_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 = $trip_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 = $trip_info->from_date;
$db_to = $trip_info->to_date;
$vehicle_id = $trip_info->op_vehicle_id;
$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 = 'OPERATION' 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 = 'OPERATION' and chart_vehicle_id = $vehicle_id");
}
return;
}
public function get_tax_info($op_line_id){
$this->db->from('operation_line');
$this->db->where('op_line_id',$op_line_id);
$op_line = $this->db->get()->row();
$vehicle_id = $op_line->op_vehicle_id;
$this->db->from('doc');
$this->db->where('doc_type',"Tax");
$this->db->where('vehicle_id',$vehicle_id);
$this->db->order_by('doc_to', 'desc');
return $this->db->get()->row();
}
public function get_trip_info($op_line_id){
$this->db->select('*,frm.city_name as frm_city_name, to.city_name as to_city_name,operation_line.tp_info as rout_info,operation_line.remarks as op_remarks');
$this->db->from('operation_line');
$this->db->join('operation', 'operation.operation_id = operation_line.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('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->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
$this->db->where('op_line_id',$op_line_id);
$this->db->where('operation_line.status',1);
return $this->db->get()->row();
}
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 get_op_line($op_line_id){
$this->db->from('operation_line');
$this->db->where('op_line_id',$op_line_id);
$this->db->where('operation_line.status',1);
return $this->db->get()->row();
}
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_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();
}
//@gs 19 june 2018
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();
}
}
?>