A new admin stats graph

1 files changed, 83 insertions, 0 deletions

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
+