Revision a3057d3f
Von Moritz Bunkus vor fast 18 Jahren hinzugefügt
| SL/AM.pm | ||
|---|---|---|
|
my $dbh = $form->dbconnect($myconfig);
|
||
|
my $query = qq{
|
||
|
SELECT c.accno, c.description, c.charttype, c.category,
|
||
|
c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from,
|
||
|
c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from,
|
||
|
c.pos_bwa, datevautomatik,
|
||
|
tk.taxkey_id, tk.pos_ustva, tk.tax_id,
|
||
|
tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate
|
||
|
FROM chart c
|
||
|
LEFT JOIN taxkeys tk
|
||
|
ON (c.id=tk.chart_id AND tk.id =
|
||
|
(SELECT id FROM taxkeys
|
||
|
WHERE taxkeys.chart_id = c.id AND startdate <= current_date
|
||
|
ORDER BY startdate DESC LIMIT 1))
|
||
|
FROM chart c
|
||
|
LEFT JOIN taxkeys tk
|
||
|
ON (c.id=tk.chart_id AND tk.id =
|
||
|
(SELECT id FROM taxkeys
|
||
|
WHERE taxkeys.chart_id = c.id AND startdate <= current_date
|
||
|
ORDER BY startdate DESC LIMIT 1))
|
||
|
WHERE c.id = ?
|
||
|
};
|
||
|
|
||
|
|
||
|
|
||
|
$main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query");
|
||
|
my $sth = $dbh->prepare($query);
|
||
|
$sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
|
||
| ... | ... | |
|
|
||
|
# get taxkeys and description
|
||
|
$query = qq{
|
||
|
SELECT
|
||
|
id,
|
||
|
SELECT
|
||
|
id,
|
||
|
(SELECT accno FROM chart WHERE id=tax.chart_id) AS chart_accno,
|
||
|
taxkey,
|
||
|
id||'--'||taxkey AS tax,
|
||
|
taxdescription,
|
||
|
id||'--'||taxkey AS tax,
|
||
|
taxdescription,
|
||
|
rate
|
||
|
FROM tax ORDER BY taxkey
|
||
|
};
|
||
| ... | ... | |
|
if ($form->{id}) {
|
||
|
# get new accounts
|
||
|
$query = qq|SELECT id, accno,description
|
||
|
FROM chart
|
||
|
WHERE link = ?
|
||
|
FROM chart
|
||
|
WHERE link = ?
|
||
|
ORDER BY accno|;
|
||
|
$main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query");
|
||
|
$sth = $dbh->prepare($query);
|
||
| ... | ... | |
|
$sth->finish;
|
||
|
|
||
|
# get the taxkeys of account
|
||
|
|
||
|
|
||
|
$query = qq{
|
||
|
SELECT
|
||
|
tk.id,
|
||
| ... | ... | |
|
tk.tax_id,
|
||
|
t.taxdescription,
|
||
|
t.rate,
|
||
|
tk.taxkey_id,
|
||
|
tk.pos_ustva,
|
||
|
tk.taxkey_id,
|
||
|
tk.pos_ustva,
|
||
|
tk.startdate
|
||
|
FROM taxkeys tk
|
||
|
LEFT JOIN tax t ON (t.id = tk.tax_id)
|
||
|
LEFT JOIN chart c ON (c.id = t.chart_id)
|
||
|
|
||
|
WHERE tk.chart_id = ?
|
||
|
ORDER BY startdate DESC
|
||
|
ORDER BY startdate DESC
|
||
|
};
|
||
|
$main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query");
|
||
|
$sth = $dbh->prepare($query);
|
||
| ... | ... | |
|
push @{ $form->{ACCOUNT_TAXKEYS} }, $ref;
|
||
|
}
|
||
|
|
||
|
$sth->finish;
|
||
|
$sth->finish;
|
||
|
|
||
|
}
|
||
|
# check if we have any transactions
|
||
| ... | ... | |
|
|
||
|
if ($form->{id}) {
|
||
|
$query = qq|UPDATE chart SET
|
||
|
accno = ?,
|
||
|
description = ?,
|
||
|
accno = ?,
|
||
|
description = ?,
|
||
|
charttype = ?,
|
||
|
category = ?,
|
||
|
category = ?,
|
||
|
link = ?,
|
||
|
pos_bwa = ?,
|
||
|
pos_bwa = ?,
|
||
|
pos_bilanz = ?,
|
||
|
pos_eur = ?,
|
||
|
new_chart_id = ?,
|
||
|
pos_eur = ?,
|
||
|
new_chart_id = ?,
|
||
|
valid_from = ?,
|
||
|
datevautomatik = ?
|
||
|
WHERE id = ?|;
|
||
|
|
||
|
@values = (
|
||
|
$form->{accno},
|
||
|
$form->{description},
|
||
|
|
||
|
@values = (
|
||
|
$form->{accno},
|
||
|
$form->{description},
|
||
|
$form->{charttype},
|
||
|
$form->{category},
|
||
|
$form->{category},
|
||
|
$form->{link},
|
||
|
conv_i($form->{pos_bwa}),
|
||
|
conv_i($form->{pos_bilanz}),
|
||
|
conv_i($form->{pos_bilanz}),
|
||
|
conv_i($form->{pos_eur}),
|
||
|
conv_i($form->{new_chart_id}),
|
||
|
conv_date($form->{valid_from}),
|
||
| ... | ... | |
|
$form->{id},
|
||
|
);
|
||
|
|
||
|
}
|
||
|
}
|
||
|
elsif ($form->{id} && !$form->{new_chart_valid}) {
|
||
|
|
||
|
$query = qq|
|
||
|
UPDATE chart
|
||
|
SET new_chart_id = ?,
|
||
|
UPDATE chart
|
||
|
SET new_chart_id = ?,
|
||
|
valid_from = ?
|
||
|
WHERE id = ?
|
||
|
|;
|
||
|
|
||
|
@values = (
|
||
|
conv_i($form->{new_chart_id}),
|
||
|
|
||
|
@values = (
|
||
|
conv_i($form->{new_chart_id}),
|
||
|
conv_date($form->{valid_from}),
|
||
|
$form->{id}
|
||
|
);
|
||
|
}
|
||
|
}
|
||
|
else {
|
||
|
|
||
|
$query = qq|
|
||
|
INSERT INTO chart (
|
||
|
accno,
|
||
|
description,
|
||
|
accno,
|
||
|
description,
|
||
|
charttype,
|
||
|
category,
|
||
|
category,
|
||
|
link,
|
||
|
pos_bwa,
|
||
|
pos_bilanz,
|
||
|
pos_bwa,
|
||
|
pos_bilanz,
|
||
|
pos_eur,
|
||
|
new_chart_id,
|
||
|
new_chart_id,
|
||
|
valid_from,
|
||
|
datevautomatik )
|
||
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
|
|;
|
||
|
|
||
|
@values = (
|
||
|
$form->{accno},
|
||
|
$form->{description},
|
||
|
$form->{accno},
|
||
|
$form->{description},
|
||
|
$form->{charttype},
|
||
|
$form->{category}, $form->{link},
|
||
|
conv_i($form->{pos_bwa}),
|
||
| ... | ... | |
|
);
|
||
|
|
||
|
}
|
||
|
|
||
|
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
|
||
|
#Save Taxkeys
|
||
|
|
||
|
my @taxkeys = ();
|
||
|
|
||
|
|
||
|
my $MAX_TRIES = 10; # Maximum count of taxkeys in form
|
||
|
my $tk_count;
|
||
|
|
||
|
|
||
|
READTAXKEYS:
|
||
|
for $tk_count (0 .. $MAX_TRIES) {
|
||
|
|
||
|
|
||
|
# Loop control
|
||
|
|
||
|
|
||
|
# Check if the account already exists, else cancel
|
||
|
last READTAXKEYS if ( $form->{'id'} == 0);
|
||
|
|
||
| ... | ... | |
|
}
|
||
|
|
||
|
# Add valid taxkeys into the array
|
||
|
push @taxkeys ,
|
||
|
push @taxkeys ,
|
||
|
{
|
||
|
id => ($form->{"taxkey_id_$tk_count"} eq 'NEW') ? conv_i('') : conv_i($form->{"taxkey_id_$tk_count"}),
|
||
|
tax_id => conv_i($form->{"taxkey_tax_$tk_count"}),
|
||
| ... | ... | |
|
pos_ustva => conv_i($form->{"taxkey_pos_ustva_$tk_count"}),
|
||
|
delete => ( $form->{"taxkey_del_$tk_count"} eq 'delete' ) ? '1' : '',
|
||
|
};
|
||
|
|
||
|
|
||
|
$tk_count++;
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
for my $j (0 .. $#taxkeys){
|
||
|
if ( defined $taxkeys[$j]{'id'} ){
|
||
|
# delete Taxkey?
|
||
|
|
||
|
|
||
|
if ($taxkeys[$j]{'delete'}){
|
||
|
$query = qq{
|
||
|
DELETE FROM taxkeys WHERE id = ?
|
||
| ... | ... | |
|
@values = ($taxkeys[$j]{'id'});
|
||
|
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
|
||
|
|
||
|
next TAXKEY;
|
||
|
}
|
||
|
|
||
|
# UPDATE Taxkey
|
||
|
|
||
|
|
||
|
$query = qq{
|
||
|
UPDATE taxkeys
|
||
|
SET taxkey_id = (SELECT taxkey FROM tax WHERE tax.id = ?),
|
||
| ... | ... | |
|
pos_ustva = ?,
|
||
|
startdate = ?
|
||
|
WHERE id = ?
|
||
|
};
|
||
|
};
|
||
|
@values = (
|
||
|
$taxkeys[$j]{'tax_id'},
|
||
|
$taxkeys[$j]{'chart_id'},
|
||
|
$taxkeys[$j]{'tax_id'},
|
||
|
$taxkeys[$j]{'tax_id'},
|
||
|
$taxkeys[$j]{'pos_ustva'},
|
||
|
$taxkeys[$j]{'startdate'},
|
||
|
$taxkeys[$j]{'id'},
|
||
|
$taxkeys[$j]{'startdate'},
|
||
|
$taxkeys[$j]{'id'},
|
||
|
);
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
}
|
||
|
else {
|
||
|
# INSERT Taxkey
|
||
|
|
||
|
|
||
|
$query = qq{
|
||
|
INSERT INTO taxkeys (
|
||
|
taxkey_id,
|
||
| ... | ... | |
|
startdate
|
||
|
)
|
||
|
VALUES ((SELECT taxkey FROM tax WHERE tax.id = ?), ?, ?, ?, ?)
|
||
|
};
|
||
|
};
|
||
|
@values = (
|
||
|
$taxkeys[$j]{'tax_id'},
|
||
|
$taxkeys[$j]{'chart_id'},
|
||
|
$taxkeys[$j]{'tax_id'},
|
||
|
$taxkeys[$j]{'tax_id'},
|
||
|
$taxkeys[$j]{'chart_id'},
|
||
|
$taxkeys[$j]{'tax_id'},
|
||
|
$taxkeys[$j]{'pos_ustva'},
|
||
|
$taxkeys[$j]{'startdate'},
|
||
|
$taxkeys[$j]{'startdate'},
|
||
|
);
|
||
|
|
||
|
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
}
|
||
|
|
||
|
|
||
|
}
|
||
|
|
||
|
# commit
|
||
| ... | ... | |
|
# connect to database
|
||
|
my $dbh = $form->dbconnect($myconfig);
|
||
|
|
||
|
my $query = qq|SELECT
|
||
|
my $query = qq|SELECT
|
||
|
t.id,
|
||
|
t.taxkey,
|
||
|
t.taxdescription,
|
||
|
round(t.rate, 2) * 100 AS rate,
|
||
|
round(t.rate * 100, 2) AS rate,
|
||
|
c.accno AS taxnumber,
|
||
|
c.description AS account_description
|
||
|
FROM tax t
|
||
| ... | ... | |
|
my $dbh = $form->dbconnect($myconfig);
|
||
|
|
||
|
# get Accounts from chart
|
||
|
my $query = qq{ SELECT
|
||
|
my $query = qq{ SELECT
|
||
|
id,
|
||
|
accno || ' - ' || description AS _taxaccount
|
||
|
FROM chart
|
||
|
WHERE link LIKE '%_tax%'
|
||
|
ORDER BY accno
|
||
|
ORDER BY accno
|
||
|
};
|
||
|
|
||
|
$sth = $dbh->prepare($query);
|
||
| ... | ... | |
|
# connect to database
|
||
|
my $dbh = $form->dbconnect($myconfig);
|
||
|
|
||
|
my $query = qq|SELECT
|
||
|
my $query = qq|SELECT
|
||
|
taxkey,
|
||
|
taxdescription,
|
||
|
round(rate, 2) * 100 AS rate,
|
||
|
round(rate * 100, 2) AS rate,
|
||
|
chart_id
|
||
|
FROM tax
|
||
|
WHERE id = ? |;
|
||
|
|
||
|
|
||
|
my $sth = $dbh->prepare($query);
|
||
|
$sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
|
||
|
|
||
| ... | ... | |
|
# see if it is used by a taxkey
|
||
|
$query = qq|SELECT count(*) FROM taxkeys
|
||
|
WHERE tax_id = ?|;
|
||
|
|
||
|
|
||
|
($form->{orphaned}) = selectrow_query($form, $dbh, $query, $form->{id});
|
||
|
|
||
|
$form->{orphaned} = !$form->{orphaned};
|
||
|
$sth->finish;
|
||
|
|
||
|
|
||
|
if (!$form->{orphaned} ) {
|
||
|
$query = qq|SELECT DISTINCT c.id, c.accno
|
||
|
FROM taxkeys tk
|
||
|
LEFT JOIN tax t ON (t.id = tk.tax_id)
|
||
|
LEFT JOIN chart c ON (c.id = tk.chart_id)
|
||
|
WHERE tk.tax_id = ?|;
|
||
|
|
||
|
|
||
|
$sth = $dbh->prepare($query);
|
||
|
$sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
|
||
|
|
||
| ... | ... | |
|
|
||
|
$sth->finish;
|
||
|
}
|
||
|
|
||
|
|
||
|
$dbh->disconnect;
|
||
|
|
||
|
$main::lxdebug->leave_sub();
|
||
| ... | ... | |
|
my ($self, $myconfig, $form) = @_;
|
||
|
|
||
|
# connect to database
|
||
|
my $dbh = $form->dbconnect($myconfig);
|
||
|
my $dbh = $form->get_standard_dbh($myconfig);
|
||
|
|
||
|
$form->{rate} = $form->{rate} / 100;
|
||
|
|
||
| ... | ... | |
|
taxnumber = (SELECT accno FROM chart WHERE id= ? )
|
||
|
WHERE id = ?|;
|
||
|
push(@values, $form->{id});
|
||
|
}
|
||
|
else {
|
||
|
|
||
|
} else {
|
||
|
#ok
|
||
|
$query = qq|INSERT INTO tax (
|
||
|
taxkey,
|
||
| ... | ... | |
|
}
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
|
||
|
$dbh->disconnect;
|
||
|
$dbh->commit();
|
||
|
|
||
|
$main::lxdebug->leave_sub();
|
||
|
}
|
||
| ... | ... | |
|
my ($self, $myconfig, $form) = @_;
|
||
|
|
||
|
# connect to database
|
||
|
my $dbh = $form->dbconnect($myconfig);
|
||
|
my $dbh = $form->get_standard_dbh($myconfig);
|
||
|
|
||
|
$query = qq|DELETE FROM tax
|
||
|
WHERE id = ?|;
|
||
|
do_query($form, $dbh, $query, $form->{id});
|
||
|
|
||
|
$dbh->disconnect;
|
||
|
$dbh->commit();
|
||
|
|
||
|
$main::lxdebug->leave_sub();
|
||
|
}
|
||
| bin/mozilla/am.pl | ||
|---|---|---|
|
AM->get_tax(\%myconfig, \%$form);
|
||
|
_get_taxaccount_selection();
|
||
|
|
||
|
$form->{rate} = $form->format_amount(\%myconfig, $form->{rate}, 2);
|
||
|
|
||
|
$form->header();
|
||
|
|
||
|
my $parameters_ref = {
|
||
| ... | ... | |
|
|
||
|
AM->taxes(\%myconfig, \%$form);
|
||
|
|
||
|
$form->{callback} =
|
||
|
"$form->{script}?action=list_tax&login=$form->{login}&password=$form->{password}";
|
||
|
map { $_->{rate} = $form->format_amount(\%myconfig, $_->{rate}, 2) } @{ $form->{TAX} };
|
||
|
|
||
|
$form->{callback} = build_std_url('action=list_tax');
|
||
|
|
||
|
$form->{title} = $locale->text('Tax-O-Matic');
|
||
|
|
||
| ... | ... | |
|
$form->isblank("rate", $locale->text('Taxrate missing!'));
|
||
|
$form->isblank("taxdescription", $locale->text('Taxdescription missing!'));
|
||
|
$form->isblank("taxkey", $locale->text('Taxkey missing!'));
|
||
|
|
||
|
|
||
|
$form->{rate} = $form->parse_amount(\%myconfig, $form->{rate});
|
||
|
|
||
|
if ( $form->{rate} <= 0 || $form->{rate} >= 100 ) {
|
||
|
$form->error($locale->text('Tax Percent is a number between 0 and 100'));
|
||
|
}
|
||
Auch abrufbar als: Unified diff
Steuern: Anzeige und Eingabe des Steuersatzes mit formatierten Zahlen. Auch Nachkommastellen bei Steuern zulassen. Kosmetik.