From 0db0d9a79acc7e62430149ae155948b089e82bb5 Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Sun, 8 Jul 2018 18:19:32 -0500 Subject: [PATCH] RT# 32241 E911 Fee Report - Include fees bundled with packages --- httemplate/search/e911.html | 156 +++++++++++++++++++++++++++++--------------- 1 file changed, 104 insertions(+), 52 deletions(-) diff --git a/httemplate/search/e911.html b/httemplate/search/e911.html index 75dbef7d5..6d387d563 100644 --- a/httemplate/search/e911.html +++ b/httemplate/search/e911.html @@ -1,6 +1,23 @@ +<%doc> + + E911 Fee Report + + Finds billing totals for a given pkgpart where the bill item matches + cust_pkg.pkgpart or cust_bill_pkg.pkgpart_override columns. + + Given date range, filter by when the invoice was paid. + + * E911 access lines - SUM(cust_bill_pkg.quantity) + * Total fees charged - SUM(cust_bill_pay_pkg.amount) + * Fee payments collected - SUM(cust_bill_pkg.setup) + SUM(cust_bill_pkg.recur) + + * Administrative fee (1%) - 1% of Fee Payments Collected + * Amount due - 99% of Fee Payments Collected + + % if ( $row ) { -%# pretty minimal report <& /elements/header.html, 'E911 Fee Report' &> + <& /elements/table-grid.html &> + <% $legend %> - E911 access lines: - <% $row->{quantity} || 0 %> + <% mt('E911 access lines') %>: + <% $report{e911_access_lines} %> - Total fees charged: - <% $money_char.sprintf('%.2f', $row->{charged_amount}) %> + <% mt('Total fees charged') %>: + <% $money_char.$report{fees_charged} %> - Fee payments collected: - <% $money_char.sprintf('%.2f', $row->{paid_amount}) %> + <% mt('Fee payments collected') %>: + <% $money_char.$report{fees_collected} %> - Administrative fee (1%): - <% $money_char.sprintf('%.2f', $row->{paid_amount} * $admin_fee) %> + <% mt('Administrative fee') %> (1%): + <% $money_char.$report{admin_fee} %> - Amount due: - <% $money_char.sprintf('%.2f', $row->{paid_amount} * (1-$admin_fee) ) %> - + <% mt('Amount due') %>: + <% $money_char.$report{e911_amount_due} %> <& /elements/footer.html &> @@ -38,6 +55,8 @@ table.grid TD { font-weight: bold; % } <%init> +our $DEBUG; + die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); @@ -56,56 +75,89 @@ my $agentnum = $1; # package classes, etc.), do NOT simply loop through this and do a # bazillion scalar_sql queries. Use a properly grouped aggregate query. -my $select = 'SELECT cust_bill_pkg.billpkgnum, cust_bill_pkg.quantity, '. -'cust_bill_pkg.setup, SUM(cust_bill_pay_pkg.amount) AS paid_amount'; - -my $from = 'FROM cust_pkg - JOIN cust_bill_pkg USING (pkgnum) - JOIN cust_bill USING (invnum) - LEFT JOIN cust_bill_pay_pkg USING (billpkgnum) - LEFT JOIN cust_bill_pay USING (billpaynum) -'; -# going by payment application date here, which should be -# max(invoice date, payment date) -my $where = "WHERE cust_pkg.pkgpart = $pkgpart -AND ( (cust_bill_pay._date >= $begin AND cust_bill_pay._date < $end) - OR cust_bill_pay.paynum IS NULL )"; +my $sql_statement = " + SELECT + sum(cust_bill_pkg.quantity) as quantity, + sum(cust_bill_pay_pkg.amount) as amount, + sum(cust_bill_pkg.setup) as setup, + sum(cust_bill_pkg.recur) as recur + FROM cust_pkg + LEFT JOIN cust_bill_pkg USING (pkgnum) + LEFT JOIN cust_bill_pay_pkg USING (billpkgnum) + LEFT JOIN cust_bill_pay USING (billpaynum) +"; if ( $agentnum ) { - $from .= ' JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)'; - $where .= "\n AND cust_main.agentnum = $agentnum"; + $sql_statement .= " + LEFT JOIN cust_main USING (custnum) + WHERE + cust_main.agentnum = ? + AND "; +} else { + $sql_statement .= " + WHERE + " } +$sql_statement .= " + ( cust_bill_pkg.pkgpart_override = ? OR cust_pkg.pkgpart = ? ) + AND ( + ( cust_bill_pay._date >= ? AND cust_bill_pay._date < ? ) + OR cust_bill_pay.paynum IS NULL + ); +"; + +# Preserving this oddball, unexplained epoch substitution +$end = '' if $end == 4294967295; -my $subquery = "$select $from $where -GROUP BY cust_bill_pkg.billpkgnum, cust_bill_pkg.quantity"; -# This has one row for each E911 line item that has any payments applied. -# Fields are the billpkgnum of the item (currently unused), the number of -# E911 charges, and the total amount paid (always > 0). +my @bind_values = ( + $agentnum ? $agentnum : (), + $pkgpart, + $pkgpart, + $begin || 0, + $end || time(), +); -# now sum those rows. -my $sql = "SELECT SUM(quantity) AS quantity, SUM(setup) AS charged_amount, -SUM(paid_amount) AS paid_amount FROM ($subquery) AS paid_fees"; # no grouping +if ( $DEBUG ) { + warn "\$sql_statement: $sql_statement\n"; + warn "\@bind_values: ".join(', ',@bind_values)."\n"; +} -my $sth = dbh->prepare($sql); -$sth->execute; +my $sth = dbh->prepare( $sql_statement ); +$sth->execute( @bind_values ) || die $sth->errstr; my $row = $sth->fetchrow_hashref; -my $admin_fee = 0.01; # 1% admin fee, allowed in Texas +my %report = ( + e911_access_lines => $row->{quantity} || 0, -$end = '' if $end == 4294967295; -my $legend = ''; -if ( $agentnum ) { - $legend = FS::agent->by_key($agentnum)->agent . ', '; -} -if ( $begin and $end ) { - $legend .= time2str('%h %o %Y', $begin) . '—' . - time2str('%h %o %Y', $end); + fees_charged => sprintf( + "%.2f", + ( $row->{setup} + $row->{recur} ) || 0, + ), + + fees_collected => sprintf( + "%.2f", + ( $row->{amount} || 0 ), + ), +); + +# Does everybody use this 1% admin fee? Should this be configurable? +$report{admin_fee} = sprintf( "%.2f", $report{fees_collected} * 0.01 ); +$report{e911_amount_due} = $report{fees_collected} - $report{admin_fee}; + +my $begin_text = + $begin + ? DateTime->from_epoch(epoch => $begin)->mdy('/') + : mt('Anytime'); + +my $end_text = DateTime->from_epoch(epoch => ( $end || time ))->mdy('/'); + +my $legend = FS::agent->by_key($agentnum)->agent . ', ' if $agentnum; +if ( $begin && $end ) { + $legend .= "$begin_text ↔ $end_text"; } elsif ( $begin ) { - $legend .= time2str('after %h %o %Y', $begin); -} elsif ( $end ) { - $legend .= time2str('before %h %o %Y', $end); + $legend .= mt('After')." $begin_text"; } else { - $legend .= 'any time'; + $legend .= mt('Through')." $end_text" } -$legend = ucfirst($legend); + -- 2.11.0