path: root/archaeological_operations/models.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Copyright (C) 2012-2013 Étienne Loks  <etienne.loks_AT_peacefrogsDOTnet>

# 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 <http://www.gnu.org/licenses/>.

# 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
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, 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")

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"), 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)
    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])
        if self.operation_code:
            items.append("-".join((unicode(self.year),
                               unicode(self.operation_code))))
        return settings.JOINT.join(items)

    def grouped_parcels(self):
        return Parcel.grouped_parcels(list(self.parcels.all()))

    @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)}

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 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"))
    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, blank=True,
                                      null=True)
    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 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")

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