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;
+
# 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'),
+ VALUES (?, ?, ?, ?, ?, ?, (SELECT id FROM employee WHERE login = ?), ?, ?,
(SELECT id FROM transfer_type WHERE direction = 'out' AND description = 'used'),
(SELECT current_date))|;
my $sthTransferPartSQL = prepare_query($form, $dbh, $transferPartSQL);
+ my $trans_id;
# der return-string für die fehlermeldung inkl. welche waren zum fertigen noch fehlen
my $temppart_chargenumber = "";
my $temppart_bestbefore = localtime();
my $temppart_qty = $partsQTY * -1;
+ ($trans_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')| ) unless $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
my $temppart_bestbefore = conv_date($temphash_ref->{bestbefore});
my $temppart_qty = $temphash_ref->{sum};
+ ($trans_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')| ) unless $trans_id;
if ($tmpPartsQTY > $temppart_qty) { # wir haben noch mehr waren zum wegbuchen.
# Wir buchen den kompletten Lagerplatzbestand und zählen die Hilfsvariable runter
$tmpPartsQTY = $tmpPartsQTY - $temppart_qty;
# 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?
+ 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
$tmpPartsQTY *=-1;
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
# keine einzelteile definiert
$kannNichtFertigen ="Für dieses Erzeugnis sind keine Einzelteile definiert.
Dementsprechend kann auch nichts hergestellt werden";
- }
+ }
# gibt die Fehlermeldung zurück. A.) Keine Teile definiert
# B.) Artikel und Anzahl der fehlenden Teile/Dienstleistungen
die "<br><br>" . $kannNichtFertigen if ($kannNichtFertigen);
# soweit alles gut. Jetzt noch die wirkliche Lagerbewegung für das Erzeugnis ausführen ...
+ ($trans_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')| ) unless $trans_id;
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'),
+ VALUES (?, ?, ?, ?, ?, ?, (SELECT id FROM employee WHERE login = ?), ?, ?,
(SELECT id FROM transfer_type WHERE direction = 'in' AND description = 'assembled'),
(select current_date))|;
my $sthTransferAssemblySQL = prepare_query($form, $dbh, $transferAssemblySQL);
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}, $trans_id);
+
+
1;
}) or do { return $kannNichtFertigen };
"comment" => "i1.comment",
"trans_type" => "tt.description",
"trans_id" => "i1.trans_id",
+ "id" => "i1.id",
"oe_id" => "COALESCE(i1.oe_id, i2.oe_id)",
"invoice_id" => "COALESCE(i1.invoice_id, i2.invoice_id)",
"date" => "i1.shippingdate",
};
$form->{l_classification_id} = 'Y';
+ $form->{l_trans_id} = 'Y';
$form->{l_part_type} = 'Y';
$form->{l_itime} = 'Y';
$form->{l_invoice_id} = $form->{l_oe_id} if $form->{l_oe_id};
$where_clause = defined($where_clause) ? $where_clause : '';
my $query =
- qq|SELECT * FROM (SELECT DISTINCT $select{trans}
- FROM inventory i1
- LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id
- LEFT JOIN parts p ON i1.parts_id = p.id
- LEFT JOIN bin b1 ON i1.bin_id = b1.id
- LEFT JOIN bin b2 ON i2.bin_id = b2.id
- LEFT JOIN warehouse w1 ON i1.warehouse_id = w1.id
- LEFT JOIN warehouse w2 ON i2.warehouse_id = w2.id
- LEFT JOIN transfer_type tt ON i1.trans_type_id = tt.id
- LEFT JOIN project pr ON i1.project_id = pr.id
- LEFT JOIN employee e ON i1.employee_id = e.id
- WHERE $where_clause i2.qty = -i1.qty AND i2.qty > 0 AND
- i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) = 2 )
- GROUP BY $group_clause
-
- UNION
-
+ qq|SELECT * FROM (
SELECT DISTINCT $select{out}
FROM inventory i1
- LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id
+ LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id AND i1.id = i2.id
LEFT JOIN parts p ON i1.parts_id = p.id
LEFT JOIN bin b1 ON i1.bin_id = b1.id
LEFT JOIN bin b2 ON i2.bin_id = b2.id
LEFT JOIN transfer_type tt ON i1.trans_type_id = tt.id
LEFT JOIN project pr ON i1.project_id = pr.id
LEFT JOIN employee e ON i1.employee_id = e.id
- WHERE $where_clause i1.qty < 0 AND
- i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) = 1 )
+ WHERE $where_clause i1.qty != 0 AND tt.direction = 'out' AND
+ i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) >= 1 )
GROUP BY $group_clause
UNION
SELECT DISTINCT $select{in}
FROM inventory i1
- LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id
+ LEFT JOIN inventory i2 ON i1.trans_id = i2.trans_id AND i1.id = i2.id
LEFT JOIN parts p ON i1.parts_id = p.id
LEFT JOIN bin b1 ON i1.bin_id = b1.id
LEFT JOIN bin b2 ON i2.bin_id = b2.id
LEFT JOIN transfer_type tt ON i1.trans_type_id = tt.id
LEFT JOIN project pr ON i1.project_id = pr.id
LEFT JOIN employee e ON i1.employee_id = e.id
- WHERE $where_clause i1.qty > 0 AND
- i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) = 1 )
+ WHERE $where_clause i1.qty != 0 AND tt.direction = 'in' AND
+ i1.trans_id IN ( SELECT i.trans_id FROM inventory i GROUP BY i.trans_id HAVING COUNT(i.trans_id) >= 1 )
GROUP BY $group_clause
- ORDER BY r_${sort_spec}) AS lines WHERE r_qty>0|;
+ ORDER BY r_${sort_spec}) AS lines WHERE r_qty != 0|;
- my @all_vars = (@filter_vars,@filter_vars,@filter_vars);
+ my @all_vars = (@filter_vars,@filter_vars);
if ($filter{limit}) {
$query .= " LIMIT ?";
There has to be at least one data set in the table assembly referenced to this assembly_id.
-=item Assembly cannot be destroyed or disassembled
+=item Assembly can be disassembled
Assemblies are like cakes. You cannot disassemble it. NEVER.
- No negative nor zero qty's are valid inputs.
+ But if your assembly is a mechanical cake you may unscrew it.
+ Assemblies are created in one transaction therefore you can
+ safely rely on the trans_id in inventory to disassemble the
+ created assemblies (see action disassemble_assembly in wh.pl).
=item The assembly item(s) have to be in the same warehouse