Edit Report:Moroccan Oil Sellout Report
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Moroccan Oil Sellout Report
-- MOC, MOR, MOD
-- ^sales
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- 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: {
-- "Qty Sold": "{{ sum }}",
-- "Net Sales": "{{ sum }}"
-- }
--}
-- FILTER: {
-- filter: "class",
-- column: "Qty Sold",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Net Sales",
-- params: {
-- class: "right"
-- }
-- }
--
SELECT salesperson as DSC, trandate as `Invoice Date`,
'200656' as `Distributor`, suppliers_partno as `Vendor Part`, itemdesc as `Description`,
(case when enteredby = 'RO' then 'DSC' when enteredby = 'WO' then 'ONLINE' else 'STORE' end) as `Type`,
(qty * -1) as `Qty Sold`,
(discountedlinetotal * -1) as `Net Sales`,
CONCAT(stocktrans.debtorno,'.00') as `ClientId`,
debtorsmaster.name as `ClientName`,
address1 as `Address`,
address3 as `City`,
address4 as `State`,
address5 as `Zip Code`,
stocktrans.loccode as `StoreID`,
locationname as `Store Name`,
transno as `Invoice Number`,
typename as `Customer Type`
FROM stocktrans
LEFT JOIN purchdata on stocktrans.stockid = purchdata.stockid
LEFT JOIN debtorsmaster on stocktrans.debtorno = debtorsmaster.debtorno
LEFT JOIN debtortype on debtorsmaster.typeid = debtortype.typeid
LEFT JOIN locations on stocktrans.loccode = locations.loccode
WHERE stocktrans.trandate >= DATE('{{range.start}}') AND stocktrans.trandate <= DATE('{{range.end}}')
AND purchdata.preferred = 1
AND (stocktrans.stockid like 'MOC%' OR stocktrans.stockid like 'MOD%' OR stocktrans.stockid like 'MOR%' OR stocktrans.stockid = 'MOR99MISC')
{% if location|first != '' and location is not empty %}
and stocktrans.loccode in (
{% for loc in location %}
{% if loc != '' %}
'{{ loc }}'
{{ not loop.last ? ',' }}
{% endif %}
{% endfor %}
)
{% endif %}
order by trandate ASC, stocktrans.orderno ASC, stocktrans.stockid ASC