inter/intra-state phone billing and custom rate import script, RT13112

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index eebcfec..ab4752f 100644 (file)
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -2593,6 +2593,9 @@ sub tables_hashref {
         'countrycode', 'varchar',     '',  3, '', '', 
         'npa',         'varchar', 'NULL', 10, '', '', #actually the whole prefix
         'nxx',         'varchar', 'NULL',  3, '', '', #actually not used
+        'latanum',     'int',     'NULL',      '', '', '',
+        'state',       'char',    'NULL',       2, '', '', 
+        'ocn',         'char',    'NULL',       4, '', '', 
       ],
       'primary_key' => 'prefixnum',
       'unique'      => [],

diff --git a/FS/FS/part_pkg/voip_cdr.pm b/FS/FS/part_pkg/voip_cdr.pm
index ad7589b..25b8d78 100644 (file)
--- a/FS/FS/part_pkg/voip_cdr.pm
+++ b/FS/FS/part_pkg/voip_cdr.pm
@@ -88,6 +88,15 @@ tie my %granularity, 'Tie::IxHash', FS::rate_detail::granularities();
                      'select_key'   => 'ratenum',
                      'select_label' => 'ratename',
                    },
+                   
+    'intrastate_ratenum'   => { 'name' => 'Optional alternate intrastate rate plan',
+                     'type' => 'select',
+                     'select_table' => 'rate',
+                     'select_key'   => 'ratenum',
+                     'select_label' => 'ratename',
+                     'disable_empty' => 0,
+                     'empty_label'   => '',
+                   },
 
     'min_included' => { 'name' => 'Minutes included when using the "single price per minute" rating method or when using the "prefix" rating method ("region group" billing)',
                     },
@@ -252,8 +261,9 @@ tie my %granularity, 'Tie::IxHash', FS::rate_detail::granularities();
                        recur_method cutoff_day
                        add_full_period
                        cdr_svc_method
-                       rating_method ratenum min_charge min_included
-                      sec_granularity
+                       rating_method ratenum intrastate_ratenum 
+                       min_charge min_included
+                              sec_granularity
                        ignore_unrateable
                        default_prefix
                        disable_src
@@ -273,7 +283,7 @@ tie my %granularity, 'Tie::IxHash', FS::rate_detail::granularities();
                        411_rewrite
                        output_format usage_mandate summarize_usage usage_section
                        bill_every_call bill_inactive_svcs
-                       count_available_phones suspend_bill
+                       count_available_phones suspend_bill 
                      )
                   ],
   'weight' => 40,
@@ -495,6 +505,30 @@ sub calc_usage {
           my $eff_ratenum = $cdr->is_tollfree('accountcode')
             ? $cust_pkg->part_pkg->option('accountcode_tollfree_ratenum')
             : '';
+
+          my $intrastate_ratenum = $cust_pkg->part_pkg->option('accountcode_tollfree_ratenum');
+          if ( $intrastate_ratenum && !$cdr->is_tollfree ) {
+            # this is relatively easy only because:
+            # -assume all numbers are valid NANP numbers NOT in a fully-qualified format
+            # -disregard toll-free
+            # -disregard private or unknown numbers
+            # -there is exactly one record in rate_prefix for a given NPANXX
+            # -default to interstate if we can't find one or both of the prefixes
+            my $dstprefix = $cdr->dst;
+            $dstprefix =~ /^(\d{6})/;
+            $dstprefix = qsearchs('rate_prefix', {   'countrycode' => '1', 
+                                                        'npa' => $1, 
+                                                 }) || '';
+            my $srcprefix = $cdr->src;
+            $srcprefix =~ /^(\d{6})/;
+            $srcprefix = qsearchs('rate_prefix', {   'countrycode' => '1',
+                                                     'npa' => $1, 
+                                                 }) || '';
+            $eff_ratenum = $intrastate_ratenum if ($srcprefix && $dstprefix
+                && $srcprefix->state && $dstprefix->state
+                && $srcprefix->state eq $dstprefix->state);
+          }
+
           $eff_ratenum ||= $ratenum;
           $rate = qsearchs('rate', { 'ratenum' => $eff_ratenum })
             or die "ratenum $eff_ratenum not found!";

diff --git a/FS/FS/rate_prefix.pm b/FS/FS/rate_prefix.pm
index ce780fe..585802a 100644 (file)
--- a/FS/FS/rate_prefix.pm
+++ b/FS/FS/rate_prefix.pm
@@ -108,6 +108,9 @@ sub check {
     || $self->ut_number('countrycode')
     || $self->ut_numbern('npa')
     || $self->ut_numbern('nxx')
+    || $self->ut_foreign_keyn('latanum', 'lata', 'latanum')
+    || $self->ut_textn('state')
+    || $self->ut_textn('ocn')
   ;
   return $error if $error;
 

diff --git a/bin/v-rate-import b/bin/v-rate-import
new file mode 100755 (executable)
index 0000000..7d52b5f
--- /dev/null
+++ b/bin/v-rate-import
@@ -0,0 +1,92 @@
+#!/usr/bin/perl
+
+use strict;
+use DBI;
+use FS::UID qw(adminsuidsetup);
+use FS::rate_prefix;
+use FS::rate_region;
+use FS::rate_detail;
+use FS::Record qw(qsearch qsearchs dbh);
+
+# Assumption: 1-to-1 relationship between rate_region and rate_prefix, with
+# two rate_detail per rate_region: one for interstate; one for intrastate
+#
+# Create interstate and intrastate rate plans - run the script once
+# per spreadsheet, setting the appropriate values below.
+####### SET THESE! ####################
+my $ratenum = 3;
+my $file = "/home/levinse/domestic_interstate.xls";
+my $sheet_name = 'domestic_interstate';
+#######################################
+
+my $user = shift or die "no user specified";
+adminsuidsetup $user;
+
+local $SIG{HUP} = 'IGNORE';
+local $SIG{INT} = 'IGNORE';
+local $SIG{QUIT} = 'IGNORE';
+local $SIG{TERM} = 'IGNORE';
+local $SIG{TSTP} = 'IGNORE';
+local $SIG{PIPE} = 'IGNORE';
+
+my $oldAutoCommit = $FS::UID::AutoCommit;
+local $FS::UID::AutoCommit = 0;
+my $dbhfs = dbh;
+
+my $dbh = DBI->connect("DBI:Excel:file=$file")
+  or die "can't connect: $DBI::errstr";
+
+my $sth = $dbh->prepare("select * from $sheet_name")
+  or die "can't prepare: ". $dbh->errstr;
+$sth->execute
+  or die "can't execute: ". $sth->errstr;
+
+my @rp_cache = qsearch('rate_prefix', {} ) or die "can't cache rate_prefix";
+my %rp_cache = map { $_->npa => $_ } @rp_cache;
+
+sub fatal {
+    my $msg = shift;
+    $dbhfs->rollback if $oldAutoCommit;
+    die $msg;
+}
+
+while ( my $row = $sth->fetchrow_hashref ) {
+  my $lata = $row->{'lata'};
+  my $ocn = $row->{'ocn'};
+  my $state = $row->{'state'};
+  my $rate = $row->{'rate'};
+  my $npanxx = $row->{'lrn'};
+  my $error = '';
+
+  my $rp;
+  if ( $rp_cache{$npanxx} ) {
+      $rp = $rp_cache{$npanxx};
+  } 
+  else {
+     my $rr = new FS::rate_region { 'regionname' => $state };
+     $error = $rr->insert;
+     fatal("can't insert rr") if $error;
+
+     $rp = new FS::rate_prefix {   'countrycode'   => '1',
+                                   'npa'           => $npanxx,
+                                   'ocn'           => $ocn,
+                                   'state'         => $state,
+                                   'latanum'       => $lata,
+                                   'regionnum'     => $rr->regionnum,
+                               }; 
+     $error = $rp->insert;
+     fatal("can't insert rp") if $error;
+     $rp_cache{$npanxx} = $rp;
+  }
+
+  my $rd = new FS::rate_detail {    'ratenum'         => $ratenum,
+                                    'min_included'    => 0,
+                                    'min_charge'      => $rate,
+                                    'sec_granularity' => 60,
+                                    'dest_regionnum'  => $rp->regionnum,
+                                };
+  $error = $rd->insert;
+  fatal("can't insert rd") if $error;
+}
+
+$dbhfs->commit or die $dbhfs->errstr if $oldAutoCommit;