SF.net SVN: ledger-smb:[6250] addons/1.3/wxPOS/setup/functions.sql

[..hidden..]

Revision: 6250
          http://sourceforge.net/p/ledger-smb/code/6250
Author:   einhverfr
Date:     2013-11-07 16:37:54 +0000 (Thu, 07 Nov 2013)
Log Message:
-----------
Supporting functions for WXPOS::Part now load, next to move routines to it.

Modified Paths:
--------------
    addons/1.3/wxPOS/setup/functions.sql

Modified: addons/1.3/wxPOS/setup/functions.sql
===================================================================
--- addons/1.3/wxPOS/setup/functions.sql	2013-11-07 16:11:55 UTC (rev 6249)
+++ addons/1.3/wxPOS/setup/functions.sql	2013-11-07 16:37:54 UTC (rev 6250)
@@ -41,6 +41,83 @@
  WHERE id IN (select id from users where username = $1);
 $$;
 
+DROP VIEW IF EXISTS wxpos_pricematrix;
 
+CREATE VIEW wxpos_pricematrix AS 
+SELECT parts_id, credit_id, pricebreak, sellprice
+  FROM partscustomer
+ WHERE credit_id is not null and
+       (validfrom is null OR validfrom >= now()) AND
+       (validto IS NULL OR validto <= now())
+UNION ALL
+SELECT p.parts_id, c.id, p.pricebreak, p.sellprice
+  FROM partscustomer p
+  JOIN entity_credit_account c ON p.pricegroup_id = c.pricegroup_id
+ WHERE c.id is not null and
+       (validfrom is null OR validfrom >= now()) AND
+       (validto IS NULL OR validto <= now())
+UNION ALL
+SELECT p.parts_id, p.credit_id, p.pricebreak, p.sellprice
+  FROM partscustomer p
+ WHERE credit_id is null and pricegroup_id is null AND
+       (validfrom is null OR validfrom >= now()) AND
+       (validto IS NULL OR validto <= now())
+UNION ALL
+select p.parts_id, p.credit_id, 0, p.lastcost
+  FROM partsvendor p;
 
+CREATE OR REPLACE FUNCTION wxpos_part__get
+(in_partnumber text, in_contact_id int)
+RETURNS parts LANGUAGE PLPGSQL AS
+$$
+DECLARE retval parts;
+        pmatrix wxpos_pricematrix;
+BEGIN
+    SELECT * INTO retval 
+      FROM parts
+     WHERE partnumber = in_partnumer AND obsolete IS NOT TRUE;
+    SELECT * INTO pmatrix FROM wxpos_pricematrix
+     WHERE parts_id =retval.parts_id
+  ORDER BY sellprice asc limit 1;
+
+    IF pmatrix.parts_id IS NOT NULL THEN
+        IF in_contact_class = 2 THEN
+           retval.sellprice = pmatrix.sellprice;
+        ELSIF in_contact_class = 1 THEN
+           retval.lastcost = pmatrix.sellprice;
+        END IF;
+    END IF;
+    RETURN retval;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION wxpos_part__search
+(in_string text, in_contact_id int, in_contact_class int)
+RETURNS SETOF parts LANGUAGE PLPGSQL AS
+$$
+DECLARE retval RECORD;
+        pmatrix record;
+BEGIN
+   FOR retval IN 
+       SELECT p.*, pm as pricematrix
+         FROM parts p
+         JOIN (select wpm.*, rank() over (order by sellprice) as ranking
+                 FROM wxpos_pricematrix wpm WHERE contact_id = in_contact_id
+              ) pm ON (pm.ranking = 1 AND pm.parts_id = p.id)
+        WHERE description 
+   LOOP
+       IF retval.pricematrix IS NOT NULL THEN
+          pmatrix := retval.pricematrix;
+          IF in_contact_class = 2 THEN
+             retval.sellprice = pmatrix.sellprice;
+          ELSIF in_contact_class = 1 THEN
+             retval.lastcost = pmatrix.sellprice;
+          END IF;
+       
+       END IF;
+       RETURN NEXT retval;
+   END LOOP;
+END;
+$$;
+
 COMMIT;

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


------------------------------------------------------------------------------
November Webinars for C, C++, Fortran Developers
Accelerate application performance with scalable programming models. Explore
techniques for threading, error checking, porting, and tuning. Get the most 
from the latest Intel processors and coprocessors. See abstracts and register
http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits