kivitendo/sql/Pg-upgrade/Pg-upgrade-2.2.0.25-2.2.0.26.pl @ ea57e835
| 3d6e7124 | Moritz Bunkus | #!/usr/bin/perl
|
||
| d2f6a8c9 | Moritz Bunkus | # Datenbankupgrade: Einfuehrung von Einheiten
|
||
| 3d6e7124 | Moritz Bunkus | die("This script cannot be run from the command line.") unless ($main::form);
|
||
sub mydberror {
|
||||
| ae1b5cba | Moritz Bunkus | my ($msg) = @_;
|
||
| 3d6e7124 | Moritz Bunkus | die($dbup_locale->text("Database update error:") .
|
||
"<br>$msg<br>" . $DBI::errstr);
|
||||
}
|
||||
sub myshowerror {
|
||||
my ($msg) = @_;
|
||||
| 9aaca433 | Moritz Bunkus | print $main::form->parse_html_template("dbupgrade/units_error", { "message" => $msg });
|
||
| 3d6e7124 | Moritz Bunkus | return 2;
|
||
}
|
||||
| ce30a8c3 | Moritz Bunkus | sub get_base_unit {
|
||
my ($units, $unit_name, $factor) = @_;
|
||||
$factor = 1 unless ($factor);
|
||||
my $unit = $units->{$unit_name};
|
||||
if (!defined($unit) || !$unit->{"base_unit"} ||
|
||||
($unit_name eq $unit->{"base_unit"})) {
|
||||
return ($unit_name, $factor);
|
||||
}
|
||||
return get_base_unit($units, $unit->{"base_unit"}, $factor * $unit->{"factor"});
|
||||
}
|
||||
sub retrieve_units {
|
||||
my ($myconfig, $form, $type, $prefix) = @_;
|
||||
my $query = "SELECT *, base_unit AS original_base_unit FROM units";
|
||||
my @values;
|
||||
if ($type) {
|
||||
$query .= " WHERE (type = ?)";
|
||||
@values = ($type);
|
||||
}
|
||||
my $sth = $dbh->prepare($query);
|
||||
$sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")");
|
||||
my $units = {};
|
||||
while (my $ref = $sth->fetchrow_hashref()) {
|
||||
$units->{$ref->{"name"}} = $ref;
|
||||
}
|
||||
$sth->finish();
|
||||
my $query_lang = "SELECT id, template_code FROM language ORDER BY description";
|
||||
$sth = $dbh->prepare($query_lang);
|
||||
$sth->execute() || $form->dberror($query_lang);
|
||||
my @languages;
|
||||
while ($ref = $sth->fetchrow_hashref()) {
|
||||
push(@languages, $ref);
|
||||
}
|
||||
$sth->finish();
|
||||
foreach my $unit (values(%{$units})) {
|
||||
($unit->{"${prefix}base_unit"}, $unit->{"${prefix}factor"}) = get_base_unit($units, $unit->{"name"});
|
||||
}
|
||||
return $units;
|
||||
}
|
||||
sub unit_select_data {
|
||||
my ($units, $selected, $empty_entry) = @_;
|
||||
my $select = [];
|
||||
if ($empty_entry) {
|
||||
push(@{$select}, { "name" => "", "base_unit" => "", "factor" => "", "selected" => "" });
|
||||
}
|
||||
foreach my $unit (sort({ lc($a) cmp lc($b) } keys(%{$units}))) {
|
||||
push(@{$select}, { "name" => $unit,
|
||||
"base_unit" => $units->{$unit}->{"base_unit"},
|
||||
"factor" => $units->{$unit}->{"factor"},
|
||||
"selected" => ($unit eq $selected) ? "selected" : "" });
|
||||
}
|
||||
return $select;
|
||||
}
|
||||
| 3d6e7124 | Moritz Bunkus | sub update_units_add_unit {
|
||
my $form = $main::form;
|
||||
return 0 unless ($form->{"new_name"});
|
||||
return myshowerror($dbup_locale->text("The name is missing."))
|
||||
if ($form->{"new_name"} eq "");
|
||||
| ce30a8c3 | Moritz Bunkus | my $units = retrieve_units(\%dbup_myconfig, $form);
|
||
| 3d6e7124 | Moritz Bunkus | return myshowerror($dbup_locale->text("A unit with this name does already exist."))
|
||
if ($units->{$form->{"new_name"}});
|
||||
| ce30a8c3 | Moritz Bunkus | $units = retrieve_units(\%dbup_myconfig, $form, $form->{"unit_type"});
|
||
| 3d6e7124 | Moritz Bunkus | |||
my ($base_unit, $factor);
|
||||
if ($form->{"new_base_unit"}) {
|
||||
return myshowerror($dbup_locale->text("The base unit does not exist."))
|
||||
unless (defined($units->{$form->{"new_base_unit"}}));
|
||||
return myshowerror($dbup_locale->text("The factor is missing."))
|
||||
if ($form->{"new_factor"} eq "");
|
||||
$factor = $form->parse_amount(\%dbup_myconfig, $form->{"new_factor"});
|
||||
return myshowerror($dbup_locale->text("The factor is missing."))
|
||||
unless ($factor);
|
||||
$base_unit = $form->{"new_base_unit"};
|
||||
}
|
||||
my $query = "INSERT INTO units " .
|
||||
"(name, base_unit, factor, type) " .
|
||||
"VALUES (?, ?, ?, ?)";
|
||||
$dbh->do($query, undef, $form->{"new_name"}, $base_unit, $factor,
|
||||
$form->{"unit_type"}) ||
|
||||
| ae1b5cba | Moritz Bunkus | mydberror($query .
|
||
| 3d6e7124 | Moritz Bunkus | " ($form->{new_name}, $base_unit, $factor, $form->{unit_type})");
|
||
$dbh->commit();
|
||||
$dbh->begin_work();
|
||||
$form->{"saved_message"} = $dbup_locale->text("The unit has been saved.");
|
||||
return 0;
|
||||
}
|
||||
sub update_units_assign_units {
|
||||
my ($query, $sth, @values);
|
||||
my $form = $main::form;
|
||||
foreach my $table (qw(parts invoice orderitems rmaitems)) {
|
||||
$query = "UPDATE $table SET unit = ? WHERE lower(unit) = ?";
|
||||
$sth = $dbh->prepare($query);
|
||||
for (my $i = 1; $i <= $form->{"rowcount"}; $i++) {
|
||||
next unless ($form->{"new_unit_$i"} && $form->{"old_unit_$i"});
|
||||
@values = ($form->{"new_unit_$i"}, lc($form->{"old_unit_$i"}));
|
||||
$sth->execute(@values) ||
|
||||
| ae1b5cba | Moritz Bunkus | mydberror($query . " (" . join(", ", @values) . ")");
|
||
| 3d6e7124 | Moritz Bunkus | }
|
||
}
|
||||
$sth->finish();
|
||||
$dbh->commit();
|
||||
$dbh->begin_work();
|
||||
}
|
||||
sub update_units_assign_known {
|
||||
my $form = $main::form;
|
||||
my %unit_name_mapping = (
|
||||
"st" => "Stck",
|
||||
"st." => "Stck",
|
||||
| 5ee0531b | Moritz Bunkus | "stk" => "Stck",
|
||
| 3d6e7124 | Moritz Bunkus | "pc" => "Stck",
|
||
"pcs" => "Stck",
|
||||
"ea" => "Stck",
|
||||
| f530dd67 | Moritz Bunkus | |||
"h" => "Std",
|
||||
"stunde" => "Std",
|
||||
"tage" => "Tag",
|
||||
| 3d6e7124 | Moritz Bunkus | );
|
||
my $i = 1;
|
||||
foreach my $k (keys(%unit_name_mapping)) {
|
||||
$form->{"old_unit_$i"} = $k;
|
||||
$form->{"new_unit_$i"} = $unit_name_mapping{$k};
|
||||
$i++;
|
||||
}
|
||||
$form->{"rowcount"} = scalar(keys(%unit_name_mapping));
|
||||
| ae1b5cba | Moritz Bunkus | update_units_assign_units();
|
||
| 3d6e7124 | Moritz Bunkus | }
|
||
sub update_units_steps_1_2 {
|
||||
my (%unknown_dimension_units, %unknown_service_units);
|
||||
my $form = $main::form;
|
||||
foreach my $table (qw(parts invoice orderitems rmaitems)) {
|
||||
my ($query, $sth, $ref);
|
||||
if ($table eq "parts") {
|
||||
| 6ec6983b | Moritz Bunkus | $query = "SELECT unit, inventory_accno_id, assembly FROM parts " .
|
||
| 3d6e7124 | Moritz Bunkus | "WHERE NOT ((unit = '') OR unit ISNULL OR " .
|
||
" unit IN (SELECT name FROM units))";
|
||||
} else {
|
||||
| 7ec33473 | Moritz Bunkus | $query = "SELECT t.unit, p.inventory_accno_id, p.assembly " .
|
||
| 3d6e7124 | Moritz Bunkus | "FROM $table t " .
|
||
"LEFT JOIN parts p ON p.id = t.parts_id " .
|
||||
"WHERE NOT ((t.unit = '') OR t.unit ISNULL OR " .
|
||||
" t.unit IN (SELECT name FROM units))";
|
||||
}
|
||||
$sth = $dbh->prepare($query);
|
||||
| ae1b5cba | Moritz Bunkus | $sth->execute() || mydberror($query);
|
||
| 3d6e7124 | Moritz Bunkus | |||
while ($ref = $sth->fetchrow_hashref()) {
|
||||
| 6ec6983b | Moritz Bunkus | if ($ref->{"inventory_accno_id"} || $ref->{"assembly"}) {
|
||
| 3d6e7124 | Moritz Bunkus | $unknown_dimension_units{$ref->{"unit"}} = 1;
|
||
} else {
|
||||
$unknown_service_units{$ref->{"unit"}} = 1;
|
||||
}
|
||||
}
|
||||
$sth->finish();
|
||||
}
|
||||
if (scalar(keys(%unknown_dimension_units)) != 0) {
|
||||
| ce30a8c3 | Moritz Bunkus | my $units = retrieve_units(\%dbup_myconfig, $form, "dimension");
|
||
my $ddbox = unit_select_data($units, undef, 1);
|
||||
| 3d6e7124 | Moritz Bunkus | |||
my @unknown_parts;
|
||||
map({ push(@unknown_parts, { "name" => $_, "NEW_UNITS" => $ddbox }); }
|
||||
sort({ lc($a) cmp lc($b) } keys(%unknown_dimension_units)));
|
||||
| 9aaca433 | Moritz Bunkus | print $form->parse_html_template("dbupgrade/units_parts",
|
||
{ "NEW_BASE_UNIT_DDBOX" => $ddbox,
|
||||
"UNKNOWN_PART_UNITS" => \@unknown_parts,
|
||||
});
|
||||
| 3d6e7124 | Moritz Bunkus | |||
return 2;
|
||||
} else {
|
||||
| 9aaca433 | Moritz Bunkus | print $form->parse_html_template("dbupgrade/units_parts_done");
|
||
| 3d6e7124 | Moritz Bunkus | }
|
||
if (scalar(keys(%unknown_service_units)) != 0) {
|
||||
| ce30a8c3 | Moritz Bunkus | my $units = retrieve_units(\%dbup_myconfig, $form, "service");
|
||
my $ddbox = unit_select_data($units, undef, 1);
|
||||
| 3d6e7124 | Moritz Bunkus | |||
my @unknown_services;
|
||||
map({ push(@unknown_services, { "name" => $_, "NEW_UNITS" => $ddbox }); }
|
||||
sort({ lc($a) cmp lc($b) } keys(%unknown_service_units)));
|
||||
| 9aaca433 | Moritz Bunkus | print $form->parse_html_template("dbupgrade/units_services",
|
||
{ "NEW_BASE_UNIT_DDBOX" => $ddbox,
|
||||
"UNKNOWN_PART_UNITS" => \@unknown_services,
|
||||
}));
|
||||
| 3d6e7124 | Moritz Bunkus | |||
return 2;
|
||||
} else {
|
||||
| 9aaca433 | Moritz Bunkus | print $form->parse_html_template("dbupgrade/units_services_done");
|
||
| 3d6e7124 | Moritz Bunkus | }
|
||
return 0;
|
||||
}
|
||||
sub update_units_step_3 {
|
||||
my $form = $main::form;
|
||||
my $query = "SELECT ";
|
||||
foreach my $table (qw(parts invoice orderitems rmaitems)) {
|
||||
$query .= "(SELECT COUNT(*) FROM $table " .
|
||||
"WHERE (unit ISNULL) OR (unit = '')) +";
|
||||
}
|
||||
substr($query, -1, 1) = "AS has_unassigned";
|
||||
my ($has_unassigned) = $dbh->selectrow_array($query);
|
||||
if ($has_unassigned) {
|
||||
| ce30a8c3 | Moritz Bunkus | my $dimension_units = retrieve_units(\%dbup_myconfig, $form,
|
||
| 3d6e7124 | Moritz Bunkus | "dimension");
|
||
| ce30a8c3 | Moritz Bunkus | my $dimension_ddbox = unit_select_data($dimension_units);
|
||
| 3d6e7124 | Moritz Bunkus | |||
| ce30a8c3 | Moritz Bunkus | my $service_units = retrieve_units(\%dbup_myconfig, $form, "service");
|
||
my $service_ddbox = unit_select_data($service_units);
|
||||
| 3d6e7124 | Moritz Bunkus | |||
| 9aaca433 | Moritz Bunkus | print $form->parse_html_template("dbupgrade/units_set_default",
|
||
{ "DIMENSION_DDBOX" => $dimension_ddbox,
|
||||
"SERVICE_DDBOX" => $service_ddbox });
|
||||
| 3d6e7124 | Moritz Bunkus | return 2;
|
||
} else {
|
||||
| 9aaca433 | Moritz Bunkus | print $form->parse_html_template("dbupgrade/units_set_default_done");
|
||
| 3d6e7124 | Moritz Bunkus | return 1;
|
||
}
|
||||
}
|
||||
sub update_units_set_default {
|
||||
my $form = $main::form;
|
||||
foreach my $table (qw(parts invoice orderitems rmaitems)) {
|
||||
my $base_query = "UPDATE $table SET unit = " .
|
||||
$dbh->quote($form->{"default_service_unit"}) . " " .
|
||||
"WHERE ((unit ISNULL) OR (unit = '')) AND ";
|
||||
my $query;
|
||||
if ($table eq "parts") {
|
||||
$query = "UPDATE $table SET unit = " .
|
||||
$dbh->quote($form->{"default_dimension_unit"}) . " " .
|
||||
| 6ec6983b | Moritz Bunkus | "WHERE ((unit ISNULL) OR (unit = '')) AND " .
|
||
"(assembly OR (inventory_accno_id > 0))";
|
||||
| 3d6e7124 | Moritz Bunkus | } else {
|
||
$query = "UPDATE $table SET unit = " .
|
||||
$dbh->quote($form->{"default_dimension_unit"}) . " " .
|
||||
"WHERE ((unit ISNULL) OR (unit = '')) AND " .
|
||||
| 6ec6983b | Moritz Bunkus | "parts_id IN (SELECT id FROM parts WHERE " .
|
||
"(assembly OR (inventory_accno_id > 0)))";
|
||||
| 3d6e7124 | Moritz Bunkus | }
|
||
| ae1b5cba | Moritz Bunkus | $dbh->do($query) || mydberror($query);
|
||
| 3d6e7124 | Moritz Bunkus | |||
if ($table eq "parts") {
|
||||
$query = "UPDATE $table SET unit = " .
|
||||
$dbh->quote($form->{"default_service_unit"}) . " " .
|
||||
"WHERE ((unit ISNULL) OR (unit = '')) AND " .
|
||||
| 6ec6983b | Moritz Bunkus | "((inventory_accno_id ISNULL) OR (inventory_accno_id = 0)) AND " .
|
||
"NOT assembly";
|
||||
| 3d6e7124 | Moritz Bunkus | } else {
|
||
$query = "UPDATE $table SET unit = " .
|
||||
$dbh->quote($form->{"default_service_unit"}) . " " .
|
||||
"WHERE ((unit ISNULL) OR (unit = '')) AND " .
|
||||
"parts_id IN (SELECT id FROM parts " .
|
||||
| 6ec6983b | Moritz Bunkus | "WHERE ((inventory_accno_id ISNULL) OR (inventory_accno_id = 0)) " .
|
||
"AND NOT assembly)";
|
||||
| 3d6e7124 | Moritz Bunkus | }
|
||
| ae1b5cba | Moritz Bunkus | $dbh->do($query) || mydberror($query);
|
||
| 3d6e7124 | Moritz Bunkus | }
|
||
}
|
||||
sub update_units {
|
||||
my $form = $main::form;
|
||||
my $res;
|
||||
| 9aaca433 | Moritz Bunkus | print $form->parse_html_template("dbupgrade/units_header");
|
||
| 3d6e7124 | Moritz Bunkus | |||
if ($form->{"action2"} eq "add_unit") {
|
||||
| ae1b5cba | Moritz Bunkus | $res = update_units_add_unit();
|
||
| 3d6e7124 | Moritz Bunkus | return $res if ($res);
|
||
} elsif ($form->{"action2"} eq "assign_units") {
|
||||
| ae1b5cba | Moritz Bunkus | update_units_assign_units();
|
||
| 3d6e7124 | Moritz Bunkus | |||
} elsif ($form->{"action2"} eq "set_default") {
|
||||
| ae1b5cba | Moritz Bunkus | update_units_set_default();
|
||
| 3d6e7124 | Moritz Bunkus | |||
}
|
||||
| ae1b5cba | Moritz Bunkus | update_units_assign_known();
|
||
| 3d6e7124 | Moritz Bunkus | |||
| ae1b5cba | Moritz Bunkus | $res = update_units_steps_1_2();
|
||
| 3d6e7124 | Moritz Bunkus | return $res if ($res);
|
||
| ae1b5cba | Moritz Bunkus | return update_units_step_3();
|
||
| 3d6e7124 | Moritz Bunkus | }
|
||
| ae1b5cba | Moritz Bunkus | update_units();
|