<?php
// +---------------------------------------------------------------------------+
// | This file is part of the Riobel project.                                  |
// | Copyright (C) Lemieux Bedard communications                               |
// |                                                                           |
// | For the full copyright and license information, please view the LICENSE   |
// | file that was distributed with this source code.                          |
// +---------------------------------------------------------------------------+

/**
 * RiobelRetailerModel handle the retailers.
 * @package core
 * @subpackage model
 * @author Jean-Philippe Dery (jean-philippe.dery@hotmail.com)
 * @copyright Lemieux Bedard communications (info@lemieuxbedard.com)
 * @since 1.0.0
 * @version 1.0.0
 */
class RiobelRetailerModel  extends RiobelBaseModel implements AgaviISingletonModel
{
	/**
	 * Find all the retailer provinces.
	 * @param string The order.
	 * @param int The limit.
	 * @param int The offset.
	 * @return object The retailer province.
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function findAllRetailerProvinces($order = null, $limit = null, $offset = null)
	{
		$doctrineQuery = Doctrine_Query::create();
		$doctrineQuery->from("RiobelRetailerProvince rp");
		if ($order) $doctrineQuery->orderby($order);
		if ($limit) $doctrineQuery->limit($limit);
		if ($offset) $doctrineQuery->offset($offset);
		return $doctrineQuery->execute();
	}

	/**
	 * Find all the retailer cities.
	 * @param string The order.
	 * @param int The limit.
	 * @param int The offset.
	 * @return object The retailer cities.
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function findAllRetailerCities($order = null, $limit = null, $offset = null)
	{
		$doctrineQuery = Doctrine_Query::create();
		$doctrineQuery->from("RiobelRetailerCity rc");
		$doctrineQuery->where("EXISTS (SELECT * FROM RiobelRetailer r WHERE r.city_id = rc.id)");
		if ($order) $doctrineQuery->orderby($order);
		if ($limit) $doctrineQuery->limit($limit);
		if ($offset) $doctrineQuery->offset($offset);
		return $doctrineQuery->execute();
	}

	/**
	 * Find the
	 * @param int The province.
	 * @param string The order.
	 * @param int The limit.
	 * @param int The offset.
	 * @return object The retailer cities.
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function findRetailerProvinces($order = null, $limit = null, $offset = null)
	{
		// TODO: Make sure this uses only the province where we have a retailer
		$doctrineQuery = Doctrine_Query::create();
		$doctrineQuery->from("RiobelRetailerProvince rp");
		if ($order) $doctrineQuery->orderby($order);
		if ($limit) $doctrineQuery->limit($limit);
		if ($offset) $doctrineQuery->offset($offset);
		return $doctrineQuery->execute();
	}

	/**
	 * Find all the retailer cities in a given province.
	 * @param int The province.
	 * @param string The order.
	 * @param int The limit.
	 * @param int The offset.
	 * @return object The retailer cities.
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function findRetailerCitiesByProvince($provinceId, $order = null, $limit = null, $offset = null)
	{
		$doctrineQuery = Doctrine_Query::create();
		$doctrineQuery->from("RiobelRetailerCity rc");
		$doctrineQuery->addWhere("rc.province_id = ?", array($provinceId));
		$doctrineQuery->addWhere("EXISTS (SELECT * FROM RiobelRetailer r WHERE r.city_id = rc.id)");
		if ($order) $doctrineQuery->orderby($order);
		if ($limit) $doctrineQuery->limit($limit);
		if ($offset) $doctrineQuery->offset($offset);
		return $doctrineQuery->execute();
	}

	/**
	 * Find all the retailers from a city in a province.
	 * @param int The province.
	 * @param int The city.
	 * @param string The order.
	 * @param int The limit.
	 * @param int The offset.
	 * @return object The retailer cities.
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function findRetailersByProvinceAndCity($provinceId, $cityId, $order = null, $limit = null, $offset = null)
	{
		$doctrineQuery = Doctrine_Query::create();
		$doctrineQuery->from("RiobelRetailer r");
		$doctrineQuery->where("r.province_id = ? AND r.city_id = ?");
		if ($order) $doctrineQuery->orderby($order);
		if ($limit) $doctrineQuery->limit($limit);
		if ($offset) $doctrineQuery->offset($offset);
		return $doctrineQuery->execute(array($provinceId, $cityId));
	}

	/**
	 * Create a new retailer.
	 * @param int The province id.
	 * @param int The city id.
	 * @param array The retailer data.
	 * @param string The original id.
	 * @return void
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function createRetailer($provinceId, $cityId, array $attributes, $originalId = '')
	{
		$retailer = new RiobelRetailer();
		$retailer->fromArray($attributes);
		$retailer['original_id'] = $originalId;
		$retailer['province_id'] = $provinceId;
		$retailer['city_id'] = $cityId;
		$retailer->save();
		return $retailer;
	}

	/**
	 * Create a new retailer province.
	 * @param array The attributes.
	 * @param string The original id.
	 * @param object The connection to the previous database.
	 * @return void
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function createRetailerProvince(array $attributes, $originalId = '')
	{
		$retailerProvince = new RiobelRetailerProvince();
		$retailerProvince->fromArray($attributes);
		$retailerProvince['original_id'] = $originalId;
		$retailerProvince->save();
		return $retailerProvince;
	}

	/**
	 * Create a new retailer city.
	 * @param int The province id.
	 * @param array The attributes.
	 * @param string The original id.
	 * @param object The connection to the previous database.
	 * @return void
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function createRetailerCity($provinceId, array $attributes, $originalId = '')
	{
		$retailerCity = new RiobelRetailerCity();
		$retailerCity->fromArray($attributes);
		$retailerCity['original_id'] = $originalId;
		$retailerCity['province_id'] = $provinceId;
		$retailerCity->save();
		return $retailerCity;
	}

	// ----------------------------------------------------------------------------
	// Original Record Finders
	// ----------------------------------------------------------------------------

	/**
	 * Find a retailer province by it's original id.
	 * @param string The original id.
	 * @return object The retailer province.
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function findRetailerProvinceByOriginalId($originalId)
	{
		$doctrineQuery = Doctrine_Query::create();
		$doctrineQuery->from("RiobelRetailerProvince rp");
		$doctrineQuery->where("original_id = ?");
		return $doctrineQuery->fetchOne(array($originalId));
	}

	/**
	 * Find a retailer city by it's original id.
	 * @param string The original id.
	 * @return object The retailer city.
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function findRetailerCityByOriginalId($originalId)
	{
		$doctrineQuery = Doctrine_Query::create();
		$doctrineQuery->from("RiobelRetailerCity rc");
		$doctrineQuery->where("original_id = ?");
		return $doctrineQuery->fetchOne(array($originalId));
	}

	// ----------------------------------------------------------------------------
	// Importation Methods
	// ----------------------------------------------------------------------------

	/**
	 * Import all the retailers, retailer provinces, retailer cities from the
	 * previous database.
	 * @param object The connection to the previous database.
	 * @return void
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	public function import($conn)
	{
		$this->importRetailerProvinces($conn);
		$this->importRetailerCities($conn);
		$this->importRetailers($conn);
	}

	/**
	 * Import all retailer provinces from the previous database.
	 * @param object The connection to the previous database.
	 * @return void
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	protected function importRetailerProvinces($conn)
	{
		Doctrine_Manager::connection()->getDbh()->query('TRUNCATE TABLE riobel_retailer_province')->execute();

		$sql = "SELECT *
				FROM rioprovinces p
				WHERE ProvDescriptA <> '' AND
				      ProvDescript  <> '' AND (
				      	(SELECT COUNT(IDRioClientAdresse)
				      	FROM rioclientadresse ca
				      	WHERE ca.IDRioProvinces = p.IDRioProvinces) > 0
				      )
				ORDER BY ProvDescript";

		$rows = $conn->query($sql)->fetchall();
		foreach ($rows as $row) {

			$originalId = $row['IDRioProvinces'];
			$nameEn     = $row['ProvDescriptA'];
			$nameFr     = $row['ProvDescript'];
			$code       = $row['ProvABBR'];

			$attributes = array(
				'name_en' => $nameEn,
				'name_fr' => $nameFr,
				'code'    => $code
			);

			$this->createRetailerProvince($attributes, $originalId);
		}
	}

	/**
	 * Import all the retailer cities from the previous database.
	 * @param object The connection to the previous database.
	 * @return void
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	protected function importRetailerCities($conn)
	{
		Doctrine_Manager::connection()->getDbh()->query('TRUNCATE TABLE riobel_retailer_city')->execute();

		$sql = "SELECT DISTINCT(Ville), IDRioClientAdresse, IDRioProvinces
				FROM rioclientadresse
				GROUP BY Ville
				ORDER BY Ville";

		$rows = $conn->query($sql)->fetchall();
		foreach ($rows as $row) {

			$provinceId = $row['IDRioProvinces'];
			$originalId = $row['Ville'];
			$nameEn     = $row['Ville'];
			$nameFr     = $row['Ville'];

			$province = $this->findRetailerProvinceByOriginalId($provinceId);
			if ($province == null) {
				continue;
			}

			$attributes = array(
				'name_en' => $nameEn,
				'name_fr' => $nameFr
			);

			$this->createRetailerCity($province['id'], $attributes, $originalId);
		}
	}

	/**
	 * Import all the retailers from the previous database.
	 * @param object The connection to the previous database.
	 * @return void
	 * @author Jean-Philippe Dery (jean-philippe.dery@lemieuxbedard.com)
	 * @since 1.0.0
	 */
	protected function importRetailers($conn)
	{
		Doctrine_Manager::connection()->getDbh()->query('TRUNCATE TABLE riobel_retailer')->execute();

		$sql = "SELECT *
				FROM rioclientadresse ca
				INNER JOIN rioclient c
					ON ca.IDRioClient = c.IDRioClient
				WHERE cliBInactif = 0";

		$rows = $conn->query($sql)->fetchall();
		foreach ($rows as $row) {

			$originalId = $row['IDRioClientAdresse'];
			$provinceId = $row['IDRioProvinces'];
			$cityId     = $row['Ville'];
			$name       = $row['NomDeLaLocalisation'];
			$address    = $row['Adresse1'];
			$phone      = $row['cliTelephone'];
			$extension  = $row['extension'];
			$fax        = $row['Fax'];

			$province = $this->findRetailerProvinceByOriginalId($provinceId);
			if ($province == null) {
				continue;
			}

			$city = $this->findRetailerCityByOriginalId($cityId);
			if ($city == null) {
				continue;
			}

			$attributes = array(
				'name'      => $name,
				'address'   => $address,
				'phone'     => $phone,
				'extension' => $extension,
				'fax'       => $fax
			);

			$this->createRetailer($province['id'], $city['id'], $attributes, $originalId);
		}
	}
}