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' },
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' });
84 __PACKAGE__->attr_sorted({ unsorted => 'customerprices', position => 'sortorder' });
86 __PACKAGE__->before_save('_before_save_set_partnumber');
88 sub _before_save_set_partnumber {
91 $self->create_trans_number if !$self->partnumber;
98 if ( $self->part_type eq 'assembly' ) {
99 return $self->assemblies;
100 } elsif ( $self->part_type eq 'assortment' ) {
101 return $self->assortment_items;
110 # for detecting if the items of an (orphaned) assembly or assortment have
111 # changed when saving
113 return join(' ', sort map { $_->part->id } @{$self->items});
120 push @errors, $::locale->text('The partnumber is missing.') if $self->id and !$self->partnumber;
121 push @errors, $::locale->text('The unit is missing.') unless $self->unit;
122 push @errors, $::locale->text('The buchungsgruppe is missing.') unless $self->buchungsgruppen_id or $self->buchungsgruppe;
124 unless ( $self->id ) {
125 push @errors, $::locale->text('The partnumber already exists.') if SL::DB::Manager::Part->get_all_count(where => [ partnumber => $self->partnumber ]);
128 if ($self->is_assortment && $self->orphaned && scalar @{$self->assortment_items} == 0) {
129 # when assortment isn't orphaned form doesn't contain any items
130 push @errors, $::locale->text('The assortment doesn\'t have any items.');
133 if ($self->is_assembly && scalar @{$self->assemblies} == 0) {
134 push @errors, $::locale->text('The assembly doesn\'t have any items.');
142 my $type = lc(shift || '');
143 die 'invalid type' unless $type =~ /^(?:part|service|assembly|assortment)$/;
145 return $self->type eq $type ? 1 : 0;
148 sub is_part { $_[0]->part_type eq 'part' }
149 sub is_assembly { $_[0]->part_type eq 'assembly' }
150 sub is_service { $_[0]->part_type eq 'service' }
151 sub is_assortment { $_[0]->part_type eq 'assortment' }
154 return $_[0]->part_type;
155 # my ($self, $type) = @_;
157 # die 'invalid type' unless $type =~ /^(?:part|service|assembly)$/;
158 # $self->assembly( $type eq 'assembly' ? 1 : 0);
159 # $self->inventory_accno_id($type ne 'service' ? 1 : undef);
162 # return 'assembly' if $self->assembly;
163 # return 'part' if $self->inventory_accno_id;
168 my ($class, %params) = @_;
169 $class->new(%params, part_type => 'part');
173 my ($class, %params) = @_;
174 $class->new(%params, part_type => 'assembly');
178 my ($class, %params) = @_;
179 $class->new(%params, part_type => 'service');
183 my ($class, %params) = @_;
184 $class->new(%params, part_type => 'assortment');
187 sub last_modification {
189 return $self->mtime // $self->itime;
194 die 'not an accessor' if @_ > 1;
196 return 1 unless $self->id;
201 SL::DB::DeliveryOrderItem
204 for my $class (@relations) {
205 eval "require $class";
206 return 1 if $class->_get_manager_class->get_all_count(query => [ parts_id => $self->id ]);
212 die 'not an accessor' if @_ > 1;
214 return 1 unless $self->id;
219 SL::DB::DeliveryOrderItem
221 SL::DB::AssortmentItem
224 for my $class (@relations) {
225 eval "require $class";
226 return 0 if $class->_get_manager_class->get_all_count(query => [ parts_id => $self->id ]);
231 sub get_sellprice_info {
235 confess "Missing part id" unless $self->id;
237 my $object = $self->load;
239 return { sellprice => $object->sellprice,
240 price_factor_id => $object->price_factor_id };
243 sub get_ordered_qty {
245 my %result = SL::DB::Manager::Part->get_ordered_qty($self->id);
247 return $result{ $self->id };
250 sub available_units {
251 shift->unit_obj->convertible_units;
254 # autogenerated accessor is slightly off...
256 shift->buchungsgruppen(@_);
260 my ($self, %params) = @_;
262 my $date = $params{date} || DateTime->today_local;
263 my $is_sales = !!$params{is_sales};
264 my $taxzone = $params{ defined($params{taxzone}) ? 'taxzone' : 'taxzone_id' } * 1;
265 my $tk_info = $::request->cache('get_taxkey');
267 $tk_info->{$self->id} //= {};
268 $tk_info->{$self->id}->{$taxzone} //= { };
269 my $cache = $tk_info->{$self->id}->{$taxzone}->{$is_sales} //= { };
271 if (!exists $cache->{$date}) {
273 $self->get_chart(type => $is_sales ? 'income' : 'expense', taxzone => $taxzone)
274 ->get_active_taxkey($date);
277 return $cache->{$date};
281 my ($self, %params) = @_;
283 my $type = (any { $_ eq $params{type} } qw(income expense inventory)) ? $params{type} : croak("Invalid 'type' parameter '$params{type}'");
284 my $taxzone = $params{ defined($params{taxzone}) ? 'taxzone' : 'taxzone_id' } * 1;
286 my $charts = $::request->cache('get_chart_id/by_part_id_and_taxzone')->{$self->id} //= {};
287 my $all_charts = $::request->cache('get_chart_id/by_id');
289 $charts->{$taxzone} ||= { };
291 if (!exists $charts->{$taxzone}->{$type}) {
292 require SL::DB::Buchungsgruppe;
293 my $bugru = SL::DB::Buchungsgruppe->load_cached($self->buchungsgruppen_id);
294 my $chart_id = ($type eq 'inventory') ? ($self->is_part ? $bugru->inventory_accno_id : undef)
295 : $bugru->call_sub("${type}_accno_id", $taxzone);
298 my $chart = $all_charts->{$chart_id} // SL::DB::Chart->load_cached($chart_id)->load;
299 $all_charts->{$chart_id} = $chart;
300 $charts->{$taxzone}->{$type} = $chart;
304 return $charts->{$taxzone}->{$type};
308 my ($self, %params) = @_;
310 return undef unless $self->id;
312 my $query = 'SELECT SUM(qty) FROM inventory WHERE parts_id = ?';
313 my @values = ($self->id);
315 if ( $params{bin_id} ) {
316 $query .= ' AND bin_id = ?';
317 push(@values, $params{bin_id});
320 if ( $params{warehouse_id} ) {
321 $query .= ' AND warehouse_id = ?';
322 push(@values, $params{warehouse_id});
325 if ( $params{shippingdate} ) {
326 die unless ref($params{shippingdate}) eq 'DateTime';
327 $query .= ' AND shippingdate <= ?';
328 push(@values, $params{shippingdate});
331 my ($stock) = selectrow_query($::form, $self->db->dbh, $query, @values);
333 return $stock || 0; # never return undef
337 # this is designed to ignore chargenumbers, expiration dates and just give a list of how much <-> where
338 sub get_simple_stock {
339 my ($self, %params) = @_;
341 return [] unless $self->id;
344 SELECT sum(qty), warehouse_id, bin_id FROM inventory WHERE parts_id = ?
345 GROUP BY warehouse_id, bin_id
347 my $stock_info = selectall_hashref_query($::form, $::form->get_standard_dbh, $query, $self->id);
348 [ map { bless $_, 'SL::DB::Part::SimpleStock'} @$stock_info ];
350 # helper class to have bin/warehouse accessors in stock result
351 { package SL::DB::Part::SimpleStock;
352 sub warehouse { require SL::DB::Warehouse; SL::DB::Manager::Warehouse->find_by_or_create(id => $_[0]->{warehouse_id}) }
353 sub bin { require SL::DB::Bin; SL::DB::Manager::Bin ->find_by_or_create(id => $_[0]->{bin_id}) }
356 sub get_simple_stock_sql {
357 my ($self, %params) = @_;
359 return [] unless $self->id;
362 SELECT w.description AS warehouse_description,
363 b.description AS bin_description,
365 SUM(i.qty * p.lastcost) AS stock_value,
367 LEAD(w.description) OVER pt AS wh_lead, -- to detect warehouse changes for subtotals in template
368 SUM( SUM(i.qty) ) OVER pt AS run_qty, -- running total of total qty
369 SUM( SUM(i.qty) ) OVER wh AS wh_run_qty, -- running total of warehouse qty
370 SUM( SUM(i.qty * p.lastcost)) OVER pt AS run_stock_value, -- running total of total stock_value
371 SUM( SUM(i.qty * p.lastcost)) OVER wh AS wh_run_stock_value -- running total of warehouse stock_value
373 LEFT JOIN parts p ON (p.id = i.parts_id)
374 LEFT JOIN warehouse w ON (i.warehouse_id = w.id)
375 LEFT JOIN bin b ON (i.bin_id = b.id)
377 GROUP BY w.description, w.sortkey, b.description, p.unit, i.parts_id
379 WINDOW pt AS (PARTITION BY i.parts_id ORDER BY w.sortkey, b.description, p.unit),
380 wh AS (PARTITION by w.description ORDER BY w.sortkey, b.description, p.unit)
381 ORDER BY w.sortkey, b.description, p.unit
384 my $stock_info = selectall_hashref_query($::form, $self->db->dbh, $query, $self->id);
388 sub get_mini_journal {
391 # inventory ids of the most recent 10 inventory trans_ids
393 # duplicate code copied from SL::Controller::Inventory mini_journal, except
394 # for the added filter on parts_id
396 my $parts_id = $self->id;
398 with last_inventories as (
403 where parts_id = $parts_id
410 from last_inventories
417 limit 20 -- so the planner knows how many ids to expect, the cte is an optimisation fence
420 my $objs = SL::DB::Manager::Inventory->get_all(
421 query => [ id => [ \"$query" ] ],
422 with_objects => [ 'parts', 'trans_type', 'bin', 'bin.warehouse' ], # prevent lazy loading in template
423 sort_by => 'itime DESC',
425 # remember order of trans_ids from query, for ordering hash later
426 my @sorted_trans_ids = uniq map { $_->trans_id } @$objs;
428 # at most 2 of them belong to a transaction and the qty determines in or out.
431 $transactions{ $_->trans_id }{ $_->qty > 0 ? 'in' : 'out' } = $_;
432 $transactions{ $_->trans_id }{base} = $_;
435 # because the inventory transactions were built in a hash, we need to sort the
436 # hash by using the original sort order of the trans_ids
437 my @sorted = map { $transactions{$_} } @sorted_trans_ids;
442 sub clone_and_reset_deep {
445 my $clone = $self->clone_and_reset; # resets id and partnumber (primary key and unique constraint)
446 $clone->makemodels( map { $_->clone_and_reset } @{$self->makemodels} ) if @{$self->makemodels};
447 $clone->translations( map { $_->clone_and_reset } @{$self->translations} ) if @{$self->translations};
449 if ( $self->is_assortment ) {
450 # use clone rather than reset_and_clone because the unique constraint would also remove parts_id
451 $clone->assortment_items( map { $_->clone } @{$self->assortment_items} );
452 $_->assortment_id(undef) foreach @{ $clone->assortment_items }
455 if ( $self->is_assembly ) {
456 $clone->assemblies( map { $_->clone_and_reset } @{$self->assemblies});
459 if ( $self->prices ) {
460 $clone->prices( map { $_->clone } @{$self->prices}); # pricegroup_id gets reset here because it is part of a unique contraint
461 if ( $clone->prices ) {
462 foreach my $price ( @{$clone->prices} ) {
464 $price->parts_id(undef);
473 my ($self, $comparison_part) = @_;
475 die "item_diffs needs a part object" unless ref($comparison_part) eq 'SL::DB::Part';
476 die "part and comparison_part need to be of the same part_type" unless
477 ( $self->part_type eq 'assembly' or $self->part_type eq 'assortment' )
478 and ( $comparison_part->part_type eq 'assembly' or $comparison_part->part_type eq 'assortment' )
479 and $self->part_type eq $comparison_part->part_type;
481 # return [], [] if $self->items_checksum eq $comparison_part->items_checksum;
482 my @self_part_ids = map { $_->parts_id } $self->items;
483 my @comparison_part_ids = map { $_->parts_id } $comparison_part->items;
485 my %orig = map{ $_ => 1 } @self_part_ids;
486 my %comparison = map{ $_ => 1 } @comparison_part_ids;
487 my (@additions, @removals);
488 @additions = grep { !exists( $orig{$_} ) } @comparison_part_ids if @comparison_part_ids;
489 @removals = grep { !exists( $comparison{$_} ) } @self_part_ids if @self_part_ids;
491 return \@additions, \@removals;
494 sub items_sellprice_sum {
495 my ($self, %params) = @_;
497 return unless $self->is_assortment or $self->is_assembly;
498 return unless $self->items;
500 if ($self->is_assembly) {
501 return sum map { $_->linetotal_sellprice } @{$self->items};
503 return sum map { $_->linetotal_sellprice(%params) } grep { $_->charge } @{$self->items};
507 sub items_lastcost_sum {
510 return unless $self->is_assortment or $self->is_assembly;
511 return unless $self->items;
512 sum map { $_->linetotal_lastcost } @{$self->items};
525 SL::DB::Part: Model for the 'parts' table
529 This is a standard Rose::DB::Object based model and can be used as one.
533 Although the base class is called C<Part> we usually talk about C<Articles> if
534 we mean instances of this class. This is because articles come in three
539 =item Part - a single part
541 =item Service - a part without onhand, and without inventory accounting
543 =item Assembly - a collection of both parts and services
545 =item Assortment - a collection of items (parts or assemblies)
549 These types are sadly represented by data inside the class and cannot be
550 migrated into a flag. To work around this, each C<Part> object knows what type
551 it currently is. Since the type is data driven, there ist no explicit setting
552 method for it, but you can construct them explicitly with C<new_part>,
553 C<new_service>, C<new_assembly> and C<new_assortment>. A Buchungsgruppe should be supplied in this
554 case, but it will use the default Buchungsgruppe if you don't.
556 Matching these there are assorted helper methods dealing with types,
557 e.g. L</new_part>, L</new_service>, L</new_assembly>, L</type>,
558 L</is_type> and others.
564 =item C<new_part %PARAMS>
566 =item C<new_service %PARAMS>
568 =item C<new_assembly %PARAMS>
570 Will set the appropriate data fields so that the resulting instance will be of
571 the requested type. Since accounting targets are part of the distinction,
572 providing a C<Buchungsgruppe> is recommended. If none is given the constructor
573 will load a default one and set the accounting targets from it.
577 Returns the type as a string. Can be one of C<part>, C<service>, C<assembly>.
579 =item C<is_type $TYPE>
581 Tests if the current object is a part, a service or an
582 assembly. C<$type> must be one of the words 'part', 'service' or
583 'assembly' (their plurals are ok, too).
585 Returns 1 if the requested type matches, 0 if it doesn't and
586 C<confess>es if an unknown C<$type> parameter is encountered.
594 Shorthand for C<is_type('part')> etc.
596 =item C<get_sellprice_info %params>
598 Retrieves the C<sellprice> and C<price_factor_id> for a part under
599 different conditions and returns a hash reference with those two keys.
601 If C<%params> contains a key C<project_id> then a project price list
602 will be consulted if one exists for that project. In this case the
603 parameter C<country_id> is evaluated as well: if a price list entry
604 has been created for this country then it will be used. Otherwise an
605 entry without a country set will be used.
607 If none of the above conditions is met then the information from
610 =item C<get_ordered_qty %params>
612 Retrieves the quantity that has been ordered from a vendor but that
613 has not been delivered yet. Only open purchase orders are considered.
615 =item C<get_taxkey %params>
617 Retrieves and returns a taxkey object valid for the given date
618 C<$params{date}> and tax zone C<$params{taxzone}>
619 (C<$params{taxzone_id}> is also recognized). The date defaults to the
620 current date if undefined.
622 This function looks up the income (for trueish values of
623 C<$params{is_sales}>) or expense (for falsish values of
624 C<$params{is_sales}>) account for the current part. It uses the part's
625 associated buchungsgruppe and uses the fields belonging to the tax
626 zone given by C<$params{taxzone}>.
628 The information retrieved by the function is cached.
630 =item C<get_chart %params>
632 Retrieves and returns a chart object valid for the given type
633 C<$params{type}> and tax zone C<$params{taxzone}>
634 (C<$params{taxzone_id}> is also recognized). The type must be one of
635 the three key words C<income>, C<expense> and C<inventory>.
637 This function uses the part's associated buchungsgruppe and uses the
638 fields belonging to the tax zone given by C<$params{taxzone}>.
640 The information retrieved by the function is cached.
642 =item C<used_in_record>
644 Checks if this article has been used in orders, invoices or delivery orders.
648 Checks if this article is used in orders, invoices, delivery orders or
651 =item C<buchungsgruppe BUCHUNGSGRUPPE>
653 Used to set the accounting information from a L<SL:DB::Buchungsgruppe> object.
654 Please note, that this is a write only accessor, the original Buchungsgruppe can
655 not be retrieved from an article once set.
657 =item C<get_simple_stock_sql>
659 Fetches the qty and the stock value for the current part for each bin and
660 warehouse where the part is in stock (or rather different from 0, might be
663 Runs some additional window functions to add the running totals (total running
664 total and total per warehouse) for qty and stock value to each line.
666 Using the LEAD(w.description) the template can check if the warehouse
667 description is about to change, i.e. the next line will contain numbers from a
668 different warehouse, so that a subtotal line can be added.
670 The last row will contain the running qty total (run_qty) and the running total
671 stock value (run_stock_value) over all warehouses/bins and can be used to add a
672 line for the grand totals.
674 =item C<items_lastcost_sum>
676 Non-recursive lastcost sum of all the items in an assembly or assortment.
678 =item C<get_stock %params>
680 Fetches stock qty in the default unit for a part.
682 bin_id and warehouse_id may be passed as params. If only a bin_id is passed,
683 the stock qty for that bin is returned. If only a warehouse_id is passed, the
684 stock qty for all bins in that warehouse is returned. If a shippingdate is
685 passed the stock qty for that date is returned.
688 my $qty = $part->get_stock(bin_id => 52);
690 $part->get_stock(shippingdate => DateTime->today->add(days => -5));
696 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>,
697 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>