<?php
//--No direct access
defined('_JEXEC') or die('=;)');

jimport('joomla.application.component.model');

include_once(JPATH_BASE."/components/com_vxc/models/settings.php");

class vxcModelShippingBatchs extends JModel
{
	var $_data;
	var $_total = null;
	var $_pagination = null;
	var $_listingMode = null;

	
	function __construct()
	{
		parent::__construct();
		$this->setDBO(VoxelCareDB::getDatabase());	
		global $mainframe,$option;
		
		$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' );
		
		if ($this->filter_order=='payment_date' && !$this->filter_order_Dir)
			$this->filter_order_Dir = 'asc';
		
		$this->searchstate	= $mainframe->getUserStateFromRequest( "$option.$view.searchstate", 'searchstate', 'COMPLETED', 'string' );
		$this->searchpaymethod	= $mainframe->getUserStateFromRequest( "$option.$view.searchpaymethod", 'searchpaymethod', '', 'string' );
	
		$userId = LegacyHelper::getUserId();
		
		$this->searchuserid	= $mainframe->getUserStateFromRequest( "$option.$view.searchuserid", 'searchuserid', $userId, 'int' );
		
		$this->searchid	= $mainframe->getUserStateFromRequest( "$option.$view.searchid", 'searchid', '', 'string' );
		
	
		$this->searchfranchise = $mainframe->getUserStateFromRequest( "$option.$view.searchfranchise", 'searchfranchise', '', 'string' );
		if (!$this->searchfranchise)
		{
			
			$this->_db->setQuery("SELECT uf.franchise id 
								FROM #__vxc_userfranchise uf 
								WHERE uf.userid = ".$userId);
			
			$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 setModeListing($value)
	{
		$this->_listingMode = $value;
		$this->searchpaymethod = '';
		$this->searchstate= '';
		$this->searchuserid  ='';
		$this->searchid = '';
		
		
	}
	
	function _buildQuery()
	{
		$where = array();
		$having= array();
		$groupby= array();

		$groupby[]  ='id';
		$groupby[]  ='user';
		
		if ($this->searchfranchise)
			$where[] = "uf.franchise = ". $this->searchfranchise;
				
		
		if ($this->_listingMode)
		{		
			$this->setState('limit', -1);
			$this->setState('limitstart', 0);
			
			$where[] = " bs.nameid <> 'SHIPPED' ";
			$where[] = " b.est_shipping_date IS NOT NULL ";
		}
		else
		{
			if ($this->searchpaymethod)
				$where[] = "pm.nameid = " .$this->_db->Quote($this->searchpaymethod);
			if ($this->searchstate)
				$where[] = "bs.nameid = ".$this->_db->Quote($this->searchstate);
					
			if ($this->searchuserid)
				$having[] = " user = ".$this->searchuserid;
			
			if ($this->searchid)
				$where[] = " b.id = ".$this->searchid;			
		}
		
		$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','state','username','payment_date','paymentmethodname','shipping_date','est_shipping_date','tracking_number','agencyname','numorders');
		
		if (!in_array($this->filter_order,$orders))
			$this->filter_order = 'id';
			
		if ($this->_listingMode)
		{
			$orderby = ' ORDER BY b.est_shipping_date ASC,u.id ASC, b.id ASC ';
		}
		else
		{
		
		if (($this->filter_order) && ($this->filter_order_Dir))
		{
			$orderby 	= ' ORDER BY '. $this->filter_order .' '. $this->filter_order_Dir;
		}
		
		}
		$orderIdBackColor = $this->_orderIdBackColor;
		
		$this->_query = " SELECT b.id id, b.state state,
								b.payment_date payment_date,
								b.shipping_date shipping_date,
								b.est_shipping_date est_shipping_date,
								b.tracking_number tracking_number,
								b.payment_user user,
								b.taxpercent batch_taxpercent,
								b.discount batch_discount,
								b.payment_inc_cost batch_payment_inc_cost,							
								b.equivalence_charge batch_equivalence_charge,
								b.shipping_cost batch_shipping_cost,
								b.shipping_vat batch_shipping_vat,
								(SELECT sum(o1.price)
								  from #__vxc_order o1
								  where o1.batch = b.id ) batch_price,									
								b.agency agency,
								b.forwarded forwarded,
								bs.nameid statenameid,
								bs.name statename,
								count(o.id) numorders,
								u.name username,
								a.name agencyname,
								pm.name paymentmethodname,		
								pm.nameid payment_methodnameid,								
								GROUP_CONCAT(o.id SEPARATOR '#') order_list	,										
								GROUP_CONCAT(CONCAT_WS(' / ',NULLIF(CONCAT_WS(' ',NULLIF(c.name,''),NULLIF(c.middlename,''),NULLIF(c.surname,'')),''),NULLIF(o.reference,'')) SEPARATOR '#') patient_list									
										
								 "
				. ' FROM #__vxc_batch b							
					LEFT JOIN #__vxc_batchstate bs ON bs.id = b.state 
					LEFT JOIN #__vxc_paymentmethod pm ON pm.id = b.payment_method
					LEFT JOIN #__vxc_order o ON o.batch = b.id 					
					LEFT JOIN #__vxc_customer c ON c.id = o.customer
					LEFT JOIN #__vxc_user u ON u.id = b.payment_user
					LEFT JOIN #__vxc_userfranchise uf ON uf.userid = u.id
					LEFT JOIN #__vxc_agency a ON a.id = b.agency '					
				. $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');
			
			// Generamos la lista de pedidos a averiguar el material
			$orderIds = array();
							
			foreach($this->_data as $batch)
			{
				$orderArray = explode('#',$batch->order_list);
			
				foreach($orderArray as $orderId)
					$orderIds[] = $orderId;			
			}
			$modelOrder = new vxcModelOrder();				
			$mats =& $modelOrder->getMaterials($orderIds);
							
			foreach($this->_data as $batch)
			{
				$matList = array();
				$orderArray = explode('#',$batch->order_list);
			
				foreach($orderArray as $orderId)
				{
					$matList[] = $mats[$orderId];
				}
					
				$batch->mat_list = $matList;
			}		
		}
		return $this->_data;
	}	

	
	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 uf.userid = u.id 
				WHERE uf.franchise = $franchiseId AND uf.deleted IS NOT TRUE
				ORDER BY u.name";
		$db->setQuery($sql);
	
		return $db->loadObjectList();
	
	}

	function getPaymentMethods()
	{
		$db =& $this->_db;
		
		$sql = "SELECT pm.nameid value , pm.name text
				FROM #__vxc_paymentmethod  pm ";
		$db->setQuery($sql);
	
		return $db->loadObjectList();	
	
	
	}
	
	function getBatchStates()
	{
		$db =& $this->_db;
		
		$sql = "SELECT bs.nameid value , bs.name text
				FROM #__vxc_batchstate  bs ";
		$db->setQuery($sql);
	
		return $db->loadObjectList();	
	}
	
	function getAgencies()
	{
	$sql = "SELECT a.id id,
					a.name name
			FROM #__vxc_agency a
			ORDER BY a.sorting ";
				
		$this->_db->setQuery($sql);
		return $this->_db->loadObjectList();
	}
	
	function getList()
	{
		// table ordering
		$lists['order_Dir']	= $this->filter_order_Dir;
		$lists['order']		= $this->filter_order;

		// search filter
		$lists['searchstate']= $this->searchstate;	
		$lists['searchpaymethod']= $this->searchpaymethod;	
		$lists['searchid']= $this->searchid;		
		$lists['searchuserid']= $this->searchuserid;
		$lists['batchstates'] = $this->getBatchStates();
		$lists['users'] = $this->getUsers();
		$lists['agencies'] = $this->getAgencies();
		$lists['paymentmethods'] = $this->getPaymentMethods();
		
				
		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 getExpeditionLabelsData(&$batchIds)
	{
	
		if (!is_array($batchIds) || count($batchIds)==0)
			return array();
			
			
		$sql = "SELECT b.id id, b.state state,
								bs.nameid statenameid,
								bs.name statename,
								b.payment_date payment_date,
								b.shipping_date shipping_date,
								b.est_shipping_date est_shipping_date,
								b.tracking_number tracking_number,
								b.payment_user user,
								u.name username,
								b.agency agency,
								b.shipping_address shipping_address,
								a.name agencyname,
								o.id orderid,
								u.name username,
								u.address useraddress,
								u.city usercity,
								u.province userprovince,
								u.pcode userpcode,
								u.phone userphone,
								u.location userlocation,	
								ua.company shipping_address_company,
								ua.address shipping_address_address,
								ua.city shipping_address_city,
								ua.province shipping_address_province,
								ua.pcode shipping_address_pcode,
								ua.country shipping_address_country,
								ua.phone shipping_address_phone,
								ua.delivery_schedule shipping_address_delivery_schedule,
								pm.name paymentmethodname,
								at.memo_left specific_address
				    FROM #__vxc_batch b							
					LEFT JOIN #__vxc_useraddress ua ON ua.id = b.shipping_address
					LEFT JOIN #__vxc_batchstate bs ON bs.id = b.state 
					LEFT JOIN #__vxc_paymentmethod pm ON pm.id = b.payment_method
					LEFT JOIN #__vxc_order o ON o.batch = b.id 					
					LEFT JOIN #__vxc_customer c ON c.id = o.customer
					LEFT JOIN #__vxc_user u ON u.id = b.payment_user
					LEFT JOIN #__vxc_userfranchise uf ON uf.userid = u.id
					LEFT JOIN #__vxc_agency a ON a.id = b.agency 	
					LEFT JOIN #__vxc_attribute at ON at.orderid = o.id AND at.type = 323
					WHERE b.id IN (".implode(',',$batchIds).")
					ORDER BY b.id ASC ,o.id ASC";
					
				
		$this->_db->setQuery($sql);
		return $this->_db->loadObjectList();
	}
	
}// class