($form->{terms}) = $sth->fetchrow_array;
$sth->finish;
+ my (@project_ids, %projectnumbers);
+
+ push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
+
+ # sort items by partsgroup
+ for $i (1 .. $form->{rowcount}) {
+ $partsgroup = "";
+ if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
+ $partsgroup = $form->{"partsgroup_$i"};
+ }
+ push @partsgroup, [$i, $partsgroup];
+ push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
+ }
+
+ if (@project_ids) {
+ $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
+ join(", ", map({ "?" } @project_ids)) . ")";
+ $sth = $dbh->prepare($query);
+ $sth->execute(@project_ids) ||
+ $form->dberror($query . " (" . join(", ", @project_ids) . ")");
+ while (my $ref = $sth->fetchrow_hashref()) {
+ $projectnumbers{$ref->{id}} = $ref->{projectnumber};
+ }
+ $sth->finish();
+ }
+
+ $form->{"globalprojectnumber"} =
+ $projectnumbers{$form->{"globalproject_id"}};
+
my $tax = 0;
my $item;
my $i;
my $subtotal_header = 0;
my $subposition = 0;
+ my @arrays =
+ qw(runningnumber number description longdescription qty ship unit bin
+ deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
+ partnotes serialnumber reqdate sellprice listprice netprice
+ discount p_discount discount_sub nodiscount_sub
+ linetotal nodiscount_linetotal tax_rate projectnumber);
+
+ my @tax_arrays =
+ qw(taxbase tax taxdescription taxrate taxnumber);
foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
$i = $item->[0];
push(@{ $form->{description} }, qq|$item->[1]|);
$sameitem = $item->[1];
- map { push(@{ $form->{$_} }, "") }
- qw(runningnumber number serialnumber bin partnotes qty unit deliverydate sellprice listprice netprice discount linetotal);
+ map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
}
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
push(@{ $form->{nodiscount_linetotal} },
$form->format_amount($myconfig, $nodiscount_linetotal, 2));
+ push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
-
@taxaccounts = split / /, $form->{"taxaccounts_$i"};
$taxrate = 0;
$taxdiff = 0;
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
- map { push(@{ $form->{$_} }, "") }
- qw(runningnumber number serialnumber unit qty bin sellprice listprice netprice discount linetotal nodiscount_linetotal);
+ map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
$sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
push(@{ $form->{description} }, $sameitem);
}
$form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
)
. qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
- map { push(@{ $form->{$_} }, "") }
- qw(number unit qty runningnumber serialnumber bin sellprice listprice netprice discount linetotal nodiscount_linetotal);
+ map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
}
$sth->finish;
}
# get rest for the customer
- my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes
+ my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes, ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
FROM customer ct
LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
WHERE ct.id = $form->{customer_id} $contact order by cp.cp_id limit 1|;
}
}
- $project_id = 'NULL';
- if ($form->{"projectnumber_$i"}) {
- $project_id = $form->{"projectnumber_$i"};
- }
$deliverydate =
($form->{"deliverydate_$i"})
? qq|'$form->{"deliverydate_$i"}'|
'$form->{"description_$i"}', '$form->{"longdescription_$i"}', $form->{"qty_$i"},
$form->{"sellprice_$i"}, $fxsellprice,
$form->{"discount_$i"}, $allocated, 'f',
- '$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"}', '$pricegroup_id',
'$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}', $baseqty, '$subtotal')|;
$dbh->do($query) || $form->dberror($query);
$form->{exchangerate}, 0);
}
+ $project_id = conv_i($form->{"globalproject_id"});
+
foreach my $trans_id (keys %{ $form->{amount} }) {
foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
next unless ($form->{expense_inventory} =~ /$accno/);
) != 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);
$form->{amount}{$trans_id}{$accno} = 0;
}
}
) != 0
) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, taxkey)
+ transdate, taxkey, project_id)
VALUES ($trans_id, (SELECT id FROM chart
WHERE 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->{AR} } != 0) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate)
+ transdate, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
WHERE c.accno = '$form->{AR}'),
- $amount, '$form->{"datepaid_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
+ $amount, '$form->{"datepaid_$i"}', ?)|;
+ do_query($form, $dbh, $query, $project_id);
}
# record payment
$form->{"paid_$i"} *= -1;
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo)
+ source, memo, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
WHERE c.accno = '$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"}', ?)|;
+ do_query($form, $dbh, $query, $project_id);
# exchangerate difference
$form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
) {
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, cleared, fx_transaction)
+ transdate, cleared, fx_transaction, 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', ?)|;
+ do_query($form, $dbh, $query, $project_id);
}
}
}
delivery_vendor_id = $form->{delivery_vendor_id},
employee_id = $form->{employee_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}
|;
$form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
"buy");
+ my $project_id = conv_i($form->{"globalproject_id"});
+
# record payments and offsetting AR
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, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
WHERE c.accno = '$form->{AR}'),
- $amount, '$form->{"datepaid_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
+ $amount, '$form->{"datepaid_$i"}', ?)|;
+ do_query($form, $dbh, $query, $project_id);
# record payment
$dbh->do($query) || $form->dberror($query);
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo)
+ source, memo, project_id)
VALUES ($form->{id}, (SELECT c.id FROM chart c
WHERE c.accno = '$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"}', ?)|;
+ do_query($form, $dbh, $query, $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, 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', ?)|;
+ do_query($form, $dbh, $query, $project_id);
}
}
}
# retrieve invoice
$query = qq|SELECT a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
- a.orddate, a.quodate,
+ a.orddate, a.quodate, a.globalproject_id,
a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,