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

[..hidden..]

Revision: 6251
          http://sourceforge.net/p/ledger-smb/code/6251
Author:   einhverfr
Date:     2013-11-08 03:12:52 +0000 (Fri, 08 Nov 2013)
Log Message:
-----------
Parts retrieval/search functions now tested and working

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:37:54 UTC (rev 6250)
+++ addons/1.3/wxPOS/setup/functions.sql	2013-11-08 03:12:52 UTC (rev 6251)
@@ -67,7 +67,7 @@
   FROM partsvendor p;
 
 CREATE OR REPLACE FUNCTION wxpos_part__get
-(in_partnumber text, in_contact_id int)
+(in_partnumber text, in_contact_id int, in_contact_class int)
 RETURNS parts LANGUAGE PLPGSQL AS
 $$
 DECLARE retval parts;
@@ -75,16 +75,16 @@
 BEGIN
     SELECT * INTO retval 
       FROM parts
-     WHERE partnumber = in_partnumer AND obsolete IS NOT TRUE;
+     WHERE partnumber = in_partnumber AND obsolete IS NOT TRUE;
     SELECT * INTO pmatrix FROM wxpos_pricematrix
-     WHERE parts_id =retval.parts_id
+     WHERE parts_id =retval.id AND credit_id = in_contact_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;
+           retval.sellprice := pmatrix.sellprice;
         ELSIF in_contact_class = 1 THEN
-           retval.lastcost = pmatrix.sellprice;
+           retval.lastcost := pmatrix.sellprice;
         END IF;
     END IF;
     RETURN retval;
@@ -96,26 +96,28 @@
 RETURNS SETOF parts LANGUAGE PLPGSQL AS
 $$
 DECLARE retval RECORD;
+        p parts;
         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 
+       SELECT pt as p, pm as pricematrix
+         FROM parts pt
+    LEFT JOIN (select wpm.*, rank() over (order by sellprice) as ranking
+                 FROM wxpos_pricematrix wpm WHERE credit_id = in_contact_id
+              ) pm ON (pm.ranking = 1 AND pm.parts_id = pt.id)
+        WHERE description @@ plainto_tsquery(in_string)
    LOOP
+       p := retval.p;
        IF retval.pricematrix IS NOT NULL THEN
           pmatrix := retval.pricematrix;
           IF in_contact_class = 2 THEN
-             retval.sellprice = pmatrix.sellprice;
+             p.sellprice := pmatrix.sellprice;
           ELSIF in_contact_class = 1 THEN
-             retval.lastcost = pmatrix.sellprice;
+             p.lastcost := pmatrix.sellprice;
           END IF;
        
        END IF;
-       RETURN NEXT retval;
+       RETURN NEXT p;
    END LOOP;
 END;
 $$;

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