Edit Report:Total Invoices by Rep
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Total Invoices by Rep
-- Select Date Range. Select Sales Rep or All.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- VARIABLE: {
-- name: "salesman",
-- display: "Salesman",
-- type: "select",
-- database_options: { table: "salesman", display: "salesmanname", column: "salesmancode", where: " current = 1 and commissionrate2 in (1,5)and salesmancode not in (08,28,00, 22,03, 04, 25, 26, 39,86, 87, 18, 101, 30,38,102) " , order:{ column: "salesmanname" , order: "ASC"} },
-- multiple:true,
-- }
-- ROLLUP: {
-- columns: {
-- "Total Sales": "Total: {{sum}}"
-- }
-- }
--
SELECT
MONTH(trandate) AS 'Month',
YEAR (trandate) AS 'Year',
salesmanname AS 'Rep',
salesperson,
sum( discountedlinetotal * - 1 ) AS 'Total Sales',
count(distinct orderno) AS 'Total Invoices'
FROM
stocktrans s
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND loccode = '00'
AND type ='10'
AND salesperson in (select salesmancode from salesman where commissionrate1 =1 )
AND salesperson NOT IN ('08','28','00', '22','03', '04', '25', '26', '39','86', '87', '18', '101', '30','38','102')
AND d.typeid NOT IN ('6', '8', '10', '21')
{% 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
salesmanname
ORDER BY
salesmanname asc