# connect to database
my $dbh = $form->dbconnect_noauto($myconfig);
- my @values = (conv_i($form->{id}));
- do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
- do_query($form, $dbh, qq|DELETE FROM gl WHERE id = ?|, @values);
+ # acc_trans entries are deleted by database triggers.
+ do_query($form, $dbh, qq|DELETE FROM gl WHERE id = ?|, conv_i($form->{id}));
# commit and redirect
my $rc = $dbh->commit;
if ($amount != 0) {
$query =
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo, project_id, taxkey, ob_transaction, cb_transaction, tax_id)
+ source, memo, project_id, taxkey, ob_transaction, cb_transaction)
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', conv_i($form->{"tax_id_$i"}));
+ $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $taxkey, $form->{ob_transaction} ? 't' : 'f', $form->{cb_transaction} ? 't' : 'f');
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, tax_id)
+ source, memo, project_id, taxkey)
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, conv_i($form->{"tax_id_$i"}));
+ $form->{"memo_$i"}, $project_id, $taxkey);
do_query($form, $dbh, $query, @values);
}
}
(SELECT c1.accno
FROM chart c1, tax t1
WHERE (t1.id = t.id) AND (c1.id = t.chart_id)) AS taxaccno,
- a.tax_id AS tax_id
+ (SELECT tk.tax_id
+ FROM taxkeys tk
+ WHERE (tk.chart_id = a.chart_id) AND (tk.startdate <= a.transdate)
+ ORDER BY tk.startdate desc LIMIT 1) AS tax_id
FROM acc_trans a
JOIN chart c ON (c.id = a.chart_id)
LEFT JOIN project p ON (p.id = a.project_id)
LEFT JOIN tax t ON
- (t.id = a.tax_id)
+ (t.id =
+ (SELECT tk.tax_id
+ FROM taxkeys tk
+ WHERE (tk.taxkey_id = a.taxkey) AND
+ ((CASE WHEN a.chart_id IN
+ (SELECT chart_id FROM taxkeys WHERE taxkey_id = a.taxkey)
+ THEN tk.chart_id = a.chart_id
+ ELSE 1 = 1
+ END)
+ OR (c.link LIKE '%tax%'))
+ AND (startdate <= a.transdate)
+ ORDER BY startdate DESC LIMIT 1))
WHERE (a.trans_id = ?)
AND (a.fx_transaction = '0')
ORDER BY a.acc_trans_id, a.transdate|;