Re: inventory function

[Victor Sterpu <..hidden..>]

The last 2 queryes that I posted were proven to be wrong after a little testing.

I've just looked on Ed's queryes.
It can't last 2 hours on a database like you described(it takes a few seconds on my db). Maybe you nedd a "vacuum analyze" and an update to max_fsm_pages - I had the same problem in the past.
Our queryes are not the same. The main reason is that I try to extract and the remaining value in the inventory account for the products.
This is necessary for me because I must match the inventory account from the inventory with the sold from the balance.

This query seems to work fine.
The qty should be correct. The value will be correctly printed if you don't have reverse ar invoices - witch will be supported in the next version.
The assembly must be correcty printed(the components will be displayed).

SELECT id, description, partnumber, sum(qty) as qty, sum(value) as value FROM (SELECT p.id, p.description, p.partnumber, -sum(i.qty) as qty, 0 as value FROM invoice i JOIN ar a ON (a.id=i.trans_id) JOIN parts p ON (i.parts_id=p.id AND p.inventory_accno_id>0) GROUP BY p.id, p.description, p.partnumber UNION ALL SELECT p.id, p.description, p.partnumber, 0, -sum(acc.amount) as value FROM acc_trans acc JOIN parts p ON (p.inventory_accno_id=acc.chart_id AND p.inventory_accno_id>0) JOIN invoice i ON (i.id=acc.invoice_id AND i.parts_id=p.id) WHERE acc.trans_id NOT IN (SELECT id FROM ap) GROUP BY p.id, p.description, p.partnumber UNION ALL SELECT p.id, p.description, p.partnumber, -sum(i.qty) as qty, -sum(i.qty*i.sellprice) as value FROM invoice i JOIN ap a ON (a.id=i.trans_id) JOIN parts p ON (i.parts_id=p.id AND p.inventory_accno_id>0) GROUP BY p.id, p.description, p.partnumber) AS temp WHERE description =  GROUP BY id, description, partnumber HAVING sum(value)!=0 OR sum(qty)!=0;

This query relies on the correct values inserted in the invoice_id field from acc_trans table.
I dont't have the correct values for this field yet, so there I must update it first and then run some other tests.

Ashley J Gittins wrote:

On Sat, 14 Jul 2007, Charley Tiggs wrote:
  

One of my clients just did inventory the past two days and we needed
this feature.  I ended up doing what Ed did.

Ed W wrote:
    

I wrote a small query to calculate the inventory and then just exported
it to a spreadsheet.  However would be useful to see it in the system.
      

I'd love to know what you came up with wrt the query. I have mangled onhand 
values in our system, and was playing the other night with a query that Ed 
posted a little while ago. It took > 2.5hrs to run! 8-O I've pasted it below 
if anyone has some ideas for optimisation (I am sure there are paths there, 
my brain just isn't too sharp atm).

We have ~ 1,000 vendor invoices, 3,500 sales invoices, 2,000 products, 13,000 
invoice lines.

Ed, Charley, were your queries much different to this version of Ed's from a 
while back?