#!/usr/bin/env python

import psycopg
import cPickle as pickle
import string
import time

DSN="dbname=rt3"
debug = False
options_file = "./rt.pickle"

def time2string(thetime):
    return time.strftime ("%Y-%B-%d %H:%M", time.localtime(thetime))

def get_tickets(lower, upper):

    lower = abs(lower)
    upper = abs(upper)

    where_clause = """WHERE 
                          created > now() - interval '%i week' AND
                          created <= now() - interval '%i week'
                    """ % \
                   (lower, upper)
    cursor.execute("""
        SELECT count(*) FROM Tickets %s""" % where_clause)
    opened = cursor.fetchone()[0]
    if debug:
        import sys
        sys.stdout.write("Opened from -%i to -%i: " % (lower, upper))
        cursor.execute("""
               SELECT id FROM Tickets %s""" % where_clause)
        for ticket in cursor.fetchall():
            sys.stdout.write ("%i " % ticket[0])
        sys.stdout.write("\n\n")              

    # It is not obvious to see what field to use. "resolved" does not
    # work on "stalled" tickets.
    where_clause = """WHERE (status != 'new' AND status != 'open') AND
                          lastupdated > now() - interval '%i week' AND
                          lastupdated <= now() - interval '%i week'
                    """ % \
                   (lower, upper)
    cursor.execute("""
        SELECT count(*) FROM Tickets %s """ % \
                   where_clause)
    closed = cursor.fetchone()[0]
    if debug:
        import sys
        sys.stdout.write("Closed from -%i to -%i: " % (lower, upper))
        cursor.execute("""
               SELECT id FROM Tickets %s""" % where_clause)
        for ticket in cursor.fetchall():
            sys.stdout.write ("%i " % ticket[0])
        sys.stdout.write("\n\n")              


    return (opened, closed)

connection = psycopg.connect(DSN)
cursor = connection.cursor()
# Find the oldest ticket
cursor.execute("""SELECT date_part('day', now() - created) FROM Tickets
        ORDER BY created LIMIT 1""")
weeks = int(cursor.fetchone()[0]/7) + 1
print "# %s from now      Opened     Closed   " % ("week")
for slot in range(weeks):
    (opened, closed) = get_tickets(lower=-slot-1, upper=-slot)
    print "-%i %i %i" % (slot, opened, closed)
connection.close()
config = {}
config["period"] = string.capitalize("week")
config["now"] = time2string(time.time())
pickle.dump(config,open(options_file,'w'))