<?php
class Ticket_sales_model extends CI_Model{
    public function get_packages(){
		$this->db->from('package');
		return $this->db->get()->result_array();
    }
	public function search($start_date,$end_date,$package_id,$type){
		if($type === "1"){
			$this->db->select('seat_capacity,ticketorder.orderid as order_id,passtripid as otripid,tripcode,ticketorder.packid,doj,count(sblttweb_passlist.passid) as nopass,IFNULL(sum(sblttweb_passlist.tariffamt),0) as tariffamt,IFNULL(sum(sblttweb_passlist.ticketamt),0) as ticketamt,IFNULL(sum(sblttweb_passlist.discountamt),0) as discountamt,IFNULL(sum(sblttweb_passlist.cashpaid),0) as cashpaid,IFNULL(sum(sblttweb_passlist.cashdue),0) as cashdue,package_title,passlist.discount,admin_discount,IFNULL(sum(sblttweb_passlist.admin_discountamt),0) as admn_discountamt,IFNULL(sum(irumudi_amount),0) as irumudi_amount'); 
		}else{
			$this->db->select('seat_capacity,ticketorderid as order_id,passtripid as otripid,tripcode,ticketorder.packid,ticketorder.doj,count(sblttweb_passlist.passid) as nopass,IFNULL(sum(sblttweb_passlist.tariffamt),0) as tariffamt,IFNULL(sum(sblttweb_passlist.ticketamt),0) as ticketamt,IFNULL(sum(sblttweb_passlist.discountamt),0) as discountamt,IFNULL(sum(sblttweb_passlist.cashpaid),0) as cashpaid,IFNULL(sum(sblttweb_passlist.cashdue),0) as cashdue,package_title,passlist.discount,admin_discount,IFNULL(sum(sblttweb_passlist.admin_discountamt),0) as admn_discountamt,IFNULL(sum(irumudi_amount),0) as irumudi_amount'); 
		}		  
		$this->db->from('vehicletrips');
		$this->db->join('package', 'package.package_id = vehicletrips.trippackid');
		$this->db->join('passlist', 'passlist.passtripid = vehicletrips.tripid');
		$this->db->join('ticketorder', 'ticketorder.orderid = passlist.ticketorderid');
		$this->db->join('vehicle', 'vehicle.vehicle_id = vehicletrips.vehicleid');
		$this->db->join('irumudi', 'irumudi.order_id = ticketorder.orderid','left');
		if($type === "0"){
			$this->db->join('cancelorder', 'cancelorder.orderid = passlist.cancelorderid');
			//$this->db->join('payment', 'payment.orderid = cancelorder.orderid');
		}
		$this->db->where('DATE_FORMAT(sblttweb_ticketorder.orderdate, "%Y-%m-%d")  BETWEEN '. $this->db->escape($start_date).' AND  '.$this->db->escape($end_date));
		if($package_id){
			$this->db->where_in('ticketorder.packid', $package_id);
		}		
		if($type === "1"){
			$this->db->group_start();
			$this->db->where('passlist.passstatus', 2)->or_where('passlist.passstatus', 0);

			$this->db->group_end();
		}else{
			$this->db->where('passlist.passstatus', 1);
		}		
		$this->db->where_in('ticketorder.orderstat', array('1','2','4'));			
		$this->db->group_by('vehicletrips.tripid');		  
		return $this->db->get()->result_array(); 
		//echo $this->db->last_query();
	}
	
}
?>