Anyone view all open tickets

ist

Hi,

I want to implemenet osTicket for a school. There current propitiatory system allows anyone to view all currently open/in progress before submitting a new ticket.

The logic behind this is that staff and students dont submit duplications and can see if an issue they are having is being dealt with

Does osTicket support this?

Thanks

IST

sherwin_flight

This should be possible with some modifications. The system uses an e-mail address and a ticket number for authentication. It should be possible to change it around a bit and hard code the login info into the page.

It should actually be pretty easy to come up with some way to address this issue.

ntozier

We use a sort of "status" page so that people can see what tickets are open prior to opening a ticket. We accomplished this by adding some code to the site/index.php near the bottom to include another file.

<div class="clear"></div>

<br />

<hr />

Before opening a new ticket for site outages, etc. Please review the open tickets beflow to ensure that a ticket is not already open.<br />

<? include('display_open_topics.php'); ?>

</div>

<br />

before

<?require(CLIENTINC_DIR.'footer.inc.php'); ?>

and then crafted a quick and dirty file called display_open_topics.php in that same directory. Here are the contents of the file.

<?php

/*********************************************************************

display_open_topics.php

Displays a block of the last X number of open tickets.

Neil Tozier <iamtmib@gmail.com>

For use with osTicket (http://www.osticket.com)

Released under the GNU General Public License WITHOUT ANY WARRANTY.

See LICENSE.TXT for details.

**********************************************************************/

//include_once('./include/settings.php');

# configure this area with your database connection information

$dbhost = 'localhost';

$dbname = 'YourDatabaseName';

$dbuser = 'YourDBUser';

$dbpass = 'YourPassword';

// The columns that you want to collect data for from the db

$columns = "name, subject, created, updated, agency, priority_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";

//mysql_connect($dbhost,$dbuser,$dbpass);

//@mysql_select_db($dbname) or die( "DB Error: Unable to select database");

$result=mysql_query($query);

$num = mysql_numrows($result);

if ($num >> 0) {

// table headers, if you add or remove columns edit this

echo "<br><table border-color=#CC9999 border=1 align=center cell-spacing=2><tr align=center style='background-color: #CC9999;'>";

echo "<td><b>Name</b></td><td><b>Site</b></td><td><b>Issue</b></td><td><b>Opened on</b></td><td><b>Last Update</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
 $name = mysql_result($result,$i,"name");
 $subject = mysql_result($result,$i,"subject");
 $created = mysql_result($result,$i,"created");
 $updated = mysql_result($result,$i,"updated");
 $agency = mysql_result($result,$i,"agency");
 $priority  = mysql_result($result,$i,"priority_id");
   // look up site/agency and display proper name
  // mysql query.
  $getsite = "SELECT agency FROM ost_agencies WHERE id = $agency";
  $siteresult=mysql_query($getsite);
  $site = mysql_result($siteresult,0,"agency");
	//mysql_close();
  //populate the table with data
 echo "<tr align=center><td> &nbsp; $name &nbsp; </td><td> &nbsp; $site &nbsp; </td><td> &nbsp; $subject ($priority) &nbsp; </td>"
     ."<td> &nbsp; $created &nbsp; </td><td> &nbsp; $updated &nbsp; </td></tr>";
  	++$i;
}echo "</table>";
}else {
 echo "<br><center><font color=red><b>There are no tickets open at this time.</b></font></center><br>";
}

?>

Note this will probably not work out of the box for you. We have a couple of custom fields that we use in tickets. But it should provide you with an idea of how to achieve your goal.

bc28001

need help please

guys i have been trying to used the code mention on this topic. and i understand all off the steps but at the end is not working it does not pull any information from the database.

i can see the values and the connection setting for the database but it always saids.

There are no tickets open at this time.

and there is i check and i check the status on the database.

is there a possibility any one an help and probably provide a more complete code for the page display_open_topics.php

any information will greatly appreciated it please.

thanks :)::

jgrossman

Agreed

I get the same results as bc28001. It has problems with the code so it shows the else statement. I removed the reference to the columns you have that we do not but keep getting the same results. This would be a great piece of code to get working. Would love some help with this one.

bc28001

totally agree

Yes this is a great peace of code and is exactly what we are looking for.. i wish i will know just a little more about coding and i wish i could get this fixed for you guys.

hope some with just a little more knowledge can help us out on this matter.

thanks guys.

:):(:

bc28001

question

one thing that i notice is that it seem that is not connecting to the database at all that is why is not doing anything.

what would be the code to display in case of not connecting successfully .??:(

bc28001

getting close

ok guys im getting closer.

the problem is here

$query = "SELECT $columns

FROM ost_ticket

WHERE status = 'open'

ORDER BY created DESC

LIMIT 0,$limit";

i think hehehe at least something is coming up.

this is what i used.

// mysql query. The columns tha

$query = "SELECT status = 'open' From ost_ticket

LIMIT 0,$limit";

i need help heheheheh

bc28001

Got it

Guys got it to work .. is perfect.. im still working some bugs but is working.

<?php

/*********************************************************************

display_open_topics.php

Displays a block of the last X number of open tickets.

Neil Tozier <iamtmib@gmail.com>

For use with osTicket (http://www.osticket.com(http://www.osticket.com))

Released under the GNU General Public License WITHOUT ANY WARRANTY.

See LICENSE.TXT for details.

**********************************************************************/

//include_once('./include/settings.php');

# configure this area with your database connection information

$dbhost = '**************';

$dbname = '**************';

$dbuser = '**************';

$dbpass = '**************';

// The columns that you want to collect data for from the db

$columns = "name, subject, created, updated, ticketID, priority_id";

// The maximum amount of open tickets that you want to display.

$limit ='100';

// mysql query. The columns tha

$query = "SELECT status = 'open', name, subject, created, updated, ticketID

From ost_ticket

ORDER BY created DESC

LIMIT 0,$limit";

//mysql_connect($dbhost,$dbuser,$dbpass);

//mysql_select_db($dbname) or die( "DB Error: Unable to select database");

$result=mysql_query($query);

$num = mysql_numrows($result);

if ($num >> 0) {

// table headers, if you add or remove columns edit this

echo "<br><table border-color=#CC9999 border=1 align=center cell-spacing=2><tr align=center style='background-color: #CC9999;'>";

echo "<td><b>Ticket</b></td><td><b>Name</b></td><td><b >Issue</b></td><td><b>Opened on</b></td><td><b>Last Update</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
 $name = mysql_result($result,$i,"name");
 $subject = mysql_result($result,$i,"subject");
 $created = mysql_result($result,$i,"created");
 $updated = mysql_result($result,$i,"updated");
 $ticketID = mysql_result($result,$i,"ticketID");
 $priority  = mysql_result($result,$i,"priority_id");
   // look up site/ticketID and display proper name
  // mysql query.
  //$getsite = "SELECT ticketID FROM ost_agencies WHERE id = $ticketID";
  //$siteresult=mysql_query($getsite);
  //$site = mysql_result($siteresult,0,"ticketID");
	//mysql_close();
  //populate the table with data
 echo "<tr align=center><td>$ticketID &nbsp; &nbsp; </td><td> &nbsp; $site &nbsp;$name </td><td> &nbsp; $subject $priority &nbsp; </td>"
     ."<td> &nbsp; $created &nbsp; </td><td> &nbsp; $updated &nbsp; </td></tr>";
  	++$i;
}echo "</table>";
}else {
 echo "<br><center><font color=red><b>There are no tickets open at this time.</b></font></center><br>";
}

?>

bc28001

finally guys

Guys i got it to work please let me know if you need the code i dont now how to posted here.

<?php

/*********************************************************************

display_open_topics.php

Displays a block of the last X number of open tickets.

Neil Tozier

For use with osTicket (http://www.osticket.com)

Released under the GNU General Public License WITHOUT ANY WARRANTY.

See LICENSE.TXT for details.

**********************************************************************/

//include_once('./include/settings.php');

# configure this area with your database connection information

$dbhost = '************';

$dbname = '************';

$dbuser = '************';

$dbpass = '************';

// The maximum amount of open tickets that you want to display.

$limit ='100';

// mysql query. The columns tha

$query = "SELECT status = 'open', name, subject, created, updated, ticketID, priority

From ost_ticket

NATURAL JOIN ost_ticket_priority

ORDER BY created DESC

LIMIT 0,$limit";

//mysql_connect($dbhost,$dbuser,$dbpass);

//mysql_select_db($dbname) or die( "DB Error: Unable to select database");

$result=mysql_query($query);

$num = mysql_numrows($result);

if ($num >> 0) {

// table headers, if you add or remove columns edit this

echo "";

echo "TicketFromSubject (Issue)Date and Time: Last UpdatePriority";

$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");

$ticketID = mysql_result($result,$i,"ticketID");

$priority = mysql_result($result,$i,"priority");

// look up site/ticketID and display proper name

// mysql query.

//$getsite = "SELECT ticketID FROM ost_agencies WHERE id = $ticketID";

//$siteresult=mysql_query($getsite);

//$site = mysql_result($siteresult,0,"ticketID");

//mysql_close();

//populate the table with data

echo "$ticketID $site $name $subject "

." $created $updated $priority ";

++$i;

}

echo "";

}

else {

echo "There are no tickets open at this time.";

}

?>:):

jgrossman

Much Better guessing show only open is left

So i took the code that you did post and tested it. I was able to have it now show a list of tickets but it does not seem to honor the only open list. It seems to show all of them regardless if they are open or closed. Can you confirm that this is what you are seeing?

bc28001

mmmm

: oh for real i have not check that part. let me look in to it do a couple of more test and get back to you on that. if possible you or anyone else can figure it out before me let me know im real close and im not a code but at least is there.

sorry for that part i will check anyway . thanks for the imput

jgrossman

Simplified Code

Ok I have a simplified version that works. I have taken out things that I dont need and just simplified the connection code to simply list the open tickets and show the ticket, name, subject, and created.

<?php

$con = mysql_connect("localhost","*****","******");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("*****", $con);

$result = mysql_query("SELECT * FROM ost_ticket WHERE status='open'");

echo "<table border='1'>

<tr border-color=#CC9999 border=1 align=center cell-spacing=2><tr align=center style='background-color: #ccc; border: 1px solid #999;'>

<th>Ticket</th>

<th>From</th>

<th>Subject</th>

<th>Date Submitted</th>

</tr>";

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td>" . $row . "</td>";

echo "</tr>";

}

echo "</table>";

mysql_close($con);

?>

But there are defenetly things that I would like to have on there that I dont know how to do. I would like to format the date in an easier to read format. Anybody know how to do that? Also I would like to have who the ticket is assigned to. Anybody know how to do that?

But this code does the basic job of showing just the open tickets in a table. Anybody out there know how to add the extra features I would be most appreciative!

bc28001

filter

Hi did you ever managed to control the filter between one or closed ticket. ???

jgrossman

Yes kind of

The code i posted right before your post will show only open tickets. But it has a lot of the features stripped out. Actually all i did was go to w3school and learn how to list info from the database and copy their solution. I am not a programmer. I can hack and read others code but I need someone who can program better than me to come up with a more elequant solution. The code above shows the ticket, subject, name, create date. But i would love to have the date displayed in an easier to read format.. I would also love to know how to show who the ticket has been assigned to.. Both those things are eluding me. If anyone knows how to do it please chime in!!!!!:)

But like i said the simple code does show only open tickets and displays them in a table.. So its almost perfect for waht I want.

bc28001

oh i understand know ok i will take this code and review it and see how we can used it to our advantage.. im in the same level as you i guess we are the only two interested on this to work hehehe either way thanks ..