5 use FS::UID qw(adminsuidsetup);
9 use FS::Record qw(qsearch qsearchs dbh);
12 # Create interstate and intrastate rate plans
14 # #delete from rate_detail;
15 # #delete from rate_region;
16 # #delete from rate_prefix;
18 # Assumption: 1-to-1 relationship between rate_region and rate_prefix, with
19 # two rate_detail per rate_region: one for interstate; one for intrastate
21 # run the script, setting the appropriate values below.
23 ####### SET THESE! ####################
27 my $intra_ratenum = 5;
28 my $inter_ratenum = 6;
31 #my $file = "/home/levinse/domestic_interstate.xls";
32 #my $file = "/home/ivan/vnes/New VNES Rate Table.xlsx";
33 my $file = "/home/ivan/New VNES Rate Table.csv";
34 #my $sheet_name = 'Sheet1';
35 #######################################
37 my $user = shift or die "no user specified";
40 local $SIG{HUP} = 'IGNORE';
41 local $SIG{INT} = 'IGNORE';
42 local $SIG{QUIT} = 'IGNORE';
43 local $SIG{TERM} = 'IGNORE';
44 local $SIG{TSTP} = 'IGNORE';
45 local $SIG{PIPE} = 'IGNORE';
47 my $oldAutoCommit = $FS::UID::AutoCommit;
48 local $FS::UID::AutoCommit = 0;
51 #my $dbh = DBI->connect("DBI:Excel:file=$file")
52 # or die "can't connect: $DBI::errstr";
54 #my $sth = $dbh->prepare("select * from $sheet_name")
55 # or die "can't prepare: ". $dbh->errstr;
57 # or die "can't execute: ". $sth->errstr;
60 my $csv = Text::CSV_XS->new or die Text::CSV->error_diag;
62 open(my $fh, "<$file") or die $!;
63 my $header = scalar(<$fh>); #NPA, NXX, LATA, State, Intrastate, Interstate
65 my @rp_cache = qsearch('rate_prefix', {} );# or die "can't cache rate_prefix";
66 my %rp_cache = map { $_->npa => $_ } @rp_cache;
70 $dbhfs->rollback; # if $oldAutoCommit;
74 while ( my $row = $csv->getline($fh) ) {
76 #my $lata = $row->{'lata'};
77 #my $ocn = $row->{'ocn'};
78 #my $state = $row->{'state'};
79 #my $rate = $row->{'rate'};
80 #my $npanxx = $row->{'lrn'};
82 #NPA, NXX, LATA, State, Intrastate, Interstate
86 my $state = $row->[3];
87 ( my $intra_rate = $row->[4] ) =~ s/^\s*\$//;
88 ( my $inter_rate = $row->[5] ) =~ s/^\s*\$//;
90 #in the new data, instead of being "$-", these are all identical to the
91 #rate from the immediatelly preceeding cell/NPANXX... probably an artifact
92 #rather than real rates then? so also skipping this import
94 next if $lata == '99999';
99 if ( $rp_cache{$npa.$nxx} ) {
100 $rp = $rp_cache{$npa.$nxx};
104 #warn "inserting new rate_region / rate_prefix for $npa-$nxx\n";
105 die "new rate_region / rate_prefix $npa-$nxx\n";
107 my $rr = new FS::rate_region { 'regionname' => $state };
108 $error = $rr->insert;
109 fatal("can't insert rr") if $error;
111 $rp = new FS::rate_prefix { 'countrycode' => '1',
112 'npa' => $npa.$nxx, #$npanxx
116 'regionnum' => $rr->regionnum,
118 $error = $rp->insert;
119 fatal("can't insert rp") if $error;
120 $rp_cache{$npa.$nxx} = $rp;
126 my %hash = ( 'dest_regionnum' => $rp->regionnum, );
128 my %intra_hash = ( 'ratenum' => $intra_ratenum,
129 'intra_class' => $intra_class,
133 my $intra_rd = qsearchs( 'rate_detail', \%intra_hash )
134 || die; #new FS::rate_detail \%intra_hash;
136 $intra_rd->min_included( 0 );
137 $intra_rd->sec_granularity( 6 ); #60
138 die if $intra_rd->min_charge > 0;
139 $intra_rd->min_charge( $intra_rate );
141 #$error = $intra_rd->ratedetailnum ? $intra_rd->replace : $intra_rd->insert;
142 $error = $intra_rd->replace;
143 fatal("can't insert/replace (intra) rd: $error") if $error;
145 my %inter_hash = ( 'ratenum' => $inter_ratenum,
146 'inter_class' => $inter_class,
150 my $inter_rd = qsearchs( 'rate_detail', \%inter_hash )
151 || die; #new FS::rate_detail \%inter_hash;
153 $inter_rd->min_included( 0 );
154 $inter_rd->sec_granularity( 6 ); #60
155 die if $inter_rd->min_charge > 0;
156 $inter_rd->min_charge( $inter_rate );
158 #$error = $inter_rd->ratedetailnum ? $inter_rd->replace : $inter_rd->insert;
159 $error = $inter_rd->replace;
160 fatal("can't insert/replace (inter) rd: $error") if $error;
162 $csv->eof or $csv->error_diag ();
166 $dbhfs->rollback or die $dbhfs->errstr; # if $oldAutoCommit;
168 $dbhfs->commit or die $dbhfs->errstr; # if $oldAutoCommit;