Restore flagged package count by maintainer in dashboard

We need to do a little dropping into SQL to accomplish this, but it isn't
all that bad to actually do and we can do the whole thing in one query.

Signed-off-by: Dan McGee <dan@archlinux.org>

3 files changed, 44 insertions, 12 deletions

diff --git a/devel/utils.py b/devel/utils.py
new file mode 100644
index 00000000..abfdabe5
--- /dev/null
+++ b/devel/utils.py
@@ -0,0 +1,37 @@
+from django.contrib.auth.models import User
+from django.db import connection
+
+from main.utils import cache_function
+from packages.models import PackageRelation
+
+@cache_function(300)
+def get_annotated_maintainers():
+    maintainers = User.objects.filter(is_active=True).order_by(
+            'first_name', 'last_name')
+
+    # annotate the maintainers with # of maintained and flagged packages
+    pkg_count_sql = """
+SELECT pr.user_id, COUNT(*), COUNT(p.flag_date)
+    FROM packages_packagerelation pr
+    JOIN packages p
+    ON pr.pkgbase = p.pkgbase
+    WHERE pr.type = %s
+    GROUP BY pr.user_id
+"""
+    cursor = connection.cursor()
+    cursor.execute(pkg_count_sql, [PackageRelation.MAINTAINER])
+    results = cursor.fetchall()
+
+    pkg_count = {}
+    flag_count = {}
+    for k, total, flagged in results:
+        pkg_count[k] = total
+        flag_count[k] = flagged
+
+    for m in maintainers:
+        m.package_count = pkg_count.get(m.id, 0)
+        m.flagged_count = flag_count.get(m.id, 0)
+
+    return maintainers
+
+# vim: set ts=4 sw=4 et:
diff --git a/devel/views.py b/devel/views.py
index eea90e33..37037670 100644
--- a/devel/views.py
+++ b/devel/views.py
@@ -12,6 +12,7 @@ from main.models import Arch, Repo
 from main.models import UserProfile
 from mirrors.models import Mirror
 from packages.models import PackageRelation
+from .utils import get_annotated_maintainers
 
 import random
 from string import ascii_letters, digits
@@ -28,8 +29,8 @@ def index(request):
             'pkg', 'pkg__arch', 'pkg__repo').filter(complete=False)
     todopkgs = todopkgs.filter(pkg__pkgbase__in=inner_q).order_by(
             'list__name', 'pkg__pkgname')
-    maintainers = User.objects.filter(is_active=True).order_by(
-            'first_name', 'last_name')
+
+    maintainers = get_annotated_maintainers()
 
     page_dict = {
             'todos': Todolist.objects.incomplete().order_by('-date_added'),
diff --git a/templates/devel/index.html b/templates/devel/index.html
index eccfbe3d..f285f7f6 100644
--- a/templates/devel/index.html
+++ b/templates/devel/index.html
@@ -168,7 +168,7 @@
         <thead>
             <tr>
                 <th class="key">Maintainer</th>
-                <th># Base Packages</th>
+                <th># Packages</th>
                 <th># Flagged</th>
             </tr>
         </thead>
@@ -178,10 +178,10 @@
                     <td>{{ maint.get_full_name }}</td>
                     <td><a href="/packages/?maintainer={{ maint.username }}"
                             title="View all packages maintained by {{ maint.get_full_name }}">
-                            <strong>{{ maint.package_relations.count }}</strong> base packages</a></td>
+                            <strong>{{ maint.package_count }}</strong> packages</a></td>
                     <td><a href="/packages/?maintainer={{ maint.username }}&amp;flagged=Flagged"
                             title="View all flagged packages maintained by {{ maint.get_full_name }}">
-                            Flagged packages</a></td>
+                            <strong>{{ maint.flagged_count }}</strong> packages</a></td>
                 </tr>
             {% endfor %}
         </tbody>
@@ -207,15 +207,9 @@ $(document).ready(function() {
         {widgets: ['zebra'], sortList: [[0,0], [1,0]]});
     $("#dash-todo:not(:has(tbody tr.empty))").tablesorter(
             {widgets: ['zebra'], sortList: [[1,1]]});
-    $("#stats-by-arch").tablesorter(
-        {widgets: ['zebra'], sortList: [[0,0]],
-        headers: { 1: { sorter: 'pkgcount' }, 2: { sorter: 'pkgcount' } } });
-    $("#stats-by-repo").tablesorter(
+    $("#stats-by-arch").add("#stats-by-repo").add("#stats-by-maintainer").tablesorter(
         {widgets: ['zebra'], sortList: [[0,0]],
         headers: { 1: { sorter: 'pkgcount' }, 2: { sorter: 'pkgcount' } } });
-    $("#stats-by-maintainer").tablesorter(
-        {widgets: ['zebra'], sortList: [[0,0]],
-        headers: { 1: { sorter: 'pkgcount' } } });
     $("h3.dash-stats").click(
         function(e) { $(this).next().toggle(); }
     );