Explanation of the FUNCTIONS_ELOGON.sjs file in the WSODBC example.
The FUNCTIONS_ELOGON.sjs file contains the the operational functions of the ODBC operation. THe connection parameters are also included as they are global as well. The included sections are as follows:
This function gets all users from the master and then imports them into the XLOGON table. The code is shown below.
function checkUser(){
db = that.ODBCCon;
if(!isBlank(USERNAME)){
//Check if the user exists
sqlQuery = "SELECT * from [dbo].["+objUserTable.name+"] WHERE "+objUserTable.cols.user+" = '"+ USERNAME +"'";
println("------------>sqlQuery:", sqlQuery);
record = db.select(sqlQuery);
println("------------> ",record);
if (record.length == 0){
return("E: Username does not exist");
} else if(record.length == 1){
//if(record[0][objUserTable.cols.client] != CLIENT)
// return("Client does not match");
//if(record[0][objUserTable.cols.user] != USERNAME)
// return("E: Username does not exist");
if(record[0][objUserTable.cols.password] != PASSWORD)
return("E: Password does not match");
if(record[0][objUserTable.cols.lock] == 'X')
return("E: User is locked");
if(record[0][objUserTable.cols.firstlogin] == 'X'){
changeLayout("NEW_PASSWORD");
return("Enter New password");
}
else{
if(record[0][objUserTable.cols.user]=='admin'){
changeLayout("ADMIN_PANEL");
} else if (record[0][objUserTable.cols.manager]=='X'){
changeLayout("MANAGER_PANEL");
WHSE = record[0][objUserTable.cols.warehouse];
} else {
changeLayout("PLATFORM");
PLANT = record[0][objUserTable.cols.plant];
WHSE = record[0][objUserTable.cols.warehouse];
}
}
PASSWORD=Crypto.Encrypt(PASSWORD);
println("---> " + USER + " authenticated.. Logging in");
SESSIONID = (new session()).id;
println(">",SESSIONID);
sqlQuery = "insert [dbo].["+objSessionTable.name+"] VALUES ('"+USERNAME+"','"+SESSIONID+"')";
println(sqlQuery);
db.exec(sqlQuery);
}
else{
return("E: User does not exist");
}
}
else
message("E: Fill in all required entry fields");
}
This function adds a user to a specified database. It wil also return error mesages if the requirement are not met, such as the password being an incorrect length. The code is as follows.
function addUser(){
if(isBlank(zuser) || isBlank(zpass) || isBlank(zpassconfirm)) {
println("---> Error :");
return("E: Fill in all required details");
}
if(isBlank(zclient) || isBlank(zplant) || isBlank(zwhse)){
println("---> Error :");
return("E: Fill in all required details");
}
println("--->",zpass);
println("--->",zpassconfirm);
if(zpass.toString().trim()!=zpassconfirm.toString().trim()){
zpass ='';
zpassconfirm = '';
return("E: Password does not match");
}
if(zpass.length < 6) return("E: Password is not long enough (Minimum length: 6 characters)");
db = that.ODBCCon;
record = db.select("select * from [dbo].["+objUserTable.name+"] WHERE "+objUserTable.cols.user+" = '"+zuser+"' AND "+objUserTable.cols.client+"='"+zclient+"'");
/*if (record.length == 0)
return("E: Username does not exists");
else */
if ( record.length > 1 )
return("E: Error with the database! Multiple user for the same client.");
sqlQuery = "insert [dbo].["+objUserTable.name+"] VALUES ('"+ system.newRecId('$S1')+ "','"+ zuser + "','"+zpass +"','"+ zclient+"','EN',CONVERT (datetime, GETDATE()),'','"+USERNAME+"','','','X','"+ zplant+"','"+ zwhse+"','')";
t = db.exec(sqlQuery);
println("------> t: ",t);
//set("V[z*]","");
changeLayout("ADMIN_PANEL");
return("S: User "+zuser+" successfully created in Client "+zclient);
}
This function will change a user's password. This incorporates the ability to encrypt the password, using our encrypt method. The code is shown below.
function changePassword(){
if(newPass.toString().trim()!=confirmPass.toString().trim()){
newPass ='';
confirmPass = '';
return("E: Password does not match");
}
newPass=newPass.toString().trim();
if(newPass.length < 6) return("E: Password is not long enough (Minimum length: 6 characters)");
db = that.ODBCCon;
var sqlQuery = "UPDATE [dbo].["+objUserTable.name+"] SET ["+objUserTable.cols.rec+"]='"+system.newRecId('$S1')+"',["+objUserTable.cols.password+"] = '"+Crypto.Encrypt(newPass)+"', ["+objUserTable.cols.firstlogin+"] = '', ["+objUserTable.cols.modifiedOn+"]=CONVERT (datetime, GETDATE()),["+objUserTable.cols.modifiedBy+"]='"+USERNAME+"' WHERE "+objUserTable.cols.user+" = '" + USERNAME+"'";
println(sqlQuery);
t = db.exec(sqlQuery);
println("------> t: ",t);
PASSWORD = Crypto.Encrypt(newPass);
set("V[z*]","");
changeLayout("PLATFORM");
return("S: Password changed successfully");
}
This function will search for a given user in the database and will return a message if the user cannot be found. The code is in the following example.
function searchUser(param){
if(isBlank(zclient.toString().trim()) || isBlank(zuser.toString().trim()))
return("E: Fill in all the required fields");
db = that.ODBCCon;
record = db.select("select * from [dbo].["+objUserTable.name+"] WHERE "+objUserTable.cols.user+" = '"+zuser+"' AND "+objUserTable.cols.client+" = '"+zclient+"'");
if(record.length == 1){
zplant = record[0][objUserTable.cols.plant];
zwhse = record[0][objUserTable.cols.warehouse];
zlocked = record[0][objUserTable.cols.lock];
changeLayout(param.screen);
}
else if (record.length == 0)
return("E: Username does not exists");
else if ( record.length > 1 )
return("E: Error with the database! Multiple user for the same client.");
else
return("E: Error code 0x01");
}
This function will modify an entry for a user, in case you wish to change information such as the plant where he or she is affiliated. The code is shown below.
function modifyUser(){
if(isBlank(zplant.toString().trim()) || isBlank(zwhse.toString().trim()))
return("E: Fill in all the required fields");
db = that.ODBCCon;
var sqlQuery = "update [dbo].["+objUserTable.name+"] SET ["+objUserTable.cols.plant+"] = '"+zplant+"', ["+objUserTable.cols.warehouse+"] ='"+zwhse+"' WHERE "+objUserTable.cols.user+" = '" + zuser+"' AND "+objUserTable.cols.client+" = '"+zclient+"'";
t = db.exec(sqlQuery);
println("------> t: ",t);
set("V[z*]","");
changeLayout("MODIFY_USER");
return("S: User data saved successfully");
}
This function will prevent a specified user from logging into the database. The code is as follows.
function lockUserToggle(param){
db = that.ODBCCon;
var sqlQuery = "update [dbo].["+objUserTable.name+"] SET ["+objUserTable.cols.lock+"] = '"+param.mode+"' WHERE "+objUserTable.cols.user+" = '" + zuser+"' AND "+objUserTable.cols.client+" = '"+zclient+"'";
t = db.exec(sqlQuery);
if(zlocked == "X"){
zlocked= " ";
return("S: User has been unlocked");
}
else{
zlocked= "X";
return("S: User has been locked");
}
}
This function resets a user's password if he or she forgets it. Error handling is also included in case the reset operation does not succeed. The code is shown below.
function resetPassword(){
if(newPass.toString().trim()!=confirmPass.toString().trim()){
newPass ='';
confirmPass = '';
return("E: Password does not match");
}
newPass=newPass.toString().trim();
if(newPass.length < 6) return("E: Password is not long enough (Minimum length: 6 characters)");
db = that.ODBCCon;
var sqlQuery = "update [dbo].["+objUserTable.name+"] SET ["+objUserTable.cols.password+"] = '"+newPass+"', ["+objUserTable.cols.firstlogin+"] = '', ["+objUserTable.cols.modifiedOn+"]=CONVERT (datetime, GETDATE()),["+objUserTable.cols.modifiedBy+"]='"+USERNAME+"' WHERE "+objUserTable.cols.user+" = '" + zuser+"'"
println(sqlQuery);
try{
t = db.exec(sqlQuery);
}
catch(err){
message(err.description);
}
println("------> t: ",t);
//set("V[z*]","");
set("V[newPass]","");
set('V[confirmPass]','');
changeLayout("MODIFY_USER_DETAIL");
return("S: Password changed successfully");
}