first pass at VoIP rating

31 files changed, 1603 insertions, 22 deletions

diff --git a/ANNOUNCE.1.5.0 b/ANNOUNCE.1.5.0
index 59f1db1..692ffbc 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 a6e3d74..797323f 100644
--- a/FS/FS.pm
+++ b/FS/FS.pm
@@ -88,6 +88,14 @@ L<FS::part_pkg_option> - Package definition option class
 L<FS::pkg_svc> - Class linking package definitions (see L<FS::part_pkg>) with
 service definitions (see L<FS::part_svc>)
 
+L<FS::rate> - Rate plans for call billing
+
+L<FS::rate_region> - Rate regions for call billing
+
+L<FS::rate_prefix> - Rate region prefixes for call billing
+
+L<FS::rate_detail> - Rate plan detail for call billing
+
 L<FS::agent> - Agent (reseller) class
 
 L<FS::agent_type> - Agent type class
diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm
index 65f8d58..8990d54 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 5eddd3a..6392778 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<perlfunc/"time">.  Also see L<Time::Local> and L<Date::Parse> 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 0000000..c22e0fc
--- /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 0000000..b8a6940
--- /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<hash> 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<rate_detail>
+
+If I<rate_detail> 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<rate_detail>
+
+If I<rate_detail> 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<FS::rate_detail>) 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<FS::rate_detail>) for this rate.
+
+=cut
+
+sub rate_detail {
+  my $self = shift;
+  qsearch( 'rate_detail', { 'ratenum' => $self->ratenum } );
+}
+
+
+=back
+
+=head1 BUGS
+
+=head1 SEE ALSO
+
+L<FS::Record>, 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 0000000..93b12f7
--- /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<FS::rate>)
+
+=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<hash> 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<FS::rate>, L<FS::rate_region>, L<FS::Record>,
+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 0000000..500462a
--- /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<FS::rate_region>)
+
+=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<hash> 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<FS::rate_region>) for this prefix.
+
+=cut
+
+sub rate_region {
+  my $self = shift;
+  qsearch('rate_region', { 'regionnum' => $self->regionnum } );
+}
+
+=back
+
+=head1 BUGS
+
+=head1 SEE ALSO
+
+L<FS::rate_region>, L<FS::Record>, 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 0000000..7945f52
--- /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<hash> 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<rate_prefix> and I<dest_detail>
+
+If I<rate_prefix> 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<dest_detail> 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<rate_prefix> and I<dest_detail>
+
+If I<rate_prefix> 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<dest_detail> 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<FS::rate_prefix>) 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<FS::rate_detail>) 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<FS::Record>, schema.html from the base documentation.
+
+=cut
+
+1;
+
diff --git a/FS/MANIFEST b/FS/MANIFEST
index b2a301a..dabc08c 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 bc27c79..288b086 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 0000000..8d54204
--- /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 0000000..ae9c8bb
--- /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 0000000..163972e
--- /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 0000000..d4bd513
--- /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 0000000..6e0db8f
--- /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 0000000..074f3a5
--- /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 2838598..4e5dcab 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 d609bd5..5da6f3b 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 782e062..146310d 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 b9da085..81c9836 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 0000000..c312601
--- /dev/null
+++ b/httemplate/browse/rate.cgi
@@ -0,0 +1,34 @@
+<!-- mason kludge -->
+<%= header("Rate plan listing", menubar( 'Main Menu' => "$p#sysadmin" )) %>
+Rate plans, regions and prefixes for VoIP and call billing.<BR><BR>
+<A HREF="<%=$p%>edit/rate.cgi"><I>Add a rate plan</I></A>
+| <A HREF="<%=$p%>edit/rate_region.cgi"><I>Add a region</I></A>
+<BR><BR>
+<SCRIPT>
+function rate_areyousure(href) {
+  if (confirm("Are you sure you want to delete this rate plan?") == true)
+    window.location.href = href;
+}
+</SCRIPT>
+
+<%= table() %>
+  <TR>
+    <TH COLSPAN=2>Rate plan</TH>
+  </TR>
+
+<% foreach my $rate ( sort { 
+     $a->getfield('ratenum') <=> $b->getfield('ratenum')
+   } qsearch('rate',{}) ) {
+%>
+  <TR>
+    <TD><A HREF="<%= $p %>edit/rate.cgi?<%= $rate->ratenum %>"><%= $rate->ratenum %></A></TD>
+    <TD><A HREF="<%= $p %>edit/rate.cgi?<%= $rate->ratenum %>"><%= $rate->ratename %></A></TD>
+  </TR>
+
+<% } %>
+
+</TABLE>
+</BODY>
+</HTML>
+
+ 
diff --git a/httemplate/docs/schema.html b/httemplate/docs/schema.html
index 8523a4a..2e78f6e 100644
--- a/httemplate/docs/schema.html
+++ b/httemplate/docs/schema.html
@@ -1,4 +1,4 @@
-<head>
+
   <title>Schema reference</title>
 </head>
 <body>
@@ -453,6 +453,33 @@
         <li>svcnum - <a href="#svc_acct">account</a>
         <li>groupname
       </ul>
+    <li><a name="rate" href="man/FS/rate.html">rate</a> - Call rate plans
+      <ul>
+        <li>ratenum - primary key
+        <li>ratename
+      </ul>
+    <li><a name="rate_detail" href="man/FS/rate_detail.html">rate_detail</a> - Call rate detail
+      <ul>
+        <li>ratenum - <a href="#rate">rate plan</a>
+        <li>orig_regionnum - call origination <a href="#rate_region">region</a>
+        <li>dest_regionnum - call destination <a href="#rate_region">region</a>
+        <li>min_included - included minutes
+        <li>min_charge - charge per minute
+        <li>sec_granularity - granularity in seconds, i.e. 6 or 60
+      </ul>
+    <li><a name="rate_region" href="man/FS/rate_region.html">rate_region</a> - Call rate region
+      <ul>
+        <li>regionnum - primary key
+        <li>regionname
+      </ul>
+    <li><a name="rate_prefix" href="man/FS/rate_prefix.html">rate_prefix</a> - Call rate prefix
+      <ul>
+        <li>prefixnum - primary key
+        <li>regionnum <a href="#rate_region">rate region</a>
+        <li>countrycode
+        <li>npa
+        <li>nxx
+      </ul>
     <li><a name="msgcat" href="man/FS/msgcat.html">msgcat</a> - i18n message catalog
       <ul>
         <li>msgnum - primary key
diff --git a/httemplate/docs/upgrade10.html b/httemplate/docs/upgrade10.html
index 2f2c2da..e17f7ad 100644
--- a/httemplate/docs/upgrade10.html
+++ b/httemplate/docs/upgrade10.html
@@ -190,6 +190,39 @@ CREATE TABLE part_pkg_option (
 CREATE INDEX part_pkg_option1 ON part_export_option ( pkgpart );
 CREATE INDEX part_pkg_option2 ON part_export_option ( optionname );
 
+CREATE TABLE rate (
+    ratenum serial NOT NULL,
+    reatename 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 );
+
 DROP INDEX cust_bill_pkg1;
 
 ALTER TABLE cust_bill_pkg ADD itemdesc varchar(80) NULL;
@@ -256,7 +289,7 @@ optionally:
 mandatory again:
 
 dbdef-create username
-create-history-tables username cust_bill_pkg_detail router part_svc_router addr_block svc_broadband acct_snarf svc_external cust_pay_refund cust_pay_void
+create-history-tables username cust_bill_pkg_detail router part_svc_router addr_block svc_broadband acct_snarf svc_external cust_pay_refund cust_pay_void part_pkg_option rate rate_detail rate_region rate_prefix
 dbdef-create username
 
 apache - fix <Files> sections to include .html also
diff --git a/httemplate/edit/part_pkg.cgi b/httemplate/edit/part_pkg.cgi
index 6a06c35..dc29924 100755
--- a/httemplate/edit/part_pkg.cgi
+++ b/httemplate/edit/part_pkg.cgi
@@ -295,8 +295,11 @@ my $widget = new HTML::Widgets::SelectLayers(
                      ? $plandata{$field}
                      : $href->{$field}{'default'} ).
                  qq!" onChange="fchanged(this)">!;
-      } elsif ( $href->{$field}{'type'} eq 'select_multiple' ) {
-        $html .= qq!<SELECT MULTIPLE NAME="$field" onChange="fchanged(this)">!;
+      } elsif ( $href->{$field}{'type'} =~ /^select/ ) {
+        $html .= '<SELECT';
+        $html .= ' MULTIPLE'
+          if $href->{$field}{'type'} eq 'select_multiple';
+        $html .= qq! NAME="$field" onChange="fchanged(this)">!;
         foreach my $record (
           qsearch( $href->{$field}{'select_table'},
                    $href->{$field}{'select_hash'}   )
diff --git a/httemplate/edit/process/rate.cgi b/httemplate/edit/process/rate.cgi
new file mode 100755
index 0000000..04ff5f8
--- /dev/null
+++ b/httemplate/edit/process/rate.cgi
@@ -0,0 +1,37 @@
+<%
+
+my $ratenum = $cgi->param('ratenum');
+
+my $old = qsearchs('rate', { 'ratenum' => $ratenum } ) if $ratenum;
+
+my @rate_detail = map {
+  my $regionnum = $_->regionnum;
+  new FS::rate_detail {
+    'dest_regionnum'  => $regionnum,
+    map { $_ => $cgi->param("$_$regionnum") }
+        qw( min_included min_charge sec_granularity )
+  };
+} qsearch('rate_region', {} );
+
+my $new = new FS::rate ( {
+  map {
+    $_, scalar($cgi->param($_));
+  } fields('rate')
+} );
+
+my $error;
+if ( $ratenum ) {
+  $error = $new->replace($old, 'rate_detail' => \@rate_detail );
+} else {
+  $error = $new->insert( 'rate_detail' => \@rate_detail );
+  $ratenum = $new->getfield('ratenum');
+}
+
+if ( $error ) {
+  $cgi->param('error', $error);
+  print $cgi->redirect(popurl(2). "rate.cgi?". $cgi->query_string );
+} else { 
+  print $cgi->redirect(popurl(3). "browse/rate.cgi");
+}
+
+%>
diff --git a/httemplate/edit/process/rate_region.cgi b/httemplate/edit/process/rate_region.cgi
new file mode 100755
index 0000000..09d3d2c
--- /dev/null
+++ b/httemplate/edit/process/rate_region.cgi
@@ -0,0 +1,51 @@
+<%
+
+my $regionnum = $cgi->param('regionnum');
+
+my $old = qsearchs('rate_region', { 'regionnum' => $regionnum } ) if $regionnum;
+
+my $new = new FS::rate_region ( {
+  map {
+    $_, scalar($cgi->param($_));
+  } ( fields('rate_region') )
+} );
+
+my $countrycode = $cgi->param('countrycode');
+my @npa = split(/\s*,\s*/, $cgi->param('npa'));
+$npa[0] = '' unless @npa;
+my @rate_prefix = map {
+                        new FS::rate_prefix {
+                          'countrycode' => $countrycode,
+                          'npa'         => $_,
+                        }
+                      } @npa;
+
+my @dest_detail = map {
+  my $ratenum = $_->ratenum;
+  new FS::rate_detail {
+    'ratenum'  => $ratenum,
+    map { $_ => $cgi->param("$_$ratenum") }
+        qw( min_included min_charge sec_granularity )
+  };
+} qsearch('rate', {} );
+
+
+my $error;
+if ( $regionnum ) {
+  $error = $new->replace($old, 'rate_prefix' => \@rate_prefix,
+                               'dest_detail' => \@dest_detail, );
+} else {
+  $error = $new->insert( 'rate_prefix' => \@rate_prefix,
+                         'dest_detail' => \@dest_detail, );
+  $regionnum = $new->getfield('regionnum');
+}
+
+if ( $error ) {
+  $cgi->param('error', $error);
+  print $cgi->redirect(popurl(2). "rate_region.cgi?". $cgi->query_string );
+} else { 
+  #print $cgi->redirect(popurl(3). "browse/rate_region.cgi");
+  print $cgi->redirect(popurl(3). "browse/rate.cgi");
+}
+
+%>
diff --git a/httemplate/edit/rate.cgi b/httemplate/edit/rate.cgi
new file mode 100644
index 0000000..83a89c4
--- /dev/null
+++ b/httemplate/edit/rate.cgi
@@ -0,0 +1,94 @@
+<!-- mason kludge -->
+<%
+
+my $rate;
+if ( $cgi->param('error') ) {
+  $rate = new FS::rate ( {
+    map { $_, scalar($cgi->param($_)) } fields('rate')
+  } );
+} elsif ( $cgi->keywords ) {
+  my($query) = $cgi->keywords;
+  $query =~ /^(\d+)$/;
+  $rate = qsearchs( 'rate', { 'ratenum' => $1 } );
+} else { #adding
+  $rate = new FS::rate {};
+}
+my $action = $rate->ratenum ? 'Edit' : 'Add';
+
+my $p1 = popurl(1);
+
+my %granularity = (
+  '6'  => '6 second',
+  '60' => 'minute',
+);
+
+%>
+
+<%= header("$action Rate plan", menubar(
+      'Main Menu' => $p,
+      'View all rate plans' => "${p}browse/rate.cgi",
+    ))
+%>
+
+<% if ( $cgi->param('error') ) { %>
+<FONT SIZE="+1" COLOR="#ff0000">Error: <%= $cgi->param('error') %></FONT><BR>
+<% } %>
+
+<FORM ACTION="<%=$p1%>process/rate.cgi" METHOD=POST>
+
+<INPUT TYPE="hidden" NAME="ratenum" VALUE="<%= $rate->ratenum %>">
+
+Rate plan
+<INPUT TYPE="text" NAME="ratename" SIZE=32 VALUE="<%= $rate->ratename %>">
+<BR><BR>
+
+<%= table() %>
+<TR>
+  <TH>Region</TH>
+  <TH>Prefix(es)</TH>
+  <TH><FONT SIZE=-1>Included<BR>minutes</FONT></TH>
+  <TH><FONT SIZE=-1>Charge per<BR>minute</FONT></TH>
+  <TH><FONT SIZE=-1>Granularity</FONT></TH>
+</TR>
+
+<% foreach my $rate_region (
+     qsearch('rate_region', {}, '', 'ORDER BY regionname' )
+   ) {
+     my $n = $rate_region->regionnum;
+     my $rate_detail =
+       $rate->dest_detail($rate_region)
+       || new FS::rate_region { 'min_included'    => 0,
+                                'min_charge'      => 0,
+                                'sec_granularity' => '60'
+                              };
+%>
+  <TR>
+    <TD><A HREF="<%=$p%>edit/rate_region.cgi?<%= $rate_region->regionnum %>"><%= $rate_region->regionname %></A></TD>
+    <TD><%= $rate_region->prefixes_short %></TD>
+    <TD><INPUT TYPE="text" SIZE=5 NAME="min_included<%=$n%>" VALUE="<%= $cgi->param("min_included$n") || $rate_detail->min_included %>"></TD>
+    <TD>$<INPUT TYPE="text" SIZE=4 NAME="min_charge<%=$n%>" VALUE="<%= sprintf('%.2f', $cgi->param("min_charge$n") || $rate_detail->min_charge ) %>"></TD>
+    <TD>
+      <SELECT NAME="sec_granularity<%=$n%>">
+        <% foreach my $granularity ( keys %granularity ) { %>
+          <OPTION VALUE="<%=$granularity%>"<%= $granularity == ( $cgi->param("sec_granularity$n") || $rate_detail->sec_granularity ) ? ' SELECTED' : '' %>><%=$granularity{$granularity}%>
+        <% } %>
+      </SELECT>
+  </TR>
+<% } %>
+
+<TR>
+  <TD COLSPAN=5 ALIGN="center">
+    <A HREF="<%=$p%>edit/rate_region.cgi"><I>Add a region</I></A>
+  </TD>
+</TR>
+
+</TABLE>
+
+<BR><INPUT TYPE="submit" VALUE="<%= 
+  $rate->ratenum ? "Apply changes" : "Add rate plan"
+%>">
+
+    </FORM>
+  </BODY>
+</HTML>
+
diff --git a/httemplate/edit/rate_region.cgi b/httemplate/edit/rate_region.cgi
new file mode 100644
index 0000000..cc14dd3
--- /dev/null
+++ b/httemplate/edit/rate_region.cgi
@@ -0,0 +1,114 @@
+<!-- mason kludge -->
+<%
+
+my $rate_region;
+if ( $cgi->param('error') ) {
+  $rate_region = new FS::rate_region ( {
+    map { $_, scalar($cgi->param($_)) } fields('rate_region')
+  } );
+} elsif ( $cgi->keywords ) {
+  my($query) = $cgi->keywords;
+  $query =~ /^(\d+)$/;
+  $rate_region = qsearchs( 'rate_region', { 'regionnum' => $1 } );
+} else { #adding
+  $rate_region = new FS::rate_region {};
+}
+my $action = $rate_region->regionnum ? 'Edit' : 'Add';
+
+my $p1 = popurl(1);
+
+my %granularity = (
+  '6'  => '6 second',
+  '60' => 'minute',
+);
+
+my @rate_prefix = $rate_region->rate_prefix;
+my $countrycode = '';
+if ( @rate_prefix ) {
+  $countrycode = $rate_prefix[0]->countrycode;
+  foreach my $rate_prefix ( @rate_prefix ) {
+    eidiot 'multiple country codes per region not yet supported by web UI'
+      unless $rate_prefix->countrycode eq $countrycode;
+  }
+}
+
+%>
+
+<%= header("$action Region", menubar(
+      'Main Menu' => $p,
+      #'View all regions' => "${p}browse/rate_region.cgi",
+    ))
+%>
+
+<% if ( $cgi->param('error') ) { %>
+<FONT SIZE="+1" COLOR="#ff0000">Error: <%= $cgi->param('error') %></FONT><BR>
+<% } %>
+
+<FORM ACTION="<%=$p1%>process/rate_region.cgi" METHOD=POST>
+
+<INPUT TYPE="hidden" NAME="regionnum" VALUE="<%= $rate_region->regionnum %>">
+
+<%= ntable('#cccccc') %>
+<TR>
+  <TH ALIGN="right">Region name</TH>
+  <TD><INPUT TYPE="text" NAME="regionname" SIZE=32 VALUE="<%= $rate_region->regionname %>"></TR>
+</TR>
+
+<TR>
+  <TH ALIGN="right">Country code</TH>
+  <TD><INPUT TYPE="text" NAME="countrycode" SIZE=4 MAXLENGTH=3 VALUE="<%= $countrycode %>"></TR>
+</TR>
+
+
+<TR>
+  <TH ALIGN="right">Prefixes</TH>
+  <TD>
+    <TEXTAREA NAME="npa" WRAP=SOFT><%= join(', ', map $_->npa, @rate_prefix ) %></TEXTAREA>
+  </TD>
+</TR>
+
+</TABLE>
+
+<BR>
+<%= table() %>
+<TR>
+  <TH>Rate plan</TH>
+  <TH><FONT SIZE=-1>Included<BR>minutes</FONT></TH>
+  <TH><FONT SIZE=-1>Charge per<BR>minute</FONT></TH>
+  <TH><FONT SIZE=-1>Granularity</FONT></TH>
+</TR>
+
+<% foreach my $rate ( qsearch('rate', {}) ) {
+
+  my $n = $rate->ratenum;
+  my $rate_detail = $rate->dest_detail($rate_region)
+                    || new FS::rate_region { 'min_included'    => 0,
+                                             'min_charge'      => 0,
+                                             'sec_granularity' => '60'
+                                           };
+
+%>
+  <TR>
+    <TD><A HREF="<%=$p%>edit/rate.cgi?<%= $rate->ratenum %>"><%= $rate->ratename %></TD>
+    <TD><INPUT TYPE="text" SIZE=5 NAME="min_included<%=$n%>" VALUE="<%= $cgi->param("min_included$n") || $rate_detail->min_included %>"></TD>
+    <TD>$<INPUT TYPE="text" SIZE=4 NAME="min_charge<%=$n%>" VALUE="<%= sprintf('%.2f', $cgi->param("min_charge$n") || $rate_detail->min_charge ) %>"></TD>
+    <TD>
+      <SELECT NAME="sec_granularity<%=$n%>">
+        <% foreach my $granularity ( keys %granularity ) { %>
+          <OPTION VALUE="<%=$granularity%>"<%= $granularity == ( $cgi->param("sec_granularity$n") || $rate_detail->sec_granularity ) ? ' SELECTED' : '' %>><%=$granularity{$granularity}%>
+        <% } %>
+      </SELECT>
+  </TR>
+<% } %>
+
+</TABLE>
+
+<BR><BR><INPUT TYPE="submit" VALUE="<%= 
+  $rate_region->regionnum ? "Apply changes" : "Add region"
+%>">
+
+    </FORM>
+  </BODY>
+</HTML>
+
+
diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi
index b506ba1..9e4a55e 100644
--- a/httemplate/search/sqlradius.cgi
+++ b/httemplate/search/sqlradius.cgi
@@ -51,6 +51,15 @@
     $ip = $1;
   }
 
+  my $prefix = $cgi->param('prefix');
+  $prefix =~ s/\D//g;
+  if ( $prefix =~ /^(\d+)$/ ) {
+    $prefix = $1;
+    $prefix = "011$prefix" unless $prefix =~ /^1/;
+  } else {
+    $prefix = '';
+  }
+
   ###
   # field formatting subroutines
   ###
@@ -221,11 +230,30 @@
   # and finally, display the thing
   ### 
 
-  foreach my $part_export ( map $_->rebless, 
+  foreach my $part_export (
+    #grep $_->can('usage_sessions'), qsearch( 'part_export' )
     qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ),
     qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } )
   ) {
     %user2svc_acct = ();
+
+    my $efields = tie my %efields, 'Tie::IxHash', %fields;
+    delete $efields{'framedipaddress'} if $part_export->option('hide_ip');
+    if ( $part_export->option('hide_data') ) {
+      delete $efields{$_} foreach qw(acctinputoctets acctoutputoctets);
+    }
+    if ( $part_export->option('show_called_station') ) {
+      $efields->Splice(1, 0,
+        'calledstationid' => {
+                               'name'   => 
+                               'attrib' => 'Called-Station-ID',
+                               'fmt'    =>
+                                 sub { length($_[0]) ? shift : '&nbsp'; },
+                               'align'  => 'left',
+                             },
+      );
+    }
+
 %>
 
 <%= $part_export->exporttype %> to <%= $part_export->machine %><BR>
@@ -239,8 +267,10 @@
   <% } %>
 </TR>
 <% foreach my $session (
-  @{ $part_export->usage_sessions( $beginning, $ending, $cgi_svc_acct, $ip ) }
-) { %>
+     @{ $part_export->usage_sessions(
+          $beginning, $ending, $cgi_svc_acct, $ip, $prefix, ) }
+   ) {
+%>
   <TR>
     <% foreach my $field ( keys %fields ) { %>
       <TD ALIGN="<%= $fields{$field}->{align} %>">
diff --git a/httemplate/search/sqlradius.html b/httemplate/search/sqlradius.html
index 48a3d86..f33313f 100644
--- a/httemplate/search/sqlradius.html
+++ b/httemplate/search/sqlradius.html
@@ -15,14 +15,35 @@
   <TD></TD>
   <TD><FONT SIZE="-1"><I>(leave blank to show all users)</I></FONT></TD>
 </TR>
-<TR>
-  <TD ALIGN="right">IP address: </TD>
-  <TD><INPUT TYPE="text" NAME="ip"></TD>
-</TR>
-<TR>
-  <TD></TD>
-  <TD><FONT SIZE="-1"><I>(leave blank to show all IPs)</I></FONT></TD>
-</TR>
+
+<% my @part_export = qsearch( 'part_export', { 'exporttype' => 'sqlradius' } );
+   push @part_export,
+     qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } );
+%>
+
+<% if ( grep { ! $_->option('hide_ip') } @part_export ) { %>
+  <TR>
+    <TD ALIGN="right">IP address: </TD>
+    <TD><INPUT TYPE="text" NAME="ip"></TD>
+  </TR>
+  <TR>
+    <TD></TD>
+    <TD><FONT SIZE="-1"><I>(leave blank to show all IPs)</I></FONT></TD>
+  </TR>
+<% } %>
+
+<% if ( grep { $_->option('show_called_station') } @part_export ) { %>
+  <TR>
+    <TD ALIGN="right">Destination prefix:</TD>
+    <TD><INPUT TYPE="text" NAME="prefix"></TD>
+  </TR>
+  <TR>
+    <TD></TD>
+    <TD><FONT SIZE="-1"><I>(country code or country code and prefix)</I></FONT></TD>
+    <TD><FONT SIZE="-1"><I>(leave blank to show all destinations)</I></FONT></TD>
+  </TR>
+<% } %>
+
 <TR>
   <TD ALIGN="right">From: </TD>
   <TD>