|
#=====================================================================
|
|
# 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::Util qw(trim);
|
|
use SL::DB;
|
|
use Data::Dumper;
|
|
use List::Util qw(sum0);
|
|
use strict;
|
|
|
|
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 $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 = ?,
|
|
my $datev = SL::DATEV->new(
|
|
dbh => $dbh,
|
|
trans_id => $form->{id},
|
|
);
|
|
$datev->generate_datev_data;
|
|
|
|
if ($datev->errors) {
|
|
die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
|
|
}
|
|
}
|
|
|
|
return 1;
|
|
}
|
|
|
|
sub _reverse_charge {
|
|
my ($self, $myconfig, $form) = @_;
|
|
|
|
# delete previous bookings, if they exists (repost)
|
|
my $ap_gl = SL::DB::Manager::ApGl->get_first(where => [ ap_id => $form->{id} ]);
|
|
my $gl_id = ref $ap_gl eq 'SL::DB::ApGl' ? $ap_gl->gl_id : undef;
|
|
|
|
SL::DB::Manager::GLTransaction->delete_all(where => [ id => $gl_id ]) if $gl_id;
|
|
SL::DB::Manager::ApGl-> delete_all(where => [ ap_id => $form->{id} ]) if $gl_id;
|
|
SL::DB::Manager::RecordLink-> delete_all(where => [ from_table => 'ap', to_table => 'gl', from_id => $form->{id} ]);
|
|
|
|
my ($i, $current_transaction);
|
|
|
|
for $i (1 .. $form->{rowcount}) {
|
|
|
|
my $tax = SL::DB::Manager::Tax->get_first( where => [id => $form->{"tax_id_$i"}, '!reverse_charge_chart_id' => undef ]);
|
|
next unless ref $tax eq 'SL::DB::Tax';
|
|
|
|
# gl booking
|
|
my ($credit, $debit);
|
|
$credit = SL::DB::Manager::Chart->find_by(id => $tax->chart_id);
|
|
$debit = SL::DB::Manager::Chart->find_by(id => $tax->reverse_charge_chart_id);
|
|
|
|
croak("No such Chart ID" . $tax->chart_id) unless ref $credit eq 'SL::DB::Chart';
|
|
croak("No such Chart ID" . $tax->reverse_chart_id) unless ref $debit eq 'SL::DB::Chart';
|
|
|
|
my ($tmpnetamount, $tmptaxamount) = $form->calculate_tax($form->{"amount_$i"}, $tax->rate, $form->{taxincluded}, 2);
|
|
$current_transaction = SL::DB::GLTransaction->new(
|
|
employee_id => $form->{employee_id},
|
|
transdate => $form->{transdate},
|
|
description => $form->{notes} || $form->{invnumber},
|
|
reference => $form->{invnumber},
|
|
department_id => $form->{department_id} ? $form->{department_id} : undef,
|
|
imported => 0, # not imported
|
|
taxincluded => 0,
|
|
)->add_chart_booking(
|
|
chart => $tmptaxamount > 0 ? $debit : $credit,
|
|
debit => abs($tmptaxamount),
|
|
source => "Reverse Charge for " . $form->{invnumber},
|
|
tax_id => 0,
|
|
)->add_chart_booking(
|
|
chart => $tmptaxamount > 0 ? $credit : $debit,
|
|
credit => abs($tmptaxamount),
|
|
source => "Reverse Charge for " . $form->{invnumber},
|
|
tax_id => 0,
|
|
)->post;
|
|
# add a stable link from ap to gl
|
|
my %props_gl = (
|
|
ap_id => $form->{id},
|
|
gl_id => $current_transaction->id,
|
|
);
|
|
SL::DB::ApGl->new(%props_gl)->save;
|
|
# Record a record link from ap to gl
|
|
my %props_rl = (
|
|
from_table => 'ap',
|
|
from_id => $form->{id},
|
|
to_table => 'gl',
|
|
to_id => $current_transaction->id,
|
|
);
|
|
SL::DB::RecordLink->new(%props_rl)->save;
|
|
}
|
|
}
|
|
|
|
sub delete_transaction {
|
|
$main::lxdebug->enter_sub();
|
|
|
|
my ($self, $myconfig, $form) = @_;
|
|
|
|
SL::DB->client->with_transaction(sub {
|
|
|
|
# if tax 94 reverse charge, clear all GL bookings and links
|
|
my $ap_gl = SL::DB::Manager::ApGl->get_first(where => [ ap_id => $form->{id} ]);
|
|
my $gl_id = ref $ap_gl eq 'SL::DB::ApGl' ? $ap_gl->gl_id : undef;
|
|
|
|
SL::DB::Manager::GLTransaction->delete_all(where => [ id => $gl_id ]) if $gl_id;
|
|
SL::DB::Manager::ApGl-> delete_all(where => [ ap_id => $form->{id} ]) if $gl_id;
|
|
SL::DB::Manager::RecordLink-> delete_all(where => [ from_table => 'ap', to_table => 'gl', from_id => $form->{id} ]);
|
|
# done gl delete for tax 94 case
|
|
|
|
# begin ap delete
|
|
my $query = qq|DELETE FROM ap 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 1;
|
|
}
|
|
|
|
sub ap_transactions {
|
|
$main::lxdebug->enter_sub();
|
|
|
|
my ($self, $myconfig, $form) = @_;
|
|
|
|
# connect to database
|
|
my $dbh = $form->get_standard_dbh($myconfig);
|
|
|
|
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, 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, } .
|
|
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 department ON (department.id = a.department_id)|;
|
|
|
|
my $where = '';
|
|
|
|
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 or view invoices is allowed.
|
|
# - If may edit all or view invoices is allowed, allow filtering by employee.
|
|
my (@permission_where, @permission_values);
|
|
|
|
if ($::auth->assert('vendor_invoice_edit', 1) || $::auth->assert('purchase_invoice_view', 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) && !$::auth->assert('purchase_invoice_view', 1)) {
|
|
# only show own invoices
|
|
push @permission_where, "a.employee_id = ?";
|
|
push @permission_values, SL::DB::Manager::Employee->current->id;
|