2 files changed, 154 insertions, 0 deletions
diff --git a/grades.sqlite b/grades.sqlite
new file mode 100644
index 0000000..97000cf
--- /dev/null
+++ b/grades.sqlite
diff --git a/index.html b/index.html
new file mode 100644
index 0000000..7553b5f
--- /dev/null
+++ b/index.html
@@ -0,0 +1,154 @@
+<!DOCTYPE html>
+<html>
+<head>
+    <title>Home</title>
+    <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.9.0/sql-wasm.js" onload integrity="sha512-PAyD/84QEfxE1X/H3RDJY9kqXtJObyGq6qA93+LnkMNWdTLHjcTKHsDOcz6Y5xAdsaMGItRP5vNs4vtj3/FRuw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
+    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/sakura.css/css/sakura.css" type="text/css">
+</head>
+
+<body>
+    <h1>iNeedATitle</h1>
+    <form>
+    <textarea id="output" rows="10" cols="80"></textarea>
+    <input type="text" id="query" value="SELECT COUNT(*) FROM raw_data" style="width: 100%">
+    <br>
+    <input type="button" value="Run Raw Query" onclick="run()">
+    </form>
+    <hr>
+    <h2>Queries</h2>
+    <h3>By Instructor</h3>
+    <p>Find classes, and average grade for a given instructor</p>
+    <label for="instructor">Instructor:</label>
+    <input type="text" id="instructor" name="instructor" list="instructors">
+    <datalist id="instructors" class="instructorDatalist">
+        </datalist>
+        <button type="button" onclick="getClassesByInstructor()">Get Class Averages</button>
+    <form>
+        <div id="table-container"></div>
+    <script type="text/javascript">
+        let SQL = undefined
+        let db = undefined
+
+        async function fetchDatabase(url) {
+        const response = await fetch(url);
+            if (!response.ok) {
+                throw new Error(`HTTP error! status: ${response.status}`);
+            }
+
+            const blob = await response.blob();
+            const buffer = await blob.arrayBuffer();
+            const uInt8Array = new Uint8Array(buffer);
+
+            db = new SQL.Database(uInt8Array);
+
+            // populate instructors
+
+            let query = "SELECT DISTINCT insname1 AS instructor_name FROM raw_data WHERE insname1 IS NOT NULL UNION SELECT DISTINCT insname2 FROM raw_data WHERE insname2 IS NOT NULL UNION SELECT DISTINCT insname3 FROM raw_data WHERE insname3 IS NOT NULL;"
+            const results = db.exec(query);
+
+            const result = results[0];
+            const columns = result.columns;
+            const rows = result.values;
+
+            let datalist = document.getElementById("instructors");
+
+            for (let i = 0; i < rows.length; i++) {
+                let option = document.createElement("option");
+                option.value = rows[i][0];
+                datalist.appendChild(option);
+            }
+
+
+        }
+
+        async function loadDatabase() {
+        SQL = await initSqlJs({
+            locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.9.0/sql-wasm.wasm`
+        });
+        fetchDatabase("./grades.sqlite");
+        }
+
+        loadDatabase();
+
+        function run() {
+            const query = document.getElementById("query").value;
+            const results = db.exec(query);
+            const result = results[0];
+            const columns = result.columns;
+            const rows = result.values;
+            const output = document.getElementById("output");
+            output.value = JSON.stringify({columns, rows}, null, 2);
+        }
+
+function getClassesByInstructor() {
+    let instructor = document.getElementById("instructor").value;
+
+    let query = `SELECT CourseTitle, GROUP_CONCAT(DISTINCT YearTerm) AS YearTerms, MAX(Subject) AS Subject, MAX(Course) AS Course, ROUND(AVG(AVG_GRD), 2) AS AverageGrade FROM raw_data AS main WHERE insname1 = '${instructor}' OR insname2 = '${instructor}' OR insname3 = '${instructor}' GROUP BY CourseTitle`
+    console.log(query);
+
+    const results = db.exec(query);
+    const result = results[0];
+    const columns = result.columns;
+    const rows = result.values; 
+    const tableContainer = document.getElementById("table-container");
+    const table = document.createElement("table");
+    const thead = document.createElement("thead");
+    const tbody = document.createElement("tbody");
+    const headerRow = document.createElement("tr");
+
+    for (let i = 0; i < columns.length; i++) {
+        const headerCell = document.createElement("th");
+        headerCell.textContent = columns[i];
+        headerRow.appendChild(headerCell);
+    }
+
+    thead.appendChild(headerRow);
+
+    for (let i = 0; i < rows.length; i++) {
+        const row = document.createElement("tr");
+        for (let j = 0; j < rows[i].length; j++) {
+            const cell = document.createElement("td");
+            if (columns[j] === "YearTerms") {
+                cell.textContent = convertYearTerm(rows[i][j]);
+            } else if (!isNaN(rows[i][j]) && rows[i][j] !== null) {
+                cell.textContent = parseFloat(rows[i][j]).toFixed(2);
+            } else {
+                cell.textContent = rows[i][j];
+            }
+            row.appendChild(cell);
+        }
+        tbody.appendChild(row);
+    }
+
+    table.appendChild(thead);
+    table.appendChild(tbody);
+    tableContainer.innerHTML = "";
+    tableContainer.appendChild(table);
+}
+
+function convertYearTerm(yearTerms) {
+    return yearTerms.split(',').map(term => {
+        const year = term.slice(0, 4); // Assuming the year is 2000+
+        const termCode = term.slice(-1); // Getting the last character for the term
+        let termName = '';
+        switch(termCode) {
+            case '1':
+                termName = 'Spring';
+                break;
+            case '4':
+                termName = 'Summer';
+                break;
+            case '7':
+                termName = 'Fall';
+                break;
+            default:
+                termName = 'Unknown';
+        }
+        return `${termName} ${year}`;
+    }).join(', ');
+}
+
+
+    </script>
+</body>
+</html>