# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
-# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
+# MA 02110-1335, USA.
#======================================================================
#
# Accounts Receivable module backend routines
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 SL::TransNumber;
+use SL::Util qw(trim);
+use SL::DB;
use strict;
sub post_transaction {
+ my ($self, $myconfig, $form, $provided_dbh, %params) = @_;
$main::lxdebug->enter_sub();
- my ($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, %params) = @_;
+
+ my $payments_only = $params{payments_only};
my ($query, $sth, $null, $taxrate, $amount, $tax);
my $exchangerate = 0;
my @values;
- my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
+ my $dbh = $provided_dbh || SL::DB->client->dbh;
$form->{defaultcurrency} = $form->get_default_currency($myconfig);
# set exchangerate
$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}, $form->{netamount}, $form->{notes}, conv_i($form->{department_id}),
+ 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 {
do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
}
+ $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);
$amount = $form->round_amount( $form->{"paid_$i"} * ($form->{exchangerate} - $form->{"exchangerate_$i"}) * -1, 2);
if ($amount != 0) {
+ # fetch fxgain and fxloss chart info from defaults if charts aren't already filled in form
+ if ( !$form->{fxgain_accno} && $::instance_conf->get_fxgain_accno_id ) {
+ $form->{fxgain_accno} = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_fxgain_accno_id)->accno;
+ };
+ if ( !$form->{fxloss_accno} && $::instance_conf->get_fxloss_accno_id ) {
+ $form->{fxloss_accno} = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_fxloss_accno_id)->accno;
+ };
+ die "fxloss_accno missing" if $amount < 0 and not $form->{fxloss_accno};
+ die "fxgain_accno missing" if $amount > 0 and not $form->{fxgain_accno};
my $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?),
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) {
- $dbh->rollback;
die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
}
}
- my $rc = 1;
- if (!$provided_dbh) {
- $rc = $dbh->commit();
- $dbh->disconnect();
- }
-
- $main::lxdebug->leave_sub() and return $rc;
+ return 1;
}
sub _delete_payments {
}
sub post_payment {
+ my ($self, $myconfig, $form, $locale) = @_;
$main::lxdebug->enter_sub();
+ my $rc = SL::DB->client->with_transaction(\&_post_payment, $self, $myconfig, $form, $locale);
+
+ $::lxdebug->leave_sub;
+ return $rc;
+}
+
+sub _post_payment {
my ($self, $myconfig, $form, $locale) = @_;
- # connect to database, turn off autocommit
- my $dbh = $form->dbconnect_noauto($myconfig);
+ my $dbh = SL::DB->client->dbh;
my (%payments, $old_form, $row, $item, $query, %keep_vars);
$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 $rc = $dbh->commit();
- $dbh->disconnect();
-
- $main::lxdebug->leave_sub();
-
- return $rc;
+ return 1;
}
sub delete_transaction {
my ($self, $myconfig, $form) = @_;
- # connect to database, turn AutoCommit off
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- # acc_trans entries are deleted by database triggers.
- my $query = qq|DELETE FROM ar WHERE id = ?|;
- do_query($form, $dbh, $query, $form->{id});
-
- # commit
- my $rc = $dbh->commit;
- $dbh->disconnect;
+ SL::DB->client->with_transaction(sub {
+ # acc_trans entries are deleted by database triggers.
+ my $query = qq|DELETE FROM ar WHERE id = ?|;
+ do_query($form, SL::DB->client->dbh, $query, $form->{id});
+ 1;
+ }) or do { die SL::DB->client->error };
$main::lxdebug->leave_sub();
- return $rc;
+ return 1;
}
sub ar_transactions {
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, | .
qq| dc.dunning_description, | .
qq| tz.description AS taxzone, | .
qq| pt.description AS payment_terms, | .
+ qq| d.description AS department, | .
qq{ ( SELECT ch.accno || ' -- ' || ch.description
FROM acc_trans at
LEFT JOIN chart ch ON ch.id = at.chart_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, $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, $form->like($form->{customer}));
+ push(@values, like($form->{customer}));
}
if ($form->{"cp_name"}) {
$where .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
- push(@values, ('%' . $form->{"cp_name"} . '%')x2);
+ push(@values, (like($form->{"cp_name"}))x2);
}
if ($form->{business_id}) {
my $business_id = $form->{business_id};
$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 = "%" . $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, $form->like($form->{$column}));
+ push(@values, like($form->{$column}));
}
}
if ($form->{"project_id"}) {
if ($form->{transdatefrom}) {
$where .= " AND a.transdate >= ?";
- push(@values, $form->{transdatefrom});
+ push(@values, trim($form->{transdatefrom}));
}
if ($form->{transdateto}) {
$where .= " AND a.transdate <= ?";
- push(@values, $form->{transdateto});
+ push(@values, trim($form->{transdateto}));
}
if ($form->{duedatefrom}) {
$where .= " AND a.duedate >= ?";
- push(@values, $form->{duedatefrom});
+ push(@values, trim($form->{duedatefrom}));
}
if ($form->{duedateto}) {
$where .= " AND a.duedate <= ?";
- push(@values, $form->{duedateto});
+ push(@values, trim($form->{duedateto}));
}
if ($form->{open} || $form->{closed}) {
unless ($form->{open} && $form->{closed}) {
}
}
- 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 (
+ SELECT invoice.trans_id
+ FROM invoice
+ LEFT JOIN parts ON (invoice.parts_id = parts.id)
+ WHERE (invoice.trans_id = a.id)
+ AND (parts.partnumber ILIKE ?)
+ LIMIT 1
+ )
+SQL
+ push @values, like($form->{parts_partnumber});
+ }
+
+ if ($form->{parts_description}) {
+ $where .= <<SQL;
+ AND EXISTS (
+ SELECT invoice.trans_id
+ FROM invoice
+ WHERE (invoice.trans_id = a.id)
+ AND (invoice.description ILIKE ?)
+ LIMIT 1
+ )
+SQL
+ 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',
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";
}
my ($self, $myconfig, $form) = @_;
# connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = SL::DB->client->dbh;
my $query =
"SELECT COALESCE(" .
" current_date)";
($form->{transdate}) = $dbh->selectrow_array($query);
- $dbh->disconnect;
-
$main::lxdebug->leave_sub();
}
$form->{forex} = $form->{exchangerate};
$exchangerate = $form->{exchangerate} ? $form->{exchangerate} : 1;
+ # expected keys: AR, AR_paid, AR_tax, AR_amount
foreach my $key (keys %{ $form->{AR_links} }) {
$j = 0;
$k = 0;
$form->{"paid_project_id_$j"} = $form->{acc_trans}{$key}->[$i - 1]->{project_id};
$form->{paidaccounts}++;
- } else {
+ } else { # e.g. AR_amount, AR, AR_tax
$akey = $key;
- $akey =~ s/AR_//;
+ $akey =~ s/AR_//; # e.g. tax, amount, AR, used to store form key tax_$i, amount_$i, ...
- if ($key eq "AR_tax" || $key eq "AP_tax") {
+ if ($key eq "AR_tax" || $key eq "AP_tax") { # AR_tax
$form->{"${key}_$form->{acc_trans}{$key}->[$i-1]->{accno}"} = "$form->{acc_trans}{$key}->[$i-1]->{accno}--$form->{acc_trans}{$key}->[$i-1]->{description}";
+ # determine the rounded tax amounts for each account, e.g. tax_1776
$form->{"${akey}_$form->{acc_trans}{$key}->[$i-1]->{accno}"} = $form->round_amount($form->{acc_trans}{$key}->[$i - 1]->{amount} / $exchangerate, 2);
+ # check e.g. $form->{1776_rate}, does this make sense for AR_tax charts? Is this ever valid? If it was, totaltax would be calculated twice
if ($form->{"$form->{acc_trans}{$key}->[$i-1]->{accno}_rate"} > 0) {
$totaltax += $form->{"${akey}_$form->{acc_trans}{$key}->[$i-1]->{accno}"};
$taxrate += $form->{"$form->{acc_trans}{$key}->[$i-1]->{accno}_rate"};
}
$index = $form->{acc_trans}{$key}->[$i - 1]->{index};
- $form->{"tax_$index"} = $form->{acc_trans}{$key}->[$i - 1]->{amount};
+ $form->{"tax_$index"} = $form->round_amount($form->{acc_trans}{$key}->[$i - 1]->{amount} / $exchangerate, 2); # convert the tax_$i amounts
+ # currently totaltax is the sum of rounded tax amounts, is this correct?
$totaltax += $form->{"tax_$index"};
- } else {
+ } else { # e.g. AR_amount, AR
$k++;
$form->{"${akey}_$k"} = $form->round_amount($form->{acc_trans}{$key}->[$i - 1]->{amount} / $exchangerate, 2);
$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};
}
}
}
sub storno {
+ my ($self, $form, $myconfig, $id) = @_;
$main::lxdebug->enter_sub();
+ my $rc = SL::DB->client->with_transaction(\&_storno, $self, $form, $myconfig, $id);
+
+ $::lxdebug->leave_sub;
+ return $rc;
+}
+
+
+sub _storno {
my ($self, $form, $myconfig, $id) = @_;
my ($query, $new_id, $storno_row, $acc_trans_rows);
- my $dbh = $form->get_standard_dbh($myconfig);
+ my $dbh = SL::DB->client->dbh;
$query = qq|SELECT nextval('glid')|;
($new_id) = selectrow_query($form, $dbh, $query);
$query = qq|UPDATE ar SET paid = amount + paid, storno = 't' WHERE id = ?|;
do_query($form, $dbh, $query, $id);
+ $form->new_lastmtime('ar') if $id == $form->{id};
+
# now copy acc_trans entries
$query = qq|SELECT a.*, c.link FROM acc_trans a LEFT JOIN chart c ON a.chart_id = c.id WHERE a.trans_id = ? ORDER BY a.acc_trans_id|;
my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
map { IO->set_datepaid(table => 'ar', id => $_, dbh => $dbh) } ($id, $new_id);
- $dbh->commit;
-
- $main::lxdebug->leave_sub();
+ return 1;
}
projects / kivitendo-erp.git / blobdiff