diff options
Diffstat (limited to 'bin')
| -rwxr-xr-x | bin/v-rate-import | 108 |
1 files changed, 77 insertions, 31 deletions
diff --git a/bin/v-rate-import b/bin/v-rate-import index 7d52b5f7e..95e8afe92 100755 --- a/bin/v-rate-import +++ b/bin/v-rate-import @@ -8,15 +8,26 @@ use FS::rate_region; use FS::rate_detail; use FS::Record qw(qsearch qsearchs dbh); +# delete from rate; +# Create interstate and intrastate rate plans +# +# delete from rate_detail; +# delete from rate_region; +# delete from rate_prefix; + # Assumption: 1-to-1 relationship between rate_region and rate_prefix, with # two rate_detail per rate_region: one for interstate; one for intrastate # -# Create interstate and intrastate rate plans - run the script once -# per spreadsheet, setting the appropriate values below. +# run the script, setting the appropriate values below. + ####### SET THESE! #################### -my $ratenum = 3; -my $file = "/home/levinse/domestic_interstate.xls"; -my $sheet_name = 'domestic_interstate'; +my $intra_ratenum = 3; +my $inter_ratenum = 2; +my $intra_class = 1; +my $inter_class = 2; +#my $file = "/home/levinse/domestic_interstate.xls"; +my $file = "/home/ivan/vnes/VNES Domestic VoIP Termination Deck 8-9-11.csv"; +#my $sheet_name = 'domestic_interstate'; ####################################### my $user = shift or die "no user specified"; @@ -33,15 +44,21 @@ my $oldAutoCommit = $FS::UID::AutoCommit; local $FS::UID::AutoCommit = 0; my $dbhfs = dbh; -my $dbh = DBI->connect("DBI:Excel:file=$file") - or die "can't connect: $DBI::errstr"; +#my $dbh = DBI->connect("DBI:Excel:file=$file") +# or die "can't connect: $DBI::errstr"; -my $sth = $dbh->prepare("select * from $sheet_name") - or die "can't prepare: ". $dbh->errstr; -$sth->execute - or die "can't execute: ". $sth->errstr; +#my $sth = $dbh->prepare("select * from $sheet_name") +# or die "can't prepare: ". $dbh->errstr; +#$sth->execute +# or die "can't execute: ". $sth->errstr; -my @rp_cache = qsearch('rate_prefix', {} ) or die "can't cache rate_prefix"; +use Text::CSV_XS; +my $csv = Text::CSV_XS->new or die Text::CSV->error_diag; + +open(my $fh, "<$file") or die $!; +my $header = scalar(<$fh>); #NPA, NXX, LATA, State, Intrastate, Interstate + +my @rp_cache = qsearch('rate_prefix', {} );# or die "can't cache rate_prefix"; my %rp_cache = map { $_->npa => $_ } @rp_cache; sub fatal { @@ -50,17 +67,29 @@ sub fatal { die $msg; } -while ( my $row = $sth->fetchrow_hashref ) { - my $lata = $row->{'lata'}; - my $ocn = $row->{'ocn'}; - my $state = $row->{'state'}; - my $rate = $row->{'rate'}; - my $npanxx = $row->{'lrn'}; +while ( my $row = $csv->getline($fh) ) { + + #my $lata = $row->{'lata'}; + #my $ocn = $row->{'ocn'}; + #my $state = $row->{'state'}; + #my $rate = $row->{'rate'}; + #my $npanxx = $row->{'lrn'}; + + #NPA, NXX, LATA, State, Intrastate, Interstate + my $npa = $row->[0]; + my $nxx = $row->[1]; + my $lata = $row->[2]; + my $state = $row->[3]; + ( my $intra_rate = $row->[4] ) =~ s/^\s*\$//; + ( my $inter_rate = $row->[5] ) =~ s/^\s*\$//; + + next if $lata == '99999'; + my $error = ''; my $rp; - if ( $rp_cache{$npanxx} ) { - $rp = $rp_cache{$npanxx}; + if ( $rp_cache{$npa.$nxx} ) { + $rp = $rp_cache{$npa.$nxx}; } else { my $rr = new FS::rate_region { 'regionname' => $state }; @@ -68,25 +97,42 @@ while ( my $row = $sth->fetchrow_hashref ) { fatal("can't insert rr") if $error; $rp = new FS::rate_prefix { 'countrycode' => '1', - 'npa' => $npanxx, - 'ocn' => $ocn, + 'npa' => $npa.$nxx, #$npanxx + #'ocn' => $ocn, 'state' => $state, 'latanum' => $lata, 'regionnum' => $rr->regionnum, }; $error = $rp->insert; fatal("can't insert rp") if $error; - $rp_cache{$npanxx} = $rp; + $rp_cache{$npa.$nxx} = $rp; } - my $rd = new FS::rate_detail { 'ratenum' => $ratenum, - 'min_included' => 0, - 'min_charge' => $rate, - 'sec_granularity' => 60, - 'dest_regionnum' => $rp->regionnum, - }; - $error = $rd->insert; - fatal("can't insert rd") if $error; + #use Data::Dumper; + #warn Dumper($rp); + + my %hash = ( 'min_included' => 0, + 'sec_granularity' => 60, + 'dest_regionnum' => $rp->regionnum, + ); + + my $intra_rd = new FS::rate_detail { 'ratenum' => $intra_ratenum, + 'min_charge' => $intra_rate, + 'intra_class' => $intra_class, + %hash, + }; + $error = $intra_rd->insert; + fatal("can't insert (intra) rd: $error") if $error; + + my $inter_rd = new FS::rate_detail { 'ratenum' => $inter_ratenum, + 'min_charge' => $inter_rate, + 'inter_class' => $inter_class, + %hash, + }; + $error = $inter_rd->insert; + fatal("can't insert (inter) rd: $error") if $error; } +$csv->eof or $csv->error_diag (); +close $fh; $dbhfs->commit or die $dbhfs->errstr if $oldAutoCommit; |