Tickets_Overlay.pm 95 KB
Newer Older
1
# BEGIN BPS TAGGED BLOCK {{{
Jesse Vincent's avatar
Jesse Vincent committed
2
# 
3
# COPYRIGHT:
Jesse Vincent's avatar
Jesse Vincent committed
4
# 
Thomas Sibley's avatar
Thomas Sibley committed
5
# This software is Copyright (c) 1996-2010 Best Practical Solutions, LLC
6
#                                          <jesse@bestpractical.com>
Jesse Vincent's avatar
Jesse Vincent committed
7
# 
8
# (Except where explicitly superseded by other copyright notices)
Jesse Vincent's avatar
Jesse Vincent committed
9
10
# 
# 
11
# LICENSE:
Jesse Vincent's avatar
Jesse Vincent committed
12
# 
13
14
15
# This work is made available to you under the terms of Version 2 of
# the GNU General Public License. A copy of that license should have
# been provided with this software, but in any event can be snarfed
16
# from www.gnu.org.
Jesse Vincent's avatar
Jesse Vincent committed
17
# 
18
19
20
21
# This work is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
Jesse Vincent's avatar
Jesse Vincent committed
22
# 
23
24
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
Jesse Vincent's avatar
Jesse Vincent committed
25
26
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
# 02110-1301 or visit their web page on the internet at
Ruslan Zakirov's avatar
Ruslan Zakirov committed
27
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
Jesse Vincent's avatar
Jesse Vincent committed
28
29
# 
# 
30
# CONTRIBUTION SUBMISSION POLICY:
Jesse Vincent's avatar
Jesse Vincent committed
31
# 
32
33
34
35
36
# (The following paragraph is not intended to limit the rights granted
# to you to modify and distribute this software under the terms of
# the GNU General Public License and is only of importance to you if
# you choose to contribute your changes and enhancements to the
# community by submitting them to Best Practical Solutions, LLC.)
Jesse Vincent's avatar
Jesse Vincent committed
37
# 
38
39
40
41
42
43
44
45
# By intentionally submitting any modifications, corrections or
# derivatives to this work, or any other work intended for use with
# Request Tracker, to Best Practical Solutions, LLC, you confirm that
# you are the copyright holder for those contributions and you grant
# Best Practical Solutions,  LLC a nonexclusive, worldwide, irrevocable,
# royalty-free, perpetual, license to use, copy, create derivative
# works based on those contributions, and sublicense and distribute
# those contributions and any derivatives thereof.
Jesse Vincent's avatar
Jesse Vincent committed
46
# 
47
# END BPS TAGGED BLOCK }}}
48

49
50
51
52
53
54
55
56
57
58
59
# Major Changes:

# - Decimated ProcessRestrictions and broke it into multiple
# functions joined by a LUT
# - Semi-Generic SQL stuff moved to another file

# Known Issues: FIXME!

# - ClearRestrictions and Reinitialization is messy and unclear.  The
# only good way to do it is to create a new RT::Tickets object.

Jesse Vincent's avatar
rt.2.1  
Jesse Vincent committed
60
61
62
63
64
65
66
67
=head1 NAME

  RT::Tickets - A collection of Ticket objects


=head1 SYNOPSIS

  use RT::Tickets;
68
  my $tickets = RT::Tickets->new($CurrentUser);
Jesse Vincent's avatar
rt.2.1  
Jesse Vincent committed
69
70
71
72
73
74
75
76
77

=head1 DESCRIPTION

   A collection of RT::Tickets.

=head1 METHODS


=cut
78

79
80
package RT::Tickets;

81
use strict;
Jesse Vincent's avatar
rt.2.1  
Jesse Vincent committed
82
no warnings qw(redefine);
83

Jesse Vincent's avatar
Jesse Vincent committed
84
use RT::CustomFields;
85
use DBIx::SearchBuilder::Unique;
86
87
88

# Configuration Tables:

89
# FIELD_METADATA is a mapping of searchable Field name, to Type, and other
90
91
# metadata.

92
our %FIELD_METADATA = (
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
    Status          => [ 'ENUM', ], #loc_left_pair
    Queue           => [ 'ENUM' => 'Queue', ], #loc_left_pair
    Type            => [ 'ENUM', ], #loc_left_pair
    Creator         => [ 'ENUM' => 'User', ], #loc_left_pair
    LastUpdatedBy   => [ 'ENUM' => 'User', ], #loc_left_pair
    Owner           => [ 'WATCHERFIELD' => 'Owner', ], #loc_left_pair
    EffectiveId     => [ 'INT', ], #loc_left_pair
    id              => [ 'ID', ], #loc_left_pair
    InitialPriority => [ 'INT', ], #loc_left_pair
    FinalPriority   => [ 'INT', ], #loc_left_pair
    Priority        => [ 'INT', ], #loc_left_pair
    TimeLeft        => [ 'INT', ], #loc_left_pair
    TimeWorked      => [ 'INT', ], #loc_left_pair
    TimeEstimated   => [ 'INT', ], #loc_left_pair

    Linked          => [ 'LINK' ], #loc_left_pair
    LinkedTo        => [ 'LINK' => 'To' ], #loc_left_pair
    LinkedFrom      => [ 'LINK' => 'From' ], #loc_left_pair
    MemberOf        => [ 'LINK' => To => 'MemberOf', ], #loc_left_pair
    DependsOn       => [ 'LINK' => To => 'DependsOn', ], #loc_left_pair
    RefersTo        => [ 'LINK' => To => 'RefersTo', ], #loc_left_pair
    HasMember       => [ 'LINK' => From => 'MemberOf', ], #loc_left_pair
    DependentOn     => [ 'LINK' => From => 'DependsOn', ], #loc_left_pair
    DependedOnBy    => [ 'LINK' => From => 'DependsOn', ], #loc_left_pair
    ReferredToBy    => [ 'LINK' => From => 'RefersTo', ], #loc_left_pair
    Told             => [ 'DATE'            => 'Told', ], #loc_left_pair
    Starts           => [ 'DATE'            => 'Starts', ], #loc_left_pair
    Started          => [ 'DATE'            => 'Started', ], #loc_left_pair
    Due              => [ 'DATE'            => 'Due', ], #loc_left_pair
    Resolved         => [ 'DATE'            => 'Resolved', ], #loc_left_pair
    LastUpdated      => [ 'DATE'            => 'LastUpdated', ], #loc_left_pair
    Created          => [ 'DATE'            => 'Created', ], #loc_left_pair
    Subject          => [ 'STRING', ], #loc_left_pair
    Content          => [ 'TRANSFIELD', ], #loc_left_pair
    ContentType      => [ 'TRANSFIELD', ], #loc_left_pair
    Filename         => [ 'TRANSFIELD', ], #loc_left_pair
    TransactionDate  => [ 'TRANSDATE', ], #loc_left_pair
    Requestor        => [ 'WATCHERFIELD'    => 'Requestor', ], #loc_left_pair
    Requestors       => [ 'WATCHERFIELD'    => 'Requestor', ], #loc_left_pair
    Cc               => [ 'WATCHERFIELD'    => 'Cc', ], #loc_left_pair
    AdminCc          => [ 'WATCHERFIELD'    => 'AdminCc', ], #loc_left_pair
    Watcher          => [ 'WATCHERFIELD', ], #loc_left_pair
    QueueCc          => [ 'WATCHERFIELD'    => 'Cc'      => 'Queue', ], #loc_left_pair
    QueueAdminCc     => [ 'WATCHERFIELD'    => 'AdminCc' => 'Queue', ], #loc_left_pair
    QueueWatcher     => [ 'WATCHERFIELD'    => undef     => 'Queue', ], #loc_left_pair
    CustomFieldValue => [ 'CUSTOMFIELD', ], #loc_left_pair
    CustomField      => [ 'CUSTOMFIELD', ], #loc_left_pair
    CF               => [ 'CUSTOMFIELD', ], #loc_left_pair
    Updated          => [ 'TRANSDATE', ], #loc_left_pair
    RequestorGroup   => [ 'MEMBERSHIPFIELD' => 'Requestor', ], #loc_left_pair
    CCGroup          => [ 'MEMBERSHIPFIELD' => 'Cc', ], #loc_left_pair
    AdminCCGroup     => [ 'MEMBERSHIPFIELD' => 'AdminCc', ], #loc_left_pair
    WatcherGroup     => [ 'MEMBERSHIPFIELD', ], #loc_left_pair
146
147
    HasAttribute     => [ 'HASATTRIBUTE', 1 ],
    HasNoAttribute     => [ 'HASATTRIBUTE', 0 ],
148
);
149
150

# Mapping of Field Type to Function
151
our %dispatch = (
152
153
    ENUM            => \&_EnumLimit,
    INT             => \&_IntLimit,
154
    ID              => \&_IdLimit,
155
156
157
158
159
    LINK            => \&_LinkLimit,
    DATE            => \&_DateLimit,
    STRING          => \&_StringLimit,
    TRANSFIELD      => \&_TransLimit,
    TRANSDATE       => \&_TransDateLimit,
160
    WATCHERFIELD    => \&_WatcherLimit,
161
162
    MEMBERSHIPFIELD => \&_WatcherMembershipLimit,
    CUSTOMFIELD     => \&_CustomFieldLimit,
163
    HASATTRIBUTE    => \&_HasAttributeLimit,
164
);
165
our %can_bundle = ();# WATCHERFIELD => "yes", );
166
167

# Default EntryAggregator per type
Jesse Vincent's avatar
Jesse Vincent committed
168
# if you specify OP, you must specify all valid OPs
169
my %DefaultEA = (
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
    INT  => 'AND',
    ENUM => {
        '='  => 'OR',
        '!=' => 'AND'
    },
    DATE => {
        '='  => 'OR',
        '>=' => 'AND',
        '<=' => 'AND',
        '>'  => 'AND',
        '<'  => 'AND'
    },
    STRING => {
        '='        => 'OR',
        '!='       => 'AND',
        'LIKE'     => 'AND',
        'NOT LIKE' => 'AND'
    },
    TRANSFIELD   => 'AND',
    TRANSDATE    => 'AND',
    LINK         => 'OR',
    LINKFIELD    => 'AND',
    TARGET       => 'AND',
    BASE         => 'AND',
    WATCHERFIELD => {
        '='        => 'OR',
        '!='       => 'AND',
        'LIKE'     => 'OR',
        'NOT LIKE' => 'AND'
    },

201
202
203
204
205
    HASATTRIBUTE => {
        '='        => 'AND',
        '!='       => 'AND',
    },

206
207
    CUSTOMFIELD => 'OR',
);
208
209
210

# Helper functions for passing the above lexically scoped tables above
# into Tickets_Overlay_SQL.
211
sub FIELDS     { return \%FIELD_METADATA }
212
sub dispatch   { return \%dispatch }
213
sub can_bundle { return \%can_bundle }
214
215
216

# Bring in the clowns.
require RT::Tickets_Overlay_SQL;
Jesse Vincent's avatar
rt.2.1  
Jesse Vincent committed
217
218
219

# {{{ sub SortFields

220
our @SORTFIELDS = qw(id Status
Jesse Vincent's avatar
Jesse Vincent committed
221
222
223
224
    Queue Subject
    Owner Created Due Starts Started
    Told
    Resolved LastUpdated Priority TimeWorked TimeLeft);
Jesse Vincent's avatar
rt.2.1  
Jesse Vincent committed
225
226
227
228
229
230
231
232

=head2 SortFields

Returns the list of fields that lists of tickets can easily be sorted by

=cut

sub SortFields {
233
234
    my $self = shift;
    return (@SORTFIELDS);
Jesse Vincent's avatar
rt.2.1  
Jesse Vincent committed
235
236
}

237
238
239
240
# }}}

# BEGIN SQL STUFF *********************************

241
242
243
244
245
246
247
248
249
250
251
252

sub CleanSlate {
    my $self = shift;
    $self->SUPER::CleanSlate( @_ );
    delete $self->{$_} foreach qw(
        _sql_cf_alias
        _sql_group_members_aliases
        _sql_object_cfv_alias
        _sql_role_group_aliases
        _sql_transalias
        _sql_trattachalias
        _sql_u_watchers_alias_for_sort
253
        _sql_u_watchers_aliases
Ruslan Zakirov's avatar
Ruslan Zakirov committed
254
        _sql_current_user_can_see_applied
255
256
257
    );
}

258
259
260
261
262
263
264
265
266
267
268
269
270
271
=head1 Limit Helper Routines

These routines are the targets of a dispatch table depending on the
type of field.  They all share the same signature:

  my ($self,$field,$op,$value,@rest) = @_;

The values in @rest should be suitable for passing directly to
DBIx::SearchBuilder::Limit.

Essentially they are an expanded/broken out (and much simplified)
version of what ProcessRestrictions used to do.  They're also much
more clearly delineated by the TYPE of field being processed.

272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
=head2 _IdLimit

Handle ID field.

=cut

sub _IdLimit {
    my ( $sb, $field, $op, $value, @rest ) = @_;

    return $sb->_IntLimit( $field, $op, $value, @rest ) unless $value eq '__Bookmarked__';

    die "Invalid operator $op for __Bookmarked__ search on $field"
        unless $op =~ /^(=|!=)$/;

    my @bookmarks = do {
        my $tmp = $sb->CurrentUser->UserObj->FirstAttribute('Bookmarks');
        $tmp = $tmp->Content if $tmp;
        $tmp ||= {};
        grep $_, keys %$tmp;
    };

    return $sb->_SQLLimit(
        FIELD    => $field,
        OPERATOR => $op,
        VALUE    => 0,
        @rest,
    ) unless @bookmarks;

    # as bookmarked tickets can be merged we have to use a join
    # but it should be pretty lightweight
    my $tickets_alias = $sb->Join(
        TYPE   => 'LEFT',
        ALIAS1 => 'main',
        FIELD1 => 'id',
        TABLE2 => 'Tickets',
        FIELD2 => 'EffectiveId',
    );
    $sb->_OpenParen;
    my $first = 1;
    my $ea = $op eq '='? 'OR': 'AND';
    foreach my $id ( sort @bookmarks ) {
        $sb->_SQLLimit(
            ALIAS    => $tickets_alias,
            FIELD    => 'id',
            OPERATOR => $op,
            VALUE    => $id,
            $first? (@rest): ( ENTRYAGGREGATOR => $ea )
        );
    }
    $sb->_CloseParen;
}

324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
=head2 _EnumLimit

Handle Fields which are limited to certain values, and potentially
need to be looked up from another class.

This subroutine actually handles two different kinds of fields.  For
some the user is responsible for limiting the values.  (i.e. Status,
Type).

For others, the value specified by the user will be looked by via
specified class.

Meta Data:
  name of class to lookup in (Optional)

=cut

sub _EnumLimit {
342
    my ( $sb, $field, $op, $value, @rest ) = @_;
343

344
345
    # SQL::Statement changes != to <>.  (Can we remove this now?)
    $op = "!=" if $op eq "<>";
346

347
    die "Invalid Operation: $op for $field"
Jesse Vincent's avatar
Jesse Vincent committed
348
349
        unless $op eq "="
        or $op     eq "!=";
350

351
    my $meta = $FIELD_METADATA{$field};
352
    if ( defined $meta->[1] && defined $value && $value !~ /^\d+$/ ) {
353
354
355
356
357
358
359
360
361
362
363
        my $class = "RT::" . $meta->[1];
        my $o     = $class->new( $sb->CurrentUser );
        $o->Load($value);
        $value = $o->Id;
    }
    $sb->_SQLLimit(
        FIELD    => $field,
        VALUE    => $value,
        OPERATOR => $op,
        @rest,
    );
364
365
366
367
368
369
370
371
372
373
374
375
376
}

=head2 _IntLimit

Handle fields where the values are limited to integers.  (For example,
Priority, TimeWorked.)

Meta Data:
  None

=cut

sub _IntLimit {
377
    my ( $sb, $field, $op, $value, @rest ) = @_;
378

379
    die "Invalid Operator $op for $field"
Jesse Vincent's avatar
Jesse Vincent committed
380
        unless $op =~ /^(=|!=|>|<|>=|<=)$/;
381

382
383
384
385
386
387
    $sb->_SQLLimit(
        FIELD    => $field,
        VALUE    => $value,
        OPERATOR => $op,
        @rest,
    );
388
389
390
391
392
393
394
}

=head2 _LinkLimit

Handle fields which deal with links between tickets.  (MemberOf, DependsOn)

Meta Data:
Ruslan Zakirov's avatar
Ruslan Zakirov committed
395
396
  1: Direction (From, To)
  2: Link Type (MemberOf, DependsOn, RefersTo)
397
398
399
400

=cut

sub _LinkLimit {
401
402
    my ( $sb, $field, $op, $value, @rest ) = @_;

403
    my $meta = $FIELD_METADATA{$field};
Ruslan Zakirov's avatar
Ruslan Zakirov committed
404
    die "Invalid Operator $op for $field" unless $op =~ /^(=|!=|IS|IS NOT)$/io;
405

Ruslan Zakirov's avatar
Ruslan Zakirov committed
406
407
408
409
    my $is_negative = 0;
    if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
        $is_negative = 1;
    }
410
411
    my $is_null = 0;
    $is_null = 1 if !$value || $value =~ /^null$/io;
Ruslan Zakirov's avatar
Ruslan Zakirov committed
412

413
414
    my $direction = $meta->[1] || '';
    my ($matchfield, $linkfield) = ('', '');
415
    if ( $direction eq 'To' ) {
416
        ($matchfield, $linkfield) = ("Target", "Base");
417
418
    }
    elsif ( $direction eq 'From' ) {
419
        ($matchfield, $linkfield) = ("Base", "Target");
420
    }
421
422
    elsif ( $direction ) {
        die "Invalid link direction '$direction' for $field\n";
Ruslan Zakirov's avatar
Ruslan Zakirov committed
423
424
425
426
427
    } else {
        $sb->_OpenParen;
        $sb->_LinkLimit( 'LinkedTo', $op, $value, @rest );
        $sb->_LinkLimit(
            'LinkedFrom', $op, $value, @rest,
428
            ENTRYAGGREGATOR => (($is_negative && $is_null) || (!$is_null && !$is_negative))? 'OR': 'AND',
Ruslan Zakirov's avatar
Ruslan Zakirov committed
429
430
431
        );
        $sb->_CloseParen;
        return;
432
433
    }

434
435
    my $is_local = 1;
    if ( $is_null ) {
Ruslan Zakirov's avatar
Ruslan Zakirov committed
436
437
        $op = ($op =~ /^(=|IS)$/)? 'IS': 'IS NOT';
    }
438
    elsif ( $value =~ /\D/ ) {
Ruslan Zakirov's avatar
Ruslan Zakirov committed
439
440
441
442
        $is_local = 0;
    }
    $matchfield = "Local$matchfield" if $is_local;

443
444
445
446
#For doing a left join to find "unlinked tickets" we want to generate a query that looks like this
#    SELECT main.* FROM Tickets main
#        LEFT JOIN Links Links_1 ON (     (Links_1.Type = 'MemberOf')
#                                      AND(main.id = Links_1.LocalTarget))
Ruslan Zakirov's avatar
Ruslan Zakirov committed
447
#        WHERE Links_1.LocalBase IS NULL;
448

449
    if ( $is_null ) {
450
451
452
453
454
455
456
457
458
459
460
461
        my $linkalias = $sb->Join(
            TYPE   => 'LEFT',
            ALIAS1 => 'main',
            FIELD1 => 'id',
            TABLE2 => 'Links',
            FIELD2 => 'Local' . $linkfield
        );
        $sb->SUPER::Limit(
            LEFTJOIN => $linkalias,
            FIELD    => 'Type',
            OPERATOR => '=',
            VALUE    => $meta->[2],
462
        ) if $meta->[2];
Ruslan Zakirov's avatar
Ruslan Zakirov committed
463
        $sb->_SQLLimit(
464
465
466
467
468
469
            @rest,
            ALIAS      => $linkalias,
            FIELD      => $matchfield,
            OPERATOR   => $op,
            VALUE      => 'NULL',
            QUOTEVALUE => 0,
Ruslan Zakirov's avatar
Ruslan Zakirov committed
470
471
        );
    }
Ruslan Zakirov's avatar
Ruslan Zakirov committed
472
    else {
473
474
475
476
477
478
479
480
481
482
483
484
        my $linkalias = $sb->Join(
            TYPE   => 'LEFT',
            ALIAS1 => 'main',
            FIELD1 => 'id',
            TABLE2 => 'Links',
            FIELD2 => 'Local' . $linkfield
        );
        $sb->SUPER::Limit(
            LEFTJOIN => $linkalias,
            FIELD    => 'Type',
            OPERATOR => '=',
            VALUE    => $meta->[2],
485
        ) if $meta->[2];
486
487
        $sb->SUPER::Limit(
            LEFTJOIN => $linkalias,
Ruslan Zakirov's avatar
Ruslan Zakirov committed
488
            FIELD    => $matchfield,
Ruslan Zakirov's avatar
Ruslan Zakirov committed
489
            OPERATOR => '=',
Ruslan Zakirov's avatar
Ruslan Zakirov committed
490
            VALUE    => $value,
491
492
        );
        $sb->_SQLLimit(
493
494
495
            @rest,
            ALIAS      => $linkalias,
            FIELD      => $matchfield,
Ruslan Zakirov's avatar
Ruslan Zakirov committed
496
            OPERATOR   => $is_negative? 'IS': 'IS NOT',
497
498
            VALUE      => 'NULL',
            QUOTEVALUE => 0,
499
500
        );
    }
501
502
503
504
505
506
507
}

=head2 _DateLimit

Handle date fields.  (Created, LastTold..)

Meta Data:
Alex Vandiver's avatar
Alex Vandiver committed
508
  1: type of link.  (Probably not necessary.)
509
510
511
512

=cut

sub _DateLimit {
513
    my ( $sb, $field, $op, $value, @rest ) = @_;
514

515
    die "Invalid Date Op: $op"
Jesse Vincent's avatar
Jesse Vincent committed
516
        unless $op =~ /^(=|>|<|>=|<=)$/;
517

518
    my $meta = $FIELD_METADATA{$field};
519
    die "Incorrect Meta Data for $field"
Jesse Vincent's avatar
Jesse Vincent committed
520
        unless ( defined $meta->[1] );
521

Jesse Vincent's avatar
Jesse Vincent committed
522
523
    my $date = RT::Date->new( $sb->CurrentUser );
    $date->Set( Format => 'unknown', Value => $value );
524

525
    if ( $op eq "=" ) {
526

527
528
529
        # if we're specifying =, that means we want everything on a
        # particular single day.  in the database, we need to check for >
        # and < the edges of that day.
Jesse Vincent's avatar
Jesse Vincent committed
530

531
532
533
534
        $date->SetToMidnight( Timezone => 'server' );
        my $daystart = $date->ISO;
        $date->AddDay;
        my $dayend = $date->ISO;
Jesse Vincent's avatar
Jesse Vincent committed
535

536
        $sb->_OpenParen;
Jesse Vincent's avatar
Jesse Vincent committed
537

538
539
540
541
542
543
        $sb->_SQLLimit(
            FIELD    => $meta->[1],
            OPERATOR => ">=",
            VALUE    => $daystart,
            @rest,
        );
Jesse Vincent's avatar
Jesse Vincent committed
544

545
546
        $sb->_SQLLimit(
            FIELD    => $meta->[1],
547
            OPERATOR => "<",
548
549
550
551
            VALUE    => $dayend,
            @rest,
            ENTRYAGGREGATOR => 'AND',
        );
Jesse Vincent's avatar
Jesse Vincent committed
552

553
        $sb->_CloseParen;
Jesse Vincent's avatar
Jesse Vincent committed
554

555
556
557
558
559
    }
    else {
        $sb->_SQLLimit(
            FIELD    => $meta->[1],
            OPERATOR => $op,
560
            VALUE    => $date->ISO,
561
562
563
            @rest,
        );
    }
564
565
566
567
568
569
570
571
572
573
574
575
}

=head2 _StringLimit

Handle simple fields which are just strings.  (Subject,Type)

Meta Data:
  None

=cut

sub _StringLimit {
576
    my ( $sb, $field, $op, $value, @rest ) = @_;
577

578
579
580
    # FIXME:
    # Valid Operators:
    #  =, !=, LIKE, NOT LIKE
581
582
583
584
585
586
587
588
    if ( (!defined $value || !length $value)
        && lc($op) ne 'is' && lc($op) ne 'is not'
        && RT->Config->Get('DatabaseType') eq 'Oracle'
    ) {
        my $negative = 1 if $op eq '!=' || $op =~ /^NOT\s/;
        $op = $negative? 'IS NOT': 'IS';
        $value = 'NULL';
    }
589

590
591
592
593
594
595
596
    $sb->_SQLLimit(
        FIELD         => $field,
        OPERATOR      => $op,
        VALUE         => $value,
        CASESENSITIVE => 0,
        @rest,
    );
597
598
599
600
601
602
}

=head2 _TransDateLimit

Handle fields limiting based on Transaction Date.

603
The inpupt value must be in a format parseable by Time::ParseDate
604
605
606
607
608
609

Meta Data:
  None

=cut

610
# This routine should really be factored into translimit.
611
sub _TransDateLimit {
612
613
614
    my ( $sb, $field, $op, $value, @rest ) = @_;

    # See the comments for TransLimit, they apply here too
615

616
617
618
619
620
621
622
623
624
625
626
627
628
629
    unless ( $sb->{_sql_transalias} ) {
        $sb->{_sql_transalias} = $sb->Join(
            ALIAS1 => 'main',
            FIELD1 => 'id',
            TABLE2 => 'Transactions',
            FIELD2 => 'ObjectId',
        );
        $sb->SUPER::Limit(
            ALIAS           => $sb->{_sql_transalias},
            FIELD           => 'ObjectType',
            VALUE           => 'RT::Ticket',
            ENTRYAGGREGATOR => 'AND',
        );
    }
630
631
632

    my $date = RT::Date->new( $sb->CurrentUser );
    $date->Set( Format => 'unknown', Value => $value );
633

634
    $sb->_OpenParen;
635
    if ( $op eq "=" ) {
636

637
638
639
640
        # if we're specifying =, that means we want everything on a
        # particular single day.  in the database, we need to check for >
        # and < the edges of that day.

641
642
643
644
        $date->SetToMidnight( Timezone => 'server' );
        my $daystart = $date->ISO;
        $date->AddDay;
        my $dayend = $date->ISO;
645
646
647
648
649
650
651
652
653
654

        $sb->_SQLLimit(
            ALIAS         => $sb->{_sql_transalias},
            FIELD         => 'Created',
            OPERATOR      => ">=",
            VALUE         => $daystart,
            CASESENSITIVE => 0,
            @rest
        );
        $sb->_SQLLimit(
Jesse Vincent's avatar
Jesse Vincent committed
655
656
657
658
659
            ALIAS         => $sb->{_sql_transalias},
            FIELD         => 'Created',
            OPERATOR      => "<=",
            VALUE         => $dayend,
            CASESENSITIVE => 0,
660
661
662
663
664
665
666
667
668
669
670
671
672
673
            @rest,
            ENTRYAGGREGATOR => 'AND',
        );

    }

    # not searching for a single day
    else {

        #Search for the right field
        $sb->_SQLLimit(
            ALIAS         => $sb->{_sql_transalias},
            FIELD         => 'Created',
            OPERATOR      => $op,
674
            VALUE         => $date->ISO,
675
676
677
678
679
            CASESENSITIVE => 0,
            @rest
        );
    }

680
    $sb->_CloseParen;
681
682
683
684
685
686
687
688
689
690
691
692
693
}

=head2 _TransLimit

Limit based on the Content of a transaction or the ContentType.

Meta Data:
  none

=cut

sub _TransLimit {

694
    # Content, ContentType, Filename
695

696
697
    # If only this was this simple.  We've got to do something
    # complicated here:
698

699
700
701
702
703
    #Basically, we want to make sure that the limits apply to
    #the same attachment, rather than just another attachment
    #for the same ticket, no matter how many clauses we lump
    #on. We put them in TicketAliases so that they get nuked
    #when we redo the join.
704

705
706
707
    # In the SQL, we might have
    #       (( Content = foo ) or ( Content = bar AND Content = baz ))
    # The AND group should share the same Alias.
708

709
710
    # Actually, maybe it doesn't matter.  We use the same alias and it
    # works itself out? (er.. different.)
711

712
    # Steal more from _ProcessRestrictions
713

714
715
    # FIXME: Maybe look at the previous FooLimit call, and if it was a
    # TransLimit and EntryAggregator == AND, reuse the Aliases?
716

717
718
719
    # Or better - store the aliases on a per subclause basis - since
    # those are going to be the things we want to relate to each other,
    # anyway.
720

721
722
723
724
    # maybe we should not allow certain kinds of aggregation of these
    # clauses and do a psuedo regex instead? - the problem is getting
    # them all into the same subclause when you have (A op B op C) - the
    # way they get parsed in the tree they're in different subclauses.
725

726
    my ( $self, $field, $op, $value, %rest ) = @_;
727

728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
    unless ( $self->{_sql_transalias} ) {
        $self->{_sql_transalias} = $self->Join(
            ALIAS1 => 'main',
            FIELD1 => 'id',
            TABLE2 => 'Transactions',
            FIELD2 => 'ObjectId',
        );
        $self->SUPER::Limit(
            ALIAS           => $self->{_sql_transalias},
            FIELD           => 'ObjectType',
            VALUE           => 'RT::Ticket',
            ENTRYAGGREGATOR => 'AND',
        );
    }
    unless ( defined $self->{_sql_trattachalias} ) {
        $self->{_sql_trattachalias} = $self->_SQLJoin(
            TYPE   => 'LEFT', # not all txns have an attachment
            ALIAS1 => $self->{_sql_transalias},
            FIELD1 => 'id',
            TABLE2 => 'Attachments',
            FIELD2 => 'TransactionId',
        );
    }
751

752
    #Search for the right field
753
    if ( $field eq 'Content' and RT->Config->Get('DontSearchFileAttachments') ) {
754
755
756
        $self->_OpenParen;
        $self->_SQLLimit(
			%rest,
757
758
759
760
761
			ALIAS         => $self->{_sql_trattachalias},
			FIELD         => $field,
			OPERATOR      => $op,
			VALUE         => $value,
			CASESENSITIVE => 0,
762
763
		       );
        $self->_SQLLimit(
764
			ENTRYAGGREGATOR => 'AND',
765
766
767
768
			ALIAS           => $self->{_sql_trattachalias},
			FIELD           => 'Filename',
			OPERATOR        => 'IS',
			VALUE           => 'NULL',
769
		       );
770
        $self->_CloseParen;
771
    } else {
Ruslan Zakirov's avatar
Ruslan Zakirov committed
772
        $self->_SQLLimit(
773
			%rest,
774
775
776
777
778
			ALIAS         => $self->{_sql_trattachalias},
			FIELD         => $field,
			OPERATOR      => $op,
			VALUE         => $value,
			CASESENSITIVE => 0,
Ruslan Zakirov's avatar
minor    
Ruslan Zakirov committed
779
        );
780
    }
781

782
783
784
785
786
787
788
789
790
791
792
793

}

=head2 _WatcherLimit

Handle watcher limits.  (Requestor, CC, etc..)

Meta Data:
  1: Field to query on



Jesse Vincent's avatar
Jesse Vincent committed
794
=cut
795

Jesse Vincent's avatar
Jesse Vincent committed
796
797
798
799
800
801
802
sub _WatcherLimit {
    my $self  = shift;
    my $field = shift;
    my $op    = shift;
    my $value = shift;
    my %rest  = (@_);

803
804
    my $meta = $FIELD_METADATA{ $field };
    my $type = $meta->[1] || '';
805
    my $class = $meta->[2] || 'Ticket';
Jesse Vincent's avatar
Jesse Vincent committed
806

Ruslan Zakirov's avatar
Changes    
Ruslan Zakirov committed
807
808
809
    # Owner was ENUM field, so "Owner = 'xxx'" allowed user to
    # search by id and Name at the same time, this is workaround
    # to preserve backward compatibility
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
    if ( $field eq 'Owner' ) {
        if ( $op =~ /^!?=$/ && (!$rest{'SUBKEY'} || $rest{'SUBKEY'} eq 'Name' || $rest{'SUBKEY'} eq 'EmailAddress') ) {
            my $o = RT::User->new( $self->CurrentUser );
            my $method = ($rest{'SUBKEY'}||'') eq 'EmailAddress' ? 'LoadByEmail': 'Load';
            $o->$method( $value );
            $self->_SQLLimit(
                FIELD    => 'Owner',
                OPERATOR => $op,
                VALUE    => $o->id,
                %rest,
            );
            return;
        }
        if ( ($rest{'SUBKEY'}||'') eq 'id' ) {
            $self->_SQLLimit(
                FIELD    => 'Owner',
                OPERATOR => $op,
                VALUE    => $value,
                %rest,
            );
            return;
        }
Ruslan Zakirov's avatar
Changes    
Ruslan Zakirov committed
832
    }
833
    $rest{SUBKEY} ||= 'EmailAddress';
Ruslan Zakirov's avatar
Changes    
Ruslan Zakirov committed
834

835
    my $groups = $self->_RoleGroupsJoin( Type => $type, Class => $class );
Jesse Vincent's avatar
Jesse Vincent committed
836

Ruslan Zakirov's avatar
Changes    
Ruslan Zakirov committed
837
    $self->_OpenParen;
838
839
    if ( $op =~ /^IS(?: NOT)?$/ ) {
        my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
840
841
        # to avoid joining the table Users into the query, we just join GM
        # and make sure we don't match records where group is member of itself
842
843
844
845
846
847
848
        $self->SUPER::Limit(
            LEFTJOIN   => $group_members,
            FIELD      => 'GroupId',
            OPERATOR   => '!=',
            VALUE      => "$group_members.MemberId",
            QUOTEVALUE => 0,
        );
Jesse Vincent's avatar
Jesse Vincent committed
849
        $self->_SQLLimit(
850
851
852
            ALIAS         => $group_members,
            FIELD         => 'GroupId',
            OPERATOR      => $op,
Jesse Vincent's avatar
Jesse Vincent committed
853
            VALUE         => $value,
854
855
856
857
858
859
860
861
862
863
864
865
866
867
            %rest,
        );
    }
    elsif ( $op =~ /^!=$|^NOT\s+/i ) {
        # reverse op
        $op =~ s/!|NOT\s+//i;

        # XXX: we have no way to build correct "Watcher.X != 'Y'" when condition
        # "X = 'Y'" matches more then one user so we try to fetch two records and
        # do the right thing when there is only one exist and semi-working solution
        # otherwise.
        my $users_obj = RT::Users->new( $self->CurrentUser );
        $users_obj->Limit(
            FIELD         => $rest{SUBKEY},
Jesse Vincent's avatar
Jesse Vincent committed
868
            OPERATOR      => $op,
869
            VALUE         => $value,
870
        );
871
872
873
874
875
876
877
878
879
880
881
882
883
884
        $users_obj->OrderBy;
        $users_obj->RowsPerPage(2);
        my @users = @{ $users_obj->ItemsArrayRef };

        my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
        if ( @users <= 1 ) {
            my $uid = 0;
            $uid = $users[0]->id if @users;
            $self->SUPER::Limit(
                LEFTJOIN      => $group_members,
                ALIAS         => $group_members,
                FIELD         => 'MemberId',
                VALUE         => $uid,
            );
Ruslan Zakirov's avatar
Changes    
Ruslan Zakirov committed
885
            $self->_SQLLimit(
886
887
888
                %rest,
                ALIAS           => $group_members,
                FIELD           => 'id',
Ruslan Zakirov's avatar
Changes    
Ruslan Zakirov committed
889
890
891
                OPERATOR        => 'IS',
                VALUE           => 'NULL',
            );
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
        } else {
            $self->SUPER::Limit(
                LEFTJOIN   => $group_members,
                FIELD      => 'GroupId',
                OPERATOR   => '!=',
                VALUE      => "$group_members.MemberId",
                QUOTEVALUE => 0,
            );
            my $users = $self->Join(
                TYPE            => 'LEFT',
                ALIAS1          => $group_members,
                FIELD1          => 'MemberId',
                TABLE2          => 'Users',
                FIELD2          => 'id',
            );
            $self->SUPER::Limit(
                LEFTJOIN      => $users,
                ALIAS         => $users,
                FIELD         => $rest{SUBKEY},
                OPERATOR      => $op,
                VALUE         => $value,
                CASESENSITIVE => 0,
            );
            $self->_SQLLimit(
                %rest,
                ALIAS         => $users,
                FIELD         => 'id',
                OPERATOR      => 'IS',
                VALUE         => 'NULL',
            );
Ruslan Zakirov's avatar
Changes    
Ruslan Zakirov committed
922
        }
923
    } else {
924
925
926
        my $group_members = $self->_GroupMembersJoin(
            GroupsAlias => $groups,
            New => 0,
927
        );
928
929
930
931
932
933
934
935
936
937
938
939

        my $users = $self->{'_sql_u_watchers_aliases'}{$group_members};
        unless ( $users ) {
            $users = $self->{'_sql_u_watchers_aliases'}{$group_members} = 
                $self->NewAlias('Users');
            $self->SUPER::Limit(
                LEFTJOIN      => $group_members,
                ALIAS         => $group_members,
                FIELD         => 'MemberId',
                VALUE         => "$users.id",
                QUOTEVALUE    => 0,
            );
Ruslan Zakirov's avatar
Changes    
Ruslan Zakirov committed
940
        }
941

942
943
944
945
946
947
948
        # we join users table without adding some join condition between tables,
        # the only conditions we have are conditions on the table iteslf,
        # for example Users.EmailAddress = 'x'. We should add this condition to
        # the top level of the query and bundle it with another similar conditions,
        # for example "Users.EmailAddress = 'x' OR Users.EmailAddress = 'Y'".
        # To achive this goal we use own SUBCLAUSE for conditions on the users table.
        $self->SUPER::Limit(
949
            %rest,
950
951
952
953
954
955
            SUBCLAUSE       => '_sql_u_watchers_'. $users,
            ALIAS           => $users,
            FIELD           => $rest{'SUBKEY'},
            VALUE           => $value,
            OPERATOR        => $op,
            CASESENSITIVE   => 0,
956
        );
957
958
959
        # A condition which ties Users and Groups (role groups) is a left join condition
        # of CachedGroupMembers table. To get correct results of the query we check
        # if there are matches in CGM table or not using 'cgm.id IS NOT NULL'.
960
        $self->_SQLLimit(
961
            %rest,
962
963
964
965
966
            ALIAS           => $group_members,
            FIELD           => 'id',
            OPERATOR        => 'IS NOT',
            VALUE           => 'NULL',
        );
Ruslan Zakirov's avatar
Changes    
Ruslan Zakirov committed
967
    }
968
969
970
    $self->_CloseParen;
}

971
sub _RoleGroupsJoin {
Jesse Vincent's avatar
Jesse Vincent committed
972
    my $self = shift;
973
974
975
976
    my %args = (New => 0, Class => 'Ticket', Type => '', @_);
    return $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
        if $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
           && !$args{'New'};
977
978

    # we always have watcher groups for ticket, so we use INNER join
979
    my $groups = $self->Join(
Jesse Vincent's avatar
Jesse Vincent committed
980
        ALIAS1          => 'main',
981
        FIELD1          => $args{'Class'} eq 'Queue'? 'Queue': 'id',
Jesse Vincent's avatar
Jesse Vincent committed
982
983
        TABLE2          => 'Groups',
        FIELD2          => 'Instance',
984
        ENTRYAGGREGATOR => 'AND',
985
    );
986
    $self->SUPER::Limit(
987
        LEFTJOIN        => $groups,
988
989
        ALIAS           => $groups,
        FIELD           => 'Domain',
990
        VALUE           => 'RT::'. $args{'Class'} .'-Role',
991
    );
992
    $self->SUPER::Limit(
993
        LEFTJOIN        => $groups,
994
995
        ALIAS           => $groups,
        FIELD           => 'Type',
996
997
        VALUE           => $args{'Type'},
    ) if $args{'Type'};
Ruslan Zakirov's avatar
Changes    
Ruslan Zakirov committed
998

999
    $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} } = $groups
1000
        unless $args{'New'};
For faster browsing, not all history is shown. View entire blame