From 34f91089a0d54fa39d622ec5e03d9e09a50e9f79 Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 8 Jan 2008 11:23:03 +0000 Subject: [PATCH] show negative balances on A/R report, closes: RT#2983 --- FS/FS/cust_credit.pm | 41 +++++++++-- FS/FS/cust_main.pm | 115 +++++++++++++++++++++---------- httemplate/search/report_receivables.cgi | 107 ++++++++++++---------------- 3 files changed, 156 insertions(+), 107 deletions(-) diff --git a/FS/FS/cust_credit.pm b/FS/FS/cust_credit.pm index 938686fea..70d78749c 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) and applications to invoices (see L). =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) for this credit. @@ -527,13 +539,13 @@ sub _upgrade_data { # class method =over 4 -=item credited_sql +=item unapplied_sql Returns an SQL fragment to retreive the unapplied amount. =cut -sub credited_sql { +sub unapplied_sql { #my $class = shift; "amount @@ -551,14 +563,29 @@ sub credited_sql { } +=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 and B should probably be called B and -B. +B and B are now called B and +B. The old method names should start to give warnings. =head1 SEE ALSO diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 952aa73c2..de4d38de9 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -5108,58 +5108,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). Also see L and -L 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). Also see L and +L 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/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi index 872cb7dc6..06aea193a 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -19,28 +19,23 @@ ) ), sprintf( $money_char.'%.2f', - $row->{'owed_0_30'} ), + $row->{'balance_0_30'} ), sprintf( $money_char.'%.2f', - $row->{'owed_30_60'} ), + $row->{'balance_30_60'} ), sprintf( $money_char.'%.2f', - $row->{'owed_60_90'} ), + $row->{'balance_60_90'} ), sprintf( $money_char.'%.2f', - $row->{'owed_90_0'} ), + $row->{'balance_90_0'} ), sprintf( ''. $money_char.'%.2f'. '', - $row->{'owed_0_0'} ), + $row->{'balance_0_0'} ), ], 'fields' => [ \&FS::UI::Web::cust_fields, - sub { sprintf( $money_char.'%.2f', - shift->get('owed_0_30') ) }, - sub { sprintf( $money_char.'%.2f', - shift->get('owed_30_60') ) }, - sub { sprintf( $money_char.'%.2f', - shift->get('owed_60_90') ) }, - sub { sprintf( $money_char.'%.2f', - shift->get('owed_90_0') ) }, - sub { sprintf( $money_char.'%.2f', - shift->get('owed_0_0') ) }, + format_balance('0_30'), + format_balance('30_60'), + format_balance('60_90'), + format_balance('90_0'), + format_balance('0_0'), ], 'links' => [ ( map { $_ ne 'Cust. Status' ? $clink : '' } @@ -86,7 +81,7 @@ my @ranges = ( [ 0, 0 ], ); -my $owed_cols = join(',', map owed( @$_, 'cust'=>1 ), @ranges ); +my $owed_cols = join(',', map balance( @$_ ), @ranges ); my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql; @@ -112,7 +107,7 @@ unless ( $cgi->param('all_customers') ) { $days = $1; } - push @where, owed($days, 0, 'cust'=>1, 'noas'=>1). " > 0"; + push @where, balance($days, 0, 'no_as'=>1). " > 0"; } @@ -133,74 +128,60 @@ my $sql_query = { 'table' => 'cust_main', 'hashref' => {}, 'select' => "*, $owed_cols, $packages_cols", - 'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)", + 'extra_sql' => $where, + 'order_by' => "order by coalesce(lower(company), ''), lower(last)", }; my $join = 'LEFT JOIN cust_main USING ( custnum )'; -my $total_sql = "select ". - join(',', map owed( @$_, join=>$join, where=>\@where ), @ranges); +my $total_sql = "SELECT ". + join(',', map balance( @$_, total=>1, join=>$join, where=>\@where ), @ranges); my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; $total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr; my $row = $total_sth->fetchrow_hashref(); -my $conf = new FS::Conf; -my $money_char = $conf->config('money_char') || '$'; - my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; <%once> -sub owed { - my($start, $end, %opt) = @_; - - my @where = (); - - #handle start and end ranges - - my $str2time = str2time_sql; - - #24h * 60m * 60s - push @where, "cust_bill._date <= $str2time now() ) - ". ($start * 86400) - if $start; - - push @where, "cust_bill._date > $str2time now() ) - ". ($end * 86400) - if $end; - - #handle 'cust' option - push @where, "cust_main.custnum = cust_bill.custnum" - if $opt{'cust'}; +my $conf = new FS::Conf; - #handle 'join' option - my $join = $opt{'join'} || ''; +my $money_char = $conf->config('money_char') || '$'; - #handle 'where' option - push @where, @{ $opt{'where'} } if $opt{'where'}; +#Example: +# +# my $balance = balance( +# $start, $end, +# '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") +# #options for totals +# 'total' => 1, #set to true to remove all customer comparison clauses +# 'join' => $join, #JOIN clause +# 'where' => \@where, #WHERE clause hashref (elements "AND"ed together) +# ) + +sub balance { + my($start, $end, %opt) = @_; - my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : ''; + my $as = $opt{'no_as'} ? '' : " AS balance_${start}_$end"; - my $as = $opt{'noas'} ? '' : "as owed_${start}_$end"; + #handle start and end ranges (86400 = 24h * 60m * 60s) + my $str2time = str2time_sql; + $start = $start ? "( $str2time now() ) - ".($start * 86400). ' )' : ''; + $end = $end ? "( $str2time now() ) - ".($end * 86400). ' )' : ''; - my $charged = <balance_date_sql( $start, $end, %opt ). $as; - "coalesce( ( select $charged from cust_bill $join $where ) ,0 ) $as"; +} +sub format_balance { #closures help alot + my $range = shift; + sub { sprintf( $money_char.'%.2f', shift->get("balance_$range") ) }; } -- 2.11.0