+sub retrieve_languages {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
+
+ if ($form->{id}) {
+ $where .= "tr.parts_id=$form->{id}";
+ }
+
+
+ if ($form->{language_values} ne "") {
+ $query = qq|SELECT l.id, l.description, tr.translation, tr.longdescription
+ FROM language l LEFT OUTER JOIN translation tr ON (tr.language_id=l.id AND $where)|;
+ } else {
+ $query = qq|SELECT l.id, l.description
+ FROM language l|;
+ }
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push(@{$languages}, $ref);
+ }
+ $sth->finish;
+
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+ return $languages;
+
+}
+
+sub follow_account_chain {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $form, $dbh, $transdate, $accno_id, $accno) = @_;
+
+ my @visited_accno_ids = ($accno_id);
+
+ my ($query, $sth);
+
+ $query =
+ "SELECT c.new_chart_id, date($transdate) >= c.valid_from AS is_valid, " .
+ " cnew.accno " .
+ "FROM chart c " .
+ "LEFT JOIN chart cnew ON c.new_chart_id = cnew.id " .
+ "WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)";
+ $sth = $dbh->prepare($query);
+
+ while (1) {
+ $sth->execute($accno_id) || $form->dberror($query . " ($accno_id)");
+ $ref = $sth->fetchrow_hashref();
+ last unless ($ref && $ref->{"is_valid"} &&
+ !grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
+ $accno_id = $ref->{"new_chart_id"};
+ $accno = $ref->{"accno"};
+ push(@visited_accno_ids, $accno_id);
+ }
+
+ $main::lxdebug->leave_sub();
+
+ return ($accno_id, $accno);
+}
+
+sub retrieve_accounts {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form, $parts_id, $index, $copy_accnos) = @_;
+
+ my ($query, $sth, $dbh);
+
+ return $main::lxdebug->leave_sub() if (!defined($form->{"taxzone_id"}));
+
+ $dbh = $form->dbconnect($myconfig);
+
+ my $transdate = "";
+ if ($form->{type} eq "invoice") {
+ if (($form->{vc} eq "vendor") || !$form->{deliverydate}) {
+ $transdate = $form->{invdate};
+ } else {
+ $transdate = $form->{deliverydate};
+ }
+ } else {
+ $transdate = $form->{transdate};
+ }
+
+ if ($transdate eq "") {
+ $transdate = "current_date";
+ } else {
+ $transdate = $dbh->quote($transdate);
+ }
+
+ $query =
+ "SELECT " .
+ " p.inventory_accno_id AS is_part, " .
+ " bg.inventory_accno_id, " .
+ " bg.income_accno_id_$form->{taxzone_id} AS income_accno_id, " .
+ " bg.expense_accno_id_$form->{taxzone_id} AS expense_accno_id, " .
+ " c1.accno AS inventory_accno, " .
+ " c2.accno AS income_accno, " .
+ " c3.accno AS expense_accno " .
+ "FROM parts p " .
+ "LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id " .
+ "LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id " .
+ "LEFT JOIN chart c2 ON bg.income_accno_id_$form->{taxzone_id} = c2.id " .
+ "LEFT JOIN chart c3 ON bg.expense_accno_id_$form->{taxzone_id} = c3.id " .
+ "WHERE p.id = ?";
+ $sth = $dbh->prepare($query);
+ $sth->execute($parts_id) || $form->dberror($query . " ($parts_id)");
+ my $ref = $sth->fetchrow_hashref();
+ $sth->finish();
+
+# $main::lxdebug->message(0, "q $query");
+
+ if (!$ref) {
+ $dbh->disconnect();
+ return $lxdebug->leave_sub();
+ }
+
+ $ref->{"inventory_accno_id"} = undef unless ($ref->{"is_part"});
+
+ my %accounts;
+ foreach my $type (qw(inventory income expense)) {
+ next unless ($ref->{"${type}_accno_id"});
+ ($accounts{"${type}_accno_id"}, $accounts{"${type}_accno"}) =
+ $self->follow_account_chain($form, $dbh, $transdate,
+ $ref->{"${type}_accno_id"},
+ $ref->{"${type}_accno"});
+ }
+
+ map({ $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} }
+ qw(inventory income expense));
+
+ my $inc_exp = $form->{"vc"} eq "customer" ? "income" : "expense";
+ my $accno_id = $accounts{"${inc_exp}_accno_id"};
+
+ $query =
+ "SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber " .
+ "FROM tax t " .
+ "LEFT JOIN chart c ON c.id = t.chart_id " .
+ "WHERE t.id IN " .
+ " (SELECT tk.tax_id " .
+ " FROM taxkeys tk " .
+ " WHERE tk.chart_id = $accno_id AND startdate <= $transdate " .
+ " ORDER BY startdate DESC LIMIT 1) ";
+ $sth = $dbh->prepare($query);
+ $sth->execute() || $form->dberror($query);
+ $ref = $sth->fetchrow_hashref();
+ $sth->finish();
+ $dbh->disconnect();
+
+ return $lxdebug->leave_sub() unless ($ref);
+
+ $form->{"taxaccounts_$index"} = $ref->{"accno"};
+ if ($form->{"taxaccounts"} !~ /$ref->{accno}/) {
+ $form->{"taxaccounts"} .= "$ref->{accno} ";
+ }
+ map({ $form->{"$ref->{accno}_${_}"} = $ref->{$_}; }
+ qw(rate description taxnumber));
+
+# $main::lxdebug->message(0, "formvars: rate " . $form->{"$ref->{accno}_rate"} .
+# " description " . $form->{"$ref->{accno}_description"} .
+# " taxnumber " . $form->{"$ref->{accno}_taxnumber"} .
+# " || taxaccounts_$index " . $form->{"taxaccounts_$index"} .
+# " || taxaccounts " . $form->{"taxaccounts"});
+
+ $sth->finish();
+
+ $main::lxdebug->leave_sub();
+}
projects / kivitendo-erp.git / blobdiff