public async Task <IActionResult> AbandonedProductTypes()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
var user = await GetCurrentUserAsync();
cmd.CommandText = @"
select ProductTypeId, Label, count(OrderId) as [IncompleteOrderCount]
from(select p.ProductTypeId, pt.Label, o.OrderId
from[Order] o
inner join OrderProduct op on op.OrderId = o.OrderId
inner join Product p on p.ProductId = op.ProductId
inner join ProductType pt on pt.ProductTypeId = p.ProductTypeId
where o.PaymentTypeId IS NULL and p.UserId = @userId
group by o.OrderId, p.ProductTypeId, pt.Label
) oo
group by ProductTypeId, Label
order by ProductTypeId
";
cmd.Parameters.Add(new SqlParameter("@userId", user.Id));
SqlDataReader reader = cmd.ExecuteReader();
var model = new AbandonedProductTypesReportViewModel();
while (reader.Read())
{
var newProductType = new ProductType
{
ProductTypeId = reader.GetInt32(reader.GetOrdinal("ProductTypeId")),
Label = reader.GetString(reader.GetOrdinal("Label"))
};
var newProductTypeOrderCount = new ProductTypeOrderCount
{
ProductType = newProductType,
IncompleteOrderCount = reader.GetInt32(reader.GetOrdinal("IncompleteOrderCount"))
};
model.IncompleteOrderCounts.Add(newProductTypeOrderCount);
}
return(View(model));
}
}
}
//Abandoned ProductTypes
public async Task <IActionResult> AbandonedProductTypes()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
var user = await GetCurrentUserAsync();
cmd.CommandText = @"SELECT ProductTypeId, Label, COUNT(OrderId) as IncompleteOrderCount
FROM (SELECT p.ProductTypeId, pt.Label, o.OrderId
FROM [Order] o INNER JOIN OrderProduct op on op.OrderId = o.OrderId
LEFT JOIN Product p on p.ProductId = op.ProductId
LEFT JOIN ProductType pt on pt.ProductTypeId = p.ProductTypeId
WHERE o.DateCompleted IS NULL and p.UserId = @userId
GROUP BY o.OrderId, p.ProductTypeId, pt.Label) o
GROUP BY ProductTypeId, Label
ORDER BY ProductTypeId";
cmd.Parameters.Add(new SqlParameter("@userId", user.Id));
SqlDataReader reader = cmd.ExecuteReader();
var model = new AbandonedProductTypesReportViewModel();
while (reader.Read())
{
var newPT = new ProductType
{
ProductTypeId = reader.GetInt32(reader.GetOrdinal("ProductTypeId")),
Label = reader.GetString(reader.GetOrdinal("Label"))
};
var newProductTypeCount = new ProductTypeCount
{
ProductType = newPT,
IncompleteOrderCount = reader.GetInt32(reader.GetOrdinal("IncompleteOrderCount"))
};
model.IncompleteOrderCounts.Add(newProductTypeCount);
}
return(View(model));
}
}
}
