use SL::DBUtils;
use SL::DO;
use SL::GenericTranslations;
+use SL::HTML::Restrict;
use SL::MoreCommon;
use SL::IC;
use SL::IO;
use SL::TransNumber;
use SL::DB::Default;
use SL::DB::Tax;
+use SL::DB::TaxZone;
+use SL::TransNumber;
use Data::Dumper;
use strict;
my @arrays =
qw(runningnumber number description longdescription qty ship unit bin
- deliverydate_oe ordnumber_oe transdate_oe validuntil
+ deliverydate_oe ordnumber_oe donumber_do transdate_oe validuntil
partnotes serialnumber reqdate sellprice listprice netprice
discount p_discount discount_sub nodiscount_sub
linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{donumber_do} }, $form->{"donumber_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
- $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
+ $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);
push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
- my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj;
+ my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
}
ORDER BY cp.cp_id
LIMIT 1|;
my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
-
+ # we have no values, probably a invalid contact person. hotfix and first idea for issue #10
+ if (!$ref) {
+ my $customer = SL::DB::Manager::Customer->find_by(id => $::form->{customer_id});
+ if ($customer) {
+ $ref->{name} = $customer->name;
+ $ref->{street} = $customer->street;
+ $ref->{zipcode} = $customer->zipcode;
+ $ref->{country} = $customer->country;
+ }
+ my $contact = SL::DB::Manager::Contact->find_by(cp_id => $::form->{cp_id});
+ if ($contact) {
+ $ref->{cp_name} = $contact->cp_name;
+ $ref->{cp_givenname} = $contact->cp_givenname;
+ $ref->{cp_gender} = $contact->cp_gender;
+ }
+ }
# remove id and taxincluded before copy back
delete @$ref{qw(id taxincluded)};
# connect to database, turn off autocommit
my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
+ my $restricter = SL::HTML::Restrict->create;
my ($query, $sth, $null, $project_id, @values);
my $exchangerate = 0;
$query = qq|SELECT nextval('glid')|;
($form->{"id"}) = selectrow_query($form, $dbh, $query);
- $query = qq|INSERT INTO ar (id, invnumber, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|;
- do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});
+ $query = qq|INSERT INTO ar (id, invnumber, currency_id, taxzone_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?), ?)|;
+ do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency}, $form->{taxzone_id});
if (!$form->{invnumber}) {
- $form->{invnumber} =
- $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
- "cnnumber" : "invnumber", $dbh);
+ my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, id => $form->{id});
+ $form->{invnumber} = $trans_number->create_unique;
}
}
}
qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
sellprice, fxsellprice, discount, allocated, assemblyitem,
unit, deliverydate, project_id, serialnumber, pricegroup_id,
- ordnumber, transdate, cusordnumber, base_qty, subtotal,
+ ordnumber, donumber, transdate, cusordnumber, base_qty, subtotal,
marge_percent, marge_total, lastcost,
price_factor_id, price_factor, marge_price_factor)
- VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
+ 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"},
+ $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"},
$form->{"sellprice_$i"}, $fxsellprice,
$form->{"discount_$i"}, $allocated, 'f',
$form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
$form->{"serialnumber_$i"}, $pricegroup_id,
- $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
+ $form->{"ordnumber_$i"}, $form->{"donumber_$i"}, conv_date($form->{"transdate_$i"}),
$form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
$form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
$form->{"lastcost_$i"},
do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
}
- # add shipto
$form->{name} = $form->{customer};
$form->{name} =~ s/--\Q$form->{customer_id}\E//;
+ # add shipto
if (!$form->{shipto_id}) {
$form->add_shipto($dbh, $form->{id}, "AR");
}
c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid
FROM invoice i, 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 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)
WHERE (i.parts_id = p.id)
AND (i.parts_id = ?)
AND ((i.base_qty + i.allocated) < 0)
$form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
- # get shipto
- $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
- $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
- delete $ref->{id};
- map { $form->{$_} = $ref->{$_} } keys %{ $ref };
-
foreach my $vc (qw(customer vendor)) {
next if !$form->{"delivery_${vc}_id"};
($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
my $taxzone_id = $form->{taxzone_id} *= 1;
- $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
+ $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.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
- i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
+ i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.donumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
i.price_factor_id, i.price_factor, i.marge_price_factor,
p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
pr.projectnumber, pg.partsgroup, prg.pricegroup
LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.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)
WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
}
$sth->finish;
- # get shipto if we did not converted an order or invoice
- if (!$form->{shipto}) {
- map { delete $form->{$_} }
- qw(shiptoname shiptodepartment_1 shiptodepartment_2
- 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, $cid);
- delete $ref->{id};
- map { $form->{$_} = $ref->{$_} } keys %$ref;
- }
-
# setup last accounts used for this customer
if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
$query =
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"};
+
+ # 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 ?;
+ |;
+ my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, '%' . $form->{"partnumber_$i"} . '%');
+ 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.
qq|SELECT
p.id, p.partnumber, p.description, p.sellprice,
p.listprice, p.inventory_accno_id, p.lastcost,
+ p.ean,
c1.accno AS inventory_accno,
c1.new_chart_id AS inventory_new_chart,
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.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_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.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_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|;
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.
projects / kivitendo-erp.git / blobdiff