Second User Login Pin


#1

OK, so going live with tablets today and last task was a second pin for users to use on tablets as RFIDs are used for main terminals.

Had more elaborate plans to use the password field but struggled to get that working so have opted for program setting per user for now.

So firstly I extended my user scripts from the custom admin pin tutorial and added some old scripts I had for program settings.

function canConfirmAdminPin(inputPin){
var userPinCheck = checkUserPin(inputPin);
var tabletPinCheck = checkTabletPin(inputPin);
if(userPinCheck != 0){	
	qry = 	"SELECT u.Name as UserName ,max(case when p.[Name] = 'CanConfirmAdminPin' then p.value end) as CanConfirmAdminPin	";
	qry +=	"FROM [Users] u JOIN [UserRoles] r on r.Id = u.UserRole_Id JOIN [Permissions] p on r.id = p.UserRoleId				";
	qry +=	"WHERE u.PinCode = '"+inputPin+"'";
	qry +=	"GROUP BY u.Name";
	var userQry = sql.Query(qry).Delimit('~').First;
} else if (tabletPinCheck != 0) {
	var tabletPinSettingName = tabletPinUser(inputPin);
	var tabletPinUserName = tabletPinSettingName.slice(1+tabletPinSettingName.indexOf('_'));
	qry = 	"SELECT u.Name as UserName ,max(case when p.[Name] = 'CanConfirmAdminPin' then p.value end) as CanConfirmAdminPin	";
	qry +=	"FROM [Users] u JOIN [UserRoles] r on r.Id = u.UserRole_Id JOIN [Permissions] p on r.id = p.UserRoleId				";
	qry +=	"WHERE u.Name = '"+tabletPinUserName+"'";
	qry +=	"GROUP BY u.Name";
	var userQry = sql.Query(qry).Delimit('~').First;
} else {
	return false
}
var userQryArray = userQry.split('~');
var userName = userQryArray[0];
var canConfirmAdminPin = userQryArray[1];
if (canConfirmAdminPin==0){
	return userName
} else {
	return false
}
}

function updateTabletPin(currentUser,inputPin){
	var tabletPinCheck = checkTabletPin(inputPin);
	var userPinCheck = checkUserPin(inputPin);
	if(tabletPinCheck != 0 || userPinCheck != 0){
		dlg.AskQuestion("Pin cannot be duplicated","OK");
	} else {
		updateValue('TabletPin_'+currentUser,inputPin);
		dlg.AskQuestion("Tablet pin for "+currentUser+" has been set","OK");
	}
}

function tabletPinLogin(inputPin){
	var tabletPinCheck = checkTabletPin(inputPin);
	if(tabletPinCheck != 0){
		var tabletPinSettingName = tabletPinUser(inputPin);
		return tabletPinSettingName.slice(1+tabletPinSettingName.indexOf('_'))
	}
}

function checkUserPin(inputPin){
	qry =	"SELECT COUNT([Name]) AS [Count]";
	qry +=	"FROM [Users]";
	qry +=	"WHERE [PinCode] = '"+inputPin+"'";
	var userQry = sql.Query(qry).First;
	return userQry
}

function checkTabletPin(inputPin){
	qry =	"SELECT COUNT([Name]) AS [Count]";
	qry +=	"FROM [ProgramSettingValues]";
	qry +=	"WHERE [Name] LIKE 'TabletPin_%' AND [Value] = '"+inputPin+"'";
	var userQry = sql.Query(qry).First;
	return userQry
}

function tabletPinUser(inputPin){
	qry =	"SELECT [Name] AS [UserName]";
	qry +=	"FROM [ProgramSettingValues]";
	qry +=	"WHERE [Name] LIKE 'TabletPin_%' AND [Value] = '"+inputPin+"'";
	var userQry = sql.Query(qry).First;
	return userQry
}

//--Get program setting
function getValue(settingName) {
  var qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+settingName+"'";
  var r = sql.Query(qry).First;
  return r;
}

//--Set program setting
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);
		}
}

There are several extras here but in short;

updateTabletPin(currentUser,inputPin)
would be used to update the program setting for TabletPin_<username>
it needs the uuser name for the ‘tablet pin’ and a pin.
it will check to make sure no user or existing ‘tablet pin’ program settings exists that matches that code.

checkUserPin(inputPin)
checkTabletPin(inputPin)
both of these functions return a count of the number of users or tablet pin program settings with same pin value.

tabletPinUser(inputPin)
This returns the name of the setting for the matching ‘tablet pin’

tabletPinLogin(inputPin)
this will check the given pin is a ‘tablet pin’ value, get the setting name (TabletPin_username) and slice off the tabletpin prefix which will be used in the login action.

getValue(settingName)
updateValue(settingName,settingValue)
These are just old p[rogram setting scripts I had from another project.

canConfirmAdminPin(inputPin)
This is from my custom confirm admin pin flow but I have extended it to also check ‘TabletPin’ values and if matches one of those then lookup the permissions for associated user.

So need a login user action;

image

Then a pin code entered event rule.
This event is triggered on an incorrect pin entry on login screen.

image

So firstly my constraint checks if the entered (failed) pin is a ‘TabletPin’
If so the action fires and triggers a login using the username derived from the ‘TabletPin’ setting name.

I also change mapping for this rule to only trigger on tablet terminals as want to make then continue to use RFID on tills. Prefer this as tablets are more ‘personal’ in that when typing their pin they are not open for people to see them type it in like on the main tills.

image

All i did then was a way for users to set their tablet pin, so added an automation command to nav screen;

image

image

The validation and ask question result messages are triggered from the script.


#2

Reserved for updates …