1 # BEGIN BPS TAGGED BLOCK {{{
5 # This software is Copyright (c) 1996-2012 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 our %SEARCHABLE_SUBFIELDS = (
163 EmailAddress Name RealName Nickname Organization Address1 Address2
164 WorkPhone HomePhone MobilePhone PagerPhone id
168 # Mapping of Field Type to Function
170 ENUM => \&_EnumLimit,
173 LINK => \&_LinkLimit,
174 DATE => \&_DateLimit,
175 STRING => \&_StringLimit,
176 TRANSFIELD => \&_TransLimit,
177 TRANSCONTENT => \&_TransContentLimit,
178 TRANSDATE => \&_TransDateLimit,
179 WATCHERFIELD => \&_WatcherLimit,
180 MEMBERSHIPFIELD => \&_WatcherMembershipLimit,
181 CUSTOMFIELD => \&_CustomFieldLimit,
182 HASATTRIBUTE => \&_HasAttributeLimit,
183 FREESIDEFIELD => \&_FreesideFieldLimit,
185 our %can_bundle = ();# WATCHERFIELD => "yes", );
187 # Default EntryAggregator per type
188 # if you specify OP, you must specify all valid OPs
229 # Helper functions for passing the above lexically scoped tables above
231 sub FIELDS { return \%FIELD_METADATA }
232 sub dispatch { return \%dispatch }
233 sub can_bundle { return \%can_bundle }
235 # Bring in the clowns.
236 require RT::Tickets_SQL;
239 our @SORTFIELDS = qw(id Status
241 Owner Created Due Starts Started
243 Resolved LastUpdated Priority TimeWorked TimeLeft);
247 Returns the list of fields that lists of tickets can easily be sorted by
253 return (@SORTFIELDS);
257 # BEGIN SQL STUFF *********************************
262 $self->SUPER::CleanSlate( @_ );
263 delete $self->{$_} foreach qw(
265 _sql_group_members_aliases
266 _sql_object_cfv_alias
267 _sql_role_group_aliases
269 _sql_u_watchers_alias_for_sort
270 _sql_u_watchers_aliases
271 _sql_current_user_can_see_applied
275 =head1 Limit Helper Routines
277 These routines are the targets of a dispatch table depending on the
278 type of field. They all share the same signature:
280 my ($self,$field,$op,$value,@rest) = @_;
282 The values in @rest should be suitable for passing directly to
283 DBIx::SearchBuilder::Limit.
285 Essentially they are an expanded/broken out (and much simplified)
286 version of what ProcessRestrictions used to do. They're also much
287 more clearly delineated by the TYPE of field being processed.
296 my ( $sb, $field, $op, $value, @rest ) = @_;
298 if ( $value eq '__Bookmarked__' ) {
299 return $sb->_BookmarkLimit( $field, $op, $value, @rest );
301 return $sb->_IntLimit( $field, $op, $value, @rest );
306 my ( $sb, $field, $op, $value, @rest ) = @_;
308 die "Invalid operator $op for __Bookmarked__ search on $field"
309 unless $op =~ /^(=|!=)$/;
312 my $tmp = $sb->CurrentUser->UserObj->FirstAttribute('Bookmarks');
313 $tmp = $tmp->Content if $tmp;
318 return $sb->_SQLLimit(
325 # as bookmarked tickets can be merged we have to use a join
326 # but it should be pretty lightweight
327 my $tickets_alias = $sb->Join(
332 FIELD2 => 'EffectiveId',
336 my $ea = $op eq '='? 'OR': 'AND';
337 foreach my $id ( sort @bookmarks ) {
339 ALIAS => $tickets_alias,
343 $first? (@rest): ( ENTRYAGGREGATOR => $ea )
345 $first = 0 if $first;
352 Handle Fields which are limited to certain values, and potentially
353 need to be looked up from another class.
355 This subroutine actually handles two different kinds of fields. For
356 some the user is responsible for limiting the values. (i.e. Status,
359 For others, the value specified by the user will be looked by via
363 name of class to lookup in (Optional)
368 my ( $sb, $field, $op, $value, @rest ) = @_;
370 # SQL::Statement changes != to <>. (Can we remove this now?)
371 $op = "!=" if $op eq "<>";
373 die "Invalid Operation: $op for $field"
377 my $meta = $FIELD_METADATA{$field};
378 if ( defined $meta->[1] && defined $value && $value !~ /^\d+$/ ) {
379 my $class = "RT::" . $meta->[1];
380 my $o = $class->new( $sb->CurrentUser );
394 Handle fields where the values are limited to integers. (For example,
395 Priority, TimeWorked.)
403 my ( $sb, $field, $op, $value, @rest ) = @_;
405 die "Invalid Operator $op for $field"
406 unless $op =~ /^(=|!=|>|<|>=|<=)$/;
418 Handle fields which deal with links between tickets. (MemberOf, DependsOn)
421 1: Direction (From, To)
422 2: Link Type (MemberOf, DependsOn, RefersTo)
427 my ( $sb, $field, $op, $value, @rest ) = @_;
429 my $meta = $FIELD_METADATA{$field};
430 die "Invalid Operator $op for $field" unless $op =~ /^(=|!=|IS|IS NOT)$/io;
433 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
437 $is_null = 1 if !$value || $value =~ /^null$/io;
440 $value = RT::URI->new( $sb->CurrentUser )->CanonicalizeURI( $value );
443 my $direction = $meta->[1] || '';
444 my ($matchfield, $linkfield) = ('', '');
445 if ( $direction eq 'To' ) {
446 ($matchfield, $linkfield) = ("Target", "Base");
448 elsif ( $direction eq 'From' ) {
449 ($matchfield, $linkfield) = ("Base", "Target");
451 elsif ( $direction ) {
452 die "Invalid link direction '$direction' for $field\n";
455 $sb->_LinkLimit( 'LinkedTo', $op, $value, @rest );
457 'LinkedFrom', $op, $value, @rest,
458 ENTRYAGGREGATOR => (($is_negative && $is_null) || (!$is_null && !$is_negative))? 'OR': 'AND',
466 $op = ($op =~ /^(=|IS)$/)? 'IS': 'IS NOT';
468 elsif ( $value =~ /\D/ ) {
471 $matchfield = "Local$matchfield" if $is_local;
473 #For doing a left join to find "unlinked tickets" we want to generate a query that looks like this
474 # SELECT main.* FROM Tickets main
475 # LEFT JOIN Links Links_1 ON ( (Links_1.Type = 'MemberOf')
476 # AND(main.id = Links_1.LocalTarget))
477 # WHERE Links_1.LocalBase IS NULL;
480 my $linkalias = $sb->Join(
485 FIELD2 => 'Local' . $linkfield
488 LEFTJOIN => $linkalias,
496 FIELD => $matchfield,
503 my $linkalias = $sb->Join(
508 FIELD2 => 'Local' . $linkfield
511 LEFTJOIN => $linkalias,
517 LEFTJOIN => $linkalias,
518 FIELD => $matchfield,
525 FIELD => $matchfield,
526 OPERATOR => $is_negative? 'IS': 'IS NOT',
535 Handle date fields. (Created, LastTold..)
538 1: type of link. (Probably not necessary.)
543 my ( $sb, $field, $op, $value, @rest ) = @_;
545 die "Invalid Date Op: $op"
546 unless $op =~ /^(=|>|<|>=|<=)$/;
548 my $meta = $FIELD_METADATA{$field};
549 die "Incorrect Meta Data for $field"
550 unless ( defined $meta->[1] );
552 $sb->_DateFieldLimit( $meta->[1], $op, $value, @rest );
555 # Factor this out for use by custom fields
557 sub _DateFieldLimit {
558 my ( $sb, $field, $op, $value, @rest ) = @_;
560 my $date = RT::Date->new( $sb->CurrentUser );
561 $date->Set( Format => 'unknown', Value => $value );
565 # if we're specifying =, that means we want everything on a
566 # particular single day. in the database, we need to check for >
567 # and < the edges of that day.
569 # Except if the value is 'this month' or 'last month', check
570 # > and < the edges of the month.
572 my ($daystart, $dayend);
573 if ( lc($value) eq 'this month' ) {
575 $date->SetToStart('month', Timezone => 'server');
576 $daystart = $date->ISO;
577 $date->AddMonth(Timezone => 'server');
578 $dayend = $date->ISO;
580 elsif ( lc($value) eq 'last month' ) {
582 $date->SetToStart('month', Timezone => 'server');
583 $dayend = $date->ISO;
585 $date->SetToStart('month', Timezone => 'server');
586 $daystart = $date->ISO;
589 $date->SetToMidnight( Timezone => 'server' );
590 $daystart = $date->ISO;
592 $dayend = $date->ISO;
609 ENTRYAGGREGATOR => 'AND',
627 Handle simple fields which are just strings. (Subject,Type)
635 my ( $sb, $field, $op, $value, @rest ) = @_;
639 # =, !=, LIKE, NOT LIKE
640 if ( RT->Config->Get('DatabaseType') eq 'Oracle'
641 && (!defined $value || !length $value)
642 && lc($op) ne 'is' && lc($op) ne 'is not'
644 if ($op eq '!=' || $op =~ /^NOT\s/i) {
661 =head2 _TransDateLimit
663 Handle fields limiting based on Transaction Date.
665 The inpupt value must be in a format parseable by Time::ParseDate
672 # This routine should really be factored into translimit.
673 sub _TransDateLimit {
674 my ( $sb, $field, $op, $value, @rest ) = @_;
676 # See the comments for TransLimit, they apply here too
678 my $txn_alias = $sb->JoinTransactions;
680 my $date = RT::Date->new( $sb->CurrentUser );
681 $date->Set( Format => 'unknown', Value => $value );
686 # if we're specifying =, that means we want everything on a
687 # particular single day. in the database, we need to check for >
688 # and < the edges of that day.
690 $date->SetToMidnight( Timezone => 'server' );
691 my $daystart = $date->ISO;
693 my $dayend = $date->ISO;
708 ENTRYAGGREGATOR => 'AND',
713 # not searching for a single day
716 #Search for the right field
731 Limit based on the ContentType or the Filename of a transaction.
736 my ( $self, $field, $op, $value, %rest ) = @_;
738 my $txn_alias = $self->JoinTransactions;
739 unless ( defined $self->{_sql_trattachalias} ) {
740 $self->{_sql_trattachalias} = $self->_SQLJoin(
741 TYPE => 'LEFT', # not all txns have an attachment
742 ALIAS1 => $txn_alias,
744 TABLE2 => 'Attachments',
745 FIELD2 => 'TransactionId',
751 ALIAS => $self->{_sql_trattachalias},
759 =head2 _TransContentLimit
761 Limit based on the Content of a transaction.
765 sub _TransContentLimit {
769 # If only this was this simple. We've got to do something
772 #Basically, we want to make sure that the limits apply to
773 #the same attachment, rather than just another attachment
774 #for the same ticket, no matter how many clauses we lump
775 #on. We put them in TicketAliases so that they get nuked
776 #when we redo the join.
778 # In the SQL, we might have
779 # (( Content = foo ) or ( Content = bar AND Content = baz ))
780 # The AND group should share the same Alias.
782 # Actually, maybe it doesn't matter. We use the same alias and it
783 # works itself out? (er.. different.)
785 # Steal more from _ProcessRestrictions
787 # FIXME: Maybe look at the previous FooLimit call, and if it was a
788 # TransLimit and EntryAggregator == AND, reuse the Aliases?
790 # Or better - store the aliases on a per subclause basis - since
791 # those are going to be the things we want to relate to each other,
794 # maybe we should not allow certain kinds of aggregation of these
795 # clauses and do a psuedo regex instead? - the problem is getting
796 # them all into the same subclause when you have (A op B op C) - the
797 # way they get parsed in the tree they're in different subclauses.
799 my ( $self, $field, $op, $value, %rest ) = @_;
800 $field = 'Content' if $field =~ /\W/;
802 my $config = RT->Config->Get('FullTextSearch') || {};
803 unless ( $config->{'Enable'} ) {
804 $self->_SQLLimit( %rest, FIELD => 'id', VALUE => 0 );
808 my $txn_alias = $self->JoinTransactions;
809 unless ( defined $self->{_sql_trattachalias} ) {
810 $self->{_sql_trattachalias} = $self->_SQLJoin(
811 TYPE => 'LEFT', # not all txns have an attachment
812 ALIAS1 => $txn_alias,
814 TABLE2 => 'Attachments',
815 FIELD2 => 'TransactionId',
820 if ( $config->{'Indexed'} ) {
821 my $db_type = RT->Config->Get('DatabaseType');
824 if ( $config->{'Table'} and $config->{'Table'} ne "Attachments") {
825 $alias = $self->{'_sql_aliases'}{'full_text'} ||= $self->_SQLJoin(
827 ALIAS1 => $self->{'_sql_trattachalias'},
829 TABLE2 => $config->{'Table'},
833 $alias = $self->{'_sql_trattachalias'};
836 #XXX: handle negative searches
837 my $index = $config->{'Column'};
838 if ( $db_type eq 'Oracle' ) {
839 my $dbh = $RT::Handle->dbh;
840 my $alias = $self->{_sql_trattachalias};
843 FUNCTION => "CONTAINS( $alias.$field, ".$dbh->quote($value) .")",
849 # this is required to trick DBIx::SB's LEFT JOINS optimizer
850 # into deciding that join is redundant as it is
852 ENTRYAGGREGATOR => 'AND',
853 ALIAS => $self->{_sql_trattachalias},
855 OPERATOR => 'IS NOT',
859 elsif ( $db_type eq 'Pg' ) {
860 my $dbh = $RT::Handle->dbh;
866 VALUE => 'plainto_tsquery('. $dbh->quote($value) .')',
870 elsif ( $db_type eq 'mysql' ) {
871 # XXX: We could theoretically skip the join to Attachments,
872 # and have Sphinx simply index and group by the TicketId,
873 # and join Ticket.id to that attribute, which would be much
874 # more efficient -- however, this is only a possibility if
875 # there are no other transaction limits.
877 # This is a special character. Note that \ does not escape
878 # itself (in Sphinx 2.1.0, at least), so 'foo\;bar' becoming
879 # 'foo\\;bar' is not a vulnerability, and is still parsed as
880 # "foo, \, ;, then bar". Happily, the default mode is
881 # "all", meaning that boolean operators are not special.
884 my $max = $config->{'MaxMatches'};
890 VALUE => "$value;limit=$max;maxmatches=$max",
896 ALIAS => $self->{_sql_trattachalias},
903 if ( RT->Config->Get('DontSearchFileAttachments') ) {
905 ENTRYAGGREGATOR => 'AND',
906 ALIAS => $self->{_sql_trattachalias},
917 Handle watcher limits. (Requestor, CC, etc..)
933 my $meta = $FIELD_METADATA{ $field };
934 my $type = $meta->[1] || '';
935 my $class = $meta->[2] || 'Ticket';
937 # Bail if the subfield is not allowed
939 and not grep { $_ eq $rest{SUBKEY} } @{$SEARCHABLE_SUBFIELDS{'User'}})
941 die "Invalid watcher subfield: '$rest{SUBKEY}'";
944 # Owner was ENUM field, so "Owner = 'xxx'" allowed user to
945 # search by id and Name at the same time, this is workaround
946 # to preserve backward compatibility
947 if ( $field eq 'Owner' ) {
948 if ( $op =~ /^!?=$/ && (!$rest{'SUBKEY'} || $rest{'SUBKEY'} eq 'Name' || $rest{'SUBKEY'} eq 'EmailAddress') ) {
949 my $o = RT::User->new( $self->CurrentUser );
950 my $method = ($rest{'SUBKEY'}||'') eq 'EmailAddress' ? 'LoadByEmail': 'Load';
951 $o->$method( $value );
960 if ( ($rest{'SUBKEY'}||'') eq 'id' ) {
970 $rest{SUBKEY} ||= 'EmailAddress';
972 my $groups = $self->_RoleGroupsJoin( Type => $type, Class => $class, New => !$type );
975 if ( $op =~ /^IS(?: NOT)?$/ ) {
976 # is [not] empty case
978 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
979 # to avoid joining the table Users into the query, we just join GM
980 # and make sure we don't match records where group is member of itself
982 LEFTJOIN => $group_members,
985 VALUE => "$group_members.MemberId",
989 ALIAS => $group_members,
996 elsif ( $op =~ /^!=$|^NOT\s+/i ) {
997 # negative condition case
1000 $op =~ s/!|NOT\s+//i;
1002 # XXX: we have no way to build correct "Watcher.X != 'Y'" when condition
1003 # "X = 'Y'" matches more then one user so we try to fetch two records and
1004 # do the right thing when there is only one exist and semi-working solution
1006 my $users_obj = RT::Users->new( $self->CurrentUser );
1008 FIELD => $rest{SUBKEY},
1012 $users_obj->OrderBy;
1013 $users_obj->RowsPerPage(2);
1014 my @users = @{ $users_obj->ItemsArrayRef };
1016 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
1017 if ( @users <= 1 ) {
1019 $uid = $users[0]->id if @users;
1020 $self->SUPER::Limit(
1021 LEFTJOIN => $group_members,
1022 ALIAS => $group_members,
1023 FIELD => 'MemberId',
1028 ALIAS => $group_members,
1034 $self->SUPER::Limit(
1035 LEFTJOIN => $group_members,
1038 VALUE => "$group_members.MemberId",
1041 my $users = $self->Join(
1043 ALIAS1 => $group_members,
1044 FIELD1 => 'MemberId',
1048 $self->SUPER::Limit(
1051 FIELD => $rest{SUBKEY},
1065 # positive condition case
1067 my $group_members = $self->_GroupMembersJoin(
1068 GroupsAlias => $groups, New => 1, Left => 0
1070 my $users = $self->Join(
1072 ALIAS1 => $group_members,
1073 FIELD1 => 'MemberId',
1080 FIELD => $rest{'SUBKEY'},
1089 sub _RoleGroupsJoin {
1091 my %args = (New => 0, Class => 'Ticket', Type => '', @_);
1092 return $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1093 if $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1096 # we always have watcher groups for ticket, so we use INNER join
1097 my $groups = $self->Join(
1099 FIELD1 => $args{'Class'} eq 'Queue'? 'Queue': 'id',
1101 FIELD2 => 'Instance',
1102 ENTRYAGGREGATOR => 'AND',
1104 $self->SUPER::Limit(
1105 LEFTJOIN => $groups,
1108 VALUE => 'RT::'. $args{'Class'} .'-Role',
1110 $self->SUPER::Limit(
1111 LEFTJOIN => $groups,
1114 VALUE => $args{'Type'},
1117 $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} } = $groups
1118 unless $args{'New'};
1123 sub _GroupMembersJoin {
1125 my %args = (New => 1, GroupsAlias => undef, Left => 1, @_);
1127 return $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1128 if $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1131 my $alias = $self->Join(
1132 $args{'Left'} ? (TYPE => 'LEFT') : (),
1133 ALIAS1 => $args{'GroupsAlias'},
1135 TABLE2 => 'CachedGroupMembers',
1136 FIELD2 => 'GroupId',
1137 ENTRYAGGREGATOR => 'AND',
1139 $self->SUPER::Limit(
1140 $args{'Left'} ? (LEFTJOIN => $alias) : (),
1142 FIELD => 'Disabled',
1146 $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} } = $alias
1147 unless $args{'New'};
1154 Helper function which provides joins to a watchers table both for limits
1161 my $type = shift || '';
1164 my $groups = $self->_RoleGroupsJoin( Type => $type );
1165 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
1166 # XXX: work around, we must hide groups that
1167 # are members of the role group we search in,
1168 # otherwise them result in wrong NULLs in Users
1169 # table and break ordering. Now, we know that
1170 # RT doesn't allow to add groups as members of the
1171 # ticket roles, so we just hide entries in CGM table
1172 # with MemberId == GroupId from results
1173 $self->SUPER::Limit(
1174 LEFTJOIN => $group_members,
1177 VALUE => "$group_members.MemberId",
1180 my $users = $self->Join(
1182 ALIAS1 => $group_members,
1183 FIELD1 => 'MemberId',
1187 return ($groups, $group_members, $users);
1190 =head2 _WatcherMembershipLimit
1192 Handle watcher membership limits, i.e. whether the watcher belongs to a
1193 specific group or not.
1196 1: Field to query on
1198 SELECT DISTINCT main.*
1202 CachedGroupMembers CachedGroupMembers_2,
1205 (main.EffectiveId = main.id)
1207 (main.Status != 'deleted')
1209 (main.Type = 'ticket')
1212 (Users_3.EmailAddress = '22')
1214 (Groups_1.Domain = 'RT::Ticket-Role')
1216 (Groups_1.Type = 'RequestorGroup')
1219 Groups_1.Instance = main.id
1221 Groups_1.id = CachedGroupMembers_2.GroupId
1223 CachedGroupMembers_2.MemberId = Users_3.id
1224 ORDER BY main.id ASC
1229 sub _WatcherMembershipLimit {
1230 my ( $self, $field, $op, $value, @rest ) = @_;
1235 my $groups = $self->NewAlias('Groups');
1236 my $groupmembers = $self->NewAlias('CachedGroupMembers');
1237 my $users = $self->NewAlias('Users');
1238 my $memberships = $self->NewAlias('CachedGroupMembers');
1240 if ( ref $field ) { # gross hack
1241 my @bundle = @$field;
1243 for my $chunk (@bundle) {
1244 ( $field, $op, $value, @rest ) = @$chunk;
1246 ALIAS => $memberships,
1257 ALIAS => $memberships,
1265 # Tie to groups for tickets we care about
1269 VALUE => 'RT::Ticket-Role',
1270 ENTRYAGGREGATOR => 'AND'
1275 FIELD1 => 'Instance',
1282 # If we care about which sort of watcher
1283 my $meta = $FIELD_METADATA{$field};
1284 my $type = ( defined $meta->[1] ? $meta->[1] : undef );
1291 ENTRYAGGREGATOR => 'AND'
1298 ALIAS2 => $groupmembers,
1303 ALIAS1 => $groupmembers,
1304 FIELD1 => 'MemberId',
1310 ALIAS => $groupmembers,
1311 FIELD => 'Disabled',
1316 ALIAS1 => $memberships,
1317 FIELD1 => 'MemberId',
1323 ALIAS => $memberships,
1324 FIELD => 'Disabled',
1333 =head2 _CustomFieldDecipher
1335 Try and turn a CF descriptor into (cfid, cfname) object pair.
1339 sub _CustomFieldDecipher {
1340 my ($self, $string) = @_;
1342 my ($queue, $field, $column) = ($string =~ /^(?:(.+?)\.)?{(.+)}(?:\.(Content|LargeContent))?$/);
1343 $field ||= ($string =~ /^{(.*?)}$/)[0] || $string;
1347 my $q = RT::Queue->new( $self->CurrentUser );
1351 # $queue = $q->Name; # should we normalize the queue?
1352 $cf = $q->CustomField( $field );
1355 $RT::Logger->warning("Queue '$queue' doesn't exist, parsed from '$string'");
1359 elsif ( $field =~ /\D/ ) {
1361 my $cfs = RT::CustomFields->new( $self->CurrentUser );
1362 $cfs->Limit( FIELD => 'Name', VALUE => $field );
1363 $cfs->LimitToLookupType('RT::Queue-RT::Ticket');
1365 # if there is more then one field the current user can
1366 # see with the same name then we shouldn't return cf object
1367 # as we don't know which one to use
1370 $cf = undef if $cfs->Next;
1374 $cf = RT::CustomField->new( $self->CurrentUser );
1375 $cf->Load( $field );
1378 return ($queue, $field, $cf, $column);
1381 =head2 _CustomFieldJoin
1383 Factor out the Join of custom fields so we can use it for sorting too
1387 sub _CustomFieldJoin {
1388 my ($self, $cfkey, $cfid, $field) = @_;
1389 # Perform one Join per CustomField
1390 if ( $self->{_sql_object_cfv_alias}{$cfkey} ||
1391 $self->{_sql_cf_alias}{$cfkey} )
1393 return ( $self->{_sql_object_cfv_alias}{$cfkey},
1394 $self->{_sql_cf_alias}{$cfkey} );
1397 my ($TicketCFs, $CFs);
1399 $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1403 TABLE2 => 'ObjectCustomFieldValues',
1404 FIELD2 => 'ObjectId',
1406 $self->SUPER::Limit(
1407 LEFTJOIN => $TicketCFs,
1408 FIELD => 'CustomField',
1410 ENTRYAGGREGATOR => 'AND'
1414 my $ocfalias = $self->Join(
1417 TABLE2 => 'ObjectCustomFields',
1418 FIELD2 => 'ObjectId',
1421 $self->SUPER::Limit(
1422 LEFTJOIN => $ocfalias,
1423 ENTRYAGGREGATOR => 'OR',
1424 FIELD => 'ObjectId',
1428 $CFs = $self->{_sql_cf_alias}{$cfkey} = $self->Join(
1430 ALIAS1 => $ocfalias,
1431 FIELD1 => 'CustomField',
1432 TABLE2 => 'CustomFields',
1435 $self->SUPER::Limit(
1437 ENTRYAGGREGATOR => 'AND',
1438 FIELD => 'LookupType',
1439 VALUE => 'RT::Queue-RT::Ticket',
1441 $self->SUPER::Limit(
1443 ENTRYAGGREGATOR => 'AND',
1448 $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1452 TABLE2 => 'ObjectCustomFieldValues',
1453 FIELD2 => 'CustomField',
1455 $self->SUPER::Limit(
1456 LEFTJOIN => $TicketCFs,
1457 FIELD => 'ObjectId',
1460 ENTRYAGGREGATOR => 'AND',
1463 $self->SUPER::Limit(
1464 LEFTJOIN => $TicketCFs,
1465 FIELD => 'ObjectType',
1466 VALUE => 'RT::Ticket',
1467 ENTRYAGGREGATOR => 'AND'
1469 $self->SUPER::Limit(
1470 LEFTJOIN => $TicketCFs,
1471 FIELD => 'Disabled',
1474 ENTRYAGGREGATOR => 'AND'
1477 return ($TicketCFs, $CFs);
1480 =head2 _CustomFieldLimit
1482 Limit based on CustomFields
1489 use Regexp::Common qw(RE_net_IPv4);
1490 use Regexp::Common::net::CIDR;
1493 sub _CustomFieldLimit {
1494 my ( $self, $_field, $op, $value, %rest ) = @_;
1496 my $field = $rest{'SUBKEY'} || die "No field specified";
1498 # For our sanity, we can only limit on one queue at a time
1500 my ($queue, $cfid, $cf, $column);
1501 ($queue, $field, $cf, $column) = $self->_CustomFieldDecipher( $field );
1502 $cfid = $cf ? $cf->id : 0 ;
1504 # If we're trying to find custom fields that don't match something, we
1505 # want tickets where the custom field has no value at all. Note that
1506 # we explicitly don't include the "IS NULL" case, since we would
1507 # otherwise end up with a redundant clause.
1509 my ($negative_op, $null_op, $inv_op, $range_op)
1510 = $self->ClassifySQLOperation( $op );
1513 return @_ unless RT->Config->Get('DatabaseType') eq 'Oracle';
1516 return %args unless $args{'FIELD'} eq 'LargeContent';
1518 my $op = $args{'OPERATOR'};
1520 $args{'OPERATOR'} = 'MATCHES';
1522 elsif ( $op eq '!=' ) {
1523 $args{'OPERATOR'} = 'NOT MATCHES';
1525 elsif ( $op =~ /^[<>]=?$/ ) {
1526 $args{'FUNCTION'} = "TO_CHAR( $args{'ALIAS'}.LargeContent )";
1531 if ( $cf && $cf->Type eq 'IPAddress' ) {
1532 my $parsed = RT::ObjectCustomFieldValue->ParseIP($value);
1537 $RT::Logger->warn("$value is not a valid IPAddress");
1541 if ( $cf && $cf->Type eq 'IPAddressRange' ) {
1543 if ( $value =~ /^\s*$RE{net}{CIDR}{IPv4}{-keep}\s*$/o ) {
1545 # convert incomplete 192.168/24 to 192.168.0.0/24 format
1547 join( '.', map $_ || 0, ( split /\./, $1 )[ 0 .. 3 ] ) . "/$2"
1551 my ( $start_ip, $end_ip ) =
1552 RT::ObjectCustomFieldValue->ParseIPRange($value);
1553 if ( $start_ip && $end_ip ) {
1554 if ( $op =~ /^([<>])=?$/ ) {
1555 my $is_less = $1 eq '<' ? 1 : 0;
1564 $value = join '-', $start_ip, $end_ip;
1568 $RT::Logger->warn("$value is not a valid IPAddressRange");
1572 my $single_value = !$cf || !$cfid || $cf->SingleValue;
1574 my $cfkey = $cfid ? $cfid : "$queue.$field";
1576 if ( $null_op && !$column ) {
1577 # IS[ NOT] NULL without column is the same as has[ no] any CF value,
1578 # we can reuse our default joins for this operation
1579 # with column specified we have different situation
1580 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1583 ALIAS => $TicketCFs,
1592 OPERATOR => 'IS NOT',
1595 ENTRYAGGREGATOR => 'AND',
1599 elsif ( $op !~ /^[<>]=?$/ && ( $cf && $cf->Type eq 'IPAddressRange')) {
1601 my ($start_ip, $end_ip) = split /-/, $value;
1604 if ( $op !~ /NOT|!=|<>/i ) { # positive equation
1605 $self->_CustomFieldLimit(
1606 'CF', '<=', $end_ip, %rest,
1607 SUBKEY => $rest{'SUBKEY'}. '.Content',
1609 $self->_CustomFieldLimit(
1610 'CF', '>=', $start_ip, %rest,
1611 SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1612 ENTRYAGGREGATOR => 'AND',
1614 # as well limit borders so DB optimizers can use better
1615 # estimations and scan less rows
1616 # have to disable this tweak because of ipv6
1617 # $self->_CustomFieldLimit(
1618 # $field, '>=', '000.000.000.000', %rest,
1619 # SUBKEY => $rest{'SUBKEY'}. '.Content',
1620 # ENTRYAGGREGATOR => 'AND',
1622 # $self->_CustomFieldLimit(
1623 # $field, '<=', '255.255.255.255', %rest,
1624 # SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1625 # ENTRYAGGREGATOR => 'AND',
1628 else { # negative equation
1629 $self->_CustomFieldLimit($field, '>', $end_ip, %rest);
1630 $self->_CustomFieldLimit(
1631 $field, '<', $start_ip, %rest,
1632 SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1633 ENTRYAGGREGATOR => 'OR',
1635 # TODO: as well limit borders so DB optimizers can use better
1636 # estimations and scan less rows, but it's harder to do
1637 # as we have OR aggregator
1641 elsif ( !$negative_op || $single_value ) {
1642 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++ if !$single_value && !$range_op;
1643 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1650 # if column is defined then deal only with it
1651 # otherwise search in Content and in LargeContent
1653 $self->_SQLLimit( $fix_op->(
1654 ALIAS => $TicketCFs,
1666 # need special treatment for Date
1667 if ( $cf and $cf->Type eq 'DateTime' and $op eq '=' ) {
1669 if ( $value =~ /:/ ) {
1670 # there is time speccified.
1671 my $date = RT::Date->new( $self->CurrentUser );
1672 $date->Set( Format => 'unknown', Value => $value );
1674 ALIAS => $TicketCFs,
1677 VALUE => $date->ISO,
1682 # no time specified, that means we want everything on a
1683 # particular day. in the database, we need to check for >
1684 # and < the edges of that day.
1685 my $date = RT::Date->new( $self->CurrentUser );
1686 $date->Set( Format => 'unknown', Value => $value );
1687 $date->SetToMidnight( Timezone => 'server' );
1688 my $daystart = $date->ISO;
1690 my $dayend = $date->ISO;
1695 ALIAS => $TicketCFs,
1703 ALIAS => $TicketCFs,
1708 ENTRYAGGREGATOR => 'AND',
1714 elsif ( $op eq '=' || $op eq '!=' || $op eq '<>' ) {
1715 if ( length( Encode::encode_utf8($value) ) < 256 ) {
1717 ALIAS => $TicketCFs,
1728 ALIAS => $TicketCFs,
1732 ENTRYAGGREGATOR => 'OR'
1735 ALIAS => $TicketCFs,
1739 ENTRYAGGREGATOR => 'OR'
1742 $self->_SQLLimit( $fix_op->(
1743 ALIAS => $TicketCFs,
1744 FIELD => 'LargeContent',
1747 ENTRYAGGREGATOR => 'AND',
1754 ALIAS => $TicketCFs,
1765 ALIAS => $TicketCFs,
1769 ENTRYAGGREGATOR => 'OR'
1772 ALIAS => $TicketCFs,
1776 ENTRYAGGREGATOR => 'OR'
1779 $self->_SQLLimit( $fix_op->(
1780 ALIAS => $TicketCFs,
1781 FIELD => 'LargeContent',
1784 ENTRYAGGREGATOR => 'AND',
1791 # XXX: if we join via CustomFields table then
1792 # because of order of left joins we get NULLs in
1793 # CF table and then get nulls for those records
1794 # in OCFVs table what result in wrong results
1795 # as decifer method now tries to load a CF then
1796 # we fall into this situation only when there
1797 # are more than one CF with the name in the DB.
1798 # the same thing applies to order by call.
1799 # TODO: reorder joins T <- OCFVs <- CFs <- OCFs if
1800 # we want treat IS NULL as (not applies or has
1805 OPERATOR => 'IS NOT',
1808 ENTRYAGGREGATOR => 'AND',
1814 ALIAS => $TicketCFs,
1815 FIELD => $column || 'Content',
1819 ENTRYAGGREGATOR => 'OR',
1827 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++;
1828 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1831 $op =~ s/!|NOT\s+//i;
1833 # if column is defined then deal only with it
1834 # otherwise search in Content and in LargeContent
1836 $self->SUPER::Limit( $fix_op->(
1837 LEFTJOIN => $TicketCFs,
1838 ALIAS => $TicketCFs,
1846 $self->SUPER::Limit(
1847 LEFTJOIN => $TicketCFs,
1848 ALIAS => $TicketCFs,
1857 ALIAS => $TicketCFs,
1866 sub _HasAttributeLimit {
1867 my ( $self, $field, $op, $value, %rest ) = @_;
1869 my $alias = $self->Join(
1873 TABLE2 => 'Attributes',
1874 FIELD2 => 'ObjectId',
1876 $self->SUPER::Limit(
1878 FIELD => 'ObjectType',
1879 VALUE => 'RT::Ticket',
1880 ENTRYAGGREGATOR => 'AND'
1882 $self->SUPER::Limit(
1887 ENTRYAGGREGATOR => 'AND'
1893 OPERATOR => $FIELD_METADATA{$field}->[1]? 'IS NOT': 'IS',
1899 # End Helper Functions
1901 # End of SQL Stuff -------------------------------------------------
1904 =head2 OrderByCols ARRAY
1906 A modified version of the OrderBy method which automatically joins where
1907 C<ALIAS> is set to the name of a watcher type.
1918 foreach my $row (@args) {
1919 if ( $row->{ALIAS} ) {
1923 if ( $row->{FIELD} !~ /\./ ) {
1924 my $meta = $self->FIELDS->{ $row->{FIELD} };
1930 if ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'Queue' ) {
1931 my $alias = $self->Join(
1934 FIELD1 => $row->{'FIELD'},
1938 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1939 } elsif ( ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'User' )
1940 || ( $meta->[0] eq 'WATCHERFIELD' && ($meta->[1]||'') eq 'Owner' )
1942 my $alias = $self->Join(
1945 FIELD1 => $row->{'FIELD'},
1949 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1956 my ( $field, $subkey ) = split /\./, $row->{FIELD}, 2;
1957 my $meta = $self->FIELDS->{$field};
1958 if ( defined $meta->[0] && $meta->[0] eq 'WATCHERFIELD' ) {
1959 # cache alias as we want to use one alias per watcher type for sorting
1960 my $users = $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] };
1962 $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] }
1963 = $users = ( $self->_WatcherJoin( $meta->[1] ) )[2];
1965 push @res, { %$row, ALIAS => $users, FIELD => $subkey };
1966 } elsif ( defined $meta->[0] && $meta->[0] eq 'CUSTOMFIELD' ) {
1967 my ($queue, $field, $cf_obj, $column) = $self->_CustomFieldDecipher( $subkey );
1968 my $cfkey = $cf_obj ? $cf_obj->id : "$queue.$field";
1969 $cfkey .= ".ordering" if !$cf_obj || ($cf_obj->MaxValues||0) != 1;
1970 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, ($cf_obj ?$cf_obj->id :0) , $field );
1971 # this is described in _CustomFieldLimit
1975 OPERATOR => 'IS NOT',
1978 ENTRYAGGREGATOR => 'AND',
1981 # For those cases where we are doing a join against the
1982 # CF name, and don't have a CFid, use Unique to make sure
1983 # we don't show duplicate tickets. NOTE: I'm pretty sure
1984 # this will stay mixed in for the life of the
1985 # class/package, and not just for the life of the object.
1986 # Potential performance issue.
1987 require DBIx::SearchBuilder::Unique;
1988 DBIx::SearchBuilder::Unique->import;
1990 my $CFvs = $self->Join(
1992 ALIAS1 => $TicketCFs,
1993 FIELD1 => 'CustomField',
1994 TABLE2 => 'CustomFieldValues',
1995 FIELD2 => 'CustomField',
1997 $self->SUPER::Limit(
2001 VALUE => $TicketCFs . ".Content",
2002 ENTRYAGGREGATOR => 'AND'
2005 push @res, { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' };
2006 push @res, { %$row, ALIAS => $TicketCFs, FIELD => 'Content' };
2007 } elsif ( $field eq "Custom" && $subkey eq "Ownership") {
2008 # PAW logic is "reversed"
2010 if (exists $row->{ORDER} ) {
2011 my $o = $row->{ORDER};
2012 delete $row->{ORDER};
2013 $order = "DESC" if $o =~ /asc/i;
2016 # Ticket.Owner 1 0 X
2017 # Unowned Tickets 0 1 X
2020 foreach my $uid ( $self->CurrentUser->Id, RT->Nobody->Id ) {
2021 if ( RT->Config->Get('DatabaseType') eq 'Oracle' ) {
2022 my $f = ($row->{'ALIAS'} || 'main') .'.Owner';
2027 FUNCTION => "CASE WHEN $f=$uid THEN 1 ELSE 0 END",
2034 FUNCTION => "Owner=$uid",
2040 push @res, { %$row, FIELD => "Priority", ORDER => $order } ;
2042 } elsif ( $field eq 'Customer' ) { #Freeside
2043 # OrderBy(FIELD => expression) doesn't work, it has to be
2044 # an actual field, so we have to do the join even if sorting
2046 my $custalias = $self->JoinToCustomer;
2047 my $cust_field = lc($subkey);
2048 if ( !$cust_field or $cust_field eq 'number' ) {
2049 $cust_field = 'custnum';
2051 elsif ( $cust_field eq 'name' ) {
2052 $cust_field = "COALESCE( $custalias.company,
2053 $custalias.last || ', ' || $custalias.first
2056 else { # order by cust_main fields directly: 'Customer.agentnum'
2057 $cust_field = $subkey;
2059 push @res, { %$row, ALIAS => $custalias, FIELD => $cust_field };
2061 } elsif ( $field eq 'Service' ) {
2063 my $svcalias = $self->JoinToService;
2064 my $svc_field = lc($subkey);
2065 if ( !$svc_field or $svc_field eq 'number' ) {
2066 $svc_field = 'svcnum';
2068 push @res, { %$row, ALIAS => $svcalias, FIELD => $svc_field };
2076 return $self->SUPER::OrderByCols(@res);
2081 sub JoinToCustLinks {
2082 # Set up join to links (id = localbase),
2083 # limit link type to 'MemberOf',
2084 # and target value to any Freeside custnum URI.
2085 # Return the linkalias for further join/limit action,
2086 # and an sql expression to retrieve the custnum.
2088 # only join once for each RT::Tickets object
2089 my $linkalias = $self->{cust_main_linkalias};
2091 $linkalias = $self->Join(
2096 FIELD2 => 'LocalBase',
2098 $self->SUPER::Limit(
2099 LEFTJOIN => $linkalias,
2102 VALUE => 'fsck.com-rt://%/ticket/%',
2104 $self->SUPER::Limit(
2105 LEFTJOIN => $linkalias,
2108 VALUE => 'MemberOf',
2110 $self->SUPER::Limit(
2111 LEFTJOIN => $linkalias,
2113 OPERATOR => 'STARTSWITH',
2114 VALUE => 'freeside://freeside/cust_main/',
2116 $self->{cust_main_linkalias} = $linkalias;
2118 my $custnum_sql = "CAST(SUBSTR($linkalias.Target,31) AS ";
2119 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
2120 $custnum_sql .= 'SIGNED INTEGER)';
2123 $custnum_sql .= 'INTEGER)';
2125 return ($linkalias, $custnum_sql);
2128 sub JoinToCustomer {
2130 my ($linkalias, $custnum_sql) = $self->JoinToCustLinks;
2131 # don't reuse this join, though--negative queries need
2133 my $custalias = $self->Join(
2135 EXPRESSION => $custnum_sql,
2136 TABLE2 => 'cust_main',
2137 FIELD2 => 'custnum',
2142 sub JoinToSvcLinks {
2144 my $linkalias = $self->{cust_svc_linkalias};
2146 $linkalias = $self->Join(
2151 FIELD2 => 'LocalBase',
2153 $self->SUPER::Limit(
2154 LEFTJOIN => $linkalias,
2157 VALUE => 'fsck.com-rt://%/ticket/%',
2160 $self->SUPER::Limit(
2161 LEFTJOIN => $linkalias,
2164 VALUE => 'MemberOf',
2166 $self->SUPER::Limit(
2167 LEFTJOIN => $linkalias,
2169 OPERATOR => 'STARTSWITH',
2170 VALUE => 'freeside://freeside/cust_svc/',
2172 $self->{cust_svc_linkalias} = $linkalias;
2174 my $svcnum_sql = "CAST(SUBSTR($linkalias.Target,30) AS ";
2175 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
2176 $svcnum_sql .= 'SIGNED INTEGER)';
2179 $svcnum_sql .= 'INTEGER)';
2181 return ($linkalias, $svcnum_sql);
2186 my ($linkalias, $svcnum_sql) = $self->JoinToSvcLinks;
2189 EXPRESSION => $svcnum_sql,
2190 TABLE2 => 'cust_svc',
2195 # This creates an alternate left join path to cust_main via cust_svc.
2196 # _FreesideFieldLimit needs to add this as a separate, independent join
2197 # and include all tickets that have a matching cust_main record via
2199 sub JoinToCustomerViaService {
2201 my $svcalias = $self->JoinToService;
2202 my $cust_pkg = $self->Join(
2204 ALIAS1 => $svcalias,
2206 TABLE2 => 'cust_pkg',
2209 my $cust_main = $self->Join(
2211 ALIAS1 => $cust_pkg,
2212 FIELD1 => 'custnum',
2213 TABLE2 => 'cust_main',
2214 FIELD2 => 'custnum',
2219 sub _FreesideFieldLimit {
2220 my ( $self, $field, $op, $value, %rest ) = @_;
2221 my $is_negative = 0;
2222 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
2223 # if the op is negative, do the join as though
2224 # the op were positive, then accept only records
2225 # where the right-side join key is null.
2227 $op = '=' if $op eq '!=';
2231 my (@alias, $table2, $subfield, $pkey);
2232 if ( $field eq 'Customer' ) {
2233 push @alias, $self->JoinToCustomer;
2234 push @alias, $self->JoinToCustomerViaService;
2237 elsif ( $field eq 'Service' ) {
2238 push @alias, $self->JoinToService;
2242 die "malformed Freeside query: $field";
2245 $subfield = $rest{SUBKEY} || $pkey;
2246 # compound subkey: separate into table name and field in that table
2247 # (must be linked by custnum)
2248 $subfield = lc($subfield);
2249 ($table2, $subfield) = ($1, $2) if $subfield =~ /^(\w+)?\.(\w+)$/;
2250 $subfield = $pkey if $subfield eq 'number';
2252 # if it's compound, create a join from cust_main or cust_svc to that
2253 # table, using custnum or svcnum, and Limit on that table instead.
2255 foreach my $a (@alias) {
2266 # do the actual Limit
2267 $self->SUPER::Limit(
2272 ENTRYAGGREGATOR => 'AND',
2273 # no SUBCLAUSE needed, limits on different aliases across left joins
2274 # are inherently independent
2277 # then, since it's a left join, exclude tickets for which there is now
2278 # no matching record in the table we just limited on. (Or where there
2279 # is a matching record, if $is_negative.)
2280 # For a cust_main query (where there are two different aliases), this
2281 # will produce a subclause: "cust_main_1.custnum IS NOT NULL OR
2282 # cust_main_2.custnum IS NOT NULL" (or "IS NULL AND..." for a negative
2289 OPERATOR => $is_negative ? 'IS' : 'IS NOT',
2292 ENTRYAGGREGATOR => $is_negative ? 'AND' : 'OR',
2293 SUBCLAUSE => 'fs_limit',
2298 foreach my $_SQLLimit (@_SQLLimit) {
2299 $self->_SQLLimit( %$_SQLLimit);
2309 Takes a paramhash with the fields FIELD, OPERATOR, VALUE and DESCRIPTION
2310 Generally best called from LimitFoo methods
2320 DESCRIPTION => undef,
2323 $args{'DESCRIPTION'} = $self->loc(
2324 "[_1] [_2] [_3]", $args{'FIELD'},
2325 $args{'OPERATOR'}, $args{'VALUE'}
2327 if ( !defined $args{'DESCRIPTION'} );
2329 my $index = $self->_NextIndex;
2331 # make the TicketRestrictions hash the equivalent of whatever we just passed in;
2333 %{ $self->{'TicketRestrictions'}{$index} } = %args;
2335 $self->{'RecalcTicketLimits'} = 1;
2337 # If we're looking at the effective id, we don't want to append the other clause
2338 # which limits us to tickets where id = effective id
2339 if ( $args{'FIELD'} eq 'EffectiveId'
2340 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
2342 $self->{'looking_at_effective_id'} = 1;
2345 if ( $args{'FIELD'} eq 'Type'
2346 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
2348 $self->{'looking_at_type'} = 1;
2359 LimitQueue takes a paramhash with the fields OPERATOR and VALUE.
2360 OPERATOR is one of = or !=. (It defaults to =).
2361 VALUE is a queue id or Name.
2374 #TODO VALUE should also take queue objects
2375 if ( defined $args{'VALUE'} && $args{'VALUE'} !~ /^\d+$/ ) {
2376 my $queue = RT::Queue->new( $self->CurrentUser );
2377 $queue->Load( $args{'VALUE'} );
2378 $args{'VALUE'} = $queue->Id;
2381 # What if they pass in an Id? Check for isNum() and convert to
2384 #TODO check for a valid queue here
2388 VALUE => $args{'VALUE'},
2389 OPERATOR => $args{'OPERATOR'},
2390 DESCRIPTION => join(
2391 ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{'VALUE'},
2401 Takes a paramhash with the fields OPERATOR and VALUE.
2402 OPERATOR is one of = or !=.
2405 RT adds Status != 'deleted' until object has
2406 allow_deleted_search internal property set.
2407 $tickets->{'allow_deleted_search'} = 1;
2408 $tickets->LimitStatus( VALUE => 'deleted' );
2420 VALUE => $args{'VALUE'},
2421 OPERATOR => $args{'OPERATOR'},
2422 DESCRIPTION => join( ' ',
2423 $self->loc('Status'), $args{'OPERATOR'},
2424 $self->loc( $args{'VALUE'} ) ),
2432 If called, this search will not automatically limit the set of results found
2433 to tickets of type "Ticket". Tickets of other types, such as "project" and
2434 "approval" will be found.
2441 # Instead of faking a Limit that later gets ignored, fake up the
2442 # fact that we're already looking at type, so that the check in
2443 # Tickets_SQL/FromSQL goes down the right branch
2445 # $self->LimitType(VALUE => '__any');
2446 $self->{looking_at_type} = 1;
2453 Takes a paramhash with the fields OPERATOR and VALUE.
2454 OPERATOR is one of = or !=, it defaults to "=".
2455 VALUE is a string to search for in the type of the ticket.
2470 VALUE => $args{'VALUE'},
2471 OPERATOR => $args{'OPERATOR'},
2472 DESCRIPTION => join( ' ',
2473 $self->loc('Type'), $args{'OPERATOR'}, $args{'Limit'}, ),
2483 Takes a paramhash with the fields OPERATOR and VALUE.
2484 OPERATOR is one of = or !=.
2485 VALUE is a string to search for in the subject of the ticket.
2494 VALUE => $args{'VALUE'},
2495 OPERATOR => $args{'OPERATOR'},
2496 DESCRIPTION => join( ' ',
2497 $self->loc('Subject'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2503 # Things that can be > < = !=
2508 Takes a paramhash with the fields OPERATOR and VALUE.
2509 OPERATOR is one of =, >, < or !=.
2510 VALUE is a ticket Id to search for
2523 VALUE => $args{'VALUE'},
2524 OPERATOR => $args{'OPERATOR'},
2526 join( ' ', $self->loc('Id'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2532 =head2 LimitPriority
2534 Takes a paramhash with the fields OPERATOR and VALUE.
2535 OPERATOR is one of =, >, < or !=.
2536 VALUE is a value to match the ticket\'s priority against
2544 FIELD => 'Priority',
2545 VALUE => $args{'VALUE'},
2546 OPERATOR => $args{'OPERATOR'},
2547 DESCRIPTION => join( ' ',
2548 $self->loc('Priority'),
2549 $args{'OPERATOR'}, $args{'VALUE'}, ),
2555 =head2 LimitInitialPriority
2557 Takes a paramhash with the fields OPERATOR and VALUE.
2558 OPERATOR is one of =, >, < or !=.
2559 VALUE is a value to match the ticket\'s initial priority against
2564 sub LimitInitialPriority {
2568 FIELD => 'InitialPriority',
2569 VALUE => $args{'VALUE'},
2570 OPERATOR => $args{'OPERATOR'},
2571 DESCRIPTION => join( ' ',
2572 $self->loc('Initial Priority'), $args{'OPERATOR'},
2579 =head2 LimitFinalPriority
2581 Takes a paramhash with the fields OPERATOR and VALUE.
2582 OPERATOR is one of =, >, < or !=.
2583 VALUE is a value to match the ticket\'s final priority against
2587 sub LimitFinalPriority {
2591 FIELD => 'FinalPriority',
2592 VALUE => $args{'VALUE'},
2593 OPERATOR => $args{'OPERATOR'},
2594 DESCRIPTION => join( ' ',
2595 $self->loc('Final Priority'), $args{'OPERATOR'},
2602 =head2 LimitTimeWorked
2604 Takes a paramhash with the fields OPERATOR and VALUE.
2605 OPERATOR is one of =, >, < or !=.
2606 VALUE is a value to match the ticket's TimeWorked attribute
2610 sub LimitTimeWorked {
2614 FIELD => 'TimeWorked',
2615 VALUE => $args{'VALUE'},
2616 OPERATOR => $args{'OPERATOR'},
2617 DESCRIPTION => join( ' ',
2618 $self->loc('Time Worked'),
2619 $args{'OPERATOR'}, $args{'VALUE'}, ),
2625 =head2 LimitTimeLeft
2627 Takes a paramhash with the fields OPERATOR and VALUE.
2628 OPERATOR is one of =, >, < or !=.
2629 VALUE is a value to match the ticket's TimeLeft attribute
2637 FIELD => 'TimeLeft',
2638 VALUE => $args{'VALUE'},
2639 OPERATOR => $args{'OPERATOR'},
2640 DESCRIPTION => join( ' ',
2641 $self->loc('Time Left'),
2642 $args{'OPERATOR'}, $args{'VALUE'}, ),
2652 Takes a paramhash with the fields OPERATOR and VALUE.
2653 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2654 VALUE is a string to search for in the body of the ticket
2663 VALUE => $args{'VALUE'},
2664 OPERATOR => $args{'OPERATOR'},
2665 DESCRIPTION => join( ' ',
2666 $self->loc('Ticket content'), $args{'OPERATOR'},
2673 =head2 LimitFilename
2675 Takes a paramhash with the fields OPERATOR and VALUE.
2676 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2677 VALUE is a string to search for in the body of the ticket
2685 FIELD => 'Filename',
2686 VALUE => $args{'VALUE'},
2687 OPERATOR => $args{'OPERATOR'},
2688 DESCRIPTION => join( ' ',
2689 $self->loc('Attachment filename'), $args{'OPERATOR'},
2695 =head2 LimitContentType
2697 Takes a paramhash with the fields OPERATOR and VALUE.
2698 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2699 VALUE is a content type to search ticket attachments for
2703 sub LimitContentType {
2707 FIELD => 'ContentType',
2708 VALUE => $args{'VALUE'},
2709 OPERATOR => $args{'OPERATOR'},
2710 DESCRIPTION => join( ' ',
2711 $self->loc('Ticket content type'), $args{'OPERATOR'},
2722 Takes a paramhash with the fields OPERATOR and VALUE.
2723 OPERATOR is one of = or !=.
2735 my $owner = RT::User->new( $self->CurrentUser );
2736 $owner->Load( $args{'VALUE'} );
2738 # FIXME: check for a valid $owner
2741 VALUE => $args{'VALUE'},
2742 OPERATOR => $args{'OPERATOR'},
2743 DESCRIPTION => join( ' ',
2744 $self->loc('Owner'), $args{'OPERATOR'}, $owner->Name(), ),
2754 Takes a paramhash with the fields OPERATOR, TYPE and VALUE.
2755 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2756 VALUE is a value to match the ticket\'s watcher email addresses against
2757 TYPE is the sort of watchers you want to match against. Leave it undef if you want to search all of them
2771 #build us up a description
2772 my ( $watcher_type, $desc );
2773 if ( $args{'TYPE'} ) {
2774 $watcher_type = $args{'TYPE'};
2777 $watcher_type = "Watcher";
2781 FIELD => $watcher_type,
2782 VALUE => $args{'VALUE'},
2783 OPERATOR => $args{'OPERATOR'},
2784 TYPE => $args{'TYPE'},
2785 DESCRIPTION => join( ' ',
2786 $self->loc($watcher_type),
2787 $args{'OPERATOR'}, $args{'VALUE'}, ),
2796 =head2 LimitLinkedTo
2798 LimitLinkedTo takes a paramhash with two fields: TYPE and TARGET
2799 TYPE limits the sort of link we want to search on
2801 TYPE = { RefersTo, MemberOf, DependsOn }
2803 TARGET is the id or URI of the TARGET of the link
2817 FIELD => 'LinkedTo',
2819 TARGET => $args{'TARGET'},
2820 TYPE => $args{'TYPE'},
2821 DESCRIPTION => $self->loc(
2822 "Tickets [_1] by [_2]",
2823 $self->loc( $args{'TYPE'} ),
2826 OPERATOR => $args{'OPERATOR'},
2832 =head2 LimitLinkedFrom
2834 LimitLinkedFrom takes a paramhash with two fields: TYPE and BASE
2835 TYPE limits the sort of link we want to search on
2838 BASE is the id or URI of the BASE of the link
2842 sub LimitLinkedFrom {
2851 # translate RT2 From/To naming to RT3 TicketSQL naming
2852 my %fromToMap = qw(DependsOn DependentOn
2854 RefersTo ReferredToBy);
2856 my $type = $args{'TYPE'};
2857 $type = $fromToMap{$type} if exists( $fromToMap{$type} );
2860 FIELD => 'LinkedTo',
2862 BASE => $args{'BASE'},
2864 DESCRIPTION => $self->loc(
2865 "Tickets [_1] [_2]",
2866 $self->loc( $args{'TYPE'} ),
2869 OPERATOR => $args{'OPERATOR'},
2876 my $ticket_id = shift;
2877 return $self->LimitLinkedTo(
2879 TARGET => $ticket_id,
2885 sub LimitHasMember {
2887 my $ticket_id = shift;
2888 return $self->LimitLinkedFrom(
2890 BASE => "$ticket_id",
2891 TYPE => 'HasMember',
2898 sub LimitDependsOn {
2900 my $ticket_id = shift;
2901 return $self->LimitLinkedTo(
2903 TARGET => $ticket_id,
2904 TYPE => 'DependsOn',
2911 sub LimitDependedOnBy {
2913 my $ticket_id = shift;
2914 return $self->LimitLinkedFrom(
2917 TYPE => 'DependentOn',
2926 my $ticket_id = shift;
2927 return $self->LimitLinkedTo(
2929 TARGET => $ticket_id,
2937 sub LimitReferredToBy {
2939 my $ticket_id = shift;
2940 return $self->LimitLinkedFrom(
2943 TYPE => 'ReferredToBy',
2951 =head2 LimitDate (FIELD => 'DateField', OPERATOR => $oper, VALUE => $ISODate)
2953 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2955 OPERATOR is one of > or <
2956 VALUE is a date and time in ISO format in GMT
2957 FIELD is one of Starts, Started, Told, Created, Resolved, LastUpdated
2959 There are also helper functions of the form LimitFIELD that eliminate
2960 the need to pass in a FIELD argument.
2974 #Set the description if we didn't get handed it above
2975 unless ( $args{'DESCRIPTION'} ) {
2976 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2977 . $args{'OPERATOR'} . " "
2978 . $args{'VALUE'} . " GMT";
2981 $self->Limit(%args);
2988 $self->LimitDate( FIELD => 'Created', @_ );
2993 $self->LimitDate( FIELD => 'Due', @_ );
2999 $self->LimitDate( FIELD => 'Starts', @_ );
3005 $self->LimitDate( FIELD => 'Started', @_ );
3010 $self->LimitDate( FIELD => 'Resolved', @_ );
3015 $self->LimitDate( FIELD => 'Told', @_ );
3018 sub LimitLastUpdated {
3020 $self->LimitDate( FIELD => 'LastUpdated', @_ );
3025 =head2 LimitTransactionDate (OPERATOR => $oper, VALUE => $ISODate)
3027 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
3029 OPERATOR is one of > or <
3030 VALUE is a date and time in ISO format in GMT
3035 sub LimitTransactionDate {
3038 FIELD => 'TransactionDate',
3045 # <20021217042756.GK28744@pallas.fsck.com>
3046 # "Kill It" - Jesse.
3048 #Set the description if we didn't get handed it above
3049 unless ( $args{'DESCRIPTION'} ) {
3050 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
3051 . $args{'OPERATOR'} . " "
3052 . $args{'VALUE'} . " GMT";
3055 $self->Limit(%args);
3062 =head2 LimitCustomField
3064 Takes a paramhash of key/value pairs with the following keys:
3068 =item CUSTOMFIELD - CustomField name or id. If a name is passed, an additional parameter QUEUE may also be passed to distinguish the custom field.
3070 =item OPERATOR - The usual Limit operators
3072 =item VALUE - The value to compare against
3078 sub LimitCustomField {
3082 CUSTOMFIELD => undef,
3084 DESCRIPTION => undef,
3085 FIELD => 'CustomFieldValue',
3090 my $CF = RT::CustomField->new( $self->CurrentUser );
3091 if ( $args{CUSTOMFIELD} =~ /^\d+$/ ) {
3092 $CF->Load( $args{CUSTOMFIELD} );
3095 $CF->LoadByNameAndQueue(
3096 Name => $args{CUSTOMFIELD},
3097 Queue => $args{QUEUE}
3099 $args{CUSTOMFIELD} = $CF->Id;
3102 #If we are looking to compare with a null value.
3103 if ( $args{'OPERATOR'} =~ /^is$/i ) {
3104 $args{'DESCRIPTION'}
3105 ||= $self->loc( "Custom field [_1] has no value.", $CF->Name );
3107 elsif ( $args{'OPERATOR'} =~ /^is not$/i ) {
3108 $args{'DESCRIPTION'}
3109 ||= $self->loc( "Custom field [_1] has a value.", $CF->Name );
3112 # if we're not looking to compare with a null value
3114 $args{'DESCRIPTION'} ||= $self->loc( "Custom field [_1] [_2] [_3]",
3115 $CF->Name, $args{OPERATOR}, $args{VALUE} );
3118 if ( defined $args{'QUEUE'} && $args{'QUEUE'} =~ /\D/ ) {
3119 my $QueueObj = RT::Queue->new( $self->CurrentUser );
3120 $QueueObj->Load( $args{'QUEUE'} );
3121 $args{'QUEUE'} = $QueueObj->Id;
3123 delete $args{'QUEUE'} unless defined $args{'QUEUE'} && length $args{'QUEUE'};
3126 @rest = ( ENTRYAGGREGATOR => 'AND' )
3127 if ( $CF->Type eq 'SelectMultiple' );
3130 VALUE => $args{VALUE},
3132 .(defined $args{'QUEUE'}? ".{$args{'QUEUE'}}" : '' )
3133 .".{" . $CF->Name . "}",
3134 OPERATOR => $args{OPERATOR},
3139 $self->{'RecalcTicketLimits'} = 1;
3146 Keep track of the counter for the array of restrictions
3152 return ( $self->{'restriction_index'}++ );
3160 $self->{'table'} = "Tickets";
3161 $self->{'RecalcTicketLimits'} = 1;
3162 $self->{'looking_at_effective_id'} = 0;
3163 $self->{'looking_at_type'} = 0;
3164 $self->{'restriction_index'} = 1;
3165 $self->{'primary_key'} = "id";
3166 delete $self->{'items_array'};
3167 delete $self->{'item_map'};
3168 delete $self->{'columns_to_display'};
3169 $self->SUPER::_Init(@_);
3178 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3179 return ( $self->SUPER::Count() );
3185 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3186 return ( $self->SUPER::CountAll() );
3191 =head2 ItemsArrayRef
3193 Returns a reference to the set of all items found in this search
3200 return $self->{'items_array'} if $self->{'items_array'};
3202 my $placeholder = $self->_ItemsCounter;
3203 $self->GotoFirstItem();
3204 while ( my $item = $self->Next ) {
3205 push( @{ $self->{'items_array'} }, $item );
3207 $self->GotoItem($placeholder);
3208 $self->{'items_array'}
3209 = $self->ItemsOrderBy( $self->{'items_array'} );
3211 return $self->{'items_array'};
3214 sub ItemsArrayRefWindow {
3218 my @old = ($self->_ItemsCounter, $self->RowsPerPage, $self->FirstRow+1);
3220 $self->RowsPerPage( $window );
3222 $self->GotoFirstItem;
3225 while ( my $item = $self->Next ) {
3229 $self->RowsPerPage( $old[1] );
3230 $self->FirstRow( $old[2] );
3231 $self->GotoItem( $old[0] );
3240 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3242 my $Ticket = $self->SUPER::Next;
3243 return $Ticket unless $Ticket;
3245 if ( $Ticket->__Value('Status') eq 'deleted'
3246 && !$self->{'allow_deleted_search'} )
3250 elsif ( RT->Config->Get('UseSQLForACLChecks') ) {
3251 # if we found a ticket with this option enabled then
3252 # all tickets we found are ACLed, cache this fact
3253 my $key = join ";:;", $self->CurrentUser->id, 'ShowTicket', 'RT::Ticket-'. $Ticket->id;
3254 $RT::Principal::_ACL_CACHE->set( $key => 1 );
3257 elsif ( $Ticket->CurrentUserHasRight('ShowTicket') ) {
3262 # If the user doesn't have the right to show this ticket
3269 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
3270 return $self->SUPER::_DoSearch( @_ );
3275 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
3276 return $self->SUPER::_DoCount( @_ );
3282 my $cache_key = 'RolesHasRight;:;ShowTicket';
3284 if ( my $cached = $RT::Principal::_ACL_CACHE->fetch( $cache_key ) ) {
3288 my $ACL = RT::ACL->new( RT->SystemUser );
3289 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
3290 $ACL->Limit( FIELD => 'PrincipalType', OPERATOR => '!=', VALUE => 'Group' );
3291 my $principal_alias = $ACL->Join(
3293 FIELD1 => 'PrincipalId',
3294 TABLE2 => 'Principals',
3297 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3300 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
3301 my $role = $ACE->__Value('PrincipalType');
3302 my $type = $ACE->__Value('ObjectType');
3303 if ( $type eq 'RT::System' ) {
3306 elsif ( $type eq 'RT::Queue' ) {
3307 next if $res{ $role } && !ref $res{ $role };
3308 push @{ $res{ $role } ||= [] }, $ACE->__Value('ObjectId');
3311 $RT::Logger->error('ShowTicket right is granted on unsupported object');
3314 $RT::Principal::_ACL_CACHE->set( $cache_key => \%res );
3318 sub _DirectlyCanSeeIn {
3320 my $id = $self->CurrentUser->id;
3322 my $cache_key = 'User-'. $id .';:;ShowTicket;:;DirectlyCanSeeIn';
3323 if ( my $cached = $RT::Principal::_ACL_CACHE->fetch( $cache_key ) ) {
3327 my $ACL = RT::ACL->new( RT->SystemUser );
3328 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
3329 my $principal_alias = $ACL->Join(
3331 FIELD1 => 'PrincipalId',
3332 TABLE2 => 'Principals',
3335 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3336 my $cgm_alias = $ACL->Join(
3338 FIELD1 => 'PrincipalId',
3339 TABLE2 => 'CachedGroupMembers',
3340 FIELD2 => 'GroupId',
3342 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
3343 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
3346 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
3347 my $type = $ACE->__Value('ObjectType');
3348 if ( $type eq 'RT::System' ) {
3349 # If user is direct member of a group that has the right
3350 # on the system then he can see any ticket
3351 $RT::Principal::_ACL_CACHE->set( $cache_key => [-1] );
3354 elsif ( $type eq 'RT::Queue' ) {
3355 push @res, $ACE->__Value('ObjectId');
3358 $RT::Logger->error('ShowTicket right is granted on unsupported object');
3361 $RT::Principal::_ACL_CACHE->set( $cache_key => \@res );
3365 sub CurrentUserCanSee {
3367 return if $self->{'_sql_current_user_can_see_applied'};
3369 return $self->{'_sql_current_user_can_see_applied'} = 1
3370 if $self->CurrentUser->UserObj->HasRight(
3371 Right => 'SuperUser', Object => $RT::System
3374 my $id = $self->CurrentUser->id;
3376 # directly can see in all queues then we have nothing to do
3377 my @direct_queues = $self->_DirectlyCanSeeIn;
3378 return $self->{'_sql_current_user_can_see_applied'} = 1
3379 if @direct_queues && $direct_queues[0] == -1;
3381 my %roles = $self->_RolesCanSee;
3383 my %skip = map { $_ => 1 } @direct_queues;
3384 foreach my $role ( keys %roles ) {
3385 next unless ref $roles{ $role };
3387 my @queues = grep !$skip{$_}, @{ $roles{ $role } };
3389 $roles{ $role } = \@queues;
3391 delete $roles{ $role };
3396 # there is no global watchers, only queues and tickes, if at
3397 # some point we will add global roles then it's gonna blow
3398 # the idea here is that if the right is set globaly for a role
3399 # and user plays this role for a queue directly not a ticket
3400 # then we have to check in advance
3401 if ( my @tmp = grep $_ ne 'Owner' && !ref $roles{ $_ }, keys %roles ) {
3403 my $groups = RT::Groups->new( RT->SystemUser );
3404 $groups->Limit( FIELD => 'Domain', VALUE => 'RT::Queue-Role' );
3406 $groups->Limit( FIELD => 'Type', VALUE => $_ );
3408 my $principal_alias = $groups->Join(
3411 TABLE2 => 'Principals',
3414 $groups->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3415 my $cgm_alias = $groups->Join(
3418 TABLE2 => 'CachedGroupMembers',
3419 FIELD2 => 'GroupId',
3421 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
3422 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
3423 while ( my $group = $groups->Next ) {
3424 push @direct_queues, $group->Instance;
3428 unless ( @direct_queues || keys %roles ) {
3429 $self->SUPER::Limit(
3434 ENTRYAGGREGATOR => 'AND',
3436 return $self->{'_sql_current_user_can_see_applied'} = 1;
3440 my $join_roles = keys %roles;
3441 $join_roles = 0 if $join_roles == 1 && $roles{'Owner'};
3442 my ($role_group_alias, $cgm_alias);
3443 if ( $join_roles ) {
3444 $role_group_alias = $self->_RoleGroupsJoin( New => 1 );
3445 $cgm_alias = $self->_GroupMembersJoin( GroupsAlias => $role_group_alias );
3446 $self->SUPER::Limit(
3447 LEFTJOIN => $cgm_alias,
3448 FIELD => 'MemberId',
3453 my $limit_queues = sub {
3457 return unless @queues;
3458 if ( @queues == 1 ) {
3459 $self->SUPER::Limit(
3464 ENTRYAGGREGATOR => $ea,
3467 $self->SUPER::_OpenParen('ACL');
3468 foreach my $q ( @queues ) {
3469 $self->SUPER::Limit(
3474 ENTRYAGGREGATOR => $ea,
3478 $self->SUPER::_CloseParen('ACL');
3483 $self->SUPER::_OpenParen('ACL');
3485 $ea = 'OR' if $limit_queues->( $ea, @direct_queues );
3486 while ( my ($role, $queues) = each %roles ) {
3487 $self->SUPER::_OpenParen('ACL');
3488 if ( $role eq 'Owner' ) {
3489 $self->SUPER::Limit(
3493 ENTRYAGGREGATOR => $ea,
3497 $self->SUPER::Limit(
3499 ALIAS => $cgm_alias,
3500 FIELD => 'MemberId',
3501 OPERATOR => 'IS NOT',
3504 ENTRYAGGREGATOR => $ea,
3506 $self->SUPER::Limit(
3508 ALIAS => $role_group_alias,
3511 ENTRYAGGREGATOR => 'AND',
3514 $limit_queues->( 'AND', @$queues ) if ref $queues;
3515 $ea = 'OR' if $ea eq 'AND';
3516 $self->SUPER::_CloseParen('ACL');
3518 $self->SUPER::_CloseParen('ACL');
3520 return $self->{'_sql_current_user_can_see_applied'} = 1;
3527 =head2 LoadRestrictions
3529 LoadRestrictions takes a string which can fully populate the TicketRestrictons hash.
3530 TODO It is not yet implemented
3536 =head2 DescribeRestrictions
3539 Returns a hash keyed by restriction id.
3540 Each element of the hash is currently a one element hash that contains DESCRIPTION which
3541 is a description of the purpose of that TicketRestriction
3545 sub DescribeRestrictions {
3550 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
3551 $listing{$row} = $self->{'TicketRestrictions'}{$row}{'DESCRIPTION'};
3558 =head2 RestrictionValues FIELD
3560 Takes a restriction field and returns a list of values this field is restricted
3565 sub RestrictionValues {
3568 map $self->{'TicketRestrictions'}{$_}{'VALUE'}, grep {
3569 $self->{'TicketRestrictions'}{$_}{'FIELD'} eq $field
3570 && $self->{'TicketRestrictions'}{$_}{'OPERATOR'} eq "="
3572 keys %{ $self->{'TicketRestrictions'} };
3577 =head2 ClearRestrictions
3579 Removes all restrictions irretrievably
3583 sub ClearRestrictions {
3585 delete $self->{'TicketRestrictions'};
3586 $self->{'looking_at_effective_id'} = 0;
3587 $self->{'looking_at_type'} = 0;
3588 $self->{'RecalcTicketLimits'} = 1;
3593 =head2 DeleteRestriction
3595 Takes the row Id of a restriction (From DescribeRestrictions' output, for example.
3596 Removes that restriction from the session's limits.
3600 sub DeleteRestriction {
3603 delete $self->{'TicketRestrictions'}{$row};
3605 $self->{'RecalcTicketLimits'} = 1;
3607 #make the underlying easysearch object forget all its preconceptions
3612 # Convert a set of oldstyle SB Restrictions to Clauses for RQL
3614 sub _RestrictionsToClauses {
3618 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
3619 my $restriction = $self->{'TicketRestrictions'}{$row};
3621 # We need to reimplement the subclause aggregation that SearchBuilder does.
3622 # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main',
3623 # Then SB AND's the different Subclauses together.
3625 # So, we want to group things into Subclauses, convert them to
3626 # SQL, and then join them with the appropriate DefaultEA.
3627 # Then join each subclause group with AND.
3629 my $field = $restriction->{'FIELD'};
3630 my $realfield = $field; # CustomFields fake up a fieldname, so
3631 # we need to figure that out
3634 # Rewrite LinkedTo meta field to the real field
3635 if ( $field =~ /LinkedTo/ ) {
3636 $realfield = $field = $restriction->{'TYPE'};
3640 # Handle subkey fields with a different real field
3641 if ( $field =~ /^(\w+)\./ ) {
3645 die "I don't know about $field yet"
3646 unless ( exists $FIELD_METADATA{$realfield}
3647 or $restriction->{CUSTOMFIELD} );
3649 my $type = $FIELD_METADATA{$realfield}->[0];
3650 my $op = $restriction->{'OPERATOR'};
3654 map { $restriction->{$_} } qw(VALUE TICKET BASE TARGET)
3657 # this performs the moral equivalent of defined or/dor/C<//>,
3658 # without the short circuiting.You need to use a 'defined or'
3659 # type thing instead of just checking for truth values, because
3660 # VALUE could be 0.(i.e. "false")
3662 # You could also use this, but I find it less aesthetic:
3663 # (although it does short circuit)
3664 #( defined $restriction->{'VALUE'}? $restriction->{VALUE} :
3665 # defined $restriction->{'TICKET'} ?
3666 # $restriction->{TICKET} :
3667 # defined $restriction->{'BASE'} ?
3668 # $restriction->{BASE} :
3669 # defined $restriction->{'TARGET'} ?
3670 # $restriction->{TARGET} )
3672 my $ea = $restriction->{ENTRYAGGREGATOR}
3673 || $DefaultEA{$type}
3676 die "Invalid operator $op for $field ($type)"
3677 unless exists $ea->{$op};
3681 # Each CustomField should be put into a different Clause so they
3682 # are ANDed together.
3683 if ( $restriction->{CUSTOMFIELD} ) {
3684 $realfield = $field;
3687 exists $clause{$realfield} or $clause{$realfield} = [];
3690 $field =~ s!(['\\])!\\$1!g;
3691 $value =~ s!(['\\])!\\$1!g;
3692 my $data = [ $ea, $type, $field, $op, $value ];
3694 # here is where we store extra data, say if it's a keyword or
3695 # something. (I.e. "TYPE SPECIFIC STUFF")
3697 if (lc $ea eq 'none') {
3698 $clause{$realfield} = [ $data ];
3700 push @{ $clause{$realfield} }, $data;
3708 =head2 _ProcessRestrictions PARAMHASH
3710 # The new _ProcessRestrictions is somewhat dependent on the SQL stuff,
3711 # but isn't quite generic enough to move into Tickets_SQL.
3715 sub _ProcessRestrictions {
3718 #Blow away ticket aliases since we'll need to regenerate them for
3720 delete $self->{'TicketAliases'};
3721 delete $self->{'items_array'};
3722 delete $self->{'item_map'};
3723 delete $self->{'raw_rows'};
3724 delete $self->{'rows'};
3725 delete $self->{'count_all'};
3727 my $sql = $self->Query; # Violating the _SQL namespace
3728 if ( !$sql || $self->{'RecalcTicketLimits'} ) {
3730 # "Restrictions to Clauses Branch\n";
3731 my $clauseRef = eval { $self->_RestrictionsToClauses; };
3733 $RT::Logger->error( "RestrictionsToClauses: " . $@ );
3737 $sql = $self->ClausesToSQL($clauseRef);
3738 $self->FromSQL($sql) if $sql;
3742 $self->{'RecalcTicketLimits'} = 0;
3746 =head2 _BuildItemMap
3748 Build up a L</ItemMap> of first/last/next/prev items, so that we can
3749 display search nav quickly.
3756 my $window = RT->Config->Get('TicketsItemMapSize');
3758 $self->{'item_map'} = {};
3760 my $items = $self->ItemsArrayRefWindow( $window );
3761 return unless $items && @$items;
3764 $self->{'item_map'}{'first'} = $items->[0]->EffectiveId;
3765 for ( my $i = 0; $i < @$items; $i++ ) {
3766 my $item = $items->[$i];
3767 my $id = $item->EffectiveId;
3768 $self->{'item_map'}{$id}{'defined'} = 1;
3769 $self->{'item_map'}{$id}{'prev'} = $prev;
3770 $self->{'item_map'}{$id}{'next'} = $items->[$i+1]->EffectiveId
3774 $self->{'item_map'}{'last'} = $prev
3775 if !$window || @$items < $window;
3780 Returns an a map of all items found by this search. The map is a hash
3784 first => <first ticket id found>,
3785 last => <last ticket id found or undef>,
3788 prev => <the ticket id found before>,
3789 next => <the ticket id found after>,
3801 $self->_BuildItemMap unless $self->{'item_map'};
3802 return $self->{'item_map'};
3808 =head2 PrepForSerialization
3810 You don't want to serialize a big tickets object, as
3811 the {items} hash will be instantly invalid _and_ eat
3816 sub PrepForSerialization {
3818 delete $self->{'items'};
3819 delete $self->{'items_array'};
3820 $self->RedoSearch();
3825 RT::Tickets supports several flags which alter search behavior:
3828 allow_deleted_search (Otherwise never show deleted tickets in search results)
3829 looking_at_type (otherwise limit to type=ticket)
3831 These flags are set by calling
3833 $tickets->{'flagname'} = 1;
3835 BUG: There should be an API for this
3845 Returns an empty new RT::Ticket item
3851 return(RT::Ticket->new($self->CurrentUser));
3853 RT::Base->_ImportOverlays();