Project Open 3.5.x

Recent Activity Query

From time to time, I want to see a snapshot of Project Open activity. Specifically, I wanted to see ticket creation and forum posting activity. So, I created a report with this query:

SELECT 'Forum' AS thetype, a.posting_date AS datecreated, a.subject, a.message, b.first_names, b.last_name
FROM im_forum_topics a, persons b
WHERE b.person_id = a.owner_id
AND DATE_PART('day', NOW() - a.posting_date) < 30
SELECT 'Ticket' AS thetype, a.ticket_creation_date AS datecreated, c.project_name AS subject, a.ticket_description AS message, b.first_names, b.last_name
FROM im_tickets a, persons b, im_projects c
WHERE b.person_id = a.ticket_customer_contacT_id
AND c.project_id = a.ticket_id
AND DATE_PART('day', NOW() - a.ticket_creation_date) < 30
ORDER BY datecreated DESC

No doubt I’ll tweak this as I find issues. I’ll probably use the techniques listed in this article to make the individual items “clickable.” But for now, I thought this might be help.

My goal is to post useful queries to both illustrate just how good a job the developers did in designing the Project Open schema and to help folks like us get the most use out of Project Open.

Terrance A. Crow is the Senior Security Engineer at a global library services company. He holds a CISSP and has been writing applications since the days of dBASE III and Lotus 1-2-3 2.01.