Edit Report:TSMENU CUSTOMER SALES EXPORT
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- TSMENU CUSTOMER SALES EXPORT
-- From the TSMENU
--
-- VARIABLE: {
-- name: "range",
-- display: "This Year Range",
-- type: "daterange",
-- default: { start: "yesterday", end: "yesterday" }
-- }
-- VARIABLE: {
-- name: "range2",
-- display: "Last Year Range",
-- type: "daterange",
-- default: { start: "yesterday -1 year", end: "yesterday -1 year" }
-- }
-- VARIABLE: {
-- name: "range3",
-- display: "2 Year Range",
-- type: "daterange",
-- default: { start: "yesterday -2 year", end: "yesterday -2 year" }
-- }
--
SELECT
debtorsmaster.debtorno AS `Customer Number`,
custbranch.branchcode AS `Ship To`,
custbranch.brname AS `Customer Name`,
braddress1 AS `Address 1`,
braddress2 AS `Address2`,
braddress3 AS `City`,
braddress4 AS `ST`,
braddress5 AS `Zip`,
branchsalesmancode AS `ID`,
phoneno AS `Phone`,
debtorsmaster.salestype AS `PR CD`,
'' AS `DCOL`,
sum(
CASE WHEN stocktrans.trandate >= '{{ range.start }}' AND stocktrans.trandate <= '{{ range.end }}'
THEN ( qty * - 1.00 )
ELSE 0
END
) AS `Cur Yr Qty`,
sum(
CASE WHEN stocktrans.trandate >= '{{ range.start }}' AND stocktrans.trandate <= '{{ range.end }}'
THEN (-1.00 * discountedlinetotal )
ELSE 0
END
) AS `Cur Yr Sales`,
sum(
CASE
WHEN stocktrans.trandate >= '{{ range2.start }}' AND stocktrans.trandate <= '{{ range2.end }}'
THEN ( qty * - 1.00 )
ELSE 0
END
) AS `Last Yr Qty`,
sum(
CASE WHEN stocktrans.trandate >= '{{ range2.start }}' AND stocktrans.trandate <= '{{ range2.end }}'
THEN (-1.00 * discountedlinetotal )
ELSE 0
END
) AS `Last Yr Sales`,
sum(
CASE
WHEN stocktrans.trandate >= '{{ range3.start }}' AND stocktrans.trandate <= '{{ range3.end }}' THEN ( qty * - 1.00 ) ELSE 0
END
) AS `2 Yr Qty`,
sum(
CASE
WHEN stocktrans.trandate >= '{{ range3.start }}' AND stocktrans.trandate <= '{{ range3.end }}'
THEN (-1.00 * discountedlinetotal )
ELSE 0
END ) AS `2 Yr Sales`,
email AS `Email Address 1`,
'' AS `Email Address Description`
FROM
stocktrans
INNER JOIN debtorsmaster ON stocktrans.debtorno = debtorsmaster.debtorno
INNER JOIN custbranch ON custbranch.debtorno = debtorsmaster.debtorno AND custbranch.branchcode = stocktrans.branchcode
WHERE
trandate >= '{{range3.start}}'
AND trandate <= '{{range.end}}'
GROUP BY
stocktrans.debtorno
union
select
debtorsmaster.debtorno AS `Customer Number`,
custbranch.branchcode AS `Ship To`,
custbranch.brname AS `Customer Name`,
braddress1 AS `Address 1`,
braddress2 AS `Address2`,
braddress3 AS `City`,
braddress4 AS `ST`,
braddress5 AS `Zip`,
salesman AS `ID`,
phoneno AS `Phone`,
debtorsmaster.salestype AS `PR CD`,
'' AS `DCOL`,
0 as `Cur Yr Qty`,
0 as `Cur Yr Sales`,
0 as `Last Yr Qty`,
0 AS `Last Yr Sales`,
0 AS `2 Yr Qty`,
0 AS `2 Yr Sales`,
email AS `Email Address 1`,
'' AS `Email Address Description`
from custbranch INNER JOIN debtorsmaster ON custbranch.debtorno = debtorsmaster.debtorno
where
concat(custbranch.debtorno,custbranch.branchcode) not in (
SELECT CONCAT(debtorsmaster.debtorno ,custbranch.branchcode) FROM stocktrans INNER JOIN debtorsmaster ON stocktrans.debtorno = debtorsmaster.debtorno
INNER JOIN custbranch ON custbranch.debtorno = debtorsmaster.debtorno AND custbranch.branchcode = stocktrans.branchcode
where
trandate >= '{{range3.start}}'
AND trandate <= '{{range.end}}'
GROUP BY stocktrans.debtorno, stocktrans.branchcode
)