Edit Report:Salesman Sales by Location (SWRS)
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Salesman Sales by Location (SWRS)
-- Salesman sales for customer by location and date range
-- VARIABLE: {
-- name: "range",
-- display: "Date Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- 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:false,
-- empty:false,
-- }
-- FILTER: {
-- filter: "class",
-- column: "Wholesale $",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Retail $",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Wholesale Units",
-- params: {
-- class: "center"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Retail Units",
-- params: {
-- class: "center"
-- }
-- }
-- ROLLUP: {
-- columns: {
-- "Street Units": "{{sum}}",
-- "Store Units": "{{sum}}",
-- "Street $": "{{sum}}",
-- "Store $": "{{sum}}",
-- "Web $": "{{sum}}"
-- }
-- }
--
select
salesperson as `Salesman ID`,
salesmanname as `Salesman Name`,
debtorno as `Customer #`,
brname as `Customer Name`,
case
when isnull((
select
COUNT(distinct transno)
from stocktrans st2
where salesperson = {{salesman}} and placedfrom = 4 and trandate >= DATE('{{range.start}}') and trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)) then 0
else (
select
COUNT(distinct transno)
from stocktrans st2
where salesperson = {{salesman}} and placedfrom = 4 and trandate >= DATE('{{range.start}}') and trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)
end as `Web Order Count`,
case
when isnull((
select
COUNT(distinct transno)
from stocktrans st2
where salesperson = {{salesman}} and fromstkloc = '00' and not placedfrom = 4 and trandate >= DATE('{{range.start}}') and trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)) then 0
else (
select
COUNT(distinct transno)
from stocktrans st2
where salesperson = {{salesman}} and fromstkloc = '00' and not placedfrom = 4 and trandate >= DATE('{{range.start}}') and trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)
end as `Street Order Count`,
case
when isnull((
select
COUNT(distinct transno)
from stocktrans st2
where salesperson = {{salesman}} and not fromstkloc = '00' and not placedfrom = 4 and trandate >= DATE('{{range.start}}') and trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)) then 0
else (
select
COUNT(distinct transno)
from stocktrans st2
where salesperson = {{salesman}} and not fromstkloc = '00' and not placedfrom = 4 and trandate >= DATE('{{range.start}}') and trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)
end as `Store Order Count`,
case
when isnull((
select
cast((sum(discountedlinetotal) * -1) as decimal (10, 2))
from stocktrans where salesperson = st1.salesperson and placedfrom = 4 and stocktrans.trandate >= DATE('{{range.start}}') and stocktrans.trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)) then 0
else (
select
cast((sum(discountedlinetotal) * -1) as decimal (10, 2))
from stocktrans where salesperson = st1.salesperson and placedfrom = 4 and stocktrans.trandate >= DATE('{{range.start}}') and stocktrans.trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)
end as `Web $`,
case
when isnull((
select
cast((sum(discountedlinetotal) * -1) as decimal (10, 2))
from stocktrans where salesperson = st1.salesperson and stocktrans.loccode = '00' and not placedfrom = 4 and stocktrans.trandate >= DATE('{{range.start}}') and stocktrans.trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)) then 0
else (
select
cast((sum(discountedlinetotal) * -1) as decimal (10, 2))
from stocktrans where salesperson = st1.salesperson and stocktrans.loccode = '00' and not placedfrom = 4 and stocktrans.trandate >= DATE('{{range.start}}') and stocktrans.trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)
end as `Street $`,
case
when isnull((
select
cast((sum(discountedlinetotal) * -1) as decimal (10, 2))
from stocktrans where salesperson = st1.salesperson and not stocktrans.loccode = '00' and not placedfrom = 4 and stocktrans.trandate >= DATE('{{range.start}}') and stocktrans.trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)) then 0
else (
select
cast((sum(discountedlinetotal) * -1) as decimal (10, 2))
from stocktrans where salesperson = st1.salesperson and not stocktrans.loccode = '00' and not placedfrom = 4 and stocktrans.trandate >= DATE('{{range.start}}') and stocktrans.trandate <= DATE('{{range.end}}') and debtorno = st1.debtorno group by debtorno, salesperson
)
end as `Store $`
from
stocktrans st1
where
st1.trandate >= DATE('{{range.start}}')
and st1.trandate <= DATE('{{range.end}}')
and st1.salesperson = '{{salesman}}'
group by
debtorno
having
`Street $` > 0 or `Store $` > 0 or `Store Order Count` > 0 or `Street Order Count` > 0 or `Web Order Count` > 0 or `Web Order Count` > 0;