Add ‘Submitted Tickets’ Listing Page for Joomla! RSTickets

If you haven’t heard, RSTickets! is an advanced Joomla! Help Desk ticketing system that allows you (or a team of yous) to manage and keep track of your clients’ issues. It’s actually one of the few effective, useful Help Desk systems available for the Joomla! 1.5+ framework that I would personally recommend. Unfortunately, it’s still under development and lacks certain features that one may desire, such as a read-only listing page that displays tickets already submitted to you or your department.

My Problem:

I noticed internal network clients were submitting several duplicate tickets related to a shared problem (i.e. printer trouble, network outages, etc.). I couldn’t blame them since they couldn’t view previously submitted tickets, so I decided to create a quick + dirty page that pulls “open,” or “on-hold,” tickets from a specific department’s table of submitted [active] tickets and displays them on a Joomla! article page (using the Sourcerer plug-in to execute custom PHP with {source} tags). Pasting this code into an article without the Sourcerer plug-in [or some sort of plug-in for executing PHP] will do nothing. Also note the include file for making a raw connection to your MySQL database. This is required (and should be stored in a directory with the appropriate permissions to prevent outside read access).

If you’d like to simply list the number (amount) of tickets (open, closed, on-hold) for all departments, you might want to check the unreleased version of RSJoomla!’s RSTicket Module.

{source}
<?php

include ("includes/connect_custom.php");

$result2 = mysql_query("SELECT * FROM jos_rstickets_tickets WHERE DepartmentId=3 AND (TicketStatus='open' OR TicketStatus='on-hold') ORDER BY TicketTime ASC") or die(mysql_error());

echo '<br />';

echo '<tr><td style="padding-bottom: 10px;" colspan="6">Please check the open, pending, or on-hold tickets listed below before submitting a support ticket. The list below can be utilized as an informal gauge for IT response times. If a duplicate support ticket is submitted, you may cancel it yourself or it will be deleted by the IT Department upon review. Thank you for your cooperation.</td></tr>';

echo '<tr><td style="font-size: 16px;font-weight: bold;padding-bottom: 10px;" colspan="6">SUBMITTED SUPPORT TICKETS:</td></tr>';

echo '<tr><td style="text-align:left; padding-bottom:7px;"><strong>Submitted:</strong></td><td style="padding-bottom:7px;"><strong>Username:</strong></td><td style="padding-bottom:7px;"><strong>Ticket Code:</strong></td><td style="padding-bottom:7px;"><strong>Subject:</strong></td><td style="padding-bottom:7px;"><strong>Status:</strong></td><td style="padding-bottom:7px;"><strong>Priority:</strong></td></tr>';

while($row = mysql_fetch_array($result2))

{

$custid =  $row['CustomerId'];
$userquery= mysql_query("SELECT name FROM jos_users WHERE id=$custid") or die(mysql_error());

$username = mysql_fetch_array($userquery);

echo '<tr style="font-size: 10px;vertical-align:top;"><td style="width: 102px; overflow: hidden;padding: 3px -10px 3px 3px;">' . date("Y-m-d H:m", $row['TicketTime']) .  '</td><td>' . $username['name'] . '</td><td>' . $row['TicketCode'] . '</td><td style="width: 235px; overflow: hidden;">' . $row['TicketSubject'] . '</td><td>' . strtoupper($row['TicketStatus']) . '</td>';

if ($row['TicketPriority']=='high'){

echo '<td style="background-color: red; color: white; font-weight: bold;padding-left: 5px;">';

} else if ($row['TicketPriority']=='normal'){

echo '<td style="background-color: blue; color: white; font-weight: bold;padding-left: 5px;">';

} else if ($row['TicketPriority']=='low'){

echo '<td style="background-color: yellow; font-color: black; font-weight: bold;padding-left: 5px;">';}

echo strtoupper($row['TicketPriority']) . '</td></tr>';

}

echo '<tr><td style="font-size:16px; padding-top: 20px; padding-bottom: 20px" colspan="6"><strong><a href="index.php?option=com_rstickets&Itemid=59"><img src="images/M_images/onsite_support1.jpg"></a><br /><a href="index.php?option=com_rstickets&Itemid=59">SUBMIT A NEW SUPPORT TICKET</a></strong></td></tr>';

echo '<br />';

?>
{/source}

connect_custom.php :

<?php

// Script:	connect_custom.php
// Author:	JR
// Date:	20080218
// Use:	    Utilized for custom DB connections to our current database for Fabrik Forms + Joomla 1.5

$hostname="localhost";
$mysql_login="thedude";
$mysql_password="sumpasswordhere";
$database="datablah";

if (!($db = mysql_pconnect($hostname, $mysql_login , $mysql_password))){
die("Can't connect to database server.");
}else{
if (!(mysql_select_db("$database",$db))){
die("Can't connect to database.");
}
}
?>