#!/usr/bin/env python # -*- coding: utf-8 -*- # Copyright (C) 2012 É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. 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 from django.utils.translation import ugettext_lazy as _, ugettext from ishtar_common.models import GeneralType, BaseHistorizedItem, \ HistoricalRecords, LightHistorizedItem, OwnPerms, Department, Source,\ Person, Organization, Town, Dashboard FILES_AVAILABLE = 'archaeological_files' in settings.INSTALLED_APPS if FILES_AVAILABLE: from archaeological_files.models import File class OperationType(GeneralType): class Meta: verbose_name = _(u"Operation type") verbose_name_plural = _(u"Operation types") @classmethod def is_preventive(cls, ope_type_id, key=''): key = key or 'prev_excavation' try: preventive = OperationType.objects.get(txt_idx=key).pk return ope_type_id == preventive except ObjectDoesNotExist: return False class RemainType(GeneralType): class Meta: verbose_name = _(u"Remain type") verbose_name_plural = _(u"Remain types") 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") def __unicode__(self): return self.label class Operation(BaseHistorizedItem, OwnPerms): TABLE_COLS = ['year_index', 'operation_type', 'remains', 'towns', 'associated_file', '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, related_name='+', null=True, blank=True, verbose_name=_(u"In charge")) year = models.IntegerField(_(u"Year")) operation_code = models.IntegerField(_(u"Operation code")) 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) 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) history = HistoricalRecords() class Meta: verbose_name = _(u"Operation") verbose_name_plural = _(u"Operations") permissions = ( ("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): items = [unicode(_('Intercommunal'))] if self.towns.count() == 1: items[0] = unicode(self.towns.all()[0]) items.append("-".join((unicode(self.year), unicode(self.operation_code)))) return JOINT.join(items) @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): 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)} 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() post_save.connect(operation_post_save, sender=Operation) class OperationByDepartment(models.Model): ''' Database view: don't forget to create it create view operation_department (id, department_id, operation_id) as select town."id", town."departement_id", operation_towns."operation_id" from ishtar_base_town town inner join ishtar_base_operation_towns operation_towns on operation_towns."town_id"=town."id" order by town."departement_id"; CREATE RULE operation_department_delete AS ON DELETE TO operation_department DO INSTEAD(); ''' 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")) 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") 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) history = HistoricalRecords() class Meta: verbose_name = _(u"Administrative act") verbose_name_plural = _(u"Administrative acts") permissions = ( ("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 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") def short_label(self): return JOINT.join([unicode(item) for item in [self.section, self.parcel_number] if item]) def __unicode__(self): return self.short_label() 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 JOINT.join(items) 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 + 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]] 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']))