5 use FS::UID qw(adminsuidsetup);
9 use FS::Record qw(qsearch qsearchs dbh);
11 # Assumption: 1-to-1 relationship between rate_region and rate_prefix, with
12 # two rate_detail per rate_region: one for interstate; one for intrastate
14 # Create interstate and intrastate rate plans - run the script once
15 # per spreadsheet, setting the appropriate values below.
16 ####### SET THESE! ####################
18 my $file = "/home/levinse/domestic_interstate.xls";
19 my $sheet_name = 'domestic_interstate';
20 #######################################
22 my $user = shift or die "no user specified";
25 local $SIG{HUP} = 'IGNORE';
26 local $SIG{INT} = 'IGNORE';
27 local $SIG{QUIT} = 'IGNORE';
28 local $SIG{TERM} = 'IGNORE';
29 local $SIG{TSTP} = 'IGNORE';
30 local $SIG{PIPE} = 'IGNORE';
32 my $oldAutoCommit = $FS::UID::AutoCommit;
33 local $FS::UID::AutoCommit = 0;
36 my $dbh = DBI->connect("DBI:Excel:file=$file")
37 or die "can't connect: $DBI::errstr";
39 my $sth = $dbh->prepare("select * from $sheet_name")
40 or die "can't prepare: ". $dbh->errstr;
42 or die "can't execute: ". $sth->errstr;
44 my @rp_cache = qsearch('rate_prefix', {} ) or die "can't cache rate_prefix";
45 my %rp_cache = map { $_->npa => $_ } @rp_cache;
49 $dbhfs->rollback if $oldAutoCommit;
53 while ( my $row = $sth->fetchrow_hashref ) {
54 my $lata = $row->{'lata'};
55 my $ocn = $row->{'ocn'};
56 my $state = $row->{'state'};
57 my $rate = $row->{'rate'};
58 my $npanxx = $row->{'lrn'};
62 if ( $rp_cache{$npanxx} ) {
63 $rp = $rp_cache{$npanxx};
66 my $rr = new FS::rate_region { 'regionname' => $state };
68 fatal("can't insert rr") if $error;
70 $rp = new FS::rate_prefix { 'countrycode' => '1',
75 'regionnum' => $rr->regionnum,
78 fatal("can't insert rp") if $error;
79 $rp_cache{$npanxx} = $rp;
82 my $rd = new FS::rate_detail { 'ratenum' => $ratenum,
84 'min_charge' => $rate,
85 'sec_granularity' => 60,
86 'dest_regionnum' => $rp->regionnum,
89 fatal("can't insert rd") if $error;
92 $dbhfs->commit or die $dbhfs->errstr if $oldAutoCommit;