private IQueryable <GroupModel> QueryGroupModel(DataContext dbContext, CustomParameterModel parameter, bool pageControl = false) { IQueryable <GroupModel> query = dbContext.GroupModels.OrderBy(x => x.Id); if (!parameter.Deleted) { query = query.Where(x => x.Deleted == false); } if (parameter.MakerId.HasValue) { query = query.Where(x => x.MakerModelId == parameter.MakerId.GetValueOrDefault()); } if (pageControl) { if (parameter.Limit.HasValue && parameter.Limit.GetValueOrDefault() > 0) { if (parameter.Page.HasValue && parameter.Page.GetValueOrDefault() >= 0) { query = query.Skip(parameter.Limit.GetValueOrDefault() * parameter.Page.GetValueOrDefault()).Take(parameter.Limit.GetValueOrDefault()); } else { query = query.Take(parameter.Limit.GetValueOrDefault()); } } } return(query); }
public IHttpActionResult GetProductList(int id, [FromUri] CustomParameterModel param) { try { if (id <= 0) { return(BadRequest()); } var group = repository.GetGroupByIdForInterface(id); if (group.Code != HttpStatusCode.OK) { return(Content(group.Code, group.message)); } param.GroupId = id; var result = productRepository.GetProductsForInterface(param); if (result == null) { return(NotFound()); } return(Ok(result)); } catch (Exception ex) { return(InternalServerError(ex)); } }
public object GetProductsPages(CustomParameterModel parameter) { try { if (parameter == null) { return(null); } if (!parameter.Limit.HasValue) { return(null); } else if (parameter.Limit.GetValueOrDefault() <= 0) { return(null); } using (DataContext dbContext = DataContext.Create()) { int maxcount = 0; int maxpages = 0; IQueryable <ProductModel> query = QueryProductModel(dbContext, parameter); maxcount = query.Count(); maxpages = CountToPages(maxcount, parameter.Limit.GetValueOrDefault()); return(new { count = maxcount, pages = maxpages }); } } catch (Exception ex) { throw ex; } }
public IHttpActionResult SetProductList(int id, [FromBody] List <ProductInterfaceModel> ProductList) { try { if (id <= 0) { return(BadRequest()); } var result = repository.GroupModifyProducts(id, ProductList); if (result.Code != HttpStatusCode.OK) { return(Content(result.Code, result.message)); } CustomParameterModel param = new CustomParameterModel(); param.GroupId = id; var products = productRepository.GetProductsForInterface(param); if (products == null) { return(Ok()); } return(Ok(products)); } catch (Exception ex) { return(InternalServerError(ex)); } }
public IHttpActionResult Post([FromBody] SalesViewInterfaceModel value) { try { var result = service.SetSalesView(value); if (result.Code != HttpStatusCode.OK) { return(Content(result.Code, result.message)); } CustomParameterModel param = new CustomParameterModel(); param.Year = value.SalesList.Select(x => x.detail_date).Min().Year; param.Year += 1; result = service.GetSalesViewByIdForInterface(value.Product.Id, param); if (result.Code != HttpStatusCode.OK) { return(Content(result.Code, result.message)); } return(Ok(result.resultData)); } catch (Exception ex) { return(InternalServerError(ex)); } }
public IHttpActionResult GetGroupList(int id, [FromUri] BaseParameterModel param) { try { if (id <= 0) { return(BadRequest()); } var maker = repository.GetMakerByIdForInterface(id); if (maker.Code != HttpStatusCode.OK) { return(BadRequest()); } var parameter = new CustomParameterModel(); parameter.MakerId = id; parameter.Deleted = param.Deleted; var result = groupRepository.GetGroupsForInterface(parameter); if (result == null || result.Count() == 0) { return(NotFound()); } return(Ok(result)); } catch (Exception ex) { return(InternalServerError(ex)); } }
public IHttpActionResult GetSalesTrend(int id, [FromUri] CustomParameterModel param) { var result = service.GetSalesTrandsForInterface(id, param); if (result == null) { return(BadRequest()); } return(Ok(result)); }
public IHttpActionResult GetCurrent(int id, [FromUri] CustomParameterModel param) { var result = service.GetCurrentStocks(id, param); if (result == null) { return(BadRequest()); } return(Ok(result)); }
public IHttpActionResult Get(int id, [FromUri] CustomParameterModel param) { try { var result = service.GetSalesViewByIdForInterface(id, param); if (result.Code != HttpStatusCode.OK) { return(Content(result.Code, result.message)); } return(Ok(result.resultData)); } catch (Exception ex) { return(InternalServerError(ex)); } }
public IHttpActionResult Get([FromUri] CustomParameterModel param) { try { var result = repository.GetProductsForInterface(param); if (result == null) { return(NotFound()); } return(Ok(result)); } catch (Exception ex) { return(InternalServerError(ex)); } }
public IHttpActionResult GetProductMaxPages([FromUri] CustomParameterModel param) { try { var result = repository.GetProductsPages(param); if (result == null) { return(BadRequest()); } return(Ok(result)); } catch (Exception ex) { return(InternalServerError(ex)); } }
public object GetCurrentStocks(int productId, CustomParameterModel parameter) { using (DataContext dbContext = DataContext.Create()) { var product = dbContext.ProductModels.Where(p => p.Id == productId).SingleOrDefault(); if (product == null) { return(null); } var stocks = dbContext.CurrentStockModels .Where(cs => cs.Deleted == false) .Where(cs => cs.ProductModelId == product.Id) .Select(cs => new { cs.WarehouseCode, cs.WarehouseName, cs.StateName, cs.ExpirationDate, cs.LogicalQuantity, cs.ActualQuantity }) .OrderBy(no => no.WarehouseCode).ThenBy(no => no.ExpirationDate).ThenBy(no => no.StateName) .ToList(); var stockMaxDate = dbContext.CurrentStockModels .Where(cs => cs.Deleted == false) .Where(cs => cs.ProductModelId == product.Id) .Select(cs => cs.ModifiedDateTime).Max(); var orders = dbContext.OrderModels .Where(od => od.Deleted == false) .Where(od => od.ProductModelId == product.Id) .Select(od => new { od.OrderNo, od.OrderDate, od.Order }).ToList(); var orderMaxDate = dbContext.OrderModels .Where(od => od.Deleted == false) .Where(od => od.ProductModelId == product.Id) .Select(od => od.ModifiedDateTime).Max(); var invoices = dbContext.InvoiceModels .Where(iv => iv.Deleted == false) .Where(iv => iv.ProductModelId == product.Id) .Select(iv => new { iv.InvoiceNo, iv.WarehouseCode, iv.ETA, iv.CustomsClearanceDate, iv.PurchaseDate, iv.Quantity }).ToList(); var invoiceMaxDate = dbContext.InvoiceModels .Where(iv => iv.Deleted == false) .Where(iv => iv.ProductModelId == product.Id) .Select(iv => iv.ModifiedDateTime).Max(); return(new { stocks, orders, invoices, stockMaxDate, orderMaxDate, invoiceMaxDate }); } }
private IQueryable <ProductModel> QueryProductModel(DataContext dbContext, CustomParameterModel parameter, bool pageControl = false) { IQueryable <ProductModel> query = dbContext.ProductModels.OrderBy(x => x.Id); if (parameter.GroupId.HasValue) { query = dbContext.GroupProductModels.Where(gp => gp.Deleted == false).Where(gp => gp.GroupModelId == parameter.GroupId.GetValueOrDefault()).Select(gp => gp.ProductModel).OrderBy(p => p.Id); } else if (parameter.MakerId.HasValue) { query = dbContext.ProductModels.Where(p => p.MakerModelId == parameter.MakerId.GetValueOrDefault()).OrderBy(p => p.Id); } else { query = dbContext.ProductModels.OrderBy(p => p.Id); } if (!parameter.Deleted) { query = query.Where(p => p.Deleted == false); } if (parameter.Enabled) { query = query.Where(p => p.Enabled == true); } if (pageControl) { if (parameter.Limit.HasValue && parameter.Limit.GetValueOrDefault() > 0) { if (parameter.Page.HasValue && parameter.Page.GetValueOrDefault() >= 0) { query = query.Skip(parameter.Limit.GetValueOrDefault() * parameter.Page.GetValueOrDefault()).Take(parameter.Limit.GetValueOrDefault()); } else { query = query.Take(parameter.Limit.GetValueOrDefault()); } } } return(query); }
public IEnumerable <GroupInterfaceModel> GetGroupsForInterface(CustomParameterModel parameter) { try { using (DataContext dbContext = DataContext.Create()) { IQueryable <GroupModel> query = QueryGroupModel(dbContext, parameter, true); var result = query.ProjectTo <GroupInterfaceModel>().ToList(); if (result == null || result.Count == 0) { return(null); } return(result); } } catch (Exception ex) { throw ex; } }
public IEnumerable <ProductInterfaceModel> GetProductsForInterface(CustomParameterModel parameter) { try { using (DataContext dbContext = DataContext.Create()) using (DbContextTransaction tx = dbContext.Database.BeginTransaction(System.Data.IsolationLevel.Serializable)) { IQueryable <ProductModel> query = QueryProductModel(dbContext, parameter, true); var result = query.ProjectTo <ProductInterfaceModel>().ToList(); if (result == null || result.Count == 0) { return(null); } return(result); } } catch (Exception ex) { throw ex; } }
public IEnumerable <SalesTrendInterfaceModel> GetSalesTrandsForInterface(int productId, CustomParameterModel parameter) { using (DataContext dbContext = DataContext.Create()) using (var productRepository = new ProductRepository()) { var product = productRepository.GetProductByIdForInterface(productId); if (product.Code != HttpStatusCode.OK) { return(null); } if (parameter.Year.HasValue == false) { return(null); } DateTime startDate = DateTime.Parse((parameter.Year.GetValueOrDefault() - 1).ToString() + "/10/1"); DateTime endDate = startDate.AddYears(1); var trends = dbContext.SalesTrendModels .Where(st => st.ProductModelId == product.resultData.Id).Where(st => st.Deleted == false) .Where(st => st.TargetDate >= startDate).Where(st => st.TargetDate < endDate) .ProjectTo <SalesTrendInterfaceModel>() .ToList(); return(trends); } }
public RepositoryResult <IEnumerable <SalesViewInterfaceModel> > GetSalesViewsForInterface(CustomParameterModel parameter, int months = 12) { try { if (parameter.GroupId.HasValue == false && parameter.MakerId.HasValue == false) { return(new RepositoryResult <IEnumerable <SalesViewInterfaceModel> >(HttpStatusCode.BadRequest)); } if (!parameter.Year.HasValue) { return(new RepositoryResult <IEnumerable <SalesViewInterfaceModel> >(HttpStatusCode.BadRequest)); } using (DataContext dbContext = DataContext.Create()) using (var productRepository = new ProductRepository()) { var products = productRepository.GetProductsForInterface(parameter); var productIds = products.Select(p => p.Id).ToList(); ICollection <OfficeModel> office = dbContext.OfficeModels.Where(o => o.Deleted == false).OrderBy(o => o.Code).ToList(); string productList = string.Join(",", productIds); string sql = ServiceResource.SelectSalesViews.Replace("@p2", productList); // 全体の販売実績データ List <SalesInterfaceModel> salesList; DateTime startDate = DateTime.Parse((parameter.Year - 1).ToString() + "/10/1"); DateTime endDate = startDate.AddMonths(months); salesList = dbContext.Database.SqlQuery <SalesInterfaceModel>(sql, startDate, endDate).ToList <SalesInterfaceModel>(); // 事業所別 List <SalesOfficeInterfaceModel> salesOfficeList; sql = ServiceResource.SelectOfficesSalesViews.Replace("@p2", productList); salesOfficeList = dbContext.Database.SqlQuery <SalesOfficeInterfaceModel>(sql, startDate.AddYears(-1), endDate).ToList <SalesOfficeInterfaceModel>(); // 返却用の準備 var resultData = new List <SalesViewInterfaceModel>(); foreach (var product in products) { // 在庫予測計算用に1ヶ月多めに取得(貿易のみ) DateTime check = startDate.AddMonths(-1);; SalesViewInterfaceModel addModel = new SalesViewInterfaceModel(); addModel.Product = product; addModel.SalesList = new List <SalesInterfaceModel>(); addModel.OfficeSales = new List <ICollection <SalesOfficeInterfaceModel> >(); for (; check <= endDate; check = check.AddMonths(1)) { var work = salesList.Where(x => x.product_id == addModel.Product.Id).Where(x => x.detail_date == check).SingleOrDefault(); if (work == null) { var tempModel = new SalesInterfaceModel(); tempModel.product_id = addModel.Product.Id; tempModel.detail_date = check; addModel.SalesList.Add(tempModel); } else { addModel.SalesList.Add(work); } var workOffice = new List <SalesOfficeInterfaceModel>(); foreach (var ofs in office) { var ofsData = salesOfficeList.Where(x => x.product_id == addModel.Product.Id).Where(x => x.detail_date == check).Where(x => x.office_id == ofs.Id).SingleOrDefault(); if (ofsData == null) { var tempModel = new SalesOfficeInterfaceModel(); tempModel.product_id = product.Id; tempModel.detail_date = check; tempModel.office_id = ofs.Id; tempModel.office_name = ofs.Name; workOffice.Add(tempModel); } else { workOffice.Add(ofsData); } } addModel.OfficeSales.Add(workOffice); } resultData.Add(addModel); } return(new RepositoryResult <IEnumerable <SalesViewInterfaceModel> >(HttpStatusCode.OK, resultData)); } } catch (Exception ex) { throw ex; } }
public RepositoryResult <SalesViewInterfaceModel> GetSalesViewByIdForInterface(int productId, CustomParameterModel parameter, int months = 12) { try { if (productId <= 0) { return(new RepositoryResult <SalesViewInterfaceModel>(HttpStatusCode.BadRequest)); } if (!parameter.Year.HasValue) { return(new RepositoryResult <SalesViewInterfaceModel>(HttpStatusCode.BadRequest)); } using (DataContext dbContext = DataContext.Create()) using (var productRepository = new ProductRepository()) { var product = productRepository.GetProductByIdForInterface(productId); if (product.Code != HttpStatusCode.OK) { return(new RepositoryResult <SalesViewInterfaceModel>(HttpStatusCode.BadRequest)); } // 全体の販売実績データ List <SalesInterfaceModel> salesList; DateTime startDate = DateTime.Parse((parameter.Year - 1).ToString() + "/10/1"); DateTime endDate = startDate.AddMonths(months); salesList = dbContext.Database.SqlQuery <SalesInterfaceModel>(ServiceResource.SelectSalesViews, startDate, endDate, product.resultData.Id).ToList <SalesInterfaceModel>(); ICollection <OfficeModel> office = dbContext.OfficeModels.Where(o => o.Deleted == false).OrderBy(o => o.Code).ToList(); // 事業所別 List <SalesOfficeInterfaceModel> salesOfficeList; salesOfficeList = dbContext.Database.SqlQuery <SalesOfficeInterfaceModel>(ServiceResource.SelectOfficesSalesViews, startDate, endDate, product.resultData.Id).ToList <SalesOfficeInterfaceModel>(); // 在庫予測計算用に1ヶ月多めに取得(貿易のみ) DateTime check = startDate.AddMonths(-1); var resultData = new SalesViewInterfaceModel(); resultData.Product = product.resultData; resultData.SalesList = new List <SalesInterfaceModel>(); resultData.OfficeSales = new List <ICollection <SalesOfficeInterfaceModel> >(); for (; check <= endDate; check = check.AddMonths(1)) { var work = salesList.Where(x => x.product_id == resultData.Product.Id).Where(x => x.detail_date == check).SingleOrDefault(); if (work == null) { var tempModel = new SalesInterfaceModel(); tempModel.product_id = resultData.Product.Id; tempModel.detail_date = check; resultData.SalesList.Add(tempModel); } else { resultData.SalesList.Add(work); } var workOffice = new List <SalesOfficeInterfaceModel>(); foreach (var value in office) { var ofsData = salesOfficeList.Where(x => x.product_id == resultData.Product.Id).Where(x => x.detail_date == check).Where(x => x.office_id == value.Id).SingleOrDefault(); if (ofsData == null) { var tempModel = new SalesOfficeInterfaceModel(); tempModel.product_id = resultData.Product.Id; tempModel.detail_date = check; tempModel.office_id = value.Id; tempModel.office_name = value.Name; tempModel.sales_plan = 0; tempModel.sales_actual = 0; workOffice.Add(tempModel); } else { workOffice.Add(ofsData); } } resultData.OfficeSales.Add(workOffice); } return(new RepositoryResult <SalesViewInterfaceModel>(HttpStatusCode.OK, resultData)); } } catch (Exception ex) { throw ex; } }