package WH;
+use Carp qw(croak);
+
use SL::AM;
use SL::DBUtils;
+use SL::DB::Inventory;
use SL::Form;
+use SL::Locale::String qw(t8);
use SL::Util qw(trim);
use warnings;
require SL::DB::TransferType;
require SL::DB::Part;
require SL::DB::Employee;
- require SL::DB::Inventory;
my $employee = SL::DB::Manager::Employee->find_by(login => $::myconfig{login});
my ($now) = selectrow_query($::form, $::form->get_standard_dbh, qq|SELECT current_date|);
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 ?";
"chargeid" => "c.id",
"warehousedescription" => "w.description",
"partunit" => "p.unit",
- "stock_value" => $form->{stock_value_basis} eq 'list_price' ? "p.listprice / COALESCE(pfac.factor, 1)" : "p.lastcost / COALESCE(pfac.factor, 1)",
+ "stock_value" => ($form->{stock_value_basis} // '') eq 'list_price' ? "p.listprice / COALESCE(pfac.factor, 1)" : "p.lastcost / COALESCE(pfac.factor, 1)",
"purchase_price" => "p.lastcost",
"list_price" => "p.listprice",
);
return ($max_qty_parts, $error);
}
+sub get_wh_and_bin_for_charge {
+ $main::lxdebug->enter_sub();
+
+ my $self = shift;
+ my %params = @_;
+ my %bin_qty;
+
+ croak t8('Need charge number!') unless $params{chargenumber};
+
+ my $inv_items = SL::DB::Manager::Inventory->get_all(where => [chargenumber => $params{chargenumber} ]);
+
+ croak t8("Invalid charge number: #1", $params{chargenumber}) unless (ref @{$inv_items}[0] eq 'SL::DB::Inventory');
+ # add all qty for one bin and add wh_id
+ ($bin_qty{$_->bin_id}{qty}, $bin_qty{$_->bin_id}{wh}) = ($bin_qty{$_->bin_id}{qty} + $_->qty, $_->warehouse_id) for @{ $inv_items };
+
+ while (my ($bin, $value) = each (%bin_qty)) {
+ if ($value->{qty} > 0) {
+ $main::lxdebug->leave_sub();
+ return ($value->{qty}, $value->{wh}, $bin, $params{chargenumber});
+ }
+ }
+
+ $main::lxdebug->leave_sub();
+ return undef;
+}
1;
__END__
'comment' => $form->{comment}
);
+
+=head2 get_wh_and_bin_for_charge C<$params{chargenumber}>
+
+Gets the current qty from the inventory entries with the mandatory chargenumber: C<$params{chargenumber}>.
+Croaks if the chargenumber is missing or no entry currently exists.
+If there is one bin and warehouse with a positive qty, this fields are returned:
+C<qty> C<warehouse_id>, C<bin_id>, C<chargenumber>.
+Otherwise returns undef.
+
+
=head3 Prerequisites
All of these prerequisites have to be trueish, otherwise the function will exit
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
projects / kivitendo-erp.git / blobdiff