Project GymLocker

Responsibility ~ O(n)
Github: ProjectGymLocker

This was my first project with an impact on people. It was exciting, but it also came with a lot of unexpected changes. The first being that I was working for somebody. I was a tech support intern at my high school, part of a pilot program for their tech-related classes. In the interest of being fully honest, it was pretty boring. But towards the end of the spring semester I was asked if I wanted to do a special project.

The physical development (gym) department was becoming frustrated with their current system for tracking students, locks, and lockers. When I asked them what they were using, they said FileMaker Pro. Sure, that's fine, but all they were using was the database examiner tool. Essentially, it was being used as a really big and slightly more powerful Excel sheet. I was thus tasked with making things more usable. What I ended up making was a (semi-ugly) web application. It worked, though onboarding was rough. The project's goals weren't very clearly communicated and I believe the project was dropped after a year when they reverted back to FileMaker Pro. Still, I had a lot of fun and learned a lot, particularly in regards to project management.

The homepage. Please note the pictures (new in V2!) straight from Google.
Features

The problem statement was essentially "make it better," so I drafted up a few designs and got to work on the algorithms. It had all the basic recordkeeping amenities: add, edit, and delete for students and locks. It allowed for side-by-side views of students, a "class view" for each teacher, and a way for them to quickly add a tardy or gym rental count (the department kept track of how many gym uniforms a student had to borrow). I'm not going to say my design was good, or even better than Project Exchange for V1, but I had about 3 months to finish the project and little direction. I ended up pushing a huge usability update and moving it from a Linux (Apache) to Windows (Windows Server) environment over the following winter break.

The student view was simple. You could enter another student's ID to do a side-by-side.

One of the features I think turned out pretty well was the class view. For some reason, I think it looks nice. It was just a table listing every student in a teacher's classes. The teacher could enter a period number and the results would automatically update. For the web devs reading this, yes, it used AJAX. But to my inexperienced tech support intern self, it was magic. But then I did magic, and it turned out looking pretty nice. A quick note: the arrows indicate how the table is sorted. Green is first, yellow is second, red is third. I stopped there, since I couldn't imagine a use case for needing a 4 level deep sort to manage a list of a maximum of 300 students.

The class view, complete with auto-updating records and a clean-looking table. I didn't even use any 3rd-party plugins.

Similarly, and using the same table code, I made the search function. It used AJAX to automatically update the records as the user typed, which makes things super convenient. But then I also let the teachers do some more interesting queries that allowed < and > for numerical fields. Want to know every student with more than 3 tardies? Easy: >3. I thought this would be helpful. I never got any feedback, but I'd like to think someone at least noticed.

Watch as your results magically update! Also, please don't hit "all" with id = 1 (they all started with 1).

And then I decided to go a little crazy. The department head wanted a feature to "graduate" students into the next year (they initially wanted year to be stored as 9-12 instead of graduating year). So that would mean taking every student in the database and adding 1 to their year. My first thought was "I'm gonna need to password protect this." My second thought was "Well, I'll just make a feature to edit mass amounts of students and make this a special case!" To be completely honest, I'm not sure if that was a good idea. But hey, the option was there for the department head and teachers he trusted. So I built the aptly named "mass student editor" and pushed. Here's how it turned out:

This page was followed by multiple warning / confirmation pages that included a naive "revert" option.

It worked exactly as intended, even with the < and > symbols. All it took was a bunch of string manipulation and egregious violation of SQL safety principles.

Implementation

I again used a Django + MySQL mix for the backend technologies. As mentioned before, I initially built the system to work with Apache on a Linux server, but I adapted it to a Windows Server environment 6 months later to better fit with the school's existing infrastructure.

Most of the features were simple, so Django was mostly just used as an intermediary between the interface and the database. If someone wanted to add a student, a prebuilt query was sent to the database to add a student and return a student code. If a student needed to be change, it was a simple UPDATE query. Most of the project was design and display. Sure, I needed to do some string manipulation for the special search functions I provided, but that didn't take very long. Anyway, here's a small taste of that mechanism: 

# Main module function
			def main(search_parameters, search_values, limit):

				query_base = """SELECT * FROM gym_locker.locks WHERE"""

				first = True;
				for search_parameter, search_value in zip(search_parameters, search_values):

					# Enforces lowercase values for search_parameter and search_value
					try:
						search_parameter = search_parameter.lower()
						search_value = search_value.lower()
					except:
						pass

					partial_query = query_constructor(search_parameter, search_value)

					if first:
						query_base += """ """ + partial_query;
						first = False;
					else:
						query_base += """ AND """ + partial_query;

				query = query_base + """ LIMIT %d""" % (limit)
				...

Yes, I still used a generic "main()" function. After the snippet ends, I just sent the query off to the database. The query_constructor() function took the parameters and values and returned a LIKE query, as shown below: 

# Constructs custom SQL query based on parsed input without an operator
			def query_constructor(search_parameter, search_value):

				# Appends the "%" SQL wildcard (matches zero or more characters) to search_value
				try:
					search_value += "%"
				except:
					search_value = "%"

				query = """%s LIKE "%s" """ % (search_parameter, search_value)

				return query
				...

The database was pretty simple. It had a table for students, locks, and query records to keep track of what the gym teachers were doing and how they were using the program. In fact, I only had one version of the schema and it was completely 1-1 with the student / lock view pages; all data that I had was displayed on those pages.

Learning Points

The project was rushed and poorly planned. However, the takeaways were essential for the work I did at AbbVie later on in the summer. Here are some of them: