<?php

/*
 *	W-AGORA 4.0
 *	-----------
 *	$Id: msql.php,v 1.12 2005/01/31 22:41:33 mdruilhe Exp $
 *	Usage:	include file - Database access functions (mSQL v2.x)
 *	Author:	Marc Druilhe <mdruilhe@w-agora.net>
 */

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

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

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

class msql_access extends DBaccess {

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

var $Auto_Free     = 0;     ## Set to 1 for automatic msql_free_result()

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


#	------------------------------------------------------------------
#				General Database access implementation (cf  : phplib)
#				Copyright (c) 1998,1999 SH Online Dienst GmbH
#                    Boris Erdmann, Kristian Koehntopp
#	------------------------------------------------------------------

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

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

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

	$this->Query_ID = @msql_query($Query_String, $this->dblink);
	$this->Row   = 0;
	if (!$this->Query_ID) {
		$this->Error = msql_error();
		$this->halt("Invalid SQL: ".$Query_String);
	}

	return $this->Query_ID;
}

/* public: walk result set */
function next_record() {
	$this->Record = @msql_fetch_array($this->Query_ID);
	$this->Row   += 1;
	$this->Error  = msql_error();

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

/* public: position in result set */
function seek($pos = 0) {
	$status = @msql_data_seek($this->Query_ID, $pos);
	if ($status)
		$this->Row = $pos;
	else
		$this->halt("seek($pos) failed: result has ".$this->num_rows()." rows");

	return;
}

function affected_rows() {
	return @msql_affected_rows($this->dblink);
}

function num_rows() {
	return @msql_num_rows($this->Query_ID);
}

function num_fields() {
	return @msql_num_fields($this->Query_ID);
}

#	------------------------------------------------------------------
#				End of PHPLIB copyright
#	------------------------------------------------------------------

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

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

	if (!empty ($dbhost)) {
		$host = $dbhost;
	}

	if ($persistent == 1) {
		$this->dblink = msql_pconnect ($host);
	} else {
		$this->dblink = msql_connect ($host);
	}

	if (empty($this->dblink)) {
		$this->Error = msql_error();
		$this->halt("msql_select_db()");
		return ERR_ACCESS;
	}

	if (!msql_select_db ($dbname, $this->dblink)) {
		$this->Error = msql_error();
		$this->halt("msql_select_db()");
		return ERR_ACCESS;
	}

	$this->dbname = $dbname;

	return 0;
}

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

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

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

# Check if table exists
# ---------------------
	$result = @msql_List_Tables ($this->dbname);
	if (!$result) {
		echo "msql error: ".msql_error()."<BR>";
		$this->Halt_On_Error = $tmp;
		return -3;
	}

	$count = msql_NumRows ($result);
	$i=0;
	while ($i < $count) {
		if ($table == msql_TableName ($result, $i)) {
			$this->Halt_On_Error = $tmp;
			return -1;	/*  table already exist */
		}
		$i++;
	}

# 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) ) {
			$desc .= ", $col TEXT($size)";
		} else {
			$type = strtoupper($size);
			switch ($type) {
				case "INT":
				case "AUTO":
					$desc .= ", $col INT";
					break;
				case "TEXT":
				case "BLOB":
					$desc .= ", $col TEXT(10000)";
					break;
				default:
					$desc .= ", $col $type";
					break;
			}
		}
	}

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

	if(!empty($pk)) {
		$pkname = $table. '_pk';
		$ret = $this->query ("CREATE UNIQUE INDEX $pkname ON $table ($pk)");
		if (!$ret) {
			$this->Halt_On_Error = $tmp;
			return -3;
		}
	}

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

function addPrimaryKey ($table, $cols) {

	$pkname = $table. '_pk';
	$ret = $this->query ("CREATE UNIQUE INDEX $pkname ON $table ($cols)");
}

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
 #	------------------------------------
	$result = msql_List_fields($this->dbname, $table);
	if (!$result) { /* access problem */
		$this->halt("could not list fields from $table");
		return -3;
	}

	msql_field_seek($result, 0);

#	build columns/values list (set all matching column names)
#	---------------------------------------------------------
	while ($db_field = msql_fetch_field($result)) {
		$colname = $db_field->name;
		$coltype = $db_field->type;
		if (isset($fields[$colname])) {
			if ($coltype == "int") {
				$values .= ", $colname=" . intVal($fields[$colname]);
			} else {
				$val = addSlashes($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";
	}

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

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
 #	------------------------------------
	$result = msql_List_fields($this->dbname, $table);
	if (!$result) { /* access problem */
		$this->halt("could not list fields from $table");
		return -3;
	}

	msql_field_seek($result, 0);

#	build columns/values list (set all matching column names)
#	---------------------------------------------------------
	$cols = "";
	$values = "";
	while ($db_field = msql_fetch_field($result)) {
		$colname = $db_field->name;
		$coltype = $db_field->type;
		if (isset($fields[$colname])) {
			$cols .= ", $colname";
			if ($coltype == "int") {
				$values .= ", " . IntVal($fields[$colname]);
			} else {
				$val = addSlashes($fields[$colname]);
				$values .= ", '$val'";
			}
		}
	}

	if (empty($values) ) {
		echo "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);

	$result = msql_query($q, $this->dblink);
	if (!$result) {
		echo "msql error: ".msql_error()."<BR>";
		return -3;
	}
	@msql_free_result($result);

	return 0;
}

function listForums ($site, $moder="", $sort="", $hide_inactive=0) {
	if (empty($moder)) {
		$query = "SELECT  S.bn_name, S.bn_title, S.rank, S.state, U.username, U.useraddress FROM ${site} = S, ${site}_users = U WHERE U.userid = S.owner";
	} else {
# gets all forums for wich $moder is moderator
		$query = "SELECT S.bn_name, S.bn_title, S.rank, S.state, U.username as ownername, U.useraddress as owneraddress FROM ${site} = S, ${site}_users = U, ${site}_userforum = UF WHERE S.bn_name=UF.bn_name AND UF.userid = '$moder' AND UF.modpriv=1";
	}

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

	$query .= ( empty($sort) ) ? " ORDER BY S.rank, S.bn_title" : " ORDER BY $sort";

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

	reset ($this->entries);
	while ($this->next_record() ) {
		$this->Record["ownername"] = $this->Record["username"];
		$this->Record["owneraddress"] = $this->Record["useraddress"];
		$key = $this->Record["cle"];
		$this->Record["parent"]=0;
		$name=ereg_replace ("^${site}_", "", $this->Record["bn_name"]);
		$this->entries[$key] = $this->Record;
		$this->children[0][] = $key;
		$forums[$name] = $this->Record;
	}

	return $forums;
}

function getForum ($site, $name) {

	$this->Halt_On_Error = "no";
	$query = "SELECT * FROM ${site} WHERE bn_name='$name'";
	$result = $this->query ($query);
	if ($this->next_record() ) {
		$forum = $this->Record;
	} else {
		$this->Halt_On_Error = "yes";
		return -3;
	}

	$query = "SELECT ${site}_users.username, ${site}_users.useraddress FROM ${site}, ${site}_users WHERE bn_name='$name' AND ${site}_users.userid = $site.owner";
	$result = $this->query ($query);

	$this->Halt_On_Error = "yes";
	if ($this->next_record() ) {
		$forum["ownername"] = $this->Record["username"];
		$forum["owneraddress"] = $this->Record["useraddress"];
	} else {
		return -3;
	}

	return $forum;
}

function updateForumStats ($site, $forum) {

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

	$result = $this->query ("SELECT unixdate FROM $forum WHERE hidden=0 ORDER BY unixdate DESC");
	if (!$this->next_record() ) {
		return -1;
	}

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

	$result = $this->query ("SELECT unixdate FROM $forum WHERE parent=0 AND hidden=0");
	if (! $this->next_record() ) {
		return -1;
	}

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

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

function getUsers($site, $forum="", $privs="", $userid="", $state="", $sort="userid", $username="") {

	$query = "SELECT DISTINCT * FROM ${site}_users U";

	if (!empty($forum) || !empty($state) ) {
		$query .= ", ${site}_userforum UF WHERE U.cle>0 AND U.userid=UF.userid";
	} else {
		$query .= " WHERE U.cle>0";
	}

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

	if (!empty($privs)) {
		$p = split (',' , $privs);
		$query .= " AND (U.userpriv='$p[0]'";
		for ($i=1; $i<count($p); $i++) {
			$query .= " OR U.userpriv='$p[$i]'";
		}
		$query .= ")";
	}

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

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

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

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

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

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

/**
 * 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) {
	$result = $this->query ("SELECT unixdate FROM ${site}_users ORDER BY unixdate DESC");
	if ($this->next_record() ) {
		$unixdate = $this-> f("unixdate");
		$query = "SELECT * FROM ${site}_users WHERE unixdate=$unixdate";
		$result = $this->query ("SELECT * FROM ${site}_users WHERE unixdate=$unixdate");
		//return $this->Record;
		$u = array ('userid'      => $this->Record["userid"],
			    'username'    => $this->Record["username"],
			    'useraddress' => $this->Record["useraddress"]
			    );
		return $u;
	} else {
		return -3;
	}
}


#	------------------------------------------------------------------
#				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 $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 thread AS tnext FROM $forum WHERE $whereclause ORDER BY newest DESC");
	if ($this->next_record() ) {
		$note["tnext"]= $this->f("tnext");
	}

	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 = msql_query("SELECT cle FROM $forum $whereClause", $this->dblink);
	if (!$result) {
		echo "msql error: ".msql_error()."<BR>";
		return (-3);
	}

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

#	2) Find the first thread to be displayed in the page starting from the end (last_thread - limit)
#	------------------------------------------------------------------------------------------------
	if (($limit>0) && $last && !$first) {
		$query = "SELECT thread FROM $forum WHERE thread >=$last ORDER BY thread";
		$result = msql_query ("$query", $this->dblink);
		if ($result) {
			$i=0;
			while ($row = msql_fetch_row($result)) {
				if ($first != $row[0]) {
					$first = $row[0];
					$i++;
				}
				if (($limit > 0) && ($i >= $limit))
					break;
			}
		}
	}

#	3) gets all threads
#	-------------------
	$whereClause="WHERE cle<>0";
	if (!$showhidden) {
		$whereClause .= " AND hidden=0";
	}
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}
	if ($first) $whereClause .= " AND thread<=$first";
	if ($last) $whereClause .= " AND thread>=$last";
	$cols = "cle,parent,childs,thread,newest";
	reset ($list_var);
	while (list($var,$val) = each($list_var)) {
		if ($val==1) $cols.=",$var";
	}
	$result = msql_query ("SELECT $cols FROM $forum $whereClause ORDER BY thread DESC,cle ASC", $this->dblink);
	if (!$result) {
		echo "mSQL error: ".msql_error()."<BR>";
		return (-3); 
	}

	$tlast=time(); $tfirst=0; $ncount = 0; $tcount = 0;
	
	while ($row = msql_fetch_array($result)) {
		$key = (int) $row["cle"];
		$parent = (int) $row["parent"];
		if ($parent==0) { // new thread
			if (($tcount < $limit) || ($limit==0)) {
				$this->entries[$key] = $row;
				$this->children[$parent][] = $key;
				if ($key < $tlast) $tlast = $key;
				if ($key > $tfirst) $tfirst = $key;
				$tcount++;
				$ncount++;
			} else {
				$next=1;
			}
		} else {
			$this->entries[$key] = $row;
			$this->children[$parent][] = $key;
			$ncount++;
		}
	}

	if (!isSet ($prev)) {
		$whereClause = "SELECT cle FROM $forum WHERE thread>$tfirst AND cle<>0";
		if (!$showhidden) {
			$whereClause .= " AND hidden=0";
		}
		$result = msql_query ($whereClause, $this->dblink);
		if (!$result) {
			echo "mSQL error: ".msql_error()."<BR>";
			return (-3); 
		}
		$prev = msql_numrows($result);
	}

	if (!isSet ($next)) {
		$whereClause = "SELECT cle FROM $forum WHERE thread<$tlast AND cle<>0";
		if (!$showhidden) {
			$whereClause .= " AND hidden=0";
		}
		$result = msql_query ($whereClause, $this->dblink);
		if (!$result) {
			echo "mSQL error: ".msql_error()."<BR>";
			return (-3); 
		}
		$next = msql_numrows($result);
	}
	
	msql_FreeResult($result);

	$ret["notes"]=$ncount;		// number of notes displayed
	$ret["threads"]=$tcount;	// number of threads displayed
	$ret["next"]=$next;
	$ret["prev"]=$prev;
	$ret["first"]=$tfirst;
	$ret["last"]=$tlast;
	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 = msql_query("SELECT cle FROM $forum WHERE $whereClause", $this->dblink);
	if (!$result) {
		echo "msql error: ".msql_error()."<BR>";
		return (-3);
	}

	$ret["total"] =  msql_numrows($result);
	if ($ret["total"] == 0) {
		return ($ret);
	}
	msql_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";
	
# Set first thread and # of threads to be returned
# ------------------------------------------------
	if ($limit>0) {
		if ($last > 0) {
			$start = ($last-$limit)+1;
			$start = ($start<0) ? 0 : $start;
		} else {
			$start = $first;
		}
		$limitClause = "LIMIT $start, $limit";
	} 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";
	$result = msql_query("$query", $this->dblink);
	if (!$result) {
		echo "msql error: ".msql_error()."<BR>";
		return (-3);
	}

	reset ($this->entries);
	reset ($this->children);
	$tcount=0;
	msql_data_seek($result, $start);
	while ($row = msql_fetch_array($result)) {
		if (($tcount < $limit) || ($limit==0)) {
			$key = $row["cle"];
			$row["parent"]=0;
			$this->entries[$key] = $row;
			$this->children[0][] = $key;
			$tcount++;
		} else {
			break;
		}
	}
	msql_FreeResult($result);

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

function search ($forum, $pattern, $casesensitive) {
	global $list_var;

	// Get fields defined in table
	$result = msql_List_fields("$this->dbname", $forum);
	if (!$result) { /* access problem */
		echo "mSQL error: ".msql_error()."<BR>";
		return (-3); 
	}

	$pattern = ereg_replace ("\*", "%", $pattern);
	$pattern = "'%$pattern%'";
	if ($casesensitive) {
		$searcher = "LIKE";
	}
	else {
		$searcher = "CLIKE";
	}
	
	$where .= "body $searcher $pattern";
	// gets fields name and type
	while ($db_field = msql_fetch_field($result)) {
		$colname = $db_field->name;
		$coltype = $db_field->type;
		if ( ($colname != "body") && ($coltype != "int") && ($coltype != "text") && ($coltype != "unknown") ) {
			 
			$where .= " OR $colname $searcher $pattern";
		}
	}
	$result = msql_query ( "SELECT * FROM $forum WHERE $where", $this->dblink);
	if (!$result) {
		echo "mSQL error: ".msql_error()."<BR>";
		return (-3); 
	}

	$num = msql_numrows($result);

	if ($num < 1 ) {
		return 0;
	}

	reset ($this->entries);
	reset ($this->children);
	while ($row = msql_fetch_array($result)) {
		$key = $row["cle"];
		$row["parent"]=0;
		$this->entries[$key] = $row;
		$this->children[0][] = $key;
	}

	@msql_free_result($result);
	return $num;
}

}; // end class

} // defined msql_ACCESS
?>
