X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_receivables.cgi;h=3696ed40d25bf9df4de55ab28a8106bb8a957e96;hb=7b125e587a4d1ee0aca692e23ea7897f671855ae;hp=f0274d02614e76b5f33b00f6d056ecc908ebd00d;hpb=1203c14b5b34bc848c3176899767a4fbc13f914a;p=freeside.git diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi index f0274d026..3696ed40d 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -1,190 +1,52 @@ - -<% - my $sql = < extract(epoch from now())-2592000 - and cust_main.custnum = cust_bill.custnum - ) - ,0 - ) as owed_0_30, - - coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - ) - from cust_bill - where cust_bill._date > extract(epoch from now())-5184000 - and cust_bill._date <= extract(epoch from now())-2592000 - and cust_main.custnum = cust_bill.custnum - ) - ,0 - ) as owed_30_60, - - coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - ) - from cust_bill - where cust_bill._date > extract(epoch from now())-7776000 - and cust_bill._date <= extract(epoch from now())-5184000 - and cust_main.custnum = cust_bill.custnum - ) - ,0 - ) as owed_60_90, - - coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - ) - from cust_bill - where cust_bill._date <= extract(epoch from now())-7776000 - and cust_main.custnum = cust_bill.custnum - ) - ,0 - ) as owed_90_plus, - - coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - ) - from cust_bill - where cust_main.custnum = cust_bill.custnum - ) - ,0 - ) as owed_total - -from cust_main - -where 0 < - coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - ) - from cust_bill - where cust_main.custnum = cust_bill.custnum - ) - ,0 - ) - -order by company, last - -END - - - #order by! - - #the grep (and the sort ) should be pushed down to SQL - #my @cust_main = sort { $a->company cmp $b->company - # || $a->last cmp $b->last } - # grep { $_->balance } - # qsearch('cust_main', {} ); - - my $totals_table = table(). 'Total'. - '0-30'. - '30-60'. - '60-90'. - '90+'. - 'total'. - ''; - $totals_table = ''; - - my $sth = dbh->prepare($sql) or die dbh->errstr; - $sth->execute or die $sth->errstr; - +<% include( 'elements/cust_main_dayranges.html', + 'title' => 'Accounts Receivable Aging Summary', + 'range_sub' => \&balance, + 'payment_links' => 1, + ) %> -<%= header('Accounts Receivable Aging Summary', menubar( 'Main Menu'=>$p, ) ) %> -<%= $totals_table %> -<%= table() %> - - Customer - 0-30 - 30-60 - 60-90 - 90+ - Total - -<% while ( my $row = $sth->fetchrow_hashref() ) { %> - - - <%= $row->{'company'} ? $row->{'company'}. ' (' : '' %> - <%= $row->{'last'}. ', '. $row->{'first'} %> - <%= $row->{'company'} ? ')' : '' %> - - $<%= sprintf("%.2f", $row->{'owed_0_30'} ) %> - $<%= sprintf("%.2f", $row->{'owed_30_60'} ) %> - $<%= sprintf("%.2f", $row->{'owed_60_90'} ) %> - $<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %> - $<%= sprintf("%.2f", $row->{'owed_total'} ) %> - -<% } %> - -<%= $totals_table %> - - +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Receivables report') + or $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + +<%once> + +#Example: +# +# my $balance = balance( +# $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") +# 'sum' => 1, #set to true to get a SUM() of the values, for totals +# +# #obsolete? options for totals (passed to cust_main::balance_date_sql) +# '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, $offset) = @_; #, %opt ? + #handle start and end ranges (86400 = 24h * 60m * 60s) + my $str2time = str2time_sql; + my $closing = str2time_sql_closing; + + # $end == 0 means "+infinity", while $start == 0 really means 0 + # so we should always include a start condition + $start = "( $str2time now() $closing - ". ($start + $offset) * 86400 . ' )'; + # but only include an end condition if $end != 0 + $end = $end ? + "( $str2time now() $closing - ". ($end + $offset) * 86400 . ' )' + : ''; + + #$opt{'unapplied_date'} = 1; + + FS::cust_main->balance_date_sql( $start, $end, 'unapplied_date'=>1, + 'cutoff' => "( $str2time now() $closing - ".$offset * 86400 . ')' ); + +} + +