I am more that just a survey : The Login System ( 3 / 3 )

In this final article about the login system we will see how we can validate the user login credentials via a system we have created. We will create a user survey and get our menu system to validate against that. I will show you how you can use ODBC to connect the two surveys together and ensure that only the correct users are allowed to login
So first off , create a new MDD call it Users and make it have at least the following metadata questions.

Metadata(en-AU, Question, label)

Warning "You should not be running this routing" info;

    tLogin "User Name :"
        style(
            Width = "15em",
            Control(
                Type = "SingleLineEdit"
            )
        )
        labelstyle(
            Align = "Right",
            Width = "12em"
        )
    text [1..50];

    tPassword "Password :"
        style(
            Width = "15em",
            Control(
                Type = "Password"
            )
        )
        labelstyle(
            Align = "Right",
            Width = "12em"
        )
    text [1..50];

    Firstname "Firstname :"
        style(
            Width = "15em",
            Control(
                Type = "SingleLineEdit"
            )
        )
        labelstyle(
            Align = "Right",
            Width = "12em"
        )
    text [1..30];

    Surname "Surname :"
        style(
            Width = "15em",
            Control(
                Type = "SingleLineEdit"
            )
        )
        labelstyle(
            Align = "Right",
            Width = "12em"
        )
    text [1..30];

    Email "Email :"
        style(
            Width = "15em",
            Control(
                Type = "SingleLineEdit"
            )
        )
        labelstyle(
            Align = "Right",
            Width = "12em"
        )
    text [1..100];

    CanLogin "Can This User Login"
        labelstyle(
            Align = "Right",
            Width = "12em"
        )
    boolean;


pUserDetails "" page( tLogin, tPassword , Firstname,Surname , CanLogin );

End Metadata

Next we need some routing , but we are not going to use the basic web routing. The reason for this is , everyone knows about the web routing , but if you create a routing with a different name only you know about it. So it adds another level of security. We won’t discard the web routing , we will add a message to it , so this is what our web routing look likes.

Routing(Web)

Warning.ask()

End Routing

the routing we are going to us is going to be called UserDetails and has the following info in it.

Routing(UserDetails)
IOM.OffPathDataMode = OffPathDataModes.dmKeep
IOM.Texts.InterviewStopped = "Your details have now been updated."
IOM.Texts.EndofInterview = "Your details have now been updated."

pUserDetails.Ask()

End Routing

The content is really simple , but there is a key row that we must have , and that is this code,

IOM.OffPathDataMode = OffPathDataModes.dmKeep

This code makes sure we keep all our answers , regardless of how we navigate the survey. If we were in a normal survey and we had gone done some logic to a specific question, recorded a response , then decided to navigate back through the survey and chooses some different answers that took us to a different question , then the info collected at the first question mentioned would be lost if we did not have keep our OffPathData.
Next we need to create some sample. Our user survey will be started and stopped so the only way to do this is to create a simple sample file. Make a text file like this one , upload it into participants and call you sample “Users_Sample” and then activate with the basic sample script.

Id;Queue;Active;Serial;Test
1;FRESH;0;0;0

When activated run the survey with the following URL and enter the details you want to use. Your server name in the URL will of course be different.

http://localhost/mrIWeb/mrIWeb.dll?I.Project=Users&i.routingcontext= UserDetails&id=1
When you enter the information , make sure you place a check in the CanLogin question. When this is done we can move back to the Menu MDD file. Add a new routing to our menu mdd file , lets call it UserLogin and add the following code.

Dim dpmAgent,bIsLoggedIn,gSite,sMessage,sFeatures

Set dpmAgent = CreateObject("SPSSMR.DPM.Security.Login.Agent2")
	dpmAgent.ConnectToDPMServer("localhost")
	bIsLoggedIn = dpmAgent.Login("MYUSER","MYPASSWORD",Null)
	gSite = dpmAgent.site.Name
	dpmAgent.Logout()
	
Set dpmAgent = null

So what does the above code do , well, in short it works out what cluster the files have been uploaded to. This then allows us to work out what the server information is. This is really useful code if you work in multiple environments , it means that we don’t have to keep changing the settings depending on what environment we have upload to. This is all based on the site name , which you can find in the top right hand corner of your browser when you are in Administration Server.

Next type the folowing,

Dim gFMROOT,gSQLServer,gStartURL,gParticipants
	
Select Case gSite

	Case = "Site"
		' ************** LAPTOP SETTINGS
		
		gFMROOT = "localhost\spssmr_fmroot"
		gSQLServer = "localhost"
		gStartURL = "http://localhost/mrIWeb/mrIWeb.dll?I.Project="
		gParticipants = "Participants"
	
	Case = "STAGING"
		' ************** STAGING SETTINGS
		
		gFMROOT = "STAGING\spssmr_fmroot"
		gSQLServer = "STAGING"
		gStartURL = "https://STAGING/mrIWeb/mrIWeb.dll?I.Project="
		gParticipants = "Participants"
		
	Case = "PRODUCTION"
		' ************** Production SETTINGS
		
		gFMROOT = "PRODUCTION\spssmr_fmroot"
		gSQLServer = "PRODUCTION"
		gStartURL = "https://PRODUCTION/mrIWeb/mrIWeb.dll?I.Project="
		gParticipants = "Participants"
End Select	

The code above just takes the Site name and based on that sets some global variables. You will need to change these to match your settings.

' ************** General SETTINGS

CONST gUserDB = "SDUsers"
CONST gUserDBSample = "SDUsers_Sample"
CONST gNavigationDB = "SDMenu"

The above lines set some constants , which is nothing special , but i would like to point out that i am using a predefined naming convention for my surveys and sample databases. All sample tables have the name of the project with the words “_Sample” after them. That way , if needed i can programmatically create a sample table name from the survey name.

' ************** Login Section

sMessage = ""

TryAgain:

	sFeatures = "-1"
	Message.Label.Inserts["MESSAGE"] = sMessage

	If ( iom.Info.IsDebug ) Then
	    pLogin.tLogin = "USERNAME"
	    pLogin.tPassword = "PASSWORD"
	End if
	    
	pLogin.LayoutTemplate = "Login.html"
	pLogin.Ask()

Next we have the beginnings of the login section. You will notice this is very similar to the code we have written before. I have added an “IOM.info.IsDebug” section that makes running these scripts easier , because you don’t have to keep entering you username and password whilst you are testing your code.

	If ( IOM.Info.IsDebug ) Then
		sFeatures = ""
	Else
		sFeatures = CheckLogin(pLogin.tLogin, pLogin.tPassword, gFMROOT, gSQLServer, gUserDB)
	End If
	
If ( sFeatures = "-1" ) Then 
	sMessage = "!!! Sorry, you dont have access !!!"
	Goto TryAgain
End If

You will notice in this bit of code i have added another “IOM.info.IsDebug” section. Again this is for debugging purposes. We dont have to worry about this too much at this stage as we will expand on the concept, but you should be able to see that our “CheckLogin” function , if it returns -1 then the user will not be able to continue.

' **************
' ************** Functions Section
' **************

Function CheckLogin(sLogin,sPassword,gFMROOT,gSQLServer,gUserDB)

	Dim oConnection , oRecordset, sFeatures
		
	sFeatures = "-1"
	
	Set oConnection = CreateObject("ADODB.Connection")
	
	oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;Location='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + gUserDB + ";Data Source=" + gSQLServer + "';Initial Catalog=\\" + gFMROOT + "\Master\" + gUserDB + "\" + gUserDB + ".mdd;MR Init Project=" + gUserDB 
	
	oConnection.Open()
	
	If ( oConnection.State = 1 ) Then
	
		Set oRecordset = CreateObject("ADODB.Recordset")
		
			oRecordset.Open("SELECT Respondent.serial,tPassword, CanLogin From Vdata where ucase(tLogin) = '" + ucase(sLogin) + "'",oConnection,3,1)
			
			On Error Goto Fail_Login
			
				sFeatures = cText(oRecordset["Respondent.serial"].Value) + "#" 
			
				If ( oRecordset["tPassword"].Value <> sPassword ) Then 
					sFeatures = "-1"
				End if
				
				
				If ( oRecordset["CanLogin"].Value = 0  ) Then 
					sFeatures = "-1"
				End if
			
			On Error Resume Next
			
			oRecordset.Close()

Fail_Login:

		Set oRecordset = Null
	
	End If
	oConnection.Close()
	
	Set oConnection = Null
	
	CheckLogin = sFeatures
	
End Function

And then finally we have our Function section which in this case just has the CheckLogin function in it. Looking at the code you will see that the CheckLogin uses ODBC calls to the VDATA table of the user survey that we uploaded , compares the login and password information and if all is well returns the respondent serial number of the record, if the details do not match then we return a -1 which will prevent the login. So whith all the code in place , what have we designed? At this stage ,not much realy, we have a login screen that works of a database and depending on the settings in the database allows or disallows the user to loging. Our screen at this stage looks like this.

In future articles we will expand in this function as it will be the place where we take user information from the user survey which will help us drive the menu survey, but for now , get the code working and then in the next article we will start to design our buttons for the menu system.