From 2dc3a3f17418f86857ed13e415513e8246e3d770 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= Date: Wed, 24 Mar 2021 14:07:22 +0100 Subject: [PATCH] =?utf8?q?Mahnungen/DMS=20Upgrade-Skript,=20dass=20die=20D?= =?utf8?q?okumente=20zu=20den=20Mahnl=C3=A4ufen=20verschiebt.?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Vorher waren die Dokumente der gemahnten Rechnung zugordnet, nun werden sie dem Mahnlauf zugeordnet. Im Prinzip könnte nun dunning1-3, dunning_invoice und dunning_orig_invoice als object_type im Filemanagement entfallen, aber es kann sein, dass Dokumente nicht zugeordnet werden können, da z.B. die Mahnung gelöscht wurde. Dabei werden Dokumente im Moment nicht mitgelöscht. --- .../file_storage_dunning_documents.sql | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 sql/Pg-upgrade2/file_storage_dunning_documents.sql diff --git a/sql/Pg-upgrade2/file_storage_dunning_documents.sql b/sql/Pg-upgrade2/file_storage_dunning_documents.sql new file mode 100644 index 000000000..865ef057e --- /dev/null +++ b/sql/Pg-upgrade2/file_storage_dunning_documents.sql @@ -0,0 +1,17 @@ +-- @tag: file_storage_dunning_documents +-- @description: Dateien f. Mahnungen von gemahnter Rechnung zum Mahnlauf verschieben +-- @depends: file_storage_dunning_invoice + +-- for the original invoice, assume that the dunning_id is the one from a dunning row where the trans_id is +-- the old files object_id (the orig. invoice) and the itime of both tables are (almost) equal +WITH table_files AS + (SELECT dunning.dunning_id, files.id FROM files LEFT JOIN dunning ON (dunning.trans_id = files.object_id) + WHERE object_type ILIKE 'dunning_orig_invoice' AND file_type LIKE 'document' AND source LIKE 'created' + AND ABS(EXTRACT(EPOCH FROM (dunning.itime - files.itime))) < 0.1) + UPDATE files SET object_type = 'dunning', object_id = (SELECT dunning_id FROM table_files WHERE table_files.id = files.id) + WHERE EXISTS (SELECT id FROM table_files WHERE table_files.id = files.id); + +-- the dunning_id for the following types can be found in the filename +UPDATE files SET object_type = 'dunning', object_id = substring(file_name FROM '(\d+).pdf')::INT + WHERE (object_type LIKE 'dunning1' OR object_type LIKE 'dunning2' OR object_type LIKE 'dunning3' OR object_type LIKE 'dunning_invoice') + AND file_type LIKE 'document' AND source LIKE 'created'; -- 2.20.1