3 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 }));
21 sub update_units_add_unit {
22 my $form = $main::form;
24 return 0 unless ($form->{"new_name"});
26 return myshowerror($dbup_locale->text("The name is missing."))
27 if ($form->{"new_name"} eq "");
28 my $units = AM->retrieve_units(\%dbup_myconfig, $form);
29 return myshowerror($dbup_locale->text("A unit with this name does already exist."))
30 if ($units->{$form->{"new_name"}});
31 $units = AM->retrieve_units(\%dbup_myconfig, $form, $form->{"unit_type"});
33 my ($base_unit, $factor);
34 if ($form->{"new_base_unit"}) {
35 return myshowerror($dbup_locale->text("The base unit does not exist."))
36 unless (defined($units->{$form->{"new_base_unit"}}));
38 return myshowerror($dbup_locale->text("The factor is missing."))
39 if ($form->{"new_factor"} eq "");
40 $factor = $form->parse_amount(\%dbup_myconfig, $form->{"new_factor"});
41 return myshowerror($dbup_locale->text("The factor is missing."))
43 $base_unit = $form->{"new_base_unit"};
46 my $query = "INSERT INTO units " .
47 "(name, base_unit, factor, type) " .
48 "VALUES (?, ?, ?, ?)";
49 $dbh->do($query, undef, $form->{"new_name"}, $base_unit, $factor,
50 $form->{"unit_type"}) ||
52 " ($form->{new_name}, $base_unit, $factor, $form->{unit_type})");
56 $form->{"saved_message"} = $dbup_locale->text("The unit has been saved.");
61 sub update_units_assign_units {
62 my ($query, $sth, @values);
64 my $form = $main::form;
66 foreach my $table (qw(parts invoice orderitems rmaitems)) {
67 $query = "UPDATE $table SET unit = ? WHERE lower(unit) = ?";
68 $sth = $dbh->prepare($query);
70 for (my $i = 1; $i <= $form->{"rowcount"}; $i++) {
71 next unless ($form->{"new_unit_$i"} && $form->{"old_unit_$i"});
72 @values = ($form->{"new_unit_$i"}, lc($form->{"old_unit_$i"}));
73 $sth->execute(@values) ||
74 mydberror($query . " (" . join(", ", @values) . ")");
83 sub update_units_assign_known {
84 my $form = $main::form;
86 my %unit_name_mapping = (
95 foreach my $k (keys(%unit_name_mapping)) {
96 $form->{"old_unit_$i"} = $k;
97 $form->{"new_unit_$i"} = $unit_name_mapping{$k};
100 $form->{"rowcount"} = scalar(keys(%unit_name_mapping));
102 update_units_assign_units();
105 sub update_units_steps_1_2 {
106 my (%unknown_dimension_units, %unknown_service_units);
108 my $form = $main::form;
110 foreach my $table (qw(parts invoice orderitems rmaitems)) {
111 my ($query, $sth, $ref);
113 if ($table eq "parts") {
114 $query = "SELECT unit, inventory_accno_id FROM parts " .
115 "WHERE NOT ((unit = '') OR unit ISNULL OR " .
116 " unit IN (SELECT name FROM units))";
119 $query = "SELECT t.unit, p.inventory_accno_id " .
121 "LEFT JOIN parts p ON p.id = t.parts_id " .
122 "WHERE NOT ((t.unit = '') OR t.unit ISNULL OR " .
123 " t.unit IN (SELECT name FROM units))";
125 $sth = $dbh->prepare($query);
126 $sth->execute() || mydberror($query);
128 while ($ref = $sth->fetchrow_hashref()) {
129 if ($ref->{"inventory_accno_id"}) {
130 $unknown_dimension_units{$ref->{"unit"}} = 1;
133 $unknown_service_units{$ref->{"unit"}} = 1;
140 if (scalar(keys(%unknown_dimension_units)) != 0) {
141 my $units = AM->retrieve_units(\%dbup_myconfig, $form, "dimension");
142 my $ddbox = AM->unit_select_data($units, undef, 1);
145 map({ push(@unknown_parts, { "name" => $_, "NEW_UNITS" => $ddbox }); }
146 sort({ lc($a) cmp lc($b) } keys(%unknown_dimension_units)));
148 print($form->parse_html_template("dbupgrade/units_parts",
149 { "NEW_BASE_UNIT_DDBOX" => $ddbox,
150 "UNKNOWN_PART_UNITS" => \@unknown_parts,
156 print($form->parse_html_template("dbupgrade/units_parts_done"));
159 if (scalar(keys(%unknown_service_units)) != 0) {
160 my $units = AM->retrieve_units(\%dbup_myconfig, $form, "service");
161 my $ddbox = AM->unit_select_data($units, undef, 1);
163 my @unknown_services;
164 map({ push(@unknown_services, { "name" => $_, "NEW_UNITS" => $ddbox }); }
165 sort({ lc($a) cmp lc($b) } keys(%unknown_service_units)));
167 print($form->parse_html_template("dbupgrade/units_services",
168 { "NEW_BASE_UNIT_DDBOX" => $ddbox,
169 "UNKNOWN_PART_UNITS" => \@unknown_services,
175 print($form->parse_html_template("dbupgrade/units_services_done"));
181 sub update_units_step_3 {
182 my $form = $main::form;
184 my $query = "SELECT ";
185 foreach my $table (qw(parts invoice orderitems rmaitems)) {
186 $query .= "(SELECT COUNT(*) FROM $table " .
187 "WHERE (unit ISNULL) OR (unit = '')) +";
189 substr($query, -1, 1) = "AS has_unassigned";
190 my ($has_unassigned) = $dbh->selectrow_array($query);
192 if ($has_unassigned) {
193 my $dimension_units = AM->retrieve_units(\%dbup_myconfig, $form,
195 my $dimension_ddbox = AM->unit_select_data($dimension_units);
197 my $service_units = AM->retrieve_units(\%dbup_myconfig, $form, "service");
198 my $service_ddbox = AM->unit_select_data($service_units);
200 print($form->parse_html_template("dbupgrade/units_set_default",
201 { "DIMENSION_DDBOX" => $dimension_ddbox,
202 "SERVICE_DDBOX" => $service_ddbox }));
206 print($form->parse_html_template("dbupgrade/units_set_default_done"));
211 sub update_units_set_default {
212 my $form = $main::form;
214 foreach my $table (qw(parts invoice orderitems rmaitems)) {
215 my $base_query = "UPDATE $table SET unit = " .
216 $dbh->quote($form->{"default_service_unit"}) . " " .
217 "WHERE ((unit ISNULL) OR (unit = '')) AND ";
220 if ($table eq "parts") {
221 $query = "UPDATE $table SET unit = " .
222 $dbh->quote($form->{"default_dimension_unit"}) . " " .
223 "WHERE ((unit ISNULL) OR (unit = '')) AND (inventory_accno_id > 0)";
225 $query = "UPDATE $table SET unit = " .
226 $dbh->quote($form->{"default_dimension_unit"}) . " " .
227 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
228 "parts_id IN (SELECT id FROM parts WHERE (inventory_accno_id > 0))";
231 $dbh->do($query) || mydberror($query);
233 if ($table eq "parts") {
234 $query = "UPDATE $table SET unit = " .
235 $dbh->quote($form->{"default_service_unit"}) . " " .
236 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
237 "(inventory_accno_id ISNULL) OR (inventory_accno_id = 0)";
239 $query = "UPDATE $table SET unit = " .
240 $dbh->quote($form->{"default_service_unit"}) . " " .
241 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
242 "parts_id IN (SELECT id FROM parts " .
243 "WHERE (inventory_accno_id ISNULL) OR (inventory_accno_id = 0))";
246 $dbh->do($query) || mydberror($query);
251 my $form = $main::form;
255 print($form->parse_html_template("dbupgrade/units_header"));
257 if ($form->{"action2"} eq "add_unit") {
258 $res = update_units_add_unit();
259 return $res if ($res);
261 } elsif ($form->{"action2"} eq "assign_units") {
262 update_units_assign_units();
264 } elsif ($form->{"action2"} eq "set_default") {
265 update_units_set_default();
269 update_units_assign_known();
271 $res = update_units_steps_1_2();
272 return $res if ($res);
274 return update_units_step_3();