How to query Decision Insight data and send a status email

Context

 You want to send an email every hour describing the Account statuses.

Create the query

In data integration menu, open the Queries tab and create a new query:

• Name: getAccountStatuses
• Dimensions: Account
• Inputs: customerName, inProcessCount, inProcessCountStatus

Save this new query:

Query parameters

The Parameters panel references the existing parameters used for the query. The ReferenceTime parameter will always be present. It is used to indicate the reference time for query execution.

Create a route that sends the result by email once an hour

Enable use of camel QUARTZ component for hourly query

See Library in Camel QUARTZ component documentation

Enable use of Camel MAIL component

See Library in Camel MAIL component documentation.

Enable result formatting

To format the result of the query in a mail we will use the templating engine called stringtemplate. The template will be saved as a new Resource.

Create a new resource that will build the mail message from the results of the query:

• Name: mail_content
• Type: Text
• Charset: UTF-8

• Content: 

  <strong>Last accounts status</strong>
  <br>
  <table border="1">
  <tr>
  <th border="1" align="left"> Customer </th> 
  <th border="1" align="left"> Payments in process </th> 
  <th border="1" align="left"> Status </th>
  </tr>
  $body.rows : {row |
  <tr>
  <td border="1">  $row.customerName.value$</td>
  <td border="1" align="center" padding="5">$row.inProcessCount.value$</td>
  <td border="1" align="right">$
  if(row.inProcessCountStatus.valueByIndicatorDataName)$   $row.inProcessCountStatus.valueByIndicatorDataName.Label$ $
  else$ null $
  endif$</td>
  </tr>
  }$</table>
  

Route configuration

Create a routing context to send emails. The route must contain a number of the properties used to define the receiver, the sender, the server URL and the password of the email account.

Create properties for your route

On the left menu, click Properties and define the following properties:

• emailReceiver – the email of the person who will receive the email
• mailServerHost – the host name of the mail server
• emailSender – the login used to send the mail.
• emailPassword – the password of the account used to send the mail.

Create the route

In the data integration menu, open the Routes tab and create a new route:

• Name: sendAccountStatuses

• Definition: 

  <?xml version="1.0" encoding="UTF-8"?>
  <routes xmlns="http://camel.apache.org/schema/spring" xmlns:u="http://www.systar.com/aluminium/camel-util">
      <route>
          <from uri="quartz://loop?cron=0+1+*+*+*+?"/> <!-- the route is executed every hour -->
          <!-- Build the parameter map for the query -->
          <setProperty propertyName="tnd-query.parameters">
              <u:map-create>
  				<!-- "ReferenceTime" entry is mandatory -->
                  <u:map-entry key="ReferenceTime">
                      <u:date-add date="NOW" scalar="-1" type="minutes"/> <!-- remove one minute to reproduce product lag and avoid null results -->
                  </u:map-entry>
              </u:map-create>
          </setProperty>
          <to uri="tnd-query:getAccountStatuses"/>
          <to uri="string-template:tnd-resource/mail_content?delimiterStart=$&amp;delimiterStop=$"/>
          <setHeader headerName="subject">
              <constant>Accounts status</constant>
          </setHeader>
          <setHeader headerName="to">
              <constant>{{emailReceiver}}</constant>
          </setHeader>
          <!--Send the email -->
          <to uri="smtps://{{mailServerHost}}?contentType=text/html&amp;username={{emailSender}}&amp;password={{emailPassword}}"/>
      </route>
  </routes>
  
  

Save and then launch the route. Every hour on the hour, an email containing the result is sent: 

To see this example in action, you can use this application and properties containing the example.