Customer Purchase History Reporting

V5 Tutorial
4

#Scripts

##SQL - @@PurchaseHistory

Pasted image762×500 39.2 KB

##PurchaseHistory [@@PurchaseHistory] (SQL)##

Script Name: PurchaseHistory
Script Handler: @@PurchaseHistory

Script:

-- PARM for Entity Type
declare @EntityType varchar(20)  = '@1'
-- PARM for Entity Name
declare @EntityName varchar(20)  = '@2'
-- PARM for Date Filter Start
declare @StartDateIn varchar(25) = '@3'
-- PARM for Date Filter End
declare @EndDateIn varchar(25)   = '@4'
-- PARM for Entity Id
declare @EntityId varchar(20)    = '@5'

-- if Entity Type is invalid, set default as 'Customers'
IF (@EntityType = '') OR (@EntityType is null) OR (@EntityType = '$1') SET @EntityType = 'Customers'

-- if Date Filter START is invalid, set default to beginning of Current Month
IF (@StartDateIn = '') OR (@StartDateIn is null) OR (@StartDateIn = '$3') SET @StartDateIn = left(CONVERT(VARCHAR(25), GETDATE(), 126),7)+'-01T00:00:00'

-- if Date Filter END is invalid, set a default
IF (@EndDateIn = '') OR (@EndDateIn is null) OR (@EndDateIn = '$4') SET @EndDateIn  = dateadd(Month,1,@StartDateIn)

-- set START and END date for Report Period
declare @StartDate datetime = convert(varchar(25),@StartDateIn,126)
declare @EndDate datetime = convert(varchar(25),@EndDateIn,126)
declare @EndDateInc datetime = convert(varchar(25),dateadd(day,-1,@EndDate),126)

SELECT
--[EntityName]
--,[Date]
--,[TicketId]
--,[TicketNumber]
[MenuItemName]
,[PortionName]
,sum([Quantity]) as [Qty]
,[Price]
,(sum([Quantity])*[Price]) as [ItemTotal]
FROM (

SELECT
 o.[Id]
,[TicketId]
,tkt.[TicketNumber]
,e.[Name] as [EntityName]
,[MenuItemName]
,[PortionName]
,[Price]
,[Quantity]
,([Price] * [Quantity]) as [ItemTotal]
,left(convert(varchar(30),[CreatedDateTime],126),10) as [Date]
FROM [Orders] o
JOIN [Tickets] tkt on tkt.[Id] = o.[TicketId]
JOIN [TicketEntities] te on te.[Ticket_Id] = tkt.[Id]
JOIN [EntityTypes] et on et.[Id] = te.[EntityTypeId] and et.[Name] = @EntityType
JOIN [Entities] e on e.[Id] = te.[EntityId]
WHERE 1=1
and e.[Name] = @EntityName
and e.[Id] = @EntityId
and o.[CreatedDateTime] >= @StartDate
and o.[CreatedDateTime] <  @EndDate
--ORDER BY o.[TicketId], o.[CreatedDateTime]

) allorders

GROUP BY
--[EntityName]
--,[Date]
--,[TicketId]
--,[TicketNumber]
[MenuItemName]
,[PortionName]
,[Price]

ORDER BY 
 [Qty] desc
, [MenuItemName]

:bulb: To exclude Void and Gift Orders from the Purchase history, modify the WHERE portion of the above SQL as such:

WHERE 1=1
and e.[Name] = @EntityName
and e.[Id] = @EntityId
and o.[CreatedDateTime] >= @StartDate
and o.[CreatedDateTime] <  @EndDate
and o.[CalculatePrice]  =  1 -- EXCLUDE Void and Gift Items

##SQL - @@PurchaseHistoryTTL

Pasted image762×500 39.4 KB

##PurchaseHistoryTTL [@@PurchaseHistoryTTL] (SQL)##

Script Name: PurchaseHistoryTTL
Script Handler: @@PurchaseHistoryTTL

Script:

-- PARM for Entity Type
declare @EntityType varchar(20)  = '@1'
-- PARM for Entity Name
declare @EntityName varchar(20)  = '@2'
-- PARM for Date Filter Start
declare @StartDateIn varchar(25) = '@3'
-- PARM for Date Filter End
declare @EndDateIn varchar(25)   = '@4'
-- PARM for Entity Id
declare @EntityId varchar(20)    = '@5'

-- if Entity Type is invalid, set default as 'Customers'
IF (@EntityType = '') OR (@EntityType is null) OR (@EntityType = '$1') SET @EntityType = 'Customers'

-- if Date Filter START is invalid, set default to beginning of Current Month
IF (@StartDateIn = '') OR (@StartDateIn is null) OR (@StartDateIn = '$3') SET @StartDateIn = left(CONVERT(VARCHAR(25), GETDATE(), 126),7)+'-01T00:00:00'

-- if Date Filter END is invalid, set a default
IF (@EndDateIn = '') OR (@EndDateIn is null) OR (@EndDateIn = '$4') SET @EndDateIn  = dateadd(Month,1,@StartDateIn)

-- set START and END date for Report Period
declare @StartDate datetime = convert(varchar(25),@StartDateIn,126)
declare @EndDate datetime = convert(varchar(25),@EndDateIn,126)
declare @EndDateInc datetime = convert(varchar(25),dateadd(day,-1,@EndDate),126)

SELECT
'TOTAL'
,'',null,null
,sum([TotalAmount]) as [TotalAmount]
FROM [Tickets] tkt
JOIN [TicketEntities] te on te.[Ticket_Id] = tkt.[Id]
JOIN [Entities] e on e.[Id] = te.[EntityId]
WHERE 1=1
and e.[Id] = @EntityId
and [Date] >= @StartDate
and [Date] <  @EndDate
2 Likes