<!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">
<meta name="viewport" content="width=device-width, initial-scale=1">
</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>