use SL::DBUtils;
use SL::DO;
use SL::GenericTranslations;
+use SL::HTML::Restrict;
use SL::IO;
use SL::MoreCommon;
use SL::DB::Default;
+use SL::DB::TaxZone;
use List::Util qw(min);
use strict;
# connect to database, turn off autocommit
my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
+ my $restricter = SL::HTML::Restrict->create;
+
$form->{defaultcurrency} = $form->get_default_currency($myconfig);
my $defaultcurrency = $form->{defaultcurrency};
&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, currency_id) VALUES (?, '', (SELECT id FROM currencies WHERE name=?))|, $form->{id}, $form->{currency});
+ do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber, currency_id, taxzone_id) VALUES (?, '', (SELECT id FROM currencies WHERE name=?), ?)|, $form->{id}, $form->{currency}, $form->{taxzone_id});
}
}
my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
my $price_factor;
+ my @processed_invoice_ids;
for my $i (1 .. $form->{rowcount}) {
next unless $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;
+ # copied from IS.pm, with some changes (no decimalplaces corrections here etc)
+ # TODO maybe use PriceTaxCalculation or something like this for backends (IR.pm / IS.pm)
# undo discount formatting
$form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
my $taxzone = $form->{taxzone_id} * 1;
$query =
qq|SELECT i.id, i.qty, i.allocated, i.trans_id, i.base_qty,
- bg.inventory_accno_id, bg.expense_accno_id_${taxzone} AS expense_accno_id, a.transdate
- FROM invoice i, ar a, parts p, buchungsgruppen bg
+ bg.inventory_accno_id, tc.expense_accno_id AS expense_accno_id, a.transdate
+ FROM invoice i, ar a, parts p, buchungsgruppen bg, taxzone_charts tc
WHERE (i.parts_id = p.id)
AND (i.parts_id = ?)
AND ((i.base_qty + i.allocated) > 0)
AND (i.trans_id = a.id)
AND (p.buchungsgruppen_id = bg.id)
+ AND (tc.buchungsgruppen_id = p.buchungsgruppen_id)
+ AND (tc.taxzone_id = ${taxzone})
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
+ # 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
+ # 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
+ # 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"}));
if ($ref->{allocated} < 0) {
-# we have an entry for it already, adjust amount
+ # 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})
$ref->{inventory_accno_id});
do_query($form, $dbh, $query, @values);
-# add expense
+ # add expense
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,
(SELECT taxkey_id
FROM taxkeys
next if $payments_only;
- # save detail record in invoice table
- my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
+ if (!$form->{"invoice_id_$i"}) {
+ # there is no persistent id, therefore create one with all necessary constraints
+ my $q_invoice_id = qq|SELECT nextval('invoiceid')|;
+ my $h_invoice_id = prepare_query($form, $dbh, $q_invoice_id);
+ do_statement($form, $h_invoice_id, $q_invoice_id);
+ $form->{"invoice_id_$i"} = $h_invoice_id->fetchrow_array();
+ my $q_create_invoice_id = qq|INSERT INTO invoice (id, trans_id, parts_id) values (?, ?, ?)|;
+ do_query($form, $dbh, $q_create_invoice_id, conv_i($form->{"invoice_id_$i"}), conv_i($form->{id}), conv_i($form->{"id_$i"}));
+ $h_invoice_id->finish();
+ }
- $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,
+ # save detail record in invoice table
+ $query = <<SQL;
+ UPDATE invoice SET trans_id = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
+ sellprice = ?, fxsellprice = ?, discount = ?, allocated = ?, unit = ?, deliverydate = ?,
+ project_id = ?, serialnumber = ?, price_factor_id = ?,
+ price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?,
+ active_price_source = ?, active_discount_source = ?
+ WHERE id = ?
+SQL
+
+ @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
+ $form->{"description_$i"}, $restricter->process($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"}));
+ conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
+ $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
+ conv_i($form->{"invoice_id_$i"}));
do_query($form, $dbh, $query, @values);
+ push @processed_invoice_ids, $form->{"invoice_id_$i"};
CVar->save_custom_variables(module => 'IC',
sub_module => 'invoice',
- trans_id => $invoice_id,
+ trans_id => $form->{"invoice_id_$i"},
configs => $ic_cvar_configs,
variables => $form,
name_prefix => 'ic_',
# set values which could be empty
my $taxzone_id = $form->{taxzone_id} * 1;
+ $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id);
# Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
# korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
}
$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 = ?, ordnumber = ?, quonumber = ?, transdate = ?,
}
- # add shipto
$form->{name} = $form->{vendor};
$form->{name} =~ s/--\Q$form->{vendor_id}\E//;
+
+ # add shipto
$form->add_shipto($dbh, $form->{id}, "AP");
# delete zero entries
'arap_id' => $form->{id},
'table' => 'ap',);
+ # search for orphaned invoice items
+ $query = sprintf 'SELECT id FROM invoice WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_invoice_ids;
+ @values = (conv_i($form->{id}), map { conv_i($_) } @processed_invoice_ids);
+ my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
+ if (scalar @orphaned_ids) {
+ # clean up invoice items
+ $query = sprintf 'DELETE FROM invoice WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
+ do_query($form, $dbh, $query, @orphaned_ids);
+ }
+
# safety check datev export
if ($::instance_conf->get_datev_check_on_purchase_invoice) {
my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
$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 = ?|;
- do_query($form, $dbh, $query, $id);
-
$query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|;
do_query($form, $dbh, $query, $id);
&reverse_invoice($dbh, $form);
+ my @values = (conv_i($form->{id}));
+
# delete zero entries
+ # wtf? use case for this?
$query = qq|DELETE FROM acc_trans WHERE amount = 0|;
do_query($form, $dbh, $query);
- # delete AP record
- $query = qq|DELETE FROM ap WHERE id = ?|;
- do_query($form, $dbh, $query, conv_i($form->{id}));
+
+ my @queries = (
+ qq|DELETE FROM invoice WHERE trans_id = ?|,
+ qq|DELETE FROM ap WHERE id = ?|,
+ );
+
+ map { do_query($form, $dbh, $_, @values) } @queries;
my $rc = $dbh->commit;
$dbh->disconnect;
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));
+ my $taxzone_id = $form->{taxzone_id} * 1;
+ $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id);
# retrieve individual items
$query =
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,
+ i.price_factor_id, i.price_factor, i.marge_price_factor, i.discount, i.active_price_source, i.active_discount_source,
p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, pr.projectnumber, pg.partsgroup
FROM invoice i
JOIN parts p ON (i.parts_id = p.id)
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 chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc where tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
+ LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc where tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_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)
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"});
}
$sth->finish();
- # get shipto if we do not convert an order or invoice
- 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);
- @{$params}{keys %$ref} = @{$ref}{keys %$ref};
- map { $params->{$_} = $ref->{$_} } keys %$ref;
- }
-
if (!$params->{id} && $params->{type} !~ /_(order|quotation)/) {
# setup last accounts used
$query =
$where .= " AND lower(${table_column}) LIKE lower(?)";
push @values, '%' . $form->{"${field}_${i}"} . '%';
}
- #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
+
+ my (%mm_by_id);
if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
- $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
- push @values, $form->{"partnumber_$i"};
- }
+ $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
+ push @values, $form->{"partnumber_$i"};
+
+ # also search hits in makemodels, but only cache the results by id and merge later
+ my $mm_query = qq|
+ SELECT parts_id, model FROM makemodel
+ LEFT JOIN parts ON parts.id = parts_id
+ WHERE NOT parts.obsolete AND model ILIKE ? AND (make IS NULL OR make = ?);
+ |;
+ my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, '%' . $form->{"partnumber_$i"} . '%', $::form->{vendor_id});
+ my @mm_ids = map { $_->{parts_id} } @$mm_results;
+ push @{$mm_by_id{ $_->{parts_id} } ||= []}, $_ for @$mm_results;
+
+ if (@mm_ids) {
+ $where .= qq| OR p.id IN (| . join(',', ('?') x @mm_ids) . qq|)|;
+ push @values, @mm_ids;
+ }
+ }
# Search for part ID overrides all other criteria.
if ($form->{"id_${i}"}) {
}
my $taxzone_id = $form->{taxzone_id} * 1;
- $taxzone_id = 0 if ((3 < $taxzone_id) || (0 > $taxzone_id));
+ $taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id);
my $query =
qq|SELECT
p.unit, p.assembly, p.onhand, p.formel,
p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
p.inventory_accno_id, p.price_factor_id,
+ p.ean,
pfac.factor AS price_factor,
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)
+ ((SELECT tc.income_accno_id
+ FROM taxzone_charts tc
+ WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_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)
+ ((SELECT tc.expense_accno_id
+ FROM taxzone_charts tc
+ WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_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|;
$form->{item_list} = [];
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ if ($mm_by_id{$ref->{id}}) {
+ $ref->{makemodels} = $mm_by_id{$ref->{id}};
+ push @{ $ref->{matches} ||= [] }, $::locale->text('Model') . ': ' . join ', ', map { $_->{model} } @{ $mm_by_id{$ref->{id}} };
+ }
+
+ if ($ref->{ean} eq $::form->{"partnumber_$i"}) {
+ push @{ $ref->{matches} ||= [] }, $::locale->text('EAN') . ': ' . $ref->{ean};
+ }
+
# In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
# es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
# Buchungskonto also aus dem Ergebnis rausgenommen werden.