Edit Report:Moroccan Oil Inventory (MOI)
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Moroccan Oil Inventory (MOI)
-- MOI Export
-- ^sales
-- VARIABLE: {
-- name: "location",
-- display: "Branches",
-- type: "select",
-- database_options: { table: "locations", display: "locationname", column: "loccode", order:{ column: "loccode" , order: "ASC"} },
-- multiple:true,
-- empty:true,
-- }
-- ROLLUP: {
-- columns: {
-- "QOH": "{{ sum }}",
-- "Value": "{{ sum }}"
-- }
--}
-- FILTER: {
-- filter: "class",
-- column: "QOH",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Value",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "twig",
-- column: "Value",
-- params: {
-- template: "{{ value | number_format(2, '.', '')}}"
-- }
-- }
--
SELECT DATE_FORMAT(NOW(), '%m/%d/%Y') as `Report Date`,
'A&A Beauty Supply' as `Distributor`, (select code from labeltoitem where item = stockmaster.stockid limit 1) as `Barcode`, stockmaster.stockid as `Item`,
mfgpart as `Vendor Part`,
locationname as `Branch`,
description as `Description`,
case when sum(quantity) < 0 then 0 else sum(quantity) end as `QOH`,
units as `UOM`,
case when sum(quantity) < 0 then 0.00 else (sum(quantity) * materialcost) end as `Value`
FROM stockmaster
INNER JOIN locstock on stockmaster.stockid = locstock.stockid
INNER JOIN locations on locstock.loccode = locations.loccode
WHERE (stockmaster.stockid like 'MOC%' OR stockmaster.stockid like 'MOD%' OR stockmaster.stockid like 'MOR%')
{% if location|first != '' and location is not empty %}
and locstock.loccode in (
{% for loc in location %}
{% if loc != '' %}
'{{ loc }}'
{{ not loop.last ? ',' }}
{% endif %}
{% endfor %}
)
{% endif %}
group by stockmaster.stockid, locstock.loccode
order by stockmaster.stockid ASC