Report Query

jdag1992 · 2014-10-13 18:42:05

Hi
I'm trying to generate a consult by id on Reports Plugins...
I have this code:
<?php
$USEDBREPLICATE= 1;
$DBCONNECION_REQUIRED= 0;
include ("../../../../inc/includes.php");
//TRANS: PRUEBA
include_once 'jhonny.en_GB.php';
$report = new PluginReportsAutoReport($LANG['plugin_reports']['jhonny']);
new PluginReportsTextCriteria($report, 'id', __('ID'));
new PluginReportsTextCriteria($report, 'name', __('Nombre'));
new PluginReportsTicketStatusCriteria($report, 'status', 'Status', array('','1','2','3','4','5','6'));
new PluginReportsTicketTypeCriteria($report, 'type');
new PluginReportsTicketCategoryCriteria($report);
$report->displayCriteriasForm();
// Declare columns
if($report->criteriasValidated()) {
$report->setSubNameAuto();

$report->setColumns(array(
new PluginReportsColumn('name', __('Nombre')),
new PluginReportsColumn('id', __('ID')),
new PluginReportsColumn('priority', __('Prioridad')),
new PluginReportsColumn('content', __('Contenido')),
));
$query = "SELECT `name`,`id`,`priority`, `content` from `glpi_tickets`".$report->addSqlCriteriasRestriction("WHERE") ;

$report->setSqlRequest($query);
$report->execute();
}
?>
This code work's fine, but the query by date doesnt work!.
Please help! Thanks

LaDenrée · 2014-10-14 00:07:18

Bonsoir,
GLPI version ?
Plugin version ?

I can't see any date criteria in your example ! (PluginReportsdateIntervalCriteria)

jdag1992 · 2014-10-14 02:06:19

Thanks for reply

This is my really code

<?php
$USEDBREPLICATE= 1;
$DBCONNECION_REQUIRED= 0;
include ("../../../../inc/includes.php");
//TRANS: PRUEBA
include_once 'cons.en_GB.php';
$report = new PluginReportsAutoReport($LANG['plugin_reports']['cons']);
//new PluginReportsDateIntervalCriteria($report, "`glpi_tickets`.`date`");
new PluginReportsTextCriteria($report, 'id', __('ID'));
$report->displayCriteriasForm();
// Declare columns
if($report->criteriasValidated()) {
$itemtype = $_POST['itemtype'];
$table = getTableForItemType($itemtype);
$report->setColumns(array(
new PluginReportsColumn('name', __('Nombre')),
new PluginReportsColumn('id', __('ID')),
));
$query = "SELECT `name`,`id` from `glpi_tickets` " .$report->addSqlCriteriasRestriction("WHERE") ;
$report->setSqlRequest($query);
$report->execute();
}
?>

I'm very appreciate your answer

I'm comment the line of DateIntervalCritery because the query by Date doenst work!

GLPI Version 0.84
Plugin Reports Version 1.7.0

Last edited by jdag1992 (2014-10-14 03:22:33)

LaDenrée · 2014-10-14 08:27:23

//new PluginReportsDateIntervalCriteria($report, "`glpi_tickets`.`date`");

I would replace double quotes by single quote
new PluginReportsDateIntervalCriteria($report, '`glpi_tickets`.`date`');

jdag1992 · 2014-10-14 18:09:56

Hi, thanks for response.

Doesn't works with simple quotes

I have this code

<?php

$USEDBREPLICATE= 1;
$DBCONNECION_REQUIRED= 0;

include ("../../../../inc/includes.php");
//TRANS: PRUEBA
include_once 'jhonny.en_GB.php';
$report = new PluginReportsAutoReport($LANG['plugin_reports']['jhonny']);
new PluginReportsTextCriteria($report, 'id', __('ID'));
new PluginReportsTextCriteria($report, 'name', __('Nombre'));
new PluginReportsTicketStatusCriteria($report, 'status', 'Status', array('','1','2','3','4','5','6'));
new PluginReportsTicketTypeCriteria($report, 'type');
new PluginReportsDateIntervalCriteria($report, '`glpi_tickets`.`date`');
new PluginReportsTicketCategoryCriteria($report);

$report->displayCriteriasForm();

// Declare columns
if($report->criteriasValidated()) {
$report->setSubNameAuto();

$report->setColumns(array(
new PluginReportsColumn('name', __('Nombre')),
new PluginReportsColumn('id', __('ID')),
new PluginReportsColumn('priority', __('Prioridad')),
new PluginReportsColumn('content', __('Contenido')),
));

$query = "SELECT `name`,`id`,`priority`, `content` from `glpi_tickets`".$report->addSqlCriteriasRestriction("WHERE") ;

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

?>

The query doesn't give any result

LaDenrée · 2014-10-14 19:06:09

could you run your report with debug mode and post the query ?

jdag1992 · 2014-10-14 19:09:12

Hi, thanks for response.
Doesn't works with simple quotes
I have this code
<?php
$USEDBREPLICATE= 1;
$DBCONNECION_REQUIRED= 0;
include ("../../../../inc/includes.php");
//TRANS: PRUEBA
include_once 'jhonny.en_GB.php';
$report = new PluginReportsAutoReport($LANG['plugin_reports']['jhonny']);
new PluginReportsTextCriteria($report, 'id', __('ID'));
new PluginReportsTextCriteria($report, 'name', __('Nombre'));
new PluginReportsTicketStatusCriteria($report, 'status', 'Status', array('','1','2','3','4','5','6'));
new PluginReportsTicketTypeCriteria($report, 'type');
new PluginReportsDateIntervalCriteria($report, '`glpi_tickets`.`date`');
new PluginReportsTicketCategoryCriteria($report);
$report->displayCriteriasForm();
// Declare columns
if($report->criteriasValidated()) {
$report->setSubNameAuto();

$report->setColumns(array(
new PluginReportsColumn('name', __('Nombre')),
new PluginReportsColumn('id', __('ID')),
new PluginReportsColumn('priority', __('Prioridad')),
new PluginReportsColumn('content', __('Contenido')),
));
$query = "SELECT `name`,`id`,`priority`, `content` from `glpi_tickets`".$report->addSqlCriteriasRestriction("WHERE") ;

$report->setSqlRequest($query);
$report->execute();
}
?>
The query doesn't give any result

jdag1992 · 2014-10-14 19:10:04

Excuse me for that, but...who is debug mode?

LaDenrée · 2014-10-14 19:17:35

on glpi front page : settings>tab: MAIN>use GLPI in mode : Debug

jdag1992 · 2014-10-14 19:32:37

This is what appears in the debug mode in the query

SQL REQUEST : 11 Queries took 0.059s
N° Queries Time Errors
1 SELECT `glpi_plugins`.*
FROM `glpi_plugins`
WHERE `glpi_plugins`.`directory` = 'customfields' 0.002
2 SELECT `glpi_plugins`.*
FROM `glpi_plugins`
WHERE `glpi_plugins`.`directory` = 'customfields' 0.001
3 SELECT *
FROM `glpi_plugins`
WHERE state=1 0.000
4 SELECT `itemtype`, `enabled`
FROM `glpi_plugin_customfields_itemtypes`
WHERE `itemtype` <> 'Version' 0.001
5 SHOW COLUMNS
FROM `glpi_itilcategories` 0.049
6 SELECT *
FROM `glpi_itilcategories`
WHERE 1 AND `glpi_itilcategories`.`id` NOT IN ('0')
ORDER BY `entities_id`, `completename` 0.001
7 SELECT *
FROM `glpi_itilcategories`
WHERE `id` = '0' 0.000
8 SELECT *
FROM `glpi_entities`
WHERE `id` = '1' 0.001
9 SELECT *
FROM `glpi_entities`
WHERE `id` = '6' 0.001
10 SELECT `name`,`id`,`priority`, `content`
FROM `glpi_tickets` 0.002
11 SELECT `name`,`id`,`priority`, `content`
FROM `glpi_tickets` LIMIT 0,20 0.001

KEY => VALUE
id =>
name =>
status=>
type=>all
glpi_tickets_date_1 => 2014-10-01
glpi_tickets_date_2 => 2014-10-14
itilcategories_id => 0
find => 1
_glpi_csrf_token => 11befc919547a830d5e50e8858dc9f2b

yllen · 2014-10-14 20:14:19

you nedd a space before WHERE
.$report->addSqlCriteriasRestriction(" WHERE")

jdag1992 · 2014-10-14 20:20:58

Hi, thanks for your response

Yllen the space not gives any change.

jdag1992 · 2014-10-14 21:34:38

I don't know but i think i have that concat the hour in date...But i don't know what i have todo in this case

LaDenrée · 2014-10-15 00:33:07

this should work.

create a variable with the datecriteria class : $datecrit=new PluginReportsDateIntervalCriteria($report, '`glpi_tickets`.`date`');

then i get start ans end date by myself to add it to the query.
I test that values are not null.

if ($datecrit->getparameter('_glpi_tickets___date__1')<>"") {
$query .=" AND `date`>'" .$datecrit->getparameter('_glpi_tickets___date__1') . " 00:00:00' ";
}
if ($datecrit->getparameter('_glpi_tickets___date__1')<>"") {
$query .=" AND `date`<'" .$datecrit->getparameter('_glpi_tickets___date__2') . " 23:59:59' ";
}

i think there is a bug in this class since the parameter is _glpi_tickets___date__1 and the class looks for _glpi_tickets_date_1 (with single underscore)

$USEDBREPLICATE= 1;
$DBCONNECION_REQUIRED= 0;
include ("../../../../inc/includes.php");
//TRANS: PRUEBA
//include_once 'jhonny.en_GB.php';
$report = new PluginReportsAutoReport('tests');
new PluginReportsTextCriteria($report, 'id', 'ID');
new PluginReportsTextCriteria($report, 'name', 'Nom');
new PluginReportsTicketStatusCriteria($report, 'status', 'Status', array('','1','2','3','4','5','6'));
new PluginReportsTicketTypeCriteria($report, 'type');
$datecrit=new PluginReportsDateIntervalCriteria($report, '`glpi_tickets`.`date`');
new PluginReportsTicketCategoryCriteria($report);
$report->displayCriteriasForm();
// Declare columns
if($report->criteriasValidated()) {
$report->setSubNameAuto();

$report->setColumns(array(
new PluginReportsColumn('name', 'Nom'),
new PluginReportsColumn('id', 'ID'),
new PluginReportsColumn('priority', 'Priorité'),
new PluginReportsColumn('content', 'description'),
new PluginReportsColumn('date', 'description')
));
$query = "SELECT `name`,`id`,`priority`, `content`,date from `glpi_tickets` WHERE 1+1 ".$report->addSqlCriteriasRestriction(' AND ') ;
if ($datecrit->getparameter('_glpi_tickets___date__1')<>"") {
$query .=" AND `date`>'" .$datecrit->getparameter('_glpi_tickets___date__1') . " 00:00:00' ";
}
if ($datecrit->getparameter('_glpi_tickets___date__1')<>"") {
$query .=" AND `date`<'" .$datecrit->getparameter('_glpi_tickets___date__2') . " 23:59:59' ";
}
$report->setSqlRequest($query);
$report->execute();
}
?>

jdag1992 · 2014-10-15 03:44:42

Hi, Excellent, the query by date works perfect! But, born new problem, doesn't works the query for the others items in the code (Category, Name, ID, Status, Ticket Type)
PD: I'm sorry if is too late in Europe when I write...I´m waiting your response. Thanks

LaDenrée · 2014-10-16 15:47:28

I'll try on my 0.84 test version;
it is about $report->addSqlCriteriasRestriction(' AND ') ; : this should add some criterias to the whereClause.

Lookin a the code i gave you i think there is a mistake with the test for the end date :

I wrote : if start date is set then add end date to the where clause.

the second test should be :if end date is set then add end date to the where clause

if ($datecrit->getparameter('_glpi_tickets___date__2')<>"") {
$query .=" AND `date`<'" .$datecrit->getparameter('_glpi_tickets___date__2') . " 23:59:59' ";
}

jdag1992 · 2014-10-16 15:59:41

Hi, when you added the query by date, I realized the mistake and change it.

On the other, they do not work the other questions, I'm trying to see how fix, just as what you can help me I appreciate.

Thanks

LaDenrée · 2014-10-16 16:11:51

I changed the $report->addSqlCriteriasRestriction it worked before.
so i will give a solution that works with dateinterval AND others criterias.
I have to find a trick to manage the query by date when all others criterias are empty. (that's why i added "WHERE 1=1 AND... ")

jdag1992 · 2014-10-16 16:21:12

I think there would be a way in which the date also start at zero, I've noticed that in debug mode dates start at zero but hours to 00:00:00 to 23:59:59.

LaDenrée · 2014-10-16 22:56:32

YESSSS ! got it !

When no date is set, date value is 'NULL' and not ""

$query = "SELECT `name`,`id`,`priority`, `content`,date from `glpi_tickets` WHERE 1=1 ".$report->addSqlCriteriasRestriction(' AND ') ;
if ($datecrit->getparameter('_glpi_tickets___date__1')<>'NULL') {
$query .=" AND `date`>'" .$datecrit->getparameter('_glpi_tickets___date__1') . " 00:00:00' ";
}
if ($datecrit->getparameter('_glpi_tickets___date__2')<> 'NULL') {
$query .=" AND `date`<'" .$datecrit->getparameter('_glpi_tickets___date__2') . " 23:59:59' ";
}

jdag1992 · 2014-10-16 23:36:35

Works fine.

I very much appreciate all the help you have give me

Thanks!

Forum GLPI-Project

Announcement

#1 2014-10-13 18:42:05

Report Query

#2 2014-10-14 00:07:18

Re: Report Query

#3 2014-10-14 02:06:19

Re: Report Query

#4 2014-10-14 08:27:23

Re: Report Query

#5 2014-10-14 18:09:56

Re: Report Query

#6 2014-10-14 19:06:09

Re: Report Query

#7 2014-10-14 19:09:12

Re: Report Query

#8 2014-10-14 19:10:04

Re: Report Query

#9 2014-10-14 19:17:35

Re: Report Query

#10 2014-10-14 19:32:37

Re: Report Query

#11 2014-10-14 20:14:19

Re: Report Query

#12 2014-10-14 20:20:58

Re: Report Query

#13 2014-10-14 21:34:38

Re: Report Query

#14 2014-10-15 00:33:07

Re: Report Query

#15 2014-10-15 03:44:42

Re: Report Query

#16 2014-10-16 15:47:28

Re: Report Query

#17 2014-10-16 15:59:41

Re: Report Query

#18 2014-10-16 16:11:51

Re: Report Query

#19 2014-10-16 16:21:12

Re: Report Query

#20 2014-10-16 22:56:32

Re: Report Query

#21 2014-10-16 23:36:35

Re: Report Query

Board footer