<?php
/*
* 2007-2012 PrestaShop
*
* NOTICE OF LICENSE
*
* This source file is subject to the Open Software License (OSL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/osl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to license@prestashop.com so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade PrestaShop to newer
* versions in the future. If you wish to customize PrestaShop for your
* needs please refer to http://www.prestashop.com for more information.
*
*  @author PrestaShop SA <contact@prestashop.com>
*  @copyright  2007-2012 PrestaShop SA
*  @version  Release: $Revision: 16460 $
*  @license	http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
*  International Registered Trademark & Property of PrestaShop SA
*/

class CountyCore extends ObjectModel
{
	public $id;
	public $name;
	public $id_state;
	public $active;

	protected 	$fieldsRequired = array('name');
	protected 	$fieldsSize = array('name' => 64);
	protected 	$fieldsValidate = array('name' => 'isGenericName', 'id_state' => 'isUnsignedId', 'active' => 'isBool');

	protected 	$table = 'county';
	protected 	$identifier = 'id_county';

	private static $_cache_get_counties = array();
	private static $_cache_county_zipcode = array();

	const USE_BOTH_TAX = 0;
	const USE_COUNTY_TAX = 1;
	const USE_STATE_TAX = 2;

	protected	$webserviceParameters = array(
		'fields' => array(
			'id_state' => array('xlink_resource'=> 'states'),
		),
	);

	public function getFields()
	{
		parent::validateFields();
		$fields['id_state'] = (int)($this->id_state);
		$fields['name'] = pSQL($this->name);
		$fields['active'] = (int)($this->active);
		return $fields;
	}

	public function delete()
	{
		$id = $this->id;
		parent::delete();

		// remove associated zip codes & tax rule
		return (County::deleteZipCodeByIdCounty($id) AND TaxRule::deleteTaxRuleByIdCounty($id));
	}

	public static function getCounties($id_state)
	{
		if (!isset(self::$_cache_get_counties[$id_state]))
		{
			self::$_cache_get_counties[$id_state] = Db::getInstance()->ExecuteS('
			SELECT * FROM `'._DB_PREFIX_.'county`
			WHERE `id_state` = '.(int)$id_state);
		}

		return self::$_cache_get_counties[$id_state];
	}

	// return the list of associated zipcode
	public function getZipCodes()
	{
		return Db::getInstance()->ExecuteS('
		SELECT * FROM `'._DB_PREFIX_.'county_zip_code`
		WHERE `id_county` = '.(int)$this->id.'
		ORDER BY `from_zip_code` ASC');
	}

	public function addZipCodes($zip_codes)
	{
		list($from, $to) = $this->breakDownZipCode($zip_codes);

		if ($from == 0)
			return false;

		return Db::getInstance()->Execute(
		'INSERT INTO `'._DB_PREFIX_.'county_zip_code` (`id_county`, `from_zip_code`, `to_zip_code`)
		VALUES ('.(int)$this->id.','.(int)$from.','.(int)$to.')');
	}


	public function removeZipCodes($zip_codes)
	{
		list($from, $to) = $this->breakDownZipCode($zip_codes);

		if ($from == 0)
			return false;

		return Db::getInstance()->Execute('
		DELETE FROM `'._DB_PREFIX_.'county_zip_code`
		WHERE `id_county` = '.(int)$this->id.'
		AND `from_zip_code` = '.(int)$from.' AND `to_zip_code` = '.(int)$to);
	}


	public function breakDownZipCode($zip_codes)
	{
		$zip_codes = preg_split('/-/', $zip_codes);

		if (sizeof($zip_codes) == 2)
		{
			$from = $zip_codes[0];
			$to   = $zip_codes[1];
			if ($zip_codes[0] > $zip_codes[1])
			{
				$from = $zip_codes[1];
				$to   = $zip_codes[0];
			}
			elseif ($zip_codes[0] == $zip_codes[1])
			{
				$from = $zip_codes[0];
				$to = $from;
			}
		}
		elseif (sizeof($zip_codes) == 1)
		{
			$from = $zip_codes[0];
			$to = $from;
		}

		if (!Validate::isInt($from) || !Validate::isInt($to))
		{
			$from = 0;
			$to = 0;
		}

		return array($from, $to);
	}

	public static function getIdCountyByZipCode($id_state, $zip_code)
	{
		if (!isset(self::$_cache_county_zipcode[$id_state.'-'.$zip_code]))
		{
			self::$_cache_county_zipcode[$id_state.'-'.$zip_code] = Db::getInstance()->getValue('
			SELECT DISTINCT c.`id_county`
			FROM `'._DB_PREFIX_.'county` c
			LEFT JOIN `'._DB_PREFIX_.'county_zip_code` cz ON (c.`id_county` = cz.`id_county`)
			WHERE c.`id_state` = '.(int)$id_state.' AND cz.`from_zip_code` >= '.(int)$zip_code.' AND cz.`to_zip_code` <= '.(int)$zip_code);
		}

		return self::$_cache_county_zipcode[$id_state.'-'.$zip_code];
	}

	public function isZipCodeRangePresent($zip_codes)
	{
		$res = false;
		list($from, $to) = $this->breakDownZipCode($zip_codes);

		if ($from == 0)
			return false;

		if ($to != 0)
		{
			$res = Db::getInstance()->getValue('
			SELECT COUNT(*) FROM `'._DB_PREFIX_.'county_zip_code` cz
			LEFT JOIN `'._DB_PREFIX_.'county` c ON (c.`id_county` = cz.`id_county`)
			LEFT JOIN `'._DB_PREFIX_.'state` s ON (s.`id_state` = c.`id_state`)
			WHERE `from_zip_code` >= '.(int)$from.' AND `to_zip_code` <= '.(int)$to.'
			AND s.`id_country` = (
				SELECT `id_country`
				FROM `'._DB_PREFIX_.'state` s
				LEFT JOIN `'._DB_PREFIX_.'county` c ON (c.`id_state` = s.`id_state`)
				WHERE `id_county` = '.(int)$this->id.')');
		}

		return ($res OR County::isZipCodePresent($from) OR County::isZipCodePresent($to));
	}

	public function isZipCodePresent($zip_code)
	{
		if ($zip_code == 0)
			return false;

		return (bool) Db::getInstance()->getValue('
		SELECT COUNT(*) FROM `'._DB_PREFIX_.'county_zip_code` cz
		LEFT JOIN `'._DB_PREFIX_.'county` c ON (c.`id_county` = cz.`id_county`)
		LEFT JOIN `'._DB_PREFIX_.'state` s ON (s.`id_state` = c.`id_state`)
		WHERE (`from_zip_code` <= '.(int)$zip_code.' AND `to_zip_code` >= '.(int)$zip_code.')
		OR (`from_zip_code` = '.(int)$zip_code.')
		AND s.`id_country` = (
			SELECT `id_country`
			FROM `'._DB_PREFIX_.'state` s
			LEFT JOIN `'._DB_PREFIX_.'county` c ON (c.`id_state` = s.`id_state`)
			WHERE `id_county` = '.(int)$this->id.')');
	}

	public static function deleteZipCodeByIdCounty($id_county)
	{
		return Db::getInstance()->Execute('DELETE FROM `'._DB_PREFIX_.'county_zip_code` WHERE `id_county` = '.(int)$id_county);
	}


	public static function getIdCountyByNameAndIdState($name, $id_state)
	{
		return Db::getInstance()->getValue('
		SELECT `id_county`
		FROM `'._DB_PREFIX_.'county`
		WHERE `name` = \''.pSQL($name).'\' AND `id_state` = '.(int)$id_state);
	}

}

