Source

django-spotnet / spotnet / stats / functions.py

from datetime import date, timedelta
from django.utils.translation import ugettext as _
from spotnet.models import Post
from spotnet.settings import CATEGORY_MAPPING


def posts_per_day():
    """Returns the number of posts for each day.

    Returns an iterable of tuples (year, day-of-year, number-of-posts)
    for each day the database has posts.
    """

    from django.db import connection, transaction
    cursor = connection.cursor()

    if cursor.db.vendor == 'sqlite':
        query = """
SELECT
  django_extract('year',  "posted") AS year,
  django_extract('month', "posted") AS month,
  django_extract('day',   "posted") AS day,
  COUNT(*) AS posts
FROM %s
GROUP BY year, month, day
ORDER BY year, month, day""" % (Post._meta.db_table, )
    elif cursor.db.vendor == 'mysql':
        query = """
SELECT
  YEAR(posted)  AS year,
  MONTH(posted) AS month,
  DAY(posted)   AS day,
  COUNT(*) AS posts
FROM %s
GROUP BY year, month, day
ORDER BY year, month, day""" % (Post._meta.db_table, )
    elif cursor.db.vendor == 'postgresql':
        query = """
SELECT
  EXTRACT(YEAR  FROM posted) AS year,
  EXTRACT(MONTH FROM posted) AS month,
  EXTRACT(DAY   FROM posted) AS day,
  COUNT(*) AS posts
FROM %s
GROUP BY year, month, day
ORDER BY year, month, day""" % (Post._meta.db_table, )
    else:
        raise Exception("Unsupported database type")

    cursor.execute(query)
    data = cursor.fetchall()

    date_start = date(*data[0][0:3])
    date_end  = date(*data[-1][0:3])
    dayspan = (date_end - date_start).days
    one_day = timedelta(days=1)

    postings = []
    total_posts = 0
    prev_date = date_start - one_day
    for year, month, day, posts in data:
        cur_date = date(year, month, day)
        while (cur_date - prev_date).days > 1:
            postings.append(0)
            prev_date += one_day
        postings.append(posts)
        total_posts += posts
        prev_date = cur_date

    assert len(postings) == dayspan + 1

    return postings, date_start, date_end, total_posts, dayspan


def posts_per_category():
    from django.db import connection, transaction
    cursor = connection.cursor()

    if cursor.db.vendor == 'postgresql':
        query = """
SELECT
  category,
  COUNT(*) AS posts,
  SUM(size) AS size
FROM %s
GROUP BY category
ORDER BY posts DESC""" % (Post._meta.db_table, )
    else:
        raise Exception("Unsupported database type")

    cursor.execute(query)
    data = cursor.fetchall()

    results = []
    unknown_category = (0, 0)
    for cat, posts, size in data:
        if cat is None:
            assert unknown_category == (0, 0)
            unknown_category = (posts, size)
        else:
            results.append((CATEGORY_MAPPING.get(cat, _('Invalid')), posts, size))

    results.append((_('Unknown'), ) + unknown_category)

    return results