X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=sql%2FPg-upgrade2%2Fcp_greeting_migration.pl;h=cb2ed66cff9972f8e5b4af1c5d7ec4d1986dcc3c;hb=c5057972d03f3546494fabe72224785e5a0a1714;hp=3b978a542aa6090de95cbc13dd48dac4320ebf4a;hpb=03ca3d74f96bf05a9c632b3e0f9538785d8a0fc6;p=kivitendo-erp.git diff --git a/sql/Pg-upgrade2/cp_greeting_migration.pl b/sql/Pg-upgrade2/cp_greeting_migration.pl index 3b978a542..cb2ed66cf 100644 --- a/sql/Pg-upgrade2/cp_greeting_migration.pl +++ b/sql/Pg-upgrade2/cp_greeting_migration.pl @@ -1,125 +1,132 @@ # @tag: cp_greeting_migration # @description: Migration of cp_greeting to cp_gender # @depends: generic_translations +package SL::DBUpgrade2::cp_greeting_migration; use strict; +use utf8; -die("This script cannot be run from the command line.") unless ($main::form); - -sub mydberror { - my ($msg) = @_; - die($dbup_locale->text("Database update error:") . - "
$msg
" . $DBI::errstr); -} - -sub do_query { - my ($query, $may_fail) = @_; - - if (!$dbh->do($query)) { - mydberror($query) unless ($may_fail); - $dbh->rollback(); - $dbh->begin_work(); - } -} - +use parent qw(SL::DBUpgrade2::Base); sub query_result { + my ($self) = @_; # list of all entries where cp_greeting is empty, meaning can't determine gender from parsing Herr/Frau/... # this assumes cp_greeting still exists, i.e. gender.sql was not run yet my ($gender_table, $mchecked, $fchecked); my $sql2 = "select cp_id,cp_givenname,cp_name,cp_title,cp_greeting from contacts where not (cp_greeting ILIKE '%frau%' OR cp_greeting ILIKE '%herr%' or cp_greeting ILIKE '%mrs.%' or cp_greeting ILIKE '%miss%') "; - my $sth2 = $dbh->prepare($sql2) or die $dbh->errstr(); - $sth2->execute() or die $dbh->errstr(); + my $sth2 = $self->dbh->prepare($sql2) or die $self->dbh->errstr(); + $sth2->execute() or die $self->dbh->errstr(); my $i = 1; $gender_table .= ''; $gender_table .= "\n"; while (my $row = $sth2->fetchrow_hashref()) { - if ($main::form->{"gender_$i"} eq "f" ) { - $mchecked = ""; - $fchecked = "checked"; - } else { - $mchecked = "checked"; - $fchecked = ""; - }; - - $gender_table .= "{cp_id}\"> \n"; - $i++; - }; + if ($::form->{"gender_$i"} eq "f" ) { + $mchecked = ""; + $fchecked = "checked"; + } else { + $mchecked = "checked"; + $fchecked = ""; + }; + + $gender_table .= "{cp_id}\"> \n"; + $i++; + } $gender_table .= ""; $gender_table .= "
cp_givennamecp_namecp_titlecp_greetingmale/female
$row->{cp_givenname} $row->{cp_name} $row->{cp_title} $row->{cp_greeting}
$row->{cp_givenname} $row->{cp_name} $row->{cp_title} $row->{cp_greeting}
"; - $main::form->{gender_table} = $gender_table; + $::form->{gender_table} = $gender_table; my $title_table; my $sql3 = "select cp_id,cp_givenname,cp_name,cp_title,cp_greeting from contacts where not ( (cp_greeting ILIKE '%frau%' OR cp_greeting ILIKE '%herr%' or cp_greeting ILIKE '%mrs.%' or cp_greeting ILIKE '%miss%')) and not (cp_greeting like ''); "; - my $sth3 = $dbh->prepare($sql3) or die $dbh->errstr(); - $sth3->execute() or die $dbh->errstr(); + my $sth3 = $self->dbh->prepare($sql3) or die $self->dbh->errstr(); + $sth3->execute() or die $self->dbh->errstr(); $title_table = ''; my $j = 1; while (my $row = $sth3->fetchrow_hashref()) { # Vorschlagsfeld fuer neuen Titel mit Werten von cp_greeting und cp_title vorbelegen - my $value = "$row->{cp_greeting}"; - $value .= " " if $row->{cp_greeting}; - $value .= "$row->{cp_title}"; + my $value = "$row->{cp_greeting}"; + $value .= " " if $row->{cp_greeting}; + $value .= "$row->{cp_title}"; - $title_table .= "\n"; - $j++; - }; + $title_table .= "\n"; + $j++; + } $title_table .= ""; $title_table .= "
cp_givennamecp_namecp_titlecp_greetingcp_title new
{cp_id}> $row->{cp_givenname} $row->{cp_name}$row->{cp_title} $row->{cp_greeting}
{cp_id}> $row->{cp_givenname} $row->{cp_name}$row->{cp_title} $row->{cp_greeting}
"; - $main::form->{title_table} = $title_table; + $::form->{title_table} = $title_table; +} -}; +sub print_question { + my ($self) = @_; + $self->query_result; + # parse html form in /templates/webpages/dbupgrade/cp_greeting_update_form + print $::form->parse_html_template("dbupgrade/cp_greeting_update_form"); +} +sub alter_schema_only { + my ($self) = @_; -sub print_question { - query_result(); - # parse html form in /templates/webpages/dbupgrade/cp_greeting_update_form - print $main::form->parse_html_template("dbupgrade/cp_greeting_update_form"); + my $sqlcode = <dbh->do($sqlcode); } -sub do_update { +sub run { + my ($self) = @_; + # main function + # Do not ask the user anything if there are no entries in the + # contacts table. + my ($data_exists) = $self->dbh->selectrow_array("SELECT * FROM contacts LIMIT 1"); + if (!$data_exists) { + $self->alter_schema_only; + return 1; + } + # first of all check if gender.sql was already run and thus cp_gender exists # if it exists there is no need for this update anymore, so return # without doing anything my $column_exists = 1; - if (!$dbh->do("SELECT cp_gender FROM contacts LIMIT 1")) { - $dbh->rollback(); - $dbh->begin_work(); + if (!$self->dbh->do("SELECT cp_gender FROM contacts LIMIT 1")) { + $self->dbh->rollback(); + $self->dbh->begin_work(); $column_exists = 0; } return 1 if $column_exists; - if (!$main::form->{do_migrate}) { + if (!$::form->{do_migrate}) { # case 1: first call of page - set_default_greetings(); - print_question(); + $self->set_default_greetings; + $self->print_question; return 2; - } else { - # case 2: submit button was pressed, hidden field do_migrate was set - migrate_data(); - }; + } -return 1; + # case 2: submit button was pressed, hidden field do_migrate was set + $self->migrate_data; + + return 1; } sub migrate_data { + my ($self) = @_; my $sqlcode = <{number_of_gender_entries}; $i++ ) { - next unless $main::form->{"cp_id_$i"}; - if ( $main::form->{"gender_$i"} eq "f" ) { - $sqlcode .= "UPDATE contacts SET cp_gender = \'f\' WHERE cp_id = $main::form->{\"cp_id_$i\"};\n"; - }; - }; + for (my $i = 1; $i <= $::form->{number_of_gender_entries}; $i++ ) { + next unless $::form->{"cp_id_$i"}; + if ( $::form->{"gender_$i"} eq "f" ) { + $sqlcode .= "UPDATE contacts SET cp_gender = \'f\' WHERE cp_id = $::form->{\"cp_id_$i\"};\n"; + } + } - for (my $i = 1; $i <= $main::form->{number_of_title_entries}; $i++ ) { - next unless $main::form->{"cp_id_title_$i"} and $main::form->{"cp_id_$i"}; - $sqlcode .= "UPDATE contacts SET cp_title = \'$main::form->{\"cp_name_$i\"}\' WHERE cp_id = $main::form->{\"cp_id_$i\"};\n"; - }; + for (my $i = 1; $i <= $::form->{number_of_title_entries}; $i++ ) { + next unless $::form->{"cp_id_title_$i"} and $::form->{"cp_id_$i"}; + $sqlcode .= "UPDATE contacts SET cp_title = \'$::form->{\"cp_name_$i\"}\' WHERE cp_id = $::form->{\"cp_id_$i\"};\n"; + } $sqlcode .= "ALTER TABLE contacts DROP COLUMN cp_greeting;"; # insert chosen default values - $sqlcode .= "INSERT INTO generic_translations (translation_type, translation) VALUES ('greetings::male','$main::form->{default_male}');"; - $sqlcode .= "INSERT INTO generic_translations (translation_type, translation) VALUES ('greetings::female','$main::form->{default_female}');"; + $sqlcode .= "INSERT INTO generic_translations (translation_type, translation) VALUES ('greetings::male','$::form->{default_male}');"; + $sqlcode .= "INSERT INTO generic_translations (translation_type, translation) VALUES ('greetings::female','$::form->{default_female}');"; my $query = $sqlcode; - do_query($query); -}; - - + $self->db_query($query); +} sub set_default_greetings { - # add html input boxes to template so user can specify default greetings - - my $default_male = "Herr"; - my $default_female = "Frau"; - - my $default_greeting_text_male = "
"; - my $default_greeting_text_female = "
"; - $main::form->{default_greeting_text_male} = $default_greeting_text_male; - $main::form->{default_greeting_text_female} = $default_greeting_text_female; -}; + my ($self) = @_; + # add html input boxes to template so user can specify default greetings -return do_update(); + my $default_male = "Herr"; + my $default_female = "Frau"; + my $default_greeting_text_male = "
"; + my $default_greeting_text_female = "
"; + $::form->{default_greeting_text_male} = $default_greeting_text_male; + $::form->{default_greeting_text_female} = $default_greeting_text_female; +} +1;