-- Customer Sales for a Product Line
-- PLACE HOLDER REPORT DESCRIPTION
-- PLACE HOLDER REPORT FILTERS AND DRILL DOWN
-- VARIABLE: {
--      name: "range",
--      display: "Report Range",
--      type: "daterange",
--      default: { start: "first day of this month", end: "last day of this month" }
-- }
-- VARIABLE: {
--   name: "brand",
--   display: "Brand",
--   type: "text",
-- }
-- VARIABLE: {
--   name: "salesman",
--   display: "Salesman",
--   type: "select",
--   database_options: { table: "salesman", display: "salesmanname", column: "salesmancode", where: " current = 1 " , order:{ column: "salesmanname" ,  order: "ASC"} },
--   multiple:true,
-- }
-- ROLLUP: {
--   columns: {
--      "TOTAL": "Total: {{sum}}"
--   }


SELECT
    s.debtorno AS CUSTOMERNO,
    name AS CUSTOMER,
    brand as BRAND,
    CAST(sum( discountedlinetotal * - 1 ) as DECIMAL(10,2)) AS TOTAL 
FROM
	stocktrans s
    Left Join debtorsmaster d ON s.debtorno = d.debtorno
where trandate between "{{ range.start }}" AND "{{ range.end }}" 
and brand like '{{ brand }}%'
{% if salesman|length > 0 %}
    and salesperson in (
	{% for person in salesman %}
		'{{ person }}'
		{{ not loop.last ? ',' }}
	{% endfor %})
{% endif %}
group by s.debtorno, brand
order by TOTAL desc, CUSTOMER asc