Customer Purchase History Reporting

QMcKay · March 8, 2016, 12:04am

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.

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.

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)

QMcKay · March 8, 2016, 12:13am

#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

QMcKay · March 8, 2016, 12:13am

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

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

QMcKay · March 8, 2016, 12:19am

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

QMcKay · March 8, 2016, 12:20am

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


----------

QMcKay · March 8, 2016, 12:22am

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

QMcKay · March 8, 2016, 12:24am

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

QMcKay · March 8, 2016, 12:24am

#DB Tools

Database Tools File:

PH Customer Purchase History.zip (3.9 KB)

QMcKay · March 8, 2016, 12:24am

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}

iberiabati · March 8, 2016, 3:06am

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

QMcKay · March 8, 2016, 6:07am

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? @Jesse do you think it is possible?

iberiabati · March 8, 2016, 7:37pm

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!

Ankit · March 10, 2016, 12:26pm

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

Jesse · March 10, 2016, 1:55pm

Sure Q I’ll work on some reports.

QMcKay · March 10, 2016, 1:59pm

You import it into SambaPOS using Database Tools Import function:

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

Jesse · March 10, 2016, 6:11pm

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.

Jesse · March 10, 2016, 6:36pm

@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.

QMcKay · March 10, 2016, 6:39pm

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.