# check if we sold the item already and
# make an entry for the expense and inventory
+ my $taxzone = $form->{taxzone_id} * 1;
$query =
qq|SELECT i.id, i.qty, i.allocated, i.trans_id, i.base_qty,
- p.inventory_accno_id, p.expense_accno_id, a.transdate
- FROM invoice i, ar a, parts p
+ bg.inventory_accno_id, bg.expense_accno_id_${taxzone} AS expense_accno_id, a.transdate
+ FROM invoice i, ar a, parts p, buchungsgruppen bg
WHERE (i.parts_id = p.id)
AND (i.parts_id = ?)
AND ((i.base_qty + i.allocated) > 0)
AND (i.trans_id = a.id)
+ AND (p.buchungsgruppen_id = bg.id)
ORDER BY transdate|;
# ORDER BY transdate guarantees FIFO
# 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, tax_id) VALUES (?, ?, ?, ?,
- (SELECT taxkey_id
- FROM taxkeys
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,
+ (SELECT taxkey_id
+ FROM taxkeys
WHERE chart_id= ?
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
- (SELECT tax_id
- FROM taxkeys
+ (SELECT tax_id
+ FROM taxkeys
WHERE chart_id= ?
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
- (SELECT chart_link FROM chart WHERE id = ?))|;
- @values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate},
+ (SELECT link FROM chart WHERE id = ?))|;
+ @values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate},
$ref->{inventory_accno_id});
do_query($form, $dbh, $query, @values);
# add expense
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?,
- (SELECT taxkey_id
- FROM taxkeys
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,
+ (SELECT taxkey_id
+ FROM taxkeys
WHERE chart_id= ?
- AND startdate <= ?
+ AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
- (SELECT tax_id
- FROM taxkeys
+ (SELECT tax_id
+ FROM taxkeys
WHERE chart_id= ?
- AND startdate <= ?
+ AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
- (SELECT chart_link FROM chart WHERE id = ?))|;
+ (SELECT link FROM chart WHERE id = ?))|;
@values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}, $ref->{transdate}, $ref->{expense_accno_id}, $ref->{transdate},
$ref->{expense_accno_id});
do_query($form, $dbh, $query, @values);
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
- (SELECT taxkey_id
- FROM taxkeys
- WHERE chart_id= (SELECT id
- FROM chart
- WHERE accno = ?)
- AND startdate <= ?
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
?,
- (SELECT tax_id
- FROM taxkeys
- WHERE chart_id= (SELECT id
- FROM chart
- WHERE accno = ?)
- AND startdate <= ?
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
(SELECT link FROM chart WHERE accno = ?))|;
@values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
- (SELECT taxkey_id
- FROM taxkeys
- WHERE chart_id= (SELECT id
- FROM chart
- WHERE accno = ?)
- AND startdate <= ?
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
?,
- (SELECT tax_id
- FROM taxkeys
- WHERE chart_id= (SELECT id
- FROM chart
- WHERE accno = ?)
- AND startdate <= ?
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
(SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($form->{id}), $form->{AP}, $amount,
$query =
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id, tax_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
- (SELECT taxkey_id
- FROM taxkeys
- WHERE chart_id= (SELECT id
- FROM chart WHERE accno = ?)
- AND startdate <= ?
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart WHERE accno = ?)
+ AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
?,
- (SELECT tax_id
- FROM taxkeys
- WHERE chart_id= (SELECT id
- FROM chart WHERE accno = ?)
- AND startdate <= ?
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart WHERE accno = ?)
+ AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
(SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
my $transdate = "";
if ($form->{type} eq "invoice") {
- $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
+ $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
+ : $form->{invdate} ? $dbh->quote($form->{invdate})
+ : "current_date";
} else {
$transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
}
my $i = 0;
while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
- # if ($customertax{$ref->{accno}}) {
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
$i++;
$ptr->{accno} = $i;