They are shortened placeholders for the table names, and they can be anything you want. They are “required” when you join 2 or more tables which contain columns with the same name. For example, all of the tables contain [Id] and most also contain [Name]. So when you join the tables, you need to specify which [Id] or [Name] you want to reference, since SQL won’t know which one to choose and will throw an error.
Instead of writing:
FROM [Orders] JOIN [Tickets] on [Tickets].[Id] = [Orders].[TicketId] WHERE 1=1
AND [Orders].[OrderStates] like '%" + stateName + "%'";
We use the place holders:
FROM [Orders] o JOIN [Tickets] t on t.[Id] = o.[TicketId] WHERE 1=1
AND o.[OrderStates] like '%" + stateName + "%'";
P.S. for what you are trying to get, you don’t even need to join the Tickets table at all. Since you don’t need the JOIN, you don’t need the placeholders either:
function getOrderSum(stateName, state) {
var qry = "";
var r = "";
var wpBeg = "";
var wpEnd = "";
qry = "SELECT [StartDate] FROM [WorkPeriods] WHERE [Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate] != [EndDate])";
wpBeg = sql.Query(qry).All;
qry = "SELECT [EndDate] FROM [WorkPeriods] WHERE [Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate] != [EndDate])";
wpEnd = sql.Query(qry).All;
//return "Start:"+wpBeg[0] + " End:"+wpEnd[0];
qry = "";
qry += "SELECT sum( [Price] * [Quantity] ) as [TotalAmount] FROM [Orders] WHERE 1=1";
qry += " AND [OrderStates] like '%" + stateName + "%'";
qry += " AND [OrderStates] like '%" + state + "%'";
qry += " AND [CreatedDateTime] >= '" + wpBeg[0] + "'";
qry += " AND [CreatedDateTime] <= '" + wpEnd[0] + "'";
var r = sql.Query(qry).All;
return "Total Amount (" + state + "): " +r[0];
}
HOWEVER I forgot a vital part, the whole day values MUST exclude the tickets (or orders) that have already been posted as part of the first ‘room charge’ one which is based on ticket…
Anychance of an idea for that?
As I am yet to fully understand the table relationships your experience would be invaluable.
Emre suggested previously to use a ‘room post’ payment type to seperate them.
Alternatively it might me a simpler solution to add an additional order state of ‘Posted’ and filter these out of that report…
I guess you will need to join the Ticket table in that case. Then filter based on a Ticket State, Ticket Tag, or Payment Type, Ticket Type, Department or whatever.
Until you know how you’re going to “flag” the Ticket, I can’t say what you would filter on. If it is a Payment Type, you will need to JOIN the Payments table and possibly the PaymentTypes table.
As your alternative, you could use the Order State method and just filter it out without having to need other joins.
qry += " AND [OrderStates] not like '%" + excludedStateName + "%'";
qry += " AND [OrderStates] not like '%" + excludedState + "%'";
An additional payment type would show split cleanly on samba report but when it domes to splitting for the end of day post to pms api it is flexible and can be implemented as needed.
The order level would obviously make scripting simpler but as said is not decided yet and open to ideas/suggestions.
Fair enough.
Although it would only be a single action to update the orders with a specific room post state.
Will see how it progresses, there will likely be some additional REST post types for things like vouchers(managed through PMS) and debtors ledgers (again managed through PMS) so will see how it progresses.
The room post script (well in fact both - just encase) will also need some if type expressions to ignore if there is a food or drink only ticket posted.
Also there is still the decision to be made as to the level of breakdown on the posts ie drinks/food or more detail.
Another thing to think about will be how flexible to make the scripts, if posible would look to define the PMSDepartments as values directly interperatable to the PMS to leave the system open to other sites without defining the connections between drinks --> their account/department on PMS which would leave the setup more open to others without allot of rescripting.
Would have liked to have done the split by product group but as it is a freely selectable field it would be risky for unexpected additions - although still the case as done so far at least it’s values that are defined which raises the awareness of the link more than groups would.
Thanks for your help again, I will get there eventually but would struggle to have done it without you without spending next few weeks learning SQL
Your explanations have really helped with my understand and greatly appreciate it.
Might need to pester you for some assistance with a ‘loop’ script should I like to make it more Versatel when it comes to post summarisation - ie looping through a list of states or the product group ideas.
Also the ‘verification’ of a post to API which emre said should be doable with payment processors in such a way that if the expected Json responce isn’t as expected ie post failed that your prompted to try again or print prevail receipt for manual transfer at later date.
So for example, I have a handler called StringFunctions and inside that I have many functions, one of which is called PAD(). Another is called FormatDate().
var sf = script.Load("StringFunctions","PAD","FormatDate");
var paddedvalue = sf.PAD(unpaddedvalue,"left",10,"0");
var formatteddate = sf.FormatDate(unformatteddate);
sf. is a general thing right? Something like ScriptFunction?
Referring to ‘loaded methods’?
If you load a script do you load the return value or is it just a place holder and you get its variables etc aswell?
Sorry looking at your example I guess it’s the return value and the sf.FUNCTION(value) runs that script using that value and returns the result as the value for that variable?
God I’m in this deeper than I probably need but never hurts
What about a function in the same script?
Same thing just using its own handler?
sf is arbitrary… it basically becomes the referenced Handler name.
You don’t get the functions variables. Function variables are scoped to the function and are not valid outside the function unless they are declared “globally”. What you get is the Return value of the function (if it returns a value at all). You can’t expect to read the value of a variable contained in one function, inside another function.
You can have many functions within a script, and there is no special handling needed. For example, here we have 4 functions in a single handler called db:
Notice the 2nd function updaterow() calls the 1st function getSettingbydb() with no need for special handling, importing, loading or declaration.
Now if I want to use any of these functions in another script, I need to declare them, like this, and I only need to “load” the ones I want to use:
// HANDLER: crazyStuff
// globally declare dbFuncs so any other functions in this script can access and use it
var dbFuncs = script.Load("db","updaterow","getAccountNameById");
// ^^declared handler ^^handler ^^functions^^
// we don't need to use a wrapper function at all... it will declare and set a global variable for this entire script (accessible to any function in the handler)...
var theNameOfTheAccount = dbFuncs.getAccountNameById(accountId);
// ^^ variable has "global" scope
function modifyDBvalue(newValue) {
dbFuncs.updaterow(newValue);
}
function getAccount(accountId) {
var accountName = dbFuncs.getAccountNameById(accountId);
return accountName;
}
function updateAccountInfo(accId,newInfo) {
// either of these will work the same
var aName = getAccount(accId); // use the above "internal" function, which calls the "imported" function
var aName2 = dbFuncs.getAccountNameById(accId); // use the "imported" function on its own
sql.ExecSql("UPDATE Accounts SET Balance = '"+newInfo+"' WHERE Name = '"+aName+"'");
}
Ok, so this is a bit of a crazy example, and it isn’t really practical, but you should get the point. Now in a CALL:X, we could do:
