From bce08af4ca2b6a160505dedd3a28edb455e1de7a Mon Sep 17 00:00:00 2001
From: =?utf8?q?Sven=20Sch=C3=B6ling?=
Date: Wed, 25 Jan 2017 14:47:05 +0100
Subject: [PATCH] Preisupdate in eigenen controller verlagert
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit
...und dabei das völlig kaputte Exceptionhandling gefixt
---
SL/Controller/PartsPriceUpdate.pm | 281 ++++++++++++++++++
SL/IC.pm | 226 --------------
bin/mozilla/ic.pl | 77 -----
menus/user/00-erp.yaml | 3 +-
.../webpages/ic/confirm_price_update.html | 15 +-
.../webpages/ic/search_update_prices.html | 213 +++++++------
6 files changed, 393 insertions(+), 422 deletions(-)
create mode 100644 SL/Controller/PartsPriceUpdate.pm
diff --git a/SL/Controller/PartsPriceUpdate.pm b/SL/Controller/PartsPriceUpdate.pm
new file mode 100644
index 000000000..6d131f10e
--- /dev/null
+++ b/SL/Controller/PartsPriceUpdate.pm
@@ -0,0 +1,281 @@
+package SL::Controller::PartsPriceUpdate;
+
+use strict;
+use parent qw(SL::Controller::Base);
+
+use SL::DBUtils qw(prepare_query selectfirst_array_query prepare_query do_statement do_query);
+use SL::JSON;
+use SL::Helper::Flash qw(flash);
+use SL::DB;
+use SL::DB::Part;
+use SL::DB::Pricegroup;
+use SL::Locale::String qw(t8);
+
+use Rose::Object::MakeMethods::Generic (
+ 'scalar --get_set_init' => [ qw(pricegroups pricegroups_by_id filter) ],
+);
+
+__PACKAGE__->run_before('check_rights');
+
+
+sub action_search_update_prices {
+ my ($self) = @_;
+
+ $self->render('ic/search_update_prices',
+ title => t8('Update Prices'),
+ );
+}
+
+sub action_confirm_price_update {
+ my ($self) = @_;
+
+ my @errors;
+ my $found;
+
+ for my $key (keys %{ $self->filter->{prices} || {} }) {
+ my $row = $self->filter->{prices}{$key};
+
+ next if $row->{price_as_number} eq '';
+
+ my $type = $row->{type};
+ my $value = $::form->parse_amount(\%::myconfig, $row->{price_as_number});
+ my $name = $key =~ /^\d+$/ ? $self->pricegroups_by_id->{$key}->pricegroup
+ : $key eq 'sellprice' ? t8('Sell Price')
+ : $key eq 'listprice' ? t8('List Price')
+ : '';
+
+ if (0 > $value && ($type eq 'percent')) {
+ push @errors, t8('You cannot adjust the price for pricegroup "#1" by a negative percentage.', $name);
+ } elsif (!$value) {
+ push @errors, t8('No valid number entered for pricegroup "#1".', $name);
+ } elsif (0 < $value) {
+ $found = 1;
+ }
+ }
+
+ push @errors, t8('No prices will be updated because no prices have been entered.') if !$found;
+
+ my $num_matches = $self->get_num_matches_for_priceupdate();
+
+ if (@errors) {
+ flash('error', $_) for @errors;
+ return $self->action_search_update_prices;
+ } else {
+
+ my $key = $::auth->create_unique_sesion_value(SL::JSON::to_json($self->filter));
+
+ $self->render('ic/confirm_price_update',
+ num_matches => $num_matches,
+ filter_key => $key,
+ );
+ }
+}
+
+sub action_update_prices {
+ my ($self) = @_;
+
+ my $num_updated = $self->do_update_prices;
+
+ if ($num_updated) {
+ $::form->redirect(t8('#1 prices were updated.', $num_updated));
+ } else {
+ $::form->error(t8('Could not update prices!'));
+ }
+}
+
+sub _create_filter_for_priceupdate {
+ my ($self) = @_;
+ my $filter = $self->filter;
+
+ my @where_values;
+ my $where = '1 = 1';
+
+ for my $item (qw(partnumber drawing microfiche make model pg.partsgroup description serialnumber)) {
+ my $column = $item;
+ $column =~ s/.*\.//;
+ next unless $filter->{$column};
+
+ $where .= qq| AND $item ILIKE ?|;
+ push @where_values, "%$filter->{$column}%";
+ }
+
+ # items which were never bought, sold or on an order
+ if ($filter->{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
+ UNION
+ SELECT DISTINCT parts_id FROM delivery_order_items
+ )|;
+
+ } elsif ($filter->{itemstatus} eq 'active') {
+ $where .= qq| AND p.obsolete = '0'|;
+
+ } elsif ($filter->{itemstatus} eq 'obsolete') {
+ $where .= qq| AND p.obsolete = '1'|;
+
+ } elsif ($filter->{itemstatus} eq 'onhand') {
+ $where .= qq| AND p.onhand > 0|;
+
+ } elsif ($filter->{itemstatus} eq 'short') {
+ $where .= qq| AND p.onhand < p.rop|;
+
+ }
+
+ for my $column (qw(make model)) {
+ next unless ($filter->{$column});
+ $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;
+ push @where_values, "%$filter->{$column}%";
+ }
+
+ return ($where, @where_values);
+}
+
+sub get_num_matches_for_priceupdate {
+ my ($self) = @_;
+ my $filter = $self->filter;
+ my $dbh = SL::DB->client->dbh;
+ my ($where, @where_values) = $self->_create_filter_for_priceupdate;
+
+ my $num_updated = 0;
+ my $query;
+
+ for my $column (qw(sellprice listprice)) {
+ next if $filter->{prices}{$column}{price_as_number} 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);
+ }
+
+ my @ids = grep { $filter->{prices}{$_}{price_as_number} } map { $_->id } @{ $self->pricegroups };
+ if (@ids) {
+ $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 IN (@{[ join ',', ('?')x@ids ]})|;
+
+ my ($result) = selectfirst_array_query($::form, $dbh, $query, @where_values, @ids);
+ $num_updated += $result if (0 <= $result);
+ }
+
+ return $num_updated;
+}
+
+sub do_update_prices {
+ SL::DB->client->with_transaction(\&_update_prices, $_[0]);
+}
+
+sub _update_prices {
+ my ($self) = @_;
+ my $filter_json = $::auth->get_session_value($::form->{filter_key});
+ my $filter = SL::JSON::from_json($filter_json);
+ $self->filter($filter);
+ die "missing filter" unless $filter;
+
+ 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)) {
+ my $row = $filter->{prices}{$column};
+ next if ($row->{price_as_number} eq "");
+
+ my $value = $::form->parse_amount(\%::myconfig, $row->{price_as_number});
+ my $operator = '+';
+
+ if ($row->{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 $pg (@{ $self->pricegroups }) {
+ my $row = $filter->{prices}{$pg->id};
+ next if $row->{price_as_number} eq "";
+
+ my $value = $::form->parse_amount(\%::myconfig, $row->{price_as_number});
+ my $result;
+
+ if ($row->{type} eq "percent") {
+ $result = do_statement($::form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, $pg->id);
+ } else {
+ $result = do_statement($::form, $sth_add, $q_add, $value, @where_values, $pg->id);
+ }
+
+ $num_updated += $result if (0 <= $result);
+ }
+
+ $sth_add->finish;
+ $sth_multiply->finish;
+
+ 1;
+}
+
+sub init_pricegroups {
+ SL::DB::Manager::Pricegroup->get_all_sorted(query => [
+ obsolete => 0,
+ ]);
+}
+
+sub init_pricegroups_by_id {
+ +{ map { $_->id => $_ } @{ $_[0]->pricegroups } }
+}
+
+sub check_rights {
+ $::auth->assert('part_service_assembly_edit');
+}
+
+sub init_filter {
+ $::form->{filter} || {};
+}
+
+1;
diff --git a/SL/IC.pm b/SL/IC.pm
index 08dfe1237..cf137928b 100644
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -49,33 +49,6 @@ use Carp;
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();
@@ -92,7 +65,6 @@ sub retrieve_buchungsgruppen {
$main::lxdebug->leave_sub();
}
-
sub assembly_item {
$main::lxdebug->enter_sub();
@@ -605,204 +577,6 @@ SQL
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();
diff --git a/bin/mozilla/ic.pl b/bin/mozilla/ic.pl
index 022422b1c..2aae5cdd5 100644
--- a/bin/mozilla/ic.pl
+++ b/bin/mozilla/ic.pl
@@ -102,83 +102,6 @@ sub search {
$lxdebug->leave_sub();
} #end search()
-sub search_update_prices {
- $lxdebug->enter_sub();
-
- $auth->assert('part_service_assembly_edit');
-
- my $pricegroups = IC->get_pricegroups(\%myconfig, \%$form);
-
- $form->{title} = $locale->text('Update Prices');
-
- $form->header;
-
- print $form->parse_html_template('ic/search_update_prices', { PRICE_ROWS => $pricegroups });
-
- $lxdebug->leave_sub();
-} #end search()
-
-sub confirm_price_update {
- $lxdebug->enter_sub();
-
- $auth->assert('part_service_assembly_edit');
-
- my @errors = ();
- my $value_found = undef;
-
- foreach my $idx (qw(sellprice listprice), (1..$form->{price_rows})) {
- my $name = $idx =~ m/\d/ ? $form->{"pricegroup_${idx}"} : $idx eq 'sellprice' ? $locale->text('Sell Price') : $locale->text('List Price');
- my $type = $idx =~ m/\d/ ? $form->{"pricegroup_type_${idx}"} : $form->{"${idx}_type"};
- my $value_idx = $idx =~ m/\d/ ? "price_${idx}" : $idx;
- my $value = $form->parse_amount(\%myconfig, $form->{$value_idx});
-
- if ((0 > $value) && ($type eq 'percent')) {
- push @errors, $locale->text('You cannot adjust the price for pricegroup "#1" by a negative percentage.', $name);
-
- } elsif (!$value && ($form->{$value_idx} ne '')) {
- push @errors, $locale->text('No valid number entered for pricegroup "#1".', $name);
-
- } elsif (0 < $value) {
- $value_found = 1;
- }
- }
-
- push @errors, $locale->text('No prices will be updated because no prices have been entered.') if (!$value_found);
-
- my $num_matches = IC->get_num_matches_for_priceupdate();
-
- $form->header();
-
- if (@errors) {
- $form->show_generic_error(join('
', @errors));
- }
-
- $form->{nextsub} = "update_prices";
-
- map { delete $form->{$_} } qw(action header);
-
- print $form->parse_html_template('ic/confirm_price_update', { HIDDENS => [ map { name => $_, value => $form->{$_} }, keys %$form ],
- num_matches => $num_matches });
-
- $lxdebug->leave_sub();
-}
-
-sub update_prices {
- $lxdebug->enter_sub();
-
- $auth->assert('part_service_assembly_edit');
-
- my $num_updated = IC->update_prices(\%myconfig, \%$form);
-
- if (-1 != $num_updated) {
- $form->redirect($locale->text('#1 prices were updated.', $num_updated));
- } else {
- $form->error($locale->text('Could not update prices!'));
- }
-
- $lxdebug->leave_sub();
-}
-
sub top100 {
$::lxdebug->enter_sub();
diff --git a/menus/user/00-erp.yaml b/menus/user/00-erp.yaml
index 0713796fe..aaa3e8e1b 100644
--- a/menus/user/00-erp.yaml
+++ b/menus/user/00-erp.yaml
@@ -99,9 +99,8 @@
icon: prices_update
order: 800
access: part_service_assembly_edit
- module: ic.pl
params:
- action: search_update_prices
+ action: PartsPriceUpdate/search_update_prices
- parent: master_data
id: master_data_price_rules
name: Price Rules
diff --git a/templates/webpages/ic/confirm_price_update.html b/templates/webpages/ic/confirm_price_update.html
index 910c91a4c..ae2e0ace4 100644
--- a/templates/webpages/ic/confirm_price_update.html
+++ b/templates/webpages/ic/confirm_price_update.html
@@ -1,23 +1,20 @@
[%- USE T8 %]
[%- USE HTML %]
[%- USE LxERP %]
+[%- USE L %]
-
diff --git a/templates/webpages/ic/search_update_prices.html b/templates/webpages/ic/search_update_prices.html
index 6c492202a..3c60782c7 100644
--- a/templates/webpages/ic/search_update_prices.html
+++ b/templates/webpages/ic/search_update_prices.html
@@ -1,114 +1,111 @@
[%- USE T8 %]
[%- USE HTML %]
[%- USE LxERP %]
+[%- USE L %]
[% 'Update prices' | $T8 %]
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+[% PROCESS 'common/flash.html' %]
+
+
--
2.20.1