Commit | Line | Data |
---|---|---|
98d6eb6c JPC |
1 | # -*- encoding: utf-8 -*- |
2 | import time | |
3 | import datetime | |
899aea3b JPC |
4 | import csv |
5 | import StringIO | |
98d6eb6c JPC |
6 | |
7 | from django.db.models import Q | |
8 | ||
9 | from datamaster_modeles import models as ref | |
98d6eb6c JPC |
10 | import rh.models as rh |
11 | ||
12 | ||
13 | KEY_DATE_DEBUT = "debut" | |
14 | KEY_DATE_FIN = "fin" | |
4d17560e JPC |
15 | |
16 | TYPE_REMUN_BSTG = (3,) | |
17 | TYPE_REMUN_MAD = (2,) | |
18 | TYPE_REMUN_BASE = (1,) | |
95ad7aab | 19 | TYPE_REMUN_FONC_RESP = (7, 8) |
4d17560e JPC |
20 | TYPE_REMUN_EXPAT = (4,) |
21 | TYPE_REMUN_LOGEMENT = (6,) | |
22 | TYPE_REMUN_TRANSP = (9,) | |
23 | TYPE_REMUN_13E = (18,) | |
95ad7aab | 24 | TYPE_REMUN_AUTRE_RECURR_NOT = (1, 2, 3, 4, 6, 7, 8, 9, 18, 13, 14, 15, 19) |
4d17560e | 25 | TYPE_PAIEMENT_PONCTUEL = u"Ponctuel" |
95ad7aab JPC |
26 | TYPE_PRIME_INTERIM = (19,) |
27 | TYPE_PRIME_INSTALLATION = (13,) | |
28 | TYPE_PRIME_DEMENAG = (15,) | |
29 | TYPE_PRIME_AVION = (14,) | |
899aea3b JPC |
30 | TYPE_NATURE_PAIEMENT = u"Accessoire" |
31 | TYPE_NATURE_CHARGES = u"Charges" | |
32 | ||
9163be6f | 33 | |
98d6eb6c JPC |
34 | class MasseSalariale(): |
35 | """ Rapport de la masse salariale. """ | |
36 | ||
37 | def __init__(self, date_debut, date_fin): | |
38 | """ date_debut: date de début pour les données temporelles | |
39 | date_fin: idem | |
40 | """ | |
41 | date_debut = datetime.date( | |
42 | *time.strptime(date_debut, "%d-%m-%Y")[0:3] | |
43 | ) | |
44 | date_fin = datetime.date(*time.strptime(date_fin, "%d-%m-%Y")[0:3]) | |
9163be6f | 45 | rapport_date_delta = date_fin - date_debut |
98d6eb6c JPC |
46 | |
47 | self.annee = date_fin.year | |
48 | ||
49 | self.devise_base = rh.Devise.objects.filter(code='EUR')[0] | |
50 | self.taux_change = {} | |
51 | ||
52 | q_range = self.build_qs("date_", date_debut, date_fin) | |
53 | q_range_d = self.build_qs("dossier__date_", date_debut, date_fin) | |
54 | remunerations = rh.Remuneration.objects.filter(q_range) \ | |
55 | .filter(q_range_d) \ | |
56 | .exclude(supprime=True) \ | |
899aea3b JPC |
57 | .select_related( |
58 | "dossier", "dossier_employe", "dossier_poste", "type" | |
59 | ) | |
98d6eb6c JPC |
60 | |
61 | employes = {} | |
62 | for r in remunerations: | |
63 | if r.dossier.employe_id not in employes: | |
9163be6f JPC |
64 | employes[r.dossier.employe_id] = { |
65 | 'dossiers': set(), | |
66 | 'remunerations': [] | |
67 | } | |
98d6eb6c JPC |
68 | employes[r.dossier.employe_id]['remunerations'].append(r) |
69 | employes[r.dossier.employe_id]['dossiers'].add(r.dossier) | |
70 | ||
71 | self.employes = employes | |
9163be6f | 72 | |
98d6eb6c JPC |
73 | self.rapport = [] |
74 | ||
75 | pays_list = ref.Pays.objects.all() | |
76 | valeurs_point_par_imp = \ | |
77 | dict( | |
9163be6f JPC |
78 | (v.implantation.id, v) for v in \ |
79 | rh.ValeurPoint.objects.filter(annee=self.annee).all() | |
98d6eb6c JPC |
80 | ) |
81 | for item in self.employes.values(): | |
82 | dossiers = item['dossiers'] | |
83 | remuns = item['remunerations'] | |
84 | #TODO, choisir le dossier primaire | |
85 | if len(dossiers) > 1: | |
86 | #TODO | |
87 | pass | |
88 | dossier = list(dossiers)[0] | |
95ad7aab | 89 | regime = float(dossier.poste.regime_travail) / 100 |
9163be6f | 90 | |
98d6eb6c JPC |
91 | if dossier.poste.expatrie: |
92 | statut = "E" | |
93 | else: | |
94 | statut = "L" | |
95 | ||
9163be6f JPC |
96 | #on détermine la date du début et fin du dossier si année en cours |
97 | try: | |
98 | d_date_fin = dossier.date_fin \ | |
99 | if dossier.date_fin.year == date_fin.year else None | |
100 | except AttributeError: | |
101 | d_date_fin = None | |
102 | try: | |
103 | d_date_debut = dossier.date_debut \ | |
104 | if dossier.date_debut.year == date_fin.year else None | |
105 | except AttributeError: | |
106 | d_date_debut = None | |
98d6eb6c JPC |
107 | |
108 | pays = \ | |
109 | pays_list[dossier.poste.implantation.adresse_physique_pays.id] | |
110 | ||
111 | #on détermine si les rémunérations sont tous dans la même devise | |
112 | devise = remuns[0].devise | |
113 | meme_devise = True | |
114 | for r in remuns[1:]: | |
115 | if devise != r.devise: | |
116 | meme_devise = False | |
117 | ||
118 | if not meme_devise: | |
119 | for r in remuns: | |
120 | self.convertir(r) | |
121 | ||
122 | bstg_dossier = None | |
123 | for d in dossiers: | |
124 | if d.organisme_bstg: | |
125 | bstg_dossier = d | |
126 | ||
9163be6f | 127 | bstg_remun = None |
98d6eb6c | 128 | if bstg_dossier: |
9163be6f | 129 | for r in bstg_dossier.rh_remunerations.all(): |
4d17560e | 130 | if r.type in TYPE_REMUN_BSTG: |
9163be6f | 131 | bstg_remun = r |
98d6eb6c | 132 | |
4d17560e JPC |
133 | salaire_complement = 0.0 |
134 | salaire_base = 0.0 | |
135 | indemnites = { | |
136 | 'fonc_resp': 0.0, | |
137 | 'expat': 0.0, | |
138 | 'logement': 0.0, | |
139 | 'transp': 0.0, | |
140 | '13e': 0.0, | |
141 | 'autre_recurr': 0.0, | |
142 | } | |
95ad7aab JPC |
143 | |
144 | primes = { | |
145 | 'interim': 0.0, | |
146 | 'installation': 0.0, | |
147 | 'demenagement': 0.0, | |
148 | 'avion': 0.0, | |
149 | 'autre': 0.0, | |
150 | } | |
899aea3b | 151 | charges = 0.0 |
4d17560e JPC |
152 | for r in remuns: |
153 | montant = float(r.montant) | |
95ad7aab JPC |
154 | |
155 | if r.type_id in TYPE_REMUN_MAD: | |
4d17560e JPC |
156 | salaire_complement += montant |
157 | ||
95ad7aab | 158 | if r.type_id in TYPE_REMUN_BASE: |
4d17560e JPC |
159 | salaire_base += montant |
160 | ||
95ad7aab | 161 | if r.type_id in TYPE_REMUN_FONC_RESP: |
4d17560e JPC |
162 | indemnites['fonc_resp'] += montant |
163 | ||
95ad7aab | 164 | if r.type_id in TYPE_REMUN_EXPAT: |
4d17560e JPC |
165 | indemnites['expat'] += montant |
166 | ||
95ad7aab | 167 | if r.type_id in TYPE_REMUN_LOGEMENT: |
4d17560e JPC |
168 | indemnites['logement'] += montant |
169 | ||
95ad7aab | 170 | if r.type_id in TYPE_REMUN_TRANSP: |
4d17560e JPC |
171 | indemnites['transp'] += montant |
172 | ||
95ad7aab | 173 | if r.type_id in TYPE_REMUN_13E: |
4d17560e JPC |
174 | indemnites['13e'] += montant |
175 | ||
95ad7aab | 176 | if r.type_id not in TYPE_REMUN_AUTRE_RECURR_NOT \ |
4d17560e JPC |
177 | and r.type.type_paiement != TYPE_PAIEMENT_PONCTUEL: |
178 | indemnites['autre_recurr'] += montant | |
179 | ||
95ad7aab JPC |
180 | if r.type_id in TYPE_PRIME_INTERIM: |
181 | primes['interim'] += montant | |
182 | ||
183 | if r.type_id in TYPE_PRIME_INSTALLATION: | |
184 | primes['installation'] += montant | |
185 | ||
186 | if r.type_id in TYPE_PRIME_DEMENAG: | |
187 | primes['demenagement'] += montant | |
188 | ||
189 | if r.type_id in TYPE_PRIME_AVION: | |
190 | primes['avion'] += montant | |
191 | ||
192 | if r.type_id not in TYPE_REMUN_AUTRE_RECURR_NOT and \ | |
193 | r.type.nature_remuneration == TYPE_NATURE_PAIEMENT: | |
194 | primes['autre'] += montant | |
195 | ||
899aea3b JPC |
196 | if r.type.nature_remuneration == TYPE_NATURE_CHARGES: |
197 | charges += montant | |
198 | ||
4d17560e JPC |
199 | total_indemnites = sum(indemnites.values()) |
200 | ||
9163be6f | 201 | #Calcul du nombre de jours pour ce dossier. |
4d17560e JPC |
202 | if dossier.date_debut and dossier.date_debut > date_debut and \ |
203 | not dossier.date_fin: | |
204 | date_delta = date_fin - dossier.date_fin | |
205 | elif dossier.date_fin and dossier.date_fin < date_fin and \ | |
206 | not dossier.date_debut: | |
207 | date_delta = dossier.date_fin - date_debut | |
208 | elif dossier.date_fin and dossier.date_debut: | |
209 | date_delta = dossier.date_fin - date_debut | |
210 | else: | |
9163be6f | 211 | date_delta = rapport_date_delta |
98d6eb6c | 212 | |
899aea3b JPC |
213 | masse_salariale = (salaire_base + total_indemnites + \ |
214 | sum(primes.values()) + charges) | |
215 | ||
98d6eb6c JPC |
216 | item_rapport = { |
217 | 'bureau': dossier.poste.implantation.region.code, | |
218 | 'pays': pays, | |
219 | 'implantation': dossier.poste.implantation.nom_court, | |
220 | 'type_implantation': dossier.poste.implantation.type, | |
221 | #'imputation': None, | |
9163be6f JPC |
222 | 'valeur_point': |
223 | #todo valeur du point si pas présent | |
98d6eb6c JPC |
224 | valeurs_point_par_imp.get( |
225 | dossier.poste.implantation_id | |
9163be6f | 226 | ), |
98d6eb6c JPC |
227 | 'numero_employe': dossier.employe_id, |
228 | 'nom': dossier.employe.nom.upper(), | |
229 | 'prenom': dossier.employe.prenom, | |
230 | 'type_de_poste': dossier.poste.type_poste.nom, | |
231 | 'intitule_de_poste': dossier.poste.nom, | |
232 | 'niveau': dossier.classement, | |
9163be6f | 233 | 'point': "%s" % |
98d6eb6c | 234 | dossier.classement.coefficient \ |
9163be6f JPC |
235 | if dossier.classement and |
236 | dossier.classement.coefficient | |
98d6eb6c | 237 | else "", |
95ad7aab | 238 | 'regime_de_travail': "%s %%" % int(regime * 100), |
98d6eb6c JPC |
239 | 'local_expatrie': statut, |
240 | 'statut': dossier.statut.code, | |
241 | 'date_fin_contrat': dossier.date_fin, | |
9163be6f JPC |
242 | 'date_debut': d_date_debut, |
243 | 'date_fin': d_date_fin, | |
98d6eb6c JPC |
244 | 'nb_jours': date_delta.days, |
245 | 'devise': remuns[0].devise, | |
9163be6f JPC |
246 | 'salaire_bstg_annuel': bstg_remun.montant \ |
247 | if bstg_remun else None, | |
248 | 'salaire_bstg_total': | |
249 | self.convertir(bstg_remun) \ | |
250 | * regime * date_delta.days \ | |
251 | if bstg_remun else None, | |
4d17560e JPC |
252 | 'organisme_bstg': dossier.organisme_bstg, |
253 | 'salaire_base_brut': \ | |
95ad7aab JPC |
254 | salaire_base * regime * ( |
255 | date_delta.days / rapport_date_delta.days | |
256 | ), | |
257 | 'salaire_complementaire': \ | |
258 | salaire_complement * regime * ( | |
259 | date_delta.days / rapport_date_delta.days | |
260 | ), | |
261 | #'salaire_total': None | |
262 | 'indemnite_fonctions': indemnites['fonc_resp'] * regime * \ | |
263 | (date_delta.days / rapport_date_delta.days), | |
264 | 'indemnite_expat': indemnites['expat'] * regime * \ | |
265 | (date_delta.days / rapport_date_delta.days), | |
266 | 'indemnite_logement': indemnites['logement'] * regime * \ | |
267 | (date_delta.days / rapport_date_delta.days), | |
268 | 'indemnites_transp': indemnites['transp'] * regime * \ | |
269 | (date_delta.days / rapport_date_delta.days), | |
270 | 'indemnites_13e': indemnites['13e'] * regime * \ | |
271 | (date_delta.days / rapport_date_delta.days), | |
272 | 'indemnites_autre': indemnites['autre_recurr'] * regime * \ | |
273 | (date_delta.days / rapport_date_delta.days), | |
274 | 'indemnites_total': total_indemnites * regime * \ | |
275 | (date_delta.days / rapport_date_delta.days), | |
276 | 'total_brut': ( | |
277 | total_indemnites + salaire_base + | |
278 | salaire_complement | |
279 | ) * regime * ( | |
280 | date_delta.days / rapport_date_delta.days | |
281 | ), | |
282 | 'prime_interim': primes['interim'] * regime * \ | |
283 | (date_delta.days / rapport_date_delta.days), | |
284 | 'prime_installation': primes['installation'] * regime * \ | |
285 | (date_delta.days / rapport_date_delta.days), | |
286 | 'prime_demenagement': primes['demenagement'] * regime * \ | |
287 | (date_delta.days / rapport_date_delta.days), | |
288 | 'prime_avion': primes['avion'] * regime * \ | |
289 | (date_delta.days / rapport_date_delta.days), | |
290 | 'prime_autre': primes['autre'] * regime * \ | |
291 | (date_delta.days / rapport_date_delta.days), | |
292 | 'prime_total': sum(primes.values()) * regime * \ | |
293 | (date_delta.days / rapport_date_delta.days), | |
899aea3b JPC |
294 | 'charges_patronales': charges, |
295 | 'charges_patronales_%s' % self.annee: charges * regime * \ | |
296 | (date_delta.days / rapport_date_delta.days), | |
297 | 'masse_salariale': masse_salariale, | |
298 | 'masse_salariale_%s' % self.annee: masse_salariale * \ | |
299 | regime * ( | |
300 | date_delta.days / rapport_date_delta.days | |
301 | ), | |
302 | ||
303 | } | |
98d6eb6c JPC |
304 | |
305 | self.rapport.append(item_rapport) | |
306 | ||
98d6eb6c JPC |
307 | def build_qs(self, prefix, date_debut, date_fin): |
308 | date_debut_null = \ | |
309 | Q(**{"%s%s__isnull" % (prefix, KEY_DATE_DEBUT): True}) | |
310 | date_fin_null = \ | |
311 | Q(**{"%s%s__isnull" % (prefix, KEY_DATE_FIN): True}) | |
312 | date_debut_superieure_ou_egale_a_borne_gauche = \ | |
313 | Q(**{"%s%s__gte" % (prefix, KEY_DATE_DEBUT): date_debut}) | |
314 | date_debut_inferieure_ou_egale_a_borne_gauche = \ | |
315 | Q(**{"%s%s__lte" % (prefix, KEY_DATE_DEBUT): date_debut}) | |
316 | date_fin_superieure_ou_egale_a_borne_gauche = \ | |
317 | Q(**{"%s%s__gte" % (prefix, KEY_DATE_FIN): date_debut}) | |
318 | date_fin_inferieure_ou_egale_a_borne_droite = \ | |
319 | Q(**{"%s%s__lte" % (prefix, KEY_DATE_FIN): date_fin}) | |
320 | date_debut_inferieure_ou_egale_a_borne_droite = \ | |
321 | Q(**{"%s%s__lte" % (prefix, KEY_DATE_DEBUT): date_fin}) | |
322 | date_fin_superieure_ou_egale_a_borne_droite = \ | |
323 | Q(**{"%s%s__gte" % (prefix, KEY_DATE_FIN): date_fin}) | |
324 | ||
325 | q_range = \ | |
326 | ( | |
327 | date_debut_null & date_fin_null | |
328 | ) | ( | |
329 | date_debut_inferieure_ou_egale_a_borne_gauche & | |
330 | date_fin_superieure_ou_egale_a_borne_gauche & | |
331 | date_fin_inferieure_ou_egale_a_borne_droite | |
332 | ) | ( | |
333 | date_debut_superieure_ou_egale_a_borne_gauche & | |
334 | date_debut_inferieure_ou_egale_a_borne_droite & | |
335 | date_fin_superieure_ou_egale_a_borne_droite | |
336 | ) | ( | |
337 | date_debut_inferieure_ou_egale_a_borne_gauche & | |
338 | date_fin_superieure_ou_egale_a_borne_droite | |
339 | ) | ( | |
340 | date_debut_null & | |
341 | date_fin_superieure_ou_egale_a_borne_droite | |
342 | ) | ( | |
343 | date_debut_inferieure_ou_egale_a_borne_gauche & | |
344 | date_fin_null | |
345 | ) | |
346 | ||
347 | return q_range | |
348 | ||
349 | def convertir(self, remuneration): | |
9163be6f JPC |
350 | if remuneration.devise != self.devise_base: |
351 | remuneration.montant = float(remuneration.montant) * \ | |
352 | self.trouver_taux(remuneration.devise).taux | |
353 | remuneration.devise = self.devise_base | |
98d6eb6c JPC |
354 | |
355 | def trouver_taux(self, devise): | |
356 | if devise.code not in self.taux_change: | |
357 | t = rh.TauxChange.objects.filter( | |
358 | devise=devise, annee=self.annee | |
359 | )[0] | |
360 | self.taux_change[devise.code] = t | |
361 | return self.taux_change[devise.code] | |
899aea3b JPC |
362 | |
363 | def csv(self): | |
364 | self.csv_handle = StringIO.StringIO() | |
365 | csv_writer = csv.writer(self.csv_handle) | |
366 | csv_writer.writerow(self.rapport[0].keys()) | |
367 | csv_writer.writerow([row.values() for row in self.rapport]) | |
368 |