X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIS.pm;h=62ba10e18a30e6b490414392305210d8a4fde1fa;hb=a3335295fe8ac1acdb4b55324812c70af1e43b47;hp=a1542850f8ecc1bc42eb4dd3aee56c46af1d6e9c;hpb=97ac8565db6bac7fcacbc41d62effaa5869043d5;p=kivitendo-erp.git diff --git a/SL/IS.pm b/SL/IS.pm index a1542850f..62ba10e18 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -36,6 +36,7 @@ package IS; use List::Util qw(max); +use Carp; use SL::AM; use SL::ARAP; use SL::CVar; @@ -60,6 +61,8 @@ use strict; sub invoice_details { $main::lxdebug->enter_sub(); + # prepare invoice for printing + my ($self, $myconfig, $form, $locale) = @_; $form->{duedate} ||= $form->{invdate}; @@ -68,9 +71,6 @@ sub invoice_details { my $dbh = $form->get_standard_dbh; my $sth; - my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|; - ($form->{terms}) = selectrow_query($form, $dbh, $query); - my (@project_ids); $form->{TEMPLATE_ARRAYS} = {}; @@ -144,18 +144,21 @@ sub invoice_details { $form->{discount} = []; - IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form); + # get some values of parts from db on store them in extra array, + # so that they can be sorted in later + my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form); + my @prepared_arrays = keys %prepared_template_arrays; my $ic_cvar_configs = CVar->get_configs(module => 'IC'); my $project_cvar_configs = CVar->get_configs(module => 'Projects'); my @arrays = - qw(runningnumber number description longdescription qty ship unit bin - 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 - price_factor price_factor_name partsgroup weight lineweight); + qw(runningnumber number description longdescription qty qty_nofmt unit bin + deliverydate_oe ordnumber_oe donumber_do transdate_oe invnumber invdate + partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt + discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt + linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription + price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt); push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs }; push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs }; @@ -164,23 +167,92 @@ sub invoice_details { my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo); - map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays); + map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays, @prepared_arrays); my $totalweight = 0; foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) { $i = $item->[0]; if ($item->[1] ne $sameitem) { + push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'partsgroup'); push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|); $sameitem = $item->[1]; - map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays)); + map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays))); } $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}); if ($form->{"id_$i"} != 0) { + # Prepare linked items for printing + if ( $form->{"invoice_id_$i"} ) { + + require SL::DB::InvoiceItem; + my $invoice_item = SL::DB::Manager::InvoiceItem->find_by( id => $form->{"invoice_id_$i"} ); + my $linkeditems = $invoice_item->linked_records( direction => 'from', recursive => 1 ); + + # check for (recursively) linked sales quotation items, sales order + # items and sales delivery order items. + + # The checks for $form->{"ordnumber_$i"} and quo and do are for the old + # behaviour, where this data was stored in its own database fields in + # the invoice items, and there were no record links for the items. + + # If this information were to be fetched in retrieve_invoice, e.g. for showing + # this information in the second row, then these fields will already have + # been set and won't be calculated again. This shouldn't be done there + # though, as each invocation creates several database calls per item, and would + # make the interface very slow for many items. So currently these + # requests are only made when printing the record. + + # When using the workflow an invoice item can only be (recursively) linked to at + # most one sales quotation item and at most one delivery order item. But it may + # be linked back to several order items, if collective orders were involved. If + # that is the case we will always choose the very first order item from the + # original order, i.e. where it first appeared in an order. + + # TODO: credit note items aren't checked for a record link to their + # invoice item + + unless ( $form->{"ordnumber_$i"} ) { + + # $form->{"ordnumber_$i"} comes from ordnumber in invoice, if an + # entry exists this must be from before the change from ordnumber to linked items. + # So we just use that value and don't check for linked items. + # In that case there won't be any links for quo or do items either + + # sales order items are fetched and sorted by id, the lowest id is first + # It is assumed that the id always grows, so the item we want (the original) will have the lowest id + # better solution: filter the order_item that doesn't have any links from other order_items + # or maybe fetch linked_records with param save_path and order by _record_length_depth + my @linked_orderitems = grep { $_->isa("SL::DB::OrderItem") && $_->record->type eq 'sales_order' } @{$linkeditems}; + if ( scalar @linked_orderitems ) { + @linked_orderitems = sort { $a->id <=> $b->id } @linked_orderitems; + my $orderitem = $linked_orderitems[0]; # 0: the original order item, -1: the last collective order item + + $form->{"ordnumber_$i"} = $orderitem->record->record_number; + $form->{"transdate_oe_$i"} = $orderitem->record->transdate->to_kivitendo; + $form->{"cusordnumber_oe_$i"} = $orderitem->record->cusordnumber; + }; + + my @linked_quoitems = grep { $_->isa("SL::DB::OrderItem") && $_->record->type eq 'sales_quotation' } @{$linkeditems}; + if ( scalar @linked_quoitems ) { + croak "an invoice item may only be linked back to 1 sales quotation item, something is wrong\n" unless scalar @linked_quoitems == 1; + $form->{"quonumber_$i"} = $linked_quoitems[0]->record->record_number; + $form->{"transdate_quo_$i"} = $linked_quoitems[0]->record->transdate->to_kivitendo; + }; + + my @linked_deliveryorderitems = grep { $_->isa("SL::DB::DeliveryOrderItem") && $_->record->type eq 'sales_delivery_order' } @{$linkeditems}; + if ( scalar @linked_deliveryorderitems ) { + croak "an invoice item may only be linked back to 1 sales delivery item, something is wrong\n" unless scalar @linked_deliveryorderitems == 1; + $form->{"donumber_$i"} = $linked_deliveryorderitems[0]->record->record_number; + $form->{"transdate_do_$i"} = $linked_deliveryorderitems[0]->record->transdate->to_kivitendo; + }; + }; + }; + + # add number, description and qty to $form->{number}, if ($form->{"subtotal_$i"} && !$subtotal_header) { $subtotal_header = $i; @@ -198,6 +270,9 @@ sub invoice_details { my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 }; + push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, $prepared_template_arrays{$_}[$i - 1]) for @prepared_arrays; + + push @{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'normal'; push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position; push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"}; push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"}; @@ -211,16 +286,23 @@ sub invoice_details { push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"}; push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"}; push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"}); + # linked item print variables + push @{ $form->{TEMPLATE_ARRAYS}->{quonumber_quo} }, $form->{"quonumber_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{transdate_quo} }, $form->{"transdate_quo_$i"}; push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_oe_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{cusordnumber_oe} }, $form->{"cusordnumber_oe_$i"}; push @{ $form->{TEMPLATE_ARRAYS}->{donumber_do} }, $form->{"donumber_$i"}; - push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{transdate_do} }, $form->{"transdate_do_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"}; push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"}; push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor}; push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description}; push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"}; push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"}; - push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"}); + push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2); + push(@{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"}); my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"}); my ($dec) = ($sellprice =~ /\.(\d+)/); @@ -346,7 +428,7 @@ sub invoice_details { $sortorder = qq|ORDER BY a.oid|; } - $query = + my $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup FROM assembly a JOIN parts p ON (a.parts_id = p.id) @@ -356,18 +438,20 @@ sub invoice_details { while (my $ref = $sth->fetchrow_hashref('NAME_lc')) { if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) { - map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays)); + map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays))); $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--"; + push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item-partsgroup'); push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem); } map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description); + push(@{ $form->{TEMPLATE_ARRAYS}->{entry_type} }, 'assembly-item'); push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"} ) . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|); - map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays)); + map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } (@arrays, @prepared_arrays))); } $sth->finish; @@ -782,7 +866,7 @@ sub post_invoice { UPDATE invoice SET trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, sellprice = ?, fxsellprice = ?, discount = ?, allocated = ?, assemblyitem = ?, unit = ?, deliverydate = ?, project_id = ?, serialnumber = ?, pricegroup_id = ?, - ordnumber = ?, donumber = ?, transdate = ?, cusordnumber = ?, base_qty = ?, subtotal = ?, + base_qty = ?, subtotal = ?, marge_percent = ?, marge_total = ?, lastcost = ?, active_price_source = ?, active_discount_source = ?, price_factor_id = ?, price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ? WHERE id = ? @@ -794,8 +878,7 @@ SQL $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"}, $form->{"donumber_$i"}, conv_date($form->{"transdate_$i"}), - $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f', + $baseqty, $form->{"subtotal_$i"} ? 't' : 'f', $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"}, $form->{"lastcost_$i"}, $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"}, @@ -1160,7 +1243,7 @@ SQL transdate = ?, orddate = ?, quodate = ?, customer_id = ?, amount = ?, netamount = ?, paid = ?, duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?, - shipvia = ?, terms = ?, notes = ?, intnotes = ?, + shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name = ?), department_id = ?, payment_id = ?, taxincluded = ?, type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?, @@ -1175,7 +1258,7 @@ SQL conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}), $amount, $netamount, $form->{"paid"}, conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"}, - $form->{"shipvia"}, conv_i($form->{"terms"}), $restricter->process($form->{"notes"}), $form->{"intnotes"}, + $form->{"shipvia"}, $restricter->process($form->{"notes"}), $form->{"intnotes"}, $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f', $form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}), conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f', @@ -1274,8 +1357,6 @@ SQL exporttype => DATEV_ET_BUCHUNGEN, format => DATEV_FORMAT_KNE, dbh => $dbh, - from => $transdate, - to => $transdate, trans_id => $form->{id}, ); @@ -1316,7 +1397,6 @@ sub transfer_out { $form->{"id_$i"}, $form->{"qty_$i"}, $form->{"unit_$i"}); - if (!@{ $err } && $wh_id && $bin_id) { push @transfers, { 'parts_id' => $form->{"id_$i"}, @@ -1353,7 +1433,7 @@ sub _determine_wh_and_bin { # ignore service if they are not configured to be transfered if ($part->is_service && !$conf->get_transfer_default_services) { $::lxdebug->leave_sub(2); - return; + return (\@errors); } # test negative qty @@ -1377,14 +1457,14 @@ sub _determine_wh_and_bin { parts_id => $part->id, bin_id => $bin_id); if ($error == 1) { - push @errors, $::locale->text("Part \"#1\" has chargenumber or best before date set. So it cannot be transfered automaticaly.", + push @errors, $::locale->text('Part "#1" has chargenumber or best before date set. So it cannot be transfered automaticaly.', $part->description); } my $form_unit_obj = SL::DB::Unit->new(name => $unit)->load; my $part_unit_qty = $form_unit_obj->convert_to($qty, $part->unit_obj); my $diff_qty = $max_qty - $part_unit_qty; if (!@errors && $diff_qty < 0) { - push @errors, $::locale->text("For part \"#1\" there are missing #2 #3 in the default warehouse/bin \"#4/#5\"", + push @errors, $::locale->text('For part "#1" there are missing #2 #3 in the default warehouse/bin "#4/#5".', $part->description, $::form->format_amount(\%::myconfig, -1*$diff_qty), $part->unit_obj->name, @@ -1392,7 +1472,7 @@ sub _determine_wh_and_bin { SL::DB::Bin->new( id => $bin_id)->load->description); } } else { - push @errors, $::locale->text("For part \"#1\" there is no default warehouse and bin defined.", + push @errors, $::locale->text('For part "#1" there is no default warehouse and bin defined.', $part->description); } @@ -1403,7 +1483,7 @@ sub _determine_wh_and_bin { if ($wh_id && $bin_id) { @errors = (); } else { - push @errors, $::locale->text("For part \"#1\" there is no default warehouse and bin for ignoring onhand defined.", + push @errors, $::locale->text('For part "#1" there is no default warehouse and bin for ignoring onhand defined.', $part->description); } } @@ -1809,18 +1889,23 @@ sub retrieve_invoice { a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber, a.orddate, a.quodate, a.globalproject_id, a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate, - a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id, + a.shippingpoint, a.shipvia, a.notes, a.intnotes, a.taxzone_id, a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id, a.employee_id, a.salesman_id, a.payment_id, + a.mtime, a.itime, a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type, a.transaction_description, a.donumber, a.invnumber_for_credit_note, a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id, + dc.dunning_description, e.name AS employee FROM ar a LEFT JOIN employee e ON (e.id = a.employee_id) + LEFT JOIN dunning_config dc ON (a.dunning_config_id = dc.id) WHERE a.id = ?|; $ref = selectfirst_hashref_query($form, $dbh, $query, $id); map { $form->{$_} = $ref->{$_} } keys %{ $ref }; + $form->{mtime} = $form->{itime} if !$form->{mtime}; + $form->{lastmtime} = $form->{mtime}; $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy"); @@ -1956,45 +2041,39 @@ sub get_customer { my $dateformat = $myconfig->{dateformat}; $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/; - my (@values, $duedate, $ref, $query); - - if ($form->{invdate}) { - $duedate = "to_date(?, '$dateformat')"; - push @values, $form->{invdate}; - } else { - $duedate = "current_date"; - } + my (@values, $ref, $query); my $cid = conv_i($form->{customer_id}); my $payment_id; - if ($form->{payment_id}) { - $payment_id = "(pt.id = ?) OR"; - push @values, conv_i($form->{payment_id}); - } - # get customer $query = qq|SELECT - c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms, + c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id, c.street, c.zipcode, c.city, c.country, c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr, c.taxincluded_checked, c.direct_debit, - $duedate + COALESCE(pt.terms_netto, 0) AS duedate, b.discount AS tradediscount, b.description AS business FROM customer c LEFT JOIN business b ON (b.id = c.business_id) - LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id)) LEFT JOIN currencies cu ON (c.currency_id=cu.id) WHERE c.id = ?|; push @values, $cid; $ref = selectfirst_hashref_query($form, $dbh, $query, @values); delete $ref->{salesman_id} if !$ref->{salesman_id}; + delete $ref->{payment_id} if $form->{payment_id}; map { $form->{$_} = $ref->{$_} } keys %$ref; + if ($form->{payment_id}) { + my $reference_date = $form->{invdate} ? DateTime->from_kivitendo($form->{invdate}) : undef; + $form->{duedate} = SL::DB::PaymentTerm->new(id => $form->{payment_id})->load->calc_date(reference_date => $reference_date)->to_kivitendo; + } else { + $form->{duedate} = DateTime->today_local->to_kivitendo; + } + # use customer currency $form->{currency} = $form->{curr};