[auf_rh_dae.git] / project / rh / masse_salariale.py

# -*- encoding: utf-8 -*-
import csv
import datetime
import itertools
import StringIO
import time

from auf.django.references import models as ref
from django.db.models import Q, Count

from project.rh import ods
from project.rh import 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_SCOLARITE = (5,)
TYPE_REMUN_TRANSP = (9,)
TYPE_REMUN_13E = (18,)
TYPE_PRIME_INTERIM = (19,)
TYPE_REMUN_ALL_INDEMNITES = list(itertools.chain(*(
        TYPE_REMUN_BSTG, TYPE_REMUN_BASE, TYPE_REMUN_FONC_RESP,
        TYPE_REMUN_EXPAT, TYPE_REMUN_LOGEMENT, TYPE_REMUN_TRANSP,
        TYPE_REMUN_13E, TYPE_PRIME_INTERIM, TYPE_REMUN_SCOLARITE)))
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', ods.Separator(), {'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)
        self.custom_filter = custom_filter

        self.region = None
        self.implantation = None
        if 'dossier__poste__implantation__region' in custom_filter:
            self.region = ref.Region.objects.get(
                    id=custom_filter['dossier__poste__implantation__region']
            )
        if 'dossier__poste__implantation' in custom_filter:
            self.implantation = ref.Implantation.objects.get(
                    id=custom_filter['dossier__poste__implantation']
            )

        remunerations = remunerations.exclude(supprime=True) \
                .select_related(
                        "dossier", "dossier_employe", "dossier_poste", "type"
                        )

        custom_filter = {}
        for k, v in self.custom_filter.items():
            custom_filter[k.replace('dossier__', '')] = v
        count_dossiers_by_employe = dict((d['id'], d['count']) for d in
                rh.Dossier.objects.filter(q_range).values('id') \
                .filter(**custom_filter) \
                .annotate(count=Count('employe')))

        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 actel", {'columnwidth': '1.75cm'}),
                ('point', u"Point", {'columnwidth': '1.75cm'}),
                ('regime_de_travail', u"Régime de travail annuel",
                    {'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 BSTG ANNUEL",
                    {'columnwidth': '2.5cm'}),
                ('salaire_bstg_euro', u"Salaire BSTG EUR",
                    {'columnwidth': '2.5cm'}),
                ('organisme_bstg', u"Organisme BSTG",
                    {'columnwidth': '2.9cm'}),
                HEADER_SEPARATOR,
                ('salaire_theorique', u"Salaire théorique ANNUEL",
                    {'columnwidth': '2.5cm', 'background-color': '#ecab44'}),
                ('salaire_base_brut', u"Salaire de base brut",
                    {'columnwidth': '2.5cm', 'background-color': '#ecab44'}),
                ('salaire_complementaire', u"Salaire complémentaire",
                    {'columnwidth': '2.5cm', 'background-color': '#ecab44'}),
                HEADER_SEPARATOR,
                ('indemnite_fonctions', u"Indemnités de fonctions",
                    {'columnwidth': '2.5cm', 'background-color': '#fff840'}),
                ('indemnite_expat', u"Indemnités d'expatriation",
                    {'columnwidth': '2.5cm', 'background-color': '#fff840'}),
                ('indemnite_scolarite', u"Indemnités de frais de scolarité",
                    {'columnwidth': '2.5cm', 'background-color': '#fff840'}),
                ('indemnite_logement', u"Indemnités de logement",
                    {'columnwidth': '2.5cm', 'background-color': '#fff840'}),
                ('indemnite_transp', u"Indemnités de transport",
                    {'columnwidth': '2.5cm', 'background-color': '#fff840'}),
                ('indemnite_13e', u"Indemnités 13e mois",
                    {'columnwidth': '2.5cm', 'background-color': '#fff840'}),
                ('prime_interim', u"Prime d'intérim",
                    {'columnwidth': '2.5cm', 'background-color': '#fff840'}),
                ('indemnite_autre', u"Autre indemnités",
                    {'columnwidth': '2.5cm', 'background-color': '#fff840'}),
                ('indemnite_sous_total', u"Sous-total d'indemnités",
                    {'columnwidth': '2.5cm', 'background-color': '#fff840'}),
                HEADER_SEPARATOR,
                ('prime_installation', u"Prime d'installation",
                    {'columnwidth': '2.5cm', 'background-color': '#d7fb0f'}),
                ('prime_demenagement', u"Prime de déménagement",
                    {'columnwidth': '2.5cm', 'background-color': '#d7fb0f'}),
                ('prime_avion', u"Prime d'avion",
                    {'columnwidth': '2.5cm', 'background-color': '#d7fb0f'}),
                ('prime_autre', u"Autre prime",
                    {'columnwidth': '2.5cm', 'background-color': '#d7fb0f'}),
                ('prime_sous_total', u"Total des primes",
                    {'columnwidth': '2.5cm', 'background-color': '#d7fb0f'}),
                HEADER_SEPARATOR,
                ('charges_patronales', u"Charges patronales",
                    {'columnwidth': '2.5cm', 'background-color': '#fb680f'}),
                ('charges_autre', u"Autres charges patronales",
                    {'columnwidth': '2.5cm', 'background-color': '#fb680f'}),
                ('charges_sous_total', u"Sous-total des charges patronales",
                    {'columnwidth': '2.5cm', 'background-color': '#fb680f'}),
                HEADER_SEPARATOR,
                ('sous_total_traitement_annee', u"Total traitements",
                    {'background-color': '#ecab44'}),
                ('sous_total_indemnite_annee', u"Total indemnités",
                    {'background-color': '#fff840'}),
                ('sous_total_accessoire_annee', u"Total accessoires",
                    {'background-color': '#d7fb0f'}),
                ('sous_total_charges_annee', u"Total charges",
                    {'background-color': '#fb680f'}),
                HEADER_SEPARATOR,
                ('masse_salariale', u"Masse salariale ANNUELLE",
                    {'columnwidth': '2.5cm', 'background-color': '#e6c6ed'}),
                ('masse_salariale_annee', u"Masse salariale",
                    {'columnwidth': '2.5cm', 'background-color': '#e6c6ed'}),
                ('masse_salariale_annee_euro', u"Masse salariale %s EUR" % \
                        self.annee, {
                            'columnwidth': '2.5cm',
                            'background-color': '#e6c6ed'
                            }
                ),
        )

        grand_total = 0.0
        grand_total_euro = 0.0

        if not lineariser_dossiers:
            for dossier_id, count in count_dossiers_by_employe.items():
                if dossier_id not in contenu or \
                    len(contenu[dossier_id]['dossiers']) != count:
                    not_in = []
                    if dossier_id in contenu:
                        for d in contenu[dossier_id]['dossiers']:
                            not_in.append(d.id)
                    custom_filter = {}
                    for k, v in self.custom_filter.items():
                        custom_filter[k.replace('dossier__', '')] = v

                    employe_id = rh.Dossier.objects.values('employe').filter(
                            id=dossier_id).all()[0]['employe']
                    dossiers = rh.Dossier.objects.filter(q_range) \
                            .filter(employe=employe_id) \
                            .filter(**custom_filter) \
                            .all()
                    for d in dossiers:
                        if d.id not in contenu:
                            contenu[d.id] = {
                                    'dossiers': set((d,)),
                                    'remunerations': []
                            }
                        else:
                            contenu[d.id]['dossiers'].add(d)

        postes = rh.Poste.objects

        custom_filter = {}
        for k, v in self.custom_filter.items():
            custom_filter[k.replace('dossier__poste__', '')] = v

        if custom_filter:
            postes = postes.filter(**custom_filter)

        postes_vacants = [p for p in postes.filter(q_range).all()
                if p.is_vacant()]
        remuneration_base = rh.TypeRemuneration.objects.get(
                id=TYPE_REMUN_BASE[0])
        remuneration_indem = rh.TypeRemuneration(
                nature_remuneration=TYPE_NATURE_INDEMN)
        remuneration_charge = rh.TypeRemuneration(
                nature_remuneration=TYPE_NATURE_CHARGES)
        for p in postes_vacants:
            d = rh.Dossier()
            d.employe = rh.Employe()
            d.statut = rh.Statut()
            d.poste = p
            d.classement = p.classement_max
            d.point = p.valeur_point_max
            if p.devise_max:
                remunerations = [
                    rh.Remuneration(
                        montant=p.salaire_max, devise=p.devise_max,
                        type=remuneration_base
                    ),
                    rh.Remuneration(
                        montant=p.indemn_max, devise=p.devise_max,
                        type=remuneration_indem
                    ),
                    rh.Remuneration(
                        montant=p.autre_max, devise=p.devise_max,
                        type=remuneration_charge
                    )
                ]
            else:
                remunerations = [
                    rh.Remuneration(
                        montant=0, devise=self.devise_base, type=type
                    )
                    for type in (
                        remuneration_base, remuneration_indem,
                        remuneration_charge
                    )
                ]
            contenu['p_%s' % p.id] = {
                'dossiers': set([d]),
                'remunerations': remunerations
            }

        for item in contenu.values():
            dossiers = item['dossiers']
            remuns = item['remunerations']

            if not dossiers:
                continue
            dossier = list(dossiers)[0]
            for d in dossiers:
                if d.principal:
                    dossier = d

            regime = (float(dossier.regime_travail) / 100)

            if dossier.statut_residence == "expat":
                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
            try:
                devise = remuns[0].devise
            except IndexError:
                devise = self.devise_base
            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)
                devise = remuns[0].devise

            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_MAD:
                        bstg_remun = rh.Remuneration(
                                montant=float(r.montant),
                                devise=r.devise
                        )

            if bstg_remun:
                bstg_remun_euro = rh.Remuneration(
                        montant=float(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,
                    'scolarite': 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,
                    }

            #Calcul du nombre de jours pour ce dossier.
            dossier_date_delta = self.calculer_nombre_jours(
                    dossier.date_debut, dossier.date_fin,
                    date_debut, date_fin)

            masse_salariale = 0.0
            masse_salariale_annee = 0.0
            for r in remuns:
                montant = float(r.montant)
                if r.date_fin is None and dossier.date_fin is not None:
                    r.date_fin = min(date_fin, dossier.date_fin)
                facteur = self.calculer_nombre_jours(
                            r.date_debut, r.date_fin,
                            date_debut, date_fin).days \
                        / float(rapport_date_delta.days)

                if r.type_id in TYPE_REMUN_BSTG:
                    salaire_complement += montant * facteur

                if r.type_id not in TYPE_REMUN_MAD:
                    masse_salariale += montant
                    masse_salariale_annee += montant * regime

                if r.type_id in TYPE_REMUN_BASE:
                    salaire_base += montant * facteur

                if r.type_id in TYPE_REMUN_FONC_RESP:
                    indemnites['fonc_resp'] += montant * facteur

                if r.type_id in TYPE_REMUN_EXPAT:
                    indemnites['expat'] += montant * facteur

                if r.type_id in TYPE_REMUN_SCOLARITE:
                    indemnites['scolarite'] += montant * facteur

                if r.type_id in TYPE_REMUN_LOGEMENT:
                    indemnites['logement'] += montant * facteur

                if r.type_id in TYPE_REMUN_TRANSP:
                    indemnites['transp'] += montant * facteur

                if r.type_id in TYPE_REMUN_13E:
                    indemnites['13e'] += montant * facteur

                if r.type_id in TYPE_PRIME_INTERIM:
                    indemnites['interim'] += montant * facteur

                if r.type_id not in TYPE_REMUN_ALL_INDEMNITES \
                        and r.type.nature_remuneration == TYPE_NATURE_INDEMN \
                        and r.type_id != TYPE_REMUN_MAD:
                    indemnites['autre_recurr'] += montant * facteur

                if r.type_id in TYPE_PRIME_INSTALLATION:
                    primes['installation'] += montant * facteur

                if r.type_id in TYPE_PRIME_DEMENAG:
                    primes['demenagement'] += montant * facteur

                if r.type_id in TYPE_PRIME_AVION:
                    primes['avion'] += montant * facteur

                if r.type_id not in TYPE_PRIME_ALL and \
                        r.type.nature_remuneration == TYPE_NATURE_PAIEMENT:
                    primes['autre'] += montant * facteur

                if r.type_id in TYPE_CHARGE_PATRONALE:
                    charges['patronale'] += montant * facteur

                if r.type_id not in TYPE_CHARGE_ALL and \
                        r.type.nature_remuneration == TYPE_NATURE_CHARGES:
                    charges['autre'] += montant * facteur

                if r.type.nature_remuneration == TYPE_NATURE_INDEMN or \
                        r.type.id in (7,):
                    total_remun_annee['indemnite'] += montant * facteur

                if r.type.nature_remuneration == TYPE_NATURE_PAIEMENT:
                    total_remun_annee['accessoire'] += montant * facteur

                if r.type.nature_remuneration == TYPE_NATURE_CHARGES:
                    total_remun_annee['charges'] += montant * facteur

                if (r.type.nature_remuneration == TYPE_NATURE_TRAITEMENT and
                        r.type.id not in (TYPE_REMUN_MAD, 7)
                        ) or r.type_id == TYPE_REMUN_BSTG:
                    total_remun_annee['traitement'] += montant * facteur

            total_indemnites = sum(indemnites.values())

            masse_salariale_euro = rh.Remuneration(
                    montant=masse_salariale_annee, devise=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
                )
            if valeur_point:
                valeur_point_label = "%s %s" % (valeur_point.valeur,
                        valeur_point.devise.code)
            else:
                valeur_point_label = ""

            salaire_theorique = (
                    round(valeur_point.valeur * int(coefficient) * regime, 2) \
                    if valeur_point and coefficient and regime else None)

            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_label,
                    '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_feminin
                            if dossier.employe.genre == "F" else
                            dossier.poste.nom,
                    'niveau': 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': dossier_date_delta.days,
                    'devise': devise.code,
                    'salaire_bstg_annuel': bstg_remun.montant \
                            if bstg_remun else "",
                    'salaire_bstg_euro': bstg_remun_euro.montant \
                            if bstg_remun else "",
                    'organisme_bstg': dossier.organisme_bstg or "",
                    'salaire_theorique': salaire_theorique,
                    'salaire_base_brut': \
                            salaire_base * regime,
                    'salaire_complementaire': \
                            salaire_complement * regime,
                    #'salaire_total': None
                    'indemnite_fonctions': indemnites['fonc_resp'] * \
                            regime,
                    'indemnite_expat': indemnites['expat'] * regime,
                    'indemnite_scolarite': indemnites['scolarite'] * \
                            regime,
                    'indemnite_logement': indemnites['logement'] * \
                            regime,
                    'indemnite_transp': indemnites['transp'] * regime,
                    'indemnite_13e': indemnites['13e'] * regime,
                    'prime_interim': indemnites['interim'] * regime,
                    'indemnite_autre': indemnites['autre_recurr'] * \
                            regime,
                    'indemnite_sous_total': total_indemnites * regime,
                    'total_brut': (
                                total_indemnites + salaire_base +
                                salaire_complement
                            ) * regime,
                    'prime_installation': primes['installation'] * regime,
                    'prime_demenagement': primes['demenagement'] * regime,
                    'prime_avion': primes['avion'] * regime,
                    'prime_autre': primes['autre'] * regime,
                    'prime_sous_total': sum(primes.values()) * regime,
                    'charges_patronales': charges['patronale'] * regime,
                    'charges_autre': charges['autre'] * regime,
                    'charges_sous_total': sum(charges.values()) * regime,
                    'sous_total_traitement_annee': \
                            total_remun_annee['traitement'] * regime,
                    'sous_total_indemnite_annee': \
                            total_remun_annee['indemnite'] * regime,
                    'sous_total_accessoire_annee': \
                            total_remun_annee['accessoire'] * regime,
                    'sous_total_charges_annee': \
                            total_remun_annee['charges'] * regime,
                    'masse_salariale': masse_salariale * regime,
                    'masse_salariale_annee': masse_salariale_annee * regime,
                    'masse_salariale_annee_euro': \
                            masse_salariale_euro.montant * regime,
                    'sep': ods.Separator(),
            }

            grand_total_euro += round(masse_salariale_euro.montant * regime
                * (
                        dossier_date_delta.days / rapport_date_delta.days
                ), 2)

            self.rapport.append(item_rapport)

        self.rapport = sorted(self.rapport, key=lambda r: r['nom'])

        self.grand_totaux = (grand_total, grand_total_euro)

    def calculer_nombre_jours(self, debut, fin, debut_limite, fin_limite):
        """Calcul le nombre de jours entre fin et debut, sans dépasser
        les limites. Si debut ou fin set null, on prend debut_limite/fin_limi
        """

        if not debut:
            debut = debut_limite
        if not fin:
            fin = fin_limite

        if debut < debut_limite:
            debut = debut_limite
        if fin > fin_limite:
            fin = fin_limite

        fin += datetime.timedelta(days=1)
        return fin - debut

    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_fin_superieure_ou_egale_a_borne_gauche &
                    date_fin_inferieure_ou_egale_a_borne_droite &
                    (
                        date_debut_inferieure_ou_egale_a_borne_gauche |
                        date_debut_null
                    )
            ) | (
                    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_fin_null
                    )
            ) | (
                    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()

        nom = u"Masse salariale %s" % self.annee
        if self.region:
            nom += u" %s" % self.region
        elif self.implantation:
            nom += u" %s" % self.implantation

        table = self.doc.add_table(name=nom)

        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')