You are not logged in.
So finally managed to create custom report based on "List of equipment by location" one, just wanted to try sharing in case someone else could see it useful for some ideas.
Using glpi 9.2.1, php 7.2 and reports 1.11.1, which finally has Status column included by default.
I procured to do stuff only in report itself, without touching anything else such as other glpi's class files.
Created new directory called "customrep" inside /usr/share/glpi/plugins/reports/report directory, and put the 2 required files inside: language file customrep.en_GB.php and report file customrep.php; this taken from https://forge.glpi-project.org/projects … eateReport link.
File customrep.en_GB.php:
<?php
$LANG['plugin_reports']['customrep'] = 'List equipment custom';File customrep.php:
1 <?php
2 //Based on:
3 //* @version $Id: listequipmentbylocation.php 352 2018-01-15 16:13:51Z yllen $
4
5 $USEDBREPLICATE = 1;
6 $DBCONNECTION_REQUIRED = 0;
7
8 include ("../../../../inc/includes.php");
9
10 //TRANS: The name of the report = List equipment custom
11 $report = new PluginReportsAutoReport(__($LANG['plugin_reports']['customrep'], 'reports'));
12 $loc = new PluginReportsLocationCriteria($report);
13
14 $report->setColumns([new PluginReportsColumnType('itemtype', __('Type')),
15 new PluginReportsColumnTypeLink('items_id', __('Item'), 'itemtype',
16 ['with_comment' => 1]),
17 new PluginReportsColumn('otherserial', __('Inventory number')),
18 new PluginReportsColumnTypeType('types_id', __('Sub type'), 'itemtype',
19 ['with_comment' => 1]),
20 new PluginReportsColumnModelType('models_id', __('Model'), 'itemtype',
21 ['with_comment' => 1]),
22 new PluginReportsColumn('serial', __('Serial number')),
23 new PluginReportsColumn('osname', __('Operating System')),
24 new PluginReportsColumn('locname', __('Location')),
25 new PluginReportsColumn('comment', __('Comment')),
26 new PluginReportsColumn('statename', __('Status'))]);
27
28 //Display criterias form is needed
29 $report->displayCriteriasForm();
30
31 //If criterias have been validated
32 if ($report->criteriasValidated()) {
33 $report->setSubNameAuto();
34
35 $query = getSqlSubRequest("Computer",$loc,new Computer());
36 foreach($CFG_GLPI["infocom_types"] as $itemtype) {
37 $obj = new $itemtype;
38 if ($obj->isField('locations_id')) {
39 if ($itemtype != "Computer") {
40 $query.= " UNION (".getSqlSubRequest($itemtype,$loc,$obj).")";
41 }
42 }
43 }
44 $report->setGroupBy("entity","itemtype");
45 $report->setSqlRequest($query);
46 $report->execute();
47 }
48 else {
49 Html::footer();
50 }
51
52
53 function getSqlSubRequest($itemtype,$loc,$obj) {
54
55 $dbu = new DbUtils();
56
57 $table = getTableForItemType($itemtype);
58 $models_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Model'));
59 $types_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Type'));
60 $fields = ['name' => 'name',
61 'otherserial' => 'otherserial',
62 $types_id => 'types_id',
63 $models_id => 'models_id',
64 'serial' => 'serial',
65 'id' => 'id',
66 'locations_id' => 'locations_id',
67 'comment' => 'comment',
68 'states_id' => 'states_id'];
69
70 $query_where = "SELECT '$itemtype' AS itemtype,
71 `$table`.`id` AS items_id,
72 `$table`.`locations_id`";
73
74 $join = "";
75 foreach ($fields as $field => $alias) {
76 if ($obj->isField($field)) {
77 if ($field == 'id' && $itemtype == 'Computer') {
78 $query_where .= ", `glpi_operatingsystems`.`name` AS osname";
79 $join .= "LEFT JOIN `glpi_items_operatingsystems` ON `glpi_items_operatingsystems`.`items_id` = `$table`.`id`
80 AND `glpi_items_operatingsystems`.`itemtype` = 'Computer'
81 LEFT JOIN `glpi_operatingsystems` ON `glpi_operatingsystems`.`id` = `glpi_items_operatingsystems`.`operatingsystems_id` ";
82 } elseif ($field == 'id' && $itemtype != 'Computer') {
83 $query_where .= ", '' AS $alias";
84 } elseif ($field == 'locations_id') {
85 $query_where .= ", `glpi_locations`.`name` AS locname";
86 $join .= "LEFT JOIN `glpi_locations` ON `glpi_locations`.`id` = `$table`.`locations_id` ";
87 } elseif ($field == 'states_id') {
88 $query_where .= ", `glpi_states`.`name` AS statename";
89 $join .= "LEFT JOIN `glpi_states` ON `glpi_states`.`id` = `$table`.`states_id` ";
90 } else {
91 $query_where .= ", `$table`.`$field` AS $alias";
92 }
93 } else {
94 $query_where .= ", '' AS $alias";
95 }
96 }
97
98 $query_where .= " FROM `$table` $join ";
99
100 if ($obj->isEntityAssign()) {
101 $query_where .= $dbu->getEntitiesRestrictRequest('WHERE', "$table");
102 } else {
103 $query_where .= 'WHERE 1';
104 }
105
106 if ($obj->maybeTemplate()) {
107 $query_where .= " AND `is_template`='0'";
108 }
109
110 if ($obj->maybeDeleted()) {
111 //Needed to allow O.S. displaying
112 if ($itemtype == 'Computer') {
113 $query_where .= " AND `$table`.`is_deleted`='0'";
114 } else {
115 $query_where .= " AND `is_deleted`='0'";
116 }
117 }
118
119 //Needed to allow locations displaying
120 if ($loc->getSqlCriteriasRestriction() != "") {
121 $query_where .= " " . substr_replace($loc->getSqlCriteriasRestriction(), "$table.", 4, 0);
122 } else {
123 $query_where .= "";
124 }
125
126 return $query_where;
127 }Logs in /var/log/glpi/sql-errors.log were imperative to be able to debug. For some reason enabling debug mode in glpi didn't make logs to be generated in /var/log/glpi/php-errors.log, so it didn't help at all. Or maybe I don't know how to use the debug mode...
All of the following may much likely sound just trivial or even foolish to the devs.
So I added more fields and changed their order.
I noticed fields of type varchar or text, such as "otherserial" or "comment" respectively, don't need an "if" block inside the function (starting at line 77).
I'm still not sure if it would be harmless to just leave all fields in general, such as "is template" and "is deleted" ones (lines 106-117) always prefixed with the corresponding table (computers, monitors...). Should it be the case, line 107 could be changed and line 113 would be the only needed one there.
I admit lines 120-124 may be kind of a dirty part or the like, but this part looks useful when leaving selected criteria in blank (i.e., no criteria -> ALL items) in report GUI.
Offline
Now for the "suggestions" part, perhaps it would kind of look like a few bugs report...
Regarding lines 39-41, I added the "if" block because I noticed in the sql-errors.log file that getSqlSubRequest function was repeating itself *twice* for one same itemtype, Computers: first one being the very first function instance -before any UNION clause-, and the other one during the infocom_types loop that walks thorugh all itemtypes, including Computers itself again.
Also, I noticed this very same old bug http://forum.glpi-project.org/viewtopic … 47#p171747 stills there. Where exactly was it "fixed"? It is not neither in current glpi release itself here nor github.
EDIT: put this here http://forum.glpi-project.org/viewtopic.php?id=160646, though devs don't seem to care anymore.
Last edited by bugless (2018-02-14 19:04:10)
Offline