| 372 |
372 |
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');
|
| 373 |
373 |
|
| 374 |
374 |
my $query =
|
| 375 |
|
qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey,
|
|
375 |
qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ar.department_id,
|
| 376 |
376 |
ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate,
|
| 377 |
|
ct.name, ct.ustid,
|
|
377 |
ct.name, ct.ustid, ct.customernumber as vcnumber,
|
|
378 |
d.description as departmentname,
|
| 378 |
379 |
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
| 379 |
380 |
ar.invoice,
|
| 380 |
381 |
t.rate AS taxrate
|
| ... | ... | |
| 383 |
384 |
LEFT JOIN customer ct ON (ar.customer_id = ct.id)
|
| 384 |
385 |
LEFT JOIN chart c ON (ac.chart_id = c.id)
|
| 385 |
386 |
LEFT JOIN tax t ON (ac.tax_id = t.id)
|
|
387 |
LEFT JOIN department d ON (ar.department_id = d.id)
|
| 386 |
388 |
WHERE (ar.id IS NOT NULL)
|
| 387 |
389 |
AND $fromto
|
| 388 |
390 |
$trans_id_filter
|
| ... | ... | |
| 390 |
392 |
|
| 391 |
393 |
UNION ALL
|
| 392 |
394 |
|
| 393 |
|
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey,
|
|
395 |
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ap.department_id,
|
| 394 |
396 |
ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate,
|
| 395 |
|
ct.name,ct.ustid,
|
|
397 |
ct.name,ct.ustid, ct.vendornumber as vcnumber,
|
|
398 |
d.description as departmentname,
|
| 396 |
399 |
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
| 397 |
400 |
ap.invoice,
|
| 398 |
401 |
t.rate AS taxrate
|
| ... | ... | |
| 401 |
404 |
LEFT JOIN vendor ct ON (ap.vendor_id = ct.id)
|
| 402 |
405 |
LEFT JOIN chart c ON (ac.chart_id = c.id)
|
| 403 |
406 |
LEFT JOIN tax t ON (ac.tax_id = t.id)
|
|
407 |
LEFT JOIN department d ON (ap.department_id = d.id)
|
| 404 |
408 |
WHERE (ap.id IS NOT NULL)
|
| 405 |
409 |
AND $fromto
|
| 406 |
410 |
$trans_id_filter
|
| ... | ... | |
| 408 |
412 |
|
| 409 |
413 |
UNION ALL
|
| 410 |
414 |
|
| 411 |
|
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey,
|
|
415 |
SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, gl.department_id,
|
| 412 |
416 |
gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate,
|
| 413 |
|
gl.description AS name, NULL as ustid,
|
|
417 |
gl.description AS name, NULL as ustid, NULL as vcnumber,
|
|
418 |
d.description as departmentname,
|
| 414 |
419 |
c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
|
| 415 |
420 |
FALSE AS invoice,
|
| 416 |
421 |
t.rate AS taxrate
|
| ... | ... | |
| 418 |
423 |
LEFT JOIN gl ON (ac.trans_id = gl.id)
|
| 419 |
424 |
LEFT JOIN chart c ON (ac.chart_id = c.id)
|
| 420 |
425 |
LEFT JOIN tax t ON (ac.tax_id = t.id)
|
|
426 |
LEFT JOIN department d ON (gl.department_id = d.id)
|
| 421 |
427 |
WHERE (gl.id IS NOT NULL)
|
| 422 |
428 |
AND $fromto
|
| 423 |
429 |
$trans_id_filter
|
| ... | ... | |
| 829 |
835 |
my $umsatz = 0;
|
| 830 |
836 |
my $gegenkonto = "";
|
| 831 |
837 |
my $konto = "";
|
| 832 |
|
my $belegfeld1 = "";
|
| 833 |
838 |
my $datum = "";
|
| 834 |
839 |
my $waehrung = "";
|
| 835 |
840 |
my $buchungstext = "";
|
| 836 |
|
my $belegfeld2 = "";
|
|
841 |
my $kostenstelle1 = ""; # Kost1=8,a,X'bb',K,J,"",N
|
|
842 |
my $kostenstelle2 = ""; # Kost2=8,a,X'bc',K,J,"",N
|
|
843 |
my $belegfeld1 = ""; # Belegfeld1=12,a,X'bd',K,J,"",N
|
|
844 |
my $belegfeld2 = ""; # Belegfeld2=12,a,X'be',K,J,"",N
|
| 837 |
845 |
my $datevautomatik = 0;
|
| 838 |
846 |
my $taxkey = 0;
|
| 839 |
847 |
my $charttax = 0;
|
| ... | ... | |
| 876 |
884 |
foreach my $umlaut (keys(%umlaute)) {
|
| 877 |
885 |
$transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
|
| 878 |
886 |
$transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
|
|
887 |
$transaction->[$haben]->{'departmentname'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
|
| 879 |
888 |
}
|
| 880 |
889 |
|
| 881 |
890 |
$transaction->[$haben]->{'invnumber'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
|
| 882 |
891 |
$transaction->[$haben]->{'name'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
|
| 883 |
892 |
|
| 884 |
893 |
$transaction->[$haben]->{'invnumber'} = substr($transaction->[$haben]->{'invnumber'}, 0, 12);
|
|
894 |
$transaction->[$haben]->{'departmentname'} = substr($transaction->[$haben]->{'departmentname'}, 0, 8);
|
|
895 |
$transaction->[$haben]->{'department_id'} = substr($transaction->[$haben]->{'department_id'}, 0, 8);
|
| 885 |
896 |
$transaction->[$haben]->{'name'} = substr($transaction->[$haben]->{'name'}, 0, 30);
|
| 886 |
897 |
$transaction->[$haben]->{'invnumber'} =~ s/\ *$//;
|
| 887 |
898 |
$transaction->[$haben]->{'name'} =~ s/\ *$//;
|
| 888 |
899 |
|
| 889 |
900 |
if ($trans_lines >= 2) {
|
| 890 |
901 |
|
|
902 |
# sammelkonto prüfen und überschreiben (Personenkonten == Kredit/Debitnummer)
|
|
903 |
# TODO besserer check auf sammelkonto (nicht stumpfsinnig über kontorahmen)
|
|
904 |
if ($transaction->[$haben]->{'accno'} =~ m/^(1400|1600)$/) {
|
|
905 |
$buchungstext = "\x1E" . $transaction->[$haben]->{'accno'} . "\x1C";
|
|
906 |
$transaction->[$haben]->{'accno'} = $transaction->[$haben]->{'vcnumber'};
|
|
907 |
}elsif ($transaction->[$soll]->{'accno'} =~ m/^(1400|1600)$/) {
|
|
908 |
$buchungstext = "\x1E" . $transaction->[$soll]->{'accno'} . "\x1C";
|
|
909 |
$transaction->[$soll]->{'accno'} = $transaction->[$soll]->{'vcnumber'};
|
|
910 |
}
|
|
911 |
|
|
912 |
|
| 891 |
913 |
$gegenkonto = "a" . trim_leading_zeroes($transaction->[$haben]->{'accno'});
|
| 892 |
914 |
$konto = "e" . trim_leading_zeroes($transaction->[$soll]->{'accno'});
|
| 893 |
915 |
if ($transaction->[$haben]->{'invnumber'} ne "") {
|
| ... | ... | |
| 898 |
920 |
$waehrung = "\xB3" . "EUR" . "\x1C";
|
| 899 |
921 |
if ($transaction->[$haben]->{'name'} ne "") {
|
| 900 |
922 |
$buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C";
|
|
923 |
$kostenstelle1 = "\xBB" . $transaction->[$haben]->{departmentname} . "\x1C";
|
|
924 |
$kostenstelle2 = "\xBC" . $transaction->[$haben]->{'department_id'} . "\x1C";
|
|
925 |
|
| 901 |
926 |
}
|
| 902 |
927 |
if (($transaction->[$haben]->{'ustid'} // '') ne "") {
|
| 903 |
928 |
$ustid = "\xBA" . $transaction->[$haben]->{'ustid'} . "\x1C";
|
| ... | ... | |
| 930 |
955 |
$kne_file->add_block($gegenkonto);
|
| 931 |
956 |
$kne_file->add_block($belegfeld1);
|
| 932 |
957 |
$kne_file->add_block($belegfeld2);
|
|
958 |
$kne_file->add_block($kostenstelle1);
|
|
959 |
$kne_file->add_block($kostenstelle2);
|
| 933 |
960 |
$kne_file->add_block($datum);
|
| 934 |
961 |
$kne_file->add_block($konto);
|
| 935 |
962 |
$kne_file->add_block($buchungstext);
|
| 936 |
|
-
|