Developer forum

Forum » Development » Extending Ecom statistics

Extending Ecom statistics

Kurt Moskjær Andersen
Kurt Moskjær Andersen
Reply

Hi,

My customer would like to show the product number instead of the product name, when viewing the MostViewedProducts and MostCriticalItems from the Ecommerce -> Statistics.

Is it possible to extend the statistics with a minimal effort or am I using a sledgehammer to crack a nut?

--
Best regards
Kurt Moskjaer Andersen


Replies

 
Nicolai Pedersen
Nicolai Pedersen
Reply
This post has been marked as an answer

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;
        }
    }
}
Votes for this answer: 1
 
Kurt Moskjær Andersen
Kurt Moskjær Andersen
Reply

Hi Nicolai,

Thank you, I will go by that.

Best regards
Kurt

 

You must be logged in to post in the forum