Revision 01f65005
Von Jan Büren vor fast 4 Jahren hinzugefügt
| SL/DB/Helper/Mappings.pm | ||
|---|---|---|
|
ar => 'invoice',
|
||
|
ap => 'purchase_invoice',
|
||
|
assembly => 'assembly',
|
||
|
assembly_inventory_part => 'assembly_inventory_part',
|
||
|
assortment_items => 'assortment_item',
|
||
|
background_jobs => 'background_job',
|
||
|
background_job_histories => 'background_job_history',
|
||
| SL/DB/MetaSetup/AssemblyInventoryPart.pm | ||
|---|---|---|
|
# This file has been auto-generated. Do not modify it; it will be overwritten
|
||
|
# by rose_auto_create_model.pl automatically.
|
||
|
package SL::DB::AssemblyInventoryPart;
|
||
|
|
||
|
use strict;
|
||
|
|
||
|
use parent qw(SL::DB::Object);
|
||
|
|
||
|
__PACKAGE__->meta->table('assembly_inventory_part');
|
||
|
|
||
|
__PACKAGE__->meta->columns(
|
||
|
inventory_assembly_id => { type => 'integer', not_null => 1 },
|
||
|
inventory_part_id => { type => 'integer', not_null => 1 },
|
||
|
itime => { type => 'timestamp', default => 'now()' },
|
||
|
mtime => { type => 'timestamp' },
|
||
|
);
|
||
|
|
||
|
__PACKAGE__->meta->primary_key_columns([ 'inventory_assembly_id', 'inventory_part_id' ]);
|
||
|
|
||
|
__PACKAGE__->meta->unique_keys([ 'inventory_part_id', 'inventory_assembly_id' ]);
|
||
|
|
||
|
__PACKAGE__->meta->allow_inline_column_values(1);
|
||
|
|
||
|
__PACKAGE__->meta->foreign_keys(
|
||
|
inventory_assembly => {
|
||
|
class => 'SL::DB::Inventory',
|
||
|
key_columns => { inventory_assembly_id => 'id' },
|
||
|
},
|
||
|
|
||
|
inventory_part => {
|
||
|
class => 'SL::DB::Inventory',
|
||
|
key_columns => { inventory_part_id => 'id' },
|
||
|
},
|
||
|
);
|
||
|
|
||
|
1;
|
||
|
;
|
||
| SL/WH.pm | ||
|---|---|---|
|
my %params = @_;
|
||
|
Common::check_params(\%params, qw(assembly_id dst_warehouse_id login qty unit dst_bin_id chargenumber bestbefore comment));
|
||
|
|
||
|
# my $maxcreate=WH->check_assembly_max_create(assembly_id =>$params{'assembly_id'}, dbh => $my_dbh);
|
||
|
|
||
|
my $myconfig = \%main::myconfig;
|
||
|
my $form = $main::form;
|
||
|
my $kannNichtFertigen =""; # Falls leer dann erfolgreich
|
||
| ... | ... | |
|
|
||
|
my $sth_part_qty_assembly = prepare_execute_query($form, $dbh, $query, $params{assembly_id});
|
||
|
|
||
|
my @trans_ids;
|
||
|
my $query_trans_id = qq|SELECT nextval('inventory_id_seq')|;
|
||
|
my $query_trans_ids = qq|INSERT INTO assembly_inventory_part (inventory_assembly_id, inventory_part_id) VALUES (?, ?)|;
|
||
|
my $sth_query_trans_ids = prepare_query($form, $dbh, $query_trans_ids);
|
||
|
|
||
|
# Hier wird das prepared Statement für die Schleife über alle Lagerplätze vorbereitet
|
||
|
my $transferPartSQL = qq|INSERT INTO inventory (parts_id, warehouse_id, bin_id, chargenumber, bestbefore, comment, employee_id, qty,
|
||
|
trans_id, trans_type_id, shippingdate)
|
||
|
VALUES (?, ?, ?, ?, ?, ?, (SELECT id FROM employee WHERE login = ?), ?, nextval('id'),
|
||
|
trans_id, id, trans_type_id, shippingdate)
|
||
|
VALUES (?, ?, ?, ?, ?, ?, (SELECT id FROM employee WHERE login = ?), ?, nextval('id'), ?,
|
||
|
(SELECT id FROM transfer_type WHERE direction = 'out' AND description = 'used'),
|
||
|
(SELECT current_date))|;
|
||
|
my $sthTransferPartSQL = prepare_query($form, $dbh, $transferPartSQL);
|
||
| ... | ... | |
|
my $temppart_bestbefore = localtime();
|
||
|
my $temppart_qty = $partsQTY * -1;
|
||
|
|
||
|
my ($trans_id) = selectrow_query($form, $dbh, $query_trans_id);
|
||
|
push @trans_ids, $trans_id;
|
||
|
do_statement($form, $sthTransferPartSQL, $transferPartSQL, $currentPart_ID, $currentPart_WH_ID,
|
||
|
$temppart_bin_id, $temppart_chargenumber, $temppart_bestbefore, 'Verbraucht für ' .
|
||
|
$self->get_part_description(parts_id => $params{assembly_id}), $params{login}, $temppart_qty);
|
||
|
$self->get_part_description(parts_id => $params{assembly_id}), $params{login}, $temppart_qty,
|
||
|
$trans_id);
|
||
|
next;
|
||
|
}
|
||
|
# Überprüfen, ob diese Anzahl gefertigt werden kann
|
||
| ... | ... | |
|
# wenn * -1 als berechnung in der parameter-übergabe angegeben wird.
|
||
|
# Dieser Wert IST und BLEIBT positiv!! Hilfe.
|
||
|
# Liegt das daran, dass dieser Wert aus einem SQL-Statement stammt?
|
||
|
my ($trans_id) = selectrow_query($form, $dbh, $query_trans_id);
|
||
|
push @trans_ids, $trans_id;
|
||
|
do_statement($form, $sthTransferPartSQL, $transferPartSQL, $currentPart_ID, $currentPart_WH_ID,
|
||
|
$temppart_bin_id, $temppart_chargenumber, $temppart_bestbefore, 'Verbraucht für ' .
|
||
|
$self->get_part_description(parts_id => $params{assembly_id}), $params{login}, $temppart_qty);
|
||
|
$self->get_part_description(parts_id => $params{assembly_id}), $params{login}, $temppart_qty, $trans_id);
|
||
|
|
||
|
# hier ist noch ein fehler am besten mit definierten erzeugnissen debuggen 02/2009 jb
|
||
|
# idee: ausbuch algorithmus mit rekursion lösen und an- und abschaltbar machen
|
||
| ... | ... | |
|
# auf jeden fall war der internal-server-error nach aktivierung von strict und warnings plus ein paar my-definitionen weg
|
||
|
} else { # okay, wir haben weniger oder gleich Waren die wir wegbuchen müssen, wir können also aufhören
|
||
|
$tmpPartsQTY *=-1;
|
||
|
my ($trans_id) = selectrow_query($form, $dbh, $query_trans_id);
|
||
|
push @trans_ids, $trans_id;
|
||
|
do_statement($form, $sthTransferPartSQL, $transferPartSQL, $currentPart_ID, $currentPart_WH_ID,
|
||
|
$temppart_bin_id, $temppart_chargenumber, $temppart_bestbefore, 'Verbraucht für ' .
|
||
|
$self->get_part_description(parts_id => $params{assembly_id}), $params{login}, $tmpPartsQTY);
|
||
|
$self->get_part_description(parts_id => $params{assembly_id}), $params{login}, $tmpPartsQTY, $trans_id);
|
||
|
last; # beendet die schleife (springt zum letzten element)
|
||
|
}
|
||
|
} # ende while SELECT SUM(qty), bin_id, chargenumber, bestbefore FROM inventory WHERE warehouse_id
|
||
| ... | ... | |
|
|
||
|
# soweit alles gut. Jetzt noch die wirkliche Lagerbewegung für das Erzeugnis ausführen ...
|
||
|
my $transferAssemblySQL = qq|INSERT INTO inventory (parts_id, warehouse_id, bin_id, chargenumber, bestbefore,
|
||
|
comment, employee_id, qty, trans_id, trans_type_id, shippingdate)
|
||
|
VALUES (?, ?, ?, ?, ?, ?, (SELECT id FROM employee WHERE login = ?), ?, nextval('id'),
|
||
|
comment, employee_id, qty, trans_id, id, trans_type_id, shippingdate)
|
||
|
VALUES (?, ?, ?, ?, ?, ?, (SELECT id FROM employee WHERE login = ?), ?, nextval('id'), ?,
|
||
|
(SELECT id FROM transfer_type WHERE direction = 'in' AND description = 'assembled'),
|
||
|
(select current_date))|;
|
||
|
my $sthTransferAssemblySQL = prepare_query($form, $dbh, $transferAssemblySQL);
|
||
|
my ($assembly_trans_id) = selectrow_query($form, $dbh, $query_trans_id);
|
||
|
do_statement($form, $sthTransferAssemblySQL, $transferAssemblySQL, $params{assembly_id}, $params{dst_warehouse_id},
|
||
|
$params{dst_bin_id}, $params{chargenumber}, conv_date($params{bestbefore}), $params{comment}, $params{login}, $params{qty});
|
||
|
$params{dst_bin_id}, $params{chargenumber}, conv_date($params{bestbefore}), $params{comment}, $params{login}, $params{qty}, $assembly_trans_id);
|
||
|
|
||
|
# save inventory transactions for this assembly
|
||
|
for my $part_id (@trans_ids) {
|
||
|
do_statement($form, $sth_query_trans_ids, $query_trans_ids, $assembly_trans_id, $part_id);
|
||
|
}
|
||
|
|
||
|
1;
|
||
|
}) or do { return $kannNichtFertigen };
|
||
|
|
||
| sql/Pg-upgrade2/assembly_inventory_part.sql | ||
|---|---|---|
|
-- @tag: assembly_inventory_part
|
||
|
-- @description: Tabelle für die wirklich verbauten Einzelteile eines Erzeugnis
|
||
|
-- @depends: warehouse release_3_5_6_1
|
||
|
|
||
|
|
||
|
CREATE TABLE assembly_inventory_part (
|
||
|
inventory_part_id INTEGER REFERENCES inventory(id),
|
||
|
inventory_assembly_id INTEGER REFERENCES inventory(id),
|
||
|
itime TIMESTAMP DEFAULT now(),
|
||
|
mtime TIMESTAMP,
|
||
|
|
||
|
PRIMARY KEY (inventory_assembly_id, inventory_part_id)
|
||
|
);
|
||
|
|
||
|
CREATE TRIGGER mtime_assembly_inventory_part BEFORE UPDATE ON assembly_inventory_part FOR EACH ROW EXECUTE PROCEDURE set_mtime();
|
||
Auch abrufbar als: Unified diff
Erzeugnis fertigen -> Zustand in Hilfstabelle dokumentieren