#!/usr/bin/perl
-die("This script cannot be run from the command line.") unless ($main::form);
+# Datenbankupgrade: Einfuehrung von Einheiten
-use SL::AM;
+die("This script cannot be run from the command line.") unless ($main::form);
sub mydberror {
my ($msg) = @_;
sub myshowerror {
my ($msg) = @_;
- print($main::form->parse_html_template("dbupgrade/units_error",
- { "message" => $msg }));
+ print $main::form->parse_html_template("dbupgrade/units_error", { "message" => $msg });
return 2;
}
+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;
+}
+
sub update_units_add_unit {
my $form = $main::form;
return myshowerror($dbup_locale->text("The name is missing."))
if ($form->{"new_name"} eq "");
- my $units = AM->retrieve_units(\%dbup_myconfig, $form);
+ my $units = retrieve_units(\%dbup_myconfig, $form);
return myshowerror($dbup_locale->text("A unit with this name does already exist."))
if ($units->{$form->{"new_name"}});
- $units = AM->retrieve_units(\%dbup_myconfig, $form, $form->{"unit_type"});
+ $units = retrieve_units(\%dbup_myconfig, $form, $form->{"unit_type"});
my ($base_unit, $factor);
if ($form->{"new_base_unit"}) {
my ($query, $sth, $ref);
if ($table eq "parts") {
- $query = "SELECT unit, inventory_accno_id FROM parts " .
+ $query = "SELECT unit, inventory_accno_id, assembly FROM parts " .
"WHERE NOT ((unit = '') OR unit ISNULL OR " .
" unit IN (SELECT name FROM units))";
} else {
- $query = "SELECT t.unit, p.inventory_accno_id " .
+ $query = "SELECT t.unit, p.inventory_accno_id, p.assembly " .
"FROM $table t " .
"LEFT JOIN parts p ON p.id = t.parts_id " .
"WHERE NOT ((t.unit = '') OR t.unit ISNULL OR " .
$sth->execute() || mydberror($query);
while ($ref = $sth->fetchrow_hashref()) {
- if ($ref->{"inventory_accno_id"}) {
+ if ($ref->{"inventory_accno_id"} || $ref->{"assembly"}) {
$unknown_dimension_units{$ref->{"unit"}} = 1;
} else {
}
if (scalar(keys(%unknown_dimension_units)) != 0) {
- my $units = AM->retrieve_units(\%dbup_myconfig, $form, "dimension");
- my $ddbox = AM->unit_select_data($units, undef, 1);
+ my $units = retrieve_units(\%dbup_myconfig, $form, "dimension");
+ my $ddbox = unit_select_data($units, undef, 1);
my @unknown_parts;
map({ push(@unknown_parts, { "name" => $_, "NEW_UNITS" => $ddbox }); }
sort({ lc($a) cmp lc($b) } keys(%unknown_dimension_units)));
- print($form->parse_html_template("dbupgrade/units_parts",
+ print $form->parse_html_template("dbupgrade/units_parts",
{ "NEW_BASE_UNIT_DDBOX" => $ddbox,
- "UNKNOWN_PART_UNITS" => \@unknown_parts,
- }));
+ "UNKNOWN_PART_UNITS" => \@unknown_parts,
+ });
return 2;
} else {
- print($form->parse_html_template("dbupgrade/units_parts_done"));
+ print $form->parse_html_template("dbupgrade/units_parts_done");
}
if (scalar(keys(%unknown_service_units)) != 0) {
- my $units = AM->retrieve_units(\%dbup_myconfig, $form, "service");
- my $ddbox = AM->unit_select_data($units, undef, 1);
+ my $units = retrieve_units(\%dbup_myconfig, $form, "service");
+ my $ddbox = unit_select_data($units, undef, 1);
my @unknown_services;
map({ push(@unknown_services, { "name" => $_, "NEW_UNITS" => $ddbox }); }
sort({ lc($a) cmp lc($b) } keys(%unknown_service_units)));
- print($form->parse_html_template("dbupgrade/units_services",
+ print $form->parse_html_template("dbupgrade/units_services",
{ "NEW_BASE_UNIT_DDBOX" => $ddbox,
- "UNKNOWN_PART_UNITS" => \@unknown_services,
- }));
+ "UNKNOWN_PART_UNITS" => \@unknown_services,
+ });
return 2;
} else {
- print($form->parse_html_template("dbupgrade/units_services_done"));
+ print $form->parse_html_template("dbupgrade/units_services_done");
}
return 0;
my ($has_unassigned) = $dbh->selectrow_array($query);
if ($has_unassigned) {
- my $dimension_units = AM->retrieve_units(\%dbup_myconfig, $form,
+ my $dimension_units = retrieve_units(\%dbup_myconfig, $form,
"dimension");
- my $dimension_ddbox = AM->unit_select_data($dimension_units);
+ my $dimension_ddbox = unit_select_data($dimension_units);
- my $service_units = AM->retrieve_units(\%dbup_myconfig, $form, "service");
- my $service_ddbox = AM->unit_select_data($service_units);
+ my $service_units = retrieve_units(\%dbup_myconfig, $form, "service");
+ my $service_ddbox = unit_select_data($service_units);
- print($form->parse_html_template("dbupgrade/units_set_default",
+ print $form->parse_html_template("dbupgrade/units_set_default",
{ "DIMENSION_DDBOX" => $dimension_ddbox,
- "SERVICE_DDBOX" => $service_ddbox }));
+ "SERVICE_DDBOX" => $service_ddbox });
return 2;
} else {
- print($form->parse_html_template("dbupgrade/units_set_default_done"));
+ print $form->parse_html_template("dbupgrade/units_set_default_done");
return 1;
}
}
if ($table eq "parts") {
$query = "UPDATE $table SET unit = " .
$dbh->quote($form->{"default_dimension_unit"}) . " " .
- "WHERE ((unit ISNULL) OR (unit = '')) AND (inventory_accno_id > 0)";
+ "WHERE ((unit ISNULL) OR (unit = '')) AND " .
+ "(assembly OR (inventory_accno_id > 0))";
} else {
$query = "UPDATE $table SET unit = " .
$dbh->quote($form->{"default_dimension_unit"}) . " " .
"WHERE ((unit ISNULL) OR (unit = '')) AND " .
- "parts_id IN (SELECT id FROM parts WHERE (inventory_accno_id > 0))";
+ "parts_id IN (SELECT id FROM parts WHERE " .
+ "(assembly OR (inventory_accno_id > 0)))";
}
$dbh->do($query) || mydberror($query);
$query = "UPDATE $table SET unit = " .
$dbh->quote($form->{"default_service_unit"}) . " " .
"WHERE ((unit ISNULL) OR (unit = '')) AND " .
- "(inventory_accno_id ISNULL) OR (inventory_accno_id = 0)";
+ "((inventory_accno_id ISNULL) OR (inventory_accno_id = 0)) AND " .
+ "NOT assembly";
} 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 " .
- "WHERE (inventory_accno_id ISNULL) OR (inventory_accno_id = 0))";
+ "WHERE ((inventory_accno_id ISNULL) OR (inventory_accno_id = 0)) " .
+ "AND NOT assembly)";
}
$dbh->do($query) || mydberror($query);
my $res;
- print($form->parse_html_template("dbupgrade/units_header"));
+ print $form->parse_html_template("dbupgrade/units_header");
if ($form->{"action2"} eq "add_unit") {
$res = update_units_add_unit();