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)
{
// 在庫予測計算用に１ヶ月多めに取得（貿易のみ）
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>();
// 在庫予測計算用に１ヶ月多めに取得（貿易のみ）
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;
}
}
