Merge branch 'fusion/alban' into qsjps

8 files changed, 62 insertions, 19 deletions

diff --git a/README.md b/README.md
index 7dc9d5c..f94cbed 100644
--- a/README.md
+++ b/README.md
@@ -20,11 +20,11 @@ Pour installer celcatsanitizer, il est possible d’utiliser
 
 Pour tester celcatsanitizer, il est recommandé d’utiliser
 [SQLite](https://www.sqlite.org/) ou
-[PostgreSQL](https://www.postgresql.org/?&).
+[PostgreSQL](https://www.postgresql.org/).
 
 Pour la production, il est recommandé d’utiliser PostgreSQL (avec le
 driver
-[psycopg2](http://initd.org/psycopg/docs/install.html#binary-install-from-pypi))
+[psycopg2](http://initd.org/psycopg/docs/install.html#binary-install-from-pypi)
 et de mettre le tout dans un environnement virtuel.
 
 Aucun autre SGBD n’a été testé, mais depuis la version 0.8.0,
diff --git a/models.py b/models.py
index 538d64c..f7450dc 100644
--- a/models.py
+++ b/models.py
@@ -16,7 +16,7 @@
 from functools import reduce
 
 from django.db import models
-from django.db.models import Manager, Q
+from django.db.models import Count, Manager, OuterRef, Q, Subquery
 from django.db.models.functions import ExtractWeek, ExtractYear
 from django.utils import timezone
 from django.utils.text import slugify
@@ -31,6 +31,7 @@ class SlugModel(models.Model):
 
         super(SlugModel, self).save(*args, **kwargs)
 
+
     class Meta:
         abstract = True
 
@@ -104,6 +105,13 @@ class GroupManager(Manager):
                                           mention=group.mention,
                                           source=group.source)
 
+    def get_relevant_groups(self, start, **criteria):
+        courses = Course.objects.filter(groups=OuterRef("pk"), begin__gte=start) \
+                                .only("pk")[:1]
+        return self.get_queryset().annotate(c=Subquery(courses,
+                                                       output_field=models.IntegerField())) \
+                                  .filter(c__isnull=False, **criteria).order_by("name")
+
 
 class Group(SlugModel):
     objects = GroupManager()
@@ -213,7 +221,8 @@ class CourseManager(Manager):
                    .annotate(year=ExtractYear("begin"),
                              week=ExtractWeek("begin")) \
                    .values("groups__mention", "groups__semester",
-                           "groups__subgroup", "year", "week")
+                           "groups__subgroup", "year", "week") \
+                   .annotate(c=Count("*"))
 
 
 class Course(models.Model):
diff --git a/templates/group_list.html b/templates/group_list.html
index 7fe1fe8..5f53cb4 100644
--- a/templates/group_list.html
+++ b/templates/group_list.html
@@ -10,4 +10,5 @@
         <li><a class="text"{% if group.weeks is not None %} href="{% if timetable %}{% url "timetable" timetable.year.slug timetable.slug group.slug %}{% else %}{% url "room-timetable" group.slug %}{% endif %}"{% endif %}>{{ group }}</a> &mdash; {% for week in group.weeks %}<a href="{% if timetable %}{% url "timetable" timetable.year.slug timetable.slug group.slug week.year week|dt_week %}{% else %}{% url "room-timetable" group.slug week.year week|dt_week %}{% endif %}">{{ week|dt_prettyprint }}</a> {% if not forloop.last %}&ndash; {% endif %}{% empty %}<em>aucun cours dans le mois à venir</em>{% endfor %}</li>
         {% endfor %}
       </ul>
+      {% if timetable %}<a href="{% url "mentions" timetable.year.slug %}">Retour à la liste des mentions</a>{% else %}<a href="{% url "index" %}">Retour à la liste des années</a>{% endif %}
 {% endblock %}
diff --git a/templates/index.html b/templates/index.html
index 081f663..007ab35 100644
--- a/templates/index.html
+++ b/templates/index.html
@@ -21,6 +21,7 @@
         <p><em>Aucun emploi du temps à afficher</em></p>
         {% endfor %}
       </ul>
+      {% if year %}<a href="{% url "index" %}">Retour à la liste des années</a>{% else %}<a href="{% url "rooms" %}">Emploi du temps des salles</a>{% endif %}
       {% endblock %}
     </div>
     <footer>
diff --git a/templates/qsjps_form.html b/templates/qsjps_form.html
index b68143f..280a7ad 100644
--- a/templates/qsjps_form.html
+++ b/templates/qsjps_form.html
@@ -15,4 +15,5 @@
           <tr><th></th><td><input type="submit" value="Trouver une salle" /></td></tr>
         </table>
       </form>
+      <a href="{% url "rooms" %}">Retour à la liste des salles</a>
 {% endblock %}
diff --git a/templates/timetable.html b/templates/timetable.html
index 8fafeed..5848e04 100644
--- a/templates/timetable.html
+++ b/templates/timetable.html
@@ -1,4 +1,5 @@
 {% extends "index.html" %}
+{% load dt_week %}
 
 {% block head %}{% if group_mode %}
     <meta name="description" content="Emploi du temps du groupe {{ group }} &ndash; Semaine {{ week }}" />
@@ -18,4 +19,9 @@
         {% if last_update %}Dernière mise à jour le {{ last_update|date:"l j F o" }} à {{ last_update|date:"H:i" }}{% endif %}
       </p>
       {% include "timetable_common.html" %}
-      {% if group_mode %}<p class="subscribe"><a href="{% url "calendars" timetable.year.slug timetable.slug group.slug %}">ICS</a> &ndash; <a href="{% url "rss" timetable.year.slug timetable.slug group.slug %}">RSS</a> &ndash; <a href="{% url "atom" timetable.year.slug timetable.slug group.slug %}">Atom</a></p>{% endif %}{% endblock %}
+      <p class="subscribe">
+        {% if group_mode %}<a href="{% url "groups" timetable.year.slug timetable.slug %}">Retour à la liste des groupes</a>{% else %}<a href="{% url "rooms" %}">Retour à la liste des salles</a>{% endif %} &ndash;
+        {% if last_week is not None %}<a href="{% if group_mode %}{% url "timetable" timetable.year.slug timetable.slug group.slug last_week.year last_week|dt_week %}{% else %}{% url "room-timetable" group.slug last_week.year last_week|dt_week %}{% endif %}">Semaine {{ last_week|dt_week }}</a>{% if next_week is not None %} &ndash; {% endif %}{% endif %}{% if next_week is not None %}<a href="{% if group_mode %}{% url "timetable" timetable.year.slug timetable.slug group.slug next_week.year next_week|dt_week %}{% else %}{% url "room-timetable" group.slug next_week.year next_week|dt_week %}{% endif %}">Semaine {{ next_week|dt_week }}</a>{% endif %}
+        {% if group_mode %}{% if last_week is not None or next_week is not None %}<br />{% endif %}
+        <a href="{% url "calendars" timetable.year.slug timetable.slug group.slug %}">ICS</a> &ndash; <a href="{% url "rss" timetable.year.slug timetable.slug group.slug %}">RSS</a> &ndash; <a href="{% url "atom" timetable.year.slug timetable.slug group.slug %}">Atom</a>{% endif %}
+      </p>{% endblock %}
diff --git a/templates/timetable_common.html b/templates/timetable_common.html
index 21300f1..6e59322 100644
--- a/templates/timetable_common.html
+++ b/templates/timetable_common.html
@@ -4,7 +4,7 @@
         <h3>{% filter title %}{{ day.0.begin|date:"l j F o" }}{% endfilter %} &ndash; de {{ day.0.begin|date:"H:i" }} à {% with day|last as last %}{{ last.end|date:"H:i" }}{% endwith %}</h3>
         <ul>{% for course in day %}
           <li class="course">
-            <b>{{ course }}</b>{% if course.type %} ({{ course.type }}){% endif %}, de {{ course.begin|date:"H:i" }} à {{ course.end|date:"H:i" }}{% if course.rooms.all|length > 0 %}<br />
+            <b>{{ course }}</b>{% if course.type %} ({{ course.type }}){% endif %}, de {{ course.begin|date:"H:i" }} à {{ course.end|date:"H:i" }}{% if group_mode and course.rooms.all|length > 0 or not group_mode and course.groups.all|length > 0 %}<br />
             <em>{% if group_mode %}{{ course.rooms.all|format_rooms }}{% else %}{{ course.groups.all|join:", " }}{% endif %}</em>{% endif %}{% if course.notes %}<br />
             <small>Remarques : {{ course.notes|linebreaksbr }}</small>{% endif %}
           </li>{% endfor %}
diff --git a/views.py b/views.py
index 94de177..bdbbdc2 100644
--- a/views.py
+++ b/views.py
@@ -15,6 +15,7 @@
 
 import datetime
 
+from django.db import connection
 from django.db.models import Count, Max
 from django.db.models.functions import ExtractWeek, ExtractYear, Length
 from django.http import Http404
@@ -29,6 +30,9 @@ from .utils import get_current_week, get_current_or_next_week, get_week, \
 
 import edt
 
+if connection.vendor == "postgresql":
+    from django.contrib.postgres.aggregates import ArrayAgg
+    from django.db.models.expressions import RawSQL
 
 def index(request):
     years = Year.objects.order_by("name")
@@ -44,16 +48,13 @@ def mention_list(request, year_slug):
 
 
 def group_list(request, year_slug, timetable_slug):
-    timetable = get_object_or_404(Timetable, year__slug=year_slug,
-                                  slug=timetable_slug)
-    groups = Group.objects.filter(source=timetable.source, hidden=False) \
-                          .order_by("name")
+    timetable = get_object_or_404(Timetable, year__slug=year_slug, slug=timetable_slug)
 
     start, _ = get_week(*get_current_week())
     end = start + datetime.timedelta(weeks=4)
 
-    groups_weeks = Course.objects.get_weeks(begin__gte=start, begin__lt=end,
-                                            groups__in=groups)
+    groups = Group.objects.get_relevant_groups(start, source=timetable.source, hidden=False)
+    groups_weeks = Course.objects.get_weeks(begin__gte=start, begin__lt=end, groups__in=groups)
 
     for group in groups:
         for group_week in groups_weeks:
@@ -90,16 +91,22 @@ def timetable_common(request, obj, year=None, week=None, timetable=None):
     if not courses.exists() and provided_week:
         raise Http404
 
+    # Récupération des semaines suivantes et précédentes pour les
+    # afficher proprement dans l’emploi du temps
+    last_week = getattr(Course.objects.get_courses(obj, begin__lt=start).last(), "begin", None)
+    next_week = getattr(Course.objects.get_courses(obj, begin__gte=end).first(), "begin", None)
+
     last_update = courses.aggregate(Max("last_update"))["last_update__max"]
     grouped_courses = group_courses(courses)
 
-    return render(request, "timetable.html",
-                  {"group": obj, "courses": grouped_courses,
-                   "last_update": last_update, "year": year, "week": int(week),
-                   "is_old_timetable": is_old_timetable,
-                   "group_mode": isinstance(obj, Group),
-                   "timetable": timetable})
-
+    return render(request, "timetable.html", {"group": obj, "courses": grouped_courses,
+                                              "last_update": last_update,
+                                              "year": year, "week": int(week),
+                                              "last_week": last_week,
+                                              "next_week": next_week,
+                                              "is_old_timetable": is_old_timetable,
+                                              "group_mode": isinstance(obj, Group),
+                                              "timetable": timetable})
 
 def timetable(request, year_slug, timetable_slug, group_slug,
               year=None, week=None):
@@ -127,6 +134,24 @@ def rooms(request):
     start, _ = get_week(*get_current_week())
     end = start + datetime.timedelta(weeks=4)
 
+    if connection.vendor == "postgresql":
+        # Si le SGBD est PostgreSQL, on utilise une requête à base de
+        # ArrayAgg. Elle présente l’avantage d’être plus rapide que la
+        # requête « généraliste » et de ne pas nécessiter de
+        # traitement après.  On récupère chaque salle ayant un cours
+        # dans le mois à venir. Pour chacun de ses cours, on ne
+        # récupère que le premier jour de la semaine, et si jamais ce
+        # jour n’est pas déjà dans la liste des semaines de cours
+        # (« weeks »), on l’y rajoute.
+        rooms = Room.objects.filter(course__begin__gte=start,
+                                    course__begin__lt=end) \
+                            .order_by("name") \
+                            .annotate(weeks=ArrayAgg(
+                                RawSQL("date_trunc('week', edt_course.begin)",
+                                       []), distinct=True))
+
+        return render(request, "group_list.html", {"groups": rooms})
+
     # Récupération des salles et de toutes les semaines où elles sont
     # concernées.
     # Cette requête associe chaque salle à toutes les semaines où un