Swim Sheets

Overview

A common problem at swim meets is the backup that occurs from timers having to record the times for each swim on paper. The times then have to be manually entered in case one of the touchpads in the water does not register the swimmers touch, resulting in a false time. The objective of our project is to create an IPhone app to assist with swim meets. We want timers at swim meets to be able to input the time they recorded for the swimmer, based on the event number, lane number, and heat number, into the app instead of writing it on paper. This will allow the times recorded to go directly into the database instead of having to be manually entered at the conclusion of each swim event.

Here is a link to the weekly log that will contain updates on our progress for the project: https://classes.engineering.wustl.edu/ese205/core/index.php?title=Swim_Sheets_Log

Team Members

Hailey Jenkins
Rebecca Bruce

Objectives

We want the IPhone app’s interface to prompt the user for the event number, heat number, and lane number. The app should then return the swimmer’s name based off the psych sheet for the overall meet. The user can then double check the accuracy of the name and override it if necessary. Once the swim is complete, a time will be entered and stored in an excel spreadsheet that could then be inputted into a swim database at a later time.

The main goals of this app are to:
1. Create a database that takes in the psych sheet and can report the swimmers name.

2. Prompt the user for event number, heat number, and lane number

3. Display data in an organized manner for overall meet results and analysis.

4. The database should take in an input of data, consisting of rosters, events, heat, and lane. These should be imported into the database from a .txt file that can be used by various teams to import into the database.

5. Results will be generated and be viewable from the database for the coaches use. These results can also be exported into a spreadsheet for viewing.

6. The data for each race will be received from inputs from the app, not an external source.

Challenges

Learn how to create a public IP address to update the times.

Learn how to create a database and PHP files.

Learn how to use Apple’s IDE to create the application.

Learn how to import the data from a document.

Learn how to use the iCloud server.

Learn how to override errors in the input process.

Learn how to code in swift.

Learn how to read and access information from the php file with swift .

Learn how to limit the user input accepted in the UITextField .

Learn how to send information from one page of the app to the next .

Learn how to send the time input to the database from swift .

Learn how to embed search in the tableview .

Gantt Chart

Budget

Blue Host: $3.95 per month for 3 months

How To: Upload an Excel File into a Database

Description:
In order to upload an excel file into a data base, you first want to register with a web hosting site that will allow you to create data tables within a database. For our project, we used bluehost to create a domain online, a database user and a database. We then used https://codewithchris.com/iphone-app-connect-to-mysql-database/ to establish a database connection that will allow us to update and read the database from an outside source, in our case, this was XCode.

The code can be found at https://bitbucket.org/swimsheets/swimsheet.git

Setting up the Database:
The first step is to get a database set up. You can do this through the hosting website that you chose. You will want to set up a user for the database as well as the database itself. You can then use phpMyAdmin to create a table within this database.

Create an Excel File:
You can now create an excel file that matches the setup of the table you created in your database. The first row of the excel file should have titles that match those of the column names in your table. Add information into a few rows of the excel file so that you will be able to see if your code works. Save this file as an excel XML file.

Upload page:
The next step is creating a php file that will allow you to upload the excel document into the database. The code for this can be found at https://bitbucket.org/swimsheets/swimsheet/src/62cbaae0bb10316dd48f44ef94f161df9c15cc5f/Code%20for%20database/upload.php?at=master&fileviewer=file-view-default
You do not need to modify any of this code to get it to work; however, you can change the part that says “Names file” in line 8 to be the label of the file you are wanting to upload.

Importing the data:
You now want to create a new php file called import.php
Make sure the name is import.php as that is what the upload.php file you just created is linked to. The code that you will want to modify for import.php is here https://bitbucket.org/swimsheets/swimsheet/src/62cbaae0bb10316dd48f44ef94f161df9c15cc5f/Code%20for%20database/import.php?at=master&fileviewer=file-view-default

You will need to change line 6 that says mysqli_connect to match the username for the database user, the password for the database user, and the name of the database you are connecting to.

The first part of this code creates a function to add an object, in this case, we added a swimmer. In the parenthesis, you will want to edit it to match the columns you have in your table. The next part of the code creates an array. Change the parts in single parenthesis to match that of your excel file. The part that has $ in front should match the columns in your table and should match what is in your add function. The next part of the code actually reads in the file that you uploaded. Continue to modify the code to match the labels of your columns in the table and your excel file.
Where the code calls the add_person function is where you are actually entering the data into the table.

The part of the code within html is for displaying your excel data at yourwebhostingsite.com/import.php, for our project, this was swimsheets.com/import.php

This part is not necessary for your data to be imported into the table; however, it allows you to see if your data was imported correctly.

The labels within can be whatever you wish, but it makes the most sense to match it to the labels of the columns in your excel file and table.

The php echo is calling the data that you have uploaded in order to display it in the table you just created within html.

Testing: In order to test how your code works, go first to yourwebdomain.com/upload.php, upload the file as an xml file. You should be redirected to yourwebdomain.com/import.php where you should be able to see what has been uploaded to your table.

Design and Solutions

Design Components The Swimsheets app was designed to...

1. Allow coaches to fill out a simple Excel XML file
2. Have the file be uploaded, and the entries entered into a datatable automatically
3. Have an app read the datatable and display the swimmer for each specific event and heat to the corresponding lane on an app
4. Allow timers to input the time for each swim
5. Have the time data uploaded back into the datable
6. Display the results in an organized manner that can be found on the Swimsheets.com website

Interface

• We used Xcode to write the swift app
• Created five pages for the app and a loading screen
• Eight different classes

AppDelegate.swift: auto generated class to make sure app screen loads

ViewController.swift: Page One of SwimSheets App, Input Lane Number. Button "go" segue to PageTwo.

PageTwo.swift: Second page of SwimSheets App with HomeModlel.swift as a protocol. Displays the next input in the php file for the given lane, printing the event name, heat number, team name and the swimmer's first and last name. Then asks for a confirmation for the swimmer's name "Yes" Button segue to PageThree, "No" Button segue to PageFive.

PageThree.swift: Third page of SwimSheets input time (MM:SS.MsMs). UITextField only allows two digit inputs with the first input being no greater than six to insure valid time. Confirmation for the time "Yes" Button segue to PageFour, "No" Button clears time input by reloading the page.

PageFour.swift: When page is loaded, it sends time input to updateresult.php. Then Displays data from PageTwo ( event name, heat number, team name and the swimmer's first and last name) or if name of swimmer is changed PageFive ( swimmer's first and last name) , and time from page three. Then pick "Next Race" Button segue back to PageTwo to read the next input for the lane number or "Exit" to quit and segue back to ViewController (page one).

PageFive.swift: With HomeModlel.swift as a protocol. Displays all possible swimmer names in table view. The search bar can be used to find and select new swimmers name. After name is picked, "Go" Button segue to PageThree.

HomeModlel.swift: Connects app to getdata.php. This reads the file and sends each line of data to LocationModel as a jsonElement that can be read by classes with HomeModlel.swift as a protocol (PageTwo and PageFive).

LocationModel.swift:' Stores all the data sent from HomeModlel one line at a time as an NSObject that can be called and read.

Server and Database

• We used Bluehost to set an SQL database and a web domain (swimsheets.com)
• Used myphpadmin to set up the data table that has columns consisting of: ID (an auto incremented number), Team, First Name, Last Name, Event Number, Event Name, Heat Number, Lane Number, Time (this value can be null)
• Data is inserted into this table when the coaches go online and download the excel XML file, fill it out, and upload it at swimsheets.com/upload.php based on the instructions listed on swimsheets.com
• Created various php files needed to execute our commands using sql queries:
  

upload.php: this is where coaches can submit the entries for the meet, once the submit button is hit, the page is directed to swimsheets.com/import.php

import.php: uploads the meet entry files into the datatable

getdata.php: establishes a connection with the SQL database, displays the entries datatable as an array that can be accessed using Xcode in swift

updateresult.php: takes in the time that is entered in the app and adds it to the entries datatable, if there is a blank time given for a Swimmer, "No Show" will be entered into the time field

displaybyevent.php: displays the entries table, sorted by event number and lane number

displaybyname.php: displays the entries table, sorted alphabetically by last name

displaybyteam.php: displays the entries table, sorted by team

emptydata.php: this php codes empties the entries datatable so that a new meet can be started with a clean database

newmeet.php: creates a button that allows the user to empty the database before beginning a new meet

How our Project Runs

1. Download the excel file from swimsheets.com
   
2. Fill out the excel file for your individual team's events
   
3. If you are the home team, follow the directions on swimsheets.com to begin a new meet in the database by emptying the previous meet entries. This involves going to http://swimsheets.com/newmeet.php and clicking the Clear Data button
   
4. Each coach can now upload their individual team's meet files by going to http://swimsheets.com/upload.php and adding the file and pressing the upload button.
   
5. Once the file is uploaded, the coaches will be automatically redirected to http://swimsheets.com/import.php This page will allow the coaches to view the entries that they just uploaded in a table format
   
6. The app will then connect to the page http://swimsheets.com/getdata.php This connection allows the app to pull the entries that were just displayed on http://swimsheets.com/import.php and the entries are loaded into the app
   
7. Once the data is loaded into the app, the users can enter the lane number that they are timing for. They will be directed to the first entry that takes place in that lane. The application will follow the format shown in the table attached below.
   
8. The timer's will reach a screen on the application that allows them to input a time for the swimmer's race
   
9. Once the timer enters the time, the time is sent back to http://swimsheets.com/updatetime.php
   
10. The time will be loaded into the row that corresponds to that particular swimmer's event and time. This row is found based on the auto incremented ID that is generated when the files are initially uploaded into the table
    
11. These results can be viewed on the various viewing pages shown on http://swimsheets.com
    

http://swimsheets.com/displaybyname.php

http://swimsheets.com/displaybyevent.php
http://swimsheets.com/displaybyteam.php

Results

Final App Layout

Demo Poster

Comparison to Objectives

• Database

Objective: Create a database of swim entries that can be updated by the Swimsheets app

Our implementation: Successfully created a database that can take in a meet entries file and insert each entry into a new row of the entries datatable. Additionally, we created an auto incremented ID that is unique to each swim that each swimmer has in order to simplify keeping track of swims as the meet progresses and to streamline the process of finding where the time should be updated after each swim is swam.

• Connection between Database and App

Objective: Allow the app to pull the entries from the database table "entries" and be able to update the time column for an individual swimmer's event

Our implementation: We created a php file called getdata.php that allows the Swimsheets app to pull all the information out of the table entries that is stored in the database. After the users of the app have entered a time, the time is then reinserted into the table entries in the database. Each individual swim entry has an auto generated ID number that allows the app to match the entered time to the right row and column in the table.

• Demo

Objective: Show users how the app and database work together to create an updated version of the table entries that can be viewed online.

Our implementation: We used two different computers, one to show the app side of the project and one to show the database side of the project. The first computer showed people how we can upload an excel file and have it load into the database. We then have the second computer run through the swift application. The second computer inputs a time. We then transitioned back to the first computer to show how the time has now updated the entries table and can be viewed at one of the various viewing pages, which can be found at swimsheets.com

• User Interface

Objective: Allow the user to move smoothly between various app screens.

Our implementation: We created 7 different classes that allow the user to maneuver between different pages in the Swimsheets app. The diagram of how our classes interact can be found under the How our Project Runs section of this wikipage.

• Results Display

Objective: Allow the users to visually see the entries and updated results that are in the table entries.

Our Implementation: We created three different viewing screens to see the entries table. One view sorts the entries by name, another sorts the entries by team, and the third view sorts the entries by event. We also formatted these pages to have the background match that of swimsheets.com. We also added a hyperlink to allow users to smoothly transition between the viewing results page and the main website for the project.

Critical Decisions

One of the decisions we made was to simplify the structure of the database we created. Instead of doing multiple tables that are linked together as shown in the ERD, we created one data table called entries and used that to run all the php files and inputs/outputs from the app. The reason for this was that we lost a group member and needed to reassess how to finish the project in a timely manner. Given more time, the structure of the database would mimic that of the ERD, which would allow the app to be used in multiple swim meets more efficiently. The way we did it still allows for the app to be used, but the user must click a clear button on swimsheets.com to clear the data table before a new meet can be run.

Source Code bitbucket