Ticket Reports

msupczenski · Feb 10, 2012

I know there are threads on reporting but there not what I am looking for.

I need a report that I can choose from a spacific time period that shows everything on the ticket. I need the ticket number, open time, close time, my additional items such as camera number and ext... I have made a simple php page that pulls data from ost_ticket table but i alse need data from ost_ticket_message, ost_ticket_note, and ost_ticket_response. Now I don't need anything fancy but I do now know how to use the primary key (ticket_id) to show only that info on that spacific table row. it turns into a mess. Can anyone please help me. I have been trying to get this for weeks on my own. I need you now.:)

msupczenski · Feb 10, 2012

This is what I have right now but I need the other data as stated in the first post. What should I do?

<?php

require('staff.inc.php');

//We are only showing landing page to users who are not logged in.

if($thisclient && is_object($thisclient) && $thisclient->isValid()) {

require('tickets.php');

exit;

}require(STAFFINC_DIR.'header.inc.php');
$con = mysql_connect("localhost","root","Password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("ticket", $con);
$result = mysql_query("SELECT * FROM ost_ticket");
echo "
<br>
<br>
<center>
<table border='1'>
<tr>
<th><p align=center>Status</th>
<th><p align=center>Created</th>
<th><p align=center>Closed</th>
<th><p align=center>Ticket</th>
<th><p align=center>Help Topic</th>
<th><p align=center>Camera #</th>
<th><p align=center>Camera Issue</th>
<th><p align=center>NVR #</th>
<th><p align=center>NVR Issue</th>
<th><p align=center>Access Control Issue</th>
<th><p align=center>Console Issue</th>
</tr>";
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "</tr>";
  }
echo "</table>";
mysql_close($con);
require(CLIENTINC_DIR.'footer.inc.php');

?>

dan55304 · Feb 11, 2012

Need similar for search results

I would like something similar but really just want a summary:

* Date

* Subject

* Message

I would like to generate this report AFTER doing a search. In other words, only on the tickets in the search results.

I also could not find anything similar in Mods. Most reports seem to be interested in statistics which I don't need.

Any ideas how this can be done?

msupczenski · Feb 11, 2012

Dan,

I am working on developing one but it might be a little bit but I will update this thread of any updates. I could use some help though, I am not a very novis programmer.

I would like something similar but really just want a summary:
* Date
* Subject
* Message
I would like to generate this report AFTER doing a search. In other words, only on the tickets in the search results.
I also could not find anything similar in Mods. Most reports seem to be interested in statistics which I don't need.
Any ideas how this can be done?

msupczenski · Feb 14, 2012

this is what i got so far. It's not connected to osticket yet but this will take the data from sql then put in on display. There is a lot of custom items such as camera and nvr but that for out system. If you remove those and use the data from the database that you want it works great. If I get a better version Ill keep you up to date.

<html>

<head>

<title>Ticket Export Site</title>

</head>

<body>

<br>

<table border="1" cellspacing="0" id="tickets_table">

<tr>

<? $todo=$_POST;

if(isset($todo) and $todo=="submit"){

$month=$_POST;

$month1=$_POST;

$day=$_POST;

$day1=$_POST;

$year=$_POST;

$year1=$_POST;

$date_value="$year-$month-$day";

$date_value1="$year1-$month1-$day1";

}?>
<form method=post name=f1 action=''><input type=hidden name=todo value=submit>
<td>
Starting Date:
<select name=month value=''>Select Month</option>
<option value='01'>January</option>
<option value='02'>February</option>
<option value='03'>March</option>
<option value='04'>April</option>
<option value='05'>May</option>
<option value='06'>June</option>
<option value='07'>July</option>
<option value='08'>August</option>
<option value='09'>September</option>
<option value='10'>October</option>
<option value='11'>November</option>
<option value='12'>December</option>
</select>
</td>
<td>
Date<select name=day>
<option value='01'>01</option>
<option value='02'>02</option>
<option value='03'>03</option>
<option value='04'>04</option>
<option value='05'>05</option>
<option value='06'>06</option>
<option value='07'>07</option>
<option value='08'>08</option>
<option value='09'>09</option>
<option value='10'>10</option>
<option value='11'>11</option>
<option value='12'>12</option>
<option value='13'>13</option>
<option value='14'>14</option>
<option value='15'>15</option>
<option value='16'>16</option>
<option value='17'>17</option>
<option value='18'>18</option>
<option value='19'>19</option>
<option value='20'>20</option>
<option value='21'>21</option>
<option value='22'>22</option>
<option value='23'>23</option>
<option value='24'>24</option>
<option value='25'>25</option>
<option value='26'>26</option>
<option value='27'>27</option>
<option value='28'>28</option>
<option value='29'>29</option>
<option value='30'>30</option>
<option value='31'>31</option>
</select>
</td>
<td>
Year(yyyy)<input type=text name=year size=4 value=2012>
</td>
</tr>
<tr>
<td>
Ending Date:
<select name=month1 value=''>Select Month</option>
<option value='01'>January</option>
<option value='02'>February</option>
<option value='03'>March</option>
<option value='04'>April</option>
<option value='05'>May</option>
<option value='06'>June</option>
<option value='07'>July</option>
<option value='08'>August</option>
<option value='09'>September</option>
<option value='10'>October</option>
<option value='11'>November</option>
<option value='12'>December</option>
</select>
</td>
<td>
Date<select name=day1 >
<option value='01'>01</option>
<option value='02'>02</option>
<option value='03'>03</option>
<option value='04'>04</option>
<option value='05'>05</option>
<option value='06'>06</option>
<option value='07'>07</option>
<option value='08'>08</option>
<option value='09'>09</option>
<option value='10'>10</option>
<option value='11'>11</option>
<option value='12'>12</option>
<option value='13'>13</option>
<option value='14'>14</option>
<option value='15'>15</option>
<option value='16'>16</option>
<option value='17'>17</option>
<option value='18'>18</option>
<option value='19'>19</option>
<option value='20'>20</option>
<option value='21'>21</option>
<option value='22'>22</option>
<option value='23'>23</option>
<option value='24'>24</option>
<option value='25'>25</option>
<option value='26'>26</option>
<option value='27'>27</option>
<option value='28'>28</option>
<option value='29'>29</option>
<option value='30'>30</option>
<option value='31'>31</option>
</select>
</td>
<td>
Year(yyyy)<input type=text name=year1 size=4 value=2012>
</td>
</tr>
<tr>
<td>
<input type=submit value=Submit>
</td>
</form>
</table>
<?php
 $con = mysql_connect("localhost","root","Password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("ticket", $con);
$result = mysql_query("SELECT ost_ticket.status, ost_ticket.created, ost_ticket.closed, ost_ticket.ticketID, ost_ticket.helptopic, ost_ticket.camera, ost_ticket.camiss, ost_ticket.nvr, ost_ticket.nvriss, ost_ticket.access, ost_ticket.console, ost_ticket_message.message, ost_ticket_note.note, ost_ticket_response.response
FROM ost_ticket 
LEFT JOIN ost_ticket_message
ON ost_ticket.ticket_id=ost_ticket_message.ticket_id
LEFT JOIN ost_ticket_note
ON ost_ticket.ticket_id=ost_ticket_note.ticket_id
LEFT JOIN ost_ticket_response
ON ost_ticket.ticket_id=ost_ticket_response.ticket_id
WHERE ost_ticket.created BETWEEN '$date_value' AND '$date_value1'");
echo "
Starting Date: $date_value
<br>
Ending Date: $date_value1
<br>
<br>
<center>
<table border='1'>
<tr>
<th><p align=center>Status</th>
<th><p align=center>Created</th>
<th><p align=center>Closed</th>
<th><p align=center>Ticket</th>
<th><p align=center>Help Topic</th>
<th><p align=center>Camera #</th>
<th><p align=center>Camera Issue</th>
<th><p align=center>NVR #</th>
<th><p align=center>NVR Issue</th>
<th><p align=center>Access Control Issue</th>
<th><p align=center>Console Issue</th>
<th><p align=center>Message</th>
<th><p align=center>Notes</th>
<th><p align=center>Response</th>
</tr>";
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><b><p align=center>&nbsp" . $row . "</b></td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p align=center>&nbsp" . $row . "</td>";
  echo "<td><p aligh=center>&nbsp" . $row . "</td>";
  echo "</tr>";
  }
echo "</table>";
mysql_close($con);
?>
<br>
</body>

</html>

Ticket Reports

Mmsupczenski

Mmsupczenski

Ddan55304

Mmsupczenski

Mmsupczenski