Skip to content

Simple but useful app to easily record, manage, and track teachers' and students' attendances in schools.

License

Notifications You must be signed in to change notification settings

mfacecchia/attendance-tracker

Repository files navigation

Attendance Tracker

Simple but useful app to easily record, manage, and track teachers' and students' attendances in schools.

Table of Contents

Built in - Technologies
Modules References
Importing modules
Environmental Variables
Database Entity Relationship model
App Routes
Hashing methods
Functions

Built in - Technologies

Modules References

Core Functionalities

Flask app | Flask Mail | Flask WTForms | MySQL Connector | OS | Datetime | Tailwind CSS | ChartJS | JQuery

Hashing & security

Argon2 | Base64 | Anti CSRF Token

Login Methods

Authlib's OAuth | Google's OAuth Flow

Importing modules

To quickly import all modules used in this project, a requirements file has been provided so simply execute the following command

pip3 install -r requirements.txt

and you'll be good to go.

DISCLAIMER: It is recommended to install all the modules after creating a virtual environment to avoid possible module conflicts with other projects in your drive.

Environmental Variables

All the application's related variables such as the secret key, the Flask Mail's configuration data, GitHub's OAuth URLs & app's secrets and Google's are securely stored in the virtual environment (not included in this repository for security purposes since personal data is used).

Below a list and explanation of all the used virtual environment's variables.

Variable Name Usage
FLASK_SECRET Flask's secret key to securely manage all sessions and cookies data (as stated on Flask's API Documentation)
MAIL_SERVER Flask mail SMTP address (as stated on Flask Mail documentation)
MAIL_PORT Flask mail port (as stated on Flask Mail documentation)
MAIL_USERNAME Flask mail address (as stated on Flask Mail documentation)
MAIL_PASSWORD Flask mail password (as stated on Flask Mail documentation) (used in this project port 587)
GITHUB_CLIENT_ID Github OAuth app client ID (as stated on Github RESI API Documentation)
GITHUB_CLIENT_SECRET Github OAuth app client secret (as stated on Github RESI API Documentation)
DB_USERNAME Database user username (as stated on MySQL Connector Documentation)
DB_PWORD Database user password (as stated on MySQL Connector Documentation)
DB_HOSTNAME Database host address (as stated on MySQL Connector Documentation)
DB_NAME Database name (as stated on MySQL Connector Documentation)
NOTE: For this project the database is locally hosted so the environmental values of DB_USERNAME, DB_PWORD, DB_HOSTNAME, DB_NAME will be `root`, `""`, `localhost` (port 3306), `Attendance_Tracker`

Database Entity Relationship model

Database Entity Relationship image

If you want to test this project on your own device you can import the database tables dump as well.

App Routes

Route Description
/ Landing page. Accessible to anyone who doesn't have an account or is not logged in. If logged in, the user will be automatically redirected to the screening page.
/login Login form. Outputs the form with Email and password input and validates the input passed through a POST request. If already logged in, the user will be automatically redirected to the screening page. For more information about hashed password verification, this section will better explain the process.
/forgot-password Outputs the form with Email input and sends an email to that address with a reset password link. Accessible via the `/login` page. For more information about Email sending function and reset password link GET parameters, this section will better explain the process.
/user/updatepassword Ouptuts the form with Password and Verify Password. Accessible via Email reset password link and after the first login on the application. Input is then validated and the user's password gets updated on the database. For more information about password verification and hashing, this section will better explain the process.
/auth/github Redirects to GitHub's OAuth App authorization. For more information about GitHub's OAuth App Authorization process, please visit the official documentation.
/auth/github/callback GitHub OAuth App callback page, used to manage GitHub OAuth REST API return values. For more information about GitHub's OAuth App Authorization process, please visit the official documentation.
/auth/github/disconnect Removes GitHub Account user ID from the database
/auth/google Redirects to Google's OAuth App Authorization. For more information about Google's OAuth App Authorization process, please visit the official documentation.
/auth/google/callback Google OAuth App callback page, used to manage Google OAuth API return values. For more information about Google's OAuth App Authorization process, please visit the official documentation.
/auth/google/disconnect Removes Google Account user ID from the database
/user User screening. Outputs all the user's related information such as name, role, and last login time as well as a list of all upcoming lessons, a user creation form (ADMIN user role only), a lesson creation form (ADMIN and TEACHER user roles only), a course creation form (ADMIN user role only), a pie chart of attended/not attended lessons percentage (STUDENT and TEACHER user roles only) or a bar chart of the total number of attendances subdivided per lesson date and course name in a chosen range of 7 days, 14 days or 30 days (TEACHER and ADMIN user roles only). For more information about Chart generation, this section will better explain the process. Accessible only if a valid session is open, otherwise redirects to the login page with an error message.
/user/create User creation form and data insertion in the database (ADMIN user role only). For more information about form validation, the official flask-wtf documentation will better explain how the whole validation process works.
/lesson/create Lesson creation form and data insertion in the database (ADMIN and TEACHER user roles only). For more information about form validation, the official flask-wtf documentation will better explain how the whole validation process works.
/lesson/list Shows a list of all the upcoming lessons with a button to manage the lesson's attendances (if the lesson's date is the same as the current date) (ADMIN and TEACHER user roles only), a button to update all lesson's parameters such as subject, description, time and date, and more (ADMIN and TEACHER user roles only), and a button to delete the lesson. The page shows 10 lessons per page, ordered per lesson date. The list varies based on the user role accessing the page; if the role is STUDENT or TEACHER, the list will show only the upcoming lessons for their specific enrolled courses.
/lesson Lesson screening. Outputs a form with all lesson's information and a button to submit the modifications and update the database (ADMIN and TEACHER user roles only).
/lesson/register-attendance Lesson screening. Outputs the selected lesson name as well as a list of all enrolled students and a checkbox to record students' attendances (ADMIN and TEACHER user roles only).
/lesson/attendances Custom API that returns a JSON file about all lessons attendances count in a defined range. For more information about this API, this section will better explain the process.
/lessons/attendances/percentage Custom API that returns a JSON file about all lessons attendances/non-attendances percentage rate in a defined range. For more information about this API, this section will better explain the process.
/course/create Course creation form and data insertion in the database (ADMIN user role only). For more information about form validation, the official flask-wtf documentation will better explain how the whole validation process works.
/user/list Shows a list of all users registered in the platform with a button to update each user's parameters such as name, surname, enrolled courses, and more (ADMIN user role only), and a button to delete the user from the Database (ADMIN user role only). The page shows 10 students per page, ordered per role (Admin, then Teachers, and then Students).
/user/select Outputs a form to update chosen user's parameters such as name, surname, enrolled courses, and more (ADMIN user role only). The inputted data is then processed based on the session user role who is making the request; if the user role is ADMIN, then the selected user data will be related to all selected user's information, otherwise, the parameters to update will be just Email and Password if the session user role is STUDENT or TEACHER (in this case, the update request will be processed based on self user's data because only the ADMIN can update other user's parameters). For more information about form validation, the official flask-wtf documentation will better explain how the whole validation process works.
/user/logout Clears the user session and logs the user out of the application.
/cookie-policy Cookie Policy page randomly generated from Termly policies generator tool.
/terms-and-conditions Terms and conditions page randomly generated from Termly policies generator tool.
/privacy Privacy policy page randomly generated from Termly policies generator tool.

Hashing methods

All user-related sensitive data such as passwords are securely hashed and stored in the database using Argon2id algorithm. To manage and verify such data, Argon2-cffi Python module is being used, in particular the `PasswordHasher` class and its relative methods verify for login and reset password verification functionalities and hash for user creation and reset password functionalities. Non-matching passwords after the verify function is called are managed with Argon2 module built-in VerifyMismatchError exception.

Instead, the password reset route uses an authenticity request method that requires the GET parameters `mail` and `uid` to be hashed with base64 algorithm in order to lower the possibility for an attacker to guess a user email and user ID combination and so reset his password.

Functions

connectToDB()

Starts a connection to the database with the given data through MySQLConnector.connect() function. Connection not established error is managed with MySQLConnector.Error error.

Returns MySQLConnection object or `False` if the connection to Database fails for some reason.

getCourses()

Executes a simple `select` query on the database and updates the global list `courses` with all the formatted courses's names and years.

Returns the formatted output of cursor.execute().

An example of output from this function will be:

[
  '1a Sviluppo software',
  '2a Cybersecurity',
  '1a Sviluppo web'
]

For more information about query to list conversion, this section will better explain the process.

update_user_data()

Manages the function to call based on the session's user's role and the given uid passed as a parameter. The function takes as parameters the form to be processed in the to-be-called function and the uid as optional which will represent the selected user ID if the ADMIN is requesting to update some chosen user. This function calls the updateDataAsUser function if the session's user role is STUDENT or TEACHER or the `uid` parameter is not given (means that the ADMIN wants to update his information), otherwise the updateDataAdAdmin function with the passed `uid` and `form` as function parameters. Returns `True` if the callback function returns `True` as well, otherwise `False`.

getValuesFromQuery()

Gets a MySQLConnector cursor as a parameter and returns a list with dictionaries as list's items with cursor columns names as keys and obtained values as dictionary values. Briefly, this function will iterate through the entire cursor and create a dictionary for each query returned column. If the cursor is empty this function will return an empty list.

This function will return a list like the following

[
  {
    'nomeCorso': 'Sviluppo Software',
    'annoCorso': '2023-2024'
  },
  {
    'nomeCorso': 'Sviluppo Web',
    'annoCorso': '2023-2024'
  }
]

updateLastLoginTime()

Programmatically updates the user's last login time on the database by executing an `update` SQL query on the database. The user is defined by the session's userID value and the current time is obtained from date.today() function.

Returns the formatted current date in `%d/%m/%Y` format by using date.strftime() function.

checkUserGithubConnection()

Checks if the user has a linked Github account. The user is defined by the session's userID value

Returns a boolean value corresponding `True` if the defined user has a linked Github Account or `False` if the Database's "githubID" field is empty

linkGithubAccount()

Updates user's "githubID" database column with the GitHub account id passed as function parameter from GitHub OAuth API return values. Before updating the column, a verification is done; a cursor gets instantiated and checks if the obtained GitHub user id is already linked to an account with a simple `select` query. If the cursor's result contains no rows (which means that no users have linked that specific GitHub account to their attendance tracker account), then it is possible to update the user's `githubID` column and the session's related `githubConnected` value is set to `True`.

Returns `True` if the column is updated, otherwise `False` in all other cases.

loginWithGithub()

Looks for GitHub user ID obtained from the GitHub account ID from GitHub OAuth API return values and executes a `select` query to obtain the relative user ID from the database. If the user is found, a session with all needed user information gets created.

Returns `True` if the GitHub user ID was found in the database, otherwise `False`.

checkUserGoogleConnection()

Checks if the user has a linked Google account. The user is defined by the session's userID value

Returns a boolean value corresponding `True` if the defined user has a linked Google Account or `False` if the Database's "googleID" field is empty

linkGoogleAccount()

Updates user's "googleID" database column with the Google account ID passed as function parameter from Google OAuth API return values. Before updating the column, a verification is done; a cursor gets instantiated and checks if the obtained Google user id is already linked to an account with a simple `select` query. If the cursor's result contains no rows (which means that no users have linked that specific Google account to their attendance tracker account), then it is possible to update the user's `googleID` column and the session's related `googleConnected` value is set to `True`.

Returns `True` if the column is updated, otherwise `False` in all other cases.

loginWithGoogle()

Looks for Google user ID obtained from the Google account ID from Google's OAuth API return values and executes a `select` query to obtain the relative user ID from the database. If the user is found, a session with all needed user information gets created.

Returns `True` if the Google user ID was found in the database, otherwise `False`.

validateFormInput()

Validates form user input by checking if the input data is not an empty string.

Returns `True` if the passed string is empty, otherwise `False`

getUsersList()

Obtains all users from the database by executing a `select` query.

Returns the formatted output of cursor.execute().

The returning list of this function will look something like this:

[
  {
    'userID': 1,
    'Nome': 'Mario',
    'Cognome': 'Rossi',
    'Tipologia': 'Studente',
    'nomeCorso': 'Sviluppo software',
    'annoCorso': 1
  },
  {
    'userID': 2,
    'Nome': 'Luigi',
    'Cognome': 'Verdi',
    'Tipologia': 'Insegnante',
    'nomeCorso': 'Cybersecurity',
    'annoCorso': 2
  }
]

For more information about query to list conversion, this section will better explain the process.

getUserData()

Gets all user's related data by executing a `select` query. The user is defined by the passed function's parameter `uid`.

Returns the formatted output of cursor.execute() in form of dictionary.

An example output of this function is:

{
  'userID': 1,
  'Nome': 'Mario'
  'Cognome': 'Rossi',
  'Tipologia': 'Studente',
  'Email': 'mariorossi@mr.com',
  'nomeCorso': 'Sviluppo software',
  'annoCorso': 1
}

For more information about query to dictionary conversion, this section will better explain the process.

updateDataAsAdmin()

Updates all selected user's data (ADMIN user only). Takes as parameters the selected user ID and the form that will be used to get the data from and to be used to update the database. For more information about password hashing, this section will better explain the process.

updateDataAsUser()

Updates all selected user's data. Takes as parameters the form that will be used to get the data from and to be used to update the database. For more information about password hashing, this section will better explain the process.

validateCoursesSelection()

Gets all courses' names and the relative years as parameters (MUST be type list()) and executes a query for each item to check if the actual selection exists. Used in user creation and update and lesson creation and update. Also allows a filter for the validation based on the chosen userID to be checked (preference passed in the `userFilter` function parameter). Returns `False` if the DB response returns `None`, else `True` if all requests return a value

getCustomMessage()

Returns a custom message based on the system clock time and a matrix with the default messages and the time ranges which will be selected to be printed out on the user screening page.

getUserCourses()

Getting all courses from the query and creating a single formatted list with all the obtained ones

An example of list output will be:

[
  '1a Sviluppo software',
  '2a Cybersecurity'
]

deleteUser()

Removes a defined user from the database based on the passed parameter `uid`.

getLessonsList()

Executes a query and returns all the upcoming lessons based on user type and enrolled courses filters, and the total number of lessons. Takes as parameters a `limit` variable used to limit the number of results and a `page` variable used to get the next `limit`ed results. Also allows to get the upcoming lessons of a determined user by passing the `uid` and `isTeacher` parameters which respectively represent the userID to get the lessons of and, if the user is a Teacher, the `isTeacher` value must be set to `True`

Returns a list with a dictionary for each list index and the total number of obtained lessons

[
  [
    {
      'idLezione': 1,
      'Materia': 'Python',
      'Descrizione': 'Sviluppo app in Flask',
      'dataLezione': '2024-03-27',
      'oraInizio': '09:00',
      'oraFine': '13:00',
      'aula': 'A001',
      'Tipologia': 'Laboratorio',
      'nomeCorso': '1a Sviluppo software',
      'Presenza': 1
    },
    {
      'idLezione': 2,
      'Materia': 'Sviluppo siti web',
      'Descrizione': 'TailwindCSS e JS',
      'dataLezione': '2024-03-28',
      'oraInizio': '09:30',
      'oraFine': '13:30',
      'aula': 'A003',
      'Tipologia': 'Lezione',
      'nomeCorso': '1a Sviluppo web',
      'Presenza': 0
    }
  ], 2
]

With a filter on the lesson's course if the request is being made from a STUDENT or TEACHER user role.

verifyUserExistence()

Verifies if the user exists before sending the recover password email. Used in user password recovery function. Returns its relative `userID` if the query returns a value, otherwise `False`

b64_encode_decode()

Takes a string as input parameter and returns its relative base64 encoded/decoded value. If `encode` parameter is `True`, the string will be encoded, if it's `False` it will be decoded. For more information about encoding and decoding with base64, this section will better explain the process.

selectUsersFromCourse()

Executes a query and returns a list of all the users attending a defined course. Course is chosen based on the function parameters `courseName` and `courseYear`.

An example output of this function will be:

[
  {
    'userID': 1,
    'Tipologia': 'Insegnante'
  },
  {
    'userID': 2,
    'Tipologia': 'Studente'
  },
  {
    'userID': 3,
    'Tipologia': 'Studente'
  },
  {
    'userID': 4,
    'Tipologia': 'Studente'
  }
]

getLessonsAttendancesCount()

Executes a query and returns in the form of JSON the count of attendances subdivided per lesson date and course ID. Takes as parameters a `range` which will be used to get all the lessons starting from the current date (obtained from datetime's date.today class' function) to `range` days before.

The output of this API will be:

[
  {
    "conteggioPresenze": 5,
    "dataLezione": "20/03/2024",
    "nomeCorso": "1a Cybersecurity"
  },
  {
    "conteggioPresenze": 14,
    "dataLezione": "20/03/2024",
    "nomeCorso": "1a Cybersecurity"
  },
  {
    "conteggioPresenze": 11,
    "dataLezione": "20/03/2024",
    "nomeCorso": "1a Cybersecurity"
  },
  {
    "conteggioPresenze": 8,
    "dataLezione": "20/03/2024",
    "nomeCorso": "1a Sviluppo software"
  },
  {
    "conteggioPresenze": 9,
    "dataLezione": "20/03/2024",
    "nomeCorso": "2a Sviluppo software"
  },
  {
    "conteggioPresenze": 1,
    "dataLezione": "20/03/2024",
    "nomeCorso": "3a Sviluppo software"
  },
  {
    "conteggioPresenze": 6,
    "dataLezione": "20/03/2024",
    "nomeCorso": "1a Sviluppo web"
  },
  {
    "conteggioPresenze": 3,
    "dataLezione": "20/03/2024",
    "nomeCorso": "2a Sviluppo web"
  },
  {
    "conteggioPresenze": 7,
    "dataLezione": "21/03/2024",
    "nomeCorso": "1a Sviluppo web"
  },
  {
    "conteggioPresenze": 2,
    "dataLezione": "24/03/2024",
    "nomeCorso": "1a Cybersecurity"
  }
]

reformatResponse()

Gets a list and converts each obtained course from the `getLessonsAttendancesCount` function to the desired format. Returns a list of dictionaries like this one:

[
  {
    "nomeCorso": "1a Cybersecurity"
    "dataLezione": "20/03/2024",
    "conteggioPresenze": 5,
  },
  {
    "nomeCorso": "1a Cybersecurity"
    "dataLezione": "20/03/2024",
    "conteggioPresenze": 14,
  },
  {
    "nomeCorso": "1a Cybersecurity"
    "dataLezione": "20/03/2024",
    "conteggioPresenze": 11,
  },
  {
    "nomeCorso": "1a Sviluppo software"
    "dataLezione": "20/03/2024",
    "conteggioPresenze": 8,
  },
  {
    "conteggioPresenze": 9,
    "dataLezione": "20/03/2024",
    "nomeCorso": "2a Sviluppo software"
  },
  {
    "nomeCorso": "3a Sviluppo software"
    "dataLezione": "20/03/2024",
    "conteggioPresenze": 1,
  },
  {
    "nomeCorso": "1a Sviluppo web"
    "dataLezione": "20/03/2024",
    "conteggioPresenze": 6,
  },
  {
    "nomeCorso": "2a Sviluppo web"
    "dataLezione": "20/03/2024",
    "conteggioPresenze": 3,
  },
  {
    "nomeCorso": "1a Sviluppo web"
    "dataLezione": "21/03/2024",
    "conteggioPresenze": 7,
  },
  {
    "nomeCorso": "1a Cybersecurity"
    "dataLezione": "24/03/2024",
    "conteggioPresenze": 2,
  }
]

getUserEnrolledCourses()

Executes a query and gets all courses' names and years based on function parameter `uid`. If the `uid` parameter is not passed, the default value will be the user's session value `uid`. Returns a list if the query returns valid values, else `False` if the connection to the database fails.

An example of a possible returning list can be:

[
  {
    'userID': 1,
    'Tipologia': 'Insegnante'
  },
  {
    'userID': 2,
    'Tipologia': 'Studente'
  },
  {
    'userID': 3,
    'Tipologia': 'Studente'
  },
  {
    'userID': 4,
    'Tipologia': 'Studente'
  }
]

getTeachersList()

Executes a query and returns a list of all teachers' IDs, names, and surnames if the query returns valid values, otherwise `False` if the connection to the Database fails.

This function will return a list similar to the following

[
  {
    'id': 10,
    'Nome': 'Francesco Verdi',
  },
  {
    'id': 11,
    'Nome': 'Mario Grigi',
  },
  {
    'id': 12,
    'Nome': 'Paolo Bianchi',
  }
]

isPageNumberValid()

Gets the `page` parameter from the URL and validates it based on some basic controls. Used in /lesson/list and /user/list routes. Returns the `page` value if it's valid, otherwise `False` if not.

getLessonInfo()

Executes a query and obtains all the information related to the lesson's `lessonID` function parameter. Returns a dictionary with all useful lesson information such as Subject, description, date and time, and all assigned teacher-related information.

The returned dictionary will be:

{
  idLezione: 1,
  'Materia': 'Python',
  'Descrizione': 'Sviluppo app in Flask',
  'dataLezione': '2024-03-27',
  'oraInizio': '09:00',
  'oraFine': '12:00',
  'aula': 'A001',
  'Tipologia': 'Laboratorio',
  'idInsegnante': 1,
  'Nome': 'Mario Rossi',
  'nomeCorso': '1a Sviluppo software'
}

update_lesson_data()

Updates lesson's data based on form submitted and validated values. Used in /lesson route. Returns `True` if the process succeeds, otherwise `False` with a flashed message if the process catches an error.

timedeltaConverter()

Converts a given `timedelta` type to datetime. Takes as parameters the actual `timedelta` `time` and its relative format to correctly convert the value. Returns the converted time.

About

Simple but useful app to easily record, manage, and track teachers' and students' attendances in schools.

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published