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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
#!/usr/bin/perl
use strict;
use Text::CSV;
use FS::Misc::Getopt;
use FS::Record qw( qsearch qsearchs dbh );
use FS::rate;
use FS::rate_region;
use FS::rate_prefix;
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 $rate = new FS::rate {
'ratename' => 'Toll-Free base rates',
};
my $r_error = $rate->insert;
die $r_error if $r_error;
my $ratenum = $rate->ratenum;
my %rate_region = ();
my ($rd, $rp, $nr) = (0, 0, 0);
while (my $row = $csv->getline_hr($in)) {
print $csv->string;
#no, this creates duplicate regions/prefixes
#my $rate_region = new FS::rate_region {
# 'regionname' => $row->{'Originating Location'},
#};
#my $rr_error = $rate_region->insert;
#die $rr_error if $rr_error;
my $cc = $row->{'Country Code'};
my $npa = '';
if ( $row->{'World Zone'} eq '1' ) {
$npa = $cc;
$cc = '1';
}
my @rate_prefix = ();
if ( length($npa) ) {
push @rate_prefix, qsearchs('rate_prefix', { countrycode=>$cc, npa=>$npa } )
or do {
my $regionname = $row->{'Originating Location'};
warn "WARNING: previously unknown countrycode/npa $cc/$npa; ".
"make sure to update previous rates for new '$regionname' ".
"region\n";
my $rate_region = new FS::rate_region {
'regionname' => $row->{'Originating Location'},
};
my $rr_error = $rate_region->insert;
die $rr_error if $rr_error;
$nr++;
my $rate_prefix = new FS::rate_prefix {
'regionnum' => $rate_region->regionnum,
'countrycode' => $cc,
'npa' => $npa,
};
my $rp_error = $rate_prefix->insert;
die $rp_error if $rp_error;
push @rate_prefix, $rate_prefix;
};
} else {
push @rate_prefix, qsearch('rate_prefix', { countrycode=>$cc } )
or die "unknown countrycode/npa $cc/$npa\n";
}
my %saw = ();
my @regionnum = grep !$saw{$_}++, map $_->regionnum, @rate_prefix;
foreach my $regionnum (@regionnum) {
my $rate_detail = new FS::rate_detail {
'ratenum' => $ratenum,
'dest_regionnum' => $regionnum,
'conn_charge' => ( ( $row->{'minimum seconds'} / 60 ) * $row->{'Dedicated Carrier (in US)'} ),
'conn_sec' => $row->{'minimum seconds'},
'min_charge' => $row->{'Dedicated Carrier (in US)'},
'sec_granularity' => $row->{"add'l sec increment"},
'min_included' => 0,
};
my $rd_error = $rate_detail->insert;
die $rd_error if $rd_error;
$rd++;
}
#no, this creates duplicate regions/prefixes
#my $rate_prefix = new FS::rate_prefix {
# 'regionnum' => $rate_region->regionnum,
# 'countrycode' => $cc,
# 'npa' => $npa,
#};
#my $rp_error = $rate_prefix->insert;
#die $rp_error if $rp_error;
$rp++;
}
dbh->commit;
print "Inserted $rd rates for $rp regions\n";
print "(Inserted $nr new regions)\n";
1;
sub usage {
die "Usage: rate-threshold_tollfree.import <user> <file>.csv\n\n";
}
|
