Reading a Global Setting in a script


#1

I am trying to create a currency update button on the navigation screen in order that my staff input the local bank issued Exchange Rate for Sri Lanka Rupees to US$. There is no API available to do this automatically and I need a simple method to input the Rate.
I have set up a simple button which displays the current rate and execute an automation command to set Global Program setting named “Dollarrate” with a prompt [?Todays Dollar Rate;;;ONC].
This works fine
I then execute a script designed to update the US$ currency value set up in the database as detailed below.

function exchangerate()
    {
    dlg.ShowMessage('Hello');
    
   function getValue(settingName) {
  var qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+Dollarrate+"'";
  var rate = sql.Query(qry).First;
  return rate;
}
    
    // check if a rate is set
    var isRateValid = ( rate == 'undefined' ? false : true );
    
    // execute the update if rate and date is valid
    if ( isRateValid ) 
      {
    //create SQL statement, CHANGE if needed
      var sqlexec = "UPDATE ForeignCurrencies SET ForeignCurrencies.ExchangeRate = " +rate+ " WHERE ForeignCurrencies.name = 'US$'";
    //execute SQL statement
      sql.ExecSql(sqlexec); 
      }
    dlg.ShowMessage('Rate:'+rate);  
    }

The script executes fine but I cannot set var rate with the value of Dollarrate. If I manually set the var rate to a value using var rate = 160, the script executes perfectly .

The problem appears to be in the following section of script

function getValue(settingName) {
  var qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+Dollarrate+"'";
  var rate = sql.Query(qry).First;
  return rate;
}

Maybe I have some syntax error in here but this is copied from working examples shown by @QMcKay in numerous posts. Anyone know what I am doing wrong?


#2

Variable Dollarrate has no value according to your script. You are not passing in a value as a parameter, nor are you hard-coding a value for that variable. So it is like executing the following query:

SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='undefined'

#3

Dollarate is a Global Program Setting I have defined in a Program setting Action as part of this automation. It is a value assigned by a prompt as detailed below


#4

Right, but you are using a VARIABLE in your JScript named Dollarate, and that variable has no value (undefined).

Just initialize the variable before anything else:

var Dollarate = 'Dollarate';

#5

added that the beginning of the script but no effect.

Maybe I am overcomplicationg this. I need to set the script variable rate with the value set in the Program Setting Dollarrate. I do not need to set Dollarrate as a variable in the script, I just need to apply the value input by the prompt in my automation to the script var rate


#6

No, it is not complicated; you just don’t understand how to code using JS LOL.

Also, you could use GraphQL instead of SQL, but you still need to use JS, and if you don’t understand what I am trying to tell you about your JS Variables, then it won’t help at all … see how I initialize the variables here first, to give them a value that gets concatenated into the GQL query …

  var settingName = 'Dollarate';
  var settingValue = 1.234;

      gqlCommand = 'mutation m {updateLocalSetting(name:"'+settingName+'",value:"'+settingValue+'"){name,value}}';
      gql.Exec(gqlCommand);

#7

That is very true. I need to do some more reading and experimenting. Is there a good section that will give me some better idea of the JS syntax and structure as cribbing other peoples scripts and trying to adapt them is clearly not ideal.

Thanks for the help as always.


#8

Internet and Google, not here.


#9

Is it just me or are you not declaring a function within a function here?

function exchangerate()
    {
    dlg.ShowMessage('Hello');
    
   function getValue(settingName) {
  var qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+Dollarrate+"'";
  var rate = sql.Query(qry).First;
  return rate;
}
    
    // check if a rate is set
    var isRateValid = ( rate == 'undefined' ? false : true );
    
    // execute the update if rate and date is valid
    if ( isRateValid )

#10

Hi Joe @JTRTech

Yes I see, this is due to cutting and pasting various code to read a Program Setting Value with JScript and not checking properly.

I thought this would be a simple bit of code to start learning JS with but clearly no such thing exists (for me anyway). I have been reading through your early days in generating your Newbook link. I now have a PMS that I will be linking with some simple(!) JSON commands and the first thing I wanted to do was synchronise my exchange rates on the 2 systems. Set up a simple button to input the days exchange rate from the Navigation screen, saved that to a Program setting called Dollarrate.

Now I cannot read this value with the script, which I need to do to then apply this to currency settings thus
var sqlexec = “UPDATE ForeignCurrencies SET ForeignCurrencies.ExchangeRate = " +rate+ " WHERE ForeignCurrencies.name = ‘US$’”;

This works in changing the exchange rate but I just cant read the Program setting to apply to the rate variable in my script.


#11

What version of samba are you running?

So you have been able to set the rate in the database using program settings but can’t read it out?


#12

You will only need the “GQL” functions or the “SQL” functions; not both. This is just to illustrate different ways to get the same result. The “SQL” functions were the only way to do this via JScript before GraphQL came into existence. Now my preference is the “GQL” methods.

Name: Update Setting
Handler: setting

function readGlobal(settingName,method) {
	settingName  = typeof settingName  === 'undefined' ? '' : settingName;
	method  = typeof method  === 'undefined' ? 'GQL' : method;
	
	var returnVal = "UNKNOWN";

    if (method=='SQL') {
	  returnVal = readGlobalSQL(settingName);
	} else {
	  returnVal = readGlobalGQL(settingName);
	}
	
	return returnVal;
}

function readGlobalSQL(settingName) {
	settingName  = typeof settingName  === 'undefined' ? '' : settingName;
	
	var qry = "";
	var res = "";
	var currentValue = "UNKNOWN";
	var newValue = "";
	
	if (settingName != '') {
	  qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name] = '" + settingName + "'";
	  res = sql.Exec(qry);
	  currentValue = res[0];
	}

    return currentValue;
}

function readGlobalGQL(settingName) {
	settingName  = typeof settingName  === 'undefined' ? '' : settingName;
	
	var qry = "";
	var res = "";
	var currentValue = "UNKNOWN";
	var newValue = "";
	
	if (settingName != '') {
      qry = '{setting:getGlobalSetting(name:"'+ settingName + '"){name,value}}';
	  res = gql.Exec(qry);
	  res = JSON.parse(res);
	  currentValue = res.data.setting.value;
	}

    return currentValue;
}

function updateGlobal(settingName,settingValue,method) {
	settingName  = typeof settingName  === 'undefined' ? '' : settingName;
	settingValue = typeof settingValue === 'undefined' ? '' : settingValue;
	method  = typeof method  === 'undefined' ? 'GQL' : method;
	
	var returnVal = "UNKNOWN";

    if (method=='SQL') {
	  returnVal = updateGlobalSQL(settingName,settingValue);
	} else {
	  returnVal = updateGlobalGQL(settingName,settingValue);
	}
	
	return renturnVal;
}


function updateGlobalSQL(settingName,settingValue) {
	settingName  = typeof settingName  === 'undefined' ? '' : settingName;
	settingValue = typeof settingValue === 'undefined' ? '' : settingValue;
	
	var qry = "";
	var res = "";
	var currentValue = "";
	var newValue = "";
	
	currentValue = readGlobalSQL(settingName);
	
	if (settingValue == 'increase') {
		newValue = Helper.ToNumber(currentValue) + 1;
	} else if (settingValue == 'decrease') {
		newValue = Helper.ToNumber(currentValue) - 1;
	} else {
		newValue = settingValue;
	}
	
	qry = "UPDATE [ProgramSettingValues] SET [Value]='" + newValue + "' WHERE [Name] = '" + settingName + "'";
	res = sql.Exec(qry);
	
	return newValue;
}

function updateGlobalGQL(settingName,settingValue) {
	settingName  = typeof settingName  === 'undefined' ? '' : settingName;
	settingValue = typeof settingValue === 'undefined' ? '' : settingValue;
	
	var qry = "";
	var res = "";
	var currentValue = "";
	var newValue = "";
	
	qry = '{setting:getGlobalSetting(name:"'+ settingName + '"){name,value}}';
	res = gql.Exec(qry);
	res = JSON.parse(res);
	currentValue = readGlobalGQL(settingName);
	
	if (settingValue == 'increase') {
		newValue = Helper.ToNumber(currentValue) + 1;
	} else if (settingValue == 'decrease') {
		newValue = Helper.ToNumber(currentValue) - 1;
	} else {
		newValue = settingValue;
	}
	
	qry = 'mutation m {setting:updateGlobalSetting(name:"'+ settingName + '",value:"' + newValue + '"){name,value}}';
	res = gql.Exec(qry);
	res = JSON.parse(res);
	newValue = res.data.setting.value;
	
	return newValue;
}

Usage:

setting.readGlobal('mySettingName')
setting.updateGlobal('mySettingName','mysettingValue')


#13

Thank you I will work through this to make sure I understand what is happening here.


#14

So guys have learnt a bit more about scripts but having some problem with Posting JSON.

The script below works fine until trying to Post data to Beds24 api.

The script collects the exchange rate, applies it to the currency setting in Samba and applies it using the var rate to the JSON script I am trying to Post to the Beds24 API.

the variable jsondata is a formatted JSON string which I have posted to the Beds24 page both manually and with Postmaster. Both methods work fine and update the Beds24 site accordingly.

Nothing happens with Sambapos and something is wrong with the script as my final Test message does not display.

This works

and this works

But my attempt to Post this working JSON data through SambaPOS does not work.


#15

So the samba post is t working or the rate in samba isn’t updating?


#16

The samba post is not working. The script for the rate is good and updates in Samba. Maybe should put this under a different topic?

Everything works until I get to web.PostJson


#17

Personally I would return the postdata and drop in json validator site to first check the string is correctly formatted.

Do you get nothing in your response?

One this you might want to check is the TLS version required by the API
Newbook upped their minimum for PCI reasons which requires a registry edit for .net to force higher security level.

See


#18

Now I am even more confused. The variable jsondata validates in Postman and returns a value as follows

{
“modifyProperty”: [
{
“template4”: “158.88”
}
]
}

which is what I expected to see (setting template4 with the value defined by currency rate). Also direct input of the variable into the text box of the API webpage also works perfectly.

If I paste the jsondata into different Json validation sites it gives me different responses and I cannot understand what I need to do.

Is there an alternative URL I can use from SambaPos which will show me exactly what web.PostJson is posting (if it is actually posting anything)

JSONlint.com tells me it is valid JSON. My worry is web.PostJson in Samba is somehow changing the output.


#19

I put the variable jsondata into JSONlint.com as below

Pressed the validate button and got the following

So it validated the structure of the variable and then tarted it up for me also. if sambaPOS is posting the variable unchanged, there is no reason this should not work.

Incidentally I copied the tarted up version of the Json string into my samba script and it completely broke the script execution.


#20

Yer, you can’t do multiple lines raw like that is that’s what you mean.
So formatting is good.
Did you look at the top security suggestion?