Syndication - update keys from match document

6 files changed, 183 insertions, 14 deletions

diff --git a/archaeological_operations/tests.py b/archaeological_operations/tests.py
index 409ee5079..69d3e7f26 100644
--- a/archaeological_operations/tests.py
+++ b/archaeological_operations/tests.py
@@ -31,6 +31,7 @@ from django.apps import apps
 from django.conf import settings
 from django.contrib.auth.models import Group
 from django.contrib.contenttypes.models import ContentType
+from django.core.files import File as DjangoFile
 from django.core.files.uploadedfile import SimpleUploadedFile
 from django.db.models import Q
 from django.test import tag
@@ -4711,6 +4712,48 @@ class ApiTest(OperationInitTest, APITestCase):
                     ope_type = str(ope_type).replace("'", "'")
                     self.assertIn(ope_type, content)
 
+    @tag("libreoffice")
+    @patch("requests.get")
+    def test_match_doc_update(self, mock_get):
+        if not settings.USE_LIBREOFFICE:
+            return
+        self._mock_request(mock_get)
+        self.client.login(username=self.username, password=self.password)
+        source = self._get_source()
+
+        url = "/admin/{}/{}/".format(
+            "ishtar_common", models_rest.ApiExternalSource.__name__.lower()
+        )
+        params = {
+            "action": "update_types_from_source",
+            "_selected_action": [source.pk],
+        }
+        self.client.post(url, params, follow=True)
+
+        base = "default-source.ods"
+        filename = settings.ROOT_PATH + "../archaeological_operations/tests/" + base
+        with open(filename, "rb") as fle:
+            source.match_document.save(base, DjangoFile(fle))
+
+        params["action"] = "update_association_from_match_document"
+        response = self.client.post(url, params, follow=True).content.decode()
+        result = {"updated": 4}
+        msg = str(_(f"{result['updated']} match key(s) updated."))
+        self.assertIn(msg, response)
+        ctype = "archaeological_operations.remaintype"
+        distant_key = distant_label = "ail"
+        msg = str(
+            _(
+                f"{ctype} - {distant_key}, {distant_label} not referenced in the database."
+            )
+        )
+        self.assertIn(msg, response)
+        sheet_name = (
+            "archaeological_operations.operation-archaeological_operations.ReportState"
+        )
+        msg = str(_(f"{sheet_name} is not a correct sheet name."))
+        self.assertIn(msg, response)
+
     def test_query_transformation(self):
         # POV: local
         # change query terms from a source Ishtar to match distant Ishtar
diff --git a/archaeological_operations/tests/default-source.ods b/archaeological_operations/tests/default-source.ods
new file mode 100644
index 000000000..b31497281
--- /dev/null
+++ b/archaeological_operations/tests/default-source.ods
diff --git a/ishtar_common/admin.py b/ishtar_common/admin.py
index a974823a5..b1f32620c 100644
--- a/ishtar_common/admin.py
+++ b/ishtar_common/admin.py
@@ -2279,6 +2279,24 @@ def update_association_from_match_document(modeladmin, request, queryset):
             message_type=messages.WARNING,
         )
     result = queryset.all()[0].update_from_match_document()
+    if not result:
+        messages.add_message(
+            request,
+            messages.ERROR,
+            str(_("Error on update. Cannot open match document.")),
+        )
+    else:
+        if result["errors"]:
+            errors = (
+                "<p>" + str(_("Error on type update from match document:")) + "</p>"
+            )
+            errors += "<ul><li>" + "</li><li>".join(result["errors"]) + "</li></ul>"
+            messages.add_message(request, messages.ERROR, mark_safe(errors))
+        messages.add_message(
+            request,
+            messages.WARNING,
+            str(_(f"{result['updated']} match key(s) updated.")),
+        )
     return HttpResponseRedirect(return_url)
 
 
diff --git a/ishtar_common/libreoffice.py b/ishtar_common/libreoffice.py
index 6337675fe..482364a73 100644
--- a/ishtar_common/libreoffice.py
+++ b/ishtar_common/libreoffice.py
@@ -74,12 +74,22 @@ class UnoCalc(UnoClient):
     def create_calc(self):
         return self.create_document('scalc')
 
+    def open_calc(self, filename):
+        return self.get_document(filename)
+
     def save_calc(self, calc, filename):
         url = "file://{}".format(filename)
         args = (PropertyValue(Name="FilterName", Value="Calc8"),)
         calc.storeToURL(url, args)
 
-    def get_sheet(self, calc, sheet_index, name=None):
+    def get_sheet_number(self, calc):
+        sheets = calc.getSheets()
+        return sheets.Count
+
+    def get_sheet(self, calc, sheet_index):
+        return calc.getSheets().getByIndex(sheet_index)
+
+    def get_or_create_sheet(self, calc, sheet_index, name=None):
         sheets = calc.getSheets()
         while sheets.Count < (sheet_index + 1):
             if name and sheets.Count == sheet_index:
@@ -89,6 +99,30 @@ class UnoCalc(UnoClient):
             sheets.insertNewByName(sheet_name, sheets.Count)
         return calc.getSheets().getByIndex(sheet_index)
 
+    def sheet_get_last_row_number(self, sheet):
+        cursor = sheet.createCursor()
+        cursor.gotoEndOfUsedArea(False)
+        return cursor.getRangeAddress().EndRow + 1
+
+    def sheet_get_data(self, sheet, last_column=None):
+        if not last_column:
+            # assume the first line is header with no empty columns
+            last_column = 0
+            has_data = True
+            while has_data:
+                has_data = sheet.getCellByPosition(last_column, 0).getString()
+                if not has_data:
+                    break
+                last_column += 1
+        last_row = self.sheet_get_last_row_number(sheet)
+        data = []
+        for row in range(last_row):
+            data.append([])
+            for col in range(last_column):
+                value = sheet.getCellByPosition(col, row).getString()
+                data[-1].append(value)
+        return data
+
     def create_list(self, sheet, col, start_row, title, items):
         items = [title] + items
         row_idx = 0
@@ -147,11 +181,11 @@ class UnoCalc(UnoClient):
         if not lst:  # no list
             return
         calc = self.create_calc()
-        lst_sheet = self.get_sheet(calc, 1, "List types")
+        lst_sheet = self.get_or_create_sheet(calc, 1, "List types")
 
         lst_col, lst_row = 0, 0
         end_row = self.create_list(lst_sheet, lst_col, lst_row, title, lst)
-        main_sheet = self.get_sheet(calc, 0)
+        main_sheet = self.get_or_create_sheet(calc, 0)
         self.set_cell_validation_list(main_sheet, 0, 0, 200, lst_sheet, lst_col,
                                       [lst_row + 1, end_row])
         self.save_calc(calc, "/tmp/test.ods")
@@ -162,7 +196,7 @@ class UnoCalc(UnoClient):
         propval.Value = True
         filename = "/tmp/main.ods"
         calc = self.get_document(filename, (propval,))
-        sheet = self.get_sheet(calc, 0)
+        sheet = self.get_or_create_sheet(calc, 0)
         validation = sheet.getCellByPosition(0, 0).Validation
         for k in dir(validation):
             if k.startswith("get"):
diff --git a/ishtar_common/models_imports.py b/ishtar_common/models_imports.py
index e530b54e3..799b3aab9 100644
--- a/ishtar_common/models_imports.py
+++ b/ishtar_common/models_imports.py
@@ -171,8 +171,8 @@ class ImporterType(models.Model):
         uno = UnoCalc()
         calc = uno.create_calc()
 
-        main_sheet = uno.get_sheet(calc, 0, self.name)
-        lst_sheet = uno.get_sheet(calc, 1, str(_("List types")))
+        main_sheet = uno.get_or_create_sheet(calc, 0, self.name)
+        lst_sheet = uno.get_or_create_sheet(calc, 1, str(_("List types")))
 
         if not calc:
             return
diff --git a/ishtar_common/models_rest.py b/ishtar_common/models_rest.py
index e29356567..cc07a8803 100644
--- a/ishtar_common/models_rest.py
+++ b/ishtar_common/models_rest.py
@@ -2,11 +2,13 @@ import datetime
 import os
 import tempfile
 
+from django.apps import apps
 from django.conf import settings
 from django.contrib.auth.models import User
 from django.contrib.contenttypes.models import ContentType
 from django.contrib.gis.db import models
 from django.contrib.postgres.fields import ArrayField
+from django.core.files import File
 from django.utils.text import slugify
 
 try:
@@ -153,14 +155,13 @@ class ApiExternalSource(models.Model):
             .values_list("associated_type", flat=True)
             .distinct()
         )
-        lst_sheet = uno.get_sheet(calc, len(types), str(_("List types")))
+        lst_sheet = uno.get_or_create_sheet(calc, len(types), str(_("List types")))
         for idx, tpe in enumerate(types):
             self._generate_match_page(idx, tpe, uno, calc, lst_sheet)
         tmpdir = tempfile.mkdtemp(prefix="ishtar-matches-")
         base = "{}-{}.ods".format(datetime.date.today().isoformat(), slugify(self.name))
         dest_filename = "{}{}{}".format(tmpdir, os.sep, base)
         uno.save_calc(calc, dest_filename)
-        from django.core.files import File
 
         with open(dest_filename, "rb") as fle:
             self.match_document = File(fle, base)
@@ -170,8 +171,14 @@ class ApiExternalSource(models.Model):
     def _generate_match_page(self, page_number, tpe, uno, calc, lst_sheet):
         model = ContentType.objects.get(pk=tpe).model_class()
         ROW_NUMBER = 1000
-        sheet = uno.get_sheet(calc, page_number)
-        sheet.Name = str(model._meta.verbose_name)
+        sheet = uno.get_or_create_sheet(calc, page_number)
+        q = ApiKeyMatch.objects.filter(source=self, associated_type=tpe)
+        if not q.count():
+            return
+        sm = q.all()[0].search_model
+        sheet.Name = (
+            f"{sm.app_label}.{sm.model}-{model._meta.app_label}.{model.__name__.lower()}"
+        )
         for col_number, column in enumerate(
             (_("Distant key"), _("Distant label"), _("Local"))
         ):
@@ -180,9 +187,7 @@ class ApiExternalSource(models.Model):
             cell.CharWeight = 150
             cell.setString(str(column))
 
-        for idx, match in enumerate(
-            ApiKeyMatch.objects.filter(source=self, associated_type=tpe).all()
-        ):
+        for idx, match in enumerate(q.all()):
             cell = sheet.getCellByPosition(0, idx + 1)
             cell.setString(match.distant_slug)
             cell = sheet.getCellByPosition(1, idx + 1)
@@ -207,7 +212,76 @@ class ApiExternalSource(models.Model):
         )
 
     def update_from_match_document(self):
-        pass
+        if not self.match_document:
+            return
+        if not UnoCalc:
+            return
+        uno = UnoCalc()
+        calc = uno.open_calc(self.match_document.path)
+        if not calc:
+            return
+        errors = []
+        updated = 0
+        for idx in range(uno.get_sheet_number(calc) - 1):  # do not read the last sheet
+            sheet = uno.get_sheet(calc, idx)
+            sheet_name = sheet.Name
+            try:
+                search_model_name, ctype = sheet.Name.split("-")
+                app_label, model_name = search_model_name.split(".")
+                search_model = ContentType.objects.get(
+                    app_label=app_label, model=model_name
+                )
+            except (ValueError, ContentType.DoesNotExist):
+                errors.append(str(_(f"{sheet_name} is not a correct sheet name.")))
+                continue
+            try:
+                app_label, model_name = ctype.split(".")
+                ct = ContentType.objects.get(app_label=app_label, model=model_name)
+            except (ValueError, ContentType.DoesNotExist):
+                errors.append(str(_(f"{sheet_name} is not a correct sheet name.")))
+                continue
+            data = uno.sheet_get_data(sheet)
+            base_q = ApiKeyMatch.objects.filter(
+                source=self, search_model=search_model, associated_type=ct
+            )
+            model = ct.model_class()
+            for idx_line, line in enumerate(data):
+                if not idx_line:  # header
+                    continue
+                distant_key, distant_label, local_name = line
+                q = base_q.filter(distant_slug=distant_key, distant_label=distant_label)
+                if not q.count():
+                    errors.append(
+                        str(
+                            _(
+                                f"{ctype} - {distant_key}, {distant_label} not referenced in the database."
+                            )
+                        )
+                    )
+                    continue
+                if q.filter(local_label=local_name).count():
+                    # no change
+                    continue
+                api_key = q.all()[0]
+                key_name = "slug"
+                if hasattr(model, "txt_idx"):
+                    key_name = "txt_idx"
+                q = model.objects.filter(label=local_name)
+                if not q.count():
+                    errors.append(
+                        str(
+                            _(
+                                f"{ctype} - {local_name} do not exists in local database."
+                            )
+                        )
+                    )
+                    continue
+                tpe = q.all()[0]
+                api_key.local_slug = getattr(tpe, key_name)
+                api_key.local_label = local_name
+                api_key.save()
+                updated += 1
+        return {"errors": errors, "updated": updated}
 
 
 class ApiKeyMatch(models.Model):