private string ConvertShoppingCartsReportDTO(ShoppingCartsReportDTO data)
{
var result = new StringBuilder();
result.AppendLine($"Total carts: {data.TotalCarts}");
result.AppendLine($"Total carts with bonuses: {data.TotalCartsWithBonuses}");
result.AppendLine($"Total carts expired in 10 days: {data.TotalCartsExpiredIn10Days}");
result.AppendLine($"Total carts expired in 20 days: {data.TotalCartsExpiredIn20Days}");
result.AppendLine($"Total carts expired in 30 days: {data.TotalCartsExpiredIn30Days}");
result.AppendLine($"Average check: {data.AverageCheck}");
return(result.ToString());
}
public async Task SaveShoppingCartReport(string fileName, ShoppingCartsReportDTO data)
{
var fullName = Path.Combine(_dirPath, $"{fileName}.txt");
var fileContent = ConvertShoppingCartsReportDTO(data);
using (var stream = new FileStream(
fullName, FileMode.Append, FileAccess.Write, FileShare.Write, 4096, useAsync: true))
{
var bytes = Encoding.UTF8.GetBytes(fileContent);
//await stream.WriteAsync(bytes, 0, bytes.Length);
}
}
public async Task GenerateReport()
{
var cartsReport = await _unitOfWork.RunInTrunsaction(async (con, tran) =>
{
var sqlQuery = @"
-- total carts
SELECT COUNT (*)
FROM ShoppingCart
-- total carts with bonuses
SELECT COUNT (DISTINCT C.Id)
FROM ShoppingCart AS C
JOIN ShoppingCartItem AS CI ON CI.ShoppingCartId = C.Id
JOIN Product AS P ON P.Id = CI.ProductId AND P.ForBonusPoints = 1
-- total expired in 10 days
SELECT COUNT (*)
FROM ShoppingCart
WHERE CAST(LatestUpdatedOn AS DATE) BETWEEN @StartDay10 AND @EndDay10
-- total expired in 20 days
SELECT COUNT (*)
FROM ShoppingCart
WHERE CAST(LatestUpdatedOn AS DATE) BETWEEN @StartDay20 AND @EndDay20
-- total expired in 30 days
SELECT COUNT (*)
FROM ShoppingCart
WHERE CAST(LatestUpdatedOn AS DATE) BETWEEN @StartDay30 AND @EndDay30
-- average check
SELECT AVG (CARTS_STAT.CART_SUM)
FROM (
SELECT C.Id AS CART_Id, SUM (CI.Cost * CI.Quantity) AS CART_SUM
FROM ShoppingCart AS C
JOIN ShoppingCartItem AS CI ON CI.ShoppingCartId = C.Id
GROUP BY C.Id
) AS CARTS_STAT
";
Func <int, string> getDateFunc = (int days) => DateTime.UtcNow.AddDays(days).ToString("yyyy-MM-dd");
var args = new
{
StartDay10 = getDateFunc(-30), EndDay10 = getDateFunc(-20),
StartDay20 = getDateFunc(-30), EndDay20 = getDateFunc(-10),
StartDay30 = getDateFunc(-30), EndDay30 = getDateFunc(0),
};
var result = new ShoppingCartsReportDTO();
using (var multi = await con.QueryMultipleAsync(sqlQuery, args, tran))
{
result.TotalCarts = multi.Read <int>().Single();
result.TotalCartsWithBonuses = multi.Read <int>().Single();
result.TotalCartsExpiredIn10Days = multi.Read <int>().Single();
result.TotalCartsExpiredIn20Days = multi.Read <int>().Single();
result.TotalCartsExpiredIn30Days = multi.Read <int>().Single();
result.AverageCheck = multi.Read <decimal>().Single();
}
return(result);
});
var fileName = $"shoppingCartReport_{DateTime.UtcNow.ToString("yyyy-MM-dd")}";
await _reportSaver.SaveShoppingCartReport(fileName, cartsReport);
}
