1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
#!/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);
# 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.
####### SET THESE! ####################
my $ratenum = 3;
my $file = "/home/levinse/domestic_interstate.xls";
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;
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 = $sth->fetchrow_hashref ) {
my $lata = $row->{'lata'};
my $ocn = $row->{'ocn'};
my $state = $row->{'state'};
my $rate = $row->{'rate'};
my $npanxx = $row->{'lrn'};
my $error = '';
my $rp;
if ( $rp_cache{$npanxx} ) {
$rp = $rp_cache{$npanxx};
}
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' => $npanxx,
'ocn' => $ocn,
'state' => $state,
'latanum' => $lata,
'regionnum' => $rr->regionnum,
};
$error = $rp->insert;
fatal("can't insert rp") if $error;
$rp_cache{$npanxx} = $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;
}
$dbhfs->commit or die $dbhfs->errstr if $oldAutoCommit;
|
