Sitecore Commerce - Create and export Monthly reports

The Task

In our Commerce implmentation, a Customer will have an EventAttendee component attached to it when purchasing a on-demand video. In the component, there will be information about the video itself souch as ProductId, SKU, OrderId, etc, as well as the DateCompleted, the field that will determine weather the Customer should be included in the monthly report or not.

So when tasked with making a report including all the customers that consumed an on-demand video in a given month, we needed to solve two problems:

• How to query only customers that consumed an on-demand video in a given month. We need to get only the Customers that have a EventAttendee component, and within it DateCompleted set within the month a report is being generated for.
• How to schedule generating said report at the beginning of each month. We need a report automatically generated on the 1st of each month.

Getting the Customers

Instead of querying all the customers and iterating trough each one, we will be using Managed lists.

This is the abstracted code snippet that we use to update the DateCompleted field. Before persisting the changes, we will add a new entry to ListMembership component, making this Customer part of the said Managed list.

//code for updating DateCompleted field
//eventAttendeeComponent is our custom component
eventAttendeeComponent.DateCompleted = DateTime.Now;

//adding new entry to ListMembership component of a customer
//customer is of type Sitecore.Commerce.Plugin.Customers.Customer
var listComponent = customer.GetComponent<TransientListMembershipsComponent>(); 
var listName = "OnDemand-Complete-2022-01";
listComponent.Memberships.Add(listName);

Persisting the Customer commerce entity will create the OnDemand-Complete-2022-01 list if it did not exist previously.

Now all we need to do to get all the Customers that consumed an on-demand video in January of 2022 is to query this list:

var customersList = await this.Command<FindEntitiesInListCommand>().Process<Customer>(commerceContext, "OnDemand-Complete-2022-01", 0, Int32.MaxValue);

Scheduling the Report generation

Our task should be run every 1st day of each month, at a precise time we selected. To do so, first we considered using build-in tools we have on our disposal, Sitecore's built-in task scheduler and/or Commerce minions. None of which would satisfy our requirement because of the interval-based nature of the scheduled execution.

To get the better precision and flexibility in defining our task execution schedule, we decided to use SiteCron.

Along with installing the SiteCron NuGet package (be mindful of the version, we installed 3.5.0 for our Sitecore 9.2 installation) you will get a .zip Sitecore package to be installed in your Sitecore instance, whitch will give you SiteCron tasks templates, example tasks, paths and structure.

SiteCron Tasks will take three parameters:

• A Cron Expression (e.g: 0 46 15 * * ?) in format: Seconds Minutes Hours Day-of-Month Month Day-of-Week Year (year is optional)
• Assembly to be executed (specify class namespace, assembly)
• Optional list of parameters in the form of url parameteres (e.g. test=1&p=first&t=second)

So our SiteCron Task Sitecore item will look like this:

Cron expression - this will trigger each 1st of the month at 03:00 AM

 0 0 3 1 1/1 ?

Assembly name and namespace - we created ScheduledTask Feature to store all the Jobs

ThinkingEngineersWeb.Feature.ScheduledTasks.Jobs.MonthlyReport, ThinkingEngineersWeb.Feature.ScheduledTasks

Task additional parameters - we are sending url and credentials for FTP server where we want our reports stored

url=ftp://XXX.XXX.XXX.XXX/reports&username=ourUser&password=ourPassword

So in our Sitecore XP project, in ScheduledTasks Feature, we create a class inherriting from Quartz.IJob and implementing Execute method

public class MonthlyReportTask : IJob
{
    public void Execute(IJobExecutionContext context)
    {
        //getting the parameters from the Task
        var dataMap = context.JobDetail.JobDataMap;
        var rawParameters = dataMap.GetString(SitecronConstants.FieldNames.Parameters);
        var parameters = WebUtil.ParseUrlParameters(rawParameters);
        var taskInfo = new ReportTaskInfo
        {
            DateToQuery = new DateTimeOffset(new DateTime(DateTimeOffset.Now.Year, DateTimeOffset.Now.Month, 1).AddDays(-1)),
            FtpRepositoryInfo = new FtpRepositoryInfoModel
            {
                FtpRepositoryUrl = parameters["url"],
                FtpRepositoryUsername = parameters["username"],
                FtpRepositoryPassword = parameters["password"]
            }
        };

        //call our Commerce ServiceProxy command
        ourCommerceProxyServices.GenerateMonthlyReport(taskInfo);
    }
}

So this way we can schedule our report generation in SiteCron Task, it will trigger MonthlyReportTask that will in turn call our Commerce Command. We can also manualy trigger SiteCron Tasks by clicking "Execute SiteCron Job Now!" from their context-menu.

This has been implemented and tested on Sitecore Experience Commerce 9.2