Edit Report:Supplier Monthly Sales Out
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Supplier Monthly Sales Out
-- Enter supplier code.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- VARIABLE: {
-- name: "supplierno",
-- display: "Supplier Code",
-- type: "text",
-- }
-- VARIABLE: {
-- name: "groupby",
-- display: "Group By",
-- type: "select",
-- multiple: false,
-- options: [{display: 'fromstkloc', value:'fromstkloc'},{display: 'Salesperson', value:'salesperson'}],
-- }
-- ROLLUP: {
-- columns: {
-- "TOTALSALES": "Totalsales: {{sum}}"
-- }
-- }
SELECT
supplierno,
suppliername,
fromstkloc,
salesperson,
salesmanname,
IF
( 'fromstkloc' = 'salesperson', salesmanname, locations.locationname ) AS 'fromstkloc',
cast( sum( discountedlinetotal * - 1 ) AS DECIMAL ( 10, 2 ) ) AS TOTALSALES
FROM
stocktrans s
INNER JOIN locations ON locations.loccode = fromstkloc
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND supplierno like '{{ supplierno }}%'
and d.typeid NOT IN ('6', '8','10','21')
{% if groupby == 'fromstkloc' %}
GROUP BY fromstkloc, supplierno
{% endif %}
{% if groupby == 'salesperson' %}
GROUP BY salesperson, supplierno
{% endif %}
HAVING TOTALSALES > 0
{% if groupby == 'fromstkloc' %}
ORDER BY fromstkloc
{% endif %}
{% if groupby == 'salesperson' %}
ORDER BY salesperson
{% endif %}