#!/bin/bash # problem-creation-graph # Plot graph of rate of problem creation # # Copyright (c) 2008 UK Citizens Online Democracy. All rights reserved. # Email: francis@mysociety.org. WWW: http://www.mysociety.org/ # # $Id: problem-creation-graph,v 1.6 2008-11-12 18:06:34 matthew 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 fixmystreet/commonlib/shlib/deployfns read_conf fixmystreet/conf/general.yml if [ $OPTION_BASE_URL = "http://reportemptyhomes.com" ]; then DATE="2008-10-01" else DATE="2007-02-01" fi SOURCEA=/tmp/fms-creation-rate-graph-data-$RANDOM$RANDOM SOURCEB=/tmp/fms-creation-rate-graph-data-$RANDOM$RANDOM SOURCEC=/tmp/fms-creation-rate-graph-data-$RANDOM$RANDOM SOURCED=/tmp/fms-creation-rate-graph-data-$RANDOM$RANDOM SOURCEE=/tmp/fms-creation-rate-graph-data-$RANDOM$RANDOM GPSCRIPT=/tmp/fms-creation-rate-graph-script-$RANDOM$RANDOM # where status in ('draft') function grab_data { echo "select date(created), count(*) from problem $1 group by date(created) order by date(created) ;" | psql --host $OPTION_FMS_DB_HOST --port $OPTION_FMS_DB_PORT -A -F " " $OPTION_FMS_DB_NAME $OPTION_FMS_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 "" $SOURCEA grab_data "where state not in ('unconfirmed')" $SOURCEB grab_data "where state not in ('unconfirmed', 'confirmed')" $SOURCEC grab_data "where state not in ('unconfirmed', 'confirmed', 'fixed', 'fixed - council', 'fixed - user')" $SOURCED grab_data "where state not in ('unconfirmed', 'confirmed', 'fixed', 'fixed - council', 'fixed - user', 'hidden')" $SOURCEE #state = 'unconfirmed' #or state = 'confirmed' #or state in ('fixed', 'fixed - council', 'fixed - user') #or state = 'hidden' #or state = 'partial' cat >$GPSCRIPT <>$GPSCRIPT " plot \"$SOURCEA\" using 1:2 with impulses lt 3 lw 15 title \"unconfirmed\"," if [ -s $SOURCEB ]; then echo -n >>$GPSCRIPT " \"$SOURCEB\" using 1:2 with impulses lt 4 lw 15 title \"confirmed\"," fi if [ -s $SOURCEC ]; then echo -n >>$GPSCRIPT " \"$SOURCEC\" using 1:2 with impulses lt 5 lw 15 title \"fixed\"," fi if [ -s $SOURCED ]; then echo -n >>$GPSCRIPT " \"$SOURCED\" using 1:2 with impulses lt 6 lw 15 title \"hidden\"," fi if [ -s $SOURCEE ]; then echo -n >>$GPSCRIPT " \"$SOURCEE\" using 1:2 with impulses lt 7 lw 15 title \"any other type\"," fi cat >>$GPSCRIPT < fixmystreet/web/fms-live-creation$EXTENSION 2>/dev/null