Revision 9f07753b
Von Moritz Bunkus vor mehr als 11 Jahren hinzugefügt
| SL/AM.pm | ||
|---|---|---|
| 475 | 475 |
do_query($form, $dbh, $query, $form->{id});
|
| 476 | 476 |
} |
| 477 | 477 |
|
| 478 |
foreach my $table (qw(partstax customertax vendortax tax)) {
|
|
| 479 |
$query = qq|DELETE FROM $table |
|
| 480 |
WHERE chart_id = ?|; |
|
| 481 |
do_query($form, $dbh, $query, $form->{id});
|
|
| 482 |
} |
|
| 478 |
$query = qq|DELETE FROM tax |
|
| 479 |
WHERE chart_id = ?|; |
|
| 480 |
do_query($form, $dbh, $query, $form->{id});
|
|
| 483 | 481 |
|
| 484 | 482 |
# delete chart of account record |
| 485 | 483 |
$query = qq|DELETE FROM chart |
| ... | ... | |
| 1779 | 1777 |
taxkey, |
| 1780 | 1778 |
taxdescription, |
| 1781 | 1779 |
round(rate * 100, 2) AS rate, |
| 1782 |
chart_id,
|
|
| 1783 |
(id IN (SELECT tax_id
|
|
| 1780 |
chart_id, |
|
| 1781 |
(id IN (SELECT tax_id |
|
| 1784 | 1782 |
FROM acc_trans)) AS tax_already_used |
| 1785 | 1783 |
FROM tax |
| 1786 | 1784 |
WHERE id = ? |; |
| SL/DB/CustomerTax.pm | ||
|---|---|---|
| 1 |
# This file has been auto-generated only because it didn't exist. |
|
| 2 |
# Feel free to modify it at will; it will not be overwritten automatically. |
|
| 3 |
|
|
| 4 |
package SL::DB::CustomerTax; |
|
| 5 |
|
|
| 6 |
use strict; |
|
| 7 |
|
|
| 8 |
use SL::DB::MetaSetup::CustomerTax; |
|
| 9 |
|
|
| 10 |
# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. |
|
| 11 |
__PACKAGE__->meta->make_manager_class; |
|
| 12 |
|
|
| 13 |
1; |
|
| SL/DB/Helper/ALL.pm | ||
|---|---|---|
| 24 | 24 |
use SL::DB::CustomVariableConfig; |
| 25 | 25 |
use SL::DB::CustomVariableValidity; |
| 26 | 26 |
use SL::DB::Customer; |
| 27 |
use SL::DB::CustomerTax; |
|
| 28 | 27 |
use SL::DB::Datev; |
| 29 | 28 |
use SL::DB::Default; |
| 30 | 29 |
use SL::DB::DeliveryOrder; |
| ... | ... | |
| 57 | 56 |
use SL::DB::OrderItem; |
| 58 | 57 |
use SL::DB::Part; |
| 59 | 58 |
use SL::DB::PartsGroup; |
| 60 |
use SL::DB::PartsTax; |
|
| 61 | 59 |
use SL::DB::PaymentTerm; |
| 62 | 60 |
use SL::DB::PeriodicInvoice; |
| 63 | 61 |
use SL::DB::PeriodicInvoicesConfig; |
| ... | ... | |
| 86 | 84 |
use SL::DB::UnitsLanguage; |
| 87 | 85 |
use SL::DB::VC; |
| 88 | 86 |
use SL::DB::Vendor; |
| 89 |
use SL::DB::VendorTax; |
|
| 90 | 87 |
use SL::DB::Warehouse; |
| 91 | 88 |
|
| 92 | 89 |
1; |
| SL/DB/Helper/Mappings.pm | ||
|---|---|---|
| 56 | 56 |
custom_variable_configs => 'custom_variable_config', |
| 57 | 57 |
custom_variables => 'custom_variable', |
| 58 | 58 |
custom_variables_validity => 'custom_variable_validity', |
| 59 |
customertax => 'customer_tax', |
|
| 60 | 59 |
datev => 'datev', |
| 61 | 60 |
defaults => 'default', |
| 62 | 61 |
delivery_orders => 'delivery_order', |
| ... | ... | |
| 85 | 84 |
oe => 'order', |
| 86 | 85 |
parts => 'part', |
| 87 | 86 |
partsgroup => 'parts_group', |
| 88 |
partstax => 'parts_tax', |
|
| 89 | 87 |
payment_terms => 'payment_term', |
| 90 | 88 |
periodic_invoices => 'periodic_invoice', |
| 91 | 89 |
periodic_invoices_configs => 'periodic_invoices_config', |
| ... | ... | |
| 109 | 107 |
units => 'unit', |
| 110 | 108 |
units_language => 'units_language', |
| 111 | 109 |
vendor => 'vendor', |
| 112 |
vendortax => 'vendor_tax', |
|
| 113 | 110 |
); |
| 114 | 111 |
|
| 115 | 112 |
my (%lxoffice_tables_to_packages, %lxoffice_tables_to_manager_packages, %lxoffice_packages_to_tables); |
| SL/DB/MetaSetup/CustomerTax.pm | ||
|---|---|---|
| 1 |
# This file has been auto-generated. Do not modify it; it will be overwritten |
|
| 2 |
# by rose_auto_create_model.pl automatically. |
|
| 3 |
package SL::DB::CustomerTax; |
|
| 4 |
|
|
| 5 |
use strict; |
|
| 6 |
|
|
| 7 |
use base qw(SL::DB::Object); |
|
| 8 |
|
|
| 9 |
__PACKAGE__->meta->setup( |
|
| 10 |
table => 'customertax', |
|
| 11 |
|
|
| 12 |
columns => [ |
|
| 13 |
customer_id => { type => 'integer' },
|
|
| 14 |
chart_id => { type => 'integer' },
|
|
| 15 |
itime => { type => 'timestamp', default => 'now()' },
|
|
| 16 |
mtime => { type => 'timestamp' },
|
|
| 17 |
id => { type => 'serial', not_null => 1 },
|
|
| 18 |
], |
|
| 19 |
|
|
| 20 |
primary_key_columns => [ 'id' ], |
|
| 21 |
|
|
| 22 |
allow_inline_column_values => 1, |
|
| 23 |
); |
|
| 24 |
|
|
| 25 |
1; |
|
| 26 |
; |
|
| SL/DB/MetaSetup/PartsTax.pm | ||
|---|---|---|
| 1 |
# This file has been auto-generated. Do not modify it; it will be overwritten |
|
| 2 |
# by rose_auto_create_model.pl automatically. |
|
| 3 |
package SL::DB::PartsTax; |
|
| 4 |
|
|
| 5 |
use strict; |
|
| 6 |
|
|
| 7 |
use base qw(SL::DB::Object); |
|
| 8 |
|
|
| 9 |
__PACKAGE__->meta->setup( |
|
| 10 |
table => 'partstax', |
|
| 11 |
|
|
| 12 |
columns => [ |
|
| 13 |
parts_id => { type => 'integer' },
|
|
| 14 |
chart_id => { type => 'integer' },
|
|
| 15 |
itime => { type => 'timestamp', default => 'now()' },
|
|
| 16 |
mtime => { type => 'timestamp' },
|
|
| 17 |
id => { type => 'serial', not_null => 1 },
|
|
| 18 |
], |
|
| 19 |
|
|
| 20 |
primary_key_columns => [ 'id' ], |
|
| 21 |
|
|
| 22 |
allow_inline_column_values => 1, |
|
| 23 |
); |
|
| 24 |
|
|
| 25 |
1; |
|
| 26 |
; |
|
| SL/DB/MetaSetup/VendorTax.pm | ||
|---|---|---|
| 1 |
# This file has been auto-generated. Do not modify it; it will be overwritten |
|
| 2 |
# by rose_auto_create_model.pl automatically. |
|
| 3 |
package SL::DB::VendorTax; |
|
| 4 |
|
|
| 5 |
use strict; |
|
| 6 |
|
|
| 7 |
use base qw(SL::DB::Object); |
|
| 8 |
|
|
| 9 |
__PACKAGE__->meta->setup( |
|
| 10 |
table => 'vendortax', |
|
| 11 |
|
|
| 12 |
columns => [ |
|
| 13 |
vendor_id => { type => 'integer' },
|
|
| 14 |
chart_id => { type => 'integer' },
|
|
| 15 |
itime => { type => 'timestamp', default => 'now()' },
|
|
| 16 |
mtime => { type => 'timestamp' },
|
|
| 17 |
id => { type => 'serial', not_null => 1 },
|
|
| 18 |
], |
|
| 19 |
|
|
| 20 |
primary_key_columns => [ 'id' ], |
|
| 21 |
|
|
| 22 |
allow_inline_column_values => 1, |
|
| 23 |
); |
|
| 24 |
|
|
| 25 |
1; |
|
| 26 |
; |
|
| SL/DB/PartsTax.pm | ||
|---|---|---|
| 1 |
# This file has been auto-generated only because it didn't exist. |
|
| 2 |
# Feel free to modify it at will; it will not be overwritten automatically. |
|
| 3 |
|
|
| 4 |
package SL::DB::PartsTax; |
|
| 5 |
|
|
| 6 |
use strict; |
|
| 7 |
|
|
| 8 |
use SL::DB::MetaSetup::PartsTax; |
|
| 9 |
|
|
| 10 |
# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. |
|
| 11 |
__PACKAGE__->meta->make_manager_class; |
|
| 12 |
|
|
| 13 |
1; |
|
| SL/DB/VendorTax.pm | ||
|---|---|---|
| 1 |
# This file has been auto-generated only because it didn't exist. |
|
| 2 |
# Feel free to modify it at will; it will not be overwritten automatically. |
|
| 3 |
|
|
| 4 |
package SL::DB::VendorTax; |
|
| 5 |
|
|
| 6 |
use strict; |
|
| 7 |
|
|
| 8 |
use SL::DB::MetaSetup::VendorTax; |
|
| 9 |
|
|
| 10 |
# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. |
|
| 11 |
__PACKAGE__->meta->make_manager_class; |
|
| 12 |
|
|
| 13 |
1; |
|
| SL/IC.pm | ||
|---|---|---|
| 156 | 156 |
} |
| 157 | 157 |
$trq->finish; |
| 158 | 158 |
|
| 159 |
# now get accno for taxes |
|
| 160 |
$query = |
|
| 161 |
qq|SELECT c.accno |
|
| 162 |
FROM chart c, partstax pt |
|
| 163 |
WHERE (pt.chart_id = c.id) AND (pt.parts_id = ?)|; |
|
| 164 |
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 165 |
while (my ($key) = $sth->fetchrow_array) {
|
|
| 166 |
$form->{amount}{$key} = $key;
|
|
| 167 |
} |
|
| 168 |
|
|
| 169 |
$sth->finish; |
|
| 170 |
|
|
| 171 | 159 |
# is it an orphan |
| 172 | 160 |
my @referencing_tables = qw(invoice orderitems inventory rmaitems); |
| 173 | 161 |
my %column_map = ( ); |
| ... | ... | |
| 295 | 283 |
do_query($form, $dbh, qq|DELETE FROM assembly WHERE id = ?|, conv_i($form->{id}));
|
| 296 | 284 |
} |
| 297 | 285 |
|
| 298 |
# delete tax records |
|
| 299 |
do_query($form, $dbh, qq|DELETE FROM partstax WHERE parts_id = ?|, conv_i($form->{id}));
|
|
| 300 |
|
|
| 301 | 286 |
# delete translations |
| 302 | 287 |
do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
|
| 303 | 288 |
|
| ... | ... | |
| 470 | 455 |
} |
| 471 | 456 |
} |
| 472 | 457 |
|
| 473 |
# insert taxes |
|
| 474 |
foreach my $item (split(/ /, $form->{taxaccounts})) {
|
|
| 475 |
if ($form->{"IC_tax_$item"}) {
|
|
| 476 |
$query = |
|
| 477 |
qq|INSERT INTO partstax (parts_id, chart_id) |
|
| 478 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?))|; |
|
| 479 |
@values = (conv_i($form->{id}), $item);
|
|
| 480 |
do_query($form, $dbh, $query, @values); |
|
| 481 |
} |
|
| 482 |
} |
|
| 483 |
|
|
| 484 | 458 |
# add assembly records |
| 485 | 459 |
if ($form->{item} eq 'assembly') {
|
| 486 | 460 |
|
| ... | ... | |
| 617 | 591 |
|
| 618 | 592 |
my %columns = ( "assembly" => "id", "parts" => "id" ); |
| 619 | 593 |
|
| 620 |
for my $table (qw(prices partstax makemodel inventory assembly translation parts)) {
|
|
| 594 |
for my $table (qw(prices makemodel inventory assembly translation parts)) {
|
|
| 621 | 595 |
my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
|
| 622 | 596 |
do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values); |
| 623 | 597 |
} |
| 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 (?, ?, ?, ?, |
| 273 |
(SELECT taxkey_id
|
|
| 274 |
FROM taxkeys
|
|
| 273 |
(SELECT taxkey_id |
|
| 274 |
FROM taxkeys |
|
| 275 | 275 |
WHERE chart_id= ? |
| 276 | 276 |
AND startdate <= ? |
| 277 | 277 |
ORDER BY startdate DESC LIMIT 1), |
| 278 |
(SELECT tax_id
|
|
| 279 |
FROM taxkeys
|
|
| 278 |
(SELECT tax_id |
|
| 279 |
FROM taxkeys |
|
| 280 | 280 |
WHERE chart_id= ? |
| 281 | 281 |
AND startdate <= ? |
| 282 | 282 |
ORDER BY startdate DESC LIMIT 1), |
| 283 | 283 |
(SELECT chart_link FROM chart WHERE id = ?))|; |
| 284 |
@values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate},
|
|
| 284 |
@values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate},
|
|
| 285 | 285 |
$ref->{inventory_accno_id});
|
| 286 | 286 |
do_query($form, $dbh, $query, @values); |
| 287 | 287 |
|
| 288 | 288 |
# add expense |
| 289 | 289 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?, |
| 290 |
(SELECT taxkey_id
|
|
| 291 |
FROM taxkeys
|
|
| 290 |
(SELECT taxkey_id |
|
| 291 |
FROM taxkeys |
|
| 292 | 292 |
WHERE chart_id= ? |
| 293 |
AND startdate <= ?
|
|
| 293 |
AND startdate <= ? |
|
| 294 | 294 |
ORDER BY startdate DESC LIMIT 1), |
| 295 |
(SELECT tax_id
|
|
| 296 |
FROM taxkeys
|
|
| 295 |
(SELECT tax_id |
|
| 296 |
FROM taxkeys |
|
| 297 | 297 |
WHERE chart_id= ? |
| 298 |
AND startdate <= ?
|
|
| 298 |
AND startdate <= ? |
|
| 299 | 299 |
ORDER BY startdate DESC LIMIT 1), |
| 300 | 300 |
(SELECT chart_link FROM chart WHERE id = ?))|; |
| 301 | 301 |
@values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}, $ref->{transdate}, $ref->{expense_accno_id}, $ref->{transdate},
|
| ... | ... | |
| 504 | 504 |
|
| 505 | 505 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id, chart_link) |
| 506 | 506 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
| 507 |
(SELECT taxkey_id
|
|
| 508 |
FROM taxkeys
|
|
| 509 |
WHERE chart_id= (SELECT id
|
|
| 510 |
FROM chart
|
|
| 511 |
WHERE accno = ?)
|
|
| 512 |
AND startdate <= ?
|
|
| 507 |
(SELECT taxkey_id |
|
| 508 |
FROM taxkeys |
|
| 509 |
WHERE chart_id= (SELECT id |
|
| 510 |
FROM chart |
|
| 511 |
WHERE accno = ?) |
|
| 512 |
AND startdate <= ? |
|
| 513 | 513 |
ORDER BY startdate DESC LIMIT 1), |
| 514 | 514 |
?, |
| 515 |
(SELECT tax_id
|
|
| 516 |
FROM taxkeys
|
|
| 517 |
WHERE chart_id= (SELECT id
|
|
| 518 |
FROM chart
|
|
| 519 |
WHERE accno = ?)
|
|
| 520 |
AND startdate <= ?
|
|
| 515 |
(SELECT tax_id |
|
| 516 |
FROM taxkeys |
|
| 517 |
WHERE chart_id= (SELECT id |
|
| 518 |
FROM chart |
|
| 519 |
WHERE accno = ?) |
|
| 520 |
AND startdate <= ? |
|
| 521 | 521 |
ORDER BY startdate DESC LIMIT 1), |
| 522 | 522 |
(SELECT link FROM chart WHERE accno = ?))|; |
| 523 | 523 |
@values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
|
| ... | ... | |
| 558 | 558 |
if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
|
| 559 | 559 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id, chart_link) |
| 560 | 560 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
| 561 |
(SELECT taxkey_id
|
|
| 562 |
FROM taxkeys
|
|
| 563 |
WHERE chart_id= (SELECT id
|
|
| 564 |
FROM chart
|
|
| 565 |
WHERE accno = ?)
|
|
| 566 |
AND startdate <= ?
|
|
| 561 |
(SELECT taxkey_id |
|
| 562 |
FROM taxkeys |
|
| 563 |
WHERE chart_id= (SELECT id |
|
| 564 |
FROM chart |
|
| 565 |
WHERE accno = ?) |
|
| 566 |
AND startdate <= ? |
|
| 567 | 567 |
ORDER BY startdate DESC LIMIT 1), |
| 568 | 568 |
?, |
| 569 |
(SELECT tax_id
|
|
| 570 |
FROM taxkeys
|
|
| 571 |
WHERE chart_id= (SELECT id
|
|
| 572 |
FROM chart
|
|
| 573 |
WHERE accno = ?)
|
|
| 574 |
AND startdate <= ?
|
|
| 569 |
(SELECT tax_id |
|
| 570 |
FROM taxkeys |
|
| 571 |
WHERE chart_id= (SELECT id |
|
| 572 |
FROM chart |
|
| 573 |
WHERE accno = ?) |
|
| 574 |
AND startdate <= ? |
|
| 575 | 575 |
ORDER BY startdate DESC LIMIT 1), |
| 576 | 576 |
(SELECT link FROM chart WHERE accno = ?))|; |
| 577 | 577 |
@values = (conv_i($form->{id}), $form->{AP}, $amount,
|
| ... | ... | |
| 585 | 585 |
$query = |
| 586 | 586 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id, tax_id, chart_link) |
| 587 | 587 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, |
| 588 |
(SELECT taxkey_id
|
|
| 589 |
FROM taxkeys
|
|
| 590 |
WHERE chart_id= (SELECT id
|
|
| 591 |
FROM chart WHERE accno = ?)
|
|
| 592 |
AND startdate <= ?
|
|
| 588 |
(SELECT taxkey_id |
|
| 589 |
FROM taxkeys |
|
| 590 |
WHERE chart_id= (SELECT id |
|
| 591 |
FROM chart WHERE accno = ?) |
|
| 592 |
AND startdate <= ? |
|
| 593 | 593 |
ORDER BY startdate DESC LIMIT 1), |
| 594 | 594 |
?, |
| 595 |
(SELECT tax_id
|
|
| 596 |
FROM taxkeys
|
|
| 597 |
WHERE chart_id= (SELECT id
|
|
| 598 |
FROM chart WHERE accno = ?)
|
|
| 599 |
AND startdate <= ?
|
|
| 595 |
(SELECT tax_id |
|
| 596 |
FROM taxkeys |
|
| 597 |
WHERE chart_id= (SELECT id |
|
| 598 |
FROM chart WHERE accno = ?) |
|
| 599 |
AND startdate <= ? |
|
| 600 | 600 |
ORDER BY startdate DESC LIMIT 1), |
| 601 | 601 |
(SELECT link FROM chart WHERE accno = ?))|; |
| 602 | 602 |
@values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
|
| ... | ... | |
| 1314 | 1314 |
my $i = 0; |
| 1315 | 1315 |
while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
|
| 1316 | 1316 |
|
| 1317 |
# if ($customertax{$ref->{accno}}) {
|
|
| 1318 | 1317 |
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
|
| 1319 | 1318 |
$i++; |
| 1320 | 1319 |
$ptr->{accno} = $i;
|
| SL/IS.pm | ||
|---|---|---|
| 863 | 863 |
$query = |
| 864 | 864 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link) |
| 865 | 865 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
| 866 |
(SELECT tax_id
|
|
| 867 |
FROM taxkeys
|
|
| 868 |
WHERE chart_id= (SELECT id
|
|
| 869 |
FROM chart
|
|
| 870 |
WHERE accno = ?)
|
|
| 871 |
AND startdate <= ?
|
|
| 866 |
(SELECT tax_id |
|
| 867 |
FROM taxkeys |
|
| 868 |
WHERE chart_id= (SELECT id |
|
| 869 |
FROM chart |
|
| 870 |
WHERE accno = ?) |
|
| 871 |
AND startdate <= ? |
|
| 872 | 872 |
ORDER BY startdate DESC LIMIT 1), |
| 873 | 873 |
(SELECT taxkey_id |
| 874 |
FROM taxkeys
|
|
| 875 |
WHERE chart_id= (SELECT id
|
|
| 876 |
FROM chart
|
|
| 877 |
WHERE accno = ?)
|
|
| 878 |
AND startdate <= ?
|
|
| 874 |
FROM taxkeys |
|
| 875 |
WHERE chart_id= (SELECT id |
|
| 876 |
FROM chart |
|
| 877 |
WHERE accno = ?) |
|
| 878 |
AND startdate <= ? |
|
| 879 | 879 |
ORDER BY startdate DESC LIMIT 1), |
| 880 | 880 |
?, |
| 881 | 881 |
(SELECT link FROM chart WHERE accno = ?))|; |
| ... | ... | |
| 892 | 892 |
$query = |
| 893 | 893 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link) |
| 894 | 894 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
| 895 |
(SELECT tax_id
|
|
| 896 |
FROM taxkeys
|
|
| 897 |
WHERE chart_id= (SELECT id
|
|
| 898 |
FROM chart
|
|
| 899 |
WHERE accno = ?)
|
|
| 900 |
AND startdate <= ?
|
|
| 895 |
(SELECT tax_id |
|
| 896 |
FROM taxkeys |
|
| 897 |
WHERE chart_id= (SELECT id |
|
| 898 |
FROM chart |
|
| 899 |
WHERE accno = ?) |
|
| 900 |
AND startdate <= ? |
|
| 901 | 901 |
ORDER BY startdate DESC LIMIT 1), |
| 902 |
(SELECT taxkey_id
|
|
| 903 |
FROM taxkeys
|
|
| 904 |
WHERE chart_id= (SELECT id
|
|
| 905 |
FROM chart
|
|
| 906 |
WHERE accno = ?)
|
|
| 907 |
AND startdate <= ?
|
|
| 902 |
(SELECT taxkey_id |
|
| 903 |
FROM taxkeys |
|
| 904 |
WHERE chart_id= (SELECT id |
|
| 905 |
FROM chart |
|
| 906 |
WHERE accno = ?) |
|
| 907 |
AND startdate <= ? |
|
| 908 | 908 |
ORDER BY startdate DESC LIMIT 1), |
| 909 | 909 |
?, |
| 910 | 910 |
(SELECT link FROM chart WHERE accno = ?))|; |
| ... | ... | |
| 956 | 956 |
$query = |
| 957 | 957 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link) |
| 958 | 958 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
| 959 |
(SELECT tax_id
|
|
| 960 |
FROM taxkeys
|
|
| 961 |
WHERE chart_id= (SELECT id
|
|
| 962 |
FROM chart
|
|
| 963 |
WHERE accno = ?)
|
|
| 964 |
AND startdate <= ?
|
|
| 959 |
(SELECT tax_id |
|
| 960 |
FROM taxkeys |
|
| 961 |
WHERE chart_id= (SELECT id |
|
| 962 |
FROM chart |
|
| 963 |
WHERE accno = ?) |
|
| 964 |
AND startdate <= ? |
|
| 965 | 965 |
ORDER BY startdate DESC LIMIT 1), |
| 966 |
(SELECT taxkey_id
|
|
| 967 |
FROM taxkeys
|
|
| 968 |
WHERE chart_id= (SELECT id
|
|
| 969 |
FROM chart
|
|
| 970 |
WHERE accno = ?)
|
|
| 971 |
AND startdate <= ?
|
|
| 966 |
(SELECT taxkey_id |
|
| 967 |
FROM taxkeys |
|
| 968 |
WHERE chart_id= (SELECT id |
|
| 969 |
FROM chart |
|
| 970 |
WHERE accno = ?) |
|
| 971 |
AND startdate <= ? |
|
| 972 | 972 |
ORDER BY startdate DESC LIMIT 1), |
| 973 | 973 |
?, |
| 974 | 974 |
(SELECT link FROM chart WHERE accno = ?))|; |
| ... | ... | |
| 983 | 983 |
$query = |
| 984 | 984 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link) |
| 985 | 985 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, |
| 986 |
(SELECT tax_id
|
|
| 987 |
FROM taxkeys
|
|
| 988 |
WHERE chart_id= (SELECT id
|
|
| 989 |
FROM chart
|
|
| 990 |
WHERE accno = ?)
|
|
| 991 |
AND startdate <= ?
|
|
| 986 |
(SELECT tax_id |
|
| 987 |
FROM taxkeys |
|
| 988 |
WHERE chart_id= (SELECT id |
|
| 989 |
FROM chart |
|
| 990 |
WHERE accno = ?) |
|
| 991 |
AND startdate <= ? |
|
| 992 | 992 |
ORDER BY startdate DESC LIMIT 1), |
| 993 |
(SELECT taxkey_id
|
|
| 994 |
FROM taxkeys
|
|
| 995 |
WHERE chart_id= (SELECT id
|
|
| 996 |
FROM chart
|
|
| 997 |
WHERE accno = ?)
|
|
| 998 |
AND startdate <= ?
|
|
| 993 |
(SELECT taxkey_id |
|
| 994 |
FROM taxkeys |
|
| 995 |
WHERE chart_id= (SELECT id |
|
| 996 |
FROM chart |
|
| 997 |
WHERE accno = ?) |
|
| 998 |
AND startdate <= ? |
|
| 999 | 999 |
ORDER BY startdate DESC LIMIT 1), |
| 1000 | 1000 |
?, |
| 1001 | 1001 |
(SELECT link FROM chart WHERE accno = ?))|; |
| ... | ... | |
| 1042 | 1042 |
$query = |
| 1043 | 1043 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link) |
| 1044 | 1044 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', |
| 1045 |
(SELECT tax_id
|
|
| 1046 |
FROM taxkeys
|
|
| 1047 |
WHERE chart_id= (SELECT id
|
|
| 1048 |
FROM chart
|
|
| 1049 |
WHERE accno = ?)
|
|
| 1050 |
AND startdate <= ?
|
|
| 1045 |
(SELECT tax_id |
|
| 1046 |
FROM taxkeys |
|
| 1047 |
WHERE chart_id= (SELECT id |
|
| 1048 |
FROM chart |
|
| 1049 |
WHERE accno = ?) |
|
| 1050 |
AND startdate <= ? |
|
| 1051 | 1051 |
ORDER BY startdate DESC LIMIT 1), |
| 1052 |
(SELECT taxkey_id
|
|
| 1053 |
FROM taxkeys
|
|
| 1054 |
WHERE chart_id= (SELECT id
|
|
| 1055 |
FROM chart
|
|
| 1056 |
WHERE accno = ?)
|
|
| 1057 |
AND startdate <= ?
|
|
| 1052 |
(SELECT taxkey_id |
|
| 1053 |
FROM taxkeys |
|
| 1054 |
WHERE chart_id= (SELECT id |
|
| 1055 |
FROM chart |
|
| 1056 |
WHERE accno = ?) |
|
| 1057 |
AND startdate <= ? |
|
| 1058 | 1058 |
ORDER BY startdate DESC LIMIT 1), |
| 1059 | 1059 |
?, |
| 1060 | 1060 |
(SELECT link FROM chart WHERE accno = ?))|; |
| ... | ... | |
| 2034 | 2034 |
my $i = 0; |
| 2035 | 2035 |
while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
|
| 2036 | 2036 |
|
| 2037 |
# if ($customertax{$ref->{accno}})
|
|
| 2038 | 2037 |
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
|
| 2039 | 2038 |
$i++; |
| 2040 | 2039 |
$ptr->{accno} = $i;
|
| SL/RP.pm | ||
|---|---|---|
| 522 | 522 |
FROM acc_trans acc |
| 523 | 523 |
INNER JOIN chart c ON (acc.chart_id = c.id AND c.link LIKE '%AR_paid%') |
| 524 | 524 |
WHERE 1=1 $inwhere AND acc.trans_id = ac.trans_id) |
| 525 |
/ (SELECT amount FROM ar WHERE id = ac.trans_id)
|
|
| 526 |
ELSE 0
|
|
| 525 |
/ (SELECT amount FROM ar WHERE id = ac.trans_id) |
|
| 526 |
ELSE 0 |
|
| 527 | 527 |
/* ar amount is zero, or we are checking with a non-ar-transaction, so we return 0 in both cases as multiplicator of ac.amount */ |
| 528 | 528 |
END |
| 529 | 529 |
) AS amount, c.$category |
| ... | ... | |
| 1392 | 1392 |
my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name)); |
| 1393 | 1393 |
$sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } qw(id transdate invnumber name netamount tax)));
|
| 1394 | 1394 |
|
| 1395 |
my $query = ''; |
|
| 1396 |
if ($form->{report} !~ /nontaxable/) {
|
|
| 1397 |
$query = |
|
| 1395 |
my $query = |
|
| 1398 | 1396 |
qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount, |
| 1399 | 1397 |
ac.amount * $ml AS tax |
| 1400 | 1398 |
FROM acc_trans ac |
| ... | ... | |
| 1416 | 1414 |
JOIN $table n ON (n.id = a.${table}_id)
|
| 1417 | 1415 |
JOIN ${table}tax t ON (t.${table}_id = n.id)
|
| 1418 | 1416 |
JOIN invoice i ON (i.trans_id = a.id) |
| 1419 |
JOIN partstax p ON (p.parts_id = i.parts_id) |
|
| 1420 | 1417 |
WHERE |
| 1421 | 1418 |
$where |
| 1422 | 1419 |
$accno |
| 1423 | 1420 |
AND (a.invoice = '1') |
| 1424 | 1421 |
ORDER BY $sortorder|; |
| 1425 |
} else {
|
|
| 1426 |
# only gather up non-taxable transactions |
|
| 1427 |
$query = |
|
| 1428 |
qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, a.invnumber, n.name, a.netamount |
|
| 1429 |
FROM acc_trans ac |
|
| 1430 |
JOIN ${arap} a ON (a.id = ac.trans_id)
|
|
| 1431 |
JOIN $table n ON (n.id = a.${table}_id)
|
|
| 1432 |
WHERE |
|
| 1433 |
$where |
|
| 1434 |
AND (a.invoice = '0') |
|
| 1435 |
AND (a.netamount = a.amount) |
|
| 1436 |
|
|
| 1437 |
UNION |
|
| 1438 |
|
|
| 1439 |
SELECT a.id, '1' AS invoice, $transdate AS transdate, a.invnumber, n.name, i.sellprice * i.qty AS netamount |
|
| 1440 |
FROM acc_trans ac |
|
| 1441 |
JOIN ${arap} a ON (a.id = ac.trans_id)
|
|
| 1442 |
JOIN $table n ON (n.id = a.${table}_id)
|
|
| 1443 |
JOIN invoice i ON (i.trans_id = a.id) |
|
| 1444 |
WHERE |
|
| 1445 |
$where |
|
| 1446 |
AND (a.invoice = '1') |
|
| 1447 |
AND ( |
|
| 1448 |
a.${table}_id NOT IN (SELECT ${table}_id FROM ${table}tax t (${table}_id))
|
|
| 1449 |
OR |
|
| 1450 |
i.parts_id NOT IN (SELECT parts_id FROM partstax p (parts_id)) |
|
| 1451 |
) |
|
| 1452 |
GROUP BY a.id, a.invnumber, $transdate, n.name, i.sellprice, i.qty |
|
| 1453 |
ORDER by $sortorder|; |
|
| 1454 |
} |
|
| 1455 | 1422 |
|
| 1456 | 1423 |
$form->{TR} = selectall_hashref_query($form, $dbh, $query);
|
| 1457 | 1424 |
|
| bin/mozilla/rp.pl | ||
|---|---|---|
| 94 | 94 |
# $locale->text('Receipts')
|
| 95 | 95 |
# $locale->text('Payments')
|
| 96 | 96 |
# $locale->text('Project Transactions')
|
| 97 |
# $locale->text('Non-taxable Sales')
|
|
| 98 |
# $locale->text('Non-taxable Purchases')
|
|
| 99 | 97 |
# $locale->text('Business evaluation')
|
| 100 | 98 |
|
| 101 | 99 |
# $form->parse_html_template('rp/html_report_susa')
|
| ... | ... | |
| 134 | 132 |
ap_aging => $::locale->text('Search AP Aging'),
|
| 135 | 133 |
tax_collected => $::locale->text('Tax collected'),
|
| 136 | 134 |
tax_paid => $::locale->text('Tax paid'),
|
| 137 |
nontaxable_sales => $::locale->text('Non-taxable Sales'),
|
|
| 138 |
nontaxable_purchases => $::locale->text('Non-taxable Purchases'),
|
|
| 139 | 135 |
receipts => $::locale->text('Receipts'),
|
| 140 | 136 |
payments => $::locale->text('Payments'),
|
| 141 | 137 |
projects => $::locale->text('Project Transactions'),
|
| locale/de/all | ||
|---|---|---|
| 1285 | 1285 |
'No vendor has been selected yet.' => 'Es wurde noch kein Lieferant ausgewählt.', |
| 1286 | 1286 |
'No warehouse has been created yet or the quantity of the bins is not configured yet.' => 'Es wurde noch kein Lager angelegt, bzw. die dazugehörigen Lagerplätze sind noch nicht konfiguriert.', |
| 1287 | 1287 |
'No.' => 'Position', |
| 1288 |
'Non-taxable Purchases' => 'Nicht zu versteuernde Einkäufe', |
|
| 1289 |
'Non-taxable Sales' => 'Nicht zu versteuernde Verkäufe', |
|
| 1290 | 1288 |
'None' => 'Kein', |
| 1291 | 1289 |
'Not Discountable' => 'Nicht rabattierfähig', |
| 1292 | 1290 |
'Not delivered' => 'Nicht geliefert', |
| sql/Pg-upgrade2/delete_customertax_vendortax_partstax.sql | ||
|---|---|---|
| 1 |
-- @tag: delete_customertax_vendortax_partstax |
|
| 2 |
-- @description: Alte Tabellen löschen |
|
| 3 |
-- @depends: release_3_0_0 |
|
| 4 |
-- @charset: utf-8 |
|
| 5 |
DROP TABLE customertax; |
|
| 6 |
DROP TABLE vendortax; |
|
| 7 |
DROP TABLE partstax; |
|
| templates/webpages/rp/report.html | ||
|---|---|---|
| 308 | 308 |
</td> |
| 309 | 309 |
</tr> |
| 310 | 310 |
[%- END %] |
| 311 |
|
|
| 312 |
[%- IF 0 # is_nontaxable %] |
|
| 313 |
<input type=hidden name=nextsub value=generate_tax_report> |
|
| 314 |
|
|
| 315 |
<input type=hidden name=db value=[% db %]> |
|
| 316 |
<input type=hidden name=sort value=transdate> |
|
| 317 |
<input type=hidden name=report value='[% report %]'> |
|
| 318 |
|
|
| 319 |
<tr> |
|
| 320 |
<th align=right>[% 'From' | $T8 %]</th> |
|
| 321 |
<td>[% L.date_tag('fromdate', fromdate) %]</td>
|
|
| 322 |
<th align=right>[% 'Bis' | $T8 %]</th> |
|
| 323 |
<td>[% L.date_tag('todate', todate) %]</td>
|
|
| 324 |
</tr> |
|
| 325 |
[%- PROCESS cash_or_accrual %] |
|
| 326 |
<tr> |
|
| 327 |
<th align=right>[% 'Include in Report' | $T8 %]</th> |
|
| 328 |
<td colspan=3> |
|
| 329 |
<table> |
|
| 330 |
<tr> |
|
| 331 |
<td><input name="l_id" class=checkbox type=checkbox value=Y></td> |
|
| 332 |
<td>[% 'ID' | $T8 %]</td> |
|
| 333 |
<td><input name="l_invnumber" class=checkbox type=checkbox value=Y checked></td> |
|
| 334 |
<td>[% 'Invoice' | $T8 %]</td> |
|
| 335 |
<td><input name="l_transdate" class=checkbox type=checkbox value=Y checked></td> |
|
| 336 |
<td>[% 'Date' | $T8 %]</td> |
|
| 337 |
</tr> |
|
| 338 |
<tr> |
|
| 339 |
<td><input name="l_name" class=checkbox type=checkbox value=Y checked></td> |
|
| 340 |
<td> |
|
| 341 |
[% LxERP.t8('Customer') IF db == 'ar' %]
|
|
| 342 |
[% LxERP.t8('Vendor') IF db == 'ap' %]
|
|
| 343 |
</td> |
|
| 344 |
<td><input name="l_netamount" class=checkbox type=checkbox value=Y checked></td> |
|
| 345 |
<td>[% 'Amount' | $T8 %]</td> |
|
| 346 |
<td><input name="l_amount" class=checkbox type=checkbox value=Y></td> |
|
| 347 |
<td>[% 'Total' | $T8 %]</td> |
|
| 348 |
</tr> |
|
| 349 |
<tr> |
|
| 350 |
<td><input name="l_subtotal" class=checkbox type=checkbox value=Y></td> |
|
| 351 |
<td>[% 'Subtotal' | $T8 %]</td> |
|
| 352 |
</tr> |
|
| 353 |
</table> |
|
| 354 |
</td> |
|
| 355 |
</tr> |
|
| 356 |
[%- END %] |
|
| 357 | 311 |
</table> |
| 358 | 312 |
|
| 359 | 313 |
<hr size=3 noshade> |
Auch abrufbar als: Unified diff
Überflüssige Tabellen customertax, vendortax, partstax entfernt
Fixt #2186.