Design decisions

Have an azure backend.
Use a view to collect everything and show that view in the frontend. Have the tables just have the data, sorting of any sort can be perfected in the views.
Try and excepts would be used through out along with error codes so that we know where the error is coming from
Functions are written in a scalable manner
Autodetection of what type of csv/xls being uploaded

Assumptions

(1) municipal_id is unique, even though the same municipal_id is seen multiple time in properties, it is assumed that those properties belong to that same municipal_id.
(2) property rows from the properties.csv file that don't have a matching municipal_id can still exist, and can be uploaded.
(3) perfect correspondence between property and municipal table rows is not a priority since multiple property rows can have the same municipal_id
(4) it is assumed that data will be uploaded multiple times, as in different reports come through so uploadability is important, however even if not, this still is good.
(5) municipal.csv will only contain rows with unique municipal_ids, as the current file doesn not contain any duplicates
(6) Assessment rolls assumed to be not unique
(7) Equation for Annual Tax taken to be := Assessment Value×(Municipal Rate + Education Rate)
(8) If either municipal rate or property are missing annual tax not calculate.
(9) Data is assumed uncorrupted.
(10) municipalities table is set data thats already there, crud is only expected for properties. So new properties added are assumed to have respective data already existing in the municipalities database.

Process documentation

Quick analysis of the data using pandas.
Installed dbeaver, ssms(SQL Server Management Studio), and visual studio.
Database and server was setup from scratch.
Municipalities table created.
Properites table created. (with municipal id as a foreign key)
Venv started with requirements.txt
Decision made to go with pyodbc to talk to server
ODBC Driver Installed and pip install pyodbc
Github private repo connected
Backend report upload functionality added.
Frontend started with npm create-react, along with react-dropzone for drag and drop, and axios for HTTP requests.
municipal id as a foreign key removed due to assumption (3) and (2)
Properties and Municipalities table uploading from frontend working, and Cross-Origin Resource Sharing added.
Upload box made nicer and municipality error handling to skip duplicate municipal_id upload.
View created for frontend, and the view calculatees the annual property tax.
Made the view show up on the frontend as is, as a table.
Create functionality added.
Delete functionality added.
Edit or update funcitonality added.

Additional features

Sort table in ascending, descending, and alphabetical.
Drop in csv files to start populate the db, autodetection of whether the csv is a municipalities file or properties file.

Version Control Commit Message Structure

feat – a new feature is introduced with the changes
fix – a bug fix has occurred
chore – changes that do not relate to a fix or feature and don't modify src or test files
refactor – refactored code that neither fixes a bug nor adds a feature
docs – updates to documentation such as a the README or other markdown files
style – changes that do not affect the meaning of the code
test – including new or correcting previous tests
perf – performance improvements
ci – continuous integration related
build – changes that affect the build system or external dependencies
revert – reverts a previous commit

Summary of Project(Code) Structure

Database/Backend

Implemented using Azure SQL. dbeaver and ssms were used to access during development. Has two tables (PA.Properties and PA.Municipilaties) and one view (ProperitesView)

backend.py has these routes:

@app.route('/upload', methods=['POST'])
@app.route('/get_properties', methods=['GET'])
@app.route('/add_property', methods=['POST'])
@app.route('/check_municipal_id/<string:municipal_id>', methods=['GET'])
@app.route('/delete_property', methods=['DELETE'])
@app.route('/edit_property', methods=['POST'])

CORS was used to maintain security

Frontend

App.tsx holds everything. In the components folder:

FileUpload.tsx: Handles csv import to db
table.tsx: Renders the table
Upload.tsx: Handles single entry property uploads

Project Setup Guide

This section provides step-by-step instructions to set up and run the project on your local machine.

Prerequisites

Git
Python 3.x
Node.js and npm

Installation and Setup

Step 1: Clone the Repository

git clone <repository-url>
cd <repository-name>

Step 2: Set Up Python Environment

Create and activate a virtual environment(before going activate virtual environment), then install dependencies:

# Create virtual environment
python -m venv venv

# Activate virtual environment
# On Windows:
venv\Scripts\activate
# On macOS/Linux:
# source venv/bin/activate

# Install required packages
pip install -r requirements.txt

Step 3: Configure Environment Variables

Create a .env file in the root directory
Add your Azure database credentials:

AZURE_DB_USER=your_username
AZURE_DB_PASSWORD=your_password

Note: Credentials will be provided via email. To access the database:

Find your current IP address (search "what's my IP" on Google)
Please email your IP address so I can add it to the fire wall on azure.

Step 4: Start the Application

Launch both backend and frontend services:

# Start the backend server
cd backend
python backend.py

# In a new terminal, start the frontend
cd frontend
npm start

Step 5: Access the Application

Open your browser and navigate to the application (typically http://localhost:3000).

Support

If you encounter any issues during setup, please contact me at mtfsolan@uwaterloo.ca.

https://github.com/Mevan-Solanga/Property-assessment-tracker

;

MTO challenge