Lower User Roles not allowed to create higher Role

[JTRTech]

Also its .First not .First() on the query function…

[JTRTech]

Would help if I was looking at the right database LOL

[JTRTech]

Ok, got it to insert but its inserting a seccond time so validation is skew somewhere…
also getting error;

Microsoft.ClearScript.VoidResult

[JTRTech]

@JTRTech

but its inserting a seccond time so validation is skew somewhere

Sorry, no its not, there isnt duplicates validation yet LOL

Hmmm… sort error first.

[GreatShakesBar]

Just restarting, lets see if it at least made the user

[JTRTech]

Ok, error was it was trying to return the result of the INSERT which I believe given the error is an SQL type value needing parsing to be returned…;

function createUser(u,p) {
  // can get User and Pin fed via function or 
  //use Data.Get to retrieve Program Settings
  var usr = (typeof u === 'undefined' || u=='' ? Data.Get("Username") : u);
  var pin = (typeof p === 'undefined' || p=='' ? Data.Get("PIN") : p);
  var grp = 'Admin';

  var q="SELECT [Id] FROM [UserRoles] WHERE [Name]='" + grp + "'";
  var grp = sql.Query(q).First;

  // more validation
  usr = (typeof usr === 'undefined' ? '' : usr);
  pin = (typeof pin === 'undefined' ? '' : pin);
  grp = (typeof grp === 'undefined' ? '' : grp);

  if (usr!='' && pin!='' && grp!='') {
    // run SQL to create the User
    q = "INSERT INTO [Users] ([PinCode], [Name], [UserRole_Id]) VALUES ('" + pin + "', '" + usr + "', '"+grp+"')";
    var r = sql.ExecSql(q);
    return true;
  }
  return false;
}

[JTRTech]

That has no validation preventing duplicates which youll need to add before using as that will likely cause samba crash I expect…

[GreatShakesBar]

@JTRTech

That has no validation preventing duplicates which youll need to add before using as that will likely cause samba crash I expect

Didn’t you add somesort of validation above?

BTW appreciate you helping me there

[GreatShakesBar]

@JTRTech

This update program setting maybe shows more into how you might do the unique user/pin verification;

function updateValue(settingName,settingValue) {
var qry = “SELECT count([Name]) as CT FROM [ProgramSettingValues] WHERE [Name]='”+settingName+“'”; //–check if the ProgramSetting EXISTS
var r = sql.Query(qry).First;
if (r==0)
{
qry = “INSERT INTO [ProgramSettingValues] ([Name], [Value]) VALUES ('”+settingName+“‘,’”+settingValue+“')”; //–if ProgramSetting does NOT exist, insert a row
sql.ExecSql(qry);
} else {
qry = “UPDATE [ProgramSettingValues] SET [Value]='”+settingValue+“’ WHERE [Name]='”+settingName+“'”; //–if ProgramSetting DOES exist, update the value
sql.ExecSql(qry);
}
}

Wasn’t this something to do with if user already exists?

[JTRTech]

Yes, ahead of you though
Here you go…;

function createUser(u,p) {
  // can get User and Pin fed via function or 
  //use Data.Get to retrieve Program Settings
  var usr = (typeof u === 'undefined' || u=='' ? Data.Get("Username") : u);
  var pin = (typeof p === 'undefined' || p=='' ? Data.Get("PIN") : p);
  var grp = 'Admin';

  var q="SELECT [Id] FROM [UserRoles] WHERE [Name]='" + grp + "'";
  var grp = sql.Query(q).First;

  // more validation
  usr = (typeof usr === 'undefined' ? '' : usr);
  pin = (typeof pin === 'undefined' ? '' : pin);
  grp = (typeof grp === 'undefined' ? '' : grp);

  var q="SELECT COUNT([Name]) AS [COUNT] FROM [Users] WHERE [Name]='" + usr + "'";
  var nameCount = sql.Query(q).First;


  var q="SELECT COUNT([Name]) AS [COUNT] FROM [Users] WHERE [PinCode]='" + pin + "'";
  var pinCount = sql.Query(q).First;

  if (usr!='' && pin!='' && grp!='' && nameCount==0 && pinCount==0) {
    // run SQL to create the User
    q = "INSERT INTO [Users] ([PinCode], [Name], [UserRole_Id]) VALUES ('" + pin + "', '" + usr + "', '"+grp+"')";
    var r = sql.ExecSql(q);
    return true;
  }
  return false;
}

[JTRTech]

Probably want to add a ask question/show message helper after each of the counts with if count>0 show message.

[GreatShakesBar]

You are a diamond,

I’m pretty sure I can now just add another QUERY line to add the correct details into the ENTITY fields too.

Don’t tell me if I can or cant though, ill work that out.

All of you have saved me a massive ballache now. Going to implement this first thing on the till

[JTRTech]

@GreatShakesBar

I’m pretty sure I can now just add another QUERY line to add the correct details into the ENTITY fields to

No need, there is a helper for that… single line to update data, used many times in my PMS intergrations (although that topic is not public at the moment)
Not got to hand and going to the pub LOL so leave you to it

[GreatShakesBar]

@JTRTech

there is a helper for that…

don’t understand

Go have a beer for me. I’m in bed now hahrrrrr

[JTRTech]

Going now.

Doont worry about the show message. Just use the returns, see below, that should do the job;

function createUser(u,p) {
  // can get User and Pin fed via function or 
  //use Data.Get to retrieve Program Settings
  var usr = (typeof u === 'undefined' || u=='' ? Data.Get("Username") : u);
  var pin = (typeof p === 'undefined' || p=='' ? Data.Get("PIN") : p);
  var grp = 'Admin';

  var q="SELECT [Id] FROM [UserRoles] WHERE [Name]='" + grp + "'";
  var grp = sql.Query(q).First;

  // more validation
  usr = (typeof usr === 'undefined' ? '' : usr);
  pin = (typeof pin === 'undefined' ? '' : pin);
  grp = (typeof grp === 'undefined' ? '' : grp);
  
  //check for existing user with that name
  var q="SELECT COUNT([Name]) AS [COUNT] FROM [Users] WHERE [Name]='" + usr + "'";
  var nameCount = sql.Query(q).First;

  //ceck for existing user with that pincode
  var q="SELECT COUNT([Name]) AS [COUNT] FROM [Users] WHERE [PinCode]='" + pin + "'";
  var pinCount = sql.Query(q).First;

  if (usr!='' && pin!='' && grp!='' && nameCount==0 && pinCount==0) {
    // run SQL to create the User
    q = "INSERT INTO [Users] ([PinCode], [Name], [UserRole_Id]) VALUES ('" + pin + "', '" + usr + "', '"+grp+"')";
    var r = sql.ExecSql(q);
    return 'User '+ usr +' Created;
  }
  return 'User or PinCode Already in use';
}

[GreatShakesBar]

Cheers dude, sound like a douche…

Unterminated string constant

Anyway, go enjoy your beer. We can fix this tomorrow

Matt

[JTRTech]

@GreatShakesBar

there is a helper for that…

don’t understand

This;
api.Entity().Data().Update();
Dont need to set as var just on its own line;

function testUpdate(entityName,entityField,fieldValue) {
  api.Entity(entityName).Data(entityField).Update(fieldValue);
  return 'Updated';
}

Typed that freehand but should work…

Put it in a loop like this and you can make an API intergration to update a bunch of entities in one go

function runRoomUpdates() {
	var entityCount 					= sqlEntityCount(roomEntityType);																				//--SQL Query responce -> entityCount Variable
	var entityList 						= sqlEntityList(roomEntityType);																				//--SQL Query responce -> entities Variable (comma seperated list of entities)
	for (var e = 0; e<entityCount; e++) { 																												//--Open entity cycle loop (Loop cycle count based on 'entityCount' variable and 'entityList' list)
		var loopRoomName				= entityList[e];																								//--Set 'roomname' Variable as Room Entity Name from 'entityList' list from above SQL query responce
		var loopRoomArrayField			= api.Entity(loopRoomName).Data(entityField_roomArray).Get();													//--Pull Entity Data for NewBook Array Data Field from Entity Name 'roomname' in Loop
		var RStatus						= api.Entity(loopRoomName).State('RStatus').Get();
		if (loopRoomArrayField == 'NOT INHOUSE') {																										//--IF room booking not inhouse
		api.Entity(loopRoomName).Data(entityField_bookingid).Update('');																				//--Update Entity Data Fields with responce variables data
		api.Entity(loopRoomName).Data(entityField_status).Update('');																					//--Update Entity Data Fields with responce variables data
		api.Entity(loopRoomName).Data(entityField_fullname).Update('');																					//--Update Entity Data Fields with responce variables data
		api.Entity(loopRoomName).Data(entityField_arrive).Update('');																					//--Update Entity Data Fields with responce variables data
		api.Entity(loopRoomName).Data(entityField_depart).Update('');																					//--Update Entity Data Fields with responce variables data
		api.Entity(loopRoomName).Data(entityField_pax).Update('');																						//--Update Entity Data Fields with responce variables data
		api.Entity(loopRoomName).Data(entityField_accountid).Update('');																				//--Update Entity Data Fields with responce variables data
		api.Entity(loopRoomName).Data(entityField_siteStatus).Update('');																				//--Update Entity Data Fields with responce variables data
		api.Entity(loopRoomName).State('Booking').Update('Vacant');

[JTRTech]

@GreatShakesBar

Unterminated string constant

My bad;
return 'User ‘+ usr +’ Created;
Should be
return ‘User ‘+ usr +’ Created’;

So;

function createUser(u,p) {
  // can get User and Pin fed via function or 
  //use Data.Get to retrieve Program Settings
  var usr = (typeof u === 'undefined' || u=='' ? Data.Get("Username") : u);
  var pin = (typeof p === 'undefined' || p=='' ? Data.Get("PIN") : p);
  var grp = 'Admin';

  var q="SELECT [Id] FROM [UserRoles] WHERE [Name]='" + grp + "'";
  var grp = sql.Query(q).First;

  // more validation
  usr = (typeof usr === 'undefined' ? '' : usr);
  pin = (typeof pin === 'undefined' ? '' : pin);
  grp = (typeof grp === 'undefined' ? '' : grp);
  
  //check for existing user with that name
  var q="SELECT COUNT([Name]) AS [COUNT] FROM [Users] WHERE [Name]='" + usr + "'";
  var nameCount = sql.Query(q).First;

  //ceck for existing user with that pincode
  var q="SELECT COUNT([Name]) AS [COUNT] FROM [Users] WHERE [PinCode]='" + pin + "'";
  var pinCount = sql.Query(q).First;

  if (usr!='' && pin!='' && grp!='' && nameCount==0 && pinCount==0) {
    // run SQL to create the User
    q = "INSERT INTO [Users] ([PinCode], [Name], [UserRole_Id]) VALUES ('" + pin + "', '" + usr + "', '"+grp+"')";
    var r = sql.ExecSql(q);
    return 'User '+ usr +' Created';
  }
  return 'User or PinCode Already in use';
}

[Jesse]

Entities can be handled with the API. Its much easier.

http://sambapos.com/wiki/doku.php?id=jscript_api

[JTRTech]

Dam pub was closed

@JTRTech

Q must be tired, hes usually s**t hot for SQL.

But pub made me realize what it was, not tired… too many JD and gingers the other day LOL