Customer Purchase History Reporting


#1

This is a Report to show Historical Purchases on a per-Customer basis. You can select a Date Range to Report on and use Entity Search to load any Customer Purchase History.

:warning: This Tutorial uses to SQL scripts to pull data from the [Orders] table and the [Tickets] table. The data in the [Orders] table does not include Additional costs or Discount values that might be applied via Order Tag Prices, so the Item Total Prices could be incorrect (too high or too low). Note in the screenshot above, this is evident since the Item Totals do not match the Grand TOTAL.

:bulb: The other thing to note is that the Orders SQL query as shown here will also include Void and Gift Items, but the Ticket SQL query will not include them, since it is strictly pulling the Ticket Total. To exclude Void and Gift Items, you should add as part of the WHERE clause in the Orders SQL query:

and [CalculatePrice]=1

This is noted in the Scripts portion of this Tutorial in regard to the SQL Script named @@PurchaseHistory.

##Quick Nav
Reports
Scripts
Custom Entity Screen
Automation Commands
Actions
Rules
DB Tools
(reserved)


Purchase history by customer or phone number
Past orders of a particular customer
End work period vs. open tickets
Customer last visit report
Inventory CONSUMPTION: Inventory Items, Products, Recipes, and Order Tags
Save customer orders
Order History Samba V5
Customer Order Report
Customer purchase history by ticket
#2

#3

#Report

##Purchase History

##Purchase History [0] (Report)##

Report Name: Purchase History
Page Size: 15cm
Display in Report Explorer: checked
Visual Printing: unchecked

Template:

[$1 Purchase History:2,1, 1, 1, 1]
>>Customer: $2|ID: $5|$3|$4|
>Item|Portion| Qty| Price| Total
@@PurchaseHistory:$1,$2,$3,$4,$5
>>@@PurchaseHistoryTTL:$1,$2,$3,$4,$5


#4

#Scripts

##SQL - @@PurchaseHistory

##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

##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




#5

#Entity Screen - definition

##Purchase History [Customers] (Entity Screen)##

Name: Purchase History
Ticket Type: Ticket
View Mode: Custom
Search Value Replace Pattern:
Appearance
Background Image:
Background Color: #00FFFFFF
Use State Display Format: unchecked
Column Count: 0
Row Count: 0
Button Height: 0
Page Count: 1
Font Size: 50
Header Button Font Size: 0
Entity List (0)
Entity Type: Customers
Display State:
State Filter:
Entities: (none)
Details Template (none)(none)
Mappings (1)
Terminal User Role Department Ticket Type Visibility
* * * * All

#Entity Screen - Widgets

##PH Purchase History [Custom Report Viewer] (Widget)##
Entity Screen: Purchase History

###Properties (13):###

Widget Type: Custom Report Viewer
Name: PH Purchase History
X: 70
Y: 0
Height: 480
Width: 595
Zindex: 0
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin:

###Settings (5):###

Border Color: #FF494429
Parameters:
Report Name: Purchase History
Setting Mappings:
Zoom: 0

##PH Entity Search [Entity Search] (Widget)##
Entity Screen: Purchase History

###Properties (13):###

Widget Type: Entity Search
Name: PH Entity Search
X: 670
Y: 65
Height: 415
Width: 500
Zindex: 0
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin:

###Settings (12):###

Automation Command Name:
Can Create Entity: unchecked
Can Display Account: unchecked
Can Edit Entity: unchecked
Command Area Height: 0
Command Area Rows: 0
Command Buttons: Show Purchase History=PH Load Report
Command Value: {ENTITY ID}
Entity Type Name: Customer
Is Keyboard Visible: unchecked
Search Label:
State Filter Name:

##PH DateBEG Label [Label Widget] (Widget)##
Entity Screen: Purchase History

###Properties (13):###

Widget Type: Label Widget
Name: PH DateBEG Label
X: 671
Y: 0
Height: 55
Width: 160
Zindex: 0
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin:

###Settings (13):###

Alignment: Left
Background Color: Transparent
Border Color: #FFF79646
Border Thickness: 1
Font Name: Segoe UI
Font Size: 12
Font Weight: Normal
Foreground Color: #FFF79646
Text: Date Start: {SETTING:PHDateBEG}
Vertical Alignment: Top

##PH DateEND Label [Label Widget] (Widget)##
Entity Screen: Purchase History

###Properties (13):###

Widget Type: Label Widget
Name: PH DateEND Label
X: 836
Y: 0
Height: 55
Width: 160
Zindex: 0
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin:

###Settings (13):###

Alignment: Left
Background Color: Transparent
Border Color: #FFF79646
Border Thickness: 1
Font Name: Segoe UI
Font Size: 12
Font Weight: Normal
Foreground Color: #FFF79646
Text: Date End: {SETTING:PHDateEND}
Vertical Alignment: Top

##PH DateBEG Input [Editor Widget] (Widget)##
Entity Screen: Purchase History

###Properties (13):###

Widget Type: Editor Widget
Name: PH DateBEG Input
X: 676
Y: 21
Height: 30
Width: 150
Zindex: 5
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin:

###Settings (6):###

Alignment:
Command Name:
Edit Mask:
Font Size: 0
Local Setting Name: PHDateBEG
Refreshing Widgets: PH DateBEG Label

##PH DateEND Input [Editor Widget] (Widget)##
Entity Screen: Purchase History

###Properties (13):###

Widget Type: Editor Widget
Name: PH DateEND Input
X: 841
Y: 21
Height: 30
Width: 150
Zindex: 5
Angle: 0
Scale: 0
Corner Radius: 0
Auto Refresh: checked
Auto Refresh Interval: 0
Margin:

###Settings (6):###

Alignment:
Command Name:
Edit Mask:
Font Size: 0
Local Setting Name: PHDateEND
Refreshing Widgets: PH DateEND Label


#6

#Automation Command

##PH Purchase History [Navigation] (Automation Command)##

Name: PH Purchase History
Category: Navigation
Button Header: Purchase\rHistory
Color: #FFC0504D
Font Size: 26
Confirmation: None
Values (0): (none)
Navigation Settings
Symbol:
Image:
Auto Refresh: 0
Tile Cache: 0
Navigation Module:
Nav Module Parameter:
Template: ```

</details>


<details>
<summary><b><u>Mappings</u></b></summary><table><tr><td><b>Terminal</b> </td><td><b>User Role</b> </td><td><b>Department</b> </td><td><b>Ticket Type</b> </td><td><b>Enabled States</b> </td><td><b>Visible States</b> </td><td><b>Visibility</b> </td></tr><tr><td><code>*</code></td><td><code>*</code></td><td><code>*</code></td><td><code>*</code></td><td><code>*</code></td><td><code>*</code></td><td><code>Display on Navigation</code></td></tr></table></details>


----------

#7

#Actions

##PH Exec AMC [Execute Automation Command] (Action)##

Action Name: PH Exec AMC
Action Type: Execute Automation Command
###Parameters:###
Automation Command Name: [:AMCname]
Command Value: [:AMCvalue]
Background: False
Delay: 0

##PH Navigate [Navigate Module] (Action)##

Action Name: PH Navigate
Action Type: Navigate Module
###Parameters:###
Module Name: Entity
Parameter: [:screenName]
Hide Header: True

##PH Refresh Custom Report Widget [Refresh Custom Report Widget] (Action)##

Action Name: PH Refresh Custom Report Widget
Action Type: Refresh Custom Report Widget
###Parameters:###
Widget Name: [:widgetName]
Report Name: [:reportName]
Report Parameters: [:reportParms]
Date Filter: [:dateFilter]

##PH Store Setting [Update Program Setting] (Action)##

Action Name: PH Store Setting
Action Type: Update Program Setting
###Parameters:###
Setting Name: [:settingName]
Setting Value: [:settingValue]
Update Type: Update
Is Local: True


#8

#Rules

##PH Set Report Parameters [Automation Command Executed] (Rule)##

Rule Name: PH Set Report Parameters
Event Name: Automation Command Executed
Rule Tags:
Custom Constraint List (1):
Execute Rule if: Matches
Automation Command Name Equals PH Set Report Parameters

##Actions (7):##

PH Store Setting

Constraint: β€˜[:CommandValue]’ == β€˜β€™

settingName: PHEntityId
settingValue: {REPORT SQL DETAILS:SELECT TOP 1 e.[Id] FROM [Entities] e JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId] WHERE et.[Name]='Customers' ORDER BY e.[Id]:F.Id}
PH Store Setting

Constraint: β€˜[:CommandValue]’ != β€˜β€™

settingName: PHEntityId
settingValue: [:CommandValue]
PH Store Setting

Constraint: β€˜[:CommandValue]’ == β€˜β€™

settingName: PHEntityName
settingValue: {REPORT SQL DETAILS:SELECT e.[Name] FROM [Entities] e WHERE e.[Id]='{SETTING:PHEntityId}':F.Name}
PH Store Setting

Constraint: β€˜[:CommandValue]’ != β€˜β€™

settingName: PHEntityName
settingValue: {REPORT SQL DETAILS:SELECT e.[Name] FROM [Entities] e WHERE e.[Id]='[:CommandValue]':F.Name}
PH Store Setting

Constraint: β€˜{SETTING:PHDateBEG}’ == β€˜β€™

settingName: PHDateBEG
settingValue: [='{DATE:yyyy-MM}'+'-01']
PH Store Setting

Constraint: β€˜{SETTING:PHDateEND}’ == β€˜β€™

settingName: PHDateEND
settingValue: [=ADM('{DATE:yyyy-MM}'+'-01',1)]
PH Store Setting

Constraint: β€˜{SETTING:PHDateEND}’ == β€˜β€™

settingName: PHDateEND
settingValue: [='{:PHDateEND}'.substr(0,10)]

##Mappings##

Mappings
Terminal User Role Department Ticket Type
* * * *

##PH Load Report [Automation Command Executed] (Rule)##

Rule Name: PH Load Report
Event Name: Automation Command Executed
Rule Tags:
Custom Constraint List (2):
Execute Rule if: Matches
Automation Command Name Equals PH Load Report
Automation Command Name Equals PH Load Report from Account Statement (optional)

##Actions (3):##

PH Exec AMC

Constraint: (none)

AMCname: PH Set Report Parameters
AMCvalue: [:CommandValue]
PH Navigate (optional)

Constraint: β€˜[:AutomationCommandName]’ == β€˜PH Load Report from Account Statement’

screenName: Purchase History
PH Refresh Custom Report Widget

Constraint: (none)

widgetName: PH Purchase History
reportName: Purchase History
reportParms: Customers,{SETTING:PHEntityName},{SETTING:PHDateBEG},{SETTING:PHDateEND},{SETTING:PHEntityId}
dateFilter:

##Mappings##

Mappings
Terminal User Role Department Ticket Type
* * * *

##PH Show Purchase History Screen [Automation Command Executed] (Rule)##

Rule Name: PH Show Purchase History Screen
Event Name: Automation Command Executed
Rule Tags:
Custom Constraint List (1):
Execute Rule if: Matches
Automation Command Name Equals PH Purchase History

##Actions (3):##

PH Exec AMC

Constraint: (none)

AMCname: PH Set Report Parameters
AMCvalue:
PH Navigate

Constraint: (none)

screenName: Purchase History
PH Refresh Custom Report Widget

Constraint: (none)

widgetName: PH Purchase History
reportName: Purchase History
reportParms: Customers,{SETTING:PHEntityName},{SETTING:PHDateBEG},{SETTING:PHDateEND},{SETTING:PHEntityId}
dateFilter:

##Mappings##

Mappings
Terminal User Role Department Ticket Type
* * * *


#9

#DB Tools

Database Tools File:

PH Customer Purchase History.zip (3.9 KB)


#10

Tutorial modified to simplify setup:

  • REMOVED JScript function: ent.getEntityNameById()
  • CHANGED Rule: PH Set Report Parameters
  • 2 Actions using {CALL:ent.getEntityNameById()} changed to use {REPORT SQL DETAILS:X}

#11

#12

You are awesome! Thanks a lot QMcKay. It would be imposible for me to do that!!
I will follow the tutorial and came back with the results!
Best regards!
Ezequiel


#13

Thanks. I made some modifications to simplify it further. See the Update Post above:

I think it would be nice if a Report Tag Guru could develop something to replace the SQL Scripts as well (@@PurchaseHistory and @@PurchaseHistoryTTL).

Any takers? @kendash do you think it is possible?


#14

QMcKay,
I followed everything but im not sure how to put the same action (PH Store Setting) 7 times in the same rule.
Sorry if i missed something. I read the tutorial three times and i guess im not missing anything.
Thanks again!

Nevermind! I find this.

Sorry to bother!

Well, i made everything as you said and its working perfect!!
Thanks a lot for your help Q!


#15

What should i do with this DB Tools text file???


#16

Sure Q I’ll work on some reports.


#17

You import it into SambaPOS using Database Tools Import function:

Manage > Settings > Database Tools > Tools > Import from File


#18

Ok @qmckay I am looking at the reports now ill post an update after a while.

PS: This is good practice its a rather complex use of Custom Reports Tags typically its nearly impossible to use specific dates with it but I have an idea.


#19

@QMcKay one issue is how to set the date. I can use O.CreatedDateTime or O.LasteUpdateDateTime field but the setting would have to match that format. So I am trying to theorize how to convert the input from Editor widget to that exact format without script. I know masks can be used but I thought that might not be ideal it would be better to allow people to enter their own format.


#20

I think either Date/Time would work just as well for our purpose. They are both Datetime data-format.

For the time-being, if you have the Report Tag syntax, I wouldn’t mind seeing what it looks like… we can work from there to try to put date filters in.