<?php
//--No direct access
defined('_JEXEC') or die('=;)');

jimport('joomla.application.component.model');

include_once(JPATH_BASE."/components/com_vxc/models/order.php");
include_once(JPATH_BASE."/components/com_vxc/models/user.php");
include_once(JPATH_BASE."/components/com_vxc/models/group.php");
include_once(JPATH_BASE."/components/com_vxc/helpers/legacy.php");

class vxcModelOrders 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');
			
		$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',	'creationdate', 'cmd' );
		
		if ($this->filter_order=='creationdate' && !$this->filter_order_Dir)
			$this->filter_order_Dir = 'desc';
				
		$this->searchcustomerreference	= $mainframe->getUserStateFromRequest( "$option.$view.searchcustomerreference", 'searchcustomerreference', '', 'string' );
		if ( $this->searchcustomerreference[0] === '>' ) 
		{
			$this->acc_search_fast = True;
		        $this->searchcustomerreference = substr($this->searchcustomerreference,1);
		}	
                else
                {
			$this->acc_search_fast = False;
                }
		$this->searchcustomerreference	= JString::strtolower( $this->searchcustomerreference );
				
		$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->searchcustomerbirthdate	= $mainframe->getUserStateFromRequest( "$option.$view.searchcustomerbirthdate", 'searchcustomerbirthdate', '', 'string' );
		
		$this->searchstate	= $mainframe->getUserStateFromRequest( "$option.$view.searchstate", 'searchstate', '', 'string' );
        $this->searchsimplestate = $mainframe->getUserStateFromRequest( "$option.$view.searchsimplestate", 'searchsimplestate', '', 'string' );

		$this->searchid = $mainframe->getUserStateFromRequest( "$option.$view.searchid", 'searchid', '', 'string' );
		
		
		$this->group = $mainframe->getUserStateFromRequest( "$option.$view.group", 'group', '', 'string' );
		
		$this->searchgroup = $mainframe->getUserStateFromRequest( "$option.$view.searchgroup", 'searchgroup', '', 'string' );
		$this->searchgroupuserid = $mainframe->getUserStateFromRequest( "$option.$view.searchgroupuserid", 'searchgroupuserid', '', 'string' );
			
		
		$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();
								
			$sql = "SELECT uf.franchise id 
								FROM #__vxc_userfranchise uf 
								WHERE uf.userid = ".$userId;
										
			$this->_db->setQuery($sql);				
			$this->searchfranchise = $this->_db->loadResult();			
		}
				
		$limit		= $mainframe->getUserStateFromRequest( 'global.list.limit', 'limit', 10, 'int' );
				
		//$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 deleteFromShoppingCart($ids) {
		if (!count($ids)) return;
		
		$modelOrder = new vxcModelOrder();
		
		foreach($ids as $id) {
			$modelOrder->SetId($id);
			$modelOrder->setAction('UNCOMPLETE');
		}
	}
	
	function delete($ids) {

        $newIds = array();

		if (count($ids)) {
		
			$sql = "SELECT o.id id, os.nameid statenameid
					FROM #__vxc_order o 
					LEFT JOIN #__vxc_orderstate os ON os.id = o.state
					WHERE o.id IN (".implode(',',$ids).")";
			$this->_db->setQuery($sql);
			$objs =& $this->_db->loadObjectList();
			

			foreach($objs as $obj)
			{
				$canDelete = $obj->statenameid=='INCOMPLETED' ||
					($obj->statenameid=='COMPLETED' && !$obj->delegatedesign);
	
				if ($canDelete)
					$newIds[] = $obj->id;
			}
			
					
			if (count($newIds))
			{			
				$sql = "UPDATE #__vxc_order SET deleted = TRUE WHERE id IN (".implode(',',$newIds).")";
				$this->_db->execute($sql);	
			}
		}

        return $newIds;
	}
	function _buildQuery()	{
        $newMode = LegacyHelper::getNewMode();
		$where = array();

		
		if ($this->searchid)
			$where[] = ' o.id = '. $this->searchid;

		if ($this->searchcustomerid)
			$where[] = ' o.customer = '.$this->searchcustomerid;
		
		if ($this->searchcustomerreference) {
			$cond = ' (( LOWER(o.reference) LIKE '. $this->_db->Quote('%'.$this->searchcustomerreference.'%') .')'.
						" OR (LOWER(CONCAT_WS(' ',c.name,c.middlename,c.surname)) LIKE ".$this->_db->Quote('%'.$this->searchcustomerreference.'%').')';

            if ($newMode)
            {
		if ( $this->acc_search_fast )
		{
                	$cond =" LOWER(atlast.value) LIKE ".$this->_db->Quote('%'.$this->searchcustomerreference.'%');
		}
 		else
		{
                	$cond .=" OR (LOWER(CONCAT_WS(' ',atfirst.value,atmiddle.value,atlast.value)) LIKE ".$this->_db->Quote('%'.$this->searchcustomerreference.'%').')';
                        $cond .=  ')';
		}
            }

		    $where[] = $cond;
        }

        if ($this->searchcustomerbirthdate) {
            $dateS =str_replace('-','/',trim($this->searchcustomerbirthdate));
            $dateArray = explode('/',$dateS);
            $dateCount = count($dateArray);

            if ($dateCount==3) {
                $day = (int) $dateArray[0];
                $month = (int) $dateArray[1];
                $year = (int) $dateArray[2];

                $mysqlDateS = sprintf('%04d-%02d-%02d',$year, $month, $day);

                $cond = " c.birthdate = ".$this->_db->Quote($mysqlDateS);
                if ($newMode)
                    $cond .=" OR atbirthdate.value = ".$this->_db->Quote($mysqlDateS);

                $where[] = "($cond)";
            }
        }

        if ($this->searchsimplestate) {

            switch($this->searchsimplestate) {

                case 'INCOMPLETE':
                    $where[] = " os.nameid = ".$this->_db->Quote('INCOMPLETED');
                    break;
                case 'INSHOPPINGCART':
                    $where[] = " os.nameid = ".$this->_db->Quote('COMPLETED');
                    break;
                case 'ORDERED':
                    $where[] = "( os.nameid <> ".$this->_db->Quote('INCOMPLETED'). " AND os.nameid <> ".$this->_db->Quote('COMPLETED').')' ;
                    break;
            }


        }
        else
            if ($this->searchstate)
                $where[] = " os.nameid = ".$this->_db->Quote($this->searchstate);
		
		switch($this->searchkind)
		{
			case 'all':
			break;
			case 'myfranchise':
			case 'byfranchise':
				if ($this->searchfranchise)
					$where[] = ' uf.franchise = '. $this->searchfranchise;
                else
                    $where[] = " TRUE = FALSE ";
			break;
			case 'mygroups':

                // Obtenemos la lista de grupos de este usuario
                $groups = $this->getGroups();

                // Creamos una lista de id's de grupos
                $groupIds = array();
                if ($groups)
                foreach($groups as $group)
                    $groupIds[] = $group->id;

                // Convertimos a lista SQL
                $groupIdsStr = implode(',',$groupIds);

				if ($groupIdsStr) // Ponemos la condicion de que el pedido tiene que pertenecer a un usuario de la lista de grupos
					$where[] = " (EXISTS(SELECT g.id
										FROM #__vxc_group g 
										LEFT JOIN #__vxc_usergroup ug ON ug.group = g.id
										WHERE ug.user = u.id AND g.id IN ($groupIdsStr))) ";
                else
                    $where[] = " TRUE = FALSE ";
			break;
			case 'bygroup':
				if ($this->searchgroup)
				{
					$userId =LegacyHelper::getUserId();
					if ($userId)
					{
                        $modelUser = new vxcModelUser();
                        $modelUser->SetId($userId);
                        $noManagerRule = '';
                        if ($modelUser->checkPrivilege('Franchise Manager')) {
                            $noManagerRule = " OR TRUE";
                        }

                        $where[] = " (EXISTS(SELECT g.id
                                            FROM #__vxc_group g
                                            LEFT JOIN #__vxc_usergroup ug ON ug.group = g.id
                                            WHERE ug.user = u.id AND ug.group = ". $this->searchgroup ."
                                            ) )	";

                    }

					
				}
                else
                    $where[] = " TRUE = FALSE ";
			break;
			case 'bygroupuser':
				if ($this->searchgroupuserid)
					$where[] = 'o.user = '. $this->searchgroupuserid;
                else
                    $where[] = " TRUE = FALSE ";
			break;			
			case 'byuser':	
				if ($this->searchuserid)
					$where[] = " o.user = ".$this->searchuserid;
                else
                    $where[] = " TRUE = FALSE ";
			break;
			case 'myuser':
			default:
				$userId = LegacyHelper::getUserId();
				$where[] = " o.user = $userId";

		}

        if (!$newMode && LegacyHelper::updatedDB())
            $where[] = " o.`uuid` IS NULL ";

		$where[] = ' o.deleted IS NOT TRUE ';
		$where =( count($where) ) ? ' WHERE ' . implode( ' AND ', $where ) : '';
		$orderby = '';
		
	
		$orders = array('id','username','customerfullname','reference','creationdate','statename');
		
		if (!in_array($this->filter_order,$orders))
			$this->filter_order = 'id';

		if (($this->filter_order) && ($this->filter_order_Dir))
		{
			$orderby 	= ' ORDER BY o.error_desc DESC,  '. $this->filter_order .' '. $this->filter_order_Dir;
		}

        $newModeFields = '';
        $newJoins = '';
        $oldModeFields ='';
        if ($newMode) {
            $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'
            LEFT JOIN jos_vxc_attribute atbirthdate ON atbirthdate.orderid = o.id AND atbirthdate.name = 'patient.dateOfBirth'
            ";
        }
        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.state state,
								o.error_desc error_desc,
								o.batch batch,
								$oldModeFields
								os.nameid statenameid,
								os.name statename,u.name username,
								b.tracking_number tracking_number,
								a.name agencyname
								 "
				. ' FROM #__vxc_order o
					LEFT JOIN #__vxc_batch b ON b.id = o.batch
					LEFT JOIN #__vxc_agency a ON a.id = b.agency
					LEFT JOIN #__vxc_customer c ON c.id = o.customer 
					LEFT JOIN #__vxc_orderstate os ON os.id = o.state 
					LEFT JOIN #__vxc_user u ON u.id = o.user					
					LEFT JOIN #__vxc_userfranchise uf ON uf.userid = u.id
					' .$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 = true;
				$ids[] = $order->id;
			}
						
			if (count($ids))
			{
				$idsStr = implode(',',$ids);

                $newFields = '';

                if (LegacyHelper::getNewMode())
                    $newFields = ' at.name name ,at.value value,';

				$sql = "
				SELECT o.id orderid,
						at.id id, $newFields
						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 getFranchises()
	{
		$db =& $this->_db;
		
		$sql = "SELECT f.id id, f.name name
				FROM #__vxc_franchise f
				ORDER BY f.name";
				
		$db->setQuery($sql);
	
		return $db->loadObjectList();
	
	}
	
	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();
	
	}
	
	function getGroups() {
		$franchiseId = $this->searchfranchise;
		if (!$franchiseId ) return array();

		$userId = LegacyHelper::getUserId();
		if (!$userId) return array();

		$modelUser = new vxcModelUser();		
		$modelUser->SetId($userId);

        if ($modelUser->checkPrivilege('Franchise Manager')) { // Si es administrador de franquicia tiene acceso a todos los grupos
            $modelGroup = new vxcModelGroup();
            return $modelGroup->getGroups();
        }
        else // Si no pedimos la lista de grupos de ese usuario
		    return $modelUser->getOrderGroups();
	}
	
	function getGroupUsers()
	{	
		$groupId = $this->searchgroup;
		
		if (!$groupId ) return array();
				
		$db =& $this->_db;
		
		$sql = "SELECT u.id id , u.name name
				FROM #__vxc_usergroup ug				
				LEFT JOIN #__vxc_user u ON u.id = ug.user
				WHERE ug.group = $groupId
				ORDER BY u.name";
								
		$db->setQuery($sql);
	
		return $db->loadObjectList();	
	}
	
	
	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['searchcustomerreference']= $this->searchcustomerreference;		
		$lists['searchuserid']= $this->searchuserid;
        $lists['searchsimplestate'] = $this->searchsimplestate;
		$lists['searchkind']= $this->searchkind;
		$lists['searchstate']= $this->searchstate;
		$lists['searchfranchise']= $this->searchfranchise;
		$lists['searchgroup'] = $this->searchgroup;
		$lists['searchgroupuserid'] = $this->searchgroupuserid;
        $lists['searchcustomerbirthdate'] = $this->searchcustomerbirthdate;
		$lists['searchid']= $this->searchid;
		$lists['group'] = $this->group;
		
		$lists['groupusers'] = $this->getGroupUsers();
		
		$lists['users'] = $this->getUsers();
		$lists['franchises'] = $this->getFranchises();
		$lists['groups'] = $this->getGroups();
				
		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') );
			$this->_pagination->maxlimit = 100;
		}
		
		return $this->_pagination;
	}
	
	
	function _getInsoleOptionsWhereClause($insoleOptions=array(),$productOptions=array()) {
    $cond = array();

    $sqlMats = '';
    if (count($insoleOptions)>0)
        foreach($insoleOptions as $mat)
        {
            if ($sqlMats)
                $sqlMats .=" OR ";
            $sqlMats .=" vl.name = ".$this->_db->Quote($mat) . " OR " ." vr.name = ".$this->_db->Quote($mat)  ;
        }


    if ($sqlMats)
    {
        $sqlMats = " EXISTS( SELECT *
								FROM #__vxc_attribute at
								LEFT JOIN #__vxc_attributetype ty ON ty.id = at.type AND ty.name = 'InsoleOptions'
								LEFT JOIN #__vxc_attributetypevalue vl ON vl.id = at.value_left
								LEFT JOIN #__vxc_attributetypevalue vr ON vr.id = at.value_right
								WHERE at.orderid = o.id AND ($sqlMats) ) ";
        $cond[] = "(".$sqlMats.")";
    }

    $sqlMats2 = '';
    if (count($productOptions)>0)
        foreach($productOptions as $mat)
        {
            if ($sqlMats2)
                $sqlMats2 .=" OR ";
            $sqlMats2 .=" at.value = ".$this->_db->Quote($mat) ;
        }


    if ($sqlMats2)  {
        $sqlMats2 = " EXISTS( SELECT *
								FROM #__vxc_attribute at
								WHERE at.orderid = o.id AND at.name=".$this->_db->Quote("general.product"). " AND  ($sqlMats2) ) ";
        $cond[] = "(".$sqlMats2.")";
    }


    if (count($cond)>0)
        return "(". implode(" OR ",$cond). ")";
    else
        return "";
}
	
	function getDeliveredInsoleCountPerDate($date, $insoleOptions=array(), $productOptions=array())
	{
	
		$sql = "SELECT COUNT(*) cantidad
				FROM #__vxc_order o
				LEFT JOIN #__vxc_batch b ON b.id = o.batch
				WHERE b.est_shipping_date = '".$date."'";
		
		$insoleOptionsClause = $this->_getInsoleOptionsWhereClause($insoleOptions,$productOptions);
		
		if ($insoleOptionsClause)
			$sql .= " AND ". $insoleOptionsClause;
		
		$this->_db->setQuery($sql);
		
		return $this->_db->loadResult();	
	}
	
	function getManufacturingInsoleCountPerDate($date,$insoleOptions=array(), $productOptions=array())
	{
	
		$sql = "SELECT COUNT(*) cantidad
				FROM #__vxc_order o				
				WHERE o.manufacturing_date = '".$date."'";
		
		$insoleOptionsClause = $this->_getInsoleOptionsWhereClause($insoleOptions,$productOptions);
		
		if ($insoleOptionsClause)
			$sql .= " AND ". $insoleOptionsClause;
		
		
		$this->_db->setQuery($sql);
		
		return $this->_db->loadResult();	
	}
	
}// class