use SL::ARAP;
use SL::Common;
use SL::CVar;
+use SL::DATEV qw(:CONSTANTS);
use SL::DBUtils;
use SL::DO;
use SL::GenericTranslations;
use SL::IO;
use SL::MoreCommon;
+use SL::DB::Default;
use List::Util qw(min);
use strict;
# connect to database, turn off autocommit
my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
$form->{defaultcurrency} = $form->get_default_currency($myconfig);
+ my $defaultcurrency = $form->{defaultcurrency};
my $ic_cvar_configs = CVar->get_configs(module => 'IC',
dbh => $dbh);
my $all_units = AM->retrieve_units($myconfig, $form);
+#markierung
if (!$payments_only) {
if ($form->{id}) {
&reverse_invoice($dbh, $form);
} else {
($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|);
- do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber) VALUES (?, '')|, $form->{id});
+ do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber, currency_id) VALUES (?, '', (SELECT id FROM currencies WHERE name=?))|, $form->{id}, $form->{currency});
}
}
- my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
- my $defaultcurrency = (split m/:/, $currencies)[0];
-
if ($form->{currency} eq $defaultcurrency) {
$form->{exchangerate} = 1;
} else {
- $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'sell');
+ $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'sell');
}
$form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
if ( $::instance_conf->get_inventory_system eq 'periodic') {
# inventory account number is overwritten with expense account number, so
# never book incoming to inventory account but always to expense account
- $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"}
+ $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"}
};
# get item baseunit
next if $payments_only;
- # update parts table
+ # update parts table by setting lastcost to current price, don't allow negative values by using abs
$query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|;
- @values = ($form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
+ @values = (abs($fxsellprice * $form->{exchangerate} / $basefactor), conv_i($form->{"id_$i"}));
do_query($form, $dbh, $query, @values);
# check if we sold the item already and
# make an entry for the expense and inventory
+ my $taxzone = $form->{taxzone_id} * 1;
$query =
qq|SELECT i.id, i.qty, i.allocated, i.trans_id, i.base_qty,
- p.inventory_accno_id, p.expense_accno_id, a.transdate
- FROM invoice i, ar a, parts p
+ bg.inventory_accno_id, bg.expense_accno_id_${taxzone} AS expense_accno_id, a.transdate
+ FROM invoice i, ar a, parts p, buchungsgruppen bg
WHERE (i.parts_id = p.id)
AND (i.parts_id = ?)
AND ((i.base_qty + i.allocated) > 0)
AND (i.trans_id = a.id)
+ AND (p.buchungsgruppen_id = bg.id)
ORDER BY transdate|;
# ORDER BY transdate guarantees FIFO
# sold two items without having bought them yet, example result of query:
-# id | qty | allocated | trans_id | inventory_accno_id | expense_accno_id | transdate
+# id | qty | allocated | trans_id | inventory_accno_id | expense_accno_id | transdate
# ---+-----+-----------+----------+--------------------+------------------+------------
# 9 | 2 | 0 | 9 | 15 | 151 | 2011-01-05
# base_qty + allocated > 0 if article has already been sold but not bought yet
# select qty,allocated,base_qty,sellprice from invoice where trans_id = 9;
-# qty | allocated | base_qty | sellprice
+# qty | allocated | base_qty | sellprice
# -----+-----------+----------+------------
# 2 | 0 | 2 | 1000.00000
# allocated >= 0
# add entry for inventory, this one is for the sold item
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey) VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?))|;
- @values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id});
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= ?
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= ?
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT link FROM chart WHERE id = ?))|;
+ @values = ($ref->{trans_id}, $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate},
+ $ref->{inventory_accno_id});
do_query($form, $dbh, $query, @values);
# add expense
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey) VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?))|;
- @values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id});
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= ?
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= ?
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT link FROM chart WHERE id = ?))|;
+ @values = ($ref->{trans_id}, $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}, $ref->{transdate}, $ref->{expense_accno_id}, $ref->{transdate},
+ $ref->{expense_accno_id});
do_query($form, $dbh, $query, @values);
}
};
# update lastcost
$query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|;
- do_query($form, $dbh, $query, $form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
+ do_query($form, $dbh, $query, $form->{"sellprice_$i"} / $basefactor, conv_i($form->{"id_$i"}));
}
next if $payments_only;
next if $payments_only || !$form->{amount}{$trans_id}{$accno};
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ ?,
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT link FROM chart WHERE accno = ?))|;
@values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
- conv_date($form->{invdate}), $accno, $project_id);
+ conv_date($form->{invdate}), $accno, conv_date($form->{invdate}), $project_id, $accno, conv_date($form->{invdate}), $accno);
do_query($form, $dbh, $query, @values);
}
}
# record payments and offsetting AP
for my $i (1 .. $form->{paidaccounts}) {
+ if ($form->{"acc_trans_id_$i"}
+ && $payments_only
+ && (SL::DB::Default->get->payments_changeable == 0)) {
+ next;
+ }
+
next if $form->{"paid_$i"} == 0;
my ($accno) = split /--/, $form->{"AP_paid_$i"};
# record AP
if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ ?,
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($form->{id}), $form->{AP}, $amount,
- $form->{"datepaid_$i"}, $form->{AP}, $project_id);
+ $form->{"datepaid_$i"}, $form->{AP}, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AP}, conv_date($form->{"datepaid_$i"}), $form->{AP});
do_query($form, $dbh, $query, @values);
}
# record payment
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
- VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ 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, taxkey, project_id, tax_id, chart_link)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ ?,
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
- $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
+ $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($form->{"datepaid_$i"}), $project_id, $accno, conv_date($form->{"datepaid_$i"}), $accno);
do_query($form, $dbh, $query, @values);
$exchangerate = 0;
$form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
next if ($form->{fx}{$accno}{$transdate} == 0);
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
- VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?)|;
- @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id);
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id, tax_id, chart_link)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?,
+ (SELECT id FROM tax WHERE taxkey=0 LIMIT 1),
+ (SELECT link FROM chart WHERE accno = ?))|;
+ @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id, $accno);
do_query($form, $dbh, $query, @values);
}
}
orddate = ?, quodate = ?, vendor_id = ?, amount = ?,
netamount = ?, paid = ?, duedate = ?,
invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?,
- intnotes = ?, curr = ?, storno_id = ?, storno = ?,
- cp_id = ?, employee_id = ?, department_id = ?,
- globalproject_id = ?
+ intnotes = ?, storno_id = ?, storno = ?,
+ cp_id = ?, employee_id = ?, department_id = ?, delivery_term_id = ?,
+ globalproject_id = ?, direct_debit = ?
WHERE id = ?|;
@values = (
$form->{invnumber}, $form->{ordnumber}, $form->{quonumber}, conv_date($form->{invdate}),
conv_date($form->{orddate}), conv_date($form->{quodate}), conv_i($form->{vendor_id}), $amount,
$netamount, $form->{paid}, conv_date($form->{duedate}),
'1', $taxzone_id, $form->{notes}, $form->{taxincluded} ? 't' : 'f',
- $form->{intnotes}, $form->{currency}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f',
- conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}),
+ $form->{intnotes}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f',
+ conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}), conv_i($form->{delivery_term_id}),
conv_i($form->{globalproject_id}),
+ $form->{direct_debit} ? 't' : 'f',
conv_i($form->{id})
);
do_query($form, $dbh, $query, @values);
'arap_id' => $form->{id},
'table' => 'ap',);
+ # safety check datev export
+ if ($::instance_conf->get_datev_check_on_purchase_invoice) {
+ my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
+ $transdate ||= DateTime->today;
+
+ my $datev = SL::DATEV->new(
+ exporttype => DATEV_ET_BUCHUNGEN,
+ format => DATEV_FORMAT_KNE,
+ dbh => $dbh,
+ from => $transdate,
+ to => $transdate,
+ trans_id => $form->{id},
+ );
+
+ $datev->export;
+
+ 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();
(SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
(SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
(SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
- (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
- d.curr AS currencies
+ (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
$q_invdate
FROM defaults d|;
$ref = selectfirst_hashref_query($form, $dbh, $query);
$query = qq|SELECT cp_id, invnumber, transdate AS invdate, duedate,
orddate, quodate, globalproject_id,
ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate,
- intnotes, curr AS currency
+ intnotes, (SELECT cu.name FROM currencies cu WHERE cu.id=ap.currency_id) AS currency, direct_debit,
+ delivery_term_id
FROM ap
WHERE id = ?|;
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
i.id AS invoice_id,
i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber,
i.price_factor_id, i.price_factor, i.marge_price_factor, i.discount,
- p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, p.bin, pr.projectnumber, pg.partsgroup
+ p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, pr.projectnumber, pg.partsgroup
FROM invoice i
JOIN parts p ON (i.parts_id = p.id)
qq|SELECT
v.id AS vendor_id, v.name AS vendor, v.discount as vendor_discount,
v.creditlimit, v.terms, v.notes AS intnotes,
- v.email, v.cc, v.bcc, v.language_id, v.payment_id,
- v.street, v.zipcode, v.city, v.country, v.taxzone_id,
+ v.email, v.cc, v.bcc, v.language_id, v.payment_id, v.delivery_term_id,
+ v.street, v.zipcode, v.city, v.country, v.taxzone_id, cu.name AS curr, v.direct_debit,
$duedate + COALESCE(pt.terms_netto, 0) AS duedate,
b.description AS business
FROM vendor v
LEFT JOIN business b ON (b.id = v.business_id)
LEFT JOIN payment_terms pt ON (v.payment_id = pt.id)
+ LEFT JOIN currencies cu ON (v.currency_id = cu.id)
WHERE 1=1 $where|;
my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
map { $params->{$_} = $ref->{$_} } keys %$ref;
+ # use vendor currency
+ $form->{currency} = $form->{curr};
+
$params->{creditremaining} = $params->{creditlimit};
$query = qq|SELECT SUM(amount - paid) FROM ap WHERE vendor_id = ?|;
$query = qq|SELECT o.amount,
(SELECT e.sell
FROM exchangerate e
- WHERE (e.curr = o.curr)
+ WHERE (e.currency_id = o.currency_id)
AND (e.transdate = o.transdate)) AS exch
FROM oe o
WHERE (o.vendor_id = ?) AND (o.quotation = '0') AND (o.closed = '0')|;
push @values, $form->{"partnumber_$i"};
}
+ # Search for part ID overrides all other criteria.
if ($form->{"id_${i}"}) {
- $where .= qq| AND p.id = ?|;
- push @values, $form->{"id_${i}"};
+ $where = qq|p.id = ?|;
+ @values = ($form->{"id_${i}"});
}
if ($form->{"description_$i"}) {
my $transdate = "";
if ($form->{type} eq "invoice") {
- $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
+ $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
+ : $form->{invdate} ? $dbh->quote($form->{invdate})
+ : "current_date";
} else {
$transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
}
my $query =
qq|SELECT
p.id, p.partnumber, p.description, p.lastcost AS sellprice, p.listprice,
- p.unit, p.assembly, p.bin, p.onhand, p.formel,
+ p.unit, p.assembly, p.onhand, p.formel,
p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
p.inventory_accno_id, p.price_factor_id,
my $i = 0;
while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
- # if ($customertax{$ref->{accno}}) {
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
$i++;
$ptr->{accno} = $i;
# get rest for the vendor
# fax and phone and email as vendor*
my $query =
- qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail
+ qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail,
+ cu.name AS currency
FROM vendor ct
LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id)
+ LEFT JOIN currencies cu ON (ct.currency_id = cu.id)
WHERE (ct.id = ?) $contact
ORDER BY cp.cp_id
LIMIT 1|;
$old_form = save_form();
# Delete all entries in acc_trans from prior payments.
- $self->_delete_payments($form, $dbh);
+ if (SL::DB::Default->get->payments_changeable != 0) {
+ $self->_delete_payments($form, $dbh);
+ }
# Save the new payments the user made before cleaning up $form.
- map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AP_paid_\d+$|^paidaccounts$/, keys %{ $form };
+ map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^gldate_\d+$|^acc_trans_id_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AP_paid_\d+$|^paidaccounts$/, keys %{ $form };
# Clean up $form so that old content won't tamper the results.
%keep_vars = map { $_, 1 } qw(login password id);
}
sub get_duedate {
- $main::lxdebug->enter_sub();
+ $::lxdebug->enter_sub;
- my $self = shift;
- my %params = @_;
+ my ($self, %params) = @_;
if (!$params{vendor_id} || !$params{invdate}) {
- $main::lxdebug->leave_sub();
+ $::lxdebug->leave_sub;
return $params{default};
}
- my $myconfig = \%main::myconfig;
- my $form = $main::form;
-
- my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
-
+ my $dbh = $::form->get_standard_dbh;
my $query = qq|SELECT ?::date + pt.terms_netto
FROM vendor v
LEFT JOIN payment_terms pt ON (pt.id = v.payment_id)
WHERE v.id = ?|;
- my ($sth, $duedate);
-
- if (($sth = $dbh->prepare($query)) && $sth->execute($params{invdate}, conv_i($params{vendor_id}))) {
- ($duedate) = $sth->fetchrow_array();
- $sth->finish();
- } else {
- $dbh->rollback();
- }
+ my ($duedate) = selectfirst_array_query($::form, $dbh, $query, $params{invdate}, $params{vendor_id});
$duedate ||= $params{default};
- $main::lxdebug->leave_sub();
+ $::lxdebug->leave_sub;
return $duedate;
}
projects / kivitendo-erp.git / blobdiff