From ec988ea69c7391292777cacdc66f670b513d99f4 Mon Sep 17 00:00:00 2001
From: Ted Trask <ttrask01@yahoo.com>
Date: Thu, 8 Oct 2009 16:40:58 +0000
Subject: Fixed bug with adhoc query, display results in a table, example
 descriptions, bumped to 0.4.7

---
 Makefile                   |  2 +-
 weblog-adhocquery-html.lsp | 63 +++++++++++++++++++++++++++++++++++-----------
 weblog-model.lua           |  2 +-
 weblog-status-html.lsp     |  6 ++---
 4 files changed, 54 insertions(+), 19 deletions(-)

diff --git a/Makefile b/Makefile
index 9b3333e..476a333 100644
--- a/Makefile
+++ b/Makefile
@@ -1,6 +1,6 @@
 APP_NAME=weblog
 PACKAGE=acf-$(APP_NAME)
-VERSION=0.4.6
+VERSION=0.4.7
 
 CRON_FILE=weblogimport
 
diff --git a/weblog-adhocquery-html.lsp b/weblog-adhocquery-html.lsp
index e1c79f2..d7aee5c 100644
--- a/weblog-adhocquery-html.lsp
+++ b/weblog-adhocquery-html.lsp
@@ -2,29 +2,61 @@
 require("viewfunctions")
 %>
 
+<style type="text/css">
+  #content table { border-collapse: collapse; width: 100%; }
+  #content table td { white-space: nowrap; padding-right:20px; border-bottom:1px solid #999; }
+  #content table tr.mark { background: #E9E9E9; }
+</style>
+
 <% if form.value.result then %>
-<H1><%= html.html_escape(form.value.result.label) %></H1>
-<DL>
-<% require("html") %>
-<%= html.cfe_unpack(form.value.result.value) %>
-<form action="/cgi-bin/acf/weblog/weblog/downloadadhocquery" method="POST">
-<input class="hidden" type="hidden"  name="query"  value="<%= html.html_escape(form.value.query.value) %>" >
-<DT>Download query result</DT><DD><input class="submit" type="submit" name="Download" value="Download"></DD>
-</FORM>
-</DL>
+	<H1><%= html.html_escape(form.value.result.label) %></H1>
+	<% if #form.value.result.value == 0 then %>
+		<p>No results, try adjusting query</p>
+	<% else %>
+		<TABLE>
+		<TR style="font-weight:bold;">                          
+		<% names = {}
+		for name,val in pairs(form.value.result.value[1]) do
+			names[#names+1] = name %>
+			<TD class="header"><%= html.html_escape(name) %></TD>
+		<% end %>
+		</TR>
+		<% for i,row in ipairs(form.value.result.value) do
+			local a,b = math.modf((i/2))
+	        	local mark = ''
+		        if (b == 0) then mark=' class="mark"' end %>
+			<TR<%= mark %>>
+			<% for j,name in ipairs(names) do %>
+				<TD><%= html.html_escape(row[name]) %></TD>
+			<% end %>
+			</TR>
+		<% end %>
+		</TABLE>
+
+		<form action="/cgi-bin/acf/weblog/weblog/downloadadhocquery" method="POST">
+		<input class="hidden" type="hidden"  name="query"  value="<%= html.html_escape(form.value.query.value) %>" >
+		<DL>
+		<DT>Download query result</DT><DD><input class="submit" type="submit" name="Download" value="Download"></DD>
+		</DL>
+		</FORM>
+	<% end %>
 <% end %>
 
 <H1><%= html.html_escape(form.label) %></H1>
-<% displayformstart(form, page_info) %>
-<% displayformitem(form.value.query, "query") %>
 This form accepts a Postgresql SELECT statement and displays the results. Examples:
 <ul>
-<li><pre>SELECT clientuserid, sum(bytes) AS total FROM weblog GROUP BY clientuserid ORDER BY total DESC</pre>
-<li><pre>SELECT extract(hour from date) AS hour, sum(numrequest) AS numrequest, sum(numblock) AS numblock FROM usagestat GROUP BY extract(hour from date) ORDER BY hour</pre>
+<li>This statement will return the total bytes transferred by each user for the entire weblog history<pre>SELECT clientuserid, sum(bytes) AS total FROM pubweblog GROUP BY clientuserid ORDER BY total DESC</pre>
+<li>This statement limits the above statement to a specific range of dates (just yesterday)<pre>SELECT clientuserid, sum(bytes) AS total FROM pubweblog WHERE logdatetime >= 'yesterday' and logdatetime < 'today' GROUP BY clientuserid ORDER BY total DESC</pre>
+<li>This statement will return the number of requests and blocks by hour over the course of the entire usage history<pre>SELECT extract(hour from date) AS hour, sum(numrequest) AS numrequest, sum(numblock) AS numblock FROM usagestat GROUP BY extract(hour from date) ORDER BY hour</pre>
 </ul>
-The available database tables and descriptions are as follows:<br>
+<% displayformstart(form, page_info) %>
+<DL>
+<% displayformitem(form.value.query, "query") %>
+</DL>
 <% displayformend(form) %>
 
+<br>
+The available database tables and descriptions are as follows:
 <H3>PubWeblog and PubBlocklog</H3>
 <DL>
 These tables contain the combined squid access log and dansguardian log for every access and blocked access respectively. The definition of the table is as follows:
@@ -41,6 +73,7 @@ These tables contain the combined squid access log and dansguardian log for ever
     shortreason text
 )
 </pre></DL>
+
 <H3>dbHistLog</H3>
 <DL>
 This table contains the database history, including such information as which log files were loaded and how many entries they contained. The definition of the table is as follows:
@@ -65,6 +98,7 @@ This table contains the list of log file sources. The definition of the table is
     enabled boolean
 )
 </pre></DL>
+
 <H3>Usagestat</H3>
 <DL>
 This table contains a historical record of pages requested and blocked by hour. The definition of the table is as follows:
@@ -76,6 +110,7 @@ This table contains a historical record of pages requested and blocked by hour.
     numblock integer
 )
 </pre></DL>
+
 <H3>Watchlist</H3>
 <DL>
 This table contains the user watch list. The definition of the table is as follows:
diff --git a/weblog-model.lua b/weblog-model.lua
index c4d5080..6cd66f5 100644
--- a/weblog-model.lua
+++ b/weblog-model.lua
@@ -1265,8 +1265,8 @@ function adhocquery(query)
 				result[#result+1] = {}
 				for name,val in pairs(row) do
 					result[#result][name] = val
-					row = cur:fetch (row, "a")
 				end
+				row = cur:fetch (row, "a")
 			end
 			-- close everything
 			cur:close()
diff --git a/weblog-status-html.lsp b/weblog-status-html.lsp
index fae98c0..b1c806b 100644
--- a/weblog-status-html.lsp
+++ b/weblog-status-html.lsp
@@ -17,15 +17,15 @@ end %>
 <% if status then %>
 <%= status.value.status.value %>
 <% else %>
-unknown
+Unknown
 <% end %>
 </DD>
 
 <DT>Weblog Database</DT><DD>
 <% if data.value then %>
-present
+Present
 <% else %>
-missing
+Missing
 <% if viewlibrary and viewlibrary.dispatch_component then
 	viewlibrary.dispatch_component("createdatabase")
 end %>
-- 
cgit v1.2.3