X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIS.pm;h=23b6f80f74d881d30b935ea78b4f010bcaaa0633;hb=2766521474b20c77e62d169a1c99efb41cb1c56f;hp=35a902e8b78d271ccde1cbce97305afd2a694fa3;hpb=6fe34cd5cc552db83cbbeaf2ab68570b8d2b4a4d;p=kivitendo-erp.git diff --git a/SL/IS.pm b/SL/IS.pm index 35a902e8b..23b6f80f7 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,8 @@ use SL::MoreCommon; use SL::IC; use SL::IO; use SL::TransNumber; +use SL::DB::Default; +use SL::DB::Tax; use Data::Dumper; use strict; @@ -112,8 +115,6 @@ sub invoice_details { my $i; my @partsgroup = (); my $partsgroup; - my %oid = ('Pg' => 'oid', - 'Oracle' => 'rowid'); # sort items by partsgroup for $i (1 .. $form->{rowcount}) { @@ -142,7 +143,7 @@ sub invoice_details { $form->{discount} = []; - IC->prepare_parts_for_printing(); + IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form); my $ic_cvar_configs = CVar->get_configs(module => 'IC'); @@ -152,7 +153,7 @@ sub invoice_details { 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); + price_factor price_factor_name partsgroup weight lineweight); push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs }; @@ -162,6 +163,7 @@ sub invoice_details { map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays); + my $totalweight = 0; foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) { $i = $item->[0]; @@ -220,12 +222,18 @@ sub invoice_details { 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) : ''; @@ -257,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"}) { @@ -273,6 +280,13 @@ sub invoice_details { push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}}); push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}}); + my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"}; + $totalweight += $lineweight; + push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3); + push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3); + push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight; + @taxaccounts = split(/ /, $form->{"taxaccounts_$i"}); $taxrate = 0; $taxdiff = 0; @@ -321,9 +335,9 @@ sub invoice_details { my $sortorder = ""; if ($form->{groupitems}) { $sortorder = - qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|; + qq|ORDER BY pg.partsgroup, a.oid|; } else { - $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|; + $sortorder = qq|ORDER BY a.oid|; } $query = @@ -353,10 +367,17 @@ 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 }; } } + $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3); + $form->{totalweight_nofmt} = $totalweight; + my $defaults = AM->get_defaults(); + $form->{weightunit} = $defaults->{weightunit}; + foreach my $item (sort keys %taxaccounts) { $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2); @@ -366,8 +387,11 @@ sub invoice_details { push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount ); push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100)); push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100); - push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%}); 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; + push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%}); } for my $i (1 .. $form->{paidaccounts}) { @@ -405,6 +429,9 @@ sub invoice_details { $form->set_payment_options($myconfig, $form->{invdate}); + $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef); + $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id}; + $form->{username} = $myconfig->{name}; $main::lxdebug->leave_sub(); @@ -448,9 +475,11 @@ 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, + cu.name AS currency FROM customer ct LEFT JOIN contacts cp on ct.id = cp.cp_cv_id + LEFT JOIN currencies cu ON (ct.currency_id = cu.id) WHERE (ct.id = ?) $where ORDER BY cp.cp_id LIMIT 1|; @@ -523,6 +552,8 @@ sub post_invoice { } $form->{defaultcurrency} = $form->get_default_currency($myconfig); + my $defaultcurrency = $form->{defaultcurrency}; + # 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 @@ -543,8 +574,8 @@ sub post_invoice { $query = qq|SELECT nextval('glid')|; ($form->{"id"}) = selectrow_query($form, $dbh, $query); - $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|; - do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}); + $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}); if (!$form->{invnumber}) { $form->{invnumber} = @@ -557,9 +588,6 @@ sub post_invoice { my ($netamount, $invoicediff) = (0, 0); my ($amount, $linetotal, $lastincomeaccno); - 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 { @@ -694,9 +722,13 @@ sub post_invoice { } } - # 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')|); @@ -716,7 +748,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"}, @@ -804,6 +836,8 @@ sub post_invoice { } $project_id = conv_i($form->{"globalproject_id"}); + # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen + 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} }) { @@ -813,9 +847,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; } @@ -826,9 +860,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); } } @@ -842,10 +876,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; } @@ -856,10 +905,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); } } @@ -878,6 +942,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"}); @@ -899,38 +969,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; @@ -949,35 +1048,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 @@ -989,13 +1100,15 @@ sub post_invoice { amount = ?, netamount = ?, paid = ?, duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?, shipvia = ?, terms = ?, notes = ?, intnotes = ?, - curr = ?, department_id = ?, payment_id = ?, taxincluded = ?, + currency_id = (SELECT id FROM currencies WHERE name = ?), + department_id = ?, payment_id = ?, taxincluded = ?, type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?, employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?, 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 = ?, + delivery_term_id = ? 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"}), @@ -1008,7 +1121,8 @@ 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->{delivery_term_id}), conv_i($form->{"id"})); do_query($form, $dbh, $query, @values); @@ -1067,6 +1181,28 @@ 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, + trans_id => $form->{id}, + ); + + $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; @@ -1121,17 +1257,18 @@ sub post_payment { # connect to database, turn off autocommit my $dbh = $form->get_standard_dbh; - $dbh->begin_work; my (%payments, $old_form, $row, $item, $query, %keep_vars); $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); @@ -1261,7 +1398,7 @@ sub cogs { # all invoice entries of an example part: -# id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno +# 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 @@ -1378,7 +1515,6 @@ sub delete_invoice { # connect to database my $dbh = $form->get_standard_dbh; - $dbh->begin_work; &reverse_invoice($dbh, $form); @@ -1398,14 +1534,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; @@ -1429,7 +1567,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 @@ -1437,8 +1575,7 @@ sub retrieve_invoice { (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 + (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno ${query_transdate} FROM defaults d|; @@ -1457,11 +1594,11 @@ sub retrieve_invoice { 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.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_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.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, a.delivery_term_id, e.name AS employee FROM ar a LEFT JOIN employee e ON (e.id = a.employee_id) @@ -1469,7 +1606,6 @@ sub retrieve_invoice { $ref = selectfirst_hashref_query($form, $dbh, $query, $id); map { $form->{$_} = $ref->{$_} } keys %{ $ref }; - $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy"); # get shipto @@ -1514,7 +1650,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.listprice, + 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 FROM invoice i @@ -1632,14 +1768,16 @@ sub get_customer { $query = qq|SELECT 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.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, + 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); @@ -1648,6 +1786,9 @@ sub get_customer { map { $form->{$_} = $ref->{$_} } keys %$ref; + # use customer currency + $form->{currency} = $form->{curr}; + $query = qq|SELECT sum(amount - paid) AS dunning_amount FROM ar @@ -1675,7 +1816,7 @@ sub get_customer { $query = qq|SELECT o.amount, (SELECT e.buy FROM exchangerate e - WHERE e.curr = o.curr + WHERE e.currency_id = o.currency_id AND e.transdate = o.transdate) FROM oe o WHERE o.customer_id = ? @@ -1771,9 +1912,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"}) { @@ -1814,10 +1956,10 @@ sub retrieve_item { 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.unit, p.assembly, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable, p.formel, p.payment_id AS part_payment_id, - p.price_factor_id, + p.price_factor_id, p.weight, pfac.factor AS price_factor, @@ -1903,7 +2045,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; @@ -1984,7 +2125,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; @@ -2062,9 +2203,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 @@ -2076,18 +2218,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"}; @@ -2098,7 +2238,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'; }