<?PHP

/*
 *	W-AGORA 4.0
 *	-----------
 *	$Id: oci8.php,v 1.28 2005/03/11 10:24:04 mdruilhe Exp $
 *	Usage:	include file - Database access functions (Oracle/OCI8)
 *	Authors:	Marc Druilhe <mdruilhe@w-agora.net>
 *				Pries, John E. <JPries@wcom.net>
 */

if (!defined('_GLOBALS')) {
	die('Hacking attempt');
}

// if (!defined("_DBACCESS")) {
if (!isset($DBACCESS)) {
	include ("$inc_dir/dbaccess.$ext");
}

class oci8_access extends DBaccess {

/* private: link and query handles */
var $dblink = 0;

var $statement = 0;

var $autocommit = 1;

/**
 * Constructor.
 * @return	void
 */
function oci8_access () {
	$this->dbtype = "OCI8";
} // end func


#	------------------------------------------------------------------
#		General Database access implementation based on PHPLIB
#		copyright: http://phplib.netuse.de
#		Oracle/OCI8 accessor based on db_oci8.inc
#	------------------------------------------------------------------

/* public: perform a query */
function query($Query_String, $Bind_Names="") {

	if ($this->statement) {
		@OCIFreeStatement($this->statement);
		$this->statement = 0;
	}

	// remove the 'LIMIT' clause from the query
	$pos = strpos($Query_String, " LIMIT ");
	if ($pos) $Query_String = substr($Query_String, 0, $pos);
	unset($pos);

	$ti = explode(" ", microtime());
	$start = (float)$ti[0]+(float)$ti[1];

	$this->statement = OCIParse($this->dblink, $Query_String);

	// Bind all PHP variables from the associative array $Bind_Names
	if (is_array($Bind_Names) ) {
		//jep need to do binding this way according to php.net help doco, bind gets confused
		//jep when trying to bind an associative array for some reason, you have to create a
		//jep new array, then bind
		$value = array();
		$i = 0;
		//jep end
		reset($Bind_Names);
		while (list($col, $val) = each($Bind_Names)) {
//jep
			$value[$i] = $val.""; //don't forget to append the null at the end of string
//			OCIBindByName($this->statement, ":$col", &$val, -1); 
//			I added back in the strlen to make sure I get length of array index
			OCIBindByName($this->statement, ":$col", &$value[$i], strlen($val)+1); 
			$i++;
//jep end
		}
	}

	if ($this->statement) {
		OCIExecute($this->statement, ($this->autocommit==1) ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT);
	}

	$error = OCIError($this->statement);
	$this->Row=0;

	$this->query_count++;
	$ti = explode(" ", microtime());
	$stop = (float)$ti[0]+(float)$ti[1];
	$elapsed = $stop - $start;
	$this->sql_time += $elapsed;

	if ($error["code"]!=1403 && $error["code"]!=0 )  {
		$this->Errno = $error["code"];
		$this->Error = $error["message"];
		$this->halt ("Invalid SQL: ".$Query_String);
	}

	if ($this->debug) {
		printf("<tt>Debug: [query %d] %s (exec. time = %.3f ms)</tt><br />\n", $this->query_count, $Query_String, $elapsed*1000);
		flush();
	}

	$this->lastQuery=$Query_String;
	return $this->statement;
}

/* public: walk result set */
function next_record() {

	$ti = explode(" ", microtime());
	$start = (float)$ti[0]+(float)$ti[1];

	if (0 == OCIFetchInto ($this->statement, $result, OCI_ASSOC+OCI_RETURN_NULLS)) {
		if ($this->debug) {
			printf("<br>ID: %d,Rows: %d<br>\n",
			$this->dblink, $this->num_rows());
		}
		$this->Row += 1;

		$error = OCIError($this->statement);
		$stat=0;
		if ($error["code"] == 1403) { # 1043 means no more records found
			$this->Error = "";
		} elseif ($error["code"] != 0)  {
			$this->Errno = $error["code"];
			$this->Error = $error["message"];
			$this->halt ("");
		}
	} else {
		unset ($this->Record);
		for (reset($result); $key = key($result); next($result) ) {
			$col = strtolower ($key);
			$this->Record[$col] = $result[$key];
			if($this->debug) echo "Debug: <b>[$col]</b>: " . $result[$key] . "<br>\n";
		}
		$stat=1;
	}

	$ti = explode(" ", microtime());
	$stop = (float)$ti[0]+(float)$ti[1];
	$this->sql_time += ($stop - $start);

	return $stat;
}

/* public: position in result set */
function seek($pos = 0) {
	$this->Row=$pos;
}

function affected_rows() {
    return OCIrowcount($this->statement);
}

function num_rows() {

	if (eregi("^[[:space:]]*SELECT[[:space:]]",$this->lastQuery) ) {
		$query = $this->lastQuery;

		// Check if the query contains an ORDER BY clause
		if (preg_match("# ORDER BY .*#mi", $query, $match)) {
		    $query = str_replace($match[0], "", $query);	// remove it from the query
		}
		
		$from_pos = strpos(strtoupper($query),"FROM");
		$q = "SELECT count(*) AS CNT ". substr($query, $from_pos);

		if($this->debug) {
			printf("Debug: query = %s<br>\n", $q);
			flush();
		}
		$stmt = OCIParse($this->dblink, $q);
		OCIExecute ($stmt, OCI_DEFAULT);
		if (OCIFetch ($stmt) ) {
			$num = OCIResult ($stmt, "CNT");
		}
	} else {
		$num = OCIrowcount($this->statement);
	}
	if ($this->debug) echo "<BR>Debug: num_rows= $num<BR>";
	return $num;

}

function num_fields() {
	return OCINumcols($this->statement);
}

function nextId ($seqname, $table="") {

	$sequence = $seqname . "_seq";
	$Query_ID = @ociparse($this->dblink, "SELECT $sequence.NEXTVAL FROM DUAL");

	if(!@ociexecute($Query_ID))	{
		$error = @OCIError($Query_ID);
		if ($error["code"]==2289) {
			$Query_ID=ociparse ($this->dblink, "CREATE SEQUENCE $sequence");
			if(!ociexecute($Query_ID)) {
				$error = OCIError($Query_ID); 
				$this->Errno = $error["code"];
				$this->Error = $error["message"];
				$this->halt("<BR> nextid() function - unable to create sequence<br>".$this->Error["message"]);
			} else {
				$Query_ID=ociparse($this->dblink,"SELECT $sequence.NEXTVAL FROM DUAL");
				ociexecute($Query_ID);
			}
		}
	}

	if (ocifetch($Query_ID)) {
		$this->autoID[$seqname] = ociresult ($Query_ID, "NEXTVAL");
	} else {
		$this->autoID[$seqname] = 0;
	}
	ocifreestatement($Query_ID);

	return $this->autoID[$seqname];
}


#	------------------------------------------------------------------
#				End of PHPLIB functions
#	------------------------------------------------------------------

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

function commit () {
	return OCICommit ($this->dblink);
}

function rollback () {
	return OCIRollback ($this->dblink);
}

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

	if (!function_exists('OCILogon') ) {
		$this->Error = sprintf (ERROR_DB_NOT_SUPPORTED, 'OCI8');
		$this->halt("OCILogon()");
		return ERR_ACCESS;
	}

	if ($persistent == 1) {
		$this->dblink = OCIPLogon ($dbuser, $dbpassword, $dbname);
	} else {
		$this->dblink = OCILogon ($dbuser, $dbpassword, $dbname);
	}

	if (empty($this->dblink)) {
		return -3;
	}

	return 0;
}

function _closeDB () {
	return @OCILogoff($this->dblink);
}

function createTable ($table, $field_defs, $pk="") {

	$tmp = $this->Halt_On_Error;
	$this->Halt_On_Error = "report";

# Check if table exists
# ---------------------
	$this->query("SELECT table_name FROM all_tables WHERE table_name=UPPER('$table')");
	if ($this->next_record() ) {
		$this->Halt_On_Error = $tmp;
		return -1;	// table already exist
	}

# Create Table
# ------------
	if (!is_array ($field_defs)) {
		$this->Halt_On_Error = $tmp;
		return -4;
	}

	reset($field_defs);
	while (list($col, $size) = each($field_defs)) {
		if (ereg ("^[0-9+]+$", $size) ) {
			if ($size>4000) {
				$desc .= ", $col LONG";
			} else {
				$desc .= ", $col VARCHAR2($size) DEFAULT '' ";
			}
		} else {
			$type = strtoupper($size);
			switch ($type) {
				case "INT":
					$desc .= ", $col INT DEFAULT 0";
					break;
				case "BLOB":
					$desc .= ", $col BLOB";
					break;
				case "TEXT":
					$desc .= ", $col VARCHAR2(4000) DEFAULT ''";
					break;
				case "AUTO":
					$desc .= ", $col INT";
					break;
				case "TIMESTAMP":
					$desc .= ", $col DATE DEFAULT SYSDATE";
					break;
				default:
					$desc .= ", $col $type";
					break;
			}
		}
	}

	if(!empty($pk)) {
		$pkname = $table. '_pk';
		$desc .= ", CONSTRAINT $pkname PRIMARY KEY ($pk)";
	}

	$desc = ereg_replace ("^, ", "", $desc);
	$ret = $this->query ("CREATE TABLE $table ($desc)");
	if (!$ret) {
		$this->Halt_On_Error = $tmp;
		return -3;
	}

	$this->Halt_On_Error = $tmp;
	return 0;
}

function updateTable ($table, &$fields, $where="") {

	if (!is_array ($fields) ) {
		echo "warning: updateTable(): item to update is not an array";
		return -3;
	}

 #	gets fields name and type from table
 #	-------------------------------------
 	$this->query("SELECT column_name, data_type, data_length FROM ALL_TAB_COLUMNS WHERE table_name=UPPER('$table')");

#	build columns/values list (set all matching column names)
#	---------------------------------------------------------
	$cols = "";
	$values = "";
	$bind_array = array();

 	while ($this->next_record()) {
		$colname = strtolower($this->Record[column_name]);
		$coltype = $this->Record[data_type];
		$collength = $this->Record[data_length];

		if (isset($fields[$colname])) {
			if ($coltype == "NUMBER") {
				$values .= ", $colname=" . intVal($fields[$colname]);
			} else {
				$val = str_replace ("'", "''", ($fields[$colname]));
				if ( ($collength > 0) && (strlen($val) > $collength) ) {
					$val = substr($val, 0, $collength-1);
				} 
				//if the value is greater than 2000 characters, we have to use parameter binding!
				if (strlen($val) > 1999) { //oops, need to do binding
					$bind_array[$colname] = $val; 
					$values .= ", $colname=:$colname";
				} else {
					$values .= ", $colname='$val'";
				}

			
			}
		}
	}

	$values = ereg_replace ("^, ", "", $values);

	if (empty($values) ) {
		echo "warning: updateTable(): no value to update";
		return -3;
	}

	$q = "UPDATE $table SET $values";
	if (!empty($where) ) {
		$q .= " WHERE $where";
	}

	if (count($bind_array)>0) {
		$result = $this->query ($q, $bind_array);
	} else {
		$result = $this->query($q);
	}

	if (!$result) {
		return -3;
	}

	return 0;
}

function preserveQuotes($str) {
	return str_replace ("'", "''", $str);
}

function insertRow ($table, &$fields) {

	if (!is_array ($fields) ) {
		echo "warning: insertRow(): item to insert is not an array";
		return -3;
	}

 #	gets fields name and type from table
 #	------------------------------------
  	$this->query("SELECT column_name, data_type, data_length FROM ALL_TAB_COLUMNS WHERE table_name=UPPER('$table')");

#	build columns/values list (set all matching column names)
#	---------------------------------------------------------
	$cols = "";
	$values = "";
	$bind_array = array();

	while ($this->next_record()) {
		$colname = strtolower($this->Record["column_name"]);
		$coltype = $this->Record["data_type"];
		$collength = $this->Record["data_length"];
		if (isset($fields[$colname])) {
			$cols .= ", $colname";
			if ($coltype == "NUMBER") {
				$values .= ", " . IntVal($fields[$colname]);
			} else {
				$val = str_replace ("'", "''", ($fields[$colname]));
				if ( ($collength > 0) && (strlen($val) > $collength) ) {
					$val = substr($val, 0, $collength-1);
				} 
				//if the value is greater than 2000 characters, we have to use parameter binding!
				if (strlen($val) > 1999) { //oops, need to do binding
					$values .= ", :".$colname;
					$bind_array[$colname] = $val; 
				} else {
					$values .= ", '$val'";
				}
			}
		}
	}

	if (empty($values) ) {
		echo "warning: insertRow(): no value to update";
		return -3;
	}

	$cols = ereg_replace ("^, ", "", $cols);
	$values = ereg_replace ("^, ", "", $values);

	$query = "INSERT INTO $table ($cols) VALUES($values)";

	if (count($bind_array)>0) {
		$result = $this->query ($query, $bind_array);
	} else {
		$result = $this->query($query);
	}

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

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

function listForums ($site, $moder="", $sort="", $hide_inactive=0, $cat='') {
	global $auth;
	/* CCU A revoir comme dans le dbaccess a priori on devrait avoir
	if ($site=="agora") {
		$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)) {
		$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 ,${site}_userforum UF WHERE S.bn_name = UF.bn_name (+)  AND UF.userid (+) = '$u'  AND 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 wich $moder is moderator
		$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 = '" . $this->preserveQuotes($moder)."' AND UF.modpriv=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 {
		$a=explode (",",$sort);
		$order = '';
		while ( list(,$s)=each ($a) ) {
			$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->f("cle");
		$this->entries[$key] = $this->Record;
		$this->children[0][] = $key;

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

	return $forums;
}

*/
	if ($site=="agora") {
		$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)) {
		$query = "SELECT S.*, C.rank AS catorder, U.username as ownername, U.useraddress as owneraddress FROM $site S, $site C, ${site}_users U WHERE S.parent=C.cle AND S.category!=1 AND S.owner=U.userid(+)";
	} else {
# gets all forums for wich $moder is moderator
		$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 S.parent=C.cle AND S.category!=1 AND S.bn_name=UF.bn_name AND UF.userid = '" . $this->preserveQuotes($moder)."' AND UF.modpriv=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 {
	    $a=explode (",",$sort);
		while ( list(,$s)=each ($a) ) {
		    $order .= ", S.".trim($s);
		}
	}
	$query .= " ORDER BY catorder, S.rank". $order;
	$result = $this->query ($query);
	reset ($this->entries);
	while ($this->next_record() ) {
		$name=ereg_replace ("^${site}_", "", $this->Record["bn_name"]);
		$forums[$name] = $this->Record;
	}

	if (!is_array($forums)) {
	    return false;
	}

# Hide private forums for unauthorized users
	$showpriv = false;
	if ($site=="agora") {
		$showpriv =true;
	} elseif (empty($auth->userid) ) {
		$list_access =array();
	} elseif ($auth->level < ADMIN) {
		$u = $this->preserveQuotes($auth->userid);
		$r = $this->query ("SELECT bn_name FROM ${site}_userforum WHERE userid='$u' AND listpriv='1'");
		while ($this->next_record() ) {
			$n=$this->Record["bn_name"];
			$list_access[$n]=1;
		}
	} else {
		$showpriv =true;
	}

# Gets all forums that the user can list 
	unset ($this->entries);
	reset ($forums);
	while (list($n,$f) = each($forums) ) {
		$bn = $f["bn_name"];
		if ( ($f["type"]!="priv") || $showpriv || isset($list_access[$bn]) ) {
			$key = $f["cle"];
			$this->entries[$key] = $f;
			$this->children[0][] = $key;
		} else {
			unset ($forums[$n]);
		}
	}
	return $forums;
}

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

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

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

/**
 * returns the latest registered user from the site user database
 *
 * @author	Marc
 * @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) {
	$query = "SELECT userid, username, useraddress FROM ${site}_users WHERE unixdate=(SELECT max(unixdate) from ${site}_users)";
	$result = $this->query ($query);
	if ($this->next_record() ) {
		$u = array ('userid'      => $this->Record["userid"],
			    'username'    => $this->Record["username"],
			    'useraddress' => $this->Record["useraddress"]
			    );
		return $u;
	} else {
		return -3;
	}
}


#	------------------------------------------------------------------
#				Messages (notes) management functions
#	------------------------------------------------------------------

function getNote ($forum, $key, $where="", $sort="") {

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

# get key of previous thread
# --------------------------
	$whereclause = "hidden=0 AND newest > " . $note["newest"];
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}

	$result = $this->query ("SELECT thread AS tprev FROM $forum WHERE newest = (SELECT min(newest) FROM $forum WHERE $whereclause)");
	if ($this->next_record() ) {
		$note["tprev"]= $this->Record["tprev"];
	}

# get key of next thread
# ----------------------
	$whereclause = "hidden=0 AND newest < " . $note["newest"];
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}

	$result = $this->query ("SELECT thread AS tnext FROM $forum WHERE newest = (SELECT max(newest) FROM $forum WHERE $whereclause)");
	if ($this->next_record() ) {
		$note["tnext"]= $this->Record["tnext"];
	}

	return $note;
}

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

	if (empty($forum)) {
		echo "DBaccess error: listThreads() : forum not defined";
		return -3;
	}

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

#	1) gets total count # of notes satisfying the query
#	---------------------------------------------------
	if ($showhidden) {
		$whereClause = (empty($where)) ? '' : "WHERE $where";
	} else {
		$whereClause = (empty($where)) ? "WHERE hidden=0" : "WHERE hidden=0 AND $where";
	}
	$result = $this->query ("SELECT count(*) as total FROM $forum $whereClause");

	if ($this->next_record() ) {
		$ret["total"] = $this->Record["total"];
	}

	if ($ret["total"] == 0) {
		return ($ret);
	}

#	2) Find the first (and last) thread to be displayed in the page
#	and gets number of notes per thread
#	----------------------------------------------------------------
	if (!$last) {
		$order="DESC";
		$last = ($showhidden) ? 0 : 1;
	} else {
		$order="ASC";
	}

	$whereClause = "WHERE newest>=$last"; // $last default to 1: don't display hidden notes
	if ($first>0) {
		$whereClause .= " AND newest<=$first";
	}
	if (!$showhidden) {
		$whereClause .= " AND hidden=0";
	}
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}

# Set the main query to get all thread Ids
# ----------------------------------------
	$query = "SELECT thread, newest, count(*) AS MSGCOUNT FROM $forum $whereClause GROUP BY thread,newest";

# Get # of threads to be returned
# -------------------------------
	$result = $this->query ("SELECT COUNT(*) AS tcount FROM ($query)");
	if ($this->next_record() ) {
		$tcount = $this->Record["tcount"];
	} else {
		return -3;
	}

	if ( ($limit>0) && ($tcount>$limit) ) {
		$tcount = $limit;
	}

# Get all thread number and count/thread
# --------------------------------------
	if ($tcount > 0) {

		// Get thread Ids
		$result = $this->query ("$query ORDER BY newest $order");
		if (!$this->next_record() ) {
			return -3;
		}

		// Get first record and set 'head' 
		$t = $this->Record["thread"];
		$whereClause = "WHERE thread IN ($t,";
		$replies[$t] = $this->Record["msgcount"]-1;
		$ncount = $this->Record["msgcount"];
		$rowcount = 1;

		if ($order=="DESC") {
			$tfirst = $this->Record["newest"];
		} else {
			$tlast = $this->Record["newest"];
		}

		while ($rowcount < $tcount) {
			if (!$this->next_record() ) {
				return -3;
			}
			$t = $this->Record["thread"];
			$whereClause .= "$t,";
			$replies[$t] = $this->Record["msgcount"]-1;
			$ncount += $this->Record["msgcount"];
			$rowcount++;
		}

		// Get Last record and set 'head'
		if ($order=="DESC") {
			$tlast = $this->Record["newest"];
		} else {
			$tfirst = $this->Record["newest"];
		}

		$whereClause = ereg_replace (",$", ")", $whereClause);

	}

	$ret["notes"]=$ncount;		// number of notes displayed
	$ret["threads"]=$tcount;	// number of threads displayed
	$ret["first"]=$tfirst;
	$ret["last"]=$tlast;

	if ($tcount < 1) {
		$ret["next"] = 0;
		$ret["prev"] = 0;
		return $ret;
	}

#	3) gets all threads
#	-------------------
	if (!$showhidden) {
		$whereClause .= " AND hidden=0";
	}
 	if (!empty($where) ) {
		$whereClause .= " AND $where";
	}

	if ($expanded != 1) {
		$whereClause .= " AND parent=0";
	}

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

	$query = "SELECT $cols FROM $forum $whereClause ORDER BY newest DESC,unixdate ASC";
	$this->query ($query);

	reset ($this->entries);
	reset ($this->children);
	if (isset($this->children[0]) ) {
		unset($this->children[0]);
	}

	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
#	---------------------------
	$whereClause = "WHERE newest>$tfirst";
	if (!$showhidden) {
		$whereClause .= " AND hidden=0";
	}
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}
	$result = $this->query("SELECT count(*) AS prev FROM $forum $whereClause");
	if ($this->next_record() ) {
		$ret["prev"]  = $this->Record["prev"];
	}

	$whereClause = "WHERE newest<$tlast";
	if (!$showhidden) {
		$whereClause .= " AND hidden=0";
	}
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}
	$result = $this->query("SELECT count(*) AS next FROM $forum $whereClause");
	if ($this->next_record() ) {
		$ret["next"]  = $this->Record["next"];
	}
	return $ret;
}

function listNotes ($forum, $first=0, $last=0, $sort="", $where="", $limit) {
	global $list_var;

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

#	1) gets total count # of notes satisfying the query
#	---------------------------------------------------
	// skip administrative note (old format)
	// don't skip hidden notes (listed in moderate_notes.php)
	$whereClause = "WHERE cle<>0"; // skip administrative record
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}
	$result = $this->query("SELECT count(*) as total FROM $forum $whereClause");
	if (!$this->next_record()) {
		return -3;
	}

	$ret["total"] = $this->Record["total"];
	if ($ret["total"] == 0) {
		return $ret;
	}

#	2) gets all notes sorted by $sort
#	---------------------------------
	$orderClause = (empty ($sort)) ? "ORDER BY unixdate DESC" : "ORDER BY $sort";

# Set first thread and # of threads to be returned
# ------------------------------------------------
	if ($limit>0) {
		if ($last > 0) {
			$start = $last-$limit;
			$start = ($start<0) ? 0 : $start;
		} else {
			$start = $first;
		}
		$end = $start + $limit+1;
		$limitClause = "rownum > $start AND rownum < $end";
	} else {
		$start=0;
		$limitClause="";
	}

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

	$query = "SELECT $cols FROM $forum $whereClause $orderClause";

	if (!empty($limitClause) ) {
		$query = "SELECT * FROM ($query) WHERE $limitClause";
	}

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

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

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

#	3) set next/previous (1 if TRUE)
#	--------------------------------
	$ret["next"] = ($ret["total"] > $ret["last"]+1) ? 1 : 0;
	$ret["prev"] = ($ret["first"] > 1) ? 1 : 0;

	return $ret;
}

}; // end class

?>
