Merge branch 'olivier' into test
authorOlivier Larchevêque <olivier.larcheveque@auf.org>
Thu, 29 Mar 2012 20:45:28 +0000 (16:45 -0400)
committerOlivier Larchevêque <olivier.larcheveque@auf.org>
Thu, 29 Mar 2012 20:45:28 +0000 (16:45 -0400)
12 files changed:
buildout.cfg
project/menu.py
project/rh/masse_salariale.py [new file with mode: 0644]
project/rh/ods.py [new file with mode: 0644]
project/rh/static/admin/js/jquery-stickytableheaders.js [new file with mode: 0644]
project/rh/templates/admin/rh/annee_select.html
project/rh/templates/admin/rh/annee_select.js [new file with mode: 0644]
project/rh/templates/rh/rapports/masse_salariale.html [new file with mode: 0644]
project/rh/templates/rh/rapports/remuneration.html [deleted file]
project/rh/templatetags/rapports.py
project/rh/urls.py
project/rh/views.py

index 635c775..b52b7f1 100644 (file)
@@ -43,6 +43,7 @@ eggs =
     django-qbe
     django-urldecorators
     pygraphviz
+    odfpy
 
 [versions]
 django-admin-tools = 0.4.1
@@ -62,6 +63,7 @@ django-sendfile = 0.2.1
 django-alphafilter = 0.5.3auf4
 django-urldecorators = 0.3
 pygraphviz = 0.36
+odfpy = 0.9.4
 
 [django]
 recipe = auf.recipe.django
index 6ddd311..b56f485 100644 (file)
@@ -50,6 +50,7 @@ class CustomMenu(Menu):
                                    items.MenuItem('Rapport des postes par implantation', reverse('rhr_postes_implantation')),
                                    #items.MenuItem('Modelisation des postes', reverse('rhr_postes_modelisation')),
                                    #items.MenuItem('Rapport hiérarchique des postes', reverse('rhr_postes_hierarchie')),
+                                   items.MenuItem('Rapport de masse salariale', reverse('rhr_masse_salariale')),
                                ]
                               ),
                 items.MenuItem('Organigrammes',
diff --git a/project/rh/masse_salariale.py b/project/rh/masse_salariale.py
new file mode 100644 (file)
index 0000000..f859766
--- /dev/null
@@ -0,0 +1,580 @@
+# -*- encoding: utf-8 -*-
+import time
+import datetime
+import csv
+import StringIO
+import itertools
+
+from django.db.models import Q
+
+from datamaster_modeles import models as ref
+import rh.ods as ods
+import rh.models as rh
+
+
+KEY_DATE_DEBUT = "debut"
+KEY_DATE_FIN = "fin"
+
+TYPE_REMUN_BSTG = (3,)
+TYPE_REMUN_MAD = (2,)
+TYPE_REMUN_BASE = (1,)
+TYPE_REMUN_FONC_RESP = (7, 8)
+TYPE_REMUN_EXPAT = (4,)
+TYPE_REMUN_LOGEMENT = (6,)
+TYPE_REMUN_TRANSP = (9,)
+TYPE_REMUN_13E = (18,)
+TYPE_PRIME_INTERIM = (19,)
+TYPE_REMUN_ALL_INDEMNITES = list(itertools.chain(*(TYPE_REMUN_BSTG,
+        TYPE_REMUN_MAD, TYPE_REMUN_BASE, TYPE_REMUN_FONC_RESP,
+        TYPE_REMUN_EXPAT, TYPE_REMUN_LOGEMENT, TYPE_REMUN_TRANSP,
+        TYPE_REMUN_13E, TYPE_PRIME_INTERIM)))
+TYPE_PRIME_INSTALLATION = (13,)
+TYPE_PRIME_DEMENAG = (15,)
+TYPE_PRIME_AVION = (14,)
+TYPE_PRIME_ALL = list(itertools.chain(
+            *(TYPE_PRIME_INSTALLATION, TYPE_PRIME_DEMENAG, TYPE_PRIME_AVION)
+        ))
+TYPE_CHARGE_PATRONALE = (17,)
+TYPE_CHARGE_ALL = list(itertools.chain(*(TYPE_CHARGE_PATRONALE,)))
+TYPE_NATURE_INDEMN = u"Indemnité"
+TYPE_NATURE_PAIEMENT = u"Accessoire"
+TYPE_NATURE_CHARGES = u"Charges"
+TYPE_NATURE_TRAITEMENT = u"Traitement"
+HEADER_SEPARATOR = ('sep', '', {'columnwidth': '0.4cm'})
+
+
+class MasseSalariale():
+    """ Rapport de la masse salariale. """
+
+    def __init__(self, date_debut, date_fin, custom_filter=None,
+            ne_pas_grouper=False):
+        """ date_debut: date de début pour les données temporelles
+        date_fin: idem
+        custom_filter: dictionnaire des paramètres à passer au queryset.
+        """
+        if not date_debut and not date_fin:
+            return
+
+        date_debut = datetime.date(
+                *time.strptime(date_debut, "%d-%m-%Y")[0:3]
+        )
+        date_fin = datetime.date(*time.strptime(date_fin, "%d-%m-%Y")[0:3])
+
+        rapport_date_delta = date_fin - date_debut
+        rapport_date_delta += datetime.timedelta(days=1)
+
+        self.annee = date_fin.year
+
+        self.devise_base = rh.Devise.objects.filter(code='EUR')[0]
+        self.taux_change = {}
+
+        q_range = self.build_qs("date_", date_debut, date_fin)
+        q_range_d = self.build_qs("dossier__date_", date_debut, date_fin)
+        remunerations = rh.Remuneration.objects.filter(q_range) \
+                .filter(q_range_d) \
+
+        if custom_filter:
+            remunerations = remunerations.filter(**custom_filter)
+
+        remunerations = remunerations.exclude(supprime=True) \
+                .select_related(
+                        "dossier", "dossier_employe", "dossier_poste", "type"
+                        )
+
+        contenu = {}
+
+        lineariser_dossiers = not ne_pas_grouper
+
+        for r in remunerations:
+            if lineariser_dossiers:
+                key = r.dossier.employe_id
+            else:
+                key = r.dossier_id
+
+            if key not in contenu:
+                contenu[key] = {
+                    'dossiers': set(),
+                    'remunerations': []
+                }
+            if lineariser_dossiers:
+                contenu[key]['remunerations'].append(r)
+            else:
+                if r.dossier_id == key:
+                    contenu[key]['remunerations'].append(r)
+            contenu[key]['dossiers'].add(r.dossier)
+
+        self.rapport = []
+
+        pays_list = {}
+        for pays in ref.Pays.objects.all():
+            pays_list[pays.id] = pays
+
+        valeurs_point_par_imp = \
+                dict(
+                    (v.implantation.id, v) for v in \
+                    rh.ValeurPoint.objects.filter(annee=self.annee).all()
+                )
+
+        self.headers = (
+                ('bureau', u"Bureau", {'columnwidth': '2cm'}),
+                ('pays', u"Pays", {'columnwidth': '3.5cm'}),
+                ('implantation', u"Implantation", {'columnwidth': '3cm'}),
+                ('valeur_point', u"Valeur du point",
+                    {'columnwidth': '2.3cm'}),
+                ('numero_employe', u"Numéro d'employé",
+                    {'columnwidth': '2.4cm'}),
+                ('nom', u"Nom", {'columnwidth': '5.4cm'}),
+                ('prenom', u"Prénom", {'columnwidth': '4.8cm'}),
+                ('type_de_poste', u"Type de poste", {'columnwidth': '2.7cm'}),
+                ('intitule_de_poste', u"Intitulé du poste",
+                    {'columnwidth': '7.25cm'}),
+                ('niveau', u"Niveau", {'columnwidth': '1.75cm'}),
+                ('point', u"Point", {'columnwidth': '1.75cm'}),
+                ('regime_de_travail', u"Régime de travail",
+                    {'columnwidth': '2cm'}),
+                ('local_expatrie', u"Local / Expatrié",
+                    {'columnwidth': '2.25cm'}),
+                ('statut', u"Statut", {'columnwidth': '1.25cm'}),
+                ('date_fin_contrat', u"Date de fin de contrat",
+                    {'columnwidth': '2cm'}),
+                HEADER_SEPARATOR,
+                ('date_debut', u"Date de début", {'columnwidth': '1.92cm'}),
+                ('date_fin', u"Date de fin", {'columnwidth': '1.92cm'}),
+                ('nb_jours', u"Nombre de jours", {'columnwidth': '2.82cm'}),
+                HEADER_SEPARATOR,
+                ('devise', u"Devise", {'columnwidth': '1.46cm'}),
+                ('salaire_bstg_annuel', u"Salaire annuel BSTG",
+                    {'columnwidth': '2.5cm'}),
+                ('salaire_bstg_total', u"Salaire total BSTG",
+                    {'columnwidth': '2.5cm'}),
+                ('organisme_bstg', u"Organisme BSTG",
+                    {'columnwidth': '2.9cm'}),
+                HEADER_SEPARATOR,
+                ('salaire_theorique', u"Salaire théorique",
+                    {'columnwidth': '2.5cm'}),
+                ('salaire_base_brut', u"Salaire de base brut",
+                    {'columnwidth': '2.5cm'}),
+                ('salaire_complementaire', u"Salaire complémentaire",
+                    {'columnwidth': '2.5cm'}),
+                HEADER_SEPARATOR,
+                ('indemnite_fonctions', u"Indemnités de fonctions",
+                    {'columnwidth': '2.5cm'}),
+                ('indemnite_expat', u"Indemnités d'expatriation",
+                    {'columnwidth': '2.5cm'}),
+                ('indemnite_logement', u"Indemnités de logement",
+                    {'columnwidth': '2.5cm'}),
+                ('indemnite_transp', u"Indemnités de transport",
+                    {'columnwidth': '2.5cm'}),
+                ('indemnite_13e', u"Indemnités 13e mois",
+                    {'columnwidth': '2.5cm'}),
+                ('prime_interim', u"Prime d'intérim",
+                    {'columnwidth': '2.5cm'}),
+                ('indemnite_autre', u"Autre indemnités",
+                    {'columnwidth': '2.5cm'}),
+                ('indemnite_sous_total', u"Sous-total d'indemnités",
+                    {'columnwidth': '2.5cm'}),
+                HEADER_SEPARATOR,
+                ('prime_installation', u"Prime d'installation",
+                    {'columnwidth': '2.5cm'}),
+                ('prime_demenagement', u"Prime de déménagement",
+                    {'columnwidth': '2.5cm'}),
+                ('prime_avion', u"Prime d'avion",
+                    {'columnwidth': '2.5cm'}),
+                ('prime_autre', u"Autre prime",
+                    {'columnwidth': '2.5cm'}),
+                ('prime_sous_total', u"Total des primes",
+                    {'columnwidth': '2.5cm'}),
+                HEADER_SEPARATOR,
+                ('charges_patronales', u"Charges patronales",
+                    {'columnwidth': '2.5cm'}),
+                ('charges_autre', u"Autres charges patronales",
+                    {'columnwidth': '2.5cm'}),
+                ('charges_sous_total', u"Sous-total des charges patronales",
+                    {'columnwidth': '2.5cm'}),
+                HEADER_SEPARATOR,
+                ('sous_total_traitement_annee', u"Total traitement annuel"),
+                ('sous_total_indemnite_annee', u"Total indemnités annuel"),
+                ('sous_total_accessoire_annee', u"Total accessoires annuel"),
+                ('sous_total_charges_annee', u"Total charges annuel"),
+                HEADER_SEPARATOR,
+                ('masse_salariale', u"Masse salariale",
+                    {'columnwidth': '2.5cm'}),
+                ('masse_salariale_annee', u"Masse salariale %s" % self.annee,
+                    {'columnwidth': '2.5cm'}),
+                ('masse_salariale_annee_euro', u"Masse salariale euro %s" % \
+                        self.annee, {'columnwidth': '2.5cm'}),
+        )
+
+        grand_total = 0.0
+        grand_total_euro = 0.0
+
+        for item in contenu.values():
+            dossiers = item['dossiers']
+            remuns = item['remunerations']
+
+            for d in dossiers:
+                if d.principal:
+                    dossier = list(dossiers)[0]
+
+            regime = float(dossier.poste.regime_travail) / 100
+
+            if dossier.poste.expatrie:
+                statut = "E"
+            else:
+                statut = "L"
+
+            #on détermine la date du début et fin du dossier si année en cours
+            try:
+                d_date_fin = dossier.date_fin \
+                        if dossier.date_fin.year == date_fin.year else None
+            except AttributeError:
+                d_date_fin = None
+            try:
+                d_date_debut = dossier.date_debut \
+                    if dossier.date_debut.year == date_fin.year else None
+            except AttributeError:
+                d_date_debut = None
+
+            pays = \
+                pays_list[dossier.poste.implantation.adresse_physique_pays.id]
+
+            #on détermine si les rémunérations sont tous dans la même devise
+            devise = remuns[0].devise
+            meme_devise = True
+            for r in remuns[1:]:
+                if devise != r.devise:
+                    meme_devise = False
+
+            if not meme_devise:
+                for r in remuns:
+                    self.convertir(r)
+
+            bstg_dossier = None
+            for d in dossiers:
+                if d.organisme_bstg:
+                    bstg_dossier = d
+
+            bstg_remun = None
+            if bstg_dossier:
+                for r in bstg_dossier.rh_remunerations.all():
+                    if r.type.id in TYPE_REMUN_BSTG:
+                        bstg_remun = r
+
+            if bstg_remun:
+                bstg_remun_euro = rh.Remuneration(
+                        montant=bstg_remun.montant, devise=bstg_remun.devise
+                )
+                self.convertir(bstg_remun_euro)
+
+            salaire_complement = 0.0
+            salaire_base = 0.0
+            indemnites = {
+                    'fonc_resp': 0.0,
+                    'expat': 0.0,
+                    'logement': 0.0,
+                    'transp': 0.0,
+                    '13e': 0.0,
+                    'autre_recurr': 0.0,
+                    'interim': 0.0,
+            }
+
+            primes = {
+                    'installation': 0.0,
+                    'demenagement': 0.0,
+                    'avion': 0.0,
+                    'autre': 0.0,
+                }
+            charges = {
+                    'patronale': 0.0,
+                    'autre': 0.0,
+                    }
+
+            total_remun_annee = {
+                    'traitement': 0.0,
+                    'indemnite': 0.0,
+                    'accessoire': 0.0,
+                    'charges': 0.0,
+                    }
+
+            for r in remuns:
+                montant = float(r.montant)
+
+                if r.type_id in TYPE_REMUN_MAD:
+                    salaire_complement += montant
+
+                if r.type_id in TYPE_REMUN_BASE:
+                    salaire_base += montant
+
+                if r.type_id in TYPE_REMUN_FONC_RESP:
+                    indemnites['fonc_resp'] += montant
+
+                if r.type_id in TYPE_REMUN_EXPAT:
+                    indemnites['expat'] += montant
+
+                if r.type_id in TYPE_REMUN_LOGEMENT:
+                    indemnites['logement'] += montant
+
+                if r.type_id in TYPE_REMUN_TRANSP:
+                    indemnites['transp'] += montant
+
+                if r.type_id in TYPE_REMUN_13E:
+                    indemnites['13e'] += montant
+
+                if r.type_id in TYPE_PRIME_INTERIM:
+                    indemnites['interim'] += montant
+
+                if r.type_id not in TYPE_REMUN_ALL_INDEMNITES \
+                        and r.type.nature_remuneration == TYPE_NATURE_INDEMN:
+                    indemnites['autre_recurr'] += montant
+
+                if r.type_id in TYPE_PRIME_INSTALLATION:
+                    primes['installation'] += montant
+
+                if r.type_id in TYPE_PRIME_DEMENAG:
+                    primes['demenagement'] += montant
+
+                if r.type_id in TYPE_PRIME_AVION:
+                    primes['avion'] += montant
+
+                if r.type_id not in TYPE_PRIME_ALL and \
+                        r.type.nature_remuneration == TYPE_NATURE_PAIEMENT:
+                    primes['autre'] += montant
+
+                if r.type_id in TYPE_CHARGE_PATRONALE:
+                    charges['patronale'] += montant
+
+                if r.type_id not in TYPE_CHARGE_ALL and \
+                        r.type.nature_remuneration == TYPE_NATURE_CHARGES:
+                    charges['autre'] += montant
+
+                if r.type.nature_remuneration == TYPE_NATURE_INDEMN:
+                    total_remun_annee['indemnite'] += montant
+
+                if r.type.nature_remuneration == TYPE_NATURE_PAIEMENT:
+                    total_remun_annee['accessoire'] += montant
+
+                if r.type.nature_remuneration == TYPE_NATURE_CHARGES:
+                    total_remun_annee['charges'] += montant
+
+                if r.type.nature_remuneration == TYPE_NATURE_TRAITEMENT:
+                    total_remun_annee['traitement'] += montant
+
+            total_indemnites = sum(indemnites.values())
+
+            #Calcul du nombre de jours pour ce dossier.
+            if dossier.date_debut and dossier.date_debut > date_debut and \
+                    not dossier.date_fin:
+                date_delta = date_fin - dossier.date_fin
+            elif dossier.date_fin and dossier.date_fin < date_fin and \
+                    not dossier.date_debut:
+                date_delta = dossier.date_fin - date_debut
+            elif dossier.date_fin and dossier.date_debut:
+                date_delta = dossier.date_fin - date_debut
+            else:
+                date_delta = rapport_date_delta
+
+            masse_salariale = (salaire_base + total_indemnites + \
+                            sum(primes.values()) + sum(charges.values()))
+            masse_salariale_euro = rh.Remuneration(montant=masse_salariale,
+                        devise=remuns[0].devise)
+            self.convertir(masse_salariale_euro)
+
+            if dossier.classement and dossier.classement.coefficient:
+                coefficient = dossier.classement.coefficient
+            else:
+                coefficient = ""
+
+            #todo valeur du point si pas présent
+            valeur_point = valeurs_point_par_imp.get(
+                    dossier.poste.implantation_id
+                ) or ""
+
+            salaire_theorique = (
+                    round(valeur_point.valeur * int(coefficient) * regime, 2) \
+                    if valeur_point and coefficient and regime else None)
+
+            rapport_nombre_jours = (float(date_delta.days)
+                    / rapport_date_delta.days)
+            item_rapport = {
+                    'bureau': dossier.poste.implantation.region.code,
+                    'pays': unicode(pays),
+                    'implantation': dossier.poste.implantation.nom_court,
+                    'type_implantation': dossier.poste.implantation.type,
+                    #'imputation': None,
+                    'valeur_point': valeur_point,
+                    'numero_employe': dossier.employe_id,
+                    'nom': dossier.employe.nom.upper(),
+                    'prenom': dossier.employe.prenom,
+                    'type_de_poste': dossier.poste.type_poste.nom,
+                    'intitule_de_poste': dossier.poste.nom,
+                    'niveau': unicode(dossier.classement),
+                    'point': coefficient,
+                    'regime_de_travail': "%s %%" % int(regime * 100),
+                    'local_expatrie': statut,
+                    'statut': dossier.statut.code,
+                    'date_fin_contrat': dossier.date_fin or "",
+                    'date_debut': d_date_debut or "",
+                    'date_fin': d_date_fin or "",
+                    'nb_jours': date_delta.days,
+                    'devise': remuns[0].devise,
+                    'salaire_bstg_annuel': bstg_remun.montant \
+                            if bstg_remun else "",
+                    'salaire_bstg_total': bstg_remun_euro.montant \
+                            if bstg_remun else "",
+                    'organisme_bstg': dossier.organisme_bstg or "",
+                    'salaire_theorique': salaire_theorique,
+                    'salaire_base_brut': \
+                            salaire_base * regime * rapport_nombre_jours,
+                    'salaire_complementaire': \
+                            salaire_complement * regime *
+                            rapport_nombre_jours,
+                    #'salaire_total': None
+                    'indemnite_fonctions': indemnites['fonc_resp'] * \
+                            regime * rapport_nombre_jours,
+                    'indemnite_expat': indemnites['expat'] * regime * \
+                            rapport_nombre_jours,
+                    'indemnite_logement': indemnites['logement'] * \
+                            regime * rapport_nombre_jours,
+                    'indemnite_transp': indemnites['transp'] * regime * \
+                            rapport_nombre_jours,
+                    'indemnite_13e': indemnites['13e'] * regime * \
+                            rapport_nombre_jours,
+                    'prime_interim': indemnites['interim'] * regime * \
+                            rapport_nombre_jours,
+                    'indemnite_autre': indemnites['autre_recurr'] * \
+                            regime * rapport_nombre_jours,
+                    'indemnite_sous_total': total_indemnites * regime * \
+                            rapport_nombre_jours,
+                    'total_brut': (
+                                total_indemnites + salaire_base +
+                                salaire_complement
+                            ) * regime * rapport_nombre_jours,
+                    'prime_installation': primes['installation'] * regime * \
+                            rapport_nombre_jours,
+                    'prime_demenagement': primes['demenagement'] * regime * \
+                            rapport_nombre_jours,
+                    'prime_avion': primes['avion'] * regime * \
+                            rapport_nombre_jours,
+                    'prime_autre': primes['autre'] * regime * \
+                            rapport_nombre_jours,
+                    'prime_sous_total': sum(primes.values()) * regime * \
+                            rapport_nombre_jours,
+                    'charges_patronales': charges['patronale'],
+                    'charges_autre': charges['autre'],
+                    'charges_sous_total': sum(charges.values()),
+                    'sous_total_traitement_annee': \
+                            total_remun_annee['traitement'],
+                    'sous_total_indemnite_annee': \
+                            total_remun_annee['indemnite'],
+                    'sous_total_accessoire_annee': \
+                            total_remun_annee['accessoire'],
+                    'sous_total_charges_annee': \
+                            total_remun_annee['charges'],
+                    'masse_salariale': masse_salariale,
+                    'masse_salariale_annee': masse_salariale * \
+                            regime * rapport_nombre_jours,
+                    'masse_salariale_annee_euro': \
+                            masse_salariale_euro.montant * regime *
+                            rapport_nombre_jours,
+                    'sep': None,
+            }
+
+            grand_total += round(masse_salariale, 2)
+            grand_total_euro += round(masse_salariale_euro.montant * regime
+                            * (
+                                    date_delta.days / rapport_date_delta.days
+                            ), 2)
+
+            self.rapport.append(item_rapport)
+
+        self.grand_totaux = (grand_total, grand_total_euro)
+
+    def build_qs(self, prefix, date_debut, date_fin):
+        date_debut_null = \
+                Q(**{"%s%s__isnull" % (prefix, KEY_DATE_DEBUT): True})
+        date_fin_null = \
+                Q(**{"%s%s__isnull" % (prefix, KEY_DATE_FIN): True})
+        date_debut_superieure_ou_egale_a_borne_gauche = \
+                Q(**{"%s%s__gte" % (prefix, KEY_DATE_DEBUT): date_debut})
+        date_debut_inferieure_ou_egale_a_borne_gauche = \
+                Q(**{"%s%s__lte" % (prefix, KEY_DATE_DEBUT): date_debut})
+        date_fin_superieure_ou_egale_a_borne_gauche = \
+                Q(**{"%s%s__gte" % (prefix, KEY_DATE_FIN): date_debut})
+        date_fin_inferieure_ou_egale_a_borne_droite = \
+                Q(**{"%s%s__lte" % (prefix, KEY_DATE_FIN): date_fin})
+        date_debut_inferieure_ou_egale_a_borne_droite = \
+                Q(**{"%s%s__lte" % (prefix, KEY_DATE_DEBUT): date_fin})
+        date_fin_superieure_ou_egale_a_borne_droite = \
+                Q(**{"%s%s__gte" % (prefix, KEY_DATE_FIN): date_fin})
+
+        q_range = \
+            (
+                date_debut_null & date_fin_null
+            ) | (
+                date_debut_inferieure_ou_egale_a_borne_gauche &
+                date_fin_superieure_ou_egale_a_borne_gauche &
+                date_fin_inferieure_ou_egale_a_borne_droite
+            ) | (
+                date_debut_superieure_ou_egale_a_borne_gauche &
+                date_debut_inferieure_ou_egale_a_borne_droite &
+                date_fin_superieure_ou_egale_a_borne_droite
+            ) | (
+                date_debut_inferieure_ou_egale_a_borne_gauche &
+                date_fin_superieure_ou_egale_a_borne_droite
+            ) | (
+                date_debut_null &
+                date_fin_superieure_ou_egale_a_borne_droite
+            ) | (
+                date_debut_inferieure_ou_egale_a_borne_gauche &
+                date_fin_null
+            )
+
+        return q_range
+
+    def convertir(self, remuneration):
+        if remuneration.devise != self.devise_base:
+            try:
+                remuneration.montant = float(remuneration.montant) * \
+                    self.trouver_taux(remuneration.devise).taux
+                remuneration.devise = self.devise_base
+            except AttributeError:
+                pass
+
+    def trouver_taux(self, devise):
+        if devise.code not in self.taux_change:
+            try:
+                t = rh.TauxChange.objects.filter(
+                        devise=devise, annee=self.annee
+                )[0]
+            except IndexError:
+                return None
+            self.taux_change[devise.code] = t
+        return self.taux_change[devise.code]
+
+    def csv(self):
+        self.csv_handle = StringIO.StringIO()
+        csv_writer = csv.writer(self.csv_handle, delimiter=",",
+                doublequote=False, escapechar="\\", quoting=csv.QUOTE_ALL,
+        )
+        header = [v[0] for v in self.rapport[0]]
+        csv_writer.writerow(header)
+        for row in self.rapport:
+            values = [v[1] for v in row]
+            csv_writer.writerow(
+                    [unicode(r).encode('utf-8') for r in values]
+            )
+
+    def ods(self):
+        self.doc = ods.OpenDocumentSpreadsheet()
+        table = self.doc.add_table(name=u'Masse salariale %s' % self.annee)
+
+        for h in self.headers:
+            if len(h) > 2:
+                table.add_column(**h[2])
+
+        table.add_row([h[1] for h in self.headers], rowheight='2cm')
+
+        for r in self.rapport:
+            table.add_row([r[h[0]] for h in self.headers])
+
+        #a.doc.write('hello_world.ods')
diff --git a/project/rh/ods.py b/project/rh/ods.py
new file mode 100644 (file)
index 0000000..3b48784
--- /dev/null
@@ -0,0 +1,140 @@
+# encoding: utf-8
+
+from decimal import Decimal
+
+import odf.opendocument
+import odf.style
+import odf.table
+from odf.style import Style, MasterPage, PageLayout, PageLayoutProperties, \
+        TextProperties, GraphicProperties, ParagraphProperties, \
+        DrawingPageProperties
+
+
+def valuetype(val):
+    valuetype = "string"
+    if isinstance(val, str):
+        valuetype = "string"
+    if isinstance(val, (int, float, Decimal)):
+        valuetype = "float"
+    if isinstance(val, bool):
+        valuetype = "boolean"
+
+    return valuetype
+
+
+class Wrapper(object):
+
+    def __init__(self, *args, **kwargs):
+        self.__wrapped = self._wrapper_constructor(*args, **kwargs)
+
+    def __getattr__(self, attr):
+        return getattr(self.__wrapped, attr)
+
+
+class OpenDocumentSpreadsheet(Wrapper):
+    _wrapper_constructor = staticmethod(
+        odf.opendocument.OpenDocumentSpreadsheet
+    )
+
+    def __init__(self, *args, **kwargs):
+        super(OpenDocumentSpreadsheet, self).__init__(*args, **kwargs)
+        self._automatic_style_idx = 0
+
+    def add_table(self, **kwargs):
+        table = Table(**kwargs)
+        table._doc = self
+        self.spreadsheet.addElement(table)
+        return table
+
+    def add_automatic_style(self, **kwargs):
+        name = 'auto_style_%d' % self._automatic_style_idx
+        style = odf.style.Style(name=name, **kwargs)
+        self.automaticstyles.addElement(style)
+        self._automatic_style_idx += 1
+        return style
+
+
+class Table(Wrapper):
+    _wrapper_constructor = staticmethod(odf.table.Table)
+
+    def add_row(self, values=[], **kwargs):
+         # attributs appartenant à table-column-poperties
+#        props = {}
+#        for attr in ['rowheight']:
+#            if attr in kwargs:
+#                props[attr] = kwargs.pop(attr)
+
+        style = self._doc.add_automatic_style(family='table-row')
+        if 'rowheight' in kwargs:
+            style.addElement(odf.style.TableRowProperties(
+                rowheight=kwargs['rowheight']))
+            kwargs['stylename'] = style.getAttribute('name')
+            del kwargs['rowheight']
+
+        style = {}
+
+        row = TableRow(**kwargs)
+        row._doc = self._doc
+        for value in values:
+            row.add_cell(value, verticalalign='middle', **style)
+        self.addElement(row)
+        return row
+
+    def add_column(self, **kwargs):
+
+        # attributs appartenant à table-column-poperties
+        props = {}
+        for attr in ['columnwidth']:
+            if attr in kwargs:
+                props[attr] = kwargs.pop(attr)
+
+        if props:
+            style = self._doc.add_automatic_style(family='table-column')
+            style.addElement(odf.style.TableColumnProperties(**props))
+            kwargs['stylename'] = style.getAttribute('name')
+        col = odf.table.TableColumn(**kwargs)
+        self.addElement(col)
+        return col
+
+
+class TableRow(Wrapper):
+    _wrapper_constructor = staticmethod(odf.table.TableRow)
+
+    def add_cell(self, value=None, **kwargs):
+        if value:
+            if isinstance(value, (basestring, unicode)):
+                kwargs['stringvalue'] = unicode(value)
+            elif isinstance(value, (int, float, Decimal)):
+                kwargs['valuetype'] = "float"
+                kwargs['value'] = float(value)
+            elif type(value) == type(None):
+                kwargs['stringvalue'] = u""
+                kwargs['stringvalue'] = u""
+            else:
+                kwargs['stringvalue'] = unicode(value)
+
+        if 'verticalalign' in kwargs:
+            style = self._doc.add_automatic_style(family='table-cell')
+            style.addElement(odf.style.TableCellProperties(
+                verticalalign=kwargs['verticalalign']))
+            kwargs['stylename'] = style.getAttribute('name')
+            del kwargs['verticalalign']
+
+#        props = {}
+#        if 'fontweight' in kwargs:
+#            props['fontweight'] = kwargs.pop('fontweight')
+#        if 'stringvalue' in kwargs:
+#            props['stringvalue'] = kwargs.pop('stringvalue')
+
+        cell = odf.table.TableCell(**kwargs)
+#        if 'fontweight' in props:
+#            tablecontents = Style(name="Bold", family="paragraph")
+#            tablecontents.addElement(TextProperties(fontweight="bold"))
+#            self._doc.styles.addElement(tablecontents)
+
+#        if 'stringvalue' in props:
+#            p = P(stylename='Bold',text=props['stringvalue'])
+#            cell.addElement(p)
+
+        self.addElement(cell)
+        return cell
diff --git a/project/rh/static/admin/js/jquery-stickytableheaders.js b/project/rh/static/admin/js/jquery-stickytableheaders.js
new file mode 100644 (file)
index 0000000..9f55aa7
--- /dev/null
@@ -0,0 +1,116 @@
+
+/*! Copyright (c) 2011 by Jonas Mosbech - https://github.com/jmosbech/StickyTableHeaders 
+    MIT license info: https://github.com/jmosbech/StickyTableHeaders/blob/master/license.txt */
+
+(function ($) {
+    $.StickyTableHeaders = function (el, options) {
+        // To avoid scope issues, use 'base' instead of 'this'
+        // to reference this class from internal events and functions.
+        var base = this;
+
+        // Access to jQuery and DOM versions of element
+        base.$el = $(el);
+        base.el = el;
+
+        // Cache DOM refs for performance reasons
+        base.$window = $(window);
+        base.$clonedHeader = null;
+        base.$originalHeader = null;
+
+        // Add a reverse reference to the DOM object
+        base.$el.data('StickyTableHeaders', base);
+
+        base.init = function () {
+            base.options = $.extend({}, $.StickyTableHeaders.defaultOptions, options);
+
+            base.$el.each(function () {
+                var $this = $(this);
+
+                // remove padding on <table> to fix issue #7
+                $this.css('padding', 0);
+
+                $this.wrap('<div class="divTableWithFloatingHeader"></div>');
+
+                base.$originalHeader = $('thead:first', this);
+                base.$clonedHeader = base.$originalHeader.clone();
+
+                base.$clonedHeader.addClass('tableFloatingHeader');
+                base.$clonedHeader.css({
+                    'position': 'fixed',
+                    'top': 0,
+                    'left': $this.css('margin-left'),
+                    'display': 'none'
+                });
+
+                base.$originalHeader.addClass('tableFloatingHeaderOriginal');
+                
+                base.$originalHeader.before(base.$clonedHeader);
+
+                // enabling support for jquery.tablesorter plugin
+                // forward clicks on clone to original
+                $('th', base.$clonedHeader).click(function(e){
+                    var index = $('th', base.$clonedHeader).index(this);
+                    $('th', base.$originalHeader).eq(index).click();
+                });
+                $this.bind('sortEnd', base.updateCloneFromOriginal );
+            });
+
+            base.updateTableHeaders();
+            base.$window.scroll(base.updateTableHeaders);
+            base.$window.resize(base.updateTableHeaders);
+        };
+
+        base.updateTableHeaders = function () {
+            base.$el.each(function () {
+                var $this = $(this);
+
+                var fixedHeaderHeight = isNaN(base.options.fixedOffset) ? base.options.fixedOffset.height() : base.options.fixedOffset;
+
+                var offset = $this.offset();
+                var scrollTop = base.$window.scrollTop() + fixedHeaderHeight;
+                var scrollLeft = base.$window.scrollLeft();
+
+                if ((scrollTop > offset.top) && (scrollTop < offset.top + $this.height())) {
+                    base.$clonedHeader.css({
+                        'top': fixedHeaderHeight,
+                        'margin-top': 0,
+                        'left': offset.left - scrollLeft,
+                        'display': 'block'
+                    });
+
+                    base.updateCloneFromOriginal();
+                }
+                else {
+                    base.$clonedHeader.css('display', 'none');
+                }
+            });
+        };
+
+        base.updateCloneFromOriginal = function () {
+            // Copy cell widths and classes from original header
+            $('th', base.$clonedHeader).each(function (index) {
+                var $this = $(this);
+                var origCell = $('th', base.$originalHeader).eq(index);
+                $this.removeClass().addClass(origCell.attr('class'));
+                $this.css('width', origCell.width());
+            });
+
+            // Copy row width from whole table
+            base.$clonedHeader.css('width', base.$originalHeader.width());
+        };
+
+        // Run initializer
+        base.init();
+    };
+
+    $.StickyTableHeaders.defaultOptions = {
+        fixedOffset: 0
+    };
+
+    $.fn.stickyTableHeaders = function (options) {
+        return this.each(function () {
+            (new $.StickyTableHeaders(this, options));
+        });
+    };
+
+})(jQuery);
index 8c5472d..3f1d851 100644 (file)
@@ -1,37 +1,7 @@
 {{ form.media }}
 
-<script type="text/javascript">
-  $(document).ready(function(){
-    function updateQueryStringParameter(a, k, v) {
-      var re = new RegExp("([?|&])" + k + "=.*?(&|$)", "i"),
-        separator = a.indexOf('?') !== -1 ? "&" : "?";
+{% include "admin/rh/annee_select.js" %}
 
-      if (a.match(re)) return a.replace(re, '$1' + k + "=" + v + '$2');
-      else return a + separator + k + "=" + v;
-    }
-    $("#plage_date_rechercher").click(function(e){
-      e.preventDefault();
-      uri = window.location.search;
-      if($("#id_date_fin").val()) 
-        uri = updateQueryStringParameter(uri, 'date_fin', $("#id_date_fin").val());
-      if($("#id_date_debut").val()) 
-        uri = updateQueryStringParameter(uri, 'date_debut', $("#id_date_debut").val());
-      window.location = window.location.pathname+uri;
-    });
-    $("#id_date_fin").keypress(function(e){
-      if(e.keyCode == 13) {
-        e.preventDefault();
-        $("#plage_date_rechercher").trigger("click");
-      }
-    });
-    $("#id_date_debut").keypress(function(e){
-      if(e.keyCode == 13) {
-        e.preventDefault();
-        $("#plage_date_rechercher").trigger("click");
-      }
-    });
-  });
-</script>
 <div class="actions">
   <label>Statut :
     {{ form.statut }}
diff --git a/project/rh/templates/admin/rh/annee_select.js b/project/rh/templates/admin/rh/annee_select.js
new file mode 100644 (file)
index 0000000..22394d6
--- /dev/null
@@ -0,0 +1,32 @@
+<script type="text/javascript">
+  $(document).ready(function(){
+    function updateQueryStringParameter(a, k, v) {
+      var re = new RegExp("([?|&])" + k + "=.*?(&|$)", "i"),
+        separator = a.indexOf('?') !== -1 ? "&" : "?";
+
+      if (a.match(re)) return a.replace(re, '$1' + k + "=" + v + '$2');
+      else return a + separator + k + "=" + v;
+    }
+    $("#plage_date_rechercher").click(function(e){
+      e.preventDefault();
+      uri = window.location.search;
+      if($("#id_date_fin").val()) 
+        uri = updateQueryStringParameter(uri, 'date_fin', $("#id_date_fin").val());
+      if($("#id_date_debut").val()) 
+        uri = updateQueryStringParameter(uri, 'date_debut', $("#id_date_debut").val());
+      window.location = window.location.pathname+uri;
+    });
+    $("#id_date_fin").keypress(function(e){
+      if(e.keyCode == 13) {
+        e.preventDefault();
+        $("#plage_date_rechercher").trigger("click");
+      }
+    });
+    $("#id_date_debut").keypress(function(e){
+      if(e.keyCode == 13) {
+        e.preventDefault();
+        $("#plage_date_rechercher").trigger("click");
+      }
+    });
+  });
+</script>
diff --git a/project/rh/templates/rh/rapports/masse_salariale.html b/project/rh/templates/rh/rapports/masse_salariale.html
new file mode 100644 (file)
index 0000000..8910afd
--- /dev/null
@@ -0,0 +1,80 @@
+{% extends 'rh/rapports/base.html' %}
+{% load adminmedia rapports i18n l10n %}
+
+{% block nomrapport %}Rapport de masse salariale{% endblock %}
+{% block count_elements %}<h2>Rapport du {{ request.GET.date_debut }} au {{ request.GET.date_fin }}</h2>{% endblock %}
+
+{% block extrastyle %}
+{{ block.super }}
+<script type="text/javascript" src="{{ STATIC_URL }}js/jquery-1.5.1.min.js"></script>
+<script type="text/javascript" src="{% admin_media_prefix %}js/jquery-stickytableheaders.js"></script>
+
+{% comment %} datepicker dates selections {% endcomment %}
+<script type="text/javascript" src="/admin/jsi18n"></script>
+<script type="text/javascript" src="{{ STATIC_URL }}/admin/js/core.js"></script>
+{{ form.media }}
+{% include "admin/rh/annee_select.js" %}
+<style>
+    #changelist .actions .filter {width: auto; float: left;}
+    #changelist .actions .filter h3 {font-size: 11px; margin-left: 0.5em;}
+</style>
+{% endblock %}
+
+{% block contentrapport %}
+
+<div class="actions">
+
+    <div class="filter">{% filter_region "dossier__poste__" %}</div>
+    <div class="filter">{% filter_implantation "dossier__poste__" %}</div>
+    <div class="clear"></div>
+    {% comment %}
+    <label>Plage de dates:
+        {{ form.date_debut }} au {{ form.date_fin }}
+    </label>
+    {% endcomment %}
+    <label>Année
+        {{ form.date_debut }}
+    </label>
+  <a href="{{ request.url }}?{{ query_string }}" id="plage_date_rechercher" class="button">Rechercher</a>
+</div>
+<div class="clear"></div>
+
+
+<script type="text/javascript">
+    jQuery(document).ready(function(){
+        $("#result_list").stickyTableHeaders();
+    });
+</script>
+<table id="result_list" class="results">
+<thead>
+<tr>
+    {% table_header headers %}
+</tr>
+</thead>
+{% localize on %}
+{% spaceless %}
+{% for row in rapport %}
+    <tr class="{% cycle 'row1' 'row2' %}">
+        {% for column in header_keys %}
+            {% if column == 'sep' %}
+                <td style="background:gray;">&nbsp;</td>
+            {% else %}
+                {% if row|hash:column|is_float %}
+                    <td>{{ row|hash:column|floatformat:2|localize }}</td>
+                {% else %}
+                    <td>{{ row|hash:column|default:"" }}</td>
+                {% endif %}
+            {% endif %}
+        {% endfor %}
+    </tr>
+{% endfor %}{% endspaceless %}
+    <tr>
+        <td colspan="{{ colspan }}" style="text-align:right;font-weight:bold;">
+            TOTAL : 
+        </td>
+        <td>{{ total }}</td>
+        <td>{{ total_euro }}</td>
+    </tr>
+</table>
+{% endlocalize %}
+{% endblock %}
diff --git a/project/rh/templates/rh/rapports/remuneration.html b/project/rh/templates/rh/rapports/remuneration.html
deleted file mode 100644 (file)
index 3ad5e08..0000000
+++ /dev/null
@@ -1,35 +0,0 @@
-{% extends 'rh/rapports/base.html' %}
-{% load adminmedia rapports i18n %}
-
-{% block nomrapport %}Rapport de rémunération{% endblock %}
-
-{% block contentrapport %}
-
-<div id="changelist-filter">
-<h2>{% trans 'Filter' %}</h2>
-{% filter_region_remun %}
-{% filter_implantation_remun %}
-</div>
-
-<table id="result_list">
-<thead>
-<tr>
-    {% table_header headers %}
-</tr>
-</thead>
-{% spaceless %}{% for employe in employes %}
-<tr class="{% cycle 'row1' 'row2' %}">
-    <td style="text-align:right"><a href="{% url admin:rh_employe_change employe.id %}">{{ employe.id }}</a></td>
-    <td><a href="{% url admin:rh_employe_change employe.id %}">{{ employe.nom|upper }}</td></a>
-    <td><a href="{% url admin:rh_employe_change employe.id %}">{{ employe.prenom }}</a></td>
-       <td align="right">{{ employe.Traitement|default:0|floatformat:2 }} &euro;</td>
-       <td align="right">{{ employe.RAS|default:0|floatformat:2 }} &euro;</td>
-       <td align="right">{{ employe.Indemnite|default:0|floatformat:2 }} &euro;</td>
-       <td align="right">{{ employe.Accessoire|default:0|floatformat:2 }} &euro;</td>
-       <td align="right">{{ employe.Charges|default:0|floatformat:2 }} &euro;</td>
-       <td align="right">{{ employe.remun_sum_euro|default:0|floatformat:2 }} &euro;</td>
-</tr>
-{% endfor %}{% endspaceless %}
-</table>
-
-{% endblock %}
index cc7f828..72f3ccc 100644 (file)
@@ -23,15 +23,19 @@ def filter_comble(context):
 
 
 @register.inclusion_tag('admin/filter.html', takes_context=True)
-def filter_region(context):
+def filter_region(context, prefix=None):
+
+    label = "".join([prefix or "", "implantation__region"])
     return {'title': u"région",
-            'choices': prepare_choices(Region.objects.values_list('id', 'nom'), 'implantation__region', context, remove=['pays', 'nord_sud'])}
+            'choices': prepare_choices(Region.objects.values_list('id', 'nom'), label, context, remove=['pays', 'nord_sud'])}
 
 
 @register.inclusion_tag('admin/filter.html', takes_context=True)
-def filter_implantation(context):
+def filter_implantation(context, prefix=None):
+
+    label = "".join([prefix or "", "implantation"])
     return {'title': u"implantation",
-            'choices': prepare_choices(Implantation.objects.values_list('id', 'nom'), 'implantation', context)}
+            'choices': prepare_choices(Implantation.objects.values_list('id', 'nom'), label, context)}
 
 
 @register.inclusion_tag('admin/filter.html', takes_context=True)
@@ -220,3 +224,15 @@ class SortHeaders:
 @register.filter
 def split(str, splitter):
     return str.split(splitter)
+
+@register.filter
+def hash(h, key):
+    if key in h:
+        return h[key]
+    else:
+        raise Exception("%s does not exist" % key)
+
+@register.filter
+def is_float(v):
+    if type(v) == type(float()):
+        return True
index cf676a7..65dc300 100644 (file)
@@ -8,6 +8,7 @@ urlpatterns = patterns(
     url(r'^admin/rh/rapports/employes_sans_contrats$', 'rapports_employe_sans_contrat', name='rhr_employe_sans_contrat'),
     url(r'^admin/rh/rapports/contrats$', 'rapports_contrat', name='rhr_contrats'),
     url(r'^admin/rh/rapports/remuneration$', 'rapports_remuneration', name='rhr_remuneration'),
+    url(r'^admin/rh/rapports/masse_salariale$', 'rapports_masse_salariale', name='rhr_masse_salariale'),
     url(r'^admin/rh/rapports/postes_par_service$', 'rapports_postes_service', name='rhr_postes_service'),
     url(r'^admin/rh/rapports/postes_par_implantation$', 'rapports_postes_implantation', name='rhr_postes_implantation'),
     url(r'^admin/rh/rapports/postes_modelisation$', 'rapports_postes_modelisation', name='rhr_postes_modelisation'),
index 2353e7a..9cb8ceb 100644 (file)
@@ -1,5 +1,6 @@
 # -*- encoding: utf-8 -*-
 
+import urllib
 from datetime import date
 from itertools import izip
 
@@ -7,11 +8,13 @@ import pygraphviz as pgv
 
 from django.core.urlresolvers import reverse
 from django.db.models import Q
-from django.contrib.auth.decorators import login_required
 from django.utils.encoding import smart_str
 from django.shortcuts import render_to_response, get_object_or_404
 from django.template import RequestContext
 from django.http import HttpResponse
+from django import forms
+from django.contrib.auth.decorators import login_required
+from django.contrib.admin import widgets as adminwidgets
 
 from datamaster_modeles import models as ref
 
@@ -21,6 +24,7 @@ from rh.decorators import drh_or_admin_required
 from rh.templatetags.rapports import SortHeaders
 from rh.change_list import RechercheTemporelle
 from rh import graph as rh_graph
+from rh.masse_salariale import MasseSalariale
 
 # pas de reference a DAE devrait etre refactorisé
 from dae.utils import get_employe_from_user
@@ -195,6 +199,70 @@ def rapports_contrat(request):
 
 @login_required
 @drh_or_admin_required
+def rapports_masse_salariale(request):
+
+    class RechercheTemporelle(forms.Form):
+        CHOICE_ANNEES = range(
+                rh.Remuneration.objects.exclude(date_debut=None) \
+                        .order_by('date_debut')[0].date_debut.year
+                , date.today().year + 1)
+        CHOICE_ANNEES.append('')
+
+        #date_debut pour que le script javascript le mette dans le querystring
+        date_debut = forms.CharField(widget=forms.Select(choices=
+                    ((a, a) for a in reversed(CHOICE_ANNEES)
+                    ))
+                )
+
+        #date_debut = forms.DateField(widget=adminwidgets.AdminDateWidget)
+        #date_fin = forms.DateField(widget=adminwidgets.AdminDateWidget)
+
+
+    form = RechercheTemporelle(request.GET)
+    get_filtre = [(k, v) for k,v in request.GET.items() \
+            if k not in ('date_debut', 'date_fin', 'implantation', )]
+    query_string = urllib.urlencode(get_filtre)
+
+    date_debut = None
+    date_fin = None
+    if request.GET.get('date_debut', None):
+        date_debut = "01-01-%s" % request.GET.get('date_debut', None)
+        date_fin = "31-12-%s" % request.GET.get('date_debut', None)
+
+    implantation = request.GET.get('dossier__poste__implantation')
+    region = request.GET.get('dossier__poste__implantation__region')
+
+    custom_filter = {}
+    if implantation:
+        custom_filter['dossier__poste__implantation'] = implantation
+    if region:
+        custom_filter['dossier__poste__implantation__region'] = region
+
+    c = {
+            'title': 'Rapport de masse salariale',
+            'form': form,
+            'headers': [],
+            'query_string': query_string,
+    }
+    if date_debut or date_fin:
+        masse = MasseSalariale(date_debut, date_fin, custom_filter,
+                request.GET.get('ne_pas_grouper', False))
+        if masse.rapport:
+            c['rapport'] = masse.rapport
+            headers = masse.headers
+            c['header_keys'] = [h[0] for h in masse.headers]
+            h = SortHeaders(request, headers, order_field_type="ot",
+                    not_sortable=c['header_keys'], order_field="o")
+            c['headers'] = list(h.headers())
+            c['total'] = masse.grand_totaux[0]
+            c['total_euro'] = masse.grand_totaux[1]
+            c['colspan'] = len(c['header_keys']) - 2
+
+    return render_to_response('rh/rapports/masse_salariale.html', c, RequestContext(request))
+
+
+@login_required
+@drh_or_admin_required
 def rapports_remuneration(request):
 
     lookup_params = dict(request.GET.items())
@@ -571,4 +639,3 @@ def organigrammes_region(request, id):
     }
 
     return render_to_response('rh/organigrammes/vide.html', c, RequestContext(request), mimetype="image/svg+xml")
-