From 0f239ed9a3d20edc4974eed38d2816f20185aefc Mon Sep 17 00:00:00 2001 From: mark Date: Wed, 22 Sep 2010 00:08:30 +0000 Subject: [PATCH] unapplied payment/refund/credit reports, RT#7503 --- httemplate/elements/menu.html | 3 + httemplate/search/cust_credit.html | 144 +++++++++++++-------- httemplate/search/elements/cust_pay_or_refund.html | 63 ++++++--- .../search/elements/report_cust_pay_or_refund.html | 6 +- httemplate/search/report_cust_credit.html | 7 +- 5 files changed, 151 insertions(+), 72 deletions(-) diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index d3e00f3de..b852a402e 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -259,6 +259,7 @@ $report_payments{'Pending Payments'} = [ $fsurl.'search/cust_pay_pending.html?ma if $curuser->access_right('View customer pending payments'); $report_payments{'Voided Payments'} = [ $fsurl.'search/report_cust_pay.html?void=1', 'Voided payment report (by type and/or date range)' ] if $curuser->access_right('View customer pending payments'); +$report_payments{'Unapplied Payments'} = [ $fsurl.'search/report_cust_pay.html?unapplied=1', 'Unapplied payment report (by type and/or date range)' ]; $report_payments{'Payment Batches'} = [ $fsurl.'search/pay_batch.html', 'Payment batches (by status and/or date range)' ] if $conf->exists('batch-enable') || $conf->config('batch-enable_payby'); $report_payments{'Unapplied Payment Aging'} = [ $fsurl.'search/report_unapplied_cust_pay.html', 'Unapplied payment aging report' ]; @@ -272,7 +273,9 @@ if($curuser->access_right('Financial reports')) { 'Rated Call Sales Report' => [ $fsurl.'graph/report_cust_bill_pkg_detail.html', 'Sales report and graph (by agent, package class, usage class and/or date range)' ], 'Employee Commission Report' => [ $fsurl.'search/report_employee_commission.html', '' ], 'Credit Report' => [ $fsurl.'search/report_cust_credit.html', 'Credit report (by employee and/or date range)' ], + 'Unapplied Credits' => [ $fsurl.'search/report_cust_credit.html?unapplied=1', 'Unapplied credit report (by type and/or date range)' ], 'Refund Report' => [ $fsurl.'search/report_cust_refund.html', 'Refund report (by type and/or date range)' ], + 'Unapplied Refunds' => [ $fsurl.'search/report_cust_refund.html?unapplied=1', 'Unapplied refund report (by type and/or date range)' ], 'Package Costs Report' => [ $fsurl.'graph/report_cust_pkg_cost.html', 'Package setup and recurring costs graph' ], ); $report_financial{'A/R Aging'} = [ $fsurl.'search/report_receivables.html', 'Accounts Receivable Aging report' ]; diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index ad8472134..a3b22b113 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -3,47 +3,14 @@ 'name' => 'credits', 'query' => $sql_query, 'count_query' => $count_query, - 'count_addl' => [ '$%.2f total credited (gross)', ], + 'count_addl' => \@count_addl, #'redirect' => $link, - 'header' => [ 'Amount', - 'Date', - 'By', - 'Reason', - FS::UI::Web::cust_header(), - ], - 'fields' => [ - #'crednum', - sub { sprintf('$%.2f', shift->amount ) }, - sub { time2str('%b %d %Y', shift->_date ) }, - 'otaker', - 'reason', - \&FS::UI::Web::cust_fields, - ], - #'align' => 'rrrllll', - 'align' => 'rrll'.FS::UI::Web::cust_aligns(), - 'links' => [ - '', - '', - '', - '', - ( map { $_ ne 'Cust. Status' ? $clink : '' } - FS::UI::Web::cust_header() - ), - ], - 'color' => [ - '', - '', - '', - '', - FS::UI::Web::cust_colors(), - ], - 'style' => [ - '', - '', - '', - '', - FS::UI::Web::cust_styles(), - ], + 'header' => \@header, + 'fields' => \@fields, + 'align' => $align, + 'links' => \@links, + 'color' => \@color, + 'style' => \@style, ) %> <%init> @@ -51,9 +18,70 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +my $money_char = FS::Conf->new->config('money_char') || '$'; + my $title = 'Credit Search Results'; #my( $count_query, $sql_query ); +my $unapplied = $cgi->param('unapplied'); +$title = "Unapplied $title" if $unapplied; +my $clink = sub { + my $cust_bill = shift; + $cust_bill->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +my (@header, @fields, $align, @links, @color, @style); +$align = ''; + +#amount +push @header, 'Amount'; +push @fields, sub { $money_char .sprintf('%.2f', shift->amount) }; +$align .= 'r'; +push @links, ''; +push @color, ''; +push @style, ''; + +# unapplied amount +if ($unapplied) { + push @header, 'Unapplied'; + push @fields, sub { $money_char .sprintf('%.2f', shift->unapplied_amount) }; + $align .= 'r'; + push @links, ''; + push @color, ''; + push @style, ''; +} + +push @header, 'Date', + 'By', + 'Reason', + FS::UI::Web::cust_header(), + ; +push @fields, sub { time2str('%b %d %Y', shift->_date ) }, + 'otaker', + 'reason', + \&FS::UI::Web::cust_fields, + ; +$align .= 'rll'.FS::UI::Web::cust_aligns(), +push @links, '', + '', + '', + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), + ; +push @color, '', + '', + '', + FS::UI::Web::cust_colors(), + ; +push @style, '', + '', + '', + FS::UI::Web::cust_styles(), + ; + my @search = (); if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { @@ -67,6 +95,10 @@ if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { $title = $agent->agent. " $title"; } +if ( $unapplied ) { + push @search, FS::cust_credit->unapplied_sql . ' > 0'; +} + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @search, "_date >= $beginning ", "_date <= $ending"; @@ -76,29 +108,33 @@ push @search, FS::UI::Web::parse_lt_gt($cgi, 'amount' ); #here is the agent virtualization push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; +my @select = ( + 'cust_credit.*', + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields(), +); + +if ( $unapplied ) { + push @select, '('.FS::cust_credit->unapplied_sql .') AS unapplied_amount'; + push @search, FS::cust_credit->unapplied_sql .' > 0'; +} + my $where = 'WHERE '. join(' AND ', @search); -my $count_query = 'SELECT COUNT(*), SUM(amount) '. - 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. +my $count_query = 'SELECT COUNT(*), SUM(amount) '; +$count_query .= ', SUM(' . FS::cust_credit->unapplied_sql . ') ' if $unapplied; +$count_query .= 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. $where; +my @count_addl = ( $money_char.'%.2f total credited (gross)' ); +push @count_addl, $money_char.'%.2f unapplied' if $unapplied; + my $sql_query = { 'table' => 'cust_credit', - 'select' => join(', ', - 'cust_credit.*', - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), + 'select' => join(', ',@select), 'hashref' => {}, 'extra_sql' => $where, 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', }; - my $clink = sub { - my $cust_bill = shift; - $cust_bill->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; - }; - diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index 75493f409..6f4aaf848 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -44,7 +44,7 @@ Examples: 'name_singular' => $name_singular, 'query' => $sql_query, 'count_query' => $count_query, - 'count_addl' => [ '$%.2f total '.$opt{name_verb}, ], + 'count_addl' => \@count_addl, 'redirect_empty' => $opt{'redirect_empty'}, 'header' => \@header, 'fields' => \@fields, @@ -68,7 +68,10 @@ my $table = $opt{'table'} || 'cust_'.$opt{'thing'}; my $amount_field = $opt{'amount_field'}; my $name_singular = $opt{'name_singular'}; -my $title = "\u$name_singular Search Results"; +my $unapplied = $cgi->param('unapplied'); +my $title = ''; +$title = 'Unapplied ' if $unapplied; +$title .= "\u$name_singular Search Results"; my $link = ''; if ( ( $curuser->access_right('View invoices') #XXX for now @@ -143,15 +146,25 @@ if ( $opt{'pre_header'} ) { push @header, "\u$name_singular", 'Amount', - 'Date', ; -$align .= 'rrr'; -push @links, '', '', ''; +$align .= 'rr'; +push @links, '', ''; push @fields, 'payby_payinfo_pretty', sub { sprintf('$%.2f', shift->$amount_field() ) }, - sub { time2str('%b %d %Y', shift->_date ) }, ; +if ( $unapplied ) { + push @header, 'Unapplied'; + $align .= 'r'; + push @links, ''; + push @fields, sub { sprintf('$%.2f', shift->unapplied_amount) }; +} + +push @header, 'Date'; +$align .= 'r'; +push @links, ''; +push @fields, sub { time2str('%b %d %Y', shift->_date ) }; + unless ( $opt{'disable_by'} ) { push @header, 'By'; $align .= 'c'; @@ -192,10 +205,17 @@ push @header, @{ $opt{'addl_header'} } push @fields, @{ $opt{'addl_fields'} } if $opt{'addl_fields'}; -my( $count_query, $sql_query ); +my( $count_query, $sql_query, @count_addl ); if ( $cgi->param('magic') ) { my @search = (); + my @select = ( + "$table.*", + FS::UI::Web::cust_sql_fields(), + 'cust_main.custnum AS cust_main_custnum', + ); + push @select, $tax_names if $tax_names; + my $orderby; if ( $cgi->param('magic') eq '_date' ) { @@ -328,6 +348,13 @@ if ( $cgi->param('magic') ) { die "unknown search magic: ". $cgi->param('magic'); } + #unapplied payment/refund + if ( $unapplied ) { + push @select, '(' . "FS::$table"->unapplied_sql . ') AS unapplied_amount'; + push @search, "FS::$table"->unapplied_sql . ' > 0'; + + } + #for the history search if ( $cgi->param('history_action') =~ /^([\w,]+)$/ ) { my @history_action = split(/,/, $1); @@ -374,18 +401,18 @@ if ( $cgi->param('magic') ) { my $search = ' WHERE '. join(' AND ', @search); - $count_query = "SELECT COUNT(*), SUM($amount_field) ". - "FROM $table $addl_from". - "$search $group_by"; + $count_query = "SELECT COUNT(*), SUM($amount_field) "; + $count_query .= ', SUM(' . "FS::$table"->unapplied_sql . ') ' + if $unapplied; + $count_query .= "FROM $table $addl_from". + "$search $group_by"; + + @count_addl = ( '$%.2f total '.$opt{name_verb} ); + push @count_addl, '$%.2f unapplied' if $unapplied; $sql_query = { 'table' => $table, - 'select' => join(', ', - "$table.*", - ( $tax_names ? $tax_names : () ), - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), + 'select' => join(', ', @select), 'hashref' => {}, 'extra_sql' => "$search $group_by ORDER BY $orderby", 'addl_from' => $addl_from, @@ -404,6 +431,7 @@ if ( $cgi->param('magic') ) { $count_query = "SELECT COUNT(*), SUM($amount_field) FROM $table". " WHERE payinfo = '$payinfo' AND payby = '$payby'". " AND ". $curuser->agentnums_sql; + @count_addl = ( '$%.2f total '.$opt{name_verb} ); $sql_query = { 'table' => $table, @@ -415,4 +443,7 @@ if ( $cgi->param('magic') ) { } +# for consistency +$title = join('',map {ucfirst} split(/\b/,$title)); + diff --git a/httemplate/search/elements/report_cust_pay_or_refund.html b/httemplate/search/elements/report_cust_pay_or_refund.html index 885457cea..9af4e33dc 100644 --- a/httemplate/search/elements/report_cust_pay_or_refund.html +++ b/httemplate/search/elements/report_cust_pay_or_refund.html @@ -17,6 +17,7 @@ Examples:
+ @@ -138,8 +139,11 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); my $void = $cgi->param('void') ? 1 : 0; +my $unapplied = $cgi->param('unapplied') ? 1 : 0; -my $title = $void ? "Voided $name_singular report" : "\u$name_singular report"; +my $title = $void ? "Voided $name_singular report" : + $unapplied ? "Unapplied $name_singular report" : + "\u$name_singular report" ; $table .= '_void' if $void; diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html index c7e552238..16a75ebf4 100644 --- a/httemplate/search/report_cust_credit.html +++ b/httemplate/search/report_cust_credit.html @@ -1,7 +1,8 @@ -<% include('/elements/header.html', 'Credit report' ) %> +<% include('/elements/header.html', $title ) %> +
@@ -48,5 +49,9 @@ my %access_user = map { $_ => qsearchs('access_user',{'usernum'=>$_})->username } @usernum; +my $unapplied = $cgi->param('unapplied') ? 1 : 0; + +my $title = $cgi->param('unapplied') ? + 'Unapplied credit report' : 'Credit report'; -- 2.11.0