<?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('TenantVo.php');

class TenantDao {

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

		$data = array();
		$data['recorddate'] = new Zend\Db\Sql\Expression('NOW()');
		if (!empty($tenant['title'])) $data['title'] = $tenant['title'];
		if (!empty($tenant['name'])) $data['name'] = $tenant['name'];
		if (!empty($tenant['lastname'])) $data['lastname'] = $tenant['lastname'];
		if (!empty($tenant['address1'])) $data['address1'] = $tenant['address1'];
		if (!empty($tenant['address2'])) $data['address2'] = $tenant['address2'];
		if (!empty($tenant['zipcode'])) $data['zipcode'] = $tenant['zipcode'];
		if (!empty($tenant['city'])) $data['city'] = $tenant['city'];
		if (!empty($tenant['country'])) $data['country'] = $tenant['country'];
		if (!empty($tenant['phone'])) $data['phone'] = $tenant['phone'];
		if (!empty($tenant['cellphone'])) $data['cellphone'] = $tenant['cellphone'];
		if (!empty($tenant['fax'])) $data['fax'] = $tenant['fax'];
		if (!empty($tenant['email'])) $data['email'] = $tenant['email'];
		if (!empty($tenant['comment'])) $data['comment'] = $tenant['comment'];
		if (!empty($tenant['reference'])) $data['reference'] = $tenant['reference'];
		if (!empty($tenant['language'])) $data['language'] = $tenant['language'];
		$data['newsletter'] = !empty($tenant['newsletter']) ? 1 : 0;
		$data['enable'] = true;

		$insert->values($data);

		$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 ($tenant) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$update = $sql->update();
		$update->table(DATABASE_DB_TABLEPREFIX . self::tenantTable);

		$data = array();
		$data['title'] = $tenant['title'];
		$data['name'] = $tenant['name'];
		$data['lastname'] = $tenant['lastname'];
		$data['address1'] = $tenant['address1'];
		$data['address2'] = $tenant['address2'];
		$data['zipcode'] = $tenant['zipcode'];
		$data['city'] = $tenant['city'];
		$data['country'] = $tenant['country'];
		$data['phone'] = $tenant['phone'];
		$data['cellphone'] = $tenant['cellphone'];
		$data['fax'] = $tenant['fax'];
		$data['email'] = $tenant['email'];
		$data['comment'] = $tenant['comment'];
		$data['language'] = $tenant['language'];
		if (isset($tenant['enable'])) $data['enable'] = $tenant['enable'];
		$data['reference'] = $tenant['reference'];
		$data['newsletter'] = !empty($tenant['newsletter']) ? 1 : 0;

		$update->set($data);
		$update->where(array('tenant_id' => $tenant['tenant_id']));
		Logger::debug(self::INDENT2 . 'Query Data: ' . print_r($data,true));
		$selectString = $sql->getSqlStringForSqlObject($update);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$affectedRows = $results->getAffectedRows();
		Logger::debug(self::INDENT2 . 'Query Result: ' . $affectedRows);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $affectedRows;
	}
	
	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::tenantTable);
		$delete->where(array('tenant_id' => $id));

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

	public static function searchTenant ($str){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable));

		$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("t.address1 LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.address2 LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.city LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.zipcode LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.country LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.phone LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.cellphone LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.fax LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.email LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.comment LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.reference LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

		$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 TenantVo($row);
		}
		Logger::debug(self::INDENT2 . 'Query Result count: ' . $resCount);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	
	public static function getTenants ($arrParam = null) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . '] : '. print_r($arrParam,true));
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable));

		if (isset($arrParam) && !empty($arrParam)) {
			$select->join(array('r' => DATABASE_DB_TABLEPREFIX . self::reservTable),'t.tenant_id = r.tenant_id', array('tenancy_id' => 'tenancy_id', 'arrival_date' => 'arrival_date', 'departure_date' => 'departure_date'),$select::JOIN_LEFT);
			if (!empty($arrParam['tenancy_id']))
				$select->where("r.tenancy_id = '" . $arrParam['tenancy_id'] . "'", Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
			if (!empty($arrParam['filter_year']))
				$select->where("EXTRACT(YEAR FROM r.arrival_date) = '" . $arrParam['filter_year'] . "'", Zend\Db\Sql\Predicate\PredicateSet::OP_AND);
			// $select->where("name LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
			// $select->where("lastname LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		}
		$select->order('lastname 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 TenantVo($row);
		}
		Logger::debug(self::INDENT2 . 'Query Result count: ' . $resCount);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}

	public static function getTenant ($id){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable));
		$select->where(array('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 TenantVo($row);
		}
		Logger::debug(self::INDENT2 . 'Query Result count: ' . $resCount);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	
	public static function getOrpheanTenant() {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable));
		$select->join(array('r' => DATABASE_DB_TABLEPREFIX . self::reservTable),'t.tenant_id = r.tenant_id', array(),$select::JOIN_LEFT);
		$select->where(array(new Zend\Db\Sql\Predicate\IsNull('r.reservation_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 TenantVo($row);
		}
		Logger::debug(self::INDENT2 . 'Query Result count: ' . $resCount);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	
	}

}

?>