summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--FS/FS/cust_bill.pm20
-rw-r--r--FS/FS/cust_credit.pm13
-rw-r--r--FS/FS/cust_main.pm16
-rw-r--r--FS/FS/cust_pay.pm12
-rw-r--r--FS/FS/cust_refund.pm12
-rwxr-xr-xhttemplate/search/report_receivables.cgi5
6 files changed, 57 insertions, 21 deletions
diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm
index 8cc84e5f0..f10a5d0c4 100644
--- a/FS/FS/cust_bill.pm
+++ b/FS/FS/cust_bill.pm
@@ -4342,7 +4342,10 @@ Returns an SQL fragment to retreive the amount owed (charged minus credited and
sub owed_sql {
my $class = shift;
- 'charged - '. $class->paid_sql. ' - '. $class->credited_sql;
+ my ($start, $end) = @_;
+ 'charged - '.
+ $class->paid_sql($start, $end). ' - '.
+ $class->credited_sql($start, $end);
}
=item net_sql
@@ -4353,7 +4356,8 @@ Returns an SQL fragment to retreive the net amount (charged minus credited).
sub net_sql {
my $class = shift;
- 'charged - '. $class->credited_sql;
+ my ($start, $end) = @_;
+ 'charged - '. $class->credited_sql($start, $end);
}
=item paid_sql
@@ -4363,9 +4367,11 @@ Returns an SQL fragment to retreive the amount paid against this invoice.
=cut
sub paid_sql {
- #my $class = shift;
+ my ($class, $start, $end) = @_;
+ $start &&= "AND cust_bill_pay._date <= $start";
+ $end &&= "AND cust_bill_pay._date > $end";
"( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
- WHERE cust_bill.invnum = cust_bill_pay.invnum )";
+ WHERE cust_bill.invnum = cust_bill_pay.invnum $start $end )";
}
=item credited_sql
@@ -4375,9 +4381,11 @@ Returns an SQL fragment to retreive the amount credited against this invoice.
=cut
sub credited_sql {
- #my $class = shift;
+ my ($class, $start, $end) = shift;
+ $start &&= "AND cust_credit_bill._date <= $start";
+ $end &&= "AND cust_credit_bill._date > $end";
"( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
- WHERE cust_bill.invnum = cust_credit_bill.invnum )";
+ WHERE cust_bill.invnum = cust_credit_bill.invnum $start $end )";
}
=item search_sql_where HASHREF
diff --git a/FS/FS/cust_credit.pm b/FS/FS/cust_credit.pm
index b87b0dbc3..674bc1047 100644
--- a/FS/FS/cust_credit.pm
+++ b/FS/FS/cust_credit.pm
@@ -565,17 +565,24 @@ Returns an SQL fragment to retreive the unapplied amount.
=cut
sub unapplied_sql {
- #my $class = shift;
+ my ($class, $start, $end) = @_;
+
+ my $bill_start = $start ? "AND cust_credit_bill._date <= $start" : '';
+ my $bill_end = $end ? "AND cust_credit_bill._date > $end" : '';
+ my $refund_start = $start ? "AND cust_credit_refund._date <= $start" : '';
+ my $refund_end = $end ? "AND cust_credit_refund._date > $end" : '';
"amount
- COALESCE(
( SELECT SUM(amount) FROM cust_credit_refund
- WHERE cust_credit.crednum = cust_credit_refund.crednum )
+ WHERE cust_credit.crednum = cust_credit_refund.crednum
+ $refund_start $refund_end )
,0
)
- COALESCE(
( SELECT SUM(amount) FROM cust_credit_bill
- WHERE cust_credit.crednum = cust_credit_bill.crednum )
+ WHERE cust_credit.crednum = cust_credit_bill.crednum
+ $bill_start $bill_end )
,0
)
";
diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 684031c1d..c1a8aafde 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -7392,6 +7392,12 @@ WHERE clause hashref (elements "AND"ed together) (typically used with the total
(unused. obsolete?)
JOIN clause (typically used with the total option)
+=item cutoff
+
+An absolute cutoff time. Payments, credits, and refunds I<applied> after this
+time will be ignored. Note that START_TIME and END_TIME only limit the date
+range for invoices and I<unapplied> payments, credits, and refunds.
+
=back
=cut
@@ -7399,10 +7405,12 @@ JOIN clause (typically used with the total option)
sub balance_date_sql {
my( $class, $start, $end, %opt ) = @_;
- 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;
+ my $cutoff = $opt{'cutoff'};
+
+ my $owed = FS::cust_bill->owed_sql($cutoff);
+ my $unapp_refund = FS::cust_refund->unapplied_sql($cutoff);
+ my $unapp_credit = FS::cust_credit->unapplied_sql($cutoff);
+ my $unapp_pay = FS::cust_pay->unapplied_sql($cutoff);
my $j = $opt{'join'} || '';
diff --git a/FS/FS/cust_pay.pm b/FS/FS/cust_pay.pm
index 175dbe890..ff8226c8d 100644
--- a/FS/FS/cust_pay.pm
+++ b/FS/FS/cust_pay.pm
@@ -659,17 +659,23 @@ Returns an SQL fragment to retreive the unapplied amount.
=cut
sub unapplied_sql {
- #my $class = shift;
+ my ($class, $start, $end) = shift;
+ my $bill_start = $start ? "AND cust_bill_pay._date <= $start" : '';
+ my $bill_end = $end ? "AND cust_bill_pay._date > $end" : '';
+ my $refund_start = $start ? "AND cust_pay_refund._date <= $start" : '';
+ my $refund_end = $end ? "AND cust_pay_refund._date > $end" : '';
"paid
- COALESCE(
( SELECT SUM(amount) FROM cust_bill_pay
- WHERE cust_pay.paynum = cust_bill_pay.paynum )
+ WHERE cust_pay.paynum = cust_bill_pay.paynum
+ $bill_start $bill_end )
,0
)
- COALESCE(
( SELECT SUM(amount) FROM cust_pay_refund
- WHERE cust_pay.paynum = cust_pay_refund.paynum )
+ WHERE cust_pay.paynum = cust_pay_refund.paynum
+ $refund_start $refund_end )
,0
)
";
diff --git a/FS/FS/cust_refund.pm b/FS/FS/cust_refund.pm
index e7c5a8215..6d08c8621 100644
--- a/FS/FS/cust_refund.pm
+++ b/FS/FS/cust_refund.pm
@@ -343,17 +343,23 @@ Returns an SQL fragment to retreive the unapplied amount.
=cut
sub unapplied_sql {
- #my $class = shift;
+ my ($class, $start, $end) = shift;
+ my $credit_start = $start ? "AND cust_credit_refund._date <= $start" : '';
+ my $credit_end = $end ? "AND cust_credit_refund._date > $end" : '';
+ my $pay_start = $start ? "AND cust_pay_refund._date <= $start" : '';
+ my $pay_end = $end ? "AND cust_pay_refund._date > $end" : '';
"refund
- COALESCE(
( SELECT SUM(amount) FROM cust_credit_refund
- WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
+ WHERE cust_refund.refundnum = cust_credit_refund.refundnum
+ $credit_start $credit_end )
,0
)
- COALESCE(
( SELECT SUM(amount) FROM cust_pay_refund
- WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
+ WHERE cust_refund.refundnum = cust_pay_refund.refundnum
+ $pay_start $pay_end )
,0
)
";
diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi
index 73ebee49d..3696ed40d 100755
--- a/httemplate/search/report_receivables.cgi
+++ b/httemplate/search/report_receivables.cgi
@@ -16,7 +16,7 @@ die "access denied"
#Example:
#
# my $balance = balance(
-# $start, $end,
+# $start, $end, $offset,
# 'no_as' => 1, #set to true when using in a WHERE clause (supress AS clause)
# #or 0 / omit when using in a SELECT clause as a column
# # ("AS balance_$start_$end")
@@ -44,7 +44,8 @@ sub balance {
#$opt{'unapplied_date'} = 1;
- FS::cust_main->balance_date_sql( $start, $end, 'unapplied_date'=>1,);
+ FS::cust_main->balance_date_sql( $start, $end, 'unapplied_date'=>1,
+ 'cutoff' => "( $str2time now() $closing - ".$offset * 86400 . ')' );
}
generated by cgit v1.2.1 (git 2.18.0) at 2024-10-05 00:36:27 +0000