improve performance of team monthly chart

The old way this was done is doing one SQL query per month. This figures
things out in a single pass. I'm seeing the page from ~10s to render
down to ~2s. Additionally - the old code wasn't computing its metrics
quite right. I've validated most of this against "team-kernel" and it
looks accurate.

Change-Id: Ibe22a62e8468e1c0664053692b00d3d22eb252ce

2 files changed, 72 insertions, 70 deletions

diff --git a/apps/patchmetrics/models.py b/apps/patchmetrics/models.py
index d5ef494..22dcc72 100644
--- a/apps/patchmetrics/models.py
+++ b/apps/patchmetrics/models.py
@@ -5,6 +5,7 @@ from dateutil import relativedelta
 
 from django.contrib.auth.models import User
 from django.db import connection, models
+from django.db.models import Count
 
 from patchwork.models import Patch, Person, Project, State
 
@@ -16,6 +17,20 @@ PROJECTS_TO_IGNORE_IN_STATS = [
 GERRIT_ROOT = 'https://review.source.android.com/'
 
 
+def x_months_ago(x):
+    '''find the starting date of X months ago
+    if x=3 and its may6, then this would treat may as short month, and return
+    march1
+    '''
+    assert x > 0
+    # get first day of this month
+    month = datetime.now().replace(
+        day=1, hour=0, minute=0, second=0, microsecond=0)
+    for x in range(x-1):
+        month = month.replace(day=1) - timedelta(days=1)
+    return month.replace(day=1)
+
+
 class GerritChange(models.Model):
     # We use gerrit's ID as the primary key.
     gerrit_id = models.IntegerField(primary_key=True)
@@ -219,7 +234,6 @@ def get_patches_accepted_by_age_range(age_increment, team=None):
             """
         replacements['team_id'] = team.id
 
-
     sql += " GROUP BY age;"
     cursor.execute(sql, replacements)
     return cursor.fetchall()
@@ -242,24 +256,6 @@ def get_number_of_patches_waiting_for_feedback():
     return total_count, more_than_30_days_old_count
 
 
-def get_patches_submitted_and_accepted(start_date, end_date):
-    """Return two lists of patches: submitted and accepted.
-
-    The first list contains all patches submitted on the given period, in any
-    state other than Superseded.
-
-    The second list contains the patches that have been accepted on the given
-    period, regardless of when they were submitted.
-    """
-    submitted = LinaroPatch.objects.filter(
-        date__gte=start_date, date__lte=end_date)
-    accepted = LinaroPatch.objects.filter(
-        date_last_state_change__gte=start_date,
-        date_last_state_change__lte=end_date,
-        state=State.objects.get(name='Accepted'))
-    return submitted, accepted
-
-
 def get_distinct_author_count(year, month):
     """Return the number of authors of patches submitted on the given month.
 
@@ -311,31 +307,6 @@ def get_patches_per_month_up_to(year, month, count_function, limit=None):
     return submitted, accepted
 
 
-def get_overall_patch_count(start_date, end_date):
-    """Return the number of submitted/accepted patches for the given period.
-    """
-    submitted, accepted = get_patches_submitted_and_accepted(
-        start_date, end_date)
-    return submitted.count(), accepted.count()
-
-
-def get_submitted_patch_counts_for_month(year, month):
-    """Return the number of patches submitted and accepted on the given month.
-
-    The return is a 2-tuple of integers where the first element is the number
-    of non-Superseded patches submitted on the given month and the second is
-    the number of patches that were submitted and accepted on the same month.
-    """
-    start_date = datetime(year=year, month=month, day=1)
-    end_date = (start_date + relativedelta.relativedelta(months=1) -
-                timedelta(seconds=1))
-    submitted, accepted = get_patches_submitted_and_accepted(
-        start_date, end_date)
-    total = submitted.count()
-    accepted = accepted.filter(date__gte=start_date, date__lte=end_date)
-    return total, accepted.count()
-
-
 def get_number_of_old_patches_accepted_on_month(year, month):
     """Return the number of patches submitted on previous months that were
     accepted on the given month.
@@ -355,6 +326,40 @@ def get_oldest_patches_waiting_for_feedback():
     return patches.order_by('date')[:10]
 
 
+def patch_count_by_month(patch_set, num_months, state=None):
+    # allow us to group patches by month
+    start = x_months_ago(num_months)
+    truncate_date = connection.ops.date_trunc_sql('month', 'date')
+    if state:
+        patch_set = patch_set.filter(state=state)
+    return patch_set.filter(
+        date__gte=start
+    ).extra(
+        {'month': truncate_date}
+    ).values('month').annotate(Count('pk')).order_by('month')
+
+
+def patch_month_metrics(patch_set, num_months=6):
+    s = State.objects.get(name='Accepted')
+    submit = patch_count_by_month(patch_set, num_months)
+    accept = patch_count_by_month(patch_set, num_months, s)
+
+    metrics = []
+    for x in reversed(range(num_months)):
+        metrics.append([x_months_ago(x+1), 0, 0])
+    for metric in metrics:
+        for x in submit:
+            if x['month'] == metric[0]:
+                metric[1] += x['pk__count']
+        for x in accept:
+            if x['month'] == metric[0]:
+                metric[2] += x['pk__count']
+
+    # convert timestamp from metrics to something flot friendly
+    return [(calendar.timegm(x[0].timetuple()) * 1000, x[1], x[2])
+            for x in metrics]
+
+
 # This is a hack to make everybody able to edit all projects without having to
 # change the Project class directly as that wouldn't be accepted upstream.
 # XXX: Unfortunately, this hack interferes with the test suite, breaking 2
diff --git a/apps/patchmetrics/views.py b/apps/patchmetrics/views.py
index 4a658e1..4c4d470 100644
--- a/apps/patchmetrics/views.py
+++ b/apps/patchmetrics/views.py
@@ -17,9 +17,8 @@ from patchmetrics.models import (
     get_patches_accepted_by_age_range,
     get_patches_per_month_up_to,
     get_patches_waiting_for_feedback_by_age_range,
-    get_submitted_patch_counts_for_month,
     get_oldest_patches_waiting_for_feedback,
-    get_overall_patch_count,
+    patch_month_metrics,
     LinaroPatch,
     PROJECTS_TO_IGNORE_IN_STATS,
     Team)
@@ -39,14 +38,16 @@ def project(request, project_id):
         request, project, 'patchmetrics.views.project',
         view_args={'project_id': project.linkname},
         should_show_move_form=True)
-    now = datetime.now()
 
     total, accepted = project.get_patch_count()
     context['project'] = project
     context['total_patches'] = total
     context['accepted_patches'] = accepted
-    context['patches_per_month_chart'] = get_patches_per_month_chart(
-        project, now.year, now.month)
+    patches = LinaroPatch.objects.filter(project=project)
+
+    ticks, submitted, accepted = get_patches_per_month(patches)
+    context['patches_per_month_chart'] = generate_patches_per_month_chart(
+        ticks, submitted, accepted)
 
     return render_to_response('patchmetrics/project.html', context)
 
@@ -68,14 +69,15 @@ def team(request, team_id):
         request, None, 'patchmetrics.views.team',
         view_args={'team_id': team.name}, patches=patches)
 
-    now = datetime.now()
     submitted, accepted = team.get_patch_count()
     context['total_submitted'] = submitted
     context['total_accepted'] = accepted
     context['team'] = team
     context['members'] = sorted(members, key=lambda member: member[1])
-    context['patches_per_month_chart'] = get_patches_per_month_chart(
-        team, now.year, now.month)
+    ticks, submitted, accepted = get_patches_per_month(team.patches)
+    context['patches_per_month_chart'] = generate_patches_per_month_chart(
+        ticks, submitted, accepted)
+
     age_increment = 7
     accepted = get_patches_accepted_by_age_range(
         age_increment=age_increment, team=team)
@@ -138,7 +140,8 @@ def frontpage(request):
     one_month_ago = datetime.now() - relativedelta.relativedelta(months=1)
     year = one_month_ago.year
     month = one_month_ago.month
-    total, accepted = get_submitted_patch_counts_for_month(year, month)
+    ticks, submitted, accepted = get_patches_per_month(
+        LinaroPatch.objects.all())
     total_waiting, old_waiting = get_number_of_patches_waiting_for_feedback()
     projects = Project.objects.exclude(
         linkname__in=PROJECTS_TO_IGNORE_IN_STATS)
@@ -151,14 +154,14 @@ def frontpage(request):
     context['distinct_monthly_authors'] = get_distinct_author_count(
         year, month)
     context['total_accepted'] = get_number_of_patches_accepted()
-    context['submitted_on_month'] = total
-    context['submitted_and_accepted_on_month'] = accepted
+    context['submitted_on_month'] = submitted[-1][1]
+    context['submitted_and_accepted_on_month'] = accepted[-1][1]
     context['waiting_for_feedback'] = total_waiting
     context['more_than_30_days_old_waiting_for_feedback'] = old_waiting
     context['old_patches_accepted_on_month'] = (
         get_number_of_old_patches_accepted_on_month(year, month))
-    context['patches_per_month_chart'] = get_overall_patches_per_month_chart(
-        year, month)
+    context['patches_per_month_chart'] = generate_patches_per_month_chart(
+        ticks, submitted, accepted)
     # days
     age_increment = 15
     new = get_patches_waiting_for_feedback_by_age_range(age_increment)
@@ -223,13 +226,6 @@ def get_patch_age_distribution_chart(data, age_increment, div_id, color=0):
     return html
 
 
-def get_overall_patches_per_month_chart(year, month):
-    submitted, accepted = get_patches_per_month_up_to(
-        year, month, get_overall_patch_count, limit=8)
-    ticks = [item[0] for item in submitted]
-    return generate_patches_per_month_chart(ticks, submitted, accepted)
-
-
 def generate_patches_per_month_chart(ticks, submitted, accepted, width=350,
                                      height=250):
     html = """
@@ -256,12 +252,13 @@ def generate_patches_per_month_chart(ticks, submitted, accepted, width=350,
     return html
 
 
-def get_patches_per_month_chart(project_or_team, year, month):
-    submitted, accepted = get_patches_per_month_up_to(
-        year, month, project_or_team.get_patch_count, limit=8)
-    ticks = [item[0] for item in submitted]
-    return generate_patches_per_month_chart(
-        ticks, submitted, accepted, width=500, height=250)
+def get_patches_per_month(patch_set):
+    metrics = patch_month_metrics(patch_set)
+    ticks = [x[0] for x in metrics]
+    submitted = [[x[0],  x[1]] for x in metrics]
+    accepted = [[x[0], x[2]] for x in metrics]
+    return ticks, submitted, accepted
+    return generate_patches_per_month_chart(ticks, submitted, accepted)
 
 
 def non_linaro_patches(request):