This thread interested me because I felt that it raised a good idea.
So I started poking around. Initially I would have liked to integrate this type of thing into the existing codebase that I use for osTicket. But I quickly realized that I would have to have an user auth (either as a staff, or as client). So instead I did the following:
1. In the root directory of the installation I opened up index.php and around line 64 inserted the following:
Before opening a new ticket for site outages, etc. Please review the open tickets beflow to ensure that a ticket is not already open.
<? include('display_open_topics.php'); ?>
This should be done before the final in that file. (Assuming that you haven't already messed with the file.)
For those not familiar with PHP or HTML this adds a dividing line, displays the text, and includes the file "display_open_topics.php".
Note: This step can be skipped completely if you want to display this information on a completely separate page. In which case simply put the file where you want, and call it via the include line above. This sample script DOES NOT display page headers/footers etc. It is intended to be used as an include in another page.
2. Next I created a rather simplistic file called "display_open_topic.php". This file is located in the same directory as the index.php. Here is the contents of my files (sans my account information):
<?php
/*********************************************************************
display_open_topics.php
Displays a block of the last X number of open tickets.
Neil Tozier
Copyright (c) 2010
For use with osTicket (http://www.osticket.com(http://www.osticket.com))
Released under the GNU General Public License WITHOUT ANY WARRANTY.
See LICENSE.TXT for details.
**********************************************************************/
# configure this area with your database connection information
$dbhost = 'localhost';
$dbname = 'dataBaseName';
$dbuser = 'MySQLUserName';
$dbpass = 'MySQLUsersPassword';
// The columns that you want to collect data for from the db
$columns = "subject, created, updated, site, priority_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";
mysql_connect($dbhost,$dbuser,$dbpass);
@[deleted]($dbname) or die( "DB Error: Unable to select database");
$result=mysql_query($query);
$num = mysql_numrows($result);
// table headers, if you add or remove columns edit this
echo "";
echo "SiteIssueOpened onLast Update";
$i=0;
while ($i < $num) {
// You will need one line below for each column name that you collect and want to display.
// Just copy one of the lines below and change the $uniqueVariable and columnName
$subject = mysql_result($result,$i,"subject");
$created = mysql_result($result,$i,"created");
$updated = mysql_result($result,$i,"updated");
$site = mysql_result($result,$i,"site");
$priority = mysql_result($result,$i,"priority");
// this line is what displays the column information. Each column that you pull from the db should have its variable here inside of a .
echo "$site$subject$created$updated";
++$i;
}
echo "";
?>
Here is a partial screen shot of what it ends up looking like:
As you can see it's not very polished. When I get a chance I will be polishing it up. Changing the date display to something a little more human readable, using the priority to color code the rows, etc. It screams against my sensibilities to have the user info in the file, but I didn't want to take the time to use the settings.php (I may revisit this part later once I've learned more about how the file structure in osTicket is setup. I consider myself a php/mysql amateur. I've been playing with PHP/MySQL for a few years now as a hobby in my space time.
Comments to tighten the code or help secure the code are appreciated. Yes this is a little sloppy, but it's not intended for a public accessible web page, and was thrown together very quickly. Hopefully over the next week I can revisit this and make some changes to make it a little easier for other people to use.
Important Note: site is a custom field that I added to my osTicket implementation. Although I left it in this example, you will have to change/remove it.
