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

class UserDao {
	
	const userTable = 'users';
	const INDENT1 = "    ";
	const INDENT2 = "		";
	const INDENT3 = "			";

	public static function isEnable ($usr){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('u' => DATABASE_DB_TABLEPREFIX . self::userTable));
		$select->where(array('username' => $usr, 'enable' => 1));

		$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::INDENT2 . 'Query Result: ' . $resCount);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $resCount;
	}

	public static function insert ($user){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		if (self::userExists($user['username']) == 0) {
			$db = DBConnector::getZendDbConnection();
			$sql = new Zend\Db\Sql\Sql($db);
			$insert = $sql->insert();
			$insert->into(DATABASE_DB_TABLEPREFIX . self::userTable);
			
			$data = array();

			$data['recorddate'] = new Zend\Db\Sql\Expression('NOW()');
			if (!empty($user['username'])) $data['username'] = $user['username'];
			if (!empty($user['email'])) $data['email'] = $user['email'];
			if (!empty($user['comment'])) $data['comment'] = $user['comment'];
			if (!empty($user['password'])) $data['password'] = md5($user['password']);
			$data['enable'] = 1;

			$insert->values($data);

			$selectString = $sql->getSqlStringForSqlObject($insert);
			Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
			$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
			$affectedRows = $results->getAffectedRows();
		}else{
			$affectedRows = -1;
		}
		Logger::debug(self::INDENT2 . 'Query Result: ' . $affectedRows);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $affectedRows;
	}
	public static function update ($user){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$update = $sql->update();
		$update->table(DATABASE_DB_TABLEPREFIX . self::userTable);
		
		$data = array();
		if (!empty($user['username'])) $data['username'] = $user['username'];
		if (!empty($user['email'])) $data['email'] = $user['email'];
		if (!empty($user['comment'])) $data['comment'] = $user['comment'];

		$update->where(array('user_id' => $user['user_id']));

		$update->set($data);
	
		$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 enable ($usr){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$update = $sql->update();
		$update->table(DATABASE_DB_TABLEPREFIX . self::userTable);
		
		$data = array();
		$data['enable'] = 1;
		$update->where(array('user_id' => $usr));

		$update->set($data);
	
		$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 setLogonDate ($usr) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		if (self::userExists($usr) == 1) {
			$db = DBConnector::getZendDbConnection();
			$sql = new Zend\Db\Sql\Sql($db);
			$update = $sql->update();
			$update->table(DATABASE_DB_TABLEPREFIX . self::userTable);
			$update->where(array('username' => $usr));

			$data = array();
			$data['lastconnectdate'] = new Zend\Db\Sql\Expression('NOW()');

			$update->set($data);

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

	public static function userExists ($usr){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('u' => DATABASE_DB_TABLEPREFIX . self::userTable));
		$select->where(array('username' => $usr));
	
		$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::INDENT2 . 'Query Result: ' . $resCount);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $resCount;
	}

	public static function userLogin ($usr, $pwd){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		if (self::userExists($usr) == 1) {
			$db = DBConnector::getZendDbConnection();
			$sql = new Zend\Db\Sql\Sql($db);
			$select = $sql->select();
			$select->from(array('u' => DATABASE_DB_TABLEPREFIX . self::userTable));
			$select->where(array('username' => $usr, 'password' => md5($pwd)));
		
			$selectString = $sql->getSqlStringForSqlObject($select);
			Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
			$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
			$resCount = $results->count();
			if ($resCount > 0){
				if (!self::isEnable($usr) > 0) {
					$resCount = -2;
				}else{
					self::setLogonDate($usr);
				}
			}else{
			}
		}else{
			$resCount = -1;
		}
		Logger::debug(self::INDENT2 . 'Query Result: ' . $resCount);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $resCount;
	}

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

		if (isset($str)) {
			$select->where("username LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
			$select->where("email LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
			$select->where("comment 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 UserVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function searchUser ($str) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('u' => DATABASE_DB_TABLEPREFIX . self::userTable));

		$select->where("username LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("email LIKE '%$str%'", Zend\Db\Sql\Predicate\PredicateSet::OP_OR);
		$select->where("comment 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 UserVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}

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

		$select->where(array('user_id' => $userid));

		$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 UserVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getUserByEmail ($email){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('u' => DATABASE_DB_TABLEPREFIX . self::userTable));

		$select->where(array('email' => $email));

		$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 UserVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	public static function getUserByResetId ($resetid){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$select = $sql->select();
		$select->from(array('u' => DATABASE_DB_TABLEPREFIX . self::userTable));

		$select->where(array('resetid' => $resetid));

		$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 UserVo($row);
		}
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $data;
	}
	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::userTable);
		$delete->where(array('user_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 addUser ($usr, $pwd, $email) {
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		if (self::userExists($usr) == 0) {
			$db = DBConnector::getZendDbConnection();
			$sql = new Zend\Db\Sql\Sql($db);
			$insert = $sql->insert();
			$insert->into(DATABASE_DB_TABLEPREFIX . self::userTable);

			$data = array();
			$data['enable'] = 0;
			$data['recorddate'] = new Zend\Db\Sql\Expression('NOW()');
			$data['username'] = $usr;
			$data['password'] = md5($pwd);
			$data['email'] = $email;

			$insert->values($data);

			$selectString = $sql->getSqlStringForSqlObject($insert);
			Logger::debug(self::INDENT2 . 'Execute query: ' . $selectString);
			$results = $db->query($selectString, $db::QUERY_MODE_EXECUTE);
			$resCount = $results->getAffectedRows();
		}else{
			$resCount = -1;
		}
		Logger::debug(self::INDENT2 . 'Query Result: ' . $resCount);
		Logger::debug(self::INDENT1 . '<-- [' . __FUNCTION__ . ']');
		return $resCount;
	}
	public static function changePassword($userid, $pwd){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$update = $sql->update();
		$update->table(DATABASE_DB_TABLEPREFIX . self::userTable);
		
		$data = array();
		$data['password'] = md5($pwd);
		$update->where(array('user_id' => $userid));

		$update->set($data);
	
		$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 setResetId($userid, $resetid){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$update = $sql->update();
		$update->table(DATABASE_DB_TABLEPREFIX . self::userTable);
		
		$data = array();
		$data['resetid'] = $resetid;
		$update->where(array('user_id' => $userid));

		$update->set($data);
	
		$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 clearResetId($userid){
		Logger::debug(self::INDENT1 . '--> [' . __FUNCTION__ . ']');
		$db = DBConnector::getZendDbConnection();
		$sql = new Zend\Db\Sql\Sql($db);
		$update = $sql->update();
		$update->table(DATABASE_DB_TABLEPREFIX . self::userTable);
		
		$data = array();
		$data['resetid'] = NULL;
		$update->where(array('user_id' => $userid));

		$update->set($data);
	
		$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;
	}
}

?>
