? $exchangerate
: $form->parse_amount($myconfig, $form->{exchangerate});
+ $form->{exchangerate} = 1 unless ($form->{exchangerate} * 1);
+
for my $i (1 .. $form->{rowcount}) {
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
# add entry for inventory, this one is for the sold item
if ($linetotal != 0) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate)
+ transdate, taxkey)
VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
- $linetotal, '$ref->{transdate}')|;
+ $linetotal, '$ref->{transdate}', (SELECT taxkey_id FROM chart WHERE id = $ref->{inventory_accno_id}))|;
$dbh->do($query) || $form->dberror($query);
# add expense
}
- $project_id = 'NULL';
- if ($form->{"projectnumber_$i"}) {
- $project_id = $form->{"projectnumber_$i"};
- }
$deliverydate =
($form->{"deliverydate_$i"})
? qq|'$form->{"deliverydate_$i"}'|
VALUES ($form->{id}, $form->{"id_$i"},
'$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|, | . ($baseqty * -1) . qq|,
$form->{"sellprice_$i"}, $fxsellprice, $allocated,
- '$form->{"unit_$i"}', $deliverydate, (SELECT id FROM project WHERE projectnumber = '$project_id'),
+ '$form->{"unit_$i"}', $deliverydate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|,
'$form->{"serialnumber_$i"}')|;
$dbh->do($query) || $form->dberror($query);
}
}
+ $project_id = conv_i($form->{"globalproject_id"});
+
$form->{datepaid} = $form->{invdate};
# all amounts are in natural state, netamount includes the taxes
) != 0
) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, taxkey)
+ transdate, taxkey, project_id)
VALUES ($trans_id, (SELECT c.id FROM chart c
WHERE c.accno = '$accno'),
$form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
- (SELECT taxkey_id FROM chart WHERE accno = '$accno'))|;
- $dbh->do($query) || $form->dberror($query);
+ (SELECT taxkey_id FROM chart WHERE accno = '$accno'), ?)|;
+ do_query($form, $dbh, $query, $project_id);
}
}
}
if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate)
+ transdate, taxkey, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
- WHERE c.accno = '$form->{AP}'),
- $amount, '$form->{"datepaid_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
+ WHERE c.accno = ?),
+ $amount, '$form->{"datepaid_$i"}',
+ (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ do_query($form, $dbh, $query, $form->{AP}, $form->{AP}, $project_id);
}
# record payment
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo)
+ source, memo, taxkey, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
- WHERE c.accno = '$accno'),
+ WHERE c.accno = ?),
$form->{"paid_$i"}, '$form->{"datepaid_$i"}',
- '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
+ '$form->{"source_$i"}', '$form->{"memo_$i"}',
+ (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ do_query($form, $dbh, $query, $accno, $accno, $project_id);
$exchangerate = 0;
) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, cleared, fx_transaction)
+ transdate, cleared, fx_transaction, taxkey, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
WHERE c.accno = '$accno'),
- $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
- $dbh->do($query) || $form->dberror($query);
+ $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', 0, ?)|;
+ do_query($form, $dbh, $query, $project_id);
}
}
}
curr = '$form->{currency}',
department_id = $form->{department_id},
storno = '$form->{storno}',
+ globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
cp_id = | . conv_i($form->{cp_id}, 'NULL') . qq|
WHERE id = $form->{id}|;
$dbh->do($query) || $form->dberror($query);
# retrieve invoice
$query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
- a.orddate, a.quodate,
+ a.orddate, a.quodate, a.globalproject_id,
a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno, a.gldate,
a.intnotes, a.curr AS currency
FROM ap a
my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms,
v.email, v.cc, v.bcc, v.language_id, v.payment_id,
v.street, v.zipcode, v.city, v.country, v.taxzone_id,
- $duedate + v.terms AS duedate,
+ $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
b.description AS business
FROM vendor v
LEFT JOIN business b ON b.id = v.business_id
- WHERE v.id = $form->{vendor_id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
+ LEFT JOIN payment_terms pt ON v.payment_id = pt.id
+ WHERE v.id = ?|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id});
map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
$form->{creditremaining} = $form->{creditlimit};
- $query = qq|SELECT SUM(a.amount - a.paid)
- FROM ap a
- WHERE a.vendor_id = $form->{vendor_id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- ($form->{creditremaining}) -= $sth->fetchrow_array;
-
- $sth->finish;
+ $query = qq|SELECT SUM(a.amount - a.paid) FROM ap a WHERE a.vendor_id = ?|;
+ ($form->{creditremaining}) -= selectfirst_array_query($form, $dbh, $query, $form->{vendor_id});
$query = qq|SELECT o.amount,
- (SELECT e.sell FROM exchangerate e
- WHERE e.curr = o.curr
- AND e.transdate = o.transdate)
- FROM oe o
- WHERE o.vendor_id = $form->{vendor_id}
- AND o.quotation = '0'
- AND o.closed = '0'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my ($amount, $exch) = $sth->fetchrow_array) {
+ (SELECT e.sell FROM exchangerate e
+ WHERE e.curr = o.curr AND e.transdate = o.transdate)
+ FROM oe o WHERE o.vendor_id = ?
+ AND o.quotation = '0' AND o.closed = '0'|;
+ while (my ($amount, $exch) = selectfirst_array_query($form, $dbh, $query, $form->{vendor_id})) {
$exch = 1 unless $exch;
$form->{creditremaining} -= $amount * $exch;
}
- $sth->finish;
# get shipto if we do not convert an order or invoice
if (!$form->{shipto}) {
map { delete $form->{$_} }
qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
- $query = qq|SELECT s.* FROM shipto s
- WHERE s.trans_id = $form->{vendor_id} AND s.module= 'CT'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
+ $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module= 'CT'|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id});
map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
}
# get taxes for vendor
- $query = qq|SELECT c.accno
- FROM chart c
+ $query = qq|SELECT c.accno FROM chart c
JOIN vendortax v ON (v.chart_id = c.id)
- WHERE v.vendor_id = $form->{vendor_id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
+ WHERE v.vendor_id = ?|;
my $vendortax = ();
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $vendortax{ $ref->{accno} } = 1;
- }
- $sth->finish;
+ $ref = selectall_hashref_query($form, $dbh, $query, $form->{vendor_id});
+ map { $vendortax{ $_->{accno} } = 1 } @$ref;
if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
# setup last accounts used
- $query = qq|SELECT c.accno, c.description, c.link, c.category
+ $query = qq|SELECT c.id, c.accno, c.description, c.link, c.category
FROM chart c
JOIN acc_trans ac ON (ac.chart_id = c.id)
JOIN ap a ON (a.id = ac.trans_id)
- WHERE a.vendor_id = $form->{vendor_id}
- AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
+ WHERE a.vendor_id = ?
+ AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
AND a.id IN (SELECT max(a2.id) FROM ap a2
- WHERE a2.vendor_id = $form->{vendor_id})|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ WHERE a2.vendor_id = ?)|;
+ $refs = selectall_hashref_query($form, $dbh, $query, $form->{vendor_id}, $form->{vendor_id});
my $i = 0;
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ for $ref (@$refs) {
if ($ref->{category} eq 'E') {
$i++;
- $form->{"AP_amount_$i"} = "$ref->{accno}--$ref->{description}";
+
+ if ($form->{initial_transdate}) {
+ my $tax_query = qq|SELECT tk.tax_id, t.rate FROM taxkeys tk
+ LEFT JOIN tax t ON tk.tax_id = t.id
+ WHERE tk.chart_id = ? AND startdate <= ?
+ ORDER BY tk.startdate DESC LIMIT 1|;
+ my ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id},
+ $form->{initial_transdate});
+ $form->{"taxchart_$i"} = "${tax_id}--${rate}";
+ }
+
+ $form->{"AP_amount_$i"} = "$ref->{accno}--$tax_id";
}
if ($ref->{category} eq 'L') {
- $form->{APselected} = $form->{AP_1} =
- "$ref->{accno}--$ref->{description}";
+ $form->{APselected} = $form->{AP_1} = $ref->{accno};
}
}
- $sth->finish;
$form->{rowcount} = $i if ($i && !$form->{type});
}
$form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
"buy");
+ my $project_id = conv_i($form->{"globalproject_id"});
+
# record payments and offsetting AP
for my $i (1 .. $form->{paidaccounts}) {
$dbh->do($query) || $form->dberror($query);
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate)
+ transdate, taxkey, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
- WHERE c.accno = '$form->{AP}'),
- $amount, '$form->{"datepaid_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
+ WHERE c.accno = ?),
+ $amount, '$form->{"datepaid_$i"}',
+ (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ do_query($form, $dbh, $query, $form->{AP}, $form->{AP}, $project_id);
$dbh->do($query) || $form->dberror($query);
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo)
+ source, memo, taxkey, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
- WHERE c.accno = '$accno'),
+ WHERE c.accno = ?),
$form->{"paid_$i"}, '$form->{"datepaid_$i"}',
- '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
+ '$form->{"source_$i"}', '$form->{"memo_$i"}',
+ (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ do_query($form, $dbh, $query, $accno, $accno, $project_id);
# gain/loss
WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
$dbh->do($query) || $form->dberror($query);
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, cleared, fx_transaction)
+ transdate, cleared, fx_transaction, taxkey, project_id)
VALUES ($form->{id},
(SELECT c.id FROM chart c
- WHERE c.accno = '$accno'),
- $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
- $dbh->do($query) || $form->dberror($query);
+ WHERE c.accno = ?),
+ $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1',
+ (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ do_query($form, $dbh, $query, $accno, $accno, $project_id);
}
}
}
projects / kivitendo-erp.git / blobdiff