1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
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>
|
