You are not logged in.
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);
?>
Offline
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);
?>
Offline
Try
$user = new GenericDropdownCriteria($report, "glpi_tracking.author", "glpi_users", "Requester");
or
$user = new UserCriteria($report);
$user->setSqlField('glpi_tracking.author');
+
Dév. Fedora 29 - PHP 5.6/7.0/7.1/7.2/7.3/7.4 - MariaDB 10.3 - GLPI master
Certifié ITILv3 - RPM pour Fedora, RHEL et CentOS sur https://blog.remirepo.net/
Offline
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!
Offline
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.
Offline