self-service support usage improvements (1733)

[freeside.git] / FS / FS / TicketSystem / RT_External.pm

diff --git a/FS/FS/TicketSystem/RT_External.pm b/FS/FS/TicketSystem/RT_External.pm
index 7dde862..cd6834c 100644 (file)
--- a/FS/FS/TicketSystem/RT_External.pm
+++ b/FS/FS/TicketSystem/RT_External.pm
@@ -1,14 +1,17 @@
 package FS::TicketSystem::RT_External;
 
 use strict;
-use vars qw( $conf $default_queueid
+use vars qw( $DEBUG $me $conf $dbh $default_queueid $external_url
              $priority_field $priority_field_queue $field
-            $dbh $external_url );
+          );
 use URI::Escape;
 use FS::UID qw(dbh);
 use FS::Record qw(qsearchs);
 use FS::cust_main;
 
+$me = '[FS::TicketSystem::RT_External]';
+$DEBUG = 0;
+
 FS::UID->install_callback( sub { 
   $conf = new FS::Conf;
   $default_queueid = $conf->config('ticket_system-default_queueid');
@@ -17,6 +20,7 @@ FS::UID->install_callback( sub {
   if ( $priority_field ) {
     $priority_field_queue =
       $conf->config('ticket_system-custom_priority_field_queue');
+
     $field = $priority_field_queue
                   ? $priority_field_queue. '.%7B'. $priority_field. '%7D'
                   : $priority_field;
@@ -35,6 +39,17 @@ FS::UID->install_callback( sub {
     $external_url = $conf->config('ticket_system-rt_external_url');
   }
 
+  #kludge... should *use* the id... but good enough for now
+  if ( $priority_field_queue =~ /^(\d+)$/ ) {
+    my $id = $1;
+    my $sql = 'SELECT Name FROM Queues WHERE Id = ?';
+    my $sth = $dbh->prepare($sql) or die $dbh->errstr. " preparing $sql";
+    $sth->execute($id)            or die $sth->errstr. " executing $sql";
+
+    $priority_field_queue = $sth->fetchrow_arrayref->[0];
+
+  }
+
 } );
 
 sub num_customer_tickets {
@@ -43,6 +58,7 @@ sub num_customer_tickets {
   my( $from_sql, @param) = $self->_from_customer( $custnum, $priority );
 
   my $sql = "SELECT COUNT(*) $from_sql";
+  warn "$me $sql (@param)" if $DEBUG;
   my $sth = $dbh->prepare($sql) or die $dbh->errstr. " preparing $sql";
   $sth->execute(@param)         or die $sth->errstr. " executing $sql";
 
@@ -55,9 +71,12 @@ sub customer_tickets {
   $limit ||= 0;
 
   my( $from_sql, @param) = $self->_from_customer( $custnum, $priority );
-  my $sql = "SELECT tickets.*, queues.name".
-            ( length($priority) ? ", objectcustomfieldvalues.content" : '' ).
-            " $from_sql ORDER BY priority, id DESC LIMIT $limit";
+  my $sql="SELECT tickets.*, queues.name, ".
+          "position(tickets.status in 'newopenstalledresolvedrejecteddeleted')".
+         " AS svalue " .
+          ( length($priority) ? ", objectcustomfieldvalues.content" : '' ).
+          " $from_sql ORDER BY svalue, priority DESC, id DESC LIMIT $limit";
+  warn "$me $sql (@param)" if $DEBUG;
   my $sth = $dbh->prepare($sql) or die $dbh->errstr. "preparing $sql";
   $sth->execute(@param)         or die $sth->errstr. "executing $sql";
 
@@ -251,7 +270,8 @@ sub queue {
   my $sth = $dbh->prepare($sql) or die $dbh->errstr. " preparing $sql";
   $sth->execute($queueid)       or die $sth->errstr. " executing $sql";
 
-  $sth->fetchrow_arrayref->[0];
+  my $rows = $sth->fetchrow_arrayref;
+  $rows ? $rows->[0] : '';
 
 }
 
@@ -260,5 +280,53 @@ sub baseurl {
   $external_url;
 }
 
+sub _retrieve_single_value {
+  my( $self, $sql ) = @_;
+
+  warn "$me $sql" if $DEBUG;
+  my $sth = $dbh->prepare($sql) or die $dbh->errstr. "preparing $sql";
+  $sth->execute                 or die $sth->errstr. "executing $sql";
+
+  my $arrayref = $sth->fetchrow_arrayref;
+  $arrayref ? $arrayref->[0] : $arrayref;
+}
+
+sub transaction_creator {
+  my( $self, $transaction_id ) = @_;
+
+  my $sql = "SELECT name from transactions JOIN users ON ".
+            "transactions.creator=users.id WHERE transactions.id = ".
+            $transaction_id;
+
+  $self->_retrieve_single_value($sql);
+}
+
+sub transaction_ticketid {
+  my( $self, $transaction_id ) = @_;
+
+  my $sql = "SELECT objectid from transactions WHERE transactions.id = ".
+            $transaction_id;
+  
+  $self->_retrieve_single_value($sql);
+}
+
+sub transaction_subject {
+  my( $self, $transaction_id ) = @_;
+
+  my $sql = "SELECT subject from transactions JOIN tickets ON objectid=".
+            "tickets.id WHERE transactions.id = ".  $transaction_id;
+  
+  $self->_retrieve_single_value($sql);
+}
+
+sub transaction_status {
+  my( $self, $transaction_id ) = @_;
+
+  my $sql = "SELECT status from transactions JOIN tickets ON objectid=".
+            "tickets.id WHERE transactions.id = ".  $transaction_id;
+  
+  $self->_retrieve_single_value($sql);
+}
+
 1;