#!/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

read OPTION_FOI_DB_HOST OPTION_FOI_DB_PORT OPTION_FOI_DB_NAME OPTION_FOI_DB_USER <<<$(ruby -r yaml -e 'db = YAML::load(STDIN.read); pr = db["production"]; puts pr["host"], pr["port"], pr["database"], pr["username"]' < config/database.yml)

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