X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Fcust_main.pm;h=946495cebf7d9e9d76285d4c080426693e2500cf;hp=a8a9ae43b7d293e09c86e3a0f62bbb07fa71034c;hb=7153190ee1bfeb6d3ad9e6da270a41a949333a7e;hpb=3dc6bff7a0a4ceaacd18a7ce6b50cd2b8a2ce498

diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index a8a9ae43b..946495ceb 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -8,6 +8,7 @@ use vars qw( $realtime_bop_decline_quiet ); #ugh
 use Safe;
 use Carp;
 use Exporter;
+use Scalar::Util qw( blessed );
 use Time::Local qw(timelocal_nocheck);
 use Data::Dumper;
 use Tie::IxHash;
@@ -19,15 +20,16 @@ use String::Approx qw(amatch);
 use Business::CreditCard 0.28;
 use Locale::Country;
 use Data::Dumper;
-use FS::UID qw( getotaker dbh );
+use FS::UID qw( getotaker dbh driver_name );
 use FS::Record qw( qsearchs qsearch dbdef );
-use FS::Misc qw( send_email generate_ps do_print );
+use FS::Misc qw( generate_email send_email generate_ps do_print );
 use FS::Msgcat qw(gettext);
 use FS::cust_pkg;
 use FS::cust_svc;
 use FS::cust_bill;
 use FS::cust_bill_pkg;
 use FS::cust_pay;
+use FS::cust_pay_pending;
 use FS::cust_pay_void;
 use FS::cust_pay_batch;
 use FS::cust_credit;
@@ -44,8 +46,6 @@ use FS::part_pkg;
 use FS::part_event;
 use FS::part_event_condition;
 #use FS::cust_event;
-use FS::cust_tax_exempt;
-use FS::cust_tax_exempt_pkg;
 use FS::type_pkgs;
 use FS::payment_gateway;
 use FS::agent_payment_gateway;
@@ -1053,7 +1053,7 @@ sub delete {
 
 }
 
-=item replace OLD_RECORD [ INVOICING_LIST_ARYREF ]
+=item replace [ OLD_RECORD ] [ INVOICING_LIST_ARYREF ]
 
 Replaces the OLD_RECORD with this one in the database.  If there is an error,
 returns the error, otherwise returns false.
@@ -1069,23 +1069,16 @@ check_invoicing_list first.  Here's an example:
 
 sub replace {
   my $self = shift;
-  my $old = shift;
+
+  my $old = ( blessed($_[0]) && $_[0]->isa('FS::Record') )
+              ? shift
+              : $self->replace_old;
+
   my @param = @_;
+
   warn "$me replace called\n"
     if $DEBUG;
 
-  local $SIG{HUP} = 'IGNORE';
-  local $SIG{INT} = 'IGNORE';
-  local $SIG{QUIT} = 'IGNORE';
-  local $SIG{TERM} = 'IGNORE';
-  local $SIG{TSTP} = 'IGNORE';
-  local $SIG{PIPE} = 'IGNORE';
-
-  # We absolutely have to have an old vs. new record to make this work.
-  if (!defined($old)) {
-    $old = qsearchs( 'cust_main', { 'custnum' => $self->custnum } );
-  }
-
   my $curuser = $FS::CurrentUser::CurrentUser;
   if (    $self->payby eq 'COMP'
        && $self->payby ne $old->payby
@@ -1100,6 +1093,13 @@ sub replace {
     && $self->payby =~ /^(CARD|DCRD)$/
     && ( $old->payinfo eq $self->payinfo || $old->paymask eq $self->paymask );
 
+  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;
@@ -1290,58 +1290,60 @@ sub check {
   
   }
 
-  my @addfields = qw(
-    last first company address1 address2 city county state zip
-    country daytime night fax
-  );
+  if ( $self->has_ship_address
+       && scalar ( grep { $self->getfield($_) ne $self->getfield("ship_$_") }
+                        $self->addr_fields )
+     )
+  {
+    my $error =
+      $self->ut_name('ship_last')
+      || $self->ut_name('ship_first')
+      || $self->ut_textn('ship_company')
+      || $self->ut_text('ship_address1')
+      || $self->ut_textn('ship_address2')
+      || $self->ut_text('ship_city')
+      || $self->ut_textn('ship_county')
+      || $self->ut_textn('ship_state')
+      || $self->ut_country('ship_country')
+    ;
+    return $error if $error;
 
-  if ( defined $self->dbdef_table->column('ship_last') ) {
-    if ( scalar ( grep { $self->getfield($_) ne $self->getfield("ship_$_") }
-                       @addfields )
-         && scalar ( grep { $self->getfield("ship_$_") ne '' } @addfields )
-       )
-    {
-      my $error =
-        $self->ut_name('ship_last')
-        || $self->ut_name('ship_first')
-        || $self->ut_textn('ship_company')
-        || $self->ut_text('ship_address1')
-        || $self->ut_textn('ship_address2')
-        || $self->ut_text('ship_city')
-        || $self->ut_textn('ship_county')
-        || $self->ut_textn('ship_state')
-        || $self->ut_country('ship_country')
-      ;
-      return $error if $error;
+    #false laziness with above
+    unless ( qsearchs('cust_main_county', {
+      'country' => $self->ship_country,
+      'state'   => '',
+     } ) ) {
+      return "Unknown ship_state/ship_county/ship_country: ".
+        $self->ship_state. "/". $self->ship_county. "/". $self->ship_country
+        unless qsearch('cust_main_county',{
+          'state'   => $self->ship_state,
+          'county'  => $self->ship_county,
+          'country' => $self->ship_country,
+        } );
+    }
+    #eofalse
 
-      #false laziness with above
-      unless ( qsearchs('cust_main_county', {
-        'country' => $self->ship_country,
-        'state'   => '',
-       } ) ) {
-        return "Unknown ship_state/ship_county/ship_country: ".
-          $self->ship_state. "/". $self->ship_county. "/". $self->ship_country
-          unless qsearch('cust_main_county',{
-            'state'   => $self->ship_state,
-            'county'  => $self->ship_county,
-            'country' => $self->ship_country,
-          } );
-      }
-      #eofalse
-
-      $error =
-        $self->ut_phonen('ship_daytime', $self->ship_country)
-        || $self->ut_phonen('ship_night', $self->ship_country)
-        || $self->ut_phonen('ship_fax', $self->ship_country)
-        || $self->ut_zip('ship_zip', $self->ship_country)
-      ;
-      return $error if $error;
+    $error =
+      $self->ut_phonen('ship_daytime', $self->ship_country)
+      || $self->ut_phonen('ship_night', $self->ship_country)
+      || $self->ut_phonen('ship_fax', $self->ship_country)
+      || $self->ut_zip('ship_zip', $self->ship_country)
+    ;
+    return $error if $error;
+
+    return "Unit # is required."
+      if $self->ship_address2 =~ /^\s*$/
+      && $conf->exists('cust_main-require_address2');
+
+  } else { # ship_ info eq billing info, so don't store dup info in database
+
+    $self->setfield("ship_$_", '')
+      foreach $self->addr_fields;
+
+    return "Unit # is required."
+      if $self->address2 =~ /^\s*$/
+      && $conf->exists('cust_main-require_address2');
 
-    } else { # ship_ info eq billing info, so don't store dup info in database
-      $self->setfield("ship_$_", '')
-        foreach qw( last first company address1 address2 city county state zip
-                    country daytime night fax );
-    }
   }
 
   #$self->payby =~ /^(CARD|DCRD|CHEK|DCHK|LECB|BILL|COMP|PREPAY|CASH|WEST|MCRD)$/
@@ -1542,6 +1544,30 @@ sub check {
   $self->SUPER::check;
 }
 
+=item addr_fields 
+
+Returns a list of fields which have ship_ duplicates.
+
+=cut
+
+sub addr_fields {
+  qw( last first company
+      address1 address2 city county state zip country
+      daytime night fax
+    );
+}
+
+=item has_ship_address
+
+Returns true if this customer record has a separate shipping address.
+
+=cut
+
+sub has_ship_address {
+  my $self = shift;
+  scalar( grep { $self->getfield("ship_$_") ne '' } $self->addr_fields );
+}
+
 =item all_pkgs
 
 Returns all packages (see L<FS::cust_pkg>) for this customer.
@@ -1595,8 +1621,8 @@ sub ncancelled_pkgs {
 
   } else {
 
-    warn "$me ncancelled_pkgs: searching for packages for custnum ".
-         $self->custnum
+    warn "$me ncancelled_pkgs: searching for packages with custnum ".
+         $self->custnum. "\n"
       if $DEBUG > 1;
 
     @cust_pkg =
@@ -1681,7 +1707,8 @@ sub num_ncancelled_pkgs {
 }
 
 sub num_pkgs {
-  my( $self, $sql ) = @_;
+  my( $self ) = shift;
+  my $sql = scalar(@_) ? shift : '';
   $sql = "AND $sql" if $sql && $sql !~ /^\s*$/ && $sql !~ /^\s*AND/i;
   my $sth = dbh->prepare(
     "SELECT COUNT(*) FROM cust_pkg WHERE custnum = ? $sql"
@@ -1888,17 +1915,29 @@ Options are passed as name-value pairs.  Currently available options are:
 
 =over 4
 
-=item time - bills the customer as if it were that time.  Specified as a UNIX timestamp; see L<perlfunc/"time">).  Also see L<Time::Local> and L<Date::Parse> for conversion functions.  For example:
+=item time
+
+Bills the customer as if it were that time.  Specified as a UNIX timestamp; see L<perlfunc/"time">).  Also see L<Time::Local> and L<Date::Parse> for conversion functions.  For example:
 
  use Date::Parse;
  ...
  $cust_main->bill( 'time' => str2time('April 20th, 2001') );
 
-=item invoice_time - used in conjunction with the I<time> option, this option specifies the date of for the generated invoices.  Other calculations, such as whether or not to generate the invoice in the first place, are not affected.
+=item invoice_time
 
-=item check_freq - "1d" for the traditional, daily events (the default), or "1m" for the new monthly events (part_event.check_freq)
+Used in conjunction with the I<time> option, this option specifies the date of for the generated invoices.  Other calculations, such as whether or not to generate the invoice in the first place, are not affected.
 
-=item resetup - if set true, re-charges setup fees.
+=item check_freq
+
+"1d" for the traditional, daily events (the default), or "1m" for the new monthly events (part_event.check_freq)
+
+=item resetup
+
+If set true, re-charges setup fees.
+
+=item debug
+
+Debugging level.  Default is 0 (no debugging), or can be set to 1 (passed-in options), 2 (traces progress), 3 (more information), or 4 (include full search queries)
 
 =back
 
@@ -1911,10 +1950,12 @@ sub bill_and_collect {
   # cancel packages
   ###
 
-  #$^T not $options{time} because freeside-daily -d is for pre-printing invoices
-  foreach my $cust_pkg (
-    grep { $_->expire && $_->expire <= $^T } $self->ncancelled_pkgs
-  ) {
+  #$options{actual_time} not $options{time} because freeside-daily -d is for
+  #pre-printing invoices
+  my @cancel_pkgs = grep { $_->expire && $_->expire <= $options{actual_time} }
+                         $self->ncancelled_pkgs;
+
+  foreach my $cust_pkg ( @cancel_pkgs ) {
     my $error = $cust_pkg->cancel;
     warn "Error cancelling expired pkg ". $cust_pkg->pkgnum.
          " for custnum ". $self->custnum. ": $error"
@@ -1925,15 +1966,22 @@ sub bill_and_collect {
   # suspend packages
   ###
 
-  #$^T not $options{time} because freeside-daily -d is for pre-printing invoices
-  foreach my $cust_pkg (
-    grep { (    $_->part_pkg->is_prepaid && $_->bill && $_->bill < $^T
-             || $_->adjourn && $_->adjourn <= $^T
-           )
-           && ! $_->susp
+  #$options{actual_time} not $options{time} because freeside-daily -d is for
+  #pre-printing invoices
+  my @susp_pkgs = 
+    grep { ! $_->susp
+           && (    (    $_->part_pkg->is_prepaid
+                     && $_->bill
+                     && $_->bill < $options{actual_time}
+                   )
+                || (    $_->adjourn
+                    && $_->adjourn <= $options{actual_time}
+                  )
+              )
          }
-         $self->ncancelled_pkgs
-  ) {
+         $self->ncancelled_pkgs;
+
+  foreach my $cust_pkg ( @susp_pkgs ) {
     my $error = $cust_pkg->suspend;
     warn "Error suspending package ". $cust_pkg->pkgnum.
          " for custnum ". $self->custnum. ": $error"
@@ -1965,19 +2013,27 @@ Options are passed as name-value pairs.  Currently available options are:
 
 =over 4
 
-=item resetup - if set true, re-charges setup fees.
+=item resetup
+
+If set true, re-charges setup fees.
 
-=item time - bills the customer as if it were that time.  Specified as a UNIX timestamp; see L<perlfunc/"time">).  Also see L<Time::Local> and L<Date::Parse> for conversion functions.  For example:
+=item time
+
+Bills the customer as if it were that time.  Specified as a UNIX timestamp; see L<perlfunc/"time">).  Also see L<Time::Local> and L<Date::Parse> for conversion functions.  For example:
 
  use Date::Parse;
  ...
  $cust_main->bill( 'time' => str2time('April 20th, 2001') );
 
-=item pkg_list - An array ref of specific packages (objects) to attempt billing, instead trying all of them.
+=item pkg_list
+
+An array ref of specific packages (objects) to attempt billing, instead trying all of them.
 
  $cust_main->bill( pkg_list => [$pkg1, $pkg2] );
 
-=item invoice_time - used in conjunction with the I<time> option, this option specifies the date of for the generated invoices.  Other calculations, such as whether or not to generate the invoice in the first place, are not affected.
+=item invoice_time
+
+Used in conjunction with the I<time> option, this option specifies the date of for the generated invoices.  Other calculations, such as whether or not to generate the invoice in the first place, are not affected.
 
 =back
 
@@ -1991,8 +2047,6 @@ sub bill {
 
   my $time = $options{'time'} || time;
 
-  my $error;
-
   #put below somehow?
   local $SIG{HUP} = 'IGNORE';
   local $SIG{INT} = 'IGNORE';
@@ -2007,21 +2061,7 @@ sub bill {
 
   $self->select_for_update; #mutex
 
-  #create a new invoice
-  #(we'll remove it later if it doesn't actually need to be generated [contains
-  # no line items] and we're inside a transaciton so nothing else will see it)
-  my $cust_bill = new FS::cust_bill ( {
-    'custnum' => $self->custnum,
-    '_date'   => ( $options{'invoice_time'} || $time ),
-    #'charged' => $charged,
-    'charged' => 0,
-  } );
-  $error = $cust_bill->insert;
-  if ( $error ) {
-    $dbh->rollback if $oldAutoCommit;
-    return "can't create invoice for customer #". $self->custnum. ": $error";
-  }
-  my $invnum = $cust_bill->invnum;
+  my @cust_bill_pkg = ();
 
   ###
   # find the packages which are due for billing, find out how much they are
@@ -2030,6 +2070,8 @@ sub bill {
 
   my( $total_setup, $total_recur ) = ( 0, 0 );
   my %tax;
+  my %taxlisthash;
+  my %taxname;
   my @precommit_hooks = ();
 
   foreach my $cust_pkg (
@@ -2045,353 +2087,408 @@ sub bill {
     $cust_pkg->setfield('bill', '')
       unless defined($cust_pkg->bill);
  
-    my $part_pkg = $cust_pkg->part_pkg;
+    #my $part_pkg = $cust_pkg->part_pkg;
 
+    my $real_pkgpart = $cust_pkg->pkgpart;
     my %hash = $cust_pkg->hash;
     my $old_cust_pkg = new FS::cust_pkg \%hash;
 
-    my @details = ();
-
-    ###
-    # bill setup
-    ###
-
-    my $setup = 0;
-    if ( ! $cust_pkg->setup &&
-         (
-           ( $conf->exists('disable_setup_suspended_pkgs') &&
-            ! $cust_pkg->getfield('susp')
-          ) || ! $conf->exists('disable_setup_suspended_pkgs')
-         )
-      || $options{'resetup'}
-    ) {
-    
-      warn "    bill setup\n" if $DEBUG > 1;
-
-      $setup = eval { $cust_pkg->calc_setup( $time, \@details ) };
-      if ( $@ ) {
-        $dbh->rollback if $oldAutoCommit;
-        return "$@ running calc_setup for $cust_pkg\n";
-      }
-
-      $cust_pkg->setfield('setup', $time) unless $cust_pkg->setup;
-    }
-
-    ###
-    # bill recurring fee
-    ### 
+    foreach my $part_pkg ( $cust_pkg->part_pkg->self_and_bill_linked ) {
 
-    my $recur = 0;
-    my $sdate;
-    if ( $part_pkg->getfield('freq') ne '0' &&
-         ! $cust_pkg->getfield('susp') &&
-         ( $cust_pkg->getfield('bill') || 0 ) <= $time
-    ) {
-
-      # XXX should this be a package event?  probably.  events are called
-      # at collection time at the moment, though...
-      if ( $part_pkg->can('reset_usage') ) {
-        warn "    resetting usage counters" if $DEBUG > 1;
-        $part_pkg->reset_usage($cust_pkg);
-      }
-
-      warn "    bill recur\n" if $DEBUG > 1;
-
-      # XXX shared with $recur_prog
-      $sdate = $cust_pkg->bill || $cust_pkg->setup || $time;
+      $cust_pkg->pkgpart($part_pkg->pkgpart); 
+      $cust_pkg->set($_, $hash{$_}) foreach qw( setup last_bill bill );
+  
+      my @details = ();
 
-      #over two params!  lets at least switch to a hashref for the rest...
-      my %param = ( 'precommit_hooks' => \@precommit_hooks, );
+      my $lineitems = 0;
 
-      $recur = eval { $cust_pkg->calc_recur( \$sdate, \@details, \%param ) };
-      if ( $@ ) {
-        $dbh->rollback if $oldAutoCommit;
-        return "$@ running calc_recur for $cust_pkg\n";
-      }
+      ###
+      # bill setup
+      ###
 
-      #change this bit to use Date::Manip? CAREFUL with timezones (see
-      # mailing list archive)
-      my ($sec,$min,$hour,$mday,$mon,$year) =
-        (localtime($sdate) )[0,1,2,3,4,5];
-
-      #pro-rating magic - if $recur_prog fiddles $sdate, want to use that
-      # only for figuring next bill date, nothing else, so, reset $sdate again
-      # here
-      $sdate = $cust_pkg->bill || $cust_pkg->setup || $time;
-      $cust_pkg->last_bill($sdate)
-        if $cust_pkg->dbdef_table->column('last_bill');
-
-      if ( $part_pkg->freq =~ /^\d+$/ ) {
-        $mon += $part_pkg->freq;
-        until ( $mon < 12 ) { $mon -= 12; $year++; }
-      } elsif ( $part_pkg->freq =~ /^(\d+)w$/ ) {
-        my $weeks = $1;
-        $mday += $weeks * 7;
-      } elsif ( $part_pkg->freq =~ /^(\d+)d$/ ) {
-        my $days = $1;
-        $mday += $days;
-      } elsif ( $part_pkg->freq =~ /^(\d+)h$/ ) {
-        my $hours = $1;
-        $hour += $hours;
-      } else {
-        $dbh->rollback if $oldAutoCommit;
-        return "unparsable frequency: ". $part_pkg->freq;
-      }
-      $cust_pkg->setfield('bill',
-        timelocal_nocheck($sec,$min,$hour,$mday,$mon,$year));
-    }
-
-    warn "\$setup is undefined" unless defined($setup);
-    warn "\$recur is undefined" unless defined($recur);
-    warn "\$cust_pkg->bill is undefined" unless defined($cust_pkg->bill);
+      my $setup = 0;
+      my $unitsetup = 0;
+      if ( ! $cust_pkg->setup &&
+           (
+             ( $conf->exists('disable_setup_suspended_pkgs') &&
+              ! $cust_pkg->getfield('susp')
+            ) || ! $conf->exists('disable_setup_suspended_pkgs')
+           )
+        || $options{'resetup'}
+      ) {
+    
+        warn "    bill setup\n" if $DEBUG > 1;
+        $lineitems++;
 
-    ###
-    # If $cust_pkg has been modified, update it and create cust_bill_pkg records
-    ###
+        $setup = eval { $cust_pkg->calc_setup( $time, \@details ) };
+        if ( $@ ) {
+          $dbh->rollback if $oldAutoCommit;
+          return "$@ running calc_setup for $cust_pkg\n";
+        }
 
-    if ( $cust_pkg->modified ) {  # hmmm.. and if the options are modified?
+        $unitsetup = $cust_pkg->part_pkg->unit_setup || $setup; #XXX uuh
 
-      warn "  package ". $cust_pkg->pkgnum. " modified; updating\n"
-        if $DEBUG >1;
+        $cust_pkg->setfield('setup', $time)
+          unless $cust_pkg->setup;
+              #do need it, but it won't get written to the db
+              #|| $cust_pkg->pkgpart != $real_pkgpart;
 
-      $error=$cust_pkg->replace($old_cust_pkg,
-                                options => { $cust_pkg->options },
-                               );
-      if ( $error ) { #just in case
-        $dbh->rollback if $oldAutoCommit;
-        return "Error modifying pkgnum ". $cust_pkg->pkgnum. ": $error";
       }
 
-      $setup = sprintf( "%.2f", $setup );
-      $recur = sprintf( "%.2f", $recur );
-      if ( $setup < 0 && ! $conf->exists('allow_negative_charges') ) {
-        $dbh->rollback if $oldAutoCommit;
-        return "negative setup $setup for pkgnum ". $cust_pkg->pkgnum;
-      }
-      if ( $recur < 0 && ! $conf->exists('allow_negative_charges') ) {
-        $dbh->rollback if $oldAutoCommit;
-        return "negative recur $recur for pkgnum ". $cust_pkg->pkgnum;
-      }
-
-      if ( $setup != 0 || $recur != 0 ) {
-
-        warn "    charges (setup=$setup, recur=$recur); adding line items\n"
-          if $DEBUG > 1;
-        my $cust_bill_pkg = new FS::cust_bill_pkg ({
-          'invnum'  => $invnum,
-          'pkgnum'  => $cust_pkg->pkgnum,
-          'setup'   => $setup,
-          'recur'   => $recur,
-          'sdate'   => $sdate,
-          'edate'   => $cust_pkg->bill,
-          'details' => \@details,
-        });
-        $error = $cust_bill_pkg->insert;
-        if ( $error ) {
+      ###
+      # bill recurring fee
+      ### 
+
+      #XXX unit stuff here too
+      my $recur = 0;
+      my $unitrecur = 0;
+      my $sdate;
+      if ( $part_pkg->getfield('freq') ne '0' &&
+           ! $cust_pkg->getfield('susp') &&
+           ( $cust_pkg->getfield('bill') || 0 ) <= $time
+      ) {
+
+        # XXX should this be a package event?  probably.  events are called
+        # at collection time at the moment, though...
+        $part_pkg->reset_usage($cust_pkg, 'debug'=>$DEBUG)
+          if $part_pkg->can('reset_usage');
+          #don't want to reset usage just cause we want a line item??
+          #&& $part_pkg->pkgpart == $real_pkgpart;
+  
+        warn "    bill recur\n" if $DEBUG > 1;
+        $lineitems++;
+  
+        # XXX shared with $recur_prog
+        $sdate = $cust_pkg->bill || $cust_pkg->setup || $time;
+  
+        #over two params!  lets at least switch to a hashref for the rest...
+        my %param = ( 'precommit_hooks' => \@precommit_hooks, );
+  
+        $recur = eval { $cust_pkg->calc_recur( \$sdate, \@details, \%param ) };
+        if ( $@ ) {
           $dbh->rollback if $oldAutoCommit;
-          return "can't create invoice line item for invoice #$invnum: $error";
+          return "$@ running calc_recur for $cust_pkg\n";
         }
-        $total_setup += $setup;
-        $total_recur += $recur;
-
-        ###
-        # handle taxes
-        ###
-
-        unless ( $self->tax =~ /Y/i || $self->payby eq 'COMP' ) {
 
-          my $prefix = 
-            ( $conf->exists('tax-ship_address') && length($self->ship_last) )
-            ? 'ship_'
-            : '';
-          my %taxhash = map { $_ => $self->get("$prefix$_") }
-                            qw( state county country );
-
-          $taxhash{'taxclass'} = $part_pkg->taxclass;
-
-          my @taxes = qsearch( 'cust_main_county', \%taxhash );
-
-          unless ( @taxes ) {
-            $taxhash{'taxclass'} = '';
-            @taxes =  qsearch( 'cust_main_county', \%taxhash );
-          }
+  
+        #change this bit to use Date::Manip? CAREFUL with timezones (see
+        # mailing list archive)
+        my ($sec,$min,$hour,$mday,$mon,$year) =
+          (localtime($sdate) )[0,1,2,3,4,5];
+    
+        #pro-rating magic - if $recur_prog fiddles $sdate, want to use that
+        # only for figuring next bill date, nothing else, so, reset $sdate again
+        # here
+        $sdate = $cust_pkg->bill || $cust_pkg->setup || $time;
+        $cust_pkg->last_bill($sdate);
+    
+        if ( $part_pkg->freq =~ /^\d+$/ ) {
+          $mon += $part_pkg->freq;
+          until ( $mon < 12 ) { $mon -= 12; $year++; }
+        } elsif ( $part_pkg->freq =~ /^(\d+)w$/ ) {
+          my $weeks = $1;
+          $mday += $weeks * 7;
+        } elsif ( $part_pkg->freq =~ /^(\d+)d$/ ) {
+          my $days = $1;
+          $mday += $days;
+        } elsif ( $part_pkg->freq =~ /^(\d+)h$/ ) {
+          my $hours = $1;
+          $hour += $hours;
+        } else {
+          $dbh->rollback if $oldAutoCommit;
+          return "unparsable frequency: ". $part_pkg->freq;
+        }
+        $cust_pkg->setfield('bill',
+          timelocal_nocheck($sec,$min,$hour,$mday,$mon,$year));
+  
+      }
 
-          #one more try at a whole-country tax rate
-          unless ( @taxes ) {
-            $taxhash{$_} = '' foreach qw( state county );
-            @taxes =  qsearch( 'cust_main_county', \%taxhash );
-          }
+      warn "\$setup is undefined" unless defined($setup);
+      warn "\$recur is undefined" unless defined($recur);
+      warn "\$cust_pkg->bill is undefined" unless defined($cust_pkg->bill);
+  
+      ###
+      # If there's line items, create em cust_bill_pkg records
+      # If $cust_pkg has been modified, update it (if we're a real pkgpart)
+      ###
+  
+      if ( $lineitems ) {
 
-          # maybe eliminate this entirely, along with all the 0% records
-          unless ( @taxes ) {
+        if ( $cust_pkg->modified && $cust_pkg->pkgpart == $real_pkgpart ) {
+          # hmm.. and if just the options are modified in some weird price plan?
+  
+          warn "  package ". $cust_pkg->pkgnum. " modified; updating\n"
+            if $DEBUG >1;
+  
+          my $error = $cust_pkg->replace( $old_cust_pkg,
+                                          'options' => { $cust_pkg->options },
+                                        );
+          if ( $error ) { #just in case
             $dbh->rollback if $oldAutoCommit;
-            return
-              "fatal: can't find tax rate for state/county/country/taxclass ".
-              join('/', ( map $self->get("$prefix$_"),
-                              qw(state county country)
-                        ),
-                        $part_pkg->taxclass ). "\n";
+            return "Error modifying pkgnum ". $cust_pkg->pkgnum. ": $error";
           }
+        }
   
-          foreach my $tax ( @taxes ) {
-
-            my $taxable_charged = 0;
-            $taxable_charged += $setup
-              unless $part_pkg->setuptax =~ /^Y$/i
-                  || $tax->setuptax =~ /^Y$/i;
-            $taxable_charged += $recur
-              unless $part_pkg->recurtax =~ /^Y$/i
-                  || $tax->recurtax =~ /^Y$/i;
-            next unless $taxable_charged;
-
-            if ( $tax->exempt_amount && $tax->exempt_amount > 0 ) {
-              #my ($mon,$year) = (localtime($sdate) )[4,5];
-              my ($mon,$year) = (localtime( $sdate || $cust_bill->_date ) )[4,5];
-              $mon++;
-              my $freq = $part_pkg->freq || 1;
-              if ( $freq !~ /(\d+)$/ ) {
-                $dbh->rollback if $oldAutoCommit;
-                return "daily/weekly package definitions not (yet?)".
-                       " compatible with monthly tax exemptions";
-              }
-              my $taxable_per_month =
-                sprintf("%.2f", $taxable_charged / $freq );
-
-              #call the whole thing off if this customer has any old
-              #exemption records...
-              my @cust_tax_exempt =
-                qsearch( 'cust_tax_exempt' => { custnum=> $self->custnum } );
-              if ( @cust_tax_exempt ) {
-                $dbh->rollback if $oldAutoCommit;
-                return
-                  'this customer still has old-style tax exemption records; '.
-                  'run bin/fs-migrate-cust_tax_exempt?';
+        $setup = sprintf( "%.2f", $setup );
+        $recur = sprintf( "%.2f", $recur );
+        if ( $setup < 0 && ! $conf->exists('allow_negative_charges') ) {
+          $dbh->rollback if $oldAutoCommit;
+          return "negative setup $setup for pkgnum ". $cust_pkg->pkgnum;
+        }
+        if ( $recur < 0 && ! $conf->exists('allow_negative_charges') ) {
+          $dbh->rollback if $oldAutoCommit;
+          return "negative recur $recur for pkgnum ". $cust_pkg->pkgnum;
+        }
+  
+        if ( $setup != 0 || $recur != 0 ) {
+  
+          warn "    charges (setup=$setup, recur=$recur); adding line items\n"
+            if $DEBUG > 1;
+          my $cust_bill_pkg = new FS::cust_bill_pkg {
+            'pkgnum'    => $cust_pkg->pkgnum,
+            'setup'     => $setup,
+            'unitsetup' => $unitsetup,
+            'recur'     => $recur,
+            'unitrecur' => $unitrecur,
+            'quantity'  => $cust_pkg->quantity,
+            'sdate'     => $sdate,
+            'edate'     => $cust_pkg->bill,
+            'details' => \@details,
+          };
+          $cust_bill_pkg->pkgpart_override($part_pkg->pkgpart)
+            unless $part_pkg->pkgpart == $real_pkgpart;
+          push @cust_bill_pkg, $cust_bill_pkg;
+
+          $total_setup += $setup;
+          $total_recur += $recur;
+  
+          ###
+          # handle taxes
+          ###
+  
+          unless ( $self->tax =~ /Y/i || $self->payby eq 'COMP' ) {
+  
+            my @taxes = ();
+            my @taxoverrides = $part_pkg->part_pkg_taxoverride;
+            
+            my $prefix = 
+              ( $conf->exists('tax-ship_address') && length($self->ship_last) )
+              ? 'ship_'
+              : '';
+  
+            if ( $conf->exists('enable_taxproducts')
+                 && (scalar(@taxoverrides) || $part_pkg->taxproductnum )
+               )
+            { 
+  
+              my @taxclassnums = ();
+              my $geocode = $self->geocode('cch');
+  
+              if ( scalar( @taxoverrides ) ) {
+                @taxclassnums = map { $_->taxclassnum } @taxoverrides;
+              }elsif ( $part_pkg->taxproductnum ) {
+                @taxclassnums = map { $_->taxclassnum }
+                                $part_pkg->part_pkg_taxrate('cch', $geocode);
               }
+  
+              my $extra_sql =
+                "AND (".
+                join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
+  
+              @taxes = qsearch({ 'table' => 'tax_rate',
+                                 'hashref' => { 'geocode' => $geocode, },
+                                 'extra_sql' => $extra_sql,
+                              })
+                if scalar(@taxclassnums);
+  
 
-              foreach my $which_month ( 1 .. $freq ) {
-
-                #maintain the new exemption table now
-                my $sql = "
-                  SELECT SUM(amount)
-                    FROM cust_tax_exempt_pkg
-                      LEFT JOIN cust_bill_pkg USING ( billpkgnum )
-                      LEFT JOIN cust_bill     USING ( invnum     )
-                    WHERE custnum = ?
-                      AND taxnum  = ?
-                      AND year    = ?
-                      AND month   = ?
-                ";
-                my $sth = dbh->prepare($sql) or do {
-                  $dbh->rollback if $oldAutoCommit;
-                  return "fatal: can't lookup exising exemption: ". dbh->errstr;
-                };
-                $sth->execute(
-                  $self->custnum,
-                  $tax->taxnum,
-                  1900+$year,
-                  $mon,
-                ) or do {
-                  $dbh->rollback if $oldAutoCommit;
-                  return "fatal: can't lookup exising exemption: ". dbh->errstr;
-                };
-                my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
-                
-                my $remaining_exemption =
-                  $tax->exempt_amount - $existing_exemption;
-                if ( $remaining_exemption > 0 ) {
-                  my $addl = $remaining_exemption > $taxable_per_month
-                    ? $taxable_per_month
-                    : $remaining_exemption;
-                  $taxable_charged -= $addl;
-
-                  my $cust_tax_exempt_pkg = new FS::cust_tax_exempt_pkg ( {
-                    'billpkgnum' => $cust_bill_pkg->billpkgnum,
-                    'taxnum'     => $tax->taxnum,
-                    'year'       => 1900+$year,
-                    'month'      => $mon,
-                    'amount'     => sprintf("%.2f", $addl ),
-                  } );
-                  $error = $cust_tax_exempt_pkg->insert;
-                  if ( $error ) {
-                    $dbh->rollback if $oldAutoCommit;
-                    return "fatal: can't insert cust_tax_exempt_pkg: $error";
-                  }
-                } # if $remaining_exemption > 0
-
-                #++
-                $mon++;
-                #until ( $mon < 12 ) { $mon -= 12; $year++; }
-                until ( $mon < 13 ) { $mon -= 12; $year++; }
+            }else{
   
-              } #foreach $which_month
+              my %taxhash = map { $_ => $self->get("$prefix$_") }
+                                qw( state county country );
   
-            } #if $tax->exempt_amount
+              $taxhash{'taxclass'} = $part_pkg->taxclass;
+  
+              @taxes = qsearch( 'cust_main_county', \%taxhash );
 
-            $taxable_charged = sprintf( "%.2f", $taxable_charged);
+            unless ( @taxes ) {
+              $taxhash{'taxclass'} = '';
+              @taxes =  qsearch( 'cust_main_county', \%taxhash );
+            }
 
-            #$tax += $taxable_charged * $cust_main_county->tax / 100
-            $tax{ $tax->taxname || 'Tax' } +=
-              $taxable_charged * $tax->tax / 100
+            #one more try at a whole-country tax rate
+            unless ( @taxes ) {
+              $taxhash{$_} = '' foreach qw( state county );
+              @taxes =  qsearch( 'cust_main_county', \%taxhash );
+            }
 
-          } #foreach my $tax ( @taxes )
+            } #if $conf->exists('enable_taxproducts') 
+  
+            # maybe eliminate this entirely, along with all the 0% records
+            unless ( @taxes ) {
+              $dbh->rollback if $oldAutoCommit;
+              my $error;
+              if ( $conf->exists('enable_taxproducts') ) { 
+                $error = 
+                  "fatal: can't find tax rate for zip/taxproduct/pkgpart ".
+                  join('/', ( map $self->get("$prefix$_"),
+                                  qw(zip)
+                            ),
+                            $part_pkg->taxproduct_description,
+                            $part_pkg->pkgpart ). "\n";
+              } else {
+                $error = 
+                  "fatal: can't find tax rate for state/county/country/taxclass ".
+                  join('/', ( map $self->get("$prefix$_"),
+                                  qw(state county country)
+                            ),
+                            $part_pkg->taxclass ). "\n";
+              }
+              return $error;
+            }
+    
+            foreach my $tax ( @taxes ) {
+              my $taxname = ref( $tax ). ' '. $tax->taxnum;
+              if ( exists( $taxlisthash{ $taxname } ) ) {
+                push @{ $taxlisthash{ $taxname  } }, $cust_bill_pkg;
+              }else{
+                $taxlisthash{ $taxname } = [ $tax, $cust_bill_pkg ];
+              }
+            }
 
-        } #unless $self->tax =~ /Y/i || $self->payby eq 'COMP'
 
-      } #if $setup != 0 || $recur != 0
-      
-    } #if $cust_pkg->modified
+          } #unless $self->tax =~ /Y/i || $self->payby eq 'COMP'
 
-  } #foreach my $cust_pkg
+        } #if $setup != 0 || $recur != 0
+      
+      } #if $cust_pkg->modified
 
-  unless ( $cust_bill->cust_bill_pkg ) {
-    $cust_bill->delete; #don't create an invoice w/o line items
+    } #foreach my $part_pkg
 
-   # XXX this seems to be broken
-   #( DBD::Pg::st execute failed: ERROR:  syntax error at or near "hcb" )
-#   # get rid of our fake history too, waste of unecessary space
-#    my $h_cleanup_query = q{
-#      DELETE FROM h_cust_bill hcb
-#       WHERE hcb.invnum = ?
-#      AND NOT EXISTS ( SELECT 1 FROM cust_bill cb where cb.invnum = hcb.invnum )
-#    };
-#    my $h_sth = $dbh->prepare($h_cleanup_query);
-#    $h_sth->execute($invnum);
+  } #foreach my $cust_pkg
 
+  unless ( @cust_bill_pkg ) { #don't create an invoice w/o line items
+    #but do commit any package date cycling that happened
     $dbh->commit or die $dbh->errstr if $oldAutoCommit;
     return '';
   }
 
-  my $charged = sprintf( "%.2f", $total_setup + $total_recur );
+  warn "having a look at the taxes we found...\n" if $DEBUG > 2;
+  foreach my $tax ( keys %taxlisthash ) {
+    my $tax_object = shift @{ $taxlisthash{$tax} };
+    warn "found ". $tax_object->taxname. " as $tax\n" if $DEBUG > 2;
+    my $listref_or_error = $tax_object->taxline( @{ $taxlisthash{$tax} } );
+    unless (ref($listref_or_error)) {
+      $dbh->rollback if $oldAutoCommit;
+      return $listref_or_error;
+    }
+    unshift @{ $taxlisthash{$tax} }, $tax_object;
 
-  foreach my $taxname ( grep { $tax{$_} > 0 } keys %tax ) {
-    my $tax = sprintf("%.2f", $tax{$taxname} );
-    $charged = sprintf( "%.2f", $charged+$tax );
+    warn "adding ". $listref_or_error->[1].
+         " as ". $listref_or_error->[0]. "\n"
+      if $DEBUG > 2;
+    $tax{ $tax_object->taxname } += $listref_or_error->[1];
+    if ( $taxname{ $listref_or_error->[0] } ) {
+      push @{ $taxname{ $listref_or_error->[0] } }, $tax_object->taxname;
+    }else{
+      $taxname{ $listref_or_error->[0] } = [ $tax_object->taxname ];
+    }
   
-    my $cust_bill_pkg = new FS::cust_bill_pkg ({
-      'invnum'   => $invnum,
+  }
+
+  #some taxes are taxed
+  my %totlisthash;
+  
+  warn "finding taxed taxes...\n" if $DEBUG > 2;
+  foreach my $tax ( keys %taxlisthash ) {
+    my $tax_object = shift @{ $taxlisthash{$tax} };
+    warn "found possible taxed tax ". $tax_object->taxname. " we call $tax\n"
+      if $DEBUG > 2;
+    next unless $tax_object->can('tax_on_tax');
+
+    foreach my $tot ( $tax_object->tax_on_tax( $self ) ) {
+      my $totname = ref( $tot ). ' '. $tot->taxnum;
+
+      warn "checking $totname which we call ". $tot->taxname. " as applicable\n"
+        if $DEBUG > 2;
+      next unless exists( $taxlisthash{ $totname } ); # only increase
+                                                      # existing taxes
+      warn "adding $totname to taxed taxes\n" if $DEBUG > 2;
+      if ( exists( $totlisthash{ $totname } ) ) {
+        push @{ $totlisthash{ $totname  } }, $tax{ $tax_object->taxname };
+      }else{
+        $totlisthash{ $totname } = [ $tot, $tax{ $tax_object->taxname } ];
+      }
+    }
+  }
+
+  warn "having a look at taxed taxes...\n" if $DEBUG > 2;
+  foreach my $tax ( keys %totlisthash ) {
+    my $tax_object = shift @{ $totlisthash{$tax} };
+    warn "found previously found taxed tax ". $tax_object->taxname. "\n"
+      if $DEBUG > 2;
+    my $listref_or_error = $tax_object->taxline( @{ $totlisthash{$tax} } );
+    unless (ref($listref_or_error)) {
+      $dbh->rollback if $oldAutoCommit;
+      return $listref_or_error;
+    }
+
+    warn "adding taxed tax amount ". $listref_or_error->[1].
+         " as ". $tax_object->taxname. "\n"
+      if $DEBUG;
+    $tax{ $tax_object->taxname } += $listref_or_error->[1];
+  }
+  
+  #consolidate and create tax line items
+  warn "consolidating and generating...\n" if $DEBUG > 2;
+  foreach my $taxname ( keys %taxname ) {
+    my $tax = 0;
+    my %seen = ();
+    warn "adding $taxname\n" if $DEBUG > 1;
+    foreach my $taxitem ( @{ $taxname{$taxname} } ) {
+      $tax += $tax{$taxitem} unless $seen{$taxitem};
+      warn "adding $tax{$taxitem}\n" if $DEBUG > 1;
+    }
+    next unless $tax;
+
+    $tax = sprintf('%.2f', $tax );
+    $total_setup = sprintf('%.2f', $total_setup+$tax );
+  
+    push @cust_bill_pkg, new FS::cust_bill_pkg {
       'pkgnum'   => 0,
       'setup'    => $tax,
       'recur'    => 0,
       'sdate'    => '',
       'edate'    => '',
       'itemdesc' => $taxname,
-    });
-    $error = $cust_bill_pkg->insert;
-    if ( $error ) {
-      $dbh->rollback if $oldAutoCommit;
-      return "can't create invoice line item for invoice #$invnum: $error";
-    }
-    $total_setup += $tax;
+    };
 
   }
 
-  $cust_bill->charged( sprintf( "%.2f", $total_setup + $total_recur ) );
-  $error = $cust_bill->replace;
+  my $charged = sprintf('%.2f', $total_setup + $total_recur );
+
+  #create the new invoice
+  my $cust_bill = new FS::cust_bill ( {
+    'custnum' => $self->custnum,
+    '_date'   => ( $options{'invoice_time'} || $time ),
+    'charged' => $charged,
+  } );
+  my $error = $cust_bill->insert;
   if ( $error ) {
     $dbh->rollback if $oldAutoCommit;
-    return "can't update charged for invoice #$invnum: $error";
+    return "can't create invoice for customer #". $self->custnum. ": $error";
   }
 
+  foreach my $cust_bill_pkg ( @cust_bill_pkg ) {
+    $cust_bill_pkg->invnum($cust_bill->invnum); 
+    my $error = $cust_bill_pkg->insert;
+    if ( $error ) {
+      $dbh->rollback if $oldAutoCommit;
+      return "can't create invoice line item: $error";
+    }
+  }
+    
+
   foreach my $hook ( @precommit_hooks ) { 
     eval {
       &{$hook}; #($self) ?
@@ -2423,15 +2520,30 @@ Currently available options are:
 
 =over 4
 
-=item invoice_time - Use this time when deciding when to print invoices and late notices on those invoices.  The default is now.  It is specified as a UNIX timestamp; see L<perlfunc/"time">).  Also see L<Time::Local> and L<Date::Parse> for conversion functions.
+=item invoice_time
+
+Use this time when deciding when to print invoices and late notices on those invoices.  The default is now.  It is specified as a UNIX timestamp; see L<perlfunc/"time">).  Also see L<Time::Local> and L<Date::Parse> for conversion functions.
+
+=item retry
 
-=item retry - Retry card/echeck/LEC transactions even when not scheduled by invoice events.
+Retry card/echeck/LEC transactions even when not scheduled by invoice events.
 
-=item quiet - set true to surpress email card/ACH decline notices.
+=item quiet
 
-=item check_freq - "1d" for the traditional, daily events (the default), or "1m" for the new monthly events (part_event.check_freq)
+set true to surpress email card/ACH decline notices.
+
+=item check_freq
+
+"1d" for the traditional, daily events (the default), or "1m" for the new monthly events (part_event.check_freq)
+
+=item payby
+
+allows for one time override of normal customer billing method
+
+=item debug
+
+Debugging level.  Default is 0 (no debugging), or can be set to 1 (passed-in options), 2 (traces progress), 3 (more information), or 4 (include full search queries)
 
-=item payby - allows for one time override of normal customer billing method
 
 =back
 
@@ -2475,6 +2587,7 @@ sub collect {
   # false laziness w/pay_batch::import_results
 
   my $due_cust_event = $self->due_cust_event(
+    'debug'      => ( $options{'debug'} || 0 ),
     'time'       => $invoice_time,
     'check_freq' => $options{'check_freq'},
   );
@@ -2488,7 +2601,16 @@ sub collect {
     #XXX lock event
     
     #re-eval event conditions (a previous event could have changed things)
-    next unless $cust_event->test_conditions( 'time' => $invoice_time );
+    unless ( $cust_event->test_conditions( 'time' => $invoice_time ) ) {
+      #don't leave stray "new/locked" records around
+      my $error = $cust_event->delete;
+      if ( $error ) {
+        #gah, even with transactions
+        $dbh->commit if $oldAutoCommit; #well.
+        return $error;
+      }
+      next;
+    }
 
     {
       local $realtime_bop_decline_quiet = 1 if $options{'quiet'};
@@ -2528,15 +2650,25 @@ options are:
 
 =over 4
 
-=item check_freq - Search only for events of this check frequency (how often events of this type are checked); currently "1d" (daily, the default) and "1m" (monthly) are recognized.
+=item check_freq
+
+Search only for events of this check frequency (how often events of this type are checked); currently "1d" (daily, the default) and "1m" (monthly) are recognized.
+
+=item time
+
+"Current time" for the events.
 
-=item time - "Current time" for the events.
+=item debug
 
-=item debug - Debugging level.  Default is 0 (no debugging), or can be set to 1 (passed-in options), 2 (traces progress), or 3 (more information)
+Debugging level.  Default is 0 (no debugging), or can be set to 1 (passed-in options), 2 (traces progress), 3 (more information), or 4 (include full search queries)
 
-=item eventtable - Only return events for the specified eventtable (by default, events of all eventtables are returned)
+=item eventtable
 
-=item objects - Explicitly pass the objects to be tested (typically used with eventtable).
+Only return events for the specified eventtable (by default, events of all eventtables are returned)
+
+=item objects
+
+Explicitly pass the objects to be tested (typically used with eventtable).
 
 =back
 
@@ -2626,7 +2758,10 @@ sub due_cust_event {
 
       $extra_sql .= " $order";
 
+      warn "searching for events for $eventtable ". $object->$pkey. "\n"
+        if $opt{'debug'} > 2;
       my @part_event = qsearch( {
+        'debug'     => ( $opt{'debug'} > 3 ? 1 : 0 ),
         'select'    => 'part_event.*',
         'table'     => 'part_event',
         'addl_from' => "$cross $join",
@@ -2648,7 +2783,7 @@ sub due_cust_event {
     }
 
     warn "    ". scalar(@e_cust_event).
-         " subtotal possible cust events found for $eventtable"
+         " subtotal possible cust events found for $eventtable\n"
       if $DEBUG > 1;
 
     push @cust_event, @e_cust_event;
@@ -2673,7 +2808,8 @@ sub due_cust_event {
     if $DEBUG; # > 1;
 
   warn "    invalid conditions not eliminated with condition_sql:\n".
-       join('', map "      $_: ".$unsat{$_}."\n", keys %unsat );
+       join('', map "      $_: ".$unsat{$_}."\n", keys %unsat )
+    if $DEBUG; # > 1;
 
   ##
   # 3: insert
@@ -2760,6 +2896,7 @@ sub retry_realtime {
 
   my @cust_event = qsearchs({
     'table'     => 'cust_event',
+    'select'    => 'cust_event.*',
     'addl_from' => "LEFT JOIN part_event USING ( eventpart ) $join",
     'hashref'   => { 'status' => 'done' },
     'extra_sql' => " AND statustext IS NOT NULL AND statustext != '' ".
@@ -2799,7 +2936,7 @@ L<http://420.am/business-onlinepayment> for supported gateways.
 
 Available methods are: I<CC>, I<ECHECK> and I<LEC>
 
-Available options are: I<description>, I<invnum>, I<quiet>
+Available options are: I<description>, I<invnum>, I<quiet>, I<paynum_ref>, I<payunique>
 
 The additional options I<payname>, I<address1>, I<address2>, I<city>, I<state>,
 I<zip>, I<payinfo> and I<paydate> are also available.  Any of these options,
@@ -2814,6 +2951,11 @@ call the B<apply_payments> method.
 
 I<quiet> can be set true to surpress email decline notices.
 
+I<paynum_ref> can be set to a scalar reference.  It will be filled in with the
+resulting paynum, if any.
+
+I<payunique> is a unique identifier for this payment.
+
 (moved from cust_bill) (probably should get realtime_{card,ach,lec} here too)
 
 =cut
@@ -2827,6 +2969,8 @@ sub realtime_bop {
 
   $options{'description'} ||= 'Internet services';
 
+  return $self->fake_bop($method, $amount, %options) if $options{'fake'};
+
   eval "use Business::OnlinePayment";  
   die $@ if $@;
 
@@ -2960,6 +3104,10 @@ sub realtime_bop {
   $content{invoice_number} = $options{'invnum'}
     if exists($options{'invnum'}) && length($options{'invnum'});
 
+  $content{email_customer} = 
+    (    $conf->exists('business-onlinepayment-email_customer')
+      || $conf->exists('business-onlinepayment-email-override') );
+      
   my $paydate = '';
   if ( $method eq 'CC' ) { 
 
@@ -2973,7 +3121,7 @@ sub realtime_bop {
     my $paycvv = exists($options{'paycvv'})
                    ? $options{'paycvv'}
                    : $self->paycvv;
-    $content{cvv2} = $self->paycvv
+    $content{cvv2} = $paycvv
       if length($paycvv);
 
     my $paystart_month = exists($options{'paystart_month'})
@@ -3032,6 +3180,49 @@ sub realtime_bop {
   # run transaction(s)
   ###
 
+  my $balance = exists( $options{'balance'} )
+                  ? $options{'balance'}
+                  : $self->balance;
+
+  $self->select_for_update; #mutex ... just until we get our pending record in
+
+  #the checks here are intended to catch concurrent payments
+  #double-form-submission prevention is taken care of in cust_pay_pending::check
+
+  #check the balance
+  return "The customer's balance has changed; $method transaction aborted."
+    if $self->balance < $balance;
+    #&& $self->balance < $amount; #might as well anyway?
+
+  #also check and make sure there aren't *other* pending payments for this cust
+
+  my @pending = qsearch('cust_pay_pending', {
+    'custnum' => $self->custnum,
+    'status'  => { op=>'!=', value=>'done' } 
+  });
+  return "A payment is already being processed for this customer (".
+         join(', ', map 'paypendingnum '. $_->paypendingnum, @pending ).
+         "); $method transaction aborted."
+    if scalar(@pending);
+
+  #okay, good to go, if we're a duplicate, cust_pay_pending will kick us out
+
+  my $cust_pay_pending = new FS::cust_pay_pending {
+    'custnum'    => $self->custnum,
+    #'invnum'     => $options{'invnum'},
+    'paid'       => $amount,
+    '_date'      => '',
+    'payby'      => $method2payby{$method},
+    'payinfo'    => $payinfo,
+    'paydate'    => $paydate,
+    'status'     => 'new',
+    'gatewaynum' => ( $payment_gateway ? $payment_gateway->gatewaynum : '' ),
+  };
+  $cust_pay_pending->payunique( $options{payunique} )
+    if defined($options{payunique}) && length($options{payunique});
+  my $cpp_new_err = $cust_pay_pending->insert; #mutex lost when this is inserted
+  return $cpp_new_err if $cpp_new_err;
+
   my( $action1, $action2 ) = split(/\s*\,\s*/, $action );
 
   my $transaction = new Business::OnlinePayment( $processor, @bop_options );
@@ -3065,9 +3256,33 @@ sub realtime_bop {
     'phone'          => $self->daytime || $self->night,
     %content, #after
   );
-  $transaction->submit();
+
+  $cust_pay_pending->status('pending');
+  my $cpp_pending_err = $cust_pay_pending->replace;
+  return $cpp_pending_err if $cpp_pending_err;
+
+  #config?
+  my $BOP_TESTING = 0;
+  my $BOP_TESTING_SUCCESS = 1;
+
+  unless ( $BOP_TESTING ) {
+    $transaction->submit();
+  } else {
+    if ( $BOP_TESTING_SUCCESS ) {
+      $transaction->is_success(1);
+      $transaction->authorization('fake auth');
+    } else {
+      $transaction->is_success(0);
+      $transaction->error_message('fake failure');
+    }
+  }
 
   if ( $transaction->is_success() && $action2 ) {
+
+    $cust_pay_pending->status('authorized');
+    my $cpp_authorized_err = $cust_pay_pending->replace;
+    return $cpp_authorized_err if $cpp_authorized_err;
+
     my $auth = $transaction->authorization;
     my $ordernum = $transaction->can('order_number')
                    ? $transaction->order_number
@@ -3109,6 +3324,10 @@ sub realtime_bop {
 
   }
 
+  $cust_pay_pending->status($transaction->is_success() ? 'captured' : 'declined');
+  my $cpp_captured_err = $cust_pay_pending->replace;
+  return $cpp_captured_err if $cpp_captured_err;
+
   ###
   # remove paycvv after initial transaction
   ###
@@ -3131,12 +3350,6 @@ sub realtime_bop {
 
   if ( $transaction->is_success() ) {
 
-    my %method2payby = (
-      'CC'     => 'CARD',
-      'ECHECK' => 'CHEK',
-      'LEC'    => 'LECB',
-    );
-
     my $paybatch = '';
     if ( $payment_gateway ) { # agent override
       $paybatch = $payment_gateway->gatewaynum. '-';
@@ -3152,13 +3365,21 @@ sub realtime_bop {
        'custnum'  => $self->custnum,
        'invnum'   => $options{'invnum'},
        'paid'     => $amount,
-       '_date'     => '',
+       '_date'    => '',
        'payby'    => $method2payby{$method},
        'payinfo'  => $payinfo,
        'paybatch' => $paybatch,
        'paydate'  => $paydate,
     } );
-    $cust_pay->payunique( $options{payunique} ) if length($options{payunique});
+    #doesn't hurt to know, even though the dup check is in cust_pay_pending now
+    $cust_pay->payunique( $options{payunique} )
+      if defined($options{payunique}) && length($options{payunique});
+
+    my $oldAutoCommit = $FS::UID::AutoCommit;
+    local $FS::UID::AutoCommit = 0;
+    my $dbh = dbh;
+
+    #start a transaction, insert the cust_pay and set cust_pay_pending.status to done in a single transction
 
     my $error = $cust_pay->insert($options{'manual'} ? ( 'manual' => 1 ) : () );
 
@@ -3168,16 +3389,41 @@ sub realtime_bop {
                                       ( 'manual' => 1 ) : ()
                                     );
       if ( $error2 ) {
-        # gah, even with transactions.
-        my $e = 'WARNING: Card/ACH debited but database not updated - '.
+        # gah.  but at least we have a record of the state we had to abort in
+        # from cust_pay_pending now.
+        my $e = "WARNING: $method captured but payment not recorded - ".
                 "error inserting payment ($processor): $error2".
                 " (previously tried insert with invnum #$options{'invnum'}" .
-                ": $error )";
+                ": $error ) - pending payment saved as paypendingnum ".
+                $cust_pay_pending->paypendingnum. "\n";
         warn $e;
         return $e;
       }
     }
-    return ''; #no error
+
+    if ( $options{'paynum_ref'} ) {
+      ${ $options{'paynum_ref'} } = $cust_pay->paynum;
+    }
+
+    $cust_pay_pending->status('done');
+    $cust_pay_pending->statustext('captured');
+    my $cpp_done_err = $cust_pay_pending->replace;
+
+    if ( $cpp_done_err ) {
+
+      $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+      my $e = "WARNING: $method captured but payment not recorded - ".
+              "error updating status for paypendingnum ".
+              $cust_pay_pending->paypendingnum. ": $cpp_done_err \n";
+      warn $e;
+      return $e;
+
+    } else {
+
+      $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+      return ''; #no error
+
+    }
 
   } else {
 
@@ -3238,10 +3484,91 @@ sub realtime_bop {
         if $error;
 
     }
-  
-    return $perror;
+
+    $cust_pay_pending->status('done');
+    $cust_pay_pending->statustext("declined: $perror");
+    my $cpp_done_err = $cust_pay_pending->replace;
+    if ( $cpp_done_err ) {
+      my $e = "WARNING: $method declined but pending payment not resolved - ".
+              "error updating status for paypendingnum ".
+              $cust_pay_pending->paypendingnum. ": $cpp_done_err \n";
+      warn $e;
+      $perror = "$e ($perror)";
+    }
+
+    return $perror;
+  }
+
+}
+
+=item fake_bop
+
+=cut
+
+sub fake_bop {
+  my( $self, $method, $amount, %options ) = @_;
+
+  if ( $options{'fake_failure'} ) {
+     return "Error: No error; test failure requested with fake_failure";
+  }
+
+  my %method2payby = (
+    'CC'     => 'CARD',
+    'ECHECK' => 'CHEK',
+    'LEC'    => 'LECB',
+  );
+
+  #my $paybatch = '';
+  #if ( $payment_gateway ) { # agent override
+  #  $paybatch = $payment_gateway->gatewaynum. '-';
+  #}
+  #
+  #$paybatch .= "$processor:". $transaction->authorization;
+  #
+  #$paybatch .= ':'. $transaction->order_number
+  #  if $transaction->can('order_number')
+  #  && length($transaction->order_number);
+
+  my $paybatch = 'FakeProcessor:54:32';
+
+  my $cust_pay = new FS::cust_pay ( {
+     'custnum'  => $self->custnum,
+     'invnum'   => $options{'invnum'},
+     'paid'     => $amount,
+     '_date'    => '',
+     'payby'    => $method2payby{$method},
+     #'payinfo'  => $payinfo,
+     'payinfo'  => '4111111111111111',
+     'paybatch' => $paybatch,
+     #'paydate'  => $paydate,
+     'paydate'  => '2012-05-01',
+  } );
+  $cust_pay->payunique( $options{payunique} ) if length($options{payunique});
+
+  my $error = $cust_pay->insert($options{'manual'} ? ( 'manual' => 1 ) : () );
+
+  if ( $error ) {
+    $cust_pay->invnum(''); #try again with no specific invnum
+    my $error2 = $cust_pay->insert( $options{'manual'} ?
+                                    ( 'manual' => 1 ) : ()
+                                  );
+    if ( $error2 ) {
+      # gah, even with transactions.
+      my $e = 'WARNING: Card/ACH debited but database not updated - '.
+              "error inserting (fake!) payment: $error2".
+              " (previously tried insert with invnum #$options{'invnum'}" .
+              ": $error )";
+      warn $e;
+      return $e;
+    }
+  }
+
+  if ( $options{'paynum_ref'} ) {
+    ${ $options{'paynum_ref'} } = $cust_pay->paynum;
   }
 
+  return ''; #no error
+
 }
 
 =item default_payment_gateway
@@ -3257,7 +3584,7 @@ sub default_payment_gateway {
   #load up config
   my $bop_config = 'business-onlinepayment';
   $bop_config .= '-ach'
-    if $method eq 'ECHECK' && $conf->exists($bop_config. '-ach');
+    if $method =~ /^(ECHECK|CHEK)$/ && $conf->exists($bop_config. '-ach');
   my ( $processor, $login, $password, $action, @bop_options ) =
     $conf->config($bop_config);
   $action ||= 'normal authorization';
@@ -3638,6 +3965,8 @@ sub batch_card {
   local $FS::UID::AutoCommit = 0;
   my $dbh = dbh;
 
+  #this needs to handle mysql as well as Pg, like svc_acct.pm
+  #(make it into a common function if folks need to do batching with mysql)
   $dbh->do("LOCK TABLE pay_batch IN SHARE ROW EXCLUSIVE MODE")
     or return "Cannot lock pay_batch: " . $dbh->errstr;
 
@@ -4336,13 +4665,13 @@ otherwise returns false.
 =cut
 
 sub credit {
-  my( $self, $amount, $reason ) = @_;
+  my( $self, $amount, $reason, %options ) = @_;
   my $cust_credit = new FS::cust_credit {
     'custnum' => $self->custnum,
     'amount'  => $amount,
     'reason'  => $reason,
   };
-  $cust_credit->insert;
+  $cust_credit->insert(%options);
 }
 
 =item charge AMOUNT [ PKG [ COMMENT [ TAXCLASS ] ] ]
@@ -4354,16 +4683,19 @@ the error, otherwise returns false.
 
 sub charge {
   my $self = shift;
-  my ( $amount, $pkg, $comment, $taxclass, $additional );
+  my ( $amount, $quantity, $pkg, $comment, $taxclass, $additional, $classnum );
   if ( ref( $_[0] ) ) {
     $amount     = $_[0]->{amount};
+    $quantity   = exists($_[0]->{quantity}) ? $_[0]->{quantity} : 1;
     $pkg        = exists($_[0]->{pkg}) ? $_[0]->{pkg} : 'One-time charge';
     $comment    = exists($_[0]->{comment}) ? $_[0]->{comment}
                                            : '$'. sprintf("%.2f",$amount);
     $taxclass   = exists($_[0]->{taxclass}) ? $_[0]->{taxclass} : '';
+    $classnum   = exists($_[0]->{classnum}) ? $_[0]->{classnum} : '';
     $additional = $_[0]->{additional};
   }else{
     $amount     = shift;
+    $quantity   = 1;
     $pkg        = @_ ? shift : 'One-time charge';
     $comment    = @_ ? shift : '$'. sprintf("%.2f",$amount);
     $taxclass   = @_ ? shift : '';
@@ -4387,6 +4719,7 @@ sub charge {
     'plan'     => 'flat',
     'freq'     => 0,
     'disabled' => 'Y',
+    'classnum' => $classnum ? $classnum : '',
     'taxclass' => $taxclass,
   } );
 
@@ -4415,8 +4748,9 @@ sub charge {
   }
 
   my $cust_pkg = new FS::cust_pkg ( {
-    'custnum' => $self->custnum,
-    'pkgpart' => $pkgpart,
+    'custnum'  => $self->custnum,
+    'pkgpart'  => $pkgpart,
+    'quantity' => $quantity,
   } );
 
   $error = $cust_pkg->insert;
@@ -4581,6 +4915,40 @@ sub country_full {
   code2country($self->country);
 }
 
+=item geocode DATA_VENDOR
+
+Returns a value for the customer location as encoded by DATA_VENDOR.
+Currently this only makes sense for "CCH" as DATA_VENDOR.
+
+=cut
+
+sub geocode {
+  my ($self, $data_vendor) = (shift, shift);  #always cch for now
+
+  my $prefix = ( $conf->exists('tax-ship_address') && length($self->ship_last) )
+               ? 'ship_'
+               : '';
+
+  my ($zip,$plus4) = split /-/, $self->get("${prefix}zip")
+    if $self->country eq 'US';
+
+  #CCH specific location stuff
+  my $extra_sql = "AND plus4lo <= '$plus4' AND plus4hi >= '$plus4'";
+
+  my $geocode = '';
+  my $cust_tax_location =
+    qsearchs( {
+                'table'     => 'cust_tax_location', 
+                'hashref'   => { 'zip' => $zip, 'data_vendor' => $data_vendor },
+                'extra_sql' => $extra_sql,
+              }
+            );
+  $geocode = $cust_tax_location->geocode
+    if $cust_tax_location;
+
+  $geocode;
+}
+
 =item cust_status
 
 =item status
@@ -4743,7 +5111,7 @@ sub prospect_sql { "
 =item active_sql
 
 Returns an SQL expression identifying active cust_main records (customers with
-no active recurring packages, but otherwise unsuspended/uncancelled).
+active recurring packages).
 
 =cut
 
@@ -4755,7 +5123,7 @@ sub active_sql { "
 =item inactive_sql
 
 Returns an SQL expression identifying inactive cust_main records (customers with
-active recurring packages).
+no active recurring packages, but otherwise unsuspended/uncancelled).
 
 =cut
 
@@ -4791,17 +5159,16 @@ sub cancelled_sql { cancel_sql(@_); }
 sub cancel_sql {
 
   my $recurring_sql = FS::cust_pkg->recurring_sql;
-  #my $recurring_sql = "
-  #  '0' != ( select freq from part_pkg
-  #             where cust_pkg.pkgpart = part_pkg.pkgpart )
-  #";
+  my $cancelled_sql = FS::cust_pkg->cancelled_sql;
 
   "
-    0 < ( $select_count_pkgs )
+        0 < ( $select_count_pkgs )
+    AND 0 < ( $select_count_pkgs AND $recurring_sql AND $cancelled_sql   )
     AND 0 = ( $select_count_pkgs AND $recurring_sql
                   AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 )
             )
   ";
+
 }
 
 =item uncancel_sql
@@ -4829,58 +5196,396 @@ Returns an SQL fragment to retreive the balance.
 =cut
 
 sub balance_sql { "
-    COALESCE( ( SELECT SUM(charged) FROM cust_bill
-                  WHERE cust_bill.custnum   = cust_main.custnum ), 0)
-  - COALESCE( ( SELECT SUM(paid)    FROM cust_pay
-                  WHERE cust_pay.custnum    = cust_main.custnum ), 0)
-  - COALESCE( ( SELECT SUM(amount)  FROM cust_credit
-                  WHERE cust_credit.custnum = cust_main.custnum ), 0)
-  + COALESCE( ( SELECT SUM(refund)  FROM cust_refund
-                   WHERE cust_refund.custnum = cust_main.custnum ), 0)
+    ( SELECT COALESCE( SUM(charged), 0 ) FROM cust_bill
+        WHERE cust_bill.custnum   = cust_main.custnum     )
+  - ( SELECT COALESCE( SUM(paid),    0 ) FROM cust_pay
+        WHERE cust_pay.custnum    = cust_main.custnum     )
+  - ( SELECT COALESCE( SUM(amount),  0 ) FROM cust_credit
+        WHERE cust_credit.custnum = cust_main.custnum     )
+  + ( SELECT COALESCE( SUM(refund),  0 ) FROM cust_refund
+        WHERE cust_refund.custnum = cust_main.custnum     )
 "; }
 
-=item balance_date_sql TIME
+=item balance_date_sql START_TIME [ END_TIME [ OPTION => VALUE ... ] ]
 
 Returns an SQL fragment to retreive the balance for this customer, only
-considering invoices with date earlier than TIME. (total_owed_date minus total_credited minus
-total_unapplied_payments).  TIME is specified as an SQL fragment or a numeric
-UNIX timestamp; see L<perlfunc/"time">).  Also see L<Time::Local> and
-L<Date::Parse> for conversion functions.
+considering invoices with date earlier than START_TIME, and optionally not
+later than END_TIME (total_owed_date minus total_credited minus
+total_unapplied_payments).
+
+Times are specified as SQL fragments or numeric
+UNIX timestamps; see L<perlfunc/"time">).  Also see L<Time::Local> and
+L<Date::Parse> for conversion functions.  The empty string can be passed
+to disable that time constraint completely.
+
+Available options are:
+
+=over 4
+
+=item unapplied_date - set to true to disregard unapplied credits, payments and refunds outside the specified time period - by default the time period restriction only applies to invoices (useful for reporting, probably a bad idea for event triggering)
+
+=item total - set to true to remove all customer comparison clauses, for totals
+
+=item where - WHERE clause hashref (elements "AND"ed together) (typically used with the total option)
+
+=item join - JOIN clause (typically used with the total option)
+
+=item 
+
+=back
 
 =cut
 
 sub balance_date_sql {
-  my( $class, $time ) = @_;
+  my( $class, $start, $end, %opt ) = @_;
 
-  my $owed_sql         = FS::cust_bill->owed_sql;
-  my $unapp_refund_sql = FS::cust_refund->unapplied_sql;
-  #my $unapp_credit_sql = FS::cust_credit->unapplied_sql;
-  my $unapp_credit_sql = FS::cust_credit->credited_sql;
-  my $unapp_pay_sql    = FS::cust_pay->unapplied_sql;
+  my $owed         = FS::cust_bill->owed_sql;
+  my $unapp_refund = FS::cust_refund->unapplied_sql;
+  my $unapp_credit = FS::cust_credit->unapplied_sql;
+  my $unapp_pay    = FS::cust_pay->unapplied_sql;
 
-  "
-      COALESCE( ( SELECT SUM($owed_sql) FROM cust_bill
-                    WHERE cust_bill.custnum   = cust_main.custnum
-                      AND cust_bill._date    <= $time             )
-                ,0
-              )
-    + COALESCE( ( SELECT SUM($unapp_refund_sql) FROM cust_refund
-                    WHERE cust_refund.custnum = cust_main.custnum )
-                ,0
-              )
-    - COALESCE( ( SELECT SUM($unapp_credit_sql) FROM cust_credit
-                    WHERE cust_credit.custnum = cust_main.custnum )
-                ,0
-              )
-    - COALESCE( ( SELECT SUM($unapp_pay_sql) FROM cust_pay
-                    WHERE cust_pay.custnum = cust_main.custnum )
-                ,0
-              )
+  my $j = $opt{'join'} || '';
+
+  my $owed_wh   = $class->_money_table_where( 'cust_bill',   $start,$end,%opt );
+  my $refund_wh = $class->_money_table_where( 'cust_refund', $start,$end,%opt );
+  my $credit_wh = $class->_money_table_where( 'cust_credit', $start,$end,%opt );
+  my $pay_wh    = $class->_money_table_where( 'cust_pay',    $start,$end,%opt );
 
+  "   ( SELECT COALESCE(SUM($owed),         0) FROM cust_bill   $j $owed_wh   )
+    + ( SELECT COALESCE(SUM($unapp_refund), 0) FROM cust_refund $j $refund_wh )
+    - ( SELECT COALESCE(SUM($unapp_credit), 0) FROM cust_credit $j $credit_wh )
+    - ( SELECT COALESCE(SUM($unapp_pay),    0) FROM cust_pay    $j $pay_wh    )
   ";
 
 }
 
+=item _money_table_where TABLE START_TIME [ END_TIME [ OPTION => VALUE ... ] ]
+
+Helper method for balance_date_sql; name (and usage) subject to change
+(suggestions welcome).
+
+Returns a WHERE clause for the specified monetary TABLE (cust_bill,
+cust_refund, cust_credit or cust_pay).
+
+If TABLE is "cust_bill" or the unapplied_date option is true, only
+considers records with date earlier than START_TIME, and optionally not
+later than END_TIME .
+
+=cut
+
+sub _money_table_where {
+  my( $class, $table, $start, $end, %opt ) = @_;
+
+  my @where = ();
+  push @where, "cust_main.custnum = $table.custnum" unless $opt{'total'};
+  if ( $table eq 'cust_bill' || $opt{'unapplied_date'} ) {
+    push @where, "$table._date <= $start" if defined($start) && length($start);
+    push @where, "$table._date >  $end"   if defined($end)   && length($end);
+  }
+  push @where, @{$opt{'where'}} if $opt{'where'};
+  my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where ) : '';
+
+  $where;
+
+}
+
+=item search_sql HASHREF
+
+(Class method)
+
+Returns a qsearch hash expression to search for parameters specified in HREF.
+Valid parameters are
+
+=over 4
+
+=item agentnum
+
+=item status
+
+=item cancelled_pkgs
+
+bool
+
+=item signupdate
+
+listref of start date, end date
+
+=item payby
+
+listref
+
+=item current_balance
+
+listref (list returned by FS::UI::Web::parse_lt_gt($cgi, 'current_balance'))
+
+=item cust_fields
+
+=item flattened_pkgs
+
+bool
+
+=back
+
+=cut
+
+sub search_sql {
+  my ($class, $params) = @_;
+
+  my $dbh = dbh;
+
+  my @where = ();
+  my $orderby;
+
+  ##
+  # parse agent
+  ##
+
+  if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
+    push @where,
+      "cust_main.agentnum = $1";
+  }
+
+  ##
+  # parse status
+  ##
+
+  #prospect active inactive suspended cancelled
+  if ( grep { $params->{'status'} eq $_ } FS::cust_main->statuses() ) {
+    my $method = $params->{'status'}. '_sql';
+    #push @where, $class->$method();
+    push @where, FS::cust_main->$method();
+  }
+  
+  ##
+  # parse cancelled package checkbox
+  ##
+
+  my $pkgwhere = "";
+
+  $pkgwhere .= "AND (cancel = 0 or cancel is null)"
+    unless $params->{'cancelled_pkgs'};
+
+  ##
+  # dates
+  ##
+
+  foreach my $field (qw( signupdate )) {
+
+    next unless exists($params->{$field});
+
+    my($beginning, $ending) = @{$params->{$field}};
+
+    push @where,
+      "cust_main.$field IS NOT NULL",
+      "cust_main.$field >= $beginning",
+      "cust_main.$field <= $ending";
+
+    $orderby ||= "ORDER BY cust_main.$field";
+
+  }
+
+  ###
+  # payby
+  ###
+
+  my @payby = grep /^([A-Z]{4})$/, @{ $params->{'payby'} };
+  if ( @payby ) {
+    push @where, '( '. join(' OR ', map "cust_main.payby = '$_'", @payby). ' )';
+  }
+
+  ##
+  # amounts
+  ##
+
+  #my $balance_sql = $class->balance_sql();
+  my $balance_sql = FS::cust_main->balance_sql();
+
+  push @where, map { s/current_balance/$balance_sql/; $_ }
+                   @{ $params->{'current_balance'} };
+
+  ##
+  # setup queries, subs, etc. for the search
+  ##
+
+  $orderby ||= 'ORDER BY custnum';
+
+  # here is the agent virtualization
+  push @where, $FS::CurrentUser::CurrentUser->agentnums_sql;
+
+  my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
+
+  my $addl_from = 'LEFT JOIN cust_pkg USING ( custnum  ) ';
+
+  my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql";
+
+  my $select = join(', ', 
+                 'cust_main.custnum',
+                 FS::UI::Web::cust_sql_fields($params->{'cust_fields'}),
+               );
+
+  my(@extra_headers) = ();
+  my(@extra_fields)  = ();
+
+  if ($params->{'flattened_pkgs'}) {
+
+    if ($dbh->{Driver}->{Name} eq 'Pg') {
+
+      $select .= ", array_to_string(array(select pkg from cust_pkg left join part_pkg using ( pkgpart ) where cust_main.custnum = cust_pkg.custnum $pkgwhere),'|') as magic";
+
+    }elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) {
+      $select .= ", GROUP_CONCAT(pkg SEPARATOR '|') as magic";
+      $addl_from .= " LEFT JOIN part_pkg using ( pkgpart )";
+    }else{
+      warn "warning: unknown database type ". $dbh->{Driver}->{Name}. 
+           "omitting packing information from report.";
+    }
+
+    my $header_query = "SELECT COUNT(cust_pkg.custnum = cust_main.custnum) AS count FROM cust_main $addl_from $extra_sql $pkgwhere group by cust_main.custnum order by count desc limit 1";
+
+    my $sth = dbh->prepare($header_query) or die dbh->errstr;
+    $sth->execute() or die $sth->errstr;
+    my $headerrow = $sth->fetchrow_arrayref;
+    my $headercount = $headerrow ? $headerrow->[0] : 0;
+    while($headercount) {
+      unshift @extra_headers, "Package ". $headercount;
+      unshift @extra_fields, eval q!sub {my $c = shift;
+                                         my @a = split '\|', $c->magic;
+                                         my $p = $a[!.--$headercount. q!];
+                                         $p;
+                                        };!;
+    }
+
+  }
+
+  my $sql_query = {
+    'table'         => 'cust_main',
+    'select'        => $select,
+    'hashref'       => {},
+    'extra_sql'     => $extra_sql,
+    'order_by'      => $orderby,
+    'count_query'   => $count_query,
+    'extra_headers' => \@extra_headers,
+    'extra_fields'  => \@extra_fields,
+  };
+
+}
+
+=item email_search_sql HASHREF
+
+(Class method)
+
+Emails a notice to the specified customers.
+
+Valid parameters are those of the L<search_sql> method, plus the following:
+
+=over 4
+
+=item from
+
+From: address
+
+=item subject
+
+Email Subject:
+
+=item html_body
+
+HTML body
+
+=item text_body
+
+Text body
+
+=item job
+
+Optional job queue job for status updates.
+
+=back
+
+Returns an error message, or false for success.
+
+If an error occurs during any email, stops the enture send and returns that
+error.  Presumably if you're getting SMTP errors aborting is better than 
+retrying everything.
+
+=cut
+
+sub email_search_sql {
+  my($class, $params) = @_;
+
+  my $from = delete $params->{from};
+  my $subject = delete $params->{subject};
+  my $html_body = delete $params->{html_body};
+  my $text_body = delete $params->{text_body};
+
+  my $job = delete $params->{'job'};
+
+  my $sql_query = $class->search_sql($params);
+
+  my $count_query   = delete($sql_query->{'count_query'});
+  my $count_sth = dbh->prepare($count_query)
+    or die "Error preparing $count_query: ". dbh->errstr;
+  $count_sth->execute
+    or die "Error executing $count_query: ". $count_sth->errstr;
+  my $count_arrayref = $count_sth->fetchrow_arrayref;
+  my $num_cust = $count_arrayref->[0];
+
+  #my @extra_headers = @{ delete($sql_query->{'extra_headers'}) };
+  #my @extra_fields  = @{ delete($sql_query->{'extra_fields'})  };
+
+
+  my( $num, $last, $min_sec ) = (0, time, 5); #progresbar foo
+
+  #eventually order+limit magic to reduce memory use?
+  foreach my $cust_main ( qsearch($sql_query) ) {
+
+    my $to = $cust_main->invoicing_list_emailonly_scalar;
+    next unless $to;
+
+    my $error = send_email(
+      generate_email(
+        'from'      => $from,
+        'to'        => $to,
+        'subject'   => $subject,
+        'html_body' => $html_body,
+        'text_body' => $text_body,
+      )
+    );
+    return $error if $error;
+
+    if ( $job ) { #progressbar foo
+      $num++;
+      if ( time - $min_sec > $last ) {
+        my $error = $job->update_statustext(
+          int( 100 * $num / $num_cust )
+        );
+        die $error if $error;
+        $last = time;
+      }
+    }
+
+  }
+
+  return '';
+}
+
+use Storable qw(thaw);
+use Data::Dumper;
+use MIME::Base64;
+sub process_email_search_sql {
+  my $job = shift;
+  #warn "$me process_re_X $method for job $job\n" if $DEBUG;
+
+  my $param = thaw(decode_base64(shift));
+  warn Dumper($param) if $DEBUG;
+
+  $param->{'job'} = $job;
+
+  my $error = FS::cust_main->email_search_sql( $param );
+  die $error if $error;
+
+}
+
 =item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ]
 
 Performs a fuzzy (approximate) search and returns the matching FS::cust_main
@@ -5009,7 +5714,14 @@ sub smart_search {
 
     }
 
-  } elsif ( $search =~ /^\s*(\d+)\s*$/ ) { # customer # search
+  # custnum search (also try agent_custid), with some tweaking options if your
+  # legacy cust "numbers" have letters
+  } elsif ( $search =~ /^\s*(\d+)\s*$/
+            || ( $conf->config('cust_main-agent_custid-format') eq 'ww?d+'
+                 && $search =~ /^\s*(\w\w?\d+)\s*$/
+               )
+          )
+  {
 
     push @cust_main, qsearch( {
       'table'     => 'cust_main',
@@ -5017,6 +5729,12 @@ sub smart_search {
       'extra_sql' => " AND $agentnums_sql", #agent virtualization
     } );
 
+    push @cust_main, qsearch( {
+      'table'     => 'cust_main',
+      'hashref'   => { 'agent_custid' => $1, %options },
+      'extra_sql' => " AND $agentnums_sql", #agent virtualization
+    } );
+
   } elsif ( $search =~ /^\s*(\S.*\S)\s+\((.+), ([^,]+)\)\s*$/ ) {
 
     my($company, $last, $first) = ( $1, $2, $3 );
@@ -5109,7 +5827,7 @@ sub smart_search {
     #getting complaints searches are not returning enough
     unless ( @cust_main  && $skip_fuzzy || $conf->exists('disable-fuzzy') ) {
 
-      #still some false laziness w/ search/cust_main.cgi
+      #still some false laziness w/search_sql (was search/cust_main.cgi)
 
       #substring
 
@@ -5180,6 +5898,72 @@ sub smart_search {
 
 }
 
+=item email_search
+
+Accepts the following options: I<email>, the email address to search for.  The
+email address will be searched for as an email invoice destination and as an
+svc_acct account.
+
+#Any additional options are treated as an additional qualifier on the search
+#(i.e. I<agentnum>).
+
+Returns a (possibly empty) array of FS::cust_main objects (but usually just
+none or one).
+
+=cut
+
+sub email_search {
+  my %options = @_;
+
+  local($DEBUG) = 1;
+
+  my $email = delete $options{'email'};
+
+  #we're only being used by RT at the moment... no agent virtualization yet
+  #my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql;
+
+  my @cust_main = ();
+
+  if ( $email =~ /([^@]+)\@([^@]+)/ ) {
+
+    my ( $user, $domain ) = ( $1, $2 );
+
+    warn "$me smart_search: searching for $user in domain $domain"
+      if $DEBUG;
+
+    push @cust_main,
+      map $_->cust_main,
+          qsearch( {
+                     'table'     => 'cust_main_invoice',
+                     'hashref'   => { 'dest' => $email },
+                   }
+                 );
+
+    push @cust_main,
+      map  $_->cust_main,
+      grep $_,
+      map  $_->cust_svc->cust_pkg,
+          qsearch( {
+                     'table'     => 'svc_acct',
+                     'hashref'   => { 'username' => $user, },
+                     'extra_sql' =>
+                       'AND ( SELECT domain FROM svc_domain
+                                WHERE svc_acct.domsvc = svc_domain.svcnum
+                            ) = '. dbh->quote($domain),
+                   }
+                 );
+  }
+
+  my %saw = ();
+  @cust_main = grep { !$saw{$_->custnum}++ } @cust_main;
+
+  warn "$me smart_search: found ". scalar(@cust_main). " unique customers"
+    if $DEBUG;
+
+  @cust_main;
+
+}
+
 =item check_and_rebuild_fuzzyfiles
 
 =cut
@@ -5314,6 +6098,18 @@ sub batch_import {
                   svc_acct.username svc_acct._password 
                 );
     $payby = 'BILL';
+ } elsif ( $format eq 'extended-plus_company' ) {
+    @fields = qw( agent_custid refnum
+                  last first company address1 address2 city state zip country
+                  daytime night
+                  ship_last ship_first ship_company ship_address1 ship_address2
+                  ship_city ship_state ship_zip ship_country
+                  payinfo paycvv paydate
+                  invoicing_list
+                  cust_pkg.pkgpart
+                  svc_acct.username svc_acct._password 
+                );
+    $payby = 'BILL';
   } else {
     die "unknown format $format";
   }
@@ -5612,7 +6408,11 @@ sub notify {
   $notify_template->compile()
     or die "can't compile template: Text::Template::ERROR";
 
-  my $paydate = $customer->paydate;
+  $FS::notify_template::_template::company_name = $conf->config('company_name');
+  $FS::notify_template::_template::company_address =
+    join("\n", $conf->config('company_address') ). "\n";
+
+  my $paydate = $customer->paydate || '2037-12-31';
   $FS::notify_template::_template::first = $customer->first;
   $FS::notify_template::_template::last = $customer->last;
   $FS::notify_template::_template::company = $customer->company;
@@ -5665,7 +6465,7 @@ I<$payby> - a description of the method of payment for the customer
             # would be nice to use FS::payby::shortname
 I<$payinfo> - the masked account information used to collect for this customer
 I<$expdate> - the expiration of the customer payment method in seconds from epoch
-I<$returnaddress> - the return address defaults to invoice_latexreturnaddress
+I<$returnaddress> - the return address defaults to invoice_latexreturnaddress or company_address
 
 =cut
 
@@ -5687,8 +6487,8 @@ sub generate_letter {
   my %letter_data = map { $_ => $self->$_ } $self->fields;
   $letter_data{payinfo} = $self->mask_payinfo;
 
-  #my $paydate = $self->paydate || '2037-12';
-  my $paydate = $self->paydate =~ /^\S+$/ ? $self->paydate : '2037-12';
+  #my $paydate = $self->paydate || '2037-12-31';
+  my $paydate = $self->paydate =~ /^\S+$/ ? $self->paydate : '2037-12-31';
 
   my $payby = $self->payby;
   my ($payyear,$paymonth,$payday) = split (/-/,$paydate);
@@ -5715,12 +6515,22 @@ sub generate_letter {
     my $retadd = join("\n", $conf->config_orbase( 'invoice_latexreturnaddress',
                                                   $self->agent_template)
                      );
-
-    $letter_data{returnaddress} = length($retadd) ? $retadd : '~';
+    if ( length($retadd) ) {
+      $letter_data{returnaddress} = $retadd;
+    } elsif ( grep /\S/, $conf->config('company_address') ) {
+      $letter_data{returnaddress} =
+        join( '\\*'."\n", map s/( {2,})/'~' x length($1)/eg,
+                          $conf->config('company_address')
+        );
+    } else {
+      $letter_data{returnaddress} = '~';
+    }
   }
 
   $letter_data{conf_dir} = "$FS::UID::conf_dir/conf.$FS::UID::datasrc";
 
+  $letter_data{company_name} = $conf->config('company_name');
+
   my $dir = $FS::UID::conf_dir."cache.". $FS::UID::datasrc;
   my $fh = new File::Temp( TEMPLATE => 'letter.'. $self->custnum. '.XXXXXXXX',
                            DIR      => $dir,
@@ -5784,9 +6594,20 @@ sub _agent_plandata {
 
   #yuck.  this whole thing needs to be reconciled better with 1.9's idea of
   #agent-specific Conf
+
+  use FS::part_event::Condition;
   
   my $agentnum = $self->agentnum;
 
+  my $regexp = '';
+  if ( driver_name =~ /^Pg/i ) {
+    $regexp = '~';
+  } elsif ( driver_name =~ /^mysql/i ) {
+    $regexp = 'REGEXP';
+  } else {
+    die "don't know how to use regular expressions in ". driver_name. " databases";
+  }
+
   my $part_event_option =
     qsearchs({
       'select'    => 'part_event_option.*',
@@ -5796,7 +6617,7 @@ sub _agent_plandata {
         LEFT JOIN part_event_option AS peo_agentnum
           ON ( part_event.eventpart = peo_agentnum.eventpart
                AND peo_agentnum.optionname = 'agentnum'
-               AND peo_agentnum.optionvalue ~ '(^|,)}. $agentnum. q{(,|$)'
+               AND peo_agentnum.optionvalue }. $regexp. q{ '(^|,)}. $agentnum. q{(,|$)'
              )
         LEFT JOIN part_event_option AS peo_cust_bill_age
           ON ( part_event.eventpart = peo_cust_bill_age.eventpart
@@ -5814,13 +6635,8 @@ sub _agent_plandata {
         " ORDER BY
            CASE WHEN peo_cust_bill_age.optionname != 'cust_bill_age'
            THEN -1
-           ELSE EXTRACT( EPOCH FROM
-                           REPLACE( peo_cust_bill_age.optionvalue,
-                                    'm',
-                                    'mon'
-                                  )::interval
-                       )
-          END
+	   ELSE ". FS::part_event::Condition->age2seconds_sql('peo_cust_bill_age.optionvalue').
+        " END
           , part_event.weight".
         " LIMIT 1"
     });