self-service support usage improvements (1733)

4 files changed, 158 insertions, 19 deletions

diff --git a/FS/FS/ClientAPI/MyAccount.pm b/FS/FS/ClientAPI/MyAccount.pm
index 4d24a5263..592b4940f 100644
--- a/FS/FS/ClientAPI/MyAccount.pm
+++ b/FS/FS/ClientAPI/MyAccount.pm
@@ -24,6 +24,7 @@ use FS::cust_bill;
 use FS::cust_main_county;
 use FS::cust_pkg;
 use FS::payby;
+use FS::acct_rt_transaction;
 use HTML::Entities;
 
 #false laziness with FS::cust_main
@@ -152,11 +153,24 @@ sub customer_info {
       0 < ( grep { $_ eq 'POST' } $cust_main->invoicing_list );
 
     if (scalar($conf->config('support_packages'))) {
-      my $support = 0;
+      my @support_services = ();
       foreach ($cust_main->support_services) {
-        $support += $_->svc_x->seconds;
+        my $seconds = $_->svc_x->seconds;
+        my $time_remaining = (($seconds < 0) ? '-' : '' ).
+                             int(abs($seconds)/3600)."h".
+                             sprintf("%02d",(abs($seconds)%3600)/60)."m";
+        my $cust_pkg = $_->cust_pkg;
+        my $pkgnum = '';
+        my $pkg = '';
+        $pkgnum = $cust_pkg->pkgnum if $cust_pkg;
+        $pkg = $cust_pkg->part_pkg->pkg if $cust_pkg;
+        push @support_services, { svcnum => $_->svcnum,
+                                  time => $time_remaining,
+                                  pkgnum => $pkgnum,
+                                  pkg => $pkg,
+                                };
       }
-      $return{support_time} = (($support < 0) ? '-' : '' ). int(abs($support)/3600)."h".sprintf("%02d",(abs($support)%3600)/60)."m";
+      $return{support_services} = \@support_services;
     }
 
   } elsif ( $session->{'svcnum'} ) { #no customer record
@@ -642,8 +656,48 @@ sub list_svcs {
 
 }
 
+sub _list_svc_usage {
+  my($svc_acct, $begin, $end) = @_;
+  my @usage = ();
+  foreach my $part_export ( 
+    map { qsearch ( 'part_export', { 'exporttype' => $_ } ) }
+    qw (sqlradius sqlradius_withdomain')
+  ) {
+
+    push @usage, @ { $part_export->usage_sessions($begin, $end, $svc_acct) };
+  }
+  (@usage);
+}
+
 sub list_svc_usage {
-  my $p = shift;
+  _usage_details(\&_list_svc_usage, @_);
+}
+
+sub _list_support_usage {
+  my($svc_acct, $begin, $end) = @_;
+  my @usage = ();
+  foreach ( grep { $begin <= $_->_date && $_->_date <= $end }
+            qsearch('acct_rt_transaction', { 'svcnum' => $svc_acct->svcnum })
+          ) {
+    push @usage, { 'seconds'  => $_->seconds,
+                   'support'  => $_->support,
+                   '_date'    => $_->_date,
+                   'id'       => $_->transaction_id,
+                   'creator'  => $_->creator,
+                   'subject'  => $_->subject,
+                   'status'   => $_->status,
+                   'ticketid' => $_->ticketid,
+                 };
+  }
+  (@usage);
+}
+
+sub list_support_usage {
+  _usage_details(\&_list_support_usage, @_);
+}
+
+sub _usage_details {
+  my ($callback, $p) = (shift,shift);
 
   my($context, $session, $custnum) = _custoragent_session_custnum($p);
   return { 'error' => $session } if $context eq 'error';
@@ -663,18 +717,8 @@ sub list_svc_usage {
     $p->{beginning} = $svc_acct->cust_svc->cust_pkg->last_bill;
     $p->{ending} = $end;
   }
-  my @usage = ();
 
-  foreach my $part_export ( 
-    map { qsearch ( 'part_export', { 'exporttype' => $_ } ) }
-    qw (sqlradius sqlradius_withdomain')
-  ) {
-
-    push @usage, @ { $part_export->usage_sessions($p->{beginning},
-                                                  $p->{ending},
-                                                  $svc_acct)
-                   };
-  }
+  my (@usage) = &$callback($svc_acct,$p->{beginning},$p->{ending});
 
   #kinda false laziness with FS::cust_main::bill, but perhaps
   #we should really change this bit to DateTime and DateTime::Duration
@@ -710,7 +754,6 @@ sub list_svc_usage {
   my $previous  = timelocal_nocheck($psec,$pmin,$phour,$pmday,$pmon,$pyear);
   my $next      = timelocal_nocheck($nsec,$nmin,$nhour,$nmday,$nmon,$nyear);
 
-
   { 
     'error'     => '',
     'svcnum'    => $p->{svcnum},
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 1ddaca0eb..3084e3625 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -997,6 +997,7 @@ sub tables_hashref {
         'transaction_id',       'int', '',   '', '', '', 
         '_date',   @date_type, '', '',
         'seconds',   'int', '',   '', '', '', #uhhhh
+        'support',   'int', '',   '', '', '',
       ],
       'primary_key' => 'svcrtid',
       'unique' => [],
diff --git a/FS/FS/TicketSystem/RT_External.pm b/FS/FS/TicketSystem/RT_External.pm
index ea9c2b5a9..cd6834c96 100644
--- a/FS/FS/TicketSystem/RT_External.pm
+++ b/FS/FS/TicketSystem/RT_External.pm
@@ -280,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;
 
diff --git a/FS/FS/acct_rt_transaction.pm b/FS/FS/acct_rt_transaction.pm
index af959e47f..93ce28044 100644
--- a/FS/FS/acct_rt_transaction.pm
+++ b/FS/FS/acct_rt_transaction.pm
@@ -39,7 +39,9 @@ FS::Record.  The following fields are currently supported:
 
 =item transaction_id -  the id of the rt transtaction from which the time applies
 
-=item seconds - the amount of time which applies
+=item seconds - the amount of time applied from tickets
+
+=item support - the amount of time applied to support services
 
 
 =back
@@ -92,7 +94,7 @@ sub insert {
     return "Can't find svc_acct " . $self->svcnum;
   }
 
-  my $error = $svc_acct->decrement_seconds($self->seconds);
+  $error = $svc_acct->decrement_seconds($self->support);
   if ( $error ) {
     $dbh->rollback if $oldAutoCommit;
     return "Error incrementing service seconds: $error";
@@ -136,7 +138,7 @@ sub delete {
     return "Can't find svc_acct " . $self->svcnum;
   }
 
-  my $error = $svc_acct->increment_seconds($self->seconds);
+  $error = $svc_acct->increment_seconds($self->support);
   if ( $error ) {
     $dbh->rollback if $oldAutoCommit;
     return "Error incrementing service seconds: $error";
@@ -173,6 +175,7 @@ sub check {
     || $self->ut_number('transaction_id')
     || $self->ut_numbern('_date')
     || $self->ut_snumber('seconds')
+    || $self->ut_snumber('support')
   ;
   return $error if $error;
 
@@ -196,6 +199,50 @@ sub check {
   $self->SUPER::check;
 }
 
+=item creator
+
+Returns the creator of the RT transaction associated with this object.
+
+=cut
+
+sub creator {
+  my $self = shift;
+  FS::TicketSystem->transaction_creator($self->transaction_id);
+}
+
+=item ticketid
+
+Returns the number of the RT ticket associated with this object.
+
+=cut
+
+sub ticketid {
+  my $self = shift;
+  FS::TicketSystem->transaction_ticketid($self->transaction_id);
+}
+
+=item subject
+
+Returns the subject of the RT ticket associated with this object.
+
+=cut
+
+sub subject {
+  my $self = shift;
+  FS::TicketSystem->transaction_subject($self->transaction_id);
+}
+
+=item status
+
+Returns the status of the RT ticket associated with this object.
+
+=cut
+
+sub status {
+  my $self = shift;
+  FS::TicketSystem->transaction_status($self->transaction_id);
+}
+
 =item batch_insert SVC_ACCT_RT_TRANSACTION_OBJECT, ...
 
 Class method which inserts multiple time applications.  Takes a list of