Edit Report:%FILL REPORT
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- %FILL REPORT
--
--
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- VARIABLE: {
-- name: "debtorno",
-- display: "Customer #",
-- type: "text",
-- }
-- VARIABLE: {
-- name: "vendor",
-- display: "Supplier",
-- type: "select",
-- database_options: { table: "suppliers", display: "suppname", column: "supplierid", where: " supptype = 2 " , order:{ column: "suppname" , order: "ASC"} },
-- multiple:true,
-- empty: true,
-- }
-- ROLLUP: {
-- columns: {
-- "Ordered": "Total Ordered: {{sum}}",
-- "Invoiced": "Total Invoiced: {{sum}}",
-- "Filled %": "AVG : {{ average | round(3) }}%"
-- }
-- }
SELECT
p.supplierno as 'Supplier#',
s.debtorno as 'Customer#',
d.name as 'Name',
stkcode as 'Item#',
sm.description as 'Description',
sum(quantity) as 'Ordered',
sum(qtyinvoiced) as 'Invoiced',
CONCAT(CAST(( sum(qtyinvoiced) / sum(quantity) * 100 ) AS DECIMAL(10,3) ) , '%') AS 'Filled %'
FROM
salesorderdetails sd
JOIN salesorders s ON s.orderno = sd.orderno
join debtorsmaster d on d.debtorno = s.debtorno
JOIN stockmaster sm ON sd.stkcode = sm.stockid
JOIN purchdata p ON p.stockid = sm.stockid
WHERE
1
{% if debtorno != '' %}
AND s.debtorno = '{{ debtorno }}'
{% endif %}
{% if vendor[0] != '' %}
{% if vendor|length > 0 %}
and p.supplierno in (
{% for prov in vendor %}
'{{ prov }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
{% endif %}
AND actualdispatchdate BETWEEN "{{ range.start }}" AND "{{ range.end }}"
and s.fromstkloc = '00'
-- and completed = 1
group by stkcode