From 03ff37cb621e68f7d224d2520099ee86f612f833 Mon Sep 17 00:00:00 2001 From: Niclas Zimmermann Date: Tue, 11 Dec 2012 13:45:27 +0100 Subject: [PATCH] tax_id in acc_trans MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Dieser Commit enthält das Update-Script um eine neue Spalte Namens 'tax_id' in die Tabelle 'acc_trans' einzufügen. Diese Spalte hat Constrait NOT NULL und einen Fremdschlüssel auf die id von tax. Das Pflichtfeld wird jetzt immer geschrieben bei: -VK-Rechnungen (auch automatisch erzeugten) -EK-Rechnungen -Kreditorenbuchungen -Debitorenbuchungen -Dialogbuchungen Durch das Feld 'tax_id' können in der Tabelle tax der Steuerschlüssel und der Steuersatz ausgelesen werden. In diesem Commit wird allerdings der Wert in tax_id nur geschrieben, im Moment wird er noch nirgendwo ausgelesen. --- SL/AP.pm | 50 +++++++++++-------- SL/AR.pm | 50 +++++++++++-------- SL/DB/Helper/PriceTaxCalculator.pm | 2 +- SL/DB/Invoice.pm | 5 +- SL/DB/MetaSetup/AccTransaction.pm | 3 +- SL/GL.pm | 12 ++--- SL/IR.pm | 38 ++++++++------ SL/IS.pm | 37 ++++++++------ .../add_fkey_tax_id_to_acc_trans.sql | 5 ++ sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql | 25 ++++++++++ 10 files changed, 144 insertions(+), 83 deletions(-) create mode 100644 sql/Pg-upgrade2/add_fkey_tax_id_to_acc_trans.sql create mode 100644 sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql diff --git a/SL/AP.pm b/SL/AP.pm index 44ddc110c..091e1c27f 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -208,24 +208,24 @@ sub post_transaction { # insert detail records in acc_trans $query = qq|INSERT INTO acc_trans | . - qq| (trans_id, chart_id, amount, transdate, project_id, taxkey)| . + qq| (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id)| . qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | . - qq| ?, ?, ?, ?)|; + qq| ?, ?, ?, ?, ?)|; @values = ($form->{id}, $form->{AP_amounts}{"amount_$i"}, $form->{"amount_$i"}, conv_date($form->{transdate}), - $project_id, $form->{"taxkey_$i"}); + $project_id, $form->{"taxkey_$i"}, conv_i($form->{"tax_id_$i"})); do_query($form, $dbh, $query, @values); if ($form->{"tax_$i"} != 0) { # insert detail records in acc_trans $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, | . - qq| project_id, taxkey) | . + qq| project_id, taxkey, tax_id) | . qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | . - qq| ?, ?, ?, ?)|; + qq| ?, ?, ?, ?, ?)|; @values = ($form->{id}, $form->{AP_amounts}{"tax_$i"}, $form->{"tax_$i"}, conv_date($form->{transdate}), - $project_id, $form->{"taxkey_$i"}); + $project_id, $form->{"taxkey_$i"}, conv_i($form->{"tax_id_$i"})); do_query($form, $dbh, $query, @values); } @@ -234,11 +234,12 @@ sub post_transaction { # add payables $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey) | . + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, | . - qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|; + qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| . + qq| (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables}, - conv_date($form->{transdate}), $form->{AP_amounts}{payables}); + conv_date($form->{transdate}), $form->{AP_amounts}{payables}, $form->{AP_amounts}{payables}, conv_date($form->{transdate})); do_query($form, $dbh, $query, @values); } @@ -282,12 +283,13 @@ sub post_transaction { 2); if ($form->{payables}) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) | . + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, | . - qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|; + qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| . + qq| (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = ($form->{id}, $form->{AP_payables}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, - $form->{AP_payables}); + $form->{AP_payables}, $form->{AP_payables}, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); } $form->{payables} = $amount; @@ -295,12 +297,14 @@ sub post_transaction { # add payment my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig)); $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey) | . + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, | . - qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|; + qq| (SELECT taxkey_id FROM chart WHERE accno = ?), | . + qq| (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $form->{"paid_$i"}, conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"}, - $form->{"memo_$i"}, $project_id, $form->{"AP_paid_account_$i"}); + $form->{"memo_$i"}, $project_id, $form->{"AP_paid_account_$i"}, + $form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); # add exchange rate difference @@ -309,12 +313,14 @@ sub post_transaction { ($form->{"exchangerate_$i"} - 1), 2); if ($amount != 0) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey) | . + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | . - qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|; + qq| (SELECT taxkey_id FROM chart WHERE accno = ?), | . + qq| (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, - $form->{"AP_paid_account_$i"}); + $form->{"AP_paid_account_$i"}, + $form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); } @@ -326,14 +332,16 @@ sub post_transaction { if ($amount != 0) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey) | . + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | . - qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|; + qq| (SELECT taxkey_id FROM chart WHERE accno = ?)| . + qq| (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = ($form->{id}, ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, ($amount > 0) ? - $form->{fxgain_accno} : $form->{fxloss_accno}); + $form->{fxgain_accno} : $form->{fxloss_accno}, + ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); } diff --git a/SL/AR.pm b/SL/AR.pm index 5b82ff108..735f6b9f4 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -172,27 +172,29 @@ sub post_transaction { my $project_id = conv_i($form->{"project_id_$i"}); # insert detail records in acc_trans - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) - VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?)|; + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id) + VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?)|; @values = (conv_i($form->{id}), $form->{AR_amounts}{"amount_$i"}, conv_i($form->{"amount_$i"}), conv_date($form->{transdate}), $project_id, - conv_i($form->{"taxkey_$i"})); + conv_i($form->{"taxkey_$i"}), conv_i($form->{"tax_id_$i"})); do_query($form, $dbh, $query, @values); if ($form->{"tax_$i"} != 0) { # insert detail records in acc_trans - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) - VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?)|; + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id) + VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?)|; @values = (conv_i($form->{id}), $form->{AR_amounts}{"tax_$i"}, conv_i($form->{"tax_$i"}), conv_date($form->{transdate}), $project_id, - conv_i($form->{"taxkey_$i"})); + conv_i($form->{"taxkey_$i"}), conv_i($form->{"tax_id_$i"})); do_query($form, $dbh, $query, @values); } } } # add recievables - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|; - @values = (conv_i($form->{id}), $form->{AR_amounts}{receivables}, conv_i($form->{receivables}), conv_date($form->{transdate}), $form->{AR_amounts}{receivables}); + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?), + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = (conv_i($form->{id}), $form->{AR_amounts}{receivables}, conv_i($form->{receivables}), conv_date($form->{transdate}), + $form->{AR_amounts}{receivables}, $form->{AR_amounts}{receivables}, conv_date($form->{transdate})); do_query($form, $dbh, $query, @values); } else { @@ -229,9 +231,10 @@ sub post_transaction { if ($amount != 0) { # add receivable - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|; - @values = (conv_i($form->{id}), $form->{AR}{receivables}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AR}{receivables}); + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?), + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @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"})); do_query($form, $dbh, $query, @values); } @@ -240,18 +243,22 @@ sub post_transaction { my $project_id = conv_i($form->{"paid_project_id_$i"}); my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig)); $amount = $form->{"paid_$i"} * -1; - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|; - @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"}); + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?), + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @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"}, + $form->{AR}{"paid_$i"}, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); # exchangerate difference for payment $amount = $form->round_amount( $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) * -1, 2); if ($amount != 0) { - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|; - @values = (conv_i($form->{id}), $form->{AR}{"paid_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AR}{"paid_$i"}); + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?), + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = (conv_i($form->{id}), $form->{AR}{"paid_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AR}{"paid_$i"}, + $form->{AR}{"paid_$i"}, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); } @@ -260,9 +267,10 @@ sub post_transaction { if ($amount != 0) { my $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}; - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|; - @values = (conv_i($form->{id}), $accno, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $accno); + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?), + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = (conv_i($form->{id}), $accno, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $accno, $accno, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); } } diff --git a/SL/DB/Helper/PriceTaxCalculator.pm b/SL/DB/Helper/PriceTaxCalculator.pm index 8bd03598d..0801299fb 100644 --- a/SL/DB/Helper/PriceTaxCalculator.pm +++ b/SL/DB/Helper/PriceTaxCalculator.pm @@ -119,7 +119,7 @@ sub _calculate_item { $self->netamount($self->netamount + $sellprice * $item->qty / $item->price_factor); my $chart = $item->part->get_chart(type => $data->{is_sales} ? 'income' : 'expense', taxzone => $self->taxzone_id); - $data->{amounts}->{ $chart->id } ||= { taxkey => $taxkey->taxkey_id, amount => 0 }; + $data->{amounts}->{ $chart->id } ||= { taxkey => $taxkey->taxkey_id, tax_id => $taxkey->tax_id, amount => 0 }; $data->{amounts}->{ $chart->id }->{amount} += $linetotal; push @{ $data->{assembly_items} }, []; diff --git a/SL/DB/Invoice.pm b/SL/DB/Invoice.pm index 516352e8a..dd26532e0 100644 --- a/SL/DB/Invoice.pm +++ b/SL/DB/Invoice.pm @@ -169,11 +169,14 @@ sub post { sub _post_add_acctrans { my ($self, $entries) = @_; + my $default_tax_id = SL::DB::Manager::Tax->find_by(taxkey => 0)->id; + while (my ($chart_id, $spec) = each %{ $entries }) { - $spec = { taxkey => 0, amount => $spec } unless ref $spec; + $spec = { taxkey => 0, tax_id => $default_tax_id, amount => $spec } unless ref $spec; SL::DB::AccTransaction->new(trans_id => $self->id, chart_id => $chart_id, amount => $spec->{amount}, + tax_id => $spec->{tax_id}, taxkey => $spec->{taxkey}, project_id => $self->globalproject_id, transdate => $self->transdate)->save; diff --git a/SL/DB/MetaSetup/AccTransaction.pm b/SL/DB/MetaSetup/AccTransaction.pm index 649b4647f..29d5c3534 100644 --- a/SL/DB/MetaSetup/AccTransaction.pm +++ b/SL/DB/MetaSetup/AccTransaction.pm @@ -26,12 +26,11 @@ __PACKAGE__->meta->setup( taxkey => { type => 'integer' }, itime => { type => 'timestamp', default => 'now()' }, mtime => { type => 'timestamp' }, + tax_id => { type => 'integer', not_null => 1 }, ], primary_key_columns => [ 'acc_trans_id' ], - allow_inline_column_values => 1, - foreign_keys => [ chart => { class => 'SL::DB::Chart', diff --git a/SL/GL.pm b/SL/GL.pm index bf9e1c96c..5783fbfd3 100644 --- a/SL/GL.pm +++ b/SL/GL.pm @@ -157,11 +157,11 @@ sub post_transaction { if ($amount != 0) { $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - source, memo, project_id, taxkey, ob_transaction, cb_transaction) + source, memo, project_id, taxkey, ob_transaction, cb_transaction, tax_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), - ?, ?, ?, ?, ?, ?, ?, ?)|; + ?, ?, ?, ?, ?, ?, ?, ?, ?)|; @values = (conv_i($form->{id}), $accno, $amount, conv_date($form->{transdate}), - $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $taxkey, $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f'); + $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $taxkey, $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f', conv_i($form->{"tax_id_$i"})); do_query($form, $dbh, $query, @values); } @@ -169,12 +169,12 @@ sub post_transaction { # add taxentry $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - source, memo, project_id, taxkey) + source, memo, project_id, taxkey, tax_id) VALUES (?, (SELECT chart_id FROM tax WHERE id = ?), - ?, ?, ?, ?, ?, ?)|; + ?, ?, ?, ?, ?, ?, ?)|; @values = (conv_i($form->{id}), conv_i($form->{"tax_id_$i"}), $tax, conv_date($form->{transdate}), $form->{"source_$i"}, - $form->{"memo_$i"}, $project_id, $taxkey); + $form->{"memo_$i"}, $project_id, $taxkey, conv_i($form->{"tax_id_$i"})); do_query($form, $dbh, $query, @values); } } diff --git a/SL/IR.pm b/SL/IR.pm index 04e071e27..b8a5faa30 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -269,13 +269,15 @@ sub post_invoice { # allocated >= 0 # add entry for inventory, this one is for the sold item - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey) VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?))|; - @values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}); + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?), + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{inventory_accno_id}, $ref->{transdate}); do_query($form, $dbh, $query, @values); # add expense - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey) VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?))|; - @values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}); + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?), + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}, $ref->{expense_accno_id}, $ref->{transdate}); do_query($form, $dbh, $query, @values); } }; @@ -478,11 +480,12 @@ sub post_invoice { next if $payments_only || !$form->{amount}{$trans_id}{$accno}; - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + (SELECT taxkey_id FROM chart WHERE accno = ?), ?, + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno}, - conv_date($form->{invdate}), $accno, $project_id); + conv_date($form->{invdate}), $accno, $project_id, $accno, conv_date($form->{invdate})); do_query($form, $dbh, $query, @values); } } @@ -517,11 +520,12 @@ sub post_invoice { # record AP if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) { - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + (SELECT taxkey_id FROM chart WHERE accno = ?), ?, + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = (conv_i($form->{id}), $form->{AP}, $amount, - $form->{"datepaid_$i"}, $form->{AP}, $project_id); + $form->{"datepaid_$i"}, $form->{AP}, $project_id, $form->{AP}, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); } @@ -529,11 +533,12 @@ sub post_invoice { my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig)); $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id) + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id, tax_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + (SELECT taxkey_id FROM chart WHERE accno = ?), ?, + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; @values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, - $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id); + $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id, $accno, conv_date($form->{"datepaid_$i"})); do_query($form, $dbh, $query, @values); $exchangerate = 0; @@ -571,9 +576,10 @@ sub post_invoice { $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2); next if ($form->{fx}{$accno}{$transdate} == 0); - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?)|; - @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id); + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id, tax_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?, + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id, $accno, $form->{fx}{$accno}{$transdate}); do_query($form, $dbh, $query, @values); } } diff --git a/SL/IS.pm b/SL/IS.pm index 28c4a6605..287a2613b 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -822,6 +822,8 @@ sub post_invoice { $project_id = conv_i($form->{"globalproject_id"}); + my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate}; + foreach my $trans_id (keys %{ $form->{amount_cogs} }) { foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) { next unless ($form->{expense_inventory} =~ /\Q$accno\E/); @@ -830,8 +832,8 @@ sub post_invoice { if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|; + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?)|; @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id)); do_query($form, $dbh, $query, @values); $form->{amount_cogs}{$trans_id}{$accno} = 0; @@ -843,8 +845,8 @@ sub post_invoice { if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|; + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?)|; @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id)); do_query($form, $dbh, $query, @values); } @@ -859,10 +861,11 @@ sub post_invoice { if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1), (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; - @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id)); + @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id)); do_query($form, $dbh, $query, @values); $form->{amount}{$trans_id}{$accno} = 0; } @@ -873,10 +876,11 @@ sub post_invoice { if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1), (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; - @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id)); + @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id)); do_query($form, $dbh, $query, @values); } } @@ -922,10 +926,11 @@ sub post_invoice { if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1), (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; - @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id); + @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, $project_id); do_query($form, $dbh, $query, @values); } @@ -934,11 +939,12 @@ sub post_invoice { my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig)); $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id) + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1), (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, - $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id); + $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, $project_id); do_query($form, $dbh, $query, @values); # exchangerate difference @@ -978,10 +984,11 @@ sub post_invoice { if ( $form->{fx}{$accno}{$transdate} != 0 ) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id) + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; - @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_i($project_id)); + (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1), + (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_i($project_id)); do_query($form, $dbh, $query, @values); } } diff --git a/sql/Pg-upgrade2/add_fkey_tax_id_to_acc_trans.sql b/sql/Pg-upgrade2/add_fkey_tax_id_to_acc_trans.sql new file mode 100644 index 000000000..a61001129 --- /dev/null +++ b/sql/Pg-upgrade2/add_fkey_tax_id_to_acc_trans.sql @@ -0,0 +1,5 @@ +-- @tag: add_fkey_tax_id_to_acc_trans +-- @description: Setzt einen Fremdschlüssel zu der Tabellenspalte tax_id in der acc_trans +-- @depends: release_3_0_0 + +ALTER TABLE acc_trans ADD FOREIGN KEY (tax_id) REFERENCES tax(id); diff --git a/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql b/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql new file mode 100644 index 000000000..a071442ba --- /dev/null +++ b/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql @@ -0,0 +1,25 @@ +-- @tag: add_tax_id_to_acc_trans +-- @description: Neue Spalte tax_id in der acc_trans +-- @depends: release_2_7_0 + + --Neue Spalte tax_id in acc_trans: + ALTER TABLE acc_trans ADD tax_id integer; + + --Spalte mit Werten füllen: + UPDATE acc_trans ac SET tax_id= + (select tk.tax_id from taxkeys tk + where tk.taxkey_id=ac.taxkey + AND tk.startdate <= COALESCE( + (select ar.deliverydate from ar where ar.id=ac.trans_id), + (select ar.transdate from ar where ar.id=ac.trans_id), + (select ap.transdate from ap where ap.id=ac.trans_id), + (select gl.transdate from gl where gl.id=ac.trans_id), + ac.transdate ) + order by startdate desc limit 1); + + --Spalten, die noch null sind (nur bei Einträgen möglich, wo auch taxkey null ist) + UPDATE acc_trans SET tax_id= (SELECT id FROM tax WHERE taxkey=0 LIMIT 1) where tax_id is null; + + --tax_id als Pflichtfeld definieren: + ALTER TABLE acc_trans ALTER tax_id SET NOT NULL; + -- 2.20.1