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 J
<?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);
?>