getan: contrib/wochenbericht annotate

annotate contrib/wochenbericht @ 191:64624032611d

Add function to convert from iso to unix week For evaluation we use the iso week as week parameter which is commonly known in europe. But SQLite internally uses unix weeks which may differ. Therefore convert the iso week to the unix week.

author	Björn Ricks <bjoern.ricks@intevation.de>
date	Thu, 17 Jan 2013 11:41:36 +0100
parents	4de86feae6ac
children

rev	line source
4 2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	1 #!/bin/bash
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	2 #
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	3 # wochenbericht
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	4 # -------------
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	5 # (c) 2008 by Sascha L. Teichmann
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	6
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	7 # Little script to summarize times within a given week.
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	8 # usage:
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	9 # ./wochenbericht [<week of year>] [<getan database file>]
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	10 # week defaults to current week, database to time.db
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	11 #
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	12 # This is Free Software in terms of GPLv3 or later. See
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	13 # LICENSE coming with getan for details.
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	14 #
5 e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	15 usage() {
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	16 cat <<EOF
32 4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	17 usage: ./wochenbericht [<week of year>] [<year>] [<getan database file>]
5 e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	18 <week of year> defaults to current week
32 4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	19 <year> defaults to current year
5 e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	20 <getan database file> defaults to time.db
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	21 EOF
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	22 exit 1
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	23 }
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	24
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	25 if [ "$1" == "--help" -o "$1" == "-h" ]; then
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	26 usage
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	27 fi
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	28
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	29
4 2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	30 if [[ "$1" -eq "" ]]; then
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	31 WEEK=`date +'%W'`
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	32 # remove hash below if you want previous week
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	33 #WEEK=`expr ${WEEK} '-' 1 '\|' 52`
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	34 else
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	35 WEEK=$1
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	36 fi
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	37
32 4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	38 if [[ "$2" -eq "" ]]; then
4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	39 YEAR=`date +'%Y'`
4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	40 else
4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	41 YEAR=$2
4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	42 fi
4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	43
4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	44 TIME_DB=${3:-time.db}
4 2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	45
5 e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	46 if [ ! -f ${TIME_DB} ]; then
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	47 echo "error: Database file ${TIME_DB} does not exist."
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	48 usage
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	49 fi
e7245c2127e5 Applied patch from Stephan Holl to show usage on '-h' of '--help'. Added check for existence of db file. Sascha L. Teichmann <teichmann@intevation.de> parents: 4 diff changeset	50
4 2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	51 sqlite3 ${TIME_DB} "
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	52 SELECT coalesce(description, 'Verschiedenes'), total FROM projects
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	53 INNER JOIN (
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	54 SELECT
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	55 project_id,
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	56 sum(strftime('%s', stop_time) - strftime('%s', start_time)) AS total
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	57 FROM entries
32 4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	58 WHERE (strftime('%W', start_time) = '${WEEK}' AND
4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	59 strftime('%Y', start_time) = '${YEAR}') OR
4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	60 (strftime('%W', stop_time) = '${WEEK}' AND
4de86feae6ac Added a parameter 'year' to the 'wochenbericht' script that filters out the week of a specific year only. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 5 diff changeset	61 strftime('%Y', stop_time) = '${YEAR}')
4 2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	62 GROUP BY project_id
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	63 ) ON id = project_id
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	64 WHERE active;
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	65 " \| awk '
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	66 function human_time(t) {
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	67 h = int(t / 3600)
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	68 m = int((t % 3600)/60.0 + 0.5)
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	69 while (m >= 60) { ++h; m -= 60 }
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	70 return sprintf("%2d:%02dh", h, m)
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	71 }
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	72 BEGIN { FS="\|"; sum = 0 }
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	73 { sum += $2
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	74 printf("%s: %s\n", human_time($2), $1)
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	75 }
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	76 END { printf("%s: Gesamt\n", human_time(sum)) }
2b2f74f301db Added little script to summarize the entries for a given week number of year. Sascha L. Teichmann <teichmann@intevation.de> parents: diff changeset	77 '

Mercurial > getan

annotate contrib/wochenbericht @ 191:64624032611d