3 # Datenbankupgrade: Einfuehrung von Einheiten
5 die("This script cannot be run from the command line.") unless ($main::form);
11 die($dbup_locale->text("Database update error:") .
12 "<br>$msg<br>" . $DBI::errstr);
18 print($main::form->parse_html_template("dbupgrade/units_error",
19 { "message" => $msg }));
23 sub update_units_add_unit {
24 my $form = $main::form;
26 return 0 unless ($form->{"new_name"});
28 return myshowerror($dbup_locale->text("The name is missing."))
29 if ($form->{"new_name"} eq "");
30 my $units = AM->retrieve_units(\%dbup_myconfig, $form);
31 return myshowerror($dbup_locale->text("A unit with this name does already exist."))
32 if ($units->{$form->{"new_name"}});
33 $units = AM->retrieve_units(\%dbup_myconfig, $form, $form->{"unit_type"});
35 my ($base_unit, $factor);
36 if ($form->{"new_base_unit"}) {
37 return myshowerror($dbup_locale->text("The base unit does not exist."))
38 unless (defined($units->{$form->{"new_base_unit"}}));
40 return myshowerror($dbup_locale->text("The factor is missing."))
41 if ($form->{"new_factor"} eq "");
42 $factor = $form->parse_amount(\%dbup_myconfig, $form->{"new_factor"});
43 return myshowerror($dbup_locale->text("The factor is missing."))
45 $base_unit = $form->{"new_base_unit"};
48 my $query = "INSERT INTO units " .
49 "(name, base_unit, factor, type) " .
50 "VALUES (?, ?, ?, ?)";
51 $dbh->do($query, undef, $form->{"new_name"}, $base_unit, $factor,
52 $form->{"unit_type"}) ||
54 " ($form->{new_name}, $base_unit, $factor, $form->{unit_type})");
58 $form->{"saved_message"} = $dbup_locale->text("The unit has been saved.");
63 sub update_units_assign_units {
64 my ($query, $sth, @values);
66 my $form = $main::form;
68 foreach my $table (qw(parts invoice orderitems rmaitems)) {
69 $query = "UPDATE $table SET unit = ? WHERE lower(unit) = ?";
70 $sth = $dbh->prepare($query);
72 for (my $i = 1; $i <= $form->{"rowcount"}; $i++) {
73 next unless ($form->{"new_unit_$i"} && $form->{"old_unit_$i"});
74 @values = ($form->{"new_unit_$i"}, lc($form->{"old_unit_$i"}));
75 $sth->execute(@values) ||
76 mydberror($query . " (" . join(", ", @values) . ")");
85 sub update_units_assign_known {
86 my $form = $main::form;
88 my %unit_name_mapping = (
102 foreach my $k (keys(%unit_name_mapping)) {
103 $form->{"old_unit_$i"} = $k;
104 $form->{"new_unit_$i"} = $unit_name_mapping{$k};
107 $form->{"rowcount"} = scalar(keys(%unit_name_mapping));
109 update_units_assign_units();
112 sub update_units_steps_1_2 {
113 my (%unknown_dimension_units, %unknown_service_units);
115 my $form = $main::form;
117 foreach my $table (qw(parts invoice orderitems rmaitems)) {
118 my ($query, $sth, $ref);
120 if ($table eq "parts") {
121 $query = "SELECT unit, inventory_accno_id FROM parts " .
122 "WHERE NOT ((unit = '') OR unit ISNULL OR " .
123 " unit IN (SELECT name FROM units))";
126 $query = "SELECT t.unit, p.inventory_accno_id " .
128 "LEFT JOIN parts p ON p.id = t.parts_id " .
129 "WHERE NOT ((t.unit = '') OR t.unit ISNULL OR " .
130 " t.unit IN (SELECT name FROM units))";
132 $sth = $dbh->prepare($query);
133 $sth->execute() || mydberror($query);
135 while ($ref = $sth->fetchrow_hashref()) {
136 if ($ref->{"inventory_accno_id"}) {
137 $unknown_dimension_units{$ref->{"unit"}} = 1;
140 $unknown_service_units{$ref->{"unit"}} = 1;
147 if (scalar(keys(%unknown_dimension_units)) != 0) {
148 my $units = AM->retrieve_units(\%dbup_myconfig, $form, "dimension");
149 my $ddbox = AM->unit_select_data($units, undef, 1);
152 map({ push(@unknown_parts, { "name" => $_, "NEW_UNITS" => $ddbox }); }
153 sort({ lc($a) cmp lc($b) } keys(%unknown_dimension_units)));
155 print($form->parse_html_template("dbupgrade/units_parts",
156 { "NEW_BASE_UNIT_DDBOX" => $ddbox,
157 "UNKNOWN_PART_UNITS" => \@unknown_parts,
163 print($form->parse_html_template("dbupgrade/units_parts_done"));
166 if (scalar(keys(%unknown_service_units)) != 0) {
167 my $units = AM->retrieve_units(\%dbup_myconfig, $form, "service");
168 my $ddbox = AM->unit_select_data($units, undef, 1);
170 my @unknown_services;
171 map({ push(@unknown_services, { "name" => $_, "NEW_UNITS" => $ddbox }); }
172 sort({ lc($a) cmp lc($b) } keys(%unknown_service_units)));
174 print($form->parse_html_template("dbupgrade/units_services",
175 { "NEW_BASE_UNIT_DDBOX" => $ddbox,
176 "UNKNOWN_PART_UNITS" => \@unknown_services,
182 print($form->parse_html_template("dbupgrade/units_services_done"));
188 sub update_units_step_3 {
189 my $form = $main::form;
191 my $query = "SELECT ";
192 foreach my $table (qw(parts invoice orderitems rmaitems)) {
193 $query .= "(SELECT COUNT(*) FROM $table " .
194 "WHERE (unit ISNULL) OR (unit = '')) +";
196 substr($query, -1, 1) = "AS has_unassigned";
197 my ($has_unassigned) = $dbh->selectrow_array($query);
199 if ($has_unassigned) {
200 my $dimension_units = AM->retrieve_units(\%dbup_myconfig, $form,
202 my $dimension_ddbox = AM->unit_select_data($dimension_units);
204 my $service_units = AM->retrieve_units(\%dbup_myconfig, $form, "service");
205 my $service_ddbox = AM->unit_select_data($service_units);
207 print($form->parse_html_template("dbupgrade/units_set_default",
208 { "DIMENSION_DDBOX" => $dimension_ddbox,
209 "SERVICE_DDBOX" => $service_ddbox }));
213 print($form->parse_html_template("dbupgrade/units_set_default_done"));
218 sub update_units_set_default {
219 my $form = $main::form;
221 foreach my $table (qw(parts invoice orderitems rmaitems)) {
222 my $base_query = "UPDATE $table SET unit = " .
223 $dbh->quote($form->{"default_service_unit"}) . " " .
224 "WHERE ((unit ISNULL) OR (unit = '')) AND ";
227 if ($table eq "parts") {
228 $query = "UPDATE $table SET unit = " .
229 $dbh->quote($form->{"default_dimension_unit"}) . " " .
230 "WHERE ((unit ISNULL) OR (unit = '')) AND (inventory_accno_id > 0)";
232 $query = "UPDATE $table SET unit = " .
233 $dbh->quote($form->{"default_dimension_unit"}) . " " .
234 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
235 "parts_id IN (SELECT id FROM parts WHERE (inventory_accno_id > 0))";
238 $dbh->do($query) || mydberror($query);
240 if ($table eq "parts") {
241 $query = "UPDATE $table SET unit = " .
242 $dbh->quote($form->{"default_service_unit"}) . " " .
243 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
244 "(inventory_accno_id ISNULL) OR (inventory_accno_id = 0)";
246 $query = "UPDATE $table SET unit = " .
247 $dbh->quote($form->{"default_service_unit"}) . " " .
248 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
249 "parts_id IN (SELECT id FROM parts " .
250 "WHERE (inventory_accno_id ISNULL) OR (inventory_accno_id = 0))";
253 $dbh->do($query) || mydberror($query);
258 my $form = $main::form;
262 print($form->parse_html_template("dbupgrade/units_header"));
264 if ($form->{"action2"} eq "add_unit") {
265 $res = update_units_add_unit();
266 return $res if ($res);
268 } elsif ($form->{"action2"} eq "assign_units") {
269 update_units_assign_units();
271 } elsif ($form->{"action2"} eq "set_default") {
272 update_units_set_default();
276 update_units_assign_known();
278 $res = update_units_steps_1_2();
279 return $res if ($res);
281 return update_units_step_3();