X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIS.pm;h=1deeb2a47a4217b5d6e923114adb92aacf7cba9c;hb=56f7991d4910ae12c7d16aab025a2ab242ca5eeb;hp=3210b1292c6276ee90e87c3fba2618e63d08b9d3;hpb=cb25314000db992f8b413d10a0d20f0e80b4b677;p=kivitendo-erp.git diff --git a/SL/IS.pm b/SL/IS.pm index 3210b1292..1deeb2a47 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -40,6 +40,7 @@ use SL::AM; use SL::ARAP; use SL::CVar; use SL::Common; +use SL::DATEV qw(:CONSTANTS); use SL::DBUtils; use SL::DO; use SL::GenericTranslations; @@ -47,6 +48,7 @@ use SL::MoreCommon; use SL::IC; use SL::IO; use SL::TransNumber; +use SL::DB::Default; use Data::Dumper; use strict; @@ -148,7 +150,7 @@ sub invoice_details { my @arrays = qw(runningnumber number description longdescription qty ship unit bin - deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil + deliverydate_oe ordnumber_oe transdate_oe validuntil partnotes serialnumber reqdate sellprice listprice netprice discount p_discount discount_sub nodiscount_sub linetotal nodiscount_linetotal tax_rate projectnumber projectdescription @@ -214,33 +216,24 @@ sub invoice_details { 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"}; - - if ($form->{lizenzen}) { - if ($form->{"licensenumber_$i"}) { - $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|; - my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"})); - push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber); - push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0)); - - } else { - push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, ""); - push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, ""); - } - } - - # listprice - push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"}); + push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"}); my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"}); my ($dec) = ($sellprice =~ /\.(\d+)/); my $decimalplaces = max 2, length($dec); - my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"}); - my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor}; - my $linetotal = $form->round_amount($linetotal_exact, 2); - my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact), - $decimalplaces); - my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2); + my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"}); + + my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor}; + my $linetotal = $form->round_amount($linetotal_exact, 2); + + my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor}; + my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2); + + my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded + + 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); push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : ''; @@ -272,8 +265,7 @@ sub invoice_details { $subtotal_header = 0; } else { - push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, ""; - push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, ""; + push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt); } if (!$form->{"discount_$i"}) { @@ -368,7 +360,9 @@ sub invoice_details { $sth->finish; } - map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs }; + push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, + CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_) + for @{ $ic_cvar_configs }; } } @@ -463,7 +457,8 @@ sub customer_details { # get rest for the customer my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes, - ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail + ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail, + ct.curr AS currency FROM customer ct LEFT JOIN contacts cp on ct.id = cp.cp_cv_id WHERE (ct.id = ?) $where @@ -483,6 +478,9 @@ sub customer_details { map { $form->{$_} = $ref->{$_} } keys %$ref; + # remove any trailing whitespace + $form->{currency} =~ s/\s*$// if ($form->{currency}); + if ($form->{delivery_customer_id}) { $query = qq|SELECT *, notes as customernotes @@ -702,16 +700,20 @@ sub post_invoice { if ($form->{"assembly_$i"}) { # record assembly item as allocated - &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty); + &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty); } else { - $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i); + $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i); } } - # get pricegroup_id and save it + # Get pricegroup_id and save it. Unfortunately the interface + # also uses ID "0" for signalling that none is selected, but "0" + # must not be stored in the database. Therefore we cannot simply + # use conv_i(). ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"}); $pricegroup_id *= 1; + $pricegroup_id = undef if !$pricegroup_id; my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|); @@ -731,7 +733,7 @@ sub post_invoice { $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"}, conv_i($pricegroup_id), + $form->{"serialnumber_$i"}, $pricegroup_id, $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}), $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f', $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"}, @@ -740,14 +742,6 @@ sub post_invoice { conv_i($form->{"marge_price_factor_$i"})); do_query($form, $dbh, $query, @values); - if ($form->{lizenzen} && $form->{"licensenumber_$i"}) { - $query = - qq|INSERT INTO licenseinvoice (trans_id, license_id) - VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|; - @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"})); - do_query($form, $dbh, $query, @values); - } - CVar->save_custom_variables(module => 'IC', sub_module => 'invoice', trans_id => $invoice_id, @@ -828,6 +822,8 @@ sub post_invoice { $project_id = conv_i($form->{"globalproject_id"}); + my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate}; + foreach my $trans_id (keys %{ $form->{amount_cogs} }) { foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) { next unless ($form->{expense_inventory} =~ /\Q$accno\E/); @@ -836,9 +832,9 @@ sub post_invoice { if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|; - @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id)); + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|; + @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno); do_query($form, $dbh, $query, @values); $form->{amount_cogs}{$trans_id}{$accno} = 0; } @@ -849,9 +845,9 @@ sub post_invoice { if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|; - @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id)); + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|; + @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno); do_query($form, $dbh, $query, @values); } } @@ -865,10 +861,25 @@ sub post_invoice { if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; - @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id)); + (SELECT tax_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT taxkey_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + ?, + (SELECT link FROM chart WHERE accno = ?))|; + @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno); do_query($form, $dbh, $query, @values); $form->{amount}{$trans_id}{$accno} = 0; } @@ -879,10 +890,25 @@ sub post_invoice { if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; - @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id)); + (SELECT tax_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT taxkey_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + ?, + (SELECT link FROM chart WHERE accno = ?))|; + @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno); do_query($form, $dbh, $query, @values); } } @@ -901,6 +927,12 @@ sub post_invoice { if (!$form->{storno}) { for my $i (1 .. $form->{paidaccounts}) { + if ($form->{"acc_trans_id_$i"} + && $payments_only + && (SL::DB::Default->get->payments_changeable == 0)) { + next; + } + next if ($form->{"paid_$i"} == 0); my ($accno) = split(/--/, $form->{"AR_paid_$i"}); @@ -922,38 +954,67 @@ sub post_invoice { if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; - @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id); + (SELECT tax_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT taxkey_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + ?, + (SELECT link FROM chart WHERE accno = ?))|; + @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, conv_date($taxdate), $project_id, $form->{AR}); do_query($form, $dbh, $query, @values); } # record payment $form->{"paid_$i"} *= -1; + my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig)); $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, + (SELECT tax_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT taxkey_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + ?, + (SELECT link FROM chart WHERE accno = ?))|; @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, - $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id); + $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno); do_query($form, $dbh, $query, @values); # exchangerate difference $form->{fx}{$accno}{ $form->{"datepaid_$i"} } += - $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff; + $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff; # gain/loss $amount = - $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} * - $form->{"exchangerate_$i"}; + $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} * + $form->{"exchangerate_$i"}; if ($amount > 0) { - $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += - $amount; + $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount; } else { - $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += - $amount; + $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount; } $diff = 0; @@ -972,35 +1033,47 @@ sub post_invoice { IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh); - if ($payments_only) { - $query = qq|UPDATE ar SET paid = ? WHERE id = ?|; - do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id})); - - $dbh->commit if !$provided_dbh; - - $main::lxdebug->leave_sub(); - return; - } - # 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 - ) { + $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2); + if ( $form->{fx}{$accno}{$transdate} != 0 ) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id) + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', - (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; - @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id); + (SELECT tax_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + (SELECT taxkey_id + FROM taxkeys + WHERE chart_id= (SELECT id + FROM chart + WHERE accno = ?) + AND startdate <= ? + ORDER BY startdate DESC LIMIT 1), + ?, + (SELECT link FROM chart WHERE accno = ?))|; + @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno); do_query($form, $dbh, $query, @values); } } } + if ($payments_only) { + $query = qq|UPDATE ar SET paid = ? WHERE id = ?|; + do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id})); + + $dbh->commit if !$provided_dbh; + + $main::lxdebug->leave_sub(); + return; + } + $amount = $netamount + $tax; # save AR record @@ -1018,7 +1091,7 @@ sub post_invoice { cp_id = ?, marge_total = ?, marge_percent = ?, globalproject_id = ?, delivery_customer_id = ?, transaction_description = ?, delivery_vendor_id = ?, - donumber = ?, invnumber_for_credit_note = ? + donumber = ?, invnumber_for_credit_note = ?, direct_debit = ? WHERE id = ?|; @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"}, conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}), @@ -1031,7 +1104,7 @@ sub post_invoice { conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent}, conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}), $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}), - $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, + $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f', conv_i($form->{"id"})); do_query($form, $dbh, $query, @values); @@ -1090,6 +1163,27 @@ sub post_invoice { 'arap_id' => $form->{id}, 'table' => 'ar',); + # safety check datev export + if ($::instance_conf->get_datev_check_on_sales_invoice) { + my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef; + $transdate ||= DateTime->today; + + my $datev = SL::DATEV->new( + exporttype => DATEV_ET_BUCHUNGEN, + format => DATEV_FORMAT_KNE, + dbh => $dbh, + from => $transdate, + to => $transdate, + ); + + $datev->export; + + if ($datev->errors) { + $dbh->rollback; + die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors; + } + } + my $rc = 1; $dbh->commit if !$provided_dbh; @@ -1151,10 +1245,12 @@ sub post_payment { $old_form = save_form(); # Delete all entries in acc_trans from prior payments. - $self->_delete_payments($form, $dbh); + if (SL::DB::Default->get->payments_changeable != 0) { + $self->_delete_payments($form, $dbh); + } # Save the new payments the user made before cleaning up $form. - map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form }; + map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^gldate_\d+$|^acc_trans_id_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form }; # Clean up $form so that old content won't tamper the results. %keep_vars = map { $_, 1 } qw(login password id); @@ -1208,7 +1304,7 @@ sub post_payment { sub process_assembly { $main::lxdebug->enter_sub(); - my ($dbh, $form, $id, $totalqty) = @_; + my ($dbh, $myconfig, $form, $id, $totalqty) = @_; my $query = qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit, @@ -1229,11 +1325,11 @@ sub process_assembly { $ref->{qty} *= $totalqty; if ($ref->{assembly}) { - &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty}); + &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty}); next; } else { if ($ref->{inventory_accno_id}) { - $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty}); + $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty}); } } @@ -1254,7 +1350,10 @@ sub process_assembly { sub cogs { $main::lxdebug->enter_sub(); - my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_; + # adjust allocated in table invoice according to FIFO princicple + # for a certain part with part_id $id + + my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_; $basefactor ||= 1; @@ -1279,18 +1378,45 @@ sub cogs { my $allocated = 0; my $qty; +# all invoice entries of an example part: + +# id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno +# ---+----------+----------+-----------+-----------+-----------------+--------------+--------------- +# 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20 +# 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50 +# 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50 +# 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20 +# 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50 + +# AND ((i.base_qty + i.allocated) < 0) filters out all but line with id=7, elsewhere i.base_qty + i.allocated has already reached 0 +# and all parts have been allocated + +# so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0 +# 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20 + +# in this example there are still 4 unsold articles + + + # search all invoice entries for the part in question, adjusting "allocated" + # until the total number of sold parts has been reached + + # ORDER BY trans_id ensures FIFO + + while (my $ref = $sth->fetchrow_hashref('NAME_lc')) { if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) { $qty = $totalqty; } + # update allocated in invoice $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty); # total expenses and inventory # sellprice is the cost of the item my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2); - if (!$::lx_office_conf{system}->{eur}) { + if ( $::instance_conf->get_inventory_system eq 'perpetual' ) { + # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno}; # add to expense $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal; @@ -1359,14 +1485,6 @@ sub reverse_invoice { my @values = (conv_i($form->{id})); do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values); do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values); - - if ($form->{lizenzen}) { - $query = - qq|DELETE FROM licenseinvoice - WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|; - do_query($form, $dbh, $query, @values); - } - do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values); $main::lxdebug->leave_sub(); @@ -1399,14 +1517,16 @@ sub delete_invoice { do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id); } - # delete AR record - do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values); - # delete spool files my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values); - # delete status entries - do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values); + my @queries = ( + qq|DELETE FROM status WHERE trans_id = ?|, + qq|DELETE FROM periodic_invoices WHERE ar_id = ?|, + qq|DELETE FROM ar WHERE id = ?|, + ); + + map { do_query($form, $dbh, $_, @values) } @queries; my $rc = $dbh->commit; @@ -1430,7 +1550,7 @@ sub retrieve_invoice { my ($sth, $ref, $query); - my $query_transdate = ", current_date AS invdate" if !$form->{id}; + my $query_transdate = !$form->{id} ? ", current_date AS invdate" : ''; $query = qq|SELECT @@ -1462,7 +1582,7 @@ sub retrieve_invoice { a.employee_id, a.salesman_id, a.payment_id, 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.marge_total, a.marge_percent, a.direct_debit, e.name AS employee FROM ar a LEFT JOIN employee e ON (e.id = a.employee_id) @@ -1470,6 +1590,8 @@ sub retrieve_invoice { $ref = selectfirst_hashref_query($form, $dbh, $query, $id); map { $form->{$_} = $ref->{$_} } keys %{ $ref }; + # remove any trailing whitespace + $form->{currency} =~ s/\s*$//; $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy"); @@ -1515,7 +1637,7 @@ sub retrieve_invoice { 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.price_factor_id, i.price_factor, i.marge_price_factor, - p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, + p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice, pr.projectnumber, pg.partsgroup, prg.pricegroup FROM invoice i @@ -1583,12 +1705,6 @@ sub retrieve_invoice { } - if ($form->{lizenzen}) { - $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|; - my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos})); - $ref->{lizenzen} = ""; - } - $ref->{qty} *= -1 if $form->{type} eq "credit_note"; chop $ref->{taxaccounts}; @@ -1641,7 +1757,8 @@ sub get_customer { c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms, c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.street, c.zipcode, c.city, c.country, - c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, + c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.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 @@ -1655,6 +1772,12 @@ sub get_customer { map { $form->{$_} = $ref->{$_} } keys %$ref; + # remove any trailing whitespace + $form->{curr} =~ s/\s*$//; + + # use customer currency if not empty + $form->{currency} = $form->{curr} if $form->{curr}; + $query = qq|SELECT sum(amount - paid) AS dunning_amount FROM ar @@ -1778,9 +1901,10 @@ sub retrieve_item { push @values, $form->{"partnumber_$i"}; } + # Search for part ID overrides all other criteria. if ($form->{"id_${i}"}) { - $where .= qq| AND p.id = ?|; - push @values, $form->{"id_${i}"}; + $where = qq|p.id = ?|; + @values = ($form->{"id_${i}"}); } if ($form->{"description_$i"}) { @@ -1848,6 +1972,19 @@ sub retrieve_item { 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; + while (my $ref = $sth->fetchrow_hashref('NAME_lc')) { # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn @@ -1897,7 +2034,6 @@ sub retrieve_item { my $i = 0; while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) { - # if ($customertax{$ref->{accno}}) if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) { $i++; $ptr->{accno} = $i; @@ -1915,55 +2051,24 @@ sub retrieve_item { $stw->finish; chop $ref->{taxaccounts}; + if ($form->{language_id}) { - $query = - qq|SELECT tr.translation, tr.longdescription - FROM translation tr - WHERE tr.language_id = ? AND tr.parts_id = ?|; - @values = (conv_i($form->{language_id}), conv_i($ref->{id})); - my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values); - if ($translation ne "") { + 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; - - } else { - $query = - 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|; - @values = (conv_i($form->{language_id}), conv_i($ref->{id})); - my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values); - if ($translation ne "") { - $ref->{description} = $translation; - $ref->{longdescription} = $longdescription; - } + last; } } $ref->{onhand} *= 1; push @{ $form->{item_list} }, $ref; - - if ($form->{lizenzen}) { - if ($ref->{inventory_accno} > 0) { - $query = - qq|SELECT l.* - FROM license l - WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|; - my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id})); - while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) { - push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr; - } - $stw->finish; - } - } } $sth->finish; + $_->[1]->finish for @translation_queries; foreach my $item (@{ $form->{item_list} }) { my $custom_variables = CVar->get_custom_variables(module => 'IC', @@ -2009,7 +2114,7 @@ sub get_pricegroups_for_parts { my $pricegroup_old = $form->{"pricegroup_old_$i"}; - # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically + # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically my $sellprice = $form->{"sellprice_$i"}; my $pricegroup_id = $form->{"pricegroup_id_$i"}; $form->{"new_pricegroup_$i"} = $selectedpricegroup_id; @@ -2087,9 +2192,10 @@ sub get_pricegroups_for_parts { $pkr->{price} *= $form->{"basefactor_$i"}; $pkr->{price} *= $basefactor; + $pkr->{price_ufmt} = $pkr->{price}; $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5); - if ($selectedpricegroup_id eq undef) { + if (!defined $selectedpricegroup_id) { # new entries in article list, either old invoice was loaded (edit) or a new article was added # Case A: open old invoice, no pricegroup selected # Case B: add new article to invoice, no pricegroup selected @@ -2101,18 +2207,16 @@ sub get_pricegroups_for_parts { if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) { # Case A $pkr->{selected} = ' selected'; - } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass} and not defined $form->{"pricegroup_id_$i"} - and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice - # for the case where pricegroup prices haven't been set + and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice + # for the case where pricegroup prices haven't been set ) { # Case B: use default pricegroup of customer $pkr->{selected} = ' selected'; # unless $form->{selected}; - # no customer pricesgroup set - if ($pkr->{price} == $pkr->{default_sellprice}) { + if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) { $pkr->{price} = $form->{"sellprice_$i"}; @@ -2123,7 +2227,7 @@ sub get_pricegroups_for_parts { $form->{"sellprice_$i"} = $pkr->{price}; } - } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) { + } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) { $pkr->{price} = $form->{"sellprice_$i"}; $pkr->{selected} = ' selected'; }