Context records: show find inside related context records

8 files changed, 190 insertions, 16 deletions

diff --git a/archaeological_context_records/migrations/0106_views_related_cr.py b/archaeological_context_records/migrations/0106_views_related_cr.py
new file mode 100644
index 000000000..189bc3013
--- /dev/null
+++ b/archaeological_context_records/migrations/0106_views_related_cr.py
@@ -0,0 +1,38 @@
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.27 on 2020-12-01 13:29
+from __future__ import unicode_literals
+
+from django.db import migrations, models
+
+import archaeological_context_records.models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('archaeological_context_records', '0105_auto_20201117_0759'),
+    ]
+
+    operations = [
+        migrations.RunSQL(
+            archaeological_context_records.models.ContextRecordTree.DELETE_SQL
+        ),
+        migrations.RunSQL(
+            archaeological_context_records.models.ContextRecordTree.CREATE_SQL
+        ),
+        migrations.CreateModel(
+            name='ContextRecordTree',
+            fields=[
+                ('key', models.TextField(primary_key=True, serialize=False)),
+            ],
+            options={
+                'db_table': 'context_records_tree',
+                'managed': False,
+            },
+        ),
+        migrations.AlterField(
+            model_name='relationtype',
+            name='logical_relation',
+            field=models.CharField(blank=True, choices=[('above', 'Above'), ('below', 'Below'), ('equal', 'Equal'), ('include', 'Include'), ('included', 'Is included')], max_length=10, null=True, verbose_name='Logical relation'),
+        ),
+    ]
diff --git a/archaeological_context_records/models.py b/archaeological_context_records/models.py
index 0a7cd3d88..8a1011084 100644
--- a/archaeological_context_records/models.py
+++ b/archaeological_context_records/models.py
@@ -20,6 +20,7 @@
 from collections import OrderedDict
 import uuid
 
+from django.apps import apps
 from django.conf import settings
 from django.contrib.gis.db import models
 from django.contrib.postgres.indexes import GinIndex
@@ -406,7 +407,7 @@ class ContextRecord(BulkUpdatedItem, DocumentItem, BaseHistorizedItem,
         'cached_label': 'cached_label__icontains',
         'datings__period__label': 'datings__period__label',
         'operation_id': 'operation_id',
-        'unit__label': "unit__label"
+        'unit__label': "unit__label",
     }
     MANY_COUNTED_FIELDS = ['base_finds']
     REVERSED_BOOL_FIELDS = [
@@ -786,6 +787,13 @@ class ContextRecord(BulkUpdatedItem, DocumentItem, BaseHistorizedItem,
     def relation_label(self):
         return self.label
 
+    def all_base_finds(self):
+        BaseFind = apps.get_model("archaeological_finds", "BaseFind")
+        ids = [self.id] + [
+            cr.cr_id for cr in ContextRecordTree.objects.filter(
+                cr_parent_id=self.id)]
+        return BaseFind.objects.filter(context_record_id__in=ids)
+
     @property
     def show_url(self):
         return reverse('show-contextrecord', args=[self.pk, ''])
@@ -805,7 +813,7 @@ class ContextRecord(BulkUpdatedItem, DocumentItem, BaseHistorizedItem,
 
     @classmethod
     def get_query_owns(cls, ishtaruser):
-        q = cls._construct_query_own(
+        return cls._construct_query_own(
             'operation__', Operation._get_query_owns_dicts(ishtaruser)
         ) | cls._construct_query_own(
             'base_finds__find__basket__',
@@ -814,7 +822,6 @@ class ContextRecord(BulkUpdatedItem, DocumentItem, BaseHistorizedItem,
             {'history_creator': ishtaruser.user_ptr},
             {'operation__end_date__isnull': True}
         ])
-        return q
 
     @classmethod
     def get_owns(cls, user, menu_filtr=None, limit=None,
@@ -855,7 +862,8 @@ class ContextRecord(BulkUpdatedItem, DocumentItem, BaseHistorizedItem,
         return self.detailed_related_context_records()
 
     def _get_associated_cached_labels(self):
-        from archaeological_finds.models import Find, BaseFind
+        BaseFind = apps.get_model("archaeological_finds", "BaseFind")
+        Find = apps.get_model("archaeological_finds", "Find")
         return list(Find.objects.filter(base_finds__context_record=self).all())\
             + list(BaseFind.objects.filter(context_record=self).all())
 
@@ -932,10 +940,11 @@ class ContextRecord(BulkUpdatedItem, DocumentItem, BaseHistorizedItem,
         return q.count()
 
     def detailed_related_context_records(self):
-        crs = []
-        for cr in self.right_relations.all():
-            crs.append("{} ({})".format(cr.right_record,
-                                         cr.relation_type.get_tiny_label()))
+        crs = [
+            "{} ({})".format(cr.right_record, cr.relation_type.get_tiny_label())
+            for cr in self.right_relations.all()
+        ]
+
         return " & ".join(crs)
 
     def find_docs_q(self):
@@ -1087,3 +1096,91 @@ class RecordRelationView(models.Model):
 
     def __str__(self):
         return "{} \"{}\"".format(self.relation_type, self.right_record)
+
+
+class ContextRecordTree(models.Model):
+    CREATE_SQL = """
+    CREATE VIEW cr_parent_relation_id AS
+        SELECT id
+        FROM archaeological_context_records_relationtype
+        WHERE logical_relation in ('included', 'equal');
+
+    CREATE VIEW context_records_tree AS
+        WITH RECURSIVE rel_tree AS (
+          SELECT cr.id AS cr_id,
+                 cr.id AS cr_parent_id, 1 AS level,
+                 cr.id || '_' || cr.id || '_1' AS key
+            FROM archaeological_context_records_contextrecord cr
+         UNION ALL
+          SELECT rel.left_record_id AS cr_id,
+                 rel.right_record_id AS cr_parent_id, 1 AS level,
+                 rel.left_record_id || '_' || rel.right_record_id || '_1' AS key
+            FROM archaeological_context_records_recordrelations rel
+            WHERE rel.relation_type_id in (
+                SELECT id FROM cr_parent_relation_id
+            )
+         UNION ALL
+          SELECT p.cr_id AS cr_id,
+                 rel.right_record_id AS cr_parent_id,
+                 p.level + 1,
+                 p.cr_id || '_' || rel.right_record_id || '_' || p.level + 1 AS key
+            FROM archaeological_context_records_recordrelations rel, rel_tree p
+            WHERE rel.left_record_id = p.cr_parent_id
+                  AND rel.relation_type_id in (
+                    SELECT id FROM cr_parent_relation_id
+                  )
+                  AND p.level < 10 -- prevent recursive...
+        )
+        SELECT DISTINCT key, cr_id, cr_parent_id, level
+          FROM rel_tree;
+
+    CREATE VIEW context_record_tree AS
+        SELECT DISTINCT y.key, y.cr_id, y.cr_parent_id
+            FROM (SELECT * FROM context_records_tree) y
+        ORDER BY y.cr_id, y.cr_parent_id;
+
+    -- deactivate deletion, update
+    CREATE RULE context_records_tree_del AS
+        ON DELETE TO context_records_tree
+        DO INSTEAD DELETE FROM archaeological_context_records_contextrecord
+            WHERE id=NULL;
+    CREATE RULE context_record_tree_del AS
+        ON DELETE TO context_record_tree
+        DO INSTEAD DELETE FROM archaeological_context_records_contextrecord
+            WHERE id=NULL;
+    CREATE RULE context_records_tree_update AS
+        ON UPDATE TO context_records_tree
+        DO INSTEAD UPDATE archaeological_context_records_contextrecord
+            SET id=id WHERE id=NULL;
+    CREATE RULE context_record_tree_update AS
+        ON UPDATE TO context_record_tree
+        DO INSTEAD UPDATE archaeological_context_records_contextrecord
+            SET id=id WHERE id=NULL;
+    CREATE RULE context_records_tree_insert AS
+        ON INSERT TO context_records_tree
+        DO INSTEAD UPDATE archaeological_context_records_contextrecord
+            SET id=id WHERE id=NULL;
+    CREATE RULE context_record_tree_insert AS
+        ON INSERT TO context_record_tree
+        DO INSTEAD UPDATE archaeological_context_records_contextrecord
+            SET id=id WHERE id=NULL;
+    """
+    DELETE_SQL = """
+    DROP VIEW IF EXISTS context_record_tree;
+    DROP VIEW IF EXISTS context_records_tree;
+    DROP VIEW IF EXISTS cr_parent_relation_id;
+    """
+    key = models.TextField(primary_key=True)
+    cr = models.ForeignKey(
+        "archaeological_context_records.ContextRecord",
+        verbose_name=_("Context record"),
+        related_name="context_record_tree_parent")
+    cr_parent = models.ForeignKey(
+        "archaeological_context_records.ContextRecord",
+        verbose_name=_("Context record parent"),
+        related_name="context_record_tree_child")
+
+    class Meta:
+        managed = False
+        db_table = 'context_records_tree'
+
diff --git a/archaeological_context_records/templates/ishtar/sheet_contextrecord.html b/archaeological_context_records/templates/ishtar/sheet_contextrecord.html
index 95f4205f8..e15a07f70 100644
--- a/archaeological_context_records/templates/ishtar/sheet_contextrecord.html
+++ b/archaeological_context_records/templates/ishtar/sheet_contextrecord.html
@@ -238,8 +238,12 @@
        role="tabpanel" aria-labelledby="{{window_id}}-finds-tab">
 
         {% trans "Finds" as finds %}
-        {% if item.base_finds.count %}
-        {% dynamic_table_document finds 'finds_for_ope' 'base_finds__context_record' item.pk 'TABLE_COLS_FOR_OPE' output %}
+        {% if item.all_base_finds.count %}
+        {% dynamic_table_document finds 'finds_for_cr' 'all_base_finds__context_record' item.pk 'TABLE_COLS_FOR_CR' output %}
+        {% else %}
+        <div class="alert alert-info">
+            {% trans "No associated finds." %}
+        </div>
         {% endif %}
 
         {% trans "Documents from associated finds" as finds_docs %}
diff --git a/archaeological_finds/models_finds.py b/archaeological_finds/models_finds.py
index 9b1f43cb6..6bb8d042e 100644
--- a/archaeological_finds/models_finds.py
+++ b/archaeological_finds/models_finds.py
@@ -810,17 +810,31 @@ class Find(BulkUpdatedItem, ValueGetter, DocumentItem, BaseHistorizedItem,
         'description',
         'base_finds__context_record__town__name',
         'base_finds__context_record__parcel', ]
+    TABLE_COLS_FOR_CR = [
+        'base_finds__cache_short_id',
+        'base_finds__cache_complete_id',
+        'previous_id', 'label',
+        'base_finds__context_record__label',
+        'cached_materials',
+        'cached_periods',
+        'find_number',
+        'cached_object_types',
+        'container__cached_label',
+        'container_ref__cached_label',
+        'description',
+        'base_finds__context_record__town__name',
+        'base_finds__context_record__parcel', ]
     NEW_QUERY_ENGINE = True
     COL_LABELS = {
-        'base_finds__context_record__label': _(u"Context record"),
-        'base_finds__cache_short_id': _(u"Base find - Short ID"),
-        'base_finds__cache_complete_id': _(u"Base find - Complete ID"),
+        'base_finds__context_record__label': _("Context record"),
+        'base_finds__cache_short_id': _("Base find - Short ID"),
+        'base_finds__cache_complete_id': _("Base find - Complete ID"),
         'base_finds__context_record__operation__code_patriarche': _(
-            u"Operation (code)"
+            "Operation (code)"
         ),
         'base_finds__context_record__town__name': _(u"Town"),
         'base_finds__context_record__operation__common_name': _(
-            u"Operation (name)"
+            "Operation (name)"
         ),
         'base_finds__context_record__archaeological_site__name':
             IshtarSiteProfile.get_default_site_label,
@@ -917,6 +931,8 @@ class Find(BulkUpdatedItem, ValueGetter, DocumentItem, BaseHistorizedItem,
     ]
     BASE_REQUEST = {'downstream_treatment__isnull': True}
     EXTRA_REQUEST_KEYS = {
+        'all_base_finds__context_record':
+            'base_finds__context_record__context_record_tree_parent__cr_parent_id',
         'base_finds__context_record':
             'base_finds__context_record__pk',
         'base_finds__context_record__archaeological_site':
diff --git a/archaeological_finds/urls.py b/archaeological_finds/urls.py
index bab3fc6b4..7bf85d9a4 100644
--- a/archaeological_finds/urls.py
+++ b/archaeological_finds/urls.py
@@ -265,6 +265,10 @@ urlpatterns = [
         name='get-own-find-for-ope', kwargs={'force_own': True}),
     url(r'get-find-for-ope/(?P<type>.+)?$', views.get_find_for_ope,
         name='get-find-for-ope'),
+    url(r'get-find-for-cr/cr/(?P<type>.+)?$', views.get_find_for_cr,
+        name='get-own-find-for-cr', kwargs={'force_own': True}),
+    url(r'get-find-for-cr/(?P<type>.+)?$', views.get_find_for_cr,
+        name='get-find-for-cr'),
     url(r'get-find-for-treatment/own/(?P<type>.+)?$',
         views.get_find_for_treatment,
         name='get-own-find-for-treatment', kwargs={'force_own': True}),
diff --git a/archaeological_finds/views.py b/archaeological_finds/views.py
index c843f02d2..4e4103224 100644
--- a/archaeological_finds/views.py
+++ b/archaeological_finds/views.py
@@ -66,6 +66,13 @@ def get_table_cols_for_ope():
     return settings.TABLE_COLS[tb_key]
 
 
+def get_table_cols_for_cr():
+    tb_key = (models.Find.SLUG, "TABLE_COLS_FOR_CR")
+    if tb_key not in settings.TABLE_COLS:
+        return models.Find.TABLE_COLS_FOR_CR
+    return settings.TABLE_COLS[tb_key]
+
+
 def get_table_cols_for_container():
     table_cols = models.Find.TABLE_COLS
     tb_key = ("find", 'TABLE_COLS')
@@ -77,6 +84,10 @@ def get_table_cols_for_container():
 get_find_for_ope = get_item(models.Find, 'get_find', 'find',
                             own_table_cols=get_table_cols_for_ope())
 
+
+get_find_for_cr = get_item(models.Find, 'get_find', 'find',
+                           own_table_cols=get_table_cols_for_cr())
+
 get_find_for_treatment = get_item(
     models.Find, 'get_find', 'find',
     own_table_cols=get_table_cols_for_ope(), base_request={})
diff --git a/ishtar_common/models.py b/ishtar_common/models.py
index 722e134ac..3a8182d2f 100644
--- a/ishtar_common/models.py
+++ b/ishtar_common/models.py
@@ -579,7 +579,9 @@ class JsonDataField(models.Model):
 LOGICAL_TYPES = (
     ('above', _("Above")),
     ('below', _("Below")),
-    ('equal', _("Equal"))
+    ('equal', _("Equal")),
+    ('include', _("Include")),
+    ('included', _("Is included")),
 )
 
 
diff --git a/ishtar_common/templatetags/window_tables.py b/ishtar_common/templatetags/window_tables.py
index a96460869..f91339cb2 100644
--- a/ishtar_common/templatetags/window_tables.py
+++ b/ishtar_common/templatetags/window_tables.py
@@ -51,6 +51,8 @@ ASSOCIATED_MODELS['finds'] = (Find, 'get-find', 'get-find-full')
 ASSOCIATED_MODELS['sites'] = (ArchaeologicalSite, 'get-site', '')
 ASSOCIATED_MODELS['finds_for_ope'] = (
     Find, 'get-find-for-ope', 'get-find-full')
+ASSOCIATED_MODELS['finds_for_cr'] = (
+    Find, 'get-find-for-cr', 'get-find-full')
 ASSOCIATED_MODELS['finds_for_treatment'] = (
     Find, 'get-find-for-treatment', 'get-find-full')
 ASSOCIATED_MODELS['finds_upstreamtreatments'] = (