Adding UI support for multiple vendor/customer accounts per company.

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

1 files changed, 122 insertions, 65 deletions

diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql
index 63714c31..f493586f 100644
--- a/sql/modules/Company.sql
+++ b/sql/modules/Company.sql
@@ -153,43 +153,122 @@ CREATE TYPE entity_credit_search_return AS (
         threshold numeric
 );
 
+CREATE TYPE entity_credit_retrieve AS (
+        id int,
+        entity_id int,
+        entity_class int,
+        discount numeric,
+        taxincluded bool,
+        creditlimit numeric,
+        terms int2,
+        meta_number text,
+        business_id int,
+        language_code text,
+        pricegroup_id int,
+        curr text,
+        startdate date,
+        enddate date,
+        ar_ap_account_id int,
+        cash_account_id int,
+        threshold numeric
+);
+
 COMMENT ON TYPE entity_credit_search_return IS
 $$ This may change in 1.4 and should not be relied upon too much $$;
 
-CREATE OR REPLACE FUNCTION entity__retrieve_credit
+CREATE OR REPLACE FUNCTION entity_credit_get_id
+(in_entity_id int, in_entity_class int, in_meta_number text)
+RETURNS int AS $$
+DECLARE out_var int;
+BEGIN
+	SELECT id INTO out_var FROM entity_credit_account
+	WHERE entity_id = in_entity_id 
+		AND in_entity_class = entity_class
+		AND in_meta_number = meta_number;
+
+	RETURN out_var;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION entity__list_credit
 (in_entity_id int, in_entity_class int) 
-RETURNS entity_credit_search_return AS
+RETURNS SETOF entity_credit_retrieve AS
 $$
-DECLARE out_row entity_credit_search_return;
+DECLARE out_row entity_credit_retrieve;
 BEGIN
 	
-	SELECT c.legal_name, c.id, e.id, ec.entity_class, ec.discount,
-		ec.taxincluded, ec.creditlimit, ec.terms, ec.meta_number,
-		ec.business_id, ec.language_code, ec.pricegroup_id, 
-		ec.curr::char(3), ec.startdate, ec.enddate, ec.ar_ap_account_id,
-		ec.cash_account_id, c.tax_id, ec.threshold
-	INTO out_row
-	FROM company c
-	JOIN entity e ON (c.entity_id = e.id)
-	JOIN entity_credit_account ec ON (c.entity_id = ec.entity_id)
-	WHERE e.id = in_entity_id
-		AND ec.entity_class = CASE WHEN in_entity_class = 3 THEN 2
-				ELSE in_entity_class END;
-
-	RETURN out_row;
+	FOR out_row IN 
+		SELECT  c.id, e.id, ec.entity_class, ec.discount,
+			ec.taxincluded, ec.creditlimit, ec.terms, 
+			ec.meta_number, ec.business_id, ec.language_code, 
+			ec.pricegroup_id, ec.curr, ec.startdate, 
+			ec.enddate, ec.ar_ap_account_id, ec.cash_account_id, 
+			c.tax_id, ec.threshold
+		FROM company c
+		JOIN entity e ON (c.entity_id = e.id)
+		JOIN entity_credit_account ec ON (c.entity_id = ec.entity_id)
+		WHERE e.id = in_entity_id
+			AND ec.entity_class = 
+				CASE WHEN in_entity_class = 3 THEN 2
+				     WHEN in_entity_class IS NULL 
+					THEN ec.entity_class
+				ELSE in_entity_class END
+	LOOP
+
+		RETURN NEXT out_row;
+	END LOOP;
 END;
 $$ LANGUAGE PLPGSQL;
 
+CREATE OR REPLACE FUNCTION company_retrieve (in_id int) RETURNS company AS
+$$
+DECLARE t_company company;
+BEGIN
+	SELECT * INTO t_company FROM company WHERE id = in_id;
+	RETURN t_company;
+END;
+$$ language plpgsql;
 
-CREATE OR REPLACE FUNCTION entity_credit_save (
+CREATE OR REPLACE FUNCTION company_save (
     in_id int, in_entity_class int,
-    
+    in_name text, in_tax_id TEXT,
+    in_entity_id int, in_sic_code text
+) RETURNS INT AS $$
+DECLARE t_entity_id INT;
+BEGIN
+	IF in_entity_id IS NULL THEN
+		INSERT INTO entity (name, entity_class)
+		VALUES (in_name, in_entity_class);
+		t_entity_id := currval('entity_id_seq');
+	ELSE 
+		t_entity_id := in_entity_id;
+	END IF;
+
+	UPDATE company
+	SET legal_name = in_name,
+		tax_id = in_tax_id,
+		sic_code = in_sic_code
+	WHERE id = in_id;
+
+	IF FOUND THEN
+		RETURN in_id;
+	ELSE
+		INSERT INTO company(entity_id, legal_name, tax_id, sic_code)
+		VALUES (t_entity_id, in_name, in_tax_id, in_sic_code);
+
+		RETURN currval('company_id_seq');
+	END IF;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION entity_credit_save (
+    in_credit_id int, in_entity_class int,
+    in_entity_id int,
     in_discount numeric, in_taxincluded bool, in_creditlimit numeric, 
     in_discount_terms int,
     in_terms int, in_meta_number varchar(32), in_business_id int, 
     in_language varchar(6), in_pricegroup_id int, 
     in_curr char, in_startdate date, in_enddate date, 
-    in_name text, in_tax_id TEXT,
     in_threshold NUMERIC,
     in_ar_ap_account_id int, 
     in_cash_account_id int
@@ -198,28 +277,29 @@ CREATE OR REPLACE FUNCTION entity_credit_save (
     
     DECLARE
         t_entity_class int;
-        new_entity_id int;
-        v_row company;
         l_id int;
     BEGIN
+            update entity_credit_account SET
+                discount = in_discount,
+                taxincluded = in_taxincluded,
+                creditlimit = in_creditlimit,
+                terms = in_terms,
+                ar_ap_account_id = in_ar_ap_account_id,
+                cash_account_id = in_cash_account_id,
+                meta_number = in_meta_number,
+                business_id = in_business_id,
+                language_code = in_language,
+                pricegroup_id = in_pricegroup_id,
+                curr = in_curr,
+                startdate = in_startdate,
+                enddate = in_enddate,
+                threshold = in_threshold,
+                discount_terms = in_discount_terms
+            where id = in_credit_id;
         
-        -- TODO:  Move every table to an upsert mode independantly.
-        SELECT INTO v_row * FROM company WHERE legal_name = in_name;
-        
-        IF NOT FOUND THEN
-            -- do some inserts
-            
-            select nextval('entity_id_seq') into new_entity_id;
-            
-            insert into entity (id, name, entity_class) 
-                VALUES (new_entity_id, in_name, in_entity_class);
-            
-            INSERT INTO company ( entity_id, legal_name, tax_id ) 
-                VALUES ( new_entity_id, in_name, in_tax_id );
-	IF in_entity_class NOT IN (1, 2) THEN
-		RETURN new_entity_id;
-	END IF;
-            
+         IF FOUND THEN
+            RETURN in_credit_id;
+         ELSE
             INSERT INTO entity_credit_account (
                 entity_id,
                 entity_class,
@@ -240,7 +320,7 @@ CREATE OR REPLACE FUNCTION entity_credit_save (
                 cash_account_id
             )
             VALUES (
-                new_entity_id,
+                in_entity_id,
                 in_entity_class,
                 in_discount / 100, 
                 in_taxincluded,
@@ -259,32 +339,9 @@ CREATE OR REPLACE FUNCTION entity_credit_save (
                 in_cash_account_id
             );
             -- entity note class
+            RETURN currval('entity_credit_account_id_seq');
+       END IF;
 
-            return new_entity_id;
-
-        ELSIF FOUND THEN
-        
-            update company set tax_id = in_tax_id where id = v_row.id;
-            update entity_credit_account SET
-                discount = in_discount,
-                taxincluded = in_taxincluded,
-                creditlimit = in_creditlimit,
-                terms = in_terms,
-                ar_ap_account_id = in_ar_ap_account_id,
-                cash_account_id = in_cash_account_id,
-                meta_number = in_meta_number,
-                business_id = in_business_id,
-                language_code = in_language,
-                pricegroup_id = in_pricegroup_id,
-                curr = in_curr,
-                startdate = in_startdate,
-                enddate = in_enddate,
-                threshold = in_threshold,
-                discount_terms = in_discount_terms
-            where entity_id = v_row.entity_id;
-            
-            return v_row.entity_id;    
-        END IF;
     END;
     
 $$ language 'plpgsql';