Emailing statements of accounts, RT#4860

13 files changed, 479 insertions, 10 deletions

diff --git a/FS/FS.pm b/FS/FS.pm
index 09ef298..7ce9741 100644
--- a/FS/FS.pm
+++ b/FS/FS.pm
@@ -242,6 +242,8 @@ L<FS::banned_pay> - Banned payment information class
 
 L<FS::cust_bill> - Invoice class
 
+L<FS::cust_statement> - Informational statement class
+
 L<FS::cust_bill_pkg> - Invoice line item class
 
 L<FS::cust_bill_pkg_detail> - Invoice line item detail class
diff --git a/FS/FS/Cron/bill.pm b/FS/FS/Cron/bill.pm
index 1feba79..2bdb120 100644
--- a/FS/FS/Cron/bill.pm
+++ b/FS/FS/Cron/bill.pm
@@ -194,13 +194,14 @@ END
     my $where = FS::part_event_condition->where_conditions_sql( $eventtable,
                                                                 'time'=>$time,
                                                               );
+    my $where = "AND $where" if $where;
 
     my $are_part_event = 
       "EXISTS ( SELECT 1 FROM part_event $join
                   WHERE check_freq = '$check_freq'
                     AND eventtable = '$eventtable'
                     AND ( disabled = '' OR disabled IS NULL )
-                    AND $where
+                    $where
               )
       ";
 
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 877cf14..9a7c4ce 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -390,16 +390,28 @@ sub tables_hashref {
 
     'cust_bill' => {
       'columns' => [
-        'invnum',    'serial',  '', '', '', '', 
-        'custnum',   'int',  '', '', '', '', 
-        '_date',     @date_type, '', '', 
-        'charged',   @money_type, '', '', 
-        'printed',   'int',  '', '', '', '', 
-        'closed',    'char', 'NULL', 1, '', '', 
+        'invnum',    'serial',     '', '', '', '', 
+        'custnum',      'int',     '', '', '', '', 
+        '_date',        @date_type,        '', '', 
+        'charged',      @money_type,       '', '', 
+        'printed',      'int',     '', '', '', '', 
+        'closed',      'char', 'NULL',  1, '', '', 
+        'statementnum', 'int', 'NULL', '', '', '',
       ],
       'primary_key' => 'invnum',
       'unique' => [],
-      'index' => [ ['custnum'], ['_date'] ],
+      'index' => [ ['custnum'], ['_date'], ['statementnum'], ],
+    },
+
+    'cust_statement' => {
+      'columns' => [
+        'statementnum', 'serial', '', '', '', '',
+        'custnum',         'int', '', '', '', '',
+        '_date',           @date_type,    '', '',
+      ],
+      'primary_key' => 'statementnum',
+      'unique' => [],
+      'index' => [ ['custnum'], ['_date'], ],
     },
 
     'cust_bill_event' => {
diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index b278e9b..ca5a4e8 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -7251,6 +7251,18 @@ sub open_cust_bill {
 
 }
 
+=item cust_statements
+
+Returns all the statements (see L<FS::cust_statement>) for this customer.
+
+=cut
+
+sub cust_statement {
+  my $self = shift;
+  sort { $a->_date <=> $b->_date }
+    qsearch('cust_statement', { 'custnum' => $self->custnum, } )
+}
+
 =item cust_credit
 
 Returns all the credits (see L<FS::cust_credit>) for this customer.
diff --git a/FS/FS/cust_statement.pm b/FS/FS/cust_statement.pm
new file mode 100644
index 0000000..cd3e7ce
--- /dev/null
+++ b/FS/FS/cust_statement.pm
@@ -0,0 +1,251 @@
+package FS::cust_statement;
+
+use strict;
+use base qw( FS::cust_bill );
+use FS::Record qw( dbh qsearch ); #qsearchs );
+use FS::cust_main;
+use FS::cust_bill;
+
+=head1 NAME
+
+FS::cust_statement - Object methods for cust_statement records
+
+=head1 SYNOPSIS
+
+  use FS::cust_statement;
+
+  $record = new FS::cust_statement \%hash;
+  $record = new FS::cust_statement { 'column' => 'value' };
+
+  $error = $record->insert;
+
+  $error = $new_record->replace($old_record);
+
+  $error = $record->delete;
+
+  $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::cust_statement object represents an informational statement which
+aggregates one or more invoices.  FS::cust_statement inherits from
+FS::cust_bill.
+
+The following fields are currently supported:
+
+=over 4
+
+=item statementnum
+
+primary key
+
+=item custnum
+
+customer
+
+=item _date
+
+date
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item new HASHREF
+
+Creates a new record.  To add the record to the database, see L<"insert">.
+
+Note that this stores the hash reference, not a distinct copy of the hash it
+points to.  You can ask the object for a copy with the I<hash> method.
+
+=cut
+
+sub new { FS::Record::new(@_); }
+
+sub table { 'cust_statement'; }
+
+=item insert
+
+Adds this record to the database.  If there is an error, returns the error,
+otherwise returns false.
+
+=cut
+
+sub insert {
+  my $self = shift;
+
+  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;
+
+  FS::Record::insert($self);
+
+  foreach my $cust_bill (
+                          qsearch({
+                            'table'     => 'cust_bill',
+                            'hashref'   => { 'custnum'      => $self->custnum,
+                                             'statementnum' => '',
+                                           },
+                            'extra_sql' => 'FOR UPDATE' ,
+                          })
+                        )
+  {
+    $cust_bill->statementnum( $self->statementnum );
+    my $error = $cust_bill->replace;
+    if ( $error ) {
+      $dbh->rollback if $oldAutoCommit;
+      return "Error associating invoice: $error";
+    }
+  }
+
+  $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+  ''; #no error
+
+}
+
+=item delete
+
+Delete this record from the database.
+
+=cut
+
+sub delete { FS::Record::delete(@_); }
+
+=item replace OLD_RECORD
+
+Replaces the OLD_RECORD with this one in the database.  If there is an error,
+returns the error, otherwise returns false.
+
+=cut
+
+sub replace { FS::Record::replace(@_); }
+
+sub replace_check { ''; }
+
+=item check
+
+Checks all fields to make sure this is a valid record.  If there is
+an error, returns the error, otherwise returns false.  Called by the insert
+and replace methods.
+
+=cut
+
+sub check {
+  my $self = shift;
+
+  my $error = 
+    $self->ut_numbern('statementnum')
+    || $self->ut_foreign_key('custnum', 'cust_main', 'custnum' )
+    || $self->ut_numbern('_date')
+  ;
+  return $error if $error;
+
+  $self->_date(time) unless $self->_date;
+
+  #don't want to call cust_bill, and Record just checks virtual fields
+  #$self->SUPER::check;
+  '';
+
+}
+
+=item cust_bill
+
+Returns the associated invoices (cust_bill records) for this statement.
+
+=cut
+
+sub cust_bill {
+  my $self = shift;
+  qsearch('cust_bill', { 'statementnum' => $self->statementnum } );
+}
+
+sub _aggregate {
+  my( $self, $method ) = ( shift, shift );
+
+  my @agg = ();
+
+  foreach my $cust_bill ( $self->cust_bill ) {
+    push @agg, $cust_bill->$method( @_ );
+  }
+
+  @agg;
+}
+
+=item cust_bill_pkg
+
+Returns the line items (see L<FS::cust_bill_pkg>) for all associated invoices.
+
+=item cust_bill_pkg_pkgnum PKGNUM
+
+Returns the line items (see L<FS::cust_bill_pkg>) for all associated invoices
+and specified pkgnum.
+
+=item cust_bill_pay
+
+Returns all payment applications (see L<FS::cust_bill_pay>) for all associated
+invoices.
+
+=item cust_credited
+
+Returns all applied credits (see L<FS::cust_credit_bill>) for all associated
+invoices.
+
+=item cust_bill_pay_pkgnum PKGNUM
+
+Returns all payment applications (see L<FS::cust_bill_pay>) for all associated
+invoices with matching pkgnum.
+
+=item cust_credited_pkgnum PKGNUM
+
+Returns all applied credits (see L<FS::cust_credit_bill>) for all associated
+invoices with matching pkgnum.
+
+=cut
+
+sub cust_bill_pay        { shift->_aggregate('cust_bill_pay',        @_); }
+sub cust_credited        { shift->_aggregate('cust_credited',        @_); }
+sub cust_bill_pay_pkgnum { shift->_aggregate('cust_bill_pay_pkgnum', @_); }
+sub cust_credited_pkgnum { shift->_aggregate('cust_credited_pkgnum', @_); }
+
+sub cust_bill_pkg        { shift->_aggregate('cust_bill_pkg',        @_); }
+sub cust_bill_pkg_pkgnum { shift->_aggregate('cust_bill_pkg_pkgnum', @_); }
+
+=item tax
+
+Returns the tax amount (see L<FS::cust_bill_pkg>) for this invoice.
+
+=cut
+
+sub tax {
+  my $self = shift;
+
+  my $total = 0;
+
+  foreach my $cust_bill ( $self->cust_bill ) {
+    $total += $cust_bill->tax;
+  }
+
+  $total;
+}
+
+=back
+
+=head1 BUGS
+
+=head1 SEE ALSO
+
+L<FS::cust_bill>, L<FS::Record>, schema.html from the base documentation.
+
+=cut
+
+1;
+
diff --git a/FS/FS/part_event.pm b/FS/FS/part_event.pm
index 6f2c536..c98c3f8 100644
--- a/FS/FS/part_event.pm
+++ b/FS/FS/part_event.pm
@@ -52,7 +52,7 @@ following fields are currently supported:
 
 =item event - event name
 
-=item eventtable - table name against which this event is triggered; currently "cust_bill" (the traditional invoice events), "cust_main" (customer events) or "cust_pkg (package events)
+=item eventtable - table name against which this event is triggered; currently "cust_bill" (the traditional invoice events), "cust_main" (customer events) or "cust_pkg (package events) (or "cust_statement")
 
 =item check_freq - how often events of this type are checked; currently "1d" (daily) and "1m" (monthly) are recognized.  Note that the apprioriate freeside-daily and/or freeside-monthly cron job needs to be in place.
 
@@ -133,7 +133,7 @@ sub check {
   my $error = 
        $self->ut_numbern('eventpart')
     || $self->ut_text('event')
-    || $self->ut_enum('eventtable', [ 'cust_bill', 'cust_main', 'cust_pkg' ] )
+    || $self->ut_enum('eventtable', [ $self->eventtables ] )
     || $self->ut_enum('check_freq', [ '1d', '1m' ])
     || $self->ut_number('weight')
     || $self->ut_alpha('action')
@@ -273,6 +273,7 @@ sub eventtable_labels {
     'cust_bill'      => 'Invoice',
     'cust_main'      => 'Customer',
     'cust_pay_batch' => 'Batch payment',
+    'cust_statement' => 'Statement',  #too general a name here? "Invoice group"?
   ;
 
   \%hash
@@ -310,6 +311,7 @@ sub eventtable_pkey {
     'cust_bill'      => 'invnum',
     'cust_pkg'       => 'pkgnum',
     'cust_pay_batch' => 'paybatchnum',
+    'cust_statement' => 'statementnum',
   };
 }
 
diff --git a/FS/FS/part_event/Action/cust_statement.pm b/FS/FS/part_event/Action/cust_statement.pm
new file mode 100644
index 0000000..8d8f127
--- /dev/null
+++ b/FS/FS/part_event/Action/cust_statement.pm
@@ -0,0 +1,38 @@
+package FS::part_event::Action::cust_statement;
+
+use strict;
+
+use base qw( FS::part_event::Action );
+
+use FS::cust_statement;
+
+sub description {
+  'Group invoices into an informational statement.';
+}
+
+sub eventtable_hashref {
+    { 'cust_main' => 1, };
+    { 'cust_pkg'  => 1, };
+}
+
+sub default_weight {
+  90;
+}
+
+sub do_action {
+  my( $self, $cust_main ) = @_;
+
+  #my( $self, $object ) = @_;
+  #my $cust_main = $self->cust_main($object);
+
+  my $cust_statement = new FS::cust_statement {
+    'custnum' => $cust_main->custnum
+  };
+  my $error = $cust_statement->insert;
+  die $error if $error;
+
+  '';
+
+}
+
+1;
diff --git a/FS/FS/part_event/Action/cust_statement_send.pm b/FS/FS/part_event/Action/cust_statement_send.pm
new file mode 100644
index 0000000..34f023e
--- /dev/null
+++ b/FS/FS/part_event/Action/cust_statement_send.pm
@@ -0,0 +1,26 @@
+package FS::part_event::Action::cust_statement_send;
+
+use strict;
+
+use base qw( FS::part_event::Action );
+
+sub description {
+  'Send statement (email/print/fax)';
+}
+
+sub eventtable_hashref {
+  { 'cust_statement' => 1, };
+}
+
+sub default_weight {
+  95;
+}
+
+sub do_action {
+  my( $self, $cust_statement ) = @_;
+
+  $cust_statement->send;
+
+}
+
+1;
diff --git a/FS/MANIFEST b/FS/MANIFEST
index 8082610..a454897 100644
--- a/FS/MANIFEST
+++ b/FS/MANIFEST
@@ -449,3 +449,5 @@ FS/part_device.pm
 t/part_device.t
 FS/cdr_termination.pm
 t/cdr_termination.t
+FS/cust_statement.pm
+t/cust_statement.t
diff --git a/FS/t/cust_statement.t b/FS/t/cust_statement.t
new file mode 100644
index 0000000..c57d94c
--- /dev/null
+++ b/FS/t/cust_statement.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::cust_statement;
+$loaded=1;
+print "ok 1\n";
diff --git a/httemplate/view/cust_main/payment_history.html b/httemplate/view/cust_main/payment_history.html
index 24af5c9..0050daf 100644
--- a/httemplate/view/cust_main/payment_history.html
+++ b/httemplate/view/cust_main/payment_history.html
@@ -392,6 +392,15 @@ foreach my $cust_bill ($cust_main->cust_bill) {
   };
 }
 
+#statements
+foreach my $cust_statement ($cust_main->cust_statement) {
+  push @history, {
+    'date'   => $cust_statement->_date,
+    'desc'   => include('payment_history/statement.html', $cust_statement, %opt ),
+    #'charge' => $cust_bill->charged,
+  };
+}
+
 #payments (some false laziness w/credits)
 foreach my $cust_pay ($cust_main->cust_pay) {
   push @history, {
diff --git a/httemplate/view/cust_main/payment_history/statement.html b/httemplate/view/cust_main/payment_history/statement.html
new file mode 100644
index 0000000..dedec9b
--- /dev/null
+++ b/httemplate/view/cust_main/payment_history/statement.html
@@ -0,0 +1,34 @@
+<% $link %><% $pre %>Statement #<% $statementnum %>
+%# (Balance $ <% $cust_statement->owed %>)
+<% $post %><% $link ? '</A>' : '' %><% $events %>
+<%init>
+
+my( $cust_statement, %opt ) = @_;
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+my($pre, $post) = ('', '');
+#if ( $cust_statement->owed > 0 ) {
+#  $pre = '<B><FONT SIZE="+1" COLOR="#FF0000">Open ';
+#  $post = '</FONT></B>';
+#}
+
+my $statementnum = $cust_statement->statementnum;
+
+my $link = $curuser->access_right('View invoices')
+             ? qq!<A HREF="${p}view/cust_statement.html?$statementnum">!
+             : '';
+
+my $events = '';
+
+#if ( $cust_statement->num_cust_event
+#     && (    $curuser->access_right('Billing event reports')
+#          || $curuser->access_right('View customer billing events')
+#        )
+#   ) {
+#  $events =
+#    qq!<BR><FONT SIZE="-1"><A HREF="${p}search/cust_event.html?statementnum=!.
+#    $cust_statement->statementnum. '">(&nbsp;View statement events&nbsp;)</A></FONT>';
+#}
+
+</%init>
diff --git a/httemplate/view/cust_statement.html b/httemplate/view/cust_statement.html
new file mode 100755
index 0000000..ec4ee9e
--- /dev/null
+++ b/httemplate/view/cust_statement.html
@@ -0,0 +1,75 @@
+<% include("/elements/header.html",'Statement View', menubar(
+  "View this customer (#$display_custnum)" => "${p}view/cust_main.cgi?$custnum",
+)) %>
+
+% if ( $FS::CurrentUser::CurrentUser->access_right('Resend invoices') ) {
+
+    <A HREF="<% $p %>misc/print-invoice.cgi?<% $link %>">Re-print this statement</A>
+
+%   if ( grep { $_ ne 'POST' } $cust_statement->cust_main->invoicing_list ) { 
+        | <A HREF="<% $p %>misc/email-invoice.cgi?<% $link %>">Re-email this statement</A>
+%   } 
+
+%   if ( $conf->exists('hylafax') && length($cust_statement->cust_main->fax) ) { 
+        | <A HREF="<% $p %>misc/fax-invoice.cgi?<% $link %>">Re-fax this statement</A>
+%   } 
+
+    <BR><BR>
+
+% } 
+
+
+% if ( $conf->exists('invoice_latex') ) { 
+
+  <A HREF="<% $p %>view/cust_statement-pdf.cgi?<% $link %>.pdf">View typeset statement</A>
+  <BR><BR>
+% } 
+
+% #if ( $cust_statement->num_cust_event ) {
+% if ( 0 ) {
+<A HREF="<%$p%>search/cust_event.html?statementnum=<% $cust_statement->statementnum %>">(&nbsp;View statement events&nbsp;)</A><BR><BR>
+% } 
+
+% if ( $conf->exists('invoice_html') ) { 
+
+  <% join('', $cust_statement->print_html('', $templatename) ) %>
+% } else { 
+
+  <PRE><% join('', $cust_statement->print_text('', $templatename) ) %></PRE>
+% } 
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('View invoices');
+
+#untaint statement
+my($query) = $cgi->keywords;
+$query =~ /^((.+)-)?(\d+)$/;
+my $templatename = $2;
+my $statementnum = $3;
+
+my $conf = new FS::Conf;
+
+my @payby =  grep /\w/, $conf->config('payby');
+#@payby = (qw( CARD DCRD CHEK DCHK LECB BILL CASH WEST COMP ))
+@payby = (qw( CARD DCRD CHEK DCHK LECB BILL CASH COMP ))
+  unless @payby;
+my %payby = map { $_=>1 } @payby;
+
+my $cust_statement = qsearchs({
+  'select'    => 'cust_statement.*',
+  'table'     => 'cust_statement',
+  'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
+  'hashref'   => { 'statementnum' => $statementnum },
+  'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql,
+});
+die "Statement #$statementnum not found!" unless $cust_statement;
+
+my $custnum = $cust_statement->custnum;
+my $display_custnum = $cust_statement->cust_main->display_custnum;
+
+my $link = $templatename ? "$templatename-$statementnum" : $statementnum;
+
+</%init>