<?php

/*
 *	W-AGORA 4.0
 *	-----------
 *	$Id: postgres.php,v 1.8 2003/08/05 09:36:57 mdruilhe Exp $
 *	Usage:				include file - Database access functions (PostgreSQL < v6.5)
 *	Author:				Marc Druilhe <mdruilhe@w-agora.com>
 *	Initial file by:	Ron Friedman <friedman@filmmaker.com>
 */

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

if (!defined("_PG_ACCESS")) {

 define("_PG_ACCESS", 1);

 if (!defined("_PG65_ACCESS")) {
	include ("$inc_dir/postgres65.$ext");
 }

class postgres_access extends postgres65_access {

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

function listThreads ($forum, $first=0, $last=0, $where="", $expanded=1, $limit=0, $showhidden=false) {
	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 = @pg_exec ($this->dblink, "SELECT count(*) as total FROM $forum $whereClause");
	if (!$result) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return -3;
	}

	$ret["total"] = @pg_result ($result, 0, "total");
	if ($ret["total"] == 0) {
	    return $ret;
	}
	pg_FreeResult($result);

#	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 = "newest>=$last"; // $last default to 1
	if ($first>0) {
		$whereClause .= " AND newest<=$first";
	}
	if (!$showhidden) {
		$whereClause .= " AND hidden=0";
	}
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}

# Set # of threads to be returned
# -------------------------------
	if (!empty($limit)) {
		$result = pg_exec ($this->dblink, "set QUERY_LIMIT TO '$limit'");
	}

	$query = "SELECT thread, newest, count(*) FROM $forum WHERE $whereClause GROUP BY newest,thread ORDER BY newest $order";
if ($this->debug) printf("Debug: query = %s<br>\n", $query);
	$result = pg_exec ($this->dblink, "$query");		
	$tcount = pg_numrows ($result);

	if ($tcount > 0) {
		if ($order=="DESC") {
			$tfirst  = @pg_result ($result, 0, "newest");
			$tlast = @pg_result ($result, $tcount-1, "newest");
		} else {
			$tlast  = @pg_result ($result, 0, "newest");
			$tfirst = @pg_result ($result, $tcount-1, "newest");
		}	

		for ($i=0; $i<$tcount; $i++) {
			$row = pg_fetch_row($result, $i);
			$t = $row[0];
			$replies[$t] = $row[2] - 1;
		}
	}
	pg_FreeResult($result);

	$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
#	-------------------
	$whereClause = "newest<=$tfirst AND newest>=$tlast";
	if (!empty($where) ) {
		$whereClause .= " AND $where";
	}

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

	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 WHERE $whereClause ORDER BY newest DESC,unixdate ASC";
if ($this->debug) printf("Debug: query = %s<br>\n", $query);
	$result = @pg_exec ($this->dblink, $query);
	if (!$result) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return (-3);
	}

	reset ($this->entries);
	reset ($this->children);
	if (isset($this->children[0]) ) {
		unset($this->children[0]);
	}
	$ncount=0;
	while ($row = @pg_fetch_array($result, $ncount)) {
		$key = (int) $row["cle"];
		$parent = (int) $row["parent"];
		if ($parent==0) {
			$row["replies"] = $replies[$key];
		}
		$this->entries[$key] = $row;
		$this->children[$parent][] = $key;
		$ncount++;
	}
	$ret["notes"]=$ncount;	// number of notes displayed
	pg_freeresult($result); 

#	4) gets next/previous count
#	---------------------------
	$whereClause = "newest>$tfirst";
	if (!$showhidden) {
		$whereClause .= " AND hidden=0";
	}
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}
	$result = @pg_exec ($this->dblink, "SELECT count(*) AS prev FROM $forum WHERE $whereClause");
	if (!$result) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return (-3);
	}
	$ret["prev"] = @pg_result ($result, 0, "prev");
	pg_freeresult($result); 

	$whereClause = "newest<$tlast";
	if (!$showhidden) {
		$whereClause .= " AND hidden=0";
	}
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}
	$result = @pg_exec ($this->dblink, "SELECT count(*) AS next FROM $forum WHERE $whereClause");
	if (!$result) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return (-3);
	}
	$ret["next"] = @pg_result ($result, 0, "next");

	pg_FreeResult($result);	
	return $ret;
}

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

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

	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 = "cle<>0";
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}
	$result = @pg_exec ($this->dblink, "SELECT count(*) as total FROM $forum WHERE $whereClause");
	if (!$result) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return -3;
	}

	$ret["total"] = @pg_result ($result, 0, "total");
	if ($ret["total"] == 0) {
	    return $ret;
	}
	pg_FreeResult($result);

#	2) gets all notes sorted by $sort
#	---------------------------------
	$whereClause = "WHERE cle>0"; // skip administrative record
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}
	
	$orderClause = (empty ($sort)) ? "ORDER BY unixdate DESC" : "ORDER BY $sort";
	
	$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";
	$result = @pg_exec ($this->dblink, "$query");
	if (!$result) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return (-3);
	}
	$num = @pg_numrows ($result);

# Set first and last thread to be displayed
# -----------------------------------------
	if ($limit>0) {
		if ($last > 0) {
			$start = ($last-$limit)+1;
			$start = ($start<0) ? 0 : $start;
		} else {
			$start = $first;
		}
		$nmaxdisplay = $num - $start;
		$ndisplay = ($limit > $nmaxdisplay) ? $nmaxdisplay : $limit;
		$end = $start+$ndisplay-1;

	} else {
		$start = 0;
		$end = $num-1;
	}
	
	reset ($this->entries);
	reset ($this->children);
	
	for ($i = $start; $i <= $end; $i++) {
		$row = @pg_fetch_array($result, $i);
		$key = $row["cle"];
		$row["parent"]=0;
		$this->entries[$key] = $row;
		$this->children[0][] = $key;
	}
	pg_FreeResult($result);

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

#	3) gets next/previous (1 if TRUE)
#	---------------------------------
	$ret["next"] = ($num > $end) ? 1 : 0;
	$ret["prev"] = ($start > 0) ? 1 : 0;
	
	return $ret;
}

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

	$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 = pg_exec ($this->dblink, "SELECT thread AS tprev FROM $forum WHERE $whereclause ORDER BY newest");
	if (!$result) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return (-3);
	}
	$ret["tprev"] = @pg_result ($result, 0, "tprev");
	pg_FreeResult($result);	

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

	$result = pg_exec ($this->dblink, "SELECT max(newest) AS tnext FROM $forum WHERE $whereclause ORDER BY newest DESC");
	if (!$result) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return (-3);
	}
	$ret["tnext"] = @pg_result ($result, 0, "tnext");
	pg_FreeResult($result);	

	return $note;
}

}; // end class

} // defined PG_ACCESS

?>
