<?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 */
?>