You are not logged in.
- Topics: Active | Unanswered
Announcement
Download last stable version of GLPI - What can you do for GLPI ? : Contribute
#1 2019-06-17 10:19:32
- robtou12
- Member
- Registered: 2018-10-05
- Posts: 16
Problème Plugin Report
Bonjour,
Ça me serait d'une grande utilité si quelqu'un peut résoudre mon problème, Voilà je veux créer un formulaire PHP via le plugin report qui me permettrait d'exporter les matériels par utilisateurs.
Voici ma première version qui marchait bien exportant le nom du matériel, la marque, le modèle, le numéro de série et le TAG :
$USEDBREPLICATE = 1;
$DBCONNECTION_REQUIRED = 0;
include ("../../../../inc/includes.php");
//TRANS: The name of the report = List of equipments by location
$report = new PluginReportsAutoReport("Matériel par utilisateur");
$usr = new PluginReportsUserCriteria($report);
$report->setColumns(array(new PluginReportsColumnType('itemtype', __('Type')),
new PluginReportsColumnTypeLink('items_id', __('Item'),
'itemtype', array('with_comment' => 1)),
new PluginReportsColumn('serial', __('Serial number')),
new PluginReportsColumnModelType('models_id', __('Model'),
'itemtype', array('with_comment' => 1)),
new PluginReportsColumn('manufacturers', __('Manufacturer')),
/*['sorton' => 'glpi_manufacturers.name']),*/
/*new PluginReportsColumnManufacturerType('manufacturers_id', __('Manufacturer'),
'itemtype', array('with_comment' => 1)),*/
new PluginReportsColumn('contact_num', __('Phone'))));
//Display criterias form is needed
$report->displayCriteriasForm();
//If criterias have been validated
if ($report->criteriasValidated()) {
$report->setSubNameAuto();
$query = getSqlSubRequest("Computer",$usr,new Computer());
foreach($CFG_GLPI["infocom_types"] as $itemtype) {
$obj = new $itemtype;
if ($obj->isField('users_id')) {
if ($itemtype != "Computer") {
$query.= " UNION (".getSqlSubRequest($itemtype,$usr,$obj).")";
}
}
}
$report->setGroupBy("entity","itemtype");
$report->setSqlRequest($query);
$report->execute();
}
else {
Html::footer();
}
function getSqlSubRequest($itemtype,$usr,$obj) {
$table = getTableForItemType($itemtype);
$models_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Model'));
$types_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Type'));
$manufacturers = 'manufacturers_id';
$fields = array('name' => 'name',
'serial' => 'serial',
$models_id => 'models_id',
$types_id => 'types_id',
$manufacturers => 'manufacturers',
'contact_num' => 'contact_num');
$query_where = "SELECT '$itemtype' AS itemtype,
`$table`.`id` AS items_id,
`$table`.`users_id`";
foreach ($fields as $field => $alias) {
if ($obj->isField($field)) {
if($itemtype == 'Computer' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($itemtype == 'Peripheral' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($itemtype == 'Phone' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($alias != 'manufacturers'){
$query_where .= ", `$table`.`$field` AS $alias";
}
if($itemtype != 'Phone' && $itemtype != 'Computer' && $itemtype != 'Peripheral'){
if($alias == 'manufacturers'){
$query_where .= ", `$table`.`$field` AS $alias";
}
}
} else {
$query_where .= ", '' AS $alias";
}
}
/*$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_computers`.`manufacturers_id`) ";*/
if($itemtype == 'Computer'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_computers`.`manufacturers_id`) ";
}
if($itemtype == 'Peripheral'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_peripherals`.`manufacturers_id`) ";
}
if($itemtype == 'Phone'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_phones`.`manufacturers_id`) ";
}
if($itemtype != 'Computer' && $itemtype != 'Peripheral' && $itemtype != 'Phone'){
$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 .= $usr->getSqlCriteriasRestriction();
/*echo $query_where;*/
return $query_where;
}
Puis j'ai voulu rajouter le nom d'utilisateur, puis le numéro de téléphone mobile par utilisateur, mais le rapport me retourne "Aucune données trouvées".
Voici l'évolution du rapport que j'avais fait :
$USEDBREPLICATE = 1;
$DBCONNECTION_REQUIRED = 0;
include ("../../../../inc/includes.php");
//TRANS: The name of the report = List of equipments by location
$report = new PluginReportsAutoReport("Matériel par utilisateur");
$usr = new PluginReportsUserCriteria($report);
$report->setColumns(array(new PluginReportsColumnType('itemtype', __('Type')),
new PluginReportsColumnTypeLink('items_id', __('Item'),
'itemtype', array('with_comment' => 1)),
new PluginReportsColumn('serial', __('Serial number')),
new PluginReportsColumnModelType('models_id', __('Model'),
'itemtype', array('with_comment' => 1)),
new PluginReportsColumn('manufacturers', __('Manufacturer')),
/*['sorton' => 'glpi_manufacturers.name']),*/
/*new PluginReportsColumnManufacturerType('manufacturers_id', __('Manufacturer'),
'itemtype', array('with_comment' => 1)),*/
new PluginReportsColumn('contact_num', __('Phone')),
new PluginReportsColumnLink('user', __('User'), 'User')));
//Display criterias form is needed
$report->displayCriteriasForm();
//If criterias have been validated
if ($report->criteriasValidated()) {
$report->setSubNameAuto();
$query = getSqlSubRequest("Computer",$usr,new Computer());
foreach($CFG_GLPI["infocom_types"] as $itemtype) {
$obj = new $itemtype;
if ($obj->isField('users_id')) {
if ($itemtype != "Computer") {
$query.= " UNION (".getSqlSubRequest($itemtype,$usr,$obj).")";
}
}
}
$report->setGroupBy("entity","itemtype");
$report->setSqlRequest($query);
$report->execute();
}
else {
Html::footer();
}
function getSqlSubRequest($itemtype,$usr,$obj) {
$table = getTableForItemType($itemtype);
$models_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Model'));
$types_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Type'));
$manufacturers = 'manufacturers_id';
$fields = array('name' => 'name',
'serial' => 'serial',
$models_id => 'models_id',
$types_id => 'types_id',
$manufacturers => 'manufacturers',
'contact_num' => 'contact_num',
'user' => 'user');
$query_where = "SELECT '$itemtype' AS itemtype,
`$table`.`id` AS items_id,
`$table`.`users_id`";
foreach ($fields as $field => $alias) {
if ($obj->isField($field) || $alias == 'user') {
if($itemtype == 'Computer' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($itemtype == 'Computer' && $alias == 'user'){
$query_where .= ", `glpi_users`.`realname` AS user";
}
if($itemtype == 'Peripheral' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($itemtype == 'Phone' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($alias != 'manufacturers' && $alias != 'user'){
$query_where .= ", `$table`.`$field` AS $alias";
}
if($itemtype != 'Phone' && $itemtype != 'Computer' && $itemtype != 'Peripheral'){
if($alias == 'manufacturers'){
$query_where .= ", `$table`.`$field` AS $alias";
}
}
} else {
$query_where .= ", '' AS $alias";
}
}
/*$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_computers`.`manufacturers_id`) ";*/
if($itemtype == 'Computer'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_computers`.`manufacturers_id`) LEFT JOIN `glpi_users` ON (`glpi_users`.`id`=`glpi_computers`.`users_id`) ";
/* LEFT JOIN `glpi_users` ON (`glpi_users`.`id`=`glpi_computers`.`users_id`)*/
}
if($itemtype == 'Peripheral'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_peripherals`.`manufacturers_id`) ";
}
if($itemtype == 'Phone'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_phones`.`manufacturers_id`) ";
}
if($itemtype != 'Computer' && $itemtype != 'Peripheral' && $itemtype != 'Phone'){
$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 .= $usr->getSqlCriteriasRestriction();
echo $query_where;
return $query_where;
}
Je suis pas un expert en PHP, mais d'avance pour toutes réponses apportées.
Offline
#2 2019-06-18 17:09:00
- yllen
- GLPI-DEV
- From: Sillery (51)
- Registered: 2008-01-14
- Posts: 15,278
Re: Problème Plugin Report
Plusieurs choses ne vont pas.
1.le champ is_deleted.
Lorsque vous ne requetiez que sur la table des computers il n'y avait pas de problème, mais là, un utilisateur peut également être supprimé.
Donc il faudrait mettre " AND $table.ìs_deleted`= 0
2. requetez le `glpi_users`.`realname` AS user uniquement dans la table glpi_computer et pas dans les tables en union
Tous les select doivent avoir le metre nombre de colonnes lors d'un UNION
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
#3 2019-06-19 10:42:50
- robtou12
- Member
- Registered: 2018-10-05
- Posts: 16
Re: Problème Plugin Report
Merci de ta réponse @yllen
Voici donc ma nouvelle version :
$USEDBREPLICATE = 1;
$DBCONNECTION_REQUIRED = 0;
include ("../../../../inc/includes.php");
//TRANS: The name of the report = List of equipments by location
$report = new PluginReportsAutoReport("Matériel par utilisateur");
$usr = new PluginReportsUserCriteria($report);
$report->setColumns(array(new PluginReportsColumnType('itemtype', __('Type')),
new PluginReportsColumnTypeLink('items_id', __('Item'),
'itemtype', array('with_comment' => 1)),
new PluginReportsColumn('serial', __('Serial number')),
new PluginReportsColumnModelType('models_id', __('Model'),
'itemtype', array('with_comment' => 1)),
new PluginReportsColumn('manufacturers', __('Manufacturer')),
/*['sorton' => 'glpi_manufacturers.name']),*/
/*new PluginReportsColumnManufacturerType('manufacturers_id', __('Manufacturer'),
'itemtype', array('with_comment' => 1)),*/
new PluginReportsColumn('contact_num', __('Phone')),
new PluginReportsColumnLink('user', __('User'), 'User')));
//Display criterias form is needed
$report->displayCriteriasForm();
//If criterias have been validated
if ($report->criteriasValidated()) {
$report->setSubNameAuto();
$query = getSqlSubRequest("Computer",$usr,new Computer());
foreach($CFG_GLPI["infocom_types"] as $itemtype) {
$obj = new $itemtype;
if ($obj->isField('users_id')) {
if ($itemtype != "Computer") {
$query.= " UNION (".getSqlSubRequest($itemtype,$usr,$obj).")";
}
}
}
$report->setGroupBy("entity","itemtype");
$report->setSqlRequest($query);
$report->execute();
}
else {
Html::footer();
}
function getSqlSubRequest($itemtype,$usr,$obj) {
$table = getTableForItemType($itemtype);
$models_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Model'));
$types_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Type'));
$manufacturers = 'manufacturers_id';
$fields = array('name' => 'name',
'serial' => 'serial',
$models_id => 'models_id',
$types_id => 'types_id',
$manufacturers => 'manufacturers',
'contact_num' => 'contact_num',
'user' => 'user');
$query_where = "SELECT '$itemtype' AS itemtype,
`$table`.`id` AS items_id,
`$table`.`users_id`";
foreach ($fields as $field => $alias) {
if ($obj->isField($field) || $alias == 'user') {
if($itemtype == 'Computer' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($itemtype == 'Computer' && $alias == 'user'){
$query_where .= ", `glpi_users`.`realname` AS user";
}
if($itemtype == 'Peripheral' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($itemtype == 'Phone' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($alias != 'manufacturers' && $alias != 'user'){
$query_where .= ", `$table`.`$field` AS $alias";
}
if($itemtype != 'Phone' && $itemtype != 'Computer' && $itemtype != 'Peripheral'){
if($alias == 'manufacturers'){
$query_where .= ", `$table`.`$field` AS $alias";
}
}
/*if($itemtype != 'Phone' && $itemtype != 'Computer' && $itemtype != 'Peripheral'){
if($alias == 'user'){
$query_where .= ", `$table`.`$field` AS $alias";
}
}*/
} else {
if ($alias != 'user'){
$query_where .= ", '' AS $alias";
}
}
}
/*$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_computers`.`manufacturers_id`) ";*/
if($itemtype == 'Computer'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_computers`.`manufacturers_id`) LEFT JOIN `glpi_users` ON (`glpi_users`.`id`=`glpi_computers`.`users_id`) ";
/* LEFT JOIN `glpi_users` ON (`glpi_users`.`id`=`glpi_computers`.`users_id`)*/
}
if($itemtype == 'Peripheral'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_peripherals`.`manufacturers_id`) ";
}
if($itemtype == 'Phone'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_phones`.`manufacturers_id`) ";
}
if($itemtype != 'Computer' && $itemtype != 'Peripheral' && $itemtype != 'Phone'){
$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 `$table`.is_deleted='0'";
}
$query_where .= $usr->getSqlCriteriasRestriction();
echo $query_where;
return $query_where;
}
Toujours un petit problème !
Et voici ma requête SQL :
SELECT 'Computer' AS itemtype, `glpi_computers`.`id` AS items_id, `glpi_computers`.`users_id`, `glpi_computers`.`name` AS name, `glpi_computers`.`serial` AS serial, `glpi_computers`.`computermodels_id` AS models_id, `glpi_computers`.`computertypes_id` AS types_id, `glpi_manufacturers`.`name` AS manufacturers, `glpi_computers`.`contact_num` AS contact_num, `glpi_users`.`realname` AS user FROM `glpi_computers` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_computers`.`manufacturers_id`) LEFT JOIN `glpi_users` ON (`glpi_users`.`id`=`glpi_computers`.`users_id`) WHERE ( 1 ) AND `is_template`='0' AND `glpi_computers`.is_deleted='0'AND users_id='1029' SELECT 'Monitor' AS itemtype, `glpi_monitors`.`id` AS items_id, `glpi_monitors`.`users_id`, `glpi_monitors`.`name` AS name, `glpi_monitors`.`serial` AS serial, `glpi_monitors`.`monitormodels_id` AS models_id, `glpi_monitors`.`monitortypes_id` AS types_id, `glpi_monitors`.`manufacturers_id` AS manufacturers, `glpi_monitors`.`contact_num` AS contact_num FROM `glpi_monitors` WHERE ( 1 ) AND `is_template`='0' AND `glpi_monitors`.is_deleted='0'AND users_id='1029' SELECT 'NetworkEquipment' AS itemtype, `glpi_networkequipments`.`id` AS items_id, `glpi_networkequipments`.`users_id`, `glpi_networkequipments`.`name` AS name, `glpi_networkequipments`.`serial` AS serial, `glpi_networkequipments`.`networkequipmentmodels_id` AS models_id, `glpi_networkequipments`.`networkequipmenttypes_id` AS types_id, `glpi_networkequipments`.`manufacturers_id` AS manufacturers, `glpi_networkequipments`.`contact_num` AS contact_num FROM `glpi_networkequipments` WHERE ( 1 ) AND `is_template`='0' AND `glpi_networkequipments`.is_deleted='0'AND users_id='1029' SELECT 'Peripheral' AS itemtype, `glpi_peripherals`.`id` AS items_id, `glpi_peripherals`.`users_id`, `glpi_peripherals`.`name` AS name, `glpi_peripherals`.`serial` AS serial, `glpi_peripherals`.`peripheralmodels_id` AS models_id, `glpi_peripherals`.`peripheraltypes_id` AS types_id, `glpi_manufacturers`.`name` AS manufacturers, `glpi_peripherals`.`contact_num` AS contact_num FROM `glpi_peripherals` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_peripherals`.`manufacturers_id`) WHERE ( 1 ) AND `is_template`='0' AND `glpi_peripherals`.is_deleted='0'AND users_id='1029' SELECT 'Phone' AS itemtype, `glpi_phones`.`id` AS items_id, `glpi_phones`.`users_id`, `glpi_phones`.`name` AS name, `glpi_phones`.`serial` AS serial, `glpi_phones`.`phonemodels_id` AS models_id, `glpi_phones`.`phonetypes_id` AS types_id, `glpi_manufacturers`.`name` AS manufacturers, `glpi_phones`.`contact_num` AS contact_num FROM `glpi_phones` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_phones`.`manufacturers_id`) WHERE ( 1 ) AND `is_template`='0' AND `glpi_phones`.is_deleted='0'AND users_id='1029' SELECT 'Printer' AS itemtype, `glpi_printers`.`id` AS items_id, `glpi_printers`.`users_id`, `glpi_printers`.`name` AS name, `glpi_printers`.`serial` AS serial, `glpi_printers`.`printermodels_id` AS models_id, `glpi_printers`.`printertypes_id` AS types_id, `glpi_printers`.`manufacturers_id` AS manufacturers, `glpi_printers`.`contact_num` AS contact_num FROM `glpi_printers` WHERE ( 1 ) AND `is_template`='0' AND `glpi_printers`.is_deleted='0'AND users_id='1029' SELECT 'Software' AS itemtype, `glpi_softwares`.`id` AS items_id, `glpi_softwares`.`users_id`, `glpi_softwares`.`name` AS name, '' AS serial, '' AS models_id, '' AS types_id, `glpi_softwares`.`manufacturers_id` AS manufacturers, '' AS contact_num FROM `glpi_softwares` WHERE ( 1 ) AND `is_template`='0' AND `glpi_softwares`.is_deleted='0'AND users_id='1029' SELECT 'SoftwareLicense' AS itemtype, `glpi_softwarelicenses`.`id` AS items_id, `glpi_softwarelicenses`.`users_id`, `glpi_softwarelicenses`.`name` AS name, `glpi_softwarelicenses`.`serial` AS serial, '' AS models_id, `glpi_softwarelicenses`.`softwarelicensetypes_id` AS types_id, `glpi_softwarelicenses`.`manufacturers_id` AS manufacturers, `glpi_softwarelicenses`.`contact_num` AS contact_num FROM `glpi_softwarelicenses` WHERE ( 1 ) AND `is_template`='0' AND `glpi_softwarelicenses`.is_deleted='0'AND users_id='1029' SELECT 'Line' AS itemtype, `glpi_lines`.`id` AS items_id, `glpi_lines`.`users_id`, `glpi_lines`.`name` AS name, '' AS serial, '' AS models_id, `glpi_lines`.`linetypes_id` AS types_id, '' AS manufacturers, '' AS contact_num FROM `glpi_lines` WHERE ( 1 ) AND `glpi_lines`.is_deleted='0'AND users_id='1029' SELECT 'Certificate' AS itemtype, `glpi_certificates`.`id` AS items_id, `glpi_certificates`.`users_id`, `glpi_certificates`.`name` AS name, `glpi_certificates`.`serial` AS serial, '' AS models_id, `glpi_certificates`.`certificatetypes_id` AS types_id, `glpi_certificates`.`manufacturers_id` AS manufacturers, `glpi_certificates`.`contact_num` AS contact_num FROM `glpi_certificates` WHERE ( 1 ) AND `is_template`='0' AND `glpi_certificates`.is_deleted='0'AND users_id='1029'
Merci de ta réponse et ton aide précieuse !
Last edited by robtou12 (2019-06-19 14:39:42)
Offline
#4 2019-06-19 16:39:38
- yllen
- GLPI-DEV
- From: Sillery (51)
- Registered: 2008-01-14
- Posts: 15,278
Re: Problème Plugin Report
Tu n'as pas corrigé
select d'un ordi
SELECT 'Computer' AS itemtype, .... `glpi_computers`.`contact_num` AS contact_num, `glpi_users`.`realname` AS user
select d'un autre objet
SELECT 'Monitor' AS itemtype, ... `glpi_monitors`.`contact_num` AS contact_num FROM `glpi_monitors
Il manque toujours glpi_users.realname pour les objets autres que computer
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
#5 2019-06-19 17:25:59
- robtou12
- Member
- Registered: 2018-10-05
- Posts: 16
Re: Problème Plugin Report
Ok top, ça fonctionne, merci à toi @yllen
Je mets pour ceux qui veulent le rapport PHP permettant d'extraire la liste matériel par utilisateurs en tenant compte seulement des paramètres suivants (Avec le plugin Reports ou Rapports en français) :
- Type
- Nom
- Numéro de série
- Modèle
- Fabricant
- Téléphone mobile de l'utilisateur
- Utilisateur concerné
<?php
/*
* @version $Id: listequipmentbylocation.php 246 2013-05-02 13:03:33Z yllen $
-------------------------------------------------------------------------
reports - Additional reports plugin for GLPI
Copyright (C) 2003-2013 by the reports Development Team.
-------------------------------------------------------------------------
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 = List of equipments by location
$report = new PluginReportsAutoReport("Matériel par utilisateur");
$usr = new PluginReportsUserCriteria($report);
$report->setColumns(array(new PluginReportsColumnType('itemtype', __('Type')),
new PluginReportsColumnTypeLink('items_id', __('Item'),
'itemtype', array('with_comment' => 1)),
new PluginReportsColumn('serial', __('Serial number')),
new PluginReportsColumnModelType('models_id', __('Model'),
'itemtype', array('with_comment' => 1)),
new PluginReportsColumn('manufacturers', __('Manufacturer')),
/*['sorton' => 'glpi_manufacturers.name']),*/
/*new PluginReportsColumnManufacturerType('manufacturers_id', __('Manufacturer'),
'itemtype', array('with_comment' => 1)),*/
new PluginReportsColumn('contact_num', __('Phone')),
new PluginReportsColumnLink('user', __('User'), 'User')));
//Display criterias form is needed
$report->displayCriteriasForm();
//If criterias have been validated
if ($report->criteriasValidated()) {
$report->setSubNameAuto();
$query = getSqlSubRequest("Computer",$usr,new Computer());
foreach($CFG_GLPI["infocom_types"] as $itemtype) {
$obj = new $itemtype;
if ($obj->isField('users_id')) {
if ($itemtype != "Computer") {
$query.= " UNION (".getSqlSubRequest($itemtype,$usr,$obj).")";
}
}
}
$report->setGroupBy("entity","itemtype");
$report->setSqlRequest($query);
$report->execute();
}
else {
Html::footer();
}
function getSqlSubRequest($itemtype,$usr,$obj) {
$table = getTableForItemType($itemtype);
$models_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Model'));
$types_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Type'));
$manufacturers = 'manufacturers_id';
$fields = array('name' => 'name',
'serial' => 'serial',
$models_id => 'models_id',
$types_id => 'types_id',
$manufacturers => 'manufacturers',
'contact_num' => 'contact_num',
'user' => 'user');
$query_where = "SELECT '$itemtype' AS itemtype,
`$table`.`id` AS items_id,
`$table`.`users_id`";
foreach ($fields as $field => $alias) {
if ($obj->isField($field) || $alias == 'user') {
if($itemtype == 'Computer' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
/*if($itemtype == 'Computer' && $alias == 'user'){
$query_where .= ", `glpi_users`.`realname` AS user";
}*/
if($itemtype == 'Peripheral' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($itemtype == 'Phone' && $alias == 'manufacturers'){
$query_where .= ", `glpi_manufacturers`.`name` AS manufacturers";
}
if($alias != 'manufacturers' && $alias != 'user' && $alias != 'contact_num'){
$query_where .= ", `$table`.`$field` AS $alias";
}
if($itemtype != 'Phone' && $itemtype != 'Computer' && $itemtype != 'Peripheral'){
if($alias == 'manufacturers'){
$query_where .= ", `$table`.`$field` AS $alias";
}
}
/*if($itemtype != 'Computer'){*/
if($alias == 'user'){
$query_where .= ", `$table`.`users_id` AS $alias";
}
if($itemtype == 'Phone'){
if($alias == 'contact_num'){
$query_where .= ", `glpi_users`.`mobile` AS $alias";
}
}
if($itemtype != 'Phone'){
if($alias == 'contact_num'){
$query_where .= ", `$table`.`$field` AS $alias";
}
}
} else {
if ($alias != 'user'){
$query_where .= ", '' AS $alias";
}
}
}
/*$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_computers`.`manufacturers_id`) ";*/
if($itemtype == 'Computer'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_computers`.`manufacturers_id`) ";
/* LEFT JOIN `glpi_users` ON (`glpi_users`.`id`=`glpi_computers`.`users_id`)*/
}
if($itemtype == 'Peripheral'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_peripherals`.`manufacturers_id`) ";
}
if($itemtype == 'Phone'){
$query_where .= " FROM `$table` LEFT JOIN `glpi_manufacturers` ON (`glpi_manufacturers`.`id`=`glpi_phones`.`manufacturers_id`) LEFT JOIN `glpi_users` ON (`glpi_users`.`id`=`glpi_phones`.`users_id`) ";
}
if($itemtype != 'Computer' && $itemtype != 'Peripheral' && $itemtype != 'Phone'){
$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 `$table`.is_deleted='0'";
}
$query_where .= $usr->getSqlCriteriasRestriction();
$echo $query_where;
return $query_where;
}
?>Prenez pas en compte les différentes fautes de codage, le principal, c'est que ça marche !
Last edited by robtou12 (2019-06-19 17:30:21)
Offline