1 # BEGIN BPS TAGGED BLOCK {{{
5 # This software is Copyright (c) 1996-2017 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 ) = @_;
1621 my $is_negative = 0;
1622 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
1623 # if the op is negative, do the join as though
1624 # the op were positive, then accept only records
1625 # where the right-side join key is null.
1627 $op = '=' if $op eq '!=';
1631 my (@alias, $table2, $subfield, $pkey);
1632 if ( $field eq 'Customer' ) {
1633 push @alias, $self->JoinToCustomer;
1634 push @alias, $self->JoinToCustomerViaService;
1637 elsif ( $field eq 'Service' ) {
1638 push @alias, $self->JoinToService;
1642 die "malformed Freeside query: $field";
1645 $subfield = $rest{SUBKEY} || $pkey;
1646 # compound subkey: separate into table name and field in that table
1647 # (must be linked by custnum)
1648 $subfield = lc($subfield);
1649 ($table2, $subfield) = ($1, $2) if $subfield =~ /^(\w+)?\.(\w+)$/;
1650 $subfield = $pkey if $subfield eq 'number';
1652 # if it's compound, create a join from cust_main or cust_svc to that
1653 # table, using custnum or svcnum, and Limit on that table instead.
1655 foreach my $a (@alias) {
1666 # do the actual Limit
1667 $self->SUPER::Limit(
1672 ENTRYAGGREGATOR => 'AND',
1673 # no SUBCLAUSE needed, limits on different aliases across left joins
1674 # are inherently independent
1677 # then, since it's a left join, exclude tickets for which there is now
1678 # no matching record in the table we just limited on. (Or where there
1679 # is a matching record, if $is_negative.)
1680 # For a cust_main query (where there are two different aliases), this
1681 # will produce a subclause: "cust_main_1.custnum IS NOT NULL OR
1682 # cust_main_2.custnum IS NOT NULL" (or "IS NULL AND..." for a negative
1689 OPERATOR => $is_negative ? 'IS' : 'IS NOT',
1692 ENTRYAGGREGATOR => $is_negative ? 'AND' : 'OR',
1693 SUBCLAUSE => 'fs_limit',
1698 #the clauses seem to now auto-paren themselves (correctly!), calling this
1699 # inserts "( )" which causes the query to syntax error out
1702 foreach my $Limit (@Limit) {
1703 $self->Limit( %$Limit);
1706 #$self->_CloseParen;
1714 Takes a paramhash with the fields FIELD, OPERATOR, VALUE and DESCRIPTION
1715 Generally best called from LimitFoo methods
1725 DESCRIPTION => undef,
1728 $args{'DESCRIPTION'} = $self->loc(
1729 "[_1] [_2] [_3]", $args{'FIELD'},
1730 $args{'OPERATOR'}, $args{'VALUE'}
1732 if ( !defined $args{'DESCRIPTION'} );
1735 if ($self->_isLimited > 1) {
1736 RT->Deprecated( Message => "Mixing old-style LimitFoo methods with Limit is deprecated" );
1738 $self->{using_restrictions} = 1;
1740 my $index = $self->_NextIndex;
1742 # make the TicketRestrictions hash the equivalent of whatever we just passed in;
1744 %{ $self->{'TicketRestrictions'}{$index} } = %args;
1746 $self->{'RecalcTicketLimits'} = 1;
1756 LimitQueue takes a paramhash with the fields OPERATOR and VALUE.
1757 OPERATOR is one of = or !=. (It defaults to =).
1758 VALUE is a queue id or Name.
1771 #TODO VALUE should also take queue objects
1772 if ( defined $args{'VALUE'} && $args{'VALUE'} !~ /^\d+$/ ) {
1773 my $queue = RT::Queue->new( $self->CurrentUser );
1774 $queue->Load( $args{'VALUE'} );
1775 $args{'VALUE'} = $queue->Id;
1778 # What if they pass in an Id? Check for isNum() and convert to
1781 #TODO check for a valid queue here
1785 VALUE => $args{'VALUE'},
1786 OPERATOR => $args{'OPERATOR'},
1787 DESCRIPTION => join(
1788 ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{'VALUE'},
1798 Takes a paramhash with the fields OPERATOR and VALUE.
1799 OPERATOR is one of = or !=.
1802 RT adds Status != 'deleted' until object has
1803 allow_deleted_search internal property set.
1804 $tickets->{'allow_deleted_search'} = 1;
1805 $tickets->LimitStatus( VALUE => 'deleted' );
1817 VALUE => $args{'VALUE'},
1818 OPERATOR => $args{'OPERATOR'},
1819 DESCRIPTION => join( ' ',
1820 $self->loc('Status'), $args{'OPERATOR'},
1821 $self->loc( $args{'VALUE'} ) ),
1825 =head2 LimitToActiveStatus
1827 Limits the status to L<RT::Queue/ActiveStatusArray>
1829 TODO: make this respect lifecycles for the queues associated with the search
1833 sub LimitToActiveStatus {
1836 my @active = RT::Queue->ActiveStatusArray();
1837 for my $active (@active) {
1844 =head2 LimitToInactiveStatus
1846 Limits the status to L<RT::Queue/InactiveStatusArray>
1848 TODO: make this respect lifecycles for the queues associated with the search
1852 sub LimitToInactiveStatus {
1855 my @active = RT::Queue->InactiveStatusArray();
1856 for my $active (@active) {
1865 If called, this search will not automatically limit the set of results found
1866 to tickets of type "Ticket". Tickets of other types, such as "project" and
1867 "approval" will be found.
1874 # Instead of faking a Limit that later gets ignored, fake up the
1875 # fact that we're already looking at type, so that the check in
1876 # FromSQL goes down the right branch
1878 # $self->LimitType(VALUE => '__any');
1879 $self->{_sql_looking_at}{type} = 1;
1886 Takes a paramhash with the fields OPERATOR and VALUE.
1887 OPERATOR is one of = or !=, it defaults to "=".
1888 VALUE is a string to search for in the type of the ticket.
1903 VALUE => $args{'VALUE'},
1904 OPERATOR => $args{'OPERATOR'},
1905 DESCRIPTION => join( ' ',
1906 $self->loc('Type'), $args{'OPERATOR'}, $args{'VALUE'}, ),
1916 Takes a paramhash with the fields OPERATOR and VALUE.
1917 OPERATOR is one of = or !=.
1918 VALUE is a string to search for in the subject of the ticket.
1927 VALUE => $args{'VALUE'},
1928 OPERATOR => $args{'OPERATOR'},
1929 DESCRIPTION => join( ' ',
1930 $self->loc('Subject'), $args{'OPERATOR'}, $args{'VALUE'}, ),
1936 # Things that can be > < = !=
1941 Takes a paramhash with the fields OPERATOR and VALUE.
1942 OPERATOR is one of =, >, < or !=.
1943 VALUE is a ticket Id to search for
1956 VALUE => $args{'VALUE'},
1957 OPERATOR => $args{'OPERATOR'},
1959 join( ' ', $self->loc('Id'), $args{'OPERATOR'}, $args{'VALUE'}, ),
1965 =head2 LimitPriority
1967 Takes a paramhash with the fields OPERATOR and VALUE.
1968 OPERATOR is one of =, >, < or !=.
1969 VALUE is a value to match the ticket's priority against
1977 FIELD => 'Priority',
1978 VALUE => $args{'VALUE'},
1979 OPERATOR => $args{'OPERATOR'},
1980 DESCRIPTION => join( ' ',
1981 $self->loc('Priority'),
1982 $args{'OPERATOR'}, $args{'VALUE'}, ),
1988 =head2 LimitInitialPriority
1990 Takes a paramhash with the fields OPERATOR and VALUE.
1991 OPERATOR is one of =, >, < or !=.
1992 VALUE is a value to match the ticket's initial priority against
1997 sub LimitInitialPriority {
2001 FIELD => 'InitialPriority',
2002 VALUE => $args{'VALUE'},
2003 OPERATOR => $args{'OPERATOR'},
2004 DESCRIPTION => join( ' ',
2005 $self->loc('Initial Priority'), $args{'OPERATOR'},
2012 =head2 LimitFinalPriority
2014 Takes a paramhash with the fields OPERATOR and VALUE.
2015 OPERATOR is one of =, >, < or !=.
2016 VALUE is a value to match the ticket's final priority against
2020 sub LimitFinalPriority {
2024 FIELD => 'FinalPriority',
2025 VALUE => $args{'VALUE'},
2026 OPERATOR => $args{'OPERATOR'},
2027 DESCRIPTION => join( ' ',
2028 $self->loc('Final Priority'), $args{'OPERATOR'},
2035 =head2 LimitTimeWorked
2037 Takes a paramhash with the fields OPERATOR and VALUE.
2038 OPERATOR is one of =, >, < or !=.
2039 VALUE is a value to match the ticket's TimeWorked attribute
2043 sub LimitTimeWorked {
2047 FIELD => 'TimeWorked',
2048 VALUE => $args{'VALUE'},
2049 OPERATOR => $args{'OPERATOR'},
2050 DESCRIPTION => join( ' ',
2051 $self->loc('Time Worked'),
2052 $args{'OPERATOR'}, $args{'VALUE'}, ),
2058 =head2 LimitTimeLeft
2060 Takes a paramhash with the fields OPERATOR and VALUE.
2061 OPERATOR is one of =, >, < or !=.
2062 VALUE is a value to match the ticket's TimeLeft attribute
2070 FIELD => 'TimeLeft',
2071 VALUE => $args{'VALUE'},
2072 OPERATOR => $args{'OPERATOR'},
2073 DESCRIPTION => join( ' ',
2074 $self->loc('Time Left'),
2075 $args{'OPERATOR'}, $args{'VALUE'}, ),
2085 Takes a paramhash with the fields OPERATOR and VALUE.
2086 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2087 VALUE is a string to search for in the body of the ticket
2096 VALUE => $args{'VALUE'},
2097 OPERATOR => $args{'OPERATOR'},
2098 DESCRIPTION => join( ' ',
2099 $self->loc('Ticket content'), $args{'OPERATOR'},
2106 =head2 LimitFilename
2108 Takes a paramhash with the fields OPERATOR and VALUE.
2109 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2110 VALUE is a string to search for in the body of the ticket
2118 FIELD => 'Filename',
2119 VALUE => $args{'VALUE'},
2120 OPERATOR => $args{'OPERATOR'},
2121 DESCRIPTION => join( ' ',
2122 $self->loc('Attachment filename'), $args{'OPERATOR'},
2128 =head2 LimitContentType
2130 Takes a paramhash with the fields OPERATOR and VALUE.
2131 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2132 VALUE is a content type to search ticket attachments for
2136 sub LimitContentType {
2140 FIELD => 'ContentType',
2141 VALUE => $args{'VALUE'},
2142 OPERATOR => $args{'OPERATOR'},
2143 DESCRIPTION => join( ' ',
2144 $self->loc('Ticket content type'), $args{'OPERATOR'},
2155 Takes a paramhash with the fields OPERATOR and VALUE.
2156 OPERATOR is one of = or !=.
2168 my $owner = RT::User->new( $self->CurrentUser );
2169 $owner->Load( $args{'VALUE'} );
2171 # FIXME: check for a valid $owner
2174 VALUE => $args{'VALUE'},
2175 OPERATOR => $args{'OPERATOR'},
2176 DESCRIPTION => join( ' ',
2177 $self->loc('Owner'), $args{'OPERATOR'}, $owner->Name(), ),
2187 Takes a paramhash with the fields OPERATOR, TYPE and VALUE.
2188 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2189 VALUE is a value to match the ticket's watcher email addresses against
2190 TYPE is the sort of watchers you want to match against. Leave it undef if you want to search all of them
2204 #build us up a description
2205 my ( $watcher_type, $desc );
2206 if ( $args{'TYPE'} ) {
2207 $watcher_type = $args{'TYPE'};
2210 $watcher_type = "Watcher";
2214 FIELD => $watcher_type,
2215 VALUE => $args{'VALUE'},
2216 OPERATOR => $args{'OPERATOR'},
2217 TYPE => $args{'TYPE'},
2218 DESCRIPTION => join( ' ',
2219 $self->loc($watcher_type),
2220 $args{'OPERATOR'}, $args{'VALUE'}, ),
2229 =head2 LimitLinkedTo
2231 LimitLinkedTo takes a paramhash with two fields: TYPE and TARGET
2232 TYPE limits the sort of link we want to search on
2234 TYPE = { RefersTo, MemberOf, DependsOn }
2236 TARGET is the id or URI of the TARGET of the link
2250 FIELD => 'LinkedTo',
2252 TARGET => $args{'TARGET'},
2253 TYPE => $args{'TYPE'},
2254 DESCRIPTION => $self->loc(
2255 "Tickets [_1] by [_2]",
2256 $self->loc( $args{'TYPE'} ),
2259 OPERATOR => $args{'OPERATOR'},
2265 =head2 LimitLinkedFrom
2267 LimitLinkedFrom takes a paramhash with two fields: TYPE and BASE
2268 TYPE limits the sort of link we want to search on
2271 BASE is the id or URI of the BASE of the link
2275 sub LimitLinkedFrom {
2284 # translate RT2 From/To naming to RT3 TicketSQL naming
2285 my %fromToMap = qw(DependsOn DependentOn
2287 RefersTo ReferredToBy);
2289 my $type = $args{'TYPE'};
2290 $type = $fromToMap{$type} if exists( $fromToMap{$type} );
2293 FIELD => 'LinkedTo',
2295 BASE => $args{'BASE'},
2297 DESCRIPTION => $self->loc(
2298 "Tickets [_1] [_2]",
2299 $self->loc( $args{'TYPE'} ),
2302 OPERATOR => $args{'OPERATOR'},
2309 my $ticket_id = shift;
2310 return $self->LimitLinkedTo(
2312 TARGET => $ticket_id,
2318 sub LimitHasMember {
2320 my $ticket_id = shift;
2321 return $self->LimitLinkedFrom(
2323 BASE => "$ticket_id",
2324 TYPE => 'HasMember',
2331 sub LimitDependsOn {
2333 my $ticket_id = shift;
2334 return $self->LimitLinkedTo(
2336 TARGET => $ticket_id,
2337 TYPE => 'DependsOn',
2344 sub LimitDependedOnBy {
2346 my $ticket_id = shift;
2347 return $self->LimitLinkedFrom(
2350 TYPE => 'DependentOn',
2359 my $ticket_id = shift;
2360 return $self->LimitLinkedTo(
2362 TARGET => $ticket_id,
2370 sub LimitReferredToBy {
2372 my $ticket_id = shift;
2373 return $self->LimitLinkedFrom(
2376 TYPE => 'ReferredToBy',
2384 =head2 LimitDate (FIELD => 'DateField', OPERATOR => $oper, VALUE => $ISODate)
2386 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2388 OPERATOR is one of > or <
2389 VALUE is a date and time in ISO format in GMT
2390 FIELD is one of Starts, Started, Told, Created, Resolved, LastUpdated
2392 There are also helper functions of the form LimitFIELD that eliminate
2393 the need to pass in a FIELD argument.
2407 #Set the description if we didn't get handed it above
2408 unless ( $args{'DESCRIPTION'} ) {
2409 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2410 . $args{'OPERATOR'} . " "
2411 . $args{'VALUE'} . " GMT";
2414 $self->LimitField(%args);
2421 $self->LimitDate( FIELD => 'Created', @_ );
2426 $self->LimitDate( FIELD => 'Due', @_ );
2432 $self->LimitDate( FIELD => 'Starts', @_ );
2438 $self->LimitDate( FIELD => 'Started', @_ );
2443 $self->LimitDate( FIELD => 'Resolved', @_ );
2448 $self->LimitDate( FIELD => 'Told', @_ );
2451 sub LimitLastUpdated {
2453 $self->LimitDate( FIELD => 'LastUpdated', @_ );
2458 =head2 LimitTransactionDate (OPERATOR => $oper, VALUE => $ISODate)
2460 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2462 OPERATOR is one of > or <
2463 VALUE is a date and time in ISO format in GMT
2468 sub LimitTransactionDate {
2471 FIELD => 'TransactionDate',
2478 # <20021217042756.GK28744@pallas.fsck.com>
2479 # "Kill It" - Jesse.
2481 #Set the description if we didn't get handed it above
2482 unless ( $args{'DESCRIPTION'} ) {
2483 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2484 . $args{'OPERATOR'} . " "
2485 . $args{'VALUE'} . " GMT";
2488 $self->LimitField(%args);
2495 =head2 LimitCustomField
2497 Takes a paramhash of key/value pairs with the following keys:
2501 =item CUSTOMFIELD - CustomField name or id. If a name is passed, an additional parameter QUEUE may also be passed to distinguish the custom field.
2503 =item OPERATOR - The usual Limit operators
2505 =item VALUE - The value to compare against
2511 sub LimitCustomField {
2515 CUSTOMFIELD => undef,
2517 DESCRIPTION => undef,
2518 FIELD => 'CustomFieldValue',
2523 my $CF = RT::CustomField->new( $self->CurrentUser );
2524 if ( $args{CUSTOMFIELD} =~ /^\d+$/ ) {
2525 $CF->Load( $args{CUSTOMFIELD} );
2529 Name => $args{CUSTOMFIELD},
2530 LookupType => RT::Ticket->CustomFieldLookupType,
2531 ObjectId => $args{QUEUE},
2533 $args{CUSTOMFIELD} = $CF->Id;
2536 #If we are looking to compare with a null value.
2537 if ( $args{'OPERATOR'} =~ /^is$/i ) {
2538 $args{'DESCRIPTION'}
2539 ||= $self->loc( "Custom field [_1] has no value.", $CF->Name );
2541 elsif ( $args{'OPERATOR'} =~ /^is not$/i ) {
2542 $args{'DESCRIPTION'}
2543 ||= $self->loc( "Custom field [_1] has a value.", $CF->Name );
2546 # if we're not looking to compare with a null value
2548 $args{'DESCRIPTION'} ||= $self->loc( "Custom field [_1] [_2] [_3]",
2549 $CF->Name, $args{OPERATOR}, $args{VALUE} );
2552 if ( defined $args{'QUEUE'} && $args{'QUEUE'} =~ /\D/ ) {
2553 my $QueueObj = RT::Queue->new( $self->CurrentUser );
2554 $QueueObj->Load( $args{'QUEUE'} );
2555 $args{'QUEUE'} = $QueueObj->Id;
2557 delete $args{'QUEUE'} unless defined $args{'QUEUE'} && length $args{'QUEUE'};
2560 @rest = ( ENTRYAGGREGATOR => 'AND' )
2561 if ( $CF->Type eq 'SelectMultiple' );
2564 VALUE => $args{VALUE},
2566 .(defined $args{'QUEUE'}? ".$args{'QUEUE'}" : '' )
2567 .".{" . $CF->Name . "}",
2568 OPERATOR => $args{OPERATOR},
2573 $self->{'RecalcTicketLimits'} = 1;
2580 Keep track of the counter for the array of restrictions
2586 return ( $self->{'restriction_index'}++ );
2594 $self->{'table'} = "Tickets";
2595 $self->{'RecalcTicketLimits'} = 1;
2596 $self->{'restriction_index'} = 1;
2597 $self->{'primary_key'} = "id";
2598 delete $self->{'items_array'};
2599 delete $self->{'item_map'};
2600 delete $self->{'columns_to_display'};
2601 $self->SUPER::_Init(@_);
2608 # Private Member Variables (which should get cleaned)
2609 $self->{'_sql_transalias'} = undef;
2610 $self->{'_sql_trattachalias'} = undef;
2611 $self->{'_sql_cf_alias'} = undef;
2612 $self->{'_sql_object_cfv_alias'} = undef;
2613 $self->{'_sql_watcher_join_users_alias'} = undef;
2614 $self->{'_sql_query'} = '';
2615 $self->{'_sql_looking_at'} = {};
2621 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2622 return ( $self->SUPER::Count() );
2628 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2629 return ( $self->SUPER::CountAll() );
2634 =head2 ItemsArrayRef
2636 Returns a reference to the set of all items found in this search
2643 return $self->{'items_array'} if $self->{'items_array'};
2645 my $placeholder = $self->_ItemsCounter;
2646 $self->GotoFirstItem();
2647 while ( my $item = $self->Next ) {
2648 push( @{ $self->{'items_array'} }, $item );
2650 $self->GotoItem($placeholder);
2651 $self->{'items_array'}
2652 = $self->ItemsOrderBy( $self->{'items_array'} );
2654 return $self->{'items_array'};
2657 sub ItemsArrayRefWindow {
2661 my @old = ($self->_ItemsCounter, $self->RowsPerPage, $self->FirstRow+1);
2663 $self->RowsPerPage( $window );
2665 $self->GotoFirstItem;
2668 while ( my $item = $self->Next ) {
2672 $self->RowsPerPage( $old[1] );
2673 $self->FirstRow( $old[2] );
2674 $self->GotoItem( $old[0] );
2683 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2685 my $Ticket = $self->SUPER::Next;
2686 return $Ticket unless $Ticket;
2688 if ( $Ticket->__Value('Status') eq 'deleted'
2689 && !$self->{'allow_deleted_search'} )
2693 elsif ( RT->Config->Get('UseSQLForACLChecks') ) {
2694 # if we found a ticket with this option enabled then
2695 # all tickets we found are ACLed, cache this fact
2696 my $key = join ";:;", $self->CurrentUser->id, 'ShowTicket', 'RT::Ticket-'. $Ticket->id;
2697 $RT::Principal::_ACL_CACHE->{ $key } = 1;
2700 elsif ( $Ticket->CurrentUserHasRight('ShowTicket') ) {
2705 # If the user doesn't have the right to show this ticket
2712 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
2713 return $self->SUPER::_DoSearch( @_ );
2718 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
2719 return $self->SUPER::_DoCount( @_ );
2725 my $cache_key = 'RolesHasRight;:;ShowTicket';
2727 if ( my $cached = $RT::Principal::_ACL_CACHE->{ $cache_key } ) {
2731 my $ACL = RT::ACL->new( RT->SystemUser );
2732 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
2733 $ACL->Limit( FIELD => 'PrincipalType', OPERATOR => '!=', VALUE => 'Group' );
2734 my $principal_alias = $ACL->Join(
2736 FIELD1 => 'PrincipalId',
2737 TABLE2 => 'Principals',
2740 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
2743 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
2744 my $role = $ACE->__Value('PrincipalType');
2745 my $type = $ACE->__Value('ObjectType');
2746 if ( $type eq 'RT::System' ) {
2749 elsif ( $type eq 'RT::Queue' ) {
2750 next if $res{ $role } && !ref $res{ $role };
2751 push @{ $res{ $role } ||= [] }, $ACE->__Value('ObjectId');
2754 $RT::Logger->error('ShowTicket right is granted on unsupported object');
2757 $RT::Principal::_ACL_CACHE->{ $cache_key } = \%res;
2761 sub _DirectlyCanSeeIn {
2763 my $id = $self->CurrentUser->id;
2765 my $cache_key = 'User-'. $id .';:;ShowTicket;:;DirectlyCanSeeIn';
2766 if ( my $cached = $RT::Principal::_ACL_CACHE->{ $cache_key } ) {
2770 my $ACL = RT::ACL->new( RT->SystemUser );
2771 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
2772 my $principal_alias = $ACL->Join(
2774 FIELD1 => 'PrincipalId',
2775 TABLE2 => 'Principals',
2778 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
2779 my $cgm_alias = $ACL->Join(
2781 FIELD1 => 'PrincipalId',
2782 TABLE2 => 'CachedGroupMembers',
2783 FIELD2 => 'GroupId',
2785 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
2786 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
2789 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
2790 my $type = $ACE->__Value('ObjectType');
2791 if ( $type eq 'RT::System' ) {
2792 # If user is direct member of a group that has the right
2793 # on the system then he can see any ticket
2794 $RT::Principal::_ACL_CACHE->{ $cache_key } = [-1];
2797 elsif ( $type eq 'RT::Queue' ) {
2798 push @res, $ACE->__Value('ObjectId');
2801 $RT::Logger->error('ShowTicket right is granted on unsupported object');
2804 $RT::Principal::_ACL_CACHE->{ $cache_key } = \@res;
2808 sub CurrentUserCanSee {
2810 return if $self->{'_sql_current_user_can_see_applied'};
2812 return $self->{'_sql_current_user_can_see_applied'} = 1
2813 if $self->CurrentUser->UserObj->HasRight(
2814 Right => 'SuperUser', Object => $RT::System
2817 local $self->{using_restrictions};
2819 my $id = $self->CurrentUser->id;
2821 # directly can see in all queues then we have nothing to do
2822 my @direct_queues = $self->_DirectlyCanSeeIn;
2823 return $self->{'_sql_current_user_can_see_applied'} = 1
2824 if @direct_queues && $direct_queues[0] == -1;
2826 my %roles = $self->_RolesCanSee;
2828 my %skip = map { $_ => 1 } @direct_queues;
2829 foreach my $role ( keys %roles ) {
2830 next unless ref $roles{ $role };
2832 my @queues = grep !$skip{$_}, @{ $roles{ $role } };
2834 $roles{ $role } = \@queues;
2836 delete $roles{ $role };
2841 # there is no global watchers, only queues and tickes, if at
2842 # some point we will add global roles then it's gonna blow
2843 # the idea here is that if the right is set globaly for a role
2844 # and user plays this role for a queue directly not a ticket
2845 # then we have to check in advance
2846 if ( my @tmp = grep $_ ne 'Owner' && !ref $roles{ $_ }, keys %roles ) {
2848 my $groups = RT::Groups->new( RT->SystemUser );
2849 $groups->Limit( FIELD => 'Domain', VALUE => 'RT::Queue-Role', CASESENSITIVE => 0 );
2852 FUNCTION => 'LOWER(?)',
2854 VALUE => [ map {lc $_} @tmp ],
2857 my $principal_alias = $groups->Join(
2860 TABLE2 => 'Principals',
2863 $groups->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
2864 my $cgm_alias = $groups->Join(
2867 TABLE2 => 'CachedGroupMembers',
2868 FIELD2 => 'GroupId',
2870 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
2871 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
2872 while ( my $group = $groups->Next ) {
2873 push @direct_queues, $group->Instance;
2877 unless ( @direct_queues || keys %roles ) {
2883 ENTRYAGGREGATOR => 'AND',
2885 return $self->{'_sql_current_user_can_see_applied'} = 1;
2889 my $join_roles = keys %roles;
2890 $join_roles = 0 if $join_roles == 1 && $roles{'Owner'};
2891 my ($role_group_alias, $cgm_alias);
2892 if ( $join_roles ) {
2893 $role_group_alias = $self->_RoleGroupsJoin( New => 1 );
2894 $cgm_alias = $self->_GroupMembersJoin( GroupsAlias => $role_group_alias );
2896 LEFTJOIN => $cgm_alias,
2897 FIELD => 'MemberId',
2902 my $limit_queues = sub {
2906 return unless @queues;
2912 VALUE => [ @queues ],
2913 ENTRYAGGREGATOR => $ea,
2918 $self->SUPER::_OpenParen('ACL');
2920 $ea = 'OR' if $limit_queues->( $ea, @direct_queues );
2921 while ( my ($role, $queues) = each %roles ) {
2922 $self->SUPER::_OpenParen('ACL');
2923 if ( $role eq 'Owner' ) {
2928 ENTRYAGGREGATOR => $ea,
2934 ALIAS => $cgm_alias,
2935 FIELD => 'MemberId',
2936 OPERATOR => 'IS NOT',
2939 ENTRYAGGREGATOR => $ea,
2943 ALIAS => $role_group_alias,
2946 ENTRYAGGREGATOR => 'AND',
2950 $limit_queues->( 'AND', @$queues ) if ref $queues;
2951 $ea = 'OR' if $ea eq 'AND';
2952 $self->SUPER::_CloseParen('ACL');
2954 $self->SUPER::_CloseParen('ACL');
2956 return $self->{'_sql_current_user_can_see_applied'} = 1;
2961 =head2 ClearRestrictions
2963 Removes all restrictions irretrievably
2967 sub ClearRestrictions {
2969 delete $self->{'TicketRestrictions'};
2970 $self->{_sql_looking_at} = {};
2971 $self->{'RecalcTicketLimits'} = 1;
2974 # Convert a set of oldstyle SB Restrictions to Clauses for RQL
2976 sub _RestrictionsToClauses {
2980 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
2981 my $restriction = $self->{'TicketRestrictions'}{$row};
2983 # We need to reimplement the subclause aggregation that SearchBuilder does.
2984 # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main',
2985 # Then SB AND's the different Subclauses together.
2987 # So, we want to group things into Subclauses, convert them to
2988 # SQL, and then join them with the appropriate DefaultEA.
2989 # Then join each subclause group with AND.
2991 my $field = $restriction->{'FIELD'};
2992 my $realfield = $field; # CustomFields fake up a fieldname, so
2993 # we need to figure that out
2996 # Rewrite LinkedTo meta field to the real field
2997 if ( $field =~ /LinkedTo/ ) {
2998 $realfield = $field = $restriction->{'TYPE'};
3002 # Handle subkey fields with a different real field
3003 if ( $field =~ /^(\w+)\./ ) {
3007 die "I don't know about $field yet"
3008 unless ( exists $FIELD_METADATA{$realfield}
3009 or $restriction->{CUSTOMFIELD} );
3011 my $type = $FIELD_METADATA{$realfield}->[0];
3012 my $op = $restriction->{'OPERATOR'};
3016 map { $restriction->{$_} } qw(VALUE TICKET BASE TARGET)
3019 # this performs the moral equivalent of defined or/dor/C<//>,
3020 # without the short circuiting.You need to use a 'defined or'
3021 # type thing instead of just checking for truth values, because
3022 # VALUE could be 0.(i.e. "false")
3024 # You could also use this, but I find it less aesthetic:
3025 # (although it does short circuit)
3026 #( defined $restriction->{'VALUE'}? $restriction->{VALUE} :
3027 # defined $restriction->{'TICKET'} ?
3028 # $restriction->{TICKET} :
3029 # defined $restriction->{'BASE'} ?
3030 # $restriction->{BASE} :
3031 # defined $restriction->{'TARGET'} ?
3032 # $restriction->{TARGET} )
3034 my $ea = $restriction->{ENTRYAGGREGATOR}
3035 || $DefaultEA{$type}
3038 die "Invalid operator $op for $field ($type)"
3039 unless exists $ea->{$op};
3043 # Each CustomField should be put into a different Clause so they
3044 # are ANDed together.
3045 if ( $restriction->{CUSTOMFIELD} ) {
3046 $realfield = $field;
3049 exists $clause{$realfield} or $clause{$realfield} = [];
3052 $field =~ s!(['\\])!\\$1!g;
3053 $value =~ s!(['\\])!\\$1!g;
3054 my $data = [ $ea, $type, $field, $op, $value ];
3056 # here is where we store extra data, say if it's a keyword or
3057 # something. (I.e. "TYPE SPECIFIC STUFF")
3059 if (lc $ea eq 'none') {
3060 $clause{$realfield} = [ $data ];
3062 push @{ $clause{$realfield} }, $data;
3074 my $clauses = shift;
3077 for my $f (keys %{$clauses}) {
3081 # Build SQL from the data hash
3082 for my $data ( @{ $clauses->{$f} } ) {
3083 $sql .= $data->[0] unless $first; $first=0; # ENTRYAGGREGATOR
3084 $sql .= " '". $data->[2] . "' "; # FIELD
3085 $sql .= $data->[3] . " "; # OPERATOR
3086 $sql .= "'". $data->[4] . "' "; # VALUE
3089 push @sql, " ( " . $sql . " ) ";
3092 return join("AND",@sql);
3095 sub _ProcessRestrictions {
3098 delete $self->{'items_array'};
3099 delete $self->{'item_map'};
3100 delete $self->{'raw_rows'};
3101 delete $self->{'count_all'};
3103 my $sql = $self->Query;
3104 if ( !$sql || $self->{'RecalcTicketLimits'} ) {
3106 local $self->{using_restrictions};
3107 # "Restrictions to Clauses Branch\n";
3108 my $clauseRef = eval { $self->_RestrictionsToClauses; };
3110 $RT::Logger->error( "RestrictionsToClauses: " . $@ );
3114 $sql = $self->ClausesToSQL($clauseRef);
3115 $self->FromSQL($sql) if $sql;
3119 $self->{'RecalcTicketLimits'} = 0;
3123 =head2 _BuildItemMap
3125 Build up a L</ItemMap> of first/last/next/prev items, so that we can
3126 display search nav quickly.
3133 my $window = RT->Config->Get('TicketsItemMapSize');
3135 $self->{'item_map'} = {};
3137 my $items = $self->ItemsArrayRefWindow( $window );
3138 return unless $items && @$items;
3141 $self->{'item_map'}{'first'} = $items->[0]->EffectiveId;
3142 for ( my $i = 0; $i < @$items; $i++ ) {
3143 my $item = $items->[$i];
3144 my $id = $item->EffectiveId;
3145 $self->{'item_map'}{$id}{'defined'} = 1;
3146 $self->{'item_map'}{$id}{'prev'} = $prev;
3147 $self->{'item_map'}{$id}{'next'} = $items->[$i+1]->EffectiveId
3151 $self->{'item_map'}{'last'} = $prev
3152 if !$window || @$items < $window;
3157 Returns an a map of all items found by this search. The map is a hash
3161 first => <first ticket id found>,
3162 last => <last ticket id found or undef>,
3165 prev => <the ticket id found before>,
3166 next => <the ticket id found after>,
3178 $self->_BuildItemMap unless $self->{'item_map'};
3179 return $self->{'item_map'};
3185 =head2 PrepForSerialization
3187 You don't want to serialize a big tickets object, as
3188 the {items} hash will be instantly invalid _and_ eat
3193 sub PrepForSerialization {
3195 delete $self->{'items'};
3196 delete $self->{'items_array'};
3197 $self->RedoSearch();
3202 RT::Tickets supports several flags which alter search behavior:
3205 allow_deleted_search (Otherwise never show deleted tickets in search results)
3207 These flags are set by calling
3209 $tickets->{'flagname'} = 1;
3211 BUG: There should be an API for this
3219 Convert a RT-SQL string into a set of SearchBuilder restrictions.
3221 Returns (1, 'Status message') on success and (0, 'Error Message') on
3227 my ($self,$string) = @_;
3230 # Bundling of joins is implemented by dynamically tracking a parallel query
3231 # tree in %sub_tree as the TicketSQL is parsed.
3233 # Only positive, OR'd watcher conditions are bundled currently. Each key
3234 # in %sub_tree is a watcher type (Requestor, Cc, AdminCc) or the generic
3235 # "Watcher" for any watcher type. Owner is not bundled because it is
3236 # denormalized into a Tickets column and doesn't need a join. AND'd
3237 # conditions are not bundled since a record may have multiple watchers
3238 # which independently match the conditions, thus necessitating two joins.
3240 # The values of %sub_tree are arrayrefs made up of:
3242 # * Open parentheses "(" pushed on by the OpenParen callback
3243 # * Arrayrefs of bundled join aliases pushed on by the Condition callback
3244 # * Entry aggregators (AND/OR) pushed on by the EntryAggregator callback
3246 # The CloseParen callback takes care of backing off the query trees until
3247 # outside of the just-closed parenthetical, thus restoring the tree state
3248 # an equivalent of before the parenthetical was entered.
3250 # The Condition callback handles starting a new subtree or extending an
3251 # existing one, determining if bundling the current condition with any
3252 # subtree is possible, and pruning any dangling entry aggregators from
3260 $callback{'OpenParen'} = sub {
3263 push @$_, '(' foreach values %sub_tree;
3265 $callback{'CloseParen'} = sub {
3268 foreach my $list ( values %sub_tree ) {
3269 if ( $list->[-1] eq '(' ) {
3271 pop @$list if $list->[-1] =~ /^(?:AND|OR)$/i;
3274 pop @$list while $list->[-2] ne '(';
3275 $list->[-1] = pop @$list;
3279 $callback{'EntryAggregator'} = sub {
3281 push @$_, $ea foreach grep @$_ && $_->[-1] ne '(', values %sub_tree;
3283 $callback{'Condition'} = sub {
3284 my ($key, $op, $value) = @_;
3286 my $negative_op = ($op eq '!=' || $op =~ /\bNOT\b/i);
3287 my $null_op = ( 'is not' eq lc($op) || 'is' eq lc($op) );
3288 # key has dot then it's compound variant and we have subkey
3290 ($key, $subkey) = ($1, $2) if $key =~ /^([^\.]+)\.(.+)$/;
3292 # normalize key and get class (type)
3294 if (exists $LOWER_CASE_FIELDS{lc $key}) {
3295 $key = $LOWER_CASE_FIELDS{lc $key};
3296 $class = $FIELD_METADATA{$key}->[0];
3298 die "Unknown field '$key' in '$string'" unless $class;
3300 # replace __CurrentUser__ with id
3301 $value = $self->CurrentUser->id if $value eq '__CurrentUser__';
3304 unless( $dispatch{ $class } ) {
3305 die "No dispatch method for class '$class'"
3307 my $sub = $dispatch{ $class };
3309 my @res; my $bundle_with;
3310 if ( $class eq 'WATCHERFIELD' && $key ne 'Owner' && !$negative_op && (!$null_op || $subkey) ) {
3311 if ( !$sub_tree{$key} ) {
3312 $sub_tree{$key} = [ ('(')x$depth, \@res ];
3314 $bundle_with = $self->_check_bundling_possibility( $string, @{ $sub_tree{$key} } );
3315 if ( $sub_tree{$key}[-1] eq '(' ) {
3316 push @{ $sub_tree{$key} }, \@res;
3321 # Remove our aggregator from subtrees where our condition didn't get added
3322 pop @$_ foreach grep @$_ && $_->[-1] =~ /^(?:AND|OR)$/i, values %sub_tree;
3324 # A reference to @res may be pushed onto $sub_tree{$key} from
3325 # above, and we fill it here.
3326 @res = $sub->( $self, $key, $op, $value,
3327 SUBCLAUSE => '', # don't need anymore
3328 ENTRYAGGREGATOR => $ea,
3330 BUNDLE => $bundle_with,
3334 RT::SQL::Parse($string, \%callback);
3338 my ($self,$query) = @_;
3341 # preserve first_row and show_rows across the CleanSlate
3342 local ($self->{'first_row'}, $self->{'show_rows'}, $self->{_sql_looking_at});
3347 return (1, $self->loc("No Query")) unless $query;
3349 $self->{_sql_query} = $query;
3351 local $self->{parsing_ticketsql} = 1;
3352 $self->_parser( $query );
3356 $RT::Logger->error("Couldn't parse query: $error");
3360 # We only want to look at EffectiveId's (mostly) for these searches.
3361 unless ( $self->{_sql_looking_at}{effectiveid} ) {
3362 # instead of EffectiveId = id we do IsMerged IS NULL
3364 FIELD => 'IsMerged',
3367 ENTRYAGGREGATOR => 'AND',
3371 unless ( $self->{_sql_looking_at}{type} ) {
3372 $self->Limit( FIELD => 'Type', VALUE => 'ticket' );
3375 # We don't want deleted tickets unless 'allow_deleted_search' is set
3376 unless( $self->{'allow_deleted_search'} ) {
3384 # set SB's dirty flag
3385 $self->{'must_redo_search'} = 1;
3386 $self->{'RecalcTicketLimits'} = 0;
3388 return (1, $self->loc("Valid Query"));
3393 Returns the last string passed to L</FromSQL>.
3399 return $self->{_sql_query};
3402 sub _check_bundling_possibility {
3405 my @list = reverse @_;
3406 while (my $e = shift @list) {
3408 if ( lc($e) eq 'and' ) {
3411 elsif ( lc($e) eq 'or' ) {
3417 "Joins optimization failed when parsing '$string'. It's bug in RT, contact Best Practical"
3419 die "Internal error. Contact your system administrator.";
3425 RT::Base->_ImportOverlays();