Search
  • Medium One

Build Custom Email Reports

Updated: May 16

This tutorial shows you how to create beautiful customized email reports with charts and analytics using Medium One workflows.  


We are going to show you how to build an email report like this one:

Contact us @ support@mediumone.com if you’d like us to help build a custom report for your application.


In this tutorial, you will:

  1. Put together Python code you can use in a Workflow to generate an email report

Here’s what you need to get started:

  1. Medium One Prototyping Sandbox [Buy]

  2. Data on your Medium One account


In this example, we have a sensor located in a remote location gathering temperature readings.  We generate daily temperature reports with connectivity status.  


This workflow can be triggered in numerous ways using Medium One.


Medium One workflows are developed using python.  In this example the device data is stored in Medium One’s memory data Store and Analytics database.


Step 1. Import Libraries and Set Configuration Variables

This section imports the python libraries and Medium One services that you will need. TIME_ZONE_OFFSET configures the time scale in the chart relative to the timezone specified.

# Configuration Options:
TIME_ZONE_OFFSET = -7  

import GoogleCharts
import Email
import datetime
import Analytics
import Store
import json
from datetime import date, timedelta
import DateRange
import Filter
import DateConversion 

Step 2. Add Functions

read_json_from_store will retrieve a device profile from the data store and return it in a dict format.


get_current_iso_time returns the current date and time in iso format.


fill_zero_count_bin is used to create a bucket for the bar chart if none exists. This is to force Google charts to show bar of zero when there’s no data in a given bucket.


# Function: Read from user's Store
def read_json_from_store(key):
    data_json = Store.get(key)
    if data_json is None: 
        data_json = "{}"
    return json.loads(data_json)

# Function: Get Current Time
def get_current_iso_time():
    dtnow = datetime.datetime.now()
    dtutcnow = datetime.datetime.utcnow()
    delta = dtnow - dtutcnow
    hh,mm = divmod((delta.days * 24*60*60 + delta.seconds + 30) // 60, 60)
    return "%s%+03d:%02d" % (dtnow.isoformat(), hh, mm)

# fill in missing bins where count is zero
def fill_zero_count_bin(bin_events):
    hours = ['00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23']
    
    for hour in hours:
        found_hour_bin = False
        for event in bin_events:
            if 'observed_at' in event:
                if hour in event['observed_at'][11:13]:
                    found_hour_bin = True
        if found_hour_bin == False:
            if int(hour) <= int(now_utc.strftime('%H')):
                new_event = {'observed_at':now_utc.isoformat()[0:11]+hour+':00:00+00:00','count': 0, 'avg': 0}
            else:
                new_event = {'observed_at':yesterday_utc.isoformat()[0:11]+hour+':00:00+00:00','count': 0, 'avg': 0}
                
            log(new_event)    
            bin_events.append(new_event) 


Step 3. Initialize Datetime Variables and Load Sensor Data

Next, we initialize a series of datetime variables and load the sensor data from Store. Note: the charts and data are for the recent 24 hour period.

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# Step 1: Load Date and Profile data
#
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TIME_OFFSET = timedelta(hours=TIME_ZONE_OFFSET)

# Get current time
now = DateConversion.to_py_datetime(get_current_iso_time())

# Get now with UTC
now_utc = now.astimezone(DateConversion.UTC())

# Offset current time with Time Zone above to prepare the charts
now_w_offset = now.astimezone(DateConversion.UTC())+TIME_OFFSET

# Load device profile from Store
profile = read_json_from_store("profile")

# get date window
yesterday = now-timedelta(days=1)
yesterday_utc = now_utc-timedelta(days=1)

last_24_window = DateRange.date_range(yesterday,now) 

Step 4. Generate Temperature Bar Chart

First we query the last 24 hrs data from Medium One’s Analytics library. In this case the temperature is stored as the raw.temperature tag.


Then we build the data required for the labels on the chart. GoogleCharts.get_chart_url is used to generate the chart url images using Google charts. The end result is sensor_chart_html which contains the html tags with the chart url.


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# Step 2: Generate 24hr sensor chart 
#
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# initialize html string for sensor chart(s)
sensor_chart_html = ''

# get last 24 hrs of data bined into hourly buckets
bar_temp_24_events = Analytics.bin_by_time('raw.temperature', last_24_window, None)
    
fill_zero_count_bin(bar_temp_24_events)
# sort the results
bar_temp_24_events.sort(key=lambda x: DateConversion.to_py_datetime(x['observed_at']), reverse=False)
        
# calculate the range the average values in each bin
temperature_range = [i['avg'] for i in bar_temp_24_events]
    
# calculate min value and reduce by 10% to add margin on the chart
temperature_min = min(temperature_range)*.9
    
# calculate max value for temperature bin
temperature_max = max(temperature_range)

# building the x axis lable for the Google Chart url
x_axis_label = '0:|'
    
for event in bar_temp_24_events:
        hour_bucket = DateConversion.to_py_datetime(event['observed_at'])
        hour_bucket_w_offset = hour_bucket.astimezone(DateConversion.UTC())+TIME_OFFSET
        x_axis_label += hour_bucket_w_offset.strftime('%H')+'|'

# build url for Google Charts
bar_temperature_chart = GoogleCharts.get_chart_url(cht="bvs",  # chart type
                                           chbh=[[25,1,0]],   # bar width and spacing
                                           chs=[700, 200],   # resolution
                                           chd=[temperature_range],  # chart data
                                           chdl=["avg"],  # chart label
                                           chtt="Temperature (Degrees Celsius)",  # chart title
                                           chco=[["ffd085"]],  # chart color
                                           chxt='x,x,y',  # label axis in order
                                           chm='N*0*,000000,0,-1,11',  # specify the label format per bar
                                           chxr='2,'+str(temperature_min)+','+str(temperature_max),  # range for y axis
                                           chds= str(temperature_min)+','+str(temperature_max),  # scale for the data
                                           chxl=x_axis_label+'1:|||||||||||||Hour|')  # label for x axis
 
# build chart html 
sensor_chart_html = '<center><img src="'+bar_temperature_chart+'" style="width:100%; height:auto; border:none;"/></center><br>' 


Step 5. Generate Connectivity Pie Chart

Using a similar concept as above, we generate a pie chart showing the percentage the device was connected. In this example the percentage is calculated by the number of ‘mac’ data points received in the past 24 hour. 144 count was expected since the device is expected to transmit once per 10 minutes.

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# Step 3: Generate 24hr connectivity status 
#
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
last_24_window = DateRange.date_range(yesterday,now)

pie_24_events = Analytics.events('raw', 
                             Filter.string_tag("raw.mac"),
                             last_24_window, 144, ['event_rcv', 'ASC'])

# to calculation up time, we expect 1 event per 10 mins
on_time = round(float(len(pie_24_events)) / 144 * 100,2)
off_time = 100-on_time

pie_chart = GoogleCharts.get_chart_url(cht="p", 
                                       chs=[300, 200], 
                                       chd=[[on_time, off_time]], 
                                       chl=["Online", "Offline"], 
                                       chco='26b08c,F5A9AB',
                                       chtt="Connectivity Health")

connectivity_chart_html = '<center><img src="'+pie_chart+'"/></center><br>' 

Step 6. Generate Map

Here is a simple example of how to include the maps image.

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# Step 4: Generate Map location 
#
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
map_image_html = '<img src="http://maps.googleapis.com/maps/api/staticmap?size=500x300&markers=size:mid:S%7C'+profile['gps']+'&zoom=10"/> <br>' 

Step 7. Generate HTML and Send

Finally, we generate the html and send to the recipient! The .replace in the bottom section inserts the charts and data needed in the email report.

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# Step 5: Build HTML Emailer 
#
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
html = '''<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
   <head>
      <meta http-equiv="Content-Type" content="text/html; charset=TF-8"/>
      <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
   </head>
   <body style="background-color:#F2F2F2;">
      <center>
         <table border="0" cellpadding="0" cellspacing="0" width="100%" id="bodyTable" style="background-color:#F2F2F2;">
            <tr>
               <td align="center" valign="top" id="bodyCell" style="padding:40px 20px;">
                  <table border="0" cellpadding="0" cellspacing="0" id="emailContainer" style="width:600px;">
                     <tr>
                        <td align="center" valign="top" style="padding-bottom:30px;">
                           <table border="0" cellpadding="0" cellspacing="0" width="100%" id="emailBody" style="background-color:#FFFFFF; border-collapse:separate !important; border-radius:4px;">
                              <tr>
                                 <td align="center" valign="top" class="mobilePadding" style=" padding-top:40px; padding-right:40px; padding-bottom:0; padding-left:40px;">
                                    <a href="www.mediumone.com" target="_blank" title="Medium" style="text-decoration:none;"><img src="http://mediumone.com/assets/img/logo@2x.png" alt="MediumOne" width="150" class="logoImage" style="border:0; color:#6DC6DD !important; display:block; font-family:Raleway,Helvetica Neue,Helvetica,Arial,sans-serif; font-size:60px; font-weight:bold; height:auto !important; letter-spacing:-2px; line-height:115%; text-align:center; line-height:100%; outline:none; text-decoration:none;"/></a>
                                    <br>
                                 </td>
                              </tr>
                              <tr>
                                 <td align="left" valign="top" class="bodyContent" style="border-bottom:1px dotted #CCCCCC; border-top:1px dotted #CCCCCC; color:#606060; font-family:Raleway,Helvetica Neue,Helvetica,Arial,sans-serif; font-size:15px; line-height:150%; padding-top:40px; padding-right:40px; padding-bottom:0; padding-left:40px; text-align:left;">
                                    <center>
                                       <h1 style="color:#606060 !important; font-family:Raleway,Helvetica Neue,Helvetica,Arial,sans-serif; font-size:26px; font-weight:bold; letter-spacing:-1px; line-height:115%; margin:0; padding:0; text-align:left;">Daily Report</h1>
                                    </center>
                                    <br>
                                 </td>
                              </tr>
                              <tr>
                                 <td align="left" valign="top" class="bodyContent" style="color:#606060; font-family:Raleway,Helvetica Neue,Helvetica,Arial,sans-serif; font-size:15px; line-height:150%; padding-top:40px; padding-right:40px; padding-bottom:0px; padding-left:80px; text-align:left;">
                                    Report Time: {{report_time}}<br>
                                    All times in {{timezone}}
                                 </td>
                              </tr>
                              <tr>
                                 <td align="left" valign="top" class="mobilePadding" style="padding-top:40px; padding-right:40px; padding-bottom:0; padding-left:40px;">
                                    <table border="0" cellpadding="0" cellspacing="0" width="100%">
                                       <tr>
                                          <td style="border-bottom:1px dotted #CCCCCC; padding-top:10px;padding-bottom:10px;">
                                             <h2 style="color:#606060 !important; font-family:Raleway,Helvetica Neue,Helvetica,Arial,sans-serif; font-size:26px; font-weight:lighter; letter-spacing:-.5px; line-height:115%; margin:0; padding:0; text-align:left;">Current Status</h2>
                                          </td>
                                       </tr>
                                    </table>
                                 </td>
                              </tr>
                              <tr>
                                 <td align="left" valign="top" class="bodyContent" style="color:#606060; font-family:Raleway,Helvetica Neue,Helvetica,Arial,sans-serif; font-size:15px; line-height:150%; padding-top:40px; padding-right:40px; padding-bottom:0px; padding-left:80px; text-align:left;">
                                    <b>Device Name:</b> {{device_name}}<br>
                                    <b>WAN IP Address:</b> {{wan_ip}}<br>
                                    <b>Last Activity:</b> {{last_activity}}<br>
                                    <br>
                              </tr>
                              <td align="left" valign="top" class="mobilePadding" style="padding-top:40px; padding-right:40px; padding-bottom:0; padding-left:40px;">
                                 <table border="0" cellpadding="0" cellspacing="0" width="100%">
                                    <tr>
                                       <td style=" border-bottom:1px dotted #CCCCCC; padding-top:10px;padding-bottom:10px;">
                                          <h2 style="color:#606060 !important; font-family:Raleway,Helvetica Neue,Helvetica,Arial,sans-serif; font-size:26px; font-weight: lighter; letter-spacing:-.5px; line-height:115%; margin:0; padding:0; text-align:left;">Last 24 Hours</h2>
                                       </td>
                                    </tr>
                                 </table>
                              </td>
                              <tr>
                                 <td align="center" valign="top" class="bodyContent" style="color:#606060; font-family:Raleway,Helvetica Neue,Helvetica,Arial,sans-serif; font-size:15px; line-height:150%; padding-top:40px; padding-right:40px; padding-bottom:0px; padding-left:80px; text-align:left;">
                                    {{sensor_chart_html}}
                                    <br>
                                    {{connectivity_chart_html}}
                                    <br>
                                    <center>Device has {{on_time}} percent up time</center>
                                    <br>
                                    <br>
                              <tr>
                                 <td align="left" valign="top" class="mobilePadding" style="padding-top:40px; padding-right:40px; padding-bottom:0; padding-left:40px;">
                                    <table border="0" cellpadding="0" cellspacing="0" width="100%">
                                       <tr>
                                          <td style="border-bottom:1px dotted #CCCCCC;">
                                             <h2 style="color:#606060 !important; font-family:Raleway,Helvetica Neue,Helvetica,Arial,sans-serif; font-size:26px; font-weight: lighter; letter-spacing:-.5px; line-height:115%; margin:0; padding:0; text-align:left;">Location:</h2>
                                             <br>
                                          </td>
                                       </tr>
                                    </table>
                                 </td>
                              </tr>
                              <td align="left" valign="top" class="bodyContent" style="color:#606060; font-family:Raleway,Helvetica Neue,Helvetica,Arial,sans-serif; font-size:15px; line-height:150%; padding-top:40px; padding-right:40px; padding-bottom:0px; padding-left:80px; text-align:left;">
                                 City: {{profile_city}}<br>
                                 Country: {{profile_country}}<br>
                                 {{map_image_html}}
                                 <br>
                              </td>
                           </table>
                        </td>
                     </tr>
                  </table>
               </td>
            </tr>
         </table>
      </center>
   </body>
</html>'''.replace('{{report_time}}', now_w_offset.strftime("%b %d %I:%M %p")) \
          .replace('{{timezone}}', 'GMT'+str(TIME_ZONE_OFFSET)) \
          .replace('{{device_name}}', profile['device_name']) \
          .replace('{{wan_ip}}', profile['wan_ip']) \
          .replace('{{last_activity}}', profile['last_activity']) \
          .replace('{{sensor_chart_html}}', sensor_chart_html) \
          .replace('{{on_time}}', str(on_time)) \
          .replace('{{connectivity_chart_html}}', connectivity_chart_html) \
          .replace('{{map_image_html}}', map_image_html) \
          .replace('{{profile_city}}', profile['city'] ) \
          .replace('{{profile_state}}', profile['state']) \
          .replace('{{profile_country}}', profile['country'] if 'country' in profile else "Unknown")

recipients = ["demo@medium.one"]
sender = "support@medium.one"
email = Email.Email(sender, 'Medium One Workflow', recipients, 'Daily Report')
email.html_message(html)
email.send() 

Congrats! You have successfully created workflow code to generate a beautiful email report.


Want to learn more about what Medium One can do?

Check out our documentation and tutorials & get started on your next IoT project!

© 2018 Medium One   All Rights Reserved

4633 Old Ironsides Dr. #280, Santa Clara, CA  95054                

info@mediumone.com

|

  • YouTube - White Circle