Revision 357d134d
Von Niclas Zimmermann vor mehr als 12 Jahren hinzugefügt
| SL/AP.pm | ||
|---|---|---|
|
# 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| ?, ?, ?, ?, (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
|
||
|
@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"}, $form->{AP_amounts}{"amount_$i"}, conv_date($form->{transdate}));
|
||
|
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| ?, ?, ?, ?, (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
|
||
|
@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"}, $form->{AP_amounts}{"tax_$i"}, conv_date($form->{transdate}));
|
||
|
do_query($form, $dbh, $query, @values);
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
|
||
|
# 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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
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 taxkey_id= (SELECT taxkey_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;
|
||
| ... | ... | |
|
# 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 taxkey_id= (SELECT taxkey_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
|
||
| ... | ... | |
|
($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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
|
||
|
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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
|
||
| SL/AR.pm | ||
|---|---|---|
|
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 taxkey_id= (SELECT taxkey_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 {
|
||
| ... | ... | |
|
|
||
|
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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
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 taxkey_id= (SELECT taxkey_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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
|
||
|
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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
}
|
||
| SL/DB/Helper/PriceTaxCalculator.pm | ||
|---|---|---|
|
$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} }, [];
|
||
| SL/DB/Invoice.pm | ||
|---|---|---|
|
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;
|
||
| SL/DB/MetaSetup/AccTransaction.pm | ||
|---|---|---|
|
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',
|
||
| SL/GL.pm | ||
|---|---|---|
|
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);
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
# 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);
|
||
|
}
|
||
|
}
|
||
| SL/IR.pm | ||
|---|---|---|
|
|
||
|
# 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 taxkey_id= (SELECT taxkey_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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
};
|
||
| ... | ... | |
|
|
||
|
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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
}
|
||
| ... | ... | |
|
|
||
|
# 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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
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 taxkey_id= (SELECT taxkey_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;
|
||
| ... | ... | |
|
$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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
}
|
||
| SL/IS.pm | ||
|---|---|---|
|
|
||
|
$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/);
|
||
| ... | ... | |
|
|
||
|
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;
|
||
| ... | ... | |
|
|
||
|
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);
|
||
|
}
|
||
| ... | ... | |
|
|
||
|
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 taxkey_id= (SELECT taxkey_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;
|
||
|
}
|
||
| ... | ... | |
|
|
||
|
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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
}
|
||
| ... | ... | |
|
|
||
|
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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
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 taxkey_id= (SELECT taxkey_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
|
||
| ... | ... | |
|
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 taxkey_id= (SELECT taxkey_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);
|
||
|
}
|
||
|
}
|
||
| sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql | ||
|---|---|---|
|
-- @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=0 where tax_id is null;
|
||
|
|
||
|
--tax_id als Pflichtfeld definieren:
|
||
|
ALTER TABLE acc_trans ALTER tax_id SET NOT NULL;
|
||
|
|
||
Auch abrufbar als: Unified diff
tax_id in acc_trans
Dieser Commit enthält das Update-Script um eine neue Spalte Namens
'tax_id' in die Tabelle 'acc_trans' einzufügen. Diese Spalte ist
ein Pflichtfeld.
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.