# 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 taxkeys
+ WHERE chart_id= ?
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (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},
+ $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) 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_id
+ FROM taxkeys
+ WHERE chart_id= ?
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (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->{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);
}
};
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, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ (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 <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT link FROM chart WHERE accno = ?))|;
@values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
- conv_date($form->{invdate}), $accno, $project_id);
+ conv_date($form->{invdate}), $accno, conv_date($form->{invdate}), $project_id, $accno, conv_date($form->{invdate}), $accno);
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, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ (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 <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($form->{id}), $form->{AP}, $amount,
- $form->{"datepaid_$i"}, $form->{AP}, $project_id);
+ $form->{"datepaid_$i"}, $form->{AP}, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AP}, conv_date($form->{"datepaid_$i"}), $form->{AP});
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, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ (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 <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT link 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($form->{"datepaid_$i"}), $project_id, $accno, conv_date($form->{"datepaid_$i"}), $accno);
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, chart_link)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?,
+ (SELECT id FROM tax WHERE taxkey=0 LIMIT 1),
+ (SELECT link FROM chart WHERE accno = ?))|;
+ @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id, $accno);
do_query($form, $dbh, $query, @values);
}
}
invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?,
intnotes = ?, curr = ?, storno_id = ?, storno = ?,
cp_id = ?, employee_id = ?, department_id = ?,
- globalproject_id = ?
+ globalproject_id = ?, direct_debit = ?
WHERE id = ?|;
@values = (
$form->{invnumber}, $form->{ordnumber}, $form->{quonumber}, conv_date($form->{invdate}),
$form->{intnotes}, $form->{currency}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f',
conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}),
conv_i($form->{globalproject_id}),
+ $form->{direct_debit} ? 't' : 'f',
conv_i($form->{id})
);
do_query($form, $dbh, $query, @values);
$query = qq|SELECT cp_id, invnumber, transdate AS invdate, duedate,
orddate, quodate, globalproject_id,
ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate,
- intnotes, curr AS currency
+ intnotes, curr AS currency, direct_debit
FROM ap
WHERE id = ?|;
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
v.id AS vendor_id, v.name AS vendor, v.discount as vendor_discount,
v.creditlimit, v.terms, v.notes AS intnotes,
v.email, v.cc, v.bcc, v.language_id, v.payment_id,
- v.street, v.zipcode, v.city, v.country, v.taxzone_id, v.curr,
+ v.street, v.zipcode, v.city, v.country, v.taxzone_id, v.curr, v.direct_debit,
$duedate + COALESCE(pt.terms_netto, 0) AS duedate,
b.description AS business
FROM vendor v
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;