Edit Report:Luxury Brand Salon Sales Breakdown by Year.
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Luxury Brand Salon Sales Breakdown by Year.
-- Luxury Brand Managers Only. Select Year.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "year",
-- display: "Year",
-- type: "text",
-- }
-- VARIABLE: {
-- name: "salesperson",
-- display: "SalesPerson Number",
-- type: "text",
-- }
--
{% set suppliernames = ['Oribe', 'StephenJ', 'K18', 'Bob'] %}
{% set supliers = ['OR', 'SJ', 'AH', 'CBB'] %}
{% set months = [01,02,03,04,05,06,07,08,09,10,11,12] %}
{% set monthnames = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun' , 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'] %}
SELECT
c.debtorno,
c.branchcode,
`name`,
sm.salesmanname as salesperson,
{% for supplier in supliers %}
{% set loopi = loop.index - 1%}
{% for month in months %}
{% set loopitwo = loop.index - 1 %}
case when (select sum(discountedlinetotal * -1) from stocktrans where stocktrans.debtorno = c.debtorno and stocktrans.salesperson = c.salesman and supplierno ='{{ supplier }}'
and MONTH(trandate) = '{{ month }}' and YEAR(trandate)='{{ year }}') is NULL
then 0
else
(select sum(discountedlinetotal * -1) from stocktrans where stocktrans.debtorno = c.debtorno and stocktrans.salesperson = c.salesman and supplierno ='{{ supplier }}'
and MONTH(trandate) = '{{ month }}' and YEAR(trandate)='{{ year }}')
end as " {{ suppliernames[loopi] }} {{ monthnames[loopitwo] }}",
{% endfor %}
{% endfor %}
'' as '_'
FROM
custbranch c
join debtorsmaster d on d.debtorno = c.debtorno
join salesman sm on c.salesman = sm.salesmancode
WHERE
d.typeid = 7
{% if salesperson != '' %}
and sm.salesmancode ='{{ salesperson }}'
{%endif%}