6 use List::MoreUtils qw(any uniq);
7 use List::Util qw(sum);
8 use Rose::DB::Object::Helpers qw(as_tree);
10 use SL::Locale::String qw(t8);
12 use SL::DB::MetaSetup::Part;
13 use SL::DB::Manager::Part;
15 use SL::DB::Helper::AttrHTML;
16 use SL::DB::Helper::AttrSorted;
17 use SL::DB::Helper::TransNumberGenerator;
18 use SL::DB::Helper::CustomVariables (
22 use SL::DB::Helper::DisplayableNamePreferences (
23 title => t8('Article'),
24 options => [ {name => 'partnumber', title => t8('Part Number') },
25 {name => 'description', title => t8('Description') },
26 {name => 'notes', title => t8('Notes')},
27 {name => 'ean', title => t8('EAN') }, ],
31 __PACKAGE__->meta->add_relationships(
33 type => 'one to many',
34 class => 'SL::DB::Assembly',
35 manager_args => { sort_by => 'position' },
36 column_map => { id => 'id' },
39 type => 'one to many',
40 class => 'SL::DB::Price',
41 column_map => { id => 'parts_id' },
42 manager_args => { with_objects => [ 'pricegroup' ] }
45 type => 'one to many',
46 class => 'SL::DB::MakeModel',
47 manager_args => { sort_by => 'sortorder' },
48 column_map => { id => 'parts_id' },
51 type => 'one to many',
52 class => 'SL::DB::PartCustomerPrice',
53 column_map => { id => 'parts_id' },
56 type => 'one to many',
57 class => 'SL::DB::Translation',
58 column_map => { id => 'parts_id' },
61 type => 'one to many',
62 class => 'SL::DB::AssortmentItem',
63 column_map => { id => 'assortment_id' },
64 manager_args => { sort_by => 'position' },
67 type => 'one to many',
68 class => 'SL::DB::History',
69 column_map => { id => 'trans_id' },
70 query_args => [ what_done => 'part' ],
71 manager_args => { sort_by => 'itime' },
74 type => 'one to many',
75 class => 'SL::DB::ShopPart',
76 column_map => { id => 'part_id' },
77 manager_args => { with_objects => [ 'shop' ] },
79 last_price_update => {
81 class => 'SL::DB::PartsPriceHistory',
82 column_map => { id => 'part_id' },
83 manager_args => { sort_by => 'valid_from DESC', limit => 1 },
87 __PACKAGE__->meta->initialize;
89 __PACKAGE__->attr_html('notes');
90 __PACKAGE__->attr_sorted({ unsorted => 'makemodels', position => 'sortorder' });
91 __PACKAGE__->attr_sorted({ unsorted => 'customerprices', position => 'sortorder' });
93 __PACKAGE__->before_save('_before_save_set_partnumber');
94 __PACKAGE__->before_save('_before_save_set_assembly_weight');
96 sub _before_save_set_partnumber {
99 $self->create_trans_number if !$self->partnumber;
103 sub _before_save_set_assembly_weight {
106 if ( $self->part_type eq 'assembly' ) {
107 my $weight_sum = $self->items_weight_sum;
108 $self->weight($self->items_weight_sum) if $weight_sum;
116 if ( $self->part_type eq 'assembly' ) {
117 return $self->assemblies;
118 } elsif ( $self->part_type eq 'assortment' ) {
119 return $self->assortment_items;
128 # for detecting if the items of an (orphaned) assembly or assortment have
129 # changed when saving
131 return join(' ', sort map { $_->part->id } @{$self->items});
138 push @errors, $::locale->text('The partnumber is missing.') if $self->id and !$self->partnumber;
139 push @errors, $::locale->text('The unit is missing.') unless $self->unit;
140 push @errors, $::locale->text('The buchungsgruppe is missing.') unless $self->buchungsgruppen_id or $self->buchungsgruppe;
142 if ( $::instance_conf->get_partsgroup_required
143 && ( !$self->partsgroup_id or ( $self->id && !$self->partsgroup_id && $self->partsgroup ) ) ) {
144 # when unsetting an existing partsgroup in the interface, $self->partsgroup_id will be undef but $self->partsgroup will still have a value
145 # this needs to be checked, as partsgroup dropdown has an empty value
146 push @errors, $::locale->text('The partsgroup is missing.');
149 unless ( $self->id ) {
150 push @errors, $::locale->text('The partnumber already exists.') if SL::DB::Manager::Part->get_all_count(where => [ partnumber => $self->partnumber ]);
153 if ($self->is_assortment && $self->orphaned && scalar @{$self->assortment_items} == 0) {
154 # when assortment isn't orphaned form doesn't contain any items
155 push @errors, $::locale->text('The assortment doesn\'t have any items.');
158 if ($self->is_assembly && scalar @{$self->assemblies} == 0) {
159 push @errors, $::locale->text('The assembly doesn\'t have any items.');
167 my $type = lc(shift || '');
168 die 'invalid type' unless $type =~ /^(?:part|service|assembly|assortment)$/;
170 return $self->type eq $type ? 1 : 0;
173 sub is_part { $_[0]->part_type eq 'part' }
174 sub is_assembly { $_[0]->part_type eq 'assembly' }
175 sub is_service { $_[0]->part_type eq 'service' }
176 sub is_assortment { $_[0]->part_type eq 'assortment' }
179 return $_[0]->part_type;
180 # my ($self, $type) = @_;
182 # die 'invalid type' unless $type =~ /^(?:part|service|assembly)$/;
183 # $self->assembly( $type eq 'assembly' ? 1 : 0);
184 # $self->inventory_accno_id($type ne 'service' ? 1 : undef);
187 # return 'assembly' if $self->assembly;
188 # return 'part' if $self->inventory_accno_id;
193 my ($class, %params) = @_;
194 $class->new(%params, part_type => 'part');
198 my ($class, %params) = @_;
199 $class->new(%params, part_type => 'assembly');
203 my ($class, %params) = @_;
204 $class->new(%params, part_type => 'service');
208 my ($class, %params) = @_;
209 $class->new(%params, part_type => 'assortment');
212 sub last_modification {
214 return $self->mtime // $self->itime;
219 die 'not an accessor' if @_ > 1;
221 return 1 unless $self->id;
226 SL::DB::DeliveryOrderItem
229 for my $class (@relations) {
230 eval "require $class";
231 return 1 if $class->_get_manager_class->get_all_count(query => [ parts_id => $self->id ]);
237 die 'not an accessor' if @_ > 1;
239 return 1 unless $self->id;
244 SL::DB::DeliveryOrderItem
246 SL::DB::AssortmentItem
249 for my $class (@relations) {
250 eval "require $class";
251 return 0 if $class->_get_manager_class->get_all_count(query => [ parts_id => $self->id ]);
256 sub get_sellprice_info {
260 confess "Missing part id" unless $self->id;
262 my $object = $self->load;
264 return { sellprice => $object->sellprice,
265 price_factor_id => $object->price_factor_id };
268 sub get_ordered_qty {
270 my %result = SL::DB::Manager::Part->get_ordered_qty($self->id);
272 return $result{ $self->id };
275 sub available_units {
276 shift->unit_obj->convertible_units;
279 # autogenerated accessor is slightly off...
281 shift->buchungsgruppen(@_);
285 my ($self, %params) = @_;
287 my $date = $params{date} || DateTime->today_local;
288 my $is_sales = !!$params{is_sales};
289 my $taxzone = $params{ defined($params{taxzone}) ? 'taxzone' : 'taxzone_id' } * 1;
290 my $tk_info = $::request->cache('get_taxkey');
292 $tk_info->{$self->id} //= {};
293 $tk_info->{$self->id}->{$taxzone} //= { };
294 my $cache = $tk_info->{$self->id}->{$taxzone}->{$is_sales} //= { };
296 if (!exists $cache->{$date}) {
298 $self->get_chart(type => $is_sales ? 'income' : 'expense', taxzone => $taxzone)
299 ->get_active_taxkey($date);
302 return $cache->{$date};
306 my ($self, %params) = @_;
308 my $type = (any { $_ eq $params{type} } qw(income expense inventory)) ? $params{type} : croak("Invalid 'type' parameter '$params{type}'");
309 my $taxzone = $params{ defined($params{taxzone}) ? 'taxzone' : 'taxzone_id' } * 1;
311 my $charts = $::request->cache('get_chart_id/by_part_id_and_taxzone')->{$self->id} //= {};
312 my $all_charts = $::request->cache('get_chart_id/by_id');
314 $charts->{$taxzone} ||= { };
316 if (!exists $charts->{$taxzone}->{$type}) {
317 require SL::DB::Buchungsgruppe;
318 my $bugru = SL::DB::Buchungsgruppe->load_cached($self->buchungsgruppen_id);
319 my $chart_id = ($type eq 'inventory') ? ($self->is_part ? $bugru->inventory_accno_id : undef)
320 : $bugru->call_sub("${type}_accno_id", $taxzone);
323 my $chart = $all_charts->{$chart_id} // SL::DB::Chart->load_cached($chart_id)->load;
324 $all_charts->{$chart_id} = $chart;
325 $charts->{$taxzone}->{$type} = $chart;
329 return $charts->{$taxzone}->{$type};
333 my ($self, %params) = @_;
335 return undef unless $self->id;
337 my $query = 'SELECT SUM(qty) FROM inventory WHERE parts_id = ?';
338 my @values = ($self->id);
340 if ( $params{bin_id} ) {
341 $query .= ' AND bin_id = ?';
342 push(@values, $params{bin_id});
345 if ( $params{warehouse_id} ) {
346 $query .= ' AND warehouse_id = ?';
347 push(@values, $params{warehouse_id});
350 if ( $params{shippingdate} ) {
351 die unless ref($params{shippingdate}) eq 'DateTime';
352 $query .= ' AND shippingdate <= ?';
353 push(@values, $params{shippingdate});
356 my ($stock) = selectrow_query($::form, $self->db->dbh, $query, @values);
358 return $stock || 0; # never return undef
362 # this is designed to ignore chargenumbers, expiration dates and just give a list of how much <-> where
363 sub get_simple_stock {
364 my ($self, %params) = @_;
366 return [] unless $self->id;
369 SELECT sum(qty), warehouse_id, bin_id FROM inventory WHERE parts_id = ?
370 GROUP BY warehouse_id, bin_id
372 my $stock_info = selectall_hashref_query($::form, $::form->get_standard_dbh, $query, $self->id);
373 [ map { bless $_, 'SL::DB::Part::SimpleStock'} @$stock_info ];
375 # helper class to have bin/warehouse accessors in stock result
376 { package SL::DB::Part::SimpleStock;
377 sub warehouse { require SL::DB::Warehouse; SL::DB::Manager::Warehouse->find_by_or_create(id => $_[0]->{warehouse_id}) }
378 sub bin { require SL::DB::Bin; SL::DB::Manager::Bin ->find_by_or_create(id => $_[0]->{bin_id}) }
381 sub get_simple_stock_sql {
382 my ($self, %params) = @_;
384 return [] unless $self->id;
387 SELECT w.description AS warehouse_description,
388 b.description AS bin_description,
390 SUM(i.qty * p.lastcost) AS stock_value,
392 LEAD(w.description) OVER pt AS wh_lead, -- to detect warehouse changes for subtotals in template
393 SUM( SUM(i.qty) ) OVER pt AS run_qty, -- running total of total qty
394 SUM( SUM(i.qty) ) OVER wh AS wh_run_qty, -- running total of warehouse qty
395 SUM( SUM(i.qty * p.lastcost)) OVER pt AS run_stock_value, -- running total of total stock_value
396 SUM( SUM(i.qty * p.lastcost)) OVER wh AS wh_run_stock_value -- running total of warehouse stock_value
398 LEFT JOIN parts p ON (p.id = i.parts_id)
399 LEFT JOIN warehouse w ON (i.warehouse_id = w.id)
400 LEFT JOIN bin b ON (i.bin_id = b.id)
402 GROUP BY w.description, w.sortkey, b.description, p.unit, i.parts_id
404 WINDOW pt AS (PARTITION BY i.parts_id ORDER BY w.sortkey, b.description, p.unit),
405 wh AS (PARTITION by w.description ORDER BY w.sortkey, b.description, p.unit)
406 ORDER BY w.sortkey, b.description, p.unit
409 my $stock_info = selectall_hashref_query($::form, $self->db->dbh, $query, $self->id);
413 sub get_mini_journal {
416 # inventory ids of the most recent 10 inventory trans_ids
418 # duplicate code copied from SL::Controller::Inventory mini_journal, except
419 # for the added filter on parts_id
421 my $parts_id = $self->id;
423 with last_inventories as (
428 where parts_id = $parts_id
435 from last_inventories
442 limit 20 -- so the planner knows how many ids to expect, the cte is an optimisation fence
445 my $objs = SL::DB::Manager::Inventory->get_all(
446 query => [ id => [ \"$query" ] ], # make emacs happy "
447 with_objects => [ 'parts', 'trans_type', 'bin', 'bin.warehouse' ], # prevent lazy loading in template
448 sort_by => 'itime DESC',
450 # remember order of trans_ids from query, for ordering hash later
451 my @sorted_trans_ids = uniq map { $_->trans_id } @$objs;
453 # at most 2 of them belong to a transaction and the qty determines in or out.
456 $transactions{ $_->trans_id }{ $_->qty > 0 ? 'in' : 'out' } = $_;
457 $transactions{ $_->trans_id }{base} = $_;
460 # because the inventory transactions were built in a hash, we need to sort the
461 # hash by using the original sort order of the trans_ids
462 my @sorted = map { $transactions{$_} } @sorted_trans_ids;
467 sub clone_and_reset_deep {
470 my $clone = $self->clone_and_reset; # resets id and partnumber (primary key and unique constraint)
471 $clone->makemodels( map { $_->clone_and_reset } @{$self->makemodels} ) if @{$self->makemodels};
472 $clone->translations( map { $_->clone_and_reset } @{$self->translations} ) if @{$self->translations};
474 if ( $self->is_assortment ) {
475 # use clone rather than reset_and_clone because the unique constraint would also remove parts_id
476 $clone->assortment_items( map { $_->clone } @{$self->assortment_items} );
477 $_->assortment_id(undef) foreach @{ $clone->assortment_items }
480 if ( $self->is_assembly ) {
481 $clone->assemblies( map { $_->clone_and_reset } @{$self->assemblies});
484 if ( $self->prices ) {
485 $clone->prices( map { $_->clone } @{$self->prices}); # pricegroup_id gets reset here because it is part of a unique contraint
486 if ( $clone->prices ) {
487 foreach my $price ( @{$clone->prices} ) {
489 $price->parts_id(undef);
498 my ($self, $comparison_part) = @_;
500 die "item_diffs needs a part object" unless ref($comparison_part) eq 'SL::DB::Part';
501 die "part and comparison_part need to be of the same part_type" unless
502 ( $self->part_type eq 'assembly' or $self->part_type eq 'assortment' )
503 and ( $comparison_part->part_type eq 'assembly' or $comparison_part->part_type eq 'assortment' )
504 and $self->part_type eq $comparison_part->part_type;
506 # return [], [] if $self->items_checksum eq $comparison_part->items_checksum;
507 my @self_part_ids = map { $_->parts_id } $self->items;
508 my @comparison_part_ids = map { $_->parts_id } $comparison_part->items;
510 my %orig = map{ $_ => 1 } @self_part_ids;
511 my %comparison = map{ $_ => 1 } @comparison_part_ids;
512 my (@additions, @removals);
513 @additions = grep { !exists( $orig{$_} ) } @comparison_part_ids if @comparison_part_ids;
514 @removals = grep { !exists( $comparison{$_} ) } @self_part_ids if @self_part_ids;
516 return \@additions, \@removals;
519 sub items_sellprice_sum {
520 my ($self, %params) = @_;
522 return unless $self->is_assortment or $self->is_assembly;
523 return unless $self->items;
525 if ($self->is_assembly) {
526 return sum map { $_->linetotal_sellprice } @{$self->items};
528 return sum map { $_->linetotal_sellprice(%params) } grep { $_->charge } @{$self->items};
532 sub items_lastcost_sum {
535 return unless $self->is_assortment or $self->is_assembly;
536 return unless $self->items;
537 sum map { $_->linetotal_lastcost } @{$self->items};
540 sub items_weight_sum {
543 return unless $self->is_assembly;
544 return unless $self->items;
545 sum map { $_->linetotal_weight} @{$self->items};
558 SL::DB::Part: Model for the 'parts' table
562 This is a standard Rose::DB::Object based model and can be used as one.
566 Although the base class is called C<Part> we usually talk about C<Articles> if
567 we mean instances of this class. This is because articles come in three
572 =item Part - a single part
574 =item Service - a part without onhand, and without inventory accounting
576 =item Assembly - a collection of both parts and services
578 =item Assortment - a collection of items (parts or assemblies)
582 These types are sadly represented by data inside the class and cannot be
583 migrated into a flag. To work around this, each C<Part> object knows what type
584 it currently is. Since the type is data driven, there ist no explicit setting
585 method for it, but you can construct them explicitly with C<new_part>,
586 C<new_service>, C<new_assembly> and C<new_assortment>. A Buchungsgruppe should be supplied in this
587 case, but it will use the default Buchungsgruppe if you don't.
589 Matching these there are assorted helper methods dealing with types,
590 e.g. L</new_part>, L</new_service>, L</new_assembly>, L</type>,
591 L</is_type> and others.
597 =item C<new_part %PARAMS>
599 =item C<new_service %PARAMS>
601 =item C<new_assembly %PARAMS>
603 Will set the appropriate data fields so that the resulting instance will be of
604 the requested type. Since accounting targets are part of the distinction,
605 providing a C<Buchungsgruppe> is recommended. If none is given the constructor
606 will load a default one and set the accounting targets from it.
610 Returns the type as a string. Can be one of C<part>, C<service>, C<assembly>.
612 =item C<is_type $TYPE>
614 Tests if the current object is a part, a service or an
615 assembly. C<$type> must be one of the words 'part', 'service' or
616 'assembly' (their plurals are ok, too).
618 Returns 1 if the requested type matches, 0 if it doesn't and
619 C<confess>es if an unknown C<$type> parameter is encountered.
627 Shorthand for C<is_type('part')> etc.
629 =item C<get_sellprice_info %params>
631 Retrieves the C<sellprice> and C<price_factor_id> for a part under
632 different conditions and returns a hash reference with those two keys.
634 If C<%params> contains a key C<project_id> then a project price list
635 will be consulted if one exists for that project. In this case the
636 parameter C<country_id> is evaluated as well: if a price list entry
637 has been created for this country then it will be used. Otherwise an
638 entry without a country set will be used.
640 If none of the above conditions is met then the information from
643 =item C<get_ordered_qty %params>
645 Retrieves the quantity that has been ordered from a vendor but that
646 has not been delivered yet. Only open purchase orders are considered.
648 =item C<get_taxkey %params>
650 Retrieves and returns a taxkey object valid for the given date
651 C<$params{date}> and tax zone C<$params{taxzone}>
652 (C<$params{taxzone_id}> is also recognized). The date defaults to the
653 current date if undefined.
655 This function looks up the income (for trueish values of
656 C<$params{is_sales}>) or expense (for falsish values of
657 C<$params{is_sales}>) account for the current part. It uses the part's
658 associated buchungsgruppe and uses the fields belonging to the tax
659 zone given by C<$params{taxzone}>.
661 The information retrieved by the function is cached.
663 =item C<get_chart %params>
665 Retrieves and returns a chart object valid for the given type
666 C<$params{type}> and tax zone C<$params{taxzone}>
667 (C<$params{taxzone_id}> is also recognized). The type must be one of
668 the three key words C<income>, C<expense> and C<inventory>.
670 This function uses the part's associated buchungsgruppe and uses the
671 fields belonging to the tax zone given by C<$params{taxzone}>.
673 The information retrieved by the function is cached.
675 =item C<used_in_record>
677 Checks if this article has been used in orders, invoices or delivery orders.
681 Checks if this article is used in orders, invoices, delivery orders or
684 =item C<buchungsgruppe BUCHUNGSGRUPPE>
686 Used to set the accounting information from a L<SL:DB::Buchungsgruppe> object.
687 Please note, that this is a write only accessor, the original Buchungsgruppe can
688 not be retrieved from an article once set.
690 =item C<get_simple_stock_sql>
692 Fetches the qty and the stock value for the current part for each bin and
693 warehouse where the part is in stock (or rather different from 0, might be
696 Runs some additional window functions to add the running totals (total running
697 total and total per warehouse) for qty and stock value to each line.
699 Using the LEAD(w.description) the template can check if the warehouse
700 description is about to change, i.e. the next line will contain numbers from a
701 different warehouse, so that a subtotal line can be added.
703 The last row will contain the running qty total (run_qty) and the running total
704 stock value (run_stock_value) over all warehouses/bins and can be used to add a
705 line for the grand totals.
707 =item C<items_lastcost_sum>
709 Non-recursive lastcost sum of all the items in an assembly or assortment.
711 =item C<get_stock %params>
713 Fetches stock qty in the default unit for a part.
715 bin_id and warehouse_id may be passed as params. If only a bin_id is passed,
716 the stock qty for that bin is returned. If only a warehouse_id is passed, the
717 stock qty for all bins in that warehouse is returned. If a shippingdate is
718 passed the stock qty for that date is returned.
721 my $qty = $part->get_stock(bin_id => 52);
723 $part->get_stock(shippingdate => DateTime->today->add(days => -5));
729 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>,
730 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>