sql/Pg-upgrade2/convert_columns_to_html_for_sending_html_emails.pl

   1 # @tag: convert_columns_to_html_for_sending_html_emails
   2 # @description: Versand von E-Mails in HTML: mehrere Text-Spalten nach HTML umwandeln
   3 # @depends: release_3_5_8
   4 package SL::DBUpgrade2::convert_columns_to_html_for_sending_html_emails;
   5
   6 use strict;
   7 use utf8;
   8
   9 use parent qw(SL::DBUpgrade2::Base);
  10
  11 use SL::HTML::Util;
  12
  13 sub convert_column {
  14   my ($self, $table, $id_column, $column_to_convert, $condition) = @_;
  15
  16   $condition = $condition ? "WHERE $condition" : "";
  17
  18   my $q_fetch = <<SQL;
  19     SELECT ${id_column}, ${column_to_convert}
  20     FROM ${table}
  21     ${condition}
  22 SQL
  23
  24   my $q_update = <<SQL;
  25     UPDATE ${table}
  26     SET ${column_to_convert} = ?
  27     WHERE ${id_column} = ?
  28 SQL
  29
  30   my $h_fetch = $self->dbh->prepare($q_fetch);
  31   $h_fetch->execute || $::form->dberror($q_fetch);
  32
  33   my $h_update = $self->dbh->prepare($q_update);
  34
  35   while (my $entry = $h_fetch->fetchrow_hashref) {
  36     $entry->{$column_to_convert} //= '';
  37     my $new_value = SL::HTML::Util->plain_text_to_html($entry->{$column_to_convert});
  38
  39     next if $entry->{$column_to_convert} eq $new_value;
  40
  41     $h_update->execute($new_value, $entry->{id}) || $::form->dberror($q_update);
  42   }
  43 }
  44
  45 sub run {
  46   my ($self) = @_;
  47
  48   $self->convert_column('defaults',                  'id', 'signature');
  49   $self->convert_column('employee',                  'id', 'deleted_signature');
  50   $self->convert_column('periodic_invoices_configs', 'id', 'email_body');
  51   $self->convert_column('generic_translations',      'id', 'translation', <<SQL);
  52     translation_type IN (
  53       'preset_text_sales_quotation', 'preset_text_sales_order', 'preset_text_sales_delivery_order',
  54       'preset_text_invoice', 'preset_text_invoice_direct_debit', 'preset_text_request_quotation',
  55       'preset_text_purchase_order', 'preset_text_periodic_invoices_email_body'
  56     )
  57 SQL
  58
  59   return 1;
  60 }
  61
  62 1;