static function geTimelineItems(Ticket $ticket, $rand) { global $DB, $CFG_GLPI; $timeline = array(); $user = new User(); $group = new Group(); $followup_obj = new TicketFollowup(); $task_obj = new TicketTask(); $document_item_obj = new Document_Item(); $ticket_valitation_obj = new TicketValidation(); //checks rights $showpublic = Session::haveRightsOr("followup", array(TicketFollowup::SEEPUBLIC, TicketFollowup::SEEPRIVATE)) && Session::haveRightsOr("task", array(TicketTask::SEEPUBLIC, TicketTask::SEEPRIVATE)); $restrict_fup = $restrict_task = ""; if (!Session::haveRight("ticket", TicketFollowup::SEEPRIVATE)) { $restrict_fup = " AND (`is_private` = '0'\n OR `users_id` ='" . Session::getLoginUserID() . "') "; } if (!Session::haveRight("ticket", TicketTask::SEEPRIVATE)) { $restrict_task = " AND (`is_private` = '0'\n OR `users_id` ='" . Session::getLoginUserID() . "') "; } if (!$showpublic) { $restrict = " AND 1 = 0"; } //add ticket followups to timeline $followups = $followup_obj->find("tickets_id = " . $ticket->getID() . " {$restrict_fup}", 'date DESC'); foreach ($followups as $followups_id => $followup) { $followup_obj->getFromDB($followups_id); $can_edit = $followup_obj->canUpdateItem(); $followup['can_edit'] = $can_edit; $timeline[$followup['date'] . "_followup_" . $followups_id] = array('type' => 'TicketFollowup', 'item' => $followup); } //add ticket tasks to timeline $tasks = $task_obj->find("tickets_id = " . $ticket->getID() . " {$restrict_task}", 'date DESC'); foreach ($tasks as $tasks_id => $task) { $task_obj->getFromDB($tasks_id); $can_edit = $task_obj->canUpdateItem(); $task['can_edit'] = $can_edit; $timeline[$task['date'] . "_task_" . $tasks_id] = array('type' => 'TicketTask', 'item' => $task); } //add ticket documents to timeline $document_obj = new Document(); $document_items = $document_item_obj->find("itemtype = 'Ticket' AND items_id = " . $ticket->getID()); foreach ($document_items as $document_item) { $document_obj->getFromDB($document_item['documents_id']); $timeline[$document_obj->fields['date_mod'] . "_document_" . $document_item['documents_id']] = array('type' => 'Document_Item', 'item' => $document_obj->fields); } //add assign changes /*$log_obj = new Log; $gassign_items = $log_obj->find("itemtype = 'Ticket' AND items_id = ".$ticket->getID()." AND itemtype_link = 'Group' AND linked_action = '15'"); foreach ($gassign_items as $logs_id => $gassign) { //find group $group_name = preg_replace("#(.*)\s\([0-9]*\)#", "$1", $gassign['new_value']); $groups = $group->find("name = '$group_name'"); $first_group = array_shift($groups); $group->getFromDB($first_group['id']); $content = __("Assigned to")." : ". "<img src='".$CFG_GLPI['root_doc']."/plugins/talk/pics/group.png' class='group_assign' />". " <strong>".$group->getLink()."</strong>"; //find user $user_name = preg_replace("#(.*)\s\([0-9]*\)#", "$1", $gassign['user_name']); $users = $user->find("CONCAT(firstname, ' ', realname) = '$user_name'"); $first_user = array_shift($users); if ($first_user == NULL) { $first_user['id'] = false; } $timeline[$gassign['date_mod']."_assign_".$logs_id] = array('type' => 'Assign', 'item' => array( 'date' => $gassign['date_mod'], 'content' => $content, 'can_edit' => false, 'users_id' => $first_user['id'] )); }*/ //add existing solution if (!empty($ticket->fields['solution'])) { $users_id = 0; $solution_date = $ticket->fields['solvedate']; //search date and user of last solution in glpi_logs if ($res_solution = $DB->query("SELECT date_mod AS solution_date, user_name FROM glpi_logs\n WHERE itemtype = 'Ticket' \n AND items_id = " . $ticket->getID() . "\n AND id_search_option = 24\n ORDER BY id DESC\n LIMIT 1")) { $data_solution = $DB->fetch_assoc($res_solution); if (!empty($data_solution['solution_date'])) { $solution_date = $data_solution['solution_date']; } // find user if (!empty($data_solution['user_name'])) { $users_id = addslashes(trim(preg_replace("/.*\\(([0-9]+)\\)/", "\$1", $data_solution['user_name']))); } } // fix trouble with html_entity_decode who skip accented characters (on windows browser) $solution_content = preg_replace_callback("/(&#[0-9]+;)/", function ($m) { return mb_convert_encoding($m[1], "UTF-8", "HTML-ENTITIES"); }, $ticket->fields['solution']); $timeline[$solution_date . "_solution"] = array('type' => 'Solution', 'item' => array('id' => 0, 'content' => Html::clean(html_entity_decode($solution_content)), 'date' => $solution_date, 'users_id' => $users_id, 'solutiontypes_id' => $ticket->fields['solutiontypes_id'], 'can_edit' => Ticket::canUpdate() && $ticket->canSolve())); } // add ticket validation to timeline if ($ticket->fields['type'] == Ticket::DEMAND_TYPE && (Session::haveRight('ticketvalidation', TicketValidation::VALIDATEREQUEST) || Session::haveRight('ticketvalidation', TicketValidation::CREATEREQUEST)) || $ticket->fields['type'] == Ticket::INCIDENT_TYPE && (Session::haveRight('ticketvalidation', TicketValidation::VALIDATEINCIDENT) || Session::haveRight('ticketvalidation', TicketValidation::CREATEINCIDENT))) { $ticket_validations = $ticket_valitation_obj->find('tickets_id = ' . $ticket->getID()); foreach ($ticket_validations as $validations_id => $validation) { $canedit = $ticket_valitation_obj->can($validations_id, UPDATE); $user->getFromDB($validation['users_id_validate']); $timeline[$validation['submission_date'] . "_validation_" . $validations_id] = array('type' => 'TicketValidation', 'item' => array('id' => $validations_id, 'date' => $validation['submission_date'], 'content' => __('Validation request') . " => " . $user->getlink() . "<br>" . $validation['comment_submission'], 'users_id' => $validation['users_id'], 'can_edit' => $canedit)); if (!empty($validation['validation_date'])) { $timeline[$validation['validation_date'] . "_validation_" . $validations_id] = array('type' => 'TicketValidation', 'item' => array('id' => $validations_id, 'date' => $validation['validation_date'], 'content' => __('Validation request answer') . " : " . _sx('status', ucfirst(TicketValidation::getStatus($validation['status']))) . "<br>" . $validation['comment_validation'], 'users_id' => $validation['users_id_validate'], 'status' => "status_" . $validation['status'], 'can_edit' => $canedit)); } } } //reverse sort timeline items by key (date) krsort($timeline); return $timeline; }
function __construct($entity = '', $event = '', $object = null, $options = array()) { parent::__construct($entity, $event, $object, $options); $this->options['sendprivate'] = false; if (isset($options['followup_id'])) { $fup = new TicketFollowup(); if ($fup->getFromDB($options['followup_id'])) { if ($fup->fields['is_private']) { $this->options['sendprivate'] = true; } } } if (isset($options['task_id'])) { $fup = new TicketTask(); if ($fup->getFromDB($options['task_id'])) { if ($fup->fields['is_private']) { $this->options['sendprivate'] = true; } } } }
/** * @since version 0.90 * **/ function getTimelineItems() { global $DB, $CFG_GLPI; $timeline = array(); $user = new User(); $group = new Group(); $followup_obj = new TicketFollowup(); $task_obj = new TicketTask(); $document_item_obj = new Document_Item(); $ticket_valitation_obj = new TicketValidation(); //checks rights $showpublic = Session::haveRightsOr("followup", array(TicketFollowup::SEEPUBLIC, TicketFollowup::SEEPRIVATE)) || Session::haveRightsOr("task", array(TicketTask::SEEPUBLIC, TicketTask::SEEPRIVATE)); $restrict_fup = $restrict_task = ""; if (!Session::haveRight("followup", TicketFollowup::SEEPRIVATE)) { $restrict_fup = " AND (`is_private` = '0'\n OR `users_id` ='" . Session::getLoginUserID() . "') "; } if (!Session::haveRight("task", TicketTask::SEEPRIVATE)) { $restrict_task = " AND (`is_private` = '0'\n OR `users_id` ='" . Session::getLoginUserID() . "') "; } if (!$showpublic) { $restrict = " AND 1 = 0"; } //add ticket followups to timeline if ($followup_obj->canview()) { $followups = $followup_obj->find("tickets_id = " . $this->getID() . " {$restrict_fup}", 'date DESC'); foreach ($followups as $followups_id => $followup) { $followup_obj->getFromDB($followups_id); $followup['can_edit'] = $followup_obj->canUpdateItem(); $timeline[$followup['date'] . "_followup_" . $followups_id] = array('type' => 'TicketFollowup', 'item' => $followup); } } //add ticket tasks to timeline if ($task_obj->canview()) { $tasks = $task_obj->find("tickets_id = " . $this->getID() . " {$restrict_task}", 'date DESC'); foreach ($tasks as $tasks_id => $task) { $task_obj->getFromDB($tasks_id); $task['can_edit'] = $task_obj->canUpdateItem(); $timeline[$task['date'] . "_task_" . $tasks_id] = array('type' => 'TicketTask', 'item' => $task); } } //add ticket documents to timeline $document_obj = new Document(); $document_items = $document_item_obj->find("itemtype = 'Ticket' AND items_id = " . $this->getID()); foreach ($document_items as $document_item) { $document_obj->getFromDB($document_item['documents_id']); $timeline[$document_obj->fields['date_mod'] . "_document_" . $document_item['documents_id']] = array('type' => 'Document_Item', 'item' => $document_obj->fields); } //add existing solution if (!empty($this->fields['solution']) || !empty($this->fields['solutiontypes_id'])) { $users_id = 0; $solution_date = $this->fields['solvedate']; //search date and user of last solution in glpi_logs if ($res_solution = $DB->query("SELECT `date_mod` AS solution_date, `user_name`, `id`\n FROM `glpi_logs`\n WHERE `itemtype` = 'Ticket'\n AND `items_id` = " . $this->getID() . "\n AND `id_search_option` = 24\n ORDER BY `id` DESC\n LIMIT 1")) { $data_solution = $DB->fetch_assoc($res_solution); if (!empty($data_solution['solution_date'])) { $solution_date = $data_solution['solution_date']; } // find user if (!empty($data_solution['user_name'])) { $users_id = addslashes(trim(preg_replace("/.*\\(([0-9]+)\\)/", "\$1", $data_solution['user_name']))); } } // fix trouble with html_entity_decode who skip accented characters (on windows browser) $solution_content = preg_replace_callback("/(&#[0-9]+;)/", function ($m) { return mb_convert_encoding($m[1], "UTF-8", "HTML-ENTITIES"); }, $this->fields['solution']); $timeline[$solution_date . "_solution"] = array('type' => 'Solution', 'item' => array('id' => 0, 'content' => Toolbox::unclean_cross_side_scripting_deep($solution_content), 'date' => $solution_date, 'users_id' => $users_id, 'solutiontypes_id' => $this->fields['solutiontypes_id'], 'can_edit' => Ticket::canUpdate() && $this->canSolve())); } // add ticket validation to timeline if ($this->fields['type'] == Ticket::DEMAND_TYPE && (Session::haveRight('ticketvalidation', TicketValidation::VALIDATEREQUEST) || Session::haveRight('ticketvalidation', TicketValidation::CREATEREQUEST)) || $this->fields['type'] == Ticket::INCIDENT_TYPE && (Session::haveRight('ticketvalidation', TicketValidation::VALIDATEINCIDENT) || Session::haveRight('ticketvalidation', TicketValidation::CREATEINCIDENT))) { $ticket_validations = $ticket_valitation_obj->find('tickets_id = ' . $this->getID()); foreach ($ticket_validations as $validations_id => $validation) { $canedit = $ticket_valitation_obj->can($validations_id, UPDATE); $user->getFromDB($validation['users_id_validate']); $timeline[$validation['submission_date'] . "_validation_" . $validations_id] = array('type' => 'TicketValidation', 'item' => array('id' => $validations_id, 'date' => $validation['submission_date'], 'content' => __('Validation request') . " => " . $user->getlink() . "<br>" . $validation['comment_submission'], 'users_id' => $validation['users_id'], 'can_edit' => $canedit)); if (!empty($validation['validation_date'])) { $timeline[$validation['validation_date'] . "_validation_" . $validations_id] = array('type' => 'TicketValidation', 'item' => array('id' => $validations_id, 'date' => $validation['validation_date'], 'content' => __('Validation request answer') . " : " . _sx('status', ucfirst(TicketValidation::getStatus($validation['status']))) . "<br>" . $validation['comment_validation'], 'users_id' => $validation['users_id_validate'], 'status' => "status_" . $validation['status'], 'can_edit' => $canedit)); } } } //reverse sort timeline items by key (date) krsort($timeline); return $timeline; }
/** * Update from 0.80.3 to 0.83 * * @return bool for success (will die for most error) **/ function update0803to083() { global $DB, $migration; $updateresult = true; $ADDTODISPLAYPREF = array(); //TRANS: %s is the number of new version $migration->displayTitle(sprintf(__('Update to %s'), '0.83')); $migration->setVersion('0.83'); $backup_tables = false; $newtables = array('glpi_entities_knowbaseitems', 'glpi_entities_reminders', 'glpi_groups_problems', 'glpi_groups_knowbaseitems', 'glpi_groups_reminders', 'glpi_knowbaseitems_profiles', 'glpi_knowbaseitems_users', 'glpi_items_problems', 'glpi_problems', 'glpi_problemtasks', 'glpi_problems_ticket', 'glpi_problems_users', 'glpi_profiles_reminders', 'glpi_reminders_users', 'glpi_ticketrecurrents', 'glpi_tickettemplates', 'glpi_tickettemplatehiddenfields', 'glpi_tickettemplatemandatoryfields', 'glpi_tickettemplatepredefinedfields', 'glpi_useremails'); foreach ($newtables as $new_table) { // rename new tables if exists ? if (TableExists($new_table)) { $migration->dropTable("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); } //TRANS: %s is the table or item implied $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Problems')); // Clean ticket validations : already done in 0.80 $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.83 clean glpi_ticketvalidations"); // Problems management if (!TableExists('glpi_problems')) { $query = "CREATE TABLE `glpi_problems` (\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 `is_deleted` tinyint(1) NOT NULL DEFAULT '0',\n `status` varchar(255) DEFAULT NULL,\n `content` longtext DEFAULT NULL,\n `date_mod` DATETIME DEFAULT NULL,\n `date` DATETIME DEFAULT NULL,\n `solvedate` DATETIME DEFAULT NULL,\n `closedate` DATETIME DEFAULT NULL,\n `due_date` DATETIME DEFAULT NULL,\n `users_id_recipient` int(11) NOT NULL DEFAULT '0',\n `users_id_lastupdater` int(11) NOT NULL DEFAULT '0',\n `suppliers_id_assign` int(11) NOT NULL DEFAULT '0',\n `urgency` int(11) NOT NULL DEFAULT '1',\n `impact` int(11) NOT NULL DEFAULT '1',\n `priority` int(11) NOT NULL DEFAULT '1',\n `itilcategories_id` int(11) NOT NULL DEFAULT '0',\n `impactcontent` longtext DEFAULT NULL,\n `causecontent` longtext DEFAULT NULL,\n `symptomcontent` longtext DEFAULT NULL,\n `solutiontypes_id` int(11) NOT NULL DEFAULT '0',\n `solution` text COLLATE utf8_unicode_ci,\n `actiontime` int(11) NOT NULL DEFAULT '0',\n `begin_waiting_date` datetime DEFAULT NULL,\n `waiting_duration` int(11) NOT NULL DEFAULT '0',\n `close_delay_stat` int(11) NOT NULL DEFAULT '0',\n `solve_delay_stat` int(11) NOT NULL DEFAULT '0',\n `notepad` LONGTEXT NULL,\n PRIMARY KEY (`id`),\n KEY `name` (`name`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`),\n KEY `is_deleted` (`is_deleted`),\n KEY `date` (`date`),\n KEY `closedate` (`closedate`),\n KEY `status` (`status`(1)),\n KEY `priority` (`priority`),\n KEY `date_mod` (`date_mod`),\n KEY `suppliers_id_assign` (`suppliers_id_assign`),\n KEY `itilcategories_id` (`itilcategories_id`),\n KEY `users_id_recipient` (`users_id_recipient`),\n KEY `solvedate` (`solvedate`),\n KEY `solutiontypes_id` (`solutiontypes_id`),\n KEY `urgency` (`urgency`),\n KEY `impact` (`impact`),\n KEY `due_date` (`due_date`),\n KEY `users_id_lastupdater` (`users_id_lastupdater`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 create glpi_problems"); $ADDTODISPLAYPREF['Problem'] = array(21, 12, 19, 15, 3, 7, 18); } if (FieldExists('glpi_tickets', 'ticket_waiting_duration', false)) { $migration->changeField('glpi_tickets', 'ticket_waiting_duration', 'waiting_duration', 'integer'); } if (!TableExists('glpi_problems_users')) { $query = "CREATE TABLE `glpi_problems_users` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `problems_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 UNIQUE KEY `unicity` (`problems_id`,`type`,`users_id`,`alternative_email`),\n KEY `user` (`users_id`,`type`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_problems_users"); } if (!TableExists('glpi_groups_problems')) { $query = "CREATE TABLE `glpi_groups_problems` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `problems_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 UNIQUE KEY `unicity` (`problems_id`,`type`,`groups_id`),\n KEY `group` (`groups_id`,`type`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_groups_problems"); } if (!TableExists('glpi_items_problems')) { $query = "CREATE TABLE `glpi_items_problems` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `problems_id` int(11) NOT NULL DEFAULT '0',\n `itemtype` varchar(100) default NULL,\n `items_id` int(11) NOT NULL DEFAULT '0',\n PRIMARY KEY (`id`),\n UNIQUE KEY `unicity` (`problems_id`,`itemtype`,`items_id`),\n KEY `item` (`itemtype`,`items_id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_items_problems"); } if (!TableExists('glpi_problems_tickets')) { $query = "CREATE TABLE `glpi_problems_tickets` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `problems_id` int(11) NOT NULL DEFAULT '0',\n `tickets_id` int(11) NOT NULL DEFAULT '0',\n PRIMARY KEY (`id`),\n UNIQUE KEY `unicity` (`problems_id`,`tickets_id`),\n KEY `tickets_id` (`tickets_id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_problems_tickets"); } if (!TableExists('glpi_problemtasks')) { $query = "CREATE TABLE `glpi_problemtasks` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `problems_id` int(11) NOT NULL DEFAULT '0',\n `taskcategories_id` int(11) NOT NULL DEFAULT '0',\n `date` datetime DEFAULT NULL,\n `begin` datetime DEFAULT NULL,\n `end` datetime DEFAULT NULL,\n `users_id` int(11) NOT NULL DEFAULT '0',\n `users_id_tech` int(11) NOT NULL DEFAULT '0',\n `content` longtext COLLATE utf8_unicode_ci,\n `actiontime` int(11) NOT NULL DEFAULT '0',\n `state` int(11) NOT NULL DEFAULT '0',\n PRIMARY KEY (`id`),\n KEY `problems_id` (`problems_id`),\n KEY `users_id` (`users_id`),\n KEY `users_id_tech` (`users_id_tech`),\n KEY `date` (`date`),\n KEY `begin` (`begin`),\n KEY `end` (`end`),\n KEY `state` (`state`),\n KEY `taskcategories_id` (taskcategories_id)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_problemtasks"); } $migration->addField("glpi_profiles", "show_my_problem", "char", array('update' => "1", 'condition' => " WHERE `own_ticket` = 1")); $migration->addField("glpi_profiles", "show_all_problem", "char", array('update' => "1", 'condition' => " WHERE `show_all_ticket` = 1")); $migration->addField("glpi_profiles", "edit_all_problem", "char", array('update' => "1", 'condition' => " WHERE `update_ticket` = 1")); $migration->changeField("glpi_profiles", 'helpdesk_status', 'ticket_status', "text", array('comment' => "json encoded array of from/dest allowed status change")); $migration->addField('glpi_profiles', 'problem_status', "text", array('comment' => "json encoded array of from/dest allowed status change")); $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Changes')); $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'TicketPlanning')); // Merge tickettasks and ticket planning if (TableExists('glpi_ticketplannings')) { $migration->addField("glpi_tickettasks", "begin", "datetime"); $migration->addField("glpi_tickettasks", "end", "datetime"); $migration->addField("glpi_tickettasks", "state", "integer", array('value' => '1')); $migration->addField("glpi_tickettasks", "users_id_tech", "integer"); $migration->migrationOneTable('glpi_tickettasks'); // migrate DATA $task = new TicketTask(); foreach ($DB->request('glpi_ticketplannings') as $data) { if ($task->getFromDB($data['tickettasks_id'])) { $query = "UPDATE `glpi_tickettasks`\n SET `begin` = " . ($data['begin'] == 'NULL' || is_null($data['begin']) ? 'NULL' : "'" . $data['begin'] . "'") . ",\n `end` = " . ($data['end'] == 'NULL' || is_null($data['end']) ? 'NULL' : "'" . $data['end'] . "'") . ",\n `users_id_tech` = '" . $data['users_id'] . "',\n `state` = '" . $data['state'] . "'\n WHERE `id` = '" . $data['tickettasks_id'] . "'"; $DB->queryOrDie($query, "0.83 migrate planning to glpi_tickettasks"); } } $migration->dropTable("glpi_ticketplannings"); $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Notification')); // Migrate templates $from = array('task.planning.user##', 'task.planning.begin##', 'task.planning.end##', 'task.planning.status##'); $to = array('task.user##', 'task.begin##', 'task.end##', 'task.status##'); $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.83 fix tags usage for multi users"); } } } } $query = "SELECT *\n FROM `glpi_notificationtemplates`\n WHERE `name` = 'Problems'"; if ($result = $DB->query($query)) { if ($DB->numrows($result) == 0) { $query = "INSERT INTO `glpi_notificationtemplates`\n (`name`, `itemtype`, `date_mod`)\n VALUES ('Problems', 'Problem', NOW())"; $DB->queryOrDie($query, "0.83 add problem notification"); $notid = $DB->insert_id(); $query = "INSERT INTO `glpi_notificationtemplatetranslations`\n (`notificationtemplates_id`, `language`, `subject`,\n `content_text`,\n `content_html`)\n VALUES ({$notid}, '', '##problem.action## ##problem.title##',\n '##IFproblem.storestatus=solved##\n ##lang.problem.url## : ##problem.urlapprove##\n ##lang.problem.solvedate## : ##problem.solvedate##\n ##lang.problem.solution.type## : ##problem.solution.type##\n ##lang.problem.solution.description## : ##problem.solution.description## ##ENDIFproblem.storestatus##\n ##ELSEproblem.storestatus## ##lang.problem.url## : ##problem.url## ##ENDELSEproblem.storestatus##\n\n ##lang.problem.description##\n\n ##lang.problem.title##  :##problem.title##\n ##lang.problem.authors##  :##IFproblem.authors## ##problem.authors## ##ENDIFproblem.authors## ##ELSEproblem.authors##--##ENDELSEproblem.authors##\n ##lang.problem.creationdate##  :##problem.creationdate##\n ##IFproblem.assigntousers## ##lang.problem.assigntousers##  : ##problem.assigntousers## ##ENDIFproblem.assigntousers##\n ##lang.problem.status##  : ##problem.status##\n ##IFproblem.assigntogroups## ##lang.problem.assigntogroups##  : ##problem.assigntogroups## ##ENDIFproblem.assigntogroups##\n ##lang.problem.urgency##  : ##problem.urgency##\n ##lang.problem.impact##  : ##problem.impact##\n ##lang.problem.priority## : ##problem.priority##\n##IFproblem.category## ##lang.problem.category##  :##problem.category## ##ENDIFproblem.category## ##ELSEproblem.category## ##lang.problem.nocategoryassigned## ##ENDELSEproblem.category##\n ##lang.problem.content##  : ##problem.content##\n\n##IFproblem.storestatus=closed##\n ##lang.problem.solvedate## : ##problem.solvedate##\n ##lang.problem.solution.type## : ##problem.solution.type##\n ##lang.problem.solution.description## : ##problem.solution.description##\n##ENDIFproblem.storestatus##\n ##lang.problem.numberoftickets## : ##problem.numberoftickets##\n\n##FOREACHtickets##\n [##ticket.date##] ##lang.problem.title## : ##ticket.title##\n ##lang.problem.content## ##ticket.content##\n\n##ENDFOREACHtickets##\n ##lang.problem.numberoftasks## : ##problem.numberoftasks##\n\n##FOREACHtasks##\n [##task.date##]\n ##lang.task.author## ##task.author##\n ##lang.task.description## ##task.description##\n ##lang.task.time## ##task.time##\n ##lang.task.category## ##task.category##\n\n##ENDFOREACHtasks##\n',\n '<p>##IFproblem.storestatus=solved##</p>\n<div>##lang.problem.url## : <a href=\"##problem.urlapprove##\">##problem.urlapprove##</a></div>\n<div><span style=\"color: #888888;\"><strong><span style=\"text-decoration: underline;\">##lang.problem.solvedate##</span></strong></span> : ##problem.solvedate##<br /><span style=\"text-decoration: underline; color: #888888;\"><strong>##lang.problem.solution.type##</strong></span> : ##problem.solution.type##<br /><span style=\"text-decoration: underline; color: #888888;\"><strong>##lang.problem.solution.description##</strong></span> : ##problem.solution.description## ##ENDIFproblem.storestatus##</div>\n<div>##ELSEproblem.storestatus## ##lang.problem.url## : <a href=\"##problem.url##\">##problem.url##</a> ##ENDELSEproblem.storestatus##</div>\n<p class=\"description b\"><strong>##lang.problem.description##</strong></p>\n<p><span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.problem.title##</span> :##problem.title## <br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.problem.authors##</span> :##IFproblem.authors## ##problem.authors## ##ENDIFproblem.authors## ##ELSEproblem.authors##--##ENDELSEproblem.authors## <br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.problem.creationdate##</span> :##problem.creationdate## <br /> ##IFproblem.assigntousers## <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.problem.assigntousers##</span> : ##problem.assigntousers## ##ENDIFproblem.assigntousers##<br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\">##lang.problem.status## </span> : ##problem.status##<br /> ##IFproblem.assigntogroups## <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.problem.assigntogroups##</span> : ##problem.assigntogroups## ##ENDIFproblem.assigntogroups##<br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.problem.urgency##</span> : ##problem.urgency##<br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.problem.impact##</span> : ##problem.impact##<br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.problem.priority##</span> : ##problem.priority## <br />##IFproblem.category##<span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\">##lang.problem.category## </span> :##problem.category## ##ENDIFproblem.category## ##ELSEproblem.category## ##lang.problem.nocategoryassigned## ##ENDELSEproblem.category## <br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.problem.content##</span> : ##problem.content##</p>\n<p>##IFproblem.storestatus=closed##<br /><span style=\"text-decoration: underline;\"><strong><span style=\"color: #888888;\">##lang.problem.solvedate##</span></strong></span> : ##problem.solvedate##<br /><span style=\"color: #888888;\"><strong><span style=\"text-decoration: underline;\">##lang.problem.solution.type##</span></strong></span> : ##problem.solution.type##<br /><span style=\"text-decoration: underline; color: #888888;\"><strong>##lang.problem.solution.description##</strong></span> : ##problem.solution.description##<br />##ENDIFproblem.storestatus##</p>\n<div class=\"description b\">##lang.problem.numberoftickets## : ##problem.numberoftickets##</div>\n<p>##FOREACHtickets##</p>\n<div><strong> [##ticket.date##] <em>##lang.problem.title## : <a href=\"##ticket.url##\">##ticket.title## </a></em></strong><br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> </span><span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\">##lang.problem.content## </span> ##ticket.content##\n<p>##ENDFOREACHtickets##</p>\n<div class=\"description b\">##lang.problem.numberoftasks## : ##problem.numberoftasks##</div>\n<p>##FOREACHtasks##</p>\n<div class=\"description b\"><strong>[##task.date##] </strong><br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.task.author##</span> ##task.author##<br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.task.description##</span> ##task.description##<br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.task.time##</span> ##task.time##<br /> <span style=\"color: #8b8c8f; font-weight: bold; text-decoration: underline;\"> ##lang.task.category##</span> ##task.category##</div>\n<p>##ENDFOREACHtasks##</p>\n</div>')"; $DB->queryOrDie($query, "0.83 add problem notification translation"); $notifications = array('new' => array(), 'update' => array(Notification::ASSIGN_TECH, Notification::OLD_TECH_IN_CHARGE), 'solved' => array(), 'add_task' => array(), 'update_task' => array(), 'delete_task' => array(), 'closed' => array(), 'delete' => array()); $notif_names = array('new' => 'New Problem', 'update' => 'Update Problem', 'solved' => 'Resolve Problem', 'add_task' => 'Add Task', 'update_task' => 'Update Task', 'delete_task' => 'Delete Task', 'closed' => 'Close Problem', 'delete' => 'Delete Problem'); foreach ($notifications as $key => $val) { $notifications[$key][] = Notification::AUTHOR; $notifications[$key][] = Notification::GLOBAL_ADMINISTRATOR; $notifications[$key][] = Notification::OBSERVER; } foreach ($notifications as $type => $targets) { $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 ('" . $notif_names[$type] . "', 0, 'Problem', '{$type}', 'mail',\n {$notid}, '', 1, 1, NOW())"; $DB->queryOrDie($query, "0.83 add problem {$type} notification"); $notifid = $DB->insert_id(); foreach ($targets as $target) { $query = "INSERT INTO `glpi_notificationtargets`\n (`id`, `notifications_id`, `type`, `items_id`)\n VALUES (NULL, {$notifid}, " . Notification::USER_TYPE . ", {$target});"; $DB->queryOrDie($query, "0.83 add problem {$type} notification target"); } } } } $migration->displayMessage(sprintf(__('Data migration - %s'), 'Clean Vlans')); // Clean `glpi_networkports_vlans` datas (`networkports_id` whithout networkports) $query = "DELETE\n FROM `glpi_networkports_vlans`\n WHERE `networkports_id` NOT IN (SELECT `id`\n FROM `glpi_networkports`)"; $DB->queryOrDie($query, "0.83 clean networkports_vlans datas"); $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Rename Solution objects')); // rename glpi_ticketsolutiontypes to glpi_solutiontypes $migration->renameTable('glpi_ticketsolutiontypes', 'glpi_solutiontypes'); // rename glpi_ticketsolutiontemplates to glpi_solutiontemplates $migration->renameTable('glpi_ticketsolutiontemplates', 'glpi_solutiontemplates'); $migration->changeField('glpi_tickets', 'ticketsolutiontypes_id', 'solutiontypes_id', 'integer'); $migration->changeField('glpi_solutiontemplates', 'ticketsolutiontypes_id', 'solutiontypes_id', 'integer'); $migration->changeField('glpi_tickets_users', 'use_notification', 'use_notification', 'bool', array('value' => '1')); // to have correct name of key $migration->dropKey('glpi_tickets', 'ticketsolutiontypes_id'); $migration->migrationOneTable('glpi_tickets'); $migration->addKey('glpi_tickets', 'solutiontypes_id'); $migration->dropKey('glpi_solutiontemplates', 'ticketsolutiontypes_id'); $migration->migrationOneTable('glpi_solutiontemplates'); $migration->addKey('glpi_solutiontemplates', 'solutiontypes_id'); $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Rename Category objects')); $migration->renameTable('glpi_ticketcategories', 'glpi_itilcategories'); $migration->dropKey('glpi_itilcategories', 'ticketcategories_id'); $migration->changeField('glpi_itilcategories', 'ticketcategories_id', 'itilcategories_id', 'integer'); $migration->migrationOneTable('glpi_itilcategories'); $migration->addKey('glpi_itilcategories', 'itilcategories_id'); $migration->dropKey('glpi_tickets', 'ticketcategories_id'); $migration->changeField('glpi_tickets', 'ticketcategories_id', 'itilcategories_id', 'integer'); $migration->migrationOneTable('glpi_tickets'); $migration->addKey('glpi_tickets', 'itilcategories_id'); // Update Itemtype datas in tables $itemtype_tables = array("glpi_bookmarks", "glpi_bookmarks_users", "glpi_displaypreferences"); $typestochange = array('TicketSolutionTemplate' => 'SolutionTemplate', 'TicketSolutionType' => 'SolutionType', 'TicketCategory' => 'ITILCategory'); foreach ($itemtype_tables as $table) { foreach ($typestochange as $key => $val) { $query = "UPDATE `{$table}`\n SET `itemtype` = '{$val}'\n WHERE `itemtype` = '{$key}'"; $DB->queryOrDie($query, "0.83 update itemtype of table {$table} for {$val}"); } } $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Add various fields')); $migration->addField("glpi_states", 'states_id', "integer"); $migration->addField("glpi_states", 'completename', "text"); $migration->addField("glpi_states", 'level', "integer"); $migration->addField("glpi_states", 'ancestors_cache', "longtext"); $migration->addField("glpi_states", 'sons_cache', "longtext"); $migration->migrationOneTable('glpi_states'); $migration->addKey("glpi_states", array('states_id', 'name'), 'unicity'); regenerateTreeCompleteName("glpi_states"); $migration->addField("glpi_knowbaseitemcategories", 'ancestors_cache', "longtext"); $migration->addField("glpi_knowbaseitemcategories", 'sons_cache', "longtext"); $migration->changeField("glpi_authldaps", 'group_condition', 'group_condition', "text"); $migration->dropKey("glpi_groups", 'ldap_value'); $migration->changeField("glpi_groups", 'ldap_value', 'ldap_value', "text"); $migration->dropKey("glpi_groups", 'ldap_group_dn'); $migration->changeField("glpi_groups", 'ldap_group_dn', 'ldap_group_dn', "text"); $migration->addField("glpi_groups", 'groups_id', "integer"); $migration->addField("glpi_groups", 'completename', "text"); $migration->addField("glpi_groups", 'level', "integer"); $migration->addField("glpi_groups", 'ancestors_cache', "longtext"); $migration->addField("glpi_groups", 'sons_cache', "longtext"); $migration->migrationOneTable('glpi_groups'); $migration->addKey("glpi_groups", 'ldap_value', '', 'INDEX', 200); $migration->addKey("glpi_groups", 'ldap_group_dn', '', 'INDEX', 200); $migration->addKey("glpi_groups", 'groups_id'); regenerateTreeCompleteName("glpi_groups"); $migration->addField("glpi_entitydatas", 'notification_subject_tag', "string", array('after' => 'admin_reply_name')); $migration->addField("glpi_vlans", 'tag', "integer"); $ADDTODISPLAYPREF['Vlan'] = array(11); $migration->addField("glpi_profiles", 'create_ticket_on_login', "bool"); $migration->addField("glpi_reminders", 'begin_view_date', "datetime"); $migration->addField("glpi_reminders", 'end_view_date', "datetime"); // only to change latin1 to utf-8 if not done in update 0.68.3 to 0.71 // because there is an index fulltext based on 2 fields (perhaps both are not in same encoding) $migration->changeField("glpi_knowbaseitems", 'answer', 'answer', "longtext"); $migration->changeField("glpi_knowbaseitems", 'question', 'name', "text"); $migration->addField("glpi_configs", "ajax_min_textsearch_load", "integer", array('after' => 'use_ajax')); $migration->addField("glpi_configs", "use_anonymous_followups", "bool", array('after' => 'use_anonymous_helpdesk')); $migration->addField("glpi_configs", "show_count_on_tabs", "bool", array('value' => '1')); $migration->addField("glpi_users", "show_count_on_tabs", "tinyint(1) NULL DEFAULT NULL"); $migration->addField("glpi_configs", "refresh_ticket_list", "integer"); $migration->addField("glpi_users", "refresh_ticket_list", "int(11) NULL DEFAULT NULL"); $migration->addField("glpi_configs", "set_default_tech", "bool", array('value' => '1')); $migration->addField("glpi_users", "set_default_tech", "tinyint(1) NULL DEFAULT NULL"); $migration->addField("glpi_reservations", "group", "integer"); $migration->addKey("glpi_reservations", array('reservationitems_id', 'group'), "resagroup"); /// Add document types $types = array('csv' => array('name' => 'Comma-Separated Values', 'icon' => 'csv-dist.png'), 'svg' => array('name' => 'Scalable Vector Graphics', 'icon' => 'svg-dist.png')); foreach ($types as $ext => $data) { $query = "SELECT *\n FROM `glpi_documenttypes`\n WHERE `ext` = '{$ext}'"; if ($result = $DB->query($query)) { if ($DB->numrows($result) == 0) { $query = "INSERT INTO `glpi_documenttypes`\n (`name`, `ext`, `icon`, `is_uploadable`, `date_mod`)\n VALUES ('" . $data['name'] . "', '{$ext}', '" . $data['icon'] . "', '1', NOW())"; $DB->queryOrDie($query, "0.83 add document type {$ext}"); } } } /// Update icons $types = array('c' => 'c-dist.png', 'h' => 'h-dist.png', 'swf' => 'swf-dist.png', 'pas' => 'pas-dist.png', 'wmv' => 'wmv-dist.png', 'zip' => 'zip-dist.png'); foreach ($types as $ext => $icon) { $query = "SELECT `id`\n FROM `glpi_documenttypes`\n WHERE `ext` = '{$ext}'"; if ($result = $DB->query($query)) { if ($DB->numrows($result) == 1) { $query = "UPDATE `glpi_documenttypes`\n SET `icon` = '{$icon}', `date_mod` = NOW()\n WHERE `id` = '" . $DB->result($result, 0, 0) . "'"; $DB->queryOrDie($query, "0.83 update icon for doc type {$ext}"); } } } /// add missing indexes for fields $migration->addKey("glpi_authldaps", "is_active"); $migration->addKey("glpi_authmails", "is_active"); $migration->addKey("glpi_ocsservers", "is_active"); $migration->changeField("glpi_users", 'token', 'password_forget_token', "char(40) NULL DEFAULT NULL"); $migration->changeField("glpi_users", 'tokendate', 'password_forget_token_date', "datetime"); $migration->addField("glpi_users", "personal_token", 'string'); $migration->addField("glpi_users", "personal_token_date", "datetime"); $migration->addField("glpi_tickets", "is_deleted", "bool"); $migration->addKey("glpi_tickets", "is_deleted"); $migration->addField("glpi_contracts", "template_name", 'string'); $migration->addField("glpi_contracts", "is_template", 'bool'); $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Give consumable to groups')); if ($migration->addField("glpi_consumables", "itemtype", "VARCHAR(100) DEFAULT NULL", array("after" => "date_out", "update" => "'User'"))) { $migration->dropKey("glpi_consumables", 'users_id'); $migration->changeField("glpi_consumables", 'users_id', 'items_id', 'integer'); $migration->addKey("glpi_consumables", array('itemtype', 'items_id'), 'item'); } $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Several emails for users')); // Several email per users if (!TableExists('glpi_useremails')) { $query = "CREATE TABLE `glpi_useremails` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `users_id` int(11) NOT NULL DEFAULT '0',\n `is_default` TINYINT( 1 ) NOT NULL DEFAULT 0,\n `is_dynamic` TINYINT( 1 ) NOT NULL DEFAULT 0,\n `email` varchar( 255 ) NULL DEFAULT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `unicity` (`users_id`,`email`),\n KEY `email` (`email`),\n KEY `is_default` (`is_default`),\n KEY `is_dynamic` (`is_dynamic`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_useremails"); } // Manage migration : populate is_default=1 // and is_dynamic depending of authldap config / authtype / auths_id if (FieldExists("glpi_users", 'email', false)) { $query = "SELECT *\n FROM `glpi_users`\n WHERE `email` <> '' AND `email` IS NOT NULL"; if ($result = $DB->query($query)) { if ($DB->numrows($result) > 0) { while ($data = $DB->fetch_assoc($result)) { $is_dynamic = 0; $ldap_servers = array(); // manage is_dynamic : if ($data['authtype'] == Auth::MAIL) { $is_dynamic = 1; } else { if (Auth::isAlternateAuth($data["authtype"]) && $data['auths_id'] > 0 || $data['authtype'] == Auth::LDAP) { if (!isset($ldap_servers[$data['auths_id']])) { $ldap_servers[$data['auths_id']] = 0; $ldap = new AuthLDAP(); if ($ldap->getFromDB($data['auths_id'])) { if (!empty($ldap->fields['email_field'])) { $ldap_servers[$data['auths_id']] = 1; } } } $is_dynamic = $ldap_servers[$data['auths_id']]; } } $query2 = "INSERT INTO `glpi_useremails`\n (`users_id`, `is_default`, `is_dynamic`, `email`)\n VALUES ('" . $data['id'] . "','1','{$is_dynamic}','" . addslashes($data['email']) . "')"; $DB->queryOrDie($query2, "0.83 move emails to glpi_useremails"); } } } // Drop email field from glpi_users $migration->dropField("glpi_users", 'email'); } // check unicity for users email : unset rule and display warning foreach ($DB->request("glpi_fieldunicities", "`itemtype` = 'User' AND `fields` LIKE '%email%'") as $data) { $query = "UPDATE `glpi_fieldunicities`\n SET `is_active` = '0'\n WHERE `id` = '" . $data['id'] . "'"; $DB->query($query); echo "<div class='red'><p>A unicity check use email for users. "; echo "Due to new feature permit several email per users, this rule have been disabled.</p></div>"; } // multiple manager in groups $migration->changeField("glpi_authldaps", 'email_field', 'email1_field', 'string'); $migration->addField("glpi_authldaps", 'email2_field', 'string'); $migration->addField("glpi_authldaps", 'email3_field', 'string'); $migration->addField("glpi_authldaps", 'email4_field', 'string'); $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Multiple managers for groups')); /// migration : multiple group managers $migration->addField("glpi_groups_users", "is_manager", 'bool'); $migration->addKey("glpi_groups_users", "is_manager"); $migration->migrationOneTable('glpi_groups_users'); if (FieldExists("glpi_groups", 'users_id', false)) { $query = "SELECT *\n FROM `glpi_groups`\n WHERE `users_id` > 0"; $user = new User(); if ($result = $DB->query($query)) { if ($DB->numrows($result) > 0) { while ($data = $DB->fetch_assoc($result)) { if ($user->getFromDB($data['users_id'])) { $query = "SELECT `id`\n FROM `glpi_groups_users`\n WHERE `groups_id` = '" . $data['id'] . "'\n AND `users_id` = '" . $data['users_id'] . "'"; if ($result2 = $DB->query($query)) { // add manager to groups_users setting if not present if ($DB->numrows($result2) == 0) { $query2 = "INSERT INTO`glpi_groups_users`\n (`users_id`, `groups_id`, `is_manager`)\n VALUES ('" . $data['users_id'] . "' ,'" . $data['id'] . "', '1')"; $DB->queryOrDie($query2, "0.83 insert manager of groups"); } else { // Update user as manager if presnet in groups_users $query2 = "UPDATE `glpi_groups_users`\n SET `is_manager` = '1'\n WHERE `groups_id` = '" . $data['id'] . "'\n AND `users_id` = '" . $data['users_id'] . "'"; $DB->queryOrDie($query2, "0.83 set manager of groups"); } } } } } } // Drop field glpi_groups $migration->dropField("glpi_groups", 'users_id'); } $migration->displayMessage(sprintf(__('Change of the database layout - %s'), 'Add entities information on document links')); if ($migration->addField("glpi_documents_items", "entities_id", "integer")) { $migration->addField("glpi_documents_items", "is_recursive", "bool"); $migration->migrationOneTable('glpi_documents_items'); $query_doc_i = "UPDATE `glpi_documents_items` as `doc_i`\n INNER JOIN `glpi_documents` as `doc`\n ON `doc`.`id` = `doc_i`.`documents_id`\n SET `doc_i`.`entities_id` = `doc`.`entities_id`,\n `doc_i`.`is_recursive` = `doc`.`is_recursive`"; $DB->queryOrDie($query_doc_i, "0.83 update entities_id and is_recursive in glpi_documents_items"); /// create index for search count on tab $migration->dropKey("glpi_documents_items", "item"); $migration->migrationOneTable('glpi_documents_items'); $migration->addKey("glpi_documents_items", array('itemtype', 'items_id', 'entities_id', 'is_recursive'), 'item'); } $migration->displayMessage(sprintf(__('Data migration - %s'), 'RuleTicket')); $changes['RuleTicket'] = array('ticketcategories_id' => 'itilcategories_id'); $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.83 update datas for rules actions"); } // Update criteria 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.83 update datas for rules criteria"); } } } } $migration->displayMessage(sprintf(__('Data migration - %s'), 'Ticket templates')); $default_ticket_template = 0; if (!TableExists('glpi_tickettemplates')) { $query = "CREATE TABLE `glpi_tickettemplates` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `name` varchar( 255 ) NULL 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 PRIMARY KEY (`id`),\n KEY `name` (`name`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_tickettemplates"); $query = "INSERT INTO `glpi_tickettemplates`\n (`name`, `is_recursive`)\n VALUES ('Default', 1)"; $DB->queryOrDie($query, "0.83 add default ticket template"); $default_ticket_template = $DB->insert_id(); } $migration->addField('glpi_itilcategories', 'tickettemplates_id_incident', "integer"); $migration->addKey('glpi_itilcategories', 'tickettemplates_id_incident'); $migration->addField('glpi_itilcategories', 'tickettemplates_id_demand', "integer"); $migration->addKey('glpi_itilcategories', 'tickettemplates_id_demand'); $migration->addField('glpi_itilcategories', 'is_incident', "integer", array('value' => 1)); $migration->addKey('glpi_itilcategories', 'is_incident'); $migration->addField('glpi_itilcategories', 'is_request', "integer", array('value' => 1)); $migration->addKey('glpi_itilcategories', 'is_request'); $migration->addField('glpi_itilcategories', 'is_problem', "integer", array('value' => 1)); $migration->addKey('glpi_itilcategories', 'is_problem'); if (!TableExists('glpi_tickettemplatehiddenfields')) { $query = "CREATE TABLE `glpi_tickettemplatehiddenfields` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `tickettemplates_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 `num` int(11) NOT NULL DEFAULT '0',\n PRIMARY KEY (`id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`),\n KEY `unicity` (`tickettemplates_id`,`num`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_tickettemplatehiddenfields"); } if (!TableExists('glpi_tickettemplatepredefinedfields')) { $query = "CREATE TABLE `glpi_tickettemplatepredefinedfields` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `tickettemplates_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 `num` int(11) NOT NULL DEFAULT '0',\n `value` TEXT DEFAULT NULL,\n PRIMARY KEY (`id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`),\n KEY `unicity` (`tickettemplates_id`,`num`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_tickettemplatepredefinedfields"); } if (!TableExists('glpi_tickettemplatemandatoryfields')) { $query = "CREATE TABLE `glpi_tickettemplatemandatoryfields` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `tickettemplates_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 `num` int(11) NOT NULL DEFAULT '0',\n PRIMARY KEY (`id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`),\n KEY `unicity` (`tickettemplates_id`,`num`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_tickettemplatemandatoryfields"); /// Add mandatory fields to default template if ($default_ticket_template > 0) { foreach ($DB->request('glpi_configs') as $data) { if (isset($data['is_ticket_title_mandatory']) && $data['is_ticket_title_mandatory']) { $query = "INSERT INTO `glpi_tickettemplatemandatoryfields`\n (`tickettemplates_id`, `num`)\n VALUES ('{$default_ticket_template}', 1)"; $DB->queryOrDie($query, "0.83 add mandatory field for default ticket template"); } if (isset($data['is_ticket_content_mandatory']) && $data['is_ticket_content_mandatory']) { $query = "INSERT INTO `glpi_tickettemplatemandatoryfields`\n (`tickettemplates_id`, `num`)\n VALUES ('{$default_ticket_template}', 21)"; $DB->queryOrDie($query, "0.83 add mandatory field for default ticket template"); } if (isset($data['is_ticket_category_mandatory']) && $data['is_ticket_category_mandatory']) { $query = "INSERT INTO `glpi_tickettemplatemandatoryfields`\n (`tickettemplates_id`, `num`)\n VALUES ('{$default_ticket_template}', 7)"; $DB->queryOrDie($query, "0.83 add mandatory field for default ticket template"); } } // Update itil categories $migration->migrationOneTable('glpi_itilcategories'); $query = "UPDATE `glpi_itilcategories`\n SET `tickettemplates_id_incident` = '{$default_ticket_template}',\n `tickettemplates_id_demand` = '{$default_ticket_template}'"; $DB->queryOrDie($query, "0.83 update default templates used by itil categories"); } } // Drop global mandatory config $migration->dropField('glpi_configs', 'is_ticket_title_mandatory'); $migration->dropField('glpi_configs', 'is_ticket_content_mandatory'); $migration->dropField('glpi_configs', 'is_ticket_category_mandatory'); $migration->addField('glpi_profiles', 'tickettemplate', "char", array('update' => '`sla`')); $migration->addField("glpi_entitydatas", "tickettemplates_id", 'integer', array('value' => '-2')); $migration->displayMessage(sprintf(__('Data migration - %s'), 'Tech Groups on items')); // Group of technicians in charge of Helpdesk items $migration->addField('glpi_computers', 'groups_id_tech', "integer", array('after' => "users_id_tech")); $migration->addKey('glpi_computers', 'groups_id_tech'); $migration->addField('glpi_monitors', 'groups_id_tech', "integer", array('after' => "users_id_tech")); $migration->addKey('glpi_monitors', 'groups_id_tech'); $migration->addField('glpi_networkequipments', 'groups_id_tech', "integer", array('after' => "users_id_tech")); $migration->addKey('glpi_networkequipments', 'groups_id_tech'); $migration->addField('glpi_peripherals', 'groups_id_tech', "integer", array('after' => "users_id_tech")); $migration->addKey('glpi_peripherals', 'groups_id_tech'); $migration->addField('glpi_phones', 'groups_id_tech', "integer", array('after' => "users_id_tech")); $migration->addKey('glpi_phones', 'groups_id_tech'); $migration->addField('glpi_printers', 'groups_id_tech', "integer", array('after' => "users_id_tech")); $migration->addKey('glpi_printers', 'groups_id_tech'); $migration->addField('glpi_softwares', 'groups_id_tech', "integer", array('after' => "users_id_tech")); $migration->addKey('glpi_softwares', 'groups_id_tech'); $migration->addField('glpi_cartridgeitems', 'groups_id_tech', "integer", array('after' => "users_id_tech")); $migration->addKey('glpi_cartridgeitems', 'groups_id_tech'); $migration->addField('glpi_consumableitems', 'groups_id_tech', "integer", array('after' => "users_id_tech")); $migration->addKey('glpi_consumableitems', 'groups_id_tech'); $migration->addField('glpi_printers', 'last_pages_counter', 'integer', array('after' => 'init_pages_counter')); $migration->addKey('glpi_printers', 'last_pages_counter'); $migration->displayMessage(sprintf(__('Data migration - %s'), 'various cleaning DB')); // Clean ticket satisfactions $query = "DELETE\n FROM `glpi_ticketsatisfactions`\n WHERE `glpi_ticketsatisfactions`.`tickets_id` NOT IN (SELECT `glpi_tickets`.`id`\n FROM `glpi_tickets`)"; $DB->queryOrDie($query, "0.83 clean glpi_ticketsatisfactions"); // Clean unused slalevels $query = "DELETE\n FROM `glpi_slalevels_tickets`\n WHERE (`glpi_slalevels_tickets`.`tickets_id`, `glpi_slalevels_tickets`.`slalevels_id`)\n NOT IN (SELECT `glpi_tickets`.`id`, `glpi_tickets`.`slalevels_id`\n FROM `glpi_tickets`)"; $DB->queryOrDie($query, "0.83 clean glpi_slalevels_tickets"); $migration->displayMessage(sprintf(__('Data migration - %s'), 'recurrent tickets')); if (!TableExists('glpi_ticketrecurrents')) { $query = "CREATE TABLE `glpi_ticketrecurrents` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `name` varchar( 255 ) NULL DEFAULT NULL,\n `comment` TEXT DEFAULT NULL,\n `entities_id` int(11) NOT NULL DEFAULT '0',\n `is_recursive` TINYINT( 1 ) NOT NULL DEFAULT 0,\n `is_active` TINYINT( 1 ) NOT NULL DEFAULT 0,\n `tickettemplates_id` int(11) NOT NULL DEFAULT '0',\n `begin_date` datetime DEFAULT NULL,\n `periodicity` int(11) NOT NULL DEFAULT '0',\n `create_before` int(11) NOT NULL DEFAULT '0',\n `next_creation_date` datetime DEFAULT NULL,\n PRIMARY KEY (`id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`),\n KEY `is_active` (`is_active`),\n KEY `tickettemplates_id` (`tickettemplates_id`),\n KEY `next_creation_date` (`next_creation_date`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_ticketrecurrents"); $ADDTODISPLAYPREF['TicketRecurrent'] = array(11, 12, 13, 15, 14); } if (!countElementsInTable('glpi_crontasks', "`itemtype`='TicketRecurrent' AND `name`='ticketrecurrent'")) { $query = "INSERT INTO `glpi_crontasks`\n (`itemtype`, `name`, `frequency`, `param`, `state`, `mode`, `allowmode`,\n `hourmin`, `hourmax`, `logs_lifetime`, `lastrun`, `lastcode`, `comment`)\n VALUES ('TicketRecurrent', 'ticketrecurrent', 3600, NULL, 1, 1, 3,\n 0, 24, 30, NULL, NULL, NULL)"; $DB->queryOrDie($query, "0.83 populate glpi_crontasks for ticketrecurrent"); } $migration->addField('glpi_profiles', 'ticketrecurrent', "char", array('update' => '`sla`')); $migration->displayMessage(sprintf(__('Data migration - %s'), 'various fields add')); // Ticket delegation $migration->addField('glpi_groups_users', 'is_userdelegate', 'bool'); $migration->addKey('glpi_groups_users', 'is_userdelegate'); //Software dictionary update $migration->addField("glpi_rulecachesoftwares", "entities_id", "string"); $migration->addField("glpi_rulecachesoftwares", "new_entities_id", "string"); $migration->addField("glpi_entitydatas", "entities_id_software", 'integer', array('value' => '-2')); // Groups perm $migration->addField('glpi_groups', 'is_requester', 'bool', array('value' => '1')); $migration->addField('glpi_groups', 'is_assign', 'bool', array('value' => '1')); $migration->addField('glpi_groups', 'is_notify', 'bool', array('value' => '1')); $migration->addField('glpi_groups', 'is_itemgroup', 'bool', array('value' => '1')); $migration->addField('glpi_groups', 'is_usergroup', 'bool', array('value' => '1')); $migration->addKey('glpi_groups', 'is_requester'); $migration->addKey('glpi_groups', 'is_assign'); $migration->addKey('glpi_groups', 'is_notify'); $migration->addKey('glpi_groups', 'is_itemgroup'); $migration->addKey('glpi_groups', 'is_usergroup'); // Ticket solution by entity $migration->addfield('glpi_solutiontypes', 'entities_id', 'integer'); $migration->addfield('glpi_solutiontypes', 'is_recursive', 'bool', array('value' => '1')); $migration->addKey('glpi_solutiontypes', 'entities_id'); $migration->addKey('glpi_solutiontypes', 'is_recursive'); // Fix solution template index $migration->dropKey('glpi_solutiontemplates', 'unicity'); $migration->addKey('glpi_solutiontemplates', 'entities_id'); // New index for count on tab $migration->addKey('glpi_ruleactions', array('field', 'value'), '', 'INDEX', 50); $migration->displayMessage(sprintf(__('Data migration - %s'), 'Create new default profiles')); $profiles = array('hotliner' => array('name' => 'Hotliner', 'interface' => 'central', 'user' => 'r', 'import_externalauth_users' => 'w', 'create_ticket' => '1', 'assign_ticket' => '1', 'global_add_followups' => '1', 'add_followups' => '1', 'update_ticket' => '1', 'observe_ticket' => '1', 'show_all_ticket' => '1', 'show_full_ticket' => '1', 'show_all_problem' => '1', 'show_planning' => '1', 'statistic' => '1', 'tickettemplate' => 'r', 'password_update' => '1', 'helpdesk_hardware' => '3', 'helpdesk_item_type' => addslashes('["Computer", "Monitor", "NetworkEquipment",' . '"Peripheral", "Phone", "Printer",' . '"Software"]'), 'create_validation' => '1', 'update_own_followups' => '1', 'create_ticket_on_login' => '1'), 'technician' => array('name' => 'Technician', 'interface' => 'central', 'password_update' => '1', 'computer' => 'w', 'monitor' => 'w', 'software' => 'w', 'networking' => 'w', 'printer' => 'w', 'peripheral' => 'w', 'cartridge' => 'w', 'consumable' => 'w', 'phone' => 'w', 'notes' => 'w', 'document' => 'w', 'knowbase' => 'w', 'faq' => 'w', 'reservation_helpdesk' => '1', 'reservation_central' => 'w', 'reports' => 'r', 'view_ocsng' => 'r', 'sync_ocsng' => 'w', 'user' => 'w', 'group' => 'r', 'entity' => 'r', 'transfer' => 'r', 'reminder_public' => 'w', 'create_ticket' => '1', 'add_followups' => '1', 'global_add_followups' => '1', 'global_add_tasks' => '1', 'update_ticket' => '1', 'own_ticket' => '1', 'show_all_ticket' => '1', 'show_assign_ticket' => '1', 'show_full_ticket' => '1', 'observe_ticket' => '1', 'update_followups' => '1', 'update_tasks' => '1', 'show_planning' => '1', 'statistic' => '1', 'helpdesk_hardware' => '3', 'helpdesk_item_type' => addslashes('["Computer", "Monitor", "NetworkEquipment",' . '"Peripheral", "Phone", "Printer",' . '"Software"]'), 'import_externalauth_users' => 'w', 'create_validation' => '1', 'sla' => 'r', 'update_own_followups' => '1', 'show_my_problem' => '1', 'show_all_problem' => '1', 'tickettemplate' => 'r', 'ticketrecurrent' => 'r'), 'supervisor' => array('name' => 'Supervisor', 'interface' => 'central', 'password_update' => '1', 'computer' => 'w', 'monitor' => 'w', 'software' => 'w', 'networking' => 'w', 'printer' => 'w', 'peripheral' => 'w', 'cartridge' => 'w', 'consumable' => 'w', 'phone' => 'w', 'notes' => 'w', 'document' => 'w', 'knowbase' => 'w', 'faq' => 'w', 'reservation_helpdesk' => '1', 'reservation_central' => 'w', 'reports' => 'r', 'view_ocsng' => 'r', 'sync_ocsng' => 'w', 'entity_dropdown' => 'w', 'rule_ticket' => 'r', 'entity_rule_ticket' => 'w', 'user' => 'w', 'group' => 'r', 'entity' => 'r', 'transfer' => 'r', 'logs' => 'r', 'reminder_public' => 'w', 'create_ticket' => '1', 'delete_ticket' => '1', 'add_followups' => '1', 'global_add_followups' => '1', 'global_add_tasks' => '1', 'update_ticket' => '1', 'update_priority' => '1', 'own_ticket' => '1', 'steal_ticket' => '1', 'assign_ticket' => '1', 'show_all_ticket' => '1', 'show_assign_ticket' => '1', 'show_full_ticket' => '1', 'observe_ticket' => '1', 'update_followups' => '1', 'update_tasks' => '1', 'show_planning' => '1', 'show_all_planning' => '1', 'statistic' => '1', 'helpdesk_hardware' => '3', 'helpdesk_item_type' => addslashes('["Computer", "Monitor", "NetworkEquipment",' . '"Peripheral", "Phone", "Printer",' . '"Software"]'), 'import_externalauth_users' => 'w', 'rule_mailcollector' => 'w', 'validate_ticket' => '1', 'create_validation' => '1', 'calendar' => 'w', 'sla' => 'w', 'update_own_followups' => '1', 'delete_followups' => '1', 'show_my_problem' => '1', 'show_all_problem' => '1', 'edit_all_problem' => '1', 'tickettemplate' => 'w', 'ticketrecurrent' => 'w')); foreach ($profiles as $profile => $data) { $query = "INSERT INTO `glpi_profiles`\n (`" . implode("`, `", array_keys($data)) . "`)\n VALUES ('" . implode("', '", $data) . "')"; $DB->queryOrDie($query, "0.83 create new profile {$profile}"); } $migration->displayMessage(sprintf(__('Data migration - %s'), 'Reminder visibility')); if (!TableExists('glpi_reminders_users')) { $query = "CREATE TABLE `glpi_reminders_users` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `reminders_id` int(11) NOT NULL DEFAULT '0',\n `users_id` int(11) NOT NULL DEFAULT '0',\n PRIMARY KEY (`id`),\n KEY `reminders_id` (`reminders_id`),\n KEY `users_id` (`users_id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_reminders_users"); } if (!TableExists('glpi_groups_reminders')) { $query = "CREATE TABLE `glpi_groups_reminders` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `reminders_id` int(11) NOT NULL DEFAULT '0',\n `groups_id` int(11) NOT NULL DEFAULT '0',\n `entities_id` int(11) NOT NULL DEFAULT '-1',\n `is_recursive` TINYINT( 1 ) NOT NULL DEFAULT 0,\n PRIMARY KEY (`id`),\n KEY `reminders_id` (`reminders_id`),\n KEY `groups_id` (`groups_id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`)\n\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_groups_reminders"); } if (!TableExists('glpi_profiles_reminders')) { $query = "CREATE TABLE `glpi_profiles_reminders` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `reminders_id` int(11) NOT NULL DEFAULT '0',\n `profiles_id` int(11) NOT NULL DEFAULT '0',\n `entities_id` int(11) NOT NULL DEFAULT '-1',\n `is_recursive` TINYINT( 1 ) NOT NULL DEFAULT 0,\n PRIMARY KEY (`id`),\n KEY `reminders_id` (`reminders_id`),\n KEY `profiles_id` (`profiles_id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_profiles_reminders"); } if (!TableExists('glpi_entities_reminders')) { $query = "CREATE TABLE `glpi_entities_reminders` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `reminders_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 PRIMARY KEY (`id`),\n KEY `reminders_id` (`reminders_id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_entities_reminders"); } /// Migrate datas for is_helpdesk_visible : add all helpdesk profiles / drop field is_helpdesk_visible if (FieldExists("glpi_reminders", 'is_helpdesk_visible', false)) { $query = "SELECT `id`\n FROM `glpi_reminders`\n WHERE `is_helpdesk_visible` = 1"; if ($result = $DB->query($query)) { if ($DB->numrows($result) > 0) { // Grab helpdesk profiles $helpdesk_profiles = array(); foreach ($DB->request("glpi_profiles", "`interface` = 'helpdesk' AND `reminder_public` = 'r'") as $data2) { $helpdesk_profiles[$data2['id']] = $data2['id']; } if (count($helpdesk_profiles)) { while ($data = $DB->fetch_assoc($result)) { foreach ($helpdesk_profiles as $pid) { $query = "INSERT INTO `glpi_profiles_reminders`\n (`reminders_id`, `profiles_id`)\n VALUES ('" . $data['id'] . "', '{$pid}');"; $DB->queryOrDie($query, "0.83 migrate data for is_helpdesk_visible drop on glpi_reminders"); } } } } } $migration->dropField("glpi_reminders", 'is_helpdesk_visible'); } // Migrate datas for entities + drop fields : is_private / entities_id / is_recursive if (FieldExists("glpi_reminders", 'is_private', false)) { $query = "SELECT *\n FROM `glpi_reminders`\n WHERE `is_private` = 0"; if ($result = $DB->query($query)) { if ($DB->numrows($result) > 0) { while ($data = $DB->fetch_assoc($result)) { $query = "INSERT INTO `glpi_entities_reminders`\n (`reminders_id`, `entities_id`, `is_recursive`)\n VALUES ('" . $data['id'] . "', '" . $data['entities_id'] . "',\n '" . $data['is_recursive'] . "');"; $DB->queryOrDie($query, "0.83 migrate data for public reminders"); } } } $migration->dropField("glpi_reminders", 'is_private'); $migration->dropField("glpi_reminders", 'entities_id'); $migration->dropField("glpi_reminders", 'is_recursive'); } $ADDTODISPLAYPREF['Reminder'] = array(2, 3, 4, 5, 6, 7); $migration->displayMessage(sprintf(__('Data migration - %s'), 'KnowbaseItem visibility')); if (!TableExists('glpi_knowbaseitems_users')) { $query = "CREATE TABLE `glpi_knowbaseitems_users` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `knowbaseitems_id` int(11) NOT NULL DEFAULT '0',\n `users_id` int(11) NOT NULL DEFAULT '0',\n PRIMARY KEY (`id`),\n KEY `knowbaseitems_id` (`knowbaseitems_id`),\n KEY `users_id` (`users_id`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_knowbaseitems_users"); } if (!TableExists('glpi_groups_knowbaseitems')) { $query = "CREATE TABLE `glpi_groups_knowbaseitems` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `knowbaseitems_id` int(11) NOT NULL DEFAULT '0',\n `groups_id` int(11) NOT NULL DEFAULT '0',\n `entities_id` int(11) NOT NULL DEFAULT '-1',\n `is_recursive` TINYINT( 1 ) NOT NULL DEFAULT 0,\n PRIMARY KEY (`id`),\n KEY `knowbaseitems_id` (`knowbaseitems_id`),\n KEY `groups_id` (`groups_id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`)\n\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_groups_knowbaseitems"); } if (!TableExists('glpi_knowbaseitems_profiles')) { $query = "CREATE TABLE `glpi_knowbaseitems_profiles` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `knowbaseitems_id` int(11) NOT NULL DEFAULT '0',\n `profiles_id` int(11) NOT NULL DEFAULT '0',\n `entities_id` int(11) NOT NULL DEFAULT '-1',\n `is_recursive` TINYINT( 1 ) NOT NULL DEFAULT 0,\n PRIMARY KEY (`id`),\n KEY `knowbaseitems_id` (`knowbaseitems_id`),\n KEY `profiles_id` (`profiles_id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_knowbaseitems_profiles"); } if (!TableExists('glpi_entities_knowbaseitems')) { $query = "CREATE TABLE `glpi_entities_knowbaseitems` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `knowbaseitems_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 PRIMARY KEY (`id`),\n KEY `knowbaseitems_id` (`knowbaseitems_id`),\n KEY `entities_id` (`entities_id`),\n KEY `is_recursive` (`is_recursive`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; $DB->queryOrDie($query, "0.83 add table glpi_entities_knowbaseitems"); } /// Migrate datas for entities_id / is_recursive if (FieldExists("glpi_knowbaseitems", 'entities_id', false)) { $query = "SELECT *\n FROM `glpi_knowbaseitems`"; if ($result = $DB->query($query)) { if ($DB->numrows($result) > 0) { while ($data = $DB->fetch_assoc($result)) { $query = "INSERT INTO `glpi_entities_knowbaseitems`\n (`knowbaseitems_id`, `entities_id`, `is_recursive`)\n VALUES ('" . $data['id'] . "', '" . $data['entities_id'] . "',\n '" . $data['is_recursive'] . "');"; $DB->queryOrDie($query, "0.83 migrate data for entities on glpi_entities_knowbaseitems"); } } } $migration->dropField("glpi_knowbaseitems", 'entities_id'); $migration->dropField("glpi_knowbaseitems", 'is_recursive'); } // Plugins $migration->addField('glpi_plugins', 'license', 'string'); $migration->migrationOneTable('glpi_entitydatas'); $restore_root_entity_value = false; // create root entity if not exist with old default values if (countElementsInTable('glpi_entitydatas', 'entities_id=0') == 0) { $query = "INSERT INTO `glpi_entitydatas`\n (`entities_id`, `entities_id_software`,\n `autofill_order_date`, `autofill_delivery_date`, `autofill_buy_date`,\n `autofill_use_date`, `autofill_warranty_date`,\n `inquest_config`, `inquest_rate`, `inquest_delay`,\n `tickettype`, `calendars_id`, `tickettemplates_id`,\n `autoclose_delay`, `auto_assign_mode`,\n `cartridges_alert_repeat`, `consumables_alert_repeat`,\n `use_licenses_alert`, `use_infocoms_alert`, `notclosed_delay`,\n `use_contracts_alert`, `use_reservations_alert`)\n VALUES (0, -10,\n 0, 0, 0,\n 0, 0,\n 1, 0, 0,\n 1, 0, '{$default_ticket_template}',\n -1, -1,\n -1, -1,\n -1, -1, -1,\n -1, -1)"; // -1 to keep config value - see 1647 $DB->queryOrDie($query, "0.83 add entities_id 0 in glpi_entitydatas"); $restore_root_entity_value = true; } else { $query = "UPDATE `glpi_entitydatas`\n SET `tickettemplates_id` = '{$default_ticket_template}'\n WHERE `entities_id` = 0\n AND `tickettemplates_id` = -2"; $DB->queryOrDie($query, "0.83 update tickettemplates_id for root entity in glpi_entitydatas"); $query = "UPDATE `glpi_entitydatas`\n SET `entities_id_software` = -10\n WHERE `entities_id` = 0\n AND `entities_id_software` = -2"; $DB->queryOrDie($query, "0.83 update entities_id_software for root entity in glpi_entitydatas"); // For root entity already exists in entitydatas in 0.78 $query = "UPDATE `glpi_entitydatas`\n SET `tickettype` = 1\n WHERE `entities_id` = 0\n AND `tickettype` = 0"; $DB->queryOrDie($query, "0.83 update tickettype for root entity in glpi_entitydatas"); $query = "UPDATE `glpi_entitydatas`\n SET `inquest_config` = 1\n WHERE `entities_id` = 0\n AND `inquest_config` = 0"; $DB->queryOrDie($query, "0.83 update inquest_config for root entity in glpi_entitydatas "); $query = "UPDATE `glpi_entitydatas`\n SET `inquest_rate` = 0\n WHERE `entities_id` = 0\n AND `inquest_rate` = '-1'"; $DB->queryOrDie($query, "0.83 update inquest_rate for root entity in glpi_entitydatas "); $query = "UPDATE `glpi_entitydatas`\n SET `inquest_delay` = 0\n WHERE `entities_id` = 0\n AND `inquest_delay` = '-1'"; $DB->queryOrDie($query, "0.83 update inquest_delay for root entity in glpi_entitydatas "); } // migration to new values for inherit parent (0 => -2) $field0 = array('calendars_id', 'tickettype', 'inquest_config'); foreach ($field0 as $field_0) { if (FieldExists("glpi_entitydatas", $field_0, false)) { $query = "UPDATE `glpi_entitydatas`\n SET `{$field_0}` = '-2'\n WHERE `{$field_0}` = '0'\n AND `entities_id` > 0"; $DB->queryOrDie($query, "0.83 new value for inherit parent 0 in glpi_entitydatas"); } } // new default value $migration->changeField("glpi_entitydatas", "calendars_id", "calendars_id", "int(11) NOT NULL DEFAULT '-2'"); $migration->changeField("glpi_entitydatas", "tickettype", "tickettype", "int(11) NOT NULL DEFAULT '-2'"); $migration->changeField("glpi_entitydatas", "inquest_config", "inquest_config", "int(11) NOT NULL DEFAULT '-2'"); $migration->changeField("glpi_entitydatas", "inquest_rate", "inquest_rate", "int(11) NOT NULL DEFAULT '0'"); $migration->changeField("glpi_entitydatas", "inquest_delay", "inquest_delay", "int(11) NOT NULL DEFAULT '-10'"); // migration to new values for inherit parent (-1 => -2) $fieldparent = array('autofill_buy_date', 'autofill_delivery_date', 'autofill_warranty_date', 'autofill_order_date', 'autofill_use_date'); foreach ($fieldparent as $field_parent) { if (FieldExists("glpi_entitydatas", $field_parent, false)) { $query = "UPDATE `glpi_entitydatas`\n SET `{$field_parent}` = '-2'\n WHERE `{$field_parent}` = '-1'"; $DB->queryOrDie($query, "0.83 new value for inherit parent -1 in glpi_entitydatas"); } } // new default value $migration->changeField("glpi_entitydatas", "autofill_buy_date", "autofill_buy_date", 'string', array('value' => '-2')); $migration->changeField("glpi_entitydatas", "autofill_delivery_date", "autofill_delivery_date", 'string', array('value' => '-2')); $migration->changeField("glpi_entitydatas", "autofill_warranty_date", "autofill_warranty_date", 'string', array('value' => '-2')); $migration->changeField("glpi_entitydatas", "autofill_order_date", "autofill_order_date", 'string', array('value' => '-2')); $migration->changeField("glpi_entitydatas", "autofill_use_date", "autofill_use_date", 'string', array('value' => '-2')); // migration to new values for inherit config $fieldconfig = array('auto_assign_mode', 'autoclose_delay', 'cartridges_alert_repeat', 'consumables_alert_repeat', 'notclosed_delay', 'use_contracts_alert', 'use_infocoms_alert', 'use_licenses_alert', 'use_reservations_alert'); $query = "SELECT *\n FROM `glpi_configs`"; if ($result = $DB->query($query)) { if ($DB->numrows($result) > 0) { if ($data = $DB->fetch_assoc($result)) { foreach ($fieldconfig as $field_config) { if (FieldExists("glpi_entitydatas", $field_config, false) && FieldExists("glpi_configs", $field_config, false)) { // value of general config $query = "UPDATE `glpi_entitydatas`\n SET `{$field_config}` = '" . $data[$field_config] . "'\n WHERE `{$field_config}` = -1"; $DB->queryOrDie($query, "0.83 migrate data from config to glpi_entitydatas"); $migration->changeField("glpi_entitydatas", "{$field_config}", "{$field_config}", "int(11) NOT NULL DEFAULT '-2'"); $migration->dropField("glpi_configs", $field_config); } } if (FieldExists("glpi_entitydatas", "auto_assign_mode", false)) { // new value for never $query = "UPDATE `glpi_entitydatas`\n SET `auto_assign_mode` = -10\n WHERE `auto_assign_mode` = 0"; $DB->queryOrDie($query, "0.83 change value Never in glpi_entitydatas for auto_assign_mode"); } } } } // value of config in each entity $fieldconfig = array('default_contract_alert', 'default_infocom_alert', 'default_alarm_threshold'); $query = "SELECT *\n FROM `glpi_configs`"; if ($result = $DB->query($query)) { if ($DB->numrows($result) > 0) { if ($data = $DB->fetch_assoc($result)) { foreach ($fieldconfig as $field_config) { if (FieldExists("glpi_configs", $field_config, false) && !FieldExists("glpi_entitydatas", $field_config, false)) { // add config fields in entitydatas $migration->addField("glpi_entitydatas", $field_config, 'integer', array('update' => $data[$field_config], 'value' => $field_config == "default_alarm_threshold" ? 10 : 0)); $migration->dropField("glpi_configs", $field_config); } } } } } if ($restore_root_entity_value) { $query = "UPDATE `glpi_entitydatas`\n SET `calendars_id` = 0\n WHERE `entities_id` = 0;"; $DB->queryOrDie($query, "0.83 restore root entity default value"); } $migration->addKey('glpi_computervirtualmachines', 'computers_id'); $migration->addKey('glpi_computervirtualmachines', 'entities_id'); $migration->addKey('glpi_computervirtualmachines', 'name'); $migration->addKey('glpi_computervirtualmachines', 'virtualmachinestates_id'); $migration->addKey('glpi_computervirtualmachines', 'virtualmachinesystems_id'); $migration->addKey('glpi_computervirtualmachines', 'vcpu'); $migration->addKey('glpi_computervirtualmachines', 'ram'); // $ADDTODISPLAYPREF['KnowbaseItem'] = array(2,3,4,5,6,7); $renametables = array('TicketSolutionType' => 'SolutionType', 'TicketSolutionTemplate' => 'SolutionTemplate', 'TicketCategory' => 'ITILCategory'); $itemtype_tables = array("glpi_bookmarks" => 'itemtype', "glpi_bookmarks_users" => 'itemtype', "glpi_displaypreferences" => 'itemtype', "glpi_logs" => 'itemtype', "glpi_events" => 'type'); foreach ($itemtype_tables as $table => $field) { foreach ($renametables as $key => $val) { $query = "UPDATE `{$table}`\n SET `{$field}` = '" . $val . "'\n WHERE `{$field}` = '" . $key . "'"; $DB->queryOrDie($query, "0.83 update itemtype of table {$table} for {$val}"); } } // ************ Keep it at the end ************** $migration->displayMessage(sprintf(__('Data migration - %s'), 'glpi_displaypreferences')); // Change is_recursive index $query = "UPDATE `glpi_displaypreferences`\n SET `num` = '86'\n WHERE `itemtype` = 'Group'\n AND `num` = '6'"; $DB->query($query); 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); } } } } // must always be at the end $migration->executeMigration(); return $updateresult; }
along with GLPI. If not, see <http://www.gnu.org/licenses/>. -------------------------------------------------------------------------- */ include '../inc/includes.php'; header("Content-Type: text/html; charset=UTF-8"); Session::checkLoginUser(); if (!isset($_POST['action'])) { exit; } switch ($_POST['action']) { case "change_task_state": if (!isset($_POST['tasks_id'])) { exit; } $task = new TicketTask(); $task->getFromDB(intval($_POST['tasks_id'])); if (!in_array($task->fields['state'], array(0, Planning::INFO))) { echo $new_state = $task->fields['state'] == Planning::DONE ? Planning::TODO : Planning::DONE; $task->update(array('id' => intval($_POST['tasks_id']), 'tickets_id' => intval($_POST['tickets_id']), 'state' => $new_state)); } break; case "viewsubitem": Html::header_nocache(); if (!isset($_POST['type'])) { exit; } if (!isset($_POST['parenttype'])) { exit; } if (($item = getItemForItemtype($_POST['type'])) && ($parent = getItemForItemtype($_POST['parenttype']))) { if (isset($_POST[$parent->getForeignKeyField()]) && isset($_POST["id"]) && $parent->getFromDB($_POST[$parent->getForeignKeyField()])) {
/** * Populate the planning with planned ticket tasks * * @param $options options array must contains : * - who ID of the user (0 = undefined) * - who_group ID of the group of users (0 = undefined) * - begin Date * - end Date * * @return array of planning item **/ static function populatePlanning($options = array()) { global $DB, $CFG_GLPI; $interv = array(); if (!isset($options['begin']) || !isset($options['begin']) || !isset($options['begin']) || !isset($options['end'])) { return $interv; } $who = $options['who']; $who_group = $options['who_group']; $begin = $options['begin']; $end = $options['end']; // Get items to print $ASSIGN = ""; if ($who_group === "mine") { if (count($_SESSION["glpigroups"])) { $groups = implode("','", $_SESSION['glpigroups']); $ASSIGN = "`users_id` IN (SELECT DISTINCT `users_id`\n FROM `glpi_groups_users`\n WHERE `groups_id` IN ('{$groups}'))\n AND "; } else { // Only personal ones $ASSIGN = "`users_id` = '{$who}'\n AND "; } } else { if ($who > 0) { $ASSIGN = "`users_id` = '{$who}'\n AND "; } if ($who_group > 0) { $ASSIGN = "`users_id` IN (SELECT `users_id`\n FROM `glpi_groups_users`\n WHERE `groups_id` = '{$who_group}')\n AND "; } } if (empty($ASSIGN)) { $ASSIGN = "`users_id` IN (SELECT DISTINCT `glpi_profiles_users`.`users_id`\n FROM `glpi_profiles`\n LEFT JOIN `glpi_profiles_users`\n ON (`glpi_profiles`.`id` = `glpi_profiles_users`.`profiles_id`)\n WHERE `glpi_profiles`.`interface` = 'central' " . getEntitiesRestrictRequest("AND", "glpi_profiles_users", '', $_SESSION["glpiactive_entity"], 1); $ASSIGN .= ") AND "; } $query = "SELECT *\n FROM `glpi_ticketplannings`\n WHERE {$ASSIGN}\n '{$begin}' < `end`\n AND '{$end}' > `begin`\n ORDER BY `begin`"; $result = $DB->query($query); $fup = new TicketTask(); $job = new Ticket(); $interv = array(); if ($DB->numrows($result) > 0) { for ($i = 0; $data = $DB->fetch_array($result); $i++) { if ($fup->getFromDB($data["tickettasks_id"])) { if ($job->getFromDBwithData($fup->fields["tickets_id"], 0)) { // Do not check entity here because webcal used non authenticated access // if (haveAccessToEntity($job->fields["entities_id"])) { $interv[$data["begin"] . "\$\$\$" . $i]["tickettasks_id"] = $data["tickettasks_id"]; $interv[$data["begin"] . "\$\$\$" . $i]["state"] = $data["state"]; $interv[$data["begin"] . "\$\$\$" . $i]["tickets_id"] = $fup->fields["tickets_id"]; $interv[$data["begin"] . "\$\$\$" . $i]["users_id"] = $data["users_id"]; $interv[$data["begin"] . "\$\$\$" . $i]["id"] = $data["id"]; if (strcmp($begin, $data["begin"]) > 0) { $interv[$data["begin"] . "\$\$\$" . $i]["begin"] = $begin; } else { $interv[$data["begin"] . "\$\$\$" . $i]["begin"] = $data["begin"]; } if (strcmp($end, $data["end"]) < 0) { $interv[$data["begin"] . "\$\$\$" . $i]["end"] = $end; } else { $interv[$data["begin"] . "\$\$\$" . $i]["end"] = $data["end"]; } $interv[$data["begin"] . "\$\$\$" . $i]["name"] = $job->fields["name"]; $interv[$data["begin"] . "\$\$\$" . $i]["content"] = resume_text($job->fields["content"], $CFG_GLPI["cut"]); $interv[$data["begin"] . "\$\$\$" . $i]["device"] = $job->hardwaredatas ? $job->hardwaredatas->getName() : ''; $interv[$data["begin"] . "\$\$\$" . $i]["status"] = $job->fields["status"]; $interv[$data["begin"] . "\$\$\$" . $i]["priority"] = $job->fields["priority"]; // } } } } } return $interv; }