Add server-side sorting and filtering for listusers, making use of tablesorter in the HTML view

1 files changed, 55 insertions, 8 deletions

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)