This is an Example, and as such, it may not work for your case, or you may want something else to happen.
Please don't PM me with configuration for your implementation. If you know why you want this, you probably know what data you seek, and what result you want to show.
This *particular* example may violate your own internal privacy procedures because it shows your agent list, along with a count of what they've opened and closed today.
There are no filters, safeguards, or ACL applied.
This query expands on the implementation of the extdata.pl example by hand-creating a JSON result ready for implementation with Google Charts API.
As an example, this gets today's open and close tickets per agent in a nice graph.
For colors,sizes, and different chart types, you'll want to consult the Google Charts documentation.
For specific reporting queries, create a topic in the forum, and read up on the OTRS API.
If you see a bug in this implementation, pm me or reply here.
graphdata.pl:
Code: Select all
#!/usr/bin/perl -w
use strict;
use warnings;
use CGI qw(:standard);
#use JSON;
print header(-type => 'application/json');
#print header(-type => 'text/plain');
# use ../../ as lib location
use FindBin qw($Bin);
use lib "$Bin/../..";
use lib "$Bin/../../Kernel/cpan-lib";
use lib "$Bin/../../Custom";
use Kernel::Config;
use Kernel::System::Encode;
use Kernel::System::Log;
use Kernel::System::Ticket;
use Kernel::System::Time;
use Kernel::System::Main;
use Kernel::System::DB;
use Kernel::System::User;
use Kernel::System::Stats;
use Kernel::System::Group;
use Kernel::System::CSV;
my %CommonObject = ();
$CommonObject{ConfigObject} = Kernel::Config->new();
$CommonObject{EncodeObject} = Kernel::System::Encode->new(%CommonObject);
$CommonObject{LogObject} = Kernel::System::Log->new(
LogPrefix => 'ExtData',
%CommonObject,
);
$CommonObject{TimeObject} = Kernel::System::Time->new(%CommonObject);
$CommonObject{MainObject} = Kernel::System::Main->new(%CommonObject);
$CommonObject{DBObject} = Kernel::System::DB->new(
%CommonObject,
# DatabaseDSN => 'DBI:odbc:database=123;host=localhost;',
# DatabaseUser => 'user',
# DatabasePw => 'somepass',
# Type => 'mysql',
);
$CommonObject{TicketObject} = Kernel::System::Ticket->new(%CommonObject);
$CommonObject{GroupObject} = Kernel::System::Group->new(%CommonObject);
$CommonObject{CSVObject} = Kernel::System::CSV->new(%CommonObject);
$CommonObject{UserObject} = Kernel::System::User->new(%CommonObject);
###
# ticket count open/closed per agent today
#
###
my %UserList = $CommonObject{UserObject}->UserSearch(
UserLogin => '*',
Valid => 1,
);
my $TimeNow = $CommonObject{TimeObject}->SystemTime();
my ( $Sec, $Min, $Hour, $Day, $Month, $Year, $WeekDay )
= $CommonObject{TimeObject}->SystemTime2Date(
SystemTime => $TimeNow,
);
my $dataTable = '{ "cols": [ {"id":"","label":"Agent","pattern":"","type":"string"},
{"id":"","label":"Open","pattern":"","type":"number"},
{"id":"","label":"Closed","pattern":"","type":"number"}],"rows": [';
while ( my ($key, $value) = each %UserList) {
$dataTable .= '{"c":[{"v":"' . $value . '"}, ';
my $CountCreated = $CommonObject{TicketObject}->TicketSearch(
# cache search result 30 min
# CacheTTL => 60 * 30,
# tickets with create time after ... (ticket newer than this date) (optional)
TicketCreateTimeNewerDate => "$Year-$Month-$Day 00:00:00",
# tickets with created time before ... (ticket older than this date) (optional)
TicketCreateTimeOlderDate => "$Year-$Month-$Day 23:59:59",
Result => 'COUNT',
OwnerIDs => [$key],
# search with user permissions
UserID => 1,
);
$dataTable .= '{"v": ' . $CountCreated .'}, ';
my $CountClosed = $CommonObject{TicketObject}->TicketSearch(
# cache search result 30 min
# CacheTTL => 60 * 30,
# tickets with create time after ... (ticket newer than this date) (optional)
TicketCloseTimeNewerDate => "$Year-$Month-$Day 00:00:00",
# tickets with created time before ... (ticket older than this date) (optional)
TicketCloseTimeOlderDate => "$Year-$Month-$Day 23:59:59",
OwnerIDs => [$key],
Result => 'COUNT',
# search with user permissions
UserID => 1,
);
$dataTable .= '{"v": ' . $CountClosed . '}]},';
}
$dataTable = substr($dataTable, 0, -1);
$dataTable .= "]}";
print $dataTable;
1;
Code: Select all
<html>
<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="jquery-1.7.2.min.js"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var jsonData = $.ajax({
url: "http://yourotrsserver/otrs/graphdata.pl",
dataType:"json",
async: false
}).responseText;
var data = new google.visualization.DataTable(jsonData);
var chart = new google.visualization.BarChart(document.getElementById('chart_div'));
chart.draw(data, {width: 400, height: 240});
}
</script>
</head>
<body>
<div id="chart_div"></div>
</body>
</html>
so i used 
