5 use FS::UID qw(adminsuidsetup);
6 use Spreadsheet::ParseExcel;
7 use FS::Record qw(qsearchs);
16 my $user = shift or usage();
17 my $file = shift or usage();
21 die "Usage:\n\n rate-level3-us.import user rates.xls [ multiplier ]\n";
26 my $csvfile = 'npa-nxx-companytype-ocn.csv'; # not distributed here
27 #NPA,NXX,COMPANY TYPE,OCN,COMPANY NAME,LATA,RATECENTER,STATE
28 open my $fh, '<', $csvfile
31 my $csv = Text::CSV_XS->new;
33 my $row = $csv->getline($fh);
34 my $lata = $row->[5] or next;
36 my $key = $lata . '-' . $ocn;
37 push @{ $lata_ocn{$key} ||= [] },
40 ratecenter => $row->[6],
46 my $multiplier = shift;
49 my $parser = Spreadsheet::ParseExcel->new;
50 my $book = $parser->parse($file);
51 my $sheet = $book->worksheet('VT - US48 OCN Rates')
52 or die "No 'VT - US48 OCN Rates' sheet found.\n";
55 for (; $row < 256; $row++) {
56 if (lc($sheet->get_cell($row, 0)->value) eq 'lata') {
60 die "Start of data table not found.\n" if $row == 256;
65 # default is to charge per second; edit this if needed
70 foreach (qw(INTERSTATE INTRASTATE)) {
71 my $rate = qsearchs('rate', { 'ratename' => $_ });
73 $rate = FS::rate->new({ 'ratename' => $_ });
74 $error = $rate->insert;
78 my $class = qsearchs('usage_class', { 'classname' => ucfirst($_) });
79 $classnum{$_} = $class->classnum if $class;
83 my ($lata, $ocn, $jurisdiction, $charge) = @_;
84 while ( $sheet->get_cell($row, 0) ) {
85 ($lata, $ocn, $jurisdiction, $charge) = map {
86 $sheet->get_cell($row, $_)->value
91 print join("\t", $lata, $ocn, $jurisdiction, $charge),"\n";
93 my $here = '[line '.($row+1).']';
95 my @regionnums; # add the rate to each of these...
99 my $regionname = 'Other US';
100 my $region = qsearchs('rate_region', { 'regionname' => $regionname });
102 $region = FS::rate_region->new({ 'regionname' => $regionname });
103 $error = $region->insert;
104 die "$here inserting region: $error\n" if $error;
107 'regionnum' => $region->regionnum,
108 'countrycode' => '1',
112 my $rate_prefix = qsearchs('rate_prefix', \%prefix);
114 $rate_prefix = FS::rate_prefix->new(\%prefix);
115 $error = $rate_prefix->insert;
116 die "$here inserting prefix: $error\n" if $error;
118 push @regionnums, $region->regionnum;
122 my $data = $lata_ocn{"$lata-$ocn"};
124 warn "$here no prefixes found for lata $lata / ocn $ocn\n";
128 # find prefixes corresponding to this LATA/OCN. there can be MANY.
130 foreach my $prefixdata (@$data) {
131 my $npa = $prefixdata->{npa}
132 or die "$here no NPA found.\n";
133 my $nxx = $prefixdata->{nxx}
134 or die "$here no NXX found.\n";
136 # show a useful regionname, but include the LATA/OCN in it to
138 my $regionname = $prefixdata->{ratecenter} . ', ' .
139 $prefixdata->{state} .
142 my $region = qsearchs('rate_region', { 'regionname' => $regionname });
144 $region = FS::rate_region->new({ 'regionname' => $regionname });
145 $error = $region->insert;
146 die "$here inserting region: $error\n" if $error;
150 'regionnum' => $region->regionnum,
151 'countrycode' => '1',
152 'npa' => $npa . $nxx,
154 my $rate_prefix = qsearchs('rate_prefix', \%prefix);
156 # don't search on unindexed fields
157 $prefix{'latanum'} = $lata;
158 $prefix{'ocn'} = $ocn;
159 $prefix{'state'} = $prefixdata->{state},
160 $rate_prefix = FS::rate_prefix->new(\%prefix);
161 $error = $rate_prefix->insert;
162 die "$here inserting prefix: $error\n" if $error;
164 push @regionnums, $region->regionnum;
165 } # foreach $prefixdata
169 $charge =~ s/^[\s\$]*//;
170 $charge = sprintf('%.05f', $charge * $multiplier);
172 foreach my $regionnum (@regionnums) {
173 my $rate = $rate{$jurisdiction}
174 or die "$here unknown jurisdiction $jurisdiction\n";
176 'ratenum' => $rate->ratenum,
177 'dest_regionnum' => $regionnum,
182 my $dest_detail = qsearchs('rate_detail', \%detail);
184 $dest_detail = FS::rate_detail->new({
187 'min_charge' => $charge,
188 'sec_granularity' => $granularity,
190 $error = $dest_detail->insert;
192 local $FS::Record::nowarn_identical = 1;
193 $dest_detail->set('min_charge' => $charge);
194 $error = $dest_detail->replace;
196 die "$here setting rate detail: $error\n" if $error;