Has anyone had a crack at effecting a User permanent price change through automation of the fly? I would like to be able to update any price cell listed below
I was thinking to use {SETTING:x} to capture āBefore Priceā but I know the RULE Evaluation can get a bit funky! So Maybe use a SQL Fetch Price and compare to {PRICE} after UPATE ORDER - [:Price] has been performed.
If they are different then SQL UPDATE on User Prompt Confirmation?
You will need to make a decision, because if the Price is not set for a Portion/PriceTag, then you will need an INSERT statement.
If the Portion/PriceTag is already set, this UPDATE statement will do the trick. Notice the special case - (REG), commented out - that needs to be used when you are updating the āregularā price ā¦
--
-- set some vars to make this dynamic
--
declare @priceTagExists int = 0
--declare @priceTag varchar(50) = '(REG)'
declare @priceTag varchar(50) = 'VIP1'
declare @product varchar(50) = 'ACCOUNT PAYMENT'
declare @portion varchar(50) = 'Normal'
declare @price varchar(50) = '100.00'
--
-- determine if the Portion/PriceTag Exists
--
SELECT @priceTagExists = count(1)
FROM [MenuItemPrices] ppr
JOIN [MenuItemPortions] po on po.[Id] = ppr.[MenuItemPortionId]
JOIN [MenuItems] pr on pr.[Id] = po.[MenuItemId]
WHERE 1=1
AND pr.[Name] = @product
AND po.[Name] = @portion
AND isnull(ppr.[PriceTag],'(REG)') = @priceTag
--
-- if it does NOT exist, then INSERT it
--
IF @priceTagExists=0
BEGIN
INSERT INTO [MenuItemPrices] ([MenuItemPortionId],[PriceTag],[Price]) VALUES (
(SELECT po.[Id] FROM [MenuItemPortions] po JOIN [MenuItems] pr on pr.[Id] = po.[MenuItemId] WHERE pr.[Name] = @product and po.[Name] = @portion)
,(SELECT pd.[Name] FROM [MenuItemPriceDefinitions] pd WHERE pd.[PriceTag] = @priceTag)
,@price
)
END
--
-- if it DOES exist, UPDATE it
--
IF @priceTagExists>0
BEGIN
UPDATE ppr SET
ppr.[Price] = @price
FROM [MenuItemPrices] ppr
JOIN [MenuItemPortions] po on po.[Id] = ppr.[MenuItemPortionId]
JOIN [MenuItems] pr on pr.[Id] = po.[MenuItemId]
WHERE 1=1
AND pr.[Name] = @product
AND po.[Name] = @portion
AND isnull(ppr.[PriceTag],'(REG)') = @priceTag
END
--
-- let's see our Results
--
SELECT
pr.[Name] as [Product]
,po.[Name] as [Portion]
,isnull([PriceTag],'(REG)') as [PriceTag]
,[Price]
FROM [MenuItemPrices] ppr
JOIN [MenuItemPortions] po on po.[Id] = ppr.[MenuItemPortionId]
JOIN [MenuItems] pr on pr.[Id] = po.[MenuItemId]
WHERE pr.[Name] = @product and po.[Name] = @portion
ORDER BY pr.[Name],po.[Id]
There is a tag for product name without portion.
Use it on my template to override Name.Portion to have a space rather than the point.
Not at PC but it will be something like product name or item name.
Check the ticket template tag list, itās on there somewhere.
Cool thanks JTRTech - {PRODUCT NAME} does the trick. Just need the last piece of the puzzle now āPrice Definitionā used for an Selected Order Line?
So here is the thing {PRICE TAG} is working but the Update Order ACTION is effecting the change of Price of the DEFAULT Price as no Price Tag is passed! The resultant new Price for the Order Line will be listed as a Regular Price.
Can you cast your eye over the generated SQL - I think the last bit is wrong? Should it be: AND isnull(ppr.[PriceTag],'(REG)',[PriceTag]) = 'Happy Hour'
Are we missing the ,[PriceTag]
[EDIT]
Wait a moment - HOLY MOLLY - writing Normal Pricesā¦
Oh no the āOld Cacheā issue, I was worried about that
The reason we do that is for when we want to update the Regular Price (the price that has no PriceTag). By using the isnull(field,'replacement') then we donāt need to change the code for the special case of the Regular price. If we did not do that, then we would need to change it to the following, but only for the special case of the Regular price:
[PriceTag] is null
Instead, we match our special case to (REG) and we donāt need to change our code:
isnull(ppr.[PriceTag],'(REG)') = '(REG)' -- the price has no PriceTag
isnull(ppr.[PriceTag],'(REG)') = 'VIP1' -- the price has PriceTag of VIP1
ISNULL is similar to COALESCE(field,'replacement')
Hey thanks Q! - guess what I (we) have it working, āthe unworkable solutionā actually working. The only thing I am having an issue with is the Cache.
When the price is updated, I do Refresh Cache ACTION but this only takes effect when I cancel the order and close ticket. What actually happens then is the Menus go blank? Then when I reclick Department all comes back and the new price is available in any Portions and Price Tag.
Well the permanent pricing workflow is operating well with 1 small hitch - the āold cacheā scenario. From the GIF below you can see the price is written to the DBO using script but the Menu Pricing Cache is not refreshed until I close the Ticket and I guess Navigate.
I know you would have special controls here to ensure SambaPOS works fast and efficient.
So what can you suggest?
PS: The Menu goes blank on its own, I assume due to the Refresh Cache Action.
[EDIT]
Well another midnight finish
Happy to accept any suggestions over night. I am thinking of an no so elegant flow of placing the ticket on hold (this may submit an old price?), switching Menus or Navigate or something, reload Ticket. Sort of clunkyā¦
That is a good point @emre!
But for now I have the problem that the Cache Reset does not do the Job as we are in middle of a Ticket? I do not know how to get around that oneā¦
Anybody feel free to offer suggestions while I pull the remaining hair from my head overnight.
Nope switching Menus back and forth will not do it after a Refresh Cache.
On next update mutation m{postResetProductCacheMessage{id}} API command will reset product prices inside tickets on all terminals.
Also updateProductPrice mutation will include notifyClients parameter to do that automatically after updating a product price.
PS: Refresh cache wonāt work inside tickets and setups a late trigger that works after closing ticket as refresh cache action clears entire cache and that may create issues with ticketās state data.
Are you referring to what I am trying now? If Yes, no problems I have already removed all that flow late last night (morning). The message to the user now says āWARNING: Please log off/on each terminal to refresh the new pricesā.
I have said it before so will say it again, your SQL skills are top notch.
