Added version of Aurynn's reconciliation logic

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1302 4979c152-3d1c-0410-bac9-87ea11338e46
author: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46> 2007-06-22 05:01:13 +0000
committer: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46> 2007-06-22 05:01:13 +0000
commit: 170174b76910a2916db1a9208597a428ebb9b40d (patch)
tree: ea09ceee935ec631d97786bd827cff9dcad2d2eb
parent: 426e5a7263770cee8138d44a8c66a257ed7665de (diff)
10 files changed, 374 insertions, 13 deletions
diff --git a/LedgerSMB/Reconciliation.pm b/LedgerSMB/Reconciliation.pm
index f12251e5..6dca96d2 100644
--- a/LedgerSMB/Reconciliation.pm
+++ b/LedgerSMB/Reconciliation.pm
@@ -65,6 +65,16 @@ arrayref of entries in the table for the given report_id.
 
 Returns undef in the event of no records found.
 
+=item get_corrections($self, $report_id, $entry_id)
+
+Returns the corrections made for the selected report and entry.
+Returns undef in the event of no corrections found.
+
+=item entry ($self,$report_id,$entry_id)
+
+Returns a single entry from the pending reports table, either cleared or
+uncleared.
+
 =back
 
 =head1 Copyright (C) 2007, The LedgerSMB core team.
@@ -155,4 +165,18 @@ sub get_report {
     return $self->report($self->{report_id});    
 }
 
+sub get_corrections {
+    
+    my $self = shift @_;
+    
+    return $self->corrections($self->{report_id},$self->{entry_id});
+}
+
+sub entry {
+    
+    my $self = shift @_;
+    
+    return $self->single_entry($self->{report_id},$self->{entry_id});
+}
+
 1;
 \ No newline at end of file
diff --git a/Reconciliation.pl b/Reconciliation.pl
new file mode 100644
index 00000000..76f60115
--- /dev/null
+++ b/Reconciliation.pl
@@ -0,0 +1,3 @@
+#!/usr/bin/perl
+
+require "lsmb-request.pl";
diff --git a/UI/approved.html b/UI/approved.html
new file mode 100644
index 00000000..16afc6cc
--- /dev/null
+++ b/UI/approved.html
@@ -0,0 +1,28 @@
+<div>
+	<center>
+		Bank statement for <?lsmbaccount?> on <?lsmbdate?> has been approved!
+	</center>
+</div>
+
+<table border=0 style="opacity:50%;">
+	
+	<tr>
+		<td>Clear date</td>
+		<td>Transaction Type</td>
+		<td>Our Balance</td>
+		<td>Their Balance</td>
+		<td>Error Corrections</td>
+		<td>Error Code</td>
+	</tr>
+	
+	<?lsmb FOREACH row = records ?>
+		<tr>
+			<td><?lsmb row.clear_time ?></td>
+			<td><?lsmb row.transaction_type ?> </td>
+			<td><?lsmb row.our_balance ?></td>
+			<td><?lsmb row.their_balance?></td>
+			<td><?lsmb row.corrections  ?></td>
+			<td><?lsmb row.errorcode ?></td>
+		</tr>
+	<?lsmb END ?>
+</table>
+\ No newline at end of file
diff --git a/UI/correct.html b/UI/correct.html
new file mode 100644
index 00000000..e9cb708a
--- /dev/null
+++ b/UI/correct.html
diff --git a/UI/corrections.html b/UI/corrections.html
new file mode 100644
index 00000000..bdf7424a
--- /dev/null
+++ b/UI/corrections.html
@@ -0,0 +1,39 @@
+<table border=0>
+	<tr>
+		<td>Clear date</td>
+		<td>Transaction Type</td>
+		<td>Our Balance</td>
+		<td>Their Balance</td>
+	</tr>
+	<tr>
+		<td><?lsmb entry.clear_time ?></td>
+		<td><?lsmb entry.transaction_type ?> </td>
+		<td><?lsmb entry.our_balance ?></td>
+		<td><?lsmb entry.their_balance?></td>
+	</tr>
+</table>
+
+<?lsmb IF NOT corrections ?>
+Corrections:
+<table border=0>	
+	<?lsmb FOREACH row = corrections ?>
+	<tr>
+		<td>
+			<div>
+				<span><?lsmbrow.user?> at <?lsmbrow.insert_time?></span>
+				<div>
+					<?lsmbrow.reason?>
+				</div>
+			</div>
+		</td>
+	</tr>
+	<?lsmbEND?>
+	
+</table>
+<?lsmbELSE?>
+
+<div>
+	No corrections found.
+</div>
+
+<?lsmbEND?>
+\ No newline at end of file
diff --git a/UI/get_password.html b/UI/get_password.html
new file mode 100644
index 00000000..4f3bfc9a
--- /dev/null
+++ b/UI/get_password.html
@@ -0,0 +1,45 @@
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
+		"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
+<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
+<head>
+	<title><?lsmb titlebar ?></title>
+	<meta http-equiv="Pragma" content="no-cache" />
+	<meta http-equiv="Expires" content="-1" />
+	<link rel="shortcut icon" href="favicon.ico" type="image/x-icon" />
+	<link rel="stylesheet" href="css/<?lsmb stylesheet ?>" type="text/css" />
+	<link rel="stylesheet" href="UI/employee.css" type="text/css" />
+
+	<meta http-equiv="content-type" 
+		content="text/html; charset=utf-8" />	
+	<meta name="robots" content="noindex,nofollow" />
+<script language="JavaScript" type="text/javascript">
+<!--
+function focus_input(){
+    document.getpassword.password.focus();
+}
+// End -->
+</script>
+</head>
+<body onload="focus_input()">
+<?lsmb IF sessionexpired ?>
+        <p><span class="warning_label">
+	<?lsmb text('Session expired!') ?>
+	</span></p>
+<?lsmb END ?>
+<form method="post" action="<?lsmb script ?>" name="getpassword">
+
+<div>
+    <span align=right class="label"> <?lsmb text('Password')  ?> </span>
+    <span class="input"><input type="password" name="password" size="30"/>
+    </span>
+    <span class="input"><button type="submit" value="continue">
+      <?lsmb text('Continue') ?>
+    </button></span>
+</div>
+
+<?lsmb FOREACH var = hidden ?>
+<input name="<?lsmb var.name ?>" type="hidden" value="<?lsmb var.value ?>" />
+</form>
+
+</body>
+</html>
diff --git a/UI/report.html b/UI/report.html
new file mode 100644
index 00000000..cd6c8972
--- /dev/null
+++ b/UI/report.html
@@ -0,0 +1,52 @@
+<center>Reconciliation Report for [% total.account %] for the month of [%total.month%]</center>
+
+<center>
+	[%IF total.errorcode != 0 %]
+	<div style="color:blue; border-style:solid; border-width:1px; border-color: blue;">
+	[%ELSE%]
+	<div style="color:red; border-style:solid; border-width:1px; border-color: blue;">
+	[%END%]
+		Our Balance: total.our_balance | Bank Balance: total.their_balance
+	</div>
+</center>
+
+<center>Report generated by [% total.user %]</center>
+
+[% if recon.error %]
+<div style="border-color:red; border-width:1px; border-style:solid; margin:3px;" >
+	[% recon.error %]	
+</div>
+[%end%]
+
+<table border=0>
+	
+	<tr>
+		<td>Clear date</td>
+		<td>Transaction Type</td>
+		<td>Our Balance</td>
+		<td>Their Balance</td>
+		<td>Error Corrections</td>
+		<td>Error Code</td>
+	</tr>
+	
+	[% FOREACH row = records %]
+		[%IF row.errorcode != 0 %]
+		<tr style="background-color:red;">
+		[% ELSIF row.id = corrected %]
+		<tr style="background-color:yellow;">		
+		[%ELSE%]
+		<tr>
+		[%END%]
+			<td>[% row.clear_time %]</td>
+			<td>[% row.transaction_type %] </td>
+			<td>[% row.our_balance %]</td>
+			<td>[% row.their_balance%]</td>
+			<td>[% row.corrections  %]</td>
+			[% IF row.errorcode > 0 %]
+			<td>[% row.errorcode %] <a href="/reconciliation.pl?corrections&amp;entry=[%row.entry_id%]">View Corrections</a> </td>		
+			[%ELSE%]
+			<td>0</td>
+			[%END%]
+		</tr>
+	[% END %]
+</table>
+\ No newline at end of file
diff --git a/UI/voucher.html b/UI/voucher.html
new file mode 100644
index 00000000..4cf6bf29
--- /dev/null
+++ b/UI/voucher.html
@@ -0,0 +1,35 @@
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
+		"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
+<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
+<head>
+	<title><?lsmb titlebar ?></title>
+	<meta http-equiv="Pragma" content="no-cache" />
+	<meta http-equiv="Expires" content="-1" />
+	<link rel="shortcut icon" href="favicon.ico" type="image/x-icon" />
+	<link rel="stylesheet" href="css/<?lsmb stylesheet ?>" type="text/css" />
+	<link rel="stylesheet" href="UI/employee.css" type="text/css" />
+
+	<meta http-equiv="content-type" 
+		content="text/html; charset=<?lsmb charset ?>" />	
+	<meta name="robots" content="noindex,nofollow" />
+</head>
+<body>
+<div id="title" class="pageheader"><span>GL Voucher Reports</span></div>
+<div id="filter">
+  <div id="date_from" class="header_entry">
+    <span class="label" id="date_from_label">Date From:</span>
+    <span class="field" id="date_from_field"><input name="date_from"></span>
+  </div>
+  <div id="date_to" class="header_entry">
+    <span class="label" id="date_to_label">Date To:</span>
+    <span class="field" id="date_to_field"><input name="date_to"></span>
+  </div>
+  <div id="include_date" class="header_entry">
+    <span class="label" id="include_date_label">And Date:</span>
+    <span class="field" id="include_date_field">
+      <input name="include_date">
+    </span>
+  </div>
+</body>
+</body>
+</html>
diff --git a/scripts/Reconciliation.pl b/scripts/Reconciliation.pl
index 189eecb4..8826f037 100644
--- a/scripts/Reconciliation.pl
+++ b/scripts/Reconciliation.pl
@@ -2,8 +2,21 @@
 
 =head1 NAME
 
+LedgerSMB::Scripts::Reconciliation - LedgerSMB class defining the Controller
+functions, template instantiation and rendering.
+
+=head1 SYOPSIS
+
+This module acts as the UI controller class for Reconciliation. It controls
+interfacing with the Core Logic and database layers.
+
+=head1 METHODS
+
 =cut
 
+# NOTE:  This is a first draft modification to use the current parameter type.
+# It will certainly need some fine tuning on my part.  Chris
+
 package LedgerSMB::Scripts::Reconciliation;
 
 use LedgerSMB::Template;
@@ -24,7 +37,7 @@ report_id.
 =cut
 
 sub display_report {
-    
+    my ($class, $request) = @_;
     my $recon = LedgerSMB::Employee->new(base => $request, copy => 'all'); 
     my $template = LedgerSMB::Template->new( user=>$user, 
         template => "reconciliation_report.html", language => $user->{language},
@@ -36,6 +49,7 @@ sub display_report {
 }
 
 sub search {
+    my ($class, $request) = @_;
     my $search = LedgerSMB::Employee->new(base => $request, copy => 'all');
     $employee->{search_results} = $employee->search();
     my $template = LedgerSMB::Template->new( user => $user, 
@@ -61,14 +75,38 @@ This is to prevent arbitrary editing of the database by unscrupulous users.
 =cut
 
 sub correct {
+    my ($class, $request) = @_;
     
     my $recon = LedgerSMB::DBObject::Reconciliation->new(base => $request, copy => 'all');
-     
-    my $template = LedgerSMB::Template->new( user => $user, 
-	template => 'reconciliation_correct.html', language => $user->{language}, 
-        format => 'html');
+    
     $recon->correct_entry();
-    $template->render($recon->get_report());
+     
+    if ($recon->{corrected_id}) {
+        
+        my $template = LedgerSMB::Template->new( user => $user, 
+    	template => 'reconciliation_report.html', language => $user->{language}, 
+            format => 'html');
+    
+        $template->render( { 
+            corrected=> $recon->{corrected_id}, 
+            report=> $recon->get_report(),
+            total=> $recon->get_total()
+        } );
+    } 
+    else {
+        
+        # indicate we were unable to correct this entry, with the error code 
+        # spat back to us by the DB.
+        my $template = LedgerSMB::Template->new( user => $user, 
+    	template => 'reconciliation_report.html', language => $user->{language}, 
+            format => 'html');
+    
+        $template->render( { 
+            recon => $recon,
+            report=> $recon->get_report(),
+            total=> $recon->get_total()
+        } );
+    }
 }
 
 =pod
@@ -88,6 +126,7 @@ it has been created.
 =cut
 
 sub new_report {
+    my ($class, $request) = @_;
     # how are we going to allow this to be created? Grr.
     # probably select a list of statements that are available to build 
     # reconciliation reports with.
@@ -116,7 +155,7 @@ sub new_report {
 
 =over
 
-=item ($self, $request, $user)
+=item approve ($self, $request, $user)
 
 Requires report_id
 
@@ -131,6 +170,12 @@ the uncorrected entries.
 =cut
 
 sub approve {
+    my ($class, $request) = @_;
+    
+    # Approve will also display the report in a blurred/opaqued out version,
+    # with the controls removed/disabled, so that we know that it has in fact
+    # been cleared. This will also provide for return-home links, auditing, 
+    # etc.
     
     my $recon = LedgerSMB::DBObject::Reconciliation->new(base => request, copy=> 'all');
     
@@ -148,11 +193,60 @@ sub approve {
     	template => 'reconciliation_report.html', language => $user->{language}, 
             format => 'html');
         $report = $recon->get_report();
+        
         ## relies on foreknowledge in the template
         ## we basically tell the template, we can't approve, this uncorrected
         ## error is preventing us.
+        
         $report->{ error } = { approval => 1 }; 
     }
     $template->render($report);
 }
-1;
-\ No newline at end of file
+
+=pod
+
+=over
+
+=item corrections ($self, $request, $user)
+
+Requires report_id and entry_id.
+
+Loads the selected entry id and all corrections associated with it. If there
+aren't any corrections, it will display "no corrections found".
+=back
+
+=cut
+
+sub corrections {
+    my ($class, $request) = @_;
+    
+    # Load the corrections for a given report & entry id.
+    # possibly should use a "micro" popup window?
+    
+    my $recon = LedgerSMB::DBObject::Reconciliation->new(base => request, copy=> 'all');
+    
+    my $template;
+        
+    $template = LedgerSMB::Template->new( user => $user, 
+	template => 'reconciliation_corrected.html', language => $user->{language}, 
+        format => 'html');
+    
+    return $template->render(
+        {
+            corrections=>$recon->get_corrections(), 
+            entry=>$recon->entry($self->{report_id}, $self->{entry_id})
+        }
+    );
+}
+
+1;
+
+=pod
+
+=head1 Copyright (C) 2007, The LedgerSMB core team.
+
+This file is licensed under the Gnu General Public License version 2, or at your
+option any later version.  A copy of the license should have been included with
+your software.
+
+=cut
diff --git a/sql/modules/Reconciliaton.sql b/sql/modules/Reconciliaton.sql
index 2700a2fb..1dfc7d7e 100644
--- a/sql/modules/Reconciliaton.sql
+++ b/sql/modules/Reconciliaton.sql
@@ -13,6 +13,15 @@ CREATE TABLE pending_reports (
     overlook boolean not null default 'f'
 );
 
+CREATE TABLE report_corrections (
+    id serial primary key not null,
+    correction_id int not null default 1,
+    entry references pending_reports(id) not null,
+    user text not null,
+    reason text not null,
+    insert_time timestamptz not null default now()
+);
+
 -- to correct OUR wrong amount.
 CREATE OR REPLACE FUNCTION reconciliation_correct_ledger (in_report_id INT, in_id int, in_new_amount NUMERIC, reason TEXT) returns INT AS $$
 
@@ -371,14 +380,46 @@ CREATE OR REPLACE FUNCTION reconciliation_get_total (in_report_id INT) returns s
         AND scn = -1;
         
         IF NOT FOUND THEN -- I think this is a fairly major error condition
-        
-            RAISE EXCEPTION "No Bank Total found."
-            
+            RAISE EXCEPTION "No Bank Total found.";
         ELSE
-            
             return row;
-        
         END IF;
     END;
 
 $$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION reconciliation_corrections (in_report_id INT, in_id INT) returns setof report_corrections AS $$
+
+    DECLARE
+        corr report_corrections;
+    BEGIN
+    
+        SELECT INTO corr FROM report_corrections WHERE report_id = in_report_id AND id = in_id LIMIT 1;
+        IF NOT FOUND THEN
+            RAISE EXCEPTION "No corrections for selected entry.";
+        ELSE
+            FOR corr IN select * from report_corrections WHERE report_id = in_report_id AND id = in_id LOOP
+                RETURN NEXT corr;
+            END LOOP;
+        END IF;
+    END;
+
+$$ language 'plplsql';
+
+CREATE OR REPLACE FUNCTION reconciliation_single_entry (in_report_id INT, in_id INT) returns setof pending_reports AS
+
+    DECLARE
+        row pending_reports;
+    BEGIN
+    
+        SELECT INTO row FROM pending_reports WHERE report_id = in_report_id and id = in_id LIMIT 1; 
+        -- if there's more than one, that's a Bad Thing
+        
+        IF NOT FOUND THEN
+            RAISE EXCEPTION "Could not find selected report entry";
+        ELSE
+            RETURN row;
+        END IF;
+    END;
+
+$$ language 'plpgsql';
+\ No newline at end of file
author	einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>	2007-06-22 05:01:13 +0000
committer	einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>	2007-06-22 05:01:13 +0000
commit	170174b76910a2916db1a9208597a428ebb9b40d (patch)
tree	ea09ceee935ec631d97786bd827cff9dcad2d2eb
parent	426e5a7263770cee8138d44a8c66a257ed7665de (diff)