From 34d4866732521a402a95e40c56676afb27adc3e3 Mon Sep 17 00:00:00 2001
From: ivan <ivan>
Date: Thu, 23 Dec 2004 08:00:15 +0000
Subject: [PATCH] adding rate import scripts

---
 bin/rate-us.import | 109 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 bin/rate.import    |  85 +++++++++++++++++++++++++++++++++++++++++
 2 files changed, 194 insertions(+)
 create mode 100755 bin/rate-us.import
 create mode 100755 bin/rate.import

diff --git a/bin/rate-us.import b/bin/rate-us.import
new file mode 100755
index 000000000..66ac5de94
--- /dev/null
+++ b/bin/rate-us.import
@@ -0,0 +1,109 @@
+#!/usr/bin/perl -w
+
+use strict;
+#use Spreadsheet::ParseExcel;
+use DBI;
+use FS::UID qw(adminsuidsetup);
+use FS::rate_region;
+use FS::rate_prefix;
+use FS::rate_region;
+
+my $ratenum = 1;
+
+my $user = shift or usage();
+adminsuidsetup $user;
+
+sub usage {
+  #die "Usage:\n\n  rate.import user rates.xls worksheet_name";
+  die "Usage:\n\n  rate.import user";
+}
+
+my %rate_region;
+
+foreach my $file ( 'areas and rates US.xls',
+                   'areas and rates US2.xls',
+                   'areas and rates US3.xls',
+                 )
+{
+
+  my $dbh = DBI->connect("DBI:Excel:file=$file")
+    or die "can't connect: $DBI::errstr";
+  
+  #my $table = shift or usage();
+  my $table = 'Sheet1';
+  my $sth = $dbh->prepare("select * from $table")
+    or die "can't prepare: ". $dbh->errstr;
+  $sth->execute
+    or die "can't execute: ". $sth->errstr;
+  
+  while ( my $row = $sth->fetchrow_hashref ) {
+  
+    #print join(' - ', map $row->{$_}, qw( rate_center Code Area_Prefix Rate ) ). "\n";
+  
+    my $regionname = $row->{'rate_center'};
+    $regionname =~ s/\xA0//g;
+    #$regionname =~ s/\xE9/e/g; #e with accent aigu
+    $regionname =~ s/(^\s+|\s+$)//;
+    $regionname .= ', USA';
+  
+    my $prefix = $row->{'area_prefix'};
+    $prefix =~ s/\xA0//g;
+    $prefix =~ s/\s$//;
+    #my $prefixprefix = '';
+    #if ( $prefix =~ /^\s*(\d+)\s*\((.*)\)\s*$/ ) {
+    #  $prefixprefix = $1;
+    #  $prefix = $2;
+    #} elsif ( $prefix =~ /^\s*\((\d{3})\)\s*(.*)$/ ) {
+    #  $prefixprefix = $1;
+    #  $prefix = $2;
+    #}
+  
+    my @rate_prefix = map { 
+                            #warn $row->{'rate_center'}. ": $prefixprefix$_\n";
+                            new FS::rate_prefix {
+                              'countrycode' => '1', # $row->{'Country'}
+                              #'npa'         => $prefixprefix.$_,
+                              'npa'         => $_,
+                            };
+                          }
+                          split(/\s*[;,]\s*/, $prefix);
+  
+  
+    my $dest_detail = new FS::rate_detail {
+                                            'ratenum'         => $ratenum,
+                                            'min_included'    => 0,
+                                            'min_charge'      =>
+                                                sprintf('%.2f', $row->{'rate'} ),
+                                            'sec_granularity' => 60,
+                                          };
+
+    unless ( exists $rate_region{$regionname} ) {
+
+      my $rate_region = new FS::rate_region {
+        'regionname' => $regionname,
+      };
+
+      my $error = $rate_region->insert( 'rate_prefix' => \@rate_prefix,
+                                        'dest_detail' => [ $dest_detail ],
+                                      );
+      die $error if $error;
+
+      $rate_region{$regionname} = $rate_region->regionnum;
+
+    } else {
+
+      foreach my $rate_prefix ( @rate_prefix ) {
+        $rate_prefix->regionnum($rate_region{$regionname});
+        my $error = $rate_prefix->insert;
+        die $error if $error;
+      }
+
+      #$rate_detail->dest_regionnum($rate_region{$regionname});
+      #$error = $rate_detail->insert;
+      #die $error if $error;
+
+    }
+  
+  }
+
+}
diff --git a/bin/rate.import b/bin/rate.import
new file mode 100755
index 000000000..29b5239d5
--- /dev/null
+++ b/bin/rate.import
@@ -0,0 +1,85 @@
+#!/usr/bin/perl
+
+use strict;
+#use Spreadsheet::ParseExcel;
+use DBI;
+use FS::UID qw(adminsuidsetup);
+use FS::rate_region;
+use FS::rate_prefix;
+use FS::rate_region;
+
+my $ratenum = 1;
+
+my $user = shift or usage();
+adminsuidsetup $user;
+
+#my $file = shift or usage();
+my $file = 'areas and rates.xls';
+my $dbh = DBI->connect("DBI:Excel:file=$file")
+  or die "can't connect: $DBI::errstr";
+
+#my $table = shift or usage();
+my $table = 'areas_and_rates';
+my $sth = $dbh->prepare("select * from $table")
+  or die "can't prepare: ". $dbh->errstr;
+$sth->execute
+  or die "can't execute: ". $sth->errstr;
+
+sub usage {
+  #die "Usage:\n\n  rate.import user rates.xls worksheet_name";
+  die "Usage:\n\n  rate.import user";
+}
+
+##
+
+while ( my $row = $sth->fetchrow_hashref ) {
+
+  #print join(' - ', map $row->{$_}, qw( Country Code Area_Prefix Rate ) ). "\n";
+
+  my $regionname = $row->{'Country'};
+  $regionname =~ s/\xA0//g;
+  $regionname =~ s/\xE9/e/g; #e with accent aigu
+  $regionname =~ s/(^\s+|\s+$)//;
+
+  #next if $regionname =~ /Sweden Telia Mobile/;
+
+  my $rate_region = new FS::rate_region {
+    'regionname' => $regionname,
+  };
+
+  my $prefix = $row->{'Area_Prefix'};
+  $prefix =~ s/\xA0//g;
+  $prefix =~ s/\s$//;
+  my $prefixprefix = '';
+  if ( $prefix =~ /^\s*(\d+)\s*\((.*)\)\s*$/ ) {
+    $prefixprefix = $1;
+    $prefix = $2;
+  } elsif ( $prefix =~ /^\s*\((\d{3})\)\s*(.*)$/ ) {
+    $prefixprefix = $1;
+    $prefix = $2;
+  }
+
+  my @rate_prefix = map { 
+                          #warn $row->{'Country'}. ": $prefixprefix$_\n";
+                          new FS::rate_prefix {
+                            'countrycode' => $row->{'Code'},
+                            'npa'         => $prefixprefix.$_,
+                          };
+                        }
+                        split(/\s*[;,]\s*/, $prefix);
+
+
+  my $dest_detail = new FS::rate_detail {
+                                          'ratenum'         => $ratenum,
+                                          'min_included'    => 0,
+                                          'min_charge'      =>
+                                              sprintf('%.2f', $row->{'Rate'} ),
+                                          'sec_granularity' => 60,
+                                        };
+  
+  my $error = $rate_region->insert( 'rate_prefix' => \@rate_prefix,
+                                    'dest_detail' => [ $dest_detail ],
+                                  );
+  die $error if $error;
+
+}
-- 
2.11.0