1 # BEGIN BPS TAGGED BLOCK {{{
5 # This software is Copyright (c) 1996-2007 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/copyleft/gpl.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 LinkedTo => [ 'LINKFIELD', ],
140 CustomFieldValue => [ 'CUSTOMFIELD', ],
141 CustomField => [ 'CUSTOMFIELD', ],
142 CF => [ 'CUSTOMFIELD', ],
143 Updated => [ 'TRANSDATE', ],
144 RequestorGroup => [ 'MEMBERSHIPFIELD' => 'Requestor', ],
145 CCGroup => [ 'MEMBERSHIPFIELD' => 'Cc', ],
146 AdminCCGroup => [ 'MEMBERSHIPFIELD' => 'AdminCc', ],
147 WatcherGroup => [ 'MEMBERSHIPFIELD', ],
150 # Mapping of Field Type to Function
152 ENUM => \&_EnumLimit,
154 LINK => \&_LinkLimit,
155 DATE => \&_DateLimit,
156 STRING => \&_StringLimit,
157 TRANSFIELD => \&_TransLimit,
158 TRANSDATE => \&_TransDateLimit,
159 WATCHERFIELD => \&_WatcherLimit,
160 MEMBERSHIPFIELD => \&_WatcherMembershipLimit,
161 LINKFIELD => \&_LinkFieldLimit,
162 CUSTOMFIELD => \&_CustomFieldLimit,
164 my %can_bundle = (); # WATCHERFIELD => "yes", );
166 # Default EntryAggregator per type
167 # if you specify OP, you must specify all valid OPs
203 # Helper functions for passing the above lexically scoped tables above
204 # into Tickets_Overlay_SQL.
205 sub FIELDS { return \%FIELD_METADATA }
206 sub dispatch { return \%dispatch }
207 sub can_bundle { return \%can_bundle }
209 # Bring in the clowns.
210 require RT::Tickets_Overlay_SQL;
214 our @SORTFIELDS = qw(id Status
216 Owner Created Due Starts Started
218 Resolved LastUpdated Priority TimeWorked TimeLeft);
222 Returns the list of fields that lists of tickets can easily be sorted by
228 return (@SORTFIELDS);
233 # BEGIN SQL STUFF *********************************
238 $self->SUPER::CleanSlate( @_ );
239 delete $self->{$_} foreach qw(
241 _sql_group_members_aliases
242 _sql_object_cfv_alias
243 _sql_role_group_aliases
246 _sql_u_watchers_alias_for_sort
247 _sql_u_watchers_aliases
251 =head1 Limit Helper Routines
253 These routines are the targets of a dispatch table depending on the
254 type of field. They all share the same signature:
256 my ($self,$field,$op,$value,@rest) = @_;
258 The values in @rest should be suitable for passing directly to
259 DBIx::SearchBuilder::Limit.
261 Essentially they are an expanded/broken out (and much simplified)
262 version of what ProcessRestrictions used to do. They're also much
263 more clearly delineated by the TYPE of field being processed.
267 Handle Fields which are limited to certain values, and potentially
268 need to be looked up from another class.
270 This subroutine actually handles two different kinds of fields. For
271 some the user is responsible for limiting the values. (i.e. Status,
274 For others, the value specified by the user will be looked by via
278 name of class to lookup in (Optional)
283 my ( $sb, $field, $op, $value, @rest ) = @_;
285 # SQL::Statement changes != to <>. (Can we remove this now?)
286 $op = "!=" if $op eq "<>";
288 die "Invalid Operation: $op for $field"
292 my $meta = $FIELD_METADATA{$field};
293 if ( defined $meta->[1] && defined $value && $value !~ /^\d+$/ ) {
294 my $class = "RT::" . $meta->[1];
295 my $o = $class->new( $sb->CurrentUser );
309 Handle fields where the values are limited to integers. (For example,
310 Priority, TimeWorked.)
318 my ( $sb, $field, $op, $value, @rest ) = @_;
320 die "Invalid Operator $op for $field"
321 unless $op =~ /^(=|!=|>|<|>=|<=)$/;
333 Handle fields which deal with links between tickets. (MemberOf, DependsOn)
336 1: Direction (From, To)
337 2: Link Type (MemberOf, DependsOn, RefersTo)
342 my ( $sb, $field, $op, $value, @rest ) = @_;
344 my $meta = $FIELD_METADATA{$field};
345 die "Incorrect Metadata for $field"
346 unless defined $meta->[1] && defined $meta->[2];
348 die "Invalid Operator $op for $field" unless $op =~ /^(=|!=|IS|IS NOT)$/io;
350 my $direction = $meta->[1];
354 if ( $direction eq 'To' ) {
355 $matchfield = "Target";
359 elsif ( $direction eq 'From' ) {
360 $linkfield = "Target";
361 $matchfield = "Base";
365 die "Invalid link direction '$meta->[1]' for $field\n";
368 my ($is_local, $is_null) = (1, 0);
369 if ( !$value || $value =~ /^null$/io ) {
371 $op = ($op =~ /^(=|IS)$/)? 'IS': 'IS NOT';
373 elsif ( $value =~ /\D/o ) {
376 $matchfield = "Local$matchfield" if $is_local;
384 #For doing a left join to find "unlinked tickets" we want to generate a query that looks like this
385 # SELECT main.* FROM Tickets main
386 # LEFT JOIN Links Links_1 ON ( (Links_1.Type = 'MemberOf')
387 # AND(main.id = Links_1.LocalTarget))
388 # WHERE Links_1.LocalBase IS NULL;
391 my $linkalias = $sb->Join(
396 FIELD2 => 'Local' . $linkfield
399 LEFTJOIN => $linkalias,
407 FIELD => $matchfield,
413 elsif ( $is_negative ) {
414 my $linkalias = $sb->Join(
419 FIELD2 => 'Local' . $linkfield
422 LEFTJOIN => $linkalias,
428 LEFTJOIN => $linkalias,
429 FIELD => $matchfield,
436 FIELD => $matchfield,
443 my $linkalias = $sb->NewAlias('Links');
454 FIELD => 'Local' . $linkfield,
458 ENTRYAGGREGATOR => 'AND',
462 FIELD => $matchfield,
465 ENTRYAGGREGATOR => 'AND',
473 Handle date fields. (Created, LastTold..)
476 1: type of link. (Probably not necessary.)
481 my ( $sb, $field, $op, $value, @rest ) = @_;
483 die "Invalid Date Op: $op"
484 unless $op =~ /^(=|>|<|>=|<=)$/;
486 my $meta = $FIELD_METADATA{$field};
487 die "Incorrect Meta Data for $field"
488 unless ( defined $meta->[1] );
490 my $date = RT::Date->new( $sb->CurrentUser );
491 $date->Set( Format => 'unknown', Value => $value );
495 # if we're specifying =, that means we want everything on a
496 # particular single day. in the database, we need to check for >
497 # and < the edges of that day.
499 $date->SetToMidnight( Timezone => 'server' );
500 my $daystart = $date->ISO;
502 my $dayend = $date->ISO;
518 ENTRYAGGREGATOR => 'AND',
536 Handle simple fields which are just strings. (Subject,Type)
544 my ( $sb, $field, $op, $value, @rest ) = @_;
548 # =, !=, LIKE, NOT LIKE
559 =head2 _TransDateLimit
561 Handle fields limiting based on Transaction Date.
563 The inpupt value must be in a format parseable by Time::ParseDate
570 # This routine should really be factored into translimit.
571 sub _TransDateLimit {
572 my ( $sb, $field, $op, $value, @rest ) = @_;
574 # See the comments for TransLimit, they apply here too
576 unless ( $sb->{_sql_transalias} ) {
577 $sb->{_sql_transalias} = $sb->Join(
580 TABLE2 => 'Transactions',
581 FIELD2 => 'ObjectId',
584 ALIAS => $sb->{_sql_transalias},
585 FIELD => 'ObjectType',
586 VALUE => 'RT::Ticket',
587 ENTRYAGGREGATOR => 'AND',
591 my $date = RT::Date->new( $sb->CurrentUser );
592 $date->Set( Format => 'unknown', Value => $value );
597 # if we're specifying =, that means we want everything on a
598 # particular single day. in the database, we need to check for >
599 # and < the edges of that day.
601 $date->SetToMidnight( Timezone => 'server' );
602 my $daystart = $date->ISO;
604 my $dayend = $date->ISO;
607 ALIAS => $sb->{_sql_transalias},
615 ALIAS => $sb->{_sql_transalias},
621 ENTRYAGGREGATOR => 'AND',
626 # not searching for a single day
629 #Search for the right field
631 ALIAS => $sb->{_sql_transalias},
645 Limit based on the Content of a transaction or the ContentType.
654 # Content, ContentType, Filename
656 # If only this was this simple. We've got to do something
659 #Basically, we want to make sure that the limits apply to
660 #the same attachment, rather than just another attachment
661 #for the same ticket, no matter how many clauses we lump
662 #on. We put them in TicketAliases so that they get nuked
663 #when we redo the join.
665 # In the SQL, we might have
666 # (( Content = foo ) or ( Content = bar AND Content = baz ))
667 # The AND group should share the same Alias.
669 # Actually, maybe it doesn't matter. We use the same alias and it
670 # works itself out? (er.. different.)
672 # Steal more from _ProcessRestrictions
674 # FIXME: Maybe look at the previous FooLimit call, and if it was a
675 # TransLimit and EntryAggregator == AND, reuse the Aliases?
677 # Or better - store the aliases on a per subclause basis - since
678 # those are going to be the things we want to relate to each other,
681 # maybe we should not allow certain kinds of aggregation of these
682 # clauses and do a psuedo regex instead? - the problem is getting
683 # them all into the same subclause when you have (A op B op C) - the
684 # way they get parsed in the tree they're in different subclauses.
686 my ( $self, $field, $op, $value, @rest ) = @_;
688 unless ( $self->{_sql_transalias} ) {
689 $self->{_sql_transalias} = $self->Join(
692 TABLE2 => 'Transactions',
693 FIELD2 => 'ObjectId',
696 ALIAS => $self->{_sql_transalias},
697 FIELD => 'ObjectType',
698 VALUE => 'RT::Ticket',
699 ENTRYAGGREGATOR => 'AND',
702 unless ( defined $self->{_sql_trattachalias} ) {
703 $self->{_sql_trattachalias} = $self->_SQLJoin(
704 TYPE => 'LEFT', # not all txns have an attachment
705 ALIAS1 => $self->{_sql_transalias},
707 TABLE2 => 'Attachments',
708 FIELD2 => 'TransactionId',
714 #Search for the right field
715 if ($field eq 'Content' and $RT::DontSearchFileAttachments) {
717 ALIAS => $self->{_sql_trattachalias},
721 SUBCLAUSE => 'contentquery',
722 ENTRYAGGREGATOR => 'AND',
725 ALIAS => $self->{_sql_trattachalias},
731 ENTRYAGGREGATOR => 'AND',
732 SUBCLAUSE => 'contentquery',
736 ALIAS => $self->{_sql_trattachalias},
741 ENTRYAGGREGATOR => 'AND',
752 Handle watcher limits. (Requestor, CC, etc..)
760 # Test to make sure that you can search for tickets by requestor address and
764 my $u1 = RT::User->new($RT::SystemUser);
765 ($id, $msg) = $u1->Create( Name => 'RequestorTestOne', EmailAddress => 'rqtest1@example.com');
767 my $u2 = RT::User->new($RT::SystemUser);
768 ($id, $msg) = $u2->Create( Name => 'RequestorTestTwo', EmailAddress => 'rqtest2@example.com');
771 my $t1 = RT::Ticket->new($RT::SystemUser);
773 ($id,$trans,$msg) =$t1->Create (Queue => 'general', Subject => 'Requestor test one', Requestor => [$u1->EmailAddress]);
776 my $t2 = RT::Ticket->new($RT::SystemUser);
777 ($id,$trans,$msg) =$t2->Create (Queue => 'general', Subject => 'Requestor test one', Requestor => [$u2->EmailAddress]);
781 my $t3 = RT::Ticket->new($RT::SystemUser);
782 ($id,$trans,$msg) =$t3->Create (Queue => 'general', Subject => 'Requestor test one', Requestor => [$u2->EmailAddress, $u1->EmailAddress]);
786 my $tix1 = RT::Tickets->new($RT::SystemUser);
787 $tix1->FromSQL('Requestor.EmailAddress LIKE "rqtest1" OR Requestor.EmailAddress LIKE "rqtest2"');
789 is ($tix1->Count, 3);
791 my $tix2 = RT::Tickets->new($RT::SystemUser);
792 $tix2->FromSQL('Requestor.Name LIKE "TestOne" OR Requestor.Name LIKE "TestTwo"');
794 is ($tix2->Count, 3);
797 my $tix3 = RT::Tickets->new($RT::SystemUser);
798 $tix3->FromSQL('Requestor.EmailAddress LIKE "rqtest1"');
800 is ($tix3->Count, 2);
802 my $tix4 = RT::Tickets->new($RT::SystemUser);
803 $tix4->FromSQL('Requestor.Name LIKE "TestOne" ');
805 is ($tix4->Count, 2);
807 # Searching for tickets that have two requestors isn't supported
808 # There's no way to differentiate "one requestor name that matches foo and bar"
809 # and "two requestors, one matching foo and one matching bar"
811 # my $tix5 = RT::Tickets->new($RT::SystemUser);
812 # $tix5->FromSQL('Requestor.Name LIKE "TestOne" AND Requestor.Name LIKE "TestTwo"');
814 # is ($tix5->Count, 1);
816 # my $tix6 = RT::Tickets->new($RT::SystemUser);
817 # $tix6->FromSQL('Requestor.EmailAddress LIKE "rqtest1" AND Requestor.EmailAddress LIKE "rqtest2"');
819 # is ($tix6->Count, 1);
833 my $meta = $FIELD_METADATA{ $field };
834 my $type = $meta->[1] || '';
836 # Owner was ENUM field, so "Owner = 'xxx'" allowed user to
837 # search by id and Name at the same time, this is workaround
838 # to preserve backward compatibility
839 if ( $field eq 'Owner' && !$rest{SUBKEY} && $op =~ /^!?=$/ ) {
840 my $o = RT::User->new( $self->CurrentUser );
850 $rest{SUBKEY} ||= 'EmailAddress';
852 my $groups = $self->_RoleGroupsJoin( Type => $type );
855 if ( $op =~ /^IS(?: NOT)?$/ ) {
856 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
857 # to avoid joining the table Users into the query, we just join GM
858 # and make sure we don't match records where group is member of itself
860 LEFTJOIN => $group_members,
863 VALUE => "$group_members.MemberId",
867 ALIAS => $group_members,
874 elsif ( $op =~ /^!=$|^NOT\s+/i ) {
876 $op =~ s/!|NOT\s+//i;
878 # XXX: we have no way to build correct "Watcher.X != 'Y'" when condition
879 # "X = 'Y'" matches more then one user so we try to fetch two records and
880 # do the right thing when there is only one exist and semi-working solution
882 my $users_obj = RT::Users->new( $self->CurrentUser );
884 FIELD => $rest{SUBKEY},
889 $users_obj->RowsPerPage(2);
890 my @users = @{ $users_obj->ItemsArrayRef };
892 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
895 $uid = $users[0]->id if @users;
897 LEFTJOIN => $group_members,
898 ALIAS => $group_members,
904 ALIAS => $group_members,
911 LEFTJOIN => $group_members,
914 VALUE => "$group_members.MemberId",
917 my $users = $self->Join(
919 ALIAS1 => $group_members,
920 FIELD1 => 'MemberId',
927 FIELD => $rest{SUBKEY},
941 my $group_members = $self->_GroupMembersJoin(
942 GroupsAlias => $groups,
946 my $users = $self->{'_sql_u_watchers_aliases'}{$group_members};
948 $users = $self->{'_sql_u_watchers_aliases'}{$group_members} =
949 $self->NewAlias('Users');
951 LEFTJOIN => $group_members,
952 ALIAS => $group_members,
954 VALUE => "$users.id",
959 # we join users table without adding some join condition between tables,
960 # the only conditions we have are conditions on the table iteslf,
961 # for example Users.EmailAddress = 'x'. We should add this condition to
962 # the top level of the query and bundle it with another similar conditions,
963 # for example "Users.EmailAddress = 'x' OR Users.EmailAddress = 'Y'".
964 # To achive this goal we use own SUBCLAUSE for conditions on the users table.
967 SUBCLAUSE => '_sql_u_watchers_'. $users,
969 FIELD => $rest{'SUBKEY'},
974 # A condition which ties Users and Groups (role groups) is a left join condition
975 # of CachedGroupMembers table. To get correct results of the query we check
976 # if there are matches in CGM table or not using 'cgm.id IS NOT NULL'.
979 ALIAS => $group_members,
981 OPERATOR => 'IS NOT',
988 sub _RoleGroupsJoin {
990 my %args = (New => 0, Type => '', @_);
991 return $self->{'_sql_role_group_aliases'}{ $args{'Type'} }
992 if $self->{'_sql_role_group_aliases'}{ $args{'Type'} } && !$args{'New'};
994 # XXX: this has been fixed in DBIx::SB-1.48
995 # XXX: if we change this from Join to NewAlias+Limit
996 # then Pg and mysql 5.x will complain because SB build wrong query.
997 # Query looks like "FROM (Tickets LEFT JOIN CGM ON(Groups.id = CGM.GroupId)), Groups"
998 # Pg doesn't like that fact that it doesn't know about Groups table yet when
999 # join CGM table into Tickets. Problem is in Join method which doesn't use
1000 # ALIAS1 argument when build braces.
1002 # we always have watcher groups for ticket, so we use INNER join
1003 my $groups = $self->Join(
1007 FIELD2 => 'Instance',
1008 ENTRYAGGREGATOR => 'AND',
1010 $self->SUPER::Limit(
1011 LEFTJOIN => $groups,
1014 VALUE => 'RT::Ticket-Role',
1016 $self->SUPER::Limit(
1017 LEFTJOIN => $groups,
1020 VALUE => $args{'Type'},
1023 $self->{'_sql_role_group_aliases'}{ $args{'Type'} } = $groups
1024 unless $args{'New'};
1029 sub _GroupMembersJoin {
1031 my %args = (New => 1, GroupsAlias => undef, @_);
1033 return $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1034 if $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} }
1037 my $alias = $self->Join(
1039 ALIAS1 => $args{'GroupsAlias'},
1041 TABLE2 => 'CachedGroupMembers',
1042 FIELD2 => 'GroupId',
1043 ENTRYAGGREGATOR => 'AND',
1046 $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} } = $alias
1047 unless $args{'New'};
1054 Helper function which provides joins to a watchers table both for limits
1061 my $type = shift || '';
1064 my $groups = $self->_RoleGroupsJoin( Type => $type );
1065 my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups );
1066 # XXX: work around, we must hide groups that
1067 # are members of the role group we search in,
1068 # otherwise them result in wrong NULLs in Users
1069 # table and break ordering. Now, we know that
1070 # RT doesn't allow to add groups as members of the
1071 # ticket roles, so we just hide entries in CGM table
1072 # with MemberId == GroupId from results
1073 $self->SUPER::Limit(
1074 LEFTJOIN => $group_members,
1077 VALUE => "$group_members.MemberId",
1080 my $users = $self->Join(
1082 ALIAS1 => $group_members,
1083 FIELD1 => 'MemberId',
1087 return ($groups, $group_members, $users);
1090 =head2 _WatcherMembershipLimit
1092 Handle watcher membership limits, i.e. whether the watcher belongs to a
1093 specific group or not.
1096 1: Field to query on
1098 SELECT DISTINCT main.*
1102 CachedGroupMembers CachedGroupMembers_2,
1105 (main.EffectiveId = main.id)
1107 (main.Status != 'deleted')
1109 (main.Type = 'ticket')
1112 (Users_3.EmailAddress = '22')
1114 (Groups_1.Domain = 'RT::Ticket-Role')
1116 (Groups_1.Type = 'RequestorGroup')
1119 Groups_1.Instance = main.id
1121 Groups_1.id = CachedGroupMembers_2.GroupId
1123 CachedGroupMembers_2.MemberId = Users_3.id
1124 ORDER BY main.id ASC
1129 sub _WatcherMembershipLimit {
1130 my ( $self, $field, $op, $value, @rest ) = @_;
1135 my $groups = $self->NewAlias('Groups');
1136 my $groupmembers = $self->NewAlias('CachedGroupMembers');
1137 my $users = $self->NewAlias('Users');
1138 my $memberships = $self->NewAlias('CachedGroupMembers');
1140 if ( ref $field ) { # gross hack
1141 my @bundle = @$field;
1143 for my $chunk (@bundle) {
1144 ( $field, $op, $value, @rest ) = @$chunk;
1146 ALIAS => $memberships,
1157 ALIAS => $memberships,
1165 # {{{ Tie to groups for tickets we care about
1169 VALUE => 'RT::Ticket-Role',
1170 ENTRYAGGREGATOR => 'AND'
1175 FIELD1 => 'Instance',
1182 # If we care about which sort of watcher
1183 my $meta = $FIELD_METADATA{$field};
1184 my $type = ( defined $meta->[1] ? $meta->[1] : undef );
1191 ENTRYAGGREGATOR => 'AND'
1198 ALIAS2 => $groupmembers,
1203 ALIAS1 => $groupmembers,
1204 FIELD1 => 'MemberId',
1210 ALIAS1 => $memberships,
1211 FIELD1 => 'MemberId',
1220 sub _LinkFieldLimit {
1225 if ( $restriction->{'TYPE'} ) {
1226 $self->SUPER::Limit(
1227 ALIAS => $LinkAlias,
1228 ENTRYAGGREGATOR => 'AND',
1231 VALUE => $restriction->{'TYPE'}
1235 #If we're trying to limit it to things that are target of
1236 if ( $restriction->{'TARGET'} ) {
1238 # If the TARGET is an integer that means that we want to look at
1239 # the LocalTarget field. otherwise, we want to look at the
1242 if ( $restriction->{'TARGET'} =~ /^(\d+)$/ ) {
1243 $matchfield = "LocalTarget";
1246 $matchfield = "Target";
1248 $self->SUPER::Limit(
1249 ALIAS => $LinkAlias,
1250 ENTRYAGGREGATOR => 'AND',
1251 FIELD => $matchfield,
1253 VALUE => $restriction->{'TARGET'}
1256 #If we're searching on target, join the base to ticket.id
1259 FIELD1 => $self->{'primary_key'},
1260 ALIAS2 => $LinkAlias,
1261 FIELD2 => 'LocalBase'
1265 #If we're trying to limit it to things that are base of
1266 elsif ( $restriction->{'BASE'} ) {
1268 # If we're trying to match a numeric link, we want to look at
1269 # LocalBase, otherwise we want to look at "Base"
1271 if ( $restriction->{'BASE'} =~ /^(\d+)$/ ) {
1272 $matchfield = "LocalBase";
1275 $matchfield = "Base";
1278 $self->SUPER::Limit(
1279 ALIAS => $LinkAlias,
1280 ENTRYAGGREGATOR => 'AND',
1281 FIELD => $matchfield,
1283 VALUE => $restriction->{'BASE'}
1286 #If we're searching on base, join the target to ticket.id
1289 FIELD1 => $self->{'primary_key'},
1290 ALIAS2 => $LinkAlias,
1291 FIELD2 => 'LocalTarget'
1297 =head2 _CustomFieldDecipher
1299 Try and turn a CF descriptor into (cfid, cfname) object pair.
1303 sub _CustomFieldDecipher {
1304 my ($self, $field) = @_;
1307 if ( $field =~ /^(.+?)\.{(.+)}$/ ) {
1308 ($queue, $field) = ($1, $2);
1310 $field = $1 if $field =~ /^{(.+)}$/; # trim { }
1314 my $q = RT::Queue->new( $self->CurrentUser );
1315 $q->Load( $queue ) if $queue;
1319 # $queue = $q->Name; # should we normalize the queue?
1320 $cf = $q->CustomField( $field );
1323 $cf = RT::CustomField->new( $self->CurrentUser );
1324 $cf->LoadByNameAndQueue( Queue => 0, Name => $field );
1326 $cfid = $cf->id if $cf;
1329 return ($queue, $field, $cfid);
1333 =head2 _CustomFieldJoin
1335 Factor out the Join of custom fields so we can use it for sorting too
1339 sub _CustomFieldJoin {
1340 my ($self, $cfkey, $cfid, $field) = @_;
1341 # Perform one Join per CustomField
1342 if ( $self->{_sql_object_cfv_alias}{$cfkey} ||
1343 $self->{_sql_cf_alias}{$cfkey} )
1345 return ( $self->{_sql_object_cfv_alias}{$cfkey},
1346 $self->{_sql_cf_alias}{$cfkey} );
1349 my ($TicketCFs, $CFs);
1351 $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1355 TABLE2 => 'ObjectCustomFieldValues',
1356 FIELD2 => 'ObjectId',
1358 $self->SUPER::Limit(
1359 LEFTJOIN => $TicketCFs,
1360 FIELD => 'CustomField',
1362 ENTRYAGGREGATOR => 'AND'
1366 my $ocfalias = $self->Join(
1369 TABLE2 => 'ObjectCustomFields',
1370 FIELD2 => 'ObjectId',
1373 $self->SUPER::Limit(
1374 LEFTJOIN => $ocfalias,
1375 ENTRYAGGREGATOR => 'OR',
1376 FIELD => 'ObjectId',
1380 $CFs = $self->{_sql_cf_alias}{$cfkey} = $self->Join(
1382 ALIAS1 => $ocfalias,
1383 FIELD1 => 'CustomField',
1384 TABLE2 => 'CustomFields',
1388 $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join(
1392 TABLE2 => 'ObjectCustomFieldValues',
1393 FIELD2 => 'CustomField',
1395 $self->SUPER::Limit(
1396 LEFTJOIN => $TicketCFs,
1397 FIELD => 'ObjectId',
1400 ENTRYAGGREGATOR => 'AND',
1403 $self->SUPER::Limit(
1404 LEFTJOIN => $TicketCFs,
1405 FIELD => 'ObjectType',
1406 VALUE => 'RT::Ticket',
1407 ENTRYAGGREGATOR => 'AND'
1409 $self->SUPER::Limit(
1410 LEFTJOIN => $TicketCFs,
1411 FIELD => 'Disabled',
1414 ENTRYAGGREGATOR => 'AND'
1417 return ($TicketCFs, $CFs);
1420 =head2 _CustomFieldLimit
1422 Limit based on CustomFields
1429 sub _CustomFieldLimit {
1430 my ( $self, $_field, $op, $value, @rest ) = @_;
1433 my $field = $rest{SUBKEY} || die "No field specified";
1435 # For our sanity, we can only limit on one queue at a time
1438 ($queue, $field, $cfid ) = $self->_CustomFieldDecipher( $field );
1440 # If we're trying to find custom fields that don't match something, we
1441 # want tickets where the custom field has no value at all. Note that
1442 # we explicitly don't include the "IS NULL" case, since we would
1443 # otherwise end up with a redundant clause.
1445 my $null_columns_ok;
1446 if ( ( $op =~ /^NOT LIKE$/i ) or ( $op eq '!=' ) ) {
1447 $null_columns_ok = 1;
1450 my $cfkey = $cfid ? $cfid : "$queue.$field";
1451 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1456 $self->SUPER::Limit(
1460 ENTRYAGGREGATOR => 'AND',
1464 $self->_OpenParen if $null_columns_ok;
1467 ALIAS => $TicketCFs,
1475 if ($null_columns_ok) {
1477 ALIAS => $TicketCFs,
1482 ENTRYAGGREGATOR => 'OR',
1491 # End Helper Functions
1493 # End of SQL Stuff -------------------------------------------------
1495 # {{{ Allow sorting on watchers
1497 =head2 OrderByCols ARRAY
1499 A modified version of the OrderBy method which automatically joins where
1500 C<ALIAS> is set to the name of a watcher type.
1511 foreach my $row (@args) {
1512 if ( $row->{ALIAS} || $row->{FIELD} !~ /\./ ) {
1516 my ( $field, $subkey ) = split /\./, $row->{FIELD}, 2;
1517 my $meta = $self->FIELDS->{$field};
1518 if ( $meta->[0] eq 'WATCHERFIELD' ) {
1519 # cache alias as we want to use one alias per watcher type for sorting
1520 my $users = $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] };
1522 $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] }
1523 = $users = ( $self->_WatcherJoin( $meta->[1] ) )[2];
1525 push @res, { %$row, ALIAS => $users, FIELD => $subkey };
1526 } elsif ( $meta->[0] eq 'CUSTOMFIELD' ) {
1527 my ($queue, $field, $cfid ) = $self->_CustomFieldDecipher( $subkey );
1528 my $cfkey = $cfid ? $cfid : "$queue.$field";
1529 my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field );
1531 # For those cases where we are doing a join against the
1532 # CF name, and don't have a CFid, use Unique to make sure
1533 # we don't show duplicate tickets. NOTE: I'm pretty sure
1534 # this will stay mixed in for the life of the
1535 # class/package, and not just for the life of the object.
1536 # Potential performance issue.
1537 require DBIx::SearchBuilder::Unique;
1538 DBIx::SearchBuilder::Unique->import;
1540 my $CFvs = $self->Join(
1542 ALIAS1 => $TicketCFs,
1543 FIELD1 => 'CustomField',
1544 TABLE2 => 'CustomFieldValues',
1545 FIELD2 => 'CustomField',
1547 $self->SUPER::Limit(
1551 VALUE => $TicketCFs . ".Content",
1552 ENTRYAGGREGATOR => 'AND'
1555 push @res, { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' };
1556 push @res, { %$row, ALIAS => $TicketCFs, FIELD => 'Content' };
1557 } elsif ( $field eq "Custom" && $subkey eq "Ownership") {
1558 # PAW logic is "reversed"
1560 if (exists $row->{ORDER} ) {
1561 my $o = $row->{ORDER};
1562 delete $row->{ORDER};
1563 $order = "DESC" if $o =~ /asc/i;
1569 # Ticket.Owner 1 0 0
1570 my $ownerId = $self->CurrentUser->Id;
1571 push @res, { %$row, FIELD => "Owner=$ownerId", ORDER => $order } ;
1573 # Unowned Tickets 0 1 0
1574 my $nobodyId = $RT::Nobody->Id;
1575 push @res, { %$row, FIELD => "Owner=$nobodyId", ORDER => $order } ;
1577 push @res, { %$row, FIELD => "Priority", ORDER => $order } ;
1583 return $self->SUPER::OrderByCols(@res);
1588 # {{{ Limit the result set based on content
1594 Takes a paramhash with the fields FIELD, OPERATOR, VALUE and DESCRIPTION
1595 Generally best called from LimitFoo methods
1605 DESCRIPTION => undef,
1608 $args{'DESCRIPTION'} = $self->loc(
1609 "[_1] [_2] [_3]", $args{'FIELD'},
1610 $args{'OPERATOR'}, $args{'VALUE'}
1612 if ( !defined $args{'DESCRIPTION'} );
1614 my $index = $self->_NextIndex;
1616 # make the TicketRestrictions hash the equivalent of whatever we just passed in;
1618 %{ $self->{'TicketRestrictions'}{$index} } = %args;
1620 $self->{'RecalcTicketLimits'} = 1;
1622 # If we're looking at the effective id, we don't want to append the other clause
1623 # which limits us to tickets where id = effective id
1624 if ( $args{'FIELD'} eq 'EffectiveId'
1625 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
1627 $self->{'looking_at_effective_id'} = 1;
1630 if ( $args{'FIELD'} eq 'Type'
1631 && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
1633 $self->{'looking_at_type'} = 1;
1643 Returns a frozen string suitable for handing back to ThawLimits.
1647 sub _FreezeThawKeys {
1648 'TicketRestrictions', 'restriction_index', 'looking_at_effective_id',
1652 # {{{ sub FreezeLimits
1657 require MIME::Base64;
1658 MIME::Base64::base64_encode(
1659 Storable::freeze( \@{$self}{ $self->_FreezeThawKeys } ) );
1666 Take a frozen Limits string generated by FreezeLimits and make this tickets
1667 object have that set of limits.
1671 # {{{ sub ThawLimits
1677 #if we don't have $in, get outta here.
1678 return undef unless ($in);
1680 $self->{'RecalcTicketLimits'} = 1;
1683 require MIME::Base64;
1685 #We don't need to die if the thaw fails.
1686 @{$self}{ $self->_FreezeThawKeys }
1687 = eval { @{ Storable::thaw( MIME::Base64::base64_decode($in) ) }; };
1689 $RT::Logger->error($@) if $@;
1695 # {{{ Limit by enum or foreign key
1697 # {{{ sub LimitQueue
1701 LimitQueue takes a paramhash with the fields OPERATOR and VALUE.
1702 OPERATOR is one of = or !=. (It defaults to =).
1703 VALUE is a queue id or Name.
1716 #TODO VALUE should also take queue objects
1717 if ( defined $args{'VALUE'} && $args{'VALUE'} !~ /^\d+$/ ) {
1718 my $queue = new RT::Queue( $self->CurrentUser );
1719 $queue->Load( $args{'VALUE'} );
1720 $args{'VALUE'} = $queue->Id;
1723 # What if they pass in an Id? Check for isNum() and convert to
1726 #TODO check for a valid queue here
1730 VALUE => $args{'VALUE'},
1731 OPERATOR => $args{'OPERATOR'},
1732 DESCRIPTION => join(
1733 ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{'VALUE'},
1741 # {{{ sub LimitStatus
1745 Takes a paramhash with the fields OPERATOR and VALUE.
1746 OPERATOR is one of = or !=.
1749 RT adds Status != 'deleted' until object has
1750 allow_deleted_search internal property set.
1751 $tickets->{'allow_deleted_search'} = 1;
1752 $tickets->LimitStatus( VALUE => 'deleted' );
1764 VALUE => $args{'VALUE'},
1765 OPERATOR => $args{'OPERATOR'},
1766 DESCRIPTION => join( ' ',
1767 $self->loc('Status'), $args{'OPERATOR'},
1768 $self->loc( $args{'VALUE'} ) ),
1774 # {{{ sub IgnoreType
1778 If called, this search will not automatically limit the set of results found
1779 to tickets of type "Ticket". Tickets of other types, such as "project" and
1780 "approval" will be found.
1787 # Instead of faking a Limit that later gets ignored, fake up the
1788 # fact that we're already looking at type, so that the check in
1789 # Tickets_Overlay_SQL/FromSQL goes down the right branch
1791 # $self->LimitType(VALUE => '__any');
1792 $self->{looking_at_type} = 1;
1801 Takes a paramhash with the fields OPERATOR and VALUE.
1802 OPERATOR is one of = or !=, it defaults to "=".
1803 VALUE is a string to search for in the type of the ticket.
1818 VALUE => $args{'VALUE'},
1819 OPERATOR => $args{'OPERATOR'},
1820 DESCRIPTION => join( ' ',
1821 $self->loc('Type'), $args{'OPERATOR'}, $args{'Limit'}, ),
1829 # {{{ Limit by string field
1831 # {{{ sub LimitSubject
1835 Takes a paramhash with the fields OPERATOR and VALUE.
1836 OPERATOR is one of = or !=.
1837 VALUE is a string to search for in the subject of the ticket.
1846 VALUE => $args{'VALUE'},
1847 OPERATOR => $args{'OPERATOR'},
1848 DESCRIPTION => join( ' ',
1849 $self->loc('Subject'), $args{'OPERATOR'}, $args{'VALUE'}, ),
1857 # {{{ Limit based on ticket numerical attributes
1858 # Things that can be > < = !=
1864 Takes a paramhash with the fields OPERATOR and VALUE.
1865 OPERATOR is one of =, >, < or !=.
1866 VALUE is a ticket Id to search for
1879 VALUE => $args{'VALUE'},
1880 OPERATOR => $args{'OPERATOR'},
1882 join( ' ', $self->loc('Id'), $args{'OPERATOR'}, $args{'VALUE'}, ),
1888 # {{{ sub LimitPriority
1890 =head2 LimitPriority
1892 Takes a paramhash with the fields OPERATOR and VALUE.
1893 OPERATOR is one of =, >, < or !=.
1894 VALUE is a value to match the ticket\'s priority against
1902 FIELD => 'Priority',
1903 VALUE => $args{'VALUE'},
1904 OPERATOR => $args{'OPERATOR'},
1905 DESCRIPTION => join( ' ',
1906 $self->loc('Priority'),
1907 $args{'OPERATOR'}, $args{'VALUE'}, ),
1913 # {{{ sub LimitInitialPriority
1915 =head2 LimitInitialPriority
1917 Takes a paramhash with the fields OPERATOR and VALUE.
1918 OPERATOR is one of =, >, < or !=.
1919 VALUE is a value to match the ticket\'s initial priority against
1924 sub LimitInitialPriority {
1928 FIELD => 'InitialPriority',
1929 VALUE => $args{'VALUE'},
1930 OPERATOR => $args{'OPERATOR'},
1931 DESCRIPTION => join( ' ',
1932 $self->loc('Initial Priority'), $args{'OPERATOR'},
1939 # {{{ sub LimitFinalPriority
1941 =head2 LimitFinalPriority
1943 Takes a paramhash with the fields OPERATOR and VALUE.
1944 OPERATOR is one of =, >, < or !=.
1945 VALUE is a value to match the ticket\'s final priority against
1949 sub LimitFinalPriority {
1953 FIELD => 'FinalPriority',
1954 VALUE => $args{'VALUE'},
1955 OPERATOR => $args{'OPERATOR'},
1956 DESCRIPTION => join( ' ',
1957 $self->loc('Final Priority'), $args{'OPERATOR'},
1964 # {{{ sub LimitTimeWorked
1966 =head2 LimitTimeWorked
1968 Takes a paramhash with the fields OPERATOR and VALUE.
1969 OPERATOR is one of =, >, < or !=.
1970 VALUE is a value to match the ticket's TimeWorked attribute
1974 sub LimitTimeWorked {
1978 FIELD => 'TimeWorked',
1979 VALUE => $args{'VALUE'},
1980 OPERATOR => $args{'OPERATOR'},
1981 DESCRIPTION => join( ' ',
1982 $self->loc('Time Worked'),
1983 $args{'OPERATOR'}, $args{'VALUE'}, ),
1989 # {{{ sub LimitTimeLeft
1991 =head2 LimitTimeLeft
1993 Takes a paramhash with the fields OPERATOR and VALUE.
1994 OPERATOR is one of =, >, < or !=.
1995 VALUE is a value to match the ticket's TimeLeft attribute
2003 FIELD => 'TimeLeft',
2004 VALUE => $args{'VALUE'},
2005 OPERATOR => $args{'OPERATOR'},
2006 DESCRIPTION => join( ' ',
2007 $self->loc('Time Left'),
2008 $args{'OPERATOR'}, $args{'VALUE'}, ),
2016 # {{{ Limiting based on attachment attributes
2018 # {{{ sub LimitContent
2022 Takes a paramhash with the fields OPERATOR and VALUE.
2023 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2024 VALUE is a string to search for in the body of the ticket
2033 VALUE => $args{'VALUE'},
2034 OPERATOR => $args{'OPERATOR'},
2035 DESCRIPTION => join( ' ',
2036 $self->loc('Ticket content'), $args{'OPERATOR'},
2043 # {{{ sub LimitFilename
2045 =head2 LimitFilename
2047 Takes a paramhash with the fields OPERATOR and VALUE.
2048 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2049 VALUE is a string to search for in the body of the ticket
2057 FIELD => 'Filename',
2058 VALUE => $args{'VALUE'},
2059 OPERATOR => $args{'OPERATOR'},
2060 DESCRIPTION => join( ' ',
2061 $self->loc('Attachment filename'), $args{'OPERATOR'},
2067 # {{{ sub LimitContentType
2069 =head2 LimitContentType
2071 Takes a paramhash with the fields OPERATOR and VALUE.
2072 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2073 VALUE is a content type to search ticket attachments for
2077 sub LimitContentType {
2081 FIELD => 'ContentType',
2082 VALUE => $args{'VALUE'},
2083 OPERATOR => $args{'OPERATOR'},
2084 DESCRIPTION => join( ' ',
2085 $self->loc('Ticket content type'), $args{'OPERATOR'},
2094 # {{{ Limiting based on people
2096 # {{{ sub LimitOwner
2100 Takes a paramhash with the fields OPERATOR and VALUE.
2101 OPERATOR is one of = or !=.
2113 my $owner = new RT::User( $self->CurrentUser );
2114 $owner->Load( $args{'VALUE'} );
2116 # FIXME: check for a valid $owner
2119 VALUE => $args{'VALUE'},
2120 OPERATOR => $args{'OPERATOR'},
2121 DESCRIPTION => join( ' ',
2122 $self->loc('Owner'), $args{'OPERATOR'}, $owner->Name(), ),
2129 # {{{ Limiting watchers
2131 # {{{ sub LimitWatcher
2135 Takes a paramhash with the fields OPERATOR, TYPE and VALUE.
2136 OPERATOR is one of =, LIKE, NOT LIKE or !=.
2137 VALUE is a value to match the ticket\'s watcher email addresses against
2138 TYPE is the sort of watchers you want to match against. Leave it undef if you want to search all of them
2142 my $t1 = RT::Ticket->new($RT::SystemUser);
2143 $t1->Create(Queue => 'general', Subject => "LimitWatchers test", Requestors => \['requestor1@example.com']);
2158 #build us up a description
2159 my ( $watcher_type, $desc );
2160 if ( $args{'TYPE'} ) {
2161 $watcher_type = $args{'TYPE'};
2164 $watcher_type = "Watcher";
2168 FIELD => $watcher_type,
2169 VALUE => $args{'VALUE'},
2170 OPERATOR => $args{'OPERATOR'},
2171 TYPE => $args{'TYPE'},
2172 DESCRIPTION => join( ' ',
2173 $self->loc($watcher_type),
2174 $args{'OPERATOR'}, $args{'VALUE'}, ),
2178 sub LimitRequestor {
2181 $RT::Logger->error( "Tickets->LimitRequestor is deprecated at ("
2182 . join( ":", caller )
2184 $self->LimitWatcher( TYPE => 'Requestor', @_ );
2194 # {{{ Limiting based on links
2198 =head2 LimitLinkedTo
2200 LimitLinkedTo takes a paramhash with two fields: TYPE and TARGET
2201 TYPE limits the sort of link we want to search on
2203 TYPE = { RefersTo, MemberOf, DependsOn }
2205 TARGET is the id or URI of the TARGET of the link
2206 (TARGET used to be 'TICKET'. 'TICKET' is deprecated, but will be treated as TARGET
2221 FIELD => 'LinkedTo',
2223 TARGET => ( $args{'TARGET'} || $args{'TICKET'} ),
2224 TYPE => $args{'TYPE'},
2225 DESCRIPTION => $self->loc(
2226 "Tickets [_1] by [_2]",
2227 $self->loc( $args{'TYPE'} ),
2228 ( $args{'TARGET'} || $args{'TICKET'} )
2230 OPERATOR => $args{'OPERATOR'},
2236 # {{{ LimitLinkedFrom
2238 =head2 LimitLinkedFrom
2240 LimitLinkedFrom takes a paramhash with two fields: TYPE and BASE
2241 TYPE limits the sort of link we want to search on
2244 BASE is the id or URI of the BASE of the link
2245 (BASE used to be 'TICKET'. 'TICKET' is deprecated, but will be treated as BASE
2250 sub LimitLinkedFrom {
2260 # translate RT2 From/To naming to RT3 TicketSQL naming
2261 my %fromToMap = qw(DependsOn DependentOn
2263 RefersTo ReferredToBy);
2265 my $type = $args{'TYPE'};
2266 $type = $fromToMap{$type} if exists( $fromToMap{$type} );
2269 FIELD => 'LinkedTo',
2271 BASE => ( $args{'BASE'} || $args{'TICKET'} ),
2273 DESCRIPTION => $self->loc(
2274 "Tickets [_1] [_2]",
2275 $self->loc( $args{'TYPE'} ),
2276 ( $args{'BASE'} || $args{'TICKET'} )
2278 OPERATOR => $args{'OPERATOR'},
2287 my $ticket_id = shift;
2288 return $self->LimitLinkedTo(
2290 TARGET => $ticket_id,
2297 # {{{ LimitHasMember
2298 sub LimitHasMember {
2300 my $ticket_id = shift;
2301 return $self->LimitLinkedFrom(
2303 BASE => "$ticket_id",
2304 TYPE => 'HasMember',
2311 # {{{ LimitDependsOn
2313 sub LimitDependsOn {
2315 my $ticket_id = shift;
2316 return $self->LimitLinkedTo(
2318 TARGET => $ticket_id,
2319 TYPE => 'DependsOn',
2326 # {{{ LimitDependedOnBy
2328 sub LimitDependedOnBy {
2330 my $ticket_id = shift;
2331 return $self->LimitLinkedFrom(
2334 TYPE => 'DependentOn',
2345 my $ticket_id = shift;
2346 return $self->LimitLinkedTo(
2348 TARGET => $ticket_id,
2356 # {{{ LimitReferredToBy
2358 sub LimitReferredToBy {
2360 my $ticket_id = shift;
2361 return $self->LimitLinkedFrom(
2364 TYPE => 'ReferredToBy',
2372 # {{{ limit based on ticket date attribtes
2376 =head2 LimitDate (FIELD => 'DateField', OPERATOR => $oper, VALUE => $ISODate)
2378 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2380 OPERATOR is one of > or <
2381 VALUE is a date and time in ISO format in GMT
2382 FIELD is one of Starts, Started, Told, Created, Resolved, LastUpdated
2384 There are also helper functions of the form LimitFIELD that eliminate
2385 the need to pass in a FIELD argument.
2399 #Set the description if we didn't get handed it above
2400 unless ( $args{'DESCRIPTION'} ) {
2401 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2402 . $args{'OPERATOR'} . " "
2403 . $args{'VALUE'} . " GMT";
2406 $self->Limit(%args);
2414 $self->LimitDate( FIELD => 'Created', @_ );
2419 $self->LimitDate( FIELD => 'Due', @_ );
2425 $self->LimitDate( FIELD => 'Starts', @_ );
2431 $self->LimitDate( FIELD => 'Started', @_ );
2436 $self->LimitDate( FIELD => 'Resolved', @_ );
2441 $self->LimitDate( FIELD => 'Told', @_ );
2444 sub LimitLastUpdated {
2446 $self->LimitDate( FIELD => 'LastUpdated', @_ );
2450 # {{{ sub LimitTransactionDate
2452 =head2 LimitTransactionDate (OPERATOR => $oper, VALUE => $ISODate)
2454 Takes a paramhash with the fields FIELD OPERATOR and VALUE.
2456 OPERATOR is one of > or <
2457 VALUE is a date and time in ISO format in GMT
2462 sub LimitTransactionDate {
2465 FIELD => 'TransactionDate',
2472 # <20021217042756.GK28744@pallas.fsck.com>
2473 # "Kill It" - Jesse.
2475 #Set the description if we didn't get handed it above
2476 unless ( $args{'DESCRIPTION'} ) {
2477 $args{'DESCRIPTION'} = $args{'FIELD'} . " "
2478 . $args{'OPERATOR'} . " "
2479 . $args{'VALUE'} . " GMT";
2482 $self->Limit(%args);
2490 # {{{ Limit based on custom fields
2491 # {{{ sub LimitCustomField
2493 =head2 LimitCustomField
2495 Takes a paramhash of key/value pairs with the following keys:
2499 =item CUSTOMFIELD - CustomField name or id. If a name is passed, an additional parameter QUEUE may also be passed to distinguish the custom field.
2501 =item OPERATOR - The usual Limit operators
2503 =item VALUE - The value to compare against
2509 sub LimitCustomField {
2513 CUSTOMFIELD => undef,
2515 DESCRIPTION => undef,
2516 FIELD => 'CustomFieldValue',
2521 my $CF = RT::CustomField->new( $self->CurrentUser );
2522 if ( $args{CUSTOMFIELD} =~ /^\d+$/ ) {
2523 $CF->Load( $args{CUSTOMFIELD} );
2526 $CF->LoadByNameAndQueue(
2527 Name => $args{CUSTOMFIELD},
2528 Queue => $args{QUEUE}
2530 $args{CUSTOMFIELD} = $CF->Id;
2533 #If we are looking to compare with a null value.
2534 if ( $args{'OPERATOR'} =~ /^is$/i ) {
2535 $args{'DESCRIPTION'}
2536 ||= $self->loc( "Custom field [_1] has no value.", $CF->Name );
2538 elsif ( $args{'OPERATOR'} =~ /^is not$/i ) {
2539 $args{'DESCRIPTION'}
2540 ||= $self->loc( "Custom field [_1] has a value.", $CF->Name );
2543 # if we're not looking to compare with a null value
2545 $args{'DESCRIPTION'} ||= $self->loc( "Custom field [_1] [_2] [_3]",
2546 $CF->Name, $args{OPERATOR}, $args{VALUE} );
2551 my $qo = new RT::Queue( $self->CurrentUser );
2552 $qo->Load( $CF->Queue );
2557 @rest = ( ENTRYAGGREGATOR => 'AND' )
2558 if ( $CF->Type eq 'SelectMultiple' );
2561 VALUE => $args{VALUE},
2565 ? $q . ".{" . $CF->Name . "}"
2568 OPERATOR => $args{OPERATOR},
2573 $self->{'RecalcTicketLimits'} = 1;
2579 # {{{ sub _NextIndex
2583 Keep track of the counter for the array of restrictions
2589 return ( $self->{'restriction_index'}++ );
2596 # {{{ Core bits to make this a DBIx::SearchBuilder object
2601 $self->{'table'} = "Tickets";
2602 $self->{'RecalcTicketLimits'} = 1;
2603 $self->{'looking_at_effective_id'} = 0;
2604 $self->{'looking_at_type'} = 0;
2605 $self->{'restriction_index'} = 1;
2606 $self->{'primary_key'} = "id";
2607 delete $self->{'items_array'};
2608 delete $self->{'item_map'};
2609 delete $self->{'columns_to_display'};
2610 $self->SUPER::_Init(@_);
2621 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2622 return ( $self->SUPER::Count() );
2630 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2631 return ( $self->SUPER::CountAll() );
2636 # {{{ sub ItemsArrayRef
2638 =head2 ItemsArrayRef
2640 Returns a reference to the set of all items found in this search
2648 unless ( $self->{'items_array'} ) {
2650 my $placeholder = $self->_ItemsCounter;
2651 $self->GotoFirstItem();
2652 while ( my $item = $self->Next ) {
2653 push( @{ $self->{'items_array'} }, $item );
2655 $self->GotoItem($placeholder);
2656 $self->{'items_array'}
2657 = $self->ItemsOrderBy( $self->{'items_array'} );
2659 return ( $self->{'items_array'} );
2668 $self->_ProcessRestrictions() if ( $self->{'RecalcTicketLimits'} == 1 );
2670 my $Ticket = $self->SUPER::Next();
2671 if ( ( defined($Ticket) ) and ( ref($Ticket) ) ) {
2673 if ( $Ticket->__Value('Status') eq 'deleted'
2674 && !$self->{'allow_deleted_search'} )
2676 return ( $self->Next() );
2679 # Since Ticket could be granted with more rights instead
2680 # of being revoked, it's ok if queue rights allow
2681 # ShowTicket. It seems need another query, but we have
2682 # rights cache in Principal::HasRight.
2683 elsif ($Ticket->QueueObj->CurrentUserHasRight('ShowTicket')
2684 || $Ticket->CurrentUserHasRight('ShowTicket') )
2689 if ( $Ticket->__Value('Status') eq 'deleted' ) {
2690 return ( $self->Next() );
2693 # Since Ticket could be granted with more rights instead
2694 # of being revoked, it's ok if queue rights allow
2695 # ShowTicket. It seems need another query, but we have
2696 # rights cache in Principal::HasRight.
2697 elsif ($Ticket->QueueObj->CurrentUserHasRight('ShowTicket')
2698 || $Ticket->CurrentUserHasRight('ShowTicket') )
2703 #If the user doesn't have the right to show this ticket
2705 return ( $self->Next() );
2709 #if there never was any ticket
2720 # {{{ Deal with storing and restoring restrictions
2722 # {{{ sub LoadRestrictions
2724 =head2 LoadRestrictions
2726 LoadRestrictions takes a string which can fully populate the TicketRestrictons hash.
2727 TODO It is not yet implemented
2733 # {{{ sub DescribeRestrictions
2735 =head2 DescribeRestrictions
2738 Returns a hash keyed by restriction id.
2739 Each element of the hash is currently a one element hash that contains DESCRIPTION which
2740 is a description of the purpose of that TicketRestriction
2744 sub DescribeRestrictions {
2747 my ( $row, %listing );
2749 foreach $row ( keys %{ $self->{'TicketRestrictions'} } ) {
2750 $listing{$row} = $self->{'TicketRestrictions'}{$row}{'DESCRIPTION'};
2757 # {{{ sub RestrictionValues
2759 =head2 RestrictionValues FIELD
2761 Takes a restriction field and returns a list of values this field is restricted
2766 sub RestrictionValues {
2769 map $self->{'TicketRestrictions'}{$_}{'VALUE'}, grep {
2770 $self->{'TicketRestrictions'}{$_}{'FIELD'} eq $field
2771 && $self->{'TicketRestrictions'}{$_}{'OPERATOR'} eq "="
2773 keys %{ $self->{'TicketRestrictions'} };
2778 # {{{ sub ClearRestrictions
2780 =head2 ClearRestrictions
2782 Removes all restrictions irretrievably
2786 sub ClearRestrictions {
2788 delete $self->{'TicketRestrictions'};
2789 $self->{'looking_at_effective_id'} = 0;
2790 $self->{'looking_at_type'} = 0;
2791 $self->{'RecalcTicketLimits'} = 1;
2796 # {{{ sub DeleteRestriction
2798 =head2 DeleteRestriction
2800 Takes the row Id of a restriction (From DescribeRestrictions' output, for example.
2801 Removes that restriction from the session's limits.
2805 sub DeleteRestriction {
2808 delete $self->{'TicketRestrictions'}{$row};
2810 $self->{'RecalcTicketLimits'} = 1;
2812 #make the underlying easysearch object forget all its preconceptions
2817 # {{{ sub _RestrictionsToClauses
2819 # Convert a set of oldstyle SB Restrictions to Clauses for RQL
2821 sub _RestrictionsToClauses {
2826 foreach $row ( keys %{ $self->{'TicketRestrictions'} } ) {
2827 my $restriction = $self->{'TicketRestrictions'}{$row};
2830 #print Dumper($restriction),"\n";
2832 # We need to reimplement the subclause aggregation that SearchBuilder does.
2833 # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main',
2834 # Then SB AND's the different Subclauses together.
2836 # So, we want to group things into Subclauses, convert them to
2837 # SQL, and then join them with the appropriate DefaultEA.
2838 # Then join each subclause group with AND.
2840 my $field = $restriction->{'FIELD'};
2841 my $realfield = $field; # CustomFields fake up a fieldname, so
2842 # we need to figure that out
2845 # Rewrite LinkedTo meta field to the real field
2846 if ( $field =~ /LinkedTo/ ) {
2847 $realfield = $field = $restriction->{'TYPE'};
2851 # Handle subkey fields with a different real field
2852 if ( $field =~ /^(\w+)\./ ) {
2856 die "I don't know about $field yet"
2857 unless ( exists $FIELD_METADATA{$realfield}
2858 or $restriction->{CUSTOMFIELD} );
2860 my $type = $FIELD_METADATA{$realfield}->[0];
2861 my $op = $restriction->{'OPERATOR'};
2865 map { $restriction->{$_} } qw(VALUE TICKET BASE TARGET)
2868 # this performs the moral equivalent of defined or/dor/C<//>,
2869 # without the short circuiting.You need to use a 'defined or'
2870 # type thing instead of just checking for truth values, because
2871 # VALUE could be 0.(i.e. "false")
2873 # You could also use this, but I find it less aesthetic:
2874 # (although it does short circuit)
2875 #( defined $restriction->{'VALUE'}? $restriction->{VALUE} :
2876 # defined $restriction->{'TICKET'} ?
2877 # $restriction->{TICKET} :
2878 # defined $restriction->{'BASE'} ?
2879 # $restriction->{BASE} :
2880 # defined $restriction->{'TARGET'} ?
2881 # $restriction->{TARGET} )
2883 my $ea = $restriction->{ENTRYAGGREGATOR}
2884 || $DefaultEA{$type}
2887 die "Invalid operator $op for $field ($type)"
2888 unless exists $ea->{$op};
2892 # Each CustomField should be put into a different Clause so they
2893 # are ANDed together.
2894 if ( $restriction->{CUSTOMFIELD} ) {
2895 $realfield = $field;
2898 exists $clause{$realfield} or $clause{$realfield} = [];
2901 $field =~ s!(['"])!\\$1!g;
2902 $value =~ s!(['"])!\\$1!g;
2903 my $data = [ $ea, $type, $field, $op, $value ];
2905 # here is where we store extra data, say if it's a keyword or
2906 # something. (I.e. "TYPE SPECIFIC STUFF")
2908 #print Dumper($data);
2909 push @{ $clause{$realfield} }, $data;
2916 # {{{ sub _ProcessRestrictions
2918 =head2 _ProcessRestrictions PARAMHASH
2920 # The new _ProcessRestrictions is somewhat dependent on the SQL stuff,
2921 # but isn't quite generic enough to move into Tickets_Overlay_SQL.
2925 sub _ProcessRestrictions {
2928 #Blow away ticket aliases since we'll need to regenerate them for
2930 delete $self->{'TicketAliases'};
2931 delete $self->{'items_array'};
2932 delete $self->{'item_map'};
2933 delete $self->{'raw_rows'};
2934 delete $self->{'rows'};
2935 delete $self->{'count_all'};
2937 my $sql = $self->Query; # Violating the _SQL namespace
2938 if ( !$sql || $self->{'RecalcTicketLimits'} ) {
2940 # "Restrictions to Clauses Branch\n";
2941 my $clauseRef = eval { $self->_RestrictionsToClauses; };
2943 $RT::Logger->error( "RestrictionsToClauses: " . $@ );
2947 $sql = $self->ClausesToSQL($clauseRef);
2948 $self->FromSQL($sql) if $sql;
2952 $self->{'RecalcTicketLimits'} = 0;
2956 =head2 _BuildItemMap
2958 # Build up a map of first/last/next/prev items, so that we can display search nav quickly
2965 my $items = $self->ItemsArrayRef;
2968 delete $self->{'item_map'};
2969 if ( $items->[0] ) {
2970 $self->{'item_map'}->{'first'} = $items->[0]->EffectiveId;
2971 while ( my $item = shift @$items ) {
2972 my $id = $item->EffectiveId;
2973 $self->{'item_map'}->{$id}->{'defined'} = 1;
2974 $self->{'item_map'}->{$id}->{prev} = $prev;
2975 $self->{'item_map'}->{$id}->{next} = $items->[0]->EffectiveId
2979 $self->{'item_map'}->{'last'} = $prev;
2985 Returns an a map of all items found by this search. The map is of the form
2987 $ItemMap->{'first'} = first ticketid found
2988 $ItemMap->{'last'} = last ticketid found
2989 $ItemMap->{$id}->{prev} = the ticket id found before $id
2990 $ItemMap->{$id}->{next} = the ticket id found after $id
2996 $self->_BuildItemMap()
2997 unless ( $self->{'items_array'} and $self->{'item_map'} );
2998 return ( $self->{'item_map'} );
3013 =head2 PrepForSerialization
3015 You don't want to serialize a big tickets object, as the {items} hash will be instantly invalid _and_ eat lots of space
3019 sub PrepForSerialization {
3021 delete $self->{'items'};
3022 $self->RedoSearch();
3027 RT::Tickets supports several flags which alter search behavior:
3030 allow_deleted_search (Otherwise never show deleted tickets in search results)
3031 looking_at_type (otherwise limit to type=ticket)
3033 These flags are set by calling
3035 $tickets->{'flagname'} = 1;
3037 BUG: There should be an API for this
3043 # We assume that we've got some tickets hanging around from before.
3044 ok( my $unlimittickets = RT::Tickets->new( $RT::SystemUser ) );
3045 ok( $unlimittickets->UnLimit );
3046 ok( $unlimittickets->Count > 0, "UnLimited tickets object should return tickets" );