3 die("This script cannot be run from the command line.") unless ($main::form);
8 map({ $dbup_myconfig{$_} = $main::form->{$_}; }
9 qw(dbname dbuser dbpasswd dbhost dbport dbconnect));
12 my ($dbup_locale, $msg) = @_;
13 die($dbup_locale->text("Database update error:") .
14 "<br>$msg<br>" . $DBI::errstr);
20 print($main::form->parse_html_template("dbupgrade/units_error",
21 { "message" => $msg }));
25 sub update_units_add_unit {
26 my ($dbup_locale, $dbh) = @_;
28 my $form = $main::form;
30 return 0 unless ($form->{"new_name"});
32 return myshowerror($dbup_locale->text("The name is missing."))
33 if ($form->{"new_name"} eq "");
34 my $units = AM->retrieve_units(\%dbup_myconfig, $form);
35 return myshowerror($dbup_locale->text("A unit with this name does already exist."))
36 if ($units->{$form->{"new_name"}});
37 $units = AM->retrieve_units(\%dbup_myconfig, $form, $form->{"unit_type"});
39 my ($base_unit, $factor);
40 if ($form->{"new_base_unit"}) {
41 return myshowerror($dbup_locale->text("The base unit does not exist."))
42 unless (defined($units->{$form->{"new_base_unit"}}));
44 return myshowerror($dbup_locale->text("The factor is missing."))
45 if ($form->{"new_factor"} eq "");
46 $factor = $form->parse_amount(\%dbup_myconfig, $form->{"new_factor"});
47 return myshowerror($dbup_locale->text("The factor is missing."))
49 $base_unit = $form->{"new_base_unit"};
52 my $query = "INSERT INTO units " .
53 "(name, base_unit, factor, type) " .
54 "VALUES (?, ?, ?, ?)";
55 $dbh->do($query, undef, $form->{"new_name"}, $base_unit, $factor,
56 $form->{"unit_type"}) ||
57 mydberror($dbup_locale, $query .
58 " ($form->{new_name}, $base_unit, $factor, $form->{unit_type})");
62 $form->{"saved_message"} = $dbup_locale->text("The unit has been saved.");
67 sub update_units_assign_units {
68 my ($dbup_locale, $dbh) = @_;
70 my ($query, $sth, @values);
72 my $form = $main::form;
74 foreach my $table (qw(parts invoice orderitems rmaitems)) {
75 $query = "UPDATE $table SET unit = ? WHERE lower(unit) = ?";
76 $sth = $dbh->prepare($query);
78 for (my $i = 1; $i <= $form->{"rowcount"}; $i++) {
79 next unless ($form->{"new_unit_$i"} && $form->{"old_unit_$i"});
80 @values = ($form->{"new_unit_$i"}, lc($form->{"old_unit_$i"}));
81 $sth->execute(@values) ||
82 mydberror($dbup_locale, $query . " (" . join(", ", @values) . ")");
91 sub update_units_assign_known {
92 my ($dbup_locale, $dbh) = @_;
94 my $form = $main::form;
96 my %unit_name_mapping = (
105 foreach my $k (keys(%unit_name_mapping)) {
106 $form->{"old_unit_$i"} = $k;
107 $form->{"new_unit_$i"} = $unit_name_mapping{$k};
110 $form->{"rowcount"} = scalar(keys(%unit_name_mapping));
112 update_units_assign_units($dbup_locale, $dbh);
115 sub update_units_steps_1_2 {
116 my ($dbup_locale, $dbh) = @_;
118 my (%unknown_dimension_units, %unknown_service_units);
120 my $form = $main::form;
122 foreach my $table (qw(parts invoice orderitems rmaitems)) {
123 my ($query, $sth, $ref);
125 if ($table eq "parts") {
126 $query = "SELECT unit, inventory_accno_id FROM parts " .
127 "WHERE NOT ((unit = '') OR unit ISNULL OR " .
128 " unit IN (SELECT name FROM units))";
131 $query = "SELECT t.unit, p.inventory_accno_id " .
133 "LEFT JOIN parts p ON p.id = t.parts_id " .
134 "WHERE NOT ((t.unit = '') OR t.unit ISNULL OR " .
135 " t.unit IN (SELECT name FROM units))";
137 $sth = $dbh->prepare($query);
138 $sth->execute() || mydberror($dbup_locale, $query);
140 while ($ref = $sth->fetchrow_hashref()) {
141 if ($ref->{"inventory_accno_id"}) {
142 $unknown_dimension_units{$ref->{"unit"}} = 1;
145 $unknown_service_units{$ref->{"unit"}} = 1;
152 if (scalar(keys(%unknown_dimension_units)) != 0) {
153 my $units = AM->retrieve_units(\%dbup_myconfig, $form, "dimension");
154 my $ddbox = AM->unit_select_data($units, undef, 1);
157 map({ push(@unknown_parts, { "name" => $_, "NEW_UNITS" => $ddbox }); }
158 sort({ lc($a) cmp lc($b) } keys(%unknown_dimension_units)));
160 print($form->parse_html_template("dbupgrade/units_parts",
161 { "NEW_BASE_UNIT_DDBOX" => $ddbox,
162 "UNKNOWN_PART_UNITS" => \@unknown_parts,
168 print($form->parse_html_template("dbupgrade/units_parts_done"));
171 if (scalar(keys(%unknown_service_units)) != 0) {
172 my $units = AM->retrieve_units(\%dbup_myconfig, $form, "service");
173 my $ddbox = AM->unit_select_data($units, undef, 1);
175 my @unknown_services;
176 map({ push(@unknown_services, { "name" => $_, "NEW_UNITS" => $ddbox }); }
177 sort({ lc($a) cmp lc($b) } keys(%unknown_service_units)));
179 print($form->parse_html_template("dbupgrade/units_services",
180 { "NEW_BASE_UNIT_DDBOX" => $ddbox,
181 "UNKNOWN_PART_UNITS" => \@unknown_services,
187 print($form->parse_html_template("dbupgrade/units_services_done"));
193 sub update_units_step_3 {
194 my ($dbup_locale, $dbh) = @_;
196 my $form = $main::form;
198 my $query = "SELECT ";
199 foreach my $table (qw(parts invoice orderitems rmaitems)) {
200 $query .= "(SELECT COUNT(*) FROM $table " .
201 "WHERE (unit ISNULL) OR (unit = '')) +";
203 substr($query, -1, 1) = "AS has_unassigned";
204 my ($has_unassigned) = $dbh->selectrow_array($query);
206 if ($has_unassigned) {
207 my $dimension_units = AM->retrieve_units(\%dbup_myconfig, $form,
209 my $dimension_ddbox = AM->unit_select_data($dimension_units);
211 my $service_units = AM->retrieve_units(\%dbup_myconfig, $form, "service");
212 my $service_ddbox = AM->unit_select_data($service_units);
214 print($form->parse_html_template("dbupgrade/units_set_default",
215 { "DIMENSION_DDBOX" => $dimension_ddbox,
216 "SERVICE_DDBOX" => $service_ddbox }));
220 print($form->parse_html_template("dbupgrade/units_set_default_done"));
225 sub update_units_set_default {
226 my ($dbup_locale, $dbh) = @_;
228 my $form = $main::form;
230 foreach my $table (qw(parts invoice orderitems rmaitems)) {
231 my $base_query = "UPDATE $table SET unit = " .
232 $dbh->quote($form->{"default_service_unit"}) . " " .
233 "WHERE ((unit ISNULL) OR (unit = '')) AND ";
236 if ($table eq "parts") {
237 $query = "UPDATE $table SET unit = " .
238 $dbh->quote($form->{"default_dimension_unit"}) . " " .
239 "WHERE ((unit ISNULL) OR (unit = '')) AND (inventory_accno_id > 0)";
241 $query = "UPDATE $table SET unit = " .
242 $dbh->quote($form->{"default_dimension_unit"}) . " " .
243 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
244 "parts_id IN (SELECT id FROM parts WHERE (inventory_accno_id > 0))";
247 $dbh->do($query) || mydberror($dbup_locale, $query);
249 if ($table eq "parts") {
250 $query = "UPDATE $table SET unit = " .
251 $dbh->quote($form->{"default_service_unit"}) . " " .
252 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
253 "(inventory_accno_id ISNULL) OR (inventory_accno_id = 0)";
255 $query = "UPDATE $table SET unit = " .
256 $dbh->quote($form->{"default_service_unit"}) . " " .
257 "WHERE ((unit ISNULL) OR (unit = '')) AND " .
258 "parts_id IN (SELECT id FROM parts " .
259 "WHERE (inventory_accno_id ISNULL) OR (inventory_accno_id = 0))";
262 $dbh->do($query) || mydberror($dbup_locale, $query);
269 my $form = $main::form;
273 my $dbup_locale = Locale->new($main::language, "dbupgrade");
275 print($form->parse_html_template("dbupgrade/units_header"));
277 if ($form->{"action2"} eq "add_unit") {
278 $res = update_units_add_unit($dbup_locale, $dbh);
279 return $res if ($res);
281 } elsif ($form->{"action2"} eq "assign_units") {
282 update_units_assign_units($dbup_locale, $dbh);
284 } elsif ($form->{"action2"} eq "set_default") {
285 update_units_set_default($dbup_locale, $dbh);
289 update_units_assign_known($dbup_locale, $dbh);
291 $res = update_units_steps_1_2($dbup_locale, $dbh);
292 return $res if ($res);
294 return update_units_step_3($dbup_locale, $dbh);