public UserClass Login(string txtUser, string txtPassword) { //26/05/2011 we now encrypt passwords txtPassword = wwi_security.DecryptString(txtPassword, "publiship"); //initialise UserClass UserLogin = new UserClass(); UserLogin.ID = Guid.Empty; int _rowcount = 0; IDataReader _rd; SubSonic.SqlQuery query = new SubSonic.SqlQuery(); try { string[] _cols = { "ContactTable.ContactID", "ContactTable.ContactName", "ContactTable.Name", "ContactTable.ContactInitials", "ContactTable.CompanyID", "ContactTable.DefaultView", "ContactTable.IsEditor", "ContactTable.EMail", "ContactTable.ControllingOfficeID", "NameAndAddressBook.CompanyName", "NameAndAddressBook.TelNo", "NameAndAddressBook.Pricer_Group" }; //query = DB.Select(_cols).From("ContactTable").LeftOuterJoin("NameAndAddressBook", "CompanyID", "ContactTable", "CompanyID").Where("ContactName").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true); //have check ContactName AND Name as usage is inconsistent in database query = DB.Select(_cols).From("ContactTable").LeftOuterJoin("NameAndAddressBook", "CompanyID", "ContactTable", "CompanyID"). Where("ContactName").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true). Or("Name").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true); _rd = query.ExecuteReader(); while (_rd.Read()) { UserLogin.ID = Guid.NewGuid(); UserLogin.UserId = (Int32)_rd["ContactID"]; UserLogin.UserName = Convert.ToString(_rd["ContactName"]); ; //Convert.ToString(_rd["ContactName"]); UserLogin.UserInitials = _rd["ContactInitials"] != null ? Convert.ToString(_rd["ContactInitials"]) : ""; UserLogin.OfficeId = -1; //external client UserLogin.CompanyId = (Int32)_rd["CompanyID"]; UserLogin.DefaultView = (Int32)_rd["DefaultView"]; UserLogin.IsEditor = (byte)_rd["IsEditor"]; UserLogin.mailTo = _rd["EMail"] != null ? Convert.ToString(_rd["EMail"]) : ""; UserLogin.OfficeName = _rd["CompanyName"] != null ? Convert.ToString(_rd["CompanyName"]) : ""; UserLogin.telNo = _rd["TelNo"] != null ? Convert.ToString(_rd["TelNo"]) : ""; UserLogin.controlOfficeId = (Int32)_rd["ControllingOfficeID"]; //this should be the new controller office link id UserLogin.companyGroup = _rd["Pricer_Group"] != null ? wwi_func.vint(_rd["Pricer_Group"].ToString()) : 0; UserLogin.loginValue = 1; //successful login _rowcount++; } //Query _qryb = new Query(Tables.ContactTable).WHERE("ContactName", Comparison.Equals, txtUser).AND("Password", Comparison.Equals, txtPassword).AND("Live", Comparison.Equals, true); //ContactTableCollection _contact = new ContactTableCollection(); //_contact.LoadAndCloseReader(_qryb.ExecuteReader()); // //if (_contact.Count != 0) // { // UserLogin.ID = Guid.NewGuid(); // UserLogin.UserId = (Int32)_contact[0].ContactID; ; // UserLogin.UserName = (String)_contact[0].ContactName; // UserLogin.UserInitials = (String)_contact[0].ContactInitials; // UserLogin.OfficeId = -1; //external client // UserLogin.CompanyId = (Int32)_contact[0].CompanyID; // UserLogin.DefaultView = (Int32)_contact[0].DefaultView; // UserLogin.IsEditor = (Int32)_contact[0].IsEditor; //} if (_rowcount == 0) //try internal user table instead { //string[] _cols2 = { "EmployeesTable.EmployeeID, EmployeesTable.Name, EmployeesTable.OfficeID, EmployeesTable.DefaultView, EmployeesTable.IsEditor, EmployeesTable.EmailAddress", "OfficeTable.OfficeName" }; string[] _cols2 = { "EmployeesTable.EmployeeID", "EmployeesTable.Name", "EmployeesTable.OfficeID", "EmployeesTable.DefaultView", "EmployeesTable.IsEditor", "EmployeesTable.EmailAddress", "OfficeTable.OfficeName" }; query = DB.Select(_cols2).From("EmployeesTable").LeftOuterJoin("OfficeTable", "OfficeID", "EmployeesTable", "OfficeID").Where("Name").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true); _rd = query.ExecuteReader(); while (_rd.Read()) { UserLogin.ID = Guid.NewGuid(); UserLogin.UserId = (Int32)_rd["EmployeeID"]; UserLogin.UserName = (String)_rd["Name"]; UserLogin.UserInitials = ""; //does not apply to internal user UserLogin.OfficeId = (Int32)_rd["OfficeID"]; UserLogin.CompanyId = -1; //does not apply to internal user UserLogin.DefaultView = (Int32)_rd["DefaultView"]; UserLogin.IsEditor = (byte)_rd["IsEditor"]; UserLogin.mailTo = _rd["EmailAddress"] != null ? Convert.ToString(_rd["EmailAddress"]) : ""; UserLogin.OfficeName = _rd["OfficeName"] != null ? Convert.ToString(_rd["OfficeName"]) : ""; UserLogin.telNo = ""; UserLogin.controlOfficeId = -1; //does not apply to internal user UserLogin.companyGroup = 0; UserLogin.loginValue = 1; //successful login _rowcount++; } //build query using username and password //check smaller employees table THEN contact table //Query _qry = new Query(Tables.EmployeesTable).WHERE("Name", Comparison.Equals, txtUser).AND("Password", Comparison.Equals, txtPassword).AND("Live", Comparison.Equals, true); //EmployeesTableCollection _employ = new EmployeesTableCollection(); //_employ.LoadAndCloseReader(_qry.ExecuteReader()); // //if (_employ.Count != 0) //{ // UserLogin.ID = Guid.NewGuid(); // UserLogin.UserId = (Int32)_employ[0].EmployeeID; // UserLogin.UserName = (String)_employ[0].Name; // UserLogin.UserInitials = ""; // UserLogin.OfficeId = (Int32)_employ[0].OfficeID; // UserLogin.CompanyId = -1; // UserLogin.DefaultView = (Int32)_employ[0].DefaultView; // UserLogin.IsEditor = (Int32)_employ[0].IsEditor; // } } catch (Exception ex) { string _ex = ex.Message.ToString(); //set guid or login will end up returning as null UserLogin.ID = Guid.NewGuid(); //return indicator that there was an error do not return error message as we want to hide that from user UserLogin.loginValue = 0; } finally { if (UserLogin.ID == Guid.Empty) { UserLogin = null; } //if (UserLogin.ID == Guid.Empty) return null; //else //{ // return UserLogin; //} } return UserLogin; }
public UserClass Login(string txtUser, string txtPassword) { //26/05/2011 we now encrypt passwords txtPassword = wwi_security.DecryptString(txtPassword, "publiship"); //initialise UserClass UserLogin = new UserClass(); UserLogin.ID = Guid.Empty; int _rowcount = 0; IDataReader _rd; SubSonic.SqlQuery query = new SubSonic.SqlQuery(); try { string[] _cols = { "ContactTable.ContactID", "ContactTable.ContactName", "ContactTable.Name", "ContactTable.ContactInitials", "ContactTable.CompanyID", "ContactTable.DefaultView", "ContactTable.IsEditor", "ContactTable.EMail", "ContactTable.ControllingOfficeID", "NameAndAddressBook.CompanyName", "NameAndAddressBook.TelNo", "NameAndAddressBook.Pricer_Group" }; //query = DB.Select(_cols).From("ContactTable").LeftOuterJoin("NameAndAddressBook", "CompanyID", "ContactTable", "CompanyID").Where("ContactName").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true); //have check ContactName AND Name as usage is inconsistent in database query = DB.Select(_cols).From("ContactTable").LeftOuterJoin("NameAndAddressBook", "CompanyID", "ContactTable", "CompanyID"). Where("ContactName").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true). Or("Name").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true); _rd = query.ExecuteReader(); while (_rd.Read()) { UserLogin.ID = Guid.NewGuid(); UserLogin.UserId = (Int32)_rd["ContactID"]; UserLogin.UserName = Convert.ToString(_rd["ContactName"]);; //Convert.ToString(_rd["ContactName"]); UserLogin.UserInitials = _rd["ContactInitials"] != null?Convert.ToString(_rd["ContactInitials"]) : ""; UserLogin.OfficeId = -1; //external client UserLogin.CompanyId = (Int32)_rd["CompanyID"]; UserLogin.DefaultView = (Int32)_rd["DefaultView"]; UserLogin.IsEditor = (byte)_rd["IsEditor"]; UserLogin.mailTo = _rd["EMail"] != null?Convert.ToString(_rd["EMail"]) : ""; UserLogin.OfficeName = _rd["CompanyName"] != null?Convert.ToString(_rd["CompanyName"]) : ""; UserLogin.telNo = _rd["TelNo"] != null?Convert.ToString(_rd["TelNo"]) : ""; UserLogin.controlOfficeId = (Int32)_rd["ControllingOfficeID"]; //this should be the new controller office link id UserLogin.companyGroup = _rd["Pricer_Group"] != null?wwi_func.vint(_rd["Pricer_Group"].ToString()) : 0; UserLogin.loginValue = 1; //successful login _rowcount++; } //Query _qryb = new Query(Tables.ContactTable).WHERE("ContactName", Comparison.Equals, txtUser).AND("Password", Comparison.Equals, txtPassword).AND("Live", Comparison.Equals, true); //ContactTableCollection _contact = new ContactTableCollection(); //_contact.LoadAndCloseReader(_qryb.ExecuteReader()); // //if (_contact.Count != 0) // { // UserLogin.ID = Guid.NewGuid(); // UserLogin.UserId = (Int32)_contact[0].ContactID; ; // UserLogin.UserName = (String)_contact[0].ContactName; // UserLogin.UserInitials = (String)_contact[0].ContactInitials; // UserLogin.OfficeId = -1; //external client // UserLogin.CompanyId = (Int32)_contact[0].CompanyID; // UserLogin.DefaultView = (Int32)_contact[0].DefaultView; // UserLogin.IsEditor = (Int32)_contact[0].IsEditor; //} if (_rowcount == 0) //try internal user table instead { //string[] _cols2 = { "EmployeesTable.EmployeeID, EmployeesTable.Name, EmployeesTable.OfficeID, EmployeesTable.DefaultView, EmployeesTable.IsEditor, EmployeesTable.EmailAddress", "OfficeTable.OfficeName" }; string[] _cols2 = { "EmployeesTable.EmployeeID", "EmployeesTable.Name", "EmployeesTable.OfficeID", "EmployeesTable.DefaultView", "EmployeesTable.IsEditor", "EmployeesTable.EmailAddress", "OfficeTable.OfficeName" }; query = DB.Select(_cols2).From("EmployeesTable").LeftOuterJoin("OfficeTable", "OfficeID", "EmployeesTable", "OfficeID").Where("Name").IsEqualTo(txtUser).And("Password").IsEqualTo(txtPassword).And("Live").IsEqualTo(true); _rd = query.ExecuteReader(); while (_rd.Read()) { UserLogin.ID = Guid.NewGuid(); UserLogin.UserId = (Int32)_rd["EmployeeID"]; UserLogin.UserName = (String)_rd["Name"]; UserLogin.UserInitials = ""; //does not apply to internal user UserLogin.OfficeId = (Int32)_rd["OfficeID"]; UserLogin.CompanyId = -1; //does not apply to internal user UserLogin.DefaultView = (Int32)_rd["DefaultView"]; UserLogin.IsEditor = (byte)_rd["IsEditor"]; UserLogin.mailTo = _rd["EmailAddress"] != null?Convert.ToString(_rd["EmailAddress"]) : ""; UserLogin.OfficeName = _rd["OfficeName"] != null?Convert.ToString(_rd["OfficeName"]) : ""; UserLogin.telNo = ""; UserLogin.controlOfficeId = -1; //does not apply to internal user UserLogin.companyGroup = 0; UserLogin.loginValue = 1; //successful login _rowcount++; } //build query using username and password //check smaller employees table THEN contact table //Query _qry = new Query(Tables.EmployeesTable).WHERE("Name", Comparison.Equals, txtUser).AND("Password", Comparison.Equals, txtPassword).AND("Live", Comparison.Equals, true); //EmployeesTableCollection _employ = new EmployeesTableCollection(); //_employ.LoadAndCloseReader(_qry.ExecuteReader()); // //if (_employ.Count != 0) //{ // UserLogin.ID = Guid.NewGuid(); // UserLogin.UserId = (Int32)_employ[0].EmployeeID; // UserLogin.UserName = (String)_employ[0].Name; // UserLogin.UserInitials = ""; // UserLogin.OfficeId = (Int32)_employ[0].OfficeID; // UserLogin.CompanyId = -1; // UserLogin.DefaultView = (Int32)_employ[0].DefaultView; // UserLogin.IsEditor = (Int32)_employ[0].IsEditor; // } } catch (Exception ex) { string _ex = ex.Message.ToString(); //set guid or login will end up returning as null UserLogin.ID = Guid.NewGuid(); //return indicator that there was an error do not return error message as we want to hide that from user UserLogin.loginValue = 0; } finally { if (UserLogin.ID == Guid.Empty) { UserLogin = null; } //if (UserLogin.ID == Guid.Empty) return null; //else //{ // return UserLogin; //} } return(UserLogin); }