--drop function inventory_book_new(integer,integer,integer,timestamp,timestamp);
CREATE OR REPLACE FUNCTION adempiere.inventory_book_new( p_m_product_id integer, p_m_product_category_id integer, p_ad_client_id integer, p_startdate timestamp without time zone, p_enddate timestamp without time zone)
    RETURNS TABLE(c_m_product_id integer, c_productvalue text, c_productname text, c_costhistoric numeric, c_currentcost_inicial numeric, c_currentcost_final numeric, c_qtyinicial numeric, c_qtyfinal numeric, c_currentcost_actual numeric, c_inqty numeric, c_outqty numeric, c_autoqty numeric, c_priceentered numeric)
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
 
DECLARE 
var_r record; 
var_rr record; 
v_costhistoric numeric =0;
v_price_list_historic CONSTANT int = 1000011;
v_m_costelement_id CONSTANT int = 1000002;
v_currentcost_inicial numeric =0;
v_currentcost_final numeric =0;
v_currentcost_actual numeric =0;
actual_cost numeric =0;
StartTime timestamptz;
EndTime timestamptz;
Delta interval;
v_inqty numeric :=0;  
v_outqty numeric :=0;
v_autoqty numeric :=0;
actual_price numeric = 0;
BEGIN 
StartTime := clock_timestamp();
FOR var_rr IN 
SELECT p.m_product_id,p.value,p.name,pp.pricelist costo_historico,COALESCE(invi.movementqty,0) qty_ini,COALESCE(invf.inqty,0) qty_in,COALESCE(invf.outqty,0) qty_out,COALESCE(invf.autoqty,0) qty_auto,COALESCE(invf.inqty+invf.outqty+invf.autoqty+invi.movementqty,invi.movementqty,0) qty_final,coalesce(max(i.newcostprice),0) costo_inicial,coalesce(max(f.newcostprice),0) costo_final,coalesce(max(priceentered),0) precio_venta,coalesce(max(costo),0) costo_actual
FROM m_productprice pp
JOIN M_product p ON p.m_product_id=pp.m_product_id
--INVENTARIO INICIAL
LEFT JOIN (SELECT CASE WHEN max(p.ProductType) = 'S' THEN 0 ELSE sum(movementqty) END movementqty,t.m_product_id
           FROM m_transaction_v t JOIN M_product p ON t.m_product_id=p.m_product_id
           WHERE movementtype <> ALL (ARRAY['M+', 'M-']) and movementdate<p_startdate
           GROUP BY t.m_product_id) invi ON invi.m_product_id = pp.m_product_id 
--COSTO INICIAL
LEFT JOIN (SELECT Distinct ON (m_product_id)  newcostprice,m_product_id FROM m_costmovement_v b where b.m_costelement_id = v_m_costelement_id AND b.created <= p_startdate ORDER BY m_product_id,b.created DESC) i ON p.m_product_id=i.m_product_id
--COSTO FINAL
LEFT JOIN (SELECT Distinct ON (m_product_id)  newcostprice,m_product_id FROM m_costmovement_v b where b.m_costelement_id = v_m_costelement_id AND b.created <= p_enddate ORDER BY m_product_id,b.created DESC) f ON p.m_product_id=f.m_product_id
--INVENTARIO FINAL
LEFT JOIN (SELECT 
           CASE WHEN p.ProductType = 'S' THEN 0 ELSE sum(CASE WHEN movementtype <> ALL (ARRAY['M+', 'M-','I+','I-','C+','C-']) THEN movementqty ELSE 0 END) END  inqty,
           CASE WHEN p.ProductType = 'S' THEN 0 ELSE sum(CASE WHEN movementtype <> ALL (ARRAY['M+', 'M-','I+','I-','V+','V-']) THEN movementqty ELSE 0 END) END outqty,
           CASE WHEN p.ProductType = 'S' THEN 0 ELSE sum(CASE WHEN movementtype <> ALL (ARRAY['M+', 'M-']) AND docsubtypeinv IN ('IU','PI') THEN movementqty ELSE 0 END) END autoqty,
           max(precio_venta.priceentered) priceentered,
           t.m_product_id,costo.newcostprice costo,min(t.movementdate) movementdate
           FROM m_transaction_v t JOIN M_product p ON t.m_product_id=p.m_product_id
           LEFT JOIN (SELECT dt.docsubtypeinv,m_inventoryline_id
                      FROM m_inventoryline il
                      JOIN m_inventory ON m_inventory.m_inventory_id = il.m_inventory_id 
                      JOIN c_doctype dt ON dt.c_doctype_id = m_inventory.c_doctype_id 
                      WHERE m_inventory.docstatus='CO') il ON il.m_inventoryline_id = t.m_inventoryline_id
           LEFT JOIN (SELECT il.m_inoutline_id,c_orderline.priceentered,c_orderline.pricelist 
                      FROM m_inoutline il 
                      JOIN m_inout ON m_inout.m_inout_id = il.m_inout_id 
                      JOIN c_orderline ON c_orderline.c_orderline_id = il.c_orderline_id) precio_venta ON precio_venta.m_inoutline_id = t.m_inoutline_id AND t.movementtype IN ('C-','C+')
		   LEFT JOIN (SELECT costo.newcostprice,iol.m_inoutline_id,costo.m_product_id 
					  FROM M_CostMovement_v costo 
					  JOIN m_inoutline iol ON costo.c_orderline_id=iol.c_orderline_id 	   
					  WHERE costo.m_costelement_id=v_m_costelement_id AND oldcostprice !=newcostprice) costo ON costo.m_product_id = t.m_product_id                       
           WHERE 
           movementdate >= p_startdate AND movementdate <= p_enddate
           GROUP BY t.m_product_id,ProductType,costo.newcostprice) invf ON invf.m_product_id = pp.m_product_id 
WHERE pp.m_pricelist_version_id = v_price_list_historic AND pp.AD_Client_ID = p_ad_client_id
and (p.m_product_id = p_m_product_id OR p_m_product_id is null)
and (p.m_product_category_id = p_m_product_category_id OR p_m_product_category_id is null)
group by p.m_product_id,pp.pricelist,p.value,p.name,invi.movementqty,invf.inqty,invf.outqty,invf.autoqty
order by p.m_product_id,min(movementdate)
LOOP
c_productname :=var_rr.name;
c_productvalue :=var_rr.value;
c_costhistoric := var_rr.costo_historico;
c_qtyinicial :=var_rr.qty_ini;
c_inqty:=var_rr.qty_in;
c_outqty:=var_rr.qty_out;
c_autoqty:=var_rr.qty_auto;
c_qtyfinal:=var_rr.qty_final;
c_currentcost_inicial := var_rr.costo_inicial;
c_currentcost_final := var_rr.costo_final;
c_priceentered:=var_rr.precio_venta;
c_currentcost_actual:=var_rr.costo_actual;
c_m_product_id :=var_rr.m_product_id;
RETURN NEXT;
END LOOP;	
RAISE NOTICE 'FIN';
     END;
$BODY$;
 
