// 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 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); } }
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 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 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 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); } }
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); } }
// 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); } }