Statistics - manage queries

5 files changed, 210 insertions, 5 deletions

diff --git a/archaeological_operations/models.py b/archaeological_operations/models.py
index b3e137399..4f180c3d5 100644
--- a/archaeological_operations/models.py
+++ b/archaeological_operations/models.py
@@ -527,6 +527,9 @@ class Operation(ClosedItem, DocumentItem, BaseHistorizedItem, QRCodeItem,
     TABLE_COLS = ['code_patriarche', 'year', 'towns_label', 'common_name',
                   'operation_type', 'start_date', 'excavation_end_date',
                   'remains']
+    # statistics
+    STATISTIC_MODALITIES = ["year", "operation_type__label",
+                            "towns__cached_label"]
 
     # search parameters
     BOOL_FIELDS = ['end_date__isnull', 'virtual_operation',
diff --git a/archaeological_operations/tests.py b/archaeological_operations/tests.py
index 10afc61f2..42ceea292 100644
--- a/archaeological_operations/tests.py
+++ b/archaeological_operations/tests.py
@@ -2006,6 +2006,35 @@ class OperationSearchTest(TestCase, OperationInitTest):
         self.assertEqual(json.loads(response.content.decode())['recordsTotal'],
                          1)
 
+    def test_statistics(self):
+        c = Client()
+        c.login(username=self.username, password=self.password)
+        q = {"stats_modality_1": "year",
+             "stats_modality_2": "operation_type__label"}
+        response = c.get(reverse('get-operation', args=['json-stats']), q)
+        self.assertEqual(response.status_code, 200)
+
+        expected_result = []
+        for ope in models.Operation.objects.all():
+            years = [y for y, res in expected_result]
+            if ope.year in years:
+                year_idx = years.index(ope.year)
+            else:
+                expected_result.append([ope.year, []])
+                year_idx = len(expected_result) - 1
+            current_values = expected_result[year_idx][1]
+            values = [v for v, cnt in current_values]
+            val = ope.operation_type.label
+            if val in values:
+                val_idx = values.index(val)
+            else:
+                current_values.append([val, 0])
+                val_idx = len(current_values) - 1
+            current_values[val_idx][1] += 1
+
+        values = json.loads(response.content.decode())
+        self.assertEqual(values['data'], expected_result)
+
 
 class OperationPermissionTest(TestCase, OperationInitTest):
     fixtures = FILE_FIXTURES
diff --git a/ishtar_common/models.py b/ishtar_common/models.py
index 15b341cb7..7aa8706b3 100644
--- a/ishtar_common/models.py
+++ b/ishtar_common/models.py
@@ -21,6 +21,7 @@
 Models description
 """
 import copy
+from collections import OrderedDict
 import datetime
 import inspect
 from importlib import import_module
@@ -1929,7 +1930,14 @@ class TemplateItem:
         return cls._label_templates_q()
 
 
-class BaseHistorizedItem(TemplateItem, FullSearch, Imported,
+class StatisticItem:
+    STATISTIC_MODALITIES = []  # example: "operation type", "material type"
+    STATISTIC_SUM_VARIABLE = OrderedDict(
+        (("pk", _("number")),)
+    )  # example: "price", "volume"
+
+
+class BaseHistorizedItem(StatisticItem, TemplateItem, FullSearch, Imported,
                          JsonData, FixAssociated):
     """
     Historized item with external ID management.
diff --git a/ishtar_common/templates/blocks/DataTables-stats.html b/ishtar_common/templates/blocks/DataTables-stats.html
new file mode 100644
index 000000000..da3a85179
--- /dev/null
+++ b/ishtar_common/templates/blocks/DataTables-stats.html
@@ -0,0 +1,125 @@
+{% load i18n %}
+<div>
+    <div id="stats-form-{{name}}">
+        <label for="stats_renderer-{{name}}">{% trans "Type" %}</label>
+        <select id="stats_renderer-{{name}}" name="stats_renderer">
+            <option value="line" selected='selected'>{% trans "Line" %}</option>
+            <option value="bar">{% trans "Bar" %}</option>
+            <option value="pie">{% trans "Pie" %}</option>
+            <option value="table">{% trans "Table" %}</option>
+        </select>
+        <label for="stats_modality_1-{{name}}">{% trans "Modality 1" %}</label>
+        <select id="stats_modality_1-{{name}}" name="stats_modality_1">
+            {% for modality, modality_lbl in current_model.STATISTIC_MODALITIES_OPTIONS.items %}
+            <option value="{{modality}}"{% if forloop.first %} selected='selected'{% endif %}>{{modality_lbl}}</option>
+            {% endfor %}
+        </select>
+        <label for="stats_modality_2-{{name}}">{% trans "Modality 2" %}</label>
+        <select id="stats_modality_2-{{name}}" name="stats_modality_2">
+            <option value="" selected='selected'>--</option>
+            {% for modality, modality_lbl in current_model.STATISTIC_MODALITIES_OPTIONS.items %}
+            <option value="{{modality}}">{{modality_lbl}}</option>
+            {% endfor %}
+        </select>
+        <label for="stats_sum-{{name}}">{% trans "Sum" %}</label>
+        <select id="stats_sum-{{name}}" name="stats_sum">
+            {% for sum_var, sum_var_lbl in current_model.STATISTIC_SUM_VARIABLE.items %}
+            <option value="{{sum_var}}"{% if forloop.first %} selected='selected'{% endif %}>{{sum_var_lbl}}</option>
+            {% endfor %}
+        </select>
+    </div>
+    <hr/>
+    <div id="charts-{{name}}">
+        <div id="chart-{{name}}"
+                style="height:400px; width:700px; margin-right:auto; margin-left:auto"></div>
+        <hr/>
+        <p class='alert alert-info' id="stats-zoom-help-{{name}}" style="z-index:-1">
+            <i class="fa fa-info-circle" aria-hidden="true"></i>&nbsp;
+            {% trans 'Draw rectangle on the graph to zoom. Double-click to reinitialize.' %}
+        </p>
+        <div class='form chart-img-form'>
+            <div class="text-center">
+                <button id="chart-img-display-{{name}}"
+                        type='button' class='btn btn-secondary'>
+                    {% trans "Display as an image" %}
+                </button>
+            </div>
+            <br>
+            <div id="chart-img-{{name}}" class='chart-img'>
+                <div class="card">
+                    <div id="img-{{name}}"
+                            class="card-img-top text-center ml-3 mt-3"></div>
+                    <div class="card-body">
+                        <div class='alert alert-info'>
+                            <i class="fa fa-info-circle" aria-hidden="true"></i>&nbsp;
+                            {% trans 'Right-click on this image to save it.' %}
+                        </div>
+                    </div>
+                </div>
+            </div>
+        </div>
+    </div>
+    <div id="stats-table-{{name}}">
+        <div id="stats-table-content-{{name}}"></div>
+        <hr>
+        <div class="text-center">
+            <a id="stats-table-csv-{{name}}" href="#"
+                    type='button' class='btn btn-secondary'>
+                {% trans "Export as CSV" %}
+            </a>
+        </div>
+    </div>
+</div>
+
+<script language="javascript" type="text/javascript">
+
+{% comment %}
+    var plot_{{name}} = $.jqplot('chart_{{name}}',
+        [{% for idx, lbl, values in dashboard.values %}{% if forloop.counter0 > 0 %} {% if forloop.counter0 > 1 %}, {% endif%} values_{{forloop.counter0}}_{{name}} {% endif %} {% endfor%}], {
+        axes:{ {%ifequal slicing 'year'%}
+            xaxis:{
+            label:'{% trans "Year" %}',
+            tickOptions: {
+                formatString: "%d"
+            }
+            },{%endifequal%}{%ifequal slicing 'month'%}
+            xaxis:{
+            label:'{% trans "Month" %}',
+            renderer:$.jqplot.DateAxisRenderer,
+            tickRenderer:$.jqplot.CanvasAxisTickRenderer,
+            tickOptions:{
+                formatString:'%b %Y',
+                angle:-25
+            }
+            },{%endifequal%}
+            yaxis:{
+            label:'{% trans "Number"%}',
+            min:0
+            }
+        },
+        highlighter: {
+            show: true,
+            sizeAdjust: 7.5
+        },
+        series:[{% for label in dashboard.serie_labels %}
+            {%if forloop.counter0%}, {% endif %}{label:"{{label}}", showmarker:showmarker}{% endfor %}
+        ],
+        cursor:{
+            show: true,
+            zoom:true,
+            showTooltip:false
+        },
+        legend: { show:true, location: 'nw' }
+    });
+    }
+
+    $('#search_{{name}}').click(function (){
+        $.post("{% url 'dashboard-main-detail' item_name %}",
+                $("#{{name}}_form").serialize(),
+                function(data){
+                    $("#{{name}}-tab").parent().html(data);
+                });
+        return false;
+    });
+    {% endcomment %}
+</script>
\ No newline at end of file
diff --git a/ishtar_common/views_item.py b/ishtar_common/views_item.py
index 01475f6ae..145c56ca4 100644
--- a/ishtar_common/views_item.py
+++ b/ishtar_common/views_item.py
@@ -18,8 +18,8 @@ from django.contrib.staticfiles.templatetags.staticfiles import static
 from django.core.cache import cache
 from django.core.exceptions import ObjectDoesNotExist
 from django.core.urlresolvers import reverse, NoReverseMatch
-from django.db.models import Q, Count, ImageField, Func, ExpressionWrapper, \
-    FloatField
+from django.db.models import Q, Count, Sum, ImageField, Func, \
+    ExpressionWrapper, FloatField
 from django.db.models.fields import FieldDoesNotExist
 from django.http import HttpResponse
 from django.shortcuts import render
@@ -1205,6 +1205,25 @@ def _get_data_from_query_old(items, query_table_cols, request,
     return datas
 
 
+def _get_json_stats(items, stats_sum_variable, stats_modality_1,
+                    stats_modality_2):
+    q = items.values(stats_modality_1, stats_modality_2)
+    if stats_sum_variable == 'pk':
+        q = q.annotate(sum=Count('pk'))
+    else:
+        q = q.annotate(sum=Sum(stats_sum_variable))
+    data = []
+    for values in q.order_by(stats_modality_1, stats_modality_2).all():
+        modality_1 = values[stats_modality_1]
+        if not data or data[-1][0] != modality_1:
+            data.append([modality_1, []])
+        data[-1][1].append(
+            (values[stats_modality_2], values["sum"])
+        )
+    data = json.dumps({"data": data})
+    return HttpResponse(data, content_type='application/json')
+
+
 DEFAULT_ROW_NUMBER = 10
 # length is used by ajax DataTables requests
 EXCLUDED_FIELDS = ['length']
@@ -1252,10 +1271,14 @@ def get_item(model, func_name, default_name, extra_request_keys=None,
             data_type = dct.pop('type')
         if not data_type:
             data_type = 'json'
-        if data_type == "json":
+        if "json" in data_type:
             EMPTY = '[]'
 
-        if data_type not in ('json', 'csv', 'json-image', 'json-map'):
+        if data_type not in ('json', 'csv', 'json-image', 'json-map',
+                             'json-stats'):
+            return HttpResponse(EMPTY, content_type='text/plain')
+
+        if data_type == 'json-stats' and len(model.STATISTIC_MODALITIES) < 2:
             return HttpResponse(EMPTY, content_type='text/plain')
 
         model_to_check = model
@@ -1605,6 +1628,23 @@ def get_item(model, func_name, default_name, extra_request_keys=None,
         table_cols = list(table_cols)
         if data_type == "json-map":
             table_cols = []  # only pk for map
+        elif data_type == "json-stats":
+            stats_modality_1 = request_items.get("stats_modality_1", None)
+            stats_modality_2 = request_items.get("stats_modality_2", None)
+            if not stats_modality_1 or \
+                    stats_modality_1 not in model.STATISTIC_MODALITIES:
+                stats_modality_1 = model.STATISTIC_MODALITIES[0]
+            if not stats_modality_2 or \
+                    stats_modality_2 not in model.STATISTIC_MODALITIES:
+                stats_modality_2 = model.STATISTIC_MODALITIES[1]
+            stats_sum_variable = request_items.get('stats_sum_variable', None)
+            stats_sum_variable_keys = list(model.STATISTIC_SUM_VARIABLE.keys())
+            if not stats_sum_variable or \
+                    stats_sum_variable not in stats_sum_variable_keys:
+                stats_sum_variable = stats_sum_variable_keys[0]
+            return _get_json_stats(
+                items, stats_sum_variable, stats_modality_1, stats_modality_2)
+
         query_table_cols = []
         for idx, cols in enumerate(table_cols):
             if type(cols) not in (list, tuple):