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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
#!/bin/bash
# request-creation-graph
# Plot graph of rate of request creation
#
# Copyright (c) 2008 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 1600,600"
EXTENSION=".png"
LW=4
#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
SOURCED=/tmp/foi-creation-rate-graph-data-$RANDOM$RANDOM
SOURCEE=/tmp/foi-creation-rate-graph-data-$RANDOM$RANDOM
SOURCEF=/tmp/foi-creation-rate-graph-data-$RANDOM$RANDOM
SOURCEG=/tmp/foi-creation-rate-graph-data-$RANDOM$RANDOM
SOURCEH=/tmp/foi-creation-rate-graph-data-$RANDOM$RANDOM
SOURCEI=/tmp/foi-creation-rate-graph-data-$RANDOM$RANDOM
SOURCEJ=/tmp/foi-creation-rate-graph-data-$RANDOM$RANDOM
SOURCEK=/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 "select
date(created_at), count(*)
from info_requests
$1
and prominence != 'backpage'
group by date(created_at)
order by date(created_at)
;" | 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 "where (1 = 1)" $SOURCEA
grab_data "where described_state not in ('waiting_response')" $SOURCEB
grab_data "where described_state not in ('waiting_response', 'waiting_clarification')" $SOURCEC
grab_data "where described_state not in ('waiting_response', 'waiting_clarification', 'not_held')" $SOURCED
grab_data "where described_state not in ('waiting_response', 'waiting_clarification', 'not_held', 'rejected')" $SOURCEE
grab_data "where described_state not in ('waiting_response', 'waiting_clarification', 'not_held', 'rejected', 'successful')" $SOURCEF
grab_data "where described_state not in ('waiting_response', 'waiting_clarification', 'not_held', 'rejected', 'successful', 'partially_successful')" $SOURCEG
grab_data "where described_state not in ('waiting_response', 'waiting_clarification', 'not_held', 'rejected', 'successful', 'partially_successful', 'requires_admin')" $SOURCEH
grab_data "where described_state not in ('waiting_response', 'waiting_clarification', 'not_held', 'rejected', 'successful', 'partially_successful', 'requires_admin', 'gone_postal')" $SOURCEI
grab_data "where described_state not in ('waiting_response', 'waiting_clarification', 'not_held', 'rejected', 'successful', 'partially_successful', 'requires_admin', 'gone_postal', 'internal_review')" $SOURCEJ
grab_data "where described_state not in ('waiting_response', 'waiting_clarification', 'not_held', 'rejected', 'successful', 'partially_successful', 'requires_admin', 'gone_postal', 'internal_review', 'error_message')" $SOURCEK
#user_withdrawn
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 xlabel "status of requests that were created on each calendar day"
set ytics nomirror
set ylabel "number of requests created on the calendar day"
set y2tics tc lt 2
set y2label "cumulative total number of requests" 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 "$SOURCEA" using 1:2 with impulses lt 8 lw $LW title "waiting_response", \
"$SOURCEB" using 1:2 with impulses lt 3 lw $LW title "waiting_clarification", \
"$SOURCEC" using 1:2 with impulses lt 9 lw $LW title "not_held", \
"$SOURCED" using 1:2 with impulses lt 6 lw $LW title "rejected", \
"$SOURCEE" using 1:2 with impulses lt 2 lw $LW title "successful", \
"$SOURCEF" using 1:2 with impulses lt 10 lw $LW title "partially_successful", \
"$SOURCEG" using 1:2 with impulses lt 5 lw $LW title "requires_admin", \
"$SOURCEH" using 1:2 with impulses lt 7 lw $LW title "gone_postal", \
"$SOURCEI" using 1:2 with impulses lt 4 lw $LW title "internal_review", \
"$SOURCEJ" using 1:2 with impulses lt 12 lw $LW title "error_message", \
"$SOURCEK" using 1:2 with impulses lt 13 lw $LW title "user_withdrawn", \
"< awk 'BEGIN { n = 0 } { n += \$2; print \$1, \$2, n; }' $SOURCEA" using 1:3 axes x1y2 with lines lt 2 title "cumulative total number of requests"
END
# lt 8 dark blue
# lt 3 light blue
# lt 9 yellow
# lt 6 red
# lt 2 light green
# lt 10 dark green
# lt 5 cyan
# lt 7 dark yellow
# lt 4 mauve
# lt 12 dark reddy brown
# lt 13 pink
# unused:
# lt 11 bluey mauve
# lt 14 lime green
#echo "gpscript $GPSCRIPT"
export GDFONTPATH=/usr/share/fonts/truetype/ttf-bitstream-vera
gnuplot < $GPSCRIPT > public/foi-live-creation$EXTENSION
|
