X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;ds=sidebyside;f=httemplate%2Fsearch%2Fe911.html;h=e2283f8737e48e46127e88f1e25c64b208b39c8b;hb=01fbb8674cee73686d3e847565fd21b71eaeb983;hp=6a9dd0a1690a1a2b314484ffc57f617d15a76ba8;hpb=8924a9dd6fa3d985a061e8c6e542990173adae34;p=freeside.git
diff --git a/httemplate/search/e911.html b/httemplate/search/e911.html
index 6a9dd0a16..e2283f873 100644
--- a/httemplate/search/e911.html
+++ b/httemplate/search/e911.html
@@ -14,7 +14,11 @@ table.grid TD { font-weight: bold;
<% $row->{quantity} || 0 %> |
- Total fees collected: |
+ Total fees charged: |
+ <% $money_char.sprintf('%.2f', $row->{charged_amount}) %> |
+
+
+ Fee payments collected: |
<% $money_char.sprintf('%.2f', $row->{paid_amount}) %> |
@@ -53,18 +57,19 @@ my $agentnum = $1;
# bazillion scalar_sql queries. Use a properly grouped aggregate query.
my $select = 'SELECT cust_bill_pkg.billpkgnum, cust_bill_pkg.quantity, '.
-'SUM(cust_bill_pay_pkg.amount) AS paid_amount';
+'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)
- JOIN cust_bill_pay_pkg USING (billpkgnum)
- JOIN cust_bill_pay USING (billpaynum)
+ 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";
+AND ( (cust_bill_pay._date >= $begin AND cust_bill_pay._date < $end)
+ OR cust_bill_pay.paynum IS NULL )";
if ( $agentnum ) {
$from .= ' JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)';
@@ -73,13 +78,14 @@ if ( $agentnum ) {
my $subquery = "$select $from $where
GROUP BY cust_bill_pkg.billpkgnum, cust_bill_pkg.quantity";
+warn $subquery;
# 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).
# now sum those rows.
-my $sql = "SELECT SUM(quantity) AS quantity, SUM(paid_amount) AS paid_amount
-FROM ($subquery) AS paid_fees"; # no grouping
+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
my $sth = dbh->prepare($sql);
$sth->execute;