From 2f3b4161da680cd0fff8c91dbdc50fe5e974610e Mon Sep 17 00:00:00 2001
From: jeff <jeff>
Date: Mon, 24 Sep 2007 15:47:52 +0000
Subject: [PATCH] self-service support usage improvements (1733)

---
 FS/FS/ClientAPI/MyAccount.pm                       | 75 +++++++++++++++-----
 FS/FS/Schema.pm                                    |  1 +
 FS/FS/TicketSystem/RT_External.pm                  | 48 +++++++++++++
 FS/FS/acct_rt_transaction.pm                       | 53 +++++++++++++-
 fs_selfservice/FS-SelfService/SelfService.pm       |  1 +
 fs_selfservice/FS-SelfService/cgi/myaccount.html   | 25 ++++++-
 fs_selfservice/FS-SelfService/cgi/selfservice.cgi  | 14 +++-
 .../FS-SelfService/cgi/view_support_details.html   | 80 ++++++++++++++++++++++
 8 files changed, 273 insertions(+), 24 deletions(-)
 create mode 100644 fs_selfservice/FS-SelfService/cgi/view_support_details.html

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
diff --git a/fs_selfservice/FS-SelfService/SelfService.pm b/fs_selfservice/FS-SelfService/SelfService.pm
index fe17fae5d..32497d90b 100644
--- a/fs_selfservice/FS-SelfService/SelfService.pm
+++ b/fs_selfservice/FS-SelfService/SelfService.pm
@@ -38,6 +38,7 @@ $socket .= '.'.$tag if defined $tag && length($tag);
   'list_pkgs'            => 'MyAccount/list_pkgs',     #add to ss cgi (added?)
   'list_svcs'            => 'MyAccount/list_svcs',     #add to ss cgi (added?)
   'list_svc_usage'       => 'MyAccount/list_svc_usage',   
+  'list_support_usage'   => 'MyAccount/list_support_usage',   
   'order_pkg'            => 'MyAccount/order_pkg',     #add to ss cgi!
   'change_pkg'           => 'MyAccount/change_pkg', 
   'order_recharge'       => 'MyAccount/order_recharge',
diff --git a/fs_selfservice/FS-SelfService/cgi/myaccount.html b/fs_selfservice/FS-SelfService/cgi/myaccount.html
index 421a866f1..65b98427d 100644
--- a/fs_selfservice/FS-SelfService/cgi/myaccount.html
+++ b/fs_selfservice/FS-SelfService/cgi/myaccount.html
@@ -37,10 +37,29 @@ Hello <%= $name %>!<BR><BR>
 %>
 
 <%=
-  if ( defined($support_time) ) {
+  if ( @support_services ) {
     $OUT .= '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=2 BGCOLOR="#eeeeee">'.
-            '<TR><TH BGCOLOR="#ff6666">Support Time Remaining</TH></TR>'.
-            "<TR><TD>$support_time</TD></TR></TABLE><BR>";
+            '<TR><TH BGCOLOR="#ff6666" COLSPAN="3">Support Time Remaining</TH>'.
+            '</TR><TR><TH ALIGN="left">#</TH><TH>Package</TH>'.
+            '<TH>Time Remaining</TH></TR>';
+    my $col1 = "ffffff";
+    my $col2 = "dddddd";
+    my $col = $col1;
+
+    foreach my $support ( @support_services ) {
+      my $td = qq!<TD BGCOLOR="#$col">!;
+      my $a = qq!<A HREF="${url}view_support_details;svcnum=!.
+              $support->{'svcnum'}. '">';
+      $OUT .=
+        "<TR>$td$a". $support->{'pkgnum'}. "</A></TD>".
+        $td.$a. $support->{'pkg'}. "</A></TD>".
+        $td.$a. $support->{'time'}. "</A></TD>".
+        '</TR>';
+      $col = $col eq $col1 ? $col2 : $col1;
+    }
+    $OUT .= '</TABLE><BR>';
+  } else {
+    $OUT .= '';
   }
 %>
 
diff --git a/fs_selfservice/FS-SelfService/cgi/selfservice.cgi b/fs_selfservice/FS-SelfService/cgi/selfservice.cgi
index 591112447..9cf8c4b1e 100644
--- a/fs_selfservice/FS-SelfService/cgi/selfservice.cgi
+++ b/fs_selfservice/FS-SelfService/cgi/selfservice.cgi
@@ -15,7 +15,8 @@ use FS::SelfService qw( login customer_info invoice
                         list_pkgs order_pkg signup_info order_recharge
                         part_svc_info provision_acct provision_external
                         unprovision_svc change_pkg
-                        list_svcs list_svc_usage myaccount_passwd
+                        list_svcs list_svc_usage list_support_usage
+                        myaccount_passwd
                       );
 
 $template_dir = '.';
@@ -67,7 +68,7 @@ $session_id = $cgi->param('session');
 
 #order|pw_list XXX ???
 $cgi->param('action') =~
-    /^(myaccount|view_invoice|make_payment|make_ach_payment|payment_results|ach_payment_results|recharge_prepay|recharge_results|logout|change_bill|change_ship|customer_order_pkg|process_order_pkg|customer_change_pkg|process_change_pkg|process_order_recharge|provision|provision_svc|process_svc_acct|process_svc_external|delete_svc|view_usage|view_usage_details|change_password|process_change_password)$/
+    /^(myaccount|view_invoice|make_payment|make_ach_payment|payment_results|ach_payment_results|recharge_prepay|recharge_results|logout|change_bill|change_ship|customer_order_pkg|process_order_pkg|customer_change_pkg|process_change_pkg|process_order_recharge|provision|provision_svc|process_svc_acct|process_svc_external|delete_svc|view_usage|view_usage_details|view_support_details|change_password|process_change_password)$/
   or die "unknown action ". $cgi->param('action');
 my $action = $1;
 
@@ -490,6 +491,15 @@ sub view_usage_details {
   );
 }
 
+sub view_support_details {
+  list_support_usage(
+    'session_id'  => $session_id,
+    'svcnum'      => $cgi->param('svcnum'),
+    'beginning'   => $cgi->param('beginning') || '',
+    'ending'      => $cgi->param('ending') || '',
+  );
+}
+
 sub change_password {
   list_svcs(
     'session_id' => $session_id,
diff --git a/fs_selfservice/FS-SelfService/cgi/view_support_details.html b/fs_selfservice/FS-SelfService/cgi/view_support_details.html
new file mode 100644
index 000000000..b3d5b13f8
--- /dev/null
+++ b/fs_selfservice/FS-SelfService/cgi/view_support_details.html
@@ -0,0 +1,80 @@
+<HTML><HEAD><TITLE>MyAccount</TITLE></HEAD>
+<BODY BGCOLOR="#eeeeee"><FONT SIZE=5>MyAccount</FONT><BR><BR>
+<%= $url = "$selfurl?session=$session_id;action="; ''; %>
+<%= include('myaccount_menu') %>
+<TD VALIGN="top">
+
+<FONT SIZE=4>Support usage details for
+<%= Date::Format::time2str('%b&nbsp;%o&nbsp;%Y', $beginning) %> -
+<%= Date::Format::time2str('%b&nbsp;%o&nbsp;%Y', $ending) %>
+</FONT><BR><BR>
+
+<%= if ( $error ) {
+  $OUT .= qq!<FONT SIZE="+1" COLOR="#ff0000">$error</FONT><BR><BR>!;
+} ''; %>
+ 
+<TABLE WIDTH="100%">
+  <TR>
+    <TD WIDTH="50%">
+<%= if ($previous < $beginning) {
+    $OUT .= qq!<A HREF="${url}view_support_details;svcnum=$svcnum;beginning=!;
+    $OUT .= qq!$previous;ending=$beginning">Previous period</A>!;
+    }else{
+      '';
+    } %>
+    </TD>
+    <TD  WIDTH="50%" ALIGN="right">
+<%= if ($next > $ending) {
+    $OUT .= qq!<A HREF="${url}view_support_details;svcnum=$svcnum;beginning=!;
+    $OUT .= qq!$ending;ending=$next">Next period</A>!;
+    }else{
+      '';
+    }%>
+    </TD>
+  </TR>
+</TABLE>
+<TABLE BGCOLOR="#cccccc">
+  <TR>
+    <TH ALIGN="left">Ticket</TH>
+    <TH ALIGN="right">Subject</TH>
+    <TH ALIGN="right">Staff</TH>
+    <TH ALIGN="left">Date</TH>
+    <TH ALIGN="right">Status</TH>
+    <TH ALIGN="right">Time</TH>
+  </TR>
+<%= my $total = 0;
+    foreach my $usage ( @usage ) {
+  $OUT .= '<TR><TD ALIGN="left">';
+    $OUT .= $usage->{'ticketid'};
+    $OUT .= '</TD><TD ALIGN="right">';
+    $OUT .= $usage->{'subject'};
+    $OUT .= '</TD><TD ALIGN="right">';
+    $OUT .= $usage->{'creator'};
+    $OUT .= '</TD><TD ALIGN="left">';
+    $OUT .= Date::Format::time2str('%T%P %a&nbsp;%b&nbsp;%o&nbsp;%Y', $usage->{'_date'});
+    $OUT .= '</TD><TD ALIGN="right">';
+    $OUT .= $usage->{'status'};
+    $OUT .= '</TD><TD ALIGN="right">';
+    my $duration =  $usage->{'support'};
+    $total += $usage->{'support'};
+    my $h = int($duration/3600);
+    my $m = sprintf("%02d", int(($duration % 3600) / 60));
+    my $s = sprintf("%02d", $duration % 60);
+    $OUT .=  $usage->{'support'} < 0 ? '-' : '';
+    $OUT .=  "$h:$m:$s";
+  $OUT .= '</TD></TR>';
+  }
+  my $h = int($total/3600);
+  my $m = sprintf("%02d", int(($total % 3600) / 60));
+  my $s = sprintf("%02d", $total % 60);
+  $OUT .=  qq!<TR><TD COLSPAN="5"></TD><TD ALIGN="right"><HR></TD></TR>!;
+  $OUT .=  qq!<TR><TD COLSPAN="5"></TD><TD ALIGN="right">$h:$m:$s</TD></TR>!;
+  %>
+
+</TABLE>
+<BR>
+
+</TD></TR></TABLE>
+<HR>
+<FONT SIZE="-2">powered by <a href="http://www.sisd.com/freeside">freeside</a></FONT>
+</BODY></HTML>
-- 
2.11.0