X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV.pm;h=d6dd7c629f672bc35d81d85e220f7948688d2b2e;hb=b2c8e6aad4973545057c6529445c0f5f6f2a1cbe;hp=055a7f07679df8b73150984f9f5880cf9cca8827;hpb=bbd8da97d8ab15a358c1c905a9b4f164ed4db04d;p=kivitendo-erp.git diff --git a/SL/DATEV.pm b/SL/DATEV.pm index 055a7f076..d6dd7c629 100644 --- a/SL/DATEV.pm +++ b/SL/DATEV.pm @@ -27,9 +27,33 @@ package DATEV; use SL::DBUtils; +use SL::DATEV::KNEFile; use Data::Dumper; +sub _fill { + $main::lxdebug->enter_sub(); + + my $text = shift; + my $field_len = shift; + my $fill_char = shift; + my $alignment = shift || 'right'; + + my $text_len = length $text; + + if ($field_len < $text_len) { + $text = substr $text, 0, $field_len; + + } elsif ($field_len > $text_len) { + my $filler = ($fill_char) x ($field_len - $text_len); + $text = $alignment eq 'right' ? $filler . $text : $text . $filler; + } + + $main::lxdebug->leave_sub(); + + return $text; +} + sub get_datev_stamm { $main::lxdebug->enter_sub(); @@ -207,13 +231,14 @@ sub get_dates { } elsif ($zeitraum eq "zeit") { - $fromto .= - "'" . $transdatefrom . "' and transdate <= '" . $transdateto . "'"; + $fromto .= "'" . $transdatefrom . "' and transdate <= '" . $transdateto . "'"; + my ($yy, $mm, $dd) = $main::locale->parse_date(\%main::myconfig, $transdatefrom); + $jahr = $yy; } $main::lxdebug->leave_sub(); - return $fromto; + return ($fromto, $jahr); } sub _get_transactions { @@ -226,6 +251,8 @@ sub _get_transactions { my $dbh = $form->get_standard_dbh($myconfig); + my @errors = (); + $fromto =~ s/transdate/ac\.transdate/g; my %taxes = selectall_as_map($form, $dbh, qq|SELECT id, rate FROM tax|, 'id', 'rate'); @@ -324,8 +351,8 @@ sub _get_transactions { || $trans->[$j]->{'taxkey'} eq "1" || $trans->[$j]->{'taxkey'} eq "10" || $trans->[$j]->{'taxkey'} eq "11")) { - my %new_trans = {}; - map({ $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys(%{ $trans->[$notsplitindex] })); + my %new_trans = (); + map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] }; $absumsatz += $trans->[$j]->{'amount'}; $new_trans{'amount'} = $trans->[$j]->{'amount'} * (-1); @@ -338,13 +365,13 @@ sub _get_transactions { } elsif (($j != $notsplitindex) && ($trans->[$j]->{'chart_id'} eq "")) { $absumsatz += ($trans->[$j]->{'amount'} * (1 + $taxes{ $taxid_taxkeys{$trans->[$j]->{'taxkey'}} })); - my %new_trans = {}; - map({ $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys(%{ $trans->[$notsplitindex] })); + my %new_trans = (); + map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] }; - $test = 1 + $taxes{ $taxid_taxkeys{$trans->[$j]->{'taxkey'}} }; - $new_trans{'amount'} = $form->round_amount(($trans->[$j]->{'amount'} * $test * -1), 2); - $new_trans{'umsatz'} = abs($form->round_amount(($trans->[$j]->{'amount'} * $test), 2)) * $ml; - $trans->[$j]->{'umsatz'} = abs($form->round_amount(($trans->[$j]->{'amount'} * $test), 2)) * $ml; + my $tax_rate = 1 + $taxes{ $taxid_taxkeys{$trans->[$j]->{'taxkey'}} }; + $new_trans{'amount'} = $form->round_amount(($trans->[$j]->{'amount'} * $tax_rate * -1), 2); + $new_trans{'umsatz'} = abs($form->round_amount(($trans->[$j]->{'amount'} * $tax_rate), 2)) * $ml; + $trans->[$j]->{'umsatz'} = abs($form->round_amount(($trans->[$j]->{'amount'} * $tax_rate), 2)) * $ml; push @splits, [ \%new_trans, $trans->[$j] ]; push @{ $form->{DATEV} }, $splits[-1]; @@ -352,11 +379,13 @@ sub _get_transactions { } if (abs($absumsatz) > 0.01) { - $form->error("Datev-Export fehlgeschlagen! Bei Transaktion $trans->[0]->{trans_id} $absumsatz\n"); + push @errors, "Datev-Export fehlgeschlagen! Bei Transaktion $trans->[0]->{trans_id} ($absumsatz)\n"; } } - $sth->finish; - $dbh->disconnect; + + $sth->finish(); + + $form->error(join("
\n", @errors)) if (@errors); $main::lxdebug->leave_sub(); } @@ -364,50 +393,27 @@ sub _get_transactions { sub make_kne_data_header { $main::lxdebug->enter_sub(); - my ($myconfig, $form, $fromto) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my @a = localtime; - $jahr = $a[5]; - - #Header - $anwendungsnr = ($fromto) ? "\x31\x31" : "\x31\x33"; - while (length($form->{datentraegernr}) < 3) { - $form->{datentraegernr} = "\x30" . $form->{datentraegernr}; - } - - $header = "\x1D\x18\x31" . $form->{datentraegernr} . $anwendungsnr; - - $dfvkz = $form->{dfvkz}; - while (length($dfvkz) < 2) { - $dfvkz = "\x30" . $dfvkz; - } - $header .= $dfvkz; - - $beraternr = $form->{beraternr}; - while (length($beraternr) < 7) { - $beraternr = "\x30" . $beraternr; - } - $header .= $beraternr; - - $mandantennr = $form->{mandantennr}; - while (length($mandantennr) < 5) { - $mandantennr = "\x30" . $mandantennr; - } - $header .= $mandantennr; + my ($myconfig, $form, $fromto, $start_jahr) = @_; - $abrechnungsnr = $form->{abrechnungsnr} . $jahr; - while (length($abrechnungsnr) < 6) { - $abrechnungsnr = "\x30" . $abrechnungsnr; + my $jahr = $start_jahr; + if (!$jahr) { + my @a = localtime; + $jahr = $a[5]; } - $header .= $abrechnungsnr; - $fromto =~ s/transdate|>=|and|\'|<=//g; - my ($from, $to) = split / /, $fromto; - $from =~ s/ //g; - $to =~ s/ //g; + #Header + my $header = "\x1D\x181"; + $header .= _fill($form->{datentraegernr}, 3, '0'); + $header .= ($fromto) ? "11" : "13"; # Anwendungsnummer + $header .= _fill($form->{dfvkz}, 2, '0'); + $header .= _fill($form->{beraternr}, 7, '0'); + $header .= _fill($form->{mandantennr}, 5, '0'); + $header .= _fill($form->{abrechnungsnr} . $jahr, 6, '0'); + + $fromto =~ s/transdate|>=|and|\'|<=//g; + my ($from, $to) = split / /, $fromto; + $from =~ s/ //g; + $to =~ s/ //g; if ($from ne "") { my ($fday, $fmonth, $fyear) = split(/\./, $from); @@ -437,47 +443,30 @@ sub make_kne_data_header { $to = ""; } $header .= $to; + if ($fromto ne "") { - $primanota = "\x30\x30\x31"; + $primanota = "001"; $header .= $primanota; } - $passwort = $form->{passwort}; - while (length($passwort) < 4) { - $passwort = "\x30" . $passwort; - } - $header .= $passwort; - - $anwendungsinfo = "\x20" x 16; - $header .= $anwendungsinfo; - $inputinfo = "\x20" x 16; - $header .= $inputinfo; - + $header .= _fill($form->{passwort}, 4, '0'); + $header .= " " x 16; # Anwendungsinfo + $header .= " " x 16; # Inputinfo $header .= "\x79"; #Versionssatz - if ($form->{exporttype} == 0) { - $versionssatz = "\xB5" . "1,"; - } else { - $versionssatz = "\xB6" . "1,"; - } + my $versionssatz = $form->{exporttype} == 0 ? "\xB5" . "1," : "\xB6" . "1,"; - $query = qq| select accno from chart limit 1|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); + my $dbh = $form->get_standard_dbh($myconfig); + my $query = qq|SELECT accno FROM chart LIMIT 1|; + my $ref = selectfirst_hashref_query($form, $dbh, $query); - $accnolength = $ref->{accno}; - $sth->finish; + $versionssatz .= length $ref->{accno}; + $versionssatz .= ","; + $versionssatz .= length $ref->{accno}; + $versionssatz .= ",SELF" . "\x1C\x79"; - $versionssatz .= length($accnolength); - $versionssatz .= ","; - $versionssatz .= length($accnolength); - $versionssatz .= ",SELF" . "\x1C\x79"; - - $dbh->disconnect; - - $header .= $versionssatz; + $header .= $versionssatz; $main::lxdebug->leave_sub(); @@ -512,27 +501,12 @@ sub datetofour { return $date; } -sub formatumsatz { - $main::lxdebug->enter_sub(); - - my ($umsatz, $stellen) = @_; - - $umsatz =~ s/-//; - ($vorkomma, $nachkomma) = split(/\./, $umsatz); - $umsatz = ""; - if ($stellen > 0) { - for ($i = $stellen; $i >= $stellen + 2 - length($vorkomma); $i--) { - $umsatz .= "0"; - } - } - for ($i = 3; $i > length($nachkomma); $i--) { - $nachkomma .= "0"; - } - $umsatz = $vorkomma . substr($nachkomma, 0, 2); +sub trim_leading_zeroes { + my $str = shift; - $main::lxdebug->leave_sub(); + $str =~ s/^0+//g; - return $umsatz; + return $str; } sub make_ed_versionset { @@ -540,22 +514,21 @@ sub make_ed_versionset { my ($header, $filename, $blockcount, $fromto) = @_; - $versionset = "V" . substr($filename, 2, 5); - $versionset .= substr($header, 6, 22); + my $versionset = "V" . substr($filename, 2, 5); + $versionset .= substr($header, 6, 22); + if ($fromto ne "") { $versionset .= "0000" . substr($header, 28, 19); } else { - $datum = "\x20" x 16; + $datum = " " x 16; $versionset .= $datum . "001" . substr($header, 28, 4); } - while (length($blockcount) < 5) { - $blockcount = "0" . $blockcount; - } - $versionset .= $blockcount; + + $versionset .= _fill($blockcount, 5, '0'); $versionset .= "001"; - $versionset .= "\x20\x31"; + $versionset .= " 1"; $versionset .= substr($header, -12, 10) . " "; - $versionset .= "\x20" x 53; + $versionset .= " " x 53; $main::lxdebug->leave_sub(); @@ -566,31 +539,14 @@ sub make_ev_header { $main::lxdebug->enter_sub(); my ($form, $fileno) = @_; - $datentraegernr = $form->{datentraegernr}; - $beraternummer = $form->{beraternr}; - $beratername = $form->{beratername}; - $anzahl_dateien = $fileno; - while (length($datentraegernr) < 3) { - $datentraegernr .= " "; - } - - while (length($beraternummer) < 7) { - $beraternummer .= " "; - } - - while (length($beratername) < 9) { - $beratername .= " "; - } - - while (length($anzahl_dateien) < 5) { - $anzahl_dateien = "0" . $anzahl_dateien; - } - - $ev_header = - $datentraegernr . "\x20\x20\x20" . $beraternummer . $beratername . "\x20"; - $ev_header .= $anzahl_dateien . $anzahl_dateien; - $ev_header .= "\x20" x 95; + my $ev_header = _fill($form->{datentraegernr}, 3, ' ', 'left'); + $ev_header .= " "; + $ev_header .= _fill($form->{beraternr}, 7, ' ', 'left'); + $ev_header .= _fill($form->{beratername}, 9, ' ', 'left'); + $ev_header .= " "; + $ev_header .= (_fill($fileno, 5, '0')) x 2; + $ev_header .= " " x 95; $main::lxdebug->leave_sub(); @@ -617,7 +573,7 @@ sub kne_buchungsexport { Buchungssätze verarbeitet: |; - $fromto = + ($fromto, $start_jahr) = &get_dates($form->{zeitraum}, $form->{monat}, $form->{quartal}, $form->{transdatefrom}, $form->{transdateto}); @@ -625,17 +581,14 @@ sub kne_buchungsexport { my $counter = 0; print qq|
2. Durchlauf:|; while (scalar(@{ $form->{DATEV} })) { - my $blockcount = 1; - my $remaining_bytes = 256; - my $total_bytes = 256; - my $umsatzsumme = 0; - my $buchungssatz = ""; + my $umsatzsumme = 0; $filename++; my $ed_filename = $export_path . $filename; push(@filenames, $filename); - open(ED, "> $ed_filename") or die "can't open outputfile: $!\n"; - $header = &make_kne_data_header($myconfig, $form, $fromto); - $remaining_bytes -= length($header); + $header = &make_kne_data_header($myconfig, $form, $fromto, $start_jahr); + + my $kne_file = SL::DATEV::KNEFile->new(); + $kne_file->add_block($header); while (scalar(@{ $form->{DATEV} }) > 0) { $transaction = shift @{ $form->{DATEV} }; @@ -645,25 +598,24 @@ sub kne_buchungsexport { print("$counter "); } - $umsatz = 0; - $gegenkonto = ""; - $konto = ""; - $belegfeld1 = ""; - $datum = ""; - $waehrung = ""; - $buchungstext = ""; - $belegfeld2 = ""; - $datevautomatik = 0; - $taxkey = 0; - $charttax = 0; - %umlaute = ('ä' => 'ae', - 'ö' => 'oe', - 'ü' => 'ue', - 'Ä' => 'Ae', - 'Ö' => 'Oe', - 'Ü' => 'Ue', - 'ß' => 'sz'); - + my $umsatz = 0; + my $gegenkonto = ""; + my $konto = ""; + my $belegfeld1 = ""; + my $datum = ""; + my $waehrung = ""; + my $buchungstext = ""; + my $belegfeld2 = ""; + my $datevautomatik = 0; + my $taxkey = 0; + my $charttax = 0; + my %umlaute = ('ä' => 'ae', + 'ö' => 'oe', + 'ü' => 'ue', + 'Ä' => 'Ae', + 'Ö' => 'Oe', + 'Ü' => 'Ue', + 'ß' => 'sz'); for (my $i = 0; $i < $trans_lines; $i++) { if ($trans_lines == 2) { if (abs($transaction->[$i]->{'amount'}) > abs($umsatz)) { @@ -686,39 +638,33 @@ sub kne_buchungsexport { if ( ($transaction->[$i]->{'id'} eq $transaction->[$i]->{'chart_id'}) && ($trans_lines > 2)) { undef($transaction->[$i]); - } elsif ($transaction->[$i]->{'amount'} > 0) { + } elsif ($transaction->[$i]->{'amount'} > 0) { $haben = $i; - } else { + } else { $soll = $i; } } - $umsatzsumme += abs($umsatz); - # Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern foreach $umlaut (keys(%umlaute)) { - $transaction->[$haben]->{'invnumber'} =~ - s/${umlaut}/${umlaute{$umlaut}}/g; - $transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g; + $transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g; + $transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g; } $transaction->[$haben]->{'invnumber'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g; - $transaction->[$haben]->{'name'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g; + $transaction->[$haben]->{'name'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g; - $transaction->[$haben]->{'invnumber'} = - substr($transaction->[$haben]->{'invnumber'}, 0, 12); - $transaction->[$haben]->{'name'} = - substr($transaction->[$haben]->{'name'}, 0, 30); + $transaction->[$haben]->{'invnumber'} = substr($transaction->[$haben]->{'invnumber'}, 0, 12); + $transaction->[$haben]->{'name'} = substr($transaction->[$haben]->{'name'}, 0, 30); $transaction->[$haben]->{'invnumber'} =~ s/\ *$//; $transaction->[$haben]->{'name'} =~ s/\ *$//; if ($trans_lines >= 2) { - $gegenkonto = "a" . $transaction->[$haben]->{'accno'}; - $konto = "e" . $transaction->[$soll]->{'accno'}; + $gegenkonto = "a" . trim_leading_zeroes($transaction->[$haben]->{'accno'}); + $konto = "e" . trim_leading_zeroes($transaction->[$soll]->{'accno'}); if ($transaction->[$haben]->{'invnumber'} ne "") { - $belegfeld1 = - "\xBD" . $transaction->[$haben]->{'invnumber'} . "\x1C"; + $belegfeld1 = "\xBD" . $transaction->[$haben]->{'invnumber'} . "\x1C"; } $datum = "d"; $datum .= &datetofour($transaction->[$haben]->{'transdate'}, 0); @@ -727,117 +673,38 @@ sub kne_buchungsexport { $buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C"; } if ($transaction->[$haben]->{'duedate'} ne "") { - $belegfeld2 = "\xBE" - . &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C"; + $belegfeld2 = "\xBE" . &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C"; } } - if (($remaining_bytes - length("+" . &formatumsatz($umsatz, 0))) <= 6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; - } - $umsatz = abs($umsatz); - $vorzeichen = ($umsatz > 0) ? "+" : "-"; - $buchungssatz .= $vorzeichen . &formatumsatz($umsatz, 0); - $remaining_bytes = $total_bytes - length($buchungssatz . $header); - - if ( ($taxkey || $datevautomatik) - && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) { - if (($remaining_bytes - length("\x6C" . "11")) <= 6) { - $fuellzeichen = - ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; - } - if (!$datevautomatik) { - $buchungssatz .= "\x6C" . $taxkey; - } else { - $buchungssatz .= "\x6C" . "4"; - } - $remaining_bytes = $total_bytes - length($buchungssatz . $header); - } + $umsatz = $kne_file->format_amount(abs($umsatz), 0); + $umsatzsumme += $umsatz; + $kne_file->add_block("+" . $umsatz); - if (($remaining_bytes - length($gegenkonto)) <= 6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; - } - $buchungssatz .= $gegenkonto; - $remaining_bytes = $total_bytes - length($buchungssatz . $header); - - if (($remaining_bytes - length($belegfeld1)) <= 6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; - } - $buchungssatz .= $belegfeld1; - $remaining_bytes = $total_bytes - length($buchungssatz . $header); - - if (($remaining_bytes - length($belegfeld2)) <= 6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; - } - $buchungssatz .= $belegfeld2; - $remaining_bytes = $total_bytes - length($buchungssatz . $header); - - if (($remaining_bytes - length($datum)) <= 6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; - } - $buchungssatz .= $datum; - $remaining_bytes = $total_bytes - length($buchungssatz . $header); - - if (($remaining_bytes - length($konto)) <= 6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; + if ( ( $datevautomatik || $taxkey) + && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) { + $kne_file->add_block("\x6C" . (!$datevautomatik ? $taxkey : "4")); } - $buchungssatz .= $konto; - $remaining_bytes = $total_bytes - length($buchungssatz . $header); - - if (($remaining_bytes - length($buchungstext)) <= 6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; - } - $buchungssatz .= $buchungstext; - $remaining_bytes = $total_bytes - length($buchungssatz . $header); - - if (($remaining_bytes - (length($waehrung . "\x79"))) <= 6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; - } - $buchungssatz .= $waehrung . "\x79"; - $remaining_bytes = $total_bytes - length($buchungssatz . $header); + $kne_file->add_block($gegenkonto); + $kne_file->add_block($belegfeld1); + $kne_file->add_block($belegfeld2); + $kne_file->add_block($datum); + $kne_file->add_block($konto); + $kne_file->add_block($buchungstext); + $kne_file->add_block($waehrung . "\x79"); } - $mandantenendsumme = - "x" . &formatumsatz($umsatzsumme, 14) . "\x79" . "\x7a"; - $fuellzeichen = - 256 - (length($header . $buchungssatz . $mandantenendsumme) % 256); - $dateiende = "\x00" x $fuellzeichen; - print(ED $header); - print(ED $buchungssatz); - print(ED $mandantenendsumme); - print(ED $dateiende); + my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a"; + + $kne_file->add_block($mandantenendsumme); + $kne_file->flush(); + + open(ED, "> $ed_filename") or die "can't open outputfile: $!\n"; + print(ED $kne_file->get_data()); close(ED); - $ed_versionset[$fileno] = - &make_ed_versionset($header, $filename, $blockcount, $fromto); + $ed_versionset[$fileno] = &make_ed_versionset($header, $filename, $kne_file->get_block_count(), $fromto); $fileno++; } @@ -894,14 +761,25 @@ sub kne_stammdatenexport { # connect to database my $dbh = $form->dbconnect($myconfig); - $query = - qq|SELECT c.accno, c.description FROM chart c WHERE c.accno >=| - . $dbh->quote($form->{accnofrom}) . qq| - AND c.accno <= | - . $dbh->quote($form->{accnoto}) . qq| ORDER BY c.accno|; + my (@where, @values) = ((), ()); + if ($form->{accnofrom}) { + push @where, 'c.accno >= ?'; + push @values, $form->{accnofrom}; + } + if ($form->{accnoto}) { + push @where, 'c.accno <= ?'; + push @values, $form->{accnoto}; + } - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $where_str = ' WHERE ' . join(' AND ', map { "($_)" } @where) if (scalar @where); + + my $query = qq|SELECT c.accno, c.description + FROM chart c + $where_str + ORDER BY c.accno|; + + my $sth = $dbh->prepare($query); + $sth->execute(@values) || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) {