Add server-side pagination, sorting, and filtering for listdevices action

1 files changed, 76 insertions, 13 deletions

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)