From 8292f99d5a0da91490b7bb445b4db0405a22045f Mon Sep 17 00:00:00 2001
From: Ted Trask <ttrask01@yahoo.com>
Date: Mon, 7 Sep 2015 22:30:42 +0000
Subject: Add server-side pagination and filtering to viewtable action using
 jquery-tablesorter

Note that filtering fails for non-character columns and have seen some table entries cause failures
---
 lib/db-viewtable-html.lsp | 203 +++++++++++++++++++++++++++++++---------------
 1 file changed, 136 insertions(+), 67 deletions(-)

(limited to 'lib/db-viewtable-html.lsp')

diff --git a/lib/db-viewtable-html.lsp b/lib/db-viewtable-html.lsp
index a8efaa7..3c89c41 100644
--- a/lib/db-viewtable-html.lsp
+++ b/lib/db-viewtable-html.lsp
@@ -11,17 +11,14 @@
 <script type="text/javascript">
 	if (typeof $.tablesorter == 'undefined') {
 		document.write('<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/jquery.tablesorter.js"><\/script>');
+		document.write('<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/jquery.tablesorter.widgets.js"><\/script>');
+		document.write('<link href="<%= html.html_escape(page_info.wwwprefix..page_info.staticdir) %>/tablesorter/jquery.tablesorter.pager.css" rel="stylesheet">');
+		document.write('<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/widgets/widget-pager.js"><\/script>');
 	}
 </script>
 
-<script type="text/javascript">
-	$(document).ready(function() {
-		$("#list").tablesorter({widgets: ['zebra']});
-		$(".deletetableentry").click(function(){ return confirm("Are you sure you want to delete this entry?")});
-	});
-</script>
-
 <%
+-- Calculate the redir to get back to this page (and set up keyvalues used to createtableentry with same connection and table)
 local redir = cfe({ type="hidden", value=page_info.orig_action.."?table="..html.url_encode(form.value.table.value) })
 local keyvalues = {table=form.value.table.value}
 if form.value.connection then
@@ -32,52 +29,28 @@ if form.value.connection then
 	end
 end
 keyvalues.redir = redir.value
-%>
-
-<% htmlviewfunctions.displaycommandresults({"deletetableentry", "updatetableentry"}, session) %>
-<% htmlviewfunctions.displaycommandresults({"createtableentry"}, session, true) %>
 
-<% if form.value.table.value ~= "" then form.label = form.label.." - "..form.value.table.value end %>
-<% local header_level = htmlviewfunctions.displaysectionstart(form, page_info) %>
-<%
-local header_level2 = htmlviewfunctions.incrementheader(header_level)
-htmlviewfunctions.displayformstart(form, page_info)
-if form.value.connection and ( next(form.value.connection.value) ~= nil or form.value.connection.errtxt ) then
-	htmlviewfunctions.displayitem(form.value.connection, page_info, header_level2, "connection")
-end
-htmlviewfunctions.displayitem(form.value.table, page_info, header_level2, "table")
-form.option = "Update"
-htmlviewfunctions.displayformend(form, htmlviewfunctions.incrementheader(header_level))
-%>
-<table id="list" class="tablesorter"><thead>
-	<tr>
-	<% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %>
-		<th>Action</th>
-	<% end %>
-	<% for i,f in ipairs(form.value.fields.value) do %>
-		<th><%= html.html_escape(f) %></th>
-	<% end %>
-	</tr>
-</thead><tbody>
-<% -- We will reuse the form connection structure to pass key values to updatetableentry and deletetableentry
+-- Create formvalues table to pass connection and key values to updatetableentry and deletetableentry
 local formvalues = {}
 if form.value.connection then
-	formvalues.connection = form.value.connection
-	for n,v in pairs(form.value.connection.value) do v.type="hidden" end
+	formvalues.connection = cfe({type="group", value={}})
+	for n,v in pairs(form.value.connection.value) do
+		formvalues.connection.value[n] = cfe({type="hidden", value=v.value})
+	end
 end
 local reversekeyfields = {}
 if form.value.keyfields then
 	formvalues.fields = cfe({ type="group", value={} })
 	formvalues.nulls = cfe({ type="group", value={} })
 	for i,f in ipairs(form.value.keyfields.value) do
-		formvalues.fields.value[f] = cfe({ type="hidden" })
-		formvalues.nulls.value[f] = cfe({ type="hidden", value="false" })
+		formvalues.fields.value[f] = cfe({ type="hidden", value=f.."_VALUE" })
+		formvalues.nulls.value[f] = cfe({ type="hidden", value=f.."_NULL" })
 		reversekeyfields[f] = i
 	end
 end
-formvalues.table = form.value.table
-form.value.table.type = "hidden"
+formvalues.table = cfe({type="hidden", value=form.value.table.value})
 formvalues.redir = redir
+
 -- We will hide the update link if all fields are key fields
 local allowupdate = false
 for i,f in ipairs(form.value.fields.value) do
@@ -88,41 +61,137 @@ for i,f in ipairs(form.value.fields.value) do
 end
 allowupdate = allowupdate and viewlibrary.check_permission("updatetableentry")
 %>
-<% for i,tableentry in ipairs(form.value.entries.value) do %>
-	<tr>
-	<% if viewlibrary.check_permission("deletetableentry") or allowupdate then %>
+
+<script type="text/javascript">
+	$(document).ready(function() {
+		// The following is a hack to include a multiline string
+		var MultiString = function(f) {
+			return f.toString().split('\n').slice(1, -1).join('\n');
+		}
+		var actions = MultiString(function() {/**
 		<%
-		if formvalues.fields then
-			for i,f in ipairs(form.value.keyfields.value) do
-				if not tableentry[f] then
-					formvalues.nulls.value[f].value = "true"
-				else
-					formvalues.fields.value[f].value = tableentry[f]
-				end
+			if allowupdate then
+				htmlviewfunctions.displayitem(cfe({type="link", value=formvalues, label="", option="Update", action="updatetableentry"}), page_info, -1)
+			end
+			if viewlibrary.check_permission("deletetableentry") then
+				htmlviewfunctions.displayitem(cfe({type="form", value=formvalues, label="", option="Delete", action="deletetableentry", class="deletetableentry"}), page_info, -1)
 			end
-		end
 		%>
-		<td>
-	<% if allowupdate then %>
-		<% htmlviewfunctions.displayitem(cfe({type="link", value=formvalues, label="", option="Update", action="updatetableentry"}), page_info, -1) %>
-	<% end %>
-	<% if viewlibrary.check_permission("deletetableentry") then %>
-		<% htmlviewfunctions.displayitem(cfe({type="form", value=formvalues, label="", option="Delete", action="deletetableentry", class="deletetableentry"}), page_info, -1) %>
-	<% end %>
-		</td>
+		**/});
+
+		var columns = [<% if viewlibrary.check_permission("deletetableentry") or allowupdate then print('"action", ') end%>"<%= table.concat(form.value.fields.value, '", "') %>"];
+
+                $("#list").tablesorter({widgets: ['zebra', 'filter', 'pager'], widgetOptions: {
+			// Filtering is handled by the server
+			filter_serversideFiltering: true,
+
+			// We can put the page number and size here, filtering and sorting handled by pager_customAjaxUrl
+			pager_ajaxUrl : '<%= html.html_escape(page_info.script)..redir.value %>&viewtype=json&page={page+1}&pagesize={size}',
+
+			// Modify the url after all processing has been applied to handle filtering and sorting
+			pager_customAjaxUrl: function(table, url) {
+				var directions = ["asc", "desc"];
+				for (var s=0; s<table.config.sortList.length; s++) {
+					// 0=column number, 1=direction(0 is asc)
+					if ((table.config.sortList[s][0] < columns.length) && (table.config.sortList[s][1] < directions.length)) {
+						url += "&orderby."+(s+1)+".column="+columns[table.config.sortList[s][0]]+"&orderby."+(s+1)+".direction="+directions[table.config.sortList[s][1]]
+					}
+				}
+				for (var f=0; f<table.config.pager.currentFilters.length; f++) {
+					var filter = table.config.pager.currentFilters[f];
+					if (filter.trim()) {
+						url += "&filter."+columns[f]+"="+encodeURIComponent(filter.trim());
+					}
+				}
+				return url;
+			},
+
+			// process ajax so that the following information is returned:
+			// [ total_rows (number), rows (array of arrays), headers (array; optional) ]
+			pager_ajaxProcessing: function(data){
+				if (data && data.value && data.value.entries) {
+					rows = [];
+					for ( r=0; r<data.value.entries.value.length; r++) {
+						row=[];
+						<% if viewlibrary.check_permission("deletetableentry") or allowupdate then %>
+							var temp = actions;
+							for (var i=1; i<columns.length; i++) {
+								if (null == data.value.entries.value[r][columns[i]]) {
+									temp = temp.replace(new RegExp(columns[i]+"_VALUE", 'g'), "null").replace(new RegExp(columns[i]+"_NULL", 'g'), "true");
+								} else {
+									temp = temp.replace(new RegExp(columns[i]+"_VALUE", 'g'), data.value.entries.value[r][columns[i]]).replace(new RegExp(columns[i]+"_NULL", 'g'), "false");
+								}
+							}
+							row[0] = temp;
+
+							for (var i=1; i<columns.length; i++) {
+						<% else %>
+							for (var i=0; i<columns.length; i++) {
+						<% end %>
+								if (null == data.value.entries.value[r][columns[i]]) {
+									row[i] = "NULL";
+								} else {
+									row[i] = data.value.entries.value[r][columns[i]];
+								}
+							}
+						rows.push(row);
+					}
+					return [ parseInt(data.value.rowcount.value), rows];
+				}
+			}
+		}})
+		.bind('pagerComplete', function(e, c){
+			$(".deletetableentry").click(function(){ return confirm("Are you sure you want to delete this entry?")});
+		});
+		$("#list").tablesorter({widgets: ['zebra']});
+	});
+</script>
+
+<% htmlviewfunctions.displaycommandresults({"deletetableentry", "updatetableentry"}, session) %>
+<% htmlviewfunctions.displaycommandresults({"createtableentry"}, session, true) %>
+
+<% if form.value.table.value ~= "" then form.label = form.label.." - "..form.value.table.value end %>
+<% local header_level = htmlviewfunctions.displaysectionstart(form, page_info) %>
+<%
+local header_level2 = htmlviewfunctions.incrementheader(header_level)
+htmlviewfunctions.displayformstart(form, page_info)
+if form.value.connection and ( next(form.value.connection.value) ~= nil or form.value.connection.errtxt ) then
+	htmlviewfunctions.displayitem(form.value.connection, page_info, header_level2, "connection")
+end
+htmlviewfunctions.displayitem(form.value.table, page_info, header_level2, "table")
+form.option = "Update"
+htmlviewfunctions.displayformend(form, htmlviewfunctions.incrementheader(header_level))
+%>
+
+<table id="list" class="tablesorter"><thead>
+	<tr>
+	<% if viewlibrary.check_permission("deletetableentry") or allowupdate then %>
+		<th class="filter-false remove sorter-false">Action</th>
 	<% end %>
 	<% for i,f in ipairs(form.value.fields.value) do %>
-		<td>
-		<% if not tableentry[f] then %>
-			<i>NULL</i>
-		<% else %>
-			<%= html.html_escape(tableentry[f]) %>
-		<% end %>
-		</td>
+		<th><%= html.html_escape(f) %></th>
 	<% end %>
 	</tr>
-<% end %>
+</thead><tbody>
 </tbody></table>
+
+<div id="pager" class="pager">
+	<form>
+		Page: <select class="gotoPage"></select>
+		<img src="<%= html.html_escape(page_info.wwwprefix..page_info.staticdir) %>/tablesorter/first.png" class="first"/>
+		<img src="<%= html.html_escape(page_info.wwwprefix..page_info.staticdir) %>/tablesorter/prev.png" class="prev"/>
+		<span class="pagedisplay"></span> <!-- this can be any element, including an input -->
+		<img src="<%= html.html_escape(page_info.wwwprefix..page_info.staticdir) %>/tablesorter/next.png" class="next"/>
+		<img src="<%= html.html_escape(page_info.wwwprefix..page_info.staticdir) %>/tablesorter/last.png" class="last"/>
+		<select class="pagesize">
+			<option selected="selected" value="10">10</option>
+			<option value="20">20</option>
+			<option value="30">30</option>
+			<option value="40">40</option>
+		</select>
+	</form>
+</div>
+
 <% htmlviewfunctions.displayinfo(form) %>
 <% if #form.value.entries.value == 0 then %>
 <p>No entries found</p>
-- 
cgit v1.2.3