1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
|
#!/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 = ();
if ( $prefix =~ /\d/ ) {
@rate_prefix = map {
#warn $row->{'Country'}. ": $prefixprefix$_\n";
new FS::rate_prefix {
'countrycode' => $row->{'Code'},
'npa' => $prefixprefix.$_,
};
}
split(/\s*[;,]\s*/, $prefix);
} else {
@rate_prefix = ( new FS::rate_prefix {
'countycode' => $row->{'Code'},
'npa' => '',
};
);
}
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;
}
|
