diff options
Diffstat (limited to 'ishtar/ishtar_base/models.py')
| -rw-r--r-- | ishtar/ishtar_base/models.py | 368 |
1 files changed, 366 insertions, 2 deletions
diff --git a/ishtar/ishtar_base/models.py b/ishtar/ishtar_base/models.py index 4fcb79186..a614df814 100644 --- a/ishtar/ishtar_base/models.py +++ b/ishtar/ishtar_base/models.py @@ -28,7 +28,7 @@ from django.utils.translation import ugettext_lazy as _, ugettext from django.utils.safestring import SafeUnicode, mark_safe from django.core.urlresolvers import reverse, NoReverseMatch from django.db.utils import DatabaseError -from django.db.models import Q, Max, Count, Sum +from django.db.models import Q, Max, Count, Sum, Avg from django.db.models.signals import m2m_changed from django.contrib.auth.models import User @@ -484,6 +484,371 @@ class FileDashboard: .annotate(number=Sum('file__total_surface'))\ .order_by('department__label') +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"), + 'documented':_(u"Documented"), + '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 + + """ + dct_res['by_month'] = [] + by_month = Operation.objects.filter(year=datetime.date.today().year, + year__isnull=False) + for fltr_key in self.filters_keys: + fltr, lbl = filters[fltr_key], filters_label[fltr_key] + month_res = by_month.filter(**fltr).\ + annotate(number=Count('pk')).\ + order_by('-year') + 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)) + """ + + + + + + + + + + + + + + + class Dashboard: def __init__(self, model): self.model = model @@ -979,7 +1344,6 @@ class Operation(BaseHistorizedItem, OwnPerms): def is_own(self, person): return False - @property def surface_ha(self): if self.surface: |