1 of 38

MBPY: Schools Technology

About this Resource

A learning resource for mbpy: ManageBac with PYthon.

These pages provide a higher-level overview of use of mbpy.

The mbpy tool and these resources are provided "as is," and is maintained by the Schools Technology Department at Faria Education Group.

For support of this project, we prefer to use the Discord PLC to post questions, etc. By learning in a community context, we can inspire others to utilize it as well.

All individual information presented in the tutorials are machine-generated data and do not represent actual individuals.

Installation

Requirements

What is required to run this?

The following are necessary to install:

Linux, Windows, or Mac computer / server able to run Python 3.10 or higher.
Python 3.10 or higher installed (3.11 is recommended)
200 MB of RAM
Git
Your GitHub usersname. At the moment, the repo is private; thus please contact Adam Morris, Schools Technology & Integrations Director at Faria, to be added to the repo. Once added, you need to accept the invitations.

Please choose from the below pages on which is most appropriate for your use case.

Getting Started Local Install

Local installation tutorial

There are some requirements for local installation:

Have access to the repos (mbpy endpoints and mbpy) and accepted the invites
Authenticated with github
1. Install utility. This will authenticate your device with github.
  1. On Mac, you can use to install it: brew install gh
2. Once the gh command is installed, use it to login: gh auth login
Have pipx
1. On Mac, brew install pipx and then pipx ensurepath
2. On Linux, python3 -m pip install --user pipx and then

Installling with pipx

Remember you have to be logged in to github for this to work gh auth login as above:

You may need to specify exactly which python version to use, which you can by pass an explicit path to the python version, as demonstrated here:

pipx install git+https://github.com/classroomtechtools/mbpy.git --python $(which python3.11)

Configure

Every time you want to add a school to mbpy, it needs to be configured. Learn how here:

Updating

Local Development Install

To develop mbpy or expand its functionality, use poetry:

After that, you can use poetry shell from that directory, add breakpoints etc and hack away. Use mpby commands like usual.

Getting Started using Docker

Installing mbpy

Getting started with Docker

Download Docker and ensure it's running on your machine.

Install with Docker

We need to get the mbpy command in our terminal. First, this command only needs to run once:

If you are new to docker, that command will build the image and make the container accessible through a default name, which in this case is mbpy_mbpycli. It is using the docker files in the codebase to determine all the settings.

If successful, the terminal will output the version number.

Executing with Docker

From inside the container:

Once the docker image has been built you have some choices on how to actually execute mbpy. The sure-fire way is to connect to the docker container, and run commands directly from within the container. This is a good getting-started method to explore.

You can do that with this command:

There, you can simply use the mbpy command line in the container itself.

For example, mbpy import students and then mbpy extract from-db --table students chain info.To exit, type exit. Note that with this method, the import data will not persist the next time you run docker with this method. (See on how to persist across executions).

From the host:

Execute the application automatically from the host with the following command, you can execute the same as above, but without the --entrypoint bash part, and then tack on to the end the commands. For example, to output the version number:

Note however this method does not persist across executions. To learn how to enable the ability to persist, see .

Further instructions with Docker

Please see the below information which essentially bootstraps the Getting Started setup for further functionality.

The instructions in Getting Started and Updating with Docker are intended to provide an environment with the shortest path to being able to get started with the application. Installing with that method has a drawback, in that once the container is exited, any data written to disk is lost.

In order to set up an environment where such operations are available, please see the below.

Enable persistence across executions

Using the docker concept of volumes, any data imported into mbpy or written to disk during an operation will persist across executions.

Updating with Docker

Get the latest changes

The mbpy tool is under active development, and improvements or additional may be made over time.

Updates

In order to get the latest, simply go to your parent directory, and from inside the indicated directories update the code base:

Tutorials

Configuration

Use configuration to set up API keys for a school (or multiple schools)

Getting Started

After installation, the next step is to enable mbpy to use a school's API key. In ManageBac, create a token with at least read permissions for all endpoints, and copy the token value. Then:

Does it say command not found? If you just installed it, you'll need to open a new terminal window.

You will then be promopted to enter the subdomain, and the token.

When complete, enter the following:

The above command reports all configuration information:

Configuration complete! Learn how to pull data from the API by heading over to "Importing":

Switching schools

Whenever mbpy starts up, it uses the indicated configuration file, whose contents include the API token given for mbpy config new.

Security note: The API token is stored in plain text in the configuration file. Access to this file should be restricted.

If you set up more than one school, and need to switch schools, you can use the following command:

That will tell mbpy to use the configuration file associated with the subdomain name you previously input.

To see a list of all subdomains available:

Expliciit configuration

The above demonstrates how to use "implicit" configuration, where mbpy config use selects the subdomain, and then every subsequent mbpy command uses that configuration (until mbpy config use is invoked).

If you would prefer to explicitly indicate which subdoman, you can set the MBPY_SUBDOMAIN variable:

That will instruct mbpy to use the configuration file, the same as if you first did mbpy config use abc.

Importing

Pulling a local copy, and keeping it up-to-date.

The mbpy tool has an import command that will use the APIs to extract the data, putting them into an SQLite database.

The following command will get pull all "core" data from the API:

mbpy import all

That will import school, programs, grades, students, teachers, classes, and memberships:

Imported school (Faria International School)
Imported 16 year groups
Imported 344 subjects and 83 groups
Imported 56 total grades across 15 programs
Imported 87 academic years and 174 academic terms
...

To pull more even more of the data, you need to know the command for it. You can view all of the available import commands with the --help feature:

mbpy import --help

This help feature can be used to learn what subcommand can go after "import", as well as any options that are available for the "import" command. The subcommands are organized into groups, such as "Convenience" and "Core Entity Importers":

This help screen informs us that we can import behaviour notes, with the following:

mbpy import behaviour-notes

It also informs us that we can completely import the classes:

And we can find out more information about each command and sub command using --help.

Displays the following help:

Extracting

About Extracting

After importing, the data can be queried through the command line. For example:

mbpy EXTRACT from-users PRINT

That will produce screens that display the first 5, and last 5 student records, and every column in the student table for those records.

        id      role   oa_id sb_id account_uid
0    10752543    Admin  None  None     None     \
1    10752544    Admin  None  None     None
2    10752545  Advisor  None  None     None
3    10752546    Admin  None  None     None
4    10752547  Advisor  None  None     None

Scroll down to see more columns for these records. (Notice the \ indicates there is wraparound.)

These commands can be used to just get data displayed, but it becomes interesting when paired with available commands to perform manipulations. For example, to find the distribution of users' domains, we could do the following:

This school is supposed to have only eduvo.com accounts. Looks like we have a few users with wrong email addresses! Let's find them:

Let's see what we got:

Let's limit to only those that are active (not archived), and let's give ourselves a link we can click:

Command-click to load the url in the browser!

The structure of these commands are the following:

Command / subcommand

Explanation

Comment

To learn more about how to query with extractors, continue to Querying Commands:

Streaming

Pipelines

Using

Pipelines are like saved queries. They produce a data set that would otherwise be a very long command.

View

Pipelines come with some built-in, and can be installed via plugins. To see what is currently available:

For example:

Which outputs the following:

Pipelines can also be used with the EXTRACT keyword, which will let allow you to pass it to a loader. Let's load it with our pretty printer PPRINT:

Scripting

Once you've imported the data, the most straight-forward thing to do is to run the pre-prepared scripts

View the scripts available

mbpy scripts list

Run a script

You can run each individual script by appending the line output above after mbpy scripts run, like this:

mbpy scripts run students_id --group health_checks

Run an entire group of scripts

Just use the group name by itself:

mbpy scripts run health_checks

Example output:

Write a script

These scripts are simply text files in the scripts folder. They can be grouped into directories. They have to end with .mbpy in order to be executed.

The idea is simple:

Write a query with the command line. See "" for how to do that.
Copy the query into a text file that ends with .mbpy
Place it into the appropriate directory

Big Query

A brief demonstration of using mbpy as a data connector for Big Query

There are convenient scripts available to load Big Query with the tables that have been imported into the SQLite database. They live in the scripts folder and can be viewed with

mbpy scripts list

They are displayed in the group load_bq. Executing them without setup will fail, as there is a process associated with ensuring that script itself has access.

First, it should be noted that the target used in this script is TO-BQ and takes some input parameters:

mbpy stream to-bq --help

In the help file, you will see that the only required parameter is table ID. BigQuery needs the dataset, and . and then the table ID, the part after the dot, in order to load the data. You only need to give it the table ID (the part after the dot) in the script, so where do you supply the dataset?

Environment variables are another way to send paramters, and this is what is used for the TO-BQ command (as well as other targets).

Environment variables are secure ways to send sensitive data in a scripting environment, which allows us to not have to hard code passwords and other sensitive information. In order to execute the script, we need to load these environment variables.

To do that, we create a text file, call it "bq_env_vars.txt", as an example:

Each of those variables (in uppercase) have values (after the = sign) that the script will use. How do we use this file?

That will load the variables into the current environment, and then executing the script will use them.

Querying & Tranformations

Querying the Database

More information about `mbpy extract from-db`

To query the database, mbpy needs the data imported first, which can be accomplished with the commands.

Extracting

The extract

Querying via Script

Creating convenient scripts

The prevoius section showed how to use the command line to query the database and get some output on the terminal and save it as a CSV:

Querying the Database

However, re-using the query later would be quite a hassle to have to type it again. In this section, we will use the mbpy script group of commands save our query and easily re-execute it.

mbpy scripts --help

╭─ Commands ────────────────────────────────────────────────────────────────────────╮
│ list  Show all saved queries                                                      │
│ run   Execute a saved query                                                       │
╰───────────────────────────────────────────────────────────────────────────────────╯

By default, it comes with a few useful scripts, which we can view:

The last one outputs this:

mbpy scripts run school

       │ timezone                           │ subdomain          │ name
╶──────┼────────────────────────────────────┼────────────────────┼──────────────────╴
     0 │ Asia/Kuala_Lumpur                  │ f1                 │ FariaOne

The search-students-by-name looks interesting, let's try it:

These scripts we are running are actually simple text files whose contents have queries built just like in the previous section. For example, the search-students-by-name script is this:

executes this:

The above also shows an additional instrumentation that is available for these saved queries, which is that you can use the {0} and {1} to refer to additional arguments passed to the script. If you pass don't pass any, or there is some other problem, mbpy will show you with a useful error message:

You can also associate multiple scripts with one command, using the --group feature. For example, there is a "health check" group that performs three queries, and outputs the results. You can execute all of them this way:

It output enough information to let me see that every student has a student_id assigned, but there are two students in Grade 1, that aren't archived, that do not have any parents associated. Also, there are two other students that don't have a homeroom teacher, nor a year group associated. Better fix that.

In order to add a script to our command, we simply navigate to the src/scripts directory in the codebase, and add the text file there. If you want to group more than one command under one group, simply create a directory.

This is the current structure:

Each script needs to end with .mbpy in order to be detected by the application.

Querying via an SQLite Client

Querying the data using SQL

The mbpy import command extracts the data from APIs, and puts it into an SQLite database.

This database is located inside the mbpy/data folder. Thus, you can use any database client to navigate the table structures, or execute select statements, joins, etc.

This is an example of using a database client to look for students with a particular domain name:

Scripts

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:
  - Create an

Can email the following sample output:

The Script

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

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

For example:

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:

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 .

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 , 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.

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.

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:

Customization

Template

The template can be coded in , 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 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:

key

Description

Example

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.

Pipelines

Dataframes with pandas

pandas is a tool that mbpy uses to assist with the creation of pipelines.

Using Python means we also access one of the premier data management tools in the industry: . There are major advantages:

Commands that can convert large data sets into pivot tables
Import and export to CSV and Excel
Much simpler and easier to access than SQL commands

Pipelines

Example Pipelines

This section details some ready-made pipelines that have been created with the mbpy tool.

Bulk Change Email domain

Change e-email domain

When domain names change, all of the users need to be updated. This pipeline asks for user input, demonstrates what actions it will take, updates the user table accordingly, and then outputs a report.

Using --help to discover what inputs this command requires:

The only required parameter is --users:

mbpy pipelines change-email-domain --users students

The script will then ask you to input the values needed

It will then download from the API, and present the selected users that will potentially be changed:

As we can see, there are lots of active students with this email address. The next step will display the first 10 and last 10 records that will be changed (not real student information):

That gives us the confidence that it is doing example what we are expecting it to do.

The operation to make the necessary API calls (PATCH on /students/{id} endpoint) commenses, with a progress bar:

Once the operation is complete, it provides a simple table indicating status code:

Finally, it provides a report of all actions that were taken. In a support context, this report can be sent back to schools for confirmation:

Enrollment by Gender

Displays gender enrollment information from the database

This command has no input parameters, although does require student information to first be imported:

mbpy import students

Once complete, this command:

mbpy pipelines enrollment-breakdown

This the kind of table displayed:

The actual pipeline code that builds the above table is the following:

The above code has a great deal of boilerplate that may make it difficult to read at first, however, the key lines are 7 and 8. If we format only those two lines, we see some patterns:

Which basically says "extract students whose archived status is False, then execute a pivot table displaying count, broken down by grade and gender.

Building the pipeline from the command line

The above Python code can be represented directly through mbpy commands as well.

Thus, we can extract the students like this:

It outputs just the first item, with columns down the left side:

Let's check out the help for the pivot command:

So we can see how to build the same command on the command line, formatted with \:

Plugins

mbpy_powerschool_plugin

A community solution.

An mbpy plugin, paired with a PowerSchool plugin, that allows a school to synchronize ManageBac with PowerSchool as the source of truth. These plugins have a key complication to solve, which is mapping PowerSchool courses with ManageBac classes.

Installation (on PS)

Directions and source code are available, however please note that some adjustments will probably be required.

API reference

Please refer to the below API methods, which contain responses, for examples of the JSON returned by the plugin.

Get Students

GET {baseUrl}/ws/schema/query/mk.ManageBac_Stu

Query Parameters

Name

Type

Description

Get Teachers

GET {baseUrl}/ws/schema/query/mk.ManageBac_Tea

Query Parameters

Name

Type

Description

Get Parents

GET {baseUrl}/ws/schema/query/mk.ManageBac_Par

Query Parameters

Name

Type

Description

Get Student Enrollments in Classes

GET {baseUrl}/ws/schema/query/mk.ManageBac_Stu_Class

Query Parameters

Name

Type

Description

mbpy Plugin

Reference information for mbpy plugin for powerschool.

Configuration Options

#TODO

Plugins Overview

mbpy supports being able to write Python code separate from the mbpy codebase.

Since mbpy is a powerful tool that extracts API information, programmers can utilize it for their own custom solutions.

Without impacting the underlying codebase, a simple plugin architecture is available. This section will demonstrate how to create such a plugin. It will show the output of an example plugin that outputs attendance records.

Directory structure

Make a new directory anywhere outside of the mbpy codebase. As long as the contents of the directory follow a consistent format, anything build into this folder will be able to add functionality to the existingmbpy command.

mbpy plugins --help

mbpy_plugin_example

Description of plugin architecture

Usage

When the directory structure is complete, go to the mbpy folder, and import it with the following command. Remember you should be in your virtual environment:

Check it was successful by inputting the command mbpy plugins --help and seeing the name displayed. It can then be executed by typing mbpy plugins name_of_plugin.

Directory Structure

The bare-bones essentials for getting started building a plugin for mbpy, with explanations.

Place the following files into the same directory:

`setup.py`:

The name property can be anything, rename it to whatever makes sense. The py_modules value needs to match the name of the module, which in this example is "example". The entry_points parameter is where the magic happens. It simply maps out that we will be injecting which module, into where. In this case, it's indicating to inject as an mbpy_plugin, so that it'll be available as a subcommand when the user uses mbpy_plugins --help to discover how to use it. The path is specified as example=example.cli:cli which means "The example module, which has a file called cli, which has a function defined as cli, and that is the code that will be injected.

`example/cli.py`:

This is the subcommand that will be added to mbpy plugins command. You can specify parameters using the api interface. There are many example of how to do this in the codebase.

`example/init.py`

This is actually a blank file, but required to exist (as this is how a module is created in Python.)

Common Idioms

The above code is truly bare-bones; there is very little that can be actually accomplished without additional imports and boilerplate.

To continue building, please use the following example idioms:

Execute a DB query:

With the code installed the user can execute mbpy plugins your-name --grade 10.

mbpy_plugin_destiny

This plugin is intended to be used for eventual processing by the third party Follette Destiny. All it does is use mbpy's pipelining feature to select the data points required by Destiny, and convert it into XML.

Destiny knows how to process this data accordingly.

mbpy_plugin_attendance_reports

This plugin reshapes both homeroom and class attendance data into a format useful for submission to governing authority. Instead of reporting on total tallies, it breaks down all of the attendance information by day.

class_grade_number │ class_grade │ gender │ count ╶─────────────────────────────┼───────────────────┼────────────┼─────────╴ 2 │ PS 1 │ Female │ 8 ╶─────────────────────────────┼───────────────────┼────────────┼─────────╴ 2 │ PS 1 │ Male │ 2 ╶─────────────────────────────┼───────────────────┼────────────┼─────────╴ 4 │ PS 3 │ Female │ 7 ╶─────────────────────────────┼───────────────────┼────────────┼─────────╴ 4 │ PS 3 │ Male │ 3 ╶─────────────────────────────┼───────────────────┼────────────┼─────────╴ 4 │ PYP 3 │ Female │ 5 ╶─────────────────────────────┼───────────────────┼────────────┼─────────╴ 4 │ PYP 3 │ Male │ 3 ╶─────────────────────────────┼───────────────────┼────────────┼─────────╴ 5 │ PS 4 │ Female │ 7 ╶─────────────────────────────┼───────────────────┼────────────┼─────────╴ 5 │ PS 4 │ Male │ 13 ╶─────────────────────────────┼───────────────────┼────────────┼─────────╴ 6 │ PS 5 │ Female │ 6 ╶─────────────────────────────┼───────────────────┼────────────┼─────────╴ ...

mbpy configuration ╷ name │ value ╺━━━━━━━━━━━━━━━━━━━━━━━━━━━━┿━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╸ subdomain │ demo ╶────────────────────────────┼──────────────────────────────────────────────────╴ token (last 4 digits) │ daa0 ╶────────────────────────────┼──────────────────────────────────────────────────╴ Root Directory │ ~/some/path/here ╶────────────────────────────┼──────────────────────────────────────────────────╴ Scripts Directory │ ~/some/path/here/mbpy/scripts ╶────────────────────────────┼──────────────────────────────────────────────────╴ Path to config file │ ~/some/path/here/mbpy/conf/demo.py ╶────────────────────────────┼──────────────────────────────────────────────────╴ Path to database │ ~/some/path/here/mbpy/data/demo.db ╶────────────────────────────┼──────────────────────────────────────────────────╴

mbpy scripts run health-checks Students without student_id ╶───────────────────────────────────────────────────────────────────────────────────╴ Empty table 0 records with 5 columns Active students with no parents assigned ╷ ╷ ╷ ╷ ╷ │ mb_id │ grade_number │ archived │ student │ has_parent ╶──────┼──────────┼──────────────┼──────────┼──────────────────────────┼────────────╴ 173 │ 14351728 │ 2 │ False │ Rogers, Norville │ False 1914 │ 14553487 │ 2 │ False │ Student1, Test (Wally) │ False │ │ │ │ Walter │ ╵ ╵ ╵ ╵ ╵ 2 records with 5 columns Students with either no grade, no year group, or no hr teacher ╷ ╷ ╷ ╷ ╷ ╷ │ mb_id │ archived │ student │ has_hr_teac… │ has_grade │ has_year_g… ╶─────┼──────────┼──────────┼──────────────┼──────────────┼───────────┼─────────────╴ 1 │ 14235240 │ True │ Student, │ False │ True │ False │ │ │ Happier │ │ │ 958 │ 14553487 │ False │ Student1, │ False │ True │ False │ │ │ Test (Wally) │ │ │ │ │ │ Walter │ │ │ ╵ ╵ ╵ ╵ ╵ ╵

╭─ Grouping, aggregation, and transformations ───────────────────────────────────╮ │ cast Make the specified column the same values, but as │ │ strings │ │ collapse-to-json-column Collapses each row to one column with key/values. │ │ columns Specify the order and rename columns in one │ │ command │ │ convert-values Map an entire column values, those not specified │ │ will become null. │ │ dottie Expand the dataframe through a dot-specified path │ │ of columns. │ │ explode Create additional identical rows for every item │ │ indicated in the column provided. │ │ explode-all For any column that have at least one list item, │ │ expand │ │ implode Opposite of explode. Takes the values in column │ │ that will roll up to lists. The other columns by │ │ default take the first value. │ │ keep Delineate the columns to keep. Easy way to │ │ remove unnecessary columns. │ │ normalize Convert a string column using a string mapper, │ │ such as str.lower() │ │ remove Deletes columns. │ │ rename Rename columns │ │ rename-prefix Add a prefix to all of the specified columns │ │ replace Replace all values to a specific value │ │ replace-nulls-with-booleans Replace any nulls with a boolean, for example │ │ False │ │ replace-with-nulls Pass in values that should be converted to None. │ │ Effects the entire input dataframe. │ │ spread Adds additional columns for every top-level │ │ property found as json values. This works by │ │ converting the dataframe to records, then │ │ flatdict which concats the properties in │ │ dot-seperated naming convention. │ │ spread-attendance Same as spread, but with class attendance │ │ responses, re-shapes to avoid sparse dictionaries │ │ to-csv Exports the current dataframe to disk. │ │ to-excel Output dataframe as an excel file. The path has │ │ to end with .xlsx (due to implementation). │ │ transpose Transpose the df. │ │ trim-whitespace Remove whitespace on either left or right side │ ╰────────────────────────────────────────────────────────────────────────────────╯

╭─ Selection and Filtering ──────────────────────────────────────────────────────╮ │ drop-columns-nulls Drop all columns that are full of nulls │ │ drop-duplicates Remove duplicates as determined by columns │ │ drop-nulls Filter out any rows from the input dataframe that contain │ │ null in the specified column. │ │ duplicates Remove duplicated items from the df, either throughout or │ │ only those found in certain columns. │ │ extract-duplicates Remove all rows except duplicated items, either throughout │ │ or only those found in certain columns. │ │ filter Select the rows which columns have the value indicated, │ │ using operation (by default, equals). │ │ filters_by Specify multiple filters. See query for a more pythonic │ │ interface. │ │ head Transform to only the first n number of rows │ │ is-false Select rows where passed column are False │ │ is-null Select rows where passed columns are None │ │ is-true Select rows where passed columns are True │ │ not-null Select rows where passed columns are not None │ │ query Query the df with pythonic syntax. Column names are │ │ variables, use backticks for columns with spaces. The @ │ │ symbol has special meaning (evaluate a local variable) so │ │ if you are filtering by email address, use FILTER --column │ │ email --value [email protected] instead. │ │ reindex Change the indexes of the df │ │ reset-index Removes all indexes. │ │ sort Change the order of the df according to the values in │ │ specified columns. │ │ tail Transform to only the first n number of rows │ ╰────────────────────────────────────────────────────────────────────────────────╯

"""
Provided for learning purposes. This actually needs 
to be wrapped into a click command line application (and other tweaks) for it
to be executed in the intended context
"""
BASE_URL = "https://domain.example.com/path"
CLIENT_ID = "secret"
CLIENT_SECRET = "secret"
# These globals should be passed via environment variables (not demonstrated here)

import pdb
import click
from uplink import Consumer, RequestsClient, Body, Path, Query, post, returns, headers
from types import SimpleNamespace
import re
import datetime
from collections import defaultdict
from mbpy_endpoints.endpoints import Endpoint
from json.decoder import JSONDecodeError
import pandas as pd
import flatdict
import os


def dot(data):
    if type(data) is list:
        return list(map(dot, data))
    elif type(data) is dict:
        sns = SimpleNamespace()
        for key, value in data.items():
            setattr(sns, key, dot(value))
        return sns
    else:
        return data


def get_dotted_path(data: dict, path: str, default=None):
    pathList = re.split(r"\.", path, flags=re.IGNORECASE)
    result = data
    for key in pathList:
        try:
            result = result[key]
        except:
            result = default
            break

    return result


from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import smtplib

import io, ssl


def export_csv(df):
    with io.StringIO() as buffer:
        df.to_csv(buffer, index=False)
        return buffer.getvalue()


def send_email(from_, send_to, subject, body, password, *dataframes):
    multipart = MIMEMultipart()

    multipart["From"] = from_
    multipart["To"] = ",".join(send_to)
    multipart["Subject"] = subject

    for filename, df in dataframes:
        attachment = MIMEApplication(export_csv(df), Name=filename)
        attachment["Content-Disposition"] = f'attachment; filename="{filename}"'
        multipart.attach(attachment)

    multipart.add_header("Content-Type", "text/plain")
    multipart.attach(MIMEText(body, "plain"))

    context = ssl.create_default_context()
    data = multipart.as_bytes()
    with smtplib.SMTP_SSL("smtp.gmail.com", 465, context=context) as email:
        email.login(from_, password)
        email.sendmail(from_, send_to, data)


@headers({"Content-Type": "application/x-www-form-urlencoded"})
class GetToken(Consumer):
    def __init__(self, client_id, client_secret, client=RequestsClient):
        base_url = BASE_URL
        super(GetToken, self).__init__(base_url=base_url, client=client)
        bearer_token = base64.b64encode(
            bytes(client_id + ":" + client_secret, "ISO-8859-1")
        ).decode("ascii")
        self.session.headers["Authorization"] = f"Basic {bearer_token}"

    @post("oauth/access_token")
    @returns.json(key="access_token")
    def get_access_token(self, grant_type: Query = "client_credentials"):
        pass


import base64


@headers({"Content-Type": "application/json"})
class PsWeb(Consumer):
    def __init__(self, client_id, client_secret, client=RequestsClient):
        auth = GetToken(client_id, client_secret)
        response = auth.get_access_token().json()
        access_token = response.get("access_token")
        if access_token is None:
            raise Exception("No access token returned!")

        base_url = "https://psweb.asw.waw.pl/ws/schema/query/"
        super(PsWeb, self).__init__(base_url=base_url, client=client)
        self.session.headers["Authorization"] = f"Bearer {access_token}"

    @post("mk.ManageBac_Stu")
    def get_students(self, pagesize: Query = 2000, page: Query = 1, **body: Body):
        pass

    @post("mk.ManageBac_Par")
    def get_parents(self, pagesize: Query = 2000, page: Query = 1, **body: Body):
        pass

    @post("mk.ManageBac_Tea")
    def get_teachers(self, pagesize: Query = 2000, page: Query = 1, **body: Body):
        pass

    @post("mk.ManageBac_Stu_Class")
    def get_enrollments(self, pagesize: Query = 10000, page: Query = 1, **body: Body):
        pass


def load_entity(api, entity, path):
    method = getattr(api, f"get_{entity}")
    response = method()
    if not response.ok:
        raise Exception(
            f"{response.request.url} => {response.status_code}\n{response.text}"
        )
    try:
        json = response.json()
    except JSONDecodeError:
        # something wrong with the endpoint, should probably fail out in production
        json = {}
    has_records = json.get("record", False)
    if not has_records:
        message = f"No {entity} records found? Must be an issue with the powerschool source. Exiting with no actions taken"
        raise Exception(message)

    objects = {}
    records = json.get("record", [])
    flattened_records = [
        dict(flatdict.FlatDict(item, delimiter=".")) for item in records
    ]
    df = pd.DataFrame.from_records(flattened_records)
    df.to_csv(f"/tmp/output_{entity}.csv", index=False)
    for item in records:
        value = get_dotted_path(item, path)
        objects[value] = dot(item)
    return (df, objects)


def load_enrollments(api):
    """ """
    page = 1
    objects = defaultdict(lambda: defaultdict(dict))
    classes = []
    records = []
    mapped_classes = []
    while True:
        response = api.get_enrollments(page=page)
        json = response.json()
        these_records = json.get("record", [])
        records.extend(these_records)
        for item in these_records:
            dotted = dot(item)
            section_number = dotted.tables.sections.section_number
            class_id = dotted.tables.sections.class_id
            if not section_number.isdigit():
                class_id = f"{class_id}{section_number}"
                mapped_classes.append(class_id)
            classes.append(class_id)
            objects[dotted.tables.students.student_number][class_id] = dotted
        page += 1

        if json.get("record") is None:
            break

    df = pd.DataFrame.from_records(records)
    df.to_csv(f"/tmp/output_schedule.csv", index=False)
    # df = pd.DataFrame.from_records([{'uniq_id': clss} for clss in set(mapped_classes)])
    # df.to_csv(f'/tmp/output_mapped_classes.csv', index=False)
    return (df, objects, set(classes))


def get_entity_by_key(mb: Endpoint, entity: str, key: str, query: str):
    """ """
    method = getattr(mb.endpoints, f"get_{entity}")
    results = [
        item for item in method(q=query).get(entity) if item.get(key) == query.strip()
    ]
    if len(results) == 0:
        return None
    assert len(results) == 1, "Issue with multiple students with same `student_id`"
    return results.pop()


def execute(mb: Endpoint, records, description, *args, **kwargs):
    """
    Interact with the endpoint, add changed record. If any error happens,
    log that too
    """
    response = None
    try:
        response = mb(*args, **kwargs)
        record = {
            "description": description,
            "action": mb.__name__,
            "change": True,
            "error": bool(response.get("error")),
            "response": response,
            "body": kwargs.get("body"),
        }

    except Exception as err:
        record = {
            "description": description,
            "action": mb.__name__,
            "change": False,
            "error": True,
            "response": str(err),
            "body": "Unexpected Error",
        }

    record.update(kwargs)
    records.append(record)
    return response


@click.command("sync-asw")
@click.option(
    "-d",
    "--date",
    "date",
    type=click.DateTime(formats=["%Y-%m-%d"]),
    default=str(datetime.date.today()),
    help="The date on which to execute",
)
@click.option("-x", "--postfix", "postfix", default="")
@click.option(
    "-a", "--associate/--skip-associations", "associations", is_flag=True, default=False
)
@click.option(
    "-p", "--skip-profile/--update-profile", "profiles", is_flag=True, default=False
)
@click.option("-u", "--user", "user", default=os.environ.get("USER"))
@click.option("-w", "--password", "password", default=os.environ.get("PASSWORD"))
@click.option("-t", "--to", "to_whom", default=[], multiple=True)
@click.pass_obj
def sync(obj, date, postfix, associations, profiles, user, password, to_whom):
    """
    Specialist software written for PowerSchool Sync
    """
    if len(to_whom) > 0 and not password:
        raise Exception("Please provide password to send email")

    from mbpy_endpoints import Generator

    date_string = date.strftime("%Y-%m-%d")

    # if not dry_run:
    #     #raise Exception('You must remove me before going into production, sir!')
    #     mb = Generator(auth_token=obj.token, tld=obj.tld, subdomain=obj.baseurl_subdomain, verbosity=1)
    # else:
    #     mb = Generator(auth_token=obj.token, tld=obj.tld, subdomain=obj.baseurl_subdomain, verbosity=1, Endpoint_class=MockEndpoint)
    # fake_mb = Generator(auth_token=obj.token, tld=obj.tld, subdomain=obj.baseurl_subdomain, verbosity=1, Endpoint_class=MockEndpoint)

    mb = obj.Generator

    api = PsWeb(
        client_id=CLIENT_ID,
        client_secret=CLIENT_SECRET,
    )

    psdf_enrollments, ps_student_enrollments, _ = load_enrollments(api)
    mb_student_enrollments = defaultdict(list)
    psdf_students, ps_students = load_entity(
        api, "students", "tables.students.student_number"
    )

    psdf_teachers, teachers = load_entity(api, "teachers", "tables.teachers.id")
    psdf_parents, parents = load_entity(
        api, "parents", "tables.students.student_number"
    )

    to_be_removed = defaultdict(lambda: defaultdict(dict))
    fields_to_be_updated = defaultdict(lambda: defaultdict(dict))

    records = []
    missing_classes = []

    try:
        mb_year_groups = {}
        for year_group in mb.generate_year_groups():
            mb_year_groups[year_group.get("grade")] = year_group

        print("Creating accounts")
        mb_students = {}
        for student in mb.generate_students():
            student_id = student.get("student_id")
            mb_students[student_id] = student

        today: datetime.date = datetime.datetime.today().date()
        date_query_param = today.strftime("%Y-%m-%d")

        for stu_id, ps_student in ps_students.items():
            mb_student = mb_students.get(stu_id)
            if not mb_student is None and mb_student.get("archived"):
                execute(
                    mb.endpoints.unarchive_a_student,
                    records,
                    stu_id,
                    id=mb_student.get("id")
                )
            if mb_student is None:
                day, month, year = ps_student.tables.students.dateofbirth.split("-")
                class_grade_number = (
                    int(ps_student.tables.students.grade.split(" ")[1]) + 1
                )
                body = {
                    "student": {
                        "student_id": stu_id,
                        "birthday": f"{year}-{month}-{day}",
                        "middle_name": ps_student.tables.students.middle_name,
                        "last_name": ps_student.tables.students.last_name,
                        "first_name": ps_student.tables.students.first_name,
                        "email": ps_student.tables.students.email,
                        "nickname": ps_student.tables.u_student_additionals.nickname,
                        "gender": {"F": "Female", "M": "Male"}.get(
                            ps_student.tables.students.gender
                        ),
                        "nationalities": ps_student.tables.u_country_codes.nat,
                        "class_grade_number": class_grade_number,
                    }
                }
                new_student = execute(
                    mb.endpoints.create_student, records, stu_id, body=body
                )
                if new_student is None:
                    records.append(
                        {
                            "description": "Error occurred when trying to create student",
                            "action": "Create student",
                            "error": True,
                            "change": False,
                            "response": None,
                            "body": "",
                        }
                    )

                else:
                    record = new_student.get(
                        "student", new_student
                    )  # convoluted for dev

                    # FIXME: add them to the right year group
                    target_year_group = mb_year_groups.get(
                        ps_student.tables.students.grade
                    )
                    assert target_year_group is not None, "Grade is wrong?"
                    if not record.get("id") in target_year_group.get("student_ids"):
                        execute(
                            mb.endpoints.add_to_year_group,
                            records,
                            f'{record.get("student_id")} > {ps_student.tables.students.grade}',
                            id=target_year_group.get("id"),
                            body={"student_ids": [record.get("id")]},
                        )

                    if not "id" in record:
                        print(f"Not adding student {stu_id}")
                        continue  # dev
                    mb_students[stu_id] = record

        mb_teachers = {}
        for teacher in mb.generate_teachers():
            email = teacher.get("email").lower()
            mb_teachers[email] = teacher

        for email, ps_teacher in teachers.items():
            mb_teach = mb_teachers.get(email)
            if mb_teach is None:
                body = {
                    "teacher": {
                        "email": ps_teacher.tables.teachers.id,
                        "first_name": ps_teacher.tables.teachers.first_name,
                        "last_name": ps_teacher.tables.teachers.last_name,
                        "middle_name": ps_teacher.tables.teachers.middle_name,
                    }
                }
                new_teacher = execute(
                    mb.endpoints.create_teacher, records, email, body=body
                )
                if not "id" in new_teacher:
                    if error := new_teacher.get("errors"):
                        print(error)
                    print(f"Not adding teacher {email}")
                    continue  # dev
                mb_teachers[email] = new_teacher

        mb_parents = {}
        for parent in mb.generate_parents():
            email = parent.get("email").lower()
            mb_parents[email] = parent

        for stu_id, ps_parent in parents.items():
            mb_stu = mb_students.get(
                stu_id
            )  # get_entity_by_key(mb, 'students', 'student_id', stu_id)
            if mb_stu is None:
                continue  # can occur in dev environment

            # link parents to students
            base = ps_parent.tables.u_student_additionals
            parent_list = []
            for par in ["mother", "father"]:
                kind = par.title()
                email = getattr(base, f"{par}_school_email")
                if email is None:
                    records.append(
                        {
                            "description": "",
                            "action": "missing_email",
                            "error": False,
                            "change": False,
                            "response": f"{stu_id} has no parent email for {kind}",
                            "body": "",
                        }
                    )
                    continue
                split = email.split("@")[0].split("_")
                first_name = f"{par}_first_name"
                first_name = (
                    getattr(base, first_name) if hasattr(base, first_name) else split[1]
                )
                last_name = f"{par}_last_name"
                last_name = (
                    getattr(base, last_name) if hasattr(base, last_name) else split[0]
                )
                parent_list.append(
                    (
                        kind,
                        {
                            "email": email,
                            "first_name": first_name.title(),
                            "last_name": last_name.title(),
                            "gender": {"mother": "Female", "father": "Male"}.get(par),
                        },
                    )
                )

            for role, parent in parent_list:
                email = parent.get("email")
                mb_parent = mb_parents.get(
                    email
                )  # get_entity_by_key(mb, 'parents', 'email', email)
                if mb_parent is None:
                    new_parent = execute(
                        mb.endpoints.create_parent,
                        records,
                        email,
                        body={"parent": parent},
                    )
                    if not "id" in new_parent:
                        continue  # dev, will not be able to associate

                    # associate it here immediately, so we don't have to rely on running --associations
                    execute(
                        mb.endpoints.add_child_association,
                        records,
                        f"associate",
                        parent_id=mb_parent.get("id"),
                        body={"child": {"id": mb_stu.get("id"), "relationship": role}},
                    )
                    mb_parents[email] = new_parent

                if mb_parent.get("archived"):
                    # shouldn't really get to this point, though, since we are unarchiving students, we'll get this for free
                    # although if it happens just above, we won't have latest info
                    # anyway, at least we'll have a record of it happening this way
                    execute(
                        mb.endpoints.unarchive_a_parent,
                        records,
                        email,
                        id=mb_parent.get("id"),
                    )

                if associations:
                    # off by default as it takes a very long time to execute, and rarely will change
                    relationships = list(
                        mb.generate_parentchild_relationships(mb_parent.get("id"))
                    )
                    this_relationship = [
                        rel.get("relationship")
                        for rel in relationships
                        if rel.get("id") == mb_stu.get("id")
                    ]
                    if len(this_relationship) == 0:
                        execute(
                            mb.endpoints.add_child_association,
                            records,
                            f'{mb_stu.get("student_id")} -> {email}',
                            parent_id=mb_parent.get("id"),
                            body={
                                "child": {"id": mb_stu.get("id"), "relationship": role}
                            },
                        )
                    elif this_relationship.pop() != role:
                        execute(
                            mb.endpoints.update_child,
                            records,
                            email,
                            parent_id=mb_parent.get("id"),
                            child_id=mb_stu.get("id"),
                            body={"child": {"relationship": role}},
                        )

        # uses generate memberships endpoint
        mb_classes = {}
        for clss in mb.generate_classes():
            uniq_id = clss.get("uniq_id")
            clss["archived"] = False
            mb_classes[uniq_id] = clss
        for clss in mb.generate_classes(archived=True):
            uniq_id = clss.get("uniq_id")
            assert uniq_id not in clss, "Class uniq IDs are not unique"
            clss["archived"] = True
            mb_classes[uniq_id] = clss

        for memb in mb.generate_memberships(
            class_happens_on=date_string, classes="active", users="active", per_page=200
        ):

            membership = dot(memb)
            clss = mb_classes.get(
                membership.uniq_class_id
            )  # get_entity_by_key(mb, 'classes', 'uniq_id', membership.class_id)

            if membership.role == "Student":
                uniq_student_id = membership.uniq_student_id.strip()
                uniq_class_id = membership.uniq_class_id.strip()

                # store mb enrollments for later comparison
                mb_student_enrollments[uniq_student_id].append(uniq_class_id)

                if uniq_student_id != membership.uniq_student_id:
                    print(f'Whitespace "{uniq_student_id}"')
                if uniq_class_id != membership.uniq_class_id:
                    print(f'Whitespace "{uniq_class_id}"')
                mb_student = mb_students.get(
                    membership.uniq_student_id
                )  # session.get(Student, membership.user_id)
                clss = mb_classes.get(membership.uniq_class_id)

                if enrolled := ps_student_enrollments[uniq_student_id][uniq_class_id]:
                    pass  # print(enrolled)
                else:
                    to_be_removed[uniq_student_id][uniq_class_id] = SimpleNamespace(
                        student=mb_student, clss=clss
                    )

                if ps_stu := ps_students.get(uniq_student_id):
                    # ensure enrolled into correct year_group
                    target_year_group = mb_year_groups.get(ps_stu.tables.students.grade)
                    assert target_year_group is not None, "Grade is wrong?"
                    if not mb_student.get("id") in target_year_group.get("student_ids"):
                        execute(
                            mb.endpoints.add_to_year_group,
                            records,
                            f'{mb_student.get("student_id")} > {ps_stu.tables.students.grade}',
                            id=target_year_group.get("id"),
                            body={"student_ids": [mb_student.get("id")]},
                        )

                    if not profiles:
                        # stop here unless we need to update profiles
                        continue

                    if ps_stu.tables.students.grade != mb_student.get("class_grade"):
                        # FIXME: This doesn't seem to be working
                        execute(
                            mb.endpoints.update_a_student,
                            records,
                            mb_student.get("student_id"),
                            id=mb_student.get("id"),
                            body={
                                "student": {
                                    "class_grade_number": int(
                                        ps_stu.tables.students.grade.split(" ")[1]
                                    )
                                    + 1
                                }
                            },
                        )
                    # ps_birthday = datetime.datetime.strptime(
                    #     ps_stu.tables.u_student_additionals.enrollmentdate, '%d-%m-%Y').date()
                    dates_checks = (
                        # ('attendance_start_date', mb_student.attendance_start_date, ps_stu.tables.u_student_additionals.enrollmentdate),
                        (
                            "birthday",
                            mb_student.get("birthday"),
                            ps_stu.tables.students.dateofbirth,
                        ),
                    )
                    for property, mb_, ps_ in dates_checks:
                        if not ps_ is None:
                            ps_date = datetime.datetime.strptime(ps_, "%d-%m-%Y").date()
                            if not mb_ is None:
                                mb_date = datetime.datetime.strptime(
                                    mb_, "%Y-%m-%d"
                                ).date()
                                if ps_date != mb_date:
                                    # incorrect
                                    fields_to_be_updated[uniq_student_id][
                                        property
                                    ] = ps_date.isoformat()
                            else:
                                # blank
                                fields_to_be_updated[uniq_student_id][
                                    property
                                ] = ps_date.isoformat()
                        else:
                            if not mb_ is None:
                                fields_to_be_updated[uniq_student_id][property] = None

                    field_checks = (
                        (
                            "email",
                            mb_student.get("email"),
                            ps_stu.tables.students.email,
                        ),
                        (
                            "last_name",
                            mb_student.get("last_name"),
                            ps_stu.tables.students.last_name,
                        ),
                        (
                            "first_name",
                            mb_student.get("first_name"),
                            ps_stu.tables.students.first_name,
                        ),
                        (
                            "middle_name",
                            mb_student.get("middle_name"),
                            ps_stu.tables.students.middle_name,
                        ),
                        (
                            "nickname",
                            mb_student.get("nickname"),
                            ps_stu.tables.u_student_additionals.nickname,
                        ),
                        (
                            "class_grade",
                            mb_student.get("class_grade"),
                            ps_stu.tables.students.grade,
                        ),
                        # ('nationalities', (mb_student.get('nationalities') or [None]).pop(),
                        #    ps_stu.tables.u_country_codes.nat),
                        (
                            "gender",
                            mb_student.get("gender"),
                            {"M": "Male", "F": "Female"}.get(
                                ps_stu.tables.students.gender
                            ),
                        ),
                    )

                    for property, mb_value, ps_value in field_checks:
                        if not ps_value is None:
                            if not mb_value is None:
                                if ps_value != mb_value:
                                    fields_to_be_updated[uniq_student_id][
                                        property
                                    ] = ps_value
                            else:
                                fields_to_be_updated[uniq_student_id][
                                    property
                                ] = ps_value
                        else:
                            if not mb_value is None:
                                fields_to_be_updated[uniq_student_id][property] = None

                    for key in [
                        k
                        for k in mb_year_groups.keys()
                        if not k == ps_stu.tables.students.grade
                    ]:
                        year_group = mb_year_groups[key]
                        if mb_student.get("id") in year_group.get("student_ids"):
                            execute(
                                mb.endpoints.remove_from_year_group,
                                records,
                                f'{ps_stu.tables.students.grade} < {mb_student.get("student_id")}',
                                id=year_group.get("id"),
                                body={"student_ids": [mb_student.get("id")]},
                            )
                    # ensure removed from other year_groups
                else:
                    # no such ps_student, archive the student
                    execute(
                        mb.endpoints.archive_a_student,
                        records,
                        mb_student.get("student_id"),
                        id=mb_student.get("id"),
                        withdrawn_on=date_query_param,
                    )

        print("SSs who need CLASSES to be REMOVED")
        for stu_id in to_be_removed:
            for class_id in to_be_removed[stu_id]:
                item = to_be_removed[stu_id][class_id]
                mb_student = item.student
                mb_class = item.clss
                execute(
                    mb.endpoints.remove_students_from_class,
                    records,
                    f"{class_id} < {stu_id}",
                    class_id=mb_class.get("id"),
                    body={"student_ids": [mb_student.get("id")]},
                )

        print("SS to be UPDATED")
        for stu_id in fields_to_be_updated:
            for property in fields_to_be_updated[stu_id]:
                mb_student = mb_students.get(
                    stu_id
                )  # session.query(Student).where(Student.student_id==stu_id).one()

                value = fields_to_be_updated[stu_id][property]
                if property == "nationalities":
                    value = [value]

                body = {"student": {}}
                body["student"][property] = value
                execute(
                    mb.endpoints.update_a_student,
                    records,
                    f"{stu_id}.{property} = {value}",
                    id=mb_student.get("id"),
                    body=body,
                )

        print("SS to be ADDED to CLASS")
        # classes that student is supposed to be enrolled in according to PS, but not in MB yet
        academic_years = mb.endpoints.get_academic_years()

        for stud_id in ps_student_enrollments:
            ps_stu = ps_students.get(stud_id)
            ps_enrol = list(ps_student_enrollments[stud_id].keys())
            mb_enrol = mb_student_enrollments[stud_id]
            mb_student = mb_students.get(stud_id)

            if mb_student is None:
                continue  # dev, new students won't be there yet

            for add in set(ps_enrol) - set(mb_enrol):
                clss = mb_classes.get(add)
                if clss is None:
                    missing_classes.append(
                        {"description": add, "error": True, "body": stu_id}
                    )
                else:
                    # FIXME: Check that the class has begun, it's possible to be in the source but not intended to be enrolled in MB yet
                    # as it wouldn't be able to remove them, either
                    years = academic_years.get(clss.get("program_code"))
                    if not years:
                        continue
                    years = years.get("academic_years")
                    start_date = None
                    for terms in years:
                        for term in terms.get("academic_terms"):
                            if term.get("id") == clss.get("start_term_id"):
                                # use datetime as click's date param will need to be compared to it
                                start_date = datetime.datetime.fromisoformat(
                                    term.get("starts_on")
                                )
                    assert start_date is not None, "start_date cannot be None"
                    if start_date <= date:
                        execute(
                            mb.endpoints.add_student_to_class,
                            records,
                            f'{stud_id} > {clss.get("uniq_id")}',
                            class_id=clss.get("id"),
                            body={"student_ids": [mb_student.get("id")]},
                        )
                    else:
                        records.append(
                            {
                                "description": f"{mb_student.get('student_id')} > {clss.get('uniq_id')}",
                                "error": False,
                                "change": False,
                                "body": "Not enrolling as class has not begun",
                                "action": "Enrol into class not yet started",
                            }
                        )

    finally:
        timestamp = f"{date_string}{postfix}"
        df = pd.DataFrame.from_records(records)
        print(df)
        df = df.sort_values(by="change", ascending=False)
        df.to_csv(f"/tmp/executions_{timestamp}.csv", index=False)

        df2 = pd.DataFrame.from_records(missing_classes)
        # df2 = df2.sort_values(by='change')
        df2.to_csv(f"/tmp/missing_classes_{timestamp}.csv", index=False)

        subject_description = ""
        change_description = ""
        error_description = ""
        num_errors = len(df.loc[df["error"]])
        num_changes = len(df.loc[df["change"]])
        if num_errors > 0:
            subject_description = f"{num_errors} errors"
            error_description = (
                df.loc[df["error"]]["action"]
                .value_counts()
                .sort_values(ascending=False)
                .to_string()
            )

        if num_changes > 0:
            subject_description += f" {num_changes} changes"
            change_description = (
                df.loc[df["change"]]["action"]
                .value_counts()
                .sort_values(ascending=False)
                .to_string()
            )

        body = ""
        if num_errors == 0 and num_changes == 0:
            body += (
                "Executed successfully. No changes needed, nor any errors encountered."
            )
        if num_errors > 0:
            body += f"Executed, but some errors happened:\n{error_description}\n\n"
        if num_changes > 0:
            body += f"Summary of changes made:\n{change_description}"

        if len(to_whom) > 0:
            send_email(
                user,
                to_whom,
                f'Sync Output {"(" if subject_description else ""}{subject_description.strip()}{")" if subject_description else ""}',
                body,
                password,
                ("sync_output.csv", df),
                ("missing_classes.csv", df2),
                *[
                    (f"powerschool_{name}.csv", d)
                    for name, d in [
                        ("students", psdf_students),
                        ("parents", psdf_parents),
                        ("enrollments", psdf_enrollments),
                        ("teachers", psdf_teachers),
                    ]
                ],
            )
        else:
            print(body)

    return records