fix join SQL
[auf_rh_dae.git] / project / rh / lib.py
1 # -*- encoding: utf-8 -*-
2
3 from collections import defaultdict
4 import datetime
5
6 from django.db import models
7 from django import forms
8 from django.contrib import admin
9 from django.conf import settings
10 from django.db.models import Q
11 from auf.django.metadata.admin import AUFMetadataAdminMixin, AUFMetadataInlineAdminMixin, AUF_METADATA_READONLY_FIELDS
12 from project.rh import models as rh
13 from forms import DossierForm, ContratForm
14 from dae.utils import get_employe_from_user
15
16
17
18 # Override of the InlineModelAdmin to support the link in the tabular inline
19 class LinkedInline(admin.options.InlineModelAdmin):
20 template = "admin/linked.html"
21 admin_model_path = None
22
23 def __init__(self, *args):
24 super(LinkedInline, self).__init__(*args)
25 if self.admin_model_path is None:
26 self.admin_model_path = self.model.__name__.lower()
27
28
29 class ProtectRegionMixin(object):
30
31 def queryset(self, request):
32 qs = super(ProtectRegionMixin, self).queryset(request)
33
34 if request.user.is_superuser:
35 return qs
36
37 employe = get_employe_from_user(request.user)
38
39 q = Q(**{self.model.prefix_implantation: employe.implantation.region})
40 qs = qs.filter(q).distinct()
41 return qs
42
43 def has_change_permission(self, request, obj=None):
44 if request.user.is_superuser:
45 return True
46
47 if obj:
48 employe = get_employe_from_user(request.user)
49 if employe.implantation.region in obj.get_regions():
50 return True
51 else:
52 return False
53
54 return True
55
56
57 # Inlines
58
59 class ReadOnlyInlineMixin(object):
60 def get_readonly_fields(self, request, obj=None):
61 return [f.name for f in self.model._meta.fields if f.name not in AUF_METADATA_READONLY_FIELDS]
62
63
64 class AyantDroitInline(AUFMetadataInlineAdminMixin, admin.StackedInline):
65 model = models.Model # à remplacer dans admin.py
66 extra = 0
67
68 fieldsets = (
69 (None, {
70 'fields': (('nom', 'prenom'), ('nom_affichage', 'genre'), 'nationalite', 'date_naissance', 'lien_parente', )
71 }),
72 )
73
74
75 class AyantDroitCommentaireInline(AUFMetadataInlineAdminMixin, admin.TabularInline):
76 readonly_fields = ('owner', )
77 model = models.Model # à remplacer dans admin.py
78 extra = 1
79
80
81 class ContratInline(AUFMetadataInlineAdminMixin, admin.TabularInline):
82 form = ContratForm
83 model = models.Model # à remplacer dans admin.py
84 extra = 1
85
86
87 class DossierROInline(ReadOnlyInlineMixin, LinkedInline):
88 exclude = AUF_METADATA_READONLY_FIELDS
89 model = models.Model # à remplacer dans admin.py
90 extra = 0
91 can_delete = False
92
93 def has_add_permission(self, request=None):
94 return False
95
96 def has_change_permission(self, request, obj=None):
97 return False
98
99 def has_delete_permission(self, request, obj=None):
100 return False
101
102
103 class DossierCommentaireInline(AUFMetadataInlineAdminMixin, admin.TabularInline):
104 readonly_fields = ('owner', )
105 model = models.Model # à remplacer dans admin.py
106 extra = 1
107
108
109 class DossierPieceInline(admin.TabularInline):
110 model = models.Model # à remplacer dans admin.py
111 extra = 4
112
113
114 class EmployeInline(admin.TabularInline):
115 model = models.Model # à remplacer dans admin.py
116
117 class EmployeCommentaireInline(AUFMetadataInlineAdminMixin, admin.TabularInline):
118 readonly_fields = ('owner', )
119 model = models.Model # à remplacer dans admin.py
120 extra = 1
121
122
123 class EmployePieceInline(admin.TabularInline):
124 model = models.Model # à remplacer dans admin.py
125 extra = 4
126
127
128 class EvenementInline(AUFMetadataInlineAdminMixin, admin.TabularInline):
129 model = models.Model # à remplacer dans admin.py
130 extra = 1
131
132
133 class EvenementRemunerationInline(AUFMetadataInlineAdminMixin, admin.TabularInline):
134 model = models.Model # à remplacer dans admin.py
135 extra = 1
136
137
138 class PosteCommentaireInline(AUFMetadataInlineAdminMixin, admin.TabularInline):
139 readonly_fields = ('owner', )
140 model = models.Model # à remplacer dans admin.py
141 extra = 1
142
143
144 class PosteFinancementInline(admin.TabularInline):
145 model = models.Model # à remplacer dans admin.py
146
147
148 class PostePieceInline(admin.TabularInline):
149 model = models.Model # à remplacer dans admin.py
150
151
152 class RemunerationInline(AUFMetadataInlineAdminMixin, admin.TabularInline):
153 model = models.Model # à remplacer dans admin.py
154 extra = 1
155
156
157 class RemunerationROInline(ReadOnlyInlineMixin, RemunerationInline):
158 pass
159
160
161 class TypePosteInline(AUFMetadataInlineAdminMixin, admin.TabularInline):
162 model = models.Model # à remplacer dans admin.py
163
164
165 # Admins
166
167 class AyantDroitAdmin(AUFMetadataAdminMixin, ProtectRegionMixin, admin.ModelAdmin):
168 """
169 L'ajout d'un nouvel ayantdroit se fait dans l'admin de l'employé.
170 """
171 alphabet_filter = 'nom'
172 search_fields = ('nom', 'prenom', 'employe__nom', 'employe__prenom', )
173 list_display = ('_employe', 'lien_parente', '_ayantdroit', )
174 inlines = (AyantDroitCommentaireInline,)
175 readonly_fields = AUFMetadataAdminMixin.readonly_fields + ('employe',)
176 fieldsets = AUFMetadataAdminMixin.fieldsets + (
177 ("Lien avec l'employé", {
178 'fields': (('employe', 'lien_parente'), )
179 }),
180
181 ('Identification', {
182 'fields': (('nom', 'prenom'), ('nom_affichage', 'genre'), 'nationalite', 'date_naissance', )
183 }),
184 )
185
186 def save_formset(self, request, form, formset, change):
187 instances = formset.save(commit=False)
188 for instance in instances:
189 if instance.__class__ == rh.AyantDroitCommentaire:
190 instance.owner = request.user
191 instance.save()
192
193 def _ayantdroit(self, obj):
194 return unicode(obj)
195 _ayantdroit.short_description = u'Ayant droit'
196
197 def _employe(self, obj):
198 return unicode(obj.employe)
199 _employe.short_description = u'Employé'
200
201 def has_add_permission(self, request):
202 return False
203
204 class AyantDroitCommentaireAdmin(admin.ModelAdmin):
205 pass
206
207
208 class ClassementAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
209 fieldsets = AUFMetadataAdminMixin.fieldsets + (
210 (None, {
211 'fields': ('type', 'echelon', 'degre', 'coefficient', )
212 }),
213 )
214
215
216 class CommentaireAdmin(admin.ModelAdmin):
217 pass
218
219
220 #class ContratAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
221 # form = ContratForm
222 # alphabet_filter = 'dossier__employe__nom'
223 # search_fields = ('dossier__employe__nom', 'dossier__employe__prenom', 'dossier__poste__nom', 'dossier__poste__nom_feminin', )
224 # list_display = ('id', '_employe', '_poste', 'date_debut', 'date_fin', '_implantation', )
225 # fieldsets = AUFMetadataAdminMixin.fieldsets + (
226 # (None, {
227 # 'fields': ('dossier', 'type_contrat', 'date_debut', 'date_fin', )
228 # }),
229 # )
230 #
231 # def lookup_allowed(self, key, value):
232 # if key in ('dossier__employe__nom__istartswith', ):
233 # return True
234 #
235 # def _employe(self, obj):
236 # return unicode(obj.dossier.employe)
237 # _employe.short_description = "Employé"
238 #
239 # def _poste(self, obj):
240 # return obj.dossier.poste.nom
241 # _poste.short_description = "Poste"
242 #
243 # def _implantation(self, obj):
244 # return obj.dossier.poste.implantation
245 # _poste.short_description = "Implantation"
246
247 class DeviseAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
248 fieldsets = AUFMetadataAdminMixin.fieldsets + (
249 (None, {
250 'fields': ('code', 'nom', ),
251 }),
252 )
253
254
255 class DossierAdmin(AUFMetadataAdminMixin, ProtectRegionMixin, admin.ModelAdmin,):
256 form = DossierForm
257 alphabet_filter = 'employe__nom'
258 search_fields = ('employe__nom', 'employe__prenom', 'poste__nom', 'poste__nom_feminin')
259 list_display = ('_employe', '_poste', 'date_debut', 'date_fin', 'date_modification', '_actif')
260 inlines = (DossierPieceInline, ContratInline,
261 RemunerationInline,
262 #EvenementInline,
263 DossierCommentaireInline,
264 )
265 fieldsets = AUFMetadataAdminMixin.fieldsets + (
266 (None, {
267 'fields': ('employe', 'poste', 'statut', 'organisme_bstg',)
268 }),
269 ('Recrutement', {
270 'fields': ('statut_residence', 'remplacement', 'remplacement_de', )
271 }),
272 ('Rémunération', {
273 'fields': ('classement', ('regime_travail', 'regime_travail_nb_heure_semaine'),)
274 }),
275 ('Occupation du Poste par cet Employe', {
276 'fields': (('date_debut', 'date_fin'), )
277 }),
278 )
279
280 add_form_template = 'admin/change_form.html'
281
282 def queryset(self, request):
283 return self.model.actifs.all()
284
285 class Media:
286 js = ('js/dossier.js',)
287
288 def lookup_allowed(self, key, value):
289 if key in ('employe__nom__istartswith', 'actif__exact', ):
290 return True
291
292 def _actif(self, dossier):
293 if dossier.employe.actif:
294 html = """<img alt="True" src="%simg/admin/icon-yes.gif">"""
295 else:
296 html = """<img alt="False" src="%simg/admin/icon-no.gif">"""
297 return html % settings.ADMIN_MEDIA_PREFIX
298 _actif.allow_tags = u'Employé actif'
299 _actif.short_description = u'Employé actif'
300 _actif.admin_order_field = 'employe__actif'
301
302 def _poste(self, dossier):
303 return unicode(dossier.poste.nom)
304 _poste.short_description = u'Poste'
305 _poste.admin_order_field = 'poste__nom'
306
307 def _employe(self, dossier):
308 return unicode(dossier.employe)
309 _employe.short_description = u'Employé'
310 _employe.admin_order_field = 'employe__nom'
311
312 def save_formset(self, request, form, formset, change):
313 instances = formset.save(commit=False)
314 for instance in instances:
315 if instance.__class__ == rh.DossierCommentaire:
316 instance.owner = request.user
317 instance.save()
318
319 def render_change_form(self, request, context, *args, **kwargs):
320 obj = kwargs.get('obj', None)
321
322 if not obj:
323 return super(DossierAdmin, self).render_change_form(request, context, *args, **kwargs)
324
325
326 remun, remun_sum, remun_sum_euro = calc_remun(obj)
327
328 extra = {
329 'remun': remun,
330 'remun_sum': remun_sum,
331 'remun_sum_euro': remun_sum_euro,
332 'employe': obj.employe,
333 'poste_nom': obj.poste.nom,
334 'poste_service': obj.poste.service,
335 'poste_implantation': obj.poste.implantation,
336 }
337
338 context.update(extra)
339
340 return super(DossierAdmin, self).render_change_form(request, context, *args, **kwargs)
341
342
343 class DossierInactifAdmin(DossierAdmin):
344 def queryset(self, request):
345 return self.model.inactifs.all()
346
347
348 class DossierPieceAdmin(admin.ModelAdmin):
349 pass
350
351
352 class DossierCommentaireAdmin(admin.ModelAdmin):
353 pass
354
355
356 class EmployeAdminForm(forms.ModelForm):
357 class Meta:
358 model = rh.Employe
359
360 def __init__(self, *args, **kwargs):
361 super(EmployeAdminForm, self).__init__(*args, **kwargs)
362 self.fields['date_naissance'].widget = forms.widgets.DateInput()
363
364
365 class EmployeAdmin(AUFMetadataAdminMixin, ProtectRegionMixin, admin.ModelAdmin):
366 alphabet_filter = 'nom'
367 search_fields = ('id', 'nom', 'prenom', 'nom_affichage', )
368 ordering = ('nom', )
369 actions = ('desactiver', )
370 form = EmployeAdminForm
371 list_display = ('id', 'nom', 'prenom', 'date_modification', 'user_modification',)
372 list_display_links = ('id', 'nom',)
373 inlines = (AyantDroitInline,
374 DossierROInline,
375 EmployePieceInline,
376 EmployeCommentaireInline)
377 fieldsets = AUFMetadataAdminMixin.fieldsets + (
378 ('Identification', {
379 'fields': (('nom', 'prenom'), ('nom_affichage', 'genre'), 'nationalite', 'date_naissance', )
380 }),
381 ('Informations personnelles', {
382 'fields': ('situation_famille', 'date_entree', )
383 }),
384 ('Coordonnées', {
385 'fields': (('tel_domicile', 'tel_cellulaire'), ('adresse', 'ville'), ('code_postal', 'province'), 'pays', )
386 }),
387 )
388
389 add_form_template = 'admin/change_form.html'
390
391 def queryset(self, request):
392 qs = super(EmployeAdmin, self).queryset(request)
393 return qs.filter(actif=True).select_related(depth=1)
394
395 def save_formset(self, request, form, formset, change):
396 instances = formset.save(commit=False)
397 for instance in instances:
398 if instance.__class__ == rh.EmployeCommentaire:
399 instance.owner = request.user
400 instance.save()
401
402 def render_change_form(self, request, context, *args, **kwargs):
403 obj = kwargs.get('obj', None)
404
405 if not obj:
406 return super(EmployeAdmin, self).render_change_form(request, context, *args, **kwargs)
407
408 remun = {}
409 remun_sum = 0
410 remun_sum_euro = 0
411 dossiers = obj.dossiers.all()
412
413 for dossier in dossiers:
414 this_remun, this_remun_sum, this_remun_sum_euro = calc_remun(dossier)
415
416 for item in this_remun:
417 if item not in remun:
418 remun[item] = this_remun[item]
419 else:
420 remun[item][0] += this_remun[item][0]
421 remun[item][1] += this_remun[item][1]
422
423 remun_sum += this_remun_sum
424 remun_sum_euro += this_remun_sum_euro
425
426 extra = {
427 'remun': remun,
428 'remun_sum': remun_sum,
429 'remun_sum_euro': remun_sum_euro,
430 }
431
432 context.update(extra)
433
434 return super(EmployeAdmin, self).render_change_form(request, context, *args, **kwargs)
435
436
437 class EmployeInactifAdmin(EmployeAdmin):
438 def queryset(self, request):
439 return self.model.inactifs.all()
440
441
442 class EmployeCommentaireAdmin(admin.ModelAdmin):
443 pass
444
445
446 class EmployePieceAdmin(admin.ModelAdmin):
447 pass
448
449
450 class EvenementAdmin(admin.ModelAdmin):
451 inlines = (EvenementRemunerationInline,)
452
453
454 class EvenementRemunerationAdmin(admin.ModelAdmin):
455 pass
456
457
458 class FamilleEmploiAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
459 inlines = (TypePosteInline,)
460 fieldsets = AUFMetadataAdminMixin.fieldsets + (
461 (None, {
462 'fields': ('nom', )
463 }),
464 )
465
466
467 class OrganismeBstgAdmin(AUFMetadataAdminMixin, ProtectRegionMixin, admin.ModelAdmin):
468 search_fields = ('nom', )
469 list_display = ('nom', 'type', 'pays', )
470 inlines = (DossierROInline,)
471 fieldsets = AUFMetadataAdminMixin.fieldsets + (
472 (None, {
473 'fields': ('nom', 'type', 'pays', )
474 }),
475 )
476
477
478 class PosteAdmin(AUFMetadataAdminMixin, ProtectRegionMixin, admin.ModelAdmin):
479 alphabet_filter = 'nom'
480 search_fields = ('nom', 'implantation__code', 'implantation__nom', 'implantation__region__code', 'implantation__region__nom', )
481 list_display = ('nom', 'implantation', 'service', 'type_poste', 'date_debut', 'date_fin', )
482 fieldsets = AUFMetadataAdminMixin.fieldsets + (
483 (None, {
484 'fields': (('nom', 'nom_feminin'), 'implantation', 'type_poste',
485 'service', 'responsable')
486 }),
487 ('Contrat', {
488 'fields': (('regime_travail', 'regime_travail_nb_heure_semaine'), )
489 }),
490 ('Recrutement', {
491 'fields': (('local', 'expatrie', 'mise_a_disposition', 'appel'),)
492 }),
493 ('Rémunération', {
494 'fields': (('classement_min', 'classement_max'),
495 ('valeur_point_min', 'valeur_point_max'),
496 ('devise_min', 'devise_max'),
497 ('salaire_min', 'salaire_max'),
498 ('indemn_min', 'indemn_max'),
499 ('autre_min', 'autre_max'))
500 }),
501 ('Comparatifs de rémunération', {
502 'fields': ('devise_comparaison',
503 ('comp_locale_min', 'comp_locale_max'),
504 ('comp_universite_min', 'comp_universite_max'),
505 ('comp_fonctionpub_min', 'comp_fonctionpub_max'),
506 ('comp_ong_min', 'comp_ong_max'),
507 ('comp_autre_min', 'comp_autre_max'))
508 }),
509 ('Justification', {
510 'fields': ('justification',)
511 }),
512 ('Autres Metadata', {
513 'fields': ('date_validation', ('date_debut', 'date_fin'))
514 }),
515 )
516
517 inlines = (PosteFinancementInline,
518 PostePieceInline,
519 DossierROInline,
520 PosteCommentaireInline, )
521
522 def save_formset(self, request, form, formset, change):
523 instances = formset.save(commit=False)
524 for instance in instances:
525 if instance.__class__ == rh.PosteCommentaire:
526 instance.owner = request.user
527 instance.save()
528 formset.save_m2m()
529
530
531 class PosteCommentaireAdmin(admin.ModelAdmin):
532 pass
533
534
535 class PosteFinancementAdmin(admin.ModelAdmin):
536 pass
537
538
539 class PostePieceAdmin(admin.ModelAdmin):
540 pass
541
542
543 class RemunerationAdmin(admin.ModelAdmin):
544 pass
545
546
547 class ResponsableImplantationAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
548 fieldsets = AUFMetadataAdminMixin.fieldsets + (
549 (None, {
550 'fields': ('employe', 'implantation', ),
551 }),
552 )
553
554
555 class ServiceAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
556 list_display = ('nom', 'actif', )
557 fieldsets = AUFMetadataAdminMixin.fieldsets + (
558 (None, {
559 'fields': ('nom', ),
560 }),
561 )
562
563 class StatutAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
564 fieldsets = AUFMetadataAdminMixin.fieldsets + (
565 (None, {
566 'fields': ('code', 'nom', ),
567 }),
568 )
569
570 class TauxChangeAdmin(admin.ModelAdmin):
571 list_display = ('taux', 'devise', 'annee', )
572 list_filter = ('devise', )
573 fieldsets = AUFMetadataAdminMixin.fieldsets + (
574 (None, {
575 'fields': ('taux', 'devise', 'annee', ),
576 }),
577 )
578
579 class TypeContratAdmin(admin.ModelAdmin):
580 inlines = (ContratInline,)
581 fieldsets = AUFMetadataAdminMixin.fieldsets + (
582 (None, {
583 'fields': ('nom', 'nom_long', ),
584 }),
585 )
586
587
588 class TypePosteAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
589 search_fields = ('nom', 'nom_feminin', )
590 list_display = ('nom', 'famille_emploi', )
591 list_filter = ('famille_emploi', )
592 fieldsets = AUFMetadataAdminMixin.fieldsets + (
593 (None, {
594 'fields': ('nom', 'nom_feminin', 'is_responsable', 'famille_emploi', )
595 }),
596 )
597
598
599 class TypeRemunerationAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
600 list_display = ('nom', 'type_paiement', 'nature_remuneration', )
601 #inlines = (RemunerationROInline,) utilité?
602 fieldsets = AUFMetadataAdminMixin.fieldsets + (
603 (None, {
604 'fields': ('nom', 'type_paiement', 'nature_remuneration', )
605 }),
606 )
607
608
609 class TypeRevalorisationAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
610 #inlines = (RemunerationROInline,) utilité?
611 fieldsets = AUFMetadataAdminMixin.fieldsets + (
612 (None, {
613 'fields': ('nom', )
614 }),
615 )
616
617
618 class ValeurPointAdmin(AUFMetadataAdminMixin, admin.ModelAdmin):
619 list_display = ('_devise_code', '_devise_nom', 'annee', 'valeur', )
620 fieldsets = AUFMetadataAdminMixin.fieldsets + (
621 (None, {
622 'fields': ('valeur', 'devise', 'implantation', 'annee', )
623 }),
624 )
625
626 def _devise_code(self, obj):
627 return obj.devise.code
628 _devise_code.short_description = "Code de la devise"
629
630 def _devise_nom(self, obj):
631 return obj.devise.nom
632 _devise_nom.short_description = "Nom de la devise"
633
634
635 def calc_remun(dossier):
636 thisyear = datetime.date.today().year
637 thisyearfilter = Q(date_debut__year=thisyear) | Q(date_fin__year=thisyear)
638
639 remunnow = dossier.rh_remuneration_remunerations.filter(thisyearfilter)
640
641 remun_sum = 0
642 remun_sum_euro = 0
643 sums = defaultdict(int)
644 sums_euro = defaultdict(int)
645 for r in remunnow:
646 nature = r.type.nature_remuneration
647 sums[nature] += r.montant
648 sums_euro[nature] += r.montant_euro()
649 remun_sum += r.montant
650 remun_sum_euro += r.montant_euro()
651
652 remun = {}
653 sums = dict(sums)
654 for n, s in sums.iteritems():
655 remun[n] = [sums[n], sums_euro[n]]
656
657 return remun, remun_sum, remun_sum_euro