<%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 ) {
<& /elements/header.html, 'E911 Fee Report' &>
<& /elements/table-grid.html &>
| <% $legend %> |
|---|
| <% mt('E911 access lines') %>: |
<% $report{e911_access_lines} %> |
| <% mt('Total fees charged') %>: |
<% $money_char.$report{fees_charged} %> |
| <% mt('Fee payments collected') %>: |
<% $money_char.$report{fees_collected} %> |
| <% mt('Administrative fee') %> (1%): |
<% $money_char.$report{admin_fee} %> |
| <% mt('Amount due') %>: |
<% $money_char.$report{e911_amount_due} %> |
<& /elements/footer.html &>
% } else { # no data
% $cgi->param('error' => 'No paid E911 fees found.');
<& /elements/errorpage.html &>
% }
<%init>
our $DEBUG;
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
my $money_char = FS::Conf->new->config('money_char') || '$';
my($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
$cgi->param('e911pkgpart') =~ /^(\d+)$/;
my $pkgpart = $1 or die 'bad e911pkgpart';
$cgi->param('agentnum') =~ /^(\d*)$/;
my $agentnum = $1;
# This has the potential to become as nightmarish as the old tax report.
# If we end up doing multiple rows for some reason (date intervals,
# package classes, etc.), do NOT simply loop through this and do a
# bazillion scalar_sql queries. Use a properly grouped aggregate query.
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 ) {
$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 @bind_values = (
$agentnum ? $agentnum : (),
$pkgpart,
$pkgpart,
$begin || 0,
$end || time(),
);
if ( $DEBUG ) {
warn "\$sql_statement: $sql_statement\n";
warn "\@bind_values: ".join(', ',@bind_values)."\n";
}
my $sth = dbh->prepare( $sql_statement );
$sth->execute( @bind_values ) || die $sth->errstr;
my $row = $sth->fetchrow_hashref;
my %report = (
e911_access_lines => $row->{quantity} || 0,
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 .= mt('After')." $begin_text";
} else {
$legend .= mt('Through')." $end_text"
}