From 028a47070f1c96c07828b9c8e802be253224df4f Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Fri, 9 Dec 2005 15:48:19 +0000 Subject: [PATCH] DB-Schema geaendert um in invoice und orderitems die Variablen ordnumber_$i, transdate_$i, cusordnumber_$i zu speichern. Speichern/Laden von Auftraegen und Buchen/Laden von Rechnungen modifiziert um diese Variablen zu halten. --- SL/IS.pm | 10 ++++++---- SL/OE.pm | 20 +++++++++++++++----- sql/Pg-upgrade-2.1.1-2.1.2.sql | 8 ++++++++ sql/lx-office.sql | 10 ++++++++-- 4 files changed, 37 insertions(+), 11 deletions(-) diff --git a/SL/IS.pm b/SL/IS.pm index 6043323e1..f4bfd1da6 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -561,14 +561,15 @@ sub post_invoice { # save detail record in invoice table $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty, sellprice, fxsellprice, discount, allocated, assemblyitem, - unit, deliverydate, project_id, serialnumber, pricegroup_id) + unit, deliverydate, project_id, serialnumber, pricegroup_id, + ordnumber, transdate, cusordnumber) VALUES ($form->{id}, $form->{"id_$i"}, '$form->{"description_$i"}', $form->{"qty_$i"}, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated, 'f', '$form->{"unit_$i"}', $deliverydate, (SELECT id from project where projectnumber = '$project_id'), - '$form->{"serialnumber_$i"}', - '$pricegroup_id')|; + '$form->{"serialnumber_$i"}', '$pricegroup_id', + '$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}')|; $dbh->do($query) || $form->dberror($query); if ($form->{lizenzen}) { @@ -1221,7 +1222,8 @@ sub retrieve_invoice { i.discount, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, pr.projectnumber, i.serialnumber, p.partnumber, p.assembly, p.bin, p.notes AS partnotes, i.id AS invoice_pos, - pg.partsgroup, i.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=i.pricegroup_id) as pricegroup + pg.partsgroup, i.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=i.pricegroup_id) as pricegroup, + i.ordnumber, i.transdate, i.cusordnumber FROM invoice i JOIN parts p ON (i.parts_id = p.id) LEFT JOIN project pr ON (i.project_id = pr.id) diff --git a/SL/OE.pm b/SL/OE.pm index 29d8c09e7..dad8e7540 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -324,7 +324,8 @@ sub save { $query = qq|INSERT INTO orderitems (|; $query .= "id, " if $form->{"orderitems_id_$i"}; $query .= qq|trans_id, parts_id, description, qty, sellprice, discount, - unit, reqdate, project_id, serialnumber, ship, pricegroup_id) + unit, reqdate, project_id, serialnumber, ship, pricegroup_id, + ordnumber, transdate, cusordnumber) VALUES (|; $query .= qq|$form->{"orderitems_id_$i"},| if $form->{"orderitems_id_$i"}; @@ -332,8 +333,8 @@ sub save { '$form->{"description_$i"}', $form->{"qty_$i"}, $fxsellprice, $form->{"discount_$i"}, '$form->{"unit_$i"}', $reqdate, (SELECT id from project where projectnumber = '$project_id'), - '$form->{"serialnumber_$i"}', $form->{"ship_$i"}, - '$pricegroup_id')|; + '$form->{"serialnumber_$i"}', $form->{"ship_$i"}, '$pricegroup_id', + '$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}')|; $dbh->do($query) || $form->dberror($query); $form->{"sellprice_$i"} = $fxsellprice; @@ -582,7 +583,7 @@ sub retrieve { # connect to database my $dbh = $form->dbconnect_noauto($myconfig); - my $query; + my $query, @ids; # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later map { push @ids, $form->{"trans_id_$_"} if ($form->{"id_$_"}) } (1 .. $form->{"rowcount"}); @@ -708,10 +709,11 @@ sub retrieve { c1.accno AS inventory_accno, c2.accno AS income_accno, c3.accno AS expense_accno, - oe.ordnumber, oe.transdate, oe.cusordnumber, + oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe, p.partnumber, p.assembly, o.description, o.qty, o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, p.notes AS partnotes, o.reqdate, o.project_id, o.serialnumber, o.ship, + o.ordnumber, o.transdate, o.cusordnumber, pr.projectnumber, pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup FROM orderitems o @@ -732,6 +734,14 @@ sub retrieve { $sth->execute || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + + # in collective order, copy global ordnumber, transdate, cusordnumber into item scope + # unless already present there + # remove _oe entries afterwards + map { $ref->{$_} = $ref->{"${_}_oe"} if ($ref->{$_} eq '') } + qw|ordnumber transdate cusordnumber| if (@ids); + map{ delete $ref->{$_} } + qw|ordnumber_oe transdate_oe cusordnumber_oe|; #set expense_accno=inventory_accno if they are different => bilanz $vendor_accno = diff --git a/sql/Pg-upgrade-2.1.1-2.1.2.sql b/sql/Pg-upgrade-2.1.1-2.1.2.sql index 534a25580..7fc851669 100644 --- a/sql/Pg-upgrade-2.1.1-2.1.2.sql +++ b/sql/Pg-upgrade-2.1.1-2.1.2.sql @@ -67,6 +67,10 @@ alter table invoice add column fxsellprice numeric(15,5); update invoice set fxsellprice=fxsellpriceold; alter table invoice drop column fxsellpriceold; -- +alter table invoice add column ordnumber text; +alter table invoice add column transdate text; +alter table invoice add column cusordnumber text; +-- --TABLE oe alter table oe rename column amount to amountold; alter table oe add column amount numeric(15,5); @@ -84,6 +88,10 @@ alter table orderitems add column sellprice numeric(15,5); update orderitems set sellprice=sellpriceold; alter table orderitems drop column sellpriceold; -- +alter table orderitems add column ordnumber text; +alter table orderitems add column transdate text; +alter table orderitems add column cusordnumber text; +-- --TABLE parts alter table parts rename column listprice to listpriceold; alter table parts add column listprice numeric(15,5); diff --git a/sql/lx-office.sql b/sql/lx-office.sql index 4b1606c7f..240803396 100644 --- a/sql/lx-office.sql +++ b/sql/lx-office.sql @@ -161,7 +161,10 @@ CREATE TABLE "invoice" ( "serialnumber" text, "itime" timestamp DEFAULT now(), "mtime" timestamp, - "pricegroup_id" integer + "pricegroup_id" integer, + "ordnumber" text, + "transdate" text, + "cusordnumber" text ); CREATE TABLE "vendor" ( @@ -397,7 +400,10 @@ CREATE TABLE "orderitems" ( "id" integer DEFAULT nextval('orderitemsid'::text) PRIMARY KEY, "itime" timestamp DEFAULT now(), "mtime" timestamp, - "pricegroup_id" integer + "pricegroup_id" integer, + "ordnumber" text, + "transdate" text, + "cusordnumber" text ); CREATE TABLE "exchangerate" ( -- 2.20.1