~mabac/launchpad-work-items-tracker/fix-none-whiteboard

#!/usr/bin/python
#
# Tools for generating reports

import datetime
import urllib, sys, os.path
from storm.locals import create_database, Store
from subprocess import Popen
from cgi import escape
import errno
import fcntl
from lpworkitems.models_roadmap import (
    Lane,
    Card,
)
from lpworkitems.models import (
    Meta, ROADMAP_STATUSES_MAP,
    get_roadmap_status_for_bp_implementation_status,    
)

valid_states = [u'inprogress', u'blocked', u'todo', u'done', u'postponed']
state_labels = [u'In Progress', u'Blocked', u'Todo', u'Done', u'Postponed']
states_and_labels = zip(valid_states, state_labels)
blueprints_base_url = 'https://blueprints.launchpad.net'

DEFAULT_THEME = "linaro"

class user_string(str):
    pass

class team_string(str):
    pass

def escape_sql(text):
    return text.replace("'", "''")


def get_theme(cfg):
    return cfg.get("theme", DEFAULT_THEME)


def report_args(args, team=None, user=None, milestone=None, date=None,
                theme=None):
    if milestone:
        args.extend(['-m',milestone])
    if team:
        args.extend(['-t',team])
    if user:
        args.extend(['-u',user])
    if date:
        args.extend(['--date', date])
    if theme:
        args.extend(['--theme', theme])


def status_overview(my_path, database, basename, config, root=None):
    cfg = load_config(config)
    team = cfg.get("primary_team", None)
    if team:
        chartname = team
    else:
        chartname = 'all'
    fh = open(basename + '.html', 'w')
    try:
        args = [os.path.join(my_path, 'html-report'), '-d', database, '--chart', '%s.svg' % chartname]
        if 'title' in cfg:
            args += ['--title', cfg['title']]
        args += ['--report-type', 'overview']
        if root:
            args += ['--root', root]
        report_args(args, team, theme=get_theme(cfg))
        proc = Popen(args, stdout=fh)
        print basename + ".html"
        proc.wait()
    finally:
        fh.close()


def group_overview(my_path, database, basename, config, group_name, root=None):
    cfg = load_config(config)
    fh = open(basename + '.html', 'w')
    chartname = os.path.basename(basename)
    try:
        args = [os.path.join(my_path, 'html-report'), '-d', database, '--chart', '%s.svg' % chartname]
        args += ['--report-type', 'group_overview']
        args += ['--group', group_name]
        if root:
            args += ['--root', root]
        report_args(args)
        html_args = args[:]
        html_args += ['--theme', get_theme(cfg)]
        proc = Popen(html_args, stdout=fh)
        print basename + '.html'
        proc.wait()
    finally:
        fh.close()

    args = [os.path.join(my_path, 'burndown-chart'), '-d', database, '-o', basename + '.svg', "--group", group_name]
    proc = Popen(args)
    print basename + '.svg'
    proc.wait()


def workitem_list(my_path, database, basename, config, status, root=None):
    """Generate a page listing the workitems in a particular status."""
    cfg = load_config(config)
    team = cfg.get("primary_team", None)
    fh = open(basename + '.html', 'w')
    chartname = os.path.basename(basename)
    try:
        args = [os.path.join(my_path, 'html-report'), '-d', database, '--chart', '%s.svg' % chartname]
        args += ['--report-type', 'workitem_list']
        args += ['--status', status]
        if root:
            args += ['--root', root]
        report_args(args, team=team, theme=get_theme(cfg))
        proc = Popen(args, stdout=fh)
        print basename + '.html'
        proc.wait()
    finally:
        fh.close()


def team_list_page(my_path, database, basename, config, root=None):
    cfg = load_config(config)
    team = cfg.get("primary_team", None)
    fh = open(basename + '.html', 'w')
    try:
        args = [os.path.join(my_path, 'html-report'), '-d', database]
        args += ['--report-type', 'team_list']
        if root:
            args += ['--root', root]
        report_args(args, team=team, theme=get_theme(cfg))
        proc = Popen(args, stdout=fh)
        print basename + '.html'
        proc.wait()
    finally:
        fh.close()


def person_list_page(my_path, database, basename, config, root=None):
    cfg = load_config(config)
    team = cfg.get("primary_team", None)
    fh = open(basename + '.html', 'w')
    try:
        args = [os.path.join(my_path, 'html-report'), '-d', database]
        args += ['--report-type', 'person_list']
        if root:
            args += ['--root', root]
        report_args(args, team=team, theme=get_theme(cfg))
        proc = Popen(args, stdout=fh)
        print basename + '.html'
        proc.wait()
    finally:
        fh.close()


def milestone_list_page(my_path, database, basename, config, root=None):
    cfg = load_config(config)
    team = cfg.get("primary_team", None)
    fh = open(basename + '.html', 'w')
    try:
        args = [os.path.join(my_path, 'html-report'), '-d', database]
        args += ['--report-type', 'milestone_list']
        if root:
            args += ['--root', root]
        report_args(args, team=team, theme=get_theme(cfg))
        proc = Popen(args, stdout=fh)
        print basename + '.html'
        proc.wait()
    finally:
        fh.close()


def about_page(my_path, database, basename, config, root=None):
    cfg = load_config(config)
    fh = open(basename + '.html', 'w')
    try:
        args = [os.path.join(my_path, 'html-report'), '-d', database]
        args += ['--report-type', 'about']
        if root:
            args += ['--root', root]
        report_args(args, theme=get_theme(cfg))
        proc = Popen(args, stdout=fh)
        print basename + '.html'
        proc.wait()
    finally:
        fh.close()


def roadmap_pages(my_path, database, basename, config, lane, root=None):
    cfg = load_config(config)
    fh = open(basename + '.html', 'w')
    chart_path, _ = os.path.split(basename)
    chart_name = os.path.join(chart_path, 'current_quarter.svg')
    try:
        args = [os.path.join(my_path, 'html-report'), '-d', database]
        args += ['--report-type', 'roadmap_page']
        args += ['--lane', lane.name]
        if root:
            args += ['--root', root]
        if lane.is_current:
            args += ['--chart', chart_name]
        report_args(args, theme=get_theme(cfg))
        proc = Popen(args, stdout=fh)
        print basename + '.html'
        proc.wait()
    finally:
        fh.close()

    if lane.is_current:
        args = [os.path.join(my_path, 'roadmap-bp-chart'), '-d', database,
                '-o', chart_name]
        args += ['--inverted']
        proc = Popen(args)
        print chart_name
        proc.wait()


def roadmap_cards(my_path, database, basename, config, card, root=None):
    cfg = load_config(config)
    fh = open(basename + '.html', 'w')
    try:
        args = [os.path.join(my_path, 'html-report'), '-d', database]
        args += ['--report-type', 'roadmap_card']
        args += ['--card', '%s' % card.card_id]
        if root:
            args += ['--root', root]
        report_args(args, theme=get_theme(cfg))
        proc = Popen(args, stdout=fh)
        print basename + '.html'
        proc.wait()
    finally:
        fh.close()


def run_reports(my_path, database, basename, config, milestone=None, team=None,
        user=None, trend_starts=None, trend_override=None, burnup=False, root=None, date=None):

    ident = (team or user)
    if milestone and ident:
        chartname = ident + '-' + milestone
    elif milestone:
        chartname = 'all-' + milestone
    elif date and ident:
        chartname = "%s-%s" % (date, ident)
    elif date:
        chartname = 'all-' + date
    elif ident:
        chartname = ident
    else:
        chartname = 'all'

    cfg = load_config(config)

    hf = open(basename + '.html', 'w')
    args = [os.path.join(my_path, 'html-report'), '-d', database, '--chart', '%s.svg' % chartname]
    if 'title' in cfg:
        args += ['--title', cfg['title']]
    if root:
        args += ['--root', root]
    report_args(
        args, team=team, user=user, milestone=milestone, date=date,
        theme=get_theme(cfg))
    hreport = Popen(args, stdout=hf)

    if not date: # date support not implemented in json-report yet
        jf = open(basename + '.json', 'w')
        args = [os.path.join(my_path, 'json-report'), '-d', database, '-c', config]
        report_args(args, team=team, user=user, milestone=milestone)
        jreport = Popen(args, stdout=jf)

    chart_path = os.path.join(os.path.dirname(basename), chartname + '.svg')
    args = [os.path.join(my_path, 'burndown-chart'), '-d', database, '-o', chart_path]
    report_args(args, team=team, user=user, milestone=milestone, date=date)

    if trend_override:
        args.extend(['--trend-start', trend_override])
    else:
        if milestone and ident:
            if (ident, milestone) in trend_starts:
                args.extend(['--trend-start', str(trend_starts[(ident, milestone)])])
        elif ident and not date:
            if ident in trend_starts:
                args.extend(['--trend-start', str(trend_starts[ident])])
    if burnup:
        args.extend(['--only-weekdays', '--inverted'])
    burndown = Popen(args)

    print basename + '.html'
    hreport.wait()
    hf.close()
    if not date:
        print basename + '.json'
        jreport.wait()
        jf.close()
    print basename + '.svg'
    burndown.wait()


def fix_stdouterr():
    '''Set stdout/stderr encoding.

    Avoid having to do .encode('UTF-8') everywhere. This is a pain; I wish
    Python supported something like "sys.stdout.encoding = 'UTF-8'".
    '''
    import codecs
    def null_decode(input, errors='strict'):
        return input, len(input)
    sys.stdout = codecs.EncodedFile(sys.stdout, 'UTF-8')
    sys.stdout.decode = null_decode
    sys.stderr = codecs.EncodedFile(sys.stderr, 'UTF-8')
    sys.stderr.decode = null_decode


def get_store(dbpath):
    assert os.path.exists(dbpath)
    db = create_database("sqlite:" + dbpath)
    return Store(db)


def load_config(config_path):
    '''Load a configuration file and return it as a dictionary.'''

    cfg = {}
    execfile(config_path, cfg)
    del cfg['__builtins__']
    return cfg

def escape_url(url):
    '''Correctly escape data intended to be included in a URL'''

    return urllib.quote(url)

def escape_html(html):
    '''Correctly escape data intended to be included as raw HTML'''

    return escape(html, True)


def blueprints_over_time(store):
    '''Calculate blueprint development over time for the current lane.

    We do not need to care about teams or groups since this is intended for the
    roadmap overview.

    Return date -> state -> count mapping. states are
    {planned,inprogress,completed,blocked}.
    '''
    data = {}
    result = store.execute("""
        SELECT status, day, count
        FROM spec_daily_count_per_state
        JOIN lane on lane.lane_id = spec_daily_count_per_state.lane_id
        WHERE lane.is_current = 1
        """)
    for status, day, count in result:
        roadmap_status = get_roadmap_status_for_bp_implementation_status(
            status)
        assert roadmap_status is not None
        if day not in data:
            data[day] = {}
        if roadmap_status not in data[day]:
            data[day][roadmap_status] = 0
        data[day][roadmap_status] += count
    return data


def workitems_over_time(store, team=None, group=None, milestone_collection=None):
    '''Calculate work item development over time.

    If team is given, this filters out blueprints for that particular team. If
    team_assignees_only is True, only work items for that team's members are
    counted, otherwise this counts all work items for specs belonging to the
    given team.

    Return date -> state -> count mapping. states are
    {todo,inprogress,done,postponed}{,_teamonly}.
    '''
    data = {}
    ms_sql = ''
    if milestone_collection is not None:
        ms_sql = milestone_collection.milestone_start_sql(store)
    # include both direct team assignments, as well as assignmets to
    # members of that team
    if team:
        # WIs which are assigned to team members
        if isinstance(team, user_string):
            result = store.execute(
                    'SELECT status, date, count(status) FROM ('
                    'SELECT DISTINCT date, w.spec, w.description, w.assignee, w.milestone, w.status AS status '
                    'FROM work_items w '
                    'WHERE assignee = ? %s '
                    ') GROUP BY status, date' % ms_sql, (unicode(team),))
        else:
            result = store.execute(
                    'SELECT status, date, count(status) FROM ('
                    'SELECT DISTINCT date, w.spec, w.description, w.assignee, w.milestone, w.status AS status '
                    'FROM work_items w LEFT JOIN teams '
                    'ON w.assignee = teams.name '
                    'WHERE (team = ? OR assignee = ?) %s'
                    ') GROUP BY status, date' % ms_sql, (unicode(team), unicode(team)))

        for (s, date, num) in result:
            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):
            result = store.execute(
                    'SELECT status, date, count(status) FROM ('
                    'SELECT DISTINCT date, w.spec, w.description, w.assignee, w.milestone, w.status AS status '
                    '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 status, date' % ms_sql, (unicode(team), unicode(team)))
        else:
            result = store.execute(
                    'SELECT status, date, count(status) FROM ('
                    'SELECT DISTINCT date, w.spec, w.description, w.assignee, w.milestone, w.status AS 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 (teams.team = ? OR w.assignee = ? OR s.assignee = ?) %s'
                    ') GROUP BY status, date' % ms_sql, (unicode(team), unicode(team), unicode(team)))

        for (s, date, num) in result:
            data.setdefault(date, {})[s] = num
    elif group:
        result = store.execute('SELECT status, date, count(status) FROM ('
                    'SELECT DISTINCT date, w.spec, w.description, w.assignee, w.milestone, w.status AS status '
                    'FROM work_items AS w JOIN specs AS s ON w.spec = s.name '
                    'JOIN spec_group_specs AS sgs ON sgs.spec = s.name '
                    'JOIN spec_groups AS g ON g.name = sgs.spec_group '
                    'WHERE g.name = ? %s'
                    ') GROUP BY status, date' % ms_sql, (unicode(group),))
        for (s, date, num) in result:
            data.setdefault(date, {})[s] = num
    else:
        # all WIs for this milestone
        result = store.execute('SELECT status, date, count(*) FROM work_items w '
                'WHERE 1=1 %s GROUP BY status, date' % ms_sql)

        for (s, date, num) in result:
            data.setdefault(date, {})[s] = num
    return data

def milestone_select_sql(milestone_collection=None):
    if milestone_collection == '':
        ms_sql = "AND w.milestone IS NULL"
    elif milestone_collection is not None:
        ms_sql = milestone_collection.milestone_select_sql()
    else:
        ms_sql = ""
    return ms_sql

def spec_group_completion(store, team=None, milestone_collection=None):
    data = {}
    ms_sql = milestone_select_sql(milestone_collection=milestone_collection)

    # last date
    result = store.execute('SELECT max(date) FROM work_items')
    (last_date,) = result.get_one()

    if team:
        # include both direct team assignments, as well as assignmnets to
        # members of that team
        if isinstance(team, user_string):
            result = store.execute('SELECT w.status, g.name, COUNT(DISTINCT w.description), g.status, g.priority, g.implementation, g.url, g.assignee, g.area '
                    'FROM work_items AS w JOIN specs AS s ON w.spec = s.name JOIN spec_group_specs AS sgs ON s.name = sgs.spec JOIN spec_groups AS g ON g.name = sgs.spec_group '
                    'WHERE w.date = ? AND'
                    '  (w.assignee = ? or (s.assignee = ? and w.assignee is null) or g.assignee = ?) %s '
                    'GROUP BY w.status,sgs.spec_group' % ms_sql,
                    (unicode(last_date), unicode(team), unicode(team), unicode(team)))
        else:
            result = store.execute('SELECT w.status, g.name, COUNT(DISTINCT w.description), g.status, g.priority, g.implementation, g.url, g.assignee, g.area '
                    'FROM work_items AS w JOIN specs AS s ON w.spec = s.name JOIN spec_group_specs AS sgs ON s.name = sgs.spec JOIN spec_groups AS g ON g.name = sgs.spec_group '
                    '   LEFT JOIN teams ON (s.assignee = teams.name OR w.assignee = teams.name OR g.assignee = teams.name)'
                    'WHERE w.date = ? AND'
                    '  (teams.team = ? OR w.assignee = ? or s.assignee = ? or g.assignee = ?) %s '
                    'GROUP BY w.status, sgs.spec_group' % ms_sql,
                    (unicode(last_date), unicode(team), unicode(team), unicode(team), unicode(team)))
    else:
        result = store.execute('SELECT w.status, g.name, COUNT(DISTINCT w.description), g.status, g.priority, g.implementation, g.url, g.assignee, g.area '
                'FROM work_items AS w JOIN specs AS s ON w.spec = s.name JOIN spec_group_specs AS sgs ON s.name = sgs.spec JOIN spec_groups AS g ON g.name = sgs.spec_group '
                'WHERE w.date = ? %s '
                'GROUP BY w.status, sgs.spec_group' % ms_sql,
                (unicode(last_date),))

    for (s, bp, num, status, priority, impl, url, assignee, area) in result:
        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['assignee'] = assignee
        info['area'] = area
    return data


def spec_group_info(store, group_name):
    result = store.execute('SELECT max(date) FROM work_items')
    (last_date,) = result.get_one()
    result = store.execute('SELECT w.status, g.name, COUNT(DISTINCT w.description), g.status, g.priority, g.implementation, g.url, g.assignee, g.area '
            'FROM work_items AS w JOIN specs AS s ON w.spec = s.name JOIN spec_group_specs AS sgs ON s.name = sgs.spec JOIN spec_groups AS g ON g.name = sgs.spec_group '
            'WHERE w.date = ? AND g.name = ?'
            'GROUP BY w.status', (unicode(last_date), unicode(group_name)))
    info = {'todo': 0, 'blocked': 0, 'done': 0, 'postponed': 0, 'inprogress': 0}
    has_data = False
    for (s, bp, num, status, priority, impl, url, assignee, area) in result:
        has_data = True
        info[s] = num
        info['status'] = status or ''
        info['priority'] = priority
        info['implementation'] = impl
        info['url'] = url
        info['assignee'] = assignee
        info['area'] = area
    if not has_data:
        return None
    result = store.execute('SELECT w.status, s.name, COUNT(DISTINCT w.description), s.status, s.priority, s.implementation, s.url, s.assignee '
            'FROM work_items AS w JOIN specs AS s ON w.spec = s.name JOIN spec_group_specs AS sgs ON s.name = sgs.spec JOIN spec_groups AS g ON g.name = sgs.spec_group '
            'WHERE w.date = ? AND g.name = ? '
            'GROUP BY w.status, s.name', (unicode(last_date), unicode(group_name)))
    specs = {}
    for (s, bp, num, status, priority, impl, url, assignee) in result:
        spec_info = specs.setdefault(bp, {'todo': 0, 'blocked': 0, 'done': 0, 'postponed': 0, 'inprogress': 0})
        spec_info[s] = num
        spec_info['status'] = status or ''
        spec_info['priority'] = priority
        spec_info['implementation'] = impl
        spec_info['url'] = url
        spec_info['assignee'] = assignee
    info['specs'] = specs
    return info


def blueprint_completion(store, team=None, milestone_collection=None):
    '''Determine current blueprint completion.

    Return blueprint -> info mapping, with info being a map with these
    keys: todo, inprogress, done, postponed, status, priority, implementation, url.
    '''
    data = {}
    ms_sql = milestone_select_sql(milestone_collection=milestone_collection)

    # last date
    result = store.execute('SELECT max(date) FROM work_items')
    (last_date,) = result.get_one()

    if team:
        # include both direct team assignments, as well as assignmnets to
        # members of that team
        if isinstance(team, user_string):
            result = store.execute('SELECT w.status, s.name, COUNT(DISTINCT w.description), s.status, s.priority, s.implementation, s.url, s.roadmap_notes, s.assignee '
                    '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,
                    (unicode(last_date), unicode(team), unicode(team)))
        else:
            result = store.execute('SELECT w.status, s.name, COUNT(DISTINCT w.description), s.status, s.priority, s.implementation, s.url, s.roadmap_notes, s.assignee '
                    '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.date = ? AND'
                    '  (teams.team = ? OR w.assignee = ? or s.assignee = ?) %s '
                    'GROUP BY w.status, w.spec' % ms_sql,
                    (unicode(last_date), unicode(team), unicode(team), unicode(team)))
    else:
        result = store.execute('SELECT w.status, s.name, COUNT(DISTINCT w.description), s.status, s.priority, s.implementation, s.url, s.roadmap_notes, s.assignee '
                'FROM work_items w, specs s '
                'ON w.spec = s.name '
                'WHERE w.date = ? %s '
                'GROUP BY w.status, w.spec' % ms_sql,
                (unicode(last_date),))

    for (s, bp, num, status, priority, impl, url, roadmap_notes, assignee) in result:
        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
        info['assignee'] = assignee
        
        result2 = store.execute('SELECT SUM(points) FROM complexity as w WHERE spec = ? AND date = ? %s' % ms_sql, (bp, last_date))
        for points in result2:
            info['complexity'] = points[0] 

    return data


def blueprint_tasks_completion(store, team=None, milestone=None):
    '''Determine current blueprint tasks completion.

    Return blueprint -> info mapping, with info being a map with these
    keys: todo, blocked, inprogress, done, postponed, status, priority, implementation, url, tasks.
    '''
    default = blueprint_completion(store, team=team)
    data = {}
    if milestone == '':
        ms_sql = "AND w.milestone IS NULL"
    elif milestone:
        ms_sql = "AND w.milestone='%s'" % escape_sql(milestone)
    else:
        ms_sql = ''

    # last date
    result = store.execute('SELECT max(date) FROM work_items')
    (last_date,) = result.get_one()

    for s in valid_states:
        if team:
            # include both direct team assignments, as well as assignmnets to
            # members of that team
            if isinstance(team, user_string):
                result = store.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,
                        (unicode(s), unicode(last_date), unicode(team), unicode(team)))
            else:
                result = store.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,
                        (unicode(s), unicode(last_date), unicode(team), unicode(team), unicode(team)))
        else:
            result = store.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 = ? %s '
                    'GROUP BY w.spec' % ms_sql,
                    (unicode(s), unicode(last_date)))

        for (bp, desc, assignee, milestone, status) in result:
            data.setdefault(bp, default[bp])
            info = data[bp].setdefault('tasks', [])
            info.append({
                'description': desc,
                'assignee': assignee,
                'milestone': milestone,
                'state': status
            })

    return data


def blueprint_count(store, team=None, milestone=None):
    if team:
        if isinstance(team, user_string):
            result = store.execute('SELECT COUNT(s.name) '
                    'FROM specs AS s '
                    'WHERE s.assignee = ?',
                    (unicode(team), ))
        else:
            result = store.execute('SELECT COUNT(s.name) '
                    'FROM specs AS s LEFT JOIN teams ON s.assignee = teams.name '
                    'WHERE teams.team = ? OR s.assignee = ?',
                    (unicode(team), unicode(team)))
    else:
        result = store.execute('SELECT COUNT(s.name) FROM specs AS s')
    return result.get_one()[0]


def milestone_due_date(store, milestone=None):
    if milestone is not None:
        result = store.execute('SELECT due_date FROM milestones WHERE name = ?', (unicode(milestone), ))
    else:
        # FIXME: this won't work for Ubuntu with Linaro's milestones in
        # there.
        result = store.execute('SELECT MAX(due_date) FROM milestones')
    end_date = result.get_one()[0]
    return end_date


def date_to_python(s):
    year, month, day = map(int, s.split('-'))
    return datetime.date(year, month, day)


def weekdays_between(start_date, end_date):
    if start_date >= end_date:
        return 0
    days_in_week = 7
    weekdays_in_week = 5
    days_between = end_date.toordinal() - start_date.toordinal()
    weeks = days_between/days_in_week
    days_remainder = days_between - (weeks * days_in_week)
    # A simplistic answer would be min(weekdays_in_week, days_left)
    # but that doesn't take in to account which days this is.
    # Depending on the weekday of the end date and the days_remainder
    # For instance, if the end date is a Sunday, and the days_remainder
    # is 1, then that means the day in question is a Saturday, so
    # shouldn't be counted
    starting_weekday = end_date.weekday()-days_remainder
    # These two options are mutually exclusive, as days_remainder is < 7
    if end_date.weekday() > 5:
        # It ends on a Sunday, so we lose the day before
        days_remainder -= 1
    elif starting_weekday < 0:
        # The remainer crosses a weekend
        # We lose one or two days. If the starting weekday is Sunday
        # (-1) we lose one day. If it is Saturday or earlier we lose two
        # days
        days_remainder += max(starting_weekday, -2)
    return (weeks * weekdays_in_week) + days_remainder


def duration_elapsed(start_date, end_date, current_date):
    if start_date >= end_date:
        return 100
    elif current_date >= end_date:
        return 100
    elif start_date >= current_date:
        return 0
    else:
        total_duration = end_date.toordinal() - start_date.toordinal()
        elapsed = current_date.toordinal() - start_date.toordinal()
        return (elapsed/(total_duration * 1.0)) * 100


def milestone_start_date(store, milestone=None):
    if milestone is not None:
        ms_sql = SingleMilestone(milestone, None, None, None).milestone_start_sql(store)
    else:
        ms_sql = ''
    result = store.execute('SELECT MIN(date) FROM work_items w '
            'WHERE 1=1 %s GROUP BY date' % ms_sql)
    date_tuple = result.get_one()
    if date_tuple is None:
        return None
    return date_tuple[0]


def days_left(store, milestone=None):
    due_date_str = milestone_due_date(store, milestone=milestone)
    due_date = date_to_python(due_date_str)
    current_date = datetime.date.today()
    start_date_str = milestone_start_date(store, milestone=milestone)
    if start_date_str is not None:
        start_date = date_to_python(start_date_str)
    else:
        start_date = current_date
    cycle_percent_elapsed = duration_elapsed(
        start_date, due_date, current_date)
    return dict(
        current_date=current_date,
        days_left=weekdays_between(current_date, due_date),
        cycle_percent_elapsed=cycle_percent_elapsed,
        )


def assignee_completion(store, team=None, group=None, milestone_collection=None):
    '''Determine current by-assignee completion.

    Return assignee -> info mapping with info being a map with these
    keys: todo, blocked, inprogress, done, postponed. Each of those values is a list of [blueprint,
    workitem, priority, spec_url].
    '''
    data = {}
    ms_sql = milestone_select_sql(milestone_collection=milestone_collection)

    # last date
    result = store.execute('SELECT max(date) FROM work_items')
    (last_date,) = result.get_one()

    for s in valid_states:
        if team:
            # include both direct team assignments, as well as assignmnets to
            # members of that team
            if isinstance(team, user_string):
                result = store.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,
                        (unicode(s), unicode(last_date), unicode(team), unicode(team)))
            else:
                result = store.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,
                        (unicode(s), unicode(last_date), unicode(team), unicode(team), unicode(team)))
        elif group:
            result = store.execute('SELECT DISTINCT w.assignee, w.spec, w.description, s.priority, s.url '
                    'FROM work_items AS w JOIN specs AS s ON w.spec = s.name '
                    'JOIN spec_group_specs AS sgs ON sgs.spec = s.name '
                    'JOIN spec_groups AS g ON g.name = sgs.spec_group '
                    'WHERE w.status = ? AND w.date = ? AND'
                    '  g.name = ? %s ' % ms_sql,
                    (unicode(s), unicode(last_date), unicode(group)))
        else:
            result = store.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 = ? %s ' % ms_sql,
                    (unicode(s), unicode(last_date)))
        for (a, bp, description, priority, url) in result:
            info = data.setdefault(a, {'todo': [], 'blocked': [], 'done': [], 'postponed': [], 'inprogress': []})
            info[s].append([bp, description, priority, url])

            result2 = store.execute('SELECT SUM(points) FROM complexity as w WHERE assignee = ? AND date = ? %s' % ms_sql, (unicode(a), unicode(last_date)))
            for points in result2:
                info['complexity'] = points[0]

    return data

def spec_information(store, config, team=None, milestone=None ):
    '''The meta information a spec exposes through Launchpad

    Returns a dictionary of specs -> meta information closely
    mirroring the DB structure
    '''

    ### cargo culted from assignee_completion
    if milestone == '':
        ms_sql = "AND milestone IS NULL"
    elif milestone:
        ms_sql = "AND milestone='%s'" % milestone
    else:
        ms_sql = ''

    ### limit by a team 'like'? (see config)
    if team:
        lookup = ''
        try:
            lookup = config.get('teams')[team]
        except KeyError:
            lookup = team
        team_sql = "AND name like '%%%s%%' " % lookup
    else:
        team_sql = ""

    completion_dict = blueprint_completion(store, team, get_milestone(store, milestone))

    # last date
    result = store.execute('SELECT max(date) FROM work_items')
    (last_date,) = result.get_one()

    ### need team information to map assignee => team
    teams = {}
    team_result = store.execute('SELECT name,team FROM teams WHERE team IS NOT NULL')
    for n,t in team_result.get_all():
        teams[n] = t

    spec_columns = [
        'name',
        'url',
        'priority',
        'implementation',
        'assignee',
        'team',
        'status',
        'milestone',
        'definition',
        'drafter',
        'approver',
        'details_url',
        'roadmap_notes',
    ]

    work_item_columns = [
        'description',
        'spec',
        'status',
        'assignee',
        'milestone',
        'date'
    ]

    meta_columns = [
        'key',
        'value',
        'spec',
        'date',
    ]

    sql = "select " + ", ".join(spec_columns) + " from specs where 1=1 " + ms_sql + " " + team_sql

    result = store.execute(sql)

    rv = {}


    ### surely.. there's a better way?
    for spec_row in result.get_all():

        spec_dict = {
            "work_items":   [],
            "meta":         [],
            "completion":   {},
        }

        for i, column in enumerate(spec_columns):
            spec_dict[column] = spec_row[i]

        ### the 'team' column is empty in the specs table,
        ### so we'll find the info ourselves

        spec_dict["team"] = teams.get(
            spec_dict['assignee'],
            teams.get(spec_dict['approver'], None))

        ### use the todo/done/postponed numbers from blueprint_completion
        csd = completion_dict.get(spec_dict["name"], None )
        if csd:
            for k in valid_states:
                spec_dict["completion"][k] = csd[k]


        ### extend it with the work item data
        wi_results = store.execute( "select " + ", ".join(work_item_columns) + " from work_items where date = ? and spec = ? "
                                    + ms_sql, (unicode(last_date), unicode(spec_dict["name"]),) )

        for wi_row in wi_results.get_all():
            wi_dict = {}

            ### map the result into key/value pairs
            for i, column in enumerate(work_item_columns):
                wi_dict[column] = wi_row[i]

            spec_dict["work_items"].append(wi_dict)

        ### extend it with the meta information
        meta_results = store.execute( "select " + ", ".join(meta_columns) + " from meta where date = ? and spec = ?",
                                      (unicode(last_date), unicode(spec_dict["name"]) ) )
 
        for meta_row in meta_results:
            meta_dict = {}

            for i, column in enumerate(meta_columns):
                meta_dict[column] = meta_row[i]

            spec_dict["meta"].append(meta_dict)

        rv[spec_dict["name"]] = spec_dict
    return rv

def team_information(store, team=None ):
    '''The team information as returned by LP

    Returns a dictionary with teamname -> members
    '''

    if team:
        teams = [ team ]
    else:
        result = store.execute('SELECT DISTINCT team FROM teams WHERE team IS NOT NULL')
        teams = [i[0] for i in result]

    rv = {}

    for t in teams:
        result = store.execute( 'SELECT name FROM teams WHERE team = ?', (unicode(t),) )

        rv[t] = [ i[0] for i in result ]

    return rv


def lanes(store):
    return store.find(Lane)


def lane(store, name, id=None):
    if id is None:
        return store.find(Lane, Lane.name == unicode(name)).one()
    else:
        return store.find(Lane, Lane.lane_id == id).one()


def current_lane(store):
    return store.find(Lane, Lane.is_current).one()


def lane_cards(store, lane):
    return lane.cards


def statuses(store, lane):
    result = []
    for status in store.find(Card.status,
                             Card.lane_id == lane.lane_id).config(distinct=True):
        result.append((status, store.find(Card,
                                          Card.lane_id == lane.lane_id,
                                          Card.status == status)))
    return result


def cards(store):
    return store.find(Card)


def card(store, card_id):
    return store.find(Card, Card.card_id == card_id)


def card_blueprints(store, roadmap_id):
    metas = store.find(Meta,
                       Meta.key == u'Roadmap id',
                       Meta.value == roadmap_id)
    return [meta.blueprint for meta in metas]


def card_blueprints_by_status(store, roadmap_id):
    blueprints = card_blueprints(store, roadmap_id)
    bp_by_status = {}
    for key in ROADMAP_STATUSES_MAP:
        bp_by_status[key] = []
    for bp in blueprints:
        bp_by_status[bp.roadmap_status].append(bp)
    return bp_by_status


def card_bp_status_counts(store, roadmap_id):
    blueprints = card_blueprints(store, roadmap_id)
    total_by_status = dict([(key, 0) for key in ROADMAP_STATUSES_MAP])
    for bp in blueprints:
        total_by_status[bp.roadmap_status] += 1
    return total_by_status


def check_card_health(store, card_health_checks, card):
    performed_checks = []
    card.is_healthy = True
    for check in card_health_checks:
        result = check.execute(card, store)
        if result == check.NOT_OK:
            card.is_healthy = False
        performed_checks.append({'name': check.name,
                                 'result': result})
    return performed_checks


def subteams(store, team):
    result = store.execute('SELECT name from team_structure where team = ?', (unicode(team),))
    return [i[0] for i in result]


def all_teams(store):
    result = store.execute('SELECT DISTINCT team from teams where team is not NULL')
    return [i[0] for i in result]


def milestone_list(store):
        milestones = {}

        result = store.execute('SELECT m.name, m.project FROM milestones m ORDER BY m.due_date')

        for (milestone, project) in result:
            milestones[milestone] = project

        return milestones


class MilestoneCollection(object):

    single_milestone = False

    def __init__(self, due_date):
        self.due_date = due_date

    @property
    def due_date_str(self):
        return self.due_date.strftime("%Y-%m-%d")

    @property
    def display_name(self):
        """A name suitable for display for this collection."""
        raise NotImplementedError(self.display_name)

    def milestone_start_sql(self, store):
        """Returns an SQL fragment that restricts workitem data to this collection.

        It can restrict to workitems targeted to milestones in this collection.

        It can also restrict to workitem data records that are after this milestone
        started, and before it completed.

        :param store: a Storm store that can be used to query needed information.
        """
        raise NotImplementedError(self.milestone_start_sql)

    def milestone_select_sql(self):
        """Returns an SQL fragments that restricts workitems to this collection.

        If the returned fragment is used in an SQL query then that query should only
        consider workitems targeted to the milestones in this collection.
        """
        raise NotImplementedError(self.milestone_select_sql)


class SingleMilestone(MilestoneCollection):

    single_milestone = True

    def __init__(self, name, link, project, due_date):
        super(SingleMilestone, self).__init__(due_date)
        self.name = name
        self.link = link
        self.project = project

    @property
    def display_name(self):
        return "milestone %s" % self.name

    def milestone_start_sql(self, store):
        result = store.execute('SELECT due_date, project FROM milestones WHERE name = ?', (unicode(self.name),))
        row = result.get_one()
        date_end = row[0]
        project = row[1]
        result = store.execute('SELECT MAX(due_date), MAX(due_date) < date(CURRENT_TIMESTAMP) FROM milestones WHERE due_date < ? AND project = ?', (unicode(date_end), unicode(project)))
        (date_start, in_milestone) = result.get_one()

        # if we are already within the milestone, start at the previous
        # milestone's end date; if we are before, start at the beginning of the
        # entire cycle, to allow planning
        if in_milestone:
            ms_sql = "AND w.milestone='%s' AND date >= '%s' AND date <= '%s'" % (
                    escape_sql(self.name), date_start, date_end)
        else:
            ms_sql = "AND w.milestone='%s' AND date <= '%s'" % (
                    escape_sql(self.name), date_end)
        return ms_sql

    def milestone_select_sql(self):
        return "AND w.milestone='%s'" % escape_sql(self.name)


class MilestoneGroup(MilestoneCollection):

    def __init__(self, due_date):
        super(MilestoneGroup, self).__init__(due_date)
        self.milestones = []

    @property
    def name(self):
        return self.due_date_str

    @property
    def display_name(self):
        return "any milestone on %s" % self.due_date_str

    def milestone_start_sql(self, store):
        result = store.execute('SELECT MAX(due_date), MAX(due_date) < date(CURRENT_TIMESTAMP) FROM milestones WHERE due_date < ?', (unicode(escape_sql(self.due_date_str)), ))
        (date_start, in_milestone) = result.get_one()
        if in_milestone:
            ms_sql = "AND date >= '%s' AND date <= '%s'" % (date_start, escape_sql(self.due_date_str))
        else:
            ms_sql = "AND date <= '%s'" % escape_sql(self.due_date_str)
        ms_sql += "AND w.milestone IN (SELECT m.name from milestones m WHERE m.due_date = '%s')" % escape_sql(self.due_date_str)
        return ms_sql

    def milestone_select_sql(self):
        return (
            "AND w.milestone IN "
            "(SELECT m.name from milestones m WHERE m.due_date = '%s')"
                % self.due_date_str)


def get_milestone(store, milestone_name):
    result = store.execute(
        "SELECT project, due_date from milestones where name = ?",
        (unicode(milestone_name), ))
    row = result.get_one()
    if row is None:
        return None
    project, due_date = row
    return SingleMilestone(milestone_name, None, project, date_to_python(due_date))


def milestone_groups(store, team=None):
    if team:
        template = team + "-%s.html"
    else:
        template = "all-%s.html"
    groups = []
    result = store.execute('SELECT m.name, m.project, m.due_date '
            'FROM milestones m ORDER BY m.due_date, m.name, m.project')
    for name, project, due_date_str in result:
        due_date = date_to_python(due_date_str)
        if len(groups) < 1 or due_date != groups[-1].due_date:
            group = MilestoneGroup(due_date)
            groups.append(group)
        else:
            group = groups[-1]
        group.milestones.append(
            SingleMilestone(name, template % name, project, due_date))
    return groups


def real_names(store):
    result = store.execute('SELECT name, display_name FROM real_names')
    names_dict = {}
    for (short_name, display_name) in result:
        names_dict[short_name] = display_name
    return names_dict


def priority_value(name):
    '''Translate a priority string into a sortable numeric value.'''

    trans = {'Essential': 99, 'High': 75, 'Medium': 50, 'Low': 25}

    return trans.get(name, 0)

def lock_file(f):
    try:
        fcntl.flock(f, fcntl.LOCK_EX | fcntl.LOCK_NB)
        return f
    except IOError, e:
        if e.errno in (errno.EACCES, errno.EAGAIN):
            return None
        raise

def fill_template(name, data, theme=None):
    from mako.lookup import TemplateLookup
    directories = ['templates']
    if theme is not None:
        directories.insert(0, os.path.join("themes", theme, "templates"))
    lookup = TemplateLookup(directories=directories)
    template = lookup.get_template(name)
    return template.render_unicode(**data)


if __name__ == '__main__':
    # some test stuff
    store = get_store(sys.argv[1])

    print '==== Assignee completion ==='
    for date, data in assignee_completion(store).iteritems():
        print date, [len(data[x]) for x in data]
    print '--------------'
    for date, data in assignee_completion(store, milestone='lucid-alpha-2').iteritems():
        print date, [len(data[x]) for x in data]
    print '--------------'
    for date, data in assignee_completion(store, 'canonical-desktop-team').iteritems():
        print date, [len(data[x]) for x in data]
    print '--------------'
    for date, data in assignee_completion(store, 'canonical-desktop-team', milestone='lucid-alpha-2').iteritems():
        print date, [len(data[x]) for x in data]