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