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->subtract(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