<?php

/*
 *	W-AGORA 4.1
 *	-----------
 *	$Id: adomsaccess.php,v 1.10 2006/07/11 21:14:09 mdruilhe Exp $
 *	Usage:	include file - Database access functions (ADO / Microsoft Access)
 *	Author:	Emanuele Iannone <manu75@libero.it>
 */

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

if (!defined("_ACCESS_ACCESS")) {
 define("_ACCESS_ACCESS", 1);

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

class adomsaccess_access extends DBaccess {

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

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

/* public: perform a query */
function query($Query_String) {

	if ($this->Query_ID) {
		$this->Query_ID->Close;
		$this->Query_ID = 0;
	}

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

	// Converte VARCHAR in CHAR e != in <>
	$Query_String=eregi_replace("varchar\(","char(",$Query_String);
	$Query_String=ereg_replace("!=","<>",$Query_String);

	// ORDER BY non funziona con gli alias, quindi sostituisce il vero nome al posto dell'alias
	if (preg_match ("/order by/i", $Query_String)) {
		if (!preg_match ("/LIMIT\b/i", $Query_String)) {
			eregi("ORDER BY (.+)",$Query_String,$a);
			$ord=explode(",",$a[1]);
			foreach ($ord as $ca) {
				$ca=eregi_replace("DESC","",$ca); $ca=eregi_replace("ASC","",$ca);
				$alias=trim($ca);
				if (eregi("([ |,])([^ ]+) as ".$alias,$Query_String,$a)) {
					$Query_String=eregi_replace("ORDER BY(.*)([ |,])".$alias."([ |,])","ORDER BY \\2".$a[2]."\\3",$Query_String);
				}
			}
		}
		else echo "Attenzione, cìè LIMIT!";
	}

	if ($this->debug) {
		printf("Debug: query = %s<br>\n", $Query_String);
		flush();
	}

	$this->Query_ID = $this->dblink->Execute($Query_String);
	$errc = $this->dblink->Errors;
	if ($errc->Count) {
		$err = $errc->Item($errc->Count-1);
		$this->Errno = $err->NativeError;
		$this->Error = $err->Description;
		$this->halt("Invalid SQL: ".$Query_String);
	}
	$this->Errno = 0;
	$this->Error = "";
	
	$this->Row   = 0;
	return $this->Query_ID;
}

/* public: walk result set */
function next_record() {
	unset($this->Record);
	$rs= $this->Query_ID;
	if (!$rs->EOF) {
		$this->Record = array();
		$num_col = $rs->Fields->Count;

		for ($i=0; $i < $num_col; $i++) {
			$f = $rs->Fields($i);
			$nom = $f->name; $t= $f->Type;
			switch($t) {
				
			case 133:// A date value (yyyymmdd)
				$this->Record[$nom] = (integer)$f->value;
				//$val = $f->value; 
				//$this->Record[$nom] = substr($val,0,4).'-'.substr($val,4,2).'-'.substr($val,6,2);
				break;
			case 135: // adDate
				if (@is_numeric($f->value)) $this->Record[$nom] = (integer)$f->value;
				//if (@is_numeric($f->value)) $this->Record[$nom] = date('Y-m-d',(integer)$f->value);
				break;
			case 1: // null
				$this->Record[$nom] = false;
				break;
			case 6: // currency is not supported properly;
				print '<br><b>'.$f->Name.': currency type not supported by PHP</b><br>';
				$this->Record[$nom] = (float) $f->value;
				break;
			default:
				@$this->Record[$nom] = @$f->value; 
				break;
			}
		} // for
		@$rs->MoveNext(); // @ needed for some versions of PHP!
		$this->Row   += 1;
	}

	$errc = $this->dblink->Errors;
	if ($errc->Count) {
		$err = $errc->Item($errc->Count-1);
		$this->Errno = $err->NativeError;
		$this->Error = $err->Description;
	}

	$stat = is_array($this->Record);
	if (!$stat && $this->Auto_Free) {
		$this->Query_ID->Close;		
		$this->Query_ID = 0;
	}
	
	return $stat;
}

/* public: position in result set */
function seek($pos = 0) {
	if ($this->Row > $pos) $this->halt("seek($pos) failed: result has ".$this->num_rows()." rows");
       else {
       	@$this->Query_ID->Move((integer)$pos - $this->Row-1); //adBookmarkFirst
		$this->Row = $pos;
	}
	return;
}

function num_rows() {
	$result = $this->Query_ID;
	$num_rec=0;
	if ($result->RecordCount) {
		while (!$result->EOF) {
			++$num_rec;
			$result->MoveNext();
		}
	}
	return $num_rec;
}

function num_fields() {
	return $this->Query_ID->Fields->Count;
}

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

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

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

	$ext = strrchr($dbname, ".");
	if(!$ext) {
		$dbname = "$dbname.mdb";
	}
	if(!file_exists($dbname)) {
		$this->halt("The file $dbname does'nt exist");
		return ERR_ACCESS;
	}

	$this->dblink = new COM("ADODB.Connection");
	if (!$this->dblink ) {
		$this->Error = sprintf (ERROR_DB_NOT_SUPPORTED, 'ADODB');
		$this->halt("COM(ADODB.Connection)");
		return ERR_ACCESS;
	}

	$conn_string = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$dbname";
	$ret = $this->dblink->Open($conn_string);
/*
	$errc = $this->dblink->Errors;
	if ($errc->Count) {
		$err = $errc->Item($errc->Count-1);
		$this->Errno = $err->NativeError;
		$this->Error = $err->Description;
		$this->halt("ADO error - could not open database: $dbname");
		return ERR_ACCESS;
	}
*/
	return 0;
}

function _closeDB () {
	if (!empty($this->dblink)) {
		$this->dblink->Close;
	}
	return TRUE;
}

function createTable ($table, $field_defs, $pk="") {
	global $php_errormsg;
	
	$tmp = $this->Halt_On_Error;
	$this->Halt_On_Error = "report";

	// Check if table exists
	// ---------------------
	$arr= array();
	$dbc = $this->dblink;
	
	$adors=@$dbc->OpenSchema(20);//tables
	if ($adors){
		$f = $adors->Fields(2);//table/view name
		$t = $adors->Fields(3);//table type
		while (!$adors->EOF){
			$tt=substr($t->value,0,6);
			if ($tt!='SYSTEM' && $tt !='ACCESS')
				$arr[]=$f->value;
			//print $f->value . ' ' . $t->value.'<br>';
			$adors->MoveNext();
		}
		$adors->Close;
	}
	
	$errc = $this->dblink->Errors;
	/*if ($errc->Count) {
		$err = $errc->Item($errc->Count-1);
		echo "SQL error: ".$err->NativeError.": ".$err->Description."<BR>";
		$this->Halt_On_Error = $tmp;
		return -3;
	}*/
	foreach ($arr as $ta) {
		if ($table == $ta) {
			$this->Halt_On_Error = $tmp;
			return -1;	/*  table already exist */
		}
	}


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

	while (list($col, $size) = each($field_defs)) {
		if (ereg ("^[0-9+]+$", $size) ) {
			if ($size>255) {
				$desc .= ", [$col] MEMO";
			} else {
				$desc .= ", [$col] TEXT($size)";
			}
		} else {
			$type = strtoupper($size);
			switch ($type) {
				case "INT":
					$desc .= ", [$col] integer";
					break;
				case "TIMESTAMP":
					$desc .= ", $col DATE";
					break;
				case "AUTO":
					$desc .= ", [$col] COUNTER";
					break;
				case "BLOB":
				case "TEXT":
					$desc .= ", [$col] MEMO";
					break;
				default:
					$desc .= ", [$col] $type";
					break;
			}
		}
	}

	if(!empty($pk)) {
		$desc .= ", PRIMARY KEY ($pk)";
	}

	$desc = ereg_replace ("^, ", "", $desc);
	$ret = $this->query ("CREATE TABLE $table ($desc)");
	if (!$ret) {
		$this->Halt_On_Error = $tmp;
		return -3;
	}
	
	$this->Errno = 0;
	$this->Error = "";
	$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
	//	------------------------------------
	unset($result); unset($colsize);
	$dbc = $this->dblink;
	$adors=@$dbc->OpenSchema(4);//tables
	if ($adors){
		$t = $adors->Fields('TABLE_NAME'); //classe table name
		while (!$adors->EOF){
			if (strtoupper($t->Value) == strtoupper($table)) {
				$c = $adors->Fields('COLUMN_NAME');
				$s = $adors->Fields('CHARACTER_MAXIMUM_LENGTH');
				$result[]=$c->value;
				@$colsize[]=@$s->Value;
			}
			$adors->MoveNext();
		}
		$adors->Close;
	}
	$nc=count($result);
	if (!$nc) { /* access problem */
		$this->halt("update table() could not list fields from $table: $php_errormsg");
		return -3;
	}

	// build columns/values list (set all matching column names)
	// ---------------------------------------------------------
	for($i=0; $i < $nc; ++$i) {
		$colname=$result[$i];
		if (isset($fields[$colname])) {
			if (!$colsize[$i]) $values .= ", $colname=" . intVal($fields[$colname]);
			else {
				$val = $this->preserveQuotes($fields[$colname]);
				$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";

	$ret = $this->query ($q);
	if (!$ret) {
		$this->Halt_On_Error = $tmp;
		return -3;
	}
	$this->Query_ID->Close;
	$this->Query_ID = 0;
	return 0;
}

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 = 'MEMO';
		} else {
			$desc = "TEXT($size)";
		}
	} else {
		$type = strtoupper($size);
		switch ($type) {
			case "INT":
				$desc = "integer";
				break;
			case "AUTO":
				$desc .= "COUNTER";
				break;
			case "BLOB":
			case "TEXT":
				$desc .= "MEMO";
				break;
			default:
				$desc = $type;
				break;
		}
	}

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

function insertRow ($table, &$fields) {

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

	//	gets fields name and type from table
	//	------------------------------------
	unset($result); unset($colsize);
	$dbc = $this->dblink;
	$adors=@$dbc->OpenSchema(4);//tables
	if ($adors){
		$t = $adors->Fields('TABLE_NAME'); //classe table name
		while (!$adors->EOF){
			if (strtoupper($t->Value) == strtoupper($table)) {
				$c = $adors->Fields('COLUMN_NAME');
				$s = $adors->Fields('CHARACTER_MAXIMUM_LENGTH');
				$result[]=$c->value;
				@$colsize[]=@$s->Value;
			}
			$adors->MoveNext();
		}
		$adors->Close;
	}
	$nc=count($result);
	if (!$nc) { /* access problem */
		$this->halt("insertRow(): could not list fields from $table: $php_errormsg");
		return -3;
	}
	
	// build columns/values list (set all matching column names)
	// ---------------------------------------------------------
	$cols = "";
	$values = "";
	for($i=0; $i < $nc; ++$i) {
		$colname=$result[$i];
		if (isset($fields[$colname])) {
			$cols .= ", $colname";
			if (!$colsize[$i]) $values .= ", " . intVal($fields[$colname]);
			else {
				$val = $this->preserveQuotes($fields[$colname]);
				$values .= ", '$val'";				
			}
		}
	}

	if (empty($values) ) {
		$this->Errno = -3;
		$this->Error = "warning: insertRow(): no value to update";
		return -3;
	}

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

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

	if ($this->debug) printf("Debug: query = %s<br>\n", $q);

	$ret = $this->query ($q);
	if (!$ret) {
		$this->Halt_On_Error = $tmp;
		return -3;
	}
	
	$this->Errno = 0;
  	$this->Error = "";

	$this->Query_ID->Close;
	$this->Query_ID = 0;
	return 0;
}

/**
 * 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, $name) {
	$this->query ("ALTER TABLE $table DROP COLUMN $col");
}

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


# gets info about forum $name in site $site 
# -----------------------------------------
function getForum ($site, $name) {
	
	$halt = $this->Halt_On_Error;
	$this->Halt_On_Error = "no";

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

	$this->Halt_On_Error = $halt;
	if ($this->next_record() ) {
		$this->Record["cat_id"] = $this->Record["parent"];
		$this->Record["cat_title"] = $cat_title;
		return $this->Record;
	} else {
		return -3;
	}
}

function listForums ($site, $moder="", $sort="", $hide_inactive=0, $cat='') {
	global $auth;
	
	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 U.userid = S.owner";
		//$query = "SELECT S.*, C.rank AS catorder, U.username AS ownername, U.useraddress AS owneraddress FROM $site S LEFT JOIN ${site}_users U ON (S.owner = U.userid) LEFT JOIN $site C ON (C.cle=S.parent) WHERE S.category!=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 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);
	unset ($this->children[0]);
	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;
}



 //	------------------------------------------------------------------
 //				users management functions
 //	------------------------------------------------------------------

/**
 * returns the latest registered user from the site user database
 *
 * @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 TOP 1 userid,username,useraddress FROM ${site}_users WHERE lastlogin>0 ORDER BY unixdate DESC";
	$result = $this->query ($query);
	if ($this->next_record() ) return $this->Record;
	else return -3;
}

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

/**
 * 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;
	$this->query ("INSERT INTO $log_table (datestamp, userid, forum, action, thread, note_id, ip) VALUES (Date(), '$userid', '$forum', '$action', '$thread', '$key', '$ip')");
} // end func



/**
 * Gets monthly visitors.
 *
 * Returns an array indexed by months with the number of distinct visitors 
 * @param	site	the site 
 * @since	4.1.3
 * @access	public
 * @return	array		An array of the form: $array[month] = visits where month as the form YYYYMM
 */
function getMonthlyVisitors ($site, $year='', $month='') {
	$log_table = $site.'_log';

	if (empty($year)) {
		$this->query("SELECT YEAR(datestamp) AS y, MONTH(datestamp) AS m FROM $log_table GROUP BY YEAR(datestamp), MONTH(datestamp)");
		while ($this->next_record()) {
			$m = sprintf ('%04d%02d', $this->Record['y'], $this->Record['m']);
			$visits[$m] = 0;
		}
	} else {
		$y = sprintf('%04d', $year);
		if (empty($month)) {
			$this->query("SELECT MONTH(datestamp) AS m FROM $log_table WHERE year(datestamp)='$y' GROUP BY MONTH(datestamp)");
			while ($this->next_record()) {
				$m = $y . sprintf ('%02d', $this->Record['m']);
				$visits[$m] = 0;
			}

		} else {
			$m = $y . sprintf('%02d', $month);
			$visits[$m] = 0;
		}
	}

	if (!is_array($visits)) {
	    return '';
	}

	reset($visits);
	while (list($month) = each ($visits)) {
		$y = substr($month, 0, 4);
		$m = substr($month, 4, 2);
		$this->query("SELECT DISTINCT userid FROM $log_table WHERE year(datestamp)='$y' AND month(datestamp)='$m'");
		$visits[$month] = (int) $this->num_rows();
	}

	return $visits;
}

/**
 * Gets Daily statistics.
 *
 * Detail description
 * @param	site	the site 
 * @param	year	the year (YYYY) 
 * @param	month	the month (1-12)
 * @since	4.1.3
 * @access	public
 * @return	array		A double dimension array : $array[day][action] = count 
 *						where day is in the rang 1..31 and action can be one value of view|insert|subscribe|login
 */
function getDailyStats ($site, $year, $month) {

	$log_table = $site.'_log';
	$y = sprintf('%04d', $year);
	$m = sprintf('%02d', $month);

	$this->query("SELECT day(datestamp) AS D, action, count(action) AS cnt FROM $log_table WHERE year(datestamp)=$y AND month(datestamp)=$m GROUP BY day(datestamp), action");
	while ($this->next_record()) {
		$d = sprintf('%02d', $this->Record['D']);
		$action = $this->Record['action'];
		$stats[$d][$action] = (int) $this->Record['cnt'];
	}
	
	return $stats;
}

/**
 * Gets Daily statistics.
 *
 * @param	site	the site 
 * @param	year	the year (YYYY) 
 * @param	month	the month (1-12)
 * @since	4.1.3
 * @access	public
 * @return	array		A double dimension array : $array[day][action] = count 
 *						where day is in the rang 1..31 and action can be one value of view|insert|subscribe|login
 */
function getDailyVisitors ($site, $year, $month) {

	$log_table = $site.'_log';
	$y = sprintf('%04d', $year);
	$m = sprintf('%02d', $month);

	$last_day = date ('t', mktime (0, 0, 0, $month, 1, $month) );
	for ($day = 1; $day <= $last_day; $day++) {
		$d = sprintf('%02d', $day);
		$this->query("SELECT DISTINCT userid FROM $log_table WHERE year(datestamp)='$y' AND month(datestamp)='$m' AND day(datestamp)='$d'");
		$visits[$d] = (int) $this->num_rows();
	}
	return $visits;
}

/**
 * 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) {

	$this->query("SELECT sum(hits) as totalhits FROM $forum WHERE hidden=0");
	if (!$this->next_record()) {
		return false;
	}
	$result['totalhits'] = $this->Record['totalhits'];

	$this->query("SELECT DISTINCT userid FROM $forum WHERE hidden=0");
	if ($this->next_record()) {
		$result['totalauthors'] = $this->num_rows();
	}

	return $result;
}

/**
 * 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 subscriton count from forum '$forum' 
 * @returns		integer	$cnt			The number of subscribed users
 */
function countSubscribedUsers ($site, $forum='') {

	$query = "SELECT DISTINCT useraddress FROM ${site}_userthread WHERE thread=0";
	if ( !empty($forum) ) $query .= " AND bn_name='$forum'";
	$result = $this->query($query);
	if (!$result) return -3;

	if ($result->RecordCount) {
		$num_rec=0;
		while (!$result->EOF) {
			++$num_rec;
			$result->MoveNext();
		}
		return $num_rec;
	}
	return 0;
}


//	------------------------------------------------------------------
//			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 TOP 1 thread AS tprev FROM $forum WHERE $whereclause ORDER BY newest");
	if ($this->next_record() ) $note["tprev"]= $this->f("tprev");

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

	$result = $this->query ("SELECT TOP 1 thread AS tnext FROM $forum WHERE $whereclause ORDER BY newest DESC");
	if ($this->next_record() ) {
		$note["tnext"]= $this->f("tnext");
	}

	return $note;
}

/*function getNewestThread ($forum, $where="") {

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

	$result = $this->query ("SELECT TOP 1 thread,unixdate FROM $forum WHERE hidden=0 ORDER BY unixdate DESC");
	if ($this->next_record() ) $thread = $this->f(0); else return -4;
	}

	$result = $this->query ("SELECT * FROM $forum WHERE cle=$thread");
	if ($this->next_record() ) $note = $this->Record; else return -4;
	}

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

	$result = $this->query ("SELECT TOP 1 thread AS tnext FROM $forum WHERE $whereclause ORDER BY newest DESC");
	if ($this->next_record() ) $note["tnext"]= $this->f(0);

	return $note;
}*/

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 = $this->query("SELECT count(cle) as total FROM $forum $whereClause");
	$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 # of threads to be returned
	// -------------------------------
	$limitClause = (empty($limit)) ? "" : "TOP $limit";

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

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

		$whereClause = "WHERE thread IN (";
		$ncount=0;
		
		$t = $this->Record["thread"];
		$whereClause .= "$t,";
		$replies[$t] = $this->Record["quanti"] - 1;
		$ncount += $this->Record["quanti"];
		
		while ($this->next_record()) {
			$t = $this->Record["thread"];
			$whereClause .= "$t,";
			$replies[$t] = $this->Record["quanti"] - 1;
			$ncount += $this->Record["quanti"];
			$a=$this->Record["newest"]; 
		}
		$whereClause = ereg_replace (",$", ")", $whereClause);
		if ($order=="DESC") $tlast = $a; else $tfirst = $a;
	}

	$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) {
		$limitClause = "TOP $ncount";
	} else {
		$whereClause .= " AND parent=0";
		$limitClause = "TOP $tcount";
	}

	$cols = "cle,parent,childs,thread,newest";
	reset($list_var);
	while (list($var,$val) = each($list_var)) {
	  if ($val==1) $cols.=",$var";
	}
	
	$query = "SELECT $limitClause $cols FROM $forum $whereClause ORDER BY newest DESC,unixdate ASC";
	if ($this->debug) printf("Debug: query = %s<br>\n", $query);
	$result = $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"]; // cle
		$parent = (int) $this->Record["parent"]; // parent
		if ($parent==0) $this->Record["replies"] = $replies[$key];
		$this->entries[$key] = $this->Record;
		$this->children[$parent][] = $key;
	}
	$this->Query_ID->Close;
	unset($result);
	
	//	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");
	$this->next_record();
	$ret["prev"]  = $this->Record["prev"];
	$this->Query_ID->Close;
	unset($result);
	
	if ($tlast) {
		$whereClause = "WHERE newest<$tlast";
	} else {
		$whereClause = "WHERE newest<0";
	}
	
	if (!$showhidden) {
		$whereClause .= " AND hidden=0";
	}

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

	$this->Query_ID->Close;
	unset($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 = "WHERE cle<>0";
	if (!empty($where)) $whereClause .= " AND $where";
	$result = $this->query("SELECT count(*) as total FROM $forum $whereClause");
	$this->next_record();
	$ret["total"]  = $this->Record["total"];
	if ($ret["total"] == 0) return $ret;
	$this->Query_ID->Close;
	unset($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";
	
	// Set first thread and # of threads to be returned
	// ------------------------------------------------
	// We have to do this somewhat roundabout. Number of 
	// msgs to grab is equal to the start-message +
	// the limit  (number of msgs to view). Then we
	// grab this number of messages using TOP and data_seek
	// to the starting position. The arithmetic is somewhat
	// simpler than MySQL's LIMIT clause here.
	if ($limit>0) {
		if ($last > 0) {
			$start = ($last-$limit);
			$start = ($start<0) ? 0 : $start;
		} else {
			$start = $first;
		}
		// We use TOP to grab the correct number of threads,
		// thence we need to use mssql_data_seek to get to the
		// start
		$tograb = $limit + $start;
		$limitClause = "TOP $tograb";
	} else {
		$start=0;
		$limitClause="";
	}
		
	$cols = "cle,parent,childs,thread,newest";
	while (list($var,$val) = each($list_var)) if ($val==1) $cols.=",$var";
	
	$query = "SELECT $limitClause $cols FROM $forum $whereClause $orderClause";
	$result = $this->query("$query");
	if ($start) $this->seek($start);

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

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

	//	3) set next/previous (1 if TRUE)
	//	--------------------------------
	$ret["next"] = ($ret["total"] > $ret["last"]) ? 1 : 0;
	$ret["prev"] = ($ret["first"] > 1) ? 1 : 0;
	
	return $ret;
}
/**
 * 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";

	$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;

	$result = $this->query ("select att_id from $upload_table WHERE note_id=$note_id and bn_name='$forum' and att_name='$name'");
	$this->next_record();
	$att_id   = $this->Record["att_id"];
	$this->Query_ID->Close;
	unset($result);

	return $att_id;
}

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 {
		// strip all extra spaces
		$pattern = ereg_replace ("[[:space:]]+", " ", trim($pattern));

		if ( strstr ($pattern, '"' ) ) {
			//first pull out all the double quoted strings
			if (strstr ($pattern, "\"")) {
				$search_string = $pattern;
				while (ereg('-*"[^"]*"', $search_string, $match)) {
					$terms[]=trim(str_replace("\"", "", $match[0]));
					$search_string=substr(strstr($search_string, $match[0]), strlen($match[0]));
				}
			}
			$pattern = ereg_replace('-*"[^"]*"', '', $pattern );
		}
		$terms = explode (" ", $pattern);
	}

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

	// Generate a pattern of [pP][aA] and so forth for insensitive search
	$canonical = strtolower($pattern);
	$insensitive = preg_replace("/([[:alpha:]])/e", "'[\\1' . strtoupper('\\1]')", $canonical);

	// for each term to search...
	reset ($terms);
	while (list (, $term) = each ($terms)) {
		if (!empty($term) ) {
			$term = $this->preserveQuotes($term);
			if(substr($term, 0, 1) == "-") {
				$term =substr($term, 1);
				$like = "NOT LIKE";
			} else {
				$like = "LIKE";
			}
			// ... select the columns to look in
			if ($casesensitive) {
				// this is assuming MSSQL-server was installed with a case-sensitive
				// sort order. Elsehow, you get case-insensitive searches anyway.
				$expr="LIKE '%$pattern%'";
			} else {
				$expr="LIKE '%$insensitive%'";
			}

			// this is assuming MSSQL-server was installed with a case-sensitive
			// sort order. Elsehow, you get case-insensitive searches anyway.
			if ($casesensitive) {
				$searchterms[] = "(" . implode ($search_fields, " $like '%$term%' OR ") . " $like '%$term%')";
			} else {
				$searchterms[] = "(" . implode ($search_fields, " $like '%$insensitive%' OR ") . " $like '%$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	

//In più ho dovuto riattivare la vecchia versione (codice commentato) di getForum() e di listForums() 
}; // end class

} // defined ACCESS_ACCESS
?>
