I've seen this listed as a feature others seem to want in the user
lists, and since I wanted it myself I took a little bit of time to write
a quick and dirty procedure to view all new/open tickets. Our users
wanted this so they could tell if others had already submitted common
problems, plus managers sometimes like to see if the tickets submitted
by their team members are being handled. Anyway, it is written in PHP
and works great for us, maybe other could make use of it.
I'm not a professional developer, so please no flames about the quality
of my code :-)
<?php
/* Connecting, selecting database */
$link = mysql_connect("localhost", "otrsdbuser", "foobar")
or die("Could not connect : " . mysql_error());
mysql_select_db("otrs") or die("Could not select database");
/* Performing SQL query */
$query = "select t.tn, CONCAT(cu.last_name,', ',cu.first_name) user,
ts.name status, t.create_time, a.a_body
from article a,
ticket_state ts,
ticket_history th,
ticket as t left outer join customer_user as cu on
(t.customer_user_id = cu.login)
where t.id = a.ticket_id and
t.id = th.ticket_id and
a.id = th.article_id and
t.ticket_state_id = ts.id and
t.ticket_state_id not in (2,3,5) and
a.article_type_id in (1,2,5,8) and
th.history_type_id in (1,12)
order by t.create_time desc";
$result = mysql_query($query) or die("Query failed : " .
mysql_error());
/* Printing results in HTML */
print "<table border=3>\n";
print "<caption><strong><font size=\"+3\">Open Helpdesk
Requests</font></strong></caption>";
print "<th>Ticket Number</th>";
print "<th>Requestor</th>";
print "<th>Ticket Status</th>";
print "<th>Create Time</th>";
print "<th>Description</th>";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
print "\t<tr>\n";
foreach ($line as $col_value) {
print "\t\t<td>$col_value</td>\n";
}
print "\t</tr>\n";
}
print "</table>\n";
/* Free resultset */
mysql_free_result($result);
/* Closing connection */
mysql_close($link);
?>