4 use Spreadsheet::ParseExcel;
5 #use DBI; # doesn't work
6 use FS::UID qw(adminsuidsetup);
7 use FS::Record qw(qsearchs);
12 use Number::Phone::Country;
14 my $user = shift or usage();
15 my $file = shift or usage();
19 die "Usage:\n\n rate-level3-us.import user rates.xls [ multiplier ]\n";
22 my $multiplier = shift;
25 my $parser = Spreadsheet::ParseExcel->new;
26 my $book = $parser->parse($file);
27 my $sheet = $book->worksheet('International Term')
28 or die "No 'International Term' sheet found.\n";
31 for (; $row < 256; $row++) {
32 if (lc($sheet->get_cell($row, 0)->value) eq 'terminating country') {
36 die "Start of data table not found.\n" if $row == 256;
41 # default is to charge per second; edit this if needed
44 my $rate = qsearchs('rate', { 'ratename' => 'International Termination' });
46 $rate = FS::rate->new({ 'ratename' => 'International Termination' });
47 $error = $rate->insert;
51 # monkeypatch to pretend Antarctica is a country
52 $Number::Phone::Country::idd_codes{'672'} = 'AQ';
55 my ($country, $zone, $prefix, $charge);
56 while ( $sheet->get_cell($row, 0) ) {
57 ($country, $zone, $prefix, $charge) = map {
58 $country = $sheet->get_cell($row, $_)->value
63 print join("\t", $country, $zone, $prefix, $charge),"\n";
65 my $here = '[line '.($row+1).']';
69 ($country, $countrycode) =
70 Number::Phone::Country::phone2country_and_idd("+$prefix");
72 die "$here can't identify country prefix $prefix\n" unless $countrycode;
73 # trim countrycodes to what will fit in the field
74 $countrycode = substr($countrycode, 0, 3);
75 # and put the rest in rate_prefix.npa
76 $prefix =~ s/^$countrycode//;
77 } elsif ( $zone == 1 ) { #NANPA
80 die "$here unknown zone type $zone\n";
82 my $region = qsearchs('rate_region', { 'regionname' => $country });
84 $region = FS::rate_region->new({ 'regionname' => $country });
85 $error = $region->insert;
86 die "$here inserting region: $error\n" if $error;
90 'regionnum' => $region->regionnum,
91 'countrycode' => $countrycode,
94 my $rate_prefix = qsearchs('rate_prefix', \%prefix);
96 $rate_prefix = FS::rate_prefix->new(\%prefix);
97 $error = $rate_prefix->insert;
98 die "$here inserting prefix: $error\n" if $error;
101 # enough to identify the detail
103 'ratenum' => $rate->ratenum,
104 'dest_regionnum' => $region->regionnum,
109 $charge =~ s/^[\s\$]*//;
110 $charge = sprintf('%.05f', $charge * $multiplier);
112 my $dest_detail = qsearchs('rate_detail', \%detail);
114 $dest_detail = FS::rate_detail->new({
117 'min_charge' => $charge,
118 'sec_granularity' => $granularity,
120 $error = $dest_detail->insert;
122 local $FS::Record::nowarn_identical = 1;
123 $dest_detail->set('min_charge' => $charge);
124 $error = $dest_detail->replace;
126 die "$here setting rate detail: $error\n" if $error;