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