From 3d8c7a3a98a3dfa05864b79e6cc2396871f20199 Mon Sep 17 00:00:00 2001 From: aurynn_cmd Date: Tue, 19 Aug 2008 23:00:35 +0000 Subject: Copious Admin and Reconciliation changes. git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2283 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/modules/Reconciliaton.sql | 55 ++++++++++++++++++++++++++++++++++--------- sql/modules/admin.sql | 25 ++++++++++++++++---- 2 files changed, 65 insertions(+), 15 deletions(-) (limited to 'sql/modules') 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 -- cgit v1.2.3