From 8c79971d5ba7a6546ff48d9a4cb05fe071312461 Mon Sep 17 00:00:00 2001
From: Mark Wells <mark@freeside.biz>
Date: Mon, 9 May 2016 23:43:07 -0700
Subject: [PATCH] script to import international rate deck, #39045

---
 bin/rate-intl.import | 113 +++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 113 insertions(+)
 create mode 100755 bin/rate-intl.import

diff --git a/bin/rate-intl.import b/bin/rate-intl.import
new file mode 100755
index 000000000..7eef5878f
--- /dev/null
+++ b/bin/rate-intl.import
@@ -0,0 +1,113 @@
+#!/usr/bin/perl
+
+use strict;
+use Text::CSV;
+use FS::Misc::Getopt;
+use FS::Record qw(qsearchs qsearch dbh);
+use FS::rate;
+use FS::rate_region;
+use FS::rate_prefix;
+use FS::rate_detail;
+
+getopts('');
+
+$FS::UID::AutoCommit = 0;
+my $dbh = dbh;
+
+my $file = shift or usage();
+open my $in, '<', $file or die "$file: $!\n";
+my $csv = Text::CSV->new({ binary => 1, auto_diag => 2 });
+# set header row
+$csv->column_names($csv->getline($in));
+
+my $error;
+
+my $granularity = 1;
+# default is to charge per second; edit this if needed
+
+while (my $row = $csv->getline_hr($in)) {
+  print $csv->string;
+
+  # ProfileKey is just a number
+  my $rate = qsearchs('rate', { 'ratename' => $row->{'ProfileKey'} });
+  if (!$rate) {
+    $rate = FS::rate->new({ 'ratename' => $row->{'ProfileKey'} });
+    $error = $rate->insert;
+    die $error if $error;
+  }
+
+  # DestinationId looks like "Country - City" or "Country - Mobile -
+  # Carrier" (or sometimes just "Country - Mobile").
+  my $region = qsearchs('rate_region', {
+      'regionname' => $row->{'DestinationId'}
+  });
+  if (!$region) {
+    $region = FS::rate_region->new({
+      'regionname' => $row->{'DestinationId'}
+    });
+    $error = $region->insert;
+    die $error if $error;
+  }
+
+  # Prefix strings found in there look like
+  # "e164:123-45-6nnnnnnn-"
+  # The first group of digits is the country code, any others are the
+  # prefix. Sometimes the nnnn's are NNNN's. The dashes are not guaranteed
+  # to be anywhere specific.
+  # Catchall prefixes start with "-A", which has a meaning like "match
+  # anything, but at a lower priority than a digit match".
+  # NANPA numbers use "1-", and for a catchall area code use "1-AAA-".
+  my $cc_long = $row->{CountryCodeLong};
+  $cc_long =~ /^e164:(\d+)-([\d-]*)A*-?n+-$/i;
+  my $countrycode = $1;
+  if (!$countrycode) { # totally legit reasons for this, e.g. 1-AAA-411
+    warn "can't parse number prefix:\n$cc_long\n";
+    next;
+  }
+  my $prefix = $2;
+  $prefix =~ s/-//g;
+
+  my %prefix = (
+      'regionnum'   => $region->regionnum,
+      'countrycode' => $countrycode,
+      'npa'         => $prefix,
+  );
+  my $rate_prefix = qsearchs('rate_prefix', \%prefix);
+  if (!$rate_prefix) {
+    $rate_prefix = FS::rate_prefix->new(\%prefix);
+    $error = $rate_prefix->insert;
+    die $error if $error;
+  }
+
+  # enough to identify the detail
+  my %detail = (
+    'ratenum'         => $rate->ratenum,
+    'dest_regionnum'  => $region->regionnum,
+    'cdrtypenum'      => '',
+    'ratetimenum'     => '',
+  );
+  my $dest_detail = qsearchs('rate_detail', \%detail);
+  # ProfileRate is 5 decimal places, same as rate_detail.min_charge
+  if (!$dest_detail) {
+    $dest_detail = FS::rate_detail->new({
+        %detail,
+        'min_included'    => 0,
+        'min_charge'      => $row->{ProfileRate},
+        'sec_granularity' => $granularity,
+    });
+    $error = $dest_detail->insert;
+  } else {
+    local $FS::Record::nowarn_identical = 1;
+    $dest_detail->set('min_charge' => $row->{ProfileRate});
+    $error = $dest_detail->replace;
+  }
+  die $error if $error;
+}
+dbh->commit;
+print "Finished.\n";
+
+
+sub usage {
+  die "Usage: rate-intl.import <user> <file>.csv\n\n";
+}
+
-- 
2.11.0