1 # BEGIN BPS TAGGED BLOCK {{{
5 # This software is Copyright (c) 1996-2013 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', ], #loc_left_pair
146 CustomField => [ 'CUSTOMFIELD', ], #loc_left_pair
147 CF => [ 'CUSTOMFIELD', ], #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;
447 $value = RT::URI->new( $sb->CurrentUser )->CanonicalizeURI( $value );
450 my $direction = $meta->[1] || '';
451 my ($matchfield, $linkfield) = ('', '');
452 if ( $direction eq 'To' ) {
453 ($matchfield, $linkfield) = ("Target", "Base");
455 elsif ( $direction eq 'From' ) {
456 ($matchfield, $linkfield) = ("Base", "Target");
458 elsif ( $direction ) {
459 die "Invalid link direction '$direction' for $field\n";
462 $sb->_LinkLimit( 'LinkedTo', $op, $value, @rest );
464 'LinkedFrom', $op, $value, @rest,
465 ENTRYAGGREGATOR => (($is_negative && $is_null) || (!$is_null && !$is_negative))? 'OR': 'AND',
473 $op = ($op =~ /^(=|IS)$/i)? 'IS': 'IS NOT';
475 elsif ( $value =~ /\D/ ) {
478 $matchfield = "Local$matchfield" if $is_local;
480 #For doing a left join to find "unlinked tickets" we want to generate a query that looks like this
481 # SELECT main.* FROM Tickets main
482 # LEFT JOIN Links Links_1 ON ( (Links_1.Type = 'MemberOf')
483 # AND(main.id = Links_1.LocalTarget))
484 # WHERE Links_1.LocalBase IS NULL;
487 my $linkalias = $sb->Join(
492 FIELD2 => 'Local' . $linkfield
495 LEFTJOIN => $linkalias,
503 FIELD => $matchfield,
510 my $linkalias = $sb->Join(
515 FIELD2 => 'Local' . $linkfield
518 LEFTJOIN => $linkalias,
524 LEFTJOIN => $linkalias,
525 FIELD => $matchfield,
532 FIELD => $matchfield,
533 OPERATOR => $is_negative? 'IS': 'IS NOT',
542 Handle date fields. (Created, LastTold..)
545 1: type of link. (Probably not necessary.)
550 my ( $sb, $field, $op, $value, @rest ) = @_;
552 die "Invalid Date Op: $op"
553 unless $op =~ /^(=|>|<|>=|<=)$/;
555 my $meta = $FIELD_METADATA{$field};
556 die "Incorrect Meta Data for $field"
557 unless ( defined $meta->[1] );
559 $sb->_DateFieldLimit( $meta->[1], $op, $value, @rest );
562 # Factor this out for use by custom fields
564 sub _DateFieldLimit {
565 my ( $sb, $field, $op, $value, @rest ) = @_;
567 my $date = RT::Date->new( $sb->CurrentUser );
568 $date->Set( Format => 'unknown', Value => $value );
572 # if we're specifying =, that means we want everything on a
573 # particular single day. in the database, we need to check for >
574 # and < the edges of that day.
576 # Except if the value is 'this month' or 'last month', check
577 # > and < the edges of the month.
579 my ($daystart, $dayend);
580 if ( lc($value) eq 'this month' ) {
582 $date->SetToStart('month', Timezone => 'server');
583 $daystart = $date->ISO;
584 $date->AddMonth(Timezone => 'server');
585 $dayend = $date->ISO;
587 elsif ( lc($value) eq 'last month' ) {
589 $date->SetToStart('month', Timezone => 'server');
590 $dayend = $date->ISO;
592 $date->SetToStart('month', Timezone => 'server');
593 $daystart = $date->ISO;
596 $date->SetToMidnight( Timezone => 'server' );
597 $daystart = $date->ISO;
599 $dayend = $date->ISO;
616 ENTRYAGGREGATOR => 'AND',
634 Handle simple fields which are just strings. (Subject,Type)
642 my ( $sb, $field, $op, $value, @rest ) = @_;
646 # =, !=, LIKE, NOT LIKE
647 if ( RT->Config->Get('DatabaseType') eq 'Oracle'
648 && (!defined $value || !length $value)
649 && lc($op) ne 'is' && lc($op) ne 'is not'
651 if ($op eq '!=' || $op =~ /^NOT\s/i) {
668 =head2 _TransDateLimit
670 Handle fields limiting based on Transaction Date.
672 The inpupt value must be in a format parseable by Time::ParseDate
679 # This routine should really be factored into translimit.
680 sub _TransDateLimit {
681 my ( $sb, $field, $op, $value, @rest ) = @_;
683 # See the comments for TransLimit, they apply here too
685 my $txn_alias = $sb->JoinTransactions;
687 my $date = RT::Date->new( $sb->CurrentUser );
688 $date->Set( Format => 'unknown', Value => $value );
693 # if we're specifying =, that means we want everything on a
694 # particular single day. in the database, we need to check for >
695 # and < the edges of that day.
697 $date->SetToMidnight( Timezone => 'server' );
698 my $daystart = $date->ISO;
700 my $dayend = $date->ISO;
715 ENTRYAGGREGATOR => 'AND',
720 # not searching for a single day
723 #Search for the right field
738 Limit based on the ContentType or the Filename of a transaction.
743 my ( $self, $field, $op, $value, %rest ) = @_;
745 my $txn_alias = $self->JoinTransactions;
746 unless ( defined $self->{_sql_trattachalias} ) {
747 $self->{_sql_trattachalias} = $self->_SQLJoin(
748 TYPE => 'LEFT', # not all txns have an attachment
749 ALIAS1 => $txn_alias,
751 TABLE2 => 'Attachments',
752 FIELD2 => 'TransactionId',
758 ALIAS => $self->{_sql_trattachalias},
766 =head2 _TransContentLimit
768 Limit based on the Content of a transaction.
772 sub _TransContentLimit {
776 # If only this was this simple. We've got to do something
779 #Basically, we want to make sure that the limits apply to
780 #the same attachment, rather than just another attachment
781 #for the same ticket, no matter how many clauses we lump
782 #on. We put them in TicketAliases so that they get nuked
783 #when we redo the join.
785 # In the SQL, we might have
786 # (( Content = foo ) or ( Content = bar AND Content = baz ))
787 # The AND group should share the same Alias.
789 # Actually, maybe it doesn't matter. We use the same alias and it
790 # works itself out? (er.. different.)
792 # Steal more from _ProcessRestrictions
794 # FIXME: Maybe look at the previous FooLimit call, and if it was a
795 # TransLimit and EntryAggregator == AND, reuse the Aliases?
797 # Or better - store the aliases on a per subclause basis - since
798 # those are going to be the things we want to relate to each other,
801 # maybe we should not allow certain kinds of aggregation of these
802 # clauses and do a psuedo regex instead? - the problem is getting
803 # them all into the same subclause when you have (A op B op C) - the
804 # way they get parsed in the tree they're in different subclauses.
806 my ( $self, $field, $op, $value, %rest ) = @_;
807 $field = 'Content' if $field =~ /\W/;
809 my $config = RT->Config->Get('FullTextSearch') || {};
810 unless ( $config->{'Enable'} ) {
811 $self->_SQLLimit( %rest, FIELD => 'id', VALUE => 0 );
815 my $txn_alias = $self->JoinTransactions;
816 unless ( defined $self->{_sql_trattachalias} ) {
817 $self->{_sql_trattachalias} = $self->_SQLJoin(
818 TYPE => 'LEFT', # not all txns have an attachment
819 ALIAS1 => $txn_alias,
821 TABLE2 => 'Attachments',
822 FIELD2 => 'TransactionId',
827 if ( $config->{'Indexed'} ) {
828 my $db_type = RT->Config->Get('DatabaseType');
831 if ( $config->{'Table'} and $config->{'Table'} ne "Attachments") {
832 $alias = $self->{'_sql_aliases'}{'full_text'} ||= $self->_SQLJoin(
834 ALIAS1 => $self->{'_sql_trattachalias'},
836 TABLE2 => $config->{'Table'},
840 $alias = $self->{'_sql_trattachalias'};
843 #XXX: handle negative searches
844 my $index = $config->{'Column'};
845 if ( $db_type eq 'Oracle' ) {
846 my $dbh = $RT::Handle->dbh;
847 my $alias = $self->{_sql_trattachalias};
850 FUNCTION => "CONTAINS( $alias.$field, ".$dbh->quote($value) .")",
856 # this is required to trick DBIx::SB's LEFT JOINS optimizer
857 # into deciding that join is redundant as it is
859 ENTRYAGGREGATOR => 'AND',
860 ALIAS => $self->{_sql_trattachalias},
862 OPERATOR => 'IS NOT',
866 elsif ( $db_type eq 'Pg' ) {
867 my $dbh = $RT::Handle->dbh;
873 VALUE => 'plainto_tsquery('. $dbh->quote($value) .')',
877 elsif ( $db_type eq 'mysql' ) {
878 # XXX: We could theoretically skip the join to Attachments,
879 # and have Sphinx simply index and group by the TicketId,
880 # and join Ticket.id to that attribute, which would be much
881 # more efficient -- however, this is only a possibility if
882 # there are no other transaction limits.
884 # This is a special character. Note that \ does not escape
885 # itself (in Sphinx 2.1.0, at least), so 'foo\;bar' becoming
886 # 'foo\\;bar' is not a vulnerability, and is still parsed as
887 # "foo, \, ;, then bar". Happily, the default mode is
888 # "all", meaning that boolean operators are not special.
891 my $max = $config->{'MaxMatches'};
897 VALUE => "$value;limit=$max;maxmatches=$max",
903 ALIAS => $self->{_sql_trattachalias},
910 if ( RT->Config->Get('DontSearchFileAttachments') ) {
912 ENTRYAGGREGATOR => 'AND',
913 ALIAS => $self->{_sql_trattachalias},
924 Handle watcher limits. (Requestor, CC, etc..)
940 my $meta = $FIELD_METADATA{ $field };
941 my $type = $meta->[1] || '';
942 my $class = $meta->[2] || 'Ticket';
944 # Bail if the subfield is not allowed
946 and not grep { $_ eq $rest{SUBKEY} } @{$SEARCHABLE_SUBFIELDS{'User'}})
948 die "Invalid watcher subfield: '$rest{SUBKEY}'";
951 # if it's equality op and search by Email or Name then we can preload user
952 # we do it to help some DBs better estimate number of rows and get better plans
953 if ( $op =~ /^!?=$/ && (!$rest{'SUBKEY'} || $rest{'SUBKEY'} eq 'Name' || $rest{'SUBKEY'} eq 'EmailAddress') ) {
954 my $o = RT::User->new( $self->CurrentUser );
957 ? $field eq 'Owner'? 'Load' : 'LoadByEmail'
958 : $rest{'SUBKEY'} eq 'EmailAddress' ? 'LoadByEmail': 'Load';
959 $o->$method( $value );
960 $rest{'SUBKEY'} = 'id';
961 $value = $o->id || 0;
964 # Owner was ENUM field, so "Owner = 'xxx'" allowed user to
965 # search by id and Name at the same time, this is workaround
966 # to preserve backward compatibility
967 if ( $field eq 'Owner' ) {
968 if ( ($rest{'SUBKEY'}||'') eq 'id' ) {
978 $rest{SUBKEY} ||= 'EmailAddress';
980 my $groups = $self->_RoleGroupsJoin( Type => $type, Class => $class, New => !$type );
983 if ( $op =~ /^IS(?: NOT)?$/i ) {
984 # is [not] empty case
986 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
987 # to avoid joining the table Users into the query, we just join GM
988 # and make sure we don't match records where group is member of itself
990 LEFTJOIN => $group_members,
993 VALUE => "$group_members.MemberId",
997 ALIAS => $group_members,
1004 elsif ( $op =~ /^!=$|^NOT\s+/i ) {
1005 # negative condition case
1008 $op =~ s/!|NOT\s+//i;
1010 # XXX: we have no way to build correct "Watcher.X != 'Y'" when condition
1011 # "X = 'Y'" matches more then one user so we try to fetch two records and
1012 # do the right thing when there is only one exist and semi-working solution
1014 my $users_obj = RT::Users->new( $self->CurrentUser );
1016 FIELD => $rest{SUBKEY},
1020 $users_obj->OrderBy;
1021 $users_obj->RowsPerPage(2);
1022 my @users = @{ $users_obj->ItemsArrayRef };
1024 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
1025 if ( @users <= 1 ) {
1027 $uid = $users[0]->id if @users;
1028 $self->SUPER::Limit(
1029 LEFTJOIN => $group_members,
1030 ALIAS => $group_members,
1031 FIELD => 'MemberId',
1036 ALIAS => $group_members,
1042 $self->SUPER::Limit(
1043 LEFTJOIN => $group_members,
1046 VALUE => "$group_members.MemberId",
1049 my $users = $self->Join(
1051 ALIAS1 => $group_members,
1052 FIELD1 => 'MemberId',
1056 $self->SUPER::Limit(
1059 FIELD => $rest{SUBKEY},
1073 # positive condition case
1075 my $group_members = $self->_GroupMembersJoin(
1076 GroupsAlias => $groups, New => 1, Left => 0
1078 my $users = $self->Join(
1080 ALIAS1 => $group_members,
1081 FIELD1 => 'MemberId',
1088 FIELD => $rest{'SUBKEY'},
1097 sub _RoleGroupsJoin {
1099 my %args = (New => 0, Class => 'Ticket', Type => '', @_);
1100 return $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1101 if $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1104 # we always have watcher groups for ticket, so we use INNER join
1105 my $groups = $self->Join(
1107 FIELD1 => $args{'Class'} eq 'Queue'? 'Queue': 'id',
1109 FIELD2 => 'Instance',
1110 ENTRYAGGREGATOR => 'AND',
1112 $self->SUPER::Limit(
1113 LEFTJOIN => $groups,
1116 VALUE => 'RT::'. $args{'Class'} .'-Role',
1118 $self->SUPER::Limit(
1119 LEFTJOIN => $groups,
1122 VALUE => $args{'Type'},
1125 $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} } = $groups
1126 unless $args{'New'};
1131 sub _GroupMembersJoin {
1133 my %args = (New => 1, GroupsAlias => undef, Left => 1, @_);
1135 return $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1136 if $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1139 my $alias = $self->Join(
1140 $args{'Left'} ? (TYPE => 'LEFT') : (),
1141 ALIAS1 => $args{'GroupsAlias'},
1143 TABLE2 => 'CachedGroupMembers',
1144 FIELD2 => 'GroupId',
1145 ENTRYAGGREGATOR => 'AND',
1147 $self->SUPER::Limit(
1148 $args{'Left'} ? (LEFTJOIN => $alias) : (),
1150 FIELD => 'Disabled',
1154 $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} } = $alias
1155 unless $args{'New'};
1162 Helper function which provides joins to a watchers table both for limits
1169 my $type = shift || '';
1172 my $groups = $self->_RoleGroupsJoin( Type => $type );
1173 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
1174 # XXX: work around, we must hide groups that
1175 # are members of the role group we search in,
1176 # otherwise them result in wrong NULLs in Users
1177 # table and break ordering. Now, we know that
1178 # RT doesn't allow to add groups as members of the
1179 # ticket roles, so we just hide entries in CGM table
1180 # with MemberId == GroupId from results
1181 $self->SUPER::Limit(
1182 LEFTJOIN => $group_members,
1185 VALUE => "$group_members.MemberId",
1188 my $users = $self->Join(
1190 ALIAS1 => $group_members,
1191 FIELD1 => 'MemberId',
1195 return ($groups, $group_members, $users);
1198 =head2 _WatcherMembershipLimit
1200 Handle watcher membership limits, i.e. whether the watcher belongs to a
1201 specific group or not.
1204 1: Field to query on
1206 SELECT DISTINCT main.*
1210 CachedGroupMembers CachedGroupMembers_2,
1213 (main.EffectiveId = main.id)
1215 (main.Status != 'deleted')
1217 (main.Type = 'ticket')
1220 (Users_3.EmailAddress = '22')
1222 (Groups_1.Domain = 'RT::Ticket-Role')
1224 (Groups_1.Type = 'RequestorGroup')
1227 Groups_1.Instance = main.id
1229 Groups_1.id = CachedGroupMembers_2.GroupId
1231 CachedGroupMembers_2.MemberId = Users_3.id
1232 ORDER BY main.id ASC
1237 sub _WatcherMembershipLimit {
1238 my ( $self, $field, $op, $value, @rest ) = @_;
1243 my $groups = $self->NewAlias('Groups');
1244 my $groupmembers = $self->NewAlias('CachedGroupMembers');
1245 my $users = $self->NewAlias('Users');
1246 my $memberships = $self->NewAlias('CachedGroupMembers');
1248 if ( ref $field ) { # gross hack
1249 my @bundle = @$field;
1251 for my $chunk (@bundle) {
1252 ( $field, $op, $value, @rest ) = @$chunk;
1254 ALIAS => $memberships,
1265 ALIAS => $memberships,
1273 # Tie to groups for tickets we care about
1277 VALUE => 'RT::Ticket-Role',
1278 ENTRYAGGREGATOR => 'AND'
1283 FIELD1 => 'Instance',
1290 # If we care about which sort of watcher
1291 my $meta = $FIELD_METADATA{$field};
1292 my $type = ( defined $meta->[1] ? $meta->[1] : undef );
1299 ENTRYAGGREGATOR => 'AND'
1306 ALIAS2 => $groupmembers,
1311 ALIAS1 => $groupmembers,
1312 FIELD1 => 'MemberId',
1318 ALIAS => $groupmembers,
1319 FIELD => 'Disabled',
1324 ALIAS1 => $memberships,
1325 FIELD1 => 'MemberId',
1331 ALIAS => $memberships,
1332 FIELD => 'Disabled',
1341 =head2 _CustomFieldDecipher
1343 Try and turn a CF descriptor into (cfid, cfname) object pair.
1347 sub _CustomFieldDecipher {
1348 my ($self, $string) = @_;
1350 my ($queue, $field, $column) = ($string =~ /^(?:(.+?)\.)?{(.+)}(?:\.(Content|LargeContent))?$/);
1351 $field ||= ($string =~ /^{(.*?)}$/)[0] || $string;
1355 my $q = RT::Queue->new( $self->CurrentUser );
1359 # $queue = $q->Name; # should we normalize the queue?
1360 $cf = $q->CustomField( $field );
1363 $RT::Logger->warning("Queue '$queue' doesn't exist, parsed from '$string'");
1367 elsif ( $field =~ /\D/ ) {
1369 my $cfs = RT::CustomFields->new( $self->CurrentUser );
1370 $cfs->Limit( FIELD => 'Name', VALUE => $field );
1371 $cfs->LimitToLookupType('RT::Queue-RT::Ticket');
1373 # if there is more then one field the current user can
1374 # see with the same name then we shouldn't return cf object
1375 # as we don't know which one to use
1378 $cf = undef if $cfs->Next;
1382 $cf = RT::CustomField->new( $self->CurrentUser );
1383 $cf->Load( $field );
1386 return ($queue, $field, $cf, $column);
1389 =head2 _CustomFieldJoin
1391 Factor out the Join of custom fields so we can use it for sorting too
1395 sub _CustomFieldJoin {
1396 my ($self, $cfkey, $cfid, $field) = @_;
1397 # Perform one Join per CustomField
1398 if ( $self->{_sql_object_cfv_alias}{$cfkey} ||
1399 $self->{_sql_cf_alias}{$cfkey} )
1401 return ( $self->{_sql_object_cfv_alias}{$cfkey},
1402 $self->{_sql_cf_alias}{$cfkey} );
1405 my ($TicketCFs, $CFs);
1407 $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1411 TABLE2 => 'ObjectCustomFieldValues',
1412 FIELD2 => 'ObjectId',
1414 $self->SUPER::Limit(
1415 LEFTJOIN => $TicketCFs,
1416 FIELD => 'CustomField',
1418 ENTRYAGGREGATOR => 'AND'
1422 my $ocfalias = $self->Join(
1425 TABLE2 => 'ObjectCustomFields',
1426 FIELD2 => 'ObjectId',
1429 $self->SUPER::Limit(
1430 LEFTJOIN => $ocfalias,
1431 ENTRYAGGREGATOR => 'OR',
1432 FIELD => 'ObjectId',
1436 $CFs = $self->{_sql_cf_alias}{$cfkey} = $self->Join(
1438 ALIAS1 => $ocfalias,
1439 FIELD1 => 'CustomField',
1440 TABLE2 => 'CustomFields',
1443 $self->SUPER::Limit(
1445 ENTRYAGGREGATOR => 'AND',
1446 FIELD => 'LookupType',
1447 VALUE => 'RT::Queue-RT::Ticket',
1449 $self->SUPER::Limit(
1451 ENTRYAGGREGATOR => 'AND',
1456 $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1460 TABLE2 => 'ObjectCustomFieldValues',
1461 FIELD2 => 'CustomField',
1463 $self->SUPER::Limit(
1464 LEFTJOIN => $TicketCFs,
1465 FIELD => 'ObjectId',
1468 ENTRYAGGREGATOR => 'AND',
1471 $self->SUPER::Limit(
1472 LEFTJOIN => $TicketCFs,
1473 FIELD => 'ObjectType',
1474 VALUE => 'RT::Ticket',
1475 ENTRYAGGREGATOR => 'AND'
1477 $self->SUPER::Limit(
1478 LEFTJOIN => $TicketCFs,
1479 FIELD => 'Disabled',
1482 ENTRYAGGREGATOR => 'AND'
1485 return ($TicketCFs, $CFs);
1488 =head2 _CustomFieldLimit
1490 Limit based on CustomFields
1497 use Regexp::Common qw(RE_net_IPv4);
1498 use Regexp::Common::net::CIDR;
1501 sub _CustomFieldLimit {
1502 my ( $self, $_field, $op, $value, %rest ) = @_;
1504 my $field = $rest{'SUBKEY'} || die "No field specified";
1506 # For our sanity, we can only limit on one queue at a time
1508 my ($queue, $cfid, $cf, $column);
1509 ($queue, $field, $cf, $column) = $self->_CustomFieldDecipher( $field );
1510 $cfid = $cf ? $cf->id : 0 ;
1512 # If we're trying to find custom fields that don't match something, we
1513 # want tickets where the custom field has no value at all. Note that
1514 # we explicitly don't include the "IS NULL" case, since we would
1515 # otherwise end up with a redundant clause.
1517 my ($negative_op, $null_op, $inv_op, $range_op)
1518 = $self->ClassifySQLOperation( $op );
1521 return @_ unless RT->Config->Get('DatabaseType') eq 'Oracle';
1524 return %args unless $args{'FIELD'} eq 'LargeContent';
1526 my $op = $args{'OPERATOR'};
1528 $args{'OPERATOR'} = 'MATCHES';
1530 elsif ( $op eq '!=' ) {
1531 $args{'OPERATOR'} = 'NOT MATCHES';
1533 elsif ( $op =~ /^[<>]=?$/ ) {
1534 $args{'FUNCTION'} = "TO_CHAR( $args{'ALIAS'}.LargeContent )";
1539 if ( $cf && $cf->Type eq 'IPAddress' ) {
1540 my $parsed = RT::ObjectCustomFieldValue->ParseIP($value);
1545 $RT::Logger->warn("$value is not a valid IPAddress");
1549 if ( $cf && $cf->Type eq 'IPAddressRange' ) {
1551 if ( $value =~ /^\s*$RE{net}{CIDR}{IPv4}{-keep}\s*$/o ) {
1553 # convert incomplete 192.168/24 to 192.168.0.0/24 format
1555 join( '.', map $_ || 0, ( split /\./, $1 )[ 0 .. 3 ] ) . "/$2"
1559 my ( $start_ip, $end_ip ) =
1560 RT::ObjectCustomFieldValue->ParseIPRange($value);
1561 if ( $start_ip && $end_ip ) {
1562 if ( $op =~ /^([<>])=?$/ ) {
1563 my $is_less = $1 eq '<' ? 1 : 0;
1572 $value = join '-', $start_ip, $end_ip;
1576 $RT::Logger->warn("$value is not a valid IPAddressRange");
1580 if ( $cf && $cf->Type =~ /^Date(?:Time)?$/ ) {
1581 my $date = RT::Date->new( $self->CurrentUser );
1582 $date->Set( Format => 'unknown', Value => $value );
1583 if ( $date->Unix ) {
1587 || $value =~ /^\s*(?:today|tomorrow|yesterday)\s*$/i
1588 || ( $value !~ /midnight|\d+:\d+:\d+/i
1589 && $date->Time( Timezone => 'user' ) eq '00:00:00' )
1592 $value = $date->Date( Timezone => 'user' );
1595 $value = $date->DateTime;
1599 $RT::Logger->warn("$value is not a valid date string");
1603 my $single_value = !$cf || !$cfid || $cf->SingleValue;
1605 my $cfkey = $cfid ? $cfid : "$queue.$field";
1607 if ( $null_op && !$column ) {
1608 # IS[ NOT] NULL without column is the same as has[ no] any CF value,
1609 # we can reuse our default joins for this operation
1610 # with column specified we have different situation
1611 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1614 ALIAS => $TicketCFs,
1623 OPERATOR => 'IS NOT',
1626 ENTRYAGGREGATOR => 'AND',
1630 elsif ( $op !~ /^[<>]=?$/ && ( $cf && $cf->Type eq 'IPAddressRange')) {
1632 my ($start_ip, $end_ip) = split /-/, $value;
1635 if ( $op !~ /NOT|!=|<>/i ) { # positive equation
1636 $self->_CustomFieldLimit(
1637 'CF', '<=', $end_ip, %rest,
1638 SUBKEY => $rest{'SUBKEY'}. '.Content',
1640 $self->_CustomFieldLimit(
1641 'CF', '>=', $start_ip, %rest,
1642 SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1643 ENTRYAGGREGATOR => 'AND',
1645 # as well limit borders so DB optimizers can use better
1646 # estimations and scan less rows
1647 # have to disable this tweak because of ipv6
1648 # $self->_CustomFieldLimit(
1649 # $field, '>=', '000.000.000.000', %rest,
1650 # SUBKEY => $rest{'SUBKEY'}. '.Content',
1651 # ENTRYAGGREGATOR => 'AND',
1653 # $self->_CustomFieldLimit(
1654 # $field, '<=', '255.255.255.255', %rest,
1655 # SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1656 # ENTRYAGGREGATOR => 'AND',
1659 else { # negative equation
1660 $self->_CustomFieldLimit($field, '>', $end_ip, %rest);
1661 $self->_CustomFieldLimit(
1662 $field, '<', $start_ip, %rest,
1663 SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1664 ENTRYAGGREGATOR => 'OR',
1666 # TODO: as well limit borders so DB optimizers can use better
1667 # estimations and scan less rows, but it's harder to do
1668 # as we have OR aggregator
1672 elsif ( !$negative_op || $single_value ) {
1673 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++ if !$single_value && !$range_op;
1674 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1681 # if column is defined then deal only with it
1682 # otherwise search in Content and in LargeContent
1684 $self->_SQLLimit( $fix_op->(
1685 ALIAS => $TicketCFs,
1697 # need special treatment for Date
1698 if ( $cf and $cf->Type eq 'DateTime' and $op eq '=' && $value !~ /:/ ) {
1699 # no time specified, that means we want everything on a
1700 # particular day. in the database, we need to check for >
1701 # and < the edges of that day.
1702 my $date = RT::Date->new( $self->CurrentUser );
1703 $date->Set( Format => 'unknown', Value => $value );
1704 my $daystart = $date->ISO;
1706 my $dayend = $date->ISO;
1711 ALIAS => $TicketCFs,
1719 ALIAS => $TicketCFs,
1724 ENTRYAGGREGATOR => 'AND',
1729 elsif ( $op eq '=' || $op eq '!=' || $op eq '<>' ) {
1730 if ( length( Encode::encode_utf8($value) ) < 256 ) {
1732 ALIAS => $TicketCFs,
1743 ALIAS => $TicketCFs,
1747 ENTRYAGGREGATOR => 'OR'
1750 ALIAS => $TicketCFs,
1754 ENTRYAGGREGATOR => 'OR'
1757 $self->_SQLLimit( $fix_op->(
1758 ALIAS => $TicketCFs,
1759 FIELD => 'LargeContent',
1762 ENTRYAGGREGATOR => 'AND',
1769 ALIAS => $TicketCFs,
1780 ALIAS => $TicketCFs,
1784 ENTRYAGGREGATOR => 'OR'
1787 ALIAS => $TicketCFs,
1791 ENTRYAGGREGATOR => 'OR'
1794 $self->_SQLLimit( $fix_op->(
1795 ALIAS => $TicketCFs,
1796 FIELD => 'LargeContent',
1799 ENTRYAGGREGATOR => 'AND',
1806 # XXX: if we join via CustomFields table then
1807 # because of order of left joins we get NULLs in
1808 # CF table and then get nulls for those records
1809 # in OCFVs table what result in wrong results
1810 # as decifer method now tries to load a CF then
1811 # we fall into this situation only when there
1812 # are more than one CF with the name in the DB.
1813 # the same thing applies to order by call.
1814 # TODO: reorder joins T <- OCFVs <- CFs <- OCFs if
1815 # we want treat IS NULL as (not applies or has
1820 OPERATOR => 'IS NOT',
1823 ENTRYAGGREGATOR => 'AND',
1829 ALIAS => $TicketCFs,
1830 FIELD => $column || 'Content',
1834 ENTRYAGGREGATOR => 'OR',
1842 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++;
1843 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1846 $op =~ s/!|NOT\s+//i;
1848 # if column is defined then deal only with it
1849 # otherwise search in Content and in LargeContent
1851 $self->SUPER::Limit( $fix_op->(
1852 LEFTJOIN => $TicketCFs,
1853 ALIAS => $TicketCFs,
1861 $self->SUPER::Limit(
1862 LEFTJOIN => $TicketCFs,
1863 ALIAS => $TicketCFs,
1872 ALIAS => $TicketCFs,
1881 sub _HasAttributeLimit {
1882 my ( $self, $field, $op, $value, %rest ) = @_;
1884 my $alias = $self->Join(
1888 TABLE2 => 'Attributes',
1889 FIELD2 => 'ObjectId',
1891 $self->SUPER::Limit(
1893 FIELD => 'ObjectType',
1894 VALUE => 'RT::Ticket',
1895 ENTRYAGGREGATOR => 'AND'
1897 $self->SUPER::Limit(
1902 ENTRYAGGREGATOR => 'AND'
1908 OPERATOR => $FIELD_METADATA{$field}->[1]? 'IS NOT': 'IS',
1914 # End Helper Functions
1916 # End of SQL Stuff -------------------------------------------------
1919 =head2 OrderByCols ARRAY
1921 A modified version of the OrderBy method which automatically joins where
1922 C<ALIAS> is set to the name of a watcher type.
1933 foreach my $row (@args) {
1934 if ( $row->{ALIAS} ) {
1938 if ( $row->{FIELD} !~ /\./ ) {
1939 my $meta = $self->FIELDS->{ $row->{FIELD} };
1945 if ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'Queue' ) {
1946 my $alias = $self->Join(
1949 FIELD1 => $row->{'FIELD'},
1953 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1954 } elsif ( ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'User' )
1955 || ( $meta->[0] eq 'WATCHERFIELD' && ($meta->[1]||'') eq 'Owner' )
1957 my $alias = $self->Join(
1960 FIELD1 => $row->{'FIELD'},
1964 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1971 my ( $field, $subkey ) = split /\./, $row->{FIELD}, 2;
1972 my $meta = $self->FIELDS->{$field};
1973 if ( defined $meta->[0] && $meta->[0] eq 'WATCHERFIELD' ) {
1974 # cache alias as we want to use one alias per watcher type for sorting
1975 my $users = $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] };
1977 $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] }
1978 = $users = ( $self->_WatcherJoin( $meta->[1] ) )[2];
1980 push @res, { %$row, ALIAS => $users, FIELD => $subkey };
1981 } elsif ( defined $meta->[0] && $meta->[0] eq 'CUSTOMFIELD' ) {
1982 my ($queue, $field, $cf_obj, $column) = $self->_CustomFieldDecipher( $subkey );
1983 my $cfkey = $cf_obj ? $cf_obj->id : "$queue.$field";
1984 $cfkey .= ".ordering" if !$cf_obj || ($cf_obj->MaxValues||0) != 1;
1985 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, ($cf_obj ?$cf_obj->id :0) , $field );
1986 # this is described in _CustomFieldLimit
1990 OPERATOR => 'IS NOT',
1993 ENTRYAGGREGATOR => 'AND',
1996 # For those cases where we are doing a join against the
1997 # CF name, and don't have a CFid, use Unique to make sure
1998 # we don't show duplicate tickets. NOTE: I'm pretty sure
1999 # this will stay mixed in for the life of the
2000 # class/package, and not just for the life of the object.
2001 # Potential performance issue.
2002 require DBIx::SearchBuilder::Unique;
2003 DBIx::SearchBuilder::Unique->import;
2005 my $CFvs = $self->Join(
2007 ALIAS1 => $TicketCFs,
2008 FIELD1 => 'CustomField',
2009 TABLE2 => 'CustomFieldValues',
2010 FIELD2 => 'CustomField',
2012 $self->SUPER::Limit(
2016 VALUE => $TicketCFs . ".Content",
2017 ENTRYAGGREGATOR => 'AND'
2020 push @res, { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' };
2021 push @res, { %$row, ALIAS => $TicketCFs, FIELD => 'Content' };
2022 } elsif ( $field eq "Custom" && $subkey eq "Ownership") {
2023 # PAW logic is "reversed"
2025 if (exists $row->{ORDER} ) {
2026 my $o = $row->{ORDER};
2027 delete $row->{ORDER};
2028 $order = "DESC" if $o =~ /asc/i;
2031 # Ticket.Owner 1 0 X
2032 # Unowned Tickets 0 1 X
2035 foreach my $uid ( $self->CurrentUser->Id, RT->Nobody->Id ) {
2036 if ( RT->Config->Get('DatabaseType') eq 'Oracle' ) {
2037 my $f = ($row->{'ALIAS'} || 'main') .'.Owner';
2042 FUNCTION => "CASE WHEN $f=$uid THEN 1 ELSE 0 END",
2049 FUNCTION => "Owner=$uid",
2055 push @res, { %$row, FIELD => "Priority", ORDER => $order } ;
2057 } elsif ( $field eq 'Customer' ) { #Freeside
2058 # OrderBy(FIELD => expression) doesn't work, it has to be
2059 # an actual field, so we have to do the join even if sorting
2061 my $custalias = $self->JoinToCustomer;
2062 my $cust_field = lc($subkey);
2063 if ( !$cust_field or $cust_field eq 'number' ) {
2064 $cust_field = 'custnum';
2066 elsif ( $cust_field eq 'name' ) {
2067 $cust_field = "COALESCE( $custalias.company,
2068 $custalias.last || ', ' || $custalias.first
2071 else { # order by cust_main fields directly: 'Customer.agentnum'
2072 $cust_field = $subkey;
2074 push @res, { %$row, ALIAS => $custalias, FIELD => $cust_field };
2076 } elsif ( $field eq 'Service' ) {
2078 my $svcalias = $self->JoinToService;
2079 my $svc_field = lc($subkey);
2080 if ( !$svc_field or $svc_field eq 'number' ) {
2081 $svc_field = 'svcnum';
2083 push @res, { %$row, ALIAS => $svcalias, FIELD => $svc_field };
2091 return $self->SUPER::OrderByCols(@res);
2096 sub JoinToCustLinks {
2097 # Set up join to links (id = localbase),
2098 # limit link type to 'MemberOf',
2099 # and target value to any Freeside custnum URI.
2100 # Return the linkalias for further join/limit action,
2101 # and an sql expression to retrieve the custnum.
2103 # only join once for each RT::Tickets object
2104 my $linkalias = $self->{cust_main_linkalias};
2106 $linkalias = $self->Join(
2111 FIELD2 => 'LocalBase',
2113 $self->SUPER::Limit(
2114 LEFTJOIN => $linkalias,
2117 VALUE => 'fsck.com-rt://%/ticket/%',
2119 $self->SUPER::Limit(
2120 LEFTJOIN => $linkalias,
2123 VALUE => 'MemberOf',
2125 $self->SUPER::Limit(
2126 LEFTJOIN => $linkalias,
2128 OPERATOR => 'STARTSWITH',
2129 VALUE => 'freeside://freeside/cust_main/',
2131 $self->{cust_main_linkalias} = $linkalias;
2133 my $custnum_sql = "CAST(SUBSTR($linkalias.Target,31) AS ";
2134 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
2135 $custnum_sql .= 'SIGNED INTEGER)';
2138 $custnum_sql .= 'INTEGER)';
2140 return ($linkalias, $custnum_sql);
2143 sub JoinToCustomer {
2145 my ($linkalias, $custnum_sql) = $self->JoinToCustLinks;
2146 # don't reuse this join, though--negative queries need
2148 my $custalias = $self->Join(
2150 EXPRESSION => $custnum_sql,
2151 TABLE2 => 'cust_main',
2152 FIELD2 => 'custnum',
2157 sub JoinToSvcLinks {
2159 my $linkalias = $self->{cust_svc_linkalias};
2161 $linkalias = $self->Join(
2166 FIELD2 => 'LocalBase',
2168 $self->SUPER::Limit(
2169 LEFTJOIN => $linkalias,
2172 VALUE => 'fsck.com-rt://%/ticket/%',
2175 $self->SUPER::Limit(
2176 LEFTJOIN => $linkalias,
2179 VALUE => 'MemberOf',
2181 $self->SUPER::Limit(
2182 LEFTJOIN => $linkalias,
2184 OPERATOR => 'STARTSWITH',
2185 VALUE => 'freeside://freeside/cust_svc/',
2187 $self->{cust_svc_linkalias} = $linkalias;
2189 my $svcnum_sql = "CAST(SUBSTR($linkalias.Target,30) AS ";
2190 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
2191 $svcnum_sql .= 'SIGNED INTEGER)';
2194 $svcnum_sql .= 'INTEGER)';
2196 return ($linkalias, $svcnum_sql);
2201 my ($linkalias, $svcnum_sql) = $self->JoinToSvcLinks;
2204 EXPRESSION => $svcnum_sql,
2205 TABLE2 => 'cust_svc',
2210 # This creates an alternate left join path to cust_main via cust_svc.
2211 # _FreesideFieldLimit needs to add this as a separate, independent join
2212 # and include all tickets that have a matching cust_main record via
2214 sub JoinToCustomerViaService {
2216 my $svcalias = $self->JoinToService;
2217 my $cust_pkg = $self->Join(
2219 ALIAS1 => $svcalias,
2221 TABLE2 => 'cust_pkg',
2224 my $cust_main = $self->Join(
2226 ALIAS1 => $cust_pkg,
2227 FIELD1 => 'custnum',
2228 TABLE2 => 'cust_main',
2229 FIELD2 => 'custnum',
2234 sub _FreesideFieldLimit {
2235 my ( $self, $field, $op, $value, %rest ) = @_;
2236 my $is_negative = 0;
2237 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
2238 # if the op is negative, do the join as though
2239 # the op were positive, then accept only records
2240 # where the right-side join key is null.
2242 $op = '=' if $op eq '!=';
2246 my (@alias, $table2, $subfield, $pkey);
2247 if ( $field eq 'Customer' ) {
2248 push @alias, $self->JoinToCustomer;
2249 push @alias, $self->JoinToCustomerViaService;
2252 elsif ( $field eq 'Service' ) {
2253 push @alias, $self->JoinToService;
2257 die "malformed Freeside query: $field";
2260 $subfield = $rest{SUBKEY} || $pkey;
2261 # compound subkey: separate into table name and field in that table
2262 # (must be linked by custnum)
2263 $subfield = lc($subfield);
2264 ($table2, $subfield) = ($1, $2) if $subfield =~ /^(\w+)?\.(\w+)$/;
2265 $subfield = $pkey if $subfield eq 'number';
2267 # if it's compound, create a join from cust_main or cust_svc to that
2268 # table, using custnum or svcnum, and Limit on that table instead.
2270 foreach my $a (@alias) {
2281 # do the actual Limit
2282 $self->SUPER::Limit(
2287 ENTRYAGGREGATOR => 'AND',
2288 # no SUBCLAUSE needed, limits on different aliases across left joins
2289 # are inherently independent
2292 # then, since it's a left join, exclude tickets for which there is now
2293 # no matching record in the table we just limited on. (Or where there
2294 # is a matching record, if $is_negative.)
2295 # For a cust_main query (where there are two different aliases), this
2296 # will produce a subclause: "cust_main_1.custnum IS NOT NULL OR
2297 # cust_main_2.custnum IS NOT NULL" (or "IS NULL AND..." for a negative
2304 OPERATOR => $is_negative ? 'IS' : 'IS NOT',
2307 ENTRYAGGREGATOR => $is_negative ? 'AND' : 'OR',
2308 SUBCLAUSE => 'fs_limit',
2313 foreach my $_SQLLimit (@_SQLLimit) {
2314 $self->_SQLLimit( %$_SQLLimit);
2324 Takes a paramhash with the fields FIELD, OPERATOR, VALUE and DESCRIPTION
2325 Generally best called from LimitFoo methods
2335 DESCRIPTION => undef,
2338 $args{'DESCRIPTION'} = $self->loc(
2339 "[_1] [_2] [_3]", $args{'FIELD'},
2340 $args{'OPERATOR'}, $args{'VALUE'}
2342 if ( !defined $args{'DESCRIPTION'} );
2344 my $index = $self->_NextIndex;
2346 # make the TicketRestrictions hash the equivalent of whatever we just passed in;
2348 %{ $self->{'TicketRestrictions'}{$index} } = %args;
2350 $self->{'RecalcTicketLimits'} = 1;
2352 # If we're looking at the effective id, we don't want to append the other clause
2353 # which limits us to tickets where id = effective id
2354 if ( $args{'FIELD'} eq 'EffectiveId'
2355 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
2357 $self->{'looking_at_effective_id'} = 1;
2360 if ( $args{'FIELD'} eq 'Type'
2361 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
2363 $self->{'looking_at_type'} = 1;
2374 LimitQueue takes a paramhash with the fields OPERATOR and VALUE.
2375 OPERATOR is one of = or !=. (It defaults to =).
2376 VALUE is a queue id or Name.
2389 #TODO VALUE should also take queue objects
2390 if ( defined $args{'VALUE'} && $args{'VALUE'} !~ /^\d+$/ ) {
2391 my $queue = RT::Queue->new( $self->CurrentUser );
2392 $queue->Load( $args{'VALUE'} );
2393 $args{'VALUE'} = $queue->Id;
2396 # What if they pass in an Id? Check for isNum() and convert to
2399 #TODO check for a valid queue here
2403 VALUE => $args{'VALUE'},
2404 OPERATOR => $args{'OPERATOR'},
2405 DESCRIPTION => join(
2406 ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{'VALUE'},
2416 Takes a paramhash with the fields OPERATOR and VALUE.
2417 OPERATOR is one of = or !=.
2420 RT adds Status != 'deleted' until object has
2421 allow_deleted_search internal property set.
2422 $tickets->{'allow_deleted_search'} = 1;
2423 $tickets->LimitStatus( VALUE => 'deleted' );
2435 VALUE => $args{'VALUE'},
2436 OPERATOR => $args{'OPERATOR'},
2437 DESCRIPTION => join( ' ',
2438 $self->loc('Status'), $args{'OPERATOR'},
2439 $self->loc( $args{'VALUE'} ) ),
2447 If called, this search will not automatically limit the set of results found
2448 to tickets of type "Ticket". Tickets of other types, such as "project" and
2449 "approval" will be found.
2456 # Instead of faking a Limit that later gets ignored, fake up the
2457 # fact that we're already looking at type, so that the check in
2458 # Tickets_SQL/FromSQL goes down the right branch
2460 # $self->LimitType(VALUE => '__any');
2461 $self->{looking_at_type} = 1;
2468 Takes a paramhash with the fields OPERATOR and VALUE.
2469 OPERATOR is one of = or !=, it defaults to "=".
2470 VALUE is a string to search for in the type of the ticket.
2485 VALUE => $args{'VALUE'},
2486 OPERATOR => $args{'OPERATOR'},
2487 DESCRIPTION => join( ' ',
2488 $self->loc('Type'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2498 Takes a paramhash with the fields OPERATOR and VALUE.
2499 OPERATOR is one of = or !=.
2500 VALUE is a string to search for in the subject of the ticket.
2509 VALUE => $args{'VALUE'},
2510 OPERATOR => $args{'OPERATOR'},
2511 DESCRIPTION => join( ' ',
2512 $self->loc('Subject'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2518 # Things that can be > < = !=
2523 Takes a paramhash with the fields OPERATOR and VALUE.
2524 OPERATOR is one of =, >, < or !=.
2525 VALUE is a ticket Id to search for
2538 VALUE => $args{'VALUE'},
2539 OPERATOR => $args{'OPERATOR'},
2541 join( ' ', $self->loc('Id'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2547 =head2 LimitPriority
2549 Takes a paramhash with the fields OPERATOR and VALUE.
2550 OPERATOR is one of =, >, < or !=.
2551 VALUE is a value to match the ticket's priority against
2559 FIELD => 'Priority',
2560 VALUE => $args{'VALUE'},
2561 OPERATOR => $args{'OPERATOR'},
2562 DESCRIPTION => join( ' ',
2563 $self->loc('Priority'),
2564 $args{'OPERATOR'}, $args{'VALUE'}, ),
2570 =head2 LimitInitialPriority
2572 Takes a paramhash with the fields OPERATOR and VALUE.
2573 OPERATOR is one of =, >, < or !=.
2574 VALUE is a value to match the ticket's initial priority against
2579 sub LimitInitialPriority {
2583 FIELD => 'InitialPriority',
2584 VALUE => $args{'VALUE'},
2585 OPERATOR => $args{'OPERATOR'},
2586 DESCRIPTION => join( ' ',
2587 $self->loc('Initial Priority'), $args{'OPERATOR'},
2594 =head2 LimitFinalPriority
2596 Takes a paramhash with the fields OPERATOR and VALUE.
2597 OPERATOR is one of =, >, < or !=.
2598 VALUE is a value to match the ticket's final priority against
2602 sub LimitFinalPriority {
2606 FIELD => 'FinalPriority',
2607 VALUE => $args{'VALUE'},
2608 OPERATOR => $args{'OPERATOR'},
2609 DESCRIPTION => join( ' ',
2610 $self->loc('Final Priority'), $args{'OPERATOR'},
2617 =head2 LimitTimeWorked
2619 Takes a paramhash with the fields OPERATOR and VALUE.
2620 OPERATOR is one of =, >, < or !=.
2621 VALUE is a value to match the ticket's TimeWorked attribute
2625 sub LimitTimeWorked {
2629 FIELD => 'TimeWorked',
2630 VALUE => $args{'VALUE'},
2631 OPERATOR => $args{'OPERATOR'},
2632 DESCRIPTION => join( ' ',
2633 $self->loc('Time Worked'),
2634 $args{'OPERATOR'}, $args{'VALUE'}, ),
2640 =head2 LimitTimeLeft
2642 Takes a paramhash with the fields OPERATOR and VALUE.
2643 OPERATOR is one of =, >, < or !=.
2644 VALUE is a value to match the ticket's TimeLeft attribute
2652 FIELD => 'TimeLeft',
2653 VALUE => $args{'VALUE'},
2654 OPERATOR => $args{'OPERATOR'},
2655 DESCRIPTION => join( ' ',
2656 $self->loc('Time Left'),
2657 $args{'OPERATOR'}, $args{'VALUE'}, ),
2667 Takes a paramhash with the fields OPERATOR and VALUE.
2668 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2669 VALUE is a string to search for in the body of the ticket
2678 VALUE => $args{'VALUE'},
2679 OPERATOR => $args{'OPERATOR'},
2680 DESCRIPTION => join( ' ',
2681 $self->loc('Ticket content'), $args{'OPERATOR'},
2688 =head2 LimitFilename
2690 Takes a paramhash with the fields OPERATOR and VALUE.
2691 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2692 VALUE is a string to search for in the body of the ticket
2700 FIELD => 'Filename',
2701 VALUE => $args{'VALUE'},
2702 OPERATOR => $args{'OPERATOR'},
2703 DESCRIPTION => join( ' ',
2704 $self->loc('Attachment filename'), $args{'OPERATOR'},
2710 =head2 LimitContentType
2712 Takes a paramhash with the fields OPERATOR and VALUE.
2713 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2714 VALUE is a content type to search ticket attachments for
2718 sub LimitContentType {
2722 FIELD => 'ContentType',
2723 VALUE => $args{'VALUE'},
2724 OPERATOR => $args{'OPERATOR'},
2725 DESCRIPTION => join( ' ',
2726 $self->loc('Ticket content type'), $args{'OPERATOR'},
2737 Takes a paramhash with the fields OPERATOR and VALUE.
2738 OPERATOR is one of = or !=.
2750 my $owner = RT::User->new( $self->CurrentUser );
2751 $owner->Load( $args{'VALUE'} );
2753 # FIXME: check for a valid $owner
2756 VALUE => $args{'VALUE'},
2757 OPERATOR => $args{'OPERATOR'},
2758 DESCRIPTION => join( ' ',
2759 $self->loc('Owner'), $args{'OPERATOR'}, $owner->Name(), ),
2769 Takes a paramhash with the fields OPERATOR, TYPE and VALUE.
2770 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2771 VALUE is a value to match the ticket's watcher email addresses against
2772 TYPE is the sort of watchers you want to match against. Leave it undef if you want to search all of them
2786 #build us up a description
2787 my ( $watcher_type, $desc );
2788 if ( $args{'TYPE'} ) {
2789 $watcher_type = $args{'TYPE'};
2792 $watcher_type = "Watcher";
2796 FIELD => $watcher_type,
2797 VALUE => $args{'VALUE'},
2798 OPERATOR => $args{'OPERATOR'},
2799 TYPE => $args{'TYPE'},
2800 DESCRIPTION => join( ' ',
2801 $self->loc($watcher_type),
2802 $args{'OPERATOR'}, $args{'VALUE'}, ),
2811 =head2 LimitLinkedTo
2813 LimitLinkedTo takes a paramhash with two fields: TYPE and TARGET
2814 TYPE limits the sort of link we want to search on
2816 TYPE = { RefersTo, MemberOf, DependsOn }
2818 TARGET is the id or URI of the TARGET of the link
2832 FIELD => 'LinkedTo',
2834 TARGET => $args{'TARGET'},
2835 TYPE => $args{'TYPE'},
2836 DESCRIPTION => $self->loc(
2837 "Tickets [_1] by [_2]",
2838 $self->loc( $args{'TYPE'} ),
2841 OPERATOR => $args{'OPERATOR'},
2847 =head2 LimitLinkedFrom
2849 LimitLinkedFrom takes a paramhash with two fields: TYPE and BASE
2850 TYPE limits the sort of link we want to search on
2853 BASE is the id or URI of the BASE of the link
2857 sub LimitLinkedFrom {
2866 # translate RT2 From/To naming to RT3 TicketSQL naming
2867 my %fromToMap = qw(DependsOn DependentOn
2869 RefersTo ReferredToBy);
2871 my $type = $args{'TYPE'};
2872 $type = $fromToMap{$type} if exists( $fromToMap{$type} );
2875 FIELD => 'LinkedTo',
2877 BASE => $args{'BASE'},
2879 DESCRIPTION => $self->loc(
2880 "Tickets [_1] [_2]",
2881 $self->loc( $args{'TYPE'} ),
2884 OPERATOR => $args{'OPERATOR'},
2891 my $ticket_id = shift;
2892 return $self->LimitLinkedTo(
2894 TARGET => $ticket_id,
2900 sub LimitHasMember {
2902 my $ticket_id = shift;
2903 return $self->LimitLinkedFrom(
2905 BASE => "$ticket_id",
2906 TYPE => 'HasMember',
2913 sub LimitDependsOn {
2915 my $ticket_id = shift;
2916 return $self->LimitLinkedTo(
2918 TARGET => $ticket_id,
2919 TYPE => 'DependsOn',
2926 sub LimitDependedOnBy {
2928 my $ticket_id = shift;
2929 return $self->LimitLinkedFrom(
2932 TYPE => 'DependentOn',
2941 my $ticket_id = shift;
2942 return $self->LimitLinkedTo(
2944 TARGET => $ticket_id,
2952 sub LimitReferredToBy {
2954 my $ticket_id = shift;
2955 return $self->LimitLinkedFrom(
2958 TYPE => 'ReferredToBy',
2966 =head2 LimitDate (FIELD => 'DateField', OPERATOR => $oper, VALUE => $ISODate)
2968 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2970 OPERATOR is one of > or <
2971 VALUE is a date and time in ISO format in GMT
2972 FIELD is one of Starts, Started, Told, Created, Resolved, LastUpdated
2974 There are also helper functions of the form LimitFIELD that eliminate
2975 the need to pass in a FIELD argument.
2989 #Set the description if we didn't get handed it above
2990 unless ( $args{'DESCRIPTION'} ) {
2991 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2992 . $args{'OPERATOR'} . " "
2993 . $args{'VALUE'} . " GMT";
2996 $self->Limit(%args);
3003 $self->LimitDate( FIELD => 'Created', @_ );
3008 $self->LimitDate( FIELD => 'Due', @_ );
3014 $self->LimitDate( FIELD => 'Starts', @_ );
3020 $self->LimitDate( FIELD => 'Started', @_ );
3025 $self->LimitDate( FIELD => 'Resolved', @_ );
3030 $self->LimitDate( FIELD => 'Told', @_ );
3033 sub LimitLastUpdated {
3035 $self->LimitDate( FIELD => 'LastUpdated', @_ );
3040 =head2 LimitTransactionDate (OPERATOR => $oper, VALUE => $ISODate)
3042 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
3044 OPERATOR is one of > or <
3045 VALUE is a date and time in ISO format in GMT
3050 sub LimitTransactionDate {
3053 FIELD => 'TransactionDate',
3060 # <20021217042756.GK28744@pallas.fsck.com>
3061 # "Kill It" - Jesse.
3063 #Set the description if we didn't get handed it above
3064 unless ( $args{'DESCRIPTION'} ) {
3065 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
3066 . $args{'OPERATOR'} . " "
3067 . $args{'VALUE'} . " GMT";
3070 $self->Limit(%args);
3077 =head2 LimitCustomField
3079 Takes a paramhash of key/value pairs with the following keys:
3083 =item CUSTOMFIELD - CustomField name or id. If a name is passed, an additional parameter QUEUE may also be passed to distinguish the custom field.
3085 =item OPERATOR - The usual Limit operators
3087 =item VALUE - The value to compare against
3093 sub LimitCustomField {
3097 CUSTOMFIELD => undef,
3099 DESCRIPTION => undef,
3100 FIELD => 'CustomFieldValue',
3105 my $CF = RT::CustomField->new( $self->CurrentUser );
3106 if ( $args{CUSTOMFIELD} =~ /^\d+$/ ) {
3107 $CF->Load( $args{CUSTOMFIELD} );
3110 $CF->LoadByNameAndQueue(
3111 Name => $args{CUSTOMFIELD},
3112 Queue => $args{QUEUE}
3114 $args{CUSTOMFIELD} = $CF->Id;
3117 #If we are looking to compare with a null value.
3118 if ( $args{'OPERATOR'} =~ /^is$/i ) {
3119 $args{'DESCRIPTION'}
3120 ||= $self->loc( "Custom field [_1] has no value.", $CF->Name );
3122 elsif ( $args{'OPERATOR'} =~ /^is not$/i ) {
3123 $args{'DESCRIPTION'}
3124 ||= $self->loc( "Custom field [_1] has a value.", $CF->Name );
3127 # if we're not looking to compare with a null value
3129 $args{'DESCRIPTION'} ||= $self->loc( "Custom field [_1] [_2] [_3]",
3130 $CF->Name, $args{OPERATOR}, $args{VALUE} );
3133 if ( defined $args{'QUEUE'} && $args{'QUEUE'} =~ /\D/ ) {
3134 my $QueueObj = RT::Queue->new( $self->CurrentUser );
3135 $QueueObj->Load( $args{'QUEUE'} );
3136 $args{'QUEUE'} = $QueueObj->Id;
3138 delete $args{'QUEUE'} unless defined $args{'QUEUE'} && length $args{'QUEUE'};
3141 @rest = ( ENTRYAGGREGATOR => 'AND' )
3142 if ( $CF->Type eq 'SelectMultiple' );
3145 VALUE => $args{VALUE},
3147 .(defined $args{'QUEUE'}? ".{$args{'QUEUE'}}" : '' )
3148 .".{" . $CF->Name . "}",
3149 OPERATOR => $args{OPERATOR},
3154 $self->{'RecalcTicketLimits'} = 1;
3161 Keep track of the counter for the array of restrictions
3167 return ( $self->{'restriction_index'}++ );
3175 $self->{'table'} = "Tickets";
3176 $self->{'RecalcTicketLimits'} = 1;
3177 $self->{'looking_at_effective_id'} = 0;
3178 $self->{'looking_at_type'} = 0;
3179 $self->{'restriction_index'} = 1;
3180 $self->{'primary_key'} = "id";
3181 delete $self->{'items_array'};
3182 delete $self->{'item_map'};
3183 delete $self->{'columns_to_display'};
3184 $self->SUPER::_Init(@_);
3193 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3194 return ( $self->SUPER::Count() );
3200 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3201 return ( $self->SUPER::CountAll() );
3206 =head2 ItemsArrayRef
3208 Returns a reference to the set of all items found in this search
3215 return $self->{'items_array'} if $self->{'items_array'};
3217 my $placeholder = $self->_ItemsCounter;
3218 $self->GotoFirstItem();
3219 while ( my $item = $self->Next ) {
3220 push( @{ $self->{'items_array'} }, $item );
3222 $self->GotoItem($placeholder);
3223 $self->{'items_array'}
3224 = $self->ItemsOrderBy( $self->{'items_array'} );
3226 return $self->{'items_array'};
3229 sub ItemsArrayRefWindow {
3233 my @old = ($self->_ItemsCounter, $self->RowsPerPage, $self->FirstRow+1);
3235 $self->RowsPerPage( $window );
3237 $self->GotoFirstItem;
3240 while ( my $item = $self->Next ) {
3244 $self->RowsPerPage( $old[1] );
3245 $self->FirstRow( $old[2] );
3246 $self->GotoItem( $old[0] );
3255 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3257 my $Ticket = $self->SUPER::Next;
3258 return $Ticket unless $Ticket;
3260 if ( $Ticket->__Value('Status') eq 'deleted'
3261 && !$self->{'allow_deleted_search'} )
3265 elsif ( RT->Config->Get('UseSQLForACLChecks') ) {
3266 # if we found a ticket with this option enabled then
3267 # all tickets we found are ACLed, cache this fact
3268 my $key = join ";:;", $self->CurrentUser->id, 'ShowTicket', 'RT::Ticket-'. $Ticket->id;
3269 $RT::Principal::_ACL_CACHE->set( $key => 1 );
3272 elsif ( $Ticket->CurrentUserHasRight('ShowTicket') ) {
3277 # If the user doesn't have the right to show this ticket
3284 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
3285 return $self->SUPER::_DoSearch( @_ );
3290 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
3291 return $self->SUPER::_DoCount( @_ );
3297 my $cache_key = 'RolesHasRight;:;ShowTicket';
3299 if ( my $cached = $RT::Principal::_ACL_CACHE->fetch( $cache_key ) ) {
3303 my $ACL = RT::ACL->new( RT->SystemUser );
3304 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
3305 $ACL->Limit( FIELD => 'PrincipalType', OPERATOR => '!=', VALUE => 'Group' );
3306 my $principal_alias = $ACL->Join(
3308 FIELD1 => 'PrincipalId',
3309 TABLE2 => 'Principals',
3312 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3315 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
3316 my $role = $ACE->__Value('PrincipalType');
3317 my $type = $ACE->__Value('ObjectType');
3318 if ( $type eq 'RT::System' ) {
3321 elsif ( $type eq 'RT::Queue' ) {
3322 next if $res{ $role } && !ref $res{ $role };
3323 push @{ $res{ $role } ||= [] }, $ACE->__Value('ObjectId');
3326 $RT::Logger->error('ShowTicket right is granted on unsupported object');
3329 $RT::Principal::_ACL_CACHE->set( $cache_key => \%res );
3333 sub _DirectlyCanSeeIn {
3335 my $id = $self->CurrentUser->id;
3337 my $cache_key = 'User-'. $id .';:;ShowTicket;:;DirectlyCanSeeIn';
3338 if ( my $cached = $RT::Principal::_ACL_CACHE->fetch( $cache_key ) ) {
3342 my $ACL = RT::ACL->new( RT->SystemUser );
3343 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
3344 my $principal_alias = $ACL->Join(
3346 FIELD1 => 'PrincipalId',
3347 TABLE2 => 'Principals',
3350 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3351 my $cgm_alias = $ACL->Join(
3353 FIELD1 => 'PrincipalId',
3354 TABLE2 => 'CachedGroupMembers',
3355 FIELD2 => 'GroupId',
3357 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
3358 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
3361 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
3362 my $type = $ACE->__Value('ObjectType');
3363 if ( $type eq 'RT::System' ) {
3364 # If user is direct member of a group that has the right
3365 # on the system then he can see any ticket
3366 $RT::Principal::_ACL_CACHE->set( $cache_key => [-1] );
3369 elsif ( $type eq 'RT::Queue' ) {
3370 push @res, $ACE->__Value('ObjectId');
3373 $RT::Logger->error('ShowTicket right is granted on unsupported object');
3376 $RT::Principal::_ACL_CACHE->set( $cache_key => \@res );
3380 sub CurrentUserCanSee {
3382 return if $self->{'_sql_current_user_can_see_applied'};
3384 return $self->{'_sql_current_user_can_see_applied'} = 1
3385 if $self->CurrentUser->UserObj->HasRight(
3386 Right => 'SuperUser', Object => $RT::System
3389 my $id = $self->CurrentUser->id;
3391 # directly can see in all queues then we have nothing to do
3392 my @direct_queues = $self->_DirectlyCanSeeIn;
3393 return $self->{'_sql_current_user_can_see_applied'} = 1
3394 if @direct_queues && $direct_queues[0] == -1;
3396 my %roles = $self->_RolesCanSee;
3398 my %skip = map { $_ => 1 } @direct_queues;
3399 foreach my $role ( keys %roles ) {
3400 next unless ref $roles{ $role };
3402 my @queues = grep !$skip{$_}, @{ $roles{ $role } };
3404 $roles{ $role } = \@queues;
3406 delete $roles{ $role };
3411 # there is no global watchers, only queues and tickes, if at
3412 # some point we will add global roles then it's gonna blow
3413 # the idea here is that if the right is set globaly for a role
3414 # and user plays this role for a queue directly not a ticket
3415 # then we have to check in advance
3416 if ( my @tmp = grep $_ ne 'Owner' && !ref $roles{ $_ }, keys %roles ) {
3418 my $groups = RT::Groups->new( RT->SystemUser );
3419 $groups->Limit( FIELD => 'Domain', VALUE => 'RT::Queue-Role' );
3421 $groups->Limit( FIELD => 'Type', VALUE => $_ );
3423 my $principal_alias = $groups->Join(
3426 TABLE2 => 'Principals',
3429 $groups->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3430 my $cgm_alias = $groups->Join(
3433 TABLE2 => 'CachedGroupMembers',
3434 FIELD2 => 'GroupId',
3436 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
3437 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
3438 while ( my $group = $groups->Next ) {
3439 push @direct_queues, $group->Instance;
3443 unless ( @direct_queues || keys %roles ) {
3444 $self->SUPER::Limit(
3449 ENTRYAGGREGATOR => 'AND',
3451 return $self->{'_sql_current_user_can_see_applied'} = 1;
3455 my $join_roles = keys %roles;
3456 $join_roles = 0 if $join_roles == 1 && $roles{'Owner'};
3457 my ($role_group_alias, $cgm_alias);
3458 if ( $join_roles ) {
3459 $role_group_alias = $self->_RoleGroupsJoin( New => 1 );
3460 $cgm_alias = $self->_GroupMembersJoin( GroupsAlias => $role_group_alias );
3461 $self->SUPER::Limit(
3462 LEFTJOIN => $cgm_alias,
3463 FIELD => 'MemberId',
3468 my $limit_queues = sub {
3472 return unless @queues;
3473 if ( @queues == 1 ) {
3474 $self->SUPER::Limit(
3479 ENTRYAGGREGATOR => $ea,
3482 $self->SUPER::_OpenParen('ACL');
3483 foreach my $q ( @queues ) {
3484 $self->SUPER::Limit(
3489 ENTRYAGGREGATOR => $ea,
3493 $self->SUPER::_CloseParen('ACL');
3498 $self->SUPER::_OpenParen('ACL');
3500 $ea = 'OR' if $limit_queues->( $ea, @direct_queues );
3501 while ( my ($role, $queues) = each %roles ) {
3502 $self->SUPER::_OpenParen('ACL');
3503 if ( $role eq 'Owner' ) {
3504 $self->SUPER::Limit(
3508 ENTRYAGGREGATOR => $ea,
3512 $self->SUPER::Limit(
3514 ALIAS => $cgm_alias,
3515 FIELD => 'MemberId',
3516 OPERATOR => 'IS NOT',
3519 ENTRYAGGREGATOR => $ea,
3521 $self->SUPER::Limit(
3523 ALIAS => $role_group_alias,
3526 ENTRYAGGREGATOR => 'AND',
3529 $limit_queues->( 'AND', @$queues ) if ref $queues;
3530 $ea = 'OR' if $ea eq 'AND';
3531 $self->SUPER::_CloseParen('ACL');
3533 $self->SUPER::_CloseParen('ACL');
3535 return $self->{'_sql_current_user_can_see_applied'} = 1;
3542 =head2 LoadRestrictions
3544 LoadRestrictions takes a string which can fully populate the TicketRestrictons hash.
3545 TODO It is not yet implemented
3551 =head2 DescribeRestrictions
3554 Returns a hash keyed by restriction id.
3555 Each element of the hash is currently a one element hash that contains DESCRIPTION which
3556 is a description of the purpose of that TicketRestriction
3560 sub DescribeRestrictions {
3565 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
3566 $listing{$row} = $self->{'TicketRestrictions'}{$row}{'DESCRIPTION'};
3573 =head2 RestrictionValues FIELD
3575 Takes a restriction field and returns a list of values this field is restricted
3580 sub RestrictionValues {
3583 map $self->{'TicketRestrictions'}{$_}{'VALUE'}, grep {
3584 $self->{'TicketRestrictions'}{$_}{'FIELD'} eq $field
3585 && $self->{'TicketRestrictions'}{$_}{'OPERATOR'} eq "="
3587 keys %{ $self->{'TicketRestrictions'} };
3592 =head2 ClearRestrictions
3594 Removes all restrictions irretrievably
3598 sub ClearRestrictions {
3600 delete $self->{'TicketRestrictions'};
3601 $self->{'looking_at_effective_id'} = 0;
3602 $self->{'looking_at_type'} = 0;
3603 $self->{'RecalcTicketLimits'} = 1;
3608 =head2 DeleteRestriction
3610 Takes the row Id of a restriction (From DescribeRestrictions' output, for example.
3611 Removes that restriction from the session's limits.
3615 sub DeleteRestriction {
3618 delete $self->{'TicketRestrictions'}{$row};
3620 $self->{'RecalcTicketLimits'} = 1;
3622 #make the underlying easysearch object forget all its preconceptions
3627 # Convert a set of oldstyle SB Restrictions to Clauses for RQL
3629 sub _RestrictionsToClauses {
3633 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
3634 my $restriction = $self->{'TicketRestrictions'}{$row};
3636 # We need to reimplement the subclause aggregation that SearchBuilder does.
3637 # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main',
3638 # Then SB AND's the different Subclauses together.
3640 # So, we want to group things into Subclauses, convert them to
3641 # SQL, and then join them with the appropriate DefaultEA.
3642 # Then join each subclause group with AND.
3644 my $field = $restriction->{'FIELD'};
3645 my $realfield = $field; # CustomFields fake up a fieldname, so
3646 # we need to figure that out
3649 # Rewrite LinkedTo meta field to the real field
3650 if ( $field =~ /LinkedTo/ ) {
3651 $realfield = $field = $restriction->{'TYPE'};
3655 # Handle subkey fields with a different real field
3656 if ( $field =~ /^(\w+)\./ ) {
3660 die "I don't know about $field yet"
3661 unless ( exists $FIELD_METADATA{$realfield}
3662 or $restriction->{CUSTOMFIELD} );
3664 my $type = $FIELD_METADATA{$realfield}->[0];
3665 my $op = $restriction->{'OPERATOR'};
3669 map { $restriction->{$_} } qw(VALUE TICKET BASE TARGET)
3672 # this performs the moral equivalent of defined or/dor/C<//>,
3673 # without the short circuiting.You need to use a 'defined or'
3674 # type thing instead of just checking for truth values, because
3675 # VALUE could be 0.(i.e. "false")
3677 # You could also use this, but I find it less aesthetic:
3678 # (although it does short circuit)
3679 #( defined $restriction->{'VALUE'}? $restriction->{VALUE} :
3680 # defined $restriction->{'TICKET'} ?
3681 # $restriction->{TICKET} :
3682 # defined $restriction->{'BASE'} ?
3683 # $restriction->{BASE} :
3684 # defined $restriction->{'TARGET'} ?
3685 # $restriction->{TARGET} )
3687 my $ea = $restriction->{ENTRYAGGREGATOR}
3688 || $DefaultEA{$type}
3691 die "Invalid operator $op for $field ($type)"
3692 unless exists $ea->{$op};
3696 # Each CustomField should be put into a different Clause so they
3697 # are ANDed together.
3698 if ( $restriction->{CUSTOMFIELD} ) {
3699 $realfield = $field;
3702 exists $clause{$realfield} or $clause{$realfield} = [];
3705 $field =~ s!(['\\])!\\$1!g;
3706 $value =~ s!(['\\])!\\$1!g;
3707 my $data = [ $ea, $type, $field, $op, $value ];
3709 # here is where we store extra data, say if it's a keyword or
3710 # something. (I.e. "TYPE SPECIFIC STUFF")
3712 if (lc $ea eq 'none') {
3713 $clause{$realfield} = [ $data ];
3715 push @{ $clause{$realfield} }, $data;
3723 =head2 _ProcessRestrictions PARAMHASH
3725 # The new _ProcessRestrictions is somewhat dependent on the SQL stuff,
3726 # but isn't quite generic enough to move into Tickets_SQL.
3730 sub _ProcessRestrictions {
3733 #Blow away ticket aliases since we'll need to regenerate them for
3735 delete $self->{'TicketAliases'};
3736 delete $self->{'items_array'};
3737 delete $self->{'item_map'};
3738 delete $self->{'raw_rows'};
3739 delete $self->{'rows'};
3740 delete $self->{'count_all'};
3742 my $sql = $self->Query; # Violating the _SQL namespace
3743 if ( !$sql || $self->{'RecalcTicketLimits'} ) {
3745 # "Restrictions to Clauses Branch\n";
3746 my $clauseRef = eval { $self->_RestrictionsToClauses; };
3748 $RT::Logger->error( "RestrictionsToClauses: " . $@ );
3752 $sql = $self->ClausesToSQL($clauseRef);
3753 $self->FromSQL($sql) if $sql;
3757 $self->{'RecalcTicketLimits'} = 0;
3761 =head2 _BuildItemMap
3763 Build up a L</ItemMap> of first/last/next/prev items, so that we can
3764 display search nav quickly.
3771 my $window = RT->Config->Get('TicketsItemMapSize');
3773 $self->{'item_map'} = {};
3775 my $items = $self->ItemsArrayRefWindow( $window );
3776 return unless $items && @$items;
3779 $self->{'item_map'}{'first'} = $items->[0]->EffectiveId;
3780 for ( my $i = 0; $i < @$items; $i++ ) {
3781 my $item = $items->[$i];
3782 my $id = $item->EffectiveId;
3783 $self->{'item_map'}{$id}{'defined'} = 1;
3784 $self->{'item_map'}{$id}{'prev'} = $prev;
3785 $self->{'item_map'}{$id}{'next'} = $items->[$i+1]->EffectiveId
3789 $self->{'item_map'}{'last'} = $prev
3790 if !$window || @$items < $window;
3795 Returns an a map of all items found by this search. The map is a hash
3799 first => <first ticket id found>,
3800 last => <last ticket id found or undef>,
3803 prev => <the ticket id found before>,
3804 next => <the ticket id found after>,
3816 $self->_BuildItemMap unless $self->{'item_map'};
3817 return $self->{'item_map'};
3823 =head2 PrepForSerialization
3825 You don't want to serialize a big tickets object, as
3826 the {items} hash will be instantly invalid _and_ eat
3831 sub PrepForSerialization {
3833 delete $self->{'items'};
3834 delete $self->{'items_array'};
3835 $self->RedoSearch();
3840 RT::Tickets supports several flags which alter search behavior:
3843 allow_deleted_search (Otherwise never show deleted tickets in search results)
3844 looking_at_type (otherwise limit to type=ticket)
3846 These flags are set by calling
3848 $tickets->{'flagname'} = 1;
3850 BUG: There should be an API for this
3860 Returns an empty new RT::Ticket item
3866 return(RT::Ticket->new($self->CurrentUser));
3868 RT::Base->_ImportOverlays();