update vnes intra/interstate rate import, RT#13961

1 files changed, 77 insertions, 31 deletions

diff --git a/bin/v-rate-import b/bin/v-rate-import
index 7d52b5f7e..95e8afe92 100755
--- a/bin/v-rate-import
+++ b/bin/v-rate-import
@@ -8,15 +8,26 @@ use FS::rate_region;
 use FS::rate_detail;
 use FS::Record qw(qsearch qsearchs dbh);
 
+# delete from rate;
+# Create interstate and intrastate rate plans
+#
+# delete from rate_detail;
+# delete from rate_region;
+# delete from rate_prefix;
+
 # 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.
+# run the script, setting the appropriate values below.
+
 ####### SET THESE! ####################
-my $ratenum = 3;
-my $file = "/home/levinse/domestic_interstate.xls";
-my $sheet_name = 'domestic_interstate';
+my $intra_ratenum = 3;
+my $inter_ratenum = 2;
+my $intra_class = 1;
+my $inter_class = 2;
+#my $file = "/home/levinse/domestic_interstate.xls";
+my $file = "/home/ivan/vnes/VNES Domestic VoIP Termination Deck 8-9-11.csv";
+#my $sheet_name = 'domestic_interstate';
 #######################################
 
 my $user = shift or die "no user specified";
@@ -33,15 +44,21 @@ 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 $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 $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";
+use Text::CSV_XS;
+my $csv = Text::CSV_XS->new or die Text::CSV->error_diag;
+
+open(my $fh, "<$file") or die $!;
+my $header = scalar(<$fh>); #NPA, NXX, LATA, State, Intrastate, Interstate
+
+my @rp_cache = qsearch('rate_prefix', {} );# or die "can't cache rate_prefix";
 my %rp_cache = map { $_->npa => $_ } @rp_cache;
 
 sub fatal {
@@ -50,17 +67,29 @@ sub fatal {
     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'};
+while ( my $row = $csv->getline($fh) ) {
+
+  #my $lata = $row->{'lata'};
+  #my $ocn = $row->{'ocn'};
+  #my $state = $row->{'state'};
+  #my $rate = $row->{'rate'};
+  #my $npanxx = $row->{'lrn'};
+
+  #NPA, NXX, LATA, State, Intrastate, Interstate
+  my $npa        = $row->[0];
+  my $nxx        = $row->[1];
+  my $lata       = $row->[2];
+  my $state      = $row->[3];
+  ( my $intra_rate = $row->[4] ) =~ s/^\s*\$//;
+  ( my $inter_rate = $row->[5] ) =~ s/^\s*\$//;
+
+  next if $lata == '99999';
+
   my $error = '';
 
   my $rp;
-  if ( $rp_cache{$npanxx} ) {
-      $rp = $rp_cache{$npanxx};
+  if ( $rp_cache{$npa.$nxx} ) {
+      $rp = $rp_cache{$npa.$nxx};
   } 
   else {
      my $rr = new FS::rate_region { 'regionname' => $state };
@@ -68,25 +97,42 @@ while ( my $row = $sth->fetchrow_hashref ) {
      fatal("can't insert rr") if $error;
 
      $rp = new FS::rate_prefix {   'countrycode'   => '1',
-                                   'npa'           => $npanxx,
-                                   'ocn'           => $ocn,
+                                   'npa'           => $npa.$nxx, #$npanxx
+                                   #'ocn'           => $ocn,
                                    'state'         => $state,
                                    'latanum'       => $lata,
                                    'regionnum'     => $rr->regionnum,
                                }; 
      $error = $rp->insert;
      fatal("can't insert rp") if $error;
-     $rp_cache{$npanxx} = $rp;
+     $rp_cache{$npa.$nxx} = $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;
+  #use Data::Dumper;
+  #warn Dumper($rp);
+
+  my %hash = ( 'min_included'    => 0,
+               'sec_granularity' => 60,
+               'dest_regionnum'  => $rp->regionnum,
+             );
+
+  my $intra_rd = new FS::rate_detail { 'ratenum'         => $intra_ratenum,
+                                       'min_charge'      => $intra_rate,
+                                       'intra_class'     => $intra_class,
+                                       %hash,
+                                     };
+  $error = $intra_rd->insert;
+  fatal("can't insert (intra) rd: $error") if $error;
+
+  my $inter_rd = new FS::rate_detail { 'ratenum'         => $inter_ratenum,
+                                       'min_charge'      => $inter_rate,
+                                       'inter_class'     => $inter_class,
+                                       %hash,
+                                     };
+  $error = $inter_rd->insert;
+  fatal("can't insert (inter) rd: $error") if $error;
 }
+$csv->eof or $csv->error_diag ();
+close $fh;
 
 $dbhfs->commit or die $dbhfs->errstr if $oldAutoCommit;