Edit Report:Loyalty Points from Store Purchases
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Loyalty Points from Store Purchases
-- Transaction History
--
-- VARIABLE: {
-- name: "customer",
-- display: "Customer Number",
-- type: "text",
-- }
-- ROLLUP: {
-- columns: {
-- "Points Earned": "Total : {{sum}}",
-- "Points Used": "Total : {{sum}}",
-- "Points Remaining from Transaction": "Total : {{sum}}",
-- }
-- }
--
SELECT debtortrans.debtorno as `Customer Number`,
debtortrans.transno as `Invoice Number`,
salesorders.orddate as `Order Date`,
salesorders.fromstkloc as `Location`,
pointvalue as `Points Earned`,
spentpoints as `Points Used`,
pointvalue - spentpoints AS `Points Remaining from Transaction`
FROM loyaltytrans INNER JOIN loyaltyaccount ON loyaltytrans.loyaltyid = loyaltyaccount.loyaltyid
INNER JOIN salesorders ON salesorders.debtorno = loyaltyaccount.debtorid and salesorders.orddate = loyaltytrans.postdate
INNER JOIN debtortrans ON salesorders.orderno = debtortrans.order_
WHERE salesorders.debtorno = '{{customer}}' AND placedfrom = 5 AND debtortrans.type = 10 AND pointvalue = FLOOR(ovamount)
UNION ALL
SELECT loyaltyaccount.debtorid as `Customer Number`,
rewardtype as `Invoice Number`,
loyaltytrans.postdate as `Order Date`,
'00' as `Location`,
pointvalue as `Points Earned`,
spentpoints as `Points Used`,
pointvalue - spentpoints AS `Points Remaining from Transaction`
FROM loyaltytrans INNER JOIN loyaltyaccount ON loyaltytrans.loyaltyid = loyaltyaccount.loyaltyid
where loyaltyaccount.debtorid = '{{customer}}' and rewardtype like 'Adjustment%';