/** * 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; }