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;
439 my $direction = $meta->[1] || '';
440 my ($matchfield, $linkfield) = ('', '');
441 if ( $direction eq 'To' ) {
442 ($matchfield, $linkfield) = ("Target", "Base");
444 elsif ( $direction eq 'From' ) {
445 ($matchfield, $linkfield) = ("Base", "Target");
447 elsif ( $direction ) {
448 die "Invalid link direction '$direction' for $field\n";
451 $sb->_LinkLimit( 'LinkedTo', $op, $value, @rest );
453 'LinkedFrom', $op, $value, @rest,
454 ENTRYAGGREGATOR => (($is_negative && $is_null) || (!$is_null && !$is_negative))? 'OR': 'AND',
462 $op = ($op =~ /^(=|IS)$/)? 'IS': 'IS NOT';
464 elsif ( $value =~ /\D/ ) {
467 $matchfield = "Local$matchfield" if $is_local;
469 #For doing a left join to find "unlinked tickets" we want to generate a query that looks like this
470 # SELECT main.* FROM Tickets main
471 # LEFT JOIN Links Links_1 ON ( (Links_1.Type = 'MemberOf')
472 # AND(main.id = Links_1.LocalTarget))
473 # WHERE Links_1.LocalBase IS NULL;
476 my $linkalias = $sb->Join(
481 FIELD2 => 'Local' . $linkfield
484 LEFTJOIN => $linkalias,
492 FIELD => $matchfield,
499 my $linkalias = $sb->Join(
504 FIELD2 => 'Local' . $linkfield
507 LEFTJOIN => $linkalias,
513 LEFTJOIN => $linkalias,
514 FIELD => $matchfield,
521 FIELD => $matchfield,
522 OPERATOR => $is_negative? 'IS': 'IS NOT',
531 Handle date fields. (Created, LastTold..)
534 1: type of link. (Probably not necessary.)
539 my ( $sb, $field, $op, $value, @rest ) = @_;
541 die "Invalid Date Op: $op"
542 unless $op =~ /^(=|>|<|>=|<=)$/;
544 my $meta = $FIELD_METADATA{$field};
545 die "Incorrect Meta Data for $field"
546 unless ( defined $meta->[1] );
548 $sb->_DateFieldLimit( $meta->[1], $op, $value, @rest );
551 # Factor this out for use by custom fields
553 sub _DateFieldLimit {
554 my ( $sb, $field, $op, $value, @rest ) = @_;
556 my $date = RT::Date->new( $sb->CurrentUser );
557 $date->Set( Format => 'unknown', Value => $value );
561 # if we're specifying =, that means we want everything on a
562 # particular single day. in the database, we need to check for >
563 # and < the edges of that day.
565 # Except if the value is 'this month' or 'last month', check
566 # > and < the edges of the month.
568 my ($daystart, $dayend);
569 if ( lc($value) eq 'this month' ) {
571 $date->SetToStart('month', Timezone => 'server');
572 $daystart = $date->ISO;
573 $date->AddMonth(Timezone => 'server');
574 $dayend = $date->ISO;
576 elsif ( lc($value) eq 'last month' ) {
578 $date->SetToStart('month', Timezone => 'server');
579 $dayend = $date->ISO;
581 $date->SetToStart('month', Timezone => 'server');
582 $daystart = $date->ISO;
585 $date->SetToMidnight( Timezone => 'server' );
586 $daystart = $date->ISO;
588 $dayend = $date->ISO;
605 ENTRYAGGREGATOR => 'AND',
623 Handle simple fields which are just strings. (Subject,Type)
631 my ( $sb, $field, $op, $value, @rest ) = @_;
635 # =, !=, LIKE, NOT LIKE
636 if ( RT->Config->Get('DatabaseType') eq 'Oracle'
637 && (!defined $value || !length $value)
638 && lc($op) ne 'is' && lc($op) ne 'is not'
640 if ($op eq '!=' || $op =~ /^NOT\s/i) {
657 =head2 _TransDateLimit
659 Handle fields limiting based on Transaction Date.
661 The inpupt value must be in a format parseable by Time::ParseDate
668 # This routine should really be factored into translimit.
669 sub _TransDateLimit {
670 my ( $sb, $field, $op, $value, @rest ) = @_;
672 # See the comments for TransLimit, they apply here too
674 my $txn_alias = $sb->JoinTransactions;
676 my $date = RT::Date->new( $sb->CurrentUser );
677 $date->Set( Format => 'unknown', Value => $value );
682 # if we're specifying =, that means we want everything on a
683 # particular single day. in the database, we need to check for >
684 # and < the edges of that day.
686 $date->SetToMidnight( Timezone => 'server' );
687 my $daystart = $date->ISO;
689 my $dayend = $date->ISO;
704 ENTRYAGGREGATOR => 'AND',
709 # not searching for a single day
712 #Search for the right field
727 Limit based on the ContentType or the Filename of a transaction.
732 my ( $self, $field, $op, $value, %rest ) = @_;
734 my $txn_alias = $self->JoinTransactions;
735 unless ( defined $self->{_sql_trattachalias} ) {
736 $self->{_sql_trattachalias} = $self->_SQLJoin(
737 TYPE => 'LEFT', # not all txns have an attachment
738 ALIAS1 => $txn_alias,
740 TABLE2 => 'Attachments',
741 FIELD2 => 'TransactionId',
747 ALIAS => $self->{_sql_trattachalias},
755 =head2 _TransContentLimit
757 Limit based on the Content of a transaction.
761 sub _TransContentLimit {
765 # If only this was this simple. We've got to do something
768 #Basically, we want to make sure that the limits apply to
769 #the same attachment, rather than just another attachment
770 #for the same ticket, no matter how many clauses we lump
771 #on. We put them in TicketAliases so that they get nuked
772 #when we redo the join.
774 # In the SQL, we might have
775 # (( Content = foo ) or ( Content = bar AND Content = baz ))
776 # The AND group should share the same Alias.
778 # Actually, maybe it doesn't matter. We use the same alias and it
779 # works itself out? (er.. different.)
781 # Steal more from _ProcessRestrictions
783 # FIXME: Maybe look at the previous FooLimit call, and if it was a
784 # TransLimit and EntryAggregator == AND, reuse the Aliases?
786 # Or better - store the aliases on a per subclause basis - since
787 # those are going to be the things we want to relate to each other,
790 # maybe we should not allow certain kinds of aggregation of these
791 # clauses and do a psuedo regex instead? - the problem is getting
792 # them all into the same subclause when you have (A op B op C) - the
793 # way they get parsed in the tree they're in different subclauses.
795 my ( $self, $field, $op, $value, %rest ) = @_;
796 $field = 'Content' if $field =~ /\W/;
798 my $config = RT->Config->Get('FullTextSearch') || {};
799 unless ( $config->{'Enable'} ) {
800 $self->_SQLLimit( %rest, FIELD => 'id', VALUE => 0 );
804 my $txn_alias = $self->JoinTransactions;
805 unless ( defined $self->{_sql_trattachalias} ) {
806 $self->{_sql_trattachalias} = $self->_SQLJoin(
807 TYPE => 'LEFT', # not all txns have an attachment
808 ALIAS1 => $txn_alias,
810 TABLE2 => 'Attachments',
811 FIELD2 => 'TransactionId',
816 if ( $config->{'Indexed'} ) {
817 my $db_type = RT->Config->Get('DatabaseType');
820 if ( $config->{'Table'} and $config->{'Table'} ne "Attachments") {
821 $alias = $self->{'_sql_aliases'}{'full_text'} ||= $self->_SQLJoin(
823 ALIAS1 => $self->{'_sql_trattachalias'},
825 TABLE2 => $config->{'Table'},
829 $alias = $self->{'_sql_trattachalias'};
832 #XXX: handle negative searches
833 my $index = $config->{'Column'};
834 if ( $db_type eq 'Oracle' ) {
835 my $dbh = $RT::Handle->dbh;
836 my $alias = $self->{_sql_trattachalias};
839 FUNCTION => "CONTAINS( $alias.$field, ".$dbh->quote($value) .")",
845 # this is required to trick DBIx::SB's LEFT JOINS optimizer
846 # into deciding that join is redundant as it is
848 ENTRYAGGREGATOR => 'AND',
849 ALIAS => $self->{_sql_trattachalias},
851 OPERATOR => 'IS NOT',
855 elsif ( $db_type eq 'Pg' ) {
856 my $dbh = $RT::Handle->dbh;
862 VALUE => 'plainto_tsquery('. $dbh->quote($value) .')',
866 elsif ( $db_type eq 'mysql' ) {
867 # XXX: We could theoretically skip the join to Attachments,
868 # and have Sphinx simply index and group by the TicketId,
869 # and join Ticket.id to that attribute, which would be much
870 # more efficient -- however, this is only a possibility if
871 # there are no other transaction limits.
873 # This is a special character. Note that \ does not escape
874 # itself (in Sphinx 2.1.0, at least), so 'foo\;bar' becoming
875 # 'foo\\;bar' is not a vulnerability, and is still parsed as
876 # "foo, \, ;, then bar". Happily, the default mode is
877 # "all", meaning that boolean operators are not special.
880 my $max = $config->{'MaxMatches'};
886 VALUE => "$value;limit=$max;maxmatches=$max",
892 ALIAS => $self->{_sql_trattachalias},
899 if ( RT->Config->Get('DontSearchFileAttachments') ) {
901 ENTRYAGGREGATOR => 'AND',
902 ALIAS => $self->{_sql_trattachalias},
913 Handle watcher limits. (Requestor, CC, etc..)
929 my $meta = $FIELD_METADATA{ $field };
930 my $type = $meta->[1] || '';
931 my $class = $meta->[2] || 'Ticket';
933 # Bail if the subfield is not allowed
935 and not grep { $_ eq $rest{SUBKEY} } @{$SEARCHABLE_SUBFIELDS{'User'}})
937 die "Invalid watcher subfield: '$rest{SUBKEY}'";
940 # Owner was ENUM field, so "Owner = 'xxx'" allowed user to
941 # search by id and Name at the same time, this is workaround
942 # to preserve backward compatibility
943 if ( $field eq 'Owner' ) {
944 if ( $op =~ /^!?=$/ && (!$rest{'SUBKEY'} || $rest{'SUBKEY'} eq 'Name' || $rest{'SUBKEY'} eq 'EmailAddress') ) {
945 my $o = RT::User->new( $self->CurrentUser );
946 my $method = ($rest{'SUBKEY'}||'') eq 'EmailAddress' ? 'LoadByEmail': 'Load';
947 $o->$method( $value );
956 if ( ($rest{'SUBKEY'}||'') eq 'id' ) {
966 $rest{SUBKEY} ||= 'EmailAddress';
968 my $groups = $self->_RoleGroupsJoin( Type => $type, Class => $class, New => !$type );
971 if ( $op =~ /^IS(?: NOT)?$/ ) {
972 # is [not] empty case
974 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
975 # to avoid joining the table Users into the query, we just join GM
976 # and make sure we don't match records where group is member of itself
978 LEFTJOIN => $group_members,
981 VALUE => "$group_members.MemberId",
985 ALIAS => $group_members,
992 elsif ( $op =~ /^!=$|^NOT\s+/i ) {
993 # negative condition case
996 $op =~ s/!|NOT\s+//i;
998 # XXX: we have no way to build correct "Watcher.X != 'Y'" when condition
999 # "X = 'Y'" matches more then one user so we try to fetch two records and
1000 # do the right thing when there is only one exist and semi-working solution
1002 my $users_obj = RT::Users->new( $self->CurrentUser );
1004 FIELD => $rest{SUBKEY},
1008 $users_obj->OrderBy;
1009 $users_obj->RowsPerPage(2);
1010 my @users = @{ $users_obj->ItemsArrayRef };
1012 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
1013 if ( @users <= 1 ) {
1015 $uid = $users[0]->id if @users;
1016 $self->SUPER::Limit(
1017 LEFTJOIN => $group_members,
1018 ALIAS => $group_members,
1019 FIELD => 'MemberId',
1024 ALIAS => $group_members,
1030 $self->SUPER::Limit(
1031 LEFTJOIN => $group_members,
1034 VALUE => "$group_members.MemberId",
1037 my $users = $self->Join(
1039 ALIAS1 => $group_members,
1040 FIELD1 => 'MemberId',
1044 $self->SUPER::Limit(
1047 FIELD => $rest{SUBKEY},
1061 # positive condition case
1063 my $group_members = $self->_GroupMembersJoin(
1064 GroupsAlias => $groups, New => 1, Left => 0
1066 my $users = $self->Join(
1068 ALIAS1 => $group_members,
1069 FIELD1 => 'MemberId',
1076 FIELD => $rest{'SUBKEY'},
1085 sub _RoleGroupsJoin {
1087 my %args = (New => 0, Class => 'Ticket', Type => '', @_);
1088 return $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1089 if $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1092 # we always have watcher groups for ticket, so we use INNER join
1093 my $groups = $self->Join(
1095 FIELD1 => $args{'Class'} eq 'Queue'? 'Queue': 'id',
1097 FIELD2 => 'Instance',
1098 ENTRYAGGREGATOR => 'AND',
1100 $self->SUPER::Limit(
1101 LEFTJOIN => $groups,
1104 VALUE => 'RT::'. $args{'Class'} .'-Role',
1106 $self->SUPER::Limit(
1107 LEFTJOIN => $groups,
1110 VALUE => $args{'Type'},
1113 $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} } = $groups
1114 unless $args{'New'};
1119 sub _GroupMembersJoin {
1121 my %args = (New => 1, GroupsAlias => undef, Left => 1, @_);
1123 return $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1124 if $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1127 my $alias = $self->Join(
1128 $args{'Left'} ? (TYPE => 'LEFT') : (),
1129 ALIAS1 => $args{'GroupsAlias'},
1131 TABLE2 => 'CachedGroupMembers',
1132 FIELD2 => 'GroupId',
1133 ENTRYAGGREGATOR => 'AND',
1135 $self->SUPER::Limit(
1136 $args{'Left'} ? (LEFTJOIN => $alias) : (),
1138 FIELD => 'Disabled',
1142 $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} } = $alias
1143 unless $args{'New'};
1150 Helper function which provides joins to a watchers table both for limits
1157 my $type = shift || '';
1160 my $groups = $self->_RoleGroupsJoin( Type => $type );
1161 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
1162 # XXX: work around, we must hide groups that
1163 # are members of the role group we search in,
1164 # otherwise them result in wrong NULLs in Users
1165 # table and break ordering. Now, we know that
1166 # RT doesn't allow to add groups as members of the
1167 # ticket roles, so we just hide entries in CGM table
1168 # with MemberId == GroupId from results
1169 $self->SUPER::Limit(
1170 LEFTJOIN => $group_members,
1173 VALUE => "$group_members.MemberId",
1176 my $users = $self->Join(
1178 ALIAS1 => $group_members,
1179 FIELD1 => 'MemberId',
1183 return ($groups, $group_members, $users);
1186 =head2 _WatcherMembershipLimit
1188 Handle watcher membership limits, i.e. whether the watcher belongs to a
1189 specific group or not.
1192 1: Field to query on
1194 SELECT DISTINCT main.*
1198 CachedGroupMembers CachedGroupMembers_2,
1201 (main.EffectiveId = main.id)
1203 (main.Status != 'deleted')
1205 (main.Type = 'ticket')
1208 (Users_3.EmailAddress = '22')
1210 (Groups_1.Domain = 'RT::Ticket-Role')
1212 (Groups_1.Type = 'RequestorGroup')
1215 Groups_1.Instance = main.id
1217 Groups_1.id = CachedGroupMembers_2.GroupId
1219 CachedGroupMembers_2.MemberId = Users_3.id
1220 ORDER BY main.id ASC
1225 sub _WatcherMembershipLimit {
1226 my ( $self, $field, $op, $value, @rest ) = @_;
1231 my $groups = $self->NewAlias('Groups');
1232 my $groupmembers = $self->NewAlias('CachedGroupMembers');
1233 my $users = $self->NewAlias('Users');
1234 my $memberships = $self->NewAlias('CachedGroupMembers');
1236 if ( ref $field ) { # gross hack
1237 my @bundle = @$field;
1239 for my $chunk (@bundle) {
1240 ( $field, $op, $value, @rest ) = @$chunk;
1242 ALIAS => $memberships,
1253 ALIAS => $memberships,
1261 # Tie to groups for tickets we care about
1265 VALUE => 'RT::Ticket-Role',
1266 ENTRYAGGREGATOR => 'AND'
1271 FIELD1 => 'Instance',
1278 # If we care about which sort of watcher
1279 my $meta = $FIELD_METADATA{$field};
1280 my $type = ( defined $meta->[1] ? $meta->[1] : undef );
1287 ENTRYAGGREGATOR => 'AND'
1294 ALIAS2 => $groupmembers,
1299 ALIAS1 => $groupmembers,
1300 FIELD1 => 'MemberId',
1306 ALIAS => $groupmembers,
1307 FIELD => 'Disabled',
1312 ALIAS1 => $memberships,
1313 FIELD1 => 'MemberId',
1319 ALIAS => $memberships,
1320 FIELD => 'Disabled',
1329 =head2 _CustomFieldDecipher
1331 Try and turn a CF descriptor into (cfid, cfname) object pair.
1335 sub _CustomFieldDecipher {
1336 my ($self, $string) = @_;
1338 my ($queue, $field, $column) = ($string =~ /^(?:(.+?)\.)?{(.+)}(?:\.(Content|LargeContent))?$/);
1339 $field ||= ($string =~ /^{(.*?)}$/)[0] || $string;
1343 my $q = RT::Queue->new( $self->CurrentUser );
1347 # $queue = $q->Name; # should we normalize the queue?
1348 $cf = $q->CustomField( $field );
1351 $RT::Logger->warning("Queue '$queue' doesn't exist, parsed from '$string'");
1355 elsif ( $field =~ /\D/ ) {
1357 my $cfs = RT::CustomFields->new( $self->CurrentUser );
1358 $cfs->Limit( FIELD => 'Name', VALUE => $field );
1359 $cfs->LimitToLookupType('RT::Queue-RT::Ticket');
1361 # if there is more then one field the current user can
1362 # see with the same name then we shouldn't return cf object
1363 # as we don't know which one to use
1366 $cf = undef if $cfs->Next;
1370 $cf = RT::CustomField->new( $self->CurrentUser );
1371 $cf->Load( $field );
1374 return ($queue, $field, $cf, $column);
1377 =head2 _CustomFieldJoin
1379 Factor out the Join of custom fields so we can use it for sorting too
1383 sub _CustomFieldJoin {
1384 my ($self, $cfkey, $cfid, $field) = @_;
1385 # Perform one Join per CustomField
1386 if ( $self->{_sql_object_cfv_alias}{$cfkey} ||
1387 $self->{_sql_cf_alias}{$cfkey} )
1389 return ( $self->{_sql_object_cfv_alias}{$cfkey},
1390 $self->{_sql_cf_alias}{$cfkey} );
1393 my ($TicketCFs, $CFs);
1395 $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1399 TABLE2 => 'ObjectCustomFieldValues',
1400 FIELD2 => 'ObjectId',
1402 $self->SUPER::Limit(
1403 LEFTJOIN => $TicketCFs,
1404 FIELD => 'CustomField',
1406 ENTRYAGGREGATOR => 'AND'
1410 my $ocfalias = $self->Join(
1413 TABLE2 => 'ObjectCustomFields',
1414 FIELD2 => 'ObjectId',
1417 $self->SUPER::Limit(
1418 LEFTJOIN => $ocfalias,
1419 ENTRYAGGREGATOR => 'OR',
1420 FIELD => 'ObjectId',
1424 $CFs = $self->{_sql_cf_alias}{$cfkey} = $self->Join(
1426 ALIAS1 => $ocfalias,
1427 FIELD1 => 'CustomField',
1428 TABLE2 => 'CustomFields',
1431 $self->SUPER::Limit(
1433 ENTRYAGGREGATOR => 'AND',
1434 FIELD => 'LookupType',
1435 VALUE => 'RT::Queue-RT::Ticket',
1437 $self->SUPER::Limit(
1439 ENTRYAGGREGATOR => 'AND',
1444 $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1448 TABLE2 => 'ObjectCustomFieldValues',
1449 FIELD2 => 'CustomField',
1451 $self->SUPER::Limit(
1452 LEFTJOIN => $TicketCFs,
1453 FIELD => 'ObjectId',
1456 ENTRYAGGREGATOR => 'AND',
1459 $self->SUPER::Limit(
1460 LEFTJOIN => $TicketCFs,
1461 FIELD => 'ObjectType',
1462 VALUE => 'RT::Ticket',
1463 ENTRYAGGREGATOR => 'AND'
1465 $self->SUPER::Limit(
1466 LEFTJOIN => $TicketCFs,
1467 FIELD => 'Disabled',
1470 ENTRYAGGREGATOR => 'AND'
1473 return ($TicketCFs, $CFs);
1476 =head2 _CustomFieldLimit
1478 Limit based on CustomFields
1485 use Regexp::Common qw(RE_net_IPv4);
1486 use Regexp::Common::net::CIDR;
1489 sub _CustomFieldLimit {
1490 my ( $self, $_field, $op, $value, %rest ) = @_;
1492 my $field = $rest{'SUBKEY'} || die "No field specified";
1494 # For our sanity, we can only limit on one queue at a time
1496 my ($queue, $cfid, $cf, $column);
1497 ($queue, $field, $cf, $column) = $self->_CustomFieldDecipher( $field );
1498 $cfid = $cf ? $cf->id : 0 ;
1500 # If we're trying to find custom fields that don't match something, we
1501 # want tickets where the custom field has no value at all. Note that
1502 # we explicitly don't include the "IS NULL" case, since we would
1503 # otherwise end up with a redundant clause.
1505 my ($negative_op, $null_op, $inv_op, $range_op)
1506 = $self->ClassifySQLOperation( $op );
1509 return @_ unless RT->Config->Get('DatabaseType') eq 'Oracle';
1512 return %args unless $args{'FIELD'} eq 'LargeContent';
1514 my $op = $args{'OPERATOR'};
1516 $args{'OPERATOR'} = 'MATCHES';
1518 elsif ( $op eq '!=' ) {
1519 $args{'OPERATOR'} = 'NOT MATCHES';
1521 elsif ( $op =~ /^[<>]=?$/ ) {
1522 $args{'FUNCTION'} = "TO_CHAR( $args{'ALIAS'}.LargeContent )";
1527 if ( $cf && $cf->Type eq 'IPAddress' ) {
1528 my $parsed = RT::ObjectCustomFieldValue->ParseIP($value);
1533 $RT::Logger->warn("$value is not a valid IPAddress");
1537 if ( $cf && $cf->Type eq 'IPAddressRange' ) {
1539 if ( $value =~ /^\s*$RE{net}{CIDR}{IPv4}{-keep}\s*$/o ) {
1541 # convert incomplete 192.168/24 to 192.168.0.0/24 format
1543 join( '.', map $_ || 0, ( split /\./, $1 )[ 0 .. 3 ] ) . "/$2"
1547 my ( $start_ip, $end_ip ) =
1548 RT::ObjectCustomFieldValue->ParseIPRange($value);
1549 if ( $start_ip && $end_ip ) {
1550 if ( $op =~ /^([<>])=?$/ ) {
1551 my $is_less = $1 eq '<' ? 1 : 0;
1560 $value = join '-', $start_ip, $end_ip;
1564 $RT::Logger->warn("$value is not a valid IPAddressRange");
1568 my $single_value = !$cf || !$cfid || $cf->SingleValue;
1570 my $cfkey = $cfid ? $cfid : "$queue.$field";
1572 if ( $null_op && !$column ) {
1573 # IS[ NOT] NULL without column is the same as has[ no] any CF value,
1574 # we can reuse our default joins for this operation
1575 # with column specified we have different situation
1576 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1579 ALIAS => $TicketCFs,
1588 OPERATOR => 'IS NOT',
1591 ENTRYAGGREGATOR => 'AND',
1595 elsif ( $op !~ /^[<>]=?$/ && ( $cf && $cf->Type eq 'IPAddressRange')) {
1597 my ($start_ip, $end_ip) = split /-/, $value;
1600 if ( $op !~ /NOT|!=|<>/i ) { # positive equation
1601 $self->_CustomFieldLimit(
1602 'CF', '<=', $end_ip, %rest,
1603 SUBKEY => $rest{'SUBKEY'}. '.Content',
1605 $self->_CustomFieldLimit(
1606 'CF', '>=', $start_ip, %rest,
1607 SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1608 ENTRYAGGREGATOR => 'AND',
1610 # as well limit borders so DB optimizers can use better
1611 # estimations and scan less rows
1612 # have to disable this tweak because of ipv6
1613 # $self->_CustomFieldLimit(
1614 # $field, '>=', '000.000.000.000', %rest,
1615 # SUBKEY => $rest{'SUBKEY'}. '.Content',
1616 # ENTRYAGGREGATOR => 'AND',
1618 # $self->_CustomFieldLimit(
1619 # $field, '<=', '255.255.255.255', %rest,
1620 # SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1621 # ENTRYAGGREGATOR => 'AND',
1624 else { # negative equation
1625 $self->_CustomFieldLimit($field, '>', $end_ip, %rest);
1626 $self->_CustomFieldLimit(
1627 $field, '<', $start_ip, %rest,
1628 SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
1629 ENTRYAGGREGATOR => 'OR',
1631 # TODO: as well limit borders so DB optimizers can use better
1632 # estimations and scan less rows, but it's harder to do
1633 # as we have OR aggregator
1637 elsif ( !$negative_op || $single_value ) {
1638 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++ if !$single_value && !$range_op;
1639 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1646 # if column is defined then deal only with it
1647 # otherwise search in Content and in LargeContent
1649 $self->_SQLLimit( $fix_op->(
1650 ALIAS => $TicketCFs,
1661 # need special treatment for Date
1662 if ( $cf and $cf->Type eq 'DateTime' and $op eq '=' ) {
1664 if ( $value =~ /:/ ) {
1665 # there is time speccified.
1666 my $date = RT::Date->new( $self->CurrentUser );
1667 $date->Set( Format => 'unknown', Value => $value );
1669 ALIAS => $TicketCFs,
1672 VALUE => $date->ISO,
1677 # no time specified, that means we want everything on a
1678 # particular day. in the database, we need to check for >
1679 # and < the edges of that day.
1680 my $date = RT::Date->new( $self->CurrentUser );
1681 $date->Set( Format => 'unknown', Value => $value );
1682 $date->SetToMidnight( Timezone => 'server' );
1683 my $daystart = $date->ISO;
1685 my $dayend = $date->ISO;
1690 ALIAS => $TicketCFs,
1698 ALIAS => $TicketCFs,
1703 ENTRYAGGREGATOR => 'AND',
1709 elsif ( $op eq '=' || $op eq '!=' || $op eq '<>' ) {
1710 if ( length( Encode::encode_utf8($value) ) < 256 ) {
1712 ALIAS => $TicketCFs,
1722 ALIAS => $TicketCFs,
1726 ENTRYAGGREGATOR => 'OR'
1729 ALIAS => $TicketCFs,
1733 ENTRYAGGREGATOR => 'OR'
1736 $self->_SQLLimit( $fix_op->(
1737 ALIAS => $TicketCFs,
1738 FIELD => 'LargeContent',
1741 ENTRYAGGREGATOR => 'AND',
1747 ALIAS => $TicketCFs,
1757 ALIAS => $TicketCFs,
1761 ENTRYAGGREGATOR => 'OR'
1764 ALIAS => $TicketCFs,
1768 ENTRYAGGREGATOR => 'OR'
1771 $self->_SQLLimit( $fix_op->(
1772 ALIAS => $TicketCFs,
1773 FIELD => 'LargeContent',
1776 ENTRYAGGREGATOR => 'AND',
1782 # XXX: if we join via CustomFields table then
1783 # because of order of left joins we get NULLs in
1784 # CF table and then get nulls for those records
1785 # in OCFVs table what result in wrong results
1786 # as decifer method now tries to load a CF then
1787 # we fall into this situation only when there
1788 # are more than one CF with the name in the DB.
1789 # the same thing applies to order by call.
1790 # TODO: reorder joins T <- OCFVs <- CFs <- OCFs if
1791 # we want treat IS NULL as (not applies or has
1796 OPERATOR => 'IS NOT',
1799 ENTRYAGGREGATOR => 'AND',
1805 ALIAS => $TicketCFs,
1806 FIELD => $column || 'Content',
1810 ENTRYAGGREGATOR => 'OR',
1818 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++;
1819 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1822 $op =~ s/!|NOT\s+//i;
1824 # if column is defined then deal only with it
1825 # otherwise search in Content and in LargeContent
1827 $self->SUPER::Limit( $fix_op->(
1828 LEFTJOIN => $TicketCFs,
1829 ALIAS => $TicketCFs,
1836 $self->SUPER::Limit(
1837 LEFTJOIN => $TicketCFs,
1838 ALIAS => $TicketCFs,
1846 ALIAS => $TicketCFs,
1855 sub _HasAttributeLimit {
1856 my ( $self, $field, $op, $value, %rest ) = @_;
1858 my $alias = $self->Join(
1862 TABLE2 => 'Attributes',
1863 FIELD2 => 'ObjectId',
1865 $self->SUPER::Limit(
1867 FIELD => 'ObjectType',
1868 VALUE => 'RT::Ticket',
1869 ENTRYAGGREGATOR => 'AND'
1871 $self->SUPER::Limit(
1876 ENTRYAGGREGATOR => 'AND'
1882 OPERATOR => $FIELD_METADATA{$field}->[1]? 'IS NOT': 'IS',
1888 # End Helper Functions
1890 # End of SQL Stuff -------------------------------------------------
1893 =head2 OrderByCols ARRAY
1895 A modified version of the OrderBy method which automatically joins where
1896 C<ALIAS> is set to the name of a watcher type.
1907 foreach my $row (@args) {
1908 if ( $row->{ALIAS} ) {
1912 if ( $row->{FIELD} !~ /\./ ) {
1913 my $meta = $self->FIELDS->{ $row->{FIELD} };
1919 if ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'Queue' ) {
1920 my $alias = $self->Join(
1923 FIELD1 => $row->{'FIELD'},
1927 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1928 } elsif ( ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'User' )
1929 || ( $meta->[0] eq 'WATCHERFIELD' && ($meta->[1]||'') eq 'Owner' )
1931 my $alias = $self->Join(
1934 FIELD1 => $row->{'FIELD'},
1938 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1945 my ( $field, $subkey ) = split /\./, $row->{FIELD}, 2;
1946 my $meta = $self->FIELDS->{$field};
1947 if ( defined $meta->[0] && $meta->[0] eq 'WATCHERFIELD' ) {
1948 # cache alias as we want to use one alias per watcher type for sorting
1949 my $users = $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] };
1951 $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] }
1952 = $users = ( $self->_WatcherJoin( $meta->[1] ) )[2];
1954 push @res, { %$row, ALIAS => $users, FIELD => $subkey };
1955 } elsif ( defined $meta->[0] && $meta->[0] eq 'CUSTOMFIELD' ) {
1956 my ($queue, $field, $cf_obj, $column) = $self->_CustomFieldDecipher( $subkey );
1957 my $cfkey = $cf_obj ? $cf_obj->id : "$queue.$field";
1958 $cfkey .= ".ordering" if !$cf_obj || ($cf_obj->MaxValues||0) != 1;
1959 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, ($cf_obj ?$cf_obj->id :0) , $field );
1960 # this is described in _CustomFieldLimit
1964 OPERATOR => 'IS NOT',
1967 ENTRYAGGREGATOR => 'AND',
1970 # For those cases where we are doing a join against the
1971 # CF name, and don't have a CFid, use Unique to make sure
1972 # we don't show duplicate tickets. NOTE: I'm pretty sure
1973 # this will stay mixed in for the life of the
1974 # class/package, and not just for the life of the object.
1975 # Potential performance issue.
1976 require DBIx::SearchBuilder::Unique;
1977 DBIx::SearchBuilder::Unique->import;
1979 my $CFvs = $self->Join(
1981 ALIAS1 => $TicketCFs,
1982 FIELD1 => 'CustomField',
1983 TABLE2 => 'CustomFieldValues',
1984 FIELD2 => 'CustomField',
1986 $self->SUPER::Limit(
1990 VALUE => $TicketCFs . ".Content",
1991 ENTRYAGGREGATOR => 'AND'
1994 push @res, { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' };
1995 push @res, { %$row, ALIAS => $TicketCFs, FIELD => 'Content' };
1996 } elsif ( $field eq "Custom" && $subkey eq "Ownership") {
1997 # PAW logic is "reversed"
1999 if (exists $row->{ORDER} ) {
2000 my $o = $row->{ORDER};
2001 delete $row->{ORDER};
2002 $order = "DESC" if $o =~ /asc/i;
2005 # Ticket.Owner 1 0 X
2006 # Unowned Tickets 0 1 X
2009 foreach my $uid ( $self->CurrentUser->Id, RT->Nobody->Id ) {
2010 if ( RT->Config->Get('DatabaseType') eq 'Oracle' ) {
2011 my $f = ($row->{'ALIAS'} || 'main') .'.Owner';
2016 FUNCTION => "CASE WHEN $f=$uid THEN 1 ELSE 0 END",
2023 FUNCTION => "Owner=$uid",
2029 push @res, { %$row, FIELD => "Priority", ORDER => $order } ;
2031 } elsif ( $field eq 'Customer' ) { #Freeside
2032 # OrderBy(FIELD => expression) doesn't work, it has to be
2033 # an actual field, so we have to do the join even if sorting
2035 my $custalias = $self->JoinToCustomer;
2036 my $cust_field = lc($subkey);
2037 if ( !$cust_field or $cust_field eq 'number' ) {
2038 $cust_field = 'custnum';
2040 elsif ( $cust_field eq 'name' ) {
2041 $cust_field = "COALESCE( $custalias.company,
2042 $custalias.last || ', ' || $custalias.first
2045 else { # order by cust_main fields directly: 'Customer.agentnum'
2046 $cust_field = $subkey;
2048 push @res, { %$row, ALIAS => $custalias, FIELD => $cust_field };
2050 } elsif ( $field eq 'Service' ) {
2052 my $svcalias = $self->JoinToService;
2053 my $svc_field = lc($subkey);
2054 if ( !$svc_field or $svc_field eq 'number' ) {
2055 $svc_field = 'svcnum';
2057 push @res, { %$row, ALIAS => $svcalias, FIELD => $svc_field };
2065 return $self->SUPER::OrderByCols(@res);
2070 sub JoinToCustLinks {
2071 # Set up join to links (id = localbase),
2072 # limit link type to 'MemberOf',
2073 # and target value to any Freeside custnum URI.
2074 # Return the linkalias for further join/limit action,
2075 # and an sql expression to retrieve the custnum.
2077 # only join once for each RT::Tickets object
2078 my $linkalias = $self->{cust_main_linkalias};
2080 $linkalias = $self->Join(
2085 FIELD2 => 'LocalBase',
2087 $self->SUPER::Limit(
2088 LEFTJOIN => $linkalias,
2091 VALUE => 'fsck.com-rt://%/ticket/%',
2093 $self->SUPER::Limit(
2094 LEFTJOIN => $linkalias,
2097 VALUE => 'MemberOf',
2099 $self->SUPER::Limit(
2100 LEFTJOIN => $linkalias,
2102 OPERATOR => 'STARTSWITH',
2103 VALUE => 'freeside://freeside/cust_main/',
2105 $self->{cust_main_linkalias} = $linkalias;
2107 my $custnum_sql = "CAST(SUBSTR($linkalias.Target,31) AS ";
2108 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
2109 $custnum_sql .= 'SIGNED INTEGER)';
2112 $custnum_sql .= 'INTEGER)';
2114 return ($linkalias, $custnum_sql);
2117 sub JoinToCustomer {
2119 my ($linkalias, $custnum_sql) = $self->JoinToCustLinks;
2120 # don't reuse this join, though--negative queries need
2122 my $custalias = $self->Join(
2124 EXPRESSION => $custnum_sql,
2125 TABLE2 => 'cust_main',
2126 FIELD2 => 'custnum',
2131 sub JoinToSvcLinks {
2133 my $linkalias = $self->{cust_svc_linkalias};
2135 $linkalias = $self->Join(
2140 FIELD2 => 'LocalBase',
2142 $self->SUPER::Limit(
2143 LEFTJOIN => $linkalias,
2146 VALUE => 'fsck.com-rt://%/ticket/%',
2149 $self->SUPER::Limit(
2150 LEFTJOIN => $linkalias,
2153 VALUE => 'MemberOf',
2155 $self->SUPER::Limit(
2156 LEFTJOIN => $linkalias,
2158 OPERATOR => 'STARTSWITH',
2159 VALUE => 'freeside://freeside/cust_svc/',
2161 $self->{cust_svc_linkalias} = $linkalias;
2163 my $svcnum_sql = "CAST(SUBSTR($linkalias.Target,30) AS ";
2164 if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
2165 $svcnum_sql .= 'SIGNED INTEGER)';
2168 $svcnum_sql .= 'INTEGER)';
2170 return ($linkalias, $svcnum_sql);
2175 my ($linkalias, $svcnum_sql) = $self->JoinToSvcLinks;
2178 EXPRESSION => $svcnum_sql,
2179 TABLE2 => 'cust_svc',
2184 # This creates an alternate left join path to cust_main via cust_svc.
2185 # _FreesideFieldLimit needs to add this as a separate, independent join
2186 # and include all tickets that have a matching cust_main record via
2188 sub JoinToCustomerViaService {
2190 my $svcalias = $self->JoinToService;
2191 my $cust_pkg = $self->Join(
2193 ALIAS1 => $svcalias,
2195 TABLE2 => 'cust_pkg',
2198 my $cust_main = $self->Join(
2200 ALIAS1 => $cust_pkg,
2201 FIELD1 => 'custnum',
2202 TABLE2 => 'cust_main',
2203 FIELD2 => 'custnum',
2208 sub _FreesideFieldLimit {
2209 my ( $self, $field, $op, $value, %rest ) = @_;
2210 my $is_negative = 0;
2211 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
2212 # if the op is negative, do the join as though
2213 # the op were positive, then accept only records
2214 # where the right-side join key is null.
2216 $op = '=' if $op eq '!=';
2220 my (@alias, $table2, $subfield, $pkey);
2221 if ( $field eq 'Customer' ) {
2222 push @alias, $self->JoinToCustomer;
2223 push @alias, $self->JoinToCustomerViaService;
2226 elsif ( $field eq 'Service' ) {
2227 push @alias, $self->JoinToService;
2231 die "malformed Freeside query: $field";
2234 $subfield = $rest{SUBKEY} || $pkey;
2235 # compound subkey: separate into table name and field in that table
2236 # (must be linked by custnum)
2237 $subfield = lc($subfield);
2238 ($table2, $subfield) = ($1, $2) if $subfield =~ /^(\w+)?\.(\w+)$/;
2239 $subfield = $pkey if $subfield eq 'number';
2241 # if it's compound, create a join from cust_main or cust_svc to that
2242 # table, using custnum or svcnum, and Limit on that table instead.
2244 foreach my $a (@alias) {
2255 # do the actual Limit
2256 $self->SUPER::Limit(
2261 ENTRYAGGREGATOR => 'AND',
2262 # no SUBCLAUSE needed, limits on different aliases across left joins
2263 # are inherently independent
2266 # then, since it's a left join, exclude tickets for which there is now
2267 # no matching record in the table we just limited on. (Or where there
2268 # is a matching record, if $is_negative.)
2269 # For a cust_main query (where there are two different aliases), this
2270 # will produce a subclause: "cust_main_1.custnum IS NOT NULL OR
2271 # cust_main_2.custnum IS NOT NULL" (or "IS NULL AND..." for a negative
2278 OPERATOR => $is_negative ? 'IS' : 'IS NOT',
2281 ENTRYAGGREGATOR => $is_negative ? 'AND' : 'OR',
2282 SUBCLAUSE => 'fs_limit',
2287 foreach my $_SQLLimit (@_SQLLimit) {
2288 $self->_SQLLimit( %$_SQLLimit);
2298 Takes a paramhash with the fields FIELD, OPERATOR, VALUE and DESCRIPTION
2299 Generally best called from LimitFoo methods
2309 DESCRIPTION => undef,
2312 $args{'DESCRIPTION'} = $self->loc(
2313 "[_1] [_2] [_3]", $args{'FIELD'},
2314 $args{'OPERATOR'}, $args{'VALUE'}
2316 if ( !defined $args{'DESCRIPTION'} );
2318 my $index = $self->_NextIndex;
2320 # make the TicketRestrictions hash the equivalent of whatever we just passed in;
2322 %{ $self->{'TicketRestrictions'}{$index} } = %args;
2324 $self->{'RecalcTicketLimits'} = 1;
2326 # If we're looking at the effective id, we don't want to append the other clause
2327 # which limits us to tickets where id = effective id
2328 if ( $args{'FIELD'} eq 'EffectiveId'
2329 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
2331 $self->{'looking_at_effective_id'} = 1;
2334 if ( $args{'FIELD'} eq 'Type'
2335 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
2337 $self->{'looking_at_type'} = 1;
2348 LimitQueue takes a paramhash with the fields OPERATOR and VALUE.
2349 OPERATOR is one of = or !=. (It defaults to =).
2350 VALUE is a queue id or Name.
2363 #TODO VALUE should also take queue objects
2364 if ( defined $args{'VALUE'} && $args{'VALUE'} !~ /^\d+$/ ) {
2365 my $queue = RT::Queue->new( $self->CurrentUser );
2366 $queue->Load( $args{'VALUE'} );
2367 $args{'VALUE'} = $queue->Id;
2370 # What if they pass in an Id? Check for isNum() and convert to
2373 #TODO check for a valid queue here
2377 VALUE => $args{'VALUE'},
2378 OPERATOR => $args{'OPERATOR'},
2379 DESCRIPTION => join(
2380 ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{'VALUE'},
2390 Takes a paramhash with the fields OPERATOR and VALUE.
2391 OPERATOR is one of = or !=.
2394 RT adds Status != 'deleted' until object has
2395 allow_deleted_search internal property set.
2396 $tickets->{'allow_deleted_search'} = 1;
2397 $tickets->LimitStatus( VALUE => 'deleted' );
2409 VALUE => $args{'VALUE'},
2410 OPERATOR => $args{'OPERATOR'},
2411 DESCRIPTION => join( ' ',
2412 $self->loc('Status'), $args{'OPERATOR'},
2413 $self->loc( $args{'VALUE'} ) ),
2421 If called, this search will not automatically limit the set of results found
2422 to tickets of type "Ticket". Tickets of other types, such as "project" and
2423 "approval" will be found.
2430 # Instead of faking a Limit that later gets ignored, fake up the
2431 # fact that we're already looking at type, so that the check in
2432 # Tickets_SQL/FromSQL goes down the right branch
2434 # $self->LimitType(VALUE => '__any');
2435 $self->{looking_at_type} = 1;
2442 Takes a paramhash with the fields OPERATOR and VALUE.
2443 OPERATOR is one of = or !=, it defaults to "=".
2444 VALUE is a string to search for in the type of the ticket.
2459 VALUE => $args{'VALUE'},
2460 OPERATOR => $args{'OPERATOR'},
2461 DESCRIPTION => join( ' ',
2462 $self->loc('Type'), $args{'OPERATOR'}, $args{'Limit'}, ),
2472 Takes a paramhash with the fields OPERATOR and VALUE.
2473 OPERATOR is one of = or !=.
2474 VALUE is a string to search for in the subject of the ticket.
2483 VALUE => $args{'VALUE'},
2484 OPERATOR => $args{'OPERATOR'},
2485 DESCRIPTION => join( ' ',
2486 $self->loc('Subject'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2492 # Things that can be > < = !=
2497 Takes a paramhash with the fields OPERATOR and VALUE.
2498 OPERATOR is one of =, >, < or !=.
2499 VALUE is a ticket Id to search for
2512 VALUE => $args{'VALUE'},
2513 OPERATOR => $args{'OPERATOR'},
2515 join( ' ', $self->loc('Id'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2521 =head2 LimitPriority
2523 Takes a paramhash with the fields OPERATOR and VALUE.
2524 OPERATOR is one of =, >, < or !=.
2525 VALUE is a value to match the ticket\'s priority against
2533 FIELD => 'Priority',
2534 VALUE => $args{'VALUE'},
2535 OPERATOR => $args{'OPERATOR'},
2536 DESCRIPTION => join( ' ',
2537 $self->loc('Priority'),
2538 $args{'OPERATOR'}, $args{'VALUE'}, ),
2544 =head2 LimitInitialPriority
2546 Takes a paramhash with the fields OPERATOR and VALUE.
2547 OPERATOR is one of =, >, < or !=.
2548 VALUE is a value to match the ticket\'s initial priority against
2553 sub LimitInitialPriority {
2557 FIELD => 'InitialPriority',
2558 VALUE => $args{'VALUE'},
2559 OPERATOR => $args{'OPERATOR'},
2560 DESCRIPTION => join( ' ',
2561 $self->loc('Initial Priority'), $args{'OPERATOR'},
2568 =head2 LimitFinalPriority
2570 Takes a paramhash with the fields OPERATOR and VALUE.
2571 OPERATOR is one of =, >, < or !=.
2572 VALUE is a value to match the ticket\'s final priority against
2576 sub LimitFinalPriority {
2580 FIELD => 'FinalPriority',
2581 VALUE => $args{'VALUE'},
2582 OPERATOR => $args{'OPERATOR'},
2583 DESCRIPTION => join( ' ',
2584 $self->loc('Final Priority'), $args{'OPERATOR'},
2591 =head2 LimitTimeWorked
2593 Takes a paramhash with the fields OPERATOR and VALUE.
2594 OPERATOR is one of =, >, < or !=.
2595 VALUE is a value to match the ticket's TimeWorked attribute
2599 sub LimitTimeWorked {
2603 FIELD => 'TimeWorked',
2604 VALUE => $args{'VALUE'},
2605 OPERATOR => $args{'OPERATOR'},
2606 DESCRIPTION => join( ' ',
2607 $self->loc('Time Worked'),
2608 $args{'OPERATOR'}, $args{'VALUE'}, ),
2614 =head2 LimitTimeLeft
2616 Takes a paramhash with the fields OPERATOR and VALUE.
2617 OPERATOR is one of =, >, < or !=.
2618 VALUE is a value to match the ticket's TimeLeft attribute
2626 FIELD => 'TimeLeft',
2627 VALUE => $args{'VALUE'},
2628 OPERATOR => $args{'OPERATOR'},
2629 DESCRIPTION => join( ' ',
2630 $self->loc('Time Left'),
2631 $args{'OPERATOR'}, $args{'VALUE'}, ),
2641 Takes a paramhash with the fields OPERATOR and VALUE.
2642 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2643 VALUE is a string to search for in the body of the ticket
2652 VALUE => $args{'VALUE'},
2653 OPERATOR => $args{'OPERATOR'},
2654 DESCRIPTION => join( ' ',
2655 $self->loc('Ticket content'), $args{'OPERATOR'},
2662 =head2 LimitFilename
2664 Takes a paramhash with the fields OPERATOR and VALUE.
2665 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2666 VALUE is a string to search for in the body of the ticket
2674 FIELD => 'Filename',
2675 VALUE => $args{'VALUE'},
2676 OPERATOR => $args{'OPERATOR'},
2677 DESCRIPTION => join( ' ',
2678 $self->loc('Attachment filename'), $args{'OPERATOR'},
2684 =head2 LimitContentType
2686 Takes a paramhash with the fields OPERATOR and VALUE.
2687 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2688 VALUE is a content type to search ticket attachments for
2692 sub LimitContentType {
2696 FIELD => 'ContentType',
2697 VALUE => $args{'VALUE'},
2698 OPERATOR => $args{'OPERATOR'},
2699 DESCRIPTION => join( ' ',
2700 $self->loc('Ticket content type'), $args{'OPERATOR'},
2711 Takes a paramhash with the fields OPERATOR and VALUE.
2712 OPERATOR is one of = or !=.
2724 my $owner = RT::User->new( $self->CurrentUser );
2725 $owner->Load( $args{'VALUE'} );
2727 # FIXME: check for a valid $owner
2730 VALUE => $args{'VALUE'},
2731 OPERATOR => $args{'OPERATOR'},
2732 DESCRIPTION => join( ' ',
2733 $self->loc('Owner'), $args{'OPERATOR'}, $owner->Name(), ),
2743 Takes a paramhash with the fields OPERATOR, TYPE and VALUE.
2744 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2745 VALUE is a value to match the ticket\'s watcher email addresses against
2746 TYPE is the sort of watchers you want to match against. Leave it undef if you want to search all of them
2760 #build us up a description
2761 my ( $watcher_type, $desc );
2762 if ( $args{'TYPE'} ) {
2763 $watcher_type = $args{'TYPE'};
2766 $watcher_type = "Watcher";
2770 FIELD => $watcher_type,
2771 VALUE => $args{'VALUE'},
2772 OPERATOR => $args{'OPERATOR'},
2773 TYPE => $args{'TYPE'},
2774 DESCRIPTION => join( ' ',
2775 $self->loc($watcher_type),
2776 $args{'OPERATOR'}, $args{'VALUE'}, ),
2785 =head2 LimitLinkedTo
2787 LimitLinkedTo takes a paramhash with two fields: TYPE and TARGET
2788 TYPE limits the sort of link we want to search on
2790 TYPE = { RefersTo, MemberOf, DependsOn }
2792 TARGET is the id or URI of the TARGET of the link
2806 FIELD => 'LinkedTo',
2808 TARGET => $args{'TARGET'},
2809 TYPE => $args{'TYPE'},
2810 DESCRIPTION => $self->loc(
2811 "Tickets [_1] by [_2]",
2812 $self->loc( $args{'TYPE'} ),
2815 OPERATOR => $args{'OPERATOR'},
2821 =head2 LimitLinkedFrom
2823 LimitLinkedFrom takes a paramhash with two fields: TYPE and BASE
2824 TYPE limits the sort of link we want to search on
2827 BASE is the id or URI of the BASE of the link
2831 sub LimitLinkedFrom {
2840 # translate RT2 From/To naming to RT3 TicketSQL naming
2841 my %fromToMap = qw(DependsOn DependentOn
2843 RefersTo ReferredToBy);
2845 my $type = $args{'TYPE'};
2846 $type = $fromToMap{$type} if exists( $fromToMap{$type} );
2849 FIELD => 'LinkedTo',
2851 BASE => $args{'BASE'},
2853 DESCRIPTION => $self->loc(
2854 "Tickets [_1] [_2]",
2855 $self->loc( $args{'TYPE'} ),
2858 OPERATOR => $args{'OPERATOR'},
2865 my $ticket_id = shift;
2866 return $self->LimitLinkedTo(
2868 TARGET => $ticket_id,
2874 sub LimitHasMember {
2876 my $ticket_id = shift;
2877 return $self->LimitLinkedFrom(
2879 BASE => "$ticket_id",
2880 TYPE => 'HasMember',
2887 sub LimitDependsOn {
2889 my $ticket_id = shift;
2890 return $self->LimitLinkedTo(
2892 TARGET => $ticket_id,
2893 TYPE => 'DependsOn',
2900 sub LimitDependedOnBy {
2902 my $ticket_id = shift;
2903 return $self->LimitLinkedFrom(
2906 TYPE => 'DependentOn',
2915 my $ticket_id = shift;
2916 return $self->LimitLinkedTo(
2918 TARGET => $ticket_id,
2926 sub LimitReferredToBy {
2928 my $ticket_id = shift;
2929 return $self->LimitLinkedFrom(
2932 TYPE => 'ReferredToBy',
2940 =head2 LimitDate (FIELD => 'DateField', OPERATOR => $oper, VALUE => $ISODate)
2942 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2944 OPERATOR is one of > or <
2945 VALUE is a date and time in ISO format in GMT
2946 FIELD is one of Starts, Started, Told, Created, Resolved, LastUpdated
2948 There are also helper functions of the form LimitFIELD that eliminate
2949 the need to pass in a FIELD argument.
2963 #Set the description if we didn't get handed it above
2964 unless ( $args{'DESCRIPTION'} ) {
2965 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2966 . $args{'OPERATOR'} . " "
2967 . $args{'VALUE'} . " GMT";
2970 $self->Limit(%args);
2977 $self->LimitDate( FIELD => 'Created', @_ );
2982 $self->LimitDate( FIELD => 'Due', @_ );
2988 $self->LimitDate( FIELD => 'Starts', @_ );
2994 $self->LimitDate( FIELD => 'Started', @_ );
2999 $self->LimitDate( FIELD => 'Resolved', @_ );
3004 $self->LimitDate( FIELD => 'Told', @_ );
3007 sub LimitLastUpdated {
3009 $self->LimitDate( FIELD => 'LastUpdated', @_ );
3014 =head2 LimitTransactionDate (OPERATOR => $oper, VALUE => $ISODate)
3016 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
3018 OPERATOR is one of > or <
3019 VALUE is a date and time in ISO format in GMT
3024 sub LimitTransactionDate {
3027 FIELD => 'TransactionDate',
3034 # <20021217042756.GK28744@pallas.fsck.com>
3035 # "Kill It" - Jesse.
3037 #Set the description if we didn't get handed it above
3038 unless ( $args{'DESCRIPTION'} ) {
3039 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
3040 . $args{'OPERATOR'} . " "
3041 . $args{'VALUE'} . " GMT";
3044 $self->Limit(%args);
3051 =head2 LimitCustomField
3053 Takes a paramhash of key/value pairs with the following keys:
3057 =item CUSTOMFIELD - CustomField name or id. If a name is passed, an additional parameter QUEUE may also be passed to distinguish the custom field.
3059 =item OPERATOR - The usual Limit operators
3061 =item VALUE - The value to compare against
3067 sub LimitCustomField {
3071 CUSTOMFIELD => undef,
3073 DESCRIPTION => undef,
3074 FIELD => 'CustomFieldValue',
3079 my $CF = RT::CustomField->new( $self->CurrentUser );
3080 if ( $args{CUSTOMFIELD} =~ /^\d+$/ ) {
3081 $CF->Load( $args{CUSTOMFIELD} );
3084 $CF->LoadByNameAndQueue(
3085 Name => $args{CUSTOMFIELD},
3086 Queue => $args{QUEUE}
3088 $args{CUSTOMFIELD} = $CF->Id;
3091 #If we are looking to compare with a null value.
3092 if ( $args{'OPERATOR'} =~ /^is$/i ) {
3093 $args{'DESCRIPTION'}
3094 ||= $self->loc( "Custom field [_1] has no value.", $CF->Name );
3096 elsif ( $args{'OPERATOR'} =~ /^is not$/i ) {
3097 $args{'DESCRIPTION'}
3098 ||= $self->loc( "Custom field [_1] has a value.", $CF->Name );
3101 # if we're not looking to compare with a null value
3103 $args{'DESCRIPTION'} ||= $self->loc( "Custom field [_1] [_2] [_3]",
3104 $CF->Name, $args{OPERATOR}, $args{VALUE} );
3107 if ( defined $args{'QUEUE'} && $args{'QUEUE'} =~ /\D/ ) {
3108 my $QueueObj = RT::Queue->new( $self->CurrentUser );
3109 $QueueObj->Load( $args{'QUEUE'} );
3110 $args{'QUEUE'} = $QueueObj->Id;
3112 delete $args{'QUEUE'} unless defined $args{'QUEUE'} && length $args{'QUEUE'};
3115 @rest = ( ENTRYAGGREGATOR => 'AND' )
3116 if ( $CF->Type eq 'SelectMultiple' );
3119 VALUE => $args{VALUE},
3121 .(defined $args{'QUEUE'}? ".{$args{'QUEUE'}}" : '' )
3122 .".{" . $CF->Name . "}",
3123 OPERATOR => $args{OPERATOR},
3128 $self->{'RecalcTicketLimits'} = 1;
3135 Keep track of the counter for the array of restrictions
3141 return ( $self->{'restriction_index'}++ );
3149 $self->{'table'} = "Tickets";
3150 $self->{'RecalcTicketLimits'} = 1;
3151 $self->{'looking_at_effective_id'} = 0;
3152 $self->{'looking_at_type'} = 0;
3153 $self->{'restriction_index'} = 1;
3154 $self->{'primary_key'} = "id";
3155 delete $self->{'items_array'};
3156 delete $self->{'item_map'};
3157 delete $self->{'columns_to_display'};
3158 $self->SUPER::_Init(@_);
3167 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3168 return ( $self->SUPER::Count() );
3174 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3175 return ( $self->SUPER::CountAll() );
3180 =head2 ItemsArrayRef
3182 Returns a reference to the set of all items found in this search
3189 return $self->{'items_array'} if $self->{'items_array'};
3191 my $placeholder = $self->_ItemsCounter;
3192 $self->GotoFirstItem();
3193 while ( my $item = $self->Next ) {
3194 push( @{ $self->{'items_array'} }, $item );
3196 $self->GotoItem($placeholder);
3197 $self->{'items_array'}
3198 = $self->ItemsOrderBy( $self->{'items_array'} );
3200 return $self->{'items_array'};
3203 sub ItemsArrayRefWindow {
3207 my @old = ($self->_ItemsCounter, $self->RowsPerPage, $self->FirstRow+1);
3209 $self->RowsPerPage( $window );
3211 $self->GotoFirstItem;
3214 while ( my $item = $self->Next ) {
3218 $self->RowsPerPage( $old[1] );
3219 $self->FirstRow( $old[2] );
3220 $self->GotoItem( $old[0] );
3229 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
3231 my $Ticket = $self->SUPER::Next;
3232 return $Ticket unless $Ticket;
3234 if ( $Ticket->__Value('Status') eq 'deleted'
3235 && !$self->{'allow_deleted_search'} )
3239 elsif ( RT->Config->Get('UseSQLForACLChecks') ) {
3240 # if we found a ticket with this option enabled then
3241 # all tickets we found are ACLed, cache this fact
3242 my $key = join ";:;", $self->CurrentUser->id, 'ShowTicket', 'RT::Ticket-'. $Ticket->id;
3243 $RT::Principal::_ACL_CACHE->set( $key => 1 );
3246 elsif ( $Ticket->CurrentUserHasRight('ShowTicket') ) {
3251 # If the user doesn't have the right to show this ticket
3258 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
3259 return $self->SUPER::_DoSearch( @_ );
3264 $self->CurrentUserCanSee if RT->Config->Get('UseSQLForACLChecks');
3265 return $self->SUPER::_DoCount( @_ );
3271 my $cache_key = 'RolesHasRight;:;ShowTicket';
3273 if ( my $cached = $RT::Principal::_ACL_CACHE->fetch( $cache_key ) ) {
3277 my $ACL = RT::ACL->new( RT->SystemUser );
3278 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
3279 $ACL->Limit( FIELD => 'PrincipalType', OPERATOR => '!=', VALUE => 'Group' );
3280 my $principal_alias = $ACL->Join(
3282 FIELD1 => 'PrincipalId',
3283 TABLE2 => 'Principals',
3286 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3289 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
3290 my $role = $ACE->__Value('PrincipalType');
3291 my $type = $ACE->__Value('ObjectType');
3292 if ( $type eq 'RT::System' ) {
3295 elsif ( $type eq 'RT::Queue' ) {
3296 next if $res{ $role } && !ref $res{ $role };
3297 push @{ $res{ $role } ||= [] }, $ACE->__Value('ObjectId');
3300 $RT::Logger->error('ShowTicket right is granted on unsupported object');
3303 $RT::Principal::_ACL_CACHE->set( $cache_key => \%res );
3307 sub _DirectlyCanSeeIn {
3309 my $id = $self->CurrentUser->id;
3311 my $cache_key = 'User-'. $id .';:;ShowTicket;:;DirectlyCanSeeIn';
3312 if ( my $cached = $RT::Principal::_ACL_CACHE->fetch( $cache_key ) ) {
3316 my $ACL = RT::ACL->new( RT->SystemUser );
3317 $ACL->Limit( FIELD => 'RightName', VALUE => 'ShowTicket' );
3318 my $principal_alias = $ACL->Join(
3320 FIELD1 => 'PrincipalId',
3321 TABLE2 => 'Principals',
3324 $ACL->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3325 my $cgm_alias = $ACL->Join(
3327 FIELD1 => 'PrincipalId',
3328 TABLE2 => 'CachedGroupMembers',
3329 FIELD2 => 'GroupId',
3331 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
3332 $ACL->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
3335 foreach my $ACE ( @{ $ACL->ItemsArrayRef } ) {
3336 my $type = $ACE->__Value('ObjectType');
3337 if ( $type eq 'RT::System' ) {
3338 # If user is direct member of a group that has the right
3339 # on the system then he can see any ticket
3340 $RT::Principal::_ACL_CACHE->set( $cache_key => [-1] );
3343 elsif ( $type eq 'RT::Queue' ) {
3344 push @res, $ACE->__Value('ObjectId');
3347 $RT::Logger->error('ShowTicket right is granted on unsupported object');
3350 $RT::Principal::_ACL_CACHE->set( $cache_key => \@res );
3354 sub CurrentUserCanSee {
3356 return if $self->{'_sql_current_user_can_see_applied'};
3358 return $self->{'_sql_current_user_can_see_applied'} = 1
3359 if $self->CurrentUser->UserObj->HasRight(
3360 Right => 'SuperUser', Object => $RT::System
3363 my $id = $self->CurrentUser->id;
3365 # directly can see in all queues then we have nothing to do
3366 my @direct_queues = $self->_DirectlyCanSeeIn;
3367 return $self->{'_sql_current_user_can_see_applied'} = 1
3368 if @direct_queues && $direct_queues[0] == -1;
3370 my %roles = $self->_RolesCanSee;
3372 my %skip = map { $_ => 1 } @direct_queues;
3373 foreach my $role ( keys %roles ) {
3374 next unless ref $roles{ $role };
3376 my @queues = grep !$skip{$_}, @{ $roles{ $role } };
3378 $roles{ $role } = \@queues;
3380 delete $roles{ $role };
3385 # there is no global watchers, only queues and tickes, if at
3386 # some point we will add global roles then it's gonna blow
3387 # the idea here is that if the right is set globaly for a role
3388 # and user plays this role for a queue directly not a ticket
3389 # then we have to check in advance
3390 if ( my @tmp = grep $_ ne 'Owner' && !ref $roles{ $_ }, keys %roles ) {
3392 my $groups = RT::Groups->new( RT->SystemUser );
3393 $groups->Limit( FIELD => 'Domain', VALUE => 'RT::Queue-Role' );
3395 $groups->Limit( FIELD => 'Type', VALUE => $_ );
3397 my $principal_alias = $groups->Join(
3400 TABLE2 => 'Principals',
3403 $groups->Limit( ALIAS => $principal_alias, FIELD => 'Disabled', VALUE => 0 );
3404 my $cgm_alias = $groups->Join(
3407 TABLE2 => 'CachedGroupMembers',
3408 FIELD2 => 'GroupId',
3410 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'MemberId', VALUE => $id );
3411 $groups->Limit( ALIAS => $cgm_alias, FIELD => 'Disabled', VALUE => 0 );
3412 while ( my $group = $groups->Next ) {
3413 push @direct_queues, $group->Instance;
3417 unless ( @direct_queues || keys %roles ) {
3418 $self->SUPER::Limit(
3423 ENTRYAGGREGATOR => 'AND',
3425 return $self->{'_sql_current_user_can_see_applied'} = 1;
3429 my $join_roles = keys %roles;
3430 $join_roles = 0 if $join_roles == 1 && $roles{'Owner'};
3431 my ($role_group_alias, $cgm_alias);
3432 if ( $join_roles ) {
3433 $role_group_alias = $self->_RoleGroupsJoin( New => 1 );
3434 $cgm_alias = $self->_GroupMembersJoin( GroupsAlias => $role_group_alias );
3435 $self->SUPER::Limit(
3436 LEFTJOIN => $cgm_alias,
3437 FIELD => 'MemberId',
3442 my $limit_queues = sub {
3446 return unless @queues;
3447 if ( @queues == 1 ) {
3448 $self->SUPER::Limit(
3453 ENTRYAGGREGATOR => $ea,
3456 $self->SUPER::_OpenParen('ACL');
3457 foreach my $q ( @queues ) {
3458 $self->SUPER::Limit(
3463 ENTRYAGGREGATOR => $ea,
3467 $self->SUPER::_CloseParen('ACL');
3472 $self->SUPER::_OpenParen('ACL');
3474 $ea = 'OR' if $limit_queues->( $ea, @direct_queues );
3475 while ( my ($role, $queues) = each %roles ) {
3476 $self->SUPER::_OpenParen('ACL');
3477 if ( $role eq 'Owner' ) {
3478 $self->SUPER::Limit(
3482 ENTRYAGGREGATOR => $ea,
3486 $self->SUPER::Limit(
3488 ALIAS => $cgm_alias,
3489 FIELD => 'MemberId',
3490 OPERATOR => 'IS NOT',
3493 ENTRYAGGREGATOR => $ea,
3495 $self->SUPER::Limit(
3497 ALIAS => $role_group_alias,
3500 ENTRYAGGREGATOR => 'AND',
3503 $limit_queues->( 'AND', @$queues ) if ref $queues;
3504 $ea = 'OR' if $ea eq 'AND';
3505 $self->SUPER::_CloseParen('ACL');
3507 $self->SUPER::_CloseParen('ACL');
3509 return $self->{'_sql_current_user_can_see_applied'} = 1;
3516 =head2 LoadRestrictions
3518 LoadRestrictions takes a string which can fully populate the TicketRestrictons hash.
3519 TODO It is not yet implemented
3525 =head2 DescribeRestrictions
3528 Returns a hash keyed by restriction id.
3529 Each element of the hash is currently a one element hash that contains DESCRIPTION which
3530 is a description of the purpose of that TicketRestriction
3534 sub DescribeRestrictions {
3539 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
3540 $listing{$row} = $self->{'TicketRestrictions'}{$row}{'DESCRIPTION'};
3547 =head2 RestrictionValues FIELD
3549 Takes a restriction field and returns a list of values this field is restricted
3554 sub RestrictionValues {
3557 map $self->{'TicketRestrictions'}{$_}{'VALUE'}, grep {
3558 $self->{'TicketRestrictions'}{$_}{'FIELD'} eq $field
3559 && $self->{'TicketRestrictions'}{$_}{'OPERATOR'} eq "="
3561 keys %{ $self->{'TicketRestrictions'} };
3566 =head2 ClearRestrictions
3568 Removes all restrictions irretrievably
3572 sub ClearRestrictions {
3574 delete $self->{'TicketRestrictions'};
3575 $self->{'looking_at_effective_id'} = 0;
3576 $self->{'looking_at_type'} = 0;
3577 $self->{'RecalcTicketLimits'} = 1;
3582 =head2 DeleteRestriction
3584 Takes the row Id of a restriction (From DescribeRestrictions' output, for example.
3585 Removes that restriction from the session's limits.
3589 sub DeleteRestriction {
3592 delete $self->{'TicketRestrictions'}{$row};
3594 $self->{'RecalcTicketLimits'} = 1;
3596 #make the underlying easysearch object forget all its preconceptions
3601 # Convert a set of oldstyle SB Restrictions to Clauses for RQL
3603 sub _RestrictionsToClauses {
3607 foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) {
3608 my $restriction = $self->{'TicketRestrictions'}{$row};
3610 # We need to reimplement the subclause aggregation that SearchBuilder does.
3611 # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main',
3612 # Then SB AND's the different Subclauses together.
3614 # So, we want to group things into Subclauses, convert them to
3615 # SQL, and then join them with the appropriate DefaultEA.
3616 # Then join each subclause group with AND.
3618 my $field = $restriction->{'FIELD'};
3619 my $realfield = $field; # CustomFields fake up a fieldname, so
3620 # we need to figure that out
3623 # Rewrite LinkedTo meta field to the real field
3624 if ( $field =~ /LinkedTo/ ) {
3625 $realfield = $field = $restriction->{'TYPE'};
3629 # Handle subkey fields with a different real field
3630 if ( $field =~ /^(\w+)\./ ) {
3634 die "I don't know about $field yet"
3635 unless ( exists $FIELD_METADATA{$realfield}
3636 or $restriction->{CUSTOMFIELD} );
3638 my $type = $FIELD_METADATA{$realfield}->[0];
3639 my $op = $restriction->{'OPERATOR'};
3643 map { $restriction->{$_} } qw(VALUE TICKET BASE TARGET)
3646 # this performs the moral equivalent of defined or/dor/C<//>,
3647 # without the short circuiting.You need to use a 'defined or'
3648 # type thing instead of just checking for truth values, because
3649 # VALUE could be 0.(i.e. "false")
3651 # You could also use this, but I find it less aesthetic:
3652 # (although it does short circuit)
3653 #( defined $restriction->{'VALUE'}? $restriction->{VALUE} :
3654 # defined $restriction->{'TICKET'} ?
3655 # $restriction->{TICKET} :
3656 # defined $restriction->{'BASE'} ?
3657 # $restriction->{BASE} :
3658 # defined $restriction->{'TARGET'} ?
3659 # $restriction->{TARGET} )
3661 my $ea = $restriction->{ENTRYAGGREGATOR}
3662 || $DefaultEA{$type}
3665 die "Invalid operator $op for $field ($type)"
3666 unless exists $ea->{$op};
3670 # Each CustomField should be put into a different Clause so they
3671 # are ANDed together.
3672 if ( $restriction->{CUSTOMFIELD} ) {
3673 $realfield = $field;
3676 exists $clause{$realfield} or $clause{$realfield} = [];
3679 $field =~ s!(['\\])!\\$1!g;
3680 $value =~ s!(['\\])!\\$1!g;
3681 my $data = [ $ea, $type, $field, $op, $value ];
3683 # here is where we store extra data, say if it's a keyword or
3684 # something. (I.e. "TYPE SPECIFIC STUFF")
3686 if (lc $ea eq 'none') {
3687 $clause{$realfield} = [ $data ];
3689 push @{ $clause{$realfield} }, $data;
3697 =head2 _ProcessRestrictions PARAMHASH
3699 # The new _ProcessRestrictions is somewhat dependent on the SQL stuff,
3700 # but isn't quite generic enough to move into Tickets_SQL.
3704 sub _ProcessRestrictions {
3707 #Blow away ticket aliases since we'll need to regenerate them for
3709 delete $self->{'TicketAliases'};
3710 delete $self->{'items_array'};
3711 delete $self->{'item_map'};
3712 delete $self->{'raw_rows'};
3713 delete $self->{'rows'};
3714 delete $self->{'count_all'};
3716 my $sql = $self->Query; # Violating the _SQL namespace
3717 if ( !$sql || $self->{'RecalcTicketLimits'} ) {
3719 # "Restrictions to Clauses Branch\n";
3720 my $clauseRef = eval { $self->_RestrictionsToClauses; };
3722 $RT::Logger->error( "RestrictionsToClauses: " . $@ );
3726 $sql = $self->ClausesToSQL($clauseRef);
3727 $self->FromSQL($sql) if $sql;
3731 $self->{'RecalcTicketLimits'} = 0;
3735 =head2 _BuildItemMap
3737 Build up a L</ItemMap> of first/last/next/prev items, so that we can
3738 display search nav quickly.
3745 my $window = RT->Config->Get('TicketsItemMapSize');
3747 $self->{'item_map'} = {};
3749 my $items = $self->ItemsArrayRefWindow( $window );
3750 return unless $items && @$items;
3753 $self->{'item_map'}{'first'} = $items->[0]->EffectiveId;
3754 for ( my $i = 0; $i < @$items; $i++ ) {
3755 my $item = $items->[$i];
3756 my $id = $item->EffectiveId;
3757 $self->{'item_map'}{$id}{'defined'} = 1;
3758 $self->{'item_map'}{$id}{'prev'} = $prev;
3759 $self->{'item_map'}{$id}{'next'} = $items->[$i+1]->EffectiveId
3763 $self->{'item_map'}{'last'} = $prev
3764 if !$window || @$items < $window;
3769 Returns an a map of all items found by this search. The map is a hash
3773 first => <first ticket id found>,
3774 last => <last ticket id found or undef>,
3777 prev => <the ticket id found before>,
3778 next => <the ticket id found after>,
3790 $self->_BuildItemMap unless $self->{'item_map'};
3791 return $self->{'item_map'};
3797 =head2 PrepForSerialization
3799 You don't want to serialize a big tickets object, as
3800 the {items} hash will be instantly invalid _and_ eat
3805 sub PrepForSerialization {
3807 delete $self->{'items'};
3808 delete $self->{'items_array'};
3809 $self->RedoSearch();
3814 RT::Tickets supports several flags which alter search behavior:
3817 allow_deleted_search (Otherwise never show deleted tickets in search results)
3818 looking_at_type (otherwise limit to type=ticket)
3820 These flags are set by calling
3822 $tickets->{'flagname'} = 1;
3824 BUG: There should be an API for this
3834 Returns an empty new RT::Ticket item
3840 return(RT::Ticket->new($self->CurrentUser));
3842 RT::Base->_ImportOverlays();