[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [2177] trunk/sql
- Subject: SF.net SVN: ledger-smb: [2177] trunk/sql
- From: ..hidden..
- Date: Mon, 30 Jun 2008 13:56:23 -0700
Revision: 2177
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2177&view=rev
Author: aurynn_cmd
Date: 2008-06-30 13:56:17 -0700 (Mon, 30 Jun 2008)
Log Message:
-----------
Adding the reconciliation SQL stuff. Testing my SVN interface.
Modified Paths:
--------------
trunk/sql/Pg-database.sql
trunk/sql/modules/Reconciliaton.sql
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2008-06-26 17:19:35 UTC (rev 2176)
+++ trunk/sql/Pg-database.sql 2008-06-30 20:56:17 UTC (rev 2177)
@@ -270,8 +270,8 @@
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 $$;
Modified: trunk/sql/modules/Reconciliaton.sql
===================================================================
--- trunk/sql/modules/Reconciliaton.sql 2008-06-26 17:19:35 UTC (rev 2176)
+++ trunk/sql/modules/Reconciliaton.sql 2008-06-30 20:56:17 UTC (rev 2177)
@@ -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 @@
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 @@
$$ 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 @@
$$ 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 @@
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 @@
-- 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 @@
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 @@
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 @@
in_report_id,
in_scn,
in_amount,
- gl.balance,
+ gl.amount,
errorcode,
in_user,
in_date,
@@ -312,26 +323,26 @@
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 @@
$$ 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 @@
$$ 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 @@
$$ 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 @@
END IF;
END;
-$$ language 'plpgsql';
+$$ language 'plplsql';
-CREATE OR REPLACE FUNCTION reconciliation_single_entry
-(in_report_id INT, in_id INT)
-returns pending_reports AS
-$$
+CREATE OR REPLACE FUNCTION reconciliation__single_entry (in_report_id INT, in_id INT) returns setof 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
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.