Show information from a custom stored procedure

This is a rough guide to fetching and displaying in an HTML Template email arbitrary data from a custom stored procedure via the Custom/Execute REST API endpoint.

NOTE: This may not be the best way to do things—edits, additions, and improvements to this document are very welcome.

In this example, we use a CUSTOMER_ORDERS stored procedure to include a summary of a constituent's recent orders in a Constituent Info template.

@using Tessitura.Services.Common.Client.Utils;
@using Tessitura.Service.Client.CRM;
@using Tessitura.Service.Client.Custom;

@* Use Newtonsoft library to parse fields from JSON data *@
@using Newtonsoft.Json;

@{
  // Define an OrderInfo object to parse JSON data from fields returned by the
  // custom query to attributes of the correct type.
  @functions {
    public class OrderInfo
    {
      [JsonPropertyAttribute("order_no")]
      public int OrderNo;
      [JsonPropertyAttribute("order_total")]
      public double OrderTotal;
      [JsonPropertyAttribute("last_update_dt")]
      public DateTime LastUpdate;
    }
  }

  // Find orders that have been updated in the last 30 days
  DateTime now = DateTime.Now;
  DateTime ordersFromDate = now.AddDays(-30);

  // Define request parameters for CUSTOMER_ORDERS custom stored procedure
  LocalProcedureRequest localProcedureRequest = new LocalProcedureRequest
  {
    ProcedureName = "CUSTOMER_ORDERS",
    ParameterValues = new ParameterValues
    {
      // Constituent ID is made available in Constituent Info templates
      new ParameterValue
      {
        Name = "constituent_id",
        Value = Model.ConstituentSnapshot.Id.ToString()
      },
      new ParameterValue
      {
        Name = "start_dt",
        Value = ordersFromDate.ToString("yyyy-MM-dd")
      }
    }
  };

  // Fetch data from stored procedure CUSTOMER_ORDERS via /Custom/Execute API
  // endpoint and parse JSON response data into a list of `OrderInfo` objects
  var orders = Model.RestClient
    .AtUrl("Custom/Execute")
    // Request JSON data in response
    .WithContentType(ContentType.Json)
    // POST request, with defined types to (de)serialize req/resp data
    .Post<LocalProcedureRequest, List<OrderInfo>>
    (localProcedureRequest)
    .ResponseObject;

  @*
  // Uncomment to see JSON response data in the HTML Template editor
  <pre>
    @Raw(Newtonsoft.Json.JsonConvert.SerializeObject(
      @orders,
      Newtonsoft.Json.Formatting.Indented
    ));
  </pre>
  *@
}

@if (orders.Count > 0)
{
  <h1>Customer Orders</h1>

  <ul>
    @foreach (var order in orders)
    {
    <li>
      @order.OrderNo -
      @order.OrderTotal.ToString("C") -
      @order.LastUpdate.ToString("dd MMMM yyyy HH:mm tt")
    </li>
    }
  </ul>

  <p>
    TOTAL: @orders.Sum(o => o.OrderTotal).ToString("C")
  </p>
}
Related
Recommended