public List <OrderLineOption> Search(int orderId)
{
using (CafeKioskEntities context = (CafeKioskEntities)DbContextCreator.Context())
{
List <int> orderLineIds = context
.OrderLines
.Where(x => x.OrderID == orderId)
.Select(x => x.OrderLineID)
.ToList();
var query = from x in context.OrderLineOptions
where orderLineIds.Contains(x.OrderLineID)
select new
{
OrderLineOption = x,
OptionName = x.Option.Name,
MenuName = x.OrderLine.Menu.Name,
MenuPrice = x.OrderLine.Menu.Price,
};
var list = query.ToList();
foreach (var item in list)
{
item.OrderLineOption.Option.Name = item.OptionName;
item.OrderLineOption.OrderLine.Menu.Name = item.MenuName;
item.OrderLineOption.OrderLine.Menu.Price = item.MenuPrice;
}
return(list.ConvertAll(x => x.OrderLineOption).ToList());
}
}
public List <YearlyBrandModel> GetBrand(DateTime startDate, DateTime endDate)
{
using (var context = DbContextCreator.Create())
{
var dataQuery = from x in context.SalesLines
where x.Sale.SelledAt >= startDate && x.Sale.SelledAt <= endDate
select new
{
brandName = x.Product.Brand.BrandName,
selledAt = x.Sale.SelledAt,
totalPrice = x.Sale.TotalPrice
};
var dataList = dataQuery.ToList();
var groupQuery = (from p in dataQuery
where p.selledAt.Year >= 2018 && p.selledAt.Year <= 2020
select new
{
BrandName = p.brandName,
TotalPrice = p.totalPrice
}).GroupBy(p => p.BrandName).
Select(group => new { group.FirstOrDefault().BrandName, TotalPrice = group.Sum(p => p.TotalPrice) }).OrderByDescending(p => p.TotalPrice);
var groupList = groupQuery.ToList();
List <YearlyBrandModel> model = new List <YearlyBrandModel>();
foreach (var x in groupList)
{
model.Add(new YearlyBrandModel(x.BrandName, x.TotalPrice));
}
return(model);
}
}
public List <Expense> GetExpenses()
{
using (DeskAssemblyEntities context = DbContextCreator.Create())
{
//Expense테이블에서 가져옴(이걸로 끝) 3건은 이걸로!!!!
List <Expense> expenses = context.Expenses.ToList();
//자원구매비는 이걸로 계산,x=order
var query = from x in context.Orders
where x.IsSale == false
select new { Date = x.Date, Cost = x.Quantity * x.Item.Price };
//갯수보기위한식(없어도됨)
var list = query.ToList();
//x=이름없는익명타입(여기안에는 cost,date라는 속성이있음)
foreach (var x in list)
{
Expense resourceExpense = new Expense();
resourceExpense.Name = "자원구매비";
resourceExpense.Date = x.Date;
resourceExpense.Cost = x.Cost;
expenses.Add(resourceExpense);
}
return(expenses);
}
}
//국가별 부품 구매량 상세 모델
//public List<MapChartDetailModel> GetPurchasedCountryDetailModels(int countryId)
//{
// using (var context = DbContextCreator.Create())
// {
// var Orders = context.Orders.ToList();
// var query = from x in context.Orders
// where x.Contract.CountryId == countryId && x.IsSale == false
// select new { Quantity = x.Quantity, ContractName = x.Contract.Name, ItemName = x.Item };
// }
//}
public List <Order> Search()
{
using (DeskAssemblyEntities context = DbContextCreator.Create())
{
var query = from x in context.Orders
select new
{
Order = x,
ItemName = x.Item.Name,
TeamName = x.Team.Name,
ContractName = x.Contract.Name,
IsSaleName = x.IsSale
};
var list = query.ToList();
foreach (var item in list)
{
item.Order.ItemName = item.ItemName;
item.Order.TeamName = item.TeamName;
item.Order.ContractName = item.ContractName;
if (item.Order.IsSale == true)
{
item.Order.IsSaleName = "판매";
}
else
{
item.Order.IsSaleName = "구매";
}
}
return(list.Select(x => x.Order).ToList());
}
}
public List <Order> Pivot()
{
using (DeskAssemblyEntities context = DbContextCreator.Create())
{
var query = from x in context.Orders
select new
{
Order = x,
ItemName = x.Item.Name,
ContractName = x.Contract.Name,
CountryName = x.Contract.Country.Name,
ItemPrice = x.Item.Price
};
var list = query.ToList();
List <Order> orders = new List <Order>();
foreach (var x in list)
{
x.Order.ItemName = x.ItemName;
x.Order.ContractName = x.ContractName;
x.Order.CountryName = x.CountryName;
x.Order.ItemPrice = x.ItemPrice;
orders.Add(x.Order);
}
return(orders);
}
}
//국가별 제품 판매량 모델
public List <MapChartModel> GetSoldCountryModels()
{
using (var context = DbContextCreator.Create())
{
var countries = context.Countries.ToList();
List <MapChartModel> models = new List <MapChartModel>();
foreach (Country country in countries)
{
var query = from x in context.Orders
where x.Contract.CountryId == country.CountryId && x.IsSale == true
select x.Quantity;
var list = query.ToList();
MapChartModel model = new MapChartModel();
model.Value = list.Sum();
model.Latitude = country.Latitude;
model.Longitude = country.Longitude;
models.Add(model);
}
return(models);
}
}
public List <Salemodel2> GetSaleModels()
{
{
using (var context = DbContextCreator.Create())
{
var productNames = context.Items.ToDictionary(x => x.ItemId, x => x.Name);
var query = from p in context.Orders
where p.IsSale == true
select new
{
Order = p,
Quantity = p.Quantity,
ProductName = p.Item.Name,
ItemId = p.ItemId
};
var query2 = from x in query
group x by x.ItemId into g
select g;
var models = new List <Salemodel2>();
foreach (var @group in query2)
{
Salemodel2 model = new Salemodel2();
model.ItemId = group.Key;
model.Quantity = group.Sum(g => g.Quantity);
model.ProductName = productNames[group.Key];
models.Add(model);
}
return(models);
}
}
}
public async void PostsInCorrectOrder()
{
ApplicationDbContext context = DbContextCreator.CreateTestContext("PostsTestDbPostsInCorrectOrder");
string authenticatedUserId = "current-user-id";
var user = CreateDummyUser(authenticatedUserId);
var category = CreateDummyCategory(1);
context.Users.Add(user);
context.Categories.Add(category);
await context.SaveChangesAsync();
var postsController = CreatePostsController(context, authenticatedUserId);
var postOld = CreateDummyPost(user.Id, category.Id);
var postNew = CreateDummyPost(user.Id, category.Id);
await postsController.PostPost(postOld);
await postsController.PostPost(postNew);
var response = await postsController.GetPosts(null, null, null);
var listIds = new List <Nullable <int> >();
foreach (var post in response.Value)
{
var dictionary = new RouteValueDictionary(post);
listIds.Add(dictionary["Id"] as Nullable <int>);
}
Assert.Equal(postNew.Id, listIds.First());
}
public Dictionary <string, int> ToDictionary()
{
using (var context = DbContextCreator.Create())
{
return(context.Libraries.ToDictionary(x => x.Location, x => x.LibraryId));
}
}
/// <summary>
/// 시간당 체크 데이터 리스트 메소드
/// </summary>
/// <param name="day"></param>
/// <returns></returns>
public List <PeriodSummary> DaySearch(DateTime day)
{
using (var context = DbContextCreator.Create())
{
DateTime @from = day.Date; //자정으로 임의의 날짜를 만듦. from은 키워드이기때문에 '@'가 들어간다.
DateTime to = from.AddDays(1); //임의의 날짜 다음날(자정).
var query = from x in context.Rents
//where x.RentDate >= @from && x.RentDate < to
select x; //하루동안의 렌트 기록을 뽑는다.
var list = query.ToList(); //리스트로 만들기.
// RentedOn
var query2 = from x in list
group x by x.RentDate.Hour into HourGroup //리스트를 모아 한 그룹을 g라고 한다.
//select new ThePeriod { Hour = g.Key, Count = g.Count()};
select HourGroup;
//return query2.ToList();
List <PeriodSummary> periods = new List <PeriodSummary>();
foreach (var g in query2)
{
PeriodSummary period = new PeriodSummary();
period.Value = g.Key; //렌트기록중에 '시'만 뽑는다.
period.Count = g.Count(); //ex) 2020.12.18. 14:58:12에 대여기록이 있다면 '14'
periods.Add(period);
}
return(periods);
}
}
public List <OriginModel> GetModels2()
{
using (MesEntities context = (MesEntities)DbContextCreator.Create())
{
Dictionary <int, string> originName = context.Origins.ToDictionary(x => x.OriginId, x => x.Name);
Dictionary <int, double> longgitud = context.Origins.ToDictionary(x => x.OriginId, x => x.Longgitude_Member);
Dictionary <int, double> latitude = context.Origins.ToDictionary(x => x.OriginId, x => x.Latitude_Member);
var query = from x in context.Transactions //Transaction 요소를 모두 가져와
group x by x.OriginId into g //OriginId별로 정렬
select g;
List <OriginModel> models = new List <OriginModel>();
foreach (var @group in query)
{
OriginModel model = new OriginModel(group.Key, group.Sum(x => x.Quantity));//key는 위에서 만든 g의 ResourceId
model.OriginName = originName[model.OriginId];
model.Longgitude = longgitud[model.OriginId];
model.Latitude = latitude[model.OriginId];
models.Add(model);
}
return(models);
}
}
public async void CanLikeAndUnlikeAPost()
{
ApplicationDbContext context = DbContextCreator.CreateTestContext("CanLikeAndUnlikeAPost");
string authenticatedUserId = "current-user-id";
var category = CreateDummyCategory(1);
var post = CreateDummyPost(authenticatedUserId, category.Id);
context.Categories.Add(category);
context.Posts.Add(post);
await context.SaveChangesAsync();
var postsController = CreatePostsController(context, authenticatedUserId);
var initialLikeCount = (from like in context.Likes where like.PostId == post.Id select like.Id).Count();
Assert.Equal(0, initialLikeCount);
await postsController.PutPostLike(post.Id, post.ApplicationUserId);
var likeCountAfterLike = (from like in context.Likes where like.PostId == post.Id select like.Id).Count();
Assert.Equal(1, likeCountAfterLike);
await postsController.PutPostUnlike(post.Id, post.ApplicationUserId);
var likeCountAfterUnlike = (from like in context.Likes where like.PostId == post.Id select like.Id).Count();
Assert.Equal(0, initialLikeCount);
}
public List <RankSummary> NewBookRank()
{
DateTime firstDay = DateTime.Today.AddMonths(-3);
DateTime lastDay = DateTime.Today; //Time으로 돌렸다가 다시 Today설정.
using (var context = DbContextCreator.Create())
{
var query = from x in context.Books
where x.PublicationDate >= firstDay && x.PublicationDate <= lastDay
let newBookCount = x.Rents.Count()
orderby newBookCount descending
select new
{
Title = x.Title,
NewBookCount = newBookCount
};
var list = query.Take(10).ToList();
return(list.ConvertAll(x => new RankSummary {
Title = x.Title, NewBookRank = x.NewBookCount
}));
}
}
public void UpdateTransaction(List <string> list)
{
int id = Convert.ToInt32(list[0]);
DateTime datetime = Convert.ToDateTime(list[2]);
int type = (list[5] == "출고") ? 0 : 1;
using (var context = DbContextCreator.Create())
{
var result = context.Transactions.SingleOrDefault(x => x.ResourceId == id &&
x.Date == datetime &&
x.Type == type);
if (result != null)
{
result.ResourceId = Convert.ToInt32(list[0]);
result.SellerName = list[1];
result.Date = Convert.ToDateTime(list[2]);
//result.Origin = list[3];
result.EmployeeId = Convert.ToInt32(list[4]);
result.Type = (list[5] == "출고") ? 0 : 1;
result.ResourceWareHouseId = Convert.ToInt32(list[6]);
}
context.SaveChanges();
}
}
public List <VeganBrandModel> VeganSalesPerYear(int year)
{
using (var context = DbContextCreator.Create())
{
DateTime firstDay = DateTime.Today.AddYears(year * -1);
DateTime lastDay = DateTime.Today;
var query = from x in context.SalesLines
where x.Sale.SelledAt >= firstDay && x.Sale.SelledAt <= lastDay &&
x.Product.Brand.BrandTag == 0
select new { SelledAt = x.Sale.SelledAt, Quantity = x.Quantity };
var list = query.ToList();
var query2 = from x in list
group x by x.SelledAt.Year into g
select new VeganBrandModel
{
Year = g.Key,
Quantity = g.Sum(y => y.Quantity)
};
return(query2.ToList());
}
}
public List <ProductsaledetailModel> GetContractorByItemId(int ItemId)
{
using (var context = DbContextCreator.Create())
{
List <ProductsaledetailModel> smodels = new List <ProductsaledetailModel>();
var contractNames = context.Contracts.ToDictionary(x => x.ContractId, x => x.Name);
var query = from x in context.Orders
where x.ItemId == ItemId //&& x.IsSale == true && x.Contract.IsVendee == true
select x;
var items = query.ToList();
foreach (Order item in items)
{
ProductsaledetailModel smodel = new ProductsaledetailModel();
item.SaleQuantitySum = GetSaleQuantitySum(item.ContractId);
smodel.Quantity = item.SaleQuantitySum;
smodel.ContractId = item.ContractId;
smodel.VendeeName = item.Contract.Name;
smodels.Add(smodel);
}
return(smodels);
}
}
public List <ProductSalesQuantityModel2> GetModels()
{
{
using (var context = DbContextCreator.Create())
{
var productNames = context.Products.ToDictionary(x => x.ProductId, x => x.Name);
var query = from p in context.ProductDetails
select new { Amounts = p.Sales.Sum(s => s.Amount), ProductId = p.ProductId };
var query2 = from x in query
group x by x.ProductId into g
select g;
var models = new List <ProductSalesQuantityModel2>();
foreach (var group in query2)
{
ProductSalesQuantityModel2 model = new ProductSalesQuantityModel2();
model.ProductId = group.Key;
model.Amount = group.Sum(g => g.Amounts);
model.ProductName = productNames[group.Key];
models.Add(model);
}
return(models);
}
}
}
public async void GetAllFollowers()
{
// Arrange
#region context preperation
var context = DbContextCreator.CreateTestContext("UsersTestDbGetAllFollowers");
#endregion
#region data preperation
ApplicationUser follower = PutOneUserInDb(context, "first", "john");
ApplicationUser followee = PutOneUserInDb(context, "second", "oh Hi mark");
context.Followers.Add(new FollowerFollowee {
FolloweeId = followee.Id, FollowerId = follower.Id
});
context.SaveChanges();
#endregion
//Act
UsersController usersController = CreateUsersController(context, follower.Id);
var users = await usersController.GetAllFollowers(followee.Id);
//Assert
List <string> listIds = new List <string>();
foreach (var user in users.Value)
{
var dictionary = new RouteValueDictionary(user);
listIds.Add(dictionary["Id"] as string);
}
Assert.Equal(follower.Id, listIds.First());
Assert.Single(listIds);
}
public async void UserIsUpdated()
{
// Arrange
#region context preperation
var context = DbContextCreator.CreateTestContext("UsersTestDbUserIsUpdated");
#endregion
#region data preperation
ApplicationUser user = PutOneUserInDb(context);
var updatedUser = new ApplicationUser
{
Id = user.Id,
UserName = "******",
Description = "Need a will? Call McGill.",
};
#endregion
// Act
UsersController usersController = CreateUsersController(context, updatedUser.Id);
var result = await usersController.PutApplicationUser(updatedUser);
// Assert
var storedData = await context.Users.SingleAsync(u => u.Id == user.Id);
Assert.Equal(updatedUser.UserName, storedData.UserName);
Assert.Equal(updatedUser.Description, storedData.Description);
var actualUser = await usersController.GetUser(user.Id);
Assert.NotNull(actualUser);
}
async public void EmptySearchPhrasesReturnNothing()
{
var context = DbContextCreator.CreateTestContext("SearchTestDBEmptySearchPhrasesAreHandled");
var user = new ApplicationUser
{
Email = "*****@*****.**",
UserName = "******",
Description = "I love fitness",
Id = "jimmy-id"
};
context.Add(user);
await context.SaveChangesAsync();
SearchController searchController = new SearchController(context);
var foundUsers = await searchController.GetSearchedUsers(null);
var hasNoResults = true;
foreach (var foundUser in foundUsers.Value)
{
hasNoResults = false;
}
Assert.True(hasNoResults);
}
public static List <PopulationOfFemalePassenger> GetPopulationOfFemalePassenger(int year, int count = 225)
{
DateTime @from = new DateTime(year, 1, 1);
DateTime to = from.AddYears(1);
using (CosmeticFinalEntities context = (CosmeticFinalEntities)DbContextCreator.Create())
{
var query = (from x in context.PassengerInfoes
where x.Date >= @from && x.Date <= to
group x by new
{
x.StationId,
} into g
select new
{
Id = g.Key.StationId,
Year = g.FirstOrDefault().Date.Year,
Name = g.FirstOrDefault().Station.Name,
PassengerNumber = g.Sum(x => x.PopulationPerMonth)
}).OrderByDescending(x => x.PassengerNumber).Take(count);
var list = query.ToList();
List <PopulationOfFemalePassenger> listPopulation = new List <PopulationOfFemalePassenger>();
foreach (var x in list)
{
listPopulation.Add(new PopulationOfFemalePassenger(x.Year, x.PassengerNumber, x.Id, x.Name));
}
return(listPopulation);
}
}
public List <Purcahselistmodel> GetItemsByCategoryId(int categoryId)
{
using (var context = DbContextCreator.Create())
{
List <Purcahselistmodel> models = new List <Purcahselistmodel>();
var itemNames = context.Items.ToDictionary(x => x.ItemId, x => x.Name);
var query = from x in context.Items
where x.CategoryId == categoryId
select x;
var items = query.ToList();
foreach (Item item in items)
{
Purcahselistmodel model = new Purcahselistmodel();
item.QuantitySum = GetQuantitySum(item.ItemId);
model.Quantity = item.QuantitySum;
model.ItemId = item.ItemId;
model.PartName = item.Name;
models.Add(model);
}
return(models);
}
}
async public void UsersCanBeSearched()
{
//Arrange
#region context preperation
var context = DbContextCreator.CreateTestContext("SearchTestDB");
#endregion
#region data preparation
var userOne = new ApplicationUser
{
Email = "*****@*****.**",
UserName = "******",
Description = "Bodybuilding guy",
Id = "specificusername-id"
};
context.Users.Add(userOne);
var userTwo = new ApplicationUser
{
Email = "*****@*****.**",
UserName = "******",
Description = "The best fitness account",
Id = "veryspecificusername-id"
};
context.Users.Add(userTwo);
await context.SaveChangesAsync();
#endregion
//Act
SearchController searchController = new SearchController(context);
string searchQueryOne = "VerySpecificUsername999";
string searchQueryTwo = "specificusername0 ";
var resultOne = searchController.GetSearchedUsers(searchQueryOne);
var resultTwo = searchController.GetSearchedUsers(searchQueryTwo);
bool[] arrayOne = SearchUserLoop(searchQueryOne, resultOne);
bool onlyOneUserInFirstResult = arrayOne[0];
bool specificUserInFirstResult = arrayOne[1];
bool[] arrayTwo = SearchUserLoop(searchQueryTwo, resultTwo);
bool onlyOneUserInSecondResult = arrayTwo[0];
bool specificUserInSecondResult = arrayTwo[1];
//Assert
Assert.True(onlyOneUserInFirstResult);
Assert.True(specificUserInFirstResult);
Assert.True(onlyOneUserInSecondResult);
Assert.True(specificUserInSecondResult);
}
public void Delete(string strOrderId)
{
using (var context = DbContextCreator.Create())
{
Order order = context.Orders.Find(Convert.ToInt32(strOrderId));
context.Orders.Remove(order);
context.SaveChanges();
}
}
public void Delete(string strWareHouseId)
{
using (var context = DbContextCreator.Create())
{
ProductWareHouse wareHouse = context.ProductWareHouses.Find(Convert.ToInt32(strWareHouseId));
context.ProductWareHouses.Remove(wareHouse);
context.SaveChanges();
}
}
public static List <CovidMonth> GetById()
{
using (var context = DbContextCreator.Create())
{
var query = from x in context.CovidMonths
select x;
return(query.ToList());
}
}
public static List <Brand> GetBrands()
{
using (CosmeticFinalEntities context = (CosmeticFinalEntities)DbContextCreator.Create())
{
var query = from x in context.Brands
select x;
return(query.ToList()); // ToList() 함수가 호출될 때 쿼리가 실제로 날아간다(실행된다).
}
}
public List <string> GetTeamNames()
{
using (var context = DbContextCreator.Create())
{
var query = (from x in context.Employees
select x.Team).Distinct();
return(query.ToList());
}
}
public async void CantGetNonExistingPostById()
{
ApplicationDbContext context = DbContextCreator.CreateTestContext("PostsTestDbGetNonExistingPostById");
string authenticatedUserId = "current-user-id";
var postsController = CreatePostsController(context, authenticatedUserId);
var response = await postsController.GetPost(2);
Assert.IsAssignableFrom <NotFoundResult>(response.Result);
}
public List <InformationNearStation> GetCompetitorStoreNumber(int stationId)
{
using (CosmeticFinalEntities context = DbContextCreator.Create())
{
var query = from x in context.InformationNearStations
where x.StationId == stationId
select x;
return(query.ToList());
}
}
