SF.net SVN: ledger-smb:[3042] trunk/sql

[..hidden..]

Revision: 3042
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3042&view=rev
Author:   einhverfr
Date:     2010-08-09 18:16:41 +0000 (Mon, 09 Aug 2010)

Log Message:
-----------
Account link fixes

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Account.sql
    trunk/sql/modules/test/Account.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2010-08-09 17:20:34 UTC (rev 3041)
+++ trunk/sql/Pg-database.sql	2010-08-09 18:16:41 UTC (rev 3042)
@@ -40,9 +40,40 @@
   primary key (end_date, account_id)
 );
 
+CREATE TABLE account_link_description (
+    description text    primary key,
+    summary     boolean not null,
+    custom      boolean not null   
+);
+
+INSERT INTO account_link_description (description, summary, custom) 
+VALUES 
+--summary links
+('AR', TRUE, FALSE),
+('AP', TRUE, FALSE),
+('IC', TRUE, FALSE),
+--custom links NOT INCLUDED
+('AR_amount',      FALSE, FALSE),
+('AR_tax',         FALSE, FALSE),
+('AR_paid',        FALSE, FALSE),
+('AR_overpayment', FALSE, FALSE),
+('AR_discount',    FALSE, FALSE),
+('AP_amount',      FALSE, FALSE),
+('AP_tax',         FALSE, FALSE),
+('AP_paid',        FALSE, FALSE),
+('AP_overpayment', FALSE, FALSE),
+('AP_discount',    FALSE, FALSE),
+('IC_sale',        FALSE, FALSE),
+('IC_tax',        FALSE, FALSE),
+('IC_cogs',        FALSE, FALSE),
+('IC_taxpart',     FALSE, FALSE),
+('IC_taxservice',  FALSE, FALSE),
+('IC_income',      FALSE, FALSE),
+('IC_expense',     FALSE, FALSE);
+
 CREATE TABLE account_link (
    account_id int references account(id),
-   description text,
+   description text references account_link_description(description),
    primary key (account_id, description)
 );
 

Modified: trunk/sql/modules/Account.sql
===================================================================
--- trunk/sql/modules/Account.sql	2010-08-09 17:20:34 UTC (rev 3041)
+++ trunk/sql/modules/Account.sql	2010-08-09 18:16:41 UTC (rev 3042)
@@ -46,19 +46,16 @@
 in_gifi text, in_heading int, in_contra bool, in_link text[])
 RETURNS int AS $$
 DECLARE 
-	t_summary_links TEXT[] = '{AR,AP,IC}';
 	t_heading_id int;
-	t_text record;
+	t_link record;
 	t_id int;
 BEGIN
 	-- check to ensure summary accounts are exclusive
         -- necessary for proper handling by legacy code
-	FOR t_text IN 
-		select t_summary_links[generate_series] AS val 
-		FROM generate_series(array_lower(t_summary_links, 1), 
-			array_upper(t_summary_links, 1))
+    FOR t_link IN SELECT description FROM account_link_description 
+    WHERE summary='t'
 	LOOP
-		IF t_text.val = ANY (in_link) and array_upper(in_link, 1) > 1 THEN
+		IF t_link.description = ANY (in_link) and array_upper(in_link, 1) > 1 THEN
 			RAISE EXCEPTION 'Invalid link settings:  Summary';
 		END IF;
 	END LOOP;
@@ -70,7 +67,12 @@
 		t_heading_id := in_heading;
 	END IF;
 
-	DELETE FROM account_link WHERE account_id = in_id;
+    -- don't remove custom links.
+	DELETE FROM account_link 
+	WHERE account_id = in_id 
+              and description in ( select description 
+                                    from  account_link_description
+                                    where custom = 'f');
 
 	UPDATE account 
 	SET accno = in_accno,
@@ -92,13 +94,13 @@
 		t_id := currval('account_id_seq');
 	END IF;
 
-	FOR t_text IN 
+	FOR t_link IN 
 		select in_link[generate_series] AS val
 		FROM generate_series(array_lower(in_link, 1), 
 			array_upper(in_link, 1))
 	LOOP
 		INSERT INTO account_link (account_id, description)
-		VALUES (t_id, t_text.val);
+		VALUES (t_id, t_link.val);
 	END LOOP;
 
 	
@@ -169,3 +171,8 @@
 SELECT * FROM account
 WHERE id IN (SELECT account_id FROM account_link WHERE description = $1);
 $$ language sql;
+
+CREATE OR REPLACE FUNCTION get_link_descriptions() RETURNS SETOF account_link_description AS
+$$
+    SELECT * FROM account_link_description;
+$$ LANGUAGE SQL;

Modified: trunk/sql/modules/test/Account.sql
===================================================================
--- trunk/sql/modules/test/Account.sql	2010-08-09 17:20:34 UTC (rev 3041)
+++ trunk/sql/modules/test/Account.sql	2010-08-09 18:16:41 UTC (rev 3042)
@@ -31,48 +31,64 @@
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AR PAID 1', 'A', 'A', '00007', 'AR_paid');
 
+insert into account_link_description(description, summary, custom)
+values ('AR_paid1', false, true);
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AR PAID 2', 'A', 'A', '00008', 'AR_paid1');
 
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AR PAID 3', 'A', 'A', '00009', 'IC_tax:AR_paid');
 
+insert into account_link_description(description, summary, custom)
+values ('AR_p', false, true);
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AR PAID 4 INVALID', 'A', 'A', '00010', 'AR_p');
 
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP PAID 1', 'A', 'A', '00011', 'AP_paid');
 
+insert into account_link_description(description, summary, custom)
+values ('AP_paid1', false, true);
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP PAID 2', 'A', 'A', '00012', 'AP_paid1');
 
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP PAID 3', 'A', 'A', '00013', 'IC_tax:AP_paid');
 
+insert into account_link_description(description, summary, custom)
+values ('AP_p', false, true);
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP PAID 4 INVALID', 'A', 'A', '00014', 'AP_p');
 
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP Overpayment 1', 'A', 'A', '00015', 'AP_overpayment');
 
+insert into account_link_description(description, summary, custom)
+values ('AP_overpayment1', false, true);
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP Overpayment 2', 'A', 'A', '00016', 'AP_overpayment1');
 
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP Overpayment 3', 'A', 'A', '00017', 'IC_tax:AP_overpayment');
 
+insert into account_link_description(description, summary, custom)
+values ('AP_overp', false, true);
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP Overpayment 4 INVALID', 'A', 'A', '00018', 'AP_overp');
 
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP Overpayment 1', 'A', 'A', '00019', 'AR_overpayment');
 
+insert into account_link_description(description, summary, custom)
+values ('AR_overpayment1', false, true);
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP Overpayment 2', 'A', 'A', '00020', 'AR_overpayment1');
 
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP Overpayment 3', 'A', 'A', '00021', 'IC_tax:AR_overpayment');
 
+insert into account_link_description(description, summary, custom)
+values ('AR_overp', false, true);
 INSERT INTO chart (description, charttype, category, accno, link)
 VALUES ('TEST AP Overpayment 4 INVALID', 'A', 'A', '00022', 'AR_overp');
 


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.