Show information from a custom stored procedure

This is a rough guide to fetching and displaying 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>
}