Episerver Find Report Generator

EpiserverFindReportClient.cs

using System;
using System.Collections.Generic;
using System.Linq;
using AlloyDemoKit.FindReport.Client.Entities;
using EPiServer.Find;
using EPiServer.ServiceLocation;
using WebGrease.Css.Extensions;

namespace AlloyDemoKit.FindReport.Client
{
    [ServiceConfiguration(ServiceType = typeof(IEpiserverFindReportClient))]
    public class EpiserverFindReportClient : IEpiserverFindReportClient
    {
        private readonly IClient _client;

        public EpiserverFindReportClient(IClient client)
        {
            _client = client;
        }

        public IReadOnlyList<IQueryCount> GetQueriesWithoutHits(DateTime from, DateTime to)
        {
            var topQueryCommand = _client.NewCommand(context => new TopQueryCommand(context, _client.DefaultIndex, from, to, 100, Type.NoHits));
            return topQueryCommand.Execute().Hits.Select(x => new QueryCount(x.Query, x.Count))
                .ToSafeReadOnlyCollection();
        }

        public IReadOnlyList<IQueryCount> GetQueriesWithoutRelevantHits(DateTime from, DateTime to)
        {
            var topQueryCommand = _client.NewCommand(context => new TopQueryCommand(context, _client.DefaultIndex, from, to, 100, Type.NoRelevantHits));
            return topQueryCommand.Execute().Hits.Select(x => new QueryCount(x.Query, x.Count))
                .ToSafeReadOnlyCollection();
        }

        public IReadOnlyList<IQueryCount> GetTopQueries(DateTime from, DateTime to)
        {
            var topQueryCommand = _client.NewCommand(context => new TopQueryCommand(context, _client.DefaultIndex, from, to, 100, Type.Top));
            return topQueryCommand.Execute().Hits.Select(x => new QueryCount(x.Query, x.Count))
                .ToSafeReadOnlyCollection();
        }
    }
}

FindReportGenerator.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="FindReportGenerator.aspx.cs" Inherits="AlloyDemoKit.FindReport.FindReportGenerator" %>
<%@ Register TagPrefix="EPiServerUI" Namespace="EPiServer.UI.WebControls" Assembly="EPiServer.UI, Version=11.17.1.0, Culture=neutral, PublicKeyToken=8fe83dea738b45b7" %>

<asp:content contentplaceholderid="MainRegion" runat="server">
    <div class="epi-formArea epi-padding">
        <div class="epi-size15">
            <div>
                <asp:Label runat="server" AssociatedControlID="fromInputDate" Text="From" />
                <EPiServer:InputDate runat="server" style="display: inline;" ID="fromInputDate" DisplayTime="false" ValidateInput="true" />
            </div>
            <div>
                <asp:Label runat="server" AssociatedControlID="toInputDate" Text="To" />
                <EPiServer:InputDate runat="server" style="display: inline;" ID="toInputDate" DisplayTime="false" ValidateInput="true" />
            </div>
            <div class="epi-indent">
                <asp:CheckBox runat="server" ID="includeTopQueriesInput" Checked="True" />
                <asp:Label runat="server" AssociatedControlID="includeTopQueriesInput" Text="Include most frequent searches" />
            </div>
            <div class="epi-indent">
                <asp:CheckBox runat="server" ID="includeQueriesWithoutHits" Checked="True" />
                <asp:Label runat="server" AssociatedControlID="includeQueriesWithoutHits" Text="Include searches without hits" />
            </div>
            <div class="epi-indent">
                <asp:CheckBox runat="server" ID="includeQueriesWithoutRelevantHits" Checked="True" />
                <asp:Label runat="server" AssociatedControlID="includeQueriesWithoutRelevantHits" Text="Include searches without relevant hits" />
            </div>
        </div>
    </div>
    <div class="epi-buttonContainer">
        <EPiServerUI:ToolButton id="Save" DisablePageLeaveCheck="true" OnClick="Generate" runat="server" SkinID="Save" text="Generate" ToolTip="Generate" />
    </div>
</asp:content>

FindReportGenerator.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;
using AlloyDemoKit.FindReport.Client;
using AlloyDemoKit.FindReport.Client.Entities;
using EPiServer;
using EPiServer.PlugIn;
using EPiServer.ServiceLocation;
using EPiServer.Shell.WebForms;
using EPiServer.Web.WebControls;
using OfficeOpenXml;

namespace AlloyDemoKit.FindReport
{
    [GuiPlugIn(Area = PlugInArea.ReportMenu,
        DisplayName = "Find Report",
        Description = "Generate and export Episerver Find reports",
        Url = "~/FindReport/FindReportGenerator.aspx",
        Category = "Episerver Find",
        SortIndex = 5000)]
    [Authorize(Roles = "Administrators, WebAdmins, WebEditors")]
    public partial class FindReportGenerator : WebFormsBase
    {
        private readonly Injected<IEpiserverFindReportClient> _findReportClient;

        protected override void OnPreInit(EventArgs e)
        {
            base.OnPreInit(e);

            MasterPageFile = UriSupport.ResolveUrlFromUIBySettings("MasterPages/EPiServerUI.master");
        }

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);

            SystemMessageContainer.Heading = "Find Report";
            SystemMessageContainer.Description = "Generate and export Episerver Find reports.";
        }


        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                return;
            }

            var fromDate = GetFromDate();
            var toDate = GetToDate();

            if (!fromDate.HasValue)
            {
                AddValidationMessage("The from date is required.");
            }

            if (fromDate.HasValue && fromDate >= toDate)
            {
                AddValidationMessage("The to date must be later than the from date.");
            }

            Page.Validate();
        }

        /// <summary>
        /// Get the from date from the form.
        /// </summary>
        /// <returns>The from date in UTC format, or null if none is supplied.</returns>
        private DateTime? GetFromDate()
        {
            if (fromInputDate.Value == DateTime.MinValue)
            {
                return null;
            }

            var fromDate = fromInputDate.Value.Date;
            DateTime.SpecifyKind(fromDate, DateTimeKind.Utc);
            // Ensure we have the start of the day
            return fromDate.Date;
        }

        /// <summary>
        /// Get the to date from the form, or use today's date if none is specified.
        /// </summary>
        /// <returns>The to date in UTC format.</returns>
        private DateTime GetToDate()
        {
            var toDate = toInputDate.Value == DateTime.MinValue ? DateTime.UtcNow : toInputDate.Value;
            DateTime.SpecifyKind(toDate, DateTimeKind.Utc);
            // Ensure we have the end of the day
            return toDate.Date.AddDays(1).AddTicks(-1);
        }

        /// <summary>
        /// Adds a validation message if it doesn't already exist.
        /// </summary>
        /// <param name="errorMessage">The error message.</param>
        private void AddValidationMessage(string errorMessage)
        {
            if (Page.Validators.OfType<StaticValidator>().All(x => x.ErrorMessage != errorMessage))
            {
                Page.Validators.Add(new StaticValidator(errorMessage));
            }
        }
 
        protected void Generate(object sender, EventArgs e)
        {
            if (!Page.IsValid)
            {
                return;
            }

            var fromDate = GetFromDate();
            var toDate = GetToDate();

            if (!fromDate.HasValue)
            {
                return;
            }

            IDictionary<string, IReadOnlyList<IQueryCount>> results = new Dictionary<string, IReadOnlyList<IQueryCount>>();

            if (includeTopQueriesInput.Checked)
            {
                results.Add("Most frequent searches", _findReportClient.Service.GetTopQueries(fromDate.Value, toDate));
            }

            if (includeQueriesWithoutHits.Checked)
            {
                results.Add("Searches without hits", _findReportClient.Service.GetQueriesWithoutHits(fromDate.Value, toDate));
            }

            if (includeQueriesWithoutRelevantHits.Checked)
            {
                results.Add("Searches without relevant hits", _findReportClient.Service.GetQueriesWithoutRelevantHits(fromDate.Value, toDate));
            }

            using (var package = new ExcelPackage())
            {
                foreach (var result in results)
                {

                    var ws = package.Workbook.Worksheets.Add(result.Key);

                    // Add the column headings
                    ws.Cells[1, 1].Value = "Query";
                    ws.Cells[1, 1].Style.Font.Bold = true;
                    ws.Cells[1, 2].Value = "Count";
                    ws.Cells[1, 2].Style.Font.Bold = true;

                    var row = 2;

                    foreach (var query in result.Value)
                    {
                        ws.Cells[row, 1].Value = query.Query;
                        ws.Cells[row, 2].Value = query.Count;

                        row++;
                    }
                }

                var packageBytes = package.GetAsByteArray();

                var filename = fromDate.Value.Date.Equals(toDate.Date)
                    ? $"{toDate:yyyyMMdd}_find_report.xlsx"
                    : $"{fromDate.Value:yyyyMMdd}-{toDate:yyyyMMdd}_find_report.xlsx";

                Response.Clear();
                Response.ClearHeaders();
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("Content-Disposition", $"attachment; filename={filename}");
                Response.OutputStream.Write(packageBytes, 0, packageBytes.Length);
                Response.Flush();
                Response.End();

            }
        }
    }
}

IEpiserverFindReportClient.cs

using System;
using System.Collections.Generic;
using AlloyDemoKit.FindReport.Client.Entities;

namespace AlloyDemoKit.FindReport.Client
{
    public interface IEpiserverFindReportClient
    {
        IReadOnlyList<IQueryCount> GetQueriesWithoutHits(DateTime from, DateTime to);

        IReadOnlyList<IQueryCount> GetQueriesWithoutRelevantHits(DateTime from, DateTime to);

        IReadOnlyList<IQueryCount> GetTopQueries(DateTime from, DateTime to);
    }
}

IQueryCount.cs

namespace AlloyDemoKit.FindReport.Client.Entities
{
    public interface IQueryCount
    {
        string Query { get; }

        int Count { get; }
    }
}

QueryCount.cs

namespace AlloyDemoKit.FindReport.Client.Entities
{
    internal class QueryCount : IQueryCount
    {
        public QueryCount(string query, int count)
        {
            Query = query;
            Count = count;
        }

        public string Query { get; }
        public int Count { get; }
    }
}

TopQueryCommand.cs

using System;
using AlloyDemoKit.FindReport.Client.Entities;
using EPiServer.Find.Api;
using EPiServer.Find.Connection;
using EPiServer.Find.Statistics.Api;

namespace AlloyDemoKit.FindReport.Client
{
    internal class TopQueryCommand : Command
    {
        private readonly string _index;
        
        private readonly DateTime _from;
        private readonly DateTime _to;
        private readonly int _size;
        private readonly string _type;

        public TopQueryCommand(ICommandContext commandContext, string index, DateTime from, DateTime to, int size, Type type) : base(commandContext)
        {
            _index = index;
            _from = from;
            _to = to;
            _size = size;
            _type = GetType(type);
        }

        public StatisticsHitsResult<TopQueryResult> Execute()
        {
            string url = GetUrl();
            var request = CommandContext.RequestFactory.CreateRequest(url, HttpVerbs.Get, ExplicitRequestTimeout);
            return GetResponse<StatisticsHitsResult<TopQueryResult>>(request);
        }

        private string GetUrl()
        {
            return $"{GetServerUrl()}{_index}/_stats/query/top?from={_from:s}Z&to={_to:s}Z&size={_size}&type={_type}";
        }

        private string GetType(Type type)
        {
            switch (type)
            {
                case Type.Top:
                    return "top";
                case Type.NoHits:
                    return "null";
                case Type.NoRelevantHits:
                    return "nullclick";
                default:
                    return "top";
            }
        }
    }
}

TopQueryResult.cs

namespace AlloyDemoKit.FindReport.Client.Entities
{
    internal class TopQueryResult
    {
        public string Query { get; set; }
        public int Count { get; set; }
    }
}

Type.cs

namespace AlloyDemoKit.FindReport.Client
{
    public enum Type
    {
        Top,
        NoHits,
        NoRelevantHits
    }
}