Posted on 14/04/2020 15:44:20
Hi Adrian
You have either a visitor id or a accessuserid on the stat tables. So you can use that - that information is available from cookie.
The last viewed products can be found in "Dynamicweb:YouHaveSeenTheseRelatedListProducts" session variable.
This is how we find most popular products (most viewed) - but it is slow, so you have to deal with that:
(Also remember that showing this list makes no sense (for the user) and will be biased with its own products since they are clicked more....)
[RelatedProductList("eCom:Related.MostPopularProducts")]
public class MostPopularProducts : RelatedProductListProvider
{
public override ProductCollection GetCollection(RelatedProductListProviderEventArgs eventArgs)
{
var cachingTimeoutInMinutes = 60;
var cacheKey = "Dynamicweb.MostPopularProductsQueryCache";
if (!string.IsNullOrEmpty(Common.Context.LanguageID))
cacheKey = cacheKey + "." + Common.Context.LanguageID;
ProductCollection col;
if (Cache.Current[cacheKey] != null)
{
col = (Cache.Current[cacheKey] as ProductCollection);
}
else
{
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append(@"
SELECT TOP 10
EcomProducts.*,
UnitsSold
FROM
EcomProducts INNER JOIN
(
SELECT
OrderLineProductID,
OrderLineProductVariantID,
SUM(OrderLineQuantity) AS UnitsSold
FROM
EcomOrders,
EcomOrderLines
WHERE
OrderLineOrderId = OrderID AND
OrderComplete = 1
GROUP BY
OrderLineProductID,
OrderLineProductVariantID
) AS SumTable
ON
OrderLineProductID = EcomProducts.ProductID AND
OrderLineProductVariantID = ProductVariantID
");
if (!string.IsNullOrEmpty(Ecommerce.Common.Context.LanguageID))
sql.AppendFormat("WHERE EcomProducts.ProductLanguageID = '{0}' ", Ecommerce.Common.Context.LanguageID);
sql.Append("ORDER BY UnitsSold DESC");
col = Product.GetProductBySql(sql.ToString());
Cache.Current.AddOrUpdate(cacheKey, col, new CacheItemPolicy { AbsoluteExpiration = DateTime.Now.AddMinutes(cachingTimeoutInMinutes) });
}
return col;
}
