<?php
/**
 * Magento Extension by TRIC Solutions
 *
 * @copyright  Copyright (c) 2012 TRIC Solutions (http://www.tric.dk)
 * @license    http://www.tric.dk/TRIC-LICENSE-COMMUNITY.txt
 * @store       http://store.tric.dk
 */

class TRIC_GLS_Model_Mysql4_Rate extends Mage_Core_Model_Mysql4_Abstract
{
	protected function _construct() {

		$this->_init('gls/rate', 'pk');
	}

	protected function _beforeSave(Mage_Core_Model_Abstract $object) {
		parent::_beforeSave($object);
		return $this;
	}

	public function loadByRequest(TRIC_GLS_Model_Rate $rates, Varien_Object $request)
	{
		$adapter = $this->_getReadAdapter();
		$select = $adapter->select()->from($this->getMainTable());
		$conditions = array();
		if ($request->getId()) array_push($conditions, '(pk != '.$adapter->quote($request->getId()).')');
		$websiteId = ($request->getWebsiteId()) ? $request->getWebsiteId() : '0';
		$destCountryId = ($request->getDestCountryId()) ? $request->getDestCountryId() : '0';
		$destRegionId = ($request->getDestRegionId()) ? $request->getDestRegionId() : '0';
		$destZip = ($request->getDestZip()) ? $request->getDestZip() : '';
		$conditionName = ($request->getConditionName()) ? $request->getConditionName() : '';
		$conditionFromValue = ($request->getConditionFromValue()) ? $request->getConditionFromValue() : '';
		$conditionToValue = ($request->getConditionToValue()) ? $request->getConditionToValue() : '';
		array_push($conditions, '(website_id = '.$adapter->quote($websiteId).')');
		array_push($conditions, '(dest_country_id LIKE '.$adapter->quote("%,".$destCountryId.",%").')');
		array_push($conditions, '(dest_region_id = '.$adapter->quote($destRegionId).')');
		array_push($conditions, '(dest_zip = '.$adapter->quote($destZip).')');
		array_push($conditions, '(condition_name = '.$adapter->quote($conditionName).')');
		array_push($conditions, '(condition_from_value = '.$adapter->quote($conditionFromValue).')');
		array_push($conditions, '(condition_to_value = '.$adapter->quote($conditionToValue).')');
		$select->where(implode(' AND ', $conditions));
		$select->limit(1);
		$row = $adapter->fetchRow($select);
		if ($row && !empty($row)) $rates->setData($row);
		$this->_afterLoad($rates);
		return $this;
	}

	public function getNewRate(Mage_Shipping_Model_Rate_Request $request,$zipRangeSet=0)
	{

		$read = $this->_getReadAdapter();
		$write = $this->_getWriteAdapter();

		$postcode = $request->getDestPostcode();
		$table = Mage::getSingleton('core/resource')->getTableName('gls/rate');

		if ($zipRangeSet && is_numeric($postcode)) {
			$zipSearchString = ' AND '.$postcode.' BETWEEN dest_zip AND dest_zip_to )';
		} else {
			$zipSearchString = $read->quoteInto(" AND ? LIKE dest_zip )", $postcode);
		}

		for ($j=0;$j<10;$j++)
		{

			$select = $read->select()->from($table);
			switch($j) {
			case 0:
				$select->where(
					$read->quoteInto(" (dest_country_id LIKE ? ", "%,".$request->getDestCountryId().",%").
					$read->quoteInto(" AND dest_region_id=? ", $request->getDestRegionId()).
					$read->quoteInto(" AND STRCMP(LOWER(dest_city),LOWER(?)) = 0  ", $request->getDestCity()).
					$zipSearchString
				);
				break;
			case 1:
				$select->where(
					$read->quoteInto(" (dest_country_id LIKE ? ", "%,".$request->getDestCountryId().",%").
					$read->quoteInto(" AND dest_region_id=?  AND dest_city=''", $request->getDestRegionId()).
					$zipSearchString
				);
				break;
			case 2:
				$select->where(
					$read->quoteInto(" (dest_country_id LIKE ? ", "%,".$request->getDestCountryId().",%").
					$read->quoteInto(" AND dest_region_id=? ", $request->getDestRegionId()).
					$read->quoteInto(" AND STRCMP(LOWER(dest_city),LOWER(?)) = 0  AND dest_zip='')", $request->getDestCity())
				);
				break;
			case 3:
				$select->where(
					$read->quoteInto("  (dest_country_id LIKE ? ", "%,".$request->getDestCountryId().",%").
					$read->quoteInto(" AND STRCMP(LOWER(dest_city),LOWER(?)) = 0  AND dest_region_id='0'", $request->getDestCity()).
					$zipSearchString
				);
				break;
			case 4:
				$select->where(
					$read->quoteInto("  (dest_country_id LIKE ? ", "%,".$request->getDestCountryId().",%").
					$read->quoteInto(" AND STRCMP(LOWER(dest_city),LOWER(?)) = 0  AND dest_region_id='0' AND dest_zip='') ", $request->getDestCity())
				);
				break;
			case 5:
				$select->where(
					$read->quoteInto("  (dest_country_id LIKE ? AND dest_region_id='0' AND dest_city='' ", "%,".$request->getDestCountryId().",%").
					$zipSearchString
				);
				break;
			case 6:
				$select->where(
					$read->quoteInto("  (dest_country_id LIKE ? ", "%,".$request->getDestCountryId().",%").
					$read->quoteInto(" AND dest_region_id=? AND dest_city='' AND dest_zip='') ", $request->getDestRegionId())
				);
				break;

			case 7:
				$select->where(
					$read->quoteInto("  (dest_country_id LIKE ? AND dest_region_id='0' AND dest_city='' AND dest_zip='') ", "%,".$request->getDestCountryId().",%")
				);
				break;
			case 8:
				$select->where(
					"  (dest_country_id='0' AND dest_region_id='0'".
					$zipSearchString
				);
				break;

			case 9:
				$select->where(
					"(dest_country_id='0' AND dest_region_id='0' AND dest_city='')"
				);
				break;
			}


			if (is_array($request->getConditionName())) {
				$i = 0;
				foreach ($request->getConditionName() as $conditionName) {
					if ($i == 0) {
						$select->where('condition_name=?', $conditionName);
					} else {
						$select->orWhere('condition_name=?', $conditionName);
					}
					$select->where('condition_from_value<=?', $request->getData($conditionName));


					$i++;
				}
			} else {
				$select->where('condition_name=?', $request->getConditionName());
				$select->where('condition_from_value<=?', $request->getData($request->getConditionName()));
				$select->where('condition_to_value>=?', $request->getData($request->getConditionName()));
			}

			$select->where('website_id=?', $request->getWebsiteId());
			$select->where('active=?', '1');

			$select->order('sort_order ASC');
			$select->order('dest_country_id DESC');
			$select->order('dest_region_id DESC');
			$select->order('dest_zip DESC');
			$select->order('condition_from_value DESC');


			$newdata=array();
			$row = $read->fetchAll($select);
			if (!empty($row))
			{
				foreach ($row as $data) {
					$newdata[]=$data;
				}
				break;
			}
		}

		return $newdata;

	}

	public function uploadAndImport(Varien_Object $object)
	{
		$csvFile = $_FILES["groups"]["tmp_name"]["gls"]["fields"]["import"]["value"];

		if (!empty($csvFile)) {

			$csv = trim(file_get_contents($csvFile));

			$table = Mage::getSingleton('core/resource')->getTableName('gls_shipping/rate');

			$websiteId = $object->getScopeId();
			$websiteModel = Mage::app()->getWebsite($websiteId);

			/*
if (isset($_POST['groups']['gls']['fields']['condition_name']['inherit'])) {
				$conditionName = (string)Mage::getConfig()->getNode('default/carriers/gls/condition_name');
			} else {
				$conditionName = $_POST['groups']['gls']['fields']['condition_name']['value'];
			}
*/
			
			if (!empty($csv)) {
				$exceptions = array();
				$csvLines = explode("\n", $csv);
				$csvLine = array_shift($csvLines);
				$csvLine = $this->_getCsvValues($csvLine);
				if (count($csvLine) < 7) {
					$exceptions[0] = Mage::helper('gls')->__("Invalid GLS Rates File Format");
				}

				$countryCodes = array();
				$regionCodes = array();
				foreach ($csvLines as $k=>$csvLine) {
					$csvLine = $this->_getCsvValues($csvLine);
					if (count($csvLine) > 0 && count($csvLine) < 7) {
						$exceptions[0] = Mage::helper('gls')->__('Invalid GLS Rates File Format');
					} else {
						$countryCodes[] = $csvLine[0];
						$regionCodes[] = $csvLine[1];
					}
				}

				if (empty($exceptions)) {
					$data = array();
					$countryCodesToIds = array();
					$regionCodesToIds = array();
					$countryCodesIso2 = array();

					$countryCollection = Mage::getResourceModel('directory/country_collection')->addCountryCodeFilter($countryCodes)->load();
					foreach ($countryCollection->getItems() as $country) {
						$countryCodesToIds[$country->getData('iso3_code')] = $country->getData('country_id');
						$countryCodesToIds[$country->getData('iso2_code')] = $country->getData('country_id');
						$countryCodesIso2[] = $country->getData('iso2_code');
					}

					$regionCollection = Mage::getResourceModel('directory/region_collection')
					->addRegionCodeFilter($regionCodes)
					->addCountryFilter($countryCodesIso2)
					->load();

					foreach ($regionCollection->getItems() as $region) {
						$regionCodesToIds[$countryCodesToIds[$region->getData('country_id')]][$region->getData('code')] = $region->getData('region_id');
					}
					
					
					foreach ($csvLines as $k=>$csvLine) {

						$csvLine = $this->_getCsvValues($csvLine);

						$countryId = $csvLine[0];
						

						if (!isset($countryCodesToIds[$csvLine[0]])
							|| !isset($regionCodesToIds[$countryCodesToIds[$csvLine[0]]])
							|| !array_key_exists($csvLine[1], $regionCodesToIds[$countryCodesToIds[$csvLine[0]]])) {
							$regionId = '0';
							if ($csvLine[1] != '*' && $csvLine[1] != '' && $csvLine[0] != '0') {
								$exceptions[] = Mage::helper('gls')->__("Invalid Region/State '%s' in the row #%s", $csvLine[1], ($k+1));
							}
						} else {
							$regionId = $regionCodesToIds[$countryCodesToIds[$csvLine[0]]][$csvLine[1]];
						}

						if (!count($csvLine)==14) {
							$exceptions[] = Mage::helper('gls')->__("Invalid number of columns in the row #%s", ($k+1));
						}
						else{
							if ($csvLine[2] == '*' || $csvLine[2] == '') {
								$city = '';
							} else {
								$city = $csvLine[2];
							}

							if ($csvLine[3] == '*' || $csvLine[3] == '') {
								$zip = '';
							} else {
								$zip = $csvLine[3];
							}

							if ($csvLine[4] == '*' || $csvLine[4] == '') {
								$zip_to = '';
							} else {
								$zip_to = $csvLine[4];
							}
							
							if ($csvLine[5] != 'package_weight' && $csvLine[5] != 'package_value' && $csvLine[5] != 'package_qty') {
								$conditionName = '';
								$exceptions[] = Mage::helper('gls')->__("Invalid %s From '%s' in the Row #%s", $conditionName, $csvLine[5], ($k+1));
							} else {
								$conditionName = $csvLine[5];
							}

							if (!$this->_isPositiveDecimalNumber($csvLine[6]) || $csvLine[6] == '*' || $csvLine[6] == '') {
								$exceptions[] = Mage::helper('gls')->__("Invalid %s From '%s' in the Row #%s", $conditionName, $csvLine[6], ($k+1));
							} else {
								$csvLine[6] = (float)$csvLine[6];
							}

							if (!$this->_isPositiveDecimalNumber($csvLine[7]) || $csvLine[7] == '*' || $csvLine[7] == '') {
								$exceptions[] = Mage::helper('gls')->__("Invalid %s To '%s' in the Row #%s", $conditionName, $csvLine[7], ($k+1));
							} else {
								$csvLine[7] = (float)$csvLine[7];
							}
							
							if (!$this->_isPositiveDecimalNumber($csvLine[8]) || $csvLine[8] == '*' || $csvLine[8] == '') {
								$exceptions[] = Mage::helper('gls')->__("Invalid %s To '%s' in the Row #%s", $conditionName, $csvLine[8], ($k+1));
							} else {
								$csvLine[8] = (float)$csvLine[8];
							}

							$data[] = array('website_id'=>$websiteId, 'dest_country_id'=>$countryId, 'dest_region_id'=>$regionId, 'dest_city'=>$city, 'dest_zip'=>$zip, 'dest_zip_to'=>$zip_to, 'condition_name'=>$conditionName, 'condition_from_value'=>$csvLine[6],'condition_to_value'=>$csvLine[7], 'price'=>$csvLine[8], 'cost'=>$csvLine[9], 'method_code'=>$csvLine[10], 'method_name'=>$csvLine[11], 'method_description'=>$csvLine[12], 'sort_order'=>$csvLine[13], 'external_id'=>$csvLine[14]);

						}
						/*
else {

							if ($csvLine[2] == '*' || $csvLine[2] == '') {
								$zip = '';
							} else {
								$zip = $csvLine[2]."%";
							}

							$city='';
							$zip_to = '';

							if (!$this->_isPositiveDecimalNumber($csvLine[3]) || $csvLine[3] == '*' || $csvLine[3] == '') {
								$exceptions[] = Mage::helper('gls')->__("Invalid %s From '%s' in the Row #%s", $conditionFullName, $csvLine[3], ($k+1));
							} else {
								$csvLine[3] = (float)$csvLine[3];
							}

							if (!$this->_isPositiveDecimalNumber($csvLine[4])) {
								$exceptions[] = Mage::helper('gls')->__("Invalid %s To '%s' in the Row #%s", $conditionFullName, $csvLine[4], ($k+1));
							} else {
								$csvLine[4] = (float)$csvLine[4];
							}
							$data[] = array('website_id'=>$websiteId, 'dest_country_id'=>$countryId, 'dest_region_id'=>$regionId,  'dest_city'=>$city,'dest_zip'=>$zip,'dest_zip_to'=>$zip_to,  'condition_name'=>$conditionName, 'condition_from_value'=>$csvLine[3],'condition_to_value'=>$csvLine[4], 'price'=>$csvLine[5], 'method_code'=>$csvLine[8], 'method_name'=>$csvLine[9], 'method_description'=>$csvLine[10], 'sort_order'=>$csvLine[11]);
						}
*/


						$dataDetails[] = array('country'=>$csvLine[0], 'region'=>$csvLine[1]);

					}
				}
				if (empty($exceptions)) {
					$connection = $this->_getWriteAdapter();

					$condition = array(
						$connection->quoteInto('website_id = ?', $websiteId),
						$connection->quoteInto('condition_name = ?', $conditionName),
						$connection->quoteInto('active = ?', '1'),
					);

					$fields = array();
					$fields['active'] = '0';

					$connection->update($table, $fields, $condition);

					foreach($data as $k=>$dataLine) {

						try {
							if(isset($dataLine['dest_country_id']) && $dataLine['dest_country_id'] == "*") {
					            $dataLine['dest_country_id'] = 0;
				            }
				            if(isset($dataLine['dest_city']) && $dataLine['dest_city'] == "*") {
					            $dataLine['dest_city'] = "";
				            }
				            if(isset($dataLine['dest_zip']) && $dataLine['dest_zip'] == "*") {
					            $dataLine['dest_zip'] = "";
				            }
				            if(isset($dataLine['dest_zip_to']) && $dataLine['dest_zip_to'] == "*") {
					            $dataLine['dest_zip_to'] = "";
				            }

							$connection->insert($table, $dataLine);
						} catch (Exception $e) {
							$exceptions[] = Mage::helper('gls')->__("Duplicate Row #%s (Country '%s', Region/State '%s', City '%s', Zip From '%s', Zip To '%s', Value From '%s' and Value To '%s', Code '%s', Name '%s', Description '%s')", ($k+1), $dataDetails[$k]['country'], $dataDetails[$k]['region'], $dataLine['dest_city'], $dataLine['dest_zip'],  $dataLine['dest_zip_to'], $dataLine['condition_from_value'], $dataLine['condition_to_value'], $dataLine['method_code'], $dataLine['method_name'], $dataLine['method_description'], $dataLine['sort_order'] );
						}
					}
				}
				if (!empty($exceptions)) {
					throw new Exception( "\n" . implode("\n", $exceptions) );
				}
			}
		}
	}

	private function _getCsvValues($string, $separator=",")
	{
		$elements = explode($separator, trim($string));
		for ($i = 0; $i < count($elements); $i++) {
			$nquotes = substr_count($elements[$i], '"');
			if ($nquotes %2 == 1) {
				for ($j = $i+1; $j < count($elements); $j++) {
					if (substr_count($elements[$j], '"') > 0) {
						array_splice($elements, $i, $j-$i+1, implode($separator, array_slice($elements, $i, $j-$i+1)));
						break;
					}
				}
			}
			if ($nquotes > 0) {
				$qstr =& $elements[$i];
				$qstr = substr_replace($qstr, '', strpos($qstr, '"'), 1);
				$qstr = substr_replace($qstr, '', strrpos($qstr, '"'), 1);
				$qstr = str_replace('""', '"', $qstr);
			}
			$elements[$i] = trim($elements[$i]);
		}
		return $elements;
	}

	private function _isPositiveDecimalNumber($n)
	{
		return preg_match("/^[0-9]+(\.[0-9]*)?$/", $n);
	}

}