for $i (1 .. $form->{rowcount}) {
if ($form->{"amount_$i"} != 0) {
my $project_id;
- if ("amount_$i" =~ /amount_/) {
- if ($form->{"project_id_$i"} && $form->{"projectnumber_$i"}) {
- $project_id = $form->{"project_id_$i"};
- }
- }
- if ("amount_$i" =~ /amount/) {
- $taxkey = $form->{AP_amounts}{"amount_$i"}{taxkey};
- }
+ $project_id = conv_i($form->{"project_id_$i"});
+ $taxkey = $form->{AP_amounts}{"amount_$i"}{taxkey};
# insert detail records in acc_trans
$query =
qq| ?, ?, ?, ?)|;
@values = ($form->{id}, $form->{AP_amounts}{"amount_$i"},
$form->{"amount_$i"}, conv_date($form->{transdate}),
- conv_i($project_id), $taxkey);
+ $project_id, $taxkey);
do_query($form, $dbh, $query, @values);
if ($form->{"tax_$i"} != 0) {
qq| ?, ?, ?, ?)|;
@values = ($form->{id}, $form->{AP_amounts}{"tax_$i"},
$form->{"tax_$i"}, conv_date($form->{transdate}),
- conv_date($project_id), $taxkey);
+ $project_id, $taxkey);
do_query($form, $dbh, $query, @values);
}
# add payables
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id) | .
- qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | .
- qq| ?, ?, ?)|;
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) | .
+ qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?)|;
@values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables},
- conv_date($form->{transdate}), conv_i($project_id));
+ conv_date($form->{transdate}));
do_query($form, $dbh, $query, @values);
# if there is no amount but a payment record a payable
my $query =
qq|SELECT a.id, a.invnumber, a.transdate, a.duedate, a.amount, a.paid, | .
qq| a.ordnumber, v.name, a.invoice, a.netamount, a.datepaid, a.notes, | .
+ qq| a.globalproject_id, | .
+ qq| pr.projectnumber AS globalprojectnumber, | .
qq| e.name AS employee | .
qq|FROM ap a | .
qq|JOIN vendor v ON (a.vendor_id = v.id) | .
- qq|LEFT JOIN employee e ON (a.employee_id = e.id)|;
+ qq|LEFT JOIN employee e ON (a.employee_id = e.id) | .
+ qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id) |;
my $where;
my @values;
$where .= " AND lower(a.notes) LIKE ?";
push(@values, $form->like($form->{notes}));
}
+ if ($form->{project_id}) {
+ $where .=
+ qq|AND ((a.globalproject_id = ?) OR EXISTS | .
+ qq| (SELECT * FROM invoice i | .
+ qq| WHERE i.project_id = ? AND i.trans_id = a.id))|;
+ push(@values, $form->{project_id}, $form->{project_id});
+ }
if ($form->{transdatefrom}) {
$where .= " AND a.transdate >= ?";