Edit Report:PCSLS
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- PCSLS
-- Customer / Product Line Sales
-- ^customers
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- VARIABLE: {
-- name: "plines",
-- display: "Line",
-- type: "select",
-- database_options: { table: "productlines", display: "line_description", column: "line_field", order:{ column: "line_description" , order: "ASC"} },
-- multiple:true,
-- empty: true
-- }
-- VARIABLE: {
-- name: "salesman",
-- display: "Salesman",
-- type: "select",
-- database_options: { table: "salesman", display: "salesmanname", column: "salesmancode", where: " current = 1 and group_id = 1" , order:{ column: "salesmanname" , order: "ASC"} },
-- multiple:true,
-- empty: true
-- }
-- VARIABLE: {
-- name: "location",
-- display: "Branches",
-- type: "select",
-- database_options: { table: "locations", display: "locationname", column: "loccode", order:{ column: "locationname" , order: "ASC"} },
-- multiple:true,
-- empty: true
-- }
-- VARIABLE: {
-- name: "conbr",
-- display: "Consolidated or Customer",
-- type: "select",
-- multiple: false,
-- options: [{display: 'Consolidated', value:'C'},{display: 'Customer Detail', value:'B'}],
-- }
-- ROLLUP: {
-- columns: {
-- "SALES": "{{sum}}",
-- "QTY": "{{sum}}"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "SALES",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "QTY",
-- params: {
-- class: "center"
-- }
-- }
SELECT
loccode as 'BR',
lineid as LINE,
(select line_description from productlines where line_field = lineid) as 'LINE DESC',
salesperson as 'SALES ID',
salesmanname as 'SALES NAME',
CAST( (SUM(discountedlinetotal)*-1) as DECIMAL(10,2)) as SALES,
(SUM(qty)*-1) as QTY
FROM
stocktrans
WHERE
1
and date(trandate) between date("{{ range.start }}") AND date("{{ 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 salesman|first != '' and salesman is not empty %}
and salesperson in (
{% for line in salesman %}
'{{ line }}'
{{ not loop.last ? ',' }}
{% endfor %}
)
{% endif %}
{% if plines|first != '' and plines is not empty %}
and lineid in (
{% for line in plines %}
'{{ line }}'
{{ not loop.last ? ',' }}
{% endfor %}
)
{% endif %}
{% if conbr == 'C' %}
group by lineid
{% else %}
GROUP BY
debtorno,
lineid
{% endif %}
order by 'LINE DESC' asc, BR asc