1 package SL::Controller::Inventory;
5 use POSIX qw(strftime);
7 use parent qw(SL::Controller::Base);
10 use SL::DB::Stocktaking;
12 use SL::DB::Warehouse;
16 use SL::ReportGenerator;
17 use SL::Locale::String qw(t8);
18 use SL::Presenter::Tag qw(select_tag);
20 use SL::Helper::Flash;
21 use SL::Controller::Helper::ReportGenerator;
22 use SL::Controller::Helper::GetModels;
23 use List::MoreUtils qw(uniq);
25 use English qw(-no_match_vars);
27 use Rose::Object::MakeMethods::Generic (
28 'scalar --get_set_init' => [ qw(warehouses units is_stocktaking stocktaking_models stocktaking_cutoff_date) ],
29 'scalar' => [ qw(warehouse bin unit part) ],
32 __PACKAGE__->run_before('_check_auth');
33 __PACKAGE__->run_before('_check_warehouses');
34 __PACKAGE__->run_before('load_part_from_form', only => [ qw(stock_in part_changed mini_stock stock stocktaking_part_changed stocktaking_get_warn_qty_threshold save_stocktaking) ]);
35 __PACKAGE__->run_before('load_unit_from_form', only => [ qw(stock_in part_changed mini_stock stock stocktaking_part_changed stocktaking_get_warn_qty_threshold save_stocktaking) ]);
36 __PACKAGE__->run_before('load_wh_from_form', only => [ qw(stock_in warehouse_changed stock stocktaking stocktaking_get_warn_qty_threshold save_stocktaking) ]);
37 __PACKAGE__->run_before('load_bin_from_form', only => [ qw(stock_in stock stocktaking stocktaking_get_warn_qty_threshold save_stocktaking) ]);
38 __PACKAGE__->run_before('set_target_from_part', only => [ qw(part_changed) ]);
39 __PACKAGE__->run_before('mini_stock', only => [ qw(stock_in mini_stock) ]);
40 __PACKAGE__->run_before('sanitize_target', only => [ qw(stock_usage stock_in warehouse_changed part_changed stocktaking stocktaking_part_changed stocktaking_get_warn_qty_threshold save_stocktaking) ]);
41 __PACKAGE__->run_before('set_layout');
46 $::form->{title} = t8('Stock');
48 $::request->layout->focus('#part_id_name');
49 my $transfer_types = WH->retrieve_transfer_types('in');
50 map { $_->{description} = $main::locale->text($_->{description}) } @{ $transfer_types };
51 $self->setup_stock_in_action_bar;
52 $self->render('inventory/warehouse_selection_stock', title => $::form->{title}, TRANSFER_TYPES => $transfer_types );
55 sub action_stock_usage {
58 $::form->{title} = t8('UsageE');
60 $::form->get_lists('warehouses' => { 'key' => 'WAREHOUSES',
63 $self->setup_stock_usage_action_bar;
64 $self->render('inventory/warehouse_usage',
65 title => $::form->{title},
66 year => DateTime->today->year,
67 WAREHOUSES => $::form->{WAREHOUSES},
68 WAREHOUSE_FILTER => 1,
77 return qw(stock incorrection found insum back outcorrection disposed
78 missing shipped used outsum consumed averconsumed);
84 $main::lxdebug->enter_sub();
86 my $form = $main::form;
87 my %myconfig = %main::myconfig;
88 my $locale = $main::locale;
90 $form->{title} = t8('UsageE');
91 $form->{report_generator_output_format} = 'HTML' if !$form->{report_generator_output_format};
93 my $report = SL::ReportGenerator->new(\%myconfig, $form);
95 my @columns = qw(partnumber partdescription);
97 push @columns , qw(ptype unit) if $form->{report_generator_output_format} eq 'HTML';
99 my @numcolumns = qw(stock incorrection found insum back outcorrection disposed
100 missing shipped used outsum consumed averconsumed);
102 push @columns , $self->getnumcolumns();
104 my @hidden_variables = qw(reporttype year duetyp fromdate todate
105 warehouse_id bin_id partnumber description bestbefore chargenumber partstypes_id);
107 'partnumber' => { 'text' => $locale->text('Part Number'), },
108 'partdescription' => { 'text' => $locale->text('Part_br_Description'), },
109 'unit' => { 'text' => $locale->text('Unit'), },
110 'stock' => { 'text' => $locale->text('stock_br'), },
111 'incorrection' => { 'text' => $locale->text('correction_br'), },
112 'found' => { 'text' => $locale->text('found_br'), },
113 'insum' => { 'text' => $locale->text('sum'), },
114 'back' => { 'text' => $locale->text('back_br'), },
115 'outcorrection' => { 'text' => $locale->text('correction_br'), },
116 'disposed' => { 'text' => $locale->text('disposed_br'), },
117 'missing' => { 'text' => $locale->text('missing_br'), },
118 'shipped' => { 'text' => $locale->text('shipped_br'), },
119 'used' => { 'text' => $locale->text('used_br'), },
120 'outsum' => { 'text' => $locale->text('sum'), },
121 'consumed' => { 'text' => $locale->text('consumed'), },
122 'averconsumed' => { 'text' => $locale->text('averconsumed_br'), },
126 map { $column_defs{$_}->{visible} = 1 } @columns;
127 #map { $column_defs{$_}->{visible} = $form->{"l_${_}"} ? 1 : 0 } @columns;
128 map { $column_defs{$_}->{align} = 'right' } @numcolumns;
130 my @custom_headers = ();
132 push @custom_headers, [
133 { 'text' => $locale->text('Part'),
134 'colspan' => ($form->{report_generator_output_format} eq 'HTML'?4:2), 'align' => 'center'},
135 { 'text' => $locale->text('Into bin'), 'colspan' => 4, 'align' => 'center'},
136 { 'text' => $locale->text('From bin'), 'colspan' => 7, 'align' => 'center'},
137 { 'text' => $locale->text('UsageWithout'), 'colspan' => 2, 'align' => 'center'},
142 map { push @line_2 , $column_defs{$_} } @columns;
143 push @custom_headers, [ @line_2 ];
145 $report->set_custom_headers(@custom_headers);
146 $report->set_columns( %column_defs );
147 $report->set_column_order(@columns);
149 $report->set_export_options('usage', @hidden_variables );
151 $report->set_sort_indicator($form->{sort}, $form->{order});
152 $report->set_options('output_format' => 'HTML',
153 'controller_class' => 'Inventory',
154 'title' => $form->{title},
155 # 'html_template' => 'inventory/usage_report',
156 'attachment_basename' => strftime($locale->text('warehouse_usage_list') . '_%Y%m%d', localtime time));
157 $report->set_options_from_form;
161 # reporttype = custom
165 my $start = DateTime->now_local;
166 my $end = DateTime->now_local;
167 my $actualepoch = $end->epoch();
170 $searchparams{reporttype} = $form->{reporttype};
171 if ($form->{reporttype} eq "custom") {
176 #forgotten the year --> thisyear
177 if ($form->{year} !~ m/^\d\d\d\d$/) {
178 $locale->date(\%myconfig, $form->current_date(\%myconfig), 0) =~
182 my $leapday = ($form->{year} % 4 == 0) ? 1:0;
184 if ($form->{duetyp} eq "13") {
189 if ($form->{duetyp} eq "A") {
191 $days = 90 + $leapday;
193 if ($form->{duetyp} eq "B") {
199 if ($form->{duetyp} eq "C") {
205 if ($form->{duetyp} eq "D") {
210 if ($form->{duetyp} eq "1" || $form->{duetyp} eq "3" || $form->{duetyp} eq "5" ||
211 $form->{duetyp} eq "7" || $form->{duetyp} eq "8" || $form->{duetyp} eq "10" ||
212 $form->{duetyp} eq "12") {
213 $smon = $emon = $form->{duetyp}*1;
216 if ($form->{duetyp} eq "2" || $form->{duetyp} eq "4" || $form->{duetyp} eq "6" ||
217 $form->{duetyp} eq "9" || $form->{duetyp} eq "11" ) {
218 $smon = $emon = $form->{duetyp}*1;
220 if ($form->{duetyp} eq "2" ) {
221 #this works from 1901 to 2099, 1900 and 2100 fail.
222 $eday = ($form->{year} % 4 == 0) ? 29 : 28;
224 $mdays=$days = $eday;
226 $searchparams{year} = $form->{year};
227 $searchparams{duetyp} = $form->{duetyp};
228 $start->set_month($smon);
229 $start->set_day($sday);
230 $start->set_year($form->{year}*1);
231 $end->set_month($emon);
232 $end->set_day($eday);
233 $end->set_year($form->{year}*1);
235 $searchparams{fromdate} = $form->{fromdate};
236 $searchparams{todate} = $form->{todate};
238 # fromdate = 01.01.2014
239 # todate = 31.05.2014
240 my ($yy, $mm, $dd) = $locale->parse_date(\%myconfig,$form->{fromdate});
241 $start->set_year($yy);
242 $start->set_month($mm);
243 $start->set_day($dd);
244 ($yy, $mm, $dd) = $locale->parse_date(\%myconfig,$form->{todate});
246 $end->set_month($mm);
248 my $dur = $start->delta_md($end);
249 $days = $dur->delta_months()*30 + $dur->delta_days() ;
251 $start->set_second(0);
252 $start->set_minute(0);
254 $end->set_second(59);
255 $end->set_minute(59);
257 if ( $end->epoch() > $actualepoch ) {
258 $end = DateTime->now_local;
259 my $dur = $start->delta_md($end);
260 $days = $dur->delta_months()*30 + $dur->delta_days() ;
262 if ( $start->epoch() > $end->epoch() ) { $start = $end;$days = 1;}
263 $days = $mdays if $days < $mdays;
264 #$main::lxdebug->message(LXDebug->DEBUG2(), "start=".$start->epoch());
265 #$main::lxdebug->message(LXDebug->DEBUG2(), " end=".$end->epoch());
266 #$main::lxdebug->message(LXDebug->DEBUG2(), " days=".$days);
267 my @andfilter = (shippingdate => { ge => $start }, shippingdate => { le => $end } );
268 if ( $form->{warehouse_id} ) {
269 push @andfilter , ( warehouse_id => $form->{warehouse_id});
270 $searchparams{warehouse_id} = $form->{warehouse_id};
271 if ( $form->{bin_id} ) {
272 push @andfilter , ( bin_id => $form->{bin_id});
273 $searchparams{bin_id} = $form->{bin_id};
276 # alias class t2 entspricht parts
277 if ( $form->{partnumber} ) {
278 push @andfilter , ( 't2.partnumber' => { ilike => '%'. $form->{partnumber} .'%' });
279 $searchparams{partnumber} = $form->{partnumber};
281 if ( $form->{description} ) {
282 push @andfilter , ( 't2.description' => { ilike => '%'. $form->{description} .'%' });
283 $searchparams{description} = $form->{description};
285 if ( $form->{bestbefore} ) {
286 push @andfilter , ( bestbefore => { eq => $form->{bestbefore} });
287 $searchparams{bestbefore} = $form->{bestbefore};
289 if ( $form->{chargenumber} ) {
290 push @andfilter , ( chargenumber => { ilike => '%'.$form->{chargenumber}.'%' });
291 $searchparams{chargenumber} = $form->{chargenumber};
293 if ( $form->{partstypes_id} ) {
294 push @andfilter , ( 't2.partstypes_id' => $form->{partstypes_id} );
295 $searchparams{partstypes_id} = $form->{partstypes_id};
298 my @filter = (and => [ @andfilter ] );
300 my $objs = SL::DB::Manager::Inventory->get_all(with_objects => ['parts'], where => [ @filter ] , sort_by => 'parts.partnumber ASC');
301 #my $objs = SL::DB::Inventory->_get_manager_class->get_all(...);
303 # manual paginating, yuck
304 my $page = $::form->{page} || 1;
306 $pages->{per_page} = $::form->{per_page} || 20;
307 my $first_nr = ($page - 1) * $pages->{per_page};
308 my $last_nr = $first_nr + $pages->{per_page};
314 $allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
315 #$main::lxdebug->message(LXDebug->DEBUG2(), "first_nr=".$first_nr." last_nr=".$last_nr);
316 foreach my $entry (@{ $objs } ) {
317 if ( $entry->parts_id != $last_partid ) {
318 if ( $last_partid > 0 ) {
319 if ( $allrows || ($row_ind >= $first_nr && $row_ind < $last_nr )) {
320 $self->make_row_result($last_row,$days,$last_partid);
321 $report->add_data($last_row);
325 $last_partid = $entry->parts_id;
327 $last_row->{partnumber}->{data} = $entry->part->partnumber;
328 $last_row->{partdescription}->{data} = $entry->part->description;
329 $last_row->{unit}->{data} = $entry->part->unit;
330 $last_row->{stock}->{data} = 0;
331 $last_row->{incorrection}->{data} = 0;
332 $last_row->{found}->{data} = 0;
333 $last_row->{back}->{data} = 0;
334 $last_row->{outcorrection}->{data} = 0;
335 $last_row->{disposed}->{data} = 0;
336 $last_row->{missing}->{data} = 0;
337 $last_row->{shipped}->{data} = 0;
338 $last_row->{used}->{data} = 0;
339 $last_row->{insum}->{data} = 0;
340 $last_row->{outsum}->{data} = 0;
341 $last_row->{consumed}->{data} = 0;
342 $last_row->{averconsumed}->{data} = 0;
344 if ( !$allrows && $row_ind >= $last_nr ) {
348 if ( $entry->trans_type->description eq 'correction' ) {
349 $prefix = $entry->trans_type->direction;
351 $last_row->{$prefix.$entry->trans_type->description}->{data} +=
352 ( $entry->trans_type->direction eq 'out' ? -$entry->qty : $entry->qty );
354 if ( $last_partid > 0 && ( $allrows || ($row_ind >= $first_nr && $row_ind < $last_nr ))) {
355 $self->make_row_result($last_row,$days,$last_partid);
356 $report->add_data($last_row);
359 my $num_rows = @{ $report->{data} } ;
360 #$main::lxdebug->message(LXDebug->DEBUG2(), "count=".$row_ind." rows=".$num_rows);
363 $pages->{max} = SL::DB::Helper::Paginated::ceil($row_ind, $pages->{per_page}) || 1;
364 $pages->{page} = $page < 1 ? 1: $page > $pages->{max} ? $pages->{max}: $page;
365 $pages->{common} = [ grep { $_->{visible} } @{ SL::DB::Helper::Paginated::make_common_pages($pages->{page}, $pages->{max}) } ];
366 $self->{pages} = $pages;
367 $searchparams{action} = "usage";
368 $self->{base_url} = $self->url_for(\%searchparams );
369 #$main::lxdebug->message(LXDebug->DEBUG2(), "page=".$pages->{page}." url=".$self->{base_url});
371 $report->set_options('raw_bottom_info_text' => $self->render('inventory/report_bottom', { output => 0 }) );
373 $report->generate_with_headers();
375 $main::lxdebug->leave_sub();
379 sub make_row_result {
380 my ($self,$row,$days,$partid) = @_;
381 my $form = $main::form;
382 my $myconfig = \%main::myconfig;
384 $row->{insum}->{data} = $row->{stock}->{data} + $row->{incorrection}->{data} + $row->{found}->{data};
385 $row->{outsum}->{data} = $row->{back}->{data} + $row->{outcorrection}->{data} + $row->{disposed}->{data} +
386 $row->{missing}->{data} + $row->{shipped}->{data} + $row->{used}->{data};
387 $row->{consumed}->{data} = $row->{outsum}->{data} -
388 $row->{outcorrection}->{data} - $row->{incorrection}->{data};
389 $row->{averconsumed}->{data} = $row->{consumed}->{data}*30/$days ;
390 map { $row->{$_}->{data} = $form->format_amount($myconfig,$row->{$_}->{data},2); } $self->getnumcolumns();
391 $row->{partnumber}->{link} = 'controller.pl?action=Part/edit&part.id' . $partid;
398 my $qty = $::form->parse_amount(\%::myconfig, $::form->{qty});
400 $transfer_error = t8('Cannot stock without amount');
402 $transfer_error = t8('Cannot stock negative amounts');
405 $::form->throw_on_error(sub {
408 parts => $self->part,
409 dst_bin => $self->bin,
410 dst_wh => $self->warehouse,
413 transfer_type => 'stock',
414 transfer_type_id => $::form->{transfer_type_id},
415 chargenumber => $::form->{chargenumber},
416 bestbefore => $::form->{bestbefore},
417 comment => $::form->{comment},
420 } or do { $transfer_error = $EVAL_ERROR->error; }
423 if (!$transfer_error) {
424 if ($::form->{write_default_bin}) {
425 $self->part->load; # onhand is calculated in between. don't mess that up
426 $self->part->bin($self->bin);
427 $self->part->warehouse($self->warehouse);
431 flash_later('info', t8('Transfer successful'));
435 my %additional_redirect_params = ();
436 if ($transfer_error) {
437 flash_later('error', $transfer_error);
438 $additional_redirect_params{$_} = $::form->{$_} for qw(qty chargenumber bestbefore ean comment);
439 $additional_redirect_params{qty} = $qty;
444 action => 'stock_in',
445 part_id => $self->part->id,
446 bin_id => $self->bin->id,
447 warehouse_id => $self->warehouse->id,
448 unit_id => $self->unit->id,
449 %additional_redirect_params,
453 sub action_part_changed {
456 # no standard? ask user if he wants to write it
457 if ($self->part->id && !$self->part->bin_id && !$self->part->warehouse_id) {
458 $self->js->show('#write_default_bin_span');
460 $self->js->hide('#write_default_bin_span')
461 ->removeAttr('#write_default_bin', 'checked');
465 ->replaceWith('#warehouse_id', $self->build_warehouse_select)
466 ->replaceWith('#bin_id', $self->build_bin_select)
467 ->replaceWith('#unit_id', $self->build_unit_select)
468 ->focus('#warehouse_id')
472 sub action_warehouse_changed {
476 ->replaceWith('#bin_id', $self->build_bin_select)
481 sub action_mini_stock {
485 ->html('#stock', $self->render('inventory/_stock', { output => 0 }))
489 sub action_stocktaking {
492 $::request->{layout}->use_javascript("${_}.js") for qw(kivi.Inventory);
493 $::request->layout->focus('#part_id_name');
494 $self->setup_stock_stocktaking_action_bar;
495 $self->render('inventory/stocktaking/form', title => t8('Stocktaking'));
498 sub action_save_stocktaking {
501 return $self->js->flash('error', t8('Please choose a part.'))->render()
502 if !$::form->{part_id};
504 return $self->js->flash('error', t8('A target quantitiy has to be given'))->render()
505 if $::form->{target_qty} eq '';
507 my $target_qty = $::form->parse_amount(\%::myconfig, $::form->{target_qty});
509 return $self->js->flash('error', t8('Error: A negative target quantity is not allowed.'))->render()
512 my $stocked_qty = _get_stocked_qty($self->part,
513 warehouse_id => $self->warehouse->id,
514 bin_id => $self->bin->id,
515 chargenumber => $::form->{chargenumber},
516 bestbefore => $::form->{bestbefore},);
518 my $stocked_qty_in_form_units = $self->part->unit_obj->convert_to($stocked_qty, $self->unit);
520 if (!$::form->{dont_check_already_counted}) {
521 my $already_counted = _already_counted($self->part,
522 warehouse_id => $self->warehouse->id,
523 bin_id => $self->bin->id,
524 cutoff_date => $::form->{cutoff_date_as_date},
525 chargenumber => $::form->{chargenumber},
526 bestbefore => $::form->{bestbefore});
527 if (scalar @$already_counted) {
528 my $reply = $self->js->dialog->open({
529 html => $self->render('inventory/stocktaking/_already_counted_dialog',
531 already_counted => $already_counted,
532 stocked_qty => $stocked_qty,
533 stocked_qty_in_form_units => $stocked_qty_in_form_units),
534 id => 'already_counted_dialog',
536 title => t8('Already counted'),
544 # - target_qty is in units given in form ($self->unit)
545 # - WH->transfer expects qtys in given unit (here: unit from form (unit -> $self->unit))
546 # Therefore use stocked_qty in form units for calculation.
547 my $qty = $target_qty - $stocked_qty_in_form_units;
548 my $src_or_dst = $qty < 0? 'src' : 'dst';
553 $::form->throw_on_error(sub {
556 parts => $self->part,
557 $src_or_dst.'_bin' => $self->bin,
558 $src_or_dst.'_wh' => $self->warehouse,
561 transfer_type => 'stocktaking',
562 chargenumber => $::form->{chargenumber},
563 bestbefore => $::form->{bestbefore},
564 ean => $::form->{ean},
565 comment => $::form->{comment},
566 record_stocktaking => 1,
567 stocktaking_qty => $target_qty,
568 stocktaking_cutoff_date => $::form->{cutoff_date_as_date},
571 } or do { $transfer_error = $EVAL_ERROR->error; }
574 return $self->js->flash('error', $transfer_error)->render()
577 flash_later('info', $::locale->text('Part successful counted'));
578 $self->redirect_to(action => 'stocktaking',
579 warehouse_id => $self->warehouse->id,
580 bin_id => $self->bin->id,
581 cutoff_date_as_date => $self->stocktaking_cutoff_date->to_kivitendo);
584 sub action_reload_stocktaking_history {
587 $::form->{filter}{'cutoff_date:date'} = $self->stocktaking_cutoff_date->to_kivitendo;
588 $::form->{filter}{'employee_id'} = SL::DB::Manager::Employee->current->id;
590 $self->prepare_stocktaking_report;
591 $self->report_generator_list_objects(report => $self->{report}, objects => $self->stocktaking_models->get, layout => 0, header => 0);
594 sub action_stocktaking_part_changed {
598 ->replaceWith('#unit_id', $self->build_unit_select)
599 ->focus('#target_qty')
603 sub action_stocktaking_journal {
606 $self->prepare_stocktaking_report(full => 1);
607 $self->report_generator_list_objects(report => $self->{report}, objects => $self->stocktaking_models->get);
610 sub action_stocktaking_get_warn_qty_threshold {
613 return $_[0]->render(\ !!0, { type => 'text' }) if !$::form->{part_id};
614 return $_[0]->render(\ !!0, { type => 'text' }) if $::form->{target_qty} eq '';
615 return $_[0]->render(\ !!0, { type => 'text' }) if 0 == $::instance_conf->get_stocktaking_qty_threshold;
617 my $target_qty = $::form->parse_amount(\%::myconfig, $::form->{target_qty});
618 my $stocked_qty = _get_stocked_qty($self->part,
619 warehouse_id => $self->warehouse->id,
620 bin_id => $self->bin->id,
621 chargenumber => $::form->{chargenumber},
622 bestbefore => $::form->{bestbefore},);
623 my $stocked_qty_in_form_units = $self->part->unit_obj->convert_to($stocked_qty, $self->unit);
624 my $qty = $target_qty - $stocked_qty_in_form_units;
628 if ($qty > $::instance_conf->get_stocktaking_qty_threshold) {
629 $warn = t8('The target quantity of #1 differs more than the threshold quantity of #2.',
630 $::form->{target_qty} . " " . $self->unit->name,
631 $::form->format_amount(\%::myconfig, $::instance_conf->get_stocktaking_qty_threshold, 2));
633 $warn .= t8('Choose "continue" if you want to use this value. Choose "cancel" otherwise.');
635 return $_[0]->render(\ $warn, { type => 'text' });
638 #================================================================
641 $main::auth->assert('warehouse_management');
644 sub _check_warehouses {
645 $_[0]->show_no_warehouses_error if !@{ $_[0]->warehouses };
648 sub init_warehouses {
649 SL::DB::Manager::Warehouse->get_all(query => [ or => [ invalid => 0, invalid => undef ]]);
653 # SL::DB::Manager::Bin->get_all();
657 SL::DB::Manager::Unit->get_all;
660 sub init_is_stocktaking {
661 return $_[0]->action_name =~ m{stocktaking};
664 sub init_stocktaking_models {
667 SL::Controller::Helper::GetModels->new(
669 model => 'Stocktaking',
675 itime => t8('Insert Date'),
676 qty => t8('Target Qty'),
677 chargenumber => t8('Charge Number'),
678 comment => t8('Comment'),
679 employee => t8('Employee'),
681 partnumber => t8('Part Number'),
682 part => t8('Part Description'),
684 cutoff_date => t8('Cutoff Date'),
686 with_objects => ['employee', 'parts', 'warehouse', 'bin'],
690 sub init_stocktaking_cutoff_date {
693 return DateTime->from_kivitendo($::form->{cutoff_date_as_date}) if $::form->{cutoff_date_as_date};
694 return SL::DB::Default->get->stocktaking_cutoff_date if SL::DB::Default->get->stocktaking_cutoff_date;
696 # Default cutoff date is last day of current year, but if current month
697 # is janurary, it is the last day of the last year.
698 my $now = DateTime->now_local;
699 my $cutoff = DateTime->new(year => $now->year, month => 12, day => 31);
700 if ($now->month < 1) {
701 $cutoff->substract(years => 1);
706 sub set_target_from_part {
709 return if !$self->part;
711 $self->warehouse($self->part->warehouse) if $self->part->warehouse;
712 $self->bin( $self->part->bin) if $self->part->bin;
715 sub sanitize_target {
718 $self->warehouse($self->warehouses->[0]) if !$self->warehouse || !$self->warehouse->id;
719 $self->bin ($self->warehouse->bins->[0]) if !$self->bin || !$self->bin->id;
720 # foreach my $warehouse ( $self->warehouses ) {
721 # $warehouse->{BINS} = [];
722 # foreach my $bin ( $self->bins ) {
723 # if ( $bin->warehouse_id == $warehouse->id ) {
724 # push @{ $warehouse->{BINS} }, $bin;
730 sub load_part_from_form {
731 $_[0]->part(SL::DB::Manager::Part->find_by_or_create(id => $::form->{part_id}||undef));
734 sub load_unit_from_form {
735 $_[0]->unit(SL::DB::Manager::Unit->find_by_or_create(id => $::form->{unit_id}));
738 sub load_wh_from_form {
740 $preselected = SL::DB::Default->get->stocktaking_warehouse_id if $_[0]->is_stocktaking;
742 $_[0]->warehouse(SL::DB::Manager::Warehouse->find_by_or_create(id => ($::form->{warehouse_id} || $preselected)));
745 sub load_bin_from_form {
747 $preselected = SL::DB::Default->get->stocktaking_bin_id if $_[0]->is_stocktaking;
749 $_[0]->bin(SL::DB::Manager::Bin->find_by_or_create(id => ($::form->{bin_id} || $preselected)));
753 $::request->layout->add_javascripts('client_js.js');
756 sub build_warehouse_select {
757 select_tag('warehouse_id', $_[0]->warehouses,
758 title_key => 'description',
759 default => $_[0]->warehouse->id,
760 onchange => 'reload_bin_selection()',
764 sub build_bin_select {
765 select_tag('bin_id', [ $_[0]->warehouse->bins ],
766 title_key => 'description',
767 default => $_[0]->bin->id,
771 sub build_unit_select {
773 ? select_tag('unit_id', $_[0]->part->available_units,
775 default => $_[0]->part->unit_obj->id,
777 : select_tag('unit_id', $_[0]->units,
785 # We want to fetch the last 10 inventory events (inventory rows with the same trans_id)
786 # To prevent a Seq Scan on inventory set an index on inventory.itime
787 # Each event may have one (transfer_in/out) or two (transfer) inventory rows
788 # So fetch the last 20, group by trans_id, limit to the last 10 trans_ids,
789 # and then extract the inventory ids from those 10 trans_ids
790 # By querying Inventory->get_all via the id instead of trans_id we can make
791 # use of the existing index on id
793 # inventory ids of the most recent 10 inventory trans_ids
795 with last_inventories as (
806 from last_inventories
813 limit 20 -- so the planner knows how many ids to expect, the cte is an optimisation fence
816 my $objs = SL::DB::Manager::Inventory->get_all(
817 query => [ id => [ \"$query" ] ],
818 with_objects => [ 'parts', 'trans_type', 'bin', 'bin.warehouse' ], # prevent lazy loading in template
819 sort_by => 'itime DESC',
821 # remember order of trans_ids from query, for ordering hash later
822 my @sorted_trans_ids = uniq map { $_->trans_id } @$objs;
824 # at most 2 of them belong to a transaction and the qty determines in or out.
827 $transactions{ $_->trans_id }{ $_->qty > 0 ? 'in' : 'out' } = $_;
828 $transactions{ $_->trans_id }{base} = $_;
831 # because the inventory transactions were built in a hash, we need to sort the
832 # hash by using the original sort order of the trans_ids
833 my @sorted = map { $transactions{$_} } @sorted_trans_ids;
841 my $stock = $self->part->get_simple_stock;
842 $self->{stock_by_bin} = { map { $_->{bin_id} => $_ } @$stock };
843 $self->{stock_empty} = ! grep { $_->{sum} * 1 } @$stock;
846 sub show_no_warehouses_error {
849 my $msg = t8('No warehouse has been created yet or the quantity of the bins is not configured yet.') . ' ';
851 if ($::auth->check_right($::myconfig{login}, 'config')) { # TODO wut?
852 $msg .= t8('You can create warehouses and bins via the menu "System -> Warehouses".');
854 $msg .= t8('Please ask your administrator to create warehouses and bins.');
856 $::form->show_generic_error($msg);
859 sub prepare_stocktaking_report {
860 my ($self, %params) = @_;
862 my $callback = $self->stocktaking_models->get_callback;
864 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
865 $self->{report} = $report;
867 my @columns = qw(itime employee ean partnumber part qty unit bin chargenumber comment cutoff_date);
868 my @sortable = qw(itime employee ean partnumber part qty bin chargenumber comment cutoff_date);
871 itime => { sub => sub { $_[0]->itime_as_timestamp },
872 text => t8('Insert Date'), },
873 employee => { sub => sub { $_[0]->employee->safe_name },
874 text => t8('Employee'), },
875 ean => { sub => sub { $_[0]->part->ean },
876 text => t8('EAN'), },
877 partnumber => { sub => sub { $_[0]->part->partnumber },
878 text => t8('Part Number'), },
879 part => { sub => sub { $_[0]->part->description },
880 text => t8('Part Description'), },
881 qty => { sub => sub { $_[0]->qty_as_number },
882 text => t8('Target Qty'),
884 unit => { sub => sub { $_[0]->part->unit },
885 text => t8('Unit'), },
886 bin => { sub => sub { $_[0]->bin->full_description },
887 text => t8('Bin'), },
888 chargenumber => { text => t8('Charge Number'), },
889 comment => { text => t8('Comment'), },
890 cutoff_date => { sub => sub { $_[0]->cutoff_date_as_date },
891 text => t8('Cutoff Date'), },
894 $report->set_options(
895 std_column_visibility => 1,
896 controller_class => 'Inventory',
897 output_format => 'HTML',
898 title => (!!$params{full})? $::locale->text('Stocktaking Journal') : $::locale->text('Stocktaking History'),
899 allow_pdf_export => !!$params{full},
900 allow_csv_export => !!$params{full},
902 $report->set_columns(%column_defs);
903 $report->set_column_order(@columns);
904 $report->set_export_options(qw(stocktaking_journal filter));
905 $report->set_options_from_form;
906 $self->stocktaking_models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
907 $self->stocktaking_models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable) if !!$params{full};
908 if (!!$params{full}) {
909 $report->set_options(
910 raw_top_info_text => $self->render('inventory/stocktaking/full_report_top', { output => 0 }),
913 $report->set_options(
914 raw_bottom_info_text => $self->render('inventory/stocktaking/report_bottom', { output => 0 }),
918 sub _get_stocked_qty {
919 my ($part, %params) = @_;
921 my $bestbefore_filter = '';
922 my $bestbefore_val_cnt = 0;
923 if ($::instance_conf->get_show_bestbefore) {
924 $bestbefore_filter = ($params{bestbefore}) ? 'AND bestbefore = ?' : 'AND bestbefore IS NULL';
925 $bestbefore_val_cnt = ($params{bestbefore}) ? 1 : 0;
929 SELECT sum(qty) FROM inventory
930 WHERE parts_id = ? AND warehouse_id = ? AND bin_id = ? AND chargenumber = ? $bestbefore_filter
931 GROUP BY warehouse_id, bin_id, chargenumber
934 my @values = ($part->id,
935 $params{warehouse_id},
937 $params{chargenumber});
938 push @values, $params{bestbefore} if $bestbefore_val_cnt;
940 my ($stocked_qty) = selectrow_query($::form, $::form->get_standard_dbh, $query, @values);
942 return 1*($stocked_qty || 0);
945 sub _already_counted {
946 my ($part, %params) = @_;
948 my %bestbefore_filter;
949 if ($::instance_conf->get_show_bestbefore) {
950 %bestbefore_filter = (bestbefore => $params{bestbefore});
953 SL::DB::Manager::Stocktaking->get_all(query => [and => [parts_id => $part->id,
954 warehouse_id => $params{warehouse_id},
955 bin_id => $params{bin_id},
956 cutoff_date => $params{cutoff_date},
957 chargenumber => $params{chargenumber},
958 %bestbefore_filter]],
959 sort_by => ['itime DESC']);
962 sub setup_stock_in_action_bar {
963 my ($self, %params) = @_;
965 for my $bar ($::request->layout->get('actionbar')) {
969 submit => [ '#form', { action => 'Inventory/stock' } ],
970 checks => [ 'check_part_selection_before_stocking' ],
971 accesskey => 'enter',
977 sub setup_stock_usage_action_bar {
978 my ($self, %params) = @_;
980 for my $bar ($::request->layout->get('actionbar')) {
984 submit => [ '#form', { action => 'Inventory/usage' } ],
985 accesskey => 'enter',
991 sub setup_stock_stocktaking_action_bar {
992 my ($self, %params) = @_;
994 for my $bar ($::request->layout->get('actionbar')) {
998 checks => [ 'kivi.Inventory.check_stocktaking_qty_threshold' ],
999 call => [ 'kivi.Inventory.save_stocktaking' ],
1000 accesskey => 'enter',
1013 SL::Controller::Inventory - Controller for inventory
1017 This controller handles stock in, stocktaking and reports about inventory
1018 in warehouses/stocks
1024 - warehouse withdrawal
1030 Stocktaking allows to document the counted quantities of parts during
1031 stocktaking for a certain cutoff date. Differences between counted and stocked
1032 quantities are corrected in the stock. The transfer type 'stocktacking' is set
1035 After picking a part, the mini stock for this part is displayed. At the bottom
1036 of the form a history of already counted parts for the current employee and the
1037 choosen cutoff date is shown.
1039 Warehouse, bin and cutoff date canbe preselected in the client configuration.
1041 If a part was already counted for this cutoff date, warehouse and bin, a warning
1042 is displayed, allowing the user to choose to add the counted quantity to the
1043 stocked one or to take his counted quantity as the new stocked quantity.
1045 There is also a journal of stocktakings.
1047 Templates are located under C<templates/webpages/inventory/stocktaking>.
1048 JavaScript functions can be found in C<js/kivi.Inventory.js>.
1054 =item C<action_stock_usage>
1056 Create a search form for stock withdrawal.
1057 The search parameter for report are made like the reports in bin/mozilla/rp.pl
1059 =item C<action_usage>
1061 Make a report about stock withdrawal.
1063 The manual pagination is implemented like the pagination in SL::Controller::CsvImport.
1065 =item C<action_stocktaking>
1067 This action renders the input form for stocktaking.
1069 =item C<action_save_stocktaking>
1071 This action saves the stocktaking values and corrects the stock after checking
1072 if the part is already counted for this warehouse, bin and cutoff date.
1073 For saving SL::WH->transfer is called.
1075 =item C<action_reload_stocktaking_history>
1077 This action is responsible for displaying the stocktaking history at the bottom
1078 of the form. It uses the stocktaking journal with fixed filters for cutoff date
1079 and the current employee. The history is displayed via javascript.
1081 =item C<action_stocktaking_part_changed>
1083 This action is called after the user selected or changed the part.
1085 =item C<action_stocktaking_get_warn_qty_threshold>
1087 This action checks if a warning should be shown and returns the warning text via
1088 ajax. The warning will be shown if the given target value is greater than the
1089 threshold given in the client configuration.
1091 =item C<is_stocktaking>
1093 This is a method to check if actions are called from stocktaking form.
1094 This actions should contain "stocktaking" in their name.
1098 =head1 SPECIAL CASES
1100 Because of the PFD-Table Formatter some parameters for PDF must be different to the HTML parameters.
1101 So in german language there are some tries to use a HTML Break in the second heading line
1102 to produce two line heading inside table. The actual version has some abbreviations for the header texts.
1106 The PDF-Table library has some limits (doesn't display all if the line is to large) so
1107 the format is adapted to this
1114 =item only for C<action_stock_usage> and C<action_usage>:
1116 Martin Helmling E<lt>martin.helmling@opendynamic.deE<gt>
1118 =item for stocktaking:
1120 Bernd Bleßmann E<lt>bernd@kivitendo-premium.deE<gt>