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
|
#!/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";
}
|
