|
#=====================================================================
|
|
# LX-Office ERP
|
|
# Copyright (C) 2004
|
|
# Based on SQL-Ledger Version 2.1.9
|
|
# Web http://www.lx-office.org
|
|
#
|
|
#=====================================================================
|
|
# SQL-Ledger Accounting
|
|
# Copyright (C) 2001
|
|
#
|
|
# Author: Dieter Simader
|
|
# Email: dsimader@sql-ledger.org
|
|
# Web: http://www.sql-ledger.org
|
|
#
|
|
# Contributors:
|
|
#
|
|
# This program is free software; you can redistribute it and/or modify
|
|
# it under the terms of the GNU General Public License as published by
|
|
# the Free Software Foundation; either version 2 of the License, or
|
|
# (at your option) any later version.
|
|
#
|
|
# This program is distributed in the hope that it will be useful,
|
|
# but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
# 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., 51 Franklin Street, Fifth Floor, Boston,
|
|
# MA 02110-1335, USA.
|
|
#======================================================================
|
|
#
|
|
# Accounts Payables database backend routines
|
|
#
|
|
#======================================================================
|
|
|
|
package AP;
|
|
|
|
use SL::DATEV qw(:CONSTANTS);
|
|
use SL::DBUtils;
|
|
use SL::IO;
|
|
use SL::MoreCommon;
|
|
use SL::DB::ApGl;
|
|
use SL::DB::Default;
|
|
use SL::DB::Draft;
|
|
use SL::DB::Order;
|
|
use SL::DB::PurchaseInvoice;
|
|
use SL::DB::EmailJournal;
|
|
use SL::DB::ValidityToken;
|
|
use SL::Util qw(trim);
|
|
use SL::DB;
|
|
use Data::Dumper;
|
|
use List::Util qw(sum0);
|
|
use strict;
|
|
use URI::Escape;
|
|
|
|
sub post_transaction {
|
|
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, %params);
|
|
|
|
$::lxdebug->leave_sub;
|
|
return $rc;
|
|
}
|
|
|
|
sub _post_transaction {
|
|
my ($self, $myconfig, $form, $provided_dbh, %params) = @_;
|
|
|
|
my $validity_token;
|
|
if (!$form->{id}) {
|
|
$validity_token = SL::DB::Manager::ValidityToken->fetch_valid_token(
|
|
scope => SL::DB::ValidityToken::SCOPE_PURCHASE_INVOICE_POST(),
|
|
token => $form->{form_validity_token},
|
|
);
|
|
|
|
die $::locale->text('The form is not valid anymore.') if !$validity_token;
|
|
}
|
|
|
|
my $payments_only = $params{payments_only};
|
|
my $dbh = $provided_dbh || SL::DB->client->dbh;
|
|
|
|
my ($null, $taxrate, $amount);
|
|
my $exchangerate = 0;
|
|
|
|
$form->{defaultcurrency} = $form->get_default_currency($myconfig);
|
|
$form->{taxincluded} = 0 unless $form->{taxincluded};
|
|
|
|
# make sure to have a id
|
|
my ($query, $sth, @values);
|
|
if (!$payments_only) {
|
|
# if we have an id delete old records
|
|
if ($form->{id}) {
|
|
|
|
# delete detail records
|
|
$query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
|
|
do_query($form, $dbh, $query, $form->{id});
|
|
|
|
} else {
|
|
|
|
($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|);
|
|
|
|
$query =
|
|
qq|INSERT INTO ap (id, invnumber, employee_id,currency_id, taxzone_id) | .
|
|
qq|VALUES (?, ?, (SELECT e.id FROM employee e WHERE e.login = ?),
|
|
(SELECT id FROM currencies WHERE name = ?), (SELECT taxzone_id FROM vendor WHERE id = ?) )|;
|
|
do_query($form, $dbh, $query, $form->{id}, $form->{invnumber}, $::myconfig{login}, $form->{currency}, $form->{vendor_id});
|
|
|
|
}
|
|
}
|
|
# check default or record exchangerate
|
|
if ($form->{currency} eq $form->{defaultcurrency}) {
|
|
$form->{exchangerate} = 1;
|
|
} else {
|
|
$exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'sell');
|
|
$form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate}, 5);
|
|
|
|
# if default exchangerate is not defined, define one
|
|
unless ($exchangerate) {
|
|
$form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate});
|
|
# delete records exchangerate -> if user sets new invdate for record
|
|
$query = qq|UPDATE ap set exchangerate = NULL where id = ?|;
|
|
do_query($form, $dbh, $query, $form->{"id"});
|
|
}
|
|
# update record exchangerate, if the default is set and differs from current
|
|
if ($exchangerate && ($form->{exchangerate} != $exchangerate)) {
|
|
$form->update_exchangerate($dbh, $form->{currency}, $form->{transdate},
|
|
0, $form->{exchangerate}, $form->{id}, 'ap');
|
|
}
|
|
}
|
|
# get the charts selected
|
|
$form->{AP_amounts}{"amount_$_"} = $form->{"AP_amount_chart_id_$_"} for (1 .. $form->{rowcount});
|
|
|
|
# calculate the totals while calculating and reformatting the $amount_$i and $tax_$i
|
|
($form->{netamount},$form->{total_tax},$form->{invtotal}) = $form->calculate_arap('buy',$form->{taxincluded}, $form->{exchangerate});
|
|
|
|
# adjust paidaccounts if there is no date in the last row
|
|
$form->{paidaccounts}-- unless ($form->{"datepaid_$form->{paidaccounts}"});
|
|
|
|
$form->{invpaid} = 0;
|
|
|
|
# add payments
|
|
for my $i (1 .. $form->{paidaccounts}) {
|
|
$form->{"paid_$i"} =
|
|
$form->round_amount($form->parse_amount($myconfig, $form->{"paid_$i"}),
|
|
2);
|
|
|
|
$form->{invpaid} += $form->{"paid_$i"};
|
|
$form->{datepaid} = $form->{"datepaid_$i"};
|
|
|
|
}
|
|
|
|
$form->{invpaid} =
|
|
$form->round_amount($form->{invpaid} * $form->{exchangerate}, 2);
|
|
|
|
# # store invoice total, this goes into ap table
|
|
# $form->{invtotal} = $form->{netamount} + $form->{total_tax};
|
|
|
|
# amount for total AP
|
|
$form->{payables} = $form->{invtotal};
|
|
|
|
if (!$payments_only) {
|
|
$query = qq|UPDATE ap SET invnumber = ?,
|
|
transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?,
|
|
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 = ?, payment_id = ?, transaction_description = ?, intnotes = ?,
|
|
qrbill_data = ?
|
|
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}), 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->{transaction_description},
|
|
$form->{intnotes},
|
|
$form->{qrbill_data_encoded} ? uri_unescape($form->{qrbill_data_encoded}) : undef,
|
|
$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 ($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) {
|
|
my $project_id;
|
|
$project_id = conv_i($form->{"project_id_$i"});
|
|
|
|
# insert detail records in acc_trans
|
|
$query =
|
|
qq|INSERT INTO acc_trans | .
|
|
qq| (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id, chart_link)| .
|
|
qq|VALUES (?, ?, ?, ?, ?, ?, ?, (SELECT c.link FROM chart c WHERE c.id = ?))|;
|
|
@values = ($form->{id}, $form->{"AP_amount_chart_id_$i"},
|
|
$form->{"amount_$i"}, conv_date($form->{transdate}),
|
|
$project_id, $form->{"taxkey_$i"}, conv_i($form->{"tax_id_$i"}),
|
|
$form->{"AP_amount_chart_id_$i"});
|
|
do_query($form, $dbh, $query, @values);
|
|
|
|
if ($form->{"tax_$i"} != 0 && !$form->{"reverse_charge_$i"}) {
|
|
# insert detail records in acc_trans
|
|
$query =
|
|
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, | .
|
|
qq| project_id, taxkey, tax_id, chart_link) | .
|
|
qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | .
|
|
qq| ?, ?, ?, ?, ?,| .
|
|
qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|;
|
|
@values = ($form->{id}, $form->{AP_amounts}{"tax_$i"},
|
|
$form->{"tax_$i"}, conv_date($form->{transdate}),
|
|
$project_id, $form->{"taxkey_$i"}, conv_i($form->{"tax_id_$i"}),
|
|
$form->{AP_amounts}{"tax_$i"});
|
|
do_query($form, $dbh, $query, @values);
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
# add payables
|
|
$query =
|
|
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) | .
|
|
qq|VALUES (?, ?, ?, ?, | .
|
|
qq| (SELECT taxkey_id FROM chart WHERE id = ?),| .
|
|
qq| (SELECT tax_id| .
|
|
qq| FROM taxkeys| .
|
|
qq| WHERE chart_id = ?| .
|
|
qq| AND startdate <= ?| .
|
|
qq| ORDER BY startdate DESC LIMIT 1),| .
|
|
qq| (SELECT c.link FROM chart c WHERE c.id = ?))|;
|
|
@values = ($form->{id}, $form->{AP_chart_id}, $form->{payables},
|
|
conv_date($form->{transdate}), $form->{AP_chart_id}, $form->{AP_chart_id}, conv_date($form->{transdate}),
|
|
$form->{AP_chart_id});
|
|
do_query($form, $dbh, $query, @values);
|
|
}
|
|
|
|
# if there is no amount but a payment record a payable
|
|
if ($form->{amount} == 0 && $form->{invtotal} == 0) {
|
|
$form->{payables} = $form->{invpaid};
|
|
}
|
|
|
|
my %already_cleared = %{ $params{already_cleared} // {} };
|
|
|
|
# add paid transactions
|
|
for my $i (1 .. $form->{paidaccounts}) {
|
|
|
|
if ($form->{"acc_trans_id_$i"} && $payments_only && (SL::DB::Default->get->payments_changeable == 0)) {
|
|
next;
|
|
}
|
|
|
|
if ($form->{"paid_$i"} != 0) {
|
|
my $project_id = conv_i($form->{"paid_project_id_$i"});
|
|
|
|
$exchangerate = 0;
|
|
if ($form->{currency} eq $form->{defaultcurrency}) {
|
|
$form->{"exchangerate_$i"} = 1;
|
|
} else {
|
|
$exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'sell');
|
|
$form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
|
|
}
|
|
$form->{"AP_paid_$i"} =~ s/\"//g;
|
|
|
|
# get paid account
|
|
|
|
($form->{"AP_paid_account_$i"}) = split(/--/, $form->{"AP_paid_$i"});
|
|
$form->{"datepaid_$i"} = $form->{transdate}
|
|
unless ($form->{"datepaid_$i"});
|
|
|
|
# if there is no amount and invtotal is zero there is no exchangerate
|
|
if ($form->{amount} == 0 && $form->{invtotal} == 0) {
|
|
$form->{exchangerate} = $form->{"exchangerate_$i"};
|
|
}
|
|
|
|
$amount =
|
|
$form->round_amount($form->{"paid_$i"} * $form->{exchangerate} * -1,
|
|
2);
|
|
|
|
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} != $amount * -1 ? 'f'
|
|
: $already_cleared{$form->{"acc_trans_id_$i"}}->{accno} != $form->{"AP_paid_account_$i"} ? 'f'
|
|
: $already_cleared{$form->{"acc_trans_id_$i"}}->{cleared} ? 't'
|
|
: 'f';
|
|
|
|
if ($form->{payables}) {
|
|
$query =
|
|
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, cleared, taxkey, tax_id, chart_link) | .
|
|
qq|VALUES (?, ?, ?, ?, ?, ?, | .
|
|
qq| (SELECT taxkey_id FROM chart WHERE id = ?),| .
|
|
qq| (SELECT tax_id| .
|
|
qq| FROM taxkeys| .
|
|
qq| WHERE chart_id = ?| .
|
|
qq| AND startdate <= ?| .
|
|
qq| ORDER BY startdate DESC LIMIT 1),| .
|
|
qq| (SELECT c.link FROM chart c WHERE c.id = ?))|;
|
|
@values = ($form->{id}, $form->{AP_chart_id}, $amount,
|
|
conv_date($form->{"datepaid_$i"}), $project_id, $new_cleared,
|
|
$form->{AP_chart_id}, $form->{AP_chart_id}, conv_date($form->{"datepaid_$i"}),
|
|
$form->{AP_chart_id});
|
|
do_query($form, $dbh, $query, @values);
|
|
}
|
|
$form->{payables} = $amount;
|
|
|
|
# add payment
|
|
my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
|
|
$query =
|
|
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, cleared, taxkey, tax_id, chart_link) | .
|
|
qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, ?, | .
|
|
qq| (SELECT taxkey_id FROM chart WHERE accno = ?), | .
|
|
qq| (SELECT tax_id| .
|
|
qq| FROM taxkeys| .
|
|
qq| WHERE chart_id= (SELECT id | .
|
|
qq| FROM chart| .
|
|
qq| WHERE accno = ?)| .
|
|
qq| AND startdate <= ?| .
|
|
qq| ORDER BY startdate DESC LIMIT 1),| .
|
|
qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|;
|
|
@values = ($form->{id}, $form->{"AP_paid_account_$i"}, $form->{"paid_$i"},
|
|
conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"},
|
|
$form->{"memo_$i"}, $project_id, $new_cleared, $form->{"AP_paid_account_$i"},
|
|
$form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"}),
|
|
$form->{"AP_paid_account_$i"});
|
|
do_query($form, $dbh, $query, @values);
|
|
|
|
# add exchange rate difference
|
|
$amount =
|
|
$form->round_amount($form->{"paid_$i"} *
|
|
($form->{"exchangerate_$i"} - 1), 2);
|
|
if ($amount != 0) {
|
|
$query =
|
|
$form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"}),
|
|
$form->{"AP_paid_account_$i"});
|
|
do_query($form, $dbh, $query, @values);
|
|
}
|
|
|
|
# exchangerate gain/loss
|
|
$amount =
|
|
$form->round_amount($form->{"paid_$i"} *
|
|
($form->{exchangerate} -
|
|
$form->{"exchangerate_$i"}), 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};
|
|
$query =
|
|
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id, chart_link) | .
|
|
qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | .
|
|
qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| .
|
|
qq| (SELECT tax_id| .
|
|
qq| FROM taxkeys| .
|
|
qq| WHERE chart_id= (SELECT id | .
|
|
qq| FROM chart| .
|
|
qq| WHERE accno = ?)| .
|
|
qq| AND startdate <= ?| .
|
|
qq| ORDER BY startdate DESC LIMIT 1),| .
|
|
qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|;
|
|
@values = ($form->{id},
|
|
($am |