Edit Report:SalesMan Customer Sales By Date
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- SalesMan Customer Sales By Date
-- All Customers With and Without Sales
-- By Date..
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "yesterday", 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) " , order:{ column: "salesmanname" , order: "ASC"} },
-- multiple:true,
-- }
-- VARIABLE: {
-- name: "braddress4",
-- display: "PROVINCE",
-- type: "select",
-- database_options: { table: "custbranch", display: "braddress4", column: "braddress4", where:" braddress4 <> '' and store_location=0 ", order:{ column: "braddress4" , order: "ASC"} },
-- multiple:true,
-- empty: true,
-- }
-- ROLLUP: {
-- columns: {
-- "NETSALES": "Total: {{sum}}"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "NETSALES",
-- params: {
-- class: "text-right"
-- }
-- }
-- VARIABLE: {
-- name: "zerosales",
-- display: "Zero Sales?",
-- type: "select",
-- multiple: false,
-- empty: true,
-- options: [{display: 'No', value:''},{display: 'Yes', value:'having NETSALES = 0 '}],
-- }
SELECT
debtorno as DEBTORNO,
branchcode as BRANCH,
brname as 'NAME',
braddress3 as 'CITY',
braddress4 as 'PROVINCE',
phoneno as 'PHONE NUMBER',
email as 'e-mail',
contactname as 'CONTACT NAME',
s.salesmanname as 'SALESMAN',
(select DATE_FORMAT(MAX(trandate), "%d/%m/%Y")
from stocktrans where debtorno=b.debtorno
and branchcode =b.branchcode
)
as LASTPURCHASE,
case WHEN (
select sum(discountedlinetotal * -1)
from stocktrans where
debtorno=b.debtorno and branchcode =b.branchcode
and trandate between "{{ range.start }}" AND "{{ range.end }}"
) is NULL
then 0.00
else
(select sum(discountedlinetotal * -1)
from stocktrans where debtorno=b.debtorno
and branchcode =b.branchcode
and trandate between "{{ range.start }}" AND "{{ range.end }}"
)
end as 'NETSALES',
case WHEN (
select sum(discountedlinetotal * -1)
from stocktrans where
debtorno=b.debtorno and branchcode =b.branchcode
and trandate between "{{ range.start }}" AND "{{ range.end }}"
and loccode <> '00' ) is NULL
then '0.00'
else
(select sum(discountedlinetotal * -1)
from stocktrans where debtorno=b.debtorno
and branchcode =b.branchcode
and trandate between "{{ range.start }}" AND "{{ range.end }}"
and loccode <> '00'
)
end as 'STORESALES',
case WHEN (
select sum(discountedlinetotal * -1)
from stocktrans where
debtorno=b.debtorno and branchcode =b.branchcode
and trandate between "{{ range.start }}" AND "{{ range.end }}"
and loccode = '00'
and placedfrom is NULL
) is NULL
then 0.00
else
(select sum(discountedlinetotal * -1)
from stocktrans where debtorno=b.debtorno
and branchcode =b.branchcode
and trandate between "{{ range.start }}" AND "{{ range.end }}"
and loccode='00'
and placedfrom is NULL
)
end as 'BACKOFFICE',
case WHEN (
select sum(discountedlinetotal * -1)
from stocktrans where
debtorno=b.debtorno and branchcode =b.branchcode
and trandate between "{{ range.start }}" AND "{{ range.end }}"
and loccode='00'
and placedfrom = 4
) is NULL
then 0.00
else
(select sum(discountedlinetotal * -1)
from stocktrans where debtorno=b.debtorno
and branchcode =b.branchcode
and trandate between "{{ range.start }}" AND "{{ range.end }}"
and loccode='00'
and placedfrom = 4
)
end as 'ECOM',
case WHEN (
select sum(discountedlinetotal * -1)
from stocktrans where
debtorno=b.debtorno and branchcode =b.branchcode
and trandate between "{{ range.start }}" AND "{{ range.end }}"
and loccode='00'
and placedfrom = 3
) is NULL
then 0.00
else
(select sum(discountedlinetotal * -1)
from stocktrans where debtorno=b.debtorno
and branchcode =b.branchcode
and trandate between "{{ range.start }}" AND "{{ range.end }}"
and loccode='00'
and placedfrom = 3
)
end as 'SALESMAN ENTRY'
FROM
custbranch b
join salesman s on b.salesman = s.salesmancode
WHERE
1
{% if salesman|length > 0 %}
and salesman in (
{% for person in salesman %}
'{{ person }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
and disabletrans = 0
{% if braddress4[0] != '' %}
{% if braddress4|length > 0 %}
and braddress4 in (
{% for prov in braddress4 %}
'{{ prov }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
{% endif %}
{{ zerosales }}
order by NETSALES desc