Edit Report:GK Location Sales
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- GK Location Sales
-- Enter supplier code.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- VARIABLE: {
-- name: "groupby",
-- display: "Group By",
-- type: "select",
-- multiple: false,
-- options: [{display: 'fromstkloc', value:'fromstkloc'},{display: 'placedfrom', value:'placedfrom'}],
-- }
-- ROLLUP: {
-- columns: {
-- "TOTALSALES": "Totalsales: {{sum}}"
-- }
-- }
SELECT
suppliername,
fromstkloc,
placedfrom,
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 IN ( 'GK', 'KS', 'VA', 'KM' )
AND d.typeid NOT IN ('6', '8','10','21')
{% if groupby == 'fromstkloc' %}
GROUP BY fromstkloc
{% endif %}
{% if groupby == 'placedfrom' %}
GROUP BY placedfrom
{% endif %}
HAVING TOTALSALES > 0
{% if groupby == 'fromstkloc' %}
ORDER BY fromstkloc
{% endif %}
{% if groupby == 'placedfrom' %}
ORDER BY placedfrom
{% endif %}