match CDRs to services by IP address, #16723

7 files changed, 87 insertions, 9 deletions

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index b2dddca..1112f52 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -2940,6 +2940,10 @@ sub tables_hashref {
         'lastapp',     'varchar',  '', $char_d, \"''", '', 
         'lastdata',    'varchar',  '', $char_d, \"''", '', 
 
+        #currently only opensips
+        'src_ip_addr', 'varchar',  'NULL',  15,    '', '',
+        'dst_ip_addr', 'varchar',  'NULL',  15,    '', '',
+
         #these don't seem to be logged by most of the SQL cdr_* modules
         #except tds under sql-illegal names, so;
         # ... don't rely on them for rating?
@@ -3039,6 +3043,7 @@ sub tables_hashref {
                    [ 'sessionnum' ], [ 'subscriber' ],
                    [ 'freesidestatus' ], [ 'freesiderewritestatus' ],
                    [ 'cdrbatch' ], [ 'cdrbatchnum' ],
+                   [ 'src_ip_addr' ], [ 'dst_ip_addr' ],
                  ],
     },
 
diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm
index ff07a59..9b70719 100644
--- a/FS/FS/cdr.pm
+++ b/FS/FS/cdr.pm
@@ -87,6 +87,10 @@ following fields are currently supported:
 
 =item lastdata - Last application data
 
+=item src_ip_addr - Source IP address (dotted quad, zero-filled)
+
+=item dst_ip_addr - Destination IP address (same)
+
 =item startdate - Start of call (UNIX-style integer timestamp)
 
 =item answerdate - Answer time of call (UNIX-style integer timestamp)
@@ -187,6 +191,8 @@ sub table_info {
         'dstchannel'            => 'Destination channel',
         #'lastapp'               => '',
         #'lastdata'              => '',
+        'src_ip_addr'           => 'Source IP',
+        'dst_ip_addr'           => 'Dest. IP',
         'startdate'             => 'Start date',
         'answerdate'            => 'Answer date',
         'enddate'               => 'End date',
@@ -1570,6 +1576,31 @@ sub _upgrade_data {
 
 }
 
+=item ip_addr_sql FIELD RANGE
+
+Returns an SQL condition to search for CDRs with an IP address 
+within RANGE.  FIELD is either 'src_ip_addr' or 'dst_ip_addr'.  RANGE 
+should be in the form "a.b.c.d-e.f.g.h' (dotted quads), where any of 
+the leftmost octets of the second address can be omitted if they're 
+the same as the first address.
+
+=cut
+
+sub ip_addr_sql {
+  my $class = shift;
+  my ($field, $range) = @_;
+  $range =~ /^[\d\.-]+$/ or die "bad ip address range '$range'";
+  my @r = split('-', $range);
+  my @saddr = split('\.', $r[0] || '');
+  my @eaddr = split('\.', $r[1] || '');
+  unshift @eaddr, (undef) x (4 - scalar @eaddr);
+  for(0..3) {
+    $eaddr[$_] = $saddr[$_] if !defined $eaddr[$_];
+  }
+  "$field >= '".sprintf('%03d.%03d.%03d.%03d', @saddr) . "' AND ".
+  "$field <= '".sprintf('%03d.%03d.%03d.%03d', @eaddr) . "'";
+}
+
 =back
 
 =head1 BUGS
diff --git a/FS/FS/part_pkg/voip_cdr.pm b/FS/FS/part_pkg/voip_cdr.pm
index 3c456dc..aaad974 100644
--- a/FS/FS/part_pkg/voip_cdr.pm
+++ b/FS/FS/part_pkg/voip_cdr.pm
@@ -20,6 +20,8 @@ tie my %cdr_svc_method, 'Tie::IxHash',
   'svc_phone.phonenum' => 'Phone numbers (svc_phone.phonenum)',
   'svc_pbx.title'      => 'PBX name (svc_pbx.title)',
   'svc_pbx.svcnum'     => 'Freeside service # (svc_pbx.svcnum)',
+  'svc_pbx.ip.src'     => 'PBX name to source IP address',
+  'svc_pbx.ip.dst'     => 'PBX name to destination IP address',
 ;
 
 tie my %rating_method, 'Tie::IxHash',
@@ -75,8 +77,9 @@ tie my %unrateable_opts, 'Tie::IxHash',
                        },
 
     'cdr_svc_method' => { 'name' => 'CDR service matching method',
-                          'type' => 'radio',
-                          'options' => \%cdr_svc_method,
+#                          'type' => 'radio',
+                          'type' => 'select',
+                          'select_options' => \%cdr_svc_method,
                         },
 
     'rating_method' => { 'name' => 'Rating method',
@@ -102,7 +105,7 @@ tie my %unrateable_opts, 'Tie::IxHash',
 
     'calls_included' => { 'name' => 'Number of calls included at no usage charge', },
 
-    'min_included' => { 'name' => 'Minutes included when using the "single price per minute" rating method or when using the "prefix" rating method ("region group" billing)',
+    'min_included' => { 'name' => 'Minutes included when using the "single price per minute" or "prefix" rating method',
                     },
 
     'min_charge' => { 'name' => 'Charge per minute when using "single price per minute" rating method',
@@ -359,7 +362,7 @@ sub calc_usage {
 
   my $use_duration = $self->option('use_duration');
 
-  my($svc_table, $svc_field) = split('\.', $cdr_svc_method);
+  my($svc_table, $svc_field, $by_ip_addr) = split('\.', $cdr_svc_method);
 
   my @cust_svc;
   if( $self->option('bill_inactive_svcs',1) ) {
@@ -388,7 +391,12 @@ sub calc_usage {
         'status'         => '',
         'for_update'     => 1,
       );  # $last_bill, $$sdate )
-    $options{'by_svcnum'} = 1 if $svc_field eq 'svcnum';
+    if ( $svc_field eq 'svcnum' ) {
+      $options{'by_svcnum'} = 1;
+    }
+    elsif ($svc_table eq 'svc_pbx' and $svc_field eq 'ip') {
+      $options{'by_ip_addr'} = $by_ip_addr;
+    }
 
     #my @invoice_details_sort;
 
diff --git a/FS/FS/svc_pbx.pm b/FS/FS/svc_pbx.pm
index 37ab174..f8b9605 100644
--- a/FS/FS/svc_pbx.pm
+++ b/FS/FS/svc_pbx.pm
@@ -283,6 +283,10 @@ with the chosen prefix.
 =item by_svcnum => 1: Select CDRs where the svcnum field matches, instead of 
 title/charged_party.  Normally this field is set after processing.
 
+=item by_ip_addr => 'src' or 'dst': Select CDRs where the src_ip_addr or 
+dst_ip_addr field matches title.  In this case, some special logic is applied
+to allow title to indicate a range of IP addresses.
+
 =item begin, end: Start and end of date range, as unix timestamp.
 
 =item cdrtypenum: Only return CDRs with this type number.
@@ -309,6 +313,10 @@ sub get_cdrs {
   if ( $options{'by_svcnum'} ) {
     $hash{'svcnum'} = $self->svcnum;
   }
+  elsif ( $options{'by_ip_addr'} =~ /^src|dst$/) {
+    my $field = 'cdr.'.$options{'by_ip_addr'}.'_ip_addr';
+    push @where, FS::cdr->ip_addr_sql($field, $self->title);
+  }
   else {
     #matching by title
     my $title = $self->title;
diff --git a/bin/cdr-opensips.import b/bin/cdr-opensips.import
index 489fac6..b8169d5 100755
--- a/bin/cdr-opensips.import
+++ b/bin/cdr-opensips.import
@@ -82,15 +82,26 @@ while ( $row = $sth->fetchrow_hashref ) {
 
   #i guess now we're NANPA-centric, but at least we warn on non-numeric numbers
   my $src = '';
-  if ( $row->{'caller_id'} =~ /^sip:(\+1)?(\d+)@/ ) {
+  my $src_ip = '';
+  if ( $row->{'caller_id'} =~ /^sip:(\+1?)?(\w+)@(.*)/ ) {
     $src = $2;
+    my $rest = $3;
+    if ($rest =~ /^([\d\.]{7,15})/) {
+      # canonicalize it so that ascii sort order works
+      $src_ip = sprintf('%03d.%03d.%03d.%03d', split('\.', $1));
+    }
   } else {
     warn "unparseable caller_id ". $row->{'caller_id'}. "\n";
   }
 
   my $dst = '';
-  if ( $row->{'callee_id'} =~ /^sip:(\+1)?(\d+)@/ ) {
+  my $dst_ip = '';
+  if ( $row->{'callee_id'} =~ /^sip:(\+1?)?(\w+)@(.*)/ ) {
     $dst = $2;
+    my $rest = $3;
+    if ($rest =~ /^([\d\.]{7,15})/) {
+      $dst_ip = sprintf('%03d.%03d.%03d.%03d', split('\.', $1));
+    }
   } else {
     warn "unparseable callee_id ". $row->{'callee_id'}. "\n";
   }
@@ -108,6 +119,8 @@ while ( $row = $sth->fetchrow_hashref ) {
     $cdr->startdate($date);
     $cdr->src($src);
     $cdr->dst($dst);
+    $cdr->src_ip_addr($src_ip);
+    $cdr->dst_ip_addr($dst_ip);
   }
   elsif ( $row->{'method'} eq 'ACK' ) {
     $cdr->answerdate($date);
diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html
index 5e917db..d0d7292 100644
--- a/httemplate/search/cdr.html
+++ b/httemplate/search/cdr.html
@@ -223,6 +223,17 @@ if ( $cgi->param('svcnum') =~ /^([\d, ]+)$/ ) {
 }
 
 ###
+# src/dst_ip_addr
+###
+foreach my $field ('src_ip_addr','dst_ip_addr') {
+  if ( $cgi->param($field) ) {
+    my $search = FS::cdr->ip_addr_sql($field, $cgi->param($field));
+    push @search, $search;
+    push @qsearch, $search;
+  }
+}
+
+###
 # cdrbatchnum (or legacy cdrbatch)
 ###
 
diff --git a/httemplate/view/svc_pbx.cgi b/httemplate/view/svc_pbx.cgi
index 79cafed..a1afeb2 100644
--- a/httemplate/view/svc_pbx.cgi
+++ b/httemplate/view/svc_pbx.cgi
@@ -1,6 +1,6 @@
 <% include('elements/svc_Common.html',
              'table'     => 'svc_pbx',
-	     'edit_url'  => $p."edit/svc_Common.html?svcdb=svc_pbx;svcnum=",
+             'edit_url'  => $p."edit/svc_Common.html?svcdb=svc_pbx;svcnum=",
              'labels'    => \%labels,
              'html_foot' => $html_foot,
           )
@@ -43,12 +43,14 @@ my $html_foot = sub {
 
   my $cdr_svc_method = $voip_pkg->option('cdr_svc_method')
                        || 'svc_phone.phonenum';
-  return '' unless $cdr_svc_method =~ /^svc_pbx\.(\w+)$/;
+  return '' unless $cdr_svc_method =~ /^svc_pbx\.(.*)$/;
   my $field = $1;
 
   my $search;
   if ( $field eq 'title' ) {
     $search = 'charged_party='. uri_escape($svc_pbx->title);
+  } elsif ( $field =~ /^ip\.(\w+)$/ ) {
+    $search = "$1_ip_addr=". uri_escape($svc_pbx->title);
   } elsif ( $field eq 'svcnum' ) {
     $search = 'svcnum='. $svc_pbx->svcnum;
   } else {