Posted on 10/05/2022 16:12:23
Hi Kurt
Ecom stats is just plugins, so you can create your own ones.
Below the code for the report in question - you probably just have to find the correct place and add the product number.
using System;
using System.Collections;
using System.Data;
using Dynamicweb.Data;
using Dynamicweb.Ecommerce.Common;
using Dynamicweb.Ecommerce.Extensibility.Editors;
using Dynamicweb.Ecommerce.Products;
using Dynamicweb.Ecommerce.Shops;
using Dynamicweb.Extensibility.AddIns;
using Dynamicweb.Extensibility.Editors;
namespace Dynamicweb.Ecommerce.Statistics
{
/// <summary>
/// Represents the statistics about the most viewed products.
/// </summary>
[AddInName("Most Viewed Products")]
[AddInDescription("Shows Most Viewed Products")]
[AddInGroup("Products")]
[AddInImage("tree/btn_eyeglasses.png")]
public class MostViewedProducts : StatisticsProvider, IDropDownOptions
{
private DataTable _statDataTable = null;
/// <summary>
/// Gets or sets start date value for statistics viewing period.
/// </summary>
[AddInParameter("Date from")]
[AddInParameterEditor(typeof(DateTimeParameterEditor), "")]
public DateTime DateFrom { get; set; } = DateTime.Now.AddMonths(-3);
/// <summary>
/// Gets or sets end date value for statistics viewing period.
/// </summary>
[AddInParameter("Date to")]
[AddInParameterEditor(typeof(DateTimeParameterEditor), "")]
public DateTime DateTo { get; set; } = DateTime.Now;
/// <summary>
/// Gets or sets the parent group.
/// </summary>
[AddInParameter("Parent group")]
[AddInParameterEditor(typeof(ProductGroupParameterEditor), "")]
public string ParentGroup { get; set; } = "";
/// <summary>
/// Gets or sets the sub group.
/// </summary>
[AddInParameter("Sub groups")]
[AddInParameterEditor(typeof(YesNoParameterEditor), "")]
public bool SubGroups { get; set; } = false;
/// <summary>
/// Gets or sets the language value for statistic filter.
/// </summary>
[AddInParameter("Language")]
[AddInParameterEditor(typeof(DropDownParameterEditor), "none=false")]
public string Language { get; set; } = string.Empty;
/// <summary>
/// Gets or sets shop value for statistics filter.
/// </summary>
[AddInParameter("Shop")]
[AddInParameterEditor(typeof(DropDownParameterEditor), "nonetext=Alle")]
public string Shop { get; set; } = "";
/// <summary>
/// Gets the X-axis column name.
/// </summary>
public override string XAxisColumn
{
get
{
return "ProductName";
}
}
/// <summary>
/// Gets the Y-axis column name.
/// </summary>
public override string YAxisColumn
{
get
{
return "ElementCount";
}
}
/// <summary>
/// Gets the color column name.
/// </summary>
public override string ColorColumn
{
get
{
return "Color";
}
}
/// <summary>
/// Gets the prefix.
/// </summary>
public override string Prefix
{
get
{
return "";
}
}
/// <summary>
/// Gets the default graph type.
/// </summary>
public override GraphType DefaultGraph
{
get
{
return GraphType.Column;
}
}
/// <summary>
/// Gets the data table.
/// </summary>
public override DataTable Data
{
get
{
if (_statDataTable is null)
{
_statDataTable = CollectData();
}
return _statDataTable;
}
}
/// <summary>
/// Implements <see cref="IDropDownOptions.GetOptions">IDropDownOptions.GetOptions</see> method, and used for DropDownLists control.
/// </summary>
/// <param name="Name">Specified current control name.</param>
/// <returns>Hashtable with specified control options.</returns>
public Hashtable GetOptions(string name)
{
// Get Hashtable
var options = new Hashtable();
switch (name ?? "")
{
case "Language":
{
options.Add("", "All");
foreach (International.Language language in Application.Languages)
options.Add(language.LanguageId, language.Name);
break;
}
case "Shop":
{
foreach (Shop shop in Application.Shops)
options.Add(shop.Id, shop.Name);
break;
}
}
// Return the hashtable
return options;
}
/// <summary>
/// Gets the subgroups.
/// </summary>
/// <param name="group">The group.</param>
/// <param name="shop">The shop.</param>
/// <param name="groupIds">Subgroup ids.</param>
/// <param name="counter">The counter.</param>
/// <returns>Subgroup ids.</returns>
public string GetSubGroups(Group group, Shop shop, string groupIds, int counter)
{
foreach (Group subGroup in Services.ProductGroups.GetSubgroups(group))
{
groupIds += "," + subGroup.Id;
if (Services.ProductGroups.HasSubgroups(subGroup))
{
groupIds += GetSubGroups(subGroup, shop, groupIds, counter + 1);
}
}
return groupIds;
}
private DataTable CollectData()
{
var dataTable = new DataTable("eCom_Statistic_MostViewedProduct");
try
{
dataTable.Columns.Add("Color", typeof(string));
dataTable.Columns.Add("ElementCount", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
var sqlBuilder = new CommandBuilder();
sqlBuilder.Add("SELECT DISTINCT TOP(20) pr.productID, pr.ProductName, t.ElementCount ");
sqlBuilder.Add("FROM (SELECT Statv2ObjectElement, COUNT(Statv2ObjectElement) AS ElementCount ");
sqlBuilder.Add("FROM Statv2Object ");
sqlBuilder.Add("WHERE Statv2ObjectType = 'eCom_Prod' AND ");
sqlBuilder.Add("(Statv2ObjectTimestamp Between {0} AND {1}) ", DateFrom, DateTo);
sqlBuilder.Add("GROUP BY Statv2ObjectElement) as t ");
sqlBuilder.Add("INNER JOIN EcomProducts AS pr ON pr.ProductID = t.Statv2ObjectElement "); // COLLATE Latin1_general_CI_AS
sqlBuilder.Add("INNER JOIN EcomGroupProductRelation AS prGr ON prGr.GroupProductRelationProductID = t.Statv2ObjectElement "); // COLLATE Latin1_general_CI_AS - Collate statement greatly reduces performance
sqlBuilder.Add("INNER JOIN EcomShopGroupRelation AS prShop ON prShop.ShopGroupGroupID = prGr.GroupProductRelationGroupID ");
if (!string.IsNullOrWhiteSpace(ParentGroup))
{
sqlBuilder.Add("WHERE GroupProductRelationGroupID = {0} ", ParentGroup);
}
if (!string.IsNullOrWhiteSpace(Shop))
{
if (!string.IsNullOrWhiteSpace(ParentGroup))
{
sqlBuilder.Add("AND ShopGroupShopID = {0} ", Shop);
}
else
{
sqlBuilder.Add("WHERE ShopGroupShopID = {0} ", Shop);
}
}
if (!string.IsNullOrEmpty(Language))
{
if (!string.IsNullOrWhiteSpace(ParentGroup) || !string.IsNullOrWhiteSpace(Shop))
{
sqlBuilder.Add("AND pr.ProductLanguageID = {0} ", Language);
}
else
{
sqlBuilder.Add("WHERE pr.ProductLanguageID = {0} ", Language);
}
}
sqlBuilder.Add("ORDER BY t.ElementCount DESC ");
using (var dataReader = Database.CreateDataReader(sqlBuilder))
{
while (dataReader.Read())
{
var dataRow = dataTable.NewRow();
dataRow["Color"] = "c00000";
dataRow["ElementCount"] = dataReader["ElementCount"];
dataRow["ProductName"] = dataReader["ProductName"];
dataTable.Rows.Add(dataRow);
}
}
}
catch
{
dataTable.Dispose();
dataTable = null;
throw;
}
return dataTable;
}
}
}
