<?PHP

/*
 *  W-AGORA 4.0
 *  -----------
 *  $Id: dbaccess.php,v 1.179 2005/04/14 13:10:12 mdruilhe Exp $
 *  Usage:      include file - Generic Database access functions
 *  Authors:    Marc Druilhe <mdruilhe@w-agora.com>
 *              Tom deLombarde <tomd@blackflute.com>
 *              Franky <liedekef@pandora.be>
 *              Raptor <XpL_Raptor@gmx.net>
 *              Ralf Schaefer <ralf.schaefer@gmx.ch>
 *      ... and others
 */

if (defined("_DBACCESS")) return;
define('_DBACCESS', 1);
$DBACCESS=1;

define ("ERR_EXISTS", -1);
define ("ERR_NOTARRAY", -2);
define ("ERR_ACCESS", -3);
define ("ERR_NOTFOUND", -4);
define ("ERR_BADARG", -5);

class DBaccess {

	/* public: result array and current row number */
	var $Record   = array();
	var $Row;

	/* public: current error number and error text */
	var $Errno    = 0;
	var $Error    = "";

	var $debug = 0;				// 1 = turns debug mode On
	var $sql_stats = 0;			// 1 = turns SQL statistics display On
	var $Halt_On_Error = "yes"; // see setHaltOnError()

	var $children = array();	// array of keys
	var $entries = array();		// array in which notes are stored
	var $users = array();		// loads threads contributors here
	var $lineNo;

	var $autocommit = 1;

	var $autoID = array();

	var $start_time = 0;
	var $sql_time = 0;
	var $query_count = 0;

	var $site;
	var $dbtype;

# ------------------------------
# General functions (see PHPLIB)
# ------------------------------

/* private: error handling */
function halt($msg) {
	if ($this->Halt_On_Error == "no")
		return;

	$reason =  $this->Error;
	if (!empty($this->Errno) ) {
		$reason = '['.$this->Errno.'] '.$reason;
	}

	print '</td></tr></table></td></tr></table></td></tr></table>';
	if ($this->Halt_On_Error == "report") {
		if(!defined('MSG_WARNING')) {
			 define ('MSG_WARNING', "<b>Warning:</b> <tt>%s</tt><br>\n%s<br>"); // text, reason
		}
		printf(MSG_WARNING, $msg, $reason);
	} else {
		if(!defined('MSG_ERROR')) {
			define ('MSG_ERROR', "<b><font color=\"red\">%s error: </font></b><tt>%s</tt><br>\n%s<br>\n"); // component, msg, reason
		}
		printf(MSG_ERROR, 'Database', $msg, $reason);
	}

	if ($this->Halt_On_Error != "report") {
		if(!defined('MSG_SESSION_HALTED')) {
			 define ('MSG_SESSION_HALTED', 'Session halted.');
		}
		die(MSG_SESSION_HALTED);
	}
}

/* public: perform a query */
function query($Query_String) {
	echo "function <b>query()</b> not implemented<br>";
	return 0;
}

/**
 * Perform a query and allows control over which rows are returned
 *
 * Detail description
 * @param   $Query_String   the SQL query
 * @param   $start          the offset of the first row to be returned
 * @param   $limit          the number of rows to be returned
 * @since   4.1.6
 * @access  public
 * @see query
 * @return  void
 */
function queryLimit ($Query_String, $start=0, $limit=0) {
	echo "function <b>queryLimit()</b> not implemented<br>";
	return 0;
} // end func

/* public: walk result set */
function next_record() {
	echo "function <b>next_record()</b> not implemented<br>";
	return 0;
}

/* public: position in result set */
function seek($pos = 0) {
	echo "function <b>seek()</b> not implemented<br>";
}

function affected_rows() {
	echo "function <b>affected_rows()</b> not implemented<br>";
}

function num_rows() {
	echo "function <b>num_rows()</b> not implemented<br>";
}

function num_fields() {
	echo "function <b>num_fields()</b> not implemented<br>";
}

/* public: shorthand notation */
function nf() {
	return $this->num_rows();
}

function np() {
	print $this->num_rows();
}

function f($Name) {
	return $this->Record[$Name];
}

function p($Name) {
	print $this->Record[$Name];
}

function setDebug ($flag) {
	 $this->debug=$flag;
	 if ($this->debug) {
	 	$this->sql_stats = 1;
	 }
}

/**
 * Print out an error message only if Debug mode is set
 *
 * @param   string  $msg    The message
 * @since   4.1.2
 * @access  private
 * @return  void
 */
function printDebug($msg) {
	if ($this->debug) print "Debug: <tt>$msg</tt><br>\n";
} // end func

/**
 * Set the "Halt On Error" flag and returns the old value
 *
 * @param  string   $value  possible values: "yes" (halt with message), "no" (ignore errors quietly), "report" (ignore error, but spit a warning)
 * @since     4.1.2
 * @access    public
 * @return    boolean   The old value
 */
function setHaltOnError ($value="yes") {
	$old = $this->Halt_On_Error;
	$this->Halt_On_Error = $value;
	return $old;
}

/**
 * Returns the elapsed time since the object has been instantiated
 *
 * @access  public
 * @return  string  exec_time (formated)
 */
function getExecTime () {
	$mtime = explode(' ', microtime());
	$mtime = $mtime[1] + $mtime[0];
	return sprintf("%.3f ms.", ($mtime - $this->start_time)*1000 );
} // end func

/**
 * Returns the Total SQL elapsed time and number of queries done
 * since the object has been instantiated
 *
 * @access  public
 * @return  string  stats (formated)
 */
function getSessionStats () {
	$ret = "<br>total session time: " . $this->getExecTime();
	$ret .= sprintf ("<br> %d total queries in %.3f ms", $this->query_count, $this->sql_time*1000);
	return $ret;
}

#   ------------------------------------------------------------------
#               General database access functions
#   ------------------------------------------------------------------

function nextId ($seqname, $table="") {
	if (empty ($this->autoID[$seqname])) {
		$this->autoID[$seqname] = time();
	} else {
		$this->autoID[$seqname] += 1;
	}
	return $this->autoID[$seqname];
}

/**
 * return the auto-generated ID or sequence number returned by the last INSERT query performed
 * or by the last call to nextId()
 * @returns     the id of the last sequence number
 */
function lastId($seqname, $table="") {
	return $this->autoID[$seqname];
}

function setAutoCommit ($mode=1) {
	$this->autocommit = $mode;
	return TRUE;
}

function commit () {
	return TRUE;
}

function rollback () {
	return TRUE;
}

function openDB ($dbhost="", $dbport="", $dbuser="", $dbpassword="", $dbname="", $site="", $persistent=0) {

	$this->site = $site;

	$mtime = explode(" ", microtime());
	$this->start_time = $mtime[1] + $mtime[0];

	$ret = $this->_openDB($dbhost, $dbport, $dbuser, $dbpassword, $dbname, $site, $persistent);
	if ($ret<0) {
		$this->printDebug ("Open ".$this->dbtype." Database connection (dbname: $dbname, dbhost: $dbhost, dbuser: $dbuser) => failed (return code: $ret)");
	} else {
		$this->printDebug ("Open ".$this->dbtype." Database connection (dbname: $dbname, dbhost: $dbhost, dbuser: $dbuser) => OK [".$this->dblink."]");
	}

	return $ret;
}

function _openDB ($dbhost="", $dbport="", $dbuser="", $dbpassword="", $dbname="", $persistent=0) {
	// not currently implemented
	return 0;
}

function closeDB () {

	if (empty($this->dblink)) {
		$ret = 0;
	} else {
		$this->printDebug ("Closing Database connection: [".$this->dblink."]");
		$ret = $this->_closeDB();
		$this->dblink = 0;
	}
	if ($this->sql_stats) {
		print $this->getSessionStats();
	}
	return $ret;
}

/**
 * Close the database.
 *
 * Detail description
 * @access	private
 * @see	closeDB
 * @return	boolean
 */
function _closeDB () {
	return true;
}


/**
 * create a new table
 *
 * @param   string	$table		the table 
 * @param   array	$field_defs	Fields definition : array ('colname' => 'type')
 * @param   string	$pk			primary key column name
 * @since   4.2
 * @access  public
 * @see     deleteTable
 * @return  void
 */
function createTable ($table, $field_defs, $pk="") {
	 echo "function <b>createTable()</b> not implemented<br>";
}

/**
 * create an index on the specified table
 *
 * @param   string  $table		the table 
 * @param   string  $cols		the columns ('col1[,col2,...]') on which to set the index 
 * @param   string  $unique		unique index or not
 * @param   string  $indexname	the optional name of the index
 * @since   4.2
 * @access  public
 * @see     dropIndex
 * @return  void
 */
function createIndex ($table, $cols, $unique=false, $indexname="") {

	if (empty($indexname)) {
		$indexname = "$table".'_'. str_replace(',', '_', $cols) . '_idx';
	}

	if ($unique) {
		$ret = $this->query ("CREATE UNIQUE INDEX $indexname ON $table($cols)");	
	} else {
		$ret = $this->query ("CREATE INDEX $indexname ON $table($cols)");	
	}

	if (!$ret) {
		return -3;
	}
	return 0;
}

/**
 * drop an index on the specified table
 *
 * @param   string  $table  the table 
 * @param   string  $indexname    the name of the index
 * @param   string  $cols    the columns on which the index is set (if indexname is not known)
 * @since   4.2
 * @access  public
 * @see     createIndex
 * @return  void
 */
function dropIndex($table, $indexname="", $cols="") {

	if (empty($indexname)) {
		if (empty($cols) ) {
			$this->halt("dropIndex(): invalid argument, either indexname or cols must be supplied");
			return -3;
		}
		$indexname = "$table" .'_'.str_replace(',', '_', $cols) . '_idx';
	}
	$ret = $this->query ("DROP INDEX $indexname ON $table");	
	if (!$ret) {
		return -3;
	}
	return 0;
}

function addPrimaryKey ($table, $cols) {

	$pkname = $table. '_pk';
	$ret = $this->query ("ALTER TABLE $table ADD CONSTRAINT $pkname PRIMARY KEY ($cols)");
}

function deleteTable ($table) {

	$ret = $this->query ("DROP TABLE $table");
	if (!$ret) {
		return -3;
	}
	return 0;
}

function updateTable ($table, &$fields, $where) {
	 echo "function <b>updateTable()</b> not implemented<br>";
}

function preserveQuotes($str) {
	return addslashes($str);
}

/**
 * Add a new column (field) in the specified table
 *
 * Currently, doesnt handle AUTO-increment
 * @param   string  $table  the table where the field is added
 * @param   string  $col    the table where the field is added
 * @param   string  $size   The field definition : INT, TEXT, BLOB, nnn (varchar(n) )
 * @since   4.1
 * @access  public
 * @see     createTable
 * @return  void
 */
function addField ($table, $col, $size) {

	if (!ereg ("^[[:alnum:]_]+$", $col) ) {
		$this->halt("addField(): invalid argument for column name ($col)");
		return -3;
	}

	if (!ereg ("^[[:alnum:]]+$", $size) ) {
		$this->halt("addField(): invalid argument for field description ($size)");
		return -3;
	}

	if (ereg ("^[0-9+]+$", $size) ) {
		if ($size>255) {
			$desc = 'TEXT';
		} else {
			$desc = "VARCHAR($size) NOT NULL DEFAULT ''";
		}
	} else {
		$type = strtoupper($size);
		switch ($type) {
			case "INT":
				$desc = "INT NOT NULL DEFAULT 0";
				break;
			default:
				$desc = $type;
				break;
		}
	}

	$ret = $this->query ("ALTER TABLE $table ADD $col $desc");
}

/**
 * drop a column (field) from the specified table
 *
 * @param   string  $table  the table where the field must be deleted
 * @param   string  $col    the name of the field
 * @since   4.1
 * @access  public
 * @see     addField
 * @return  void
 */
function dropField ($table, $col) {
	echo "function <b>dropField()</b> not implemented<br>";
}

#   ------------------------------------------------------------------
#               Site management functions
#   ------------------------------------------------------------------

# gets info about site $site
# --------------------------
function getSite ($site) {

	$main_site = "agora";
	$result = $this->query ("SELECT A.*, U.username AS ownername, U.useraddress AS owneraddress FROM $main_site A , ${main_site}_users U WHERE U.userid = A.owner AND A.bn_name='$site'");

	if ($this->next_record() ) {
		return $this->Record;
	} else {
		return -3;
	}
}

function createSite ($site, $descr_file) {

# reads fields description
# -------------------------
	if (!is_file ($descr_file)) {
		echo "error: createSite(): could not include file: '$descr_file'";
		return -4;
	}

	include "$descr_file";

	$field_descr =array();

# Set core variables (managed by w-agora)
# ---------------------------------------
	if (is_array ($bn_var)) {
		reset ($bn_var);
		while (list(, $var) = each($bn_var)) {
			$field_descr[$var] = $bn_var_size[$var];
		}
	}

# add user fields
# ---------------
	if (is_array ($db_var)) {
		reset ($db_var);
		while (list(, $var) = each($db_var)) {
			$field_descr[$var] = $bn_var_size[$var];
		}
	}

# add DB management fields
# ------------------------
	$field_descr["cle"] = "INT";
	$field_descr["parent"] = "INT";
	$field_descr["childs"] = "INT";
	$field_descr["thread"] = "INT";
	$field_descr["newest"] = "INT";

	$ret = $this->createTable ($site, $field_descr, 'cle');
	if($ret <0) {
		return $ret;
	}

	$this->createIndex ($site, 'bn_name');
	$this->createIndex ($site, 'parent');
	return 0;
}

function deleteSite ($site, $halt_on_error="report") {

	$old_report = $this->setHaltOnError ($halt_on_error);

# remove all site tables
# ----------------------
	$ret = $this->deleteTable ($site);
	$ret = $this->deleteTable ("${site}_users");
	$ret = $this->deleteTable ("${site}_userforum");
	$ret = $this->deleteTable ("${site}_userthread");
	$ret = $this->deleteTable ("${site}_attachments");
	$ret = $this->deleteTable ("${site}_log");
	$ret = $this->deleteTable ("${site}_log_t1");
	$ret = $this->deleteTable ("${site}_log_t2");
	$ret = $this->deleteTable ("${site}_log_t3");
	$ret = $this->deleteTable ("${site}_log_t4");
	$ret = $this->deleteTable ("${site}_dailyforumstats");
	$ret = $this->deleteTable ("${site}_dailyuserstats");
	$ret = $this->deleteTable ("${site}_menus");

	$this->setHaltOnError ($old_report);

	return 0;
}

#   ------------------------------------------------------------------
#               LOG / STATS functions
#   ------------------------------------------------------------------


/**
 * Deprecated : uses DBStats class
 */
function getMonthlyStats ($site, $year='', $month='') {
	global $ext, $inc_dir;

	require_once "$inc_dir/dbstats.$ext";
	$stats =& new DBStats($this, $site);
	return $stats->getMonthlyStats ($year, $month);
}

/**
 * Deprecated : uses DBStats class
 */
function getMonthlyVisitors ($site, $year='', $month='') {
	global $ext, $inc_dir;

	require_once "$inc_dir/dbstats.$ext";
	$stats =& new DBStats($this, $site);
	return $stats->getMonthlyVisitors ($year, $month);
}

/**
 * Deprecated : uses DBStats class
 */
function getDailyStats ($site, $year, $month) {
	global $ext, $inc_dir;

	require_once "$inc_dir/dbstats.$ext";
	$stats =& new DBStats($this, $site);
	return $stats->getDailyStats ($year, $month);
}

/**
 * Deprecated : uses DBStats class
 */
function getDailyVisitors ($site, $year, $month) {
	global $ext, $inc_dir;

	require_once "$inc_dir/dbstats.$ext";
	$stats =& new DBStats($this, $site);
	return $stats->getDailyVisitors ($year, $month);
}

/**
 * Insert an entry in the LOG table
 */
function addLogEntry ($forum, $action, $userid, $thread=0, $key=0, $ip='') {
	$log_table = $this->site.'_log';
	$ip = ($ip=='') ? get_remote_ip() : $ip;
	$userid =  $this->preserveQuotes ($userid);
	$this->query ("INSERT INTO $log_table (userid, forum, action, thread, note_id, ip) VALUES ('$userid', '$forum', '$action', '$thread', '$key', '$ip')");
} // end func

function getSiteStats ($site, $show_all=false) {
	global $auth;

	$from = "$site S";
	$where = "S.category=0";

	if (! $show_all) {
		if ( empty($auth->userid) ) {
			$where .= " AND S.state != '0' AND S.rank != 0  AND S.type != 'priv'";
		 } elseif ($auth->level < ADMIN) {
			$userid = $this->preserveQuotes($auth->userid);
			$from .= " LEFT OUTER JOIN ${site}_userforum UF ON (S.bn_name = UF.bn_name AND UF.userid= '$userid')";
			$where .= " AND S.state != '0' AND S.rank != 0 AND (S.type != 'priv' OR UF.listpriv='1')";
		}
	}
	$result = $this->query ("SELECT count(S.bn_name) as tforums, sum(S.totalthreads) as tthreads, sum(S.totalnotes) as tnotes, sum(S.att_count) as tfiles, sum(S.att_size) as tsize, max(S.lastnote) as mlast FROM $from WHERE $where");
	if ($this->next_record() ) {
		$stats['totalforums']   = (int) $this->f('tforums');
		$stats['totalthreads']  = (int) $this->f('tthreads');
		$stats['totalnotes']    = (int) $this->f('tnotes');
		$stats['totalfiles']    = (int) $this->f('tfiles');
		$stats['totalfilesize'] = (int) $this->f('tsize');
		$stats['lastnote'] = (int) $this->f('mlast');
	}

	$result = $this->query ("SELECT count(userid) as totalusers FROM ${site}_users WHERE lastlogin>0");
	if ($this->next_record() ) {
		$stats['totalusers'] = $this->f('totalusers');
	}

	return $stats;
}

/**
 * Get Forum statistics.
 *
 * @param   forum   the forum table ($bn_db)
 * @since   4.1.3
 * @access  public
 * @return  array   totalhits = # views, totalauthors = # of distincts authors
 */
function getForumStats ($forum) {

	$q = "SELECT sum(hits) as totalhits, count(DISTINCT userid) as totalauthors FROM $forum WHERE hidden='0'";
	$this->query("$q");
	if ($this->next_record()) {
		return $this->Record;
	}

	return false;
}


#   ------------------------------------------------------------------
#               Forum management functions
#   ------------------------------------------------------------------

function createForum ($forum, $descr_file) {

# reads fields description
# -------------------------
	if (!is_file ($descr_file)) {
		echo "error: createForum(): could not include file: '$descr_file'";
		return -4;
	}

	include "$descr_file";

	$field_descr =array();

# Set core variables (managed by w-agora)
# ---------------------------------------
	if (is_array($bn_var) && isset($admin_table) ) {
		// $bn_var set in admin templates
		reset ($bn_var);
		while (list(, $var) = each($bn_var)) {
			$field_descr[$var] = $bn_var_size[$var];
		}
	} else {
		// set default w-agora variables for a new forum
		$field_descr["unixdate"]    = "INT";
		$field_descr["userid"]      = "32";
		$field_descr["mod_date"]    = "INT";
		$field_descr["mod_userid"]  = "32";
		$field_descr["password"]    = "32";
		$field_descr["filename"]    = "64";
		$field_descr["att_size"]    = "INT";
		$field_descr["mail_reply"]  = "1";
		$field_descr["hidden"]      = "INT";
		$field_descr["hits"]        = "INT";
		$field_descr["closed"]      = "INT";
		$field_descr["ip"]          = "32";
	}

# add user fields
# ---------------
	if (is_array ($db_var)) {
		reset ($db_var);
		while (list(, $var) = each($db_var)) {
			$field_descr[$var] = $bn_var_size[$var];
		}
	}

# add DB management fields
# ------------------------
	$field_descr["cle"] = "INT";
	$field_descr["parent"] = "INT";
	$field_descr["childs"] = "INT";
	$field_descr["thread"] = "INT";
	$field_descr["newest"] = "INT";

	$this->createTable ($forum, $field_descr, 'cle');
	$this->createIndex ($forum, 'parent');
	$this->createIndex ($forum, 'thread');
	$this->createIndex ($forum, 'newest');
	$this->createIndex ($forum, 'userid');
}

function deleteForum ($site, $forum) {

# delete table
	$ret = $this->deleteTable ("$forum");
	if ($ret != 0) {
		$this->Error ="could drop table $forum from site $site";
		return $ret;
	}

# Delete entry in the forums table ($site)
# ---------------------------------------
	$ret = $this->deleteForumEntry ($site, $forum);
	if ($ret < 0) {
		$this->Error ="could not delete entry for forum $forum in table $site";
		return -3;
	}

# delete attachments (table $site_attachments) (files are removed outside this function)
# --------------------------------------------------------------------------------------
	$this->query ("DELETE FROM ${site}_attachments WHERE bn_name='$forum'");

# delete all user->forum relations (table $site_userforum)
# --------------------------------------------------------
	$this->deletePrivs ($site, $forum, '');

# Delete user subscriptions for this forum (table $site_userthread)
# -----------------------------------------------------------------
	$this->unSubscribeUser ($site, $forum);

	return 0;
}

function insertForum($site, &$fields) {
	global $now;

# check if forum already exists
	$forum = $fields["bn_name"];
	$result = $this->query ("SELECT bn_name FROM $site WHERE bn_name='$forum'");
	if ($this->next_record() ) {
		return ERR_EXISTS;
	}

	if (empty($fields["parent"])) {
		$fields["parent"] = 0;
	}
	$fields["thread"] = $now;
	$fields["childs"] = 0;
	if (empty($fields["unixdate"])) {
		$fields["unixdate"] = $now;
	}
	$fields["newest"] = $fields["unixdate"];
	$fields["cle"] = $now;
	$fields["category"] = 0;
	$ret = $this->insertRow($site, $fields);
	return ($ret==0) ? $now : $ret;
}

function deleteForumEntry ($site, $forum) {
	$ret = $this->query ("DELETE FROM $site WHERE bn_name='$forum'");
	if (!$ret) {
		$this->Error ="could not delete entry for forum $forum in table $site";
		return -3;
	}
	return 0;
}

# gets info about forum $name in site $site
# -----------------------------------------
function getForum ($site, $name) {

	$result = $this->query ("SELECT S.parent AS cat_id, S.*, C.bn_title AS cat_title, U.username AS ownername, U.useraddress AS owneraddress FROM $site S , ${site}_users U ,$site C WHERE U.userid = S.owner AND C.cle=S.parent AND S.bn_name='$name'");

	if ($this->next_record() ) {
		return $this->Record;
	} else {
		return -3;
	}
}

function updateForum ($site, $forum, $fields) {
	$result =  $this->query ("SELECT bn_name FROM $site WHERE bn_name='$forum'");
	if (!$result) { /* access problem */
		return -3;
	}

	if (!$this->next_record() ) {
		return -4; /* no row selected */
	}

	$fields["bn_name"] = $forum;
	return $this->updateTable ($site, $fields, "bn_name='$forum'");
}

function updateForumStats ($site, $forum) {

	if (!$this->debug) {
		$this->Halt_On_Error = "no";
	}

	$fields = array();

	$result = $this->query ("select count(cle) as totalnotes, max(unixdate) as lastnote from $forum where hidden=0");
	if (!$this->next_record() ) {
		return -1;
	}

	$fields["totalnotes"] = $this-> f("totalnotes");
	$fields["lastnote"] = $this-> f("lastnote");

	$result = $this->query ("select count(cle) as totalthreads from $forum where parent=0 and hidden=0");
	if (! $this->next_record() ) {
		return -1;
	}
	$fields["totalthreads"] = $this-> f("totalthreads");

	$result = $this->query ("SELECT count(att_size) AS cnt, sum(att_size) AS sz FROM ${site}_attachments WHERE bn_name='$forum' AND hidden!='1'");
	if ($this->next_record() ) {
		$fields['att_count'] = (int) $this-> f('cnt');
		$fields['att_size'] = (int) $this-> f('sz');
	}

	$ret = $this->updateTable ($site, $fields, "bn_db='$forum'");

	$this->Halt_On_Error = "yes";
	return $ret;
}

function getForums ($site, $moder="", $sort="", $hide_inactive=1, $cat='') {
	global $auth;

	$select = "S.bn_name, S.bn_title";
	$where = "S.category=0";

	if ($hide_inactive) {
		$where .= " AND S.state != '0' AND S.rank != 0";
	}

	if ($cat != '') {
		$where .= " AND S.parent=$cat";
	}

	if (empty($auth->userid)) {
		$from = "$site S";
		$where .= " AND S.type != 'priv'";
	} elseif (!empty($moder)) {
		$from = "$site S, ${site}_userforum UF";
		$userid = $this->preserveQuotes($moder);
		$where .= " AND S.bn_name=UF.bn_name AND UF.userid = '$userid' AND UF.modpriv=1";
	} elseif ($auth->level < ADMIN) {
		$userid = $this->preserveQuotes($auth->userid);
		$from = "$site S LEFT JOIN ${site}_userforum UF ON (S.bn_name=UF.bn_name AND UF.userid = '$userid')";
		$where .= " AND (S.type != 'priv' OR UF.listpriv=1)";
	} else {
		$from = "$site S";
	}

	$order = "S.parent";

	if (empty($sort)) {
		$order .= ",S.rank,S.bn_title";
	} else {
		$a = explode (",", $sort);
		foreach($a as $s) {
			$order .= ", S.".trim($s);
		}
	}

	$result = $this->query ("SELECT $select FROM $from WHERE $where ORDER BY $order");
	while ($this->next_record() ) {
		$name = $this->f("bn_name");
		$forums[$name] = $this->f("bn_title");
	}
	return $forums;
}

/**
 * First version, only one category level
 */
function listForums ($site, $moder="", $sort="", $hide_inactive=0, $cat='') {
	global $auth;

	if ($site=="agora") {
		// get all sites
		$query = "SELECT S.*, 1 as catorder, U.username as ownername, U.useraddress as owneraddress FROM $site S, ${site}_users U WHERE U.userid = S.owner";
	} elseif (!empty($moder)) {
		// gets all forums for wich $moder is moderator
		$u = $this->preserveQuotes($moder);
		$query = "SELECT S.*, C.rank AS catorder, U.username as ownername, U.useraddress as owneraddress FROM $site S, $site C, ${site}_users U, ${site}_userforum UF WHERE U.userid = S.owner AND S.parent=C.cle AND S.category!=1 AND S.bn_name=UF.bn_name AND UF.userid = '$u' AND UF.modpriv=1";
	} elseif ($auth->level < ADMIN) {
		// gets only the forums that the current user can list (ie not private or with list privilege) 
		$u = (empty($auth->userid)) ? 'guest' : $this->preserveQuotes($auth->userid);
		$query = "SELECT S.*, C.rank AS catorder, U.username AS ownername, U.useraddress AS owneraddress FROM  ${site}_users U, $site C, $site S LEFT JOIN ${site}_userforum UF ON (S.bn_name=UF.bn_name AND UF.userid = '$u') WHERE U.userid = S.owner AND C.cle=S.parent AND S.category!=1 AND (S.type != 'priv' OR UF.listpriv=1)";
	} else {
		// gets all forums for an admin 
		$query = "SELECT S.*, C.rank AS catorder, U.username as ownername, U.useraddress as owneraddress FROM $site S, $site C, ${site}_users U WHERE U.userid = S.owner AND S.parent=C.cle AND S.category!=1";
	}

	if ($hide_inactive) {
		$query .= " AND S.state != '0' AND S.rank != 0";
	}

	if ($cat != '') {
		$query .= " AND S.parent=$cat";
	}

	if (empty($sort)) {
		$order = ",S.bn_title";
	}else {
		$order = '';
		$a=explode (",",$sort);
		foreach($a as $s) {
			$order .= ", S.".trim($s);
		}
	}

	$query .= " ORDER BY catorder, S.parent, S.rank". $order;
	$result = $this->query ($query);
	reset ($this->entries);
	while ($this->next_record() ) {
		$key = $this->Record['cle'];
		$this->entries[$key] = $this->Record;
		$this->children[0][] = $key;

		$name = ereg_replace ("^${site}_", "", $this->Record['bn_name']);
		$forums[$name] = $this->Record;	
	}

	return $forums;
}

/**
 * Deprecated : uses ListRenderer class
 */
function displayForumList () {
	global $ext,$inc_dir;

	require_once "$inc_dir/listrenderer.$ext";
	$list =& new ListRenderer($this);
	$list->displayForumList();
	unset($list);
}


#   ------------------------------------------------------------------
#               Categories functions
#   ------------------------------------------------------------------

function getCategories ($site) {

	$cats =false;
	$query = "SELECT cle, rank, bn_title FROM $site WHERE category=1 ORDER BY rank, bn_title";
	$result = $this->query ($query);
	while ($this->next_record() ) {
		$cat_id = $this->Record["cle"];
		$cats[$cat_id] = $this->Record["bn_title"];
	}
	return $cats;
}

function addCategory ($site, $cat, $parent=0) {
	global $now;

	$fields["cle"]      = $now;
	$fields["thread"]   = $now;
	$fields["unixdate"] = $now;
	$fields["newest"]   = $now;
	$fields["parent"]   = (int) $parent;
	$fields["childs"]   = 0;
	$fields["bn_title"] = $cat["title"];
	$fields["rank"]     =  $cat["order"];
	$fields["owner"]    = $cat["owner"];
	$fields["category"] = "1";
	$fields["state"]    = "1";
	$fields["category"] = "1";
	// $fields["bn_name"]   = "category";
	$ret = $this->insertRow($site, $fields);
	return ($ret==0) ? $now : $ret;
}

function deleteCategory ($site, $key) {
	$ret = $this->query ("DELETE FROM $site WHERE cle=$key");
	if ($ret) {
		// Change category for forums that were in this category
		$f["parent"] = "0";
		return $this->updateTable ("$site", $f, "parent=$key");
	}

}

function getCategory ($site, $key) {
	$ret = $this->query ("SELECT bn_title AS cat_title, rank, state, owner FROM $site WHERE cle=$key");
	if ($this->next_record() ) {
		return $this->Record;
	} else {
		return -3;
	}

}

#   ------------------------------------------------------------------
#               User management functions
#   ------------------------------------------------------------------

function getUser($site, $value, $field="userid") {

	if (empty($value)) {
		return ERR_BADARG;
	}
	$query = sprintf ("SELECT * FROM ${site}_users WHERE $field='%s'", $this->preserveQuotes($value) );
	$result = $this->query ($query);
	if ($this->next_record() ) {
		return $this->Record;
	} else {
		return -3;
	}
}

/**
 * reads a user from the db and chacks their password.
 * if passwords don't match it returns nothing.
 */
function getUserCheckPassword($site, $userid, $password) {

	$user =$this->getUser($site, $userid);
	if (is_array($user) && ($password != $user["password"]) )
		return;
	else
		return $user;
}

/**
 * returns the latest registered user from the site user database
 *
 * @author  Raptor
 * @param   string  $site       The site where to find the user
 * @returns array   "userid"    The user's id
 *          "username"  The user's name
 *          "useraddress"   The user's email address
 */
function getNewestUser($site) {
	$this->query ("SELECT max(unixdate) AS lastcle FROM ${site}_users WHERE lastlogin>0");
	if (!$this->next_record() ) {
		return -3;
	}

	$lastcle = $this->Record["lastcle"];
	$this->query ("SELECT userid, username, useraddress FROM ${site}_users WHERE unixdate='$lastcle'");
	if ($this->next_record() ) {
		$u = array (
			'userid'      => $this->Record["userid"],
			'username'    => $this->Record["username"],
			'useraddress' => $this->Record["useraddress"]
		);
		return $u;
	} else {
		return -3;
	}
}

/**
 *  get all site administrators (userid, username, useraddress) into an array
 *
 */
function getAdministrators ($site) {

	$users = array();

	// get all administrators
	$query = "SELECT userid, username, useraddress FROM ${site}_users WHERE userpriv='admin' OR userpriv='root'";

	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

	$i=0;
	while ($this->next_record() ) {
		$u = $this->Record["userid"];
		$users[$u] = $this->Record;
		$i++;
	}

	return ($i>0) ? $users : 0;
}

/**
 *  get all moderators having moderator privilege on a given forum
 *  (userid, username, useraddress) into an array
 */
function getModerators ($site, $forum) {

	$users = array();

	// get all moderators having moderator privilege on this forum
	$query = "SELECT U.userid, U.username, U.useraddress FROM ${site}_users U, ${site}_userforum UF WHERE U.userid=UF.userid AND U.userpriv='moder' AND UF.bn_name='$forum' AND UF.modpriv=1";

	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

	$i=0;
	while ($this->next_record() ) {
		$u = $this->Record["userid"];
		$users[$u] = $this->Record;
		$i++;
	}

	return ($i>0) ? $users : 0;
}

/**
 *  get all users in the site 'site' matching the given criteria
 *  - if $forum is set, then search only users which have access to this forum
 *      - if $privs is set, then search only users which a given privilege (admin, moder, user)
 *      - if $userid is set, then returns only users where userid begins with $userid
 *      - if $state is set, then search only users in the given state (active, pending, inactive)
 */
function getUsers($site, $forum="", $privs="", $userid="", $where="", $sort="userid") {

	if (!empty($forum) ) {
		$query = "SELECT U.*, UF.state, UF.bn_name FROM ${site}_users U, ${site}_userforum UF WHERE U.userid=UF.userid AND UF.bn_name='$forum'";
	} else {
		$query = "SELECT U.* FROM ${site}_users U WHERE U.unixdate>0";
	}

	if (!empty($userid)) {
		$query .= " AND (U.userid LIKE '$userid%' OR U.username LIKE '$userid%')";
	}

	if (!empty($privs)) {
		$p = split (',' , $privs);
		$query .= " AND (U.userpriv='" . implode ("' OR U.userpriv='", $p) . "')";
	}

	if ($where != "") {
		$query .= " AND $where";
	}

	if (!empty($sort)) {
		$query .= " ORDER BY U.$sort";
	}

	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

	$i=0;
	$users = array();

	while ($this->next_record() ) {
		$u = $this->Record["userid"];
		$users[$u] = $this->Record;
		$i++;
	}

	return ($i>0) ? $users : 0;
}


/**
 *  get all users in the site 'site'
 *  @returns    array   $users  associative array (pairs userid/username)
 */
function getAllUsers($site, $sort="username", $where="") {

	if (empty($sort)) {
		$fields = "userid";
		$order = "userid";
	} else {
		$fields = "userid,$sort";
		$order = $sort;
	}

	$query = "SELECT $fields FROM ${site}_users";

	if (!empty($where)) {
		$query .= " WHERE $where";
	}

	$query .= " ORDER BY $order";

	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

	while ($this->next_record() ) {
		$userid = $this->Record["userid"];
		$users[$userid] = $this->Record[$order];
	}

	return $users;
}

/**
 * get all authors (registered or not) who posted into one or all forums
 *
 * @param   string $site    The site name
 * @param   string $forum   The forum name ($bn). If empty, then all forums will be searched
 * @param   string $where   Optional restrictive where clause
 * @see     getAllUsers()
 * @since   4.1
 * @access  public
 * @return  array   $users  associative array (pairs username/username)
 */
function getAllAuthors($site, $forum="", $where="") {

 # gets all forums => load $forums[] with all tables names in this site
 # -------------------------------------------------------------------
	$query = "SELECT distinct bn_db, bn_name FROM $site WHERE state != '0' AND category=0";
	if (!empty($forum) ) {
		$query .= " AND bn_name='$forum'";
	}

	$result = $this->query ($query);
	while ($this->next_record() ) {
		$table = $this->Record["bn_db"];
		$forums[$table] = $this->Record["bn_name"];
	}

	if (!is_array ($forums) ) {
		return -1;
	}

	reset ($forums);
	while (list($table, $bn) = each ($forums) ) {
		$query = "SELECT DISTINCT username FROM $table";
		if (!empty($where)) {
			$query .= " WHERE $where";
		}

		$result = $this->query ($query);
		while ($this->next_record() ) {
			$username = $this->Record["username"];
			$users[$username] = $this->Record["username"];
		}
	}

	asort ($users);
	return $users;
}

/**
 *  get all users in the site 'site' who post into a forum
 *  @returns    array   $users  associative array (pairs userid/username)
 */
function getActiveUsers($site, $forum="", $sort="username") {

	$query = "SELECT DISTINCT userid,username FROM ${site}_userforum";

	if (!empty($forum)) {
		$query .= " AND bn_name='$forum'";
	}

	if (!empty($sort)) {
		$query .= " ORDER BY $sort";
	}

	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

	while ($this->next_record() ) {
		$userid = $this->Record["userid"];
		$users[$userid] = $this->Record["username"];
	}

	return $users;
}

/**
 * Gets all users that didn't login into the site or didn't post any
 * message since a given date
 *
 * @param   string $site    The site name
 * @param   string $date    The ending date
 * @param   string $type    type of search (post | login)
 * @param   string $sort    Optional order clause (default userid)
 * @return  array   $users  the users that matched the query
 */
function getInactiveUsers($site, $date, $type="post", $sort="userid") {

# Gets all users according to the action "type" ("last post" or "last login")
# ---------------------------------------------------------------------------
	if ($type=="post") {
		$query = "SELECT * FROM ${site}_users WHERE userpriv='user' AND lastpost < $date AND unixdate < $date";
	} else {
		$query = "SELECT * FROM ${site}_users WHERE userpriv='user' AND lastlogin < $date AND unixdate < $date";
	}

	if (!empty($sort)) {
		$query .= " ORDER BY $sort";
	}

# Gets all users satisfying the query
# -----------------------------------
	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

	$i=0;
	$users = array();
	while ($this->next_record() ) {
		$users[$i++] = $this->Record;
	}

	return ($i==0) ? "" : $users;
}

function insertUser($site, &$fields) {
	global $now;

	if (!is_array($fields) || empty($fields['userid'])) {
		echo "warning: insertUser(): invalid argument";
		return ERR_BADARG;
	}

	if (empty($fields["unixdate"])) {
		$fields["unixdate"] = $now;
	}

	 // !!! only for compatibility purpose (should be removed, but was set as primary index in older versions)
	$fields["cle"] = $now;

	$userid=$this->preserveQuotes($fields['userid']);

	$result = $this->query ("SELECT userid FROM ${site}_users WHERE userid='$userid'");
	if ($this->next_record() ) {
		return -3;      // key already exists, try later...
	}

	$ret = $this->insertRow("${site}_users", $fields);
	return ($ret==0) ? $now : $ret;
}

function deleteUser ($site, $userid) {

	$userid=$this->preserveQuotes($userid);

# Unsubscribe this user from receiving emails (to be done before removing him)
	$result = $this->query ("SELECT useraddress FROM ${site}_users WHERE userid='$userid'");
	if ($this->next_record() ) {
		$useraddress = $this->f("useraddress");
		$this->unSubscribeUser ($site, "", $useraddress, 0);
	}

# delete user in users table
	$result = $this->query ("DELETE FROM ${site}_users WHERE userid='$userid'");
	if (!$result) {
		return -3;
	}

# delete privileges in userforum table
	$result = $this->query ("DELETE FROM ${site}_userforum WHERE userid='$userid'");
	if (!$result) {
		return -3;
	}

# remove 'owner' field for all forums owned by this user
	$result = $this->query ("UPDATE $site SET owner='admin' WHERE owner='$userid'");
	if (!$result) {
		return -3;
	}

	return 0;
}

function updateUser($site, $userid, &$fields) {

	$query = sprintf ("SELECT userid,useraddress FROM ${site}_users WHERE userid='%s'", $this->preserveQuotes($userid) );
	$result =  $this->query ($query);
	if (!$result) { /* access problem */
		return -3;
	}

	if (!$this->next_record() ) {
		return -4; /* no row selected */
	}

	if (isset($fields["useraddress"]) && ($site!= "agora") ) {
		$newaddress = $fields["useraddress"];
		$useraddress = $this->f("useraddress");
		if ($useraddress != $newaddress) {
			$result =  $this->query ("UPDATE ${site}_userthread SET useraddress='$newaddress' WHERE useraddress='$useraddress'");
		}
	}

	return $this->updateTable ("${site}_users", $fields, "userid='".$this->preserveQuotes($userid)."'");
}

function updateUserStats($site, $forum, $userid, $lastpost) {

	$userid = $this->preserveQuotes($userid);
	$result =  $this->query ("SELECT userid FROM ${site}_userforum WHERE userid='$userid' AND bn_name='$forum'");
	if (!$result) { /* access problem */
		return -3;
	}

	if ($this->next_record() ) {
		$query = "UPDATE ${site}_userforum SET totalpost=totalpost+1, lastpost=$lastpost WHERE userid='$userid' AND bn_name='$forum'";
	} else {
		$query = "INSERT INTO ${site}_userforum (userid, bn_name, listpriv, readpriv, writepriv, totalpost, lastpost, state) VALUES('$userid', '$forum', '1',  '1', '1', '1', '$lastpost', '1')";
	}
	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

	$query = "UPDATE ${site}_users SET totalposts=totalposts+1, lastpost=$lastpost, lastforumpost='$forum' WHERE userid='$userid'";
	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

}

function updateUserLogin($site, $userid) {

	global $now;

	if (empty($now)) {
		$now =time();
	}

	$userid=$this->preserveQuotes($userid);
	$result =  $this->query ("SELECT userid FROM ${site}_users WHERE userid='$userid'");
	if (!$result) { /* access problem */
		return -3;
	}

	if (!$this->next_record() ) {
		return ERR_NOTFOUND;
	}

	$result = $this->query ("UPDATE ${site}_users SET totallogins=totallogins+1, lastlogin='$now' WHERE userid='$userid'");
	if (!$result) {
		return -3;
	}
}


#   ------------------------------------------------------------------
#               Permissions / User privileges
#   ------------------------------------------------------------------

function getPrivs($site, $forum, $userid) {

	$userid=$this->preserveQuotes($userid);
	$result = $this->query ("SELECT * FROM ${site}_userforum WHERE bn_name='$forum' AND userid = '$userid'");
	if (!$result) {
		return -3;
	}

	if ($this->next_record() ) {
		return $this->Record;
	} else {
		return -3;
	}
}

function insertPrivs ($site, $forum, $userid, &$fields) {
	global $now;

	if (empty ($forum)) {
		$this->halt("insertPrivs(): invalid argument: forum not set");
		return -3;
	}

	if (empty ($userid)) {
		$this->halt("insertPrivs(): invalid argument: userid not set");
		return -3;
	}

	if (!is_array ($fields) ) {
		$this->halt("insertPrivs(): item to insert is not an array");
		return -3;
	}

# check if permissions already exists
	$this->query ("SELECT bn_name FROM ${site}_userforum WHERE bn_name='$forum' AND userid = '" . $this->preserveQuotes($userid) . "'");

	if ($this->next_record() ) {
		return $this->updatePrivs ($site, $forum, $userid, $fields);
	}

	if (empty($fields["unixdate"])) {
		$fields["unixdate"] = time();
	}

	$fields["userid"] = $userid;
	$fields["bn_name"] = $forum;

	return $this->insertRow("${site}_userforum", $fields);
}

function deletePrivs ($site, $forum="", $userid="") {


	$query = "DELETE FROM ${site}_userforum";
	if (!empty ($userid)) {
		$userid=$this->preserveQuotes($userid);
		$query .= " WHERE userid='$userid'";
	}

	if (!empty ($forum)) {
		if (empty ($userid)) {
			$query .= " WHERE bn_name='$forum'";
		} else {
			$query .= " AND bn_name='$forum'";
		}
	}

	$result = $this->query ($query);
	if (!$result) {
		return (-3);
	}
	return 0;
}

function updatePrivs ($site, $forum, $userid, $fields) {

	$sane_userid = $this->preserveQuotes($userid);
	$result = $this->query ("SELECT userid FROM ${site}_userforum WHERE userid='$sane_userid' AND bn_name='$forum'");
	if (!$this->next_record() ) {
		return -4; /* no row selected */
	}

	$fields["bn_name"] = $forum;
	$fields["userid"] = $userid;

	return $this->updateTable ("${site}_userforum", $fields, "userid='$sane_userid' AND bn_name='$forum'");
}

#   ------------------------------------------------------------------
#               Forum/thread Mail subscriptions
#   ------------------------------------------------------------------

/**
 * Subscribe a user to a forum / thread so that he receive new post by email
 *
 * @param       string  $site           The site in which the forum is
 * @param       string  $forum          The forum where the user is subscribed to
 * @param       string  $useraddress    The email where to send new posts
 * @param       integer $thread         thread number for which replies will be sent (thread subscription)
 *                                      if 0 then all posts into the forum will be sent (forum subscription)
 * @param       integer $keepthreads    In case of forum subscription, don't remove individual threads subscription records
 * @returns     integer $result         0 if OK, negative value if error
 */
function subscribeUser ($site, $forum, $useraddress, $thread=0, $keepthreads=0) {

	settype ($thread, "integer");

	$query = "SELECT thread FROM ${site}_userthread WHERE bn_name='$forum' AND useraddress='$useraddress' AND (thread=$thread";
	$query .= ($thread > 0) ? " OR thread=0)" : ")";

	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

# returns if user already subscribed
# ----------------------------------
	if ($this->next_record() ) {
		return 0;
	}

# If forum subscripton, remove all thread-user subscription records
# -----------------------------------------------------------------
	if ( ($thread == 0) && !$keepthreads) {
		$query = "DELETE FROM ${site}_userthread WHERE useraddress='$useraddress' AND bn_name='$forum'";
		$result = $this->query ($query);
	}

# subscribes user to this thread
# ------------------------------

	$query = "INSERT INTO ${site}_userthread (bn_name, thread, useraddress) VALUES('$forum', $thread, '$useraddress')";
	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}
	return 0;
}

/**
 * Remove a user from a thread/forum subscription
 *
 * @param       string  $site           The site in which the forum is
 * @param       string  $forum          The forum where the user is subscribed to,
 *                                      if empty then the user will be removed from all forums in that site
 * @param       string  $useraddress    The email address to remove, if empty then all users will be removed
 * @param       integer $thread         thread number for which to unsubcribe the user(s), 0 = all forum
 * @param       integer $keepthreads    Don't unsubscribe user for individual threads
 * @returns     integer $result         0 if OK, negative value if error
 */
 function unSubscribeUser ($site, $forum="", $useraddress="", $thread=0, $keepthreads=0) {

	settype ($thread, "integer");

# Either forum, thread or useraddress must be set
# -----------------------------------------------
	if (empty($forum) && empty($thread) && empty($useraddress)) {
		return -3;
	}

	if ($thread > 0) {
		$query = "DELETE FROM ${site}_userthread WHERE thread=$thread";
	} else {
		$query = "DELETE FROM ${site}_userthread WHERE ";
		if ($keepthreads==1) {
			$query .= "thread=0";
		} else {
			$query .= "thread>=0";
		}
	}

	if (!empty($forum) ) {
		$query .= " AND bn_name='$forum'";
	}

	if (!empty($useraddress)) {
		$query .= " AND useraddress='$useraddress'";
	}

	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}
}

/**
 * Check if a user has subscribed to a forum / thread
 *
 * @param       string  $site           The site in which the forum is
 * @param       string  $forum          The forum to check subscription,
 * @param       string  $useraddress    The email address to check
 * @param       integer $thread         thread number or 0 = all forum
 * @returns     boolean $subscribed         TRUE if user has subscribed
 */
function checkSubscribedUser ($site, $forum, $useraddress, $thread=0) {

	setType ($thread, "integer");

	$query = "SELECT useraddress FROM ${site}_userthread WHERE bn_name='$forum' AND thread=$thread AND useraddress='$useraddress'";
	$result = $this->query ($query);

	if ($result && $this->next_record() ) {
		return TRUE;
	}
	return FALSE;
}

/**
 * Get all user addresses that have subscribed to a forum / thread
 *
 * @param       string  $site           The site in which the forum is
 * @param       string  $forum          The forum to check subscription,
 * @param       integer $thread         thread number or 0 = all forum
 * @returns     array   $users          The array of addresses [user@addr, user2@addr]
 */
function getSubscribedUsers ($site, $forum, $thread=0) {

	setType ($thread, "integer");

	$query = "SELECT DISTINCT useraddress FROM ${site}_userthread WHERE bn_name='$forum' AND (thread=$thread";
	$query .= ($thread > 0) ? " OR thread=0)" : ")";
	$query .=" ORDER BY useraddress";

	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

	$i=0;
	while ($this->next_record() ) {
		$useraddresses[$i++] = $this->f("useraddress");
	}
	return $useraddresses;
}

/**
 * Get the list of subscriptions (threads/forums) for a given user
 *
 * @param       string  $site           The site
 * @param       string  $user			The user to get subscriptions
 * @returns     array   $subscriptions	The double-dimensions array of subscriptions :
 *										array ['bn_name']['thread'] => 'thread topic title'
 */
function getSubscriptions ($site, $useraddress, $threads_only=false) {

	// Get all forum/threads subscriptions
	$query = "SELECT bn_name, thread FROM ${site}_userthread WHERE useraddress='$useraddress'";
	if ($threads_only===true) {
		$query .= " AND thread>0";
	}

	$query .= " ORDER BY bn_name, thread";

	// Get all forum/threads subscriptions
	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

	$subscriptions = array();
	while ($this->next_record() ) {
		$bn_name = $this->Record['bn_name'];
		$thread = $this->Record['thread'];
		$subscriptions[$bn_name][$thread] = $thread;
	}

	// Get all topic titles
	foreach ($subscriptions as $bn_name=>$threads) {
		if (is_array($threads)) {
			$thread_list = implode(',', $threads);
			$result = $this->query ("SELECT thread, subject FROM $bn_name WHERE cle IN ($thread_list) ORDER BY subject");
			while ($this->next_record() ) {
				$thread = $this->Record['thread'];
				$subscriptions[$bn_name][$thread] = $this->Record['subject'];
			}
		}
	}

	return $subscriptions;
}

/**
 * Return the number of subscribed users in a site / forum
 *
 * @param       string  $site           The site in which the forum is
 * @param       string  $forum          returns only subscription count from forum '$forum'
 * @returns     integer $cnt            The number of subscribed users
 */
function countSubscribedUsers ($site, $forum='', $thread=0) {

	setType ($thread, "integer");

	$query = "SELECT count(DISTINCT useraddress) AS cnt FROM ${site}_userthread WHERE thread=$thread";
	if ( !empty($forum) ) {
		$query .= " AND bn_name='$forum'";
	}

	$result = $this->query ($query);
	if (!$result) {
		return -3;
	}

	if ($this->next_record() ) {
		return $this->Record['cnt'];
	}

	return 0;
}


#   ------------------------------------------------------------------
#               Attachments management functions
#   ------------------------------------------------------------------

/**
 * insert attachment reference in database
 *
 * @param     string $note_id   id of the associated note
 * @param     string $name
 * @param     string $path
 * @param     string $inline    requested display mode
 * @param     string $size
 * @param     string $type
 * @param     string $state     state of attachement (1: active, 0, inactive, P: pending, D: Deleted)
 * @param     string $hidden
 * @access    public
 * @return    integer           The id of the attachment >0 if OK, a negative value in case of error
 */
function insertAttachment ($forum, $note_id, $name, $path, $inline="A", $size=0, $type="", $state='1', $hidden=0) {

	$upload_table = $this->site . "_attachments";

	$att_id = $this->nextId('attachments', $upload_table);
	if ($att_id <0) {
		return -1;
	}

	$fields["att_id"]   = $att_id;
	$fields["unixdate"] = time();
	$fields["note_id"]  = $note_id;
	$fields["bn_name"]  = $forum;
	$fields["att_name"] = $name;
	$fields["att_path"] = $path;
	$fields["att_size"] = empty ($size) ? filesize($path) : $size;
	$fields["att_type"] = empty ($type) ? "application/octet-stream" : $type;
	$fields["state"]    = $state;
	$fields["hidden"]   = $hidden;
	$fields["inline"]   = $inline;
	$ret = $this->insertRow($upload_table, $fields);
	if ($ret != 0) {
		return -1;
	}

	return $this->lastId('attachments', $upload_table);
}

/**
 * Validate pending files, assign them to the new inserted note
 *  and remove all older files that have not been validated
 *
 * @param     string  $forum    forum name
 * @param     integer $dir      upload directory (needed to remove old files)
 * @param     integer $old_id   temporary id that was previously assigned
 * @param     integer $new_id   id of the associated note
 * @access    public
 * @return    boolean true if OK
 */
function validateAttachments ($forum, $dir, $old_id, $new_id) {

	setType ($old_id, "integer");
	setType ($new_id, "integer");

	$upload_table = $this->site . "_attachments";
	$purgetime = time() - $GLOBALS['oldfiles_gctime'];

	// validate 'pending' files
	$this->query ("UPDATE $upload_table SET note_id=$new_id, state='1' WHERE note_id=$old_id AND state='P' AND bn_name='$forum'");

	// remove files elected to be deleted and purge old files (older than 1 day)
	$whereclause = "(bn_name='$forum') AND (note_id=$old_id AND state='D') OR (state IN ('P', 'D') AND unixdate<$purgetime)";
	$this->query ("SELECT att_id, att_name FROM $upload_table WHERE $whereclause");
	$att_name = "";
	while ($this->next_record() ) {
		$att_name = $this-> f("att_name");
		$att_id = $this-> f("att_id");
		@unlink ("$dir/$att_id.$att_name");
	}

	if (!empty($att_name) ) {
		$this->query ("DELETE FROM $upload_table WHERE $whereclause");
	}

	// adjust attachments size
	$this->query ("SELECT sum(att_size) AS sz FROM $upload_table WHERE bn_name='$forum' AND note_id=$new_id AND state='1'");
	if ($this->next_record()) {
		$att_size = (integer) $this-> f("sz");
		$this->query ("UPDATE $forum SET att_size=$att_size WHERE cle=$new_id");
	}

	$result = $this->query ("SELECT count(att_size) AS cnt, sum(att_size) AS sz FROM $upload_table WHERE bn_name='$forum' AND hidden!='1'");
	if ($this->next_record() ) {
		$fields['att_count'] = (int) $this-> f('cnt');
		$fields['att_size'] = (int) $this-> f('sz');
	}

	$ret = $this->updateTable ($this->site, $fields, "bn_db='$forum'");

	return true;
}

/**
 * Get attachment(s) associated to a note.
 *
 * Detail description
 * @param
 * @since     1.0
 * @access    public
 * @return    array     attachments
 */
function getAttachments ($forum, $note_id=0, $att_id=0) {

	$upload_table = $this->site . "_attachments";
	$select = 'note_id, att_id, att_name, att_type, att_size, att_path, inline, hidden, state';

	if ($att_id>0) {
		// att_id is unique in the attachments table
		$query = "SELECT $select FROM $upload_table WHERE att_id=$att_id";
	} elseif ($note_id>0) {
		// gets all attachments assoctiated with the note
		$query = "SELECT $select FROM $upload_table WHERE note_id=$note_id AND bn_name='$forum' AND state IN ('P', '1') AND hidden!='1' ORDER BY att_id";
	} else {
		// either note_id or att_id must be set
		return ERR_BADARG;
	}
	$this->query ($query);

	$i=0;
	while ($this->next_record() ) {
		$att[$i] = $this->Record;
		$i++;
	}

	return ($i==0 ) ? "" : $att;

}

/**
 * Flag the attachments as "deleted"
 *
 * @param   string $forum   The forum
 * @param   string $key     the id of the associated note
 * @param   string $att_ids ID(s) of attachments to be deleted (can be an array)
 * @since   4.1
 * @access  public
 * @return  void
 */
// FIXME: $forum, $key not necessary here!!! 
function setDeleteAttachment ($forum, $key, $att_ids) {

	$upload_table = $this->site . "_attachments";

	if (is_array($att_ids) ) {
		$att_ids = implode ($att_ids, ",");
		$q = "UPDATE $upload_table SET state='D' WHERE att_id IN ($att_ids)";
	} elseif (!empty($att_ids)) {
		$q = "UPDATE $upload_table SET state='D' WHERE att_id=$att_ids";
	} else {
		return false;
	}

	$this->query($q);
}

/**
 * Flag the attachments as "inline"
 *
 * @param   string $forum   The forum
 * @param   string $key the id of the associated note
 * @param   string $att_id  ID of attachment to be set to inline
 * @param   string $mode    inline mode (1 = Inline, 0 = as Link)
 * @access  public
 * @return  void
 */
// FIXME: $forum, $key not necessary here!!! 
function setInlineAttachment ($forum, $key, $att_id, $mode=0) {

	settype($mode, "integer");
	$upload_table = $this->site . "_attachments";

	$q = "UPDATE $upload_table SET inline='$mode' WHERE att_id=$att_id";
	$this->query($q);
}

/**
 * Delete all attachments associated with a note
 *
 * @param   string $forum   The forum
 * @param   string $key     the id of the associated note
 * @param   string $dir     The directory that contain attachments
 * @param   string $att_id  ID of attachment to be deleted, if none, all will be deleted
 * @since   4.1
 * @access  public
 * @return  void
 */
function deleteAttachment ($forum, $key, $dir, $att_id=0) {

	settype($att_id, "integer");
	settype($key, "integer");
	$upload_table = $this->site . "_attachments";

	$whereclause = ($att_id>0) ? "att_id=$att_id" : "bn_name='$forum' AND note_id=$key";
	$this->query ("SELECT att_id, att_name FROM $upload_table WHERE $whereclause");

	while ($this->next_record() ) {
		$att_name = $this-> f("att_name");
		$att_id = $this-> f("att_id");
		@unlink ("$dir/$att_id.$att_name");
	}

	$this->query ("DELETE FROM $upload_table WHERE $whereclause");
}


#   ------------------------------------------------------------------
#               Notes management functions
#   ------------------------------------------------------------------

/**
 * Get the specified note from the forum
 *
 * Return all fields of the specified note in an associative array
 * + if parameter $sort is set, then the function returns get the IDS of the next/previous notes
 * in the fields $note["tnext"] and $note["tprev"] respectively, according to the given sort order
 *
 * @param   string  $forum  The forum table
 * @param   integer $key    The ID (field: "cle" of the note
 * @param   string  $where  The optional WHERE clause used to retreive next/previous IDS
 * @param   string  $sort   ORDER BY clause used to retreive next/previous IDS
 * @return  array   $note
 */
function getNote ($forum, $key, $where="", $sort="") {
	global $bn_var_size;

	settype($key, "integer");

	$site = $this->site;

# get all fields in selected note
# -------------------------------
	$result = $this->query ("SELECT * FROM $forum WHERE cle=$key");
	if (! $this->next_record() ) {
		return -4;
	}
	$note = $this->Record;

# get key of next/previous thread according to the sort order
# -----------------------------------------------------------
	if (!empty($sort) ) {
		list ($sort_col, $sort_order) = split(' ', $sort, 2);
		if (empty($sort_order)) {
			$sort_order = "ASC";
		}

		$bn_var_size["unixdate"] = "INT";
		$bn_var_size["mod_date"] = "INT";
		$bn_var_size["att_size"] = "INT";
		$bn_var_size["hits"] = "INT";
		$bn_var_size["cle"] = "INT";
		$bn_var_size["parent"] = "INT";
		$bn_var_size["childs"] = "INT";
		$bn_var_size["thread"] = "INT";
		$bn_var_size["newest"] = "INT";

		if (strtoupper($bn_var_size[$sort_col]) == 'INT') {
			$value = (int)$note[$sort_col];
		} else {
			$value = "'" . $this->preserveQuotes($note[$sort_col]). "'";
		}

		$whereclause = "hidden=0 AND parent=0";
		if (!empty($where)) {
			$whereclause .= $where;
		}

		$this->query ("SELECT thread, $sort_col FROM $forum WHERE $whereclause AND $sort_col > $value ORDER BY $sort_col ASC LIMIT 1");
		if ($this->next_record() ) {
			$tnext = $this->Record["thread"];
		}

		$this->query ("SELECT thread, $sort_col FROM $forum WHERE $whereclause AND $sort_col < $value ORDER BY $sort_col DESC LIMIT 1");
		if ($this->next_record() ) {
			$tprev = $this->Record["thread"];
		}

		$note["tnext"] = ($sort_order == "ASC") ? $tnext : $tprev;
		$note["tprev"] = ($sort_order == "ASC") ? $tprev : $tnext;
	}

	// Get user info from users table
	$uid = $note['userid'];
	if (!empty($uid)) {
		$result = $this->query ('SELECT * FROM '.$this->site.'_users WHERE userid =\''.$this->preserveQuotes($uid)."'");
		if ($this->next_record() ) {
			$this->users[$uid] = $this->Record;
			foreach($this->Record as $var=>$val) {
				$note["user_$var"] = $val;
			}
		}
	}
	return $note;
}

/**
 * Get latest updated thread
 */
function getNewestThread ($forum, $where="") {

	$whereclause = "hidden=0";
	if (!empty($where)) {
		$whereclause .= " AND $where";
	}

	$this->query ("SELECT max(cle) AS latest FROM $forum WHERE $whereclause");
	if (!$this->next_record() ) {
		return -4;
	}

	$cle = $this->Record["latest"];
	$this->query ("SELECT thread FROM $forum WHERE cle=$cle");
	if (!$this->next_record() ) {
		return -4;
	}

	return $this->Record["thread"];
}

/**
 * Get thread contents.
 *
 * Loads the entries[] / children[] arrays with the contents of a given thread
 * Also Loads the users[] array with user infos for all disinct users found in threads
 * @access  public
 * @param   string  $forum      the forum (tablename) in which the thread is located
 * @param   integer $key		Note's id (either key or thread must be set)
 * @param   integer $thread		thread Id (either key or thread must be set)
 * @param   integer $order		Notes order - 'a' : ascending, 'd' : descending
 * @param   integer $display	display mode (How notes are retreived) - 't' : thread, 'f' : flat
 * @param   integer $showhidden	whether to display hidden notes or not
 * @return	integer the number of notes in the thread
 */
function getThread ($forum, $key=0, $thread=0, $order='a', $display='t', $showhidden=false) {

	unset ($this->entries);
	unset ($this->children);

	if ( empty($key) && empty($thread) ) {
		$this->halt("getThread(): invalid argument, either key or parent must be set");	
	}

	if(empty($thread)) {
		$result = $this->query ("SELECT thread FROM $forum WHERE cle=$key");
		if (!$this->next_record() ) {
			return (-3);
		}
		$thread = $this->Record['thread'];
	}

	$whereclause = "thread=$thread";

	if (! $showhidden) {
		$whereclause .= " AND hidden=0";
	}

	reset ($GLOBALS["list_var"]);
	$cols = "cle,parent,childs,thread,newest";
	while (list($var,$val) = each($GLOBALS["list_var"])) {
		if ($val==1) $cols.=",$var";
	}

	$order_by = ($order == 'd') ? 'unixdate DESC' : 'unixdate';
	$result = $this->query ("SELECT $cols FROM $forum WHERE $whereclause ORDER BY $order_by");
	if (!$result) {
		return (-3);
	}
	$count = 0;
	$set = '';
	while ($this->next_record() ) {
		$key = (int) $this->f("cle");
		$parent = (int) $this->f("parent");
		$this->entries[$key] = $this->Record;
		if ($display=='f') {
			$this->children[0][] = $key;
		} else {
			// default to threaded mode
			$this->children[$parent][] = $key;
		}
		$count++;

		if ( isset($this->Record['userid'])  )  {
			$uid = $this->Record['userid'];
			if (!isset($this->users[$uid])) {
				$this->users[$uid] = array();
				$set .= "'" .$this->preserveQuotes($uid)."',";
			}
		}
	}

	if (!empty($set)) {
		$result = $this->query ('SELECT * FROM '.$this->site.'_users WHERE userid in ('.ereg_replace (',$', '', $set).')');
		while ($this->next_record() ) {
			$uid = $this->Record['userid'];
			$this->users[$uid] = $this->Record;
		}
	}

	return $count;
}

/**
 * Deprecated : uses ListRenderer class
 */
function displayList ($key, $action, $start=0, $count=0) {

	global $ext,$inc_dir;

	require_once "$inc_dir/listrenderer.$ext";
	$list =& new ListRenderer($this, $start, $count);
	$list->displayList($key, $action);
	unset($list);
}

function listThreads ($forum, $first=0, $last=0, $where="", $expanded=1, $limit=0, $showhidden=false) {
	global $bn_var_size, $list_var, $threads_order, $default_threads_order;

	settype ($first, "integer");
	settype ($last, "integer");
	settype ($limit, "integer");

	unset ($this->entries);
	unset ($this->children);

// FIX ME !! order by replies is not yet supported
// -----------------------------------------------
	if (empty($threads_order) || ereg ("^replies *", $threads_order) ) {
		$threads_order = (empty($default_threads_order)) ? "newest DESC" : $default_threads_order;
	} else {
		// check that the fields exists in the table
		$bn_var_size["unixdate"] = "INT";
		$bn_var_size["mod_date"] = "INT";
		$bn_var_size["att_size"] = "INT";
		$bn_var_size["hits"] = "INT";
		$bn_var_size["cle"] = "INT";
		$bn_var_size["parent"] = "INT";
		$bn_var_size["childs"] = "INT";
		$bn_var_size["thread"] = "INT";
		$bn_var_size["newest"] = "INT";
		list($var) = split(' ', $threads_order);
		if (!isset($bn_var_size[$var]) ) {
			$threads_order = (empty($default_threads_order)) ? "newest DESC" : $default_threads_order;
		}
	}

#   1) gets total count of threads and notes that are satisfying the query
#   ----------------------------------------------------------------------
	$whereClause = '';
	if (!$showhidden) {
		$whereClause .= 'WHERE hidden=0';
	}

	if (!empty($where)) {
		$whereClause = ( empty($whereClause) ) ? "WHERE $where" : "$whereClause AND $where";
	}

	$result = $this->query("SELECT count(cle) as total FROM $forum $whereClause");
	if (!$this->next_record() ) {
		return ERR_NOTFOUND;
	}
	$ret["total"] = $this->Record['total'];
	if ($ret["total"] == 0) {
		return ($ret);
	}

	$getTopicsWhereClause = ( empty($whereClause) ) ? "WHERE parent=0" : "$whereClause AND parent=0";
	$result = $this->query("SELECT count(thread) as totalthreads FROM $forum $getTopicsWhereClause");
	if (!$this->next_record() ) {
		return ERR_NOTFOUND;
	}
	$ret["totalthreads"] = $this->Record['totalthreads'];

#   2) Get all threads Ids, according to the sort order and to first and last thread
#   to be displayed and builds the query that will return the full threads contents
#   --------------------------------------------------------------------------------
	if (empty($first)) {
		if (empty($last)) {
			$limitClause = (empty($limit)) ? "" : "LIMIT $limit";
		} else {
			$first = ($last>$limit) ? $last-$limit+1 : 0;
			$row_count = ($last>$limit) ? $limit : $last+1;
			$limitClause = "LIMIT $first, $row_count";
		}
	} else {
		if ($last>$first) {
			$limit = $last-$first+1;
		} elseif (empty ($limit)) {
			$limit = '-1';
		}
		$limitClause = "LIMIT $first, $limit";
	}

	$result = $this->query ("SELECT thread,newest FROM $forum $getTopicsWhereClause ORDER BY $threads_order $limitClause");

	$tcount = 0;
	while ($this->next_record() ) {
		$t = $this->Record['thread'];
		if (!empty($t)) {
			$threadslist .= "$t,";
			$tcount++;
		}
	}
	$threadslist = ereg_replace (',$', '', $threadslist);

	$ret["threads"] = $tcount;  // number of threads displayed
	if ($tcount < 1) {
		$ret["next"] = 0;
		$ret["prev"] = 0;
		return $ret;
	}

	$ret["first"]   = $first;
	$ret["last"]    = $first + $tcount - 1;

	$getCountWhereClause = ( empty($whereClause) ) ? "WHERE thread IN ($threadslist)" : "$whereClause AND thread IN ($threadslist)";

	$result = $this->query("SELECT thread, count(thread) AS replies FROM $forum $getCountWhereClause GROUP BY thread");

	$ncount=0;
	while ($this->next_record() ) {
		$t = $this->Record['thread'];
		$replies[$t] = $this->Record['replies'] - 1;
		$ncount += $this->Record['replies'];
	}

	$ret["notes"]   = $ncount;      // number of notes displayed

#   3) gets all threads
#   -------------------
	if ($expanded != 1) {
		$cols = "cle,parent,childs,thread,newest";
		foreach($list_var as $var=>$listed) {
			if ($listed) $cols.=",$var";
		}
		$whereClause = "$getTopicsWhereClause AND thread IN ($threadslist)";
		$query = "SELECT $cols FROM $forum $whereClause ORDER BY $threads_order";
	} else {
		$cols = "R.cle,R.parent,R.childs,R.thread,R.newest";
		foreach($list_var as $var=>$listed) {
			if ($listed) $cols.=",R.$var";
		}
		$whereClause = "WHERE T.parent=0 AND T.thread=R.thread AND T.thread IN ($threadslist)";
		if (!$showhidden) {
			$whereClause .= " AND R.hidden=0";
		}

		$query = "SELECT $cols FROM $forum T, $forum R $whereClause ORDER BY T.$threads_order,R.unixdate ASC";
	}

	$result = $this->query($query);

	while ($this->next_record() ) {
		$key = (int) $this->Record["cle"];
		$parent = (int) $this->Record["parent"];
		if ($parent==0) {
			$this->Record["replies"] = $replies[$key];
		}
		$this->entries[$key] = $this->Record;
		$this->children[$parent][] = $key;
	}

#   4) gets next/previous count
#   ---------------------------
	$ret["next"] = ($ret["totalthreads"] > ($ret['last']+1) ) ? 1 : 0;
	$ret["prev"] = ($first > 0) ? 1 : 0;
	return $ret;
}

function listNotes ($forum, $first=0, $last=0, $sort="", $where="", $limit) {
	 echo "function <b>listNotes()</b> not implemented<br>";
}

function insertNote ($forum, $key, &$fields) {

# check if note already exists
# ----------------------------
	$result = $this->query ("SELECT cle FROM $forum WHERE cle=$key");
	if (!$result) {
		return -3;
	}

	if ($this->next_record() ) {
		return ERR_EXISTS;
	}

#   Insert note
#   -----------
	$fields["parent"] = 0;
	$fields["thread"] = $key;
	$fields["childs"] = 0;
	if (empty($fields["unixdate"])) {
		$fields["unixdate"] = time();
	}
	if (empty($fields["mod_date"])) {
		$fields["mod_date"] = $fields["unixdate"];
	}
	$fields["newest"] = $fields["unixdate"];
	$fields["cle"] = $key;
	$rc = $this->insertRow($forum, $fields);

#   Update forum statistics
# -----------------------
	if ($rc == 0) {
		$this->updateForumStats ($this->site, $forum);
		return $key;
	} else {
		return $rc;
	}
}

function threadNote ($forum, $key, $parent_key, &$fields) {

#   Check if parent exists and get 'thread id'
#   ------------------------------------------
	$result = $this->query ("SELECT thread FROM $forum WHERE cle=$parent_key");
	if (!$this->next_record() ) {
		return -4;
	}

	$thread = $this->f("thread");

# check if note already exists
# ----------------------------
	$result = $this->query ("SELECT cle FROM $forum WHERE cle=$key");
	if ($this->next_record() ) {
		return ERR_EXISTS;
	}

#   Insert note
#   -----------
	$fields["parent"] = $parent_key;
	$fields["thread"] = $thread;
	$fields["childs"] = 0;

	if (empty($fields["unixdate"])) {
		$fields["unixdate"] = time();
	}

	if (empty($fields["mod_date"])) {
		$fields["mod_date"] = $fields["unixdate"];
	}

	if (empty($fields["hidden"])) {
		$fields["newest"] = $fields["unixdate"];
	}
	$fields["cle"] = $key;

	$rc = $this->insertRow ($forum, $fields);
	if ($rc != 0) {
		return $rc;
	}

#   Update parent note
#   ------------------
	$result = $this->query ("UPDATE $forum SET childs=childs+1 WHERE cle=$parent_key");
	if (!$result) {
		return -3;
	}

#   Update "newest" field in all notes in this thread
#   -------------------------------------------------
	if (empty($fields["hidden"])) {
		$this->updateNewest ($forum, $thread);
	}

#   Update forum statistics
#   -----------------------
	$this->updateForumStats ($this->site, $forum);
	return $key;
}

function updateNote ($forum, $key, &$fields) {

# get the current thread id and last in thread
# --------------------------------------------
	$result =  $this->query ("SELECT thread, newest FROM $forum WHERE cle=$key");
	if (!$this->next_record() ) {
		return -4; /* no row selected */
	}

	$thread = (int) $this->f("thread");
	$newest = (int) $this->f("newest");

	if (empty($fields["mod_date"])) {
		$fields["mod_date"] = $GLOBALS["now"];
	}

# Update note in database
# -----------------------
	$result = $this->updateTable ($forum, $fields, "cle=$key");
	if ($result != 0) {
		return $result;
	}

# Update newest in thread if timestamp updated
# --------------------------------------------
	if ($fields["unixdate"] > $newest) {
		$this->updateNewest ($forum, $thread, $fields["unixdate"]);
	}

	return 0;
}

/**
 * Update recursively the 'thread' in all descendants of a note
 *
 * @access  private
 * @param   string  $forum      the forum (tablename) in which the thread is located
 * @param   integer $parent     note id for which all descendants will be updated
 * @param   integer $newthread  the new 'thread' value
 */
function updateThread ($forum, $parent, $newthread) {

# Update thread value for all immediate childs of 'parent'
# --------------------------------------------------------
	$result = $this->query ("UPDATE $forum SET thread=$newthread WHERE parent=$parent");

# Gets all childrens and sub-childs count
# ---------------------------------------
	$result = $this->query ("SELECT cle, childs FROM $forum WHERE parent=$parent");
	while ($this->next_record() ) {
		$cle = (int) $this->f("cle");
		$childkeys[$cle] = (int) $this->f("childs");
	}

# if immediate children have children => update the 'thread' column recursively
# -----------------------------------------------------------------------------
	if (is_array($childkeys) ) {
		reset ($childkeys);
		while ( list ($key, $childs) = each ($childkeys) ) {
			if ($childs > 0) {
				$this->updateThread ($forum, $key, $newthread);
			}
		}
	}
}

/**
 * Update the 'newest' column for all notes in a thread
 *
 * @access  private
 * @param   string  $forum      the forum (tablename) in which the thread is located
 * @param   integer $thread     The thread to be updated
 * @param   integer $newest     the new 'newest' value, if empty then gets the newest from the thread
 */
function updateNewest ($forum, $thread, $newest="") {
	if ($newest=="") {
		$this->query ("SELECT MAX(unixdate) AS newest FROM $forum WHERE thread=$thread AND hidden=0");
		if ($this->next_record() ) {
			$newest = $this->f("newest");
		}
	}
	if (!empty($newest) ) {
		$this->query ("UPDATE $forum SET newest=$newest WHERE thread=$thread");
	}
}

function deleteNote ($forum, $key, $bn_dir_notes) {

	$site = $this->site;

	$result =  $this->query ("SELECT filename, parent, childs, thread FROM $forum WHERE cle=$key");
	if (!$result) { /* access problem */
		return -3;
	}

	if (!$this->next_record() ) {
		return -4; /* no row selected */
	}

	$filename = $this->f("filename");
	$parent = (int) $this->f("parent");
	$childs = (int) $this->f("childs");
	$thread = (int) $this->f("thread");

	// delete note
	$this->query("DELETE FROM $forum WHERE cle=$key");

	if ($parent > 0) {
		// decrement childs count in parent
		$this->query ("UPDATE $forum SET childs=childs-1 WHERE cle=$parent");
		$this->updateNewest ($forum, $thread);
	} else {
		// This note was at root thread level => all children become new threads
		$this->query ("SELECT cle FROM $forum WHERE parent=$key");

		while ($this->next_record() ) {
			$childkeys[] = (int) $this->f("cle");
		}

		if (is_array($childkeys) ) {
			for (reset ($childkeys); $child=current($childkeys); next($childkeys)) {
				// Update thread value for all immediate childs of 'parent'
				$this->query ("UPDATE $forum SET thread=$child WHERE cle=$child");
				$this->updateThread ($forum, $child, $child);
				$this->updateNewest ($forum, $child);
			}
		}
		// remove all subscribed users to this thread
		$this->unSubscribeUser ($this->site, $forum, "", $key);
	}

# Link children to parent
# -----------------------
	if ($childs > 0) {
		$this->query ("UPDATE $forum SET parent=$parent WHERE parent=$key");
	}

# remove HTML file (static mode)
# ------------------------------
	if (!empty($filename) && ($filename != "none") && is_file("$bn_dir_notes/$filename")) {
		@unlink ("$bn_dir_notes/$filename");
	}

# Remove attachment(s)
# --------------------
	$this->deleteAttachment ($forum, $key, $bn_dir_notes);

# Update statistics
# -----------------
	$this->updateForumStats ($this->site, $forum);

	return 0;
}

function getReplies ($forum, $parent) {
	$result =  $this->query ("SELECT * FROM $forum WHERE parent=$parent");
	while ($this->next_record() ) {
		$key = $this-> f("cle");
		$this->all_replies[$key] = $this->Record;
		$this->getReplies ($forum, $key);
	}
}

function copyNote ($forum1, $key1, $bn_dir_notes1, $forum2, $key2, $bn_dir_notes2) {

# Get note with $key1 from $forum1 and all its replies
# ---------------------------
	$result =  $this->query ("SELECT * FROM $forum1 WHERE cle=$key1");
	if ($this->next_record() ) {
		unset($this->all_replies);
		$this->all_replies[$key1] = $this->Record;
		$this->getReplies ($forum1, $key1);
	}

# Copy and attach each note to new note
# ------------------------
	reset ($this->all_replies);
	while ( list($oldkey, $note) = each ($this->all_replies) ) {
		$parent = $note['parent'];
		$trykey = $oldkey;
		if (!isset($newkeys[$parent])) $newkeys[$parent]=$key2;

		if ($newkeys[$parent]==0) {
			do {
				$newkey = $this->insertNote ($forum2, $trykey, $note);
				$trykey++;
			} while ($newkey==ERR_EXISTS);
		} else {
			do {
				$newkey = $this->threadNote ($forum2, $trykey, $newkeys[$parent], $note);
				$trykey++;
			} while ($newkey==ERR_EXISTS);
		}

		if ($newkey>0) {
			$newkeys[$oldkey] = $newkey;
		} else {
			return $newkey;
		}

		// copy all attachments
		$att = $this->getAttachments ($forum1, $oldkey);
		if (is_array($att)) {
			$att_count = count($att);
			for ($i=0; $i<$att_count; $i++) {
				$oldid  = $att[$i]["att_id"];
				$name   = $att[$i]["att_name"];
				$type   = $att[$i]["att_type"];
				$inline =  $att[$i]["inline"];
				$state  =  $att[$i]["state"];
				$hidden =  $att[$i]["hidden"];
				$size   = (integer) $att[$i]["att_size"];
				$id=$this->insertAttachment ($forum2, $newkey, $name, $bn_dir_notes2, $inline, $size, $type, $state, $hidden);
				if ($id>0) {
					copy ("$bn_dir_notes1/$oldid.$name", "$bn_dir_notes2/$id.$name");
				} else {
					$this->halt (str_replace("{NAME}",$name, str_replace("{FORUM}",$forum2, str_replace("{CLE}", $newkey, ERROR_INSERT_ATTTACHMENT))));
					return $id;
				}
			}
		}

	}

# Update forum statistics
# -----------------------
	$this->updateForumStats ($this->site, $forum2);

	return 0;
}

function mDeleteNotes ($forum, $notes, $bn_dir_notes) {

	for (reset ($notes); $note=current($notes); next($notes)) {
		$ret = $this->deleteNote($forum, $note, $bn_dir_notes);
		if ($ret < 0)
			return $ret;
		else
			$i++;
	}
	return 0;
}

function mDeleteThreads ($forum, $notes, $bn_dir_notes) {

	for (reset ($notes); $note=current($notes); next($notes)) {
		$ret = $this->deleteThread($forum, $note, $bn_dir_notes);
		if ($ret < 0) {
			return $ret;
		}
	}
	return 0;
}

function deleteThread ($forum, $thread, $bn_dir_notes) {

# Remove static HTML files + get all keys
# ---------------------------------------
	$result =  $this->query ("SELECT cle, filename FROM $forum WHERE thread=$thread");
	while ($this->next_record() ) {
		$filename = $this-> f("filename");
		$keys[] = $this-> f("cle");

		// remove HTML file (static mode)
		if (!empty($filename) && ($filename != "none") && is_file("$bn_dir_notes/$filename")) {
			@unlink ("$bn_dir_notes/$filename");
		}
	}

# remove attachments
# ------------------
	reset ($keys);
	while ( list(,$key) = each ($keys) ) {
		$this->deleteAttachment ($forum, $key, $bn_dir_notes);
	}

# delete thread
# -------------
	$this->query("DELETE FROM $forum WHERE thread=$thread");

# remove all subscribed users to this thread
# ------------------------------------------
	$this->unSubscribeUser ($this->site, $forum, "", $thread);

# Update forum statistics
# -----------------------
	$this->updateForumStats ($this->site, $forum);

	return 0;
}

function copyThread ($forum1, $thread1, $bn_dir_notes1, $forum2, $thread2=0, $bn_dir_notes2) {

# Get all notes in old thread
# ---------------------------
	$result =  $this->query ("SELECT * FROM $forum1 WHERE thread=$thread1 ORDER BY unixdate");
	while ($this->next_record() ) {
		$key = $this-> f("cle");
		$old_thread[$key] = $this->Record;
	}

# Copy each notes to new thread
# ------------------------
	reset ($old_thread);
	while ( list($oldkey, $note) = each ($old_thread) ) {
		$parent = $note['parent'];
		$trykey = $oldkey;
		if ($parent==0) {
			do {
				$newkey = $this->insertNote ($forum2, $trykey, $note);
				$trykey++;
			} while ($newkey==ERR_EXISTS);
		} elseif (isset($newkeys[$parent]) ) {
			do {
				$newkey = $this->threadNote ($forum2, $trykey, $newkeys[$parent], $note);
				$trykey++;
			} while ($newkey==ERR_EXISTS);
		} else {
			// should not occur
			$this->halt (ERROR_PARENT_NOT_FOUND);
			return -1;
		}

		if ($newkey>0) {
			$newkeys[$oldkey] = $newkey;
		} else {
			return $newkey;
		}

		// copy all attachments
		$att = $this->getAttachments ($forum1, $oldkey);
		if (is_array($att)) {
			$att_count = count($att);
			for ($i=0; $i<$att_count; $i++) {
				$oldid  = $att[$i]["att_id"];
				$name   = $att[$i]["att_name"];
				$type   = $att[$i]["att_type"];
				$inline =  $att[$i]["inline"];
				$state  =  $att[$i]["state"];
				$hidden =  $att[$i]["hidden"];
				$size   = (integer) $att[$i]["att_size"];
				$id=$this->insertAttachment ($forum2, $newkey, $name, $bn_dir_notes2, $inline, $size, $type, $state, $hidden);
				if ($id>0) {
					copy ("$bn_dir_notes1/$oldid.$name", "$bn_dir_notes2/$id.$name");
				} else {
					$this->halt (str_replace("{NAME}",$name, str_replace("{FORUM}",$forum2, str_replace("{CLE}", $newkey, ERROR_INSERT_ATTTACHMENT))));
					return $id;
				}
			}
		}

	}

# Update forum statistics
# -----------------------
	$this->updateForumStats ($this->site, $forum2);

	return 0;
}


/**
 *  Get all threads (threads_id) opened or modified betwwen 2 given dates
 *
 *  @param  string  $forum      Table name which contains the note to be deleted
 *  @param  integer $from       first date (unix timestamp format)
 *  @param  integer $to         last date (unix timestamp format)
 *  @param  string  $type       "lastdate" = last post date in thread or "startdate" = start date of threads
 *  @return array   $threads    An array with values set to the thread id
 */

function getThreadsDate ($forum, $from, $to, $type="lastdate") {

	$col = ($type=="lastdate") ? "newest" : "thread";

	$result = $this->query ("SELECT DISTINCT thread FROM $forum WHERE $col>=$from AND $col <= $to");
	if (!$result) {
		return 0;
	}

	while ($this->next_record() ) {
		$threads[] = $this-> f("thread");
	}
	return $threads;
}

/**
 *  delete entire threads by given date
 *
 *  @param  string  $forum          Table name which contains the note to be deleted
 *  @param  integer $from           first date (unix timestamp format)
 *  @param  integer $to             last date (unix timestamp format)
 *  @param  string  $bn_dir_notes   directory in which the static HTML files and attachments are stored
 *  @param  string  $type           "lastdate" = last post date in thread or "startdate" = start date of threads
 */
function mDeleteThreadsDate ($forum, $from, $to, $bn_dir_notes, $type="lastdate") {

	$col = ($type=="lastdate") ? "newest" : "thread";

# delete static HTML files and attachments and unsubscribe users
# --------------------------------------------------------------
	$result = $this->query ("SELECT cle, thread, filename FROM $forum WHERE $col>=$from AND $col <= $to");
	if (!$result) {
		return 0;
	}

	while ($this->next_record() ) {
		$cle = $this-> f("cle");
		$thread = $this-> f("thread");
		$filename = $this-> f("filename");
		$keys[] = $this-> f("cle");

		// remove HTML file (static mode)
		if (!empty($filename) && ($filename != "none") && is_file("$bn_dir_notes/$filename")) {
			@unlink ("$bn_dir_notes/$filename");
		}

		// remove all subscribed users to this thread
		if ($cle == $thread) {  // if this is the first message in the thread
			$this->unSubscribeUser ($this->site, $forum, "", $thread);
		}

	}

# Remove attachments
# ------------------
	if (is_array($keys)) {
		reset ($keys);
		while ( list(,$key) = each ($keys) ) {
			$this->deleteAttachment ($forum, $key, $bn_dir_notes);
		}
	}

# delete threads
# --------------
	$this->query("DELETE FROM $forum WHERE $col >= $from AND $col <= $to");

# Update forum statistics
# -----------------------
	$this->updateForumStats ($this->site, $forum);
}

function mDeleteDate ($forum, $from, $to, $bn_dir_notes) {

# Gets all individual notes in the range [$from - $to]
# ----------------------------------------------------
	$result = $this->query ("SELECT cle FROM $forum WHERE cle>=$from AND cle <= $to");
	if (!$result) {
		return 0;
	}

# stores all keys in an array (cannot perform embedded SQL queries w/ PHPLIB)
# ---------------------------------------------------------------------------
	while ($this->next_record() ) {
		$notes[] = $this-> f("cle");
	}

	return $this->mDeleteNotes ($forum, $notes, $bn_dir_notes);
}

function setClosedThread ($forum, $thread, $closed=1) {

	settype ($closed, "integer");

	// check that thread exists
	$result =  $this->query ("SELECT cle FROM $forum WHERE cle=$thread");

	if (!$result) {
		return -3;  /* access problem */
	}

	if (!$this->next_record() ) {
		return -4;  /* no row selected */
	}

	// (un)close Thread
	$this->query("UPDATE $forum SET closed=$closed WHERE thread=$thread");
}

/**
 * Build the where clause used in the search function
 *
 * @param   string  $pattern        The pattern to search
 * @param   array   $search_fields  An array that contain the fields name to be searched
 * @param   integer $search_type    search mode : 0= all words, 1=any word, 2=exact phrase
 * @param   integer $casesensitive  Case sensitive search (1 = yes, default: 0 = no)
 * @param   string  $where          An optional where clause
 *
 * @access  public
 * @return  string  the where clause
 */
function buildSearchWhereClause ($pattern, &$search_fields, $search_type, $casesensitive=0, $where="") {

 # build the search terms arrays
 # -----------------------------
	if ($search_type == 2) {
		// search exact phrase
		$terms[]=$pattern;
	} else {
		$terms = split_search_terms ($pattern);
	}

 # build the query
 # ---------------
	$whereclause = "hidden=0";

	// for each term to search...
	if (is_array($terms) ) {
		reset ($terms);
		while (list (, $term) = each ($terms)) {
			if (!empty($term) ) {
				$term = $this->preserveQuotes($term);
				if ( (substr($term, 0, 1) == "-") && ($search_type!=2) ) {
					$term =substr($term, 1);
					$like = "NOT LIKE";
				} else {
					$like = "LIKE";
				}
				// ... select the columns to look in
				if ($casesensitive) {
					$searchterms[] = '( ' . implode ($search_fields, " $like '%$term%' OR ") . " $like '%$term%' )";
				} else {
					$searchterms[] = '( upper(' . implode ($search_fields, ") $like upper('%$term%') OR upper(") . ") $like upper('%$term%') )";
				}
			}
		}
	}

	// now build the complete query, merging all partial term search clauses
	if (is_array ($searchterms) ) {
		$cmp = ($search_type==0) ? "AND" : "OR";
		$whereclause .= " AND (" . implode (" $cmp ", $searchterms) . ") ";
	}

	if (!empty($where) ) {
		$whereclause .= " AND ($where)";
	}
	return $whereclause;

} // end func

/**
 * Returns a user menu from the menu table (RFU)
 *
 * @param   $site   site name
 * @param   $name   Menu name
 * @return  array $menu     associative array of (key, value) pairs
 */
 function getMenu ($site, $name) {

	if (!$this->query ("SELECT menu_key, menu_value FROM ".$site."_menus WHERE menu_name='$name' ORDER BY menu_value ASC") ) {
		return ERR_ACCESS;
	}

	$menu = array();
	while ($this->next_record() ) {
		$k = (empty($this->Record['menu_key'])) ? $this->Record['menu_value'] : $this->Record['menu_key'];
		$menu[$k] = $this->Record['menu_value'];
	}

	return (count($menu)>0) ? $menu : ERR_NOTFOUND;
 }


} // end Class

?>
