Ejemplo n.º 1
0
        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());
            }
        }
Ejemplo n.º 2
0
        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);
            }
        }
Ejemplo n.º 3
0
        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);
            }
        }
Ejemplo n.º 4
0
        //국가별 부품 구매량 상세 모델
        //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());
            }
        }
Ejemplo n.º 5
0
        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);
            }
        }
Ejemplo n.º 6
0
        //국가별 제품 판매량 모델
        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);
            }
        }
Ejemplo n.º 7
0
        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);
                }
            }
        }
Ejemplo n.º 8
0
        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());
        }
Ejemplo n.º 9
0
 public Dictionary <string, int> ToDictionary()
 {
     using (var context = DbContextCreator.Create())
     {
         return(context.Libraries.ToDictionary(x => x.Location, x => x.LibraryId));
     }
 }
Ejemplo n.º 10
0
        /// <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);
            }
        }
Ejemplo n.º 12
0
        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);
        }
Ejemplo n.º 13
0
        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();
            }
        }
Ejemplo n.º 15
0
        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());
            }
        }
Ejemplo n.º 16
0
        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);
            }
        }
Ejemplo n.º 17
0
        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);
                }
            }
        }
Ejemplo n.º 18
0
        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);
        }
Ejemplo n.º 19
0
        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);
        }
Ejemplo n.º 20
0
        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);
        }
Ejemplo n.º 21
0
        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);
            }
        }
Ejemplo n.º 22
0
        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);
            }
        }
Ejemplo n.º 23
0
        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);
        }
Ejemplo n.º 24
0
 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();
     }
 }
Ejemplo n.º 26
0
        public static List <CovidMonth> GetById()
        {
            using (var context = DbContextCreator.Create())
            {
                var query = from x in context.CovidMonths
                            select x;

                return(query.ToList());
            }
        }
Ejemplo n.º 27
0
        public static List <Brand> GetBrands()
        {
            using (CosmeticFinalEntities context = (CosmeticFinalEntities)DbContextCreator.Create())
            {
                var query = from x in context.Brands
                            select x;

                return(query.ToList()); // ToList() 함수가 호출될 때 쿼리가 실제로 날아간다(실행된다).
            }
        }
Ejemplo n.º 28
0
        public List <string> GetTeamNames()
        {
            using (var context = DbContextCreator.Create())
            {
                var query = (from x in context.Employees
                             select x.Team).Distinct();

                return(query.ToList());
            }
        }
Ejemplo n.º 29
0
        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);
        }
Ejemplo n.º 30
0
 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());
     }
 }