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();
projects / kivitendo-erp.git / blobdiff