So i want to create a button that when pressed opens a box to “scan” the product barcode, and for this to then display the price (and eventually possibly other info like current stock level)
ive never used SQL or script before so really not sure where to start, ive googled SQL to try and read up and think i need to use the WHERE clause as my reading tells me that function is for extracting records that fulfil a specific condition
so in my case i want to SELECT price FROM the product information WHERE there barcode equals that which is scanned (i have a item tag for barcode which is the field ill need to link to)
So my idea is:
Create an auto command button called Price Check - DONE
Create a rule to that when the Price Check button is pressed a prompt appears to scan the barcode and this is saved as a program setting called Price Check - DONE
Using SQL and Scripts the stored program setting for the barcode is linked into the WHERE clause to match that of a products ITEM TAG barcode field to then extract and show the price in a display message action.
Can part 3 be done like im thinking?
The script i setup is probably very obviously wrong but i have never done it before so dont know what info is required where, and then im not sure how to call it in a rule to display the price
This is the script
am i anywhere near on the right lines? any help to sort would be great
I offered a starting point for the SQL query on another thread.
You will need to join prices to menu items which will also involve portions and potentially price tags.
As you will know portion is seperate to barcode as a product level detail so likely this will always be ‘Normal’ so next point is price lists if you have more than one.
SELECT p.[Price]
FROM [MenuItemPrices] p
LEFT JOIN [MenuItemPortions] s ON s.[Id] = p.[MenuItemPortionId]
LEFT JOIN [MenuItems] m ON m.[Id] = s.[MenuItemId]
WHERE m.[Barcode]=1234 AND s.[Name]="Normal"
That was how far I got but have multiple price lists which I think is what was causing the issue I was having.
As a non retail person without a scanner in any of my systems I didnt push further but is a starting point.
@JTRTech is going about it the correct way with his SQL query.
The Product Price is not directly tied to the Product. The Price is tied to the Portion (which is tied to the Product), which also links to Price Definitions.
So your query is not so direct as you might like, and you need to link (ie. JOIN) multiple DB Tables together to retrieve the information, and have multiple constraints in the WHERE clause.
If you don’t need to worry about PriceDefs, then joining 3 Tables as JTRTech has done will suffice, otherwise you will need to join a 4th Table and constrain the query further with the current PriceDef in use.
so with putting that in a script how do i link this to the auto command button to search the barcode entered and display the price in a display message action?
Ive only ever done in a jscript by having the query in a text string with …sqlstring’+variableName+'sqlstring…
Where the variableName is a value fed into the jscript.
Like this;
i just dont understand it, never even looked at sql until last night when i started reading about it. Dont get how it works and no idea about how to use it in samba to run a script and display the result
i know lol, ive just never ever used sql before so i dont really understand it, so in the script you posted:
SELECT p.[Price]
FROM [MenuItemPrices] p
LEFT JOIN [MenuItemPortions] s ON s.[Id] = p.[MenuItemPortionId]
LEFT JOIN [MenuItems] m ON m.[Id] = s.[MenuItemId]
WHERE m.[Barcode]=1234 AND s.[Name]="Normal"
Do i just add that as a new script under automation and call it Price Check for example, and put the handler as @@PriceCheck?
And also the 1234 is that as if the barcode is hard coded to 1234? and in my case as im using a program setting to store the “barcode” scanned can i replace 1234 with {SETTING:Price Check} so the script would be
SELECT p.[Price]
FROM [MenuItemPrices] p
LEFT JOIN [MenuItemPortions] s ON s.[Id] = p.[MenuItemPortionId]
LEFT JOIN [MenuItems] m ON m.[Id] = s.[MenuItemId]
WHERE m.[Barcode]={SETTING:Price Check} AND s.[Name]=“Normal”
TicketTransfer is the script function and within the () is variables.
In the previous example it would be;
{CALL:scriptHandler.orderDetails(‘{ORDER ID TAG}’)}
Its not best example as is a script used by another script so {ORDER ID TAG} isnt valid tag.
I like the {CALL:xxx} method over execute script as the returned value goes into the action or place its placed where as execute script wouldnt return values to the action, you would have to either trigger the resulting automation from within the script or put in program setting etc.
The Function field is the HandlerName, in your case @@PriceCheck.
In the case of executing JScript, Function field needs the JScript HandlerName and the FunctionName within the JScript handler.
The Parameters field in the case of SQL is a comma-separated list, so you should make that a [:variable] that you will populate from the Rule to feed in the BarCode.
In the @@PriceCheck script, you access the Parameters via an @indexNumber, so the SQL script would become the following (notice the @1 which is the first value of the Parameters list):
SELECT p.[Price]
FROM [MenuItemPrices] p
LEFT JOIN [MenuItemPortions] s ON s.[Id] = p.[MenuItemPortionId]
LEFT JOIN [MenuItems] m ON m.[Id] = s.[MenuItemId]
WHERE m.[Barcode]='@1'AND s.[Name]="Normal"
creates the program setting where a keypad appears to enter the barcode, this then starts an auto command to run the script
Executes the script using the details provided above Function = @@PriceCheck and Parameter = {SETTING:Price Check} which is the store value of the barcode from the first rule. This rule then generates another auto command to fire another rule with a display message action to display the price
This rule should display the price in a display message action, I dont think this rule is correct but i dont know if the previous two are correctly setup. this 3rd rule doesnt generate the show message box, nothing happens
Using a script and {CALL:xxx} you only need one rule and one action;
Script;
function checkPrice(inputBarcode) {
qry = "SELECT p.[Price] FROM [MenuItemPrices] p LEFT JOIN [MenuItemPortions] s ON s.[Id] = p.[MenuItemPortionId] LEFT JOIN [MenuItems] m ON m.[Id] = s.[MenuItemId] WHERE m.[Barcode]="+inputBarcode+"AND s.[Name]='Normal'";
var salesTicketCount = sql.Query(qry).First;
return salesTicketCount;
}
so the script just uses the portion Normal, is there anyway to change it from saying Normal to a value that displays all portiona, or can i just add my different portions as a comma separated list after Normal?
