Help with SQL to create a price check button

looks like the closing size tag is in the wrong place, the opening one is coloured black but the closing one is brown and this causes nothing to be displayed

function checkPrice(inputBarcode) {																	
 	var qry = "SELECT m.[Name], s.[Name], 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 +"'";
	var priceList = sql.Query(qry).All;
	var priceCount = priceList.Length;
	var response = <size 70>'Product Name: ' + priceList[0].substr(0,priceList[0].indexOf(','))</size> +'\r';
	for (t = 0; t < priceCount; t++) {
		var list = priceList[t].split(','); //list[0] = Product Name, list[1] = Portion Name, list[2] = Price, 
		list[1] = list[1].replace('Normal','');
		list[2] = '£'+list[2];
		response += '\r' + list[1] + (list[1] ? ' ' : '') + list[2];
    }
	return response;
}

Can we make this script to show prices for a certain group code?

That had to have come back with error right??

Try this;

var response = ‘<size 70>Product Name: ’ + priceList[0].substr(0,priceList[0].indexOf(’,’))+’ \r’;

The size parts need to be inside the quoted text string parts.

Group code, think you would be better of with a price list report rather than a script and how does that link to this discussion. It will have nothing to do with barcode and how would you set the GroupCode?
What are you expecting to see or trying to do?

Yea nothing was displayed, just getting ready for work so ill try your suggestion tonight :grinning:

No what m saying I want to get price via barcode but we cant categories according to group code?
Like I have a group code name non vat so I don’t have to do any calculation price check can show normal price. For the rest we can multiply price with 12.5% in the script.

Please somebody help I have to run samba today in a grocery which I was working from last 3 weeks.
The only thing remaining is price check.

@QMcKay please help this time I have run samba today.
I sort out everything just this price check remaining.
it would be a big achievement for us when I complete this project and I want to share this victory with all of you guys.
This would be Trinindad and Tobago 1st big grocery who would be using samba.
it doesn’t matters to me which way you can solve this problem either sql or either opening ticket.

Ive already got the price check working, including if the product has portions thanks to @JTRTech and Sukasem

Doesnt need to create a ticket just press price check button, scan barcode and itll tell you the product, portions and all prices

That should work for you no probs, im just working on formatting size bit ill post tutorial possibly tonight, but you should be able to get it all from the conversation in the post

but what about tax??
I have vat on all products except with the non vat group code.

Easiest way is to set prices inclusive of tax then you can use the method above

I already setup everything and I have 5000 items in the product list.
I cant change right now and I have to go live today.

Tax is a whole other ball of wax. That’s what Tax Templates do, and the associated product mappings. If your prices were Tax inclusive, you would be done.

You are not going to be able to pull from DB the Tax info and match up the mappings to products/groups/portions etc, without a huge amount of effort that I have no care to get in to.

If you have a fixed Tax (percent or amount) on all Items, then just add it to the script already provided.


At this point, I suggest you approach it all from a completely different angle.

Scan the product in to the Ticket, and look at the Ticket. Done. Or do some more automation to follow that…

You could then automatically “print” the ticket (ie. to file), then use script to read the file, parse out the Product, Portion, Price, Tax, etc), then Display it (Message Box or Ask Question dialog), then cancel order and close Ticket.

Or think of another method of printing rather than to file, like Browser Printer, or Task Printer (I love Tasks). Then use GQL to fetch the Task Data and parse it, then display, cancel, close. I would probably go with Task Printer/Template and GQL.

I love to use SQL too, but some things are not worth the time and effort (it was fine to do all this via SQL before you decided you wanted Tax), and now that you have a complete explanation of what you want, I would definitely use a Task. When you get the hang of setting up a Task Printer Template, things are easy-peasy.

##Task


##Printer


##Printer Template

[LAYOUT]
{ORDERS}

[ORDERS]
++{PRODUCT NAME}.{PORTION NAME} - {TAX TOTAL} - {PRICE}
(Id={ORDER UID})
(name={PRODUCT NAME})
(portion={PORTION NAME})
(price={PRICE})
(taxTotal={TAX TOTAL})
(ticketTotal={TICKET TOTAL})


##Print Job


##The Printed Task as “seen” in the DB


##Report to show the Task

[Price Check Tasks:2,1, 1, 1, 1, 1]
>>taskName|name|portion|price|tax|total
>{REPORT TASK DETAILS:T.Name,TSC.name,TSC.portion,TSC.price,TSC.taxTotal,TSC.ticketTotal:T.Completed=False && (TST=PC Price Check Task)}


##Script to get Task using GQL

Name: PC Price Check
Handler: priceCheck

function getTasks(taskTypeName,isCompleted) {
   var data = gql.Exec('{tasks:getTasks(taskType:"'+taskTypeName+'",isCompleted:'+isCompleted+'){id,isCompleted,identifier,name,content,contentText,customData{name,value},startDate,endDate,userName}}');
   return data;
}


##Better Script

function getTasks(taskTypeName,isCompleted) {
	var data = gql.Exec('{tasks:getTasks(taskType:"'+taskTypeName+'",isCompleted:'+isCompleted+'){id,isCompleted,identifier,name,content,contentText,customData{name,value},startDate,endDate,userName}}');
	var obj  = JSON.parse(data);
	//return data;

	var tasks = obj.data.tasks;
	var task  = tasks[0];

	var taskType = taskTypeName;

	var id    = task.id;
	var ident = task.identifier;
	var name  = task.name;

	var customData = task.customData;

	itemName     = customData[0].value;
	portion      = customData[1].value;
	price        = Helper.ToNumber(customData[2].value);
	taxTotal     = Helper.ToNumber(customData[3].value);
	ticketTotal  = Helper.ToNumber(customData[4].value);
	pricePlusTax = price + taxTotal;

	var info = '';
	info+='Item : ' + itemName + ' ('+portion+')';
	info+='\rPrice: ' + Helper.Format(price);
	info+='\rTax  : ' + Helper.Format(taxTotal);
	info+='\rTotal: ' + Helper.Format(pricePlusTax);

	dlg.ShowMessage(info);
	
	var formattedInfo = '<font Consolas>' + info.replace(/\r/g,'<br/>') + '</font>';
	
	dlg.AskQuestion(formattedInfo,'Ok');


	// delete the Task
	var del = gql.Exec('mutation m {deleteTask(id:'+id+'){id}}');

	return formattedInfo;
}

##Rule (uses Automation Command Button but you could invoke this from barcode scanned)


##Working

1 Like

Or just hire Q or JTR to do it for you. lol.
I think they can provide you with script to update 5000+ items price in minutes.

How can I change prices from sql? But I want to change prices for specific group code.

how can I invoke this from barcode scanned?
can we configure the way we can scan the item after pressing price check?

oh boy, Q task method is even harder than this. Even this simpler one you still hard time understand it. Most of us still never really used task yet (including me). Maybe only Q that used it.

To be honest, why on ticket line at cashier you want to show price without tax and price check including tax. It should be consistent. At a glance it will look like different price.

could you tell me the way i can change all the product prices with sql?
All products=(product price*12.5%)

Yeah you are thats why the best soloution is to change all the item prices.