From 942291e4ee3bacc216839d2989a63123f87b6bcd Mon Sep 17 00:00:00 2001
From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= <bernd@kivitendo-premium.de>
Date: Thu, 22 Jan 2015 16:36:36 +0100
Subject: [PATCH] =?utf8?q?Item-Positionen=20f=C3=BCr=20Rechnungen=20in=20D?=
 =?utf8?q?B:=20DB-Upgrade-Skript;=20Rose?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Positionen der Artikelzeilen für Rechnungen in der Datenbank speichern.
---
 SL/DB/InvoiceItem.pm                 |  3 ++
 SL/DB/MetaSetup/InvoiceItem.pm       |  1 +
 sql/Pg-upgrade2/invoice_positions.pl | 46 ++++++++++++++++++++++++++++
 3 files changed, 50 insertions(+)
 create mode 100644 sql/Pg-upgrade2/invoice_positions.pl

diff --git a/SL/DB/InvoiceItem.pm b/SL/DB/InvoiceItem.pm
index 4374a6cdc..1e0e4e749 100644
--- a/SL/DB/InvoiceItem.pm
+++ b/SL/DB/InvoiceItem.pm
@@ -3,6 +3,7 @@ package SL::DB::InvoiceItem;
 use strict;
 
 use SL::DB::MetaSetup::InvoiceItem;
+use SL::DB::Helper::ActsAsList;
 use SL::DB::Helper::CustomVariables (
   sub_module  => 'invoice',
   cvars_alias => 1,
@@ -16,6 +17,8 @@ use SL::DB::Helper::CustomVariables (
 
 __PACKAGE__->meta->make_manager_class;
 
+__PACKAGE__->configure_acts_as_list(group_by => [qw(trans_id)]);
+
 __PACKAGE__->meta->add_relationships(
   invoice          => {
     type           => 'one to one',
diff --git a/SL/DB/MetaSetup/InvoiceItem.pm b/SL/DB/MetaSetup/InvoiceItem.pm
index 68ed569e4..af759ab17 100644
--- a/SL/DB/MetaSetup/InvoiceItem.pm
+++ b/SL/DB/MetaSetup/InvoiceItem.pm
@@ -30,6 +30,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/sql/Pg-upgrade2/invoice_positions.pl b/sql/Pg-upgrade2/invoice_positions.pl
new file mode 100644
index 000000000..854c75fbb
--- /dev/null
+++ b/sql/Pg-upgrade2/invoice_positions.pl
@@ -0,0 +1,46 @@
+# @tag: invoice_positions
+# @description: Spalte für Positionen der Einträge in Rechnungen
+# @depends: release_3_1_0
+# @encoding: utf-8
+package SL::DBUpgrade2::invoice_positions;
+
+use strict;
+use utf8;
+
+use parent qw(SL::DBUpgrade2::Base);
+
+sub run {
+  my ($self) = @_;
+
+  my $query = qq|ALTER TABLE invoice ADD position INTEGER|;
+  $self->db_query($query);
+
+
+  $query = qq|SELECT * FROM invoice ORDER BY trans_id, 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 invoice
+  my $last_invoice_id;
+  my $position;
+  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+    if ($ref->{trans_id} != $last_invoice_id) {
+      $position = 1;
+    } else {
+      $position++;
+    }
+    $last_invoice_id = $ref->{trans_id};
+
+    $query = qq|UPDATE invoice SET position = ? WHERE id = ?|;
+    $self->db_query($query, bind => [ $position, $ref->{id} ]);
+  }
+  $sth->finish;
+
+  $query = qq|ALTER TABLE invoice ALTER COLUMN position SET NOT NULL|;
+  $self->db_query($query);
+
+  return 1;
+}
+
+1;
-- 
2.20.1