From ca5de5712845a874f8abe976d92aef945d513ca7 Mon Sep 17 00:00:00 2001
From: Moritz Bunkus <m.bunkus@linet-services.de>
Date: Wed, 21 Feb 2007 07:49:28 +0000
Subject: [PATCH] =?utf8?q?Speichern=20der=20Historie=20in=20einer=20eigene?=
 =?utf8?q?n=20Tabelle.=20Auch=20ein=20Fix=20f=C3=BCr=20Bugzilla-ID=20558.?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 SL/Form.pm                      | 31 ++++++++++++++++++-------------
 sql/Pg-upgrade2/history_erp.sql | 18 ++++++++++++++++++
 2 files changed, 36 insertions(+), 13 deletions(-)
 create mode 100644 sql/Pg-upgrade2/history_erp.sql

diff --git a/SL/Form.pm b/SL/Form.pm
index b6e0d8365..9193e3356 100644
--- a/SL/Form.pm
+++ b/SL/Form.pm
@@ -2248,9 +2248,12 @@ sub save_history {
 		&get_employee($self, $dbh);
 	}
 	
-	my $query = qq|INSERT INTO status (trans_id, employee_id, addition, what_done) 
-				   VALUES ($self->{id}, $self->{employee_id}, '$self->{addition}', '$self->{what_done}')|;
-	$dbh->do($query) || $self->dberror($query);
+	my $query =
+    qq|INSERT INTO history_erp (trans_id, employee_id, addition, what_done) | .
+    qq|VALUES (?, ?, ?, ?)|;
+  my @values = (conv_i($self->{id}), conv_i($self->{employee_id}),
+                $self->{addition}, $self->{what_done});
+  do_query($self, $dbh, $query, @values);
 	
 	$main::lxdebug->leave_sub();
 }
@@ -2264,27 +2267,29 @@ sub get_history {
 	my $restriction = shift();
 	my @tempArray;
 	my $i = 0;
-	if($trans_id ne ""){
-		my $query = qq|SELECT st.employee_id, st.itime, st.addition, st.what_done, emp.name 
-				   FROM status st 
-				   LEFT JOIN employee emp 
-				   ON emp.id = st.employee_id
-				   WHERE trans_id = $trans_id|
-				   . $restriction;
+	if ($trans_id ne "") {
+		my $query =
+      qq|SELECT h.employee_id, h.itime, h.addition, h.what_done, emp.name | .
+      qq|FROM history_erp h | .
+      qq|LEFT JOIN employee emp | .
+      qq|ON emp.id = st.employee_id | .
+      qq|WHERE trans_id = ? |
+      . $restriction;
 	
 		my $sth = $dbh->prepare($query) || $self->dberror($query);
 	
-		$sth->execute() || $self->dberror($query);
+		$sth->execute($trans_id) || $self->dberror("$query ($trans_id)");
 
 		while(my $hash_ref = $sth->fetchrow_hashref()) {
 			$hash_ref->{addition} = $main::locale->text($hash_ref->{addition});
 			$hash_ref->{what_done} = $main::locale->text($hash_ref->{what_done});
 			$tempArray[$i++] = $hash_ref; 
 		}
-		return \@tempArray if ($i > 0 && $tempArray[0] ne "");
+    $main::lxdebug->leave_sub() and return \@tempArray
+      if ($i > 0 && $tempArray[0] ne "");
 	}
-	return 0;
 	$main::lxdebug->leave_sub();
+	return 0;
 }
 
 sub save_status {
diff --git a/sql/Pg-upgrade2/history_erp.sql b/sql/Pg-upgrade2/history_erp.sql
new file mode 100644
index 000000000..daa9c4b05
--- /dev/null
+++ b/sql/Pg-upgrade2/history_erp.sql
@@ -0,0 +1,18 @@
+-- @tag: history_erp
+-- @description: Entfernen der Spalten in Tabelle status zum Speichern der history und daf&uuml;r eigene Tabelle f&uuml;r die history
+-- @depends: status_history
+ALTER TABLE status DROP COLUMN id;
+ALTER TABLE status DROP COLUMN employee_id;
+ALTER TABLE status DROP COLUMN addition;
+ALTER TABLE status DROP COLUMN what_done;
+
+CREATE TABLE history_erp (
+       id integer NOT NULL DEFAULT nextval('id'),
+       trans_id integer,
+       employee_id integer,
+       addition text,
+       what_done text,
+
+       PRIMARY KEY (id),
+       FOREIGN KEY (employee_id) REFERENCES employee (id)
+);
-- 
2.20.1