+ $query = qq|UPDATE printers SET
+ 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 (?, ?, ?)|;
+ }
+ do_query($form, $dbh, $query, @values);
+
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub delete_printer {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
+
+ $query = qq|DELETE FROM printers
+ WHERE id = ?|;
+ do_query($form, $dbh, $query, $form->{id});
+
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub payment {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
+
+ my $query = qq|SELECT * FROM payment_terms ORDER BY sortkey|;
+
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ $form->{ALL} = [];
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{ALL} }, $ref;
+ }
+
+ $sth->finish;
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_payment {
+ $main::lxdebug->enter_sub();
+
+ 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 = ?,
+ terms_netto = ?, terms_skonto = ?,
+ percent_skonto = ?
+ WHERE id = ?|;
+ my @values = ($form->{description}, $form->{description_long},
+ $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 prepare_template_filename {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ my ($filename, $display_filename);
+
+ if ($form->{type} eq "stylesheet") {
+ $filename = "css/$myconfig->{stylesheet}";
+ $display_filename = $myconfig->{stylesheet};
+
+ } else {
+ $filename = $form->{formname};
+
+ if ($form->{language}) {
+ my ($id, $template_code) = split(/--/, $form->{language});
+ $filename .= "_${template_code}";
+ }
+
+ if ($form->{printer}) {
+ my ($id, $template_code) = split(/--/, $form->{printer});
+ $filename .= "_${template_code}";
+ }
+
+ $filename .= "." . ($form->{format} eq "html" ? "html" : "tex");
+ $filename =~ s|.*/||;
+ $display_filename = $filename;
+ $filename = "$myconfig->{templates}/$filename";
+ }
+
+ $main::lxdebug->leave_sub();
+
+ return ($filename, $display_filename);
+}
+
+
+sub load_template {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $filename) = @_;
+
+ my ($content, $lines) = ("", 0);
+
+ local *TEMPLATE;
+
+ if (open(TEMPLATE, $filename)) {
+ while (<TEMPLATE>) {
+ $content .= $_;
+ $lines++;
+ }
+ close(TEMPLATE);
+ }
+
+ $main::lxdebug->leave_sub();
+
+ return ($content, $lines);
+}
+
+sub save_template {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $filename, $content) = @_;
+
+ local *TEMPLATE;
+
+ my $error = "";
+
+ if (open(TEMPLATE, ">$filename")) {
+ $content =~ s/\r\n/\n/g;
+ print(TEMPLATE $content);
+ close(TEMPLATE);
+ } else {
+ $error = $!;
+ }
+
+ $main::lxdebug->leave_sub();
+
+ return $error;
+}
+
+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 | .
+ 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
+ $query = qq|UPDATE employee
+ SET name = ?
+ WHERE login = ?|;
+ do_query($form, $dbh, $query, $form->{name}, $form->{login});
+
+ my $rc = $dbh->commit;
+ $dbh->disconnect;
+
+ # save first currency in myconfig
+ $form->{currency} = substr($form->{curr}, 0, 3);
+
+ $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 closedto {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ my $dbh = $form->dbconnect($myconfig);
+
+ my $query = qq|SELECT closedto, revtrans FROM defaults|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ ($form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
+
+ $sth->finish;
+
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub closebooks {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ my $dbh = $form->dbconnect($myconfig);
+
+ my ($query, @values);
+
+ if ($form->{revtrans}) {
+ $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}));
+