Merge branch 'FREESIDE_2_3_BRANCH' of git.freeside.biz:/home/git/freeside into FREESIDE_2_3_BRANCH

19 files changed, 410 insertions, 139 deletions

diff --git a/FS/FS/ClientAPI/MyAccount.pm b/FS/FS/ClientAPI/MyAccount.pm
index 7bc3011d2..08e3f7e4b 100644
--- a/FS/FS/ClientAPI/MyAccount.pm
+++ b/FS/FS/ClientAPI/MyAccount.pm
@@ -570,6 +570,12 @@ sub edit_info {
     or return { 'error' => "unknown custnum $custnum" };
 
   my $new = new FS::cust_main { $cust_main->hash };
+  # Avoid accidentally changing the service address.
+  if ( !$new->has_ship_address ) {
+    $new->set( $_ => $new->get($_) )
+      foreach $new->addr_fields;
+  }
+
   $new->set( $_ => $p->{$_} )
     foreach grep { exists $p->{$_} } @cust_main_editable_fields;
 
@@ -729,7 +735,7 @@ sub payment_info {
     $return{payinfo2} = $payinfo2;
     $return{paytype}  = $cust_main->paytype;
     $return{paystate} = $cust_main->paystate;
-
+    $return{payname}  = $cust_main->payname;	# override 'first/last name' default from above, if any.  Is instution-name here.  (#15819)
   }
 
   if ( $conf->config('prepayment_discounts-credit_type') ) {
@@ -927,9 +933,17 @@ sub do_process_payment {
     my $new = new FS::cust_main { $cust_main->hash };
     if ($payby eq 'CARD' || $payby eq 'DCRD') {
       $new->set( $_ => $validate->{$_} )
-        foreach qw( payname paystart_month paystart_year payissue payip
-                    address1 address2 city state zip country );
+        foreach qw( payname paystart_month paystart_year payissue payip );
       $new->set( 'payby' => $validate->{'auto'} ? 'CARD' : 'DCRD' );
+
+      # Avoid accidentally changing the service address.
+      if ( !$new->has_ship_address ) {
+        $new->set( "ship_$_" => $new->get($_) ) 
+          foreach $new->addr_fields;
+      }
+      $new->set( $_ => $validate->{$_} )
+        foreach qw(address1 address2 city state country zip);
+
     } elsif ($payby eq 'CHEK' || $payby eq 'DCHK') {
       $new->set( $_ => $validate->{$_} )
         foreach qw( payname payip paytype paystate
diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index 047386478..535504cbf 100644
--- a/FS/FS/Conf.pm
+++ b/FS/FS/Conf.pm
@@ -3030,6 +3030,24 @@ and customer address. Include units.',
   },
 
   {
+    'key'         => 'cust_location-label_prefix',
+    'section'     => 'UI',
+    'description' => 'Optional "site ID" to show in the location label',
+    'type'        => 'select',
+    'select_hash' => [ '' => '',
+                       'CoStAg' => 'CoStAgXXXXX (country, state, agent name, locationnum)',
+                      ],
+  },
+
+  {
+    'key'         => 'cust_location-agent_code',
+    'section'     => 'UI',
+    'description' => 'Optional agent string for cust_location-label_prefix',
+    'type'        => 'text',
+    'per_agent'   => 1,
+  },
+
+  {
     'key'         => 'cust_pkg-display_times',
     'section'     => 'UI',
     'description' => 'Display full timestamps (not just dates) for customer packages.  Useful if you are doing real-time things like hourly prepaid.',
@@ -3957,6 +3975,13 @@ and customer address. Include units.',
   },
 
   {
+    'key'         => 'unsuspend_email_admin',
+    'section'     => '',
+    'description' => 'Destination admin email address to enable unsuspension notices',
+    'type'        => 'text',
+  },
+  
+  {
     'key'         => 'email_report-subject',
     'section'     => '',
     'description' => 'Subject for reports emailed by freeside-fetch.  Defaults to "Freeside report".',
diff --git a/FS/FS/Conf_compat17.pm b/FS/FS/Conf_compat17.pm
index 6685935d3..2e4bb055f 100644
--- a/FS/FS/Conf_compat17.pm
+++ b/FS/FS/Conf_compat17.pm
@@ -2458,6 +2458,13 @@ httemplate/docs/config.html
   },
 
   {
+    'key'         => 'unsuspend_email_admin',
+    'section'     => '',
+    'description' => 'Destination admin email address to enable unsuspension notices',
+    'type'        => 'text',
+  },
+  
+  {
     'key'         => 'email_report-subject',
     'section'     => '',
     'description' => 'Subject for reports emailed by freeside-fetch.  Defaults to "Freeside report".',
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index fdf29e0a0..a403af309 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -2544,7 +2544,7 @@ sub tables_hashref {
         'plan_id',             'varchar', 'NULL', $char_d, '', '',
       ],
       'primary_key' => 'svcnum',
-      'unique'      => [ [ 'mac_addr' ] ],
+      'unique'      => [ [ 'ip_addr' ], [ 'mac_addr' ] ],
       'index'       => [],
     },
 
diff --git a/FS/FS/cdr/cia.pm b/FS/FS/cdr/cia.pm
index 070f3fb0d..ca44c0fdf 100644
--- a/FS/FS/cdr/cia.pm
+++ b/FS/FS/cdr/cia.pm
@@ -20,11 +20,12 @@ use FS::cdr qw(_cdr_date_parser_maker);
     skip(2),          # Conference Start Time, Conference End Time
     _cdr_date_parser_maker('startdate'),  # Connect Time
     _cdr_date_parser_maker('enddate'),    # Disconnect Time
+    skip(1),          # Duration
     sub { my($cdr, $data, $conf, $param) = @_;
           $cdr->duration($data);
           $cdr->billsec( $data);
-    },                # Duration
-    skip(2),          # Roundup Duration, User Name
+    },                # Roundup Duration
+    skip(1),          # User Name
     'dst',            # DNIS
     'src',            # ANI
     skip(2),          # Call Type, Toll Free, 
diff --git a/FS/FS/cust_location.pm b/FS/FS/cust_location.pm
index a5250ec05..a99fa17d8 100644
--- a/FS/FS/cust_location.pm
+++ b/FS/FS/cust_location.pm
@@ -408,6 +408,42 @@ sub dealternize {
   '';
 }
 
+=item location_label
+
+Returns the label of the location object, with an optional site ID
+string (based on the cust_location-label_prefix config option).
+
+=cut
+
+sub location_label {
+  my $self = shift;
+  my %opt = @_;
+  my $conf = new FS::Conf;
+  my $prefix = '';
+  my $format = $conf->config('cust_location-label_prefix') || '';
+  if ( $format eq 'CoStAg' ) {
+    my $cust_or_prospect;
+    if ( $self->custnum ) {
+      $cust_or_prospect = FS::cust_main->by_key($self->custnum);
+    }
+    elsif ( $self->prospectnum )  {
+      $cust_or_prospect = FS::prospect_main->by_key($self->prospectnum);
+    }
+    my $agent = $conf->config('cust_location-agent_code', 
+                  $cust_or_prospect->agentnum)
+                || $cust_or_prospect->agent->agent;
+    # else this location is invalid
+    $prefix = uc( join('',
+        $self->country,
+        ($self->state =~ /^(..)/),
+        ($agent =~ /^(..)/),
+        sprintf('%05d', $self->locationnum)
+    ) );
+  }
+  $prefix .= ($opt{join_string} ||  ': ') if $prefix;
+  $prefix . $self->SUPER::location_label(%opt);
+}
+
 =back
 
 =head1 BUGS
diff --git a/FS/FS/cust_main_county.pm b/FS/FS/cust_main_county.pm
index e01e1d2af..ae3b66cd9 100644
--- a/FS/FS/cust_main_county.pm
+++ b/FS/FS/cust_main_county.pm
@@ -176,7 +176,7 @@ with different tax classes.
 sub sql_taxclass_sameregion {
   my $self = shift;
 
-  my $same_query = 'SELECT taxclass FROM cust_main_county '.
+  my $same_query = 'SELECT DISTINCT taxclass FROM cust_main_county '.
                    ' WHERE taxnum != ? AND country = ?';
   my @same_param = ( 'taxnum', 'country' );
   foreach my $opt_field (qw( state county )) {
diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index a0e21fec0..27cdc9ec8 100644
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -1176,6 +1176,8 @@ sub unsuspend {
   
   } #if $date 
 
+  my @labels = ();
+
   foreach my $cust_svc (
     qsearch('cust_svc',{'pkgnum'=> $self->pkgnum } )
   ) {
@@ -1195,6 +1197,8 @@ sub unsuspend {
         $dbh->rollback if $oldAutoCommit;
         return $error;
       }
+      my( $label, $value ) = $cust_svc->label;
+      push @labels, "$label: $value";
     }
 
   }
@@ -1225,6 +1229,29 @@ sub unsuspend {
     return $error;
   }
 
+  if ( $conf->config('unsuspend_email_admin') ) {
+ 
+    my $error = send_email(
+      'from'    => $conf->config('invoice_from', $self->cust_main->agentnum),
+                                 #invoice_from ??? well as good as any
+      'to'      => $conf->config('unsuspend_email_admin'),
+      'subject' => 'FREESIDE NOTIFICATION: Customer package unsuspended',       'body'    => [
+        "This is an automatic message from your Freeside installation\n",
+        "informing you that the following customer package has been unsuspended:\n",
+        "\n",
+        'Customer: #'. $self->custnum. ' '. $self->cust_main->name. "\n",
+        'Package : #'. $self->pkgnum. " (". $self->part_pkg->pkg_comment. ")\n",
+        ( map { "Service : $_\n" } @labels ),
+      ],
+    );
+
+    if ( $error ) {
+      warn "WARNING: can't send unsuspension admin email (unsuspending anyway): ".
+           "$error\n";
+    }
+
+  }
+
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
 
   ''; #no errors
@@ -3375,7 +3402,13 @@ sub location_sql {
 
   # '?' placeholders in _location_sql_where
   my $x = $ornull ? 3 : 2;
-  my @bill_param = ( ('city')x3, ('county')x$x, ('state')x$x, 'country' );
+  my @bill_param = ( 
+    ('district')x3,
+    ('city')x3, 
+    ('county')x$x,
+    ('state')x$x,
+    'country'
+  );
 
   my $main_where;
   my @main_param;
@@ -3434,16 +3467,17 @@ sub _location_sql_where {
 
   $ornull = $ornull ? ' OR ? IS NULL ' : '';
 
-  my $or_empty_city   = " OR ( ? = '' AND $table.${prefix}city   IS NULL ) ";
-  my $or_empty_county = " OR ( ? = '' AND $table.${prefix}county IS NULL ) ";
-  my $or_empty_state =  " OR ( ? = '' AND $table.${prefix}state  IS NULL ) ";
+  my $or_empty_city     = " OR ( ? = '' AND $table.${prefix}city     IS NULL )";
+  my $or_empty_county   = " OR ( ? = '' AND $table.${prefix}county   IS NULL )";
+  my $or_empty_state    = " OR ( ? = '' AND $table.${prefix}state    IS NULL )";
 
 #        ( $table.${prefix}city    = ? $or_empty_city   $ornull )
   "
-        ( $table.${prefix}city    = ? OR ? = '' OR CAST(? AS text) IS NULL )
-    AND ( $table.${prefix}county  = ? $or_empty_county $ornull )
-    AND ( $table.${prefix}state   = ? $or_empty_state  $ornull )
-    AND   $table.${prefix}country = ?
+        ( $table.${prefix}district = ? OR ? = '' OR CAST(? AS text) IS NULL )
+    AND ( $table.${prefix}city     = ? OR ? = '' OR CAST(? AS text) IS NULL )
+    AND ( $table.${prefix}county   = ? $or_empty_county $ornull )
+    AND ( $table.${prefix}state    = ? $or_empty_state  $ornull )
+    AND   $table.${prefix}country  = ?
   ";
 }
 
diff --git a/FS/FS/part_event/Action/cust_bill_send_csv_ftp.pm b/FS/FS/part_event/Action/cust_bill_send_csv_ftp.pm
index bf472683f..71bbaa89b 100644
--- a/FS/FS/part_event/Action/cust_bill_send_csv_ftp.pm
+++ b/FS/FS/part_event/Action/cust_bill_send_csv_ftp.pm
@@ -15,9 +15,10 @@ sub option_fields {
   (
     'ftpformat'   => { label   => 'Format',
                        type    =>'select',
-                       options => ['default', 'billco'],
+                       options => ['default', 'billco', 'oneline'],
                        option_labels => { 'default' => 'Default',
                                           'billco'  => 'Billco',
+                                          'oneline' => 'One line',
                                         },
                      },
     'ftpserver'   => 'FTP server',
diff --git a/FS/FS/part_event/Action/cust_bill_spool_csv.pm b/FS/FS/part_event/Action/cust_bill_spool_csv.pm
index 11ecbc555..1504a4fa9 100644
--- a/FS/FS/part_event/Action/cust_bill_spool_csv.pm
+++ b/FS/FS/part_event/Action/cust_bill_spool_csv.pm
@@ -15,9 +15,10 @@ sub option_fields {
   (
     'spoolformat'       => { label   => 'Format',
                              type    => 'select',
-                             options => ['default', 'billco'],
+                             options => ['default', 'billco', 'oneline'],
                              option_labels => { 'default' => 'Default',
                                                 'billco'  => 'Billco',
+                                                'oneline' => 'One line',
                                               },
                            },
     'spoolbalanceover'  => { label =>
diff --git a/FS/FS/part_event/Condition/once_percust_every.pm b/FS/FS/part_event/Condition/once_percust_every.pm
new file mode 100644
index 000000000..9e2ec1f00
--- /dev/null
+++ b/FS/FS/part_event/Condition/once_percust_every.pm
@@ -0,0 +1,58 @@
+package FS::part_event::Condition::once_percust_every;
+
+use strict;
+use FS::Record qw( qsearch );
+use FS::part_event;
+use FS::cust_event;
+
+use base qw( FS::part_event::Condition );
+
+sub description { "Don't run this event more than once per customer in the specified interval"; }
+
+sub eventtable_hashref {
+    { 'cust_main' => 0,
+      'cust_bill' => 1,
+      'cust_pkg'  => 1,
+    };
+}
+
+# Runs the event at most "once every X", per customer.
+
+sub option_fields {
+  (
+    'run_delay'  => { label=>'Interval', type=>'freq', value=>'1m', },
+  );
+}
+
+sub condition {
+  my($self, $object, %opt) = @_;
+
+  my $obj_pkey = $object->primary_key;
+  my $obj_table = $object->table;
+  my $custnum = $object->custnum;
+
+  my @where = (
+    "tablenum IN ( SELECT $obj_pkey FROM $obj_table WHERE custnum = $custnum )"
+  );
+  if ( $opt{'cust_event'}->eventnum =~ /^(\d+)$/ ) {
+    push @where, " eventnum != $1 ";
+  }
+  my $extra_sql = ' AND '. join(' AND ', @where);
+ 
+  my $max_date = $self->option_age_from('run_delay', $opt{'time'});
+ 
+  my @existing = qsearch( {
+    'table'     => 'cust_event',
+    'hashref'   => {
+                     'eventpart' => $self->eventpart,
+                     'status'    => { op=>'!=', value=>'failed'  },
+                     '_date'     => { op=>'>',  value=>$max_date },
+                   },
+    'extra_sql' => $extra_sql,
+  } );
+
+  ! scalar(@existing);
+
+}
+
+1;
diff --git a/FS/FS/part_event/Condition/pkg_dundate_age.pm b/FS/FS/part_event/Condition/pkg_dundate_age.pm
new file mode 100644
index 000000000..2ea2a2041
--- /dev/null
+++ b/FS/FS/part_event/Condition/pkg_dundate_age.pm
@@ -0,0 +1,43 @@
+package FS::part_event::Condition::pkg_dundate_age;
+use base qw( FS::part_event::Condition );
+
+use strict;
+
+sub description {
+  "Skip until specified #days before package suspension delay date";
+}
+
+
+sub option_fields {
+  (
+    'age'     => { 'label'      => 'Time before suspension delay date',
+                   'type'       => 'freq',
+                 },
+  );
+}
+
+sub eventtable_hashref {
+  { 'cust_main' => 0,
+    'cust_bill' => 0,
+    'cust_pkg'  => 1,
+  };
+}
+
+sub condition {
+  my($self, $cust_pkg, %opt) = @_;
+
+  my $age = $self->option_age_from('age', $opt{'time'} );
+
+  $cust_pkg->dundate <= $age;
+}
+
+sub condition_sql {
+  my( $class, $table, %opt ) = @_;
+  return 'true' unless $table eq 'cust_pkg';
+  
+  my $age = $class->condition_sql_option_age_from('age', $opt{'time'});
+  
+  "COALESCE($table.dundate,0) <= ". $age;
+}
+
+1;
diff --git a/FS/FS/svc_broadband.pm b/FS/FS/svc_broadband.pm
index 636ba1fd9..307493264 100755
--- a/FS/FS/svc_broadband.pm
+++ b/FS/FS/svc_broadband.pm
@@ -542,9 +542,9 @@ sub _check_ip_addr {
 
 sub _check_duplicate {
   my $self = shift;
-
-  $self->lock_table;
-
+  # Not a reliable check because the table isn't locked, but 
+  # that's why we have a unique index.  This is just to give a
+  # friendlier error message.
   my @dup;
   @dup = $self->find_duplicates('global', 'ip_addr');
   if ( @dup ) {
diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index 94860d3f2..820b17864 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -9,7 +9,9 @@
                  'header'      => [
                    emt('Description'),
                    ( $unearned
-                     ? ( emt('Unearned'), emt('Owed'), emt('Payment date') )
+                     ? ( emt('Unearned'), 
+                         emt('Owed'), # useful in 'paid' mode?
+                         emt('Payment date') )
                      : ( emt('Setup charge') )
                    ),
                    ( $use_usage eq 'usage'
@@ -39,9 +41,10 @@
                            $elapsed = 0 if $elapsed < 0;
 
                            my $remaining = 1 - $elapsed/$period;
+                           my $base = ($unearned_mode eq 'paid' ? 'total_paid' : 'recur');
 
                            sprintf($money_char. '%.2f',
-                             $remaining * $cust_bill_pkg->recur );
+                             $remaining * $cust_bill_pkg->$base );
 
                          } else {
                            sprintf($money_char.'%.2f', $cust_bill_pkg->setup );
@@ -137,6 +140,12 @@ die "access denied"
 my $conf = new FS::Conf;
 
 my $unearned = '';
+my $unearned_mode = '';
+my $unearned_base = '';
+
+my @select = ( 'cust_bill_pkg.*',
+               'cust_bill._date', );
+my ($join_cust, $join_pkg ) = ('', '');
 
 #here is the agent virtualization
 my $agentnums_sql =
@@ -146,14 +155,18 @@ my @where = ( $agentnums_sql );
 
 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 
+if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
+  push @where, FS::cust_main->cust_status_sql . " = '$1'";
+}
+
 if ( $cgi->param('distribute') == 1 ) {
   push @where, "sdate <= $ending",
                "edate >  $beginning",
   ;
 }
 else {
-  push @where, "_date >= $beginning",
-               "_date <= $ending";
+  push @where, "cust_bill._date >= $beginning",
+               "cust_bill._date <= $ending";
 }
 
 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
@@ -218,7 +231,7 @@ if ( $cgi->param('taxclass')
 
 }
 
-my @loc_param = qw( city county state country );
+my @loc_param = qw( district city county state country );
 
 if ( $cgi->param('out') ) {
 
@@ -266,7 +279,7 @@ if ( $cgi->param('out') ) {
 
           my %ph = ( 'county' => dbh->quote($_),
                      map { $_ => dbh->quote( $cgi->param($_) ) }
-                       qw( city state country )
+                       qw( district city state country )
                    );
 
           my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
@@ -330,22 +343,46 @@ if ( $cgi->param('out') ) {
 
   push @where, FS::tax_rate_location->location_sql(
                  map { $_ => (scalar($cgi->param($_)) || '') }
-                   qw( city county state locationtaxid )
+                   qw( district city county state locationtaxid )
                );
 
 } elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) {
 
   $unearned = $1;
+  $unearned_mode = $cgi->param('mode');
 
   push @where, "cust_bill_pkg.sdate < $unearned",
                "cust_bill_pkg.edate > $unearned",
                "cust_bill_pkg.recur != 0",
-               "part_pkg.freq != '0'",
+               "part_pkg.freq != '0'";
+
+  if ( !$cgi->param('include_monthly') ) {
+    push @where,
                "part_pkg.freq != '1'",
                "part_pkg.freq NOT LIKE '%h'",
                "part_pkg.freq NOT LIKE '%d'",
                "part_pkg.freq NOT LIKE '%w'";
+  }
 
+  if ( !$unearned_mode or $unearned_mode eq 'billed' ) {
+    $unearned_base = 'cust_bill_pkg.recur';
+  }
+  elsif ( $unearned_mode eq 'paid' ) {
+    $join_pkg .= "JOIN (
+      SELECT billpkgnum, SUM(cust_bill_pay_pkg.amount) AS total_paid
+      FROM      cust_bill_pay_pkg
+           JOIN cust_bill_pay USING (billpaynum)
+           JOIN cust_pay      USING (paynum)
+      WHERE cust_bill_pay_pkg.setuprecur = 'recur'
+        AND cust_pay._date <= $unearned
+      GROUP BY billpkgnum
+    ) AS cust_bill_pkg_paid USING (billpkgnum)";
+    $unearned_base = 'total_paid';
+    push @select, 'total_paid';
+  }
+  else {
+    die "invalid mode '$unearned_mode'";
+  }
 }
 
 if ( $cgi->param('itemdesc') ) {
@@ -468,7 +505,7 @@ if ( $cgi->param('pkg_tax') ) {
   } elsif ( $use_usage eq 'usage' ) {
     $count_query .= "SUM(usage)";
   } elsif ( $unearned ) {
-    $count_query .= "SUM(cust_bill_pkg.recur)";
+    $count_query .= "SUM($unearned_base)";
   } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
     $count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))";
   } elsif ( $cgi->param('iscredit') eq 'rate') {
@@ -492,23 +529,21 @@ if ( $cgi->param('pkg_tax') ) {
 
     my $remaining = "(1 - $elapsed/$period)";
 
-    $count_query .= ", SUM($remaining * cust_bill_pkg.recur)";
+    $count_query .= ", SUM($remaining * $unearned_base)";
 
   }
 
 }
 
-my $join_cust =  '      JOIN cust_bill USING ( invnum ) 
-                   LEFT JOIN cust_main USING ( custnum ) ';
+$join_cust =  '      JOIN cust_bill USING ( invnum ) 
+                LEFT JOIN cust_main USING ( custnum ) ';
 
-
-my $join_pkg;
 if ( $cgi->param('nottax') ) {
 
-  $join_pkg =  ' LEFT JOIN cust_pkg USING ( pkgnum )
-                 LEFT JOIN part_pkg USING ( pkgpart )
-                 LEFT JOIN part_pkg AS override
-                   ON pkgpart_override = override.pkgpart ';
+  $join_pkg .=  ' LEFT JOIN cust_pkg USING ( pkgnum )
+                  LEFT JOIN part_pkg USING ( pkgpart )
+                  LEFT JOIN part_pkg AS override
+                    ON pkgpart_override = override.pkgpart ';
   $join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) '
     if $conf->exists('tax-pkg_address');
 
@@ -567,9 +602,6 @@ if ($use_usage) {
   $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
 }
 
-my @select = ( 'cust_bill_pkg.*',
-               'cust_bill._date', );
-
 push @select, 'part_pkg.pkg',
               'part_pkg.freq',
   unless $cgi->param('istax');
@@ -583,7 +615,7 @@ my $query = {
   'hashref'   => {},
   'select'    => join(', ', @select ),
   'extra_sql' => $where,
-  'order_by'  => 'ORDER BY _date, billpkgnum',
+  'order_by'  => 'ORDER BY cust_bill._date, billpkgnum',
 };
 
 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
@@ -593,9 +625,8 @@ my $conf = new FS::Conf;
 my $money_char = $conf->config('money_char') || '$';
 
 my $owed_sub = sub {
-  $money_char. shift->owed_recur; #_recur :/
+  $money_char . shift->owed_recur;
 };
-
 my $payment_date_sub = sub {
   #my $cust_bill_pkg = shift;
   my @cust_pay = sort { $a->_date <=> $b->_date }
diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/prepaid_income.html
index 2fe5b6f10..d224af9d7 100644
--- a/httemplate/search/report_prepaid_income.cgi
+++ b/httemplate/search/prepaid_income.html
@@ -58,7 +58,9 @@
 <% $actual_label %><% $actual_label ? 'u' : 'U' %>nearned revenue
 is the amount of unearned revenue
 <% $actual_label ? 'Freeside has actually' : '' %>
-invoiced for packages with longer-than monthly terms.
+invoiced for packages with 
+<% $cgi->param('include_monthly') ? 'terms extending into the future.'
+                                  : 'longer-than monthly terms.' %>
 
 % if ( $legacy ) {
   <BR><BR>
@@ -82,13 +84,19 @@ my $actual_label = $legacy ? 'Actual ' : '';
 
 #doesn't yet deal with daily/weekly packages
 
+my $mode = $cgi->param('mode');
+
 my $time = time;
 
 my $now = $cgi->param('date') && parse_datetime($cgi->param('date')) || $time;
 $now =~ /^(\d+)$/ or die "unparsable date?";
 $now = $1;
 
-my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now";
+my $link = "cust_bill_pkg.cgi?nottax=1;unearned_now=$now;mode=$mode";
+
+if ( $cgi->param('include_monthly') ) {
+  $link .= ';include_monthly=1';
+}
 
 my $curuser = $FS::CurrentUser::CurrentUser;
 
@@ -108,6 +116,13 @@ my @where = ();
 #here is the agent virtualization
 push @where, $curuser->agentnums_sql( 'table'=>'cust_main' );
 
+my $status = '';
+if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
+  $status = $1;
+  $link .= ";status=$status";
+  push @where, FS::cust_main->cust_status_sql . " = '$status'";
+}
+
 my %total = ();
 my %total_legacy = ();
 foreach my $agentnum (@agentnums) {
@@ -117,38 +132,6 @@ foreach my $agentnum (@agentnums) {
 
   my( $total, $total_legacy ) = ( 0, 0 );
 
-  # my @cust_bill_pkg =
-  #   grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[hdw])$/ }
-  #     qsearch({
-  #       'select'    => 'cust_bill_pkg.*',
-  #       'table'     => 'cust_bill_pkg',
-  #       'addl_from' => ' LEFT JOIN cust_bill USING ( invnum  ) '.
-  #                      ' LEFT JOIN cust_main USING ( custnum ) ',
-  #       'hashref'   => {
-  #                        'recur' => { op=>'!=', value=>0    },
-  #                        'sdate' => { op=>'<',  value=>$now },
-  #                        'edate' => { op=>'>',  value=>$now },
-  #                      },
-  #       'extra_sql' => $where,
-  #     });
-  #
-  #    foreach my $cust_bill_pkg ( @cust_bill_pkg) { 
-  #      my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate;
-  #   
-  #      my $elapsed = $now - $cust_bill_pkg->sdate;
-  #      $elapsed = 0 if $elapsed < 0;
-  #   
-  #      my $remaining = 1 - $elapsed/$period;
-  #   
-  #      my $unearned = $remaining * $cust_bill_pkg->recur;
-  #      $total += $unearned;
-  #   
-  #    }
-
-  #re-written in sql:
-
-  #false laziness w/cust_bill_pkg.cgi
-
   my $float = 'REAL'; #'DOUBLE PRECISION';
 
   my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)";
@@ -160,23 +143,46 @@ foreach my $agentnum (@agentnums) {
 
   my $remaining = "(1 - $elapsed/$period)";
 
-  my $select = "SUM($remaining * cust_bill_pkg.recur)";
+  my $base;
+  my $from = '';
+  if ( $mode eq 'billed' ) {
+    $base = 'cust_bill_pkg.recur';
+    $from = 'cust_bill_pkg';
+  }
+  elsif ( $mode eq 'paid' ) {
+    $base = 'cust_bill_pay_pkg.amount';
+    $from = 'cust_bill_pay_pkg 
+             LEFT JOIN cust_bill_pkg USING ( billpkgnum )
+             LEFT JOIN cust_bill_pay USING ( billpaynum )
+             LEFT JOIN cust_pay      USING ( paynum )';
+    $where .= ' AND ' if $where;
+    $where .= "cust_bill_pay_pkg.setuprecur = 'recur' 
+               AND cust_pay._date <= $now";
+  }
+  else {
+    die "invalid mode '$mode'";
+  }
+  my $select = "SUM($remaining * $base)";
 
-  #[...]
+  if ( !$cgi->param('include_monthly') ) {
+    # all except freq != 0; one-time charges should never be included
+    $where .= "
+                 AND part_pkg.freq != '1'
+                 AND part_pkg.freq NOT LIKE '%h'
+                 AND part_pkg.freq NOT LIKE '%d'
+                 AND part_pkg.freq NOT LIKE '%w'";
+  }
 
-  my $sql = "SELECT $select FROM cust_bill_pkg
-                            LEFT JOIN cust_pkg  USING ( pkgnum )
-                            LEFT JOIN part_pkg  USING ( pkgpart )
-                            LEFT JOIN cust_main USING ( custnum )
+  my $sql = 
+  "SELECT $select FROM $from 
+                  LEFT JOIN cust_pkg  ON (cust_bill_pkg.pkgnum = cust_pkg.pkgnum)
+                  LEFT JOIN part_pkg  USING ( pkgpart )
+                  LEFT JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
                WHERE pkgpart > 0
-                 AND sdate < $now
-                 AND edate > $now
+                 AND cust_bill_pkg.sdate < $now
+                 AND cust_bill_pkg.edate > $now
                  AND cust_bill_pkg.recur != 0
                  AND part_pkg.freq != '0'
-                 AND part_pkg.freq != '1'
-                 AND part_pkg.freq NOT LIKE '%h'
-                 AND part_pkg.freq NOT LIKE '%d'
-                 AND part_pkg.freq NOT LIKE '%w'
                  $where
              ";
 
diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html
index 061b24c68..90b72f60b 100644
--- a/httemplate/search/report_prepaid_income.html
+++ b/httemplate/search/report_prepaid_income.html
@@ -2,7 +2,7 @@
 
 <% include('/elements/init_calendar.html') %>
 
-<FORM ACTION="report_prepaid_income.cgi" METHOD="GET">
+<FORM ACTION="prepaid_income.html" METHOD="GET">
 
 <TABLE BGCOLOR="#cccccc" CELLSPACING=0>
 
@@ -13,7 +13,7 @@
   </TR>
 
   <TR>
-    <TD>As of </TD>
+    <TD ALIGN="right">As of </TD>
     <TD>
       <INPUT TYPE="text" NAME="date" ID="date_text" VALUE="now">
       <IMG SRC="../images/calendar.png" ID="date_button" STYLE="cursor: pointer" TITLE="Select date">
@@ -30,7 +30,20 @@
   </TR>
 
   <% include( '/elements/tr-select-agent.html', 'disable_empty'=>0 ) %>
-
+  
+  <& /elements/tr-select-cust_main-status.html,
+      label => mt('Customer Status') &>
+  <& /elements/tr-select.html,
+      label => 'Invoice Status',
+      field => 'mode',
+      options => [ qw(billed paid) ] &>
+  <TR>
+    <TD ALIGN="right">
+      <INPUT TYPE="checkbox" NAME="include_monthly" VALUE=1>
+    </TD>
+    <TD ALIGN="left"><% emt('Include packages with period <= 1 month') %>
+    </TD>
+  </TR>
   <TR>
     <TD COLSPAN=2>&nbsp;</TD>
   </TR>
diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index 0cd652d83..bfbc5fe5a 100755
--- a/httemplate/search/report_tax.cgi
+++ b/httemplate/search/report_tax.cgi
@@ -275,7 +275,10 @@ sub gotcust {
   my $table = shift;
   my $prefix = @_ ? shift : '';
   "
-        ( $table.${prefix}city  = cust_main_county.city
+        ( $table.${prefix}district = cust_main_county.district
+          OR cust_main_county.district = ''
+          OR cust_main_county.district IS NULL )
+    AND ( $table.${prefix}city  = cust_main_county.city
           OR cust_main_county.city = ''
           OR cust_main_county.city IS NULL )
     AND ( $table.${prefix}county  = cust_main_county.county
@@ -332,6 +335,7 @@ if ( $conf->exists('tax-pkg_address') ) {
 }
 
 my $out = 'Out of taxable region(s)';
+# these are actually tax labels, not regions
 my %regions = ();
 
 foreach my $r ( qsearch({ 'table'     => 'cust_main_county',
@@ -341,6 +345,7 @@ foreach my $r ( qsearch({ 'table'     => 'cust_main_county',
 {
   #warn $r->county. ' '. $r->state. ' '. $r->country. "\n";
 
+  # set up a %regions entry for this region's tax label
   my $label = getlabel($r);
   $regions{$label}->{'label'} = $label;
 
@@ -366,6 +371,7 @@ foreach my $r ( qsearch({ 'table'     => 'cust_main_county',
 
   } else {
 
+    # SQL for "taxclass doesn't match any other tax in the region"
     my $same_sql = $r->sql_taxclass_sameregion;
     $mywhere .= " AND $same_sql" if $same_sql;
 
@@ -375,42 +381,24 @@ foreach my $r ( qsearch({ 'table'     => 'cust_main_county',
 
   }
 
+  # FROM cust_bill_pkg JOIN (whatever is needed to determine tax location)
+  # WHERE (matches tax location and agentnum and taxclass)
+  # takes parameters in @base_param, plus taxclass if there is one
   my $fromwhere = "$from_join_cust_pkg $mywhere"; # AND payby != 'COMP' ";
 
-#  my $label = getlabel($r);
-#  $regions{$label}->{'label'} = $label;
-
   my $nottax = 'pkgnum != 0';
 
-  ## calculate total for this region
+  ## calculate total of sales (non-tax line items) for this region
 
   my $t_sql =
    "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax";
   my $t = scalar_sql($r, \@param, $t_sql);
   $regions{$label}->{'total'} += $t;
 
-  #if ( $label eq $out ) # && $t ) {
-  #  warn "adding $t for ".
-  #       join('/', map $r->$_, qw( taxclass county state country ) ). "\n";
-  #  #warn $t_sql if $r->state eq 'FL';
-  #}
+  #$regions{$label}->{subtotals}->{$r->taxnum} = $t; #useful debug
 
   ## calculate customer-exemption for this region
 
-##  my $taxable = $t;
-
-#  my($taxable, $x_cust) = (0, 0);
-#  foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i }
-#                       qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) {
-#    $taxable += scalar_sql($r, \@param, 
-#      "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )"
-#    );
-#
-#    $x_cust += scalar_sql($r, \@param, 
-#      "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'"
-#    );
-#  }
-
   #false laziness -ish w/report_tax.cgi
   my $cust_exempt;
   if ( $r->taxname ) {
@@ -486,10 +474,12 @@ foreach my $r ( qsearch({ 'table'     => 'cust_main_county',
   } else {
     $regions{$label}->{'rate'} = $r->tax.'%';
   }
-
 }
+#warn Dumper(\%regions);
+# $regions{$label} now contains 'total', 'exempt_cust', 'exempt_pkg', 
+# 'exempt_monthly', summed over each set of regions with the same label.
 
-my $distinct = "country, state, county, city, 
+my $distinct = "country, state, county, city, district,
                 CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname";
 my $taxclass_distinct = 
   #a little bit unsure of this part... test?
@@ -528,11 +518,19 @@ $creditfromwhere .= ")";
 $taxfromwhere .= " $taxwhere "; #AND payby != 'COMP' ";
 $creditfromwhere .= " $taxwhere AND billpkgtaxratelocationnum IS NULL"; #AND payby != 'COMP' ";
 
-my @taxparam = @base_param;
+#should i be a cust_main_county method or something
+# yes. yes, you should.
 
+# $taxfromwhere: Most of a query to find cust_bill_pkg records linked to a 
+# customer matching a given state/county/city/district (and within the date 
+# range for the report).
+# @base_param: A list of the fields from cust_main_county to use as parameters.
+
+# $_taxamount_sub: Takes a cust_main_county and returns the sum of taxes billed
+# within the report period for all customers located in that county.  If 
+# the cust_main_county has a taxname, limits to taxes with that name; otherwise
+# includes all line items with pkgnum = 0 and description either 'Tax' or empty.
 
-#should i be a cust_main_county method or something
-#need to pass in $taxfromwhere & @taxparam???
 my $_taxamount_sub = sub {
   my $r = shift;
 
@@ -545,9 +543,11 @@ my $_taxamount_sub = sub {
   my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ".
             " $taxfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax";
 
-  scalar_sql($r, \@taxparam, $sql );
+  scalar_sql($r, [ @base_param ], $sql );
 };
 
+# $_creditamount_sub: As above, but returns the sum of credits applied 
+
 my $_creditamount_sub = sub {
   my $r = shift;
 
@@ -560,7 +560,7 @@ my $_creditamount_sub = sub {
   my $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ".
             " $creditfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax";
 
-  scalar_sql($r, \@taxparam, $sql );
+  scalar_sql($r, [ @base_param ], $sql );
 };
 
 #tax-report_groups filtering
@@ -734,7 +734,8 @@ sub getlabel {
   my $label;
   if (
     $r->tax == 0 
-    && ! scalar( qsearch('cust_main_county', { 'city'    => $r->city,
+    && ! scalar( qsearch('cust_main_county', { 'district'=> $r->district,
+                                               'city'    => $r->city,
                                                'county'  => $r->county,
                                                'state'   => $r->state,
                                                'country' => $r->country,
@@ -747,10 +748,6 @@ sub getlabel {
     #kludge to avoid "will not stay shared" warning
     my $out = 'Out of taxable region(s)';
     $label = $out;
-#  } elsif ( $r->taxname && count_taxname($r->taxname) == 1 ) {
-#    $label = $r->taxname;
-##    $regions{$label}->{'taxname'} = $label;
-##    push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country );
   } else {
     $label = $r->country;
     $label = $r->state.", $label" if $r->state;
diff --git a/rt/lib/RT/Action/Accumulate.pm b/rt/lib/RT/Action/Accumulate.pm
index c4ca667ea..14675b894 100644
--- a/rt/lib/RT/Action/Accumulate.pm
+++ b/rt/lib/RT/Action/Accumulate.pm
@@ -23,7 +23,8 @@ the same name, and should be single-valued fields.
 sub Prepare {
     my $self = shift;
     my $cfname = $self->Argument or return 0;
-    $self->{'inc_by'} = $self->TransactionObj->FirstCustomFieldValue($cfname);
+    $self->{'inc_by'} = $self->TransactionObj->FirstCustomFieldValue($cfname) 
+                        || '';
     return ( $self->{'inc_by'} =~ /^(\d+)$/ );
 }
 
diff --git a/rt/lib/RT/Action/EscalateQueue.pm b/rt/lib/RT/Action/EscalateQueue.pm
index adafbdfb7..2775a8384 100755
--- a/rt/lib/RT/Action/EscalateQueue.pm
+++ b/rt/lib/RT/Action/EscalateQueue.pm
@@ -116,15 +116,18 @@ sub Commit {
     if ( my $due_in = $new_queue->DefaultDueIn ) {
         $Due->SetToNow;
         $Due->AddDays( $due_in );
-    }
-    ( $val, $msg ) = $ticket->_Set(
-      Field => 'Due',
-      Value => $Due->ISO,
-      RecordTransaction => 0,
-    );
-    if (! $val) {
-        $RT::Logger->error( "Couldn't set new due date: $msg" );
-        return (0, $msg);
+        
+        if ( $Due->ISO ne $ticket->Due ) {
+            ( $val, $msg ) = $ticket->_Set(
+              Field => 'Due',
+              Value => $Due->ISO,
+              RecordTransaction => 0,
+            );
+            if (! $val) {
+                $RT::Logger->error( "Couldn't set new due date: $msg" );
+                return (0, $msg);
+            }
+        }
     }
     return 1;
 }