Work on operation dashboard (refs #525)

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

3 files changed, 110 insertions, 15 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")
diff --git a/ishtar/templates/dashboard_operation.html b/ishtar/templates/dashboard_operation.html
index add9ecdd3..b1fb0e8d5 100644
--- a/ishtar/templates/dashboard_operation.html
+++ b/ishtar/templates/dashboard_operation.html
@@ -253,6 +253,32 @@
       </tr>
     </table></div>
 
+    <div class='table'>
+    <table>
+      <caption>{% trans "By month" %}</caption>
+      <tr>
+        <th>{% trans "State" %}</th>{%for mt in dashboard.last_months %}<th>{{mt.date|date:"F Y"|capfirst}}</th>{% endfor %}
+      </tr>
+      {% for lbl, months in dashboard.survey.by_month %}
+      <tr>
+        <th class='sub'>{{lbl}}</th>{%for nb in months %}<td>{{nb}}</td>{% endfor %}
+      </tr>
+      {% endfor %}
+    </table></div>
+
+    <div class='table'>
+    <table>
+      <caption>{% trans "By department" %}</caption>
+      <tr>
+        <th>{% trans "Department" %}</th>{%for yr in dashboard.years %}<th>{{yr}}</th>{% endfor %}<th>{% trans "Sum" %}</th>
+      </tr>
+      {% for lbl, years in dashboard.survey.by_dpt %}
+      <tr>
+        <th class='sub'>{{lbl}}</th>{%for nb in years %}<td{%if forloop.last%} class='sub'{%endif%}>{{nb}}</td>{% endfor %}
+      </tr>
+      {% endfor %}
+    </table></div>
+
 
 
   </div>
@@ -369,6 +395,31 @@
       </tr>
     </table></div>
 
+    <div class='table'>
+    <table>
+      <caption>{% trans "By month" %}</caption>
+      <tr>
+        <th>{% trans "State" %}</th>{%for mt in dashboard.last_months %}<th>{{mt.date|date:"F Y"|capfirst}}</th>{% endfor %}
+      </tr>
+      {% for lbl, months in dashboard.excavation.by_month %}
+      <tr>
+        <th class='sub'>{{lbl}}</th>{%for nb in months %}<td>{{nb}}</td>{% endfor %}
+      </tr>
+      {% endfor %}
+    </table></div>
+
+    <div class='table'>
+    <table>
+      <caption>{% trans "By department" %}</caption>
+      <tr>
+        <th>{% trans "Department" %}</th>{%for yr in dashboard.years %}<th>{{yr}}</th>{% endfor %}<th>{% trans "Sum" %}</th>
+      </tr>
+      {% for lbl, years in dashboard.excavation.by_dpt %}
+      <tr>
+        <th class='sub'>{{lbl}}</th>{%for nb in years %}<td{%if forloop.last%} class='sub'{%endif%}>{{nb}}</td>{% endfor %}
+      </tr>
+      {% endfor %}
+    </table></div>
 
   </div>
 <!--
diff --git a/static/media/style.css b/static/media/style.css
index 9e1e77fed..b126e7e22 100644
--- a/static/media/style.css
+++ b/static/media/style.css
@@ -378,14 +378,17 @@ table.confirm tr.spacer td:last-child{
     color:#FFF;
 }
 
-#window table th.sub, .dashboard table th.sub{
-    text-align:left;
+#window table th.sub, .dashboard table th.sub, .dashboard table td.sub{
     background-color:#994242;
     border:1px solid #EEE;
     color:#FFF;
     padding:0 1em;
 }
 
+#window table th.sub, .dashboard table th.sub{
+    text-align:left;
+}
+
 #window table td, .dashboard table td{
     text-align:right;
     padding:0 1em;