From 7c9b978add192dc16c380967999f5eb4117d5913 Mon Sep 17 00:00:00 2001
From: Moritz Bunkus <m.bunkus@linet.de>
Date: Wed, 12 Jan 2022 11:21:56 +0100
Subject: [PATCH] E-Mails als HTML verschicken: Konvertierung relevanter
 Datenbankfelder

---
 ...columns_to_html_for_sending_html_emails.pl | 46 ++++++++++++++
 ...columns_to_html_for_sending_html_emails.pl | 62 +++++++++++++++++++
 2 files changed, 108 insertions(+)
 create mode 100644 sql/Pg-upgrade2-auth/convert_columns_to_html_for_sending_html_emails.pl
 create mode 100644 sql/Pg-upgrade2/convert_columns_to_html_for_sending_html_emails.pl

diff --git a/sql/Pg-upgrade2-auth/convert_columns_to_html_for_sending_html_emails.pl b/sql/Pg-upgrade2-auth/convert_columns_to_html_for_sending_html_emails.pl
new file mode 100644
index 000000000..f8ffbe63f
--- /dev/null
+++ b/sql/Pg-upgrade2-auth/convert_columns_to_html_for_sending_html_emails.pl
@@ -0,0 +1,46 @@
+# @tag: convert_columns_to_html_for_sending_html_emails
+# @description: Versand von E-Mails in HTML: mehrere Text-Spalten nach HTML umwandeln
+# @depends: release_3_5_8
+package SL::DBUpgrade2::Auth::convert_columns_to_html_for_sending_html_emails;
+
+use strict;
+use utf8;
+
+use parent qw(SL::DBUpgrade2::Base);
+
+use SL::HTML::Util;
+
+sub run {
+  my ($self) = @_;
+
+  my $q_fetch = <<SQL;
+    SELECT user_id, cfg_key, cfg_value
+    FROM auth.user_config
+    WHERE (cfg_key = 'signature')
+SQL
+
+  my $q_update = <<SQL;
+    UPDATE auth.user_config
+    SET cfg_value = ?
+    WHERE (user_id = ?)
+      AND (cfg_key = 'signature')
+SQL
+
+  my $h_fetch = $self->dbh->prepare($q_fetch);
+  $h_fetch->execute || $::form->dberror($q_fetch);
+
+  my $h_update = $self->dbh->prepare($q_update);
+
+  while (my $entry = $h_fetch->fetchrow_hashref) {
+    $entry->{cfg_value} //= '';
+    my $new_value = SL::HTML::Util->plain_text_to_html($entry->{cfg_value});
+
+    next if $entry->{cfg_value} eq $new_value;
+
+    $h_update->execute($new_value, $entry->{user_id}) || $::form->dberror($q_update);
+  }
+
+  return 1;
+}
+
+1;
diff --git a/sql/Pg-upgrade2/convert_columns_to_html_for_sending_html_emails.pl b/sql/Pg-upgrade2/convert_columns_to_html_for_sending_html_emails.pl
new file mode 100644
index 000000000..168ea459f
--- /dev/null
+++ b/sql/Pg-upgrade2/convert_columns_to_html_for_sending_html_emails.pl
@@ -0,0 +1,62 @@
+# @tag: convert_columns_to_html_for_sending_html_emails
+# @description: Versand von E-Mails in HTML: mehrere Text-Spalten nach HTML umwandeln
+# @depends: release_3_5_8
+package SL::DBUpgrade2::convert_columns_to_html_for_sending_html_emails;
+
+use strict;
+use utf8;
+
+use parent qw(SL::DBUpgrade2::Base);
+
+use SL::HTML::Util;
+
+sub convert_column {
+  my ($self, $table, $id_column, $column_to_convert, $condition) = @_;
+
+  $condition = $condition ? "WHERE $condition" : "";
+
+  my $q_fetch = <<SQL;
+    SELECT ${id_column}, ${column_to_convert}
+    FROM ${table}
+    ${condition}
+SQL
+
+  my $q_update = <<SQL;
+    UPDATE ${table}
+    SET ${column_to_convert} = ?
+    WHERE ${id_column} = ?
+SQL
+
+  my $h_fetch = $self->dbh->prepare($q_fetch);
+  $h_fetch->execute || $::form->dberror($q_fetch);
+
+  my $h_update = $self->dbh->prepare($q_update);
+
+  while (my $entry = $h_fetch->fetchrow_hashref) {
+    $entry->{$column_to_convert} //= '';
+    my $new_value = SL::HTML::Util->plain_text_to_html($entry->{$column_to_convert});
+
+    next if $entry->{$column_to_convert} eq $new_value;
+
+    $h_update->execute($new_value, $entry->{id}) || $::form->dberror($q_update);
+  }
+}
+
+sub run {
+  my ($self) = @_;
+
+  $self->convert_column('defaults',                  'id', 'signature');
+  $self->convert_column('employee',                  'id', 'deleted_signature');
+  $self->convert_column('periodic_invoices_configs', 'id', 'email_body');
+  $self->convert_column('generic_translations',      'id', 'translation', <<SQL);
+    translation_type IN (
+      'preset_text_sales_quotation', 'preset_text_sales_order', 'preset_text_sales_delivery_order',
+      'preset_text_invoice', 'preset_text_invoice_direct_debit', 'preset_text_request_quotation',
+      'preset_text_purchase_order', 'preset_text_periodic_invoices_email_body'
+    )
+SQL
+
+  return 1;
+}
+
+1;
-- 
2.20.1