Hello, I am trying to pull out the following values programatically for reporting purposes. I am sure having access to this information will be of use to others:

* The total number of all tickets
* The total number open tickets
* The total number of closed tickets
* Total number of users who can be assigned tickets
* Total number of open tickets per user

Help would be greatly appreciated.

Comments

NToronto’s picture

NToronto commented

To expand on this issue - I am looking to integrate some reporting features and charts into this Support module. I would like to be able to see easily stats such as the total number of all tickets, the total number of open tickets per user, etc. I am looking for guidance on the best way to calculate these values from the database...

jeremy’s picture

jeremy commented
Category: support » feature

I hope you will be contributing this functionality back? I think the best thing would be to create a second module, named something like "support_report" where this functionality lives. It can either be its own project, or it can be included in this project. If it has minimal dependencies than I'm fine including it with the core project. If it has a lot of dependencies, then it should live as an external project.

As for your specific questions:

  • The total number of all tickets

    Tickets are nodes, so it's as simple as "SELECT COUNT(*) FROM node WHERE TYPE = 'support_ticket'"

  • The total number open tickets

    This information is stored in another table. Assuming you've not created custom states, this information can be retrieved with the following query: "SELECT COUNT(*) FROM support_ticket WHERE state IN (1, 2, 3)"

    To do it correctly however, supporting custom states, you really should do: "SELECT COUNT(t.nid) FROM support_ticket t LEFT JOIN support_states s ON t.state = s.sid WHERE s.isclosed = FALSE"

  • The total number of closed tickets

    The wrong way to do it is: "SELECT COUNT(*) FROM support_ticket WHERE state = 4"

    The correct way to do it is: "SELECT COUNT(t.nid) FROM support_ticket t LEFT JOIN support_states s ON t.state = s.sid WHERE s.isclosed = TRUE"

  • Total number of open tickets per user

    "SELECT COUNT(t.nid) FROM support_ticket t LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE s.isclosed = FALSE AND uid = %d" WHERE uid is the user you want the stats for.

    Or, to get these stats for all users in one query: "SELECT n.uid, COUNT(t.nid) AS count FROM support_ticket t LEFT JOIN support_states s ON t.state = s.sid LEFT JOIN node n ON t.nid = n.nid WHERE s.isclosed = FALSE GROUP BY uid"

  • Total number of users who can be assigned tickets

    This is a little more complicated, and I'm too distracted at the moment to offer you a solution. But if you create a module that accomplishes the above you're very much headed in the right direction and I'll happily contribute a patch to accomplish this one too... :)

jeremy’s picture

jeremy commented
Status: Active » Fixed

Version 1.2 will include the new support_charts module which uses the Google chart API to display some basic statistics:
http://drupal.org/cvs?commit=261230

Currently supported statistics include:

  • Open tickets (pie chart)
  • Open tickets per state (pie chart)
  • Open tickets per priority (pie chart)
  • Open tickets per client (pie chart)
  • Assigned open tickets (pie chart)
  • Tickets created this month (line chart)
  • Tickets updated this month (line chart)
  • Tickets closed this month (line chart)

All of these charts should be made more configurable (ie, so you can run a report on a specific client, or for a specific time period), but this is a good starting point. Any specific feature requests for the charting ability should be added as followup issues.

NToronto’s picture

NToronto commented

That's great news!

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

NToronto’s picture

NToronto commented
Status: Closed (fixed) » Needs review

The release notes says that Support requires the Charts Module.

There are actually two modules which go by the name of Charts:
http://drupalmodules.com/module/chart-api
http://drupalmodules.com/module/charts

It may be a good idea to clarify in the documentation which module is required.

* On the topic of naming, I understand that your module was renamed from JobTrack to Support. However, have you ever tried to search for the module using, "Drupal Support". or "Support for Drupal". It is almost like naming your company "Company" and expecting people to be able to search for it. The module name is way too generic and should be changed to anything unique such as, "SupportTickets", "Supportastic", etc.

jeremy’s picture

jeremy commented
Status: Needs review » Fixed

Good point. I've added documentation regarding which chart module is required, as well as a link from the main project page.
http://drupal.org/cvs?commit=271852

I like the idea behind the "charts" module and being able to use multiple charting backends, but it is very much alpha quality -- it's missing key features and charting frequently breaks. I selected the "chart" module as it has proven to be much more stable. Fortunately the APIs are similar, so it would not be difficult to switch the dependency at a later time if the "charts" module stabilizes into a usable state.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.