1 package SL::Controller::PartsPriceUpdate;
4 use parent qw(SL::Controller::Base);
6 use SL::DBUtils qw(prepare_query selectfirst_array_query prepare_query do_statement do_query);
8 use SL::Helper::Flash qw(flash);
11 use SL::DB::Pricegroup;
12 use SL::Locale::String qw(t8);
14 use Rose::Object::MakeMethods::Generic (
15 'scalar --get_set_init' => [ qw(pricegroups pricegroups_by_id filter) ],
18 __PACKAGE__->run_before('check_rights');
21 sub action_search_update_prices {
24 $self->render('ic/search_update_prices',
25 title => t8('Update Prices'),
29 sub action_confirm_price_update {
35 for my $key (keys %{ $self->filter->{prices} || {} }) {
36 my $row = $self->filter->{prices}{$key};
38 next if $row->{price_as_number} eq '';
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')
47 if (0 > $value && ($type eq 'percent')) {
48 push @errors, t8('You cannot adjust the price for pricegroup "#1" by a negative percentage.', $name);
50 push @errors, t8('No valid number entered for pricegroup "#1".', $name);
51 } elsif (0 < $value) {
56 push @errors, t8('No prices will be updated because no prices have been entered.') if !$found;
58 my $num_matches = $self->get_num_matches_for_priceupdate();
61 flash('error', $_) for @errors;
62 return $self->action_search_update_prices;
65 my $key = $::auth->create_unique_sesion_value(SL::JSON::to_json($self->filter));
67 $self->render('ic/confirm_price_update',
68 num_matches => $num_matches,
74 sub action_update_prices {
77 my $num_updated = $self->do_update_prices;
80 $::form->redirect(t8('#1 prices were updated.', $num_updated));
82 $::form->error(t8('Could not update prices!'));
86 sub _create_filter_for_priceupdate {
88 my $filter = $self->filter;
93 for my $item (qw(partnumber drawing microfiche make model pg.partsgroup description serialnumber)) {
96 next unless $filter->{$column};
98 $where .= qq| AND $item ILIKE ?|;
99 push @where_values, "%$filter->{$column}%";
102 # items which were never bought, sold or on an order
103 if ($filter->{itemstatus} eq 'orphaned') {
105 qq| AND (p.onhand = 0)
108 SELECT DISTINCT parts_id FROM invoice
110 SELECT DISTINCT parts_id FROM assembly
112 SELECT DISTINCT parts_id FROM orderitems
114 SELECT DISTINCT parts_id FROM delivery_order_items
117 } elsif ($filter->{itemstatus} eq 'active') {
118 $where .= qq| AND p.obsolete = '0'|;
120 } elsif ($filter->{itemstatus} eq 'obsolete') {
121 $where .= qq| AND p.obsolete = '1'|;
123 } elsif ($filter->{itemstatus} eq 'onhand') {
124 $where .= qq| AND p.onhand > 0|;
126 } elsif ($filter->{itemstatus} eq 'short') {
127 $where .= qq| AND p.onhand < p.rop|;
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}%";
137 return ($where, @where_values);
140 sub get_num_matches_for_priceupdate {
142 my $filter = $self->filter;
143 my $dbh = SL::DB->client->dbh;
144 my ($where, @where_values) = $self->_create_filter_for_priceupdate;
149 for my $column (qw(sellprice listprice)) {
150 next if $filter->{prices}{$column}{price_as_number} eq "";
158 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
160 my ($result) = selectfirst_array_query($::form, $dbh, $query, @where_values);
161 $num_updated += $result if (0 <= $result);
164 my @ids = grep { $filter->{prices}{$_}{price_as_number} } map { $_->id } @{ $self->pricegroups };
172 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
174 AND pricegroup_id IN (@{[ join ',', ('?')x@ids ]})|;
176 my ($result) = selectfirst_array_query($::form, $dbh, $query, @where_values, @ids);
177 $num_updated += $result if (0 <= $result);
183 sub do_update_prices {
184 SL::DB->client->with_transaction(\&_update_prices, $_[0]);
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;
194 my ($where, @where_values) = $self->_create_filter_for_priceupdate;
197 # connect to database
198 my $dbh = SL::DB->client->dbh;
200 for my $column (qw(sellprice listprice)) {
201 my $row = $filter->{prices}{$column};
202 next if ($row->{price_as_number} eq "");
204 my $value = $::form->parse_amount(\%::myconfig, $row->{price_as_number});
207 if ($row->{type} eq "percent") {
208 $value = ($value / 100) + 1;
213 qq|UPDATE parts SET $column = $column $operator ?
217 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
219 my $result = do_query($::form, $dbh, $query, $value, @where_values);
220 $num_updated += $result if 0 <= $result;
224 qq|UPDATE prices SET price = price + ?
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);
233 qq|UPDATE prices SET price = price * ?
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);
241 for my $pg (@{ $self->pricegroups }) {
242 my $row = $filter->{prices}{$pg->id};
243 next if $row->{price_as_number} eq "";
245 my $value = $::form->parse_amount(\%::myconfig, $row->{price_as_number});
248 if ($row->{type} eq "percent") {
249 $result = do_statement($::form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, $pg->id);
251 $result = do_statement($::form, $sth_add, $q_add, $value, @where_values, $pg->id);
254 $num_updated += $result if (0 <= $result);
258 $sth_multiply->finish;
263 sub init_pricegroups {
264 SL::DB::Manager::Pricegroup->get_all_sorted(query => [
269 sub init_pricegroups_by_id {
270 +{ map { $_->id => $_ } @{ $_[0]->pricegroups } }
274 $::auth->assert('part_service_assembly_edit');
278 $::form->{filter} || {};