xls stats from db

utils.py

"""More specific helpers."""

from django.conf import settings
from django.core.mail.message import EmailMessage

from datetime import date, timedelta
import os

from core.extdb import ExtDB
from stats.xls import TABLE_DAILY, TABLE_WEEKLY
from stats.sql import RECIPIENT_STATS


def day(for_date=None):
    """Day range to be used with start >= date > finish
    >>> title, start, finish = day()

    Default is yesterday which is last full day range.
    """

    if for_date is None:
        for_date = date.today() - timedelta(1)

    return str(for_date), for_date, for_date + timedelta(1)


def week(for_date=None):
    """Week range to be used with start >= date > finish
    >>> title, start, finish = week()

    Default is previous week.
    """

    if for_date is None:
        for_date = date.today()  # TODO: check for off-by-one err

    return (
        str(for_date - timedelta(for_date.isoweekday())),
        for_date - timedelta(for_date.isoweekday() + 6),
        for_date - timedelta(for_date.isoweekday() - 1))


class Stats(object):
    def __init__(self, db, sql):
        self.db = db
        self.sql = sql

    def prepare(self, start, finish):
        """Grab some data for a range specified:
        return [{'spam': 'sausage', 'bacon': 1, 'eggs': 2}, {...}]
        """

        return ExtDB(self.db).all(self.sql, start, finish)

    def daily(self, for_day=None):
        title, start, finish = day(for_day)
        rows = self.prepare(start, finish)
        doc = TABLE_DAILY.render(rows, start=start)

        return "[%s] Daily stats for %s" % (self.db, title), doc

    def weekly(self, for_day=None):
        title, start, finish = week(for_day)
        rows = self.prepare(start, finish)
        doc = TABLE_WEEKLY.render(rows, start=start, finish=title)
        return "[%s] " \
                "Weekly stats for %s..%s" % (self.db, start, title), doc


def mail_stats(generator, to=None, fname=None):
    title, doc = generator()

    if fname is None:
        fname = "/tmp/%s.xls" % '_'.join(
            x for x in title.replace('..', '_').split()
            if not x.startswith('['))

    if to is None:
        doc.save(fname)
        return title, doc, fname

    try:
        msg = EmailMessage(
            subject = title,
            body = 'See attached file.',
            to = to, bcc = settings.ADMINS)
        msg.attach_file(fname)
        msg.send()
    finally:
        os.remove(fname)

xls.py

"""Render summary reports to xls tables.

Latest verision: https://gist.github.com/1084270

Based on http://goo.gl/AQQfV
"""

import pyExcelerator as xl


STYLES = {
    str: "General",
    int: "0",
    float: "#,##0.00"
}

ALIGNS = {
    '<': xl.Alignment.HORZ_LEFT,
    '|': xl.Alignment.HORZ_CENTER,
    '>': xl.Alignment.HORZ_RIGHT,
    '^': xl.Alignment.VERT_TOP,
    '-': xl.Alignment.VERT_CENTER,
    'v': xl.Alignment.VERT_BOTTOM,
}

FONT_ATTRS = (
    'name', 'height',
    'bold', 'italic',
    'underline', 'struck_out', 'outline'
    'colour_index',
)


class StyleFactory(object):
    _cache = {}

    def __call__(self, **options):
        """Style factory with cache to minimize styles in document.

        # Set font face
        name = 'Times New Roman'

        # Make the font bold, underlined and italic
        bold = True
        underline = True
        italic = True

        # To strike through the text
        struck_out = True

        # To add green color to the font object
        colour_index = 3
        # Other colors: 1- White, 2- Red, 3- Green, 4- Blue,
                        5- Yellow, 6- Magenta, 7- Cyan

        # To outline the font
        outline = True

        # Set borders
        borders = {'top': 1, 'right': 1, 'bottom': 1, 'left': 1}

        # Set alignment
        align = {'horz': '^', 'vert': '>'} #  See ALIGNS for shortcuts
        """

        key = str(options)
        if key in self._cache:
            return self._cache[key]

        style = xl.XFStyle()

        if 'borders' in options:
            borders = xl.Borders()
            for attr, value in options['borders'].iteritems():
                setattr(borders, attr, value)
            style.borders = borders
            del options['borders']

        if 'align' in options:
            align = xl.Alignment()
            for attr, value in options['align'].iteritems():
                setattr(align, attr, ALIGNS.get(value, value))
            style.alignment = align
            del options['align']

        font = xl.Font()
        for attr, value in options.iteritems():
            setattr(font if attr in FONT_ATTRS else style, attr, value)

        style.font = font

        self._cache[key] = style
        return style

style = StyleFactory()


class Table(object):
    """pyExcelerator helper to render data with cooked styles"""

    title_style = style(height=220, borders={'bottom': 2})
    header_style = style(bold=True)
    total_style = style(
        bold = True,
        borders = {'top': 2},
        align = {'horz': '>'}
    )

    def __init__(self, title="", *fields):
        self.title = title
        self.fields = fields

    def render(self, data, **context):
        title = self.title % context \
                if '%' in self.title \
                else self.title

        doc = xl.Workbook()
        sheet = doc.add_sheet(title.split()[-1])

        cursor = 0

        if title:
            sheet.write_merge(
                cursor, 0, 0, len(self.fields) - 1,
                title, self.title_style)
            cursor += 1

        for pos, header in enumerate(x[1] for x in self.fields):
            sheet.col(pos).width = 0x0d00 + 500
            sheet.write(cursor, pos, header, self.header_style)
        cursor += 1
        data_start = cursor + 1

        sheet.panes_frozen = True
        sheet.horz_split_pos = cursor

        for row in data:
            for pos, (key, _, cast) in enumerate(self.fields):
                value = cast(row[key])
                sheet.write(
                    cursor, pos, value,
                    style(num_format_str = STYLES[cast]))
            cursor += 1

        data_end = cursor

        for pos, cast in enumerate(x[2] for x in self.fields):
            if pos == 0:
                sheet.write(cursor, 0, "Total:", self.total_style)
                continue
            if cast in (int, float):
                func = xl.Formula(
                    'SUM({0}{1}:{0}{2})'.format(
                    chr(65 + pos), data_start, data_end))

                sheet.write(cursor, pos, func, self.total_style)

        return doc


TABLE_WEEKLY = Table("Weekly stats for %(start)s..%(finish)s",
    ('recipient', "Recipient", str),
    ('count', "Count", int),
    ('amount', "Amount", float),
    ('avg_amount', "Avg. Amount", float),
    ('value', "Value", float),
    ('avg_value', "Avg. Value", float),
    ('commission', "Commission", float)
)


TABLE_DAILY = Table("Daily stats for %(start)s",
    ('recipient', "Recipient", str),
    ('count', "Count", int),
    ('amount', "Amount", float),
    ('value', "Value", float),
    ('commission', "Commission", float)
)