<?php
/**
* @version		3.0.0
* @package		JomLand TicketCenter
* @copyright	Copyright (C) 2010 JomLand.com. All rights reserved.
* @license		GNU/GPLv3 http://www.gnu.org/licenses/gpl-3.0.html
*/
defined('_JEXEC') or die();

jimport( 'joomla.application.component.model' );

class JLTicketCenterModelEvent extends JModelLegacy
{
	
	function getEvents(){
		
		$db = JFactory::getDBO();
		$pparams = new JLTicketCenterHelperSetup;
		
		$where_array = array();

		$join ='';
		
		if (JRequest::getInt('city',0)>0){
			$where_array[] = 'e.`city_id`='.JRequest::getInt('city',0);
			
			$join .= " LEFT JOIN #__jltcenter_city city  ON city.`id`=e.`city_id` ";
			$join .= " LEFT JOIN #__jltcenter_venue v  ON v.`id`=e.`venue_id` ";
			$join .= " LEFT JOIN #__jltcenter_state state  ON state.`id`=city.`state_id` ";
			$join .= " LEFT JOIN #__jltcenter_country country ON country.`id`=city.`country_id` ";
			
		}
		elseif (JRequest::getInt('state',0)>0){
			$where_array[] = 'e.`state_id`='.JRequest::getInt('state',0);
			
			$join .= " LEFT JOIN #__jltcenter_city city  ON city.`id`=e.`city_id` ";
			$join .= " LEFT JOIN #__jltcenter_venue v  ON v.`id`=e.`venue_id` ";
			$join .= " LEFT JOIN #__jltcenter_state state  ON state.`id`=e.`state_id` ";
			$join .= " LEFT JOIN #__jltcenter_country country ON country.`id`=e.`country_id` ";
			
		}elseif (JRequest::getInt('country',0)>0){
			$where_array[] ='e.`country_id`='.JRequest::getInt('country',0);
			$join .= " LEFT JOIN #__jltcenter_city city  ON city.`id`=e.`city_id` ";
			$join .= " LEFT JOIN #__jltcenter_venue v  ON v.`id`=e.`venue_id` ";
			$join .= " LEFT JOIN #__jltcenter_state state  ON state.`id`=e.`state_id` ";
			$join .= " LEFT JOIN #__jltcenter_country country ON country.`id`=e.`country_id` ";
			
		}elseif (JRequest::getInt('venue',0)>0){
			$where_array[] ='e.`venue_id`='.JRequest::getInt('venue',0);
			$join .= " LEFT JOIN #__jltcenter_venue v  ON v.`id`=e.`venue_id` ";
			$join .= " LEFT JOIN #__jltcenter_city city  ON city.`id`=v.`city_id` ";
			$join .= " LEFT JOIN #__jltcenter_state state  ON state.`id`=v.`state_id` ";
			$join .= " LEFT JOIN #__jltcenter_country country ON country.`id`=v.`country_id` ";
		}
		elseif (JRequest::getInt('performer',0)>0){
			$where_array[] = 'e.`performer_id`='.JRequest::getInt('performer',0);
			
			$join .= " LEFT JOIN #__jltcenter_city city  ON city.`id`=e.`city_id` ";
			$join .= " LEFT JOIN #__jltcenter_venue v  ON v.`id`=e.`venue_id` ";
			$join .= " LEFT JOIN #__jltcenter_state state  ON state.`id`=e.`state_id` ";
			$join .= " LEFT JOIN #__jltcenter_country country ON country.`id`=e.`country_id` ";
		}		
		elseif (JRequest::getInt('cat_id',0)>0){
			$where_array[] = 'p.`cat_id`='.JRequest::getInt('cat_id',0);
			
			$join .= " LEFT JOIN #__jltcenter_city city  ON city.`id`=e.`city_id` ";
			$join .= " LEFT JOIN #__jltcenter_venue v  ON v.`id`=e.`venue_id` ";
			$join .= " LEFT JOIN #__jltcenter_state state  ON state.`id`=e.`state_id` ";
			$join .= " LEFT JOIN #__jltcenter_country country ON country.`id`=e.`country_id` ";
		}else{
			$join .= " LEFT JOIN #__jltcenter_city city  ON city.`id`=e.`city_id` ";
			$join .= " LEFT JOIN #__jltcenter_venue v  ON v.`id`=e.`venue_id` ";
			$join .= " LEFT JOIN #__jltcenter_state state  ON state.`id`=e.`state_id` ";
			$join .= " LEFT JOIN #__jltcenter_country country ON country.`id`=e.`country_id` ";
		}
		
		$where_array[] ='e.`published`=1';
		$where_array[] ='e.`event_date`>NOW()';
		
				
		$query = 'SELECT e.*,p.`performer_name`,v.`venue_name`,v.`address`,city.`city_name`,state.`state_name`,country.`country`  AS `country`, cat.`title` as `cat_name`,p.`cat_id`,cat.`description` as `cat_description` ';
		$query .= ' FROM #__jltcenter_events e';
		$query .= " LEFT JOIN #__jltcenter_performer p  ON p.`id`=e.`performer_id` ";
		$query .= " LEFT JOIN #__categories cat ON cat.`id`=p.`cat_id`";
		$query .= $join;
		
		if(JRequest::getInt('jltcenter_feature',0)){
			$where_array[] ='e.`feature`=1'; 
		}
		
		if($pparams->get('hide_soldout')==1){
			// Add stock check
			$hide_query= "SELECT e.id
						,(SELECT SUM(tt.`tickets`)
						FROM #__jltcenter_events_ticket_type tt
						WHERE tt.`event_id` = e.`id`) AS `tickets_avail`,
						(SELECT SUM(tt.`tickets_sold`)
						FROM #__jltcenter_events_ticket_type tt
						WHERE tt.`event_id` = e.`id`) AS `sum_tickets_sold`,
						(SELECT SUM(tt.`tickets_ordered`)
						FROM #__jltcenter_events_ticket_type tt
						WHERE tt.`event_id` = e.`id`) AS `sum_tickets_ordered`";
			$hide_query.= ' FROM #__jltcenter_events e';			
			
			if (JRequest::getInt('cat_id',0)>0){
				$query .= " LEFT JOIN #__jltcenter_performer p  ON p.`id`=e.`performer_id` ";
				$query .= " LEFT JOIN #__categories cat ON cat.`id`=p.`cat_id`";
			}
			
			$hide_query.=" WHERE ".implode(' AND ',$where_array);

			if (JRequest::getInt('cat_id',0)>0){
				$hide_query.= ' AND p.`cat_id`='.JRequest::getInt('cat_id',0);	
			}
			
			$db->setQuery($hide_query);
			
			$sold_out_events_test = $db->loadObjectList();
			if (!empty($sold_out_events_test )){
				$sold_out_event_ids = array();
				foreach ($sold_out_events_test as $sold_event ){
					if (0 >= $sold_event->tickets_avail - $sold_event->sum_tickets_sold - $sold_event->sum_tickets_ordered ){
						$sold_out_event_ids[] = $sold_event->id;
					}
				}
				if (!empty($sold_out_event_ids)){
					$where_array[] = "NOT(e.id IN (".implode(',',$sold_out_event_ids)."))";
				}
			}
		}
						
		$query .=" WHERE ".implode(' AND ',$where_array);
		
		switch (JRequest::getInt('jltcenter_order_by',0)){
			case 1: //Popular
				$query .=" ORDER BY `hits`,`event_date`";
			break;
			
			default:
				$query .=" ORDER BY `event_date`";
			break;
		}
		
		if (JRequest::getInt('num_of_event',0)>0){
			$query .=" LIMIT ".JRequest::getInt('num_of_event');
		}
		
		$db->setQuery($query);
		//echo $db->getQuery();
		$events_list = $db->loadObjectList();
		
		return $events_list; 
	}//EOF 
	
	function getPerformer(){
		$db = JFactory::getDBO();
		
		$query = " SELECT p.*, cat.`title` as `cat_title` "
			. " FROM #__jltcenter_performer p "
			. " LEFT JOIN #__categories cat ON cat.`id`=p.`cat_id` AND cat.`extension` = 'com_jlticketcenter'"
			. " WHERE p.`id`=" .JRequest::getInt('performer',0);
		
		$db->setQuery($query);
			
		$performer = $db->loadObject();
		
		return $performer;
	}
	
	public function getVenue(){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		 
		$query->select('v.*');
		$query->from('#__jltcenter_venue v');
		
		$query->select('city.`city_name`');
		$query->join('LEFT', '#__jltcenter_city city ON city.`id`=v.`city_id`');
		
		$query->select('s.`state_name`');
		$query->join('LEFT', '#__jltcenter_state s ON s.`id`=v.`state_id`');
		
		$query->select('c.`country`');
		$query->join('LEFT', '#__jltcenter_country c ON c.`id`=v.`country_id`');
		
		$query->where('v.`id`='.JRequest::getInt('venue',0));
		$db->setQuery($query);
		
		$venue = $db->loadObject();
		
		return $venue;
	}//EOF getVenue
	
	public function getCity(){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		 
		$query->select('city.*,city.`id` as `city_id`');
		$query->from('#__jltcenter_city city');
		
		$query->select('s.`state_name`');
		$query->join('LEFT', '#__jltcenter_state s ON s.`id`=city.`state_id`');
		
		$query->select('c.`country`');
		$query->join('LEFT', '#__jltcenter_country c ON c.`id`=city.`country_id`');
		
		$query->where('city.`id`='.JRequest::getInt('city',0));
		$db->setQuery($query);
		
		$city = $db->loadObject();
		
		return $city;
	}//EOF getCity
	
	public function getState($property = null, $default = null){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		 
		$query->select('s.*,s.`id` as `state_id`');
		$query->from('#__jltcenter_state s');
		
		$query->select('c.`country`');
		$query->join('LEFT', '#__jltcenter_country c ON c.`id`=s.`country_id`');
		
		$query->where('s.`id`='.JRequest::getInt('state',0));
		$db->setQuery($query);
		
		$state = $db->loadObject();
		
		return $state;
	}//EOF getState
	
	
	public function getCountry(){
		$db = JFactory::getDBO();
		$query = $db->getQuery(true);
		 
		$query->select('c.*,c.`id` as `country_id`');
		$query->from('#__jltcenter_country c');
		
		$query->where('c.`id`='.JRequest::getInt('country',0));
		$db->setQuery($query);
		
		$country = $db->loadObject();
		
		return $country;
	}//EOF getState
	
	
	
}//EOC