Schedule XForm data mailings

Our client did not want an email very single time a customer filled in a form, but instead wanted a scheduled mailing with all entries.

As I could not find a way to add additional options to the ChannelOptions I decided to overrule the functionality of the "Email" option.

If we take Alloy (WebForms version) as an example, you can do this on two locations. If you want this functionality on all forms, you can add it to the XFormInitialization module. If you want it only on specific pages / blocks you can do this there. The mechanism is the same.

If the "Email" option is selected on the form, I’ll create an entry in the DDS. These are stored in a specific object I called "XFormsEmailContent".

using System;

    using EPiServer.Data.Dynamic;

    [EPiServerDataStore(AutomaticallyCreateStore = true, AutomaticallyRemapStore = true)]
    public class XFormsEmailContent
    {
        public Guid FormId { get; set; }

        public string MailFrom { get; set; }
        
        public string MailTo { get; set; }
        
        public string MailSubject { get; set; }

        public bool InProgress { get; set; }

        /// <summary>
        /// Initializes a new instance of the <see cref="T:System.Object"/> class.
        /// </summary>
        public XFormsEmailContent()
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="T:System.Object"/> class.
        /// </summary>
        public XFormsEmailContent(Guid formId, string mailFrom, string mailTo, string mailSubject)
        {
            this.FormId = formId;
            this.MailFrom = mailFrom;
            this.MailTo = mailTo;
            this.MailSubject = mailSubject;
            this.InProgress = false;
        }
    }

In this object I will store the id of the form, the email settings from the form and whether the form is being processed (in the scheduled job).

Next you will need to perform some custom actions when the form is submitted:

        private void XForm_BeforeSubmitPostedData(object sender, SaveFormDataEventArgs e)
        {
            DataStoreProvider dataStoreProvider = DataStoreProvider.CreateInstance();

            // Get the datastore for the email settings, or create one
            DynamicDataStore store = DynamicDataStoreFactory.Instance.GetStore(typeof(XFormsEmailContent))
                                     ?? DynamicDataStoreFactory.Instance.CreateStore(typeof(XFormsEmailContent));

            // Get the setting for this form 
            IQueryable<XFormsEmailContent> allItems =
                store.Items<XFormsEmailContent>().Where(xf => xf.FormId == e.FormData.FormId);

            // Get the <see cref="XFormsEmailContent"/> for this form.
            XFormsEmailContent xFormData = allItems.SingleOrDefault();

            if (e.FormData.ChannelOptions == ChannelOptions.Email)
            {
                // Always update the settings
                if (xFormData != null)
                {
                    store.Delete(xFormData);
                }

                // Create the settings object.
                xFormData = new XFormsEmailContent(
                        e.FormData.FormId,
                        e.FormData.MailFrom,
                        e.FormData.MailTo,
                        e.FormData.MailSubject);

                dataStoreProvider.ExecuteTransaction(
                     () =>
                     {
                         // Set the DataStoreProvider on store
                         store.DataStoreProvider = dataStoreProvider;
                         store.Save(xFormData);
                     });

                // Remove the email option to prevent sending the single email.
                e.FormData.ChannelOptions &= ~ChannelOptions.Email;

                // We add the DataBase option for backup.
                e.FormData.ChannelOptions |= ChannelOptions.Database;
            }
            else
            {
                if (xFormData != null)
                {
                    // Delete the setting, as the option has been changed.
                    dataStoreProvider.ExecuteTransaction(
                        () =>
                            {
                                // Set the DataStoreProvider on store
                                store.DataStoreProvider = dataStoreProvider;
                                store.Delete(xFormData);
                            });
                }
            }
        }

Look at the comments in the code for the why and what.

In the InitializationModule you can add this EventHandler like this:

public void XForm_ControlSetup(object sender, EventArgs e)
        {
            XFormControl control = (XFormControl)sender;
            control.AfterSubmitPostedData += this.XForm_AfterSubmitPostedData;
            control.BeforeSubmitPostedData += this.XForm_BeforeSubmitPostedData;
        }

If you want to add it to the FormBlockControl you will need to change the property used to render the XForm to a control

<div>
<XForms:XFormControl ID="XForm" runat="server" EnableClientScript="true" />
    </div>

Attach the EventHandler as follows

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

            this.XForm.FormDefinition = this.CurrentBlock.Form;

            this.XForm.BeforeSubmitPostedData += this.XForm_BeforeSubmitPostedData;
        }

Next you will need to create a scheduled job that processes the forms. For the CSV file creation I used XMLtoCSV. The base code for the XML creation I read somewhere., but can’t remember where anymore. So drop me a line if it’s yours, I’ll give you the credit due. The rest of the code is pretty straight forward, explanations are in the comments. Basically I get all the forms that need to be processed from the DDS. Next I create an xml and csv from the data and send it as an email.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Linq;
    using System.Net.Mail;
    using System.Reflection;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Web;
    using System.Web.Hosting;
    using System.Xml;

    using EPiServer;
    using EPiServer.BaseLibrary.Scheduling;
    using EPiServer.Core;
    using EPiServer.Data.Dynamic;
    using EPiServer.DataAbstraction;
    using EPiServer.PlugIn;
    using EPiServer.ServiceLocation;
    using EPiServer.Web;
    using EPiServer.XForms;

    using EPiServer75.Site.Models;

    using log4net;

    using Moor.XmlConversionLibrary.XmlToCsvStrategy;

    [ScheduledPlugIn(DisplayName = "Form Data Exporter",
        Description = "Extracts Form Data from all forms in the website.", SortIndex = 110)]
    public class FormDataExport : JobBase
    {
        /// <summary>
        ///     Initializes the <see cref="LogManager">LogManager</see> for the <see cref="FormDataExport" /> class.
        /// </summary>
        private static readonly ILog Logger = LogManager.GetLogger(typeof(FormDataExport));

        private bool stop;

        private DateTime fromDate, toDate;

        private string outDir;

        private string subDir;

        public FormDataExport()
            : base()
        {
            this.IsStoppable = true;
        }

        public override void Stop()
        {
            this.stop = true;
            base.Stop();
        }

        public override string Execute()
        {
            if (this.stop)
            {
                return "Job has been stopped";
            }

            StringBuilder formInfo = new StringBuilder();
            DynamicDataStore store = null;
            XFormsEmailContent xFormInProgress = null;

            try
            {
                this.Setup();

                // Add the date range
                formInfo.AppendFormat(
                    "Date range {0:dd/MM/yyyy HH:mm:ss} to {1:dd/MM/yyyy HH:mm:ss}",
                    this.fromDate,
                    this.toDate);

                formInfo.AppendLine();

                // Get the datastore for the email settings, or create one
                store = DynamicDataStoreFactory.Instance.GetStore(typeof(XFormsEmailContent)) ?? DynamicDataStoreFactory.Instance.CreateStore(typeof(XFormsEmailContent));

                // Get the forms not in progress
                List<XFormsEmailContent> emailContent = store.Items<XFormsEmailContent>().Where(e => !e.InProgress).ToList();

                foreach (XFormsEmailContent xFormsEmailContent in emailContent)
                {

                    // Double check if the forms is indeed not in progress, might be the case on load balanced environments.
                    XFormsEmailContent xFormContent = store.Items<XFormsEmailContent>().SingleOrDefault(xf => xf.FormId == xFormsEmailContent.FormId && xf.InProgress);

                    if (xFormContent != null)
                    {
                        continue;
                    }

                    // Set status to in progress
                    xFormsEmailContent.InProgress = true;
                    store.Save(xFormsEmailContent);

                    // Set the "xFormInProgress" variable, for exception handling.
                    xFormInProgress = xFormsEmailContent;

                    // Get the XForm
                    XForm xform = XForm.CreateInstance(xFormsEmailContent.FormId);

                    Dictionary<string, StringBuilder> formData = new Dictionary<string, StringBuilder>();
                    Dictionary<string, int> formFreq = new Dictionary<string, int>();

                    int numData = 0;

                    foreach (XFormData xdata in xform.GetPostedData(this.fromDate, this.toDate))
                    {
                        string pageNameKey = GetPageName(xdata.PageGuid);

                        if (string.IsNullOrWhiteSpace(pageNameKey))
                        {
                            continue;
                        }

                        if (!formData.ContainsKey(pageNameKey))
                        {
                            formData.Add(pageNameKey, new StringBuilder());
                            formFreq.Add(pageNameKey, 0);
                        }

                        string xmlData = GetXmlData(xdata);

                        formData[pageNameKey].Append(xmlData);
                        formFreq[pageNameKey]++;

                        numData++;
                    }

                    foreach (KeyValuePair<string, StringBuilder> keyValuePair in formData)
                    {
                        // Create an xml file withe the exported data
                        string xmlFile = this.SaveXmlAsFile(keyValuePair.Key, keyValuePair.Value.ToString());

                        // Create a csv file form the xml
                        string csvFile = ConvertXmlToCsv(xmlFile);

                        // Send the email
                        SendEmail(
                            xFormsEmailContent.MailFrom,
                            xFormsEmailContent.MailTo,
                            xFormsEmailContent.MailSubject,
                            xmlFile,
                            csvFile);
                    }

                    // Add info to the output
                    formInfo.AppendFormat("Form: {0}, {1} (", xform.FormName, numData);

                    foreach (KeyValuePair<string, int> keyValuePair in formFreq)
                    {
                        formInfo.AppendFormat("{0} = {1};", keyValuePair.Key, keyValuePair.Value);
                    }

                    formInfo.AppendLine(")");

                    // The form wass processed, so the status can be set to false again
                    xFormsEmailContent.InProgress = false;

                    store.Save(xFormsEmailContent);

                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex.Message, ex);

                if (store == null || xFormInProgress == null)
                {
                    throw;
                }

                xFormInProgress.InProgress = false;
                store.Save(xFormInProgress);

                throw;
            }

            this.SaveInfoAsFile(formInfo.ToString());

            this.CleanupFiles();

            return formInfo.ToString().Replace("\n\r",@"<br\>");
        }

        /// <summary>
        /// Sets up the environment required by this scheduled job.
        /// </summary>
        private void Setup()
        {
            // Create the main directory
            this.outDir = string.Format("{0}/ExportedFormData/", HostingEnvironment.MapPath("~/App_Data"));

            // Create the sub directory for the specific job
            this.subDir = string.Format("{0}{1}", this.outDir, DateTime.Now.ToString("yyyyMMdd_MMM_dd_yyyy"));

            if (!Directory.Exists(this.outDir))
            {
                Directory.CreateDirectory(this.outDir);
            }

            if (!Directory.Exists(this.subDir))
            {
                Directory.CreateDirectory(this.subDir);
            }

            // Get the last succesful execution date. This is the starting point for the export.
            DateTime lastRunDate = GetLastSuccessfulExecutionDate() ?? DateTime.MinValue;

            this.fromDate = lastRunDate;
            this.toDate = DateTime.Now;
        }

        /// <summary>
        /// Sends the email.
        /// </summary>
        /// <param name="mailFrom">The mail from.</param>
        /// <param name="mailTo">The mail to.</param>
        /// <param name="mailSubject">The mail subject.</param>
        /// <param name="xmlFileName">Name of the XML file.</param>
        /// <param name="csvFileName">Name of the CSV file.</param>
        private void SendEmail(string mailFrom, string mailTo, string mailSubject, string xmlFileName, string csvFileName)
        {
            MailMessage message = new MailMessage
                                      {
                                          IsBodyHtml = true,
                                          Body = string.Empty,
                                          From = new MailAddress(mailFrom),
                                          Subject = mailSubject
                                      };

            message.Attachments.Add(new Attachment(xmlFileName));
            message.Attachments.Add(new Attachment(csvFileName));

            message.To.Add(mailTo);

            using (SmtpClient client = new SmtpClient())
            {
                client.Send(message);    
            }
        }

        /// <summary>
        /// Gets the name of the page.
        /// </summary>
        /// <param name="guid">The unique identifier.</param>
        /// <returns>System.String.</returns>
        private static string GetPageName(Guid guid)
        {
            PermanentContentLinkMap map = PermanentLinkMapStore.Find(guid) as PermanentContentLinkMap;

            if (map == null || ContentReference.IsNullOrEmpty(map.ContentReference))
            {
                return "Form used on block";
            }

            IContentRepository repo = ServiceLocator.Current.GetInstance<IContentRepository>();

            PageData pageData;

            return repo.TryGet(map.ContentReference, out pageData) ? pageData.PageName : null;
        }

        /// <summary>
        /// Gets the XML data.
        /// </summary>
        /// <param name="xdata">The xdata.</param>
        /// <returns>System.String.</returns>
        private static string GetXmlData(XFormData xdata)
        {
            StringBuilder xml = new StringBuilder();

            xml.AppendLine(" <record>");

            xml.AppendFormat("  <{0}>{1}</{0}>", "FullDateTime", xdata.DatePosted.ToString("yyyy-MM-dd HH:mm:ss"));
            xml.AppendLine();

            XmlNode instanceNode = xdata.Data.SelectSingleNode("/instance");

            if (instanceNode != null)
            {
                foreach (XmlNode node in instanceNode.ChildNodes.Cast<XmlNode>().Where(node => node.Name != "Scheduled"))
                {
                    xml.AppendFormat("  <{0}>{1}</{0}>", node.Name, HttpUtility.HtmlEncode(node.InnerText));
                    xml.AppendLine();
                }
            }

            xml.AppendLine(" </record>");

            return xml.ToString();
        }

        /// <summary>
        /// Saves the XML as file.
        /// </summary>
        /// <param name="filename">The filename.</param>
        /// <param name="contents">The contents.</param>
        /// <returns>System.String.</returns>
        private string SaveXmlAsFile(string filename, string contents)
        {

            filename = filename.Replace(" ", "_");

            string regexSearch = new string(Path.GetInvalidFileNameChars()) + new string(Path.GetInvalidPathChars());
            Regex r = new Regex(string.Format("[{0}]", Regex.Escape(regexSearch)));
            filename = r.Replace(filename, "");

            filename = this.subDir + "\\" + filename + ".xml";

            using (StreamWriter sw = File.CreateText(filename))
            {
                sw.WriteLine("<FormPostings>");
                sw.Write(contents);
                sw.WriteLine("</FormPostings>");
            }

            return filename;
        }

        /// <summary>
        /// Converts the XML to CSV.
        /// </summary>
        /// <param name="filename">The filename.</param>
        /// <returns>System.String.</returns>
        private static string ConvertXmlToCsv(string filename)
        {
            XmlToCsvUsingDataSet converter = new XmlToCsvUsingDataSet(filename);
            XmlToCsvContext context = new XmlToCsvContext(converter);

            string csvFileName = filename.Replace(".xml", ".csv");

            foreach (string xmlTableName in context.Strategy.TableNameCollection)
            {
                context.Execute(xmlTableName, csvFileName, Encoding.Unicode);
            }

            return csvFileName;
        }


        /// <summary>
        /// Saves the information as file.
        /// </summary>
        /// <param name="info">The information.</param>
        private void SaveInfoAsFile(string info)
        {
            string filename = this.subDir + "\\info.txt";

            using (StreamWriter sw = File.CreateText(filename))
            {
                sw.Write(info);
            }
        }

        /// <summary>
        /// Gets the last successful execution date.
        /// </summary>
        /// <returns>System.Nullable&lt;DateTime&gt;.</returns>
        private static DateTime? GetLastSuccessfulExecutionDate()
        {
            Type thisType = typeof(FormDataExport);
            string typeName = thisType.FullName;
            string assemblyName =
                Assembly.GetAssembly(thisType).GetName().Name;

            ScheduledJob thisJob =
                ScheduledJob.Load("Execute", typeName, assemblyName);
            DataTable log = thisJob.LoadLog();
            DataRow[] successFullExecutions = log.Select(
                "Status = 0", "Exec DESC");

            DateTime? lastSuccessfullExecution = new DateTime?();
            if (successFullExecutions.Length > 0)
                lastSuccessfullExecution =
                    (DateTime)successFullExecutions[0]["Exec"];

            return lastSuccessfullExecution;
        }

        /// <summary>
        /// Cleans up the files.
        /// </summary>
        private void CleanupFiles()
        {
            try
            {
                Directory.Delete(this.subDir, true);
            }
            catch (Exception exception)
            {
                Logger.Error("Error deleting exported files", exception);
            }
        }

    }

Note that this is all for a WebForms solution. I’m working in getting this to work in MVC, which is more complicated.

Advertisements

4 thoughts on “Schedule XForm data mailings

  1. Jeroen, nice article. Thank you for sharing. I am also doing the similar thing in the MVC, I can’t see too much difference. The most important thing is to figure out where to hook up the XForm events. Thanks for XFormActionHelper that gives me everything I need. I hope the following code snippet can help you to get the MVC version done quickly.

    public override ActionResult Index(XFormBlock currentBlock)
    {
    XFormActionHelper.BeforeSubmitPostedData += XFormActionHelper_BeforeSubmitPostedData;
    }

    Like

  2. Hi,

    How can I get the xForm Posted data with fields name. I have a page for poll with xForm property.The users submit the form with selection. Now I want to display the poll results and also want to calculate the poll statistics.

    foreach (PollPage page in pollPages)
    {

    foreach (XFormData formData in page.Form.GetPostedData())
    {

    //How can I get the formData values and name of the fields

    }
    }

    Like

    1. if you have the FormData you can loop through the values like this:

      var formValueCollection = e.FormData.GetValues();
      foreach (string key in formValueCollection.AllKeys)
      {
      Response.Write(string.Format("{0}: {1}\r\n", key, formValueCollection[key]));
      }

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s