<?php
// $Author: perlstalker $
// $Id: sasql_db.php,v 1.18 2006/11/08 15:24:39 perlstalker Exp $

require_once('DB.php');

if (!defined('SM_PATH')) { define('SM_PATH' , '../../'); }
include_once(SM_PATH.'include/load_prefs.php');
include_once(SM_PATH.'plugins/sasql/sasql_conf.php');

global $columns;

sqgetGlobalVar('color', $color, SQ_SESSION);

if (!isset ($columns)) {
  $columns = array();
}

if (!isset($columns['username'])) {
  $columns['username'] = 'username';
}

if (!isset($columns['preference'])) {
  $columns['preference'] = 'preference';
}

if (!isset($columns['value'])) {
  $columns['value'] = 'value';
}

if (!isset($columns['prefid'])) {
  $columns['id'] = 'prefid';
}

function sasql_DBConnect ( $dsn ) {
  global $color;
  $dbh = DB::connect ($dsn, true);
  if (DB::isError($dbh) ) { # || DB::isWarning($dbh)) {
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error in %s: %s"),
			    'sasql_DBConnect',
			    DB::errorMessage($dbh)),
	       $color);
    return "";
  }

  return $dbh;
}

function sasql_DBDisconnect ( $dbh ) {
  $dbh->disconnect();
  return "";
}

function sasql_ReadWBList ( $dbh, $table, $username ) {
  global $columns, $color;
  $query = sprintf ("SELECT * from %s WHERE %s = '%s' AND (%s = 'whitelist_from' OR %s = 'unwhitelist_from' OR %s = 'blacklist_from' OR %s = 'unblacklist_from' OR %s = 'whitelist_to') order by %s,%s asc",
		    $table,
		    $columns['username'],
		    $username,
		    $columns['preference'],
		    $columns['preference'],
		    $columns['preference'],
		    $columns['preference'],
		    $columns['preference'],
		    $columns['preference'],
		    $columns['value']
		    );
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error in %s: %s"),
			    'sasql_ReadWBList',
			    $res->getDebugInfo()),
	       $color);
    return "";
  }

  $wbList = array('whitelist_from' => array(),
		  'blacklist_from' => array(),
		  'unwhitelist_from' => array(),
		  'unblacklist_from' => array(),
		  'whitelist_to' => array());
  while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
    array_push ($wbList[strtolower($row['preference'])],
		array('value' => $row['value'],
		      'prefid' => $row['prefid'],
		      'preference' => strtolower($row['preference'])));
  }
  return $wbList;
}

// See if $addr is in $username's white or blacklist.
// $wblist = 'w' => Whitelist
// $wblist = 'b' => Blacklist
function sasql_AddrInWBList ($dbh, $table, $username, $wblist, $addr)
{
  global $columns;
  $wlquery = sprintf ("SELECT * from %s where %s = '%s' and (%s = 'whitelist_from' and %s = '%s')",
		      $table,
		      $columns['username'],
		      $username,
		      $columns['preference'],
		      $columns['value'],
		      $addr
		      );
  $blquery = sprintf ("SELECT * from %s where %s = '%s' and (%s = 'blacklist_from' and %s = '%s')",
		      $table,
		      $columns['username'],
		      $username,
		      $columns['preference'],
		      $columns['value'],
		      $addr
		      );

  $res = false;
  if ($wblist == 'w') {
    #error_log("W Query: $wlquery");
    $res = $dbh->query($wlquery);
  } elseif ($wblist == 'b') {
    #error_log("B Query: $blquery");
    $res = $dbh->query($blquery);
  }

  $found = 0;
  if (DB::isError($res)) {
    // bad stuff
  } else {
    $row = $res->fetchRow(DB_FETCHMODE_ASSOC);
    $found = $row['value'];
  }

  if ($found) { return 1; }
  else { return 0; }
}

// Same as above but useful if you don't have an existing connection.
// This may be removed at some later date because I think it may be
// better to use _DBConnect+_AddrInWBList+_DBDisconnect instead.
function sasql_AddrInWBList_dsn ($dsn, $table, $username, $wblist, $addr)
{
  global $columns;
  //connect
  $dbh = sasql_DBConnect ($dsn);
  if (DB::isError($dbh)) {
  } else {
    $found = sasql_AddrInWBList ($dbh, $table, $username, $wblist, $addr);
  }
  //disconnect
  sasql_DBDisconnect ($dbh);
  return $found;
}

// Check if $domain is in $username's white/blacklist
// $wblist = 'w' => Whitelist
// $wblist = 'b' => Blacklist
function sasql_DomainInWBList ($dbh, $table, $username, $wblist, $domain) {
	global $colunms;
	$wlquery = sprintf ("SELECT * from %s where %s = '%s' and (%s like 'whitelist_%%' and %s like '%%@%s')",
						$table,
						$columns['username'],
						$username,
						$columns['preference'],
						$columns['value'],
						$domain
						);
	$blquery = sprintf ("SELECT * from %s where %s = '%s' and (%s like 'blacklist_%%' and %s like '%%@%s')",
						$table,
						$columns['username'],
						$username,
						$columns['preference'],
						$columns['value'],
						$domain
						);
						
	$res = false;
  	if ($wblist == 'w') {
    	$res = $dbh->query($wlquery);
	} elseif ($wblist == 'b') {
    	$res = $dbh->query($blquery);
  	}

	$found = 0;
  	if (DB::isError($res)) {
    	// bad stuff
  	} else {
    	$row = $res->fetchRow(DB_FETCHMODE_ASSOC);
    	$found = $row['value'];
  	}

  	if ($found) { return 1; }
  	else { return 0; }
}

//function sasql_WriteWBList ($dsn, $table, $username, $wbList) {}

function sasql_ReadPrefs ( $dbh, $table, $username ) {
  global $columns;
  global $sa_params;
  global $color;
  $query = sprintf ("SELECT * from %s WHERE %s = '%s' AND (%s != 'whitelist_from' OR %s != 'blacklist_from') order by %s desc",
		    $table,
		    $columns['username'],
		    $username,
		    $columns['preference'],
		    $columns['preference'],
		    $columns['preference']
		    );
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error in %s: %s"),
			    'sasql_ReadPrefs',
			    $res->getDebugInfo()),
	       $color);
    return "";
  }

  $prefs = array();
  while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
    $p = $row['preference'];
    if (isset($sa_params[$row['preference']]['multiple'])
        and $sa_params[$row['preference']]['multiple']) {
      if (! isset($prefs[$row['preference']])) {
	$prefs[$row['preference']] = array ('value' => array(),
					    'prefid' => array());
      }
      if ($sa_params[$row['preference']]['one_line']) {
	$xValues = explode (' ', $row['value']);
	foreach ($xValues as $v) {
	  array_push ($prefs[$row['preference']]['value'], $v);
	  array_push ($prefs[$row['preference']]['prefid'], $row['prefid']);
	}
      } else {
	array_push ($prefs[$row['preference']]['value'], $row['value']);
	array_push ($prefs[$row['preference']]['prefid'], $row['prefid']);
      }
    } else {
      $prefs[$row['preference']] = array ('value' => $row['value'],
					  'prefid' => $row['prefid']);
    }
  }
  return $prefs;
}

//function sasql_WritePrefs ( $dsn, $table, $username, $prefs ) {}

function sasql_UpdatePref ( $dbh, $table, $username, $prefid, $preference, $value ) {
  global $columns;
  global $color;
  $query = sprintf ("UPDATE %s set %s ='%s', %s ='%s' where %s ='%s' and %s ='%s'",
		    $table,
		    $columns['value'],
		    $value,
		    $columns['preference'],
		    strtolower($preference),
		    $columns['id'],
		    $prefid,
		    $columns['username'],
		    $username
		    );
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error in %s: %s"),
			    'sasql_UpdatePref',
			    $res->getDebugInfo()),
	       $color);
  }
}

function sasql_UpdatePref_multiple ( $dbh, $table, $username,
				     $preference, $value ) {
  global $columns;
  global $color;
  // NOTE: $value is an array
  // There is probably a cleaner way to do this. I'll worry about it later.
  // Delete all values for this preference and user.
  global $sa_params;
  $query = sprintf ("DELETE from %s where %s = '%s' and %s='%s'",
		    $table,
		    $columns['username'],
		    $username,
		    $columns['preference'],
		    $preference);
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error: %s"),
			    'sasql_UpdatePref_multiple',
			    //DB::errorMessage($dbh)));
			    $res->getDebugInfo()),
	       $color);
  }
  // (Re)Add the new values.
  sasql_AddPref_multiple ($dbh, $table, $username, $preference, $value);
}

function sasql_AddPref ( $dbh, $table, $username, $preference, $value ) {
  global $columns;
  global $color;
  $query = sprintf ("INSERT INTO %s (%s,%s,%s) VALUES('%s', '%s', '%s')",
		    $table,
		    $columns['username'],
		    $columns['value'],
		    $columns['preference'],
		    $username,
		    $value,
		    strtolower($preference));
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error in %s: %s"),
			    'sasql_AddPref',
			    $res->getDebugInfo()),
	       $color);
  }
}

function sasql_AddPref_multiple ( $dbh, $table, $username,
				  $preference, $value ) {
  // NOTE: $value is an array
  global $sa_params;
  global $color;
  if (is_array($value)) {
    if ($sa_params[$preference]['one_line']) {
      $one_val = implode (' ', $value);
      sasql_AddPref ($dbh, $table, $username, $preference, $one_val);
    } else {
      foreach ($value as $v) {
	$query = sprintf ("INSERT INTO %s (%s, %s, %s) VALUES('%s', '%s', '%s')",
			  $table,
			  $columns['username'],
			  $columns['value'],
			  $columns['preference'],
			  $username,
			  $v,
			  strtolower($preference));
	$res = $dbh->query($query);
	if (DB::isError($res)) {
	  sasql_DBDisconnect($dbh);
	  include_once (SM_PATH.'functions/display_messages.php');
	  error_box (sprintf ( _("sasql_AddPref_multiple Database error: %s"),
				  $res->getDebugInfo()),
		     $color);
	}
      }
    }
  } else {
    sasql_AddPref ($dbh, $table, $username, $preference, $value);
  }
}

function sasql_DelPref ( $dbh, $table, $username, $prefid ) {
  global $columns;
  global $color;
  $query = sprintf ("DELETE from %s where %s='%s' and %s='%s'",
		    $table,
		    $columns['id'],
		    $prefid,
		    $columns['username'],
		    $username);
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error in %s: %s"),
			    'sasql_DelPref',
			    $res->getDebugInfo()),
	       $color);
  }
}

function sasql_DelPref_value ($dbh, $table, $username, $pref, $value) {
  global $columns;
  global $color;
  $query = sprintf ("DELETE from %s where %s='%s'"
		    . "and %s = '%s' and %s='%s'",
		    $table,
		    $columns['preference'],
		    $pref,
		    $columns['value'],
		    $value,
		    $columns['username'],
		    $username);
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error in %s: %s"),
			    'sasql_DelPref_value',
			    $res->getDebugInfo()),
	       $color);
  }
}

function sasql_DelPref_name ($dbh, $table, $username, $pref) {
  global $columns;
  global $color;
  $query = sprintf ("DELETE from %s where %s='%s'"
		    . " and %s='%s'",
		    $table,
		    $columns['preference'],
		    $pref,
		    $columns['username'],
		    $username);
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error %s: %s"),
			    'sasql_DelPref_name',
			    $res->getDebugInfo()),
	       $color);
  }
}

function sasql_GetPref ( $dbh, $table, $username, $prefid ) {
  global $columns;
  global $color;
  $query = sprintf ("SELECT * from %s WHERE %s = '%s' AND %s = %s",
		    $table,
		    $columns['username'],
		    $username,
		    $columns['id'],
		    $prefid);
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error in %s: %s"),
			    'sasql_GetPref',
			    $res->getDebugInfo()),
	       $color);
    return "";
  }

  if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
    $pref = array ('value' => $row['value'],
		   'preference' => strtolower($row['preference']),
		   'prefid' => $row['prefid']);
  } else {
    $pref = "";
  }
  return $pref;
}

// if you know the list and email address, then return the prefid
// so that can be used in other function calls such as to delete an
// address from the DB.
function sasql_GetID ( $dbh, $table, $username, $preference, $value) {
  global $columns;
  global $color;
  $query = sprintf ("SELECT * from %s WHERE %s = '%s' AND " .
                    "%s='%s' AND %s='%s'",
		    $table,
		    $columns['username'],
		    $username,
		    $columns['preference'],
		    $preference,
		    $columns['value'],
		    $value);
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error in %s: %s"),
			    'sasql_GetID',
			    $res->getDebugInfo()),
	       $color);
    return "";
  }

  if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
    $pref = $row['prefid'];
  } else {
    $pref = "";
  }
  return $pref;
}

function sasql_GetBayesStats ($dbh, $table, $username) {
  global $bayes_vars_cols, $color;

  $query = sprintf ("SELECT %s, %s from %s where %s = '%s'",
		    $bayes_vars_cols['spam_count'],
		    $bayes_vars_cols['ham_count'],
		    $table,
		    $bayes_vars_cols['username'],
		    $username);
  $res = $dbh->query($query);
  if (DB::isError($res)) {
    sasql_DBDisconnect($dbh);
    include_once (SM_PATH.'functions/display_messages.php');
    /* To translators: %s's are the function name
       and PEAR generated error message.
    */
    error_box (sprintf ( _("Database error in %s: %s"),
			    'sasql_GetID',
			    $res->getDebugInfo()),
	       $color);
    return "";
  }

  $counts = array();

  if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
    $counts['spam_count'] = $row[$bayes_vars_cols['spam_count']];
    $counts['ham_count'] = $row[$bayes_vars_cols['ham_count']];
  } else {
    $counts['spam_count'] = 0;
    $counts['ham_count'] = 0;
  }
  return $counts;
}

?>