1 files changed, 63 insertions, 16 deletions
diff --git a/provisioning-model.lua b/provisioning-model.lua
index 0e95058..1f6c9c7 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -2116,31 +2116,78 @@ function mymodule.put_file(self, clientdata)
 	return retval
 end
 
-mymodule.list_requests = function()
-	local retval = {}
-	local errtxt
-	-- Get the templates from the DB
+mymodule.list_requests = function(self, clientdata)
+	local retval = cfe({ type="group", value={}, label="Requests" })
+	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="date", 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="Requests" })
+
+	-- 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 = {device_id="c.device_id", agent="r.agent", date="r.date", ip="r.ip", mac="r.mac"}
+	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] = "r.date DESC"
+	end
+
         local res, err = pcall(function()
 		local connected = databaseconnect()
-		local sql = "SELECT * FROM provisioning_requests ORDER BY date DESC"
-		retval = getselectresponse(sql)
-		-- Get the corresponding device_id's for each request
-		sql = "SELECT * FROM provisioning_values WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name='mac')"
-		local ids = getselectresponse(sql)
-		local reverseids = {}
-		for i,v in ipairs(ids) do
-			reverseids[v.value] = v.device_id
+		
+		local filter = {}
+		columns.data = nil -- Cannot regex filter based on date because of the timestamp type
+		for c,f in pairs(retval.value.filter.value) do
+			if columns[c] and f ~= "" then
+				if c == "device_id" then
+					if tonumber(f) then
+						filter[#filter+1] = columns[c].."='"..provdb.escape(f).."'"
+					else
+						filter[#filter+1] = columns[c].." IS NULL"
+					end
+				else
+					filter[#filter+1] = columns[c].."~'"..provdb.escape(f).."'"
+				end
+			end
 		end
-		for i,v in ipairs(retval) do
-			v.device_id = reverseids[v.mac]
+
+		local sql = " FROM (provisioning_requests r LEFT JOIN (SELECT v.device_id, v.value FROM (provisioning_values v JOIN provisioning_params p USING(param_id)) WHERE p.name='mac') c ON r.mac=c.value)"
+		if #filter>0 then
+			sql = sql.." WHERE "..table.concat(filter, " AND ")
+		end
+		if page > 0 then
+			local count = 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 = getselectresponse("SELECT r.*, c.device_id"..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="Requests", errtxt=errtxt })
+	return retval
 end
 
 mymodule.get_delete_request = function(self, clientdata)