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 <[email protected]>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:[email protected]] > *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 < > [email protected]> 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:[email protected]] > *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 <[email protected]> > 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:* [email protected] [mailto: > [email protected]] *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 > > > > >
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [email protected] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
