Fossil SCM

Implement sorting by event count on some of the stats_report pages.

drh 2013-05-05 23:49 trunk
Commit 10aaf0c9713e139dc1a21f5569967939e8376137
Parent d0ce29b031e148a…
2 files changed +20 -5 +7 -7
~ src/report.c ~ src/timeline.c
M src/report.c
+20 -5
--- src/report.c
+++ src/report.c
@@ -923,23 +923,29 @@
923923
 **
924924
 ** The javascript is derived from:
925925
 **
926926
 ** http://www.webtoolkit.info/sortable-html-table.html
927927
 **
928
+** This variation allows column types to be expressed using the second
929
+** argument. Each character of the second argument represent a column.
930
+** "t" means sort as text. "n" means sort numerically. "x" means do not
931
+** sort on this column. If there are fewer characters in zColumnTypes[] than
932
+** their are columns, the all extra columns assume type "t" (text).
928933
 */
929
-void output_table_sorting_javascript(const char *zTableId){
934
+void output_table_sorting_javascript(const char *zTableId, const char *zColumnTypes){
930935
 @ <script>
931
- @ function SortableTable(tableEl){
936
+ @ function SortableTable(tableEl,columnTypes){
932937
 @ this.tbody = tableEl.getElementsByTagName('tbody');
933938
 @ this.sort = function (cell) {
934939
 @ var column = cell.cellIndex;
940
+ @ var sortFn = cell.sortType=="n" ? this.sortNumeric : this.sortText;
935941
 @ this.sortIndex = column;
936942
 @ var newRows = new Array();
937943
 @ for (j = 0; j < this.tbody[0].rows.length; j++) {
938944
 @ newRows[j] = this.tbody[0].rows[j];
939945
 @ }
940
- @ newRows.sort(this.sortText);
946
+ @ newRows.sort(sortFn);
941947
 @ if (cell.getAttribute("sortdir") == 'down') {
942948
 @ newRows.reverse();
943949
 @ cell.setAttribute('sortdir','up');
944950
 @ } else {
945951
 @ cell.setAttribute('sortdir','down');
@@ -953,10 +959,18 @@
953959
 @ aa = a.cells[i].textContent.replace(/^\W+/,'').toLowerCase();
954960
 @ bb = b.cells[i].textContent.replace(/^\W+/,'').toLowerCase();
955961
 @ if(aa==bb) return 0;
956962
 @ if(aa<bb) return -1;
957963
 @ return 1;
964
+ @ }
965
+ @ this.sortNumeric = function(a,b) {
966
+ @ var i = thisObject.sortIndex;
967
+ @ aa = parseFloat(a.cells[i].textContent);
968
+ @ if (isNaN(aa)) aa = 0;
969
+ @ bb = parseFloat(b.cells[i].textContent);
970
+ @ if (isNaN(bb)) bb = 0;
971
+ @ return aa-bb;
958972
 @ }
959973
 @ var thisObject = this;
960974
 @ var x = tableEl.getElementsByTagName('thead');
961975
 @ if(!(this.tbody && this.tbody[0].rows && this.tbody[0].rows.length>0)){
962976
 @ return;
@@ -966,17 +980,18 @@
966980
 @ } else {
967981
 @ return;
968982
 @ }
969983
 @ for (var i=0; i<sortRow.cells.length; i++) {
970984
 @ sortRow.cells[i].sTable = this;
985
+ @ sortRow.cells[i].sortType = columnTypes[i] || 't';
971986
 @ sortRow.cells[i].onclick = function () {
972987
 @ this.sTable.sort(this);
973988
 @ return false;
974989
 @ }
975990
 @ }
976991
 @ }
977
- @ var t = new SortableTable(gebi("%s(zTableId)"));
992
+ @ var t = new SortableTable(gebi("%s(zTableId)"),"%s(zColumnTypes)");
978993
 @ </script>
979994
 }
980995
981996
982997
 /*
@@ -1067,11 +1082,11 @@
10671082
 if( zErr1 ){
10681083
 @ <p class="reportError">Error: %h(zErr1)</p>
10691084
 }else if( zErr2 ){
10701085
 @ <p class="reportError">Error: %h(zErr2)</p>
10711086
 }
1072
- output_table_sorting_javascript("reportTable");
1087
+ output_table_sorting_javascript("reportTable","");
10731088
 style_footer();
10741089
 }else{
10751090
 report_restrict_sql(&zErr1);
10761091
 sqlite3_exec_readonly(g.db, zSql, output_tab_separated, &count, &zErr2);
10771092
 report_unrestrict_sql();
10781093
--- src/report.c
+++ src/report.c
@@ -923,23 +923,29 @@
923 **
924 ** The javascript is derived from:
925 **
926 ** http://www.webtoolkit.info/sortable-html-table.html
927 **
 
 
 
 
 
928 */
929 void output_table_sorting_javascript(const char *zTableId){
930 @ <script>
931 @ function SortableTable(tableEl){
932 @ this.tbody = tableEl.getElementsByTagName('tbody');
933 @ this.sort = function (cell) {
934 @ var column = cell.cellIndex;
 
935 @ this.sortIndex = column;
936 @ var newRows = new Array();
937 @ for (j = 0; j < this.tbody[0].rows.length; j++) {
938 @ newRows[j] = this.tbody[0].rows[j];
939 @ }
940 @ newRows.sort(this.sortText);
941 @ if (cell.getAttribute("sortdir") == 'down') {
942 @ newRows.reverse();
943 @ cell.setAttribute('sortdir','up');
944 @ } else {
945 @ cell.setAttribute('sortdir','down');
@@ -953,10 +959,18 @@
953 @ aa = a.cells[i].textContent.replace(/^\W+/,'').toLowerCase();
954 @ bb = b.cells[i].textContent.replace(/^\W+/,'').toLowerCase();
955 @ if(aa==bb) return 0;
956 @ if(aa<bb) return -1;
957 @ return 1;
 
 
 
 
 
 
 
 
958 @ }
959 @ var thisObject = this;
960 @ var x = tableEl.getElementsByTagName('thead');
961 @ if(!(this.tbody && this.tbody[0].rows && this.tbody[0].rows.length>0)){
962 @ return;
@@ -966,17 +980,18 @@
966 @ } else {
967 @ return;
968 @ }
969 @ for (var i=0; i<sortRow.cells.length; i++) {
970 @ sortRow.cells[i].sTable = this;
 
971 @ sortRow.cells[i].onclick = function () {
972 @ this.sTable.sort(this);
973 @ return false;
974 @ }
975 @ }
976 @ }
977 @ var t = new SortableTable(gebi("%s(zTableId)"));
978 @ </script>
979 }
980
981
982 /*
@@ -1067,11 +1082,11 @@
1067 if( zErr1 ){
1068 @ <p class="reportError">Error: %h(zErr1)</p>
1069 }else if( zErr2 ){
1070 @ <p class="reportError">Error: %h(zErr2)</p>
1071 }
1072 output_table_sorting_javascript("reportTable");
1073 style_footer();
1074 }else{
1075 report_restrict_sql(&zErr1);
1076 sqlite3_exec_readonly(g.db, zSql, output_tab_separated, &count, &zErr2);
1077 report_unrestrict_sql();
1078
--- src/report.c
+++ src/report.c
@@ -923,23 +923,29 @@
923 **
924 ** The javascript is derived from:
925 **
926 ** http://www.webtoolkit.info/sortable-html-table.html
927 **
928 ** This variation allows column types to be expressed using the second
929 ** argument. Each character of the second argument represent a column.
930 ** "t" means sort as text. "n" means sort numerically. "x" means do not
931 ** sort on this column. If there are fewer characters in zColumnTypes[] than
932 ** their are columns, the all extra columns assume type "t" (text).
933 */
934 void output_table_sorting_javascript(const char *zTableId, const char *zColumnTypes){
935 @ <script>
936 @ function SortableTable(tableEl,columnTypes){
937 @ this.tbody = tableEl.getElementsByTagName('tbody');
938 @ this.sort = function (cell) {
939 @ var column = cell.cellIndex;
940 @ var sortFn = cell.sortType=="n" ? this.sortNumeric : this.sortText;
941 @ this.sortIndex = column;
942 @ var newRows = new Array();
943 @ for (j = 0; j < this.tbody[0].rows.length; j++) {
944 @ newRows[j] = this.tbody[0].rows[j];
945 @ }
946 @ newRows.sort(sortFn);
947 @ if (cell.getAttribute("sortdir") == 'down') {
948 @ newRows.reverse();
949 @ cell.setAttribute('sortdir','up');
950 @ } else {
951 @ cell.setAttribute('sortdir','down');
@@ -953,10 +959,18 @@
959 @ aa = a.cells[i].textContent.replace(/^\W+/,'').toLowerCase();
960 @ bb = b.cells[i].textContent.replace(/^\W+/,'').toLowerCase();
961 @ if(aa==bb) return 0;
962 @ if(aa<bb) return -1;
963 @ return 1;
964 @ }
965 @ this.sortNumeric = function(a,b) {
966 @ var i = thisObject.sortIndex;
967 @ aa = parseFloat(a.cells[i].textContent);
968 @ if (isNaN(aa)) aa = 0;
969 @ bb = parseFloat(b.cells[i].textContent);
970 @ if (isNaN(bb)) bb = 0;
971 @ return aa-bb;
972 @ }
973 @ var thisObject = this;
974 @ var x = tableEl.getElementsByTagName('thead');
975 @ if(!(this.tbody && this.tbody[0].rows && this.tbody[0].rows.length>0)){
976 @ return;
@@ -966,17 +980,18 @@
980 @ } else {
981 @ return;
982 @ }
983 @ for (var i=0; i<sortRow.cells.length; i++) {
984 @ sortRow.cells[i].sTable = this;
985 @ sortRow.cells[i].sortType = columnTypes[i] || 't';
986 @ sortRow.cells[i].onclick = function () {
987 @ this.sTable.sort(this);
988 @ return false;
989 @ }
990 @ }
991 @ }
992 @ var t = new SortableTable(gebi("%s(zTableId)"),"%s(zColumnTypes)");
993 @ </script>
994 }
995
996
997 /*
@@ -1067,11 +1082,11 @@
1082 if( zErr1 ){
1083 @ <p class="reportError">Error: %h(zErr1)</p>
1084 }else if( zErr2 ){
1085 @ <p class="reportError">Error: %h(zErr2)</p>
1086 }
1087 output_table_sorting_javascript("reportTable","");
1088 style_footer();
1089 }else{
1090 report_restrict_sql(&zErr1);
1091 sqlite3_exec_readonly(g.db, zSql, output_tab_separated, &count, &zErr2);
1092 report_unrestrict_sql();
1093
M src/timeline.c
+7 -7
--- src/timeline.c
+++ src/timeline.c
@@ -1937,16 +1937,21 @@
19371937
 @ <td></td>
19381938
 @ <td colspan='2'>Yearly total: %d(nEventsPerYear)</td>
19391939
 @</tr>
19401940
 }
19411941
1942
+#if 0
19421943
 rowClass = ++nRowNumber % 2;
19431944
 @ <tr class='row%d(rowClass)'>
19441945
 @ <td colspan='3'>Total events: %d(nEventTotal)</td>
19451946
 @ </tr>
1947
+#endif
19461948
 @ </tbody></table>
19471949
 db_finalize(&query);
1950
+ if( !includeMonth ){
1951
+ output_table_sorting_javascript("statsTable","tnx");
1952
+ }
19481953
 }
19491954
19501955
 void stats_report_by_user(){
19511956
 Stmt query = empty_Stmt;
19521957
 int const nPixelsPerEvent = 1; /* for sizing the "graph" part */
@@ -1957,11 +1962,11 @@
19571962
 Blob sql = empty_blob; /* SQL */
19581963
 blob_append(&sql,
19591964
 "SELECT user, "
19601965
 "COUNT(*) AS eventCount "
19611966
 "FROM event "
1962
- "GROUP BY user ORDER BY user",
1967
+ "GROUP BY user ORDER BY user COLLATE nocase",
19631968
 -1);
19641969
 db_prepare(&query, blob_str(&sql));
19651970
 blob_reset(&sql);
19661971
 @ <h1>Timeline Events by User</h1>
19671972
 @ <table class='statistics-report-table-events' border='0'
@@ -1990,18 +1995,13 @@
19901995
 /*
19911996
 Potential improvement: calculate the min/max event counts and
19921997
 use percent-based graph bars.
19931998
 */
19941999
 }
1995
-
1996
- rowClass = ++nRowNumber % 2;
1997
- @ <tr class='row%d(rowClass)'>
1998
- @ <td colspan='3'>Total events: %d(nEventTotal)</td>
1999
- @ </tr>
20002000
 @ </tbody></table>
20012001
 db_finalize(&query);
2002
- output_table_sorting_javascript("statsTable");
2002
+ output_table_sorting_javascript("statsTable","tnx");
20032003
 }
20042004
20052005
 /*
20062006
 ** WEBPAGE: stats_report
20072007
 **
20082008
--- src/timeline.c
+++ src/timeline.c
@@ -1937,16 +1937,21 @@
1937 @ <td></td>
1938 @ <td colspan='2'>Yearly total: %d(nEventsPerYear)</td>
1939 @</tr>
1940 }
1941
 
1942 rowClass = ++nRowNumber % 2;
1943 @ <tr class='row%d(rowClass)'>
1944 @ <td colspan='3'>Total events: %d(nEventTotal)</td>
1945 @ </tr>
 
1946 @ </tbody></table>
1947 db_finalize(&query);
 
 
 
1948 }
1949
1950 void stats_report_by_user(){
1951 Stmt query = empty_Stmt;
1952 int const nPixelsPerEvent = 1; /* for sizing the "graph" part */
@@ -1957,11 +1962,11 @@
1957 Blob sql = empty_blob; /* SQL */
1958 blob_append(&sql,
1959 "SELECT user, "
1960 "COUNT(*) AS eventCount "
1961 "FROM event "
1962 "GROUP BY user ORDER BY user",
1963 -1);
1964 db_prepare(&query, blob_str(&sql));
1965 blob_reset(&sql);
1966 @ <h1>Timeline Events by User</h1>
1967 @ <table class='statistics-report-table-events' border='0'
@@ -1990,18 +1995,13 @@
1990 /*
1991 Potential improvement: calculate the min/max event counts and
1992 use percent-based graph bars.
1993 */
1994 }
1995
1996 rowClass = ++nRowNumber % 2;
1997 @ <tr class='row%d(rowClass)'>
1998 @ <td colspan='3'>Total events: %d(nEventTotal)</td>
1999 @ </tr>
2000 @ </tbody></table>
2001 db_finalize(&query);
2002 output_table_sorting_javascript("statsTable");
2003 }
2004
2005 /*
2006 ** WEBPAGE: stats_report
2007 **
2008
--- src/timeline.c
+++ src/timeline.c
@@ -1937,16 +1937,21 @@
1937 @ <td></td>
1938 @ <td colspan='2'>Yearly total: %d(nEventsPerYear)</td>
1939 @</tr>
1940 }
1941
1942 #if 0
1943 rowClass = ++nRowNumber % 2;
1944 @ <tr class='row%d(rowClass)'>
1945 @ <td colspan='3'>Total events: %d(nEventTotal)</td>
1946 @ </tr>
1947 #endif
1948 @ </tbody></table>
1949 db_finalize(&query);
1950 if( !includeMonth ){
1951 output_table_sorting_javascript("statsTable","tnx");
1952 }
1953 }
1954
1955 void stats_report_by_user(){
1956 Stmt query = empty_Stmt;
1957 int const nPixelsPerEvent = 1; /* for sizing the "graph" part */
@@ -1957,11 +1962,11 @@
1962 Blob sql = empty_blob; /* SQL */
1963 blob_append(&sql,
1964 "SELECT user, "
1965 "COUNT(*) AS eventCount "
1966 "FROM event "
1967 "GROUP BY user ORDER BY user COLLATE nocase",
1968 -1);
1969 db_prepare(&query, blob_str(&sql));
1970 blob_reset(&sql);
1971 @ <h1>Timeline Events by User</h1>
1972 @ <table class='statistics-report-table-events' border='0'
@@ -1990,18 +1995,13 @@
1995 /*
1996 Potential improvement: calculate the min/max event counts and
1997 use percent-based graph bars.
1998 */
1999 }
 
 
 
 
 
2000 @ </tbody></table>
2001 db_finalize(&query);
2002 output_table_sorting_javascript("statsTable","tnx");
2003 }
2004
2005 /*
2006 ** WEBPAGE: stats_report
2007 **
2008

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button