- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query = qq|SELECT * FROM payment_terms WHERE id = ?|;
- my $sth = $dbh->prepare($query);
- $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})");
-
- my $ref = $sth->fetchrow_hashref(NAME_lc);
- map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish();
-
- $query =
- qq|SELECT t.language_id, t.description_long, l.description AS language | .
- qq|FROM translation_payment_terms t | .
- qq|LEFT JOIN language l ON t.language_id = l.id | .
- 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|FROM language l|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})");
-
- my %mapping;
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $mapping{ $ref->{"language_id"} } = $ref
- unless (defined($mapping{ $ref->{"language_id"} }));
- }
- $sth->finish;
-
- $form->{"TRANSLATION"} = [sort({ $a->{"language"} cmp $b->{"language"} }
- values(%mapping))];
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub save_payment {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- my $query;
-
- if (!$form->{id}) {
- $query = qq|SELECT nextval('id'), COALESCE(MAX(sortkey) + 1, 1) | .
- qq|FROM payment_terms|;
- my $sortkey;
- ($form->{id}, $sortkey) = selectrow_query($form, $dbh, $query);
-
- $query = qq|INSERT INTO payment_terms (id, sortkey) VALUES (?, ?)|;
- do_query($form, $dbh, $query, $form->{id}, $sortkey);
-
- } else {
- $query =
- qq|DELETE FROM translation_payment_terms | .
- qq|WHERE payment_terms_id = ?|;
- do_query($form, $dbh, $query, $form->{"id"});
- }
-
- $query = qq|UPDATE payment_terms SET
- description = ?, description_long = ?,
- ranking = ?,
- terms_netto = ?, terms_skonto = ?,
- percent_skonto = ?
- WHERE id = ?|;
- my @values = ($form->{description}, $form->{description_long},
- $form->{ranking} * 1,
- $form->{terms_netto} * 1, $form->{terms_skonto} * 1,
- $form->{percent_skonto} * 1,
- $form->{id});
- do_query($form, $dbh, $query, @values);
-
- $query = qq|SELECT id FROM language|;
- my @language_ids;
- my $sth = $dbh->prepare($query);
- $sth->execute() || $form->dberror($query);
-
- while (my ($id) = $sth->fetchrow_array()) {
- push(@language_ids, $id);
- }
- $sth->finish();
-
- $query =
- qq|INSERT INTO translation_payment_terms | .
- qq|(language_id, payment_terms_id, description_long) | .
- qq|VALUES (?, ?, ?)|;
- $sth = $dbh->prepare($query);
-
- foreach my $language_id (@language_ids) {
- do_statement($form, $sth, $query, $language_id, $form->{"id"},
- $form->{"description_long_${language_id}"});
- }
- $sth->finish();
-
- $dbh->commit();
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub delete_payment {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- my $query =
- qq|DELETE FROM translation_payment_terms WHERE payment_terms_id = ?|;
- do_query($form, $dbh, $query, $form->{"id"});
-
- $query = qq|DELETE FROM payment_terms WHERE id = ?|;
- do_query($form, $dbh, $query, $form->{"id"});
-
- $dbh->commit();
- $dbh->disconnect;
-
- $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();
-
- my ($self, $form) = @_;
-
- open(TEMPLATE, "$form->{file}") or $form->error("$form->{file} : $!");
-
- while (<TEMPLATE>) {
- $form->{body} .= $_;
- }
-
- close(TEMPLATE);
-
- $main::lxdebug->leave_sub();
-}
-
-sub save_template {
- $main::lxdebug->enter_sub();
-
- my ($self, $form) = @_;
-
- open(TEMPLATE, ">$form->{file}") or $form->error("$form->{file} : $!");
-
- # strip
- $form->{body} =~ s/\r\n/\n/g;
- print TEMPLATE $form->{body};
-
- close(TEMPLATE);
-
- $main::lxdebug->leave_sub();
-}
-
-sub save_preferences {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form, $memberfile, $userspath, $webdav) = @_;
-
- map { ($form->{$_}) = split(/--/, $form->{$_}) }
- qw(inventory_accno income_accno expense_accno fxgain_accno fxloss_accno);
-
- my @a;
- $form->{curr} =~ s/ //g;
- map { push(@a, uc pack "A3", $_) if $_ } split(/:/, $form->{curr});
- $form->{curr} = join ':', @a;
-
- # connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- # 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);
-
- # 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);
-# }
-
- my $rc = $dbh->commit;
- $dbh->disconnect;
-
- # save first currency in myconfig
- $form->{currency} = substr($form->{curr}, 0, 3);
-
- my $myconfig = new User "$memberfile", "$form->{login}";
-
- foreach my $item (keys %$form) {
- $myconfig->{$item} = $form->{$item};
- }
-
- $myconfig->save_member($memberfile, $userspath);
-
- if ($webdav) {
- @webdavdirs =
- qw(angebote bestellungen rechnungen anfragen lieferantenbestellungen einkaufsrechnungen);
- foreach $directory (@webdavdirs) {
- $file = "webdav/" . $directory . "/webdav-user";
- if ($myconfig->{$directory}) {
- open(HTACCESS, "$file") or die "cannot open webdav-user $!\n";
- while (<HTACCESS>) {
- ($login, $password) = split(/:/, $_);
- if ($login ne $form->{login}) {
- $newfile .= $_;
- }
- }
- close(HTACCESS);
- open(HTACCESS, "> $file") or die "cannot open webdav-user $!\n";
- $newfile .= $myconfig->{login} . ":" . $myconfig->{password} . "\n";
- print(HTACCESS $newfile);
- close(HTACCESS);
- } else {
- $form->{$directory} = 0;
- open(HTACCESS, "$file") or die "cannot open webdav-user $!\n";
- while (<HTACCESS>) {
- ($login, $password) = split(/:/, $_);
- if ($login ne $form->{login}) {
- $newfile .= $_;
- }
- }
- close(HTACCESS);
- open(HTACCESS, "> $file") or die "cannot open webdav-user $!\n";
- print(HTACCESS $newfile);
- close(HTACCESS);
- }
- }
- }
-
- $main::lxdebug->leave_sub();
-
- return $rc;
-}
-
-sub defaultaccounts {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- # get defaults from defaults table
- my $query = qq|SELECT * FROM defaults|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $form->{defaults} = $sth->fetchrow_hashref(NAME_lc);
- $form->{defaults}{IC} = $form->{defaults}{inventory_accno_id};
- $form->{defaults}{IC_income} = $form->{defaults}{income_accno_id};
- $form->{defaults}{IC_expense} = $form->{defaults}{expense_accno_id};
- $form->{defaults}{FX_gain} = $form->{defaults}{fxgain_accno_id};
- $form->{defaults}{FX_loss} = $form->{defaults}{fxloss_accno_id};
-
- $sth->finish;
-
- $query = qq|SELECT c.id, c.accno, c.description, c.link
- FROM chart c
- WHERE c.link LIKE '%IC%'
- ORDER BY c.accno|;
- $sth = $dbh->prepare($query);
- $sth->execute || $self->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- foreach my $key (split(/:/, $ref->{link})) {
- if ($key =~ /IC/) {
- $nkey = $key;
- if ($key =~ /cogs/) {
- $nkey = "IC_expense";
- }
- if ($key =~ /sale/) {
- $nkey = "IC_income";
- }
- %{ $form->{IC}{$nkey}{ $ref->{accno} } } = (
- id => $ref->{id},
- description => $ref->{description}
- );
- }
- }
- }
- $sth->finish;
-
- $query = qq|SELECT c.id, c.accno, c.description
- FROM chart c
- WHERE c.category = 'I'
- AND c.charttype = 'A'
- ORDER BY c.accno|;
- $sth = $dbh->prepare($query);
- $sth->execute || $self->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- %{ $form->{IC}{FX_gain}{ $ref->{accno} } } = (
- id => $ref->{id},
- description => $ref->{description}
- );
- }
- $sth->finish;
-
- $query = qq|SELECT c.id, c.accno, c.description
- FROM chart c
- WHERE c.category = 'E'
- AND c.charttype = 'A'
- ORDER BY c.accno|;
- $sth = $dbh->prepare($query);
- $sth->execute || $self->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- %{ $form->{IC}{FX_loss}{ $ref->{accno} } } = (
- id => $ref->{id},
- description => $ref->{description}
- );
- }
- $sth->finish;
-
- # now get the tax rates and numbers
- $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|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $form->{taxrates}{ $ref->{accno} }{id} = $ref->{id};
- $form->{taxrates}{ $ref->{accno} }{description} = $ref->{description};
- $form->{taxrates}{ $ref->{accno} }{taxnumber} = $ref->{taxnumber}
- if $ref->{taxnumber};
- $form->{taxrates}{ $ref->{accno} }{rate} = $ref->{rate} if $ref->{rate};
- }
-
- $sth->finish;
- $dbh->disconnect;
-
- $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