/ - Diff - projekt kivitendo - wissen

« Zurück | Weiter »

Revision 9d679693

Von Moritz Bunkus vor fast 18 Jahren hinzugefügt

ID 9d679693eeb06baf737355f5c07ea7abf33e7dbb
Vorgänger e07e9534
Nachfolger 0ec7b690

Verhinderung von SQL injection durch Verwendung von parametrisierten Abfragen. Entfernen der Verwaltungsfunktionen für "SIC".

       if ($form->{id} && $form->{orphaned}) {
         $query = qq|UPDATE chart SET
                     accno = ?, description = ?, charttype = ?,
     		gifi_accno = ?, category = ?, link = ?,
                     gifi_accno = ?, category = ?, link = ?,
                     taxkey_id = ?,
                     pos_ustva = ?, pos_bwa   = ?, pos_bilanz = ?,
                     pos_eur = ?, new_chart_id = ?, valid_from = ?
     		WHERE id = ?|;
                     WHERE id = ?|;
         @values = ($form->{accno}, $form->{description}, $form->{charttype},
     	       $form->{gifi_accno}, $form->{category}, $form->{link},
     	       conv_i($taxkey),
     	       conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}),
     	       conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),
     	       conv_i($form->{new_chart_id}),
     	       conv_date($form->{valid_from}),
     	       $form->{id});
                    $form->{gifi_accno}, $form->{category}, $form->{link},
                    conv_i($taxkey),
                    conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}),
                    conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),
                    conv_i($form->{new_chart_id}),
                    conv_date($form->{valid_from}),
                    $form->{id});
       } elsif ($form->{id} && !$form->{new_chart_valid}) {
         $query = qq|UPDATE chart SET new_chart_id = ?, valid_from = ?
     		WHERE id = ?|;
                     WHERE id = ?|;
         @values = (conv_i($form->{new_chart_id}), conv_date($form->{valid_from}),
     	       $form->{id});
                    $form->{id});
       } else {
         $query = qq|INSERT INTO chart
                     (accno, description, charttype,
-...
                      new_chart_id, valid_from)
                     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
         @values = ($form->{accno}, $form->{description}, $form->{charttype},
     	       $form->{gifi_accno}, $form->{category}, $form->{link},
     	       conv_i($taxkey),
     	       conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}),
     	       conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),
     	       conv_i($form->{new_chart_id}),
     	       conv_date($form->{valid_from}));
                    $form->{gifi_accno}, $form->{category}, $form->{link},
                    conv_i($taxkey),
                    conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}),
                    conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),
                    conv_i($form->{new_chart_id}),
                    conv_date($form->{valid_from}));
+      }
       do_query($form, $dbh, $query, @values);
-...
           qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | .
           qq|VALUES ((SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?)|;
         do_query($form, $dbh, $query,
     	     $form->{accno}, conv_i($tax_id), conv_i($taxkey),
     	     conv_i($form->{pos_ustva}), conv_date($startdate));
                  $form->{accno}, conv_i($tax_id), conv_i($taxkey),
                  conv_i($form->{pos_ustva}), conv_date($startdate));
       } else {
         $query = qq|DELETE FROM taxkeys WHERE chart_id = ? AND tax_id = ?|;
-...
           qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | .
           qq|VALUES (?, ?, ?, ?, ?)|;
         do_query($form, $dbh, $query,
     	     $form->{id}, conv_i($tax_id), conv_i($taxkey),
     	     conv_i($form->{pos_ustva}), conv_date($startdate));
                  $form->{id}, conv_i($tax_id), conv_i($taxkey),
                  conv_i($form->{pos_ustva}), conv_date($startdate));
+      }
       # commit
-...
       my $query = qq|SELECT d.id, d.description, d.role
                      FROM department d
     		 ORDER BY 2|;
                      ORDER BY 2|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       $form->{ALL} = [];
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{ALL} }, $ref;
+      }
-...
       my $query = qq|SELECT d.description, d.role
                      FROM department d
     	         WHERE d.id = $form->{id}|;
                      WHERE d.id = ?|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
       my $ref = $sth->fetchrow_hashref(NAME_lc);
-...
       # see if it is in use
       $query = qq|SELECT count(*) FROM dpt_trans d
                   WHERE d.department_id = $form->{id}|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
                   WHERE d.department_id = ?|;
       ($form->{orphaned}) = selectrow_query($form, $dbh, $query, $form->{id});
       ($form->{orphaned}) = $sth->fetchrow_array;
       $form->{orphaned} = !$form->{orphaned};
       $sth->finish;
-...
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       $form->{description} =~ s/\'/\'\'/g;
       my @values = ($form->{description}, $form->{role});
       if ($form->{id}) {
         $query = qq|UPDATE department SET
     		description = '$form->{description}',
     		role = '$form->{role}'
     		WHERE id = $form->{id}|;
                     description = ?, role = ?
                     WHERE id = ?|;
         push(@values, $form->{id});
       } else {
         $query = qq|INSERT INTO department
                     (description, role)
                     VALUES ('$form->{description}', '$form->{role}')|;
                     VALUES (?, ?)|;
+      }
       $dbh->do($query) || $form->dberror($query);
       do_query($form, $dbh, $query, @values);
       $dbh->disconnect;
-...
       my $dbh = $form->dbconnect($myconfig);
       $query = qq|DELETE FROM department
     	      WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
                   WHERE id = ?|;
       do_query($form, $dbh, $query, $form->{id});
       $dbh->disconnect;
-...
       my $query = qq|SELECT id, lead
                      FROM leads
     		 ORDER BY 2|;
                      ORDER BY 2|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       $form->{ALL};
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{ALL} }, $ref;
+      }
-...
       my $dbh = $form->dbconnect($myconfig);
       my $query =
         qq|SELECT l.id, l.lead
                      FROM leads l
     	         WHERE l.id = $form->{id}|;
         qq|SELECT l.id, l.lead | .
         qq|FROM leads l | .
         qq|WHERE l.id = ?|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
       my $ref = $sth->fetchrow_hashref(NAME_lc);
-...
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       $form->{lead} =~ s/\'/\'\'/g;
       my @values = ($form->{description});
       # id is the old record
       if ($form->{id}) {
         $query = qq|UPDATE leads SET
     		lead = '$form->{description}'
     		WHERE id = $form->{id}|;
                     lead = ?
                     WHERE id = ?|;
         puhs(@values, $form->{id});
       } else {
         $query = qq|INSERT INTO leads
                     (lead)
                     VALUES ('$form->{description}')|;
                     VALUES (?)|;
+      }
       $dbh->do($query) || $form->dberror($query);
       do_query($form, $dbh, $query, @values);
       $dbh->disconnect;
-...
       my $dbh = $form->dbconnect($myconfig);
       $query = qq|DELETE FROM leads
     	      WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
                   WHERE id = ?|;
       do_query($form, $dbh, $query, $form->{id});
       $dbh->disconnect;
-...
       my $query = qq|SELECT id, description, discount, customernumberinit, salesman
                      FROM business
     		 ORDER BY 2|;
                      ORDER BY 2|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       $form->{ALL};
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{ALL} }, $ref;
+      }
-...
       my $query =
         qq|SELECT b.description, b.discount, b.customernumberinit, b.salesman
                      FROM business b
     	         WHERE b.id = $form->{id}|;
            FROM business b
            WHERE b.id = ?|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
       my $ref = $sth->fetchrow_hashref(NAME_lc);
-...
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       $form->{description} =~ s/\'/\'\'/g;
       $form->{discount} /= 100;
       $form->{salesman} *= 1;
       my @values = ($form->{description}, $form->{discount},
                     $form->{customernumberinit}, $form->{salesman} ? 't' : 'f');
       # id is the old record
       if ($form->{id}) {
         $query = qq|UPDATE business SET
     		description = '$form->{description}',
     		discount = $form->{discount},
                     customernumberinit = '$form->{customernumberinit}',
                     salesman = '$form->{salesman}'
     		WHERE id = $form->{id}|;
                     description = ?,
                     discount = ?,
                     customernumberinit = ?,
                     salesman = ?
                     WHERE id = ?|;
         push(@values, $form->{id});
       } else {
         $query = qq|INSERT INTO business
                     (description, discount, customernumberinit, salesman)
                     VALUES ('$form->{description}', $form->{discount}, '$form->{customernumberinit}', '$form->{salesman}')|;
                     VALUES (?, ?, ?, ?)|;
+      }
       $dbh->do($query) || $form->dberror($query);
       do_query($form, $dbh, $query, @values);
       $dbh->disconnect;
-...
       my $dbh = $form->dbconnect($myconfig);
       $query = qq|DELETE FROM business
     	      WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
                   WHERE id = ?|;
       do_query($form, $dbh, $query, $form->{id});
       $dbh->disconnect;
-...
         "SELECT template_code, " .
         "  output_numberformat, output_dateformat, output_longdates " .
         "FROM language WHERE id = ?";
       my @res = $dbh->selectrow_array($query, undef, $id);
       my @res = selectrow_query($form, $dbh, $query, $id);
       $dbh->disconnect;
       $main::lxdebug->leave_sub();
-...
           "  output_numberformat, output_dateformat, output_longdates" .
           ") VALUES (?, ?, ?, ?, ?, ?)";
+      }
       $dbh->do($query, undef, @values) ||
         $form->dberror($query . " (" . join(", ", @values) . ")");
       do_query($form, $dbh, $query, @values);
       $dbh->disconnect;
-...
           qq|SELECT count(id) = 0 AS orphaned
              FROM parts
              WHERE buchungsgruppen_id = ?|;
         ($form->{orphaned}) = $dbh->selectrow_array($query, undef, $form->{id});
         ($form->{orphaned}) = selectrow_arra($query, undef, $form->{id});
         $form->dberror($query . " ($form->{id})") if ($dbh->err);
+      }
-...
       my $query = qq|SELECT id, printer_description, template_code, printer_command
                      FROM printers
     		 ORDER BY 2|;
                      ORDER BY 2|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
-...
       my $query =
         qq|SELECT p.printer_description, p.template_code, p.printer_command
                      FROM printers p
     	         WHERE p.id = $form->{id}|;
            FROM printers p
            WHERE p.id = ?|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
       my $ref = $sth->fetchrow_hashref(NAME_lc);
-...
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       $form->{printer_description} =~ s/\'/\'\'/g;
       $form->{printer_command} =~ s/\'/\'\'/g;
       $form->{template_code} =~ s/\'/\'\'/g;
       my @values = ($form->{printer_description},
                     $form->{template_code},
                     $form->{printer_command});
       # id is the old record
       if ($form->{id}) {
         $query = qq|UPDATE printers SET
     		printer_description = '$form->{printer_description}',
     		template_code = '$form->{template_code}',
     		printer_command = '$form->{printer_command}'
     		WHERE id = $form->{id}|;
                     printer_description = ?, template_code = ?, printer_command = ?
                     WHERE id = ?|;
         push(@values, $form->{id});
       } else {
         $query = qq|INSERT INTO printers
                     (printer_description, template_code, printer_command)
                     VALUES ('$form->{printer_description}', '$form->{template_code}', '$form->{printer_command}')|;
                     VALUES (?, ?, ?)|;
+      }
       $dbh->do($query) || $form->dberror($query);
       do_query($form, $dbh, $query, @values);
       $dbh->disconnect;
-...
       my $dbh = $form->dbconnect($myconfig);
       $query = qq|DELETE FROM printers
     	      WHERE id = $form->{id}|;
       $dbh->do($query) || $form->dberror($query);
                   WHERE id = ?|;
       do_query($form, $dbh, $query, $form->{id});
       $dbh->disconnect;
-...
         qq|WHERE t.payment_terms_id = ? | .
         qq|UNION | .
         qq|SELECT l.id AS language_id, NULL AS description_long, | .
         qq|l.description AS language | .
         qq|  l.description AS language | .
         qq|FROM language l|;
       $sth = $dbh->prepare($query);
       $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})");
-...
       $main::lxdebug->leave_sub();
+    }
     sub sic {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       my $query = qq|SELECT code, sictype, description
                      FROM sic
     		 ORDER BY code|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
         push @{ $form->{ALL} }, $ref;
+      }
       $sth->finish;
       $dbh->disconnect;
       $main::lxdebug->leave_sub();
+    }
     sub get_sic {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       my $query = qq|SELECT s.code, s.sictype, s.description
                      FROM sic s
     	         WHERE s.code = '$form->{code}'|;
       my $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
       my $ref = $sth->fetchrow_hashref(NAME_lc);
       map { $form->{$_} = $ref->{$_} } keys %$ref;
       $sth->finish;
       $dbh->disconnect;
       $main::lxdebug->leave_sub();
+    }
     sub save_sic {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       $form->{code}        =~ s/\'/\'\'/g;
       $form->{description} =~ s/\'/\'\'/g;
       # if there is an id
       if ($form->{id}) {
         $query = qq|UPDATE sic SET
                     code = '$form->{code}',
     		sictype = '$form->{sictype}',
     		description = '$form->{description}'
     		WHERE code = '$form->{id}'|;
       } else {
         $query = qq|INSERT INTO sic
                     (code, sictype, description)
                     VALUES ('$form->{code}', '$form->{sictype}', '$form->{description}')|;
+      }
       $dbh->do($query) || $form->dberror($query);
       $dbh->disconnect;
       $main::lxdebug->leave_sub();
+    }
     sub delete_sic {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form) = @_;
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       $query = qq|DELETE FROM sic
     	      WHERE code = '$form->{code}'|;
       $dbh->do($query) || $form->dberror($query);
       $dbh->disconnect;
       $main::lxdebug->leave_sub();
+    }
     sub load_template {
       $main::lxdebug->enter_sub();
-...
       # these defaults are database wide
       # user specific variables are in myconfig
       # save defaults
       my $query = qq|UPDATE defaults SET
                      inventory_accno_id =
     		     (SELECT c.id FROM chart c
     		                WHERE c.accno = '$form->{inventory_accno}'),
                      income_accno_id =
     		     (SELECT c.id FROM chart c
     		                WHERE c.accno = '$form->{income_accno}'),
     	         expense_accno_id =
     		     (SELECT c.id FROM chart c
     		                WHERE c.accno = '$form->{expense_accno}'),
     	         fxgain_accno_id =
     		     (SELECT c.id FROM chart c
     		                WHERE c.accno = '$form->{fxgain_accno}'),
     	         fxloss_accno_id =
     		     (SELECT c.id FROM chart c
     		                WHERE c.accno = '$form->{fxloss_accno}'),
     	         invnumber = '$form->{invnumber}',
                      cnnumber  = '$form->{cnnumber}',
     	         sonumber = '$form->{sonumber}',
     	         ponumber = '$form->{ponumber}',
     		 sqnumber = '$form->{sqnumber}',
     		 rfqnumber = '$form->{rfqnumber}',
                      customernumber = '$form->{customernumber}',
     		 vendornumber = '$form->{vendornumber}',
                      articlenumber = '$form->{articlenumber}',
                      servicenumber = '$form->{servicenumber}',
                      yearend = '$form->{yearend}',
     		 curr = '$form->{curr}',
     		 businessnumber = '$form->{businessnumber}'
     		|;
       $dbh->do($query) || $form->dberror($query);
       my $query =
         qq|UPDATE defaults SET | .
         qq|inventory_accno_id = (SELECT c.id FROM chart c WHERE c.accno = ?), | .
         qq|income_accno_id = (SELECT c.id FROM chart c WHERE c.accno = ?), | .
         qq|expense_accno_id = (SELECT c.id FROM chart c WHERE c.accno = ?), | .
         qq|fxgain_accno_id = (SELECT c.id FROM chart c WHERE c.accno = ?), | .
         qq|fxloss_accno_id = (SELECT c.id FROM chart c WHERE c.accno = ?), | .
         qq|invnumber = ?, | .
         qq|cnnumber  = ?, | .
         qq|sonumber = ?, | .
         qq|ponumber = ?, | .
         qq|sqnumber = ?, | .
         qq|rfqnumber = ?, | .
         qq|customernumber = ?, | .
         qq|vendornumber = ?, | .
         qq|articlenumber = ?, | .
         qq|servicenumber = ?, | .
         qq|yearend = ?, | .
         qq|curr = ?, | .
         qq|businessnumber = ?|;
       my @values = ($form->{inventory_accno}, $form->{income_accno},
                     $form->{expense_accno},
                     $form->{fxgain_accno}, $form->{fxloss_accno},
                     $form->{invnumber}, $form->{cnnumber},
                     $form->{sonumber}, $form->{ponumber},
                     $form->{sqnumber}, $form->{rfqnumber},
                     $form->{customernumber}, $form->{vendornumber},
                     $form->{articlenumber}, $form->{servicenumber},
                     $form->{yearend}, $form->{curr},
                     $form->{businessnumber});
       do_query($form, $dbh, $query, @values);
       # update name
       my $name = $form->{name};
       $name =~ s/\'/\'\'/g;
       $query = qq|UPDATE employee
                   SET name = '$name'
     	      WHERE login = '$form->{login}'|;
       $dbh->do($query) || $form->dberror($query);
     #   foreach my $item (split(/ /, $form->{taxaccounts})) {
     #     $query = qq|UPDATE tax
     # 		SET rate = | . ($form->{$item} / 100) . qq|,
     # 		taxnumber = '$form->{"taxnumber_$item"}'
     # 		WHERE chart_id = $item|;
     #     $dbh->do($query) || $form->dberror($query);
     #   }
                   SET name = ?
                   WHERE login = ?|;
       do_query($form, $dbh, $query, $form->{name}, $form->{login});
       my $rc = $dbh->commit;
       $dbh->disconnect;
-...
       $query = qq|SELECT c.id, c.accno, c.description
                   FROM chart c
     	      WHERE c.category = 'I'
     	      AND c.charttype = 'A'
                   WHERE c.category = 'I'
                   AND c.charttype = 'A'
                   ORDER BY c.accno|;
       $sth = $dbh->prepare($query);
       $sth->execute || $self->dberror($query);
-...
       $query = qq|SELECT c.id, c.accno, c.description
                   FROM chart c
     	      WHERE c.category = 'E'
     	      AND c.charttype = 'A'
                   WHERE c.category = 'E'
                   AND c.charttype = 'A'
                   ORDER BY c.accno|;
       $sth = $dbh->prepare($query);
       $sth->execute || $self->dberror($query);
-...
       $query = qq|SELECT c.id, c.accno, c.description,
                   t.rate * 100 AS rate, t.taxnumber
                   FROM chart c, tax t
     	      WHERE c.id = t.chart_id|;
                   WHERE c.id = t.chart_id|;
       $sth = $dbh->prepare($query);
       $sth->execute || $form->dberror($query);
-...
       $main::lxdebug->leave_sub();
+    }
     sub backup {
       $main::lxdebug->enter_sub();
       my ($self, $myconfig, $form, $userspath) = @_;
       my $mail;
       my $err;
       my $boundary = time;
       my $tmpfile  =
         "$userspath/$boundary.$myconfig->{dbname}-$form->{dbversion}.sql";
       my $out = $form->{OUT};
       $form->{OUT} = ">$tmpfile";
       if ($form->{media} eq 'email') {
         use SL::Mailer;
         $mail = new Mailer;
         $mail->{to}      = qq|"$myconfig->{name}" <$myconfig->{email}>|;
         $mail->{from}    = qq|"$myconfig->{name}" <$myconfig->{email}>|;
         $mail->{subject} =
           "Lx-Office Backup / $myconfig->{dbname}-$form->{dbversion}.sql";
         @{ $mail->{attachments} } = ($tmpfile);
         $mail->{version} = $form->{version};
         $mail->{fileid}  = "$boundary.";
         $myconfig->{signature} =~ s/\\n/\r\n/g;
         $mail->{message} = "--\n$myconfig->{signature}";
+      }
       open(OUT, "$form->{OUT}") or $form->error("$form->{OUT} : $!");
       # get sequences, functions and triggers
       open(FH, "sql/lx-office.sql") or $form->error("sql/lx-office.sql : $!");
       my @sequences = ();
       my @functions = ();
       my @triggers  = ();
       my @indices   = ();
       my %tablespecs;
       my $query = "";
       my @quote_chars;
       while (<FH>) {
         # Remove DOS and Unix style line endings.
         s/[\r\n]//g;
         # ignore comments or empty lines
         next if /^(--.*|\s+)$/;
         for (my $i = 0; $i < length($_); $i++) {
           my $char = substr($_, $i, 1);
           # Are we inside a string?
           if (@quote_chars) {
             if ($char eq $quote_chars[-1]) {
               pop(@quote_chars);
+            }
             $query .= $char;
           } else {
             if (($char eq "'") || ($char eq "\"")) {
               push(@quote_chars, $char);
             } elsif ($char eq ";") {
               # Query is complete. Check for triggers and functions.
               if ($query =~ /^create\s+function\s+\"?(\w+)\"?/i) {
                 push(@functions, $query);
               } elsif ($query =~ /^create\s+trigger\s+\"?(\w+)\"?/i) {
                 push(@triggers, $query);
               } elsif ($query =~ /^create\s+sequence\s+\"?(\w+)\"?/i) {
                 push(@sequences, $1);
               } elsif ($query =~ /^create\s+table\s+\"?(\w+)\"?/i) {
                 $tablespecs{$1} = $query;
               } elsif ($query =~ /^create\s+index\s+\"?(\w+)\"?/i) {
                 push(@indices, $query);
+              }
               $query = "";
               $char  = "";
+            }
             $query .= $char;
+          }
+        }
+      }
       close(FH);
       # connect to database
       my $dbh = $form->dbconnect($myconfig);
       # get all the tables
       my @tables = $dbh->tables('', '', 'customer', '', { noprefix => 0 });
       my $today = scalar localtime;
       $myconfig->{dbhost} = 'localhost' unless $myconfig->{dbhost};
       print OUT qq|-- Lx-Office Backup
     -- Dataset: $myconfig->{dbname}
     -- Version: $form->{dbversion}
     -- Host: $myconfig->{dbhost}
     -- Login: $form->{login}
     -- User: $myconfig->{name}
     -- Date: $today
     --
     -- set options
     $myconfig->{dboptions};
     --
     |;
       print OUT "-- DROP Sequences\n";
       my $item;
       foreach $item (@sequences) {
         print OUT qq|DROP SEQUENCE $item;\n|;
+      }
       print OUT "-- DROP Triggers\n";
       foreach $item (@triggers) {
         if ($item =~ /^create\s+trigger\s+\"?(\w+)\"?\s+.*on\s+\"?(\w+)\"?\s+/i) {
           print OUT qq|DROP TRIGGER "$1" ON "$2";\n|;
+        }
+      }
       print OUT "-- DROP Functions\n";
       foreach $item (@functions) {
         if ($item =~ /^create\s+function\s+\"?(\w+)\"?/i) {
           print OUT qq|DROP FUNCTION "$1" ();\n|;
+        }
+      }
       foreach $table (@tables) {
         if (!($table =~ /^sql_.*/)) {
           my $query = qq|SELECT * FROM $table|;
           my $sth = $dbh->prepare($query);
           $sth->execute || $form->dberror($query);
           $query = "INSERT INTO $table (";
           map { $query .= qq|$sth->{NAME}->[$_],| }
             (0 .. $sth->{NUM_OF_FIELDS} - 1);
           chop $query;
           $query .= ") VALUES";
           if ($tablespecs{$table}) {
             print(OUT "--\n");
             print(OUT "DROP TABLE $table;\n");
             print(OUT $tablespecs{$table}, ";\n");
           } else {
             print(OUT "--\n");
             print(OUT "DELETE FROM $table;\n");
+          }
           while (my @arr = $sth->fetchrow_array) {
             $fields = "(";
             foreach my $item (@arr) {
               if (defined $item) {
                 $item =~ s/\'/\'\'/g;
                 $fields .= qq|'$item',|;
               } else {
                 $fields .= 'NULL,';
+              }
+            }
             chop $fields;
             $fields .= ")";
             print OUT qq|$query $fields;\n|;
+          }
           $sth->finish;
+        }
+      }
       # create indices, sequences, functions and triggers
       print(OUT "-- CREATE Indices\n");
       map({ print(OUT "$_;\n"); } @indices);
       print OUT "-- CREATE Sequences\n";
       foreach $item (@sequences) {
         $query = qq|SELECT last_value FROM $item|;
         $sth   = $dbh->prepare($query);
         $sth->execute || $form->dberror($query);
         my ($id) = $sth->fetchrow_array;
         $sth->finish;
         print OUT qq|--
     CREATE SEQUENCE $item START $id;
     |;
+      }
       print OUT "-- CREATE Functions\n";
       # functions
       map { print(OUT $_, ";\n"); } @functions;
       print OUT "-- CREATE Triggers\n";
       # triggers
       map { print(OUT $_, ";\n"); } @triggers;
       close(OUT);
       $dbh->disconnect;
       # compress backup
       my @args = ("gzip", "$tmpfile");
       system(@args) == 0 or $form->error("$args[0] : $?");
       $tmpfile .= ".gz";
       if ($form->{media} eq 'email') {
         @{ $mail->{attachments} } = ($tmpfile);
         $err = $mail->send($out);
+      }
       if ($form->{media} eq 'file') {
         open(IN,  "$tmpfile") or $form->error("$tmpfile : $!");
         open(OUT, ">-")       or $form->error("STDOUT : $!");
         print OUT qq|Content-Type: application/x-tar-gzip;
     Content-Disposition: attachment; filename="$myconfig->{dbname}-$form->{dbversion}.sql.gz"
     |;
         while (<IN>) {
           print OUT $_;
+        }
         close(IN);
         close(OUT);
+      }
       unlink "$tmpfile";
       $main::lxdebug->leave_sub();
+    }
     sub closedto {
       $main::lxdebug->enter_sub();
-...
       my $dbh = $form->dbconnect($myconfig);
       my ($query, @values);
       if ($form->{revtrans}) {
         $query = qq|UPDATE defaults SET closedto = NULL, revtrans = '1'|;
         $query = qq|UPDATE defaults SET closedto = NULL,
     				    revtrans = '1'|;
       } elsif ($form->{closedto}) {
         $query = qq|UPDATE defaults SET closedto = ?, revtrans = '0'|;
         @values = (conv_date($form->{closedto}));
         $query = qq|UPDATE defaults SET closedto = '$form->{closedto}',
     				      revtrans = '0'|;
       } else {
         $query = qq|UPDATE defaults SET closedto = NULL,
     				      revtrans = '0'|;
         $query = qq|UPDATE defaults SET closedto = NULL, revtrans = '0'|;
+      }
       # set close in defaults
       $dbh->do($query) || $form->dberror($query);
       do_query($form, $dbh, $query, @values);
       $dbh->disconnect;
-...
       my $dbh = $form->dbconnect($myconfig);
       map({ $_->{"in_use"} = 0; } values(%{$units}));
       foreach my $unit (values(%{$units})) {
         my $base_unit = $unit->{"original_base_unit"};
         while ($base_unit) {
           $units->{$base_unit}->{"in_use"} = 1;
           $units->{$base_unit}->{"DEPENDING_UNITS"} = [] unless ($units->{$base_unit}->{"DEPENDING_UNITS"});
           push(@{$units->{$base_unit}->{"DEPENDING_UNITS"}}, $unit->{"name"});
           $base_unit = $units->{$base_unit}->{"original_base_unit"};
-...
+      }
       foreach my $unit (values(%{$units})) {
         $unit->{"in_use"} = 0;
         map({ $_ = $dbh->quote($_); } @{$unit->{"DEPENDING_UNITS"}});
         foreach my $table (qw(parts invoice orderitems)) {
-...
           if (0 == scalar(@{$unit->{"DEPENDING_UNITS"}})) {
             $query .= "= " . $dbh->quote($unit->{"name"});
           } else {
             $query .= "IN (" . $dbh->quote($unit->{"name"}) . "," . join(",", @{$unit->{"DEPENDING_UNITS"}}) . ")";
             $query .= "IN (" . $dbh->quote($unit->{"name"}) . "," .
               join(",", map({ $dbh->quote($_) } @{$unit->{"DEPENDING_UNITS"}})) . ")";
+          }
           my ($count) = $dbh->selectrow_array($query);

     |;
         $discount =
           $form->format_amount(\%myconfig, $ref->{discount} * 100, 1, "&nbsp");
           $form->format_amount(\%myconfig, $ref->{discount} * 100);
         $description =
           ($ref->{salesman})
           ? "<b>$ref->{description}</b>"
-...
       $lxdebug->enter_sub();
       $form->isblank("description", $locale->text('Description missing!'));
       $form->{discount} = $form->parse_amount(\%myconfig, $form->{discount}) / 100;
       AM->save_business(\%myconfig, \%$form);
       $form->redirect($locale->text('Business saved!'));
-...
       $lxdebug->leave_sub();
+    }
     sub add_sic {
       $lxdebug->enter_sub();
       $form->{title} = "Add";
       $form->{callback} =
         "$form->{script}?action=add_sic&path=$form->{path}&login=$form->{login}&password=$form->{password}"
         unless $form->{callback};
       &sic_header;
       &form_footer;
       $lxdebug->leave_sub();
+    }
     sub edit_sic {
       $lxdebug->enter_sub();
       $form->{title} = "Edit";
       AM->get_sic(\%myconfig, \%$form);
       &sic_header;
       $form->{orphaned} = 1;
       &form_footer;
       $lxdebug->leave_sub();
+    }
     sub list_sic {
       $lxdebug->enter_sub();
       AM->sic(\%myconfig, \%$form);
       $form->{callback} =
         "$form->{script}?action=list_sic&path=$form->{path}&login=$form->{login}&password=$form->{password}";
       $callback = $form->escape($form->{callback});
       $form->{title} = $locale->text('Standard Industrial Codes');
       @column_index = qw(code description);
       $column_header{code} =
         qq|<th class=listheading>| . $locale->text('Code') . qq|</th>|;
       $column_header{description} =
         qq|<th class=listheading>| . $locale->text('Description') . qq|</th>|;
       $form->header;
       print qq|
     <body>
     <table width=100%>
       <tr>
         <th class=listtop>$form->{title}</th>
       </tr>
       <tr height="5"></tr>
       <tr>
         <td>
           <table width=100%>
             <tr class=listheading>
     |;
       map { print "$column_header{$_}\n" } @column_index;
       print qq|
             </tr>
     |;
       foreach $ref (@{ $form->{ALL} }) {
         $i++;
         $i %= 2;
         if ($ref->{sictype} eq 'H') {
           print qq|
             <tr valign=top class=listheading>
     |;
           $column_data{code} =
             qq|<th><a href=$form->{script}?action=edit_sic&code=$ref->{code}&path=$form->{path}&login=$form->{login}&password=$form->{password}&callback=$callback>$ref->{code}</th>|;
           $column_data{description} = qq|<th>$ref->{description}</th>|;
         } else {
           print qq|
             <tr valign=top class=listrow$i>
     |;
           $column_data{code} =
             qq|<td><a href=$form->{script}?action=edit_sic&code=$ref->{code}&path=$form->{path}&login=$form->{login}&password=$form->{password}&callback=$callback>$ref->{code}</td>|;
           $column_data{description} = qq|<td>$ref->{description}</td>|;
+        }
         map { print "$column_data{$_}\n" } @column_index;
         print qq|
     	</tr>
     |;
+      }
       print qq|
           </table>
         </td>
       </tr>
       <tr>
       <td><hr size=3 noshade></td>
       </tr>
     </table>
     <br>
     <form method=post action=$form->{script}>
     <input name=callback type=hidden value="$form->{callback}">
     <input type=hidden name=type value=sic>
     <input type=hidden name=path value=$form->{path}>
     <input type=hidden name=login value=$form->{login}>
     <input type=hidden name=password value=$form->{password}>
     <input class=submit type=submit name=action value="|
         . $locale->text('Add') . qq|">
       </form>
       </body>
       </html>
     |;
       $lxdebug->leave_sub();
+    }
     sub sic_header {
       $lxdebug->enter_sub();
       $form->{title} = $locale->text("$form->{title} SIC");
       # $locale->text('Add SIC')
       # $locale->text('Edit SIC')
       $form->{code}        =~ s/\"/&quot;/g;
       $form->{description} =~ s/\"/&quot;/g;
       $checked = ($form->{sictype} eq 'H') ? "checked" : "";
       $form->header;
       print qq|
     <body>
     <form method=post action=$form->{script}>
     <input type=hidden name=type value=sic>
     <input type=hidden name=id value=$form->{code}>
     <table width=100%>
       <tr>
         <th class=listtop colspan=2>$form->{title}</th>
       </tr>
       <tr height="5"></tr>
       <tr>
         <th align=right>| . $locale->text('Code') . qq|</th>
         <td><input name=code size=10 value=$form->{code}></td>
       <tr>
       <tr>
         <td></td>
         <th align=left><input name=sictype type=checkbox style=checkbox value="H" $checked> |
         . $locale->text('Heading') . qq|</th>
       <tr>
       <tr>
         <th align=right>| . $locale->text('Description') . qq|</th>
         <td><input name=description size=60 value="$form->{description}"></td>
       </tr>
         <td colspan=2><hr size=3 noshade></td>
       </tr>
     </table>
     |;
       $lxdebug->leave_sub();
+    }
     sub save_sic {
       $lxdebug->enter_sub();
       $form->isblank("code",        $locale->text('Code missing!'));
       $form->isblank("description", $locale->text('Description missing!'));
       AM->save_sic(\%myconfig, \%$form);
       $form->redirect($locale->text('SIC saved!'));
       $lxdebug->leave_sub();
+    }
     sub delete_sic {
       $lxdebug->enter_sub();
       AM->delete_sic(\%myconfig, \%$form);
       $form->redirect($locale->text('SIC deleted!'));
       $lxdebug->leave_sub();
+    }
     sub display_stylesheet {
       $lxdebug->enter_sub();
-...
       $lxdebug->leave_sub();
+    }
     sub backup {
       $lxdebug->enter_sub();
       if ($form->{media} eq 'email') {
         $form->error($locale->text('No email address for') . " $myconfig{name}")
           unless ($myconfig{email});
         $form->{OUT} = "$sendmail";
+      }
       AM->backup(\%myconfig, \%$form, $userspath);
       if ($form->{media} eq 'email') {
         $form->redirect($locale->text('Backup sent to') . qq| $myconfig{email}|);
+      }
       $lxdebug->leave_sub();
+    }
     sub audit_control {
       $lxdebug->enter_sub();

       'Add Quotation'               => 'Angebot erfassen',
       'Add RFQ'                     => 'Neue Preisanfrage',
       'Add Request for Quotation'   => 'Anfrage erfassen',
       'Add SIC'                     => 'SIC erfassen',
       'Add Sales Invoice'           => 'Rechnung erfassen',
       'Add Sales Order'             => 'Auftrag erfassen',
       'Add Service'                 => 'Dienstleistung erfassen',
-...
       'BOM'                         => 'St?ckliste',
       'BWA'                         => 'BWA',
       'Back'                        => 'Zur&uuml;ck',
       'Backup sent to'              => 'Eine Sicherungskopie wurde gesandt an',
       'Balance'                     => 'Bilanz',
       'Balance Sheet'               => 'Bilanz',
       'Bank'                        => 'Bank',
-...
       'Close'                       => '?bernehmen',
       'Close Books up to'           => 'Die B?cher abschlie?en bis zum',
       'Closed'                      => 'Geschlossen',
       'Code'                        => 'kode',
       'Code missing!'               => 'kode fehlt!',
       'Collective Orders only work for orders from one customer!' => 'Sammelauftr?ge funktionieren nur f?r Auftr?ge von einem Kunden!',
       'Comment'                     => 'Kommentar',
       'Company'                     => 'Firma',
-...
       'Edit Purchase Order'         => 'Lieferantenaufrag bearbeiten',
       'Edit Quotation'              => 'Angebot bearbeiten',
       'Edit Request for Quotation'  => 'Anfrage bearbeiten',
       'Edit SIC'                    => 'SIC bearbeiten',
       'Edit Sales Invoice'          => 'Rechnung bearbeiten',
       'Edit Sales Order'            => 'Auftrag bearbeiten',
       'Edit Service'                => 'Dienstleistung bearbeiten',
-...
       'No Database Drivers available!' => 'Kein Datenbanktreiber verf?gbar!',
       'No Dataset selected!'        => 'Keine Datenbank ausgew?hlt!',
       'No Vendor was found matching the search parameters.' => 'Zu dem Suchbegriff wurde kein H?ndler gefunden',
       'No email address for'        => 'Keine eMailaddresse f?r',
       'No employee was found matching the search parameters.' => 'Es wurde kein Angestellter gefunden, auf den die Suchparameter zutreffen.',
       'No entries were found which had no unit assigned to them.' => 'Es wurden keine Eintr&auml;ge gefunden, denen keine Einheit zugeordnet war.',
       'No licenses were found that match the search criteria.' => 'Es wurden keine Lizenzen gefunden, auf die die Suchkriterien zutreffen.',
-...
       'Revenue'                     => 'Erl?skonto',
       'Revenue Account'             => 'Erl?skonto',
       'SIC'                         => 'SIC',
       'SIC deleted!'                => 'SIC gel?scht',
       'SIC saved!'                  => 'SIC gespeichert',
       'Sales Invoice'               => 'Rechnung',
       'Sales Invoices'              => 'Kundenrechnung',
       'Sales Order'                 => 'Kundenauftrag',
-...
       'Sold'                        => 'Verkauft',
       'Source'                      => 'Beleg',
       'Spoolfile'                   => 'Druckdatei',
       'Standard Industrial Codes'   => 'SIC',
       'Start Dunning Process'       => 'Mahnprozess starten',
       'Startdate'                   => 'G?ltig ab',
       'Statement'                   => 'Sammelrechnung',

       'Add Lead'                    => 'Kundenquelle erfassen',
       'Add Payment Terms'           => 'Zahlungskonditionen hinzuf?gen',
       'Add Printer'                 => 'Drucker hinzuf?gen',
       'Add SIC'                     => 'SIC erfassen',
       'Add and edit %s'             => '%s hinzuf&uuml;gen und bearbeiten',
       'Address'                     => 'Adresse',
       'Article Code'                => 'Artikelk?rzel',
-...
       'Aufwand EU o. UStId'         => 'Aufwand EU o. UStId',
       'Aufwand Inland'              => 'Aufwand Inland',
       'BWA'                         => 'BWA',
       'Backup sent to'              => 'Eine Sicherungskopie wurde gesandt an',
       'Bestandskonto'               => 'Bestandskonto',
       'Bilanz'                      => 'Bilanz',
       'Books are open'              => 'Die B?cher sind ge?ffnet.',
-...
       'Cannot save preferences!'    => 'Benutzereinstellungen k?nnen nicht gespeichert werden!',
       'Chart of Accounts'           => 'Konten?bersicht',
       'Close Books up to'           => 'Die B?cher abschlie?en bis zum',
       'Code'                        => 'kode',
       'Code missing!'               => 'kode fehlt!',
       'Company'                     => 'Firma',
       'Continue'                    => 'Weiter',
       'Cost Center'                 => 'Kostenstelle',
-...
       'Edit Payment Terms'          => 'Zahlungskonditionen bearbeiten',
       'Edit Preferences for'        => 'Benutzereinstellungen f?r',
       'Edit Printer'                => 'Drucker bearbeiten',
       'Edit SIC'                    => 'SIC bearbeiten',
       'Edit Template'               => 'Vorlage bearbeiten',
       'Enforce transaction reversal for all dates' => 'Gegenbuchungen f?r jeden Zeitraum aktualisieren',
       'Enter longdescription'       => 'Langtext eingeben',
-...
       'No'                          => 'Nein',
       'No Customer was found matching the search parameters.' => 'Zu dem Suchbegriff wurde kein Endkunde gefunden',
       'No Vendor was found matching the search parameters.' => 'Zu dem Suchbegriff wurde kein H?ndler gefunden',
       'No email address for'        => 'Keine eMailaddresse f?r',
       'No employee was found matching the search parameters.' => 'Es wurde kein Angestellter gefunden, auf den die Suchparameter zutreffen.',
       'No part was found matching the search parameters.' => 'Es wurde kein Artikel gefunden, auf den die Suchparameter zutreffen.',
       'No project was found matching the search parameters.' => 'Es wurde kein Projekt gefunden, auf das die Suchparameter zutreffen.',
-...
       'Receivables'                 => 'Forderungen',
       'Revenue'                     => 'Erl?skonto',
       'Revenue Account'             => 'Erl?skonto',
       'SIC deleted!'                => 'SIC gel?scht',
       'SIC saved!'                  => 'SIC gespeichert',
       'Salesman'                    => 'Vertreter',
       'Save'                        => 'Speichern',
       'Screen'                      => 'Bildschirm',
-...
       'Signature'                   => 'Unterschrift',
       'Skonto'                      => 'Skonto',
       'Skonto Terms'                => 'Zahlungsziel Skonto',
       'Standard Industrial Codes'   => 'SIC',
       'Steuersatz'                  => 'Steuersatz',
       'Stylesheet'                  => 'Stilvorlage',
       'Tax'                         => 'Steuer',
-...
       'add_lead'                    => 'add_lead',
       'add_payment'                 => 'add_payment',
       'add_printer'                 => 'add_printer',
       'add_sic'                     => 'add_sic',
       'add_unit'                    => 'add_unit',
       'audit_control'               => 'audit_control',
       'backup'                      => 'backup',
       'buchungsgruppe_header'       => 'buchungsgruppe_header',
       'build_std_url'               => 'build_std_url',
       'business_header'             => 'business_header',
-...
       'delete_lead'                 => 'delete_lead',
       'delete_payment'              => 'delete_payment',
       'delete_printer'              => 'delete_printer',
       'delete_sic'                  => 'delete_sic',
       'delivery_customer_selection' => 'delivery_customer_selection',
       'department_header'           => 'department_header',
       'display_form'                => 'display_form',
-...
       'edit_lead'                   => 'edit_lead',
       'edit_payment'                => 'edit_payment',
       'edit_printer'                => 'edit_printer',
       'edit_sic'                    => 'edit_sic',
       'edit_template'               => 'edit_template',
       'edit_units'                  => 'edit_units',
       'employee_selection_internal' => 'employee_selection_internal',
-...
       'list_lead'                   => 'list_lead',
       'list_payment'                => 'list_payment',
       'list_printer'                => 'list_printer',
       'list_sic'                    => 'list_sic',
       'part_selection_internal'     => 'part_selection_internal',
       'payment_header'              => 'payment_header',
       'printer_header'              => 'printer_header',
-...
       'save_payment'                => 'save_payment',
       'save_preferences'            => 'save_preferences',
       'save_printer'                => 'save_printer',
       'save_sic'                    => 'save_sic',
       'save_template'               => 'save_template',
       'save_unit'                   => 'save_unit',
       'select_employee'             => 'select_employee',
-...
       'select_part_internal'        => 'select_part_internal',
       'set_longdescription'         => 'set_longdescription',
       'set_unit_languages'          => 'set_unit_languages',
       'sic_header'                  => 'sic_header',
       'swap_buchungsgruppen'        => 'swap_buchungsgruppen',
       'swap_payment_terms'          => 'swap_payment_terms',
       'swap_units'                  => 'swap_units',

     [System--Import CSV--Parts]
     module=lxo-import/partsB.php
     #[System--SIC]
     #module=menu.pl
     #action=acc_menu
     #target=acc_menu
     #submenu=1
+    #
     #[System--SIC--Add SIC]
     #module=am.pl
     #action=add_sic
+    #
     #[System--SIC--List SIC]
     #module=am.pl
     #action=list_sic
     [System--HTML Templates]
     module=menu.pl

Auch abrufbar als: Unified diff

Projekt

Allgemein

Profil

projekt kivitendo

Revision 9d679693

Von Moritz Bunkus vor fast 18 Jahren hinzugefügt