Inventory Controller - Datenbankoptimierungen für mini_journal
[kivitendo-erp.git] / SL / Controller / Inventory.pm
1 package SL::Controller::Inventory;
2
3 use strict;
4 use warnings;
5 use POSIX qw(strftime);
6
7 use parent qw(SL::Controller::Base);
8
9 use SL::DB::Inventory;
10 use SL::DB::Stocktaking;
11 use SL::DB::Part;
12 use SL::DB::Warehouse;
13 use SL::DB::Unit;
14 use SL::DB::Default;
15 use SL::WH;
16 use SL::ReportGenerator;
17 use SL::Locale::String qw(t8);
18 use SL::Presenter::Tag qw(select_tag);
19 use SL::DBUtils;
20 use SL::Helper::Flash;
21 use SL::Controller::Helper::ReportGenerator;
22 use SL::Controller::Helper::GetModels;
23 use List::MoreUtils qw(uniq);
24
25 use English qw(-no_match_vars);
26
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) ],
30 );
31
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');
42
43 sub action_stock_in {
44   my ($self) = @_;
45
46   $::form->{title}   = t8('Stock');
47
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 );
53 }
54
55 sub action_stock_usage {
56   my ($self) = @_;
57
58   $::form->{title}   = t8('UsageE');
59
60   $::form->get_lists('warehouses' => { 'key'    => 'WAREHOUSES',
61                                        'bins'   => 'BINS', });
62
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,
69                 warehouse_id => 0,
70                 bin_id => 0
71       );
72
73 }
74
75 sub getnumcolumns {
76   my ($self) = @_;
77   return qw(stock incorrection found insum back outcorrection disposed
78                      missing shipped used outsum consumed averconsumed);
79 }
80
81 sub action_usage {
82   my ($self) = @_;
83
84   $main::lxdebug->enter_sub();
85
86   my $form     = $main::form;
87   my %myconfig = %main::myconfig;
88   my $locale   = $main::locale;
89
90   $form->{title}   = t8('UsageE');
91   $form->{report_generator_output_format} = 'HTML' if !$form->{report_generator_output_format};
92
93   my $report = SL::ReportGenerator->new(\%myconfig, $form);
94
95   my @columns = qw(partnumber partdescription);
96
97   push @columns , qw(ptype unit) if $form->{report_generator_output_format} eq 'HTML';
98
99   my @numcolumns = qw(stock incorrection found insum back outcorrection disposed
100                      missing shipped used outsum consumed averconsumed);
101
102   push @columns , $self->getnumcolumns();
103
104   my @hidden_variables = qw(reporttype year duetyp fromdate todate
105                             warehouse_id bin_id partnumber description bestbefore chargenumber partstypes_id);
106   my %column_defs = (
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'), },
123   );
124
125
126   map { $column_defs{$_}->{visible} = 1 } @columns;
127   #map { $column_defs{$_}->{visible} = $form->{"l_${_}"} ? 1 : 0 } @columns;
128   map { $column_defs{$_}->{align} = 'right' } @numcolumns;
129
130   my @custom_headers = ();
131   # Zeile 1:
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'},
138   ];
139
140   # Zeile 2:
141   my @line_2 = ();
142   map { push @line_2 , $column_defs{$_} } @columns;
143   push @custom_headers, [ @line_2 ];
144
145   $report->set_custom_headers(@custom_headers);
146   $report->set_columns( %column_defs );
147   $report->set_column_order(@columns);
148
149   $report->set_export_options('usage', @hidden_variables );
150
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;
158
159   my %searchparams ;
160 # form vars
161 #   reporttype = custom
162 #   year = 2014
163 #   duetyp = 7
164
165   my $start       = DateTime->now_local;
166   my $end         = DateTime->now_local;
167   my $actualepoch = $end->epoch();
168   my $days = 365;
169   my $mdays=30;
170   $searchparams{reporttype} = $form->{reporttype};
171   if ($form->{reporttype} eq "custom") {
172     my $smon = 1;
173     my $emon = 12;
174     my $sday = 1;
175     my $eday = 31;
176     #forgotten the year --> thisyear
177     if ($form->{year} !~ m/^\d\d\d\d$/) {
178       $locale->date(\%myconfig, $form->current_date(\%myconfig), 0) =~
179         /(\d\d\d\d)/;
180       $form->{year} = $1;
181     }
182     my $leapday = ($form->{year} % 4 == 0) ? 1:0;
183     #yearly report
184     if ($form->{duetyp} eq "13") {
185         $days += $leapday;
186     }
187
188     #Quater reports
189     if ($form->{duetyp} eq "A") {
190       $emon = 3;
191       $days = 90 + $leapday;
192     }
193     if ($form->{duetyp} eq "B") {
194       $smon = 4;
195       $emon = 6;
196       $eday = 30;
197       $days = 91;
198     }
199     if ($form->{duetyp} eq "C") {
200       $smon = 7;
201       $emon = 9;
202       $eday = 30;
203       $days = 92;
204     }
205     if ($form->{duetyp} eq "D") {
206       $smon = 10;
207       $days = 92;
208     }
209     #Monthly reports
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;
214         $mdays=$days = 31;
215     }
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;
219         $eday = 30;
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;
223         }
224         $mdays=$days = $eday;
225     }
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);
234   }  else {
235     $searchparams{fromdate} = $form->{fromdate};
236     $searchparams{todate} = $form->{todate};
237 #   reporttype = free
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});
245     $end->set_year($yy);
246     $end->set_month($mm);
247     $end->set_day($dd);
248     my $dur = $start->delta_md($end);
249     $days = $dur->delta_months()*30 + $dur->delta_days() ;
250   }
251   $start->set_second(0);
252   $start->set_minute(0);
253   $start->set_hour(0);
254   $end->set_second(59);
255   $end->set_minute(59);
256   $end->set_hour(23);
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() ;
261   }
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};
274       }
275   }
276   # alias class t2 entspricht parts
277   if ( $form->{partnumber} ) {
278       push @andfilter , ( 't2.partnumber' => { ilike => '%'. $form->{partnumber} .'%' });
279       $searchparams{partnumber} = $form->{partnumber};
280   }
281   if ( $form->{description} ) {
282       push @andfilter , ( 't2.description' => { ilike => '%'. $form->{description} .'%'  });
283       $searchparams{description} = $form->{description};
284   }
285   if ( $form->{bestbefore} ) {
286     push @andfilter , ( bestbefore => { eq => $form->{bestbefore} });
287       $searchparams{bestbefore} = $form->{bestbefore};
288   }
289   if ( $form->{chargenumber} ) {
290       push @andfilter , ( chargenumber => { ilike => '%'.$form->{chargenumber}.'%' });
291       $searchparams{chargenumber} = $form->{chargenumber};
292   }
293   if ( $form->{partstypes_id} ) {
294       push @andfilter , ( 't2.partstypes_id' => $form->{partstypes_id} );
295       $searchparams{partstypes_id} = $form->{partstypes_id};
296   }
297
298   my @filter = (and => [ @andfilter ] );
299
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(...);
302
303   # manual paginating, yuck
304   my $page = $::form->{page} || 1;
305   my $pages = {};
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};
309
310   my $last_partid = 0;
311   my $last_row = { };
312   my $row_ind = 0;
313   my $allrows = 0;
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);
322               }
323               $row_ind++ ;
324           }
325           $last_partid = $entry->parts_id;
326           $last_row = { };
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;
343       }
344       if ( !$allrows && $row_ind >= $last_nr ) {
345           next;
346       }
347       my $prefix='';
348       if ( $entry->trans_type->description eq 'correction' ) {
349           $prefix = $entry->trans_type->direction;
350       }
351       $last_row->{$prefix.$entry->trans_type->description}->{data} +=
352           ( $entry->trans_type->direction eq 'out' ? -$entry->qty : $entry->qty );
353   }
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);
357       $row_ind++ ;
358   }
359   my $num_rows = @{ $report->{data} } ;
360   #$main::lxdebug->message(LXDebug->DEBUG2(), "count=".$row_ind." rows=".$num_rows);
361
362   if ( ! $allrows ) {
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});
370
371       $report->set_options('raw_bottom_info_text' => $self->render('inventory/report_bottom', { output => 0 }) );
372   }
373   $report->generate_with_headers();
374
375   $main::lxdebug->leave_sub();
376
377 }
378
379 sub make_row_result {
380   my ($self,$row,$days,$partid) = @_;
381   my $form     = $main::form;
382   my $myconfig = \%main::myconfig;
383
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;
392 }
393
394 sub action_stock {
395   my ($self) = @_;
396
397   my $transfer_error;
398   my $qty = $::form->parse_amount(\%::myconfig, $::form->{qty});
399   if (!$qty) {
400     $transfer_error = t8('Cannot stock without amount');
401   } elsif ($qty < 0) {
402     $transfer_error = t8('Cannot stock negative amounts');
403   } else {
404     # do stock
405     $::form->throw_on_error(sub {
406       eval {
407         WH->transfer({
408           parts         => $self->part,
409           dst_bin       => $self->bin,
410           dst_wh        => $self->warehouse,
411           qty           => $qty,
412           unit          => $self->unit,
413           transfer_type => 'stock',
414           transfer_type_id => $::form->{transfer_type_id},
415           chargenumber  => $::form->{chargenumber},
416           bestbefore    => $::form->{bestbefore},
417           comment       => $::form->{comment},
418         });
419         1;
420       } or do { $transfer_error = $EVAL_ERROR->error; }
421     });
422
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);
428         $self->part->save;
429       }
430
431       flash_later('info', t8('Transfer successful'));
432     }
433   }
434
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;
440   }
441
442   # redirect
443   $self->redirect_to(
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,
450   );
451 }
452
453 sub action_part_changed {
454   my ($self) = @_;
455
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');
459   } else {
460     $self->js->hide('#write_default_bin_span')
461              ->removeAttr('#write_default_bin', 'checked');
462   }
463
464   $self->js
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')
469     ->render;
470 }
471
472 sub action_warehouse_changed {
473   my ($self) = @_;
474
475   $self->js
476     ->replaceWith('#bin_id', $self->build_bin_select)
477     ->focus('#bin_id')
478     ->render;
479 }
480
481 sub action_mini_stock {
482   my ($self) = @_;
483
484   $self->js
485     ->html('#stock', $self->render('inventory/_stock', { output => 0 }))
486     ->render;
487 }
488
489 sub action_stocktaking {
490   my ($self) = @_;
491
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'));
496 }
497
498 sub action_save_stocktaking {
499   my ($self) = @_;
500
501   return $self->js->flash('error', t8('Please choose a part.'))->render()
502     if !$::form->{part_id};
503
504   return $self->js->flash('error', t8('A target quantitiy has to be given'))->render()
505     if $::form->{target_qty} eq '';
506
507   my $target_qty = $::form->parse_amount(\%::myconfig, $::form->{target_qty});
508
509   return $self->js->flash('error', t8('Error: A negative target quantity is not allowed.'))->render()
510     if $target_qty < 0;
511
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},);
517
518   my $stocked_qty_in_form_units = $self->part->unit_obj->convert_to($stocked_qty, $self->unit);
519
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',
530                                 { output => 0 },
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',
535         dialog => {
536           title => t8('Already counted'),
537         },
538       })->render;
539
540       return $reply;
541     }
542   }
543
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';
549   $qty           = abs($qty);
550
551   my $transfer_error;
552   # do stock
553   $::form->throw_on_error(sub {
554     eval {
555       WH->transfer({
556         parts                   => $self->part,
557         $src_or_dst.'_bin'      => $self->bin,
558         $src_or_dst.'_wh'       => $self->warehouse,
559         qty                     => $qty,
560         unit                    => $self->unit,
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},
569       });
570       1;
571     } or do { $transfer_error = $EVAL_ERROR->error; }
572   });
573
574   return $self->js->flash('error', $transfer_error)->render()
575     if $transfer_error;
576
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);
582 }
583
584 sub action_reload_stocktaking_history {
585   my ($self) = @_;
586
587   $::form->{filter}{'cutoff_date:date'} = $self->stocktaking_cutoff_date->to_kivitendo;
588   $::form->{filter}{'employee_id'}      = SL::DB::Manager::Employee->current->id;
589
590   $self->prepare_stocktaking_report;
591   $self->report_generator_list_objects(report => $self->{report}, objects => $self->stocktaking_models->get, layout => 0, header => 0);
592 }
593
594 sub action_stocktaking_part_changed {
595   my ($self) = @_;
596
597   $self->js
598     ->replaceWith('#unit_id', $self->build_unit_select)
599     ->focus('#target_qty')
600     ->render;
601 }
602
603 sub action_stocktaking_journal {
604   my ($self) = @_;
605
606   $self->prepare_stocktaking_report(full => 1);
607   $self->report_generator_list_objects(report => $self->{report}, objects => $self->stocktaking_models->get);
608 }
609
610 sub action_stocktaking_get_warn_qty_threshold {
611   my ($self) = @_;
612
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;
616
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;
625   $qty           = abs($qty);
626
627   my $warn;
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));
632     $warn .= "\n";
633     $warn .= t8('Choose "continue" if you want to use this value. Choose "cancel" otherwise.');
634   }
635   return $_[0]->render(\ $warn, { type => 'text' });
636 }
637
638 #================================================================
639
640 sub _check_auth {
641   $main::auth->assert('warehouse_management');
642 }
643
644 sub _check_warehouses {
645   $_[0]->show_no_warehouses_error if !@{ $_[0]->warehouses };
646 }
647
648 sub init_warehouses {
649   SL::DB::Manager::Warehouse->get_all(query => [ or => [ invalid => 0, invalid => undef ]]);
650 }
651
652 #sub init_bins {
653 #  SL::DB::Manager::Bin->get_all();
654 #}
655
656 sub init_units {
657   SL::DB::Manager::Unit->get_all;
658 }
659
660 sub init_is_stocktaking {
661   return $_[0]->action_name =~ m{stocktaking};
662 }
663
664 sub init_stocktaking_models {
665   my ($self) = @_;
666
667   SL::Controller::Helper::GetModels->new(
668     controller   => $self,
669     model        => 'Stocktaking',
670     sorted       => {
671       _default => {
672         by    => 'itime',
673         dir   => 0,
674       },
675       itime        => t8('Insert Date'),
676       qty          => t8('Target Qty'),
677       chargenumber => t8('Charge Number'),
678       comment      => t8('Comment'),
679       employee     => t8('Employee'),
680       ean          => t8('EAN'),
681       partnumber   => t8('Part Number'),
682       part         => t8('Part Description'),
683       bin          => t8('Bin'),
684       cutoff_date  => t8('Cutoff Date'),
685     },
686     with_objects => ['employee', 'parts', 'warehouse', 'bin'],
687   );
688 }
689
690 sub init_stocktaking_cutoff_date {
691   my ($self) = @_;
692
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;
695
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);
702   }
703   return $cutoff;
704 }
705
706 sub set_target_from_part {
707   my ($self) = @_;
708
709   return if !$self->part;
710
711   $self->warehouse($self->part->warehouse) if $self->part->warehouse;
712   $self->bin(      $self->part->bin)       if $self->part->bin;
713 }
714
715 sub sanitize_target {
716   my ($self) = @_;
717
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;
725 #         }
726 #      }
727 #  }
728 }
729
730 sub load_part_from_form {
731   $_[0]->part(SL::DB::Manager::Part->find_by_or_create(id => $::form->{part_id}||undef));
732 }
733
734 sub load_unit_from_form {
735   $_[0]->unit(SL::DB::Manager::Unit->find_by_or_create(id => $::form->{unit_id}));
736 }
737
738 sub load_wh_from_form {
739   my $preselected;
740   $preselected = SL::DB::Default->get->stocktaking_warehouse_id if $_[0]->is_stocktaking;
741
742   $_[0]->warehouse(SL::DB::Manager::Warehouse->find_by_or_create(id => ($::form->{warehouse_id} || $preselected)));
743 }
744
745 sub load_bin_from_form {
746   my $preselected;
747   $preselected = SL::DB::Default->get->stocktaking_bin_id if $_[0]->is_stocktaking;
748
749   $_[0]->bin(SL::DB::Manager::Bin->find_by_or_create(id => ($::form->{bin_id} || $preselected)));
750 }
751
752 sub set_layout {
753   $::request->layout->add_javascripts('client_js.js');
754 }
755
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()',
761   )
762 }
763
764 sub build_bin_select {
765   select_tag('bin_id', [ $_[0]->warehouse->bins ],
766     title_key => 'description',
767     default   => $_[0]->bin->id,
768   );
769 }
770
771 sub build_unit_select {
772   $_[0]->part->id
773     ? select_tag('unit_id', $_[0]->part->available_units,
774         title_key => 'name',
775         default   => $_[0]->part->unit_obj->id,
776       )
777     : select_tag('unit_id', $_[0]->units,
778         title_key => 'name',
779       )
780 }
781
782 sub mini_journal {
783   my ($self) = @_;
784
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
792
793   # inventory ids of the most recent 10 inventory trans_ids
794   my $query = <<SQL;
795 with last_inventories as (
796    select id,
797           trans_id,
798           itime
799      from inventory
800  order by itime desc
801     limit 20
802 ),
803 grouped_ids as (
804    select trans_id,
805           array_agg(id) as ids
806      from last_inventories
807  group by trans_id
808  order by max(itime)
809      desc limit 10
810 )
811 select unnest(ids)
812   from grouped_ids
813  limit 20  -- so the planner knows how many ids to expect, the cte is an optimisation fence
814 SQL
815
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',
820   );
821   # remember order of trans_ids from query, for ordering hash later
822   my @sorted_trans_ids = uniq map { $_->trans_id } @$objs;
823
824   # at most 2 of them belong to a transaction and the qty determines in or out.
825   my %transactions;
826   for (@$objs) {
827     $transactions{ $_->trans_id }{ $_->qty > 0 ? 'in' : 'out' } = $_;
828     $transactions{ $_->trans_id }{base} = $_;
829   }
830
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;
834
835   return \@sorted;
836 }
837
838 sub mini_stock {
839   my ($self) = @_;
840
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;
844 }
845
846 sub show_no_warehouses_error {
847   my ($self) = @_;
848
849   my $msg = t8('No warehouse has been created yet or the quantity of the bins is not configured yet.') . ' ';
850
851   if ($::auth->check_right($::myconfig{login}, 'config')) { # TODO wut?
852     $msg .= t8('You can create warehouses and bins via the menu "System -> Warehouses".');
853   } else {
854     $msg .= t8('Please ask your administrator to create warehouses and bins.');
855   }
856   $::form->show_generic_error($msg);
857 }
858
859 sub prepare_stocktaking_report {
860   my ($self, %params) = @_;
861
862   my $callback    = $self->stocktaking_models->get_callback;
863
864   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
865   $self->{report} = $report;
866
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);
869
870   my %column_defs = (
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'),
883                          align => 'right', },
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'), },
892   );
893
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},
901   );
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 }),
911     );
912   }
913   $report->set_options(
914     raw_bottom_info_text => $self->render('inventory/stocktaking/report_bottom',   { output => 0 }),
915   );
916 }
917
918 sub _get_stocked_qty {
919   my ($part, %params) = @_;
920
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;
926   }
927
928   my $query = <<SQL;
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
932 SQL
933
934   my @values = ($part->id,
935                 $params{warehouse_id},
936                 $params{bin_id},
937                 $params{chargenumber});
938   push @values, $params{bestbefore} if $bestbefore_val_cnt;
939
940   my ($stocked_qty) = selectrow_query($::form, $::form->get_standard_dbh, $query, @values);
941
942   return 1*($stocked_qty || 0);
943 }
944
945 sub _already_counted {
946   my ($part, %params) = @_;
947
948   my %bestbefore_filter;
949   if ($::instance_conf->get_show_bestbefore) {
950     %bestbefore_filter = (bestbefore => $params{bestbefore});
951   }
952
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']);
960 }
961
962 sub setup_stock_in_action_bar {
963   my ($self, %params) = @_;
964
965   for my $bar ($::request->layout->get('actionbar')) {
966     $bar->add(
967       action => [
968         t8('Stock'),
969         submit    => [ '#form', { action => 'Inventory/stock' } ],
970         checks    => [ 'check_part_selection_before_stocking' ],
971         accesskey => 'enter',
972       ],
973     );
974   }
975 }
976
977 sub setup_stock_usage_action_bar {
978   my ($self, %params) = @_;
979
980   for my $bar ($::request->layout->get('actionbar')) {
981     $bar->add(
982       action => [
983         t8('Show'),
984         submit    => [ '#form', { action => 'Inventory/usage' } ],
985         accesskey => 'enter',
986       ],
987     );
988   }
989 }
990
991 sub setup_stock_stocktaking_action_bar {
992   my ($self, %params) = @_;
993
994   for my $bar ($::request->layout->get('actionbar')) {
995     $bar->add(
996       action => [
997         t8('Save'),
998         checks    => [ 'kivi.Inventory.check_stocktaking_qty_threshold' ],
999         call      => [ 'kivi.Inventory.save_stocktaking' ],
1000         accesskey => 'enter',
1001       ],
1002     );
1003   }
1004 }
1005
1006 1;
1007 __END__
1008
1009 =encoding utf-8
1010
1011 =head1 NAME
1012
1013 SL::Controller::Inventory - Controller for inventory
1014
1015 =head1 DESCRIPTION
1016
1017 This controller handles stock in, stocktaking and reports about inventory
1018 in warehouses/stocks
1019
1020 - warehouse content
1021
1022 - warehouse journal
1023
1024 - warehouse withdrawal
1025
1026 - stocktaking
1027
1028 =head2 Stocktaking
1029
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
1033 here.
1034
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.
1038
1039 Warehouse, bin and cutoff date canbe preselected in the client configuration.
1040
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.
1044
1045 There is also a journal of stocktakings.
1046
1047 Templates are located under C<templates/webpages/inventory/stocktaking>.
1048 JavaScript functions can be found in C<js/kivi.Inventory.js>.
1049
1050 =head1 FUNCTIONS
1051
1052 =over 4
1053
1054 =item C<action_stock_usage>
1055
1056 Create a search form for stock withdrawal.
1057 The search parameter for report are made like the reports in bin/mozilla/rp.pl
1058
1059 =item C<action_usage>
1060
1061 Make a report about stock withdrawal.
1062
1063 The manual pagination is implemented like the pagination in SL::Controller::CsvImport.
1064
1065 =item C<action_stocktaking>
1066
1067 This action renders the input form for stocktaking.
1068
1069 =item C<action_save_stocktaking>
1070
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.
1074
1075 =item C<action_reload_stocktaking_history>
1076
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.
1080
1081 =item C<action_stocktaking_part_changed>
1082
1083 This action is called after the user selected or changed the part.
1084
1085 =item C<action_stocktaking_get_warn_qty_threshold>
1086
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.
1090
1091 =item C<is_stocktaking>
1092
1093 This is a method to check if actions are called from stocktaking form.
1094 This actions should contain "stocktaking" in their name.
1095
1096 =back
1097
1098 =head1 SPECIAL CASES
1099
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.
1103
1104 =head1 BUGS
1105
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
1108
1109
1110 =head1 AUTHOR
1111
1112 =over 4
1113
1114 =item only for C<action_stock_usage> and C<action_usage>:
1115
1116 Martin Helmling E<lt>martin.helmling@opendynamic.deE<gt>
1117
1118 =item for stocktaking:
1119
1120 Bernd Bleßmann E<lt>bernd@kivitendo-premium.deE<gt>
1121
1122 =back
1123
1124 =cut