1 # BEGIN BPS TAGGED BLOCK {{{
5 # This software is Copyright (c) 1996-2015 Best Practical Solutions, LLC
6 # <sales@bestpractical.com>
8 # (Except where explicitly superseded by other copyright notices)
13 # This work is made available to you under the terms of Version 2 of
14 # the GNU General Public License. A copy of that license should have
15 # been provided with this software, but in any event can be snarfed
18 # This work is distributed in the hope that it will be useful, but
19 # WITHOUT ANY WARRANTY; without even the implied warranty of
20 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
21 # General Public License for more details.
23 # You should have received a copy of the GNU General Public License
24 # along with this program; if not, write to the Free Software
25 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
26 # 02110-1301 or visit their web page on the internet at
27 # http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
30 # CONTRIBUTION SUBMISSION POLICY:
32 # (The following paragraph is not intended to limit the rights granted
33 # to you to modify and distribute this software under the terms of
34 # the GNU General Public License and is only of importance to you if
35 # you choose to contribute your changes and enhancements to the
36 # community by submitting them to Best Practical Solutions, LLC.)
38 # By intentionally submitting any modifications, corrections or
39 # derivatives to this work, or any other work intended for use with
40 # Request Tracker, to Best Practical Solutions, LLC, you confirm that
41 # you are the copyright holder for those contributions and you grant
42 # Best Practical Solutions, LLC a nonexclusive, worldwide, irrevocable,
43 # royalty-free, perpetual, license to use, copy, create derivative
44 # works based on those contributions, and sublicense and distribute
45 # those contributions and any derivatives thereof.
47 # END BPS TAGGED BLOCK }}}
51 # - 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'}
93 # Configuration Tables:
95 # FIELD_METADATA is a mapping of searchable Field name, to Type, and other
98 our %FIELD_METADATA = (
99 Status => [ 'ENUM', ], #loc_left_pair
100 Queue => [ 'ENUM' => 'Queue', ], #loc_left_pair
101 Type => [ 'ENUM', ], #loc_left_pair
102 Creator => [ 'ENUM' => 'User', ], #loc_left_pair
103 LastUpdatedBy => [ 'ENUM' => 'User', ], #loc_left_pair
104 Owner => [ 'WATCHERFIELD' => 'Owner', ], #loc_left_pair
105 EffectiveId => [ 'INT', ], #loc_left_pair
106 id => [ 'ID', ], #loc_left_pair
107 InitialPriority => [ 'INT', ], #loc_left_pair
108 FinalPriority => [ 'INT', ], #loc_left_pair
109 Priority => [ 'INT', ], #loc_left_pair
110 TimeLeft => [ 'INT', ], #loc_left_pair
111 TimeWorked => [ 'INT', ], #loc_left_pair
112 TimeEstimated => [ 'INT', ], #loc_left_pair
114 Linked => [ 'LINK' ], #loc_left_pair
115 LinkedTo => [ 'LINK' => 'To' ], #loc_left_pair
116 LinkedFrom => [ 'LINK' => 'From' ], #loc_left_pair
117 MemberOf => [ 'LINK' => To => 'MemberOf', ], #loc_left_pair
118 DependsOn => [ 'LINK' => To => 'DependsOn', ], #loc_left_pair
119 RefersTo => [ 'LINK' => To => 'RefersTo', ], #loc_left_pair
120 HasMember => [ 'LINK' => From => 'MemberOf', ], #loc_left_pair
121 DependentOn => [ 'LINK' => From => 'DependsOn', ], #loc_left_pair
122 DependedOnBy => [ 'LINK' => From => 'DependsOn', ], #loc_left_pair
123 ReferredToBy => [ 'LINK' => From => 'RefersTo', ], #loc_left_pair
124 Told => [ 'DATE' => 'Told', ], #loc_left_pair
125 Starts => [ 'DATE' => 'Starts', ], #loc_left_pair
126 Started => [ 'DATE' => 'Started', ], #loc_left_pair
127 Due => [ 'DATE' => 'Due', ], #loc_left_pair
128 Resolved => [ 'DATE' => 'Resolved', ], #loc_left_pair
129 LastUpdated => [ 'DATE' => 'LastUpdated', ], #loc_left_pair
130 Created => [ 'DATE' => 'Created', ], #loc_left_pair
131 Subject => [ 'STRING', ], #loc_left_pair
132 Content => [ 'TRANSCONTENT', ], #loc_left_pair
133 ContentType => [ 'TRANSFIELD', ], #loc_left_pair
134 Filename => [ 'TRANSFIELD', ], #loc_left_pair
135 TransactionDate => [ 'TRANSDATE', ], #loc_left_pair
136 Requestor => [ 'WATCHERFIELD' => 'Requestor', ], #loc_left_pair
137 Requestors => [ 'WATCHERFIELD' => 'Requestor', ], #loc_left_pair
138 Cc => [ 'WATCHERFIELD' => 'Cc', ], #loc_left_pair
139 AdminCc => [ 'WATCHERFIELD' => 'AdminCc', ], #loc_left_pair
140 Watcher => [ 'WATCHERFIELD', ], #loc_left_pair
141 QueueCc => [ 'WATCHERFIELD' => 'Cc' => 'Queue', ], #loc_left_pair
142 QueueAdminCc => [ 'WATCHERFIELD' => 'AdminCc' => 'Queue', ], #loc_left_pair
143 QueueWatcher => [ 'WATCHERFIELD' => undef => 'Queue', ], #loc_left_pair
144 CustomFieldValue => [ 'CUSTOMFIELD' => 'Ticket' ], #loc_left_pair
145 CustomField => [ 'CUSTOMFIELD' => 'Ticket' ], #loc_left_pair
146 CF => [ 'CUSTOMFIELD' => 'Ticket' ], #loc_left_pair
147 Updated => [ 'TRANSDATE', ], #loc_left_pair
148 RequestorGroup => [ 'MEMBERSHIPFIELD' => 'Requestor', ], #loc_left_pair
149 CCGroup => [ 'MEMBERSHIPFIELD' => 'Cc', ], #loc_left_pair
150 AdminCCGroup => [ 'MEMBERSHIPFIELD' => 'AdminCc', ], #loc_left_pair
151 WatcherGroup => [ 'MEMBERSHIPFIELD', ], #loc_left_pair
152 HasAttribute => [ 'HASATTRIBUTE', 1 ],
153 HasNoAttribute => [ 'HASATTRIBUTE', 0 ],
155 Customer => [ 'FREESIDEFIELD' => 'Customer' ],
156 Service => [ 'FREESIDEFIELD' => 'Service' ],
157 WillResolve => [ 'DATE' => 'WillResolve', ], #loc_left_pair
160 # Lower Case version of FIELDS, for case insensitivity
161 our %LOWER_CASE_FIELDS = map { ( lc($_) => $_ ) } (keys %FIELD_METADATA);
163 our %SEARCHABLE_SUBFIELDS = (
165 EmailAddress Name RealName Nickname Organization Address1 Address2
166 WorkPhone HomePhone MobilePhone PagerPhone id
170 # Mapping of Field Type to Function
172 ENUM => \&_EnumLimit,
175 LINK => \&_LinkLimit,
176 DATE => \&_DateLimit,
177 STRING => \&_StringLimit,
178 TRANSFIELD => \&_TransLimit,
179 TRANSCONTENT => \&_TransContentLimit,
180 TRANSDATE => \&_TransDateLimit,
181 WATCHERFIELD => \&_WatcherLimit,
182 MEMBERSHIPFIELD => \&_WatcherMembershipLimit,
183 CUSTOMFIELD => \&_CustomFieldLimit,
184 HASATTRIBUTE => \&_HasAttributeLimit,
185 FREESIDEFIELD => \&_FreesideFieldLimit,
187 our %can_bundle = ();# WATCHERFIELD => "yes", );
189 # Default EntryAggregator per type
190 # if you specify OP, you must specify all valid OPs
231 # Helper functions for passing the above lexically scoped tables above
233 sub FIELDS { return \%FIELD_METADATA }
234 sub dispatch { return \%dispatch }
235 sub can_bundle { return \%can_bundle }
237 # Bring in the clowns.
238 require RT::Tickets_SQL;
241 our @SORTFIELDS = qw(id Status
243 Owner Created Due Starts Started
245 Resolved LastUpdated Priority TimeWorked TimeLeft);
249 Returns the list of fields that lists of tickets can easily be sorted by
255 return (@SORTFIELDS);
259 # BEGIN SQL STUFF *********************************
264 $self->SUPER::CleanSlate( @_ );
265 delete $self->{$_} foreach qw(
267 _sql_group_members_aliases
268 _sql_object_cfv_alias
269 _sql_role_group_aliases
271 _sql_u_watchers_alias_for_sort
272 _sql_u_watchers_aliases
273 _sql_current_user_can_see_applied
277 =head1 Limit Helper Routines
279 These routines are the targets of a dispatch table depending on the
280 type of field. They all share the same signature:
282 my ($self,$field,$op,$value,@rest) = @_;
284 The values in @rest should be suitable for passing directly to
285 DBIx::SearchBuilder::Limit.
287 Essentially they are an expanded/broken out (and much simplified)
288 version of what ProcessRestrictions used to do. They're also much
289 more clearly delineated by the TYPE of field being processed.
298 my ( $sb, $field, $op, $value, @rest ) = @_;
300 if ( $value eq '__Bookmarked__' ) {
301 return $sb->_BookmarkLimit( $field, $op, $value, @rest );
303 return $sb->_IntLimit( $field, $op, $value, @rest );
308 my ( $sb, $field, $op, $value, @rest ) = @_;
310 die "Invalid operator $op for __Bookmarked__ search on $field"
311 unless $op =~ /^(=|!=)$/;
314 my $tmp = $sb->CurrentUser->UserObj->FirstAttribute('Bookmarks');
315 $tmp = $tmp->Content if $tmp;
320 return $sb->_SQLLimit(
327 # as bookmarked tickets can be merged we have to use a join
328 # but it should be pretty lightweight
329 my $tickets_alias = $sb->Join(
334 FIELD2 => 'EffectiveId',
338 my $ea = $op eq '='? 'OR': 'AND';
339 foreach my $id ( sort @bookmarks ) {
341 ALIAS => $tickets_alias,
345 $first? (@rest): ( ENTRYAGGREGATOR => $ea )
347 $first = 0 if $first;
354 Handle Fields which are limited to certain values, and potentially
355 need to be looked up from another class.
357 This subroutine actually handles two different kinds of fields. For
358 some the user is responsible for limiting the values. (i.e. Status,
361 For others, the value specified by the user will be looked by via
365 name of class to lookup in (Optional)
370 my ( $sb, $field, $op, $value, @rest ) = @_;
372 # SQL::Statement changes != to <>. (Can we remove this now?)
373 $op = "!=" if $op eq "<>";
375 die "Invalid Operation: $op for $field"
379 my $meta = $FIELD_METADATA{$field};
380 if ( defined $meta->[1] && defined $value && $value !~ /^\d+$/ ) {
381 my $class = "RT::" . $meta->[1];
382 my $o = $class->new( $sb->CurrentUser );
384 $value = $o->Id || 0;
385 } elsif ( $field eq "Type" ) {
386 $value = lc $value if $value =~ /^(ticket|approval|reminder)$/i;
387 } elsif ($field eq "Status") {
400 Handle fields where the values are limited to integers. (For example,
401 Priority, TimeWorked.)
409 my ( $sb, $field, $op, $value, @rest ) = @_;
411 die "Invalid Operator $op for $field"
412 unless $op =~ /^(=|!=|>|<|>=|<=)$/;
424 Handle fields which deal with links between tickets. (MemberOf, DependsOn)
427 1: Direction (From, To)
428 2: Link Type (MemberOf, DependsOn, RefersTo)
433 my ( $sb, $field, $op, $value, @rest ) = @_;
435 my $meta = $FIELD_METADATA{$field};
436 die "Invalid Operator $op for $field" unless $op =~ /^(=|!=|IS|IS NOT)$/io;
439 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
443 $is_null = 1 if !$value || $value =~ /^null$/io;
445 my $direction = $meta->[1] || '';
446 my ($matchfield, $linkfield) = ('', '');
447 if ( $direction eq 'To' ) {
448 ($matchfield, $linkfield) = ("Target", "Base");
450 elsif ( $direction eq 'From' ) {
451 ($matchfield, $linkfield) = ("Base", "Target");
453 elsif ( $direction ) {
454 die "Invalid link direction '$direction' for $field\n";
457 $sb->_LinkLimit( 'LinkedTo', $op, $value, @rest );
459 'LinkedFrom', $op, $value, @rest,
460 ENTRYAGGREGATOR => (($is_negative && $is_null) || (!$is_null && !$is_negative))? 'OR': 'AND',
468 $op = ($op =~ /^(=|IS)$/i)? 'IS': 'IS NOT';
470 elsif ( $value =~ /\D/ ) {
471 $value = RT::URI->new( $sb->CurrentUser )->CanonicalizeURI( $value );
474 $matchfield = "Local$matchfield" if $is_local;
476 #For doing a left join to find "unlinked tickets" we want to generate a query that looks like this
477 # SELECT main.* FROM Tickets main
478 # LEFT JOIN Links Links_1 ON ( (Links_1.Type = 'MemberOf')
479 # AND(main.id = Links_1.LocalTarget))
480 # WHERE Links_1.LocalBase IS NULL;
483 my $linkalias = $sb->Join(
488 FIELD2 => 'Local' . $linkfield
491 LEFTJOIN => $linkalias,
499 FIELD => $matchfield,
506 my $linkalias = $sb->Join(
511 FIELD2 => 'Local' . $linkfield
514 LEFTJOIN => $linkalias,
520 LEFTJOIN => $linkalias,
521 FIELD => $matchfield,
528 FIELD => $matchfield,
529 OPERATOR => $is_negative? 'IS': 'IS NOT',
538 Handle date fields. (Created, LastTold..)
541 1: type of link. (Probably not necessary.)
546 my ( $sb, $field, $op, $value, @rest ) = @_;
548 die "Invalid Date Op: $op"
549 unless $op =~ /^(=|>|<|>=|<=)$/;
551 my $meta = $FIELD_METADATA{$field};
552 die "Incorrect Meta Data for $field"
553 unless ( defined $meta->[1] );
555 $sb->_DateFieldLimit( $meta->[1], $op, $value, @rest );
558 # Factor this out for use by custom fields
560 sub _DateFieldLimit {
561 my ( $sb, $field, $op, $value, @rest ) = @_;
563 my $date = RT::Date->new( $sb->CurrentUser );
564 $date->Set( Format => 'unknown', Value => $value );
568 # if we're specifying =, that means we want everything on a
569 # particular single day. in the database, we need to check for >
570 # and < the edges of that day.
572 # Except if the value is 'this month' or 'last month', check
573 # > and < the edges of the month.
575 my ($daystart, $dayend);
576 if ( lc($value) eq 'this month' ) {
578 $date->SetToStart('month', Timezone => 'server');
579 $daystart = $date->ISO;
580 $date->AddMonth(Timezone => 'server');
581 $dayend = $date->ISO;
583 elsif ( lc($value) eq 'last month' ) {
585 $date->SetToStart('month', Timezone => 'server');
586 $dayend = $date->ISO;
588 $date->SetToStart('month', Timezone => 'server');
589 $daystart = $date->ISO;
592 $date->SetToMidnight( Timezone => 'server' );
593 $daystart = $date->ISO;
595 $dayend = $date->ISO;
612 ENTRYAGGREGATOR => 'AND',
630 Handle simple fields which are just strings. (Subject,Type)
638 my ( $sb, $field, $op, $value, @rest ) = @_;
642 # =, !=, LIKE, NOT LIKE
643 if ( RT->Config->Get('DatabaseType') eq 'Oracle'
644 && (!defined $value || !length $value)
645 && lc($op) ne 'is' && lc($op) ne 'is not'
647 if ($op eq '!=' || $op =~ /^NOT\s/i) {
664 =head2 _TransDateLimit
666 Handle fields limiting based on Transaction Date.
668 The inpupt value must be in a format parseable by Time::ParseDate
675 # This routine should really be factored into translimit.
676 sub _TransDateLimit {
677 my ( $sb, $field, $op, $value, @rest ) = @_;
679 # See the comments for TransLimit, they apply here too
681 my $txn_alias = $sb->JoinTransactions;
683 my $date = RT::Date->new( $sb->CurrentUser );
684 $date->Set( Format => 'unknown', Value => $value );
689 # if we're specifying =, that means we want everything on a
690 # particular single day. in the database, we need to check for >
691 # and < the edges of that day.
693 $date->SetToMidnight( Timezone => 'server' );
694 my $daystart = $date->ISO;
696 my $dayend = $date->ISO;
711 ENTRYAGGREGATOR => 'AND',
716 # not searching for a single day
719 #Search for the right field
734 Limit based on the ContentType or the Filename of a transaction.
739 my ( $self, $field, $op, $value, %rest ) = @_;
741 my $txn_alias = $self->JoinTransactions;
742 unless ( defined $self->{_sql_trattachalias} ) {
743 $self->{_sql_trattachalias} = $self->_SQLJoin(
744 TYPE => 'LEFT', # not all txns have an attachment
745 ALIAS1 => $txn_alias,
747 TABLE2 => 'Attachments',
748 FIELD2 => 'TransactionId',
754 ALIAS => $self->{_sql_trattachalias},
762 =head2 _TransContentLimit
764 Limit based on the Content of a transaction.
768 sub _TransContentLimit {
772 # If only this was this simple. We've got to do something
775 #Basically, we want to make sure that the limits apply to
776 #the same attachment, rather than just another attachment
777 #for the same ticket, no matter how many clauses we lump
778 #on. We put them in TicketAliases so that they get nuked
779 #when we redo the join.
781 # In the SQL, we might have
782 # (( Content = foo ) or ( Content = bar AND Content = baz ))
783 # The AND group should share the same Alias.
785 # Actually, maybe it doesn't matter. We use the same alias and it
786 # works itself out? (er.. different.)
788 # Steal more from _ProcessRestrictions
790 # FIXME: Maybe look at the previous FooLimit call, and if it was a
791 # TransLimit and EntryAggregator == AND, reuse the Aliases?
793 # Or better - store the aliases on a per subclause basis - since
794 # those are going to be the things we want to relate to each other,
797 # maybe we should not allow certain kinds of aggregation of these
798 # clauses and do a psuedo regex instead? - the problem is getting
799 # them all into the same subclause when you have (A op B op C) - the
800 # way they get parsed in the tree they're in different subclauses.
802 my ( $self, $field, $op, $value, %rest ) = @_;
803 $field = 'Content' if $field =~ /\W/;
805 my $config = RT->Config->Get('FullTextSearch') || {};
806 unless ( $config->{'Enable'} ) {
807 $self->_SQLLimit( %rest, FIELD => 'id', VALUE => 0 );
811 my $txn_alias = $self->JoinTransactions;
812 unless ( defined $self->{_sql_trattachalias} ) {
813 $self->{_sql_trattachalias} = $self->_SQLJoin(
814 TYPE => 'LEFT', # not all txns have an attachment
815 ALIAS1 => $txn_alias,
817 TABLE2 => 'Attachments',
818 FIELD2 => 'TransactionId',
823 if ( $config->{'Indexed'} ) {
824 my $db_type = RT->Config->Get('DatabaseType');
827 if ( $config->{'Table'} and $config->{'Table'} ne "Attachments") {
828 $alias = $self->{'_sql_aliases'}{'full_text'} ||= $self->_SQLJoin(
830 ALIAS1 => $self->{'_sql_trattachalias'},
832 TABLE2 => $config->{'Table'},
836 $alias = $self->{'_sql_trattachalias'};
839 #XXX: handle negative searches
840 my $index = $config->{'Column'};
841 if ( $db_type eq 'Oracle' ) {
842 my $dbh = $RT::Handle->dbh;
843 my $alias = $self->{_sql_trattachalias};
846 FUNCTION => "CONTAINS( $alias.$field, ".$dbh->quote($value) .")",
852 # this is required to trick DBIx::SB's LEFT JOINS optimizer
853 # into deciding that join is redundant as it is
855 ENTRYAGGREGATOR => 'AND',
856 ALIAS => $self->{_sql_trattachalias},
858 OPERATOR => 'IS NOT',
862 elsif ( $db_type eq 'Pg' ) {
863 my $dbh = $RT::Handle->dbh;
869 VALUE => 'plainto_tsquery('. $dbh->quote($value) .')',
873 elsif ( $db_type eq 'mysql' ) {
874 # XXX: We could theoretically skip the join to Attachments,
875 # and have Sphinx simply index and group by the TicketId,
876 # and join Ticket.id to that attribute, which would be much
877 # more efficient -- however, this is only a possibility if
878 # there are no other transaction limits.
880 # This is a special character. Note that \ does not escape
881 # itself (in Sphinx 2.1.0, at least), so 'foo\;bar' becoming
882 # 'foo\\;bar' is not a vulnerability, and is still parsed as
883 # "foo, \, ;, then bar". Happily, the default mode is
884 # "all", meaning that boolean operators are not special.
887 my $max = $config->{'MaxMatches'};
893 VALUE => "$value;limit=$max;maxmatches=$max",
899 ALIAS => $self->{_sql_trattachalias},
906 if ( RT->Config->Get('DontSearchFileAttachments') ) {
908 ENTRYAGGREGATOR => 'AND',
909 ALIAS => $self->{_sql_trattachalias},
920 Handle watcher limits. (Requestor, CC, etc..)
936 my $meta = $FIELD_METADATA{ $field };
937 my $type = $meta->[1] || '';
938 my $class = $meta->[2] || 'Ticket';
940 # Bail if the subfield is not allowed
942 and not grep { $_ eq $rest{SUBKEY} } @{$SEARCHABLE_SUBFIELDS{'User'}})
944 die "Invalid watcher subfield: '$rest{SUBKEY}'";
947 # if it's equality op and search by Email or Name then we can preload user
948 # we do it to help some DBs better estimate number of rows and get better plans
949 if ( $op =~ /^!?=$/ && (!$rest{'SUBKEY'} || $rest{'SUBKEY'} eq 'Name' || $rest{'SUBKEY'} eq 'EmailAddress') ) {
950 my $o = RT::User->new( $self->CurrentUser );
953 ? $field eq 'Owner'? 'Load' : 'LoadByEmail'
954 : $rest{'SUBKEY'} eq 'EmailAddress' ? 'LoadByEmail': 'Load';
955 $o->$method( $value );
956 $rest{'SUBKEY'} = 'id';
957 $value = $o->id || 0;
960 # Owner was ENUM field, so "Owner = 'xxx'" allowed user to
961 # search by id and Name at the same time, this is workaround
962 # to preserve backward compatibility
963 if ( $field eq 'Owner' ) {
964 if ( ($rest{'SUBKEY'}||'') eq 'id' ) {
974 $rest{SUBKEY} ||= 'EmailAddress';
976 my ($groups, $group_members, $users);
977 if ( $rest{'BUNDLE'} ) {
978 ($groups, $group_members, $users) = @{ $rest{'BUNDLE'} };
980 $groups = $self->_RoleGroupsJoin( Type => $type, Class => $class, New => !$type );
984 if ( $op =~ /^IS(?: NOT)?$/i ) {
985 # is [not] empty case
987 $group_members ||= $self->_GroupMembersJoin( GroupsAlias => $groups );
988 # to avoid joining the table Users into the query, we just join GM
989 # and make sure we don't match records where group is member of itself
991 LEFTJOIN => $group_members,
994 VALUE => "$group_members.MemberId",
998 ALIAS => $group_members,
1005 elsif ( $op =~ /^!=$|^NOT\s+/i ) {
1006 # negative condition case
1009 $op =~ s/!|NOT\s+//i;
1011 # XXX: we have no way to build correct "Watcher.X != 'Y'" when condition
1012 # "X = 'Y'" matches more then one user so we try to fetch two records and
1013 # do the right thing when there is only one exist and semi-working solution
1015 my $users_obj = RT::Users->new( $self->CurrentUser );
1017 FIELD => $rest{SUBKEY},
1021 $users_obj->OrderBy;
1022 $users_obj->RowsPerPage(2);
1023 my @users = @{ $users_obj->ItemsArrayRef };
1025 $group_members ||= $self->_GroupMembersJoin( GroupsAlias => $groups );
1026 if ( @users <= 1 ) {
1028 $uid = $users[0]->id if @users;
1029 $self->SUPER::Limit(
1030 LEFTJOIN => $group_members,
1031 ALIAS => $group_members,
1032 FIELD => 'MemberId',
1037 ALIAS => $group_members,
1043 $self->SUPER::Limit(
1044 LEFTJOIN => $group_members,
1047 VALUE => "$group_members.MemberId",
1050 $users ||= $self->Join(
1052 ALIAS1 => $group_members,
1053 FIELD1 => 'MemberId',
1057 $self->SUPER::Limit(
1060 FIELD => $rest{SUBKEY},
1074 # positive condition case
1076 $group_members ||= $self->_GroupMembersJoin(
1077 GroupsAlias => $groups, New => 1, Left => 0
1079 $users ||= $self->Join(
1081 ALIAS1 => $group_members,
1082 FIELD1 => 'MemberId',
1089 FIELD => $rest{'SUBKEY'},
1096 return ($groups, $group_members, $users);
1099 sub _RoleGroupsJoin {
1101 my %args = (New => 0, Class => 'Ticket', Type => '', @_);
1102 return $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1103 if $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1106 # we always have watcher groups for ticket, so we use INNER join
1107 my $groups = $self->Join(
1109 FIELD1 => $args{'Class'} eq 'Queue'? 'Queue': 'id',
1111 FIELD2 => 'Instance',
1112 ENTRYAGGREGATOR => 'AND',
1114 $self->SUPER::Limit(
1115 LEFTJOIN => $groups,
1118 VALUE => 'RT::'. $args{'Class'} .'-Role',
1120 $self->SUPER::Limit(
1121 LEFTJOIN => $groups,
1124 VALUE => $args{'Type'},
1127 $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} } = $groups
1128 unless $args{'New'};
1133 sub _GroupMembersJoin {
1135 my %args = (New => 1, GroupsAlias => undef, Left => 1, @_);
1137 return $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1138 if $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1141 my $alias = $self->Join(
1142 $args{'Left'} ? (TYPE => 'LEFT') : (),
1143 ALIAS1 => $args{'GroupsAlias'},
1145 TABLE2 => 'CachedGroupMembers',
1146 FIELD2 => 'GroupId',
1147 ENTRYAGGREGATOR => 'AND',
1149 $self->SUPER::Limit(
1150 $args{'Left'} ? (LEFTJOIN => $alias) : (),
1152 FIELD => 'Disabled',
1156 $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} } = $alias
1157 unless $args{'New'};
1164 Helper function which provides joins to a watchers table both for limits
1171 my $type = shift || '';
1174 my $groups = $self->_RoleGroupsJoin( Type => $type );
1175 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
1176 # XXX: work around, we must hide groups that
1177 # are members of the role group we search in,
1178 # otherwise them result in wrong NULLs in Users
1179 # table and break ordering. Now, we know that
1180 # RT doesn't allow to add groups as members of the
1181 # ticket roles, so we just hide entries in CGM table
1182 # with MemberId == GroupId from results
1183 $self->SUPER::Limit(
1184 LEFTJOIN => $group_members,
1187 VALUE => "$group_members.MemberId",
1190 my $users = $self->Join(
1192 ALIAS1 => $group_members,
1193 FIELD1 => 'MemberId',
1197 return ($groups, $group_members, $users);
1200 =head2 _WatcherMembershipLimit
1202 Handle watcher membership limits, i.e. whether the watcher belongs to a
1203 specific group or not.
1206 1: Field to query on
1208 SELECT DISTINCT main.*
1212 CachedGroupMembers CachedGroupMembers_2,
1215 (main.EffectiveId = main.id)
1217 (main.Status != 'deleted')
1219 (main.Type = 'ticket')
1222 (Users_3.EmailAddress = '22')
1224 (Groups_1.Domain = 'RT::Ticket-Role')
1226 (Groups_1.Type = 'RequestorGroup')
1229 Groups_1.Instance = main.id
1231 Groups_1.id = CachedGroupMembers_2.GroupId
1233 CachedGroupMembers_2.MemberId = Users_3.id
1234 ORDER BY main.id ASC
1239 sub _WatcherMembershipLimit {
1240 my ( $self, $field, $op, $value, @rest ) = @_;
1245 my $groups = $self->NewAlias('Groups');
1246 my $groupmembers = $self->NewAlias('CachedGroupMembers');
1247 my $users = $self->NewAlias('Users');
1248 my $memberships = $self->NewAlias('CachedGroupMembers');
1250 if ( ref $field ) { # gross hack
1251 my @bundle = @$field;
1253 for my $chunk (@bundle) {
1254 ( $field, $op, $value, @rest ) = @$chunk;
1256 ALIAS => $memberships,
1267 ALIAS => $memberships,
1275 # Tie to groups for tickets we care about
1279 VALUE => 'RT::Ticket-Role',
1280 ENTRYAGGREGATOR => 'AND'
1285 FIELD1 => 'Instance',
1292 # If we care about which sort of watcher
1293 my $meta = $FIELD_METADATA{$field};
1294 my $type = ( defined $meta->[1] ? $meta->[1] : undef );
1301 ENTRYAGGREGATOR => 'AND'
1308 ALIAS2 => $groupmembers,
1313 ALIAS1 => $groupmembers,
1314 FIELD1 => 'MemberId',
1320 ALIAS => $groupmembers,
1321 FIELD => 'Disabled',
1326 ALIAS1 => $memberships,
1327 FIELD1 => 'MemberId',
1333 ALIAS => $memberships,
1334 FIELD => 'Disabled',
1343 =head2 _CustomFieldDecipher
1345 Try and turn a CF descriptor into (cfid, cfname) object pair.
1347 Takes an optional second parameter of the CF LookupType, defaults to Ticket CFs.
1351 sub _CustomFieldDecipher {
1352 my ($self, $string, $lookuptype) = @_;
1353 $lookuptype ||= $self->_SingularClass->CustomFieldLookupType;
1355 my ($object, $field, $column) = ($string =~ /^(?:(.+?)\.)?\{(.+)\}(?:\.(Content|LargeContent))?$/);
1356 $field ||= ($string =~ /^{(.*?)}$/)[0] || $string;
1358 my ($cf, $applied_to);
1361 my $record_class = RT::CustomField->RecordClassFromLookupType($lookuptype);
1362 $applied_to = $record_class->new( $self->CurrentUser );
1363 $applied_to->Load( $object );
1365 if ( $applied_to->id ) {
1366 RT->Logger->debug("Limiting to CFs identified by '$field' applied to $record_class #@{[$applied_to->id]} (loaded via '$object')");
1369 RT->Logger->warning("$record_class '$object' doesn't exist, parsed from '$string'");
1375 if ( $field =~ /\D/ ) {
1377 my $cfs = RT::CustomFields->new( $self->CurrentUser );
1378 $cfs->Limit( FIELD => 'Name', VALUE => $field, ($applied_to ? (CASESENSITIVE => 0) : ()) );
1379 $cfs->LimitToLookupType($lookuptype);
1382 $cfs->SetContextObject($applied_to);
1383 $cfs->LimitToObjectId($applied_to->id);
1386 # if there is more then one field the current user can
1387 # see with the same name then we shouldn't return cf object
1388 # as we don't know which one to use
1391 $cf = undef if $cfs->Next;
1395 $cf = RT::CustomField->new( $self->CurrentUser );
1396 $cf->Load( $field );
1397 $cf->SetContextObject($applied_to)
1398 if $cf->id and $applied_to;
1401 return ($object, $field, $cf, $column);
1404 =head2 _CustomFieldJoin
1406 Factor out the Join of custom fields so we can use it for sorting too
1410 our %JOIN_ALIAS_FOR_LOOKUP_TYPE = (
1411 RT::Ticket->CustomFieldLookupType => sub { "main" },
1414 sub _CustomFieldJoin {
1415 my ($self, $cfkey, $cfid, $field, $type) = @_;
1416 $type ||= RT::Ticket->CustomFieldLookupType;
1418 # Perform one Join per CustomField
1419 if ( $self->{_sql_object_cfv_alias}{$cfkey} ||
1420 $self->{_sql_cf_alias}{$cfkey} )
1422 return ( $self->{_sql_object_cfv_alias}{$cfkey},
1423 $self->{_sql_cf_alias}{$cfkey} );
1426 my $ObjectAlias = $JOIN_ALIAS_FOR_LOOKUP_TYPE{$type}
1427 ? $JOIN_ALIAS_FOR_LOOKUP_TYPE{$type}->($self)
1428 : die "We don't know how to join on $type";
1430 my ($ObjectCFs, $CFs);
1432 $ObjectCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1434 ALIAS1 => $ObjectAlias,
1436 TABLE2 => 'ObjectCustomFieldValues',
1437 FIELD2 => 'ObjectId',
1439 $self->SUPER::Limit(
1440 LEFTJOIN => $ObjectCFs,
1441 FIELD => 'CustomField',
1443 ENTRYAGGREGATOR => 'AND'
1447 my $ocfalias = $self->Join(
1450 TABLE2 => 'ObjectCustomFields',
1451 FIELD2 => 'ObjectId',
1454 $self->SUPER::Limit(
1455 LEFTJOIN => $ocfalias,
1456 ENTRYAGGREGATOR => 'OR',
1457 FIELD => 'ObjectId',
1461 $CFs = $self->{_sql_cf_alias}{$cfkey} = $self->Join(
1463 ALIAS1 => $ocfalias,
1464 FIELD1 => 'CustomField',
1465 TABLE2 => 'CustomFields',
1468 $self->SUPER::Limit(
1470 ENTRYAGGREGATOR => 'AND',
1471 FIELD => 'LookupType',
1474 $self->SUPER::Limit(
1476 ENTRYAGGREGATOR => 'AND',
1481 $ObjectCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1485 TABLE2 => 'ObjectCustomFieldValues',
1486 FIELD2 => 'CustomField',
1488 $self->SUPER::Limit(
1489 LEFTJOIN => $ObjectCFs,
1490 FIELD => 'ObjectId',
1491 VALUE => "$ObjectAlias.id",
1493 ENTRYAGGREGATOR => 'AND',
1497 $self->SUPER::Limit(
1498 LEFTJOIN => $ObjectCFs,
1499 FIELD => 'ObjectType',
1500 VALUE => RT::CustomField->ObjectTypeFromLookupType($type),
1501 ENTRYAGGREGATOR => 'AND'
1503 $self->SUPER::Limit(
1504 LEFTJOIN => $ObjectCFs,
1505 FIELD => 'Disabled',
1508 ENTRYAGGREGATOR => 'AND'
1511 return ($ObjectCFs, $CFs);
1514 =head2 _CustomFieldLimit
1516 Limit based on CustomFields
1523 use Regexp::Common qw(RE_net_IPv4);
1524 use Regexp::Common::net::CIDR;
1527 sub _CustomFieldLimit {
1528 my ( $self, $_field, $op, $value, %rest ) = @_;
1530 my $meta = $FIELD_METADATA{ $_field };
1531 my $class = $meta->[1] || 'Ticket';
1532 my $type = "RT::$class"->CustomFieldLookupType;
1534 my $field = $rest{'SUBKEY'} || die "No field specified";
1536 # For our sanity, we can only limit on one queue at a time
1538 my ($object, $cfid, $cf, $column);
1539 ($object, $field, $cf, $column) = $self->_CustomFieldDecipher( $field, $type );
1540 $cfid = $cf ? $cf->id : 0 ;
1542 # If we're trying to find custom fields that don't match something, we
1543 # want tickets where the custom field has no value at all. Note that
1544 # we explicitly don't include the "IS NULL" case, since we would
1545 # otherwise end up with a redundant clause.
1547 my ($negative_op, $null_op, $inv_op, $range_op)
1548 = $self->ClassifySQLOperation( $op );
1551 return @_ unless RT->Config->Get('DatabaseType') eq 'Oracle';
1554 return %args unless $args{'FIELD'} eq 'LargeContent';
1556 my $op = $args{'OPERATOR'};
1558 $args{'OPERATOR'} = 'MATCHES';
1560 elsif ( $op eq '!=' ) {
1561 $args{'OPERATOR'} = 'NOT MATCHES';
1563 elsif ( $op =~ /^[<>]=?$/ ) {
1564 $args{'FUNCTION'} = "TO_CHAR( $args{'ALIAS'}.LargeContent )";
1569 if ( $cf && $cf->Type eq 'IPAddress' ) {
1570 my $parsed = RT::ObjectCustomFieldValue->ParseIP($value);
1575 $RT::Logger->warn("$value is not a valid IPAddress");
1579 if ( $cf && $cf->Type eq 'IPAddressRange' ) {
1580 my ( $start_ip, $end_ip ) =
1581 RT::ObjectCustomFieldValue->ParseIPRange($value);
1582 if ( $start_ip && $end_ip ) {
1583 if ( $op =~ /^([<>])=?$/ ) {
1584 my $is_less = $1 eq '<' ? 1 : 0;
1593 $value = join '-', $start_ip, $end_ip;
1597 $RT::Logger->warn("$value is not a valid IPAddressRange");
1601 if ( $cf && $cf->Type =~ /^Date(?:Time)?$/ ) {
1602 my $date = RT::Date->new( $self->CurrentUser );
1603 $date->Set( Format => 'unknown', Value => $value );
1604 if ( $date->Unix ) {
1608 || $value =~ /^\s*(?:today|tomorrow|yesterday)\s*$/i
1609 || ( $value !~ /midnight|\d+:\d+:\d+/i
1610 && $date->Time( Timezone => 'user' ) eq '00:00:00' )
1613 $value = $date->Date( Timezone => 'user' );
1616 $value = $date->DateTime;
1620 $RT::Logger->warn("$value is not a valid date string");
1624 my $single_value = !$cf || !$cfid || $cf->SingleValue;
1626 my $cfkey = $cfid ? $cfid : "$type-$object.$field";
1628 if ( $null_op && !$column ) {
1629 # IS[ NOT] NULL without column is the same as has[ no] any CF value,
1630 # we can reuse our default joins for this operation
1631 # with column specified we have different situation
1632 my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field, $type );
1635 ALIAS => $ObjectCFs,
1644 OPERATOR => 'IS NOT',
1647 ENTRYAGGREGATOR => 'AND',
1651 elsif ( $op !~ /^[<>]=?$/ && ( $cf && $cf->Type eq 'IPAddressRange')) {
1653 my ($start_ip, $end_ip) = split /-/, $value;
1656 if ( $op !~ /NOT|!=|<>/i ) { # positive equation
1657 $self->_CustomFieldLimit(
1658 $_field, '<=', $end_ip, %rest,
1659 SUBKEY => $rest{'SUBKEY'}. '.Content',
1661 $self->_CustomFieldLimit(
1662 $_field, '>=', $start_ip, %rest,
1663 SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1664 ENTRYAGGREGATOR => 'AND',
1666 # as well limit borders so DB optimizers can use better
1667 # estimations and scan less rows
1668 # have to disable this tweak because of ipv6
1669 # $self->_CustomFieldLimit(
1670 # $_field, '>=', '000.000.000.000', %rest,
1671 # SUBKEY => $rest{'SUBKEY'}. '.Content',
1672 # ENTRYAGGREGATOR => 'AND',
1674 # $self->_CustomFieldLimit(
1675 # $_field, '<=', '255.255.255.255', %rest,
1676 # SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1677 # ENTRYAGGREGATOR => 'AND',
1680 else { # negative equation
1681 $self->_CustomFieldLimit($_field, '>', $end_ip, %rest);
1682 $self->_CustomFieldLimit(
1683 $_field, '<', $start_ip, %rest,
1684 SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1685 ENTRYAGGREGATOR => 'OR',
1687 # TODO: as well limit borders so DB optimizers can use better
1688 # estimations and scan less rows, but it's harder to do
1689 # as we have OR aggregator
1693 elsif ( !$negative_op || $single_value ) {
1694 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++ if !$single_value && !$range_op;
1695 my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field, $type );
1702 # if column is defined then deal only with it
1703 # otherwise search in Content and in LargeContent
1705 $self->_SQLLimit( $fix_op->(
1706 ALIAS => $ObjectCFs,
1718 # need special treatment for Date
1719 if ( $cf and $cf->Type eq 'DateTime' and $op eq '=' && $value !~ /:/ ) {
1720 # no time specified, that means we want everything on a
1721 # particular day. in the database, we need to check for >
1722 # and < the edges of that day.
1723 my $date = RT::Date->new( $self->CurrentUser );
1724 $date->Set( Format => 'unknown', Value => $value );
1725 my $daystart = $date->ISO;
1727 my $dayend = $date->ISO;
1732 ALIAS => $ObjectCFs,
1740 ALIAS => $ObjectCFs,
1745 ENTRYAGGREGATOR => 'AND',
1750 elsif ( $op eq '=' || $op eq '!=' || $op eq '<>' ) {
1751 if ( length( Encode::encode( "UTF-8", $value) ) < 256 ) {
1753 ALIAS => $ObjectCFs,
1764 ALIAS => $ObjectCFs,
1768 ENTRYAGGREGATOR => 'OR'
1771 ALIAS => $ObjectCFs,
1775 ENTRYAGGREGATOR => 'OR'
1778 $self->_SQLLimit( $fix_op->(
1779 ALIAS => $ObjectCFs,
1780 FIELD => 'LargeContent',
1783 ENTRYAGGREGATOR => 'AND',
1790 ALIAS => $ObjectCFs,
1801 ALIAS => $ObjectCFs,
1805 ENTRYAGGREGATOR => 'OR'
1808 ALIAS => $ObjectCFs,
1812 ENTRYAGGREGATOR => 'OR'
1815 $self->_SQLLimit( $fix_op->(
1816 ALIAS => $ObjectCFs,
1817 FIELD => 'LargeContent',
1820 ENTRYAGGREGATOR => 'AND',
1827 # XXX: if we join via CustomFields table then
1828 # because of order of left joins we get NULLs in
1829 # CF table and then get nulls for those records
1830 # in OCFVs table what result in wrong results
1831 # as decifer method now tries to load a CF then
1832 # we fall into this situation only when there
1833 # are more than one CF with the name in the DB.
1834 # the same thing applies to order by call.
1835 # TODO: reorder joins T <- OCFVs <- CFs <- OCFs if
1836 # we want treat IS NULL as (not applies or has
1841 OPERATOR => 'IS NOT',
1844 ENTRYAGGREGATOR => 'AND',
1850 ALIAS => $ObjectCFs,
1851 FIELD => $column || 'Content',
1855 ENTRYAGGREGATOR => 'OR',
1863 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++;
1864 my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field, $type );
1867 $op =~ s/!|NOT\s+//i;
1869 # if column is defined then deal only with it
1870 # otherwise search in Content and in LargeContent
1872 $self->SUPER::Limit( $fix_op->(
1873 LEFTJOIN => $ObjectCFs,
1874 ALIAS => $ObjectCFs,
1882 $self->SUPER::Limit(
1883 LEFTJOIN => $ObjectCFs,
1884 ALIAS => $ObjectCFs,
1893 ALIAS => $ObjectCFs,
1902 sub _HasAttributeLimit {
1903 my ( $self, $field, $op, $value, %rest ) = @_;
1905 my $alias = $self->Join(
1909 TABLE2 => 'Attributes',
1910 FIELD2 => 'ObjectId',
1912 $self->SUPER::Limit(
1914 FIELD => 'ObjectType',
1915 VALUE => 'RT::Ticket',
1916 ENTRYAGGREGATOR => 'AND'
1918 $self->SUPER::Limit(
1923 ENTRYAGGREGATOR => 'AND'
1929 OPERATOR => $FIELD_METADATA{$field}->[1]? 'IS NOT': 'IS',
1935 # End Helper Functions
1937 # End of SQL Stuff -------------------------------------------------
1940 =head2 OrderByCols ARRAY
1942 A modified version of the OrderBy method which automatically joins where
1943 C<ALIAS> is set to the name of a watcher type.
1954 foreach my $row (@args) {
1955 if ( $row->{ALIAS} ) {
1959 if ( $row->{FIELD} !~ /\./ ) {
1960 my $meta = $self->FIELDS->{ $row->{FIELD} };
1966 if ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'Queue' ) {
1967 my $alias = $self->Join(
1970 FIELD1 => $row->{'FIELD'},
1974 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1975 } elsif ( ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'User' )
1976 || ( $meta->[0] eq 'WATCHERFIELD' && ($meta->[1]||'') eq 'Owner' )
1978 my $alias = $self->Join(
1981 FIELD1 => $row->{'FIELD'},
1985 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1992 my ( $field, $subkey ) = split /\./, $row->{FIELD}, 2;
1993 my $meta = $self->FIELDS->{$field};
1994 if ( defined $meta->[0] && $meta->[0] eq 'WATCHERFIELD' ) {
1995 # cache alias as we want to use one alias per watcher type for sorting
1996 my $users = $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] };
1998 $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] }
1999 = $users = ( $self->_WatcherJoin( $meta->[1] ) )[2];
2001 push @res, { %$row, ALIAS => $users, FIELD => $subkey };
2002 } elsif ( defined $meta->[0] && $meta->[0] eq 'CUSTOMFIELD' ) {
2003 my ($object, $field, $cf_obj, $column) = $self->_CustomFieldDecipher( $subkey );
2004 my $cfkey = $cf_obj ? $cf_obj->id : "$object.$field";
2005 $cfkey .= ".ordering" if !$cf_obj || ($cf_obj->MaxValues||0) != 1;
2006 my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, ($cf_obj ?$cf_obj->id :0) , $field );
2007 # this is described in _CustomFieldLimit
2011 OPERATOR => 'IS NOT',
2014 ENTRYAGGREGATOR => 'AND',
2016 my $CFvs = $self->Join(
2018 ALIAS1 => $ObjectCFs,
2019 FIELD1 => 'CustomField',
2020 TABLE2 => 'CustomFieldValues',
2021 FIELD2 => 'CustomField',
2023 $self->SUPER::Limit(
2027 VALUE => $ObjectCFs . ".Content",
2028 ENTRYAGGREGATOR => 'AND'
2031 push @res, { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' };
2032 push @res, { %$row, ALIAS => $ObjectCFs, FIELD => 'Content' };
2033 } elsif ( $field eq "Custom" && $subkey eq "Ownership") {
2034 # PAW logic is "reversed"
2036 if (exists $row->{ORDER} ) {
2037 my $o = $row->{ORDER};
2038 delete $row->{ORDER};
2039 $order = "DESC" if $o =~ /asc/i;
2042 # Ticket.Owner 1 0 X
2043 # Unowned Tickets 0 1 X
2046 foreach my $uid ( $self->CurrentUser->Id, RT->Nobody->Id ) {
2047 if ( RT->Config->Get('DatabaseType') eq 'Oracle' ) {
2048 my $f = ($row->{'ALIAS'} || 'main') .'.Owner';
2053 FUNCTION => "CASE WHEN $f=$uid THEN 1 ELSE 0 END",
2060 FUNCTION => "Owner=$uid",
2066 push @res, { %$row, FIELD => "Priority", ORDER => $order } ;
2068 } elsif ( $field eq 'Customer' ) { #Freeside
2069 # OrderBy(FIELD => expression) doesn't work, it has to be
2070 # an actual field, so we have to do the join even if sorting
2072 my $custalias = $self->JoinToCustomer;
2073 my $cust_field = lc($subkey);
2074 if ( !$cust_field or $cust_field eq 'number' ) {
2075 $cust_field = 'custnum';
2077 elsif ( $cust_field eq 'name' ) {
2078 $cust_field = "COALESCE( $custalias.company,
2079 $custalias.last || ', ' || $custalias.first
2082 else { # order by cust_main fields directly: 'Customer.agentnum'
2083 $cust_field = $subkey;
2085 push @res, { %$row, ALIAS => $custalias, FIELD => $cust_field };
2087 } elsif ( $field eq 'Service' ) {
2089 my $svcalias = $self->JoinToService;
2090 my $svc_field = lc($subkey);
2091 if ( !$svc_field or $svc_field eq 'number' ) {
2092 $svc_field = 'svcnum';
2094 push @res, { %$row, ALIAS => $svcalias, FIELD => $svc_field };
2102 return $self->SUPER::OrderByCols(@res);
2107 sub JoinToCustLinks {
2108 # Set up join to links (id = localbase),
2109 # limit link type to 'MemberOf',
2110 # and target value to any Freeside custnum URI.
2111 # Return the linkalias for further join/limit action,
2112 # and an sql expression to retrieve the custnum.
2114 # only join once for each RT::Tickets object
2115 my $linkalias = $self->{cust_main_linkalias};
2117 $linkalias = $self->Join(
2122 FIELD2 => 'LocalBase',
2124 $self->SUPER::Limit(
2125 LEFTJOIN => $linkalias,
2128 VALUE => 'fsck.com-rt://%/ticket/%',
2130 $self->SUPER::Limit(
2131 LEFTJOIN => $linkalias,
2134 VALUE => 'MemberOf',
2136 $self->SUPER::Limit(
2137 LEFTJOIN => $linkalias,
2139 OPERATOR => 'STARTSWITH',
2140 VALUE => 'freeside://freeside/cust_main/',
2142 $self->{cust_main_linkalias} = $linkalias;
2144 my $custnum_sql = "CAST(SUBSTR($linkalias.Target,31) AS ";
2145 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
2146 $custnum_sql .= 'SIGNED INTEGER)';
2149 $custnum_sql .= 'INTEGER)';
2151 return ($linkalias, $custnum_sql);
2154 sub JoinToCustomer {
2156 my ($linkalias, $custnum_sql) = $self->JoinToCustLinks;
2157 # don't reuse this join, though--negative queries need
2159 my $custalias = $self->Join(
2161 EXPRESSION => $custnum_sql,
2162 TABLE2 => 'cust_main',
2163 FIELD2 => 'custnum',
2168 sub JoinToSvcLinks {
2170 my $linkalias = $self->{cust_svc_linkalias};
2172 $linkalias = $self->Join(
2177 FIELD2 => 'LocalBase',
2179 $self->SUPER::Limit(
2180 LEFTJOIN => $linkalias,
2183 VALUE => 'fsck.com-rt://%/ticket/%',
2186 $self->SUPER::Limit(
2187 LEFTJOIN => $linkalias,
2190 VALUE => 'MemberOf',
2192 $self->SUPER::Limit(
2193 LEFTJOIN => $linkalias,
2195 OPERATOR => 'STARTSWITH',
2196 VALUE => 'freeside://freeside/cust_svc/',
2198 $self->{cust_svc_linkalias} = $linkalias;
2200 my $svcnum_sql = "CAST(SUBSTR($linkalias.Target,30) AS ";
2201 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
2202 $svcnum_sql .= 'SIGNED INTEGER)';
2205 $svcnum_sql .= 'INTEGER)';
2207 return ($linkalias, $svcnum_sql);
2212 my ($linkalias, $svcnum_sql) = $self->JoinToSvcLinks;
2215 EXPRESSION => $svcnum_sql,
2216 TABLE2 => 'cust_svc',
2221 # This creates an alternate left join path to cust_main via cust_svc.
2222 # _FreesideFieldLimit needs to add this as a separate, independent join
2223 # and include all tickets that have a matching cust_main record via
2225 sub JoinToCustomerViaService {
2227 my $svcalias = $self->JoinToService;
2228 my $cust_pkg = $self->Join(
2230 ALIAS1 => $svcalias,
2232 TABLE2 => 'cust_pkg',
2235 my $cust_main = $self->Join(
2237 ALIAS1 => $cust_pkg,
2238 FIELD1 => 'custnum',
2239 TABLE2 => 'cust_main',
2240 FIELD2 => 'custnum',
2245 sub _FreesideFieldLimit {
2246 my ( $self, $field, $op, $value, %rest ) = @_;
2247 my $is_negative = 0;
2248 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
2249 # if the op is negative, do the join as though
2250 # the op were positive, then accept only records
2251 # where the right-side join key is null.
2253 $op = '=' if $op eq '!=';
2257 my (@alias, $table2, $subfield, $pkey);
2258 if ( $field eq 'Customer' ) {
2259 push @alias, $self->JoinToCustomer;
2260 push @alias, $self->JoinToCustomerViaService;
2263 elsif ( $field eq 'Service' ) {
2264 push @alias, $self->JoinToService;
2268 die "malformed Freeside query: $field";
2271 $subfield = $rest{SUBKEY} || $pkey;
2272 # compound subkey: separate into table name and field in that table
2273 # (must be linked by custnum)
2274 $subfield = lc($subfield);
2275 ($table2, $subfield) = ($1, $2) if $subfield =~ /^(\w+)?\.(\w+)$/;
2276 $subfield = $pkey if $subfield eq 'number';
2278 # if it's compound, create a join from cust_main or cust_svc to that
2279 # table, using custnum or svcnum, and Limit on that table instead.
2281 foreach my $a (@alias) {
2292 # do the actual Limit
2293 $self->SUPER::Limit(
2298 ENTRYAGGREGATOR => 'AND',
2299 # no SUBCLAUSE needed, limits on different aliases across left joins
2300 # are inherently independent
2303 # then, since it's a left join, exclude tickets for which there is now
2304 # no matching record in the table we just limited on. (Or where there
2305 # is a matching record, if $is_negative.)
2306 # For a cust_main query (where there are two different aliases), this
2307 # will produce a subclause: "cust_main_1.custnum IS NOT NULL OR
2308 # cust_main_2.custnum IS NOT NULL" (or "IS NULL AND..." for a negative
2310 # This requires the ENTRYAGGREGATOR to be OR for positive queries
2311 # (where a matching customer exists), but ONLY between these two
2312 # constraints and NOT with anything else in the query, hence the
2319 OPERATOR => $is_negative ? 'IS' : 'IS NOT',
2322 ENTRYAGGREGATOR => $is_negative ? 'AND' : 'OR',
2323 SUBCLAUSE => 'fs_limit',
2328 # _SQLLimit would force SUBCLAUSE to 'ticketsql'; bypass it
2329 $self->SUPER::Limit( %$_ );
2338 Takes a paramhash with the fields FIELD, OPERATOR, VALUE and DESCRIPTION
2339 Generally best called from LimitFoo methods
2349 DESCRIPTION => undef,
2352 $args{'DESCRIPTION'} = $self->loc(
2353 "[_1] [_2] [_3]", $args{'FIELD'},
2354 $args{'OPERATOR'}, $args{'VALUE'}
2356 if ( !defined $args{'DESCRIPTION'} );
2358 my $index = $self->_NextIndex;
2360 # make the TicketRestrictions hash the equivalent of whatever we just passed in;
2362 %{ $self->{'TicketRestrictions'}{$index} } = %args;
2364 $self->{'RecalcTicketLimits'} = 1;
2366 # If we're looking at the effective id, we don't want to append the other clause
2367 # which limits us to tickets where id = effective id
2368 if ( $args{'FIELD'} eq 'EffectiveId'
2369 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
2371 $self->{'looking_at_effective_id'} = 1;
2374 if ( $args{'FIELD'} eq 'Type'
2375 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
2377 $self->{'looking_at_type'} = 1;
2388 LimitQueue takes a paramhash with the fields OPERATOR and VALUE.
2389 OPERATOR is one of = or !=. (It defaults to =).
2390 VALUE is a queue id or Name.
2403 #TODO VALUE should also take queue objects
2404 if ( defined $args{'VALUE'} && $args{'VALUE'} !~ /^\d+$/ ) {
2405 my $queue = RT::Queue->new( $self->CurrentUser );
2406 $queue->Load( $args{'VALUE'} );
2407 $args{'VALUE'} = $queue->Id;
2410 # What if they pass in an Id? Check for isNum() and convert to
2413 #TODO check for a valid queue here
2417 VALUE => $args{'VALUE'},
2418 OPERATOR => $args{'OPERATOR'},
2419 DESCRIPTION => join(
2420 ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{'VALUE'},
2430 Takes a paramhash with the fields OPERATOR and VALUE.
2431 OPERATOR is one of = or !=.
2434 RT adds Status != 'deleted' until object has
2435 allow_deleted_search internal property set.
2436 $tickets->{'allow_deleted_search'} = 1;
2437 $tickets->LimitStatus( VALUE => 'deleted' );
2449 VALUE => $args{'VALUE'},
2450 OPERATOR => $args{'OPERATOR'},
2451 DESCRIPTION => join( ' ',
2452 $self->loc('Status'), $args{'OPERATOR'},
2453 $self->loc( $args{'VALUE'} ) ),
2461 If called, this search will not automatically limit the set of results found
2462 to tickets of type "Ticket". Tickets of other types, such as "project" and
2463 "approval" will be found.
2470 # Instead of faking a Limit that later gets ignored, fake up the
2471 # fact that we're already looking at type, so that the check in
2472 # Tickets_SQL/FromSQL goes down the right branch
2474 # $self->LimitType(VALUE => '__any');
2475 $self->{looking_at_type} = 1;
2482 Takes a paramhash with the fields OPERATOR and VALUE.
2483 OPERATOR is one of = or !=, it defaults to "=".
2484 VALUE is a string to search for in the type of the ticket.
2499 VALUE => $args{'VALUE'},
2500 OPERATOR => $args{'OPERATOR'},
2501 DESCRIPTION => join( ' ',
2502 $self->loc('Type'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2512 Takes a paramhash with the fields OPERATOR and VALUE.
2513 OPERATOR is one of = or !=.
2514 VALUE is a string to search for in the subject of the ticket.
2523 VALUE => $args{'VALUE'},
2524 OPERATOR => $args{'OPERATOR'},
2525 DESCRIPTION => join( ' ',
2526 $self->loc('Subject'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2532 # Things that can be > < = !=
2537 Takes a paramhash with the fields OPERATOR and VALUE.
2538 OPERATOR is one of =, >, < or !=.
2539 VALUE is a ticket Id to search for
2552 VALUE => $args{'VALUE'},
2553 OPERATOR => $args{'OPERATOR'},
2555 join( ' ', $self->loc('Id'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2561 =head2 LimitPriority
2563 Takes a paramhash with the fields OPERATOR and VALUE.
2564 OPERATOR is one of =, >, < or !=.
2565 VALUE is a value to match the ticket's priority against
2573 FIELD => 'Priority',
2574 VALUE => $args{'VALUE'},
2575 OPERATOR => $args{'OPERATOR'},
2576 DESCRIPTION => join( ' ',
2577 $self->loc('Priority'),
2578 $args{'OPERATOR'}, $args{'VALUE'}, ),
2584 =head2 LimitInitialPriority
2586 Takes a paramhash with the fields OPERATOR and VALUE.
2587 OPERATOR is one of =, >, < or !=.
2588 VALUE is a value to match the ticket's initial priority against
2593 sub LimitInitialPriority {
2597 FIELD => 'InitialPriority',
2598 VALUE => $args{'VALUE'},
2599 OPERATOR => $args{'OPERATOR'},
2600 DESCRIPTION => join( ' ',
2601 $self->loc('Initial Priority'), $args{'OPERATOR'},
2608 =head2 LimitFinalPriority
2610 Takes a paramhash with the fields OPERATOR and VALUE.
2611 OPERATOR is one of =, >, < or !=.
2612 VALUE is a value to match the ticket's final priority against
2616 sub LimitFinalPriority {
2620 FIELD => 'FinalPriority',
2621 VALUE => $args{'VALUE'},
2622 OPERATOR => $args{'OPERATOR'},
2623 DESCRIPTION => join( ' ',
2624 $self->loc('Final Priority'), $args{'OPERATOR'},
2631 =head2 LimitTimeWorked
2633 Takes a paramhash with the fields OPERATOR and VALUE.
2634 OPERATOR is one of =, >, < or !=.
2635 VALUE is a value to match the ticket's TimeWorked attribute
2639 sub LimitTimeWorked {
2643 FIELD => 'TimeWorked',
2644 VALUE => $args{'VALUE'},
2645 OPERATOR => $args{'OPERATOR'},
2646 DESCRIPTION => join( ' ',
2647 $self->loc('Time Worked'),
2648 $args{'OPERATOR'}, $args{'VALUE'}, ),
2654 =head2 LimitTimeLeft
2656 Takes a paramhash with the fields OPERATOR and VALUE.
2657 OPERATOR is one of =, >, < or !=.
2658 VALUE is a value to match the ticket's TimeLeft attribute
2666 FIELD => 'TimeLeft',
2667 VALUE => $args{'VALUE'},
2668 OPERATOR => $args{'OPERATOR'},
2669 DESCRIPTION => join( ' ',
2670 $self->loc('Time Left'),
2671 $args{'OPERATOR'}, $args{'VALUE'}, ),
2681 Takes a paramhash with the fields OPERATOR and VALUE.
2682 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2683 VALUE is a string to search for in the body of the ticket
2692 VALUE => $args{'VALUE'},
2693 OPERATOR => $args{'OPERATOR'},
2694 DESCRIPTION => join( ' ',
2695 $self->loc('Ticket content'), $args{'OPERATOR'},
2702 =head2 LimitFilename
2704 Takes a paramhash with the fields OPERATOR and VALUE.
2705 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2706 VALUE is a string to search for in the body of the ticket
2714 FIELD => 'Filename',
2715 VALUE => $args{'VALUE'},
2716 OPERATOR => $args{'OPERATOR'},
2717 DESCRIPTION => join( ' ',
2718 $self->loc('Attachment filename'), $args{'OPERATOR'},
2724 =head2 LimitContentType
2726 Takes a paramhash with the fields OPERATOR and VALUE.
2727 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2728 VALUE is a content type to search ticket attachments for
2732 sub LimitContentType {
2736 FIELD => 'ContentType',
2737 VALUE => $args{'VALUE'},
2738 OPERATOR => $args{'OPERATOR'},
2739 DESCRIPTION => join( ' ',
2740 $self->loc('Ticket content type'), $args{'OPERATOR'},
2751 Takes a paramhash with the fields OPERATOR and VALUE.
2752 OPERATOR is one of = or !=.
2764 my $owner = RT::User->new( $self->CurrentUser );
2765 $owner->Load( $args{'VALUE'} );
2767 # FIXME: check for a valid $owner
2770 VALUE => $args{'VALUE'},
2771 OPERATOR => $args{'OPERATOR'},
2772 DESCRIPTION => join( ' ',
2773 $self->loc('Owner'), $args{'OPERATOR'}, $owner->Name(), ),
2783 Takes a paramhash with the fields OPERATOR, TYPE and VALUE.
2784 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2785 VALUE is a value to match the ticket's watcher email addresses against
2786 TYPE is the sort of watchers you want to match against. Leave it undef if you want to search all of them
2800 #build us up a description
2801 my ( $watcher_type, $desc );
2802 if ( $args{'TYPE'} ) {
2803 $watcher_type = $args{'TYPE'};
2806 $watcher_type = "Watcher";
2810 FIELD => $watcher_type,
2811 VALUE => $args{'VALUE'},
2812 OPERATOR => $args{'OPERATOR'},
2813 TYPE => $args{'TYPE'},
2814 DESCRIPTION => join( ' ',
2815 $self->loc($watcher_type),
2816 $args{'OPERATOR'}, $args{'VALUE'}, ),
2825 =head2 LimitLinkedTo
2827 LimitLinkedTo takes a paramhash with two fields: TYPE and TARGET
2828 TYPE limits the sort of link we want to search on
2830 TYPE = { RefersTo, MemberOf, DependsOn }
2832 TARGET is the id or URI of the TARGET of the link
2846 FIELD => 'LinkedTo',
2848 TARGET => $args{'TARGET'},
2849 TYPE => $args{'TYPE'},
2850 DESCRIPTION => $self->loc(
2851 "Tickets [_1] by [_2]",
2852 $self->loc( $args{'TYPE'} ),
2855 OPERATOR => $args{'OPERATOR'},
2861 =head2 LimitLinkedFrom
2863 LimitLinkedFrom takes a paramhash with two fields: TYPE and BASE
2864 TYPE limits the sort of link we want to search on
2867 BASE is the id or URI of the BASE of the link
2871 sub LimitLinkedFrom {
2880 # translate RT2 From/To naming to RT3 TicketSQL naming
2881 my %fromToMap = qw(DependsOn DependentOn
2883 RefersTo ReferredToBy);
2885 my $type = $args{'TYPE'};
2886 $type = $fromToMap{$type} if exists( $fromToMap{$type} );
2889 FIELD => 'LinkedTo',
2891 BASE => $args{'BASE'},
2893 DESCRIPTION => $self->loc(
2894 "Tickets [_1] [_2]",
2895 $self->loc( $args{'TYPE'} ),
2898 OPERATOR => $args{'OPERATOR'},
2905 my $ticket_id = shift;
2906 return $self->LimitLinkedTo(
2908 TARGET => $ticket_id,
2914 sub LimitHasMember {
2916 my $ticket_id = shift;
2917 return $self->LimitLinkedFrom(
2919 BASE => "$ticket_id",
2920 TYPE => 'HasMember',
2927 sub LimitDependsOn {
2929 my $ticket_id = shift;
2930 return $self->LimitLinkedTo(
2932 TARGET => $ticket_id,
2933 TYPE => 'DependsOn',
2940 sub LimitDependedOnBy {
2942 my $ticket_id = shift;
2943 return $self->LimitLinkedFrom(
2946 TYPE => 'DependentOn',
2955 my $ticket_id = shift;
2956 return $self->LimitLinkedTo(
2958 TARGET => $ticket_id,
2966 sub LimitReferredToBy {
2968 my $ticket_id = shift;
2969 return $self->LimitLinkedFrom(
2972 TYPE => 'ReferredToBy',
2980 =head2 LimitDate (FIELD => 'DateField', OPERATOR => $oper, VALUE => $ISODate)
2982 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2984 OPERATOR is one of > or <
2985 VALUE is a date and time in ISO format in GMT
2986 FIELD is one of Starts, Started, Told, Created, Resolved, LastUpdated
2988 There are also helper functions of the form LimitFIELD that eliminate
2989 the need to pass in a FIELD argument.
3003 #Set the description if we didn't get handed it above
3004 unless ( $args{'DESCRIPTION'} ) {
3005 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
3006 . $args{'OPERATOR'} . " "
3007 . $args{'VALUE'} . " GMT";
3010 $self->Limit(%args);
3017 $self->LimitDate( FIELD => 'Created', @_ );
3022 $self->LimitDate( FIELD => 'Due', @_ );
3028 $self->LimitDate( FIELD => 'Starts', @_ );
3034 $self->LimitDate( FIELD => 'Started', @_ );
3039 $self->LimitDate( FIELD => 'Resolved', @_ );
3044 $self->LimitDate( FIELD => 'Told', @_ );
3047 sub LimitLastUpdated {
3049 $self->LimitDate( FIELD => 'LastUpdated', @_ );
3054 =head2 LimitTransactionDate (OPERATOR => $oper, VALUE => $ISODate)
3056 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
3058 OPERATOR is one of > or <
3059 VALUE is a date and time in ISO format in GMT
3064 sub LimitTransactionDate {
3067 FIELD => 'TransactionDate',
3074 # <20021217042756.GK28744@pallas.fsck.com>
3075 # "Kill It" - Jesse.
3077 #Set the description if we didn't get handed it above
3078 unless ( $args{'DESCRIPTION'} ) {
3079 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
3080 . $args{'OPERATOR'} . " "
3081 . $args{'VALUE'} . " GMT";
3084 $self->Limit(%args);
3091 =head2 LimitCustomField
3093 Takes a paramhash of key/value pairs with the following keys:
3097 =item CUSTOMFIELD - CustomField name or id. If a name is passed, an additional parameter QUEUE may also be passed to distinguish the custom field.
3099 =item OPERATOR - The usual Limit operators
3101 =item VALUE - The value to compare against
3107 sub LimitCustomField {
3111 CUSTOMFIELD => undef,
3113 DESCRIPTION => undef,
3114 FIELD => 'CustomFieldValue',
3119 my $CF = RT::CustomField->new( $self->CurrentUser );
3120 if ( $args{CUSTOMFIELD} =~ /^\d+$/ ) {
3121 $CF->Load( $args{CUSTOMFIELD} );
3124 $CF->LoadByNameAndQueue(
3125 Name => $args{CUSTOMFIELD},
3126 Queue => $args{QUEUE}
3128 $args{CUSTOMFIELD} = $CF->Id;
3131 #If we are looking to compare with a null value.
3132 if ( $args{'OPERATOR'} =~ /^is$/i ) {
3133 $args{'DESCRIPTION'}
3134 ||= $self->loc( "Custom field [_1] has no value.", $CF->Name );
3136 elsif ( $args{'OPERATOR'} =~ /^is not$/i ) {
3137 $args{'DESCRIPTION'}
3138 ||= $self->loc( "Custom field [_1] has a value.", $CF->Name );
3141 # if we're not looking to compare with a null value
3143 $args{'DESCRIPTION'} ||= $self->loc( "Custom field [_1] [_2] [_3]",
3144 $CF->Name, $args{OPERATOR}, $args{VALUE} );
3147 if ( defined $args{'QUEUE'} && $args{'QUEUE'} =~ /\D/ ) {
3148 my $QueueObj = RT::Queue->new( $self->CurrentUser );
3149 $QueueObj->Load( $args{'QUEUE'} );
3150 $args{'QUEUE'} = $QueueObj->Id;
3152 delete $args{'QUEUE'} unless defined $args{'QUEUE'} && length $args{'QUEUE'};
3155 @rest = ( ENTRYAGGREGATOR => 'AND' )
3156 if ( $CF->Type eq 'SelectMultiple' );
3159 VALUE => $args{VALUE},
3161 .(defined $args{'QUEUE'}? ".$args{'QUEUE'}" : '' )
3162 .".{" . $CF->Name . "}",
3163 OPERATOR => $args{OPERATOR},
3168 $self->{'RecalcTicketLimits'} = 1;
3175 Keep track of the counter for the array of restrictions
3181 return ( $self->{'restriction_index'}++ );
3189 $self->{'table'} = "Tickets";
3190 $self->{'RecalcTicketLimits'} = 1;
3191 $self->{'looking_at_effective_id'} = 0;
3192 $self->{'looking_at_type'} = 0;
3193 $self->{'restriction_index'} = 1;
3194 $self->{'primary_key'} = "id";
3195 delete $self->{'items_array'};
3196 delete $self->{'item_map'};
3197 delete $self->{'columns_to_display'};
3198 $self->SUPER::_Init(@_);
3207 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3208 return ( $self->SUPER::Count() );
3214 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3215 return ( $self->SUPER::CountAll() );
3220 =head2 ItemsArrayRef
3222 Returns a reference to the set of all items found in this search
3229 return $self->{'items_array'} if $self->{'items_array'};
3231 my $placeholder = $self->_ItemsCounter;
3232 $self->GotoFirstItem();
3233 while ( my $item = $self->Next ) {
3234 push( @{ $self->{'items_array'} }, $item );
3236 $self->GotoItem($placeholder);
3237 $self->{'items_array'}
3238 = $self->ItemsOrderBy( $self->{'items_array'} );
3240 return $self->{'items_array'};
3243 sub ItemsArrayRefWindow {
3247 my @old = ($self->_ItemsCounter, $self->RowsPerPage, $self->FirstRow+1);
3249 $self->RowsPerPage( $window );
3251 $self->GotoFirstItem;
3254 while ( my $item = $self->Next ) {
3258 $self->RowsPerPage( $old[1] );
3259 $self->FirstRow( $old[2] );
3260 $self->GotoItem( $old[0] );
3269 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3271 my $Ticket = $self->SUPER::Next;
3272 return $Ticket unless $Ticket;
3274 if ( $Ticket->__Value('Status') eq 'deleted'
3275 && !$self->{'allow_deleted_search'} )
3279 elsif ( RT->Config->Get('UseSQLForACLChecks') ) {
3280 # if we found a ticket with this option enabled then
3281 # all tickets we found are ACLed, cache this fact
3282 my $key = join ";:;", $self->CurrentUser->id, 'ShowTicket', 'RT::Ticket-'. $Ticket->id;
3283 $RT::Principal::_ACL_CACHE->set( $key => 1 );
3286 elsif ( $Ticket->CurrentUserHasRight('ShowTicket') ) {
3291 # If the user doesn't have the right to show this ticket
3298 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
3299 return $self->SUPER::_DoSearch( @_ );
3304 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
3305 return $self->SUPER::_DoCount( @_ );
3311 my $cache_key = 'RolesHasRight;:;ShowTicket';
3313 if ( my $cached = $RT::Principal::_ACL_CACHE->fetch( $cache_key ) ) {
3317 my $ACL = RT::ACL->new( RT->SystemUser );
3318 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
3319 $ACL->Limit( FIELD => 'PrincipalType', OPERATOR => '!=', VALUE => 'Group' );
3320 my $principal_alias = $ACL->Join(
3322 FIELD1 => 'PrincipalId',
3323 TABLE2 => 'Principals',
3326 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3329 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
3330 my $role = $ACE->__Value('PrincipalType');
3331 my $type = $ACE->__Value('ObjectType');
3332 if ( $type eq 'RT::System' ) {
3335 elsif ( $type eq 'RT::Queue' ) {
3336 next if $res{ $role } && !ref $res{ $role };
3337 push @{ $res{ $role } ||= [] }, $ACE->__Value('ObjectId');
3340 $RT::Logger->error('ShowTicket right is granted on unsupported object');
3343 $RT::Principal::_ACL_CACHE->set( $cache_key => \%res );
3347 sub _DirectlyCanSeeIn {
3349 my $id = $self->CurrentUser->id;
3351 my $cache_key = 'User-'. $id .';:;ShowTicket;:;DirectlyCanSeeIn';
3352 if ( my $cached = $RT::Principal::_ACL_CACHE->fetch( $cache_key ) ) {
3356 my $ACL = RT::ACL->new( RT->SystemUser );
3357 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
3358 my $principal_alias = $ACL->Join(
3360 FIELD1 => 'PrincipalId',
3361 TABLE2 => 'Principals',
3364 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3365 my $cgm_alias = $ACL->Join(
3367 FIELD1 => 'PrincipalId',
3368 TABLE2 => 'CachedGroupMembers',
3369 FIELD2 => 'GroupId',
3371 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
3372 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
3375 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
3376 my $type = $ACE->__Value('ObjectType');
3377 if ( $type eq 'RT::System' ) {
3378 # If user is direct member of a group that has the right
3379 # on the system then he can see any ticket
3380 $RT::Principal::_ACL_CACHE->set( $cache_key => [-1] );
3383 elsif ( $type eq 'RT::Queue' ) {
3384 push @res, $ACE->__Value('ObjectId');
3387 $RT::Logger->error('ShowTicket right is granted on unsupported object');
3390 $RT::Principal::_ACL_CACHE->set( $cache_key => \@res );
3394 sub CurrentUserCanSee {
3396 return if $self->{'_sql_current_user_can_see_applied'};
3398 return $self->{'_sql_current_user_can_see_applied'} = 1
3399 if $self->CurrentUser->UserObj->HasRight(
3400 Right => 'SuperUser', Object => $RT::System
3403 my $id = $self->CurrentUser->id;
3405 # directly can see in all queues then we have nothing to do
3406 my @direct_queues = $self->_DirectlyCanSeeIn;
3407 return $self->{'_sql_current_user_can_see_applied'} = 1
3408 if @direct_queues && $direct_queues[0] == -1;
3410 my %roles = $self->_RolesCanSee;
3412 my %skip = map { $_ => 1 } @direct_queues;
3413 foreach my $role ( keys %roles ) {
3414 next unless ref $roles{ $role };
3416 my @queues = grep !$skip{$_}, @{ $roles{ $role } };
3418 $roles{ $role } = \@queues;
3420 delete $roles{ $role };
3425 # there is no global watchers, only queues and tickes, if at
3426 # some point we will add global roles then it's gonna blow
3427 # the idea here is that if the right is set globaly for a role
3428 # and user plays this role for a queue directly not a ticket
3429 # then we have to check in advance
3430 if ( my @tmp = grep $_ ne 'Owner' && !ref $roles{ $_ }, keys %roles ) {
3432 my $groups = RT::Groups->new( RT->SystemUser );
3433 $groups->Limit( FIELD => 'Domain', VALUE => 'RT::Queue-Role' );
3435 $groups->Limit( FIELD => 'Type', VALUE => $_ );
3437 my $principal_alias = $groups->Join(
3440 TABLE2 => 'Principals',
3443 $groups->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3444 my $cgm_alias = $groups->Join(
3447 TABLE2 => 'CachedGroupMembers',
3448 FIELD2 => 'GroupId',
3450 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
3451 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
3452 while ( my $group = $groups->Next ) {
3453 push @direct_queues, $group->Instance;
3457 unless ( @direct_queues || keys %roles ) {
3458 $self->SUPER::Limit(
3463 ENTRYAGGREGATOR => 'AND',
3465 return $self->{'_sql_current_user_can_see_applied'} = 1;
3469 my $join_roles = keys %roles;
3470 $join_roles = 0 if $join_roles == 1 && $roles{'Owner'};
3471 my ($role_group_alias, $cgm_alias);
3472 if ( $join_roles ) {
3473 $role_group_alias = $self->_RoleGroupsJoin( New => 1 );
3474 $cgm_alias = $self->_GroupMembersJoin( GroupsAlias => $role_group_alias );
3475 $self->SUPER::Limit(
3476 LEFTJOIN => $cgm_alias,
3477 FIELD => 'MemberId',
3482 my $limit_queues = sub {
3486 return unless @queues;
3487 if ( @queues == 1 ) {
3488 $self->SUPER::Limit(
3493 ENTRYAGGREGATOR => $ea,
3496 $self->SUPER::_OpenParen('ACL');
3497 foreach my $q ( @queues ) {
3498 $self->SUPER::Limit(
3503 ENTRYAGGREGATOR => $ea,
3507 $self->SUPER::_CloseParen('ACL');
3512 $self->SUPER::_OpenParen('ACL');
3514 $ea = 'OR' if $limit_queues->( $ea, @direct_queues );
3515 while ( my ($role, $queues) = each %roles ) {
3516 $self->SUPER::_OpenParen('ACL');
3517 if ( $role eq 'Owner' ) {
3518 $self->SUPER::Limit(
3522 ENTRYAGGREGATOR => $ea,
3526 $self->SUPER::Limit(
3528 ALIAS => $cgm_alias,
3529 FIELD => 'MemberId',
3530 OPERATOR => 'IS NOT',
3533 ENTRYAGGREGATOR => $ea,
3535 $self->SUPER::Limit(
3537 ALIAS => $role_group_alias,
3540 ENTRYAGGREGATOR => 'AND',
3543 $limit_queues->( 'AND', @$queues ) if ref $queues;
3544 $ea = 'OR' if $ea eq 'AND';
3545 $self->SUPER::_CloseParen('ACL');
3547 $self->SUPER::_CloseParen('ACL');
3549 return $self->{'_sql_current_user_can_see_applied'} = 1;
3556 =head2 LoadRestrictions
3558 LoadRestrictions takes a string which can fully populate the TicketRestrictons hash.
3559 TODO It is not yet implemented
3565 =head2 DescribeRestrictions
3568 Returns a hash keyed by restriction id.
3569 Each element of the hash is currently a one element hash that contains DESCRIPTION which
3570 is a description of the purpose of that TicketRestriction
3574 sub DescribeRestrictions {
3579 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
3580 $listing{$row} = $self->{'TicketRestrictions'}{$row}{'DESCRIPTION'};
3587 =head2 RestrictionValues FIELD
3589 Takes a restriction field and returns a list of values this field is restricted
3594 sub RestrictionValues {
3597 map $self->{'TicketRestrictions'}{$_}{'VALUE'}, grep {
3598 $self->{'TicketRestrictions'}{$_}{'FIELD'} eq $field
3599 && $self->{'TicketRestrictions'}{$_}{'OPERATOR'} eq "="
3601 keys %{ $self->{'TicketRestrictions'} };
3606 =head2 ClearRestrictions
3608 Removes all restrictions irretrievably
3612 sub ClearRestrictions {
3614 delete $self->{'TicketRestrictions'};
3615 $self->{'looking_at_effective_id'} = 0;
3616 $self->{'looking_at_type'} = 0;
3617 $self->{'RecalcTicketLimits'} = 1;
3622 =head2 DeleteRestriction
3624 Takes the row Id of a restriction (From DescribeRestrictions' output, for example.
3625 Removes that restriction from the session's limits.
3629 sub DeleteRestriction {
3632 delete $self->{'TicketRestrictions'}{$row};
3634 $self->{'RecalcTicketLimits'} = 1;
3636 #make the underlying easysearch object forget all its preconceptions
3641 # Convert a set of oldstyle SB Restrictions to Clauses for RQL
3643 sub _RestrictionsToClauses {
3647 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
3648 my $restriction = $self->{'TicketRestrictions'}{$row};
3650 # We need to reimplement the subclause aggregation that SearchBuilder does.
3651 # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main',
3652 # Then SB AND's the different Subclauses together.
3654 # So, we want to group things into Subclauses, convert them to
3655 # SQL, and then join them with the appropriate DefaultEA.
3656 # Then join each subclause group with AND.
3658 my $field = $restriction->{'FIELD'};
3659 my $realfield = $field; # CustomFields fake up a fieldname, so
3660 # we need to figure that out
3663 # Rewrite LinkedTo meta field to the real field
3664 if ( $field =~ /LinkedTo/ ) {
3665 $realfield = $field = $restriction->{'TYPE'};
3669 # Handle subkey fields with a different real field
3670 if ( $field =~ /^(\w+)\./ ) {
3674 die "I don't know about $field yet"
3675 unless ( exists $FIELD_METADATA{$realfield}
3676 or $restriction->{CUSTOMFIELD} );
3678 my $type = $FIELD_METADATA{$realfield}->[0];
3679 my $op = $restriction->{'OPERATOR'};
3683 map { $restriction->{$_} } qw(VALUE TICKET BASE TARGET)
3686 # this performs the moral equivalent of defined or/dor/C<//>,
3687 # without the short circuiting.You need to use a 'defined or'
3688 # type thing instead of just checking for truth values, because
3689 # VALUE could be 0.(i.e. "false")
3691 # You could also use this, but I find it less aesthetic:
3692 # (although it does short circuit)
3693 #( defined $restriction->{'VALUE'}? $restriction->{VALUE} :
3694 # defined $restriction->{'TICKET'} ?
3695 # $restriction->{TICKET} :
3696 # defined $restriction->{'BASE'} ?
3697 # $restriction->{BASE} :
3698 # defined $restriction->{'TARGET'} ?
3699 # $restriction->{TARGET} )
3701 my $ea = $restriction->{ENTRYAGGREGATOR}
3702 || $DefaultEA{$type}
3705 die "Invalid operator $op for $field ($type)"
3706 unless exists $ea->{$op};
3710 # Each CustomField should be put into a different Clause so they
3711 # are ANDed together.
3712 if ( $restriction->{CUSTOMFIELD} ) {
3713 $realfield = $field;
3716 exists $clause{$realfield} or $clause{$realfield} = [];
3719 $field =~ s!(['\\])!\\$1!g;
3720 $value =~ s!(['\\])!\\$1!g;
3721 my $data = [ $ea, $type, $field, $op, $value ];
3723 # here is where we store extra data, say if it's a keyword or
3724 # something. (I.e. "TYPE SPECIFIC STUFF")
3726 if (lc $ea eq 'none') {
3727 $clause{$realfield} = [ $data ];
3729 push @{ $clause{$realfield} }, $data;
3737 =head2 _ProcessRestrictions PARAMHASH
3739 # The new _ProcessRestrictions is somewhat dependent on the SQL stuff,
3740 # but isn't quite generic enough to move into Tickets_SQL.
3744 sub _ProcessRestrictions {
3747 #Blow away ticket aliases since we'll need to regenerate them for
3749 delete $self->{'TicketAliases'};
3750 delete $self->{'items_array'};
3751 delete $self->{'item_map'};
3752 delete $self->{'raw_rows'};
3753 delete $self->{'rows'};
3754 delete $self->{'count_all'};
3756 my $sql = $self->Query; # Violating the _SQL namespace
3757 if ( !$sql || $self->{'RecalcTicketLimits'} ) {
3759 # "Restrictions to Clauses Branch\n";
3760 my $clauseRef = eval { $self->_RestrictionsToClauses; };
3762 $RT::Logger->error( "RestrictionsToClauses: " . $@ );
3766 $sql = $self->ClausesToSQL($clauseRef);
3767 $self->FromSQL($sql) if $sql;
3771 $self->{'RecalcTicketLimits'} = 0;
3775 =head2 _BuildItemMap
3777 Build up a L</ItemMap> of first/last/next/prev items, so that we can
3778 display search nav quickly.
3785 my $window = RT->Config->Get('TicketsItemMapSize');
3787 $self->{'item_map'} = {};
3789 my $items = $self->ItemsArrayRefWindow( $window );
3790 return unless $items && @$items;
3793 $self->{'item_map'}{'first'} = $items->[0]->EffectiveId;
3794 for ( my $i = 0; $i < @$items; $i++ ) {
3795 my $item = $items->[$i];
3796 my $id = $item->EffectiveId;
3797 $self->{'item_map'}{$id}{'defined'} = 1;
3798 $self->{'item_map'}{$id}{'prev'} = $prev;
3799 $self->{'item_map'}{$id}{'next'} = $items->[$i+1]->EffectiveId
3803 $self->{'item_map'}{'last'} = $prev
3804 if !$window || @$items < $window;
3809 Returns an a map of all items found by this search. The map is a hash
3813 first => <first ticket id found>,
3814 last => <last ticket id found or undef>,
3817 prev => <the ticket id found before>,
3818 next => <the ticket id found after>,
3830 $self->_BuildItemMap unless $self->{'item_map'};
3831 return $self->{'item_map'};
3837 =head2 PrepForSerialization
3839 You don't want to serialize a big tickets object, as
3840 the {items} hash will be instantly invalid _and_ eat
3845 sub PrepForSerialization {
3847 delete $self->{'items'};
3848 delete $self->{'items_array'};
3849 $self->RedoSearch();
3854 RT::Tickets supports several flags which alter search behavior:
3857 allow_deleted_search (Otherwise never show deleted tickets in search results)
3858 looking_at_type (otherwise limit to type=ticket)
3860 These flags are set by calling
3862 $tickets->{'flagname'} = 1;
3864 BUG: There should be an API for this
3874 Returns an empty new RT::Ticket item
3880 return(RT::Ticket->new($self->CurrentUser));
3882 RT::Base->_ImportOverlays();