diff options
author | Ted Trask <ttrask01@yahoo.com> | 2015-07-05 20:40:14 -0400 |
---|---|---|
committer | Ted Trask <ttrask01@yahoo.com> | 2015-07-05 20:40:14 -0400 |
commit | df9f7a1dd10b1c3b4e17739222b882cdcf2b17f8 (patch) | |
tree | ef460fc3350b61e0084e15200892461121264146 | |
parent | 116b9b0f4c06074740ae99e91107c42c5ba56242 (diff) | |
download | acf-provisioning-df9f7a1dd10b1c3b4e17739222b882cdcf2b17f8.tar.bz2 acf-provisioning-df9f7a1dd10b1c3b4e17739222b882cdcf2b17f8.tar.xz |
Add server-side pagination, sorting, and filtering for listdevices action
-rw-r--r-- | provisioning-controller.lua | 2 | ||||
-rw-r--r-- | provisioning-listdevices-html.lsp | 153 | ||||
-rw-r--r-- | provisioning-model.lua | 89 |
3 files changed, 181 insertions, 63 deletions
diff --git a/provisioning-controller.lua b/provisioning-controller.lua index 02f03d1..c8a3455 100644 --- a/provisioning-controller.lua +++ b/provisioning-controller.lua @@ -87,7 +87,7 @@ mymodule.deleteparam = function( self ) end mymodule.listdevices = function( self ) - return self.model.list_devices() + return self.model.list_devices(self, self.clientdata) end mymodule.editdevice = function( self ) diff --git a/provisioning-listdevices-html.lsp b/provisioning-listdevices-html.lsp index c511d1e..79c2b32 100644 --- a/provisioning-listdevices-html.lsp +++ b/provisioning-listdevices-html.lsp @@ -12,9 +12,19 @@ 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> +<% -- Determine all of the groups +local columns = {"device_id"} +for i,v in ipairs( view.value.groups.value ) do + columns[#columns+1] = v.name +end +%> + <script type="text/javascript"> <% -- Since we're including createdevice as a component, we break the automatic redirect if session.createdeviceresult and not session.createdeviceresult.errtxt then @@ -28,74 +38,119 @@ html = require("acf.html") %> $(document).ready(function() { - $("#list").tablesorter({headers: {0:{sorter: false}}, widgets: ['zebra']}); - $(".deletedevice").click(function(){ return confirm("Are you sure you want to delete this device?")}); + // 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() {/** + <% + local device_id = cfe({ type="hidden", value="REPLACEME" }) + local redir = cfe({ type="hidden", value=page_info.orig_action }) + if viewlibrary.check_permission("editdevice") then + htmlviewfunctions.displayitem(cfe({type="link", value={device_id=device_id, redir=redir}, label="", option="Edit", action="editdevice"}), page_info, -1) + end + if viewlibrary.check_permission("overridedeviceparams") then + htmlviewfunctions.displayitem(cfe({type="link", value={device_id=device_id, redir=redir}, label="", option="Params", action="overridedeviceparams"}), page_info, -1) + elseif viewlibrary.check_permission("editdeviceparams") then + htmlviewfunctions.displayitem(cfe({type="link", value={device_id=device_id, redir=redir}, label="", option="Params", action="editdeviceparams"}), page_info, -1) + end + if viewlibrary.check_permission("getdevicevalues") then + htmlviewfunctions.displayitem(cfe({type="form", value={value=device_id, id=cfe({type="hidden", value="device_id"}), viewtype=cfe({type="hidden", value="templated"})}, label="", option="View", action="getdevicevalues"}), page_info, -1) + end + if viewlibrary.check_permission("deletedevice") then + htmlviewfunctions.displayitem(cfe({type="form", value={device_id=device_id}, label="", option="Delete", action="deletedevice", class="deletedevice"}), page_info, -1) + end + %> + **/}); + + $("#list").tablesorter({headers: {0:{sorter: false}}, 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", "<%= table.concat(columns, '", "') %>"]; + 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=[]; + row[0] = actions.replace(/REPLACEME/g, data.value.result.value[r].device_id); + <% + for c,n in ipairs(columns) do + print("row["..c.."] = data.value.result.value[r]['"..html.html_escape(n).."'];") + end + %> + rows.push(row); + } + return [ parseInt(data.value.rowcount.value), rows]; + } + } + }}) + .bind('pagerComplete', function(e, c){ + $(".deletedevice").click(function(){ return confirm("Are you sure you want to delete this device?")}); + }); }); </script> <% htmlviewfunctions.displaycommandresults({"deletedevice", "editdevice", "editdeviceparams", "overridedeviceparams"}, session) %> <% htmlviewfunctions.displaycommandresults({"createdevice"}, session, true) %> -<% -- Determine all of the groups -local tmp = {} -for k,v in ipairs( view.value ) do - for g,c in pairs(v) do - if not tmp[g] then tmp[g] = true end - end -end -local display = {} -for n in pairs(tmp) do - if n ~= "device_id" then - display[#display+1] = n - end -end -table.sort(display) -%> - <% local header_level = htmlviewfunctions.displaysectionstart(view, page_info) %> <table id="list" class="tablesorter"><thead> <tr> - <th>Action</th> + <th class="filter-false remove sorter-false">Action</th> <th>Device ID</th> - <% for i,n in ipairs(display) do %> - <th><%= string.gsub(n, "^.", string.upper) %> + <% for i,g in ipairs(view.value.groups.value) do %> + <th><%= html.html_escape(g.label) %></th> <% end %> </tr> </thead><tbody> -<% local device_id = cfe({ type="hidden", value="" }) %> -<% local redir = cfe({ type="hidden", value=page_info.orig_action }) %> -<% for k,v in ipairs( view.value ) do %> - <tr> - <td> - <% device_id.value = v.device_id %> - <% if viewlibrary.check_permission("editdevice") then %> - <% htmlviewfunctions.displayitem(cfe({type="link", value={device_id=device_id, redir=redir}, label="", option="Edit", action="editdevice"}), page_info, -1) %> - <% end %> - <% if viewlibrary.check_permission("overridedeviceparams") then %> - <% htmlviewfunctions.displayitem(cfe({type="link", value={device_id=device_id, redir=redir}, label="", option="Params", action="overridedeviceparams"}), page_info, -1) %> - <% elseif viewlibrary.check_permission("editdeviceparams") then %> - <% htmlviewfunctions.displayitem(cfe({type="link", value={device_id=device_id, redir=redir}, label="", option="Params", action="editdeviceparams"}), page_info, -1) %> - <% end %> - <% if viewlibrary.check_permission("getdevicevalues") then %> - <% htmlviewfunctions.displayitem(cfe({type="form", value={value=device_id, id=cfe({type="hidden", value="device_id"}), viewtype=cfe({type="hidden", value="templated"})}, label="", option="View", action="getdevicevalues"}), page_info, -1) %> - <% end %> - <% if viewlibrary.check_permission("deletedevice") then %> - <% htmlviewfunctions.displayitem(cfe({type="form", value={device_id=device_id}, label="", option="Delete", action="deletedevice", class="deletedevice"}), page_info, -1) %> - <% end %> - </td> - <td><%= html.html_escape(v.device_id) %></td> - <% for i,n in ipairs(display) do %> - <td><%= html.html_escape(v[n]) %></td> - <% end %> - </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> + <% if view.errtxt then %> <p class="error"><%= html.html_escape(view.errtxt) %></p> <% end %> -<% if #view.value == 0 then %> +<% if #view.value.result.value == 0 then %> <p>No devices found</p> <% end %> <% htmlviewfunctions.displaysectionend(header_level) %> diff --git a/provisioning-model.lua b/provisioning-model.lua index f4fb1a3..0e95058 100644 --- a/provisioning-model.lua +++ b/provisioning-model.lua @@ -1456,29 +1456,92 @@ mymodule.delete_param = function(self, delreq) return delreq end -mymodule.list_devices = function() - local retval = {} - local errtxt +mymodule.list_devices = function(self, clientdata) + local retval = cfe({ type="group", value={}, label="Provisioning Devices" }) + 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="device_id", 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="Provisioning Devices" }) + retval.value.groups = cfe({ type="structure", value={}, label="Provisioning Class Groups" }) + + -- 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 + -- Get the devices from the DB local res, err = pcall(function() local connected = databaseconnect() - local sql = "SELECT device_id, c.label AS class, g.label AS group FROM devices_to_classes d2c JOIN provisioning_classes c USING(class_id) JOIN provisioning_class_groups g USING(class_group_id) ORDER BY device_id" - local tmp = getselectresponse(sql) - local reverse_device_id = {} - for i,d in ipairs(tmp) do - if not reverse_device_id[d.device_id] then - retval[#retval+1] = {device_id=d.device_id} - reverse_device_id[d.device_id] = #retval + + -- First, get the columns / class_groups + local sql = "SELECT * from provisioning_class_groups ORDER BY seq ASC" + retval.value.groups.value = getselectresponse(sql) + + -- Set up the dynamic tables for order/filter and the sql statement for each column / class_group + local columns = {device_id="device_id"} + local selects = {"device_id"} + local joins = {"devices_to_classes"} + for i,g in ipairs(retval.value.groups.value) do + columns[g.name] = provdb.escape(g.name)..".label" + selects[#selects+1] = provdb.escape(g.name)..".label AS "..provdb.escape(g.name) + joins[#joins+1] = "(SELECT device_id, c.label FROM devices_to_classes d2c JOIN provisioning_classes c USING(class_id) WHERE c.class_group_id='"..g.class_group_id.."') "..provdb.escape(g.name).." USING(device_id)" + end + + -- Handle the sorting + local orderby = {} + 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 - retval[reverse_device_id[d.device_id]][d.group] = d.class end + if #orderby == 0 then + orderby[#orderby+1] = "device_id ASC" + end + + -- Handle the filtering + local filter = {} + for c,f in pairs(retval.value.filter.value) do + if columns[c] and f ~= "" then + filter[#filter+1] = columns[c].."~'"..provdb.escape(f).."'" + end + end + + -- Set up the joins and filtering first + sql = " FROM "..table.concat(joins, " LEFT JOIN ") + if #filter>0 then + sql = sql.." WHERE "..table.concat(filter, " AND ") + end + -- If we're paginating, get the full count here + if page > 0 then + local count = getselectresponse("SELECT count(DISTINCT device_id)"..sql) + retval.value.rowcount.value = count[1].count + end + -- Then we can finish the sql with the selects and sorting + sql = "SELECT DISTINCT "..table.concat(selects, ", ")..sql + 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(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="Devices", errtxt=errtxt }) + return retval end mymodule.get_existing_device = function(self, clientdata) |