Dev - Help to create a rather complex entity-view definition

Posted by Jacopo Cappellato on Dec 12, 2005; 3:22pm
URL: http://ofbiz.116.s1.nabble.com/Dev-Help-to-create-a-rather-complex-entity-view-definition-tp166352.html

Hi all,

I'm in the process of implementing a service to automatically compute
the average cost of a product (based on recent purchase orders), see:

http://jira.undersunconsulting.com/browse/OFBIZ-240


Ok, I'd like to set up a good view definition to efficiently perform the
weighted sum based on the quantity and price; in SQL the query should
look like this one:

SELECT
PRODUCT_ID, SUM(UNIT_PRICE * QUANTITY) / SUM(QUANTITY)
FROM
ORDER_ITEM AS OI, ORDER_HEADER AS OH
WHERE
OH.ORDER_ID = OI.ORDER_ID AND OH.ORDER_TYPE_ID = 'PURCHASE_ORDER' AND
ORDER_DATE > '2004-12-31 00:00:00.0'
GROUP BY
PRODUCT_ID

The problem/question is: how can I do this with a view-definition?
Can you help me with this?

Thanks,

Jacopo

PS: the above SQL will not take into account the cancelled items and all
the adjustments... but we will cope with them in the next step ;-)

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev