Importing hubot daily standups from Redis

config.toml.tmpl

[redis]
host = "192.168.1.218"
port = 6379
password = "PASSWORD"

[excel]
workbook = "Daily.xlsx"

slurp.py

#!/usr/bin/env python3

import sys
import toml
import redis
import json
import xlsxwriter

ROOMS = {
    "daily-updates:CHWJ7PH7Y": "DevOps",
    "daily-updates:CHZ9S39FD": "Sustaining",
    "daily-updates:CJBGZGXPE": "Framework"
    }

room_activity = {}

config = toml.load("config.toml")

try:
    r = redis.Redis(host=config['redis']['host'], port=config['redis']['port'], db=0, password=config['redis']['password'])
    hubot_key = r.keys()[0]
except Exception as e:
    print("Could not connect! Make sure you running:\n\nkubectl port-forward --address 0.0.0.0 --namespace default svc/redis-master 6379:6379\n")
    print(e)
    sys.exit(1)

hubot_brain = json.loads(r.get(hubot_key))
dailies = [(room, content) for room, content in hubot_brain['_private'].items() if room.startswith('daily-updates:')]
for room,content in dailies:
    daily_activity = {}
    room_activity[room] = {'activity': daily_activity, 'users':[]}
    for user,report in content.items():
        room_activity[room]['users'].append(user)
        for day,items in report.items():
            if daily_activity.get(day) is None:
                daily_activity[day] = {}
            if daily_activity[day].get(user) is None:
                daily_activity[day][user] = []
            for item in items:
                daily_activity[day][user].append(item)

"""
We are going to create a spreadsheet for each room
"""
workbook = xlsxwriter.Workbook(config['excel']['workbook'])

bold = workbook.add_format({'bold': True})
wrap = workbook.add_format({'text_wrap': True, 'valign': 'top'})
empty = workbook.add_format({'bg_color': '#FFA500', 'font_color': '#9C0006'})

for room, data in room_activity.items():
    worksheet = workbook.add_worksheet(ROOMS[room])
    worksheet.set_column(0, 0, 10)
    worksheet.set_column(1, 99, 30)
    row = 0

    col = 1
    for user in data['users']:
        worksheet.write(row, col, user, bold)
        col += 1
    max_col = col - 1

    activity = data['activity']

    for day in sorted(activity):
        row += 1
        col = 0
        worksheet.write(row, col, day, bold)
        for user in data['users']:
            col += 1
            if activity[day].get(user) is None:
                worksheet.write(row, col, "", wrap)
            else:
                worksheet.write(row, col, "\n".join(activity[day][user]), wrap)

    worksheet.conditional_format(1, 1, row, max_col, {'type': 'blanks', 'format': empty})

workbook.close()