Data Update Script

Relevant source files

The following files were used as context for generating this wiki page:

• generate_sample_data.py
• finder/views.py
• finder/templates/finder/upload_excel.html
• finder/static/finder/js/scripts.js
• README.md
• finder/tests.py

The Campuspal Room Finder relies on a robust data update system to populate the application with room and class schedule information. This system is composed of two primary components: a Python script for generating realistic sample data, and a web-based interface for administrators to upload this data in Excel (.xlsx) format. This process is the foundational step for making the room finder functional.

The system is designed to parse structured Excel files, validate their contents, and update the application's database accordingly. It provides feedback on the success of the import, as well as warnings for any data rows that could not be processed.

Overall Data Flow

The data pipeline begins with the optional generation of sample Excel files. A user (typically an administrator) then uploads these files through a dedicated web interface. The backend processes the files, clears existing data, and populates the database with the new room and schedule information.

The diagram below illustrates the complete data flow from generation to database population.

Sample Data Generation

To facilitate testing and provide a clear data format example, the repository includes a script to generate realistic sample data.

Sources: generate_sample_data.py

Execution

The script generate_sample_data.py uses the pandas library to create two Excel files: sample_data/rooms.xlsx and sample_data/timetable.xlsx. It is configured with various parameters to control the size and variety of the generated data.

Sources: generate_sample_data.py:84-85

Configuration

The script's behavior is controlled by several configuration variables at the top of the file.

Parameter	Description	Example Value
NUM_ROOMS	The total number of rooms to generate.	50
NUM_SCHEDULES	The total number of class schedule entries to generate.	300
BUILDINGS	A dictionary mapping full building names to their short codes.	{"Hephaestus Hall": "HH"}
AMENITIES_LIST	A list of possible amenities a room can have.	["Projector", "Whiteboard"]
PHOTO_URLS	A list of sample photo URLs for rooms.	https://images.unsplash.com/...
COURSES	A list of course codes.	["CS101", "MA203"]
DAYS	A list of valid day abbreviations.	["Mon", "Tue", "Wed", "Thu", "Fri"]

Sources: generate_sample_data.py:5-32

Output Schemas

rooms.xlsx

This file contains details about each room.

Field	Type	Description	Example
Room	String	The unique room identifier, typically a building code plus number.	HH101
Building	String	The full name of the building.	Hephaestus Hall
capacity	Integer	The seating capacity of the room.	30
amenities	JSON String	A JSON-formatted list of amenities available in the room.	["Projector", "Whiteboard"]
photo_url	String	A URL to an image of the room.	https://...

Sources: generate_sample_data.py:46-48

timetable.xlsx

This file contains the schedule of classes in various rooms.

Field	Type	Description	Example
Course	String	The course code.	CS101
Subject	String	The full name of the course subject.	Intro to Programming
Day/Time	String	A formatted string for the day and time slot.	Mon 09:00-10:30
Room	String	The identifier of the room where the class is held.	HH101
Building	String	The full name of the building.	Hephaestus Hall
Lecturer	String	The name of the lecturer.	Dr. Smith

Sources: generate_sample_data.py:79-81

Web-Based Data Upload

The primary method for updating data is through a web interface accessible to staff members at the /upload/ URL.

Sources: finder/templates/finder/upload_excel.html:23, finder/views.py:119

Frontend Interface

The upload page provides a user-friendly interface for uploading the rooms.xlsx and timetable.xlsx files.

• UI Components: The page (upload_excel.html) features two distinct drag-and-drop zones, one for the rooms file and one for the timetable file. Users can either drag files into these zones or click to open a file browser.
  Sources: finder/templates/finder/upload_excel.html:43-57
• Client-Side Logic: The file finder/static/finder/js/scripts.js enhances the user experience by:
  • Handling drag-and-drop events (dragover, dragleave, drop).
  • Displaying the name of the selected file in the UI.
  • Adding visual feedback (file-selected class) when a file is chosen.
  • Disabling the "Upload Files" button and showing a spinner during form submission to prevent multiple clicks and indicate processing.
    Sources: finder/static/finder/js/scripts.js:154-209

The following sequence diagram shows the interaction between the user, browser, and server during the upload process.

Backend Processing (upload_excel view)

The upload_excel function in finder/views.py handles the server-side logic. It is restricted to staff members via the @staff_member_required decorator.

Sources: finder/views.py:119

File Processing Logic

When a POST request is received, the view processes each uploaded file. A critical behavior is that it deletes all existing data from the corresponding database table before inserting the new data. This makes the upload a full replacement rather than an incremental update.

• Rooms File:

  1. The file is read into a pandas DataFrame.
  2. It checks for the presence of Room and Building columns.
  3. Room.objects.all().delete() is called to clear the table.
  4. It iterates through the DataFrame rows, creating or updating Room objects.
  5. Note: The web upload view only processes the Room and Building fields. Other fields like capacity and amenities are ignored by this view, though they are handled by the load_rooms_excel management command.
     Sources: finder/views.py:128-148, README.md:213-214

• Timetable File:

  1. The file is read into a pandas DataFrame.
  2. It checks for the expected columns (Course, Subject, etc.).
  3. ClassSchedule.objects.all().delete() is called.
  4. It iterates through each row, performing detailed parsing and validation.
     Sources: finder/views.py:152-249

Timetable Row Parsing

Each row in the timetable file undergoes a rigorous parsing process, illustrated by the flowchart below.

The Day/Time field is parsed using the following regular expression, which accommodates both 12-hour and 24-hour formats:

# From finder/views.py:187
match = re.match(
    r"(?P<day>\w+)\s+(?P<start>\d{1,2}:\d{2}\s*"
    r"(?:AM|PM)?)[-–](?P<end>\d{1,2}:\d{2}\s*"
    r"(?:AM|PM)?)",
    day_time)

Sources: finder/views.py:187-192

User Feedback

The system uses Django's messages framework to provide detailed feedback to the user after an upload attempt.

• messages.success(): Reports the number of rooms and/or timetable entries successfully loaded.
• messages.warning(): Informs the user about specific rows that were skipped due to formatting errors, invalid days, or rooms not being found.
• messages.error(): Reports critical errors that prevent a file from being processed, such as missing columns or file read exceptions.

Upon a successful upload of timetable data, the user is redirected to the main search page.
Sources: finder/views.py:146-148, 168-169, 193-228, 246-247, 251

Conclusion

The data update system is a cornerstone of the Campuspal application, providing a structured and reliable workflow for populating the database. It combines a helpful sample data generator with a user-friendly web upload interface. The backend processing is robust, with clear data replacement logic and detailed validation that provides actionable feedback to administrators, ensuring data integrity. While there is a slight feature discrepancy between the web upload and management commands for room data, the overall system is well-architected for its purpose.