X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FTicketSystem%2FRT_External.pm;h=5c51bdd020c254d26ecd7502f5f20a0e9831c6b3;hb=9e5512eacf9868dfaef17c4ce7112d06f768a525;hp=c788c03a19df0a5791a9236917ffed1e71de39c4;hpb=70569273833d91085c6c562865a78a08ddde49a1;p=freeside.git

diff --git a/FS/FS/TicketSystem/RT_External.pm b/FS/FS/TicketSystem/RT_External.pm
index c788c03a1..5c51bdd02 100644
--- a/FS/FS/TicketSystem/RT_External.pm
+++ b/FS/FS/TicketSystem/RT_External.pm
@@ -9,6 +9,7 @@ use URI::Escape;
 use FS::UID qw(dbh);
 use FS::Record qw(qsearchs);
 use FS::cust_main;
+use Carp qw(cluck);
 
 $me = '[FS::TicketSystem::RT_External]';
 $DEBUG = 0;
@@ -96,6 +97,31 @@ sub customer_tickets {
 
 }
 
+sub comments_on_tickets {
+  my ($self, $custnum, $limit, $time ) = @_;
+  $limit ||= 0;
+
+  my( $from_sql, @param) = $self->_from_customer( $custnum );
+  my $sql = qq{
+    SELECT transactions.*, Attachments.content, Tickets.subject
+    FROM transactions
+      JOIN Attachments ON( Attachments.transactionid = transactions.id )
+      JOIN Tickets ON ( Tickets.id = transactions.objectid )
+      JOIN Links  ON ( Tickets.id    = Links.LocalBase
+                       AND Links.Base LIKE '%/ticket/' || Tickets.id )
+       
+
+    WHERE ( Status = 'new' OR Status = 'open' OR Status = 'stalled' )
+      AND Target = 'freeside://freeside/cust_main/$custnum'
+       AND transactions.type = 'Comment'
+       AND transactions.created >= (SELECT TIMESTAMP WITH TIME ZONE 'epoch' + $time * INTERVAL '1 second')
+     LIMIT $limit
+  };
+  cluck $sql if $DEBUG > 0;
+  #AND created > 
+  $dbh->selectall_arrayref( $sql, { Slice => {} } ) or die $dbh->errstr . " $sql";
+}
+
 sub _from_customer {
   my( $self, $custnum, $priority ) = @_;
 
@@ -156,14 +182,16 @@ sub _from_customer {
   }
 
   my $sql = "
-                    FROM Tickets
-                    JOIN Queues ON ( Tickets.Queue = Queues.id       )
-                    JOIN Links  ON ( Tickets.id    = Links.LocalBase )
-                    JOIN Users  ON ( Tickets.Owner = Users.id        )
-                    $join 
-       WHERE ( ". join(' OR ', map "Status = '$_'", $self->statuses ). " )
-         AND Target = 'freeside://freeside/cust_main/$custnum'
-         $where
+    FROM Tickets
+      JOIN Queues ON ( Tickets.Queue = Queues.id )
+      JOIN Users  ON ( Tickets.Owner = Users.id  )
+      JOIN Links  ON ( Tickets.id    = Links.LocalBase
+                       AND Links.Base LIKE '%/ticket/' || Tickets.id )
+      $join 
+
+    WHERE ( ". join(' OR ', map "Status = '$_'", $self->statuses ). " )
+      AND Target = 'freeside://freeside/cust_main/$custnum'
+      $where
   ";
 
   ( $sql, @param );