public static List <SalesVolumeMonthCategoryModel> GetSalesVolumeByDayCategory(DateTime day)
        {
            DateTime start = day;
            DateTime end   = start.AddDays(7);

            using (var context = new CosmeticSolutionSystemEntities())
            {
                //context.Database.Log = (log) => Debug.WriteLine(log);

                var queryX = from x in context.Categories
                             orderby x.CategoryName
                             select x.CategoryName;

                int i = 0;

                var listX = queryX.ToList();

                foreach (var x in listX)
                {
                    categoryArr[i] = x.ToString();
                    i++;
                }

                var query = (from x in context.SalesLines
                             where DbFunctions.TruncateTime(x.Sale.SelledAt) >= DbFunctions.TruncateTime(start) &&
                             DbFunctions.TruncateTime(x.Sale.SelledAt) <= DbFunctions.TruncateTime(end)
                             select new
                {
                    Date = x.Sale.SelledAt,
                    CategoryName = x.Product.Category.CategoryName,
                    Quantity = x.Quantity
                }).GroupBy(x => new { x.Date.Day, x.CategoryName }).Select
                                (group => new { group.FirstOrDefault().Date, group.FirstOrDefault().CategoryName, Quantity = group.Sum(x => x.Quantity) }
                                );

                var list = query.ToList();

                List <SalesVolumeMonthCategoryModel> model = new List <SalesVolumeMonthCategoryModel>();

                for (int y = 0; y < 7; y++)
                {
                    foreach (var item in categoryArr)
                    {
                        model.Add(new SalesVolumeMonthCategoryModel(start.AddDays(y), item.ToString(), 0));
                    }
                }

                foreach (var item in list)
                {
                    SalesVolumeMonthCategoryModel categoryModel = model.Find(x => x.CategoryName == item.CategoryName && new DateTime(x.Date.Year, x.Date.Month, x.Date.Day) == new DateTime(item.Date.Year, item.Date.Month, item.Date.Day));

                    if (categoryModel != null)
                    {
                        categoryModel.Quantity = item.Quantity;
                    }
                }

                return(model);
            }
        }
        public static List <TopWorstProductModel> GetTopProductByYear(int topCount, DateTime year)
        {
            DateTime start = new DateTime(year.Year, 1, 1);
            DateTime end   = start.AddYears(1); // 기간을 1년으로 설정

            using (var context = new CosmeticSolutionSystemEntities())
            {
                var query = (from x in context.SalesLines
                             where x.Sale.SelledAt >= start && x.Sale.SelledAt <= end
                             select new
                {
                    ProductName = x.Product.ProductName,
                    Quantity = x.Quantity,
                }).GroupBy(x => x.ProductName).Select(
                    group => new { group.FirstOrDefault().ProductName, Quantity = group.Sum(x => x.Quantity) }).OrderByDescending(x => x.Quantity).Take(topCount);

                var list = query.ToList();

                List <TopWorstProductModel> model = new List <TopWorstProductModel>();

                foreach (var x in list)
                {
                    model.Add(new TopWorstProductModel(x.ProductName, x.Quantity));
                }

                return(model);
            }
        }
        // 일별 판매량을 카테고리 별로 가져온다
        public static List <DaySalesByCategoryModel> GetSalesVolumeByDay(DateTime start)
        {
            start = new DateTime(start.Year, start.Month, start.Day);

            using (var context = new CosmeticSolutionSystemEntities())
            {
                var queryX = from x in context.Categories
                             orderby x.CategoryName
                             select x.CategoryName;

                int i = 0;

                foreach (var x in queryX)
                {
                    categoryArr[i] = x.ToString();
                }


                //context.Database.Log = (log) => Debug.WriteLine(log);

                var query = (from x in context.SalesLines
                             where DbFunctions.TruncateTime(x.Sale.SelledAt) == DbFunctions.TruncateTime(start)
                             select new
                {
                    CategoryName = x.Product.Category.CategoryName,
                    Quantity = x.Quantity,
                }).GroupBy(x => x.CategoryName).Select(
                    group => new { group.FirstOrDefault().CategoryName, Quantity = group.Sum(x => x.Quantity) }
                    );

                var list = query.ToList();

                List <DaySalesByCategoryModel> model = new List <DaySalesByCategoryModel>();

                // 카테고리 6개를 무조건 만든다
                foreach (var x in queryX)
                {
                    model.Add(new DaySalesByCategoryModel(x.ToString(), 0));
                }

                foreach (var item in list)
                {
                    DaySalesByCategoryModel tempModel = model.Find(x => x.CategoryName == item.CategoryName);

                    if (tempModel != null)
                    {
                        tempModel.Quantity = item.Quantity;
                    }
                }

                return(model);
            }
        }
        public List <CategorizedByAgeModel> GetModelsCategory(int year)
        {
            DateTime @from = DateTime.Today.AddMonths(year * -1);
            DateTime to    = DateTime.Today;

            using (CosmeticSolutionSystemEntities context = new CosmeticSolutionSystemEntities())
            {
                var query = from x in context.SalesLines
                            where x.Sale.SelledAt >= @from && x.Sale.SelledAt <= to
                            select new
                {
                    Date         = x.Sale.SelledAt,
                    Quantity     = x.Quantity,
                    CategoryName = x.Product.Category.CategoryName,
                    Age          = x.Sale.Customer.Birth
                };

                var list = query.ToList();

                List <CategorizedByAgeModel> models = new List <CategorizedByAgeModel>();

                foreach (var item in list)
                {
                    CategorizedByAgeModel model = models.Find(x => x.Date == item.Date && x.CategoryName == item.CategoryName &&
                                                              x.Age == item.Age);

                    if (model != null)
                    {
                        model.Quantity += item.Quantity;
                    }
                    else
                    {
                        model              = new CategorizedByAgeModel();
                        model.Date         = item.Date;
                        model.CategoryName = item.CategoryName;
                        model.Quantity     = item.Quantity;
                        model.Age          = item.Age;

                        models.Add(model);
                    }
                }

                return(models);
            }
        }
        public static List <SalesModel> GetModels(int month)
        {
            DateTime @from = DateTime.Today.AddMonths(month * -1);
            DateTime to    = DateTime.Today;

            using (CosmeticSolutionSystemEntities context = new CosmeticSolutionSystemEntities())
            {
                var query = from x in context.SalesLines
                            where x.Sale.SelledAt >= @from && x.Sale.SelledAt <= to
                            select new
                {
                    Date = x.Sale.SelledAt,
                    // TotalPrice = x.Sale.TotalPrice,
                    Quantity     = x.Quantity,
                    CategoryName = x.Product.Category.CategoryName
                };

                var list = query.ToList();

                List <SalesModel> models = new List <SalesModel>();

                foreach (var item in list)
                {
                    SalesModel model = models.Find(x => x.Date == item.Date && x.CategoryName == item.CategoryName);

                    if (model != null)
                    {
                        model.Quantity += item.Quantity;
                    }
                    else
                    {
                        model              = new SalesModel();
                        model.Date         = item.Date;
                        model.CategoryName = item.CategoryName;
                        model.Quantity     = item.Quantity;

                        models.Add(model);
                    }
                }

                return(models);
            }
        }
        public static List <DayMonthSalesModel> GetSalesVolumeByMonth(DateTime startMonth)
        {
            int cntMonths = 12;

            DateTime start = new DateTime(
                startMonth.Year, startMonth.Month, 1);
            DateTime end = start.AddMonths(cntMonths);

            using (var context = new CosmeticSolutionSystemEntities())
            {
                var query = (from x in context.SalesLines
                             where x.Sale.SelledAt >= start && x.Sale.SelledAt <= end
                             select new
                {
                    Date = x.Sale.SelledAt,
                    Quantity = x.Quantity,
                }).GroupBy(x => x.Date.Month).Select(
                    group => new { group.FirstOrDefault().Date, Quantity = group.Sum(x => x.Quantity) });

                var list = query.ToList();

                List <DayMonthSalesModel> model = new List <DayMonthSalesModel>();

                for (int i = 0; i < cntMonths; i++)
                {
                    DateTime months = start.AddMonths(i);
                    model.Add(new DayMonthSalesModel(months, 0));
                }

                foreach (var item in list)
                {
                    DayMonthSalesModel sales = model.Find(x => x.Date.Month == item.Date.Month);

                    if (sales != null)
                    {
                        sales.SalesVolume = item.Quantity;
                    }
                }

                return(model);
            }
        }
        public List <HourlySales> SearchDate(DateTime startDate, DateTime endDate)
        {
            using (CosmeticSolutionSystemEntities context = new CosmeticSolutionSystemEntities())
            {
                var query = from x in context.Sales
                            where x.SelledAt >= startDate && x.SelledAt <= endDate
                            select x;

                var groupQuery = from p in query
                                 where p.SelledAt.Hour >= 1 && p.SelledAt.Hour <= 24
                                 group p by p.SelledAt.Hour into g
                                 select new HourlySales
                {
                    Hour          = g.Key,
                    CustomerCount = g.Count()
                };

                return(groupQuery.ToList());
            }
        }