You are to extract the following data for the Fall 2009 and Winter 2010 Quarter Daily Enrollment Reports and Grade Distributions listed at http://ir.calstatela.edu/ir/.

Daily Enrollment Report
--------------------
Subject (Ex: ART)
Category (Ex: 101A)
Title (Ex: WORLD ART)
Days (Ex: TTr)
Start Time (Ex: 11:40)
End Time (Ex: 13:20)
Building (Ex: KH)
Room (Ex: LH1)
Instructor Name (Ex: Asgari Marjan)
Units (Ex: 4)
Class # (Ex: 10057)

Grade Distribution
--------------------
Class # (Ex: 10057)
Section (Ex: 01)
Grades (A, A-, B+, B, B-, C+, C, C-, D+, D, D-, F, I, CR, NC, W)
Average GPA (Ex. 3.16)

To extract this data you are to export the EXCEL files (xls) as Comma Separated Value files (csv). The exported files should be named:

Fall2009_DER.csv
Fall2009_Grades.csv
Winter2010_DER.csv
Winter2010_Grades.csv

Next, write a SHELL script named "PopulateDatabase.sh" that extracts the data listed above from the CSV files and inserts it into a MySQL database. The data should be stored in the following tables:

Final_Courses
--------------------
- Class_Number : Number
- Quarter : String (Ex: Fall 2009)
- Subject : String
- Category : String
- Title : String
- Days : String
- Start_Time : String
- End_Time : String
- Building : String
- Room : String
- Instructor_Name : String
- Units : Number

Final_Grades
--------------------
- Class_Number : Number
- Section : String
- Grade_Count_A : Number
- Grade_Count_A_Minus : Number
- Grade_Count_B_Plus : Number
- Grade_Count_B : Number
- Grade_Count_B_Minus : Number
- Grade_Count_C_Plus : Number
- Grade_Count_C : Number
- Grade_Count_C_Minus : Number
- Grade_Count_D_Plus : Number
- Grade_Count_D : Number
- Grade_Count_D_Minus : Number
- Grade_Count_F : Number
- Average_GPA : Number

In addition to "PopulateDatabase.sh", you should create an additional SHELL script named "SetupDatabase.sh" that contains all MySQL commands required to create the tables specified above. "PopulateDatabase.sh" should CALL "SetupDatabase.sh" internally.

Next, you are to create a PERL script named "FinalPerlSearch.pl" that takes two command-line arguments:

1. SearchAction
2. SearchValue

The "FinalPerlSearch.pl" script will be used to search the data that is stored in your database. The "SearchAction" argument will specify the type of search to perform, and "SearchValue" will be used to filter search results. Below are the types of search that your PERL script must support:

SUBJECT - Search By Subject (Ex: CS, ART, EE, etc…)
TITLE - Search By Title (Ex: Java, World Art, etc…)
INSTRUCTOR - Search By Instructor (Ex: Kang, Cervantes, etc…)
CATEGORY - Search By Category (Ex: 101A, 345C, 202, etc…)
CLASS_NUMBER - Search By Class Number (Ex: 10057)

"FinalPerlSearch.pl" will be called using the Common Gateway Interface (CGI) from the Apache Web Server. For example, if we wanted to list all courses taught by Dr. Eun-Young Elaine Kang, your script would be executed via the following URL (assuming your Student ID on the CS1 server is cs345s35):

http://cs1.calstatela.edu:8280/~cs345s35/cgi-bin/FinalPerlSearch.pl?SearchAction=INSTRUCTOR&SearchValue=Eun-Young

The output of your script should be formatted as follows:

[
["Class_Number","Subject","Category","Title","Days", "Start_Time", "End_Time", "Building", "Room", "Section", "Instructor_Name", "Average_GPA"]
]

For those familiar with JavaScript, the output of the script is an array of arrays. Beware, however, that the placement of quotation marks, colons, and commas, are critical to the description of the reported data. Below is the actual output we could expect to see when the above search is executed:

[
["17841","CS","202","INTRO TO OBJECT ORIENTED PROGR","TTR", "16:20", "18:00", "ET", "A210", "03", "Kang Eun-Young", "3.14"],
["16803","CS","598","GRADUATE DIRECTED STUDY","", "", "", "", "", "02", "Kang Eun-Young", ""]
]

The format of the output will always remain the same regardless of the "SearchAction" that is specified. Only the reported data will change based on the "SearchValue".

Finally, you are to deploy the following Web page(s) on the CS1 server.

final.zip

Assuming that your username is cs345s35, your pages will be located at:

http://cs1.calstatela.edu:8280/~cs345s35/final/

Grading:

10pts - CSV Files - Upload all CSV files to CSNS
10pts - SetupDatabase.sh - Script used for creating tables in your MySQL database
10pts - PopulateDatabase.sh - Script used to extract data from the CSV files and insert them into your MySQL database
50pts - FinalPerlSearch.pl - Perl Script that searches your MySQL database based on certain search-criteria and reports results as a JSON object
10pts - CS1 Deployment - Deploy the provided HTML/CSS/JavaScript files and your Perl Script to the CS1 server in the specified locations
10pts - LinkToFinalExamDemo.html - A simple Webpage that contains a hyperlink to your working demo on the CS1 server.
-----
Total: 100pts

Extra Credit:
5pts - Write a "SetupWebPage.sh" SHELL script that assumes final.zip is located in your home folder, and automatically extracts all files from the zip file, and moves them to their specified locations.
10pts - Re-write the index.html file as a PERL script located at:

http://cs1.calstatela.edu:8280/cgi-bin/FinalIndex.pl