Revision 76c486e3
Von Sven Schöling vor etwa 15 Jahren hinzugefügt
| sql/Pg-upgrade2/USTVA_at.pl | ||
|---|---|---|
| 2 | 2 |
# @description: USTVA Report Daten fuer Oesterreich. Vielen Dank an Gerhard Winkler.. |
| 3 | 3 |
# @depends: USTVA_abstraction |
| 4 | 4 |
|
| 5 |
unless ( $main::form ) {
|
|
| 5 |
use strict; |
|
| 6 |
|
|
| 7 |
unless ( $main::form ) {
|
|
| 6 | 8 |
die("This script cannot be run from the command line.");
|
| 7 | 9 |
} |
| 8 | 10 |
|
| 11 |
# import vars from caller |
|
| 12 |
our ($dbup_locale, $dbup_myconfig, $dbh, $iconv); |
|
| 13 |
|
|
| 9 | 14 |
if ( check_coa('Austria') ){
|
| 10 | 15 |
|
| 11 | 16 |
if ( coa_is_empty() ) {
|
| 12 | 17 |
print qq|Eine leere Datenbank mit Kontenrahmen ?sterreich vorgefunden. <br /> |
| 13 | 18 |
Die Aktualisierungen werden eingespielt...<br /> |
| 14 | 19 |
<b>Achtung: Dieses Update ist ungetestet und bedarf weiterer Konfiguration</b>|; |
| 15 |
|
|
| 20 |
|
|
| 16 | 21 |
return 1 |
| 17 |
&& clear_tables(( 'tax.report_variables', 'tax.report_headings',
|
|
| 18 |
'tax.report_categorys', 'taxkeys',
|
|
| 22 |
&& clear_tables(( 'tax.report_variables', 'tax.report_headings', |
|
| 23 |
'tax.report_categorys', 'taxkeys', |
|
| 19 | 24 |
'tax', 'chart', |
| 20 | 25 |
'buchungsgruppen', |
| 21 | 26 |
)) |
| ... | ... | |
| 25 | 30 |
&& do_insert_taxkeys() |
| 26 | 31 |
&& do_insert_buchungsgruppen() |
| 27 | 32 |
; |
| 28 |
}
|
|
| 33 |
} |
|
| 29 | 34 |
else {
|
| 30 |
print qq|Eine ?sterreichische Datenbank in der bereits Buchungss?tze enthalten sind, kann nicht aktualisiert werden.<br />
|
|
| 35 |
print qq|Eine ?sterreichische Datenbank in der bereits Buchungss?tze enthalten sind, kann nicht aktualisiert werden.<br /> |
|
| 31 | 36 |
Bitte eine neue Datenbank mit Kontenrahmen 'Austria' anlegen.|; |
| 32 | 37 |
return 1; |
| 33 | 38 |
} |
| ... | ... | |
| 76 | 81 |
} |
| 77 | 82 |
|
| 78 | 83 |
sub check_coa {
|
| 79 |
|
|
| 84 |
|
|
| 80 | 85 |
my ( $want_coa ) = @_; |
| 81 |
|
|
| 86 |
|
|
| 82 | 87 |
my $query = q{ SELECT count(*) FROM defaults WHERE coa = ? };
|
| 83 |
my ($have_coa) = selectrow_query($form, $dbh, $query, $want_coa); |
|
| 88 |
my ($have_coa) = selectrow_query($main::form, $dbh, $query, $want_coa);
|
|
| 84 | 89 |
|
| 85 | 90 |
return $have_coa; |
| 86 | 91 |
|
| 87 | 92 |
} |
| 88 | 93 |
|
| 89 | 94 |
sub coa_is_empty {
|
| 90 |
|
|
| 91 |
my $query = q{ SELECT count(*)
|
|
| 95 |
|
|
| 96 |
my $query = q{ SELECT count(*)
|
|
| 92 | 97 |
FROM ar, ap, gl, invoice, acc_trans, customer, vendor, parts |
| 93 | 98 |
}; |
| 94 |
my ($empty) = selectrow_query($form, $dbh, $query); |
|
| 99 |
my ($empty) = selectrow_query($main::form, $dbh, $query);
|
|
| 95 | 100 |
|
| 96 | 101 |
$empty = !$empty; |
| 97 | 102 |
|
| ... | ... | |
| 106 | 111 |
"INSERT INTO tax.report_headings (id, category_id, type, description, subdescription) VALUES (0, 0, NULL, NULL, NULL)", |
| 107 | 112 |
); |
| 108 | 113 |
|
| 109 |
map({ do_query($_); } @queries);
|
|
| 114 |
map({ do_query($_); } @queries);
|
|
| 110 | 115 |
|
| 111 | 116 |
|
| 112 | 117 |
my @copy_statements = ( |
| 113 | 118 |
"INSERT INTO tax.report_variables (id, position, heading_id, description, dec_places, valid_from) VALUES (?, ?, ?, ?, ?, ?)", |
| 114 | 119 |
); |
| 115 | 120 |
|
| 116 |
|
|
| 121 |
|
|
| 117 | 122 |
my @copy_data = ( |
| 118 |
[
|
|
| 123 |
[ |
|
| 119 | 124 |
"1;000;0;a) Gesamtbetrag der Bemessungsgrundlage f?r Lieferungen und sonstige Leistungen (ohne den nachstehend angef?hrten Eigenverbrauch) einschlie?lich Anzahlungen (jeweils ohne Umsatzsteuer);2;1970-01-01", |
| 120 | 125 |
"2;001;0;zuz?glich Eigenverbrauch (?1 Abs. 1 Z 2, ? 3 Abs. 2 und ? 3a Abs. 1a);2;1970-01-01", |
| 121 | 126 |
"3;021;0;abz?glich Ums?tze f?r die die Steuerschuld gem?? ? 19 Abs. 1 zweiter Satz sowie gem?? ? 19 Abs. 1a, Abs. 1b, Abs. 1c auf den Leistungsempf?nger ?bergegangen ist.;2;1970-01-01", |
| ... | ... | |
| 355 | 360 |
return 1; |
| 356 | 361 |
} |
| 357 | 362 |
sub do_insert_tax {
|
| 358 |
|
|
| 363 |
|
|
| 359 | 364 |
my @copy_statements = ( |
| 360 | 365 |
"INSERT INTO tax (chart_id, taxnumber, taxkey, taxdescription, itime, mtime, rate, id) VALUES (65, '2510', 7, 'Vorsteuer 10%', '2006-01-30 11:08:23.332857', '2006-02-08 20:28:09.63567', 0.10000, 173);", |
| 361 | 366 |
"INSERT INTO tax (chart_id, taxnumber, taxkey, taxdescription, itime, mtime, rate, id) VALUES (64, '2512', 8, 'Vorsteuer 12%', '2006-02-02 17:39:18.535036', '2006-02-08 20:28:21.463869', 0.12000, 174);", |
| ... | ... | |
| 366 | 371 |
"INSERT INTO tax (chart_id, taxnumber, taxkey, taxdescription, itime, mtime, rate, id) VALUES (NULL, NULL, 10, 'Im anderen EG-Staat steuerpfl. Lieferung', '2006-01-30 11:08:23.332857', '2006-02-08 12:45:36.44088', NULL, 171);", |
| 367 | 372 |
"INSERT INTO tax (chart_id, taxnumber, taxkey, taxdescription, itime, mtime, rate, id) VALUES (NULL, NULL, 11, 'Steuerfreie EG-Lief. an Abn. mit UStIdNr', '2006-01-30 11:08:23.332857', '2006-02-08 12:45:36.44088', NULL, 172);", |
| 368 | 373 |
"INSERT INTO tax (chart_id, taxnumber, taxkey, taxdescription, itime, mtime, rate, id) VALUES (NULL, NULL, 0, 'Keine Steuer', '2006-01-30 11:08:23.332857', '2006-02-08 12:45:36.44088', 0.00000, 0);", |
| 369 |
|
|
| 374 |
|
|
| 370 | 375 |
); |
| 371 | 376 |
|
| 372 | 377 |
for my $statement ( 0 .. $#copy_statements ) {
|
| ... | ... | |
| 378 | 383 |
} |
| 379 | 384 |
|
| 380 | 385 |
sub do_insert_taxkeys {
|
| 381 |
|
|
| 386 |
|
|
| 382 | 387 |
my @copy_statements = ( |
| 383 | 388 |
"INSERT INTO taxkeys VALUES (230, 69, 177, 2, NULL, '1970-01-01');", |
| 384 | 389 |
"INSERT INTO taxkeys VALUES (231, 72, 178, 3, NULL, '1970-01-01');", |
| ... | ... | |
| 476 | 481 |
"UPDATE taxkeys SET pos_ustva='017' WHERE chart_id IN (SELECT id FROM chart WHERE accno IN ('4015', '4025', '4035', '4045', '4315', '4325', '4335', '4345'));",
|
| 477 | 482 |
"UPDATE taxkeys SET pos_ustva='022' WHERE chart_id IN (SELECT id FROM chart WHERE accno IN ('4040', '4045'));",
|
| 478 | 483 |
"UPDATE taxkeys SET pos_ustva='122' WHERE chart_id IN (SELECT id FROM chart WHERE accno IN ('3520'));",
|
| 479 |
"UPDATE taxkeys SET pos_ustva='029' WHERE chart_id IN (SELECT id FROM chart WHERE accno IN ('4010', '4015'));",
|
|
| 484 |
"UPDATE taxkeys SET pos_ustva='029' WHERE chart_id IN (SELECT id FROM chart WHERE accno IN ('4010', '4015'));",
|
|
| 480 | 485 |
"UPDATE taxkeys SET pos_ustva='129' WHERE chart_id IN (SELECT id FROM chart WHERE accno IN ('3510'));",
|
| 481 | 486 |
"UPDATE taxkeys SET pos_ustva='025' WHERE chart_id IN (SELECT id FROM chart WHERE accno IN ('4012'));",
|
| 482 | 487 |
"UPDATE taxkeys SET pos_ustva='125' WHERE chart_id IN (SELECT id FROM chart WHERE accno IN ('3512'));",
|
Auch abrufbar als: Unified diff
Und wieder ein Schwung strict.