/// <summary>
/// Query sample N X N and grouping
/// </summary>
public async Task <SumarizedPurchaseOrder> GetSumarizedPurchaseOrderFromDate(DateTime date)
{
// Para a tabela de PurchaseOrderProducts
// Incluo a referência da tabela product e purchaseOrder
// Filtrando para data passada por parâmetro
// Agrupo pelo Id do produto e sua descrição
var purchaseOrderProductUnits = await QueryAsync <SumarizedProduct>(@"
SELECT product.Id AS Id,
product.Description AS Description,
SUM(purchaseOrderProduct.Quantity) AS Quantity,
SUM(purchaseOrderProduct.Quantity * purchaseOrderProduct.UnitValue) AS TotalValue
FROM PurchaseOrderProducts purchaseOrderProduct
INNER JOIN Products product ON product.Id = purchaseOrderProduct.ProductId
INNER JOIN PurchaseOrders purchaseOrder ON purchaseOrder.Id = purchaseOrderProduct.PurchaseOrderId
WHERE purchaseOrder.Date = @Date
GROUP BY product.Id, product.Description",
new { Date = date.Date });
var sumarized = new SumarizedPurchaseOrder()
{
Date = date,
TotalQuantity = purchaseOrderProductUnits.Sum(s => s.Quantity),
TotalValue = purchaseOrderProductUnits.Sum(s => s.TotalValue),
Products = purchaseOrderProductUnits
};
return(sumarized);
}
/// <summary>
/// Query sample N X N and grouping
/// </summary>
public async Task <SumarizedPurchaseOrder> GetSumarizedPurchaseOrderFromDate(DateTime date)
{
if (date == DateTime.MinValue)
{
date = DateTime.UtcNow;
}
// Para a tabela de ProductOrder
// Incluo a referência da tabela product e order
// Filtrando para data passada por parâmetro
var baseQuery = Context.PurchaseOrderProducts
.Include(i => i.Product)
.Include(i => i.Order)
.Where(w => w.Order.Date == date)
.Select(productOrder => productOrder);
// Agrupo pelo Id do produto e sua descrição
var sumarizedByProduct = (from productOrders in (from po in baseQuery
select new
{
ProductId = po.Product.Id,
po.Product.Description,
po.Quantity,
Total = po.Quantity * po.UnitValue
}).ToList()
group productOrders by new { productOrders.ProductId, productOrders.Description } into productGroup
select new
{
productGroup.Key.ProductId,
ProductDescription = productGroup.Key.Description,
Amount = productGroup.Sum(s => s.Quantity),
TotalValue = productGroup.Sum(s => s.Total)
});
var sumarized = new SumarizedPurchaseOrder()
{
Date = date,
TotalQuantity = await baseQuery.SumAsync(s => s.Quantity),
TotalValue = sumarizedByProduct.Sum(s => s.TotalValue),
Products = sumarizedByProduct.Select(s => new SumarizedProduct()
{
Id = s.ProductId,
Description = s.ProductDescription,
Amount = s.Amount,
TotalValue = s.TotalValue
})
};
return(sumarized);
}
