/**
* Update from 0.78.2 to 0.80
*
* @return bool for success (will die for most error)
**/
function update0782to080()
{
global $DB, $migration;
$updateresult = true;
$ADDTODISPLAYPREF = array();
//TRANS: %s is the number of new version
$migration->displayTitle(sprintf(__('Update to %s'), '0.80'));
$migration->setVersion('0.80');
$backup_tables = false;
$newtables = array('glpi_calendars', 'glpi_calendars_holidays', 'glpi_calendarsegments', 'glpi_computervirtualmachines', 'glpi_computers_softwarelicenses', 'glpi_fieldblacklists', 'glpi_fieldunicities', 'glpi_groups_tickets', 'glpi_holidays', 'glpi_rulecacheprinters', 'glpi_slas', 'glpi_slalevels', 'glpi_slalevels_tickets', 'glpi_slalevelactions', 'glpi_tickets_tickets', 'glpi_tickets_users', 'glpi_ticketsatisfactions', 'glpi_ticketsolutiontemplates', 'glpi_virtualmachinestates', 'glpi_virtualmachinesystems', 'glpi_virtualmachinetypes');
foreach ($newtables as $new_table) {
// rename new tables if exists ?
if (TableExists($new_table)) {
if (TableExists("backup_{$new_table}")) {
$query = "DROP TABLE `backup_" . $new_table . "`";
$DB->queryOrDie($query, "0.80 drop backup table backup_{$new_table}");
}
$migration->displayWarning("{$new_table} table already exists. " . "A backup have been done to backup_{$new_table}.");
$backup_tables = true;
$query = $migration->renameTable("{$new_table}", "backup_{$new_table}");
}
}
if ($backup_tables) {
$migration->displayWarning("You can delete backup tables if you have no need of them.", true);
}
$migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Calendar'));
// Updating schema
$default_calendar_id = 0;
if (!TableExists('glpi_calendars')) {
$query = "CREATE TABLE `glpi_calendars` (\n `id` int(11) NOT NULL auto_increment,\n `name` varchar(255) default NULL,\n `entities_id` int(11) NOT NULL default '0',\n `is_recursive` tinyint(1) NOT NULL default '0',\n `comment` TEXT DEFAULT NULL ,\n `date_mod` DATETIME DEFAULT NULL ,\n `cache_duration` TEXT DEFAULT NULL ,\n PRIMARY KEY (`id`),\n KEY `name` (`name`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`),\n KEY `date_mod` (`date_mod`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 create glpi_calendars");
$ADDTODISPLAYPREF['Calendar'] = array(19);
// Create default calendar : use existing config planning_begin _end
$query = "INSERT INTO `glpi_calendars`\n (`name`, `entities_id`, `is_recursive`, `comment`)\n VALUES ('Default', 0, 1, 'Default calendar');";
$DB->queryOrDie($query, "0.80 add default glpi_calendars");
$default_calendar_id = $DB->insert_id();
}
if (!TableExists('glpi_calendarsegments')) {
$query = "CREATE TABLE `glpi_calendarsegments` (\n `id` int(11) NOT NULL auto_increment,\n `calendars_id` int(11) NOT NULL default '0',\n `entities_id` int(11) NOT NULL default '0',\n `is_recursive` tinyint(1) NOT NULL default '0',\n `day` tinyint(1) NOT NULL default '1' COMMENT 'numer of the day based on date(w)',\n `begin` time DEFAULT NULL,\n `end` time DEFAULT NULL,\n PRIMARY KEY (`id`),\n KEY `calendars_id` (`calendars_id`),\n KEY `day` (`day`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 create glpi_calendarsegments");
// add defautl days : from monday to friday
if ($default_calendar_id > 0) {
$query = "SELECT `planning_begin`, `planning_end`\n FROM `glpi_configs`\n WHERE `id` = '1'";
if ($result = $DB->query($query)) {
$begin = $DB->result($result, 0, 'planning_begin');
$end = $DB->result($result, 0, 'planning_end');
if ($begin < $end) {
for ($i = 1; $i < 6; $i++) {
$query = "INSERT INTO `glpi_calendarsegments`\n (`calendars_id`, `day`, `begin`, `end`)\n VALUES ({$default_calendar_id}, {$i}, '{$begin}', '{$end}')";
$DB->queryOrDie($query, "0.80 add default glpi_calendarsegments");
}
}
}
// Update calendar
$calendar = new Calendar();
if ($calendar->getFromDB($default_calendar_id)) {
$query = "UPDATE `glpi_calendars`\n SET `cache_duration` = '" . exportArrayToDB($calendar->getDaysDurations()) . "'\n WHERE `id` = '{$default_calendar_id}'";
$DB->queryOrDie($query, "0.80 update default calendar cache");
}
}
}
// Holidays : wrong management : may be a group of several days : will be easy to managed holidays
if (!TableExists('glpi_holidays')) {
$query = "CREATE TABLE `glpi_holidays` (\n `id` int(11) NOT NULL auto_increment,\n `name` varchar(255) default NULL,\n `entities_id` int(11) NOT NULL default '0',\n `is_recursive` tinyint(1) NOT NULL default '0',\n `comment` TEXT DEFAULT NULL ,\n `begin_date` date default NULL,\n `end_date` date default NULL,\n `is_perpetual` tinyint(1) NOT NULL default '0',\n PRIMARY KEY (`id`),\n KEY `name` (`name`),\n KEY `begin_date` (`begin_date`),\n KEY `end_date` (`end_date`),\n KEY `is_perpetual` (`is_perpetual`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 create glpi_holidays");
$ADDTODISPLAYPREF['Holiday'] = array(11, 12, 13);
}
if (!TableExists('glpi_calendars_holidays')) {
$query = "CREATE TABLE `glpi_calendars_holidays` (\n `id` int(11) NOT NULL auto_increment,\n `calendars_id` int(11) NOT NULL default '0',\n `holidays_id` int(11) NOT NULL default '0',\n PRIMARY KEY (`id`),\n UNIQUE KEY `unicity` (`calendars_id`,`holidays_id`),\n KEY `holidays_id` (`holidays_id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 create glpi_calendars_holidays");
}
$migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'SLA'));
// Updating schema
if (!TableExists('glpi_slas')) {
$query = "CREATE TABLE `glpi_slas` (\n `id` int(11) NOT NULL auto_increment,\n `name` varchar(255) default NULL,\n `entities_id` int(11) NOT NULL default '0',\n `is_recursive` tinyint(1) NOT NULL default '0',\n `comment` TEXT DEFAULT NULL ,\n `resolution_time` int(11) NOT NULL,\n `calendars_id` int(11) NOT NULL default '0',\n `date_mod` datetime default NULL,\n PRIMARY KEY (`id`),\n KEY `name` (`name`),\n KEY `calendars_id` (`calendars_id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`),\n KEY `date_mod` (`date_mod`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 create glpi_slas");
$ADDTODISPLAYPREF['SLA'] = array(4);
// Get first Ticket template
$query = "SELECT `id`\n FROM `glpi_notificationtemplates`\n WHERE `itemtype` LIKE 'Ticket%'\n ORDER BY `id` ASC";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) > 0) {
$query = "INSERT INTO `glpi_notifications`\n (`name`, `entities_id`, `itemtype`, `event`, `mode`,\n `notificationtemplates_id`, `comment`, `is_recursive`, `is_active`,\n `date_mod`)\n VALUES ('Ticket Recall', 0, 'Ticket', 'recall', 'mail',\n " . $DB->result($result, 0, 0) . ", '', 1, 1,\n NOW());";
$DB->queryOrDie($query, "0.80 insert notification");
}
}
}
if (!TableExists('glpi_slalevels')) {
$query = "CREATE TABLE `glpi_slalevels` (\n `id` int(11) NOT NULL auto_increment,\n `name` varchar(255) collate utf8_unicode_ci default NULL,\n `slas_id` int(11) NOT NULL default '0',\n `execution_time` int(11) NOT NULL,\n `is_active` tinyint(1) NOT NULL default '1',\n PRIMARY KEY (`id`),\n KEY `name` (`name`),\n KEY `is_active` (`is_active`),\n KEY `slas_id` (`slas_id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 create glpi_slalevels");
}
if (!TableExists('glpi_slalevelactions')) {
$query = "CREATE TABLE `glpi_slalevelactions` (\n `id` int(11) NOT NULL auto_increment,\n `slalevels_id` int(11) NOT NULL default '0',\n `action_type` varchar(255) collate utf8_unicode_ci default NULL,\n `field` varchar(255) collate utf8_unicode_ci default NULL,\n `value` varchar(255) collate utf8_unicode_ci default NULL,\n PRIMARY KEY (`id`),\n KEY `slalevels_id` (`slalevels_id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;";
$DB->queryOrDie($query, "0.80 create glpi_slalevelactions");
}
$migration->addField("glpi_profiles", "calendar", "CHAR( 1 ) NULL", array('update' => "`entity_dropdown`"));
$migration->addField("glpi_profiles", "sla", "CHAR( 1 ) NULL", array('update' => "`entity_rule_ticket`"));
$migration->addField("glpi_tickets", "slas_id", "INT( 11 ) NOT NULL DEFAULT 0");
$migration->addKey("glpi_tickets", "slas_id");
$migration->addField("glpi_tickets", "slalevels_id", "INT( 11 ) NOT NULL DEFAULT 0");
$migration->addKey("glpi_tickets", "slalevels_id");
if ($migration->addField("glpi_tickets", "due_date", "datetime default NULL")) {
$ADDTODISPLAYPREF['Ticket'] = array(18);
}
$migration->addKey("glpi_tickets", "due_date");
$migration->addField("glpi_tickets", "begin_waiting_date", "datetime default NULL");
$migration->addField("glpi_tickets", "sla_waiting_duration", "INT( 11 ) NOT NULL DEFAULT 0");
if (!TableExists('glpi_slalevels_tickets')) {
$query = "CREATE TABLE `glpi_slalevels_tickets` (\n `id` int(11) NOT NULL auto_increment,\n `tickets_id` int(11) NOT NULL default '0',\n `slalevels_id` int(11) NOT NULL default '0',\n `date` datetime default NULL,\n PRIMARY KEY (`id`),\n KEY `tickets_id` (`tickets_id`),\n KEY `slalevels_id` (`slalevels_id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;";
$DB->queryOrDie($query, "0.80 create glpi_slalevels_tickets");
}
if (!countElementsInTable('glpi_crontasks', "`itemtype`='SlaLevel_Ticket' AND `name`='slaticket'")) {
$query = "INSERT INTO `glpi_crontasks`\n (`itemtype`, `name`, `frequency`, `param`, `state`, `mode`, `allowmode`,\n `hourmin`, `hourmax`, `logs_lifetime`, `lastrun`, `lastcode`, `comment`)\n VALUES ('SlaLevel_Ticket', 'slaticket', 300, NULL, 1, 1, 3,\n 0, 24, 30, NULL, NULL, NULL)";
$DB->queryOrDie($query, "0.80 populate glpi_crontasks for slaticket");
}
$migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'PasswordForget'));
// Updating schema
$migration->addField("glpi_users", "token", "char( 40 ) NULL DEFAULT ''");
$migration->addField("glpi_users", "tokendate", "datetime NULL DEFAULT NULL");
$query = "SELECT *\n FROM `glpi_notificationtemplates`\n WHERE `name` = 'Password Forget'";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) == 0) {
$query = "INSERT INTO `glpi_notificationtemplates`\n (`name`, `itemtype`, `date_mod`)\n VALUES ('Password Forget', 'User', NOW())";
$DB->queryOrDie($query, "0.80 add password forget notification");
$notid = $DB->insert_id();
$query = "INSERT INTO `glpi_notificationtemplatetranslations`\n (`notificationtemplates_id`, `language`, `subject`,\n `content_text`,\n `content_html`)\n VALUES ({$notid}, '', '##user.action##',\n '##user.realname## ##user.firstname##\n\n##lang.passwordforget.information##\n\n##lang.passwordforget.link## ##user.passwordforgeturl##',\n '<p><strong>##user.realname## ##user.firstname##</strong></p>\n<p>##lang.passwordforget.information##</p>\n<p>##lang.passwordforget.link## <a title=\"##user.passwordforgeturl##\" href=\"##user.passwordforgeturl##\">##user.passwordforgeturl##</a></p>')";
$DB->queryOrDie($query, "0.80 add password forget notification translation");
$query = "INSERT INTO `glpi_notifications`\n (`name`, `entities_id`, `itemtype`, `event`, `mode`,\n `notificationtemplates_id`, `comment`, `is_recursive`, `is_active`,\n `date_mod`)\n VALUES ('Password Forget', 0, 'User', 'passwordforget', 'mail',\n {$notid}, '', 1, 1,\n NOW())";
$DB->queryOrDie($query, "0.80 add password forget notification");
$notifid = $DB->insert_id();
$query = "INSERT INTO `glpi_notificationtargets`\n (`id`, `notifications_id`, `type`, `items_id`)\n VALUES (NULL, {$notifid}, 1, 19);";
$DB->queryOrDie($query, "0.80 add password forget notification target");
}
}
$migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Ticket'));
// Updating schema
$migration->addField("glpi_tickets", "ticket_waiting_duration", "INT( 11 ) NOT NULL DEFAULT 0");
$migration->addField("glpi_entitydatas", "calendars_id", "INT( 11 ) NOT NULL DEFAULT 0");
$migration->addField("glpi_tickets", "close_delay_stat", "INT( 11 ) NOT NULL DEFAULT 0", array('update' => "(UNIX_TIMESTAMP(`glpi_tickets`.`closedate`)\n - UNIX_TIMESTAMP(`glpi_tickets`.`date`))", 'condition' => " WHERE `glpi_tickets`.`status` = 'closed'\n AND `glpi_tickets`.`date` IS NOT NULL\n AND `glpi_tickets`.`closedate` IS NOT NULL\n AND `glpi_tickets`.`closedate` > `glpi_tickets`.`date`"));
$migration->addField("glpi_tickets", "solve_delay_stat", "INT( 11 ) NOT NULL DEFAULT 0", array('update' => "(UNIX_TIMESTAMP(`glpi_tickets`.`solvedate`)\n - UNIX_TIMESTAMP(`glpi_tickets`.`date`))", 'condition' => " WHERE (`glpi_tickets`.`status` = 'closed'\n OR `glpi_tickets`.`status` = 'solved')\n AND `glpi_tickets`.`date` IS NOT NULL\n AND `glpi_tickets`.`solvedate` IS NOT NULL\n AND `glpi_tickets`.`solvedate` > `glpi_tickets`.`date`"));
if ($migration->addField("glpi_tickets", "takeintoaccount_delay_stat", "INT( 11 ) NOT NULL DEFAULT 0")) {
$migration->migrationOneTable('glpi_tickets');
// Manage stat computation for existing tickets
// Solved tickets
$query = "SELECT `glpi_tickets`.`id` AS ID,\n MIN(UNIX_TIMESTAMP(`glpi_tickets`.`solvedate`)\n - UNIX_TIMESTAMP(`glpi_tickets`.`date`)) AS OPEN,\n MIN(UNIX_TIMESTAMP(`glpi_ticketfollowups`.`date`)\n - UNIX_TIMESTAMP(`glpi_tickets`.`date`)) AS FIRST,\n MIN(UNIX_TIMESTAMP(`glpi_tickettasks`.`date`)\n - UNIX_TIMESTAMP(`glpi_tickets`.`date`)) AS FIRST2\n FROM `glpi_tickets`\n LEFT JOIN `glpi_ticketfollowups`\n ON (`glpi_ticketfollowups`.`tickets_id` = `glpi_tickets`.`id`)\n LEFT JOIN `glpi_tickettasks`\n ON (`glpi_tickettasks`.`tickets_id` = `glpi_tickets`.`id`)\n WHERE (`glpi_tickets`.`status` = 'closed'\n OR `glpi_tickets`.`status` = 'solved')\n AND `glpi_tickets`.`solvedate` IS NOT NULL\n GROUP BY `glpi_tickets`.`id`";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) > 0) {
while ($data = $DB->fetch_assoc($result)) {
$firstactiontime = min($data['OPEN'], $data['FIRST'], $data['FIRST2']);
$firstactiontime = max(0, $firstactiontime);
$query2 = "UPDATE `glpi_tickets`\n SET `takeintoaccount_delay_stat` = '{$firstactiontime}'\n WHERE `id` = '" . $data['ID'] . "'";
$DB->queryOrDie($query2, "0.80 update takeintoaccount_delay_stat values for #" . $data['ID']);
}
}
}
// Not solved tickets
$query = "SELECT `glpi_tickets`.`id` AS ID,\n MIN(UNIX_TIMESTAMP(`glpi_ticketfollowups`.`date`)\n - UNIX_TIMESTAMP(`glpi_tickets`.`date`)) AS FIRST,\n MIN(UNIX_TIMESTAMP(`glpi_tickettasks`.`date`)\n - UNIX_TIMESTAMP(`glpi_tickets`.`date`)) AS FIRST2\n FROM `glpi_tickets`\n LEFT JOIN `glpi_ticketfollowups`\n ON (`glpi_ticketfollowups`.`tickets_id` = `glpi_tickets`.`id`)\n LEFT JOIN `glpi_tickettasks`\n ON (`glpi_tickettasks`.`tickets_id` = `glpi_tickets`.`id`)\n WHERE (`glpi_tickets`.`status` <> 'closed'\n AND `glpi_tickets`.`status` <> 'solved')\n OR `glpi_tickets`.`solvedate` IS NULL\n GROUP BY `glpi_tickets`.`id`";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) > 0) {
while ($data = $DB->fetch_assoc($result)) {
$firstactiontime = min($data['FIRST'], $data['FIRST2']);
$firstactiontime = max(0, $firstactiontime);
$query2 = "UPDATE `glpi_tickets`\n SET `takeintoaccount_delay_stat` = '{$firstactiontime}'\n WHERE `id` = '" . $data['ID'] . "'";
$DB->queryOrDie($query2, "0.80 update takeintoaccount_delay_stat values for #" . $data['ID']);
}
}
}
}
// Put realtime in seconds
$migration->addField("glpi_tickets", "actiontime", "INT( 11 ) NOT NULL DEFAULT 0", array('update' => "ROUND(realtime * 3600)"));
$migration->dropField("glpi_tickets", "realtime");
$migration->addField("glpi_tickettasks", "actiontime", "INT( 11 ) NOT NULL DEFAULT 0", array('update' => "ROUND(realtime * 3600)"));
$migration->dropField("glpi_tickettasks", "realtime");
$migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Software'));
// Updating schema
if ($migration->addField("glpi_softwareversions", "operatingsystems_id", "INT( 11 ) NOT NULL DEFAULT '0'")) {
$migration->addKey("glpi_softwareversions", "operatingsystems_id");
$migration->migrationOneTable('glpi_softwareversions');
$query = "UPDATE `glpi_softwareversions`,\n (SELECT `id`, `operatingsystems_id`\n FROM `glpi_softwares`) AS SOFT\n SET `glpi_softwareversions`.`operatingsystems_id` = `SOFT`.`operatingsystems_id`\n WHERE `glpi_softwareversions`.`softwares_id` = `SOFT`.`id` ";
$DB->queryOrDie($query, "0.80 transfer operatingsystems_id from glpi_softwares to glpi_softwareversions");
$migration->dropField("glpi_softwares", "operatingsystems_id");
}
if (!isIndex("glpi_computers_softwareversions", "unicity")) {
// clean datas
$query = "SELECT `computers_id`,\n `softwareversions_id`,\n COUNT(*) AS CPT\n FROM `glpi_computers_softwareversions`\n GROUP BY `computers_id`, `softwareversions_id`\n HAVING CPT > 1";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
$query2 = "SELECT `id`\n FROM `glpi_computers_softwareversions`\n WHERE `computers_id` = '" . $data['computers_id'] . "'\n AND `softwareversions_id` = '" . $data['softwareversions_id'] . "'\n LIMIT 1";
if ($result2 = $DB->query($query2)) {
if ($DB->numrows($result2)) {
$keep_id = $DB->result($result2, 0, 0);
$query3 = "DELETE\n FROM `glpi_computers_softwareversions`\n WHERE `computers_id` = '" . $data['computers_id'] . "'\n AND `softwareversions_id` = '" . $data['softwareversions_id'] . "'\n AND `id` <> {$keep_id}";
$DB->queryOrDie($query3, "0.80 clean glpi_computers_softwareversions");
}
}
}
}
}
$migration->addKey("glpi_computers_softwareversions", array('computers_id', 'softwareversions_id'), 'unicity', "UNIQUE");
}
$migration->dropKey("glpi_computers_softwareversions", "computers_id");
// For real count : copy template and deleted information
$migration->addField("glpi_computers_softwareversions", "is_deleted", "tinyint(1) NOT NULL DEFAULT 0");
// Gain de temps pour les beta-testeurs
if ($migration->addField("glpi_computers_softwareversions", "is_template", "tinyint(1) NOT NULL DEFAULT 0")) {
$migration->migrationOneTable('glpi_computers_softwareversions');
// Update datas
$query = "SELECT DISTINCT `computers_id`\n FROM `glpi_computers_softwareversions`";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
$comp = new Computer();
if ($comp->getFromDB($data['computers_id'])) {
$query = "UPDATE `glpi_computers_softwareversions`\n SET `is_template` = '" . $comp->getField('is_template') . "',\n `is_deleted` = '" . $comp->getField('is_deleted') . "'\n WHERE `computers_id` = '" . $data['computers_id'] . "';";
$DB->query($query);
}
}
}
}
}
if (!TableExists("glpi_computers_softwarelicenses")) {
$query = "CREATE TABLE `glpi_computers_softwarelicenses` (\n `id` int(11) NOT NULL auto_increment,\n `computers_id` int(11) NOT NULL default '0',\n `softwarelicenses_id` int(11) NOT NULL default '0',\n PRIMARY KEY (`id`),\n KEY `computers_id` (`computers_id`),\n KEY `softwarelicenses_id` (`softwarelicenses_id`),\n UNIQUE `unicity` ( `computers_id` , `softwarelicenses_id` )\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 create glpi_computers_softwarelicenses");
}
if (FieldExists("glpi_softwarelicenses", "computers_id", false)) {
$query = "SELECT *\n FROM `glpi_softwarelicenses`\n WHERE `computers_id` > 0\n AND `computers_id` IS NOT NULL";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
$query = "INSERT INTO `glpi_computers_softwarelicenses`\n (`computers_id`, `softwarelicenses_id`)\n VALUES ('" . $data['computers_id'] . "','" . $data['id'] . "')";
$DB->queryOrDie($query, "0.80 migrate data to computers_softwarelicenses table");
}
}
}
$migration->dropField("glpi_softwarelicenses", "computers_id");
}
$migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Common'));
// Updating schema
$migration->addField("glpi_softwarelicenses", "date_mod", "DATETIME NULL");
$migration->addKey("glpi_softwarelicenses", "date_mod");
$migration->renameTable("glpi_cartridges_printermodels", "glpi_cartridgeitems_printermodels");
$migration->addField("glpi_monitors", "have_hdmi", "tinyint(1) NOT NULL DEFAULT 0 AFTER `have_pivot`");
$migration->addField("glpi_monitors", "have_displayport", "tinyint(1) NOT NULL DEFAULT 0 AFTER `have_hdmi`");
$migration->dropField("glpi_configs", "dbreplicate_email");
$migration->addField("glpi_configs", "auto_create_infocoms", "tinyint(1) NOT NULL DEFAULT 0");
$migration->addField("glpi_configs", "csv_delimiter", "CHAR( 1 ) NOT NULL AFTER `number_format`", array('update' => "';'"));
$migration->addField("glpi_users", "csv_delimiter", "CHAR( 1 ) NULL AFTER `number_format`");
$migration->addField("glpi_users", "names_format", "INT( 11 ) NULL DEFAULT NULL AFTER `number_format`");
// drop car fait sur mauvais champ
$migration->dropKey("glpi_budgets", "end_date");
$migration->migrationOneTable("glpi_budgets");
$migration->addKey("glpi_budgets", "end_date");
$migration->addField("glpi_authldaps", "is_active", "TINYINT( 1 ) NOT NULL DEFAULT '0'", array('update' => "'1'"));
$ADDTODISPLAYPREF['AuthLdap'] = array(30);
$migration->addField("glpi_authmails", "is_active", "TINYINT( 1 ) NOT NULL DEFAULT '0'", array('update' => "'1'"));
$ADDTODISPLAYPREF['AuthMail'] = array(6);
$migration->addField("glpi_ocsservers", "is_active", "TINYINT( 1 ) NOT NULL DEFAULT '0'", array('update' => "'1'"));
$ADDTODISPLAYPREF['OcsServer'] = array(6);
$migration->changeField("glpi_configs", "use_auto_assign_to_tech", "auto_assign_mode", "INT( 11 ) NOT NULL DEFAULT '1'");
$migration->addField("glpi_entitydatas", "auto_assign_mode", "INT( 11 ) NOT NULL DEFAULT '-1'");
$migration->changeField("glpi_entitydatas", "ldapservers_id", "authldaps_id", "INT( 11 ) NOT NULL DEFAULT '0'");
$migration->addField("glpi_users", "user_dn", "TEXT DEFAULT NULL");
$migration->addField("glpi_tickets", "users_id_lastupdater", "INT( 11 ) NOT NULL DEFAULT 0 AFTER `date_mod`");
$migration->addKey("glpi_tickets", "users_id_lastupdater");
$migration->addField("glpi_tickets", "type", "INT( 11 ) NOT NULL DEFAULT 1 AFTER `ticketcategories_id`");
$migration->addKey("glpi_tickets", "type");
$migration->addField("glpi_entitydatas", "tickettype", "INT( 11 ) NOT NULL DEFAULT 0");
// Link between tickets
if (!TableExists('glpi_tickets_tickets')) {
$query = "CREATE TABLE `glpi_tickets_tickets` (\n `id` int(11) NOT NULL auto_increment,\n `tickets_id_1` int(11) NOT NULL default '0',\n `tickets_id_2` int(11) NOT NULL default '0',\n `link` int(11) NOT NULL default '1',\n PRIMARY KEY (`id`),\n KEY `unicity` (`tickets_id_1`,`tickets_id_2`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 create glpi_tickets_tickets");
}
//inquest
if (!TableExists('glpi_ticketsatisfactions')) {
$query = "CREATE TABLE `glpi_ticketsatisfactions` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `tickets_id` int(11) NOT NULL DEFAULT '0',\n `type` int(11) NOT NULL DEFAULT '1',\n `date_begin` DATETIME NULL ,\n `date_answered` DATETIME NULL ,\n `satisfaction` INT(11) NULL ,\n `comment` text COLLATE utf8_unicode_ci,\n PRIMARY KEY (`id`),\n UNIQUE KEY `tickets_id` (`tickets_id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 create glpi_ticketsatisfactions");
}
// config inquest by entity
$migration->addField("glpi_entitydatas", "max_closedate", "DATETIME NULL");
if (!countElementsInTable('glpi_crontasks', "`itemtype`='Ticket' AND `name`='createinquest'")) {
$query = "INSERT INTO `glpi_crontasks`\n (`itemtype`, `name`, `frequency`, `param`, `state`, `mode`, `allowmode`,\n `hourmin`, `hourmax`, `logs_lifetime`, `lastrun`, `lastcode`, `comment`)\n VALUES ('Ticket', 'createinquest', 86400, NULL, 1, 1, 3,\n 0, 24, 30, NULL, NULL, NULL)";
$DB->queryOrDie($query, "0.80 populate glpi_crontasks for ticketsatisfaction");
}
$migration->addField("glpi_entitydatas", "inquest_config", "INT(11) NOT NULL DEFAULT '0'");
$migration->addField("glpi_entitydatas", "inquest_rate", "INT(11) NOT NULL DEFAULT '-1'");
$migration->addField("glpi_entitydatas", "inquest_delay", "INT(11) NOT NULL DEFAULT '-1'");
$migration->addField("glpi_entitydatas", "inquest_URL", "VARCHAR( 255 ) NULL");
$migration->addField("glpi_networkports", "comment", "TEXT COLLATE utf8_unicode_ci");
$migration->addField("glpi_profiles", "rule_dictionnary_printer", "CHAR( 1 ) NULL", array('update' => "`rule_dictionnary_software`"));
$query = "SELECT *\n FROM `glpi_notificationtemplates`\n WHERE `name` = 'Ticket Satisfaction'";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) == 0) {
$query = "INSERT INTO `glpi_notificationtemplates`\n (`name`, `itemtype`, `date_mod`)\n VALUES ('Ticket Satisfaction', 'Ticket', NOW())";
$DB->queryOrDie($query, "0.80 add ticket satisfaction notification");
$notid = $DB->insert_id();
$query = "INSERT INTO `glpi_notificationtemplatetranslations`\n (`notificationtemplates_id`, `language`, `subject`,\n `content_text`, `content_html`)\n VALUES ({$notid}, '', '##ticket.action## ##ticket.title##',\n '##lang.ticket.title## : ##ticket.title##\n\n##lang.ticket.closedate## : ##ticket.closedate##\n\n##lang.satisfaction.text## ##ticket.urlsatisfaction##',\n\n '<p>##lang.ticket.title## : ##ticket.title##</p>\n<p>##lang.ticket.closedate## : ##ticket.closedate##</p>\n<p>##lang.satisfaction.text## <a href=\"##ticket.urlsatisfaction##\">##ticket.urlsatisfaction##</a></p>')";
$DB->queryOrDie($query, "0.80 add ticket satisfaction notification translation");
$query = "INSERT INTO `glpi_notifications`\n (`name`, `entities_id`, `itemtype`, `event`, `mode`,\n `notificationtemplates_id`, `comment`, `is_recursive`, `is_active`,\n `date_mod`)\n VALUES ('Ticket Satisfaction', 0, 'Ticket', 'satisfaction', 'mail',\n {$notid}, '', 1, 1,\n NOW())";
$DB->queryOrDie($query, "0.80 add ticket satisfaction notification");
$notifid = $DB->insert_id();
// $query = "INSERT INTO `glpi_notificationtargets`
// (`id`, `notifications_id`, `type`, `items_id`)
// VALUES (NULL, $notifid, 1, 3)";
// $DB->queryOrDie($query, "0.80 add ticket satisfaction notification target");
}
}
//New infocom dates
$migration->addField("glpi_infocoms", "order_date", "DATE NULL");
$migration->addField("glpi_infocoms", "delivery_date", "DATE NULL");
$migration->addField("glpi_infocoms", "inventory_date", "DATE NULL");
$migration->addField("glpi_infocoms", "warranty_date", "DATE NULL", array('update' => "`buy_date`"));
if (!TableExists('glpi_rulecacheprinters')) {
$query = "CREATE TABLE `glpi_rulecacheprinters` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `old_value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,\n `manufacturer` varchar(255) COLLATE utf8_unicode_ci NOT NULL,\n `rules_id` int(11) NOT NULL DEFAULT '0',\n `new_value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,\n `new_manufacturer` varchar(255) COLLATE utf8_unicode_ci NOT NULL,\n `ignore_ocs_import` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,\n `is_global` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,\n PRIMARY KEY (`id`),\n KEY `old_value` (`old_value`),\n KEY `rules_id` (`rules_id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 add table glpi_rulecacheprinters");
}
$migration->addField("glpi_configs", "use_slave_for_search", "tinyint( 1 ) NOT NULL DEFAULT '0'");
$migration->addField("glpi_configs", "admin_email_name", "varchar( 255 ) collate utf8_unicode_ci default NULL AFTER `admin_email`");
$migration->addField("glpi_configs", "admin_reply_name", "varchar( 255 ) collate utf8_unicode_ci default NULL AFTER `admin_reply`");
$migration->addField("glpi_entitydatas", "admin_email_name", "varchar( 255 ) collate utf8_unicode_ci default NULL AFTER `admin_email`");
$migration->addField("glpi_entitydatas", "admin_reply_name", "varchar( 255 ) collate utf8_unicode_ci default NULL AFTER `admin_reply`");
$migration->addField("glpi_notificationtemplates", "css", "text COLLATE utf8_unicode_ci");
$migration->addField("glpi_configs", "url_maxlength", "int(11) NOT NULL DEFAULT '30' AFTER `list_limit_max`");
$migration->displayMessage(sprintf(__('Data migration - %s'), 'Multi user group for tickets'));
if (!TableExists('glpi_groups_tickets')) {
$query = "CREATE TABLE `glpi_groups_tickets` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `tickets_id` int(11) NOT NULL DEFAULT '0',\n `groups_id` int(11) NOT NULL DEFAULT '0',\n `type` int(11) NOT NULL DEFAULT '1',\n PRIMARY KEY (`id`),\n KEY `unicity` (`tickets_id`,`type`,`groups_id`),\n KEY `group` (`groups_id`,`type`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 add table glpi_groups_tickets");
$query = "SELECT `id`, `groups_id`, `groups_id_assign`\n FROM `glpi_tickets`";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
if ($data['groups_id'] > 0) {
$query = "INSERT INTO `glpi_groups_tickets`\n (`tickets_id`, `groups_id`,\n `type`)\n VALUES ('" . $data['id'] . "', '" . $data['groups_id'] . "',\n '" . CommonITILActor::REQUESTER . "')";
$DB->queryOrDie($query, "0.80 migrate data to glpi_groups_tickets table");
}
if ($data['groups_id_assign'] > 0) {
$query = "INSERT INTO `glpi_groups_tickets`\n (`tickets_id`, `groups_id`,\n `type`)\n VALUES ('" . $data['id'] . "', '" . $data['groups_id_assign'] . "',\n '" . CommonITILActor::ASSIGN . "')";
$DB->queryOrDie($query, "0.80 migrate data to glpi_groups_tickets table");
}
}
}
}
$migration->dropField('glpi_tickets', 'groups_id');
$migration->dropField('glpi_tickets', 'groups_id_assign');
// Migrate templates
$from = array('ticket.group##', 'ticket.assigntogroup##', 'ticket.assigntouser##', 'ticket.author.name##', 'ticket.author##');
$to = array('ticket.groups##', 'ticket.assigntogroups##', 'ticket.assigntousers##', 'ticket.authors##', 'author.id##');
$query = "SELECT `glpi_notificationtemplatetranslations`.*\n FROM `glpi_notificationtemplatetranslations`\n INNER JOIN `glpi_notificationtemplates`\n ON (`glpi_notificationtemplates`.`id`\n = `glpi_notificationtemplatetranslations`.`notificationtemplates_id`)\n WHERE `glpi_notificationtemplates`.`itemtype` = 'Ticket'";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
$query = "UPDATE `glpi_notificationtemplatetranslations`\n SET `subject` = '" . addslashes(str_replace($from, $to, $data['subject'])) . "',\n `content_text` = '" . addslashes(str_replace($from, $to, $data['content_text'])) . "',\n `content_html` = '" . addslashes(str_replace($from, $to, $data['content_html'])) . "'\n WHERE `id` = " . $data['id'] . "";
$DB->queryOrDie($query, "0.80 fix tags usage for multi users");
}
}
}
}
if (!TableExists('glpi_tickets_users')) {
$query = "CREATE TABLE `glpi_tickets_users` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `tickets_id` int(11) NOT NULL DEFAULT '0',\n `users_id` int(11) NOT NULL DEFAULT '0',\n `type` int(11) NOT NULL DEFAULT '1',\n `use_notification` tinyint(1) NOT NULL DEFAULT '0',\n `alternative_email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,\n PRIMARY KEY (`id`),\n KEY `tickets_id` (`tickets_id`),\n KEY `user` (`users_id`,`type`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 add table glpi_tickets_users");
$query = "SELECT `glpi_tickets`.`id`,\n `glpi_tickets`.`users_id_assign`,\n `glpi_tickets`.`users_id`,\n `glpi_tickets`.`use_email_notification`,\n `glpi_tickets`.`user_email`,\n `glpi_users`.`email` AS EMAIL\n FROM `glpi_tickets`\n LEFT JOIN `glpi_users` ON (`glpi_users`.`id` = `glpi_tickets`.`users_id`)";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
if ($data['users_id_assign'] > 0) {
$query = "INSERT INTO `glpi_tickets_users`\n (`tickets_id`, `users_id`,\n `type`, `use_notification`)\n VALUES ('" . $data['id'] . "', '" . $data['users_id_assign'] . "',\n '" . CommonITILActor::ASSIGN . "', '1')";
$DB->queryOrDie($query, "0.80 migrate data to glpi_tickets_users table");
}
if ($data['users_id'] > 0 || $data['use_email_notification'] && !empty($data['user_email'])) {
$user_id = 0;
if ($data['users_id'] > 0) {
$user_id = $data['users_id'];
}
$user_email = '';
if (strcasecmp($data['user_email'], $data['EMAIL']) != 0) {
$user_email = addslashes($data['user_email']);
}
$query = "INSERT INTO `glpi_tickets_users`\n (`tickets_id`, `users_id`,`type`,\n `use_notification`, `alternative_email`)\n VALUES ('" . $data['id'] . "', '{$user_id}', '" . CommonITILActor::REQUESTER . "',\n '" . $data['use_email_notification'] . "', '{$user_email}')";
$DB->queryOrDie($query, "0.80 migrate data to glpi_tickets_users table");
}
}
}
}
$migration->dropField('glpi_tickets', 'users_id');
$migration->dropField('glpi_tickets', 'users_id_assign');
$migration->dropField('glpi_tickets', 'use_email_notification');
$migration->dropField('glpi_tickets', 'user_email');
// ADD observer when requester is set : 3>21 / 13>20 / 12 >22
$fromto = array(3 => 21, 13 => 20, 12 => 22);
// GROUP_SUPERVISOR
foreach ($fromto as $from => $to) {
$query = "SELECT *\n FROM `glpi_notificationtargets`\n INNER JOIN `glpi_notifications`\n ON (`glpi_notifications`.`id` = `glpi_notificationtargets`.`notifications_id`)\n WHERE `glpi_notifications`.`itemtype` = 'Ticket'\n AND `glpi_notificationtargets`.`type` = '1'\n AND `glpi_notificationtargets`.`items_id` = '{$from}'";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
$query = "INSERT INTO `glpi_notificationtargets`\n (`items_id` ,`type` ,`notifications_id`)\n VALUES ('{$to}', '1', '" . $data['notifications_id'] . "')";
$DB->queryOrDie($query, "0.80 insert default notif for observer");
}
}
}
}
}
$migration->displayMessage(sprintf(__('Data migration - %s'), 'passwords encryption'));
if ($migration->addField('glpi_configs', 'proxy_passwd', 'varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL')) {
$migration->migrationOneTable('glpi_configs');
$query = "SELECT `proxy_password`\n FROM `glpi_configs`\n WHERE `id` = '1'";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) > 0) {
$value = $DB->result($result, 0, 0);
if (!empty($value)) {
$query = "UPDATE `glpi_configs`\n SET `proxy_passwd` = '" . addslashes(Toolbox::encrypt($value, GLPIKEY)) . "'\n WHERE `id` = '1' ";
$DB->queryOrDie($query, "0.80 update proxy_passwd in glpi_configs");
}
}
}
$migration->dropField('glpi_configs', 'proxy_password');
}
if ($migration->addField('glpi_configs', 'smtp_passwd', 'varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL')) {
$migration->migrationOneTable('glpi_configs');
$query = "SELECT `smtp_password`\n FROM `glpi_configs`\n WHERE `id` = '1'";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) > 0) {
$value = $DB->result($result, 0, 0);
if (!empty($value)) {
$query = "UPDATE `glpi_configs`\n SET `smtp_passwd` = '" . addslashes(Toolbox::encrypt($value, GLPIKEY)) . "'\n WHERE `id` = '1' ";
$DB->queryOrDie($query, "0.80 update smtp_passwd in glpi_configs");
}
}
}
$migration->dropField('glpi_configs', 'smtp_password');
}
if ($migration->addField('glpi_authldaps', 'rootdn_passwd', 'varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL')) {
$migration->migrationOneTable('glpi_authldaps');
$query = "SELECT *\n FROM `glpi_authldaps`\n WHERE `rootdn_password` IS NOT NULL\n AND `rootdn_password` <> ''";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
if (!empty($data['rootdn_password'])) {
$query = "UPDATE `glpi_authldaps`\n SET `rootdn_passwd` = '" . addslashes(Toolbox::encrypt($data['rootdn_password'], GLPIKEY)) . "'\n WHERE `id` = '" . $data['id'] . "' ";
$DB->queryOrDie($query, "0.80 update rootdn_passwd in glpi_authldaps");
}
}
}
}
$migration->dropField('glpi_authldaps', 'rootdn_password');
}
//Add date config management fields
$migration->addField("glpi_entitydatas", "autofill_warranty_date", "varchar(255) COLLATE utf8_unicode_ci DEFAULT '-1'", array('update' => "'0'", 'condition' => " WHERE `entities_id` = '0'"));
$migration->addField("glpi_entitydatas", "autofill_use_date", "varchar(255) COLLATE utf8_unicode_ci DEFAULT '-1'", array('update' => "'0'", 'condition' => " WHERE `entities_id` = '0'"));
$migration->addField("glpi_entitydatas", "autofill_buy_date", "varchar(255) COLLATE utf8_unicode_ci DEFAULT '-1'", array('update' => "'0'", 'condition' => " WHERE `entities_id` = '0'"));
$migration->addField("glpi_entitydatas", "autofill_delivery_date", "varchar(255) COLLATE utf8_unicode_ci DEFAULT '-1'", array('update' => "'0'", 'condition' => " WHERE `entities_id` = '0'"));
$migration->addField("glpi_entitydatas", "autofill_order_date", "varchar(255) COLLATE utf8_unicode_ci DEFAULT '-1'", array('update' => "'0'", 'condition' => " WHERE `entities_id` = '0'"));
if (!TableExists('glpi_fieldunicities')) {
$query = "CREATE TABLE `glpi_fieldunicities` (\n `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,\n `name` VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n `is_recursive` TINYINT( 1 ) NOT NULL DEFAULT '0',\n `itemtype` VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n `entities_id` INT( 11 ) NOT NULL DEFAULT '-1',\n `fields` VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n `is_active` TINYINT( 1 ) NOT NULL DEFAULT '0',\n `action_refuse` TINYINT( 1 ) NOT NULL DEFAULT '0',\n `action_notify` TINYINT( 1 ) NOT NULL DEFAULT '0',\n `comment` text COLLATE utf8_unicode_ci\n ) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci\n COMMENT = 'Stores field unicity criterias'";
$DB->queryOrDie($query, "0.80 add table glpi_fieldunicities");
$ADDTODISPLAYPREF['FieldUnicity'] = array(1, 80, 4, 3, 86, 30);
}
$query = "SELECT *\n FROM `glpi_notificationtemplates`\n WHERE `name` = 'Item not unique'";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) == 0) {
$query = "INSERT INTO `glpi_notificationtemplates`\n (`name`, `itemtype`, `date_mod`)\n VALUES ('Item not unique', 'FieldUnicity', NOW())";
$DB->queryOrDie($query, "0.80 add item not unique notification");
$notid = $DB->insert_id();
$query = "INSERT INTO `glpi_notificationtemplatetranslations` " . "VALUES(NULL, {$notid}, '', '##lang.unicity.action##', " . "'##lang.unicity.entity## : ##unicity.entity## \r\n\n" . "##lang.unicity.itemtype## : ##unicity.itemtype## \r\n\n" . "##lang.unicity.message## : ##unicity.message## \r\n\n" . "##lang.unicity.action_user## : ##unicity.action_user## \r\n\n" . "##lang.unicity.action_type## : ##unicity.action_type## \r\n\n" . "##lang.unicity.date## : ##unicity.date##'," . "'<p>##lang.unicity.entity## : ##unicity.entity##</p>\r\n<p>" . "##lang.unicity.itemtype## : ##unicity.itemtype##</p>\r\n<p>" . "##lang.unicity.message## : ##unicity.message##</p>\r\n<p>" . "##lang.unicity.action_user## : ##unicity.action_user##</p>\r\n<p>" . "##lang.unicity.action_type## : ##unicity.action_type##</p>\r\n<p>" . "##lang.unicity.date## : ##unicity.date##</p>');";
$DB->queryOrDie($query, "0.80 add item not unique notification translation");
$query = "INSERT INTO `glpi_notifications`\n (`name`, `entities_id`, `itemtype`, `event`, `mode`,\n `notificationtemplates_id`, `comment`, `is_recursive`, `is_active`,\n `date_mod`)\n VALUES ('Item not unique', 0, 'FieldUnicity', 'refuse', 'mail',\n {$notid}, '', 1, 1,\n NOW())";
$DB->queryOrDie($query, "0.80 add computer not unique notification");
$notifid = $DB->insert_id();
// $query = "INSERT INTO `glpi_notificationtargets`
// (`notifications_id`, `type`, `items_id`)
// VALUES ($notifid, 1, 19);";
// $DB->queryOrDie($query, "0.80 add computer not unique notification target");
}
}
if (!TableExists("glpi_fieldblacklists")) {
$query = "CREATE TABLE `glpi_fieldblacklists` (\n `id` INT (11) NOT NULL AUTO_INCREMENT,\n `name` VARCHAR (255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n `field` VARCHAR (255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n `value` VARCHAR (255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n `itemtype` VARCHAR (255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\n `entities_id` INT (11) NOT NULL DEFAULT '0',\n `is_recursive` TINYINT (1) NOT NULL DEFAULT '0',\n `comment` TEXT COLLATE utf8_unicode_ci,\n PRIMARY KEY (id),\n KEY `name` (`name`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 add table glpi_fieldblacklists");
}
if ($migration->addField('glpi_mailcollectors', 'passwd', 'varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL')) {
$migration->migrationOneTable('glpi_mailcollectors');
$query = "SELECT *\n FROM `glpi_mailcollectors`\n WHERE `password` IS NOT NULL\n AND `password` <> ''";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
if (!empty($data['password'])) {
$query = "UPDATE `glpi_mailcollectors`\n SET `passwd` = '" . addslashes(Toolbox::encrypt($data['password'], GLPIKEY)) . "'\n WHERE `id`= '" . $data['id'] . "' ";
$DB->queryOrDie($query, "0.80 update passwd in glpi_mailcollectors");
}
}
}
}
$migration->dropField('glpi_mailcollectors', 'password');
}
$migration->displayMessage(sprintf(__('Data migration - %s'), 'rule ticket migration'));
$changes['RuleTicket'] = array('users_id' => '_users_id_requester', 'groups_id' => '_groups_id_requester', 'users_id_assign' => '_users_id_assign', 'groups_id_assign' => '_groups_id_assign');
// For Rule::RULE_TRACKING_AUTO_ACTION
$changes['RuleMailCollector'] = array('username' => '_users_id_requester');
$DB->query("SET SESSION group_concat_max_len = 4194304;");
foreach ($changes as $ruletype => $tab) {
// Get rules
$query = "SELECT GROUP_CONCAT(`id`)\n FROM `glpi_rules`\n WHERE `sub_type` = '" . $ruletype . "'\n GROUP BY `sub_type`";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) > 0) {
// Get rule string
$rules = $DB->result($result, 0, 0);
// Update actions
foreach ($tab as $old => $new) {
$query = "UPDATE `glpi_ruleactions`\n SET `field` = '{$new}'\n WHERE `field` = '{$old}'\n AND `rules_id` IN ({$rules})";
$DB->queryOrDie($query, "0.80 update datas for rules actions");
}
// Update criterias
foreach ($tab as $old => $new) {
$query = "UPDATE `glpi_rulecriterias`\n SET `criteria` = '{$new}'\n WHERE `criteria` = '{$old}'\n AND `rules_id` IN ({$rules})";
$DB->queryOrDie($query, "0.80 update datas for rules criterias");
}
}
}
}
// Add watcher crontask
if (!countElementsInTable('glpi_crontasks', "`itemtype`='Crontask' AND `name`='watcher'")) {
$query = "INSERT INTO `glpi_crontasks`\n (`itemtype`, `name`, `frequency`, `param`, `state`, `mode`, `allowmode`,\n `hourmin`, `hourmax`, `logs_lifetime`, `lastrun`, `lastcode`, `comment`)\n VALUES ('Crontask', 'watcher', 86400, NULL, 1, 1, 3,\n 0, 24, 30, NULL, NULL, NULL);";
$DB->queryOrDie($query, "0.80 populate glpi_crontasks for watcher");
}
$query = "SELECT *\n FROM `glpi_notificationtemplates`\n WHERE `name` = 'Crontask'";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) == 0) {
$query = "INSERT INTO `glpi_notificationtemplates`\n (`name`, `itemtype`, `date_mod`)\n VALUES ('Crontask', 'Crontask', NOW())";
$DB->queryOrDie($query, "0.80 add crontask watcher notification");
$notid = $DB->insert_id();
$query = "INSERT INTO `glpi_notificationtemplatetranslations`\n VALUES (NULL, {$notid}, '', '##crontask.action##',\n '##lang.crontask.warning## \r\n\n##FOREACHcrontasks## \n ##crontask.name## : ##crontask.description##\n \n##ENDFOREACHcrontasks##', '<p>##lang.crontask.warning##</p>\r\n<p>##FOREACHcrontasks## <br /><a href=\"##crontask.url##\">##crontask.name##</a> : ##crontask.description##<br /> <br />##ENDFOREACHcrontasks##</p>')";
$DB->queryOrDie($query, "0.80 add crontask notification translation");
$query = "INSERT INTO `glpi_notifications`\n VALUES (NULL, 'Crontask Watcher', 0, 'Crontask', 'alert', 'mail', {$notid}, '', 1, 1,\n NOW())";
$DB->queryOrDie($query, "0.80 add crontask notification");
$notifid = $DB->insert_id();
$query = "INSERT INTO `glpi_notificationtargets`\n (`id`, `notifications_id`, `type`, `items_id`)\n VALUES (NULL, {$notifid}, 1, 1)";
$DB->queryOrDie($query, "0.80 add crontask notification target to global admin");
}
}
/* OCS-NG new clean links features */
if ($migration->addField('glpi_ocslinks', 'entities_id', 'int(11) NOT NULL DEFAULT \'0\'')) {
$migration->migrationOneTable("glpi_ocslinks");
$query = "SELECT `glpi_ocslinks`.`computers_id`, `glpi_computers`.`entities_id`\n FROM `glpi_ocslinks`\n INNER JOIN `glpi_computers`\n ON (`glpi_computers`.`id` = `glpi_ocslinks`.`computers_id`)";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
$query = "UPDATE `glpi_ocslinks`\n SET `entities_id` = '" . $data['entities_id'] . "'\n WHERE `computers_id` = '" . $data['computers_id'] . "'";
$DB->queryOrDie($query, "0.80 copy entities_id from computers to ocslinks ");
}
}
}
}
$migration->addField("glpi_profiles", "clean_ocsng", "char(1) COLLATE utf8_unicode_ci DEFAULT NULL", array('update' => "`sync_ocsng`"));
/* END - OCS-NG new clean links features */
$migration->addField("glpi_transfers", "keep_disk", "int( 11 ) NOT NULL DEFAULT 0", array('update' => "'1'"));
if ($migration->addField("glpi_reminders", "is_helpdesk_visible", "tinyint( 1 ) NOT NULL DEFAULT 0")) {
$query = "UPDATE `glpi_profiles`\n SET `reminder_public` = 'r'\n WHERE `interface` = 'helpdesk';";
$DB->queryOrDie($query, "0.80 default set of reminder view for helpdesk users");
}
if (!TableExists('glpi_ticketsolutiontemplates')) {
$query = "CREATE TABLE `glpi_ticketsolutiontemplates` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `entities_id` int(11) NOT NULL DEFAULT '0',\n `is_recursive` tinyint(1) NOT NULL DEFAULT '0',\n `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,\n `content` text COLLATE utf8_unicode_ci,\n `ticketsolutiontypes_id` int(11) NOT NULL DEFAULT '0',\n `comment` text COLLATE utf8_unicode_ci,\n PRIMARY KEY (`id`),\n UNIQUE KEY `unicity` (`entities_id`,`name`),\n KEY `name` (`name`),\n KEY `is_recursive` (`is_recursive`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 create glpi_ticketsolutiontemplates");
}
// Fix templates tags
$updates = array('Ticket' => array('from' => array('##lang.validation.validationstatus##'), 'to' => array('##lang.validation.status## : ##validation.status##')));
foreach ($updates as $itemtype => $changes) {
$query = "SELECT `glpi_notificationtemplatetranslations`.*\n FROM `glpi_notificationtemplatetranslations`\n INNER JOIN `glpi_notificationtemplates`\n ON (`glpi_notificationtemplates`.`id`\n = `glpi_notificationtemplatetranslations`.`notificationtemplates_id`)\n WHERE `glpi_notificationtemplates`.`itemtype` = '{$itemtype}'";
if ($result = $DB->query($query)) {
if ($DB->numrows($result)) {
while ($data = $DB->fetch_assoc($result)) {
$query = "UPDATE `glpi_notificationtemplatetranslations`\n SET `subject` = '" . addslashes(str_replace($changes['from'], $changes['to'], $data['subject'])) . "',\n `content_text` = '" . addslashes(str_replace($changes['from'], $changes['to'], $data['content_text'])) . "',\n `content_html` = '" . addslashes(str_replace($changes['from'], $changes['to'], $data['content_html'])) . "'\n WHERE `id` = '" . $data['id'] . "'";
$DB->queryOrDie($query, "0.80 fix template tag usage for {$itemtype}");
}
}
}
}
$migration->addField('glpi_profiles', 'update_own_followups', 'char(1) COLLATE utf8_unicode_ci DEFAULT NULL', array('update' => '1', 'condition' => " WHERE `update_followups` = 1"));
$migration->addField('glpi_profiles', 'delete_followups', 'char(1) COLLATE utf8_unicode_ci DEFAULT NULL', array('update' => '`update_followups`'));
$migration->addField('glpi_ocsservers', 'deleted_behavior', "VARCHAR( 255 ) NOT NULL DEFAULT '1'");
//User registration number
$migration->addField('glpi_users', 'registration_number', 'VARCHAR( 255 ) COLLATE utf8_unicode_ci DEFAULT NULL');
$migration->addField('glpi_authldaps', 'registration_number_field', 'VARCHAR( 255 ) COLLATE utf8_unicode_ci DEFAULT NULL');
$migration->addField("glpi_users", "date_sync", "datetime default NULL AFTER `date_mod`", array('update' => "`date_mod`", 'condition' => " WHERE `auths_id` > 0"));
//Migrate OCS computers link from static config to rules engine
if (FieldExists('glpi_ocsservers', 'is_glpi_link_enabled', false)) {
$ocs_servers = getAllDatasFromTable('glpi_ocsservers');
$ranking = 1;
foreach ($ocs_servers as $ocs_server) {
if ($ocs_server['is_glpi_link_enabled']) {
$query = "INSERT INTO `glpi_rules`\n (`entities_id`, `sub_type`, `ranking`, `name`,\n `description`, `match`, `is_active`, `date_mod`, `is_recursive`)\n VALUES ('0', 'RuleImportComputer', '{$ranking}', '" . $ocs_server['name'] . "',\n '', 'AND', 1, NOW(), 1)";
$DB->queryOrDie($query, "0.80 add new rule RuleImportComputer");
$rule_id = $DB->insert_id();
$query = "INSERT INTO `glpi_rulecriterias`\n (`rules_id`, `criteria`, `condition`, `pattern`)\n VALUES ('{$rule_id}', 'ocsservers_id', '0', '" . $ocs_server['id'] . "')";
$DB->queryOrDie($query, "0.80 add new criteria RuleImportComputer");
if ($ocs_server['states_id_linkif']) {
$query = "INSERT INTO `glpi_rulecriterias`\n (`rules_id`, `criteria`, `condition`,\n `pattern`)\n VALUES ('{$rule_id}', 'states_id', '0',\n '" . $ocs_server['states_id_linkif'] . "')";
$DB->queryOrDie($query, "0.80 add new criteria RuleImportComputer");
}
$simple_criteria = array('use_ip_to_link' => 'IPADDRESS', 'use_mac_to_link' => 'MACADDRESS', 'use_serial_to_link' => 'serial');
foreach ($simple_criteria as $field => $value) {
$tmpcriteria = array();
if ($ocs_server[$field]) {
$query = "INSERT INTO `glpi_rulecriterias`\n (`rules_id`, `criteria`, `condition`, `pattern`)\n VALUES ('{$rule_id}', '{$value}', '10', '1')";
$DB->queryOrDie($query, "0.80 add new criteria RuleImportComputer");
}
}
$tmpcriteria = array();
$query = "INSERT INTO `glpi_rulecriterias`\n (`rules_id`, `criteria`, `condition`, `pattern`)";
switch ($ocs_server['use_name_to_link']) {
case 1:
$query .= "VALUES ('{$rule_id}', 'name', '10', '1')";
$DB->query($query);
break;
case 2:
$query .= "VALUES ('{$rule_id}', 'name', '30', '1')";
$DB->query($query);
break;
}
$query = "INSERT INTO `glpi_ruleactions`\n (`rules_id`, `action_type`, `field`, `value`)\n VALUES ('{$rule_id}', 'assign', '_fusion', '0')";
$DB->queryOrDie($query, "0.80 add new action RuleImportComputer");
$ranking++;
}
}
$todrop = array('is_glpi_link_enabled', 'states_id_linkif', 'use_ip_to_link', 'use_mac_to_link', 'use_name_to_link', 'use_serial_to_link');
foreach ($todrop as $field) {
$migration->dropField('glpi_ocsservers', $field);
}
}
/* New automatic transfert feature */
$migration->addField('glpi_configs', 'transfers_id_auto', 'int(11) NOT NULL DEFAULT 0');
$migration->addField('glpi_ocslinks', 'tag', 'varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL');
/* END - New automatic transfert feature */
$migration->addField('glpi_profiles', 'entity_helpdesk', 'char(1) COLLATE utf8_unicode_ci DEFAULT NULL', array('update' => '`notification`'));
$migration->addField('glpi_computers', 'uuid', 'varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL');
$migration->addField('glpi_ocsservers', 'import_vms', 'TINYINT(1) NOT NULL DEFAULT 0');
$migration->addField('glpi_ocsservers', 'import_general_uuid', 'TINYINT(1) NOT NULL DEFAULT 0');
$migration->addField('glpi_ocslinks', 'import_vm', 'LONGTEXT COLLATE utf8_unicode_ci DEFAULT NULL');
if (!TableExists('glpi_virtualmachinetypes')) {
$query = "CREATE TABLE `glpi_virtualmachinetypes` (\n `id` INT(11) NOT NULL AUTO_INCREMENT,\n `name` VARCHAR(255) NOT NULL DEFAULT '',\n `comment` TEXT NOT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE = MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 add new table glpi_virtualmachinetypes");
}
if (!TableExists('glpi_virtualmachinesystems')) {
$query = "CREATE TABLE `glpi_virtualmachinesystems` (\n `id` INT(11) NOT NULL AUTO_INCREMENT,\n `name` VARCHAR(255) NOT NULL DEFAULT '',\n `comment` TEXT NOT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE = MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 add new table glpi_virtualmachinesystems");
}
if (!TableExists('glpi_virtualmachinestates')) {
$query = "CREATE TABLE `glpi_virtualmachinestates` (\n `id` INT(11) NOT NULL AUTO_INCREMENT,\n `name` VARCHAR(255) NOT NULL DEFAULT '',\n `comment` TEXT NOT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE = MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 add new table glpi_virtualmachinestates");
}
if (!TableExists('glpi_computervirtualmachines')) {
$query = "CREATE TABLE `glpi_computervirtualmachines` (\n `id` INT NOT NULL AUTO_INCREMENT,\n `entities_id` INT(11) NOT NULL DEFAULT '0',\n `computers_id` INT(11) NOT NULL DEFAULT '0',\n `name` VARCHAR(255) NOT NULL DEFAULT '',\n `virtualmachinestates_id` INT(11) NOT NULL DEFAULT '0',\n `virtualmachinesystems_id` INT(11) NOT NULL DEFAULT '0',\n `virtualmachinetypes_id` INT(11) NOT NULL DEFAULT '0',\n `uuid` VARCHAR(255) NOT NULL DEFAULT '',\n `vcpu` INT(11) NOT NULL DEFAULT '0',\n `ram` VARCHAR(255) NOT NULL DEFAULT '',\n PRIMARY KEY (`id`)\n ) ENGINE = MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$DB->queryOrDie($query, "0.80 add new table glpi_computervirtualmachines");
}
// Clean ticket validations
$query = "DELETE\n FROM `glpi_ticketvalidations`\n WHERE `glpi_ticketvalidations`.`tickets_id` NOT IN (SELECT `glpi_tickets`.`id`\n FROM `glpi_tickets`)";
$DB->queryOrDie($query, "0.80 clean glpi_ticketvalidations");
// Keep it at the end
$migration->displayMessage(sprintf(__('Data migration - %s'), 'glpi_displaypreferences'));
foreach ($ADDTODISPLAYPREF as $type => $tab) {
$query = "SELECT DISTINCT users_id\n FROM `glpi_displaypreferences`\n WHERE `itemtype` = '{$type}'";
if ($result = $DB->query($query)) {
if ($DB->numrows($result) > 0) {
while ($data = $DB->fetch_assoc($result)) {
$query = "SELECT MAX(`rank`)\n FROM `glpi_displaypreferences`\n WHERE `users_id` = '" . $data['users_id'] . "'\n AND `itemtype` = '{$type}'";
$result = $DB->query($query);
$rank = $DB->result($result, 0, 0);
$rank++;
foreach ($tab as $newval) {
$query = "SELECT *\n FROM `glpi_displaypreferences`\n WHERE `users_id` = '" . $data['users_id'] . "'\n AND `num` = '{$newval}'\n AND `itemtype` = '{$type}'";
if ($result2 = $DB->query($query)) {
if ($DB->numrows($result2) == 0) {
$query = "INSERT INTO `glpi_displaypreferences`\n (`itemtype` ,`num` ,`rank` ,`users_id`)\n VALUES ('{$type}', '{$newval}', '" . $rank++ . "',\n '" . $data['users_id'] . "')";
$DB->query($query);
}
}
}
}
} else {
// Add for default user
$rank = 1;
foreach ($tab as $newval) {
$query = "INSERT INTO `glpi_displaypreferences`\n (`itemtype` ,`num` ,`rank` ,`users_id`)\n VALUES ('{$type}', '{$newval}', '" . $rank++ . "', '0')";
$DB->query($query);
}
}
}
}
// Change value of autoclose ticket
$query = "UPDATE `glpi_configs`\n SET `autoclose_delay` = '-1'\n WHERE `id` = '1'\n AND `autoclose_delay` = '0'";
$DB->queryOrDie($query, "0.80 change autoclose ticket in glpi_configs");
$query = "UPDATE `glpi_entitydatas`\n SET `autoclose_delay` = '-2'\n WHERE `autoclose_delay` = '-1'";
$DB->queryOrDie($query, "0.80 change autoclose ticket in glpi_entitydatas for inherit");
$query = "UPDATE `glpi_entitydatas`\n SET `autoclose_delay` = '-1'\n WHERE `autoclose_delay` = '0'";
$DB->queryOrDie($query, "0.80 change autoclose ticket in glpi_entitydatas for inactive");
// must always be at the end
$migration->executeMigration();
return $updateresult;
}
