<input type=hidden name="patterncount" value="$patterncount">
<input type=hidden name="patternlength" value="$patterncount">
<input type=hidden name="delimiter" value="$delimiter">
- <input type=hidden name="part" value="$part">
+ <input type=hidden name="part" value="$part">
|;
my $h = 0;
my $i = 0;
#}
#if ( $terminal eq 'mozilla' or $terminal eq 'js' ) {
- print qq|
+ print qq|
<br>
<script language="Javascript">
function update_auswahl()
$elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
}
foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
- keys(%elster_land_fa)) {
+ keys(%elster_land_fa)
+ ) {
print qq|
elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
$j++;
print qq|
}|;
}
- print qq|
+ print qq|
}
</script>
print qq|
<tr>
<td>Finanzamt
- </td>
+ </td>
<td>
<select size="1" name="elsterFFFF_new">|;
if ($elsterFFFF eq '') {
print qq|<option value="Auswahl" $checked>hier auswählen...</option>|;
} else {
foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
- keys(%elster_land_fa)) {
+ keys(%elster_land_fa)
+ ) {
print qq|
<option value="$ffff"|;
print qq|
</td>
</tr>
- </table>
+ </table>
</select>|;
$main::lxdebug->leave_sub();
if ($DBI::err) {
#There is no table, read the table from sql/finanzamt.sql
- print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
+ print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
$myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...</p>|;
process_query($form, $dbh, $filename) || $self->error(DBI->errstr);
$main::lxdebug->leave_sub();
}
-1;
+sub ustva {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
+
+ my $last_period = 0;
+ my $category = "pos_ustva";
+ my @categories_cent = qw(511 861 36 80 971 931 98 96 53 74
+ 85 65 66 61 62 67 63 64 59 69 39 83
+ Z43 Z45 Z53 Z62 Z65 Z67);
+
+ my @categories_euro = qw(41 44 49 43 48 51 86 35 77 76 91 97 93
+ 95 94 42 60 45 52 73 84);
+
+ $form->{decimalplaces} *= 1;
+
+ foreach $item (@categories_cent) {
+ $form->{"$item"} = 0;
+ }
+ foreach $item (@categories_euro) {
+ $form->{"$item"} = 0;
+ }
+
+ &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
+ $form, $category);
+
+ #
+ # Berechnung der USTVA Formularfelder
+ #
+ $form->{"51r"} = $form->{"511"};
+ $form->{"86r"} = $form->{"861"};
+ $form->{"97r"} = $form->{"971"};
+ $form->{"93r"} = $form->{"931"};
+ $form->{"Z43"} =
+ $form->{"511"} + $form->{"861"} + $form->{"36"} + $form->{"80"} +
+ $form->{"971"} + $form->{"931"} + $form->{"96"} + $form->{"98"};
+ $form->{"Z45"} = $form->{"Z43"};
+ $form->{"Z53"} = $form->{"Z43"};
+ $form->{"Z62"} =
+ $form->{"Z43"} - $form->{"66"} - $form->{"61"} - $form->{"62"} -
+ $form->{"63"} - $form->{"64"} - $form->{"59"};
+ $form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
+ $form->{"83"} = $form->{"Z65"} - $form->{"39"};
+
+ foreach $item (@categories_cent) {
+ $form->{$item} =
+ $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),
+ 2, '0');
+ }
+
+ foreach $item (@categories_euro) {
+ $form->{$item} =
+ $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0),
+ 0, '0');
+ }
+
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+}
+
+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 = "1 = 1";
+ my $glwhere = "";
+ my $subwhere = "";
+ my $ARwhere = "";
+ my $arwhere = "";
+ my $item;
+
+ if ($fromdate) {
+ if ($form->{method} eq 'cash') {
+ $subwhere .= " AND transdate >= '$fromdate'";
+ $glwhere = " AND ac.transdate >= '$fromdate'";
+ $ARwhere .= " AND acc.transdate >= '$fromdate'";
+ }
+ $where .= " AND ac.transdate >= '$fromdate'";
+ }
+
+ if ($todate) {
+ $where .= " AND ac.transdate <= '$todate'";
+ $ARwhere .= " AND acc.transdate <= '$todate'";
+ $subwhere .= " AND 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
+ |;
+ }
+
+ if ($form->{project_id}) {
+ $project = qq|
+ AND ac.project_id = $form->{project_id}
+ |;
+ }
+
+ if ($form->{method} eq 'cash') {
+
+ $query = qq|
+
+ SELECT
+ SUM( ac.amount *
+ -- Bezahlt / Rechnungssumme
+ (
+ SELECT SUM(acc.amount)
+ FROM acc_trans acc
+ INNER JOIN chart c ON (acc.chart_id = c.id AND c.link like '%AR_paid%')
+ WHERE
+ 1=1
+ $ARwhere
+ AND acc.trans_id = ac.trans_id
+ )
+ /
+ (
+ select amount from ar where id = ac.trans_id
+ )
+ ) AS amount,
+ c.pos_ustva
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ --JOIN ar ON (ar.id = ac.trans_id)
+ where
+ 1=1
+ -- Here no where, please. All Transactions ever should be
+ -- testet if they are paied in the USTVA report period.
+ GROUP BY c.pos_ustva
+ UNION
+
+ SELECT sum(ac.amount) AS amount,
+ c.$category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ap a ON (a.id = ac.trans_id)
+ $dpt_join
+ WHERE $where
+ $dpt_where
+ AND ac.trans_id IN
+ (
+ SELECT trans_id
+ FROM acc_trans
+ JOIN chart ON (chart_id = id)
+ WHERE link LIKE '%AP_amount%'
+ $subwhere
+ )
+
+ $project
+ GROUP BY c.$category
+
+ UNION
+
+ SELECT sum(ac.amount) AS amount,
+ c.$category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gl a ON (a.id = ac.trans_id)
+ $dpt_join
+ WHERE $where
+ $glwhere
+ $dpt_from
+ AND NOT (c.link = 'AR' OR c.link = 'AP')
+ $project
+ GROUP BY c.$category
+
+ |;
+
+ if ($form->{project_id}) {
+
+ $query .= qq|
+
+ UNION
+
+ SELECT SUM(ac.sellprice * ac.qty) AS amount,
+ c.$category
+ FROM invoice ac
+ JOIN ar a ON (a.id = ac.trans_id)
+ JOIN parts p ON (ac.parts_id = p.id)
+ JOIN chart c on (p.income_accno_id = c.id)
+ $dpt_join
+ -- use transdate from subwhere
+ WHERE 1 = 1 $subwhere
+ AND c.category = 'I'
+ $dpt_where
+ AND ac.trans_id IN
+ (
+ SELECT trans_id
+ FROM acc_trans
+ JOIN chart ON (chart_id = id)
+ WHERE link LIKE '%AR_paid%'
+ $subwhere
+ )
+
+ $project
+ GROUP BY c.$category
+
+ UNION
+
+ SELECT SUM(ac.sellprice) AS amount,
+ c.$category
+ FROM invoice ac
+ JOIN ap a ON (a.id = ac.trans_id)
+ JOIN parts p ON (ac.parts_id = p.id)
+ JOIN chart c on (p.expense_accno_id = c.id)
+ $dpt_join
+ WHERE 1 = 1 $subwhere
+ AND c.category = 'E'
+ $dpt_where
+ AND ac.trans_id IN
+ (
+ SELECT trans_id
+ FROM acc_trans
+ JOIN chart ON (chart_id = id)
+ WHERE link LIKE '%AP_paid%'
+ $subwhere
+ )
+
+ $project
+ GROUP BY c.$category
+ |;
+ }
+
+ } else {
+
+ 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|
+
+ SELECT sum(ac.amount) AS amount,
+ c.$category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ $dpt_join
+ WHERE $where
+ $dpt_where
+ $project
+ GROUP BY c.$category
+ |;
+
+ if ($form->{project_id}) {
+
+ $query .= qq|
+
+ UNION
+
+ SELECT SUM(ac.sellprice * ac.qty) AS amount,
+ c.$category
+ FROM invoice ac
+ JOIN ar a ON (a.id = ac.trans_id)
+ JOIN parts p ON (ac.parts_id = p.id)
+ JOIN chart c on (p.income_accno_id = c.id)
+ $dpt_join
+ -- use transdate from subwhere
+ WHERE 1 = 1 $subwhere
+ AND c.category = 'I'
+ $dpt_where
+ $project
+ GROUP BY c.$category
+
+ UNION
+
+ SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
+ c.$category
+ FROM invoice ac
+ JOIN ap a ON (a.id = ac.trans_id)
+ JOIN parts p ON (ac.parts_id = p.id)
+ JOIN chart c on (p.expense_accno_id = c.id)
+ $dpt_join
+ WHERE 1 = 1 $subwhere
+ AND c.category = 'E'
+ $dpt_where
+ $project
+ GROUP BY c.$category
+ |;
+
+ }
+ }
+ my @accno;
+ my $accno;
+ my $ref;
+
+ # Show all $query in Debuglevel LXDebug::QUERY
+ $callingdetails = (caller (0))[3];
+ $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ if ($ref->{amount} < 0) {
+ $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};
+ }
+ }
+ $sth->finish;
+
+ $main::lxdebug->leave_sub();
+}
+
+1;
projects / kivitendo-erp.git / blobdiff