Add server-side pagination/sorting/filtering to listrequests action using tablesorter

3 files changed, 167 insertions, 53 deletions

diff --git a/provisioning-controller.lua b/provisioning-controller.lua
index c8a3455..3b9ef12 100644
--- a/provisioning-controller.lua
+++ b/provisioning-controller.lua
@@ -147,7 +147,7 @@ mymodule.putfile = function( self )
 end
 
 mymodule.listrequests = function( self )
-	return self.model.list_requests()
+	return self.model.list_requests(self, self.clientdata)
 end
 
 mymodule.deleterequest = function( self )
diff --git a/provisioning-listrequests-html.lsp b/provisioning-listrequests-html.lsp
index 365f2db..9ddd3c0 100644
--- a/provisioning-listrequests-html.lsp
+++ b/provisioning-listrequests-html.lsp
@@ -12,6 +12,9 @@ html = require("acf.html")
 <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>
 
@@ -41,8 +44,85 @@ html = require("acf.html")
 %>
 
         $(document).ready(function() {
-		$("#list").tablesorter({headers: {1:{sorter:'digit'}, 3:{sorter:'ipAddress'}}, widgets: ['zebra']});
-		$(".deleterequest").click(function(){ return confirm("Are you sure you want to delete this request?")});
+		// The following is a hack to include a multiline string
+		var MultiString = function(f) {
+			return f.toString().split('\n').slice(1, -1).join('\n');
+		}
+		<% local mac = cfe({ type="hidden", value="REPLACEME" }) %>
+		var actions = MultiString(function() {/**
+		<%
+			if viewlibrary.check_permission("deleterequest") then
+				htmlviewfunctions.displayitem(cfe({type="form", value={mac=mac}, label="", option="Delete", action="deleterequest", class="deleterequest" }), page_info, -1)
+			end
+		%>
+		**/});
+		var foundactions = MultiString(function() {/**
+		<%
+			if viewlibrary.check_permission("searchdevices") then
+				htmlviewfunctions.displayitem(cfe({type="form", value={id=cfe({type="hidden", value="device_id"}), value=cfe({type="hidden", value="REPLACEME"})}, label="", option="Search", action="searchdevices" }), page_info, -1)
+			end
+		%>
+		**/});
+		var missingactions = MultiString(function() {/**
+		<%
+			if viewlibrary.check_permission("createdevicefromrequest") then
+				htmlviewfunctions.displayitem(cfe({type="form", value={mac=mac}, label="", option="Create", action="createdevicefromrequest" }), page_info, -1)
+			end
+		%>
+		**/});
+
+                $("#list").tablesorter({headers: {1:{sorter:'digit'}, 3:{sorter:'ipAddress'}}, 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 .. page_info.orig_action) %>?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 columns = ["action", "date", "mac", "ip", "agent", "device_id"];
+				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.result) {
+					rows = [];
+					for ( r=0; r<data.value.result.value.length; r++) {
+						row=[];
+						if (data.value.result.value[r].device_id != null) {
+							row[0] = actions.replace(/REPLACEME/g, data.value.result.value[r].mac) + foundactions.replace(/REPLACEME/g, data.value.result.value[r].device_id);
+						} else {
+							row[0] = actions.replace(/REPLACEME/g, data.value.result.value[r].mac) + missingactions.replace(/REPLACEME/g, data.value.result.value[r].mac);
+						}
+						row[1] = '<span class="hide">'+Date.UTC(data.value.result.value[r].date)+'</span>'+data.value.result.value[r].date;
+						row[2] = data.value.result.value[r].mac;
+						row[3] = data.value.result.value[r].ip;
+						row[4] = data.value.result.value[r].agent;
+						row[5] = data.value.result.value[r].device_id;
+						rows.push(row);
+					}
+					return [ parseInt(data.value.rowcount.value), rows];
+				}
+			}
+		}})
+		.bind('pagerComplete', function(e, c){
+			$(".deleterequest").click(function(){ return confirm("Are you sure you want to delete this request?")});
+		});
         });
 </script>
 
@@ -51,45 +131,32 @@ html = require("acf.html")
 <% local header_level = htmlviewfunctions.displaysectionstart(cfe({label="Requests"}), page_info) %>
 <table id="list" class="tablesorter"><thead>
 	<tr>
-		<th>Action</th>
-		<th>Timestamp</th>
+		<th class="filter-false remove sorter-false">Action</th>
+		<th class="filter-false">Timestamp</th>
 		<th>MAC Address</th>
 		<th>IP Address</th>
 		<th>User Agent</th>
+		<th>Device ID</th>
 	</tr>		
 </thead><tbody>
-<% local mac = cfe({ type="hidden", value="" }) %>
-<% for k,v in ipairs( view.value ) do %>
-	<tr>
-		<td>
-		<% mac.value = v.mac %>
-		<% if viewlibrary.check_permission("deleterequest") then %>
-			<% htmlviewfunctions.displayitem(cfe({type="form", value={mac=mac}, label="", option="Delete", action="deleterequest", class="deleterequest" }), page_info, -1) %>
-		<% end %>
-		<% if v.device_id then %>
-			<% if viewlibrary.check_permission("searchdevices") then %>
-				<% htmlviewfunctions.displayitem(cfe({type="form", value={id=cfe({type="hidden", value="device_id"}), value=cfe({type="hidden", value=v.device_id})}, label="", option="Search", action="searchdevices" }), page_info, -1) %>
-			<% end %>
-		<% else %>
-			<% if viewlibrary.check_permission("createdevicefromrequest") then %>
-				<% htmlviewfunctions.displayitem(cfe({type="form", value={mac=mac}, label="", option="Create", action="createdevicefromrequest" }), page_info, -1) %>
-			<% end %>
-		<% end %>
-		</td>
-		<td>
-		<span class="hide">
-		<% local date = {}
-		date.year, date.month, date.day, date.hour, date.min, date.sec, remainder = string.match(v.date, "(%d+)%-(%d+)%-(%d+) (%d+):(%d+):(%d+)")
-		local rem = string.match(v.date, "%.%d+$")
-		io.write((os.time(date) - 1318000000)..(rem or "")) %>
-		</span>
-		<%= html.html_escape(v.date) %>
-		</td>
-		<td><%= html.html_escape(v.mac) %></td>
-		<td><%= html.html_escape(v.ip) %></td>
-		<td><%= html.html_escape(v.agent) %></td>
-	</tr>
-<% end %>
 </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.displaysectionend(header_level) %>
diff --git a/provisioning-model.lua b/provisioning-model.lua
index 0e95058..1f6c9c7 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -2116,31 +2116,78 @@ function mymodule.put_file(self, clientdata)
 	return retval
 end
 
-mymodule.list_requests = function()
-	local retval = {}
-	local errtxt
-	-- Get the templates from the DB
+mymodule.list_requests = function(self, clientdata)
+	local retval = cfe({ type="group", value={}, label="Requests" })
+	retval.value.page = cfe({ value=0, label="Page Number", descr="0 indicates ALL", key=true })
+	retval.value.pagesize = cfe({ value=10, label="Page Size", key=true })
+	retval.value.rowcount = cfe({ value=0, label="Row Count" })
+	-- orderby must be an array of tables with column name and direction
+	retval.value.orderby = cfe({ type="structure", value={{column="date", direction="asc"}}, label="Order By", key=true })
+	-- filter is a table with a string filter for each column
+	retval.value.filter = cfe({ type="structure", value={}, label="Filter", key=true })
+	self.handle_clientdata(retval, clientdata)
+	retval.value.result = cfe({ type="structure", value={}, label="Requests" })
+
+	-- Process the incoming page data
+	local page = tonumber(retval.value.page.value) or 0
+	retval.value.page.value = page
+	local pagesize = tonumber(retval.value.pagesize.value) or 10
+	retval.value.pagesize.value = pagesize
+	local orderby = {}
+	local columns = {device_id="c.device_id", agent="r.agent", date="r.date", ip="r.ip", mac="r.mac"}
+	local directions = {asc="ASC", desc="DESC", ASC="ASC", DESC="DESC"}
+	for i,o in ipairs(retval.value.orderby.value) do
+		if columns[o.column] and directions[o.direction] then
+			orderby[#orderby+1] = columns[o.column].." "..directions[o.direction]
+		end
+	end
+	if #orderby == 0 then
+		orderby[#orderby+1] = "r.date DESC"
+	end
+
         local res, err = pcall(function()
 		local connected = databaseconnect()
-		local sql = "SELECT * FROM provisioning_requests ORDER BY date DESC"
-		retval = getselectresponse(sql)
-		-- Get the corresponding device_id's for each request
-		sql = "SELECT * FROM provisioning_values WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name='mac')"
-		local ids = getselectresponse(sql)
-		local reverseids = {}
-		for i,v in ipairs(ids) do
-			reverseids[v.value] = v.device_id
+		
+		local filter = {}
+		columns.data = nil -- Cannot regex filter based on date because of the timestamp type
+		for c,f in pairs(retval.value.filter.value) do
+			if columns[c] and f ~= "" then
+				if c == "device_id" then
+					if tonumber(f) then
+						filter[#filter+1] = columns[c].."='"..provdb.escape(f).."'"
+					else
+						filter[#filter+1] = columns[c].." IS NULL"
+					end
+				else
+					filter[#filter+1] = columns[c].."~'"..provdb.escape(f).."'"
+				end
+			end
 		end
-		for i,v in ipairs(retval) do
-			v.device_id = reverseids[v.mac]
+
+		local sql = " FROM (provisioning_requests r LEFT JOIN (SELECT v.device_id, v.value FROM (provisioning_values v JOIN provisioning_params p USING(param_id)) WHERE p.name='mac') c ON r.mac=c.value)"
+		if #filter>0 then
+			sql = sql.." WHERE "..table.concat(filter, " AND ")
+		end
+		if page > 0 then
+			local count = getselectresponse("SELECT count(*)"..sql)
+			retval.value.rowcount.value = count[1].count
 		end
+		sql = sql.." ORDER BY "..table.concat(orderby, ", ")
+		if page > 0 then
+			sql = sql.." LIMIT "..pagesize.." OFFSET "..(page - 1)*pagesize
+		end
+		retval.value.result.value = getselectresponse("SELECT r.*, c.device_id"..sql) or {}
+		if page <= 0 then
+			retval.value.rowcount.value = #retval.value.result.value
+		end
+
 		if connected then provdb.databasedisconnect() end
 	end)
 	if not res and err then
-		errtxt = err
+		retval.errtxt = err
 	end
 
-	return cfe({ type="structure", value=retval, label="Requests", errtxt=errtxt })
+	return retval
 end
 
 mymodule.get_delete_request = function(self, clientdata)