You are not logged in.

Announcement

 Téléchargez la dernière version stable de GLPI      -     Et vous, que pouvez vous faire pour le projet GLPI ? :  Contribuer
 Download last stable version of GLPI                      -     What can you do for GLPI ? :  Contribute

#1 2017-12-01 00:51:43

bugless
Member
Registered: 2010-01-24
Posts: 37

Help needed with custom report

Hello.

I'm trying to make a custom report through glpi-reports plugin. I'm taking as direct base the "List of equipment by location" report since this is the one with the most columns showing by default. I just need to add some more columns that are not there.

So I created a new directory called "customrep" inside /usr/share/glpi/plugins/reports/report directory, and put there the 2 required files: an already modified copy of listequipmentbylocation.php renamed as customrep.php, and the language file.

File customrep.en_GB.php:

<?php
$LANG['plugin_reports']['customrep'] = 'List equipment';

File customrep.php:

1   <?php
2   //Left this line just in case version number is needed
3   //version Id: listequipmentbylocation.php 319 2015-10-02 10:21:16Z yllen
4
5   $USEDBREPLICATE        = 1;
6   $DBCONNECTION_REQUIRED = 0;
7
8   include ("../../../../inc/includes.php");
9
10  //TRANS: The name of the report = List of equipments by location
11  $report = new PluginReportsAutoReport(__($LANG['plugin_reports']['customrep'], 'reports'));
12  $loc    = new PluginReportsLocationCriteria($report);
13
14  $report->setColumns(array(new PluginReportsColumnType('itemtype', __('Type')),
15                            new PluginReportsColumnTypeLink('items_id', __('Item'),
16                                                            'itemtype', array('with_comment' => 1)),
17                            new PluginReportsColumn('serial', __('Serial number')),
18                            new PluginReportsColumn('otherserial', __('Inventory number')),
19                            new PluginReportsColumn('states_id', __('Status')),
20                            new PluginReportsColumnModelType('models_id', __('Model'),
21                                                             'itemtype', array('with_comment' => 1)),
22                            new PluginReportsColumnTypeType('types_id', __('Type'),
23                                                            'itemtype', array('with_comment' => 1))));
24
25  //Display criterias form is needed
26  $report->displayCriteriasForm();
27
28  //If criterias have been validated
29  if ($report->criteriasValidated()) {
30     $report->setSubNameAuto();
31
32     $query = getSqlSubRequest("Computer",$loc,new Computer());
33     foreach($CFG_GLPI["infocom_types"] as $itemtype) {
34        $obj = new $itemtype;
35        if ($obj->isField('locations_id')) {
36           $query.= "UNION (".getSqlSubRequest($itemtype,$loc,$obj).")";
37        }
38     }
39     $report->setGroupBy("entity","itemtype");
40     $report->setSqlRequest($query);
41     $report->execute();
42  }
43  else {
44     Html::footer();
45  }
46
47
48  function getSqlSubRequest($itemtype,$loc,$obj) {
49
50     $table     = getTableForItemType($itemtype);
51     $models_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Model'));
52     $types_id  = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Type'));
53     $fields    = array('name'        => 'name',
54                        'serial'      => 'serial',
55                        'otherserial' => 'otherserial',
56                        'states_id'   => 'states_id',
57                        $models_id    => 'models_id',
58                        $types_id     => 'types_id');
59
60     $query_where = "SELECT '$itemtype' AS itemtype,
61                            `$table`.`id` AS items_id,
62                            `$table`.`locations_id`";
63
64     foreach ($fields as $field => $alias) {
65        if ($obj->isField($field)) {
66           $query_where .= ", `$table`.`$field` AS $alias";
67        } else {
68           $query_where .= ", '' AS $alias";
69        }
70     }
71
72     $query_where .= " FROM `$table` ";
73
74     if ($obj->isEntityAssign()) {
75        $query_where .= getEntitiesRestrictRequest('WHERE', "$table");
76     } else {
77        $query_where .= 'WHERE 1';
78     }
79
80     if ($obj->maybeTemplate()) {
81        $query_where .= " AND `is_template`='0'";
82     }
83
84     if ($obj->maybeDeleted()) {
85        $query_where .= " AND `is_deleted`='0'";
86     }
87
88     $query_where .= $loc->getSqlCriteriasRestriction();
89
90     return $query_where;
91  }

I numbered the lines in this one for better clarity.
I have never handled php coding before so I'm total novice, though I already read glpi-reports docs.

First I wanted to add the Status column, so I tried by adding lines 19 and 56. Checked in glpi and the column got added indeed, but all I see is a number '1' in all the fields instead of the actual status text.

What am I doing wrong, or what else do I need?
Do I face the same problem if trying to add any other additional column?

Thanks.

Last edited by bugless (2017-12-14 03:59:07)

Offline

#2 2017-12-07 10:23:52

remi
GLPI-DEV
From: Champagne
Registered: 2007-04-28
Posts: 7,127
Website

Re: Help needed with custom report

See the "printers" report which have the state column.


Dév. Fedora 29 - PHP 5.6/7.0/7.1/7.2/7.3/7.4 - MariaDB 10.3 - GLPI master
Certifié ITILv3 - RPM pour Fedora, RHEL et CentOS sur https://blog.remirepo.net/

Offline

#3 2017-12-07 22:03:33

bugless
Member
Registered: 2010-01-24
Posts: 37

Re: Help needed with custom report

Printers report has "state" as field, but none of these report's fields works in customrep.php, report displays just blank fields in glpi.
I also searched for printer report's field names directly in mariadb database, and either they're non existent or they exist but in tables such as glpi_entitites, glpi_crontasks, glpi_plugins, and so, but neither related to computers, printers, or asset-related.
Printers report uses noin-existent fields! How the **** does it work?
Regardless it doesn't work for customrep.php

And how can I add more columns to customrep such as manufacturer, operating system, comments, and location itself? Not even status is working!

Wish @yllen can be back online, because remi is not willing to help (perhaps he got bothered enough in irc...)

Last edited by bugless (2017-12-14 04:18:09)

Offline

#4 2017-12-09 07:56:31

bugless
Member
Registered: 2010-01-24
Posts: 37

Re: Help needed with custom report

Truth to be told what I want to do is just a "global report", a simple report with no need to ask for any criteria beforehand but just displaying *all* global items with *all* common columns for everything even if some of them don't apply for a certain kind of item (say, the O.S. field for printer items just displaying blank).

At first I found the closest one doing what I needed was precisely the By location report, I just needed to *not* specify any location (i.e., leaving it as ---) in order to display everything globally. I found it was just missing some more columns.
I thought this should just be a matter of *slightly* modifying List of equipment by location report, for which no one has helped...

I already searched around the forums. Other users chose to make report from scratch following this link https://forge.glpi-project.org/projects … eateReport
But that looks already outdated: autoreport class is little to no documented, there are missing columns to be covered such as ModelType and TypeType, nothing that could explain what the "#$% getSqlSubRequest() function is for.......

Offline

#5 2017-12-11 23:18:02

emmahersen
Member
Registered: 2017-11-20
Posts: 6

Re: Help needed with custom report

Look at plugins/reports/report/location/Location.php =>

1. you create a new dir in plugins/reports/report/location/MyReport
2. you create a new file MyReport.php
3. in this file, you just define your columns and your SQL request

Offline

#6 2017-12-14 04:15:39

bugless
Member
Registered: 2010-01-24
Posts: 37

Re: Help needed with custom report

So I just realized why the create report link above and Location report are not very useful for me and why I based precisely on List of equipment by location report.

The create report article above only serves for querying some columns from one single sql table. If looking for columns from different sql tables one would need to make several reports and maybe putting all them together in one single file, making it too long and inefficient. Location report is just based on this.

List of equipment by location report has a way to include pc models, printer models, network device models, etc in one single column just depending of the item type, instead of using one column for printer model, another for pc model, etc. Same for device type column. All this probably done by the getSqlSubRequest() function itself.

I already tried what is said in this old thread http://forum.glpi-project.org/viewtopic.php?id=35976 (had to use google translate). Absolute no use.

yllen is perhaps gone for good already. Could anyone else really help please?

Offline

#7 2018-01-02 20:12:30

bugless
Member
Registered: 2010-01-24
Posts: 37

Re: Help needed with custom report

Any help please?

Offline

#8 2018-01-08 16:42:51

yllen
GLPI-DEV
From: Sillery (51)
Registered: 2008-01-14
Posts: 15,273

Re: Help needed with custom report

To have status name instead of number representing status, you must use PluginReportsStatusCriteria instead of PluginReportsColumn


CentOS 6.5 - CentOS 7.x
PHP 5.6 - PHP 7.x - MySQL 5.6  - MariaDB 10.2 + APC + oOPcache
GLPI from 0.72 to dev version
Certifiée ITIL (ITV2F, ITILF, ITILOSA)

Offline

#9 2018-01-09 18:47:50

bugless
Member
Registered: 2010-01-24
Posts: 37

Re: Help needed with custom report

@yllen:

Tried changing PluginReportsColumn for PluginReportsStatusCriteria in line 19 (see first post here), resulted in breaking report: now when selecting it it just directs to a totally blank page.

Do you think I need to do something like this? http://forum.glpi-project.org/viewtopic.php?id=35976

Also, and equally important as well please, how would I add more columns to customrep such as manufacturer, operating system, comments, and location itself? Would they be similar to status, or would each one be a totally different case?

Reading the file includes.php did not help too much...

If you have time please read whole thread to see what I have found and tried so far

thanks

Last edited by bugless (2018-01-09 20:04:36)

Offline

Board footer

Powered by FluxBB