Edit Report:Customer Sales History (branch info)
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Customer Sales History (branch info)
-- Invoice Listing for a customer
-- PLACE HOLDER REPORT FILTERS AND DRILL DOWN
-- VARIABLE: {
-- name: "range",
-- display: "Date Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- VARIABLE: {
-- name: "debtorno",
-- display: "Customer Number",
-- type: "text",
-- }
-- VARIABLE: {
-- name: "branchcode",
-- display: "Branch Number",
-- type: "text",
-- }
-- ROLLUP: {
-- columns: {
-- "Sales Amt": "{{ sum }}",
-- "Tax Amt": "{{ sum }}",
-- "Freight Amt": "{{ sum }}",
-- "Total": "{{sum}}",
-- "Alloc Amt": "{{sum}}",
-- "Balance": "{{sum}}"
-- }
-- }
SELECT systypenames.typename as `Document Type`,
debtortrans.debtorno as `Customer Number`,
debtortrans.branchcode as `Branch`,
debtorsmaster.name as `Customer Name`,
debtortrans.order_ as `Order Number`,
transno as `Invoice Number`,
trandate as `Transaction Date`,
FORMAT(ovamount,2) as `Sales Amt`,
FORMAT(ovgst,2) as `Tax Amt`,
FORMAT (ovfreight,2) as `Freight Amt`,
FORMAT (ovdiscount,2) as `Add Chg/Disc Amt`,
FORMAT((ovamount + ovgst+ovfreight +ovdiscount),2) as `Total`,
FORMAT(alloc,2) as `Alloc Amt`,
FORMAT((ovamount + ovgst+ovfreight +ovdiscount -alloc),2) as `Balance`,
invtext as `Comments`
FROM debtortrans INNER JOIN debtorsmaster
ON debtortrans.debtorno=debtorsmaster.debtorno
INNER JOIN currencies
ON debtorsmaster.currcode=currencies.currabrev
LEFT JOIN salesorders on debtortrans.order_ = salesorders.orderno
inner join systypenames on systypenames.typeid = debtortrans.type
WHERE debtortrans.type IN (10, 11)
AND debtortrans.debtorno = '{{ debtorno }}'
AND debtortrans.branchcode like '%{{ branchcode }}%'
AND trandate>='{{range.start}}'
AND trandate<='{{range.end}}'
ORDER BY debtortrans.debtorno, debtortrans.branchcode, debtortrans.type, debtortrans.trandate