From 3954d14b8a2d394b95bb8ecf8b0ed92c030fbd1d Mon Sep 17 00:00:00 2001
From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= <bernd@kivitendo-premium.de>
Date: Fri, 9 Jan 2015 19:05:52 +0100
Subject: [PATCH] Item-Positionen in DB: DB-Upgrade-Skript; Rose
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Positionen der Artikelzeilen für Angebote/Aufträge und Lieferscheine mit
in der Datenbank speichern.
---
 SL/DB/DeliveryOrderItem.pm                    |  3 +
 SL/DB/MetaSetup/DeliveryOrderItem.pm          |  1 +
 SL/DB/MetaSetup/OrderItem.pm                  |  1 +
 SL/DB/OrderItem.pm                            |  3 +
 ...deritems_delivery_order_items_positions.pl | 56 +++++++++++++++++++
 5 files changed, 64 insertions(+)
 create mode 100644 sql/Pg-upgrade2/orderitems_delivery_order_items_positions.pl

diff --git a/SL/DB/DeliveryOrderItem.pm b/SL/DB/DeliveryOrderItem.pm
index 638082ccf..81451a891 100644
--- a/SL/DB/DeliveryOrderItem.pm
+++ b/SL/DB/DeliveryOrderItem.pm
@@ -3,6 +3,7 @@ package SL::DB::DeliveryOrderItem;
 use strict;
 
 use SL::DB::MetaSetup::DeliveryOrderItem;
+use SL::DB::Helper::ActsAsList;
 use SL::DB::Helper::CustomVariables (
   sub_module  => 'delivery_order_items',
   cvars_alias => 1,
@@ -18,6 +19,8 @@ __PACKAGE__->meta->make_manager_class;
 
 __PACKAGE__->meta->initialize;
 
+__PACKAGE__->configure_acts_as_list(group_by => [qw(delivery_order_id)]);
+
 # methods
 
 1;
diff --git a/SL/DB/MetaSetup/DeliveryOrderItem.pm b/SL/DB/MetaSetup/DeliveryOrderItem.pm
index c9b90e399..aabf2b5f0 100644
--- a/SL/DB/MetaSetup/DeliveryOrderItem.pm
+++ b/SL/DB/MetaSetup/DeliveryOrderItem.pm
@@ -24,6 +24,7 @@ __PACKAGE__->meta->columns(
   mtime                  => { type => 'timestamp' },
   ordnumber              => { type => 'text' },
   parts_id               => { type => 'integer', not_null => 1 },
+  position               => { type => 'integer', not_null => 1 },
   price_factor           => { type => 'numeric', default => 1, precision => 15, scale => 5 },
   price_factor_id        => { type => 'integer' },
   pricegroup_id          => { type => 'integer' },
diff --git a/SL/DB/MetaSetup/OrderItem.pm b/SL/DB/MetaSetup/OrderItem.pm
index 7e4b2c916..53ded975e 100644
--- a/SL/DB/MetaSetup/OrderItem.pm
+++ b/SL/DB/MetaSetup/OrderItem.pm
@@ -25,6 +25,7 @@ __PACKAGE__->meta->columns(
   mtime                  => { type => 'timestamp' },
   ordnumber              => { type => 'text' },
   parts_id               => { type => 'integer' },
+  position               => { type => 'integer', not_null => 1 },
   price_factor           => { type => 'numeric', default => 1, precision => 15, scale => 5 },
   price_factor_id        => { type => 'integer' },
   pricegroup_id          => { type => 'integer' },
diff --git a/SL/DB/OrderItem.pm b/SL/DB/OrderItem.pm
index 81e6cd39e..a6c8ac8f9 100644
--- a/SL/DB/OrderItem.pm
+++ b/SL/DB/OrderItem.pm
@@ -7,6 +7,7 @@ use List::Util qw(sum);
 use SL::DB::MetaSetup::OrderItem;
 use SL::DB::Manager::OrderItem;
 use SL::DB::DeliveryOrderItemsStock;
+use SL::DB::Helper::ActsAsList;
 use SL::DB::Helper::CustomVariables (
   sub_module  => 'orderitems',
   cvars_alias => 1,
@@ -20,6 +21,8 @@ use SL::DB::Helper::CustomVariables (
 
 __PACKAGE__->meta->initialize;
 
+__PACKAGE__->configure_acts_as_list(group_by => [qw(trans_id)]);
+
 sub is_price_update_available {
   my $self = shift;
   return $self->origprice > $self->part->sellprice;
diff --git a/sql/Pg-upgrade2/orderitems_delivery_order_items_positions.pl b/sql/Pg-upgrade2/orderitems_delivery_order_items_positions.pl
new file mode 100644
index 000000000..c3adfa12a
--- /dev/null
+++ b/sql/Pg-upgrade2/orderitems_delivery_order_items_positions.pl
@@ -0,0 +1,56 @@
+# @tag: orderitems_delivery_order_items_positions
+# @description: Spalte für Positionen der Einträge in Angeboten/Auftträgen und Lieferscheinen.
+# @depends: release_3_1_0
+# @encoding: utf-8
+package SL::DBUpgrade2::orderitems_delivery_order_items_positions;
+
+use strict;
+use utf8;
+
+use parent qw(SL::DBUpgrade2::Base);
+
+sub run {
+  my ($self) = @_;
+
+  my %order_id_cols = (
+    orderitems           => 'trans_id',
+    delivery_order_items => 'delivery_order_id',
+  );
+
+  foreach my $table ( keys %order_id_cols ) {
+
+    my $query = qq|ALTER TABLE $table ADD position INTEGER|;
+    $self->db_query($query);
+
+
+    my $order_id_col = $order_id_cols{ $table };
+    $query = qq|SELECT * FROM $table ORDER BY $order_id_col, id|;
+
+    my $sth = $self->dbh->prepare($query);
+    $sth->execute || $::form->dberror($query);
+
+    # set new postition field in order of ids, starting by one for each order
+    my $last_order_id;
+    my $position;
+    while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+      if ($ref->{ $order_id_col } != $last_order_id) {
+        $position = 1;
+      } else {
+        $position++;
+      }
+      $last_order_id = $ref->{ $order_id_col };
+
+      $query = qq|UPDATE $table SET position = ? WHERE id = ?|;
+      $self->db_query($query, bind => [ $position, $ref->{id} ]);
+    }
+    $sth->finish;
+
+
+    $query = qq|ALTER TABLE $table ALTER COLUMN position SET NOT NULL|;
+    $self->db_query($query);
+  }
+
+  return 1;
+}
+
+1;
-- 
2.20.1