SF.net SVN: ledger-smb:[4032] branches/1.3/sql/modules/Company.sql

[..hidden..]

Revision: 4032
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4032&view=rev
Author:   einhverfr
Date:     2011-11-22 09:02:29 +0000 (Tue, 22 Nov 2011)
Log Message:
-----------
Groundwork for fix on pricematrix issues
If we have another RC, then this can be safely included, as impact is minimal (routines not yet used by application), and if not, we can just package RC1 for 1.3.6.
Impact is deemed minimal because this commit includes stored procedures which are known to load, and which are not yet used by the appliction, meaning they cannot break anything.

Modified Paths:
--------------
    branches/1.3/sql/modules/Company.sql

Modified: branches/1.3/sql/modules/Company.sql
===================================================================
--- branches/1.3/sql/modules/Company.sql	2011-11-22 08:56:40 UTC (rev 4031)
+++ branches/1.3/sql/modules/Company.sql	2011-11-22 09:02:29 UTC (rev 4032)
@@ -1332,7 +1332,20 @@
 ) IS 
 $$ Returns a list of all entity credit accounts attached to that entity.$$;
 
+-- pricematrix
 
+CREATE OR REPLACE FUNCTION eca__get_pricematrix_by_pricegroup(in_id int)
+RETURNS SETOF eca__pricematrix AS
+$$
+SELECT pc.parts_id, p.partnumber, p.description, pc.credit_id, pc.pricebreak,
+       pc.sellprice, NULL::numeric, NULL::int, NULL::text, pc.validfrom, 
+       pc.validto, pc.curr, pc.entry_id
+  FROM partscustomer pc
+  JOIN parts p on pc.parts_id = p.id
+  JOIN entity_credit_account eca ON pc.pricegroup_id = eca.pricegroup_id
+ WHERE eca.id = $1 AND eca.entity_class = 2
+$$ LANGUAGE SQL;
+
 CREATE OR REPLACE FUNCTION eca__get_pricematrix(in_id int) 
 RETURNS SETOF eca__pricematrix AS
 $$
@@ -1356,4 +1369,118 @@
 
 $$ language sql;
 
+COMMENT ON FUNCTION eca__get_pricematrix(in_id int) IS
+$$ This returns the pricematrix for the customer or vendor 
+(entity_credit_account identified by in_id), orderd by partnumber, validfrom
+$$;
+
+CREATE OR REPLACE FUNCTION eca__delete_pricematrix 
+(in_credit_id int, in_entry_id int)
+RETURNS BOOL AS
+$$
+DECLARE retval bool;
+
+BEGIN
+
+retval := false;
+
+DELETE FROM partsvendor 
+ WHERE entry_id = in_entry_id 
+       AND credit_id = in_credit_id;
+
+retval := FOUND;
+
+DELETE FROM partscustomer
+ WHERE entry_id = in_entry_id
+       AND credit_id = in_credit_id;
+
+RETURN FOUND or retval;
+
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION eca__save_pricematrix
+(in_parts_id int, in_credit_id int, in_pricebreak numeric, in_price numeric, 
+ in_lead_time int2, in_partnumber text, in_validfrom date, in_validto date, 
+ in_curr char(3), in_entry_id int)
+RETURNS eca__pricematrix AS
+$$
+DECLARE 
+   retval eca__pricematrix;
+   t_insert bool;
+
+BEGIN
+
+t_insert := false;
+
+PERFORM * FROM entity_credit_account 
+  WHERE id = in_credit_id AND entity_class = 1;
+
+IF FOUND THEN -- VENDOR
+    UPDATE partsvendor
+       SET lastcost = in_price,
+           leadtime = in_lead_time,
+           partnumber = in_partnumber,
+           curr = in_curr
+     WHERE credit_id = in_credit_id AND entry_id = in_entry_id;
+
+    IF NOT FOUND THEN
+        INSERT INTO partsvendor
+               (parts_id, credit_id, lastcost, leadtime, partnumber, curr)
+        VALUES (in_parts_id, in_credit_id, in_price, in_leadtime::int2, 
+               in_partnumber, in_curr);
+    END IF;
+
+    SELECT pv.parts_id, p.partnumber, p.description, pv.credit_id, NULL, NULL,
+           pv.lastcost, pv.leadtime::int, pv.partnumber, NULL, NULL, pv.curr, 
+           pv.entry_id
+      INTO retval
+      FROM partsvendor pv
+      JOIN parts p ON p.id = pv.parts_id
+     WHERE parts_id = in_parts_id and credit_id = in_credit_id;
+
+    RETURN retval;
+END IF;
+
+PERFORM * FROM entity_credit_account
+  WHERE id = in_credit_id AND entity_class = 2;
+
+IF FOUND THEN -- CUSTOMER
+    UPDATE partscustomer
+       SET pricebreak = in_pricebreak,
+           sellprice  = in_price,
+           validfrom  = in_validfrom,
+           validto    = in_validto,
+           curr       = in_curr
+     WHERE entry_id = in_entry_id and credit_id = in_credit_id;
+
+    IF NOT FOUND THEN
+        INSERT INTO partscustomer
+               (parts_id, credit_id, sellprice, validfrom, validto, curr)
+        VALUES (in_parts_id, in_credit_id, in_price, in_validfrom, in_validto, 
+                in_curr);
+
+        t_insert := true;
+    END IF;
+
+    SELECT pc.parts_id, p.partnumber, p.description, pc.credit_id, 
+           pc.pricebreak, pc.sellprice, NULL, NULL, NULL, pc.validfrom, 
+           pc.validto, pc.curr, pc.entry_id
+      INTO retval
+      FROM partscustomer pc
+      JOIN parts p on pc.parts_id = p.id
+     WHERE entry_id = CASE WHEN t_insert 
+                           THEN currval('partscustomer_entry_id_seq') 
+                           ELSE in_entry_id 
+                      END;
+                           
+    RETURN retval;
+
+END IF;
+
+RAISE EXCEPTION 'No valid entity credit account found';
+   
+END;
+$$ LANGUAGE PLPGSQL;
+
 COMMIT;

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