<?php

class CustomShippingQuoteEngine extends ShippingQuoteEngine {

	function GetShippingQuotes($params)
	{
		$db =& $this->Application->GetADODBConnection();
		$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']);
		$shipping_types = $db->Query($query, 'ShippingId');

		if (!$this->Application->IsAdmin()) {
			$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_abbr, $user_state_abbr, $user_zip, $weight, $items, $amount, $shipping_type=null)
	{
		$db =& $this->Application->GetADODBConnection();

		$user_country_id = (int) $db->GetOne('SELECT DestId FROM '.TABLE_PREFIX.'StdDestinations WHERE DestType=1 AND DestAbbr = '.$db->qstr($user_country_abbr));
		$user_state_id = (int) $db->GetOne('SELECT DestId FROM '.TABLE_PREFIX.'StdDestinations WHERE DestType=2 AND DestAbbr = '.$db->qstr($user_state_abbr));
		$user_zip = (string) $user_zip;
		$weight = (float) $weight;
		$items = (int) $items;
		$amount = (float) $amount;
		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
										'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 '.$weight.' WHEN 2 THEN '.$items.' WHEN 3 THEN '.$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(sz.CODallowed IS NULL, 0, sz.CODallowed) AS COD,
									st.Status = 2 as SelectedOnly
								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 = '.$db->qstr($user_zip).') '.# user zip code
												')
											)
											AND
											'.# Bracket match
											'( (st.Type = 1
													AND
												 sb.Start <= '.$weight.' AND (sb.End > '.$weight.' OR sb.End = -1) '.# items total weight
												')
												OR
												(st.Type = 2
													AND
												 sb.Start <= '.$items.' AND (sb.End > '.$items.' OR sb.End = -1) '.# items total qty
												')
												OR
												(st.Type = 3
													AND
												 sb.Start <= '.$amount.' AND (sb.End > '.$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;

	}

}

?>