package IR;
use SL::AM;
+use SL::ARAP;
use SL::Common;
+use SL::CVar;
use SL::DBUtils;
+use SL::DO;
+use SL::GenericTranslations;
+use SL::IO;
use SL::MoreCommon;
use List::Util qw(min);
+use strict;
+
sub post_invoice {
$main::lxdebug->enter_sub();
my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
$form->{defaultcurrency} = $form->get_default_currency($myconfig);
+ my $ic_cvar_configs = CVar->get_configs(module => 'IC',
+ dbh => $dbh);
+
my ($query, $sth, @values, $project_id);
my ($allocated, $taxrate, $taxamount, $taxdiff, $item);
my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
my $exchangerate = 0;
+ my ($basefactor, $baseqty, @taxaccounts, $totaltax);
my $all_units = AM->retrieve_units($myconfig, $form);
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
$form->{"qty_$i"} *= -1 if $form->{storno};
- $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"} if $main::eur;
+ 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"}
+ };
# get item baseunit
if (!$item_units{$form->{"id_$i"}}) {
map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
$price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
-
+ #####################################################################
+ # das ist aus IS.pm kopiert. schlimm. jb 7.10.2009
+ # ich würde mir wünschen, dass diese vier stellen zusammengefasst werden
+ # ... vier stellen = (einkauf + verkauf) * (maske + backend)
+ # ansonsten stolpert man immer wieder viermal statt einmal heftig
+ # und auch das undo discount formatting ist nicht besonders wartungsfreundlich
+
+ # keine ahnung wofür das in IS.pm gemacht wird:
+ # my ($dec) = ($fxsellprice =~ /\.(\d+)/);
+ # $dec = length $dec;
+ # my $decimalplaces = ($dec > 2) ? $dec : 2;
+
+ # undo discount formatting
+ $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
+ # deduct discount
+ $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});
+
+ ######################################################################
if ($form->{"inventory_accno_$i"}) {
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
if ($form->{taxincluded}) {
+
$taxamount = $linetotal * ($taxrate / (1 + $taxrate));
$form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
+
} else {
$taxamount = $linetotal * $taxrate;
}
@values = ($form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
do_query($form, $dbh, $query, @values);
- $form->update_balance($dbh, "parts", "onhand", qq|id = ?|, $baseqty, $form->{"id_$i"}) if !$form->{shipped};
-
# check if we sold the item already and
# make an entry for the expense and inventory
$query =
- qq|SELECT i.id, i.qty, i.allocated, i.trans_id,
+ 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
WHERE (i.parts_id = p.id)
AND ((i.base_qty + i.allocated) > 0)
AND (i.trans_id = a.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
+# ---+-----+-----------+----------+--------------------+------------------+------------
+# 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
+# -----+-----------+----------+------------
+# 2 | 0 | 2 | 1000.00000
+
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
- my $totalqty = $base_qty;
+ my $totalqty = $baseqty;
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
my $qty = min $totalqty, ($ref->{base_qty} + $ref->{allocated});
$linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
- if ($ref->{allocated} < 0) {
-
- # we have an entry for it already, adjust amount
- $form->update_balance($dbh, "acc_trans", "amount",
- qq| (trans_id = $ref->{trans_id})
- AND (chart_id = $ref->{inventory_accno_id})
- AND (transdate = '$ref->{transdate}')|,
- $linetotal);
-
- $form->update_balance($dbh, "acc_trans", "amount",
- qq| (trans_id = $ref->{trans_id})
- AND (chart_id = $ref->{expense_accno_id})
- AND (transdate = '$ref->{transdate}')|,
- $linetotal * -1);
-
- } elsif ($linetotal != 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});
- 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});
- do_query($form, $dbh, $query, @values);
- }
+ if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
+ # Warenbestandsbuchungen nur bei Bestandsmethode
+
+ if ($ref->{allocated} < 0) {
+
+# we have an entry for it already, adjust amount
+ $form->update_balance($dbh, "acc_trans", "amount",
+ qq| (trans_id = $ref->{trans_id})
+ AND (chart_id = $ref->{inventory_accno_id})
+ AND (transdate = '$ref->{transdate}')|,
+ $linetotal);
+
+ $form->update_balance($dbh, "acc_trans", "amount",
+ qq| (trans_id = $ref->{trans_id})
+ AND (chart_id = $ref->{expense_accno_id})
+ AND (transdate = '$ref->{transdate}')|,
+ $linetotal * -1);
+
+ } elsif ($linetotal != 0) {
+
+ # 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});
+ 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});
+ do_query($form, $dbh, $query, @values);
+ }
+ };
# update allocated for sold item
$form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1);
$sth->finish();
} else { # if ($form->{"inventory_accno_id_$i"})
+ # part doesn't have an inventory_accno_id
+ # lastcost of the part is updated at the end
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
next if $payments_only;
# save detail record in invoice table
+ my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
+
$query =
- qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty,
- sellprice, fxsellprice, allocated, unit, deliverydate,
+ qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty, base_qty,
+ sellprice, fxsellprice, discount, allocated, unit, deliverydate,
project_id, serialnumber, price_factor_id, price_factor, marge_price_factor)
- VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT factor FROM price_factors WHERE id = ?), ?)|;
- @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
- $form->{"description_$i"}, $form->{"qty_$i"} * -1,
- $baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $allocated,
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT factor FROM price_factors WHERE id = ?), ?)|;
+ @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
+ $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"} * -1,
+ $baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated,
$form->{"unit_$i"}, conv_date($form->{deliverydate}),
conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"},
conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}));
do_query($form, $dbh, $query, @values);
+
+ CVar->save_custom_variables(module => 'IC',
+ sub_module => 'invoice',
+ trans_id => $invoice_id,
+ configs => $ic_cvar_configs,
+ variables => $form,
+ name_prefix => 'ic_',
+ name_postfix => "_$i",
+ dbh => $dbh);
}
$h_item_unit->finish();
$invoicediff += $paiddiff;
$expensediff += $paiddiff;
- ######## this only applies to tax included
-
+######## this only applies to tax included
+
+ # in the sales invoice case rounding errors only have to be corrected for
+ # income accounts, it is enough to add the total rounding error to one of
+ # the income accounts, with the one assigned to the last row being used
+ # (lastinventoryaccno)
+
+ # in the purchase invoice case rounding errors may be split between
+ # inventory accounts and expense accounts. After rounding, an error of 1
+ # cent is introduced if the total rounding error exceeds 0.005. The total
+ # error is made up of $invoicediff and $expensediff, however, so if both
+ # values are below 0.005, but add up to a total >= 0.005, correcting
+ # lastinventoryaccno and lastexpenseaccno separately has no effect after
+ # rounding. This caused bug 1579. Therefore when the combined total exceeds
+ # 0.005, but neither do individually, the account with the larger value
+ # shall receive the total rounding error, and the next time it is rounded
+ # the 1 cent correction will be introduced.
+
$form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff if $lastinventoryaccno;
$form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff if $lastexpenseaccno;
+ if ( (abs($expensediff)+abs($invoicediff)) >= 0.005 and abs($expensediff) < 0.005 and abs($invoicediff) < 0.005 ) {
+
+ # in total the rounding error adds up to 1 cent effectively, correct the
+ # larger of the two numbers
+
+ if ( abs($form->{amount}{ $form->{id} }{$lastinventoryaccno}) > abs($form->{amount}{ $form->{id} }{$lastexpenseaccno}) ) {
+ # $invoicediff has already been deducted, now also deduct expensediff
+ $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $expensediff;
+ } else {
+ # expensediff has already been deducted, now also deduct invoicediff
+ $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $invoicediff;
+ };
+ };
+
} else {
$amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
$paiddiff = $amount - $netamount * $form->{exchangerate};
$form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
-
+
$form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff, 2) if $form->{paid} != 0;
- # update exchangerate
-
+# update exchangerate
+
$form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0, $form->{exchangerate})
if ($form->{currency} ne $defaultcurrency) && !$exchangerate;
- # record acc_trans transactions
+# record acc_trans transactions
foreach my $trans_id (keys %{ $form->{amount} }) {
foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
$form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
+
next if $payments_only || !$form->{amount}{$trans_id}{$accno};
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
@values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
conv_date($form->{invdate}), $accno, $project_id);
do_query($form, $dbh, $query, @values);
}
# force AP entry if 0
-
+
$form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid} if $form->{amount}{$form->{id}}{$form->{AP}} == 0;
# record payments and offsetting AP
for my $i (1 .. $form->{paidaccounts}) {
+ if ($form->{"acc_trans_id_$i"}
+ && $payments_only
+ && ($::lx_office_conf{features}->{payments_changeable} == 0)) {
+ next;
+ }
+
next if $form->{"paid_$i"} == 0;
my ($accno) = split /--/, $form->{"AP_paid_$i"};
}
# 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 = ?), ?, ?, ?, ?,
+ 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)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
(SELECT taxkey_id 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, $project_id);
do_query($form, $dbh, $query, @values);
$exchangerate = 0;
}
}
+ IO->set_datepaid(table => 'ap', id => $form->{id}, dbh => $dbh);
+
if ($payments_only) {
- $query = qq|UPDATE ap SET paid = ?, datepaid = ? WHERE id = ?|;
- do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
+ $query = qq|UPDATE ap SET paid = ? WHERE id = ?|;
+ do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
if (!$provided_dbh) {
$dbh->commit();
# set values which could be empty
my $taxzone_id = $form->{taxzone_id} * 1;
- $form->{department_id} = (split /--/, $form->{department})[1];
+
+ # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
+ # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
+ # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
+ # copy & paste von IS.pm
+ if (!$form->{department_id}){
+ $form->{department_id} = (split /--/, $form->{department})[1];
+ }
$form->{invnumber} = $form->{id} unless $form->{invnumber};
$taxzone_id = 0 if (3 < $taxzone_id) || (0 > $taxzone_id);
$query = qq|UPDATE ap SET
invnumber = ?, ordnumber = ?, quonumber = ?, transdate = ?,
orddate = ?, quodate = ?, vendor_id = ?, amount = ?,
- netamount = ?, paid = ?, duedate = ?, datepaid = ?,
+ netamount = ?, paid = ?, duedate = ?,
invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?,
intnotes = ?, curr = ?, storno_id = ?, storno = ?,
- cp_id = ?, employee_id = ?, department_id = ?,
+ cp_id = ?, employee_id = ?, department_id = ?,
globalproject_id = ?
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}), $form->{paid} ? conv_date($form->{datepaid}) : undef,
+ $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}),
+ conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}),
conv_i($form->{globalproject_id}),
conv_i($form->{id})
);
do_query($form, $dbh, $query, conv_i($form->{storno_id}));
$query = qq!UPDATE ap SET intnotes = ? || intnotes WHERE id = ?!;
- do_query($form, $dbh, $query, 'Rechnung storniert am $form->{invdate} ', conv_i($form->{storno_id}));
+ do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{storno_id}));
$query = qq|UPDATE ap SET paid = amount WHERE id = ?|;
do_query($form, $dbh, $query, conv_i($form->{id}));
# delete zero entries
do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE amount = 0|);
- Common::webdav_folder($form) if ($main::webdav);
+ Common::webdav_folder($form);
+
+ # Link this record to the records it was created from.
+ RecordLinks->create_links('dbh' => $dbh,
+ 'mode' => 'ids',
+ 'from_table' => 'oe',
+ 'from_ids' => $form->{convert_from_oe_ids},
+ 'to_table' => 'ap',
+ 'to_id' => $form->{id},
+ );
+ delete $form->{convert_from_oe_ids};
+
+ my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
+ if (scalar @convert_from_do_ids) {
+ DO->close_orders('dbh' => $dbh,
+ 'ids' => \@convert_from_do_ids);
+
+ RecordLinks->create_links('dbh' => $dbh,
+ 'mode' => 'ids',
+ 'from_table' => 'delivery_orders',
+ 'from_ids' => \@convert_from_do_ids,
+ 'to_table' => 'ap',
+ 'to_id' => $form->{id},
+ );
+ }
+ delete $form->{convert_from_do_ids};
- my $rc = 1;
+ ARAP->close_orders_if_billed('dbh' => $dbh,
+ 'arap_id' => $form->{id},
+ 'table' => 'ap',);
+ my $rc = 1;
if (!$provided_dbh) {
$rc = $dbh->commit();
$dbh->disconnect();
my $netamount = 0;
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
$netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
next unless $ref->{inventory_accno_id};
- # update onhand
- $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|, $ref->{qty});
-
# if $ref->{allocated} > 0 than we sold that many items
next if ($ref->{allocated} <= 0);
ORDER BY transdate DESC|;
my $sth2 = prepare_execute_query($form, $dbh, $query, $ref->{parts_id});
- while (my $pthref = $sth2->fetchrow_hashref(NAME_lc)) {
+ while (my $pthref = $sth2->fetchrow_hashref("NAME_lc")) {
my $qty = $ref->{allocated};
if (($ref->{allocated} + $pthref->{allocated}) > 0) {
$qty = $pthref->{allocated} * -1;
c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid,
c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
- i.description, 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.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
FROM invoice i
ORDER BY i.id|;
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ # Retrieve custom variables.
+ my $cvars = CVar->get_custom_variables(dbh => $dbh,
+ module => 'IC',
+ sub_module => 'invoice',
+ trans_id => $ref->{invoice_id},
+ );
+ map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
+ delete $ref->{invoice_id};
+
map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
delete($ref->{"part_inventory_accno_id"});
$ref->{taxaccounts} = "";
my $i = 0;
- while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
+ while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
$i++;
$ptr->{accno} = $i;
}
$sth->finish();
- Common::webdav_folder($form) if ($main::webdav);
+ Common::webdav_folder($form);
$dbh->disconnect();
sub get_vendor {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form, $params) = @_;
+
+ $params = $form unless defined $params && ref $params eq "HASH";
# connect to database
my $dbh = $form->dbconnect($myconfig);
my $dateformat = $myconfig->{dateformat};
$dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
- my $vid = conv_i($form->{vendor_id});
+ my $vid = conv_i($params->{vendor_id});
+ my $vnr = conv_i($params->{vendornumber});
my $duedate =
- ($form->{invdate})
- ? "to_date(" . $dbh->quote($form->{invdate}) . ", '$dateformat')"
+ ($params->{invdate})
+ ? "to_date(" . $dbh->quote($params->{invdate}) . ", '$dateformat')"
: "current_date";
# get vendor
+ my @values = ();
+ my $where = '';
+ if ($vid) {
+ $where .= 'AND v.id = ?';
+ push @values, $vid;
+ }
+ if ($vnr) {
+ $where .= 'AND v.vendornumber = ?';
+ push @values, $vnr;
+ }
my $query =
qq|SELECT
- v.name AS vendor, v.creditlimit, v.terms, v.notes AS intnotes,
+ 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,
$duedate + COALESCE(pt.terms_netto, 0) AS duedate,
FROM vendor v
LEFT JOIN business b ON (b.id = v.business_id)
LEFT JOIN payment_terms pt ON (v.payment_id = pt.id)
- WHERE v.id = ?|;
- $ref = selectfirst_hashref_query($form, $dbh, $query, $vid);
- map { $form->{$_} = $ref->{$_} } keys %$ref;
+ WHERE 1=1 $where|;
+ my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
+ map { $params->{$_} = $ref->{$_} } keys %$ref;
- $form->{creditremaining} = $form->{creditlimit};
+ $params->{creditremaining} = $params->{creditlimit};
$query = qq|SELECT SUM(amount - paid) FROM ap WHERE vendor_id = ?|;
my ($unpaid_invoices) = selectfirst_array_query($form, $dbh, $query, $vid);
- $form->{creditremaining} -= $unpaid_invoices;
+ $params->{creditremaining} -= $unpaid_invoices;
$query = qq|SELECT o.amount,
(SELECT e.sell
my $sth = prepare_execute_query($form, $dbh, $query, $vid);
while (my ($amount, $exch) = $sth->fetchrow_array()) {
$exch = 1 unless $exch;
- $form->{creditremaining} -= $amount * $exch;
+ $params->{creditremaining} -= $amount * $exch;
}
$sth->finish();
# get shipto if we do not convert an order or invoice
- if (!$form->{shipto}) {
- delete @{$form}{qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail)};
+ if (!$params->{shipto}) {
+ delete @{$params}{qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail)};
$query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module= 'CT')|;
$ref = selectfirst_hashref_query($form, $dbh, $query, $vid);
- @{$form}{keys %$ref} = @{$ref}{keys %$ref};
- map { $form->{$_} = $ref->{$_} } keys %$ref;
+ @{$params}{keys %$ref} = @{$ref}{keys %$ref};
+ map { $params->{$_} = $ref->{$_} } keys %$ref;
}
- if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
+ if (!$params->{id} && $params->{type} !~ /_(order|quotation)/) {
# setup last accounts used
$query =
qq|SELECT c.id, c.accno, c.description, c.link, c.category
for $ref (@$refs) {
if ($ref->{category} eq 'E') {
$i++;
-
- if ($form->{initial_transdate}) {
+ my ($tax_id, $rate);
+ if ($params->{initial_transdate}) {
my $tax_query = qq|SELECT tk.tax_id, t.rate FROM taxkeys tk
LEFT JOIN tax t ON (tk.tax_id = t.id)
WHERE (tk.chart_id = ?) AND (startdate <= ?)
ORDER BY tk.startdate DESC
LIMIT 1|;
- my ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id}, $form->{initial_transdate});
- $form->{"taxchart_$i"} = "${tax_id}--${rate}";
+ ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id}, $params->{initial_transdate});
+ $params->{"taxchart_$i"} = "${tax_id}--${rate}";
}
- $form->{"AP_amount_$i"} = "$ref->{accno}--$tax_id";
+ $params->{"AP_amount_$i"} = "$ref->{accno}--$tax_id";
}
if ($ref->{category} eq 'L') {
- $form->{APselected} = $form->{AP_1} = $ref->{accno};
+ $params->{APselected} = $params->{AP_1} = $ref->{accno};
}
}
- $form->{rowcount} = $i if ($i && !$form->{type});
+ $params->{rowcount} = $i if ($i && !$params->{type});
}
$dbh->disconnect();
$where .= " AND lower(${table_column}) LIKE lower(?)";
push @values, '%' . $form->{"${field}_${i}"} . '%';
}
+ #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
+ if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
+ $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
+ push @values, $form->{"partnumber_$i"};
+ }
+
+ # Search for part ID overrides all other criteria.
+ if ($form->{"id_${i}"}) {
+ $where = qq|p.id = ?|;
+ @values = ($form->{"id_${i}"});
+ }
if ($form->{"description_$i"}) {
$where .= " ORDER BY p.description";
WHERE $where|;
my $sth = prepare_execute_query($form, $dbh, $query, @values);
+ my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
+ FROM translation tr
+ WHERE tr.language_id = ? AND tr.parts_id = ?| ],
+ [ qq|SELECT tr.translation, tr.longdescription
+ FROM translation tr
+ WHERE tr.language_id IN
+ (SELECT id
+ FROM language
+ WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
+ AND tr.parts_id = ?
+ LIMIT 1| ] );
+ map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
+
$form->{item_list} = [];
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
# In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
# es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
delete($ref->{inventory_accno_id});
# get tax rates and description
- $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
+ my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
$query =
qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
FROM tax t
$ref->{taxaccounts} = "";
my $i = 0;
- while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
+ while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
# if ($customertax{$ref->{accno}}) {
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
$form->{taxaccounts} .= "$ptr->{accno} ";
}
+ if ($form->{language_id}) {
+ for my $spec (@translation_queries) {
+ do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
+ my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
+ next unless $translation;
+ $ref->{description} = $translation;
+ $ref->{longdescription} = $longdescription;
+ last;
+ }
+ }
}
$stw->finish();
chop $ref->{taxaccounts};
+ $ref->{onhand} *= 1;
+
push @{ $form->{item_list} }, $ref;
}
$sth->finish();
+ $_->[1]->finish for @translation_queries;
+
+ foreach my $item (@{ $form->{item_list} }) {
+ my $custom_variables = CVar->get_custom_variables(module => 'IC',
+ trans_id => $item->{id},
+ dbh => $dbh,
+ );
+
+ map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
+ }
+
$dbh->disconnect();
$main::lxdebug->leave_sub();
my @values;
# get contact id, set it if nessessary
- $form->{cp_id} = (split /--/, $form->{contact})[1];
+ $form->{cp_id} *= 1;
my $contact = "";
if ($form->{cp_id}) {
$contact = "AND cp.cp_id = ?";
map { $form->{$_} = $ref->{$_} } keys %$ref;
+ my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
+ 'module' => 'CT',
+ 'trans_id' => $form->{vendor_id});
+ map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
+
+ $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
+ 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
+ 'allow_fallback' => 1);
+
$dbh->disconnect();
$main::lxdebug->leave_sub();
ORDER BY accno|;
my $sth = prepare_execute_query($query, $dbh, $query);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
foreach my $key (split(/:/, $ref->{link})) {
if ($key =~ /IC/) {
push @{ $form->{IC_links}{$key} },
my ($self, $form, $dbh) = @_;
- my @delete_oids;
+ my @delete_acc_trans_ids;
# Delete old payment entries from acc_trans.
my $query =
- qq|SELECT oid
+ qq|SELECT acc_trans_id
FROM acc_trans
WHERE (trans_id = ?) AND fx_transaction
UNION
- SELECT at.oid
+ SELECT at.acc_trans_id
FROM acc_trans at
LEFT JOIN chart c ON (at.chart_id = c.id)
WHERE (trans_id = ?) AND (c.link LIKE '%AP_paid%')|;
- push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
+ push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
$query =
- qq|SELECT at.oid
+ qq|SELECT at.acc_trans_id
FROM acc_trans at
LEFT JOIN chart c ON (at.chart_id = c.id)
WHERE (trans_id = ?)
AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%'))
- ORDER BY at.oid
+ ORDER BY at.acc_trans_id
OFFSET 1|;
- push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
+ push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
- if (@delete_oids) {
- $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
+ if (@delete_acc_trans_ids) {
+ $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
do_query($form, $dbh, $query);
}
$old_form = save_form();
# Delete all entries in acc_trans from prior payments.
- $self->_delete_payments($form, $dbh);
+ if ($::lx_office_conf{features}->{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);
LEFT JOIN chart c ON (at.chart_id = c.id)
WHERE (trans_id = ?)
AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%'))
- ORDER BY at.oid
+ ORDER BY at.acc_trans_id
LIMIT 1|;
($form->{AP}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
return $rc;
}
+sub get_duedate {
+ $main::lxdebug->enter_sub();
+
+ my $self = shift;
+ my %params = @_;
+
+ if (!$params{vendor_id} || !$params{invdate}) {
+ $main::lxdebug->leave_sub();
+ return $params{default};
+ }
+
+ my $myconfig = \%main::myconfig;
+ my $form = $main::form;
+
+ my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
+
+ 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();
+ }
+
+ $duedate ||= $params{default};
+
+ $main::lxdebug->leave_sub();
+
+ return $duedate;
+}
+
+
1;