Revision 347f2cff
Von Moritz Bunkus vor mehr als 11 Jahren hinzugefügt
| sql/Pg-upgrade2/warehouse.pl | ||
|---|---|---|
| 1 | 1 |
# @tag: warehouse |
| 2 | 2 |
# @description: Diverse neue Tabellen und Spalten zur Mehrlagerfähigkeit inkl. Migration |
| 3 | 3 |
# @depends: release_2_4_3 |
| 4 |
package SL::DBUpgrade2::warehouse; |
|
| 4 | 5 |
|
| 5 | 6 |
use strict; |
| 7 |
use utf8; |
|
| 6 | 8 |
|
| 7 |
die("This script cannot be run from the command line.") unless ($main::form);
|
|
| 8 |
|
|
| 9 |
my $do_sql_migration = 0; |
|
| 10 |
my ($check_sql, $sqlcode); |
|
| 11 |
|
|
| 12 |
sub mydberror {
|
|
| 13 |
my ($msg) = @_; |
|
| 14 |
die($dbup_locale->text("Database update error:") .
|
|
| 15 |
"<br>$msg<br>" . $DBI::errstr); |
|
| 16 |
} |
|
| 17 |
|
|
| 18 |
sub do_query {
|
|
| 19 |
my ($query, $may_fail) = @_; |
|
| 20 |
|
|
| 21 |
if (!$dbh->do($query)) {
|
|
| 22 |
mydberror($query) unless ($may_fail); |
|
| 23 |
$dbh->rollback(); |
|
| 24 |
$dbh->begin_work(); |
|
| 25 |
} |
|
| 26 |
} |
|
| 9 |
use parent qw(SL::DBUpgrade2::Base); |
|
| 27 | 10 |
|
| 11 |
use SL::DBUtils; |
|
| 28 | 12 |
|
| 29 | 13 |
sub print_question {
|
| 30 |
print $main::form->parse_html_template("dbupgrade/warehouse_form");
|
|
| 31 |
} |
|
| 32 |
|
|
| 33 |
sub do_update {
|
|
| 34 |
if (!$main::form->{do_migrate}
|
|
| 35 |
&& (selectfirst_array_query($main::form, $dbh, $check_sql))[0]) { # check if update is needed
|
|
| 36 |
print_question(); |
|
| 37 |
return 2; |
|
| 38 |
} else {
|
|
| 39 |
if ($main::form->{do_migrate} eq 'Y') {
|
|
| 40 |
# if yes, both warehouse and bin must be given |
|
| 41 |
if (!$main::form->{import_warehouse} || !$main::form->{bin_default}) {
|
|
| 42 |
print_question(); |
|
| 43 |
return 2; |
|
| 44 |
} |
|
| 45 |
# flag for extra code |
|
| 46 |
$do_sql_migration = 1; |
|
| 47 |
} |
|
| 48 |
} |
|
| 49 |
my $warehouse = $main::form->{import_warehouse} ne '' ? $main::form->{import_warehouse} : "Transfer";
|
|
| 50 |
my $bin = $main::form->{bin_default} ne '' ? $main::form->{bin_default} : "1";
|
|
| 51 |
|
|
| 52 |
$warehouse = $dbh->quote($warehouse); |
|
| 53 |
$bin = $dbh->quote($bin); |
|
| 54 |
|
|
| 55 |
my $migration_code = <<EOF |
|
| 56 |
|
|
| 57 |
-- Adjust warehouse |
|
| 58 |
INSERT INTO warehouse (description, sortkey, invalid) VALUES ($warehouse, 1, FALSE); |
|
| 59 |
|
|
| 60 |
UPDATE tmp_parts SET bin = NULL WHERE bin = ''; |
|
| 61 |
|
|
| 62 |
-- Restore old onhand |
|
| 63 |
INSERT INTO bin |
|
| 64 |
(warehouse_id, description) |
|
| 65 |
(SELECT DISTINCT warehouse.id, COALESCE(bin, $bin) |
|
| 66 |
FROM warehouse, tmp_parts |
|
| 67 |
WHERE warehouse.description=$warehouse); |
|
| 68 |
INSERT INTO inventory |
|
| 69 |
(warehouse_id, parts_id, bin_id, qty, employee_id, trans_id, trans_type_id, chargenumber) |
|
| 70 |
(SELECT warehouse.id, tmp_parts.id, bin.id, onhand, (SELECT id FROM employee LIMIT 1), nextval('id'), transfer_type.id, ''
|
|
| 71 |
FROM transfer_type, warehouse, tmp_parts, bin |
|
| 72 |
WHERE warehouse.description = $warehouse |
|
| 73 |
AND COALESCE(bin, $bin) = bin.description |
|
| 74 |
AND transfer_type.description = 'stock'); |
|
| 75 |
EOF |
|
| 76 |
; |
|
| 77 |
|
|
| 78 |
# do standard code |
|
| 79 |
my $query = $sqlcode; |
|
| 80 |
$query .= $migration_code if $do_sql_migration; |
|
| 81 |
|
|
| 82 |
do_query($query); |
|
| 83 |
|
|
| 84 |
return 1; |
|
| 14 |
print $::form->parse_html_template("dbupgrade/warehouse_form");
|
|
| 85 | 15 |
} |
| 86 | 16 |
|
| 17 |
sub run {
|
|
| 18 |
my ($self) = @_; |
|
| 87 | 19 |
|
| 88 |
|
|
| 89 |
$sqlcode = <<EOF |
|
| 20 |
my $do_sql_migration = 0; |
|
| 21 |
my $check_sql = qq|SELECT COUNT(id) FROM parts WHERE onhand > 0;|; |
|
| 22 |
my $sqlcode = <<SQL; |
|
| 90 | 23 |
-- Table "bin" for bins. |
| 91 | 24 |
CREATE TABLE bin ( |
| 92 | 25 |
id integer NOT NULL DEFAULT nextval('id'),
|
| ... | ... | |
| 200 | 133 |
CREATE TRIGGER trig_update_onhand |
| 201 | 134 |
AFTER INSERT OR UPDATE OR DELETE ON inventory |
| 202 | 135 |
FOR EACH ROW EXECUTE PROCEDURE update_onhand(); |
| 203 |
EOF |
|
| 204 |
; |
|
| 136 |
SQL |
|
| 137 |
|
|
| 138 |
if (!$::form->{do_migrate}
|
|
| 139 |
&& (selectfirst_array_query($::form, $self->dbh, $check_sql))[0]) { # check if update is needed
|
|
| 140 |
print_question(); |
|
| 141 |
return 2; |
|
| 142 |
} else {
|
|
| 143 |
if ($::form->{do_migrate} eq 'Y') {
|
|
| 144 |
# if yes, both warehouse and bin must be given |
|
| 145 |
if (!$::form->{import_warehouse} || !$::form->{bin_default}) {
|
|
| 146 |
print_question(); |
|
| 147 |
return 2; |
|
| 148 |
} |
|
| 149 |
# flag for extra code |
|
| 150 |
$do_sql_migration = 1; |
|
| 151 |
} |
|
| 152 |
} |
|
| 153 |
my $warehouse = $::form->{import_warehouse} ne '' ? $::form->{import_warehouse} : "Transfer";
|
|
| 154 |
my $bin = $::form->{bin_default} ne '' ? $::form->{bin_default} : "1";
|
|
| 155 |
|
|
| 156 |
$warehouse = $self->dbh->quote($warehouse); |
|
| 157 |
$bin = $self->dbh->quote($bin); |
|
| 158 |
|
|
| 159 |
my $migration_code = <<EOF |
|
| 205 | 160 |
|
| 161 |
-- Adjust warehouse |
|
| 162 |
INSERT INTO warehouse (description, sortkey, invalid) VALUES ($warehouse, 1, FALSE); |
|
| 206 | 163 |
|
| 207 |
$check_sql = <<EOF |
|
| 208 |
SELECT COUNT(id) FROM parts WHERE onhand > 0; |
|
| 164 |
UPDATE tmp_parts SET bin = NULL WHERE bin = ''; |
|
| 165 |
|
|
| 166 |
-- Restore old onhand |
|
| 167 |
INSERT INTO bin |
|
| 168 |
(warehouse_id, description) |
|
| 169 |
(SELECT DISTINCT warehouse.id, COALESCE(bin, $bin) |
|
| 170 |
FROM warehouse, tmp_parts |
|
| 171 |
WHERE warehouse.description=$warehouse); |
|
| 172 |
INSERT INTO inventory |
|
| 173 |
(warehouse_id, parts_id, bin_id, qty, employee_id, trans_id, trans_type_id, chargenumber) |
|
| 174 |
(SELECT warehouse.id, tmp_parts.id, bin.id, onhand, (SELECT id FROM employee LIMIT 1), nextval('id'), transfer_type.id, ''
|
|
| 175 |
FROM transfer_type, warehouse, tmp_parts, bin |
|
| 176 |
WHERE warehouse.description = $warehouse |
|
| 177 |
AND COALESCE(bin, $bin) = bin.description |
|
| 178 |
AND transfer_type.description = 'stock'); |
|
| 209 | 179 |
EOF |
| 210 | 180 |
; |
| 211 | 181 |
|
| 212 |
return do_update(); |
|
| 182 |
# do standard code |
|
| 183 |
my $query = $sqlcode; |
|
| 184 |
$query .= $migration_code if $do_sql_migration; |
|
| 185 |
|
|
| 186 |
$self->db_query($query); |
|
| 187 |
|
|
| 188 |
return 1; |
|
| 189 |
} |
|
| 190 |
|
|
| 191 |
1; |
|
Auch abrufbar als: Unified diff
Perl-Datenbank-Upgradescripte auf Objektorientierung & strict umgestellt