Revision a3057d3f
Von Moritz Bunkus vor etwa 17 Jahren hinzugefügt
SL/AM.pm | ||
---|---|---|
49 | 49 |
my $dbh = $form->dbconnect($myconfig); |
50 | 50 |
my $query = qq{ |
51 | 51 |
SELECT c.accno, c.description, c.charttype, c.category, |
52 |
c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from,
|
|
52 |
c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, |
|
53 | 53 |
c.pos_bwa, datevautomatik, |
54 | 54 |
tk.taxkey_id, tk.pos_ustva, tk.tax_id, |
55 | 55 |
tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate |
56 |
FROM chart c
|
|
57 |
LEFT JOIN taxkeys tk
|
|
58 |
ON (c.id=tk.chart_id AND tk.id =
|
|
59 |
(SELECT id FROM taxkeys
|
|
60 |
WHERE taxkeys.chart_id = c.id AND startdate <= current_date
|
|
61 |
ORDER BY startdate DESC LIMIT 1))
|
|
56 |
FROM chart c |
|
57 |
LEFT JOIN taxkeys tk |
|
58 |
ON (c.id=tk.chart_id AND tk.id = |
|
59 |
(SELECT id FROM taxkeys |
|
60 |
WHERE taxkeys.chart_id = c.id AND startdate <= current_date |
|
61 |
ORDER BY startdate DESC LIMIT 1)) |
|
62 | 62 |
WHERE c.id = ? |
63 | 63 |
}; |
64 | 64 |
|
65 |
|
|
65 |
|
|
66 | 66 |
$main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query"); |
67 | 67 |
my $sth = $dbh->prepare($query); |
68 | 68 |
$sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); |
... | ... | |
92 | 92 |
|
93 | 93 |
# get taxkeys and description |
94 | 94 |
$query = qq{ |
95 |
SELECT
|
|
96 |
id,
|
|
95 |
SELECT |
|
96 |
id, |
|
97 | 97 |
(SELECT accno FROM chart WHERE id=tax.chart_id) AS chart_accno, |
98 | 98 |
taxkey, |
99 |
id||'--'||taxkey AS tax,
|
|
100 |
taxdescription,
|
|
99 |
id||'--'||taxkey AS tax, |
|
100 |
taxdescription, |
|
101 | 101 |
rate |
102 | 102 |
FROM tax ORDER BY taxkey |
103 | 103 |
}; |
... | ... | |
115 | 115 |
if ($form->{id}) { |
116 | 116 |
# get new accounts |
117 | 117 |
$query = qq|SELECT id, accno,description |
118 |
FROM chart
|
|
119 |
WHERE link = ?
|
|
118 |
FROM chart |
|
119 |
WHERE link = ? |
|
120 | 120 |
ORDER BY accno|; |
121 | 121 |
$main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query"); |
122 | 122 |
$sth = $dbh->prepare($query); |
... | ... | |
130 | 130 |
$sth->finish; |
131 | 131 |
|
132 | 132 |
# get the taxkeys of account |
133 |
|
|
133 |
|
|
134 | 134 |
$query = qq{ |
135 | 135 |
SELECT |
136 | 136 |
tk.id, |
... | ... | |
139 | 139 |
tk.tax_id, |
140 | 140 |
t.taxdescription, |
141 | 141 |
t.rate, |
142 |
tk.taxkey_id,
|
|
143 |
tk.pos_ustva,
|
|
142 |
tk.taxkey_id, |
|
143 |
tk.pos_ustva, |
|
144 | 144 |
tk.startdate |
145 | 145 |
FROM taxkeys tk |
146 | 146 |
LEFT JOIN tax t ON (t.id = tk.tax_id) |
147 | 147 |
LEFT JOIN chart c ON (c.id = t.chart_id) |
148 | 148 |
|
149 | 149 |
WHERE tk.chart_id = ? |
150 |
ORDER BY startdate DESC
|
|
150 |
ORDER BY startdate DESC |
|
151 | 151 |
}; |
152 | 152 |
$main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query"); |
153 | 153 |
$sth = $dbh->prepare($query); |
... | ... | |
160 | 160 |
push @{ $form->{ACCOUNT_TAXKEYS} }, $ref; |
161 | 161 |
} |
162 | 162 |
|
163 |
$sth->finish;
|
|
163 |
$sth->finish; |
|
164 | 164 |
|
165 | 165 |
} |
166 | 166 |
# check if we have any transactions |
... | ... | |
233 | 233 |
|
234 | 234 |
if ($form->{id}) { |
235 | 235 |
$query = qq|UPDATE chart SET |
236 |
accno = ?,
|
|
237 |
description = ?,
|
|
236 |
accno = ?, |
|
237 |
description = ?, |
|
238 | 238 |
charttype = ?, |
239 |
category = ?,
|
|
239 |
category = ?, |
|
240 | 240 |
link = ?, |
241 |
pos_bwa = ?,
|
|
241 |
pos_bwa = ?, |
|
242 | 242 |
pos_bilanz = ?, |
243 |
pos_eur = ?,
|
|
244 |
new_chart_id = ?,
|
|
243 |
pos_eur = ?, |
|
244 |
new_chart_id = ?, |
|
245 | 245 |
valid_from = ?, |
246 | 246 |
datevautomatik = ? |
247 | 247 |
WHERE id = ?|; |
248 |
|
|
249 |
@values = (
|
|
250 |
$form->{accno},
|
|
251 |
$form->{description},
|
|
248 |
|
|
249 |
@values = ( |
|
250 |
$form->{accno}, |
|
251 |
$form->{description}, |
|
252 | 252 |
$form->{charttype}, |
253 |
$form->{category},
|
|
253 |
$form->{category}, |
|
254 | 254 |
$form->{link}, |
255 | 255 |
conv_i($form->{pos_bwa}), |
256 |
conv_i($form->{pos_bilanz}),
|
|
256 |
conv_i($form->{pos_bilanz}), |
|
257 | 257 |
conv_i($form->{pos_eur}), |
258 | 258 |
conv_i($form->{new_chart_id}), |
259 | 259 |
conv_date($form->{valid_from}), |
... | ... | |
261 | 261 |
$form->{id}, |
262 | 262 |
); |
263 | 263 |
|
264 |
}
|
|
264 |
} |
|
265 | 265 |
elsif ($form->{id} && !$form->{new_chart_valid}) { |
266 | 266 |
|
267 | 267 |
$query = qq| |
268 |
UPDATE chart
|
|
269 |
SET new_chart_id = ?,
|
|
268 |
UPDATE chart |
|
269 |
SET new_chart_id = ?, |
|
270 | 270 |
valid_from = ? |
271 | 271 |
WHERE id = ? |
272 | 272 |
|; |
273 |
|
|
274 |
@values = (
|
|
275 |
conv_i($form->{new_chart_id}),
|
|
273 |
|
|
274 |
@values = ( |
|
275 |
conv_i($form->{new_chart_id}), |
|
276 | 276 |
conv_date($form->{valid_from}), |
277 | 277 |
$form->{id} |
278 | 278 |
); |
279 |
}
|
|
279 |
} |
|
280 | 280 |
else { |
281 | 281 |
|
282 | 282 |
$query = qq| |
283 | 283 |
INSERT INTO chart ( |
284 |
accno,
|
|
285 |
description,
|
|
284 |
accno, |
|
285 |
description, |
|
286 | 286 |
charttype, |
287 |
category,
|
|
287 |
category, |
|
288 | 288 |
link, |
289 |
pos_bwa,
|
|
290 |
pos_bilanz,
|
|
289 |
pos_bwa, |
|
290 |
pos_bilanz, |
|
291 | 291 |
pos_eur, |
292 |
new_chart_id,
|
|
292 |
new_chart_id, |
|
293 | 293 |
valid_from, |
294 | 294 |
datevautomatik ) |
295 | 295 |
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) |
296 | 296 |
|; |
297 | 297 |
|
298 | 298 |
@values = ( |
299 |
$form->{accno},
|
|
300 |
$form->{description},
|
|
299 |
$form->{accno}, |
|
300 |
$form->{description}, |
|
301 | 301 |
$form->{charttype}, |
302 | 302 |
$form->{category}, $form->{link}, |
303 | 303 |
conv_i($form->{pos_bwa}), |
... | ... | |
308 | 308 |
); |
309 | 309 |
|
310 | 310 |
} |
311 |
|
|
311 |
|
|
312 | 312 |
do_query($form, $dbh, $query, @values); |
313 | 313 |
|
314 | 314 |
#Save Taxkeys |
315 | 315 |
|
316 | 316 |
my @taxkeys = (); |
317 |
|
|
317 |
|
|
318 | 318 |
my $MAX_TRIES = 10; # Maximum count of taxkeys in form |
319 | 319 |
my $tk_count; |
320 |
|
|
320 |
|
|
321 | 321 |
READTAXKEYS: |
322 | 322 |
for $tk_count (0 .. $MAX_TRIES) { |
323 |
|
|
323 |
|
|
324 | 324 |
# Loop control |
325 |
|
|
325 |
|
|
326 | 326 |
# Check if the account already exists, else cancel |
327 | 327 |
last READTAXKEYS if ( $form->{'id'} == 0); |
328 | 328 |
|
... | ... | |
339 | 339 |
} |
340 | 340 |
|
341 | 341 |
# Add valid taxkeys into the array |
342 |
push @taxkeys ,
|
|
342 |
push @taxkeys , |
|
343 | 343 |
{ |
344 | 344 |
id => ($form->{"taxkey_id_$tk_count"} eq 'NEW') ? conv_i('') : conv_i($form->{"taxkey_id_$tk_count"}), |
345 | 345 |
tax_id => conv_i($form->{"taxkey_tax_$tk_count"}), |
... | ... | |
348 | 348 |
pos_ustva => conv_i($form->{"taxkey_pos_ustva_$tk_count"}), |
349 | 349 |
delete => ( $form->{"taxkey_del_$tk_count"} eq 'delete' ) ? '1' : '', |
350 | 350 |
}; |
351 |
|
|
351 |
|
|
352 | 352 |
$tk_count++; |
353 | 353 |
} |
354 | 354 |
|
... | ... | |
356 | 356 |
for my $j (0 .. $#taxkeys){ |
357 | 357 |
if ( defined $taxkeys[$j]{'id'} ){ |
358 | 358 |
# delete Taxkey? |
359 |
|
|
359 |
|
|
360 | 360 |
if ($taxkeys[$j]{'delete'}){ |
361 | 361 |
$query = qq{ |
362 | 362 |
DELETE FROM taxkeys WHERE id = ? |
... | ... | |
365 | 365 |
@values = ($taxkeys[$j]{'id'}); |
366 | 366 |
|
367 | 367 |
do_query($form, $dbh, $query, @values); |
368 |
|
|
368 |
|
|
369 | 369 |
next TAXKEY; |
370 | 370 |
} |
371 | 371 |
|
372 | 372 |
# UPDATE Taxkey |
373 |
|
|
373 |
|
|
374 | 374 |
$query = qq{ |
375 | 375 |
UPDATE taxkeys |
376 | 376 |
SET taxkey_id = (SELECT taxkey FROM tax WHERE tax.id = ?), |
... | ... | |
379 | 379 |
pos_ustva = ?, |
380 | 380 |
startdate = ? |
381 | 381 |
WHERE id = ? |
382 |
};
|
|
382 |
}; |
|
383 | 383 |
@values = ( |
384 | 384 |
$taxkeys[$j]{'tax_id'}, |
385 | 385 |
$taxkeys[$j]{'chart_id'}, |
386 |
$taxkeys[$j]{'tax_id'},
|
|
386 |
$taxkeys[$j]{'tax_id'}, |
|
387 | 387 |
$taxkeys[$j]{'pos_ustva'}, |
388 |
$taxkeys[$j]{'startdate'},
|
|
389 |
$taxkeys[$j]{'id'},
|
|
388 |
$taxkeys[$j]{'startdate'}, |
|
389 |
$taxkeys[$j]{'id'}, |
|
390 | 390 |
); |
391 | 391 |
do_query($form, $dbh, $query, @values); |
392 | 392 |
} |
393 | 393 |
else { |
394 | 394 |
# INSERT Taxkey |
395 |
|
|
395 |
|
|
396 | 396 |
$query = qq{ |
397 | 397 |
INSERT INTO taxkeys ( |
398 | 398 |
taxkey_id, |
... | ... | |
402 | 402 |
startdate |
403 | 403 |
) |
404 | 404 |
VALUES ((SELECT taxkey FROM tax WHERE tax.id = ?), ?, ?, ?, ?) |
405 |
};
|
|
405 |
}; |
|
406 | 406 |
@values = ( |
407 |
$taxkeys[$j]{'tax_id'},
|
|
408 |
$taxkeys[$j]{'chart_id'},
|
|
409 |
$taxkeys[$j]{'tax_id'},
|
|
407 |
$taxkeys[$j]{'tax_id'}, |
|
408 |
$taxkeys[$j]{'chart_id'}, |
|
409 |
$taxkeys[$j]{'tax_id'}, |
|
410 | 410 |
$taxkeys[$j]{'pos_ustva'}, |
411 |
$taxkeys[$j]{'startdate'},
|
|
411 |
$taxkeys[$j]{'startdate'}, |
|
412 | 412 |
); |
413 |
|
|
413 |
|
|
414 | 414 |
do_query($form, $dbh, $query, @values); |
415 | 415 |
} |
416 |
|
|
416 |
|
|
417 | 417 |
} |
418 | 418 |
|
419 | 419 |
# commit |
... | ... | |
2063 | 2063 |
# connect to database |
2064 | 2064 |
my $dbh = $form->dbconnect($myconfig); |
2065 | 2065 |
|
2066 |
my $query = qq|SELECT
|
|
2066 |
my $query = qq|SELECT |
|
2067 | 2067 |
t.id, |
2068 | 2068 |
t.taxkey, |
2069 | 2069 |
t.taxdescription, |
2070 |
round(t.rate, 2) * 100 AS rate,
|
|
2070 |
round(t.rate * 100, 2) AS rate,
|
|
2071 | 2071 |
c.accno AS taxnumber, |
2072 | 2072 |
c.description AS account_description |
2073 | 2073 |
FROM tax t |
... | ... | |
2096 | 2096 |
my $dbh = $form->dbconnect($myconfig); |
2097 | 2097 |
|
2098 | 2098 |
# get Accounts from chart |
2099 |
my $query = qq{ SELECT
|
|
2099 |
my $query = qq{ SELECT |
|
2100 | 2100 |
id, |
2101 | 2101 |
accno || ' - ' || description AS _taxaccount |
2102 | 2102 |
FROM chart |
2103 | 2103 |
WHERE link LIKE '%_tax%' |
2104 |
ORDER BY accno
|
|
2104 |
ORDER BY accno |
|
2105 | 2105 |
}; |
2106 | 2106 |
|
2107 | 2107 |
$sth = $dbh->prepare($query); |
... | ... | |
2127 | 2127 |
# connect to database |
2128 | 2128 |
my $dbh = $form->dbconnect($myconfig); |
2129 | 2129 |
|
2130 |
my $query = qq|SELECT
|
|
2130 |
my $query = qq|SELECT |
|
2131 | 2131 |
taxkey, |
2132 | 2132 |
taxdescription, |
2133 |
round(rate, 2) * 100 AS rate,
|
|
2133 |
round(rate * 100, 2) AS rate,
|
|
2134 | 2134 |
chart_id |
2135 | 2135 |
FROM tax |
2136 | 2136 |
WHERE id = ? |; |
2137 |
|
|
2137 |
|
|
2138 | 2138 |
my $sth = $dbh->prepare($query); |
2139 | 2139 |
$sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); |
2140 | 2140 |
|
... | ... | |
2147 | 2147 |
# see if it is used by a taxkey |
2148 | 2148 |
$query = qq|SELECT count(*) FROM taxkeys |
2149 | 2149 |
WHERE tax_id = ?|; |
2150 |
|
|
2150 |
|
|
2151 | 2151 |
($form->{orphaned}) = selectrow_query($form, $dbh, $query, $form->{id}); |
2152 | 2152 |
|
2153 | 2153 |
$form->{orphaned} = !$form->{orphaned}; |
2154 | 2154 |
$sth->finish; |
2155 |
|
|
2155 |
|
|
2156 | 2156 |
if (!$form->{orphaned} ) { |
2157 | 2157 |
$query = qq|SELECT DISTINCT c.id, c.accno |
2158 | 2158 |
FROM taxkeys tk |
2159 | 2159 |
LEFT JOIN tax t ON (t.id = tk.tax_id) |
2160 | 2160 |
LEFT JOIN chart c ON (c.id = tk.chart_id) |
2161 | 2161 |
WHERE tk.tax_id = ?|; |
2162 |
|
|
2162 |
|
|
2163 | 2163 |
$sth = $dbh->prepare($query); |
2164 | 2164 |
$sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); |
2165 | 2165 |
|
... | ... | |
2170 | 2170 |
|
2171 | 2171 |
$sth->finish; |
2172 | 2172 |
} |
2173 |
|
|
2173 |
|
|
2174 | 2174 |
$dbh->disconnect; |
2175 | 2175 |
|
2176 | 2176 |
$main::lxdebug->leave_sub(); |
... | ... | |
2182 | 2182 |
my ($self, $myconfig, $form) = @_; |
2183 | 2183 |
|
2184 | 2184 |
# connect to database |
2185 |
my $dbh = $form->dbconnect($myconfig);
|
|
2185 |
my $dbh = $form->get_standard_dbh($myconfig);
|
|
2186 | 2186 |
|
2187 | 2187 |
$form->{rate} = $form->{rate} / 100; |
2188 | 2188 |
|
... | ... | |
2196 | 2196 |
taxnumber = (SELECT accno FROM chart WHERE id= ? ) |
2197 | 2197 |
WHERE id = ?|; |
2198 | 2198 |
push(@values, $form->{id}); |
2199 |
} |
|
2200 |
else { |
|
2199 |
|
|
2200 |
} else {
|
|
2201 | 2201 |
#ok |
2202 | 2202 |
$query = qq|INSERT INTO tax ( |
2203 | 2203 |
taxkey, |
... | ... | |
2210 | 2210 |
} |
2211 | 2211 |
do_query($form, $dbh, $query, @values); |
2212 | 2212 |
|
2213 |
$dbh->disconnect;
|
|
2213 |
$dbh->commit();
|
|
2214 | 2214 |
|
2215 | 2215 |
$main::lxdebug->leave_sub(); |
2216 | 2216 |
} |
... | ... | |
2221 | 2221 |
my ($self, $myconfig, $form) = @_; |
2222 | 2222 |
|
2223 | 2223 |
# connect to database |
2224 |
my $dbh = $form->dbconnect($myconfig);
|
|
2224 |
my $dbh = $form->get_standard_dbh($myconfig);
|
|
2225 | 2225 |
|
2226 | 2226 |
$query = qq|DELETE FROM tax |
2227 | 2227 |
WHERE id = ?|; |
2228 | 2228 |
do_query($form, $dbh, $query, $form->{id}); |
2229 | 2229 |
|
2230 |
$dbh->disconnect;
|
|
2230 |
$dbh->commit();
|
|
2231 | 2231 |
|
2232 | 2232 |
$main::lxdebug->leave_sub(); |
2233 | 2233 |
} |
Auch abrufbar als: Unified diff
Steuern: Anzeige und Eingabe des Steuersatzes mit formatierten Zahlen. Auch Nachkommastellen bei Steuern zulassen. Kosmetik.