<?php
/**
 * Shopping Basket functions using 
 * ShoppingBasket data table and cookies
 * 
 * @author Neil Corke <neil@corkeweb.com>
 * 
 * Updated 2015-09-28 (Neil Corke)
 *
 */
class Falk_ShoppingBasketService
{
    /**
     * ShoppingBasket Zend Table  
     *
     * @var ShoppingBasketTable
     */
  	protected $shoppingBasket;
  	
  	/**
     * The database adapater
     */
  	protected $db;
  	
  	/**
  	 * Master Database
  	 */
  	protected $masterDB;
  	
  	/**
  	 * The checkout session
  	 */
  	protected $cos;
  	
  	/**
  	 * Stores the visitors Basket ID:
  	 */
  	private static $_mBasketId;
 	
 	/**
 	 * 
 	 * Instantiate the ShoppingBasketTable model
 	 * and ensure Basket ID exists.
 	 */
  	function __construct()
    {                
      	// Get the default db adapter:
    	$this->db = Zend_Db_Table::getDefaultAdapter();
    	Zend_Db_Table_Abstract::setDefaultAdapter($this->db);
    	
    	$this->shoppingBasket = new Order_Model_ShoppingBasketTable(); 
    	
    	// The checkout session:
    	$this->cos = new Zend_Session_Namespace('falk.checkout');
    	$this->cos->setExpirationSeconds(12 * 60 * 60); 	

    	// The master database name:
    	$this->masterDB = Zend_Registry::get('masterDb');
    	
    	// Check for Basket Id and set if it does not exits:
    	self::_setBasketId();
    }
    
    
	/**
	 * 
	 * Checks for Basket Id stored in session, or as a cookie, generate 
	 * a new ID and save it to the $_mBasketId class member, the session
	 * and a cookie (on subsequent requests $_mBasketId will be populated
	 * from the session.
	 * 
	 * @access private
	 */
    private static function _setBasketId()	
    {
    	// If the Basket ID hasn't already been set:
    	if (self::$_mBasketId == '') {
    		
    		// If the visitors basket ID is in the session, get it from there:
    		$session = new Zend_Session_Namespace('falk.basket');
    		
    		if (isset($session->basketId)) {
    		
    			self::$_mBasketId = $session->basketId;
    		
    		// If not, check whether the basket ID was saved as a cookie:
    		} elseif (isset ($_COOKIE['Basket'])) {
    		
    			// Save the Basket ID from the cookie:
    			self::$_mBasketId = $_COOKIE['Basket'];
    			$session->basketId = self::$_mBasketId;
				
			// Generate new Basket ID	
    		} else {
    			
    		    // Use secure token service:
				self::$_mBasketId = Secure_Token_Service::generate(32);
    		
    			// Store Basket Id in session:
    			$session->basketId = self::$_mBasketId;
    		}  

    		// Set or regenerate the cookie:
    		Falk_Visitor_Tracking_Cookie::set('Basket', self::$_mBasketId, 1095);
    	}	
    }
    
    
    /**
     * Returns the visitor's Basket Id:
     * 
     * @return string - the Basket Id
     * 
     * @access public
     */
    public static function _getBasketId()
    {
    	// Ensure we have a Basket Id for the current visitor:
    	if (!isset (self::$_mBasketId)) {
    		
    		self::_setBasketId();
    	}
    	
    	// Return Basket Id:
    	return self::$_mBasketId;
    }
     
    
    /**
     * Get the number of items in the visitors basket
     * 
     * @param string $basketId - the Basket ID
     * 
     * @return number - the number of items in the basket
     * 
     * @access public
     */
    public function fetchItemCount($basketId = NULL) 
    {
        // Get the Basket Id, if not provided:
        if (! $basketId) {
        		
        	$basketId = self::_getBasketId();
        }
        
        $select = $this->shoppingBasket->select();
        $select->from(array('sb' => 'ShoppingBasket'),
        		            array('SUM(sb.quantity)'))
        		
        	   ->where('sb.basketId = ?', $basketId)
        	   ->where('sb.buyNow = ?', 1);
        
        $result = $this->db->fetchOne($select);
        
        $count = isset($result) ? $result : 0;
        
        return (int) $count;
    }
    
    
    /**
     * Get the number of items "saved for later"
     *
     * @param string $basketId - the Basket id
     *
     * @return number - the number of items
     *
     * @access public
     */
    public function fetchSavedForLaterCount($basketId = NULL)
    {
    	// Get the Basket Id, if not provided:
    	if (! $basketId) {
    
    		$basketId = self::_getBasketId();
    	}
    
    	$select = $this->shoppingBasket->select();
    	$select->from(array('sb' => 'ShoppingBasket'),
    			array('SUM(sb.quantity)'))
    
    			->where('sb.basketId = ?', $basketId)
    			->where('sb.buyNow = ?', 0);
    
    	$result = $this->db->fetchOne($select);
    
    	$count = isset($result) ? $result : 0;
    
    	return (int) $count;
    }
    
    
    /**
     * Retrieves the shopping basket "buy now" counts   
     *
     * @return array
     *
     *      number  totalItems - total number of items in the basket
     *      number  mpdEligible - number of MPD eligible items in the basket
     *      number  discounted - number of discounted items in the basket
     *      number  complimentary - number of complimentary item sin the basket
     *
     * @access public
     */
    public function fetchBuyNowCounts($basketId = NULL)
    {
    	// Get the Basket Id, if not provided:
    	if (! $basketId) {
    			
    		$basketId = self::_getBasketId();
    	}
    	 
    	$select = $this->shoppingBasket->select()->setIntegrityCheck(false);
    
    	$select->from(array('sb' => 'ShoppingBasket'),
    			            array('totalItems' => 'SUM(sb.quantity)',
    					          'mpdEligible' => 'SUM(COALESCE(NULLIF(psp.mpdEligible, 0), 0) * sb.quantity)',
    					          'discounted' => 'SUM(FLOOR(LEAST(0.01, psp.specialPrice) * 100) * sb.quantity)',
    					          'complimentary' => 'SUM(COALESCE(NULLIF(0, psp.regularPrice), 1) * sb.quantity)'))
    					 
    		   ->joinInner(array('p' => $this->masterDB . '.Products'),
    							 'sb.productId = p.productId',
    							 array())
    								
    		   ->joinInner(array('psp' => 'ProductSalePrices'),
    							 'sb.productId = psp.productId',
    							 array())
    										
    		   ->where('sb.basketId = ?', $basketId)
    		   ->where('sb.quantity > ?', 0)
    		   ->where('sb.buyNow = ?', 1)
    		   ->where('psp.activePricing = ?', 1)
    		   ->group('sb.basketId');
    
        $row = $this->shoppingBasket->fetchRow($select);
    
        if ($row) {
    
    		return $row->toArray();
    								 
    	} else {
    								 
            return null;
    	}
    }
    
    
    /**
     * Get the basket subtotal (excluding sales tax)
     * 
     * @param string $basketId - the Basket ID
     * 
     * @return - the basket subtotal
     * 
     * @access public
     */
    public function fetchSubTotal($basketId = NULL)  
    {
        // Get the Basket Id, if not provided:
        if (! $basketId) {
        
        	$basketId = self::_getBasketId();
        }
        
        $select = $this->shoppingBasket->select()->setIntegrityCheck(false);
    
    	$select->from(array('sb' => 'ShoppingBasket'),
    			      array('SUM(COALESCE(NULLIF(psp.specialPrice, 0), psp.regularPrice) * sb.quantity)'))
    				    					  
    		   ->joinInner(array('p' => $this->masterDB . '.Products'), 
    					         'sb.productId = p.productId',
    				              array())
    							
    		   ->joinInner(array('psp' => 'ProductSalePrices'),
    						     'sb.productId = psp.productId',
    			                 array())
    							
    		   ->where('sb.basketId = ?', $basketId)
    		   ->where('sb.quantity > ?', 0)
    		   ->where('sb.buyNow = ?', 1)
    		   ->where('psp.activePricing = ?', 1)
    		   ->group('sb.basketId');
    		   
    	$total = $this->db->fetchOne($select);	   
    
        $total = isset($total) ? $total : 0;
        
        return (float) $total;
    }
    
      
    /**
     * Get the basket subtotal, including any sales tax
     * 
     * @param array $items - the basket items array
     * 
     * @return number - the basket subtotal including any sales tax
     * 
     * @access public
     */
    public function fetchSubTotalWithSalesTax($items = null)
    {
    	// Get item array is not provided:
    	if (! $items || ! is_array($items)) {
    		 
    		$items = $this->fetchProductsInBasket(1);  		 
    	}
    	 
    	// Loop through items array and apply tax to each item
    	// This avoids rounding errors on tax
    	$basketTotal = 0;
    	foreach ($items as $item) {
    		 
    		$salesTaxRate = Zend_Registry::get('salesTaxRate');
    		$salesTax = $salesTaxRate > 0 && Zend_Registry::get('salesTaxDisplay') == 1 ? sprintf('%0.2f', $item['currentPrice'] * ($salesTaxRate / 100)) : 0;
    		 
    		$basketTotal += $item['quantity'] * ($item['currentPrice'] + $salesTax);
    	}
    	 
    	return (float) $basketTotal;
    }
    
    
    /**
     * Set the country code into the session
     * 
     * @param string $countryCode - the country code
     * 
     * @access public
     */
    public function setCountryCodeInSession($countryCode)
    {    	
    	$this->cos->deliveryCountryCode = trim(strtoupper($countryCode)); 	
    	
    	// Check country VAT rate:
    	$vistorService = new Falk_VisitorService();
    	$salesTaxRate = $vistorService->getSalesTaxRateByCountry($countryCode);
    	
    	// Update VAT if rate changed:
    	if ($salesTaxRate != $this->cos->salesTaxRate && $this->cos->intraStateSale == 0) {
    	    
    	    $this->cos->salesTaxRate = $salesTaxRate;   	    
    	}
    }
    	
    	
    /**
     * Set the user provided coupon code into the session
     * 
     * @param string $voucherCode - the inputed coupon code
     * 
     * @access public
     */
    public function setCouponCodeInSession($voucherCode)
    {    	
    	// Remove all characters that are not a-z, 0-9, dash, underscore or spaces:
    	$voucherCode = preg_replace('#[^-a-zA-Z0-9_ ]#', '', $voucherCode);
    	$voucherCode = trim($voucherCode);
    	
    	// Change all dashes, underscores and spaces to dashes:
    	$voucherCode = preg_replace('#[-_ ]+#', '-', $voucherCode);
    	
    	$this->cos->voucherCode = strtoupper($voucherCode);
    }	
    
    
    /**
     * Calculate and set the discounts, delivery charges and other key values
     * in the checkout session 
     * 
     * @access public
     */
    public function setBasketSummaryInSession()
    {                
        // Instantiate service models:
        $orderService = new Falk_SalesOrderService();
        $customerService = new Falk_CustomerService();
        $discountService = new Falk_DiscountService();
        $deliveryService = new Falk_DeliveryService();
        $countryService = new Falk_CountryService();
        $auth = Zend_Auth::getInstance();
        
        // Get the Shopping Basket ID, if not set in the session:
        if (! isset($this->cos->basketId)) {
        
            $this->cos->basketId = Falk_ShoppingBasketService::_getBasketId();  
        }
              
        // Get the Visitor ID, if not set in the session:
        if (! isset($this->cos->visitorId)) {
        
            $this->cos->visitorId = Zend_Registry::get('visitorId');
        }
              
        // Get the Visitor IP, if not set in the session:
        if (! isset($this->cos->vistorIp)) {
        
            $this->cos->vistorIp = Falk_VisitorService::getAtonIpAddress();
        }
        
        // "Best guess" customer ID:
        if (! isset($this->cos->trackedCustomerId)) {
        
        	$this->cos->trackedCustomerId = Zend_Registry::get('customerId');
        }
        
        // Get the default delivery country, if not in the session:
        if (! isset($this->cos->deliveryCountryCode)) {
        
        	$this->cos->deliveryCountryCode = Zend_Registry::get('visitorCountry');
        }    
        
        // Get the tax rate, if not in the session:
        if (! isset($this->cos->salesTaxRate)) {
            
            $this->cos->salesTaxRate = Zend_Registry::get('salesTaxRate');
        }
             
        // Check if customer logged in:
        if ($auth->getIdentity()) {
        
        	$this->cos->customerLoggedin = 1;
        	$this->cos->customerId = $auth->getIdentity()->customerId;
        	$this->cos->customerEmail = $auth->getIdentity()->email;
        	$this->cos->customerHonorificId = $auth->getIdentity()->prefixId;
        	$this->cos->customerFirstName = $auth->getIdentity()->firstName;
        	$this->cos->customerLastName = $auth->getIdentity()->lastName;
        	 
        } else {
        
        	$this->cos->customerLoggedin = 0;
        	$this->cos->intraStateSale = 0;
        	$this->cos->loyaltyDiscountRate = $this->cos->loyaltyDiscount = $this->cos->loyaltyDiscountWithTax = 0;
        }
               
        // The basket item sub-total and "buy now" counts:
    	$basketSubTotal = $this->fetchSubTotal();
    	$basketSubTotalWithTax = $this->fetchSubTotalWithSalesTax();
        $buyNowCount = $this->fetchBuyNowCounts();   
        
        // Calculate any multi-piece discount:
        if (Zend_Registry::get('mpdActive') == 1 && $this->cos->loyaltyDiscountRate == 0 && ($buyNowCount['totalItems'] - $buyNowCount['complimentary'] > 1)) {
        
        	$this->cos->mpDiscountRate = $discountService->fetchMPDiscountRate($basketSubTotal);
        	$this->cos->mpDiscount = $discountService->fetchDiscountTotalByItem($this->cos->mpDiscountRate, false);
        	$this->cos->mpDiscountWithTax = $discountService->fetchDiscountTotalByItem($this->cos->mpDiscountRate, true);
        
        } else {
        
        	$this->cos->mpDiscountRate = $this->cos->mpDiscount = $this->cos->mpDiscountWithTax = 0;
        }
                         
        // Calculate the estimated delivery charge (if no delivery quote applied):
        //if($this->cos->delChargeCalculated < 1 && !empty($this->cos->deliveryQuoteCode) ) {
            $estimatedCharge = $deliveryService->calculateEstimatedCharge($basketSubTotal, $this->cos->deliveryCountryCode);    
            
            $this->cos->deliveryCountryCode = $estimatedCharge['countryCode'];
            $this->cos->delCountryName = $estimatedCharge['countryName'];
            $this->cos->formCountryCode = $estimatedCharge['formCountryCode'];    
            $this->cos->delChargeCalculated = $estimatedCharge['calculated'];
            $this->cos->estimatedDeliveryCharge = self::getTaxInclusivePrice($estimatedCharge['estimatedCharge']);
            $this->cos->freeDeliveryThreshold = self::getTaxInclusivePrice($estimatedCharge['freeThreshold']);
            $this->cos->reqForFreeDelivery = $estimatedCharge['reqForFreeDelivery']; 
            
            // If no fixed charges, clear previous selected delivery options from the session:
            if ($estimatedCharge['calculated'] <= 0) {
                
                 $this->cos->deliveryCost = 0;
                 $this->cos->deliveryChargeNet = 0;
                 $this->cos->deliveryChargeVAT = 0;       
            }
            
            // Sales Tax rate:
            $this->cos->salesTaxRateId = $estimatedCharge['salesTaxRateId'];
            $this->cos->salesTaxRate = $estimatedCharge['salesTaxRate'];         
        //}
        
        // Basket sub-total:
        $this->cos->itemCount = $buyNowCount['totalItems'];
        $this->cos->subTotal = ($basketSubTotal ? $basketSubTotal : 0);
        $this->cos->subTotalWithTax = ($basketSubTotalWithTax ? $basketSubTotalWithTax : 0);
                
        // Calculate voucher code discount (if any):      
        if (isset($this->cos->voucherCode)) {
            
            $couponService = new Falk_CouponService();
            $voucherDiscount = $this->cos->voucherDiscount = $couponService->calculateCouponDiscount($this->cos->voucherCode);
        	$this->cos->voucherDiscountWithTax = self::getTaxInclusivePrice($voucherDiscount);
        
        } else {
            
            $this->cos->voucherDiscount = $this->cos->voucherDiscountWithTax = 0;           
        }  
      
        // If customer is logged in, check for intra-state and special discounts:
        if ($auth->getIdentity()) {
        
        	// Check if customer set for intra state zero-rate VAT sales:
        	$intraState = $customerService->checkIntraStateVatStatus($auth->getIdentity()->customerId);
        	 
        	if ($intraState) {
        
        		$this->cos->intraStateSale = 1;
        		$this->cos->salesTaxRateId = 75;
        
        		Falk_VisitorService::setSalesTaxRate(0);
        		Falk_VisitorService::setTaxDisplayStatus(0);     
        	}
        
        	// Check if eligible for special customer loyalty discount:
        	$this->cos->loyaltyDiscountRate = $customerService->fetchLoyaltyDiscountRate($auth->getIdentity()->customerId);
        
        	// Calculate the special loyalty discount:
        	if ($this->cos->loyaltyDiscountRate > 0) { 
       		
        		$this->cos->loyaltyDiscount = $discountService->fetchDiscountTotalByItem($this->cos->loyaltyDiscountRate, false);
        		$this->cos->loyaltyDiscountWithTax = $discountService->fetchDiscountTotalByItem($this->cos->loyaltyDiscountRate, true);
        		
        		$this->cos->mpDiscountRate = $this->cos->mpDiscount = $this->cos->mpDiscountWithTax = 0;
        
        	} else {
        
        		$this->cos->loyaltyDiscount = $this->cos->loyaltyDiscountWithTax = 0;
        	}
        } 
             
        // Recalculate the basket total:
        $this->setBasketTotal();
    }
    
    
    /**
     * Calculate the basket total from session values and reset
     *
     * @param string $negativeAllowed - allow negative totals
     */
    public function setBasketTotal($negativeAllowed = false)
    {   	
    	// Calculate order total:
    	if (isset($this->cos->deliveryChargeNet)) {
    
    		$orderTotal = $this->cos->subTotalWithTax + $this->cos->deliveryChargeNet + $this->cos->deliveryChargeVAT - ($this->cos->voucherDiscountWithTax + $this->cos->mpDiscountWithTax + $this->cos->loyaltyDiscountWithTax);
    
    	} else {
    
    		$orderTotal = $this->cos->subTotalWithTax + $this->cos->estimatedDeliveryCharge - ($this->cos->voucherDiscountWithTax + $this->cos->mpDiscountWithTax + $this->cos->loyaltyDiscountWithTax);
    	}
    
    	if ($negativeAllowed == true) {
    
    		$this->cos->orderTotal = sprintf('%0.2f', $orderTotal);
    
    	} else {
    
    		$this->cos->orderTotal = ($orderTotal >= 0) ? sprintf('%0.2f', $orderTotal) : 0;
    	}
    }
    
    
    /**
     * Retrieve one item in the shopping basket
     *
     * @param int     $productId - the product ID
     * @param int     $buyNow  In Basket = 1, save for later = 0
     * @param string  $basketId - the Basket Id
     *
     * @return array of arrays:
     *
     * 				int      quantity - product quantity ordered
     *              int      buyNow - buynow (Y=1, Save for later = 0)
     * 				int	     productId - the product ID
     * 				string	 falkcode - Falk code
     *              string   primaryImage - image file name
     *              string   productUrl - product page URL
     *              int      defCategoryId - default category ID
     *              string   shortName - short product name
     * 				string	 displayName - product name
     * 				decimal	 regularPrice - regular price
     *              decimal  regularNetPrice - current price
     *              decimal  regularPriceVAT - current price
     *              decimal  specialPrice - current price
     *              decimal  specialNetPrice - current price
     *              decimal  specialPriceVAT - current price
     *              decimal  currentPrice - current price
     *              decimal  currentNetPrice - current price
     *              decimal  currentPriceVAT - current price
     *              boolean  mpdEligible - eligible for MP discount
     * 				int 	 freeStock - available stock
     *              int      stockOnOrder - number on order
     * 			    string	 stockArriving - date new stock arriving (formatted text)
     *
     * @access public
     */
    public function fetchSingleProduct($productId, $buyNow = NULL, $basketId = NULL)
    {
        // Get the Basket Id, if not provided:
        if (! $basketId) {
        
        	// Get the basket Id:
        	$basketId = self::_getBasketId();
        }
        
        $select = $this->shoppingBasket->select()->setIntegrityCheck(false);
        
        $select->from(array('sb' => 'ShoppingBasket'),
        		      array('sb.quantity',
                            'sb.buyNow'))
        
        	   ->joinInner(array('p' => $this->masterDB . '.Products'),
        				        'sb.productId = p.productId',
        				         array('p.productId',
        						       'p.falkCode',
        						       'primaryImage',
        						       'defCategoryId'))
        						            							                               	    	  
               ->joinInner(array('pl' => 'ProductListings'),
                				 'p.productId = pl.productId',
                			     array('productUrl' => 'pl.urlSlug',
                			          'pl.shortName',
                					  'pl.displayName'))
                					             
               ->joinInner(array('psp' => 'ProductSalePrices'),
                			     'sb.productId = psp.productId',
                			     array('psp.regularPrice',
                					   'psp.regularNetPrice',
                					   'psp.regularPriceVAT',
                					   'psp.specialPrice',
                					   'psp.specialNetPrice',
                					   'psp.specialPriceVAT',
                					   'currentPrice' => 'COALESCE(NULLIF(psp.specialPrice, 0), psp.regularPrice)',
                	                   'currentNetPrice' => 'COALESCE(NULLIF(psp.specialNetPrice, 0), psp.regularNetPrice)',
                					   'currentPriceVAT' => 'COALESCE(NULLIF(psp.specialPriceVAT, 0), psp.regularPriceVAT)',
                	                   'psp.mpdEligible'))
                					             
               ->joinInner(array('pi' => $this->masterDB . '.ProductInventory'),
                			     'p.productId = pi.productId',
                			     array('freeStock' => '(pi.stockCount - pi.stockReserved + pi.stockGhost)',
                					   'pi.stockOnOrder',
                					   'stockArriving' => new Zend_Db_Expr('DATE_FORMAT(pi.dateStockDue,"%d %b, %Y")')))
                							
               
               ->where('sb.basketId = ?', $basketId)
               ->where('sb.productId = ?' , $productId)
               ->where('psp.activePricing = ?', 1);
               
        if (isset($buyNow)) {       
        
            $select->where('sb.buyNow = ?', $buyNow);
        }     
                				
        $row = $this->shoppingBasket->fetchRow($select);
                	
        if ($row) {
                    		
            return $row->toArray();
                
        } else {
                			
            return NULL;
        }
    }  
    
    
    /**
     * Retrieve items in the shopping basket
     *
     * @param int     $buyNow - 0 = save for later
     * @param string  $basketId - the Basket Id
     *
     * @return array of arrays:
     *
     * 				int      quantity - product quantity ordered
     * 				int	     productId - the product ID
     * 				string	 falkcode - Falk code
     *              string   primaryImage - image file name
     *              string   productUrl - product page URL
     *              int      defCategoryId - default category ID
     *              string   shortName - short product name
     * 				string	 displayName - product name
     * 				decimal	 regularPrice - regular price
     *              decimal  regularNetPrice - current price
     *              decimal  regularPriceVAT - current price
     *              decimal  specialPrice - current price
     *              decimal  specialNetPrice - current price
     *              decimal  specialPriceVAT - current price
     *              decimal  currentPrice - current price
     *              decimal  currentNetPrice - current price
     *              decimal  currentPriceVAT - current price
     *              boolean  mpdEligible - eligible for MP discount
     * 				int 	 freeStock - available stock
     *              int      stockOnOrder - number on order
     * 			    string	 stockArriving - date new stock arriving (formatted text)
     *
     * @access public
     */
    public function fetchProductsInBasket($buyNow = 1, $basketId = NULL)
    {
        // Get the Basket Id, if not provided:
        if (! $basketId) {
        
            // Get the basket Id:
        	$basketId = self::_getBasketId();
        }
        	 
        $select = $this->shoppingBasket->select()->setIntegrityCheck(false);
        
        $select->from(array('sb' => 'ShoppingBasket'),
        			  array('sb.quantity'))
        
        	   ->joinInner(array('p' => $this->masterDB . '.Products'),
        				         'sb.productId = p.productId',
        					     array('p.productId',
        							   'p.falkCode',
        							   'primaryImage',
        	                           'defCategoryId'))
        							                               	    	  
               ->joinInner(array('pl' => 'ProductListings'),
        					     'p.productId = pl.productId',
        					     array('productUrl' => 'pl.urlSlug',
        					           'pl.shortName',
        					           'pl.displayName'))
        					             
        	   ->joinInner(array('psp' => 'ProductSalePrices'),
        					     'sb.productId = psp.productId',
        					     array('psp.regularPrice',
        					          // 'psp.regularNetPrice',
        					          // 'psp.regularPriceVAT',
        					           'psp.specialPrice',
        					          // 'psp.specialNetPrice',
        					          // 'psp.specialPriceVAT',
        					           'currentPrice' => 'COALESCE(NULLIF(psp.specialPrice, 0), psp.regularPrice)',
        	                           //'currentNetPrice' => 'COALESCE(NULLIF(psp.specialNetPrice, 0), psp.regularNetPrice)',
        					           //'currentPriceVAT' => 'COALESCE(NULLIF(psp.specialPriceVAT, 0), psp.regularPriceVAT)',
        	                           'psp.mpdEligible'))
        					             
        	   ->joinInner(array('pi' => $this->masterDB . '.ProductInventory'),
        					     'p.productId = pi.productId',
        					      array('freeStock' => '(pi.stockCount - pi.stockReserved + pi.stockGhost)',
        					            'pi.stockOnOrder',
        					            'stockArriving' => new Zend_Db_Expr('DATE_FORMAT(pi.dateStockDue,"%d %b, %Y")')))
        					             
        	   ->joinLeft(array('ps' => $this->masterDB . '.ProductSpecifications'),
        					    'p.productId = ps.productId',
        					    array())
        							
        	   ->where('sb.basketId = ?', $basketId)
        	   ->where('sb.quantity > ?', 0)
        	   ->where('sb.buyNow = ?', $buyNow)
        	   ->order('ps.isCollection DESC')
        	   ->order('ps.isLid')
        	   ->order('p.defCategoryId')
        	   ->where('psp.activePricing = ?', 1)
        	   ->order('p.falkCode');
        				
        $rowSet = $this->shoppingBasket->fetchAll($select);
        	
        if ($rowSet) {
            		
            return $rowSet->toArray();
        
        } else {
        			
            return NULL;
        }
    }  
        
    
    /**
     * Add the product to the table - if none with same
     * product ID inserts new record, otherwise the quantity
     * field on the existing record is adjusted
     *
     * @param int  $productId - the product Id
     * @param int  $quantity - quantity to add
     *
     * @access public
     */
    public function addProduct($productId, $quantity = 1)
    {
    	// Get the Basket Id:
    	$basketId = self::_getBasketId();
    	 
    	// Check if customer Id available:
    	$customerId = Zend_Registry::get('customerId');
    
    	// If customer Id not retrieved:
    	if(! $customerId) {
    		 
    		// Try and get this from existing basket:
    		$select = $this->shoppingBasket->select();
    		$select->from('ShoppingBasket', 'customerId')
    		       ->where('basketId = ?', $basketId)
    		       ->limit(1);
    		 
    		$row = $this->shoppingBasket->fetchRow($select);
    		
    		$customerId = ($row ? $row->customerId : 0);
    	}
    	  
    	// Count matching products in basket already:
    	$select = $this->shoppingBasket->select();
    	$select->from('ShoppingBasket', 'quantity')
    	       ->where('basketId = ?', $basketId)
    	       ->where('productId = ?', $productId);
    
    	$result = $this->db->fetchOne($select);
    	$count = ($result ? $result : 0);
    
    	// If the product is already in the basket
    	// update the existing total:
    	if ($result) {
    	    
    	    // If removing last item from basket:
    	    if ($result == 1 && $quantity == -1) {
    	        
    	        $this->removeProduct($productId);
    	        
    	        // Check that there are not just bonus comp items left in basket:
    	        $this->clearBonusProductsOnly($basketId);
    	        
    	    } else {
    
        		$data = array('quantity'      => $count + $quantity,
        				      'customerId'    => $customerId,
        		              'buyNow'        => 1, // will reset "saved for later" items - to avoid some of each
        				      'addedUpdated'  => new Zend_Db_Expr('NOW()'));
        
        		$where = array();
        		$where[] = $this->shoppingBasket->getAdapter()->quoteInto('basketId = ?', $basketId);
        		$where[] = $this->shoppingBasket->getAdapter()->quoteInto('productId = ?', $productId);
        
        		$this->shoppingBasket->update($data, $where);
        		
    	    }
    	
    	} else { // Create new entry:
    
    		$params = array('basketId' 	  => $basketId,
    				        'productId'   => $productId,
    				        'customerId'  => $customerId,
    				        'quantity'    => $quantity,
    		                'buyNow'      => 1,
    				        'addedUpdated'     => new Zend_Db_Expr('NOW()'));
    
    		$this->shoppingBasket->insert($params);
    	} 	
    } 
        
 
    /**
     * Removes a product from the shopping basket table
     * 
     * @param int  $productId - the product ID
     * 
     * @access public
     */
    public function removeProduct($productId) 
    {
    	// Get the Basket Id:
    	$basketId = self::_getBasketId();
    	
    	$where = array();
		$where[] = $this->shoppingBasket->getAdapter()->quoteInto('basketId = ?', $basketId);
		$where[] = $this->shoppingBasket->getAdapter()->quoteInto('productId = ?', $productId);
			
    	$this->shoppingBasket->delete($where, 1);
    	
    	// Check that there are not just bonus comp items left in basket:
    	$this->clearBonusProductsOnly($basketId);
    }
    
    
    /**
     * Change item status from "buy now" to "save for later"
     * - will also remove bonus/comp items if these are all that left
     *
     * @param int  $productId - the product Id
     *
     * @return int - number of items left in Basket
     *
     * @access public
     */
    public function saveProductForLater($productId)
    {
    	// Get the Basket Id:
    	$basketId = self::_getBasketId();
    
    	// Check if customer Id available:
    	$customerId = Zend_Registry::get('customerId');
    
    	// If customer Id not retrieved:
    	if(! $customerId) {
    
    		// Try and get this from existing basket:
    		$select = $this->shoppingBasket->select();
    		$select->from('ShoppingBasket', 'customerId')
    		->where('basketId = ?', $basketId)
    		->limit(1);
    
    		$row = $this->shoppingBasket->fetchRow($select);
    
    		$customerId = ($row ? $row->customerId : 0);
    	}
    
    	// Change the item status:
    	$data = array('customerId'    => $customerId,
    			'buyNow'        => 0,
    			'addedUpdated'  => new Zend_Db_Expr('NOW()'));
    
    	$where = array();
    	$where[] = $this->shoppingBasket->getAdapter()->quoteInto('basketId = ?', $basketId);
    	$where[] = $this->shoppingBasket->getAdapter()->quoteInto('productId = ?', $productId);
    
    	$this->shoppingBasket->update($data, $where);
    
    	// Check that there are not just bonus/comp items left in basket:
    	$this->clearBonusProductsOnly($basketId);
    
    	// Return number of items remaining with "buy now" status:
    	return (int) $this->fetchItemCount($basketId);
    }
    
    
    /**
     * Change item status from "save for later" to "buy now"
     *
     * @param int  $productId - the product Id
     *
     * @access public
     */
    public function moveProductBackToBasket($productId)
    {
    	// Get the Basket Id:
    	$basketId = self::_getBasketId();
    
    	// Check if customer Id available:
    	$customerId = Zend_Registry::get('customerId');
    
    	// If customer Id not retrieved:
    	if(! $customerId) {
    
    		// Try and get this from existing basket:
    		$select = $this->shoppingBasket->select();
    		$select->from('ShoppingBasket', 'customerId')
    		->where('basketId = ?', $basketId)
    		->limit(1);
    
    		$row = $this->shoppingBasket->fetchRow($select);
    
    		$customerId = ($row ? $row->customerId : 0);
    	}
    
    	// Change the item status to buy now:
    	$data = array('customerId'    => $customerId,
    			'buyNow'        => 1,
    			'addedUpdated'  => new Zend_Db_Expr('NOW()'));
    
    	$where = array();
    	$where[] = $this->shoppingBasket->getAdapter()->quoteInto('basketId = ?', $basketId);
    	$where[] = $this->shoppingBasket->getAdapter()->quoteInto('productId = ?', $productId);
    
    	$this->shoppingBasket->update($data, $where);
    }
    
       
    /**
     * Checks whether specified product is in the shopping basket
     * - used to validated discount coupons
     *
     * @param int $productId - the Product ID
     * 
     * @return int - count of specified product in the basket
     * 
     * @access public
     */
	public function checkProductInBasket($productId)
    {
    	// Get the Basket Id:
    	$basketId = self::_getBasketId();
    	
    	// Build the query:
		$select = $this->shoppingBasket->select();
		
		$select->from(array('sb' => 'ShoppingBasket'), 
					  'sb.quantity')
							
				->where('sb.basketId = ?', $basketId)
				->where('sb.productId = ?', $productId)
				->where('sb.buyNow = ?', 1);
				
    	$result = $this->db->fetchOne($select);
		
		// Return the result:
		return (int) $result;
    }
    
    
    /**
     * Check if order eligible for free bottle of Bar Keepers Friend
     *
     * @param int $productId - the BKF product ID (bonus version, not one for sale)
     * @param int $minimumSpend - minimum order amount to qualify
     *
     * @return boolean
     *
     * @access public
     */
    public function checkBKFBonusEligible($productId, $minimumSpend)
    {
    	// Get the Basket Id:
    	$basketId = self::_getBasketId();
    
    	// Check if already in the basket:
    	$select = $this->shoppingBasket->select();
    
    	$select->from(array('sb' => 'ShoppingBasket'),
    			'sb.quantity')
    			 
    			->where('sb.basketId = ?', $basketId)
    			->where('sb.productId = ?', $productId)
    			->where('sb.buyNow = ?', 1);
    
    	$inBasket = $this->db->fetchOne($select);
    
    	if ($inBasket > 0) {
    
    		return false;
    	}
    
    	// Check for minimum spend:
    	$select = $this->shoppingBasket->select()->setIntegrityCheck(false);
    
    	$select->from(array('sb' => 'ShoppingBasket'),
    			           'SUM(COALESCE(NULLIF(psp.specialPrice, 0), psp.regularPrice) * sb.quantity)')
    				
    		   ->joinInner(array('p' => $this->masterDB . '.Products'),
    					         'sb.productId = p.productId',
    					         array())
    					 
    		   ->joinInner(array('psp' => 'ProductSalePrices'),
    							 'sb.productId = psp.productId',
    							 array())
    								
    		   ->where('sb.basketId = ?', $basketId)
    		   ->where('sb.buyNow = ?', 1)
    		   ->group('sb.basketId');
    
        $spend = $this->db->fetchOne($select);
    
    	if ($spend >= $minimumSpend) {
    
    	   return true;
    
    	} else {
    
    	   return false;
    	}
    }
    
    
    /**
     * Check if order eligible for free set of Papillon Pan Protectors
     *
     * @param int $productId - the PPP product ID (bonus version, not one for sale)
     * @param int $minimumCount - minimum number of pans to qualify
     *
     * @return boolean
     *
     * @access public
     */
    public function checkPPPBonusEligible($productId, $minimumCount)
    {
    	// Get the Basket Id:
    	$basketId = self::_getBasketId();
    
    	// Check if already in the basket:
    	$select = $this->shoppingBasket->select();
    
    	$select->from(array('sb' => 'ShoppingBasket'),
    			'sb.quantity')
    
    			->where('sb.basketId = ?', $basketId)
    			->where('sb.productId = ?', $productId)
    			->where('sb.buyNow = ?', 1);
    
    	$inBasket = $this->db->fetchOne($select);
    
    	if ($inBasket > 0) {
    
    		return false;
    	}
    
    	// Check for minimum count quantity:
    	$select = $this->shoppingBasket->select()->setIntegrityCheck(false);
    
    	$select->from(array('sb' => 'ShoppingBasket'),
    			            'SUM(sb.quantity)')
    
    		   ->joinInner(array('ps' => $this->masterDB . '.ProductSpecifications'),
    					         'sb.productId = ps.productId',
    					         array())
    
    		   ->where('sb.basketId = ?', $basketId)
    		   ->where('sb.buyNow = ?', 1)
    		   ->where('ps.isLid = ?', 0)
    		   ->where('ps.isCollection = ?', 0)
    		   ->group('sb.basketId');
    
    	$count = $this->db->fetchOne($select);
    
    	if ($count >= $minimumCount) {
    
    	   return true;
    
    	} else {
    
    	   return false;
    	}
    }
    

    /**
     * Checks basket contents and clears complimnetary/bonus items if these
     * are all that are left in it
     *
     * @param string $basketId - the basket Id
     *
     * @access private
     */
    private function clearBonusProductsOnly($basketId = NULL)
    {
    	// Get the Basket Id, if not provided:
    	if (! $basketId) {
    
    		$basketId = self::_getBasketId();
    	}
    
    	$select = $this->shoppingBasket->select()->setIntegrityCheck(false);
    	$select->from(array('sb' => 'ShoppingBasket'),
    			             array('SUM(COALESCE(NULLIF(psp.specialPrice, 0), psp.regularPrice) * sb.quantity)'))
    
    		   ->joinInner(array('psp' => 'ProductSalePrices'),
    					         'sb.productId = psp.productId',
    					         array())
    
    		   ->where('sb.basketId = ?', $basketId)
    		   ->where('sb.buyNow = ?', 1);
    
        $basketTotal = $this->db->fetchOne($select);
    				
    	// If there is nothing chargeable in the basket:
    	if ($basketTotal == 0) {
    
    		// Remove whatever is left (but not "saved for later items"):
    		$where = array();
    		$where[] = $this->shoppingBasket->getAdapter()->quoteInto('basketId = ?', $basketId);
    		$where[] = $this->shoppingBasket->getAdapter()->quoteInto('buyNow = ?', 1);
    					
    	   $this->shoppingBasket->delete($where);	
    	}
    }
    
    
    /**
     * Sets the Customer Id to all of the customer's shopping basket items:
     *
     * @param int $customerId - the customer Id
     *
     * @access public
     */
    public function setItemsToCustomer($customerId)
    {
    	// Get the Basket Id:
    	$basketId = self::_getBasketId();
    
    	// Update records:
    	$data = array('customerId' => $customerId);
    
    	$where = $this->shoppingBasket->getAdapter()->quoteInto('basketId = ?', $basketId);
    
    	// Execute query:
    	$this->shoppingBasket->update($data, $where);
    }
    
    
    /**
     * Check whether the Shopping Basket ID matches the customer record
     *
     * This is will enable customer to view basket on other broswers by
     * logging in, or without the cookie
     *
     * If the these do not match, update the table and reset cookie and session value
     *
     * @param string $customerBasketId - the customer basket Id
     *
     * @access public
     */
    public function matchIdToCustomer($customerBasketId)
    {
    	// If Basket Id and customer record not in sync:
    	if ($customerBasketId != self::_getBasketId()) {
    
    		// Update any table rows with correct basket ID:
    		$data = array('basketId' => $customerBasketId);
    		$where = $this->shoppingBasket->getAdapter()->quoteInto('basketId = ?', self::_getBasketId());
    		$this->shoppingBasket->update($data, $where);
    
    		// Set or regenerate the cookie:
    		Falk_Visitor_Tracking_Cookie::set('Basket', $customerBasketId, 1095);
    
    		// Update the session:
    		$session = new Zend_Session_Namespace('falk.basket');
    		$session->basketId = $customerBasketId;
    	}
    }
    
    
    /**
     * Return price (including any sales tax) from registry setting
     *
     * @param number $basePrice
     *
     * @return decimal the tax inclusive price
     *
     * @access public
     */
    public static function getTaxInclusivePrice($basePrice)
    {
    	$basePrice = trim($basePrice);
    
    	if (is_numeric($basePrice)) {
    
    		$salesTaxRate = Zend_Registry::get('salesTaxRate');
    		$salesTax = $salesTaxRate > 0 ? sprintf('%0.2f', $basePrice  * ($salesTaxRate / 100)) : 0;
    
    		return sprintf('%0.2f', $basePrice + $salesTax);
    	}
    }
    
   
    

    
    
    
    
       
    
    /**
     * Empty the shopping basket 
     * - after checkout
     * 
     * @access public
     */
    public function emptyBasket()
    {
    	// Get the Basket Id:
    	$basketId = self::_getBasketId();
    	
    	// Run the delete query:
    	$where = $this->shoppingBasket->getAdapter()->quoteInto('basketId = ?', $basketId);
    	$this->shoppingBasket->delete($where);
    	
    	// Generate a new Basket ID:
    	self::$_mBasketId = md5(uniqid(rand(), true));
    		
    	// Store Basket Id in session:
    	$session = new Zend_Session_Namespace('falk.basket');
    	$session->basketId = self::$_mBasketId;
    	
    	// Delete basket cookie:
    	Falk_Visitor_Tracking_Cookie::delete('Basket');
    }  
}