Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Show open tickets to Public

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?
«1

Comments

  • 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:

    image
  • 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

    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.
  • @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' [COLOR=\"Red\"]AND dept_id = '1'[/COLOR]
    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.
  • ntozier;37358 said:
    @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' [COLOR=\"Red\"]AND dept_id = '1'[/COLOR]
    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 <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.
    **********************************************************************/

    // 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 "";

    $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: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 ""
    .""
    .""
    ."";

    ++$i;
    }
    echo "
    NameSiteIssueOpened onLast Update
      $name     $subject ($priority)     $created     $updated  
    ";
    }

    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)
  • 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);
    @mysql_select_db($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: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> &nbsp; $ticketid &nbsp; </td><td BGCOLOR=$bgcolour id='openticks-a' nowrap> &nbsp; $name &nbsp; </td><td BGCOLOR=$bgcolour id='openticks-a'> &nbsp; $subject ($priority) &nbsp; </td>\"
    .\"<td BGCOLOR=$bgcolour id='openticks-a'> &nbsp; $created &nbsp; </td><td BGCOLOR=$bgcolour id='openticks-b'>\"
    .\" &nbsp; $updated &nbsp; </td><td BGCOLOR=$bgcolour id='openticks-a'> &nbsp; $dept &nbsp; </td></tr>\";

    ++$i;
    }
    echo \"</table>\";
    }

    else {
    echo \"<p style='text-align:center;'><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.
  • OK well thank you both for your replies. I'll give this a go here in a bit and see. Thats why I'm confused though, queries run fine if I do them manually, but not getting any love when trying to in the live site so I'll take a look into it. Thanks for all your help
  • @chaosratt
    I actually purposefully didn't include ticket #, since that's essentially one half of what someone needs to log into the system. I saw it as meaning that with a little bit of knowledge (such as a companies username schema) since it displays users names and with the ticket number anyone could log into the ticket system as that client and update their tickets, and cause general badness. Until osTicket actually has its own user authentication system for clients I felt it was to insecure for us. If you are utilizing osTicket for an closed intranet site its probably okay, but if its at all internet facing...

    Sorry off the soap box :)

    @codyw725
    Feel free to keep posting and we'll help you the ebst that we can. :)
  • ntozier,
    I fully understand your concern. osTicket will be public, but this list will not be. We have an ancient, clunky, must-die-now order processing and ticket system, where the current tickets show up on the dashboard, just above the pending orders. I planned to embed this table there, since everyone in the company has that page up at all times. Its on a magnificently obfuscated url, behind a login page. Our current system doesn't show the TK#, so I might drop it, it was more an exercise for me to become familiar with it. I've tinkered a bit with PHP in the past, but never pulled SQL data using it.
  • chaosratt,
    Ah okay, gotcha. That makes sense. I just want to make sure that [other] people don't implement this sort of thing with out realizing the security implications because they read about it on osticket.com and didn't think about what it actually meant. :)
  • So since its not defined who does the queries run as? I guess maybe thats my problem is in my script there is no username or password designated for it to run as? Just a thought?
  • Presuming you didn't change anything from the last cut and paste its running as your osticket database user.

    If you made the script stand alone by added the following:

    # configure this area with your database connection information
    $dbhost = 'localhost'; // FQDN server name or IP (or localhost for local machine)
    $dbname = 'database'; // database name
    $dbuser = 'dbusername'; // database username
    $dbpass = 'dbpassword'; // database password for username (above)

    # make the connection to the MySQL server
    mysql_connect($dbhost,$dbuser,$dbpass);
    @mysql_select_db($dbname) or die( \"DB Error: Unable to select database\");


    Then whatever user you specified.
  • Well I even tried chaosratt's script above, modified it with my info and still no dice. I'm not getting any php error logs generated, even though I've turned them all on it php.ini so I'm not sure. Guess I'll chalk it up as a loss and move on. Thanks
  • Have you made sure that you have the FAQ enabled, have at least 1 category *and* have at least 1 article? The table would not appear on the index until I did that.
    With the edits in my version with the SQL info, you should be able to hit the php file directly in you browser.
  • chaosratt;37915 said:
    Have you made sure that you have the FAQ enabled, have at least 1 category *and* have at least 1 article? The table would not appear on the index until I did that.
    With the edits in my version with the SQL info, you should be able to hit the php file directly in you browser.
    Yes I have a FAQ setup in the knowledgebase with a category and with an article and still no dice....
  • So its not out putting anything at all after the line that reads:
    Be sure to browse both our Frequently Asked Questions (FAQs), and the open tickets below before opening a ticket.
    .

    Try this:
    open and edit \index.php
    scroll down and replace line 48-EOF
    with this


    <?php
    if($cfg && $cfg->isKnowledgebaseEnabled()){
    //FIXME: provide ability to feature or select random FAQs ??
    ?>
    <p>Be sure to browse both our <a href=\"kb/index.php\">Frequently Asked Questions (FAQs)</a>.
    <?php
    } ?>
    <p>Please review the open tickets below before opening a ticket. Thank you.
    <div id=\"openticks\"><?php include('display_open_topics.php'); ?></div>
    </p>
    </div>
    <?php require(CLIENTINC_DIR.'footer.inc.php'); ?>


    This moves the table out of the FAQ section but still after that and inside the closing div. I've attached a zip with an alternate index.php so you can just drop it into place. You will have to rename index-alternate.php to index.php. Make sure that you back up your current index.php in case you want to revert to it.
    index-alternate.zip
    1K
Sign In or Register to comment.