Issue 3309520: Email AR Aging statement patch

[John Locke <..hidden..>]

This one I just fixed today -- we've got a lot of accounts aging, and
I'm tired of having to look up email addresses ;-)

Note that AR -> Aging calls LedgerSMB::RP->get_customer which looks for
the addresses in the "customer" view. This view did not even have an
"email" column and did not contain data for cc and bcc. I had to drop
the view because the number of columns was changing, then load the new
view with the appropriate joins to the eca_to_contact table. Also, in
the customer view, it needed to key off "entity_id" not "id".

Regarding admin/manager access to the bcc field, Chris had indicated
several months ago when I spoke to him about this, that there wasn't an
equivalent in LSMB 1.3 to use, so for this and the previous patch, I
just stripped the check for this access from the template, so it always
shows bcc.

Cheers,
John Locke
http://freelock.com

diff --git LedgerSMB/RP.pm LedgerSMB/RP.pm
index c67b7dc..f1e5b1e 100644
--- LedgerSMB/RP.pm
+++ LedgerSMB/RP.pm
@@ -1872,7 +1872,7 @@ sub get_customer {
 
     my $query = qq|
 		SELECT name, email, cc, bcc FROM $form->{ct} ct
-		 WHERE ct.id = ?|;
+		 WHERE ct.entity_id = ?|;
     $sth = $dbh->prepare($query);
     $sth->execute( $form->{"$form->{ct}_id"} );
     ( $form->{ $form->{ct} }, $form->{email}, $form->{cc}, $form->{bcc} ) =
diff --git UI/rp-email.html UI/rp-email.html
index 731b388..d990dbf 100644
--- UI/rp-email.html
+++ UI/rp-email.html
@@ -20,11 +20,8 @@
 	<tr>
 	  <th align="right" nowrap="nowrap"><?lsmb text('Subject') ?></th>
 	  <td><?lsmb PROCESS input element_data={name => 'subject', size=> '30', value => form.subject} ?></td>
-<?lsmb IF user.role == 'admin' OR user.role == 'manager' ?>
 	  <th align="right" nowrap="nowrap"><?lsmb text('Bcc') ?></th>
-	  <td><?lsmb PROCESS input element_data={name => 'bcc', size=> '30', value => form.bcc} ?></td><?lsmb
-ELSE ?><th> </th><td> </td><?lsmb
-END ?>
+	  <td><?lsmb PROCESS input element_data={name => 'bcc', size=> '30', value => form.bcc} ?></td>
 	</tr>
       </table>
     </td>
diff --git bin/rp.pl bin/rp.pl
index af1f43f..326e7a3 100644
--- bin/rp.pl
+++ bin/rp.pl
@@ -1413,7 +1413,7 @@ sub e_mail {
 
     &print_options;
 
-    for (qw(email cc bcc subject message type sendmode format action nextsub)) {
+    for (qw(subject message type sendmode format action nextsub)) {
         delete $form->{$_};
     }
 
diff --git sql/Pg-database.sql sql/Pg-database.sql
index 06cfd28..cd75561 100644
--- sql/Pg-database.sql
+++ sql/Pg-database.sql
@@ -940,8 +940,9 @@ CREATE VIEW customer AS
         emd.creditlimit,
         emd.terms,
         emd.meta_number as customernumber,
-        emd.cc,
-        emd.bcc,
+        ece.contact as email,
+        ecc.contact as cc,
+        ecb.contact as bcc,
         emd.business_id,
         emd.language_code,
         emd.pricegroup_id,
@@ -954,6 +955,9 @@ CREATE VIEW customer AS
     FROM entity_credit_account emd 
     LEFT JOIN entity_bank_account eba on emd.entity_id = eba.entity_id
     Left join entity_note ein on ein.ref_key = emd.entity_id
+    LEFT JOIN eca_to_contact ece ON emd.id = ece.credit_id AND ece.contact_class_id = 12
+    LEFT JOIN eca_to_contact ecc ON emd.id = ecc.credit_id AND ecc.contact_class_id = 13
+    LEFT JOIN eca_to_contact ecb ON emd.id = ecb.credit_id AND ecb.contact_class_id = 14
     join company c on c.entity_id = emd.entity_id
     join entity e on c.entity_id = e.id
     where emd.entity_class = 2;