public static function install(Migration $migration)
{
global $DB;
$table = getTableForItemType(__CLASS__);
//Installation
if (!TableExists($table) && !TableExists("glpi_plugin_order")) {
$migration->displayMessage("Installing {$table}");
$query = "CREATE TABLE IF NOT EXISTS `glpi_plugin_order_orders` (\n `id` int(11) NOT NULL auto_increment,\n `entities_id` int(11) NOT NULL default '0',\n `is_template` tinyint(1) NOT NULL default '0',\n `template_name` varchar(255) collate utf8_unicode_ci default NULL,\n `is_recursive` tinyint(1) NOT NULL default '0',\n `name` varchar(255) collate utf8_unicode_ci default NULL,\n `num_order` varchar(255) collate utf8_unicode_ci default NULL,\n `budgets_id` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_budgets (id)',\n `plugin_order_ordertaxes_id` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_plugin_order_ordertaxes (id)',\n `plugin_order_orderpayments_id` int (11) NOT NULL default '0' COMMENT 'RELATION to glpi_plugin_order_orderpayments (id)',\n `order_date` date default NULL,\n `duedate` date default NULL,\n `deliverydate` date default NULL,\n `is_late` tinyint(1) NOT NULL default '0',\n `suppliers_id` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_suppliers (id)',\n `contacts_id` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_contacts (id)',\n `locations_id` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_locations (id)',\n `plugin_order_orderstates_id` int(11) NOT NULL default 1,\n `plugin_order_billstates_id` int(11) NOT NULL default 1,\n `port_price` float NOT NULL default 0,\n `comment` text collate utf8_unicode_ci,\n `notepad` longtext collate utf8_unicode_ci,\n `is_deleted` tinyint(1) NOT NULL default '0',\n `users_id` int(11) NOT NULL default '0',\n `groups_id` int(11) NOT NULL default '0',\n `users_id_delivery` int(11) NOT NULL default '0',\n `groups_id_delivery` int(11) NOT NULL default '0',\n `plugin_order_ordertypes_id` int (11) NOT NULL default '0' COMMENT 'RELATION to glpi_plugin_order_ordertypes (id)',\n `date_mod` datetime default NULL,\n `is_helpdesk_visible` tinyint(1) NOT NULL default '1',\n PRIMARY KEY (`id`),\n KEY `name` (`name`),\n KEY `entities_id` (`entities_id`),\n KEY `plugin_order_ordertaxes_id` (`plugin_order_ordertaxes_id`),\n KEY `plugin_order_orderpayments_id` (`plugin_order_orderpayments_id`),\n KEY `states_id` (`plugin_order_orderstates_id`),\n KEY `suppliers_id` (`suppliers_id`),\n KEY `contacts_id` (`contacts_id`),\n KEY `locations_id` (`locations_id`),\n KEY `is_late` (`locations_id`),\n KEY `is_template` (`is_template`),\n KEY `is_deleted` (`is_deleted`),\n KEY date_mod (date_mod)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
$DB->query($query) or die($DB->error());
Crontask::Register(__CLASS__, 'computeLateOrders', HOUR_TIMESTAMP, array('param' => 24, 'mode' => CronTask::MODE_EXTERNAL));
} else {
//Upgrade
$migration->displayMessage("Upgrading {$table}");
if (TableExists('glpi_plugin_order')) {
//Update to 1.1.0
$migration->addField('glpi_plugin_order', "port_price", "FLOAT NOT NULL default '0'");
$migration->addField('glpi_plugin_order', "taxes", "FLOAT NOT NULL default '0'");
if (FieldExists("glpi_plugin_order", "numordersupplier")) {
foreach ($DB->request("glpi_plugin_order") as $data) {
$query = "INSERT INTO `glpi_plugin_order_suppliers`\n (`ID`, `FK_order`, `numorder`, `numbill`) VALUES\n (NULL, '" . $data["ID"] . "', '" . $data["numordersupplier"] . "', '" . $data["numbill"] . "') ";
$DB->query($query) or die($DB->error());
}
}
$migration->dropField('glpi_plugin_order', 'numordersupplier');
$migration->dropField('glpi_plugin_order', 'numbill');
$migration->migrationOneTable('glpi_plugin_order');
}
//1.2.0
$domigration_itemtypes = false;
if ($migration->renameTable("glpi_plugin_order", $table)) {
$domigration_itemtypes = true;
}
$migration->changeField($table, "ID", "id", "int(11) NOT NULL AUTO_INCREMENT");
$migration->changeField($table, "FK_entities", "entities_id", "int(11) NOT NULL default 0");
$migration->changeField($table, "recursive", "is_recursive", "tinyint(1) NOT NULL default 0");
$migration->changeField($table, "name", "name", "varchar(255) collate utf8_unicode_ci default NULL");
$migration->changeField($table, "budget", "budgets_id", "int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_budgets (id)'");
$migration->changeField($table, "numorder", "num_order", "varchar(255) collate utf8_unicode_ci default NULL");
$migration->changeField($table, "taxes", "plugin_order_ordertaxes_id", "int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_plugin_order_ordertaxes (id)'");
$migration->changeField($table, "payment", "plugin_order_orderpayments_id", "int (11) NOT NULL default '0' COMMENT 'RELATION to glpi_plugin_order_orderpayments (id)'");
$migration->changeField($table, "date", "order_date", "date default NULL");
$migration->changeField($table, "FK_enterprise", "suppliers_id", "int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_suppliers (id)'");
$migration->changeField($table, "FK_contact", "contacts_id", "int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_contacts (id)'");
$migration->changeField($table, "location", "locations_id", "int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_locations (id)'");
$migration->changeField($table, "status", "states_id", "int(11) NOT NULL default '0'");
$migration->changeField($table, "comment", "comment", "text collate utf8_unicode_ci");
$migration->changeField($table, "notes", "notepad", "longtext collate utf8_unicode_ci");
$migration->changeField($table, "deleted", "is_deleted", "tinyint(1) NOT NULL default '0'");
$migration->addKey($table, "name");
$migration->addKey($table, "entities_id");
$migration->addKey($table, "plugin_order_ordertaxes_id");
$migration->addKey($table, "plugin_order_orderpayments_id");
$migration->addKey($table, "states_id");
$migration->addKey($table, "suppliers_id");
$migration->addKey($table, "contacts_id");
$migration->addKey($table, "locations_id");
$migration->addKey($table, "is_deleted");
$migration->migrationOneTable($table);
//Only migrate itemtypes when it's only necessary, otherwise it breaks upgrade procedure !
if ($domigration_itemtypes) {
Plugin::migrateItemType(array(3150 => 'PluginOrderOrder'), array("glpi_bookmarks", "glpi_bookmarks_users", "glpi_displaypreferences", "glpi_documents_items", "glpi_infocoms", "glpi_logs", "glpi_tickets"), array());
}
if (TableExists("glpi_plugin_order_budgets")) {
//Manage budgets (here because class has been remove since 1.4.0)
$migration->changeField("glpi_plugin_order_budgets", "ID", "id", " int(11) NOT NULL auto_increment");
$migration->changeField("glpi_plugin_order_budgets", "FK_entities", "entities_id", "int(11) NOT NULL default '0'");
$migration->changeField("glpi_plugin_order_budgets", "FK_budget", "budgets_id", "int(11) NOT NULL default '0'");
$migration->changeField("glpi_plugin_order_budgets", "comments", "comment", "text collate utf8_unicode_ci");
$migration->changeField("glpi_plugin_order_budgets", "deleted", "is_deleted", "tinyint(1) NOT NULL default '0'");
$migration->changeField("glpi_plugin_order_budgets", "startdate", "start_date", "date default NULL");
$migration->changeField("glpi_plugin_order_budgets", "enddate", "end_date", "date default NULL");
$migration->changeField("glpi_plugin_order_budgets", "value", "value", "float NOT NULL DEFAULT '0'");
$migration->addKey("glpi_plugin_order_budgets", "entities_id");
$migration->addKey("glpi_plugin_order_budgets", "is_deleted");
$migration->migrationOneTable("glpi_plugin_order_budgets");
Plugin::migrateItemType(array(3153 => 'PluginOrderBudget'), array("glpi_bookmarks", "glpi_bookmarks_users", "glpi_displaypreferences", "glpi_documents_items", "glpi_infocoms", "glpi_logs", "glpi_tickets"), array());
//Manage budgets migration before dropping the table
$budget = new Budget();
$matchings = array('budgets_id' => 'id', 'name' => 'name', 'start_date' => 'begin_date', 'end_date' => 'end_date', 'value' => 'value', 'comment' => 'comment', 'entities_id' => 'entities_id', 'is_deleted' => 'is_deleted');
foreach (getAllDatasFromTable("glpi_plugin_order_budgets") as $data) {
$tmp = array();
$id = false;
foreach ($matchings as $old => $new) {
if (!is_null($data[$old])) {
$tmp[$new] = $data[$old];
}
}
$tmp['comment'] = Toolbox::addslashes_deep($tmp['comment']);
//Budget already exists in the core: update it
if ($budget->getFromDB($data['budgets_id'])) {
$budget->update($tmp);
$id = $tmp['id'];
} else {
//Budget doesn't exists in the core: create it
unset($tmp['id']);
$id = $budget->add($tmp);
}
}
$DB->query("DROP TABLE `glpi_plugin_order_budgets`");
foreach (array('glpi_displaypreferences', 'glpi_documents_items', 'glpi_bookmarks', 'glpi_logs') as $t) {
$DB->query("DELETE FROM `{$t}` WHERE `itemtype` = 'PluginOrderBudget'");
}
}
//1.3.0
$migration->addField($table, "plugin_order_ordertypes_id", "int (11) NOT NULL default '0' COMMENT 'RELATION to glpi_plugin_order_ordertypes (id)'");
$migration->migrationOneTable($table);
//1.4.0
if ($migration->changeField("glpi_plugin_order_orders", "states_id", "plugin_order_orderstates_id", "int(11) NOT NULL default 1")) {
$migration->migrationOneTable($table);
$query = "UPDATE `glpi_plugin_order_orders` SET `plugin_order_orderstates_id`=`plugin_order_orderstates_id`+1";
$DB->query($query) or die($DB->error());
}
$migration->addField($table, "duedate", "DATETIME NULL");
$migration->migrationOneTable($table);
//1.5.0
if (TableExists("glpi_dropdown_plugin_order_status")) {
$DB->query("DROP TABLE `glpi_dropdown_plugin_order_status`") or die($DB->error());
}
if (TableExists("glpi_plugin_order_mailing")) {
$DB->query("DROP TABLE IF EXISTS `glpi_plugin_order_mailing`;") or die($DB->error());
}
$migration->addField($table, 'plugin_order_billstates_id', "int(11) NOT NULL default 0");
//1.5.2
$migration->addField($table, 'deliverydate', "DATETIME NULL");
$migration->addField($table, "is_late", "TINYINT(1) NOT NULL DEFAULT '0'");
$migration->addKey($table, "is_late");
if (!countElementsInTable('glpi_crontasks', "`name`='computeLateOrders'")) {
Crontask::Register(__CLASS__, 'computeLateOrders', HOUR_TIMESTAMP, array('param' => 24, 'mode' => CronTask::MODE_EXTERNAL));
}
$migration->migrationOneTable($table);
if ($migration->addField($table, "is_template", "tinyint(1) NOT NULL DEFAULT 0")) {
$migration->addField($table, "template_name", "VARCHAR(255) collate utf8_unicode_ci default NULL");
$migration->migrationOneTable($table);
}
$migration->addField($table, "users_id", "INT(11) NOT NULL DEFAULT '0'");
$migration->addField($table, "groups_id", "INT(11) NOT NULL DEFAULT '0'");
$migration->addField($table, "users_id_delivery", "INT(11) NOT NULL DEFAULT '0'");
$migration->addField($table, "groups_id_delivery", "INT(11) NOT NULL DEFAULT '0'");
//1.7.0
$migration->addField($table, "date_mod", "datetime");
$migration->addKey($table, "date_mod");
//1.7.2
$migration->addField($table, "is_helpdesk_visible", "bool", array('value' => 1));
$migration->migrationOneTable($table);
//Displayprefs
$prefs = array(1 => 1, 2 => 2, 4 => 4, 5 => 5, 6 => 6, 7 => 7, 10 => 10);
foreach ($prefs as $num => $rank) {
if (!countElementsInTable("glpi_displaypreferences", "`itemtype`='PluginOrderOrder' AND `num`='{$num}'\n AND `users_id`='0'")) {
$DB->query("INSERT INTO glpi_displaypreferences\n VALUES (NULL,'PluginOrderOrder','{$num}','{$rank}','0');");
}
}
//Remove unused notifications
$notification = new Notification();
$notification->deleteByCriteria("`itemtype`='PluginOrderOrder_Item'");
}
}
/**
* Print the HTML array of value consumed for a budget
*
*@return Nothing (display)
**/
function showValuesByEntity()
{
global $DB, $LANG;
$budgets_id = $this->fields['id'];
if (!$this->can($budgets_id, 'r')) {
return false;
}
// Type for which infocom are only template
$ignore = array('CartridgeItem', 'ConsumableItem', 'Software');
$query = "SELECT DISTINCT `itemtype`\n FROM `glpi_infocoms`\n WHERE `budgets_id` = '{$budgets_id}'\n AND `itemtype` NOT IN ('" . implode("','", $ignore) . "')" . getEntitiesRestrictRequest(" AND", 'glpi_infocoms', "entities_id") . "\n GROUP BY `itemtype`";
$result = $DB->query($query);
$total = 0;
$entities_values = array();
$entitiestype_values = array();
$found_types = array();
if ($DB->numrows($result)) {
while ($types = $DB->fetch_array($result)) {
if (!class_exists($types['itemtype'])) {
continue;
}
$item = new $types['itemtype']();
$found_types[$types['itemtype']] = $item->getTypeName();
$table = getTableForItemType($types['itemtype']);
$query_infos = "SELECT SUM(`glpi_infocoms`.`value`) AS `sumvalue`,\n `{$table}`.`entities_id`\n FROM `{$table}`\n INNER JOIN `glpi_infocoms`\n ON (`glpi_infocoms`.`items_id` = `{$table}`.`id`\n AND `glpi_infocoms`.`itemtype` = '" . $types['itemtype'] . "')\n LEFT JOIN `glpi_entities`\n ON (`{$table}`.`entities_id` = `glpi_entities`.`id`)\n WHERE `glpi_infocoms`.`budgets_id` = '{$budgets_id}' " . getEntitiesRestrictRequest(" AND", $table, "entities_id");
if ($item->maybeTemplate()) {
$query_infos .= " AND `{$table}`.`is_template` = '0' ";
}
$query_infos .= "GROUP BY `{$table}`.`entities_id`\n ORDER BY `glpi_entities`.`completename` ASC";
if ($result_infos = $DB->query($query_infos)) {
//Store, for each entity, the budget spent
while ($values = $DB->fetch_array($result_infos)) {
if (!isset($entities_values[$values['entities_id']])) {
$entities_values[$values['entities_id']] = 0;
}
if (!isset($entitiestype_values[$values['entities_id']][$types['itemtype']])) {
$entitiestype_values[$values['entities_id']][$types['itemtype']] = 0;
}
$entities_values[$values['entities_id']] += $values['sumvalue'];
$entitiestype_values[$values['entities_id']][$types['itemtype']] += $values['sumvalue'];
}
}
}
$budget = new Budget();
$budget->getFromDB($budgets_id);
$colspan = count($found_types) + 2;
echo "<div class='spaced'><table class='tab_cadre'>";
echo "<tr><th colspan='{$colspan}'>" . $LANG['financial'][108] . "</th></tr>";
echo "<tr><th>" . $LANG['entity'][0] . "</th>";
if (count($found_types)) {
foreach ($found_types as $type => $typename) {
echo "<th>{$typename}</th>";
}
}
echo "<th>" . $LANG['common'][33] . "</th>";
echo "</tr>";
foreach ($entities_values as $entity => $value) {
echo "<tr class='tab_bg_1'>";
echo "<td class='b'>" . Dropdown::getDropdownName('glpi_entities', $entity) . "</td>";
if (count($found_types)) {
foreach ($found_types as $type => $typename) {
echo "<td class='right'>";
$typevalue = 0;
if (isset($entitiestype_values[$entity][$type])) {
$typevalue = $entitiestype_values[$entity][$type];
}
echo formatNumber($typevalue);
echo "</td>";
}
}
echo "<td class='right b'>" . formatNumber($value) . "</td>";
echo "</tr>";
$total += $value;
}
echo "<tr class='tab_bg_1'><th colspan='{$colspan}'><br></th></tr>";
echo "<tr class='tab_bg_1'>";
echo "<td class='right' colspan='" . ($colspan - 1) . "'>" . $LANG['financial'][108] . "</td>";
echo "<td class='right b'>" . formatNumber($total) . "</td></tr>";
if ($_SESSION['glpiactive_entity'] == $budget->fields['entities_id']) {
echo "<tr class='tab_bg_1'>";
echo "<td class='right' colspan='" . ($colspan - 1) . "'>" . $LANG['financial'][109] . "</td>";
echo "<td class='right b'>" . formatNumber($budget->fields['value'] - $total) . "</td></tr>";
}
echo "</table></div>";
}
}
