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 ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
# connect to database, turn off autocommit
- my $dbh = $form->dbconnect_noauto($myconfig);
+ 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, $null, $project_id);
+ 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 $allocated;
- my $taxrate;
- my $taxamount;
- my $taxdiff;
- my $item;
+ my ($basefactor, $baseqty, @taxaccounts, $totaltax);
- my $service_units = AM->retrieve_units($myconfig,$form,"service");
- my $part_units = AM->retrieve_units($myconfig,$form,"dimension");
+ my $all_units = AM->retrieve_units($myconfig, $form);
- if ($form->{id}) {
+ 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});
+ }
+ }
- &reverse_invoice($dbh, $form);
+ 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 {
- my $uid = rand() . time;
-
- $uid .= $form->{login};
+ $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'sell');
+ }
- $uid = substr($uid, 2, 75);
+ $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate});
+ $form->{exchangerate} = 1 unless ($form->{exchangerate} * 1);
- $query = qq|INSERT INTO ap (invnumber, employee_id)
- VALUES ('$uid', (SELECT e.id FROM employee e
- WHERE e.login = '$form->{login}'))|;
- $dbh->do($query) || $form->dberror($query);
+ my %item_units;
+ my $q_item_unit = qq|SELECT unit FROM parts WHERE id = ?|;
+ my $h_item_unit = prepare_query($form, $dbh, $q_item_unit);
- $query = qq|SELECT a.id FROM ap a
- WHERE a.invnumber = '$uid'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
+ my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
+ my $price_factor;
- ($form->{id}) = $sth->fetchrow_array;
- $sth->finish;
- }
+ for my $i (1 .. $form->{rowcount}) {
+ next unless $form->{"id_$i"};
- map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber quonumber);
+ $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
+ $form->{"qty_$i"} *= -1 if $form->{storno};
- my ($amount, $linetotal, $lastinventoryaccno, $lastexpenseaccno);
- my ($netamount, $invoicediff, $expensediff) = (0, 0, 0);
+ 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"}
+ };
- if ($form->{currency} eq $form->{defaultcurrency}) {
- $form->{exchangerate} = 1;
- } else {
- $exchangerate =
- $form->check_exchangerate($myconfig, $form->{currency},
- $form->{transdate}, 'sell');
- }
+ # get item baseunit
+ if (!$item_units{$form->{"id_$i"}}) {
+ do_statement($form, $h_item_unit, $q_item_unit, $form->{"id_$i"});
+ ($item_units{$form->{"id_$i"}}) = $h_item_unit->fetchrow_array();
+ }
- $form->{exchangerate} =
- ($exchangerate)
- ? $exchangerate
- : $form->parse_amount($myconfig, $form->{exchangerate});
+ my $item_unit = $item_units{$form->{"id_$i"}};
- for my $i (1 .. $form->{rowcount}) {
- $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
-
- if ($form->{storno}) {
- $form->{"qty_$i"} *= -1;
+ if (defined($all_units->{$item_unit}->{factor})
+ && ($all_units->{$item_unit}->{factor} ne '')
+ && ($all_units->{$item_unit}->{factor} * 1 != 0)) {
+ $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
+ } else {
+ $basefactor = 1;
}
+ $baseqty = $form->{"qty_$i"} * $basefactor;
+
+ @taxaccounts = split / /, $form->{"taxaccounts_$i"};
+ $taxdiff = 0;
+ $allocated = 0;
+ $taxrate = 0;
+
+ $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
+ (my $fxsellprice = $form->{"sellprice_$i"}) =~ /\.(\d+)/;
+ my $dec = length $1;
+ my $decimalplaces = ($dec > 2) ? $dec : 2;
+
+ 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));
- if ($main::eur) {
- $form->{"inventory_accno_$i"} = $form->{"expense_accno_$i"};
- }
-
- if ($form->{"id_$i"}) {
-
- # get item baseunit
- $query = qq|SELECT p.unit
- FROM parts p
- WHERE p.id = $form->{"id_$i"}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my ($item_unit) = $sth->fetchrow_array();
- $sth->finish;
-
- if ($form->{"inventory_accno_$i"}) {
- if (defined($part_units->{$item_unit}->{factor}) && $part_units->{$item_unit}->{factor} ne '' && $part_units->{$item_unit}->{factor} ne '0') {
- $basefactor = $part_units->{$form->{"unit_$i"}}->{factor} / $part_units->{$item_unit}->{factor};
- } else {
- $basefactor = 1;
- }
- $baseqty = $form->{"qty_$i"} * $basefactor;
} else {
- if (defined($service_units->{$item_unit}->{factor}) && $service_units->{$item_unit}->{factor} ne '' && $service_units->{$item_unit}->{factor} ne '0') {
- $basefactor = $service_units->{$form->{"unit_$i"}}->{factor} / $service_units->{$item_unit}->{factor};
+ $taxamount = $linetotal * $taxrate;
+ }
+
+ $netamount += $linetotal;
+
+ if ($form->round_amount($taxrate, 7) == 0) {
+ if ($form->{taxincluded}) {
+ foreach $item (@taxaccounts) {
+ $taxamount =
+ $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
+ $taxdiff += $taxamount;
+ $form->{amount}{ $form->{id} }{$item} -= $taxamount;
+ }
+ $form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
+
} else {
- $basefactor = 1;
+ map { $form->{amount}{ $form->{id} }{$_} -= $linetotal * $form->{"${_}_rate"} } @taxaccounts;
}
- $baseqty = $form->{"qty_$i"} * $basefactor;
+
+ } else {
+ map { $form->{amount}{ $form->{id} }{$_} -= $taxamount * $form->{"${_}_rate"} / $taxrate } @taxaccounts;
}
- map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
- qw(partnumber description unit);
+ # add purchase to inventory, this one is without the tax!
+ $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
+ $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
+ $linetotal = $form->round_amount($linetotal, 2);
- @taxaccounts = split / /, $form->{"taxaccounts_$i"};
- $taxdiff = 0;
- $allocated = 0;
- $taxrate = 0;
+ # this is the difference for the inventory
+ $invoicediff += ($amount - $linetotal);
- $form->{"sellprice_$i"} =
- $form->parse_amount($myconfig, $form->{"sellprice_$i"});
- my $fxsellprice = $form->{"sellprice_$i"};
+ $form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -= $linetotal;
- my ($dec) = ($fxsellprice =~ /\.(\d+)/);
- $dec = length $dec;
- my $decimalplaces = ($dec > 2) ? $dec : 2;
+ # adjust and round sellprice
+ $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces);
- map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
+ $lastinventoryaccno = $form->{"inventory_accno_$i"};
- if ($form->{"inventory_accno_$i"}) {
+ next if $payments_only;
- $linetotal =
- $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
+ # update parts table
+ $query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|;
+ @values = ($form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
+ do_query($form, $dbh, $query, @values);
- if ($form->{taxincluded}) {
- $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
- $form->{"sellprice_$i"} =
- $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
- } else {
- $taxamount = $linetotal * $taxrate;
- }
+ # 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, 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.parts_id = ?)
+ AND ((i.base_qty + i.allocated) > 0)
+ AND (i.trans_id = a.id)
+ ORDER BY transdate|;
+ # ORDER BY transdate guarantees FIFO
- $netamount += $linetotal;
-
- if ($form->round_amount($taxrate, 7) == 0) {
- if ($form->{taxincluded}) {
- foreach $item (@taxaccounts) {
- $taxamount =
- $form->round_amount($linetotal * $form->{"${item}_rate"} /
- (1 + abs($form->{"${item}_rate"})),
- 2);
- $taxdiff += $taxamount;
- $form->{amount}{ $form->{id} }{$item} -= $taxamount;
- }
- $form->{amount}{ $form->{id} }{ $taxaccounts[0] } += $taxdiff;
- } else {
- map {
- $form->{amount}{ $form->{id} }{$_} -=
- $linetotal * $form->{"${_}_rate"}
- } @taxaccounts;
- }
- } else {
- map {
- $form->{amount}{ $form->{id} }{$_} -=
- $taxamount * $form->{"${_}_rate"} / $taxrate
- } @taxaccounts;
- }
+# 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
- # add purchase to inventory, this one is without the tax!
- $amount =
- $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
- $linetotal =
- $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
- $form->{exchangerate};
- $linetotal = $form->round_amount($linetotal, 2);
-
- # this is the difference for the inventory
- $invoicediff += ($amount - $linetotal);
-
- $form->{amount}{ $form->{id} }{ $form->{"inventory_accno_$i"} } -=
- $linetotal;
-
- # adjust and round sellprice
- $form->{"sellprice_$i"} =
- $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
- $decimalplaces);
-
- # update parts table
- $query = qq|UPDATE parts SET
- lastcost = $form->{"sellprice_$i"}
- WHERE id = $form->{"id_$i"}|;
-
- $dbh->do($query) || $form->dberror($query);
-
- $form->update_balance($dbh, "parts", "onhand",
- qq|id = $form->{"id_$i"}|,
- $baseqty)
- unless $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,
- 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.parts_id = $form->{"id_$i"}
- AND (i.base_qty + i.allocated) > 0
- AND i.trans_id = a.id
- ORDER BY transdate|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my $totalqty = $base_qty;
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-
- my $qty = $ref->{base_qty} + $ref->{allocated};
-
- if (($qty - $totalqty) > 0) {
- $qty = $totalqty;
- }
+# 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 = $baseqty;
- $linetotal = $form->round_amount(($form->{"sellprice_$i"} * $qty) / $basefactor, 2);
+ 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 ( $::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);
+# 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);
+ $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);
- } else {
+ } elsif ($linetotal != 0) {
+ # allocated >= 0
# add entry for inventory, this one is for the sold item
- if ($linetotal != 0) {
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate)
- VALUES ($ref->{trans_id}, $ref->{inventory_accno_id},
- $linetotal, '$ref->{transdate}')|;
- $dbh->do($query) || $form->dberror($query);
-
- # add expense
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, taxkey)
- VALUES ($ref->{trans_id}, $ref->{expense_accno_id},
- | . ($linetotal * -1) . qq|, '$ref->{transdate}',
- (SELECT taxkey from tax WHERE chart_id = $ref->{expense_accno_id}))|;
- $dbh->do($query) || $form->dberror($query);
- }
+ $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);
+ # update allocated for sold item
+ $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1);
- $allocated += $qty;
+ $allocated += $qty;
- last if (($totalqty -= $qty) <= 0);
- }
+ last if ($totalqty -= $qty) <= 0;
+ }
- $sth->finish;
+ $sth->finish();
- $lastinventoryaccno = $form->{"inventory_accno_$i"};
+ } else { # if ($form->{"inventory_accno_id_$i"})
+ # part doesn't have an inventory_accno_id
+ # lastcost of the part is updated at the end
- } else {
+ $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
- $linetotal =
- $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
+ if ($form->{taxincluded}) {
+ $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
+ $form->{"sellprice_$i"} = $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
- if ($form->{taxincluded}) {
- $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
+ } else {
+ $taxamount = $linetotal * $taxrate;
+ }
- $form->{"sellprice_$i"} =
- $form->{"sellprice_$i"} * (1 / (1 + $taxrate));
- } else {
- $taxamount = $linetotal * $taxrate;
- }
+ $netamount += $linetotal;
- $netamount += $linetotal;
-
- if ($form->round_amount($taxrate, 7) == 0) {
- if ($form->{taxincluded}) {
- foreach $item (@taxaccounts) {
- $taxamount =
- $linetotal * $form->{"${item}_rate"} /
- (1 + abs($form->{"${item}_rate"}));
- $totaltax += $taxamount;
- $form->{amount}{ $form->{id} }{$item} -= $taxamount;
- }
- } else {
- map {
- $form->{amount}{ $form->{id} }{$_} -=
- $linetotal * $form->{"${_}_rate"}
- } @taxaccounts;
+ if ($form->round_amount($taxrate, 7) == 0) {
+ if ($form->{taxincluded}) {
+ foreach $item (@taxaccounts) {
+ $taxamount = $linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"}));
+ $totaltax += $taxamount;
+ $form->{amount}{ $form->{id} }{$item} -= $taxamount;
}
} else {
- map {
- $form->{amount}{ $form->{id} }{$_} -=
- $taxamount * $form->{"${_}_rate"} / $taxrate
- } @taxaccounts;
+ map { $form->{amount}{ $form->{id} }{$_} -= $linetotal * $form->{"${_}_rate"} } @taxaccounts;
}
+ } else {
+ map { $form->{amount}{ $form->{id} }{$_} -= $taxamount * $form->{"${_}_rate"} / $taxrate } @taxaccounts;
+ }
- $amount =
- $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
- $linetotal =
- $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
- $form->{exchangerate};
- $linetotal = $form->round_amount($linetotal, 2);
-
- # this is the difference for expense
- $expensediff += ($amount - $linetotal);
-
- # add amount to expense
- $form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -=
- $linetotal;
+ $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;
+ $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
+ $linetotal = $form->round_amount($linetotal, 2);
- $lastexpenseaccno = $form->{"expense_accno_$i"};
+ # this is the difference for expense
+ $expensediff += ($amount - $linetotal);
- # adjust and round sellprice
- $form->{"sellprice_$i"} =
- $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
- $decimalplaces);
+ # add amount to expense
+ $form->{amount}{ $form->{id} }{ $form->{"expense_accno_$i"} } -= $linetotal;
- # update lastcost
- $query = qq|UPDATE parts SET
- lastcost = $form->{"sellprice_$i"}
- WHERE id = $form->{"id_$i"}|;
+ $lastexpenseaccno = $form->{"expense_accno_$i"};
- $dbh->do($query) || $form->dberror($query);
+ # adjust and round sellprice
+ $form->{"sellprice_$i"} = $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces);
- }
+ next if $payments_only;
- $deliverydate =
- ($form->{"deliverydate_$i"})
- ? qq|'$form->{"deliverydate_$i"}'|
- : "NULL";
-
- # save detail record in invoice table
- $query = qq|INSERT INTO invoice (trans_id, parts_id, description, qty, base_qty,
- sellprice, fxsellprice, allocated, unit, deliverydate,
- project_id, serialnumber)
- VALUES ($form->{id}, $form->{"id_$i"},
- '$form->{"description_$i"}', | . ($form->{"qty_$i"} * -1) . qq|, | . ($baseqty * -1) . qq|,
- $form->{"sellprice_$i"}, $fxsellprice, $allocated,
- '$form->{"unit_$i"}', $deliverydate, | . conv_i($form->{"project_id_$i"}) . qq|,
- '$form->{"serialnumber_$i"}')|;
- $dbh->do($query) || $form->dberror($query);
+ # update lastcost
+ $query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|;
+ do_query($form, $dbh, $query, $form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
}
+
+ 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 (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 = ($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();
+
$project_id = conv_i($form->{"globalproject_id"});
$form->{datepaid} = $form->{invdate};
# total payments
for my $i (1 .. $form->{paidaccounts}) {
- $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
- $form->{paid} += $form->{"paid_$i"};
- $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
+ $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
+ $form->{paid} += $form->{"paid_$i"};
+ $form->{datepaid} = $form->{"datepaid_$i"} if $form->{"datepaid_$i"};
}
my ($tax, $paiddiff) = (0, 0);
$netamount = $amount;
foreach $item (split / /, $form->{taxaccounts}) {
- $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
+ $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
$form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
- $amount = $form->{amount}{ $form->{id} }{$item} * -1;
- $tax += $amount;
+
+ $amount = $form->{amount}{ $form->{id} }{$item} * -1;
+ $tax += $amount;
$netamount -= $amount;
}
$invoicediff += $paiddiff;
$expensediff += $paiddiff;
- ######## this only applies to tax included
- if ($lastinventoryaccno) {
- $form->{amount}{ $form->{id} }{$lastinventoryaccno} -= $invoicediff;
- }
- if ($lastexpenseaccno) {
- $form->{amount}{ $form->{id} }{$lastexpenseaccno} -= $expensediff;
- }
+######## 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};
$netamount = $amount;
+
foreach my $item (split / /, $form->{taxaccounts}) {
- $form->{amount}{ $form->{id} }{$item} =
- $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
- $amount =
- $form->round_amount(
- $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1,
- 2);
- $paiddiff +=
- $amount - $form->{amount}{ $form->{id} }{$item} *
- $form->{exchangerate} * -1;
- $form->{amount}{ $form->{id} }{$item} =
- $form->round_amount($amount * -1, 2);
- $amount = $form->{amount}{ $form->{id} }{$item} * -1;
- $tax += $amount;
+ $form->{amount}{ $form->{id} }{$item} = $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
+ $amount = $form->round_amount( $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1, 2);
+ $paiddiff += $amount - $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate} * -1;
+ $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount * -1, 2);
+ $amount = $form->{amount}{ $form->{id} }{$item} * -1;
+ $tax += $amount;
}
}
$form->{amount}{ $form->{id} }{ $form->{AP} } = $netamount + $tax;
- if ($form->{paid} != 0) {
- $form->{paid} =
- $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff,
- 2);
- }
- # update exchangerate
- if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
- $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0,
- $form->{exchangerate});
- }
+ $form->{paid} = $form->round_amount($form->{paid} * $form->{exchangerate} + $paiddiff, 2) if $form->{paid} != 0;
+
+# update exchangerate
- # record acc_trans transactions
+ $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, 0, $form->{exchangerate})
+ if ($form->{currency} ne $defaultcurrency) && !$exchangerate;
+
+# record acc_trans transactions
foreach my $trans_id (keys %{ $form->{amount} }) {
foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
- if (
- ($form->{amount}{$trans_id}{$accno} =
- $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
- ) != 0
- ) {
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, taxkey, project_id)
- VALUES ($trans_id, (SELECT c.id FROM chart c
- WHERE c.accno = '$accno'),
- $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
- (SELECT taxkey_id FROM chart WHERE accno = '$accno'), ?)|;
- do_query($form, $dbh, $query, $project_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 = ?), ?)|;
+ @values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
+ conv_date($form->{invdate}), $accno, $project_id);
+ do_query($form, $dbh, $query, @values);
}
}
# deduct payment differences from paiddiff
for my $i (1 .. $form->{paidaccounts}) {
if ($form->{"paid_$i"} != 0) {
- $amount =
- $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
+ $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
$paiddiff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
}
}
# force AP entry if 0
- $form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid}
- if ($form->{amount}{ $form->{id} }{ $form->{AP} } == 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;
+ }
- if ($form->{"paid_$i"} != 0) {
- my ($accno) = split /--/, $form->{"AP_paid_$i"};
- $form->{"datepaid_$i"} = $form->{invdate}
- unless ($form->{"datepaid_$i"});
- $form->{datepaid} = $form->{"datepaid_$i"};
-
- $amount = (
- $form->round_amount(
- $form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2
- )
- ) * -1;
-
- # record AP
-
- if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, project_id)
- VALUES ($form->{id}, (SELECT c.id FROM chart c
- WHERE c.accno = '$form->{AP}'),
- $amount, '$form->{"datepaid_$i"}', ?)|;
- do_query($form, $dbh, $query, $project_id);
- }
+ next if $form->{"paid_$i"} == 0;
- # record payment
+ my ($accno) = split /--/, $form->{"AP_paid_$i"};
+ $form->{"datepaid_$i"} = $form->{invdate} unless ($form->{"datepaid_$i"});
+ $form->{datepaid} = $form->{"datepaid_$i"};
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo, project_id)
- VALUES ($form->{id}, (SELECT c.id FROM chart c
- WHERE c.accno = '$accno'),
- $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
- '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|;
- do_query($form, $dbh, $query, $project_id);
+ $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2) * -1;
- $exchangerate = 0;
+ # 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)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
+ (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ @values = (conv_i($form->{id}), $form->{AP}, $amount,
+ $form->{"datepaid_$i"}, $form->{AP}, $project_id);
+ do_query($form, $dbh, $query, @values);
+ }
- 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)
- ? $exchangerate
- : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
- }
+ # record payment
+ my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
- # exchangerate difference
- $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
- $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
-
- # gain/loss
- $amount =
- ($form->{"paid_$i"} * $form->{exchangerate}) -
- ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
- if ($amount > 0) {
- $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
- $amount;
- } else {
- $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
- $amount;
- }
+ $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"},
+ $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
+ do_query($form, $dbh, $query, @values);
- $paiddiff = 0;
+ $exchangerate = 0;
- # update exchange rate
- if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
- $form->update_exchangerate($dbh, $form->{currency},
- $form->{"datepaid_$i"},
- 0, $form->{"exchangerate_$i"});
- }
+ if ($form->{currency} eq $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"});
}
+
+ # exchangerate difference
+ $form->{fx}{$accno}{ $form->{"datepaid_$i"} } += $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $paiddiff;
+
+ # gain/loss
+ $amount =
+ ($form->{"paid_$i"} * $form->{exchangerate}) -
+ ($form->{"paid_$i"} * $form->{"exchangerate_$i"});
+ if ($amount > 0) {
+ $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
+ } else {
+ $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
+ }
+
+ $paiddiff = 0;
+
+ # update exchange rate
+ $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, 0, $form->{"exchangerate_$i"})
+ if ($form->{currency} ne $defaultcurrency) && !$exchangerate;
}
# record exchange rate differences and gains/losses
foreach my $accno (keys %{ $form->{fx} }) {
foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
- if (
- ($form->{fx}{$accno}{$transdate} =
- $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
- ) != 0
- ) {
-
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, cleared, fx_transaction, project_id)
- VALUES ($form->{id}, (SELECT c.id FROM chart c
- WHERE c.accno = '$accno'),
- $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', ?)|;
- do_query($form, $dbh, $query, $project_id);
- }
+ $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);
+ do_query($form, $dbh, $query, @values);
}
}
- $amount = $netamount + $tax;
+ IO->set_datepaid(table => 'ap', id => $form->{id}, dbh => $dbh);
- # set values which could be empty
- $form->{taxincluded} *= 1;
- my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
- my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
+ if ($payments_only) {
+ $query = qq|UPDATE ap SET paid = ? WHERE id = ?|;
+ do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
- ($null, $form->{department_id}) = split(/--/, $form->{department});
- $form->{department_id} *= 1;
- $form->{payment_id} *= 1;
- $form->{language_id} *= 1;
- $form->{taxzone_id} *= 1;
- $form->{storno} *= 1;
+ if (!$provided_dbh) {
+ $dbh->commit();
+ $dbh->disconnect();
+ }
+ $main::lxdebug->leave_sub();
+ return;
+ }
- $form->{invnumber} = $form->{id} unless $form->{invnumber};
+ $amount = $netamount + $tax;
+
+ # set values which could be empty
+ my $taxzone_id = $form->{taxzone_id} * 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);
# save AP record
- $query = qq|UPDATE ap set
- invnumber = '$form->{invnumber}',
- ordnumber = '$form->{ordnumber}',
- quonumber = '$form->{quonumber}',
- transdate = '$form->{invdate}',
- orddate = | . conv_dateq($form->{"orddate"}) . qq|,
- quodate = | . conv_dateq($form->{"quodate"}) . qq|,
- vendor_id = $form->{vendor_id},
- amount = $amount,
- netamount = $netamount,
- paid = $form->{paid},
- datepaid = $datepaid,
- duedate = $duedate,
- invoice = '1',
- taxzone_id = '$form->{taxzone_id}',
- taxincluded = '$form->{taxincluded}',
- notes = '$form->{notes}',
- intnotes = '$form->{intnotes}',
- curr = '$form->{currency}',
- department_id = $form->{department_id},
- storno = '$form->{storno}',
- globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
- cp_id = | . conv_i($form->{cp_id}, 'NULL') . qq|
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|UPDATE ap SET
+ invnumber = ?, ordnumber = ?, quonumber = ?, transdate = ?,
+ 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 = ?
+ 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}),
+ conv_i($form->{globalproject_id}),
+ conv_i($form->{id})
+ );
+ do_query($form, $dbh, $query, @values);
if ($form->{storno}) {
- $query = qq| update ap set paid=paid+amount where id=$form->{storno_id}|;
- $dbh->do($query) || $form->dberror($query);
- $query = qq| update ap set storno='$form->{storno}' where id=$form->{storno_id}|;
- $dbh->do($query) || $form->dberror($query);
- $query = qq§ update ap set intnotes='Rechnung storniert am $form->{invdate} ' || intnotes where id=$form->{storno_id}§;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq| update ap set paid=amount where id=$form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|UPDATE ap SET paid = paid + amount WHERE id = ?|;
+ do_query($form, $dbh, $query, conv_i($form->{storno_id}));
+
+ $query = qq|UPDATE ap SET storno = 't' WHERE 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}));
+
+ $query = qq|UPDATE ap SET paid = amount WHERE id = ?|;
+ do_query($form, $dbh, $query, conv_i($form->{id}));
}
# add shipto
$form->{name} = $form->{vendor};
- $form->{name} =~ s/--$form->{vendor_id}//;
+ $form->{name} =~ s/--\Q$form->{vendor_id}\E//;
$form->add_shipto($dbh, $form->{id}, "AP");
# delete zero entries
- $query = qq|DELETE FROM acc_trans
- WHERE amount = 0|;
- $dbh->do($query) || $form->dberror($query);
+ do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE amount = 0|);
+
+ 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};
- Common::webdav_folder($form) if ($main::webdav);
+ ARAP->close_orders_if_billed('dbh' => $dbh,
+ 'arap_id' => $form->{id},
+ 'table' => 'ap',);
- my $rc = $dbh->commit;
- $dbh->disconnect;
+ my $rc = 1;
+ if (!$provided_dbh) {
+ $rc = $dbh->commit();
+ $dbh->disconnect();
+ }
$main::lxdebug->leave_sub();
my ($dbh, $form) = @_;
# reverse inventory items
- my $query = qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,
- i.qty, i.allocated, i.sellprice
- FROM invoice i, parts p
- WHERE i.parts_id = p.id
- AND i.trans_id = $form->{id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my $query =
+ qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id, i.qty, i.allocated, i.sellprice
+ FROM invoice i, parts p
+ WHERE (i.parts_id = p.id)
+ AND (i.trans_id = ?)|;
+ my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
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);
- if ($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
- if ($ref->{allocated} > 0) {
-
- # get references for sold items
- $query = qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
- FROM invoice i, ar a
- WHERE i.parts_id = $ref->{parts_id}
- AND i.allocated < 0
- AND i.trans_id = a.id
- ORDER BY transdate DESC|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $pthref = $sth->fetchrow_hashref(NAME_lc)) {
- my $qty = $ref->{allocated};
- if (($ref->{allocated} + $pthref->{allocated}) > 0) {
- $qty = $pthref->{allocated} * -1;
- }
+ next unless $ref->{inventory_accno_id};
+
+ # if $ref->{allocated} > 0 than we sold that many items
+ next if ($ref->{allocated} <= 0);
+
+ # get references for sold items
+ $query =
+ qq|SELECT i.id, i.trans_id, i.allocated, a.transdate
+ FROM invoice i, ar a
+ WHERE (i.parts_id = ?)
+ AND (i.allocated < 0)
+ AND (i.trans_id = a.id)
+ ORDER BY transdate DESC|;
+ my $sth2 = prepare_execute_query($form, $dbh, $query, $ref->{parts_id});
+
+ while (my $pthref = $sth2->fetchrow_hashref("NAME_lc")) {
+ my $qty = $ref->{allocated};
+ if (($ref->{allocated} + $pthref->{allocated}) > 0) {
+ $qty = $pthref->{allocated} * -1;
+ }
- my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
+ my $amount = $form->round_amount($ref->{sellprice} * $qty, 2);
- #adjust allocated
- $form->update_balance($dbh, "invoice", "allocated",
- qq|id = $pthref->{id}|, $qty);
+ #adjust allocated
+ $form->update_balance($dbh, "invoice", "allocated", qq|id = $pthref->{id}|, $qty);
- $form->update_balance(
- $dbh,
- "acc_trans",
- "amount",
- qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{expense_accno_id} AND transdate = '$pthref->{transdate}'|,
- $amount);
+ $form->update_balance($dbh, "acc_trans", "amount",
+ qq| (trans_id = $pthref->{trans_id})
+ AND (chart_id = $ref->{expense_accno_id})
+ AND (transdate = '$pthref->{transdate}')|,
+ $amount);
- $form->update_balance(
- $dbh,
- "acc_trans",
- "amount",
- qq|trans_id = $pthref->{trans_id} AND chart_id = $ref->{inventory_accno_id} AND transdate = '$pthref->{transdate}'|,
- $amount * -1);
+ $form->update_balance($dbh, "acc_trans", "amount",
+ qq| (trans_id = $pthref->{trans_id})
+ AND (chart_id = $ref->{inventory_accno_id})
+ AND (transdate = '$pthref->{transdate}')|,
+ $amount * -1);
- last if (($ref->{allocated} -= $qty) <= 0);
- }
- $sth->finish;
+ last if (($ref->{allocated} -= $qty) <= 0);
}
- }
+ $sth2->finish();
}
- $sth->finish;
+ $sth->finish();
+
+ my $id = conv_i($form->{id});
# delete acc_trans
- $query = qq|DELETE FROM acc_trans
- WHERE trans_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
+ do_query($form, $dbh, $query, $id);
# delete invoice entries
- $query = qq|DELETE FROM invoice
- WHERE trans_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|DELETE FROM invoice WHERE trans_id = ?|;
+ do_query($form, $dbh, $query, $id);
- $query = qq|DELETE FROM shipto
- WHERE trans_id = $form->{id} AND module = 'AP'|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|;
+ do_query($form, $dbh, $query, $id);
$main::lxdebug->leave_sub();
}
$main::lxdebug->enter_sub();
my ($self, $myconfig, $form) = @_;
-
+ my $query;
# connect to database
my $dbh = $form->dbconnect_noauto($myconfig);
&reverse_invoice($dbh, $form);
# delete zero entries
- my $query = qq|DELETE FROM acc_trans
- WHERE amount = 0|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|DELETE FROM acc_trans WHERE amount = 0|;
+ do_query($form, $dbh, $query);
# delete AP record
- my $query = qq|DELETE FROM ap
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ $query = qq|DELETE FROM ap WHERE id = ?|;
+ do_query($form, $dbh, $query, conv_i($form->{id}));
my $rc = $dbh->commit;
$dbh->disconnect;
my ($self, $myconfig, $form) = @_;
# connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
+ my $dbh = $form->dbconnect($myconfig);
- my $query;
+ my ($query, $sth, $ref, $q_invdate);
+
+ if (!$form->{id}) {
+ $q_invdate = qq|, COALESCE((SELECT transdate FROM ar WHERE id = (SELECT MAX(id) FROM ar)), current_date) AS invdate|;
+ if ($form->{vendor_id}) {
+ my $vendor_id = $dbh->quote($form->{vendor_id} * 1);
+ $q_invdate .=
+ qq|, COALESCE((SELECT transdate FROM ar WHERE id = (SELECT MAX(id) FROM ar)), current_date) +
+ COALESCE((SELECT pt.terms_netto
+ FROM vendor v
+ LEFT JOIN payment_terms pt ON (v.payment_id = pt.id)
+ WHERE v.id = $vendor_id),
+ 0) AS duedate|;
+ }
+ }
- if ($form->{id}) {
-
- # get default accounts and last invoice number
- $query = qq|SELECT (SELECT c.accno FROM chart c
- WHERE d.inventory_accno_id = c.id) AS inventory_accno,
- (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
- FROM defaults d|;
- } else {
- $query = qq|SELECT (SELECT c.accno FROM chart c
- WHERE d.inventory_accno_id = c.id) AS inventory_accno,
- (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,
- current_date AS invdate
- FROM defaults d|;
+ # get default accounts and last invoice number
+
+ $query = qq|SELECT
+ (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
+ (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
+ $q_invdate
+ FROM defaults d|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query);
+ map { $form->{$_} = $ref->{$_} } keys %$ref;
+
+ if (!$form->{id}) {
+ $dbh->disconnect();
+ $main::lxdebug->leave_sub();
+
+ return;
}
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- my $ref = $sth->fetchrow_hashref(NAME_lc);
+ # retrieve invoice
+ $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
+ FROM ap
+ WHERE id = ?|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
-
- if ($form->{id}) {
-
- # retrieve invoice
- $query = qq|SELECT a.cp_id, a.invnumber, a.transdate AS invdate, a.duedate,
- a.orddate, a.quodate, a.globalproject_id,
- a.ordnumber, a.quonumber, a.paid, a.taxincluded, a.notes, a.taxzone_id, a.storno, a.gldate,
- a.intnotes, a.curr AS currency
- FROM ap a
- WHERE a.id = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
- map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
-
- $form->{exchangerate} =
- $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
- "sell");
-
- # get shipto
- $query = qq|SELECT s.* FROM shipto s
- WHERE s.trans_id = $form->{id} AND s.module = 'AP'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
- delete($ref->{id});
- map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
-
- my $transdate =
- $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
-
- if(!$form->{taxzone_id}) {
- $form->{taxzone_id} = 0;
- }
- # retrieve individual items
- $query = qq|SELECT c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
- 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,
- p.partnumber, i.description, i.qty, i.fxsellprice AS sellprice, p.inventory_accno_id AS part_inventory_accno_id,
- i.parts_id AS id, i.unit, p.bin, i.deliverydate,
- pr.projectnumber,
- i.project_id, i.serialnumber,
- pg.partsgroup
- FROM invoice i
- JOIN parts p ON (i.parts_id = p.id)
- LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
- LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
- LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
- LEFT JOIN project pr ON (i.project_id = pr.id)
- LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
- WHERE i.trans_id = $form->{id}
- ORDER BY i.id|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- if (!$ref->{"part_inventory_accno_id"}) {
- map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
- }
- delete($ref->{"part_inventory_accno_id"});
-
- while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
- my $query = qq| SELECT accno AS inventory_accno, new_chart_id AS inventory_new_chart, date($transdate) - valid_from AS inventory_valid FROM chart WHERE id = $ref->{inventory_new_chart}|;
- my $stw = $dbh->prepare($query);
- $stw->execute || $form->dberror($query);
- ($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
- $stw->finish;
- }
-
- while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
- my $query = qq| SELECT accno AS income_accno, new_chart_id AS income_new_chart, date($transdate) - valid_from AS income_valid FROM chart WHERE id = $ref->{income_new_chart}|;
- my $stw = $dbh->prepare($query);
- $stw->execute || $form->dberror($query);
- ($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
- $stw->finish;
+
+ $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell");
+
+ # get shipto
+ $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
+ delete $ref->{id};
+ map { $form->{$_} = $ref->{$_} } keys %$ref;
+
+ my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
+ my $taxzone_id = $form->{taxzone_id} * 1;
+
+ $taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id));
+
+ # retrieve individual items
+ $query =
+ qq|SELECT
+ c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
+ 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.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
+ JOIN parts p ON (i.parts_id = p.id)
+ LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
+ LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
+ LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
+ LEFT JOIN project pr ON (i.project_id = pr.id)
+ LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
+
+ WHERE i.trans_id = ?
+
+ ORDER BY i.id|;
+ $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
+
+ 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"});
+
+ foreach my $type (qw(inventory income expense)) {
+ while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
+ my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
+ @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
}
-
- while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
- my $query = qq| SELECT accno AS expense_accno, new_chart_id AS expense_new_chart, date($transdate) - valid_from AS expense_valid FROM chart WHERE id = $ref->{expense_new_chart}|;
- my $stw = $dbh->prepare($query);
- $stw->execute || $form->dberror($query);
- ($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
- $stw->finish;
+ }
+
+ # get tax rates and description
+ 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
+ LEFT JOIN chart c ON (c.id = t.chart_id)
+ WHERE t.id in
+ (SELECT tk.tax_id FROM taxkeys tk
+ WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
+ AND (startdate <= $transdate)
+ ORDER BY startdate DESC
+ LIMIT 1)
+ ORDER BY c.accno|;
+ my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
+ $ref->{taxaccounts} = "";
+
+ my $i = 0;
+ while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
+ if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
+ $i++;
+ $ptr->{accno} = $i;
}
- # get tax rates and description
- $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 LEFT JOIN chart c on (c.id=t.chart_id)
- WHERE t.id in (SELECT tk.tax_id from taxkeys tk where tk.chart_id = (SELECT id from chart WHERE accno='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)
- ORDER BY c.accno|;
- $stw = $dbh->prepare($query);
- $stw->execute || $form->dberror($query);
- $ref->{taxaccounts} = "";
- my $i = 0;
- while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
-
- # if ($customertax{$ref->{accno}}) {
- if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
- $i++;
- $ptr->{accno} = $i;
- }
- $ref->{taxaccounts} .= "$ptr->{accno} ";
- if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
- $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
- $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
- $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
- $form->{taxaccounts} .= "$ptr->{accno} ";
- }
+ $ref->{taxaccounts} .= "$ptr->{accno} ";
+ if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
+ $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
+ $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
+ $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
+ $form->{taxaccounts} .= "$ptr->{accno} ";
}
- chop $ref->{taxaccounts};
- push @{ $form->{invoice_details} }, $ref;
- $stw->finish;
}
- $sth->finish;
- Common::webdav_folder($form) if ($main::webdav);
+ chop $ref->{taxaccounts};
+ push @{ $form->{invoice_details} }, $ref;
+ $stw->finish();
}
+ $sth->finish();
- my $rc = $dbh->commit;
- $dbh->disconnect;
+ Common::webdav_folder($form);
- $main::lxdebug->leave_sub();
+ $dbh->disconnect();
- return $rc;
+ $main::lxdebug->leave_sub();
}
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($params->{vendor_id});
+ my $vnr = conv_i($params->{vendornumber});
+
my $duedate =
- ($form->{invdate})
- ? "to_date('$form->{invdate}', '$dateformat')"
+ ($params->{invdate})
+ ? "to_date(" . $dbh->quote($params->{invdate}) . ", '$dateformat')"
: "current_date";
- $form->{vendor_id} *= 1;
-
# get vendor
- my $query = qq|SELECT v.name AS vendor, v.creditlimit, v.terms,
- v.email, v.cc, v.bcc, v.language_id, v.payment_id,
- v.street, v.zipcode, v.city, v.country, v.taxzone_id,
- $duedate + v.terms AS duedate,
- b.description AS business
- FROM vendor v
- LEFT JOIN business b ON b.id = v.business_id
- WHERE v.id = $form->{vendor_id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
- map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
-
- $form->{creditremaining} = $form->{creditlimit};
- $query = qq|SELECT SUM(a.amount - a.paid)
- FROM ap a
- WHERE a.vendor_id = $form->{vendor_id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- ($form->{creditremaining}) -= $sth->fetchrow_array;
-
- $sth->finish;
+ 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.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,
+ 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)
+ WHERE 1=1 $where|;
+ my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
+ map { $params->{$_} = $ref->{$_} } keys %$ref;
+
+ $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);
+ $params->{creditremaining} -= $unpaid_invoices;
$query = qq|SELECT o.amount,
- (SELECT e.sell FROM exchangerate e
- WHERE e.curr = o.curr
- AND e.transdate = o.transdate)
- FROM oe o
- WHERE o.vendor_id = $form->{vendor_id}
- AND o.quotation = '0'
- AND o.closed = '0'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my ($amount, $exch) = $sth->fetchrow_array) {
+ (SELECT e.sell
+ FROM exchangerate e
+ WHERE (e.curr = o.curr)
+ AND (e.transdate = o.transdate)) AS exch
+ FROM oe o
+ WHERE (o.vendor_id = ?) AND (o.quotation = '0') AND (o.closed = '0')|;
+ 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;
+ $sth->finish();
# get shipto if we do not convert an order or invoice
- if (!$form->{shipto}) {
- map { delete $form->{$_} }
- qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
-
- $query = qq|SELECT s.* FROM shipto s
- WHERE s.trans_id = $form->{vendor_id} AND s.module= 'CT'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
- map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
- }
+ if (!$params->{shipto}) {
+ delete @{$params}{qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail)};
- # get taxes for vendor
- $query = qq|SELECT c.accno
- FROM chart c
- JOIN vendortax v ON (v.chart_id = c.id)
- WHERE v.vendor_id = $form->{vendor_id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my $vendortax = ();
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $vendortax{ $ref->{accno} } = 1;
+ $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module= 'CT')|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, $vid);
+ @{$params}{keys %$ref} = @{$ref}{keys %$ref};
+ map { $params->{$_} = $ref->{$_} } keys %$ref;
}
- $sth->finish;
-
- if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
+ if (!$params->{id} && $params->{type} !~ /_(order|quotation)/) {
# setup last accounts used
- $query = qq|SELECT c.accno, c.description, c.link, c.category
- FROM chart c
- JOIN acc_trans ac ON (ac.chart_id = c.id)
- JOIN ap a ON (a.id = ac.trans_id)
- WHERE a.vendor_id = $form->{vendor_id}
- AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
- AND a.id IN (SELECT max(a2.id) FROM ap a2
- WHERE a2.vendor_id = $form->{vendor_id})|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $query =
+ qq|SELECT c.id, c.accno, c.description, c.link, c.category
+ FROM chart c
+ JOIN acc_trans ac ON (ac.chart_id = c.id)
+ JOIN ap a ON (a.id = ac.trans_id)
+ WHERE (a.vendor_id = ?)
+ AND (NOT ((c.link LIKE '%_tax%') OR (c.link LIKE '%_paid%')))
+ AND (a.id IN (SELECT max(a2.id) FROM ap a2 WHERE a2.vendor_id = ?))|;
+ my $refs = selectall_hashref_query($form, $dbh, $query, $vid, $vid);
my $i = 0;
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ for $ref (@$refs) {
if ($ref->{category} eq 'E') {
$i++;
- $form->{"AP_amount_$i"} = "$ref->{accno}--$ref->{description}";
+ 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|;
+ ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id}, $params->{initial_transdate});
+ $params->{"taxchart_$i"} = "${tax_id}--${rate}";
+ }
+
+ $params->{"AP_amount_$i"} = "$ref->{accno}--$tax_id";
}
+
if ($ref->{category} eq 'L') {
- $form->{APselected} = $form->{AP_1} =
- "$ref->{accno}--$ref->{description}";
+ $params->{APselected} = $params->{AP_1} = $ref->{accno};
}
}
- $sth->finish;
- $form->{rowcount} = $i if ($i && !$form->{type});
+ $params->{rowcount} = $i if ($i && !$params->{type});
}
- $dbh->disconnect;
+ $dbh->disconnect();
$main::lxdebug->leave_sub();
}
# don't include assemblies or obsolete parts
my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
+ my @values;
- if ($form->{"partnumber_$i"}) {
- my $partnumber = $form->like(lc $form->{"partnumber_$i"});
- $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
+ foreach my $table_column (qw(p.partnumber p.description pg.partsgroup)) {
+ my $field = (split m{\.}, $table_column)[1];
+ next unless $form->{"${field}_${i}"};
+ $where .= " AND lower(${table_column}) LIKE lower(?)";
+ push @values, '%' . $form->{"${field}_${i}"} . '%';
}
-
- if ($form->{"description_$i"}) {
- my $description = $form->like(lc $form->{"description_$i"});
- $where .= " AND lower(p.description) LIKE '$description'";
- }
-
- if ($form->{"partsgroup_$i"}) {
- my $partsgroup = $form->like(lc $form->{"partsgroup_$i"});
- $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'";
+ #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"}) {
my $transdate = "";
if ($form->{type} eq "invoice") {
- $transdate =
- $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
+ $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
} else {
- $transdate =
- $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
+ $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.inventory_accno_id,
- c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
- 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,
- p.unit, p.assembly, p.bin, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
- pg.partsgroup, p.formel
- FROM parts p
- LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
- LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
- LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
- LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
- WHERE $where|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ my $taxzone_id = $form->{taxzone_id} * 1;
+ $taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id));
+
+ 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.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
+ p.inventory_accno_id, p.price_factor_id,
+
+ pfac.factor AS price_factor,
+
+ c1.accno AS inventory_accno,
+ c1.new_chart_id AS inventory_new_chart,
+ date($transdate) - c1.valid_from AS inventory_valid,
+
+ 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,
+
+ pg.partsgroup
+
+ FROM parts p
+ LEFT JOIN chart c1 ON
+ ((SELECT inventory_accno_id
+ FROM buchungsgruppen
+ WHERE id = p.buchungsgruppen_id) = c1.id)
+ LEFT JOIN chart c2 ON
+ ((SELECT income_accno_id_${taxzone_id}
+ FROM buchungsgruppen
+ WHERE id = p.buchungsgruppen_id) = c2.id)
+ LEFT JOIN chart c3 ON
+ ((SELECT expense_accno_id_${taxzone_id}
+ FROM buchungsgruppen
+ WHERE id = p.buchungsgruppen_id) = c3.id)
+ LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
+ LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
+ 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")) {
# 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};
- $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
- FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
- WHERE t.id in (SELECT tk.tax_id from taxkeys tk where tk.chart_id = (SELECT id from chart WHERE accno='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)
- ORDER BY c.accno|;
- $stw = $dbh->prepare($query);
- $stw->execute || $form->dberror($query);
+ 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
+ LEFT JOIN chart c on (c.id = t.chart_id)
+ WHERE t.id IN
+ (SELECT tk.tax_id
+ FROM taxkeys tk
+ WHERE tk.chart_id =
+ (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND (startdate <= $transdate)
+ ORDER BY startdate DESC
+ LIMIT 1)
+ ORDER BY c.accno|;
+ my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
$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)) {
$i++;
$ptr->{accno} = $i;
}
+
$ref->{taxaccounts} .= "$ptr->{accno} ";
- if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
- $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
- $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
- $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
- $form->{taxaccounts} .= "$ptr->{accno} ";
+
+ if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
+ $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
+ $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
+ $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
+ $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;
+ $stw->finish();
chop $ref->{taxaccounts};
+ $ref->{onhand} *= 1;
+
push @{ $form->{item_list} }, $ref;
}
- $sth->finish;
- $dbh->disconnect;
+ $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();
}
# connect to database
my $dbh = $form->dbconnect($myconfig);
- # get contact id, set it if nessessary
- ($null, $form->{cp_id}) = split /--/, $form->{contact};
+ my @values;
- $contact = "";
+ # get contact id, set it if nessessary
+ $form->{cp_id} *= 1;
+ my $contact = "";
if ($form->{cp_id}) {
- $contact = "and cp.cp_id = $form->{cp_id}";
+ $contact = "AND cp.cp_id = ?";
+ push @values, $form->{cp_id};
}
# 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
- FROM vendor ct
- LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
- WHERE ct.id = $form->{vendor_id} $contact order by cp.cp_id limit 1|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
+ FROM vendor ct
+ LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id)
+ WHERE (ct.id = ?) $contact
+ ORDER BY cp.cp_id
+ LIMIT 1|;
+ my $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id}, @values);
# remove id and taxincluded before copy back
delete @$ref{qw(id taxincluded)};
map { $form->{$_} = $ref->{$_} } keys %$ref;
- $sth->finish;
- $dbh->disconnect;
+ 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();
}
# connect to database
my $dbh = $form->dbconnect($myconfig);
- my $query = qq|SELECT c.accno, c.description, c.link
- FROM chart c
- WHERE c.link LIKE '%IC%'
- ORDER BY c.accno|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my $query =
+ qq|SELECT accno, description, link
+ FROM chart
+ WHERE link LIKE '%IC%'
+ 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} },
}
}
- $sth->finish;
+ $sth->finish();
+ $dbh->disconnect();
+
+ $main::lxdebug->leave_sub();
+}
+
+sub _delete_payments {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $form, $dbh) = @_;
+
+ my @delete_acc_trans_ids;
+
+ # Delete old payment entries from acc_trans.
+ my $query =
+ qq|SELECT acc_trans_id
+ FROM acc_trans
+ WHERE (trans_id = ?) AND fx_transaction
+
+ UNION
+
+ 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_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
+
+ $query =
+ 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.acc_trans_id
+ OFFSET 1|;
+ push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
+
+ 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);
+ }
+
$main::lxdebug->leave_sub();
}
# connect to database, turn off autocommit
my $dbh = $form->dbconnect_noauto($myconfig);
- $form->{datepaid} = $form->{invdate};
+ my (%payments, $old_form, $row, $item, $query, %keep_vars);
- # total payments, don't move we need it here
- for my $i (1 .. $form->{paidaccounts}) {
- $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
- $form->{paid} += $form->{"paid_$i"};
- $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
+ $old_form = save_form();
+
+ # Delete all entries in acc_trans from prior payments.
+ if ($::lx_office_conf{features}->{payments_changeable} != 0) {
+ $self->_delete_payments($form, $dbh);
}
- $form->{exchangerate} =
- $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
- "buy");
+ # Save the new payments the user made before cleaning up $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 };
- my $project_id = conv_i($form->{"globalproject_id"});
+ # Clean up $form so that old content won't tamper the results.
+ %keep_vars = map { $_, 1 } qw(login password id);
+ map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };
- # record payments and offsetting AP
- for my $i (1 .. $form->{paidaccounts}) {
+ # Retrieve the invoice from the database.
+ $self->retrieve_invoice($myconfig, $form);
- if ($form->{"paid_$i"} != 0) {
- my ($accno) = split /--/, $form->{"AP_paid_$i"};
- $form->{"datepaid_$i"} = $form->{invdate}
- unless ($form->{"datepaid_$i"});
- $form->{datepaid} = $form->{"datepaid_$i"};
-
- $exchangerate = 0;
- if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
- $form->{"exchangerate_$i"} = 1;
- } else {
- $exchangerate =
- $form->check_exchangerate($myconfig, $form->{currency},
- $form->{"datepaid_$i"}, 'buy');
-
- $form->{"exchangerate_$i"} =
- ($exchangerate)
- ? $exchangerate
- : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
- }
+ # Set up the content of $form in the way that IR::post_invoice() expects.
+ $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
- # record AP
- $amount =
- $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
- 2) * -1;
+ for $row (1 .. scalar @{ $form->{invoice_details} }) {
+ $item = $form->{invoice_details}->[$row - 1];
+ map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice);
- $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
- WHERE c.accno = '$form->{AP}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
- $dbh->do($query) || $form->dberror($query);
+ map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
+ }
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, project_id)
- VALUES ($form->{id}, (SELECT c.id FROM chart c
- WHERE c.accno = '$form->{AP}'),
- $amount, '$form->{"datepaid_$i"}', ?)|;
- do_query($form, $dbh, $query, $project_id);
+ $form->{rowcount} = scalar @{ $form->{invoice_details} };
+ delete @{$form}{qw(invoice_details paidaccounts storno paid)};
+ # Restore the payment options from the user input.
+ map { $form->{$_} = $payments{$_} } keys %payments;
- $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
- WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
- $dbh->do($query) || $form->dberror($query);
+ # Get the AP accno (which is normally done by Form::create_links()).
+ $query =
+ qq|SELECT c.accno
+ 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.acc_trans_id
+ LIMIT 1|;
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
- source, memo, project_id)
- VALUES ($form->{id}, (SELECT c.id FROM chart c
- WHERE c.accno = '$accno'),
- $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
- '$form->{"source_$i"}', '$form->{"memo_$i"}', ?)|;
- do_query($form, $dbh, $query, $project_id);
+ ($form->{AP}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
+ # Post the new payments.
+ $self->post_invoice($myconfig, $form, $dbh, 1);
- # gain/loss
- $amount =
- $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
- $form->{"exchangerate_$i"};
- if ($amount > 0) {
- $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
- $amount;
- } else {
- $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
- $amount;
- }
+ restore_form($old_form);
- $diff = 0;
+ my $rc = $dbh->commit();
+ $dbh->disconnect();
- # update exchange rate
- if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
- $form->update_exchangerate($dbh, $form->{currency},
- $form->{"datepaid_$i"},
- $form->{"exchangerate_$i"}, 0);
- }
- }
- }
+ $main::lxdebug->leave_sub();
- # record exchange rate differences and gains/losses
- foreach my $accno (keys %{ $form->{fx} }) {
- foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
- if (
- ($form->{fx}{$accno}{$transdate} =
- $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
- ) != 0
- ) {
- $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
- WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
- $dbh->do($query) || $form->dberror($query);
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
- transdate, cleared, fx_transaction, project_id)
- VALUES ($form->{id},
- (SELECT c.id FROM chart c
- WHERE c.accno = '$accno'),
- $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1', ?)|;
- do_query($form, $dbh, $query, $project_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 $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
- # save AP record
- my $query = qq|UPDATE ap set
- paid = $form->{paid},
- datepaid = $datepaid
- WHERE id=$form->{id}|;
+ my $myconfig = \%main::myconfig;
+ my $form = $main::form;
- $dbh->do($query) || $form->dberror($query);
+ my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
- my $rc = $dbh->commit;
- $dbh->disconnect;
+ 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 $rc;
+ return $duedate;
}
+
1;
projects / kivitendo-erp.git / blobdiff