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

2»

Comments

  • Yes sir that worked like a charm! Thanks for all your help! I might modify it to add some different columns, but its showing up now. Thanks again!
  • last time......is there a way to display LOW, NORMAL, HIGH for priority instead of just a priority_id? I know the actual names for the priorities are stored in the ost_ticket_priority table. I tried making an IF statement, but it didn't work.
  • You could do something like this

    if($priority_id == '1') { 
    $priority = 'Low';
    }
    if($priority_id == '2') {
    $priority = 'Normal';
    }
    if($priority_id == '3') {
    $priority = 'High';
    }
    if($priority_id == '4') {
    $priority = 'Emergency';
    }
    echo $priority;
  • You could even echo that inside an img tag with appropriately named images and have it display small gifs and/or images.
  • ntozier;37964 said:
    You could do something like this

    if($priority_id == '1') { 
    $priority = 'Low';
    }
    if($priority_id == '2') {
    $priority = 'Normal';
    }
    if($priority_id == '3') {
    $priority = 'High';
    }
    if($priority_id == '4') {
    $priority = 'Emergency';
    }
    echo $priority;
    Where would the IF statement go? I tried putting it in and its not doing anything.
  • I'd add it the block at about line 55 of the display_open_topics.php.
  • I've made some changes that I'd like to contribute back to the group.
    Note: This is embedded into our order processing control panel that only staff see, I would *NOT* recommend showing this to the public.

    image

    <?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 = '<DBPASS>'; // 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 = \"ticket_id, ticketID, staff_id, 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' AND dept_id != 3
    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 width=890 border-color=#BFBFBF border=0 cell-spacing=2 style='font-family: Verdana, Geneva, Arial, Helvetica, sans-serif; font-size: xx-small;'><tr style='background-color: #BFBFBF;' align=center>\";
    echo \"<td id='openticks-a'><b>Priority</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><td id='openticks-b'><b>Assigned To</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
    $ticket_id = mysql_result($result,$i,\"ticket_ID\");
    $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\");
    $staff_id = mysql_result($result,$i,\"staff_id\");

    // if no update say so, and make the date format more human friendly
    if ($updated == '0000-00-00 00:00:00')
    {
    $updated_date = 'No update yet';
    }
    else
    {
    $updated_date = date(\"d-m-y g:iA\", strtotime($updated));
    }

    //make the created date format more human friendly
    $created_date = date(\"d-m-y g:iA\", strtotime($created));


    // look up department and then cross refference to get department's name
    $getdept_names = \"SELECT * FROM ost_department WHERE dept_id='$department_id'\";
    $deptresult = mysql_query($getdept_names);
    $dept = mysql_result($deptresult,0,\"dept_name\");

    //look up the staff ID# and then cross reference it to get the staff member's name
    $getstaff_names = \"SELECT * FROM ost_staff WHERE staff_id='$staff_id'\";
    $staffresult = mysql_query($getstaff_names);
    $staff = mysql_result($staffresult,0,\"firstname\");


    // 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><img src='priority/pr_$priority.gif'></td>\"
    .\"<td BGCOLOR=$bgcolour id='openticks-a' nowrap> &nbsp; $name &nbsp; </td><td BGCOLOR=$bgcolour id='openticks-a'>&nbsp;<a href='http://support.coolgear.com/scp/tickets.php?id=$ticket_id' target='_blank'>$subject</a>&nbsp;</td>\"
    .\"<td BGCOLOR=$bgcolour id='openticks-a'> &nbsp; $created_date &nbsp; </td><td BGCOLOR=$bgcolour id='openticks-b'>\"
    .\" &nbsp; $updated_date &nbsp; </td><td BGCOLOR=$bgcolour id='openticks-a'> &nbsp; $dept &nbsp; </td>\"
    .\"<td BGCOLOR=$bgcolour id='openticks-a'> &nbsp; $staff &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>\";
    }
    ?>
  • chaosratt;38906 said:
    I've made some changes that I'd like to contribute back to the group.
    Note: This is embedded into our order processing control panel that only staff see, I would *NOT* recommend showing this to the public.

    image

    <?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 = '<DBPASS>'; // 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 = \"ticket_id, ticketID, staff_id, 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' AND dept_id != 3
    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 width=890 border-color=#BFBFBF border=0 cell-spacing=2 style='font-family: Verdana, Geneva, Arial, Helvetica, sans-serif; font-size: xx-small;'><tr style='background-color: #BFBFBF;' align=center>\";
    echo \"<td id='openticks-a'><b>Priority</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><td id='openticks-b'><b>Assigned To</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
    $ticket_id = mysql_result($result,$i,\"ticket_ID\");
    $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\");
    $staff_id = mysql_result($result,$i,\"staff_id\");

    // if no update say so, and make the date format more human friendly
    if ($updated == '0000-00-00 00:00:00')
    {
    $updated_date = 'No update yet';
    }
    else
    {
    $updated_date = date(\"d-m-y g:iA\", strtotime($updated));
    }

    //make the created date format more human friendly
    $created_date = date(\"d-m-y g:iA\", strtotime($created));


    // look up department and then cross refference to get department's name
    $getdept_names = \"SELECT * FROM ost_department WHERE dept_id='$department_id'\";
    $deptresult = mysql_query($getdept_names);
    $dept = mysql_result($deptresult,0,\"dept_name\");

    //look up the staff ID# and then cross reference it to get the staff member's name
    $getstaff_names = \"SELECT * FROM ost_staff WHERE staff_id='$staff_id'\";
    $staffresult = mysql_query($getstaff_names);
    $staff = mysql_result($staffresult,0,\"firstname\");


    // 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><img src='priority/pr_$priority.gif'></td>\"
    .\"<td BGCOLOR=$bgcolour id='openticks-a' nowrap> &nbsp; $name &nbsp; </td><td BGCOLOR=$bgcolour id='openticks-a'>&nbsp;<a href='http://support.coolgear.com/scp/tickets.php?id=$ticket_id' target='_blank'>$subject</a>&nbsp;</td>\"
    .\"<td BGCOLOR=$bgcolour id='openticks-a'> &nbsp; $created_date &nbsp; </td><td BGCOLOR=$bgcolour id='openticks-b'>\"
    .\" &nbsp; $updated_date &nbsp; </td><td BGCOLOR=$bgcolour id='openticks-a'> &nbsp; $dept &nbsp; </td>\"
    .\"<td BGCOLOR=$bgcolour id='openticks-a'> &nbsp; $staff &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>\";
    }
    ?>
    @chaosratt your idea is cool, could you add some html specific like charset to support UTF-8 for international language
  • newpopnow;40373 said:
    @chaosratt your idea is cool, could you add some html specific like charset to support UTF-8 for international language
    I'm afraid my knowledge of HTML is quite limited. I simply took what I knew and modified ntozier's script to fit my needs.
  • To do that you would want to modify the headers being sent. To do so you would use something like:

    header('Content-Type: text/html; charset=utf-8');


    I THINK (guessing here) that you would want to add that to your \include\client\header.inc.php
  • thanks

    i used this today, thank you.
  • You're very welcome. :)
  • Ntozier, I was looking at this and found your new updated version (http://www.tmib.net/add-client-side-open-ticket-list-osticket-18).  I was wondering how I could sort that table by priority to show the Emergency tickets at the top.  It would make it easier for user's to notice an important update such as a server being down or something similar.  What would be the easiest way to do this?

    Thanks!!
  • Ntozier, I did a lot reading up on views in MySQL and decided to create a view on the fly with all the information that I wanted and sort that view before entering the while loop that fills the table with data.  Thanks!
  • Yes, you could have it sort by priority, but starting with 1.8 priority is no longer stored in the ost_ticket table.  So you would need to query ost_form_entry. (internal ticket id = object_id iirc) and then use the id from that table to look up the priority from ost_form_entry_values.  there id=entry_id.  the field_id in that table is probably = 7, and the value or value_id would be the field you want to sort on.  You could do this whole thing with a fairly (imo) elaborate LEFT JOIN query or by doing multiple selects. [This is not something that I'm real interested for us personally, as I just want to sort on the created date.]

    I'm not real sure why you feel like a view would be the best thing to use in this case.
    Perhaps you can elaborate why you wouldn't just use a query?
  • I was thinking that using a view would make it easier somehow, but in all honestly, you're right.  There's no point.  Using just a query saves one query against the database which makes it just a little faster.  I also joined the ost_form_entry_values table to the ost_ticket_priority table so I could sort by the priority number instead so that it stays in proper order.  Otherwise it would sort alphabetically: Emergency, High, Low, Normal.  Thanks!!
  • Glad to be of assistance.  If you want to send me the changes that you made I certainly wouldn't mind adding them as "optional" customizations to the mod doc. :)
  • edited March 2014

    I just wanted to update this post for the changes I made to be 1.8 compatible. I used ntozier's updated script from his website: http://www.tmib.net/add-client-side-open-ticket-list-osticket-18 and tweaked a few settings to make it display pretty much like my old version.

    Please note that this should *never* be publicly exposed, we use this i-framed in an intra-net application.

    Since I cant seem to post code now, here is a pastebin link:

    http://pastebin.com/VSbw85QE

  • I have updated my version to, but haven't posted it yet. :)

    The new version also includes some admin panel integration as I am actually hoping to merge it with  core.
  • Old topic but I'm looking for exactly this solution for the current version: We're planning to use osTicket internally and want to show all currently open tickets to (logged in and non logged in) users on the welcome landing page. I tried the solution above but it doesn't work anymore. Calling the page alone just show's "There are no tickets open at this time.".

    Maybe this solution is completely obsolete and implemented officially?! Unfortunately the search function here gives me tons of results searching for "show tickets landing page" or smth like that.

    Thanks!!
Sign In or Register to comment.