Merge lp:~clint-fewbar/launchpad-work-items-tracker/server-team-mods into lp:launchpad-work-items-tracker

[Clint Byrum]

- reordering of statuses so inprogress appears between todo and done in html report
- only generate milestones due after today, unless told otherwise.

[lp:~clint-fewbar/launchpad-work-items-tracker/server-team-mods]

212. By Clint Byrum on 2010-08-13

generate milestones due today or later, rather than only after today

213. By Clint Byrum on 2010-08-18

only show user direct assigned items on personal page

214. By Clint Byrum on 2010-08-18

- adding blocked status
- where possible, reducing number of queries run by using group by instead
- adding index for assignee+milestone (used for several of the user mode queries)

215. By Clint Byrum on 2010-08-22

need to create indexes on db initialization as well

216. By Clint Byrum on 2010-09-03

merging with trunk

217. By Clint Byrum on 2010-09-03

fixing user page svg generation

218. By Clint Byrum on 2010-09-13

merging with trunk

219. By Clint Byrum on 2010-09-13

Fixing failure during milestone-only html-report (forgot to remove w.status clause and make args an explicit tuple)

220. By Clint Byrum on 2010-09-13

Re-showing data points accidentally hidden in burndown chart

221. By Clint Byrum on 2010-09-13

fixing broken trend lines

222. By Clint Byrum on 2010-09-13

- changing link for users to their wi tracker page on team pages
- fixing all teams burndown charts to look nicer (no more foreign/team distinction)

223. By Clint Byrum on 2010-09-14

- fixing burnup chart trend line
- adding all users in server team to burnup_chart_teams, and enabling adding users to burnup

Unmerged revisions

=== modified file 'burndown-chart'
--- burndown-chart	2010-07-29 22:11:16 +0000
+++ burndown-chart	2010-08-18 21:15:52 +0000
@@ -43,10 +43,11 @@
 
     for date in xrange(date_to_ordinal(start_date), date_to_ordinal(end_date)+1):
         i = data.get(ordinal_to_date(date), {})
-        count = i.get('done', 0) + i.get('todo', 0) + i.get('inprogress', 0) + i.get('postponed', 0)
+        count = i.get('done', 0) + i.get('todo', 0) + i.get('blocked', 0) + i.get('inprogress', 0) + i.get('postponed', 0)
         if max_items < count:
             max_items = count
         pcdata.append((date, i.get('todo_teamonly', 0), i.get('todo', 0),
+            i.get('blocked_teamonly', 0), i.get('blocked', 0),
             i.get('inprogress_teamonly', 0), i.get('inprogress', 0),
             i.get('done_teamonly', 0), i.get('done', 0),
             i.get('postponed_teamonly', 0), i.get('postponed', 0), count))
@@ -71,6 +72,9 @@
     # create the chart area
     # tell it to start at coords 0,0
     # tell it the labels, and the tics, etc..
+    # HACK: to prevent 0 div
+    if max_items == 0:
+        max_items = 1
     ar = area.T(legend=legend.T(loc=(legend_x,legend_y)), loc=(0,0),
             x_axis=axis.X(label='Date', tic_interval=x_interval,format=format_date),
             y_axis=axis.Y(label='Work Items', tic_interval=y_interval),
@@ -91,30 +95,37 @@
     plot2.fill_style = fill_style.Plain(bgcolor=color.coral1)
     plot2.line_style = None
 
-    plot3 = bar_plot.T(label='inprogress (team)', hcol=3, stack_on = plot2)
-    plot3.fill_style = fill_style.Plain(bgcolor=color.orange2)
+    plot3 = bar_plot.T(label='blocked (team)', hcol=3, stack_on = plot2)
+    plot3.fill_style = fill_style.Plain(bgcolor=color.darkred)
     plot3.line_style = None
-    plot4 = bar_plot.T(label='inprogress (foreign)', hcol=4, stack_on = plot2)
-    plot4.fill_style = fill_style.Plain(bgcolor=color.orange1)
+    plot4 = bar_plot.T(label='blocked (foreign)', hcol=4, stack_on = plot2)
+    plot4.fill_style = fill_style.Plain(bgcolor=color.indianred)
     plot4.line_style = None
 
-    plot5 = bar_plot.T(label='done (team)', hcol=5, stack_on = plot4)
-    plot5.fill_style = fill_style.green
+    plot5 = bar_plot.T(label='inprogress (team)', hcol=5, stack_on = plot4)
+    plot5.fill_style = fill_style.Plain(bgcolor=color.orange2)
     plot5.line_style = None
-    plot6 = bar_plot.T(label='done (foreign)', hcol=6, stack_on = plot4)
-    plot6.fill_style = fill_style.Plain(bgcolor=color.olivedrab1)
+    plot6 = bar_plot.T(label='inprogress (foreign)', hcol=6, stack_on = plot4)
+    plot6.fill_style = fill_style.Plain(bgcolor=color.orange1)
     plot6.line_style = None
 
-    plot7 = bar_plot.T(label="postponed (team)", hcol=7, stack_on = plot6)
-    plot7.fill_style = fill_style.Plain(bgcolor=color.yellow2)
+    plot7 = bar_plot.T(label='done (team)', hcol=7, stack_on = plot6)
+    plot7.fill_style = fill_style.green
     plot7.line_style = None
-    plot8 = bar_plot.T(label="postponed (foreign)  ", hcol=8, stack_on = plot6)
-    plot8.fill_style = fill_style.Plain(bgcolor=color.yellow1)
+    plot8 = bar_plot.T(label='done (foreign)', hcol=8, stack_on = plot6)
+    plot8.fill_style = fill_style.Plain(bgcolor=color.olivedrab1)
     plot8.line_style = None
 
-    plot9 = bar_plot.T(label='total', hcol=9)
-    plot9.fill_style = None
-    plot9.line_style = line_style.gray30
+    plot9 = bar_plot.T(label="postponed (team)", hcol=9, stack_on = plot8)
+    plot9.fill_style = fill_style.Plain(bgcolor=color.yellow2)
+    plot9.line_style = None
+    plot10 = bar_plot.T(label="postponed (foreign)  ", hcol=10, stack_on = plot8)
+    plot10.fill_style = fill_style.Plain(bgcolor=color.yellow1)
+    plot10.line_style = None
+
+    plot11 = bar_plot.T(label='total', hcol=9)
+    plot10.fill_style = None
+    plot10.line_style = line_style.gray30
 
     # create the canvas with the specified filename and file format
     can = canvas.init(filename,format)
@@ -156,6 +167,8 @@
         help='Output file', dest='output')
 optparser.add_option('--trend-start', type='int',
         help='Explicitly set start of trend line', dest='trendstart')
+optparser.add_option('-u', '--user', 
+        help='Run for this user', dest='user')
 
 (opts, args) = optparser.parse_args()
 if not opts.database:
@@ -163,6 +176,15 @@
 if not opts.output:
     optparser.error('No output file given')
 
+if opts.user and  opts.team:
+    optparser.error('team and user options are mutually exclusive')
+
+# The typing allows polymorphic behavior
+if opts.user:
+    opts.team = report_tools.user_string(opts.user)
+elif opts.team:
+    opts.team = report_tools.team_string(opts.team)
+
 # get date -> state -> count mapping
 db = report_tools.get_db(opts.database)
 data = report_tools.workitems_over_time(db, opts.team, opts.milestone)

=== modified file 'collect'
--- collect	2010-07-29 22:11:16 +0000
+++ collect	2010-08-18 21:15:52 +0000
@@ -618,7 +618,7 @@
             else:
                 status_search = fields[2:]
             for f in status_search:
-                if 'DONE' in f or 'POSTPONED' in f or 'TODO' in f or 'INPROGRESS' in f:
+                if 'DONE' in f or 'POSTPONED' in f or 'TODO' in f or 'INPROGRESS' in f or 'BLOCKED' in f:
                     ff = f.split()
                     if len(ff) == 2:
                         assignee = ff[1]
@@ -628,6 +628,9 @@
                     elif 'POSTPONED' in f:
                         istatus = 'postponed'
                         break
+                    elif 'BLOCKED' in f:
+                        istatus = 'blocked'
+                        break
                     elif 'INPROGRESS' in f and allow_inprogress:
                         istatus = 'inprogress'
                         break
@@ -807,6 +810,13 @@
             db.commit()
             ver = 5
 
+        if ver == 5:
+            dbg('Upgrading DB to layout version 6')
+            create_v6_indexes(cur)
+            cur.execute('UPDATE version SET db_layout_ref = 6')
+            db.commit()
+            ver = 6
+
     return db
 
 def create_v5_indexes(cur):
@@ -814,6 +824,9 @@
     cur.execute('''CREATE INDEX work_items_date_idx ON work_items (date)''')
     cur.execute('''CREATE INDEX work_items_status_idx ON work_items (status)''')
 
+def create_v6_indexes(cur):
+    cur.execute('''CREATE INDEX work_items_assignee_milestone_idx on work_items(assignee,milestone)''')
+
 ########################################################################
 #
 # Program operations and main

=== modified file 'generate-all'
--- generate-all	2010-07-28 16:21:04 +0000
+++ generate-all	2010-08-18 21:15:52 +0000
@@ -20,6 +20,9 @@
         dest='config', metavar='PATH')
 optparser.add_option('-o', '--output', metavar='DIRECTORY',
         help='Output directory', dest='output_dir')
+optparser.add_option('-a', '--all-milestones',
+        help='Regnerate all milestones, even those that were due before today',
+        dest='all_milestones', default=False, action='store_true')
 
 (opts, args) = optparser.parse_args()
 if not opts.database:
@@ -38,8 +41,12 @@
 
 # get milestones and teams
 cur = db.cursor()
-cur.execute('SELECT name FROM milestones')
+if opts.all_milestones:
+    cur.execute('SELECT name FROM milestones')
+else:
+    cur.execute("SELECT name FROM milestones WHERE due_date >= date('now')")
 milestones = [i[0] for i in cur]
+print 'regenerating milestones: %s' % ','.join(milestones)
 cur.execute('SELECT DISTINCT team FROM teams')
 teams = [i[0] for i in cur]
 cur.execute('SELECT DISTINCT name FROM teams')
@@ -61,17 +68,17 @@
         print basename + '.html'
         f = open(basename + '.html', 'w')
         subprocess.call([os.path.join(my_path, 'html-report'), '-d', opts.database,
-                '-t', u, '-m', m, '--chart', '%s.svg' % target], stdout=f)
+                '-u', u, '-m', m, '--chart', '%s.svg' % target], stdout=f)
         f.close()
 
         print basename + '.json'
         f = open(basename + '.json', 'w')
         subprocess.call([os.path.join(my_path, 'json-report'), '-d', opts.database,
-                '-t', u, '-m', m, '-c', opts.config], stdout=f)
+                '-u', u, '-m', m, '-c', opts.config or ''], stdout=f)
         f.close()
 
         print basename + '.svg'
-        argv = [os.path.join(my_path, 'burndown-chart'), '-d', opts.database, '-t',
+        argv = [os.path.join(my_path, 'burndown-chart'), '-d', opts.database, '-u',
             u, '-m', m, '-o', basename + '.svg']
         if u in trend_starts:
             argv += ['--trend-start', str(trend_starts[t])]

=== modified file 'html-report'
--- html-report	2010-07-29 22:11:16 +0000
+++ html-report	2010-08-18 21:15:52 +0000
@@ -27,7 +27,7 @@
 <p>(Click header to sort)</p>
 <table id="byspecification">
  <thead>
-  <tr><th>Specification</th><th>Complexity</th> <th>todo</th><th>inprogress</th><th>postponed</th><th>done</th> <th>Completion</th> <th>Priority</th> <th>Status</th></tr>
+  <tr><th>Specification</th><th>Complexity</th> <th>todo</th><th>blocked</th><th>inprogress</th><th>postponed</th><th>done</th> <th>Completion</th> <th>Priority</th> <th>Status</th></tr>
  </thead>
 '''
     data = report_tools.blueprint_completion(db, team, milestone)
@@ -35,16 +35,16 @@
     completion = []
     for (bp, i) in data.iteritems():
         completion.append((bp,
-            int(float(i['postponed']+i['done'])/(i['todo']+i['done']+i['postponed']+i['inprogress'])*100 + 0.5)))
+            int(float(i['postponed']+i['done'])/(i['todo']+i['blocked']+i['done']+i['postponed']+i['inprogress'])*100 + 0.5)))
 
     completion.sort(key=lambda k: k[1]*100+report_tools.priority_value(data[k[0]]['priority']), reverse=True)
 
     for (bp, percent) in completion:
-        print '  <tr><td>%s</td> <td>%s</td><td>%i</td><td>%i</td><td>%i</td><td>%i</td> <td>%i%%<br/>' \
+        print '  <tr><td>%s</td> <td>%s</td><td>%i</td><td>%i</td><td>%i</td><td>%i</td><td>%i</td> <td>%i%%<br/>' \
             '<span style="font-size: 70%%">%s</span></td> <td>%s</td> <td>%s</td></tr>' % (
             '<a href="%s">%s</a>' % (data[bp]['url'], escape_html(bp)),
                 data[bp]['complexity'] or '',
-                data[bp]['todo'], data[bp]['inprogress'], data[bp]['postponed'], data[bp]['done'],
+                data[bp]['todo'], data[bp]['blocked'], data[bp]['inprogress'], data[bp]['postponed'], data[bp]['done'],
                 percent,
                 data[bp]['implementation'],
                 format_priority(data[bp]['priority']),
@@ -58,24 +58,24 @@
 <p>(Click header to sort)</p>
 <table id="byassignee">
  <thead>
-  <tr><th>Assignee</th> <th>Complexity</th><th>todo</th><th>inprogress</th><th>postponed</th><th>done</th>  <th>Completion</th></tr>
+  <tr><th>Assignee</th> <th>Complexity</th><th>todo</th><th>blocked</th><th>inprogress</th><th>postponed</th><th>done</th>  <th>Completion</th></tr>
  </thead>
 '''
     data = report_tools.assignee_completion(db, team, milestone)
 
     completion = []
     for a, i in data.iteritems():
-        (todo, inprogress, postponed, done) = (len(i['todo']), len(i['inprogress']), len(i['postponed']), len(i['done']))
+        (todo, blocked, inprogress, postponed, done) = (len(i['todo']), len(i['blocked']), len(i['inprogress']), len(i['postponed']), len(i['done']))
         completion.append((a,
-            int(float(postponed+done) / (todo+inprogress+done+postponed)*100 + 0.5)))
+            int(float(postponed+done) / (todo+blocked+inprogress+done+postponed)*100 + 0.5)))
 
     completion.sort(key=lambda k: k[0], reverse=False)
 
     for (a, percent) in completion:
         a_html = escape_url(a or team or 'nobody')
         url = '%s/~%s/+specs?role=assignee' % (report_tools.blueprints_base_url, a_html)
-        print '  <tr><td><a href="%s">%s</a></td> <td>%s</td><td>%i</td><td>%i</td><td>%i</td><td>%i</td> <td>%i%%</td></tr>' % (
-                url, a_html, data[a]['complexity'] or '', len(data[a]['todo']), len(data[a]['inprogress']), len(data[a]['postponed']),
+        print '  <tr><td><a href="%s">%s</a></td> <td>%s</td><td>%i</td><td>%i</td><td>%i</td><td>%i</td><td>%i</td> <td>%i%%</td></tr>' % (
+                url, a_html, data[a]['complexity'] or '', len(data[a]['todo']), len(data[a]['blocked']), len(data[a]['inprogress']), len(data[a]['postponed']),
                 len(data[a]['done']), percent)
     print '</table>'
 
@@ -101,12 +101,14 @@
 
     for a, i in sorted(data.iteritems(), key=lambda k: k[0]):
         todo_len = len(i['todo'])
+        blocked_len = len(i['blocked'])
         postponed_len = len(i['postponed'])
         done_len = len(i['done'])
         inprogress_len = len(i['inprogress'])
         a_html = escape_url(a or team or 'nobody')
         a_url = '%s/~%s/+specs?role=assignee' % (report_tools.blueprints_base_url, a_html)
         rows = {'todo': '<td rowspan="%s">todo</td>' % todo_len,
+                'blocked': '<td rowspan="%s">blocked</td>' % blocked_len,
                 'postponed': '<td rowspan="%s">postponed</td>' % postponed_len,
                 'done': '<td rowspan="%s">done</td>' % done_len,
                 'inprogress': '<td rowspan="%s">inprogress</td>' % inprogress_len}
@@ -118,7 +120,7 @@
                 print '  <tr>',
                 if not printed_assignee:
                     print '<td rowspan="%s"><a href="%s" name="%s">%s</a></td> ' % (
-                        todo_len+postponed_len+done_len+inprogress_len, a_url, escape_url(a or team or 'nobody'),
+                        todo_len+blocked_len+postponed_len+done_len+inprogress_len, a_url, escape_url(a or team or 'nobody'),
                         a_html)
                     printed_assignee = True
                 if not printed_status:
@@ -260,10 +262,20 @@
             help='Restrict report to a particular milestone', dest='milestone')
     optparser.add_option('--chart', default='burndown.svg',
             help='(Relative) URL to burndown chart', dest='chart_url')
+    optparser.add_option('-u', '--user', 
+            help='Run for this user', dest='user')
 
     (opts, args) = optparser.parse_args()
     if not opts.database:
         optparser.error('No database given')
+    if opts.user and  opts.team:
+        optparser.error('team and user options are mutually exclusive')
+
+    # The typing allows polymorphic behavior
+    if opts.user:
+        opts.team = report_tools.user_string(opts.user)
+    elif opts.team:
+        opts.team = report_tools.team_string(opts.team)
 
     db = report_tools.get_db(opts.database)
 

=== modified file 'json-report'
--- json-report	2010-04-06 13:48:48 +0000
+++ json-report	2010-08-18 21:15:52 +0000
@@ -25,12 +25,23 @@
     optparser.add_option('-c', '--config',
         help='Path to configuration file (if given, this can read adjusted trend lines start values)',
         dest='config', metavar='PATH')
+    optparser.add_option('-u', '--user', 
+        help='Run for this user', dest='user')
 
     (opts, args) = optparser.parse_args()
     if not opts.database:
         optparser.error('No database given')
     db = report_tools.get_db(opts.database)
 
+    if opts.user and  opts.team:
+        optparser.error('team and user options are mutually exclusive')
+
+    # The typing allows polymorphic behavior
+    if opts.user:
+        opts.team = report_tools.user_string(opts.user)
+    elif opts.team:
+        opts.team = report_tools.team_string(opts.team)
+
     if opts.config:
         config = report_tools.load_config(opts.config)
     else:

=== modified file 'report_tools.py'
--- report_tools.py	2010-07-29 22:11:16 +0000
+++ report_tools.py	2010-08-18 21:15:52 +0000
@@ -6,9 +6,15 @@
 import sqlite3 as dbapi2
 from cgi import escape
 
-valid_states = ['todo', 'done', 'postponed', 'inprogress']
+valid_states = ['inprogress','blocked','todo','done', 'postponed']
 blueprints_base_url = 'https://blueprints.launchpad.net'
 
+class user_string(str):
+    pass
+
+class team_string(str):
+    pass
+
 def escape_sql(text):
     return text.replace("'", "''")
 
@@ -84,35 +90,49 @@
 
     # include both direct team assignments, as well as assignmets to
     # members of that team
-    for s in valid_states:
-        cur = db.cursor()
-        if team:
-            # WIs which are assigned to team members
-            cur.execute('SELECT date, COUNT(DISTINCT w.description) '
+    cur = db.cursor()
+    if team:
+        # WIs which are assigned to team members
+        if isinstance(team, user_string):
+            cur.execute('SELECT status, date, COUNT(DISTINCT w.description) '
+                    'FROM work_items w '
+                    'WHERE assignee = ? %s '
+                    'GROUP BY status, date' % ms_sql, [team])
+        else:
+            cur.execute('SELECT status, date, COUNT(DISTINCT w.description) '
                     'FROM work_items w LEFT JOIN teams '
                     'ON w.assignee = teams.name '
-                    'WHERE status = ? AND (team = ? OR assignee = ?) %s'
-                    'GROUP BY date' % ms_sql, (s, team, team))
-
-            for (date, num) in cur:
-                data.setdefault(date, {})[s + '_teamonly'] = num
-
-            # all WIs which belong to that team's specs
-            cur.execute('SELECT date, COUNT(DISTINCT w.description) '
+                    'WHERE (team = ? OR assignee = ?) %s'
+                    'GROUP BY status, date' % ms_sql, (team, team))
+
+        for (s, date, num) in cur:
+            data.setdefault(date, {})[s + '_teamonly'] = num
+
+        # all WIs which belong to that team's specs
+        # Only do this for teams, users only want their
+        # assigned work items
+        if isinstance(team, user_string):
+            cur.execute('SELECT w.status, date, COUNT(DISTINCT w.description) '
+                    'FROM work_items w, specs s ON w.spec = s.name '
+                    'WHERE (w.assignee = ? OR (s.assignee = ? and w.assignee is null)) %s'
+                    'GROUP BY w.status, date' % ms_sql, (team, team))
+        else:
+            cur.execute('SELECT w.status, date, COUNT(DISTINCT w.description) '
                     'FROM work_items w, specs s ON w.spec = s.name '
                     '   LEFT JOIN teams ON (s.assignee = teams.name OR w.assignee = teams.name) '
-                    'WHERE w.status = ? AND (teams.team = ? OR w.assignee = ? OR s.assignee = ?) %s'
-                    'GROUP BY date' % ms_sql, (s, team, team, team))
-
-            for (date, num) in cur:
-                data.setdefault(date, {})[s] = num
-        else:
-            # all WIs for this milestone
-            cur.execute('SELECT date, count(*) FROM work_items w '
-                    'WHERE status=?  %s GROUP BY date' % ms_sql, (s,))
-
-            for (date, num) in cur:
-                data.setdefault(date, {})[s] = num
+                    'WHERE (teams.team = ? OR w.assignee = ? OR s.assignee = ?) %s'
+                    'GROUP BY w.status, date' % ms_sql, (team, team, team))
+
+        for (s, date, num) in cur:
+            data.setdefault(date, {})[s] = num
+
+    else:
+        # all WIs for this milestone
+        cur.execute('SELECT status, date, count(*) FROM work_items w '
+                'WHERE 1=1 %s GROUP BY status, date' % ms_sql)
+
+        for (date, num) in cur:
+            data.setdefault(date, {})[s] = num
     return data
 
 def blueprint_completion(db, team=None, milestone=None):
@@ -134,39 +154,46 @@
     cur.execute('SELECT max(date) FROM work_items')
     (last_date,) = cur.fetchone()
 
-    for s in valid_states:
-        cur = db.cursor()
-        if team:
-            # include both direct team assignments, as well as assignmnets to
-            # members of that team
-            cur.execute('SELECT s.name, COUNT(DISTINCT w.description), s.status, s.priority, s.implementation, s.url, s.roadmap_notes '
+    cur = db.cursor()
+    if team:
+        # include both direct team assignments, as well as assignmnets to
+        # members of that team
+        if isinstance(team, user_string):
+            cur.execute('SELECT w.status, s.name, COUNT(DISTINCT w.description), s.status, s.priority, s.implementation, s.url, s.roadmap_notes '
+                    'FROM work_items w, specs s ON w.spec = s.name '
+                    'WHERE w.date = ? AND'
+                    '  (w.assignee = ? or (s.assignee = ? and w.assignee is null)) %s '
+                    'GROUP BY w.status,w.spec' % ms_sql,
+                    (last_date, team, team))
+        else:
+            cur.execute('SELECT w.status, s.name, COUNT(DISTINCT w.description), s.status, s.priority, s.implementation, s.url, s.roadmap_notes '
                     'FROM work_items w, specs s ON w.spec = s.name '
                     '   LEFT JOIN teams ON (s.assignee = teams.name OR w.assignee = teams.name)'
-                    'WHERE w.status = ? AND w.date = ? AND'
+                    'WHERE w.date = ? AND'
                     '  (teams.team = ? OR w.assignee = ? or s.assignee = ?) %s '
-                    'GROUP BY w.spec' % ms_sql,
-                    (s, last_date, team, team, team))
-        else:
-            cur.execute('SELECT s.name, COUNT(DISTINCT w.description), s.status, s.priority, s.implementation, s.url, s.roadmap_notes '
-                    'FROM work_items w, specs s '
-                    'ON w.spec = s.name '
-                    'WHERE w.status = ? AND w.date = ? %s '
-                    'GROUP BY w.spec' % ms_sql,
-                    (s, last_date))
+                    'GROUP BY w.status, w.spec' % ms_sql,
+                    (last_date, team, team, team))
+    else:
+        cur.execute('SELECT w.status, s.name, COUNT(DISTINCT w.description), s.status, s.priority, s.implementation, s.url, s.roadmap_notes '
+                'FROM work_items w, specs s '
+                'ON w.spec = s.name '
+                'WHERE w.status = ? AND w.date = ? %s '
+                'GROUP BY w.status, w.spec' % ms_sql,
+                (last_date))
 
-        for (bp, num, status, priority, impl, url, roadmap_notes) in cur:
-            info = data.setdefault(bp, {'todo': 0, 'done': 0, 'postponed': 0, 'inprogress': 0})
-            info[s] = num
-            info['status'] = status or ''
-            info['priority'] = priority
-            info['implementation'] = impl
-            info['url'] = url
-            info['roadmap_notes'] = roadmap_notes
-            
-            cur2 = db.cursor();
-            cur2.execute('SELECT SUM(points) FROM complexity as w WHERE spec = ? AND date = ? %s' % ms_sql, (bp, last_date))
-            for points in cur2:
-                info['complexity'] = points[0] 
+    for (s, bp, num, status, priority, impl, url, roadmap_notes) in cur:
+        info = data.setdefault(bp, {'todo': 0, 'blocked': 0, 'done': 0, 'postponed': 0, 'inprogress': 0})
+        info[s] = num
+        info['status'] = status or ''
+        info['priority'] = priority
+        info['implementation'] = impl
+        info['url'] = url
+        info['roadmap_notes'] = roadmap_notes
+        
+        cur2 = db.cursor();
+        cur2.execute('SELECT SUM(points) FROM complexity as w WHERE spec = ? AND date = ? %s' % ms_sql, (bp, last_date))
+        for points in cur2:
+            info['complexity'] = points[0] 
 
     return data
 
@@ -175,7 +202,7 @@
     '''Determine current blueprint tasks completion.
 
     Return blueprint -> info mapping, with info being a map with these
-    keys: todo, inprogress, done, postponed, status, priority, implementation, url, tasks.
+    keys: todo, blocked, inprogress, done, postponed, status, priority, implementation, url, tasks.
     '''
     default = blueprint_completion(db, team=team)
     data = {}
@@ -196,12 +223,19 @@
         if team:
             # include both direct team assignments, as well as assignmnets to
             # members of that team
-            cur.execute('SELECT DISTINCT w.spec, w.description, w.assignee, w.milestone, w.status '
-                    'FROM work_items w, specs s ON w.spec = s.name '
-                    '   LEFT JOIN teams ON (s.assignee = teams.name OR w.assignee = teams.name) '
-                    'WHERE w.status = ? AND w.date = ? AND'
-                    '  (teams.team = ? OR w.assignee = ? or s.assignee = ?) %s ' % ms_sql,
-                    (s, last_date, team, team, team))
+            if isinstance(team, user_string):
+                cur.execute('SELECT DISTINCT w.spec, w.description, w.assignee, w.milestone, w.status '
+                        'FROM work_items w, specs s ON w.spec = s.name '
+                        'WHERE w.status = ? AND w.date = ? AND'
+                        '  (w.assignee = ? or (s.assignee = ? and w.assignee is null)) %s ' % ms_sql,
+                        (s, last_date, team, team))
+            else:
+                cur.execute('SELECT DISTINCT w.spec, w.description, w.assignee, w.milestone, w.status '
+                        'FROM work_items w, specs s ON w.spec = s.name '
+                        '   LEFT JOIN teams ON (s.assignee = teams.name OR w.assignee = teams.name) '
+                        'WHERE w.status = ? AND w.date = ? AND'
+                        '  (teams.team = ? OR w.assignee = ? or s.assignee = ?) %s ' % ms_sql,
+                        (s, last_date, team, team, team))
         else:
             cur.execute('SELECT DISTINCT w.spec, w.description, w.assignee, w.milestone, w.status '
                     'FROM work_items w, specs s '
@@ -227,7 +261,7 @@
     '''Determine current by-assignee completion.
 
     Return assignee -> info mapping with info being a map with these
-    keys: todo, inprogress, done, postponed. Each of those values is a list of [blueprint,
+    keys: todo, blocked, inprogress, done, postponed. Each of those values is a list of [blueprint,
     workitem, priority, spec_url].
     '''
     data = {}
@@ -248,12 +282,19 @@
         if team:
             # include both direct team assignments, as well as assignmnets to
             # members of that team
-            cur.execute('SELECT DISTINCT w.assignee, w.spec, w.description, s.priority, s.url '
-                    'FROM work_items w, specs s ON w.spec = s.name '
-                    '   LEFT JOIN teams ON (s.assignee = teams.name OR w.assignee = teams.name) '
-                    'WHERE w.status = ? AND w.date = ? AND'
-                    '  (teams.team = ? OR w.assignee = ? or s.assignee = ?) %s ' % ms_sql,
-                    (s, last_date, team, team, team))
+            if isinstance(team, user_string):
+                cur.execute('SELECT DISTINCT w.assignee, w.spec, w.description, s.priority, s.url '
+                        'FROM work_items w, specs s ON w.spec = s.name '
+                        'WHERE w.status = ? AND w.date = ? AND'
+                        '  (w.assignee = ? or (s.assignee = ? and w.assignee is null)) %s ' % ms_sql,
+                        (s, last_date, team, team))
+            else:
+                cur.execute('SELECT DISTINCT w.assignee, w.spec, w.description, s.priority, s.url '
+                        'FROM work_items w, specs s ON w.spec = s.name '
+                        '   LEFT JOIN teams ON (s.assignee = teams.name OR w.assignee = teams.name) '
+                        'WHERE w.status = ? AND w.date = ? AND'
+                        '  (teams.team = ? OR w.assignee = ? or s.assignee = ?) %s ' % ms_sql,
+                        (s, last_date, team, team, team))
         else:
             cur.execute('SELECT DISTINCT w.assignee, w.spec, w.description, s.priority, s.url '
                     'FROM work_items w, specs s '
@@ -261,7 +302,7 @@
                     'WHERE w.status = ? AND w.date = ? %s ' % ms_sql,
                     (s, last_date))
         for (a, bp, description, priority, url) in cur:
-            info = data.setdefault(a, {'todo': [], 'done': [], 'postponed': [], 'inprogress': []})
+            info = data.setdefault(a, {'todo': [], 'blocked': [], 'done': [], 'postponed': [], 'inprogress': []})
             info[s].append([bp, description, priority, url])
 
             cur2 = db.cursor();