<?php
/**
 * @version $Id$
 * @package    vxc
 * @subpackage Models
 * @author     EasyJoomla {@link http://www.easy-joomla.org Easy-Joomla.org}
 * @author     VoxelWorks S.L. {@link http://www.voxelworks.com}
 * @author     Created on 19-Jul-09
 */

//--No direct access
defined('_JEXEC') or die('=;)');

jimport('joomla.application.component.model');

include_once(JPATH_BASE."/components/com_vxc/models/batchstates.php");


class vxcModelBatch extends JModel
{

	var $_id = 0;
	var $_item = null;
	/**
	 * Constructor
	 *
	 * @access	public
	 * @return	void
	 */
	function __construct()
	{
		parent::__construct();
		$this->setDBO(VoxelCareDB::getDatabase());
	}//function
				
	function SetId($id)
	{
		
		$this->_id = (int)$id;
		$this->_item = null;
	}
	
	
	
	function store(&$data)
	{
		$table =& $this->getTable('batch');				
		$db =& $this->_db;		
		
		$table->reset();
		$id = (int)$data['id'];
		
		$table->load($id);
		if (!$table->bind($data))
			return false;
		
		if (!$table->check())
			return false;
		
		if (!$table->store(true))
			return false;		
		
		$this->SetId($table->id);		
		$data['id'] = $this->_id;	
		
		return true;	
	}
	
	
	
	function getData()
	{
		if (empty($this->_item))
		{
			$db =& $this->_db;
		
			$sql = "SELECT b.id id, b.state state,
							b.agency agency,
							a.name agencyname,
							b.shipping_date shipping_date,
							b.payment_date payment_date,
							b.payment_user payment_user,
							b.tracking_number tracking_number,
							b.shipping_address shipping_address,
							bs.nameid statenameid,
							b.payment_method payment_method,
							b.shipping_cost shipping_cost,
						    b.shipping_vat shipping_vat,
							b.deliverynotes_cost deliverynotes_cost,
							b.taxpercent taxpercent,
							b.discount discount,
							b.equivalence_charge equivalence_charge,
							b.est_shipping_date est_shipping_date,
							b.payment_inc_cost payment_inc_cost,
							b.forwarded forwarded,
							pm.name payment_methodname,
							pm.nameid payment_methodnameid,
							ua.company address_company,
							ua.address address_address,
							ua.city address_city,
							ua.province address_province,
							ua.pcode address_pcode,
							ua.country address_country,
							ua.phone address_phone
					FROM #__vxc_batch b
					LEFT JOIN #__vxc_batchstate bs ON bs.id = b.state
					LEFT JOIN #__vxc_agency a ON a.id = b.agency
					LEFT JOIN #__vxc_paymentmethod pm ON pm.id = b.payment_method
					LEFT JOIN #__vxc_useraddress ua ON ua.id = b.shipping_address
					WHERE b.id = ".$this->_id;
		
	
			$db->setQuery($sql);
			
			$row = $db->loadObject();
			
			if (!$row)
			{						
				$row = new stdClass();
				$row->id = 0;
				$row->state = null;			
				$row->shipping_date= null;				
				$row->payment_date = null;	
				$row->tracking_number = null;
			}
			
			$this->_item = $row;			
		}				
		
		return $this->_item;	
	}	
	function getOrders()
	{
		$sql = "SELECT o.id id,
						o.batch batch,
						o.reference reference,
						o.price price,	
						o.currentStep currentStep,
						o.patient_price patient_price,
						o.franchise_to_company_fee franchise_to_company_fee,
						o.voxelcare_to_franchise_fee voxelcare_to_franchise_fee,
						o.paid_amount,
						o.manufacturing_date manufacturing_date,
						os.name statename,
						os.nameid statenameid,
						CONCAT_WS(' ',NULLIF(c.name,''),NULLIF(c.middlename,''),NULLIF(c.surname,'')) customerfullname,
						r.id receipt,
						r.paid receiptpaid
				FROM #__vxc_order o						
				LEFT JOIN #__vxc_orderstate os ON os.id = o.state
				LEFT JOIN #__vxc_customer c ON c.id = o.customer
				LEFT JOIN #__vxc_receipt r ON r.orderid = o.id
				WHERE  o.deleted is not true  and o.batch = ".$this->_id;
		$this->_db->setQuery($sql);
		return $this->_db->loadObjectList();
	}
	
	function getDeliveryNotes()
	{
		$sql = "SELECT dn.id id,
						dn.concept concept,
						dn.price price,
						dn.userid userid,
						dn.batch batch,
						dn.vat vat,
						dn.total total,
						dn.amount amount
				FROM #__vxc_deliverynote dn
				WHERE dn.batch = ". $this->_id;
				
		$this->_db->setQuery($sql);
		return $this->_db->loadObjectList();		
				
	
	}

    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 getOrderCount()
	{
		$sql = "SELECT count(*) cantidad
				FROM #__vxc_order o	
				WHERE  o.deleted is not true  and o.batch = ".$this->_id;
		
		$this->_db->setQuery($sql);
		return $this->_db->loadResult();	
	
	}
	
	function getOrderDistribution($insoleCategories) {
		
		$distribution = array();
		
		for ($i=0;$i<count($insoleCategories);$i++) {
			$name = $insoleCategories[$i]['name'];
			$options = $insoleCategories[$i]['options'];
            $productOptions = $insoleCategories[$i]['productOptions'];
				
		
			$sql = "SELECT count(*) cantidad
					FROM #__vxc_order o	
					WHERE  o.deleted IS NOT TRUE
					    AND o.batch = ".$this->_id;
			
			$insoleOptionsClause = $this->_getInsoleOptionsWhereClause($options,$productOptions);
			
			if ($insoleOptionsClause)
				$sql .= " AND ". $insoleOptionsClause;
				
			$this->_db->setQuery($sql);
			$count = $this->_db->loadResult();	
			
			if ($count>0)
				$distribution[$name] = $count;
		}
			
		return $distribution;		
	}
	
	
	function SetStateName($state)
	{	
		$data = array();
		$data['id'] = $this->_id;
		$modelBatchStates = new vxcModelBatchStates();			
		$data['state'] = $modelBatchStates->getIdFromName($state);
		if ($state=='SHIPPED')
		{
			$dateNow =  new JDate();
			$data['shipping_date'] = $dateNow->toMySQL();
		}
	
		$this->store($data);
	}
	
	function UpdateState()
	{
		$item =& $this->getData();
		
		if ($item->statenameid=='SHIPPED')
			return;
				
		$orders = $this->getOrders();
		
		$allmanufactured = true;
		foreach($orders as $order)		
			if ($order->statenameid != 'MANUFACTURED')
			{
				$allmanufactured= false;
				break;
			}		
		if ($allmanufactured)
			$state ='COMPLETED';
		else
			$state ='INCOMPLETED';

		if ($state!=$item->statenameid)
			$this->SetStateName($state);
	}
	
	function isBatchChecked()
	{
		$batchId = (int)$this->_id;		
		if (!$batchId) return false;
		
		$sql = "SELECT COUNT(*) nonCheckedOrders
				FROM #__vxc_order o
				LEFT JOIN #__vxc_batch b ON b.id = o.batch
				WHERE o.checked_date IS NULL AND b.id = $batchId";
		
		$this->_db->setQuery($sql);
		
		$nonCheckedOrders = $this->_db->loadResult();
		
		return ($nonCheckedOrders==0);	
	}

	
}// class