1 # BEGIN BPS TAGGED BLOCK {{{
5 # This software is Copyright (c) 1996-2014 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 # - Decimated ProcessRestrictions and broke it into multiple
52 # functions joined by a LUT
53 # - Semi-Generic SQL stuff moved to another file
55 # Known Issues: FIXME!
57 # - ClearRestrictions and Reinitialization is messy and unclear. The
58 # only good way to do it is to create a new RT::Tickets object.
62 RT::Tickets - A collection of Ticket objects
68 my $tickets = RT::Tickets->new($CurrentUser);
72 A collection of RT::Tickets.
87 use base 'RT::SearchBuilder';
89 sub Table { 'Tickets'}
92 use DBIx::SearchBuilder::Unique;
94 # Configuration Tables:
96 # FIELD_METADATA is a mapping of searchable Field name, to Type, and other
99 our %FIELD_METADATA = (
100 Status => [ 'ENUM', ], #loc_left_pair
101 Queue => [ 'ENUM' => 'Queue', ], #loc_left_pair
102 Type => [ 'ENUM', ], #loc_left_pair
103 Creator => [ 'ENUM' => 'User', ], #loc_left_pair
104 LastUpdatedBy => [ 'ENUM' => 'User', ], #loc_left_pair
105 Owner => [ 'WATCHERFIELD' => 'Owner', ], #loc_left_pair
106 EffectiveId => [ 'INT', ], #loc_left_pair
107 id => [ 'ID', ], #loc_left_pair
108 InitialPriority => [ 'INT', ], #loc_left_pair
109 FinalPriority => [ 'INT', ], #loc_left_pair
110 Priority => [ 'INT', ], #loc_left_pair
111 TimeLeft => [ 'INT', ], #loc_left_pair
112 TimeWorked => [ 'INT', ], #loc_left_pair
113 TimeEstimated => [ 'INT', ], #loc_left_pair
115 Linked => [ 'LINK' ], #loc_left_pair
116 LinkedTo => [ 'LINK' => 'To' ], #loc_left_pair
117 LinkedFrom => [ 'LINK' => 'From' ], #loc_left_pair
118 MemberOf => [ 'LINK' => To => 'MemberOf', ], #loc_left_pair
119 DependsOn => [ 'LINK' => To => 'DependsOn', ], #loc_left_pair
120 RefersTo => [ 'LINK' => To => 'RefersTo', ], #loc_left_pair
121 HasMember => [ 'LINK' => From => 'MemberOf', ], #loc_left_pair
122 DependentOn => [ 'LINK' => From => 'DependsOn', ], #loc_left_pair
123 DependedOnBy => [ 'LINK' => From => 'DependsOn', ], #loc_left_pair
124 ReferredToBy => [ 'LINK' => From => 'RefersTo', ], #loc_left_pair
125 Told => [ 'DATE' => 'Told', ], #loc_left_pair
126 Starts => [ 'DATE' => 'Starts', ], #loc_left_pair
127 Started => [ 'DATE' => 'Started', ], #loc_left_pair
128 Due => [ 'DATE' => 'Due', ], #loc_left_pair
129 Resolved => [ 'DATE' => 'Resolved', ], #loc_left_pair
130 LastUpdated => [ 'DATE' => 'LastUpdated', ], #loc_left_pair
131 Created => [ 'DATE' => 'Created', ], #loc_left_pair
132 Subject => [ 'STRING', ], #loc_left_pair
133 Content => [ 'TRANSCONTENT', ], #loc_left_pair
134 ContentType => [ 'TRANSFIELD', ], #loc_left_pair
135 Filename => [ 'TRANSFIELD', ], #loc_left_pair
136 TransactionDate => [ 'TRANSDATE', ], #loc_left_pair
137 Requestor => [ 'WATCHERFIELD' => 'Requestor', ], #loc_left_pair
138 Requestors => [ 'WATCHERFIELD' => 'Requestor', ], #loc_left_pair
139 Cc => [ 'WATCHERFIELD' => 'Cc', ], #loc_left_pair
140 AdminCc => [ 'WATCHERFIELD' => 'AdminCc', ], #loc_left_pair
141 Watcher => [ 'WATCHERFIELD', ], #loc_left_pair
142 QueueCc => [ 'WATCHERFIELD' => 'Cc' => 'Queue', ], #loc_left_pair
143 QueueAdminCc => [ 'WATCHERFIELD' => 'AdminCc' => 'Queue', ], #loc_left_pair
144 QueueWatcher => [ 'WATCHERFIELD' => undef => 'Queue', ], #loc_left_pair
145 CustomFieldValue => [ 'CUSTOMFIELD' => 'Ticket' ], #loc_left_pair
146 CustomField => [ 'CUSTOMFIELD' => 'Ticket' ], #loc_left_pair
147 CF => [ 'CUSTOMFIELD' => 'Ticket' ], #loc_left_pair
148 Updated => [ 'TRANSDATE', ], #loc_left_pair
149 RequestorGroup => [ 'MEMBERSHIPFIELD' => 'Requestor', ], #loc_left_pair
150 CCGroup => [ 'MEMBERSHIPFIELD' => 'Cc', ], #loc_left_pair
151 AdminCCGroup => [ 'MEMBERSHIPFIELD' => 'AdminCc', ], #loc_left_pair
152 WatcherGroup => [ 'MEMBERSHIPFIELD', ], #loc_left_pair
153 HasAttribute => [ 'HASATTRIBUTE', 1 ],
154 HasNoAttribute => [ 'HASATTRIBUTE', 0 ],
156 Customer => [ 'FREESIDEFIELD' => 'Customer' ],
157 Service => [ 'FREESIDEFIELD' => 'Service' ],
158 WillResolve => [ 'DATE' => 'WillResolve', ], #loc_left_pair
161 # Lower Case version of FIELDS, for case insensitivity
162 our %LOWER_CASE_FIELDS = map { ( lc($_) => $_ ) } (keys %FIELD_METADATA);
164 our %SEARCHABLE_SUBFIELDS = (
166 EmailAddress Name RealName Nickname Organization Address1 Address2
167 WorkPhone HomePhone MobilePhone PagerPhone id
171 # Mapping of Field Type to Function
173 ENUM => \&_EnumLimit,
176 LINK => \&_LinkLimit,
177 DATE => \&_DateLimit,
178 STRING => \&_StringLimit,
179 TRANSFIELD => \&_TransLimit,
180 TRANSCONTENT => \&_TransContentLimit,
181 TRANSDATE => \&_TransDateLimit,
182 WATCHERFIELD => \&_WatcherLimit,
183 MEMBERSHIPFIELD => \&_WatcherMembershipLimit,
184 CUSTOMFIELD => \&_CustomFieldLimit,
185 HASATTRIBUTE => \&_HasAttributeLimit,
186 FREESIDEFIELD => \&_FreesideFieldLimit,
188 our %can_bundle = ();# WATCHERFIELD => "yes", );
190 # Default EntryAggregator per type
191 # if you specify OP, you must specify all valid OPs
232 # Helper functions for passing the above lexically scoped tables above
234 sub FIELDS { return \%FIELD_METADATA }
235 sub dispatch { return \%dispatch }
236 sub can_bundle { return \%can_bundle }
238 # Bring in the clowns.
239 require RT::Tickets_SQL;
242 our @SORTFIELDS = qw(id Status
244 Owner Created Due Starts Started
246 Resolved LastUpdated Priority TimeWorked TimeLeft);
250 Returns the list of fields that lists of tickets can easily be sorted by
256 return (@SORTFIELDS);
260 # BEGIN SQL STUFF *********************************
265 $self->SUPER::CleanSlate( @_ );
266 delete $self->{$_} foreach qw(
268 _sql_group_members_aliases
269 _sql_object_cfv_alias
270 _sql_role_group_aliases
272 _sql_u_watchers_alias_for_sort
273 _sql_u_watchers_aliases
274 _sql_current_user_can_see_applied
278 =head1 Limit Helper Routines
280 These routines are the targets of a dispatch table depending on the
281 type of field. They all share the same signature:
283 my ($self,$field,$op,$value,@rest) = @_;
285 The values in @rest should be suitable for passing directly to
286 DBIx::SearchBuilder::Limit.
288 Essentially they are an expanded/broken out (and much simplified)
289 version of what ProcessRestrictions used to do. They're also much
290 more clearly delineated by the TYPE of field being processed.
299 my ( $sb, $field, $op, $value, @rest ) = @_;
301 if ( $value eq '__Bookmarked__' ) {
302 return $sb->_BookmarkLimit( $field, $op, $value, @rest );
304 return $sb->_IntLimit( $field, $op, $value, @rest );
309 my ( $sb, $field, $op, $value, @rest ) = @_;
311 die "Invalid operator $op for __Bookmarked__ search on $field"
312 unless $op =~ /^(=|!=)$/;
315 my $tmp = $sb->CurrentUser->UserObj->FirstAttribute('Bookmarks');
316 $tmp = $tmp->Content if $tmp;
321 return $sb->_SQLLimit(
328 # as bookmarked tickets can be merged we have to use a join
329 # but it should be pretty lightweight
330 my $tickets_alias = $sb->Join(
335 FIELD2 => 'EffectiveId',
339 my $ea = $op eq '='? 'OR': 'AND';
340 foreach my $id ( sort @bookmarks ) {
342 ALIAS => $tickets_alias,
346 $first? (@rest): ( ENTRYAGGREGATOR => $ea )
348 $first = 0 if $first;
355 Handle Fields which are limited to certain values, and potentially
356 need to be looked up from another class.
358 This subroutine actually handles two different kinds of fields. For
359 some the user is responsible for limiting the values. (i.e. Status,
362 For others, the value specified by the user will be looked by via
366 name of class to lookup in (Optional)
371 my ( $sb, $field, $op, $value, @rest ) = @_;
373 # SQL::Statement changes != to <>. (Can we remove this now?)
374 $op = "!=" if $op eq "<>";
376 die "Invalid Operation: $op for $field"
380 my $meta = $FIELD_METADATA{$field};
381 if ( defined $meta->[1] && defined $value && $value !~ /^\d+$/ ) {
382 my $class = "RT::" . $meta->[1];
383 my $o = $class->new( $sb->CurrentUser );
385 $value = $o->Id || 0;
386 } elsif ( $field eq "Type" ) {
387 $value = lc $value if $value =~ /^(ticket|approval|reminder)$/i;
388 } elsif ($field eq "Status") {
401 Handle fields where the values are limited to integers. (For example,
402 Priority, TimeWorked.)
410 my ( $sb, $field, $op, $value, @rest ) = @_;
412 die "Invalid Operator $op for $field"
413 unless $op =~ /^(=|!=|>|<|>=|<=)$/;
425 Handle fields which deal with links between tickets. (MemberOf, DependsOn)
428 1: Direction (From, To)
429 2: Link Type (MemberOf, DependsOn, RefersTo)
434 my ( $sb, $field, $op, $value, @rest ) = @_;
436 my $meta = $FIELD_METADATA{$field};
437 die "Invalid Operator $op for $field" unless $op =~ /^(=|!=|IS|IS NOT)$/io;
440 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
444 $is_null = 1 if !$value || $value =~ /^null$/io;
446 my $direction = $meta->[1] || '';
447 my ($matchfield, $linkfield) = ('', '');
448 if ( $direction eq 'To' ) {
449 ($matchfield, $linkfield) = ("Target", "Base");
451 elsif ( $direction eq 'From' ) {
452 ($matchfield, $linkfield) = ("Base", "Target");
454 elsif ( $direction ) {
455 die "Invalid link direction '$direction' for $field\n";
458 $sb->_LinkLimit( 'LinkedTo', $op, $value, @rest );
460 'LinkedFrom', $op, $value, @rest,
461 ENTRYAGGREGATOR => (($is_negative && $is_null) || (!$is_null && !$is_negative))? 'OR': 'AND',
469 $op = ($op =~ /^(=|IS)$/i)? 'IS': 'IS NOT';
471 elsif ( $value =~ /\D/ ) {
472 $value = RT::URI->new( $sb->CurrentUser )->CanonicalizeURI( $value );
475 $matchfield = "Local$matchfield" if $is_local;
477 #For doing a left join to find "unlinked tickets" we want to generate a query that looks like this
478 # SELECT main.* FROM Tickets main
479 # LEFT JOIN Links Links_1 ON ( (Links_1.Type = 'MemberOf')
480 # AND(main.id = Links_1.LocalTarget))
481 # WHERE Links_1.LocalBase IS NULL;
484 my $linkalias = $sb->Join(
489 FIELD2 => 'Local' . $linkfield
492 LEFTJOIN => $linkalias,
500 FIELD => $matchfield,
507 my $linkalias = $sb->Join(
512 FIELD2 => 'Local' . $linkfield
515 LEFTJOIN => $linkalias,
521 LEFTJOIN => $linkalias,
522 FIELD => $matchfield,
529 FIELD => $matchfield,
530 OPERATOR => $is_negative? 'IS': 'IS NOT',
539 Handle date fields. (Created, LastTold..)
542 1: type of link. (Probably not necessary.)
547 my ( $sb, $field, $op, $value, @rest ) = @_;
549 die "Invalid Date Op: $op"
550 unless $op =~ /^(=|>|<|>=|<=)$/;
552 my $meta = $FIELD_METADATA{$field};
553 die "Incorrect Meta Data for $field"
554 unless ( defined $meta->[1] );
556 $sb->_DateFieldLimit( $meta->[1], $op, $value, @rest );
559 # Factor this out for use by custom fields
561 sub _DateFieldLimit {
562 my ( $sb, $field, $op, $value, @rest ) = @_;
564 my $date = RT::Date->new( $sb->CurrentUser );
565 $date->Set( Format => 'unknown', Value => $value );
569 # if we're specifying =, that means we want everything on a
570 # particular single day. in the database, we need to check for >
571 # and < the edges of that day.
573 # Except if the value is 'this month' or 'last month', check
574 # > and < the edges of the month.
576 my ($daystart, $dayend);
577 if ( lc($value) eq 'this month' ) {
579 $date->SetToStart('month', Timezone => 'server');
580 $daystart = $date->ISO;
581 $date->AddMonth(Timezone => 'server');
582 $dayend = $date->ISO;
584 elsif ( lc($value) eq 'last month' ) {
586 $date->SetToStart('month', Timezone => 'server');
587 $dayend = $date->ISO;
589 $date->SetToStart('month', Timezone => 'server');
590 $daystart = $date->ISO;
593 $date->SetToMidnight( Timezone => 'server' );
594 $daystart = $date->ISO;
596 $dayend = $date->ISO;
613 ENTRYAGGREGATOR => 'AND',
631 Handle simple fields which are just strings. (Subject,Type)
639 my ( $sb, $field, $op, $value, @rest ) = @_;
643 # =, !=, LIKE, NOT LIKE
644 if ( RT->Config->Get('DatabaseType') eq 'Oracle'
645 && (!defined $value || !length $value)
646 && lc($op) ne 'is' && lc($op) ne 'is not'
648 if ($op eq '!=' || $op =~ /^NOT\s/i) {
665 =head2 _TransDateLimit
667 Handle fields limiting based on Transaction Date.
669 The inpupt value must be in a format parseable by Time::ParseDate
676 # This routine should really be factored into translimit.
677 sub _TransDateLimit {
678 my ( $sb, $field, $op, $value, @rest ) = @_;
680 # See the comments for TransLimit, they apply here too
682 my $txn_alias = $sb->JoinTransactions;
684 my $date = RT::Date->new( $sb->CurrentUser );
685 $date->Set( Format => 'unknown', Value => $value );
690 # if we're specifying =, that means we want everything on a
691 # particular single day. in the database, we need to check for >
692 # and < the edges of that day.
694 $date->SetToMidnight( Timezone => 'server' );
695 my $daystart = $date->ISO;
697 my $dayend = $date->ISO;
712 ENTRYAGGREGATOR => 'AND',
717 # not searching for a single day
720 #Search for the right field
735 Limit based on the ContentType or the Filename of a transaction.
740 my ( $self, $field, $op, $value, %rest ) = @_;
742 my $txn_alias = $self->JoinTransactions;
743 unless ( defined $self->{_sql_trattachalias} ) {
744 $self->{_sql_trattachalias} = $self->_SQLJoin(
745 TYPE => 'LEFT', # not all txns have an attachment
746 ALIAS1 => $txn_alias,
748 TABLE2 => 'Attachments',
749 FIELD2 => 'TransactionId',
755 ALIAS => $self->{_sql_trattachalias},
763 =head2 _TransContentLimit
765 Limit based on the Content of a transaction.
769 sub _TransContentLimit {
773 # If only this was this simple. We've got to do something
776 #Basically, we want to make sure that the limits apply to
777 #the same attachment, rather than just another attachment
778 #for the same ticket, no matter how many clauses we lump
779 #on. We put them in TicketAliases so that they get nuked
780 #when we redo the join.
782 # In the SQL, we might have
783 # (( Content = foo ) or ( Content = bar AND Content = baz ))
784 # The AND group should share the same Alias.
786 # Actually, maybe it doesn't matter. We use the same alias and it
787 # works itself out? (er.. different.)
789 # Steal more from _ProcessRestrictions
791 # FIXME: Maybe look at the previous FooLimit call, and if it was a
792 # TransLimit and EntryAggregator == AND, reuse the Aliases?
794 # Or better - store the aliases on a per subclause basis - since
795 # those are going to be the things we want to relate to each other,
798 # maybe we should not allow certain kinds of aggregation of these
799 # clauses and do a psuedo regex instead? - the problem is getting
800 # them all into the same subclause when you have (A op B op C) - the
801 # way they get parsed in the tree they're in different subclauses.
803 my ( $self, $field, $op, $value, %rest ) = @_;
804 $field = 'Content' if $field =~ /\W/;
806 my $config = RT->Config->Get('FullTextSearch') || {};
807 unless ( $config->{'Enable'} ) {
808 $self->_SQLLimit( %rest, FIELD => 'id', VALUE => 0 );
812 my $txn_alias = $self->JoinTransactions;
813 unless ( defined $self->{_sql_trattachalias} ) {
814 $self->{_sql_trattachalias} = $self->_SQLJoin(
815 TYPE => 'LEFT', # not all txns have an attachment
816 ALIAS1 => $txn_alias,
818 TABLE2 => 'Attachments',
819 FIELD2 => 'TransactionId',
824 if ( $config->{'Indexed'} ) {
825 my $db_type = RT->Config->Get('DatabaseType');
828 if ( $config->{'Table'} and $config->{'Table'} ne "Attachments") {
829 $alias = $self->{'_sql_aliases'}{'full_text'} ||= $self->_SQLJoin(
831 ALIAS1 => $self->{'_sql_trattachalias'},
833 TABLE2 => $config->{'Table'},
837 $alias = $self->{'_sql_trattachalias'};
840 #XXX: handle negative searches
841 my $index = $config->{'Column'};
842 if ( $db_type eq 'Oracle' ) {
843 my $dbh = $RT::Handle->dbh;
844 my $alias = $self->{_sql_trattachalias};
847 FUNCTION => "CONTAINS( $alias.$field, ".$dbh->quote($value) .")",
853 # this is required to trick DBIx::SB's LEFT JOINS optimizer
854 # into deciding that join is redundant as it is
856 ENTRYAGGREGATOR => 'AND',
857 ALIAS => $self->{_sql_trattachalias},
859 OPERATOR => 'IS NOT',
863 elsif ( $db_type eq 'Pg' ) {
864 my $dbh = $RT::Handle->dbh;
870 VALUE => 'plainto_tsquery('. $dbh->quote($value) .')',
874 elsif ( $db_type eq 'mysql' ) {
875 # XXX: We could theoretically skip the join to Attachments,
876 # and have Sphinx simply index and group by the TicketId,
877 # and join Ticket.id to that attribute, which would be much
878 # more efficient -- however, this is only a possibility if
879 # there are no other transaction limits.
881 # This is a special character. Note that \ does not escape
882 # itself (in Sphinx 2.1.0, at least), so 'foo\;bar' becoming
883 # 'foo\\;bar' is not a vulnerability, and is still parsed as
884 # "foo, \, ;, then bar". Happily, the default mode is
885 # "all", meaning that boolean operators are not special.
888 my $max = $config->{'MaxMatches'};
894 VALUE => "$value;limit=$max;maxmatches=$max",
900 ALIAS => $self->{_sql_trattachalias},
907 if ( RT->Config->Get('DontSearchFileAttachments') ) {
909 ENTRYAGGREGATOR => 'AND',
910 ALIAS => $self->{_sql_trattachalias},
921 Handle watcher limits. (Requestor, CC, etc..)
937 my $meta = $FIELD_METADATA{ $field };
938 my $type = $meta->[1] || '';
939 my $class = $meta->[2] || 'Ticket';
941 # Bail if the subfield is not allowed
943 and not grep { $_ eq $rest{SUBKEY} } @{$SEARCHABLE_SUBFIELDS{'User'}})
945 die "Invalid watcher subfield: '$rest{SUBKEY}'";
948 # if it's equality op and search by Email or Name then we can preload user
949 # we do it to help some DBs better estimate number of rows and get better plans
950 if ( $op =~ /^!?=$/ && (!$rest{'SUBKEY'} || $rest{'SUBKEY'} eq 'Name' || $rest{'SUBKEY'} eq 'EmailAddress') ) {
951 my $o = RT::User->new( $self->CurrentUser );
954 ? $field eq 'Owner'? 'Load' : 'LoadByEmail'
955 : $rest{'SUBKEY'} eq 'EmailAddress' ? 'LoadByEmail': 'Load';
956 $o->$method( $value );
957 $rest{'SUBKEY'} = 'id';
958 $value = $o->id || 0;
961 # Owner was ENUM field, so "Owner = 'xxx'" allowed user to
962 # search by id and Name at the same time, this is workaround
963 # to preserve backward compatibility
964 if ( $field eq 'Owner' ) {
965 if ( ($rest{'SUBKEY'}||'') eq 'id' ) {
975 $rest{SUBKEY} ||= 'EmailAddress';
977 my ($groups, $group_members, $users);
978 if ( $rest{'BUNDLE'} ) {
979 ($groups, $group_members, $users) = @{ $rest{'BUNDLE'} };
981 $groups = $self->_RoleGroupsJoin( Type => $type, Class => $class, New => !$type );
985 if ( $op =~ /^IS(?: NOT)?$/i ) {
986 # is [not] empty case
988 $group_members ||= $self->_GroupMembersJoin( GroupsAlias => $groups );
989 # to avoid joining the table Users into the query, we just join GM
990 # and make sure we don't match records where group is member of itself
992 LEFTJOIN => $group_members,
995 VALUE => "$group_members.MemberId",
999 ALIAS => $group_members,
1006 elsif ( $op =~ /^!=$|^NOT\s+/i ) {
1007 # negative condition case
1010 $op =~ s/!|NOT\s+//i;
1012 # XXX: we have no way to build correct "Watcher.X != 'Y'" when condition
1013 # "X = 'Y'" matches more then one user so we try to fetch two records and
1014 # do the right thing when there is only one exist and semi-working solution
1016 my $users_obj = RT::Users->new( $self->CurrentUser );
1018 FIELD => $rest{SUBKEY},
1022 $users_obj->OrderBy;
1023 $users_obj->RowsPerPage(2);
1024 my @users = @{ $users_obj->ItemsArrayRef };
1026 $group_members ||= $self->_GroupMembersJoin( GroupsAlias => $groups );
1027 if ( @users <= 1 ) {
1029 $uid = $users[0]->id if @users;
1030 $self->SUPER::Limit(
1031 LEFTJOIN => $group_members,
1032 ALIAS => $group_members,
1033 FIELD => 'MemberId',
1038 ALIAS => $group_members,
1044 $self->SUPER::Limit(
1045 LEFTJOIN => $group_members,
1048 VALUE => "$group_members.MemberId",
1051 $users ||= $self->Join(
1053 ALIAS1 => $group_members,
1054 FIELD1 => 'MemberId',
1058 $self->SUPER::Limit(
1061 FIELD => $rest{SUBKEY},
1075 # positive condition case
1077 $group_members ||= $self->_GroupMembersJoin(
1078 GroupsAlias => $groups, New => 1, Left => 0
1080 $users ||= $self->Join(
1082 ALIAS1 => $group_members,
1083 FIELD1 => 'MemberId',
1090 FIELD => $rest{'SUBKEY'},
1097 return ($groups, $group_members, $users);
1100 sub _RoleGroupsJoin {
1102 my %args = (New => 0, Class => 'Ticket', Type => '', @_);
1103 return $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1104 if $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1107 # we always have watcher groups for ticket, so we use INNER join
1108 my $groups = $self->Join(
1110 FIELD1 => $args{'Class'} eq 'Queue'? 'Queue': 'id',
1112 FIELD2 => 'Instance',
1113 ENTRYAGGREGATOR => 'AND',
1115 $self->SUPER::Limit(
1116 LEFTJOIN => $groups,
1119 VALUE => 'RT::'. $args{'Class'} .'-Role',
1121 $self->SUPER::Limit(
1122 LEFTJOIN => $groups,
1125 VALUE => $args{'Type'},
1128 $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} } = $groups
1129 unless $args{'New'};
1134 sub _GroupMembersJoin {
1136 my %args = (New => 1, GroupsAlias => undef, Left => 1, @_);
1138 return $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1139 if $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1142 my $alias = $self->Join(
1143 $args{'Left'} ? (TYPE => 'LEFT') : (),
1144 ALIAS1 => $args{'GroupsAlias'},
1146 TABLE2 => 'CachedGroupMembers',
1147 FIELD2 => 'GroupId',
1148 ENTRYAGGREGATOR => 'AND',
1150 $self->SUPER::Limit(
1151 $args{'Left'} ? (LEFTJOIN => $alias) : (),
1153 FIELD => 'Disabled',
1157 $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} } = $alias
1158 unless $args{'New'};
1165 Helper function which provides joins to a watchers table both for limits
1172 my $type = shift || '';
1175 my $groups = $self->_RoleGroupsJoin( Type => $type );
1176 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
1177 # XXX: work around, we must hide groups that
1178 # are members of the role group we search in,
1179 # otherwise them result in wrong NULLs in Users
1180 # table and break ordering. Now, we know that
1181 # RT doesn't allow to add groups as members of the
1182 # ticket roles, so we just hide entries in CGM table
1183 # with MemberId == GroupId from results
1184 $self->SUPER::Limit(
1185 LEFTJOIN => $group_members,
1188 VALUE => "$group_members.MemberId",
1191 my $users = $self->Join(
1193 ALIAS1 => $group_members,
1194 FIELD1 => 'MemberId',
1198 return ($groups, $group_members, $users);
1201 =head2 _WatcherMembershipLimit
1203 Handle watcher membership limits, i.e. whether the watcher belongs to a
1204 specific group or not.
1207 1: Field to query on
1209 SELECT DISTINCT main.*
1213 CachedGroupMembers CachedGroupMembers_2,
1216 (main.EffectiveId = main.id)
1218 (main.Status != 'deleted')
1220 (main.Type = 'ticket')
1223 (Users_3.EmailAddress = '22')
1225 (Groups_1.Domain = 'RT::Ticket-Role')
1227 (Groups_1.Type = 'RequestorGroup')
1230 Groups_1.Instance = main.id
1232 Groups_1.id = CachedGroupMembers_2.GroupId
1234 CachedGroupMembers_2.MemberId = Users_3.id
1235 ORDER BY main.id ASC
1240 sub _WatcherMembershipLimit {
1241 my ( $self, $field, $op, $value, @rest ) = @_;
1246 my $groups = $self->NewAlias('Groups');
1247 my $groupmembers = $self->NewAlias('CachedGroupMembers');
1248 my $users = $self->NewAlias('Users');
1249 my $memberships = $self->NewAlias('CachedGroupMembers');
1251 if ( ref $field ) { # gross hack
1252 my @bundle = @$field;
1254 for my $chunk (@bundle) {
1255 ( $field, $op, $value, @rest ) = @$chunk;
1257 ALIAS => $memberships,
1268 ALIAS => $memberships,
1276 # Tie to groups for tickets we care about
1280 VALUE => 'RT::Ticket-Role',
1281 ENTRYAGGREGATOR => 'AND'
1286 FIELD1 => 'Instance',
1293 # If we care about which sort of watcher
1294 my $meta = $FIELD_METADATA{$field};
1295 my $type = ( defined $meta->[1] ? $meta->[1] : undef );
1302 ENTRYAGGREGATOR => 'AND'
1309 ALIAS2 => $groupmembers,
1314 ALIAS1 => $groupmembers,
1315 FIELD1 => 'MemberId',
1321 ALIAS => $groupmembers,
1322 FIELD => 'Disabled',
1327 ALIAS1 => $memberships,
1328 FIELD1 => 'MemberId',
1334 ALIAS => $memberships,
1335 FIELD => 'Disabled',
1344 =head2 _CustomFieldDecipher
1346 Try and turn a CF descriptor into (cfid, cfname) object pair.
1348 Takes an optional second parameter of the CF LookupType, defaults to Ticket CFs.
1352 sub _CustomFieldDecipher {
1353 my ($self, $string, $lookuptype) = @_;
1354 $lookuptype ||= $self->_SingularClass->CustomFieldLookupType;
1356 my ($object, $field, $column) = ($string =~ /^(?:(.+?)\.)?\{(.+)\}(?:\.(Content|LargeContent))?$/);
1357 $field ||= ($string =~ /^{(.*?)}$/)[0] || $string;
1359 my ($cf, $applied_to);
1362 my $record_class = RT::CustomField->RecordClassFromLookupType($lookuptype);
1363 $applied_to = $record_class->new( $self->CurrentUser );
1364 $applied_to->Load( $object );
1366 if ( $applied_to->id ) {
1367 RT->Logger->debug("Limiting to CFs identified by '$field' applied to $record_class #@{[$applied_to->id]} (loaded via '$object')");
1370 RT->Logger->warning("$record_class '$object' doesn't exist, parsed from '$string'");
1376 if ( $field =~ /\D/ ) {
1378 my $cfs = RT::CustomFields->new( $self->CurrentUser );
1379 $cfs->Limit( FIELD => 'Name', VALUE => $field, ($applied_to ? (CASESENSITIVE => 0) : ()) );
1380 $cfs->LimitToLookupType($lookuptype);
1383 $cfs->SetContextObject($applied_to);
1384 $cfs->LimitToObjectId($applied_to->id);
1387 # if there is more then one field the current user can
1388 # see with the same name then we shouldn't return cf object
1389 # as we don't know which one to use
1392 $cf = undef if $cfs->Next;
1396 $cf = RT::CustomField->new( $self->CurrentUser );
1397 $cf->Load( $field );
1398 $cf->SetContextObject($applied_to)
1399 if $cf->id and $applied_to;
1402 return ($object, $field, $cf, $column);
1405 =head2 _CustomFieldJoin
1407 Factor out the Join of custom fields so we can use it for sorting too
1411 our %JOIN_ALIAS_FOR_LOOKUP_TYPE = (
1412 RT::Ticket->CustomFieldLookupType => sub { "main" },
1415 sub _CustomFieldJoin {
1416 my ($self, $cfkey, $cfid, $field, $type) = @_;
1417 $type ||= RT::Ticket->CustomFieldLookupType;
1419 # Perform one Join per CustomField
1420 if ( $self->{_sql_object_cfv_alias}{$cfkey} ||
1421 $self->{_sql_cf_alias}{$cfkey} )
1423 return ( $self->{_sql_object_cfv_alias}{$cfkey},
1424 $self->{_sql_cf_alias}{$cfkey} );
1427 my $ObjectAlias = $JOIN_ALIAS_FOR_LOOKUP_TYPE{$type}
1428 ? $JOIN_ALIAS_FOR_LOOKUP_TYPE{$type}->($self)
1429 : die "We don't know how to join on $type";
1431 my ($ObjectCFs, $CFs);
1433 $ObjectCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1435 ALIAS1 => $ObjectAlias,
1437 TABLE2 => 'ObjectCustomFieldValues',
1438 FIELD2 => 'ObjectId',
1440 $self->SUPER::Limit(
1441 LEFTJOIN => $ObjectCFs,
1442 FIELD => 'CustomField',
1444 ENTRYAGGREGATOR => 'AND'
1448 my $ocfalias = $self->Join(
1451 TABLE2 => 'ObjectCustomFields',
1452 FIELD2 => 'ObjectId',
1455 $self->SUPER::Limit(
1456 LEFTJOIN => $ocfalias,
1457 ENTRYAGGREGATOR => 'OR',
1458 FIELD => 'ObjectId',
1462 $CFs = $self->{_sql_cf_alias}{$cfkey} = $self->Join(
1464 ALIAS1 => $ocfalias,
1465 FIELD1 => 'CustomField',
1466 TABLE2 => 'CustomFields',
1469 $self->SUPER::Limit(
1471 ENTRYAGGREGATOR => 'AND',
1472 FIELD => 'LookupType',
1475 $self->SUPER::Limit(
1477 ENTRYAGGREGATOR => 'AND',
1482 $ObjectCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1486 TABLE2 => 'ObjectCustomFieldValues',
1487 FIELD2 => 'CustomField',
1489 $self->SUPER::Limit(
1490 LEFTJOIN => $ObjectCFs,
1491 FIELD => 'ObjectId',
1492 VALUE => "$ObjectAlias.id",
1494 ENTRYAGGREGATOR => 'AND',
1498 $self->SUPER::Limit(
1499 LEFTJOIN => $ObjectCFs,
1500 FIELD => 'ObjectType',
1501 VALUE => RT::CustomField->ObjectTypeFromLookupType($type),
1502 ENTRYAGGREGATOR => 'AND'
1504 $self->SUPER::Limit(
1505 LEFTJOIN => $ObjectCFs,
1506 FIELD => 'Disabled',
1509 ENTRYAGGREGATOR => 'AND'
1512 return ($ObjectCFs, $CFs);
1515 =head2 _CustomFieldLimit
1517 Limit based on CustomFields
1524 use Regexp::Common qw(RE_net_IPv4);
1525 use Regexp::Common::net::CIDR;
1528 sub _CustomFieldLimit {
1529 my ( $self, $_field, $op, $value, %rest ) = @_;
1531 my $meta = $FIELD_METADATA{ $_field };
1532 my $class = $meta->[1] || 'Ticket';
1533 my $type = "RT::$class"->CustomFieldLookupType;
1535 my $field = $rest{'SUBKEY'} || die "No field specified";
1537 # For our sanity, we can only limit on one queue at a time
1539 my ($object, $cfid, $cf, $column);
1540 ($object, $field, $cf, $column) = $self->_CustomFieldDecipher( $field, $type );
1541 $cfid = $cf ? $cf->id : 0 ;
1543 # If we're trying to find custom fields that don't match something, we
1544 # want tickets where the custom field has no value at all. Note that
1545 # we explicitly don't include the "IS NULL" case, since we would
1546 # otherwise end up with a redundant clause.
1548 my ($negative_op, $null_op, $inv_op, $range_op)
1549 = $self->ClassifySQLOperation( $op );
1552 return @_ unless RT->Config->Get('DatabaseType') eq 'Oracle';
1555 return %args unless $args{'FIELD'} eq 'LargeContent';
1557 my $op = $args{'OPERATOR'};
1559 $args{'OPERATOR'} = 'MATCHES';
1561 elsif ( $op eq '!=' ) {
1562 $args{'OPERATOR'} = 'NOT MATCHES';
1564 elsif ( $op =~ /^[<>]=?$/ ) {
1565 $args{'FUNCTION'} = "TO_CHAR( $args{'ALIAS'}.LargeContent )";
1570 if ( $cf && $cf->Type eq 'IPAddress' ) {
1571 my $parsed = RT::ObjectCustomFieldValue->ParseIP($value);
1576 $RT::Logger->warn("$value is not a valid IPAddress");
1580 if ( $cf && $cf->Type eq 'IPAddressRange' ) {
1582 if ( $value =~ /^\s*$RE{net}{CIDR}{IPv4}{-keep}\s*$/o ) {
1584 # convert incomplete 192.168/24 to 192.168.0.0/24 format
1586 join( '.', map $_ || 0, ( split /\./, $1 )[ 0 .. 3 ] ) . "/$2"
1590 my ( $start_ip, $end_ip ) =
1591 RT::ObjectCustomFieldValue->ParseIPRange($value);
1592 if ( $start_ip && $end_ip ) {
1593 if ( $op =~ /^([<>])=?$/ ) {
1594 my $is_less = $1 eq '<' ? 1 : 0;
1603 $value = join '-', $start_ip, $end_ip;
1607 $RT::Logger->warn("$value is not a valid IPAddressRange");
1611 if ( $cf && $cf->Type =~ /^Date(?:Time)?$/ ) {
1612 my $date = RT::Date->new( $self->CurrentUser );
1613 $date->Set( Format => 'unknown', Value => $value );
1614 if ( $date->Unix ) {
1618 || $value =~ /^\s*(?:today|tomorrow|yesterday)\s*$/i
1619 || ( $value !~ /midnight|\d+:\d+:\d+/i
1620 && $date->Time( Timezone => 'user' ) eq '00:00:00' )
1623 $value = $date->Date( Timezone => 'user' );
1626 $value = $date->DateTime;
1630 $RT::Logger->warn("$value is not a valid date string");
1634 my $single_value = !$cf || !$cfid || $cf->SingleValue;
1636 my $cfkey = $cfid ? $cfid : "$type-$object.$field";
1638 if ( $null_op && !$column ) {
1639 # IS[ NOT] NULL without column is the same as has[ no] any CF value,
1640 # we can reuse our default joins for this operation
1641 # with column specified we have different situation
1642 my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field, $type );
1645 ALIAS => $ObjectCFs,
1654 OPERATOR => 'IS NOT',
1657 ENTRYAGGREGATOR => 'AND',
1661 elsif ( $op !~ /^[<>]=?$/ && ( $cf && $cf->Type eq 'IPAddressRange')) {
1663 my ($start_ip, $end_ip) = split /-/, $value;
1666 if ( $op !~ /NOT|!=|<>/i ) { # positive equation
1667 $self->_CustomFieldLimit(
1668 $_field, '<=', $end_ip, %rest,
1669 SUBKEY => $rest{'SUBKEY'}. '.Content',
1671 $self->_CustomFieldLimit(
1672 $_field, '>=', $start_ip, %rest,
1673 SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1674 ENTRYAGGREGATOR => 'AND',
1676 # as well limit borders so DB optimizers can use better
1677 # estimations and scan less rows
1678 # have to disable this tweak because of ipv6
1679 # $self->_CustomFieldLimit(
1680 # $_field, '>=', '000.000.000.000', %rest,
1681 # SUBKEY => $rest{'SUBKEY'}. '.Content',
1682 # ENTRYAGGREGATOR => 'AND',
1684 # $self->_CustomFieldLimit(
1685 # $_field, '<=', '255.255.255.255', %rest,
1686 # SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1687 # ENTRYAGGREGATOR => 'AND',
1690 else { # negative equation
1691 $self->_CustomFieldLimit($_field, '>', $end_ip, %rest);
1692 $self->_CustomFieldLimit(
1693 $_field, '<', $start_ip, %rest,
1694 SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1695 ENTRYAGGREGATOR => 'OR',
1697 # TODO: as well limit borders so DB optimizers can use better
1698 # estimations and scan less rows, but it's harder to do
1699 # as we have OR aggregator
1703 elsif ( !$negative_op || $single_value ) {
1704 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++ if !$single_value && !$range_op;
1705 my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field, $type );
1712 # if column is defined then deal only with it
1713 # otherwise search in Content and in LargeContent
1715 $self->_SQLLimit( $fix_op->(
1716 ALIAS => $ObjectCFs,
1728 # need special treatment for Date
1729 if ( $cf and $cf->Type eq 'DateTime' and $op eq '=' && $value !~ /:/ ) {
1730 # no time specified, that means we want everything on a
1731 # particular day. in the database, we need to check for >
1732 # and < the edges of that day.
1733 my $date = RT::Date->new( $self->CurrentUser );
1734 $date->Set( Format => 'unknown', Value => $value );
1735 my $daystart = $date->ISO;
1737 my $dayend = $date->ISO;
1742 ALIAS => $ObjectCFs,
1750 ALIAS => $ObjectCFs,
1755 ENTRYAGGREGATOR => 'AND',
1760 elsif ( $op eq '=' || $op eq '!=' || $op eq '<>' ) {
1761 if ( length( Encode::encode_utf8($value) ) < 256 ) {
1763 ALIAS => $ObjectCFs,
1774 ALIAS => $ObjectCFs,
1778 ENTRYAGGREGATOR => 'OR'
1781 ALIAS => $ObjectCFs,
1785 ENTRYAGGREGATOR => 'OR'
1788 $self->_SQLLimit( $fix_op->(
1789 ALIAS => $ObjectCFs,
1790 FIELD => 'LargeContent',
1793 ENTRYAGGREGATOR => 'AND',
1800 ALIAS => $ObjectCFs,
1811 ALIAS => $ObjectCFs,
1815 ENTRYAGGREGATOR => 'OR'
1818 ALIAS => $ObjectCFs,
1822 ENTRYAGGREGATOR => 'OR'
1825 $self->_SQLLimit( $fix_op->(
1826 ALIAS => $ObjectCFs,
1827 FIELD => 'LargeContent',
1830 ENTRYAGGREGATOR => 'AND',
1837 # XXX: if we join via CustomFields table then
1838 # because of order of left joins we get NULLs in
1839 # CF table and then get nulls for those records
1840 # in OCFVs table what result in wrong results
1841 # as decifer method now tries to load a CF then
1842 # we fall into this situation only when there
1843 # are more than one CF with the name in the DB.
1844 # the same thing applies to order by call.
1845 # TODO: reorder joins T <- OCFVs <- CFs <- OCFs if
1846 # we want treat IS NULL as (not applies or has
1851 OPERATOR => 'IS NOT',
1854 ENTRYAGGREGATOR => 'AND',
1860 ALIAS => $ObjectCFs,
1861 FIELD => $column || 'Content',
1865 ENTRYAGGREGATOR => 'OR',
1873 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++;
1874 my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field, $type );
1877 $op =~ s/!|NOT\s+//i;
1879 # if column is defined then deal only with it
1880 # otherwise search in Content and in LargeContent
1882 $self->SUPER::Limit( $fix_op->(
1883 LEFTJOIN => $ObjectCFs,
1884 ALIAS => $ObjectCFs,
1892 $self->SUPER::Limit(
1893 LEFTJOIN => $ObjectCFs,
1894 ALIAS => $ObjectCFs,
1903 ALIAS => $ObjectCFs,
1912 sub _HasAttributeLimit {
1913 my ( $self, $field, $op, $value, %rest ) = @_;
1915 my $alias = $self->Join(
1919 TABLE2 => 'Attributes',
1920 FIELD2 => 'ObjectId',
1922 $self->SUPER::Limit(
1924 FIELD => 'ObjectType',
1925 VALUE => 'RT::Ticket',
1926 ENTRYAGGREGATOR => 'AND'
1928 $self->SUPER::Limit(
1933 ENTRYAGGREGATOR => 'AND'
1939 OPERATOR => $FIELD_METADATA{$field}->[1]? 'IS NOT': 'IS',
1945 # End Helper Functions
1947 # End of SQL Stuff -------------------------------------------------
1950 =head2 OrderByCols ARRAY
1952 A modified version of the OrderBy method which automatically joins where
1953 C<ALIAS> is set to the name of a watcher type.
1964 foreach my $row (@args) {
1965 if ( $row->{ALIAS} ) {
1969 if ( $row->{FIELD} !~ /\./ ) {
1970 my $meta = $self->FIELDS->{ $row->{FIELD} };
1976 if ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'Queue' ) {
1977 my $alias = $self->Join(
1980 FIELD1 => $row->{'FIELD'},
1984 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1985 } elsif ( ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'User' )
1986 || ( $meta->[0] eq 'WATCHERFIELD' && ($meta->[1]||'') eq 'Owner' )
1988 my $alias = $self->Join(
1991 FIELD1 => $row->{'FIELD'},
1995 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
2002 my ( $field, $subkey ) = split /\./, $row->{FIELD}, 2;
2003 my $meta = $self->FIELDS->{$field};
2004 if ( defined $meta->[0] && $meta->[0] eq 'WATCHERFIELD' ) {
2005 # cache alias as we want to use one alias per watcher type for sorting
2006 my $users = $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] };
2008 $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] }
2009 = $users = ( $self->_WatcherJoin( $meta->[1] ) )[2];
2011 push @res, { %$row, ALIAS => $users, FIELD => $subkey };
2012 } elsif ( defined $meta->[0] && $meta->[0] eq 'CUSTOMFIELD' ) {
2013 my ($object, $field, $cf_obj, $column) = $self->_CustomFieldDecipher( $subkey );
2014 my $cfkey = $cf_obj ? $cf_obj->id : "$object.$field";
2015 $cfkey .= ".ordering" if !$cf_obj || ($cf_obj->MaxValues||0) != 1;
2016 my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, ($cf_obj ?$cf_obj->id :0) , $field );
2017 # this is described in _CustomFieldLimit
2021 OPERATOR => 'IS NOT',
2024 ENTRYAGGREGATOR => 'AND',
2027 # For those cases where we are doing a join against the
2028 # CF name, and don't have a CFid, use Unique to make sure
2029 # we don't show duplicate tickets. NOTE: I'm pretty sure
2030 # this will stay mixed in for the life of the
2031 # class/package, and not just for the life of the object.
2032 # Potential performance issue.
2033 require DBIx::SearchBuilder::Unique;
2034 DBIx::SearchBuilder::Unique->import;
2036 my $CFvs = $self->Join(
2038 ALIAS1 => $ObjectCFs,
2039 FIELD1 => 'CustomField',
2040 TABLE2 => 'CustomFieldValues',
2041 FIELD2 => 'CustomField',
2043 $self->SUPER::Limit(
2047 VALUE => $ObjectCFs . ".Content",
2048 ENTRYAGGREGATOR => 'AND'
2051 push @res, { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' };
2052 push @res, { %$row, ALIAS => $ObjectCFs, FIELD => 'Content' };
2053 } elsif ( $field eq "Custom" && $subkey eq "Ownership") {
2054 # PAW logic is "reversed"
2056 if (exists $row->{ORDER} ) {
2057 my $o = $row->{ORDER};
2058 delete $row->{ORDER};
2059 $order = "DESC" if $o =~ /asc/i;
2062 # Ticket.Owner 1 0 X
2063 # Unowned Tickets 0 1 X
2066 foreach my $uid ( $self->CurrentUser->Id, RT->Nobody->Id ) {
2067 if ( RT->Config->Get('DatabaseType') eq 'Oracle' ) {
2068 my $f = ($row->{'ALIAS'} || 'main') .'.Owner';
2073 FUNCTION => "CASE WHEN $f=$uid THEN 1 ELSE 0 END",
2080 FUNCTION => "Owner=$uid",
2086 push @res, { %$row, FIELD => "Priority", ORDER => $order } ;
2088 } elsif ( $field eq 'Customer' ) { #Freeside
2089 # OrderBy(FIELD => expression) doesn't work, it has to be
2090 # an actual field, so we have to do the join even if sorting
2092 my $custalias = $self->JoinToCustomer;
2093 my $cust_field = lc($subkey);
2094 if ( !$cust_field or $cust_field eq 'number' ) {
2095 $cust_field = 'custnum';
2097 elsif ( $cust_field eq 'name' ) {
2098 $cust_field = "COALESCE( $custalias.company,
2099 $custalias.last || ', ' || $custalias.first
2102 else { # order by cust_main fields directly: 'Customer.agentnum'
2103 $cust_field = $subkey;
2105 push @res, { %$row, ALIAS => $custalias, FIELD => $cust_field };
2107 } elsif ( $field eq 'Service' ) {
2109 my $svcalias = $self->JoinToService;
2110 my $svc_field = lc($subkey);
2111 if ( !$svc_field or $svc_field eq 'number' ) {
2112 $svc_field = 'svcnum';
2114 push @res, { %$row, ALIAS => $svcalias, FIELD => $svc_field };
2122 return $self->SUPER::OrderByCols(@res);
2127 sub JoinToCustLinks {
2128 # Set up join to links (id = localbase),
2129 # limit link type to 'MemberOf',
2130 # and target value to any Freeside custnum URI.
2131 # Return the linkalias for further join/limit action,
2132 # and an sql expression to retrieve the custnum.
2134 # only join once for each RT::Tickets object
2135 my $linkalias = $self->{cust_main_linkalias};
2137 $linkalias = $self->Join(
2142 FIELD2 => 'LocalBase',
2144 $self->SUPER::Limit(
2145 LEFTJOIN => $linkalias,
2148 VALUE => 'fsck.com-rt://%/ticket/%',
2150 $self->SUPER::Limit(
2151 LEFTJOIN => $linkalias,
2154 VALUE => 'MemberOf',
2156 $self->SUPER::Limit(
2157 LEFTJOIN => $linkalias,
2159 OPERATOR => 'STARTSWITH',
2160 VALUE => 'freeside://freeside/cust_main/',
2162 $self->{cust_main_linkalias} = $linkalias;
2164 my $custnum_sql = "CAST(SUBSTR($linkalias.Target,31) AS ";
2165 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
2166 $custnum_sql .= 'SIGNED INTEGER)';
2169 $custnum_sql .= 'INTEGER)';
2171 return ($linkalias, $custnum_sql);
2174 sub JoinToCustomer {
2176 my ($linkalias, $custnum_sql) = $self->JoinToCustLinks;
2177 # don't reuse this join, though--negative queries need
2179 my $custalias = $self->Join(
2181 EXPRESSION => $custnum_sql,
2182 TABLE2 => 'cust_main',
2183 FIELD2 => 'custnum',
2188 sub JoinToSvcLinks {
2190 my $linkalias = $self->{cust_svc_linkalias};
2192 $linkalias = $self->Join(
2197 FIELD2 => 'LocalBase',
2199 $self->SUPER::Limit(
2200 LEFTJOIN => $linkalias,
2203 VALUE => 'fsck.com-rt://%/ticket/%',
2206 $self->SUPER::Limit(
2207 LEFTJOIN => $linkalias,
2210 VALUE => 'MemberOf',
2212 $self->SUPER::Limit(
2213 LEFTJOIN => $linkalias,
2215 OPERATOR => 'STARTSWITH',
2216 VALUE => 'freeside://freeside/cust_svc/',
2218 $self->{cust_svc_linkalias} = $linkalias;
2220 my $svcnum_sql = "CAST(SUBSTR($linkalias.Target,30) AS ";
2221 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
2222 $svcnum_sql .= 'SIGNED INTEGER)';
2225 $svcnum_sql .= 'INTEGER)';
2227 return ($linkalias, $svcnum_sql);
2232 my ($linkalias, $svcnum_sql) = $self->JoinToSvcLinks;
2235 EXPRESSION => $svcnum_sql,
2236 TABLE2 => 'cust_svc',
2241 # This creates an alternate left join path to cust_main via cust_svc.
2242 # _FreesideFieldLimit needs to add this as a separate, independent join
2243 # and include all tickets that have a matching cust_main record via
2245 sub JoinToCustomerViaService {
2247 my $svcalias = $self->JoinToService;
2248 my $cust_pkg = $self->Join(
2250 ALIAS1 => $svcalias,
2252 TABLE2 => 'cust_pkg',
2255 my $cust_main = $self->Join(
2257 ALIAS1 => $cust_pkg,
2258 FIELD1 => 'custnum',
2259 TABLE2 => 'cust_main',
2260 FIELD2 => 'custnum',
2265 sub _FreesideFieldLimit {
2266 my ( $self, $field, $op, $value, %rest ) = @_;
2267 my $is_negative = 0;
2268 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
2269 # if the op is negative, do the join as though
2270 # the op were positive, then accept only records
2271 # where the right-side join key is null.
2273 $op = '=' if $op eq '!=';
2277 my (@alias, $table2, $subfield, $pkey);
2278 if ( $field eq 'Customer' ) {
2279 push @alias, $self->JoinToCustomer;
2280 push @alias, $self->JoinToCustomerViaService;
2283 elsif ( $field eq 'Service' ) {
2284 push @alias, $self->JoinToService;
2288 die "malformed Freeside query: $field";
2291 $subfield = $rest{SUBKEY} || $pkey;
2292 # compound subkey: separate into table name and field in that table
2293 # (must be linked by custnum)
2294 $subfield = lc($subfield);
2295 ($table2, $subfield) = ($1, $2) if $subfield =~ /^(\w+)?\.(\w+)$/;
2296 $subfield = $pkey if $subfield eq 'number';
2298 # if it's compound, create a join from cust_main or cust_svc to that
2299 # table, using custnum or svcnum, and Limit on that table instead.
2301 foreach my $a (@alias) {
2312 # do the actual Limit
2313 $self->SUPER::Limit(
2318 ENTRYAGGREGATOR => 'AND',
2319 # no SUBCLAUSE needed, limits on different aliases across left joins
2320 # are inherently independent
2323 # then, since it's a left join, exclude tickets for which there is now
2324 # no matching record in the table we just limited on. (Or where there
2325 # is a matching record, if $is_negative.)
2326 # For a cust_main query (where there are two different aliases), this
2327 # will produce a subclause: "cust_main_1.custnum IS NOT NULL OR
2328 # cust_main_2.custnum IS NOT NULL" (or "IS NULL AND..." for a negative
2335 OPERATOR => $is_negative ? 'IS' : 'IS NOT',
2338 ENTRYAGGREGATOR => $is_negative ? 'AND' : 'OR',
2339 SUBCLAUSE => 'fs_limit',
2344 foreach my $_SQLLimit (@_SQLLimit) {
2345 $self->_SQLLimit( %$_SQLLimit);
2355 Takes a paramhash with the fields FIELD, OPERATOR, VALUE and DESCRIPTION
2356 Generally best called from LimitFoo methods
2366 DESCRIPTION => undef,
2369 $args{'DESCRIPTION'} = $self->loc(
2370 "[_1] [_2] [_3]", $args{'FIELD'},
2371 $args{'OPERATOR'}, $args{'VALUE'}
2373 if ( !defined $args{'DESCRIPTION'} );
2375 my $index = $self->_NextIndex;
2377 # make the TicketRestrictions hash the equivalent of whatever we just passed in;
2379 %{ $self->{'TicketRestrictions'}{$index} } = %args;
2381 $self->{'RecalcTicketLimits'} = 1;
2383 # If we're looking at the effective id, we don't want to append the other clause
2384 # which limits us to tickets where id = effective id
2385 if ( $args{'FIELD'} eq 'EffectiveId'
2386 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
2388 $self->{'looking_at_effective_id'} = 1;
2391 if ( $args{'FIELD'} eq 'Type'
2392 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
2394 $self->{'looking_at_type'} = 1;
2405 LimitQueue takes a paramhash with the fields OPERATOR and VALUE.
2406 OPERATOR is one of = or !=. (It defaults to =).
2407 VALUE is a queue id or Name.
2420 #TODO VALUE should also take queue objects
2421 if ( defined $args{'VALUE'} && $args{'VALUE'} !~ /^\d+$/ ) {
2422 my $queue = RT::Queue->new( $self->CurrentUser );
2423 $queue->Load( $args{'VALUE'} );
2424 $args{'VALUE'} = $queue->Id;
2427 # What if they pass in an Id? Check for isNum() and convert to
2430 #TODO check for a valid queue here
2434 VALUE => $args{'VALUE'},
2435 OPERATOR => $args{'OPERATOR'},
2436 DESCRIPTION => join(
2437 ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{'VALUE'},
2447 Takes a paramhash with the fields OPERATOR and VALUE.
2448 OPERATOR is one of = or !=.
2451 RT adds Status != 'deleted' until object has
2452 allow_deleted_search internal property set.
2453 $tickets->{'allow_deleted_search'} = 1;
2454 $tickets->LimitStatus( VALUE => 'deleted' );
2466 VALUE => $args{'VALUE'},
2467 OPERATOR => $args{'OPERATOR'},
2468 DESCRIPTION => join( ' ',
2469 $self->loc('Status'), $args{'OPERATOR'},
2470 $self->loc( $args{'VALUE'} ) ),
2478 If called, this search will not automatically limit the set of results found
2479 to tickets of type "Ticket". Tickets of other types, such as "project" and
2480 "approval" will be found.
2487 # Instead of faking a Limit that later gets ignored, fake up the
2488 # fact that we're already looking at type, so that the check in
2489 # Tickets_SQL/FromSQL goes down the right branch
2491 # $self->LimitType(VALUE => '__any');
2492 $self->{looking_at_type} = 1;
2499 Takes a paramhash with the fields OPERATOR and VALUE.
2500 OPERATOR is one of = or !=, it defaults to "=".
2501 VALUE is a string to search for in the type of the ticket.
2516 VALUE => $args{'VALUE'},
2517 OPERATOR => $args{'OPERATOR'},
2518 DESCRIPTION => join( ' ',
2519 $self->loc('Type'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2529 Takes a paramhash with the fields OPERATOR and VALUE.
2530 OPERATOR is one of = or !=.
2531 VALUE is a string to search for in the subject of the ticket.
2540 VALUE => $args{'VALUE'},
2541 OPERATOR => $args{'OPERATOR'},
2542 DESCRIPTION => join( ' ',
2543 $self->loc('Subject'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2549 # Things that can be > < = !=
2554 Takes a paramhash with the fields OPERATOR and VALUE.
2555 OPERATOR is one of =, >, < or !=.
2556 VALUE is a ticket Id to search for
2569 VALUE => $args{'VALUE'},
2570 OPERATOR => $args{'OPERATOR'},
2572 join( ' ', $self->loc('Id'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2578 =head2 LimitPriority
2580 Takes a paramhash with the fields OPERATOR and VALUE.
2581 OPERATOR is one of =, >, < or !=.
2582 VALUE is a value to match the ticket's priority against
2590 FIELD => 'Priority',
2591 VALUE => $args{'VALUE'},
2592 OPERATOR => $args{'OPERATOR'},
2593 DESCRIPTION => join( ' ',
2594 $self->loc('Priority'),
2595 $args{'OPERATOR'}, $args{'VALUE'}, ),
2601 =head2 LimitInitialPriority
2603 Takes a paramhash with the fields OPERATOR and VALUE.
2604 OPERATOR is one of =, >, < or !=.
2605 VALUE is a value to match the ticket's initial priority against
2610 sub LimitInitialPriority {
2614 FIELD => 'InitialPriority',
2615 VALUE => $args{'VALUE'},
2616 OPERATOR => $args{'OPERATOR'},
2617 DESCRIPTION => join( ' ',
2618 $self->loc('Initial Priority'), $args{'OPERATOR'},
2625 =head2 LimitFinalPriority
2627 Takes a paramhash with the fields OPERATOR and VALUE.
2628 OPERATOR is one of =, >, < or !=.
2629 VALUE is a value to match the ticket's final priority against
2633 sub LimitFinalPriority {
2637 FIELD => 'FinalPriority',
2638 VALUE => $args{'VALUE'},
2639 OPERATOR => $args{'OPERATOR'},
2640 DESCRIPTION => join( ' ',
2641 $self->loc('Final Priority'), $args{'OPERATOR'},
2648 =head2 LimitTimeWorked
2650 Takes a paramhash with the fields OPERATOR and VALUE.
2651 OPERATOR is one of =, >, < or !=.
2652 VALUE is a value to match the ticket's TimeWorked attribute
2656 sub LimitTimeWorked {
2660 FIELD => 'TimeWorked',
2661 VALUE => $args{'VALUE'},
2662 OPERATOR => $args{'OPERATOR'},
2663 DESCRIPTION => join( ' ',
2664 $self->loc('Time Worked'),
2665 $args{'OPERATOR'}, $args{'VALUE'}, ),
2671 =head2 LimitTimeLeft
2673 Takes a paramhash with the fields OPERATOR and VALUE.
2674 OPERATOR is one of =, >, < or !=.
2675 VALUE is a value to match the ticket's TimeLeft attribute
2683 FIELD => 'TimeLeft',
2684 VALUE => $args{'VALUE'},
2685 OPERATOR => $args{'OPERATOR'},
2686 DESCRIPTION => join( ' ',
2687 $self->loc('Time Left'),
2688 $args{'OPERATOR'}, $args{'VALUE'}, ),
2698 Takes a paramhash with the fields OPERATOR and VALUE.
2699 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2700 VALUE is a string to search for in the body of the ticket
2709 VALUE => $args{'VALUE'},
2710 OPERATOR => $args{'OPERATOR'},
2711 DESCRIPTION => join( ' ',
2712 $self->loc('Ticket content'), $args{'OPERATOR'},
2719 =head2 LimitFilename
2721 Takes a paramhash with the fields OPERATOR and VALUE.
2722 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2723 VALUE is a string to search for in the body of the ticket
2731 FIELD => 'Filename',
2732 VALUE => $args{'VALUE'},
2733 OPERATOR => $args{'OPERATOR'},
2734 DESCRIPTION => join( ' ',
2735 $self->loc('Attachment filename'), $args{'OPERATOR'},
2741 =head2 LimitContentType
2743 Takes a paramhash with the fields OPERATOR and VALUE.
2744 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2745 VALUE is a content type to search ticket attachments for
2749 sub LimitContentType {
2753 FIELD => 'ContentType',
2754 VALUE => $args{'VALUE'},
2755 OPERATOR => $args{'OPERATOR'},
2756 DESCRIPTION => join( ' ',
2757 $self->loc('Ticket content type'), $args{'OPERATOR'},
2768 Takes a paramhash with the fields OPERATOR and VALUE.
2769 OPERATOR is one of = or !=.
2781 my $owner = RT::User->new( $self->CurrentUser );
2782 $owner->Load( $args{'VALUE'} );
2784 # FIXME: check for a valid $owner
2787 VALUE => $args{'VALUE'},
2788 OPERATOR => $args{'OPERATOR'},
2789 DESCRIPTION => join( ' ',
2790 $self->loc('Owner'), $args{'OPERATOR'}, $owner->Name(), ),
2800 Takes a paramhash with the fields OPERATOR, TYPE and VALUE.
2801 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2802 VALUE is a value to match the ticket's watcher email addresses against
2803 TYPE is the sort of watchers you want to match against. Leave it undef if you want to search all of them
2817 #build us up a description
2818 my ( $watcher_type, $desc );
2819 if ( $args{'TYPE'} ) {
2820 $watcher_type = $args{'TYPE'};
2823 $watcher_type = "Watcher";
2827 FIELD => $watcher_type,
2828 VALUE => $args{'VALUE'},
2829 OPERATOR => $args{'OPERATOR'},
2830 TYPE => $args{'TYPE'},
2831 DESCRIPTION => join( ' ',
2832 $self->loc($watcher_type),
2833 $args{'OPERATOR'}, $args{'VALUE'}, ),
2842 =head2 LimitLinkedTo
2844 LimitLinkedTo takes a paramhash with two fields: TYPE and TARGET
2845 TYPE limits the sort of link we want to search on
2847 TYPE = { RefersTo, MemberOf, DependsOn }
2849 TARGET is the id or URI of the TARGET of the link
2863 FIELD => 'LinkedTo',
2865 TARGET => $args{'TARGET'},
2866 TYPE => $args{'TYPE'},
2867 DESCRIPTION => $self->loc(
2868 "Tickets [_1] by [_2]",
2869 $self->loc( $args{'TYPE'} ),
2872 OPERATOR => $args{'OPERATOR'},
2878 =head2 LimitLinkedFrom
2880 LimitLinkedFrom takes a paramhash with two fields: TYPE and BASE
2881 TYPE limits the sort of link we want to search on
2884 BASE is the id or URI of the BASE of the link
2888 sub LimitLinkedFrom {
2897 # translate RT2 From/To naming to RT3 TicketSQL naming
2898 my %fromToMap = qw(DependsOn DependentOn
2900 RefersTo ReferredToBy);
2902 my $type = $args{'TYPE'};
2903 $type = $fromToMap{$type} if exists( $fromToMap{$type} );
2906 FIELD => 'LinkedTo',
2908 BASE => $args{'BASE'},
2910 DESCRIPTION => $self->loc(
2911 "Tickets [_1] [_2]",
2912 $self->loc( $args{'TYPE'} ),
2915 OPERATOR => $args{'OPERATOR'},
2922 my $ticket_id = shift;
2923 return $self->LimitLinkedTo(
2925 TARGET => $ticket_id,
2931 sub LimitHasMember {
2933 my $ticket_id = shift;
2934 return $self->LimitLinkedFrom(
2936 BASE => "$ticket_id",
2937 TYPE => 'HasMember',
2944 sub LimitDependsOn {
2946 my $ticket_id = shift;
2947 return $self->LimitLinkedTo(
2949 TARGET => $ticket_id,
2950 TYPE => 'DependsOn',
2957 sub LimitDependedOnBy {
2959 my $ticket_id = shift;
2960 return $self->LimitLinkedFrom(
2963 TYPE => 'DependentOn',
2972 my $ticket_id = shift;
2973 return $self->LimitLinkedTo(
2975 TARGET => $ticket_id,
2983 sub LimitReferredToBy {
2985 my $ticket_id = shift;
2986 return $self->LimitLinkedFrom(
2989 TYPE => 'ReferredToBy',
2997 =head2 LimitDate (FIELD => 'DateField', OPERATOR => $oper, VALUE => $ISODate)
2999 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
3001 OPERATOR is one of > or <
3002 VALUE is a date and time in ISO format in GMT
3003 FIELD is one of Starts, Started, Told, Created, Resolved, LastUpdated
3005 There are also helper functions of the form LimitFIELD that eliminate
3006 the need to pass in a FIELD argument.
3020 #Set the description if we didn't get handed it above
3021 unless ( $args{'DESCRIPTION'} ) {
3022 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
3023 . $args{'OPERATOR'} . " "
3024 . $args{'VALUE'} . " GMT";
3027 $self->Limit(%args);
3034 $self->LimitDate( FIELD => 'Created', @_ );
3039 $self->LimitDate( FIELD => 'Due', @_ );
3045 $self->LimitDate( FIELD => 'Starts', @_ );
3051 $self->LimitDate( FIELD => 'Started', @_ );
3056 $self->LimitDate( FIELD => 'Resolved', @_ );
3061 $self->LimitDate( FIELD => 'Told', @_ );
3064 sub LimitLastUpdated {
3066 $self->LimitDate( FIELD => 'LastUpdated', @_ );
3071 =head2 LimitTransactionDate (OPERATOR => $oper, VALUE => $ISODate)
3073 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
3075 OPERATOR is one of > or <
3076 VALUE is a date and time in ISO format in GMT
3081 sub LimitTransactionDate {
3084 FIELD => 'TransactionDate',
3091 # <20021217042756.GK28744@pallas.fsck.com>
3092 # "Kill It" - Jesse.
3094 #Set the description if we didn't get handed it above
3095 unless ( $args{'DESCRIPTION'} ) {
3096 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
3097 . $args{'OPERATOR'} . " "
3098 . $args{'VALUE'} . " GMT";
3101 $self->Limit(%args);
3108 =head2 LimitCustomField
3110 Takes a paramhash of key/value pairs with the following keys:
3114 =item CUSTOMFIELD - CustomField name or id. If a name is passed, an additional parameter QUEUE may also be passed to distinguish the custom field.
3116 =item OPERATOR - The usual Limit operators
3118 =item VALUE - The value to compare against
3124 sub LimitCustomField {
3128 CUSTOMFIELD => undef,
3130 DESCRIPTION => undef,
3131 FIELD => 'CustomFieldValue',
3136 my $CF = RT::CustomField->new( $self->CurrentUser );
3137 if ( $args{CUSTOMFIELD} =~ /^\d+$/ ) {
3138 $CF->Load( $args{CUSTOMFIELD} );
3141 $CF->LoadByNameAndQueue(
3142 Name => $args{CUSTOMFIELD},
3143 Queue => $args{QUEUE}
3145 $args{CUSTOMFIELD} = $CF->Id;
3148 #If we are looking to compare with a null value.
3149 if ( $args{'OPERATOR'} =~ /^is$/i ) {
3150 $args{'DESCRIPTION'}
3151 ||= $self->loc( "Custom field [_1] has no value.", $CF->Name );
3153 elsif ( $args{'OPERATOR'} =~ /^is not$/i ) {
3154 $args{'DESCRIPTION'}
3155 ||= $self->loc( "Custom field [_1] has a value.", $CF->Name );
3158 # if we're not looking to compare with a null value
3160 $args{'DESCRIPTION'} ||= $self->loc( "Custom field [_1] [_2] [_3]",
3161 $CF->Name, $args{OPERATOR}, $args{VALUE} );
3164 if ( defined $args{'QUEUE'} && $args{'QUEUE'} =~ /\D/ ) {
3165 my $QueueObj = RT::Queue->new( $self->CurrentUser );
3166 $QueueObj->Load( $args{'QUEUE'} );
3167 $args{'QUEUE'} = $QueueObj->Id;
3169 delete $args{'QUEUE'} unless defined $args{'QUEUE'} && length $args{'QUEUE'};
3172 @rest = ( ENTRYAGGREGATOR => 'AND' )
3173 if ( $CF->Type eq 'SelectMultiple' );
3176 VALUE => $args{VALUE},
3178 .(defined $args{'QUEUE'}? ".$args{'QUEUE'}" : '' )
3179 .".{" . $CF->Name . "}",
3180 OPERATOR => $args{OPERATOR},
3185 $self->{'RecalcTicketLimits'} = 1;
3192 Keep track of the counter for the array of restrictions
3198 return ( $self->{'restriction_index'}++ );
3206 $self->{'table'} = "Tickets";
3207 $self->{'RecalcTicketLimits'} = 1;
3208 $self->{'looking_at_effective_id'} = 0;
3209 $self->{'looking_at_type'} = 0;
3210 $self->{'restriction_index'} = 1;
3211 $self->{'primary_key'} = "id";
3212 delete $self->{'items_array'};
3213 delete $self->{'item_map'};
3214 delete $self->{'columns_to_display'};
3215 $self->SUPER::_Init(@_);
3224 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3225 return ( $self->SUPER::Count() );
3231 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3232 return ( $self->SUPER::CountAll() );
3237 =head2 ItemsArrayRef
3239 Returns a reference to the set of all items found in this search
3246 return $self->{'items_array'} if $self->{'items_array'};
3248 my $placeholder = $self->_ItemsCounter;
3249 $self->GotoFirstItem();
3250 while ( my $item = $self->Next ) {
3251 push( @{ $self->{'items_array'} }, $item );
3253 $self->GotoItem($placeholder);
3254 $self->{'items_array'}
3255 = $self->ItemsOrderBy( $self->{'items_array'} );
3257 return $self->{'items_array'};
3260 sub ItemsArrayRefWindow {
3264 my @old = ($self->_ItemsCounter, $self->RowsPerPage, $self->FirstRow+1);
3266 $self->RowsPerPage( $window );
3268 $self->GotoFirstItem;
3271 while ( my $item = $self->Next ) {
3275 $self->RowsPerPage( $old[1] );
3276 $self->FirstRow( $old[2] );
3277 $self->GotoItem( $old[0] );
3286 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3288 my $Ticket = $self->SUPER::Next;
3289 return $Ticket unless $Ticket;
3291 if ( $Ticket->__Value('Status') eq 'deleted'
3292 && !$self->{'allow_deleted_search'} )
3296 elsif ( RT->Config->Get('UseSQLForACLChecks') ) {
3297 # if we found a ticket with this option enabled then
3298 # all tickets we found are ACLed, cache this fact
3299 my $key = join ";:;", $self->CurrentUser->id, 'ShowTicket', 'RT::Ticket-'. $Ticket->id;
3300 $RT::Principal::_ACL_CACHE->set( $key => 1 );
3303 elsif ( $Ticket->CurrentUserHasRight('ShowTicket') ) {
3308 # If the user doesn't have the right to show this ticket
3315 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
3316 return $self->SUPER::_DoSearch( @_ );
3321 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
3322 return $self->SUPER::_DoCount( @_ );
3328 my $cache_key = 'RolesHasRight;:;ShowTicket';
3330 if ( my $cached = $RT::Principal::_ACL_CACHE->fetch( $cache_key ) ) {
3334 my $ACL = RT::ACL->new( RT->SystemUser );
3335 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
3336 $ACL->Limit( FIELD => 'PrincipalType', OPERATOR => '!=', VALUE => 'Group' );
3337 my $principal_alias = $ACL->Join(
3339 FIELD1 => 'PrincipalId',
3340 TABLE2 => 'Principals',
3343 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3346 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
3347 my $role = $ACE->__Value('PrincipalType');
3348 my $type = $ACE->__Value('ObjectType');
3349 if ( $type eq 'RT::System' ) {
3352 elsif ( $type eq 'RT::Queue' ) {
3353 next if $res{ $role } && !ref $res{ $role };
3354 push @{ $res{ $role } ||= [] }, $ACE->__Value('ObjectId');
3357 $RT::Logger->error('ShowTicket right is granted on unsupported object');
3360 $RT::Principal::_ACL_CACHE->set( $cache_key => \%res );
3364 sub _DirectlyCanSeeIn {
3366 my $id = $self->CurrentUser->id;
3368 my $cache_key = 'User-'. $id .';:;ShowTicket;:;DirectlyCanSeeIn';
3369 if ( my $cached = $RT::Principal::_ACL_CACHE->fetch( $cache_key ) ) {
3373 my $ACL = RT::ACL->new( RT->SystemUser );
3374 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
3375 my $principal_alias = $ACL->Join(
3377 FIELD1 => 'PrincipalId',
3378 TABLE2 => 'Principals',
3381 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3382 my $cgm_alias = $ACL->Join(
3384 FIELD1 => 'PrincipalId',
3385 TABLE2 => 'CachedGroupMembers',
3386 FIELD2 => 'GroupId',
3388 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
3389 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
3392 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
3393 my $type = $ACE->__Value('ObjectType');
3394 if ( $type eq 'RT::System' ) {
3395 # If user is direct member of a group that has the right
3396 # on the system then he can see any ticket
3397 $RT::Principal::_ACL_CACHE->set( $cache_key => [-1] );
3400 elsif ( $type eq 'RT::Queue' ) {
3401 push @res, $ACE->__Value('ObjectId');
3404 $RT::Logger->error('ShowTicket right is granted on unsupported object');
3407 $RT::Principal::_ACL_CACHE->set( $cache_key => \@res );
3411 sub CurrentUserCanSee {
3413 return if $self->{'_sql_current_user_can_see_applied'};
3415 return $self->{'_sql_current_user_can_see_applied'} = 1
3416 if $self->CurrentUser->UserObj->HasRight(
3417 Right => 'SuperUser', Object => $RT::System
3420 my $id = $self->CurrentUser->id;
3422 # directly can see in all queues then we have nothing to do
3423 my @direct_queues = $self->_DirectlyCanSeeIn;
3424 return $self->{'_sql_current_user_can_see_applied'} = 1
3425 if @direct_queues && $direct_queues[0] == -1;
3427 my %roles = $self->_RolesCanSee;
3429 my %skip = map { $_ => 1 } @direct_queues;
3430 foreach my $role ( keys %roles ) {
3431 next unless ref $roles{ $role };
3433 my @queues = grep !$skip{$_}, @{ $roles{ $role } };
3435 $roles{ $role } = \@queues;
3437 delete $roles{ $role };
3442 # there is no global watchers, only queues and tickes, if at
3443 # some point we will add global roles then it's gonna blow
3444 # the idea here is that if the right is set globaly for a role
3445 # and user plays this role for a queue directly not a ticket
3446 # then we have to check in advance
3447 if ( my @tmp = grep $_ ne 'Owner' && !ref $roles{ $_ }, keys %roles ) {
3449 my $groups = RT::Groups->new( RT->SystemUser );
3450 $groups->Limit( FIELD => 'Domain', VALUE => 'RT::Queue-Role' );
3452 $groups->Limit( FIELD => 'Type', VALUE => $_ );
3454 my $principal_alias = $groups->Join(
3457 TABLE2 => 'Principals',
3460 $groups->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3461 my $cgm_alias = $groups->Join(
3464 TABLE2 => 'CachedGroupMembers',
3465 FIELD2 => 'GroupId',
3467 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
3468 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
3469 while ( my $group = $groups->Next ) {
3470 push @direct_queues, $group->Instance;
3474 unless ( @direct_queues || keys %roles ) {
3475 $self->SUPER::Limit(
3480 ENTRYAGGREGATOR => 'AND',
3482 return $self->{'_sql_current_user_can_see_applied'} = 1;
3486 my $join_roles = keys %roles;
3487 $join_roles = 0 if $join_roles == 1 && $roles{'Owner'};
3488 my ($role_group_alias, $cgm_alias);
3489 if ( $join_roles ) {
3490 $role_group_alias = $self->_RoleGroupsJoin( New => 1 );
3491 $cgm_alias = $self->_GroupMembersJoin( GroupsAlias => $role_group_alias );
3492 $self->SUPER::Limit(
3493 LEFTJOIN => $cgm_alias,
3494 FIELD => 'MemberId',
3499 my $limit_queues = sub {
3503 return unless @queues;
3504 if ( @queues == 1 ) {
3505 $self->SUPER::Limit(
3510 ENTRYAGGREGATOR => $ea,
3513 $self->SUPER::_OpenParen('ACL');
3514 foreach my $q ( @queues ) {
3515 $self->SUPER::Limit(
3520 ENTRYAGGREGATOR => $ea,
3524 $self->SUPER::_CloseParen('ACL');
3529 $self->SUPER::_OpenParen('ACL');
3531 $ea = 'OR' if $limit_queues->( $ea, @direct_queues );
3532 while ( my ($role, $queues) = each %roles ) {
3533 $self->SUPER::_OpenParen('ACL');
3534 if ( $role eq 'Owner' ) {
3535 $self->SUPER::Limit(
3539 ENTRYAGGREGATOR => $ea,
3543 $self->SUPER::Limit(
3545 ALIAS => $cgm_alias,
3546 FIELD => 'MemberId',
3547 OPERATOR => 'IS NOT',
3550 ENTRYAGGREGATOR => $ea,
3552 $self->SUPER::Limit(
3554 ALIAS => $role_group_alias,
3557 ENTRYAGGREGATOR => 'AND',
3560 $limit_queues->( 'AND', @$queues ) if ref $queues;
3561 $ea = 'OR' if $ea eq 'AND';
3562 $self->SUPER::_CloseParen('ACL');
3564 $self->SUPER::_CloseParen('ACL');
3566 return $self->{'_sql_current_user_can_see_applied'} = 1;
3573 =head2 LoadRestrictions
3575 LoadRestrictions takes a string which can fully populate the TicketRestrictons hash.
3576 TODO It is not yet implemented
3582 =head2 DescribeRestrictions
3585 Returns a hash keyed by restriction id.
3586 Each element of the hash is currently a one element hash that contains DESCRIPTION which
3587 is a description of the purpose of that TicketRestriction
3591 sub DescribeRestrictions {
3596 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
3597 $listing{$row} = $self->{'TicketRestrictions'}{$row}{'DESCRIPTION'};
3604 =head2 RestrictionValues FIELD
3606 Takes a restriction field and returns a list of values this field is restricted
3611 sub RestrictionValues {
3614 map $self->{'TicketRestrictions'}{$_}{'VALUE'}, grep {
3615 $self->{'TicketRestrictions'}{$_}{'FIELD'} eq $field
3616 && $self->{'TicketRestrictions'}{$_}{'OPERATOR'} eq "="
3618 keys %{ $self->{'TicketRestrictions'} };
3623 =head2 ClearRestrictions
3625 Removes all restrictions irretrievably
3629 sub ClearRestrictions {
3631 delete $self->{'TicketRestrictions'};
3632 $self->{'looking_at_effective_id'} = 0;
3633 $self->{'looking_at_type'} = 0;
3634 $self->{'RecalcTicketLimits'} = 1;
3639 =head2 DeleteRestriction
3641 Takes the row Id of a restriction (From DescribeRestrictions' output, for example.
3642 Removes that restriction from the session's limits.
3646 sub DeleteRestriction {
3649 delete $self->{'TicketRestrictions'}{$row};
3651 $self->{'RecalcTicketLimits'} = 1;
3653 #make the underlying easysearch object forget all its preconceptions
3658 # Convert a set of oldstyle SB Restrictions to Clauses for RQL
3660 sub _RestrictionsToClauses {
3664 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
3665 my $restriction = $self->{'TicketRestrictions'}{$row};
3667 # We need to reimplement the subclause aggregation that SearchBuilder does.
3668 # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main',
3669 # Then SB AND's the different Subclauses together.
3671 # So, we want to group things into Subclauses, convert them to
3672 # SQL, and then join them with the appropriate DefaultEA.
3673 # Then join each subclause group with AND.
3675 my $field = $restriction->{'FIELD'};
3676 my $realfield = $field; # CustomFields fake up a fieldname, so
3677 # we need to figure that out
3680 # Rewrite LinkedTo meta field to the real field
3681 if ( $field =~ /LinkedTo/ ) {
3682 $realfield = $field = $restriction->{'TYPE'};
3686 # Handle subkey fields with a different real field
3687 if ( $field =~ /^(\w+)\./ ) {
3691 die "I don't know about $field yet"
3692 unless ( exists $FIELD_METADATA{$realfield}
3693 or $restriction->{CUSTOMFIELD} );
3695 my $type = $FIELD_METADATA{$realfield}->[0];
3696 my $op = $restriction->{'OPERATOR'};
3700 map { $restriction->{$_} } qw(VALUE TICKET BASE TARGET)
3703 # this performs the moral equivalent of defined or/dor/C<//>,
3704 # without the short circuiting.You need to use a 'defined or'
3705 # type thing instead of just checking for truth values, because
3706 # VALUE could be 0.(i.e. "false")
3708 # You could also use this, but I find it less aesthetic:
3709 # (although it does short circuit)
3710 #( defined $restriction->{'VALUE'}? $restriction->{VALUE} :
3711 # defined $restriction->{'TICKET'} ?
3712 # $restriction->{TICKET} :
3713 # defined $restriction->{'BASE'} ?
3714 # $restriction->{BASE} :
3715 # defined $restriction->{'TARGET'} ?
3716 # $restriction->{TARGET} )
3718 my $ea = $restriction->{ENTRYAGGREGATOR}
3719 || $DefaultEA{$type}
3722 die "Invalid operator $op for $field ($type)"
3723 unless exists $ea->{$op};
3727 # Each CustomField should be put into a different Clause so they
3728 # are ANDed together.
3729 if ( $restriction->{CUSTOMFIELD} ) {
3730 $realfield = $field;
3733 exists $clause{$realfield} or $clause{$realfield} = [];
3736 $field =~ s!(['\\])!\\$1!g;
3737 $value =~ s!(['\\])!\\$1!g;
3738 my $data = [ $ea, $type, $field, $op, $value ];
3740 # here is where we store extra data, say if it's a keyword or
3741 # something. (I.e. "TYPE SPECIFIC STUFF")
3743 if (lc $ea eq 'none') {
3744 $clause{$realfield} = [ $data ];
3746 push @{ $clause{$realfield} }, $data;
3754 =head2 _ProcessRestrictions PARAMHASH
3756 # The new _ProcessRestrictions is somewhat dependent on the SQL stuff,
3757 # but isn't quite generic enough to move into Tickets_SQL.
3761 sub _ProcessRestrictions {
3764 #Blow away ticket aliases since we'll need to regenerate them for
3766 delete $self->{'TicketAliases'};
3767 delete $self->{'items_array'};
3768 delete $self->{'item_map'};
3769 delete $self->{'raw_rows'};
3770 delete $self->{'rows'};
3771 delete $self->{'count_all'};
3773 my $sql = $self->Query; # Violating the _SQL namespace
3774 if ( !$sql || $self->{'RecalcTicketLimits'} ) {
3776 # "Restrictions to Clauses Branch\n";
3777 my $clauseRef = eval { $self->_RestrictionsToClauses; };
3779 $RT::Logger->error( "RestrictionsToClauses: " . $@ );
3783 $sql = $self->ClausesToSQL($clauseRef);
3784 $self->FromSQL($sql) if $sql;
3788 $self->{'RecalcTicketLimits'} = 0;
3792 =head2 _BuildItemMap
3794 Build up a L</ItemMap> of first/last/next/prev items, so that we can
3795 display search nav quickly.
3802 my $window = RT->Config->Get('TicketsItemMapSize');
3804 $self->{'item_map'} = {};
3806 my $items = $self->ItemsArrayRefWindow( $window );
3807 return unless $items && @$items;
3810 $self->{'item_map'}{'first'} = $items->[0]->EffectiveId;
3811 for ( my $i = 0; $i < @$items; $i++ ) {
3812 my $item = $items->[$i];
3813 my $id = $item->EffectiveId;
3814 $self->{'item_map'}{$id}{'defined'} = 1;
3815 $self->{'item_map'}{$id}{'prev'} = $prev;
3816 $self->{'item_map'}{$id}{'next'} = $items->[$i+1]->EffectiveId
3820 $self->{'item_map'}{'last'} = $prev
3821 if !$window || @$items < $window;
3826 Returns an a map of all items found by this search. The map is a hash
3830 first => <first ticket id found>,
3831 last => <last ticket id found or undef>,
3834 prev => <the ticket id found before>,
3835 next => <the ticket id found after>,
3847 $self->_BuildItemMap unless $self->{'item_map'};
3848 return $self->{'item_map'};
3854 =head2 PrepForSerialization
3856 You don't want to serialize a big tickets object, as
3857 the {items} hash will be instantly invalid _and_ eat
3862 sub PrepForSerialization {
3864 delete $self->{'items'};
3865 delete $self->{'items_array'};
3866 $self->RedoSearch();
3871 RT::Tickets supports several flags which alter search behavior:
3874 allow_deleted_search (Otherwise never show deleted tickets in search results)
3875 looking_at_type (otherwise limit to type=ticket)
3877 These flags are set by calling
3879 $tickets->{'flagname'} = 1;
3881 BUG: There should be an API for this
3891 Returns an empty new RT::Ticket item
3897 return(RT::Ticket->new($self->CurrentUser));
3899 RT::Base->_ImportOverlays();