From: ivan Date: Sat, 20 Nov 2004 17:26:56 +0000 (+0000) Subject: first pass at VoIP rating X-Git-Tag: BEFORE_FINAL_MASONIZE~851 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=48ba2845d0119c56971d5b724661aa37e73b49dd first pass at VoIP rating --- diff --git a/ANNOUNCE.1.5.0 b/ANNOUNCE.1.5.0 index 59f1db1f0..692ffbc02 100644 --- a/ANNOUNCE.1.5.0 +++ b/ANNOUNCE.1.5.0 @@ -21,4 +21,6 @@ 1.5.0pre7: - fix bug that could cause mis-billing on upgrades! (new installs ok) - update install documentation for 1.5 HTML::Mason or Apache::ASP install -- historical late notice viewing in web interface +# - historical late notice viewing in web interface +- VoIP billing for CDRs from RADIUS +# - promotional codes for signup diff --git a/FS/FS.pm b/FS/FS.pm index a6e3d74af..797323f54 100644 --- a/FS/FS.pm +++ b/FS/FS.pm @@ -88,6 +88,14 @@ L - Package definition option class L - Class linking package definitions (see L) with service definitions (see L) +L - Rate plans for call billing + +L - Rate regions for call billing + +L - Rate region prefixes for call billing + +L - Rate plan detail for call billing + L - Agent (reseller) class L - Agent type class diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm index 65f8d58b6..8990d54c1 100644 --- a/FS/FS/cust_svc.pm +++ b/FS/FS/cust_svc.pm @@ -555,6 +555,7 @@ sub get_session_history { #$attrib ??? + #my @part_export = $cust_svc->part_svc->part_export->can('usage_sessions'); my @part_export = $self->part_svc->part_export('sqlradius'); push @part_export, $self->part_svc->part_export('sqlradius_withdomain'); die "no sqlradius or sqlradius_withdomain export configured for this". diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm index 5eddd3a09..63927780b 100644 --- a/FS/FS/part_export/sqlradius.pm +++ b/FS/FS/part_export/sqlradius.pm @@ -12,8 +12,20 @@ tie %options, 'Tie::IxHash', 'username' => { label=>'Database username' }, 'password' => { label=>'Database password' }, 'ignore_accounting' => { - type => 'checkbox', - label=>'Ignore accounting records from this database' + type => 'checkbox', + label => 'Ignore accounting records from this database' + }, + 'hide_ip' => { + type => 'checkbox', + label => 'Hide IP address information on session reports', + }, + 'hide_data' => { + type => 'checkbox', + label => 'Hide download/upload information on session reports', + }, + 'show_called_station' => { + type => 'checkbox', + label => 'Show the Called-Station-ID on session reports', }, ; @@ -335,7 +347,7 @@ sub sqlradius_connect { #-- -=item usage_sessions TIMESTAMP_START TIMESTAMP_END [ SVC_ACCT [ IP [ SQL_SELECT ] ] ] +=item usage_sessions TIMESTAMP_START TIMESTAMP_END [ SVC_ACCT [ IP [ PREFIX [ SQL_SELECT ] ] ] ] TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see L. Also see L and L for conversion @@ -345,6 +357,9 @@ SVC_ACCT, if specified, limits the results to the specified account. IP, if specified, limits the results to the specified IP address. +PREFIX, if specified, limits the results to records with a matching +Called-Station-ID. + #SQL_SELECT defaults to * if unspecified. It can be useful to set it to #SUM(acctsessiontime) or SUM(AcctInputOctets), etc. @@ -367,6 +382,8 @@ Returns an arrayref of hashrefs with the following fields: =item acctoutputoctets +=item calledstationid + =back =cut @@ -377,6 +394,7 @@ sub usage_sessions { my( $self, $start, $end ) = splice(@_, 0, 3); my $svc_acct = @_ ? shift : ''; my $ip = @_ ? shift : ''; + my $prefix = @_ ? shift : ''; #my $select = @_ ? shift : '*'; $end ||= 2147483647; @@ -401,6 +419,7 @@ sub usage_sessions { my @fields = ( qw( username realm framedipaddress acctsessiontime acctinputoctets acctoutputoctets + calledstationid ), "$str2time acctstarttime ) as acctstarttime", "$str2time acctstoptime ) as acctstoptime", @@ -425,6 +444,11 @@ sub usage_sessions { push @param, $ip; } + if ( length($prefix) ) { + #assume sip: for now, else things get ugly trying to match /^\w+:$prefix/ + $where .= " CalledStationID LIKE 'sip:$prefix\%' AND"; + } + push @param, $start, $end; my $sth = $dbh->prepare('SELECT '. join(', ', @fields). diff --git a/FS/FS/part_pkg/voip_sqlradacct.pm b/FS/FS/part_pkg/voip_sqlradacct.pm new file mode 100644 index 000000000..c22e0fc9a --- /dev/null +++ b/FS/FS/part_pkg/voip_sqlradacct.pm @@ -0,0 +1,153 @@ +package FS::part_pkg::voip_sqlradacct; + +use strict; +use vars qw(@ISA %info); +use FS::Record qw(qsearchs qsearch); +use FS::part_pkg; +#use FS::rate; +use FS::rate_prefix; + +@ISA = qw(FS::part_pkg); + +%info = ( + 'name' => 'VoIP rating by plan of CDR records in an SQL RADIUS radacct table', + 'fields' => { + 'setup_fee' => { 'name' => 'Setup fee for this package', + 'default' => 0, + }, + 'recur_flat' => { 'name' => 'Base monthly charge for this package', + 'default' => 0, + }, + 'ratenum' => { 'name' => 'Rate plan', + 'type' => 'select', + 'select_table' => 'rate', + 'select_key' => 'ratenum', + 'select_label' => 'ratename', + }, + }, + 'fieldorder' => [qw( setup_fee recur_flat ratenum )], + 'weight' => 40, +); + +sub calc_setup { + my($self, $cust_pkg ) = @_; + $self->option('setup_fee'); +} + +sub calc_recur { + my($self, $cust_pkg, $sdate, $details ) = @_; + + my $last_bill = $cust_pkg->last_bill; + + my $ratenum = $cust_pkg->part_pkg->option('ratenum'); + + my %included_min = (); + + my $charges = 0; + + foreach my $cust_svc ( + grep { $_->part_svc->svcdb eq 'svc_acct' } $cust_pkg->cust_svc + ) { + + foreach my $session ( + $cust_svc->get_session_history( $last_bill, $$sdate ) + ) { + + ### + # look up rate details based on called station id + ### + + my $dest = $session->{'calledstationid'}; + + #remove non-phone# stuff and whitespace + $dest =~ s/\s//g; + my $proto = ''; + $dest =~ s/^(\w+):// and $proto = $1; #sip: + my $ip = ''; + $dest =~ s/\@((\d{1,3}\.){3}\d{1,3})$// and $ip = $1; # @10.54.32.1 + + #determine the country code + my $countrycode; + if ( $dest =~ /^011((\d\d)(\d))(\d+)$/ ) { + + my( $three, $two, $unknown, $rest ) = ( $1, $2, $3, $4 ); + #first look for 2 digit country code + if ( qsearch('rate_prefix', { 'countrycode' => $two } ) ) { + $countrycode = $two; + $dest = $unknown.$rest; + } else { #3 digit country code + $countrycode = $three; + $dest = $rest; + } + + } else { + $countrycode = '1'; + } + + #find a rate prefix, first look at most specific (4 digits) then 3, etc., + # finally trying the country code only + my $rate_prefix = ''; + for my $len ( reverse(1..4) ) { + $rate_prefix = qsearchs('rate_prefix', { + 'countrycode' => $countrycode, + 'npa' => { op=> 'LIKE', value=> substr($dest, 0, $len) } + } ) and last; + } + $rate_prefix ||= qsearchs('rate_prefix', { + 'countrycode' => $countrycode, + 'npa' => '', + }); + die "Can't find rate for call to countrycode $countrycode number $dest\n" + unless $rate_prefix; + + my $regionnum = $rate_prefix->regionnum; + + my $rate_detail = qsearchs('rate_detail', { + 'ratenum' => $ratenum, + 'dest_regionnum' => $regionnum, + } ); + + ### + # find the price and add detail to the invoice + ### + + $included_min{$regionnum} = $rate_detail->min_included + unless exists $included_min{$regionnum}; + + my $granularity = $rate_detail->sec_granularity; + my $seconds = $session->{'acctsessiontime'}; + $seconds += $granularity - ( $seconds % $granularity ); + my $minutes = sprintf("%.1f", $seconds / 60); + $minutes =~ s/\.0$// if $granularity == 60; + + $included_min{$regionnum} -= $minutes; + + my $charge = 0; + if ( $included_min{$regionnum} < 0 ) { + my $charge_min = 0 - $included_min{$regionnum}; + $included_min{$regionnum} = 0; + $charge = sprintf('%.2f', $rate_detail->min_charge * $charge_min ); + $charges += $charge; + } + + push @$details, + #[ + join(' - ', + "+$countrycode $dest", + $rate_prefix->rate_region->regionname, + $minutes.'m', + '$'.$charge, + #] + ) + ; + + } # $session + + } # $cust_svc + + $self->option('recur_flat') + $charges; + +} + +1; + diff --git a/FS/FS/rate.pm b/FS/FS/rate.pm new file mode 100644 index 000000000..b8a694041 --- /dev/null +++ b/FS/FS/rate.pm @@ -0,0 +1,242 @@ +package FS::rate; + +use strict; +use vars qw( @ISA ); +use FS::Record qw( qsearch qsearchs dbh ); +use FS::rate_detail; + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::rate - Object methods for rate records + +=head1 SYNOPSIS + + use FS::rate; + + $record = new FS::rate \%hash; + $record = new FS::rate { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::rate object represents an rate plan. FS::rate inherits from +FS::Record. The following fields are currently supported: + +=over 4 + +=item ratenum - primary key + +=item ratename + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new rate plan. To add the rate plan to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +# the new method can be inherited from FS::Record, if a table method is defined + +sub table { 'rate'; } + +=item insert [ , OPTION => VALUE ... ] + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +Currently available options are: I + +If I is set to an array reference of FS::rate_detail objects, the +objects will have their ratenum field set and will be inserted after this +record. + +=cut + +sub insert { + my $self = shift; + my %options = @_; + + 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 $dbh = dbh; + + my $error = $self->check; + return $error if $error; + + $error = $self->SUPER::insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + + if ( $options{'rate_detail'} ) { + foreach my $rate_detail ( @{$options{'rate_detail'}} ) { + $rate_detail->ratenum($self->ratenum); + $error = $rate_detail->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + + ''; +} + + + +=item delete + +Delete this record from the database. + +=cut + +# the delete method can be inherited from FS::Record + +=item replace OLD_RECORD [ , OPTION => VALUE ... ] + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +Currently available options are: I + +If I is set to an array reference of FS::rate_detail objects, the +objects will have their ratenum field set and will be inserted after this +record. Any existing rate_detail records associated with this record will be +deleted. + +=cut + +sub replace { + my ($new, $old) = (shift, shift); + my %options = @_; + + 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 $dbh = dbh; + + my @old_rate_detail = (); + @old_rate_detail = $old->rate_detail if $options{'rate_detail'}; + + my $error = $new->SUPER::replace($old); + if ($error) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + + foreach my $old_rate_detail ( @old_rate_detail ) { + my $error = $old_rate_detail->delete; + if ($error) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + + foreach my $rate_detail ( @{$options{'rate_detail'}} ) { + $rate_detail->ratenum($new->ratenum); + $error = $rate_detail->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + ''; + +} + +=item check + +Checks all fields to make sure this is a valid rate plan. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +# the check method should currently be supplied - FS::Record contains some +# data checking routines + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('ratenum') + || $self->ut_text('ratename') + ; + return $error if $error; + + $self->SUPER::check; +} + +=item dest_detail REGIONNUM | RATE_REGION_OBJECTD + +Returns the rate detail (see L) for this rate to the +specificed destination. + +=cut + +sub dest_detail { + my $self = shift; + my $regionnum = ref($_[0]) ? shift->regionnum : shift; + qsearchs( 'rate_detail', { 'ratenum' => $self->ratenum, + 'dest_regionnum' => $regionnum, } ); +} + +=item rate_detail + +Returns all region-specific details (see L) for this rate. + +=cut + +sub rate_detail { + my $self = shift; + qsearch( 'rate_detail', { 'ratenum' => $self->ratenum } ); +} + + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/FS/rate_detail.pm b/FS/FS/rate_detail.pm new file mode 100644 index 000000000..93b12f74d --- /dev/null +++ b/FS/FS/rate_detail.pm @@ -0,0 +1,131 @@ +package FS::rate_detail; + +use strict; +use vars qw( @ISA ); +use FS::Record qw( qsearch qsearchs ); + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::rate_detail - Object methods for rate_detail records + +=head1 SYNOPSIS + + use FS::rate_detail; + + $record = new FS::rate_detail \%hash; + $record = new FS::rate_detail { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::rate_detail object represents an call plan rate. FS::rate_detail +inherits from FS::Record. The following fields are currently supported: + +=over 4 + +=item ratenum - rate plan (see L) + +=item orig_regionnum - call origination region + +=item dest_regionnum - call destination region + +=item min_included - included minutes + +=item min_charge - charge per minute + +=item sec_granularity - granularity in seconds, i.e. 6 or 60 + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new example. To add the example to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +# the new method can be inherited from FS::Record, if a table method is defined + +sub table { 'rate_detail'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=cut + +# the insert method can be inherited from FS::Record + +=item delete + +Delete this record from the database. + +=cut + +# the delete method can be inherited from FS::Record + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=cut + +# the replace method can be inherited from FS::Record + +=item check + +Checks all fields to make sure this is a valid example. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +# the check method should currently be supplied - FS::Record contains some +# data checking routines + +sub check { + my $self = shift; + + my $error = + $self->ut_foreign_key('ratenum', 'rate', 'ratenum') + || $self->ut_foreign_keyn('orig_regionnum', 'rate_region', 'regionnum' ) + || $self->ut_foreign_key('dest_regionnum', 'rate_region', 'regionnum' ) + || $self->ut_number('min_included') + || $self->ut_money('min_charge') + || $self->ut_number('sec_granularity') + ; + return $error if $error; + + $self->SUPER::check; +} + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L, L, L, +schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/FS/rate_prefix.pm b/FS/FS/rate_prefix.pm new file mode 100644 index 000000000..500462a15 --- /dev/null +++ b/FS/FS/rate_prefix.pm @@ -0,0 +1,139 @@ +package FS::rate_prefix; + +use strict; +use vars qw( @ISA ); +use FS::Record qw( qsearch qsearchs ); +use FS::rate_region; + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::rate_prefix - Object methods for rate_prefix records + +=head1 SYNOPSIS + + use FS::rate_prefix; + + $record = new FS::rate_prefix \%hash; + $record = new FS::rate_prefix { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::rate_prefix object represents an call rating prefix. FS::rate_prefix +inherits from FS::Record. The following fields are currently supported: + +=over 4 + +=item prefixnum - primary key + +=item regionnum - call ration region (see L) + +=item countrycode + +=item npa + +=item nxx + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new prefix. To add the prefix to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +# the new method can be inherited from FS::Record, if a table method is defined + +sub table { 'rate_prefix'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=cut + +# the insert method can be inherited from FS::Record + +=item delete + +Delete this record from the database. + +=cut + +# the delete method can be inherited from FS::Record + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=cut + +# the replace method can be inherited from FS::Record + +=item check + +Checks all fields to make sure this is a valid prefix. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +# the check method should currently be supplied - FS::Record contains some +# data checking routines + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('prefixnum') + || $self->ut_foreign_key('regionnum', 'rate_region', 'regionnum' ) + || $self->ut_number('countrycode') + || $self->ut_numbern('npa') + || $self->ut_numbern('nxx') + ; + return $error if $error; + + $self->SUPER::check; +} + +=item rate_region + +Returns the rate region (see L) for this prefix. + +=cut + +sub rate_region { + my $self = shift; + qsearch('rate_region', { 'regionnum' => $self->regionnum } ); +} + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L, L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/FS/rate_region.pm b/FS/FS/rate_region.pm new file mode 100644 index 000000000..7945f5290 --- /dev/null +++ b/FS/FS/rate_region.pm @@ -0,0 +1,306 @@ +package FS::rate_region; + +use strict; +use vars qw( @ISA ); +use FS::Record qw( qsearch qsearchs dbh ); +use FS::rate_prefix; +use FS::rate_detail; + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::rate_region - Object methods for rate_region records + +=head1 SYNOPSIS + + use FS::rate_region; + + $record = new FS::rate_region \%hash; + $record = new FS::rate_region { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::rate_region object represents an call rating region. FS::rate_region +inherits from FS::Record. The following fields are currently supported: + +=over 4 + +=item regionnum - primary key + +=item regionname + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new region. To add the region to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +# the new method can be inherited from FS::Record, if a table method is defined + +sub table { 'rate_region'; } + +=item insert [ , OPTION => VALUE ... ] + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +Currently available options are: I and I + +If I is set to an array reference of FS::rate_prefix objects, the +objects will have their regionnum field set and will be inserted after this +record. + +If I is set to an array reference of FS::rate_detail objects, the +objects will have their dest_regionnum field set and will be inserted after +this record. + + +=cut + +sub insert { + my $self = shift; + my %options = @_; + + 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 $dbh = dbh; + + my $error = $self->check; + return $error if $error; + + $error = $self->SUPER::insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + + if ( $options{'rate_prefix'} ) { + foreach my $rate_prefix ( @{$options{'rate_prefix'}} ) { + $rate_prefix->regionnum($self->regionnum); + $error = $rate_prefix->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + } + + if ( $options{'dest_detail'} ) { + foreach my $rate_detail ( @{$options{'dest_detail'}} ) { + $rate_detail->dest_regionnum($self->regionnum); + $error = $rate_detail->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + + ''; +} + +=item delete + +Delete this record from the database. + +=cut + +# the delete method can be inherited from FS::Record + +=item replace OLD_RECORD [ , OPTION => VALUE ... ] + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +Currently available options are: I and I + +If I is set to an array reference of FS::rate_prefix objects, the +objects will have their regionnum field set and will be inserted after this +record. Any existing rate_prefix records associated with this record will be +deleted. + +If I is set to an array reference of FS::rate_detail objects, the +objects will have their dest_regionnum field set and will be inserted after +this record. Any existing rate_detail records associated with this record will +be deleted. + +=cut + +sub replace { + my ($new, $old) = (shift, shift); + my %options = @_; + + 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 $dbh = dbh; + + my @old_rate_prefix = (); + @old_rate_prefix = $old->rate_prefix if $options{'rate_prefix'}; + my @old_dest_detail = (); + @old_dest_detail = $old->dest_detail if $options{'dest_detail'}; + + my $error = $new->SUPER::replace($old); + if ($error) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + + foreach my $old_rate_prefix ( @old_rate_prefix ) { + my $error = $old_rate_prefix->delete; + if ($error) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + foreach my $old_dest_detail ( @old_dest_detail ) { + my $error = $old_dest_detail->delete; + if ($error) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + + foreach my $rate_prefix ( @{$options{'rate_prefix'}} ) { + $rate_prefix->regionnum($new->regionnum); + $error = $rate_prefix->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + foreach my $rate_detail ( @{$options{'dest_detail'}} ) { + $rate_detail->dest_regionnum($new->regionnum); + $error = $rate_detail->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + ''; + +} + +=item check + +Checks all fields to make sure this is a valid region. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +# the check method should currently be supplied - FS::Record contains some +# data checking routines + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('regionnum') + || $self->ut_text('regionname') + ; + return $error if $error; + + $self->SUPER::check; +} + +=item rate_prefix + +Returns all prefixes (see L) for this region. + +=cut + +sub rate_prefix { + my $self = shift; + + sort { $a->countrycode cmp $b->countrycode + or $a->npa cmp $b->npa + or $a->nxx cmp $b->nxx + } + qsearch( 'rate_prefix', { 'regionnum' => $self->regionnum } ); +} + +=item dest_detail + +Returns all rate details (see L) for this region as a +destionation. + +=cut + +sub dest_detail { + my $self = shift; + qsearch( 'rate_detail', { 'dest_regionnum' => $self->regionnum, } ); +} + +=item prefixes_short + +Returns a string representing all the prefixes for this region. + +=cut + +sub prefixes_short { + my $self = shift; + + my $countrycode = ''; + my $out = ''; + + foreach my $rate_prefix ( $self->rate_prefix ) { + if ( $countrycode ne $rate_prefix->countrycode ) { + $out =~ s/,$//; + $countrycode = $rate_prefix->countrycode; + $out.= " $countrycode-"; + } + $out .= $rate_prefix->npa. ','; + } + $out =~ s/,$//; + + $out; +} + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/MANIFEST b/FS/MANIFEST index b2a301a89..dabc08c70 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -105,6 +105,7 @@ FS/part_pkg/sql_external.pm FS/part_pkg/sql_generic.pm FS/part_pkg/sqlradacct_hour.pm FS/part_pkg/subscription.pm +FS/part_pkg/voip_sqlradacct.pm FS/part_pop_local.pm FS/part_referral.pm FS/part_svc.pm @@ -112,6 +113,10 @@ FS/part_svc_column.pm FS/part_svc_router.pm FS/part_virtual_field.pm FS/pkg_svc.pm +FS/rate.pm +FS/rate_detail.pm +FS/rate_region.pm +FS/rate_prefix.pm FS/svc_Common.pm FS/svc_acct.pm FS/svc_acct_pop.pm @@ -213,6 +218,7 @@ t/part_pkg-sql_external.t t/part_pkg-sql_generic.t t/part_pkg-sqlradacct_hour.t t/part_pkg-subscription.t +t/part_pkg-voip_sqlradacct.t t/part_pop_local.t t/part_referral.t t/part_svc.t @@ -220,6 +226,10 @@ t/part_svc_column.t t/pkg_svc.t t/port.t t/prepay_credit.t +t/rate.t +t/rate_detail.t +t/rate_region.t +t/rate_prefix.t t/radius_usergroup.t t/session.t t/svc_acct.t diff --git a/FS/bin/freeside-setup b/FS/bin/freeside-setup index bc27c79d0..288b08663 100755 --- a/FS/bin/freeside-setup +++ b/FS/bin/freeside-setup @@ -270,7 +270,7 @@ foreach my $country ( sort map uc($_), all_country_codes ) { #billing events foreach my $aref ( - [ 'COMP', 'Comp invoice', '$cust_bill->comp();', 30, 'comp' ], + #[ 'COMP', 'Comp invoice', '$cust_bill->comp();', 30, 'comp' ], [ 'CARD', 'Batch card', '$cust_bill->batch_card();', 40, 'batch-card' ], [ 'BILL', 'Send invoice', '$cust_bill->send();', 50, 'send' ], [ 'DCRD', 'Send invoice', '$cust_bill->send();', 50, 'send' ], @@ -1160,6 +1160,53 @@ sub tables_hash_hack { 'index' => [ [ 'pkgpart' ], [ 'optionname' ] ], }, + 'rate' => { + 'columns' => [ + 'ratenum', 'serial', '', '', + 'ratename', 'varchar', '', $char_d, + ], + 'primary_key' => 'ratenum', + 'unique' => [], + 'index' => [], + }, + + 'rate_detail' => { + 'columns' => [ + 'ratenum', 'int', '', '', + 'orig_regionnum', 'int', 'NULL', '', + 'dest_regionnum', 'int', '', '', + 'min_included', 'int', '', '', + 'min_charge', @money_type, + 'sec_granularity', 'int', '', '', + #time period (link to table of periods)? + ], + 'primary_key' => '', + 'unique' => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ], + 'index' => [], + }, + + 'rate_region' => { + 'columns' => [ + 'regionnum', 'serial', '', '', + 'regionname', 'varchar', '', $char_d, + 'primary_key' => 'regionnum', + 'unique' => []. + 'index' => [], + }, + + 'rate_prefix' => { + 'columns' => [ + 'prefixnum', 'serial', '', '', + 'regionnum', 'int', '', '',, + 'countrycode', 'varchar', '', 3, + 'npa', 'varchar', 'NULL', 4, #not 3? + 'nxx', 'varchar', 'NULL', 3, + ], + 'primary_key' => 'prefixnum', + 'unique' => []. + 'index' => [ [ 'countrycode' ], [ 'regionnum' ] ], + + ); %tables; diff --git a/FS/t/part_pkg-voip_sqlradacct.t b/FS/t/part_pkg-voip_sqlradacct.t new file mode 100644 index 000000000..8d542044d --- /dev/null +++ b/FS/t/part_pkg-voip_sqlradacct.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::part_pkg::voip_sqlradacct; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/rate.t b/FS/t/rate.t new file mode 100644 index 000000000..ae9c8bb31 --- /dev/null +++ b/FS/t/rate.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::rate; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/rate_detail.t b/FS/t/rate_detail.t new file mode 100644 index 000000000..163972e81 --- /dev/null +++ b/FS/t/rate_detail.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::rate_detail; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/rate_prefix.t b/FS/t/rate_prefix.t new file mode 100644 index 000000000..d4bd51363 --- /dev/null +++ b/FS/t/rate_prefix.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::rate_prefix; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/rate_region.t b/FS/t/rate_region.t new file mode 100644 index 000000000..6e0db8f27 --- /dev/null +++ b/FS/t/rate_region.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::rate_region; +$loaded=1; +print "ok 1\n"; diff --git a/README.1.5.0pre7 b/README.1.5.0pre7 new file mode 100644 index 000000000..074f3a5bf --- /dev/null +++ b/README.1.5.0pre7 @@ -0,0 +1,37 @@ +CREATE TABLE rate ( + ratenum serial NOT NULL, + ratename varchar(80) NOT NULL, + PRIMARY KEY (ratenum) +); + +CREATE TABLE rate_detail ( + ratenum int NOT NULL, + orig_regionnum int NULL, + dest_regionnum int NOT NULL, + min_included int NOT NULL, + min_charge decimal(10,2) NOT NULL, + sec_granularity int NOT NULL +); +CREATE UNIQUE INDEX rate_detail1 ON rate_detail ( ratenum, orig_regionnum, dest_regionnum ); + +CREATE TABLE rate_region ( + regionnum serial NOT NULL, + regionname varchar(80) NOT NULL, + PRIMARY KEY (regionnum) +); + +CREATE TABLE rate_prefix ( + prefixnum serial NOT NULL, + regionnum int NOT NULL, + countrycode varchar(3) NOT NULL, + npa varchar(4) NULL, + nxx varchar(3) NULL, + PRIMARY KEY (prefixnum) +); +CREATE INDEX rate_prefix1 ON rate_prefix ( countrycode ); +CREATE INDEX rate_prefix2 ON rate_prefix ( regionnum ); + +dbdef-create username +create-history-tables username rate rate_detail rate_region rate_prefix +dbdef-create username + diff --git a/SCHEMA_CHANGE b/SCHEMA_CHANGE index 2838598f9..4e5dcabcf 100644 --- a/SCHEMA_CHANGE +++ b/SCHEMA_CHANGE @@ -1,6 +1,6 @@ FS/bin/freeside-setup httemplate/docs/upgrade10.html -README.1.5.0pre6 +README.1.5.0preX httemplate/docs/schema.html for new tables: edit FS/FS.pm, add a new FS/FS/table_name.pm and FS/t/table_name.t, edit FS/MANIFEST diff --git a/eg/table_template.pm b/eg/table_template.pm index d609bd544..5da6f3b28 100644 --- a/eg/table_template.pm +++ b/eg/table_template.pm @@ -93,7 +93,13 @@ and replace methods. sub check { my $self = shift; - ''; #no error + my $error = + $self->ut_numbern('primary_key') + || $self->ut_number('validate_other_fields') + ; + return $error if $error; + + $self->SUPER::check; } =back diff --git a/htetc/global.asa b/htetc/global.asa index 782e06223..146310d57 100644 --- a/htetc/global.asa +++ b/htetc/global.asa @@ -71,6 +71,9 @@ use FS::part_export; use FS::part_export_option; use FS::export_svc; use FS::msgcat; +use FS::rate; +use FS::rate_region; +use FS::rate_prefix; sub Script_OnStart { $Response->AddHeader('Cache-control' => 'no-cache'); diff --git a/htetc/handler.pl b/htetc/handler.pl index b9da085a1..81c983692 100644 --- a/htetc/handler.pl +++ b/htetc/handler.pl @@ -154,6 +154,9 @@ sub handler use FS::part_export_option; use FS::export_svc; use FS::msgcat; + use FS::rate; + use FS::rate_region; + use FS::rate_prefix; if ( %%%RT_ENABLED%%% ) { eval ' diff --git a/httemplate/browse/rate.cgi b/httemplate/browse/rate.cgi new file mode 100644 index 000000000..c31260166 --- /dev/null +++ b/httemplate/browse/rate.cgi @@ -0,0 +1,34 @@ + +<%= header("Rate plan listing", menubar( 'Main Menu' => "$p#sysadmin" )) %> +Rate plans, regions and prefixes for VoIP and call billing.

+Add a rate plan +| Add a region +

+ + +<%= table() %> + + Rate plan + + +<% foreach my $rate ( sort { + $a->getfield('ratenum') <=> $b->getfield('ratenum') + } qsearch('rate',{}) ) { +%> + + <%= $rate->ratenum %> + <%= $rate->ratename %> + + +<% } %> + + + + + + diff --git a/httemplate/docs/schema.html b/httemplate/docs/schema.html index 8523a4a79..2e78f6e9e 100644 --- a/httemplate/docs/schema.html +++ b/httemplate/docs/schema.html @@ -1,4 +1,4 @@ - + Schema reference @@ -453,6 +453,33 @@
  • svcnum - account
  • groupname +
  • rate - Call rate plans +
      +
    • ratenum - primary key +
    • ratename +
    +
  • rate_detail - Call rate detail +
      +
    • ratenum - rate plan +
    • orig_regionnum - call origination region +
    • dest_regionnum - call destination region +
    • min_included - included minutes +
    • min_charge - charge per minute +
    • sec_granularity - granularity in seconds, i.e. 6 or 60 +
    +
  • rate_region - Call rate region +
      +
    • regionnum - primary key +
    • regionname +
    +
  • rate_prefix - Call rate prefix +
      +
    • prefixnum - primary key +
    • regionnum rate region +
    • countrycode +
    • npa +
    • nxx +
  • msgcat - i18n message catalog