1 # @tag: default_bin_parts
2 # @description: Freitext Feld Lagerplatz nach Lager und Lagerplatz migrieren
3 # @depends: release_3_0_0 add_warehouse_defaults
5 package SL::DBUpgrade2::default_bin_parts;
11 use parent qw(SL::DBUpgrade2::Base);
15 $::form->get_lists('warehouses' => { 'key' => 'WAREHOUSES',
17 if (scalar @{ $::form->{WAREHOUSES} }) {
18 $::form->{warehouse_id} ||= $::form->{WAREHOUSES}->[0]->{id};
19 $::form->{bin_id} ||= $::form->{WAREHOUSES}->[0]->{BINS}->[0]->{id};
21 $::form->{NO_WAREHOUSE} = 1;
23 $::form->{warehouse_id} = 0; # 0 ist die ID für leere Option
25 if ( $::form->{'continued'} ) {
27 my $CREATE_WAREHOUSE = 0;
28 if (!defined($::form->{NO_WAREHOUSE}) && defined($::form->{create_new_bins}) && $::form->{warehouse_id_default}) {
31 if (defined($::form->{NO_WAREHOUSE}) && defined($::form->{create_new_bins}) && $::form->{new_warehouse}) {
32 $CREATE_WAREHOUSE = 1;
37 my $insert_warehouse_query = qq|INSERT into warehouse (description, invalid, sortkey) VALUES (?, 'false', 1) |;
38 my $prepared_insert_warehouse_query = $self->dbh->prepare($insert_warehouse_query) || $self->db_error($insert_warehouse_query);
41 my $insert_bin_query = qq|INSERT into bin (description, warehouse_id) VALUES (?, ?) |;
42 my $prepared_insert_bin_query = $self->dbh->prepare($insert_bin_query) || $self->db_error($insert_bin_query);
44 # Lagerplatz aus Liste zuweisen
45 my $update_query = qq|UPDATE parts SET warehouse_id = ?, bin_id = ? WHERE id = ?|;
46 my $prepared_update_query = $self->dbh->prepare($update_query) || $self->db_error($update_query);
49 # gerade angelegten Lagerplatz zuweisen
50 my $update_new_bin_query = qq|UPDATE parts SET warehouse_id = (SELECT warehouse_id from bin where description = ?),
51 bin_id = (SELECT id from bin where description = ?)
53 my $prepared_update_new_bin_query = $self->dbh->prepare($update_new_bin_query) || $self->db_error($update_new_bin_query);
56 # kein lager vorhanden, aber wir legen ein neues an.
57 if ($CREATE_WAREHOUSE && $CREATE_BINS) {
58 $prepared_insert_warehouse_query->execute($::form->{new_warehouse}) || $self->db_error($insert_warehouse_query);
59 $prepared_insert_warehouse_query->finish();
60 my $query = qq|SELECT id FROM warehouse LIMIT 1;|;
61 my $sth = $self->dbh->prepare($query);
62 $sth->execute || $::form->dberror($query);
63 $::form->{warehouse_id_default} = $sth->fetchrow_array();
66 foreach my $i (1 .. $::form->{rowcount}) {
68 # Best Case: Lagerplatz aus Liste gewählt
69 # bei zurückspringen auf leeres lager, wird der lagerplatz nicht zurückgesetzt
70 # erstmal an dieser stelle abfangen, damit nichts angelegt wird
71 if ($::form->{"bin_id_$i"} && $::form->{"warehouse_id_$i"}) {
72 $prepared_update_query->execute($::form->{"warehouse_id_$i"}, $::form->{"bin_id_$i"}, $::form->{"partid_$i"}) || $self->db_error($update_query);
73 } elsif ($CREATE_BINS) {
74 # Lager vorhanden, bzw. vorher erstellt. alte bins automatisch hinzufügen und zum Standardlagerplatz verdrahten
75 $prepared_insert_bin_query->execute($::form->{"bin_$i"}, $::form->{warehouse_id_default}) || $self->db_error($insert_bin_query);
76 $prepared_update_new_bin_query->execute($::form->{"bin_$i"}, $::form->{"bin_$i"}, $::form->{"partid_$i"}) || $self->db_error($update_new_bin_query);
79 $prepared_insert_bin_query->finish();
80 $prepared_update_new_bin_query->finish();
81 $prepared_update_query->finish();
82 $::form->{FINISH} = 1;
83 # das alte textfeld entfernen
84 #my $query = qq|ALTER TABLE parts drop COLUMN bin|;
85 #$self->db_query($query);
89 my $query = qq|SELECT id, partnumber, description, bin
91 WHERE '' <> NULLIF ( bin, '')
92 ORDER BY partnumber;|;
94 my $sth = $self->dbh->prepare($query);
95 $sth->execute || $::form->dberror($query);
97 $::form->{PARTS} = [ selectall_hashref_query($::form, $self->dbh, $query) ];
99 if ( (scalar @{ $::form->{PARTS} } > 0 ) && !$::form->{NO_WAREHOUSE} && !$::form->{FINISH} ) {
100 &print_error_message;
102 } elsif ( (scalar @{ $::form->{PARTS} } > 0 ) && $::form->{NO_WAREHOUSE} && !$::form->{FINISH} ) {
103 &print_error_message_no_warehouse;
106 # das alte textfeld entfernen
107 # hier nochmal, da oben schon ein return 1 gesetzt ist
108 $query = qq|ALTER TABLE parts drop COLUMN bin|;
109 $self->db_query($query);
113 sub print_error_message {
114 print $::form->parse_html_template("dbupgrade/default_bin_parts");
117 sub print_error_message_no_warehouse {
118 print $::form->parse_html_template("dbupgrade/default_bin_parts_no_warehouse");