Revision bce08af4
Von Sven Schöling vor mehr als 8 Jahren hinzugefügt
| SL/IC.pm | ||
|---|---|---|
|
|
||
|
use strict;
|
||
|
|
||
|
sub get_pricegroups {
|
||
|
$main::lxdebug->enter_sub();
|
||
|
|
||
|
my ($self, $myconfig, $form) = @_;
|
||
|
|
||
|
my $dbh = $form->get_standard_dbh;
|
||
|
|
||
|
# get pricegroups
|
||
|
my $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY lower(pricegroup)|;
|
||
|
my $pricegroups = selectall_hashref_query($form, $dbh, $query);
|
||
|
|
||
|
my $i = 1;
|
||
|
foreach my $pg (@{ $pricegroups }) {
|
||
|
$form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
|
||
|
$form->{"pricegroup_id_$i"} = "$pg->{id}";
|
||
|
$form->{"pricegroup_$i"} = "$pg->{pricegroup}";
|
||
|
$i++;
|
||
|
}
|
||
|
|
||
|
#correct rows
|
||
|
$form->{price_rows} = $i - 1;
|
||
|
|
||
|
$main::lxdebug->leave_sub();
|
||
|
|
||
|
return $pricegroups;
|
||
|
}
|
||
|
|
||
|
sub retrieve_buchungsgruppen {
|
||
|
$main::lxdebug->enter_sub();
|
||
|
|
||
| ... | ... | |
|
$main::lxdebug->leave_sub();
|
||
|
}
|
||
|
|
||
|
|
||
|
sub assembly_item {
|
||
|
$main::lxdebug->enter_sub();
|
||
|
|
||
| ... | ... | |
|
return $form->{parts};
|
||
|
}
|
||
|
|
||
|
sub _create_filter_for_priceupdate {
|
||
|
$main::lxdebug->enter_sub();
|
||
|
|
||
|
my $self = shift;
|
||
|
my $myconfig = \%main::myconfig;
|
||
|
my $form = $main::form;
|
||
|
|
||
|
my @where_values;
|
||
|
my $where = '1 = 1';
|
||
|
|
||
|
foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
|
||
|
my $column = $item;
|
||
|
$column =~ s/.*\.//;
|
||
|
next unless ($form->{$column});
|
||
|
|
||
|
$where .= qq| AND $item ILIKE ?|;
|
||
|
push(@where_values, like($form->{$column}));
|
||
|
}
|
||
|
|
||
|
foreach my $item (qw(description serialnumber)) {
|
||
|
next unless ($form->{$item});
|
||
|
|
||
|
$where .= qq| AND (${item} ILIKE ?)|;
|
||
|
push(@where_values, like($form->{$item}));
|
||
|
}
|
||
|
|
||
|
|
||
|
# items which were never bought, sold or on an order
|
||
|
if ($form->{itemstatus} eq 'orphaned') {
|
||
|
$where .=
|
||
|
qq| AND (p.onhand = 0)
|
||
|
AND p.id NOT IN
|
||
|
(
|
||
|
SELECT DISTINCT parts_id FROM invoice
|
||
|
UNION
|
||
|
SELECT DISTINCT parts_id FROM assembly
|
||
|
UNION
|
||
|
SELECT DISTINCT parts_id FROM orderitems
|
||
|
)|;
|
||
|
|
||
|
} elsif ($form->{itemstatus} eq 'active') {
|
||
|
$where .= qq| AND p.obsolete = '0'|;
|
||
|
|
||
|
} elsif ($form->{itemstatus} eq 'obsolete') {
|
||
|
$where .= qq| AND p.obsolete = '1'|;
|
||
|
|
||
|
} elsif ($form->{itemstatus} eq 'onhand') {
|
||
|
$where .= qq| AND p.onhand > 0|;
|
||
|
|
||
|
} elsif ($form->{itemstatus} eq 'short') {
|
||
|
$where .= qq| AND p.onhand < p.rop|;
|
||
|
|
||
|
}
|
||
|
|
||
|
foreach my $column (qw(make model)) {
|
||
|
next unless ($form->{$column});
|
||
|
$where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;
|
||
|
push(@where_values, like($form->{$column}));
|
||
|
}
|
||
|
|
||
|
$main::lxdebug->leave_sub();
|
||
|
|
||
|
return ($where, @where_values);
|
||
|
}
|
||
|
|
||
|
sub get_num_matches_for_priceupdate {
|
||
|
$main::lxdebug->enter_sub();
|
||
|
|
||
|
my $self = shift;
|
||
|
|
||
|
my $myconfig = \%main::myconfig;
|
||
|
my $form = $main::form;
|
||
|
|
||
|
my $dbh = $form->get_standard_dbh($myconfig);
|
||
|
|
||
|
my ($where, @where_values) = $self->_create_filter_for_priceupdate();
|
||
|
|
||
|
my $num_updated = 0;
|
||
|
my $query;
|
||
|
|
||
|
for my $column (qw(sellprice listprice)) {
|
||
|
next if ($form->{$column} eq "");
|
||
|
|
||
|
$query =
|
||
|
qq|SELECT COUNT(*)
|
||
|
FROM parts
|
||
|
WHERE id IN
|
||
|
(SELECT p.id
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
WHERE $where)|;
|
||
|
my ($result) = selectfirst_array_query($form, $dbh, $query, @where_values);
|
||
|
$num_updated += $result if (0 <= $result);
|
||
|
}
|
||
|
|
||
|
$query =
|
||
|
qq|SELECT COUNT(*)
|
||
|
FROM prices
|
||
|
WHERE parts_id IN
|
||
|
(SELECT p.id
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
WHERE $where) AND (pricegroup_id = ?)|;
|
||
|
my $sth = prepare_query($form, $dbh, $query);
|
||
|
|
||
|
for my $i (1 .. $form->{price_rows}) {
|
||
|
next if ($form->{"price_$i"} eq "");
|
||
|
|
||
|
my ($result) = do_statement($form, $sth, $query, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
||
|
$num_updated += $result if (0 <= $result);
|
||
|
}
|
||
|
$sth->finish();
|
||
|
|
||
|
$main::lxdebug->leave_sub();
|
||
|
|
||
|
return $num_updated;
|
||
|
}
|
||
|
|
||
|
sub update_prices {
|
||
|
my ($self, $myconfig, $form) = @_;
|
||
|
$main::lxdebug->enter_sub();
|
||
|
|
||
|
my $num_updated = SL::DB->client->with_transaction(\&_update_prices, $self, $myconfig, $form);
|
||
|
|
||
|
$main::lxdebug->leave_sub();
|
||
|
return $num_updated;
|
||
|
}
|
||
|
|
||
|
sub _update_prices {
|
||
|
my ($self, $myconfig, $form) = @_;
|
||
|
|
||
|
my ($where, @where_values) = $self->_create_filter_for_priceupdate();
|
||
|
my $num_updated = 0;
|
||
|
|
||
|
# connect to database
|
||
|
my $dbh = SL::DB->client->dbh;
|
||
|
|
||
|
for my $column (qw(sellprice listprice)) {
|
||
|
next if ($form->{$column} eq "");
|
||
|
|
||
|
my $value = $form->parse_amount($myconfig, $form->{$column});
|
||
|
my $operator = '+';
|
||
|
|
||
|
if ($form->{"${column}_type"} eq "percent") {
|
||
|
$value = ($value / 100) + 1;
|
||
|
$operator = '*';
|
||
|
}
|
||
|
|
||
|
my $query =
|
||
|
qq|UPDATE parts SET $column = $column $operator ?
|
||
|
WHERE id IN
|
||
|
(SELECT p.id
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
WHERE $where)|;
|
||
|
my $result = do_query($form, $dbh, $query, $value, @where_values);
|
||
|
$num_updated += $result if (0 <= $result);
|
||
|
}
|
||
|
|
||
|
my $q_add =
|
||
|
qq|UPDATE prices SET price = price + ?
|
||
|
WHERE parts_id IN
|
||
|
(SELECT p.id
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
WHERE $where) AND (pricegroup_id = ?)|;
|
||
|
my $sth_add = prepare_query($form, $dbh, $q_add);
|
||
|
|
||
|
my $q_multiply =
|
||
|
qq|UPDATE prices SET price = price * ?
|
||
|
WHERE parts_id IN
|
||
|
(SELECT p.id
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
WHERE $where) AND (pricegroup_id = ?)|;
|
||
|
my $sth_multiply = prepare_query($form, $dbh, $q_multiply);
|
||
|
|
||
|
for my $i (1 .. $form->{price_rows}) {
|
||
|
next if ($form->{"price_$i"} eq "");
|
||
|
|
||
|
my $value = $form->parse_amount($myconfig, $form->{"price_$i"});
|
||
|
my $result;
|
||
|
|
||
|
if ($form->{"pricegroup_type_$i"} eq "percent") {
|
||
|
$result = do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
||
|
} else {
|
||
|
$result = do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
||
|
}
|
||
|
|
||
|
$num_updated += $result if (0 <= $result);
|
||
|
}
|
||
|
|
||
|
$sth_add->finish();
|
||
|
$sth_multiply->finish();
|
||
|
|
||
|
return $num_updated;
|
||
|
}
|
||
|
|
||
|
# get partnumber, description, unit, sellprice and soldtotal with choice through $sortorder for Top100
|
||
|
sub get_parts {
|
||
|
$main::lxdebug->enter_sub();
|
||
Auch abrufbar als: Unified diff
Preisupdate in eigenen controller verlagert
...und dabei das völlig kaputte Exceptionhandling gefixt