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

[..hidden..]

Revision: 2433
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2433&view=rev
Author:   einhverfr
Date:     2008-12-22 17:37:05 +0000 (Mon, 22 Dec 2008)

Log Message:
-----------
Correcting improper handling of meta_number filter

Modified Paths:
--------------
    trunk/sql/modules/Inventory.sql
    trunk/sql/modules/Payment.sql

Modified: trunk/sql/modules/Inventory.sql
===================================================================
--- trunk/sql/modules/Inventory.sql	2008-12-18 19:10:14 UTC (rev 2432)
+++ trunk/sql/modules/Inventory.sql	2008-12-22 17:37:05 UTC (rev 2433)
@@ -3,22 +3,40 @@
 RETURNS parts AS
 $$
 DECLARE out_row parts%ROWTYPE;
+	t_parts_id int;
 BEGIN
+	SELECT id INTO t_parts_id 
+	FROM parts 
+	WHERE partnumber = in_partnumber
+		and obsolete is not true
+		and assembly is not true;
+
         SELECT p.id, p.partnumber, p.description, p.unit, p.listprice, 
 		p.sellprice, p.lastcost, p.priceupdate, p.weight, 
-                p.onhand - sum(coalesce(i.qty, 0))
+                sum(coalesce(c.multiplier, 1) * i.qty) * -1
                 AS onhand, p.notes, p.makemodel, p.assembly, p.alternate, 
 		p.rop, p.inventory_accno_id, p.income_accno_id, p.expense_accno_id,
 		p.bin, p.obsolete, p.bom, p.image, p.microfiche, p.partsgroup_id, 
 		p.project_id, p.avgcost
-	INTO out_row;
+	INTO out_row
         FROM parts p
-        LEFT JOIN invoice i ON (i.parts_id = p.id
-                AND i.trans_id IN 
-                        (select id FROM ar WHERE transdate > in_trans_date
-                        UNION 
-                        SELECT id FROM ap WHERE transdate > in_trans_date))
+	LEFT JOIN (  SELECT product(qty) as multiplier, t_parts_id  as part_used
+		FROM assembly a
+		JOIN parts p ON (a.id = p.id and p.volume_break = true)
+		JOIN (SELECT *, t_parts_id as part_used
+                     FROM connectby('assembly', 'id', 'parts_id', 'id', 
+			t_parts_id,
+                                0, ',')
+                        c(id integer, parent integer, "level" integer,
+                                path text, list_order integer)
+		) asm ON (asm.id = p.id)
+	) c ON (c.part_used = t_parts_id)
+        JOIN invoice i ON (i.parts_id = p.id OR i.parts_id = c.part_used)
+	JOIN (select id, transdate from ar
+		UNION select id, transdate from ap) a ON (i.trans_id = a.id)
+
         WHERE p.partnumber = in_partnumber
+		AND a.transdate <= in_transdate
                 AND assembly IS FALSE AND obsolete IS NOT TRUE
         GROUP BY p.id, p.partnumber, p.description, p.unit, p.listprice,
                 p.sellprice, p.lastcost, p.priceupdate, p.weight,
@@ -31,6 +49,17 @@
 END;
 $$ LANGUAGE PLPGSQL;
 
+CREATE FUNCTION product (numeric, numeric) RETURNS numeric AS
+$$
+SELECT $1 * $2;
+$$ language sql;
+
+CREATE AGGREGATE product(
+	base_type = numeric,
+	sfunc = product,
+	stype = numeric
+);
+
 CREATE OR REPLACE FUNCTION inventory_create_report(in_transdate date) RETURNS int
 AS
 $$
@@ -49,4 +78,5 @@
 	VALUES (in_report_id, in_parts_id, in_onhand, in_counted);
 
 	RETURN currval('inventory_report_line_id_seq');
+end;
 $$ LANGUAGE plpgsql;

Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql	2008-12-18 19:10:14 UTC (rev 2432)
+++ trunk/sql/modules/Payment.sql	2008-12-22 17:37:05 UTC (rev 2433)
@@ -312,7 +312,7 @@
 		        GROUP BY trans_id) p ON (a.id = p.trans_id)
 		LEFT JOIN "session" s ON (s."session_id" = t.locked_by)
 		LEFT JOIN users u ON (u.id = s.users_id)
-		   WHERE a.batch_id = in_batch_id
+		   WHERE (a.batch_id = in_batch_id
 		          OR (a.invoice_class = in_account_class
 		             AND a.approved
 			 AND c.business_id = 
@@ -322,8 +322,6 @@
 		         AND c.entity_class = in_account_class
 		         AND a.curr = in_currency
 		         AND a.entity_credit_account = c.id
-		         AND (in_meta_number IS NULL OR 
-                             in_meta_number = c.meta_number)
 			 AND p.due <> 0
 		         AND a.amount <> a.paid 
 			 AND NOT a.on_hold
@@ -332,7 +330,9 @@
 		                            chart_id = (SELECT id frOM chart
 		                                         WHERE accno
 		                                               = in_ar_ap_accno)
-		                    ))
+		                    )))
+		         AND (in_meta_number IS NULL OR 
+                             in_meta_number = c.meta_number)
 		GROUP BY c.id, e.name, c.meta_number, c.threshold, 
 			e.control_code, c.description
 		  HAVING  (sum(p.due) >= c.threshold


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