#!/bin/sh
#
# Plugin to monitor the average number of hours worked by in sysadmin role.
#
# Parameters:
# 	
# 	config
# 	autoconf
#
# Configuration variables
#
#	mysqlopts    - Options to pass to mysql which should include the
#  			username, password, host and timetracker database.
#
# Example of the entry in plugin-conf.d/munin-node
#	[timetracker*]
#	user root
#	env.mysqlopts --defaults-file=/etc/mysql/alternc.cnf
#	env.mysqldb time
#
#
#%# family=auto
#%# capabilities=autoconf

MYSQLOPTS="$mysqlopts"
MYSQLDB="$mysqldb"
MYSQL=${mysql:-mysql}

if [ "$1" = "autoconf" ]; then
        $MYSQL --version 2>/dev/null >/dev/null
        if [ $? -eq 0 ]
        then
		$MYSQL  $MYSQLOPTS -N -B -e " select CONCAT(project, '.label'), p.name from timeentry as t, project as p where t.project = p.id AND start > (NOW() -  interval 28 day) AND start <= NOW() AND project IN ($PROJECTS) group by project;" $MYSQLDB 2>/dev/null
                if [ $? -eq 0 ]
                then
                        echo yes
                        exit 0
                else
                        echo "no (could not connect to mysql)"
                fi
        else
                echo "no (mysql client not found)"
        fi
        exit 1
fi

if [ "$1" = "config" ]; then
	echo 'graph_title Timetracker - average hours worked per week (sysadmins)'
	echo 'graph_vlabel hours'
	echo 'graph_category timetracker'
	echo 'graph_args --logarithmic'

	echo 'infra.label Infrastructures'
	echo 'hag.label HAG'
	echo 'colo.label Colocation/dedicated/vservers'
	echo 'bureau.label Office'

	echo 'all.label Total'

	exit 0
fi

$MYSQL  $MYSQLOPTS -N -B -e " select 'infra.value', format(sum(delta) / 60 / 60 / 4, 2) as timeinmins from timeentry as t where start > (NOW() -  interval 28 day) AND start <= NOW() AND project = 35 AND comment like '%infra%';" $MYSQLDB 2>/dev/null
$MYSQL  $MYSQLOPTS -N -B -e " select 'hag.value', format(sum(delta) / 60 / 60 / 4, 2) as timeinmins from timeentry as t where start > (NOW() -  interval 28 day) AND start <= NOW() AND project = 35 AND comment like '%hag%';" $MYSQLDB 2>/dev/null
$MYSQL  $MYSQLOPTS -N -B -e " select 'colo.value', format(sum(delta) / 60 / 60 / 4, 2) as timeinmins from timeentry as t where start > (NOW() -  interval 28 day) AND start <= NOW() AND project = 35 AND comment like '%colo%';" $MYSQLDB 2>/dev/null
$MYSQL  $MYSQLOPTS -N -B -e " select 'bureau.value', format(sum(delta) / 60 / 60 / 4, 2) as timeinmins from timeentry as t where start > (NOW() -  interval 28 day) AND start <= NOW() AND project = 35 AND comment like '%bureau%';" $MYSQLDB 2>/dev/null

$MYSQL  $MYSQLOPTS -N -B -e " select 'all.value', format(sum(delta) / 60 / 60 / 4, 2) as timeinmins from timeentry as t where start > (NOW() -  interval 28 day) AND start <= NOW() AND project = 35;" $MYSQLDB 2>/dev/null