public async Task <Tuple <List <ProductReportViewModel>, int> > SearchAsync(ProductReportRequestModel request)
{
ReportDbContext db = new ReportDbContext();
BaseRepository <ProductReport> repo = new BaseRepository <ProductReport>(db);
var service = new BaseService <ProductReport, ProductReportRequestModel, ProductReportViewModel>(repo);
Tuple <List <ProductReportViewModel>, int> tuple = await service.SearchAsync(request);
return(tuple);
}
public async Task <ResultList <Persistence.EFModels.Product> > ProductReport(ProductReportRequestModel model)
{
_logger.Info("ProductReport Started");
var result = await _productStore.ProductReport(model.MaxQuantity, model.MinQuantity,
model.Enabled, model.MaxPrice, model.MinPrice, model.StorageId, model.SupplierId);
_logger.Info("ProductReport Finished");
return(result);
}
public ResultList <Product> ProductReport(ProductReportRequestModel model)
{
/*
* In EntityFrameworkCore we have two methods for executing Stored Procedures that:
* 1- Query for records from a database - FromSqlRaw()
* 2- Execute a command (typically DML) on the database - ExecuteSqlRaw() or the awaitable ExecuteSqlRawAsync()
* https://referbruv.com/blog/posts/working-with-stored-procedures-in-aspnet-core-ef-core
*/
var maxQuantityParam = new SqlParameter("@MaxQuantity", model.MaxQuantity);
var minQuantityParam = new SqlParameter("@MinQuantity", model.MinQuantity);
var enabledParam = new SqlParameter("@Enabled", model.Enabled);
var maxPriceParam = new SqlParameter("@MaxPrice", model.MaxPrice);
var minPriceParam = new SqlParameter("@MinPrice", model.MinPrice);
var storageIdParam = new SqlParameter("@StorageId", model.StorageId == null ? DBNull.Value : model.StorageId);
var supplierIdParam = new SqlParameter("@SupplierId", model.SupplierId == null ? DBNull.Value : model.SupplierId);
var result = _context.Products.FromSqlRaw("exec spGetProductReport @MaxQuantity, @MinQuantity, @Enabled, @MaxPrice, @MinPrice, @StorageId, @SupplierId",
maxQuantityParam, minQuantityParam, enabledParam, maxPriceParam,
minPriceParam, storageIdParam, supplierIdParam).ToList();
//The required column 'StorageId' was not present in the results of a 'FromSql' operation.
return(ResultList <Product> .Successful(null));
}
public ResultList <Product> ProductReportEfCore(ProductReportRequestModel product)
{
return(_productEfCoreService.ProductReport(product));
}
public async Task <ResultList <Product> > ProductReportSp(ProductReportRequestModel product)
{
return(await _productSpService.ProductReport(product));
}
public Tuple <List <ProductReport>, int> GetProductStockReport(ProductReportRequestModel request)
{
BusinessDbContext db = base.Repository.Db as BusinessDbContext;
request.StartDate = request.StartDate.Date;
request.EndDate = request.EndDate.Date.AddDays(1).AddMinutes(-1);
var dbSaleDetailsDone = db.SaleDetails.Include(x => x.Sale).Where(x => x.Sale.OrderState == OrderState.Delivered || x.Sale.OrderState == OrderState.Completed).AsQueryable();
var dbSaleDetailsProcessing = db.SaleDetails.Include(x => x.Sale).Where(x => x.Sale.OrderState > OrderState.Pending && x.Sale.OrderState < OrderState.Delivered).AsQueryable();
var dbPurchaseDetails = db.PurchaseDetails.AsQueryable();
var dbStockTransferOutDetailsApproved = db.StockTransferDetails.Include(x => x.StockTransfer).Where(x => x.StockTransfer.TransferState == StockTransferState.Approved).AsQueryable();
var dbStockTransferInDetailsApproved = db.StockTransferDetails.Include(x => x.StockTransfer).Where(x => x.StockTransfer.TransferState == StockTransferState.Approved).AsQueryable();
var dbStockTransferOutDetailsPending = db.StockTransferDetails.Include(x => x.StockTransfer).Where(x => x.StockTransfer.TransferState == StockTransferState.Pending).AsQueryable();
var dbStockTransferInDetailsPending = db.StockTransferDetails.Include(x => x.StockTransfer).Where(x => x.StockTransfer.TransferState == StockTransferState.Pending).AsQueryable();
if (request.WarehouseId.IdIsOk() && request.WarehouseId != new Guid().ToString())
{
dbSaleDetailsDone = dbSaleDetailsDone.Where(x => x.WarehouseId == request.WarehouseId);
dbSaleDetailsProcessing = dbSaleDetailsProcessing.Where(x => x.WarehouseId == request.WarehouseId);
dbPurchaseDetails = dbPurchaseDetails.Where(x => x.WarehouseId == request.WarehouseId);
dbStockTransferOutDetailsApproved =
dbStockTransferOutDetailsApproved.Where(x => x.SourceWarehouseId == request.WarehouseId);
dbStockTransferInDetailsApproved =
dbStockTransferInDetailsApproved.Where(x => x.DestinationWarehouseId == request.WarehouseId);
}
List <StockReportModelTemp> purchaseDetailListBefore = dbPurchaseDetails
.Where(x => x.ShopId == request.ShopId && DbFunctions.TruncateTime(x.Created) < request.StartDate)
.GroupBy(x => x.ProductDetailId).Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.Paid),
RowsCount = x.Select(y => y.PurchaseId).Distinct().Count()
}).ToList();
List <StockReportModelTemp> saleDetailListBeforeDone = dbSaleDetailsDone.Where(x =>
x.ShopId == request.ShopId && DbFunctions.TruncateTime(x.Created) < request.StartDate).GroupBy(x => x.ProductDetailId).Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PaidAmount),
RowsCount = x.Select(y => y.SaleId).Distinct().Count()
}).ToList();
List <StockReportModelTemp> saleDetailListBeforeProcessing = dbSaleDetailsProcessing.Where(x =>
x.ShopId == request.ShopId && DbFunctions.TruncateTime(x.Created) < request.StartDate).GroupBy(x => x.ProductDetailId).Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PaidAmount),
RowsCount = x.Select(y => y.SaleId).Distinct().Count()
}).ToList();
List <StockReportModelTemp> purchaseDetailList = dbPurchaseDetails
.Where(x => x.ShopId == request.ShopId && DbFunctions.TruncateTime(x.Created) >= request.StartDate && DbFunctions.TruncateTime(x.Created) <= request.EndDate)
.GroupBy(x => x.ProductDetailId).Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.Paid),
RowsCount = x.Select(y => y.PurchaseId).Distinct().Count()
}).ToList();
List <StockReportModelTemp> saleDetailListDone = dbSaleDetailsDone.Where(x =>
x.ShopId == request.ShopId && DbFunctions.TruncateTime(x.Created) >= request.StartDate && DbFunctions.TruncateTime(x.Created) <= request.EndDate).GroupBy(x => x.ProductDetailId).Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PaidAmount),
RowsCount = x.Select(y => y.SaleId).Distinct().Count()
}).ToList();
List <StockReportModelTemp> saleDetailListProcessing = dbSaleDetailsProcessing.Where(x =>
x.ShopId == request.ShopId && DbFunctions.TruncateTime(x.Created) >= request.StartDate && DbFunctions.TruncateTime(x.Created) <= request.EndDate).GroupBy(x => x.ProductDetailId).Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PaidAmount),
RowsCount = x.Select(y => y.SaleId).Distinct().Count()
}).ToList();
List <StockReportModelTemp> stockTransferInDetailListBeforeApproved = new List <StockReportModelTemp>();
List <StockReportModelTemp> stockTransferInDetailListApproved = new List <StockReportModelTemp>();
List <StockReportModelTemp> stockTransferOutDetailListBeforeApproved = new List <StockReportModelTemp>();
List <StockReportModelTemp> stockTransferOutDetailListApproved = new List <StockReportModelTemp>();
List <StockReportModelTemp> stockTransferInDetailListBeforePending = new List <StockReportModelTemp>();
List <StockReportModelTemp> stockTransferInDetailListPending = new List <StockReportModelTemp>();
List <StockReportModelTemp> stockTransferOutDetailListBeforePending = new List <StockReportModelTemp>();
List <StockReportModelTemp> stockTransferOutDetailListPending = new List <StockReportModelTemp>();
if (request.WarehouseId.IdIsOk() && request.WarehouseId != new Guid().ToString())
{
stockTransferInDetailListBeforeApproved = dbStockTransferInDetailsApproved
.Where(x => x.ShopId == request.ShopId &&
x.DestinationWarehouseId == request.WarehouseId &&
DbFunctions.TruncateTime(x.Created) < request.StartDate).GroupBy(x => x.ProductDetailId)
.Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PriceTotal),
RowsCount = x.Select(y => y.StockTransferId).Distinct().Count()
}).ToList();
stockTransferOutDetailListBeforeApproved = dbStockTransferOutDetailsApproved
.Where(x => x.ShopId == request.ShopId &&
x.SourceWarehouseId == request.WarehouseId &&
DbFunctions.TruncateTime(x.Created) < request.StartDate).GroupBy(x => x.ProductDetailId)
.Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PriceTotal),
RowsCount = x.Select(y => y.StockTransferId).Distinct().Count()
}).ToList();
stockTransferInDetailListApproved = dbStockTransferInDetailsApproved
.Where(x => x.ShopId == request.ShopId &&
x.DestinationWarehouseId == request.WarehouseId &&
DbFunctions.TruncateTime(x.Created) >= request.StartDate &&
DbFunctions.TruncateTime(x.Created) <= request.EndDate).GroupBy(x => x.ProductDetailId)
.Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PriceTotal),
RowsCount = x.Select(y => y.StockTransferId).Distinct().Count()
}).ToList();
stockTransferOutDetailListApproved = dbStockTransferOutDetailsApproved
.Where(x => x.ShopId == request.ShopId &&
x.SourceWarehouseId == request.WarehouseId &&
DbFunctions.TruncateTime(x.Created) >= request.StartDate &&
DbFunctions.TruncateTime(x.Created) <= request.EndDate).GroupBy(x => x.ProductDetailId)
.Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PriceTotal),
RowsCount = x.Select(y => y.StockTransferId).Distinct().Count()
}).ToList();
stockTransferInDetailListBeforePending = dbStockTransferInDetailsPending
.Where(x => x.ShopId == request.ShopId &&
x.DestinationWarehouseId == request.WarehouseId &&
DbFunctions.TruncateTime(x.Created) < request.StartDate).GroupBy(x => x.ProductDetailId)
.Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PriceTotal),
RowsCount = x.Select(y => y.StockTransferId).Distinct().Count()
}).ToList();
stockTransferOutDetailListBeforePending = dbStockTransferOutDetailsPending
.Where(x => x.ShopId == request.ShopId &&
x.SourceWarehouseId == request.WarehouseId &&
DbFunctions.TruncateTime(x.Created) < request.StartDate).GroupBy(x => x.ProductDetailId)
.Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PriceTotal),
RowsCount = x.Select(y => y.StockTransferId).Distinct().Count()
}).ToList();
stockTransferInDetailListPending = dbStockTransferInDetailsPending
.Where(x => x.ShopId == request.ShopId &&
x.DestinationWarehouseId == request.WarehouseId &&
DbFunctions.TruncateTime(x.Created) >= request.StartDate &&
DbFunctions.TruncateTime(x.Created) <= request.EndDate).GroupBy(x => x.ProductDetailId)
.Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PriceTotal),
RowsCount = x.Select(y => y.StockTransferId).Distinct().Count()
}).ToList();
stockTransferOutDetailListPending = dbStockTransferOutDetailsPending
.Where(x => x.ShopId == request.ShopId &&
x.SourceWarehouseId == request.WarehouseId &&
DbFunctions.TruncateTime(x.Created) >= request.StartDate &&
DbFunctions.TruncateTime(x.Created) <= request.EndDate).GroupBy(x => x.ProductDetailId)
.Select(x => new StockReportModelTemp()
{
ProductDetailId = x.Key,
Quantity = x.Sum(y => y.Quantity),
Amount = x.Sum(y => y.PriceTotal),
RowsCount = x.Select(y => y.StockTransferId).Distinct().Count()
}).ToList();
}
var productDetails = db.ProductDetails.AsQueryable();
if (request.IsProductActive)
{
productDetails = productDetails.Where(x => x.IsActive);
}
var products = productDetails.Where(x => x.ShopId == request.ShopId).Include(x => x.ProductCategory)
.Include(x => x.ProductCategory.ProductGroup).ToList().OrderBy(x => x.Name);
List <ProductReport> productReports = new List <ProductReport>();
foreach (var productDetail in products)
{
StockReportModelTemp purchaseDetail = purchaseDetailList.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp saleDetailDone = saleDetailListDone.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp saleDetailProcessing = saleDetailListProcessing.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp purchaseDetailBefore =
purchaseDetailListBefore.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp saleDetailBeforeDone = saleDetailListBeforeDone.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp saleDetailBeforeProcessing = saleDetailListBeforeProcessing.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp stockInApproved = stockTransferInDetailListApproved.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp stockOutApproved = stockTransferOutDetailListApproved.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp stockInPending = stockTransferInDetailListPending.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp stockOutPending = stockTransferOutDetailListPending.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp stockInBeforeApproved = stockTransferInDetailListBeforeApproved.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
StockReportModelTemp stockOutBeforeApproved = stockTransferOutDetailListBeforeApproved.FirstOrDefault(x => x.ProductDetailId == productDetail.Id);
var purchaseQuantityBefore = purchaseDetailBefore?.Quantity ?? 0;
var stockInQuantityBeforeApproved = stockInBeforeApproved?.Quantity ?? 0;
var stockOutQuantityBeforeApproved = stockOutBeforeApproved?.Quantity ?? 0;
var saleQuantityBeforeDone = saleDetailBeforeDone?.Quantity ?? 0;
var saleQuantityBeforeProcessing = saleDetailBeforeProcessing?.Quantity ?? 0;
var startingToday = stockInQuantityBeforeApproved
+ purchaseQuantityBefore
- saleQuantityBeforeProcessing
- saleQuantityBeforeDone
- stockOutQuantityBeforeApproved;
var productReport = new ProductReport();
productReport.Id = productDetail.Id;
productReport.ProductDetailId = productDetail.Id;
productReport.ProductDetailName = productDetail.Name;
productReport.ProductCategoryId = productDetail.ProductCategoryId;
productReport.ProductCategoryName = productDetail.ProductCategory.Name;
productReport.ProductGroupId = productDetail.ProductCategory.ProductGroupId;
productReport.ProductGroupName = productDetail.ProductCategory.ProductGroup.Name;
productReport.QuantityStartingToday = startingToday;
productReport.QuantityPurchaseToday = purchaseDetail?.Quantity ?? 0;
productReport.QuantitySaleDoneToday = saleDetailDone?.Quantity ?? 0;
productReport.QuantitySaleProcessingToday = saleDetailProcessing?.Quantity ?? 0;
productReport.QuantityStockInApprovedToday = stockInApproved?.Quantity ?? 0;
productReport.QuantityStockOutApprovedToday = stockOutApproved?.Quantity ?? 0;
productReport.QuantityStockInPendingToday = stockInPending?.Quantity ?? 0;
productReport.QuantityStockOutPendingToday = stockOutPending?.Quantity ?? 0;
productReport.AmountSaleToday = saleDetailDone?.Amount ?? 0;
productReport.RowsCount = saleDetailDone?.RowsCount ?? 0;
productReport.ShopId = productDetail.ShopId;
productReport.Created = request.StartDate;
productReport.IsActive = productDetail.IsActive;
productReport.QuantityEndingToday =
startingToday
+ productReport.QuantityPurchaseToday
+ productReport.QuantityStockInApprovedToday
- productReport.QuantitySaleToday
- productReport.QuantityStockOutApprovedToday;
productReports.Add(productReport);
}
//var reportViewModels = productReports.ConvertAll(x => new ProductReportViewModel(x)).ToList();
return(new Tuple <List <ProductReport>, int>(productReports, productReports.Count));
}
