Added viewactivitylog functionality (no actual logs yet)

6 files changed, 199 insertions, 3 deletions

diff --git a/provisioning-controller.lua b/provisioning-controller.lua
index e488998..81a8941 100644
--- a/provisioning-controller.lua
+++ b/provisioning-controller.lua
@@ -174,4 +174,8 @@ mymodule.bulkdumprawdevices = function( self )
 	return self.handle_form(self, self.model.get_bulk_dump_request, self.model.bulk_dump_raw_devices, self.clientdata, "Dump", "Bulk Dump Devices")
 end
 
+mymodule.viewactivitylog = function( self )
+	return self.model.getactivitylog(self, self.clientdata)
+end
+
 return mymodule
diff --git a/provisioning-model.lua b/provisioning-model.lua
index 47f9017..c46e058 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -35,6 +35,26 @@ local functions
 -- ################################################################################
 -- LOCAL FUNCTIONS
 
+local logme = function(message)
+	local userid = "-"
+	if mymodule.sessiondata and mymodule.sessiondata.userinfo and mymodule.sessiondata.userinfo.userid then
+		userid = tostring(mymodule.sessiondata.userinfo.userid)
+	end
+	if provdb.isconnected() then
+		local sql = string.format("INSERT INTO dbhistlog VALUES ('now', '%s', '%s')", provdb.escape(message), provdb.escape(userid))
+		provdb.runsqlcommand(sql, true)
+	end
+end
+
+-- Delete history log information from more than a month ago
+local groomdbhistlog = function()
+	if provdb.isconnected() then
+		local sql = "DELETE FROM dbhistlog WHERE logdatetime < (now() - INTERVAL '1 month')"
+		provdb.runsqlcommand(sql, true)
+		logme("removed " .. res .. " old dbhistlog lines")
+	end
+end
+
 local function logcall(fn)
 	if mymodule.sessiondata and mymodule.sessiondata.userinfo and mymodule.sessiondata.userinfo.userid then
 		mymodule.logevent("acf-provisioning: "..tostring(fn).." by "..tostring(mymodule.sessiondata.userinfo.userid))
@@ -2686,4 +2706,73 @@ mymodule.bulk_dump_raw_devices = function(self, dumprequest)
 	return dump_devices(self, dumprequest, false)
 end
 
+mymodule.getactivitylog = function(self, clientdata)
+	local connected
+	local retval = cfe({ type="group", value={}, label="Provisioning Activity Log" })
+	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="logdatetime", direction="desc"}}, 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="Log Entries" })
+
+	-- 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 = {logdatetime="logdatetime", msgtext="msgtext", userid="userid"}
+	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] = "logdatetime DESC"
+	end
+
+	local res, err = pcall(function()
+		connected = databaseconnect(true)
+
+		local filter = {}
+		columns.logdatetime = nil -- Cannot regex filter based on logdatetime because of the timestamp type
+		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
+
+		local sql = " FROM dbhistlog"
+		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 *"..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
+		handlesqlexception(connected, err)
+		retval.errtxt = err
+	end
+
+	return retval
+end
+
 return mymodule
diff --git a/provisioning-scripts.lua b/provisioning-scripts.lua
index 83be198..65cd36b 100644
--- a/provisioning-scripts.lua
+++ b/provisioning-scripts.lua
@@ -1493,7 +1493,13 @@ mymodule.provisioning_options = {
 
 -- List of getfile requests
 mymodule.provisioning_requests = {
-	"CREATE TABLE provisioning_requests (mac VARCHAR(12) unique, ip VARCHAR(15), agent VARCHAR(255), date timestamp)",
+	"CREATE TABLE provisioning_requests (mac VARCHAR(12) UNIQUE, ip VARCHAR(15), agent VARCHAR(255), date timestamp)",
+}
+
+-- History
+mymodule.dbhistlog = {
+	"CREATE TABLE dbhistlog (logdatetime timestamp(3) without time zone NOT NULL, msgtext text, userid text)",
+	"CREATE INDEX dbhistlogdatetimeidx ON dbhistlog USING btree (logdatetime)",
 }
 
 return mymodule
diff --git a/provisioning-viewactivitylog-html.lsp b/provisioning-viewactivitylog-html.lsp
new file mode 100644
index 0000000..d1e7228
--- /dev/null
+++ b/provisioning-viewactivitylog-html.lsp
@@ -0,0 +1,96 @@
+<% local view, viewlibrary, page_info, session = ...
+htmlviewfunctions = require("htmlviewfunctions")
+html = require("acf.html")
+%>
+
+<script type="text/javascript">
+	if (typeof jQuery == 'undefined') {
+		document.write('<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/jquery-latest.js"><\/script>');
+	}
+</script>
+
+<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:'digit'}}, 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 = ["logdatetime", "msgtext", "userid"];
+				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] = '<span class="hide">'+Date.UTC(data.value.result.value[r].logdatetime)+'</span>'+data.value.result.value[r].logdatetime;
+						row[1] = data.value.result.value[r].msgtext;
+						row[2] = data.value.result.value[r].userid;
+						rows.push(row);
+					}
+					return [ parseInt(data.value.rowcount.value), rows];
+				}
+			}
+		}});
+        });
+</script>
+
+<% local header_level = htmlviewfunctions.displaysectionstart(view, page_info) %>
+<table id="list" class="tablesorter"><thead>
+	<tr>
+		<th class="filter-false">Timestamp</th>
+		<th>Message</th>
+		<th>User ID</th>
+	</tr>
+</thead><tbody>
+</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>
+
+<% htmlviewfunctions.displaysectionend(header_level) %>
diff --git a/provisioning.menu b/provisioning.menu
index 2a42626..ef3bd96 100644
--- a/provisioning.menu
+++ b/provisioning.menu
@@ -14,3 +14,4 @@ Applications	89Provisioning		Param_Groups	listgroups
 Applications	89Provisioning		Params		listparams
 Applications	89Provisioning		Script_Files	listfiles
 Applications	89Provisioning		Database	dumpdatabase
+Applications 	89Provisioning		History		viewactivitylog
diff --git a/provisioning.roles b/provisioning.roles
index ef59e81..999d62c 100644
--- a/provisioning.roles
+++ b/provisioning.roles
@@ -1,4 +1,4 @@
-USER=provisioning:searchdevices,provisioning:listdevices,provisioning:viewdeviceparams,provisioning:searchbyextension,provisioning:searchbymac,provisioning:listrequests,provisioning:bulkdumpdevices,provisioning:bulk
+USER=provisioning:searchdevices,provisioning:listdevices,provisioning:viewdeviceparams,provisioning:searchbyextension,provisioning:searchbymac,provisioning:listrequests,provisioning:bulkdumpdevices,provisioning:bulk,provisioning:viewactivitylog
 EDITOR=provisioning:editdevice,provisioning:createdevice,provisioning:duplicatedevice,provisioning:deletedevice,provisioning:editdeviceparams,provisioning:deleterequest,provisioning:createdevicefromrequest,provisioning:bulkcreatedevices
 EXPERT=provisioning:listtemplates,provisioning:edittemplate,provisioning:createtemplate,provisioning:deletetemplate,provisioning:listclassgroups,provisioning:editclassgroup,provisioning:createclassgroup,provisioning:deleteclassgroup,provisioning:listclasses,provisioning:editclass,provisioning:createclass,provisioning:deleteclass,provisioning:listgroups,provisioning:editgroup,provisioning:creategroup,provisioning:deletegroup,provisioning:listparams,provisioning:editparam,provisioning:createparam,provisioning:deleteparam,provisioning:overridedeviceparams,provisioning:editoptions,provisioning:listfiles,provisioning:editfile,provisioning:getdevicevalues,provisioning:bulkdumprawdevices
-ADMIN=provisioning:listtemplates,provisioning:edittemplate,provisioning:createtemplate,provisioning:deletetemplate,provisioning:searchdevices,provisioning:listdevices,provisioning:editdevice,provisioning:createdevice,provisioning:duplicatedevice,provisioning:deletedevice,provisioning:listclassgroups,provisioning:editclassgroup,provisioning:createclassgroup,provisioning:deleteclassgroup,provisioning:listclasses,provisioning:editclass,provisioning:createclass,provisioning:deleteclass,provisioning:listgroups,provisioning:editgroup,provisioning:creategroup,provisioning:deletegroup,provisioning:listparams,provisioning:editparam,provisioning:createparam,provisioning:deleteparam,provisioning:editdeviceparams,provisioning:overridedeviceparams,provisioning:viewdeviceparams,provisioning:editoptions,provisioning:listfiles,provisioning:editfile,provisioning:searchbyextension,provisioning:searchbymac,provisioning:getdevicevalues,provisioning:dumpdatabase,provisioning:getclassvalues,provisioning:listrequests,provisioning:deleterequest,provisioning:createdevicefromrequest,provisioning:bulkcreatedevices,provisioning:bulkdumprawdevices,provisioning:bulk
+ADMIN=provisioning:listtemplates,provisioning:edittemplate,provisioning:createtemplate,provisioning:deletetemplate,provisioning:searchdevices,provisioning:listdevices,provisioning:editdevice,provisioning:createdevice,provisioning:duplicatedevice,provisioning:deletedevice,provisioning:listclassgroups,provisioning:editclassgroup,provisioning:createclassgroup,provisioning:deleteclassgroup,provisioning:listclasses,provisioning:editclass,provisioning:createclass,provisioning:deleteclass,provisioning:listgroups,provisioning:editgroup,provisioning:creategroup,provisioning:deletegroup,provisioning:listparams,provisioning:editparam,provisioning:createparam,provisioning:deleteparam,provisioning:editdeviceparams,provisioning:overridedeviceparams,provisioning:viewdeviceparams,provisioning:editoptions,provisioning:listfiles,provisioning:editfile,provisioning:searchbyextension,provisioning:searchbymac,provisioning:getdevicevalues,provisioning:dumpdatabase,provisioning:getclassvalues,provisioning:listrequests,provisioning:deleterequest,provisioning:createdevicefromrequest,provisioning:bulkcreatedevices,provisioning:bulkdumprawdevices,provisioning:bulk,provisioning:viewactivitylog