Location: PHPKode > projects > OutBoard - In/Out Office Board > outboard-2.2.5/lib/OutboardDatabase.php
<?php

/**
 * OutboardDatabase.php
 *
 * Controls all database access for the OutBoard.
 *
 * 2005-02-15	Richard F. Feuerriegel	(hide@address.com)
 *	- Initial creation
 *
 **/

require_once("lib/OutboardConfig.php");

Class OutboardDatabase extends OutboardConfig {

var $dbh = null;           // Database handle
var $hostIP = null;        // IP address of the user's computer
var $operatingUser = null; // The username of the person using the OutBoard
var $readonly = null;      // Boolean. True if the board is in read-only mode.
var $admin = null;         // Boolean. True if the user is an OutBoard administrator.
var $session = null;       // The current session value used for authentication later
var $result = null;	   // The current query result handle


Function OutboardDatabase() {
  // Call the superclass constructor
  $this->OutboardConfig();
  
  if (! function_exists("mysql_pconnect")) {
	  trigger_error("The MySQL libraries are not installed.");
  }

  // Open the database connection
  $this->dbh = @mysql_pconnect($this->getConfig('dbhost'),
			      $this->getConfig('dbuser'),
			      $this->getConfig('dbpass'));
  if (! $this->dbh) {
    trigger_error("Unable to connect to the database server.");
  }
  if (! @mysql_select_db($this->getConfig('db'),$this->dbh)) {
    trigger_error("Unable to open the OutBoard database.");
  }
  $this->hostIP = $_SERVER['REMOTE_ADDR'];
  $this->setReadonly(false);
  $this->setAdmin(false);
  $this->autoLogoutIdlers();
}


Function getOperatingUser() { return $this->operatingUser; }
Function setOperatingUser($username) { $this->operatingUser = $username; }

Function isReadonly() { return $this->readonly; }
Function setReadonly($boolean) { $this->readonly = $boolean; }

Function isAdmin() { return $this->admin; }
Function setAdmin($boolean) { $this->admin = $boolean; }

Function isChangeable($userid) {
  $level  = $this->getConfig('allow_change');
  $opuser = $this->getOperatingUser();
  if     ($level == "all") { return true; }
  elseif ($level == "user_only"  && $userid == $opuser) { return true; }
  elseif ($level == "admin_only" && $this->isAdmin() )  { return true; }
  elseif ($level == "user_admin" 
	  && ($this->isAdmin() || $userid == $opuser))  { return true; }
  else { return false; }
}


Function getSession($session_cookie) {
  $table = $this->getConfig('table');
  if ($session_cookie == "") { return null; }
  $stmt = "SELECT userid,name,options FROM $table WHERE session = '$session_cookie'";
  $this->_query($stmt);
  if ($this->numRows() != 1) {
    return null;
  } else {
    $row = $this->getRow();
    $this->setOperatingUser($row['userid']);
    $this->setReadonly(ereg("<READONLY>",$row['options']));
    $this->setAdmin(ereg("<ADMIN>",$row['options']));
    return $this->getOperatinguser();
  } 
}


Function setSession($session = "NONE") {
  $table = $this->getConfig('table');
  if (! $userid = $this->getOperatingUser()) { return false; }
  if ($session == "NONE") {
    mt_srand((double)microtime()*1000000); 
    $session = mt_rand(1,10000000) . uniqid(""); 
    $this->session = $session;
  }
  $stmt = "UPDATE $table SET session='$session' WHERE userid='$userid'";
  if ($this->_query($stmt)) {
    return $session;
  } else {
    return false;
  }
}


Function checkPassword($username,$password) {
  $table = $this->getConfig('table');
  $username = addslashes($username);
  $password = addslashes($password);
  $stmt = "SELECT userid FROM $table WHERE userid='$username' and password=password('$password')";
  if ($this->_query($stmt)) {
    if ($this->numRows() == 1) {
      $row = $this->getRow();
      $this->setOperatingUser($row['userid']);
      return $this->setSession(); 
    }
  }
  return false;
}


Function getLogStartDate() {
  $logtable = $this->getConfig('logtable');
  $stmt = "SELECT timestamp,"
	 ."date_format(timestamp, '%Y-%m-%d') as changedate "
	 ."FROM $logtable ORDER BY rowid ASC limit 1";
  if ($this->_query($stmt)) {
    $row = $this->getRow();
    return $row['changedate'];
  } else {
    return null;
  }
}

Function getLogEndDate() {
  $logtable = $this->getConfig('logtable');
  $stmt = "SELECT timestamp,"
	 ."date_format(timestamp, '%Y-%m-%d') as changedate "
	 ."FROM $logtable timestamp ORDER BY rowid DESC limit 1";
  if ($this->_query($stmt)) {
    $row = $this->getRow();
    return $row['changedate'];
  } else {
    return null;
  }
}


// Gets all the rows/data from the log table for a specific user 
// and data range. $start and $end are in the form 'yyyy-mm-dd'.
Function getLogData($userid,$start,$end) {
  $logtable = $this->getConfig('logtable');
  $stmt =
    "SELECT rowid,userid,back,remarks,name,timestamp,
            date_format(back, '%H:%i') as backtime,
            date_format(timestamp, '%Y-%m-%d') as changedate,
            date_format(timestamp, '%H:%i') as changetime,
            date_format(timestamp, '%d') as day,
            unix_timestamp(timestamp) as timeinseconds
     FROM $logtable
     WHERE timestamp >= '$start'
       AND timestamp <= '$end 23:59:59'
       AND userid = '$userid'
     ORDER BY timestamp
    ";
  if ($this->_query($stmt)) {
    return true;
  } else {
    return false;
  }
}

// Gets the log data and converts it into an array;
Function getLogDataArray($userid,$start,$end) {
  if ($this->getLogData($userid,$start,$end)) {
    if (! $this->numRows()) { return null; }
    $ld = Array();
    while($row = $this->getRow()) {
      $ld[] = $row;
    }
    return $ld;
  } else {
    return null;
  }
}


Function getNames() {
  $table = $this->getConfig('table');
  $stmt = "SELECT DISTINCT userid,name FROM $table "
         ."WHERE options is null or options NOT LIKE '%<READONLY>%' ORDER BY name";
  $this->_query($stmt);
  if (! $this->numRows()) { return null; }
  $userArray = Array();
  while($row = $this->getRow()) {
    $userArray[$row['userid']] = $row['name'];
  } 
  return $userArray;
}


// Gets all the rows/data from the main Outboard table
Function getData() {
  $table = $this->getConfig('table');
  $stmt = "select rowid, userid, name, options, unix_timestamp(back) as back, "
         ."remarks, last_change, date_format(timestamp, '%m/%d, %l:%i %p') as timestamp "
         ."from $table order by name";
  if ($this->_query($stmt)) {
    return true;
  } else {
    return false;
  }
}

// Moves the dots to Out after a specified idle time (in seconds).
Function autoLogoutIdlers() {
  $seconds = abs(floor($this->getConfig('max_idle_seconds')));
  if ($seconds > 0) {
    $table = $this->getConfig('table');
    $out = $this->getConfig('out');
    $stmt = "UPDATE $table "
	   ."SET back='$out',last_change='auto-logout,0.0.0.0' "
	   ."WHERE back != '$out' AND "
	   ."(unix_timestamp(now()) - unix_timestamp(timestamp)) > $seconds";
    $this->_query($stmt);
  }
}

// Gets the data on a single person
Function getDataByID($rowid) {
  $table = $this->getConfig('table');
  $rowid = addslashes($rowid);
  $stmt = "select rowid, userid, password, name, options, "
         ."remarks, last_change, date_format(timestamp, '%m/%d, %l:%i %p') as timestamp "
         ."from $table where rowid = '$rowid'";
  if ($this->_query($stmt)) {
    return true;
  } else {
    return false;
  }
}

Function isBoardMember($userid) {
  $table = $this->getConfig('table');
  $userid = addslashes($userid);
  $stmt = "select rowid from $table where userid = '$userid'";
  $this->_query($stmt);
  if ($this->numRows() == 1) {
    return true;
  } else {
    return false;
  }
}


Function setDotIn($userid) {
  return $this->_moveDot($userid,$this->getConfig('in')); 
}


Function setDotOut($userid) {
  return $this->_moveDot($userid,$this->getConfig('out')); 
}


Function setDotTime($userid,$hour) {
  $current = getdate();
  $return_datetime = mktime($hour,'00','00',$current['mon'],$current['mday'],$current['year']);
  $back = date('Y-m-d H:i:s',$return_datetime);
  return $this->_moveDot($userid,$back); 
}


Function _moveDot($userid,$back) {
  if ($this->isReadonly()) { return true; }
  if (! $this->isChangeable($userid)) { return true; }
  $table = $this->getConfig('table');
  $hostIP = $this->hostIP;
  $operatingUser = $this->operatingUser;
  $stmt = "update $table "
	 ."set back='$back',last_change='$operatingUser,$hostIP' "
	 ."where userid = '$userid'";
  if($this->_query($stmt)) {
    return $this->_log($userid);
  }
}


Function setRemarks($userid,$remarks) {
  if ($this->isReadonly()) { return true; }
  if (! $this->isChangeable($userid)) { return true; }
  $table = $this->getConfig('table');
  $hostIP = $this->hostIP;
  $operatingUser = $this->operatingUser;
  $remarks = trim($remarks);
  $stmt = "update $table "
	 ."set remarks='".addslashes($remarks)."',last_change='$operatingUser,$hostIP' "
	 ."where userid = '$userid'";
  if($this->_query($stmt)) {
    return $this->_log($userid);
  }
}


Function _log($userid) {
  $table = $this->getConfig('table');
  $logtable = $this->getConfig('logtable');
  $stmt = "select * from $table where userid = '$userid'";
  $this->_query($stmt); 
  $row = $this->getRow();
  $r_remarks = addslashes($row['remarks']);
  $r_userid = $row['userid'];
  $r_back = $row['back'];
  $r_name = $row['name'];
  $r_last_change = $row['last_change'];
  $stmt = "INSERT INTO $logtable (userid,back,remarks,name,last_change) "
         ."VALUES ('$r_userid','$r_back','$r_remarks','$r_name','$r_last_change')";
  return $this->_query($stmt);
}


// Edits an existing user if $rowid is set; adds otherwise.
Function saveUser($rowid,$name,$pass,$visible,$options) {
  $table = $this->getConfig('table');
  $rowid = addslashes($rowid);
  $name = addslashes($name);
  $pass = addslashes($pass);
  $visible = addslashes($visible); // name
  $options = addslashes($options);
  if ($rowid) {
    $this->getDataByID($rowid);
    $row = $this->getRow();
    // Only update the password if it changed on the form.
    if ($row['password'] != $pass) {
      $password = "password('$pass')";
    } else {
      $password = "'$pass'";
    }
    $stmt = "UPDATE $table SET "
	   ."userid='$name',password=$password,"
	   ."name='$visible',options='$options' "
	   ."WHERE rowid='$rowid'";
  } else {
    $stmt = "INSERT INTO $table (userid,password,name,options) "
	   ."VALUES ('$name',password('$pass'),'$visible','$options')";
  }
  return $this->_query($stmt);
}


// Delete the user from the OutBoard
Function deleteUser($rowid) {
  $table = $this->getConfig('table');
  $rowid = addslashes($rowid);
  if (! $rowid) { return null; }
  $stmt = "DELETE FROM $table WHERE rowid='$rowid'";
  return $this->_query($stmt);
}


Function _query($stmt) {
  if (! $stmt) { return false; }
  if ($this->result = mysql_query($stmt)) {
    return true;
  } else {
    trigger_error("Error in database query.");
    //print(mysql_error());
    //print("stmt = ".$stmt);
    return false;
  }
}

Function numRows() {
  if (! $this->result) { return null; }
  return mysql_num_rows($this->result);
}

Function getRow() {
  if (! $this->result) { return null; }
  if ($row = mysql_fetch_array($this->result)) { 
    return $row;
  } else {
    return null;
  } 
}

}

?>
Return current item: OutBoard - In/Out Office Board