trello2burndown

trello2burndown.py

#! python3

import csv
import datetime
import json
import os
import pickle
import sqlite3

BEGIN_OF_SPRINT = datetime.datetime(2016, 2, 21)
DAYS_OF_SPRINT = 10

def find_lists(filename):
    trello_lists = {}
    with open(filename) as data_file:
        trello = json.load(data_file)
    for item in trello['lists']:
        if item['name'] == 'Offen':
            trello_lists['open'] = item['id']
        elif item['name'] == 'In Arbeit':
            trello_lists['doing'] = item['id']
        elif item['name'] == 'Erledigt':
            trello_lists['done'] = item['id']
    return trello, trello_lists

def find_story_points(card):
    storypoints = 0
    for label in card['labels']:
        if label['name'] == 'schwer':
            storypoints = 8
        elif label['name'] == 'mittel':
            storypoints = 3
        elif label['name'] == 'leicht':
            storypoints = 1
    return storypoints

def find_cards_on_list(trello, trello_lists, list_name):
    open_cards = {}
    for item in trello['cards']:
        if item['idList'] == trello_lists[list_name]:
            storypoints = find_story_points(item)
            open_cards[item['id']] = {
                'name': item['name'],
                'storypoints': storypoints
            }
    return open_cards

def sum_storypoints(*cards):
    storypoints = 0
    for card_set in cards:
        for card_id, card in card_set.items():
            storypoints = storypoints + card['storypoints']
    return storypoints

def get_date(trello):
    work_date = datetime.datetime.strptime(trello['dateLastActivity'][:19], '%Y-%m-%dT%H:%M:%S')
    return work_date

def get_date_as_string(trello):
    work_date = get_date(trello)
    return work_date.strftime('%Y-%m-%d')

def get_work_day(work_date):
    delta = work_date - BEGIN_OF_SPRINT
    return delta.days

def calculate_ideal_remaining_effort(work_day, total_points):
    return float(total_points) - ((float(total_points) / float(DAYS_OF_SPRINT)) *
        float(work_day))

def convert_pickle_to_rows(data):
    rows = []
    for work_day, data_dict in data.items():
        row = [data_dict[0], str(work_day), str(data_dict[1]),
                str(data_dict[2])]
        rows.append(row)
    return rows

def create_db_if_not_exists(filename="trello.db"):
    if os.path.exists(filename):
        return filename
    conn = sqlite3.connect(filename)
    c = conn.cursor()
    c.execute('''CREATE TABLE burndowns
                (date TEXT PRIMARY KEY,
                work_day INTEGER,
                ideal_remaining_effort REAL,
                actual_remaining_effort INTEGER,
                sum_storypoints INTEGER)''')
    conn.commit()
    conn.close()
    return filename

def parse_trello_data(filename='trello.json'):
    trello, trello_lists = find_lists(filename)
    open_cards = find_cards_on_list(trello, trello_lists, 'open')
    doing_cards = find_cards_on_list(trello, trello_lists, 'doing')
    done_cards = find_cards_on_list(trello, trello_lists, 'done')
    actual_remaining_effort = sum_storypoints(open_cards, doing_cards)
    sum_points = sum_storypoints(open_cards, doing_cards, done_cards)
    work_day = get_work_day(get_date(trello))
    ideal_remaining_effort = calculate_ideal_remaining_effort(work_day, sum_points)
    date_string = get_date_as_string(trello)
    return (date_string, work_day, ideal_remaining_effort,
            actual_remaining_effort, sum_points)

def add_or_update_row(data, filename="trello.db"):
    conn = sqlite3.connect(filename)
    c = conn.cursor()
    c.execute('''INSERT OR REPLACE INTO burndowns VALUES (?,?,?,?,?)''', data)
    conn.commit()
    conn.close()

def fetch_data(filename):
    conn = sqlite3.connect(filename)
    c = conn.cursor()
    c.execute('''SELECT * FROM burndowns ORDER BY WORK_DAY ASC''')
    chart_data = c.fetchall()
    conn.commit()
    conn.close()
    return chart_data

def create_report(filename):
    chart_data = fetch_data(filename)
    print("+------------+-----+--------+-------+-------+")
    print("|   Date     | Day | Actual | Ideal | Total |")
    print("+============+=====+========+=======+=======+")
    for row in chart_data:
        print("| %s |  %2d | %6.1f | %5d | %5d |" % row)
    print("+------------+-----+--------+-------+-------+")

def export_as_csv(database, csv_file="trello.csv"):
    chart_data = fetch_data(database)
    with open(csv_file, 'w', newline='') as fp:
        writer = csv.writer(fp)
        for row in chart_data:
            writer.writerow(row)

if __name__ == '__main__':
    database = create_db_if_not_exists()
    data = parse_trello_data('trello.json')
    add_or_update_row(data, database)
    create_report(database)
    export_as_csv(database)