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 = ref($EVAL_ERROR) eq 'SL::X::FormError' ? $EVAL_ERROR->error : $EVAL_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_sorted_naturally->[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_sorted_naturally,
 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 20
 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   $report->{title} = t8('Stocktaking Journal');
 872   $self->{report}  = $report;
 873
 874   my @columns     = qw(itime employee ean partnumber part qty unit bin chargenumber comment cutoff_date);
 875   my @sortable    = qw(itime employee ean partnumber part qty bin chargenumber comment cutoff_date);
 876
 877   my %column_defs = (
 878     itime           => { sub   => sub { $_[0]->itime_as_timestamp },
 879                          text  => t8('Insert Date'), },
 880     employee        => { sub   => sub { $_[0]->employee->safe_name },
 881                          text  => t8('Employee'), },
 882     ean             => { sub   => sub { $_[0]->part->ean },
 883                          text  => t8('EAN'), },
 884     partnumber      => { sub   => sub { $_[0]->part->partnumber },
 885                          text  => t8('Part Number'), },
 886     part            => { sub   => sub { $_[0]->part->description },
 887                          text  => t8('Part Description'), },
 888     qty             => { sub   => sub { $_[0]->qty_as_number },
 889                          text  => t8('Target Qty'),
 890                          align => 'right', },
 891     unit            => { sub   => sub { $_[0]->part->unit },
 892                          text  => t8('Unit'), },
 893     bin             => { sub   => sub { $_[0]->bin->full_description },
 894                          text  => t8('Bin'), },
 895     chargenumber    => { text  => t8('Charge Number'), },
 896     comment         => { text  => t8('Comment'), },
 897     cutoff_date     => { sub   => sub { $_[0]->cutoff_date_as_date },
 898                          text  => t8('Cutoff Date'), },
 899   );
 900
 901   $report->set_options(
 902     std_column_visibility => 1,
 903     controller_class      => 'Inventory',
 904     output_format         => 'HTML',
 905     title                 => (!!$params{full})? $::locale->text('Stocktaking Journal') : $::locale->text('Stocktaking History'),
 906     allow_pdf_export      => !!$params{full},
 907     allow_csv_export      => !!$params{full},
 908   );
 909   $report->set_columns(%column_defs);
 910   $report->set_column_order(@columns);
 911   $report->set_export_options(qw(stocktaking_journal filter));
 912   $report->set_options_from_form;
 913   $self->stocktaking_models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 914   $self->stocktaking_models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable) if !!$params{full};
 915   if (!!$params{full}) {
 916     $report->set_options(
 917       raw_top_info_text    => $self->render('inventory/stocktaking/full_report_top', { output => 0 }),
 918     );
 919   }
 920   $report->set_options(
 921     raw_bottom_info_text => $self->render('inventory/stocktaking/report_bottom',   { output => 0 }),
 922   );
 923 }
 924
 925 sub _get_stocked_qty {
 926   my ($part, %params) = @_;
 927
 928   my $bestbefore_filter  = '';
 929   my $bestbefore_val_cnt = 0;
 930   if ($::instance_conf->get_show_bestbefore) {
 931     $bestbefore_filter  = ($params{bestbefore}) ? 'AND bestbefore = ?' : 'AND bestbefore IS NULL';
 932     $bestbefore_val_cnt = ($params{bestbefore}) ? 1                    : 0;
 933   }
 934
 935   my $query = <<SQL;
 936     SELECT sum(qty) FROM inventory
 937       WHERE parts_id = ? AND warehouse_id = ? AND bin_id = ? AND chargenumber = ? $bestbefore_filter
 938       GROUP BY warehouse_id, bin_id, chargenumber
 939 SQL
 940
 941   my @values = ($part->id,
 942                 $params{warehouse_id},
 943                 $params{bin_id},
 944                 $params{chargenumber});
 945   push @values, $params{bestbefore} if $bestbefore_val_cnt;
 946
 947   my ($stocked_qty) = selectrow_query($::form, $::form->get_standard_dbh, $query, @values);
 948
 949   return 1*($stocked_qty || 0);
 950 }
 951
 952 sub _already_counted {
 953   my ($part, %params) = @_;
 954
 955   my %bestbefore_filter;
 956   if ($::instance_conf->get_show_bestbefore) {
 957     %bestbefore_filter = (bestbefore => ($params{bestbefore} || undef));
 958   }
 959
 960   SL::DB::Manager::Stocktaking->get_all(query => [and => [parts_id     => $part->id,
 961                                                           warehouse_id => $params{warehouse_id},
 962                                                           bin_id       => $params{bin_id},
 963                                                           cutoff_date  => $params{cutoff_date},
 964                                                           chargenumber => $params{chargenumber},
 965                                                           %bestbefore_filter]],
 966                                         sort_by => ['itime DESC']);
 967 }
 968
 969 sub setup_stock_in_action_bar {
 970   my ($self, %params) = @_;
 971
 972   for my $bar ($::request->layout->get('actionbar')) {
 973     $bar->add(
 974       action => [
 975         t8('Stock'),
 976         submit    => [ '#form', { action => 'Inventory/stock' } ],
 977         checks    => [ 'check_part_selection_before_stocking' ],
 978         accesskey => 'enter',
 979       ],
 980     );
 981   }
 982 }
 983
 984 sub setup_stock_usage_action_bar {
 985   my ($self, %params) = @_;
 986
 987   for my $bar ($::request->layout->get('actionbar')) {
 988     $bar->add(
 989       action => [
 990         t8('Show'),
 991         submit    => [ '#form', { action => 'Inventory/usage' } ],
 992         accesskey => 'enter',
 993       ],
 994     );
 995   }
 996 }
 997
 998 sub setup_stock_stocktaking_action_bar {
 999   my ($self, %params) = @_;
1000
1001   for my $bar ($::request->layout->get('actionbar')) {
1002     $bar->add(
1003       action => [
1004         t8('Save'),
1005         checks    => [ 'kivi.Inventory.check_stocktaking_qty_threshold' ],
1006         call      => [ 'kivi.Inventory.save_stocktaking' ],
1007         accesskey => 'enter',
1008       ],
1009     );
1010   }
1011 }
1012
1013 1;
1014 __END__
1015
1016 =encoding utf-8
1017
1018 =head1 NAME
1019
1020 SL::Controller::Inventory - Controller for inventory
1021
1022 =head1 DESCRIPTION
1023
1024 This controller handles stock in, stocktaking and reports about inventory
1025 in warehouses/stocks
1026
1027 - warehouse content
1028
1029 - warehouse journal
1030
1031 - warehouse withdrawal
1032
1033 - stocktaking
1034
1035 =head2 Stocktaking
1036
1037 Stocktaking allows to document the counted quantities of parts during
1038 stocktaking for a certain cutoff date. Differences between counted and stocked
1039 quantities are corrected in the stock. The transfer type 'stocktacking' is set
1040 here.
1041
1042 After picking a part, the mini stock for this part is displayed. At the bottom
1043 of the form a history of already counted parts for the current employee and the
1044 choosen cutoff date is shown.
1045
1046 Warehouse, bin and cutoff date canbe preselected in the client configuration.
1047
1048 If a part was already counted for this cutoff date, warehouse and bin, a warning
1049 is displayed, allowing the user to choose to add the counted quantity to the
1050 stocked one or to take his counted quantity as the new stocked quantity.
1051
1052 There is also a journal of stocktakings.
1053
1054 Templates are located under C<templates/webpages/inventory/stocktaking>.
1055 JavaScript functions can be found in C<js/kivi.Inventory.js>.
1056
1057 =head1 FUNCTIONS
1058
1059 =over 4
1060
1061 =item C<action_stock_usage>
1062
1063 Create a search form for stock withdrawal.
1064 The search parameter for report are made like the reports in bin/mozilla/rp.pl
1065
1066 =item C<action_usage>
1067
1068 Make a report about stock withdrawal.
1069
1070 The manual pagination is implemented like the pagination in SL::Controller::CsvImport.
1071
1072 =item C<action_stocktaking>
1073
1074 This action renders the input form for stocktaking.
1075
1076 =item C<action_save_stocktaking>
1077
1078 This action saves the stocktaking values and corrects the stock after checking
1079 if the part is already counted for this warehouse, bin and cutoff date.
1080 For saving SL::WH->transfer is called.
1081
1082 =item C<action_reload_stocktaking_history>
1083
1084 This action is responsible for displaying the stocktaking history at the bottom
1085 of the form. It uses the stocktaking journal with fixed filters for cutoff date
1086 and the current employee. The history is displayed via javascript.
1087
1088 =item C<action_stocktaking_part_changed>
1089
1090 This action is called after the user selected or changed the part.
1091
1092 =item C<action_stocktaking_get_warn_qty_threshold>
1093
1094 This action checks if a warning should be shown and returns the warning text via
1095 ajax. The warning will be shown if the given target value is greater than the
1096 threshold given in the client configuration.
1097
1098 =item C<is_stocktaking>
1099
1100 This is a method to check if actions are called from stocktaking form.
1101 This actions should contain "stocktaking" in their name.
1102
1103 =back
1104
1105 =head1 SPECIAL CASES
1106
1107 Because of the PFD-Table Formatter some parameters for PDF must be different to the HTML parameters.
1108 So in german language there are some tries to use a HTML Break in the second heading line
1109 to produce two line heading inside table. The actual version has some abbreviations for the header texts.
1110
1111 =head1 BUGS
1112
1113 The PDF-Table library has some limits (doesn't display all if the line is to large) so
1114 the format is adapted to this
1115
1116
1117 =head1 AUTHOR
1118
1119 =over 4
1120
1121 =item only for C<action_stock_usage> and C<action_usage>:
1122
1123 Martin Helmling E<lt>martin.helmling@opendynamic.deE<gt>
1124
1125 =item for stocktaking:
1126
1127 Bernd Bleßmann E<lt>bernd@kivitendo-premium.deE<gt>
1128
1129 =back
1130
1131 =cut