#DB Tools
Database Tools File:
PH Customer Purchase History.zip (3.9 KB)
Tutorial modified to simplify setup:
ent.getEntityNameById()
PH Set Report Parameters
{CALL:ent.getEntityNameById()}
changed to use {REPORT SQL DETAILS:X}
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
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?
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!
What should i do with this DB Tools text file???
Sure Q I’ll work on some reports.
You import it into SambaPOS using Database Tools Import function:
Manage > Settings > Database Tools > Tools > Import from File
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.
@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.
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.
Let me test it see if it works. I am just going to enter some static stuff first.
Here is what I am working on atm. Its not tested yet and not complete but you might get the idea.
[{ENTITY NAME} Purchase History:2,1, 1, 1, 1]
@{ENTITY NAME}
@{REPORT ENTITY DETAILS:E.Id:(EN={ENTITY NAME})}
>>Customer: $1|ID: $2|{SETTING:PHDateBEG}|{SETTING:PHDateEND}|
>Item|Portion| Qty| Price| Total
{REPORT ORDER DETAILS:O.MenuItemName,O.PortionName,O.Quantity.sum,O.Price.sum,O.TotalPrice:(TEN.Customer=$1) AND (TS.Status=Paid) AND O.CreatedDateTime >= {SETTING:PHDateBEG} AND O.CreatedDateTime < {SETTING:PHDateEND}}
{ENTITY NAME} is just placeholder atm while I figure out how to interact with the widget.
Few issues is the structure of Date the colons in the expression.
HMM Perhaps I have my syntax wrong…
Problem lies in the REPORT ORDER DETAILS tag… I am going to work on it more…
Ok updated semi working code:
[{ENTITY NAME} Purchase History:2,1, 1, 1, 1,1]
@{ENTITY NAME}
@{REPORT ENTITY DETAILS:E.Id:(EN={ENTITY NAME})}
>>Customer: B11|ID: B11|{SETTING:PHDateBEG}|{SETTING:PHDateEND}|
>Item|Portion| Qty| Price| Total
{REPORT ORDER DETAILS:O.MenuItemName,O.PortionName,O.Quantity.sum,O.Price.sum,O.TotalPrice:(TENC.Tables=$1)}
OK weird. I have been playing with Report Preview and its worked fine but now all of a sudden it wont recognize a parameter.
Take a look at first parameter:
Take a look if I hardcode the 11:
It was working fine just a moment ago. The parameter is clearly working inside the first line but after that it drops off.
@emre any idea?
Report Preview sometimes un-caches values like $parameters
, so you might need to use the actual Entity Screen to re-populate the parameter.