From 74e62a80677d5158b0573d37e27e59030a5fa7d8 Mon Sep 17 00:00:00 2001 From: Navan Chauhan Date: Thu, 4 Jan 2024 18:51:59 +0530 Subject: sortable tables --- index.html | 111 ++++++++------ sorttable.js | 495 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 563 insertions(+), 43 deletions(-) create mode 100644 sorttable.js diff --git a/index.html b/index.html index e403cdc..201bee2 100644 --- a/index.html +++ b/index.html @@ -5,7 +5,7 @@ CU Boulder Grades Database - + @@ -14,7 +14,7 @@

iNeedATitle

Loading database...

- +
@@ -44,6 +44,30 @@
+
+

By Subject

+

...

+
+

By A&S Division

+

Useful for finding electives

+

Find classes, and average grade for a given A&S Division

+ + + + + +
+
+


@@ -127,18 +151,13 @@ }, null, 2); } - function getClassesByInstructor() { - let instructor = document.getElementById("instructor").value; - - let query = `SELECT Subject || ' ' || Course as "Class", CourseTitle as "Title", GROUP_CONCAT(DISTINCT YearTerm) AS YearTerms, ROUND(AVG(AVG_GRD), 2) AS "Average Grade", ROUND(AVG(PCT_A), 2) as "% A", ROUND(AVG(PCT_B), 2) as "% B", ROUND(AVG(PCT_C_MINUS_OR_BELOW), 2) as "% C- and below", ROUND(AVG(N_GRADE)) as "Average No. of Students" FROM raw_data AS main WHERE insname1 = "${instructor.toUpperCase()}" OR insname2 = "${instructor.toUpperCase()}" OR insname3 = "${instructor.toUpperCase()}" GROUP BY CourseTitle` - console.log(query); - - const results = db.exec(query); + function createTable(results, className) { const result = results[0]; const columns = result.columns; const rows = result.values; - const tableContainer = document.getElementById("table-container"); + const tableContainer = document.getElementById(className); const table = document.createElement("table"); + table.classList.add("sortable") const thead = document.createElement("thead"); const tbody = document.createElement("tbody"); const headerRow = document.createElement("tr"); @@ -173,48 +192,54 @@ tableContainer.appendChild(table); } + function getClassesByInstructor() { + let instructor = document.getElementById("instructor").value; + + let query = `SELECT Subject || ' ' || Course as "Class", CourseTitle as "Title", GROUP_CONCAT(DISTINCT YearTerm) AS YearTerms, ROUND(AVG(AVG_GRD), 2) AS "Average Grade", ROUND(AVG(PCT_A), 2) as "% A", ROUND(AVG(PCT_B), 2) as "% B", ROUND(AVG(PCT_C_MINUS_OR_BELOW), 2) as "% C- and below", ROUND(AVG(N_GRADE)) as "Average No. of Students" FROM raw_data AS main WHERE insname1 = "${instructor.toUpperCase()}" OR insname2 = "${instructor.toUpperCase()}" OR insname3 = "${instructor.toUpperCase()}" GROUP BY CourseTitle` + console.log(query); + + const results = db.exec(query); + createTable(results, "table-container") + sorttable.makeSortable(document.getElementById("table-container").children[0]); + } + function getClassesByClassCode() { let classCode = document.getElementById("classCode").value; - let query = `SELECT YearTerm as "Year-Term", insname1 as Instructor, AVG_GRD as "Average Grade", PCT_A as "% A", PCT_B as "% B", PCT_C_MINUS_OR_BELOW as "% C- and below", N_GRADE as "No. Graded" FROM raw_data WHERE Subject || ' ' || Course = '${classCode.toUpperCase()}';` + let query = `SELECT YearTerm as "YearTerms", insname1 as Instructor, AVG_GRD as "Average Grade", PCT_A as "% A", PCT_B as "% B", PCT_C_MINUS_OR_BELOW as "% C- and below", N_GRADE as "No. Graded" FROM raw_data WHERE Subject || ' ' || Course = '${classCode.toUpperCase()}';` const results = db.exec(query); - const result = results[0]; - const columns = result.columns; - const rows = result.values; - const tableContainer = document.getElementById("table-container-class-codes"); - const table = document.createElement("table"); - const thead = document.createElement("thead"); - const tbody = document.createElement("tbody"); - const headerRow = document.createElement("tr"); + createTable(results, "table-container-class-codes") + sorttable.makeSortable(document.getElementById("table-container-class-codes").children[0]); + } - for (let i = 0; i < columns.length; i++) { - const headerCell = document.createElement("th"); - headerCell.textContent = columns[i]; - headerRow.appendChild(headerCell); + function getClassesByDivision() { + let division = document.getElementById("division").value + let grade = document.getElementById("grade").value + + switch (grade) { + case 'upper': + grade = "BETWEEN 3000 and 4999"; + break; + case 'lower': + grade = "BETWEEN 0000 and 2999"; + break; + case 'both': + grade = "BETWEEN 0000 and 4999"; + break; + case 'grad': + grade = ">= 5000" + break; + default: + grade = ">= 0"; } - thead.appendChild(headerRow); + let query = `SELECT DISTINCT Subject || ' ' || Course AS "Class Code", CourseTitle as "Title", AVG(AVG_GRD) AS "Average Grade" FROM raw_data WHERE (CrsPBADiv = '${division}' AND COURSE ${grade} ) AND Subject != 'APPM' GROUP BY "Class Code", "Title"` - 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] === "Year-Term") { - 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); - } + console.log(query); - table.appendChild(thead); - table.appendChild(tbody); - tableContainer.innerHTML = ""; - tableContainer.appendChild(table); + const results = db.exec(query); + createTable(results, "table-container-division") + sorttable.makeSortable(document.getElementById("table-container-division").children[0]); } function convertYearTerm(yearTerms) { diff --git a/sorttable.js b/sorttable.js new file mode 100644 index 0000000..38b0fc6 --- /dev/null +++ b/sorttable.js @@ -0,0 +1,495 @@ +/* + SortTable + version 2 + 7th April 2007 + Stuart Langridge, http://www.kryogenix.org/code/browser/sorttable/ + + Instructions: + Download this file + Add to your HTML + Add class="sortable" to any table you'd like to make sortable + Click on the headers to sort + + Thanks to many, many people for contributions and suggestions. + Licenced as X11: http://www.kryogenix.org/code/browser/licence.html + This basically means: do what you want with it. +*/ + + +var stIsIE = /*@cc_on!@*/false; + +sorttable = { + init: function() { + // quit if this function has already been called + if (arguments.callee.done) return; + // flag this function so we don't do the same thing twice + arguments.callee.done = true; + // kill the timer + if (_timer) clearInterval(_timer); + + if (!document.createElement || !document.getElementsByTagName) return; + + sorttable.DATE_RE = /^(\d\d?)[\/\.-](\d\d?)[\/\.-]((\d\d)?\d\d)$/; + + forEach(document.getElementsByTagName('table'), function(table) { + if (table.className.search(/\bsortable\b/) != -1) { + sorttable.makeSortable(table); + } + }); + + }, + + makeSortable: function(table) { + if (table.getElementsByTagName('thead').length == 0) { + // table doesn't have a tHead. Since it should have, create one and + // put the first table row in it. + the = document.createElement('thead'); + the.appendChild(table.rows[0]); + table.insertBefore(the,table.firstChild); + } + // Safari doesn't support table.tHead, sigh + if (table.tHead == null) table.tHead = table.getElementsByTagName('thead')[0]; + + if (table.tHead.rows.length != 1) return; // can't cope with two header rows + + // Sorttable v1 put rows with a class of "sortbottom" at the bottom (as + // "total" rows, for example). This is B&R, since what you're supposed + // to do is put them in a tfoot. So, if there are sortbottom rows, + // for backwards compatibility, move them to tfoot (creating it if needed). + sortbottomrows = []; + for (var i=0; i5' : ' ▴'; + this.appendChild(sortrevind); + return; + } + if (this.className.search(/\bsorttable_sorted_reverse\b/) != -1) { + // if we're already sorted by this column in reverse, just + // re-reverse the table, which is quicker + sorttable.reverse(this.sorttable_tbody); + this.className = this.className.replace('sorttable_sorted_reverse', + 'sorttable_sorted'); + this.removeChild(document.getElementById('sorttable_sortrevind')); + sortfwdind = document.createElement('span'); + sortfwdind.id = "sorttable_sortfwdind"; + sortfwdind.innerHTML = stIsIE ? ' 6' : ' ▾'; + this.appendChild(sortfwdind); + return; + } + + // remove sorttable_sorted classes + theadrow = this.parentNode; + forEach(theadrow.childNodes, function(cell) { + if (cell.nodeType == 1) { // an element + cell.className = cell.className.replace('sorttable_sorted_reverse',''); + cell.className = cell.className.replace('sorttable_sorted',''); + } + }); + sortfwdind = document.getElementById('sorttable_sortfwdind'); + if (sortfwdind) { sortfwdind.parentNode.removeChild(sortfwdind); } + sortrevind = document.getElementById('sorttable_sortrevind'); + if (sortrevind) { sortrevind.parentNode.removeChild(sortrevind); } + + this.className += ' sorttable_sorted'; + sortfwdind = document.createElement('span'); + sortfwdind.id = "sorttable_sortfwdind"; + sortfwdind.innerHTML = stIsIE ? ' 6' : ' ▾'; + this.appendChild(sortfwdind); + + // build an array to sort. This is a Schwartzian transform thing, + // i.e., we "decorate" each row with the actual sort key, + // sort based on the sort keys, and then put the rows back in order + // which is a lot faster because you only do getInnerText once per row + row_array = []; + col = this.sorttable_columnindex; + rows = this.sorttable_tbody.rows; + for (var j=0; j 12) { + // definitely dd/mm + return sorttable.sort_ddmm; + } else if (second > 12) { + return sorttable.sort_mmdd; + } else { + // looks like a date, but we can't tell which, so assume + // that it's dd/mm (English imperialism!) and keep looking + sortfn = sorttable.sort_ddmm; + } + } + } + } + return sortfn; + }, + + getInnerText: function(node) { + // gets the text we want to use for sorting for a cell. + // strips leading and trailing whitespace. + // this is *not* a generic getInnerText function; it's special to sorttable. + // for example, you can override the cell text with a customkey attribute. + // it also gets .value for fields. + + if (!node) return ""; + + hasInputs = (typeof node.getElementsByTagName == 'function') && + node.getElementsByTagName('input').length; + + if (node.getAttribute("sorttable_customkey") != null) { + return node.getAttribute("sorttable_customkey"); + } + else if (typeof node.textContent != 'undefined' && !hasInputs) { + return node.textContent.replace(/^\s+|\s+$/g, ''); + } + else if (typeof node.innerText != 'undefined' && !hasInputs) { + return node.innerText.replace(/^\s+|\s+$/g, ''); + } + else if (typeof node.text != 'undefined' && !hasInputs) { + return node.text.replace(/^\s+|\s+$/g, ''); + } + else { + switch (node.nodeType) { + case 3: + if (node.nodeName.toLowerCase() == 'input') { + return node.value.replace(/^\s+|\s+$/g, ''); + } + case 4: + return node.nodeValue.replace(/^\s+|\s+$/g, ''); + break; + case 1: + case 11: + var innerText = ''; + for (var i = 0; i < node.childNodes.length; i++) { + innerText += sorttable.getInnerText(node.childNodes[i]); + } + return innerText.replace(/^\s+|\s+$/g, ''); + break; + default: + return ''; + } + } + }, + + reverse: function(tbody) { + // reverse the rows in a tbody + newrows = []; + for (var i=0; i=0; i--) { + tbody.appendChild(newrows[i]); + } + delete newrows; + }, + + /* sort functions + each sort function takes two parameters, a and b + you are comparing a[0] and b[0] */ + sort_numeric: function(a,b) { + aa = parseFloat(a[0].replace(/[^0-9.-]/g,'')); + if (isNaN(aa)) aa = 0; + bb = parseFloat(b[0].replace(/[^0-9.-]/g,'')); + if (isNaN(bb)) bb = 0; + return aa-bb; + }, + sort_alpha: function(a,b) { + if (a[0]==b[0]) return 0; + if (a[0] 0 ) { + var q = list[i]; list[i] = list[i+1]; list[i+1] = q; + swap = true; + } + } // for + t--; + + if (!swap) break; + + for(var i = t; i > b; --i) { + if ( comp_func(list[i], list[i-1]) < 0 ) { + var q = list[i]; list[i] = list[i-1]; list[i-1] = q; + swap = true; + } + } // for + b++; + + } // while(swap) + } +} + +/* ****************************************************************** + Supporting functions: bundled here to avoid depending on a library + ****************************************************************** */ + +// Dean Edwards/Matthias Miller/John Resig + +/* for Mozilla/Opera9 */ +if (document.addEventListener) { + document.addEventListener("DOMContentLoaded", sorttable.init, false); +} + +/* for Internet Explorer */ +/*@cc_on @*/ +/*@if (@_win32) + document.write("