* * Purpose of file: * Generate location report * Illustrate use of simpleReport * ---------------------------------------------------------------------- */ //Options for GLPI 0.71 and newer : need slave db to access the report $USEDBREPLICATE = 1; $DBCONNECTION_REQUIRED = 0; // Really a big SQL request include "../../../../inc/includes.php"; $report = new PluginReportsAutoReport(); new PluginReportsDateIntervalCriteria($report, 'order_date', __("Date of order", "order")); new PluginReportsDateIntervalCriteria($report, 'deliverydate', __("Delivery date")); new PluginReportsLocationCriteria($report, 'locations_id', __("Delivery location", "order")); new PluginReportsSupplierCriteria($report, 'suppliers_id', __("Supplier")); new PluginReportsDropdownCriteria($report, 'plugin_order_orderstates_id', 'PluginOrderOrderState', __("Status")); $report->displayCriteriasForm(); if ($report->criteriasValidated()) { $report->setSubNameAuto(); $report->setColumns(array(new PluginReportsColumnLink('entities_id', __("Entity"), 'Entity'), new PluginReportsColumnLink('id', __("Name"), 'PluginOrderOrder', array('with_comment' => true, 'with_navigate' => true)), new PluginReportsColumn('num_order'['plugin_order'][0]), new PluginReportsColumnLink('suppliers_id', __("Supplier"), 'Supplier'), new PluginReportsColumnLink('plugin_order_orderstates_id', __("Status"), 'PluginOrderOrderState', array('with_comment' => true)), new PluginReportsColumnDateTime('order_date', __("Date of order", "order")), new PluginReportsColumnDateTime('duedate', __("Estimated due date", "order")), new PluginReportsColumnDateTime('deliverydate', __("Delivery date")), new PluginReportsColumnLink('locations_id', __("Delivery location", "order"), 'Location', array('with_comment' => true)))); //TODO : ne pas chercher dans la poublelles $query = "SELECT * FROM `glpi_plugin_order_orders`"; $query .= getEntitiesRestrictRequest(" WHERE", "glpi_plugin_order_orders"); $query .= $report->addSqlCriteriasRestriction(); $query .= " AND `is_deleted`='0' AND `is_template`='0' "; $query .= "GROUP BY `entities_id`, `plugin_order_orderstates_id`, `num_order`, `order_date`"; $report->setGroupBy("entities_id", "plugin_order_orderstates_id", "num_order", "order_date"); $report->setSqlRequest($query); $report->execute(); }
https://forge.indepnet.net/projects/reports ------------------------------------------------------------------------- LICENSE This file is part of reports. reports is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. reports is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with reports. If not, see <http://www.gnu.org/licenses/>. -------------------------------------------------------------------------- */ $USEDBREPLICATE = 1; $DBCONNECTION_REQUIRED = 0; include "../../../../inc/includes.php"; //TRANS: The name of the report = Licenses by expiration date $report = new PluginReportsAutoReport(__('licensesexpires_report_title', 'reports')); $report->setColumns(array('expire' => __('Valid to', 'reports'), 'name' => __('License name'), 'software' => sprintf(__('%1$s - %2$s'), _n('Software', 'Software', 1), __('Purchase version')), 'serial' => __('Serial number'), 'completename' => __('Entity'), 'comments' => __('Comments'), 'ordinateur' => __('Computer'))); $query = "SELECT `glpi_softwarelicenses`.`expire`,\n `glpi_softwarelicenses`.`name`,\n CONCAT(`glpi_softwares`.`name`,' - ',buyversion.`name`) AS software,\n `glpi_softwarelicenses`.`serial`,\n `glpi_entities`.`completename`,\n `glpi_softwarelicenses`.`comment`,\n `glpi_computers`.`name` AS ordinateur\n FROM `glpi_softwarelicenses`\n LEFT JOIN `glpi_softwares`\n ON (`glpi_softwarelicenses`.`softwares_id` = `glpi_softwares`.`id`)\n LEFT JOIN `glpi_softwarelicensetypes`\n ON (`glpi_softwarelicensetypes`.`id`=`glpi_softwarelicenses`.`softwarelicensetypes_id`)\n LEFT JOIN `glpi_softwareversions` AS buyversion\n ON (buyversion.`id` = `glpi_softwarelicenses`.`softwareversions_id_buy`)\n LEFT JOIN `glpi_entities`\n ON (`glpi_softwares`.`entities_id` = `glpi_entities`.`id`)\n LEFT JOIN `glpi_computers_softwarelicenses`\n ON (`glpi_softwarelicenses`.`id` = `glpi_computers_softwarelicenses`.`softwarelicenses_id`)\n LEFT JOIN `glpi_computers`\n ON (`glpi_computers`.`id` = `glpi_computers_softwarelicenses`.`computers_id`)\n WHERE `glpi_softwares`.`is_deleted` = '0'\n AND `glpi_softwares`.`is_template` = '0' " . getEntitiesRestrictRequest(' AND ', 'glpi_softwarelicenses') . "\n ORDER BY `glpi_softwarelicenses`.`expire`, `name`"; $report->setGroupBy(array('expire', 'name')); $report->setSqlRequest($query); $report->execute();
reports is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. reports is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with reports. If not, see <http://www.gnu.org/licenses/>. -------------------------------------------------------------------------- */ $USEDBREPLICATE = 1; $DBCONNECTION_REQUIRED = 0; include "../../../../inc/includes.php"; //TRANS: The name of the report = Detailed license report $report = new PluginReportsAutoReport(__('licenses_report_title', 'reports')); $license = new PluginReportsSoftwareWithLicenseCriteria($report); $license->setSqlField("`glpi_softwarelicenses`.`softwares_id`"); $report->displayCriteriasForm(); // Form validate and only one software with license if ($report->criteriasValidated() && $license->getParameterValue() > 0) { $report->setSubNameAuto(); $report->setColumns(array("license" => _n('License', 'Licenses', 2), "serial" => __('Serial number'), "nombre" => _x('Quantity', 'Number'), "type" => __('Type'), "buy" => __('Purchase version'), "used" => __('Used version', 'reports'), "expire" => __('Expiration'), "comment" => __('Comments'), "name" => __('Computer'))); $query = "SELECT `glpi_softwarelicenses`.`name` AS license,\n `glpi_softwarelicenses`.`serial`,\n `glpi_softwarelicenses`.`number` AS nombre,\n `glpi_softwarelicensetypes`.`name` AS type,\n buyversion.`name` AS buy,\n useversion.`name` AS used,\n `glpi_softwarelicenses`.`expire`,\n `glpi_softwarelicenses`.`comment`,\n `glpi_computers`.`name`\n FROM `glpi_softwarelicenses`\n LEFT JOIN `glpi_softwares`\n ON (`glpi_softwarelicenses`.`softwares_id` = `glpi_softwares`.`id`)\n LEFT JOIN `glpi_computers_softwarelicenses`\n ON (`glpi_softwarelicenses`.`id`\n = `glpi_computers_softwarelicenses`.`softwarelicenses_id`)\n LEFT JOIN `glpi_computers`\n ON (`glpi_computers`.`id` = `glpi_computers_softwarelicenses`.`computers_id`)\n LEFT JOIN `glpi_softwareversions` AS buyversion\n ON (buyversion.`id` = `glpi_softwarelicenses`.`softwareversions_id_buy`)\n LEFT JOIN `glpi_softwareversions` AS useversion\n ON (useversion.`id` = `glpi_softwarelicenses`.`softwareversions_id_use`)\n LEFT JOIN `glpi_softwarelicensetypes`\n ON (`glpi_softwarelicensetypes`.`id`\n =`glpi_softwarelicenses`.`softwarelicensetypes_id`)\n LEFT JOIN `glpi_entities`\n ON (`glpi_softwares`.`entities_id` = `glpi_entities`.`id`)" . $report->addSqlCriteriasRestriction("WHERE") . "\n AND `glpi_softwares`.`is_deleted` = '0'\n AND `glpi_softwares`.`is_template` = '0' " . getEntitiesRestrictRequest(' AND ', 'glpi_softwares') . "\n ORDER BY license"; $report->setGroupBy("license"); $report->setSqlRequest($query); $report->execute(); }
(at your option) any later version. Reports is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details. You should have received a copy of the GNU Affero General Public License along with Reports. If not, see <http://www.gnu.org/licenses/>. @package reports @authors Nelly Mahu-Lasson, Remi Collet, Benoit Machiavello @copyright Copyright (c) 2009-2015 Reports plugin team @license AGPL License 3.0 or (at your option) any later version http://www.gnu.org/licenses/agpl-3.0-standalone.html @link https://forge.indepnet.net/projects/reports @link http://www.glpi-project.org/ @since 2009 -------------------------------------------------------------------------- */ $USEDBREPLICATE = 1; $DBCONNECTION_REQUIRED = 0; include "../../../../inc/includes.php"; //TRANS: The name of the report = List of groups and members $report = new PluginReportsAutoReport(__('listgroups_report_title', 'reports')); //$group = new GroupCriteria($report); $report->setColumns(array(new PluginReportsColumn('completename', __('Entity')), new PluginReportsColumnLink('groupid', __('Group'), 'Group'), new PluginReportsColumnLink('userid', __('Login'), 'User'), new PluginReportsColumn('firstname', __('First name')), new PluginReportsColumn('realname', __('Surname')), new PluginReportsColumnDateTime('last_login', __('Last login')))); $query = "SELECT `glpi_entities`.`completename`,\n `glpi_groups`.`id` AS groupid,\n `glpi_users`.`id` AS userid,\n `glpi_users`.`firstname`,\n `glpi_users`.`realname`,\n `glpi_users`.`last_login`\n FROM `glpi_groups`\n LEFT JOIN `glpi_groups_users` ON (`glpi_groups_users`.`groups_id` = `glpi_groups`.`id`)\n LEFT JOIN `glpi_users` ON (`glpi_groups_users`.`users_id` = `glpi_users`.`id`\n AND `glpi_users`.`is_deleted` = '0' )\n LEFT JOIN `glpi_entities` ON (`glpi_groups`.`entities_id` = `glpi_entities`.`id`)" . getEntitiesRestrictRequest(" WHERE ", "glpi_groups") . "\n ORDER BY `completename`, `glpi_groups`.`name`, `glpi_users`.`name`"; $report->setGroupBy(array('completename', 'groupid')); $report->setSqlRequest($query); $report->execute();
$DBCONNECTION_REQUIRED = 0; include "../../../../inc/includes.php"; //TRANS: The name of the report = Search in the financial information (plural) $report = new PluginReportsAutoReport(__('searchinfocom_report_title', 'reports')); //Report's search criterias new PluginReportsDateIntervalCriteria($report, 'order_date', __('Order date')); new PluginReportsDateIntervalCriteria($report, 'buy_date', __('Date of purchase')); new PluginReportsDateIntervalCriteria($report, 'delivery_date', __('Delivery date')); new PluginReportsDateIntervalCriteria($report, 'use_date', __('Startup date')); new PluginReportsDateIntervalCriteria($report, 'inventory_date', __('Date of last physical inventory')); new PluginReportsTextCriteria($report, 'immo_number', __('Immobilization number')); new PluginReportsTextCriteria($report, 'order_number', __('Order number')); new PluginReportsTextCriteria($report, 'delivery_number', __('Delivery form')); new PluginReportsDropdownCriteria($report, 'budgets_id', 'glpi_budgets', __('Budget')); //Display criterias form is needed $report->displayCriteriasForm(); //If criterias have been validated if ($report->criteriasValidated()) { // Report title $report->setSubNameAuto(); // Report Columns $cols = array(new PluginReportsColumnType('itemtype', __('Type')), new PluginReportsColumnTypeLink('items_id', __('Item'), 'itemtype', array('with_comment' => 1)), new PluginReportsColumnDate('order_date', __('Order date')), new PluginReportsColumn('order_number', __('Order number')), new PluginReportsColumnDate('buy_date', __('Date of purchase')), new PluginReportsColumn('delivery_date', __('Delivery date')), new PluginReportsColumn('delivery_number', __('Delivery form')), new PluginReportsColumn('immo_number', __('Immobilization number')), new PluginReportsColumnDate('use_date', __('Startup date')), new PluginReportsColumnDate('inventory_date', __('Date of last physical inventory')), new PluginReportsColumnLink('budgets_id', __('Budget'), 'Budget')); $report->setColumns($cols); // Build SQL request $sql = "SELECT *\n FROM `glpi_infocoms`\n WHERE `itemtype` NOT IN ('Software', 'CartridgeItem', 'ConsumableItem')" . $report->addSqlCriteriasRestriction() . getEntitiesRestrictRequest('AND', 'glpi_infocoms') . "ORDER BY `itemtype`"; $report->setGroupBy('itemtype'); $report->setSqlRequest($sql); $report->execute(); } else { Html::footer(); }
the Free Software Foundation, either version 3 of the License, or (at your option) any later version. Reports is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details. You should have received a copy of the GNU Affero General Public License along with Reports. If not, see <http://www.gnu.org/licenses/>. @package reports @authors Nelly Mahu-Lasson, Remi Collet, Alexandre Delaunay @copyright Copyright (c) 2009-2015 Reports plugin team @license AGPL License 3.0 or (at your option) any later version http://www.gnu.org/licenses/agpl-3.0-standalone.html @link https://forge.indepnet.net/projects/reports @link http://www.glpi-project.org/ @since 2009 -------------------------------------------------------------------------- */ $USEDBREPLICATE = 1; $DBCONNECTION_REQUIRED = 0; include "../../../../inc/includes.php"; //TRANS: The name of the report = Number of equipments by location $report = new PluginReportsAutoReport(__('equipmentbylocation_report_title', 'reports')); $report->setColumns(array(new PluginReportsColumn('entity', __('Entity')), new PluginReportsColumn('location', __('Location')), new PluginReportsColumnInteger('computernumber', _n('Computer', 'Computers', 2)), new PluginReportsColumnInteger('networknumber', _n('Network', 'Networks', 2)), new PluginReportsColumnInteger('monitornumber', _n('Monitor', 'Monitors', 2)), new PluginReportsColumnInteger('printernumber', _n('Printer', 'Printers', 2)), new PluginReportsColumnInteger('peripheralnumber', _n('Device', 'Devices', 2)), new PluginReportsColumnInteger('phonenumber', _n('Phone', 'Phones', 2)))); $query = "SELECT i.`entity`, i.`location`, i.`computernumber`, i.`networknumber`,\n i.`monitornumber`, i.`printernumber`, j.`peripheralnumber`, l.`phonenumber`\n FROM (SELECT g.`entity`, g.`location`, g.`computernumber`, g.`networknumber`,\n g.`monitornumber`, h.`printernumber`, g.`id`\n FROM (SELECT e.`entity`, e.`location`, e.`computernumber`, e.`networknumber`,\n f.`monitornumber`, e.`id`\n FROM (SELECT c.`entity`, c.`location`, c.`computernumber`, d.`networknumber`,\n c.`id`\n FROM (SELECT a.`entity`, a.`location`, b.`computernumber`, a.`id`\n FROM (SELECT `glpi_entities`.`completename` AS entity,\n `glpi_locations`.`completename` AS location,\n `glpi_locations`.`id` AS id\n FROM `glpi_locations`\n LEFT JOIN `glpi_entities`\n ON (`glpi_locations`.`entities_id`=`glpi_entities`.`id`) " . getEntitiesRestrictRequest(" WHERE ", "glpi_locations") . ") a\n LEFT OUTER JOIN (SELECT count(*) AS computernumber,\n `glpi_computers`.`locations_id` AS id\n FROM `glpi_computers`\n WHERE is_deleted=0 AND is_template=0\n " . getEntitiesRestrictRequest(" AND ", "glpi_computers") . "\n GROUP BY `glpi_computers`.`locations_id`) b\n ON (a.id = b.id)\n ) c\n LEFT OUTER JOIN (SELECT count(*) AS networknumber,\n `glpi_networkequipments`.`locations_id` AS id\n FROM `glpi_networkequipments`\n WHERE is_deleted=0 AND is_template=0\n " . getEntitiesRestrictRequest(" AND ", "glpi_networkequipments") . "\n GROUP BY `glpi_networkequipments`.`locations_id`) d\n ON (c.id = d.id)\n ) e\n LEFT OUTER JOIN (SELECT count(*) AS monitornumber,\n `glpi_monitors`.`locations_id` AS id\n FROM `glpi_monitors`\n WHERE is_deleted=0 AND is_template=0\n " . getEntitiesRestrictRequest(" AND ", "glpi_monitors") . "\n GROUP BY `glpi_monitors`.`locations_id`) f\n ON (e.id = f.id)\n ) g\n LEFT OUTER JOIN (SELECT count(*) AS printernumber,\n `glpi_printers`.`locations_id` AS id\n FROM `glpi_printers`\n WHERE is_deleted=0 AND is_template=0\n " . getEntitiesRestrictRequest(" AND ", "glpi_printers") . "\n GROUP BY `glpi_printers`.`locations_id`) h\n ON (g.id = h.id)\n ) i\n LEFT OUTER JOIN (SELECT count(*) AS peripheralnumber,\n `glpi_peripherals`.`locations_id` AS id\n FROM `glpi_peripherals`\n WHERE is_deleted=0 AND is_template=0\n " . getEntitiesRestrictRequest(" AND ", "glpi_peripherals") . "\n GROUP BY `glpi_peripherals`.`locations_id`) j\n ON (i.id = j.id)\n LEFT OUTER JOIN (SELECT count(*) AS phonenumber,\n `glpi_phones`.`locations_id` AS id\n FROM `glpi_phones`\n WHERE is_deleted=0 AND is_template=0\n " . getEntitiesRestrictRequest(" AND ", "glpi_phones") . "\n GROUP BY `glpi_phones`.`locations_id`) l\n ON (i.id = l.id)\n ORDER BY i.entity, i.location"; $report->setGroupBy("entity"); $report->setSqlRequest($query); $report->execute();
} if ($item->isField('locations_id')) { $select .= ", `glpi_locations`.`completename` AS location\n , `glpi_locations`.`building`\n , `glpi_locations`.`room`"; $from .= "LEFT JOIN `glpi_locations`\n ON (`glpi_locations`.`id` = `{$table}`.`locations_id`)"; } else { $select .= ", '' AS location, '' AS building, '' AS room"; } $select .= ", `glpi_infocoms`.*\n , `glpi_infocoms`.`suppliers_id` AS supplier\n , `glpi_budgets`.`name` AS budget"; $from .= "LEFT JOIN `glpi_infocoms`\n ON (`glpi_infocoms`.`itemtype` = '{$itemtype}'\n AND `glpi_infocoms`.`items_id` = `{$table}`.`id`)\n LEFT JOIN `glpi_budgets`\n ON (`glpi_budgets`.`id` = `glpi_infocoms`.`budgets_id`)"; if ($item->maybeDeleted()) { $where .= " AND `{$table}`.`is_deleted` = 0 "; } if ($item->maybeTemplate()) { $where .= " AND `{$table}`.`is_template` = 0 "; } if ($item->isEntityAssign()) { $where .= getEntitiesRestrictRequest(" AND ", $table); } $where .= $budg->getSqlCriteriasRestriction(); $where .= $date->getSqlCriteriasRestriction(); if ($sql) { $sql .= " UNION "; } $sql .= "({$select} {$from} {$where})"; } $report->setGroupBy('entity'); $report->setSqlRequest($sql); $report->execute(); } else { Html::footer(); }
$report = new PluginReportsAutoReport(__('listequipmentbylocation_report_title', 'reports')); $loc = new PluginReportsLocationCriteria($report); $report->setColumns(array(new PluginReportsColumnType('itemtype', __('Type')), new PluginReportsColumnTypeLink('items_id', __('Item'), 'itemtype', array('with_comment' => 1)), new PluginReportsColumn('serial', __('Serial number')), new PluginReportsColumn('otherserial', __('Inventory number')), new PluginReportsColumnModelType('models_id', __('Model'), 'itemtype', array('with_comment' => 1)), new PluginReportsColumnTypeType('types_id', __('Type'), 'itemtype', array('with_comment' => 1)))); //Display criterias form is needed $report->displayCriteriasForm(); //If criterias have been validated if ($report->criteriasValidated()) { $report->setSubNameAuto(); $query = getSqlSubRequest("Computer", $loc, new Computer()); foreach ($CFG_GLPI["infocom_types"] as $itemtype) { $obj = new $itemtype(); if ($obj->isField('locations_id')) { $query .= "UNION (" . getSqlSubRequest($itemtype, $loc, $obj) . ")"; } } $report->setGroupBy("entity", "itemtype"); $report->setSqlRequest($query); $report->execute(); } else { Html::footer(); } function getSqlSubRequest($itemtype, $loc, $obj) { $table = getTableForItemType($itemtype); $models_id = getForeignKeyFieldForTable(getTableForItemType($itemtype . 'Model')); $types_id = getForeignKeyFieldForTable(getTableForItemType($itemtype . 'Type')); $fields = array('name' => 'name', 'serial' => 'serial', 'otherserial' => 'otherserial', $models_id => 'models_id', $types_id => 'types_id'); $query_where = "SELECT '{$itemtype}' AS itemtype,\n `{$table}`.`id` AS items_id,\n `{$table}`.`locations_id`"; foreach ($fields as $field => $alias) { if ($obj->isField($field)) { $query_where .= ", `{$table}`.`{$field}` AS {$alias}";
* ---------------------------------------------------------------------- */ //Options for GLPI 0.71 and newer : need slave db to access the report $USEDBREPLICATE = 1; $DBCONNECTION_REQUIRED = 0; // Really a big SQL request include "../../../../inc/includes.php"; $report = new PluginReportsAutoReport(); new PluginReportsDateIntervalCriteria($report, 'order_date', __("Date of order", "order")); new PluginReportsDateIntervalCriteria($report, 'deliverydate', __("Delivery date")); new PluginReportsLocationCriteria($report, 'locations_id', __("Delivery location", "order")); new PluginReportsSupplierCriteria($report, 'suppliers_id', __("Supplier")); new PluginReportsDropdownCriteria($report, 'plugin_order_orderstates_id', 'PluginOrderOrderState', __("Status")); $report->displayCriteriasForm(); if ($report->criteriasValidated()) { $report->setSubNameAuto(); $report->setColumns(array(new PluginReportsColumnLink('suppliers_id', __("Supplier"), 'Supplier'), new PluginReportsColumnLink('entities_id', __("Entity"), 'Entity'), new PluginReportsColumnInteger('total'['plugin_order']['deliveryinfos'][2]), new PluginReportsColumnInteger('late'['plugin_order']['deliveryinfos'][3]))); //TODO : ne pas chercher dans la poublelles $query_total = "SELECT count(*) FROM `glpi_plugin_order_orders`"; $query_total .= getEntitiesRestrictRequest(" WHERE", "glpi_plugin_order_orders"); $query_total .= $report->addSqlCriteriasRestriction(); $query_total .= "AND `glpi_plugin_order_orders`.`suppliers_id`=`suppliers`.`id`"; $query_late = $query_total . " AND `is_late`='1' AND `is_deleted`='0' AND `is_template`='0'"; $supplier = "JOIN `glpi_suppliers`as suppliers\n ON (`glpi_plugin_order_orders`.`suppliers_id` = suppliers.`id`)"; $query = "SELECT DISTINCT `suppliers_id`, ({$query_total}) AS `total`, ({$query_late}) AS `late`\n FROM `glpi_plugin_order_orders` {$supplier}"; $query .= getEntitiesRestrictRequest(" WHERE", "glpi_plugin_order_orders"); $query .= $report->addSqlCriteriasRestriction(); $report->setGroupBy("suppliers_id"); $report->setSqlRequest($query); $report->execute(); }