osTickets> Reports v5.0

IT’S HERE! REPORTS 6.0 FOR OSTICKET VERSION 1.7

NOTE: If you are running any versions from 2.3 to 4.1 the “Replies per Staff” report is WRONG. I strongly suggest you upgrade to 4.2+

Ok, so after being on the osTicket forum since July 2009 I’ve noticed that one big MOD that everyone wants and never fully gets is reporting. The following is my stab at it.

This MOD has been implemented and tested on 1.6ST and 1.6RC5, please let me know if you run into any issues.

Note: For version 3.3+ you will need to create a scp/reports folder (and make sure its writable by Apache) and place the image (csv.png) into the scp/images folder.

Requirements: MySQL 5
pChart (for use with emailed reports) requires the GD and FreeType PHP extensions.

Note: Reports v2.4+ is compatible with Internet Explorer.

First off you need your actual reports.php page…

English v5.0 [broken link] (Place in your scp/ folder)
Now available in Italiano! Grazie playsoft!
Italiano v3.4 [broken link]

Now you just need to be able to easily browse to it

include/staff/header.inc.php

Replace:

<div id="container">
<div id="header">
<a id="logo" href="index.php" title="osTicket"><img src="images/ostlogo.jpg" width="188" height="72" alt="osTicket"></a>
<p id="info">Welcome back, <strong><?=$thisuser->getUsername()?></strong>
<?php
if($thisuser->isAdmin() && !defined('ADMINPAGE')) { ?>
| <a href="admin.php">Admin Panel</a>
<?}else{?>
| <a href="index.php">Staff Panel</a>
<?}?>
| <a href="profile.php?t=pref">My Preference</a> | <a href="logout.php">Log Out</a></p>

with

<?
// Get the report options
$OptionsQuery = "SELECT viewable from ".REPORTS_TABLE." LIMIT 1";
$OptionsResult = mysql_query($OptionsQuery) or die(mysql_error());
while($graphOptions = mysql_fetch_array($OptionsResult)){?>
<div id="container">
<div id="header">
<a id="logo" href="index.php" title="osTicket"><img src="images/ostlogo.jpg" width="188" height="72" alt="osTicket"></a>
<p id="info">Welcome back, <strong><?=$thisuser->getUsername()?></strong>
<?php
if($thisuser->isAdmin() && !defined('ADMINPAGE')) { ?>
| <a href="admin.php">Admin Panel</a> | <a href="reports.php">Reports</a>
<?}else{?>
| <a href="index.php">Staff Panel </a> <? if((!$thisuser->isadmin() && !$thisuser->isManager() && $graphOptions['viewable']=='staff') ||
(!$thisuser->isadmin() && $thisuser->isManager() && (($graphOptions['viewable']=='managers') || ($graphOptions['viewable']=='staff'))) ||
$thisuser->isadmin())
{?> | <a href="reports.php">Reports</a> <?}?>
<?}?>
| <?="$isdeptmanager";?><a href="profile.php?t=pref">My Preference</a> | <a href="logout.php">Log Out</a></p>
<?}?>

In order to use the “Report Settings” (admin) page then you’ll need to add a new table to your database and then fill it with initial data, make sure and change the table prefix (ost_) if you are not using the default.

CREATE TABLE `ost_reports` (
`3d` TINYINT NOT NULL ,
`graphWidth` INT NOT NULL ,
`graphHeight` INT NOT NULL ,
`resolution` VARCHAR( 255 ) NOT NULL ,
`viewable` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;

INSERT INTO `ost_reports` (`3d`, `graphWidth`, `graphHeight`, `resolution`, `viewable`) VALUES ('1', '400', '240', 'hours', 'admins');

Add the REPORTS_TABLE constant to
main.inc.php
below

define('GROUP_TABLE',TABLE_PREFIX.'groups');

add

define('REPORTS_TABLE',TABLE_PREFIX.'reports');

include/class.ticket.php
remove staff_id=0 from the following line (If you do not do this tickets will be unassigned once they are closed and reports regarding staff will be inaccurate):

$sql= 'UPDATE '.TICKET_TABLE.' SET status='.db_input('closed').',isoverdue=0,duedate=NULL,updated=NOW(),closed=NOW() '.

ToDo

• Scheduled email reports
• Average time to initial response.
• Sorting by column title
• Language Support

Changelog