From dbbe3098fc8fdd0de397c753b7814deb523d48dd Mon Sep 17 00:00:00 2001
From: ivan <ivan>
Date: Wed, 26 Aug 2009 11:22:01 +0000
Subject: [PATCH] first pass at balance reporting, RT#5496

---
 httemplate/search/report_svc_phone.html | 32 ++++++++++++++++
 httemplate/search/svc_phone.cgi         | 66 ++++++++++++++++++++++++++++++---
 2 files changed, 93 insertions(+), 5 deletions(-)
 create mode 100644 httemplate/search/report_svc_phone.html

diff --git a/httemplate/search/report_svc_phone.html b/httemplate/search/report_svc_phone.html
new file mode 100644
index 000000000..2d643405d
--- /dev/null
+++ b/httemplate/search/report_svc_phone.html
@@ -0,0 +1,32 @@
+<% include('/elements/header.html', 'Phone number total usage' ) %>
+
+<FORM ACTION="svc_phone.cgi" METHOD="GET">
+
+<INPUT TYPE="hidden" NAME="magic"       VALUE="all">
+<INPUT TYPE="hidden" NAME="usage_total" VALUE="1">
+
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+%#  <TR>
+%#    <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left">
+%#     <FONT SIZE="+1">Search options</FONT>
+%#    </TH>
+%#  </TR>
+
+  <% include ( '/elements/tr-input-beginning_ending.html', prefix=>'usage' ) %>
+
+</TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="Search phone numbers">
+
+</FORM>
+
+<% include('/elements/footer.html') %>
+<%init>
+
+#? 'List services' ?  something new?
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('List rating data');
+
+</%init>
diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi
index 49340c6c3..dccc6742b 100644
--- a/httemplate/search/svc_phone.cgi
+++ b/httemplate/search/svc_phone.cgi
@@ -3,33 +3,39 @@
                  'name'              => 'phone numbers',
                  'query'             => $sql_query,
                  'count_query'       => $count_query,
-                 'redirect'          => $link,
+                 'redirect'          => $redirect,
                  'header'            => [ '#',
                                           'Service',
                                           'Country code',
                                           'Phone number',
+                                          @header,
                                           FS::UI::Web::cust_header(),
                                         ],
                  'fields'            => [ 'svcnum',
                                           'svc',
                                           'countrycode',
                                           'phonenum',
+                                          @fields,
                                           \&FS::UI::Web::cust_fields,
                                         ],
                  'links'             => [ $link,
                                           $link,
                                           $link,
                                           $link,
+                                          ( map '', @header ),
                                           ( map { $_ ne 'Cust. Status' ? $link_cust : '' }
                                                 FS::UI::Web::cust_header()
                                           ),
                                         ],
-                 'align' => 'rlrr'. FS::UI::Web::cust_aligns(),
+                 'align' => 'rlrr'.
+                            join('', map 'r', @header).
+                            FS::UI::Web::cust_aligns(),
                  'color' => [ 
                               '',
                               '',
                               '',
                               '',
+                              ( map '', @header ),
                               FS::UI::Web::cust_colors(),
                             ],
                  'style' => [ 
@@ -37,6 +43,7 @@
                               '',
                               '',
                               '',
+                              ( map '', @header ),
                               FS::UI::Web::cust_styles(),
                             ],
               )
@@ -48,9 +55,16 @@ die "access denied"
 
 my $conf = new FS::Conf;
 
-my $orderby = 'ORDER BY svcnum';
+my @select = ();
 my %svc_phone = ();
 my @extra_sql = ();
+my $orderby = 'ORDER BY svcnum';
+
+my @header = ();
+my @fields = ();
+my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ];
+my $redirect = $link;
+
 if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
   push @extra_sql, 'pkgnum IS NULL'
@@ -61,6 +75,50 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
     $orderby = "ORDER BY $sortby";
   }
 
+  if ( $cgi->param('usage_total') ) {
+
+    my($beginning,$ending) = FS::UI::Web::parse_beginning_ending($cgi, 'usage');
+
+    $redirect = '';
+
+    my $and_date = " AND startdate >= $beginning ".
+                   " AND startdate <= $ending ";
+
+    my $fromwhere = " FROM cdr WHERE cdr.svcnum = svc_phone.svcnum $and_date";
+
+    #more efficient to join against cdr just once... this will do for now
+    push @select, map { " ( SELECT SUM($_) $fromwhere ) AS $_ " }
+                      qw( billsec rated_price );
+
+    my $money_char = $conf->config('money_char') || '$';
+
+    push @header, 'Minutes', 'Billed';
+    push @fields, 
+      sub { sprintf('%.3f', shift->get('billsec') / 60 ); },
+      sub { $money_char. sprintf('%.2f', shift->get('rated_price') ); };
+
+    #XXX and termination... (this needs a config to turn on, not by default)
+    if ( 1 ) { # $conf->exists('cdr-termination_hack') { #}
+
+      my $f_w =
+        " FROM cdr_termination LEFT JOIN cdr USING ( acctid ) ".
+        " WHERE cdr.acctid = svc_phone.phonenum ". # XXX connectone-specific
+        $and_date;
+
+      push @select,
+        " ( SELECT SUM(billsec) $f_w ) AS term_billsec ".
+        " ( SELECT SUM(cdr_termination.rated_price) $f_w ) AS term_rated_price";
+
+      push @header, 'Term Min', 'Term Billed',
+      push @fields,
+        sub { sprintf('%.3f', shift->get('term_billsec') / 60 ); },
+        sub { $money_char. sprintf('%.2f', shift->get('rated_price') ); };
+
+    }
+                 
+
+  }
+
 } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) {
   push @extra_sql, "svcpart = $1";
 } else {
@@ -106,8 +164,6 @@ my $sql_query = {
   'addl_from' => $addl_from,
 };
 
-my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ];
-
 #smaller false laziness w/svc_*.cgi here
 my $link_cust = sub {
   my $svc_x = shift;
-- 
2.11.0