Revision bce08af4
Von Sven Schöling vor fast 8 Jahren hinzugefügt
| SL/Controller/PartsPriceUpdate.pm | ||
|---|---|---|
| 1 |
package SL::Controller::PartsPriceUpdate; |
|
| 2 |
|
|
| 3 |
use strict; |
|
| 4 |
use parent qw(SL::Controller::Base); |
|
| 5 |
|
|
| 6 |
use SL::DBUtils qw(prepare_query selectfirst_array_query prepare_query do_statement do_query); |
|
| 7 |
use SL::JSON; |
|
| 8 |
use SL::Helper::Flash qw(flash); |
|
| 9 |
use SL::DB; |
|
| 10 |
use SL::DB::Part; |
|
| 11 |
use SL::DB::Pricegroup; |
|
| 12 |
use SL::Locale::String qw(t8); |
|
| 13 |
|
|
| 14 |
use Rose::Object::MakeMethods::Generic ( |
|
| 15 |
'scalar --get_set_init' => [ qw(pricegroups pricegroups_by_id filter) ], |
|
| 16 |
); |
|
| 17 |
|
|
| 18 |
__PACKAGE__->run_before('check_rights');
|
|
| 19 |
|
|
| 20 |
|
|
| 21 |
sub action_search_update_prices {
|
|
| 22 |
my ($self) = @_; |
|
| 23 |
|
|
| 24 |
$self->render('ic/search_update_prices',
|
|
| 25 |
title => t8('Update Prices'),
|
|
| 26 |
); |
|
| 27 |
} |
|
| 28 |
|
|
| 29 |
sub action_confirm_price_update {
|
|
| 30 |
my ($self) = @_; |
|
| 31 |
|
|
| 32 |
my @errors; |
|
| 33 |
my $found; |
|
| 34 |
|
|
| 35 |
for my $key (keys %{ $self->filter->{prices} || {} }) {
|
|
| 36 |
my $row = $self->filter->{prices}{$key};
|
|
| 37 |
|
|
| 38 |
next if $row->{price_as_number} eq '';
|
|
| 39 |
|
|
| 40 |
my $type = $row->{type};
|
|
| 41 |
my $value = $::form->parse_amount(\%::myconfig, $row->{price_as_number});
|
|
| 42 |
my $name = $key =~ /^\d+$/ ? $self->pricegroups_by_id->{$key}->pricegroup
|
|
| 43 |
: $key eq 'sellprice' ? t8('Sell Price')
|
|
| 44 |
: $key eq 'listprice' ? t8('List Price')
|
|
| 45 |
: ''; |
|
| 46 |
|
|
| 47 |
if (0 > $value && ($type eq 'percent')) {
|
|
| 48 |
push @errors, t8('You cannot adjust the price for pricegroup "#1" by a negative percentage.', $name);
|
|
| 49 |
} elsif (!$value) {
|
|
| 50 |
push @errors, t8('No valid number entered for pricegroup "#1".', $name);
|
|
| 51 |
} elsif (0 < $value) {
|
|
| 52 |
$found = 1; |
|
| 53 |
} |
|
| 54 |
} |
|
| 55 |
|
|
| 56 |
push @errors, t8('No prices will be updated because no prices have been entered.') if !$found;
|
|
| 57 |
|
|
| 58 |
my $num_matches = $self->get_num_matches_for_priceupdate(); |
|
| 59 |
|
|
| 60 |
if (@errors) {
|
|
| 61 |
flash('error', $_) for @errors;
|
|
| 62 |
return $self->action_search_update_prices; |
|
| 63 |
} else {
|
|
| 64 |
|
|
| 65 |
my $key = $::auth->create_unique_sesion_value(SL::JSON::to_json($self->filter)); |
|
| 66 |
|
|
| 67 |
$self->render('ic/confirm_price_update',
|
|
| 68 |
num_matches => $num_matches, |
|
| 69 |
filter_key => $key, |
|
| 70 |
); |
|
| 71 |
} |
|
| 72 |
} |
|
| 73 |
|
|
| 74 |
sub action_update_prices {
|
|
| 75 |
my ($self) = @_; |
|
| 76 |
|
|
| 77 |
my $num_updated = $self->do_update_prices; |
|
| 78 |
|
|
| 79 |
if ($num_updated) {
|
|
| 80 |
$::form->redirect(t8('#1 prices were updated.', $num_updated));
|
|
| 81 |
} else {
|
|
| 82 |
$::form->error(t8('Could not update prices!'));
|
|
| 83 |
} |
|
| 84 |
} |
|
| 85 |
|
|
| 86 |
sub _create_filter_for_priceupdate {
|
|
| 87 |
my ($self) = @_; |
|
| 88 |
my $filter = $self->filter; |
|
| 89 |
|
|
| 90 |
my @where_values; |
|
| 91 |
my $where = '1 = 1'; |
|
| 92 |
|
|
| 93 |
for my $item (qw(partnumber drawing microfiche make model pg.partsgroup description serialnumber)) {
|
|
| 94 |
my $column = $item; |
|
| 95 |
$column =~ s/.*\.//; |
|
| 96 |
next unless $filter->{$column};
|
|
| 97 |
|
|
| 98 |
$where .= qq| AND $item ILIKE ?|; |
|
| 99 |
push @where_values, "%$filter->{$column}%";
|
|
| 100 |
} |
|
| 101 |
|
|
| 102 |
# items which were never bought, sold or on an order |
|
| 103 |
if ($filter->{itemstatus} eq 'orphaned') {
|
|
| 104 |
$where .= |
|
| 105 |
qq| AND (p.onhand = 0) |
|
| 106 |
AND p.id NOT IN |
|
| 107 |
( |
|
| 108 |
SELECT DISTINCT parts_id FROM invoice |
|
| 109 |
UNION |
|
| 110 |
SELECT DISTINCT parts_id FROM assembly |
|
| 111 |
UNION |
|
| 112 |
SELECT DISTINCT parts_id FROM orderitems |
|
| 113 |
UNION |
|
| 114 |
SELECT DISTINCT parts_id FROM delivery_order_items |
|
| 115 |
)|; |
|
| 116 |
|
|
| 117 |
} elsif ($filter->{itemstatus} eq 'active') {
|
|
| 118 |
$where .= qq| AND p.obsolete = '0'|; |
|
| 119 |
|
|
| 120 |
} elsif ($filter->{itemstatus} eq 'obsolete') {
|
|
| 121 |
$where .= qq| AND p.obsolete = '1'|; |
|
| 122 |
|
|
| 123 |
} elsif ($filter->{itemstatus} eq 'onhand') {
|
|
| 124 |
$where .= qq| AND p.onhand > 0|; |
|
| 125 |
|
|
| 126 |
} elsif ($filter->{itemstatus} eq 'short') {
|
|
| 127 |
$where .= qq| AND p.onhand < p.rop|; |
|
| 128 |
|
|
| 129 |
} |
|
| 130 |
|
|
| 131 |
for my $column (qw(make model)) {
|
|
| 132 |
next unless ($filter->{$column});
|
|
| 133 |
$where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|; |
|
| 134 |
push @where_values, "%$filter->{$column}%";
|
|
| 135 |
} |
|
| 136 |
|
|
| 137 |
return ($where, @where_values); |
|
| 138 |
} |
|
| 139 |
|
|
| 140 |
sub get_num_matches_for_priceupdate {
|
|
| 141 |
my ($self) = @_; |
|
| 142 |
my $filter = $self->filter; |
|
| 143 |
my $dbh = SL::DB->client->dbh; |
|
| 144 |
my ($where, @where_values) = $self->_create_filter_for_priceupdate; |
|
| 145 |
|
|
| 146 |
my $num_updated = 0; |
|
| 147 |
my $query; |
|
| 148 |
|
|
| 149 |
for my $column (qw(sellprice listprice)) {
|
|
| 150 |
next if $filter->{prices}{$column}{price_as_number} eq "";
|
|
| 151 |
|
|
| 152 |
$query = |
|
| 153 |
qq|SELECT COUNT(*) |
|
| 154 |
FROM parts |
|
| 155 |
WHERE id IN |
|
| 156 |
(SELECT p.id |
|
| 157 |
FROM parts p |
|
| 158 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 159 |
WHERE $where)|; |
|
| 160 |
my ($result) = selectfirst_array_query($::form, $dbh, $query, @where_values); |
|
| 161 |
$num_updated += $result if (0 <= $result); |
|
| 162 |
} |
|
| 163 |
|
|
| 164 |
my @ids = grep { $filter->{prices}{$_}{price_as_number} } map { $_->id } @{ $self->pricegroups };
|
|
| 165 |
if (@ids) {
|
|
| 166 |
$query = |
|
| 167 |
qq|SELECT COUNT(*) |
|
| 168 |
FROM prices |
|
| 169 |
WHERE parts_id IN |
|
| 170 |
(SELECT p.id |
|
| 171 |
FROM parts p |
|
| 172 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 173 |
WHERE $where) |
|
| 174 |
AND pricegroup_id IN (@{[ join ',', ('?')x@ids ]})|;
|
|
| 175 |
|
|
| 176 |
my ($result) = selectfirst_array_query($::form, $dbh, $query, @where_values, @ids); |
|
| 177 |
$num_updated += $result if (0 <= $result); |
|
| 178 |
} |
|
| 179 |
|
|
| 180 |
return $num_updated; |
|
| 181 |
} |
|
| 182 |
|
|
| 183 |
sub do_update_prices {
|
|
| 184 |
SL::DB->client->with_transaction(\&_update_prices, $_[0]); |
|
| 185 |
} |
|
| 186 |
|
|
| 187 |
sub _update_prices {
|
|
| 188 |
my ($self) = @_; |
|
| 189 |
my $filter_json = $::auth->get_session_value($::form->{filter_key});
|
|
| 190 |
my $filter = SL::JSON::from_json($filter_json); |
|
| 191 |
$self->filter($filter); |
|
| 192 |
die "missing filter" unless $filter; |
|
| 193 |
|
|
| 194 |
my ($where, @where_values) = $self->_create_filter_for_priceupdate; |
|
| 195 |
my $num_updated = 0; |
|
| 196 |
|
|
| 197 |
# connect to database |
|
| 198 |
my $dbh = SL::DB->client->dbh; |
|
| 199 |
|
|
| 200 |
for my $column (qw(sellprice listprice)) {
|
|
| 201 |
my $row = $filter->{prices}{$column};
|
|
| 202 |
next if ($row->{price_as_number} eq "");
|
|
| 203 |
|
|
| 204 |
my $value = $::form->parse_amount(\%::myconfig, $row->{price_as_number});
|
|
| 205 |
my $operator = '+'; |
|
| 206 |
|
|
| 207 |
if ($row->{type} eq "percent") {
|
|
| 208 |
$value = ($value / 100) + 1; |
|
| 209 |
$operator = '*'; |
|
| 210 |
} |
|
| 211 |
|
|
| 212 |
my $query = |
|
| 213 |
qq|UPDATE parts SET $column = $column $operator ? |
|
| 214 |
WHERE id IN |
|
| 215 |
(SELECT p.id |
|
| 216 |
FROM parts p |
|
| 217 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 218 |
WHERE $where)|; |
|
| 219 |
my $result = do_query($::form, $dbh, $query, $value, @where_values); |
|
| 220 |
$num_updated += $result if 0 <= $result; |
|
| 221 |
} |
|
| 222 |
|
|
| 223 |
my $q_add = |
|
| 224 |
qq|UPDATE prices SET price = price + ? |
|
| 225 |
WHERE parts_id IN |
|
| 226 |
(SELECT p.id |
|
| 227 |
FROM parts p |
|
| 228 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 229 |
WHERE $where) AND (pricegroup_id = ?)|; |
|
| 230 |
my $sth_add = prepare_query($::form, $dbh, $q_add); |
|
| 231 |
|
|
| 232 |
my $q_multiply = |
|
| 233 |
qq|UPDATE prices SET price = price * ? |
|
| 234 |
WHERE parts_id IN |
|
| 235 |
(SELECT p.id |
|
| 236 |
FROM parts p |
|
| 237 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 238 |
WHERE $where) AND (pricegroup_id = ?)|; |
|
| 239 |
my $sth_multiply = prepare_query($::form, $dbh, $q_multiply); |
|
| 240 |
|
|
| 241 |
for my $pg (@{ $self->pricegroups }) {
|
|
| 242 |
my $row = $filter->{prices}{$pg->id};
|
|
| 243 |
next if $row->{price_as_number} eq "";
|
|
| 244 |
|
|
| 245 |
my $value = $::form->parse_amount(\%::myconfig, $row->{price_as_number});
|
|
| 246 |
my $result; |
|
| 247 |
|
|
| 248 |
if ($row->{type} eq "percent") {
|
|
| 249 |
$result = do_statement($::form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, $pg->id); |
|
| 250 |
} else {
|
|
| 251 |
$result = do_statement($::form, $sth_add, $q_add, $value, @where_values, $pg->id); |
|
| 252 |
} |
|
| 253 |
|
|
| 254 |
$num_updated += $result if (0 <= $result); |
|
| 255 |
} |
|
| 256 |
|
|
| 257 |
$sth_add->finish; |
|
| 258 |
$sth_multiply->finish; |
|
| 259 |
|
|
| 260 |
1; |
|
| 261 |
} |
|
| 262 |
|
|
| 263 |
sub init_pricegroups {
|
|
| 264 |
SL::DB::Manager::Pricegroup->get_all_sorted(query => [ |
|
| 265 |
obsolete => 0, |
|
| 266 |
]); |
|
| 267 |
} |
|
| 268 |
|
|
| 269 |
sub init_pricegroups_by_id {
|
|
| 270 |
+{ map { $_->id => $_ } @{ $_[0]->pricegroups } }
|
|
| 271 |
} |
|
| 272 |
|
|
| 273 |
sub check_rights {
|
|
| 274 |
$::auth->assert('part_service_assembly_edit');
|
|
| 275 |
} |
|
| 276 |
|
|
| 277 |
sub init_filter {
|
|
| 278 |
$::form->{filter} || {};
|
|
| 279 |
} |
|
| 280 |
|
|
| 281 |
1; |
|
| SL/IC.pm | ||
|---|---|---|
| 49 | 49 |
|
| 50 | 50 |
use strict; |
| 51 | 51 |
|
| 52 |
sub get_pricegroups {
|
|
| 53 |
$main::lxdebug->enter_sub(); |
|
| 54 |
|
|
| 55 |
my ($self, $myconfig, $form) = @_; |
|
| 56 |
|
|
| 57 |
my $dbh = $form->get_standard_dbh; |
|
| 58 |
|
|
| 59 |
# get pricegroups |
|
| 60 |
my $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY lower(pricegroup)|; |
|
| 61 |
my $pricegroups = selectall_hashref_query($form, $dbh, $query); |
|
| 62 |
|
|
| 63 |
my $i = 1; |
|
| 64 |
foreach my $pg (@{ $pricegroups }) {
|
|
| 65 |
$form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
|
|
| 66 |
$form->{"pricegroup_id_$i"} = "$pg->{id}";
|
|
| 67 |
$form->{"pricegroup_$i"} = "$pg->{pricegroup}";
|
|
| 68 |
$i++; |
|
| 69 |
} |
|
| 70 |
|
|
| 71 |
#correct rows |
|
| 72 |
$form->{price_rows} = $i - 1;
|
|
| 73 |
|
|
| 74 |
$main::lxdebug->leave_sub(); |
|
| 75 |
|
|
| 76 |
return $pricegroups; |
|
| 77 |
} |
|
| 78 |
|
|
| 79 | 52 |
sub retrieve_buchungsgruppen {
|
| 80 | 53 |
$main::lxdebug->enter_sub(); |
| 81 | 54 |
|
| ... | ... | |
| 92 | 65 |
$main::lxdebug->leave_sub(); |
| 93 | 66 |
} |
| 94 | 67 |
|
| 95 |
|
|
| 96 | 68 |
sub assembly_item {
|
| 97 | 69 |
$main::lxdebug->enter_sub(); |
| 98 | 70 |
|
| ... | ... | |
| 605 | 577 |
return $form->{parts};
|
| 606 | 578 |
} |
| 607 | 579 |
|
| 608 |
sub _create_filter_for_priceupdate {
|
|
| 609 |
$main::lxdebug->enter_sub(); |
|
| 610 |
|
|
| 611 |
my $self = shift; |
|
| 612 |
my $myconfig = \%main::myconfig; |
|
| 613 |
my $form = $main::form; |
|
| 614 |
|
|
| 615 |
my @where_values; |
|
| 616 |
my $where = '1 = 1'; |
|
| 617 |
|
|
| 618 |
foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
|
|
| 619 |
my $column = $item; |
|
| 620 |
$column =~ s/.*\.//; |
|
| 621 |
next unless ($form->{$column});
|
|
| 622 |
|
|
| 623 |
$where .= qq| AND $item ILIKE ?|; |
|
| 624 |
push(@where_values, like($form->{$column}));
|
|
| 625 |
} |
|
| 626 |
|
|
| 627 |
foreach my $item (qw(description serialnumber)) {
|
|
| 628 |
next unless ($form->{$item});
|
|
| 629 |
|
|
| 630 |
$where .= qq| AND (${item} ILIKE ?)|;
|
|
| 631 |
push(@where_values, like($form->{$item}));
|
|
| 632 |
} |
|
| 633 |
|
|
| 634 |
|
|
| 635 |
# items which were never bought, sold or on an order |
|
| 636 |
if ($form->{itemstatus} eq 'orphaned') {
|
|
| 637 |
$where .= |
|
| 638 |
qq| AND (p.onhand = 0) |
|
| 639 |
AND p.id NOT IN |
|
| 640 |
( |
|
| 641 |
SELECT DISTINCT parts_id FROM invoice |
|
| 642 |
UNION |
|
| 643 |
SELECT DISTINCT parts_id FROM assembly |
|
| 644 |
UNION |
|
| 645 |
SELECT DISTINCT parts_id FROM orderitems |
|
| 646 |
)|; |
|
| 647 |
|
|
| 648 |
} elsif ($form->{itemstatus} eq 'active') {
|
|
| 649 |
$where .= qq| AND p.obsolete = '0'|; |
|
| 650 |
|
|
| 651 |
} elsif ($form->{itemstatus} eq 'obsolete') {
|
|
| 652 |
$where .= qq| AND p.obsolete = '1'|; |
|
| 653 |
|
|
| 654 |
} elsif ($form->{itemstatus} eq 'onhand') {
|
|
| 655 |
$where .= qq| AND p.onhand > 0|; |
|
| 656 |
|
|
| 657 |
} elsif ($form->{itemstatus} eq 'short') {
|
|
| 658 |
$where .= qq| AND p.onhand < p.rop|; |
|
| 659 |
|
|
| 660 |
} |
|
| 661 |
|
|
| 662 |
foreach my $column (qw(make model)) {
|
|
| 663 |
next unless ($form->{$column});
|
|
| 664 |
$where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|; |
|
| 665 |
push(@where_values, like($form->{$column}));
|
|
| 666 |
} |
|
| 667 |
|
|
| 668 |
$main::lxdebug->leave_sub(); |
|
| 669 |
|
|
| 670 |
return ($where, @where_values); |
|
| 671 |
} |
|
| 672 |
|
|
| 673 |
sub get_num_matches_for_priceupdate {
|
|
| 674 |
$main::lxdebug->enter_sub(); |
|
| 675 |
|
|
| 676 |
my $self = shift; |
|
| 677 |
|
|
| 678 |
my $myconfig = \%main::myconfig; |
|
| 679 |
my $form = $main::form; |
|
| 680 |
|
|
| 681 |
my $dbh = $form->get_standard_dbh($myconfig); |
|
| 682 |
|
|
| 683 |
my ($where, @where_values) = $self->_create_filter_for_priceupdate(); |
|
| 684 |
|
|
| 685 |
my $num_updated = 0; |
|
| 686 |
my $query; |
|
| 687 |
|
|
| 688 |
for my $column (qw(sellprice listprice)) {
|
|
| 689 |
next if ($form->{$column} eq "");
|
|
| 690 |
|
|
| 691 |
$query = |
|
| 692 |
qq|SELECT COUNT(*) |
|
| 693 |
FROM parts |
|
| 694 |
WHERE id IN |
|
| 695 |
(SELECT p.id |
|
| 696 |
FROM parts p |
|
| 697 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 698 |
WHERE $where)|; |
|
| 699 |
my ($result) = selectfirst_array_query($form, $dbh, $query, @where_values); |
|
| 700 |
$num_updated += $result if (0 <= $result); |
|
| 701 |
} |
|
| 702 |
|
|
| 703 |
$query = |
|
| 704 |
qq|SELECT COUNT(*) |
|
| 705 |
FROM prices |
|
| 706 |
WHERE parts_id IN |
|
| 707 |
(SELECT p.id |
|
| 708 |
FROM parts p |
|
| 709 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 710 |
WHERE $where) AND (pricegroup_id = ?)|; |
|
| 711 |
my $sth = prepare_query($form, $dbh, $query); |
|
| 712 |
|
|
| 713 |
for my $i (1 .. $form->{price_rows}) {
|
|
| 714 |
next if ($form->{"price_$i"} eq "");
|
|
| 715 |
|
|
| 716 |
my ($result) = do_statement($form, $sth, $query, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
|
| 717 |
$num_updated += $result if (0 <= $result); |
|
| 718 |
} |
|
| 719 |
$sth->finish(); |
|
| 720 |
|
|
| 721 |
$main::lxdebug->leave_sub(); |
|
| 722 |
|
|
| 723 |
return $num_updated; |
|
| 724 |
} |
|
| 725 |
|
|
| 726 |
sub update_prices {
|
|
| 727 |
my ($self, $myconfig, $form) = @_; |
|
| 728 |
$main::lxdebug->enter_sub(); |
|
| 729 |
|
|
| 730 |
my $num_updated = SL::DB->client->with_transaction(\&_update_prices, $self, $myconfig, $form); |
|
| 731 |
|
|
| 732 |
$main::lxdebug->leave_sub(); |
|
| 733 |
return $num_updated; |
|
| 734 |
} |
|
| 735 |
|
|
| 736 |
sub _update_prices {
|
|
| 737 |
my ($self, $myconfig, $form) = @_; |
|
| 738 |
|
|
| 739 |
my ($where, @where_values) = $self->_create_filter_for_priceupdate(); |
|
| 740 |
my $num_updated = 0; |
|
| 741 |
|
|
| 742 |
# connect to database |
|
| 743 |
my $dbh = SL::DB->client->dbh; |
|
| 744 |
|
|
| 745 |
for my $column (qw(sellprice listprice)) {
|
|
| 746 |
next if ($form->{$column} eq "");
|
|
| 747 |
|
|
| 748 |
my $value = $form->parse_amount($myconfig, $form->{$column});
|
|
| 749 |
my $operator = '+'; |
|
| 750 |
|
|
| 751 |
if ($form->{"${column}_type"} eq "percent") {
|
|
| 752 |
$value = ($value / 100) + 1; |
|
| 753 |
$operator = '*'; |
|
| 754 |
} |
|
| 755 |
|
|
| 756 |
my $query = |
|
| 757 |
qq|UPDATE parts SET $column = $column $operator ? |
|
| 758 |
WHERE id IN |
|
| 759 |
(SELECT p.id |
|
| 760 |
FROM parts p |
|
| 761 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 762 |
WHERE $where)|; |
|
| 763 |
my $result = do_query($form, $dbh, $query, $value, @where_values); |
|
| 764 |
$num_updated += $result if (0 <= $result); |
|
| 765 |
} |
|
| 766 |
|
|
| 767 |
my $q_add = |
|
| 768 |
qq|UPDATE prices SET price = price + ? |
|
| 769 |
WHERE parts_id IN |
|
| 770 |
(SELECT p.id |
|
| 771 |
FROM parts p |
|
| 772 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 773 |
WHERE $where) AND (pricegroup_id = ?)|; |
|
| 774 |
my $sth_add = prepare_query($form, $dbh, $q_add); |
|
| 775 |
|
|
| 776 |
my $q_multiply = |
|
| 777 |
qq|UPDATE prices SET price = price * ? |
|
| 778 |
WHERE parts_id IN |
|
| 779 |
(SELECT p.id |
|
| 780 |
FROM parts p |
|
| 781 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 782 |
WHERE $where) AND (pricegroup_id = ?)|; |
|
| 783 |
my $sth_multiply = prepare_query($form, $dbh, $q_multiply); |
|
| 784 |
|
|
| 785 |
for my $i (1 .. $form->{price_rows}) {
|
|
| 786 |
next if ($form->{"price_$i"} eq "");
|
|
| 787 |
|
|
| 788 |
my $value = $form->parse_amount($myconfig, $form->{"price_$i"});
|
|
| 789 |
my $result; |
|
| 790 |
|
|
| 791 |
if ($form->{"pricegroup_type_$i"} eq "percent") {
|
|
| 792 |
$result = do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
|
| 793 |
} else {
|
|
| 794 |
$result = do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
|
|
| 795 |
} |
|
| 796 |
|
|
| 797 |
$num_updated += $result if (0 <= $result); |
|
| 798 |
} |
|
| 799 |
|
|
| 800 |
$sth_add->finish(); |
|
| 801 |
$sth_multiply->finish(); |
|
| 802 |
|
|
| 803 |
return $num_updated; |
|
| 804 |
} |
|
| 805 |
|
|
| 806 | 580 |
# get partnumber, description, unit, sellprice and soldtotal with choice through $sortorder for Top100 |
| 807 | 581 |
sub get_parts {
|
| 808 | 582 |
$main::lxdebug->enter_sub(); |
| bin/mozilla/ic.pl | ||
|---|---|---|
| 102 | 102 |
$lxdebug->leave_sub(); |
| 103 | 103 |
} #end search() |
| 104 | 104 |
|
| 105 |
sub search_update_prices {
|
|
| 106 |
$lxdebug->enter_sub(); |
|
| 107 |
|
|
| 108 |
$auth->assert('part_service_assembly_edit');
|
|
| 109 |
|
|
| 110 |
my $pricegroups = IC->get_pricegroups(\%myconfig, \%$form); |
|
| 111 |
|
|
| 112 |
$form->{title} = $locale->text('Update Prices');
|
|
| 113 |
|
|
| 114 |
$form->header; |
|
| 115 |
|
|
| 116 |
print $form->parse_html_template('ic/search_update_prices', { PRICE_ROWS => $pricegroups });
|
|
| 117 |
|
|
| 118 |
$lxdebug->leave_sub(); |
|
| 119 |
} #end search() |
|
| 120 |
|
|
| 121 |
sub confirm_price_update {
|
|
| 122 |
$lxdebug->enter_sub(); |
|
| 123 |
|
|
| 124 |
$auth->assert('part_service_assembly_edit');
|
|
| 125 |
|
|
| 126 |
my @errors = (); |
|
| 127 |
my $value_found = undef; |
|
| 128 |
|
|
| 129 |
foreach my $idx (qw(sellprice listprice), (1..$form->{price_rows})) {
|
|
| 130 |
my $name = $idx =~ m/\d/ ? $form->{"pricegroup_${idx}"} : $idx eq 'sellprice' ? $locale->text('Sell Price') : $locale->text('List Price');
|
|
| 131 |
my $type = $idx =~ m/\d/ ? $form->{"pricegroup_type_${idx}"} : $form->{"${idx}_type"};
|
|
| 132 |
my $value_idx = $idx =~ m/\d/ ? "price_${idx}" : $idx;
|
|
| 133 |
my $value = $form->parse_amount(\%myconfig, $form->{$value_idx});
|
|
| 134 |
|
|
| 135 |
if ((0 > $value) && ($type eq 'percent')) {
|
|
| 136 |
push @errors, $locale->text('You cannot adjust the price for pricegroup "#1" by a negative percentage.', $name);
|
|
| 137 |
|
|
| 138 |
} elsif (!$value && ($form->{$value_idx} ne '')) {
|
|
| 139 |
push @errors, $locale->text('No valid number entered for pricegroup "#1".', $name);
|
|
| 140 |
|
|
| 141 |
} elsif (0 < $value) {
|
|
| 142 |
$value_found = 1; |
|
| 143 |
} |
|
| 144 |
} |
|
| 145 |
|
|
| 146 |
push @errors, $locale->text('No prices will be updated because no prices have been entered.') if (!$value_found);
|
|
| 147 |
|
|
| 148 |
my $num_matches = IC->get_num_matches_for_priceupdate(); |
|
| 149 |
|
|
| 150 |
$form->header(); |
|
| 151 |
|
|
| 152 |
if (@errors) {
|
|
| 153 |
$form->show_generic_error(join('<br>', @errors));
|
|
| 154 |
} |
|
| 155 |
|
|
| 156 |
$form->{nextsub} = "update_prices";
|
|
| 157 |
|
|
| 158 |
map { delete $form->{$_} } qw(action header);
|
|
| 159 |
|
|
| 160 |
print $form->parse_html_template('ic/confirm_price_update', { HIDDENS => [ map { name => $_, value => $form->{$_} }, keys %$form ],
|
|
| 161 |
num_matches => $num_matches }); |
|
| 162 |
|
|
| 163 |
$lxdebug->leave_sub(); |
|
| 164 |
} |
|
| 165 |
|
|
| 166 |
sub update_prices {
|
|
| 167 |
$lxdebug->enter_sub(); |
|
| 168 |
|
|
| 169 |
$auth->assert('part_service_assembly_edit');
|
|
| 170 |
|
|
| 171 |
my $num_updated = IC->update_prices(\%myconfig, \%$form); |
|
| 172 |
|
|
| 173 |
if (-1 != $num_updated) {
|
|
| 174 |
$form->redirect($locale->text('#1 prices were updated.', $num_updated));
|
|
| 175 |
} else {
|
|
| 176 |
$form->error($locale->text('Could not update prices!'));
|
|
| 177 |
} |
|
| 178 |
|
|
| 179 |
$lxdebug->leave_sub(); |
|
| 180 |
} |
|
| 181 |
|
|
| 182 | 105 |
sub top100 {
|
| 183 | 106 |
$::lxdebug->enter_sub(); |
| 184 | 107 |
|
| menus/user/00-erp.yaml | ||
|---|---|---|
| 99 | 99 |
icon: prices_update |
| 100 | 100 |
order: 800 |
| 101 | 101 |
access: part_service_assembly_edit |
| 102 |
module: ic.pl |
|
| 103 | 102 |
params: |
| 104 |
action: search_update_prices |
|
| 103 |
action: PartsPriceUpdate/search_update_prices
|
|
| 105 | 104 |
- parent: master_data |
| 106 | 105 |
id: master_data_price_rules |
| 107 | 106 |
name: Price Rules |
| templates/webpages/ic/confirm_price_update.html | ||
|---|---|---|
| 1 | 1 |
[%- USE T8 %] |
| 2 | 2 |
[%- USE HTML %] |
| 3 | 3 |
[%- USE LxERP %] |
| 4 |
[%- USE L %] |
|
| 4 | 5 |
|
| 5 |
<form method="post" action="ic.pl"> |
|
| 6 |
|
|
| 7 |
[%- FOREACH row = HIDDENS %] |
|
| 8 |
<input type="hidden" name="[% HTML.escape(row.name) %]" value="[% HTML.escape(row.value) %]" > |
|
| 9 |
[%- END %] |
|
| 6 |
<form method="post" action="controller.pl"> |
|
| 10 | 7 |
|
| 11 | 8 |
<h2 class="confirm">[% 'Confirm!' | $T8 %]</h2> |
| 12 | 9 |
|
| 13 |
<p> |
|
| 14 |
[% LxERP.t8('Approximately #1 prices will be updated.', num_matches) %]
|
|
| 15 |
</p> |
|
| 10 |
<p>[% LxERP.t8('Approximately #1 prices will be updated.', num_matches) %]</p>
|
|
| 16 | 11 |
|
| 17 | 12 |
<p>[% 'Are you sure you want to update the prices' | $T8 %]?</p> |
| 18 | 13 |
|
| 19 | 14 |
<p> |
| 20 |
<input name="action" class="submit" type="submit" value="[% 'Continue' | $T8 %]"> |
|
| 15 |
[% L.hidden_tag('filter_key', filter_key) %]
|
|
| 16 |
[% L.hidden_tag('action', 'PartsPriceUpdate/dispatch') %]
|
|
| 17 |
<input name="action_update_prices" class="submit" type="submit" value="[% 'Continue' | $T8 %]"> |
|
| 21 | 18 |
<input type="button" class="submit" onclick="history.back()" value="[% 'Back' | $T8 %]"> |
| 22 | 19 |
</p> |
| 23 | 20 |
</form> |
| templates/webpages/ic/search_update_prices.html | ||
|---|---|---|
| 1 | 1 |
[%- USE T8 %] |
| 2 | 2 |
[%- USE HTML %] |
| 3 | 3 |
[%- USE LxERP %] |
| 4 |
[%- USE L %] |
|
| 4 | 5 |
<h1>[% 'Update prices' | $T8 %]</h1> |
| 5 | 6 |
|
| 6 |
<form method="post" action="ic.pl"> |
|
| 7 |
|
|
| 8 |
<input type="hidden" name="title" value="[% 'Update prices' | $T8 %]"> |
|
| 9 |
|
|
| 10 |
<p> |
|
| 11 |
<table> |
|
| 12 |
<tr> |
|
| 13 |
<th align="right" nowrap>[% 'Part Number' | $T8 %]</th> |
|
| 14 |
<td><input name="partnumber" size="20"></td> |
|
| 15 |
</tr> |
|
| 16 |
|
|
| 17 |
<tr> |
|
| 18 |
<th align="right" nowrap>[% 'Part Description' | $T8 %]</th> |
|
| 19 |
<td colspan="3"><input name="description" size="20"></td> |
|
| 20 |
</tr> |
|
| 21 |
|
|
| 22 |
<tr> |
|
| 23 |
<th align="right" nowrap>[% 'Partsgroup' | $T8 %]</th> |
|
| 24 |
<td><input name="partsgroup" size="20"></td> |
|
| 25 |
<th align="right" nowrap>[% 'Serial Number' | $T8 %]</th> |
|
| 26 |
<td><input name="serialnumber" size="20"></td> |
|
| 27 |
</tr> |
|
| 28 |
|
|
| 29 |
<tr> |
|
| 30 |
<th align="right" nowrap>[% 'Make' | $T8 %]</th> |
|
| 31 |
<td><input name="make" size="20"></td> |
|
| 32 |
<th align="right" nowrap>[% 'Model' | $T8 %]</th> |
|
| 33 |
<td><input name="model" size="20"></td> |
|
| 34 |
</tr> |
|
| 35 |
|
|
| 36 |
<tr> |
|
| 37 |
<th align="right" nowrap>[% 'Drawing' | $T8 %]</th> |
|
| 38 |
<td><input name="drawing" size="20"></td> |
|
| 39 |
<th align="right" nowrap>[% 'Microfiche' | $T8 %]</th> |
|
| 40 |
<td><input name="microfiche" size="20"></td> |
|
| 41 |
</tr> |
|
| 42 |
|
|
| 43 |
<tr> |
|
| 44 |
<td></td> |
|
| 45 |
<td colspan="3"> |
|
| 46 |
<input name="itemstatus" id="itemstatus_active" class="radio" type="radio" value="active" checked> |
|
| 47 |
<label for="itemstatus_active">[% 'Active' | $T8 %]</label> |
|
| 48 |
<input name="itemstatus" id="itemstatus_onhand" class="radio" type="radio" value="onhand"> |
|
| 49 |
<label for="itemstatus_onhand">[% 'On Hand' | $T8 %]</label> |
|
| 50 |
<input name="itemstatus" id="itemstatus_short" class="radio" type="radio" value="short"> |
|
| 51 |
<label for="itemstatus_short">[% 'Short' | $T8 %]</label> |
|
| 52 |
<input name="itemstatus" id="itemstatus_obsolete" class="radio" type="radio" value="obsolete"> |
|
| 53 |
<label for="itemstatus_obsolete">[% 'Obsolete' | $T8 %]</label> |
|
| 54 |
<input name="itemstatus" id="itemstatus_orphaned" class="radio" type="radio" value="orphaned"> |
|
| 55 |
<label for="itemstatus_orphaned">[% 'Orphaned' | $T8 %]</label> |
|
| 56 |
</td> |
|
| 57 |
</tr> |
|
| 58 |
</table> |
|
| 59 |
</p> |
|
| 60 |
|
|
| 61 |
<hr size="1" noshade> |
|
| 62 |
|
|
| 63 |
<p> |
|
| 64 |
<table> |
|
| 65 |
<tr> |
|
| 66 |
<th class="listheading">[% 'Price group' | $T8 %]</th> |
|
| 67 |
<th class="listheading">[% 'Preis' | $T8 %]</th> |
|
| 68 |
<th class="listheading">[% 'Prozentual/Absolut' | $T8 %]</th> |
|
| 69 |
</tr> |
|
| 70 |
|
|
| 71 |
<tr> |
|
| 72 |
<td>[% 'Sell Price' | $T8 %]</td> |
|
| 73 |
<td><input name="sellprice" size="11" value="[% HTML.escape(sellprice) %]"></td> |
|
| 74 |
<td align="center"> |
|
| 75 |
<input name="sellprice_type" class="radio" type="radio" value="percent" checked> / |
|
| 76 |
<input name="sellprice_type" class="radio" type="radio" value="absolut"> |
|
| 77 |
</td> |
|
| 78 |
</tr> |
|
| 79 |
|
|
| 80 |
<tr> |
|
| 81 |
<td>[% 'List Price' | $T8 %]</td> |
|
| 82 |
<td><input name="listprice" size="11" value="[% HTML.escape(listprice) %]"></td> |
|
| 83 |
<td align="center"> |
|
| 84 |
<input name="listprice_type" class="radio" type="radio" value="percent" checked> / |
|
| 85 |
<input name="listprice_type" class="radio" type="radio" value="absolut"> |
|
| 86 |
</td> |
|
| 87 |
</tr> |
|
| 88 |
|
|
| 89 |
[%- FOREACH row = PRICE_ROWS %] |
|
| 90 |
<input type="hidden" name="pricegroup_id_[% loop.count %]" value="[% HTML.escape(row.id) %]"> |
|
| 91 |
|
|
| 92 |
<tr> |
|
| 93 |
<td><input type="hidden" name="pricegroup_[% loop.count %]" size="30" value="[% HTML.escape(row.pricegroup) %]">[% HTML.escape(row.pricegroup) %]</td> |
|
| 94 |
<td><input name="price_[% loop.count %]" size="11"></td> |
|
| 95 |
<td align="center"> |
|
| 96 |
<input name="pricegroup_type_[% loop.count %]" class="radio" type="radio" value="percent" checked> / |
|
| 97 |
<input name="pricegroup_type_[% loop.count %]" class="radio" type="radio" value="absolut"> |
|
| 98 |
</td> |
|
| 99 |
</tr> |
|
| 100 |
[%- END %] |
|
| 101 |
|
|
| 102 |
</table> |
|
| 103 |
</p> |
|
| 104 |
|
|
| 105 |
<hr size="3" noshade> |
|
| 106 |
|
|
| 107 |
<input type="hidden" name="nextsub" value="confirm_price_update"> |
|
| 108 |
<input type="hidden" name="price_rows" value="[% HTML.escape(price_rows) %]"> |
|
| 109 |
|
|
| 110 |
<p> |
|
| 111 |
<input class="submit" type="submit" name="action" value="[% 'Continue' | $T8 %]"> |
|
| 112 |
</p> |
|
| 113 |
</form> |
|
| 7 |
[% PROCESS 'common/flash.html' %] |
|
| 8 |
|
|
| 9 |
<form method="post" action="controller.pl"> |
|
| 10 |
<table> |
|
| 11 |
<tr> |
|
| 12 |
<th align="right" nowrap>[% 'Part Number' | $T8 %]</th> |
|
| 13 |
<td>[% L.input_tag('filter.partnumber', FORM.filter.partnumber, size=20) %]</td>
|
|
| 14 |
</tr> |
|
| 15 |
|
|
| 16 |
<tr> |
|
| 17 |
<th align="right" nowrap>[% 'Part Description' | $T8 %]</th> |
|
| 18 |
<td colspan="3">[% L.input_tag('filter.description', FORM.filter.description, size=20) %]</td>
|
|
| 19 |
</tr> |
|
| 20 |
|
|
| 21 |
<tr> |
|
| 22 |
<th align="right" nowrap>[% 'Partsgroup' | $T8 %]</th> |
|
| 23 |
<td>[% L.input_tag('filter.partsgroup', FORM.filter.partsgroup, size=20) %]</td>
|
|
| 24 |
<th align="right" nowrap>[% 'Serial Number' | $T8 %]</th> |
|
| 25 |
<td>[% L.input_tag('filter.serialnumber', FORM.filter.serialnumber, size=20) %]</td>
|
|
| 26 |
</tr> |
|
| 27 |
|
|
| 28 |
<tr> |
|
| 29 |
<th align="right" nowrap>[% 'Make' | $T8 %]</th> |
|
| 30 |
<td>[% L.input_tag('filter.make', FORM.filter.make, size=20) %]</td>
|
|
| 31 |
<th align="right" nowrap>[% 'Model' | $T8 %]</th> |
|
| 32 |
<td>[% L.input_tag('filter.model', FORM.filter.model, size=20) %]</td>
|
|
| 33 |
</tr> |
|
| 34 |
|
|
| 35 |
<tr> |
|
| 36 |
<th align="right" nowrap>[% 'Drawing' | $T8 %]</th> |
|
| 37 |
<td>[% L.input_tag('filter.drawing', FORM.filter.drawing, size=20) %]</td>
|
|
| 38 |
<th align="right" nowrap>[% 'Microfiche' | $T8 %]</th> |
|
| 39 |
<td>[% L.input_tag('filter.microfiche', FORM.filter.microfiche, size=20) %]</td>
|
|
| 40 |
</tr> |
|
| 41 |
|
|
| 42 |
<tr> |
|
| 43 |
<td></td> |
|
| 44 |
<td colspan="3"> |
|
| 45 |
[% L.radio_button_tag('filter.itemstatus', value='active', label=LxERP.t8('Active'), checked=!FORM.filter.itemstatus||FORM.filter.itemstatus=='active') %]
|
|
| 46 |
[% L.radio_button_tag('filter.itemstatus', value='onhand', label=LxERP.t8('On Hand'), checked=FORM.filter.itemstatus=='onhand') %]
|
|
| 47 |
[% L.radio_button_tag('filter.itemstatus', value='short', label=LxERP.t8('Short'), checked=FORM.filter.itemstatus=='short') %]
|
|
| 48 |
[% L.radio_button_tag('filter.itemstatus', value='obsolete', label=LxERP.t8('Obsolete'), checked=FORM.filter.itemstatus=='obsolete') %]
|
|
| 49 |
[% L.radio_button_tag('filter.itemstatus', value='orphaned', label=LxERP.t8('Orphaned'), checked=FORM.filter.itemstatus=='orphaned') %]
|
|
| 50 |
</td> |
|
| 51 |
</tr> |
|
| 52 |
</table> |
|
| 53 |
|
|
| 54 |
<hr size="1" noshade> |
|
| 55 |
|
|
| 56 |
<table> |
|
| 57 |
<tr> |
|
| 58 |
<th class="listheading">[% 'Price group' | $T8 %]</th> |
|
| 59 |
<th class="listheading">[% 'Preis' | $T8 %]</th> |
|
| 60 |
<th class="listheading">[% 'Prozentual/Absolut' | $T8 %]</th> |
|
| 61 |
</tr> |
|
| 62 |
|
|
| 63 |
<tr> |
|
| 64 |
<td>[% 'Sell Price' | $T8 %]</td> |
|
| 65 |
<td>[% L.input_tag('filter.prices.sellprice.price_as_number', FORM.filter.prices.sellprice.price_as_number, size=11) %]</td>
|
|
| 66 |
<td align="center"> |
|
| 67 |
[% L.radio_button_tag("filter.prices.sellprice.type",
|
|
| 68 |
value="percent", |
|
| 69 |
checked=!FORM.filter.prices.sellprice.type || FORM.filter.prices.sellprice.type == 'percent') %] / |
|
| 70 |
[% L.radio_button_tag("filter.prices.sellprice.type",
|
|
| 71 |
value="absolut", |
|
| 72 |
checked=FORM.filter.prices.sellprice.type == 'absolut') %] |
|
| 73 |
</td> |
|
| 74 |
</tr> |
|
| 75 |
|
|
| 76 |
<tr> |
|
| 77 |
<td>[% 'List Price' | $T8 %]</td> |
|
| 78 |
<td>[% L.input_tag('filter.prices.listprice.price_as_number', FORM.filter.prices.listprice.price_as_number, size=11) %]</td>
|
|
| 79 |
<td align="center"> |
|
| 80 |
[% L.radio_button_tag("filter.prices.listprice.type",
|
|
| 81 |
value="percent", |
|
| 82 |
checked=!FORM.filter.prices.listprice.type || FORM.filter.prices.listprice.type == 'percent') %] / |
|
| 83 |
[% L.radio_button_tag("filter.prices.listprice.type",
|
|
| 84 |
value="absolut", |
|
| 85 |
checked=FORM.filter.prices.listprice.type == 'absolut') %] |
|
| 86 |
</td> |
|
| 87 |
</tr> |
|
| 88 |
|
|
| 89 |
[%- FOREACH pg = SELF.pricegroups %] |
|
| 90 |
<tr> |
|
| 91 |
<td>[% pg.pricegroup | html %]</td> |
|
| 92 |
<td>[% L.input_tag('filter.prices.' _ pg.id _ '.price_as_number', FORM.filter.prices.${pg.id}.price_as_number, size=11) %]</td>
|
|
| 93 |
<td align="center"> |
|
| 94 |
[% L.radio_button_tag("filter.prices." _ pg.id _ ".type",
|
|
| 95 |
value="percent", |
|
| 96 |
checked=!FORM.filter.prices.${pg.id}.type || FORM.filter.prices.${pg.id}.type == 'percent') %] /
|
|
| 97 |
[% L.radio_button_tag("filter.prices." _ pg.id _ ".type",
|
|
| 98 |
value="absolut", |
|
| 99 |
checked=FORM.filter.prices.${pg.id}.type == 'absolut') %]
|
|
| 100 |
</td> |
|
| 101 |
</tr> |
|
| 102 |
[%- END %] |
|
| 103 |
|
|
| 104 |
</table> |
|
| 105 |
|
|
| 106 |
<hr size="3" noshade> |
|
| 107 |
|
|
| 108 |
[% L.hidden_tag('action', 'PartsPriceUpdate/dispatch') %]
|
|
| 109 |
<input class="submit" type="submit" name="action_confirm_price_update" value="[% 'Continue' | $T8 %]"> |
|
| 110 |
</form> |
|
| 114 | 111 |
|
Auch abrufbar als: Unified diff
Preisupdate in eigenen controller verlagert
...und dabei das völlig kaputte Exceptionhandling gefixt