1 # BEGIN BPS TAGGED BLOCK {{{
5 # This software is Copyright (c) 1996-2009 Best Practical Solutions, LLC
6 # <jesse@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 }}}
50 # - Decimated ProcessRestrictions and broke it into multiple
51 # functions joined by a LUT
52 # - Semi-Generic SQL stuff moved to another file
54 # Known Issues: FIXME!
56 # - ClearRestrictions and Reinitialization is messy and unclear. The
57 # only good way to do it is to create a new RT::Tickets object.
61 RT::Tickets - A collection of Ticket objects
67 my $tickets = new RT::Tickets($CurrentUser);
71 A collection of RT::Tickets.
77 ok (require RT::Tickets);
78 ok( my $testtickets = RT::Tickets->new( $RT::SystemUser ) );
79 ok( $testtickets->LimitStatus( VALUE => 'deleted' ) );
80 # Should be zero until 'allow_deleted_search'
81 ok( $testtickets->Count == 0 );
90 no warnings qw(redefine);
93 use DBIx::SearchBuilder::Unique;
95 # Configuration Tables:
97 # FIELD_METADATA is a mapping of searchable Field name, to Type, and other
100 my %FIELD_METADATA = (
101 Status => [ 'ENUM', ],
102 Queue => [ 'ENUM' => 'Queue', ],
104 Creator => [ 'ENUM' => 'User', ],
105 LastUpdatedBy => [ 'ENUM' => 'User', ],
106 Owner => [ 'WATCHERFIELD' => 'Owner', ],
107 EffectiveId => [ 'INT', ],
109 InitialPriority => [ 'INT', ],
110 FinalPriority => [ 'INT', ],
111 Priority => [ 'INT', ],
112 TimeLeft => [ 'INT', ],
113 TimeWorked => [ 'INT', ],
114 TimeEstimated => [ 'INT', ],
115 MemberOf => [ 'LINK' => To => 'MemberOf', ],
116 DependsOn => [ 'LINK' => To => 'DependsOn', ],
117 RefersTo => [ 'LINK' => To => 'RefersTo', ],
118 HasMember => [ 'LINK' => From => 'MemberOf', ],
119 DependentOn => [ 'LINK' => From => 'DependsOn', ],
120 DependedOnBy => [ 'LINK' => From => 'DependsOn', ],
121 ReferredToBy => [ 'LINK' => From => 'RefersTo', ],
122 Told => [ 'DATE' => 'Told', ],
123 Starts => [ 'DATE' => 'Starts', ],
124 Started => [ 'DATE' => 'Started', ],
125 Due => [ 'DATE' => 'Due', ],
126 Resolved => [ 'DATE' => 'Resolved', ],
127 LastUpdated => [ 'DATE' => 'LastUpdated', ],
128 Created => [ 'DATE' => 'Created', ],
129 Subject => [ 'STRING', ],
130 Content => [ 'TRANSFIELD', ],
131 ContentType => [ 'TRANSFIELD', ],
132 Filename => [ 'TRANSFIELD', ],
133 TransactionDate => [ 'TRANSDATE', ],
134 Requestor => [ 'WATCHERFIELD' => 'Requestor', ],
135 Requestors => [ 'WATCHERFIELD' => 'Requestor', ],
136 Cc => [ 'WATCHERFIELD' => 'Cc', ],
137 AdminCc => [ 'WATCHERFIELD' => 'AdminCc', ],
138 Watcher => [ 'WATCHERFIELD', ],
139 QueueCc => [ 'WATCHERFIELD' => 'Cc' => 'Queue', ],
140 QueueAdminCc => [ 'WATCHERFIELD' => 'AdminCc' => 'Queue', ],
141 QueueWatcher => [ 'WATCHERFIELD' => undef => 'Queue', ],
142 LinkedTo => [ 'LINKFIELD', ],
143 CustomFieldValue => [ 'CUSTOMFIELD', ],
144 CustomField => [ 'CUSTOMFIELD', ],
145 CF => [ 'CUSTOMFIELD', ],
146 Updated => [ 'TRANSDATE', ],
147 RequestorGroup => [ 'MEMBERSHIPFIELD' => 'Requestor', ],
148 CCGroup => [ 'MEMBERSHIPFIELD' => 'Cc', ],
149 AdminCCGroup => [ 'MEMBERSHIPFIELD' => 'AdminCc', ],
150 WatcherGroup => [ 'MEMBERSHIPFIELD', ],
151 HasAttribute => [ 'HASATTRIBUTE', 1 ],
152 HasNoAttribute => [ 'HASATTRIBUTE', 0 ],
155 # Mapping of Field Type to Function
157 ENUM => \&_EnumLimit,
159 LINK => \&_LinkLimit,
160 DATE => \&_DateLimit,
161 STRING => \&_StringLimit,
162 TRANSFIELD => \&_TransLimit,
163 TRANSDATE => \&_TransDateLimit,
164 WATCHERFIELD => \&_WatcherLimit,
165 MEMBERSHIPFIELD => \&_WatcherMembershipLimit,
166 LINKFIELD => \&_LinkFieldLimit,
167 CUSTOMFIELD => \&_CustomFieldLimit,
168 HASATTRIBUTE => \&_HasAttributeLimit,
170 my %can_bundle = (); # WATCHERFIELD => "yes", );
172 # Default EntryAggregator per type
173 # if you specify OP, you must specify all valid OPs
214 # Helper functions for passing the above lexically scoped tables above
215 # into Tickets_Overlay_SQL.
216 sub FIELDS { return \%FIELD_METADATA }
217 sub dispatch { return \%dispatch }
218 sub can_bundle { return \%can_bundle }
220 # Bring in the clowns.
221 require RT::Tickets_Overlay_SQL;
225 our @SORTFIELDS = qw(id Status
227 Owner Created Due Starts Started
229 Resolved LastUpdated Priority TimeWorked TimeLeft);
233 Returns the list of fields that lists of tickets can easily be sorted by
239 return (@SORTFIELDS);
244 # BEGIN SQL STUFF *********************************
249 $self->SUPER::CleanSlate( @_ );
250 delete $self->{$_} foreach qw(
252 _sql_group_members_aliases
253 _sql_object_cfv_alias
254 _sql_role_group_aliases
257 _sql_u_watchers_alias_for_sort
258 _sql_u_watchers_aliases
262 =head1 Limit Helper Routines
264 These routines are the targets of a dispatch table depending on the
265 type of field. They all share the same signature:
267 my ($self,$field,$op,$value,@rest) = @_;
269 The values in @rest should be suitable for passing directly to
270 DBIx::SearchBuilder::Limit.
272 Essentially they are an expanded/broken out (and much simplified)
273 version of what ProcessRestrictions used to do. They're also much
274 more clearly delineated by the TYPE of field being processed.
278 Handle Fields which are limited to certain values, and potentially
279 need to be looked up from another class.
281 This subroutine actually handles two different kinds of fields. For
282 some the user is responsible for limiting the values. (i.e. Status,
285 For others, the value specified by the user will be looked by via
289 name of class to lookup in (Optional)
294 my ( $sb, $field, $op, $value, @rest ) = @_;
296 # SQL::Statement changes != to <>. (Can we remove this now?)
297 $op = "!=" if $op eq "<>";
299 die "Invalid Operation: $op for $field"
303 my $meta = $FIELD_METADATA{$field};
304 if ( defined $meta->[1] && defined $value && $value !~ /^\d+$/ ) {
305 my $class = "RT::" . $meta->[1];
306 my $o = $class->new( $sb->CurrentUser );
320 Handle fields where the values are limited to integers. (For example,
321 Priority, TimeWorked.)
329 my ( $sb, $field, $op, $value, @rest ) = @_;
331 die "Invalid Operator $op for $field"
332 unless $op =~ /^(=|!=|>|<|>=|<=)$/;
344 Handle fields which deal with links between tickets. (MemberOf, DependsOn)
347 1: Direction (From, To)
348 2: Link Type (MemberOf, DependsOn, RefersTo)
353 my ( $sb, $field, $op, $value, @rest ) = @_;
355 my $meta = $FIELD_METADATA{$field};
356 die "Incorrect Metadata for $field"
357 unless defined $meta->[1] && defined $meta->[2];
359 die "Invalid Operator $op for $field" unless $op =~ /^(=|!=|IS|IS NOT)$/io;
361 my $direction = $meta->[1];
365 if ( $direction eq 'To' ) {
366 $matchfield = "Target";
370 elsif ( $direction eq 'From' ) {
371 $linkfield = "Target";
372 $matchfield = "Base";
376 die "Invalid link direction '$meta->[1]' for $field\n";
379 my ($is_local, $is_null) = (1, 0);
380 if ( !$value || $value =~ /^null$/io ) {
382 $op = ($op =~ /^(=|IS)$/)? 'IS': 'IS NOT';
384 elsif ( $value =~ /\D/o ) {
387 $matchfield = "Local$matchfield" if $is_local;
395 #For doing a left join to find "unlinked tickets" we want to generate a query that looks like this
396 # SELECT main.* FROM Tickets main
397 # LEFT JOIN Links Links_1 ON ( (Links_1.Type = 'MemberOf')
398 # AND(main.id = Links_1.LocalTarget))
399 # WHERE Links_1.LocalBase IS NULL;
402 my $linkalias = $sb->Join(
407 FIELD2 => 'Local' . $linkfield
410 LEFTJOIN => $linkalias,
418 FIELD => $matchfield,
424 elsif ( $is_negative ) {
425 my $linkalias = $sb->Join(
430 FIELD2 => 'Local' . $linkfield
433 LEFTJOIN => $linkalias,
439 LEFTJOIN => $linkalias,
440 FIELD => $matchfield,
447 FIELD => $matchfield,
454 my $linkalias = $sb->NewAlias('Links');
465 FIELD => 'Local' . $linkfield,
469 ENTRYAGGREGATOR => 'AND',
473 FIELD => $matchfield,
476 ENTRYAGGREGATOR => 'AND',
484 Handle date fields. (Created, LastTold..)
487 1: type of link. (Probably not necessary.)
492 my ( $sb, $field, $op, $value, @rest ) = @_;
494 die "Invalid Date Op: $op"
495 unless $op =~ /^(=|>|<|>=|<=)$/;
497 my $meta = $FIELD_METADATA{$field};
498 die "Incorrect Meta Data for $field"
499 unless ( defined $meta->[1] );
501 my $date = RT::Date->new( $sb->CurrentUser );
502 $date->Set( Format => 'unknown', Value => $value );
506 # if we're specifying =, that means we want everything on a
507 # particular single day. in the database, we need to check for >
508 # and < the edges of that day.
510 $date->SetToMidnight( Timezone => 'server' );
511 my $daystart = $date->ISO;
513 my $dayend = $date->ISO;
529 ENTRYAGGREGATOR => 'AND',
547 Handle simple fields which are just strings. (Subject,Type)
555 my ( $sb, $field, $op, $value, @rest ) = @_;
559 # =, !=, LIKE, NOT LIKE
570 =head2 _TransDateLimit
572 Handle fields limiting based on Transaction Date.
574 The inpupt value must be in a format parseable by Time::ParseDate
581 # This routine should really be factored into translimit.
582 sub _TransDateLimit {
583 my ( $sb, $field, $op, $value, @rest ) = @_;
585 # See the comments for TransLimit, they apply here too
587 unless ( $sb->{_sql_transalias} ) {
588 $sb->{_sql_transalias} = $sb->Join(
591 TABLE2 => 'Transactions',
592 FIELD2 => 'ObjectId',
595 ALIAS => $sb->{_sql_transalias},
596 FIELD => 'ObjectType',
597 VALUE => 'RT::Ticket',
598 ENTRYAGGREGATOR => 'AND',
602 my $date = RT::Date->new( $sb->CurrentUser );
603 $date->Set( Format => 'unknown', Value => $value );
608 # if we're specifying =, that means we want everything on a
609 # particular single day. in the database, we need to check for >
610 # and < the edges of that day.
612 $date->SetToMidnight( Timezone => 'server' );
613 my $daystart = $date->ISO;
615 my $dayend = $date->ISO;
618 ALIAS => $sb->{_sql_transalias},
626 ALIAS => $sb->{_sql_transalias},
632 ENTRYAGGREGATOR => 'AND',
637 # not searching for a single day
640 #Search for the right field
642 ALIAS => $sb->{_sql_transalias},
656 Limit based on the Content of a transaction or the ContentType.
665 # Content, ContentType, Filename
667 # If only this was this simple. We've got to do something
670 #Basically, we want to make sure that the limits apply to
671 #the same attachment, rather than just another attachment
672 #for the same ticket, no matter how many clauses we lump
673 #on. We put them in TicketAliases so that they get nuked
674 #when we redo the join.
676 # In the SQL, we might have
677 # (( Content = foo ) or ( Content = bar AND Content = baz ))
678 # The AND group should share the same Alias.
680 # Actually, maybe it doesn't matter. We use the same alias and it
681 # works itself out? (er.. different.)
683 # Steal more from _ProcessRestrictions
685 # FIXME: Maybe look at the previous FooLimit call, and if it was a
686 # TransLimit and EntryAggregator == AND, reuse the Aliases?
688 # Or better - store the aliases on a per subclause basis - since
689 # those are going to be the things we want to relate to each other,
692 # maybe we should not allow certain kinds of aggregation of these
693 # clauses and do a psuedo regex instead? - the problem is getting
694 # them all into the same subclause when you have (A op B op C) - the
695 # way they get parsed in the tree they're in different subclauses.
697 my ( $self, $field, $op, $value, @rest ) = @_;
699 unless ( $self->{_sql_transalias} ) {
700 $self->{_sql_transalias} = $self->Join(
703 TABLE2 => 'Transactions',
704 FIELD2 => 'ObjectId',
707 ALIAS => $self->{_sql_transalias},
708 FIELD => 'ObjectType',
709 VALUE => 'RT::Ticket',
710 ENTRYAGGREGATOR => 'AND',
713 unless ( defined $self->{_sql_trattachalias} ) {
714 $self->{_sql_trattachalias} = $self->_SQLJoin(
715 TYPE => 'LEFT', # not all txns have an attachment
716 ALIAS1 => $self->{_sql_transalias},
718 TABLE2 => 'Attachments',
719 FIELD2 => 'TransactionId',
725 #Search for the right field
726 if ($field eq 'Content' and $RT::DontSearchFileAttachments) {
728 ALIAS => $self->{_sql_trattachalias},
732 SUBCLAUSE => 'contentquery',
733 ENTRYAGGREGATOR => 'AND',
736 ALIAS => $self->{_sql_trattachalias},
742 ENTRYAGGREGATOR => 'AND',
743 SUBCLAUSE => 'contentquery',
747 ALIAS => $self->{_sql_trattachalias},
752 ENTRYAGGREGATOR => 'AND',
763 Handle watcher limits. (Requestor, CC, etc..)
771 # Test to make sure that you can search for tickets by requestor address and
775 my $u1 = RT::User->new($RT::SystemUser);
776 ($id, $msg) = $u1->Create( Name => 'RequestorTestOne', EmailAddress => 'rqtest1@example.com');
778 my $u2 = RT::User->new($RT::SystemUser);
779 ($id, $msg) = $u2->Create( Name => 'RequestorTestTwo', EmailAddress => 'rqtest2@example.com');
782 my $t1 = RT::Ticket->new($RT::SystemUser);
784 ($id,$trans,$msg) =$t1->Create (Queue => 'general', Subject => 'Requestor test one', Requestor => [$u1->EmailAddress]);
787 my $t2 = RT::Ticket->new($RT::SystemUser);
788 ($id,$trans,$msg) =$t2->Create (Queue => 'general', Subject => 'Requestor test one', Requestor => [$u2->EmailAddress]);
792 my $t3 = RT::Ticket->new($RT::SystemUser);
793 ($id,$trans,$msg) =$t3->Create (Queue => 'general', Subject => 'Requestor test one', Requestor => [$u2->EmailAddress, $u1->EmailAddress]);
797 my $tix1 = RT::Tickets->new($RT::SystemUser);
798 $tix1->FromSQL('Requestor.EmailAddress LIKE "rqtest1" OR Requestor.EmailAddress LIKE "rqtest2"');
800 is ($tix1->Count, 3);
802 my $tix2 = RT::Tickets->new($RT::SystemUser);
803 $tix2->FromSQL('Requestor.Name LIKE "TestOne" OR Requestor.Name LIKE "TestTwo"');
805 is ($tix2->Count, 3);
808 my $tix3 = RT::Tickets->new($RT::SystemUser);
809 $tix3->FromSQL('Requestor.EmailAddress LIKE "rqtest1"');
811 is ($tix3->Count, 2);
813 my $tix4 = RT::Tickets->new($RT::SystemUser);
814 $tix4->FromSQL('Requestor.Name LIKE "TestOne" ');
816 is ($tix4->Count, 2);
818 # Searching for tickets that have two requestors isn't supported
819 # There's no way to differentiate "one requestor name that matches foo and bar"
820 # and "two requestors, one matching foo and one matching bar"
822 # my $tix5 = RT::Tickets->new($RT::SystemUser);
823 # $tix5->FromSQL('Requestor.Name LIKE "TestOne" AND Requestor.Name LIKE "TestTwo"');
825 # is ($tix5->Count, 1);
827 # my $tix6 = RT::Tickets->new($RT::SystemUser);
828 # $tix6->FromSQL('Requestor.EmailAddress LIKE "rqtest1" AND Requestor.EmailAddress LIKE "rqtest2"');
830 # is ($tix6->Count, 1);
844 my $meta = $FIELD_METADATA{ $field };
845 my $type = $meta->[1] || '';
846 my $class = $meta->[2] || 'Ticket';
848 # Owner was ENUM field, so "Owner = 'xxx'" allowed user to
849 # search by id and Name at the same time, this is workaround
850 # to preserve backward compatibility
851 if ( $field eq 'Owner' && !$rest{SUBKEY} && $op =~ /^!?=$/ ) {
852 my $o = RT::User->new( $self->CurrentUser );
862 $rest{SUBKEY} ||= 'EmailAddress';
864 my $groups = $self->_RoleGroupsJoin( Type => $type, Class => $class );
867 if ( $op =~ /^IS(?: NOT)?$/ ) {
868 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
869 # to avoid joining the table Users into the query, we just join GM
870 # and make sure we don't match records where group is member of itself
872 LEFTJOIN => $group_members,
875 VALUE => "$group_members.MemberId",
879 ALIAS => $group_members,
886 elsif ( $op =~ /^!=$|^NOT\s+/i ) {
888 $op =~ s/!|NOT\s+//i;
890 # XXX: we have no way to build correct "Watcher.X != 'Y'" when condition
891 # "X = 'Y'" matches more then one user so we try to fetch two records and
892 # do the right thing when there is only one exist and semi-working solution
894 my $users_obj = RT::Users->new( $self->CurrentUser );
896 FIELD => $rest{SUBKEY},
901 $users_obj->RowsPerPage(2);
902 my @users = @{ $users_obj->ItemsArrayRef };
904 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
907 $uid = $users[0]->id if @users;
909 LEFTJOIN => $group_members,
910 ALIAS => $group_members,
916 ALIAS => $group_members,
923 LEFTJOIN => $group_members,
926 VALUE => "$group_members.MemberId",
929 my $users = $self->Join(
931 ALIAS1 => $group_members,
932 FIELD1 => 'MemberId',
939 FIELD => $rest{SUBKEY},
953 my $group_members = $self->_GroupMembersJoin(
954 GroupsAlias => $groups,
958 my $users = $self->{'_sql_u_watchers_aliases'}{$group_members};
960 $users = $self->{'_sql_u_watchers_aliases'}{$group_members} =
961 $self->NewAlias('Users');
963 LEFTJOIN => $group_members,
964 ALIAS => $group_members,
966 VALUE => "$users.id",
971 # we join users table without adding some join condition between tables,
972 # the only conditions we have are conditions on the table iteslf,
973 # for example Users.EmailAddress = 'x'. We should add this condition to
974 # the top level of the query and bundle it with another similar conditions,
975 # for example "Users.EmailAddress = 'x' OR Users.EmailAddress = 'Y'".
976 # To achive this goal we use own SUBCLAUSE for conditions on the users table.
979 SUBCLAUSE => '_sql_u_watchers_'. $users,
981 FIELD => $rest{'SUBKEY'},
986 # A condition which ties Users and Groups (role groups) is a left join condition
987 # of CachedGroupMembers table. To get correct results of the query we check
988 # if there are matches in CGM table or not using 'cgm.id IS NOT NULL'.
991 ALIAS => $group_members,
993 OPERATOR => 'IS NOT',
1000 sub _RoleGroupsJoin {
1002 my %args = (New => 0, Class => 'Ticket', Type => '', @_);
1003 return $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1004 if $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} }
1007 # XXX: this has been fixed in DBIx::SB-1.48
1008 # XXX: if we change this from Join to NewAlias+Limit
1009 # then Pg and mysql 5.x will complain because SB build wrong query.
1010 # Query looks like "FROM (Tickets LEFT JOIN CGM ON(Groups.id = CGM.GroupId)), Groups"
1011 # Pg doesn't like that fact that it doesn't know about Groups table yet when
1012 # join CGM table into Tickets. Problem is in Join method which doesn't use
1013 # ALIAS1 argument when build braces.
1015 # we always have watcher groups for ticket, so we use INNER join
1016 my $groups = $self->Join(
1018 FIELD1 => $args{'Class'} eq 'Queue'? 'Queue': 'id',
1020 FIELD2 => 'Instance',
1021 ENTRYAGGREGATOR => 'AND',
1023 $self->SUPER::Limit(
1024 LEFTJOIN => $groups,
1027 VALUE => 'RT::'. $args{'Class'} .'-Role',
1029 $self->SUPER::Limit(
1030 LEFTJOIN => $groups,
1033 VALUE => $args{'Type'},
1036 $self->{'_sql_role_group_aliases'}{ $args{'Class'} .'-'. $args{'Type'} } = $groups
1037 unless $args{'New'};
1042 sub _GroupMembersJoin {
1044 my %args = (New => 1, GroupsAlias => undef, @_);
1046 return $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1047 if $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1050 my $alias = $self->Join(
1052 ALIAS1 => $args{'GroupsAlias'},
1054 TABLE2 => 'CachedGroupMembers',
1055 FIELD2 => 'GroupId',
1056 ENTRYAGGREGATOR => 'AND',
1059 $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} } = $alias
1060 unless $args{'New'};
1067 Helper function which provides joins to a watchers table both for limits
1074 my $type = shift || '';
1077 my $groups = $self->_RoleGroupsJoin( Type => $type );
1078 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
1079 # XXX: work around, we must hide groups that
1080 # are members of the role group we search in,
1081 # otherwise them result in wrong NULLs in Users
1082 # table and break ordering. Now, we know that
1083 # RT doesn't allow to add groups as members of the
1084 # ticket roles, so we just hide entries in CGM table
1085 # with MemberId == GroupId from results
1086 $self->SUPER::Limit(
1087 LEFTJOIN => $group_members,
1090 VALUE => "$group_members.MemberId",
1093 my $users = $self->Join(
1095 ALIAS1 => $group_members,
1096 FIELD1 => 'MemberId',
1100 return ($groups, $group_members, $users);
1103 =head2 _WatcherMembershipLimit
1105 Handle watcher membership limits, i.e. whether the watcher belongs to a
1106 specific group or not.
1109 1: Field to query on
1111 SELECT DISTINCT main.*
1115 CachedGroupMembers CachedGroupMembers_2,
1118 (main.EffectiveId = main.id)
1120 (main.Status != 'deleted')
1122 (main.Type = 'ticket')
1125 (Users_3.EmailAddress = '22')
1127 (Groups_1.Domain = 'RT::Ticket-Role')
1129 (Groups_1.Type = 'RequestorGroup')
1132 Groups_1.Instance = main.id
1134 Groups_1.id = CachedGroupMembers_2.GroupId
1136 CachedGroupMembers_2.MemberId = Users_3.id
1137 ORDER BY main.id ASC
1142 sub _WatcherMembershipLimit {
1143 my ( $self, $field, $op, $value, @rest ) = @_;
1148 my $groups = $self->NewAlias('Groups');
1149 my $groupmembers = $self->NewAlias('CachedGroupMembers');
1150 my $users = $self->NewAlias('Users');
1151 my $memberships = $self->NewAlias('CachedGroupMembers');
1153 if ( ref $field ) { # gross hack
1154 my @bundle = @$field;
1156 for my $chunk (@bundle) {
1157 ( $field, $op, $value, @rest ) = @$chunk;
1159 ALIAS => $memberships,
1170 ALIAS => $memberships,
1178 # {{{ Tie to groups for tickets we care about
1182 VALUE => 'RT::Ticket-Role',
1183 ENTRYAGGREGATOR => 'AND'
1188 FIELD1 => 'Instance',
1195 # If we care about which sort of watcher
1196 my $meta = $FIELD_METADATA{$field};
1197 my $type = ( defined $meta->[1] ? $meta->[1] : undef );
1204 ENTRYAGGREGATOR => 'AND'
1211 ALIAS2 => $groupmembers,
1216 ALIAS1 => $groupmembers,
1217 FIELD1 => 'MemberId',
1223 ALIAS1 => $memberships,
1224 FIELD1 => 'MemberId',
1233 sub _LinkFieldLimit {
1238 if ( $restriction->{'TYPE'} ) {
1239 $self->SUPER::Limit(
1240 ALIAS => $LinkAlias,
1241 ENTRYAGGREGATOR => 'AND',
1244 VALUE => $restriction->{'TYPE'}
1248 #If we're trying to limit it to things that are target of
1249 if ( $restriction->{'TARGET'} ) {
1251 # If the TARGET is an integer that means that we want to look at
1252 # the LocalTarget field. otherwise, we want to look at the
1255 if ( $restriction->{'TARGET'} =~ /^(\d+)$/ ) {
1256 $matchfield = "LocalTarget";
1259 $matchfield = "Target";
1261 $self->SUPER::Limit(
1262 ALIAS => $LinkAlias,
1263 ENTRYAGGREGATOR => 'AND',
1264 FIELD => $matchfield,
1266 VALUE => $restriction->{'TARGET'}
1269 #If we're searching on target, join the base to ticket.id
1272 FIELD1 => $self->{'primary_key'},
1273 ALIAS2 => $LinkAlias,
1274 FIELD2 => 'LocalBase'
1278 #If we're trying to limit it to things that are base of
1279 elsif ( $restriction->{'BASE'} ) {
1281 # If we're trying to match a numeric link, we want to look at
1282 # LocalBase, otherwise we want to look at "Base"
1284 if ( $restriction->{'BASE'} =~ /^(\d+)$/ ) {
1285 $matchfield = "LocalBase";
1288 $matchfield = "Base";
1291 $self->SUPER::Limit(
1292 ALIAS => $LinkAlias,
1293 ENTRYAGGREGATOR => 'AND',
1294 FIELD => $matchfield,
1296 VALUE => $restriction->{'BASE'}
1299 #If we're searching on base, join the target to ticket.id
1302 FIELD1 => $self->{'primary_key'},
1303 ALIAS2 => $LinkAlias,
1304 FIELD2 => 'LocalTarget'
1310 =head2 _CustomFieldDecipher
1312 Try and turn a CF descriptor into (cfid, cfname) object pair.
1316 sub _CustomFieldDecipher {
1317 my ($self, $field) = @_;
1320 if ( $field =~ /^(.+?)\.{(.+)}$/ ) {
1321 ($queue, $field) = ($1, $2);
1323 $field = $1 if $field =~ /^{(.+)}$/; # trim { }
1327 my $q = RT::Queue->new( $self->CurrentUser );
1332 # $queue = $q->Name; # should we normalize the queue?
1333 $cf = $q->CustomField( $field );
1336 $cf = RT::CustomField->new( $self->CurrentUser );
1337 $cf->LoadByNameAndQueue( Queue => 0, Name => $field );
1339 return ($queue, $field, $cf->id, $cf)
1341 return ($queue, $field);
1344 my $cfs = RT::CustomFields->new( $self->CurrentUser );
1345 $cfs->Limit( FIELD => 'Name', VALUE => $field );
1346 $cfs->LimitToLookupType('RT::Queue-RT::Ticket');
1347 my $count = $cfs->Count;
1348 return (undef, $field, undef) if $count > 1;
1349 return (undef, $field, 0) if $count == 0;
1350 my $cf = $cfs->First;
1351 return (undef, $field, $cf->id, $cf) if $cf && $cf->id;
1352 return (undef, $field, undef);
1355 =head2 _CustomFieldJoin
1357 Factor out the Join of custom fields so we can use it for sorting too
1361 sub _CustomFieldJoin {
1362 my ($self, $cfkey, $cfid, $field) = @_;
1363 # Perform one Join per CustomField
1364 if ( $self->{_sql_object_cfv_alias}{$cfkey} ||
1365 $self->{_sql_cf_alias}{$cfkey} )
1367 return ( $self->{_sql_object_cfv_alias}{$cfkey},
1368 $self->{_sql_cf_alias}{$cfkey} );
1371 my ($TicketCFs, $CFs);
1373 $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1377 TABLE2 => 'ObjectCustomFieldValues',
1378 FIELD2 => 'ObjectId',
1380 $self->SUPER::Limit(
1381 LEFTJOIN => $TicketCFs,
1382 FIELD => 'CustomField',
1384 ENTRYAGGREGATOR => 'AND'
1388 my $ocfalias = $self->Join(
1391 TABLE2 => 'ObjectCustomFields',
1392 FIELD2 => 'ObjectId',
1395 $self->SUPER::Limit(
1396 LEFTJOIN => $ocfalias,
1397 ENTRYAGGREGATOR => 'OR',
1398 FIELD => 'ObjectId',
1402 $CFs = $self->{_sql_cf_alias}{$cfkey} = $self->Join(
1404 ALIAS1 => $ocfalias,
1405 FIELD1 => 'CustomField',
1406 TABLE2 => 'CustomFields',
1409 $self->SUPER::Limit(
1411 ENTRYAGGREGATOR => 'AND',
1412 FIELD => 'LookupType',
1413 VALUE => 'RT::Queue-RT::Ticket',
1415 $self->SUPER::Limit(
1417 ENTRYAGGREGATOR => 'AND',
1422 $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1426 TABLE2 => 'ObjectCustomFieldValues',
1427 FIELD2 => 'CustomField',
1429 $self->SUPER::Limit(
1430 LEFTJOIN => $TicketCFs,
1431 FIELD => 'ObjectId',
1434 ENTRYAGGREGATOR => 'AND',
1437 $self->SUPER::Limit(
1438 LEFTJOIN => $TicketCFs,
1439 FIELD => 'ObjectType',
1440 VALUE => 'RT::Ticket',
1441 ENTRYAGGREGATOR => 'AND'
1443 $self->SUPER::Limit(
1444 LEFTJOIN => $TicketCFs,
1445 FIELD => 'Disabled',
1448 ENTRYAGGREGATOR => 'AND'
1451 return ($TicketCFs, $CFs);
1454 =head2 _CustomFieldLimit
1456 Limit based on CustomFields
1463 sub _CustomFieldLimit {
1464 my ( $self, $_field, $op, $value, @rest ) = @_;
1467 my $field = $rest{SUBKEY} || die "No field specified";
1469 # For our sanity, we can only limit on one queue at a time
1472 ($queue, $field, $cfid ) = $self->_CustomFieldDecipher( $field );
1474 # If we're trying to find custom fields that don't match something, we
1475 # want tickets where the custom field has no value at all. Note that
1476 # we explicitly don't include the "IS NULL" case, since we would
1477 # otherwise end up with a redundant clause.
1479 my $null_columns_ok;
1480 if ( ( $op =~ /^NOT LIKE$/i ) or ( $op eq '!=' ) ) {
1481 $null_columns_ok = 1;
1484 my $cfkey = $cfid ? $cfid : "$queue.$field";
1485 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1491 ALIAS => $TicketCFs,
1498 # XXX: if we join via CustomFields table then
1499 # because of order of left joins we get NULLs in
1500 # CF table and then get nulls for those records
1501 # in OCFVs table what result in wrong results
1502 # as decifer method now tries to load a CF then
1503 # we fall into this situation only when there
1504 # are more than one CF with the name in the DB.
1505 # the same thing applies to order by call.
1506 # TODO: reorder joins T <- OCFVs <- CFs <- OCFs if
1507 # we want treat IS NULL as (not applies or has
1512 OPERATOR => 'IS NOT',
1515 ENTRYAGGREGATOR => 'AND',
1519 if ($null_columns_ok) {
1521 ALIAS => $TicketCFs,
1526 ENTRYAGGREGATOR => 'OR',
1534 sub _HasAttributeLimit {
1535 my ( $self, $field, $op, $value, %rest ) = @_;
1537 my $alias = $self->Join(
1541 TABLE2 => 'Attributes',
1542 FIELD2 => 'ObjectId',
1544 $self->SUPER::Limit(
1546 FIELD => 'ObjectType',
1547 VALUE => 'RT::Ticket',
1548 ENTRYAGGREGATOR => 'AND'
1550 $self->SUPER::Limit(
1555 ENTRYAGGREGATOR => 'AND'
1561 OPERATOR => $FIELD_METADATA{$field}->[1]? 'IS NOT': 'IS',
1568 # End Helper Functions
1570 # End of SQL Stuff -------------------------------------------------
1572 # {{{ Allow sorting on watchers
1574 =head2 OrderByCols ARRAY
1576 A modified version of the OrderBy method which automatically joins where
1577 C<ALIAS> is set to the name of a watcher type.
1588 foreach my $row (@args) {
1589 if ( $row->{ALIAS} ) {
1593 if ( $row->{FIELD} !~ /\./ ) {
1594 my $meta = $self->FIELDS->{ $row->{FIELD} };
1600 if ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'Queue' ) {
1601 my $alias = $self->Join(
1604 FIELD1 => $row->{'FIELD'},
1608 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1609 } elsif ( ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'User' )
1610 || ( $meta->[0] eq 'WATCHERFIELD' && ($meta->[1]||'') eq 'Owner' )
1612 my $alias = $self->Join(
1615 FIELD1 => $row->{'FIELD'},
1619 push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
1626 my ( $field, $subkey ) = split /\./, $row->{FIELD}, 2;
1627 my $meta = $self->FIELDS->{$field};
1628 if ( $meta->[0] eq 'WATCHERFIELD' ) {
1629 # cache alias as we want to use one alias per watcher type for sorting
1630 my $users = $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] };
1632 $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] }
1633 = $users = ( $self->_WatcherJoin( $meta->[1] ) )[2];
1635 push @res, { %$row, ALIAS => $users, FIELD => $subkey };
1636 } elsif ( $meta->[0] eq 'CUSTOMFIELD' ) {
1637 my ($queue, $field, $cfid, $cf_obj) = $self->_CustomFieldDecipher( $subkey );
1638 my $cfkey = $cfid ? $cfid : "$queue.$field";
1639 $cfkey .= ".ordering" if !$cf_obj || ($cf_obj->MaxValues||0) != 1;
1640 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1641 # this is described in _CustomFieldLimit
1645 OPERATOR => 'IS NOT',
1648 ENTRYAGGREGATOR => 'AND',
1651 # For those cases where we are doing a join against the
1652 # CF name, and don't have a CFid, use Unique to make sure
1653 # we don't show duplicate tickets. NOTE: I'm pretty sure
1654 # this will stay mixed in for the life of the
1655 # class/package, and not just for the life of the object.
1656 # Potential performance issue.
1657 require DBIx::SearchBuilder::Unique;
1658 DBIx::SearchBuilder::Unique->import;
1660 my $CFvs = $self->Join(
1662 ALIAS1 => $TicketCFs,
1663 FIELD1 => 'CustomField',
1664 TABLE2 => 'CustomFieldValues',
1665 FIELD2 => 'CustomField',
1667 $self->SUPER::Limit(
1671 VALUE => $TicketCFs . ".Content",
1672 ENTRYAGGREGATOR => 'AND'
1675 push @res, { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' };
1676 push @res, { %$row, ALIAS => $TicketCFs, FIELD => 'Content' };
1677 } elsif ( $field eq "Custom" && $subkey eq "Ownership") {
1678 # PAW logic is "reversed"
1680 if (exists $row->{ORDER} ) {
1681 my $o = $row->{ORDER};
1682 delete $row->{ORDER};
1683 $order = "DESC" if $o =~ /asc/i;
1689 # Ticket.Owner 1 0 0
1690 my $ownerId = $self->CurrentUser->Id;
1691 push @res, { %$row, FIELD => "Owner=$ownerId", ORDER => $order } ;
1693 # Unowned Tickets 0 1 0
1694 my $nobodyId = $RT::Nobody->Id;
1695 push @res, { %$row, FIELD => "Owner=$nobodyId", ORDER => $order } ;
1697 push @res, { %$row, FIELD => "Priority", ORDER => $order } ;
1703 return $self->SUPER::OrderByCols(@res);
1708 # {{{ Limit the result set based on content
1714 Takes a paramhash with the fields FIELD, OPERATOR, VALUE and DESCRIPTION
1715 Generally best called from LimitFoo methods
1725 DESCRIPTION => undef,
1728 $args{'DESCRIPTION'} = $self->loc(
1729 "[_1] [_2] [_3]", $args{'FIELD'},
1730 $args{'OPERATOR'}, $args{'VALUE'}
1732 if ( !defined $args{'DESCRIPTION'} );
1734 my $index = $self->_NextIndex;
1736 # make the TicketRestrictions hash the equivalent of whatever we just passed in;
1738 %{ $self->{'TicketRestrictions'}{$index} } = %args;
1740 $self->{'RecalcTicketLimits'} = 1;
1742 # If we're looking at the effective id, we don't want to append the other clause
1743 # which limits us to tickets where id = effective id
1744 if ( $args{'FIELD'} eq 'EffectiveId'
1745 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
1747 $self->{'looking_at_effective_id'} = 1;
1750 if ( $args{'FIELD'} eq 'Type'
1751 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
1753 $self->{'looking_at_type'} = 1;
1763 Returns a frozen string suitable for handing back to ThawLimits.
1767 sub _FreezeThawKeys {
1768 'TicketRestrictions', 'restriction_index', 'looking_at_effective_id',
1772 # {{{ sub FreezeLimits
1777 require MIME::Base64;
1778 MIME::Base64::base64_encode(
1779 Storable::freeze( \@{$self}{ $self->_FreezeThawKeys } ) );
1786 Take a frozen Limits string generated by FreezeLimits and make this tickets
1787 object have that set of limits.
1791 # {{{ sub ThawLimits
1797 #if we don't have $in, get outta here.
1798 return undef unless ($in);
1800 $self->{'RecalcTicketLimits'} = 1;
1803 require MIME::Base64;
1805 #We don't need to die if the thaw fails.
1806 @{$self}{ $self->_FreezeThawKeys }
1807 = eval { @{ Storable::thaw( MIME::Base64::base64_decode($in) ) }; };
1809 $RT::Logger->error($@) if $@;
1815 # {{{ Limit by enum or foreign key
1817 # {{{ sub LimitQueue
1821 LimitQueue takes a paramhash with the fields OPERATOR and VALUE.
1822 OPERATOR is one of = or !=. (It defaults to =).
1823 VALUE is a queue id or Name.
1836 #TODO VALUE should also take queue objects
1837 if ( defined $args{'VALUE'} && $args{'VALUE'} !~ /^\d+$/ ) {
1838 my $queue = new RT::Queue( $self->CurrentUser );
1839 $queue->Load( $args{'VALUE'} );
1840 $args{'VALUE'} = $queue->Id;
1843 # What if they pass in an Id? Check for isNum() and convert to
1846 #TODO check for a valid queue here
1850 VALUE => $args{'VALUE'},
1851 OPERATOR => $args{'OPERATOR'},
1852 DESCRIPTION => join(
1853 ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{'VALUE'},
1861 # {{{ sub LimitStatus
1865 Takes a paramhash with the fields OPERATOR and VALUE.
1866 OPERATOR is one of = or !=.
1869 RT adds Status != 'deleted' until object has
1870 allow_deleted_search internal property set.
1871 $tickets->{'allow_deleted_search'} = 1;
1872 $tickets->LimitStatus( VALUE => 'deleted' );
1884 VALUE => $args{'VALUE'},
1885 OPERATOR => $args{'OPERATOR'},
1886 DESCRIPTION => join( ' ',
1887 $self->loc('Status'), $args{'OPERATOR'},
1888 $self->loc( $args{'VALUE'} ) ),
1894 # {{{ sub IgnoreType
1898 If called, this search will not automatically limit the set of results found
1899 to tickets of type "Ticket". Tickets of other types, such as "project" and
1900 "approval" will be found.
1907 # Instead of faking a Limit that later gets ignored, fake up the
1908 # fact that we're already looking at type, so that the check in
1909 # Tickets_Overlay_SQL/FromSQL goes down the right branch
1911 # $self->LimitType(VALUE => '__any');
1912 $self->{looking_at_type} = 1;
1921 Takes a paramhash with the fields OPERATOR and VALUE.
1922 OPERATOR is one of = or !=, it defaults to "=".
1923 VALUE is a string to search for in the type of the ticket.
1938 VALUE => $args{'VALUE'},
1939 OPERATOR => $args{'OPERATOR'},
1940 DESCRIPTION => join( ' ',
1941 $self->loc('Type'), $args{'OPERATOR'}, $args{'Limit'}, ),
1949 # {{{ Limit by string field
1951 # {{{ sub LimitSubject
1955 Takes a paramhash with the fields OPERATOR and VALUE.
1956 OPERATOR is one of = or !=.
1957 VALUE is a string to search for in the subject of the ticket.
1966 VALUE => $args{'VALUE'},
1967 OPERATOR => $args{'OPERATOR'},
1968 DESCRIPTION => join( ' ',
1969 $self->loc('Subject'), $args{'OPERATOR'}, $args{'VALUE'}, ),
1977 # {{{ Limit based on ticket numerical attributes
1978 # Things that can be > < = !=
1984 Takes a paramhash with the fields OPERATOR and VALUE.
1985 OPERATOR is one of =, >, < or !=.
1986 VALUE is a ticket Id to search for
1999 VALUE => $args{'VALUE'},
2000 OPERATOR => $args{'OPERATOR'},
2002 join( ' ', $self->loc('Id'), $args{'OPERATOR'}, $args{'VALUE'}, ),
2008 # {{{ sub LimitPriority
2010 =head2 LimitPriority
2012 Takes a paramhash with the fields OPERATOR and VALUE.
2013 OPERATOR is one of =, >, < or !=.
2014 VALUE is a value to match the ticket\'s priority against
2022 FIELD => 'Priority',
2023 VALUE => $args{'VALUE'},
2024 OPERATOR => $args{'OPERATOR'},
2025 DESCRIPTION => join( ' ',
2026 $self->loc('Priority'),
2027 $args{'OPERATOR'}, $args{'VALUE'}, ),
2033 # {{{ sub LimitInitialPriority
2035 =head2 LimitInitialPriority
2037 Takes a paramhash with the fields OPERATOR and VALUE.
2038 OPERATOR is one of =, >, < or !=.
2039 VALUE is a value to match the ticket\'s initial priority against
2044 sub LimitInitialPriority {
2048 FIELD => 'InitialPriority',
2049 VALUE => $args{'VALUE'},
2050 OPERATOR => $args{'OPERATOR'},
2051 DESCRIPTION => join( ' ',
2052 $self->loc('Initial Priority'), $args{'OPERATOR'},
2059 # {{{ sub LimitFinalPriority
2061 =head2 LimitFinalPriority
2063 Takes a paramhash with the fields OPERATOR and VALUE.
2064 OPERATOR is one of =, >, < or !=.
2065 VALUE is a value to match the ticket\'s final priority against
2069 sub LimitFinalPriority {
2073 FIELD => 'FinalPriority',
2074 VALUE => $args{'VALUE'},
2075 OPERATOR => $args{'OPERATOR'},
2076 DESCRIPTION => join( ' ',
2077 $self->loc('Final Priority'), $args{'OPERATOR'},
2084 # {{{ sub LimitTimeWorked
2086 =head2 LimitTimeWorked
2088 Takes a paramhash with the fields OPERATOR and VALUE.
2089 OPERATOR is one of =, >, < or !=.
2090 VALUE is a value to match the ticket's TimeWorked attribute
2094 sub LimitTimeWorked {
2098 FIELD => 'TimeWorked',
2099 VALUE => $args{'VALUE'},
2100 OPERATOR => $args{'OPERATOR'},
2101 DESCRIPTION => join( ' ',
2102 $self->loc('Time Worked'),
2103 $args{'OPERATOR'}, $args{'VALUE'}, ),
2109 # {{{ sub LimitTimeLeft
2111 =head2 LimitTimeLeft
2113 Takes a paramhash with the fields OPERATOR and VALUE.
2114 OPERATOR is one of =, >, < or !=.
2115 VALUE is a value to match the ticket's TimeLeft attribute
2123 FIELD => 'TimeLeft',
2124 VALUE => $args{'VALUE'},
2125 OPERATOR => $args{'OPERATOR'},
2126 DESCRIPTION => join( ' ',
2127 $self->loc('Time Left'),
2128 $args{'OPERATOR'}, $args{'VALUE'}, ),
2136 # {{{ Limiting based on attachment attributes
2138 # {{{ sub LimitContent
2142 Takes a paramhash with the fields OPERATOR and VALUE.
2143 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2144 VALUE is a string to search for in the body of the ticket
2153 VALUE => $args{'VALUE'},
2154 OPERATOR => $args{'OPERATOR'},
2155 DESCRIPTION => join( ' ',
2156 $self->loc('Ticket content'), $args{'OPERATOR'},
2163 # {{{ sub LimitFilename
2165 =head2 LimitFilename
2167 Takes a paramhash with the fields OPERATOR and VALUE.
2168 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2169 VALUE is a string to search for in the body of the ticket
2177 FIELD => 'Filename',
2178 VALUE => $args{'VALUE'},
2179 OPERATOR => $args{'OPERATOR'},
2180 DESCRIPTION => join( ' ',
2181 $self->loc('Attachment filename'), $args{'OPERATOR'},
2187 # {{{ sub LimitContentType
2189 =head2 LimitContentType
2191 Takes a paramhash with the fields OPERATOR and VALUE.
2192 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2193 VALUE is a content type to search ticket attachments for
2197 sub LimitContentType {
2201 FIELD => 'ContentType',
2202 VALUE => $args{'VALUE'},
2203 OPERATOR => $args{'OPERATOR'},
2204 DESCRIPTION => join( ' ',
2205 $self->loc('Ticket content type'), $args{'OPERATOR'},
2214 # {{{ Limiting based on people
2216 # {{{ sub LimitOwner
2220 Takes a paramhash with the fields OPERATOR and VALUE.
2221 OPERATOR is one of = or !=.
2233 my $owner = new RT::User( $self->CurrentUser );
2234 $owner->Load( $args{'VALUE'} );
2236 # FIXME: check for a valid $owner
2239 VALUE => $args{'VALUE'},
2240 OPERATOR => $args{'OPERATOR'},
2241 DESCRIPTION => join( ' ',
2242 $self->loc('Owner'), $args{'OPERATOR'}, $owner->Name(), ),
2249 # {{{ Limiting watchers
2251 # {{{ sub LimitWatcher
2255 Takes a paramhash with the fields OPERATOR, TYPE and VALUE.
2256 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2257 VALUE is a value to match the ticket\'s watcher email addresses against
2258 TYPE is the sort of watchers you want to match against. Leave it undef if you want to search all of them
2262 my $t1 = RT::Ticket->new($RT::SystemUser);
2263 $t1->Create(Queue => 'general', Subject => "LimitWatchers test", Requestors => \['requestor1@example.com']);
2278 #build us up a description
2279 my ( $watcher_type, $desc );
2280 if ( $args{'TYPE'} ) {
2281 $watcher_type = $args{'TYPE'};
2284 $watcher_type = "Watcher";
2288 FIELD => $watcher_type,
2289 VALUE => $args{'VALUE'},
2290 OPERATOR => $args{'OPERATOR'},
2291 TYPE => $args{'TYPE'},
2292 DESCRIPTION => join( ' ',
2293 $self->loc($watcher_type),
2294 $args{'OPERATOR'}, $args{'VALUE'}, ),
2298 sub LimitRequestor {
2301 $RT::Logger->error( "Tickets->LimitRequestor is deprecated at ("
2302 . join( ":", caller )
2304 $self->LimitWatcher( TYPE => 'Requestor', @_ );
2314 # {{{ Limiting based on links
2318 =head2 LimitLinkedTo
2320 LimitLinkedTo takes a paramhash with two fields: TYPE and TARGET
2321 TYPE limits the sort of link we want to search on
2323 TYPE = { RefersTo, MemberOf, DependsOn }
2325 TARGET is the id or URI of the TARGET of the link
2326 (TARGET used to be 'TICKET'. 'TICKET' is deprecated, but will be treated as TARGET
2341 FIELD => 'LinkedTo',
2343 TARGET => ( $args{'TARGET'} || $args{'TICKET'} ),
2344 TYPE => $args{'TYPE'},
2345 DESCRIPTION => $self->loc(
2346 "Tickets [_1] by [_2]",
2347 $self->loc( $args{'TYPE'} ),
2348 ( $args{'TARGET'} || $args{'TICKET'} )
2350 OPERATOR => $args{'OPERATOR'},
2356 # {{{ LimitLinkedFrom
2358 =head2 LimitLinkedFrom
2360 LimitLinkedFrom takes a paramhash with two fields: TYPE and BASE
2361 TYPE limits the sort of link we want to search on
2364 BASE is the id or URI of the BASE of the link
2365 (BASE used to be 'TICKET'. 'TICKET' is deprecated, but will be treated as BASE
2370 sub LimitLinkedFrom {
2380 # translate RT2 From/To naming to RT3 TicketSQL naming
2381 my %fromToMap = qw(DependsOn DependentOn
2383 RefersTo ReferredToBy);
2385 my $type = $args{'TYPE'};
2386 $type = $fromToMap{$type} if exists( $fromToMap{$type} );
2389 FIELD => 'LinkedTo',
2391 BASE => ( $args{'BASE'} || $args{'TICKET'} ),
2393 DESCRIPTION => $self->loc(
2394 "Tickets [_1] [_2]",
2395 $self->loc( $args{'TYPE'} ),
2396 ( $args{'BASE'} || $args{'TICKET'} )
2398 OPERATOR => $args{'OPERATOR'},
2407 my $ticket_id = shift;
2408 return $self->LimitLinkedTo(
2410 TARGET => $ticket_id,
2417 # {{{ LimitHasMember
2418 sub LimitHasMember {
2420 my $ticket_id = shift;
2421 return $self->LimitLinkedFrom(
2423 BASE => "$ticket_id",
2424 TYPE => 'HasMember',
2431 # {{{ LimitDependsOn
2433 sub LimitDependsOn {
2435 my $ticket_id = shift;
2436 return $self->LimitLinkedTo(
2438 TARGET => $ticket_id,
2439 TYPE => 'DependsOn',
2446 # {{{ LimitDependedOnBy
2448 sub LimitDependedOnBy {
2450 my $ticket_id = shift;
2451 return $self->LimitLinkedFrom(
2454 TYPE => 'DependentOn',
2465 my $ticket_id = shift;
2466 return $self->LimitLinkedTo(
2468 TARGET => $ticket_id,
2476 # {{{ LimitReferredToBy
2478 sub LimitReferredToBy {
2480 my $ticket_id = shift;
2481 return $self->LimitLinkedFrom(
2484 TYPE => 'ReferredToBy',
2492 # {{{ limit based on ticket date attribtes
2496 =head2 LimitDate (FIELD => 'DateField', OPERATOR => $oper, VALUE => $ISODate)
2498 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2500 OPERATOR is one of > or <
2501 VALUE is a date and time in ISO format in GMT
2502 FIELD is one of Starts, Started, Told, Created, Resolved, LastUpdated
2504 There are also helper functions of the form LimitFIELD that eliminate
2505 the need to pass in a FIELD argument.
2519 #Set the description if we didn't get handed it above
2520 unless ( $args{'DESCRIPTION'} ) {
2521 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2522 . $args{'OPERATOR'} . " "
2523 . $args{'VALUE'} . " GMT";
2526 $self->Limit(%args);
2534 $self->LimitDate( FIELD => 'Created', @_ );
2539 $self->LimitDate( FIELD => 'Due', @_ );
2545 $self->LimitDate( FIELD => 'Starts', @_ );
2551 $self->LimitDate( FIELD => 'Started', @_ );
2556 $self->LimitDate( FIELD => 'Resolved', @_ );
2561 $self->LimitDate( FIELD => 'Told', @_ );
2564 sub LimitLastUpdated {
2566 $self->LimitDate( FIELD => 'LastUpdated', @_ );
2570 # {{{ sub LimitTransactionDate
2572 =head2 LimitTransactionDate (OPERATOR => $oper, VALUE => $ISODate)
2574 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2576 OPERATOR is one of > or <
2577 VALUE is a date and time in ISO format in GMT
2582 sub LimitTransactionDate {
2585 FIELD => 'TransactionDate',
2592 # <20021217042756.GK28744@pallas.fsck.com>
2593 # "Kill It" - Jesse.
2595 #Set the description if we didn't get handed it above
2596 unless ( $args{'DESCRIPTION'} ) {
2597 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2598 . $args{'OPERATOR'} . " "
2599 . $args{'VALUE'} . " GMT";
2602 $self->Limit(%args);
2610 # {{{ Limit based on custom fields
2611 # {{{ sub LimitCustomField
2613 =head2 LimitCustomField
2615 Takes a paramhash of key/value pairs with the following keys:
2619 =item CUSTOMFIELD - CustomField name or id. If a name is passed, an additional parameter QUEUE may also be passed to distinguish the custom field.
2621 =item OPERATOR - The usual Limit operators
2623 =item VALUE - The value to compare against
2629 sub LimitCustomField {
2633 CUSTOMFIELD => undef,
2635 DESCRIPTION => undef,
2636 FIELD => 'CustomFieldValue',
2641 my $CF = RT::CustomField->new( $self->CurrentUser );
2642 if ( $args{CUSTOMFIELD} =~ /^\d+$/ ) {
2643 $CF->Load( $args{CUSTOMFIELD} );
2646 $CF->LoadByNameAndQueue(
2647 Name => $args{CUSTOMFIELD},
2648 Queue => $args{QUEUE}
2650 $args{CUSTOMFIELD} = $CF->Id;
2653 #If we are looking to compare with a null value.
2654 if ( $args{'OPERATOR'} =~ /^is$/i ) {
2655 $args{'DESCRIPTION'}
2656 ||= $self->loc( "Custom field [_1] has no value.", $CF->Name );
2658 elsif ( $args{'OPERATOR'} =~ /^is not$/i ) {
2659 $args{'DESCRIPTION'}
2660 ||= $self->loc( "Custom field [_1] has a value.", $CF->Name );
2663 # if we're not looking to compare with a null value
2665 $args{'DESCRIPTION'} ||= $self->loc( "Custom field [_1] [_2] [_3]",
2666 $CF->Name, $args{OPERATOR}, $args{VALUE} );
2671 my $qo = new RT::Queue( $self->CurrentUser );
2672 $qo->Load( $CF->Queue );
2677 @rest = ( ENTRYAGGREGATOR => 'AND' )
2678 if ( $CF->Type eq 'SelectMultiple' );
2681 VALUE => $args{VALUE},
2685 ? $q . ".{" . $CF->Name . "}"
2688 OPERATOR => $args{OPERATOR},
2693 $self->{'RecalcTicketLimits'} = 1;
2699 # {{{ sub _NextIndex
2703 Keep track of the counter for the array of restrictions
2709 return ( $self->{'restriction_index'}++ );
2716 # {{{ Core bits to make this a DBIx::SearchBuilder object
2721 $self->{'table'} = "Tickets";
2722 $self->{'RecalcTicketLimits'} = 1;
2723 $self->{'looking_at_effective_id'} = 0;
2724 $self->{'looking_at_type'} = 0;
2725 $self->{'restriction_index'} = 1;
2726 $self->{'primary_key'} = "id";
2727 delete $self->{'items_array'};
2728 delete $self->{'item_map'};
2729 delete $self->{'columns_to_display'};
2730 $self->SUPER::_Init(@_);
2741 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2742 return ( $self->SUPER::Count() );
2750 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2751 return ( $self->SUPER::CountAll() );
2756 # {{{ sub ItemsArrayRef
2758 =head2 ItemsArrayRef
2760 Returns a reference to the set of all items found in this search
2768 unless ( $self->{'items_array'} ) {
2770 my $placeholder = $self->_ItemsCounter;
2771 $self->GotoFirstItem();
2772 while ( my $item = $self->Next ) {
2773 push( @{ $self->{'items_array'} }, $item );
2775 $self->GotoItem($placeholder);
2776 $self->{'items_array'}
2777 = $self->ItemsOrderBy( $self->{'items_array'} );
2779 return ( $self->{'items_array'} );
2788 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2790 my $Ticket = $self->SUPER::Next();
2791 if ( ( defined($Ticket) ) and ( ref($Ticket) ) ) {
2793 if ( $Ticket->__Value('Status') eq 'deleted'
2794 && !$self->{'allow_deleted_search'} )
2796 return ( $self->Next() );
2799 # Since Ticket could be granted with more rights instead
2800 # of being revoked, it's ok if queue rights allow
2801 # ShowTicket. It seems need another query, but we have
2802 # rights cache in Principal::HasRight.
2803 elsif ( $Ticket->CurrentUserHasRight('ShowTicket') )
2808 #If the user doesn't have the right to show this ticket
2810 return ( $self->Next() );
2814 #if there never was any ticket
2825 # {{{ Deal with storing and restoring restrictions
2827 # {{{ sub LoadRestrictions
2829 =head2 LoadRestrictions
2831 LoadRestrictions takes a string which can fully populate the TicketRestrictons hash.
2832 TODO It is not yet implemented
2838 # {{{ sub DescribeRestrictions
2840 =head2 DescribeRestrictions
2843 Returns a hash keyed by restriction id.
2844 Each element of the hash is currently a one element hash that contains DESCRIPTION which
2845 is a description of the purpose of that TicketRestriction
2849 sub DescribeRestrictions {
2852 my ( $row, %listing );
2854 foreach $row ( keys %{ $self->{'TicketRestrictions'} } ) {
2855 $listing{$row} = $self->{'TicketRestrictions'}{$row}{'DESCRIPTION'};
2862 # {{{ sub RestrictionValues
2864 =head2 RestrictionValues FIELD
2866 Takes a restriction field and returns a list of values this field is restricted
2871 sub RestrictionValues {
2874 map $self->{'TicketRestrictions'}{$_}{'VALUE'}, grep {
2875 $self->{'TicketRestrictions'}{$_}{'FIELD'} eq $field
2876 && $self->{'TicketRestrictions'}{$_}{'OPERATOR'} eq "="
2878 keys %{ $self->{'TicketRestrictions'} };
2883 # {{{ sub ClearRestrictions
2885 =head2 ClearRestrictions
2887 Removes all restrictions irretrievably
2891 sub ClearRestrictions {
2893 delete $self->{'TicketRestrictions'};
2894 $self->{'looking_at_effective_id'} = 0;
2895 $self->{'looking_at_type'} = 0;
2896 $self->{'RecalcTicketLimits'} = 1;
2901 # {{{ sub DeleteRestriction
2903 =head2 DeleteRestriction
2905 Takes the row Id of a restriction (From DescribeRestrictions' output, for example.
2906 Removes that restriction from the session's limits.
2910 sub DeleteRestriction {
2913 delete $self->{'TicketRestrictions'}{$row};
2915 $self->{'RecalcTicketLimits'} = 1;
2917 #make the underlying easysearch object forget all its preconceptions
2922 # {{{ sub _RestrictionsToClauses
2924 # Convert a set of oldstyle SB Restrictions to Clauses for RQL
2926 sub _RestrictionsToClauses {
2931 foreach $row ( keys %{ $self->{'TicketRestrictions'} } ) {
2932 my $restriction = $self->{'TicketRestrictions'}{$row};
2935 #print Dumper($restriction),"\n";
2937 # We need to reimplement the subclause aggregation that SearchBuilder does.
2938 # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main',
2939 # Then SB AND's the different Subclauses together.
2941 # So, we want to group things into Subclauses, convert them to
2942 # SQL, and then join them with the appropriate DefaultEA.
2943 # Then join each subclause group with AND.
2945 my $field = $restriction->{'FIELD'};
2946 my $realfield = $field; # CustomFields fake up a fieldname, so
2947 # we need to figure that out
2950 # Rewrite LinkedTo meta field to the real field
2951 if ( $field =~ /LinkedTo/ ) {
2952 $realfield = $field = $restriction->{'TYPE'};
2956 # Handle subkey fields with a different real field
2957 if ( $field =~ /^(\w+)\./ ) {
2961 die "I don't know about $field yet"
2962 unless ( exists $FIELD_METADATA{$realfield}
2963 or $restriction->{CUSTOMFIELD} );
2965 my $type = $FIELD_METADATA{$realfield}->[0];
2966 my $op = $restriction->{'OPERATOR'};
2970 map { $restriction->{$_} } qw(VALUE TICKET BASE TARGET)
2973 # this performs the moral equivalent of defined or/dor/C<//>,
2974 # without the short circuiting.You need to use a 'defined or'
2975 # type thing instead of just checking for truth values, because
2976 # VALUE could be 0.(i.e. "false")
2978 # You could also use this, but I find it less aesthetic:
2979 # (although it does short circuit)
2980 #( defined $restriction->{'VALUE'}? $restriction->{VALUE} :
2981 # defined $restriction->{'TICKET'} ?
2982 # $restriction->{TICKET} :
2983 # defined $restriction->{'BASE'} ?
2984 # $restriction->{BASE} :
2985 # defined $restriction->{'TARGET'} ?
2986 # $restriction->{TARGET} )
2988 my $ea = $restriction->{ENTRYAGGREGATOR}
2989 || $DefaultEA{$type}
2992 die "Invalid operator $op for $field ($type)"
2993 unless exists $ea->{$op};
2997 # Each CustomField should be put into a different Clause so they
2998 # are ANDed together.
2999 if ( $restriction->{CUSTOMFIELD} ) {
3000 $realfield = $field;
3003 exists $clause{$realfield} or $clause{$realfield} = [];
3006 $field =~ s!(['"])!\\$1!g;
3007 $value =~ s!(['"])!\\$1!g;
3008 my $data = [ $ea, $type, $field, $op, $value ];
3010 # here is where we store extra data, say if it's a keyword or
3011 # something. (I.e. "TYPE SPECIFIC STUFF")
3013 #print Dumper($data);
3014 push @{ $clause{$realfield} }, $data;
3021 # {{{ sub _ProcessRestrictions
3023 =head2 _ProcessRestrictions PARAMHASH
3025 # The new _ProcessRestrictions is somewhat dependent on the SQL stuff,
3026 # but isn't quite generic enough to move into Tickets_Overlay_SQL.
3030 sub _ProcessRestrictions {
3033 #Blow away ticket aliases since we'll need to regenerate them for
3035 delete $self->{'TicketAliases'};
3036 delete $self->{'items_array'};
3037 delete $self->{'item_map'};
3038 delete $self->{'raw_rows'};
3039 delete $self->{'rows'};
3040 delete $self->{'count_all'};
3042 my $sql = $self->Query; # Violating the _SQL namespace
3043 if ( !$sql || $self->{'RecalcTicketLimits'} ) {
3045 # "Restrictions to Clauses Branch\n";
3046 my $clauseRef = eval { $self->_RestrictionsToClauses; };
3048 $RT::Logger->error( "RestrictionsToClauses: " . $@ );
3052 $sql = $self->ClausesToSQL($clauseRef);
3053 $self->FromSQL($sql) if $sql;
3057 $self->{'RecalcTicketLimits'} = 0;
3061 =head2 _BuildItemMap
3063 # Build up a map of first/last/next/prev items, so that we can display search nav quickly
3070 my $items = $self->ItemsArrayRef;
3073 delete $self->{'item_map'};
3074 if ( $items->[0] ) {
3075 $self->{'item_map'}->{'first'} = $items->[0]->EffectiveId;
3076 while ( my $item = shift @$items ) {
3077 my $id = $item->EffectiveId;
3078 $self->{'item_map'}->{$id}->{'defined'} = 1;
3079 $self->{'item_map'}->{$id}->{prev} = $prev;
3080 $self->{'item_map'}->{$id}->{next} = $items->[0]->EffectiveId
3084 $self->{'item_map'}->{'last'} = $prev;
3090 Returns an a map of all items found by this search. The map is of the form
3092 $ItemMap->{'first'} = first ticketid found
3093 $ItemMap->{'last'} = last ticketid found
3094 $ItemMap->{$id}->{prev} = the ticket id found before $id
3095 $ItemMap->{$id}->{next} = the ticket id found after $id
3101 $self->_BuildItemMap()
3102 unless ( $self->{'items_array'} and $self->{'item_map'} );
3103 return ( $self->{'item_map'} );
3118 =head2 PrepForSerialization
3120 You don't want to serialize a big tickets object, as the {items} hash will be instantly invalid _and_ eat lots of space
3124 sub PrepForSerialization {
3126 delete $self->{'items'};
3127 $self->RedoSearch();
3132 RT::Tickets supports several flags which alter search behavior:
3135 allow_deleted_search (Otherwise never show deleted tickets in search results)
3136 looking_at_type (otherwise limit to type=ticket)
3138 These flags are set by calling
3140 $tickets->{'flagname'} = 1;
3142 BUG: There should be an API for this
3147 # We assume that we've got some tickets hanging around from before.
3148 ok( my $unlimittickets = RT::Tickets->new( $RT::SystemUser ) );
3149 ok( $unlimittickets->UnLimit );
3150 ok( $unlimittickets->Count > 0, "UnLimited tickets object should return tickets" );