Copious Admin and Reconciliation changes.

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

2 files changed, 65 insertions, 15 deletions

diff --git a/sql/modules/Reconciliaton.sql b/sql/modules/Reconciliaton.sql
index b8aa2d97..045d5e93 100644
--- a/sql/modules/Reconciliaton.sql
+++ b/sql/modules/Reconciliaton.sql
@@ -2,7 +2,7 @@ CREATE TABLE reports (
     id bigserial primary key not null,
     report_id int NOT NULL,
     account text not null,
-    scn text not null, -- What is SCN?
+    scn text not null, -- SCN is the check #
     their_balance numeric,
     our_balance numeric,
     errorcode INT,
@@ -15,6 +15,11 @@ CREATE TABLE reports (
     approved boolean not null default 'f'
 );
 
+CREATE TABLE coa_to_account (
+    chart_id int not null references chart(id),
+    account text not null CHECK (account ~ '[0-9]{7}(xxx)')
+);
+
 
 CREATE TABLE report_corrections (
     id serial primary key not null,
@@ -57,7 +62,7 @@ CREATE OR REPLACE FUNCTION reconciliation__correct_ledger (in_report_id INT, in_
                 -- 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 %. 
                 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)
+                perform select reconciliation__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.
@@ -243,7 +248,7 @@ $$ language 'sql';
 create or replace function reconciliation__add_entry(
     in_report_id INT, 
     in_scn INT, 
-    in_amount INT, 
+    in_amount numeric, 
     in_account INT, 
     in_user TEXT, 
     in_date TIMESTAMP
@@ -257,9 +262,13 @@ create or replace function reconciliation__add_entry(
     BEGIN
     
         SELECT INTO la FROM acc_trans gl 
-        WHERE gl.source = in_scn 
-        and gl.account = in_account 
-        and gl.amount = in_amount;
+        JOIN chart c on gl.chart_id = c.id
+        JOIN ap ON gl.trans_id = ap.id
+        JOIN coa_to_account cta on cta.chart_id = gl.chart_id
+        WHERE gl.source ~ in_scn -- does it look like it?
+        and cta.account = in_account 
+        and gl.amount = in_amount
+        AND gl.transdate = in_date;
         
         lid := NULL;
         IF NOT FOUND THEN
@@ -414,18 +423,28 @@ CREATE OR REPLACE FUNCTION reconciliation__search (
     in_date_begin DATE, 
     in_date_end DATE, 
     in_account TEXT,
-    in_status BOOLEAN
+    in_status TEXT
 ) RETURNS setof reports AS $$
 
     DECLARE
         row reports;
         statement text;
         where_stmt text;
+        v_status BOOLEAN;
+        v_accum NUMERIC;
     BEGIN
+        
+        if in_status = "pending" then
+            v_status = 'ft'::bool;
+        ELSIF in_status = "approved" THEN
+        
+            v_status = 't'::bool;
+        END IF;
+        
         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
+            or v_status IS NOT NULL
         THEN
             statement = "select pr.* from reports pr ";
             statement = statement + "join acc_trans at on pr.ledger_id = at.entry_id ";
@@ -446,9 +465,9 @@ CREATE OR REPLACE FUNCTION reconciliation__search (
             
             IF in_status IS NOT NULL THEN
                 
-                if in_status == 't'::bool THEN
+                if v_status == 't'::bool THEN
                     where_stmt = where_stmt + " approved = 't'::bool AND ";
-                ELSIF in_status == 'f'::bool THEN
+                ELSIF v_status == 'f'::bool THEN
                     where_stmt = where_stmt + " approved = 'f'::bool AND ";
                 END IF;
             
@@ -464,4 +483,18 @@ CREATE OR REPLACE FUNCTION reconciliation__search (
             END LOOP;
         
         END IF;
-    END;
\ No newline at end of file
+    END;
+$$ language 'plpgsql';
+
+create type recon_accounts as (
+    name text,
+    id int
+);
+
+create or replace function reconciliation__get_accounts () returns setof recon_accounts as $$
+    SELECT 
+        coa.accno || ' ' || coa.description as name,
+        coa.id as id
+    FROM chart coa, coa_to_account cta
+    WHERE cta.chart_id = coa.id;
+$$ language sql;
\ No newline at end of file
diff --git a/sql/modules/admin.sql b/sql/modules/admin.sql
index 11c2e5b2..e2023a5f 100644
--- a/sql/modules/admin.sql
+++ b/sql/modules/admin.sql
@@ -255,7 +255,7 @@ CREATE OR REPLACE FUNCTION admin__save_user(
             
             --- The entity is expected to already BE created. See admin.pm.
             
-            if admin_is_user(in_username) then
+            if admin__is_user(in_username) then
                 
                 -- uhm, this is bad.
                 RAISE EXCEPTION 'Fatal exception: Username already exists in Postgres; not
@@ -357,7 +357,7 @@ CREATE OR REPLACE FUNCTION admin__delete_user(in_username TEXT) returns INT as $
     
 $$ language 'plpgsql';
 
-comment on function admin_delete_user(text) is $$ 
+comment on function admin__delete_user(text) is $$ 
     Drops the provided user, as well as deletes the entity and user configuration data.
 $$;
 
@@ -380,7 +380,7 @@ CREATE OR REPLACE FUNCTION admin__delete_group (in_dbname TEXT, in_group_name TE
     END;
 $$ language 'plpgsql';
 
-comment on function admin_delete_group(text,text) IS $$ 
+comment on function admin__delete_group(text,text) IS $$ 
     Deletes the input group from the database. Not designed to be used to 
     remove a login-capable user.
 $$;
@@ -441,11 +441,28 @@ create or replace view user_listable as
     join users u on u.entity_id = e.id;
         
 
-create or replace function user_get_all_users () returns setof user_listable as $$
+create or replace function user__get_all_users () returns setof user_listable as $$
     
     select * from user_listable;
     
 $$ language sql;
 
+create or replace function admin__get_roles (in_database text) returns setof text as $$
+DECLARE
+    v_rol text;
+BEGIN
+    FOR v_rol in 
+        SELECT 
+            rolname
+        from 
+            pg_roles
+        where 
+            rolname ~ ('^lsmb_' || in_database)
+    LOOP
+        RETURN NEXT v_rol;
+    END LOOP;
+END;
+$$ language plpgsql;
+
 
 commit;
\ No newline at end of file