<?php
//--No direct access
defined('_JEXEC') or die('=;)');

jimport('joomla.application.component.model');


require_once(JPATH_BASE."/components/com_vxc/helpers/siteoptionshelper.php");

class vxcModelCheckingOrders extends JModel
{
	var $_data;
	var $_total = null;
	var $_pagination = null;
    var $_simpleRoute = false;
	
	
	
	function __construct()
	{
		parent::__construct();
		global $mainframe,$option;
		$this->setDBO(VoxelCareDB::getDatabase());
		$view  =JRequest::getVar('view');
		
		$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',	'batch', 'cmd' );
		
		if ($this->filter_order=='batch' && !$this->filter_order_Dir)
			$this->filter_order_Dir = 'desc';
		
		$this->searchreference	= $mainframe->getUserStateFromRequest( "$option.$view.searchreference", 'searchreference', '', 'string' );
		$this->searchreference	= JString::strtolower( $this->searchreference );

		$this->searchcustomer	= $mainframe->getUserStateFromRequest( "$option.$view.searchcustomer", 'searchcustomer', '', 'string' );
		$this->searchcustomer	= JString::strtolower( $this->searchcustomer );
				
		$customerId = (int) JRequest::getVar('customerId');
			
		if ($customerId)		
			$this->searchcustomerid = $customerId;
		
		$this->searchfranchise = $mainframe->getUserStateFromRequest( "$option.$view.searchfranchise", 'searchfranchise', '', 'string' );
		if (!$this->searchfranchise)
		{
			$userId = LegacyHelper::getUserId();
			$this->_db->setQuery("SELECT uf.franchise id 
								FROM #__vxc_userfranchise uf 
								WHERE uf.userid = ".$userId);
			
			$this->searchfranchise = $this->_db->loadResult();			
		}
			

        $this->_simpleRoute = SiteOptionsHelper::useSimpleRoute();
		
		//$limit = -1;
		$limit = 200;
		//$limitstart	= $mainframe->getUserStateFromRequest( $option.'.limitstart', 'limitstart', 0, 'int' );
		$limitstart = JRequest::getVar('limitstart',0);
		$limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);
	}
	
	function delete($ids)
	{
		if (count($ids))
		{
			$sql = "UPDATE #__vxc_order SET deleted = TRUE WHERE id IN (".implode(',',$ids).")";
			$this->_db->execute($sql);	
		}
	}
	
	function _buildQuery()
	{
		$where = array();

		
		if ($this->searchcustomerid)
			$where[] = ' o.customer = '.$this->searchcustomerid;
		
		if ($this->searchreference)
			$where[] = ' LOWER(o.reference) LIKE '. $this->_db->Quote('%'.$this->searchreference.'%') ;

        if ($this->searchcustomer) {
            $cond = " ((LOWER(CONCAT_WS(' ',c.name,c.middlename,c.surname)) LIKE ".$this->_db->Quote('%'.$this->searchcustomer.'%').')';

            if (LegacyHelper::getNewMode())
                $cond .=" OR (LOWER(CONCAT_WS(' ',atfirst.value,atmiddle.value,atlast.value)) LIKE ".$this->_db->Quote('%'.$this->searchcustomer.'%').')';

            $cond .=  ')';
            $where[] = $cond;
        }

		if ($this->searchfranchise)
			$where[] = " uf.franchise = ". $this->searchfranchise;
		
		$where[] = ' o.error_desc IS NULL ';
		$where[] = ' o.deleted IS NOT TRUE ';
		$where[] = ' o.archived IS NOT TRUE ';

        if (!LegacyHelper::getNewMode() && LegacyHelper::updatedDB())
            $where[] = " o.`uuid` IS NULL ";


        if ($this->_simpleRoute)
            $where[] = " (os.nameid = 'COMPLETED' OR os.nameid = 'CONFIRMED') ";
        else
            $where[] = " os.nameid = 'CONFIRMED' ";
		
		
		$where =( count($where) ) ? ' WHERE ' . implode( ' AND ', $where ) : '';
		$orderby = '';
		
	
		$orders = array('id','customerfullname','reference','username','receiptpaid','batch','creationdate','reviewers','predesign');
		
		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;
		}

        $newModeFields = '';
        $newJoins = '';
        $oldModeFields ='';
        if (LegacyHelper::getNewMode())
        {
            $newModeFields = "o.uuid uuid, CONCAT_WS(' ',NULLIF(CONCAT_WS(' ',NULLIF(c.name,''),NULLIF(c.middlename,''),NULLIF(c.surname,'')),''),NULLIF(atfirst.value,''),NULLIF(atmiddle.value,''),NULLIF(atlast.value,'')) customerfullname,";

            $newJoins = "
            LEFT JOIN jos_vxc_attribute atfirst ON atfirst.orderid = o.id AND atfirst.name = 'patient.firstName'
            LEFT JOIN jos_vxc_attribute atmiddle ON atmiddle.orderid = o.id AND atmiddle.name = 'patient.middleName'
            LEFT JOIN jos_vxc_attribute atlast ON atlast.orderid = o.id AND atlast.name = 'patient.lastName'
            ";
        }
        else {
            $oldModeFields = "CONCAT_WS(' ',NULLIF(c.name,''),NULLIF(c.middlename,''),NULLIF(c.surname,'')) customerfullname,";
        }

		$this->_query = " SELECT o.id id,o.user user, $newModeFields
								o.customer customer, o.reference reference,								
								o.creationdate creationdate, o.currentStep currentStep,
								o.completedSteps completedSteps,
								o.delegatedesign predesign,
								o.last_save_user last_save_user,
								o.reviewers reviewers,
								$oldModeFields
								o.batch,
								u.name username,
								r.id receiptid,
								r.paid receiptpaid
								 "
				. ' FROM #__vxc_order o					
					LEFT JOIN #__vxc_receipt r ON r.orderid = o.id
					LEFT JOIN #__vxc_user u ON u.id = o.user
					LEFT JOIN #__vxc_userfranchise uf ON uf.userid = o.user
					LEFT JOIN #__vxc_orderstate os ON os.id = o.state
					LEFT JOIN #__vxc_customer c ON c.id = o.customer ' .$newJoins . ' '
				. $where
				. $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');
			
			$ids = array();
			foreach($this->_data as $order)
			{
				$order->readOnly = false;
				$ids[] = $order->id;
			}
						
			if (count($ids))
			{
				$idsStr = implode(',',$ids);


                $newModeFields = '';
                if (LegacyHelper::getNewMode())
                    $newModeFields = "at.value value, at.name name,";


				$sql = "
				SELECT o.id orderid, $newModeFields
						at.id id, 
						ty.name tyname ,
						dt.name dtname,
						at.value_left valueleft,
						at.value_right valueright,
						atvl.name mvalueleft,
						atvr.name mvalueright,
						at.memo_left memoleft,
						at.memo_right memoright
						
				FROM #__vxc_attribute at
				LEFT JOIN #__vxc_order o on o.id =at.orderid 
				LEFT JOIN #__vxc_attributetype ty ON ty.id = at.type
				LEFT JOIN #__vxc_datatype dt ON dt.id = ty.datatype
				LEFT JOIN #__vxc_attributetypevalue atvl ON atvl.id=at.value_left
				LEFT JOIN #__vxc_attributetypevalue atvr ON atvr.id=at.value_right
				WHERE o.id IN ($idsStr)";
												
											
				$this->_db->setQuery($sql);		
				$attributes =& $this->_db->loadObjectList();	
				
				foreach($attributes as $attribute)
				{
					
					if (!$this->_data[$attribute->orderid]->attributes)
						$this->_data[$attribute->orderid]->attributes=array();
					$this->_data[$attribute->orderid]->attributes[]=$attribute;
					
				}				
			}
			
		}
		return $this->_data;
	}	

	function getCustomers()
	{
		$userId = LegacyHelper::getUserId();
		$db =& $this->_db;
		$sql = "SELECT id id, CONCAT_WS(' ',name,middlename,surname) fullname 
				FROM #__vxc_customer
				WHERE user = $userId AND deleted IS NOT TRUE";
		$db->setQuery($sql);
		$customers = $db->loadObjectList();
		return $customers;
	}
	
	function getList()
	{
		// table ordering
		$lists['order_Dir']	= $this->filter_order_Dir;
		$lists['order']		= $this->filter_order;

		// search filter
		$lists['searchreference']= $this->searchreference;
		$lists['searchcustomer']= $this->searchcustomer;
		
		
		
		
				
		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;
	}
}// class