path: root/archaeological_operations/models.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Copyright (C) 2012 É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 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 settings.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 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)
    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()

    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)

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