Edit Report:CSSV - Customer Sales by Salesman / Vendor
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- CSSV - Customer Sales by Salesman / Vendor
-- CSSV
-- ^sales
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- VARIABLE: {
-- name: "location",
-- display: "Branch",
-- type: "select",
-- multiple: true,
-- empty: true,
-- database_options: { table: "locations", display: "locationname", column: "loccode", order:{ column: "loccode" , order: "ASC"} },
-- }
-- VARIABLE: {
-- name: "vendor",
-- display: "Vendor",
-- type: "select",
-- database_options: { table: "purchdata", display: "supplierno", column: "supplierno", order:{ column: "supplierno" , order: "ASC"} },
-- multiple:true,
-- empty:true,
-- }
-- VARIABLE: {
-- name: "salesman",
-- display: "Salesperson",
-- type: "select",
-- database_options: { table: "salesman", display: "salesmanname", where: " group_id = 1 ", column: "salesmancode", order:{ column: "salesmancode" , order: "ASC"} },
-- multiple:true,
-- empty:true,
-- }
-- ROLLUP: {
-- columns: {
-- "Sales": "{{ sum }}",
-- }
--}
-- FILTER: {
-- filter: "class",
-- column: "Sales",
-- params: {
-- class: "right"
-- }
-- }
select {% if location == '' %} '99' {% else %} loccode {% endif %} as `Branch`, purchdata.supplierno as `Vendor`, salesperson as `DSC` , stocktrans.debtorno as `Customer`,
brname as `Name`, round(sum(discountedlinetotal * -1),2) as `Sales`, round(sum(qty * -1),0) as `Qty Sold`, address1 as `Address`, address3 as `City`, address4 as `State`, address5 as `Zip` from stocktrans
inner join purchdata on stocktrans.stockid = purchdata.stockid and preferred = 1
inner join debtorsmaster on stocktrans.debtorno = debtorsmaster.debtorno
where trandate between "{{ range.start }}" AND "{{ range.end }}"
{% if location|first != '' and location is not empty %}
and loccode in (
{% for loc in location %}
{% if loc != '' %}
'{{ loc }}'
{{ not loop.last ? ',' }}
{% endif %}
{% endfor %}
)
{% endif %}
{% if vendor is not empty %}
and purchdata.supplierno in (
{% for v1 in vendor %}
'{{ v1 }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
{% endif %}
{% if salesman|first != '' and salesman is not empty %}
and salesperson in (
{% for s1 in salesman %}
'{{ s1 }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
{% endif %}
group by `Vendor`, `DSC`, stocktrans.debtorno
order by `Vendor` ASC, `DSC` ASC, `Customer` ASC