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

class PaymentDao {
	
	const reservationTable = 'reservation';
	const paymentTable = 'payment';
	const tenantTable = 'tenant';
	const tenancyTable = 'tenancy';
	const INDENT1 = "    ";
	const INDENT2 = "		";
	const INDENT3 = "			";

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

		$data = array();
		$data['recorddate'] = new Zend\Db\Sql\Expression('NOW()');
		if (!empty($payment['payment_date'])) $data['payment_date'] = $payment['payment_date'];
		if (!empty($payment['reservation_id'])) $data['reservation_id'] = $payment['reservation_id'];
		if (!empty($payment['method'])) $data['method'] = $payment['method'];
		if (!empty($payment['type'])) $data['type'] = $payment['type'];
		if (!empty($payment['amount']) || $payment['amount'] === '0') $data['amount'] = $payment['amount'];
		if (!empty($payment['comment'])) $data['comment'] = $payment['comment'];

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

		$data = array();
		$data['recorddate'] = new Zend\Db\Sql\Expression('NOW()');
		if (!empty($id['payment_date'])) $data['payment_date'] = $id['payment_date'];
		if (!empty($id['reservation_id'])) $data['reservation_id'] = $id['reservation_id'];
		if (!empty($id['method'])) $data['method'] = $id['method'];
		if (!empty($id['type'])) $data['type'] = $id['type'];
		if (!empty($id['amount']) || $id['amount'] === '0') $data['amount'] = $id['amount'];
		if (!empty($id['comment'])) $data['comment'] = $id['comment'];

		$update->set($data);
		$update->where(array('payment_id' => $id['payment_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 deletePaymentFromReservation ($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::paymentTable);
		$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);
		$affectedRows = $results->getAffectedRows();
		Logger::debug(self::INDENT2 . 'Query Result: ' . $affectedRows);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $affectedRows;
	}
	
	public static function getPaymentByReservation ($id){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('p' => DATABASE_DB_TABLEPREFIX . self::paymentTable));
		$select->join(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable), 'r.reservation_id = p.reservation_id', $select::SQL_STAR);
		$select->where(array('p.reservation_id' => $id));
		$select->order('payment_date ASC');
	
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new PaymentVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getPaymentByTenant ($id){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('p' => DATABASE_DB_TABLEPREFIX . self::paymentTable));
		$select->join(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable),'r.reservation_id = p.reservation_id', $select::SQL_STAR);
		$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);
		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new PaymentVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getPaymentByTenancy ($id){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('p' => DATABASE_DB_TABLEPREFIX . self::paymentTable));
		$select->join(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable),'r.reservation_id = p.reservation_id', $select::SQL_STAR);
		$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);
		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new PaymentVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getPayments () {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('p' => DATABASE_DB_TABLEPREFIX . self::paymentTable));
		$select->join(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable),'r.reservation_id = p.reservation_id', $select::SQL_STAR);
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'r.tenant_id = t.tenant_id', $select::SQL_STAR);
		$select->join(array('tc' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'r.tenancy_id = tc.tenancy_id', array('tenancy_name' => 'name'));

		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new PaymentVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}

	public static function getPayment ($id) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('p' => DATABASE_DB_TABLEPREFIX . self::paymentTable));
		$select->join(array('r' => DATABASE_DB_TABLEPREFIX . self::reservationTable),'r.reservation_id = p.reservation_id', $select::SQL_STAR);
		$select->join(array('t' => DATABASE_DB_TABLEPREFIX . self::tenantTable),'r.tenant_id = t.tenant_id', $select::SQL_STAR);
		$select->join(array('tc' => DATABASE_DB_TABLEPREFIX . self::tenancyTable),'r.tenancy_id = tc.tenancy_id', array('tenancy_name' => 'name'));
		$select->where(array('p.payment_id' => $id));
		
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new PaymentVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	// this avoid to have mixed content of "COMMENT" columns
	public static function getPaymentOnly ($id) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('p' => DATABASE_DB_TABLEPREFIX . self::paymentTable));
		$select->where(array('p.payment_id' => $id));
		
		$selectString = $sql->getSqlStringForSqlObject($select);
		Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
		$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
		$data = array();
		foreach ($results->toArray() as $row) {
			$data[] = new PaymentVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}

}

?>
