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

// No direct access
defined( '_JEXEC' ) or die( 'Restricted access' );

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

class JLTicketCenterModelEvents extends JModelLegacy
{
	var $_data;
	var $_total = null;
	var $_pagination = null;

	function __construct()
	{
		parent::__construct();

		$mainframe = JFactory::getApplication();

		// Get pagination request variables
		$limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		$limitstart = JRequest::getVar('limitstart', 0, '', 'int');

		// In case limit has been changed, adjust it
		$limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);

		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);

		// Ordering
		$context			= 'com_jlticketcenter.events.list.';
		$filter_order     = $mainframe->getUserStateFromRequest($context.'filter_order','filter_order','e.event_date','cmd');
		$filter_order_Dir = $mainframe->getUserStateFromRequest($context.'filter_order_Dir','filter_order_Dir','asc','word');

		$this->setState('filter_order', $filter_order);
		$this->setState('filter_order_Dir', $filter_order_Dir);
	}


	/**
	 * Returns the query
	 * @return string The query to be used to retrieve the rows from the database
	 */
	function _buildQuery()
	{
		$filter = JRequest::getVar( 'filter', null );// LOWER;

		$search =$filter['search'];
		if ($search){
			$search = strtolower($search);
			$where[] = "(LOWER(e.`event_name`) LIKE '%".$search."%' OR LOWER(p.`performer_name`) LIKE '%".$search."%' OR LOWER(v.`venue_name`) LIKE '%".$search."%' OR LOWER(city.`city_name`) LIKE '%".$search."%' OR LOWER(country.`country`) LIKE '%".$search."%')";
		}
		if ($filter['state']){
			if ($filter['state']=='U'){
				$where[] = "e.`published`=0";
			}else{
				$where[] = "e.`published`=1";
			}
		}

		if ($filter['country']){
			$where[] = "e.`country_id`='".$filter['country']."'";
		}

		if ($filter['states']){
			$where[] = "e.`state_id`='".$filter['states']."'";
		}
		if ($filter['catid']){
			$where[] = "p.`cat_id`=".$filter['catid'];
		}

		if ($filter['city']){
			$where[] = "e.`city_id`=".$filter['city'];
		}

		if ($filter['performer']){
			$where[] = "e.`performer_id`=".$filter['performer'];
		}


		$query = 'SELECT e.*,p.`performer_name`,v.`venue_name`,city.`city_name`,state.`state_name`,country.`country`  AS `country`  '
		.',(SELECT SUM(tt.`tickets`)
			FROM #__jltcenter_events_ticket_type tt
			WHERE tt.`event_id` = e.`id`) AS `tickets`,
			(SELECT SUM(tt.`tickets_sold`)
			FROM #__jltcenter_events_ticket_type tt
			WHERE tt.`event_id` = e.`id`) AS `tickets_sold`,
			(SELECT SUM(tt.`tickets_ordered`)
			FROM #__jltcenter_events_ticket_type tt
			WHERE tt.`event_id` = e.`id`) AS `tickets_ordered`'
			.' FROM #__jltcenter_events e';

			$query .= " LEFT JOIN #__jltcenter_performer p  ON p.`id`=e.`performer_id` ";
			$query .= " LEFT JOIN #__jltcenter_venue v  ON v.`id`=e.`venue_id` ";
			$query .= " LEFT JOIN #__jltcenter_city city  ON city.`id`=e.`city_id` ";
			$query .= " LEFT JOIN #__jltcenter_state state  ON state.`id`=e.`state_id` ";
			$query .= " LEFT JOIN #__jltcenter_country country ON country.`id`=e.`country_id` ";


			if (isset($where)){
				$query .=" WHERE ".implode(' AND ',$where);
			}

			$query .= $this->_buildEventsOrderBy();

			return $query;
	}


	function _buildEventsOrderBy()
	{
		$mainframe = JFactory::getApplication();

		$orderby = '';
		$filter_order     = $this->getState('filter_order');
		$filter_order_Dir = $this->getState('filter_order_Dir');

		/* Error handling is never a bad thing*/
		if(!empty($filter_order) && !empty($filter_order_Dir) ){
			$orderby = ' ORDER BY '.$filter_order.' '.$filter_order_Dir;
		}
		return $orderby;
	}

	function getData()
	{
		if (empty($this->_data)) {
			$query = $this->_buildQuery();

			$this->_data = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit'));
		}
		return $this->_data;
	}

	 
	function getTotal()
	{
		if (empty($this->_total)) {
			$query = $this->_buildQuery();
			$this->_total = $this->_getListCount($query);
		}
		return $this->_total;
	}
	 

	function getPagination()
	{
		if (empty($this->_pagination)) {
			jimport('joomla.html.pagination');
			$this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
		}
		return $this->_pagination;
	}


	function getCountry()
	{
		$db =& JFactory::getDBO();

		$query = "SELECT *  FROM #__jltcenter_country  WHERE `published`=1 ORDER BY `country`";
		$db->setQuery($query);
		$counties = $db->loadObjectList();

		return $counties;
	}

	 

	function getStates()
	{
		$db =& JFactory::getDBO();

		$query = "SELECT *  FROM #__jltcenter_state WHERE `published`=1 ORDER BY `state_name`";
		$db->setQuery($query);
		$states = $db->loadObjectList();

		return $states;
	}


	 
	function getCities()
	{
		$db =& JFactory::getDBO();

		$filter = JRequest::getVar( 'filter', null);
		if ($filter['country']){
			$where[] ="c.`country_id`=".$filter['country'];
		}
		if ($filter['states']){
			$where[] ="c.`state_id`=".$filter['states'];
		}

		$query = "SELECT c.*,s.`state_name`
					FROM #__jltcenter_city c
				  LEFT JOIN #__jltcenter_state s ON s.id = c.state_id 
				  WHERE c.`published`=1
				  ";

		if ($where){
			$query .=' AND '. implode(' AND ', $where);
		}
		//echo "<br>".implode(' AND ', $where)."<br>";
		$query .= " ORDER BY c.`city_name`";
		$db->setQuery($query);
		//echo "<br>".$db->getQuery()."<br>";
		$cities = $db->loadObjectList();


		return $cities;
	}
	 
	/**
	 * Method to store a record
	 *
	 * @access	public
	 * @return	boolean	True on success
	 */
	function store($data)
	{
		$row =& $this->getTable();

		//$data = JRequest::get('post');

		if (!$row->bind($data)) {
			$this->setError($this->_db->getErrorMsg());
			return false;
		}

		if (!$row->check($data)) {
			$this->setError($this->_db->getErrorMsg());
			return false;
		}

		if (!$row->store()) {
			$this->setError($this->_db->getErrorMsg());
			return false;
		}

		if (!$data['id']){
			$data['id'] = $this->_db->insertid();
			JRequest::set($data,'post');
		}

		return true;
	}

	function delete()
	{
		$cids = JRequest::getVar( 'cid', array(0), 'post', 'array' );

		$row =&$this->getTable();

		if (count( $cids )) {
			foreach($cids as $cid) {
				if (!$row->delete( $cid )) {
					$this->setError( $row->getErrorMsg() );
					return false;
				}
			}
		}
		return true;
	}

	function getCountrySelect(){
		if (!class_exists(GENERAL_MODEL)){
			include 'ticketscenter.php';
		}
		$model = JModelLegacy::getInstance('General', 'JLTicketCenterModel');
		return $model->buildCountrySelect();
	}

	function getStateSelect(){
		if (!class_exists(GENERAL_MODEL)){
			include 'ticketscenter.php';
		}
		$model = JModelLegacy::getInstance('General', 'JLTicketCenterModel');
		return $model->buildStateSelect();
	}

	function getCitySelect(){
		if (!class_exists(GENERAL_MODEL)){
			include 'ticketscenter.php';
		}
		$model = JModelLegacy::getInstance('General', 'JLTicketCenterModel');
		return $model->buildCitySelect();
	}

	function getVenueSelect(){
		if (!class_exists(GENERAL_MODEL)){
			include 'ticketscenter.php';
		}
		$model = JModelLegacy::getInstance('General', 'JLTicketCenterModel');
		return $model->buildVenueSelect();
	}//EOF

	function getPerformerSelect(){
		if (!class_exists(GENERAL_MODEL)){
			include 'ticketscenter.php';
		}
		$model = JModelLegacy::getInstance('General', 'JLTicketCenterModel');
		return $model->buildPerformerSelect();
	}//EOF

	function cancel()
	{
		$row =& $this->getTable();
		$row->bind(JRequest::get('post'));
		$row->checkin();
		return;
	}

	function getGuestList(){
		$id	= JRequest::getInt('id',0);
		if ($id==0){
			return;
		}

		$row =& JTable::getInstance('events', 'Table');
		$row->load($id);

		$where = array(0=>"ol.`event_id`=".$id);
		if ($row->event_type==0){
			// paid event
			$where[]="o.`order_status`>=1";
		}
		$query = 'SELECT ol.*,e.`event_name`,e.`event_date`,e.`event_type`,p.`performer_name`,v.`venue_name`,city.`city_name`,state.`state_name`,country.`country`  AS `country`  '
		.' ,client.`first_name`,client.`last_name`,client.`phone_num`,client.`street`,client.`city`,client.`state`,client.`country`,client.`email`,client.`zip_code`'
		.' ,ticket.`ticket_desc`'
                .' ,o.`hearabout`'
		. ' FROM #__jltcenter_order_lines ol';
			
		$query .= " LEFT JOIN #__jltcenter_events  e  ON e.`id`=ol.`event_id` ";
		$query .= " LEFT JOIN #__jltcenter_performer p  ON p.`id`=e.`performer_id` ";
		$query .= " LEFT JOIN #__jltcenter_venue v  ON v.`id`=e.`venue_id` ";
		$query .= " LEFT JOIN #__jltcenter_city city  ON city.`id`=e.`city_id` ";
		$query .= " LEFT JOIN #__jltcenter_state state  ON state.`id`=e.`state_id` ";
		$query .= " LEFT JOIN #__jltcenter_country country ON country.`id`=e.`country_id` ";
		$query .= " LEFT JOIN #__jltcenter_order o ON o.`order_id`=ol.`order_id` ";
		$query .= " LEFT JOIN #__jltcenter_users client ON client.`id`=o.`client_id` ";
		$query .= " LEFT JOIN #__jltcenter_events_ticket_type ticket ON ticket.`ticket_type`=ol.`ticket_type` ";


		if ($where){
			$query .=" WHERE ".implode(' AND ',$where);
		}
			
		$this->_db->setQuery( $query);
		$result = $this->_db->loadObjectList();

		return $result;
	}
} // EOC