eiru_reports_dashboard/controllers/helpers/widget_stock_valuation.py

# -*- coding: utf-8 -*-
from openerp.http import request as r

def get_stock_valuation():
    user_store = r.env.user.store_id.id
    query = '''
        SELECT
        	location.id,
        	quant.id,
        	quant.qty,
        	quant.product_id,
        	(array_agg(history.cost ORDER BY history.id DESC))[1] * quant.qty as cost
        FROM stock_location AS location
        LEFT JOIN stock_quant AS quant
        ON quant.location_id = location.id
        LEFT JOIN product_product AS product
        ON product.id = quant.product_id
        LEFT JOIN product_price_history AS history
        ON history.product_template_id = product.product_tmpl_id
        WHERE location.usage = 'internal'
        AND location.active = TRUE
        AND product.active = TRUE
        AND location.store_id = ''' + str(user_store) + '''
        GROUP BY
        	location.id,
        	quant.id,
        	quant.qty,
        	quant.product_id
    '''

    r.cr.execute(query)

    data = [
        {
            'location_id': j[0],
            'quant_id': j[1],
            'qty': j[2],
            'product_id': j[3],
            'cost': j[4],
        } for j in r.cr.fetchall()
    ]

    values = []
    valuation = 0
    quantity = 0

    for item in data:
        valuation += item['cost']
        quantity += item['qty']

    values.append({
        'valuation': valuation,
        'quantity': quantity,
    })

    return values