modification du rapport listequipmentbylocation

bruno.desmet · 2014-04-01 17:38:03

bonjour,

Pour des besoins de gestion, je voudrais modifier le rapport listequipmentbylocation afin d'y ajouter des colonnes.

J'ai réussi pour les informations directement interrogeable dans la table computer, mais je n'arrive pas à rajouter la colonne qui devrait afficher les status qui se trouvent dans la table glpi_states

Peut-être auriez vous une idées

Je joins mon travail en l'état

<?php
/*
* @version $Id: listequipmentbylocation.php 198 2011-10-27 12:00:52Z remi $
-------------------------------------------------------------------------
reports - Additional reports plugin for GLPI
Copyright (C) 2003-2011 by the reports Development Team.

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/>.
--------------------------------------------------------------------------
*/

/*
* ----------------------------------------------------------------------
* Original Author of file: Nelly Lasson
*
* 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;

define('GLPI_ROOT', '../../../..');
include (GLPI_ROOT . "/inc/includes.php");

$report = new PluginReportsAutoReport();
$loc = new PluginReportsLocationCriteria($report);

$report->setColumns(array(new PluginReportsColumnType('itemtype', 'Composant'),
new PluginReportsColumnTypeLink('items_id', 'Nom',
'itemtype', array('with_comment' => 1)),
new PluginReportsColumn('serial', 'N° de série'),
new PluginReportsColumn('otherserial', 'N° d’inventaire'),
new PluginReportsColumnModelType('models_id', $LANG['common'][22],
'itemtype', array('with_comment' => 1)),
new PluginReportsColumnTypeType('types_id', $LANG['common'][17],
'itemtype', array('with_comment' => 1)),

   new PluginReportsColumnModelType('states_id', $LANG['state'][0],
'itemtype', array('with_comment' => 1)),
   new PluginReportsColumn('comment', $LANG['common'][25]),
   ));

//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'));

$states_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Status'));

$types_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Type'));

$fields = array('name' => 'name',
'serial' => 'serial',
'otherserial' => 'otherserial',
   'comment' => 'comment',
$models_id => 'models_id',

   $states_id => 'states_id',

$types_id => 'types_id');

$query_where = "SELECT '$itemtype' AS itemtype,
`$table`.`id` AS items_id,
`$table`.`locations_id`";

foreach ($fields as $field => $alias) {
if ($obj->isField($field)) {
$query_where .= ", `$table`.`$field` AS $alias";
} else {
$query_where .= ", '' AS $alias";
}
}

$query_where .= " FROM `$table` ";

if ($obj->isEntityAssign()) {
$query_where .= getEntitiesRestrictRequest('WHERE', "$table");
} else {
$query_where .= 'WHERE 1';
}

if ($obj->maybeTemplate()) {
$query_where .= " AND `is_template`='0'";
}

if ($obj->maybeDeleted()) {
$query_where .= " AND `is_deleted`='0'";
}

$query_where .= $loc->getSqlCriteriasRestriction();

return $query_where;
}
?>

yllen · 2014-04-04 11:18:56

Il faut ajouter une jointure ave class table glpi_states

$query_where .= " FROM `$table`
LEFT JOIN `glpi_states` ON `glpi_states` .`id` = `$table`.`states_id`

bruno.desmet · 2014-04-04 13:37:02

Bonjour et merci pour ta réponse

cela semble coincé au niveau d'une table :

Unknown column 'glpi_cartridgeitems.states_id' in 'on clause'

je remets le source pour que tu puisse voir la modification

<?php
/*
* @version $Id: listequipmentbylocation.php 198 2011-10-27 12:00:52Z remi $
-------------------------------------------------------------------------
reports - Additional reports plugin for GLPI
Copyright (C) 2003-2011 by the reports Development Team.

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/>.
--------------------------------------------------------------------------
*/

/*
* ----------------------------------------------------------------------
* Original Author of file: Nelly Lasson
*
* 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;

define('GLPI_ROOT', '../../../..');
include (GLPI_ROOT . "/inc/includes.php");

$report = new PluginReportsAutoReport();
$loc = new PluginReportsLocationCriteria($report);

/* $report->setColumns(array(new PluginReportsColumnType('itemtype', 'Composant'),
new PluginReportsColumnTypeType('types_id', $LANG['common'][17],
'itemtype', array('with_comment' => 1)),
   new PluginReportsColumnModelType('models_id', $LANG['common'][22],
'itemtype', array('with_comment' => 1)),
   new PluginReportsColumn('serial', 'N° de série'),
   new PluginReportsColumn('otherserial', 'N° d’inventaire'),
   new PluginReportsColumnTypeLink('items_id', 'Nom',
'itemtype', array('with_comment' => 1)),



   new PluginReportsColumn('comment', $LANG['common'][25]),
   ));
*/

$report->setColumns(array(new PluginReportsColumnType('itemtype', 'Composant'),
new PluginReportsColumnTypeType('types_id', $LANG['common'][17],'itemtype', array('with_comment' => 1)),
   new PluginReportsColumnModelType('models_id', $LANG['common'][22],'itemtype', array('with_comment' => 1)),
   new PluginReportsColumn('serial', 'N° de série'),
   new PluginReportsColumn('otherserial', 'N° d’inventaire'),
   new PluginReportsColumnTypeLink('items_id', 'Nom','itemtype', array('with_comment' => 1)),
new PluginReportsColumn('comment', $LANG['common'][25]),
   new PluginReportsColumn('states_id', 'etat'),

   )
   );

//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).")";


}

//echo $query ;
}

$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'));

//$states_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'State'));
$states_id = 'states_id';
//echo $models_id ;
//echo $states_id ;

$fields = array('name' => 'name',
'serial' => 'serial',
'otherserial' => 'otherserial',
   'comment' => 'comment',
$models_id => 'models_id',

   'states_id' => 'states_id',

$types_id => 'types_id');

$query_where = "SELECT '$itemtype' AS itemtype,
`$table`.`id` AS items_id,
`$table`.`locations_id`";

foreach ($fields as $field => $alias) {

   if ($obj->isField($field)) {
$query_where .= ", `$table`.`$field` AS $alias";

   //echo $query_where ;


} else {
$query_where .= ", '' AS $alias";
}
}

// LEFT JOIN `glpi_states` ON `glpi_states` .`id` = `$table`.`states_id`";
//$query_where .= " FROM `$table` LEFT JOIN `glpi_states` ON `glpi_states` .`id` = `$table`.`states_id`
$query_where .= " FROM `$table` LEFT JOIN `glpi_states` ON `glpi_states` .`id` = `$table`.`states_id`";

// $query_where .= " FROM `$table` ";

if ($obj->isEntityAssign()) {
$query_where .= getEntitiesRestrictRequest('WHERE', "$table");

} else {
$query_where .= 'WHERE 1';
}

if ($obj->maybeTemplate()) {
$query_where .= " AND `is_template`='0'";
}

if ($obj->maybeDeleted()) {
$query_where .= " AND `is_deleted`='0'";
}

$query_where .= $loc->getSqlCriteriasRestriction();

return $query_where;

}
?>

yllen wrote:

Il faut ajouter une jointure ave class table glpi_states
$query_where .= " FROM `$table`
LEFT JOIN `glpi_states` ON `glpi_states` .`id` = `$table`.`states_id`

yllen · 2014-04-04 14:09:19

Dans ce cas il faut faire un LEFT JOIN conditionnel
if ($obj->isField('states_id')) {
$query_where .= LEFT JOIN...
}

bruno.desmet · 2014-04-04 15:07:33

yllen wrote:

Dans ce cas il faut faire un LEFT JOIN conditionnel
if ($obj->isField('states_id')) {
$query_where .= LEFT JOIN...
}

ce que j'ai fait, sauf l'information retournée n'est pas celle du champ nom dans la table glpi_states, mais celle du champ states_id de la table computer :-(

<?php
/*
* @version $Id: listequipmentbylocation.php 198 2011-10-27 12:00:52Z remi $
-------------------------------------------------------------------------
reports - Additional reports plugin for GLPI
Copyright (C) 2003-2011 by the reports Development Team.

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/>.
--------------------------------------------------------------------------
*/

/*
* ----------------------------------------------------------------------
* Original Author of file: Nelly Lasson
*
* 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;

define('GLPI_ROOT', '../../../..');
include (GLPI_ROOT . "/inc/includes.php");

$report = new PluginReportsAutoReport();
$loc = new PluginReportsLocationCriteria($report);

/* $report->setColumns(array(new PluginReportsColumnType('itemtype', 'Composant'),
new PluginReportsColumnTypeType('types_id', $LANG['common'][17],
'itemtype', array('with_comment' => 1)),
   new PluginReportsColumnModelType('models_id', $LANG['common'][22],
'itemtype', array('with_comment' => 1)),
   new PluginReportsColumn('serial', 'N° de série'),
   new PluginReportsColumn('otherserial', 'N° d’inventaire'),
   new PluginReportsColumnTypeLink('items_id', 'Nom',
'itemtype', array('with_comment' => 1)),

new PluginReportsColumn('serial', 'N° de série'),



   new PluginReportsColumn('comment', $LANG['common'][25]),
   ));
*/

$report->setColumns(array(new PluginReportsColumnType('itemtype', 'Composant'),
new PluginReportsColumnTypeType('types_id', $LANG['common'][17],'itemtype', array('with_comment' => 1)),
   new PluginReportsColumnModelType('models_id', $LANG['common'][22],'itemtype', array('with_comment' => 1)),
   new PluginReportsColumn('serial', 'N° de série'),
   new PluginReportsColumn('otherserial', 'N° d’inventaire'),
   new PluginReportsColumnTypeLink('items_id', 'Nom','itemtype', array('with_comment' => 1)),
new PluginReportsColumn('comment', $LANG['common'][25]),
   new PluginReportsColumn('states_id', 'etat'),

   )
   );

//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).")";


}

//echo $query ;
}

$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'));

//$states_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'State'));
$states_id = 'states_id';
//echo $models_id ;
//echo $states_id ;

$fields = array('name' => 'name',
'serial' => 'serial',
'otherserial' => 'otherserial',
   'comment' => 'comment',
$models_id => 'models_id',

   'states_id' => 'states_id',

$types_id => 'types_id');

$query_where = "SELECT '$itemtype' AS itemtype,
`$table`.`id` AS items_id,
`$table`.`locations_id`";

foreach ($fields as $field => $alias) {

   if ($obj->isField($field)) {
$query_where .= ", `$table`.`$field` AS $alias";

   //echo $query_where ;


} else {
$query_where .= ", '' AS $alias";
}
}

// LEFT JOIN `glpi_states` ON `glpi_states` .`id` = `$table`.`states_id`";
//$query_where .= " FROM `$table` LEFT JOIN `glpi_states` ON `glpi_states` .`id` = `$table`.`states_id`

//$query_where .= " FROM `$table` LEFT JOIN `glpi_states` ON `glpi_states` .`id` = `$table`.`states_id`";

$query_where .= " FROM `$table` ";

if ($obj->isField('states_id')) {
$query_where .= "LEFT JOIN `glpi_states` ON `glpi_states` .`id` = `$table`.`states_id`";

}

if ($obj->isEntityAssign()) {
$query_where .= getEntitiesRestrictRequest('WHERE', "$table");

} else {
$query_where .= 'WHERE 1';
}

if ($obj->maybeTemplate()) {
$query_where .= " AND `is_template`='0'";
}

if ($obj->maybeDeleted()) {
$query_where .= " AND `is_deleted`='0'";
}

$query_where .= $loc->getSqlCriteriasRestriction();

return $query_where;

}
?>

bruno.desmet · 2014-04-10 14:07:28

J'ai étudié un peu plus précisément le script.

En fait le soucis semble venir de la méthode employée pour construire la requête.

dans le source on a

....

foreach($CFG_GLPI["infocom_types"] as $itemtype) {
$obj = new $itemtype;
if ($obj->isField('locations_id')) {
$query.= "UNION (".getSqlSubRequest($itemtype,$loc,$obj).")";
}
}

...

qui fait appel à la section "infocom_types" :

$CFG_GLPI["infocom_types"] = array('Cartridge', 'CartridgeItem', 'Computer',
'Consumable', 'ConsumableItem', 'Monitor',
'NetworkEquipment', 'Peripheral', 'Phone',
'Printer', 'Software', 'SoftwareLicense');

puis on concatène avec le résultat de la fonction getSqlSubRequest qui se base sur ces valeurs.

Or je cherche à récupérer la donnée de la colonne name de la table glpi_states.

J'ai bien compris qu'il fallait utiliser une jointure comme indiqué plus haut

if ($obj->isField('states_id')) {
$query_where .= "LEFT JOIN `glpi_states` ON `glpi_states` .`id` = `$table`.`states_id`";

}
, mais je ne vois pas comment intégrer cela dans la fonction

getSqlSubRequest

function getSqlSubRequest($itemtype,$loc,$obj) {

$table = getTableForItemType($itemtype);

echo $table .' : <br>';

$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,
`$table`.`id` AS items_id,
`$table`.`locations_id`";

// echo $query_where .' : <br>';

foreach ($fields as $field => $alias) {
if ($obj->isField($field)) {
$query_where .= ", `$table`.`$field` AS $alias";
} else {
$query_where .= ", '' AS $alias";
}
}
$query_where .= " FROM `$table` ";
if ($obj->isEntityAssign()) {
$query_where .= getEntitiesRestrictRequest('WHERE', "$table");
} else {
$query_where .= 'WHERE 1';
}
if ($obj->maybeTemplate()) {
$query_where .= " AND `is_template`='0'";
}
if ($obj->maybeDeleted()) {
$query_where .= " AND `is_deleted`='0'";
}
$query_where .= $loc->getSqlCriteriasRestriction();

return $query_where;
}

et remonter cela dans la partie de construction du rapport

$report->setColumns(array(new PluginReportsColumnType('itemtype', $LANG['common'][17]),
new PluginReportsColumnTypeLink('items_id', $LANG['common'][1], 'itemtype', array('with_comment'=>1)),
new PluginReportsColumn('serial',$LANG['common'][19]),
new PluginReportsColumn('otherserial', $LANG['common'][20]),
new PluginReportsColumnModelType('models_id',$LANG['common'][22],'itemtype',array('with_comment'=>1)),
new PluginReportsColumnTypeType('types_id',$LANG['common'][17],'itemtype',array('with_comment'=>1)),
));

si vous aviez un début de piste

Forum GLPI-Project

Announcement

#1 2014-04-01 17:38:03

modification du rapport listequipmentbylocation

#2 2014-04-04 11:18:56

Re: modification du rapport listequipmentbylocation

#3 2014-04-04 13:37:02

Re: modification du rapport listequipmentbylocation

#4 2014-04-04 14:09:19

Re: modification du rapport listequipmentbylocation

#5 2014-04-04 15:07:33

Re: modification du rapport listequipmentbylocation

#6 2014-04-10 14:07:28

Re: modification du rapport listequipmentbylocation

Board footer