Reports plugin with GLPI 10

g.schulz · 2023-11-17 16:15:40

Hello,

I come from GLPI 9.5.9 and passed to 10.0.10 version which changed ... a little bit ! ;-)

Everything is working fine except a query which was made in addition to a personnlized report (with reports plugin).
The query reports all the tasks between 2 dates with the sum of the durations.
When we are using the report, the tasks are displayed but the sum is not working anymore. Probably there was a little change on how to call a SQL query directly as we were doing :

   $queryTotal = "SELECT SEC_TO_TIME(SUM(`glpi_tickettasks`.`actiontime`)) as duree
		FROM `glpi_tickettasks` ".
		$report->addSqlCriteriasRestriction('WHERE').
		"AND `glpi_tickettasks`.`users_id` = ".Session::getLoginUserID();
	
   $resTot = $DB->request($queryTotal);
   
   echo "<br /><center><b>TOTAL ".($resTot->next()["duree"])."<b /><center /><br />";

I don't have any error, $resTot is just empty.

I tried like that but no changes :

   $queryTotal = "SELECT SEC_TO_TIME(SUM(`glpi_tickettasks`.`actiontime`)) as duree
		FROM `glpi_tickettasks` ".
		$report->addSqlCriteriasRestriction('WHERE').
		" AND `glpi_tickettasks`.`users_id` = ".Session::getLoginUserID();
	
   $resTot = $DB->request($queryTotal);
   
   if ($row = $resTot->next()) {
    $Total = $row['duree'];
}
   
   echo "<br /><center><b>TOTAL : ".$Total."<b /><center /><br />";

I found this post : https://forum.glpi-project.org/viewtopic.php?id=283925
which seems to be close to my topic but didn't manage to make it work...

Does someone have another idea please ?

LaDenrée · 2023-11-17 16:58:02

i make it this way using reports 1.16

$date = new PluginReportsDateIntervalCriteria($report, 'T.`date_creation`',"Taches crees(inclus)");

$report->displayCriteriasForm();
if ($report->criteriasValidated()) {
//Intitulés des colonnes :  nom de la colonne dans la requete sql et nom affiché sur le rapport à l'ecran : mettre à jour la variable dans les fichiers de traduction;

$cols = array(
        	new PluginReportsColumn('UREALNAME',"Utilisateur")
		,new PluginReportsColumn('TDUREE',"Duree totale")
		
                                                );
   $report->setColumns($cols);



$query = "SELECT U.`realname` AS UREALNAME, 
SEC_TO_TIME( sum( T.actiontime ) ) AS TDUREE 
FROM `glpi_tickettasks` AS T 
JOIN `glpi_users` AS U ON T.`users_id` = U.`id` ";
$query .=" WHERE 1=1 ". $date->getSqlCriteriasRestriction()." group by UREALNAME";

$report->setSqlRequest($query);

I allways add "where 1=1" in my whereclause to make sure that i dont get " WHERE AND" (AND might be added by sqlcriteriarestriction

my sql look like this (in debug mode you can see all the queries.)

SELECT U.`realname` AS UREALNAME, 
SEC_TO_TIME( sum( T.actiontime ) ) AS TDUREE 
FROM `glpi_tickettasks` AS T 
JOIN `glpi_users` AS U ON T.`users_id` = U.`id`  
WHERE 1=1 AND (T.`date_creation`>= '2023-11-17 00:00:00' AND T.`date_creation`<='2023-11-18 23:59:59' ) group by UREALNAME

g.schulz · 2023-11-17 18:11:55

Thanks LaDenrée for your fast answer.

My issue is not the execution of the report itself which works fine but the sum of times.
I can't use the same way for both queries if my memery is good (unless it changed), that's why i wrote the query on another way.

I have this code for the main query :

   $report->setSqlRequest($query);
   $report->execute();

and it gives me the list of tasks.

I already checked for the result of the query with displying the query ($queryTotal) and i have a working SQL query :
SELECT SEC_TO_TIME(SUM(`glpi_tickettasks`.`actiontime`)) as duree FROM `glpi_tickettasks` WHERE (`glpi_tickettasks`.`Date`>= '2023-11-17 00:00:00' AND `glpi_tickettasks`.`Date`<='2023-11-17 23:59:59' )AND `glpi_tickettasks`.`users_id` = 833

I see that there is a space at the wrong place on the last "AND" but this is not the issue : the query works.

Last edited by g.schulz (2023-11-17 18:16:37)

g.schulz · 2023-11-21 12:06:12

Hello,

I tried to look at it further and with debug mode, i have this warning :

Trying to access array offset on value of type null in line XX

and this XX line corresponds to :

   echo "<br /><center><b>TOTAL ".($resTot->next()["duree"])."<b /><center /><br />";

So i guess that $resTot->next()["duree"] does not work anymore.

I tryed to break down the code like that :

   $resTot = $DB->request($queryTotal);
   $row = $resTot->next();
   echo $row['duree'];

But still the same warning on "$row['duree']"

Does anyone have an idea please ?

cconard96 · 2023-11-21 15:38:14

The iterator object returned by "$DB->request" actually follows the correct iterator pattern since GLPI 10. Previously, it didn't.

The correct way to get the current result is "$resTot->current()". Calling "next()" advances the internal pointer to the next location only and doesn't return anything. If you call "current()" again, the next item is returned or null if there are no more results.
You can loop through all results easily with a foreach loop like:
foreach ($resTot as $row) {
echo $row['duree'];
}
You can also check how many results are in the iterator with "count($resTot)" or "$resTot->count()".

g.schulz · 2023-11-21 16:09:33

Hi cconard96,

So simple and so effective, thank you ..

I tried to add the [Solved] Tag on the title and did not manage to do it ..

Last edited by g.schulz (2023-11-21 16:11:15)

Forum GLPI-Project

Announcement

#1 2023-11-17 16:15:40

Reports plugin with GLPI 10

#2 2023-11-17 16:58:02

Re: Reports plugin with GLPI 10

#3 2023-11-17 18:11:55

Re: Reports plugin with GLPI 10

#4 2023-11-21 12:06:12

Re: Reports plugin with GLPI 10

#5 2023-11-21 15:38:14

Re: Reports plugin with GLPI 10

#6 2023-11-21 16:09:33

Re: Reports plugin with GLPI 10

Board footer