| 1 |
# Copyright (C) 2006 Sam Bloomquist <spooninator@hotmail.com> |
|---|
| 2 |
# All rights reserved. |
|---|
| 3 |
# |
|---|
| 4 |
# This software may at some point consist of voluntary contributions made by |
|---|
| 5 |
# many individuals. For the exact contribution history, see the revision |
|---|
| 6 |
# history and logs, available at http://projects.edgewall.com/trac/. |
|---|
| 7 |
# |
|---|
| 8 |
# Author: Sam Bloomquist <spooninator@hotmail.com> |
|---|
| 9 |
|
|---|
| 10 |
import time |
|---|
| 11 |
import sys |
|---|
| 12 |
|
|---|
| 13 |
from trac.core import * |
|---|
| 14 |
from trac.util import format_date |
|---|
| 15 |
from trac.env import open_environment |
|---|
| 16 |
|
|---|
| 17 |
def main(): |
|---|
| 18 |
if len(sys.argv) != 2: |
|---|
| 19 |
print >> sys.stderr, 'Must supply a trac_env as an argument to the burndown_job' |
|---|
| 20 |
sys.exit(1) |
|---|
| 21 |
|
|---|
| 22 |
env_path = sys.argv[1] |
|---|
| 23 |
|
|---|
| 24 |
# today's date |
|---|
| 25 |
today = format_date(int(time.time())) |
|---|
| 26 |
|
|---|
| 27 |
# open up a connection to the trac database |
|---|
| 28 |
env = open_environment(env_path) |
|---|
| 29 |
db = env.get_db_cnx() |
|---|
| 30 |
cursor = db.cursor() |
|---|
| 31 |
|
|---|
| 32 |
# make sure that there isn't already an entry for today in the burndown table |
|---|
| 33 |
cursor.execute("SELECT id FROM burndown WHERE date = '%s'" % today) |
|---|
| 34 |
row = cursor.fetchone() |
|---|
| 35 |
needs_update = False |
|---|
| 36 |
if row: |
|---|
| 37 |
print >> sys.stderr, 'burndown_job.py has already been run today - update needed' |
|---|
| 38 |
needs_update = True |
|---|
| 39 |
else: |
|---|
| 40 |
print >> sys.stderr, 'first run of burndown_job.py today - insert needed' |
|---|
| 41 |
|
|---|
| 42 |
# get arrays of the various components and milestones in the trac environment |
|---|
| 43 |
cursor.execute("SELECT name AS comp FROM component") |
|---|
| 44 |
components = cursor.fetchall() |
|---|
| 45 |
cursor.execute("SELECT name, started, completed FROM milestone") |
|---|
| 46 |
milestones = cursor.fetchall() |
|---|
| 47 |
|
|---|
| 48 |
for mile in milestones: |
|---|
| 49 |
if mile[1] and not mile[2]: # milestone started, but not completed |
|---|
| 50 |
for comp in components: |
|---|
| 51 |
sqlSelect = "SELECT est.value AS estimate, ts.value AS spent "\ |
|---|
| 52 |
"FROM ticket t "\ |
|---|
| 53 |
" LEFT OUTER JOIN ticket_custom est ON (t.id = est.ticket AND est.name = 'estimatedhours') "\ |
|---|
| 54 |
" LEFT OUTER JOIN ticket_custom ts ON (t.id = ts.ticket AND ts.name = 'totalhours') "\ |
|---|
| 55 |
"WHERE t.component = '%s' AND t.milestone = '%s' " |
|---|
| 56 |
cursor.execute(sqlSelect % (comp[0], mile[0])) |
|---|
| 57 |
|
|---|
| 58 |
rows = cursor.fetchall() |
|---|
| 59 |
hours = 0 |
|---|
| 60 |
estimate = 0 |
|---|
| 61 |
spent = 0 |
|---|
| 62 |
if rows: |
|---|
| 63 |
for estimate, spent in rows: |
|---|
| 64 |
if not estimate: |
|---|
| 65 |
estimate = 0 |
|---|
| 66 |
if not spent: |
|---|
| 67 |
spent = 0 |
|---|
| 68 |
|
|---|
| 69 |
hours += float(estimate) - float(spent) |
|---|
| 70 |
|
|---|
| 71 |
if needs_update: |
|---|
| 72 |
cursor.execute("UPDATE burndown SET hours_remaining = '%f' WHERE date = '%s' AND milestone_name = '%s'"\ |
|---|
| 73 |
"AND component_name = '%s'" % (hours, today, mile[0], comp[0])) |
|---|
| 74 |
else: |
|---|
| 75 |
cursor.execute("INSERT INTO burndown(id,component_name, milestone_name, date, hours_remaining) "\ |
|---|
| 76 |
" VALUES(NULL,'%s','%s','%s',%f)" % (comp[0], mile[0], today, hours)) |
|---|
| 77 |
|
|---|
| 78 |
db.commit() |
|---|
| 79 |
|
|---|
| 80 |
if __name__ == '__main__': |
|---|
| 81 |
main() |
|---|
