[rt-users] Time Worked Report

[Stephen Cochran]

I just took a quick look to see if I could see where the Transaction is
created for editing the TimeWorked field in the Ticket Basics.Traced it as
follows Ticket/Modify.html -> Web.pm::ProcessTicketBasics() ->
Web.pm::UpdateRecordObject() -> Record.pm::Update(), but that seems to be
it. I don't see what triggers a transaction in any of those functions.

Probably easier to disable the TimeWorked field in the
Ticket/Elements/EditBasics file so it can't be used by accident and train
users to put in a comment/reply.

Would be clean to simply have a transcation generated from modifying the
basic info update the TimeTaken field in the transaction to be the different
of old - new TimeWorked. Then all time info could be pulled from the
transaction table easily.

Steve


On Tue, Mar 3, 2009 at 10:38 AM, Alex Young
<alexyoung at scoutsolutions.co.uk>wrote:

>  Its been suggested to make the Ticket.TimeWorked field uneditable by
> users. That would stop anyone from editing the field directly, and it would
> only change when adding or subtracted time worked in a transaction. Besides,
> if youre removing time from a ticket you should be saying why youre removing
> time. Same for adding time.
>
>
>
> Stephen, dont forget to *reply to all* if you want the list to see your
> replies. (Which I think you do)
>
>
>
>
>
> *From:* Stephen Cochran [mailto:stephen.a.cochran.lists at cahir.net]
> *Sent:* 03 March 2009 15:33
>
> *To:* Alex Young
> *Subject:* Re: [rt-users] Time Worked Report
>
>
>
>
> You could use the same logic and not restrict on resolved, but not sure how
> you'd know not to double-count any time put in by adjusting the
> Tickets.TimeWorked directly. It could also be adjusted downwards as well,
> which raises all sorts of other questions about where that would get
> subtracted from. Adjustments made to TimeWorked do cause a transaction to be
> created, it just doesn't populate the TimeTaken field of the transaction.
>
> Now that I think about it, that might be the simplest solution, beacuse
> most of the complexity comes from trying to accurately deal with direct
> adjustments made to the Tickets.TimeWorked field.
>
> Anyone from BestP that could weigh in on the consequences of having a
> change to TimeWorked also record the different in Transactions.TimeTaken?
>
>  On Tue, Mar 3, 2009 at 9:52 AM, Alex Young <
> alexyoung at scoutsolutions.co.uk> wrote:
>
> Looks like that would work to me. Though I would need to get the time taken
> no matter what the status is as I was aiming at pulling the info out for
> time sheets for the developers and support department so the accounts
> department can invoice clients. At the moment RT users are putting all their
> work in RT, and then putting their time in a separate timesheet, which
> management then compile and give to accounts.
>
>
>
> It would be very useful to have a time sheet extension for this purpose,
> and most of the code and info must already be available in RT to do this.
>
>
>
>
>
>
>
> *From:* Stephen Cochran [mailto:stephen.a.cochran.lists at cahir.net]
> *Sent:* 03 March 2009 14:27
> *To:* Alex Young
> *Subject:* Re: [rt-users] Time Worked Report
>
>
>
> Thinking through this, the only way to be completely accurate list of time
> spend in some given time_window would be the following (in psudo sql):
>
> # total time worked in time_window from transactions
> select SUM(Transactions.TimeTaken) from ...
> where (Tickets.status = open or stalled or new) and
> Transactions.Created is within time_window
>
> +
>
> # get total time worked from tickets resolved in time_window
> select Tickets.TimeWorked from ....
> where (Tickets.status = resolved) and
> Tickets.Resolved is within time_window
>
> -
>
> # subtract time from all transactions from tickets resolved in time_window
> select SUM(Transactions.TimeTaken) from ...
> where (Tickets.status = resolved) and
> Tickets.Resolved is within time_window and
> Transactions.ObjectId = Tickets.id
>
> This will capture all times entered directly into the TimeWorked field of
> the ticket as occurring during the time_window when the ticket was resolved.
> Probably fairly accurate, and would never be double counted.
>
> Anyone see a flaw in this? Still think this is harder than it should be.
> Explains why RT hasn't had these reports already ;)
>
> On Tue, Mar 3, 2009 at 4:28 AM, Alex Young <alexyoung at scoutsolutions.co.uk>
> wrote:
>
> I had a bit of a go at doing this myself. It needs some more work as it
> doesnt take into account if time has been removed from a ticket. It happens
> sometimes because of typos etc.
>
>
>
> If you get anywhere with this please share it, as I havent had the time to
> work on it further.
>
>
>
> PRNumber is an internal reference number that we book client work too, so
> you wont need that, or you can change it for something else.
>
>
>
> SELECT distinct SUM(Transactions.TimeTaken) AS 'Time Taken (Mins)',
> Transactions.Created, Users.RealName, Tickets.Subject, Queues.Name AS 'Queue
> Name', Transactions.ObjectId AS 'Ticket ID',
>
> (select ObjectCustomFieldValues.Content from ObjectCustomFieldValues where
> ObjectCustomFieldValues.CustomField = '11' and Transactions.ObjectId =
> ObjectCustomFieldValues.ObjectId order by ObjectCustomFieldValues.id desc
> LIMIT 1) AS PRNumber
>
> FROM Transactions
>
> LEFT JOIN Users
>
> ON Transactions.Creator = Users.Id
>
> LEFT JOIN Tickets
>
> ON Transactions.ObjectId = Tickets.id
>
> LEFT JOIN Queues
>
> ON Tickets.Queue = Queues.Id
>
> WHERE Transactions.TimeTaken !=0
>
> AND DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= Transactions.Created
>
> GROUP BY Subject;
>
>
>
>
>
> *From:* rt-users-bounces at lists.bestpractical.com [mailto:
> rt-users-bounces at lists.bestpractical.com] *On Behalf Of *Stephen Cochran
> *Sent:* 03 March 2009 05:01
> *To:* rt Users
> *Subject:* [rt-users] Time Worked Report
>
>
>
>
> I've written a sql query to pull out the time worked for all tickets
> resolved in the last week among other things. The problem with this is that
> it doesn't give a complete picture of time worked for any given week since a
> ticket could have had time worked put in as part of a transaction but the
> ticket might still be open. I could query the Transactions table for the
> TimeTaken field, but that could lead to double-counting if any of those
> transactions are part of a resolved ticket.
>
> I know I could work through the db/sql and find the right query to pull out
> the time worked in the last week, but I'm wondering if someone else has
> already done it so I can save myself the trouble.
>
> Thanks,
> Steve
>
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090303/ea1edb34/attachment.htm>