Adding the reconciliation SQL stuff. Testing my SVN interface.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2177 4979c152-3d1c-0410-bac9-87ea11338e46

1 files changed, 178 insertions, 114 deletions

diff --git a/sql/modules/Reconciliaton.sql b/sql/modules/Reconciliaton.sql
index ddb03230..5bc88c0e 100644
--- a/sql/modules/Reconciliaton.sql
+++ b/sql/modules/Reconciliaton.sql
@@ -1,6 +1,31 @@
-BEGIN;
+CREATE TABLE reports (
+    id bigserial primary key not null,
+    report_id int NOT NULL,
+    scn int, -- What is SCN?
+    their_balance numeric,
+    our_balance numeric,
+    errorcode INT,
+    user int references entity(id) not null, -- why ois this not an entity reference?
+    corrections INT NOT NULL DEFAULT 0
+    clear_time TIMESTAMP NOT NULL,
+    insert_time TIMESTAMPTZ NOT NULL DEFAULT now(),
+    ledger_id int REFERENCES acc_trans(entry_id),
+    overlook boolean not null default 'f',
+    approved boolean not null default 'f'
+);
+
+
+CREATE TABLE report_corrections (
+    id serial primary key not null,
+    correction_id int not null default 1,
+    entry references reports(id) not null,
+    user references entity(id) not null, -- why is this not an entity reference?
+    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 $$
+CREATE OR REPLACE FUNCTION reconciliation__correct_ledger (in_report_id INT, in_id int, in_new_amount NUMERIC, reason TEXT) returns INT AS $$
 
     DECLARE
         new_code INT;
@@ -9,36 +34,30 @@ CREATE OR REPLACE FUNCTION reconciliation_correct_ledger (in_report_id INT, in_i
         in_user TEXT;
         full_reason TEXT;
     BEGIN
-        select session_user into in_user; -- safer than current_user
+        select into in_user from current_user;
         
-        select * into current_row from pending_reports 
-	where pending_reports.id = in_report_id and pending_reports.id = in_id;
-        select * into l_row from acc_trans where entry_id = current_row.lid;
+        select into current_row from reports where reports.id = in_report_id and reports.id = in_id;
+        select into l_row from acc_trans where entry_id = current_row.lid;
         
         IF NOT FOUND THEN
             RAISE EXCEPTION 'No such id % in this report.', in_scn;
         END IF;
         
-        IF in_user <> current_row.entered_by THEN
+        IF user <> current_row.user THEN
         
             IF current_row.our_balance <> in_new_amount AND in_new_amount = current_row.their_balance THEN
-                update pending_reports
-                set corrections = corrections + 1, 
-                new_balance = in_new_amount,
+                update reports pr
+                set pr.corrections = reports.corrections + 1, 
+                pr.new_balance = in_new_amount,
                 error_code = 0
                 where id = in_report_id and scn = in_scn;
                 return 0;
                 
                 -- After that, it is required to update the general ledger.
-                full_reason := 'User % is filing a reconciliation correction ' 
-			|| 'on the general ledger, changing amount % to ' ||
-			'amount %.\n' ||
-                	'Their reason given is: %', 
-			in_user, current_row.our_balance, in_new_amount, reason;
-
-                select update_ledger(current_row.lid, in_new_amount, 
-			full_reason);
-            ELSIF current_row.our_balance = in_new_amount THEN
+                full_reason := "User % is filing a reconciliation correction on the general ledger, changing amount % to amount %. 
+                Their reason given is: %", in_user, current_row.our_balance, in_new_amount, reason;
+                select update_ledger(current_row.lid, in_new_amount, full_reason)
+            ELSE IF current_row.our_balance = in_new_amount THEN
                 -- This should be something, does it equal the original 
                 -- balance? If so, there's no change.
                 return current_row.error_code;
@@ -51,33 +70,30 @@ CREATE OR REPLACE FUNCTION reconciliation_correct_ledger (in_report_id INT, in_i
 $$ language 'plpgsql';
 
 -- to correct an incorrect bank statement value.
-CREATE OR REPLACE FUNCTION reconciliation_correct_bank_statement (in_report_id INT, in_id int, in_new_amount NUMERIC) returns INT AS $$
+CREATE OR REPLACE FUNCTION reconciliation__correct_bank_statement (in_report_id INT, in_id int, in_new_amount NUMERIC) returns INT AS $$
 
     DECLARE
         new_code INT;
         current_row RECORD;
         in_user TEXT;
     BEGIN
-        select SESSION_USER into in_user;
-        select * into current_row from pending_reports 
-	where pending_reports.id = in_report_id 
-		and pending_reports.scn = in_scn;
-
+        select into in_user from current_user;
+        select into current_row from reports where reports.id = in_report_id and reports.scn = in_scn;
         IF NOT FOUND THEN
             RAISE EXCEPTION 'No such SCN % in this report.', in_scn;
         END IF;
         
-        IF in_user <> current_row.entered_by THEN
+        IF user <> current_row.user THEN
         
             IF current_row.their_balance <> in_new_amount AND in_new_amount = current_row.our_balance THEN
-                update pending_reports
-                set corrections = corrections + 1, 
-                new_balance = in_new_amount,
+                update reports pr
+                set pr.corrections = reports.corrections + 1, 
+                pr.new_balance = in_new_amount,
                 error_code = 0
                 where id = in_report_id and scn = in_scn;
                 return 0;
             
-            ELSIF current_row.their_balance = in_new_amount THEN
+            ELSE IF current_row.their_balance = in_new_amount THEN
                 -- This should be something, does it equal the original 
                 -- balance? If so, there's no change.
                 return current_row.error_code;         
@@ -90,97 +106,88 @@ CREATE OR REPLACE FUNCTION reconciliation_correct_bank_statement (in_report_id I
 $$ language 'plpgsql';
 
 
-CREATE OR REPLACE FUNCTION reconciliation_correct_passthrough ( in_report_id int, in_id int ) returns INT AS $$
+CREATE OR REPLACE reconciliation__correct_passthrough ( in_report_id int, in_id int ) returns INT AS $$
 
     DECLARE
         in_user TEXT;
-        pending_entry pending_reports;
+        pending_entry reports;
     BEGIN
-        select SESSION_USER into in_user;
+        select into in_user from current_user;
         
-        select * into pending_entry from pending_reports 
-	where report_id = in_report_id and id = in_id;
+        select into pending_entry from reports where report_id = in_report_id and id = in_id;
         
         IF NOT FOUND THEN
             -- Raise an exception.
-            RAISE EXCEPTION 'Cannot find entry.';
-        ELSIF pending_entry.errorcode <> 4 THEN 
+            RAISE EXCEPTION "Cannot find entry.";
+        ELSE IF pending_entry.errorcode <> 4 THEN 
             -- Only error codes of 4 may be "passed through" safely.
-            RAISE EXCEPTION 'Selected entry not permitted to be passed through.';
+            RAISE EXCEPTION "Selected entry not permitted to be passed through.";
             
         ELSE
             -- Then we mark it passthroughable, and "approve" will overlook it.
-            update pending_reports set overlook = 't', errorcode = 0 where report_id = in_report_id and id = in_id;
+            update reports set overlook = 't', errorcode = 0 where report_id = in_report_id and id = in_id;
             return 0;
         END IF;
     END;
 
 $$ language 'plpgsql';
 
-CREATE OR REPLACE FUNCTION reconciliation_correct_bank_charge (in_report_id int, in_id int) returns INT AS $$
+CREATE OR REPLACE FUNCTION reconciliation__correct_bank_charge (in_report_id int, in_id int) returns INT AS $$
 
     DECLARE
         in_user TEXT;
-        pending_entry pending_reports;
+        pending_entry reports;
     BEGIN
     
-        select * into pending_entry from pending_reports 
-	where report_id = in_report_id and id = in_id;
-    
         IF NOT FOUND THEN
              -- Raise an exception.
-             RAISE EXCEPTION 'Cannot find entry with ID % in report %.', in_id, in_report_id;
-         ELSIF pending_entry.errorcode <> 2 THEN 
-             -- Only error codes of 4 may be "passed through" safely.
-             RAISE EXCEPTION 'Attempt to retroactively add a non-bank-charge entry to the ledger.';
+             RAISE EXCEPTION "Cannot find entry with ID % in report %.", in_id, in_report_id;
+         ELSE IF pending_entry.errorcode <> 2 THEN 
+             -- Only error codes of 2 may be "passed through" safely.
+             RAISE EXCEPTION "Attempt to retroactively add a non-bank-charge entry to the ledger.";
          
          ELSE
              -- Then we mark it passthroughable, and "approve" will overlook it.
              
-             select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_time, 'Bank charge');
+             select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_date, 'Bank charge');
              
-             update pending_reports set errorcode = 0 where report_id = in_report_id and id = in_id;
+             update reports set errorcode = 0 where report_id = in_report_id and id = in_id;
              return 0;
          END IF;
     END;
 
 $$ LANGUAGE 'plpgsql';
 
-CREATE OR REPLACE FUNCTION reconciliation_correct_unaccounted_charge (in_report_id int, in_id int, reason TEXT) RETURNS INT AS $$
+CREATE OR REPLACE FUNCTION reconciliation__correct_unaccounted_charge (in_report_id int, in_id int, reason TEXT) RETURNS INT AS $$
 
     DECLARE
         in_user TEXT;
-        pending_entry pending_reports;
+        pending_entry reports;
         note TEXT;
     BEGIN
     
-        select * into pending_entry from pending_reports 
-	where report_id = in_report_id and id = in_id;
-    
         IF NOT FOUND THEN
              -- Raise an exception.
-             RAISE EXCEPTION 'Cannot find entry with ID % in report %.', in_id, in_report_id;
-         ELSIF pending_entry.errorcode <> 3 THEN 
-             -- Only error codes of 4 may be "passed through" safely.
-             RAISE EXCEPTION 'Not an unaccounted charge; cannot be retroactively added to the ledger.';
+             RAISE EXCEPTION "Cannot find entry with ID % in report %.", in_id, in_report_id;
+         ELSE IF pending_entry.errorcode <> 3 THEN 
+             -- Only error codes of 3 may be "passed through" safely.
+             RAISE EXCEPTION "Not an unaccounted charge; cannot be retroactively added to the ledger.";
          
          ELSE
              -- Then we mark it passthroughable, and "approve" will overlook it.
              
-             note := 'Retroactive addition of an unaccounted entry, of value '
-		|| pending_entry || '. \n' ||
-		'Being added by user ' || in_user || ' with the following ' ||
-		'explanation: ' || in_reason;
+             note := 'Retroactive addition of an unaccounted entry, of value %. 
+             Being added by user % with the following explanation: %', pending_entry.their_balance, in_user, in_reason;
              
-             select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_time,note);
+             select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_date,note);
              
-             update pending_reports set errorcode = 0 where report_id = in_report_id and id = in_id;
-             return 0;
+             update reports set errorcode = 0 where report_id = in_report_id and id = in_id;
+             return in_id;
          END IF;
     END;
 $$ language 'plpgsql';
 
-CREATE OR REPLACE FUNCTION reconciliation_report_approve (in_report_id INT) returns INT as $$
+CREATE OR REPLACE FUNCTION reconciliation__report_approve (in_report_id INT) returns INT as $$
     
     -- Does some basic checks before allowing the approval to go through; 
     -- moves the approval to "reports", I guess, or some other "final" table.
@@ -190,25 +197,25 @@ CREATE OR REPLACE FUNCTION reconciliation_report_approve (in_report_id INT) retu
     
     DECLARE
         current_row RECORD;
-        completed pending_reports;
+        completed reports;
         total_errors INT;
         in_user TEXT;
     BEGIN
         
-        select SESSION_USER into in_user;
-        select * into current_row from pending_reports where report_id = in_report_id;
+        select into in_user current_user;
+        select into current_row distinct on user * from reports where report_id = in_report_id;
         IF NOT FOUND THEN
-            RAISE EXCEPTION 'Fatal Error: Pending report % not found', in_report_id;
+            RAISE EXCEPTION "Fatal Error: Pending report % not found", in_report_id;
         END IF;
         
-        IF current_row.entered_by = in_user THEN
-            RAISE EXCEPTION 'Fatal Error: User % cannot self-approve report!', in_user;
+        IF current_row.user = in_user THEN
+            RAISE EXCEPTION "Fatal Error: User % cannot self-approve report!", in_user;
         END IF;
         
-        SELECT INTO total_errors count(*) from pending_reports where report_id = in_report_id and error_code <> 0;
+        SELECT INTO total_errors count(*) from reports where report_id = in_report_id and error_code <> 0;
         
         IF total_errors <> 0 THEN
-            RAISE EXCEPTION 'Fatal Error: Cannot approve while % uncorrected errors remain.', total_errors;
+            RAISE EXCEPTION "Fatal Error: Cannot approve while % uncorrected errors remain.", total_errors;
         END IF;
         
         -- so far, so good. Different user, and no errors remain. Therefore, we can move it to completed reports.
@@ -217,34 +224,38 @@ CREATE OR REPLACE FUNCTION reconciliation_report_approve (in_report_id INT) retu
         -- in_user, to note who approved the report, than the user who
         -- filed it. This may require clunkier syntax..
         
-        insert into reports 
-		(report_id, scn, their_balance, our_balance, coentered_by, 
-			correction )
-        SELECT report_id, scn, their_balance, our_balance, code, entered_by, 
-		corrections 
-            FROM pending_reports 
-            WHERE report_id = in_report_id;
+        -- 
+        
+        update reports set approved = 't', clear_time = now() where report_id = in_report_id;
         
         return 1;        
     END;
 
 $$ language 'plpgsql';
 
-CREATE OR REPLACE FUNCTION reconciliation_new_report_id () returns BIGINT as $$
+CREATE OR REPLACE FUNCTION reconciliation__new_report_id () returns INT as $$
 
-    SELECT nextval('pending_reports_id_seq');
+    SELECT nextval('pending_report_report_id_seq');
 
 $$ language 'sql';
 
-create or replace function reconciliation_add_entry(in_report_id INT, in_scn INT, in_amount INT, in_account INT, in_user TEXT, in_date TIMESTAMP) RETURNS INT AS $$
-    DECLARE
+create or replace function reconciliation__add_entry(
+    in_report_id INT, 
+    in_scn INT, 
+    in_amount INT, 
+    in_account INT, 
+    in_user TEXT, 
+    in_date TIMESTAMP
+) RETURNS INT AS $$
+    
+    DELCARE
         la RECORD;
         errorcode INT;
         our_value NUMERIC;
         lid INT;
     BEGIN
     
-        SELECT * INTO la FROM acc_trans gl 
+        SELECT INTO la FROM acc_trans gl 
         WHERE gl.source = in_scn 
         and gl.account = in_account 
         and gl.amount = in_amount;
@@ -268,7 +279,7 @@ create or replace function reconciliation_add_entry(in_report_id INT, in_scn INT
                 our_value := 0;
             END IF;
             
-        ELSif la.amount <> in_amount THEN
+        ELSE if la.amount <> in_amount THEN
         
             errorcode := 1;
             our_value := la.amount;
@@ -283,13 +294,13 @@ create or replace function reconciliation_add_entry(in_report_id INT, in_scn INT
             
         END IF;
         
-        INSERT INTO pending_reports (
+        INSERT INTO reports (
                 report_id,
                 scn,
                 their_balance,
                 our_balance,
                 error_code, 
-                entered_by,
+                user,
                 clear_time,
                 ledger_id
             ) 
@@ -297,7 +308,7 @@ create or replace function reconciliation_add_entry(in_report_id INT, in_scn INT
                 in_report_id,
                 in_scn,
                 in_amount,
-                gl.balance,
+                gl.amount,
                 errorcode,
                 in_user,
                 in_date,
@@ -312,26 +323,26 @@ create or replace function reconciliation_add_entry(in_report_id INT, in_scn INT
     END;    
 $$ language 'plpgsql';
 
-create or replace function reconciliation_pending_transactions (in_report_id INT, in_month TIMESTAMP, in_user INT) RETURNS setof int as $$
+create or replace function reconciliation__pending_transactions (in_report_id INT, in_month TIMESTAMP, in_user INT) RETURNS setof int as $$
     
     DECLARE
         gl_row acc_trans;
     BEGIN
     
         FOR gl_row IN
-            select gl.* from acc_trans gl, pending_reports pr 
+            select gl.* from acc_trans gl, reports pr 
             where gl.cleared = 'f' 
             and date_trunc('month',gl.transdate) <= date_trunc('month', in_month)
             and gl.entry_id <> pr.lid
         LOOP
         
-            INSERT INTO pending_reports (
+            INSERT INTO reports (
                 report_id,
                 scn,
                 their_balance,
                 our_balance,
                 error_code, 
-                entered_by,
+                user,
                 clear_time,
                 ledger_id
             ) 
@@ -351,12 +362,12 @@ create or replace function reconciliation_pending_transactions (in_report_id INT
     
 $$ language plpgsql;
 
-CREATE OR REPLACE FUNCTION reconciliation_report (in_report_id INT) RETURNS setof pending_reports as $$
+CREATE OR REPLACE FUNCTION reconciliation__report (in_report_id INT) RETURNS setof reports as $$
 
     DECLARE
-        row pending_reports;
+        row reports;
     BEGIN    
-        FOR row IN select * from pending_reports where report_id = in_report_id LOOP
+        FOR row IN select * from reports where report_id = in_report_id LOOP
         
             RETURN NEXT row;
         
@@ -365,19 +376,19 @@ CREATE OR REPLACE FUNCTION reconciliation_report (in_report_id INT) RETURNS seto
 
 $$ language 'plpgsql';
 
-CREATE OR REPLACE FUNCTION reconciliation_get_total (in_report_id INT) returns pending_reports AS $$
+CREATE OR REPLACE FUNCTION reconciliation__get_total (in_report_id INT) returns setof reports AS $$
 
     DECLARE
-        row pending_reports;
+        row reports;
     BEGIN
     
-        SELECT * INTO row FROM pending_reports 
+        SELECT INTO row FROM reports 
         WHERE ledger_id IS NULL 
         and report_id = in_report_id 
         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;
@@ -385,15 +396,15 @@ CREATE OR REPLACE FUNCTION reconciliation_get_total (in_report_id INT) returns p
 
 $$ language 'plpgsql';
 
-CREATE OR REPLACE FUNCTION reconciliation_corrections (in_report_id INT, in_id INT) returns setof report_corrections AS $$
+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;
+        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.';
+            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;
@@ -401,25 +412,78 @@ CREATE OR REPLACE FUNCTION reconciliation_corrections (in_report_id INT, in_id I
         END IF;
     END;
 
-$$ language 'plpgsql';
+$$ language 'plplsql';
+
+CREATE OR REPLACE FUNCTION reconciliation__single_entry (in_report_id INT, in_id INT) returns setof reports AS $$
 
-CREATE OR REPLACE FUNCTION reconciliation_single_entry 
-(in_report_id INT, in_id INT) 
-returns pending_reports AS
-$$
     DECLARE
-        row pending_reports;
+        row reports;
     BEGIN
     
-        SELECT * INTO row FROM pending_reports WHERE report_id = in_report_id and id = in_id LIMIT 1; 
+        SELECT INTO row FROM 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';
+            RAISE EXCEPTION "Could not find selected report entry";
         ELSE
             RETURN row;
         END IF;
     END;
 
 $$ language 'plpgsql';
-COMMIT;
+
+CREATE OR REPLACE FUNCTION reconciliation__search (
+    in_date_begin DATE, 
+    in_date_end DATE, 
+    in_account TEXT,
+    in_status BOOLEAN
+) RETURNS setof reports AS $$
+
+    DECLARE
+        row reports;
+        statement text;
+        where_stmt text;
+    BEGIN
+        IF in_date_begin IS NOT NULL
+            or in_date_end IS NOT NULL
+            or in_account IS NOT NULL
+            or in_status IS NOT NULL
+        THEN
+            statement = "select pr.* from reports pr ";
+            statement = statement + "join acc_trans at on pr.ledger_id = at.entry_id ";
+            
+            IF in_account IS NOT NULL THEN
+                
+                statement = statement + "join chart c on at.chart_id = c.id ";
+                where_stmt = "c.accno =~ " + quote_literal(in_account) + " AND ";
+            END IF;
+            
+            IF in_date_begin IS NOT NULL THEN
+                where_stmt = where_stmt + "insert_time >= " + quote_literal(in_date_begin) + " AND ";
+            END IF;
+            
+            IF in_date_end IS NOT NULL THEN
+                where_stmt = where_stmt + "insert_time <= " + quote_literal(in_date_end) + " AND ";
+            END IF;
+            
+            IF in_status IS NOT NULL THEN
+                
+                if in_status == 't'::bool THEN
+                    where_stmt = where_stmt + " approved = 't'::bool AND ";
+                ELSIF in_status == 'f'::bool THEN
+                    where_stmt = where_stmt + " approved = 'f'::bool AND ";
+                END IF;
+            
+            END IF;
+            
+            FOR row in EXECUTE statement LOOP
+                RETURN NEXT row;
+            END LOOP;
+        ELSE
+        
+            FOR row IN SELECT * FROM reports LOOP
+                RETURN NEXT row;
+            END LOOP;
+        
+        END IF;
+    END;
\ No newline at end of file