Introduction

This project presents a Python-based server-side script designed to process encrypted attendance data from an IoT-based fingerprint system deployed in educational institutions. Integrated with Firebase Realtime Database, the script (`process_attendance_decryption.py`) decrypts AES-128 encrypted records, structures them using Pandas, and generates reports in CSV, HTML, and Excel formats. It automates data handling, ensures security, and provides actionable insights for attendance management. The script complements the embedded system, detailed in our Embedded Systems Projects, forming a robust end-to-end solution.

System Overview

Aim and Objectives

Aim:
To develop a server-side script for secure decryption and processing of IoT-generated attendance data, enabling automated reporting and analytics.

Objectives:

1. Retrieve encrypted attendance records from Firebase Realtime Database.
2. Implement AES-128 decryption with PKCS#7 padding for secure data access.
3. Generate structured CSV reports for each class and date.
4. Create styled HTML and Excel reports for user-friendly analytics.
5. Handle variable Firebase data structures (list and dictionary formats).
6. Ensure robust error handling for decryption and data processing.
7. Integrate with the IoT fingerprint system for end-to-end functionality.

Features & Deliverables

• Data Retrieval: Fetches encrypted records from Firebase under `/attendance_records/`.
• Secure Decryption: Uses AES-128 (CBC mode) with PKCS#7 padding to decrypt Base64-encoded data.
• Structured Reporting: Generates CSV files per class/date in `attendance/ /.csv`.
• Multi-Format Outputs: Produces styled HTML tables and formatted Excel spreadsheets.
• Data Handling: Supports both list and dictionary Firebase structures for compatibility.
• Error Management: Logs decryption failures and skips malformed records.
• Automation: Streamlines attendance data processing with minimal user intervention.

Code Implementation

The server-side script (`process_attendance_decryption.py`) is written in Python, leveraging libraries for Firebase access, decryption, and data processing. Sensitive credentials (e.g., Firebase API key, AES key/IV) are redacted with `*******` for security.

import pyrebase
import json
import base64
import pandas as pd
import os
from tabulate import tabulate
from datetime import datetime
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
from openpyxl.utils import get_column_letter
from Crypto.Cipher import AES
from Crypto.Util.Padding import unpad

# Firebase Configuration (Sensitive credentials redacted)
firebaseConfig = {
    "apiKey": "*******",
    "authDomain": "*******",
    "databaseURL": "*******",
    "projectId": "*******",
    "storageBucket": "*******",
    "messagingSenderId": "*******",
    "appId": "*******"
}

# Initialize Firebase
firebase = pyrebase.initialize_app(firebaseConfig)
db = firebase.database()

# AES Configuration (Sensitive key/IV redacted)
AES_KEY = b"*******"  # 16-byte key
AES_IV = b"*******"   # 16-byte IV

def decrypt_data(encrypted_b64, class_name, date, identifier):
    try:
        encrypted_data = base64.b64decode(encrypted_b64)
        cipher = AES.new(AES_KEY, AES.MODE_CBC, AES_IV)
        padded_data = cipher.decrypt(encrypted_data)
        plaintext = unpad(padded_data, AES.block_size)
        json_data = json.loads(plaintext.decode('utf-8'))
        print(f"Decrypted data for {class_name}/{date}/{identifier}: {json_data}")
        return json_data
    except Exception as e:
        print(f"Decryption error for {class_name}/{date}/{identifier}: {e}")
        return None

def fetch_and_process_attendance():
    attendance_records = db.child("attendance_records").get().val()
    all_csv_data = []
    for class_name in attendance_records:
        class_data = attendance_records[class_name]
        for date in class_data:
            date_data = class_data[date]
            csv_data = []
            if isinstance(date_data, dict):
                for id_key, record in date_data.items():
                    if id_key.startswith("id_"):
                        id_num = id_key.replace("id_", "")
                    else:
                        id_num = id_key
                    if isinstance(record, str):
                        encrypted_b64 = record
                        decrypted = decrypt_data(encrypted_b64, class_name, date, id_key)
                        if decrypted:
                            csv_data.append({
                                "id": decrypted["id"],
                                "matric_number": decrypted["matric_number"],
                                "course": decrypted["course"],
                                "date": decrypted["date"],
                                "present": decrypted["present"],
                                "present_time": decrypted["present_time"],
                                "absent_time": decrypted["absent_time"] if decrypted["absent_time"] else ""
                            })
            elif isinstance(date_data, list):
                for idx, record in enumerate(date_data):
                    if record:
                        encrypted_b64 = record
                        decrypted = decrypt_data(encrypted_b64, class_name, date, idx)
                        if decrypted:
                            csv_data.append({
                                "id": decrypted["id"],
                                "matric_number": decrypted["matric_number"],
                                "course": decrypted["course"],
                                "date": decrypted["date"],
                                "present": decrypted["present"],
                                "present_time": decrypted["present_time"],
                                "absent_time": decrypted["absent_time"] if decrypted["absent_time"] else ""
                            })
            if csv_data:
                df = pd.DataFrame(csv_data)
                output_dir = f"attendance/{class_name}"
                os.makedirs(output_dir, exist_ok=True)
                csv_path = f"{output_dir}/{date}.csv"
                df.to_csv(csv_path, index=False)
                print(f"Generated CSV: {csv_path}")
                all_csv_data.extend(csv_data)

    if all_csv_data:
        all_df = pd.DataFrame(all_csv_data)
        all_df.sort_values(by=["course", "date", "id"], inplace=True)
        print("\nAll Attendance Data:")
        print(tabulate(all_df, headers="keys", tablefmt="psql", showindex=False))

        # Generate HTML Report
        html_path = "attendance/attendance_report.html"
        html_content = """
        
        
            
            
        
        
            
Attendance Report
            
        """
        for _, row in all_df.iterrows():
            html_content += f"""
                
            """
        html_content += "

                

                    
ID
Matric Number
Course
Date
                    
Present
Present Time
Absent Time
                

                    
{row['id']}
{row['matric_number']}
{row['course']}
                    
{row['date']}
{row['present']}
{row['present_time']}
                    
{row['absent_time']}
                
"
        with open(html_path, "w") as f:
            f.write(html_content)
        print(f"Generated HTML: {html_path}")

        # Generate Excel Report
        excel_path = "attendance/attendance_summary.xlsx"
        wb = Workbook()
        ws = wb.active
        ws.title = "Attendance Summary"
        headers = ["ID", "Matric Number", "Course", "Date", "Present", "Present Time", "Absent Time"]
        ws.append(headers)
        header_fill = PatternFill(start_color="D3D3D3", end_color="D3D3D3", fill_type="solid")
        thin_border = Border(left=Side(style="thin"), right=Side(style="thin"),
                             top=Side(style="thin"), bottom=Side(style="thin"))
        for cell in ws[1]:
            cell.fill = header_fill
            cell.border = thin_border
            cell.font = Font(bold=True)
            cell.alignment = Alignment(horizontal="center")
        for _, row in all_df.iterrows():
            ws.append([row["id"], row["matric_number"], row["course"], row["date"],
                       row["present"], row["present_time"], row["absent_time"]])
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=7):
            for cell in row:
                cell.border = thin_border
                cell.alignment = Alignment(horizontal="left")
        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = max_length + 2
            ws.column_dimensions[column].width = adjusted_width
        wb.save(excel_path)
        print(f"Generated Excel: {excel_path}")

if __name__ == "__main__":
    fetch_and_process_attendance()
                 

Process / Methodology

Software Implementation

The script was developed in Python using Visual Studio Code, with the following workflow:

• Configure Firebase with Pyrebase4, using redacted credentials for security.
• Fetch encrypted records from `/attendance_records/` using Firebase’s REST API.
• Decrypt data with Pycryptodome, handling Base64 decoding and PKCS#7 unpadding.
• Structure data with Pandas, creating DataFrames for each class/date.
• Generate CSV files in `attendance//.csv`.
• Produce HTML reports with CSS styling and Excel reports with Openpyxl formatting.
• Implement error handling for decryption failures and malformed data.

Data Processing Workflow

View Data Processing Workflow (SVG file)

Challenges & Solutions

• Challenge: Handling mixed Firebase data structures (list vs. dictionary).
  Solution: Added conditional logic to process both formats dynamically.
• Challenge: Decryption errors due to invalid Base64 or padding.
  Solution: Implemented try-except blocks to log errors and skip invalid records.
• Challenge: Formatting Excel reports for readability.
  Solution: Used Openpyxl to add borders, headers, and auto-adjusted column widths.
• Challenge: Ensuring scalability for large datasets.
  Solution: Optimized Pandas operations and batched CSV generation.

Results & Impact

• Automated Reporting: Generated CSV, HTML, and Excel reports for 10+ classes across multiple dates.
• Secure Processing: Successfully decrypted 100% of valid AES-128 records.
• Scalability: Handled datasets with thousands of records efficiently.
• User Accessibility: Produced styled reports for easy analysis by administrators.
• Integration: Seamlessly complemented the IoT system, detailed in Embedded Systems Projects.

Sample Outputs Firebase Data View
Firebase Data View (registered students)
Firebase Data View (registered students)
Firebase Data View (attendance records)
Firebase Data View (attendance records) CSV Report for Class EIE520 CSV Report for Class EIE521 CSV Report for Class EIE522 HTML Report Excel Report

What I Learned

• Data Engineering: Mastered Pandas for efficient data manipulation and reporting.
• Security: Gained expertise in AES decryption and secure data handling.
• Automation: Learned to streamline workflows with Python scripting.
• Cloud Integration: Developed skills in Firebase API interactions.
• Error Handling: Improved robustness with comprehensive exception management.

Demonstration & Access

• GitHub Repository: View code & documentation
• Related Hardware Project: Explore the IoT Fingerprint System

Code Snippet

Future Enhancements

1. Integrate machine learning for attendance pattern analysis.
2. Add a web dashboard for real-time report visualization.
3. Support additional data formats (e.g., JSON, PDF).
4. Optimize decryption for larger datasets with parallel processing.

My Software and Myself Happy Me, and the Code I Built 🥳

Thank You for Visiting My Portfolio

I sincerely appreciate you exploring my software projects and learning about my work in data engineering and automation. I hope this project demonstrates my passion for building secure, scalable solutions. For the hardware counterpart, visit the Embedded Systems Projects.

If you have questions or wish to collaborate, please reach out via the Contact section. Your feedback is valued and will be addressed promptly.

Best regards,
Damilare Lekan Adekeye