From 4fa07d4f009b19123ac4e331d3f90de1e16a9b7d Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Thu, 25 Jun 2015 23:45:03 -0400 Subject: Add server-side sorting and filtering for listusers, making use of tablesorter in the HTML view --- vmail-controller.lua | 2 +- vmail-listusers-html.lsp | 107 +++++++++++++++++++++++++++++++++++++---------- 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") @@ -28,37 +92,36 @@ html = require("acf.html") <% local header_level2 = htmlviewfunctions.incrementheader(header_level) %> - + -<% local username = cfe({ type="hidden", value="" }) %> -<% local redir = cfe({ type="hidden", value=page_info.orig_action }) %> -<% for k,v in ipairs( view.value ) do %> - - - - - - -<% end %>
ActionAction Extension First Name Last Name
- <% 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 %> - <%= html.html_escape(v.username) %><%= html.html_escape(v.firstname) %><%= html.html_escape(v.lastname) %>
+
+
+ Page: + + + + + + +
+
+ <% if view.errtxt then %>

<%= html.html_escape(view.errtxt) %>

<% end %> -<% if #view.value == 0 then %> +<% if #view.value.result.value == 0 then %>

No users found

<% 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) -- cgit v1.2.3