Problems with SQL-time functions

RT Users
1

Hello,

I was given the task to filter all the tickets inside or outside fixed
business hours in a specified date range. I will display you table to
demonstrate what I mean:

weekday office opens office closes

Monday-Thursday 8:00:00 16:00:00
Friday 8:00:00 15:30:00

In usual SQL, one would filter weekday by weekday and exclude the time
outside or inside working hours. If you only want to enlist the Tickets
inside business hours, you can use the TIME and WEEKDAY functions, the
SQL statement would look like that:

Select id, Created, Subject from Tickets where WEEKDAY(Created) not in
(0,6) and ((WEEKDAY(Created) in(1, 2, 3, 4) and TIME(Created) >
‘08:00:00’ and TIME(Created) < ‘16:00:00’) or (WEEKDAY(Created)=5 and
TIME(Created) > ‘08:00:00’ and TIME(Created) < ‘15:30:00’))

If you put the statement after the where-clause into the method FormSQL
of the class RT::Ticket, you will not get the right results, but /all/
tickets.

I do not know why that method does not work. Does anybody have any
hints? Or is it just impossible to do so?

Greetings,

Wolfram

2

Hello,

I was given the task to filter all the tickets inside or outside
fixed business hours in a specified date range. I will display you
table to demonstrate what I mean:

weekday office opens office closes

Monday-Thursday 8:00:00 16:00:00
Friday 8:00:00 15:30:00

In usual SQL, one would filter weekday by weekday and exclude the
time outside or inside working hours. If you only want to enlist the
Tickets inside business hours, you can use the TIME and WEEKDAY
functions, the SQL statement would look like that:

Select id, Created, Subject from Tickets where WEEKDAY(Created) not
in (0,6) and ((WEEKDAY(Created) in(1, 2, 3, 4) and TIME(Created) >
‘08:00:00’ and TIME(Created) < ‘16:00:00’) or (WEEKDAY(Created)=5
and TIME(Created) > ‘08:00:00’ and TIME(Created) < ‘15:30:00’))

If you put the statement after the where-clause into the method
FormSQL of the class RT::Ticket, you will not get the right results,
but /all/ tickets.

FromSQL isn’t SQL, it is TicketSQL. I bet it is throwing away a
number of your constructs that it doesn’t understand. You may be able
to construct what you need using a vanilla Limit and the FUNCTION
arguments.

-kevin

3

I have had some wonderful help getting my user information pulled from
AD into my RT installation. Now I am looking at ways to present this to
the technicians. We use our RT installation for our technical support
helpdesk and most of our tickets are opened via E-Mail.

I want to present the user information on the main screen of each ticket
instead of having to click into the “More about user@info.com”. I have
looked at the SideBySideView extension, but that does not look like it
can be used for user info. Please correct me if I am wrong.

Does anyone know of an already contributed way to do what I am looking
for?

Thanks,

Peter Barton

4

Well I found RT-Extension-UserDetails and am very excited about it. The
install seems very easy. I applied the patch as per the instructions in
the README. I am running RT-3.8.6 so I needed to apply the patch. I am
no Linux Admin, but I did the following:

Cd /opt/rt3/
patch -p1 <
/opt/rt3/src/RT-Extension-UserDetails-0.01/patches/callbacks-3.8.patch

It ran with no errors.

I followed the rest of the README with no problems. I then restarted
apache and not I had a little link bubble to the right of my user’s
names. However with I passed my mouse over it all that popped up in the
window is “Details”. When I actually clicked on the bubble I got the
following error:

RT::User:: Unimplemented in HTML::Mason::Commands.
(/opt/rt3/local/plugins/RT-Extension-UserDetails/html/Elements/ShowUserD
etails line 10)

I was able to verify when I moused over the bubble in the bottom of my
web browser it reflected the correct user #.

After messing around with it with no luck I decided to upgrade my test
RT installation to 3.8.8 and I am still having the exact same problems.

Any help would be appreciated.

Thanks,

Peter Barton-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Peter
Barton
Sent: Friday, September 03, 2010 10:02 AM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] User Information

I have had some wonderful help getting my user information pulled from
AD into my RT installation. Now I am looking at ways to present this to
the technicians. We use our RT installation for our technical support
helpdesk and most of our tickets are opened via E-Mail.

I want to present the user information on the main screen of each ticket
instead of having to click into the “More about user@info.com”. I have
looked at the SideBySideView extension, but that does not look like it
can be used for user info. Please correct me if I am wrong.

Does anyone know of an already contributed way to do what I am looking
for?

Thanks,

Peter Barton

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

5

I ended up making a change to my RT_SiteConfig.pm and it started
working. I am using FireFox 3.6.8 and it works great. However as noted
by Steve McStravick this is not working with IE8. He posted something
about it on August 17th entitled “RT-Extension-UserDetails not working
with IE8”. Has anyone found a fix for this?

Peter Barton-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Peter
Barton
Sent: Friday, September 03, 2010 2:14 PM
To: rt-users@lists.bestpractical.com
Cc: elacour@home-dn.net
Subject: Re: [rt-users] User Information

Well I found RT-Extension-UserDetails and am very excited about it. The
install seems very easy. I applied the patch as per the instructions in
the README. I am running RT-3.8.6 so I needed to apply the patch. I am
no Linux Admin, but I did the following:

Cd /opt/rt3/
patch -p1 <
/opt/rt3/src/RT-Extension-UserDetails-0.01/patches/callbacks-3.8.patch

It ran with no errors.

I followed the rest of the README with no problems. I then restarted
apache and not I had a little link bubble to the right of my user’s
names. However with I passed my mouse over it all that popped up in the
window is “Details”. When I actually clicked on the bubble I got the
following error:

RT::User:: Unimplemented in HTML::Mason::Commands.
(/opt/rt3/local/plugins/RT-Extension-UserDetails/html/Elements/ShowUserD
etails line 10)

I was able to verify when I moused over the bubble in the bottom of my
web browser it reflected the correct user #.

After messing around with it with no luck I decided to upgrade my test
RT installation to 3.8.8 and I am still having the exact same problems.

Any help would be appreciated.

Thanks,

Peter Barton

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Peter
Barton
Sent: Friday, September 03, 2010 10:02 AM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] User Information

I have had some wonderful help getting my user information pulled from
AD into my RT installation. Now I am looking at ways to present this to
the technicians. We use our RT installation for our technical support
helpdesk and most of our tickets are opened via E-Mail.

I want to present the user information on the main screen of each ticket
instead of having to click into the “More about user@info.com”. I have
looked at the SideBySideView extension, but that does not look like it
can be used for user info. Please correct me if I am wrong.

Does anyone know of an already contributed way to do what I am looking
for?

Thanks,

Peter Barton

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year – Learn how to get the most out of RT!

6

RT::User:: Unimplemented in HTML::Mason::Commands.
(/opt/rt3/local/plugins/RT-Extension-UserDetails/html/Elements/ShowUserD
etails line 10)

fixed in git (bad handling of default settings, when you don’t set a
setting yourself).

7

I ended up making a change to my RT_SiteConfig.pm and it started
working. I am using FireFox 3.6.8 and it works great. However as noted
by Steve McStravick this is not working with IE8. He posted something
about it on August 17th entitled “RT-Extension-UserDetails not working
with IE8”. Has anyone found a fix for this?

fixed in git (missing ending part of a tag :():

http://git.home-dn.net/?p=manu/RT-Extension-UserDetails.git;a=summary