marei: headline/colspec keys for SimpleTabular
[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   # Sometimes we want to open stock_in with a part already selected, but only
49   # the parts_id is passed in the url (and not also warehouse, bin and unit).
50   # Setting select_default_bin in the form will make sure the default warehouse
51   # and bin of that part will already be preselected, as normally
52   # set_target_from_part is only called when a part is changed.
53   $self->set_target_from_part if $::form->{select_default_bin};
54   $::request->layout->focus('#part_id_name');
55   my $transfer_types = WH->retrieve_transfer_types('in');
56   map { $_->{description} = $main::locale->text($_->{description}) } @{ $transfer_types };
57   $self->setup_stock_in_action_bar;
58   $self->render('inventory/warehouse_selection_stock', title => $::form->{title}, TRANSFER_TYPES => $transfer_types );
59 }
60
61 sub action_stock_usage {
62   my ($self) = @_;
63
64   $::form->{title}   = t8('UsageE');
65
66   $::form->get_lists('warehouses' => { 'key'    => 'WAREHOUSES',
67                                        'bins'   => 'BINS', });
68
69   $self->setup_stock_usage_action_bar;
70   $self->render('inventory/warehouse_usage',
71                 title => $::form->{title},
72                 year => DateTime->today->year,
73                 WAREHOUSES => $::form->{WAREHOUSES},
74                 WAREHOUSE_FILTER => 1,
75                 warehouse_id => 0,
76                 bin_id => 0
77       );
78
79 }
80
81 sub getnumcolumns {
82   my ($self) = @_;
83   return qw(stock incorrection found insum back outcorrection disposed
84                      missing shipped used outsum consumed averconsumed);
85 }
86
87 sub action_usage {
88   my ($self) = @_;
89
90   $main::lxdebug->enter_sub();
91
92   my $form     = $main::form;
93   my %myconfig = %main::myconfig;
94   my $locale   = $main::locale;
95
96   $form->{title}   = t8('UsageE');
97   $form->{report_generator_output_format} = 'HTML' if !$form->{report_generator_output_format};
98
99   my $report = SL::ReportGenerator->new(\%myconfig, $form);
100
101   my @columns = qw(partnumber partdescription);
102
103   push @columns , qw(ptype unit) if $form->{report_generator_output_format} eq 'HTML';
104
105   my @numcolumns = qw(stock incorrection found insum back outcorrection disposed
106                      missing shipped used outsum consumed averconsumed);
107
108   push @columns , $self->getnumcolumns();
109
110   my @hidden_variables = qw(reporttype year duetyp fromdate todate
111                             warehouse_id bin_id partnumber description bestbefore chargenumber partstypes_id);
112   my %column_defs = (
113     'partnumber'      => { 'text' => $locale->text('Part Number'), },
114     'partdescription' => { 'text' => $locale->text('Part_br_Description'), },
115     'unit'            => { 'text' => $locale->text('Unit'), },
116     'stock'           => { 'text' => $locale->text('stock_br'), },
117     'incorrection'    => { 'text' => $locale->text('correction_br'), },
118     'found'           => { 'text' => $locale->text('found_br'), },
119     'insum'           => { 'text' => $locale->text('sum'), },
120     'back'            => { 'text' => $locale->text('back_br'), },
121     'outcorrection'   => { 'text' => $locale->text('correction_br'), },
122     'disposed'        => { 'text' => $locale->text('disposed_br'), },
123     'missing'         => { 'text' => $locale->text('missing_br'), },
124     'shipped'         => { 'text' => $locale->text('shipped_br'), },
125     'used'            => { 'text' => $locale->text('used_br'), },
126     'outsum'          => { 'text' => $locale->text('sum'), },
127     'consumed'        => { 'text' => $locale->text('consumed'), },
128     'averconsumed'    => { 'text' => $locale->text('averconsumed_br'), },
129   );
130
131
132   map { $column_defs{$_}->{visible} = 1 } @columns;
133   #map { $column_defs{$_}->{visible} = $form->{"l_${_}"} ? 1 : 0 } @columns;
134   map { $column_defs{$_}->{align} = 'right' } @numcolumns;
135
136   my @custom_headers = ();
137   # Zeile 1:
138   push @custom_headers, [
139       { 'text' => $locale->text('Part'),
140         'colspan' => ($form->{report_generator_output_format} eq 'HTML'?4:2), 'align' => 'center'},
141       { 'text' => $locale->text('Into bin'), 'colspan' => 4, 'align' => 'center'},
142       { 'text' => $locale->text('From bin'), 'colspan' => 7, 'align' => 'center'},
143       { 'text' => $locale->text('UsageWithout'),    'colspan' => 2, 'align' => 'center'},
144   ];
145
146   # Zeile 2:
147   my @line_2 = ();
148   map { push @line_2 , $column_defs{$_} } @columns;
149   push @custom_headers, [ @line_2 ];
150
151   $report->set_custom_headers(@custom_headers);
152   $report->set_columns( %column_defs );
153   $report->set_column_order(@columns);
154
155   $report->set_export_options('usage', @hidden_variables );
156
157   $report->set_sort_indicator($form->{sort}, $form->{order});
158   $report->set_options('output_format'        => 'HTML',
159                        'controller_class'     => 'Inventory',
160                        'title'                => $form->{title},
161 #                      'html_template'        => 'inventory/usage_report',
162                        'attachment_basename'  => strftime($locale->text('warehouse_usage_list') . '_%Y%m%d', localtime time));
163   $report->set_options_from_form;
164
165   my %searchparams ;
166 # form vars
167 #   reporttype = custom
168 #   year = 2014
169 #   duetyp = 7
170
171   my $start       = DateTime->now_local;
172   my $end         = DateTime->now_local;
173   my $actualepoch = $end->epoch();
174   my $days = 365;
175   my $mdays=30;
176   $searchparams{reporttype} = $form->{reporttype};
177   if ($form->{reporttype} eq "custom") {
178     my $smon = 1;
179     my $emon = 12;
180     my $sday = 1;
181     my $eday = 31;
182     #forgotten the year --> thisyear
183     if ($form->{year} !~ m/^\d\d\d\d$/) {
184       $locale->date(\%myconfig, $form->current_date(\%myconfig), 0) =~
185         /(\d\d\d\d)/;
186       $form->{year} = $1;
187     }
188     my $leapday = ($form->{year} % 4 == 0) ? 1:0;
189     #yearly report
190     if ($form->{duetyp} eq "13") {
191         $days += $leapday;
192     }
193
194     #Quater reports
195     if ($form->{duetyp} eq "A") {
196       $emon = 3;
197       $days = 90 + $leapday;
198     }
199     if ($form->{duetyp} eq "B") {
200       $smon = 4;
201       $emon = 6;
202       $eday = 30;
203       $days = 91;
204     }
205     if ($form->{duetyp} eq "C") {
206       $smon = 7;
207       $emon = 9;
208       $eday = 30;
209       $days = 92;
210     }
211     if ($form->{duetyp} eq "D") {
212       $smon = 10;
213       $days = 92;
214     }
215     #Monthly reports
216     if ($form->{duetyp} eq "1" || $form->{duetyp} eq "3" || $form->{duetyp} eq "5" ||
217         $form->{duetyp} eq "7" || $form->{duetyp} eq "8" || $form->{duetyp} eq "10" ||
218         $form->{duetyp} eq "12") {
219         $smon = $emon = $form->{duetyp}*1;
220         $mdays=$days = 31;
221     }
222     if ($form->{duetyp} eq "2" || $form->{duetyp} eq "4" || $form->{duetyp} eq "6" ||
223         $form->{duetyp} eq "9" || $form->{duetyp} eq "11" ) {
224         $smon = $emon = $form->{duetyp}*1;
225         $eday = 30;
226         if ($form->{duetyp} eq "2" ) {
227             #this works from 1901 to 2099, 1900 and 2100 fail.
228             $eday = ($form->{year} % 4 == 0) ? 29 : 28;
229         }
230         $mdays=$days = $eday;
231     }
232     $searchparams{year} = $form->{year};
233     $searchparams{duetyp} = $form->{duetyp};
234     $start->set_month($smon);
235     $start->set_day($sday);
236     $start->set_year($form->{year}*1);
237     $end->set_month($emon);
238     $end->set_day($eday);
239     $end->set_year($form->{year}*1);
240   }  else {
241     $searchparams{fromdate} = $form->{fromdate};
242     $searchparams{todate} = $form->{todate};
243 #   reporttype = free
244 #   fromdate = 01.01.2014
245 #   todate = 31.05.2014
246     my ($yy, $mm, $dd) = $locale->parse_date(\%myconfig,$form->{fromdate});
247     $start->set_year($yy);
248     $start->set_month($mm);
249     $start->set_day($dd);
250     ($yy, $mm, $dd) = $locale->parse_date(\%myconfig,$form->{todate});
251     $end->set_year($yy);
252     $end->set_month($mm);
253     $end->set_day($dd);
254     my $dur = $start->delta_md($end);
255     $days = $dur->delta_months()*30 + $dur->delta_days() ;
256   }
257   $start->set_second(0);
258   $start->set_minute(0);
259   $start->set_hour(0);
260   $end->set_second(59);
261   $end->set_minute(59);
262   $end->set_hour(23);
263   if ( $end->epoch() > $actualepoch ) {
264       $end = DateTime->now_local;
265       my $dur = $start->delta_md($end);
266       $days = $dur->delta_months()*30 + $dur->delta_days() ;
267   }
268   if ( $start->epoch() > $end->epoch() ) { $start = $end;$days = 1;}
269   $days = $mdays if $days < $mdays;
270   #$main::lxdebug->message(LXDebug->DEBUG2(), "start=".$start->epoch());
271   #$main::lxdebug->message(LXDebug->DEBUG2(), "  end=".$end->epoch());
272   #$main::lxdebug->message(LXDebug->DEBUG2(), " days=".$days);
273   my @andfilter = (shippingdate => { ge => $start }, shippingdate => { le => $end } );
274   if ( $form->{warehouse_id} ) {
275       push @andfilter , ( warehouse_id => $form->{warehouse_id});
276       $searchparams{warehouse_id} = $form->{warehouse_id};
277       if ( $form->{bin_id} ) {
278           push @andfilter , ( bin_id => $form->{bin_id});
279           $searchparams{bin_id} = $form->{bin_id};
280       }
281   }
282   # alias class t2 entspricht parts
283   if ( $form->{partnumber} ) {
284       push @andfilter , ( 't2.partnumber' => { ilike => '%'. $form->{partnumber} .'%' });
285       $searchparams{partnumber} = $form->{partnumber};
286   }
287   if ( $form->{description} ) {
288       push @andfilter , ( 't2.description' => { ilike => '%'. $form->{description} .'%'  });
289       $searchparams{description} = $form->{description};
290   }
291   if ( $form->{bestbefore} ) {
292     push @andfilter , ( bestbefore => { eq => $form->{bestbefore} });
293       $searchparams{bestbefore} = $form->{bestbefore};
294   }
295   if ( $form->{chargenumber} ) {
296       push @andfilter , ( chargenumber => { ilike => '%'.$form->{chargenumber}.'%' });
297       $searchparams{chargenumber} = $form->{chargenumber};
298   }
299   if ( $form->{partstypes_id} ) {
300       push @andfilter , ( 't2.partstypes_id' => $form->{partstypes_id} );
301       $searchparams{partstypes_id} = $form->{partstypes_id};
302   }
303
304   my @filter = (and => [ @andfilter ] );
305
306   my $objs = SL::DB::Manager::Inventory->get_all(with_objects => ['parts'], where => [ @filter ] , sort_by => 'parts.partnumber ASC');
307   #my $objs = SL::DB::Inventory->_get_manager_class->get_all(...);
308
309   # manual paginating, yuck
310   my $page = $::form->{page} || 1;
311   my $pages = {};
312   $pages->{per_page}        = $::form->{per_page} || 20;
313   my $first_nr = ($page - 1) * $pages->{per_page};
314   my $last_nr  = $first_nr + $pages->{per_page};
315
316   my $last_partid = 0;
317   my $last_row = { };
318   my $row_ind = 0;
319   my $allrows = 0;
320   $allrows = 1 if $form->{report_generator_output_format} ne 'HTML' ;
321   #$main::lxdebug->message(LXDebug->DEBUG2(), "first_nr=".$first_nr." last_nr=".$last_nr);
322   foreach my $entry (@{ $objs } ) {
323       if ( $entry->parts_id != $last_partid ) {
324           if ( $last_partid > 0 ) {
325               if ( $allrows || ($row_ind >= $first_nr && $row_ind < $last_nr )) {
326                   $self->make_row_result($last_row,$days,$last_partid);
327                   $report->add_data($last_row);
328               }
329               $row_ind++ ;
330           }
331           $last_partid = $entry->parts_id;
332           $last_row = { };
333           $last_row->{partnumber}->{data} = $entry->part->partnumber;
334           $last_row->{partdescription}->{data} = $entry->part->description;
335           $last_row->{unit}->{data} = $entry->part->unit;
336           $last_row->{stock}->{data} = 0;
337           $last_row->{incorrection}->{data} = 0;
338           $last_row->{found}->{data} = 0;
339           $last_row->{back}->{data} = 0;
340           $last_row->{outcorrection}->{data} = 0;
341           $last_row->{disposed}->{data} = 0;
342           $last_row->{missing}->{data} = 0;
343           $last_row->{shipped}->{data} = 0;
344           $last_row->{used}->{data} = 0;
345           $last_row->{insum}->{data} = 0;
346           $last_row->{outsum}->{data} = 0;
347           $last_row->{consumed}->{data} = 0;
348           $last_row->{averconsumed}->{data} = 0;
349       }
350       if ( !$allrows && $row_ind >= $last_nr ) {
351           next;
352       }
353       my $prefix='';
354       if ( $entry->trans_type->description eq 'correction' ) {
355           $prefix = $entry->trans_type->direction;
356       }
357       $last_row->{$prefix.$entry->trans_type->description}->{data} +=
358           ( $entry->trans_type->direction eq 'out' ? -$entry->qty : $entry->qty );
359   }
360   if ( $last_partid > 0 && ( $allrows || ($row_ind >= $first_nr && $row_ind < $last_nr ))) {
361       $self->make_row_result($last_row,$days,$last_partid);
362       $report->add_data($last_row);
363       $row_ind++ ;
364   }
365   my $num_rows = @{ $report->{data} } ;
366   #$main::lxdebug->message(LXDebug->DEBUG2(), "count=".$row_ind." rows=".$num_rows);
367
368   if ( ! $allrows ) {
369       $pages->{max}  = SL::DB::Helper::Paginated::ceil($row_ind, $pages->{per_page}) || 1;
370       $pages->{page} = $page < 1 ? 1: $page > $pages->{max} ? $pages->{max}: $page;
371       $pages->{common} = [ grep { $_->{visible} } @{ SL::DB::Helper::Paginated::make_common_pages($pages->{page}, $pages->{max}) } ];
372       $self->{pages} = $pages;
373       $searchparams{action} = "usage";
374       $self->{base_url} = $self->url_for(\%searchparams );
375       #$main::lxdebug->message(LXDebug->DEBUG2(), "page=".$pages->{page}." url=".$self->{base_url});
376
377       $report->set_options('raw_bottom_info_text' => $self->render('inventory/report_bottom', { output => 0 }) );
378   }
379   $report->generate_with_headers();
380
381   $main::lxdebug->leave_sub();
382
383 }
384
385 sub make_row_result {
386   my ($self,$row,$days,$partid) = @_;
387   my $form     = $main::form;
388   my $myconfig = \%main::myconfig;
389
390   $row->{insum}->{data}  = $row->{stock}->{data} + $row->{incorrection}->{data} + $row->{found}->{data};
391   $row->{outsum}->{data} = $row->{back}->{data} + $row->{outcorrection}->{data} + $row->{disposed}->{data} +
392        $row->{missing}->{data} + $row->{shipped}->{data} + $row->{used}->{data};
393   $row->{consumed}->{data} = $row->{outsum}->{data} -
394        $row->{outcorrection}->{data} - $row->{incorrection}->{data};
395   $row->{averconsumed}->{data} = $row->{consumed}->{data}*30/$days ;
396   map { $row->{$_}->{data} = $form->format_amount($myconfig,$row->{$_}->{data},2); } $self->getnumcolumns();
397   $row->{partnumber}->{link} = 'controller.pl?action=Part/edit&part.id' . $partid;
398 }
399
400 sub action_stock {
401   my ($self) = @_;
402
403   my $transfer_error;
404   my $qty = $::form->parse_amount(\%::myconfig, $::form->{qty});
405   if (!$qty) {
406     $transfer_error = t8('Cannot stock without amount');
407   } elsif ($qty < 0) {
408     $transfer_error = t8('Cannot stock negative amounts');
409   } else {
410     # do stock
411     $::form->throw_on_error(sub {
412       eval {
413         WH->transfer({
414           parts         => $self->part,
415           dst_bin       => $self->bin,
416           dst_wh        => $self->warehouse,
417           qty           => $qty,
418           unit          => $self->unit,
419           transfer_type => 'stock',
420           transfer_type_id => $::form->{transfer_type_id},
421           chargenumber  => $::form->{chargenumber},
422           bestbefore    => $::form->{bestbefore},
423           comment       => $::form->{comment},
424         });
425         1;
426       } or do { $transfer_error = $EVAL_ERROR->error; }
427     });
428
429     if (!$transfer_error) {
430       if ($::form->{write_default_bin}) {
431         $self->part->load;   # onhand is calculated in between. don't mess that up
432         $self->part->bin($self->bin);
433         $self->part->warehouse($self->warehouse);
434         $self->part->save;
435       }
436
437       flash_later('info', t8('Transfer successful'));
438     }
439   }
440
441   my %additional_redirect_params = ();
442   if ($transfer_error) {
443     flash_later('error', $transfer_error);
444     $additional_redirect_params{$_}  = $::form->{$_} for qw(qty chargenumber bestbefore ean comment);
445     $additional_redirect_params{qty} = $qty;
446   }
447
448   # redirect
449   $self->redirect_to(
450     action       => 'stock_in',
451     part_id      => $self->part->id,
452     bin_id       => $self->bin->id,
453     warehouse_id => $self->warehouse->id,
454     unit_id      => $self->unit->id,
455     %additional_redirect_params,
456   );
457 }
458
459 sub action_part_changed {
460   my ($self) = @_;
461
462   # no standard? ask user if he wants to write it
463   if ($self->part->id && !$self->part->bin_id && !$self->part->warehouse_id) {
464     $self->js->show('#write_default_bin_span');
465   } else {
466     $self->js->hide('#write_default_bin_span')
467              ->removeAttr('#write_default_bin', 'checked');
468   }
469
470   $self->js
471     ->replaceWith('#warehouse_id', $self->build_warehouse_select)
472     ->replaceWith('#bin_id', $self->build_bin_select)
473     ->replaceWith('#unit_id', $self->build_unit_select)
474     ->focus('#warehouse_id')
475     ->render;
476 }
477
478 sub action_warehouse_changed {
479   my ($self) = @_;
480
481   $self->js
482     ->replaceWith('#bin_id', $self->build_bin_select)
483     ->focus('#bin_id')
484     ->render;
485 }
486
487 sub action_mini_stock {
488   my ($self) = @_;
489
490   $self->js
491     ->html('#stock', $self->render('inventory/_stock', { output => 0 }))
492     ->render;
493 }
494
495 sub action_stocktaking {
496   my ($self) = @_;
497
498   $::request->{layout}->use_javascript("${_}.js") for qw(kivi.Inventory);
499   $::request->layout->focus('#part_id_name');
500   $self->setup_stock_stocktaking_action_bar;
501   $self->render('inventory/stocktaking/form', title => t8('Stocktaking'));
502 }
503
504 sub action_save_stocktaking {
505   my ($self) = @_;
506
507   return $self->js->flash('error', t8('Please choose a part.'))->render()
508     if !$::form->{part_id};
509
510   return $self->js->flash('error', t8('A target quantitiy has to be given'))->render()
511     if $::form->{target_qty} eq '';
512
513   my $target_qty = $::form->parse_amount(\%::myconfig, $::form->{target_qty});
514
515   return $self->js->flash('error', t8('Error: A negative target quantity is not allowed.'))->render()
516     if $target_qty < 0;
517
518   my $stocked_qty  = _get_stocked_qty($self->part,
519                                       warehouse_id => $self->warehouse->id,
520                                       bin_id       => $self->bin->id,
521                                       chargenumber => $::form->{chargenumber},
522                                       bestbefore   => $::form->{bestbefore},);
523
524   my $stocked_qty_in_form_units = $self->part->unit_obj->convert_to($stocked_qty, $self->unit);
525
526   if (!$::form->{dont_check_already_counted}) {
527     my $already_counted = _already_counted($self->part,
528                                            warehouse_id => $self->warehouse->id,
529                                            bin_id       => $self->bin->id,
530                                            cutoff_date  => $::form->{cutoff_date_as_date},
531                                            chargenumber => $::form->{chargenumber},
532                                            bestbefore   => $::form->{bestbefore});
533     if (scalar @$already_counted) {
534       my $reply = $self->js->dialog->open({
535         html   => $self->render('inventory/stocktaking/_already_counted_dialog',
536                                 { output => 0 },
537                                 already_counted           => $already_counted,
538                                 stocked_qty               => $stocked_qty,
539                                 stocked_qty_in_form_units => $stocked_qty_in_form_units),
540         id     => 'already_counted_dialog',
541         dialog => {
542           title => t8('Already counted'),
543         },
544       })->render;
545
546       return $reply;
547     }
548   }
549
550   # - target_qty is in units given in form ($self->unit)
551   # - WH->transfer expects qtys in given unit (here: unit from form (unit -> $self->unit))
552   # Therefore use stocked_qty in form units for calculation.
553   my $qty        = $target_qty - $stocked_qty_in_form_units;
554   my $src_or_dst = $qty < 0? 'src' : 'dst';
555   $qty           = abs($qty);
556
557   my $transfer_error;
558   # do stock
559   $::form->throw_on_error(sub {
560     eval {
561       WH->transfer({
562         parts                   => $self->part,
563         $src_or_dst.'_bin'      => $self->bin,
564         $src_or_dst.'_wh'       => $self->warehouse,
565         qty                     => $qty,
566         unit                    => $self->unit,
567         transfer_type           => 'stocktaking',
568         chargenumber            => $::form->{chargenumber},
569         bestbefore              => $::form->{bestbefore},
570         ean                     => $::form->{ean},
571         comment                 => $::form->{comment},
572         record_stocktaking      => 1,
573         stocktaking_qty         => $target_qty,
574         stocktaking_cutoff_date => $::form->{cutoff_date_as_date},
575       });
576       1;
577     } or do { $transfer_error = $EVAL_ERROR->error; }
578   });
579
580   return $self->js->flash('error', $transfer_error)->render()
581     if $transfer_error;
582
583   flash_later('info', $::locale->text('Part successful counted'));
584   $self->redirect_to(action              => 'stocktaking',
585                      warehouse_id        => $self->warehouse->id,
586                      bin_id              => $self->bin->id,
587                      cutoff_date_as_date => $self->stocktaking_cutoff_date->to_kivitendo);
588 }
589
590 sub action_reload_stocktaking_history {
591   my ($self) = @_;
592
593   $::form->{filter}{'cutoff_date:date'} = $self->stocktaking_cutoff_date->to_kivitendo;
594   $::form->{filter}{'employee_id'}      = SL::DB::Manager::Employee->current->id;
595
596   $self->prepare_stocktaking_report;
597   $self->report_generator_list_objects(report => $self->{report}, objects => $self->stocktaking_models->get, layout => 0, header => 0);
598 }
599
600 sub action_stocktaking_part_changed {
601   my ($self) = @_;
602
603   $self->js
604     ->replaceWith('#unit_id', $self->build_unit_select)
605     ->focus('#target_qty')
606     ->render;
607 }
608
609 sub action_stocktaking_journal {
610   my ($self) = @_;
611
612   $self->prepare_stocktaking_report(full => 1);
613   $self->report_generator_list_objects(report => $self->{report}, objects => $self->stocktaking_models->get);
614 }
615
616 sub action_stocktaking_get_warn_qty_threshold {
617   my ($self) = @_;
618
619   return $_[0]->render(\ !!0, { type => 'text' }) if !$::form->{part_id};
620   return $_[0]->render(\ !!0, { type => 'text' }) if $::form->{target_qty} eq '';
621   return $_[0]->render(\ !!0, { type => 'text' }) if 0 == $::instance_conf->get_stocktaking_qty_threshold;
622
623   my $target_qty  = $::form->parse_amount(\%::myconfig, $::form->{target_qty});
624   my $stocked_qty = _get_stocked_qty($self->part,
625                                      warehouse_id => $self->warehouse->id,
626                                      bin_id       => $self->bin->id,
627                                      chargenumber => $::form->{chargenumber},
628                                      bestbefore   => $::form->{bestbefore},);
629   my $stocked_qty_in_form_units = $self->part->unit_obj->convert_to($stocked_qty, $self->unit);
630   my $qty        = $target_qty - $stocked_qty_in_form_units;
631   $qty           = abs($qty);
632
633   my $warn;
634   if ($qty > $::instance_conf->get_stocktaking_qty_threshold) {
635     $warn  = t8('The target quantity of #1 differs more than the threshold quantity of #2.',
636                 $::form->{target_qty} . " " . $self->unit->name,
637                 $::form->format_amount(\%::myconfig, $::instance_conf->get_stocktaking_qty_threshold, 2));
638     $warn .= "\n";
639     $warn .= t8('Choose "continue" if you want to use this value. Choose "cancel" otherwise.');
640   }
641   return $_[0]->render(\ $warn, { type => 'text' });
642 }
643
644 #================================================================
645
646 sub _check_auth {
647   $main::auth->assert('warehouse_management');
648 }
649
650 sub _check_warehouses {
651   $_[0]->show_no_warehouses_error if !@{ $_[0]->warehouses };
652 }
653
654 sub init_warehouses {
655   SL::DB::Manager::Warehouse->get_all(query => [ or => [ invalid => 0, invalid => undef ]]);
656 }
657
658 #sub init_bins {
659 #  SL::DB::Manager::Bin->get_all();
660 #}
661
662 sub init_units {
663   SL::DB::Manager::Unit->get_all;
664 }
665
666 sub init_is_stocktaking {
667   return $_[0]->action_name =~ m{stocktaking};
668 }
669
670 sub init_stocktaking_models {
671   my ($self) = @_;
672
673   SL::Controller::Helper::GetModels->new(
674     controller   => $self,
675     model        => 'Stocktaking',
676     sorted       => {
677       _default => {
678         by    => 'itime',
679         dir   => 0,
680       },
681       itime        => t8('Insert Date'),
682       qty          => t8('Target Qty'),
683       chargenumber => t8('Charge Number'),
684       comment      => t8('Comment'),
685       employee     => t8('Employee'),
686       ean          => t8('EAN'),
687       partnumber   => t8('Part Number'),
688       part         => t8('Part Description'),
689       bin          => t8('Bin'),
690       cutoff_date  => t8('Cutoff Date'),
691     },
692     with_objects => ['employee', 'parts', 'warehouse', 'bin'],
693   );
694 }
695
696 sub init_stocktaking_cutoff_date {
697   my ($self) = @_;
698
699   return DateTime->from_kivitendo($::form->{cutoff_date_as_date}) if $::form->{cutoff_date_as_date};
700   return SL::DB::Default->get->stocktaking_cutoff_date if SL::DB::Default->get->stocktaking_cutoff_date;
701
702   # Default cutoff date is last day of current year, but if current month
703   # is janurary, it is the last day of the last year.
704   my $now    = DateTime->now_local;
705   my $cutoff = DateTime->new(year => $now->year, month => 12, day => 31);
706   if ($now->month < 1) {
707     $cutoff->substract(years => 1);
708   }
709   return $cutoff;
710 }
711
712 sub set_target_from_part {
713   my ($self) = @_;
714
715   return if !$self->part;
716
717   $self->warehouse($self->part->warehouse) if $self->part->warehouse;
718   $self->bin(      $self->part->bin)       if $self->part->bin;
719 }
720
721 sub sanitize_target {
722   my ($self) = @_;
723
724   $self->warehouse($self->warehouses->[0])       if !$self->warehouse || !$self->warehouse->id;
725   $self->bin      ($self->warehouse->bins->[0])  if !$self->bin       || !$self->bin->id;
726 #  foreach my $warehouse ( $self->warehouses ) {
727 #      $warehouse->{BINS} = [];
728 #      foreach my $bin ( $self->bins ) {
729 #         if ( $bin->warehouse_id == $warehouse->id ) {
730 #             push @{ $warehouse->{BINS} }, $bin;
731 #         }
732 #      }
733 #  }
734 }
735
736 sub load_part_from_form {
737   $_[0]->part(SL::DB::Manager::Part->find_by_or_create(id => $::form->{part_id}||undef));
738 }
739
740 sub load_unit_from_form {
741   $_[0]->unit(SL::DB::Manager::Unit->find_by_or_create(id => $::form->{unit_id}));
742 }
743
744 sub load_wh_from_form {
745   my $preselected;
746   $preselected = SL::DB::Default->get->stocktaking_warehouse_id if $_[0]->is_stocktaking;
747
748   $_[0]->warehouse(SL::DB::Manager::Warehouse->find_by_or_create(id => ($::form->{warehouse_id} || $preselected)));
749 }
750
751 sub load_bin_from_form {
752   my $preselected;
753   $preselected = SL::DB::Default->get->stocktaking_bin_id if $_[0]->is_stocktaking;
754
755   $_[0]->bin(SL::DB::Manager::Bin->find_by_or_create(id => ($::form->{bin_id} || $preselected)));
756 }
757
758 sub set_layout {
759   $::request->layout->add_javascripts('client_js.js');
760 }
761
762 sub build_warehouse_select {
763   select_tag('warehouse_id', $_[0]->warehouses,
764    title_key => 'description',
765    default   => $_[0]->warehouse->id,
766    onchange  => 'reload_bin_selection()',
767   )
768 }
769
770 sub build_bin_select {
771   select_tag('bin_id', [ $_[0]->warehouse->bins ],
772     title_key => 'description',
773     default   => $_[0]->bin->id,
774   );
775 }
776
777 sub build_unit_select {
778   $_[0]->part->id
779     ? select_tag('unit_id', $_[0]->part->available_units,
780         title_key => 'name',
781         default   => $_[0]->part->unit_obj->id,
782       )
783     : select_tag('unit_id', $_[0]->units,
784         title_key => 'name',
785       )
786 }
787
788 sub mini_journal {
789   my ($self) = @_;
790
791   # We want to fetch the last 10 inventory events (inventory rows with the same trans_id)
792   # To prevent a Seq Scan on inventory set an index on inventory.itime
793   # Each event may have one (transfer_in/out) or two (transfer) inventory rows
794   # So fetch the last 20, group by trans_id, limit to the last 10 trans_ids,
795   # and then extract the inventory ids from those 10 trans_ids
796   # By querying Inventory->get_all via the id instead of trans_id we can make
797   # use of the existing index on id
798
799   # inventory ids of the most recent 10 inventory trans_ids
800   my $query = <<SQL;
801 with last_inventories as (
802    select id,
803           trans_id,
804           itime
805      from inventory
806  order by itime desc
807     limit 20
808 ),
809 grouped_ids as (
810    select trans_id,
811           array_agg(id) as ids
812      from last_inventories
813  group by trans_id
814  order by max(itime)
815      desc limit 10
816 )
817 select unnest(ids)
818   from grouped_ids
819  limit 20  -- so the planner knows how many ids to expect, the cte is an optimisation fence
820 SQL
821
822   my $objs  = SL::DB::Manager::Inventory->get_all(
823     query        => [ id => [ \"$query" ] ],                           # " make emacs happy
824     with_objects => [ 'parts', 'trans_type', 'bin', 'bin.warehouse' ], # prevent lazy loading in template
825     sort_by      => 'itime DESC',
826   );
827   # remember order of trans_ids from query, for ordering hash later
828   my @sorted_trans_ids = uniq map { $_->trans_id } @$objs;
829
830   # at most 2 of them belong to a transaction and the qty determines in or out.
831   my %transactions;
832   for (@$objs) {
833     $transactions{ $_->trans_id }{ $_->qty > 0 ? 'in' : 'out' } = $_;
834     $transactions{ $_->trans_id }{base} = $_;
835   }
836
837   # because the inventory transactions were built in a hash, we need to sort the
838   # hash by using the original sort order of the trans_ids
839   my @sorted = map { $transactions{$_} } @sorted_trans_ids;
840
841   return \@sorted;
842 }
843
844 sub mini_stock {
845   my ($self) = @_;
846
847   my $stock             = $self->part->get_simple_stock;
848   $self->{stock_by_bin} = { map { $_->{bin_id} => $_ } @$stock };
849   $self->{stock_empty}  = ! grep { $_->{sum} * 1 } @$stock;
850 }
851
852 sub show_no_warehouses_error {
853   my ($self) = @_;
854
855   my $msg = t8('No warehouse has been created yet or the quantity of the bins is not configured yet.') . ' ';
856
857   if ($::auth->check_right($::myconfig{login}, 'config')) { # TODO wut?
858     $msg .= t8('You can create warehouses and bins via the menu "System -> Warehouses".');
859   } else {
860     $msg .= t8('Please ask your administrator to create warehouses and bins.');
861   }
862   $::form->show_generic_error($msg);
863 }
864
865 sub prepare_stocktaking_report {
866   my ($self, %params) = @_;
867
868   my $callback    = $self->stocktaking_models->get_callback;
869
870   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
871   $self->{report} = $report;
872
873   my @columns     = qw(itime employee ean partnumber part qty unit bin chargenumber comment cutoff_date);
874   my @sortable    = qw(itime employee ean partnumber part qty bin chargenumber comment cutoff_date);
875
876   my %column_defs = (
877     itime           => { sub   => sub { $_[0]->itime_as_timestamp },
878                          text  => t8('Insert Date'), },
879     employee        => { sub   => sub { $_[0]->employee->safe_name },
880                          text  => t8('Employee'), },
881     ean             => { sub   => sub { $_[0]->part->ean },
882                          text  => t8('EAN'), },
883     partnumber      => { sub   => sub { $_[0]->part->partnumber },
884                          text  => t8('Part Number'), },
885     part            => { sub   => sub { $_[0]->part->description },
886                          text  => t8('Part Description'), },
887     qty             => { sub   => sub { $_[0]->qty_as_number },
888                          text  => t8('Target Qty'),
889                          align => 'right', },
890     unit            => { sub   => sub { $_[0]->part->unit },
891                          text  => t8('Unit'), },
892     bin             => { sub   => sub { $_[0]->bin->full_description },
893                          text  => t8('Bin'), },
894     chargenumber    => { text  => t8('Charge Number'), },
895     comment         => { text  => t8('Comment'), },
896     cutoff_date     => { sub   => sub { $_[0]->cutoff_date_as_date },
897                          text  => t8('Cutoff Date'), },
898   );
899
900   $report->set_options(
901     std_column_visibility => 1,
902     controller_class      => 'Inventory',
903     output_format         => 'HTML',
904     title                 => (!!$params{full})? $::locale->text('Stocktaking Journal') : $::locale->text('Stocktaking History'),
905     allow_pdf_export      => !!$params{full},
906     allow_csv_export      => !!$params{full},
907   );
908   $report->set_columns(%column_defs);
909   $report->set_column_order(@columns);
910   $report->set_export_options(qw(stocktaking_journal filter));
911   $report->set_options_from_form;
912   $self->stocktaking_models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
913   $self->stocktaking_models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable) if !!$params{full};
914   if (!!$params{full}) {
915     $report->set_options(
916       raw_top_info_text    => $self->render('inventory/stocktaking/full_report_top', { output => 0 }),
917     );
918   }
919   $report->set_options(
920     raw_bottom_info_text => $self->render('inventory/stocktaking/report_bottom',   { output => 0 }),
921   );
922 }
923
924 sub _get_stocked_qty {
925   my ($part, %params) = @_;
926
927   my $bestbefore_filter  = '';
928   my $bestbefore_val_cnt = 0;
929   if ($::instance_conf->get_show_bestbefore) {
930     $bestbefore_filter  = ($params{bestbefore}) ? 'AND bestbefore = ?' : 'AND bestbefore IS NULL';
931     $bestbefore_val_cnt = ($params{bestbefore}) ? 1                    : 0;
932   }
933
934   my $query = <<SQL;
935     SELECT sum(qty) FROM inventory
936       WHERE parts_id = ? AND warehouse_id = ? AND bin_id = ? AND chargenumber = ? $bestbefore_filter
937       GROUP BY warehouse_id, bin_id, chargenumber
938 SQL
939
940   my @values = ($part->id,
941                 $params{warehouse_id},
942                 $params{bin_id},
943                 $params{chargenumber});
944   push @values, $params{bestbefore} if $bestbefore_val_cnt;
945
946   my ($stocked_qty) = selectrow_query($::form, $::form->get_standard_dbh, $query, @values);
947
948   return 1*($stocked_qty || 0);
949 }
950
951 sub _already_counted {
952   my ($part, %params) = @_;
953
954   my %bestbefore_filter;
955   if ($::instance_conf->get_show_bestbefore) {
956     %bestbefore_filter = (bestbefore => ($params{bestbefore} || undef));
957   }
958
959   SL::DB::Manager::Stocktaking->get_all(query => [and => [parts_id     => $part->id,
960                                                           warehouse_id => $params{warehouse_id},
961                                                           bin_id       => $params{bin_id},
962                                                           cutoff_date  => $params{cutoff_date},
963                                                           chargenumber => $params{chargenumber},
964                                                           %bestbefore_filter]],
965                                         sort_by => ['itime DESC']);
966 }
967
968 sub setup_stock_in_action_bar {
969   my ($self, %params) = @_;
970
971   for my $bar ($::request->layout->get('actionbar')) {
972     $bar->add(
973       action => [
974         t8('Stock'),
975         submit    => [ '#form', { action => 'Inventory/stock' } ],
976         checks    => [ 'check_part_selection_before_stocking' ],
977         accesskey => 'enter',
978       ],
979     );
980   }
981 }
982
983 sub setup_stock_usage_action_bar {
984   my ($self, %params) = @_;
985
986   for my $bar ($::request->layout->get('actionbar')) {
987     $bar->add(
988       action => [
989         t8('Show'),
990         submit    => [ '#form', { action => 'Inventory/usage' } ],
991         accesskey => 'enter',
992       ],
993     );
994   }
995 }
996
997 sub setup_stock_stocktaking_action_bar {
998   my ($self, %params) = @_;
999
1000   for my $bar ($::request->layout->get('actionbar')) {
1001     $bar->add(
1002       action => [
1003         t8('Save'),
1004         checks    => [ 'kivi.Inventory.check_stocktaking_qty_threshold' ],
1005         call      => [ 'kivi.Inventory.save_stocktaking' ],
1006         accesskey => 'enter',
1007       ],
1008     );
1009   }
1010 }
1011
1012 1;
1013 __END__
1014
1015 =encoding utf-8
1016
1017 =head1 NAME
1018
1019 SL::Controller::Inventory - Controller for inventory
1020
1021 =head1 DESCRIPTION
1022
1023 This controller handles stock in, stocktaking and reports about inventory
1024 in warehouses/stocks
1025
1026 - warehouse content
1027
1028 - warehouse journal
1029
1030 - warehouse withdrawal
1031
1032 - stocktaking
1033
1034 =head2 Stocktaking
1035
1036 Stocktaking allows to document the counted quantities of parts during
1037 stocktaking for a certain cutoff date. Differences between counted and stocked
1038 quantities are corrected in the stock. The transfer type 'stocktacking' is set
1039 here.
1040
1041 After picking a part, the mini stock for this part is displayed. At the bottom
1042 of the form a history of already counted parts for the current employee and the
1043 choosen cutoff date is shown.
1044
1045 Warehouse, bin and cutoff date canbe preselected in the client configuration.
1046
1047 If a part was already counted for this cutoff date, warehouse and bin, a warning
1048 is displayed, allowing the user to choose to add the counted quantity to the
1049 stocked one or to take his counted quantity as the new stocked quantity.
1050
1051 There is also a journal of stocktakings.
1052
1053 Templates are located under C<templates/webpages/inventory/stocktaking>.
1054 JavaScript functions can be found in C<js/kivi.Inventory.js>.
1055
1056 =head1 FUNCTIONS
1057
1058 =over 4
1059
1060 =item C<action_stock_usage>
1061
1062 Create a search form for stock withdrawal.
1063 The search parameter for report are made like the reports in bin/mozilla/rp.pl
1064
1065 =item C<action_usage>
1066
1067 Make a report about stock withdrawal.
1068
1069 The manual pagination is implemented like the pagination in SL::Controller::CsvImport.
1070
1071 =item C<action_stocktaking>
1072
1073 This action renders the input form for stocktaking.
1074
1075 =item C<action_save_stocktaking>
1076
1077 This action saves the stocktaking values and corrects the stock after checking
1078 if the part is already counted for this warehouse, bin and cutoff date.
1079 For saving SL::WH->transfer is called.
1080
1081 =item C<action_reload_stocktaking_history>
1082
1083 This action is responsible for displaying the stocktaking history at the bottom
1084 of the form. It uses the stocktaking journal with fixed filters for cutoff date
1085 and the current employee. The history is displayed via javascript.
1086
1087 =item C<action_stocktaking_part_changed>
1088
1089 This action is called after the user selected or changed the part.
1090
1091 =item C<action_stocktaking_get_warn_qty_threshold>
1092
1093 This action checks if a warning should be shown and returns the warning text via
1094 ajax. The warning will be shown if the given target value is greater than the
1095 threshold given in the client configuration.
1096
1097 =item C<is_stocktaking>
1098
1099 This is a method to check if actions are called from stocktaking form.
1100 This actions should contain "stocktaking" in their name.
1101
1102 =back
1103
1104 =head1 SPECIAL CASES
1105
1106 Because of the PFD-Table Formatter some parameters for PDF must be different to the HTML parameters.
1107 So in german language there are some tries to use a HTML Break in the second heading line
1108 to produce two line heading inside table. The actual version has some abbreviations for the header texts.
1109
1110 =head1 BUGS
1111
1112 The PDF-Table library has some limits (doesn't display all if the line is to large) so
1113 the format is adapted to this
1114
1115
1116 =head1 AUTHOR
1117
1118 =over 4
1119
1120 =item only for C<action_stock_usage> and C<action_usage>:
1121
1122 Martin Helmling E<lt>martin.helmling@opendynamic.deE<gt>
1123
1124 =item for stocktaking:
1125
1126 Bernd Bleßmann E<lt>bernd@kivitendo-premium.deE<gt>
1127
1128 =back
1129
1130 =cut