Skip to content

This project takes a SEIS output file and preps it for import into Aeries

Notifications You must be signed in to change notification settings

manuellara/spedFastAPI

Repository files navigation

Overview

This project intended to:

  • Take in a CSV file from SEIS
    • Entered
    • Exited
  • Clean up the SEIS CSV file
    • Convert data types
    • Filter out inconsistent data
  • Compare SEIS data to what's currently in Aeries CSE table
    • Compare each ID's value (Aeries vs SEIS)
  • Output a merged CSV file for upload into Aeries with all the changes
  • Output a change log (dropped IDs and updated values)

Sequence Diagram

sequenceDiagram
  autonumber
  actor User
  User ->> spedFastAPI: Upload SEIS Entered/Exited .CSV
  spedFastAPI ->> spedFastAPI: Process file
  User ->> spedFastAPI: Request files
  spedFastAPI -->> User: Return files
Loading

TODOs

  • Get CSV files from SEIS
    • Entered
    • Exited
  • Read in SEIS files as dataframes
    • Entered
    • Exited
  • Filter SEIS District IDs so they are all consistent
    • Remove blanks
    • Remove non numeric
  • Convert SEIS District ID to integer
  • Convert SEIS Disability Code 1 and Disability Code 2 to integer
  • Get list of SEIS District IDs
  • Initialize Aeries CSE dataframe
  • Query SQL Server to get Aeries data
    • Get SQL Server connection variables
    • Create SQL statement
    • Execute SQL query
      • Save results for Aeries CSE table in dataframe
  • Convert Aeries CSE table date fields to a readable format
  • Export Aeries CSE dataframe to CSV Aeries output.csv as a snapshot
  • Open the attribute mapping JSON file
  • Iterate over Aeries dataframe IDs
    • Iterate over every mapped attribute
      • Check if conversion is needed for the attribute
        • If yes, process replace value function with conversion
          • School Type
          • Percent IN Regular Class
          • Case Manager
          • Plan Type (Edu Plan for SpEd Svcs)
        • If no, process regular replace value function
  • Convert XR column from float64 to Int64
  • Export Aeries dataframe as CSV merged.csv as the snapshot with changes
  • Compare original snapshot CSV against the snapshot with changes CSV
    • Export change log to compare_data.json
    • Set up some analytics on the change log
  • Add SQLAlchemy (replace pyODBC)

Mapping

SEIS Aeries Mapping Required? Completed
SEIS ID CSE.SEI
Last Name
FirstName
Student SSID CSE.ID (map to STU.CID)
Date of Birth
District ID CSE.ID
Disability 1 Code CSE.DI
Disability 1
Disability 2 Code CSE.DI2
Disability 2
Disability 3 Code CSE.DI3
Disability 3
Student Eligibility Status
Date of Original SpEd Entry CSE.ED
Date of Exit from SpEd CSE.XD
Exit Reason CSE.XR
School CDS Code CSE.SS
School of Attendance
District of SPED Accountability CDS Code CSE.DS & CSE.DR
School of Residence CDS Code
School of Residence
Case Manager CSE.SI True Done
Case Manager Email
Percent IN Regular Class CSE.IRC True Done
School Type (Attendance School) CSE.TY True Done
SELPA CSE.SE
Plan Type (Edu Plan for SpEd Svcs) CSE.PT True Done
Graduation Plan Code CSE.GP
Program Setting Code (Ages 0-2) CSE.FI
Preschool Program Setting (3-5 year-old Preschool and 4 year-old TK/Kgn) CSE.FP
Program Setting (TK/Kgn or greater, ages 5-22) CSE.FS
Date of Next Annual Plan Review CSE.AD
Date of Initial Referral CSE.RD
Referred By CSE.RB True
Date of Initial Parent Consent CSE.PC
Date of Infant Initial Referral CSE.IRD
Infant Refer By CSE.IRB
Date of Infant Parent Consent CSE.IPC
Date of Initial Evaluation CSE.IE
Meeting Delay Code CSE.EDL
Date of Infant Initial Evaluation CSE.IIE
Special Transportation CSE.ST True
Primary Residence CSE.RS True
Early Intervention Services CSE.EI True
SBAC Participation Code in ELA CSE.PA2
SBAC Participation Description in ELA
SBAC Participation Code in Math CSE.PA3
SBAC Participation Description in Math
SBAC Participation Code in Science CSE.PA4
SBAC Participation Description in Science

Getting started

Clone the repo to your local machine

git clone https://github.com/manuellara/spedFastAPI.git

Mappings

The /mappings folder contains mappings specific to MY school district, so you will need to review these files. Do not change the structure, only the values

Environment variables

Add a .env to the root directory with the following values:

  • sqlSA (SQL Server service account username)
  • sqlSAPass (SQL Server service account password)
  • sqlServer (SQL Server server name)
  • sqlDatabase (Current Aeries database e.g. dst2...)

Run locally in a python virtual env

In the root directory, create a virtual env

Next, activate the virtual env

Next, install the requirements

Finally, use the included Taskfile to start the api. If you don't have Taskfile installed, be sure to install it before running the next command

task start

Build and run Docker container

For this step, you will need Docker and Taskfile installed

Using the Taskfile, run the following command to build the container

task build

After the container has been built, run the following command to run the container

task run 

Utility commands/scripts

List Taskfile commands

task -l

Count changes in compare file

import json

jsonFile = open('compare_data.json')
data = json.load(jsonFile)

print( len(data['changed']) )

Resources

Containerizing FastAPI application

Adding file uploads to FastAPI application

About

This project takes a SEIS output file and preps it for import into Aeries

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published