Work on operation dashboard (refs #525)

 - manage OperationDepartment database view
 - table by department
 - table by month

1 files changed, 54 insertions, 13 deletions

diff --git a/ishtar/ishtar_base/models.py b/ishtar/ishtar_base/models.py
index a614df814..8e456191e 100644
--- a/ishtar/ishtar_base/models.py
+++ b/ishtar/ishtar_base/models.py
@@ -498,7 +498,8 @@ class OperationDashboard:
             '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},
+            'documented_closed':{'source__isnull':False,
+                                 'end_date__isnull':False},
             'closed':{'end_date__isnull':False}
             }
         filters_label = {
@@ -813,15 +814,14 @@ class OperationDashboard:
 
             # TODO:Man-Days/hectare by Year
 
-            """
+            # CHECK: month of realisation or month?
             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).\
+                fltr.update(operation_type)
+                month_res = by_realisation_month.filter(**fltr).\
                                 annotate(number=Count('pk')).\
-                                order_by('-year')
+                                order_by('-date')
                 month_dct = {}
                 for mt in month_res.all():
                     month_dct[mt.date] = mt.number
@@ -832,9 +832,33 @@ class OperationDashboard:
                         months.append(month_dct[date])
                     else:
                         months.append(0)
-                self.by_realisation_month.append((lbl, months))
-            """
+                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))
 
 
 
@@ -1225,11 +1249,11 @@ class FileByDepartment(models.Model):
     '''
     Database view: don't forget to create it
 
-create view file_department (department_id, file_id) as
-    select town."departement_id", file_towns."file_id"
-        from ishtar_base_town town
-    inner join ishtar_base_file_towns file_towns on
-        file_towns."town_id"=town."id" order by town."departement_id";
+    create view file_department (department_id, file_id) as
+        select town."departement_id", file_towns."file_id"
+            from ishtar_base_town town
+        inner join ishtar_base_file_towns file_towns on
+            file_towns."town_id"=town."id" order by town."departement_id";
     '''
     file = models.ForeignKey(File, verbose_name=_(u"File"))
     department = models.ForeignKey(Departement, verbose_name=_(u"Department"),
@@ -1378,6 +1402,23 @@ class Operation(BaseHistorizedItem, OwnPerms):
         return {'date':item.history_date,
                 'user':IshtarUser.objects.get(pk=item.history_modifier_id)}
 
+class OperationByDepartment(models.Model):
+    '''
+    Database view: don't forget to create it
+
+    create view operation_department (department_id, operation_id) as
+        select 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";
+    '''
+    operation = models.ForeignKey(Operation, verbose_name=_(u"Operation"))
+    department = models.ForeignKey(Departement, 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")