domestic rate import script, #71954

diff --git a/bin/rate-level3-us.import b/bin/rate-level3-us.import
new file mode 100755 (executable)
index 0000000..804cb5f
--- /dev/null
+++ b/bin/rate-level3-us.import
@@ -0,0 +1,201 @@
+#!/usr/bin/perl -w
+
+use strict;
+
+use FS::UID qw(adminsuidsetup);
+use Spreadsheet::ParseExcel;
+use FS::Record qw(qsearchs);
+use FS::rate;
+use FS::rate_region;
+use FS::rate_prefix;
+use FS::rate_detail;
+use FS::usage_class;
+
+use Text::CSV_XS;
+
+my $user = shift or usage();
+my $file = shift or usage();
+adminsuidsetup $user;
+
+sub usage {
+  die "Usage:\n\n  rate-level3-us.import user rates.xls [ multiplier ]\n";
+}
+
+my %lata_ocn;
+
+my $csvfile = 'npa-nxx-companytype-ocn.csv'; # not distributed here
+#NPA,NXX,COMPANY TYPE,OCN,COMPANY NAME,LATA,RATECENTER,STATE
+open my $fh, '<', $csvfile
+  or die $!;
+
+my $csv = Text::CSV_XS->new;
+while (!$csv->eof) {
+  my $row = $csv->getline($fh);
+  my $lata = $row->[5] or next;
+  my $ocn = $row->[3];
+  my $key = $lata . '-' . $ocn;
+  push @{ $lata_ocn{$key} ||= [] },
+    { npa => $row->[0],
+      nxx => $row->[1],
+      ratecenter => $row->[6],
+      state => $row->[7]
+    }
+  ;
+}
+
+my $multiplier = shift;
+$multiplier ||= 1;
+
+my $parser = Spreadsheet::ParseExcel->new;
+my $book = $parser->parse($file);
+my $sheet = $book->worksheet('VT - US48 OCN Rates')
+  or die "No 'VT - US48 OCN Rates' sheet found.\n";
+
+my $row = 0;
+for (; $row < 256; $row++) {
+  if (lc($sheet->get_cell($row, 0)->value) eq 'lata') {
+    last;
+  }
+}
+die "Start of data table not found.\n" if $row == 256;
+
+my $error;
+
+my $granularity = 1;
+# default is to charge per second; edit this if needed
+
+
+my %rate;
+my %classnum;
+foreach (qw(INTERSTATE INTRASTATE)) {
+  my $rate = qsearchs('rate', { 'ratename' => $_ });
+  if (!$rate) {
+    $rate = FS::rate->new({ 'ratename' => $_ });
+    $error = $rate->insert;
+    die $error if $error;
+  }
+  $rate{$_} = $rate;
+  my $class = qsearchs('usage_class', { 'classname' => ucfirst($_) });
+  $classnum{$_} = $class->classnum if $class;
+}
+
+$row++;
+my ($lata, $ocn, $jurisdiction, $charge) = @_;
+while ( $sheet->get_cell($row, 0) ) {
+  ($lata, $ocn, $jurisdiction, $charge) = map {
+    $sheet->get_cell($row, $_)->value
+  } 0..3;
+
+  last if !$lata;
+
+  print join("\t", $lata, $ocn, $jurisdiction, $charge),"\n";
+
+  my $here = '[line '.($row+1).']';
+
+  my @regionnums; # add the rate to each of these...
+
+  if ( $lata eq '*' ) {
+
+    my $regionname = 'Other US';
+    my $region = qsearchs('rate_region', { 'regionname' => $regionname });
+    if (!$region) {
+      $region = FS::rate_region->new({ 'regionname' => $regionname });
+      $error = $region->insert;
+      die "$here inserting region: $error\n" if $error;
+    }
+    my %prefix = (
+      'regionnum' => $region->regionnum,
+      'countrycode' => '1',
+      'npa' => '',
+      'nxx' => '',
+    );
+    my $rate_prefix = qsearchs('rate_prefix', \%prefix);
+    if (!$rate_prefix) {
+      $rate_prefix = FS::rate_prefix->new(\%prefix);
+      $error = $rate_prefix->insert;
+      die "$here inserting prefix: $error\n" if $error;
+    }
+    push @regionnums, $region->regionnum;
+
+  } else {
+
+    my $data = $lata_ocn{"$lata-$ocn"};
+    if (!$data) {
+      warn "$here no prefixes found for lata $lata / ocn $ocn\n";
+      next;
+    }
+
+    # find prefixes corresponding to this LATA/OCN. there can be MANY.
+
+    foreach my $prefixdata (@$data) {
+      my $npa = $prefixdata->{npa}
+        or die "$here no NPA found.\n";
+      my $nxx = $prefixdata->{nxx}
+        or die "$here no NXX found.\n";
+
+      # show a useful regionname, but include the LATA/OCN in it to
+      # prevent overlap.
+      my $regionname = $prefixdata->{ratecenter} . ', ' .
+                       $prefixdata->{state} .
+                       " $lata-$ocn";
+
+      my $region = qsearchs('rate_region', { 'regionname' => $regionname });
+      if (!$region) {
+        $region = FS::rate_region->new({ 'regionname' => $regionname });
+        $error = $region->insert;
+        die "$here inserting region: $error\n" if $error;
+      }
+
+      my %prefix = (
+        'regionnum'   => $region->regionnum,
+        'countrycode' => '1',
+        'npa'         => $npa . $nxx,
+      );
+      my $rate_prefix = qsearchs('rate_prefix', \%prefix);
+      if (!$rate_prefix) {
+        # don't search on unindexed fields
+        $prefix{'latanum'} = $lata;
+        $prefix{'ocn'}     = $ocn;
+        $prefix{'state'}   = $prefixdata->{state},
+        $rate_prefix = FS::rate_prefix->new(\%prefix);
+        $error = $rate_prefix->insert;
+        die "$here inserting prefix: $error\n" if $error;
+      }
+      push @regionnums, $region->regionnum;
+    } # foreach $prefixdata
+
+  } # $lata ne '*'
+
+  $charge =~ s/^[\s\$]*//;
+  $charge = sprintf('%.05f', $charge * $multiplier);
+
+  foreach my $regionnum (@regionnums) {
+    my $rate = $rate{$jurisdiction}
+      or die "$here unknown jurisdiction $jurisdiction\n";
+    my %detail = (
+      'ratenum'         => $rate->ratenum,
+      'dest_regionnum'  => $regionnum,
+      'cdrtypenum'      => '',
+      'ratetimenum'     => '',
+    );
+    
+    my $dest_detail = qsearchs('rate_detail', \%detail);
+    if (!$dest_detail) {
+      $dest_detail = FS::rate_detail->new({
+          %detail,
+          'min_included'    => 0,
+          'min_charge'      => $charge,
+          'sec_granularity' => $granularity,
+      });
+      $error = $dest_detail->insert;
+    } else {
+      local $FS::Record::nowarn_identical = 1;
+      $dest_detail->set('min_charge' => $charge);
+      $error = $dest_detail->replace;
+    }
+    die "$here setting rate detail: $error\n" if $error;
+  }
+} continue {
+  $row++
+}
+