function up_5402_6000($_prefix, $_link, $_engine)
{
$commands[] = array(1060, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "resources` ADD `languages` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',ADD `kb_public` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0',ADD `kb_bot` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0',ADD `kb_ft_search` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0',ADD INDEX ( `languages` , `kb_public` ) ");
$commands[] = array(1060, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "resources` ADD `shortcut_word` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';");
$commands[] = array(1060, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_browsers` ADD `data_id` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' AFTER `visit_id`;");
$commands[] = array(1054, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "data_updates` CHANGE `update_ratings` `update_feedbacks` BIGINT( 20 ) UNSIGNED NOT NULL DEFAULT '0';");
$commands[] = array(1060, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "administration_log` ADD `trace` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER `value`;");
$commands[] = array(1060, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "events` ADD `exclude_mobile` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0';");
$commands[] = array(1060, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "events` ADD `exclude_countries` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';");
$commands[] = array(1050, "CREATE TABLE IF NOT EXISTS `" . DBManager::RealEscape($_prefix) . "user_data` (`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',`create` int(10) unsigned NOT NULL DEFAULT '0',`h_fullname` varchar(254) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',`h_email` varchar(254) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',`h_company` varchar(254) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',`h_phone` varchar(254) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',`h_customs` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`h_text` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,PRIMARY KEY (`id`)) ENGINE=" . $_engine . " DEFAULT CHARSET=utf8 COLLATE=utf8_bin;");
$commands[] = array(1050, "CREATE TABLE IF NOT EXISTS `" . DBManager::RealEscape($_prefix) . "feedbacks` (`id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`created` int(10) unsigned NOT NULL DEFAULT '0',`chat_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`ticket_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`resource_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`user_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`operator_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`group_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`data_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`ip_hash` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',PRIMARY KEY (`id`)) ENGINE=" . $_engine . " DEFAULT CHARSET=utf8 COLLATE=utf8_bin;");
$commands[] = array(1050, "CREATE TABLE IF NOT EXISTS `" . DBManager::RealEscape($_prefix) . "feedback_criteria` (`fid` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`cid` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`value` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '',PRIMARY KEY (`fid`,`cid`)) ENGINE=" . $_engine . " DEFAULT CHARSET=utf8 COLLATE=utf8_bin;");
$commands[] = array(1050, "CREATE TABLE IF NOT EXISTS `" . DBManager::RealEscape($_prefix) . "feedback_criteria_config` (`id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`type` tinyint(1) unsigned NOT NULL DEFAULT '0',`name` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`title` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',PRIMARY KEY (`id`)) ENGINE=" . $_engine . " DEFAULT CHARSET=utf8 COLLATE=utf8_bin;");
$commands[] = array(1050, "CREATE TABLE IF NOT EXISTS `" . DBManager::RealEscape($_prefix) . "stats_aggs_feedbacks` (`year` smallint(5) unsigned NOT NULL DEFAULT '0',`month` tinyint(3) unsigned NOT NULL DEFAULT '0',`day` tinyint(3) unsigned NOT NULL DEFAULT '0',`operator_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`group_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',`amount` int(10) unsigned NOT NULL DEFAULT '0',`ca` double unsigned NOT NULL DEFAULT '0',`cb` double unsigned NOT NULL DEFAULT '0',`cc` double unsigned NOT NULL DEFAULT '0',`cd` double unsigned NOT NULL DEFAULT '0',PRIMARY KEY (`year`,`month`,`day`,`operator_id`,`group_id`)) ENGINE=" . $_engine . " DEFAULT CHARSET=utf8 COLLATE=utf8_bin;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "resources` DROP `rank`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_chats` DROP `fullname`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_chats` DROP `email`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_chats` DROP `company`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_chats` DROP `phone`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_chats` DROP `customs`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_chats` DROP `question`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_browsers` DROP `fullname`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_browsers` DROP `email`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_browsers` DROP `company`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_browsers` DROP `phone`;");
$commands[] = array(1091, "ALTER TABLE `" . DBManager::RealEscape($_prefix) . "visitor_browsers` DROP `customs`;");
$res = processCommandList($commands, $_link);
// import ratings
$_link->Query(true, "INSERT INTO `" . DBManager::RealEscape($_prefix) . "feedback_criteria_config` (`id`, `type`, `name`, `title`) VALUES ('d0', 0, 'Knowledge', '<!--lang_client_feedback_knowledge-->'),('d1', 0, 'Friendlyness', '<!--lang_client_feedback_friendliness-->'),('d2', 0, 'Responsivenness', '<!--lang_client_feedback_responsiveness-->'),('d3', 0, 'Overall', '<!--lang_client_feedback_overall-->'),('d4', 1, 'Comment', '<!--lang_client_feedback_comment-->');");
$sttime = time();
$max_fb_time = null;
if ($result = $_link->Query(true, "SELECT * FROM `" . DB_PREFIX . "ratings` ORDER BY `time` DESC;")) {
while ($sttime > time() - 15 && ($row = DBManager::FetchArray($result))) {
$userData = new UserData($row["fullname"], $row["email"], $row["company"], "");
$hash = $userData->Hash();
$_link->Query(true, "INSERT IGNORE INTO `" . DBManager::RealEscape($_prefix) . "user_data` (`id`,`create`,`h_fullname`,`h_email`,`h_company`,`h_customs`) VALUES ('" . DBManager::RealEscape($hash) . "'," . intval(time()) . ",'" . DBManager::RealEscape($row["fullname"]) . "','" . DBManager::RealEscape($row["email"]) . "','" . DBManager::RealEscape($row["company"]) . "','');");
$_link->Query(true, "INSERT INTO `" . DBManager::RealEscape($_prefix) . "feedbacks` (`id`, `created`, `chat_id`, `user_id`, `operator_id`, `data_id`, `ip_hash`) VALUES ('" . DBManager::RealEscape($id = getId(32)) . "', " . intval($row["time"]) . ", '" . DBManager::RealEscape($row["chat_id"]) . "', '" . DBManager::RealEscape($row["user_id"]) . "', '" . DBManager::RealEscape($row["internal_id"]) . "','" . DBManager::RealEscape($hash) . "','" . DBManager::RealEscape(md5($row["ip"])) . "');");
$_link->Query(true, "INSERT INTO `" . DBManager::RealEscape($_prefix) . "feedback_criteria` (`fid`, `cid`, `value`) VALUES ('" . DBManager::RealEscape($id) . "', 'd0', '" . DBManager::RealEscape($row["qualification"]) . "');");
$_link->Query(true, "INSERT INTO `" . DBManager::RealEscape($_prefix) . "feedback_criteria` (`fid`, `cid`, `value`) VALUES ('" . DBManager::RealEscape($id) . "', 'd1', '" . DBManager::RealEscape($row["politeness"]) . "');");
if ($max_fb_time == null) {
$max_fb_time = $row["time"];
}
}
}
if ($max_fb_time == null) {
$max_fb_time = time();
}
CacheManager::WriteDataUpdateTime(DATA_UPDATE_KEY_FEEDBACKS, false, $_link, $_prefix, intval($max_fb_time) * 1000);
CacheManager::WriteDataUpdateTime(DATA_UPDATE_KEY_FILTERS, false, $_link, $_prefix, intval(time()) * 1000);
return $res;
}
static function FromTicketMessage($_ticketMessage)
{
$d = new UserData($_ticketMessage->Fullname, $_ticketMessage->Email, $_ticketMessage->Company, $_ticketMessage->Phone, $_ticketMessage->Customs, "");
$d->Id = $d->Hash();
return $d;
}
