use SL::MoreCommon;
use SL::DB::Default;
use SL::DB::Draft;
+use SL::DB::Order;
+use SL::DB::PurchaseInvoice;
use SL::Util qw(trim);
use SL::DB;
use Data::Dumper;
-
+use List::Util qw(sum0);
use strict;
sub post_transaction {
$query = qq|UPDATE ap SET invnumber = ?,
transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?,
- amount = ?, duedate = ?, paid = ?, netamount = ?,
+ amount = ?, duedate = ?, deliverydate = ?, tax_point = ?, paid = ?, netamount = ?,
currency_id = (SELECT id FROM currencies WHERE name = ?), notes = ?, department_id = ?, storno = ?, storno_id = ?,
- globalproject_id = ?, direct_debit = ?
+ globalproject_id = ?, direct_debit = ?, payment_id = ?
WHERE id = ?|;
@values = ($form->{invnumber}, conv_date($form->{transdate}),
$form->{ordnumber}, conv_i($form->{vendor_id}),
$form->{taxincluded} ? 't' : 'f', $form->{invtotal},
- conv_date($form->{duedate}), $form->{invpaid},
- $form->{netamount},
+ conv_date($form->{duedate}), conv_date($form->{deliverydate}), conv_date($form->{tax_point}),
+ $form->{invpaid}, $form->{netamount},
$form->{currency}, $form->{notes},
conv_i($form->{department_id}), $form->{storno},
$form->{storno_id}, conv_i($form->{globalproject_id}),
$form->{direct_debit} ? 't' : 'f',
+ conv_i($form->{payment_id}),
$form->{id});
do_query($form, $dbh, $query, @values);
$form->new_lastmtime('ap');
+ # Link this record to the record it was created from.
+ my $convert_from_oe_id = delete $form->{convert_from_oe_id};
+ if (!$form->{postasnew} && $convert_from_oe_id) {
+ RecordLinks->create_links('dbh' => $dbh,
+ 'mode' => 'ids',
+ 'from_table' => 'oe',
+ 'from_ids' => $convert_from_oe_id,
+ 'to_table' => 'ap',
+ 'to_id' => $form->{id},
+ );
+
+ # Close the record it was created from if the amount of
+ # all APs create from this record equals the records amount.
+ my @links = RecordLinks->get_links('dbh' => $dbh,
+ 'from_table' => 'oe',
+ 'from_id' => $convert_from_oe_id,
+ 'to_table' => 'ap',
+ );
+
+ my $amount_sum = sum0 map { SL::DB::PurchaseInvoice->new(id => $_->{to_id})->load->amount } @links;
+ my $order = SL::DB::Order->new(id => $convert_from_oe_id)->load;
+
+ $order->update_attributes(closed => 1) if ($amount_sum - $order->amount) == 0;
+ }
+
# add individual transactions
for my $i (1 .. $form->{rowcount}) {
if ($form->{"amount_$i"} != 0) {
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, a.storno, a.storno_id, a.direct_debit, | .
+ qq| a.transaction_description, a.itime::DATE AS insertdate, | .
qq| pr.projectnumber AS globalprojectnumber, | .
qq| e.name AS employee, | .
qq| v.vendornumber, v.country, v.ustid, | .
qq| tz.description AS taxzone, | .
qq| pt.description AS payment_terms, | .
+ qq| department.description AS department, | .
qq{ ( SELECT ch.accno || ' -- ' || ch.description
FROM acc_trans at
LEFT JOIN chart ch ON ch.id = at.chart_id
WHERE ch.link ~ 'AP[[:>:]]'
AND at.trans_id = a.id
LIMIT 1
- ) AS charts } .
+ ) AS charts, } .
+ qq{ ( SELECT ch.accno || ' -- ' || ch.description
+ FROM acc_trans at
+ LEFT JOIN chart ch ON ch.id = at.chart_id
+ WHERE ch.link ~ 'AP_amount'
+ AND at.trans_id = a.id
+ LIMIT 1
+ ) AS debit_chart } .
qq|FROM ap a | .
qq|JOIN vendor v ON (a.vendor_id = v.id) | .
qq|LEFT JOIN contacts cp ON (a.cp_id = cp.cp_id) | .
qq|LEFT JOIN employee e ON (a.employee_id = e.id) | .
qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id) | .
qq|LEFT JOIN tax_zones tz ON (tz.id = a.taxzone_id)| .
- qq|LEFT JOIN payment_terms pt ON (pt.id = a.payment_id)|;
+ qq|LEFT JOIN payment_terms pt ON (pt.id = a.payment_id)| .
+ qq|LEFT JOIN department ON (department.id = a.department_id)|;
my $where = '';
- unless ( $::auth->assert('show_ap_transactions', 1) ) {
- $where .= " AND NOT invoice = 'f' "; # remove ap transactions from Sales -> Reports -> Invoices
- };
-
my @values;
+ # Permissions:
+ # - Always return invoices & AP transactions for projects the employee has "view invoices" permissions for, no matter what the other rules say.
+ # - Exclude AP transactions if no permissions for them exist.
+ # - Limit to own invoices unless may edit all invoices.
+ # - If may edit all, allow filtering by employee.
+ my (@permission_where, @permission_values);
+
+ if ($::auth->assert('vendor_invoice_edit', 1)) {
+ if (!$::auth->assert('show_ap_transactions', 1)) {
+ push @permission_where, "NOT invoice = 'f'"; # remove ap transactions from Purchase -> Reports -> Invoices
+ }
+
+ if (!$::auth->assert('purchase_all_edit', 1)) {
+ # only show own invoices
+ push @permission_where, "a.employee_id = ?";
+ push @permission_values, SL::DB::Manager::Employee->current->id;
+
+ } else {
+ if ($form->{employee_id}) {
+ push @permission_where, "a.employee_id = ?";
+ push @permission_values, conv_i($form->{employee_id});
+ }
+ }
+ }
+
+ if (@permission_where || !$::auth->assert('vendor_invoice_edit', 1)) {
+ my $permission_where_str = @permission_where ? "OR (" . join(" AND ", map { "($_)" } @permission_where) . ")" : "";
+ $where .= qq|
+ AND ( (a.globalproject_id IN (
+ SELECT epi.project_id
+ FROM employee_project_invoices epi
+ WHERE epi.employee_id = ?))
+ $permission_where_str)
+ |;
+ push @values, SL::DB::Manager::Employee->current->id, @permission_values;
+ }
+
if ($form->{vendor}) {
$where .= " AND v.name ILIKE ?";
push(@values, like($form->{vendor}));
$where .= " AND a.ordnumber ILIKE ?";
push(@values, like($form->{ordnumber}));
}
+ if ($form->{transaction_description}) {
+ $where .= " AND a.transaction_description ILIKE ?";
+ push(@values, like($form->{transaction_description}));
+ }
if ($form->{notes}) {
- $where .= " AND lower(a.notes) LIKE ?";
+ $where .= " AND a.notes ILIKE ?";
push(@values, like($form->{notes}));
}
if ($form->{project_id}) {
$where .= " AND a.transdate <= ?";
push(@values, trim($form->{transdateto}));
}
+ if ($form->{duedatefrom}) {
+ $where .= " AND a.duedate >= ?";
+ push(@values, trim($form->{duedatefrom}));
+ }
+ if ($form->{duedateto}) {
+ $where .= " AND a.duedate <= ?";
+ push(@values, trim($form->{duedateto}));
+ }
if ($form->{open} || $form->{closed}) {
unless ($form->{open} && $form->{closed}) {
$where .= " AND a.amount <> a.paid" if ($form->{open});
}
if ($where) {
- substr($where, 0, 4, " WHERE ");
+ $where =~ s{\s*AND\s*}{ WHERE };
$query .= $where;
}
my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
my $sortorder = join(', ', map { "$_ $sortdir" } @a);
- if (grep({ $_ eq $form->{sort} } qw(transdate id invnumber ordnumber name netamount tax amount paid datepaid due duedate notes employee transaction_description direct_debit))) {
+ if (grep({ $_ eq $form->{sort} } qw(transdate id invnumber ordnumber name netamount tax amount paid datepaid due duedate notes employee transaction_description direct_debit department))) {
$sortorder = $form->{sort} . " $sortdir";
}
$storno_row->{netamount} *= -1;
$storno_row->{paid} = $storno_row->{amount};
- delete @$storno_row{qw(itime mtime)};
+ delete @$storno_row{qw(itime mtime gldate)};
$query = sprintf 'INSERT INTO ap (%s) VALUES (%s)', join(', ', keys %$storno_row), join(', ', map '?', values %$storno_row);
do_query($form, $dbh, $query, (values %$storno_row));
}
for my $row (@$rowref) {
- delete @$row{qw(itime mtime link acc_trans_id)};
+ delete @$row{qw(itime mtime link acc_trans_id gldate)};
$query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row);
$row->{trans_id} = $new_id;
$row->{amount} *= -1;