//public List<AgeModel> AgeRank(int topCount, DateTime year){
// //new DateTime(year.Year - 1, 1, 1);
// DateTime @from = DateTime.Today.AddYears(-1);
// DateTime to = from.AddYears(1); //기간 1년
// using(var context = new CosmeticFinalEntities())
// {
// var query = (from x in context.Orders
// where x.SelledAt >= @from && x.SelledAt <= to
// select new
// {
// Age = x.Customer.Birth,
// TotalPrice = x.TotalPrice
// }).GroupBy(x => x.Age).Select(
// group => new { group.FirstOrDefault().Age, TotalPrice = group.Sum(x => x.TotalPrice) }).OrderByDescending(x=>x.TotalPrice).Take(topCount);
// var list = query.ToList();
// List<AgeModel> model = new List<AgeModel>();
// foreach(var x in list)
// {
// model.Add(new AgeModel(x.Age, x.TotalPrice));
// }
// return model;
// }
//}
public List <AgeModel> AgeRank()
{
DateTime start = DateTime.Today.AddYears(-1);
DateTime end = DateTime.Today.AddYears(1); // 기간을 1년으로 설정
int yyy = start.Year;
using (var context = new CosmeticFinalEntities())
{
var query = (from x in context.Orders
where x.SelledAt >= start && x.SelledAt <= end
select new
{
Date = x.SelledAt,
Birth = (((yyy - x.Customer.Birth) / 10) * 10),
TotalPrice = x.TotalPrice
}).GroupBy(x => x.Birth).Select(
group => new { Birth = group.FirstOrDefault().Birth, TotalPrice = group.Sum(x => x.TotalPrice), Date = group.FirstOrDefault().Date });
var list = query.ToList();
List <AgeModel> model = new List <AgeModel>();
foreach (var x in list)
{
model.Add(new AgeModel(x.Birth, x.TotalPrice, x.Date));
}
return(model);
}
}
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 static List <Station> GetStation(string address)
{
using (CosmeticFinalEntities context = (CosmeticFinalEntities)DbContextCreator.Create())
{
var query = from x in context.Stations
where x.Address == address
select x;
var list = query.ToList();
return(list);
}
}
public static List <string> GetAddress()
{
using (CosmeticFinalEntities context = (CosmeticFinalEntities)DbContextCreator.Create())
{
var query = from x in context.Stations
select x.Address;
var list = query.Distinct().ToList();
return(list);
}
}
public static List <OrderAnalysis> GetOrderAnalysisResult(int year, int month = 1, int day = 1)
{
DateTime @from = new DateTime(year, month, day);
DateTime to = from.AddYears(1);
using (CosmeticFinalEntities context = DbContextCreator.Create())
{
// 판매 데이터 테이블과 고객 테이블을 조인하여 고객의 생일을 가져온다. 생일은 연령대 계산에 사용한다.
//var query = from x in context.Orders
// where x.SelledAt >= @from && x.SelledAt <= to
// select new
// {
// Id = x.OrderId,
// Birth = x.Customer.Birth,
// };
var query = (from x in context.Orders
where x.SelledAt >= @from && x.SelledAt <= to
select new
{
OrderDate = x.SelledAt.Year,
Quantity = 1,
AgeGroup = (DateTime.Now.Year - x.Customer.Birth - 1) >= 60 ? 4 :
((DateTime.Now.Year - x.Customer.Birth - 1) < 60 && (DateTime.Now.Year - x.Customer.Birth - 1) >= 40) ? 3 :
((DateTime.Now.Year - x.Customer.Birth - 1) < 40 && (DateTime.Now.Year - x.Customer.Birth - 1) >= 20) ? 2 : 1
}).GroupBy(x => x.AgeGroup)
.Select
(
group =>
new
{
group.FirstOrDefault().OrderDate,
group.FirstOrDefault().AgeGroup,
NumberOrder = group.Sum(x => x.Quantity)
}
).OrderByDescending(x => x.NumberOrder).Take(1);
var queryY = query.ToList();
List <OrderAnalysis> list = new List <OrderAnalysis>();
foreach (var y in queryY)
{
list.Add(new OrderAnalysis(y.AgeGroup, new DateTime(y.OrderDate, 1, 1), y.NumberOrder));
}
return(list);
}
}
public static List <StationModel> GetInfo(string station)
{
using (var context = new CosmeticFinalEntities())
{
var query = from x in context.Stations
where x.Name == station
select new
{
x.Latitude,
x.Longitude
};
var list = query.ToList();
List <StationModel> model = new List <StationModel>();
foreach (var x in list)
{
model.Add(new StationModel(x.Latitude, x.Longitude));
}
return(model.ToList());
}
}
public static List <BranchModel> GetBranch(int stationId)
{
using (CosmeticFinalEntities context = (CosmeticFinalEntities)DbContextCreator.Create())
{
var query = from x in context.Branches
where x.StationId == stationId
select new
{
x.Name,
x.Latitude,
x.Longitude
};
var list = query.ToList();
List <BranchModel> model = new List <BranchModel>();
foreach (var x in list)
{
model.Add(new BranchModel(x.Name, x.Latitude, x.Longitude));
}
return(model);
}
}
public List <OpenCloseModel> GetOpenClose()
{
DateTime start = DateTime.Today.AddYears(-1);
DateTime end = DateTime.Today.AddYears(1); // 기간을 1년으로 설정
int yyy = start.Year;
using (var context = new CosmeticFinalEntities())
{
var query = (from x in context.PassengerInfoes
where x.Date >= start && x.Date <= end
select new
{
Date = x.Date.Year,
StationName = x.Station.Name,
StationId = x.StationId,
StationAddress = x.Station.Address,
TotalOpenEight = x.OpenTimeEight,
TotalOpenNine = x.OpenTimeNine,
TotalCloseNine = x.CloseTimeNine,
TotalCloseTen = x.CloseTimeTen
}).GroupBy(x => x.StationId).Select(
group => new { Date = group.FirstOrDefault().Date, StationId = group.FirstOrDefault().StationId, StationName = group.FirstOrDefault().StationName, StationAddress = group.FirstOrDefault().StationAddress, TotalOpenEight = group.Sum(x => x.TotalOpenEight), TotalOpenNine = group.Sum(x => x.TotalOpenNine), TotalCloseNine = group.Sum(x => x.TotalCloseNine), TotalCloseTen = group.Sum(x => x.TotalCloseTen) });
var list = query.ToList();
List <OpenCloseModel> models = new List <OpenCloseModel>();
OpenCloseModel model;
foreach (var item in list)
{
model = new OpenCloseModel();
//model.Year = item.Date;
model.StationId = item.StationId;
model.StationName = item.StationName;
model.StationAddress = item.StationAddress;
if (item.TotalOpenEight > item.TotalOpenNine)
{
model.OpenTime = (int)item.TotalOpenEight;
model.Open = "8시";
}
else
{
model.OpenTime = (int)item.TotalOpenNine;
model.Open = "9시";
}
if (item.TotalCloseNine > item.TotalCloseTen)
{
model.CloseTime = (int)item.TotalCloseNine;
model.Close = "9시";
}
else
{
model.CloseTime = (int)item.TotalCloseTen;
model.Close = "10시";
}
models.Add(model);
}
return(models);
}
}
