sub get_account {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
- my $query = qq{
- SELECT c.accno, c.description, c.charttype, c.category,
- c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from,
- c.pos_bwa, datevautomatik,
- tk.taxkey_id, tk.pos_ustva, tk.tax_id,
- tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate
- FROM chart c
- LEFT JOIN taxkeys tk
- ON (c.id=tk.chart_id AND tk.id =
- (SELECT id FROM taxkeys
- WHERE taxkeys.chart_id = c.id AND startdate <= current_date
- ORDER BY startdate DESC LIMIT 1))
- WHERE c.id = ?
- };
-
+ # fetch chart-related data and set form fields
+ # get_account is called by add_account in am.pl
+ # always sets $form->{TAXKEY} and default_accounts
+ # loads chart data when $form->{id} is passed
- $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query");
- my $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
-
- my $ref = $sth->fetchrow_hashref("NAME_lc");
-
- foreach my $key (keys %$ref) {
- $form->{"$key"} = $ref->{"$key"};
- }
-
- $sth->finish;
+ my ($self, $myconfig, $form) = @_;
# get default accounts
- $query = qq|SELECT inventory_accno_id, income_accno_id, expense_accno_id
- FROM defaults|;
- $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query");
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref("NAME_lc");
-
- map { $form->{$_} = $ref->{$_} } keys %{ $ref };
-
- $sth->finish;
-
-
-
- # get taxkeys and description
- $query = qq{
- SELECT
- id,
- (SELECT accno FROM chart WHERE id=tax.chart_id) AS chart_accno,
- taxkey,
- id||'--'||taxkey AS tax,
- taxdescription,
- rate
- FROM tax ORDER BY taxkey
- };
- $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query");
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ map { $form->{$_} = $::instance_conf->{$_} } qw(inventory_accno_id income_accno_id expense_accno_id);
+ require SL::DB::Tax;
+ my $taxes = SL::DB::Manager::Tax->get_all( with_objects => ['chart'] , sort_by => 'taxkey' );
$form->{TAXKEY} = [];
+ foreach my $tk ( @{$taxes} ) {
+ push @{ $form->{TAXKEY} }, { id => $tk->id,
+ chart_accno => $tk->chart_id ? $tk->chart->accno : undef,
+ taxkey => $tk->taxkey,
+ tax => $tk->id . '--' . $tk->taxkey,
+ rate => $tk->rate
+ };
+ };
- while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
- push @{ $form->{TAXKEY} }, $ref;
- }
-
- $sth->finish;
if ($form->{id}) {
- # get new accounts
- $query = qq|SELECT id, accno,description
- FROM chart
- WHERE link = ?
- ORDER BY accno|;
- $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query");
- $sth = $dbh->prepare($query);
- $sth->execute($form->{link}) || $form->dberror($query . " ($form->{link})");
- $form->{NEWACCOUNT} = [];
- while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
- push @{ $form->{NEWACCOUNT} }, $ref;
+ my $chart_obj = SL::DB::Manager::Chart->find_by(id => $form->{id}) || die "Can't open chart";
+
+ my @chart_fields = qw(accno description charttype category link pos_bilanz
+ pos_eur pos_er new_chart_id valid_from pos_bwa datevautomatik);
+ foreach my $cf ( @chart_fields ) {
+ $form->{"$cf"} = $chart_obj->$cf;
}
- $sth->finish;
+ my $active_taxkey = $chart_obj->get_active_taxkey;
+ $form->{$_} = $active_taxkey->$_ foreach qw(taxkey_id pos_ustva tax_id startdate);
+ $form->{tax} = $active_taxkey->tax_id . '--' . $active_taxkey->taxkey_id;
- # get the taxkeys of account
-
- $query = qq{
- SELECT
- tk.id,
- tk.chart_id,
- c.accno,
- tk.tax_id,
- t.taxdescription,
- t.rate,
- tk.taxkey_id,
- tk.pos_ustva,
- tk.startdate
- FROM taxkeys tk
- LEFT JOIN tax t ON (t.id = tk.tax_id)
- LEFT JOIN chart c ON (c.id = t.chart_id)
-
- WHERE tk.chart_id = ?
- ORDER BY startdate DESC
- };
- $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query");
- $sth = $dbh->prepare($query);
+ # check if there are any transactions for this chart
+ $form->{orphaned} = $chart_obj->has_transaction ? 0 : 1;
- $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
+ # check if new account is active
+ # The old sql query was broken since at least 2006 and always returned 0
+ $form->{new_chart_valid} = $chart_obj->new_chart_valid;
+ # get the taxkeys of the account
$form->{ACCOUNT_TAXKEYS} = [];
-
- while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
- push @{ $form->{ACCOUNT_TAXKEYS} }, $ref;
+ foreach my $taxkey ( @{ $chart_obj->taxkeys } ) {
+ push @{ $form->{ACCOUNT_TAXKEYS} }, { id => $taxkey->id,
+ chart_id => $taxkey->chart_id,
+ tax_id => $taxkey->tax_id,
+ taxkey_id => $taxkey->taxkey_id,
+ pos_ustva => $taxkey->pos_ustva,
+ startdate => $taxkey->startdate->to_kivitendo,
+ taxdescription => $taxkey->tax->taxdescription,
+ rate => $taxkey->tax->rate,
+ accno => defined $taxkey->tax->chart_id ? $taxkey->tax->chart->accno : undef,
+ };
}
- $sth->finish;
-
- }
- # check if we have any transactions
- $query = qq|SELECT a.trans_id FROM acc_trans a
- WHERE a.chart_id = ?|;
- $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query");
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
-
- ($form->{orphaned}) = $sth->fetchrow_array;
- $form->{orphaned} = !$form->{orphaned};
- $sth->finish;
+ # get new accounts (Folgekonto). Find all charts with the same link
+ $form->{NEWACCOUNT} = $chart_obj->db->dbh->selectall_arrayref('select id, accno,description from chart where link = ? order by accno', {Slice => {}}, $chart_obj->link);
- # check if new account is active
- $form->{new_chart_valid} = 0;
- if ($form->{new_chart_id}) {
- $query = qq|SELECT current_date-valid_from FROM chart
- WHERE id = ?|;
- $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query");
- my ($count) = selectrow_query($form, $dbh, $query, $form->{id});
- if ($count >=0) {
- $form->{new_chart_valid} = 1;
- }
- $sth->finish;
- }
-
- $dbh->disconnect;
+ } else { # set to orphaned for new charts, so chart_type can be changed (needed by $AccountIsPosted)
+ $form->{orphaned} = 1;
+ };
$main::lxdebug->leave_sub();
}
}
}
- $form->{link} = "";
- foreach my $item ($form->{AR}, $form->{AR_amount},
- $form->{AR_tax}, $form->{AR_paid},
- $form->{AP}, $form->{AP_amount},
- $form->{AP_tax}, $form->{AP_paid},
- $form->{IC}, $form->{IC_sale},
- $form->{IC_cogs}, $form->{IC_taxpart},
- $form->{IC_income}, $form->{IC_expense},
- $form->{IC_taxservice}
- ) {
- $form->{link} .= "${item}:" if ($item);
- }
- chop $form->{link};
+ my @link_order = qw(AR AR_amount AR_tax AR_paid AP AP_amount AP_tax AP_paid IC IC_sale IC_cogs IC_taxpart IC_income IC_expense IC_taxservice);
+ $form->{link} = join ':', grep $_, map $form->{$_}, @link_order;
# strip blanks from accno
map { $form->{$_} =~ s/ //g; } qw(accno);
+ # collapse multiple (horizontal) whitespace in chart description (Ticket 148)
+ map { $form->{$_} =~ s/\h+/ /g } qw(description);
+
my ($query, $sth);
if ($form->{id} eq "NULL") {
pos_bwa = ?,
pos_bilanz = ?,
pos_eur = ?,
+ pos_er = ?,
new_chart_id = ?,
valid_from = ?,
datevautomatik = ?
conv_i($form->{pos_bwa}),
conv_i($form->{pos_bilanz}),
conv_i($form->{pos_eur}),
+ conv_i($form->{pos_er}),
conv_i($form->{new_chart_id}),
conv_date($form->{valid_from}),
($form->{datevautomatik} eq 'T') ? 'true':'false',
projects / kivitendo-erp.git / blobdiff