Email Notifier

If you are using Seeq’s SaaS product, instead of this Data Lab-based notification mechanism, it is recommended that you utilize the built in notification capabilities in Seeq R60+ as described in the following Seeq Knowledge Base article: https://telemetry.seeq.com/support-link/kb/latest/cloud/notifications-on-conditions

This Notebook is intended to be scheduled from Email Notification Scheduler.ipynb. Before using this Notifier, you will need to configure the connection to the email server. Upon updating the following cell with appropriate credentials, uncomment the line

# test_email()

so that it reads

test_email()

If you see Success! in the text that appears below the cell after running it, restore the comment character # at the start of the line, click the Save (💾) icon in the toolbar above, and you’re ready to go!

# For sample SMTP setup, see https://support.google.com/mail/answer/7126229
smtp_configuration = {
    'SMTP Host': 'smtp.mycompany.com',
    'SMTP Port': 587,
    'Email Address': 'email.sender@mycompany.com',
    'Email Password': 'app.password.here'
}


def test_email():
    import smtplib
    smtp_host = smtp_configuration['SMTP Host']
    smtp_port = smtp_configuration['SMTP Port']
    smtp_username = smtp_configuration['Email Address']
    smtp_password = smtp_configuration['Email Password']

    with smtplib.SMTP(smtp_host, smtp_port) as smtp_client:
        smtp_client.starttls()
        smtp_client.set_debuglevel(1)
        smtp_client.login(smtp_username, smtp_password)
        print('Success!')

# test_email()

import json
import os
import pathlib
import re
import smtplib
from datetime import datetime, timedelta
from email.message import EmailMessage
from email.mime.application import MIMEApplication
from pathlib import Path

import pandas as pd
import pytz
import requests
from bs4 import BeautifulSoup

from seeq import spy

current_job = spy.jobs.pull()
current_job

SENT_CAPSULES_PICKLE_SUBFOLDER = Path('_Sent Capsules')
SENT_CAPSULES_PICKLE_NAME = Path(
    f'sent_capsules_for_condition_{current_job["Condition ID"]}_in_analysis_{current_job["Workbook ID"]}.pkl'
)
SENT_CAPSULES_PICKLE_PATH = SENT_CAPSULES_PICKLE_SUBFOLDER / SENT_CAPSULES_PICKLE_NAME

if not os.path.exists(SENT_CAPSULES_PICKLE_SUBFOLDER):
    os.mkdir(SENT_CAPSULES_PICKLE_SUBFOLDER)

Capsule Handlers

def update_sent_capsules(sent_capsules_df, newly_sent_capsules_df, polling_range_start):
    if sent_capsules_df.empty:
        return newly_sent_capsules_df
    if newly_sent_capsules_df.empty:
        return sent_capsules_df
    updated_df = sent_capsules_df.append(newly_sent_capsules_df, ignore_index=True).drop_duplicates('Capsule Start')
    return updated_df[updated_df['Capsule Start'] > polling_range_start].sort_values(by='Capsule Start')


def get_unsent_capsules(sent_capsules_df, pulled_capsules_df):
    if sent_capsules_df.empty or pulled_capsules_df.empty:
        return pulled_capsules_df
    return pulled_capsules_df[~pulled_capsules_df['Capsule Start'].isin(sent_capsules_df['Capsule Start'])]


def store_sent_capsules(capsules_df, to_file_path=SENT_CAPSULES_PICKLE_PATH):
    capsules_df.to_pickle(to_file_path)


def retrieve_sent_capsules(from_file_path=SENT_CAPSULES_PICKLE_PATH):
    if os.path.exists(from_file_path):
        return pd.read_pickle(from_file_path)
    else:
        return pd.DataFrame()

Email Builder

# The template should have substitution fields like {capsule["Some Property"]}, which will be replaced by the associated
# property if possible
def fill_in_template(template, capsule, job):
    import re
    capsule_substitutions = set(re.findall(r'({capsule\[\"(.*?)\"\]})', template))
    job_substitutions = set(re.findall(r'({job\[\"(.*?)\"\]})', template))
    for to_replace, prop in capsule_substitutions:
        replacement = str(capsule[prop]) if prop in capsule else '{Capsule property not found}'
        if prop in ['Capsule Start', 'Capsule End']:
            replacement = capsule[prop].astimezone(pytz.timezone(job['Time Zone'])).isoformat()
        template = template.replace(to_replace, replacement)
    for to_replace, prop in job_substitutions:
        replacement = str(job[prop]) if prop in job else '{Job property not found}'
        template = template.replace(to_replace, replacement)
    return template


def get_attachment_for_topic_document_pdf(topic_document_url):
    base_url = spy.client.host[:-4]
    screenshots_url = f'{base_url}/screenshots'
    match = re.match(r'.*/workbook/(.*?)/worksheet/(.*)', topic_document_url)
    workbook_id = match.group(1)
    worksheet_id = match.group(2)
    presentation_url = f'{base_url}/present/worksheet/{workbook_id}/{worksheet_id}'
    pdf_specs = {
        "url": presentation_url,
        "format": "PDF",
        "orientation": "Portrait",
        "paperSize": "Letter",
        "margin": "0.5in",
        "cancellationGroup": f"PDF Export {presentation_url}"
    }
    cookies = {
        'sq-auth': spy.client.auth_token
    }
    headers = {
        'Content-Type': 'application/json',
        'x-sq-csrf': spy.client.csrf_token
    }
    try:
        response = requests.post(screenshots_url, data=json.dumps(pdf_specs), cookies=cookies, headers=headers)
        pdf_url = f'{base_url}{response.headers["Location"]}'
        pdf_response = requests.get(pdf_url, cookies=cookies, headers=headers)
        filename = pathlib.PurePosixPath(pdf_url).name
        attachment = MIMEApplication(
            pdf_response.content,
            Name=filename
        )
        attachment['Content-Disposition'] = f'attachment; filename="{filename}"'
        return attachment
    except Exception as ex:
        print(ex)
        return None


def attach_file_to_msg(filename, msg):
    from os.path import basename
    with open(filename, "rb") as the_file:
        attachment = MIMEApplication(
            the_file.read(),
            Name=basename(filename)
        )
    attachment['Content-Disposition'] = f'attachment; filename="{basename(filename)}"'
    msg.attach(attachment)


def add_inline_image_with_content_id_to_msg(path_to_image, content_id, msg):
    with open(path_to_image, "rb") as img_file:
        inline_img = MIMEApplication(
            img_file.read(),
            Name=pathlib.Path(path_to_image).name
        )
    inline_img['Content-Disposition'] = 'inline'
    inline_img['Content-ID'] = content_id
    msg.attach(inline_img)


def build_email(job, capsule):
    msg = EmailMessage()
    msg.make_mixed()

    alternative_msg = EmailMessage()
    alternative_msg.make_alternative()

    related_msg = EmailMessage()
    related_msg.make_related()

    html_content = fill_in_template(job['Html Template'], capsule, job)

    soup = BeautifulSoup(html_content, 'html.parser')
    text_content = ' '.join([text for text in soup.find_all(text=True)])
    text_msg = EmailMessage()
    text_msg.set_content(text_content)
    alternative_msg.attach(text_msg)

    html_msg = EmailMessage()
    html_msg.set_content(html_content, subtype='html')

    related_msg.attach(html_msg)
    add_inline_image_with_content_id_to_msg('./Seeq Data Lab.jpg', 'sdl', related_msg)

    alternative_msg.attach(related_msg)
    msg.attach(alternative_msg)

    if job['Topic Document URL']:
        attachment = get_attachment_for_topic_document_pdf(job['Topic Document URL'])
        if attachment:
            msg.attach(attachment)

    msg['Subject'] = fill_in_template(job['Subject Template'], capsule, job)
    msg['From'] = smtp_configuration['Email Address']
    msg['To'] = job['To']
    msg['Cc'] = job['Cc']
    msg['Bcc'] = job['Bcc']

    # Here one could add support for inserting inline content in the template based on a dictionary of content IDs
    # and associated file paths specified by job_details['Inline Content'].  This would be intended for static
    # content that the admin/configurer of the Notifications would set up in advance, probably setting the
    # default value in the form for the Add-on Tool.

    return msg

def send_emails(job, unsent_capsules):
    smtp_host = smtp_configuration['SMTP Host']
    smtp_port = smtp_configuration['SMTP Port']
    smtp_username = smtp_configuration['Email Address']
    smtp_password = smtp_configuration['Email Password']

    messages_to_send = list()
    sent_capsules = list()
    exceptions = list()
    for _, capsule in unsent_capsules.iterrows():
        messages_to_send.append((capsule, build_email(job, capsule)))
    with smtplib.SMTP(smtp_host, smtp_port) as smtp_client:
        smtp_client.starttls()
        smtp_client.set_debuglevel(1)
        smtp_client.login(smtp_username, smtp_password)
        for capsule, message in messages_to_send:
            try:
                smtp_client.send_message(message)
                sent_capsules.append(capsule)
            except Exception as ex:
                exceptions.append((capsule, ex))
        smtp_client.quit()
    return (sent_capsules, exceptions)

condition = spy.search({'ID': current_job['Condition ID']})  # ID is used to ensure only one Condition in results
sent_capsules = retrieve_sent_capsules()

lookback_microseconds = int(24 * 60 * 60 * 1000 * 1000 * float(current_job['Lookback Interval']))
polling_range_end = datetime.now(pytz.utc)
polling_range_start = (polling_range_end - timedelta(microseconds=lookback_microseconds))
inception = pd.Timestamp(current_job['Inception'])
if inception > polling_range_start:
    polling_range_start = inception

capsules_starting_in_lookback_interval = spy.pull(condition, start=polling_range_start, end=polling_range_end,
                                                  tz_convert='UTC')
if capsules_starting_in_lookback_interval.empty or 'Capsule Start' not in capsules_starting_in_lookback_interval:
    capsules_starting_in_lookback_interval = pd.DataFrame()
else:
    capsules_starting_in_lookback_interval = capsules_starting_in_lookback_interval[
        capsules_starting_in_lookback_interval['Capsule Start'] > polling_range_start
        ]
capsules_starting_in_lookback_interval

unsent_capsules = get_unsent_capsules(retrieve_sent_capsules(), capsules_starting_in_lookback_interval)
unsent_capsules

try:
    emailed_capsules, exceptions = send_emails(current_job, unsent_capsules)
    print(f'Emails were sent successfully for {len(emailed_capsules)} capsules:')
    print(emailed_capsules)
    print(f'Send failed for {len(exceptions)} capsules:')
    print(exceptions)
except Exception as ex:
    emailed_capsules, exceptions = ([], [])
    print(f'Something went wrong sending emails: {ex}')

if emailed_capsules:
    start_list = [capsule['Capsule Start'] for capsule in emailed_capsules]
    newly_sent_capsules = capsules_starting_in_lookback_interval[
        capsules_starting_in_lookback_interval['Capsule Start'].isin(start_list)
    ]
    sent_capsules_updated = update_sent_capsules(sent_capsules, newly_sent_capsules, polling_range_start)
    store_sent_capsules(sent_capsules_updated)

if emailed_capsules:
    start_list = [capsule['Capsule Start'] for capsule in emailed_capsules]
    newly_sent_capsules = capsules_starting_in_lookback_interval[
        capsules_starting_in_lookback_interval['Capsule Start'].isin(start_list)
    ]
    sent_capsules_updated = update_sent_capsules(sent_capsules, newly_sent_capsules, polling_range_start)
    store_sent_capsules(sent_capsules_updated)