upgrade-110-197.php

jsp程序开发系统

<?php
// +-------------------------------------------------------------+
// | DeskPRO v [2.0.1 Production]
// | Copyright (C) 2001 - 2004 Headstart Solutions Limited
// | Supplied by WTN-WDYL
// | Nullified by WTN-WDYL
// | Distribution via WebForum, ForumRU and associated file dumps
// +-------------------------------------------------------------+
// | DESKPRO IS NOT FREE SOFTWARE
// +-------------------------------------------------------------+
// | License ID : Full Enterprise License =) ...
// | License Owner : WTN-WDYL Team
// +-------------------------------------------------------------+
// | $RCSfile: upgrade-110-197.php,v $
// | $Date: 2004/02/12 21:16:57 $
// | $Revision: 1.8 $
// +-------------------------------------------------------------+
// | File Details:
// | - Worker script to upgrade from v1.1.0 to v2.0.0.
// +-------------------------------------------------------------+

error_reporting(E_ALL & ~E_NOTICE);
ob_implicit_flush();
install_check();

////////////////////////// CREATING TABLES //////////////////////////

if ($_REQUEST['step'] == 1) {

	do_message("Removing unused tables");
	$queries = array(
		"DROP TABLE gateway_error",
		"DROP TABLE template"
	);

	execute($queries);
	do_message_yes();

	do_message("Loading basic data");
	load_data('./../upgrade_v1_v2/upgrade_tables.sql');
	do_message_yes();

}

////////////////////////// UPDATE CATEGORIES, PRIORITIES //////////////////////////

if ($_REQUEST['step'] == 2) {

	do_message("Updating category table");

	$queries = array(
		"RENAME TABLE categories TO ticket_cat",
		"ALTER TABLE ticket_cat 
			ADD COLUMN user_select int(1) NOT NULL DEFAULT '0',
			ADD COLUMN show_category int(10) NOT NULL DEFAULT '0',
			ADD COLUMN require_registration int(1) NOT NULL DEFAULT '0',
			ADD COLUMN auto_assign_tech int(1) NOT NULL DEFAULT '0'",
		"UPDATE ticket_cat SET user_select = '1'"
	);

	execute($queries);
	do_message_yes();

	do_message("Updating priority table");

	$queries = array(
		"RENAME TABLE priority TO ticket_pri",
		"ALTER TABLE ticket_pri
			ADD COLUMN user_view int(1) NOT NULL DEFAULT '0',
			ADD COLUMN user_select int(1) NOT NULL DEFAULT '0',
			ADD COLUMN show_priority int(10) NOT NULL DEFAULT '0',
			ADD COLUMN require_registration int(1) NOT NULL DEFAULT '0',
			ADD COLUMN auto_assign_tech int(1) NOT NULL DEFAULT '0'",
		"UPDATE ticket_pri SET user_view = '1', user_select = '1'"
	);

	execute($queries);
	do_message_yes();

}

////////////////////////// E-MAIL BANS //////////////////////////

if ($_REQUEST['step'] == 3) {

	do_message("Updating banned e-mails");
	
	$bans = $db->query_return_array_id("SELECT * FROM email_ban", 'email');
	if (is_array($bans)) {
		$bans = array_values($bans);
		$db->query("INSERT INTO data SET name = 'email_ban', data = '" . mysql_escape_string(serialize($bans)) . "', isdefault = '1'");
	}
	$db->query("DROP TABLE email_ban");

	do_message_yes();

}

////////////////////////// GATEWAY ACCOUNTS //////////////////////////

if ($_REQUEST['step'] == 4) {

	do_message("Updating gateway accounts");

	$queries = array(
		"RENAME TABLE gateway TO gateway_accounts",
		"ALTER TABLE gateway_accounts CHANGE technician tech int(10) NOT NULL DEFAULT 0,
			ADD COLUMN is_default int(1) NOT NULL DEFAULT 0"
	);

	execute($queries);

	$gateway = $db->query_return("SELECT min(id) AS id FROM gateway_accounts");
	$db->query("UPDATE gateway_accounts SET is_default = '1' WHERE id = '$gateway[id]'");

	do_message_yes();

}

////////////////////////// ANNOUNCEMENTS //////////////////////////

if ($_REQUEST['step'] == 5) {
	
	do_message("Updating announcements");

	$queries = array(
		"RENAME TABLE announcements TO news",
		"ALTER TABLE news ADD COLUMN date int(10) NOT NULL DEFAULT 0,
			ADD COLUMN logged_in int(1) NOT NULL DEFAULT 0,
			ADD COLUMN logged_out int(1) NOT NULL DEFAULT 0",
		"UPDATE news SET date = unix_timestamp()",
		"UPDATE news SET logged_in = 1",
		"UPDATE news SET logged_out = 1"
	);

	execute($queries);

	do_message_yes();

}

////////////////////////// PRIVATE MESSAGES //////////////////////////

if ($_REQUEST['step'] == 6) {

	do_message("Create private message tables");

	$queries = array(
		"CREATE TABLE pm_relations (
			pmid int(10) NOT NULL DEFAULT 0, 
			techid int(10) NOT NULL DEFAULT 0, 
			is_read int(1) NOT NULL DEFAULT 0
		)",
		"CREATE TABLE pm_source (
			id int(10) NOT NULL AUTO_INCREMENT,
			fromid int(10) NOT NULL default '0', 
			title varchar(250) NOT NULL default '', 
			message mediumtext NOT NULL, 
			timestamp int(10) NOT NULL default '0',
PRIMARY KEY (id)
		)"
	);

	execute($queries);
	do_message_yes();

	$messages = $db->query_return_array("SELECT * FROM messages");

	do_message("Update private messatges");
	if (is_array($messages)) {
		foreach ($messages AS $message) {
			$db->query("INSERT INTO pm_source SET
				fromid = '$message[fromid]',
				title = '" . mysql_escape_string($message['subject']) . "',
				message = '" . mysql_escape_string($message['message']) . "',
				timestamp = '$message[send_date]'"
			);
			if($id = $db->last_id()) {
				$db->query("INSERT INTO pm_relations SET
					pmid = '$id',
					techid = '$message[toid]',
					is_read = '$message[is_read]'"
				);
			}
		}
	}

	$db->query("DROP TABLE messages");
	do_message_yes();

}

////////////////////////// UPDATE TECHS //////////////////////////

if ($_REQUEST['step'] == 7) {

	do_message("Getting technician data");

	$categories = $db->query_return_array_id("SELECT id FROM ticket_cat", 'id');

	$db->query("SELECT * FROM tech_cat");
	while ($data = $db->row_array()) {
		if ($data['admin_p']) {
			$tech_cats[$data['techid']][] = $data['catid'];
		}
	}

	if (is_array($tech_cats)) {
		foreach ($tech_cats AS $tech => $cats) {
			$tech_cats[$tech] = array_diff($categories, $cats);
		}
	}

	$tech_email = $db->query_return_array("SELECT * FROM tech_email");

	do_message_yes();
	do_message("Removing technician tables");

	$queries = array(
		"DROP TABLE tech_email",
		"CREATE TABLE tech_email ( 
			techid int(10) NOT NULL default '0', 
			fieldname varchar(250) NOT NULL default '', 
			value varchar(250) NOT NULL default '', 
			newreply int(1) NOT NULL default '0', 
			newticket int(1) NOT NULL default '0', 
			email int(1) NOT NULL default '0', 
			sms int(1) NOT NULL default '0', 
			KEY fieldname (fieldname,value)
		)",
		"DROP TABLE tech_sendmail",
		"RENAME TABLE tech_ticketwatch TO tech_ticket_watch",
		"ALTER TABLE tech_ticket_watch ADD COLUMN created int(10) DEFAULT '0',
			CHANGE COLUMN date_todo datetodo date DEFAULT '0000-00-00'"
	);

	execute($queries);
	do_message_yes();

	do_message("Updating tech email notifications");

	if (is_array($tech_email)) {
		foreach ($tech_email AS $tech_email) {
			if ($tech_email['cat_id']) {
				$data = "fieldname = 'category', value = '$tech_email[cat_id]'";
			} else {
				$data = "fieldname = 'priority', value = '$tech_email[pri_id]'";
			}
			if ($tech_email['email']) {
				$email = 1;
			} else {
				$email = 0;
			}
			$db->query("INSERT INTO tech_email SET
				techid = '$tech_email[tech_id]',
				newreply = '$tech_email[replys]',
				newticket = '$tech_email[tickets]',
				email = '$email',
				sms = '',
				$data"
			);
		}
	}

	do_message_yes();
	do_message("Updating the user table");

	// Now alter and update the actual tech table.

	$queries = array(
		"ALTER TABLE tech DROP COLUMN real_name,
			CHANGE COLUMN all_newticket email_new_email int(1) NOT NULL DEFAULT '0',
			CHANGE COLUMN all_replyticket email_reply_email int(1) NOT NULL DEFAULT '0',
			CHANGE COLUMN all_ownticket email_own_email int(1) NOT NULL DEFAULT '0',
			DROP COLUMN p_cat_control,
			ADD COLUMN fielddisplay MEDIUMTEXT NOT NULL DEFAULT '',
			ADD COLUMN alert_reply_your int(1) NOT NULL DEFAULT '1',
			ADD COLUMN alert_reply_cat int(1) NOT NULL DEFAULT '0',
			ADD COLUMN alert_reply_all int(1) NOT NULL DEFAULT '0',
			ADD COLUMN alert_new_cat int(1) NOT NULL DEFAULT '1',
			ADD COLUMN alert_new_all int(1) NOT NULL DEFAULT '0',
			ADD COLUMN alert_pm int(1) NOT NULL DEFAULT '0',
			ADD COLUMN alert_sound int(1) NOT NULL DEFAULT '0',
			ADD COLUMN alert_popup int(1) NOT NULL DEFAULT '0',
			ADD COLUMN alert_time int(10) NOT NULL DEFAULT '0',
			ADD COLUMN alert_frequency int(1) NOT NULL DEFAULT '0',
			ADD COLUMN cats_user varchar(255) NOT NULL DEFAULT '',
			ADD COLUMN cats_admin varchar(255) NOT NULL DEFAULT '',
			ADD COLUMN email_new_sms int(1) NOT NULL DEFAULT '0',
			ADD COLUMN email_reply_sms int(1) NOT NULL DEFAULT '0',
			ADD COLUMN email_own_sms int(1) NOT NULL DEFAULT '0',
			ADD COLUMN sms varchar(255) NOT NULL DEFAULT '',
			ADD COLUMN faq_editor_yes int(1) NOT NULL DEFAULT '0',
			ADD COLUMN faq_editor_no int(1) NOT NULL DEFAULT '0',
			ADD COLUMN disabled int(1) NOT NULL DEFAULT '0',
			ADD COLUMN email_assigned int(1) NOT NULL DEFAULT '0',
			ADD COLUMN email_pm int(1) NOT NULL DEFAULT '0',
			ADD COLUMN weekstart int(11) NOT NULL DEFAULT '0',
			ADD COLUMN p_approve_new_registrations int(1) NOT NULL DEFAULT '1',
			ADD COLUMN password_cookie varchar(8) NOT NULL DEFAULT '',
			ADD COLUMN disabled_reason varchar(255) NOT NULL DEFAULT '',
			ADD COLUMN email_attachments int(1) NOT NULL DEFAULT '0',
			ADD COLUMN email_own_attachments int(1) NOT NULL DEFAULT '0',
			ADD COLUMN copy_to_clipboard int(1) NOT NULL DEFAULT '0',
			ADD COLUMN p_user_expire int(1) NOT NULL DEFAULT '1',
			ADD COLUMN selected_sound varchar(255) NOT NULL DEFAULT '',
			ADD COLUMN p_quickedit int(1) NOT NULL DEFAULT '1',
			ADD COLUMN p_global_note int(1) NOT NULL DEFAULT '1',
			ADD COLUMN footer int(10) NOT NULL DEFAULT '0'",
		"UPDATE tech SET password_cookie = md5('username' + rand())"
	);

	execute($queries);

	do_message_yes();
	do_message("Updating tech restricted categories");

	$techs = $db->query_return_array_id("SELECT id FROM tech");

	if (is_array($techs)) {
		foreach($techs AS $tech) {
			if (is_array($tech_cats[$tech['id']])) {
				$db->query("UPDATE tech SET cats_admin = '" . join(',', $tech_cats[$tech['id']]) . "' WHERE id = '$tech[id]'");
			}
		}
	}

	do_message_yes();

}

////////////////////////// UPDATE TASKS //////////////////////////

if ($_REQUEST['step'] == 8) {

	do_message("Updating calendar tables");

	$tasks = $db->query_return_array("SELECT * FROM tech_todo");

	$queries = array(
		"DROP TABLE tech_todo",
		"CREATE TABLE calendar_task ( 
			id int(10) NOT NULL auto_increment, 
			title varchar(250) NOT NULL default '', 
			description mediumtext NOT NULL, 
			techmaker int(10) NOT NULL default '0', 
			multistaff int(1) NOT NULL default '0', 
			globalcomplete int(1) NOT NULL default '0', 
			notifycompletion int(1) NOT NULL default '0', 
			repeattype int(1) NOT NULL default '0', 
			value1 int(10) NOT NULL default '0', 
			value2 varchar(250) NOT NULL default '0', 
			starttime time NOT NULL default '00:00:00', 
			startdate date NOT NULL default '0000-00-00', 
			enddate date NOT NULL default '0000-00-00', 
			endtime time NOT NULL default '00:00:00', 
			PRIMARY KEY  (id), 
			UNIQUE KEY id (id), 
			KEY repeattype (repeattype), 
			KEY startdate (startdate), 
			KEY enddate (enddate))",
		"CREATE TABLE calendar_task_tech ( 
			id int(10) NOT NULL auto_increment, 
			eventid int(10) NOT NULL default '0', 
			email_due int(1) NOT NULL default '0', 
			email_before1 int(3) NOT NULL default '0', 
			email_before2 int(3) NOT NULL default '0', 
			techid int(1) NOT NULL default '0', 
			completed int(1) NOT NULL default '0', 
			stamp int(10) default '0', 
			PRIMARY KEY  (id), 
			KEY eventid (eventid))"
	);

	execute($queries);

	do_message_yes();
	do_message("Updating calendar tasks");

	if (is_array($tasks)) {
		foreach ($tasks AS $task) {
			$db->query("INSERT INTO calendar_task SET
				title = '" . mysql_escape_string($task['title']) . "',
				description = '" . mysql_escape_string($task['todo']) . "',
				startdate = '" . date('Y-m-d', $task['date_added']) . "',
				enddate = '" . mysql_escape_string($task['date_todo']) . "',
				techmaker = '" . mysql_escape_string($task['techid']) . "'"
			);
			$id = $db->last_id();
			$db->query("INSERT INTO calendar_task_tech SET
				eventid = '$id',
				techid = '" . mysql_escape_string($task['techid']) . "',
				completed = '" . mysql_escape_string($task['completed']) . "',
				stamp = '" . mysql_escape_string($task['date_added']) . "'"
			);
		}
	}

	do_message_yes();
}

////////////////////////// CUSTOM FIELDS //////////////////////////

if ($_REQUEST['step'] == 9) {

	do_message("Updating user custom field table");

	$queries = array(
		"RENAME TABLE user_table TO user_def",
		"UPDATE user_def SET formtype = 'input' WHERE formtype = 'textfield'",
		"ALTER TABLE user_def CHANGE reg_ex regex varchar(255),
			CHANGE formlength length int(10),
			CHANGE rows height int(10),
			CHANGE field_order displayorder int(10),
			CHANGE description description mediumtext,
			CHANGE display_name display_name mediumtext,
			CHANGE parse_default_value parsed_default_value varchar(255),
			DROP COLUMN sql_type,
			DROP COLUMN listvalues,
			DROP COLUMN admin_editable,
			CHANGE formtype formtype enum('input','select','textarea','multiselect','radio','checkbox','system') DEFAULT 'select',
			ADD COLUMN data mediumtext,
			ADD COLUMN extrainput int(1) DEFAULT '0',
			ADD COLUMN maxoptions smallint(4) DEFAULT '0',
			ADD COLUMN minoptions smallint(4) DEFAULT '0',
			ADD COLUMN minlength smallint(6) DEFAULT '0',
			ADD COLUMN maxlength smallint(6) DEFAULT '0',
			ADD COLUMN error_message varchar(255),
			ADD COLUMN required int(1) DEFAULT '0',
			ADD COLUMN perline int(2) DEFAULT '0',
			ADD COLUMN extrainput_location int(1) DEFAULT '0',
			ADD COLUMN extrainput_text varchar(255) DEFAULT '0',
			ADD COLUMN multiselect int(1) DEFAULT '0'",
		"DELETE FROM user_def WHERE name NOT like '%custom%'"
	);

	execute($queries);

	do_message_yes();
	do_message("Updating user custom fields");

	$user_customs = $db->query_return_array("SELECT * FROM user_def WHERE name like '%custom%'");

	if (is_array($user_customs)) {
		foreach ($user_customs AS $user_custom) {
			if ($user_custom['formtype'] == 'radio' or $user_custom['formtype'] == 'select') {
				// We have to load the data array
				$values = explode('###', $user_custom['listvalues']);
				if (is_array($values)) {
					$i = 0;
					$data = array();
					foreach($values AS $value) {
						$data[] = array($i, $i, $value, 0);
					}
				}
			}
			$data = mysql_escape_string(serialize($data));
			$display_name = serialize(array('1' => $user_custom['display_name']));
			$description = serialize(array('1' => $user_custom['description']));
			$error_message = serialize(array('1' => ''));
			$display_name = mysql_escape_string($display_name);
			$error_message = mysql_escape_string($error_message);
			$description = mysql_escape_string($description);
			$db->query("UPDATE user_def SET
				data = '$data',
				display_name = '$display_name',
				error_message = '$error_message',
				description = '$description'
				WHERE id = '$user_custom[id]'"
			);
		}
	}