diff options
Diffstat (limited to 'archaeological_context_records/models.py')
| -rw-r--r-- | archaeological_context_records/models.py | 104 |
1 files changed, 104 insertions, 0 deletions
diff --git a/archaeological_context_records/models.py b/archaeological_context_records/models.py index 377cea087..4fcbcf258 100644 --- a/archaeological_context_records/models.py +++ b/archaeological_context_records/models.py @@ -20,6 +20,7 @@ from django.conf import settings from django.contrib.gis.db import models from django.core.urlresolvers import reverse +from django.db import connection, transaction from django.db.models import Q from django.db.models.signals import post_delete, post_save from django.utils.translation import ugettext_lazy as _, ugettext, pgettext @@ -118,6 +119,26 @@ post_save.connect(post_save_cache, sender=IdentificationType) post_delete.connect(post_save_cache, sender=IdentificationType) +class CRBulkView(object): + CREATE_SQL = """ + CREATE VIEW context_records_cached_label_bulk_update + AS ( + SELECT cr.id AS id, ope.code_patriarche AS main_code, + ope.year AS year, + ope.operation_code AS ope_code, + parcel.section AS section, + parcel.parcel_number AS number, cr.label AS label + FROM archaeological_context_records_contextrecord AS cr + INNER JOIN archaeological_operations_operation ope + ON ope.id = cr.operation_id + INNER JOIN archaeological_operations_parcel parcel + ON cr.parcel_id = parcel.id + );""" + DELETE_SQL = """ + DROP VIEW context_records_cached_label_bulk_update; + """ + + class ContextRecord(BaseHistorizedItem, ImageModel, OwnPerms, ValueGetter, ShortMenuItem): SHOW_URL = 'show-contextrecord' @@ -253,6 +274,89 @@ class ContextRecord(BaseHistorizedItem, ImageModel, OwnPerms, def __unicode__(self): return self.short_label + @classmethod + def cached_label_bulk_update(cls, operation_id): + sql = """ + UPDATE "archaeological_context_records_contextrecord" AS cr + SET cached_label = + CASE context_records_cached_label_bulk_update.main_code + WHEN NULL THEN + CASE context_records_cached_label_bulk_update.year + IS NOT NULL + AND context_records_cached_label_bulk_update.ope_code + IS NOT NULL + WHEN TRUE THEN + '{ope_prefix}' || + context_records_cached_label_bulk_update.year || + '-' || + context_records_cached_label_bulk_update.ope_code + ELSE '' + END + ELSE + '{main_ope_prefix}' || + context_records_cached_label_bulk_update.main_code + END + || '{join}' || + context_records_cached_label_bulk_update.section || '{join}' || + context_records_cached_label_bulk_update.number || '{join}' || + context_records_cached_label_bulk_update.label + FROM t + WHERE cr.id = context_records_cached_label_bulk_update.id + AND cr.id IN ( + SELECT id FROM archaeological_context_records_contextrecord + WHERE operation_id = %s + ); + """.format(main_ope_prefix=settings.ISHTAR_OPE_PREFIX, + ope_prefix=settings.ISHTAR_DEF_OPE_PREFIX, + join=settings.JOINT) + c = connection.cursor() + # with connection.cursor() as c: + c.execute(sql, [int(operation_id)]) + transaction.commit_unless_managed() + ''' + sql = """ + WITH t AS ( + SELECT cr.id as id, ope.code_patriarche as main_code, + ope.year as year, + ope.operation_code as ope_code, + parcel.section as section, + parcel.parcel_number as number, cr.label as label + FROM archaeological_context_records_contextrecord AS cr + INNER JOIN archaeological_operations_operation ope + ON ope.id = cr.operation_id + INNER JOIN archaeological_operations_parcel parcel + ON cr.parcel_id = parcel.id + ) + + UPDATE archaeological_context_records_contextrecord AS cr + SET cached_label = + CASE t.main_code + WHEN NULL THEN + CASE t.year IS NOT NULL AND t.ope_code IS NOT NULL + WHEN TRUE THEN + '{ope_prefix}' || t.year || '-' || t.ope_code + ELSE '' + END + ELSE + '{main_ope_prefix}' || t.main_code + END + || + '{join}' || t.section || '{join}' || + t.number || '{join}' || t.label + FROM t + WHERE cr.id = t.id AND cr.id IN ( + SELECT id FROM archaeological_context_records_contextrecord + WHERE operation_id = {ope_id} + ); + """.format(main_ope_prefix=settings.ISHTAR_OPE_PREFIX, + ope_prefix=settings.ISHTAR_DEF_OPE_PREFIX, + join=settings.JOINT, ope_id=operation_id) + + cursor = connection.cursor() + cursor.execute(sql) + + ''' + @property def short_label(self): return settings.JOINT.join([unicode(item) for item in [ |