3 # Usage: bin/cdr_upstream_rate.import username ratenum filename
5 # records will be imported into cdr_upstream_rate, rate_detail and rate_region
7 # Example: bin/cdr_upstream_rate.import ivan 1 ~ivan/convergent/sample_rate_table.csv
9 # username: a freeside login (from /usr/local/etc/freeside/mapsecrets)
10 # ratenum: rate plan (FS::rate) created with the web UI
13 # the following fields are currently used:
14 # - Class Code => cdr_upstream_rate.rateid
15 # - Description => rate_region.regionname
16 # (rate_detail->dest_region)
17 # - 1_rate => ( * 60 / 1_rate_seconds ) => rate_detail.min_charge
18 # - 1_rate_seconds => (used above)
19 # - 1_second_increment => rate_detail.sec_granularity
21 # the following fields are not (yet) used:
22 # - Flagfall => what's this for?
24 # - 1_cap_time => freeside doesn't have voip time caps yet...
25 # - 1_cap_cost => freeside doesn't have voip cost caps yet...
26 # - 1_repeat => not sure what this is for, sample data is all 0
29 # - 2_rate_seconds => |
30 # - 2_second_increment => | not sure what the second set of rate data
31 # - 2_cap_time => | is supposed to be for...
35 # - Carrier => probably not needed?
36 # - Start Date => not necessary?
39 use vars qw( $DEBUG );
41 use FS::UID qw(dbh adminsuidsetup);
42 use FS::Record qw(qsearchs);
44 use FS::cdr_upstream_rate;
50 my $user = shift or die &usage;
53 my $ratenum = shift or die &usage;
55 my $rate = qsearchs( 'rate', { 'ratenum' => $ratenum } );
56 die "rate plan $ratenum not found in rate table\n"
59 my $csv = new Text::CSV_XS;
60 my $hline = scalar(<>);
62 $csv->parse($hline) or die "can't parse header: $hline\n";
63 my @header = $csv->fields();
65 $FS::UID::AutoCommit = 0;
72 # #$line =~ /^(\d+),"([^"]+)"$/ or do {
74 # $line =~ /^(\d+),"([^"]+)"/ or do {
75 # warn "unparsable line: $line\n";
79 $csv->parse($line) or die "can't parse line: $line\n";
80 my @line = $csv->fields();
82 my %hash = map { $_ => shift(@line) } @header;
84 warn join('', map { "$_ => $hash{$_}\n" } keys %hash )
87 my $rate_region = new FS::rate_region {
88 'regionname' => $hash{'Description'}
91 my $error = $rate_region->insert;
94 die "error inserting into rate_region: $error\n";
96 my $dest_regionnum = $rate_region->regionnum;
97 warn "rate_region $dest_regionnum inserted\n"
100 my $rate_detail = new FS::rate_detail {
101 'ratenum' => $ratenum,
102 'dest_regionnum' => $dest_regionnum,
104 #'min_charge', => sprintf('%.5f', 60 * $hash{'1_rate'} / $hash{'1_rate_seconds'} ),
105 'min_charge', => sprintf('%.5f', $hash{'1_rate'} /
106 ( $hash{'1_rate_seconds'} / 60 )
108 'sec_granularity' => $hash{'1_second_increment'},
110 $error = $rate_detail->insert;
113 die "error inserting into rate_detail: $error\n";
115 my $ratedetailnum = $rate_detail->ratedetailnum;
116 warn "rate_detail $ratedetailnum inserted\n"
119 my $cdr_upstream_rate = new FS::cdr_upstream_rate {
120 'upstream_rateid' => $hash{'Class Code'},
121 'ratedetailnum' => $rate_detail->ratedetailnum,
123 $error = $cdr_upstream_rate->insert;
126 die "error inserting into cdr_upstream_rate: $error\n";
128 warn "cdr_upstream_rate ". $cdr_upstream_rate->upstreamratenum. " inserted\n"
131 dbh->commit or die "can't commit: ". dbh->errstr;
137 dbh->commit or die "can't commit: ". dbh->errstr;
140 "Usage:\n\ncdr_upstream_rate.import username ratenum filename\n";