[rt-users] "Can you make it act like a time card system?"

[Hugh Esco]

I use a cron driven bash script to run this query each night
to monitor the hours for which we're incurring obligations to
our contractors at YMD Partners LLC.

SELECT realname AS name,
        round(sum(timetaken)/60.0,2) as hours,
        q.name AS queue,
        tix.id AS ticket,
        tix.status,
        tix.subject
FROM transactions tr
LEFT JOIN tickets tix
ON tr.objectid = tix.id
LEFT JOIN users u
ON u.id = tr.creator
LEFT JOIN queues q
ON tix.queue = q.id
WHERE tr.objecttype = 'RT::Ticket'
AND tr.created > '2010-11-01 00:00:00'
AND tr.created < '2010-12-01 00:00:00'
GROUP BY u.id, u.realname, q.name,
        tix.queue, tix.id, tix.status,
        tix.subject
HAVING sum(timetaken) > 0
ORDER BY u.realname, tix.queue, tix.id ;

This query is run monthly to generate client
specific reports.

SELECT realname AS name,
        round(sum(timetaken)/60.0,2) as hours,
        q.name AS queue,
        tix.id AS ticket,
        tix.status,
        tix.subject
FROM transactions tr
LEFT JOIN tickets tix
ON tr.objectid = tix.id
LEFT JOIN users u
ON u.id = tr.creator
LEFT JOIN queues q
ON tix.queue = q.id
WHERE tr.objecttype = 'RT::Ticket'
AND tix.queue IN(14,15,21,22)
AND tr.created > '2010-11-01 00:00:00'
AND tr.created < '2010-12-01 00:00:00'
GROUP BY u.id, u.realname, q.name,
        tix.queue, tix.id, tix.status,
        tix.subject
HAVING sum(timetaken) > 0
ORDER BY q.name, u.realname, tix.queue, tix.id ;

These reports get emailed to me each night.

-------------------------------


On Mon, 06 Dec 2010 15:09:16 -0800
Gary Greene <ggreene at minervanetworks.com> wrote:

> On 6/12/10 1:49 PM, "RAT" <robert3t at netzero.net> wrote:
> > We are nearly finished with a new RT deployment but the request was made to be
> > able to print out reports for each user for each day showing hours worked and
> > where there are gaps (I know *sigh*).  I didn't see any reports or extensions
> > that exactly fit the bill.  Does anyone have any ideas?
> > 
> > Robert Threet
> > http://yesistilluseperl.blogspot.com/
> 
> My best guess would be to use a script that either uses the REST API to get
> the info from the DB, or reads directly from the DB to get the ticket work
> time and then output a pretty HTML report per user, which is linked off a
> roster, or some-such.
> 
> -- 
> Gary L. Greene, Jr.
> IT Operations
> Minerva Networks, Inc.
> Cell:   (650) 704-6633
> Office: (408) 240-1239

-- 
Hugh Esco 
skype: hresco3_ ; 678-921-8186 
http://www.CampaignFoundations.com/
Providing Application Hosting, 
Telephony and IT Development Services 
to Green Candidates, Green Parties and
the non profits working for a just and sustainable future.

if( $insurance->rationing() ) { $people->die(); }