Revision 2a4516c1
Von Niclas Zimmermann vor fast 12 Jahren hinzugefügt
| SL/AP.pm | ||
|---|---|---|
| 237 | 237 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) | . |
| 238 | 238 |
qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, | . |
| 239 | 239 |
qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| . |
| 240 |
qq| (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 240 |
qq| (SELECT tax_id| . |
|
| 241 |
qq| FROM taxkeys| . |
|
| 242 |
qq| WHERE chart_id= (SELECT id | . |
|
| 243 |
qq| FROM chart| . |
|
| 244 |
qq| WHERE accno = ?)| . |
|
| 245 |
qq| AND startdate <= ?| . |
|
| 246 |
qq| ORDER BY startdate DESC LIMIT 1))|; |
|
| 241 | 247 |
@values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables},
|
| 242 | 248 |
conv_date($form->{transdate}), $form->{AP_amounts}{payables}, $form->{AP_amounts}{payables}, conv_date($form->{transdate}));
|
| 243 | 249 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 286 | 292 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id) | . |
| 287 | 293 |
qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, | . |
| 288 | 294 |
qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| . |
| 289 |
qq| (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 295 |
qq| (SELECT tax_id| . |
|
| 296 |
qq| FROM taxkeys| . |
|
| 297 |
qq| WHERE chart_id= (SELECT id | . |
|
| 298 |
qq| FROM chart| . |
|
| 299 |
qq| WHERE accno = ?)| . |
|
| 300 |
qq| AND startdate <= ?| . |
|
| 301 |
qq| ORDER BY startdate DESC LIMIT 1))|; |
|
| 290 | 302 |
@values = ($form->{id}, $form->{AP_payables}, $amount,
|
| 291 | 303 |
conv_date($form->{"datepaid_$i"}), $project_id,
|
| 292 | 304 |
$form->{AP_payables}, $form->{AP_payables}, conv_date($form->{"datepaid_$i"}));
|
| ... | ... | |
| 300 | 312 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id) | . |
| 301 | 313 |
qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, | . |
| 302 | 314 |
qq| (SELECT taxkey_id FROM chart WHERE accno = ?), | . |
| 303 |
qq| (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 315 |
qq| (SELECT tax_id| . |
|
| 316 |
qq| FROM taxkeys| . |
|
| 317 |
qq| WHERE chart_id= (SELECT id | . |
|
| 318 |
qq| FROM chart| . |
|
| 319 |
qq| WHERE accno = ?)| . |
|
| 320 |
qq| AND startdate <= ?| . |
|
| 321 |
qq| ORDER BY startdate DESC LIMIT 1))|; |
|
| 304 | 322 |
@values = ($form->{id}, $form->{"AP_paid_account_$i"}, $form->{"paid_$i"},
|
| 305 | 323 |
conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"},
|
| 306 | 324 |
$form->{"memo_$i"}, $project_id, $form->{"AP_paid_account_$i"},
|
| ... | ... | |
| 316 | 334 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | . |
| 317 | 335 |
qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | . |
| 318 | 336 |
qq| (SELECT taxkey_id FROM chart WHERE accno = ?), | . |
| 319 |
qq| (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 337 |
qq| (SELECT tax_id| . |
|
| 338 |
qq| FROM taxkeys| . |
|
| 339 |
qq| WHERE chart_id= (SELECT id | . |
|
| 340 |
qq| FROM chart| . |
|
| 341 |
qq| WHERE accno = ?)| . |
|
| 342 |
qq| AND startdate <= ?| . |
|
| 343 |
qq| ORDER BY startdate DESC LIMIT 1))|; |
|
| 320 | 344 |
@values = ($form->{id}, $form->{"AP_paid_account_$i"}, $amount,
|
| 321 | 345 |
conv_date($form->{"datepaid_$i"}), $project_id,
|
| 322 | 346 |
$form->{"AP_paid_account_$i"},
|
| ... | ... | |
| 335 | 359 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | . |
| 336 | 360 |
qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | . |
| 337 | 361 |
qq| (SELECT taxkey_id FROM chart WHERE accno = ?)| . |
| 338 |
qq| (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 362 |
qq| (SELECT tax_id| . |
|
| 363 |
qq| FROM taxkeys| . |
|
| 364 |
qq| WHERE chart_id= (SELECT id | . |
|
| 365 |
qq| FROM chart| . |
|
| 366 |
qq| WHERE accno = ?)| . |
|
| 367 |
qq| AND startdate <= ?| . |
|
| 368 |
qq| ORDER BY startdate DESC LIMIT 1))|; |
|
| 339 | 369 |
@values = ($form->{id}, ($amount > 0) ?
|
| 340 | 370 |
$form->{fxgain_accno} : $form->{fxloss_accno},
|
| 341 | 371 |
$amount, conv_date($form->{"datepaid_$i"}), $project_id,
|
| SL/AR.pm | ||
|---|---|---|
| 192 | 192 |
# add recievables |
| 193 | 193 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) |
| 194 | 194 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?), |
| 195 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 195 |
(SELECT tax_id |
|
| 196 |
FROM taxkeys |
|
| 197 |
WHERE chart_id= (SELECT id |
|
| 198 |
FROM chart |
|
| 199 |
WHERE accno = ?) |
|
| 200 |
AND startdate <= ? |
|
| 201 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 196 | 202 |
@values = (conv_i($form->{id}), $form->{AR_amounts}{receivables}, conv_i($form->{receivables}), conv_date($form->{transdate}),
|
| 197 | 203 |
$form->{AR_amounts}{receivables}, $form->{AR_amounts}{receivables}, conv_date($form->{transdate}));
|
| 198 | 204 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 233 | 239 |
# add receivable |
| 234 | 240 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id) |
| 235 | 241 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?), |
| 236 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 242 |
(SELECT tax_id |
|
| 243 |
FROM taxkeys |
|
| 244 |
WHERE chart_id= (SELECT id |
|
| 245 |
FROM chart |
|
| 246 |
WHERE accno = ?) |
|
| 247 |
AND startdate <= ? |
|
| 248 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 237 | 249 |
@values = (conv_i($form->{id}), $form->{AR}{receivables}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AR}{receivables}, $form->{AR}{receivables}, conv_date($form->{"datepaid_$i"}));
|
| 238 | 250 |
do_query($form, $dbh, $query, @values); |
| 239 | 251 |
} |
| ... | ... | |
| 245 | 257 |
$amount = $form->{"paid_$i"} * -1;
|
| 246 | 258 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id) |
| 247 | 259 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?), |
| 248 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 260 |
(SELECT tax_id |
|
| 261 |
FROM taxkeys |
|
| 262 |
WHERE chart_id= (SELECT id |
|
| 263 |
FROM chart |
|
| 264 |
WHERE accno = ?) |
|
| 265 |
AND startdate <= ? |
|
| 266 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 249 | 267 |
@values = (conv_i($form->{id}), $form->{AR}{"paid_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $form->{AR}{"paid_$i"},
|
| 250 | 268 |
$form->{AR}{"paid_$i"}, conv_date($form->{"datepaid_$i"}));
|
| 251 | 269 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 256 | 274 |
if ($amount != 0) {
|
| 257 | 275 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) |
| 258 | 276 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?), |
| 259 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 277 |
(SELECT tax_id |
|
| 278 |
FROM taxkeys |
|
| 279 |
WHERE chart_id= (SELECT id |
|
| 280 |
FROM chart |
|
| 281 |
WHERE accno = ?) |
|
| 282 |
AND startdate <= ? |
|
| 283 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 260 | 284 |
@values = (conv_i($form->{id}), $form->{AR}{"paid_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AR}{"paid_$i"},
|
| 261 | 285 |
$form->{AR}{"paid_$i"}, conv_date($form->{"datepaid_$i"}));
|
| 262 | 286 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 269 | 293 |
my $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
|
| 270 | 294 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) |
| 271 | 295 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?), |
| 272 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 296 |
(SELECT tax_id |
|
| 297 |
FROM taxkeys |
|
| 298 |
WHERE chart_id= (SELECT id |
|
| 299 |
FROM chart |
|
| 300 |
WHERE accno = ?) |
|
| 301 |
AND startdate <= ? |
|
| 302 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 273 | 303 |
@values = (conv_i($form->{id}), $accno, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $accno, $accno, conv_date($form->{"datepaid_$i"}));
|
| 274 | 304 |
do_query($form, $dbh, $query, @values); |
| 275 | 305 |
} |
| SL/IR.pm | ||
|---|---|---|
| 270 | 270 |
# allocated >= 0 |
| 271 | 271 |
# add entry for inventory, this one is for the sold item |
| 272 | 272 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?), |
| 273 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 273 |
(SELECT tax_id |
|
| 274 |
FROM taxkeys |
|
| 275 |
WHERE chart_id= (SELECT id |
|
| 276 |
FROM chart |
|
| 277 |
WHERE accno = ?) |
|
| 278 |
AND startdate <= ? |
|
| 279 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 274 | 280 |
@values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{inventory_accno_id}, $ref->{transdate});
|
| 275 | 281 |
do_query($form, $dbh, $query, @values); |
| 276 | 282 |
|
| 277 | 283 |
# add expense |
| 278 | 284 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?), |
| 279 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 285 |
(SELECT tax_id |
|
| 286 |
FROM taxkeys |
|
| 287 |
WHERE chart_id= (SELECT id |
|
| 288 |
FROM chart |
|
| 289 |
WHERE accno = ?) |
|
| 290 |
AND startdate <= ? |
|
| 291 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 280 | 292 |
@values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}, $ref->{expense_accno_id}, $ref->{transdate});
|
| 281 | 293 |
do_query($form, $dbh, $query, @values); |
| 282 | 294 |
} |
| ... | ... | |
| 483 | 495 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id) |
| 484 | 496 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
| 485 | 497 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?, |
| 486 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 498 |
(SELECT tax_id |
|
| 499 |
FROM taxkeys |
|
| 500 |
WHERE chart_id= (SELECT id |
|
| 501 |
FROM chart |
|
| 502 |
WHERE accno = ?) |
|
| 503 |
AND startdate <= ? |
|
| 504 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 487 | 505 |
@values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
|
| 488 | 506 |
conv_date($form->{invdate}), $accno, $project_id, $accno, conv_date($form->{invdate}));
|
| 489 | 507 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 523 | 541 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id) |
| 524 | 542 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
| 525 | 543 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?, |
| 526 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 544 |
(SELECT tax_id |
|
| 545 |
FROM taxkeys |
|
| 546 |
WHERE chart_id= (SELECT id |
|
| 547 |
FROM chart |
|
| 548 |
WHERE accno = ?) |
|
| 549 |
AND startdate <= ? |
|
| 550 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 527 | 551 |
@values = (conv_i($form->{id}), $form->{AP}, $amount,
|
| 528 | 552 |
$form->{"datepaid_$i"}, $form->{AP}, $project_id, $form->{AP}, conv_date($form->{"datepaid_$i"}));
|
| 529 | 553 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 536 | 560 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id, tax_id) |
| 537 | 561 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, |
| 538 | 562 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?, |
| 539 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 563 |
(SELECT tax_id |
|
| 564 |
FROM taxkeys |
|
| 565 |
WHERE chart_id= (SELECT id |
|
| 566 |
FROM chart WHERE accno = ?) |
|
| 567 |
AND startdate <= ? |
|
| 568 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 540 | 569 |
@values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
|
| 541 | 570 |
$gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id, $accno, conv_date($form->{"datepaid_$i"}));
|
| 542 | 571 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 578 | 607 |
|
| 579 | 608 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id, tax_id) |
| 580 | 609 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?, |
| 581 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; |
|
| 610 |
(SELECT tax_id |
|
| 611 |
FROM taxkeys |
|
| 612 |
WHERE chart_id= (SELECT id |
|
| 613 |
FROM chart |
|
| 614 |
WHERE accno = ?) |
|
| 615 |
AND startdate <= ? |
|
| 616 |
ORDER BY startdate DESC LIMIT 1))|; |
|
| 582 | 617 |
@values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id, $accno, $form->{fx}{$accno}{$transdate});
|
| 583 | 618 |
do_query($form, $dbh, $query, @values); |
| 584 | 619 |
} |
| SL/IS.pm | ||
|---|---|---|
| 863 | 863 |
$query = |
| 864 | 864 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id) |
| 865 | 865 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
| 866 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1), |
|
| 866 |
(SELECT tax_id |
|
| 867 |
FROM taxkeys |
|
| 868 |
WHERE chart_id= (SELECT id |
|
| 869 |
FROM chart |
|
| 870 |
WHERE accno = ?) |
|
| 871 |
AND startdate <= ? |
|
| 872 |
ORDER BY startdate DESC LIMIT 1), |
|
| 867 | 873 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
| 868 | 874 |
@values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
|
| 869 | 875 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 878 | 884 |
$query = |
| 879 | 885 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id) |
| 880 | 886 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
| 881 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1), |
|
| 887 |
(SELECT tax_id |
|
| 888 |
FROM taxkeys |
|
| 889 |
WHERE chart_id= (SELECT id |
|
| 890 |
FROM chart |
|
| 891 |
WHERE accno = ?) |
|
| 892 |
AND startdate <= ? |
|
| 893 |
ORDER BY startdate DESC LIMIT 1), |
|
| 882 | 894 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
| 883 | 895 |
@values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
|
| 884 | 896 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 928 | 940 |
$query = |
| 929 | 941 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id) |
| 930 | 942 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
| 931 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1), |
|
| 943 |
(SELECT tax_id |
|
| 944 |
FROM taxkeys |
|
| 945 |
WHERE chart_id= (SELECT id |
|
| 946 |
FROM chart |
|
| 947 |
WHERE accno = ?) |
|
| 948 |
AND startdate <= ? |
|
| 949 |
ORDER BY startdate DESC LIMIT 1), |
|
| 932 | 950 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
| 933 | 951 |
@values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, $project_id);
|
| 934 | 952 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 941 | 959 |
$query = |
| 942 | 960 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id) |
| 943 | 961 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, |
| 944 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1), |
|
| 962 |
(SELECT tax_id |
|
| 963 |
FROM taxkeys |
|
| 964 |
WHERE chart_id= (SELECT id |
|
| 965 |
FROM chart |
|
| 966 |
WHERE accno = ?) |
|
| 967 |
AND startdate <= ? |
|
| 968 |
ORDER BY startdate DESC LIMIT 1), |
|
| 945 | 969 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
| 946 | 970 |
@values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
|
| 947 | 971 |
$gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, $project_id);
|
| ... | ... | |
| 986 | 1010 |
$query = |
| 987 | 1011 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id) |
| 988 | 1012 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', |
| 989 |
(SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1), |
|
| 1013 |
(SELECT tax_id |
|
| 1014 |
FROM taxkeys |
|
| 1015 |
WHERE chart_id= (SELECT id |
|
| 1016 |
FROM chart |
|
| 1017 |
WHERE accno = ?) |
|
| 1018 |
AND startdate <= ? |
|
| 1019 |
ORDER BY startdate DESC LIMIT 1), |
|
| 990 | 1020 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
| 991 | 1021 |
@values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_i($project_id));
|
| 992 | 1022 |
do_query($form, $dbh, $query, @values); |
| sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql | ||
|---|---|---|
| 7 | 7 |
|
| 8 | 8 |
--Spalte mit Werten füllen: |
| 9 | 9 |
UPDATE acc_trans ac SET tax_id= |
| 10 |
(select tk.tax_id from taxkeys tk
|
|
| 11 |
where tk.taxkey_id=ac.taxkey
|
|
| 10 |
(SELECT tk.tax_id FROM taxkeys tk
|
|
| 11 |
WHERE tk.taxkey_id=ac.taxkey
|
|
| 12 | 12 |
AND tk.startdate <= COALESCE( |
| 13 |
(select ar.deliverydate from ar where ar.id=ac.trans_id),
|
|
| 14 |
(select ar.transdate from ar where ar.id=ac.trans_id),
|
|
| 15 |
(select ap.transdate from ap where ap.id=ac.trans_id),
|
|
| 16 |
(select gl.transdate from gl where gl.id=ac.trans_id),
|
|
| 13 |
(SELECT ar.deliverydate FROM ar WHERE ar.id=ac.trans_id),
|
|
| 14 |
(SELECT ar.transdate FROM ar WHERE ar.id=ac.trans_id),
|
|
| 15 |
(SELECT ap.transdate FROM ap WHERE ap.id=ac.trans_id),
|
|
| 16 |
(SELECT gl.transdate FROM gl WHERE gl.id=ac.trans_id),
|
|
| 17 | 17 |
ac.transdate ) |
| 18 |
order by startdate desc limit 1);
|
|
| 18 |
ORDER BY startdate DESC LIMIT 1);
|
|
| 19 | 19 |
|
| 20 | 20 |
--Spalten, die noch null sind (nur bei Einträgen möglich, wo auch taxkey null ist) |
| 21 |
UPDATE acc_trans SET tax_id= (SELECT id FROM tax WHERE taxkey=0 LIMIT 1) where tax_id is null;
|
|
| 21 |
UPDATE acc_trans SET tax_id= (SELECT id FROM tax WHERE taxkey=0 LIMIT 1) WHERE tax_id IS NULL;
|
|
| 22 | 22 |
|
| 23 | 23 |
--tax_id als Pflichtfeld definieren: |
| 24 | 24 |
ALTER TABLE acc_trans ALTER tax_id SET NOT NULL; |
Auch abrufbar als: Unified diff
Bessere Formatierung
Verbessert die Formatierung von Commit 5c5e92bc37819dd9df952068b9e58b47dd0b0de2
(Großschreibung von SQL-Befehlen, Zeilenumbrüche bei langen Sub-Queries)