From 60d7d9edb2fe286fd6c1cf47b6df04cdadcc8a7c Mon Sep 17 00:00:00 2001 From: Étienne Loks Date: Fri, 10 May 2019 22:46:17 +0200 Subject: Statistics - manage queries --- archaeological_operations/models.py | 3 + archaeological_operations/tests.py | 29 +++++ ishtar_common/models.py | 10 +- .../templates/blocks/DataTables-stats.html | 125 +++++++++++++++++++++ ishtar_common/views_item.py | 48 +++++++- 5 files changed, 210 insertions(+), 5 deletions(-) create mode 100644 ishtar_common/templates/blocks/DataTables-stats.html 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 %} +
+
+ + + + + + + + +
+
+
+
+
+

+   + {% trans 'Draw rectangle on the graph to zoom. Double-click to reinitialize.' %} +

+
+
+ +
+
+
+
+
+
+
+   + {% trans 'Right-click on this image to save it.' %} +
+
+
+
+
+
+ +
+ + \ 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): -- cgit v1.2.3