# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
-# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
+# MA 02110-1335, USA.
#======================================================================
#
# Warehouse module
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 $db = SL::DB::Inventory->new->db;
$db->with_transaction(sub{
while (my $transfer = shift @args) {
- my ($trans_id) = selectrow_query($::form, $::form->get_standard_dbh, qq|SELECT nextval('id')|);
+ my $trans_id;
+ ($trans_id) = selectrow_query($::form, $::form->get_standard_dbh, qq|SELECT nextval('id')|) if $transfer->{qty};
my $part = $objectify->($transfer, 'parts', 'SL::DB::Part');
my $unit = $objectify->($transfer, 'unit', 'SL::DB::Unit', name => $transfer->{unit});
$direction |= 1 if $src_bin;
$direction |= 2 if $dst_bin;
- my $transfer_type = $objectify->($transfer, 'transfer_type', 'SL::DB::TransferType', direction => $directions[$direction],
- description => $transfer->{transfer_type});
+ my $transfer_type_id;
+ if ($transfer->{transfer_type_id}) {
+ $transfer_type_id = $transfer->{transfer_type_id};
+ } else {
+ my $transfer_type = $objectify->($transfer, 'transfer_type', 'SL::DB::TransferType', direction => $directions[$direction],
+ description => $transfer->{transfer_type});
+ $transfer_type_id = $transfer_type->id;
+ }
+
+ my $stocktaking_qty = $transfer->{stocktaking_qty};
my %params = (
part => $part,
employee => $employee,
- trans_type => $transfer_type,
+ trans_type_id => $transfer_type_id,
project => $project,
trans_id => $trans_id,
shippingdate => !$transfer->{shippingdate} || $transfer->{shippingdate} eq 'current_date'
);
if ($unit) {
- $qty = $unit->convert_to($qty, $part->unit_obj);
+ $qty = $unit->convert_to($qty, $part->unit_obj);
+ $stocktaking_qty = $unit->convert_to($stocktaking_qty, $part->unit_obj);
}
$params{chargenumber} ||= '';
- if ($direction & 1) {
- SL::DB::Inventory->new(
+ my @inventories;
+ if ($qty && $direction & 1) {
+ push @inventories, SL::DB::Inventory->new(
%params,
warehouse => $src_wh,
bin => $src_bin,
)->save;
}
- if ($direction & 2) {
- SL::DB::Inventory->new(
+ if ($qty && $direction & 2) {
+ push @inventories, SL::DB::Inventory->new(
%params,
warehouse => $dst_wh->id,
bin => $dst_bin->id,
}
}
+ # Record stocktaking if requested.
+ # This is only possible if transfer was a stock in or stock out,
+ # but not both (transfer).
+ if ($transfer->{record_stocktaking}) {
+ die 'Stocktaking can only be recorded for stock in or stock out, but not on a transfer.' if scalar @inventories > 1;
+
+ my $inventory_id;
+ $inventory_id = $inventories[0]->id if $inventories[0];
+
+ SL::DB::Stocktaking->new(
+ inventory_id => $inventory_id,
+ warehouse => $src_wh || $dst_wh,
+ bin => $src_bin || $dst_bin,
+ parts_id => $part->id,
+ employee_id => $employee->id,
+ qty => $stocktaking_qty,
+ comment => $transfer->{comment},
+ cutoff_date => $transfer->{stocktaking_cutoff_date},
+ chargenumber => $transfer->{chargenumber},
+ bestbefore => $transfer->{bestbefore},
+ )->save;
+
+ }
+
push @trans_ids, $trans_id;
}
my $query = qq|SELECT assembly.parts_id, assembly.qty, parts.warehouse_id
FROM assembly INNER JOIN parts ON assembly.parts_id = parts.id
- WHERE assembly.id = ? AND (inventory_accno_id IS NOT NULL OR parts.assembly = TRUE)|;
+ WHERE assembly.id = ? AND parts.part_type != 'service'|;
my $sth_part_qty_assembly = prepare_execute_query($form, $dbh, $query, $params{assembly_id});
}
# gibt die Fehlermeldung zurück. A.) Keine Teile definiert
# B.) Artikel und Anzahl der fehlenden Teile/Dienstleistungen
- if ($kannNichtFertigen) {
- return 0;
- }
+ die "<br><br>" . $kannNichtFertigen if ($kannNichtFertigen);
# 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,
push @filter_vars, like($filter{description});
}
+ if ($filter{classification_id}) {
+ push @filter_ary, "p.classification_id = ?";
+ push @filter_vars, $filter{classification_id};
+ }
+
if ($filter{chargenumber}) {
push @filter_ary, "i1.chargenumber ILIKE ?";
push @filter_vars, like($filter{chargenumber});
my $sort_order = $form->{order};
$sort_col = $filter{sort} unless $sort_col;
+ $sort_col = 'shippingdate' if $sort_col eq 'date';
$sort_order = ($sort_col = 'shippingdate') unless $sort_col;
- $sort_col = 'shippingdate' if $sort_col eq 'date';
- $sort_order = $filter{order} unless $sort_order;
- my $sort_spec = "${sort_col} " . ($sort_order ? " DESC" : " ASC");
+
+ my %orderspecs = (
+ 'shippingdate' => ['shippingdate', 'r_itime', 'r_parts_id'],
+ 'bin_to' => ['bin_to', 'r_itime', 'r_parts_id'],
+ 'bin_from' => ['bin_from', 'r_itime', 'r_parts_id'],
+ 'warehouse_to' => ['warehouse_to, r_itime, r_parts_id'],
+ 'warehouse_from' => ['warehouse_from, r_itime, r_parts_id'],
+ 'partnumber' => ['partnumber'],
+ 'partdescription'=> ['partdescription'],
+ 'partunit' => ['partunit, r_itime, r_parts_id'],
+ 'qty' => ['qty, r_itime, r_parts_id'],
+ 'oe_id' => ['oe_id'],
+ 'comment' => ['comment'],
+ 'trans_type' => ['trans_type'],
+ 'employee' => ['employee'],
+ 'projectnumber' => ['projectnumber'],
+ 'chargenumber' => ['chargenumber'],
+ );
+
+ $sort_order = $filter{order} unless $sort_order;
+ my $ASC = ($sort_order ? " DESC" : " ASC");
+ my $sort_spec = join("$ASC , ", @{$orderspecs{$sort_col}}). " $ASC";
my $where_clause = @filter_ary ? join(" AND ", @filter_ary) . " AND " : '';
"qty" => "ABS(SUM(i1.qty))",
"partnumber" => "p.partnumber",
"partdescription" => "p.description",
+ "classification_id" => "p.classification_id",
+ "part_type" => "p.part_type",
"bindescription" => "b.description",
"chargenumber" => "i1.chargenumber",
"bestbefore" => "i1.bestbefore",
"warehouse_from" => "'$filter{na}'",
};
+ $form->{l_classification_id} = 'Y';
+ $form->{l_part_type} = 'Y';
+ $form->{l_itime} = 'Y';
$form->{l_invoice_id} = $form->{l_oe_id} if $form->{l_oe_id};
# build the select clauses.
}
my $group_clause = join ", ", map { +/^l_/; "r_$'" }
- ( grep( { !/qty$/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_partunit l_shippingdate) );
+ ( grep( { !/qty$/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form), qw(l_parts_id l_partunit l_shippingdate l_itime) );
$where_clause = defined($where_clause) ? $where_clause : '';
my $query =
- qq|SELECT DISTINCT $select{trans}
+ 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
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 )
GROUP BY $group_clause
- ORDER BY r_${sort_spec}|;
+ ORDER BY r_${sort_spec}) AS lines WHERE r_qty>0|;
+
+ my @all_vars = (@filter_vars,@filter_vars,@filter_vars);
+
+ if ($filter{limit}) {
+ $query .= " LIMIT ?";
+ push @all_vars,$filter{limit};
+ }
+ if ($filter{offset}) {
+ $query .= " OFFSET ?";
+ push @all_vars, $filter{offset};
+ }
- my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars, @filter_vars, @filter_vars);
+ my $sth = prepare_execute_query($form, $dbh, $query, @all_vars);
my ($h_oe_id, $q_oe_id);
if ($form->{l_oe_id}) {
$q_oe_id = <<SQL;
- SELECT oe.id AS id,
- CASE WHEN oe.quotation THEN oe.quonumber ELSE oe.ordnumber END AS number,
- CASE
- WHEN oe.customer_id IS NOT NULL AND COALESCE(oe.quotation, FALSE) THEN 'sales_quotation'
- WHEN oe.customer_id IS NOT NULL AND NOT COALESCE(oe.quotation, FALSE) THEN 'sales_order'
- WHEN oe.customer_id IS NULL AND COALESCE(oe.quotation, FALSE) THEN 'request_quotation'
- ELSE 'purchase_order'
- END AS type
- FROM oe
- WHERE oe.id = ?
-
- UNION
-
SELECT dord.id AS id, dord.donumber AS number,
CASE
WHEN dord.customer_id IS NULL THEN 'purchase_delivery_order'
UNION
- SELECT ar.id AS id, ar.invnumber AS number, 'sales_invoice' AS type
- FROM ar
- WHERE ar.id = ?
-
- UNION
-
- SELECT ap.id AS id, ap.invnumber AS number, 'purchase_invoice' AS type
- FROM ap
- WHERE ap.id = ?
-
- UNION
-
SELECT ar.id AS id, ar.invnumber AS number, 'sales_invoice' AS type
FROM ar
WHERE ar.id = (SELECT trans_id FROM invoice WHERE id = ?)
}
if ($h_oe_id && ($ref->{oe_id} || $ref->{invoice_id})) {
- my $id = $ref->{oe_id} ? $ref->{oe_id} : $ref->{invoice_id};
- do_statement($form, $h_oe_id, $q_oe_id, ($id) x 6);
+ do_statement($form, $h_oe_id, $q_oe_id, $ref->{oe_id}, ($ref->{invoice_id}) x 2);
$ref->{oe_id_info} = $h_oe_id->fetchrow_hashref() || {};
}
# - warehouse_id - will return matches with this warehouse_id only
# - partnumber - will return only matches where the given string is a substring of the partnumber
# - partsid - will return matches with this parts_id only
+# - classification_id - will return matches with this parts with this classification only
# - description - will return only matches where the given string is a substring of the description
# - chargenumber - will return only matches where the given string is a substring of the chargenumber
# - bestbefore - will return only matches with this bestbefore date
push @filter_vars, like($filter{partnumber});
}
+ if ($filter{classification_id}) {
+ push @filter_ary, "p.classification_id = ?";
+ push @filter_vars, $filter{classification_id};
+ }
+
if ($filter{description}) {
push @filter_ary, "p.description ILIKE ?";
push @filter_vars, like($filter{description});
push @filter_vars, trim($form->{bestbefore});
}
+ if ($filter{classification_id}) {
+ push @filter_ary, "p.classification_id = ?";
+ push @filter_vars, $filter{classification_id};
+ }
+
if ($filter{ean}) {
push @filter_ary, "p.ean ILIKE ?";
push @filter_vars, like($filter{ean});
"warehouseid" => "i.warehouse_id",
"partnumber" => "p.partnumber",
"partdescription" => "p.description",
+ "classification_id" => "p.classification_id",
+ "part_type" => "p.part_type",
"bindescription" => "b.description",
"binid" => "b.id",
"chargenumber" => "i.chargenumber",
"chargeid" => "c.id",
"warehousedescription" => "w.description",
"partunit" => "p.unit",
- "stock_value" => "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",
);
+ $form->{l_classification_id} = 'Y';
+ $form->{l_part_type} = 'Y';
+
my $select_clause = join ', ', map { +/^l_/; "$select_tokens{$'} AS $'" }
( grep( { !/qty/ and /^l_/ and $form->{$_} eq 'Y' } keys %$form),
qw(l_parts_id l_qty l_partunit) );
qw(l_parts_id l_qty l_partunit) );
my $query =
- qq|SELECT $select_clause
+ qq|SELECT * FROM ( SELECT $select_clause
FROM inventory i
LEFT JOIN parts p ON i.parts_id = p.id
LEFT JOIN bin b ON i.bin_id = b.id
$joins
WHERE $where_clause
GROUP BY $group_clause
- ORDER BY $sort_spec|;
+ ORDER BY $sort_spec ) AS lines WHERE qty<>0|;
- my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars);
+ if ($filter{limit}) {
+ $query .= " LIMIT ?";
+ push @filter_vars,$filter{limit};
+ }
+ if ($filter{offset}) {
+ $query .= " OFFSET ?";
+ push @filter_vars, $filter{offset};
+ }
+ my $sth = prepare_execute_query($form, $dbh, $query, @filter_vars );
my (%non_empty_bins, @all_fields, @contents);
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__
more than one is supplied, it is guaranteed, that all are processed in the same
transaction.
+It is possible to record stocktakings within this transaction as well.
+This is useful if the transfer is the result of stocktaking (see also
+C<SL::Controller::Inventory>). To do so the parameters C<record_stocktaking>,
+C<stocktaking_qty> and C<stocktaking_cutoff_date> hava to be given.
+If stocktaking should be saved, then the transfer quantity can be zero. In this
+case no entry in inventory will be made, but only the stocktaking entry.
+
Here is a full list of parameters. All "_id" parameters except oe and
orderitems can be called without id with RDB objects as well.
An expiration date. Note that this is not by default used by C<warehouse_report>.
+=item record_stocktaking
+
+A boolean flag to indicate that a stocktaking entry should be saved.
+
+=item stocktaking_qty
+
+The quantity for the stocktaking entry.
+
+=item stocktaking_cutoff_date
+
+The cutoff date for the stocktaking entry.
+
=back
=head2 create_assembly \%PARAMS, [ \%PARAMS, ... ]
Assembly item(s) will be stocked out and the assembly will be stocked in,
taking into account the qty and units which can be defined for each
-assembly item seperately.
+assembly item separately.
The calling params originate from C<transfer> but only parts_id with the
attribute assembly are processed.
'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
The method is transaction safe, in case of errors not a single entry will be made
in inventory.
-Two prerequisites can be changed with this global parameters
+Two prerequisites can be changed with these global parameters
=over 2
projects / kivitendo-erp.git / blobdiff