You are not logged in.
Pages: 1
Hello,
I'm trying to make a report for a selected location (like list equip by location) with the following structure:
1. Crit. No. (autonumbered)
2. Computer Model (Computer Serial Number)
3. Computer specifications (CPU, total SODIMM, HDD size)
4. Installed software
Can anyone help me constructing this report?
Best Regards,
Chris
Offline
Hello,
So far i've managed to display some of needed columns.
I can't display the total installed memory, total hdd size and installed software.
Can anybody help me?
Thanks,
Chris
<?php
$USEDBREPLICATE = 1;
$DBCONNECTION_REQUIRED = 0;
include ("../../../../inc/includes.php");
//TRANS: The name of the report = List of equipments by location
$report = new PluginReportsAutoReport(__('sentinel_report_title', 'reports'));
$loc = new PluginReportsLocationCriteria($report);
$report->setColumns([new PluginReportsColumnModelType('models_id', __('Model'), 'itemtype',
['with_comment' => 1]),
new PluginReportsColumn('serial', __('Serial number')),
new PluginReportsColumn('osname', __('Operating System')),
new PluginReportsColumn('proc', __('Processor'))]);
new PluginReportsColumn('mem', __('Memory'))]);
new PluginReportsColumn('hdd', __('HDD Size'))]);
new PluginReportsColumn('software', __('Installed software'))]);
//Display criterias form is needed
$report->displayCriteriasForm();
//If criterias have been validated
if ($report->criteriasValidated()) {
$report->setSubNameAuto();
$query = getSqlSubRequest("Computer",$loc,new Computer());
$report->setGroupBy("entity","itemtype");
$report->setSqlRequest($query);
$report->execute();
}
else {
Html::footer();
}
function getSqlSubRequest($itemtype,$loc,$obj) {
$dbu = new DbUtils();
$table = getTableForItemType($itemtype);
$models_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Model'));
$types_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Type'));
$fields = [$models_id => 'models_id',
'serial' => 'serial',
'id' => 'id'];
$query_where = "SELECT '$itemtype' AS itemtype,
`$table`.`id` AS items_id,
`$table`.`locations_id`";
$join = "";
foreach ($fields as $field => $alias) {
if ($obj->isField($field)) {
if ($field == 'id' && $itemtype == 'Computer') {
$query_where .= ", `glpi_operatingsystems`.`name` AS osname, `glpi_deviceprocessors`.`designation` AS proc, `glpi_items_deviceharddrives`.`capacity` AS hdd, `glpi_items_devicememories`.`size` AS mem";
$join .= "LEFT JOIN `glpi_items_operatingsystems` ON `glpi_items_operatingsystems`.`items_id` = `$table`.`id` AND `glpi_items_operatingsystems`.`itemtype` = 'Computer'
LEFT JOIN `glpi_operatingsystems` ON `glpi_operatingsystems`.`id` = `glpi_items_operatingsystems`.`operatingsystems_id`
LEFT JOIN `glpi_items_deviceprocessors` ON `glpi_items_deviceprocessors`.`items_id` = `$table`.`id` AND `glpi_items_deviceprocessors`.`itemtype` = 'Computer'
LEFT JOIN `glpi_deviceprocessors` ON `glpi_items_deviceprocessors`.`deviceprocessors_id` = `glpi_deviceprocessors`.`id`
LEFT JOIN glpi_items_deviceharddrives ON (glpi_computers.id = glpi_items_deviceharddrives.items_id AND glpi_items_deviceharddrives.itemtype = 'Computer')
LEFT JOIN glpi_deviceharddrives ON (glpi_items_deviceharddrives.deviceharddrives_id = glpi_deviceharddrives.id)
LEFT JOIN glpi_items_devicememories ON (glpi_computers.id = glpi_items_devicememories.items_id AND glpi_items_devicememories.itemtype = 'Computer')
LEFT JOIN glpi_devicememories ON (glpi_items_devicememories.devicememories_id = glpi_devicememories.id) ";
} else {
$query_where .= ", `$table`.`$field` AS $alias";
}
} else {
$query_where .= ", '' AS $alias";
}
}
$query_where .= " FROM `$table` $join ";
if ($obj->isEntityAssign()) {
$query_where .= $dbu->getEntitiesRestrictRequest('WHERE', "$table");
} else {
$query_where .= ' WHERE 1';
}
if ($obj->maybeTemplate()) {
$query_where .= " AND `is_template`='0'";
}
if ($obj->maybeDeleted()) {
if ($itemtype == 'Computer') {
$query_where .= " AND `$table`.`is_deleted`='0'";
} else {
$query_where .= " AND `is_deleted`='0'";
}
}
if ($loc->getSqlCriteriasRestriction() != "") {
$query_where .= " " . substr_replace($loc->getSqlCriteriasRestriction(), "$table.", 4, 0);
} else {
$query_where .= "";
}
return $query_where;
}Last edited by casper_1707 (2018-05-22 13:03:39)
Offline
Finally, i've managed to do the wanted report.
Below the code.
<?php
$USEDBREPLICATE = 1;
$DBCONNECTION_REQUIRED = 0;
include ("../../../../inc/includes.php");
//TRANS: The name of the report = List of equipments by location
$report = new PluginReportsAutoReport(__('sentinel_report_title', 'reports'));
$loc = new PluginReportsLocationCriteria($report);
$report->setColumns([new PluginReportsColumnModelType('models_id', __('Model'), 'itemtype',
['with_comment' => 1]),
new PluginReportsColumn('serial', __('Serial number')),
new PluginReportsColumn('osname', __('Operating System')),
new PluginReportsColumn('proc', __('Procesor')),
new PluginReportsColumn('mem', __('Memorie (GB)')),
new PluginReportsColumn('hdd', __('Stocare')),
new PluginReportsColumn('software', __('Software instalat'))]);
//Display criterias form is needed
$report->displayCriteriasForm();
//If criterias have been validated
if ($report->criteriasValidated()) {
$report->setSubNameAuto();
$query = getSqlSubRequest("Computer",$loc,new Computer());
$report->setGroupBy("entity","itemtype");
$report->setSqlRequest($query);
$report->execute();
}
else {
Html::footer();
}
function getSqlSubRequest($itemtype,$loc,$obj) {
$dbu = new DbUtils();
$table = getTableForItemType($itemtype);
$models_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Model'));
$types_id = getForeignKeyFieldForTable(getTableForItemType($itemtype.'Type'));
$fields = [$models_id => 'models_id',
'serial' => 'serial',
'id' => 'id'];
$query_where = "SELECT '$itemtype' AS itemtype,
`$table`.`id` AS items_id,
`$table`.`locations_id`";
$join = "";
foreach ($fields as $field => $alias) {
if ($obj->isField($field)) {
if ($field == 'id' && $itemtype == 'Computer') {
$query_where .= ", `glpi_operatingsystems`.`name` AS osname,
`glpi_deviceprocessors`.`designation` AS proc,
(SELECT
GROUP_CONCAT(
CONCAT(`glpi_deviceharddrives`.`designation`, '<br />')
SEPARATOR ''
)
FROM `glpi_deviceharddrives`
LEFT JOIN `glpi_items_deviceharddrives` ON `glpi_items_deviceharddrives`.`deviceharddrives_id` = `glpi_deviceharddrives`.`id`
WHERE `glpi_items_deviceharddrives`.`items_id` = `glpi_computers`.`id`
) AS hdd,
(SELECT
ROUND((SUM(`glpi_items_devicememories`.`size`)/1024),0)
FROM `glpi_items_devicememories`
WHERE `glpi_items_devicememories`.`items_id` = `glpi_computers`.`id`
) AS mem,
(SELECT
GROUP_CONCAT(
CONCAT(`glpi_softwares`.`name`, '<br />')
SEPARATOR ''
)
FROM `glpi_softwares`
LEFT JOIN `glpi_softwareversions` ON ( `glpi_softwares`.`id` = `glpi_softwareversions`.`softwares_id` )
LEFT JOIN `glpi_computers_softwareversions` ON ( `glpi_computers_softwareversions`.`softwareversions_id` = `glpi_softwareversions`.`id` )
WHERE `glpi_computers_softwareversions`.`computers_id` = `glpi_computers`.`id`
) AS software ";
$join .= "LEFT JOIN `glpi_items_operatingsystems` ON `glpi_items_operatingsystems`.`items_id` = `$table`.`id` AND `glpi_items_operatingsystems`.`itemtype` = 'Computer'
LEFT JOIN `glpi_operatingsystems` ON `glpi_operatingsystems`.`id` = `glpi_items_operatingsystems`.`operatingsystems_id`
LEFT JOIN `glpi_items_deviceprocessors` ON `glpi_items_deviceprocessors`.`items_id` = `$table`.`id` AND `glpi_items_deviceprocessors`.`itemtype` = 'Computer'
LEFT JOIN `glpi_deviceprocessors` ON `glpi_items_deviceprocessors`.`deviceprocessors_id` = `glpi_deviceprocessors`.`id` ";
} else {
$query_where .= ", `$table`.`$field` AS $alias";
}
} else {
$query_where .= ", '' AS $alias";
}
}
$query_where .= " FROM `$table` $join ";
if ($obj->isEntityAssign()) {
$query_where .= $dbu->getEntitiesRestrictRequest('WHERE', "$table");
} else {
$query_where .= ' WHERE 1';
}
//if ($obj->maybeTemplate()) {
// $query_where .= " AND `is_template`='0'";
//}
if ($obj->maybeDeleted()) {
if ($itemtype == 'Computer') {
$query_where .= " AND `$table`.`is_deleted`='0'";
} else {
$query_where .= " AND `is_deleted`='0'";
}
}
if ($loc->getSqlCriteriasRestriction() != "") {
$query_where .= " " . substr_replace($loc->getSqlCriteriasRestriction(), "$table.", 4, 0);
} else {
$query_where .= "";
}
$query_where .= " GROUP BY `glpi_computers`.`id`";
return $query_where;
}Offline
Pages: 1