Customer purchase history by ticket

V5 Question
1

I have been following @QMcKay’s tutorial on customer purchase history and used some of the examples as the basis for something I wanted to do.

Q’s version gives a reasonably detailed output on an order-by-order basis, something like this…

Item           QTY   Price   Total
Pizza          2     10      20.00
Burger         1     5.50     5.50
Drink          2     1        2.00

This works great, but I need to ability to list on a ticket-by-ticket basis what was ordered and how it was paid for. I also use A LOT of order tags, so these also need to be included. Something like this…

>>Ticket 1   05-MAY-2018   35.00
>2  Pizza                  20.00
     + Extra cheese         5.00
     + Extra meat           5.00
>1  Burger                  5.50
      + Mayo                0.50
      + Bacon               4.00
== CASH                   -30.00
== Card                    -5.00

>>Ticket 25   06-MAY-2018   3.00
>2   Drink                  2.00
      + Ice                 1.00
== CASH                    -3.00

I have been able to create this report successfully using the following template. Our EnityNames are customer email addresses.

@{REPORT TICKET DETAILS:T.Id:(TEN.Customers=dave@gmail.com):,}
@{REPORT ORDER DETAILS:O.Id:T.Id=$1::,}
>>@@GetTicketDetailsFromTicketId:$1
>@@GetOrderDetailsFromOrderId:$2,$1
@@GetOrderTagDetailsFromOrderId:$2,$1
@@GetPaymentsFromTicketId:$1

This gives the result I am looking for because of the way the SambaPOS handles the looping of the $ variables. There is almost an outer-loop of the $1 and an inner-loop of the $2.

This works create when I hard-code an EntityName in to the report. However, I need to use a Custom Report Viewer Widget to display this report and pass the EntityName as a variable in to the report.

The problem I have in 5.1.61 and 5.2.3 is that it seems when I pass a variable in to the report viewer it replaces the value of the first @List. So, I thought, OK, let me make an extra @List at the start of the report that I don’t mind being over-written…

[Customer Purchase History:1,4,1,1,1]
@dave@gmail.com
@{REPORT TICKET DETAILS:T.Id:(TEN.Customers=$1):,}
{REPORT SQL DETAILS:SELECT Id FROM Orders Where MenuItemName != '$1' AND Ticketid = $2:F.Id::,}

The $1 in the SQL statement is not required, but I found that for me to get the results I was expecting I had to include both $1 and $2 on that line so SambaPOS would loop correctly and give me a value for $2. The above report works as expected and gives an output as follows…

101,102    -- The order IDs for Ticket 1
243        -- The order IDs for Ticket 25

So far so good, but when I then try to use these IDs as an @lsit everything starts to go wrong…

[Customer Purchase History:1,4,1,1,1]
@dave@gmail.com
@{REPORT TICKET DETAILS:T.Id:(TEN.Customers=$1):,}
@{REPORT SQL DETAILS:SELECT Id FROM Orders Where MenuItemName != '$1' AND Ticketid = $2:F.Id::,}
$1 $2 $3

Output

dave@gmail.com 1 $3
dave@gmail.com 25 $3

So what is going on here? Why is the SQL able to successfully take the $2 variable and output the OrderIDs as expected, but when I use it as an @List it doesn’t work.

Further investigation…
To try to debug this, I turned the SQL statement in to a script ({CALL:x.getOrderIdsByTicketId($2,$1)}) and inside this script I used a dlg.show('Finding Order IDs'); to create a popup so I could confirm the script was running. When I ran the script without using it as an @List everything worked as expected and I saw the pop-up message. BUT, when I tried to use the script as @List I no-longer see the pop-up, so it is as if SambaPOS is just not firing that script at all and that’s why I never see any results for the third variable $3.

Any ideas on what might be wrong, or a better way I can achieve this TicketID + OrderID looping?

2

@Jesse - From something I read on an old forum post from Emre, it seemed that you were something to do with the way in how the @List functionality works and how the looping works, so I am not sure if you can shed any light on this question?

3

You have ventured into confusing territory… I have been there too, and it’s a mindbender. :stuck_out_tongue_winking_eye:

This might help (maybe not):

Report

[Employee Earnings $entityName:1,1,1,1,1, 1, 1, 1, 1, 1]
>EmployeeName|StartDate|EndDate|DT|H|Hours|HOL|OT|REG|EARNED
@@TC_EmployeeEarnings:$1,$2,$3,$4,$entityName
     can we use a $namedVariable  ^^^^^^^^^^^ to be referenced in SQL script

SQL

-- PARM for Employee Entity Type
declare @entityType varchar(20)  = 'Employees' --'@1'
-- PARM for Employee 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 Employee Name via GQL
declare @entityNameGQL varchar(20)  = '@entityName'
--$namedVariable from Report referenced here ^^^^^

You can use “named variables” as parameters instead of using only the $1 / @1 variables.

The next question is: how to set the value for “named variables”?

GraphQL

GraphQL can do it using the parameters parameter with an array of name/value pairs:

getCustomReport(
name:"TC Employee Hours"
,user:"Q"
,dateFilter:"This Month"
,parameters:[{name:"entityType",value:"Employees"},{name:"entityName",value:"Ovania"},{name:"startDate",value:"2016-07-01"},{name:"endDate",value:"2016-08-01"}]
)

Actions

These Actions can do it using the parameters parameter:

Print Report

image

Specify parameters as:

<varName>=<varValue>,<varName2>=<varValue2>,...
entityType=Employees,entityName=Ovania

Refresh Custom Report Widget

image

Specify parameters as:

<varName>=<varValue>,<varName2>=<varValue2>,...
entityType=Employees,entityName=Ovania

Report Widget

And the Report Widget can do it using the parameters parameter in Widget Settings:

image

Specify parameters as:

<varName>=<varValue>,<varName2>=<varValue2>,...
entityType=Employees,entityName=Ovania

:bulb: NOTE: if you don’t specify the varName, and use only a comma-separated list of varValues, then the varNames will automatically become: $1,$2,$3,...

For example:

Parameters: Employees,Ovania

… is like doing this:

Parameters: 1=Employees,2=Ovania

4

Thanks @QMcKay - Yes I am starting to feel like that!

Thanks so much for your reply, it sounds like it would be the solution, but i am having no luck whatsoever…

I have setup this test report:

[$entityname Purchase History:1,4,1]
$entityname $1 $2

And I have setup the report widget in the following way…
image

But the report displays as…

$entityname Purchase History
$entityname entityname=matt something=else

So as you can see, this is not being treated like and Name+Value pair of named parameters, but instead two parameters which set the values of $1 and $2.

Any idea what I am doing wrong (on 5.2.3)? I have also tried refreshing and setting the parameters via an Action, but I get exactly the same result…

5

So the way he programed parameters to work is specific. It only supports two and if you use it in the report name it gets screwy. You can’t define a third parameter and use $3

It’s better to use SQL in a script and call it in the template.

6

Ah, thanks. That explains why the third was not working correctly.

I am still stumped as to why named variables is not working as @QMcKay described in his example…?

7

Maybe because it just doesn’t. It works with GraphQL getCustomReport(), so I just assumed the Actions and Widget would work that way as well (did not test). A shame if that isn’t the case.

8

What? Only 2? Since when?

Most of my Reports of this type have 5 parameters: $1,$2,$3,$4,$5 …

I decided early on a “standard” for my self to not get confused:

$1 : entityType (most of the time, but can be whatever)
$2 : entityName (most of the time, but can be whatever)
$3 : dateBeg (ALWAYS)
$4 : dateEnd (ALWAYS)
$5 : other stuff (rarely used)

Even if some vars are not used in the Report (ie. empty string placeholders), I always know that $3 and $4 contain dates (if applicable).

9

I’m not talking about SQL I’m specifically talking about using @ parameters in report syntax

10

So you mean Reports only support 2x @lists ? News to me.

11

Yes that’s what I’m saying. Long ago when implemented it started with just a single one. He expanded it to two.

I’m pretty sure it’s a beta topic but I’d have to dig it up.

1 Like
12

So it was not beta it was v4 forum when we first started with custom reports. Originally it only supported one but he expanded it to two. Also if you use lists before the report title to use them in the title itself it kinda breaks them for rest of report. I asked him about that long ago and it was not something he could just fix. Something about how the parser reads stuff after [] brackets.

Been a long time about 3 years ago since we discussed it.

13

It does seem to support more than 2 @lists… Take a look at this…

[Test:1,1,1]
@red,green,blue
@dave,john,phil
@pizza,burger,drink
$1 $2 $3

Gives the result of:

red dave pizza
red dave burger
red dave drink
red john pizza
red john burger
etc...

The main thing I am confused about is why these names variables are not working for me, but they seem to work for @QMcKay

2 Likes
14

Ok so I stand corrected.

15

You must have missed my reply:

16

Ah - Yes. I missed that. I will check it out with GQL. A while back you showed me how to use GQL to set the Ticket Note value, so I will try to use the same logic for this…

17

Unfortunately, it won’t be the same at all. The query getCustomReport() returns JSON-formatted data which you need to cycle through and build the display yourself. It doesn’t just return a “formatted report” like you see in Report Explorer or in the Report Widget…

{
  "data": {
    "report": {
      "name": "VOIDS",
      "header": "",
      "startDate": "04/04/2018 10:17:13",
      "endDate": "05/07/2018 10:55:03",
      "tables": [
        {
          "name": "Void Items List",
          "maxHeight": 500.0,
          "columns": [
            {
              "header": "Ticket No"
            },
            {
              "header": "Order No"
            },
            {
              "header": "Void Item Name"
            }
          ],
          "rows": [
            {
              "cells": [
                "15281",
                "18018",
                "Barena"
              ]
            },
            {
              "cells": [
                "15282",
                "18019",
                "Barena"
              ]
            },
            {
              "cells": [
                "15286",
                "18023",
                "Barena"
              ]
            },
            {
              "cells": [
                "15287",
                "18024",
                "Barena"
              ]
            },
            {
              "cells": [
                "15288",
                "18025",
                "Barena"
              ]
            },
            {
              "cells": [
                "15361",
                "18098",
                "Barena"
              ]
            },
            {
              "cells": [
                "15363",
                "18100",
                "Barena"
              ]
            },
            {
              "cells": [
                "15364",
                "18104",
                "Imperial"
              ]
            }
          ]
        }
      ]
    }
  },
  "errors": null
}

P.S. I am a bit surprised that the Actions and Widget don’t support named variables. I just assumed when the named-variable feature was introduced in GQL, it would be implemented in other SambaPOS areas (because that’s how @emre tends to do things).

I wish I could confirm this with him; instead I need to run some tests to prove it to myself one way or the other.

18

Thanks for all your efforts. Instead of using getCustomReport() is there any GQL for manipulating Entity Screens or Report Widgets?

Or, do you know an effective way of running these inner and outer loops within SQL? It’s not really what SQL is designed for though, right? - That sort of thing is normally done at the presentation level rather than the DB level.

Of course maybe another option if for me to learn more about this whole GQL stuff and build a web page which displays the info instead of relying on an Entity Screen and report widget?

19

No.

Not sure what you are referring to.

That’s what I did in QMX to replace the Report Explorer, and my Timeclock (Reports) - the Reports are still defined in SambaPOS, but they are fetched using GQL and display is formatted as HTML using the returned JSON.

I still use Report Explorer, but rarely.

And I still use Report Widgets for some things, because those things are already done, they work, and building a GQL-equivalent is somewhat more trouble than it’s worth and would be very specific to the purpose.

1 Like
20

That would be your best option. You get full control and can do much more.

2 Likes