diff options
Diffstat (limited to 'bin/v-rate-import')
| -rwxr-xr-x | bin/v-rate-import | 138 |
1 files changed, 138 insertions, 0 deletions
diff --git a/bin/v-rate-import b/bin/v-rate-import new file mode 100755 index 000000000..c913c33e0 --- /dev/null +++ b/bin/v-rate-import @@ -0,0 +1,138 @@ +#!/usr/bin/perl + +use strict; +use DBI; +use FS::UID qw(adminsuidsetup); +use FS::rate_prefix; +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 +# +# run the script, setting the appropriate values below. + +####### SET THESE! #################### +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"; +adminsuidsetup $user; + +local $SIG{HUP} = 'IGNORE'; +local $SIG{INT} = 'IGNORE'; +local $SIG{QUIT} = 'IGNORE'; +local $SIG{TERM} = 'IGNORE'; +local $SIG{TSTP} = 'IGNORE'; +local $SIG{PIPE} = 'IGNORE'; + +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 $sth = $dbh->prepare("select * from $sheet_name") +# or die "can't prepare: ". $dbh->errstr; +#$sth->execute +# or die "can't execute: ". $sth->errstr; + +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 { + my $msg = shift; + $dbhfs->rollback if $oldAutoCommit; + die $msg; +} + +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{$npa.$nxx} ) { + $rp = $rp_cache{$npa.$nxx}; + } + else { + my $rr = new FS::rate_region { 'regionname' => $state }; + $error = $rr->insert; + fatal("can't insert rr") if $error; + + $rp = new FS::rate_prefix { 'countrycode' => '1', + '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{$npa.$nxx} = $rp; + } + + #use Data::Dumper; + #warn Dumper($rp); + + my %hash = ( 'min_included' => 0, + 'sec_granularity' => 6, #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; |