[rt-devel] Question about reports

[Senoner Samuel]

Now I got it!!!!!!
 
I send it to your for your interest.
I copied limit watcher and changed a little bit and created LimitUserComments.
With LIKE as operator instead of = you can search a part of it
here the diff, for me it seems to work, but I have to rewrite the statistic site, because i get all results x2
 
--- Tickets_Overlay.pm.17122003 2003-12-17 13:13:58.000000000 +0100
+++ Tickets_Overlay.pm  2003-12-17 13:12:39.000000000 +0100
@@ -103,6 +103,7 @@
     Requestor       => ['WATCHERFIELD' => 'Requestor',],
     CC              => ['WATCHERFIELD' => 'Cc',],
     AdminCC         => ['WATCHERFIELD' => 'AdminCC',],
+    UserComments    => ['USERCOMMENTS'],
     Watcher        => ['WATCHERFIELD'],
     LinkedTo       => ['LINKFIELD',],
     CustomFieldValue =>['CUSTOMFIELD',],
@@ -119,6 +120,7 @@
     TRANSFIELD     => \&_TransLimit,
     TRANSDATE      => \&_TransDateLimit,
     WATCHERFIELD    => \&_WatcherLimit,
+    USERCOMMENTS    => \&_UserCommentsLimit,
     LINKFIELD      => \&_LinkFieldLimit,
     CUSTOMFIELD    => \&_CustomFieldLimit,
   );
@@ -590,6 +592,78 @@
 
 }
 
+
+
+sub _UserCommentsLimit {
+  my ($self,$field,$op,$value, at rest) = @_;
+  my %rest = @rest;
+
+  $self->_OpenParen;
+
+  my $groups        = $self->NewAlias('Groups');
+  my $group_princs  = $self->NewAlias('Principals');
+  my $groupmembers  = $self->NewAlias('CachedGroupMembers');
+  my $member_princs = $self->NewAlias('Principals');
+  my $users         = $self->NewAlias('Users');
+
+
+  #Find user watchers
+#  my $subclause = undef;
+#  my $aggregator = 'OR';
+#  if ($restriction->{'OPERATOR'} =~ /!|NOT/i ){
+#    $subclause = 'AndEmailIsNot';
+#    $aggregator = 'AND';
+#  }
+
+
+  $self->_SQLLimit(ALIAS => $users,
+                   FIELD => $rest{SUBKEY} || 'Comments',
+                   VALUE           => $value,
+                   OPERATOR        => $op,
+                   CASESENSITIVE   => 0,
+                   @rest,
+                  );
+
+  # {{{ Tie to groups for tickets we care about
+  $self->_SQLLimit(ALIAS => $groups,
+                   FIELD => 'Domain',
+                   VALUE => 'RT::Ticket-Role',
+                   ENTRYAGGREGATOR => 'AND');
+
+  $self->Join(ALIAS1 => $groups, FIELD1 => 'Instance',
+              ALIAS2 => 'main',   FIELD2 => 'id');
+  # }}}
+
+  # If we care about which sort of watcher
+  my $meta = $FIELDS{$field};
+  my $type = ( defined $meta->[1] ? $meta->[1] : undef );
+
+  if ( $type ) {
+    $self->_SQLLimit(ALIAS => $groups,
+                     FIELD => 'Type',
+                     VALUE => $type,
+                     ENTRYAGGREGATOR => 'AND');
+  }
+
+  $self->Join (ALIAS1 => $groups,  FIELD1 => 'id',
+               ALIAS2 => $group_princs, FIELD2 => 'ObjectId');
+  $self->_SQLLimit(ALIAS => $group_princs,
+                   FIELD => 'PrincipalType',
+                   VALUE => 'Group',
+                   ENTRYAGGREGATOR => 'AND');
+  $self->Join( ALIAS1 => $group_princs, FIELD1 => 'id',
+               ALIAS2 => $groupmembers, FIELD2 => 'GroupId');
+  $self->Join( ALIAS1 => $groupmembers, FIELD1 => 'MemberId',
+               ALIAS2 => $member_princs, FIELD2 => 'id');
+  $self->Join (ALIAS1 => $member_princs, FIELD1 => 'ObjectId',
+               ALIAS2 => $users, FIELD2 => 'id');
+
+ $self->_CloseParen;
+
+}
+
+
 sub _LinkFieldLimit {
   my $restriction;
   my $self;
@@ -1307,6 +1381,32 @@
 
 }
 
+sub LimitUserComments {
+    my $self = shift;
+    my %args = ( OPERATOR => '=',
+                 VALUE => undef,
+                 TYPE => undef,
+               @_);
+
+    $RT::Logger->debug("we are searching for $args{'VALUE'} and operator $args{'OPERATOR'} also type $args{'TYPE'}");
+    #build us up a description
+#    my ($watcher_type, $desc);
+#    if ($args{'TYPE'}) {
+#        $watcher_type = $args{'TYPE'};
+#    }
+#    else {
+#        $watcher_type = "Watcher";
+#    }
+
+    $self->Limit (FIELD => 'UserComments',
+                  VALUE => $args{'VALUE'},
+                  OPERATOR => $args{'OPERATOR'},
+                  TYPE => $args{'TYPE'},
+                  DESCRIPTION => join(
+                   ' ', $self->loc('UserComments'), $args{'OPERATOR'}, $args{'VALUE'},
+                  ),
+                 );
+}
 # }}}
 

Samuel

________________________________

From: Senoner Samuel 
Sent: Tuesday,16 December,2003 16:59
To: rt-devel at lists.fsck.com
Subject: [rt-devel] Question about reports


I want to make a report or a statistic on who creates most tickets. All my users have a commenet filed which begins with his role, for example student faculty of XXX, stuent faculty of YYY, professor faculty of XXX, Secretary faculty of XXX.
Now I want to know which group of users creates the most tickets. I searches in Tickets Overlay where I can limit results by Watchers, in special type the requestor.
I tried using a SQL gui to get an idea of the structure. When i make limitwatcher with the requestor, I use the requestors mail address, which is a field in the users table, so it should be the same to have a search by comments, and wildcards shouldn't be harder......
How can I start a limit search, or create one that filters the comment instead of the emailaddress, I tried to grep by emailaddress, but haven't find where it is coded......
 
Thanks for any help
 
Samuel