Number of entries in each category

Duckz · Post by **Duckz** » Wed Sep 05, 2007 2:23 pm

Script URL: Internal
Version of script: 0.94
Hosting company: Internal Webserver
URL of phpinfo.php: Internal
URL of session_test.php: Internal
What terms did you try when SEARCHING for a solution: database query category, number of items in each category

Write your message below:

Hi there

I need a way to list the number of tickets in each category, from a given date.

I can use phpMyAdmin to find the category id's, as well as the number of items in each category, but I need to be able to do this on a monthly basis. Therefore I need to know how many items are new to each category in say the last month.

Can anybody suggest a way of doing this?

Many Thanks

Lee

Post by **Klemen** » Wed Sep 05, 2007 2:41 pm

Try something like this in your phpMyAdmin (or write a script to pefrom the query):

Code: Select all

SELECT hesk_categories.name AS category_name, count( * ) AS number_of_tickets
FROM `hesk_tickets` 
JOIN `hesk_categories` ON hesk_tickets.category = hesk_categories.id
WHERE `dt` 
BETWEEN '2007-08-01'
AND '2007-09-01'
GROUP BY category

To change the dates simply change '2007-08-01' (from date) and '2007-09-01' (to date). Date format is 'YYYY-MM-DD'.

Haven't tested it though, but should work.

Duckz · Post by **Duckz** » Wed Sep 05, 2007 3:23 pm

Thank you that's perfect. Not to push my luck, but would it be possible to add to this query to tell me how many Open?Resolved tickets there are in each category? If so how?

Once again, I'm really greatful

Lee

Post by **Klemen** » Wed Sep 05, 2007 5:01 pm

Well first you need to know what the status numbers mean:
0 = NEW
1 = WAITING REPLY (FROM CUSTOMER)
2 = REPLIED
3 = RESOLVED (CLOSED)

Don't know if you can do what you want in a single SQL query, you will probably have to get summary for each of the category like this:

Code: Select all

SELECT status, count( * ) AS number_of_tickets 
FROM `hesk_tickets` 
WHERE `category` = '1'
AND `dt`
BETWEEN '2007-08-01' 
AND '2007-09-01' 
GROUP BY status

You'd have to do that for each of your categories (replace '1' in `category` = '1' with your category id) and then sum up the results.

rbbigdog · Post by **rbbigdog** » Thu Sep 06, 2007 3:05 am

I can see a nice report page coming....a few drop down choices and away it goes!

Any volunteers?

Post by **Klemen** » Thu Sep 06, 2007 10:23 am

It's on my "todo" list, but the diploma has *a bit* higher priority.

rbbigdog · Post by **rbbigdog** » Sat Sep 08, 2007 1:23 am

I know your very busy Klemen....we all appreciate the script and your time helping us....I was just fishing the forum waters for another taker (I figured there was someone else out here that could do it).

Good luck on that diploma!!

rbbigdog · Post by **rbbigdog** » Sun Sep 09, 2007 1:15 am

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'>&nbsp;".$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'>&nbsp;".$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