package USTVA;
+use SL::DBUtils;
+
+sub get_coa {
+
+ my ( $self, $form, $myconfig) = @_;
+
+ my $query = q{ SELECT coa FROM defaults };
+
+ my $dbh = $form->dbconnect($myconfig);
+ my $sth = $dbh->prepare($query);
+ $sth->execute() || $form->dberror($query);
+
+ my ($coa) = selectrow_query($form, $dbh, $query);
+
+ $sth->finish;
+ $dbh->disconnect;
+
+ $form->{coa} = $coa;
+ $form->{"COA_$coa"} = '1';
+ $form->{COA_Germany} = '1' if ( $coa eq 'Germany-DATEV-SKR03EU' or $coa eq 'Germany-DATEV-SKR04EU');
+
+ return;
+}
+
+
+sub report_variables {
+ # Get all positions for taxreport out of the database
+ # Needs Databaseupdate Pg-upgrade2/USTVA_abstraction.pl
+
+ return unless defined wantarray;
+
+ my ( $self,
+ $arg_ref) = @_;
+
+ my $myconfig = $arg_ref->{myconfig};
+ my $form = $arg_ref->{form};
+ my $type = $arg_ref->{type}; # 'paied' || 'received' || ''
+ my $attribute = $arg_ref->{attribute}; #
+ my $dec_places = (defined $arg_ref->{dec_places}) ? $arg_ref->{dec_places}:undef;
+
+ my $where_type = "AND tax.report_headings.type = '$type'" if ( $type );
+ my $where_dcp = "AND tax.report_variables.dec_places = '$dec_places'" if ( defined $dec_places );
+
+ my $query = qq|
+ SELECT $attribute
+ FROM tax.report_variables
+ LEFT JOIN tax.report_headings
+ ON (tax.report_variables.heading_id = tax.report_headings.id)
+ WHERE 1=1
+ $where_type
+ $where_dcp
+ |;
+
+ my $dbh = $form->dbconnect($myconfig);
+ my $sth = $dbh->prepare($query);
+
+ $sth->execute() || $form->dberror($query);
+
+ my @positions;
+
+ while ( my $row_ref = $sth->fetchrow_arrayref() ) {
+ push @positions, @$row_ref; # Copy the array contents
+ }
+
+ $sth->finish;
+
+ $dbh->disconnect;
+
+ return @positions;
+
+}
+
sub create_steuernummer {
$main::lxdebug->enter_sub();
my $h = 0;
my $i = 0;
- $steuernummer_new = $part;
- $elstersteuernummer_new = $elster_FFFF;
+ $steuernummer_new = $part;
+ $elstersteuernummer_new = $elster_FFFF;
$elstersteuernummer_new .= '0';
for ($h = 1; $h < $patterncount; $h++) {
} else {
- if ($form->{error_function}) {
- &{ $form->{error_function} }($msg);
- } else {
- die "Hinweis: $msg\n";
- }
+ die "Hinweis: $msg\n";
}
$main::lxdebug->leave_sub();
# return unless (-f $filename);
- open(FH, "$filename") or $form->error("$filename : $!\n");
+ open my $FH, "<", "$filename" or $form->error("$filename : $!\n");
my $query = "";
my $sth;
my @quote_chars;
- while (<FH>) {
+ while (<$FH>) {
# Remove DOS and Unix style line endings.
s/[\r\n]//g;
}
}
- close FH;
+ close $FH;
$main::lxdebug->leave_sub();
}
my $last_period = 0;
my $category = "pos_ustva";
- my @category_cent = qw(
- 511 861 36 80 971 931 98 96 53 74
- 85 65 66 61 62 67 63 64 59 69
- 39 83 811 891 Z43 Z45 Z53 Z62 Z65 Z67
- );
- my @category_euro = qw(
- 41 44 49 43 48 51
- 86 35 77 76 91 97
- 93 95 94 42 60 45
- 52 73 84 81 89
- );
+ my @category_cent = USTVA->report_variables({
+ myconfig => $myconfig,
+ form => $form,
+ type => '',
+ attribute => 'position',
+ dec_places => '2',
+ });
+
+ push @category_cent, qw(83 Z43 Z45 Z53 Z62 Z65 Z67);
+
+ my @category_euro = USTVA->report_variables({
+ myconfig => $myconfig,
+ form => $form,
+ type => '',
+ attribute => 'position',
+ dec_places => '0',
+ });
+
+ push @category_euro, USTVA->report_variables({
+ myconfig => $myconfig,
+ form => $form,
+ type => '',
+ attribute => 'position',
+ dec_places => '0',
+ });
$form->{decimalplaces} *= 1;
foreach $item (@category_euro) {
$form->{"$item"} = 0;
}
+ my $coa_name = coa_get($dbh);
+ $form->{coa} = $coa_name;
+
+ # Controlvariable for templates
+ $form->{"$coa_name"} = '1';
+
+ $main::lxdebug->message(LXDebug::DEBUG2, "COA: '$form->{coa}', \$form->{$coa_name} = 1");
&get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
$form, $category);
+ ###########################################
+ #
+ # Nationspecific Modfications
+ #
+ ###########################################
+
+ # Germany
+
+ if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU'){
+
+ # 16%/19% Umstellung
+ # Umordnen der Kennziffern
+ if ( $form->{year} < 2007) {
+ $form->{35} += $form->{81};
+ $form->{36} += $form->{811};
+ $form->{95} += $form->{89};
+ $form->{98} += $form->{891};
+ map { delete $form->{$_} } qw(81 811 89 891);
+ } else {
+ $form->{35} += $form->{51};
+ $form->{36} += $form->{511};
+ $form->{95} += $form->{97};
+ $form->{98} += $form->{971};
+ map { delete $form->{$_} } qw(51 511 97 971);
+ }
- # 16%/19% Umstellung
- # Umordnen der Kennziffern
- if ( $form->{year} < 2007) {
- $form->{35} += $form->{81};
- $form->{36} += $form->{811};
- $form->{95} += $form->{89};
- $form->{98} += $form->{891};
- map { delete $form->{$_} } qw(81 811 89 891);
- } else {
- $form->{35} += $form->{51};
- $form->{36} += $form->{511};
- $form->{95} += $form->{97};
- $form->{98} += $form->{971};
- map { delete $form->{$_} } qw(51 511 97 971);
}
+ # Fixme: Wird auch noch für Oesterreich gebraucht,
+ # weil kein eigenes Ausgabeformular
+ # sotte aber aus der allgeméinen Steuerberechnung verschwinden
#
# Berechnung der USTVA Formularfelder laut Bogen 207
#
$main::lxdebug->leave_sub();
}
+sub coa_get {
+
+ my ($dbh) = @_;
+
+ my $query= qq|SELECT coa FROM defaults|;
+
+ my $sth = $dbh->prepare($query);
+
+ $sth->execute || $form->dberror($query);
+
+ ($ref) = $sth->fetchrow_array;
+
+ return $ref;
+
+};
+
sub get_accounts_ustva {
$main::lxdebug->enter_sub();
my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
- my ($null, $department_id) = split /--/, $form->{department};
-
my $query;
- my $dpt_where;
- my $dpt_join;
- my $project;
my $where = "";
my $glwhere = "";
my $subwhere = "";
my $APwhere = '';
my $arwhere = "";
my $item;
- my $gltaxkey_where = "(tk.pos_ustva>=59 AND tk.pos_ustva<=66)";
+
+ my $gltaxkey_where = "(tk.pos_ustva>=59 AND tk.pos_ustva<=66)";
if ($fromdate) {
if ($form->{method} eq 'cash') {
if ($todate) {
$where .= " AND ac.transdate <= '$todate'";
$ARwhere .= " AND acc.transdate <= '$todate'";
- $subwhere .= " AND transdate <= '$todate'";
- $APwhere .= " AND AP.transdate <= '$todate'";
}
- if ($department_id) {
- $dpt_join = qq|
- JOIN department t ON (a.department_id = t.id)
- |;
- $dpt_where = qq|
- AND t.id = $department_id
- |;
- }
+ my $acc_trans_where = '1=1';
+ if ($fromdate || $todate) {
+ $acc_trans_where = "ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE ";
- if ($form->{project_id}) {
- $project = qq|
- AND ac.project_id = $form->{project_id}
- |;
+ if ($fromdate) {
+ $acc_trans_where .= "transdate >= '$fromdate'";
+ }
+ if ($todate) {
+ $acc_trans_where .= " AND " if ($fromdate);
+ $acc_trans_where .= "transdate <= '$todate'";
+ }
+
+ $acc_trans_where .= ")";
}
+
############################################
# Method eq 'cash' = IST Versteuerung
############################################
)
)
WHERE
- 1=1
- -- Here no where, please. All Transactions ever should be
- -- testet if they are paied in the USTVA report period.
+ $acc_trans_where
GROUP BY tk.pos_ustva
|;
# Method eq 'accrual' = Soll Versteuerung
#########################################
- if ($department_id) {
- $dpt_join = qq|
- JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
- |;
- $dpt_where = qq|
- AND t.department_id = $department_id
- |;
- }
-
-
$query = qq|
-- Alle Einnahmen AR und pos_ustva erfassen
SELECT
$dpt_join
WHERE 1 = 1
$where
- $dpt_where
- $project
GROUP BY tk.pos_ustva
|;
} else {
- $self->error("Unknown tax method: $form->{method}")
+ $form->error("Unknown tax method: $form->{method}")
}
sum(ac.amount) AS amount,
tk.pos_ustva
FROM acc_trans ac
- JOIN AP ON (AP.id = ac.trans_id )
+ JOIN ap ON (ap.id = ac.trans_id )
JOIN chart c ON (c.id = ac.chart_id)
LEFT JOIN taxkeys tk ON (
tk.id = (
SELECT id FROM taxkeys
- WHERE chart_id=ac.chart_id
- --AND taxkey_id=ac.taxkey
+ WHERE 1=1
+ AND chart_id=ac.chart_id
+ --AND taxkey_id = ac.taxkey
AND startdate <= COALESCE(AP.transdate)
ORDER BY startdate DESC LIMIT 1
)
)
WHERE
1=1
- $APwhere
- $dpt_where
- $project
+ $where
GROUP BY tk.pos_ustva
UNION -- Einnahmen direkter gl Buchungen erfassen
tk.id = (
SELECT id FROM taxkeys
WHERE chart_id=ac.chart_id
- --AND taxkey_id=ac.taxkey
AND NOT $gltaxkey_where
AND startdate <= COALESCE(ac.transdate)
ORDER BY startdate DESC LIMIT 1
$dpt_join
WHERE 1 = 1
$where
- $dpt_from
- $project
GROUP BY tk.pos_ustva
tk.id = (
SELECT id FROM taxkeys
WHERE chart_id=ac.chart_id
- --AND taxkey_id=ac.taxkey
AND $gltaxkey_where
AND startdate <= COALESCE(ac.transdate)
ORDER BY startdate DESC LIMIT 1
$dpt_join
WHERE 1 = 1
$where
- $dpt_from
- $project
GROUP BY tk.pos_ustva
|;
$sth->execute || $form->dberror($query);
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
# Bug 365 solved?!
$ref->{amount} *= -1;
- if ($category eq "pos_bwa") {
- if ($last_period) {
- $form->{ $ref->{$category} }{kumm} += $ref->{amount};
- } else {
- $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
- }
- } else {
- $form->{ $ref->{$category} } += $ref->{amount};
- }
+ $form->{ $ref->{$category} } += $ref->{amount};
}
$sth->finish;
}
+sub get_config {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $userspath, $filename) = @_;
+
+ $form->error("Missing Parameter: @_") if !$userspath || !$filename;
+
+ my $form = $main::form;
+
+ $filename = "$form->{login}_$filename";
+ $filename =~ s|.*/||;
+ $filename = "$userspath/$filename";
+ open my $FACONF, "<", $filename or sub {# Annon Sub
+ # catch open error
+ # create file if file does not exist
+ open my $FANEW, ">", $filename or $form->error("CREATE: $filename : $!");
+ close $FANEW or $form->error("CLOSE: $filename : $!");
+
+ #try again open file
+ open my $FACONF, "<", $filename or $form->error("OPEN: $filename : $!");
+ };
+
+ while (<$FACONF>) {
+ last if (/^\[/);
+ next if (/^(\#|\s)/);
+
+ # remove comments
+ s/\s#.*//g;
+
+ # remove any trailing whitespace
+ s/^\s*(.*?)\s*$/$1/;
+ my ($key, $value) = split(/=/, $_, 2);
+
+ $form->{$key} = "$value";
+
+ }
+
+ close $FACONF;
+
+ $main::lxdebug->leave_sub();
+}
1;
projects / kivitendo-erp.git / blobdiff