6 use List::MoreUtils qw(any uniq);
7 use Rose::DB::Object::Helpers qw(as_tree);
9 use SL::Locale::String qw(t8);
11 use SL::DB::MetaSetup::Part;
12 use SL::DB::Manager::Part;
14 use SL::DB::Helper::AttrHTML;
15 use SL::DB::Helper::AttrSorted;
16 use SL::DB::Helper::TransNumberGenerator;
17 use SL::DB::Helper::CustomVariables (
21 use SL::DB::Helper::DisplayableNamePreferences (
22 title => t8('Article'),
23 options => [ {name => 'partnumber', title => t8('Part Number') },
24 {name => 'description', title => t8('Description') },
25 {name => 'notes', title => t8('Notes')},
26 {name => 'ean', title => t8('EAN') }, ],
29 use List::Util qw(sum);
31 __PACKAGE__->meta->add_relationships(
33 type => 'one to many',
34 class => 'SL::DB::Assembly',
35 manager_args => { sort_by => 'position, oid' },
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' },
66 type => 'one to many',
67 class => 'SL::DB::History',
68 column_map => { id => 'trans_id' },
69 query_args => [ what_done => 'part' ],
70 manager_args => { sort_by => 'itime' },
73 type => 'one to many',
74 class => 'SL::DB::ShopPart',
75 column_map => { id => 'part_id' },
76 manager_args => { with_objects => [ 'shop' ] },
80 __PACKAGE__->meta->initialize;
82 __PACKAGE__->attr_html('notes');
83 __PACKAGE__->attr_sorted({ unsorted => 'makemodels', position => 'sortorder' });
85 __PACKAGE__->before_save('_before_save_set_partnumber');
87 sub _before_save_set_partnumber {
90 $self->create_trans_number if !$self->partnumber;
97 if ( $self->part_type eq 'assembly' ) {
98 return $self->assemblies;
99 } elsif ( $self->part_type eq 'assortment' ) {
100 return $self->assortment_items;
109 # for detecting if the items of an (orphaned) assembly or assortment have
110 # changed when saving
112 return join(' ', sort map { $_->part->id } @{$self->items});
119 push @errors, $::locale->text('The partnumber is missing.') if $self->id and !$self->partnumber;
120 push @errors, $::locale->text('The unit is missing.') unless $self->unit;
121 push @errors, $::locale->text('The buchungsgruppe is missing.') unless $self->buchungsgruppen_id or $self->buchungsgruppe;
123 unless ( $self->id ) {
124 push @errors, $::locale->text('The partnumber already exists.') if SL::DB::Manager::Part->get_all_count(where => [ partnumber => $self->partnumber ]);
127 if ($self->is_assortment && $self->orphaned && scalar @{$self->assortment_items} == 0) {
128 # when assortment isn't orphaned form doesn't contain any items
129 push @errors, $::locale->text('The assortment doesn\'t have any items.');
132 if ($self->is_assembly && scalar @{$self->assemblies} == 0) {
133 push @errors, $::locale->text('The assembly doesn\'t have any items.');
141 my $type = lc(shift || '');
142 die 'invalid type' unless $type =~ /^(?:part|service|assembly|assortment)$/;
144 return $self->type eq $type ? 1 : 0;
147 sub is_part { $_[0]->part_type eq 'part' }
148 sub is_assembly { $_[0]->part_type eq 'assembly' }
149 sub is_service { $_[0]->part_type eq 'service' }
150 sub is_assortment { $_[0]->part_type eq 'assortment' }
153 return $_[0]->part_type;
154 # my ($self, $type) = @_;
156 # die 'invalid type' unless $type =~ /^(?:part|service|assembly)$/;
157 # $self->assembly( $type eq 'assembly' ? 1 : 0);
158 # $self->inventory_accno_id($type ne 'service' ? 1 : undef);
161 # return 'assembly' if $self->assembly;
162 # return 'part' if $self->inventory_accno_id;
167 my ($class, %params) = @_;
168 $class->new(%params, part_type => 'part');
172 my ($class, %params) = @_;
173 $class->new(%params, part_type => 'assembly');
177 my ($class, %params) = @_;
178 $class->new(%params, part_type => 'service');
182 my ($class, %params) = @_;
183 $class->new(%params, part_type => 'assortment');
186 sub last_modification {
188 return $self->mtime // $self->itime;
193 die 'not an accessor' if @_ > 1;
195 return 1 unless $self->id;
200 SL::DB::DeliveryOrderItem
203 for my $class (@relations) {
204 eval "require $class";
205 return 1 if $class->_get_manager_class->get_all_count(query => [ parts_id => $self->id ]);
211 die 'not an accessor' if @_ > 1;
213 return 1 unless $self->id;
218 SL::DB::DeliveryOrderItem
220 SL::DB::AssortmentItem
223 for my $class (@relations) {
224 eval "require $class";
225 return 0 if $class->_get_manager_class->get_all_count(query => [ parts_id => $self->id ]);
230 sub get_sellprice_info {
234 confess "Missing part id" unless $self->id;
236 my $object = $self->load;
238 return { sellprice => $object->sellprice,
239 price_factor_id => $object->price_factor_id };
242 sub get_ordered_qty {
244 my %result = SL::DB::Manager::Part->get_ordered_qty($self->id);
246 return $result{ $self->id };
249 sub available_units {
250 shift->unit_obj->convertible_units;
253 # autogenerated accessor is slightly off...
255 shift->buchungsgruppen(@_);
259 my ($self, %params) = @_;
261 my $date = $params{date} || DateTime->today_local;
262 my $is_sales = !!$params{is_sales};
263 my $taxzone = $params{ defined($params{taxzone}) ? 'taxzone' : 'taxzone_id' } * 1;
264 my $tk_info = $::request->cache('get_taxkey');
266 $tk_info->{$self->id} //= {};
267 $tk_info->{$self->id}->{$taxzone} //= { };
268 my $cache = $tk_info->{$self->id}->{$taxzone}->{$is_sales} //= { };
270 if (!exists $cache->{$date}) {
272 $self->get_chart(type => $is_sales ? 'income' : 'expense', taxzone => $taxzone)
273 ->get_active_taxkey($date);
276 return $cache->{$date};
280 my ($self, %params) = @_;
282 my $type = (any { $_ eq $params{type} } qw(income expense inventory)) ? $params{type} : croak("Invalid 'type' parameter '$params{type}'");
283 my $taxzone = $params{ defined($params{taxzone}) ? 'taxzone' : 'taxzone_id' } * 1;
285 my $charts = $::request->cache('get_chart_id/by_part_id_and_taxzone')->{$self->id} //= {};
286 my $all_charts = $::request->cache('get_chart_id/by_id');
288 $charts->{$taxzone} ||= { };
290 if (!exists $charts->{$taxzone}->{$type}) {
291 require SL::DB::Buchungsgruppe;
292 my $bugru = SL::DB::Buchungsgruppe->load_cached($self->buchungsgruppen_id);
293 my $chart_id = ($type eq 'inventory') ? ($self->is_part ? $bugru->inventory_accno_id : undef)
294 : $bugru->call_sub("${type}_accno_id", $taxzone);
297 my $chart = $all_charts->{$chart_id} // SL::DB::Chart->load_cached($chart_id)->load;
298 $all_charts->{$chart_id} = $chart;
299 $charts->{$taxzone}->{$type} = $chart;
303 return $charts->{$taxzone}->{$type};
307 my ($self, %params) = @_;
309 return undef unless $self->id;
311 my $query = 'SELECT SUM(qty) FROM inventory WHERE parts_id = ?';
312 my @values = ($self->id);
314 if ( $params{bin_id} ) {
315 $query .= ' AND bin_id = ?';
316 push(@values, $params{bin_id});
319 if ( $params{warehouse_id} ) {
320 $query .= ' AND warehouse_id = ?';
321 push(@values, $params{warehouse_id});
324 if ( $params{shippingdate} ) {
325 die unless ref($params{shippingdate}) eq 'DateTime';
326 $query .= ' AND shippingdate <= ?';
327 push(@values, $params{shippingdate});
330 my ($stock) = selectrow_query($::form, $self->db->dbh, $query, @values);
332 return $stock || 0; # never return undef
336 # this is designed to ignore chargenumbers, expiration dates and just give a list of how much <-> where
337 sub get_simple_stock {
338 my ($self, %params) = @_;
340 return [] unless $self->id;
343 SELECT sum(qty), warehouse_id, bin_id FROM inventory WHERE parts_id = ?
344 GROUP BY warehouse_id, bin_id
346 my $stock_info = selectall_hashref_query($::form, $::form->get_standard_dbh, $query, $self->id);
347 [ map { bless $_, 'SL::DB::Part::SimpleStock'} @$stock_info ];
349 # helper class to have bin/warehouse accessors in stock result
350 { package SL::DB::Part::SimpleStock;
351 sub warehouse { require SL::DB::Warehouse; SL::DB::Manager::Warehouse->find_by_or_create(id => $_[0]->{warehouse_id}) }
352 sub bin { require SL::DB::Bin; SL::DB::Manager::Bin ->find_by_or_create(id => $_[0]->{bin_id}) }
355 sub get_simple_stock_sql {
356 my ($self, %params) = @_;
358 return [] unless $self->id;
361 SELECT w.description AS warehouse_description,
362 b.description AS bin_description,
364 SUM(i.qty * p.lastcost) AS stock_value,
366 LEAD(w.description) OVER pt AS wh_lead, -- to detect warehouse changes for subtotals in template
367 SUM( SUM(i.qty) ) OVER pt AS run_qty, -- running total of total qty
368 SUM( SUM(i.qty) ) OVER wh AS wh_run_qty, -- running total of warehouse qty
369 SUM( SUM(i.qty * p.lastcost)) OVER pt AS run_stock_value, -- running total of total stock_value
370 SUM( SUM(i.qty * p.lastcost)) OVER wh AS wh_run_stock_value -- running total of warehouse stock_value
372 LEFT JOIN parts p ON (p.id = i.parts_id)
373 LEFT JOIN warehouse w ON (i.warehouse_id = w.id)
374 LEFT JOIN bin b ON (i.bin_id = b.id)
376 GROUP BY w.description, w.sortkey, b.description, p.unit, i.parts_id
378 WINDOW pt AS (PARTITION BY i.parts_id ORDER BY w.sortkey, b.description, p.unit),
379 wh AS (PARTITION by w.description ORDER BY w.sortkey, b.description, p.unit)
380 ORDER BY w.sortkey, b.description, p.unit
383 my $stock_info = selectall_hashref_query($::form, $self->db->dbh, $query, $self->id);
387 sub get_mini_journal {
390 # inventory ids of the most recent 10 inventory trans_ids
392 # duplicate code copied from SL::Controller::Inventory mini_journal, except
393 # for the added filter on parts_id
395 my $parts_id = $self->id;
397 with last_inventories as (
402 where parts_id = $parts_id
409 from last_inventories
416 limit 20 -- so the planner knows how many ids to expect, the cte is an optimisation fence
419 my $objs = SL::DB::Manager::Inventory->get_all(
420 query => [ id => [ \"$query" ] ],
421 with_objects => [ 'parts', 'trans_type', 'bin', 'bin.warehouse' ], # prevent lazy loading in template
422 sort_by => 'itime DESC',
424 # remember order of trans_ids from query, for ordering hash later
425 my @sorted_trans_ids = uniq map { $_->trans_id } @$objs;
427 # at most 2 of them belong to a transaction and the qty determines in or out.
430 $transactions{ $_->trans_id }{ $_->qty > 0 ? 'in' : 'out' } = $_;
431 $transactions{ $_->trans_id }{base} = $_;
434 # because the inventory transactions were built in a hash, we need to sort the
435 # hash by using the original sort order of the trans_ids
436 my @sorted = map { $transactions{$_} } @sorted_trans_ids;
441 sub clone_and_reset_deep {
444 my $clone = $self->clone_and_reset; # resets id and partnumber (primary key and unique constraint)
445 $clone->makemodels( map { $_->clone_and_reset } @{$self->makemodels} ) if @{$self->makemodels};
446 $clone->translations( map { $_->clone_and_reset } @{$self->translations} ) if @{$self->translations};
448 if ( $self->is_assortment ) {
449 # use clone rather than reset_and_clone because the unique constraint would also remove parts_id
450 $clone->assortment_items( map { $_->clone } @{$self->assortment_items} );
451 $_->assortment_id(undef) foreach @{ $clone->assortment_items }
454 if ( $self->is_assembly ) {
455 $clone->assemblies( map { $_->clone_and_reset } @{$self->assemblies});
458 if ( $self->prices ) {
459 $clone->prices( map { $_->clone } @{$self->prices}); # pricegroup_id gets reset here because it is part of a unique contraint
460 if ( $clone->prices ) {
461 foreach my $price ( @{$clone->prices} ) {
463 $price->parts_id(undef);
472 my ($self, $comparison_part) = @_;
474 die "item_diffs needs a part object" unless ref($comparison_part) eq 'SL::DB::Part';
475 die "part and comparison_part need to be of the same part_type" unless
476 ( $self->part_type eq 'assembly' or $self->part_type eq 'assortment' )
477 and ( $comparison_part->part_type eq 'assembly' or $comparison_part->part_type eq 'assortment' )
478 and $self->part_type eq $comparison_part->part_type;
480 # return [], [] if $self->items_checksum eq $comparison_part->items_checksum;
481 my @self_part_ids = map { $_->parts_id } $self->items;
482 my @comparison_part_ids = map { $_->parts_id } $comparison_part->items;
484 my %orig = map{ $_ => 1 } @self_part_ids;
485 my %comparison = map{ $_ => 1 } @comparison_part_ids;
486 my (@additions, @removals);
487 @additions = grep { !exists( $orig{$_} ) } @comparison_part_ids if @comparison_part_ids;
488 @removals = grep { !exists( $comparison{$_} ) } @self_part_ids if @self_part_ids;
490 return \@additions, \@removals;
493 sub items_sellprice_sum {
494 my ($self, %params) = @_;
496 return unless $self->is_assortment or $self->is_assembly;
497 return unless $self->items;
499 if ($self->is_assembly) {
500 return sum map { $_->linetotal_sellprice } @{$self->items};
502 return sum map { $_->linetotal_sellprice(%params) } grep { $_->charge } @{$self->items};
506 sub items_lastcost_sum {
509 return unless $self->is_assortment or $self->is_assembly;
510 return unless $self->items;
511 sum map { $_->linetotal_lastcost } @{$self->items};
524 SL::DB::Part: Model for the 'parts' table
528 This is a standard Rose::DB::Object based model and can be used as one.
532 Although the base class is called C<Part> we usually talk about C<Articles> if
533 we mean instances of this class. This is because articles come in three
538 =item Part - a single part
540 =item Service - a part without onhand, and without inventory accounting
542 =item Assembly - a collection of both parts and services
544 =item Assortment - a collection of items (parts or assemblies)
548 These types are sadly represented by data inside the class and cannot be
549 migrated into a flag. To work around this, each C<Part> object knows what type
550 it currently is. Since the type is data driven, there ist no explicit setting
551 method for it, but you can construct them explicitly with C<new_part>,
552 C<new_service>, C<new_assembly> and C<new_assortment>. A Buchungsgruppe should be supplied in this
553 case, but it will use the default Buchungsgruppe if you don't.
555 Matching these there are assorted helper methods dealing with types,
556 e.g. L</new_part>, L</new_service>, L</new_assembly>, L</type>,
557 L</is_type> and others.
563 =item C<new_part %PARAMS>
565 =item C<new_service %PARAMS>
567 =item C<new_assembly %PARAMS>
569 Will set the appropriate data fields so that the resulting instance will be of
570 the requested type. Since accounting targets are part of the distinction,
571 providing a C<Buchungsgruppe> is recommended. If none is given the constructor
572 will load a default one and set the accounting targets from it.
576 Returns the type as a string. Can be one of C<part>, C<service>, C<assembly>.
578 =item C<is_type $TYPE>
580 Tests if the current object is a part, a service or an
581 assembly. C<$type> must be one of the words 'part', 'service' or
582 'assembly' (their plurals are ok, too).
584 Returns 1 if the requested type matches, 0 if it doesn't and
585 C<confess>es if an unknown C<$type> parameter is encountered.
593 Shorthand for C<is_type('part')> etc.
595 =item C<get_sellprice_info %params>
597 Retrieves the C<sellprice> and C<price_factor_id> for a part under
598 different conditions and returns a hash reference with those two keys.
600 If C<%params> contains a key C<project_id> then a project price list
601 will be consulted if one exists for that project. In this case the
602 parameter C<country_id> is evaluated as well: if a price list entry
603 has been created for this country then it will be used. Otherwise an
604 entry without a country set will be used.
606 If none of the above conditions is met then the information from
609 =item C<get_ordered_qty %params>
611 Retrieves the quantity that has been ordered from a vendor but that
612 has not been delivered yet. Only open purchase orders are considered.
614 =item C<get_taxkey %params>
616 Retrieves and returns a taxkey object valid for the given date
617 C<$params{date}> and tax zone C<$params{taxzone}>
618 (C<$params{taxzone_id}> is also recognized). The date defaults to the
619 current date if undefined.
621 This function looks up the income (for trueish values of
622 C<$params{is_sales}>) or expense (for falsish values of
623 C<$params{is_sales}>) account for the current part. It uses the part's
624 associated buchungsgruppe and uses the fields belonging to the tax
625 zone given by C<$params{taxzone}>.
627 The information retrieved by the function is cached.
629 =item C<get_chart %params>
631 Retrieves and returns a chart object valid for the given type
632 C<$params{type}> and tax zone C<$params{taxzone}>
633 (C<$params{taxzone_id}> is also recognized). The type must be one of
634 the three key words C<income>, C<expense> and C<inventory>.
636 This function uses the part's associated buchungsgruppe and uses the
637 fields belonging to the tax zone given by C<$params{taxzone}>.
639 The information retrieved by the function is cached.
641 =item C<used_in_record>
643 Checks if this article has been used in orders, invoices or delivery orders.
647 Checks if this article is used in orders, invoices, delivery orders or
650 =item C<buchungsgruppe BUCHUNGSGRUPPE>
652 Used to set the accounting information from a L<SL:DB::Buchungsgruppe> object.
653 Please note, that this is a write only accessor, the original Buchungsgruppe can
654 not be retrieved from an article once set.
656 =item C<get_simple_stock_sql>
658 Fetches the qty and the stock value for the current part for each bin and
659 warehouse where the part is in stock (or rather different from 0, might be
662 Runs some additional window functions to add the running totals (total running
663 total and total per warehouse) for qty and stock value to each line.
665 Using the LEAD(w.description) the template can check if the warehouse
666 description is about to change, i.e. the next line will contain numbers from a
667 different warehouse, so that a subtotal line can be added.
669 The last row will contain the running qty total (run_qty) and the running total
670 stock value (run_stock_value) over all warehouses/bins and can be used to add a
671 line for the grand totals.
673 =item C<items_lastcost_sum>
675 Non-recursive lastcost sum of all the items in an assembly or assortment.
677 =item C<get_stock %params>
679 Fetches stock qty in the default unit for a part.
681 bin_id and warehouse_id may be passed as params. If only a bin_id is passed,
682 the stock qty for that bin is returned. If only a warehouse_id is passed, the
683 stock qty for all bins in that warehouse is returned. If a shippingdate is
684 passed the stock qty for that date is returned.
687 my $qty = $part->get_stock(bin_id => 52);
689 $part->get_stock(shippingdate => DateTime->today->add(days => -5));
695 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>,
696 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>