Reports

[trancer01]

IS there any way to get a report on the closed/open tickets? Say we want to know how many tickets we closed per month and how long it took us to close them, is this possible?

[JayDee]

The best i have come up with at the moment is exporting the files to a CSV file using a php script.

All this does is pulls the direct from the MySQL database. I placed a link to a couple of scripts in the header of the helpdesk.

Can't remember where i got it from and i can in no way take credit for the code depicted below.


<?php
$host = 'localhost';
$user = '%SQLUsername%';
$pass = '%SQLPassword%';
$db = '%database name%';
$table = 'ost_ticket';
$file = 'closedTicketReport';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].",";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table." WHERE status = 'closed'");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].",";
}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

Still working out how to reduce it to one month of records but at the moment just using MS excel (shudders) to filter out everything that isn't the current month, and from there I can make pretty graphs and stuff from there.

Hope that helps.

Cheers

Jay

[Corey]

Can't remember where i got it from and i can in no way take credit for the code depicted below.

Jay

http://www.tutorial5.com/content/view/159/85/

For anyone else having problems with it or need help with adding things to that script, you can use that link and they will be able to help you.

[JayDee]

Yeah that's the one.

Thanks Corey.

[uuys]

I have copied code into closereport.php on the upload director. Running it gives me the database output in the browser, how do I get the file downloaded to a csv file to inport into excel.

Still new to osticket and php

thanks

[JayDee]

Try right clicking the link and "Save taget as" or somthing of the like.

[TheBigChief]

I tweeked this a little and it allowed me to see department name's rather than ID's and also Staff Name's rather than ID's.

<?php
$host = 'localhost';
$user = 'user';
$pass = 'password';
$db = 'test_db';
$table = 'ost_ticket';
$file = 'closedTicketReport';


$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die ("Can not connect.");

$csv_output = "Ticket ID, Department, Priority, Staff Firstname, Staff Surname, Email, Name, Subject, Phone, Status, Source, Reopened, Closed, Created";

$csv_output .= "\n";

$query = "SELECT ost_ticket.ticket_id, ost_department.dept_name, ost_ticket.priority_id, ost_staff.firstname, ost_staff.lastname, ost_ticket.email, ost_ticket.name, ost_ticket.subject, ost_ticket.phone, ost_ticket.status, ost_ticket.source, ost_ticket.reopened, ost_ticket.closed, ost_ticket.created
FROM ost_ticket, ost_staff, ost_department
WHERE ost_staff.dept_id = ost_department.dept_id
AND ost_ticket.staff_id = ost_staff.staff_id";

$result = mysql_query($query);

if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_row($result)) {
// where you see 14 below this is the ammount of items selected in the query above
for ($j = 0; $j < 14; $j++) {
$csv_output .= $row[$j]. " ,";
}
$csv_output .= "\n";
}
}

$filename = $file."_".date("Y-m-d_H-i",time());

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");

print $csv_output;
exit;

?>

I also added this to the SCP tickets.php file to allow me to click to export:

$nav->addSubMenu(array('desc'=>'New Ticket','href'=>'tickets.php?a=open','iconclass'=>'newTicket'));

$nav->addSubMenu(array('desc'=>'Export','href'=>'closedTicketReport.php?a=open','iconclass'=>'attachment'));

//Render the page...

[AdamEIS]

got no values :(

[TheBigChief]

got no values :(

Remember to change the section at the top to match what you have:

<?php
$host = 'localhost';
$user = 'user';
$pass = 'password';
$db = 'test_db';
$table = 'ost_ticket';
$file = 'closedTicketReport';


Also you need to ensure that when you log into the admin panel you can see tickets otherwise it wont work.

[trancer01]

So JayDee, (or anyone)
which files are you modifying/adding this code into? or is it a new file you created and are putting somewhere?

Thanks

The best i have come up with at the moment is exporting the files to a CSV file using a php script.

All this does is pulls the direct from the MySQL database. I placed a link to a couple of scripts in the header of the helpdesk.

Can't remember where i got it from and i can in no way take credit for the code depicted below.


<?php
$host = 'localhost';
$user = '%SQLUsername%';
$pass = '%SQLPassword%';
$db = '%database name%';
$table = 'ost_ticket';
$file = 'closedTicketReport';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].",";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table." WHERE status = 'closed'");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].",";
}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

Still working out how to reduce it to one month of records but at the moment just using MS excel (shudders) to filter out everything that isn't the current month, and from there I can make pretty graphs and stuff from there.

Hope that helps.

Cheers

Jay

[JayDee]

Hi trancer01

I think you'll find this somewhere above mate ;)



I also added this to the SCP tickets.php file to allow me to click to export:

$nav->addSubMenu(array('desc'=>'New Ticket','href'=>'tickets.php?a=open','iconclass'=>'newTicket'));

$nav->addSubMenu(array('desc'=>'Export','href'=>'closedTicketReport.php?a=open','iconclass'=>'attachment'));

//Render the page...

I have also been working on a whole Reporting panel, I would post the source but not sure how many people it would apply to. I have made a fair amount of Database Schema changes that i'm not sure everybody wants. If anyone's interested I don't mind sending my whole 'flavoured' osTicket out to the masses. Feel free to send us a message.

[funspanishclass]

I would be very interested in the reporting panel.

THANKS!!

[armitage]

Me too Buddy!

cheers
;)

[JayDee]

No worries,

Once I get some time i will clean it up and open a new thread for it. Flat out right now so could be another month but i will get it up!

Cheers,

./Jay

[trancer01]

Thanks Jay,
Let me just make sure I completely understand this before I go to implement it on our test server;
The main section of text I create a PHP page with that conent, then add the line (below) into tickets.php?

Do I need to include that line in a specific spot? or just in the general body (obviously not in an existing function).

Thanks much!

Hi trancer01

I think you'll find this somewhere above mate ;)



I have also been working on a whole Reporting panel, I would post the source but not sure how many people it would apply to. I have made a fair amount of Database Schema changes that i'm not sure everybody wants. If anyone's interested I don't mind sending my whole 'flavoured' osTicket out to the masses. Feel free to send us a message.

[trancer01]

OK, ignore the above post, I got it.

What I don't understand is how to tell it to grab only the closed tickets for the past month...

When I export, it only gives me 10 of the 20+ tickets we currently have OPEN....

Did I miss something?

Thanks =)

[JayDee]

If you can show me the script your using I might beable to help. just need to massarge the sql statement into what you need from it.

[trancer01]

If you can show me the script your using I might beable to help. just need to massarge the sql statement into what you need from it.

I just used yours on the first page.

[dajaes]

Has anyone taken this mod any further? It would be great to have a breakdown of closed tickets per staff member, per month. I'd try it myself but PHP isnt my specialty.
________
SL175 (http://www.cyclechaos.com/wiki/Honda_SL175)

[JayDee]

Hey hey, all done on my work with reports, I have opened a new thread.

http://osticket.com/forums/showthread.php?t=1537

[RobertC]

Is there a way to have the report emailed instead? Please help!
:eek: