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

class TenancyDao {

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

		$data = array();
		$data['recorddate'] = new Zend\Db\Sql\Expression('NOW()');
		if (!empty($tenancy['name'])) $data['name'] = $tenancy['name'];
		if (!empty($tenancy['comment'])) $data['comment'] = $tenancy['comment'];
		if (!empty($tenancy['address1'])) $data['address1'] = $tenancy['address1'];
		if (!empty($tenancy['address2'])) $data['address2'] = $tenancy['address2'];
		if (!empty($tenancy['zipcode'])) $data['zipcode'] = $tenancy['zipcode'];
		if (!empty($tenancy['city'])) $data['city'] = $tenancy['city'];
		if (!empty($tenancy['country'])) $data['country'] = $tenancy['country'];
		if (!empty($tenancy['state'])) $data['state'] = $tenancy['state'];
		if (!empty($tenancy['vat'])) $data['vat'] = $tenancy['vat'];
		if (!empty($tenancy['imagepath'])) $data['imagepath'] = $tenancy['imagepath'];
		if (!empty($tenancy['field1'])) $data['field1'] = $tenancy['field1'];
		if (!empty($tenancy['beds'])) $data['beds'] = $tenancy['beds'];

		$insert->values($data);

		$selectString = $sql->getSqlStringForSqlObject($insert);
		Logger::debug('		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 $db->getDriver()->getLastGeneratedValue();
	}

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

		$data = array();
		if (isset($tenancy['name'])) $data['name'] = $tenancy['name'];
		if (isset($tenancy['comment'])) $data['comment'] = $tenancy['comment'];
		if (isset($tenancy['address1'])) $data['address1'] = $tenancy['address1'];
		if (isset($tenancy['address2'])) $data['address2'] = $tenancy['address2'];
		if (isset($tenancy['zipcode'])) $data['zipcode'] = $tenancy['zipcode'];
		if (isset($tenancy['city'])) $data['city'] = $tenancy['city'];
		if (isset($tenancy['country'])) $data['country'] = $tenancy['country'];
		if (isset($tenancy['state'])) $data['state'] = $tenancy['state'];
		if (isset($tenancy['vat'])) $data['vat'] = $tenancy['vat'];
		if (!empty($tenancy['imagepath'])) $data['imagepath'] = $tenancy['imagepath'];
		$data['field1'] = $tenancy['field1'];
		if (!empty($tenancy['beds'])) $data['beds'] = $tenancy['beds'];

		$update->set($data);
		$update->where(array('tenancy_id' => $tenancy['tenancy_id']));

		$selectString = $sql->getSqlStringForSqlObject($update);
		Logger::debug('		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::tenancyTable);
		$delete->where(array('tenancy_id' => $id));

		$selectString = $sql->getSqlStringForSqlObject($delete);
		Logger::debug('		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 getTenancys ($str = null) {
		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::tenancyTable));
		
		if (isset($str)) {
			$select->where("l.name LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
			$select->where("l.lastname LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
			$select->where("l.comment LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		}


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

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new TenancyVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}

	public static function searchTenancy ($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::tenancyTable));
		
		$select->where("l.name LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("l.lastname LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("l.comment 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.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.zipcode 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.country LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.state LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("t.comment LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);

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

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new TenancyVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}

	public static function getTenancy ($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::tenancyTable));
		$select->where(array('tenancy_id' => $id));
	
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug('		Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$resCount = $results->count();

		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new TenancyVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
}

?>