A new admin stats graph

4 files changed, 92 insertions, 1 deletions

diff --git a/app/views/admin_general/stats.rhtml b/app/views/admin_general/stats.rhtml
index 9aca960d1..dd72b13be 100644
--- a/app/views/admin_general/stats.rhtml
+++ b/app/views/admin_general/stats.rhtml
@@ -16,6 +16,12 @@
 <% end %>
 </table>
 
+<h2>Chart of users</h2>
+
+<p>
+    <img src="<%= main_url("/foi-user-use.png")%>">
+</p>
+
 <h2>Tracks by type</h2>
 
 <table>
diff --git a/config/crontab.ugly b/config/crontab.ugly
index 7d68a2b07..f4aa09194 100644
--- a/config/crontab.ugly
+++ b/config/crontab.ugly
@@ -4,7 +4,7 @@
 # Copyright (c) 2008 UK Citizens Online Democracy. All rights reserved.
 # Email: francis@mysociety.org. WWW: http://www.mysociety.org/
 #
-# $Id: crontab.ugly,v 1.21 2009-02-09 09:51:53 francis Exp $
+# $Id: crontab.ugly,v 1.22 2009-02-12 20:01:30 root Exp $
 
 PATH=/usr/local/bin:/usr/bin:/bin
 MAILTO=team@whatdotheyknow.com
@@ -29,4 +29,5 @@ MAILTO=team@whatdotheyknow.com
 
 # Once a day on all servers
 43 2 * * * !!(*= $user *)!! /data/vhost/!!(*= $vhost *)!!/mysociety/foi/script/request-creation-graph
+48 2 * * * !!(*= $user *)!! /data/vhost/!!(*= $vhost *)!!/mysociety/foi/script/user-use-graph
 
diff --git a/public/.cvsignore b/public/.cvsignore
index c3b0558db..f1e08352c 100644
--- a/public/.cvsignore
+++ b/public/.cvsignore
@@ -1,3 +1,4 @@
 down.html
 foi-live-creation.png
+foi-user-use.png
 request
diff --git a/script/user-use-graph b/script/user-use-graph
new file mode 100755
index 000000000..9f511c55b
--- /dev/null
+++ b/script/user-use-graph
@@ -0,0 +1,83 @@
+#!/bin/bash
+# user-use-graph
+# Plot graph of user use of site.
+#
+# Copyright (c) 2009 UK Citizens Online Democracy. All rights reserved.
+# Email: francis@mysociety.org. WWW: http://www.mysociety.org/
+# 
+# $Id: user-use-graph,v 1.1 2009-02-12 20:01:31 root Exp $
+
+GPLOT_OUTPUT="set terminal png font 'Vera.ttf' 9 size 1200,400"
+EXTENSION=".png"
+#GPLOT_OUTPUT="set terminal fig color big thickness 1"
+#EXTENSION=".fig"
+#GPLOT_OUTPUT="set terminal svg size 800 250"
+#EXTENSION=".svg"
+
+cd `dirname $0`
+cd ../../
+source shlib/deployfns
+
+# XXX this is nasty :)
+OPTION_FOI_DB_HOST=`grep "host:" foi/config/database.yml | head --lines=1 | cut -d ":" -f 2`
+OPTION_FOI_DB_PORT=`grep "port:" foi/config/database.yml | head --lines=1 | cut -d ":" -f 2`
+OPTION_FOI_DB_NAME=`grep "database:" foi/config/database.yml | head --lines=1 | cut -d ":" -f 2`
+OPTION_FOI_DB_USER=`grep "username:" foi/config/database.yml | head --lines=1 | cut -d ":" -f 2`
+
+SOURCEA=/tmp/foi-creation-rate-graph-data-$RANDOM$RANDOM
+SOURCEB=/tmp/foi-creation-rate-graph-data-$RANDOM$RANDOM
+SOURCEC=/tmp/foi-creation-rate-graph-data-$RANDOM$RANDOM
+GPSCRIPT=/tmp/foi-creation-rate-graph-script-$RANDOM$RANDOM
+
+#    where status in ('draft') 
+
+function grab_data {
+    echo "$1;" | psql --host $OPTION_FOI_DB_HOST --port $OPTION_FOI_DB_PORT -A -F " " $OPTION_FOI_DB_NAME $OPTION_FOI_DB_USER | egrep -v "date|rows" >$2
+}
+
+# rather nastily, work out the cumulative heights in reverse, so can plot impulses on top of each other
+grab_data "select date(created_at), count (distinct user_id) from info_requests group by date(created_at) order by date(created_at)" $SOURCEA
+grab_data "select date(created_at), count(*) from users group by date(created_at) order by date(created_at)" $SOURCEB
+grab_data "select date(created_at), count(*) from users where email_confirmed = 't' group by date(created_at) order by date(created_at)" $SOURCEC
+
+#state = 'unconfirmed'
+#or state = 'confirmed'
+#or state = 'fixed'
+#or state = 'hidden'
+#or state = 'flickr'
+
+
+cat >$GPSCRIPT <<END 
+    unset border
+    unset arrow
+    set key left
+    set tics out
+    $GPLOT_OUTPUT
+
+    set xdata time
+    set timefmt "%Y-%m-%d"
+    set xrange ["2007-12-01":]
+    set format x "%d %b %Y"
+    set xtics nomirror
+
+    set ytics nomirror 
+    set ylabel "number of users on the calendar day" 
+    set y2tics tc lt 2
+    set y2label "cumulative total number of users" tc lt 2
+    set format y2 "%.0f"
+
+#    set arrow 1 from '2005-02-14', 0 to '2005-02-14', 900 lt 0 nohead
+#    set label 1 'launch of beta' at '2005-02-17', 900
+
+    n = 0
+    plot \
+    "$SOURCEB" using 1:2 with impulses lw 15 lt 3 title "users each day ... who registered",\
+    "$SOURCEC" using 1:2 with impulses lw 15 lt 4 title "... and since confirmed their email",\
+    "$SOURCEA" using 1:2 with lines lt 6 title "... who made an FOI request",\
+    "< awk 'BEGIN { n = 0 } { n += \$2; print \$1, \$2, n; }' $SOURCEB" using 1:3 axes x1y2 with lines lt 2 title "cumulative total number of users"
+END
+#echo "gpscript $GPSCRIPT"
+
+export GDFONTPATH=/usr/share/fonts/truetype/ttf-bitstream-vera
+gnuplot < $GPSCRIPT > foi/public/foi-user-use$EXTENSION
+