public static Product UpdateProduct(Product product)
{
Product result = null;
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
Product productToUpdate = database.Products.FirstOrDefault(p => p.ProductID == product.ProductID);
productToUpdate.ProductNumber = product.ProductNumber;
productToUpdate.Name = product.Name;
productToUpdate.Color = product.Color;
productToUpdate.ListPrice = product.ListPrice;
productToUpdate.ProductDescription = product.ProductDescription;
productToUpdate.ProductCategoryID = product.ProductCategoryID;
productToUpdate.Quantity = product.Quantity;
productToUpdate.IsActive = product.IsActive;
database.SaveChanges();
result = productToUpdate;
//result = database.Products
// .Include(c=>c.ProductCategory)
// .Include(so=>so.SalesOrderItems)
// .FirstOrDefault(c => c.ProductID == productToUpdate.ProductID);
}
}
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 catagories",
SystemReason = "Exception reading catagories",
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 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 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 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 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 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 RemoveProduct(int productID)
{
bool result = false;
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
Product productToRemove = database.Products.FirstOrDefault(c => c.ProductID == productID);
if (productToRemove != null)
{
database.Products.Remove(productToRemove);
//productToRemove.MarkAsDeleted();
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 product",
SystemReason = "Removing product",
SystemMessage = e.Message
};
throw new FaultException <SystemFault>(sf);
}
}
return(result);
}
public static int AddProduct(Product product)
{
int result = 0;
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
product.ModifiedDate = DateTime.Now;
product.rowguid = new Guid();
database.Products.Add(product);
//database.Products.AddObject(product);
database.SaveChanges();
Product newProduct = database.Products.FirstOrDefault(c => c.ProductNumber == product.ProductNumber);
result = (newProduct == null) ? 0 : newProduct.ProductID;
}
}
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 product",
SystemReason = "Exception adding product",
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 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 int ProductListCount()
//{
// int productsListCount = 0;
// try
// {
// using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
// {
// var products = from product in database.Products
// .Include(p => p.ProductCategory)
// orderby product.ProductID
// select product;
// productsListCount = products.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 = "Counting products",
// SystemReason = "Exception count products",
// SystemMessage = e.Message
// };
// throw new FaultException<SystemFault>(sf);
// }
// }
// return productsListCount;
//}
public static Product GetProduct(int productID)
{
Product productToGet = null;
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
database.Configuration.ProxyCreationEnabled = false;
var products = from product in database.Products
.Include(p => p.ProductCategory)
where product.ProductID == productID
select product;
productToGet = products.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 = "Getting product",
SystemReason = "Exception getting product",
SystemMessage = e.Message
};
throw new FaultException <SystemFault>(sf);
}
}
return(productToGet);
}
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 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);
}
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 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 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 List <ProductData> ProductList() // (int take, int skip)
{
List <ProductData> productsList = new List <ProductData>();
try
{
using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer())
{
var products = from product in database.Products
.Include(p => p.ProductCategory)
.Include(so => so.SalesOrderItems)
orderby product.ProductID
select new ProductData()
{
ProductNumber = product.ProductNumber,
ProductID = product.ProductID,
CategoryID = product.ProductCategory.ProductCategoryID,
CategoryName = product.ProductCategory.Name,
Color = product.Color,
ListPrice = product.ListPrice,
Name = product.Name,
ProductDescription = product.ProductDescription,
Quantity = product.Quantity,
SalesOrderCount = product.SalesOrderItems.Count,
IsActive = product.IsActive,
IsCategoryActive = product.ProductCategory.IsActive
};
//select product;
//productsList = products.Skip(skip).Take(take).ToList();
productsList = products.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 products",
SystemReason = "Exception reading products",
SystemMessage = e.Message
};
throw new FaultException <SystemFault>(sf);
}
}
return(productsList);
}