From 7349649bae8d80eabfd253592d59f8455f9ef6b0 Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Mon, 25 Jul 2016 15:52:49 +0200 Subject: [PATCH] Kunde: Datenbanktabelle "klass" nach "pricegroup_id" migriert MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit und einen Fremdschlüssel gesetzt. --- SL/CT.pm | 2 +- SL/Common.pm | 2 +- SL/Controller/CsvImport/CustomerVendor.pm | 2 +- SL/DB/MetaSetup/Customer.pm | 7 ++++++- SL/IC.pm | 3 +-- SL/IS.pm | 2 +- SL/PriceSource/Pricegroup.pm | 4 ++-- bin/mozilla/oe.pl | 2 +- doc/UPGRADE | 5 +++++ locale/de/all | 1 - locale/en/all | 1 - ...omer_klass_rename_to_pricegroup_id_and_foreign_key.sql | 8 ++++++++ templates/webpages/customer_vendor/tabs/billing.html | 2 +- templates/webpages/do/form_header.html | 2 +- templates/webpages/ir/form_header.html | 1 - templates/webpages/is/form_header.html | 2 +- 16 files changed, 30 insertions(+), 16 deletions(-) create mode 100644 sql/Pg-upgrade2/customer_klass_rename_to_pricegroup_id_and_foreign_key.sql diff --git a/SL/CT.pm b/SL/CT.pm index 416ed6871..d8497d008 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -251,7 +251,7 @@ sub search { } my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : ''; - my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.klass = pg.id) | : ''; + my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : ''; my $query = qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | . qq| pt.description as payment | . diff --git a/SL/Common.pm b/SL/Common.pm index f1aa53c2d..2027a2676 100644 --- a/SL/Common.pm +++ b/SL/Common.pm @@ -350,7 +350,7 @@ sub get_vc_details { $form->{CONTACTS} = selectall_hashref_query($form, $dbh, $query, $vc_id); # Only show default pricegroup for customer, not vendor, which is why this is outside the main query - ($form->{pricegroup}) = selectrow_query($form, $dbh, qq|SELECT pricegroup FROM pricegroup WHERE id = ?|, $form->{klass}); + ($form->{pricegroup}) = selectrow_query($form, $dbh, qq|SELECT pricegroup FROM pricegroup WHERE id = ?|, $form->{pricegroup_id}); $dbh->disconnect(); diff --git a/SL/Controller/CsvImport/CustomerVendor.pm b/SL/Controller/CsvImport/CustomerVendor.pm index d1e026d42..0042533a0 100644 --- a/SL/Controller/CsvImport/CustomerVendor.pm +++ b/SL/Controller/CsvImport/CustomerVendor.pm @@ -300,7 +300,7 @@ sub setup_displayable_columns { { name => 'greeting', description => $::locale->text('Greeting') }, { name => 'homepage', description => $::locale->text('Homepage') }, { name => 'iban', description => $::locale->text('IBAN') }, - { name => 'klass', description => $::locale->text('Preisklasse') }, + { name => 'pricegroup_id', description => $::locale->text('Price group (database ID)') }, { name => 'language_id', description => $::locale->text('Language (database ID)') }, { name => 'language', description => $::locale->text('Language (name)') }, { name => 'name', description => $::locale->text('Name') }, diff --git a/SL/DB/MetaSetup/Customer.pm b/SL/DB/MetaSetup/Customer.pm index aca587470..2b241ab0d 100644 --- a/SL/DB/MetaSetup/Customer.pm +++ b/SL/DB/MetaSetup/Customer.pm @@ -38,7 +38,6 @@ __PACKAGE__->meta->columns( iban => { type => 'text' }, id => { type => 'integer', not_null => 1, sequence => 'id' }, itime => { type => 'timestamp', default => 'now()' }, - klass => { type => 'integer', default => '0' }, language => { type => 'text' }, language_id => { type => 'integer' }, mandate_date_of_signature => { type => 'date' }, @@ -49,6 +48,7 @@ __PACKAGE__->meta->columns( obsolete => { type => 'boolean', default => 'false' }, payment_id => { type => 'integer' }, phone => { type => 'text' }, + pricegroup_id => { type => 'integer' }, salesman_id => { type => 'integer' }, street => { type => 'text' }, taxincluded => { type => 'boolean' }, @@ -91,6 +91,11 @@ __PACKAGE__->meta->foreign_keys( key_columns => { payment_id => 'id' }, }, + pricegroup => { + class => 'SL::DB::Pricegroup', + key_columns => { pricegroup_id => 'id' }, + }, + taxzone => { class => 'SL::DB::TaxZone', key_columns => { taxzone_id => 'id' }, diff --git a/SL/IC.pm b/SL/IC.pm index 0b5db7e84..81b3cce94 100644 --- a/SL/IC.pm +++ b/SL/IC.pm @@ -193,8 +193,7 @@ sub get_pricegroups { my $i = 1; foreach my $pg (@{ $pricegroups }) { - $form->{"klass_$i"} = "$pg->{id}"; - $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2); + $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2); $form->{"pricegroup_id_$i"} = "$pg->{id}"; $form->{"pricegroup_$i"} = "$pg->{pricegroup}"; $i++; diff --git a/SL/IS.pm b/SL/IS.pm index 57b1f9eb4..d7ba6b563 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -2112,7 +2112,7 @@ sub get_customer { c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id, c.street, c.zipcode, c.city, c.country, - c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr, + c.notes AS intnotes, c.pricegroup_id as customer_pricegroup_id, c.taxzone_id, c.salesman_id, cu.name AS curr, c.taxincluded_checked, c.direct_debit, b.discount AS tradediscount, b.description AS business FROM customer c diff --git a/SL/PriceSource/Pricegroup.pm b/SL/PriceSource/Pricegroup.pm index 6408300b1..20b49c284 100644 --- a/SL/PriceSource/Pricegroup.pm +++ b/SL/PriceSource/Pricegroup.pm @@ -54,9 +54,9 @@ sub best_price { my @prices = $self->available_prices; my $customer = $self->record->customer; - return () if !$customer || !$customer->klass; + return () if !$customer || !$customer->pricegroup_id; - my $best_price = first { $_->spec == $customer->klass } @prices; + my $best_price = first { $_->spec == $customer->pricegroup_id } @prices; return $best_price || (); } diff --git a/bin/mozilla/oe.pl b/bin/mozilla/oe.pl index 4dbb79ba0..a14bbed07 100644 --- a/bin/mozilla/oe.pl +++ b/bin/mozilla/oe.pl @@ -425,7 +425,7 @@ sub form_header { # business $TMPL_VAR{business_label} = ($form->{vc} eq "customer" ? $locale->text('Customer type') : $locale->text('Vendor type')); - push @custom_hiddens, "customer_klass" if $form->{vc} eq 'customer'; + push @custom_hiddens, "customer_pricegroup_id" if $form->{vc} eq 'customer'; my $credittext = $locale->text('Credit Limit exceeded!!!'); diff --git a/doc/UPGRADE b/doc/UPGRADE index e7881c9c2..d3c84ef17 100644 --- a/doc/UPGRADE +++ b/doc/UPGRADE @@ -10,6 +10,11 @@ Upgrade auf v3.4.x Unstable * In der Rechte-Tabelle auth.master_rights wurden alle Positionswerte mit 100 multipliziert, um Lücken für neue Rechte zu schaffen. +* In der Tabelle "customer" wurde die Spalte "klass" nach "pricegroup_id" + migriert. Bei Kunden ohne Preisgruppe ist der Datenbankwert jetzt NULL statt + "0". Falls Kunden per CSV-Import importiert werden muß dieses Feld in der + CSV-Datei ebenfalls umbenannt werden. + * Es wird jetzt Postgres 9.1 vorausgesetzt um GIN Indizes zu verwenden. Wenn das auf dem Zielsystem absolut nicht möglich ist, muss das Upgradescript sql/Pg-Upgrade2/trigram_indices.sql deaktiviert oder entfernt werden. diff --git a/locale/de/all b/locale/de/all index b0c44a806..2bb37dd0d 100755 --- a/locale/de/all +++ b/locale/de/all @@ -2080,7 +2080,6 @@ $self->{texts} = { 'Preferences saved!' => 'Einstellungen gespeichert!', 'Prefix for the new bins\' names' => 'Namenspräfix für die neuen Lagerplätze', 'Preis' => 'Preis', - 'Preisklasse' => 'Preisgruppe', 'Prepare bank collection via SEPA XML' => 'Einzug via SEPA XML vorbereiten', 'Prepare bank transfer via SEPA XML' => 'Überweisung via SEPA XML vorbereiten', 'Prepayment' => 'Vorauszahlung', diff --git a/locale/en/all b/locale/en/all index fb2a22d8b..1b0f2dd35 100644 --- a/locale/en/all +++ b/locale/en/all @@ -2079,7 +2079,6 @@ $self->{texts} = { 'Preferences saved!' => '', 'Prefix for the new bins\' names' => '', 'Preis' => '', - 'Preisklasse' => '', 'Prepare bank collection via SEPA XML' => '', 'Prepare bank transfer via SEPA XML' => '', 'Prepayment' => '', diff --git a/sql/Pg-upgrade2/customer_klass_rename_to_pricegroup_id_and_foreign_key.sql b/sql/Pg-upgrade2/customer_klass_rename_to_pricegroup_id_and_foreign_key.sql new file mode 100644 index 000000000..b6b789d0a --- /dev/null +++ b/sql/Pg-upgrade2/customer_klass_rename_to_pricegroup_id_and_foreign_key.sql @@ -0,0 +1,8 @@ +-- @tag: customer_klass_rename_to_pricegroup_id_and_foreign_key +-- @description: klass nach pricegroup_id umbenannt +-- @depends: release_3_4_1 +-- @ignore: 0 + +ALTER TABLE customer ADD COLUMN pricegroup_id INTEGER REFERENCES pricegroup (id); +UPDATE customer SET pricegroup_id = klass WHERE klass != 0; +ALTER TABLE customer DROP COLUMN klass; diff --git a/templates/webpages/customer_vendor/tabs/billing.html b/templates/webpages/customer_vendor/tabs/billing.html index c917f639d..cec118b97 100644 --- a/templates/webpages/customer_vendor/tabs/billing.html +++ b/templates/webpages/customer_vendor/tabs/billing.html @@ -317,7 +317,7 @@ [% 'Price group' | $T8 %] - [% L.select_tag('cv.klass', SELF.all_pricegroups, default = SELF.cv.klass, value_key = 'id', title_key = 'pricegroup', with_empty = 1) %] + [% L.select_tag('cv.pricegroup_id', SELF.all_pricegroups, default = SELF.cv.pricegroup_id, value_key = 'id', title_key = 'pricegroup', with_empty = 1) %] [% END %] diff --git a/templates/webpages/do/form_header.html b/templates/webpages/do/form_header.html index 6ae87d368..4bfefaabd 100644 --- a/templates/webpages/do/form_header.html +++ b/templates/webpages/do/form_header.html @@ -73,7 +73,7 @@ - + diff --git a/templates/webpages/ir/form_header.html b/templates/webpages/ir/form_header.html index 6975d0da2..75ffde5d4 100644 --- a/templates/webpages/ir/form_header.html +++ b/templates/webpages/ir/form_header.html @@ -63,7 +63,6 @@ onChange = "document.getElementById('update_button').click();" -%] - diff --git a/templates/webpages/is/form_header.html b/templates/webpages/is/form_header.html index f412fac1f..ff9353a42 100644 --- a/templates/webpages/is/form_header.html +++ b/templates/webpages/is/form_header.html @@ -65,7 +65,7 @@ allow_textbox = 1, onChange = "document.getElementById('update_button').click();" -%] - + -- 2.20.1