<?php
/* SYSKEYS $Id: do_sysval_aed.php,v 1.4 2003/04/24 19:08:27 eddieajau Exp $ */
$del = isset($_POST['del']) ? $_POST['del'] : 0;
$obj = new CSysVal();
if (!$obj->bind($_POST)) {
$AppUI->setMsg($obj->getError(), UI_MSG_ERROR);
$AppUI->redirect();
}
$AppUI->setMsg("System Lookup Values", UI_MSG_ALERT);
if ($del) {
if ($msg = $obj->delete()) {
$AppUI->setMsg($msg, UI_MSG_ERROR);
} else {
$AppUI->setMsg("deleted", UI_MSG_ALERT, true);
}
} else {
if ($msg = $obj->store()) {
$AppUI->setMsg($msg, UI_MSG_ERROR);
} else {
$AppUI->setMsg(@$_POST['sysval_id'] ? 'updated' : 'inserted', UI_MSG_OK, true);
}
}
$AppUI->redirect("m=system&u=syskeys");
function upgrade($old_version)
{
$success = 1;
switch ($old_version) {
case "0.1":
// Drop unused columns, add some new columns
$bulk_sql[] = "\n ALTER TABLE `helpdesk_items`\n ADD `item_requestor_phone` varchar(30) NOT NULL default '' AFTER `item_requestor_email`,\n ADD `item_company_id` int(11) NOT NULL default '0' AFTER `item_project_id`,\n ADD `item_requestor_type` tinyint NOT NULL default '0' AFTER `item_requestor_phone`,\n ADD `item_notify` int(1) DEFAULT '1' NOT NULL AFTER `item_assigned_to`,\n ADD `item_created_by` int(11) NOT NULL default '0',\n\t\t ADD `item_updated` datetime default NULL,\n DROP `item_receipt_target`,\n DROP `item_receipt_custom`,\n DROP `item_receipted`,\n DROP `item_resolve_target`,\n DROP `item_resolve_custom`,\n DROP `item_resolved`,\n DROP `item_assetno`\n ";
// Add help desk item id to task log table
$bulk_sql[] = "\n ALTER TABLE `task_log`\n ADD `task_log_help_desk_id` int(11) NOT NULL default '0' AFTER `task_log_task`\n ";
// Add help desk item status log table
$bulk_sql[] = "\n CREATE TABLE `helpdesk_item_status` (\n `status_id` INT NOT NULL AUTO_INCREMENT,\n `status_item_id` INT NOT NULL,\n `status_code` TINYINT NOT NULL,\n `status_date` TIMESTAMP NOT NULL,\n `status_modified_by` INT NOT NULL,\n `status_comment` TEXT DEFAULT '',\n PRIMARY KEY (`status_id`)\n )\n ";
// Execute the above SQL
foreach ($bulk_sql as $s) {
db_exec($s);
if (db_error()) {
$success = 0;
}
}
// Add audit trail to system values
$sql = "SELECT syskey_id\n FROM syskeys\n WHERE syskey_name = 'HelpDeskList'";
$syskey_id = db_loadResult($sql);
$sv = new CSysVal($syskey_id, 'HelpDeskAuditTrail', "0|Created\n1|Title\n2|Requestor Name\n3|Requestor E-mail\n4|Requestor Phone\n5|Assigned To\n6|Notify by e-mail\n7|Company\n8|Project\n9|Call Type\n10|Call Source\n11|Status\n12|Priority\n13|Severity\n14|Operating System\n15|Application\n16|Summary\n17|Deleted");
$sv->store();
// Update help desk status values
$sql = "UPDATE sysvals\n SET sysval_value='0|Unassigned\n1|Open\n2|Closed\n3|On Hold\n4|Testing'\n WHERE sysval_title='HelpDeskStatus'\n LIMIT 1";
db_exec($sql);
/* Get data for conversion update */
$sql = "SELECT item_id,item_requestor_id,item_created,item_project_id\n FROM helpdesk_items";
$items = db_loadList($sql);
/* Populate the status log table with the item's creation date */
foreach ($items as $item) {
$timestamp = date('Ymdhis', db_dateTime2unix($item['item_created']));
$sql = "INSERT INTO helpdesk_item_status\n (status_item_id,status_code,status_date,status_modified_by)\n VALUES ({$item['item_id']},0,'{$timestamp}',\n {$item['item_requestor_id']})";
db_exec($sql);
}
/* Figure out the company for each item based on project id or based
on requestor id */
foreach ($items as $item) {
if ($item['item_project_id']) {
$sql = "SELECT project_company\n FROM projects\n WHERE project_id='{$item['item_project_id']}'";
$company_id = db_loadResult($sql);
} else {
if ($item['item_requestor_id']) {
$sql = "SELECT user_company\n FROM users\n WHERE user_id='{$item['item_requestor_id']}'";
$company_id = db_loadResult($sql);
}
}
if ($company_id) {
$sql = "UPDATE helpdesk_items\n SET item_company_id='{$company_id}'\n WHERE item_id='{$item['item_id']}'";
db_exec($sql);
}
}
// If our status was 5 (Testing), now it is 4 (Testing)
$sql = "UPDATE helpdesk_items\n SET item_status='4'\n WHERE item_status='5'";
db_exec($sql);
break;
case 0.2:
// Version 0.3 features new permissions
$success = 1;
break;
case 0.3:
// Version 0.31 includes new watchers functionality
$sql = "\n\t\tCREATE TABLE helpdesk_item_watchers (\n\t\t `item_id` int(11) NOT NULL default '0',\n\t\t `user_id` int(11) NOT NULL default '0',\n\t\t `notify` char(1) NOT NULL default ''\n\t\t) TYPE=MyISAM";
db_exec($sql);
case 0.31:
$sql = "\n ALTER TABLE `helpdesk_items`\n\t\t ADD `item_updated` datetime default NULL\n ";
db_exec($sql);
$sql = "SELECT `item_id` FROM helpdesk_items";
$rows = db_loadList($sql);
$sql = '';
foreach ($rows as $row) {
$sql = "SELECT MAX(status_date) status_date FROM helpdesk_item_status WHERE status_item_id =" . $row['item_id'];
$sdrow = db_loadList($sql);
$sql = '';
$sql = "UPDATE `helpdesk_items`\n \t \tSET `item_updated`='" . $sdrow[0]['status_date'] . "' \n \t \tWHERE `item_id`=" . $row['item_id'];
db_exec($sql);
}
if (db_error()) {
$success = 0;
} else {
$success = 1;
}
break;
default:
$success = 0;
}
// NOTE: Need to return true, not null, if all is good
return $success;
}