/// <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 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<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 <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 Dictionary <string, int> ToDictionary() { using (var context = DbContextCreator.Create()) { return(context.Libraries.ToDictionary(x => x.Location, x => x.LibraryId)); } }
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 <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 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 <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 <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 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 <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 <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 <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); } }
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 List <string> GetTeamNames() { using (var context = DbContextCreator.Create()) { var query = (from x in context.Employees select x.Team).Distinct(); return(query.ToList()); } }
public List <Resource> GetResourceInfo() { using (var context = DbContextCreator.Create()) { var query = from x in context.Resources select x; return(query.ToList()); } }
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()); } }
public static List <FinancialStatement> GetFinancialStatements() { using (var context = DbContextCreator.Create()) { var query = from x in context.FinancialStatements select x; return(query.ToList()); } }
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() 함수가 호출될 때 쿼리가 실제로 날아간다(실행된다). } }
//protected override Expression<Func<Pet, bool>> IsKey(int key) //{ // return x => x.PetID == key; //} public List <Pet> GetPetList(string family) { using (var context = DbContextCreator.Create()) { var query = from x in context.Pets where x.Familly == family select x; return(query.ToList()); } }
public List <Sale> ThisYear(int year) { using (var context = DbContextCreator.Create()) { var query = from x in context.Sales where x.SelledAt.Year == DateTime.Today.Year select x; return(query.ToList()); } }
public List <Employee> GetEmployeeByTeam(string teamName) { using (var context = DbContextCreator.Create()) { var query = from x in context.Employees where x.Team == teamName select x; return(query.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 List <Employee> GetByPK(int employeeId) { using (var context = DbContextCreator.Create()) { var query = from x in context.Employees where x.EmployeeId == employeeId select x; return(query.ToList()); } }