improve emailed cdr csv file (#5727 again)

7 files changed, 71 insertions, 13 deletions

diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index 11afd9f..c8216ec 100644
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -1988,6 +1988,22 @@ sub ut_money {
   '';
 }
 
+=item ut_moneyn COLUMN
+
+Check/untaint monetary numbers.  May be negative.  If there
+is an error, returns the error, otherwise returns false.
+
+=cut
+
+sub ut_moneyn {
+  my($self,$field)=@_;
+  if ($self->getfield($field) eq '') {
+    $self->setfield($field, '');
+    return '';
+  }
+  $self->ut_money($field);
+}
+
 =item ut_text COLUMN
 
 Check/untaint text.  Alphanumerics, spaces, and the following punctuation
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 0ede000..877cf14 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -550,6 +550,7 @@ sub tables_hashref {
         'amount',  @money_typen, '', '', 
         'format',  'char', 'NULL', 1, '', '',
         'classnum', 'int', 'NULL', '', '', '',
+        'phonenum', 'varchar', 'NULL', 15, '', '',
         'detail',  'varchar', '', 255, '', '', 
       ],
       'primary_key' => 'detailnum',
diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm
index 4acdd85..a35ea00 100644
--- a/FS/FS/cust_bill.pm
+++ b/FS/FS/cust_bill.pm
@@ -794,8 +794,11 @@ sub generate_email {
       push @otherparts, build MIME::Entity
         'Type'        => 'text/csv',
         'Encoding'    => '7bit',
-        'Data'        => [ map { "$_\n" } $self->call_details ],
+        'Data'        => [ map { "$_\n" }
+                             $self->call_details('prepend_billed_number' => 1)
+                         ],
         'Disposition' => 'attachment',
+        'Filename'    => 'usage-'. $self->invnum. '.csv',
       ;
 
     }
@@ -3129,20 +3132,36 @@ sub _items_payments {
 
 }
 
-=item call_details
+=item call_details [ OPTION => VALUE ... ]
 
 Returns an array of CSV strings representing the call details for this invoice
+The only option available is the boolean prepend_billed_number
 
 =cut
 
 sub call_details {
-  my $self = shift;
-  map { $_->details( 'format_function' => sub{ shift },
-                     'escape_function' => sub{ return() },
-                   )
-      }
-    grep { $_->pkgnum }
-    $self->cust_bill_pkg;
+  my ($self, %opt) = @_;
+
+  my $format_function = sub { shift };
+
+  if ($opt{prepend_billed_number}) {
+    $format_function = sub {
+      my $detail = shift;
+      my $row = shift;
+
+      $row->amount ? $row->phonenum. ",". $detail : '"Billed number",'. $detail;
+      
+    };
+  }
+
+  my @details = map { $_->details( 'format_function' => $format_function,
+                                   'escape_function' => sub{ return() },
+                                 )
+                    }
+                  grep { $_->pkgnum }
+                  $self->cust_bill_pkg;
+  my $header = $details[0];
+  ( $header, grep { $_ ne $header } @details );
 }
 
 
diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm
index bb30f03..b6e8528 100644
--- a/FS/FS/cust_bill_pkg.pm
+++ b/FS/FS/cust_bill_pkg.pm
@@ -119,6 +119,7 @@ sub insert {
         'detail'     => (ref($detail) ? $detail->[1] : $detail ),
         'amount'     => (ref($detail) ? $detail->[2] : '' ),
         'classnum'   => (ref($detail) ? $detail->[3] : '' ),
+        'phonenum'   => (ref($detail) ? $detail->[4] : '' ),
       };
       $error = $cust_bill_pkg_detail->insert;
       if ( $error ) {
@@ -371,7 +372,7 @@ sub details {
   $format_sub = $opt{format_function} if $opt{format_function};
 
   map { ( $_->format eq 'C'
-          ? &{$format_sub}( $_->detail )
+          ? &{$format_sub}( $_->detail, $_ )
           : &{$escape_function}( $_->detail )
         )
       }
diff --git a/FS/FS/cust_bill_pkg_detail.pm b/FS/FS/cust_bill_pkg_detail.pm
index 9a90936..008f3ff 100644
--- a/FS/FS/cust_bill_pkg_detail.pm
+++ b/FS/FS/cust_bill_pkg_detail.pm
@@ -4,6 +4,7 @@ use strict;
 use vars qw( @ISA $me $DEBUG %GetInfoType );
 use FS::Record qw( qsearch qsearchs dbdef dbh );
 use FS::cust_bill_pkg;
+use FS::Conf;
 
 @ISA = qw(FS::Record);
 $me = '[ FS::cust_bill_pkg_detail ]';
@@ -102,10 +103,26 @@ and replace methods.
 sub check {
   my $self = shift;
 
+  my $conf = new FS::Conf;
+
+  my $phonenum = $self->phonenum;
+  my $phonenum_check_method;
+  if ( $conf->exists('svc_phone-allow_alpha_phonenum') ) {
+    $phonenum =~ s/\W//g;
+    $phonenum_check_method = 'ut_alphan';
+  } else {
+    $phonenum =~ s/\D//g;
+    $phonenum_check_method = 'ut_numbern';
+  }
+  $self->phonenum($phonenum);
+
   $self->ut_numbern('detailnum')
     || $self->ut_foreign_key('billpkgnum', 'cust_bill_pkg', 'billpkgnum')
+    || $self->ut_moneyn('amount')
     || $self->ut_enum('format', [ '', 'C' ] )
     || $self->ut_text('detail')
+    || $self->ut_foreign_keyn('classnum', 'usage_class', 'classnum')
+    || $self->$phonenum_check_method('phonenum')
     || $self->SUPER::check
     ;
 
diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm
index fe83138..3c28204 100644
--- a/FS/FS/cust_svc.pm
+++ b/FS/FS/cust_svc.pm
@@ -741,7 +741,8 @@ sub get_cdrs {
     qsearch( {
       'table'      => 'cdr',
       'hashref'    => \%hash,
-      'extra_sql'  => "$extra_sql $for_update",
+      'extra_sql'  => $extra_sql,
+      'order_by'   => "ORDER BY startdate $for_update",
     } );
 
   @cdrs;
diff --git a/FS/FS/part_pkg/voip_cdr.pm b/FS/FS/part_pkg/voip_cdr.pm
index 4f05963..9f150d6 100644
--- a/FS/FS/part_pkg/voip_cdr.pm
+++ b/FS/FS/part_pkg/voip_cdr.pm
@@ -558,13 +558,16 @@ sub calc_usage {
         if ( $charge > 0 ) {
           #just use FS::cust_bill_pkg_detail objects?
           my $call_details;
+          my $phonenum = $cust_svc->svc_x->phonenum;
 
           #if ( $self->option('rating_method') eq 'upstream_simple' ) {
           if ( scalar(@call_details) == 1 ) {
-            $call_details = [ 'C', $call_details[0], $charge, $classnum ];
+            $call_details =
+              [ 'C', $call_details[0], $charge, $classnum, $phonenum ];
           } else { #only used for $rating_method eq 'upstream' now
             $csv->combine(@call_details);
-            $call_details = [ 'C', $csv->string, $charge, $classnum ];
+            $call_details =
+              [ 'C', $csv->string, $charge, $classnum, $phonenum ];
           }
           warn "  adding details on charge to invoice: [ ".
               join(', ', @{$call_details} ). " ]"