How to get an affordable amount of products (Odoo v8 and v9)

Question

I need to get an affordable amount of products from stock oooh.

There are several models in which I have Stock_quant, stock_move, stock_location.

What I'm trying to achieve are two things:

• Total Products Available
• Available quantity based on location

Can anyone guide me?

Answer 1

The fund-related fields are defined in the products (functional field) and directly from the product, which you can get for all warehouses / locations or for a single location / warehouse.

Example:

For all warehouses / locations

product = self.env['product.product'].browse(PRODUCT_ID)
available_qty = product.qty_available

/ (WAREHOUSE_ID/LOCATION_ID )

product = self.env['product.product'].browse(PRODUCT_ID)
available_qty = product.with_context({'warehouse' : WAREHOUSE_ID}).qty_available

available_qty = product.with_context({'location' : LOCATION_ID}).qty_available

.

Forecasted Stock => virtual_available
Incoming Stock => incoming
Outgoing Stock => outgoing

. - / , .

product.py .

:

@api.onchange('product_id','source_location') 
def product_qty_location_check(self): 
    if self.product_id and self.source_location: 
        product = self.product_id
        available_qty = product.with_context({'location' : self.source_location.id}).qty_‌​available 
        print available_qty

Answer 2

Odoo 8,9 10:

with
  uitstock as (
    select
      t.name product, sum(product_qty) sumout, m.product_id, m.product_uom 
    from stock_move m 
      left join product_product p on m.product_id = p.id 
      left join product_template t on p.product_tmpl_id = t.id
    where
      m.state like 'done' 
      and m.location_id in (select id from stock_location where complete_name like '%Stock%') 
      and m.location_dest_id not in (select id from stock_location where complete_name like '%Stock%') 
    group by product_id,product_uom, t.name order by t.name asc
  ),
  instock as (
    select
      t.list_price purchaseprice, t.name product, sum(product_qty) sumin, m.product_id, m.product_uom
    from stock_move m
      left join product_product p on m.product_id = p.id
      left join product_template t on p.product_tmpl_id = t.id
    where 
      m.state like 'done' and m.location_id not in (select id from stock_location where complete_name like '%Stock%')
      and m.location_dest_id in (select id from stock_location where complete_name like '%Stock%')
    group by product_id,product_uom, t.name, t.list_price order by t.name asc
  ) 
select
  i.product, sumin-coalesce(sumout,0) AS stock, sumin, sumout, purchaseprice, ((sumin-coalesce(sumout,0)) * purchaseprice) as stockvalue
from uitstock u 
  full outer join instock i on u.product = i.product