<?php
//--No direct access
defined('_JEXEC') or die('=;)');

jimport('joomla.application.component.model');


class vxcModelReceipts extends JModel
{
	var $_data;
	var $_total = null;
	var $_pagination = null;
	

	function __construct()
	{
		parent::__construct();
		global $mainframe,$option;
		$this->setDBO(VoxelCareDB::getDatabase());	
		
		$view  =JRequest::getVar('view');		
		//global $option;
		$this->filter_order_Dir	= $mainframe->getUserStateFromRequest( $option.$view.'.filter_order_Dir', 'filter_order_Dir', '', 'word' );
		$this->filter_order	= $mainframe->getUserStateFromRequest( $option.$view.'.filter_order', 'filter_order',	'payment_date', 'cmd' );
		
		
		$this->searchmonth	= $mainframe->getUserStateFromRequest( "$option.$view.searchmonth", 'searchmonth', '', 'int' );
		$this->searchyear	= $mainframe->getUserStateFromRequest( "$option.$view.searchyear", 'searchyear', '', 'int' );
		
		
		$this->searchfranchise = $mainframe->getUserStateFromRequest( "$option.$view.searchfranchise", 'searchfranchise', '', 'string' );
		if (!$this->searchfranchise)
		{
			$userId = LegacyHelper::getUserId();
			$this->_db->setQuery("SELECT uf.franchise id 
								FROM jos_vxc_userfranchise uf 
								WHERE uf.userid = ".$userId);
			
			$this->searchfranchise = $this->_db->loadResult();		
			
				
		}
		
		$this->searchkind	= $mainframe->getUserStateFromRequest( "$option.$view.searchkind", 'searchkind', 'MYUSER', 'string' );
		$this->searchkind	= JString::strtolower( $this->searchkind );

		if ($this->searchkind == 'byuser')
		{
			
			$this->searchuserid	= $mainframe->getUserStateFromRequest( "$option.$view.searchuserid", 'searchuserid', '0', 'string' );
			$this->searchuserid	= JString::strtolower( $this->searchuserid );
			
		}
		
		$this->searchbatch = $mainframe->getUserStateFromRequest( "$option.$view.searchbatch", 'searchbatch', '', 'string' );
		$this->searchorder = $mainframe->getUserStateFromRequest( "$option.$view.searchorder", 'searchorder', '', 'string' );
		$this->searchcustomername = $mainframe->getUserStateFromRequest( "$option.$view.searchcustomername", 'searchcustomername', '', 'string' );
		
		
		
		
		$limit	= $mainframe->getUserStateFromRequest( 'global.list.limit', 'limit', 10, 'int' );		
		
		$limitstart = JRequest::getVar('limitstart',0);
		$limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);
	}
	
	function _buildQuery()
	{
		$where = array();
		$groupby= array();
		$having=array();

		if ($this->searchmonth)
			$where[] = " MONTH(i.generation_date) = ". (int)$this->searchmonth;
		
		if ($this->searchyear)
			$where[] = " YEAR(i.generation_date) = ". (int)$this->searchyear;
	
		if ($this->searchbatch)
		{
			$where[] = " o.batch = ".$this->searchbatch;
		}
		
		if ($this->searchorder)
		{
			$where[] = " o.id = ".$this->searchorder;
		}
		
		if ($this->searchcustomername)
		{
			$where[] = " o.id IN ( ".
				" (SELECT o1.id
					FROM #__vxc_order o1 
					LEFT JOIN #__vxc_customer c1 ON c1.id = o1.customer
					WHERE 				
					 (					 
						( LOWER(o1.reference) LIKE ". $this->_db->Quote('%'.$this->searchcustomername.'%') .')'.
						" OR ".
						"(LOWER(CONCAT_WS(' ',c1.name,c1.middlename,c1.surname)) LIKE ".$this->_db->Quote('%'.$this->searchcustomername.'%').')'.
				  	 ") AND o1.id = o.id
				))";
		}
		
		switch($this->searchkind)
		{
			case 'all':
			break;
			case 'myfranchise':
			case 'byfranchise':
				if ($this->searchfranchise)
					$where[] = ' uf.franchise = '. $this->searchfranchise;
			break;		
			case 'byuser':					
				if ($this->searchuserid)					
					$where[] = " o.user = ".$this->searchuserid;
			break;
			case 'myuser':
			default:
				$userId = LegacyHelper::getUserId();
				$where[] = " o.user = $userId";

		}
        if (!LegacyHelper::getNewMode() && LegacyHelper::updatedDB())
            $where[] = " o.`uuid` IS NULL ";

        $where[] = " o.deleted IS NOT TRUE ";
			
		$where =( count($where) ) ? ' WHERE ' . implode( ' AND ', $where ) : '';
		$having =( count($having) ) ? ' HAVING ' . implode( ' AND ', $having ) : '';
		$groupby =( count($groupby) ) ? ' GROUP BY ' . implode( ' , ', $groupby ) : '';

		$orderby = '';	
		
		$orders = array('id','reference','username','paid','total','batch');

		if (!in_array($this->filter_order,$orders))
			$this->filter_order = 'id';

		if (($this->filter_order) && ($this->filter_order_Dir))
		{
			$orderby 	= ' ORDER BY '. $this->filter_order .' '. $this->filter_order_Dir;
		}
		
		$this->_query = " SELECT r.id id, 
								r.paid paid,
								r.orderid orderid,								
								r.reference reference,
								r.paid_date paid_date,
								b.id batch,								
								u.name username,
								o.paid_amount paid_amount
				FROM #__vxc_receipt r
				LEFT JOIN #__vxc_order o ON o.id = r.orderid
				LEFT JOIN #__vxc_user u ON u.id = o.user
				LEFT JOIN #__vxc_userfranchise uf ON uf.userid = u.id
				LEFT JOIN #__vxc_franchise f ON uf.franchise = f.id
				LEFT JOIN #__vxc_batch b ON b.id = o.batch "
				. $where
				. $groupby
				. $having
				. $orderby;
        				
					
		return $this->_query;	
	}
	

	function getData() 
	{
 		if (empty($this->_data))
 		{
			$query = $this->_buildQuery();
			
			$this->_db->setQuery( $query,$this->getState('limitstart'), $this->getState('limit') );
			$this->_data = $this->_db->loadObjectList('id');
		}
		return $this->_data;
	}	
		
	function getList()
	{
		// table ordering
		$lists['order_Dir']	= $this->filter_order_Dir;
		$lists['order']		= $this->filter_order;

		// search filter
		$lists['searchmonth']= $this->searchmonth;
		$lists['searchyear']= $this->searchyear;
		$lists['searchuserid']= $this->searchuserid;
		$lists['searchkind']= $this->searchkind;
		$lists['searchbatch']= $this->searchbatch;		
		$lists['searchfranchise']= $this->searchfranchise;
		$lists['searchorder'] = $this->searchorder;
		$lists['searchcustomername'] = $this->searchcustomername;
		$lists['users'] = $this->getUsers();
		
		
		
				
		return $lists;
	}

	function getTotal()
	{
		// Load the content if it doesn't already exist
		if (empty($this->_total))
		{
			$query = $this->_buildQuery();
			$this->_total = $this->_getListCount($query);	
		}
		
		return $this->_total;
	}

	function getPagination()
	{
		// Load the content if it doesn't already exist
		if (empty($this->_pagination))
		{
			jimport('joomla.html.pagination');
			$this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );			
		}
		
		return $this->_pagination;
	}
	
		
	function getUsers()
	{
		$franchiseId = $this->searchfranchise;
		if (!$franchiseId ) return array();
		
		$db =& $this->_db;
		
		$sql = "SELECT u.id id , u.name name
				FROM #__vxc_user u
				LEFT JOIN #__vxc_userfranchise uf ON u.id = uf.userid
				WHERE uf.franchise = $franchiseId AND uf.deleted IS NOT TRUE
				ORDER BY u.name";
		$db->setQuery($sql);
	
		return $db->loadObjectList();
	
	}
	
	
}// class