- Edited
Here is my current version of ntozier's very useful script.
I removed his 'agency' lookup parts (since that's another mod, though I may reuse that for my own purposes later) and re-purposed the agency lookup code to do a department name lookup. Its crude, but it works for me:
<?php
/*********************************************************************
display_open_topics.php
Displays a block of the last X number of open tickets.
Neil Tozier <tmib@tmib.net>
Copyright (c) 2010-2013
For use with osTicket version 1.7ST (http://www.osticket.com)
Released under the GNU General Public License WITHOUT ANY WARRANTY.
See osTickets's LICENSE.TXT for details.
**********************************************************************/
# configure this area with your database connection information
$dbhost = 'localhost'; // FQDN server name or IP (or localhost for local machine)
$dbname = 'osticket'; // database name
$dbuser = 'osticket'; // database username
$dbpass = '<your db pass here>'; // database password for username (above)
# make the connection to the MySQL server
mysql_connect($dbhost,$dbuser,$dbpass);
<USERMENTION username="mysql_select_db">@mysql_select_db</USERMENTION>($dbname) or die( "DB Error: Unable to select database");
// The columns that you want to collect data for from the db
$columns = "ticketID, name, subject, created, updated, priority_id, dept_id";
// The maximum amount of open tickets that you want to display.
$limit ='10';
// mysql query. The columns tha
$query = "SELECT $columns
FROM ost_ticket
WHERE status = 'open'
ORDER BY created DESC
LIMIT 0,$limit";
$result=mysql_query($query);
$num = mysql_num_rows($result);
if ($num >> 0) {
// table headers, if you add or remove columns edit this
echo "<table border-color=#BFBFBF border=0 cell-spacing=2><tr style='background-color: #BFBFBF;'>";
echo "<td id='openticks-a'><b>Ticket #</b></td><td id='openticks-a'><b>Name</b></td><td id='openticks-a'><b>Issue</b></td><td id='openticks-a'><b>Opened on</b></td><td id='openticks-b'><b>Last Update</b></td><td id='openticks-b'><b>Department</b></td></tr>";
$i=0;
while ($i < $num) {
// You will need one line below for each column name that you collect and want to display.
// If you are unfamiliar with php its essentially $uniqueVariable = mysql junk ( columnName );
// Just copy one of the lines below and change the $uniqueVariable and columnName
$ticketid = mysql_result($result,$i,"ticketID");
$name = mysql_result($result,$i,"name");
$subject = mysql_result($result,$i,"subject");
$created = mysql_result($result,$i,"created");
$updated = mysql_result($result,$i,"updated");
$priority = mysql_result($result,$i,"priority_id");
$department_id = mysql_result($result,$i,"dept_id");
// if no update say so
if ($updated == '0000-00-00 00') {
$updated = 'no update yet';
}
// look up department and display proper name
// mysql query.
$getdept_names = "SELECT * FROM ost_department WHERE dept_id='$department_id'";
$deptresult = mysql_query($getdept_names);
$dept = mysql_result($deptresult,0,"dept_name");
// change row back ground color to make more readable
if(($i % 2) == 1) //odd
{$bgcolour = '#F6F6F6';}
else //even
{$bgcolour = '#FEFEFE';}
//populate the table with data
echo "<tr align=center><td BGCOLOR=$bgcolour id='openticks-a' nowrap> $ticketid </td><td BGCOLOR=$bgcolour id='openticks-a' nowrap> $name </td><td BGCOLOR=$bgcolour id='openticks-a'> $subject ($priority) </td>"
."<td BGCOLOR=$bgcolour id='openticks-a'> $created </td><td BGCOLOR=$bgcolour id='openticks-b'>"
." $updated </td><td BGCOLOR=$bgcolour id='openticks-a'> $dept </td></tr>";
++$i;
}echo "</table>";
}else {
echo "<p style='text-align;'><span id='msg_warning'>There are no tickets open at this time.</span></p>";
}?>
This spits out the table stand-alone, which means you can iFrame it into another website. It also displays the ticket# as well. I see that the dashboard links to the ticket_id (Which is *NOT* the ticket#), so I might even make it a dynamic link that someone can click to go right to the ticket.
