X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fsvc_phone.cgi;h=29434083f443bd9f569aca8d22c6e83174bcf69f;hb=ea3ce8d7f076e7fecff4be7ae63bc413adb0adf5;hp=0c1d578876a7f95798729a0c9d631574999d7b6b;hpb=590603ecaea3184f64530755a76626be8205da49;p=freeside.git

diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi
index 0c1d57887..29434083f 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 $and_date = " AND enddate >= $beginning AND enddate <= $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.carrierid = CAST(svc_phone.phonenum AS BIGINT) ". # XXX connectone-specific, has to match svc_external.id :/
+        $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 {
@@ -74,7 +132,9 @@ my $addl_from = ' LEFT JOIN cust_svc  USING ( svcnum  ) '.
                 ' LEFT JOIN cust_main USING ( custnum ) ';
 
 #here is the agent virtualization
-push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql;
+push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql(
+                   'null_right' => 'View/link unlinked services'
+                 );
 
 my $extra_sql = '';
 if ( @extra_sql ) {
@@ -97,15 +157,15 @@ my $sql_query = {
   'select'    => join(', ',
                    'svc_phone.*',
                    'part_svc.svc',
-                    'cust_main.custnum',
-                    FS::UI::Web::cust_sql_fields(),
+                   @select,
+                   'cust_main.custnum',
+                   FS::UI::Web::cust_sql_fields(),
                  ),
-  'extra_sql' => "$extra_sql $orderby",
+  'extra_sql' => $extra_sql,
+  'order_by'  => $orderby,
   '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;