$form->{taxincluded} = 0 if ($form->{amount} == 0);
for $i (1 .. $form->{rowcount}) {
- ($form->{"tax_id_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"};
+ ($form->{"tax_id_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"};
$query =
qq|SELECT c.accno, t.taxkey, t.rate | .
WHERE id = ?|;
my @values = ($form->{invnumber}, conv_date($form->{transdate}),
$form->{ordnumber}, conv_i($form->{vendor_id}),
- $form->{taxincluded}, $form->{invtotal},
+ $form->{taxincluded} ? 't' : 'f', $form->{invtotal},
conv_date($form->{duedate}), $form->{invpaid},
conv_date($datepaid), $form->{netamount},
$form->{currency}, $form->{notes},
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, taxkey) | .
+ qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, | .
+ qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|;
@values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables},
- conv_date($form->{transdate}), conv_i($project_id));
+ conv_date($form->{transdate}), $form->{AP_amounts}{payables});
do_query($form, $dbh, $query, @values);
# if there is no amount but a payment record a payable
# add paid transactions
for my $i (1 .. $form->{paidaccounts}) {
if ($form->{"paid_$i"} != 0) {
+ my $project_id = conv_i($form->{"paid_project_id_$i"});
$exchangerate = 0;
if ($form->{currency} eq $form->{defaultcurrency}) {
2);
if ($form->{payables}) {
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) | .
- qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?)|;
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) | .
+ qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, | .
+ qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|;
@values = ($form->{id}, $form->{AP}{payables}, $amount,
- conv_date($form->{"datepaid_$i"}));
+ conv_date($form->{"datepaid_$i"}), $project_id,
+ $form->{AP}{payables});
do_query($form, $dbh, $query, @values);
}
$form->{payables} = $amount;
# add payment
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo) | .
- qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?)|;
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id, taxkey) | .
+ qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, | .
+ qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|;
@values = ($form->{id}, $form->{AP}{"paid_$i"}, $form->{"paid_$i"},
conv_date($form->{"datepaid_$i"}), $form->{"source_$i"},
- $form->{"memo_$i"});
+ $form->{"memo_$i"}, $project_id, $form->{AP}{"paid_$i"});
do_query($form, $dbh, $query, @values);
# add exchange rate difference
($form->{"exchangerate_$i"} - 1), 2);
if ($amount != 0) {
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared) | .
- qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, 't', 'f')|;
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey) | .
+ qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | .
+ qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|;
@values = ($form->{id}, $form->{AP}{"paid_$i"}, $amount,
- conv_date($form->{"datepaid_$i"}));
+ conv_date($form->{"datepaid_$i"}), $project_id,
+ $form->{AP}{"paid_$i"});
do_query($form, $dbh, $query, @values);
}
if ($amount != 0) {
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared) | .
- qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, 't', 'f')|;
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey) | .
+ qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | .
+ qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|;
@values = ($form->{id}, ($amount > 0) ?
$form->{fxgain_accno} : $form->{fxloss_accno},
- $amount, conv_date($form->{"datepaid_$i"}));
+ $amount, conv_date($form->{"datepaid_$i"}), $project_id,
+ ($amount > 0) ?
+ $form->{fxgain_accno} : $form->{fxloss_accno});
do_query($form, $dbh, $query, @values);
}
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 $where = qq| WHERE storno != true |;
my @values;
if ($form->{vendor_id}) {
$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 >= ?";
}
if ($where) {
- substr($where, 0, 4) = "WHERE";
+# substr($where, 0, 4) = "WHERE";
$query .= $where;
}
# connect to database, turn off autocommit
my $dbh = $form->dbconnect_noauto($myconfig);
- $form->{datepaid} = $form->{invdate};
+ $form->{datepaid} = $form->{transdate};
# total payments, don't move we need it here
for my $i (1 .. $form->{paidaccounts}) {
}
$form->{exchangerate} =
- $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
+ $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate},
"buy");
my (@values, $query);
+ my ($accno_ap) = split(/--/, $form->{APselected});
+
# record payments and offsetting AP
for my $i (1 .. $form->{paidaccounts}) {
if ($form->{"paid_$i"} != 0) {
my ($accno) = split /--/, $form->{"AP_paid_$i"};
- $form->{"datepaid_$i"} = $form->{invdate}
+ $form->{"datepaid_$i"} = $form->{transdate}
unless ($form->{"datepaid_$i"});
$form->{datepaid} = $form->{"datepaid_$i"};
$form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
2) * -1;
-
$query =
qq|DELETE FROM acc_trans | .
qq|WHERE trans_id = ? | .
qq| AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) | .
qq| AND amount = ? AND transdate = ?|;
- @values = ($form->{id}, $form->{AP}, $amount,
+ @values = ($form->{id}, $accno_ap, $amount,
conv_date($form->{"datepaid_$i"}));
do_query($form, $dbh, $query, @values);
$query =
- 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}, $amount,
- conv_date($form->{"datepaid_$i"}));
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) | .
+ qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, | .
+ qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|;
+ @values = ($form->{id}, $accno_ap, $amount,
+ conv_date($form->{"datepaid_$i"}),
+ conv_i($form->{"paid_project_id_$i"}), $accno_ap);
do_query($form, $dbh, $query, @values);
$query =
do_query($form, $dbh, $query, @values);
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo) | .
- qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?)|;
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id, taxkey) | .
+ qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, | .
+ qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|;
@values = ($form->{id}, $accno, $form->{"paid_$i"},
$form->{"datepaid_$i"},
- $form->{"source_$i"}, $form->{"memo_$i"});
+ $form->{"source_$i"}, $form->{"memo_$i"},
+ conv_i($form->{"paid_project_id_$i"}), $accno);
do_query($form, $dbh, $query, @values);
# gain/loss
do_query($form, $dbh, $query, @values);
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction) | .
- qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, 'f', 't')|;
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey) | .
+ qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, 'f', 't', | .
+ qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|;
@values = ($form->{id}, $accno, $form->{fx}{$accno}{$transdate},
- conv_date($transdate));
+ conv_date($transdate), $accno);
do_query($form, $dbh, $query, @values);
}
}