public static int AddCategory(ProductCategory category)
{
int result = 0;
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
category.ModifiedDate = DateTime.Now;
category.rowguid = new Guid();
database.ProductCategories.Add(category);
database.SaveChanges();
ProductCategory newCategory = database.ProductCategories.FirstOrDefault(c => c.ProductCategoryID == category.ProductCategoryID);
result = (newCategory == null) ? 0 : newCategory.ProductCategoryID;
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Adding category",
SystemReason = "Exception adding category",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return result;
}
public static bool Authenicate(string username, string password)
{
bool result = false;
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
Customer customer = database.Customers.FirstOrDefault(c => c.Username == username);
if (customer == null) return false;
byte[] salt = Convert.FromBase64String(customer.PasswordSalt);
byte[] passwordBytes = Encoding.UTF8.GetBytes(password);
byte[] hash = Convert.FromBase64String(customer.PasswordHash);
if (CompareByteArrays(hash, GeneratedSaltedHash(passwordBytes, salt)))
{ return true; }
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Authenicating user",
SystemReason = "Exception authenicating user",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return result;
}
public static List<ProductCategory> CategoriesList()
{
List<ProductCategory> categoriesList = new List<ProductCategory>();
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
var categories = from category in database.ProductCategories
select category;
foreach (var cat in categories)
{
var q = from category in database.ProductCategories
.Include(c => c.Products)
where category.ProductCategoryID == cat.ProductCategoryID
select category.Products.Count;
cat.ProductCount = q.FirstOrDefault();
}
categoriesList = categories.ToList();
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Iterate through categories",
SystemReason = "Exception reading categories",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return categoriesList;
}
public static ProductCategory UpdateCategory(ProductCategory category)
{
ProductCategory result = null;
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
ProductCategory categoryToUpdate = database.ProductCategories.FirstOrDefault(c => c.ProductCategoryID == category.ProductCategoryID);
if (categoryToUpdate == null)
{
categoryToUpdate = new ProductCategory()
{
ProductCategoryID = 0,
Name = category.Name,
IsActive = category.IsActive,
ModifiedDate = DateTime.Now,
rowguid = Guid.NewGuid()
};
database.ProductCategories.Add(categoryToUpdate);
}
else
{
categoryToUpdate.Name = category.Name;
categoryToUpdate.IsActive = category.IsActive;
categoryToUpdate.ModifiedDate = DateTime.Now;
categoryToUpdate.rowguid = Guid.NewGuid();
}
database.SaveChanges();
result = database.ProductCategories.FirstOrDefault(c => c.rowguid == categoryToUpdate.rowguid);
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Updating category",
SystemReason = "Updating category",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return result;
}
public static bool RemoveCategory(int categoryID)
{
bool result = false;
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
ProductCategory categoryToRemove = database.ProductCategories.FirstOrDefault(c => c.ProductCategoryID == categoryID);
if (categoryToRemove != null)
{
database.ProductCategories.Remove(categoryToRemove);
database.SaveChanges();
result = true;
}
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Removing category",
SystemReason = "Removing category",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return result;
}
public static ProductCategory GetCategory(int categoryID)
{
ProductCategory categoryToGet = new ProductCategory();
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
var categories = from category in database.ProductCategories
where category.ProductCategoryID == categoryID
select category;
categoryToGet = categories.FirstOrDefault();
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Iterate through categories",
SystemReason = "Exception reading categories",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return categoryToGet;
}
public static List<SalesOrder> GetSalesOrderList(string userName, int take, int skip)
{
List<SalesOrder> salesOrderList = new List<SalesOrder>();
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
var customer = database.Customers.FirstOrDefault(c => c.Username == userName);
if (customer != null)
{
var salesorders = from salesorder in database.SalesOrders
.Include(so => so.SalesOrderItems.Select(soi => soi.Product.ProductCategory))
.Where(so => so.CustomerID == customer.CustomerID)
orderby salesorder.SalesOrderID ascending
select salesorder;
salesOrderList = salesorders.Skip(skip).Take(take).ToList();
}
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Iterate through product history",
SystemReason = "Exception reading product history",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return salesOrderList;
}
public static void SaveSalesOrder(List<ShoppingCartItem> shoppingCart, string username)
{
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
Customer customer = database.Customers.First(c => c.Username == username);
SalesOrder salesOrder = new SalesOrder()
{
CustomerID = customer.CustomerID,
Customer = customer,
SalesOrderID = 0,
SalesOrderItems = new List<SalesOrderItem>(),
OrderDate = DateTime.Now
};
foreach (ShoppingCartItem item in shoppingCart)
{
Product matchingProduct = database.Products.FirstOrDefault(p => p.ProductNumber == item.ProductNumber);
if (matchingProduct == null) throw new FaultException<SystemFault>(new SystemFault
{
SystemOperation = "Checking out",
SystemReason = "Shopping cart",
SystemMessage = String.Format("Product {0} does not exist.", item.ProductNumber)
});
SalesOrderItem salesOrderItem = new SalesOrderItem()
{
SalesOrderID = 0,
ProductID = matchingProduct.ProductID,
ActualCost = matchingProduct.ListPrice,
OrderBy = item.OrderBy,
ModifiedDate = DateTime.Now,
Quantity = item.Quantity,
};
salesOrder.SalesOrderItems.Add(salesOrderItem);
}
database.SalesOrders.Add(salesOrder);
database.SaveChanges();
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Checking out",
SystemReason = "Exception checking out shopping cart",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
}
public static List<SalesHistoryData> GetSalesHistory(string userName, int take, int skip)
{
List<SalesHistoryData> salesHistoryList = new List<SalesHistoryData>();
List<SalesOrder> salesOrderList = new List<SalesOrder>();
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
var customer = database.Customers.FirstOrDefault(c => c.Username == userName);
if (customer != null)
{
var salesorders = from salesorder in database.SalesOrders
.Include(so => so.SalesOrderItems.Select(soi => soi.Product.ProductCategory))
.Where(so => so.CustomerID == customer.CustomerID)
orderby salesorder.SalesOrderID ascending
select salesorder;
salesOrderList = salesorders.Skip(skip).Take(take).ToList();
foreach (var salesOrder in salesOrderList)
{
foreach (var salesOrderItem in salesOrder.SalesOrderItems.OrderBy(item=>item.OrderBy))
{
salesHistoryList.Add(new SalesHistoryData()
{
SalesOrderNumber = salesOrder.SalesOrderID,
OrderDate = salesOrder.OrderDate,
ProductNumber = salesOrderItem.Product.ProductNumber,
ProductName = salesOrderItem.Product.Name,
Quantity = salesOrderItem.Quantity,
PerItemCost = salesOrderItem.Product.ListPrice,
ItemSubTotal = salesOrderItem.Quantity * salesOrderItem.Product.ListPrice,
OrderTotal = salesOrder.SalesOrderItems.Sum(item => item.Quantity * item.Product.ListPrice),
SortOrder = salesOrderItem.OrderBy
});
}
}
}
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Iterate through product history",
SystemReason = "Exception reading product history",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return salesHistoryList;
}
public static int GetSalesOrderListCount(string userName)
{
int salesOrderListCount = 0;
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
var customer = database.Customers.FirstOrDefault(c => c.Username == userName);
if (customer != null)
{
var salesorders = from salesorder in database.SalesOrders
//.Include(so => so.SalesOrderItems.Select(soi => soi.Product.ProductCategory))
.Where(so => so.CustomerID == customer.CustomerID)
select salesorder;
salesOrderListCount = salesorders.Count();
}
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Iterate through product history",
SystemReason = "Exception reading product history",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return salesOrderListCount;
}
public static string[] GetUserRoles(string username)
{
List<string> result = new List<string>();
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
Customer customer = database.Customers.FirstOrDefault(c => c.Username == username);
if (customer != null)
{
result.Add(Role.User.ToString());
if (customer.IsAdmin)
result.Add(Role.Admin.ToString());
}
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Authorizing user",
SystemReason = "Exception authorizing user",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return result.ToArray<string>();
}
//public static void CreateInitialUsers()
//{
// CreateUser(new CustomerData()
// {
// Username = "******",
// FirstName = "Fred",
// LastName = "Flintstone"
// }, "Pa$$w0rd");
// CreateUser(new CustomerData()
// {
// Username = "******",
// FirstName = "Bert",
// LastName = "Andernie"
// }, "Pa$$w0rd");
// Authenicate("Fred", "Pa$$w0rd");
//}
private static void CreateUser(CustomerData customer, string password)
{
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
byte[] salt = CreateSalt(32);
byte[] passwordBytes = Encoding.UTF8.GetBytes(password);
Customer customerToAdd = new Customer()
{
Username = customer.Username,
FirstName = customer.FirstName,
LastName = customer.LastName,
IsAdmin = false,
PasswordSalt = Convert.ToBase64String(salt),
PasswordHash = Convert.ToBase64String(GeneratedSaltedHash(passwordBytes, salt)),
};
database.Customers.Add(customerToAdd);
database.SaveChanges();
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Authenicating user",
SystemReason = "Exception authenicating user",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
}
public static bool IsUserAdmin(string username)
{
bool result = false;
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
Customer customer = database.Customers.FirstOrDefault(c => c.Username == username);
if (customer != null && customer.IsAdmin)
result = true;
}
}
catch (Exception e)
{
if (e.InnerException is System.Data.SqlClient.SqlException)
{
DatabaseFault dbf = new DatabaseFault
{
DbOperation = "Connect to database",
DbReason = "Exception accessing database",
DbMessage = e.InnerException.Message
};
throw new FaultException<DatabaseFault>(dbf);
}
else
{
SystemFault sf = new SystemFault
{
SystemOperation = "Is Admin",
SystemReason = "Exception authorizing user",
SystemMessage = e.Message
};
throw new FaultException<SystemFault>(sf);
}
}
return result;
}
