Rapports trie, WIP et optimisation de requete SQL

diff --git a/project/lib.py b/project/lib.py
index d1b1809..a1a5481 100644 (file)
--- a/project/lib.py
+++ b/project/lib.py
@@ -46,16 +46,3 @@ def safe_create_groupe(name=None, id=None):
         return None
     return grp
 
-def multikeysort(items, columns):
-    """ Trie un une liste de tableau à la Django (-column pour descandant, column pour ascendant)
-    http://stackoverflow.com/questions/1143671/python-sorting-list-of-dictionaries-by-multiple-keys
-    """
-    comparers = [ ((itemgetter(col[1:].strip()), -1) if col.startswith('-') else (itemgetter(col.strip()), 1)) for col in columns]  
-    def comparer(left, right):
-        for fn, mult in comparers:
-            result = cmp(fn(left), fn(right))
-            if result:
-                return mult * result
-        else:
-            return 0
-    return sorted(items, cmp=comparer)

diff --git a/project/rh/templates/rh/rapports/contrats.html b/project/rh/templates/rh/rapports/contrats.html
index e1eda23..a8ceaec 100644 (file)
--- a/project/rh/templates/rh/rapports/contrats.html
+++ b/project/rh/templates/rh/rapports/contrats.html
@@ -20,13 +20,7 @@
 <table id="result_list">
 <thead>
 <tr>
-       <th># de l'employé</th>
-       <th>Nom</th>
-       <th>Prénom</th>
-       <th>Poste</th>
-       <th>Type de contrat</th>
-       <th>Date début</th>
-       <th>Date fin</th>
+    {% table_header headers %}
 </tr>
 </thead>
 {% spaceless %}{% for contrat in contrats %}

diff --git a/project/rh/views.py b/project/rh/views.py
index 6ac5724..ffccb9d 100644 (file)
--- a/project/rh/views.py
+++ b/project/rh/views.py
@@ -16,7 +16,6 @@ from rh import models as rh
 from rh.lib import calc_remun
 from rh.decorators import drh_or_admin_required
 from rh.templatetags.rapports import SortHeaders
-from project.lib import multikeysort
 
 # pas de reference a DAE devrait etre refactorisé
 from dae.utils import get_employe_from_user
@@ -106,50 +105,20 @@ def rapports_poste(request):
             comble = value
             del lookup_params[key]
 
-    postes = rh.Poste.actifs.select_related('implantation').all()
-    postes = postes.filter(**lookup_params)
-
-    poste_ids = set([p.pk for p in postes])
-
-    dossiers = rh.Dossier.objects.select_related('poste', 'employe').filter(poste__in=poste_ids)
-
-    employes = dict((d.poste.id, d.employe) for d in dossiers)
-
-    out = []
-
-    for p in postes:
-        employe = employes.get(p.id)
-
-        if comble == 'c' and not employe:
-            continue
-
-        if comble == 'n' and employe:
-            continue
-
-        line = {}
-        out.append(line)
-
-        line['id'] = p.id
-        line['nom'] = p.nom
-        line['implantation'] = p.implantation.nom
-        if employe:
-            line['employe_id'] = employe.id
-            line['employe_nom'] = employe.nom
-            line['employe_prenom'] = employe.prenom
-        else:
-            line['employe_id'] = None
-            line['employe_nom'] = None
-            line['employe_prenom'] = None
+    #postes = rh.Poste.actifs.select_related('implantation', 'dossier').all()
+    postes = rh.Poste.actifs.select_related('implantation') \
+            .extra(select={'employe_id':'select group_concat(employe) from rh_dossier where poste=rh_poste.id and rh_dossier.actif = 1'}) \
+            .extra(select={'employe_nom':'select group_concat(rh_employe.nom) from rh_dossier inner join rh_employe on rh_dossier.employe = rh_employe.id where poste=rh_poste.id and rh_dossier.actif = 1'}) \
+            .extra(select={'employe_prenom':'select group_concat(rh_employe.prenom) from rh_dossier inner join rh_employe on rh_dossier.employe = rh_employe.id where poste=rh_poste.id and rh_dossier.actif = 1'})
 
+    postes = postes.filter(**lookup_params)
     if 'o' in request.GET:
-        try:
-            out = multikeysort(out, ["%s%s" % ('-' if 'ot' in request.GET and request.GET['ot'] == "desc" else '', request.GET['o'])])
-        except KeyError:
-            raise Http404
+        postes = postes.order_by("%s%s" % ('-' if 'ot' in request.GET and request.GET['ot'] == "desc" else '', request.GET['o']))
+
     headers = [
         ("id", u"# du poste"),
         ("nom", u"Nom du poste"),
-        ("implantation", u"Implantation"),
+        ("implantation__id", u"Implantation"),
         ("employe_id", u"# de l'employé"),
         ("employe_nom", u"Nom"),
         ("employe_prenom", u"Prénom"),
@@ -157,9 +126,9 @@ def rapports_poste(request):
     h = SortHeaders(request, headers, order_field_type="ot", order_field="o")
     c = {
         'title': 'Rapport des postes',
-        'postes': out,
-        'count': len(out),
-        'headers': list(h.headers())
+        'postes': postes,
+        'count': len(postes),
+        'headers': list(h.headers()),
     }
 
     return render_to_response('rh/rapports/postes.html', c, RequestContext(request))
@@ -169,6 +138,10 @@ def rapports_poste(request):
 def rapports_contrat(request):
 
     lookup_params = dict(request.GET.items())
+    if 'ot' in lookup_params:
+        del lookup_params['ot']
+    if 'o' in lookup_params:
+        del lookup_params['o']
 
     for key, value in lookup_params.items():
         if not isinstance(key, str):
@@ -177,16 +150,30 @@ def rapports_contrat(request):
             del lookup_params[key]
             lookup_params[smart_str(key)] = value
 
-    contrats = rh.Contrat.objects.select_related('dossier', 'dossier__poste', 'dossier__poste__implantation', 'type_contrat', 'dossier__employe').all()
+    contrats = rh.Contrat.objects.select_related('dossier', 'dossier__poste', 'dossier__poste__implantation', 'type_contrat', 'dossier__employe')
     contrats = contrats.filter(**lookup_params)
+    if 'o' in request.GET:
+        contrats = contrats.order_by("%s%s" % ('-' if 'ot' in request.GET and request.GET['ot'] == "desc" else '', request.GET['o']))
 
     employes = set([c.dossier.employe_id for c in contrats])
 
+    headers = [
+        ("dossier__employe__id", u"# de l'employé"),
+        ("dossier__employe__nom", u"Nom"),
+        ("dossier__employe__prenom", u"Prénom"),
+        ("type_contrat__nom", u"Poste"),
+        ("type_contrat__nom", u"Type de contrat"),
+        ("date_debut", u"Date début"),
+        ("date_fin", u"Date fin"),
+    ]
+    h = SortHeaders(request, headers, order_field_type="ot", order_field="o")
+
     c = {
         'title': 'Rapport des contrats',
         'contrats': contrats,
         'count': len(contrats),
         'count_employe' : len(employes),
+        'headers': list(h.headers()),
     }
 
     return render_to_response('rh/rapports/contrats.html', c, RequestContext(request))