You are not logged in.
Pages: 1
OS: Ubuntu Server build 12.04 LTS
GLPI: 0.83.7
Reports Plugin: 1.6.1
I had posted a few days ago regarding a similar topic, but no one has responded and I think a report is the better/easier way to accomplish this.
I want to have the ability to view in a list, a particular user's previously assigned Computers. This way, I can look up their last computer in case we forgot to retrieve a file from it -- granted it wasn't already wiped and reassigned. I have a SQL statement prepared that basically gets me a printout of all "assigned to" dates with a computer inventory number and user's name associated, but I'd like to create a report that allows me to select the user in question, then only show me the relevant assignment history associated with that one user.
Here is what I have so far in my pastAssignments.php report file:
<?php
//Options for GLPI 0.71 and newer : need slave db to access the report
$USEDBREPLICATE = 1;
$DBCONNECTION_REQUIRED = 0;
define('GLPI_ROOT', '../../../..');
include (GLPI_ROOT . "/inc/includes.php");
$report = new PluginReportsAutoReport($LANG['plugin_reports']['pastAssignments'][1]);
$name = new PluginReportsUserCriteria($report, 'glpi_users.name', $LANG['login'][6]);
//Display criterias form is needed
$report->displayCriteriasForm();
//specify columns displayed
if ($report->criteriasValidated()) {
$report->setSubNameAuto();
$cols = array(
new PluginReportsColumn('name', $LANG["entity"][0]),
new PluginReportsColumn('firstname', $LANG["common"][43]),
new PluginReportsColumn('realname', $LANG["common"][48]),
new PluginReportsColumn('itemtype', $LANG["common"][48]),
new PluginReportsColumn('Computer', $LANG["common"][48]),
new PluginReportsColumnDate('Date', $LANG["common"][48]),
);
$report->setColumns($cols);
//TSQL
$query = "SELECT glpi_users.name,
glpi_users.firstname,
glpi_users.realname,
glpi_logs.new_value,
glpi_logs.itemtype,
glpi_logs.date_mod AS Date,
glpi_computers.otherserial AS Computer,
FROM glpi_users, glpi_logs
LEFT JOIN glpi_computers ON (glpi_computers.id=glpi_logs.items_id)
LEFT JOIN glpi_phones ON (glpi_phones.id=glpi_logs.items_id)
WHERE glpi_users.name=glpi_logs.new_value
AND glpi_logs.itemtype='Computer'
ORDER BY Date DESC";
$report->setSqlRequest($query);
//Execute the query (and display results) :
$report->execute();
} else {
Html::footer();
} I'm stuck though. I've looked at the reports plugin wiki, but can't find the answer I'm looking for. How do I essentially "plug" the username value I have selected from the "PluginReportsUserCriteria()" object into the SQL statement so that I get just the history information for that single user?
Currently, I get no results no matter what user I select in the dropdown, and I'm not sure how to find out why.
I'm not a programmer by any means, and certainly a php newb at best. I think if I can get one report working that I fully understand, then I can build from there to more complex reports if needed.
GLPI: 0.90.4-1 (Remi Repo), CentOS 7
Offline
Ok, I've at least gotten the report to generate a list of all the computer assignment actions:
<?php
//Options for GLPI 0.71 and newer : need slave db to access the report
$USEDBREPLICATE = 1;
$DBCONNECTION_REQUIRED = 0;
define('GLPI_ROOT', '../../../..');
include (GLPI_ROOT . "/inc/includes.php");
$report = new PluginReportsAutoReport();
$name = new PluginReportsUserCriteria($report, 'glpi_users.name', $LANG['login'][6]);
//Display criterias form is needed
$report->displayCriteriasForm();
//specify columns displayed
if ($report->criteriasValidated()) {
$report->setSubNameAuto();
$report->setColumns(array(new PluginReportsColumn('name', $LANG["stats"][20]),
new PluginReportsColumn('firstname', $LANG["common"][43]),
new PluginReportsColumn('realname', $LANG["common"][48]),
new PluginReportsColumn('itemtype', $LANG["common"][17]),
new PluginReportsColumn('Computer', $LANG["common"][20]),
new PluginReportsColumnDate('Date', $LANG["common"][27]),
));
//TSQL
$query = "SELECT `glpi_users`.`name`,
`glpi_users`.`firstname`,
`glpi_users`.`realname`,
`glpi_logs`.`new_value`,
`glpi_logs`.`itemtype`,
`glpi_logs`.`date_mod` AS `Date`,
`glpi_computers`.`otherserial` AS `Computer`
FROM `glpi_users`, `glpi_logs`
LEFT JOIN `glpi_computers` ON (`glpi_computers`.`id`=`glpi_logs`.`items_id`)
LEFT JOIN `glpi_phones` ON (`glpi_phones`.`id`=`glpi_logs`.`items_id`)
WHERE `glpi_users`.`name`=`glpi_logs`.`new_value`
AND `glpi_logs`.`itemtype`='Computer'
ORDER BY Date DESC";
$report->setSqlRequest($query);
//Execute the query (and display results) :
$report->execute();
} else {
Html::footer();
}
However, now I'm back to me original problem, where I can select a name from the dropdown menu, but no matter what it shows me ALL of the entries generated by the SQL statement. How do I use the username value selected in the dropdown to generate a report based only on the selected user?
Last edited by rbernier (2013-07-22 18:22:08)
GLPI: 0.90.4-1 (Remi Repo), CentOS 7
Offline
I just wanted to ask for a little assistance on this again. I've basically got a working SQL statement in my report, in that it collects and displays what I want, however the key piece I am missing is how to "connect" the User List dropdown selection to what I see displayed in the report.
At this point I get all past assignment actions on all users. I would like to only see the individual selected in the dropdown. Thanks in advance, I really would appreciate the help!
Last edited by rbernier (2013-08-27 21:31:36)
GLPI: 0.90.4-1 (Remi Repo), CentOS 7
Offline
Pages: 1