
report = new com.openbravo.pos.reports.PanelReportBean();

report.setTitleKey("Menu.Inventory_stock_value_supplier");
report.setReport("/com/openbravo/reports/inventory_stock_value_supplier");
report.setResourceBundle("com/openbravo/reports/inventory_messages");
 
report.setSentence("SELECT " +
                "'All' AS ALLLOCATION,  " +
                "locations.id AS LOCATIONID,  " +
                "locations.NAME AS LOCATIONNAME,  " +
                "categories.id AS CATEGORYID,   " +
                "categories.NAME AS CATEGORYNAME,  " +
                "suppliers.name as SUPPLIER, " +
                "products.code AS PRODUCT_CODE, " +
                "products.NAME AS PRODUCT_NAME, " +
                "products.pricebuy AS PRECIO_COMPRA, " +
                "stockcurrent.UNITS,  " +
                "taxes.rate AS IMPUESTO, " +
                "round(products.pricesell*(1+taxes.rate)) AS PRECIO_VENTA, " +
                "stockcurrent.UNITS*round(products.pricesell*(1+taxes.rate)) AS VALOR_EN_INVENTARIO, " +
                "round(((round(products.pricesell)/(products.pricebuy))-1)*100,2) as MARGEN " +
            "FROM stockcurrent  " + 
                "JOIN locations ON stockcurrent.LOCATION = locations.ID  " +
                "JOIN products ON stockcurrent.PRODUCT = products.ID  " +
                "inner join suppliers on suppliers.id = products.supplier   " +
                "JOIN categories ON products.CATEGORY = categories.ID   " +
                    "LEFT OUTER JOIN stocklevel ON stockcurrent.LOCATION = stocklevel.LOCATION AND stockcurrent.PRODUCT = stocklevel.PRODUCT  " +
            	    "LEFT OUTER JOIN attributesetinstance ON stockcurrent.ATTRIBUTESETINSTANCE_ID = attributesetinstance.ID  " +
		"inner join taxes on taxes.id = products.taxcat " + 
            "WHERE stockcurrent.UNITS > 0 " +
            "ORDER BY LOCATIONNAME, CATEGORYNAME, PRODUCT_NAME");

 
report.addField("ALLLOCATION", com.openbravo.data.loader.Datas.STRING);
report.addField("LOCATIONID", com.openbravo.data.loader.Datas.STRING);
report.addField("LOCATIONNAME", com.openbravo.data.loader.Datas.STRING);
report.addField("CATEGORYID", com.openbravo.data.loader.Datas.STRING);
report.addField("CATEGORYNAME", com.openbravo.data.loader.Datas.STRING);
report.addField("SUPPLIER", com.openbravo.data.loader.Datas.STRING);
report.addField("PRODUCT_CODE", com.openbravo.data.loader.Datas.STRING);
report.addField("PRODUCT_NAME", com.openbravo.data.loader.Datas.STRING);
report.addField("PRECIO_COMPRA", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("UNITS", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("IMPUESTO", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("PRECIO_VENTA", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("VALOR_EN_INVENTARIO", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("MARGEN", com.openbravo.data.loader.Datas.DOUBLE);

report;