public static Store LoadbyId(Guid id)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblStore tblStore = dc.tblStores.FirstOrDefault(c => c.Id == id);
if (tblStore != null)
{
Store store = new Store
{
Id = tblStore.Id,
Title = tblStore.Title
};
return(store);
}
else
{
throw new Exception("Row was not found!!!");
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static List <Layout> LoadByStoreId(Guid StoreId)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
var results = dc.tblLayouts.Where(l => l.StoreId == StoreId).ToList();
List <Layout> layouts = new List <Layout>();
if (results != null)
{
foreach (var item in results)
{
Layout layout = new Layout
{
Id = item.Id,
StoreId = item.StoreId,
Title = item.Title,
Aisles = AisleManager.LoadByLayoutId(item.Id)
};
layouts.Add(layout);
}
return(layouts);
}
else
{
throw new Exception("Cannot find Layout.");
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static AisleProduct LoadAisleByProductId(Guid productId)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
var results = (from ap in dc.tblAisleProducts
join a in dc.tblAisles on ap.AisleId equals a.Id
join p in dc.tblProducts on ap.ProductId equals p.Id
join l in dc.tblLayouts on a.LayoutId equals l.Id
where ap.ProductId == productId
select new
{
Id = ap.Id,
AisleId = a.Id,
ProductId = p.Id,
AisleNumber = a.Number
}).FirstOrDefault();
// Create a new AisleProduct object based on returned values
AisleProduct aisleProduct = new AisleProduct();
aisleProduct.Id = results.Id;
aisleProduct.AisleId = results.AisleId;
aisleProduct.ProductId = results.ProductId;
aisleProduct.AisleNumber = results.AisleNumber;
return(aisleProduct);
}
}
catch (Exception ex)
{
throw ex;
}
}
// TODO: Add code to sort the list alphabetically by Store.Title
public static List <Store> Load()
{
try
{
List <Store> stores = new List <Store>();
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
var results = (from s in dc.tblStores
orderby s.Title
select new
{
s.Id,
s.Title
}).ToList();
foreach (var s in results)
{
Store store = new Store();
store.Id = s.Id;
store.Title = s.Title;
stores.Add(store);
}
return(stores);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static List <Aisle> LoadByLayoutId(Guid layoutId)
{
try
{
List <Aisle> aisles = new List <Aisle>();
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
var results = dc.tblAisles.Where(a => a.LayoutId == layoutId);
foreach (var aisle in results)
{
Aisle a = new Aisle();
a.Id = aisle.Id;
a.LayoutId = aisle.LayoutId;
a.Number = aisle.Number;
a.Lineup = aisle.Lineup;
a.Products = ProductManager.LoadByAisleId(aisle.Id);
aisles.Add(a);
}
}
return(aisles);
}
catch (Exception ex)
{
throw ex;
}
}
public static int Update(AisleProduct aisleProduct)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblAisleProduct updatedrow = dc.tblAisleProducts.FirstOrDefault(ap => ap.Id == aisleProduct.Id);
if (updatedrow != null)
{
updatedrow.Id = aisleProduct.Id;
updatedrow.AisleId = aisleProduct.AisleId;
updatedrow.ProductId = aisleProduct.ProductId;
return(dc.SaveChanges());
}
else
{
return(0);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static UserProduct LoadById(Guid id)
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblUserProduct exsitingRow = dc.tblUserProducts.FirstOrDefault(u => u.Id == id);
if (exsitingRow != null)
{
UserProduct uProduct = new UserProduct
{
Id = exsitingRow.Id,
UserId = exsitingRow.UserId,
ProductId = exsitingRow.ProductId,
ProductTitle = exsitingRow.tblProduct.Title,
InCart = exsitingRow.InCart,
Amount = exsitingRow.Amount,
Notes = exsitingRow.Notes,
//ProductAisle = exsitingRow.tblAisleProduct.Aisle
};
return(uProduct);
}
else
{
throw new Exception("Row was not found.");
}
}
}
public static int Delete(Guid aisleId, Guid productId)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblAisleProduct deletedrow = dc.tblAisleProducts
.FirstOrDefault(u => u.AisleId == aisleId &&
u.ProductId == productId);
if (deletedrow != null)
{
dc.tblAisleProducts.Remove(deletedrow);
return(dc.SaveChanges());
}
else
{
return(0);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static int Delete(Guid id)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblUserProduct row = dc.tblUserProducts
.FirstOrDefault(up => up.Id == id);
if (row != null)
{
dc.tblUserProducts.Remove(row);
return(dc.SaveChanges());
}
else
{
return(0);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static int Update(UserProduct userProduct)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblUserProduct tbluserProduct = dc.tblUserProducts.FirstOrDefault(up => up.Id == userProduct.Id);
if (tbluserProduct != null)
{
tbluserProduct.UserId = userProduct.UserId;
tbluserProduct.ProductId = userProduct.ProductId;
tbluserProduct.Amount = userProduct.Amount;
tbluserProduct.Notes = userProduct.Notes;
tbluserProduct.InCart = userProduct.InCart;
return(dc.SaveChanges());
}
else
{
return(0);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static int Update(Layout layout)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblLayout updatedrow = dc.tblLayouts.FirstOrDefault(l => l.Id == layout.Id);
if (updatedrow != null)
{
updatedrow.StoreId = layout.StoreId;
updatedrow.Title = layout.Title;
return(dc.SaveChanges());
}
else
{
return(0);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Layout LoadById(Guid id)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblLayout tbllayout = dc.tblLayouts.FirstOrDefault(l => l.Id == id);
Layout layout = new Layout();
if (tbllayout != null)
{
layout.Id = tbllayout.Id;
layout.StoreId = (StoreManager.LoadByLayoutId(id)).Id;
layout.Title = tbllayout.Title;
return(layout);
}
else
{
throw new Exception("Cannot find Layout.");
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Product LoadById(Guid id)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblProduct tblProduct = dc.tblProducts.FirstOrDefault(c => c.Id == id);
if (tblProduct != null)
{
Product product = new Product
{
Id = tblProduct.Id,
Title = tblProduct.Title
};
return(product);
}
else
{
throw new Exception("Row was not found.");
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static List <Product> Load()
{
try
{
List <Product> products = new List <Product>();
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
var results = (from p in dc.tblProducts
orderby p.Title
select new
{
p.Id,
p.Title
}).ToList();
foreach (var p in results)
{
Product product = new Product();
product.Id = p.Id;
product.Title = p.Title;
products.Add(product);
}
return(products);
}
}
catch (Exception)
{
throw;
}
}
public static List <Product> LoadByAisleId(Guid aisleId)
{
List <Product> products = new List <Product>();
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
var results = (from ap in dc.tblAisleProducts
join p in dc.tblProducts on ap.ProductId equals p.Id
join a in dc.tblAisles on ap.AisleId equals a.Id
where ap.AisleId == aisleId
select new
{
Id = p.Id,
Title = p.Title
}).ToList();
results.ForEach(p => products.Add(new Product
{
Id = p.Id,
Title = p.Title,
}));
}
return(products);
}
// TODO: Add code to order list of Products alphabetically
public static List <Product> LoadAll()
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
List <Product> products = new List <Product>();
dc.tblProducts.ToList().ForEach(p => products.Add(new Product
{
Id = p.Id,
Title = p.Title
}));
return(products);
}
}
public void Insert()
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
Assert.AreEqual(3, dc.tblStores.Count());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static int Insert(Product product)
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblProduct newRow = new tblProduct();
newRow.Id = Guid.NewGuid();
newRow.Title = product.Title;
dc.tblProducts.Add(newRow);
return(dc.SaveChanges());
}
}
// Load GroceryList by StoreId
public static List <UserProduct> LoadGroceryByLayout(Guid userId, Guid storeId)
{
try
{
List <UserProduct> userProducts = new List <UserProduct>();
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
var results = (from up in dc.tblUserProducts
join p in dc.tblProducts on up.ProductId equals p.Id
join ap in dc.tblAisleProducts on p.Id equals ap.ProductId
join a in dc.tblAisles on ap.AisleId equals a.Id
join l in dc.tblLayouts on a.LayoutId equals l.Id
join s in dc.tblStores on l.StoreId equals s.Id
where up.UserId == userId && s.Id == storeId
orderby up.InCart, a.Lineup, p.Title
select new
{
up.Id,
up.UserId,
up.ProductId,
p.Title,
up.Amount,
up.Notes,
up.InCart,
a.Number
}).ToList();
foreach (var up in results)
{
UserProduct userProduct = new UserProduct();
userProduct.Id = up.Id;
userProduct.UserId = up.UserId;
userProduct.ProductId = up.ProductId;
userProduct.ProductTitle = up.Title;
userProduct.Amount = up.Amount;
userProduct.Notes = up.Notes;
userProduct.InCart = up.InCart;
userProduct.ProductAisle = up.Number;
userProducts.Add(userProduct);
}
return(userProducts);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void spClearGroceryList(Guid userId)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
// Stored Proc to delete all things in list
var spResults = dc.ClearGroceryList(userId);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static int Delete(Guid id)
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblUser deleterow = dc.tblUsers.FirstOrDefault(c => c.Id == id);
if (deleterow != null)
{
dc.tblUsers.Remove(deleterow);
return(dc.SaveChanges());
}
else
{
throw new Exception("Row was not found!!!");
}
}
}
public static int Insert(Aisle aisle)
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblAisle tblaisle = new tblAisle();
tblaisle.Id = Guid.NewGuid();
tblaisle.LayoutId = aisle.LayoutId;
tblaisle.Number = aisle.Number;
tblaisle.Lineup = aisle.Lineup;
aisle.Id = tblaisle.Id;
dc.tblAisles.Add(tblaisle);
return(dc.SaveChanges());
}
}
public static List <User> Load()
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
List <User> users = new List <User>();
dc.tblUsers.ToList().ForEach(u => users.Add(new User
{
Id = u.Id,
FirstName = u.FirstName,
LastName = u.LastName,
UserPass = u.UserPass,
Email = u.Email,
GroceryList = UserProductManager.LoadByUserId(u.Id)
}));
return(users);
}
}
public static bool Login(User user)
{
if (!string.IsNullOrEmpty(user.Email))
{
if (!string.IsNullOrEmpty(user.UserPass))
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblUser tbluser = dc.tblUsers.FirstOrDefault(u => u.Email == user.Email);
if (tbluser != null)
{
//if(GetHash(tbluser.UserPass) == GetHash(user.UserPass)) //"gwhlGAT6y3ua+P/FOjOiLWocisI="
if (VerifyHash(tbluser.UserPass, user.UserPass))
{
user.FirstName = tbluser.FirstName;
user.LastName = tbluser.LastName;
user.Email = tbluser.Email;
user.Id = tbluser.Id;
user.GroceryList = UserProductManager.LoadByUserId(tbluser.Id);
return(true);
}
else
{
// return false;
throw new Exception("Login Failed!"); // Cannot log in with these credentials
}
}
else
{
//return false;
throw new Exception("Login Failed!"); // User not found
}
}
}
else
{
throw new Exception("Password was not set!");
}
}
else
{
throw new Exception("Email was not set!"); // This outputs to the view so I changed it to "Email"
}
}
public static int UpdateGroceryList(User user)
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblUser tbluser = dc.tblUsers.FirstOrDefault(u => u.Id == u.Id);
if (tbluser != null)
{
tbluser.FirstName = user.FirstName;
tbluser.LastName = user.LastName;
tbluser.Email = user.Email;
tbluser.UserPass = user.UserPass;
// Delete all the tblUserProduct rows and add back in.
var existing = dc.tblUserProducts.Where(up => up.UserId == user.Id);
dc.tblUserProducts.RemoveRange(existing);
List <tblUserProduct> tblUserProducts = new List <tblUserProduct>();
// Also make sense of the tblUserProducts
foreach (UserProduct up in user.GroceryList)
{
tblUserProduct tbluserproduct = new tblUserProduct();
tbluserproduct.Id = Guid.NewGuid();
tbluserproduct.UserId = user.Id;
tbluserproduct.ProductId = up.Id;
tbluserproduct.InCart = up.InCart;
tbluserproduct.Amount = up.Amount;
tbluserproduct.Notes = up.Notes;
tblUserProducts.Add(tbluserproduct);
}
dc.tblUserProducts.AddRange(tblUserProducts);
int result = dc.SaveChanges();
return(result);
}
else
{
throw new Exception("Row was not found!!");
}
}
}
// Check to see if a user's grocery list includes a product
public static UserProduct SearchGroceryByProduct(Guid userId, string productTitle)
{
try
{
List <UserProduct> userProducts = new List <UserProduct>();
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
var results = (from up in dc.tblUserProducts
join p in dc.tblProducts on up.ProductId equals p.Id
where up.UserId == userId && p.Title == productTitle
orderby productTitle
select new
{
up.Id,
up.UserId,
up.ProductId,
p.Title,
up.Amount,
up.Notes,
up.InCart
}).ToList();
foreach (var up in results)
{
UserProduct userProduct = new UserProduct();
userProduct.Id = up.Id;
userProduct.UserId = up.UserId;
userProduct.ProductId = up.ProductId;
userProduct.ProductTitle = up.Title;
userProduct.Amount = up.Amount;
userProduct.Notes = up.Notes;
userProduct.InCart = up.InCart;
userProducts.Add(userProduct);
}
return(userProducts.FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static int Update(Aisle aisle)
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblAisle tblaisle = dc.tblAisles.FirstOrDefault(a => a.Id == aisle.Id);
if (tblaisle != null)
{
tblaisle.LayoutId = aisle.LayoutId;
tblaisle.Number = aisle.Number;
tblaisle.Lineup = aisle.Lineup;
return(dc.SaveChanges());
}
else
{
return(0);
}
}
}
public static bool Insert(Store store)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblStore newrow = new tblStore();
newrow.Id = Guid.NewGuid();
newrow.Title = store.Title;
dc.tblStores.Add(newrow);
dc.SaveChanges();
store.Id = newrow.Id;
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static bool Update(User user)
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblUser updatedrow = dc.tblUsers.Where(c => c.Id == user.Id).FirstOrDefault();
if (updatedrow != null)
{
updatedrow.FirstName = user.FirstName;
updatedrow.LastName = user.LastName;
updatedrow.Email = user.Email;
updatedrow.UserPass = CreateHash(user.UserPass);
dc.SaveChanges();
return(true);
}
else
{
throw new Exception("Row was not found!");
}
}
}
public static int Insert(AisleProduct aisleProduct)
{
try
{
using (GroceryGetterEntities dc = new GroceryGetterEntities())
{
tblAisleProduct newrow = new tblAisleProduct();
newrow.Id = Guid.NewGuid();
newrow.AisleId = aisleProduct.AisleId;
newrow.ProductId = aisleProduct.ProductId;
dc.tblAisleProducts.Add(newrow);
return(dc.SaveChanges());
}
}
catch (Exception ex)
{
throw ex;
}
}
