public static void CreateAlbum(Album album)
{
MusicStoreEntities db = new MusicStoreEntities();
db.Albums.Add(album);
db.SaveChanges();
}
public static void AddToCart(Album album, string ShoppingCartID)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
// Get the matching cart and album instances
var cartItem = db.Carts.SingleOrDefault(
c => c.CartId == ShoppingCartID &&
c.AlbumId == album.AlbumId);
if (cartItem == null)
{
// Create a new cart item if no cart item exists
cartItem = new Cart
{
AlbumId = album.AlbumId,
CartId = ShoppingCartID,
Count = 1,
DateCreated = DateTime.Now,
};
db.Carts.Add(cartItem);
}
else
{
// If the item does exist in the cart, then add one to the quantity
cartItem.Count++;
}
// Save changes
db.SaveChanges();
}
}
public static void EditAlbum(Album album)
{
MusicStoreEntities db = new MusicStoreEntities();
db.Entry(album).State = EntityState.Modified;
db.SaveChanges();
}
public static void AddOrder(Order order)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
db.Orders.Add(order);
SqlParameter OrderDate = new SqlParameter("@OrderDate", order.OrderDate);
SqlParameter Username = new SqlParameter("@Username", order.Username);
SqlParameter FirstName = new SqlParameter("@FirstName", order.FirstName);
SqlParameter LastName = new SqlParameter("@LastName", order.LastName);
SqlParameter Address = new SqlParameter("@Address", order.Address);
SqlParameter City = new SqlParameter("@City", order.City);
SqlParameter State = new SqlParameter("@State", order.State);
SqlParameter PostalCode = new SqlParameter("@PostalCode", order.PostalCode);
SqlParameter Country = new SqlParameter("@Country", order.Country);
SqlParameter Phone = new SqlParameter("@Phone", order.City);
SqlParameter Email = new SqlParameter("@Email", order.City);
SqlParameter Total = new SqlParameter("@Total", order.Total);
db.Database.ExecuteSqlCommand("InsertOrders @OrderDate,@Username,@FirstName,@LastName,@Address,@City,@State,@PostalCode,@Country,@Phone,@Email,@Total",
OrderDate, Username, FirstName, LastName, Address, City, State, PostalCode, Country, Phone, Email, Total);
//db.SaveChanges();
}
}
public static void EmptyCart()
{
//MusicStoreEntities db = new MusicStoreEntities();
//var cartItems = db.Carts.Where(cart => cart.CartId == ShoppingCartId);
//foreach (var cartItem in cartItems)
//{
// db.Carts.Remove(cartItem);
//}
//// Save changes
//db.SaveChanges();
using (MusicStoreEntities db = new MusicStoreEntities())
{
var cartItems = db.Carts.Where(cart => cart.CartId == ShoppingCartId);
foreach (var cartItem in cartItems)
{
db.Carts.Remove(cartItem);
}
// Save changes
db.SaveChanges();
}
}
public static List <Cart> GetCartItems(string ShoppingCartID)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
return(db.Carts.Include("Album").Where(cart => cart.CartId == ShoppingCartID).ToList());
}
}
// Retrieve Genre and its Associated Albums from database
public static Genre BrowseGenre(string genre)
{
MusicStoreEntities db = new MusicStoreEntities();
//return db.Genres.Single(g => g.Name == genre);
return(db.Genres.Include("Albums").Single(g => g.Name == genre));
}
public static int RemoveFromCart(string ShoppingCartID, int id)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
// Get the cart
var cartItem = db.Carts.Single(
cart => cart.CartId == ShoppingCartID &&
cart.RecordId == id);
int itemCount = 0;
if (cartItem != null)
{
if (cartItem.Count > 1)
{
cartItem.Count--;
itemCount = cartItem.Count;
}
else
{
db.Carts.Remove(cartItem);
}
// Save changes
db.SaveChanges();
}
return(itemCount);
}
}
public static IEnumerable <Genre> GetTopSellingGenres(int count)
{
MusicStoreEntities db = new MusicStoreEntities();
return(db.Genres.OrderByDescending(a => a.GenreId)
.Take(count)
.ToList());
}
public static void AddOrder(Order order)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
db.Orders.Add(order);
db.SaveChanges();
}
}
// Retrieve Genre and its Associated Albums from database
public static Genre BrowseGenre(string genre)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
//return db.Database.SqlQuery<Genre>("GetAllGenres").Include("Albums").Single(g => g.Name == genre);
return(db.Genres.Include("Albums").Single(g => g.Name == genre));
}
}
public static Album FindAlbum(int id)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
return(db.Albums.Find(id));
}
//MusicStoreEntities db = new MusicStoreEntities();
//return db.Albums.Find(id);
}
public static IEnumerable <Genre> GetTopSellingGenres(int count)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
return(db.Database.SqlQuery <Genre>("GetAllGenres").OrderByDescending(a => a.GenreId)
.Take(count)
.ToList());;
}
}
public static IEnumerable <Genre> GetAllGenreList()
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
return(db.Database.SqlQuery <Genre>("GetAllGenres").ToList());
//return db.Genres.ToList<Genre>();
}
}
public static IEnumerable <Album> GetAllStoreMangerList()
{
//MusicStoreEntities db = new MusicStoreEntities();
//return db.Albums.Include(a => a.Genre).Include(a => a.Artist);
using (MusicStoreEntities db = new MusicStoreEntities())
{
return(db.Albums.Include(a => a.Genre).Include(a => a.Artist));
}
}
public static IEnumerable <Album> GetAllAlbumList()
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
return(db.Database.SqlQuery <Album>("GetAllAlbums").ToList());
}
//MusicStoreEntities db = new MusicStoreEntities();
//return db.Albums.ToList<Album>();
}
public static bool IsValid(int id, string userName)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
bool isValid = db.Orders.Any(
o => o.OrderId == id &&
o.Username == userName);
return(isValid);
}
}
// When a user has logged in, migrate their shopping cart to
// be associated with their username
public static void MigrateCart(string userName)
{
MusicStoreEntities db = new MusicStoreEntities();
var shoppingCart = db.Carts.Where(c => c.CartId == ShoppingCartId);
foreach (Cart item in shoppingCart)
{
item.CartId = userName;
}
db.SaveChanges();
}
public static decimal GetTotal(string ShoppingCartID)
{
MusicStoreEntities db = new MusicStoreEntities();
// Multiply album price by count of that album to get
// the current price for each of those albums in the cart
// sum all album price totals to get the cart total
decimal?total = (from cartItems in db.Carts
where cartItems.CartId == ShoppingCartID
select(int?) cartItems.Count *cartItems.Album.Price).Sum();
return(total ?? decimal.Zero);
}
public static int GetCount()
{
MusicStoreEntities db = new MusicStoreEntities();
// Get the count of each item in the cart and sum them up
int?count = (from cartItems in db.Carts
where cartItems.CartId == ShoppingCartId
select(int?) cartItems.Count).Sum();
// Return 0 if all entries are null
return(count ?? 0);
}
public static void CreateAlbum(Album album)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
SqlParameter GenreId = new SqlParameter("@GenreId", album.GenreId);
SqlParameter ArtistId = new SqlParameter("@ArtistId", album.ArtistId);
SqlParameter Title = new SqlParameter("@Title", album.Title);
SqlParameter Price = new SqlParameter("@Price", album.Title);
SqlParameter AlbumArtUrl = new SqlParameter("@AlbumArtUrl", album.AlbumArtUrl);
db.Database.ExecuteSqlCommand("InsertAlbum @GenreId,@ArtistId,@Title,@Price,@AlbumArtUrl", GenreId, ArtistId, Title, Price, AlbumArtUrl);
}
//db.Albums.Add(album);
//db.SaveChanges();
}
public static Album GetAlbum(int id)
{
using (MusicStoreEntities db = new MusicStoreEntities())
{
// Retrieve the album from the database
//var addedAlbum = db.Albums
// .Single(album => album.AlbumId == id);
//var addedAlbum = db.Carts.Where(c => c.RecordId == id);
var addedAlbum = (from cartItems in db.Carts
where cartItems.RecordId == id
select cartItems.Album).Single();
return(addedAlbum);
}
}
public static int CreateOrder(Order order, string ShoppingCartID)
{
MusicStoreEntities db = new MusicStoreEntities();
decimal orderTotal = 0;
var cartItems = GetCartItems(ShoppingCartID);
// Iterate over the items in the cart, adding the order details for each
foreach (var item in cartItems)
{
var orderDetail = new OrderDetail
{
AlbumId = item.AlbumId,
OrderId = order.OrderId,
UnitPrice = item.Album.Price,
Quantity = item.Count
};
// Set the order total of the shopping cart
orderTotal += (item.Count * item.Album.Price);
//db.OrderDetails.Add(orderDetail);
SqlParameter CartId = new SqlParameter("@CartId", item.CartId);
SqlParameter AlbumId = new SqlParameter("@AlbumId", item.AlbumId);
SqlParameter Count = new SqlParameter("@Count", item.Count);
SqlParameter DateCreated = new SqlParameter("@DateCreated", item.DateCreated);
db.Database.ExecuteSqlCommand("InsertCarts @CartId,@AlbumId,@Count,@DateCreated", CartId, AlbumId, Count, DateCreated);
}
// Set the order's total to the orderTotal count
order.Total = orderTotal;
// Save the order
//db.SaveChanges();
// Empty the shopping cart
EmptyCart();
// Return the OrderId as the confirmation number
return(order.OrderId);
}
public static IEnumerable <Album> GetTopSellingAlbums(int count)
{
MusicStoreEntities db = new MusicStoreEntities();
var rowcount = db.Albums
.OrderByDescending(a => a.OrderDetails.Count())
.Take(count)
.ToList().Count();
if (rowcount > 0)
{
return(db.Albums
.OrderByDescending(a => a.OrderDetails.Count())
.Take(count)
.ToList());
}
else
{
return(db.Albums
.Take(count)
.ToList());
}
}
public static int CreateOrder(Order order, string ShoppingCartID)
{
MusicStoreEntities db = new MusicStoreEntities();
decimal orderTotal = 0;
var cartItems = GetCartItems(ShoppingCartID);
// Iterate over the items in the cart, adding the order details for each
foreach (var item in cartItems)
{
var orderDetail = new OrderDetail
{
AlbumId = item.AlbumId,
OrderId = order.OrderId,
UnitPrice = item.Album.Price,
Quantity = item.Count
};
// Set the order total of the shopping cart
orderTotal += (item.Count * item.Album.Price);
db.OrderDetails.Add(orderDetail);
}
// Set the order's total to the orderTotal count
order.Total = orderTotal;
// Save the order
db.SaveChanges();
// Empty the shopping cart
EmptyCart();
// Return the OrderId as the confirmation number
return(order.OrderId);
}
public static IEnumerable <Album> GetAllAlbumList()
{
MusicStoreEntities db = new MusicStoreEntities();
return(db.Albums.ToList <Album>());
}
public static IEnumerable <Artist> GetAllArtistList()
{
MusicStoreEntities db = new MusicStoreEntities();
return(db.Artists.ToList <Artist>());
}
public static IEnumerable <Genre> GetAllGenreList()
{
MusicStoreEntities db = new MusicStoreEntities();
return(db.Genres.ToList <Genre>());
}
public static Album FindAlbum(int id)
{
MusicStoreEntities db = new MusicStoreEntities();
return(db.Albums.Find(id));
}