custom report trouble with Reports plugin

r3d3y3 · 2010-09-24 00:55:29

GLPI version: 0.72.4
Reports version: 1.3.0
OS: debian squeeze 64 bit

I'm trying to create a custom report that will display tickets by a selected requester.

I am using the criteria "GenericDropdownCriteria".

I don't get a dropdown box from the following line, only the text "Requester"
new GenericDropdownCriteria($report,"dropdownnamerequester","glpi_users.name","Requester");

Thanks in advance for any suggestions.

Cheers,
Patrick

Here's all my very rough code:

//$NEEDED_ITEMS = array("enterprise");
//Options for GLPI 0.71 and newer : need slave db to access the report
$USEDBREPLICATE=1;
$DBCONNECTION_REQUIRED=0; // Really a big SQL request

define('GLPI_ROOT', '../../../..');
include (GLPI_ROOT . "/inc/includes.php");

$report = new AutoReport("ticketsbyusers");

//Report's search criterias
//new DateIntervalCriteria($report,"gt.date");
//new UserCriteria($report/*",gt.author"*/);
new GenericDropdownCriteria($report,"dropdownnamerequester","glpi_users.name","Requester");

//Display criterias form is needed
$report->displayCriteriasForm($_SERVER['PHP_SELF']);

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

//Names of the columns to be displayed
$colnumsnames = array ("date" => $LANG["reports"][60],
"author" => $LANG["job"][4],
"priority" => $LANG["joblist"][2],
"status" => $LANG["joblist"][0],
"ID"=>$LANG['common'][2],
"name" => $LANG["common"][57],
);
$report->setColumnsNames($colnumsnames);

//Colunmns mappings if needed
$columns_mappings = array("priority"=>getPriorityLabelsArray());
$report->setColumnsMappings($columns_mappings);

$query = "SELECT DATE(gt.date) as date, glpi_users.name as author, gt.priority as priority, gt.status as status, gt.ID as ID, gt.name as name " .
"FROM glpi_tracking as gt, glpi_users ";
$query.= $report->addSqlCriteriasRestriction("WHERE");
$query.= "AND gt.status NOT IN (\"old_done\", \"old_notdone\") " .
"AND glpi_tracking.ID=gt.author " .
"ORDER BY priority DESC, date ASC";

$report->setSqlRequest($query);

$report->execute();
}
else
commonFooter();
?>
<?
echo $report->addSqlCriteriasRestriction("WHERE");
//uncomment to display the contents of the $LANG array
print_r($LANG);
?>

r3d3y3 · 2010-09-25 15:30:48

I still would like to understand why "new GenericDropdownCriteria($report,"dropdownnamerequester","glpi_users.name","Requester");" is not working for me, but I have started on a work around for this particular report.

Instead of using "$report->addSqlCriteriasRestriction("WHERE");" directly in the mysql query, I'm doing a find and replace on the string first. Here's some more rough code:

//$NEEDED_ITEMS = array("enterprise");
//Options for GLPI 0.71 and newer : need slave db to access the report
$USEDBREPLICATE=1;
$DBCONNECTION_REQUIRED=0; // Really a big SQL request

define('GLPI_ROOT', '../../../..');
include (GLPI_ROOT . "/inc/includes.php");

$report = new AutoReport("ticketsbyusers");

//Report's search criterias
//new DateIntervalCriteria($report,"gt.date");
new UserCriteria($report);
//new GenericDropdownCriteria($report,"dropdownnamerequester","glpi_users.name","Requester");

//Display criterias form is needed
$report->displayCriteriasForm($_SERVER['PHP_SELF']);

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

//Names of the columns to be displayed
$colnumsnames = array ("date" => $LANG["reports"][60],
//"author" => $LANG["job"][4],
"priority" => $LANG["joblist"][2],
"status" => $LANG["joblist"][0],
"ID"=>$LANG['common'][2],
"name" => $LANG["common"][57],
);
$report->setColumnsNames($colnumsnames);

//Colunmns mappings if needed
$columns_mappings = array("priority"=>getPriorityLabelsArray());
$report->setColumnsMappings($columns_mappings);

//find&replace text in the WHERE string
$where = str_replace("user","author",$report->addSqlCriteriasRestriction("WHERE"));

//$query = "SELECT DATE(gt.date) as date, glpi_users.name as author, gt.priority as priority, gt.status as status, gt.ID as ID, gt.name as name " .
$query = "SELECT DATE(gt.date) as date, gt.priority as priority, gt.status as status, gt.ID as ID, gt.name as name " .
"FROM glpi_tracking as gt, glpi_users ";
//$query.= $report->addSqlCriteriasRestriction("WHERE");
$query.= $where;
$query.= "AND gt.status NOT IN (\"old_done\", \"old_notdone\") " .
//"AND glpi_tracking.ID=gt.author " .
"ORDER BY priority DESC, date ASC";

$report->setSqlRequest($query);

$report->execute();
}
else
commonFooter();
?>
<?
echo $where;
echo $report->addSqlCriteriasRestriction("WHERE");
//uncomment to display the contents of the $LANG array
print_r($LANG);
?>

remi · 2010-09-25 17:28:02

Try

$user = new GenericDropdownCriteria($report, "glpi_tracking.author", "glpi_users", "Requester");

or

$user = new UserCriteria($report);
$user->setSqlField('glpi_tracking.author');

+

r3d3y3 · 2010-09-27 22:34:53

Thanks for the reply remi.

I tried them both but I'm not doing something right because they both result in blank reports and do not keep the requester name once I click the search button. I have tried some different things, but don't know where or how to reference the variable $user in the sql query.

Thank you very much for help!

EiZ · 2010-11-08 18:32:10

Hi,

I confirm this problem with GenericDropdownCriteria : the selected value is ignored and the criteria value is alwais 0. I tried

new GenericDropdownCriteria($report,"glpi_tracking.FK_group","glpi_groups","Client");

Strangely when using GroupCriteria (which is almost the same as the former GenericDropdownCriteria, the group value is correctly selected.
Thus the string replacement trick is the way to go. Thanks for this.

Forum GLPI-Project

Announcement

#1 2010-09-24 00:55:29

custom report trouble with Reports plugin

#2 2010-09-25 15:30:48

Re: custom report trouble with Reports plugin

#3 2010-09-25 17:28:02

Re: custom report trouble with Reports plugin

#4 2010-09-27 22:34:53

Re: custom report trouble with Reports plugin

#5 2010-11-08 18:32:10

Re: custom report trouble with Reports plugin

Board footer