//Password functions
public static bool CheckPassword(string userID, string password)
{
try
{
string strDbUser = DbUserPrefix + userID;
ProjectTileSqlDatabase defaultPtDb = SqlServerConnection.DefaultPtDbConnection();
using (defaultPtDb)
{
Staff thisUser = defaultPtDb.Staff.FirstOrDefault(s => s.UserID == userID);
if (thisUser == null)
{
return(false);
}
var checkPasswordResults = defaultPtDb.stf_CheckHashedPassword(userID, password).ToList();
bool passwordMatches = (checkPasswordResults[0] == true);
return(passwordMatches);
}
}
catch (SqlException sqlException)
{
MessageFunctions.Error("Error accessing the database", sqlException);
return(false);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error checking existing login", generalException);
return(false);
}
}
// Data retrieval
public static List <Products> ProductsList(string search, bool includeAll = false)
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
List <Products> productGridList = new List <Products>();
productGridList = (from p in existingPtDb.Products
where search == "" || p.ProductName.Contains(search) || p.ProductDescription.Contains(search)
orderby p.ProductName
select p).ToList();
if (includeAll)
{
Products dummyProduct = new Products {
ID = 0, ProductName = AllRecords, ProductDescription = "", LatestVersion = 0
};
productGridList.Add(dummyProduct);
}
return(productGridList);
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error retrieving products list", generalException);
return(null);
}
}
public static void logError(string customMessage, string errorType, string expMessage = "", string targetSite = "", string innerException = "")
{
try
{
ErrorLog newError = new ErrorLog()
{
CustomMessage = customMessage,
ExceptionMessage = expMessage,
ExceptionType = errorType,
TargetSite = targetSite,
LoggedAt = DateTime.Now,
LoggedBy = MyUserID,
InnerException = innerException
};
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
existingPtDb.ErrorLog.Add(newError);
existingPtDb.SaveChanges();
}
}
catch // (Exception e)
{
// MessageBox.Show(e.Message + ": " + e.InnerException.ToString());
// Do nothing - no point throwing another error!
}
}
public static bool HasSingleSignon(string userID)
{
try
{
ProjectTileSqlDatabase defaultPtDb = SqlServerConnection.DefaultPtDbConnection();
using (defaultPtDb)
{
Staff thisUser = defaultPtDb.Staff.FirstOrDefault(s => s.UserID == userID);
if (thisUser == null)
{
return(false);
}
else
{
return(thisUser.SingleSignon && thisUser.OSUser != "" && thisUser.Active);
}
}
}
catch (SqlException sqlException)
{
MessageFunctions.Error("Error accessing the database", sqlException);
return(false);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error checking for single sign-on capability", generalException);
return(false);
}
}
public static string SingleSignonID()
{
try
{
// Log in as the administration user to find the user
ProjectTileSqlDatabase defaultPtDb = SqlServerConnection.DefaultPtDbConnection();
using (defaultPtDb)
{
Staff thisUser = defaultPtDb.Staff.FirstOrDefault(s => s.OSUser == domainUser);
if (thisUser == null)
{
return("");
}
else if (thisUser.SingleSignon && thisUser.UserID != "")
{
return(thisUser.UserID);
}
else
{
return("");
}
}
}
catch (SqlException sqlException)
{
MessageFunctions.Error("Error accessing the database", sqlException);
return("");
}
catch (Exception generalException)
{
MessageFunctions.Error("Error checking for single sign-on capability", generalException);
return("");
}
}
public static bool AmendProduct(int productID, string productName, string productDescription, string version)
{
try
{
decimal versionNumber;
if (!Decimal.TryParse(version, out versionNumber))
{
MessageFunctions.InvalidMessage("Cannot amend product '" + productName + "': new version number is not a decimal.", "Invalid Version");
return(false);
}
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
Products thisProduct = existingPtDb.Products.Find(productID);
if (thisProduct.LatestVersion > versionNumber)
{
bool carryOn = MessageFunctions.WarningYesNo("The new version number is lower than the existing one. Is that correct?", "Unexpected Version");
if (!carryOn)
{
return(false);
}
}
thisProduct.ProductName = productName;
thisProduct.ProductDescription = productDescription;
thisProduct.LatestVersion = versionNumber;
if (ValidateProduct(ref thisProduct, productID))
{
existingPtDb.SaveChanges();
return(true);
}
else
{
return(false);
}
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Problem saving changes to product '" + productName + "'", generalException);
return(false);
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error amending product '" + productName + "'", generalException);
return(false);
}
}
// Login functions
public static bool AttemptLogin(string userID, string password)
{
try
{
int entityID;
string databaseLogin = DbUserPrefix + userID;
if (HasSingleSignon(userID))
{
changeDatabasePassword(userID, password);
} // Changes only the back-end password, to set a new temp password for SSO or reset to the stored one for non-SSO
ProjectTileSqlDatabase userPtDb = SqlServerConnection.UserPtDbConnection(databaseLogin, password);
using (userPtDb)
{
Staff thisUser = userPtDb.Staff.First(s => s.UserID == userID);
entityID = (int)thisUser.DefaultEntity;
Entities currentEntity = userPtDb.Entities.Find(entityID);
if (thisUser.FirstName != "")
{
if (!thisUser.Active)
{
MessageFunctions.InvalidMessage("User is not active. Please contact your system administrator.", "Inactive User");
}
else if (thisUser.LeaveDate < DateTime.Now)
{
MessageFunctions.InvalidMessage("User has left. Please contact your system administrator.", "Not Current User");
}
else if (thisUser.StartDate > DateTime.Now)
{
MessageFunctions.InvalidMessage("User has not yet started. Please contact your system administrator.", "Not Current User");
}
else
{
LogIn(thisUser, currentEntity);
}
}
return(true);
}
}
catch (SqlException sqlException)
{
MessageFunctions.Error("Error accessing the database", sqlException);
return(false);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error logging in", generalException);
return(false);
}
}
/*
* public static SqlConnection Connection (string strUserID, string strPassword)
* {
*
* SqlConnection DBConnection = new SqlConnection();
*
* SqlConnectionStringBuilder SqlBuilder = new SqlConnectionStringBuilder();
* SqlBuilder.DataSource = ".\\SQLExpress";
* SqlBuilder.InitialCatalog = "ProjectTile";
* //SqlBuilder.IntegratedSecurity = true;
* SqlBuilder.UserID = strUserID;
* SqlBuilder.Password = strPassword;
* DBConnection.ConnectionString = SqlBuilder.ConnectionString;
*
* return DBConnection;
* }
*/
public static ProjectTileSqlDatabase DefaultPtDbConnection()
{
try
{
ProjectTileSqlDatabase dB = new ProjectTileSqlDatabase();
return(dB);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error opening default SQL connection", generalException);
return(null);
}
}
public static ProjectTileSqlDatabase ExistingPtDbConnection()
{
try
{
ProjectTileSqlDatabase dB = UserPtDbConnection(savedUser, savedPassword);
return(dB);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error using existing SQL connection", generalException);
return(null);
}
}
public static Products GetProductByID(int productID)
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
return(existingPtDb.Products.Find(productID));
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error finding product with ID " + productID.ToString() + "", generalException);
return(null);
}
}
public static TablePermissions GetPermission(int recordID)
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
return(existingPtDb.TablePermissions.FirstOrDefault(tp => tp.ID == recordID));
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error retrieving table permission details", generalException);
return(null);
}
}
public static ProjectTileSqlDatabase UserPtDbConnection(string userID, string password)
{
string dataSource = "data source=";
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["ProjectTileSqlDatabase"].ConnectionString;
int dataSourcePos = connection.IndexOf(dataSource);
connection = connection.Substring(dataSourcePos);
connection = connection.Replace(dataSource, "");
int endPos = connection.IndexOf(";initial catalog=");
connection = connection.Substring(0, endPos);
SqlConnectionStringBuilder SqlBuilder = new SqlConnectionStringBuilder();
//SqlBuilder.DataSource = "sqlexpress1.cq1cqdtv0373.eu-west-2.rds.amazonaws.com";
SqlBuilder.DataSource = connection;
SqlBuilder.InitialCatalog = "ProjectTile";
SqlBuilder.IntegratedSecurity = false;
SqlBuilder.UserID = userID;
SqlBuilder.Password = password;
SqlBuilder.PersistSecurityInfo = true;
SqlBuilder.MultipleActiveResultSets = true;
string connectionString = SqlBuilder.ToString();
EntityConnectionStringBuilder ecsBuilder = new EntityConnectionStringBuilder();
ecsBuilder.Provider = "System.Data.SqlClient";
ecsBuilder.ProviderConnectionString = connectionString;
ecsBuilder.Metadata = @"res://*/ProjectTileDataModel.csdl|res://*/ProjectTileDataModel.ssdl|res://*/ProjectTileDataModel.msl";
connectionString = ecsBuilder.ToString();
try
{
ProjectTileSqlDatabase dB = new ProjectTileSqlDatabase(connectionString);
savedUser = userID;
savedPassword = password;
return(dB);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error opening SQL connection for user " + userID + "", generalException);
return(null);
}
}
public static Entities GetEntityByName(string entityName)
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
Entities selectedEntity = existingPtDb.Entities.First(ent => ent.EntityName == entityName);
return(selectedEntity);
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error retrieving an Entity called " + entityName + " from the database", generalException);
return(null);
}
}
//public static string[] EntityNameList(int thisUserID, bool includeAll, int excludeID = 0)
//{
// try
// {
// int[] entityIDs = AllowedEntityIDs(thisUserID).Where(aei => !aei.Equals(excludeID)).ToArray();
// ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
// using (existingPtDb)
// {
// var entityList = existingPtDb.Entities
// .Where(ent => entityIDs.Contains(ent.ID))
// .Select(ent => ent.EntityName)
// .ToList();
// if (includeAll) { entityList.Add(AllRecords); }
// string[] entityArray = entityList.ToArray();
// return entityArray;
// }
// }
// catch (Exception generalException)
// {
// MessageFunctions.Error("Error listing valid Entity names", generalException);
// return null;
// }
//}
public static Entities GetEntity(int entityID)
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
Entities selectedEntity = existingPtDb.Entities.Find(entityID);
return(selectedEntity);
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error retrieving Entity ID " + entityID.ToString() + " from the database", generalException);
return(null);
}
}
public static int NewProduct(string productName, string productDescription, string version)
{
try
{
decimal versionNumber;
if (!Decimal.TryParse(version, out versionNumber))
{
MessageFunctions.InvalidMessage("Cannot create new product: version number is not a decimal.", "Invalid Version");
return(0);
}
Products newProduct = new Products()
{
ProductName = productName, ProductDescription = productDescription, LatestVersion = versionNumber
};
if (ValidateProduct(ref newProduct, 0))
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
existingPtDb.Products.Add(newProduct);
existingPtDb.SaveChanges();
return(newProduct.ID);
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Problem saving new product", generalException);
return(0);
}
}
else
{
return(0);
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error creating new product", generalException);
return(0);
}
}
private static void changeDatabasePassword(string userID, string newPassword)
{
try
{
ProjectTileSqlDatabase defaultPtDb = SqlServerConnection.DefaultPtDbConnection();
using (defaultPtDb)
{
defaultPtDb.stf_ChangeDatabasePassword(userID, newPassword);
}
}
catch (SqlException sqlException)
{
MessageFunctions.Error("Error accessing the database", sqlException);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error updating database password to handle single sign-on", generalException);
}
}
public static List <string> LogTables()
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
List <string> tables = existingPtDb.AuditEntries.OrderBy(ae => ae.TableName).Select(ae => ae.TableName).Distinct().ToList();
tables.Sort();
tables.Insert(0, PleaseSelect);
return(tables);
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error retrieving list of auditable tables", generalException);
return(null);
}
}
// Data retrieval
public static int[] AllowedEntityIDs(int staffID)
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
int[] allowedEntities = existingPtDb.StaffEntities
.Where(se => se.StaffID == staffID)
.Select(se => (int)se.EntityID)
.ToArray();
return(allowedEntities);
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error listing valid Entity IDs", generalException);
return(null);
}
}
// Default Entity functions
public static void SetDefaultEntity(ref Entities selectedEntity, int staffID = 0)
{
if (selectedEntity == null)
{
MessageFunctions.InvalidMessage("Please select an Entity to amend from the drop-down list.", "No Entity Selected");
return;
}
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
try
{
if (staffID == 0)
{
staffID = MyStaffID;
}
Staff thisUser = existingPtDb.Staff.Find(staffID);
thisUser.DefaultEntity = selectedEntity.ID;
existingPtDb.SaveChanges();
if (staffID == MyStaffID)
{
UpdateMyDefaultEntity(ref selectedEntity);
}
}
catch (SqlException sqlException)
{
MessageFunctions.Error("SQL error saving new default Entity preference to the database", sqlException);
return;
}
catch (Exception generalException)
{
MessageFunctions.Error("Error saving new default Entity preference to the database", generalException);
return;
}
}
}
// Entity changes
public static bool AllowEntity(int entityID, int staffID)
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
StaffEntities createStaffEntity = new StaffEntities();
createStaffEntity.EntityID = entityID;
createStaffEntity.StaffID = staffID;
existingPtDb.StaffEntities.Add(createStaffEntity);
existingPtDb.SaveChanges();
return(true);
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error retrieving an Entity with ID " + entityID.ToString() + " from the database", generalException);
return(false);
}
}
public static List <Entities> AllowedEntities(int staffID)
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
List <Entities> allowedEntities = (from se in existingPtDb.StaffEntities
join e in existingPtDb.Entities on se.EntityID equals e.ID
where se.StaffID == staffID
orderby e.EntityName
select e)
.ToList();
return(allowedEntities);
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error retrieving valid Entities", generalException);
return(null);
}
}
// ---------------------------------------------------------- //
// -------------------- Page Management --------------------- //
// ---------------------------------------------------------- //
// --------------- Navigation --------------- //
// ------------- Data retrieval ------------- //
public static List <AuditProxy> AllLogEntries(DateTime fromDate, DateTime toDate, string tableName, string userID)
{
DateTime maxTime = toDate.AddDays(1);
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
return((from ae in existingPtDb.AuditEntries
join s in existingPtDb.Staff on ae.UserName.Replace(DbUserPrefix, "") equals s.UserID
into GroupJoin from ss in GroupJoin.DefaultIfEmpty()
where ae.ChangeTime >= fromDate && ae.ChangeTime < maxTime &&
ae.TableName == tableName &&
(userID == "" || userID == AllCodes || userID == ae.UserName.Replace(DbUserPrefix, ""))
orderby ae.ChangeTime descending, ae.PrimaryValue ascending
select new AuditProxy
{
ID = ae.ID,
ActionType = ae.ActionType,
User = ss ?? null,
UserName = ae.UserName.Replace(DbUserPrefix, ""),
ChangeTime = (DateTime)ae.ChangeTime,
TableName = ae.TableName,
PrimaryColumn = ae.PrimaryColumn,
PrimaryValue = ae.PrimaryValue,
ChangeColumn = ae.ChangeColumn,
OldValue = ae.OldValue,
NewValue = ae.NewValue
}
).ToList());
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error retrieving log entry details", generalException);
return(null);
}
}
public static string GetDeletedValue(AuditProxy entry, string columnName)
{
try
{
//if (entry.ChangeColumn == columnName) { return entry.OldValue; }
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
DateTime changeTime = (DateTime)entry.ChangeTime;
DateTime earliest = changeTime.AddMinutes(-1);
DateTime latest = changeTime.AddMinutes(1);
List <AuditEntries> matchingEntries = existingPtDb.AuditEntries.Where(ae =>
ae.TableName == entry.TableName &&
ae.PrimaryValue == entry.PrimaryValue &&
ae.UserName.Replace(DbUserPrefix, "") == entry.UserName &&
ae.ChangeTime >= earliest && ae.ChangeTime <= latest &&
ae.ActionType == entry.ActionType
).ToList();
if (matchingEntries.Exists(me => me.ChangeColumn == columnName))
{
return(matchingEntries.Where(me => me.ChangeColumn == columnName).Select(me => me.OldValue).FirstOrDefault());
}
else
{
return("");
}
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error retrieving details of deletion", generalException);
return(null);
}
}
public TableSecurity(Staff currentUser = null, int userID = 0)
{
try
{
ProjectTileSqlDatabase defaultPtDb = SqlServerConnection.DefaultPtDbConnection();
using (defaultPtDb)
{
if (currentUser == null && userID == 0)
{
MessageFunctions.Error("A staff record or ID number must be provided to get Table Permissions.", null);
}
else if (currentUser == null)
{
currentUser = defaultPtDb.Staff.Find(userID);
}
string thisUsersRole = currentUser.RoleCode;
var permissions = from tp in defaultPtDb.TablePermissions where tp.RoleCode == thisUsersRole select tp;
// Set all permissions that can be set based on the TablePermissions table
foreach (var tUP in permissions)
{
userPermissions.Add("View" + tUP.TableName, (bool)tUP.ViewTable);
userPermissions.Add("Add" + tUP.TableName, (bool)tUP.InsertRows);
userPermissions.Add("Edit" + tUP.TableName, (bool)tUP.UpdateRows);
userPermissions.Add("Activate" + tUP.TableName, (bool)tUP.ChangeStatus);
}
// ToDo - Now set other permissions based on other factors, e.g. can the user change Entity or do they only have one?
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error setting security permissions", generalException);;
}
}
// Changes
public static bool ValidateProduct(ref Products thisProduct, int existingID)
{
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
string productName = thisProduct.ProductName;
if (!PageFunctions.SqlInputOK(productName, true, "Product name"))
{
return(false);
}
Products checkNewName = existingPtDb.Products.FirstOrDefault(p => p.ID != existingID && p.ProductName == productName);
if (checkNewName == null)
{
thisProduct.ProductName = productName;
}
else
{
string errorText = (existingID > 0) ?
"Could not amend Product. Another Product with name '" + productName + "' already exists." :
"Could not create new Product. A Product with name '" + productName + "' already exists.";
MessageFunctions.InvalidMessage(errorText, "Duplicate Name");
return(false);
}
string productDescription = thisProduct.ProductDescription;
if (!PageFunctions.SqlInputOK(productDescription, true, "Product description"))
{
return(false);
}
Products checkNewDescription = existingPtDb.Products.FirstOrDefault(p => p.ID != existingID && p.ProductDescription == productDescription);
if (checkNewDescription == null)
{
thisProduct.ProductDescription = productDescription;
}
else
{
string errorText = (existingID > 0) ?
"Could not amend product. Another product with description '" + productDescription + "' already exists." :
"Could not create new product. A product with description '" + productDescription + "' already exists.";
MessageFunctions.InvalidMessage(errorText, "Duplicate Description");
return(false);
}
return(true);
}
}
catch (SqlException sqlException)
{
MessageFunctions.Error("SQL error saving changes to the database", sqlException);
return(false);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error saving changes to the database", generalException);
return(false);
}
}
public static void NewEntity(string entityName, string entityDescription, bool switchTo, bool makeDefault)
{
int newEntityID;
Entities newEntity;
if (!PageFunctions.SqlInputOK(entityName, true, "Entity name"))
{
return;
}
else if (!PageFunctions.SqlInputOK(entityDescription, true, "Entity description"))
{
return;
}
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
Entities checkNewName = existingPtDb.Entities.FirstOrDefault(ent => ent.EntityName == entityName);
if (checkNewName != null)
{
MessageFunctions.InvalidMessage("Could not create new Entity. An Entity with name '" + entityName + "' already exists.", "Duplicate Name");
return;
}
Entities checkNewDescription = existingPtDb.Entities.FirstOrDefault(ent => ent.EntityDescription == entityDescription);
if (checkNewDescription != null)
{
MessageFunctions.InvalidMessage("Could not create new Entity. An Entity with description '" + entityDescription + "' already exists.", "Duplicate Description");
return;
}
try
{
try
{
newEntity = new Entities();
newEntity.EntityName = entityName;
newEntity.EntityDescription = entityDescription;
try
{
existingPtDb.Entities.Add(newEntity);
existingPtDb.SaveChanges();
newEntityID = newEntity.ID;
}
catch (Exception generalException)
{
MessageFunctions.Error("Problem creating entity ID", generalException);
return;
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error creating database record", generalException);
return;
}
try
{
Staff currentUser = MyStaffRecord;
AllowEntity(newEntityID, currentUser.ID);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error providing access to the new database", generalException);
return;
}
try
{
existingPtDb.SaveChanges();
string switched = ". Use the 'Change Current Entity' function to log into it if you wish to work in this Entity.";
if (switchTo)
{
UpdateCurrentEntity(ref newEntity);
switched = " and you are now logged into it.";
}
if (makeDefault)
{
SetDefaultEntity(ref newEntity);
}
MessageFunctions.SuccessAlert("Entity '" + entityName + "' has been created" + switched, "New Entity Created");
PageFunctions.ShowTilesPage();
}
catch (SqlException sqlException)
{
MessageFunctions.Error("SQL error saving changes to the database", sqlException);
return;
}
catch (Exception generalException)
{
MessageFunctions.Error("Error saving changes to the database", generalException);
return;
}
}
catch (Exception generalException) { MessageFunctions.Error("Error creating new database", generalException); }
}
}
catch (Exception generalException) { MessageFunctions.Error("Error checking new database details", generalException); }
}
public static void AmendEntity(ref Entities selectedEntity, string entityName, string entityDescription)
{
int intSelectedEntityID;
if (selectedEntity == null)
{
MessageFunctions.InvalidMessage("Please select an Entity to amend from the drop-down list.", "No Entity Selected");
return;
}
if (!PageFunctions.SqlInputOK(entityName, true, "Entity name"))
{
return;
}
else if (!PageFunctions.SqlInputOK(entityDescription, true, "Entity description"))
{
return;
}
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
intSelectedEntityID = selectedEntity.ID;
Entities checkNewName = existingPtDb.Entities.FirstOrDefault(ent => ent.EntityName == entityName && ent.ID != intSelectedEntityID);
if (checkNewName != null)
{
MessageFunctions.InvalidMessage("Could not amend Entity. Another Entity with name '" + entityName + "' already exists.", "Duplicate Name");
return;
}
Entities checkNewDescription = existingPtDb.Entities.FirstOrDefault(ent => ent.EntityDescription == entityDescription && ent.ID != intSelectedEntityID);
if (checkNewDescription != null)
{
MessageFunctions.InvalidMessage("Could not amend Entity. Another Entity with description '" + entityDescription + "' already exists.", "Duplicate Description");
return;
}
try
{
try
{
string nameChange = "";
string originalName = selectedEntity.EntityName;
if (originalName != entityName)
{
nameChange = " to '" + entityName + "'";
}
;
Entities changeDbEntity = existingPtDb.Entities.Find(intSelectedEntityID);
changeDbEntity.EntityName = entityName;
changeDbEntity.EntityDescription = entityDescription;
existingPtDb.SaveChanges();
MessageFunctions.SuccessAlert("Entity '" + originalName + "' has been amended" + nameChange + ".", "Entity Amended");
if (changeDbEntity.ID == CurrentEntityID)
{
UpdateCurrentEntity(ref changeDbEntity);
}
if (changeDbEntity.ID == MyDefaultEntityID)
{
UpdateMyDefaultEntity(ref changeDbEntity);
}
PageFunctions.ShowTilesPage();
}
catch (Exception generalException)
{
MessageFunctions.Error("Error amending database record", generalException);
return;
}
}
catch (Exception generalException) { MessageFunctions.Error("Error creating new database", generalException); }
}
}
catch (Exception generalException) { MessageFunctions.Error("Error checking new database details", generalException); }
}
public static void SetErrorLogEntries(DateTime fromDate, DateTime toDate, string type, string userID)
{
DateTime maxTime = toDate.AddDays(1);
try
{
ProjectTileSqlDatabase existingPtDb = SqlServerConnection.ExistingPtDbConnection();
using (existingPtDb)
{
ErrorLogEntries = (from el in existingPtDb.ErrorLog
join s in existingPtDb.Staff on el.LoggedBy equals s.UserID
into GroupJoin from ss in GroupJoin.DefaultIfEmpty()
where el.LoggedAt >= fromDate && el.LoggedAt <= maxTime &&
(type == AllRecords || el.ExceptionType.Replace("System.", "") == type) &&
(userID == "" || userID == AllCodes || userID == el.LoggedBy.Replace(DbUserPrefix, ""))
orderby(DateTime) el.LoggedAt descending
select new ErrorProxy
{
// ID = el.ID,
CustomMessage = el.CustomMessage,
ExceptionMessage = el.ExceptionMessage,
ExceptionType = el.ExceptionType,
TargetSite = el.TargetSite,
LoggedAt = DbFunctions.CreateDateTime( // Have to do this 'long hand' to make sure we get distinct results
((DateTime)el.LoggedAt).Year,
((DateTime)el.LoggedAt).Month,
((DateTime)el.LoggedAt).Day,
((DateTime)el.LoggedAt).Hour,
((DateTime)el.LoggedAt).Minute,
0),
LoggedBy = el.LoggedBy,
User = ss ?? null,
InnerException = el.InnerException
}
).Distinct().ToList();
//foreach (ErrorProxy el in allErrors)
//{
// DateTime dt = el.LoggedAt;
// el.LoggedAt = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, 0);
//}
//ErrorLogEntries = allErrors.Select(ae => new ErrorProxy
//{
// CustomMessage = ae.CustomMessage,
// ExceptionMessage = ae.ExceptionMessage,
// ExceptionType = ae.ExceptionType,
// TargetSite = ae.TargetSite,
// LoggedAt = ae.LoggedAt,
// LoggedBy = ae.LoggedBy,
// User = ae.User,
// InnerException = ae.InnerException
//}
//).Distinct().ToList();
}
}
catch (Exception generalException)
{
MessageFunctions.Error("Error retrieving error log entries", generalException);
}
}
public static bool SingleSignon(string userID)
{
Staff thisUser = null;
string newPassword = "";
try
{
// Log in as the administration user to find the user
ProjectTileSqlDatabase defaultPtDb = SqlServerConnection.DefaultPtDbConnection();
using (defaultPtDb)
{
thisUser = defaultPtDb.Staff.FirstOrDefault(s => s.UserID == userID);
}
if (thisUser == null)
{
MessageFunctions.InvalidMessage("No matching user found with UserID " + userID + ".", "Invalid UserID");
return(false);
}
else if (thisUser.SingleSignon && domainUser == thisUser.OSUser)
{
char[] validChars = Enumerable.Range('A', 26)
.Concat(Enumerable.Range('a', 26))
.Concat(Enumerable.Range('0', 10))
.Select(i => (char)i)
.ToArray();
byte[] randomNumber = new byte[64 + 1];
RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider();
using (crypto)
{
crypto.GetBytes(randomNumber);
int length = 32 + (int)(32 * (randomNumber[0] / (double)byte.MaxValue));
newPassword = new string(randomNumber
.Skip(1)
.Take(length)
.Select(b => (int)((validChars.Length - 1) * (b / (double)byte.MaxValue)))
.Select(i => validChars[i])
.ToArray()
);
newPassword = newPassword.Substring(0, Math.Min(newPassword.Length, 20));
crypto.Dispose();
}
AttemptLogin(userID, newPassword); // AttemptLogin will now handle the password change if needed
return(true);
}
else
{
return(false);
}
}
catch (SqlException sqlException)
{
MessageFunctions.Error("Error accessing the database", sqlException);
return(false);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error finding matching staff member", generalException);
return(false);
}
}
public static bool ChangeLoginDetails(int staffID, string userID, string newPassword, string confirmPassword)
{
bool passwordChange = (newPassword != "");
bool userIDChange = false;
if (userID == "")
{
MessageFunctions.Error("UserID has not been passed to this function.", null); // UserID is required to check complexity so that userID cannot equal password
return(false);
}
if (passwordChange && newPassword != confirmPassword)
{
MessageFunctions.InvalidMessage("New password does not match confirmation. Please check both fields and try again.", "Password Mismatch");
return(false);
}
else if (passwordChange && !PasswordComplexityOK(userID, newPassword))
{
return(false);
}
else
{
try
{
// Log in as the administration user to allow the change to be made
ProjectTileSqlDatabase defaultPtDb = SqlServerConnection.DefaultPtDbConnection();
using (defaultPtDb)
{
try
{
Staff thisUser = defaultPtDb.Staff.FirstOrDefault(s => s.ID == staffID);
if (thisUser == null)
{
MessageFunctions.Error("Error amending login details in the database: user with ID " + staffID.ToString() + " not found.", null);
return(false);
}
if (passwordChange)
{
thisUser.Passwd = newPassword;
}
if (thisUser.UserID == null || thisUser.UserID != userID)
{
Staff checkUserID = defaultPtDb.Staff.FirstOrDefault(s => s.UserID == userID && s.ID != staffID);
if (checkUserID != null)
{
MessageFunctions.InvalidMessage("A different staff member with UserID '" + userID +
"' already exists. Please try a different one.", "Duplicate UserID");
return(false);
}
userIDChange = true;
thisUser.UserID = userID;
}
defaultPtDb.SaveChanges();
// Now amend any history records, to show that the user effectively made this change
DateTime timeFrom = System.DateTime.Now.AddMinutes(-5);
int[] auditEntryIDs = defaultPtDb.AuditEntries
.Where(ae => ae.TableName == "Staff" &&
ae.ChangeTime >= timeFrom &&
ae.ActionType == "Updated" &&
ae.PrimaryValue == staffID.ToString() &&
ae.UserName.Substring(0, 5) != DbUserPrefix &&
((passwordChange && ae.ChangeColumn == "PasswordHash") || (userIDChange && ae.ChangeColumn == "UserID"))
)
.OrderByDescending(ae => ae.ChangeTime)
.Select(ae => (int)ae.ID)
.ToArray();
foreach (int entry in auditEntryIDs)
{
AuditEntries lastAuditEntry = defaultPtDb.AuditEntries.Find(entry);
lastAuditEntry.UserName = DbUserPrefix + MyUserID;
defaultPtDb.SaveChanges();
}
if (staffID == MyStaffID)
{
string databaseLogin = DbUserPrefix + userID;
ProjectTileSqlDatabase userPtDb = SqlServerConnection.UserPtDbConnection(databaseLogin, newPassword); // Log in again so that future database calls have the new password
}
return(true);
}
catch (SqlException sqlException)
{
MessageFunctions.Error("Error amending login details in the database", sqlException);
return(false);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error amending login details", generalException);
return(false);
}
}
}
catch (SqlException sqlException)
{
MessageFunctions.Error("Error accessing the database", sqlException);
return(false);
}
catch (Exception generalException)
{
MessageFunctions.Error("Error checking existing login", generalException);
return(false);
}
}
}