|
package SL::DB::Manager::Part;
|
|
|
|
use strict;
|
|
|
|
use SL::DB::Helper::Manager;
|
|
use SL::DB::Helper::Sorted;
|
|
use SL::DB::Helper::Paginated;
|
|
use SL::DB::Helper::Filtered;
|
|
use base qw(SL::DB::Helper::Manager);
|
|
|
|
use Carp;
|
|
use SL::DBUtils;
|
|
use SL::MoreCommon qw(listify);
|
|
|
|
sub object_class { 'SL::DB::Part' }
|
|
|
|
__PACKAGE__->make_manager_methods;
|
|
__PACKAGE__->add_filter_specs(
|
|
part_type => sub {
|
|
my ($key, $value, $prefix) = @_;
|
|
return __PACKAGE__->type_filter($value, $prefix);
|
|
},
|
|
all => sub {
|
|
my ($key, $value, $prefix) = @_;
|
|
return or => [ map { $prefix . $_ => $value } qw(partnumber description ean) ]
|
|
},
|
|
all_with_makemodel => sub {
|
|
my ($key, $value, $prefix) = @_;
|
|
return or => [ map { $prefix . $_ => $value } qw(partnumber description ean makemodels.model) ],
|
|
$prefix . 'makemodels';
|
|
},
|
|
all_with_customer_partnumber => sub {
|
|
my ($key, $value, $prefix) = @_;
|
|
return or => [ map { $prefix . $_ => $value } qw(partnumber description ean customerprices.customer_partnumber) ],
|
|
$prefix . 'customerprices';
|
|
},
|
|
price_change_printed => sub {
|
|
my ($key, $value, $prefix) = @_;
|
|
die "value must be a scalar ref to a hash ref"
|
|
unless ref $value eq 'REF' && ref ($$value) eq 'HASH';
|
|
|
|
my %value = %$$value;
|
|
|
|
my $template = $value{template};
|
|
my $print_type = $value{print_type};
|
|
my $printed = $value{printed};
|
|
|
|
my $comp = !!$printed ? '>' : '<=';
|
|
|
|
# table part_table is aliased as t1
|
|
return
|
|
[\qq{(
|
|
SELECT DISTINCT CASE WHEN count(*) $comp 1 THEN FALSE ELSE TRUE END
|
|
FROM (
|
|
(
|
|
-- last printed or first price
|
|
SELECT t2_sellprice
|
|
FROM (
|
|
(
|
|
SELECT
|
|
parts_price_history.sellprice AS t2_sellprice,
|
|
parts_price_history.valid_from AS t2_valid_from,
|
|
parts_price_history.id as t2_id
|
|
FROM parts_price_history
|
|
JOIN part_label_prints
|
|
ON (parts_price_history.id = part_label_prints.price_history_id)
|
|
WHERE parts_price_history.part_id = t1.id AND (
|
|
('' = ? OR part_label_prints.template = ?) AND
|
|
('' = ? OR part_label_prints.print_type = ?)
|
|
)
|
|
ORDER by
|
|
parts_price_history.valid_from DESC,
|
|
parts_price_history.id DESC
|
|
LIMIT 1
|
|
) UNION (
|
|
SELECT
|
|
parts_price_history.sellprice AS t2_sellprice,
|
|
parts_price_history.valid_from AS t2_valid_from,
|
|
parts_price_history.id as t2_id
|
|
FROM parts_price_history
|
|
WHERE part_id = t1.id
|
|
ORDER BY
|
|
parts_price_history.valid_from ASC,
|
|
parts_price_history.id ASC
|
|
LIMIT 1
|
|
)
|
|
)
|
|
ORDER by
|
|
t2_valid_from DESC,
|
|
t2_id DESC
|
|
LIMIT 1
|
|
) UNION (
|
|
-- current price
|
|
SELECT parts_price_history.sellprice AS t2_sellprice
|
|
FROM parts_price_history
|
|
WHERE part_id = t1.id
|
|
ORDER BY
|
|
parts_price_history.valid_from DESC,
|
|
parts_price_history.id
|
|
DESC LIMIT 1
|
|
)
|
|
)
|
|
)} => ($template, $template, $print_type, $print_type || 'stock')
|
|
] => \'TRUE';
|
|
},
|
|
);
|
|
|
|
sub type_filter {
|
|
my ($class, $type, $prefix) = @_;
|
|
|
|
return () unless $type;
|
|
|
|
$prefix //= '';
|
|
|
|
# this is to make selections like part_type => { part => 1, service => 1 } work
|
|
if ('HASH' eq ref $type) {
|
|
$type = [ grep { $type->{$_} } keys %$type ];
|
|
}
|
|
|
|
my @types = grep { $_ } listify($type);
|
|
my @filter;
|
|
|
|
for my $type (@types) {
|
|
if ($type =~ m/^part/) {
|
|
push @filter, ($prefix . part_type => 'part');
|
|
} elsif ($type =~ m/^service/) {
|
|
push @filter, ($prefix . part_type => 'service');
|
|
} elsif ($type =~ m/^assembly/) {
|
|
push @filter, ($prefix . part_type => 'assembly');
|
|
} elsif ($type =~ m/^assortment/) {
|
|
push @filter, ($prefix . part_type => 'assortment');
|
|
}
|
|
}
|
|
|
|
return @filter > 2 ? (or => \@filter) : @filter;
|
|
}
|
|
|
|
sub get_ordered_qty {
|
|
my $class = shift;
|
|
my @part_ids = @_;
|
|
|
|
return () unless @part_ids;
|
|
|
|
my $placeholders = join ',', ('?') x @part_ids;
|
|
my $query = <<SQL;
|
|
SELECT oi.parts_id, SUM(oi.base_qty) AS qty
|
|
FROM orderitems oi
|
|