201.php

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

		"ALTER TABLE `ticket_log` DROP INDEX techid",	
		"ALTER TABLE `ticket_log` ADD INDEX (`techid`) ",
		"ALTER TABLE `ticket_message` DROP INDEX date",	
		"ALTER TABLE `user` DROP INDEX username",	
		"ALTER TABLE `user` ADD INDEX (`username`) ",	
		"ALTER TABLE `user` DROP INDEX email",	
		"ALTER TABLE `user` ADD INDEX (`email`) ",	
		"ALTER TABLE `user` DROP INDEX awaiting_manual_validation",	
		"ALTER TABLE `user` ADD INDEX (`awaiting_manual_validation`) ",	
		"ALTER TABLE `user_bill` DROP INDEX ticketid",
		"ALTER TABLE `user_bill` DROP INDEX userid",	
		"ALTER TABLE `user_bill` ADD INDEX (`ticketid`, `userid`) ",	
		"ALTER TABLE `user_notes` DROP INDEX userid",	
		"ALTER TABLE `user_notes` ADD INDEX (`userid`, `techid`) ",	
		"ALTER TABLE `user_session` DROP INDEX userid",	
		"ALTER TABLE `user_session` ADD INDEX (`userid`) ",	
		"ALTER TABLE `query_log` DROP INDEX duration",
		"ALTER TABLE `query_log` ADD INDEX (`duration`) ",
		"ALTER TABLE `query_log` DROP INDEX stamp",
		"ALTER TABLE `query_log` ADD INDEX (`stamp`) "
	);
	
	execute($queries);
	do_message_yes();

}

############################################# STEP 6 #############################################

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

	$db->Halt_On_Error = 'yes';

	/*
		2.0.1 changes
	*/

	do_message("Delete orphaned templates");
	$db->query("DELETE FROM template_email WHERE (language = 0 AND category != 'Tech Emails')");
	$db->query("DELETE FROM template_email WHERE (category != 'User Emails' AND category != 'Tech Emails')");
	do_message_yes();

	do_message("Updating calendar timezone fields");

	$queries = array(
		"ALTER TABLE calendar_task CHANGE timezone timezone int(10) NOT NULL default '0'",
		"ALTER TABLE calendar_task CHANGE weekstart weekstart int(10) NOT NULL default '0'",
		"ALTER TABLE calendar_task CHANGE timezone_dst timezone_dst int(1) NOT NULL default '0'",
		"ALTER TABLE gateway_pop_accounts CHANGE target target varchar(64) NOT NULL default 'user'", 
		"ALTER TABLE ticket CHANGE authcode authcode varchar(250) NOT NULL default ''" 
	);

	execute($queries);
	do_message_yes();

	do_message("Updating tech & user timezone fields");

	$queries = array(
		"ALTER TABLE tech CHANGE timezone timezone int(10) NOT NULL default '0'",
		"ALTER TABLE tech CHANGE weekstart weekstart int(10) NOT NULL default '0'",
		"ALTER TABLE tech CHANGE timezone_dst timezone_dst int(1) NOT NULL default '0'",
		"ALTER TABLE user CHANGE timezone timezone int(10) NOT NULL default '0'",
		"ALTER TABLE user CHANGE timezone_dst timezone_dst int(1) NOT NULL default '0'",
		"ALTER TABLE template_email CHANGE language language int(10) NOT NULL default '0'"
	);

	execute($queries);
	do_message_yes();

	// we need to redo the languages for the new language import system
	do_message("Updating the languages");

	$result = $db->query_return("
		SELECT COUNT(*) AS total FROM languages WHERE id = 1 OR id = 2 OR id = 3 OR id = 4 OR id = 5
	");

	if ($result['total'] == 5) {

		$queries = array(
			"ALTER TABLE languages ADD installid int(10) NOT NULL default '0'",
			"UPDATE languages SET installid = 1 WHERE id = 1",
			"UPDATE languages SET installid = 2 WHERE id = 2",
			"UPDATE languages SET installid = 3 WHERE id = 3",
			"UPDATE languages SET installid = 4 WHERE id = 4",
			"UPDATE languages SET installid = 5 WHERE id = 5"
		);
	} else {

		$queries = array(
			"ALTER TABLE languages ADD installid int(10) NOT NULL default '0'",
			"DELETE FROM languages WHERE id = 1 OR id = 2 OR id = 3 OR id = 4 OR id = 5",
			"INSERT INTO languages VALUES (1,'English',1,0,'en','ISO-8859-1','ltr',1)",
			"INSERT INTO languages VALUES (2,'Spanish',1,0,'es','ISO-8859-1','ltr',2)",
			"INSERT INTO languages VALUES (3,'Hebrew',1,0,'he','ISO-8859-8-i','rtl',3)",
			"INSERT INTO languages VALUES (4,'Dutch',1,0,'nl','ISO-8859-1','ltr',4)",
			"INSERT INTO languages VALUES (5,'Danish',1,0,'da','ISO-8859-1','ltr',5)"
		);
	}

	$queries[] = "INSERT INTO languages VALUES (6, 'Russian', 1, 0, 'ru', 'ISO-8859-5', 'ltr', 6)";

	execute($queries);
	do_message_yes();

	// remove reports of too many templates from beta/rc releases
	do_message("Removing orphaned email templates");

	$db->query("DELETE FROM template_email WHERE !custom AND name NOT IN ('BODY_article_accepted', 'BODY_article_changed', 'BODY_article_deleted', 'BODY_category_updated', 'BODY_cc', 'BODY_changepass', 'BODY_comment_sent', 'BODY_comment_standard', 'BODY_email_warning', 'BODY_error_new', 'BODY_error_reply', 'BODY_faq_article_sent', 'BODY_newemail_confirm', 'BODY_newpass', 'BODY_newquestion', 'BODY_newquestion_tech', 'BODY_newquestion_validate', 'BODY_question_closed', 'BODY_question_reopened', 'BODY_question_staff_reply', 'BODY_question_user_reply', 'BODY_register', 'BODY_register_confirm', 'BODY_register_confirmed', 'BODY_register_confirmed_tech', 'BODY_register_manually_validated', 'BODY_register_tech', 'EMAIL_footer', 'TECHBODY_digest', 'TECHBODY_error_reply', 'TECHBODY_faq', 'TECHBODY_newpm', 'TECHBODY_newquestion', 'TECHBODY_newquestion_sms', 'TECHBODY_newtask', 'TECHBODY_newuser', 'TECHBODY_ownership', 'TECHBODY_reply', 'TECHBODY_reply_sms', 'TECHBODY_report', 'TECHBODY_task_reminder', 'TECHBODY_task_toggled')");
	
	$db->query("DELETE FROM template WHERE !custom AND name NOT IN ('ERROR_default', 'FAQ_article', 'FAQ_article_print', 'FAQ_category', 'FAQ_comment', 'FAQ_email', 'FAQ_header', 'FAQ_home', 'FAQ_new', 'FAQ_search', 'FAQ_subscriptions', 'FRONT_index', 'FRONT_news', 'HELP_content', 'HELP_footer', 'HF_css', 'HF_footer', 'HF_header', 'HF_header_small', 'HF_language', 'HTML_bordertable_end', 'HTML_bordertable_start', 'HTML_rowspacer', 'HTML_tablebitall', 'HTML_tablebit_end', 'HTML_tablebit_start', 'HTML_tableelement_end', 'HTML_tableelement_start', 'LIST_ticketlisting', 'NEW_expired', 'NEW_newticket', 'PROFILE_emails', 'PROFILE_header', 'PROFILE_login', 'PROFILE_password', 'PROFILE_profile', 'REDIRECT_standard_redirect', 'REG_register', 'VIEW_edit', 'VIEW_main', 'VIEW_print')");

	$languages = $db->query_return_array_id("SELECT * FROM languages", 'name');
	foreach ($languages AS $key => $var) {

		$db->query("
			SELECT COUNT(*) AS total, name 
			FROM template_email
			WHERE !backup
			AND language = '$key'
			GROUP BY name 
			HAVING total > 1
		");

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

			$db2->query("
				SELECT id 
				FROM template_email 
				WHERE name = '" . addslashes($result[name]) . "'
				AND language = '$key'
				AND !backup
			");

			unset($i);
			while ($result = $db2->row_array()) {

				if ($i) {
					$delete_ids[] = $result[id];
				} else {
					$i++;
				}
			}
		}
	}

	$db->query("DELETE FROM template_email WHERE id IN " . array2sql($delete_ids));

	do_message_yes();

}

############################################# STEP 7 #############################################

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

	$db->Halt_On_Error = 'yes';

	do_message("Updating tickets");

	$refs = array();
	$db->query("SELECT ref, COUNT(*) AS total FROM ticket GROUP BY ref HAVING total >= 2");
	while ($result = $db->row_array()) {
		$refs[] = $result['ref'];
	}

	// create a new temp column to indicate which need updating
	if ($db->num_rows()) {
		
		$db2->query("ALTER TABLE ticket ADD ref_temp int(1) NOT NULL DEFAULT '0'");
	
		foreach ($refs AS $key => $var) {

			$db->query("
				SELECT id, userid 
				FROM ticket 
				WHERE ref = '" . addslashes($var) . "'
				ORDER BY id ASC
			");
			
			while ($result = $db->row_array()) {

				// first ticket?
				if ($i) {
					$db2->query("UPDATE ticket SET ref_temp = '1' WHERE id = '$result[id]'");
				} else {
					$db2->query("UPDATE ticket SET ref_temp = '2' WHERE id = '$result[id]'");
				}
				$i++;
			}
		}

		$db->query("SELECT id FROM ticket WHERE ref_temp = '1'");
		while ($result = $db->row_array()) {
			$ids[] = $result['id'];
		}

		$db->query("SELECT id FROM ticket WHERE ref_temp = '2'");
		while ($result = $db->row_array()) {
			$ids_2[] = $result['id'];
		}

		foreach ($ids AS $key => $var) {

			$authcode = substr(md5(rand(0,100000) . mktime()), 0, 8);

			$db2->query("
				UPDATE ticket SET 
					ref = '" . make_ticket_ref() . "',
					authcode = '" . addslashes($authcode) . "'
				WHERE id = '$var'
			");
		}

		foreach ($ids_2 AS $key => $var) {
			
			$authcode = substr(md5(rand(0,100000) . mktime()), 0, 8);

			$db2->query("
				UPDATE ticket SET 
					authcode = '" . addslashes($authcode) . "'
				WHERE id = '$var'
			");
		}

		$db->query("ALTER TABLE ticket DROP ref_temp");
	}

	do_message_yes();

	do_message("Creating ticket index");

	$db->Halt_On_Error = 'no';
	$db->query("ALTER TABLE `ticket` DROP INDEX `ref` ");
	$db->Halt_On_Error = 'yes';
	$db->query("ALTER TABLE `ticket` ADD UNIQUE (`ref`)"); 
	
	do_message_yes();

}

############################################# STEP 8 #############################################

// give tickets an authcode
if ($_REQUEST['step'] == 8) {

	do_message("Setting ticket auth codes");

	$db->query("SELECT id FROM ticket WHERE !authcode");
	if ($db->num_rows()) {
		while ($res = $db->row_array()) {
			$process[] = $res['id'];
		}

		foreach($process AS $id) {
			$authcode = substr(md5(rand(0,100000) . mktime()), 0, 8);
			$db->query("UPDATE ticket SET authcode = '" . mysql_escape_string($authcode) . "' WHERE id = '$id'");
		}
	}
	do_message_yes();
}

############################################# STEP 9 #############################################

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

	$db->Halt_On_Error = 'yes';

	do_message("Updating version number to v2.0.1");

	$db->query("UPDATE settings SET value = '2.0.1' WHERE settings = 'deskpro_version'");

	do_message_yes();

	define('FINISHED', 1);

}

?>