diff options
author | Ted Trask <ttrask01@yahoo.com> | 2015-06-25 23:45:03 -0400 |
---|---|---|
committer | Ted Trask <ttrask01@yahoo.com> | 2015-06-25 23:45:03 -0400 |
commit | 4fa07d4f009b19123ac4e331d3f90de1e16a9b7d (patch) | |
tree | 089a8d3b424b315972b9ff972c6fda668436e81f | |
parent | 59a505f2bfaaf8018f22cdffabe3bbe95f968fb8 (diff) | |
download | acf-freeswitch-vmail-4fa07d4f009b19123ac4e331d3f90de1e16a9b7d.tar.bz2 acf-freeswitch-vmail-4fa07d4f009b19123ac4e331d3f90de1e16a9b7d.tar.xz |
Add server-side sorting and filtering for listusers, making use of tablesorter in the HTML view
-rw-r--r-- | vmail-controller.lua | 2 | ||||
-rw-r--r-- | vmail-listusers-html.lsp | 107 | ||||
-rw-r--r-- | vmail-model.lua | 63 |
3 files changed, 141 insertions, 31 deletions
diff --git a/vmail-controller.lua b/vmail-controller.lua index a1837e2..a478db4 100644 --- a/vmail-controller.lua +++ b/vmail-controller.lua @@ -7,7 +7,7 @@ mymodule.listfolders = function( self ) end mymodule.listusers = function( self ) - return self.model.list_users() + return self.model.list_users(self, self.clientdata) end mymodule.deleteuser = function( self ) diff --git a/vmail-listusers-html.lsp b/vmail-listusers-html.lsp index 1972dc5..6e621e0 100644 --- a/vmail-listusers-html.lsp +++ b/vmail-listusers-html.lsp @@ -12,13 +12,77 @@ 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> <script type="text/javascript"> $(document).ready(function() { - $("#list").tablesorter({headers: {0:{sorter: false}}, widgets: ['zebra']}); - $(".deleteuser").click(function(){ return confirm("Are you sure you want to delete this user?")}); + // 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 username = cfe({ type="hidden", value="REPLACEME" }) + local redir = cfe({ type="hidden", value=page_info.orig_action }) + if viewlibrary.check_permission("editusersettings") then + htmlviewfunctions.displayitem(cfe({type="link", value={username=username, redir=redir}, label="", option="Edit", action="editusersettings"}), page_info, -1) + end + if viewlibrary.check_permission("deleteuser") then + htmlviewfunctions.displayitem(cfe({type="form", value={username=username}, label="", option="Delete", action="deleteuser", class="deleteuser" }), 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", "username", "firstname", "lastname"]; + 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].username); + row[1] = data.value.result.value[r].username; + row[2] = data.value.result.value[r].firstname; + row[3] = data.value.result.value[r].lastname; + rows.push(row); + } + return [ parseInt(data.value.rowcount.value), rows]; + } + } + }}) + .bind('pagerComplete', function(e, c){ + $(".deleteuser").click(function(){ return confirm("Are you sure you want to delete this user?")}); + }); }); </script> @@ -28,37 +92,36 @@ html = require("acf.html") <% local header_level2 = htmlviewfunctions.incrementheader(header_level) %> <table id="list" class="tablesorter"><thead> <tr> - <th>Action</th> + <th class="filter-false remove sorter-false">Action</th> <th>Extension</th> <th>First Name</th> <th>Last Name</th> </tr> </thead><tbody> -<% local username = cfe({ type="hidden", value="" }) %> -<% local redir = cfe({ type="hidden", value=page_info.orig_action }) %> -<% for k,v in ipairs( view.value ) do %> - <tr> - <td> - <% username.value = v.username %> - <% if viewlibrary.check_permission("editusersettings") then %> - <% htmlviewfunctions.displayitem(cfe({type="link", value={username=username, redir=redir}, label="", option="Edit", action="editusersettings"}), page_info, -1) %> - <% end %> - <% if viewlibrary.check_permission("deleteuser") then %> - <% htmlviewfunctions.displayitem(cfe({type="form", value={username=username}, label="", option="Delete", action="deleteuser", class="deleteuser" }), page_info, -1) %> - <% end %> - </td> - <td><%= html.html_escape(v.username) %></td> - <td><%= html.html_escape(v.firstname) %></td> - <td><%= html.html_escape(v.lastname) %></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> + <% 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 users found</p> <% end %> diff --git a/vmail-model.lua b/vmail-model.lua index 51261cf..8d7e421 100644 --- a/vmail-model.lua +++ b/vmail-model.lua @@ -734,21 +734,68 @@ mymodule.list_passwords = function(username) return cfe({ type="structure", value=users, label="Voicemail User Passwords", errtxt=errtxt }) end -mymodule.list_users = function() - local errtxt - local users = {} +mymodule.list_users = function(self, clientdata) + local retval = cfe({ type="group", value={}, label="Voicemail Users" }) + 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="username", 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={username="", firstname="", lastname=""}, label="Filter", key=true }) + self.handle_clientdata(retval, clientdata) + retval.value.result = cfe({ type="structure", value={}, label="Voicemail Users" }) + + -- 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 = {username="u.username", lastname="v1.value", firstname="v2.value"} + 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] = "username ASC" + end + local res, err = pcall(function() local connected = vmaildb.databaseconnect() - -- This crazy query gets the username from voicemail_users, the firstname and lastname from two instances of voicemail_values (using voicemail_params to determine the corresponding nid values) drops usernames starting with "tempuser" and ordering by username - local sql = "SELECT u.username, v1.value lastname, v2.value firstname FROM voicemail_users u LEFT OUTER JOIN voicemail_values v1 ON u.uid = v1.uid AND v1.nid=(SELECT nid FROM voicemail_params WHERE name='lastname') LEFT OUTER JOIN voicemail_values v2 ON u.uid = v2.uid AND v2.nid=(SELECT nid FROM voicemail_params WHERE name='firstname') WHERE u.username NOT LIKE 'tempuser%' ORDER BY u.username" - users = vmaildb.getselectresponse(sql) + + local filter = {} + for c,f in pairs(retval.value.filter.value) do + if columns[c] and f ~= "" then + filter[#filter+1] = columns[c].."~'"..vmaildb.escape(f).."'" + end + end + -- This crazy query gets the username from voicemail_users, the firstname and lastname from two instances of voicemail_values (using voicemail_params to determine the corresponding nid values) drops usernames starting with "tempuser" + local sql = " FROM voicemail_users u LEFT OUTER JOIN voicemail_values v1 ON u.uid = v1.uid AND v1.nid=(SELECT nid FROM voicemail_params WHERE name='lastname') LEFT OUTER JOIN voicemail_values v2 ON u.uid = v2.uid AND v2.nid=(SELECT nid FROM voicemail_params WHERE name='firstname') WHERE u.username NOT LIKE 'tempuser%'" + if #filter>0 then + sql = sql.." AND "..table.concat(filter, " AND ") + end + if page > 0 then + local count = vmaildb.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 = vmaildb.getselectresponse("SELECT u.username, v1.value lastname, v2.value firstname"..sql) or {} + if page <= 0 then + retval.value.rowcount.value = #retval.value.result.value + end if connected then databasedisconnect() end end) if not res and err then - errtxt = err + retval.errtxt = err end - return cfe({ type="structure", value=users, label="Voicemail Users", errtxt=errtxt }) + return retval end mymodule.get_delete_user = function(self, clientdata) |