3 # Datenbankupgrade: Einfuehrung von Einheiten
5 die("This script cannot be run from the command line.") unless ($main::form);
9 die($dbup_locale->text("Database update error:") .
10 "<br>$msg<br>" . $DBI::errstr);
16 print($main::form->parse_html_template("dbupgrade/units_error",
17 { "message" => $msg }));
22 my ($units, $unit_name, $factor) = @_;
24 $factor = 1 unless ($factor);
26 my $unit = $units->{$unit_name};
28 if (!defined($unit) || !$unit->{"base_unit"} ||
29 ($unit_name eq $unit->{"base_unit"})) {
30 return ($unit_name, $factor);
33 return get_base_unit($units, $unit->{"base_unit"}, $factor * $unit->{"factor"});
37 my ($myconfig, $form, $type, $prefix) = @_;
39 my $query = "SELECT *, base_unit AS original_base_unit FROM units";
42 $query .= " WHERE (type = ?)";
46 my $sth = $dbh->prepare($query);
47 $sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")");
50 while (my $ref = $sth->fetchrow_hashref()) {
51 $units->{$ref->{"name"}} = $ref;
55 my $query_lang = "SELECT id, template_code FROM language ORDER BY description";
56 $sth = $dbh->prepare($query_lang);
57 $sth->execute() || $form->dberror($query_lang);
59 while ($ref = $sth->fetchrow_hashref()) {
60 push(@languages, $ref);
64 foreach my $unit (values(%{$units})) {
65 ($unit->{"${prefix}base_unit"}, $unit->{"${prefix}factor"}) = get_base_unit($units, $unit->{"name"});
71 sub unit_select_data {
72 my ($units, $selected, $empty_entry) = @_;
77 push(@{$select}, { "name" => "", "base_unit" => "", "factor" => "", "selected" => "" });
80 foreach my $unit (sort({ lc($a) cmp lc($b) } keys(%{$units}))) {
81 push(@{$select}, { "name" => $unit,
82 "base_unit" => $units->{$unit}->{"base_unit"},
83 "factor" => $units->{$unit}->{"factor"},
84 "selected" => ($unit eq $selected) ? "selected" : "" });
90 sub update_units_add_unit {
91 my $form = $main::form;
93 return 0 unless ($form->{"new_name"});
95 return myshowerror($dbup_locale->text("The name is missing."))
96 if ($form->{"new_name"} eq "");
97 my $units = retrieve_units(\%dbup_myconfig, $form);
98 return myshowerror($dbup_locale->text("A unit with this name does already exist."))
99 if ($units->{$form->{"new_name"}});
100 $units = retrieve_units(\%dbup_myconfig, $form, $form->{"unit_type"});
102 my ($base_unit, $factor);
103 if ($form->{"new_base_unit"}) {
104 return myshowerror($dbup_locale->text("The base unit does not exist."))
105 unless (defined($units->{$form->{"new_base_unit"}}));
107 return myshowerror($dbup_locale->text("The factor is missing."))
108 if ($form->{"new_factor"} eq "");
109 $factor = $form->parse_amount(\%dbup_myconfig, $form->{"new_factor"});
110 return myshowerror($dbup_locale->text("The factor is missing."))
112 $base_unit = $form->{"new_base_unit"};
115 my $query = "INSERT INTO units " .
116 "(name, base_unit, factor, type) " .
117 "VALUES (?, ?, ?, ?)";
118 $dbh->do($query, undef, $form->{"new_name"}, $base_unit, $factor,
119 $form->{"unit_type"}) ||
121 " ($form->{new_name}, $base_unit, $factor, $form->{unit_type})");
125 $form->{"saved_message"} = $dbup_locale->text("The unit has been saved.");
130 sub update_units_assign_units {
131 my ($query, $sth, @values);
133 my $form = $main::form;
135 foreach my $table (qw(parts invoice orderitems rmaitems)) {
136 $query = "UPDATE $table SET unit = ? WHERE lower(unit) = ?";
137 $sth = $dbh->prepare($query);
139 for (my $i = 1; $i <= $form->{"rowcount"}; $i++) {
140 next unless ($form->{"new_unit_$i"} && $form->{"old_unit_$i"});
141 @values = ($form->{"new_unit_$i"}, lc($form->{"old_unit_$i"}));
142 $sth->execute(@values) ||
143 mydberror($query . " (" . join(", ", @values) . ")");
152 sub update_units_assign_known {
153 my $form = $main::form;
155 my %unit_name_mapping = (
169 foreach my $k (keys(%unit_name_mapping)) {
170 $form->{"old_unit_$i"} = $k;
171 $form->{"new_unit_$i"} = $unit_name_mapping{$k};
174 $form->{"rowcount"} = scalar(keys(%unit_name_mapping));
176 update_units_assign_units();
179 sub update_units_steps_1_2 {
180 my (%unknown_dimension_units, %unknown_service_units);
182 my $form = $main::form;
184 foreach my $table (qw(parts invoice orderitems rmaitems)) {
185 my ($query, $sth, $ref);
187 if ($table eq "parts") {
188 $query = "SELECT unit, inventory_accno_id, assembly FROM parts " .
189 "WHERE NOT ((unit = '') OR unit ISNULL OR " .
190 " unit IN (SELECT name FROM units))";
193 $query = "SELECT t.unit, p.inventory_accno_id, p.assembly " .
195 "LEFT JOIN parts p ON p.id = t.parts_id " .
196 "WHERE NOT ((t.unit = '') OR t.unit ISNULL OR " .
197 " t.unit IN (SELECT name FROM units))";
199 $sth = $dbh->prepare($query);
200 $sth->execute() || mydberror($query);
202 while ($ref = $sth->fetchrow_hashref()) {
203 if ($ref->{"inventory_accno_id"} || $ref->{"assembly"}) {
204 $unknown_dimension_units{$ref->{"unit"}} = 1;
207 $unknown_service_units{$ref->{"unit"}} = 1;
214 if (scalar(keys(%unknown_dimension_units)) != 0) {
215 my $units = retrieve_units(\%dbup_myconfig, $form, "dimension");
216 my $ddbox = unit_select_data($units, undef, 1);
219 map({ push(@unknown_parts, { "name" => $_, "NEW_UNITS" => $ddbox }); }
220 sort({ lc($a) cmp lc($b) } keys(%unknown_dimension_units)));
222 print($form->parse_html_template("dbupgrade/units_parts",
223 { "NEW_BASE_UNIT_DDBOX" => $ddbox,
224 "UNKNOWN_PART_UNITS" => \@unknown_parts,
230 print($form->parse_html_template("dbupgrade/units_parts_done"));
233 if (scalar(keys(%unknown_service_units)) != 0) {
234 my $units = retrieve_units(\%dbup_myconfig, $form, "service");
235 my $ddbox = unit_select_data($units, undef, 1);
237 my @unknown_services;
238 map({ push(@unknown_services, { "name" => $_, "NEW_UNITS" => $ddbox }); }
239 sort({ lc($a) cmp lc($b) } keys(%unknown_service_units)));
241 print($form->parse_html_template("dbupgrade/units_services",
242 { "NEW_BASE_UNIT_DDBOX" => $ddbox,
243 "UNKNOWN_PART_UNITS" => \@unknown_services,
249 print($form->parse_html_template("dbupgrade/units_services_done"));
255 sub update_units_step_3 {
256 my $form = $main::form;
258 my $query = "SELECT ";
259 foreach my $table (qw(parts invoice orderitems rmaitems)) {
260 $query .= "(SELECT COUNT(*) FROM $table " .
261 "WHERE (unit ISNULL) OR (unit = '')) +";
263 substr($query, -1, 1) = "AS has_unassigned";
264 my ($has_unassigned) = $dbh->selectrow_array($query);
266 if ($has_unassigned) {
267 my $dimension_units = retrieve_units(\%dbup_myconfig, $form,
269 my $dimension_ddbox = unit_select_data($dimension_units);
271 my $service_units = retrieve_units(\%dbup_myconfig, $form, "service");
272 my $service_ddbox = unit_select_data($service_units);
274 print($form->parse_html_template("dbupgrade/units_set_default",
275 { "DIMENSION_DDBOX" => $dimension_ddbox,
276 "SERVICE_DDBOX" => $service_ddbox }));
280 print($form->parse_html_template("dbupgrade/units_set_default_done"));
285 sub update_units_set_default {
286 my $form = $main::form;
288 foreach my $table (qw(parts invoice orderitems rmaitems)) {
289 my $base_query = "UPDATE $table SET unit = " .
290 $dbh->quote($form->{"default_service_unit"}) . " " .
291 "WHERE ((unit ISNULL) OR (unit = '')) AND ";
294 if ($table eq "parts") {
295 $query = "UPDATE $table SET unit = " .
296 $dbh->quote($form->{"default_dimension_unit"}) . " " .
297 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
298 "(assembly OR (inventory_accno_id > 0))";
300 $query = "UPDATE $table SET unit = " .
301 $dbh->quote($form->{"default_dimension_unit"}) . " " .
302 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
303 "parts_id IN (SELECT id FROM parts WHERE " .
304 "(assembly OR (inventory_accno_id > 0)))";
307 $dbh->do($query) || mydberror($query);
309 if ($table eq "parts") {
310 $query = "UPDATE $table SET unit = " .
311 $dbh->quote($form->{"default_service_unit"}) . " " .
312 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
313 "((inventory_accno_id ISNULL) OR (inventory_accno_id = 0)) AND " .
316 $query = "UPDATE $table SET unit = " .
317 $dbh->quote($form->{"default_service_unit"}) . " " .
318 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
319 "parts_id IN (SELECT id FROM parts " .
320 "WHERE ((inventory_accno_id ISNULL) OR (inventory_accno_id = 0)) " .
324 $dbh->do($query) || mydberror($query);
329 my $form = $main::form;
333 print($form->parse_html_template("dbupgrade/units_header"));
335 if ($form->{"action2"} eq "add_unit") {
336 $res = update_units_add_unit();
337 return $res if ($res);
339 } elsif ($form->{"action2"} eq "assign_units") {
340 update_units_assign_units();
342 } elsif ($form->{"action2"} eq "set_default") {
343 update_units_set_default();
347 update_units_assign_known();
349 $res = update_units_steps_1_2();
350 return $res if ($res);
352 return update_units_step_3();