Add ‘Submitted Tickets’ Listing Page for Joomla! RSTickets

JR

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.

[php]

{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 ‘
’;

echo ‘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.’;

echo ‘SUBMITTED SUPPORT TICKETS:’;

echo ‘Submitted:Username:Ticket Code:Subject:Status:Priority:’;

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 ‘’ . date(“Y-m-d H:m”, $row[‘TicketTime’]) . ‘’ . $username[‘name’] . ‘’ . $row[‘TicketCode’] . ‘’ . $row[‘TicketSubject’] . ‘’ . strtoupper($row[‘TicketStatus’]) . ‘’;

if ($row[‘TicketPriority’]==’high’){

echo ‘’;

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

echo ‘’;

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

echo ‘’;}

echo strtoupper($row[‘TicketPriority’]) . ‘’;

}

echo ‘
SUBMIT A NEW SUPPORT TICKET’;

echo ‘
’;

?>

{/source}

[/php]

connect_custom.php :

[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.“);

}

}

?>

[/php]