private int CheckStaffNameExist(string displayName, int staffNameId, int staffId)
{
RunStoredProcedure rsp = new RunStoredProcedure();
// check if staff name exists in db
int exist = rsp.StoredProcedureReturnInt("Proc_CheckStaffNameExist", "staffName", displayName, "count");
// if staff name exists, activate the correct staff name from db
if (exist > 0)
{
// activate staff name on logged in user
rsp.StoredProcedureUpdateBool("Proc_UpdateStaffName_Varchar", "active", true, "staffName", displayName);
// get staff name id
staffNameId = rsp.StoredProcedureReturnInt("Proc_GetStaffNameId_StaffName", "staffName", displayName, "staffNameId");
}
// staff name does not exists in db, create a new staff name id and make it active
else
{
// add a new staff name and return staff name id
staffNameId = rsp.StoredProcedureInsertRow("Proc_AddStaffName", "staffName", displayName, "staffNameId");
}
// update staff name id in staff table
rsp.StoredProcedureUpdateInt("Proc_UpdateStaffNameId", "staffNameId", staffNameId, "staffId", staffId);
return(staffNameId);
}
public string GetGroups(string username)
{
DirectorySearcher search = new DirectorySearcher(path);
search.Filter = "(cn=" + filterAttribute + ")";
search.PropertiesToLoad.Add("memberOf");
StringBuilder groupNames = new StringBuilder();
using (HostingEnvironment.Impersonate())
{
try
{
SearchResult result = search.FindOne();
int propertyCount = result.Properties["memberOf"].Count, equalIndex, commaIndex;
string dn, grp;
for (int propertyCounter = 0; propertyCounter < propertyCount; propertyCounter++)
{
dn = (string)result.Properties["memberOf"][propertyCounter];
equalIndex = dn.IndexOf("=", 1);
commaIndex = dn.IndexOf(",", 1);
if (-1 == equalIndex)
{
return(null);
}
grp = dn.Substring((equalIndex + 1), (commaIndex - equalIndex) - 1); // Retrieves the Group Name in Active Directory
if (grp.Contains("MRReports") || grp.Contains("CUReports")) // if group name contains the following conditions, add it to group names and append a back slash
{
groupNames.Append(grp);
groupNames.Append("|");
}
}
groupNames.Append(filterAttribute); // append the user's display name after the list of groups
}
catch (Exception ex)
{
RunStoredProcedure rsp = new RunStoredProcedure();
string groups = rsp.GetGroupNames(username);
if (!string.IsNullOrEmpty(groups))
{
// link the stored procedure created in db
string name = rsp.GetName(username);
groupNames.Append(groups);
groupNames.Append("|");
groupNames.Append(name);
}
else
{
throw new Exception("Error obtaining group names. " + ex.Message);
}
}
return(groupNames.ToString());
}
}
// upon login, check staff details and update necessary details
private void Staff(string displayName, string group)
{
// check whether the staff logged in has the same credentials compared from the previous credentials logged in - if not, update db
RunStoredProcedure rsp = new RunStoredProcedure();
var staffId = 0;
var staffNameId = 0;
try
{
staffId = rsp.StoredProcedureReturnInt("Proc_GetStaffId", "username", txtUsername.Text, "staffId");
}
catch
{
// set staffId to zero to add the staff details to db
staffId = 0;
}
// if staff exist in the db - next step is to compare from previous login and update staff details
if (staffId != 0)
{
staffNameId = rsp.StoredProcedureReturnInt("Proc_GetStaffNameId_Staff", "username", txtUsername.Text, "staffNameId");
string staffName = rsp.StoredProcedureReturnString("Proc_GetStaffName", "staffId", staffId, "staffName");
// staff name is different from ad compared to db
if (!displayName.Equals(staffName))
{
// disable active staff name on logged in user
rsp.StoredProcedureUpdateBool("Proc_UpdateStaffName_Int", "active", false, "staffNameId", staffNameId);
// add or update staff name from db
staffNameId = CheckStaffNameExist(displayName, staffNameId, staffId);
}
}
// staff does not exist in db - create a new staff id
else
{
// add or update staff name from db
staffNameId = CheckStaffNameExist(displayName, staffNameId, staffId);
// add a new staff name and return staff name id
staffId = rsp.StoredProcedureInsertRow("Proc_AddStaff", "staffNameId", staffNameId, "username", txtUsername.Text, "staffId");
}
UserCredentials.StaffId = staffId;
UserCredentials.StaffNameId = staffNameId;
// upon login, update user's role and group access
UpdateStaffDetails(group, staffId);
}
protected void btnPrint_Click(object sender, EventArgs e)
{
// log the print activity
RunStoredProcedure rsp = new RunStoredProcedure();
try
{
rsp.Log(3, Int32.Parse(Session["LinkRId"].ToString()));
}
catch { }
ScriptManager.RegisterStartupScript(this, typeof(string), "OPEN_WINDOW", "var Mleft = (screen.width/2)-(760/2);var Mtop = (screen.height/2)-(700/2);window.open( '/Web_Forms/PrintReport.aspx?LinkReport=1&ReportName=" + Request.QueryString["ReportName"].ToString() + "&Version=" + Request.QueryString["Version"].ToString() + "', null, 'height=2,width=2,status=yes,toolbar=no,scrollbars=yes,menubar=no,location=no,top=\'+Mtop+\', left=\'+Mleft+\'' );", true);
if (!string.IsNullOrEmpty(Session["LinkAuditVersion"].ToString()))
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "closeWindow", "closeWindow();", true);
}
}
protected void btnUpdatePassword_Click(object sender, EventArgs e)
{
// once the new password is submitted, redirect them to the default url
// update the password for this user
RunStoredProcedure rsp = new RunStoredProcedure();
// join these two methods together
// encrypt password
string encryptedPassword = rsp.EncryptPassword(txtNewPassword.Text);
// update password stored in the database
rsp.StoredProcedureUpdateString("Proc_UpdatePassword", "password", encryptedPassword, "username", txtUsername.Text);
//ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Password updated');location.href='/Web_Forms/Default.aspx';", true); // show alert textbox first then redirect to default url
AlertMessage alert = new AlertMessage();
alert.DisplayMessage("Password updated!");
// hide the current objetcs displayed and display a textbox to write their new password
divLogin.Visible = true;
divNewPassword.Visible = false;
txtUsername.Focus();
}
private bool CheckIfPasswordIsGiven()
{
bool passwordGiven = true;
RunStoredProcedure rsp = new RunStoredProcedure();
string password = rsp.StoredProcedureReturnString("Proc_GetPassword", "username", txtUsername.Text, "password");
if (string.Equals(password, txtPassword.Text))
{
// hide the current objetcs displayed and display a textbox to write their new password
divLogin.Visible = false;
divNewPassword.Visible = true;
txtNewPassword.Focus();
passwordGiven = true;
}
else
{
passwordGiven = false;
}
return(passwordGiven);
}
// upon login, update user's role and group access
private void UpdateStaffDetails(string group, int staffId)
{
// set staff's role
var role = "";
if (group.Contains("MRBankingSeniorManager"))
{
role = "MR Senior Manager";
}
else if (group.Contains("MRBankingDutyManager"))
{
role = "MR Duty Manager";
}
else if (group.Contains("CUBankingDutyManager"))
{
role = "CU Duty Manager";
}
else if (group.Contains("MRBankingSupervisor"))
{
role = "MR Supervisor";
}
else if (group.Contains("MRBankingClearance"))
{
role = "MR Clearance";
}
else if (group.Contains("CUBankingClearance"))
{
role = "CU Clearance";
}
// update staff's role and group
RunStoredProcedure rsp = new RunStoredProcedure();
rsp.StoredProcedureUpdateString("Proc_UpdateRole", "role", role, "staffId", staffId);
rsp.StoredProcedureUpdateString("Proc_UpdateGroup", "group", group, "staffId", staffId);
UserCredentials.Role = role;
}
protected void btnLogin_Click(object sender, EventArgs e)
{
string group, displayName;
string[] groupArray;
StringBuilder groupsList = new StringBuilder();
AuthenticateUser authUser = new AuthenticateUser("LDAP://MRSLGROUP");
try
{
using (HostingEnvironment.Impersonate())
{
if (true == authUser.IsAuthenticated("MRSLGROUP", txtUsername.Text, txtPassword.Text)) // check if login details are valid - checking from Active Directory User Account details
{
group = authUser.GetGroups(txtUsername.Text); // retrieve user groups + display name
groupArray = group.Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
Session["Username"] = txtUsername.Text;
UserCredentials.Username = txtUsername.Text; // record username
displayName = groupArray[groupArray.Length - 1];
Session["DisplayName"] = displayName;
UserCredentials.DisplayName = displayName;
groupArray = groupArray.Take(groupArray.Count() - 1).ToArray(); // delete the last array item (display name), to keep this array variable set to usr groups only
for (int i = 0; i < groupArray.Length; i++)
{
groupsList.Append(groupArray[i]); // store group name
groupsList.Append("|"); // add a back slash delimeter
}
group = groupsList.ToString(); // set user groups
UserCredentials.Groups = group;
// upon login, check staff details and update necessary details
Staff(displayName, group);
RunStoredProcedure rsp = new RunStoredProcedure();
// encrypt password
string encryptedPassword = rsp.EncryptPassword(txtPassword.Text);
// update password stored in the database
rsp.StoredProcedureUpdateString("Proc_UpdatePassword", "password", encryptedPassword, "username", txtUsername.Text);
bool isCookiePersistent = false; // Create the ticket, and add the groups.
// set expiration of the authentication ticket - current set: 480 minutes / 8 hours
FormsAuthenticationTicket authTicket = new FormsAuthenticationTicket(1, txtUsername.Text, DateTime.Now, DateTime.Now.AddMinutes(480), isCookiePersistent, group);
string encryptedTicket = FormsAuthentication.Encrypt(authTicket); //Encrypt the ticket.
HttpCookie authCookie = new HttpCookie(FormsAuthentication.FormsCookieName, encryptedTicket); //Create a cookie, and then add the encrypted ticket to the cookie as data.
if (true == isCookiePersistent)
{
authCookie.Expires = authTicket.Expiration;
}
Response.Cookies.Add(authCookie); //Add the cookie to the outgoing cookies collection.
Response.Redirect(FormsAuthentication.GetRedirectUrl(txtUsername.Text, false), false); //You can redirect now.
}
else
{
bool passwordGiven = CheckIfPasswordIsGiven();
if (!passwordGiven)
{
errorLabel.Text = "Invalid details. Please check your username and password.";
}
}
}
}
catch (Exception ex)
{
bool passwordGiven = CheckIfPasswordIsGiven();
if (!passwordGiven)
{
errorLabel.Text = "Error logging in user. " + ex.Message;
}
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
SearchReport.CreateReportReset(); // takes off the selected report in ddlCreateReport
// get the last Report ID
string query = "SELECT MAX(ReportId) AS ReportId FROM dbo.Report_MerrylandsRSLReception";
int lastRId, result, returnFlag = 2;
DateTime temp, date = DateTime.Parse(DateTime.Now.ToShortDateString());
Report.ErrorMessage = "";
con.Open();
SqlCommand getRId = new SqlCommand(query, con);
try
{
lastRId = (int)getRId.ExecuteScalar();
// add plus one to the current report id to be used in this report
lastRId += 1;
}
catch
{
lastRId = 6000001;
}
con.Close();
Report.LastReportId = lastRId.ToString();
if (txtDatePicker.Text == "")
{
Report.ErrorMessage = Report.ErrorMessage + "\\n* Shift Date shouldn't be empty.";
txtDatePicker.Focus();
returnFlag = 1;
}
else if (!DateTime.TryParse(txtDatePicker.Text, out temp))
{
Report.ErrorMessage = Report.ErrorMessage + "\\n* Shifts Date entry is not in date format please select an appropriate date.";
txtDatePicker.Focus();
returnFlag = 1;
}
else if (DateTime.TryParse(txtDatePicker.Text, out temp))
{
// compare selected date to current date
result = DateTime.Compare(DateTime.Parse(DateTime.Parse(txtDatePicker.Text).ToShortDateString()), date);
if (result > 0)
{
Report.ErrorMessage = Report.ErrorMessage + "\\n* DATE MUST BE BEFORE CURRENT DATE.";
txtDatePicker.Focus();
returnFlag = 1;
}
}
//if (txtSpecialComments.Text == "")
//{
// Report.ErrorMessage = Report.ErrorMessage + "\\n* COVID-19 section shouldn't be empty.";
// txtSpecialComments.Focus();
// returnFlag = 1;
//}
if (returnFlag == 1)
{
ScriptManager.RegisterStartupScript(this, GetType(), "ServerControlScript", "alert(\"" + Report.ErrorMessage + "\");", true);
return;
}
// change the format of the shift date to timestamp format
DateTime shift_date = DateTime.Parse(txtDatePicker.Text);
string shift_tDate = shift_date.ToString("yyyyMMdd");
// separate the shift date day of week value
string shift_DOW = shift_date.DayOfWeek.ToString();
// change the format of the entry date to timestamp format
DateTime entry_date = DateTime.Now;
// pop a message if shift is unchanged
if (ddlShift.SelectedItem.Value == "-1")
{
showAlert("Please select Shift.");
ddlShift.Focus();
return;
}
// get staff's id
string cmdText = "SELECT StaffId FROM Staff WHERE Username = '******'",
variable = "getStaff";
readFiles(cmdText, variable);
// insert data to table
using (DataClassesDataContext dc = new DataClassesDataContext())
{
Report_MerrylandsRSLReception dm = new Report_MerrylandsRSLReception();
dm.ReportId = Int32.Parse(Report.LastReportId);
dm.RCatId = 6; // MR Reception Category
dm.StaffId = Int32.Parse(Session["currentStaffId"].ToString());
dm.StaffName = UserCredentials.DisplayName;
dm.ShiftId = Int32.Parse(ddlShift.SelectedItem.Value);
dm.ShiftDate = shift_date.Date;
dm.ShiftDOW = shift_DOW;
dm.EntryDate = entry_date;
dm.Report_Table = "Report_MerrylandsRSLReception";
dm.AuditVersion = 1;
dm.ReportStat = "Awaiting Completion";
dm.Report_Version = 2; // current version
dm.ReadByList = "," + UserCredentials.StaffId + ",";
dm.SignInSlip = txtSignInSlip.Text.Replace("\n", "<br />").Replace("'", "^");
dm.Refusals = txtRefusals.Text.Replace("\n", "<br />").Replace("'", "^");
dm.EventsField = txtEventsField.Text.Replace("\n", "<br />").Replace("'", "^");
dm.GeneralComments = txtGeneralComms.Text.Replace("\n", "<br />").Replace("'", "^");
dm.SpecialComments = txtSpecialComments.Text.Replace("\n", "<br />").Replace("'", "^");
dc.Report_MerrylandsRSLReceptions.InsertOnSubmit(dm);
dc.SubmitChanges();
}
//log the create activity
RunStoredProcedure rsp = new RunStoredProcedure();
try
{
rsp.Log(4, Int32.Parse(Report.LastReportId));
}
catch { }
//showAlert("Report Submitted.");
//Response.Redirect("Default.aspx", false);
ScriptManager.RegisterStartupScript(this, this.GetType(), "redirect",
"alert('Report Submitted.'); window.location='" +
Request.ApplicationPath + "Default.aspx';", true);
SearchReport.SetAccordion = "1";
SearchReport.RunOnStart = true;
SearchReport.FromCreateReport = true;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
SearchReport.CreateReportReset(); // takes off the selected report in ddlCreateReport
// get the last Report ID
string query = "SELECT MAX(ReportId) AS ReportId FROM dbo.Report_MerrylandsRSLCaretaker";
int lastRId, result, returnFlag = 2;
DateTime temp, date = DateTime.Parse(DateTime.Now.ToShortDateString());
Report.ErrorMessage = "";
con.Open();
SqlCommand getRId = new SqlCommand(query, con);
try
{
lastRId = (int)getRId.ExecuteScalar();
// add plus one to the current report id to be used in this report
lastRId += 1;
}
catch
{
lastRId = 13000001;
}
con.Close();
Report.LastReportId = lastRId.ToString();
if (txtDatePicker.Text == "")
{
Report.ErrorMessage = Report.ErrorMessage + "\\n* Shift Date shouldn't be empty.";
txtDatePicker.Focus();
returnFlag = 1;
}
else if (!DateTime.TryParse(txtDatePicker.Text, out temp))
{
Report.ErrorMessage = Report.ErrorMessage + "\\n* Shifts Date entry is not in date format please select an appropriate date.";
txtDatePicker.Focus();
returnFlag = 1;
}
else if (DateTime.TryParse(txtDatePicker.Text, out temp))
{
// compare selected date to current date
result = DateTime.Compare(DateTime.Parse(DateTime.Parse(txtDatePicker.Text).ToShortDateString()), date);
if (result > 0)
{
Report.ErrorMessage = Report.ErrorMessage + "\\n* DATE MUST BE BEFORE CURRENT DATE.";
txtDatePicker.Focus();
returnFlag = 1;
}
}
if (returnFlag == 1)
{
ScriptManager.RegisterStartupScript(this, GetType(), "ServerControlScript", "alert(\"" + Report.ErrorMessage + "\");", true);
return;
}
// change the format of the shift date to timestamp format
DateTime shift_date = DateTime.Parse(txtDatePicker.Text);
string shift_tDate = shift_date.ToString("yyyyMMdd");
// separate the shift date day of week value
string shift_DOW = shift_date.DayOfWeek.ToString();
// change the format of the entry date to timestamp format
DateTime entry_date = DateTime.Now;
// pop a message if shift is unchanged
//if (ddlShift.SelectedItem.Value == "-1")
//{
// showAlert("Please select Shift.");
// ddlShift.Focus();
// return;
//}
// get staff's id
string cmdText = "SELECT StaffId FROM Staff WHERE Username = '******'",
variable = "getStaff";
readFiles(cmdText, variable);
// store in a string all the selected item in the checkboxlist
// Create the list to store.
List <String> YrStrList1 = new List <string>();
// Loop through each item.
foreach (ListItem item in List_Location.Items)
{
if (item.Selected)
{
// If the item is selected, add the value to the list.
YrStrList1.Add(item.Value);
}
}
// Join the string together using the ; delimiter.
string Location = String.Join(",", YrStrList1.ToArray());
if (!Location.Equals(""))
{
Location += ",";
}
// insert data to table
using (DataClassesDataContext dc = new DataClassesDataContext())
{
Report_MerrylandsRSLCaretaker dm = new Report_MerrylandsRSLCaretaker();
dm.ReportId = Int32.Parse(Report.LastReportId);
dm.RCatId = 13; // Customer Relations Officer Category
dm.StaffId = Int32.Parse(Session["currentStaffId"].ToString());
//dm.ShiftId = Int32.Parse(ddlShift.SelectedItem.Value);
dm.StaffName = UserCredentials.DisplayName;
dm.ShiftDate = shift_date.Date;
dm.ShiftDOW = shift_DOW;
dm.EntryDate = entry_date;
dm.Report_Table = "Report_MerrylandsRSLCaretaker";
dm.AuditVersion = 1;
dm.ReportStat = "Awaiting Completion";
dm.Report_Version = 1; // current version
dm.ReadByList = "," + UserCredentials.StaffId + ",";
dm.Spare1 = Location;
dm.Occupancy = txtOccupancy.Text.Replace("\n", "<br />").Replace("'", "^");
dm.Maintenance = txtMaintenance.Text.Replace("\n", "<br />").Replace("'", "^");
dm.GeneralComments = txtGeneralComments.Text.Replace("\n", "<br />").Replace("'", "^");
dc.Report_MerrylandsRSLCaretakers.InsertOnSubmit(dm);
dc.SubmitChanges();
}
//log the create activity
RunStoredProcedure rsp = new RunStoredProcedure();
try
{
rsp.Log(4, Int32.Parse(Report.LastReportId));
}
catch { }
//showAlert("Report Submitted.");
//Response.Redirect("Default.aspx", false);
ScriptManager.RegisterStartupScript(this, this.GetType(), "redirect",
"alert('Report Submitted.'); window.location='" +
Request.ApplicationPath + "Default.aspx';", true);
SearchReport.SetAccordion = "1";
SearchReport.RunOnStart = true;
SearchReport.FromCreateReport = true;
}
public bool IsAuthenticated(string domain, string username, string password)
{
string domainAndUsername = domain + @"\" + username;
DirectoryEntry entry = new DirectoryEntry(path, domainAndUsername, password);
using (HostingEnvironment.Impersonate()) // Provides application-management functions and application services to a managed application within its application domain. Impersonates the user represented by the application identity.
{
try
{
DirectorySearcher search = new DirectorySearcher(entry);
search.Filter = "(SAMAccountName=" + username + ")";
search.PropertiesToLoad.Add("cn");
// we were having issues with search.FindAll() method listed below and it takes 15 seconds to load
// below is the error message that is displayed
// ExtendedErrorMessage = "8009030C: LdapErr: DSID-0C0904DC, comment: AcceptSecurityContext error, data 52e, v1db1"
// possible solution reference : https://social.technet.microsoft.com/Forums/windowsserver/en-US/2786da89-3dc7-43d9-8a75-3db54825ff36/solved-ldap-authentication-error-code-49-80090308-comment-acceptsecuritycontext-error-data?forum=winserverDS
// solution implemented: create an exception for local users not found in active directory
// reference: https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/exceptions/creating-and-throwing-exceptions
if (username.ToLower().Equals("malb"))
{
throw new SystemException("user is not found in active directory");
}
foreach (SearchResult result in search.FindAll())
{
if (null != result)
{
path = result.Path;
filterAttribute = (string)result.Properties["cn"][0]; // Picks up the display name from Active Directory
break;
}
}
}
catch (Exception ex)
{
RunStoredProcedure rsp = new RunStoredProcedure();
// check if username exists
bool userExist = rsp.UserExist(username);
if (userExist) // write an if statement to check whether the username exist in the database
{
string userPassword = rsp.GetPassword(username);
string decryptedPassword = rsp.DecryptPassword(userPassword);
if (string.Equals(decryptedPassword, password)) // if it is, check if there is any password stored and match if exist return true
{
return(true);
}
else
{
return(false);
}
}
else // else throw the exception
{
throw new Exception("Error authenticating user. " + ex.Message);
}
}
return(true);
}
}
public string GetGroups(string username)
{
DirectorySearcher search = new DirectorySearcher(path);
search.Filter = "(cn=" + filterAttribute + ")";
search.PropertiesToLoad.Add("memberOf");
StringBuilder groupNames = new StringBuilder();
using (HostingEnvironment.Impersonate())
{
try
{
SearchResult result = search.FindOne();
int propertyCount = result.Properties["memberOf"].Count, equalIndex, commaIndex;
string dn, grp;
for (int propertyCounter = 0; propertyCounter < propertyCount; propertyCounter++)
{
dn = (string)result.Properties["memberOf"][propertyCounter];
equalIndex = dn.IndexOf("=", 1);
commaIndex = dn.IndexOf(",", 1);
if (-1 == equalIndex)
{
return(null);
}
grp = dn.Substring((equalIndex + 1), (commaIndex - equalIndex) - 1); // Retrieves the Group Name in Active Directory
if (grp.Contains("MRBanking") || grp.Contains("CUBanking")) // if group name contains the following conditions, add it to group names and append a back slash
{
groupNames.Append(grp);
groupNames.Append("|");
}
}
groupNames.Append(filterAttribute); // append the user's display name after the list of groups
}
catch (Exception ex)
{
// exception existed - user must've been using the local db
// get user group access from local db
RunStoredProcedure rsp = new RunStoredProcedure();
string group = rsp.StoredProcedureReturnString("Proc_GetGroup", "username", username, "group");
if (!string.IsNullOrEmpty(group))
{
// get staff id and staff name respectively
int staffId = rsp.StoredProcedureReturnInt("Proc_GetStaffId", "username", username, "staffId");
string staffName = rsp.StoredProcedureReturnString("Proc_GetStaffName", "staffId", staffId, "staffName");
// add group access value with backslash delimiter - last value is staff name
groupNames.Append(group);
groupNames.Append("|");
groupNames.Append(staffName);
}
else
{
throw new Exception("Error obtaining group names. " + ex.Message);
}
}
return(groupNames.ToString());
}
}
