kivitendo/sql/Pg-upgrade2/erzeugnisnummern.pl @ 4cb374f9
| 607a8079 | Niclas Zimmermann | # @tag: erzeugnisnummern
|
||
# @description: Erzeugnisnummern und Artikelnummern sollen eindeutig sein.
|
||||
# @depends: release_3_0_0
|
||||
| 347f2cff | Moritz Bunkus | package SL::DBUpgrade2::erzeugnisnummern;
|
||
| 607a8079 | Niclas Zimmermann | |||
use strict;
|
||||
| 347f2cff | Moritz Bunkus | use utf8;
|
||
| 607a8079 | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | use parent qw(SL::DBUpgrade2::Base);
|
||
| 607a8079 | Niclas Zimmermann | |||
| ac3ca512 | Moritz Bunkus | use SL::DBUtils;
|
||
| 347f2cff | Moritz Bunkus | sub run {
|
||
my ($self) = @_;
|
||||
| 607a8079 | Niclas Zimmermann | |||
| bfdfc9ee | Sven Schöling | if (defined $::form->{upgrade_action} && $::form->{upgrade_action} eq 'filter_parts') {
|
||
| 48452323 | Niclas Zimmermann | return $self->filter_parts($self);
|
||
}
|
||||
| 347f2cff | Moritz Bunkus | if ( $::form->{'continued'} ) {
|
||
| f41e5b6e | Niclas Zimmermann | my $update_query;
|
||
| 347f2cff | Moritz Bunkus | foreach my $i (1 .. $::form->{rowcount}) {
|
||
$update_query = qq|UPDATE parts SET partnumber = '| . $::form->{"partnumber_$i"} . qq|' WHERE id = | . $::form->{"partid_$i"};
|
||||
$self->db_query($update_query);
|
||||
| f41e5b6e | Niclas Zimmermann | }
|
||
}
|
||||
| ac3ca512 | Moritz Bunkus | my $query = qq|SELECT id, partnumber, description, unit, notes, assembly, ean, inventory_accno_id, obsolete
|
||
| 607a8079 | Niclas Zimmermann | FROM parts pa
|
||
WHERE (SELECT COUNT(*)
|
||||
FROM parts p
|
||||
WHERE p.partnumber=pa.partnumber)
|
||||
> 1
|
||||
ORDER BY partnumber;|;
|
||||
| ac3ca512 | Moritz Bunkus | $::form->{PARTS} = [ selectall_hashref_query($::form, $self->dbh, $query) ];
|
||
| 607a8079 | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | if ( scalar @{ $::form->{PARTS} } > 0 ) {
|
||
| 607a8079 | Niclas Zimmermann | &print_error_message;
|
||
| f41e5b6e | Niclas Zimmermann | return 2;
|
||
| 607a8079 | Niclas Zimmermann | }
|
||
$query = qq|ALTER TABLE parts ADD UNIQUE (partnumber)|;
|
||||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 607a8079 | Niclas Zimmermann | |||
$query = qq|ALTER TABLE defaults ADD assemblynumber TEXT|;
|
||||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 607a8079 | Niclas Zimmermann | return 1;
|
||
| 347f2cff | Moritz Bunkus | } # end run
|
||
| 607a8079 | Niclas Zimmermann | |||
sub print_error_message {
|
||||
| 347f2cff | Moritz Bunkus | print $::form->parse_html_template("dbupgrade/erzeugnisnummern");
|
||
| 607a8079 | Niclas Zimmermann | }
|
||
| 48452323 | Niclas Zimmermann | sub filter_parts {
|
||
my $self = shift;
|
||||
my $where = 'TRUE';
|
||||
my @values;
|
||||
if ( $::form->{filter_partnumber} ) {
|
||||
$where .= ' AND partnumber ILIKE ?';
|
||||
| bed19453 | Moritz Bunkus | push(@values, like( $::form->{filter_partnumber} ));
|
||
| 48452323 | Niclas Zimmermann | }
|
||
if ($::form->{filter_description}) {
|
||||
$where .= ' AND description ILIKE ?';
|
||||
| bed19453 | Moritz Bunkus | push(@values, like($::form->{filter_description}));
|
||
| 48452323 | Niclas Zimmermann | }
|
||
if ($::form->{filter_notes}) {
|
||||
$where .= ' AND notes ILIKE ?';
|
||||
| bed19453 | Moritz Bunkus | push(@values, like($::form->{filter_notes}));
|
||
| 48452323 | Niclas Zimmermann | }
|
||
if ($::form->{filter_ean}) {
|
||||
$where .= ' AND ean ILIKE ?';
|
||||
| bed19453 | Moritz Bunkus | push(@values, like($::form->{filter_ean}));
|
||
| 48452323 | Niclas Zimmermann | }
|
||
if ($::form->{filter_type} eq 'assembly') {
|
||||
| 98b64fe1 | Geoffrey Richardson | $where .= " AND part_type = 'assembly'";
|
||
| 48452323 | Niclas Zimmermann | }
|
||
if ($::form->{filter_type} eq 'service') {
|
||||
| 98b64fe1 | Geoffrey Richardson | $where .= " AND part_type = 'service'";
|
||
| 48452323 | Niclas Zimmermann | }
|
||
if ($::form->{filter_type} eq 'part') {
|
||||
| 98b64fe1 | Geoffrey Richardson | $where .= " AND part_type = 'part'";
|
||
| 48452323 | Niclas Zimmermann | }
|
||
if ($::form->{filter_obsolete} eq 'obsolete') {
|
||||
$where .= ' AND obsolete';
|
||||
}
|
||||
if ($::form->{filter_obsolete} eq 'valid') {
|
||||
$where .= ' AND NOT obsolete';
|
||||
}
|
||||
my $query = qq|SELECT id, partnumber, description, unit, notes, assembly, ean, inventory_accno_id, obsolete
|
||||
FROM parts
|
||||
WHERE $where
|
||||
ORDER BY partnumber|;
|
||||
$::form->{ALL_PARTS} = [ selectall_hashref_query($::form, $self->dbh, $query, @values) ];
|
||||
print $::form->parse_html_template("dbupgrade/show_partlist");
|
||||
return 2;
|
||||
}
|
||||
| 347f2cff | Moritz Bunkus | 1;
|