1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
#!/bin/bash
# user-use-graph
# Plot graph of user use of site.
#
# Copyright (c) 2009 UK Citizens Online Democracy. All rights reserved.
# Email: hello@mysociety.org. WWW: http://www.mysociety.org/
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 commonlib/shlib/deployfns
# TODO: this is nasty :)
OPTION_FOI_DB_HOST=`grep "host:" config/database.yml | head --lines=1 | cut -d ":" -f 2`
OPTION_FOI_DB_PORT=`grep "port:" config/database.yml | head --lines=1 | cut -d ":" -f 2`
OPTION_FOI_DB_NAME=`grep "database:" config/database.yml | head --lines=1 | cut -d ":" -f 2`
OPTION_FOI_DB_USER=`grep "username:" 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 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 > public/foi-user-use$EXTENSION
|
