Daily Homeroom Attendance Report

Automatically email a breakdown of statuses every day

Overview

With the following input:

  • The date for which to report on, ex 2023-02-15

  • A subject for the email, ex "Daily attendance report"

  • List of comma-seperated emails to send to

  • An HTML template

  • Credentials to use smtp connection

    • Host, username, password, port

    • For a Google account:

Can email the following sample output:

The Script

The script to run the daily attendance report is the following:

mbpy \
    SCRIPTS \
    RUN daily_attendance_report \
        {0} \
        {1} \
        {2} \
        {3}

Where:

  • {0} is the date for which to report on

  • {1} is the Subject of the email

  • {2} is the /path/to/template.html file

  • {3} list of emails

For example:

mbpy \
    SCRIPTS \
    RUN daily_attendance_report \
        2023-02-15 \
        'The Subject' \
        /path/to/template/file.html \
        someone@example.com,another@example.com

SMTP configuration

You will notice the above command does not include any SMTP server information. The way to provide these inputs is via shell environment variables. The steps are:

  • Create a file with the below contents

  • Replace those values with your real values

  • Save to a path location

  • Load those environment variables into the shell, with source /path/to/file

Example file:

export SMTP_HOST=smtp.example.com
export SMTP_USER=username
export SMTP_PASSWORD=secret
export SMTP_PORT=465
export SMTP_TLS=yes

If you are using Google and would like to login as a user, go to Manage Account —> Security —> App passwords. This is the password to use above, not your user login. Help article on App Passwords.

Template configuration

One of the inputs is a path to a template file. You can either use the simple one for testing, or you can customize it extensively. In addition, you can find the template used to generate the sample above below "Default template".

The template is written in jinja templating language, which is a cross between html and Python. It is a convenient way to include both presentation information, such as colors, and logical parameters, such as for loops, into the same file.

Testing template:

This will render a simple html table under the Testing header.

<h1>Testing</h1>
{{ data.table }}

Default template:

The following uses the business logic possible with python-like syntax, such as for loops, along with style tags.

Please see customization below to tweak or bulid your own.

<html>
<body style="font-family: Arial, Helvetica, sans-serif;">
  <h2>{{ data.subject }}</h2>
  <h3>Generated on {{ data.today.strftime("%A %e %B, %Y") }} (for {{ data.attrs.days }} day(s), from {{ data.attrs.start_date.date() }} to {{ data.attrs.end_date.date() }})</h3>
  <p>Here is this week&apos;s attendance report for students. Please find the table below with the attendance status for each grade:</p>

  <table style="border: 1px solid black; border-collapse: collapse;">
    <thead>
        <tr style="border-top: 1px solid black;">
        <th style="font-weight: 900; padding: 10px; color: #eee; background-color: #999;">{{ data.df.index.name }}</th>
        {% for column_name in data.df.columns -%}
            <td style="font-weight: 900; padding: 10px; color: #eee; background-color: #999;">{{ column_name }}</td>
        {% endfor -%}
        </tr>
    </thead>
    <tbody>
        {% set values = data.df.values -%}
        {% for grade in data.df.index -%}
            <tr style="border-top: 1px solid black">
            {% if grade == 'Total' -%}
                <th style="font-size:smaller; padding: 10px;background-color: #FF9300;">{{ grade }}</th>
            {% else -%}
                <th style="font-size:smaller; padding: 10px;background-color: #FFC600;">{{ grade }}</th>
            {% endif -%}
            {% set row_index = loop.index0 -%}
            {% set row = values[row_index] -%}
            {% for value in row -%}
                {% if grade == 'Total' -%}
                    <td style="text-align: center; padding: 10px; font-weight: 900; background-color: #FF9300;">{{ value }}</td>
                {% else -%}
                    <td style="text-align: center;padding: 10px;{{ 'font-style: italic;background-color: #eee;' if loop.index == 1 else ''}}">{{ value }}</td>
                {% endif -%}
            {% endfor -%}
            </tr>
        {% endfor -%}
    </tbody>
  </table>
  <p>Thank you.</p>
</body>
</html>

Run every day automatically

The above describes how to execute it from the command line directly. To run it every day, a cron job can be installed. Instructions for this task can depend on which system you are using, but briefly:

  • Use crontab -e to edit the cron table. Every line in the file is one task

  • A cron job executes in a secure environment, different from the command line directly. That means you need to write the full path.

Here is an example of the daily attendance report being sent every weekday at 4:05 pm:

05 16 * * 1,2,3,4,5 . /path/to/smtp.txt && /path/to/venv/mbpy scripts run daily_homeroom_attendance $(date -I) 'Daily Attendance Report' /path/to/template/file.html 'someone@example.com,someone_else@example.com'

Customization

Template

The template can be coded in jinja's template language, which just means you can use both html, css, and a simple python-like syntax to build the presentation and the business logic needed for the intended output.

Please note that styling information in an email with html is not the same as doing so in the browser. Here is one example of a good resource that explains some of the specific techniques to use when styling email with hand-written html.

The variable data is available in the template file, that has the following keys:

The full command

The mbpy run scripts daily_homeroom_attendance is actually short-hand script for a longer, harder-to-type sequence. Users can re-write for their own use cases.

mbpy \
     EXTRACT \
         FROM-HR-ATTENDANCE --start-date {0} \
                            --end-date {0} \
     SMTP --subject '{1}' \
          --template '{2}' \
          --to '{3}' \
          GROUP-BY-PIVOT --margins-name Total \
                         --value 'Student Id' \
                         --column Status \ 
                         --row Grade \
                         --row 'Grade #' \
          ZERO-NAS \
          SPREADSHEET --sort-row 'Grade #' \
                      --sort-column Total \
                      --margins-name Total \
                      --rows-ascending
                      --columns-descending
                      --astype int64
PreviousQuerying via an SQLite ClientNextDataframes with pandas

Last updated