summaryrefslogtreecommitdiff
path: root/FS
diff options
context:
space:
mode:
Diffstat (limited to 'FS')
-rw-r--r--FS/FS/cust_credit.pm66
-rw-r--r--FS/FS/cust_main.pm145
-rw-r--r--FS/FS/cust_pay.pm30
-rw-r--r--FS/FS/cust_refund.pm30
4 files changed, 227 insertions, 44 deletions
diff --git a/FS/FS/cust_credit.pm b/FS/FS/cust_credit.pm
index d16356e6e..9ea27c9ad 100644
--- a/FS/FS/cust_credit.pm
+++ b/FS/FS/cust_credit.pm
@@ -331,15 +331,15 @@ sub cust_credit_bill {
;
}
-=item credited
+=item unapplied
-Returns the amount of this credit that is still outstanding; which is
+Returns the amount of this credit that is still unapplied/outstanding;
amount minus all refund applications (see L<FS::cust_credit_refund>) and
applications to invoices (see L<FS::cust_credit_bill>).
=cut
-sub credited {
+sub unapplied {
my $self = shift;
my $amount = $self->amount;
$amount -= $_->amount foreach ( $self->cust_credit_refund );
@@ -347,6 +347,18 @@ sub credited {
sprintf( "%.2f", $amount );
}
+=item credited
+
+Deprecated name for the unapplied method.
+
+=cut
+
+sub credited {
+ my $self = shift;
+ #carp "cust_credit->credited deprecated; use ->unapplied";
+ $self->unapplied(@_);
+}
+
=item cust_main
Returns the customer (see L<FS::cust_main>) for this credit.
@@ -524,10 +536,58 @@ sub _upgrade_data { # class method
=back
+=head1 CLASS METHODS
+
+=over 4
+
+=item unapplied_sql
+
+Returns an SQL fragment to retreive the unapplied amount.
+
+=cut
+
+sub unapplied_sql {
+ #my $class = shift;
+
+ "amount
+ - COALESCE(
+ ( SELECT SUM(amount) FROM cust_credit_refund
+ WHERE cust_credit.crednum = cust_credit_refund.crednum )
+ ,0
+ )
+ - COALESCE(
+ ( SELECT SUM(amount) FROM cust_credit_bill
+ WHERE cust_credit.crednum = cust_credit_bill.crednum )
+ ,0
+ )
+ ";
+
+}
+
+=item credited_sql
+
+Deprecated name for the unapplied_sql method.
+
+=cut
+
+sub credited_sql {
+ #my $class = shift;
+
+ #carp "cust_credit->credited_sql deprecated; use ->unapplied_sql";
+
+ #$class->unapplied_sql(@_);
+ unapplied_sql();
+}
+
+=back
+
=head1 BUGS
The delete method. The replace method.
+B<credited> and B<credited_sql> are now called B<unapplied> and
+B<unapplied_sql>. The old method names should start to give warnings.
+
=head1 SEE ALSO
L<FS::Record>, L<FS::cust_credit_refund>, L<FS::cust_refund>,
diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 031f47733..7a560cb02 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -3767,17 +3767,38 @@ sub total_unapplied_payments {
sprintf( "%.2f", $total_unapplied );
}
+=item total_unapplied_refunds
+
+Returns the total unrefunded refunds (see L<FS::cust_refund>) for this
+customer. See L<FS::cust_refund/unapplied>.
+
+=cut
+
+sub total_unapplied_refunds {
+ my $self = shift;
+ my $total_unapplied = 0;
+ foreach my $cust_refund ( qsearch('cust_refund', {
+ 'custnum' => $self->custnum,
+ } ) ) {
+ $total_unapplied += $cust_refund->unapplied;
+ }
+ sprintf( "%.2f", $total_unapplied );
+}
+
=item balance
-Returns the balance for this customer (total_owed minus total_credited
-minus total_unapplied_payments).
+Returns the balance for this customer (total_owed plus total_unrefunded, minus
+total_credited minus total_unapplied_payments).
=cut
sub balance {
my $self = shift;
sprintf( "%.2f",
- $self->total_owed - $self->total_credited - $self->total_unapplied_payments
+ $self->total_owed
+ + $self->total_unapplied_refunds
+ - $self->total_credited
+ - $self->total_unapplied_payments
);
}
@@ -3795,7 +3816,8 @@ sub balance_date {
my $self = shift;
my $time = shift;
sprintf( "%.2f",
- $self->total_owed_date($time)
+ $self->total_owed_date($time)
+ + $self->total_unapplied_refunds
- $self->total_credited
- $self->total_unapplied_payments
);
@@ -4514,58 +4536,99 @@ 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 length($start);
+ push @where, "$table._date > $end" if length($end);
+ }
+ push @where, @{$opt{'where'}} if $opt{'where'};
+ my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where ) : '';
+
+ $where;
+
+}
+
=item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ]
Performs a fuzzy (approximate) search and returns the matching FS::cust_main
diff --git a/FS/FS/cust_pay.pm b/FS/FS/cust_pay.pm
index c2cdd3eef..f969460a9 100644
--- a/FS/FS/cust_pay.pm
+++ b/FS/FS/cust_pay.pm
@@ -620,6 +620,36 @@ sub _parse_paybatch {
=back
+=head1 CLASS METHODS
+
+=over 4
+
+=item unapplied_sql
+
+Returns an SQL fragment to retreive the unapplied amount.
+
+=cut
+
+sub unapplied_sql {
+ #my $class = shift;
+
+ "paid
+ - COALESCE(
+ ( SELECT SUM(amount) FROM cust_bill_pay
+ WHERE cust_pay.paynum = cust_bill_pay.paynum )
+ ,0
+ )
+ - COALESCE(
+ ( SELECT SUM(amount) FROM cust_pay_refund
+ WHERE cust_pay.paynum = cust_pay_refund.paynum )
+ ,0
+ )
+ ";
+
+}
+
+=back
+
=head1 BUGS
Delete and replace methods.
diff --git a/FS/FS/cust_refund.pm b/FS/FS/cust_refund.pm
index 9cd9bf845..e142a609b 100644
--- a/FS/FS/cust_refund.pm
+++ b/FS/FS/cust_refund.pm
@@ -307,6 +307,36 @@ sub unapplied {
=back
+=head1 CLASS METHODS
+
+=over 4
+
+=item unapplied_sql
+
+Returns an SQL fragment to retreive the unapplied amount.
+
+=cut
+
+sub unapplied_sql {
+ #my $class = shift;
+
+ "refund
+ - COALESCE(
+ ( SELECT SUM(amount) FROM cust_credit_refund
+ WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
+ ,0
+ )
+ - COALESCE(
+ ( SELECT SUM(amount) FROM cust_pay_refund
+ WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
+ ,0
+ )
+ ";
+
+}
+
+=back
+
=head1 BUGS
Delete and replace methods.
generated by cgit v1.2.1 (git 2.18.0) at 2025-06-27 13:20:23 +0000