First off, I'm no programmer, but I thought I'd see if I could put together a report page. After an hour or so, I quickly realized I was way in over my head with it, but I did get a crude report script to work (with the help of other pieces of scripts from here and there...mostly one script from a guy named Hasin from 2004).
It's completely independent of the hesk scripts, so you need to put in your database information if you want to try it (or better yet, improve on it...you might have to scrap it and start over though....it's nothing special).
It uses 3 files.
Report.php (front page and input selections)
Usage.php (parameters, including database info)
phpReportGen.php (the engine)
Report.php looks like this (for my office):
Code: Select all
<html>
<head>
<title>Priority and Category Report</title>
</head>
<body>
<p><b>Crude HelpDesk Report for Priority and Category Fields based on Entered Dates</b></p><BR>
<form name="Priority Report" action="usage.php" method="POST">
<input type="hidden" name="report_submit" value="1" />
<select name="priority">
<option value="3">Low</option>
<option value="2">Medium</option>
<option value="1">High</option>
</select> Select Priority of Ticket<br /><br />
<input type="hidden" name="category_submit" value="1" />
<select name="category">
<option value="1">Computer Help</option>
<option value="2">Facilities Help</option>
<option value="3">Telephone Help</option>
</select> Select Category of Ticket<br /><br />
<p><input type="date" name="begdate" size="12" maxlength="10" value="2007_08_01"> Beginning Date for Report <font color="red">(format YYYY_MM_DD)</font></p>
<p><input type="date" name="enddate" size="12" maxlength="10" value="2007_09_01"> Ending Date for Report <font color="red">(format YYYY_MM_DD)</font></p>
<br><input type="submit"/>
</form>
</body>
</head>
</html>
You'll have to create your own categories and priorities for the above script.
Usage.php looks like this:
Code: Select all
<html>
<head>
<title>HelpDesk Report Generator</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?php
//Check whether the form has been submitted
if (array_key_exists('report_submit', $_POST)){
$priority = $_POST['priority'];
$category = $_POST['category'];
$begdate = $_POST['begdate'];
$enddate = $_POST['enddate'];
}
?>
<?php
include_once("phpReportGen.php");
$prg = new phpReportGenerator();
$prg->width = "100%";
$prg->cellpad = "0";
$prg->cellspace = "0";
$prg->border = "1";
$prg->header_color = "#465584";
$prg->header_textcolor="#FFFFFF";
$prg->body_alignment = "left";
$prg->body_color = "#D1DCEB";
$prg->body_textcolor = "#000000";
$prg->surrounded = '1';
//$prg->font_name = "Boishakhi";
$link = mysql_connect('localhost', 'user', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname");
$res = mysql_query("select trackid, name, subject, dt, status from hesk_tickets where ((((priority=$priority) AND (category=$category))) AND (dt between ('$begdate') AND ('$enddate')))");
$prg->mysql_resource = $res;
//$prg->title = "Test Table";
$prg->generateReport();
?>
Status Legend: 0=New, 1=Waiting, 2=Replied, 3=Resolved
</body>
</html>
Again, the above script needs your database info.....
phpReportGen.php looks like this (not changed from original in any way):
Code: Select all
<?php
class phpReportGenerator
{
var $mysql_resource;
var $header;
var $foolter;
var $fields = array();
var $cellpad;
var $cellspace;
var $border;
var $width;
var $modified_width;
var $header_color;
var $header_textcolor;
var $header_alignment;
var $body_color;
var $body_textcolor;
var $body_alignment;
var $surrounded;
var $font_name;
function generateReport()
{
$this->border = (empty($this->border))?"0":$this->border;
$this->cellpad = (empty($this->cellpad))?"1":$this->cellpad;
$this->cellspace = (empty($this->cellspace))?"0":$this->cellspace;
$this->width = (empty($this->width))?"100%":$this->width;
$this->header_color = (empty($this->header_color))?"#FFFFFF":$this->header_color;
$this->header_textcolor = (empty($this->header_textcolor))?"#000000":$this->header_textcolor;
$this->header_alignment = (empty($this->header_alignment))?"left":$this->header_alignment;
$this->body_color = (empty($this->body_color))?"#FFFFFF":$this->body_color;
$this->body_textcolor = (empty($this->body_textcolor))?"#000000":$this->body_textcolor;
$this->body_alignment = (empty($this->body_alignment))?"left":$this->body_alignment;
$this->surrounded = (empty($this->surrounded))?false:true;
$this->modified_width = ($this->surrounded==true)?"100%":$this->width;
$this->cellpad = (empty($this->font_name))?"Arial":$this->font_name;
//echo "modified_width : ".$this->modified_width."<br>";
/*if (!is_resource($this->mysql_resource))
/* die ("User doesn't supply any valid mysql resource after executing query result");
/*
* Lets calculate how many fields are there in supplied resource
* and store their name in $this->fields[] array
*/
$field_count = mysql_num_fields($this->mysql_resource);
$i = 0;
while ($i < $field_count)
{
$field = mysql_fetch_field($this->mysql_resource);
$this->fields[$i] = $field->name;
$this->fields[$i][0] = strtoupper($this->fields[$i][0]);
$i++;
}
/*
* Now start table generation
* We must draw this table according to number of fields
*/
echo "<b><i>".$this->header."</i></b>";
echo "<P></P>";
//Check If our table has to be surrounded by an additional table
//which increase style of this table
if ($this->surrounded == true)
echo "<table width='$this->width' border='1' cellspacing='0' cellpadding='0'><tr><td>";
echo "<table width='$this->modified_width' border='$this->border' cellspacing='$this->cellspace' cellpadding='$this->cellpad'>";
echo "<tr bgcolor = '$this->header_color'>";
//Header Draw
for ($i = 0; $i< $field_count; $i++)
{
//Now Draw Headers
echo "<th align = '$this->header_alignment'><font color = '$this->header_textcolor' face = '$this->font_name'> ".$this->fields[$i]."</font></th>";
}
echo "</tr>";
//Now fill the table with data
while ($rows = mysql_fetch_row($this->mysql_resource))
{
echo "<tr align = '$this->body_alignment' bgcolor = '$this->body_color'>";
for ($i = 0; $i < $field_count; $i++)
{
//Now Draw Data
echo "<td><font color = '$this->body_textcolor' face = '$this->font_name'> ".$rows[$i]."</font></td>";
}
echo "</tr>";
}
echo "</table>";
if ($this->surrounded == true)
echo "</td></tr></table>";
}
}
?>
Even though it's not a work of art in anyway, I still thought I would share it (again, maybe it will inspire someone else to make a good one). I'll probably tinker with it when I get a chance again, but won't put too much time into it because I ended up going the wrong direction with it (since I couldn't really intergrate it into Hesk very well).
RB
