Location: PHPKode > projects > BugIn and Ticketing System > bugin-0.65/lib/database.inc.php
<?php

/*
 * This is the main file for communicating to the DB server.
 *
 * $Id: database.inc.php,v 1.63 2004/03/09 14:55:04 notgod Exp $
 * $Revision: 1.63 $
 * $Author: notgod $
 * $Date: 2004/03/09 14:55:04 $
 */

/* class buginDb {{{1
	This is the main database class */
class buginDb {
	/* function buginDb($dbHost, $dbUser, $dbPass, $dbName, $dbType, $debug = 0) {{{2
	    The constructor.  This creates the DB instance, and attaches to the DB. */
	function buginDb($dbHost, $dbUser, $dbPass, $dbName, $dbType, $debug = 0) {
		$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
		$this->dbHost = $dbHost;
		$this->dbUser = $dbUser;
		$this->dbPass = $dbPass;
		$this->dbName = $dbName;
		$this->dbType = $dbType;
		$this->tablePrefix = "bugin";
		$this->debug = $debug;
		$this->db = &NewADOConnection($dbType);
		$this->db->SetFetchMode($ADODB_FETCH_MODE);
		$this->db->PConnect($dbHost, $dbUser, $dbPass, $dbName);

		$this->getUserList();
		$this->getLangList();
		$this->getGroupList();
		$this->getProjectList();
		$this->getTriggerList();

		// Load the ACL after users, groups, and projects...
		$this->getACLList();
		$this->getStatusList();
		$this->getPriorityList();
		$this->getCategoryList();
		$this->getFieldList();
		return $this->db;
	}
	/* }}}2 */
	
	/* function getUserList() {{{2
	    Returns a status array. */
	function getUserList() {
		global $options;
		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_users ORDER BY name");

		while (!$query->EOF) {
			$row = $query->fields;
			$row['user_group'] = explode(",", $row['user_group']);
			if ($row['name'] == "") {
				$row['name'] = $row['username'];
			}
			$users[$row['uid']] = $row;
			$query->MoveNext();
		}

		$users[USER_SYSTEM] = $users[1];
		$users[USER_SYSTEM]['uid'] = USER_SYSTEM;
		$users[USER_SYSTEM]['name'] = $options['systemName'];

		$users[USER_SCHEDULER] = $users[1];
		$users[USER_SCHEDULER]['uid'] = USER_SCHEDULER;
		$users[USER_SCHEDULER]['name'] = "{$options['systemName']} Scheduler";

		$this->lists['user'] = $users;
	}
	/* }}}2 */

	/* function getModList($num = 30) {{{2
	   Returns an array of modifications */
	function getModList($num = 30) {
		$query = $this->db->SelectLimit("SELECT * FROM {$this->tablePrefix}_modifications ORDER BY ts DESC", $num);
		$mods = array();

		while (!$query->EOF) {
			$row = $query->fields;
			$row['date'] = strtotime($row['ts']);
			array_push($mods, $row);
			$query->MoveNext();
		}

		return $mods;
	}
	/* }}}2 */

	/* function getGroupList() {{{2
	   Returns an array of groups */
	function getGroupList() {
		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_groups");

		while (!$query->EOF) {
			$row = $query->fields;
			$groups[$row['gid']] = $row;
			$groups[$row['gid']]['id'] = $row['gid'];
			$groups[$row['gid']]['name'] = $row['group_name'];
			$query->MoveNext();
		}

		$this->lists['group'] = $groups;

		return $groups;
	}
	/* }}}2 */

	/* function getACLList() {{{2
	   Returns an array of ACLs */
	function getACLList() {
		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_acl ORDER BY project,acl_type");
		
		while (!$query->EOF) {
			$row = $query->fields;
			$query->MoveNext();
			$acl[$row['id']] = $row;
			switch ($row['acl_type']) {
				case "group":
					reset($this->lists['user']);
					if ($row['id_tag'] == 0) {
						$this->lists['project'][$row['project']]['acl'][0] |= $row['flags'];
					}

					while (list($key2, $user) = each($this->lists['user'])) {
						if (($row['id_tag'] == 0) || in_array($row['id_tag'], $user['user_group'])){
							if (!isset($this->lists['project'][$row['project']]['acl'][$key2])) {
								$this->lists['project'][$row['project']]['acl'][$key2] = 0;
							}
							$this->lists['project'][$row['project']]['acl'][$key2] |= $row['flags'];
						}
					}
					break;
				case "user":
					$this->lists['project'][$row['project']]['acl'][$row['id_tag']] |= $row['flags'];
				break;
			} // Close switch
		}
		$this->lists['acl'] = $acl;

	}
	/* }}}2 */

	/* function getTriggerAffects($tid) {{{2
	   Returns all triggers. */
	function getTriggerAffects($tid) {
		$affects = array();
		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_trigger_affect WHERE tid = '{$tid}'");
		while (!$query->EOF) {
			$affects[$query->fields['id']] = $query->fields;
			$query->MoveNext();
		}
		return $affects;
	}
	/* }}}2 */

	/* function getTriggerRules($tid) {{{2
	   Returns all triggers. */
	function getTriggerRules($tid) {
		$rules = array();
		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_trigger_rule WHERE tid = '{$tid}'");
		while (!$query->EOF) {
			$rules[$query->fields['id']] = $query->fields;
			$query->MoveNext();
		}

		return $rules;
	}
	/* }}}2 */

	/* function getTriggers() {{{2

	   Returns all triggers. */
	function getTriggerList() {
		$queryString = "SELECT * FROM {$this->tablePrefix}_trigger";
		$query = $this->db->Execute($queryString);
		buginError ("Select Statement: ", MESSAGE_DEBUG);
		while (!$query->EOF) {
			$triggerList[$query->fields['id']] = $query->fields;
			$query->MoveNext();
		}

		while (list($key, $value) = each($triggerList)) {
			$triggers[$value['id']] = $value;
			$triggers[$value['id']]['rules'] = $this->getTriggerRules($value['id']);
			$triggers[$value['id']]['affects'] = $this->getTriggerAffects($value['id']);
		}
		$this->lists['trigger'] = $triggers;
		return $triggers;
	}
	/* }}}2 */

	/* function getLangList() {{{2

	   Returns all available languages. */
	function getLangList() {
		$queryString = "SELECT * FROM {$this->tablePrefix}_lang";
		$query = $this->db->Execute($queryString);
		buginError ("Select Statement: ", MESSAGE_DEBUG);
		while (!$query->EOF) {
			$langList[$query->fields['id']] = $query->fields;
			$query->MoveNext();
		}

		$this->lists['lang'] = $langList;
		return $triggers;
	}
	/* }}}2 */

	/* function getProjectList() {{{2
	   Returns an array of projects. */
	function getProjectList() {
		global $projectSortBy;
	
		// Get root projects	
		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_projects WHERE proj_sub_of = '0' ORDER BY {$projectSortBy}");
		
		while (!$query->EOF) {
			$row = $query->fields;
			$projects[$row['id']] = $row;
			$projects[$row['id']]['acl'] = array('0');
			$query->MoveNext();
		}

		// Get Sub-Projects
		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_projects WHERE proj_sub_of != '0' ORDER BY proj_sub_of,{$projectSortBy}");
		while (!$query->EOF) {
			$row = $query->fields;
			$subProjects[$row['proj_sub_of']][$row['id']] = $row;
			$subProjects[$row['proj_sub_of']][$row['id']]['acl'] = array('0');
			$query->MoveNext();
		}


		// Hash it all out....
		while (list($key, $value) = @each($projects)) {
			$proj[$value['id']] = $value;
			$proj[$value['id']]['displayName'] = $value['name'];
			$proj[$value['id']]['optionName'] = $value['name'];
			$proj[$value['id']]['realName'] = $value['name'];

			if ($subProjects[$key]) {
				while (list($key1, $value1) = each($subProjects[$key])) {
					$proj[$value1['id']] = $value1;
					$proj[$value1['id']]['name'] = "{$value['name']}/{$value1['name']}" ;
					$proj[$value1['id']]['optionName'] = "+".$value1['name'];
					$proj[$value1['id']]['realName'] = $value1['name'];
				}
			}
		}

		$this->lists['rootProject'] = $projects;
		$this->lists['subProject'] = $subProjects;

		$this->lists['project'] = $proj;

		return $proj;
	}
	/* }}}2 */

	/* function getFieldsList() {{{2
	   Returns an array of user fields. */
	function getFieldList() {
		global $projectSortBy;

		$fields = array();
		$this->lists['field'] = array();
	
		// Get root projects	
		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_ticket_fields");
		if ($query != false) {
			while (!$query->EOF) {
				$row = $query->fields;
				$id = "usr_{$row['field_name']}";
				$fields[$id] = $row;
				$fields[$id]['field_name'] = $id;
				$fields[$id]['values'] = array();
				$query->MoveNext();
			}

			$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_ticket_field_values");
			if (count($fields) && $query != false) {
				while (!$query->EOF) {
					$row = $query->fields;
					array_push($fields[$row['field']]['values'], array('name' => $row['name'], 'value' => $row['value']));
					$query->MoveNext();
				}
				$this->lists['field'] = $fields;
			}
			else {
				$this->lists['field'] = array();
			}
		}

	}
	/* }}}2 */

	/* function getPriorityList() {{{2
	   Returns a list of Priorities. */
	function getPriorityList() {

		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_priority");

		while (!$query->EOF) {
			$row = $query->fields;
			$priorities[$row['id']] = $row;
			$query->MoveNext();
		}

		$this->lists['priority'] = $priorities;
	}
	/* }}}2 */

	/* function getCategoryList() {{{2
	    Returns a list of Categories. */
	function getCategoryList() {
		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_category ORDER BY name");

		while (!$query->EOF) {
			$row = $query->fields;
			$category[$row['id']] = $row;
			$query->MoveNext();
		}

		$this->lists['category'] = $category;
	}
	/* }}}2 */

	/* function getStatusList() {{{2
	   Returns a status array. */
	function getStatusList() {
		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_status");

		while (!$query->EOF) {
			$row = $query->fields;
			$status[$row['id']] = $row;
			$query->MoveNext();
		}

		$this->lists['status'] = $status;
	}
	/* }}}2 */

	/* function getTicketList($endmod = 0, $selectmod = 0) {{{2
     Returns a ticket list.*/
	function getTicketList($endmod = 0, $selectmod = 0, $order = "", $limit=0) {
		global $options;
		global $listOrderBy;
		global $userinfo;

		if (!isset($listOrderBy)) {
			$listOrderBy = "id";
		}

		if ($selectmod)
			$selectmod = $this->tablePrefix."_tickets.*,".$selectmod;
		else
			$selectmod = $this->tablePrefix."_tickets.*";

		$orderField = array();
		$ticketField = array();
		$orderTable = array();

		if (!is_array($order)) {
			$newOrder = array('0' => $order);
		}
		else {
			$newOrder = $order;
		}

		unset($order);
		
		while (list($key, $theorder) = each($newOrder)) {
			switch($theorder) {
				case "category":
				case "priority":
				case "status":
				case "category":
					$orderTable[] = "{$this->tablePrefix}_$theorder";
					$ticketField[] = $this->tablePrefix."_tickets.{$theorder}";
					$orderField[] = $this->tablePrefix."_{$theorder}.id";
					$order[] = "{$this->tablePrefix}_$theorder.name";
					break;
				case "projects":
					$orderTable[] = "{$this->tablePrefix}_$theorder";
					$ticketField[] = $this->tablePrefix."_tickets.project";
					$orderField[] = $this->tablePrefix."_projects.id";
					$order[] = "{$this->tablePrefix}_$theorder.name";
					break;
				case "owner":
					global $ticketList;
					if ($ticketList[$_REQUEST['sg']][$_REQUEST['listCol']][0]['template'] == "unowned") {
						$order[] = "id";
						break;
					}
				case "creator":
					$ticketField[] = $this->tablePrefix."_tickets.{$theorder}";
					$orderField[] = $this->tablePrefix."_users.uid";
					$orderTable[] = "{$this->tablePrefix}_users";
					$order[] =  $this->tablePrefix."_users.name";
					break;
				default:
					$order[] = $listOrderBy;
					break;
			}
		}

		$order = implode(",", $order);
		$orderTable = implode(",", $orderTable);

		if (!empty($orderTable))
			$orderTable = ",{$orderTable}";

		$orderSelect = "";

		if (!empty($orderField) && !empty($ticketField)) {
			while (list($key, $value) = each($orderField)) {
				$orderSelect = "{$orderField[$key]} = {$ticketField[$key]} AND ";
			}
		}

		$endmodOrder = "{$orderSelect}{$endmod}";

		if (trim($endmodOrder)) {
			$endmodOrder = "WHERE {$endmodOrder} AND ";
		} else {
			$endmodOrder = "WHERE ";
		}

		if (trim($endmod)) {
			$endmod = "WHERE {$endmod} AND ";
		} else {
			$endmod = "WHERE ";
		}

		if (!isset($_SESSION['reverse_sort'])){
			$rev = 0;
		}
		else {
			$rev = intval($_SESSION['reverse_sort']);
		}

		switch($rev) {
				case 0:
					if (($userinfo['options'] & OPTION_LIST_SORT_REVERSE ))
						$order .= " DESC";
					break;
				case 1:
					if (!($userinfo['options'] & OPTION_LIST_SORT_REVERSE ))
						$order .= " DESC";
		}

		$tickets = array();

		$pi = array();
		$projIn = " (";

		while (list($key, $value) = each($this->lists['project'])) {
			if ( ($options['anonCLAccess'] || $_REQUEST['area'] == "scheduler") || (isset($_SESSION['userinfo']) && ($value['acl'][$_SESSION['userinfo']['uid']] & ACL_READ)) ) {
				$pi[$key] = $key;
			}
		}

		if (count($pi)) {
			$pjs = implode(",", $pi);
			if ($pjs) {
				$projIn = " ({$this->tablePrefix}_tickets.project IN({$pjs})";
				if (isset($_SESSION['userinfo']))
					$projIn .= " OR ";
			}
		}
		if (isset($_SESSION['userinfo'])) {
			$projIn .= "(({$this->tablePrefix}_tickets.creator = {$_SESSION['userinfo']['uid']}) OR ({$this->tablePrefix}_tickets.owner = {$_SESSION['userinfo']['uid']}))";
		}
		$projIn .= " )";

		$select = "SELECT {$selectmod} FROM {$this->tablePrefix}_tickets{$orderTable} {$endmodOrder}{$projIn} ORDER BY {$order}";

		$fullSelect = "SELECT count(*) FROM {$this->tablePrefix}_tickets {$endmod}{$projIn}";

		if (is_array($limit))
			$query = $this->db->SelectLimit($select, $limit[0], $limit[1]);
		else
			$query = $this->db->Execute($select);
		if ($query != false) {
			while (!$query->EOF) {
				$ticket = $query->fields;
				$ticket['added_ts'] = $this->db->UnixTimeStamp($ticket['date_added']);
				$ticket['modified_ts'] = $this->db->UnixTimeStamp($ticket['date_modified']);
				$ticket['added'] = date($options['dateFormat'], $ticket['added_ts']);
				$ticket['modified'] = date($options['dateFormat'], $ticket['modified_ts']);
				$ticket['age'] = time() - $ticket['modified_ts'];
				$tickets[$ticket['id']] = $ticket;
				$ticket = array();
				$query->MoveNext();
			}
		}
		
		$query2 = $this->db->Execute($fullSelect);
		$this->totalRecords = $query2->fields;
		reset($tickets);
		return $tickets;
	}
/*		$this->totalRecords = 0;
		return array();*/

	/* }}}2 */

	/* function getTicket($num) {{{2
     Returns a tickets. */
	function getTicket($num) {
		global $options;
		global $adminUserGroup;

		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_tickets WHERE id = '$num'");

		if (($query != false) && ($query->fields != false)) {
			$ticket = $query->fields;
			$ticket['added_ts'] = $this->db->UnixTimeStamp($ticket['date_added']);
			$ticket['modified_ts'] = $this->db->UnixTimeStamp($ticket['date_modified']);
			$ticket['modified'] = date($options['dateFormat'], $ticket['modified_ts']);
			$ticket['added'] = date($options['dateFormat'], $ticket['added_ts']);
			$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_modifications WHERE ticket_id = '$num' ORDER BY ts");

			// Get modifications
			$m = 0;
			while (!$query->EOF) {
				$mod = $query->fields;
				$ticket['modifications'][$m] = $mod;
				$ticket['modifications'][$m]['date'] = date($options['dateFormat'], $this->db->UnixTimeStamp($mod['ts']));
				$m ++;
				$query->MoveNext();
			}

			$ticket['validOwners'] = array();

                        // Generate the real valid users for this tickets...
			reset($this->lists['user']);
			while (list($key, $value) = each($this->lists['user'])) {
				// Expand the ticket group list
				// Reset the group
				if (isset($this->lists['project'][$ticket['project']]) && isset($this->lists['project'][$ticket['project']]['acl'][$key])) {
					$acl = $this->lists['project'][$ticket['project']]['acl'][$key];
					if ((($acl & ACL_MODIFY) && ($acl & ACL_READ)) || ($ticket['owner'] == $key) || ($ticket['creator'] == $key)) {
						array_push($ticket['validOwners'], $value);
					}
				}
			}
			return $ticket;
		}
		// Ticket not found
		return FALSE;

	}
	/* }}}2 */

	/* function getUser($us) {{{2
	   Returns an array that is a user in the DB. */
	function getUser($us) {
		global $adminUserGroup;

		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_users WHERE (username = '$us')");
		if (!$query->EOF) {
			$row = $query->fields;
			$row['user_group'] = explode(",", $row['user_group']);
			$row['user_projects'] = array();

			// Now we pull out the projects that this user can access.
			reset($this->lists['project']);
			while (list($key, $value) = each($this->lists['project'])) {
				// Split up the user_group
				if ($value['acl'][$row['uid']] & ACL_READ) {
					array_push($row['user_projects'], $value['id']);
				}
			}

			// Check for admin privs....
			if (in_array($adminUserGroup, $row['user_group']) || ($row['uid'] == 1)) {
				$row['isAdmin'] = 1;
			}
			else {
				$row['isAdmin'] = 0;
			}
			return $row;
		}
		else {
			buginError("Invalid User", 1);
		}
	}
	/* }}}2 */

	/* function getTemplates($langId) {{{2
		 Returns the templates for the given language ID. */
	function getTemplates($langId, $options = array()) {

		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_templates WHERE lang_id=".intval($langId));
		if ($query->EOF) {
			$options['templatesFromFile'] = 1;
		}
		else {
			$options['templatesFromFile'] = 0;
			while (!$query->EOF) {
				if ($row = $query->fields) {
					switch ($row['name']) {
						case 'notification':
							$options['notificationTemplate'] = $row['value'];
							break;
						case 'notify_subj':
							$options['notificationSubjectTemplate'] = $row['value'];
							break;
						case 'signup_email':
							$options['signupEmailTemplate'] = $row['value'];
							break;
					}
					$query->MoveNext();
				}
				else {
					$options['templatesFromFile'] = 1;
					break(2);
				}
			}
		}

		if ($options['templatesFromFile'] == 1) {
			$language = $this->lists['lang'][$langId]['name'];
			$ot = $options;
			require_once("{$options['buginBase']}conf/lang/email.{$language}.php");
			$options = array_merge($ot, $options);
		}
		return $options;
	}
	/* }}}2 */

	/* function getConfig($options) {{{2
	   Returns an array that is a user in the DB. */
	function getConfig($options) {
		if (!count($options)) {
			$noLangGet = 1;
		}
		// Assemble the array of config vars that are "arrays"
		$arrays = array("timeWarnStatusIgnore");

		$query = $this->db->Execute("SELECT * FROM {$this->tablePrefix}_config");

		while (!$query->EOF) {
			$row = $query->fields;
			$query->MoveNext();
			// Value always overwrites text...
			if (in_array($row['name'], $arrays))
				$options[$row['name']] = explode(",", $row['value']);
			else
				$options[$row['name']] = $row['value'];
		}

		if (!isset($noLangGet)) {
			$options = $this->getTemplates($options['defaultLanguage'], $options);
		}

		return $options;
	}

	function alterDB($update) {

		# Get the number of rows to be altered.
		$total = count($update);

		# Go through each update
		while (list($i, $value) = each($update)) {

			# Do we have a row match set?
			if (isset($value['rowmatches'])) {
				$rm = array();
				while (list($rowMatchKey, $rowMatchValue) = each($value['rowmatches'])) {
					$rm[] = "$rowMatchKey=$rowMatchValue";
				}
				$where = "WHERE ".implode(" AND ", $rm);
			}
			else {
				$where = "WHERE {$value['uniqueRow']}={$value['rowid']}";
			}


			switch ($value['action']) {
				case "add":
					# Don't forget to floss your variables!
					$addpart = array();
					$addpart['a'] = "";
					$addpart['b'] = "";

					#Go through the modifications
					while (list($ii, $modification) = each($value['modify'])) {
						
						# Put the strings together
						if (strlen($addpart['a'])) {
							$addpart['a'] .= ",";
							$addpart['b'] .= ",";
						}

						$addpart['a'] .= $value['modify'][$ii]['fieldName'];
						$addpart['b'] .= $value['modify'][$ii]['fieldValue'];

					}

					$addStmt = "INSERT INTO {$this->tablePrefix}_{$value['tableName']} ({$addpart['a']}) values({$addpart['b']});";

					// Execute the statement
					if (!$result = @$this->db->Execute($addStmt)) {
						buginError ("Error in executing <b>$addStmt</b>");
						return 0;
					}

					break;

			case "delete":

				$deleteStmt = "DELETE FROM {$this->tablePrefix}_{$value['tableName']} {$where}";

				// Execute the statement
				if (!$result = @$this->db->Execute($deleteStmt)) {
					buginError ("Error in executing <b>$deleteStmt</b>");
					return 0;
				}

				break;

			case "change":
				# Clean used variables
				$updatepart = "";

				while (list($ii, $modification) = each($value['modify'])) {
					if (strlen($updatepart)) {
						$updatepart .= ",";
					}
					$updatepart .= $value['modify'][$ii]['fieldName']."=".$value['modify'][$ii]['fieldValue'];
				}

				$updateStmt = "UPDATE {$this->tablePrefix}_{$value['tableName']} SET {$updatepart} {$where}";

				// Execute the Statement
				if (!$result = @$this->db->Execute($updateStmt)) {
					buginError ("Could not update the database. (<b>$updateStmt</b>)");
					return 0;
				}

				break;
			default:
				buginError("Action is wrong in alterDB");
				break;
		}	# end switch
	}
}

	/* }}}2 */
}

/* }}}1 */

?>
Return current item: BugIn and Ticketing System