Customer Purchase History Reporting

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.

Jesse · March 10, 2016, 6:40pm

Let me test it see if it works. I am just going to enter some static stuff first.