Generate Scheduled Email

Abstract

• Introduction
• Requirements to Undertake the Tutorial
  • Skills
  • Permissions
• Dependencies
  • env_conf.ps1
  • GenerateRowLink.ps1
  • SqlConnection.ps1
  • GetRows.ps1
  • SendEmail.ps1
  • Baseline.Common.Management.dll
  • Template.html
• Commented Powershell Script
• Configuring the Scheduled Job

Introduction

Check the Requirements

Check the Requirements to Undertake the Tutorial section to verify if you have the knowledge and admin permissions required to complete this tutorial.

Also, you need the following files for the tutorial:

• env_conf.ps1
• GenerateRowLink.ps1
• SqlConnection.ps1
• GetRows.ps1
• Baseline.Common.Management.dll
• SendEmail.ps1
• Template.html

Click here to download the archive containing the files. The Dependencies section explains each file.

This tutorial teaches you step-by-step how to create a scheduled email containing the week's rows and related links by using a Powershell script. The tutorial takes as example the following use case: a user needs a weekly email reminder listing all the rows recorded in the last 7 days; each email's entry must have a link leading to its row in the application.

All the dependencies are ready and you must only understand how the commented script example works. Then you can use the same script as a template for your own needs.

Before starting, check if you have the skills required at the Requirements to Undertake the Tutorial section. This tutorial gives for granted those requirements during the explanation.

Example

The tutorial considers the following use case to explain how to create recurrent emails through Powershell.

You have an eLegere application called Invoices.

A user must receive a weekly email listing all the invoces emitted in the last week. The list must contain as well a link to the invoice's row in the Invoices application.

In this way, the user can check the rows created the latest week and view them in the application through the links.

In this tutorial, you will follow 4 steps:

1. Unpacking all the provided dependencies to run the script in the dedicated folder for Powershell Scripts
2. Adding the information required in the configuration file env_conf.
3. Change the required settings in the Powershell script.
4. Configuring inside eLegere a Powershell Script action in the Scheduler.

Requirements to Undertake the Tutorial

Read the required list of skills and administration permissions for the eLegere installation.

If you satisfy the requirements below, you can undertake this tutorial.

Skills

This tutorial gives for granted the following skills:

• Powershell: A Basic knowledge is required to understand the script. Any introductory guide is enough to understand this tutorial's content.
• Transact SQL: A Basic knowledge is required to understand one script's part. Any introductory course to Transact SQL is enough to understand this tutorial.
• HTML: A Basic understanding of HTML is required. The HTML template provided in this tutorial as example is simple, but requires a minimal understanding of what HTML is. Any introductory guide will be enough.

Permissions

You must have FTP access to

• the front end machine of the the eLegere installation. (Your administrator knows what the front end machine is and they will grant you the access.)
• the path storing the Powershell scripts on the eLegere's frontend machine. (All custom Powershell script are stored in a folder path that the administrator has decided. You will need access to this folder.) The tutorial will call this folder the Powershell Path.

Also, you need the Scheduler Management permission from your administrator in the Domain where you want to configure the email notification.

Request the permissions above to to your eLegere installation's administrator.

Dependencies

Warning

The Powershell script at Commented Powershell Script section requires all the dependencies provided to work. Failing to place the required files causes errors during the script's execution.

Please, download the dependencies' archive and follow the instructions.

The tutorial requires to place in the Powershell Path the already-made dependencies that you can download by clicking here. You will download an archive containing everything you need to run the script.

You must place the dependencies folders Common, Library, and Templates in the Powershell Path (see Permissions for more info).

The Powershell Path should have the following structure after you have uncompressed the archive:

.
├── Common/
│   ├── env_conf.ps1
│   ├── generateRowLink.ps1
│   ├── GetRows.ps1
│   ├── GetSqlConnection.ps1
│   └── SendEmail.ps1
├── Library/
│   └── Baseline.Common.Management.dll
└── Templates/
    └── Template.html

Read about what each dependency does. You don't need to learn all the details, just the basic idea of why do you need them.

env_conf.ps1

env_conf.ps1 is a script containing some configurations you need for the main Powershell script.

Why a Separate Config File?

If you have multiple Powershell Scripts requiring the same parameters for email settings, credentials, and Connection String, it's useful to have a separate file. Having a separate file enables to update the shared settings without changing each script manually.

You must configure some settings in the env_conf.ps1 before setting up the script. Read the comments to understand what parameters you must type in each variable.

<# 
    env_conf contains a function that creates an object.
    The object contains all the required settings to run the script.
    The values in the script are examples. Replace them with values provided 
    by your administrator.
#>

<# 
    The function encapsulates all the info related to connection 
    to your eLegere installation, the email SMTP, and the Library folder.
#>

function getConf(){
$confProperties = @{

    <# 
        SqlConnectionString = Connection String to connect to the database's machine. 
        Ask to your admin.
    #>

    SqlConnectionString = "[Insert here Connection String]" 

    # HostBaseUrl = Your eLegere's installation URL 

    HostBaseUrl         = "https://myserver.com/" 

    # Secret Access Key to access the HostBaseURL. Ask to your admin.

    HostSecretKey       = "[Insert here the access key.]"

    # Parameters for the SMTP that will send the email

    MailServer          = "[Insert STMP server here.]" 
    MailPort            = 587
    MailEnableSSL       = $true
    MailUsername        = "your.email@provider.com"
    MailPassword        = "[Insert password here.]"
    MailFrom            = "[Insert sender's email here.]"

    # The eLegere database's schema 

    CommonSchema        = "[Inser SQL schema here.]"

    <# 
        Folder containing the Baseline.Common.Management.dll dependency
        You don't have to change it if you have decompressed 
        the archive attached to this tutorial.
    #>

    LibrariesFolderName = "Libraries"
}

# The function creates a Powershell object containing all the required info and returns it. 

$conf = New-Object -TypeName psobject -Property $confProperties
return $conf
}

GenerateRowLink.ps1

This Powershell Script recalls and apply the =GENERATEROWLINK() function, enabling to use it through a dedicated method.

SqlConnection.ps1

This Powershell Script provides all the methods to connect to the machine hosting the SQL database.

GetRows.ps1

This Powershell scripts provides all the methods to submit a database query and retrive the rows you need.

SendEmail.ps1

This Powershell script provides the method to send a custom email.

Baseline.Common.Management.dll

A custom library file required to execute some actions from the eLegere installation.

Template.html

The template that the Commented Powershell Script will format and fill with the retrieved rows.

The layout will appear in the email. The Commented Powershell Script fills the _TABLE_ component in the HTML.

Tip

You can change the Title and Subtitle according to your needs. Change the headings in the HTML Code.

Commented Powershell Script

This section comments each part of the main Powershell Script that will generate your email with the rows' list and their links to the application.

Change what the comments point out according to the settings provided by your administrator.

#  Retrives the Powershell Path location and assigns it to a variable.

$scriptPath = (Get-Location).Path

# Import the dependencies from the Common folder. You will call methods from these .ps1 files.

. $scriptPath\Common\env_conf.ps1
. $scriptPath\Common\GetSqlConnection.ps1
. $scriptPath\Common\GetRows.ps1
. $scriptPath\Common\generateRowLink.ps1
. $scriptPath\Common\SendEmail.ps1

# Acquires the HTML template to be filled with the rows from the Templates folder.

$template=Get-Content -Path $scriptPath\templates\Template.html

# Retrieves the object generated by the function in env_conf.ps1.

$conf = getConf

# The function converts the 12 character numeric Date in a readable format.

function convertDate($numericDate){

    return $numericDate.Substring(6,2)+'/'+$numericDate.Substring(4,2)+'/'+$numericDate.Substring(0,4)

}

<# 
    Function to create the HTML table from the retrieved rows for the template.
    Accepts as arguments the row's list and a dictionary containing a map
    of each row and the related link ($links). 
    The main function will create such dictionary.
#>

function createTable($rows,$links){

    # Prepares the table header's HTML with the fields' name.

    $table =  "
        <style>
        {font-family: Helvetica; font-size: 10pt;}
        TABLE{border: 1px solid black; border-collapse: collapse; font-size:11pt;}
        TH{border: 1px solid black; background: #d6feff; padding: 5px; color: #1c4d8a; font-size:13pt;}
        TD{border: 1px solid black; padding: 5px;}
        </style>
        <table>
        <tr>
        <th>Invoice ID</th>
        <th>CustomerName</th>
        <th>EmissionDate</th>
        <th>Item</th>
        <th>Quantity</th>
        <th>UnitPrice</th>
        <th>VAT</th>
        <th>Total</th>
        </tr>
    "

    <# 
        $rows contains the fields to be reported and formatted in the HTML template
        foreach repeates the operation for each retrieved row from the query. 
    #>

    $rows |foreach { 

        # The script rounds the prices and formats Date and VAT before placing them in the HTML.

        $openedDate = convertDate($_.EmissionDate.ToString())
        $convertedPercentage =  $_.VAT.ToString("P")
        $roundedTotal = [math]::Round($_.Total, 2)
        $roundedUnitPrice = [math]::Round($_.UnitPrice, 2)

        <# 
            Formats the rows's fields in HTML by replacing the values within
            the $tablerow string. Each variable corresponds to a table's field.
            Exception made for $_.Link: contains the result of the =GENERATEROWLINK().
        #>

        $tableRow = " 
            <tr>
            <td style='text-align:center;'><a href="""+$links[$_.IdEntry]+""">"+$_.IdEntry+"</a></td>
            <td>"+$_.CustomerName+"</td>
            <td>"+$openedDate+"</td>
            <td style='text-align:center;'>"+$_.Item+"</td>
            <td style='text-align:center;'>"+$_.Quantity+"</td>
            <td style='text-align:center;'>"+$roundedUnitPrice+"&#8364</td>
            <td style='text-align:center;'>"+$convertedPercentage+"%</td>
            <td style='text-align:center;'>"+$roundedTotal+"&#8364</td>
            </tr>
        "

        # Each row formatted in HTML is recorded in order in the $table variable.

        $table += $tableRow 
    }

    <#
        After having iterated the function on all the rows, 
        the function returns the variable containg the HTML table 
        with all the rows.
    #>

    return $table
}

# The function replaces the _TABLE_ element in the HTML with the formatted rows.

function addTemplate($table){
    return $template.Replace("__TABLE__",$table)
}


<#
    Adds further configuration parameters that aren't common to different scripts.
    That is why you write them here instead in the env_conf. 
    env_conf contains only the settings common to all custom scripts.

    Settings changing from app to app are the Application's ID (applicationId), 
    the mail's subject or the application's SQL table schema.

    Replace each value according to your database, application, and email account.
#>

$applicationId="02b28572-d66c-467b-a8f8-2046755b097c"
$schema="finance001"
$tableName="TBL_INVOICES"
$appBusinessKey = "IdEntry"
$toEmail="your.email@email.com"
$mailSubject="Weekly Reminder | Invoices"
$commonSchema=$conf.CommonSchema


<# 
    Defines a variable containing the SQL query as string.
    Use the [elegere].[F_APP_BIGINT2DATETIME] to convert a Date field value
    in a format that you can use in a comparison.

    Change the query according to your needs. 
    The script retrieves the database's schema from env_conf ($commonSchema).
#>

$rowsQuery = "          
    SELECT * FROM [$schema].TBL_INVOICES WHERE [$commonSchema].[F_APP_BIGINT2DATETIME](DT_CREATION)>=DATEADD(d,-7,GETDATE());
"

<# 
    This is the main function that the script will run.
    It recalls functions defined above and methods from Common folder.
#>

function main() {

    # Get the Connection String from the object provided by env_conf.ps1

    $connection=GetSqlConnection $conf.SqlConnectionString

    # Opens the SQL connection

    $connection.Open()

    # Tries to get the rows satisfying the query from the $rowsQuery variable and format the email.

    try
    {
        # Retrives the rows according to the query in the $rowsQuery variable.        

        $rows = GetRows $connection $rowsQuery

        <# 
            Applies the =GENERATEROWLINK() function to each row and adds 
            a further field to the formatted table with the link.
        #>

        $rowLinks=@{}
        $rows |foreach{
            $link=GenerateRowLink $connection   $conf.HostBaseUrl  $applicationId  $conf.HostSecretKey $schema $tableName "IdEntry" $_.IdEntry
            $rowLinks.Add($_.IdEntry,$link)
        }

        # Uses the createTableRow function to create the table with the rows 

        $rowsTable=createTable $rows $rowLinks

        # Gets the template and replaces _TABLE_ with the formatted table containi

        $emailBody=addTemplate $rowsTable

        <# 
            Generates the email notification from the formatted HTML and sends it. 
            Requires the parameters containing all the information to connect to 
            the STMP and send the email.
        #>

        sendEmail $conf.MailServer $conf.MailPort $conf.MailEnableSSL $conf.MailUsername $conf.MailPassword $conf.MailFrom $toEmail $mailSubject $emailBody

        # Returns a confirmation message to the user in the Log.

        $message = @{
            Message = "Success"
            ReturnCode = "200"
          }
          $result = New-Object psobject -Property $message
        }
    # If an exception happens, the script reports the error message in the Log.
    catch [Exception] 
    {
        echo $_.Exception.Message
        $message = @{
            Message = $_.Exception.Message
            ReturnCode = "500"
          }
        $result = New-Object psobject -Property $message
    }
    # Whatever is the result, closes the SQL connection. 
    finally 
    {
        $connection.Close()
        $connection.Dispose()

    }
    return $result
}

# Launches the function 'main'.

main

Save the script in the Powershell Path. Choose a name for the *.ps1 file, for instance SendScheduledEmailWithRows.ps1.

The Powershell Path should now appear as follows.

.
├── Common/
│   ├── env_conf.ps1
│   ├── generateRowLink.ps1
│   ├── GetRows.ps1
│   ├── GetSqlConnection.ps1
│   └── SendEmail.ps1
├── Library/
│   └── Baseline.Common.Management.dll
├── Templates/
│   └── Template.html
└── SendScheduledEmailWithRows.ps1

Next step is configuring the script launch in the Scheduler within eLegere

Configuring the Scheduled Job

You have set up the Powershell script that eLegere will execute. Now, you must configure the scheduled action in the Scheduler that will lunch the script.

Example

The user must receive the email notification with a certain regularity. In the considered use case, once a week.

Follow the steps in How to Schedule an Action or Notification at a Certain Time? to create a Powershell Script action in the Scheduler.

Once you have created the action, follow the steps to set up all the required settings in the Add Job window to select the Powershell script.

1. Customize the following settings:

   

   • (1) Choose a Name.
   • (2) Choose Recurring type and flag Enabled
   • (3) Choose Weekly frequency and the day in which you want to receive the email (e.g. Friday).
   • (4) Choose a time when you want to receive the email.

2. In the Powershell Script Composer tab, specify Powershell Script file to execute from the Powershell Path.

   

3. Click on the Confirm button.

You will receive a email notification each Friday informing you of the latest entries. Clicking on the Invoice ID opens the row in the application.

Success

You will receive each week an email with a summary of the recorded rows and a link to view them individually in the application.

You can use the same script and dependencies for different applications: change the variable's values (e.g. the SQL query) where required or replace the HTML template to shape the example on your needs.