1 # BEGIN BPS TAGGED BLOCK {{{
5 # This software is Copyright (c) 1996-2015 Best Practical Solutions, LLC
6 # <sales@bestpractical.com>
8 # (Except where explicitly superseded by other copyright notices)
13 # This work is made available to you under the terms of Version 2 of
14 # the GNU General Public License. A copy of that license should have
15 # been provided with this software, but in any event can be snarfed
18 # This work is distributed in the hope that it will be useful, but
19 # WITHOUT ANY WARRANTY; without even the implied warranty of
20 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
21 # General Public License for more details.
23 # You should have received a copy of the GNU General Public License
24 # along with this program; if not, write to the Free Software
25 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
26 # 02110-1301 or visit their web page on the internet at
27 # http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
30 # CONTRIBUTION SUBMISSION POLICY:
32 # (The following paragraph is not intended to limit the rights granted
33 # to you to modify and distribute this software under the terms of
34 # the GNU General Public License and is only of importance to you if
35 # you choose to contribute your changes and enhancements to the
36 # community by submitting them to Best Practical Solutions, LLC.)
38 # By intentionally submitting any modifications, corrections or
39 # derivatives to this work, or any other work intended for use with
40 # Request Tracker, to Best Practical Solutions, LLC, you confirm that
41 # you are the copyright holder for those contributions and you grant
42 # Best Practical Solutions, LLC a nonexclusive, worldwide, irrevocable,
43 # royalty-free, perpetual, license to use, copy, create derivative
44 # works based on those contributions, and sublicense and distribute
45 # those contributions and any derivatives thereof.
47 # END BPS TAGGED BLOCK }}}
51 RT::Tickets - A collection of Ticket objects
57 my $tickets = RT::Tickets->new($CurrentUser);
61 A collection of RT::Tickets.
73 use base 'RT::SearchBuilder';
75 use Role::Basic 'with';
76 with 'RT::SearchBuilder::Role::Roles';
78 use Scalar::Util qw/blessed/;
83 sub Table { 'Tickets'}
87 __PACKAGE__->RegisterCustomFieldJoin(@$_) for
88 [ "RT::Transaction" => sub { $_[0]->JoinTransactions } ],
89 [ "RT::Queue" => sub {
90 # XXX: Could avoid join and use main.Queue with some refactoring?
91 return $_[0]->{_sql_aliases}{queues} ||= $_[0]->Join(
100 # Configuration Tables:
102 # FIELD_METADATA is a mapping of searchable Field name, to Type, and other
105 our %FIELD_METADATA = (
106 Status => [ 'STRING', ], #loc_left_pair
107 Queue => [ 'ENUM' => 'Queue', ], #loc_left_pair
108 Type => [ 'ENUM', ], #loc_left_pair
109 Creator => [ 'ENUM' => 'User', ], #loc_left_pair
110 LastUpdatedBy => [ 'ENUM' => 'User', ], #loc_left_pair
111 Owner => [ 'WATCHERFIELD' => 'Owner', ], #loc_left_pair
112 EffectiveId => [ 'INT', ], #loc_left_pair
113 id => [ 'ID', ], #loc_left_pair
114 InitialPriority => [ 'INT', ], #loc_left_pair
115 FinalPriority => [ 'INT', ], #loc_left_pair
116 Priority => [ 'INT', ], #loc_left_pair
117 TimeLeft => [ 'INT', ], #loc_left_pair
118 TimeWorked => [ 'INT', ], #loc_left_pair
119 TimeEstimated => [ 'INT', ], #loc_left_pair
121 Linked => [ 'LINK' ], #loc_left_pair
122 LinkedTo => [ 'LINK' => 'To' ], #loc_left_pair
123 LinkedFrom => [ 'LINK' => 'From' ], #loc_left_pair
124 MemberOf => [ 'LINK' => To => 'MemberOf', ], #loc_left_pair
125 DependsOn => [ 'LINK' => To => 'DependsOn', ], #loc_left_pair
126 RefersTo => [ 'LINK' => To => 'RefersTo', ], #loc_left_pair
127 HasMember => [ 'LINK' => From => 'MemberOf', ], #loc_left_pair
128 DependentOn => [ 'LINK' => From => 'DependsOn', ], #loc_left_pair
129 DependedOnBy => [ 'LINK' => From => 'DependsOn', ], #loc_left_pair
130 ReferredToBy => [ 'LINK' => From => 'RefersTo', ], #loc_left_pair
131 Told => [ 'DATE' => 'Told', ], #loc_left_pair
132 Starts => [ 'DATE' => 'Starts', ], #loc_left_pair
133 Started => [ 'DATE' => 'Started', ], #loc_left_pair
134 Due => [ 'DATE' => 'Due', ], #loc_left_pair
135 Resolved => [ 'DATE' => 'Resolved', ], #loc_left_pair
136 LastUpdated => [ 'DATE' => 'LastUpdated', ], #loc_left_pair
137 Created => [ 'DATE' => 'Created', ], #loc_left_pair
138 Subject => [ 'STRING', ], #loc_left_pair
139 Content => [ 'TRANSCONTENT', ], #loc_left_pair
140 ContentType => [ 'TRANSFIELD', ], #loc_left_pair
141 Filename => [ 'TRANSFIELD', ], #loc_left_pair
142 TransactionDate => [ 'TRANSDATE', ], #loc_left_pair
143 Requestor => [ 'WATCHERFIELD' => 'Requestor', ], #loc_left_pair
144 Requestors => [ 'WATCHERFIELD' => 'Requestor', ], #loc_left_pair
145 Cc => [ 'WATCHERFIELD' => 'Cc', ], #loc_left_pair
146 AdminCc => [ 'WATCHERFIELD' => 'AdminCc', ], #loc_left_pair
147 Watcher => [ 'WATCHERFIELD', ], #loc_left_pair
148 QueueCc => [ 'WATCHERFIELD' => 'Cc' => 'Queue', ], #loc_left_pair
149 QueueAdminCc => [ 'WATCHERFIELD' => 'AdminCc' => 'Queue', ], #loc_left_pair
150 QueueWatcher => [ 'WATCHERFIELD' => undef => 'Queue', ], #loc_left_pair
151 CustomFieldValue => [ 'CUSTOMFIELD' => 'Ticket' ], #loc_left_pair
152 CustomField => [ 'CUSTOMFIELD' => 'Ticket' ], #loc_left_pair
153 CF => [ 'CUSTOMFIELD' => 'Ticket' ], #loc_left_pair
154 TxnCF => [ 'CUSTOMFIELD' => 'Transaction' ], #loc_left_pair
155 TransactionCF => [ 'CUSTOMFIELD' => 'Transaction' ], #loc_left_pair
156 QueueCF => [ 'CUSTOMFIELD' => 'Queue' ], #loc_left_pair
157 Lifecycle => [ 'LIFECYCLE' ], #loc_left_pair
158 Updated => [ 'TRANSDATE', ], #loc_left_pair
159 UpdatedBy => [ 'TRANSCREATOR', ], #loc_left_pair
160 OwnerGroup => [ 'MEMBERSHIPFIELD' => 'Owner', ], #loc_left_pair
161 RequestorGroup => [ 'MEMBERSHIPFIELD' => 'Requestor', ], #loc_left_pair
162 CCGroup => [ 'MEMBERSHIPFIELD' => 'Cc', ], #loc_left_pair
163 AdminCCGroup => [ 'MEMBERSHIPFIELD' => 'AdminCc', ], #loc_left_pair
164 WatcherGroup => [ 'MEMBERSHIPFIELD', ], #loc_left_pair
165 HasAttribute => [ 'HASATTRIBUTE', 1 ],
166 HasNoAttribute => [ 'HASATTRIBUTE', 0 ],
168 Customer => [ 'FREESIDEFIELD' => 'Customer' ],
169 Service => [ 'FREESIDEFIELD' => 'Service' ],
170 WillResolve => [ 'DATE' => 'WillResolve', ], #loc_left_pair
173 # Lower Case version of FIELDS, for case insensitivity
174 our %LOWER_CASE_FIELDS = map { ( lc($_) => $_ ) } (keys %FIELD_METADATA);
176 our %SEARCHABLE_SUBFIELDS = (
178 EmailAddress Name RealName Nickname Organization Address1 Address2
179 City State Zip Country WorkPhone HomePhone MobilePhone PagerPhone id
183 # Mapping of Field Type to Function
185 ENUM => \&_EnumLimit,
188 LINK => \&_LinkLimit,
189 DATE => \&_DateLimit,
190 STRING => \&_StringLimit,
191 TRANSFIELD => \&_TransLimit,
192 TRANSCONTENT => \&_TransContentLimit,
193 TRANSDATE => \&_TransDateLimit,
194 TRANSCREATOR => \&_TransCreatorLimit,
195 WATCHERFIELD => \&_WatcherLimit,
196 MEMBERSHIPFIELD => \&_WatcherMembershipLimit,
197 CUSTOMFIELD => \&_CustomFieldLimit,
198 HASATTRIBUTE => \&_HasAttributeLimit,
199 LIFECYCLE => \&_LifecycleLimit,
200 FREESIDEFIELD => \&_FreesideFieldLimit,
203 # Default EntryAggregator per type
204 # if you specify OP, you must specify all valid OPs
247 sub FIELDS { return \%FIELD_METADATA }
249 our @SORTFIELDS = qw(id Status
251 Owner Created Due Starts Started
253 Resolved LastUpdated Priority TimeWorked TimeLeft);
257 Returns the list of fields that lists of tickets can easily be sorted by
263 return (@SORTFIELDS);
267 # BEGIN SQL STUFF *********************************
272 $self->SUPER::CleanSlate( @_ );
273 delete $self->{$_} foreach qw(
275 _sql_group_members_aliases
276 _sql_object_cfv_alias
277 _sql_role_group_aliases
279 _sql_u_watchers_alias_for_sort
280 _sql_u_watchers_aliases
281 _sql_current_user_can_see_applied
285 =head1 Limit Helper Routines
287 These routines are the targets of a dispatch table depending on the
288 type of field. They all share the same signature:
290 my ($self,$field,$op,$value,@rest) = @_;
292 The values in @rest should be suitable for passing directly to
293 DBIx::SearchBuilder::Limit.
295 Essentially they are an expanded/broken out (and much simplified)
296 version of what ProcessRestrictions used to do. They're also much
297 more clearly delineated by the TYPE of field being processed.
306 my ( $sb, $field, $op, $value, @rest ) = @_;
308 if ( $value eq '__Bookmarked__' ) {
309 return $sb->_BookmarkLimit( $field, $op, $value, @rest );
311 return $sb->_IntLimit( $field, $op, $value, @rest );
316 my ( $sb, $field, $op, $value, @rest ) = @_;
318 die "Invalid operator $op for __Bookmarked__ search on $field"
319 unless $op =~ /^(=|!=)$/;
321 my @bookmarks = $sb->CurrentUser->UserObj->Bookmarks;
330 # as bookmarked tickets can be merged we have to use a join
331 # but it should be pretty lightweight
332 my $tickets_alias = $sb->Join(
337 FIELD2 => 'EffectiveId',
340 $op = $op eq '='? 'IN': 'NOT IN';
342 ALIAS => $tickets_alias,
345 VALUE => [ @bookmarks ],
352 Handle Fields which are limited to certain values, and potentially
353 need to be looked up from another class.
355 This subroutine actually handles two different kinds of fields. For
356 some the user is responsible for limiting the values. (i.e. Status,
359 For others, the value specified by the user will be looked by via
363 name of class to lookup in (Optional)
368 my ( $sb, $field, $op, $value, @rest ) = @_;
370 # SQL::Statement changes != to <>. (Can we remove this now?)
371 $op = "!=" if $op eq "<>";
373 die "Invalid Operation: $op for $field"
377 my $meta = $FIELD_METADATA{$field};
378 if ( defined $meta->[1] && defined $value && $value !~ /^\d+$/ ) {
379 my $class = "RT::" . $meta->[1];
380 my $o = $class->new( $sb->CurrentUser );
382 $value = $o->Id || 0;
383 } elsif ( $field eq "Type" ) {
384 $value = lc $value if $value =~ /^(ticket|approval|reminder)$/i;
396 Handle fields where the values are limited to integers. (For example,
397 Priority, TimeWorked.)
405 my ( $sb, $field, $op, $value, @rest ) = @_;
407 my $is_a_like = $op =~ /MATCHES|ENDSWITH|STARTSWITH|LIKE/i;
409 # We want to support <id LIKE '1%'> for ticket autocomplete,
410 # but we need to explicitly typecast on Postgres
411 if ( $is_a_like && RT->Config->Get('DatabaseType') eq 'Pg' ) {
413 FUNCTION => "CAST(main.$field AS TEXT)",
430 Handle fields which deal with links between tickets. (MemberOf, DependsOn)
433 1: Direction (From, To)
434 2: Link Type (MemberOf, DependsOn, RefersTo)
439 my ( $sb, $field, $op, $value, @rest ) = @_;
441 my $meta = $FIELD_METADATA{$field};
442 die "Invalid Operator $op for $field" unless $op =~ /^(=|!=|IS|IS NOT)$/io;
445 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
449 $is_null = 1 if !$value || $value =~ /^null$/io;
451 my $direction = $meta->[1] || '';
452 my ($matchfield, $linkfield) = ('', '');
453 if ( $direction eq 'To' ) {
454 ($matchfield, $linkfield) = ("Target", "Base");
456 elsif ( $direction eq 'From' ) {
457 ($matchfield, $linkfield) = ("Base", "Target");
459 elsif ( $direction ) {
460 die "Invalid link direction '$direction' for $field\n";
463 $sb->_LinkLimit( 'LinkedTo', $op, $value, @rest );
465 'LinkedFrom', $op, $value, @rest,
466 ENTRYAGGREGATOR => (($is_negative && $is_null) || (!$is_null && !$is_negative))? 'OR': 'AND',
474 $op = ($op =~ /^(=|IS)$/i)? 'IS': 'IS NOT';
476 elsif ( $value =~ /\D/ ) {
477 $value = RT::URI->new( $sb->CurrentUser )->CanonicalizeURI( $value );
480 $matchfield = "Local$matchfield" if $is_local;
482 #For doing a left join to find "unlinked tickets" we want to generate a query that looks like this
483 # SELECT main.* FROM Tickets main
484 # LEFT JOIN Links Links_1 ON ( (Links_1.Type = 'MemberOf')
485 # AND(main.id = Links_1.LocalTarget))
486 # WHERE Links_1.LocalBase IS NULL;
489 my $linkalias = $sb->Join(
494 FIELD2 => 'Local' . $linkfield
497 LEFTJOIN => $linkalias,
505 FIELD => $matchfield,
512 my $linkalias = $sb->Join(
517 FIELD2 => 'Local' . $linkfield
520 LEFTJOIN => $linkalias,
526 LEFTJOIN => $linkalias,
527 FIELD => $matchfield,
534 FIELD => $matchfield,
535 OPERATOR => $is_negative? 'IS': 'IS NOT',
544 Handle date fields. (Created, LastTold..)
547 1: type of link. (Probably not necessary.)
552 my ( $sb, $field, $op, $value, %rest ) = @_;
554 die "Invalid Date Op: $op"
555 unless $op =~ /^(=|>|<|>=|<=|IS(\s+NOT)?)$/i;
557 my $meta = $FIELD_METADATA{$field};
558 die "Incorrect Meta Data for $field"
559 unless ( defined $meta->[1] );
561 $sb->_DateFieldLimit( $meta->[1], $op, $value, %rest );
564 # Factor this out for use by custom fields
566 sub _DateFieldLimit {
567 my ( $sb, $field, $op, $value, %rest ) = @_;
569 if ( $op =~ /^(IS(\s+NOT)?)$/i) {
571 FUNCTION => $sb->NotSetDateToNullFunction,
579 if ( my $subkey = $rest{SUBKEY} ) {
580 if ( $subkey eq 'DayOfWeek' && $op !~ /IS/i && $value =~ /[^0-9]/ ) {
581 for ( my $i = 0; $i < @RT::Date::DAYS_OF_WEEK; $i++ ) {
582 # Use a case-insensitive regex for better matching across
583 # locales since we don't have fc() and lc() is worse. Really
584 # we should be doing Unicode normalization too, but we don't do
585 # that elsewhere in RT.
587 # XXX I18N: Replace the regex with fc() once we're guaranteed 5.16.
588 next unless lc $RT::Date::DAYS_OF_WEEK[ $i ] eq lc $value
589 or $sb->CurrentUser->loc($RT::Date::DAYS_OF_WEEK[ $i ]) =~ /^\Q$value\E$/i;
593 return $sb->Limit( FIELD => 'id', VALUE => 0, %rest )
594 if $value =~ /[^0-9]/;
596 elsif ( $subkey eq 'Month' && $op !~ /IS/i && $value =~ /[^0-9]/ ) {
597 for ( my $i = 0; $i < @RT::Date::MONTHS; $i++ ) {
598 # Use a case-insensitive regex for better matching across
599 # locales since we don't have fc() and lc() is worse. Really
600 # we should be doing Unicode normalization too, but we don't do
601 # that elsewhere in RT.
603 # XXX I18N: Replace the regex with fc() once we're guaranteed 5.16.
604 next unless lc $RT::Date::MONTHS[ $i ] eq lc $value
605 or $sb->CurrentUser->loc($RT::Date::MONTHS[ $i ]) =~ /^\Q$value\E$/i;
607 $value = $i + 1; last;
609 return $sb->Limit( FIELD => 'id', VALUE => 0, %rest )
610 if $value =~ /[^0-9]/;
614 if ( RT->Config->Get('ChartsTimezonesInDB') ) {
615 my $to = $sb->CurrentUser->UserObj->Timezone
616 || RT->Config->Get('Timezone');
617 $tz = { From => 'UTC', To => $to }
618 if $to && lc $to ne 'utc';
621 # $subkey is validated by DateTimeFunction
622 my $function = $RT::Handle->DateTimeFunction(
624 Field => $sb->NotSetDateToNullFunction,
629 FUNCTION => $function,
637 my $date = RT::Date->new( $sb->CurrentUser );
638 $date->Set( Format => 'unknown', Value => $value );
642 # if we're specifying =, that means we want everything on a
643 # particular single day. in the database, we need to check for >
644 # and < the edges of that day.
646 # Except if the value is 'this month' or 'last month', check
647 # > and < the edges of the month.
649 my ($daystart, $dayend);
650 if ( lc($value) eq 'this month' ) {
652 $date->SetToStart('month', Timezone => 'server');
653 $daystart = $date->ISO;
654 $date->AddMonth(Timezone => 'server');
655 $dayend = $date->ISO;
657 elsif ( lc($value) eq 'last month' ) {
659 $date->SetToStart('month', Timezone => 'server');
660 $dayend = $date->ISO;
662 $date->SetToStart('month', Timezone => 'server');
663 $daystart = $date->ISO;
666 $date->SetToMidnight( Timezone => 'server' );
667 $daystart = $date->ISO;
669 $dayend = $date->ISO;
686 ENTRYAGGREGATOR => 'AND',
694 FUNCTION => $sb->NotSetDateToNullFunction,
705 Handle simple fields which are just strings. (Subject,Type)
713 my ( $sb, $field, $op, $value, @rest ) = @_;
717 # =, !=, LIKE, NOT LIKE
718 if ( RT->Config->Get('DatabaseType') eq 'Oracle'
719 && (!defined $value || !length $value)
720 && lc($op) ne 'is' && lc($op) ne 'is not'
722 if ($op eq '!=' || $op =~ /^NOT\s/i) {
730 if ($field eq "Status") {
743 =head2 _TransDateLimit
745 Handle fields limiting based on Transaction Date.
747 The inpupt value must be in a format parseable by Time::ParseDate
754 # This routine should really be factored into translimit.
755 sub _TransDateLimit {
756 my ( $sb, $field, $op, $value, @rest ) = @_;
758 # See the comments for TransLimit, they apply here too
760 my $txn_alias = $sb->JoinTransactions;
762 my $date = RT::Date->new( $sb->CurrentUser );
763 $date->Set( Format => 'unknown', Value => $value );
768 # if we're specifying =, that means we want everything on a
769 # particular single day. in the database, we need to check for >
770 # and < the edges of that day.
772 $date->SetToMidnight( Timezone => 'server' );
773 my $daystart = $date->ISO;
775 my $dayend = $date->ISO;
790 ENTRYAGGREGATOR => 'AND',
795 # not searching for a single day
798 #Search for the right field
811 sub _TransCreatorLimit {
812 my ( $sb, $field, $op, $value, @rest ) = @_;
813 $op = "!=" if $op eq "<>";
814 die "Invalid Operation: $op for $field" unless $op eq "=" or $op eq "!=";
816 # See the comments for TransLimit, they apply here too
817 my $txn_alias = $sb->JoinTransactions;
818 if ( defined $value && $value !~ /^\d+$/ ) {
819 my $u = RT::User->new( $sb->CurrentUser );
821 $value = $u->id || 0;
823 $sb->Limit( ALIAS => $txn_alias, FIELD => 'Creator', OPERATOR => $op, VALUE => $value, @rest );
828 Limit based on the ContentType or the Filename of a transaction.
833 my ( $self, $field, $op, $value, %rest ) = @_;
835 my $txn_alias = $self->JoinTransactions;
836 unless ( defined $self->{_sql_trattachalias} ) {
837 $self->{_sql_trattachalias} = $self->Join(
838 TYPE => 'LEFT', # not all txns have an attachment
839 ALIAS1 => $txn_alias,
841 TABLE2 => 'Attachments',
842 FIELD2 => 'TransactionId',
848 ALIAS => $self->{_sql_trattachalias},
856 =head2 _TransContentLimit
858 Limit based on the Content of a transaction.
862 sub _TransContentLimit {
866 # If only this was this simple. We've got to do something
869 #Basically, we want to make sure that the limits apply to
870 #the same attachment, rather than just another attachment
871 #for the same ticket, no matter how many clauses we lump
874 # In the SQL, we might have
875 # (( Content = foo ) or ( Content = bar AND Content = baz ))
876 # The AND group should share the same Alias.
878 # Actually, maybe it doesn't matter. We use the same alias and it
879 # works itself out? (er.. different.)
881 # Steal more from _ProcessRestrictions
883 # FIXME: Maybe look at the previous FooLimit call, and if it was a
884 # TransLimit and EntryAggregator == AND, reuse the Aliases?
886 # Or better - store the aliases on a per subclause basis - since
887 # those are going to be the things we want to relate to each other,
890 # maybe we should not allow certain kinds of aggregation of these
891 # clauses and do a psuedo regex instead? - the problem is getting
892 # them all into the same subclause when you have (A op B op C) - the
893 # way they get parsed in the tree they're in different subclauses.
895 my ( $self, $field, $op, $value, %rest ) = @_;
896 $field = 'Content' if $field =~ /\W/;
898 my $config = RT->Config->Get('FullTextSearch') || {};
899 unless ( $config->{'Enable'} ) {
900 $self->Limit( %rest, FIELD => 'id', VALUE => 0 );
904 my $txn_alias = $self->JoinTransactions;
905 unless ( defined $self->{_sql_trattachalias} ) {
906 $self->{_sql_trattachalias} = $self->Join(
907 TYPE => 'LEFT', # not all txns have an attachment
908 ALIAS1 => $txn_alias,
910 TABLE2 => 'Attachments',
911 FIELD2 => 'TransactionId',
916 if ( $config->{'Indexed'} ) {
917 my $db_type = RT->Config->Get('DatabaseType');
920 if ( $config->{'Table'} and $config->{'Table'} ne "Attachments") {
921 $alias = $self->{'_sql_aliases'}{'full_text'} ||= $self->Join(
923 ALIAS1 => $self->{'_sql_trattachalias'},
925 TABLE2 => $config->{'Table'},
929 $alias = $self->{'_sql_trattachalias'};
932 #XXX: handle negative searches
933 my $index = $config->{'Column'};
934 if ( $db_type eq 'Oracle' ) {
935 my $dbh = $RT::Handle->dbh;
936 my $alias = $self->{_sql_trattachalias};
939 FUNCTION => "CONTAINS( $alias.$field, ".$dbh->quote($value) .")",
945 # this is required to trick DBIx::SB's LEFT JOINS optimizer
946 # into deciding that join is redundant as it is
948 ENTRYAGGREGATOR => 'AND',
949 ALIAS => $self->{_sql_trattachalias},
951 OPERATOR => 'IS NOT',
955 elsif ( $db_type eq 'Pg' ) {
956 my $dbh = $RT::Handle->dbh;
962 VALUE => 'plainto_tsquery('. $dbh->quote($value) .')',
966 elsif ( $db_type eq 'mysql' and not $config->{Sphinx}) {
967 my $dbh = $RT::Handle->dbh;
970 FUNCTION => "MATCH($alias.Content)",
971 OPERATOR => 'AGAINST',
972 VALUE => "(". $dbh->quote($value) ." IN BOOLEAN MODE)",
975 # As with Oracle, above, this forces the LEFT JOINs into
976 # JOINS, which allows the FULLTEXT index to be used.
977 # Orthogonally, the IS NOT NULL clause also helps the
978 # optimizer decide to use the index.
980 ENTRYAGGREGATOR => 'AND',
983 OPERATOR => 'IS NOT',
988 elsif ( $db_type eq 'mysql' ) {
989 # XXX: We could theoretically skip the join to Attachments,
990 # and have Sphinx simply index and group by the TicketId,
991 # and join Ticket.id to that attribute, which would be much
992 # more efficient -- however, this is only a possibility if
993 # there are no other transaction limits.
995 # This is a special character. Note that \ does not escape
996 # itself (in Sphinx 2.1.0, at least), so 'foo\;bar' becoming
997 # 'foo\\;bar' is not a vulnerability, and is still parsed as
998 # "foo, \, ;, then bar". Happily, the default mode is
999 # "all", meaning that boolean operators are not special.
1000 $value =~ s/;/\\;/g;
1002 my $max = $config->{'MaxMatches'};
1008 VALUE => "$value;limit=$max;maxmatches=$max",
1014 ALIAS => $self->{_sql_trattachalias},
1021 if ( RT->Config->Get('DontSearchFileAttachments') ) {
1023 ENTRYAGGREGATOR => 'AND',
1024 ALIAS => $self->{_sql_trattachalias},
1025 FIELD => 'Filename',
1033 =head2 _WatcherLimit
1035 Handle watcher limits. (Requestor, CC, etc..)
1038 1: Field to query on
1051 my $meta = $FIELD_METADATA{ $field };
1052 my $type = $meta->[1] || '';
1053 my $class = $meta->[2] || 'Ticket';
1055 # Bail if the subfield is not allowed
1057 and not grep { $_ eq $rest{SUBKEY} } @{$SEARCHABLE_SUBFIELDS{'User'}})
1059 die "Invalid watcher subfield: '$rest{SUBKEY}'";
1064 CLASS => "RT::$class",
1065 FIELD => $rest{SUBKEY},
1068 SUBCLAUSE => "ticketsql",
1073 =head2 _WatcherMembershipLimit
1075 Handle watcher membership limits, i.e. whether the watcher belongs to a
1076 specific group or not.
1083 sub _WatcherMembershipLimit {
1084 my ( $self, $field, $op, $value, %rest ) = @_;
1086 # we don't support anything but '='
1087 die "Invalid $field Op: $op"
1088 unless $op =~ /^=$/;
1090 unless ( $value =~ /^\d+$/ ) {
1091 my $group = RT::Group->new( $self->CurrentUser );
1092 $group->LoadUserDefinedGroup( $value );
1093 $value = $group->id || 0;
1096 my $meta = $FIELD_METADATA{$field};
1097 my $type = $meta->[1] || '';
1099 my ($members_alias, $members_column);
1100 if ( $type eq 'Owner' ) {
1101 ($members_alias, $members_column) = ('main', 'Owner');
1103 (undef, undef, $members_alias) = $self->_WatcherJoin( New => 1, Name => $type );
1104 $members_column = 'id';
1107 my $cgm_alias = $self->Join(
1108 ALIAS1 => $members_alias,
1109 FIELD1 => $members_column,
1110 TABLE2 => 'CachedGroupMembers',
1111 FIELD2 => 'MemberId',
1114 LEFTJOIN => $cgm_alias,
1115 ALIAS => $cgm_alias,
1116 FIELD => 'Disabled',
1121 ALIAS => $cgm_alias,
1129 =head2 _CustomFieldDecipher
1131 Try and turn a CF descriptor into (cfid, cfname) object pair.
1133 Takes an optional second parameter of the CF LookupType, defaults to Ticket CFs.
1137 sub _CustomFieldDecipher {
1138 my ($self, $string, $lookuptype) = @_;
1139 $lookuptype ||= $self->_SingularClass->CustomFieldLookupType;
1141 my ($object, $field, $column) = ($string =~ /^(?:(.+?)\.)?\{(.+)\}(?:\.(Content|LargeContent))?$/);
1142 $field ||= ($string =~ /^\{(.*?)\}$/)[0] || $string;
1144 my ($cf, $applied_to);
1147 my $record_class = RT::CustomField->RecordClassFromLookupType($lookuptype);
1148 $applied_to = $record_class->new( $self->CurrentUser );
1149 $applied_to->Load( $object );
1151 if ( $applied_to->id ) {
1152 RT->Logger->debug("Limiting to CFs identified by '$field' applied to $record_class #@{[$applied_to->id]} (loaded via '$object')");
1155 RT->Logger->warning("$record_class '$object' doesn't exist, parsed from '$string'");
1161 if ( $field =~ /\D/ ) {
1163 my $cfs = RT::CustomFields->new( $self->CurrentUser );
1164 $cfs->Limit( FIELD => 'Name', VALUE => $field, CASESENSITIVE => 0 );
1165 $cfs->LimitToLookupType($lookuptype);
1168 $cfs->SetContextObject($applied_to);
1169 $cfs->LimitToObjectId($applied_to->id);
1172 # if there is more then one field the current user can
1173 # see with the same name then we shouldn't return cf object
1174 # as we don't know which one to use
1177 $cf = undef if $cfs->Next;
1181 $cf = RT::CustomField->new( $self->CurrentUser );
1182 $cf->Load( $field );
1183 $cf->SetContextObject($applied_to)
1184 if $cf->id and $applied_to;
1187 return ($object, $field, $cf, $column);
1190 =head2 _CustomFieldLimit
1192 Limit based on CustomFields
1199 sub _CustomFieldLimit {
1200 my ( $self, $_field, $op, $value, %rest ) = @_;
1202 my $meta = $FIELD_METADATA{ $_field };
1203 my $class = $meta->[1] || 'Ticket';
1204 my $type = "RT::$class"->CustomFieldLookupType;
1206 my $field = $rest{'SUBKEY'} || die "No field specified";
1208 # For our sanity, we can only limit on one object at a time
1210 my ($object, $cfid, $cf, $column);
1211 ($object, $field, $cf, $column) = $self->_CustomFieldDecipher( $field, $type );
1214 $self->_LimitCustomField(
1216 LOOKUPTYPE => $type,
1217 CUSTOMFIELD => $cf || $field,
1218 KEY => $cf ? $cf->id : "$type-$object.$field",
1222 SUBCLAUSE => "ticketsql",
1226 sub _CustomFieldJoinByName {
1228 my ($ObjectAlias, $cf, $type) = @_;
1230 my ($ocfvalias, $CFs, $ocfalias) = $self->SUPER::_CustomFieldJoinByName(@_);
1232 LEFTJOIN => $ocfalias,
1233 ENTRYAGGREGATOR => 'OR',
1234 FIELD => 'ObjectId',
1235 VALUE => 'main.Queue',
1238 return ($ocfvalias, $CFs, $ocfalias);
1241 sub _HasAttributeLimit {
1242 my ( $self, $field, $op, $value, %rest ) = @_;
1244 my $alias = $self->Join(
1248 TABLE2 => 'Attributes',
1249 FIELD2 => 'ObjectId',
1253 FIELD => 'ObjectType',
1254 VALUE => 'RT::Ticket',
1255 ENTRYAGGREGATOR => 'AND'
1262 ENTRYAGGREGATOR => 'AND'
1268 OPERATOR => $FIELD_METADATA{$field}->[1]? 'IS NOT': 'IS',
1274 sub _LifecycleLimit {
1275 my ( $self, $field, $op, $value, %rest ) = @_;
1277 die "Invalid Operator $op for $field" if $op =~ /^(IS|IS NOT)$/io;
1278 my $queue = $self->{_sql_aliases}{queues} ||= $_[0]->Join(
1287 FIELD => 'Lifecycle',
1294 # End Helper Functions
1296 # End of SQL Stuff -------------------------------------------------
1299 =head2 OrderByCols ARRAY
1301 A modified version of the OrderBy method which automatically joins where
1302 C<ALIAS> is set to the name of a watcher type.
1313 foreach my $row (@args) {
1314 if ( $row->{ALIAS} ) {
1318 if ( $row->{FIELD} !~ /\./ ) {
1319 my $meta = $FIELD_METADATA{ $row->{FIELD} };
1325 if ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'Queue' ) {
1326 my $alias = $self->Join(
1329 FIELD1 => $row->{'FIELD'},
1333 push @res, { %$row, ALIAS => $alias, FIELD => "Name", CASESENSITIVE => 0 };
1334 } elsif ( ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'User' )
1335 || ( $meta->[0] eq 'WATCHERFIELD' && ($meta->[1]||'') eq 'Owner' )
1337 my $alias = $self->Join(
1340 FIELD1 => $row->{'FIELD'},
1344 push @res, { %$row, ALIAS => $alias, FIELD => "Name", CASESENSITIVE => 0 };
1351 my ( $field, $subkey ) = split /\./, $row->{FIELD}, 2;
1352 my $meta = $FIELD_METADATA{$field};
1353 if ( defined $meta->[0] && $meta->[0] eq 'WATCHERFIELD' ) {
1354 # cache alias as we want to use one alias per watcher type for sorting
1355 my $cache_key = join "-", map { $_ || "" } @$meta[1,2];
1356 my $users = $self->{_sql_u_watchers_alias_for_sort}{ $cache_key };
1358 $self->{_sql_u_watchers_alias_for_sort}{ $cache_key }
1359 = $users = ( $self->_WatcherJoin( Name => $meta->[1], Class => "RT::" . ($meta->[2] || 'Ticket') ) )[2];
1361 push @res, { %$row, ALIAS => $users, FIELD => $subkey };
1362 } elsif ( defined $meta->[0] && $meta->[0] eq 'CUSTOMFIELD' ) {
1363 my ($object, $field, $cf, $column) = $self->_CustomFieldDecipher( $subkey );
1364 my $cfkey = $cf ? $cf->id : "$object.$field";
1365 push @res, $self->_OrderByCF( $row, $cfkey, ($cf || $field) );
1366 } elsif ( $field eq "Custom" && $subkey eq "Ownership") {
1367 # PAW logic is "reversed"
1369 if (exists $row->{ORDER} ) {
1370 my $o = $row->{ORDER};
1371 delete $row->{ORDER};
1372 $order = "DESC" if $o =~ /asc/i;
1375 # Ticket.Owner 1 0 X
1376 # Unowned Tickets 0 1 X
1379 foreach my $uid ( $self->CurrentUser->Id, RT->Nobody->Id ) {
1380 if ( RT->Config->Get('DatabaseType') eq 'Oracle' ) {
1381 my $f = ($row->{'ALIAS'} || 'main') .'.Owner';
1386 FUNCTION => "CASE WHEN $f=$uid THEN 1 ELSE 0 END",
1393 FUNCTION => "Owner=$uid",
1399 push @res, { %$row, FIELD => "Priority", ORDER => $order } ;
1401 } elsif ( $field eq 'Customer' ) { #Freeside
1402 # OrderBy(FIELD => expression) doesn't work, it has to be
1403 # an actual field, so we have to do the join even if sorting
1405 my $custalias = $self->JoinToCustomer;
1406 my $cust_field = lc($subkey);
1407 if ( !$cust_field or $cust_field eq 'number' ) {
1408 $cust_field = 'custnum';
1410 elsif ( $cust_field eq 'name' ) {
1411 $cust_field = "COALESCE( $custalias.company,
1412 $custalias.last || ', ' || $custalias.first
1415 else { # order by cust_main fields directly: 'Customer.agentnum'
1416 $cust_field = $subkey;
1418 push @res, { %$row, ALIAS => $custalias, FIELD => $cust_field };
1420 } elsif ( $field eq 'Service' ) {
1422 my $svcalias = $self->JoinToService;
1423 my $svc_field = lc($subkey);
1424 if ( !$svc_field or $svc_field eq 'number' ) {
1425 $svc_field = 'svcnum';
1427 push @res, { %$row, ALIAS => $svcalias, FIELD => $svc_field };
1435 return $self->SUPER::OrderByCols(@res);
1440 RT->Deprecated( Remove => "4.4", Instead => "Limit" );
1445 RT->Deprecated( Remove => "4.4", Instead => "Join" );
1450 $_[0]->SUPER::_OpenParen( $_[1] || 'ticketsql' );
1453 $_[0]->SUPER::_CloseParen( $_[1] || 'ticketsql' );
1459 $self->{'must_redo_search'} = 1;
1460 delete $self->{'raw_rows'};
1461 delete $self->{'count_all'};
1463 if ($self->{'using_restrictions'}) {
1464 RT->Deprecated( Message => "Mixing old-style LimitFoo methods with Limit is deprecated" );
1465 $self->LimitField(@_);
1468 $args{SUBCLAUSE} ||= "ticketsql"
1469 if $self->{parsing_ticketsql} and not $args{LEFTJOIN};
1471 $self->{_sql_looking_at}{ lc $args{FIELD} } = 1
1472 if $args{FIELD} and (not $args{ALIAS} or $args{ALIAS} eq "main");
1474 $self->SUPER::Limit(%args);
1480 sub JoinToCustLinks {
1481 # Set up join to links (id = localbase),
1482 # limit link type to 'MemberOf',
1483 # and target value to any Freeside custnum URI.
1484 # Return the linkalias for further join/limit action,
1485 # and an sql expression to retrieve the custnum.
1487 # only join once for each RT::Tickets object
1488 my $linkalias = $self->{cust_main_linkalias};
1490 $linkalias = $self->Join(
1495 FIELD2 => 'LocalBase',
1497 $self->SUPER::Limit(
1498 LEFTJOIN => $linkalias,
1501 VALUE => 'fsck.com-rt://%/ticket/%',
1503 $self->SUPER::Limit(
1504 LEFTJOIN => $linkalias,
1507 VALUE => 'MemberOf',
1509 $self->SUPER::Limit(
1510 LEFTJOIN => $linkalias,
1512 OPERATOR => 'STARTSWITH',
1513 VALUE => 'freeside://freeside/cust_main/',
1515 $self->{cust_main_linkalias} = $linkalias;
1517 my $custnum_sql = "CAST(SUBSTR($linkalias.Target,31) AS ";
1518 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
1519 $custnum_sql .= 'SIGNED INTEGER)';
1522 $custnum_sql .= 'INTEGER)';
1524 return ($linkalias, $custnum_sql);
1527 sub JoinToCustomer {
1529 my ($linkalias, $custnum_sql) = $self->JoinToCustLinks;
1530 # don't reuse this join, though--negative queries need
1532 my $custalias = $self->Join(
1534 EXPRESSION => $custnum_sql,
1535 TABLE2 => 'cust_main',
1536 FIELD2 => 'custnum',
1541 sub JoinToSvcLinks {
1543 my $linkalias = $self->{cust_svc_linkalias};
1545 $linkalias = $self->Join(
1550 FIELD2 => 'LocalBase',
1552 $self->SUPER::Limit(
1553 LEFTJOIN => $linkalias,
1556 VALUE => 'fsck.com-rt://%/ticket/%',
1559 $self->SUPER::Limit(
1560 LEFTJOIN => $linkalias,
1563 VALUE => 'MemberOf',
1565 $self->SUPER::Limit(
1566 LEFTJOIN => $linkalias,
1568 OPERATOR => 'STARTSWITH',
1569 VALUE => 'freeside://freeside/cust_svc/',
1571 $self->{cust_svc_linkalias} = $linkalias;
1573 my $svcnum_sql = "CAST(SUBSTR($linkalias.Target,30) AS ";
1574 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
1575 $svcnum_sql .= 'SIGNED INTEGER)';
1578 $svcnum_sql .= 'INTEGER)';
1580 return ($linkalias, $svcnum_sql);
1585 my ($linkalias, $svcnum_sql) = $self->JoinToSvcLinks;
1588 EXPRESSION => $svcnum_sql,
1589 TABLE2 => 'cust_svc',
1594 # This creates an alternate left join path to cust_main via cust_svc.
1595 # _FreesideFieldLimit needs to add this as a separate, independent join
1596 # and include all tickets that have a matching cust_main record via
1598 sub JoinToCustomerViaService {
1600 my $svcalias = $self->JoinToService;
1601 my $cust_pkg = $self->Join(
1603 ALIAS1 => $svcalias,
1605 TABLE2 => 'cust_pkg',
1608 my $cust_main = $self->Join(
1610 ALIAS1 => $cust_pkg,
1611 FIELD1 => 'custnum',
1612 TABLE2 => 'cust_main',
1613 FIELD2 => 'custnum',
1618 sub _FreesideFieldLimit {
1619 my ( $self, $field, $op, $value, %rest ) = @_;
1620 my $is_negative = 0;
1621 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
1622 # if the op is negative, do the join as though
1623 # the op were positive, then accept only records
1624 # where the right-side join key is null.
1626 $op = '=' if $op eq '!=';
1630 my (@alias, $table2, $subfield, $pkey);
1631 if ( $field eq 'Customer' ) {
1632 push @alias, $self->JoinToCustomer;
1633 push @alias, $self->JoinToCustomerViaService;
1636 elsif ( $field eq 'Service' ) {
1637 push @alias, $self->JoinToService;
1641 die "malformed Freeside query: $field";
1644 $subfield = $rest{SUBKEY} || $pkey;
1645 # compound subkey: separate into table name and field in that table
1646 # (must be linked by custnum)
1647 $subfield = lc($subfield);
1648 ($table2, $subfield) = ($1, $2) if $subfield =~ /^(\w+)?\.(\w+)$/;
1649 $subfield = $pkey if $subfield eq 'number';
1651 # if it's compound, create a join from cust_main or cust_svc to that
1652 # table, using custnum or svcnum, and Limit on that table instead.
1654 foreach my $a (@alias) {
1665 # do the actual Limit
1666 $self->SUPER::Limit(
1671 ENTRYAGGREGATOR => 'AND',
1672 # no SUBCLAUSE needed, limits on different aliases across left joins
1673 # are inherently independent
1676 # then, since it's a left join, exclude tickets for which there is now
1677 # no matching record in the table we just limited on. (Or where there
1678 # is a matching record, if $is_negative.)
1679 # For a cust_main query (where there are two different aliases), this
1680 # will produce a subclause: "cust_main_1.custnum IS NOT NULL OR
1681 # cust_main_2.custnum IS NOT NULL" (or "IS NULL AND..." for a negative
1688 OPERATOR => $is_negative ? 'IS' : 'IS NOT',
1691 ENTRYAGGREGATOR => $is_negative ? 'AND' : 'OR',
1692 SUBCLAUSE => 'fs_limit',
1697 foreach my $_SQLLimit (@_SQLLimit) {
1698 $self->_SQLLimit( %$_SQLLimit);
1708 Takes a paramhash with the fields FIELD, OPERATOR, VALUE and DESCRIPTION
1709 Generally best called from LimitFoo methods
1719 DESCRIPTION => undef,
1722 $args{'DESCRIPTION'} = $self->loc(
1723 "[_1] [_2] [_3]", $args{'FIELD'},
1724 $args{'OPERATOR'}, $args{'VALUE'}
1726 if ( !defined $args{'DESCRIPTION'} );
1729 if ($self->_isLimited > 1) {
1730 RT->Deprecated( Message => "Mixing old-style LimitFoo methods with Limit is deprecated" );
1732 $self->{using_restrictions} = 1;
1734 my $index = $self->_NextIndex;
1736 # make the TicketRestrictions hash the equivalent of whatever we just passed in;
1738 %{ $self->{'TicketRestrictions'}{$index} } = %args;
1740 $self->{'RecalcTicketLimits'} = 1;
1750 LimitQueue takes a paramhash with the fields OPERATOR and VALUE.
1751 OPERATOR is one of = or !=. (It defaults to =).
1752 VALUE is a queue id or Name.
1765 #TODO VALUE should also take queue objects
1766 if ( defined $args{'VALUE'} && $args{'VALUE'} !~ /^\d+$/ ) {
1767 my $queue = RT::Queue->new( $self->CurrentUser );
1768 $queue->Load( $args{'VALUE'} );
1769 $args{'VALUE'} = $queue->Id;
1772 # What if they pass in an Id? Check for isNum() and convert to
1775 #TODO check for a valid queue here
1779 VALUE => $args{'VALUE'},
1780 OPERATOR => $args{'OPERATOR'},
1781 DESCRIPTION => join(
1782 ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{'VALUE'},
1792 Takes a paramhash with the fields OPERATOR and VALUE.
1793 OPERATOR is one of = or !=.
1796 RT adds Status != 'deleted' until object has
1797 allow_deleted_search internal property set.
1798 $tickets->{'allow_deleted_search'} = 1;
1799 $tickets->LimitStatus( VALUE => 'deleted' );
1811 VALUE => $args{'VALUE'},
1812 OPERATOR => $args{'OPERATOR'},
1813 DESCRIPTION => join( ' ',
1814 $self->loc('Status'), $args{'OPERATOR'},
1815 $self->loc( $args{'VALUE'} ) ),
1819 =head2 LimitToActiveStatus
1821 Limits the status to L<RT::Queue/ActiveStatusArray>
1823 TODO: make this respect lifecycles for the queues associated with the search
1827 sub LimitToActiveStatus {
1830 my @active = RT::Queue->ActiveStatusArray();
1831 for my $active (@active) {
1838 =head2 LimitToInactiveStatus
1840 Limits the status to L<RT::Queue/InactiveStatusArray>
1842 TODO: make this respect lifecycles for the queues associated with the search
1846 sub LimitToInactiveStatus {
1849 my @active = RT::Queue->InactiveStatusArray();
1850 for my $active (@active) {
1859 If called, this search will not automatically limit the set of results found
1860 to tickets of type "Ticket". Tickets of other types, such as "project" and
1861 "approval" will be found.
1868 # Instead of faking a Limit that later gets ignored, fake up the
1869 # fact that we're already looking at type, so that the check in
1870 # FromSQL goes down the right branch
1872 # $self->LimitType(VALUE => '__any');
1873 $self->{_sql_looking_at}{type} = 1;
1880 Takes a paramhash with the fields OPERATOR and VALUE.
1881 OPERATOR is one of = or !=, it defaults to "=".
1882 VALUE is a string to search for in the type of the ticket.
1897 VALUE => $args{'VALUE'},
1898 OPERATOR => $args{'OPERATOR'},
1899 DESCRIPTION => join( ' ',
1900 $self->loc('Type'), $args{'OPERATOR'}, $args{'VALUE'}, ),
1910 Takes a paramhash with the fields OPERATOR and VALUE.
1911 OPERATOR is one of = or !=.
1912 VALUE is a string to search for in the subject of the ticket.
1921 VALUE => $args{'VALUE'},
1922 OPERATOR => $args{'OPERATOR'},
1923 DESCRIPTION => join( ' ',
1924 $self->loc('Subject'), $args{'OPERATOR'}, $args{'VALUE'}, ),
1930 # Things that can be > < = !=
1935 Takes a paramhash with the fields OPERATOR and VALUE.
1936 OPERATOR is one of =, >, < or !=.
1937 VALUE is a ticket Id to search for
1950 VALUE => $args{'VALUE'},
1951 OPERATOR => $args{'OPERATOR'},
1953 join( ' ', $self->loc('Id'), $args{'OPERATOR'}, $args{'VALUE'}, ),
1959 =head2 LimitPriority
1961 Takes a paramhash with the fields OPERATOR and VALUE.
1962 OPERATOR is one of =, >, < or !=.
1963 VALUE is a value to match the ticket's priority against
1971 FIELD => 'Priority',
1972 VALUE => $args{'VALUE'},
1973 OPERATOR => $args{'OPERATOR'},
1974 DESCRIPTION => join( ' ',
1975 $self->loc('Priority'),
1976 $args{'OPERATOR'}, $args{'VALUE'}, ),
1982 =head2 LimitInitialPriority
1984 Takes a paramhash with the fields OPERATOR and VALUE.
1985 OPERATOR is one of =, >, < or !=.
1986 VALUE is a value to match the ticket's initial priority against
1991 sub LimitInitialPriority {
1995 FIELD => 'InitialPriority',
1996 VALUE => $args{'VALUE'},
1997 OPERATOR => $args{'OPERATOR'},
1998 DESCRIPTION => join( ' ',
1999 $self->loc('Initial Priority'), $args{'OPERATOR'},
2006 =head2 LimitFinalPriority
2008 Takes a paramhash with the fields OPERATOR and VALUE.
2009 OPERATOR is one of =, >, < or !=.
2010 VALUE is a value to match the ticket's final priority against
2014 sub LimitFinalPriority {
2018 FIELD => 'FinalPriority',
2019 VALUE => $args{'VALUE'},
2020 OPERATOR => $args{'OPERATOR'},
2021 DESCRIPTION => join( ' ',
2022 $self->loc('Final Priority'), $args{'OPERATOR'},
2029 =head2 LimitTimeWorked
2031 Takes a paramhash with the fields OPERATOR and VALUE.
2032 OPERATOR is one of =, >, < or !=.
2033 VALUE is a value to match the ticket's TimeWorked attribute
2037 sub LimitTimeWorked {
2041 FIELD => 'TimeWorked',
2042 VALUE => $args{'VALUE'},
2043 OPERATOR => $args{'OPERATOR'},
2044 DESCRIPTION => join( ' ',
2045 $self->loc('Time Worked'),
2046 $args{'OPERATOR'}, $args{'VALUE'}, ),
2052 =head2 LimitTimeLeft
2054 Takes a paramhash with the fields OPERATOR and VALUE.
2055 OPERATOR is one of =, >, < or !=.
2056 VALUE is a value to match the ticket's TimeLeft attribute
2064 FIELD => 'TimeLeft',
2065 VALUE => $args{'VALUE'},
2066 OPERATOR => $args{'OPERATOR'},
2067 DESCRIPTION => join( ' ',
2068 $self->loc('Time Left'),
2069 $args{'OPERATOR'}, $args{'VALUE'}, ),
2079 Takes a paramhash with the fields OPERATOR and VALUE.
2080 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2081 VALUE is a string to search for in the body of the ticket
2090 VALUE => $args{'VALUE'},
2091 OPERATOR => $args{'OPERATOR'},
2092 DESCRIPTION => join( ' ',
2093 $self->loc('Ticket content'), $args{'OPERATOR'},
2100 =head2 LimitFilename
2102 Takes a paramhash with the fields OPERATOR and VALUE.
2103 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2104 VALUE is a string to search for in the body of the ticket
2112 FIELD => 'Filename',
2113 VALUE => $args{'VALUE'},
2114 OPERATOR => $args{'OPERATOR'},
2115 DESCRIPTION => join( ' ',
2116 $self->loc('Attachment filename'), $args{'OPERATOR'},
2122 =head2 LimitContentType
2124 Takes a paramhash with the fields OPERATOR and VALUE.
2125 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2126 VALUE is a content type to search ticket attachments for
2130 sub LimitContentType {
2134 FIELD => 'ContentType',
2135 VALUE => $args{'VALUE'},
2136 OPERATOR => $args{'OPERATOR'},
2137 DESCRIPTION => join( ' ',
2138 $self->loc('Ticket content type'), $args{'OPERATOR'},
2149 Takes a paramhash with the fields OPERATOR and VALUE.
2150 OPERATOR is one of = or !=.
2162 my $owner = RT::User->new( $self->CurrentUser );
2163 $owner->Load( $args{'VALUE'} );
2165 # FIXME: check for a valid $owner
2168 VALUE => $args{'VALUE'},
2169 OPERATOR => $args{'OPERATOR'},
2170 DESCRIPTION => join( ' ',
2171 $self->loc('Owner'), $args{'OPERATOR'}, $owner->Name(), ),
2181 Takes a paramhash with the fields OPERATOR, TYPE and VALUE.
2182 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2183 VALUE is a value to match the ticket's watcher email addresses against
2184 TYPE is the sort of watchers you want to match against. Leave it undef if you want to search all of them
2198 #build us up a description
2199 my ( $watcher_type, $desc );
2200 if ( $args{'TYPE'} ) {
2201 $watcher_type = $args{'TYPE'};
2204 $watcher_type = "Watcher";
2208 FIELD => $watcher_type,
2209 VALUE => $args{'VALUE'},
2210 OPERATOR => $args{'OPERATOR'},
2211 TYPE => $args{'TYPE'},
2212 DESCRIPTION => join( ' ',
2213 $self->loc($watcher_type),
2214 $args{'OPERATOR'}, $args{'VALUE'}, ),
2223 =head2 LimitLinkedTo
2225 LimitLinkedTo takes a paramhash with two fields: TYPE and TARGET
2226 TYPE limits the sort of link we want to search on
2228 TYPE = { RefersTo, MemberOf, DependsOn }
2230 TARGET is the id or URI of the TARGET of the link
2244 FIELD => 'LinkedTo',
2246 TARGET => $args{'TARGET'},
2247 TYPE => $args{'TYPE'},
2248 DESCRIPTION => $self->loc(
2249 "Tickets [_1] by [_2]",
2250 $self->loc( $args{'TYPE'} ),
2253 OPERATOR => $args{'OPERATOR'},
2259 =head2 LimitLinkedFrom
2261 LimitLinkedFrom takes a paramhash with two fields: TYPE and BASE
2262 TYPE limits the sort of link we want to search on
2265 BASE is the id or URI of the BASE of the link
2269 sub LimitLinkedFrom {
2278 # translate RT2 From/To naming to RT3 TicketSQL naming
2279 my %fromToMap = qw(DependsOn DependentOn
2281 RefersTo ReferredToBy);
2283 my $type = $args{'TYPE'};
2284 $type = $fromToMap{$type} if exists( $fromToMap{$type} );
2287 FIELD => 'LinkedTo',
2289 BASE => $args{'BASE'},
2291 DESCRIPTION => $self->loc(
2292 "Tickets [_1] [_2]",
2293 $self->loc( $args{'TYPE'} ),
2296 OPERATOR => $args{'OPERATOR'},
2303 my $ticket_id = shift;
2304 return $self->LimitLinkedTo(
2306 TARGET => $ticket_id,
2312 sub LimitHasMember {
2314 my $ticket_id = shift;
2315 return $self->LimitLinkedFrom(
2317 BASE => "$ticket_id",
2318 TYPE => 'HasMember',
2325 sub LimitDependsOn {
2327 my $ticket_id = shift;
2328 return $self->LimitLinkedTo(
2330 TARGET => $ticket_id,
2331 TYPE => 'DependsOn',
2338 sub LimitDependedOnBy {
2340 my $ticket_id = shift;
2341 return $self->LimitLinkedFrom(
2344 TYPE => 'DependentOn',
2353 my $ticket_id = shift;
2354 return $self->LimitLinkedTo(
2356 TARGET => $ticket_id,
2364 sub LimitReferredToBy {
2366 my $ticket_id = shift;
2367 return $self->LimitLinkedFrom(
2370 TYPE => 'ReferredToBy',
2378 =head2 LimitDate (FIELD => 'DateField', OPERATOR => $oper, VALUE => $ISODate)
2380 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2382 OPERATOR is one of > or <
2383 VALUE is a date and time in ISO format in GMT
2384 FIELD is one of Starts, Started, Told, Created, Resolved, LastUpdated
2386 There are also helper functions of the form LimitFIELD that eliminate
2387 the need to pass in a FIELD argument.
2401 #Set the description if we didn't get handed it above
2402 unless ( $args{'DESCRIPTION'} ) {
2403 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2404 . $args{'OPERATOR'} . " "
2405 . $args{'VALUE'} . " GMT";
2408 $self->LimitField(%args);
2415 $self->LimitDate( FIELD => 'Created', @_ );
2420 $self->LimitDate( FIELD => 'Due', @_ );
2426 $self->LimitDate( FIELD => 'Starts', @_ );
2432 $self->LimitDate( FIELD => 'Started', @_ );
2437 $self->LimitDate( FIELD => 'Resolved', @_ );
2442 $self->LimitDate( FIELD => 'Told', @_ );
2445 sub LimitLastUpdated {
2447 $self->LimitDate( FIELD => 'LastUpdated', @_ );
2452 =head2 LimitTransactionDate (OPERATOR => $oper, VALUE => $ISODate)
2454 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2456 OPERATOR is one of > or <
2457 VALUE is a date and time in ISO format in GMT
2462 sub LimitTransactionDate {
2465 FIELD => 'TransactionDate',
2472 # <20021217042756.GK28744@pallas.fsck.com>
2473 # "Kill It" - Jesse.
2475 #Set the description if we didn't get handed it above
2476 unless ( $args{'DESCRIPTION'} ) {
2477 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2478 . $args{'OPERATOR'} . " "
2479 . $args{'VALUE'} . " GMT";
2482 $self->LimitField(%args);
2489 =head2 LimitCustomField
2491 Takes a paramhash of key/value pairs with the following keys:
2495 =item CUSTOMFIELD - CustomField name or id. If a name is passed, an additional parameter QUEUE may also be passed to distinguish the custom field.
2497 =item OPERATOR - The usual Limit operators
2499 =item VALUE - The value to compare against
2505 sub LimitCustomField {
2509 CUSTOMFIELD => undef,
2511 DESCRIPTION => undef,
2512 FIELD => 'CustomFieldValue',
2517 my $CF = RT::CustomField->new( $self->CurrentUser );
2518 if ( $args{CUSTOMFIELD} =~ /^\d+$/ ) {
2519 $CF->Load( $args{CUSTOMFIELD} );
2523 Name => $args{CUSTOMFIELD},
2524 LookupType => RT::Ticket->CustomFieldLookupType,
2525 ObjectId => $args{QUEUE},
2527 $args{CUSTOMFIELD} = $CF->Id;
2530 #If we are looking to compare with a null value.
2531 if ( $args{'OPERATOR'} =~ /^is$/i ) {
2532 $args{'DESCRIPTION'}
2533 ||= $self->loc( "Custom field [_1] has no value.", $CF->Name );
2535 elsif ( $args{'OPERATOR'} =~ /^is not$/i ) {
2536 $args{'DESCRIPTION'}
2537 ||= $self->loc( "Custom field [_1] has a value.", $CF->Name );
2540 # if we're not looking to compare with a null value
2542 $args{'DESCRIPTION'} ||= $self->loc( "Custom field [_1] [_2] [_3]",
2543 $CF->Name, $args{OPERATOR}, $args{VALUE} );
2546 if ( defined $args{'QUEUE'} && $args{'QUEUE'} =~ /\D/ ) {
2547 my $QueueObj = RT::Queue->new( $self->CurrentUser );
2548 $QueueObj->Load( $args{'QUEUE'} );
2549 $args{'QUEUE'} = $QueueObj->Id;
2551 delete $args{'QUEUE'} unless defined $args{'QUEUE'} && length $args{'QUEUE'};
2554 @rest = ( ENTRYAGGREGATOR => 'AND' )
2555 if ( $CF->Type eq 'SelectMultiple' );
2558 VALUE => $args{VALUE},
2560 .(defined $args{'QUEUE'}? ".$args{'QUEUE'}" : '' )
2561 .".{" . $CF->Name . "}",
2562 OPERATOR => $args{OPERATOR},
2567 $self->{'RecalcTicketLimits'} = 1;
2574 Keep track of the counter for the array of restrictions
2580 return ( $self->{'restriction_index'}++ );
2588 $self->{'table'} = "Tickets";
2589 $self->{'RecalcTicketLimits'} = 1;
2590 $self->{'restriction_index'} = 1;
2591 $self->{'primary_key'} = "id";
2592 delete $self->{'items_array'};
2593 delete $self->{'item_map'};
2594 delete $self->{'columns_to_display'};
2595 $self->SUPER::_Init(@_);
2602 # Private Member Variables (which should get cleaned)
2603 $self->{'_sql_transalias'} = undef;
2604 $self->{'_sql_trattachalias'} = undef;
2605 $self->{'_sql_cf_alias'} = undef;
2606 $self->{'_sql_object_cfv_alias'} = undef;
2607 $self->{'_sql_watcher_join_users_alias'} = undef;
2608 $self->{'_sql_query'} = '';
2609 $self->{'_sql_looking_at'} = {};
2615 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2616 return ( $self->SUPER::Count() );
2622 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2623 return ( $self->SUPER::CountAll() );
2628 =head2 ItemsArrayRef
2630 Returns a reference to the set of all items found in this search
2637 return $self->{'items_array'} if $self->{'items_array'};
2639 my $placeholder = $self->_ItemsCounter;
2640 $self->GotoFirstItem();
2641 while ( my $item = $self->Next ) {
2642 push( @{ $self->{'items_array'} }, $item );
2644 $self->GotoItem($placeholder);
2645 $self->{'items_array'}
2646 = $self->ItemsOrderBy( $self->{'items_array'} );
2648 return $self->{'items_array'};
2651 sub ItemsArrayRefWindow {
2655 my @old = ($self->_ItemsCounter, $self->RowsPerPage, $self->FirstRow+1);
2657 $self->RowsPerPage( $window );
2659 $self->GotoFirstItem;
2662 while ( my $item = $self->Next ) {
2666 $self->RowsPerPage( $old[1] );
2667 $self->FirstRow( $old[2] );
2668 $self->GotoItem( $old[0] );
2677 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2679 my $Ticket = $self->SUPER::Next;
2680 return $Ticket unless $Ticket;
2682 if ( $Ticket->__Value('Status') eq 'deleted'
2683 && !$self->{'allow_deleted_search'} )
2687 elsif ( RT->Config->Get('UseSQLForACLChecks') ) {
2688 # if we found a ticket with this option enabled then
2689 # all tickets we found are ACLed, cache this fact
2690 my $key = join ";:;", $self->CurrentUser->id, 'ShowTicket', 'RT::Ticket-'. $Ticket->id;
2691 $RT::Principal::_ACL_CACHE->{ $key } = 1;
2694 elsif ( $Ticket->CurrentUserHasRight('ShowTicket') ) {
2699 # If the user doesn't have the right to show this ticket
2706 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
2707 return $self->SUPER::_DoSearch( @_ );
2712 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
2713 return $self->SUPER::_DoCount( @_ );
2719 my $cache_key = 'RolesHasRight;:;ShowTicket';
2721 if ( my $cached = $RT::Principal::_ACL_CACHE->{ $cache_key } ) {
2725 my $ACL = RT::ACL->new( RT->SystemUser );
2726 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
2727 $ACL->Limit( FIELD => 'PrincipalType', OPERATOR => '!=', VALUE => 'Group' );
2728 my $principal_alias = $ACL->Join(
2730 FIELD1 => 'PrincipalId',
2731 TABLE2 => 'Principals',
2734 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
2737 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
2738 my $role = $ACE->__Value('PrincipalType');
2739 my $type = $ACE->__Value('ObjectType');
2740 if ( $type eq 'RT::System' ) {
2743 elsif ( $type eq 'RT::Queue' ) {
2744 next if $res{ $role } && !ref $res{ $role };
2745 push @{ $res{ $role } ||= [] }, $ACE->__Value('ObjectId');
2748 $RT::Logger->error('ShowTicket right is granted on unsupported object');
2751 $RT::Principal::_ACL_CACHE->{ $cache_key } = \%res;
2755 sub _DirectlyCanSeeIn {
2757 my $id = $self->CurrentUser->id;
2759 my $cache_key = 'User-'. $id .';:;ShowTicket;:;DirectlyCanSeeIn';
2760 if ( my $cached = $RT::Principal::_ACL_CACHE->{ $cache_key } ) {
2764 my $ACL = RT::ACL->new( RT->SystemUser );
2765 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
2766 my $principal_alias = $ACL->Join(
2768 FIELD1 => 'PrincipalId',
2769 TABLE2 => 'Principals',
2772 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
2773 my $cgm_alias = $ACL->Join(
2775 FIELD1 => 'PrincipalId',
2776 TABLE2 => 'CachedGroupMembers',
2777 FIELD2 => 'GroupId',
2779 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
2780 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
2783 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
2784 my $type = $ACE->__Value('ObjectType');
2785 if ( $type eq 'RT::System' ) {
2786 # If user is direct member of a group that has the right
2787 # on the system then he can see any ticket
2788 $RT::Principal::_ACL_CACHE->{ $cache_key } = [-1];
2791 elsif ( $type eq 'RT::Queue' ) {
2792 push @res, $ACE->__Value('ObjectId');
2795 $RT::Logger->error('ShowTicket right is granted on unsupported object');
2798 $RT::Principal::_ACL_CACHE->{ $cache_key } = \@res;
2802 sub CurrentUserCanSee {
2804 return if $self->{'_sql_current_user_can_see_applied'};
2806 return $self->{'_sql_current_user_can_see_applied'} = 1
2807 if $self->CurrentUser->UserObj->HasRight(
2808 Right => 'SuperUser', Object => $RT::System
2811 local $self->{using_restrictions};
2813 my $id = $self->CurrentUser->id;
2815 # directly can see in all queues then we have nothing to do
2816 my @direct_queues = $self->_DirectlyCanSeeIn;
2817 return $self->{'_sql_current_user_can_see_applied'} = 1
2818 if @direct_queues && $direct_queues[0] == -1;
2820 my %roles = $self->_RolesCanSee;
2822 my %skip = map { $_ => 1 } @direct_queues;
2823 foreach my $role ( keys %roles ) {
2824 next unless ref $roles{ $role };
2826 my @queues = grep !$skip{$_}, @{ $roles{ $role } };
2828 $roles{ $role } = \@queues;
2830 delete $roles{ $role };
2835 # there is no global watchers, only queues and tickes, if at
2836 # some point we will add global roles then it's gonna blow
2837 # the idea here is that if the right is set globaly for a role
2838 # and user plays this role for a queue directly not a ticket
2839 # then we have to check in advance
2840 if ( my @tmp = grep $_ ne 'Owner' && !ref $roles{ $_ }, keys %roles ) {
2842 my $groups = RT::Groups->new( RT->SystemUser );
2843 $groups->Limit( FIELD => 'Domain', VALUE => 'RT::Queue-Role', CASESENSITIVE => 0 );
2846 FUNCTION => 'LOWER(?)',
2848 VALUE => [ map {lc $_} @tmp ],
2851 my $principal_alias = $groups->Join(
2854 TABLE2 => 'Principals',
2857 $groups->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
2858 my $cgm_alias = $groups->Join(
2861 TABLE2 => 'CachedGroupMembers',
2862 FIELD2 => 'GroupId',
2864 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
2865 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
2866 while ( my $group = $groups->Next ) {
2867 push @direct_queues, $group->Instance;
2871 unless ( @direct_queues || keys %roles ) {
2877 ENTRYAGGREGATOR => 'AND',
2879 return $self->{'_sql_current_user_can_see_applied'} = 1;
2883 my $join_roles = keys %roles;
2884 $join_roles = 0 if $join_roles == 1 && $roles{'Owner'};
2885 my ($role_group_alias, $cgm_alias);
2886 if ( $join_roles ) {
2887 $role_group_alias = $self->_RoleGroupsJoin( New => 1 );
2888 $cgm_alias = $self->_GroupMembersJoin( GroupsAlias => $role_group_alias );
2890 LEFTJOIN => $cgm_alias,
2891 FIELD => 'MemberId',
2896 my $limit_queues = sub {
2900 return unless @queues;
2906 VALUE => [ @queues ],
2907 ENTRYAGGREGATOR => $ea,
2912 $self->SUPER::_OpenParen('ACL');
2914 $ea = 'OR' if $limit_queues->( $ea, @direct_queues );
2915 while ( my ($role, $queues) = each %roles ) {
2916 $self->SUPER::_OpenParen('ACL');
2917 if ( $role eq 'Owner' ) {
2922 ENTRYAGGREGATOR => $ea,
2928 ALIAS => $cgm_alias,
2929 FIELD => 'MemberId',
2930 OPERATOR => 'IS NOT',
2933 ENTRYAGGREGATOR => $ea,
2937 ALIAS => $role_group_alias,
2940 ENTRYAGGREGATOR => 'AND',
2944 $limit_queues->( 'AND', @$queues ) if ref $queues;
2945 $ea = 'OR' if $ea eq 'AND';
2946 $self->SUPER::_CloseParen('ACL');
2948 $self->SUPER::_CloseParen('ACL');
2950 return $self->{'_sql_current_user_can_see_applied'} = 1;
2955 =head2 ClearRestrictions
2957 Removes all restrictions irretrievably
2961 sub ClearRestrictions {
2963 delete $self->{'TicketRestrictions'};
2964 $self->{_sql_looking_at} = {};
2965 $self->{'RecalcTicketLimits'} = 1;
2968 # Convert a set of oldstyle SB Restrictions to Clauses for RQL
2970 sub _RestrictionsToClauses {
2974 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
2975 my $restriction = $self->{'TicketRestrictions'}{$row};
2977 # We need to reimplement the subclause aggregation that SearchBuilder does.
2978 # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main',
2979 # Then SB AND's the different Subclauses together.
2981 # So, we want to group things into Subclauses, convert them to
2982 # SQL, and then join them with the appropriate DefaultEA.
2983 # Then join each subclause group with AND.
2985 my $field = $restriction->{'FIELD'};
2986 my $realfield = $field; # CustomFields fake up a fieldname, so
2987 # we need to figure that out
2990 # Rewrite LinkedTo meta field to the real field
2991 if ( $field =~ /LinkedTo/ ) {
2992 $realfield = $field = $restriction->{'TYPE'};
2996 # Handle subkey fields with a different real field
2997 if ( $field =~ /^(\w+)\./ ) {
3001 die "I don't know about $field yet"
3002 unless ( exists $FIELD_METADATA{$realfield}
3003 or $restriction->{CUSTOMFIELD} );
3005 my $type = $FIELD_METADATA{$realfield}->[0];
3006 my $op = $restriction->{'OPERATOR'};
3010 map { $restriction->{$_} } qw(VALUE TICKET BASE TARGET)
3013 # this performs the moral equivalent of defined or/dor/C<//>,
3014 # without the short circuiting.You need to use a 'defined or'
3015 # type thing instead of just checking for truth values, because
3016 # VALUE could be 0.(i.e. "false")
3018 # You could also use this, but I find it less aesthetic:
3019 # (although it does short circuit)
3020 #( defined $restriction->{'VALUE'}? $restriction->{VALUE} :
3021 # defined $restriction->{'TICKET'} ?
3022 # $restriction->{TICKET} :
3023 # defined $restriction->{'BASE'} ?
3024 # $restriction->{BASE} :
3025 # defined $restriction->{'TARGET'} ?
3026 # $restriction->{TARGET} )
3028 my $ea = $restriction->{ENTRYAGGREGATOR}
3029 || $DefaultEA{$type}
3032 die "Invalid operator $op for $field ($type)"
3033 unless exists $ea->{$op};
3037 # Each CustomField should be put into a different Clause so they
3038 # are ANDed together.
3039 if ( $restriction->{CUSTOMFIELD} ) {
3040 $realfield = $field;
3043 exists $clause{$realfield} or $clause{$realfield} = [];
3046 $field =~ s!(['\\])!\\$1!g;
3047 $value =~ s!(['\\])!\\$1!g;
3048 my $data = [ $ea, $type, $field, $op, $value ];
3050 # here is where we store extra data, say if it's a keyword or
3051 # something. (I.e. "TYPE SPECIFIC STUFF")
3053 if (lc $ea eq 'none') {
3054 $clause{$realfield} = [ $data ];
3056 push @{ $clause{$realfield} }, $data;
3068 my $clauses = shift;
3071 for my $f (keys %{$clauses}) {
3075 # Build SQL from the data hash
3076 for my $data ( @{ $clauses->{$f} } ) {
3077 $sql .= $data->[0] unless $first; $first=0; # ENTRYAGGREGATOR
3078 $sql .= " '". $data->[2] . "' "; # FIELD
3079 $sql .= $data->[3] . " "; # OPERATOR
3080 $sql .= "'". $data->[4] . "' "; # VALUE
3083 push @sql, " ( " . $sql . " ) ";
3086 return join("AND",@sql);
3089 sub _ProcessRestrictions {
3092 delete $self->{'items_array'};
3093 delete $self->{'item_map'};
3094 delete $self->{'raw_rows'};
3095 delete $self->{'count_all'};
3097 my $sql = $self->Query;
3098 if ( !$sql || $self->{'RecalcTicketLimits'} ) {
3100 local $self->{using_restrictions};
3101 # "Restrictions to Clauses Branch\n";
3102 my $clauseRef = eval { $self->_RestrictionsToClauses; };
3104 $RT::Logger->error( "RestrictionsToClauses: " . $@ );
3108 $sql = $self->ClausesToSQL($clauseRef);
3109 $self->FromSQL($sql) if $sql;
3113 $self->{'RecalcTicketLimits'} = 0;
3117 =head2 _BuildItemMap
3119 Build up a L</ItemMap> of first/last/next/prev items, so that we can
3120 display search nav quickly.
3127 my $window = RT->Config->Get('TicketsItemMapSize');
3129 $self->{'item_map'} = {};
3131 my $items = $self->ItemsArrayRefWindow( $window );
3132 return unless $items && @$items;
3135 $self->{'item_map'}{'first'} = $items->[0]->EffectiveId;
3136 for ( my $i = 0; $i < @$items; $i++ ) {
3137 my $item = $items->[$i];
3138 my $id = $item->EffectiveId;
3139 $self->{'item_map'}{$id}{'defined'} = 1;
3140 $self->{'item_map'}{$id}{'prev'} = $prev;
3141 $self->{'item_map'}{$id}{'next'} = $items->[$i+1]->EffectiveId
3145 $self->{'item_map'}{'last'} = $prev
3146 if !$window || @$items < $window;
3151 Returns an a map of all items found by this search. The map is a hash
3155 first => <first ticket id found>,
3156 last => <last ticket id found or undef>,
3159 prev => <the ticket id found before>,
3160 next => <the ticket id found after>,
3172 $self->_BuildItemMap unless $self->{'item_map'};
3173 return $self->{'item_map'};
3179 =head2 PrepForSerialization
3181 You don't want to serialize a big tickets object, as
3182 the {items} hash will be instantly invalid _and_ eat
3187 sub PrepForSerialization {
3189 delete $self->{'items'};
3190 delete $self->{'items_array'};
3191 $self->RedoSearch();
3196 RT::Tickets supports several flags which alter search behavior:
3199 allow_deleted_search (Otherwise never show deleted tickets in search results)
3201 These flags are set by calling
3203 $tickets->{'flagname'} = 1;
3205 BUG: There should be an API for this
3213 Convert a RT-SQL string into a set of SearchBuilder restrictions.
3215 Returns (1, 'Status message') on success and (0, 'Error Message') on
3221 my ($self,$string) = @_;
3224 # Bundling of joins is implemented by dynamically tracking a parallel query
3225 # tree in %sub_tree as the TicketSQL is parsed.
3227 # Only positive, OR'd watcher conditions are bundled currently. Each key
3228 # in %sub_tree is a watcher type (Requestor, Cc, AdminCc) or the generic
3229 # "Watcher" for any watcher type. Owner is not bundled because it is
3230 # denormalized into a Tickets column and doesn't need a join. AND'd
3231 # conditions are not bundled since a record may have multiple watchers
3232 # which independently match the conditions, thus necessitating two joins.
3234 # The values of %sub_tree are arrayrefs made up of:
3236 # * Open parentheses "(" pushed on by the OpenParen callback
3237 # * Arrayrefs of bundled join aliases pushed on by the Condition callback
3238 # * Entry aggregators (AND/OR) pushed on by the EntryAggregator callback
3240 # The CloseParen callback takes care of backing off the query trees until
3241 # outside of the just-closed parenthetical, thus restoring the tree state
3242 # an equivalent of before the parenthetical was entered.
3244 # The Condition callback handles starting a new subtree or extending an
3245 # existing one, determining if bundling the current condition with any
3246 # subtree is possible, and pruning any dangling entry aggregators from
3254 $callback{'OpenParen'} = sub {
3257 push @$_, '(' foreach values %sub_tree;
3259 $callback{'CloseParen'} = sub {
3262 foreach my $list ( values %sub_tree ) {
3263 if ( $list->[-1] eq '(' ) {
3265 pop @$list if $list->[-1] =~ /^(?:AND|OR)$/i;
3268 pop @$list while $list->[-2] ne '(';
3269 $list->[-1] = pop @$list;
3273 $callback{'EntryAggregator'} = sub {
3275 push @$_, $ea foreach grep @$_ && $_->[-1] ne '(', values %sub_tree;
3277 $callback{'Condition'} = sub {
3278 my ($key, $op, $value) = @_;
3280 my $negative_op = ($op eq '!=' || $op =~ /\bNOT\b/i);
3281 my $null_op = ( 'is not' eq lc($op) || 'is' eq lc($op) );
3282 # key has dot then it's compound variant and we have subkey
3284 ($key, $subkey) = ($1, $2) if $key =~ /^([^\.]+)\.(.+)$/;
3286 # normalize key and get class (type)
3288 if (exists $LOWER_CASE_FIELDS{lc $key}) {
3289 $key = $LOWER_CASE_FIELDS{lc $key};
3290 $class = $FIELD_METADATA{$key}->[0];
3292 die "Unknown field '$key' in '$string'" unless $class;
3294 # replace __CurrentUser__ with id
3295 $value = $self->CurrentUser->id if $value eq '__CurrentUser__';
3298 unless( $dispatch{ $class } ) {
3299 die "No dispatch method for class '$class'"
3301 my $sub = $dispatch{ $class };
3303 my @res; my $bundle_with;
3304 if ( $class eq 'WATCHERFIELD' && $key ne 'Owner' && !$negative_op && (!$null_op || $subkey) ) {
3305 if ( !$sub_tree{$key} ) {
3306 $sub_tree{$key} = [ ('(')x$depth, \@res ];
3308 $bundle_with = $self->_check_bundling_possibility( $string, @{ $sub_tree{$key} } );
3309 if ( $sub_tree{$key}[-1] eq '(' ) {
3310 push @{ $sub_tree{$key} }, \@res;
3315 # Remove our aggregator from subtrees where our condition didn't get added
3316 pop @$_ foreach grep @$_ && $_->[-1] =~ /^(?:AND|OR)$/i, values %sub_tree;
3318 # A reference to @res may be pushed onto $sub_tree{$key} from
3319 # above, and we fill it here.
3320 @res = $sub->( $self, $key, $op, $value,
3321 SUBCLAUSE => '', # don't need anymore
3322 ENTRYAGGREGATOR => $ea,
3324 BUNDLE => $bundle_with,
3328 RT::SQL::Parse($string, \%callback);
3332 my ($self,$query) = @_;
3335 # preserve first_row and show_rows across the CleanSlate
3336 local ($self->{'first_row'}, $self->{'show_rows'}, $self->{_sql_looking_at});
3341 return (1, $self->loc("No Query")) unless $query;
3343 $self->{_sql_query} = $query;
3345 local $self->{parsing_ticketsql} = 1;
3346 $self->_parser( $query );
3350 $RT::Logger->error("Couldn't parse query: $error");
3354 # We only want to look at EffectiveId's (mostly) for these searches.
3355 unless ( $self->{_sql_looking_at}{effectiveid} ) {
3356 # instead of EffectiveId = id we do IsMerged IS NULL
3358 FIELD => 'IsMerged',
3361 ENTRYAGGREGATOR => 'AND',
3365 unless ( $self->{_sql_looking_at}{type} ) {
3366 $self->Limit( FIELD => 'Type', VALUE => 'ticket' );
3369 # We don't want deleted tickets unless 'allow_deleted_search' is set
3370 unless( $self->{'allow_deleted_search'} ) {
3378 # set SB's dirty flag
3379 $self->{'must_redo_search'} = 1;
3380 $self->{'RecalcTicketLimits'} = 0;
3382 return (1, $self->loc("Valid Query"));
3387 Returns the last string passed to L</FromSQL>.
3393 return $self->{_sql_query};
3396 sub _check_bundling_possibility {
3399 my @list = reverse @_;
3400 while (my $e = shift @list) {
3402 if ( lc($e) eq 'and' ) {
3405 elsif ( lc($e) eq 'or' ) {
3411 "Joins optimization failed when parsing '$string'. It's bug in RT, contact Best Practical"
3413 die "Internal error. Contact your system administrator.";
3419 RT::Base->_ImportOverlays();