[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Enhancement/Patch: Show payee in reconciliation

Subject: Enhancement/Patch: Show payee in reconciliation
From: John Locke <..hidden..>
Date: Sun, 19 Jun 2011 08:12:44 -0700

Hi,

Ok, this is still carving up a patch from yesterday, haven't merged in
the commits in the past ~18 hours or so, so let me know if you need this
re-rolled.

It also is probably not the best approach -- probably better to alter
reconciliation__report_details, but this was one of my first forays into
the code base (several months ago) so I simply created a new view and
duplicated that stored function to return rows from it instead of
cr_report_line.

The point is, it's tough to reconcile a bunch of transactions with
similar amounts if you don't know who was on the other side of the
transaction. This patch adds a "payee" column to the reconciliation
report so you can see that info when reconciling a cash account.

There's also a bug fix for getting the right username attached to the
report.

And... a question. How do I export the definition for a single function
or view in PSQL? I can easily get the body, but I'm wondering how to
grab the header stuff too without having to enter it manually?

Cheers,
John Locke
http://freelock.com

diff --git LedgerSMB/DBObject/Reconciliation.pm LedgerSMB/DBObject/Reconciliation.pm
index cc90e8d..8ef7dbf 100644
--- LedgerSMB/DBObject/Reconciliation.pm
+++ LedgerSMB/DBObject/Reconciliation.pm
@@ -326,7 +326,7 @@ sub get {
         $self->{dbh}->commit;
     }
     @{$self->{report_lines}} = $self->exec_method(
-		funcname=>'reconciliation__report_details'
+		funcname=>'reconciliation__report_details_payee'
     );
     ($ref) = $self->exec_method(funcname=>'account_get', 
                                 args => [$self->{chart_id}]);
diff --git UI/reconciliation/report.html UI/reconciliation/report.html
index 4d49849..636369e 100644
--- UI/reconciliation/report.html
+++ UI/reconciliation/report.html
@@ -60,7 +60,7 @@
 <?lsmb i = 1 ?>
 <table border=0 id="cleared-table">
 	<tr class="listtop">
-		<th colspan=9><?lsmb text('Cleared Transactions') ?></th>
+		<th colspan="10"><?lsmb text('Cleared Transactions') ?></th>
 	</tr>
 	<tr class="listheading">
 		<th><?lsmb text('Cleared') ?></th>
@@ -68,6 +68,7 @@
 		<th><?lsmb text('Clear date') ?></th>
 		<th><?lsmb text('Source') ?></th>
 		<th><?lsmb text('Posted Date') ?></th>
+		<th><?lsmb text('Payee') ?></th>
 		<th><?lsmb text('Our Debits') ?></th>
 		<th><?lsmb text('Our Credits') ?></th>
 		<th><?lsmb text('Their Debits') ?></th>
@@ -97,6 +98,7 @@
 			<td><?lsmb row.clear_time ?></td>
 			<td><?lsmb row.scn ?> </td>
 			<td><?lsmb row.post_date ?></td>
+			<td><?lsmb row.payee ?></td>
 			<td><?lsmb row.our_debits ?></td>
 			<td><?lsmb row.our_credits ?></td>
 			<td><?lsmb row.their_debits ?></td>
@@ -113,7 +115,7 @@
 	</table>
 <table border=0 id="error-table">
 	<tr class="listtop">
-		<th colspan=9><?lsmb text('Mismatched Transactions (From Upload)') ?></th>
+		<th colspan="10"><?lsmb text('Mismatched Transactions (From Upload)') ?></th>
 	</tr>
 	<tr class="listheading">
 		<th><?lsmb text('Cleared') ?></th>
@@ -121,6 +123,7 @@
 		<th><?lsmb text('Clear date') ?></th>
 		<th><?lsmb text('Source') ?></th>
 		<th><?lsmb text('Posted Date') ?></th>
+		<th><?lsmb text('Payee') ?></th>
 		<th><?lsmb text('Our Debits') ?></th>
 		<th><?lsmb text('Our Credits') ?></th>
 		<th><?lsmb text('Their Debits') ?></th>
@@ -146,6 +149,7 @@
 			<td><?lsmb row.clear_time ?></td>
 			<td><?lsmb row.scn ?> </td>
 			<td><?lsmb row.post_date ?></td>
+			<td><?lsmb row.payee ?></td>
 			<td><?lsmb row.our_debits ?></td>
 			<td><?lsmb row.our_credits ?></td>
 			<td><?lsmb row.their_debits ?></td>
@@ -164,12 +168,13 @@
 	</table>
 	<table id="outstanding-table">
 	<tr class="listtop">
-		<th colspan=5><?lsmb text('Outstanding Transactions') ?></th>
+		<th colspan="6"><?lsmb text('Outstanding Transactions') ?></th>
 	</tr>
 	<tr class="listheading">
 		<th><?lsmb text('Cleared') ?></th>
 		<th><?lsmb text('Source') ?></th>
 		<th><?lsmb text('Posted Date') ?></th>
+		<th><?lsmb text('Payee') ?></th>
 		<th><?lsmb text('Our Debits') ?></th>
 		<th><?lsmb text('Our Credits') ?></th>
 	</tr>
@@ -190,6 +195,7 @@
 			?></td>
 			<td><?lsmb row.scn ?> </td>
 			<td><?lsmb row.post_date ?></td>
+			<td><?lsmb row.payee ?></td>
 			<td><?lsmb row.our_debits ?></td>
 			<td><?lsmb row.our_credits ?></td>
 		</tr>
diff --git scripts/recon.pl scripts/recon.pl
index 80ed7a0..59d44a6 100644
--- scripts/recon.pl
+++ scripts/recon.pl
@@ -132,7 +132,7 @@ sub get_results {
         my @accounts = $search->get_accounts();
         my $act_hash = {};
         for my $act (@accounts){
-            $act_hash->{"$act->{id}"} = $act->{account};
+            $act_hash->{"$act->{id}"} = $act->{name};
         }
         for my $row (@results){
             $row->{account} = $act_hash->{"$row->{chart_id}"};
@@ -336,7 +336,7 @@ sub _display_report {
         $recon->close_form;
         $recon->open_form({commit => 1});
         $recon->add_entries($recon->import_file('csv_file')) if !$recon->{submitted};
-        $recon->{can_approve} = $recon->is_allowed_role({allowed_roles => ['recon_supervisor']});
+        $recon->{can_approve} = $recon->is_allowed_role({allowed_roles => ['reconciliation_approve']});
         $recon->get();
         $template = LedgerSMB::Template->new( 
             user=> $user,
diff --git UI/users/preferences.html UI/users/preferences.html
index 904e1b3..0bc070f 100644
--- UI/users/preferences.html
+++ UI/users/preferences.html
@@ -69,7 +69,7 @@
 	      <tr>
 		<th align="right"><?lsmb text('Stylesheet') ?></th>
 		<td><?lsmb PROCESS select element_data={
-                      name = 'css'
+                      name = 'stylesheet'
                       options = cssfiles
                       default_values = [stylesheet]
                       text_attr = 'file'




diff --git sql/modules/Reconciliation.sql sql/modules/Reconciliation.sql
index 697489d..cd229f8 100644
--- sql/modules/Reconciliation.sql
+++ sql/modules/Reconciliation.sql
@@ -86,7 +86,7 @@ $$
            FROM cr_report 
           WHERE id = in_report_id 
             AND submitted = TRUE
-            AND entered_by = people__get_my_entity_id();
+            AND entered_by = person__get_my_entity_id();
         
         IF FOUND THEN
             -- Creators cannot delete their own reports if they've been submitted.
@@ -95,7 +95,7 @@ $$
         
         UPDATE cr_report
            SET deleted = TRUE,
-               deleted_by = people__get_my_entity_id()
+               deleted_by = person__get_my_entity_id()
          WHERE id = in_report_id;
          
         return TRUE;

CREATE OR REPLACE VIEW recon_payee AS
 SELECT n.name AS payee, rr.id, rr.report_id, rr.scn, rr.their_balance, rr.our_balance, rr.errorcode, rr."user", rr.clear_time, rr.insert_time, rr.trans_type, rr.post_date, rr.ledger_id, rr.voucher_id, rr.overlook, rr.cleared
   FROM cr_report_line rr
   LEFT JOIN acc_trans ac ON rr.ledger_id = ac.entry_id
   LEFT JOIN gl ON ac.trans_id = gl.id
   LEFT JOIN (( SELECT ap.id, e.name
   FROM ap
   JOIN entity_credit_account eca ON ap.entity_credit_account = eca.id
   JOIN entity e ON eca.entity_id = e.id
UNION 
 SELECT ar.id, e.name
   FROM ar
   JOIN entity_credit_account eca ON ar.entity_credit_account = eca.id
   JOIN entity e ON eca.entity_id = e.id)
UNION 
 SELECT gl.id, gl.description
   FROM gl) n ON n.id = ac.trans_id;


CREATE OR REPLACE FUNCTION reconciliation__report_details_payee (in_report_id INT) RETURNS setof recon_payee as $$
   DECLARE
        row recon_payee;
    BEGIN    
        FOR row IN 
        	select * from recon_payee where report_id = in_report_id 
        	order by scn, post_date
        LOOP
          RETURN NEXT row;
        END LOOP;    
    END;
$$ language 'plpgsql';

Follow-Ups:
- Re: Enhancement/Patch: Show payee in reconciliation
  - From: Chris Travers

Prev by Date: Re: Tax account creation: how to 'detect' tax accounts?
Next by Date: Bug/Patch: email form not populated with emails from account
Previous by thread: Tax account creation: how to 'detect' tax accounts?
Next by thread: Re: Enhancement/Patch: Show payee in reconciliation
Index(es):
- Date
- Thread