Forum GLPI-Project

JorgeLopez

Member

Registered: 2011-03-24

Posts: 44

Plugin Reports Bug?

I'm testing plugin Reports for GLPI (1.5.0 version, under development I think) on GLPI 0.80 RC2 in an ubuntu linux 10.10 x64.

I've cloned the example report "ticketsbyentity" as "tickectbygroup", just for learning.

Well, I've needed to add into dropdowncriteria.class.php file, into the constructor, this sentence:

$this->setSqlField($table.".".$name);

to get the function getSqlCriteriasRestriccion() returns something like "AND glpi_groups.id = 'n'" and not something like "AND id = 'n'", to prevent a mysql error about ambiguousness. It possible other reports are affected by this issue.

Error in sqlerror.log:

...
*** MySQL query error :
***
SQL: SELECT `glpi_groups`.`name` AS name,
                    (SELECT COUNT(*)
              FROM `glpi_groups_users`
              WHERE `glpi_groups_users`.`groups_id`=`glpi_groups`.`id` ) as nbusers,
                    COUNT(`glpi_groups_tickets`.`tickets_id`) AS number
             FROM `glpi_groups`
             INNER JOIN `glpi_groups_tickets` ON (`glpi_groups_tickets`.`groups_id`=`glpi_groups`.`id`) WHERE  (  1 ) AND id=\'10\' GROUP BY `glpi_groups`.`id` ORDER BY `glpi_groups`.`name` ASC
Error: Column 'id' in where clause is ambiguous
Backtrace :
/var/www/glpi-080/plugins/reports/inc/autoreport.class.php :247        DBmysql->query()
/var/www/glpi-080/plugins/reports/report/ticketsbygroup/ticketsbygroup.php :78        PluginReportsAutoReport->execute()
/var/www/glpi-080/plugins/reports/report/ticketsbygroup/ticketsbygroup.php
...

Also, in order to get specific data for the choosen group, I've had to change the place of the sentence:

   $prof->getSqlCriteriasRestriction();

removing it from $subcpt and adding it to $query as shown below.

Here it is the change in  dropdowncriteria.class.php file :

.....

   function __construct($report, $name, $table='', $label = '') {
      parent :: __construct($report, $name );

      if (empty($table)) {
         $table = getTableNameForForeignKeyField($name);
      }

      $this->table = $table;

//Starts change
      $this->setSqlField($table.".".$name);
//Ends change

      $this->addCriteriaLabel($name, $label);
   }

.....

Also, here it is the report "ticketsbygroup.php", reflecting the way I arranged query composition, in the case it can be interesting:

<?php

/* Comments Omitted
*/

//      Options for GLPI 0.71 and newer : need slave db to access the report
$USEDBREPLICATE=1;
$DBCONNECTION_REQUIRED=1;

// Initialization of the variables
define('GLPI_ROOT',  '../../../..');
include (GLPI_ROOT . "/inc/includes.php");

$report = new PluginReportsAutoReport($LANG['plugin_reports']['ticketsbygroup'][1]);

//Report's search criterias
$prof = new PluginReportsDropdownCriteria($report,'id','glpi_groups', $LANG['common'][35]);

//Display criterias form is needed
$report->displayCriteriasForm();

//If criterias have been validated
if ($report->criteriasValidated()) {
   $report->setSubNameAuto();

   //Names of the columns to be displayed
   $cols = array (new PluginReportsColumn('name', $LANG['common'][35],
                                           array('sorton' => '`glpi_groups`.`name`')),
                  new PluginReportsColumnInteger('nbusers', $LANG['plugin_reports']['ticketsbygroup'][5],
                                                 array('withtotal' => true,
                                                       'sorton'    => 'nbusers')),
                  new PluginReportsColumnInteger('number', $LANG['plugin_reports']['ticketsbygroup'][2],
                                                 array('withtotal' => true,
                                                       'sorton'    => 'number')));

   $report->setColumns($cols);

   $subcpt = "SELECT COUNT(*)
              FROM `glpi_groups_users`
              WHERE `glpi_groups_users`.`groups_id`=`glpi_groups`.`id` ";

// I've moved this sentence...
//             $prof->getSqlCriteriasRestriction().

   $query = "SELECT `glpi_groups`.`name` AS name,
                    ($subcpt) as nbusers,
                    COUNT(`glpi_groups_tickets`.`tickets_id`) AS number
             FROM `glpi_groups`
             INNER JOIN `glpi_groups_tickets` ON (`glpi_groups_tickets`.`groups_id`=`glpi_groups`.`id`)".
             getEntitiesRestrictRequest(" WHERE ", "glpi_entities") .

//...to this place to get specific data for group
             $prof->getSqlCriteriasRestriction().

            "GROUP BY `glpi_groups`.`id`".
            $report->getOrderBy('name');

   $report->setSqlRequest($query);
   $report->execute(array('withtotal'=>true));

} else {
   commonFooter();
}

?>

I hope this can be useful.

Last edited by JorgeLopez (2011-05-05 10:43:12)

---

-----------
Jorge López Díaz
GLPI 0.80.4 producción, GLPI 0.83rc2 test
Ubuntu server 10.04, 10.10 y 11.04

yllen

GLPI-DEV

From: Sillery (51)

Registered: 2008-01-14

Posts: 15,278

Re: Plugin Reports Bug?

I have no probleme because i indicate in setSqlField('`mytable`.`myfield`')

---

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)