1 package SL::Controller::CsvImport::Inventory;
7 use SL::Helper::DateTime;
10 use SL::DB::Inventory;
12 use SL::DB::Warehouse;
14 use SL::DB::TransferType;
17 use parent qw(SL::Controller::CsvImport::Base);
20 use Rose::Object::MakeMethods::Generic
22 'scalar --get_set_init' => [ qw(settings parts_by warehouses_by bins_by) ],
28 $self->class('SL::DB::Inventory');
31 sub set_profile_defaults {
37 my $profile = $self->SUPER::init_profile;
38 delete @{$profile}{qw(trans_id oe_id delivery_order_items_stock_id bestbefore trans_type_id project_id)};
46 return { map { ( $_ => $self->controller->profile->get($_) ) } qw(warehouse apply_warehouse
48 comment apply_comment) };
54 my $all_parts = SL::DB::Manager::Part->get_all;
55 return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $all_parts } } ) } qw(id partnumber ean description) };
58 sub init_warehouses_by {
61 my $all_warehouses = SL::DB::Manager::Warehouse->get_all(query => [ or => [ invalid => 0, invalid => undef ]]);
62 return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $all_warehouses } } ) } qw(id description) };
68 my $all_bins = SL::DB::Manager::Bin->get_all();
70 $bins_by->{_wh_id_and_id_ident()} = { map { ( _wh_id_and_id_maker($_->warehouse_id, $_->id) => $_ ) } @{ $all_bins } };
71 $bins_by->{_wh_id_and_description_ident()} = { map { ( _wh_id_and_description_maker($_->warehouse_id, $_->description) => $_ ) } @{ $all_bins } };
79 $self->controller->track_progress(phase => 'building data', progress => 0);
82 my $num_data = scalar @{ $self->controller->data };
83 foreach my $entry (@{ $self->controller->data }) {
84 $self->controller->track_progress(progress => $i/$num_data * 100) if $i % 100 == 0;
86 $self->check_warehouse($entry);
87 $self->check_bin($entry);
88 $self->check_part($entry);
89 $self->check_qty($entry) unless scalar @{ $entry->{errors} };
90 $self->handle_comment($entry);
91 $self->handle_employee($entry);
92 $self->handle_transfer_type($entry) unless scalar @{ $entry->{errors} };
93 $self->handle_shippingdate($entry);
98 $self->add_info_columns(qw(warehouse bin partnumber employee target_qty));
101 sub setup_displayable_columns {
104 $self->SUPER::setup_displayable_columns;
106 $self->add_displayable_columns({ name => 'bin', description => $::locale->text('Bin') },
107 { name => 'bin_id', description => $::locale->text('Bin (database ID)') },
108 { name => 'chargenumber', description => $::locale->text('Charge number') },
109 { name => 'comment', description => $::locale->text('Comment') },
110 { name => 'employee_id', description => $::locale->text('Employee (database ID)') },
111 { name => 'partnumber', description => $::locale->text('Part Number') },
112 { name => 'parts_id', description => $::locale->text('Part (database ID)') },
113 { name => 'qty', description => $::locale->text('qty (to transfer)') },
114 { name => 'shippingdate', description => $::locale->text('Shipping date') },
115 { name => 'target_qty', description => $::locale->text('Target Qty') },
116 { name => 'warehouse', description => $::locale->text('Warehouse') },
117 { name => 'warehouse_id', description => $::locale->text('Warehouse (database ID)') },
121 sub check_warehouse {
122 my ($self, $entry) = @_;
124 my $object = $entry->{object};
126 # If warehouse from front-end is enforced for all transfers, use this, if valid.
127 if ($self->settings->{apply_warehouse} eq 'all') {
128 $object->warehouse_id(undef);
129 my $wh = $self->warehouses_by->{description}->{ $self->settings->{warehouse} };
131 push @{ $entry->{errors} }, $::locale->text('Error: Invalid warehouse');
135 $object->warehouse_id($wh->id);
138 # If warehouse from front-end is enforced for transfers with missing warehouse, use this, if valid.
139 if ( $self->settings->{apply_warehouse} eq 'missing'
140 && ! $object->warehouse_id
141 && ! $entry->{raw_data}->{warehouse} ) {
142 my $wh = $self->warehouses_by->{description}->{ $self->settings->{warehouse} };
144 push @{ $entry->{errors} }, $::locale->text('Error: Invalid warehouse');
148 $object->warehouse_id($wh->id);
151 # Check wether or not warehouse ID is valid.
152 if ($object->warehouse_id && !$self->warehouses_by->{id}->{ $object->warehouse_id }) {
153 push @{ $entry->{errors} }, $::locale->text('Error: Invalid warehouse');
157 # Map description to ID if given.
158 if (!$object->warehouse_id && $entry->{raw_data}->{warehouse}) {
159 my $wh = $self->warehouses_by->{description}->{ $entry->{raw_data}->{warehouse} };
161 push @{ $entry->{errors} }, $::locale->text('Error: Invalid warehouse');
165 $object->warehouse_id($wh->id);
168 if ($object->warehouse_id) {
169 $entry->{info_data}->{warehouse} = $self->warehouses_by->{id}->{ $object->warehouse_id }->description;
171 push @{ $entry->{errors} }, $::locale->text('Error: Warehouse not found');
178 # Check bin for given warehouse, so check_warehouse must be called first.
180 my ($self, $entry) = @_;
182 my $object = $entry->{object};
184 # If bin from front-end is enforced for all transfers, use this, if valid.
185 if ($self->settings->{apply_bin} eq 'all') {
186 $object->bin_id(undef);
187 my $bin = $self->bins_by->{_wh_id_and_description_ident()}->{ _wh_id_and_description_maker($object->warehouse_id, $self->settings->{bin}) };
189 push @{ $entry->{errors} }, $::locale->text('Error: Invalid bin');
193 $object->bin_id($bin->id);
196 # If bin from front-end is enforced for transfers with missing bin, use this, if valid.
197 if ( $self->settings->{apply_bin} eq 'missing'
199 && ! $entry->{raw_data}->{bin} ) {
200 my $bin = $self->bins_by->{_wh_id_and_description_ident()}->{ _wh_id_and_description_maker($object->warehouse_id, $self->settings->{bin}) };
202 push @{ $entry->{errors} }, $::locale->text('Error: Invalid bin');
206 $object->bin_id($bin->id);
209 # Check wether or not bin ID is valid.
210 if ($object->bin_id && !$self->bins_by->{_wh_id_and_id_ident()}->{ _wh_id_and_id_maker($object->warehouse_id, $object->bin_id) }) {
211 push @{ $entry->{errors} }, $::locale->text('Error: Invalid bin');
215 # Map description to ID if given.
216 if (!$object->bin_id && $entry->{raw_data}->{bin}) {
217 my $bin = $self->bins_by->{_wh_id_and_description_ident()}->{ _wh_id_and_description_maker($object->warehouse_id, $entry->{raw_data}->{bin}) };
219 push @{ $entry->{errors} }, $::locale->text('Error: Invalid bin');
223 $object->bin_id($bin->id);
226 if ($object->bin_id) {
227 $entry->{info_data}->{bin} = $self->bins_by->{_wh_id_and_id_ident()}->{ _wh_id_and_id_maker($object->warehouse_id, $object->bin_id) }->description;
229 push @{ $entry->{errors} }, $::locale->text('Error: Bin not found');
237 my ($self, $entry) = @_;
239 my $object = $entry->{object};
241 # Check wether or non part ID is valid.
242 if ($object->parts_id && !$self->parts_by->{id}->{ $object->parts_id }) {
243 push @{ $entry->{errors} }, $::locale->text('Error: Invalid part');
247 # Map number to ID if given.
248 if (!$object->parts_id && $entry->{raw_data}->{partnumber}) {
249 my $part = $self->parts_by->{partnumber}->{ $entry->{raw_data}->{partnumber} };
251 push @{ $entry->{errors} }, $::locale->text('Error: Invalid part');
255 $object->parts_id($part->id);
258 if ($object->parts_id) {
259 $entry->{info_data}->{partnumber} = $self->parts_by->{id}->{ $object->parts_id }->partnumber;
261 push @{ $entry->{errors} }, $::locale->text('Error: Part not found');
268 # This imports inventories when target_qty is given, transfers else.
269 # So we get the actual qty in stock and transfer the difference in case of
272 my ($self, $entry) = @_;
274 my $object = $entry->{object};
276 if (! exists $entry->{raw_data}->{target_qty} && ! exists $entry->{raw_data}->{qty}) {
277 push @{ $entry->{errors} }, $::locale->text('Error: A quantity or a target quantity must be given.');
281 if (exists $entry->{raw_data}->{target_qty} && exists $entry->{raw_data}->{qty}) {
282 push @{ $entry->{errors} }, $::locale->text('Error: A quantity and a target quantity could not be given both.');
286 if (exists $entry->{raw_data}->{target_qty} && ($entry->{raw_data}->{target_qty} * 1) < 0) {
287 push @{ $entry->{errors} }, $::locale->text('Error: A negative target quantity is not allowed.');
291 # Actual quantity is read from stock or is the result of transfers for the
292 # same part, warehouse, bin and chargenumber done before.
293 my $key = join '+', $object->parts_id, $object->warehouse_id, $object->bin_id, $object->chargenumber;
294 if (!exists $self->{resulting_quantities}->{$key}) {
296 SELECT sum(qty) FROM inventory
297 WHERE parts_id = ? AND warehouse_id = ? AND bin_id = ? AND chargenumber = ?
298 GROUP BY warehouse_id, bin_id, chargenumber
301 my ($stocked_qty) = selectrow_query($::form, $::form->get_standard_dbh, $query,
302 $object->parts_id, $object->warehouse_id, $object->bin_id, $object->chargenumber);
303 $self->{resulting_quantities}->{$key} = $stocked_qty;
305 my $actual_qty = $self->{resulting_quantities}->{$key};
307 if (exists $entry->{raw_data}->{target_qty}) {
308 my $target_qty = $entry->{raw_data}->{target_qty} * 1;
310 $object->qty($target_qty - $actual_qty);
311 $self->add_columns(qw(qty));
314 if ($object->qty == 0) {
315 push @{ $entry->{errors} }, $::locale->text('Error: Quantity to transfer is zero.');
319 # Check if resulting quantity is below zero.
320 if ( ($actual_qty + $object->qty) < 0 ) {
321 push @{ $entry->{errors} }, $::locale->text('Error: Transfer would result in a negative target quantity.');
325 $self->{resulting_quantities}->{$key} += $object->qty;
326 $entry->{info_data}->{target_qty} = $self->{resulting_quantities}->{$key};
332 my ($self, $entry) = @_;
334 my $object = $entry->{object};
336 # If comment from front-end is enforced for all transfers, use this, if valid.
337 if ($self->settings->{apply_comment} eq 'all') {
338 $object->comment($self->settings->{comment});
341 # If comment from front-end is enforced for transfers with missing comment, use this, if valid.
342 if ($self->settings->{apply_comment} eq 'missing' && ! $object->comment) {
343 $object->comment($self->settings->{comment});
349 sub handle_transfer_type {
350 my ($self, $entry) = @_;
352 my $object = $entry->{object};
354 my $transfer_type = SL::DB::Manager::TransferType->find_by(description => 'correction',
355 direction => ($object->qty > 0)? 'in': 'out');
356 $object->trans_type($transfer_type);
361 # ToDo: employee by name
362 sub handle_employee {
363 my ($self, $entry) = @_;
365 my $object = $entry->{object};
367 # employee from front end if not given
368 if (!$object->employee_id) {
369 $object->employee_id($self->controller->{employee_id})
372 # employee from login if not given
373 if (!$object->employee_id) {
374 $object->employee_id(SL::DB::Manager::Employee->find_by(login => $::myconfig{login})->id);
377 if ($object->employee_id) {
378 $entry->{info_data}->{employee} = $object->employee->name;
383 sub handle_shippingdate {
384 my ($self, $entry) = @_;
386 my $object = $entry->{object};
388 if (!$object->shippingdate) {
389 $object->shippingdate(DateTime->today_local);
394 my ($self, %params) = @_;
396 my $data = $params{data} || $self->controller->data;
398 foreach my $entry (@{ $data }) {
399 my ($trans_id) = selectrow_query($::form, $::form->get_standard_dbh, qq|SELECT nextval('id')|);
400 $entry->{object}->trans_id($trans_id);
403 $self->SUPER::save_objects(%params);
406 sub _wh_id_and_description_ident {
407 return 'wh_id+description';
410 sub _wh_id_and_description_maker {
411 return join '+', $_[0], $_[1]
414 sub _wh_id_and_id_ident {
418 sub _wh_id_and_id_maker {
419 return join '+', $_[0], $_[1]