<?php
/*******************************************************************************
 *                    _ _ _ _
 *                   / _ _  /
 *            _ _   / /__/ /_ _   ____ _ _   _ _ _    __
 *           / _ ` /   _ _// _ ` / __// _ ` / _ \\ \ / /
 *          / _ _// /\ \  / _ _/ \_ \/ _ _// / \/ \ ` /
 *          \_ , /_/  \_\ \_ ,  /__ /\_ , /_/      \_/
 *
 *******************************************************************************
 *    Application:  eReserv
 *    Version:      6
 *    Author:       David Di Certo
 *    Website:      http://www.cogites.com
 *    Email:        contact@cogites.com
 *    Copyright:    2013 - David Di Certo
 *******************************************************************************
 *    == FR ==      La revente et la redistribution de l'application sont
 *                  strictement interdites.
 *
 *                  Vous êtes tenu d'avoir pris connaissance des
 *                  Conditions Générales de Vente disponibles sur Cogites.com
 *                  (y compris pour la version Free).
 *
 *    == EN ==      Resale and redistribution of the application are
 *                  Strictly prohibited.
 *
 *                  You are required to have read the Terms and Conditions
 *                  available on Cogites.com (including the Free version).
 *
 *******************************************************************************
*/

require_once('config/Logger.php');
require_once('ReservationVo.php');

class ReservationDao {

	const reservationTable = 'reservation';
	const tenancyTable = 'tenancy';
	const tenantTable = 'tenant';
	const INDENT1 = "    ";
	const INDENT2 = "		";
	const INDENT3 = "			";

	public static function insert ($reservation) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$insert = $sql->insert();
		$insert->into(DATABASE_DB_TABLEPREFIX . self::reservationTable);

		$data = array();
		$data['recorddate'] = new Zend\Db\Sql\Expression('NOW()');
		if (!empty($reservation['tenancy_id'])) $data['tenancy_id'] = $reservation['tenancy_id'];
		if (!empty($reservation['tenant_id'])) $data['tenant_id'] = $reservation['tenant_id'];
		if (!empty($reservation['arrival_date'])) $data['arrival_date'] = $reservation['arrival_date'];
		if (!empty($reservation['departure_date'])) $data['departure_date'] = $reservation['departure_date'];
		if (isset($reservation['price'])) $data['price'] = $reservation['price'];
		if (isset($reservation['vat'])) $data['vat'] = $reservation['vat'];
		if (isset($reservation['deposit'])) $data['deposit'] = $reservation['deposit'];
		if (!empty($reservation['paid'])) $data['paid'] = $reservation['paid'];
		if (!empty($reservation['adults_nb']) || $reservation['adults_nb'] === '0') $data['adults_nb'] = $reservation['adults_nb'];
		if (!empty($reservation['childs_nb']) || $reservation['childs_nb'] === '0') $data['childs_nb'] = $reservation['childs_nb'];
		if (!empty($reservation['baby_nb']) || $reservation['baby_nb'] === '0') $data['baby_nb'] = $reservation['baby_nb'];
// TODO: manage boolean update. When adding payment the disabled is set to blank
		$data['disabled'] = !empty($reservation['disabled']) ? 1 : 0;
		if (!empty($reservation['comment'])) $data['comment'] = $reservation['comment'];
		if (!empty($reservation['reference'])) $data['reference'] = $reservation['reference'];
		if (isset($reservation['status'])) $data['status'] = $reservation['status'];

		$insert->values($data);
		Logger::debug('--> ReservationInsert: ' . print_r($data,true));
		$selectString = $sql->getSqlStringForSqlObject($insert);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		Logger::debug(self::INDENT2 . 'Query Result ID: ' . $db->getDriver()->getLastGeneratedValue());
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $db->getDriver()->getLastGeneratedValue();
	}

	public static function update ($reservation) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$update = $sql->update();
		$update->table(DATABASE_DB_TABLEPREFIX . self::reservationTable);

		$data = array();
		if (!empty($reservation['tenancy_id'])) $data['tenancy_id'] = $reservation['tenancy_id'];
		if (!empty($reservation['tenant_id'])) $data['tenant_id'] = $reservation['tenant_id'];
		if (!empty($reservation['arrival_date'])) $data['arrival_date'] = $reservation['arrival_date'];
		if (!empty($reservation['departure_date'])) $data['departure_date'] = $reservation['departure_date'];
		if (isset($reservation['price'])) $data['price'] = $reservation['price'];
		if (isset($reservation['vat'])) $data['vat'] = $reservation['vat'];
		if (isset($reservation['deposit'])) $data['deposit'] = $reservation['deposit'];
		if (!empty($reservation['paid'])) $data['paid'] = $reservation['paid'];
		if (isset($reservation['adults_nb'])) $data['adults_nb'] = $reservation['adults_nb'];
		if (isset($reservation['childs_nb'])) $data['childs_nb'] = $reservation['childs_nb'];
		if (isset($reservation['baby_nb'])) $data['baby_nb'] = $reservation['baby_nb'];
// TODO: manage boolean update. When adding payment the disabled is set to blank
		$data['disabled'] = !empty($reservation['disabled']) ? 1 : 0;
		if (!empty($reservation['comment'])) $data['comment'] = $reservation['comment'];
		if (!empty($reservation['reference'])) $data['reference'] = $reservation['reference'];
		if (isset($reservation['status'])) $data['status'] = $reservation['status'];
		if (isset($reservation['lease_date'])) $data['lease_date'] = $reservation['lease_date'];
		if (isset($reservation['lease_date']) && ($reservation['lease_date'] == 'NULL')) $data['lease_date'] = new Zend\Db\Sql\Expression('NULL');

		$update->set($data);
		$update->where(array('reservation_id' => $reservation['reservation_id']));

		$selectString = $sql->getSqlStringForSqlObject($update);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		Logger::debug(self::INDENT2 . 'Query Result count: ' . $results->getAffectedRows());
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $results->getAffectedRows();
	}

	public static function delete ($id) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$delete = $sql->delete();
		$delete->from(DATABASE_DB_TABLEPREFIX . self::reservationTable);
		$delete->where(array('reservation_id' => $id));

		$selectString = $sql->getSqlStringForSqlObject($delete);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		Logger::debug(self::INDENT2 . 'Query Result: ' . $results->getAffectedRows());
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $results->getAffectedRows();
	}
	public static function getReservations ($str) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name'),$select::JOIN_LEFT);
		
		if (isset($str)) {
			$select->where("t.name LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
			$select->where("t.comment LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		}
		$select->order('arrival_date ASC');

		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function searchReservation ($str) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name'),$select::JOIN_LEFT);
		
		$select->where("t.title LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.name LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.lastname LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

		$select->where("ty.name LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

		$select->where("r.arrival_date LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("r.departure_date LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("r.price LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("r.comment LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

		$select->order('arrival_date ASC');

		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getReservation ($id){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(DATABASE_DB_TABLEPREFIX . self::reservationTable);
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name'),$select::JOIN_LEFT);
		$select->where(array('r.reservation_id' => $id));

		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	// Tax module
	public static function getReservationRange ($from, $to, $id = null){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(DATABASE_DB_TABLEPREFIX . self::reservationTable);
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name'),$select::JOIN_LEFT);
		$select->where('((arrival_date BETWEEN "' . $from . '" AND "' . $to. '")', Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where('(departure_date BETWEEN "' . $from . '" AND "' . $to . '")', Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where('("' . $from . '" BETWEEN arrival_date AND departure_date)', Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where('("' . $to . '" BETWEEN arrival_date AND departure_date))', Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		if (isset($id) && !empty($id)) {
			$select->where(array('r.tenancy_id' => $id));
		}
		// $select->where('arrival_date <= ?', $from, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		// $select->where('arrival_date >= ?', $to, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		// $select->where('departure_date <= ?', $from, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		// $select->where('departure_date >= ?', $to, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

		$select->order('arrival_date ASC');

		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	// public static function isReservationAvailableORI ($from, $to, $id, $minstatus){
		// Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		// Logger::debug("  -> isReservationAvailable: [" . $from . "-" . $to . "-" . $id . "-" . $minstatus . "]");
		// $db = DBConnector::getZendDbConnection();
		// $sql = new Zend\Db\Sql\Sql($db);
		// $select = $sql->select();
		// $select->from(DATABASE_DB_TABLEPREFIX . self::reservationTable);
		// $select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		// $select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		// $select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name'),$select::JOIN_LEFT);
		// $select->where('r.tenancy_id = '. $id, Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
		// if (isset($minstatus) && (!empty($minstatus))) $select->where('r.status > ' . $minstatus, Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
		// $select->where('(("' . $from . '" < arrival_date AND "' . $to . '" > arrival_date ) OR ("' . 
		// $from . '" > arrival_date AND "' . $to . '" < departure_date ) OR ("' . 
		// $from . '" < departure_date AND "' . $to . '" > departure_date ) OR ("' . 
		// $from . '" < arrival_date AND "' . $to . '" > departure_date ))', Zend\Db\Sql\Predicate\PredicateSet::OP_AND);

		//// $select->where('arrival_date <= ?', $from, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		//// $select->where('arrival_date >= ?', $to, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		//// $select->where('departure_date <= ?', $from, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		//// $select->where('departure_date >= ?', $to, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

		// $select->order('arrival_date ASC');

		// $selectString = $sql->getSqlStringForSqlObject($select);
		// Logger::debug(self::INDENT2 . 'Execute query [isReservationAvailable]: ' . $selectString);
		// $results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		// $resCount = $results->count();

		// $data = array();
		// foreach ($results->toArray() as $row) {
			// $data[] = new ReservationVo($row);
		// }
		// Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		// return $data;
	// }
	public static function isReservationAvailable ($from, $to, $id, $minstatus){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		Logger::debug("  -> isReservationAvailable: [" . $from . "-" . $to . "-" . $id . "-" . $minstatus . "]");
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(DATABASE_DB_TABLEPREFIX . self::reservationTable);
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name'),$select::JOIN_LEFT);
		$select->where('r.tenancy_id = '. $id, Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
		if (isset($minstatus)) $select->where('r.status >= ' . $minstatus, Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
	
		// $select->where('(("' . $from . '" < arrival_date AND "' . $to . '" > arrival_date ) OR ("' . 
		// $from . '" > arrival_date AND "' . $to . '" < departure_date ) OR ("' . 
		// $from . '" < departure_date AND "' . $to . '" > departure_date ) OR ("' . 
		// $from . '" < arrival_date AND "' . $to . '" > departure_date ))', Zend\Db\Sql\Predicate\PredicateSet::OP_AND);

		// $select->where('(("' . 
		// $from . '" >= arrival_date AND "' . $from . '" > departure_date ) OR ("' . 
		// $to . '" > arrival_date AND "' . $to . '" <= departure_date ) OR ("' . 
		// $from . '" < arrival_date AND "' . $to . '" > arrival_date ) OR ("' . 
		// $from . '" = arrival_date AND "' . $to . '" = departure_date ) OR ("' . 
		// $from . '" = arrival_date ))', Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
	
		$select->where('(("' . 
		$from . '" >= arrival_date AND "' . $from . '" < departure_date ) OR ("' . 
		$to   . '" > arrival_date AND "' . $to . '" < departure_date ) OR ("' . 
		$from . '" <= arrival_date AND "' . $to . '" > arrival_date )' .
		')', Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
	
		// $select->where('arrival_date <= ?', $from, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		// $select->where('arrival_date >= ?', $to, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		// $select->where('departure_date <= ?', $from, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		// $select->where('departure_date >= ?', $to, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

		$select->order('arrival_date ASC');

		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query [isReservationAvailable]: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function isReservationAvailable_2 ($from, $to, $id, $minstatus){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		Logger::debug("  -> isReservationAvailable: [" . $from . "-" . $to . "-" . $id . "-" . $minstatus . "]");
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(DATABASE_DB_TABLEPREFIX . self::reservationTable);
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name'),$select::JOIN_LEFT);
		$select->where('r.tenancy_id <> '. $id, Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
		if (isset($minstatus) && (!empty($minstatus))) $select->where('r.status > ' . $minstatus, Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
		$select->where('(("' . $from . '" >= arrival_date AND "' . $from . '" < departure_date ) OR ("' . 
		$to . '" > arrival_date AND "' . $to . '" <= departure_date ) OR ("' . 
		$from . '" < arrival_date AND "' . $to . '" > arrival_date ) OR ("' . 
		$from . '" = arrival_date AND "' . $to . '" = departure_date ))', Zend\Db\Sql\Predicate\PredicateSet::OP_AND);

		// $select->where('arrival_date <= ?', $from, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		// $select->where('arrival_date >= ?', $to, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		// $select->where('departure_date <= ?', $from, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		// $select->where('departure_date >= ?', $to, Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

		$select->order('arrival_date ASC');

		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query [isReservationAvailable]: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getReservationFromTenant ($id) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(DATABASE_DB_TABLEPREFIX . self::reservationTable);
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name', 'tenancy_imagepath' => 'imagepath'),$select::JOIN_LEFT);
		$select->where(array('r.tenant_id' => $id));
	
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getReservationFromTenancy ($id) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(DATABASE_DB_TABLEPREFIX . self::reservationTable);
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name', 'tenancy_imagepath' => 'imagepath'),$select::JOIN_LEFT);
		$select->where(array('r.tenancy_id' => $id));
	
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getCurrentReservations ($nb){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name', 'tenancy_imagepath' => 'imagepath'),$select::JOIN_LEFT);
		$select->where('DATE(arrival_date) <= DATE(NOW())');
		$select->where('DATE(departure_date) >= DATE(NOW())');
		$select->where('status >= ' . ReservationMgr::RESERV_VALIDATE);
		$select->where('enable = true');
		$select->order('arrival_date ASC');
		if (is_numeric($nb)) {
			$select->limit($nb);
		}

		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getNextReservations ($nb){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name', 'tenancy_imagepath' => 'imagepath'),$select::JOIN_LEFT);
		$select->where('DATE(arrival_date) >= DATE(NOW())');
		$select->where('status >= ' . ReservationMgr::RESERV_VALIDATE);
		$select->where('enable = true');
		$select->order('arrival_date ASC');
		if (is_numeric($nb)) {
			$select->limit($nb);
		}
	
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getTodayDepartureReservations (){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name', 'tenancy_imagepath' => 'imagepath'),$select::JOIN_LEFT);
		$select->where('DATE(departure_date) = DATE(NOW())');
		$select->where('status >= ' . ReservationMgr::RESERV_VALIDATE);
		$select->where('enable = true');
	
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getTodayArrivalReservations (){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name', 'tenancy_imagepath' => 'imagepath'),$select::JOIN_LEFT);
		$select->where('DATE(arrival_date) = DATE(NOW())');
		$select->where('status >= ' . ReservationMgr::RESERV_VALIDATE);
		$select->where('enable = true');
	
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getReservationsPerYear($year) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'t.tenant_id = r.tenant_id', array('tenant_name' => 'name', 'tenant_lastname' => 'lastname', 'tenant_email' => 'email'),$select::JOIN_LEFT);
		$select->join(array('ty' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'ty.tenancy_id = r.tenancy_id', array('tenancy_name' => 'name', 'tenancy_imagepath' => 'imagepath'),$select::JOIN_LEFT);
		$select->where('(EXTRACT(YEAR FROM arrival_date) = ' . $year . ' OR EXTRACT(YEAR FROM departure_date) = ' . $year . ')');
		$select->where('enable = true');
		$select->order('arrival_date ASC');
	
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new ReservationVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getReservationsOmniresa($id, $date) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable));
		$select->where('("'. $date . '" BETWEEN arrival_date AND departure_date)', Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where('status = ' . ReservationMgr::RESERV_CLOSED);
		$select->where(array('tenancy_id' => $id));
	
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $resCount;
	}
	
}

?>
