From b632cee8434442efd8ae3962126c34123172daac Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Mon, 14 Jan 2013 15:59:28 +0100 Subject: [PATCH] =?utf8?q?InvoiceItem,=20OrderItem,=20DeliveryOrderItem:?= =?utf8?q?=20diverse=20Fremdschl=C3=BCssel=20eingef=C3=BChrt?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/DB/DeliveryOrderItem.pm | 9 ++++++- SL/DB/InvoiceItem.pm | 15 ++++------- SL/DB/MetaSetup/DeliveryOrderItem.pm | 5 ++++ SL/DB/MetaSetup/InvoiceItem.pm | 15 +++++++++++ SL/DB/MetaSetup/OrderItem.pm | 20 +++++++++++++++ SL/DB/OrderItem.pm | 25 ++++++++----------- SL/DO.pm | 9 ++++++- SL/IS.pm | 8 ++++-- SL/OE.pm | 8 ++++-- ...ivery_order_items_invoice_foreign_keys.sql | 22 ++++++++++++++++ 10 files changed, 105 insertions(+), 31 deletions(-) create mode 100644 sql/Pg-upgrade2/orderitems_delivery_order_items_invoice_foreign_keys.sql diff --git a/SL/DB/DeliveryOrderItem.pm b/SL/DB/DeliveryOrderItem.pm index 873c0a422..f35928850 100644 --- a/SL/DB/DeliveryOrderItem.pm +++ b/SL/DB/DeliveryOrderItem.pm @@ -12,6 +12,13 @@ use SL::DB::Helper::CustomVariables ( ); __PACKAGE__->meta->make_manager_class; +__PACKAGE__->meta->add_relationship( + unit_obj => { + type => 'one to one', + class => 'SL::DB::Unit', + column_map => { unit => 'name' }, + }, +); __PACKAGE__->meta->initialize; @@ -19,7 +26,7 @@ __PACKAGE__->meta->initialize; sub part { # canonial alias for parts. - return shift->parts; + goto &parts; } 1; diff --git a/SL/DB/InvoiceItem.pm b/SL/DB/InvoiceItem.pm index 44c121e94..016f94575 100644 --- a/SL/DB/InvoiceItem.pm +++ b/SL/DB/InvoiceItem.pm @@ -12,16 +12,6 @@ use SL::DB::Helper::CustomVariables ( ); __PACKAGE__->meta->add_relationship( - part => { - type => 'one to one', - class => 'SL::DB::Part', - column_map => { parts_id => 'id' }, - }, - price_factor_obj => { - type => 'one to one', - class => 'SL::DB::PriceFactor', - column_map => { price_factor_id => 'id' }, - }, unit_obj => { type => 'one to one', class => 'SL::DB::Unit', @@ -34,4 +24,9 @@ __PACKAGE__->meta->make_manager_class; __PACKAGE__->meta->initialize; +sub part { + # canonial alias for parts. + goto &parts; +} + 1; diff --git a/SL/DB/MetaSetup/DeliveryOrderItem.pm b/SL/DB/MetaSetup/DeliveryOrderItem.pm index 3065ebcf8..1e9a52be2 100644 --- a/SL/DB/MetaSetup/DeliveryOrderItem.pm +++ b/SL/DB/MetaSetup/DeliveryOrderItem.pm @@ -55,6 +55,11 @@ __PACKAGE__->meta->setup( key_columns => { price_factor_id => 'id' }, }, + pricegroup => { + class => 'SL::DB::Pricegroup', + key_columns => { pricegroup_id => 'id' }, + }, + project => { class => 'SL::DB::Project', key_columns => { project_id => 'id' }, diff --git a/SL/DB/MetaSetup/InvoiceItem.pm b/SL/DB/MetaSetup/InvoiceItem.pm index 9a851e057..470c4c4e6 100644 --- a/SL/DB/MetaSetup/InvoiceItem.pm +++ b/SL/DB/MetaSetup/InvoiceItem.pm @@ -50,6 +50,21 @@ __PACKAGE__->meta->setup( class => 'SL::DB::Part', key_columns => { parts_id => 'id' }, }, + + price_factor_obj => { + class => 'SL::DB::PriceFactor', + key_columns => { price_factor_id => 'id' }, + }, + + pricegroup => { + class => 'SL::DB::Pricegroup', + key_columns => { pricegroup_id => 'id' }, + }, + + project => { + class => 'SL::DB::Project', + key_columns => { project_id => 'id' }, + }, ], ); diff --git a/SL/DB/MetaSetup/OrderItem.pm b/SL/DB/MetaSetup/OrderItem.pm index 3f048c34f..732d4c92e 100644 --- a/SL/DB/MetaSetup/OrderItem.pm +++ b/SL/DB/MetaSetup/OrderItem.pm @@ -48,6 +48,26 @@ __PACKAGE__->meta->setup( class => 'SL::DB::Part', key_columns => { parts_id => 'id' }, }, + + price_factor_obj => { + class => 'SL::DB::PriceFactor', + key_columns => { price_factor_id => 'id' }, + }, + + pricegroup => { + class => 'SL::DB::Pricegroup', + key_columns => { pricegroup_id => 'id' }, + }, + + project => { + class => 'SL::DB::Project', + key_columns => { project_id => 'id' }, + }, + + trans => { + class => 'SL::DB::Order', + key_columns => { trans_id => 'id' }, + }, ], ); diff --git a/SL/DB/OrderItem.pm b/SL/DB/OrderItem.pm index 3b868d350..7372f81a8 100644 --- a/SL/DB/OrderItem.pm +++ b/SL/DB/OrderItem.pm @@ -16,26 +16,11 @@ use SL::DB::Helper::CustomVariables ( ); __PACKAGE__->meta->add_relationship( - part => { - type => 'one to one', - class => 'SL::DB::Part', - column_map => { parts_id => 'id' }, - }, - price_factor_obj => { - type => 'one to one', - class => 'SL::DB::PriceFactor', - column_map => { price_factor_id => 'id' }, - }, unit_obj => { type => 'one to one', class => 'SL::DB::Unit', column_map => { unit => 'name' }, }, - order => { - type => 'one to one', - class => 'SL::DB::Order', - column_map => { trans_id => 'id' }, - }, ); __PACKAGE__->meta->initialize; @@ -54,4 +39,14 @@ sub shipped_qty { return sum(map { AM->convert_unit($_->unit => $self->unit) * $_->qty } @doi); } +sub part { + # canonial alias for parts. + goto &parts; +} + +sub order { + # canonial alias for trans. + goto &trans; +} + 1; diff --git a/SL/DO.pm b/SL/DO.pm index 4fbe8020a..dbb29a1e3 100644 --- a/SL/DO.pm +++ b/SL/DO.pm @@ -293,6 +293,13 @@ sub save { do_statement($form, $h_item_id, $q_item_id); my ($item_id) = $h_item_id->fetchrow_array(); + # Get pricegroup_id and save it. Unfortunately the interface + # also uses ID "0" for signalling that none is selected, but "0" + # must not be stored in the database. Therefore we cannot simply + # use conv_i(). + my $pricegroup_id = $form->{"pricegroup_id_$i"} * 1; + $pricegroup_id = undef if !$pricegroup_id; + # save detail record in delivery_order_items table @values = (conv_i($item_id), conv_i($form->{id}), conv_i($form->{"id_$i"}), $form->{"description_$i"}, $form->{"longdescription_$i"}, @@ -305,7 +312,7 @@ sub save { $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}), - conv_i($form->{"pricegroup_id_$i"})); + $pricegroup_id); do_statement($form, $h_item, $q_item, @values); my $stock_info = DO->unpack_stock_information('packed' => $form->{"stock_${in_out}_$i"}); diff --git a/SL/IS.pm b/SL/IS.pm index ee3c3bc7e..28c4a6605 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -707,9 +707,13 @@ sub post_invoice { } } - # get pricegroup_id and save it + # Get pricegroup_id and save it. Unfortunately the interface + # also uses ID "0" for signalling that none is selected, but "0" + # must not be stored in the database. Therefore we cannot simply + # use conv_i(). ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"}); $pricegroup_id *= 1; + $pricegroup_id = undef if !$pricegroup_id; my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|); @@ -729,7 +733,7 @@ sub post_invoice { $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated, 'f', $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}), - $form->{"serialnumber_$i"}, conv_i($pricegroup_id), + $form->{"serialnumber_$i"}, $pricegroup_id, $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}), $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f', $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"}, diff --git a/SL/OE.pm b/SL/OE.pm index a865e3d75..93848b9fc 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -419,9 +419,13 @@ sub save { $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef; - # get pricegroup_id and save ist + # Get pricegroup_id and save it. Unfortunately the interface + # also uses ID "0" for signalling that none is selected, but "0" + # must not be stored in the database. Therefore we cannot simply + # use conv_i(). ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"}); $pricegroup_id *= 1; + $pricegroup_id = undef if !$pricegroup_id; # save detail record in orderitems table my $orderitems_id = $form->{"orderitems_id_$i"}; @@ -441,7 +445,7 @@ sub save { $form->{"qty_$i"}, $baseqty, $fxsellprice, $form->{"discount_$i"}, $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}), - $form->{"serialnumber_$i"}, $form->{"ship_$i"}, conv_i($pricegroup_id), + $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id, $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}), $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f', $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"}, diff --git a/sql/Pg-upgrade2/orderitems_delivery_order_items_invoice_foreign_keys.sql b/sql/Pg-upgrade2/orderitems_delivery_order_items_invoice_foreign_keys.sql new file mode 100644 index 000000000..0de00c5d3 --- /dev/null +++ b/sql/Pg-upgrade2/orderitems_delivery_order_items_invoice_foreign_keys.sql @@ -0,0 +1,22 @@ +-- @tag: orderitems_delivery_order_items_invoice_foreign_keys +-- @description: Fremdschlüssel für Tabellen oderitems, delivery_order_items, invoice +-- @depends: release_3_0_0 +-- @charset: utf-8 +UPDATE orderitems SET pricegroup_id = NULL WHERE pricegroup_id = 0; +UPDATE delivery_order_items SET pricegroup_id = NULL WHERE pricegroup_id = 0; +UPDATE invoice SET pricegroup_id = NULL WHERE pricegroup_id = 0; + +UPDATE orderitems SET project_id = NULL WHERE project_id NOT IN (SELECT id FROM project); +UPDATE delivery_order_items SET project_id = NULL WHERE project_id NOT IN (SELECT id FROM project); +UPDATE invoice SET project_id = NULL WHERE project_id NOT IN (SELECT id FROM project); + +ALTER TABLE orderitems ADD FOREIGN KEY (trans_id) REFERENCES oe (id); +ALTER TABLE orderitems ADD FOREIGN KEY (project_id) REFERENCES project (id); +ALTER TABLE orderitems ADD FOREIGN KEY (pricegroup_id) REFERENCES pricegroup (id); +ALTER TABLE orderitems ADD FOREIGN KEY (price_factor_id) REFERENCES price_factors (id); + +ALTER TABLE delivery_order_items ADD FOREIGN KEY (pricegroup_id) REFERENCES pricegroup (id); + +ALTER TABLE invoice ADD FOREIGN KEY (project_id) REFERENCES project (id); +ALTER TABLE invoice ADD FOREIGN KEY (pricegroup_id) REFERENCES pricegroup (id); +ALTER TABLE invoice ADD FOREIGN KEY (price_factor_id) REFERENCES price_factors (id); -- 2.20.1