/*---------------------------------*/
public Chain GetChain(string chain_name)
{
using (var db = new PricesContext())
{
var chain = db.Chains.Where(s => s.Name.Equals(chain_name)).FirstOrDefault();
return(chain);
}
}
/*---------------------------------*/
public IEnumerable <Store> GetStoresOfChain(string chain_name)
{
using (var db = new PricesContext())
{
var list = db.Stores.Where((c) => c.Chain.Name.Equals(chain_name)).ToList();
return(list);
}
}
/*---------------------------------*/
public Store GetStore(string chain_name, string store_name)
{
using (var db = new PricesContext())
{
var store = db.Stores.Where(s => s.Chain.Name.Equals(chain_name) && s.Name.Equals(store_name)).FirstOrDefault();
return(store);
}
}
/*---------------------------------*/
public List <Price> GetPrices(Item item)
{
using (var db = new PricesContext())
{
var prices = db.Prices.Where(i => i.Item.ItemCode.Equals(item.ItemCode));
return(prices.ToList());
}
}
/*---------------------------------*/
public IEnumerable <string> GetCities()
{
using (var db = new PricesContext())
{
var cities = from s in db.Stores
select s.City;
return(cities.ToList());
}
}
public List <Chain> GetChains()
{
List <Chain> list = null;
using (var db = new PricesContext())
{
list = db.Chains.ToList();
return(list);
}
}
/*---------------------------------*/
//return the the most common items
/*
* public List<Item> GetCommonItems()
* {
* using (var db = new PricesContext())
* {
*
* var num_of_stores = (from s in db.Stores select new { s.StoreId, s.Chain.ChainNumber }).Distinct().Count();
*
* var mutualItemsInStores = from it in db.Items
* group it.ItemCode by it.StoreId into Count
* orderby Count.Count()
* where Count.Count() == num_of_stores
* select (from it in db.Items
* where it.ItemCode.Equals(Count.Key)
* select it);
*
*
* //count appearance in stores
* var mutualItemsInStores = (from it in db.Items
*
* group it.Name by it.StoreId into Count
* orderby Count.Count() descending
* select new
* {
* Name = Count.Key,
* Count = Count.Count()
* });
*
* var biggestCount = mutualItemsInStores.Max(x => x.Count);
*
* var mostCommonItems = from it in mutualItemsInStores
* where it.Count == biggestCount
* select it.Name;
*
*
* return mutualItemsInStores.First().ToList();
*
* }
* }
* /*---------------------------------*/
//return prices of stores that contains all items
public List <Cart> GetFullCartPrices(List <ItemQuantity> items)
{
using (var db = new PricesContext())
{
var itemsCodes = items.Select(i => i.Item.ItemCode).ToList();
//join 'Store' with 'Price'
var joined = (from store in db.Stores
join p in db.Prices on new { ChainId = store.Chain.ChainNumber, store.StoreId } equals new { p.ChainId, p.StoreId }
where itemsCodes.Contains(p.Item.ItemCode)
select new
{
StorId = store.StoreId,
ChainName = store.Chain.Name,
StoreName = store.Name,
Item = p.Item,
Price = p.ItemPrice
});
var result = (from s in db.Stores
// where itemsObj.Except(it.Items).Count() == 0 //all the items exist in store
join p in db.Prices on new { ChainId = s.Chain.ChainNumber, s.StoreId } equals new { p.ChainId, p.StoreId }
where itemsCodes.Contains(p.Item.ItemCode)
group p by new { ChainName = s.Chain.Name, StoreName = s.Name }
into grp
select new Cart
{
ChainName = grp.Key.ChainName,
StoreName = grp.Key.StoreName,
Items = grp.Select(p => new ItemQuantity
{
Quantity = 0,
Item = p.Item,
Price = p.ItemPrice
})
.ToList()
}).ToList();
//multiple by quatity in cart
foreach (var r in result)
{
foreach (var i in r.Items)
{
i.Quantity = (from q in items
where q.Item.ItemCode.Equals(i.Item.ItemCode)
select q.Quantity).First();
}
r.CartPrice = r.Items.Sum(x => x.Price);
}
return(result);
}
}
/*---------------------------------*/
public Item GetItemByCode(string itemcode)
{
long code = Convert.ToInt64(itemcode);
using (var db = new PricesContext())
{
var item = from i in db.Items
where i.ItemCode == code
select i;
return(item.FirstOrDefault());
}
}
/*---------------------------------*/
public List <Item> GetItemsInStore(string chain_name, string store_name)
{
using (var db = new PricesContext())
{
var list = (from x in db.Stores
join p in db.Prices on new { x.StoreId, ChainId = x.Chain.ChainNumber } equals new { p.StoreId, p.ChainId }
where x.Name.Equals(store_name) && x.Chain.Name.Equals(chain_name)
select p.Item)
.ToList();
return(list);
}
}
/*---------------------------------*/
public Price GetPrice(Chain chain, Store store, string itemCode)
{
using (var db = new PricesContext())
{
var price = (from p in db.Prices
where p.Item.ItemCode.Equals(itemCode) &&
p.StoreId.Equals(store.StoreId) &&
p.ChainId.Equals(chain.ChainNumber)
select p).First();
return(price);
}
}