use Data::Dumper;
use SL::DATEV qw(:CONSTANTS);
use SL::DBUtils;
+use SL::DB::Draft;
use SL::IO;
use SL::MoreCommon;
use SL::DB::Default;
use strict;
sub post_transaction {
- my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
+ my ($self, $myconfig, $form, $provided_dbh, %params) = @_;
$main::lxdebug->enter_sub();
- my $rc = SL::DB->client->with_transaction(\&_post_transaction, $self, $myconfig, $form, $provided_dbh, $payments_only);
+ my $rc = SL::DB->client->with_transaction(\&_post_transaction, $self, $myconfig, $form, $provided_dbh, %params);
$::lxdebug->leave_sub;
return $rc;
}
sub _post_transaction {
- my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
+ my ($self, $myconfig, $form, $provided_dbh, %params) = @_;
+
+ my $payments_only = $params{payments_only};
my ($query, $sth, $null, $taxrate, $amount, $tax);
my $exchangerate = 0;
$form->parse_amount($myconfig, $form->{exchangerate}) );
# get the charts selected
- map { ($form->{AR_amounts}{"amount_$_"}) = split /--/, $form->{"AR_amount_$_"} } 1 .. $form->{rowcount};
-
- $form->{AR_amounts}{receivables} = $form->{ARselected};
- $form->{AR}{receivables} = $form->{ARselected};
+ $form->{AR_amounts}{"amount_$_"} = $form->{"AR_amount_chart_id_$_"} for (1 .. $form->{rowcount});
$form->{tax} = 0; # is this still needed?
}
$form->{paid} = $form->round_amount($form->{paid} * ($form->{exchangerate} || 1), 2);
- ($null, $form->{employee_id}) = split /--/, $form->{employee};
-
$form->get_employee($dbh) unless $form->{employee_id};
# if we have an id delete old records else make one
}
}
- # update department
- ($null, $form->{department_id}) = split(/--/, $form->{department});
-
# amount for AR account
$form->{receivables} = $form->round_amount($form->{amount}, 2) * -1;
$query =
qq|UPDATE ar set
invnumber = ?, ordnumber = ?, transdate = ?, customer_id = ?,
- taxincluded = ?, amount = ?, duedate = ?, paid = ?,
+ taxincluded = ?, amount = ?, duedate = ?, deliverydate = ?, tax_point = ?, paid = ?,
currency_id = (SELECT id FROM currencies WHERE name = ?),
netamount = ?, notes = ?, department_id = ?,
employee_id = ?, storno = ?, storno_id = ?, globalproject_id = ?,
- direct_debit = ?
+ direct_debit = ?, transaction_description = ?
WHERE id = ?|;
my @values = ($form->{invnumber}, $form->{ordnumber}, conv_date($form->{transdate}), conv_i($form->{customer_id}), $form->{taxincluded} ? 't' : 'f', $form->{amount},
- conv_date($form->{duedate}), $form->{paid},
+ conv_date($form->{duedate}), conv_date($form->{deliverydate}), conv_date($form->{tax_point}), $form->{paid},
$form->{currency},
$form->{netamount}, $form->{notes}, conv_i($form->{department_id}),
conv_i($form->{employee_id}), $form->{storno} ? 't' : 'f', $form->{storno_id},
- conv_i($form->{globalproject_id}), $form->{direct_debit} ? 't' : 'f', conv_i($form->{id}));
+ conv_i($form->{globalproject_id}), $form->{direct_debit} ? 't' : 'f', $form->{transaction_description},
+ conv_i($form->{id}));
do_query($form, $dbh, $query, @values);
# add individual transactions for AR, amount and taxes
# insert detail records in acc_trans
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id, chart_link)
- VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?, (SELECT c.link FROM chart c WHERE c.accno = ?))|;
+ VALUES (?, ?, ?, ?, ?, ?, ?, (SELECT c.link FROM chart c WHERE c.id = ?))|;
@values = (conv_i($form->{id}), $form->{AR_amounts}{"amount_$i"}, conv_i($form->{"amount_$i"}), conv_date($form->{transdate}), $project_id,
conv_i($form->{"taxkey_$i"}), conv_i($form->{"tax_id_$i"}), $form->{AR_amounts}{"amount_$i"});
do_query($form, $dbh, $query, @values);
# add recievables
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link)
- VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?),
+ VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?),
(SELECT tax_id
FROM taxkeys
- WHERE chart_id= (SELECT id
- FROM chart
- WHERE accno = ?)
+ WHERE chart_id = ?
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
- (SELECT c.link FROM chart c WHERE c.accno = ?))|;
- @values = (conv_i($form->{id}), $form->{AR_amounts}{receivables}, conv_i($form->{receivables}), conv_date($form->{transdate}),
- $form->{AR_amounts}{receivables}, $form->{AR_amounts}{receivables}, conv_date($form->{transdate}), $form->{AR_amounts}{receivables});
+ (SELECT c.link FROM chart c WHERE c.id = ?))|;
+ @values = (conv_i($form->{id}), $form->{AR_chart_id}, conv_i($form->{receivables}), conv_date($form->{transdate}),
+ $form->{AR_chart_id}, $form->{AR_chart_id}, conv_date($form->{transdate}), $form->{AR_chart_id});
do_query($form, $dbh, $query, @values);
} else {
$form->new_lastmtime('ar');
+ my %already_cleared = %{ $params{already_cleared} // {} };
+
# add paid transactions
for my $i (1 .. $form->{paidaccounts}) {
$form->{exchangerate} = $form->{"exchangerate_$i"}
if ($form->{amount} == 0 && $form->{netamount} == 0);
+ my $new_cleared = !$form->{"acc_trans_id_$i"} ? 'f'
+ : !$already_cleared{$form->{"acc_trans_id_$i"}} ? 'f'
+ : $already_cleared{$form->{"acc_trans_id_$i"}}->{amount} != $form->{"paid_$i"} * -1 ? 'f'
+ : $already_cleared{$form->{"acc_trans_id_$i"}}->{accno} != $form->{AR}{"paid_$i"} ? 'f'
+ : $already_cleared{$form->{"acc_trans_id_$i"}}->{cleared} ? 't'
+ : 'f';
+
# receivables amount
$amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
if ($amount != 0) {
# add receivable
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id, chart_link)
- VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?),
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, cleared, taxkey, tax_id, chart_link)
+ VALUES (?, ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?),
(SELECT tax_id
FROM taxkeys
- WHERE chart_id= (SELECT id
- FROM chart
- WHERE accno = ?)
+ WHERE chart_id = ?
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
- (SELECT c.link FROM chart c WHERE c.accno = ?))|;
- @values = (conv_i($form->{id}), $form->{AR}{receivables}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AR}{receivables}, $form->{AR}{receivables}, conv_date($form->{"datepaid_$i"}),
- $form->{AR}{receivables});
+ (SELECT c.link FROM chart c WHERE c.id = ?))|;
+ @values = (conv_i($form->{id}), $form->{AR_chart_id}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $new_cleared,
+ $form->{AR_chart_id}, $form->{AR_chart_id}, conv_date($form->{"datepaid_$i"}), $form->{AR_chart_id});
do_query($form, $dbh, $query, @values);
}
my $project_id = conv_i($form->{"paid_project_id_$i"});
my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
$amount = $form->{"paid_$i"} * -1;
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id, chart_link)
- VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?),
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, cleared, taxkey, tax_id, chart_link)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?),
(SELECT tax_id
FROM taxkeys
WHERE chart_id= (SELECT id
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
(SELECT c.link FROM chart c WHERE c.accno = ?))|;
- @values = (conv_i($form->{id}), $form->{AR}{"paid_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $form->{AR}{"paid_$i"},
+ @values = (conv_i($form->{id}), $form->{AR}{"paid_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $new_cleared, $form->{AR}{"paid_$i"},
$form->{AR}{"paid_$i"}, conv_date($form->{"datepaid_$i"}), $form->{AR}{"paid_$i"});
do_query($form, $dbh, $query, @values);
IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
+ if ($form->{draft_id}) {
+ SL::DB::Manager::Draft->delete_all(where => [ id => delete($form->{draft_id}) ]);
+ }
+
# safety check datev export
if ($::instance_conf->get_datev_check_on_ar_transaction) {
- my $transdate = $::form->{transdate} ? DateTime->from_lxoffice($::form->{transdate}) : undef;
- $transdate ||= DateTime->today;
-
my $datev = SL::DATEV->new(
- exporttype => DATEV_ET_BUCHUNGEN,
- format => DATEV_FORMAT_KNE,
dbh => $dbh,
trans_id => $form->{id},
);
- $datev->export;
+ $datev->generate_datev_data;
if ($datev->errors) {
die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
$old_form = save_form();
+ $query = <<SQL;
+ SELECT at.acc_trans_id, at.amount, at.cleared, c.accno
+ FROM acc_trans at
+ LEFT JOIN chart c ON (at.chart_id = c.id)
+ WHERE (at.trans_id = ?)
+SQL
+
+ my %already_cleared = selectall_as_map($form, $dbh, $query, 'acc_trans_id', [ qw(amount cleared accno) ], $form->{id});
+
# Delete all entries in acc_trans from prior payments.
if (SL::DB::Default->get->payments_changeable != 0) {
$self->_delete_payments($form, $dbh);
$form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
$form->{defaultcurrency} = $form->get_default_currency($myconfig);
- # Get the AR accno (which is normally done by Form::create_links()).
+ # Get the AR chart ID (which is normally done by Form::create_links()).
$query =
- qq|SELECT c.accno
+ qq|SELECT c.id
FROM acc_trans at
LEFT JOIN chart c ON (at.chart_id = c.id)
WHERE (trans_id = ?)
ORDER BY at.acc_trans_id
LIMIT 1|;
- ($form->{ARselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
+ ($form->{AR_chart_id}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
# Post the new payments.
- $self->post_transaction($myconfig, $form, $dbh, 1);
+ $self->post_transaction($myconfig, $form, $dbh, payments_only => 1, already_cleared => \%already_cleared);
restore_form($old_form);
my $query =
qq|SELECT DISTINCT a.id, a.invnumber, a.ordnumber, a.cusordnumber, a.transdate, | .
+ qq| a.donumber, a.deliverydate, | .
qq| a.duedate, a.netamount, a.amount, a.paid, | .
qq| a.invoice, a.datepaid, a.notes, a.shipvia, | .
qq| a.shippingpoint, a.storno, a.storno_id, a.globalproject_id, | .
qq| a.marge_total, a.marge_percent, | .
qq| a.transaction_description, a.direct_debit, | .
+ qq| a.type, | .
qq| pr.projectnumber AS globalprojectnumber, | .
qq| c.name, c.customernumber, c.country, c.ustid, b.description as customertype, | .
qq| c.id as customer_id, | .
my $where = "1 = 1";
- unless ( $::auth->assert('show_ar_transactions', 1) ) {
- $where .= " AND NOT invoice = 'f' "; # remove ar transactions from Sales -> Reports -> Invoices
- };
+ # Permissions:
+ # - Always return invoices & AR transactions for projects the employee has "view invoices" permissions for, no matter what the other rules say.
+ # - Exclude AR transactions if no permissions for them exist.
+ # - Limit to own invoices unless may edit all invoices or view invoices is allowed.
+ # - If may edit all or view invoices is allowed, allow filtering by employee/salesman.
+ my (@permission_where, @permission_values);
- if ($form->{customernumber}) {
- $where .= " AND c.customernumber = ?";
- push(@values, trim($form->{customernumber}));
+ if ($::auth->assert('invoice_edit', 1) || $::auth->assert('sales_invoice_view', 1)) {
+ if (!$::auth->assert('show_ar_transactions', 1) ) {
+ push @permission_where, "NOT invoice = 'f'"; # remove ar transactions from Sales -> Reports -> Invoices
+ }
+
+ if (!$::auth->assert('sales_all_edit', 1) && !$::auth->assert('sales_invoice_view', 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 ($form->{salesman_id}) {
+ push @permission_where, "a.salesman_id = ?";
+ push @permission_values, conv_i($form->{salesman_id});
+ }
+ }
+ }
+
+ if (@permission_where || (!$::auth->assert('invoice_edit', 1) && !$::auth->assert('sales_invoice_view', 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->{customer_id}) {
- $where .= " AND a.customer_id = ?";
- push(@values, $form->{customer_id});
- } elsif ($form->{customer}) {
+
+ if ($form->{customer}) {
$where .= " AND c.name ILIKE ?";
push(@values, like($form->{customer}));
}
$where .= " AND c.business_id = ?";
push(@values, $business_id);
}
- if ($form->{department_id}) {
- my $department_id = $form->{department_id};
- $where .= " AND a.department_id = ?";
- push(@values, $department_id);
- }
- if ($form->{department}) {
- my $department = like($form->{department});
- $where .= " AND d.description ILIKE ?";
- push(@values, $department);
+ if ($form->{taxzone_id}) {
+ $where .= " AND a.taxzone_id = ?";
+ push(@values, $form->{taxzone_id});
}
- foreach my $column (qw(invnumber ordnumber cusordnumber notes transaction_description)) {
+ foreach my $column (qw(invnumber ordnumber cusordnumber notes transaction_description shipvia shippingpoint)) {
if ($form->{$column}) {
$where .= " AND a.$column ILIKE ?";
push(@values, like($form->{$column}));
}
}
- if (!$main::auth->assert('sales_all_edit', 1)) {
- # only show own invoices
- $where .= " AND a.employee_id = (select id from employee where login= ?)";
- push (@values, $::myconfig{login});
- } else {
- if ($form->{employee_id}) {
- $where .= " AND a.employee_id = ?";
- push @values, conv_i($form->{employee_id});
- }
- if ($form->{salesman_id}) {
- $where .= " AND a.salesman_id = ?";
- push @values, conv_i($form->{salesman_id});
- }
- };
-
if ($form->{parts_partnumber}) {
$where .= <<SQL;
AND EXISTS (
push @values, like($form->{parts_description});
}
+ if ($form->{show_not_mailed}) {
+ $where .= <<SQL;
+ AND NOT EXISTS (
+ SELECT rl.to_id
+ FROM record_links rl
+ WHERE (rl.from_id = a.id)
+ AND (rl.to_table = 'email_journal')
+ LIMIT 1
+ )
+SQL
+ }
+
+ if ($form->{show_marked_as_closed}) {
+ $query .= '
+ LEFT JOIN (
+ SELECT SUM(acc_trans.amount) AS amount, trans_id
+ FROM acc_trans
+ LEFT JOIN chart ON chart.id = chart_id
+ WHERE chart.link ILIKE ?
+ GROUP BY trans_id
+ ) AS paid_difference ON (paid_difference.trans_id = a.id)
+ ';
+ unshift @values, '%AR_paid%';
+ $where .= ' AND COALESCE(paid_difference.amount, 0) + a.paid != 0';
+ }
+
my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
'trans_id_field' => 'c.id',
'filter' => $form,
my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
my $sortorder = join(', ', map { "$_ $sortdir" } @a);
- if (grep({ $_ eq $form->{sort} } qw(id transdate duedate invnumber ordnumber cusordnumber name datepaid employee shippingpoint shipvia transaction_description))) {
+ if (grep({ $_ eq $form->{sort} } qw(id transdate duedate invnumber ordnumber cusordnumber donumber deliverydate name datepaid employee shippingpoint shipvia transaction_description department taxzone))) {
$sortorder = $form->{sort} . " $sortdir";
}
$form->{"projectnumber_$k"} = $form->{acc_trans}{$key}->[$i-1]->{projectnumber};
$form->{taxrate} = $form->{acc_trans}{$key}->[$i - 1]->{rate};
$form->{"project_id_$k"} = $form->{acc_trans}{$key}->[$i-1]->{project_id};
- }
-
- $form->{"${key}_$i"} = "$form->{acc_trans}{$key}->[$i-1]->{accno}--$form->{acc_trans}{$key}->[$i-1]->{description}";
-
- if ($akey eq "AR") {
- $form->{ARselected} = $form->{acc_trans}{$key}->[$i-1]->{accno};
- } elsif ($akey eq "amount") {
- $form->{"${key}_$k"} = $form->{acc_trans}{$key}->[$i-1]->{accno} . "--" . $form->{acc_trans}{$key}->[$i-1]->{id};
+ $form->{"${key}_chart_id_$k"} = $form->{acc_trans}{$key}->[$i-1]->{chart_id};
$form->{"taxchart_$k"} = $form->{acc_trans}{$key}->[$i-1]->{id} . "--" . $form->{acc_trans}{$key}->[$i-1]->{rate};
}
}
projects / kivitendo-erp.git / blobdiff