X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FAM.pm;h=9ab6c8092cadfc8beb1c82d9a3dbb4e9a64af7d2;hb=44fb4fe8d98ced50689764dab64b4633016c9fe0;hp=cbbfae03f61b3735798cc46bf07a2b5738bf59df;hpb=15f58ff3dfd79651a95535b53f864ea0e8cb6620;p=kivitendo-erp.git diff --git a/SL/AM.pm b/SL/AM.pm index cbbfae03f..9ab6c8092 100644 --- a/SL/AM.pm +++ b/SL/AM.pm @@ -53,151 +53,70 @@ use strict; 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(); } @@ -224,23 +143,15 @@ sub save_account { } } - $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") { @@ -301,6 +212,7 @@ sub save_account { pos_bwa = ?, pos_bilanz = ?, pos_eur = ?, + pos_er = ?, new_chart_id = ?, valid_from = ?, datevautomatik = ? @@ -315,6 +227,7 @@ sub save_account { 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',