1 use Test::MockTime qw(set_fixed_time restore_time);
6 use RT::Test nodata => 1, tests => undef;
7 RT->Config->Set( 'Timezone' => 'EST5EDT' ); # -04:00
10 { Principal => 'Everyone', Right => [qw(
11 SeeQueue ShowTicket CreateTicket SeeCustomField ModifyCustomField
15 my $q = RT::Test->load_or_create_queue( Name => 'General' );
16 ok $q && $q->id, 'loaded or created a queue';
18 my $user_m = RT::Test->load_or_create_user( Name => 'moscow', Timezone => 'Europe/Moscow' );
19 ok $user_m && $user_m->id;
20 $user_m = RT::CurrentUser->new( $user_m );
22 my $user_b = RT::Test->load_or_create_user( Name => 'boston', Timezone => 'America/New_York' );
23 ok $user_b && $user_b->id;
24 $user_b = RT::CurrentUser->new( $user_b );
26 my $cf = RT::CustomField->new(RT->SystemUser);
29 Name => 'TestDateTime',
32 LookupType => RT::Ticket->CustomFieldLookupType,
36 ok( $cf->AddToObject($q), 'date cf apply to queue' );
37 my $cf_name = $cf->Name;
39 my $ticket = RT::Ticket->new(RT->SystemUser);
45 'CustomField-' . $cf->id => '2010-05-04 07:00:00',
47 'create ticket with cf set to 2010-05-04 07:00:00( 2010-05-04 11:00:00 with UTC )'
51 $ticket->CustomFieldValues->First->Content,
52 '2010-05-04 11:00:00',
53 'date in db is in timezone UTC'
58 my $tickets = RT::Tickets->new(RT->SystemUser);
59 $tickets->LimitCustomField(
60 CUSTOMFIELD => $cf->id,
62 VALUE => '2010-05-04 07:00:00', # this timezone is server
65 is( $tickets->Count, 1, 'found the ticket with exact date: 2010-05-04 07:00:00' );
70 # TODO according to the code, if OPERATOR is '=', it means on that day
71 # this will test this behavior
72 my $tickets = RT::Tickets->new(RT->SystemUser);
73 $tickets->LimitCustomField(
74 CUSTOMFIELD => $cf->id,
76 VALUE => '2010-05-04',
79 is( $tickets->Count, 1, 'found the ticket with rough date: 2010-05-04' );
84 # TODO according to the code, if OPERATOR is '=', it means on that day
85 # this will test this behavior
86 my $tickets = RT::Tickets->new(RT->SystemUser);
87 $tickets->LimitCustomField(
88 CUSTOMFIELD => $cf->id,
90 VALUE => '2010-05-05',
93 is( $tickets->Count, 0, 'did not find the ticket with wrong datetime: 2010-05-05' );
97 my $tickets = RT::Tickets->new(RT->SystemUser);
98 $tickets->FromSQL( "'CF.{$cf_name}' = 'May 4 2010 7am'" );
99 is( $tickets->Count, 1, 'found the ticket with = May 4 2010 7am' );
101 $tickets->FromSQL( "'CF.{$cf_name}' = 'May 4 2010 8am'" );
102 is( $tickets->Count, 0, 'did not find the ticket with = May 4 2010 8am' );
104 $tickets->FromSQL( "'CF.{$cf_name}' > 'May 3 2010 7am'" );
105 is( $tickets->Count, 1, 'found the ticket with > May 3 2010 7am' );
107 $tickets->FromSQL( "'CF.{$cf_name}' < 'May 4 2010 8am'" );
108 is( $tickets->Count, 1, 'found the ticket with < May 4 2010 8am' );
113 my $tickets = RT::Tickets->new( RT->SystemUser );
115 while( my $ticket = $tickets->Next ) {
124 'CustomField-' . $cf->id => '2010-06-21 17:00:01',
126 'create ticket with cf set to 2010-06-21 17:00:01( 2010-06-21 21:00:01 with UTC )'
129 my $shanghai = RT::Test->load_or_create_user(
131 Timezone => 'Asia/Shanghai',
135 $shanghai->PrincipalObj->GrantRight(
136 Right => 'SuperUser',
137 Object => $RT::System,
141 my $current_user = RT::CurrentUser->new($shanghai);
142 my $tickets = RT::Tickets->new($current_user);
143 $tickets->LimitCustomField(
144 CUSTOMFIELD => $cf->id,
146 VALUE => '2010-06-22',
148 is( $tickets->Count, 1, 'found the ticket with rough datetime: 2010-06-22' );
151 $tickets->LimitCustomField(
152 CUSTOMFIELD => $cf->id,
154 VALUE => '2010-06-21',
156 is( $tickets->Count, 1, 'found the ticket with > 2010-06-21' );
159 $tickets->LimitCustomField(
160 CUSTOMFIELD => $cf->id,
162 VALUE => '2010-06-23',
164 is( $tickets->Count, 1, 'found the ticket with < 2010-06-23' );
167 $tickets->LimitCustomField(
168 CUSTOMFIELD => $cf->id,
170 VALUE => '2010-06-22 05:00:01',
172 is( $tickets->Count, 1, 'found the ticket with = 2010-06-22 01:00:01' );
175 # set timezone in all places to UTC
177 RT->SystemUser->UserObj->__Set(Field => 'Timezone', Value => 'UTC')
178 if RT->SystemUser->UserObj->Timezone;
179 RT->Config->Set( Timezone => 'UTC' );
182 # search by absolute date with '=', but date only
184 my $ticket = RT::Ticket->new(RT->SystemUser);
185 my ($tid) = $ticket->Create(
188 'CustomField-' . $cf->id => '2013-02-11 23:14:15',
190 is $ticket->FirstCustomFieldValue($cf_name), '2013-02-11 23:14:15';
192 my $tickets = RT::Tickets->new($user_m);
193 $tickets->FromSQL("'CustomField.{$cf_name}' = '2013-02-11' AND id = $tid");
194 is( $tickets->Count, 0);
196 $tickets = RT::Tickets->new($user_m);
197 $tickets->FromSQL("'CustomField.{$cf_name}' = '2013-02-12' AND id = $tid");
198 is( $tickets->Count, 1);
200 $tickets = RT::Tickets->new($user_b);
201 $tickets->FromSQL("'CustomField.{$cf_name}' = '2013-02-11' AND id = $tid");
202 is( $tickets->Count, 1);
204 $tickets = RT::Tickets->new($user_b);
205 $tickets->FromSQL("'CustomField.{$cf_name}' = '2013-02-12' AND id = $tid");
206 is( $tickets->Count, 0);
210 my $tickets = RT::Tickets->new(RT->SystemUser);
211 $tickets->LimitCustomField(
212 CUSTOMFIELD => $cf->id,
217 is( $tickets->Count, 0, 'did not find the ticket with date IS NULL' );
221 my $tickets = RT::Tickets->new(RT->SystemUser);
222 $tickets->LimitCustomField(
223 CUSTOMFIELD => $cf->id,
224 OPERATOR => 'IS NOT',
228 is( $tickets->Count, 2, 'did find the ticket with date IS NOT NULL' );
232 # search by relative date with '=', but date only
234 my $ticket = RT::Ticket->new(RT->SystemUser);
235 my ($tid) = $ticket->Create(
238 'CustomField-' . $cf->id => '2013-02-11 23:14:15',
240 is $ticket->FirstCustomFieldValue($cf_name), '2013-02-11 23:14:15';
242 set_fixed_time("2013-02-10T16:10:00Z");
243 my $tickets = RT::Tickets->new($user_m);
244 $tickets->FromSQL("'CustomField.{$cf_name}' = 'tomorrow' AND id = $tid");
245 is( $tickets->Count, 0);
247 set_fixed_time("2013-02-10T23:10:00Z");
248 $tickets = RT::Tickets->new($user_m);
249 $tickets->FromSQL("'CustomField.{$cf_name}' = 'tomorrow' AND id = $tid");
250 is( $tickets->Count, 1);
252 set_fixed_time("2013-02-10T23:10:00Z");
253 $tickets = RT::Tickets->new($user_b);
254 $tickets->FromSQL("'CustomField.{$cf_name}' = 'tomorrow' AND id = $tid");
255 is( $tickets->Count, 1);
257 set_fixed_time("2013-02-10T02:10:00Z");
258 $tickets = RT::Tickets->new($user_b);
259 $tickets->FromSQL("'CustomField.{$cf_name}' = 'tomorrow' AND id = $tid");
260 is( $tickets->Count, 0);