diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/Pg-database.sql | 4 | ||||
| -rw-r--r-- | sql/modules/Reconciliaton.sql | 292 |
2 files changed, 180 insertions, 116 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index c9f2bea3..ca907d50 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -270,8 +270,8 @@ CREATE TABLE company_to_contact ( company_id integer not null references company(id) ON DELETE CASCADE, contact_class_id integer references contact_class(id) not null, contact text check(contact ~ '[[:alnum:]_]') not null, - description text not null, - PRIMARY KEY (company_id,contact_class_id,contact)); + description text, + PRIMARY KEY (company_id, contact_class_id, contact)); COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$; 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 |