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
Office: 604 568 0313 x114
Fax: 604 568 0314
Cell: 778 689 1244
Skype: will.sheldon
#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 (cryth...@gmail.com) wrote:
Hi Will,
This might (?) be helpful.
http://forums.otterhub.org/viewtopic.php?f=60&t=12546#p50624
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 <w...@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
Office: 604 568 0313 x114
Fax: 604 568 0314
Cell: 778 689 1244
Skype: will.sheldon
#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
---------------------------------------------------------------------
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
