Dual Pins for Users


#21

Thats how i imagine it to work and is what i originally wanted to do with users before doing the entity setup. If this works as we think i may change back to users setup and not entities as users :grinning:


#22

@emre i think it would be useful to have an additonal field in the login user action of PIN code

So we can use this action as part of a switch user setup. The idea being that a member of staff swipes their card in an open ticket (which contains their login PIN), this then closes that ticket, logs the current user out (which we can do already) but then the login user action needs to be able to read the pin that was swiped from the card from { NUMBERPAD } to log in the user

But to do this dont we need the PIN code field in the login user action so we can use number pad value in that field so it reads the pin. At the moment we can only set it as the user name?

Even with using the new pin code entered rule we would need this. I see it as using the pin code entered rule (which comes from swiping the card or rfid etc) there would be maybe 4 actions in this rule:

  1. Add ticket tag of current user so ticket can be closed
  2. Add close ticket action
  3. Add logout action
  4. Add login user action reading the pin swiped from the card/rfid etc to load the new user

(May need a couple of separate rules if this doesnt work in 1 but thats the idea

Would this be possible if you could add the additional field to the action?


#23

What is the action expecting @RickH? I haven’t had chance to look yet as flat out at new property…
Is it wanting username?
If so we can workaround with a script option to use pin aswell would be handy.


#24

Yea just option for username so of we could get pin added too thatd make things a whole lot easier :wink:


#25

Just use the already available number pad entered rule?


#26

How would the login action know who to login as it can only read the username and not pin


#27

You decide that with constraints


#28

Does that like mean your going to add this @emre? :grinning:


#29

You have all you need right now


#30

I’m not entirely sure how this works… but… what if you had an ask question action with a numberpad that has constraints that say if pin entered is xxxx login ‘user’? And only make that mapped to a tablet?


#31

To what end? we didnt have login user action before so wouldnt have helped and what would you do with the pin?


#32

Like I said I’m not quite sure what the new action does or how it works… I was going on the possibility that it logs in a user when actioned?

And if so… could it be constrained to action if a pin entered on a numberpad within an ask question action would work…?


#33

For switch user I imagine numberpad entered -> validated as a pin --> ticket layed away --> ticket closed --> current user logged out --> user for verified pin is logged in

The subject of the topic was dual pin so (and I see some backlash from kendash :stuck_out_tongue_winking_eye: ) would be something like your create user automation flow which duplicates username as entity with a rfid and pin fields, one being the default user pin and the other manual pin being used to check the new Pin Code Entered rule to allow a user to have 2 logins, long rfid pin and shorter manual pin.


#34

This is what the Action and Rule look like.

The Login User Action requires the User Name as it’s parameter. No PIN.

The Pin Code Entered Rule will only be fired if the PIN does not match a configured User PIN. That is, it only triggers on NON-valid PINs.

So you could theoretically load up the Rule with multiple Login User Actions, each with their own PIN constraint (ie. for a FOB number), which then fires JScript to look up the matching User Name for that PIN, whether that User Name comes from a SQL call, or an API call to a Staff Entity Custom Data Field, to return a User Name that can then be used with the Login User Action…


#35

Awesome! Gonna be playing with this tomorrow whilst having a hangover in bed hahah


#36

That’s what I was trying to convey with little time to spell it out. Thank you Q for clarifying my thought for them :slight_smile:


#37

Documenting to get FOB/User from CustomData (SQL 2016) …

##Query

SELECT
e.[Id]
,e.[Name]
,jsonData.jsonValue as [FOB]
--,e.[CustomData]
FROM [Entities] e
JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId]
JOIN [EntityCustomFields] cf on cf.[EntityTypeId] = e.[EntityTypeId]
-- here we "join" the [Entities] table to itself and use the OPENJSON function
-- on the [CustomData] column
CROSS APPLY OPENJSON(e.[CustomData])
-- this WITH portion allows explicit definition of the schema JSON Keys for output
-- and gives references to the columns/field above in the SELECT portion
WITH (
  jsonName  varchar(50) '$.Name'
 ,jsonValue varchar(50) '$.Value'
) jsonData
WHERE et.[Name] = 'Employees'
AND cf.[Name] = 'FOB'
AND jsonName = 'FOB'


##Function

function getEntityByCustomDataFieldValue(entityType,fieldName,fieldValue) {
  var qry = "SELECT e.[Name] FROM [Entities] e JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId] JOIN [EntityCustomFields] cf on cf.[EntityTypeId] = e.[EntityTypeId] CROSS APPLY OPENJSON(e.[CustomData]) WITH (jsonName varchar(50) '$.Name', jsonValue varchar(50) '$.Value') jsonData WHERE et.[Name] = '"+entityType+"' AND cf.[Name] = '"+fieldName+"' AND jsonName = 'FOB' AND jsonValue='"+fieldValue+"'";
  var userName = sql.Query(qry).First;
  return userName || 'NOTFOUND';
}

Sample Calls:

getEntityByCustomDataFieldValue('Employees','FOB','123456') // returns Entity Name 'Q'
getEntityByCustomDataFieldValue('Employees','FOB','135')    // returns 'NOTFOUND'

SQL Server Express 2016 / 2017
#38

Assuming you have an Entity Type named “Employees”, and it has a Custom Data Field with the name “FOB”, and the Entity Name matches a valid User Name …


Script:

:warning: Uses SQL syntax valid in SQL 2016 or later.

USER [user] (Script)

Script Name: USER
Script Handler: user

Script:

:warning: Uses SQL syntax valid in SQL 2016 or later.

function getEntityByCustomDataFieldValue(entityType,fieldName,fieldValue) {
  var qry = "SELECT e.[Name] FROM [Entities] e JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId] JOIN [EntityCustomFields] cf on cf.[EntityTypeId] = e.[EntityTypeId] CROSS APPLY OPENJSON(e.[CustomData]) WITH (jsonName varchar(50) '$.Name', jsonValue varchar(50) '$.Value') jsonData WHERE et.[Name] = '"+entityType+"' AND cf.[Name] = '"+fieldName+"' AND jsonName = 'FOB' AND jsonValue='"+fieldValue+"'";
  var userName = sql.Query(qry).First;
  return userName || 'NOTFOUND';
}


Actions

USER ExecAMC [Execute Automation Command] (Action)

Action Name: USER ExecAMC
Action Type: Execute Automation Command

Parameters:

Automation Command Name: [:AMCname]
Command Value: [:AMCvalue]
Background: [:runBG]
Delay: [:delay]

USER Login User [Login User] (Action)

Action Name: USER Login User
Action Type: Login User

Parameters:

User Name: [:userName]

Rules:

USER PIN Entered [Pin Code Entered] (Rule)

Rule Name: USER PIN Entered
Event Name: Pin Code Entered
Rule Tags:
Custom Constraint List (0):
Execute Rule if: Matches

Actions (1):

USER ExecAMC

Constraint: (none)

AMCname: USER Login by FOB
AMCvalue: {CALL:user.getEntityByCustomDataFieldValue('Employees','FOB','[:Pin]')}
runBG:
delay:

USER Login by FOB [Automation Command Executed] (Rule)

Rule Name: USER Login by FOB
Event Name: Automation Command Executed
Rule Tags:
Custom Constraint List (1):
Execute Rule if: Matches
Automation Command Name Equals USER Login by FOB

Actions (2):

USER Login User

Constraint: ‘[:CommandValue]’ != ‘NOTFOUND’

userName: [:CommandValue]
MSG TEST

Constraint: ‘[:CommandValue]’ == ‘NOTFOUND’

MessageToDisplay: PIN not Valid!


#39

Hey guys, Ive been trying to get this to work but I’ve run into a problem I can’t figure out. I thought it might be a permissions issue but i tried to run it on the server pc and it also didn’t work


#40

Its a little tricky when you only show a small bit of the code…
Are you running sql 2016 or higher? This script uses a JSON parser introduced in 2016 which wont work in previous versions.