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>

1 files changed, 37 insertions, 0 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: