-}
-
-sub get_lead {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query =
- qq|SELECT l.id, l.lead
- FROM leads l
- WHERE l.id = $form->{id}|;
- 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_lead {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $form->{lead} =~ s/\'/\'\'/g;
-
- # id is the old record
- if ($form->{id}) {
- $query = qq|UPDATE leads SET
- lead = '$form->{description}'
- WHERE id = $form->{id}|;
- } else {
- $query = qq|INSERT INTO leads
- (lead)
- VALUES ('$form->{description}')|;
- }
- $dbh->do($query) || $form->dberror($query);
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub delete_lead {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $query = qq|DELETE FROM leads
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub business {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query = qq|SELECT id, description, discount, customernumberinit, salesman
- FROM business
- ORDER BY 2|;
-
- $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_business {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query =
- qq|SELECT b.description, b.discount, b.customernumberinit, b.salesman
- FROM business b
- WHERE b.id = $form->{id}|;
- 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_business {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $form->{description} =~ s/\'/\'\'/g;
- $form->{discount} /= 100;
- $form->{salesman} *= 1;
-
- # 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}|;
- } else {
- $query = qq|INSERT INTO business
- (description, discount, customernumberinit, salesman)
- VALUES ('$form->{description}', $form->{discount}, '$form->{customernumberinit}', '$form->{salesman}')|;
- }
- $dbh->do($query) || $form->dberror($query);
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub delete_business {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $query = qq|DELETE FROM business
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-
-sub language {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form, $return_list) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query =
- "SELECT id, description, template_code, article_code, " .
- " output_numberformat, output_dateformat, output_longdates " .
- "FROM language ORDER BY description";
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my $ary = [];
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push(@{ $ary }, $ref);
- }
-
- $sth->finish;
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-
- if ($return_list) {
- return @{$ary};
- } else {
- $form->{ALL} = $ary;
- }
-}
-
-sub get_language {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query =
- "SELECT description, template_code, article_code, " .
- " output_numberformat, output_dateformat, output_longdates " .
- "FROM language 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;
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub get_language_details {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form, $id) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query =
- "SELECT template_code, " .
- " output_numberformat, output_dateformat, output_longdates " .
- "FROM language WHERE id = ?";
- my @res = $dbh->selectrow_array($query, undef, $id);
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-
- return @res;
-}
-
-sub save_language {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
- my (@values, $query);
-
- map({ push(@values, $form->{$_}); }
- qw(description template_code article_code
- output_numberformat output_dateformat output_longdates));
-
- # id is the old record
- if ($form->{id}) {
- $query =
- "UPDATE language SET " .
- " description = ?, template_code = ?, article_code = ?, " .
- " output_numberformat = ?, output_dateformat = ?, " .
- " output_longdates = ? " .
- "WHERE id = ?";
- push(@values, $form->{id});
- } else {
- $query =
- "INSERT INTO language (" .
- " description, template_code, article_code, " .
- " output_numberformat, output_dateformat, output_longdates" .
- ") VALUES (?, ?, ?, ?, ?, ?)";
- }
- $dbh->do($query, undef, @values) ||
- $form->dberror($query . " (" . join(", ", @values) . ")");
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub delete_language {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- my $query = "DELETE FROM units_language WHERE language_id = ?";
- $dbh->do($query, undef, $form->{"id"}) ||
- $form->dberror($query . " ($form->{id})");
-
- $query = "DELETE FROM language WHERE id = ?";
- $dbh->do($query, undef, $form->{"id"}) ||
- $form->dberror($query . " ($form->{id})");
-
- $dbh->commit();
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-
-sub buchungsgruppe {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query = qq|SELECT id, description, inventory_accno_id, (select accno from chart where id=inventory_accno_id) as inventory_accno, income_accno_id_0, (select accno from chart where id=income_accno_id_0) as income_accno_0, expense_accno_id_0, (select accno from chart where id=expense_accno_id_0) as expense_accno_0, income_accno_id_1, (select accno from chart where id=income_accno_id_1) as income_accno_1, expense_accno_id_1, (select accno from chart where id=expense_accno_id_1) as expense_accno_1, income_accno_id_2, (select accno from chart where id=income_accno_id_2) as income_accno_2, expense_accno_id_2, (select accno from chart where id=expense_accno_id_2) as expense_accno_2, income_accno_id_3, (select accno from chart where id=income_accno_id_3) as income_accno_3, expense_accno_id_3, (select accno from chart where id=expense_accno_id_3) as expense_accno_3
- FROM buchungsgruppen
- ORDER BY id|;
-
- $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_buchungsgruppe {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- if ($form->{id}) {
- my $query =
- qq|SELECT description, inventory_accno_id, (select accno from chart where id=inventory_accno_id) as inventory_accno, income_accno_id_0, (select accno from chart where id=income_accno_id_0) as income_accno_0, expense_accno_id_0, (select accno from chart where id=expense_accno_id_0) as expense_accno_0, income_accno_id_1, (select accno from chart where id=income_accno_id_1) as income_accno_1, expense_accno_id_1, (select accno from chart where id=expense_accno_id_1) as expense_accno_1, income_accno_id_2, (select accno from chart where id=income_accno_id_2) as income_accno_2, expense_accno_id_2, (select accno from chart where id=expense_accno_id_2) as expense_accno_2, income_accno_id_3, (select accno from chart where id=income_accno_id_3) as income_accno_3, expense_accno_id_3, (select accno from chart where id=expense_accno_id_3) as expense_accno_3
- FROM buchungsgruppen
- WHERE id = $form->{id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my $ref = $sth->fetchrow_hashref(NAME_lc);
-
- map { $form->{$_} = $ref->{$_} } keys %$ref;
-
- $sth->finish;
-
- my $query =
- qq|SELECT count(id) as anzahl
- FROM parts
- WHERE buchungsgruppen_id = $form->{id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my $ref = $sth->fetchrow_hashref(NAME_lc);
- if (!$ref->{anzahl}) {
- $form->{orphaned} = 1;
- }
- $sth->finish;
-
- }
-
- $query = "SELECT inventory_accno_id, income_accno_id, expense_accno_id ".
- "FROM defaults";
- ($form->{"std_inventory_accno_id"}, $form->{"std_income_accno_id"},
- $form->{"std_expense_accno_id"}) = $dbh->selectrow_array($query);
-
- my $module = "IC";
- $query = qq|SELECT c.accno, c.description, c.link, c.id,
- d.inventory_accno_id, d.income_accno_id, d.expense_accno_id
- FROM chart c, defaults d
- WHERE c.link LIKE '%$module%'
- ORDER BY c.accno|;
-
-
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- foreach my $key (split(/:/, $ref->{link})) {
- if (!$form->{"std_inventory_accno_id"} && ($key eq "IC")) {
- $form->{"std_inventory_accno_id"} = $ref->{"id"};
- }
- if ($key =~ /$module/) {
- if ( ($ref->{id} eq $ref->{inventory_accno_id})
- || ($ref->{id} eq $ref->{income_accno_id})
- || ($ref->{id} eq $ref->{expense_accno_id})) {
- push @{ $form->{"${module}_links"}{$key} },
- { accno => $ref->{accno},
- description => $ref->{description},
- selected => "selected",
- id => $ref->{id} };
- } else {
- push @{ $form->{"${module}_links"}{$key} },
- { accno => $ref->{accno},
- description => $ref->{description},
- selected => "",
- id => $ref->{id} };
- }
- }
- }
- }
- $sth->finish;
-
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub save_buchungsgruppe {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $form->{description} =~ s/\'/\'\'/g;
-
-
- # id is the old record
- if ($form->{id}) {
- $query = qq|UPDATE buchungsgruppen SET
- description = '$form->{description}',
- inventory_accno_id = '$form->{inventory_accno_id}',
- income_accno_id_0 = '$form->{income_accno_id_0}',
- expense_accno_id_0 = '$form->{expense_accno_id_0}',
- income_accno_id_1 = '$form->{income_accno_id_1}',
- expense_accno_id_1 = '$form->{expense_accno_id_1}',
- income_accno_id_2 = '$form->{income_accno_id_2}',
- expense_accno_id_2 = '$form->{expense_accno_id_2}',
- income_accno_id_3 = '$form->{income_accno_id_3}',
- expense_accno_id_3 = '$form->{expense_accno_id_3}'
- WHERE id = $form->{id}|;
- } else {
- $query = qq|INSERT INTO buchungsgruppen
- (description, inventory_accno_id, income_accno_id_0, expense_accno_id_0, income_accno_id_1, expense_accno_id_1, income_accno_id_2, expense_accno_id_2, income_accno_id_3, expense_accno_id_3)
- VALUES ('$form->{description}', '$form->{inventory_accno_id}', '$form->{income_accno_id_0}', '$form->{expense_accno_id_0}', '$form->{income_accno_id_1}', '$form->{expense_accno_id_1}', '$form->{income_accno_id_2}', '$form->{expense_accno_id_2}', '$form->{income_accno_id_3}', '$form->{expense_accno_id_3}')|;
- }
- $dbh->do($query) || $form->dberror($query);
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub delete_buchungsgruppe {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $query = qq|DELETE FROM buchungsgruppen
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub printer {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query = qq|SELECT id, printer_description, template_code, printer_command
- FROM printers
- 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;
- }
-
- $sth->finish;
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub get_printer {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query =
- qq|SELECT p.printer_description, p.template_code, p.printer_command
- FROM printers p
- WHERE p.id = $form->{id}|;
- 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_printer {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $form->{printer_description} =~ s/\'/\'\'/g;
- $form->{printer_command} =~ s/\'/\'\'/g;
- $form->{template_code} =~ s/\'/\'\'/g;
-
-
- # 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}|;
- } else {
- $query = qq|INSERT INTO printers
- (printer_description, template_code, printer_command)
- VALUES ('$form->{printer_description}', '$form->{template_code}', '$form->{printer_command}')|;
- }
- $dbh->do($query) || $form->dberror($query);
-
- $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 = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $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 id|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $ref->{percent_skonto} = $form->format_amount($myconfig,($ref->{percent_skonto} * 100));
- 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 = $form->{id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my $ref = $sth->fetchrow_hashref(NAME_lc);
- $ref->{percent_skonto} = $form->format_amount($myconfig,($ref->{percent_skonto} * 100));
-
- map { $form->{$_} = $ref->{$_} } keys %$ref;
-
- $sth->finish;
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub save_payment {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $form->{description} =~ s/\'/\'\'/g;
- $form->{description_long} =~ s/\'/\'\'/g;
- $percentskonto = $form->parse_amount($myconfig, $form->{percent_skonto}) /100;
- $form->{ranking} *= 1;
- $form->{terms_netto} *= 1;
- $form->{terms_skonto} *= 1;
- $form->{percent_skonto} *= 1;
-
-
-
- # id is the old record
- if ($form->{id}) {
- $query = qq|UPDATE payment_terms SET
- description = '$form->{description}',
- ranking = $form->{ranking},
- description_long = '$form->{description_long}',
- terms_netto = $form->{terms_netto},
- terms_skonto = $form->{terms_skonto},
- percent_skonto = $percentskonto
- WHERE id = $form->{id}|;
- } else {
- $query = qq|INSERT INTO payment_terms
- (description, ranking, description_long, terms_netto, terms_skonto, percent_skonto)
- VALUES ('$form->{description}', $form->{ranking}, '$form->{description_long}', $form->{terms_netto}, $form->{terms_skonto}, $percentskonto)|;
- }
- $dbh->do($query) || $form->dberror($query);
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub delete_payment {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $query = qq|DELETE FROM payment_terms
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $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