#!/usr/bin/env python # -*- coding: utf-8 -*- # Copyright (C) 2012-2013 Étienne Loks # This program is free software: you can redistribute it and/or modify # it under the terms of the GNU Affero General Public License as # published by the Free Software Foundation, either version 3 of the # License, or (at your option) any later version. # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU Affero General Public License for more details. # You should have received a copy of the GNU Affero General Public License # along with this program. If not, see . # See the file COPYING for details. import datetime from itertools import groupby from django.conf import settings from django.contrib.gis.db import models from django.db.models import Q, Count, Sum, Max, Avg from django.db.models.signals import post_save, m2m_changed from django.utils.translation import ugettext_lazy as _, ugettext from ishtar_common.utils import cached_label_changed from ishtar_common.models import GeneralType, BaseHistorizedItem, \ HistoricalRecords, LightHistorizedItem, OwnPerms, Department, Source,\ Person, Organization, Town, Dashboard, IshtarUser FILES_AVAILABLE = 'archaeological_files' in settings.INSTALLED_APPS if FILES_AVAILABLE: from archaeological_files.models import File class OperationType(GeneralType): preventive = models.BooleanField(_(u"Is preventive"), default=True) class Meta: verbose_name = _(u"Operation type") verbose_name_plural = _(u"Operation types") ordering = ['label'] @classmethod def is_preventive(cls, ope_type_id, key=''): try: op_type = OperationType.objects.get(pk=ope_type_id) except ObjectDoesNotExist: return False if not key: return op_type.preventive return key == op_type.txt_idx class RemainType(GeneralType): class Meta: verbose_name = _(u"Remain type") verbose_name_plural = _(u"Remain types") ordering = ('label',) class Period(GeneralType) : order = models.IntegerField(_(u"Order")) start_date = models.IntegerField(_(u"Start date")) end_date = models.IntegerField(_(u"End date")) parent = models.ForeignKey("Period", verbose_name=_(u"Parent period"), blank=True, null=True) class Meta: verbose_name = _(u"Type Period") verbose_name_plural = _(u"Types Period") ordering = ('order',) def __unicode__(self): return self.label class Operation(BaseHistorizedItem, OwnPerms): TABLE_COLS = ['year_index', 'operation_type', 'remains', 'towns', 'associated_file_short_label', 'start_date', 'excavation_end_date'] start_date = models.DateField(_(u"Start date"), null=True, blank=True) excavation_end_date = models.DateField(_(u"Excavation end date"), null=True, blank=True) end_date = models.DateField(_(u"Closing date"), null=True, blank=True) in_charge = models.ForeignKey(Person, blank=True, null=True, verbose_name=_(u"In charge"), related_name='operation_responsability') year = models.IntegerField(_(u"Year"), null=True, blank=True) operation_code = models.IntegerField(_(u"Operation code"), null=True, blank=True) if FILES_AVAILABLE: associated_file = models.ForeignKey(File, related_name='operations', verbose_name=_(u"File"), blank=True, null=True) operation_type = models.ForeignKey(OperationType, related_name='+', verbose_name=_(u"Operation type")) surface = models.IntegerField(_(u"Surface (m²)"), blank=True, null=True) remains = models.ManyToManyField("RemainType", verbose_name=_(u'Remains')) towns = models.ManyToManyField(Town, verbose_name=_(u"Towns")) cost = models.IntegerField(_(u"Cost (€)"), blank=True, null=True) periods = models.ManyToManyField(Period, verbose_name=_(u"Periods")) scheduled_man_days = models.IntegerField(_(u"Scheduled man-days"), blank=True, null=True) optional_man_days = models.IntegerField(_(u"Optional man-days"), blank=True, null=True) effective_man_days = models.IntegerField(_(u"Effective man-days"), blank=True, null=True) if settings.COUNTRY == 'fr': code_patriarche = models.IntegerField(u"Code PATRIARCHE", null=True, blank=True) TABLE_COLS = ['code_patriarche'] + TABLE_COLS code_dracar = models.CharField(u"Code DRACAR", max_length=10, null=True, blank=True) fnap_financing = models.FloatField(u"Financement FNAP (%)", blank=True, null=True) fnap_cost = models.IntegerField(u"Financement FNAP (€)", blank=True, null=True) zoning_prescription = models.NullBooleanField( _(u"Prescription on zoning"), blank=True, null=True) large_area_prescription = models.NullBooleanField( _(u"Prescription on large area"), blank=True, null=True) geoarchaeological_context_prescription = models.NullBooleanField( _(u"Prescription on geoarchaeological context"), blank=True, null=True) cira_rapporteur = models.ForeignKey(Person, related_name='+', null=True, blank=True, verbose_name=u"Rapporteur CIRA") negative_result = models.NullBooleanField( u"Résultat considéré comme négatif", blank=True, null=True) cira_date = models.DateField(u"Date avis CIRA", null=True, blank=True) eas_number = models.CharField(u"Numéro de l'EA", max_length=20, null=True, blank=True) operator_reference = models.CharField(_(u"Operator reference"), max_length=20, null=True, blank=True) common_name = models.CharField(_(u"Generic name"), max_length=120, null=True, blank=True) comment = models.TextField(_(u"Comment"), null=True, blank=True) cached_label = models.CharField(_(u"Cached name"), max_length=500, null=True, blank=True) history = HistoricalRecords() class Meta: verbose_name = _(u"Operation") verbose_name_plural = _(u"Operations") permissions = ( ("view_operation", ugettext(u"Can view all Operation")), ("view_own_operation", ugettext(u"Can view own Operation")), ("add_own_operation", ugettext(u"Can add own Operation")), ("change_own_operation", ugettext(u"Can change own Operation")), ("delete_own_operation", ugettext(u"Can delete own Operation")), ) def __unicode__(self): if self.cached_label: return self.cached_label self.save() return self.cached_label @property def short_class_name(self): return _(u"OPE") @property def reference(self): if self.code_patriarche: return unicode(self.code_patriarche) if self.year and self.operation_code: return u"-".join((unicode(self.year), unicode(self.operation_code))) return "00" def _generate_cached_label(self): items = [self.get_town_label(), self.reference] if self.common_name: items.append(self.common_name) cached_label = settings.JOINT.join(items) return cached_label def get_town_label(self): lbl = unicode(_('Intercommunal')) if self.towns.count() == 1: lbl = self.towns.all()[0].name return lbl def get_department(self): q = self.towns if not self.towns.count(): return '00' return self.towns.all()[0].numero_insee[:2] def grouped_parcels(self): return Parcel.grouped_parcels(list(self.parcels.all())) associated_file_short_label_lbl = _(u"Archaelogical file") @property def associated_file_short_label(self): if not self.associated_file: return "" return self.associated_file.short_label @classmethod def get_available_operation_code(cls, year=None): if not year: year = datetime.date.today().year max_val = cls.objects.filter(year=year).aggregate( Max('operation_code'))["operation_code__max"] return (max_val + 1) if max_val else 1 @classmethod def get_years(cls): return [res['year'] for res in list(cls.objects.values('year').annotate( Count("id")).order_by())] @classmethod def get_by_year(cls, year): return cls.objects.filter(year=year) @classmethod def get_total_number(cls): return cls.objects.count() year_index_lbl = _(u"Operation code") @property def year_index(self): if not self.operation_code: return "" lbl = unicode(self.operation_code) lbl = u"%d-%s%s" % (self.year, (3-len(lbl))*"0", lbl) return lbl def clean(self): objs = self.__class__.objects.filter(year=self.year, operation_code=self.operation_code) if self.pk: objs = objs.exclude(pk=self.pk) if objs.count(): raise ValidationError(_(u"This operation code already exists for " u"this year")) def is_own(self, person): return False @property def surface_ha(self): if self.surface: return self.surface/10000.0 @property def cost_by_m2(self): if not self.surface or not self.cost: return return round(float(self.cost)/self.surface, 2) @property def cost_by_m2(self): if not self.surface or not self.cost: return return round(float(self.cost)/self.surface, 2) @classmethod def get_query_owns(cls, user): return Q(in_charge=user.person)|Q(history_modifier=user)\ & Q(end_date__isnull=True) def is_active(self): return not bool(self.end_date) def closing(self): if self.is_active(): return for item in self.history.all(): if not item.end_date: break return {'date':item.history_date, 'user':IshtarUser.objects.get(pk=item.history_modifier_id)} m2m_changed.connect(cached_label_changed, sender=Operation.towns.through) def operation_post_save(sender, **kwargs): if not kwargs['instance']: return operation = kwargs['instance'] if operation.fnap_financing and operation.cost: fnap_cost = int(float(operation.cost)/100*operation.fnap_financing) if not operation.fnap_cost or operation.fnap_cost != fnap_cost: operation.fnap_cost = fnap_cost operation.save() elif operation.fnap_cost and operation.cost: fnap_percent = float(operation.fnap_cost)*100/operation.cost operation.fnap_financing = fnap_percent operation.save() cached_label_changed(sender, **kwargs) post_save.connect(operation_post_save, sender=Operation) class OperationByDepartment(models.Model): ''' Database view for dashboard ''' operation = models.ForeignKey(Operation, verbose_name=_(u"Operation")) department = models.ForeignKey(Department, verbose_name=_(u"Department"), blank=True, null=True) class Meta: managed = False db_table = 'operation_department' class OperationSource(Source): class Meta: verbose_name = _(u"Operation documentation") verbose_name_plural = _(u"Operation documentations") operation = models.ForeignKey(Operation, verbose_name=_(u"Operation"), related_name="source") index = models.IntegerField(verbose_name=_(u"Index"), blank=True, null=True) TABLE_COLS = ['operation.year', 'operation.operation_code'] + \ Source.TABLE_COLS class ActType(GeneralType): TYPE = (('F', _(u'Archaelogical file')), ('O', _(u'Operation')), ) intented_to = models.CharField(_(u"Intended to"), max_length=1, choices=TYPE) class Meta: verbose_name = _(u"Act type") verbose_name_plural = _(u"Act types") ordering = ('label',) class AdministrativeAct(BaseHistorizedItem, OwnPerms): TABLE_COLS = ['act_type', 'associated_file', 'operation', 'associated_file.towns', 'operation.towns'] TABLE_COLS_FILE = ['act_type', 'associated_file', 'associated_file.towns',] TABLE_COLS_OPE = ['act_type', 'operation', 'operation.towns'] act_type = models.ForeignKey(ActType, verbose_name=_(u"Act type")) in_charge = models.ForeignKey(Person, blank=True, null=True, related_name='+', verbose_name=_(u"Person in charge of the operation")) operator = models.ForeignKey(Organization, blank=True, null=True, verbose_name=_(u"Archaeological preventive operator")) scientific = models.ForeignKey(Person, blank=True, null=True, related_name='+', verbose_name=_(u"Person in charge of the scientific part")) signatory = models.ForeignKey(Person, blank=True, null=True, related_name='+', verbose_name=_(u"Signatory")) operation = models.ForeignKey(Operation, blank=True, null=True, related_name='administrative_act', verbose_name=_(u"Operation")) if FILES_AVAILABLE: associated_file = models.ForeignKey(File, blank=True, null=True, related_name='administrative_act', verbose_name=_(u"Archaelogical file")) signature_date = models.DateField(_(u"Signature date"), blank=True, null=True) act_object = models.CharField(_(u"Object"), max_length=200) if settings.COUNTRY == 'fr': ref_sra = models.CharField(u"Référence SRA", max_length=15, blank=True, null=True) history = HistoricalRecords() class Meta: verbose_name = _(u"Administrative act") verbose_name_plural = _(u"Administrative acts") permissions = ( ("view_administrativeact", ugettext(u"Can view all Administrative act")), ("view_own_administrativeact", ugettext(u"Can view own Administrative act")), ("add_own_administrativeact", ugettext(u"Can add own Administrative act")), ("change_own_administrativeact", ugettext(u"Can change own Administrative act")), ("delete_own_administrativeact", ugettext(u"Can delete own Administrative act")), ) def __unicode__(self): return settings.JOINT.join([unicode(item) for item in [self.operation, self.associated_file, self.act_object] if item]) class Parcel(LightHistorizedItem): if FILES_AVAILABLE: associated_file = models.ForeignKey(File, related_name='parcels', blank=True, null=True, verbose_name=_(u"File")) operation = models.ForeignKey(Operation, related_name='parcels', blank=True, null=True, verbose_name=_(u"Operation")) year = models.IntegerField(_(u"Year"), blank=True, null=True) town = models.ForeignKey(Town, related_name='parcels', verbose_name=_(u"Town")) section = models.CharField(_(u"Section"), max_length=4) parcel_number = models.CharField(_(u"Parcel number"), max_length=6) class Meta: verbose_name = _(u"Parcel") verbose_name_plural = _(u"Parcels") @property def short_label(self): return settings.JOINT.join([unicode(item) for item in [self.section, self.parcel_number] if item]) def __unicode__(self): return self.short_label @staticmethod def grouped_parcels(parcels): sortkeyfn = lambda s:(getattr(s, 'year'), getattr(s, 'town'), getattr(s, 'section')) parcels.sort(key=sortkeyfn) grouped = [] for keys, parcel_grp in groupby(parcels, key=sortkeyfn): for idx, parcel in enumerate(parcel_grp): if not idx: grouped.append(parcel) grouped[-1].parcel_numbers = [] grouped[-1].parcel_numbers.append(parcel.parcel_number) grouped[-1].parcel_numbers.sort() return grouped def long_label(self): items = [unicode(self.operation or self.associated_file)] items += [unicode(item) for item in [self.section, self.parcel_number] if item] return settings.JOINT.join(items) def parcel_post_save(sender, **kwargs): if not kwargs['instance'] or not FILES_AVAILABLE: return parcel = kwargs['instance'] if parcel.operation and parcel.associated_file: return if parcel.operation and parcel.operation.associated_file: parcel.associated_file = parcel.operation.associated_file parcel.save() return post_save.connect(parcel_post_save, sender=Parcel) class ParcelOwner(LightHistorizedItem): owner = models.ForeignKey(Person, verbose_name=_(u"Owner")) parcel = models.ForeignKey(Parcel, verbose_name=_(u"Parcel")) start_date = models.DateField(_(u"Start date")) end_date = models.DateField(_(u"End date")) class Meta: verbose_name = _(u"Parcel owner") verbose_name_plural = _(u"Parcel owners") def __unicode__(self): return self.owner + settings.JOINT + self.parcel class OperationDashboard: def __init__(self): main_dashboard = Dashboard(Operation) self.total_number = main_dashboard.total_number self.filters_keys = ['recorded', 'effective', 'active', 'field', 'documented', 'closed', 'documented_closed'] filters = { 'recorded':{}, 'effective':{'in_charge__isnull':False}, 'active':{'in_charge__isnull':False, 'end_date__isnull':True}, 'field':{'excavation_end_date__isnull':True}, 'documented':{'source__isnull':False}, 'documented_closed':{'source__isnull':False, 'end_date__isnull':False}, 'closed':{'end_date__isnull':False} } filters_label = { 'recorded':_(u"Recorded"), 'effective':_(u"Effective"), 'active':_(u"Active"), 'field':_(u"Field completed"), 'documented':_(u"Associated report"), 'closed':_(u"Closed"), 'documented_closed':_(u"Documented and closed"), } self.filters_label = [filters_label[k] for k in self.filters_keys] self.total = [] for fltr_key in self.filters_keys: fltr, lbl = filters[fltr_key], filters_label[fltr_key] nb = Operation.objects.filter(**fltr).count() self.total.append((lbl, nb)) self.surface_by_type = Operation.objects\ .values('operation_type__label')\ .annotate(number=Sum('surface'))\ .order_by('-number','operation_type__label') self.by_type = [] self.types = OperationType.objects.filter(available=True).all() for fltr_key in self.filters_keys: fltr, lbl = filters[fltr_key], filters_label[fltr_key] type_res = Operation.objects.filter(**fltr).\ values('operation_type', 'operation_type__label').\ annotate(number=Count('pk')).\ order_by('operation_type') types_dct = {} for typ in type_res.all(): types_dct[typ['operation_type']] = typ["number"] types = [] for typ in self.types: if typ.pk in types_dct: types.append(types_dct[typ.pk]) else: types.append(0) self.by_type.append((lbl, types)) self.by_year = [] self.years = [res['year'] for res in Operation.objects.values('year')\ .order_by('-year').distinct()] for fltr_key in self.filters_keys: fltr, lbl = filters[fltr_key], filters_label[fltr_key] year_res = Operation.objects.filter(**fltr).\ values('year').\ annotate(number=Count('pk')).\ order_by('year') years_dct = {} for yr in year_res.all(): years_dct[yr['year']] = yr["number"] years = [] for yr in self.years: if yr in years_dct: years.append(years_dct[yr]) else: years.append(0) self.by_year.append((lbl, years)) self.by_realisation_year = [] self.realisation_years = [res['date'] for res in \ Operation.objects.extra( {'date':"date_trunc('year', start_date)"}).values('date')\ .filter(start_date__isnull=False).order_by('-date').distinct()] for fltr_key in self.filters_keys: fltr, lbl = filters[fltr_key], filters_label[fltr_key] year_res = Operation.objects.filter(**fltr).extra( {'date':"date_trunc('year', start_date)"}).values('date').\ values('date').filter(start_date__isnull=False).\ annotate(number=Count('pk')).\ order_by('-date') years_dct = {} for yr in year_res.all(): years_dct[yr['date']] = yr["number"] years = [] for yr in self.realisation_years: if yr in years_dct: years.append(years_dct[yr]) else: years.append(0) self.by_realisation_year.append((lbl, years)) self.effective = [] for typ in self.types: year_res = Operation.objects.filter(**{'in_charge__isnull':False, 'operation_type':typ}).\ values('year').\ annotate(number=Count('pk')).\ order_by('-year').distinct() years_dct = {} for yr in year_res.all(): years_dct[yr['year']] = yr["number"] years = [] for yr in self.years: if yr in years_dct: years.append(years_dct[yr]) else: years.append(0) self.effective.append((typ, years)) # TODO: by date now = datetime.date.today() limit = datetime.date(now.year, now.month, 1) - datetime.timedelta(365) by_realisation_month = Operation.objects.filter(start_date__gt=limit, start_date__isnull=False).extra( {'date':"date_trunc('month', start_date)"}) self.last_months = [] date = datetime.datetime(now.year, now.month, 1) for mt_idx in xrange(12): self.last_months.append(date) if date.month > 1: date = datetime.datetime(date.year, date.month - 1, 1) else: date = datetime.datetime(date.year - 1, 12, 1) self.by_realisation_month = [] for fltr_key in self.filters_keys: fltr, lbl = filters[fltr_key], filters_label[fltr_key] month_res = by_realisation_month.filter(**fltr).\ annotate(number=Count('pk')).\ order_by('-date') month_dct = {} for mt in month_res.all(): month_dct[mt.date] = mt.number date = datetime.date(now.year, now.month, 1) months = [] for date in self.last_months: if date in month_dct: months.append(month_dct[date]) else: months.append(0) self.by_realisation_month.append((lbl, months)) # survey and excavations self.survey, self.excavation = {}, {} for dct_res, ope_types in ((self.survey, ('arch_diagnostic',)), (self.excavation, ('prev_excavation', 'prog_excavation'))): dct_res['total'] = [] operation_type = {'operation_type__txt_idx__in':ope_types} for fltr_key in self.filters_keys: fltr, lbl = filters[fltr_key], filters_label[fltr_key] fltr.update(operation_type) nb = Operation.objects.filter(**fltr).count() dct_res['total'].append((lbl, nb)) dct_res['by_year'] = [] for fltr_key in self.filters_keys: fltr, lbl = filters[fltr_key], filters_label[fltr_key] fltr.update(operation_type) year_res = Operation.objects.filter(**fltr).\ values('year').\ annotate(number=Count('pk')).\ order_by('year') years_dct = {} for yr in year_res.all(): years_dct[yr['year']] = yr["number"] years = [] for yr in self.years: if yr in years_dct: years.append(years_dct[yr]) else: years.append(0) dct_res['by_year'].append((lbl, years)) dct_res['by_realisation_year'] = [] for fltr_key in self.filters_keys: fltr, lbl = filters[fltr_key], filters_label[fltr_key] fltr.update(operation_type) year_res = Operation.objects.filter(**fltr).extra( {'date':"date_trunc('year', start_date)"}).values('date').\ filter(start_date__isnull=False).\ annotate(number=Count('pk')).\ order_by('-date') years_dct = {} for yr in year_res.all(): years_dct[yr['date']] = yr["number"] years = [] for yr in self.realisation_years: if yr in years_dct: years.append(years_dct[yr]) else: years.append(0) dct_res['by_realisation_year'].append((lbl, years)) current_year_ope = Operation.objects.filter(**operation_type)\ .filter(year=datetime.date.today().year) current_realisation_year_ope = Operation.objects\ .filter(**operation_type)\ .filter(start_date__year=datetime.date.today().year) res_keys = [('area_realised', current_realisation_year_ope)] if dct_res == self.survey: res_keys.append(('area', current_year_ope)) for res_key, base_ope in res_keys: dct_res[res_key] = [] for fltr_key in self.filters_keys: fltr, lbl = filters[fltr_key], filters_label[fltr_key] area_res = base_ope.filter(**fltr)\ .annotate(number=Sum('surface')).all() val = 0 if area_res: val = area_res[0].number dct_res[res_key].append(val) # TODO... res_keys = [('manday_realised', current_realisation_year_ope)] if dct_res == self.survey: res_keys.append(('manday', current_year_ope)) for res_key, base_ope in res_keys: dct_res[res_key] = [] for fltr_key in self.filters_keys: dct_res[res_key].append('-') # TODO... res_keys = [('mandayhect_realised', current_realisation_year_ope)] if dct_res == self.survey: res_keys.append(('mandayhect', current_year_ope)) for res_key, base_ope in res_keys: dct_res[res_key] = [] for fltr_key in self.filters_keys: dct_res[res_key].append('-') # TODO... dct_res['mandayhect_real_effective'] = '-' if dct_res == self.survey: dct_res['mandayhect_effective'] = '-' res_keys = [('org_realised', current_realisation_year_ope)] if dct_res == self.survey: res_keys.append(('org', current_year_ope)) for res_key, base_ope in res_keys: org_res = base_ope.filter(in_charge__attached_to__isnull=False)\ .values('in_charge__attached_to', 'in_charge__attached_to__name')\ .annotate(area=Sum('surface'))\ .order_by('in_charge__attached_to__name').all() # TODO: man-days, man-days/hectare dct_res[res_key] = org_res year_ope = Operation.objects.filter(**operation_type) res_keys = ['org_by_year'] if dct_res == self.survey: res_keys.append('org_by_year_realised') q = year_ope.values('in_charge__attached_to', 'in_charge__attached_to__name').\ filter(in_charge__attached_to__isnull=False).\ order_by('in_charge__attached_to__name').distinct() org_list = [(org['in_charge__attached_to'], org['in_charge__attached_to__name']) for org in q] org_list_dct = dict(org_list) for res_key in res_keys: dct_res[res_key] = [] years = self.years if res_key == 'org_by_year_realised': years = self.realisation_years for org_id, org_label in org_list: org_res = year_ope.filter(in_charge__attached_to__pk=org_id) key_date = '' if res_key == 'org_by_year': org_res = org_res.values('year') key_date = 'year' else: org_res = org_res.extra( {'date':"date_trunc('year', start_date)"}).values('date').\ filter(start_date__isnull=False) key_date = 'date' org_res = org_res.annotate(area=Sum('surface'), cost=Sum('cost')) years_dct = {} for yr in org_res.all(): area = yr['area'] if yr['area'] else 0 cost = yr['cost'] if yr['cost'] else 0 years_dct[yr[key_date]] = (area, cost) r_years = [] for yr in years: if yr in years_dct: r_years.append(years_dct[yr]) else: r_years.append((0, 0)) dct_res[res_key].append((org_label, r_years)) area_means, area_sums = [], [] cost_means, cost_sums = [], [] for idx, year in enumerate(years): vals = [r_years[idx] for lbl, r_years in dct_res[res_key]] if not vals: continue sum_area = sum([a for a, c in vals]) sum_cost = sum([c for a, c in vals]) area_means.append(sum_area/len(vals)) area_sums.append(sum_area) cost_means.append(sum_cost/len(vals)) cost_sums.append(sum_cost) dct_res[res_key+'_area_mean'] = area_means dct_res[res_key+'_area_sum'] = area_sums dct_res[res_key+'_cost_mean'] = cost_means dct_res[res_key+'_cost_mean'] = cost_sums if dct_res == self.survey: self.survey['effective'] = [] for yr in self.years: year_res = Operation.objects.filter(in_charge__isnull=False, year=yr).\ annotate(number=Sum('surface'), mean=Avg('surface')) nb = year_res[0].number if year_res.count() else 0 nb = nb if nb else 0 mean = year_res[0].mean if year_res.count() else 0 mean = mean if mean else 0 self.survey['effective'].append((nb, mean)) # TODO:Man-Days/hectare by Year # CHECK: month of realisation or month? dct_res['by_month'] = [] for fltr_key in self.filters_keys: fltr, lbl = filters[fltr_key], filters_label[fltr_key] fltr.update(operation_type) month_res = by_realisation_month.filter(**fltr).\ annotate(number=Count('pk')).\ order_by('-date') month_dct = {} for mt in month_res.all(): month_dct[mt.date] = mt.number date = datetime.date(now.year, now.month, 1) months = [] for date in self.last_months: if date in month_dct: months.append(month_dct[date]) else: months.append(0) dct_res['by_month'].append((lbl, months)) operation_type = {'operation_type__txt_idx__in':ope_types} self.departments = [(fd['department__pk'], fd['department__label']) for fd in OperationByDepartment.objects\ .filter(department__isnull=False)\ .values('department__label', 'department__pk')\ .order_by('department__label').distinct()] dct_res['by_dpt'] = [] for dpt_id, dpt_label in self.departments: vals = OperationByDepartment.objects\ .filter(department__pk=dpt_id, operation__operation_type__txt_idx__in=ope_types)\ .values('department__pk', 'operation__year')\ .annotate(number=Count('operation'))\ .order_by('operation__year') dct_years = {} for v in vals: dct_years[v['operation__year']] = v['number'] years = [] for y in self.years: if y in dct_years: years.append(dct_years[y]) else: years.append(0) years.append(sum(years)) dct_res['by_dpt'].append((dpt_label, years)) dct_res['effective_by_dpt'] = [] for dpt_id, dpt_label in self.departments: vals = OperationByDepartment.objects\ .filter(department__pk=dpt_id, operation__in_charge__isnull=False, operation__operation_type__txt_idx__in=ope_types)\ .values('department__pk', 'operation__year')\ .annotate(number=Count('operation'), area=Sum('operation__surface'), fnap=Sum('operation__fnap_cost'), cost=Sum('operation__cost'))\ .order_by('operation__year') dct_years = {} for v in vals: values = [] for value in (v['number'], v['area'], v['cost'], v['fnap']): values.append(value if value else 0) dct_years[v['operation__year']] = values years = [] for y in self.years: if y in dct_years: years.append(dct_years[y]) else: years.append((0, 0, 0, 0)) nbs, areas, costs, fnaps = zip(*years) years.append((sum(nbs), sum(areas), sum(costs), sum(fnaps))) dct_res['effective_by_dpt'].append((dpt_label, years)) OperationTown = Operation.towns.through query = OperationTown.objects\ .filter(operation__in_charge__isnull=False, operation__operation_type__txt_idx__in=ope_types)\ .values('town__name', 'town__departement__number')\ .annotate(nb=Count('operation'))\ .order_by('-nb', 'town__name')[:10] dct_res['towns'] = [] for r in query: dct_res['towns'].append((u"%s (%s)" % (r['town__name'], r['town__departement__number']), r['nb'])) if dct_res == self.survey: query = OperationTown.objects\ .filter(operation__in_charge__isnull=False, operation__operation_type__txt_idx__in=ope_types, operation__surface__isnull=False)\ .values('town__name', 'town__departement__number')\ .annotate(nb=Sum('operation__surface'))\ .order_by('-nb', 'town__name')[:10] dct_res['towns_surface'] = [] for r in query: dct_res['towns_surface'].append((u"%s (%s)" % ( r['town__name'], r['town__departement__number']), r['nb'])) else: query = OperationTown.objects\ .filter(operation__in_charge__isnull=False, operation__operation_type__txt_idx__in=ope_types, operation__cost__isnull=False)\ .values('town__name', 'town__departement__number')\ .annotate(nb=Sum('operation__cost'))\ .order_by('-nb', 'town__name')[:10] dct_res['towns_cost'] = [] for r in query: dct_res['towns_cost'].append((u"%s (%s)" % (r['town__name'], r['town__departement__number']), r['nb']))