2 %# pretty minimal report
3 <& /elements/header.html, 'E911 Fee Report' &>
4 <& /elements/table-grid.html &>
5 <STYLE TYPE="text/css">
6 table.grid TD:first-child { font-weight: normal }
7 table.grid TD { font-weight: bold;
11 <TR><TH COLSPAN=2><% $legend %></TH></TR>
13 <TD>E911 access lines:</TD>
14 <TD><% $row->{quantity} || 0 %></TD>
17 <TD>Total fees collected: </TD>
18 <TD><% $money_char.sprintf('%.2f', $row->{paid_amount}) %></TD>
21 <TD>Administrative fee (1%): </TD>
22 <TD><% $money_char.sprintf('%.2f', $row->{paid_amount} * $admin_fee) %></TD>
26 <TD><% $money_char.sprintf('%.2f', $row->{paid_amount} * (1-$admin_fee) ) %>
30 <& /elements/footer.html &>
32 % $cgi->param('error' => 'No paid E911 fees found.');
33 <& /elements/errorpage.html &>
38 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
40 my $money_char = FS::Conf->new->config('money_char') || '$';
42 my($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
44 $cgi->param('e911pkgpart') =~ /^(\d+)$/;
45 my $pkgpart = $1 or die 'bad e911pkgpart';
47 $cgi->param('agentnum') =~ /^(\d*)$/;
50 # This has the potential to become as nightmarish as the old tax report.
51 # If we end up doing multiple rows for some reason (date intervals,
52 # package classes, etc.), do NOT simply loop through this and do a
53 # bazillion scalar_sql queries. Use a properly grouped aggregate query.
55 my $select = 'SELECT cust_bill_pkg.billpkgnum, cust_bill_pkg.quantity, '.
56 'SUM(cust_bill_pay_pkg.amount) AS paid_amount';
58 my $from = 'FROM cust_pkg
59 JOIN cust_bill_pkg USING (pkgnum)
60 JOIN cust_bill USING (invnum)
61 JOIN cust_bill_pay_pkg USING (billpkgnum)
62 JOIN cust_bill_pay USING (billpaynum)
64 # going by payment application date here, which should be
65 # max(invoice date, payment date)
66 my $where = "WHERE cust_pkg.pkgpart = $pkgpart
67 AND cust_bill_pay._date >= $begin AND cust_bill_pay._date < $end";
70 $from .= ' JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)';
71 $where .= "\n AND cust_main.agentnum = $agentnum";
74 my $subquery = "$select $from $where
75 GROUP BY cust_bill_pkg.billpkgnum, cust_bill_pkg.quantity";
76 # This has one row for each E911 line item that has any payments applied.
77 # Fields are the billpkgnum of the item (currently unused), the number of
78 # E911 charges, and the total amount paid (always > 0).
81 my $sql = "SELECT SUM(quantity) AS quantity, SUM(paid_amount) AS paid_amount
82 FROM ($subquery) AS paid_fees"; # no grouping
84 my $sth = dbh->prepare($sql);
86 my $row = $sth->fetchrow_hashref;
88 my $admin_fee = 0.01; # 1% admin fee, allowed in Texas
90 $end = '' if $end == 4294967295;
93 $legend = FS::agent->by_key($agentnum)->agent . ', ';
95 if ( $begin and $end ) {
96 $legend .= time2str('%h %o %Y', $begin) . '—' .
97 time2str('%h %o %Y', $end);
99 $legend .= time2str('after %h %o %Y', $begin);
101 $legend .= time2str('before %h %o %Y', $end);
103 $legend .= 'any time';
105 $legend = ucfirst($legend);