Project Open Custom E-Mail Notifications - Part 1

Project Open has a powerful, intricate, full-featured e-mail notification system, complete with opt-out functionality.

I hate to admit it, but I could not understand how to make it work.

True, I figured out how to tweak the e-mails Project Open sends when you open a new help desk ticket -- a technique, by the way, which also works with the e-mails Project Open generates when you edit a ticket.

But after more hours than I care to admit, and after too many questions like this one posted to the Source Forge forum, I had to come to the conclusion that I just wasn't smart enough to make it wrork.

So, I built my own, and I thought it might help you if I shared my experience. I want to make it clear that I am in no way suggesting that what's in Project Open isn't completely functional. Plus, I'm not suggesting the developers dropped the ball. I'm convinced their solution is elegant and useful.

It was just beyond me.

I've targeted my solution specifically at tickets that go through a workflow that's defined using the basic or advanced workflow editor. I've tested it with Project Open 3.4.x and 3.5.x.

You probably already know that entering a ticket creates a row in im_tickets. To send e-mails, though, we won't even look at im_tickets. We'll look at the tables used to track a ticket's progress through a workflow. Those tables are:

1. wf_tasks: Holds the individual steps of a workflow. They are added as a ticket moves through each step in its workflow.
2. wf_cases: Tracks step types and focuses the scope of the query against wf_tasks.
3. wf_workflows: Defines the workflow and lets us get the human friendly title for the workflow.
4. wf_transitions: Defines the names of the workflow steps and lets us get the human friendly name of the workflow step.
5. wf_user_tasks: Gives us a way to focus the query against the table parties so we can get the e-mail address.
6. parties: Holds the assigned individual(s) e-mail address(es).

With these tables, we can know what workflows have open steps. We can also figure out what e-mail addresses need to be notified.

We can build this query using the tables enumerated above:


SELECT a.task_id, a.workflow_key, a.transition_key, b.*, c.description, d.transition_name, acs_object__name(e.user_id) AS thename,
f.email
FROM wf_tasks a, wf_cases b, wf_workflows c, wf_transitions d, wf_user_tasks e, parties f
WHERE b.case_id = a.case_id
AND c.workflow_key = a.workflow_key
AND d.workflow_key = a.workflow_key
AND d.transition_key = a.transition_key
AND e.task_id = a.task_id
AND f.party_id = e.user_id
AND a.state = 'enabled'
ORDER BY task_id DESC


This query will display every task that's enabled -- that is to say, every task that's ready to be worked on.

In Part 2, I"ll present a query showing the same kind of information for the items that have closed. That will allow us to send an e-mail as the ticket both moves through the workflow and gets closed.