Forum GLPI-Project

kkgigiioo

Member

Registered: 2019-02-14

Posts: 3

Existing report customization

Hi all!

I hope you can help me how to edit one of the existing report from the reports plugin. This report is "list of equipment by location" and I need to change it so that I could already display the "Location" caption in the header, but I can't assign a value.
Include the existing code, please help me!

$USEDBREPLICATE                = 1;
$DBCONNECTION_REQUIRED = 0;

include("../../../../inc/includes.php");

//TRANS: The name of the report = List of equipments by location
$report = new PluginReportsAutoReport(__('listequipmentbylocation_report_title', 'reports'));
$loc      = new PluginReportsLocationCriteria($report);

$report->setColumns([new PluginReportsColumnTypeLink('items_id', __('Item'), 'itemtype', ['with_comment' => 1]),
                                 new PluginReportsColumn('statename', __('Status')),
                                 new PluginReportsColumn('serial', __('Serial number')),
                                 new PluginReportsColumn('otherserial', __('Inventory number')),
                                 new PluginReportsColumnModelType('models_id', __('Model'), "itemtype, ['with_comment'=>1]),
                                 new PluginReportsColumn('contact', __('Alternate username')),
                                 new PluginReportsColumn('location', __('Location'))
                              ]);

//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') && ($itemtype != "Computer")){
               $query .= "UNION (".getSqlSubRequest($itemtype, $loc, $obj).")";
          }
     }
     
     $report->setGroupBy("entity", "itemtype");
     $report->setSqlRequest($query);
     $report->execute();
}
else {
     Html::footer();
}

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

     $dbu = new DbUtils();

     $table         = $dbu->getTableForItemType($itemtype);
     $models_id = $dbu->getForeignKeyFieldForTable($dbu->getTableForItemType($itemtype.'Model'));
     $type_id     = $dbu->getForeignKeyFieldForTable($dbu->getTableForItemType($itemtype.'Type'));
     $fields        = ['name'           => 'name',
                          'serial'            => 'serial',
                          'otherserial'    => 'otherserial'
                          'states_id'      => 'states_id'
                          'contact'        => 'contact'
                          'location'        => 'location'
                          $models_id    => 'models_id'
                          $types_id      => 'types_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 == 'states_id'){
                    $query_where .= ", `glpi_states`.`name` AS statename";
                    $join = "LEFT JOIN `glpi_states` ON `glpi_states`.`id` = `$table`.`states_id` ";
               }
               else {
                    $query_where .= ", `$table`.`$field` AS $alias";
               }
          }
          else {
               $query_where .= ", '' AS $alias";
          }
     }

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

     if($obj->isEntityAssign()){
          $query_where .= $bsu->getEntitesRestrictRequest('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;
}

kkgigiioo

Member

Registered: 2019-02-14

Posts: 3

Re: Existing report customization

Thanks for nothing to the forum ... I've solved my problem.

All I had to do was fix the link problems and enter the nessessary lines. And now it's working

$report changes:

$report->setColumns([new PluginReportsColumnTypeLink('items_id', __('Item'), 'itemtype', ['with_comment' => 1]),
                                 new PluginReportsColumn('statename', __('Status')),
                                 new PluginReportsColumn('serial', __('Serial number')),
                                 new PluginReportsColumn('otherserial', __('Inventory number')),
                                 new PluginReportsColumnModelType('models_id', __('Model'), "itemtype, ['with_comment'=>1]),
                                 new PluginReportsColumn('contact', __('Alternate username')),
                                 new PluginReportsColumn('compname', __('Location'))
                              ]);

array changes:

$fields = ['name'           => 'name',
              'serial'            => 'serial',
              'otherserial'    => 'otherserial'
              'states_id'      => 'states_id'
              'contact'        => 'contact'
              'locations_id'  => 'locations_id'
              $models_id    => 'models_id'
              $types_id      => 'types_id'
          ];

if-else changes:

foreach($fields as $field => $alias){
          if($obj->isField($field)){
               if($field == 'states_id'){
                    $query_where .= ", `glpi_states`.`name` AS statename";
                    $join = "LEFT JOIN `glpi_states` ON `glpi_states`.`id` = `$table`.`states_id` ";
               }
               else if($field == "locations_id"){
                    $query_where .= ", `glpi_locations`.`completename` AS compname";
                    $join .= "LEFT JOIN `glpi_locations` ON `glpi_locations`.`id` = `$table`.`locations_id` ";
               }
               else {
                    $query_where .= ", `$table`.`$field` AS $alias";
               }
          }
          else {
               $query_where .= ", '' AS $alias";
          }
     }