One of the things I wish OSticket had was the ability to allow a "public" dashboard showing open tickets. I think it's important for management and other members of the company to see our que/ work load.
Does anyone have any suggestions on how to do this, or have you done this in the past?
Its on my to do list, and mostly completed. I was running something similar with 1.6ST.
It doesn't display ticket number. It does show: who opened a ticket (name), ticket subject, date opened, site opened for, last updated.
Once I finish re-writing it I will of course post it on the forums.
Here is a screenshot of the client landing page:
That would be amazing, and thank you!
I've posted it here:
Add a client side open ticket list to osticket 1.7ST
http://www.tmib.net/add-client-side-open-ticket-list-osticket-17st(http://www.tmib.net/add-client-side-open-ticket-list-osticket-17st)
I'll make a thread for it here on the forums later when I get a chance.
Thanks a ton! Ill add the code, and let you know how it goes.
Great MOD
This MOD works very well and will be very useful. Is there a way to display which department the ticket came into and limit tickets to only certain departments to be displayed? I'm not an expert but I would imagine that it would require a pull from the database on the department field and set to only match on specific departments. I guess it would be similar to how you are pulling only open tickets.
@[deleted],
Each ticket has a department id (dept_id) so you could include that in the SQL query. Say you only wanted tickets with a dept_id of 1 (note: they are stored as numbers in the database!) you would edit lines 22-26 (the SQL query) by adding whats in red below:
$query = "SELECT $columns
FROM ost_ticket
WHERE status = 'open' AND dept_id = '1'
ORDER BY created DESC
LIMIT 0,$limit";
You can look up the number to department name relationship in the ost_department table either by browsing it or running a SQL query like this:
SELECT dept_id,dept_name FROM ost_department WHERE ispublic='1'
Works great, thanks for posting the info.
You're very welcome. :)
So while implementing this on one of my sites today I noticed a little error on my part. The open tickets apparently only display if you have enabled knowledgebase and actually have a category setup. Ummm oops? I'll fix it later and post about it when I get a chance.
@jsmith773,Each ticket has a department id (dept_id) so you could include that in the SQL query. Say you only wanted tickets with a dept_id of 1 (note: they are stored as numbers in the database!) you would edit lines 22-26 (the SQL query) by adding whats in red below:
$query = "SELECT $columns
FROM ost_ticket
WHERE status = 'open' AND dept_id = '1'
ORDER BY created DESC
LIMIT 0,$limit";You can look up the number to department name relationship in the ost_department table either by browsing it or running a SQL query like this:
SELECT dept_id,dept_name FROM ost_department WHERE ispublic='1'
I ran the query on the database and found the correct number for each department. I then edited the correct file and modified the correct line. When I do that the list disappears on the front page and doesn't display anything.
I added this to my site and its not showing open tickets (I have knowledgebase enabled and I have two categories setup, one internal and one public). The page displays correctcly at the bottom stating to Be sure to browse both our FAQ's and the open tickets below before opening a ticket, yet it doesn't show any tickets. I have 3 open tickets in my dashboard, yet none display. Any ideas?
Are you getting any errors ni your PHP log?
Might also want to check your MySQL logs.
I'll have to try it out in my spare time. I removed it since it wasn't functioning correctly so when I get time I'll have to modify the files and try it again. Thanks
Added back all the code (after removing our agency sql queries) and its still not working. PHP and SQL show no errors. I just see the standard page, except at the bottom it now says to check the FAQs or currently opened tickets below, but there is no table, no nothing
Heres the code I'm using:
<?php
/*********************************************************************
display_open_topics.php
Displays a block of the last X number of open tickets.
Neil Tozier
Copyright (c) 2010-2013
For use with osTicket version 1.7ST (http://www.osticket.com(http://www.osticket.com))
Released under the GNU General Public License WITHOUT ANY WARRANTY.
See osTickets's LICENSE.TXT for details.
**********************************************************************/
// The columns that you want to collect data for from the db
$columns = "name, subject, created, updated";
// mysql query. The columns tha
$query = "SELECT $columns
FROM ost_ticket
WHERE status = 'closed'
ORDER BY created DESC
LIMIT 0,10;
$result=mysql_query($query);
$num = mysql_numrows($result);
if ($num >> 0) {
// table headers, if you add or remove columns edit this
echo "";
echo "NameSiteIssueOpened onLast Update";
$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
$name = mysql_result($result,$i,"name");
$subject = mysql_result($result,$i,"subject");
$created = mysql_result($result,$i,"created");
$updated = mysql_result($result,$i,"updated");
// if no update say so
if ($updated == '0000-00-00 00') {
$updated = 'no update yet';
}
// 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 " $name "
." $subject ($priority) "
." $created "
." $updated ";
++$i;
}echo "";
}else {
echo "There are no tickets open at this time.
";}?>
Its not even displaying the last portion where it states there are no open tickets. I can manually run all the SQL queries on my server and it comes back fine so I'm guessing there is something wrong with the table parameters in the code
WHERE status = 'closed'
You're trying to select closed tickets?
When you removed $limit from the code you missed adding a " in on line 31. Change:
LIMIT 0,10;
to
LIMIT 0,10";
Yes I wanted it to show closed for testing purposes because open was not working and I must have forgot to change it back before copying and pasting the code. I added back the " and ran the query and the query returned (didn't realize it was closing your $query statement. However, it still does not work on the client page.
I'm not sure what to tell you then because after the making the changes I mentioned to the code that you pasted above... it works on my live site.
Although I did just notice another change that you will want to make.
on line 32
remove
<td id='openticks-a'><b>Site</b></td>
You removed the rest of the site/agency references except for this one.
I recommend that you consult your php error logs, and see what the error is that's getting reported. (You may need to turn error reporting on in your php.ini)
