From: levinse Date: Thu, 9 Jun 2011 02:14:45 +0000 (+0000) Subject: inter/intra-state phone billing and custom rate import script, RT13112 X-Git-Tag: freeside_2_3_0~159 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=c40ec6975d9d8275454eb9a1b076a4078d87c8df inter/intra-state phone billing and custom rate import script, RT13112 --- diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index eebcfecd2..ab4752f4b 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -2593,6 +2593,9 @@ sub tables_hashref { 'countrycode', 'varchar', '', 3, '', '', 'npa', 'varchar', 'NULL', 10, '', '', #actually the whole prefix 'nxx', 'varchar', 'NULL', 3, '', '', #actually not used + 'latanum', 'int', 'NULL', '', '', '', + 'state', 'char', 'NULL', 2, '', '', + 'ocn', 'char', 'NULL', 4, '', '', ], 'primary_key' => 'prefixnum', 'unique' => [], diff --git a/FS/FS/part_pkg/voip_cdr.pm b/FS/FS/part_pkg/voip_cdr.pm index ad7589b2b..25b8d7801 100644 --- a/FS/FS/part_pkg/voip_cdr.pm +++ b/FS/FS/part_pkg/voip_cdr.pm @@ -88,6 +88,15 @@ tie my %granularity, 'Tie::IxHash', FS::rate_detail::granularities(); 'select_key' => 'ratenum', 'select_label' => 'ratename', }, + + 'intrastate_ratenum' => { 'name' => 'Optional alternate intrastate rate plan', + 'type' => 'select', + 'select_table' => 'rate', + 'select_key' => 'ratenum', + 'select_label' => 'ratename', + 'disable_empty' => 0, + 'empty_label' => '', + }, 'min_included' => { 'name' => 'Minutes included when using the "single price per minute" rating method or when using the "prefix" rating method ("region group" billing)', }, @@ -252,8 +261,9 @@ tie my %granularity, 'Tie::IxHash', FS::rate_detail::granularities(); recur_method cutoff_day add_full_period cdr_svc_method - rating_method ratenum min_charge min_included - sec_granularity + rating_method ratenum intrastate_ratenum + min_charge min_included + sec_granularity ignore_unrateable default_prefix disable_src @@ -273,7 +283,7 @@ tie my %granularity, 'Tie::IxHash', FS::rate_detail::granularities(); 411_rewrite output_format usage_mandate summarize_usage usage_section bill_every_call bill_inactive_svcs - count_available_phones suspend_bill + count_available_phones suspend_bill ) ], 'weight' => 40, @@ -495,6 +505,30 @@ sub calc_usage { my $eff_ratenum = $cdr->is_tollfree('accountcode') ? $cust_pkg->part_pkg->option('accountcode_tollfree_ratenum') : ''; + + my $intrastate_ratenum = $cust_pkg->part_pkg->option('accountcode_tollfree_ratenum'); + if ( $intrastate_ratenum && !$cdr->is_tollfree ) { + # this is relatively easy only because: + # -assume all numbers are valid NANP numbers NOT in a fully-qualified format + # -disregard toll-free + # -disregard private or unknown numbers + # -there is exactly one record in rate_prefix for a given NPANXX + # -default to interstate if we can't find one or both of the prefixes + my $dstprefix = $cdr->dst; + $dstprefix =~ /^(\d{6})/; + $dstprefix = qsearchs('rate_prefix', { 'countrycode' => '1', + 'npa' => $1, + }) || ''; + my $srcprefix = $cdr->src; + $srcprefix =~ /^(\d{6})/; + $srcprefix = qsearchs('rate_prefix', { 'countrycode' => '1', + 'npa' => $1, + }) || ''; + $eff_ratenum = $intrastate_ratenum if ($srcprefix && $dstprefix + && $srcprefix->state && $dstprefix->state + && $srcprefix->state eq $dstprefix->state); + } + $eff_ratenum ||= $ratenum; $rate = qsearchs('rate', { 'ratenum' => $eff_ratenum }) or die "ratenum $eff_ratenum not found!"; diff --git a/FS/FS/rate_prefix.pm b/FS/FS/rate_prefix.pm index ce780fefe..585802add 100644 --- a/FS/FS/rate_prefix.pm +++ b/FS/FS/rate_prefix.pm @@ -108,6 +108,9 @@ sub check { || $self->ut_number('countrycode') || $self->ut_numbern('npa') || $self->ut_numbern('nxx') + || $self->ut_foreign_keyn('latanum', 'lata', 'latanum') + || $self->ut_textn('state') + || $self->ut_textn('ocn') ; return $error if $error; diff --git a/bin/v-rate-import b/bin/v-rate-import new file mode 100755 index 000000000..7d52b5f7e --- /dev/null +++ b/bin/v-rate-import @@ -0,0 +1,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;