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()); } }