#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
# zeiterfassung
# -------------
# (c) 2008 by Sascha L. Teichmann <sascha.teichmann@intevation.de>
#
# Simple script which generates lines for zeiterfassung.txt files.
#
# This is Free Software licensed under the terms of GPLv3 or later.
# For details see LICENSE coming with the source of 'getan'.
#
import os
import os.path
import sys
import getopt
import re
import codecs

from pysqlite2 import dbapi2 as db

from datetime import date

DEFAULT_DATABASE = "time.db"

TYPE_OF_ENTRY = "-"

WORKPACKAGE = re.compile("^\[(\w*)(\s|\])")

USAGE = '''usage: %s <options>
    with <options>
    [--user=|-u <user>]        : Name of user,          default: $USER
    [--database=|-d <database>]: getan database,        default: time.db
    [--project=|-p <key>]      : Key of output project, default: all
    [--encoding=|-e encoding]  : encoding of output,    default: none
    [--week=]|-w <week>]       : week of year
    [--list|-l]                : list all projects
    [--help|-h]                : This text'''

LIST_PROJECTS = '''
SELECT key, description, active FROM projects
'''

PROJECT_ID_BY_KEY = '''
SELECT id, description FROM projects where key = :key
'''

ALL_PROJECT_IDS = '''
SELECT id, key, description FROM projects
'''

WEEK_ENTRIES = '''
SELECT 
    date(start_time) AS t, 
    sum(strftime('%s', stop_time) - strftime('%s', start_time)), 
    'no description' AS description
FROM entries 
WHERE 
    project_id = :project_id AND 
    (description IS NULL or length(description) = 0) -- trim() function is missing
    AND (strftime('%W', start_time) = :week OR strftime('%W', stop_time) = :week)
GROUP BY round(julianday(start_time))
UNION
SELECT date(start_time) AS s, strftime('%s', stop_time) - strftime('%s', start_time), description
FROM entries 
WHERE 
    project_id = :project_id AND 
    description IS NOT NULL AND length(description) > 0
    AND (strftime('%W', start_time) = :week OR strftime('%W', stop_time) = :week)
ORDER BY t
'''

ENTRIES = '''
SELECT 
    date(start_time), 
    sum(strftime('%s', stop_time) - strftime('%s', start_time)), 
    'no description' AS description
FROM entries 
WHERE 
    project_id = :project_id AND 
    (description IS NULL or length(description) = 0) -- trim() function is missing
GROUP BY round(julianday(start_time))
UNION
SELECT date(start_time), strftime('%s', stop_time) - strftime('%s', start_time), description
FROM entries 
WHERE 
    project_id = :project_id AND 
    description IS NOT NULL AND length(description) > 0
'''

def human_time(s):
    h = s / 3600
    m = (s % 3600) / 60
    if (s % 60) >= 30:
        m += 1
        if m == 60:
            m = 0
            h += 1
    return "%2d:%02d" % (h, m)

class TermError(Exception):

    def __init__(self, msg):
        Exception.__init__(self)
        self.msg = msg

    def __str__(self):
        return repr(self.msg)

def tolerantClose(c):
    if c:
        try: c.close()
        except: pass

def usage(exit_code = 0):
    print USAGE % sys.argv[0]
    sys.exit(exit_code)

def main():

    database      = DEFAULT_DATABASE
    user          = None
    list_projects = False
    project       = None
    encoding      = None
    week          = None

    opts, args = getopt.getopt(
        sys.argv[1:],
        'd:u:p:e:hl:w:',
        ['database=', 'user=', 'project=', 'encoding=', 'help', 'list', 'week='])

    for opt, val in opts:
        if opt in ("--database", "-d"):
            database = val
        elif opt in ("--user", "-u"):
            user = val
        elif opt in ("--project", "-p"):
            project = val
        elif opt in ("--encoding", "-e"):
            encoding = val
        elif opt in ("--help", "-h"):
            usage()
        elif opt in ("--list", "-l"):
            list_projects = True
        elif opt in ("--week", "-w"):
            week = val

    if not user:
        user = os.getenv("USER")

    if encoding:
        Writer = codecs.getwriter(encoding)
        sys.stdout = Writer(sys.stdout)

    if not os.path.isfile(database):
        print >> sys.stderr, "'%s' does not exist or is not a file." % database
        sys.exit(1)

    con, cur = None, None
    try:
        try:
            con = db.connect(database)
            if encoding:
                con.text_factory = lambda s: s.decode(encoding)
            cur = con.cursor()

            if list_projects:
                cur.execute(LIST_PROJECTS)
                while True:
                    row = cur.fetchone()
                    if not row: break
                    print "%s %s %s" % (row[0], row[2] and "*" or "-", row[1])
            else:
                if project:
                    cur.execute(PROJECT_ID_BY_KEY, { 'key': project })
                    row = cur.fetchone()
                    if row is None:
                        raise TermError("There is no project with key '%s'" % project)
                    project_ids = [[row[0], project, row[1]]]
                else:
                    cur.execute(ALL_PROJECT_IDS);
                    project_ids = cur.fetchall()

                for project_id, project, proj_desc in project_ids:
                    print "# project: %s (%s)" % (project, proj_desc)
                    if not week is None:
                        cur.execute(WEEK_ENTRIES, {'project_id': project_id, 'week': week})
                    else:
                        cur.execute(ENTRIES, {'project_id': project_id})
                    total = 0
                    while True:
                        row = cur.fetchone()
                        if not row: break
                        d = date(*map(int, row[0].split('-'))) 
                        t = max(60, row[1])
                        c = row[2]
                        total += t
                        workpackage = "----"
                        if c:
                            m = WORKPACKAGE.match(c)
                            if m:
                                workpackage = m.group(1)
                                c = c[m.end():].strip()
                        print "%s %sh %s %-3s [%s] %s" % (
                            d.strftime("%d.%m.%Y"),
                            human_time(t),
                            TYPE_OF_ENTRY,
                            user,
                            workpackage,
                            c)
                    print "# total: %sh\n\n" % human_time(total)
        finally:
            tolerantClose(cur)
            tolerantClose(con)

    except TermError, e:
        print >> sys.stderr, "error: %s" % e.msg

if __name__ == '__main__':
    main()

# vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8: