Feature #153 » datev.patch
| SL/DATEV.pm | ||
|---|---|---|
|
my %all_taxchart_ids = selectall_as_map($form, $self->dbh, qq|SELECT DISTINCT chart_id, TRUE AS is_set FROM tax|, 'chart_id', 'is_set');
|
||
|
my $query =
|
||
|
qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey,
|
||
|
qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ar.department_id,
|
||
|
ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate,
|
||
|
ct.name, ct.ustid,
|
||
|
ct.name, ct.ustid, ct.customernumber as vcnumber,
|
||
|
d.description as departmentname,
|
||
|
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
||
|
ar.invoice,
|
||
|
t.rate AS taxrate
|
||
| ... | ... | |
|
LEFT JOIN customer ct ON (ar.customer_id = ct.id)
|
||
|
LEFT JOIN chart c ON (ac.chart_id = c.id)
|
||
|
LEFT JOIN tax t ON (ac.tax_id = t.id)
|
||
|
LEFT JOIN department d ON (ar.department_id = d.id)
|
||
|
WHERE (ar.id IS NOT NULL)
|
||
|
AND $fromto
|
||
|
$trans_id_filter
|
||
| ... | ... | |
|
UNION ALL
|
||
|
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey,
|
||
|
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ap.department_id,
|
||
|
ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate,
|
||
|
ct.name,ct.ustid,
|
||
|
ct.name,ct.ustid, ct.vendornumber as vcnumber,
|
||
|
d.description as departmentname,
|
||
|
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
||
|
ap.invoice,
|
||
|
t.rate AS taxrate
|
||
| ... | ... | |
|
LEFT JOIN vendor ct ON (ap.vendor_id = ct.id)
|
||
|
LEFT JOIN chart c ON (ac.chart_id = c.id)
|
||
|
LEFT JOIN tax t ON (ac.tax_id = t.id)
|
||
|
LEFT JOIN department d ON (ap.department_id = d.id)
|
||
|
WHERE (ap.id IS NOT NULL)
|
||
|
AND $fromto
|
||
|
$trans_id_filter
|
||
| ... | ... | |
|
UNION ALL
|
||
|
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey,
|
||
|
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, gl.department_id,
|
||
|
gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate,
|
||
|
gl.description AS name, NULL as ustid,
|
||
|
gl.description AS name, NULL as ustid, NULL as vcnumber,
|
||
|
d.description as departmentname,
|
||
|
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
||
|
FALSE AS invoice,
|
||
|
t.rate AS taxrate
|
||
| ... | ... | |
|
LEFT JOIN gl ON (ac.trans_id = gl.id)
|
||
|
LEFT JOIN chart c ON (ac.chart_id = c.id)
|
||
|
LEFT JOIN tax t ON (ac.tax_id = t.id)
|
||
|
LEFT JOIN department d ON (gl.department_id = d.id)
|
||
|
WHERE (gl.id IS NOT NULL)
|
||
|
AND $fromto
|
||
|
$trans_id_filter
|
||
| ... | ... | |
|
my $umsatz = 0;
|
||
|
my $gegenkonto = "";
|
||
|
my $konto = "";
|
||
|
my $belegfeld1 = "";
|
||
|
my $datum = "";
|
||
|
my $waehrung = "";
|
||
|
my $buchungstext = "";
|
||
|
my $belegfeld2 = "";
|
||
|
my $kostenstelle1 = ""; # Kost1=8,a,X'bb',K,J,"",N
|
||
|
my $kostenstelle2 = ""; # Kost2=8,a,X'bc',K,J,"",N
|
||
|
my $belegfeld1 = ""; # Belegfeld1=12,a,X'bd',K,J,"",N
|
||
|
my $belegfeld2 = ""; # Belegfeld2=12,a,X'be',K,J,"",N
|
||
|
my $datevautomatik = 0;
|
||
|
my $taxkey = 0;
|
||
|
my $charttax = 0;
|
||
| ... | ... | |
|
foreach my $umlaut (keys(%umlaute)) {
|
||
|
$transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
|
||
|
$transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
|
||
|
$transaction->[$haben]->{'departmentname'} =~ 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]->{'invnumber'} = substr($transaction->[$haben]->{'invnumber'}, 0, 12);
|
||
|
$transaction->[$haben]->{'departmentname'} = substr($transaction->[$haben]->{'departmentname'}, 0, 8);
|
||
|
$transaction->[$haben]->{'department_id'} = substr($transaction->[$haben]->{'department_id'}, 0, 8);
|
||
|
$transaction->[$haben]->{'name'} = substr($transaction->[$haben]->{'name'}, 0, 30);
|
||
|
$transaction->[$haben]->{'invnumber'} =~ s/\ *$//;
|
||
|
$transaction->[$haben]->{'name'} =~ s/\ *$//;
|
||
|
if ($trans_lines >= 2) {
|
||
|
# sammelkonto prüfen und überschreiben (Personenkonten == Kredit/Debitnummer)
|
||
|
# TODO besserer check auf sammelkonto (nicht stumpfsinnig über kontorahmen)
|
||
|
if ($transaction->[$haben]->{'accno'} =~ m/^(1400|1600)$/) {
|
||
|
$buchungstext = "\x1E" . $transaction->[$haben]->{'accno'} . "\x1C";
|
||
|
$transaction->[$haben]->{'accno'} = $transaction->[$haben]->{'vcnumber'};
|
||
|
}elsif ($transaction->[$soll]->{'accno'} =~ m/^(1400|1600)$/) {
|
||
|
$buchungstext = "\x1E" . $transaction->[$soll]->{'accno'} . "\x1C";
|
||
|
$transaction->[$soll]->{'accno'} = $transaction->[$soll]->{'vcnumber'};
|
||
|
}
|
||
|
$gegenkonto = "a" . trim_leading_zeroes($transaction->[$haben]->{'accno'});
|
||
|
$konto = "e" . trim_leading_zeroes($transaction->[$soll]->{'accno'});
|
||
|
if ($transaction->[$haben]->{'invnumber'} ne "") {
|
||
| ... | ... | |
|
$waehrung = "\xB3" . "EUR" . "\x1C";
|
||
|
if ($transaction->[$haben]->{'name'} ne "") {
|
||
|
$buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C";
|
||
|
$kostenstelle1 = "\xBB" . $transaction->[$haben]->{departmentname} . "\x1C";
|
||
|
$kostenstelle2 = "\xBC" . $transaction->[$haben]->{'department_id'} . "\x1C";
|
||
|
}
|
||
|
if (($transaction->[$haben]->{'ustid'} // '') ne "") {
|
||
|
$ustid = "\xBA" . $transaction->[$haben]->{'ustid'} . "\x1C";
|
||
| ... | ... | |
|
$kne_file->add_block($gegenkonto);
|
||
|
$kne_file->add_block($belegfeld1);
|
||
|
$kne_file->add_block($belegfeld2);
|
||
|
$kne_file->add_block($kostenstelle1);
|
||
|
$kne_file->add_block($kostenstelle2);
|
||
|
$kne_file->add_block($datum);
|
||
|
$kne_file->add_block($konto);
|
||
|
$kne_file->add_block($buchungstext);
|
||