upgrade-110-197.php

本代码是为客户联系管理而做的系统

	do_message_yes();

}

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

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

	do_message("Updating ticket custom field table");

	$queries = array(
		"RENAME TABLE ticket_table TO ticket_def",
		"UPDATE ticket_def SET formtype = 'input' WHERE formtype = 'textfield'",
		"ALTER TABLE ticket_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 user_start ticket_start int(1),
			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 ticket_def WHERE name NOT like '%custom%'"
	);

	execute($queries);
	do_message_yes();
	do_message("Updating ticket custom fields");

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

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

	do_message_yes();
}

////////////////////////// USER TABLE //////////////////////////

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

	do_message("Updating ticket custom fields");

	$users = $db->query_return_array("SELECT * FROM user");
	$totusers = $db->num_rows();

	$queries = array(
		"CREATE TABLE user2 ( 
			id int(10) unsigned NOT NULL auto_increment, 
			username varchar(250) NOT NULL default '', 
			email varchar(250) NOT NULL default '', 
			validate_key varchar(6) NOT NULL default '0', 
			awaiting_validation int(1) NOT NULL default '0', 
			password varchar(250) NOT NULL default '', 
			date_registered int(10) NOT NULL default '0', 
			language int(10) NOT NULL default '0', 
			password_cookie varchar(8) NOT NULL default '', 
			password_url varchar(8) NOT NULL default '', 
			autoresponds int(1) NOT NULL default '0', 
			disabled int(1) NOT NULL default '0', 
			awaiting_manual_validation int(1) NOT NULL default '0', 
			expire_tickets int(11) default NULL, 
			expire_date int(10) unsigned default NULL, 
			expire_type enum('none','ticket','date','both') default 'none', 
			disabled_reason varchar(255) default NULL, 
			timezone varchar(32) DEFAULT '', 
			PRIMARY KEY  (id), 
			UNIQUE KEY id (id)) 
		TYPE=MyISAM;");

	execute($queries);

	$user_fields = $db->query_return_array("SELECT name FROM user_def");
	if (is_array($user_fields)) {
		foreach ($user_fields AS $field) {
			$db->query("ALTER TABLE user2 ADD $field[name] MEDIUMTEXT");
		}
	}

	if (is_array($users)) {
		$data = array();
		$usernames = array();
		$total = 0;
		foreach ($users AS $user) {
			$hash = md5($username . mktime() . rand());
			$hash = mysql_escape_string(substr($hash, 0, 8));
			$username = make_username($user['email'], NULL, 1);
			$i = 0;
			if (in_array($username, $usernames)) {
				$i++;
				while (in_array(($username . $i), $usernames)) {
					$i++;
				}
				$username = ($username . $i);
			}
			$usernames[] = $username;
			$data_tmp = array(
				$user['id'], # ID
				$username, # Username
				$user['email'], # Email
				$user['validate_number'], # Validate_key
				0, # awaiting_validation
				$user['password'], # password
				mktime(), # date_registered
				0, # language
				$hash, # password_cookie
				$hash, # password_url
				0, # autoresponds
				0, # disabled
				0, # awaiting_manual_validation
				NULL, # expire_tickets
				NULL, # expire_date
				'none', # expire_type
				NULL, # disabled_reason
				''); # timezone

			if (is_array($user_fields)) {
				foreach ($user_fields AS $field) {
					$data_tmp[] = $user[$field['name']];
				}
			}
			$data[] = $data_tmp;
			$count++;
			if ($count >= 500) {
				$db->query("INSERT INTO user2 VALUES " . multi_array2sql($data));
				unset($data);
				$total += $count;
				$count = 0;
			}
		}
		if ($count) {
			$db->query("INSERT INTO user2 VALUES " . multi_array2sql($data));
		}
	}
	$db->query("DROP TABLE user");
	$db->query("RENAME TABLE user2 TO user");

	do_message_yes();

}

////////////////////////// TICKET TABLE //////////////////////////

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

	do_message("Updating ticket table");

	$queries = array(
		"ALTER TABLE ticket
			CHANGE COLUMN user_id userid int(10) NOT NULL default '0', 
			CHANGE COLUMN admin_owner tech int(10) NOT NULL default '0', 
			ADD COLUMN language int(10) NOT NULL default '0', 
			CHANGE COLUMN priority priority int(10) NOT NULL default '0',
			CHANGE COLUMN category category int(10) NOT NULL default '0', 	
			CHANGE COLUMN awaiting_reply awaiting_tech int(1) NOT NULL default '0', 
			CHANGE COLUMN date_started date_opened int(10) NOT NULL default '0', 
			CHANGE COLUMN last_reply date_lastreply int(10) NOT NULL default '0', 
			ADD COLUMN date_lastreply_tech int(10) NOT NULL default '0', 
			CHANGE COLUMN lock_id lock_techid int(10) NOT NULL default '0', 
			CHANGE COLUMN onhold_date date_locked int(10) NOT NULL default '0', 
			ADD COLUMN ref varchar(20) NOT NULL default '', 
			ADD COLUMN gatewayid int(10) NOT NULL default '0', 
			ADD COLUMN ticketemail varchar(250) NOT NULL default '', 
			ADD COLUMN nodisplay int(1) NOT NULL default '0', 
			ADD COLUMN date_awaiting_toggled INT(10) NOT NULL DEFAULT '0', 
			CHANGE COLUMN ticket_pass authcode varchar(8) NOT NULL default '',
			DROP COLUMN gateway,
			DROP COLUMN is_email,
			ADD KEY userid (userid), 
			ADD KEY techid (tech), 
			ADD KEY category (category), 
			ADD KEY priority (priority), 
			ADD KEY ref (ref), 
			ADD KEY is_open (is_open), 
			ADD KEY awaiting_tech (awaiting_tech), 
			ADD KEY nodisplay (nodisplay)
		",
        "RENAME TABLE reply TO ticket_message",
        "ALTER TABLE ticket_message
            CHANGE COLUMN admin_id techid int(1) NOT NULL DEFAULT '0',
            ADD COLUMN sourceid int(10) NOT NULL DEFAULT '0',
            ADD COLUMN userid int(10) NOT NULL DEFAULT '0',
            ADD COLUMN striptags int(1) NOT NULL DEFAULT '0'",
        "CREATE INDEX techid ON ticket_message (techid)"
	);

	execute($queries);

	do_message_yes();

}

////////////////////////// TICKETS //////////////////////////

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

	$result = $db->query_return("SELECT COUNT(*) AS total FROM ticket WHERE !ref");
	$total = $result['total'];

	if ($total < 300) {
		do_message("Updating $total tickets");
	} else {
		$left = $total - 300;
		do_message("Updating 1,000 tickets ($left remaining)");
	}

	$db->query("SELECT id FROM ticket WHERE !ref LIMIT 300");
	if ($db->num_rows()) {
		while ($res = $db->row_array()) {
			$process[] = $res['id'];
		}
		foreach($process AS $id) {
			$db->query("UPDATE ticket SET ref = '" . mysql_escape_string(make_ticket_ref()) . "' WHERE id = '$id'");
		}
	}
	do_message_yes();

	if ($left) {

		define('CUSTOMDIRECT', 1);
		$step = 13;
		$extra_variables = '';

	}
}

////////////////////////// UPDATE ATTACHMENTS //////////////////////////

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

	do_message("Create & Alter attachment tables");

	$queries = array(
		"CREATE TABLE faq_attachments ( 
			id int(10) NOT NULL auto_increment, 
			blobid int(10) NOT NULL default '0', 
			filename varchar(255) NOT NULL default '0', 
			filesize varchar(255) NOT NULL default '0', 
			extension varchar(10) NOT NULL default '0', 
			articleid int(10) NOT NULL default '0', 
			techid int(10) NOT NULL default '0', 
			timestamp int(10) NOT NULL default '0', 
			PRIMARY KEY  (id), 
			UNIQUE KEY id (id), 
			KEY articleid (articleid))",
		"CREATE TABLE tech_attachments ( 
			id int(10) NOT NULL auto_increment, 
			blobid int(10) NOT NULL default '0', 
			filename varchar(250) NOT NULL default '', 
			filesize varchar(50) NOT NULL default '', 
			techid int(10) NOT NULL default '0', 
			category int(10) NOT NULL default '0', 
			extension varchar(10) NOT NULL default '', 
			timestamp int(10) NOT NULL default '0', 
			comments mediumtext NOT NULL, 
			PRIMARY KEY  (id), 
			UNIQUE KEY id (id))",
		"CREATE TABLE blobs ( 
			id int(10) unsigned NOT NULL auto_increment, 
			blobdata longblob NOT NULL, 
			PRIMARY KEY  (id), 
			UNIQUE KEY id (id))",
		"RENAME TABLE attachments TO ticket_attachments",
		"ALTER TABLE ticket_attachments
			DROP COLUMN description,
			DROP COLUMN uploaded,
			CHANGE COLUMN filetype extension varchar(5) NOT NULL DEFAULT '0',
			ADD COLUMN blobid int(10) NOT NULL DEFAULT '0',
			ADD COLUMN techid int(10) NOT NULL DEFAULT '0',
			ADD COLUMN userid int(10) NOT NULL DEFAULT '0',
			ADD COLUMN temporaryid int(10) NOT NULL DEFAULT '0',
			ADD COLUMN timestamp int(10) NOT NULL DEFAULT '0',
			ADD COLUMN toemail int(1) NOT NULL DEFAULT '0'"
	);

	execute($queries);

	do_message_yes();

}
	
////////////////////////// UPDATE ATTACHMENT DATA //////////////////////////

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

	$result = $db->query_return("SELECT COUNT(*) AS total FROM ticket_attachments WHERE !blobid");
	$total = $result['total'];

	if ($total < 25) {
		do_message("Updating $total attachments");
	} else {
		$left = $total - 25;
		do_message("Updating 25 attachments ($left remaining)");
	}

	$db->query("SELECT * FROM ticket_attachments WHERE !blobid LIMIT 25");

	while ($result = $db->row_array()) {

		$db2->query("
		INSERT INTO blobs SET
			blobdata  = '" . mysql_escape_string($result['attachment']) . "'
		");

		$id = $db2->last_id();
		
		$db2->query("
			UPDATE ticket_attachments SET
			blobid = '" . $id . "',
			extension = '" . addslashes(attachment_extension($result[filename])) . "',
			attachment = ''
			WHERE id = '$result[id]'
		");
	}

	do_message_yes();

	if ($left) {

		define('CUSTOMDIRECT', 1);
		$step = 15;
		$extra_variables = '';

	} else {

		do_message("Dropping old attachment fields");
		$db->query("ALTER TABLE ticket_attachments DROP attachment");
		do_message_yes();

	}
}

////////////////////////// UPDATE KNOWLEDGE BASE //////////////////////////

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

	do_message("Updating knowledge base");

	$queries = array(
		"RENAME TABLE kb_cats TO faq_cats",
		"ALTER TABLE faq_cats
			CHANGE COLUMN article_number totalarticles int(10),
			ADD COLUMN articles int(10) NOT NULL DEFAULT '0',
			ADD COLUMN p_loggedin int(1) NOT NULL DEFAULT '0',
			ADD COLUMN p_restricted int(1) NOT NULL DEFAULT '0',
			ADD COLUMN parentlist varchar(250) NOT NULL DEFAULT '',
			ADD COLUMN newdate int(10) NOT NULL DEFAULT '0',
			ADD COLUMN editdate int(10) NOT NULL DEFAULT '0'",
		"UPDATE faq_cats SET newdate = unix_timestamp(), editdate = unix_timestamp()",
		"RENAME TABLE kb_articles TO faq_articles",
		"ALTER TABLE faq_articles
			CHANGE COLUMN made_by techid_made int(10) NOT NULL DEFAULT '0',
			CHANGE COLUMN modified_by techid_modified int(10) NOT NULL DEFAULT '0',
			CHANGE COLUMN category_id category int(10) NOT NULL DEFAULT '0',
			ADD COLUMN show_order int(10) NOT NULL DEFAULT '0',
			ADD COLUMN to_validate int(10) NOT NULL DEFAULT '0',
			ADD COLUMN keywords mediumtext NOT NULL DEFAULT '',
			ADD COLUMN userid int(10) NOT NULL DEFAULT '0',
			ADD COLUMN question_html int(1) NOT NULL DEFAULT '0',
			ADD COLUMN answer_html int(1) NOT NULL DEFAULT '0',
			ADD COLUMN rating int(10) NOT NULL DEFAULT '0',
			ADD COLUMN votes int(10) NOT NULL DEFAULT '0',
			ADD COLUMN ref varchar(20) NOT NULL DEFAULT ''",
		"RENAME TABLE kb_related TO faq_articles_related"
	);

	execute($queries);

	do_message_yes();
	do_message("Updating knowledge base articles");

	$faqs = $db->query_return_array_id("SELECT id FROM faq_articles", 'id');
	if (is_array($faqs)) {
		foreach ($faqs AS $id) {
			$db->query("UPDATE faq_articles SET ref = '" . make_ticket_ref('faq_articles') . "' WHERE id = $id");
		}
	}

	require_once('./../tech/faq/faq_include.php');

	$db->query("SELECT keywords, id FROM faq_articles");
	while ($result = $db->row_array()) {
		$words = explode(',', $result['keywords']);
		if (@is_array($words)) {
			foreach($words AS $key => $var) {
				if (trim($var) != '') {
					if ($data[$var]) {
						$data[$var] .= ',' . $result['id'];
					} else {
						$data[$var] = $result['id'];
					}
				}
			}
		}
	}

	$db->query("DELETE FROM faq_keywords");
	if (is_array($data)) {
		$db->query("INSERT INTO faq_keywords (word, articles) VALUES " . insertsql($data) . "");
	}

	do_message_yes();
	do_message("Updating knowledge base counters");

	update_counters();
	update_parentlist();

	do_message_yes();

	do_message("Updating version number to v2.0.0");
	$db->query("UPDATE settings SET value = '2.0.0' WHERE settings = 'deskpro_version'");
	do_message_yes();

}

////////////////////////// UPDATE KNOWLEDGE BASE //////////////////////////

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

	do_message("Loading Installation Data");
	load_data('./../upgrade_v1_v2/upgrade_data.sql');
	do_message_yes();

	define('FINISHED', 1);

}

?>