5 Finds billing totals for a given pkgpart where the bill item matches
6 cust_pkg.pkgpart or cust_bill_pkg.pkgpart_override columns.
8 Given date range, filter by when the invoice was paid.
10 * E911 access lines - SUM(cust_bill_pkg.quantity)
11 * Total fees charged - SUM(cust_bill_pay_pkg.amount)
12 * Fee payments collected - SUM(cust_bill_pkg.setup) + SUM(cust_bill_pkg.recur)
14 * Administrative fee (1%) - 1% of Fee Payments Collected
15 * Amount due - 99% of Fee Payments Collected
19 <& /elements/header.html, 'E911 Fee Report' &>
21 <& /elements/table-grid.html &>
22 <STYLE TYPE="text/css">
23 table.grid TD:first-child { font-weight: normal }
24 table.grid TD { font-weight: bold;
29 <TR><TH COLSPAN=2><% $legend %></TH></TR>
31 <TD><% mt('E911 access lines') %>:</TD>
32 <TD><% $report{e911_access_lines} %></TD>
35 <TD><% mt('Total fees charged') %>: </TD>
36 <TD><% $money_char.$report{fees_charged} %></TD>
39 <TD><% mt('Fee payments collected') %>: </TD>
40 <TD><% $money_char.$report{fees_collected} %></TD>
43 <TD><% mt('Administrative fee') %> (1%): </TD>
44 <TD><% $money_char.$report{admin_fee} %></TD>
47 <TD><% mt('Amount due') %>: </TD>
48 <TD><% $money_char.$report{e911_amount_due} %></TD>
51 <& /elements/footer.html &>
53 % $cgi->param('error' => 'No paid E911 fees found.');
54 <& /elements/errorpage.html &>
61 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
63 my $money_char = FS::Conf->new->config('money_char') || '$';
65 my($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
67 $cgi->param('e911pkgpart') =~ /^(\d+)$/;
68 my $pkgpart = $1 or die 'bad e911pkgpart';
70 $cgi->param('agentnum') =~ /^(\d*)$/;
73 # This has the potential to become as nightmarish as the old tax report.
74 # If we end up doing multiple rows for some reason (date intervals,
75 # package classes, etc.), do NOT simply loop through this and do a
76 # bazillion scalar_sql queries. Use a properly grouped aggregate query.
80 sum(cust_bill_pkg.quantity) as quantity,
81 sum(cust_bill_pay_pkg.amount) as amount,
82 sum(cust_bill_pkg.setup) as setup,
83 sum(cust_bill_pkg.recur) as recur
86 LEFT JOIN cust_bill_pkg USING (pkgnum)
87 LEFT JOIN cust_bill_pay_pkg USING (billpkgnum)
88 LEFT JOIN cust_bill_pay USING (billpaynum)
92 LEFT JOIN cust_main USING (custnum)
94 cust_main.agentnum = ?
102 ( cust_bill_pkg.pkgpart_override = ? OR cust_pkg.pkgpart = ? )
104 ( cust_bill_pay._date >= ? AND cust_bill_pay._date < ? )
105 OR cust_bill_pay.paynum IS NULL
109 # Preserving this oddball, unexplained epoch substitution
110 $end = '' if $end == 4294967295;
113 $agentnum ? $agentnum : (),
121 warn "\$sql_statement: $sql_statement\n";
122 warn "\@bind_values: ".join(', ',@bind_values)."\n";
125 my $sth = dbh->prepare( $sql_statement );
126 $sth->execute( @bind_values ) || die $sth->errstr;
127 my $row = $sth->fetchrow_hashref;
130 e911_access_lines => $row->{quantity} || 0,
132 fees_charged => sprintf(
134 ( $row->{setup} + $row->{recur} ) || 0,
137 fees_collected => sprintf(
139 ( $row->{amount} || 0 ),
143 # Does everybody use this 1% admin fee? Should this be configurable?
144 $report{admin_fee} = sprintf( "%.2f", $report{fees_collected} * 0.01 );
145 $report{e911_amount_due} = $report{fees_collected} - $report{admin_fee};
149 ? DateTime->from_epoch(epoch => $begin)->mdy('/')
152 my $end_text = DateTime->from_epoch(epoch => ( $end || time ))->mdy('/');
154 my $legend = FS::agent->by_key($agentnum)->agent . ', ' if $agentnum;
155 if ( $begin && $end ) {
156 $legend .= "$begin_text ↔ $end_text";
158 $legend .= mt('After')." $begin_text";
160 $legend .= mt('Through')." $end_text"