diff options
author | Ted Trask <ttrask01@yahoo.com> | 2015-07-06 15:43:11 -0400 |
---|---|---|
committer | Ted Trask <ttrask01@yahoo.com> | 2015-07-06 15:43:11 -0400 |
commit | 7e0641398b5e5dfc13caa28c06b9c0e75038b1a0 (patch) | |
tree | eaf63f331a0fcb8f954e1dc806201c5e489e0969 | |
parent | 8e9359a77cb89ac67db14b21dc3f44e26f32285d (diff) | |
download | acf-provisioning-7e0641398b5e5dfc13caa28c06b9c0e75038b1a0.tar.bz2 acf-provisioning-7e0641398b5e5dfc13caa28c06b9c0e75038b1a0.tar.xz |
Add server-side pagination/sorting/filtering to listrequests action using tablesorter
-rw-r--r-- | provisioning-controller.lua | 2 | ||||
-rw-r--r-- | provisioning-listrequests-html.lsp | 139 | ||||
-rw-r--r-- | provisioning-model.lua | 79 |
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) |