InvoiceItem, OrderItem, DeliveryOrderItem: diverse Fremdschlüssel eingeführt

diff --git a/SL/DB/DeliveryOrderItem.pm b/SL/DB/DeliveryOrderItem.pm
index 873c0a422e194974937216ed5bc797fc53d851d5..f35928850896804d0ee0ade28c51c63325957f50 100644 (file)
--- 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->make_manager_class;

+__PACKAGE__->meta->add_relationship(
+  unit_obj       => {
+    type         => 'one to one',
+    class        => 'SL::DB::Unit',
+    column_map   => { unit => 'name' },
+  },
+);

 
 __PACKAGE__->meta->initialize;
 
 
 __PACKAGE__->meta->initialize;
 


@@ -19,7 +26,7 @@ __PACKAGE__->meta->initialize;
 
 sub part {
   # canonial alias for parts.
 
 sub part {
   # canonial alias for parts.

-  return shift->parts;
+  goto &parts;

 }
 
 1;
 }
 
 1;

diff --git a/SL/DB/InvoiceItem.pm b/SL/DB/InvoiceItem.pm
index 44c121e942c145630ae5a61ae9cbb6ea4acc4f50..016f94575b194db3890e511bdef6fea15a4796f7 100644 (file)
--- a/SL/DB/InvoiceItem.pm
+++ b/SL/DB/InvoiceItem.pm
@@ -12,16 +12,6 @@ use SL::DB::Helper::CustomVariables (
 );
 
 __PACKAGE__->meta->add_relationship(
 );
 
 __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',
   unit_obj       => {
     type         => 'one to one',
     class        => 'SL::DB::Unit',


@@ -34,4 +24,9 @@ __PACKAGE__->meta->make_manager_class;
 
 __PACKAGE__->meta->initialize;
 
 
 __PACKAGE__->meta->initialize;
 

+sub part {
+  # canonial alias for parts.
+  goto &parts;
+}
+

 1;
 1;

diff --git a/SL/DB/MetaSetup/DeliveryOrderItem.pm b/SL/DB/MetaSetup/DeliveryOrderItem.pm
index 3065ebcf8bd9805ff8e700e49ec040502bd8cdb6..1e9a52be24e5378a9454345749b4b9d3c706e6b1 100644 (file)
--- 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' },
     },
 
       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' },
     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 9a851e0570da6f55414616ab37b9b221112331ff..470c4c4e677ae3ad7d4332ed1f3fcbd6b38511d8 100644 (file)
--- 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' },
     },
       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 3f048c34f2d1a0a5b2f194ef11b1f58c34713eb8..732d4c92e06e03e891cf38c349567cfb37b54f50 100644 (file)
--- 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' },
     },
       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 3b868d350991bee5fb9c79b55fb6e8236ec675c6..7372f81a8d81642d4991ce5ff8773010ecc21861 100644 (file)
--- a/SL/DB/OrderItem.pm
+++ b/SL/DB/OrderItem.pm
@@ -16,26 +16,11 @@ use SL::DB::Helper::CustomVariables (
 );
 
 __PACKAGE__->meta->add_relationship(
 );
 
 __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' },
   },
   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;
 );
 
 __PACKAGE__->meta->initialize;


@@ -54,4 +39,14 @@ sub shipped_qty {
   return sum(map { AM->convert_unit($_->unit => $self->unit) * $_->qty } @doi);
 }
 
   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;
 1;

diff --git a/SL/DO.pm b/SL/DO.pm
index 4fbe8020a1adf96bf561e633674ff50cfc13cdb9..dbb29a1e3e5ddac12e23e142b60c6ec9b4a97352 100644 (file)
--- 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();
 
     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"},
     # 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"}),
                $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"});
     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 ee3c3bc7e7ef9af301c22ae72cbe4f87104b2a87..28c4a660576e090325bc79d3a69fbbce6ab6f651 100644 (file)
--- 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;
       ($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')|);
 
 
       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->{"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"},
                  $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 a865e3d759c23da3948482fe6624b1da003e78a4..93848b9fc197cf7a65652e729b9287733166b6be 100644 (file)
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -419,9 +419,13 @@ sub save {
 
       $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
 
 
       $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;
       ($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"};
 
       # 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->{"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"},
            $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 (file)
index 0000000..0de00c5
--- /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);