Projekt

Allgemein

Profil

Herunterladen (5,12 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/SL/DB/Manager/Part.pm @ 92d44251

82515b2d Sven Schöling
package SL::DB::Manager::Part;

use strict;

ac552280 Moritz Bunkus
use SL::DB::Helper::Manager;
9d391728 Sven Schöling
use SL::DB::Helper::Sorted;
use SL::DB::Helper::Paginated;
use SL::DB::Helper::Filtered;
ac552280 Moritz Bunkus
use base qw(SL::DB::Helper::Manager);
82515b2d Sven Schöling
use Carp;
use SL::DBUtils;
60009b97 Sven Schöling
use SL::MoreCommon qw(listify);
82515b2d Sven Schöling
sub object_class { 'SL::DB::Part' }

__PACKAGE__->make_manager_methods;
9d391728 Sven Schöling
__PACKAGE__->add_filter_specs(
862bc8ce Geoffrey Richardson
part_type => sub {
9d391728 Sven Schöling
my ($key, $value, $prefix) = @_;
return __PACKAGE__->type_filter($value, $prefix);
},
all => sub {
my ($key, $value, $prefix) = @_;
87b5c8e4 Bernd Bleßmann
return or => [ map { $prefix . $_ => $value } qw(partnumber description ean) ]
88d774c6 Bernd Bleßmann
},
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';
},
9d391728 Sven Schöling
);
82515b2d Sven Schöling
sub type_filter {
9d391728 Sven Schöling
my ($class, $type, $prefix) = @_;
60009b97 Sven Schöling
return () unless $type;

9d391728 Sven Schöling
$prefix //= '';

98b64fe1 Geoffrey Richardson
# this is to make selections like part_type => { part => 1, service => 1 } work
9d391728 Sven Schöling
if ('HASH' eq ref $type) {
84ad2fe8 Moritz Bunkus
$type = [ grep { $type->{$_} } keys %$type ];
9d391728 Sven Schöling
}

80d5f170 Moritz Bunkus
my @types = grep { $_ } listify($type);
60009b97 Sven Schöling
my @filter;

for my $type (@types) {
if ($type =~ m/^part/) {
98b64fe1 Geoffrey Richardson
push @filter, ($prefix . part_type => 'part');
60009b97 Sven Schöling
} elsif ($type =~ m/^service/) {
98b64fe1 Geoffrey Richardson
push @filter, ($prefix . part_type => 'service');
} elsif ($type =~ m/^assembly/) {
push @filter, ($prefix . part_type => 'assembly');
65de6f61 Geoffrey Richardson
} elsif ($type =~ m/^assortment/) {
push @filter, ($prefix . part_type => 'assortment');
60009b97 Sven Schöling
}
82515b2d Sven Schöling
}

35d63a9f Moritz Bunkus
return @filter > 2 ? (or => \@filter) : @filter;
82515b2d Sven Schöling
}

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
LEFT JOIN oe ON (oi.trans_id = oe.id)
WHERE (oi.parts_id IN ($placeholders))
6c8eb668 Tamino Steinert
AND oe.record_type = 'purchase_order'
82515b2d Sven Schöling
AND (NOT COALESCE(oe.closed, FALSE))
AND (NOT COALESCE(oe.delivered, FALSE))
GROUP BY oi.parts_id
SQL

my %qty_by_id = map { $_->{parts_id} => $_->{qty} * 1 } @{ selectall_hashref_query($::form, $class->object_class->init_db->dbh, $query, @part_ids) };
map { $qty_by_id{$_} ||= 0 } @part_ids;

return %qty_by_id;
}

e13733db Tamino Steinert
sub get_open_ordered_qty {
my $class = shift;
my $part_id = shift;
return () unless $part_id;

my $query = <<SQL;
WITH
open_qty AS (
SELECT parts_id, sum(oi.qty) as sum
FROM orderitems oi
LEFT OUTER JOIN oe o ON (oi.trans_id = o.id)
WHERE
oi.parts_id = ?
AND (NOT COALESCE(o.quotation, FALSE))
AND (NOT COALESCE(o.closed, FALSE))
AND (NOT COALESCE(o.delivered, FALSE))
AND (COALESCE(o.vendor_id, 0) <> 0)
GROUP BY oi.parts_id
),

open_orderitems_ids AS (
SELECT oi.id, parts_id
FROM orderitems oi
LEFT OUTER JOIN oe o ON (oi.trans_id = o.id)
WHERE
oi.parts_id = ?
AND (NOT COALESCE(o.quotation, FALSE))
AND (NOT COALESCE(o.closed, FALSE))
AND (NOT COALESCE(o.delivered, FALSE))
AND (o.vendor_id is not null)
),

delivered_qty AS (
SELECT parts_id, sum(qty) AS sum
FROM delivery_order_items
WHERE id IN (
SELECT to_id from record_links
WHERE
from_id IN ( SELECT id FROM open_orderitems_ids)
AND from_table = 'orderitems'
AND to_table = 'delivery_order_items'
) AND parts_id = ?
GROUP BY parts_id
),

open_ordered_qty AS (
SELECT
oq.parts_id,
oq.sum AS ordered_sum,
COALESCE(dq.sum,0.00) AS sum,
sum(COALESCE(oq.sum,0.00) - COALESCE(dq.sum,0.00)) AS open_qty
FROM open_qty oq
LEFT JOIN delivered_qty dq ON dq.parts_id = oq.parts_id
GROUP BY oq.parts_id, oq.sum, dq.sum
)

SELECT open_qty FROM open_ordered_qty

SQL

my ($open_qty) = selectfirst_array_query(
$::form, $class->object_class->init_db->dbh,
$query, $part_id, $part_id, $part_id
);

$open_qty ||= 0;
return $open_qty
}

2504ebe1 Sven Schöling
sub _sort_spec {
(
default => [ 'partnumber', 1 ],
columns => {
SIMPLE => 'ALL',
},
nulls => {},
);
}

82515b2d Sven Schöling
1;
6858b66b Moritz Bunkus
__END__

=pod

=encoding utf8

=head1 NAME

SL::DB::Manager::Part - RDBO manager for the C<parts> table

=head1 FUNCTIONS

=over 4

=item C<get_ordered_qty @part_ids>

For each of the given part IDs the ordered quantity is
calculated. This is done by summing over all open purchase orders.

Returns a hash with the part IDs being the keys and the ordered
quantities being the values.

=item C<type_filter @types>

Constructs a partial filter for matching any of the article types
given with C<@types>. The returned partial filter is suitable for a
Rose manager query.

Each type can be either 'C<part>', 'C<service>' or 'C<assembly>'
(their plurals are recognized as well). If multiple types are given
then they're combined with C<OR>.

=back

=head1 BUGS

Nothing here yet.

=head1 AUTHOR

Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>,
Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>

=cut