<?php
/**
* @version	$Id: custom_shipping_quote_engine.php 16516 2017-01-20 14:12:22Z alex $
* @package	In-Commerce
* @copyright	Copyright (C) 1997 - 2009 Intechnic. All rights reserved.
* @license	Commercial License
* This software is protected by copyright law and international treaties.
* Unauthorized reproduction or unlicensed usage of the code of this program,
* or any portion of it may result in severe civil and criminal penalties,
* and will be prosecuted to the maximum extent possible under the law
* See http://www.in-portal.org/commercial-license for copyright notices and details.
*/

defined('FULL_PATH') or die('restricted access!');

class CustomShippingQuoteEngine extends ShippingQuoteEngine {

	function GetShippingQuotes($params)
	{
		$packages = $params['packages'];
		$default_pack = array_shift($packages);
		$query = $this->QueryShippingCost($params['dest_country'], $params['dest_state'], $params['dest_postal'], $default_pack['weight'], $params['items'], $params['amount'], $params['shipping_type'], $params['promo_params']);
		$shipping_types = $this->Conn->Query($query, 'ShippingId');

		if (!$this->Application->isAdminUser) {
			$user_groups = explode(',', $this->Application->RecallVar('UserGroups'));
			$filteres_shipping_types = array();
			foreach ($shipping_types as $key=>$shipping_type) {
				$shipping_type_groups = explode(',', trim($shipping_type['PortalGroups'], ','));
				if (array_intersect($shipping_type_groups, $user_groups)) {
					$filteres_shipping_types[$key] = $shipping_type;
				}
			}
			$shipping_types = $filteres_shipping_types;
		}

		return $shipping_types;
	}

	function QueryShippingCost($user_country, $user_state, $user_zip, $weight, $items, $amount, $shipping_type=null, $promo_params)
	{
		/** @var kCountryStatesHelper $cs_helper */
		$cs_helper = $this->Application->recallObject('CountryStatesHelper');

		$user_country_id = $cs_helper->getCountryStateId($user_country, DESTINATION_TYPE_COUNTRY);
		$user_state_id = $cs_helper->getCountryStateId($user_state, DESTINATION_TYPE_STATE);

		$user_zip = (string) $user_zip;
		$weight = (float) $weight;
		$items = (int) $items;
		$amount = (float) $amount;

		$promo_weight = $promo_params['weight'];
		$promo_amount = (float) $promo_params['amount'];
		$promo_items = (float) $promo_params['items'];

		if (isset($shipping_type)) $shipping_type = (int) $shipping_type;

		$query = 'SELECT
									CONCAT("CUST_", st.ShippingID) as ShippingId,
									IF(st.Type = 4, st.BaseFee, '.# taking only base fee for handling
										'IF (
												st.IsFreePromoShipping = 1 AND
												( (st.FreeShippingMinAmount != 0 AND st.FreeShippingMinAmount <= '.$amount.') OR
												(st.Type = 1 AND '.$promo_weight.' = 0) OR
												(st.Type = 2 AND '.$promo_items.' = 0) OR
												(st.Type = 3 AND '.$promo_amount.' = 0)
												), 0,
											MIN(
											IF(st.BaseFee IS NULL, 0, st.BaseFee) +
											IF(st.CostType IN (1,3), IF(sc.Flat IS NULL, 0, sc.Flat), 0) +
											IF(st.CostType IN (2,3),
												IF(sc.PerUnit IS NULL, 0, sc.PerUnit)
												*
												( CASE st.Type
													WHEN 1 THEN IF(st.IsFreePromoShipping = 1, '.$promo_weight.', '.$weight.')
													WHEN 2 THEN IF(st.IsFreePromoShipping = 1, '.$promo_items.', '.$items.')
													WHEN 3 THEN IF(st.IsFreePromoShipping = 1, '.$promo_amount.', '.$amount.')
													ELSE 0 END ),
												0)
											)
										)
									) AS TotalCost,
									st.Name as ShippingName,
									st.Type as Type,
									st.CODFlatSurcharge as CODFlat,
									st.CODPercentSurcharge as CODPercent,
									st.PortalGroups as PortalGroups,
									IF (
										st.InsuranceType = 1,
										st.InsuranceFee,
										st.InsuranceFee * '.$amount.' / 100
									) AS InsuranceFee,
									IF(sz.CODallowed IS NULL, 0, sz.CODallowed) AS COD,
									st.Status = 2 as SelectedOnly,
									st.Code
								FROM '.TABLE_PREFIX.'ShippingType AS st '. 																#all shipping types
								'LEFT JOIN '.TABLE_PREFIX.'ShippingBrackets AS sb	'.											#getting brackets by shipping type
								'ON sb.ShippingTypeID = st.ShippingID	'.
								'LEFT JOIN '.TABLE_PREFIX.'ShippingZones AS sz '.													#getting zones by shipping type
								'ON sz.ShippingTypeID = st.ShippingID '.
								'LEFT JOIN '.TABLE_PREFIX.'ShippingZonesDestinations AS szd	'.						#getting destinations by shipping type
								'ON szd.ShippingZoneId = sz.ZoneID '.
								'LEFT JOIN '.TABLE_PREFIX.'ShippingCosts AS sc	'.													#getting costs by bracket and zone
								'ON sc.BracketId = sb.BracketId AND sc.ZoneID = sz.ZoneID '.
								'WHERE
									'.(isset($shipping_type) ? 'st.ShippingID = '.$shipping_type.' AND ' : '').'
									(st.Status >= 1) '.		# enabled (1) or Selected Only (2) shipping types
									'AND
									(
										'.# handlign should require brackets/zones (st.Type = 4) # handling - does not required brackets
										# OR
										'(
											( st.Type IN (1,2,3,4) ) '. # bracket dependant types
											'AND
											'.# Zone match
											'(	(sz.Type = 1 AND
													(szd.StdDestId = '.$user_country_id.') '.# user country id
												')
												OR
												(sz.Type = 2 AND
													(szd.StdDestId = '.$user_state_id.') '.# user state id
												')
												OR
												(sz.Type = 3 AND
													(szd.StdDestId = '.$user_country_id.') '.# user country id
													'AND
													(szd.DestValue = '.$this->Conn->qstr($user_zip).') '.# user zip code
												')
											)
											AND
											'.# Bracket match
											'( (st.Type = 1
													AND
												 sb.Start <= IF(st.IsFreePromoShipping = 1, '.$promo_weight.', '.$weight.') AND (sb.End > IF(st.IsFreePromoShipping = 1, '.$promo_weight.', '.$weight.') OR sb.End = -1) '.# items total weight
												')
												OR
												(st.Type = 2
													AND
												 sb.Start <= IF(st.IsFreePromoShipping = 1, '.$promo_items.', '.$items.') AND (sb.End > IF(st.IsFreePromoShipping = 1, '.$promo_items.', '.$items.') OR sb.End = -1) '.# items total qty
												')
												OR
												(st.Type = 3
													AND
												 sb.Start <= IF(st.IsFreePromoShipping = 1, '.$promo_amount.', '.$amount.') AND (sb.End > IF(st.IsFreePromoShipping = 1, '.$promo_amount.', '.$amount.') OR sb.End = -1) '.# items total amount
												')
												OR
												st.Type = 4 '.# handling - does not depend on brackets
											')
											AND
											'.# Empty costs handling
											'( (st.ZeroIfEmpty = 0	AND (sc.Flat IS NOT NULL OR sc.PerUnit IS NOT NULL) ) '.# if no shimpent on empty - flat or perunit should be filled in
												'OR
												(st.ZeroIfEmpty = 1)	'.		# zero on empty - is ok (nulls will be converted in SELECT part)
												'OR
												(st.Type = 4) 	'.				# ignore costs for handling
											')
										)
									)
								GROUP BY st.ShippingId 			'.	# getting minimal price (possible with closest address match) for every shipping type
								'ORDER BY TotalCost asc';
		return $query;
	}

	function GetAvailableTypes()
	{
		$conn =& $this->Application->GetADODBConnection();
		$types = $conn->Query('SELECT * FROM '.TABLE_PREFIX.'ShippingType');

		$ret = array();
		foreach ($types as $a_type) {
			$a_type['_ClassName'] = get_class($this);
			$a_type['_Id'] = 'CUST_'.$a_type['ShippingID'];
			$a_type['_Name'] = '(Custom) '.$a_type['Name'];
			$ret[] = $a_type;
		}
		return $ret;

	}
}