Thanks Gerald.

This is what we ended up with, in case it helps anyone. This gets the last week’s worth of time data, as well as a dynamic field (in our case project code) and output to a csv file in /tmp/


select t.tn ticketnumber, CONCAT(u2.first_name, " ", u2.last_name) owner,
CONCAT(u.first_name, " ", u.last_name) name, t.title title, q.name queue, t.customer_user_id customer_user,
t.customer_id customer, ta.time_unit time_unit, DATE_FORMAT(ta.create_time, "%m/%d/%y") create_time,
a.a_subject subject, a.a_body body 
,df.value_text project
from otrs.ticket t

left join otrs.time_accounting ta on ta.ticket_id=t.id
left join otrs.queue q on t.queue_id = q.id
left join otrs.article a on a.id=ta.article_id
left join otrs.users u on u.id = a.create_by
left join otrs.users u2 on u2.id=t.user_id
left join otrs.dynamic_field_value df on (df.object_id=t.id AND df.field_id=12)

where ta.time_unit is not null
AND df.value_text is not null
AND ta.create_time >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND ta.create_time < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
ORDER BY create_time

INTO OUTFILE '/tmp/otrs-time.csv'
 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n’





Will Sheldon
IT Manager

#300-152 West Hastings
Vancouver, BC, V6B 1G8 Canada 
www.appnovation.com
     

Click here to unsubscribe from further Appnovation communications.



On August 25, 2014 at 1:09:33 PM, Gerald Young (crythias@gmail.com) wrote:

Hi Will,
This might (?) be helpful.

If you're interested in tickets that have a dynamic field value, check out http://forums.otterhub.org/viewtopic.php?f=53&t=16009#p77686

WHERE create_time BETWEEN NOW() and DATE_SUB(NOW(), INTERVAL 7 DAY)




On Mon, Aug 25, 2014 at 4:03 PM, Will Sheldon <will@appnovation.com> wrote:

Hello all :)

We have a dynamic field added to track project number (if this is the wrong way to add project accounting I’d be happy to know the correct way).

We also have time units enabled and made mandatory (Frontend::Agent; Ticket::Frontend::AccountTime - Yes; Ticket::Frontend::NeedAccountedTime - Yes)

I’m trying to produce a report covering the last week showing time per project (totals) and time per project broken down by ticket, listing clientID, ticket#, subject and project.

Firstly, I have no idea how to do this :( I’ve had a play around with the stats generator to no avail. I’m not even sure if I should be using TimeAccounting or TicketAccountedTime..

Secondly, I’m not sure how to display just time for the last 7 days (some tickets remain open for several weeks and can accrue upwards of 30-40 hours work)


If anyone has done this I’d love to have a chat. If anyone can provide support in getting this setup I’d be happy to pay for a little training…





Will Sheldon
IT Manager

#300-152 West Hastings
Vancouver, BC, V6B 1G8 Canada 
www.appnovation.com
      

Click here to unsubscribe from further Appnovation communications.

---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs