From f087c3736d8c707c18cb3ea87692995940361057 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Jan=20B=C3=BCren?= Date: Thu, 22 Jan 2015 13:01:18 +0100 Subject: [PATCH] =?utf8?q?persistente=20ids=20f=C3=BCr=20invoice=20(items)?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit analog zu do, oe auch die verknüpften items für rechnungen persistent machen. - invoice_id retrieve_invoice in array übernehmen - invoice_pos entfernt (war ggf. vor 2006 ähnlich vorgesehen) - reverse_invoice gekürzt, sodass hier keine invoice gelöscht werden - delete_invoice erweitert, sodass hier invoice gelöscht wird - ferner code von IS.pm nach IR.pm portiert (queries in array) - use_as_new invoice_ids löschen - ferner bei storno invoice_ids löschen und ... - bei Verkaufsrechnung Gutschrift Ferner Kommentare (IR.pm) eingerückt tests: Verkaufsrechnung: gesamten beleg löschen i.O. update i.O. als neu speichern i.O. mittlere position löschen i.O. Storno i.O. Gutschrift i.O. Einkaufsrechnung: als neu speichern i.O. Zahlung buchen i.O. mittlere position löschen i.O. gesamten beleg löschen i.O. Storno i.O. keine Gutschrift möglich --- SL/IR.pm | 99 +++++++++++++++++++++++++++-------------------- SL/IS.pm | 57 ++++++++++++++++++--------- bin/mozilla/io.pl | 3 ++ bin/mozilla/ir.pl | 3 ++ bin/mozilla/is.pl | 6 +++ 5 files changed, 109 insertions(+), 59 deletions(-) diff --git a/SL/IR.pm b/SL/IR.pm index b68df256f..8ce3d0428 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -102,6 +102,7 @@ sub post_invoice { my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} }; my $price_factor; + my @processed_invoice_ids; for my $i (1 .. $form->{rowcount}) { next unless $form->{"id_$i"}; @@ -144,17 +145,8 @@ sub post_invoice { map { $taxrate += $form->{"${_}_rate"} } @taxaccounts; $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1; - ##################################################################### - # das ist aus IS.pm kopiert. schlimm. jb 7.10.2009 - # ich würde mir wünschen, dass diese vier stellen zusammengefasst werden - # ... vier stellen = (einkauf + verkauf) * (maske + backend) - # ansonsten stolpert man immer wieder viermal statt einmal heftig - # und auch das undo discount formatting ist nicht besonders wartungsfreundlich - - # keine ahnung wofür das in IS.pm gemacht wird: - # my ($dec) = ($fxsellprice =~ /\.(\d+)/); - # $dec = length $dec; - # my $decimalplaces = ($dec > 2) ? $dec : 2; + # copied from IS.pm, with some changes (no decimalplaces corrections here etc) + # TODO maybe use PriceTaxCalculation or something like this for backends (IR.pm / IS.pm) # undo discount formatting $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100; @@ -234,17 +226,17 @@ sub post_invoice { ORDER BY transdate|; # ORDER BY transdate guarantees FIFO -# sold two items without having bought them yet, example result of query: -# id | qty | allocated | trans_id | inventory_accno_id | expense_accno_id | transdate -# ---+-----+-----------+----------+--------------------+------------------+------------ -# 9 | 2 | 0 | 9 | 15 | 151 | 2011-01-05 + # sold two items without having bought them yet, example result of query: + # id | qty | allocated | trans_id | inventory_accno_id | expense_accno_id | transdate + # ---+-----+-----------+----------+--------------------+------------------+------------ + # 9 | 2 | 0 | 9 | 15 | 151 | 2011-01-05 -# base_qty + allocated > 0 if article has already been sold but not bought yet + # base_qty + allocated > 0 if article has already been sold but not bought yet -# select qty,allocated,base_qty,sellprice from invoice where trans_id = 9; -# qty | allocated | base_qty | sellprice -# -----+-----------+----------+------------ -# 2 | 0 | 2 | 1000.00000 + # select qty,allocated,base_qty,sellprice from invoice where trans_id = 9; + # qty | allocated | base_qty | sellprice + # -----+-----------+----------+------------ + # 2 | 0 | 2 | 1000.00000 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"})); @@ -259,7 +251,7 @@ sub post_invoice { if ($ref->{allocated} < 0) { -# we have an entry for it already, adjust amount + # we have an entry for it already, adjust amount $form->update_balance($dbh, "acc_trans", "amount", qq| (trans_id = $ref->{trans_id}) AND (chart_id = $ref->{inventory_accno_id}) @@ -292,7 +284,7 @@ sub post_invoice { $ref->{inventory_accno_id}); do_query($form, $dbh, $query, @values); -# add expense + # add expense $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM taxkeys @@ -375,28 +367,41 @@ sub post_invoice { next if $payments_only; - # save detail record in invoice table - my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|); + if (!$form->{"invoice_id_$i"}) { + # there is no persistent id, therefore create one with all necessary constraints + my $q_invoice_id = qq|SELECT nextval('invoiceid')|; + my $h_invoice_id = prepare_query($form, $dbh, $q_invoice_id); + do_statement($form, $h_invoice_id, $q_invoice_id); + $form->{"invoice_id_$i"} = $h_invoice_id->fetchrow_array(); + my $q_create_invoice_id = qq|INSERT INTO invoice (id, trans_id, parts_id) values (?, ?, ?)|; + do_query($form, $dbh, $q_create_invoice_id, conv_i($form->{"invoice_id_$i"}), conv_i($form->{id}), conv_i($form->{"id_$i"})); + $h_invoice_id->finish(); + } - $query = - qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty, base_qty, - sellprice, fxsellprice, discount, allocated, unit, deliverydate, - project_id, serialnumber, price_factor_id, price_factor, marge_price_factor, - active_price_source, active_discount_source) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT factor FROM price_factors WHERE id = ?), ?, ?, ?)|; - @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}), + # save detail record in invoice table + $query = <{id}), conv_i($form->{"id_$i"}), $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"} * -1, $baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated, $form->{"unit_$i"}, conv_date($form->{deliverydate}), conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"}, conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}), $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"}, - ); + conv_i($form->{"invoice_id_$i"})); do_query($form, $dbh, $query, @values); + push @processed_invoice_ids, $form->{"invoice_id_$i"}; CVar->save_custom_variables(module => 'IC', sub_module => 'invoice', - trans_id => $invoice_id, + trans_id => $form->{"invoice_id_$i"}, configs => $ic_cvar_configs, variables => $form, name_prefix => 'ic_', @@ -765,6 +770,16 @@ sub post_invoice { 'arap_id' => $form->{id}, 'table' => 'ap',); + # search for orphaned invoice items + $query = sprintf 'SELECT id FROM invoice WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_invoice_ids; + @values = (conv_i($form->{id}), map { conv_i($_) } @processed_invoice_ids); + my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values); + if (scalar @orphaned_ids) { + # clean up invoice items + $query = sprintf 'DELETE FROM invoice WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids; + do_query($form, $dbh, $query, @orphaned_ids); + } + # safety check datev export if ($::instance_conf->get_datev_check_on_purchase_invoice) { my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef; @@ -866,10 +881,6 @@ sub reverse_invoice { $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|; do_query($form, $dbh, $query, $id); - # delete invoice entries - $query = qq|DELETE FROM invoice WHERE trans_id = ?|; - do_query($form, $dbh, $query, $id); - $query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|; do_query($form, $dbh, $query, $id); @@ -886,13 +897,20 @@ sub delete_invoice { &reverse_invoice($dbh, $form); + my @values = (conv_i($form->{id})); + # delete zero entries + # wtf? use case for this? $query = qq|DELETE FROM acc_trans WHERE amount = 0|; do_query($form, $dbh, $query); - # delete AP record - $query = qq|DELETE FROM ap WHERE id = ?|; - do_query($form, $dbh, $query, conv_i($form->{id})); + + my @queries = ( + qq|DELETE FROM invoice WHERE trans_id = ?|, + qq|DELETE FROM ap WHERE id = ?|, + ); + + map { do_query($form, $dbh, $_, @values) } @queries; my $rc = $dbh->commit; $dbh->disconnect; @@ -1003,7 +1021,6 @@ sub retrieve_invoice { trans_id => $ref->{invoice_id}, ); map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars }; - delete $ref->{invoice_id}; map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"}; delete($ref->{"part_inventory_accno_id"}); diff --git a/SL/IS.pm b/SL/IS.pm index 227b4428b..e09c08c31 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -633,6 +633,8 @@ sub post_invoice { $form->{amount} = {}; $form->{amount_cogs} = {}; + my @processed_invoice_ids; + foreach my $i (1 .. $form->{rowcount}) { if ($form->{type} eq "credit_note") { $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1; @@ -753,21 +755,29 @@ sub post_invoice { $pricegroup_id *= 1; $pricegroup_id = undef if !$pricegroup_id; - my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|); + if (!$form->{"invoice_id_$i"}) { + # there is no persistent id, therefore create one with all necessary constraints + my $q_invoice_id = qq|SELECT nextval('invoiceid')|; + my $h_invoice_id = prepare_query($form, $dbh, $q_invoice_id); + do_statement($form, $h_invoice_id, $q_invoice_id); + $form->{"invoice_id_$i"} = $h_invoice_id->fetchrow_array(); + my $q_create_invoice_id = qq|INSERT INTO invoice (id, trans_id, parts_id) values (?, ?, ?)|; + do_query($form, $dbh, $q_create_invoice_id, conv_i($form->{"invoice_id_$i"}), conv_i($form->{id}), conv_i($form->{"id_$i"})); + $h_invoice_id->finish(); + } # save detail record in invoice table - $query = - 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, donumber, transdate, cusordnumber, base_qty, subtotal, - marge_percent, marge_total, lastcost, active_price_source, active_discount_source, - - price_factor_id, price_factor, marge_price_factor) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, - (SELECT factor FROM price_factors WHERE id = ?), ?)|; - - @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}), + $query = <{id}), conv_i($form->{"id_$i"}), $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"}, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated, 'f', @@ -779,12 +789,14 @@ sub post_invoice { $form->{"lastcost_$i"}, $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"}, conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}), - conv_i($form->{"marge_price_factor_$i"})); + conv_i($form->{"marge_price_factor_$i"}), + conv_i($form->{"invoice_id_$i"})); do_query($form, $dbh, $query, @values); + push @processed_invoice_ids, $form->{"invoice_id_$i"}; CVar->save_custom_variables(module => 'IC', sub_module => 'invoice', - trans_id => $invoice_id, + trans_id => $form->{"invoice_id_$i"}, configs => $ic_cvar_configs, variables => $form, name_prefix => 'ic_', @@ -1217,6 +1229,16 @@ sub post_invoice { 'arap_id' => $form->{id}, 'table' => 'ar',); + # search for orphaned invoice items + $query = sprintf 'SELECT id FROM invoice WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_invoice_ids; + @values = (conv_i($form->{id}), map { conv_i($_) } @processed_invoice_ids); + my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values); + if (scalar @orphaned_ids) { + # clean up invoice items + $query = sprintf 'DELETE FROM invoice WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids; + do_query($form, $dbh, $query, @orphaned_ids); + } + # safety check datev export if ($::instance_conf->get_datev_check_on_sales_invoice) { my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef; @@ -1538,7 +1560,6 @@ sub reverse_invoice { # delete acc_trans 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); do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values); $main::lxdebug->leave_sub(); @@ -1576,6 +1597,7 @@ sub delete_invoice { my @queries = ( qq|DELETE FROM status WHERE trans_id = ?|, qq|DELETE FROM periodic_invoices WHERE ar_id = ?|, + qq|DELETE FROM invoice WHERE trans_id = ?|, qq|DELETE FROM ar WHERE id = ?|, ); @@ -1678,7 +1700,7 @@ sub retrieve_invoice { 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.donumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost, + i.project_id, i.serialnumber, 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, i.active_price_source, i.active_discount_source, 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 @@ -1705,7 +1727,6 @@ sub retrieve_invoice { trans_id => $ref->{invoice_id}, ); map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars }; - delete $ref->{invoice_id}; map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"}; delete($ref->{"part_inventory_accno_id"}); diff --git a/bin/mozilla/io.pl b/bin/mozilla/io.pl index 68be7e77b..0b0d7723d 100644 --- a/bin/mozilla/io.pl +++ b/bin/mozilla/io.pl @@ -445,6 +445,9 @@ sub display_row { push @hidden_vars, qw(invoice_id converted_from_quotation_orderitems_id converted_from_order_orderitems_id converted_from_delivery_order_items_id); } + if ($::form->{type} =~ /credit_note/) { + push @hidden_vars, qw(invoice_id converted_from_invoice_id); + } if ($is_delivery_order) { map { $form->{"${_}_${i}"} = $form->format_amount(\%myconfig, $form->{"${_}_${i}"}) } qw(sellprice discount lastcost); push @hidden_vars, grep { defined $form->{"${_}_${i}"} } qw(sellprice discount not_discountable price_factor_id lastcost); diff --git a/bin/mozilla/ir.pl b/bin/mozilla/ir.pl index b9ede695e..c535bcfec 100644 --- a/bin/mozilla/ir.pl +++ b/bin/mozilla/ir.pl @@ -586,6 +586,8 @@ sub storno { # Payments must not be recorded for the new storno invoice. $form->{paidaccounts} = 0; map { my $key = $_; delete $form->{$key} if grep { $key =~ /^$_/ } qw(datepaid_ gldate_ acc_trans_id_ source_ memo_ paid_ exchangerate_ AR_paid_) } keys %{ $form }; + # set new ids for storno invoice + delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"}; # saving the history if(!exists $form->{addition} && $form->{id} ne "") { @@ -618,6 +620,7 @@ sub use_as_new { $form->{paidaccounts} = 1; $form->{rowcount}--; $form->{invdate} = $form->current_date(\%myconfig); + delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"}; &display_form; $main::lxdebug->leave_sub(); diff --git a/bin/mozilla/is.pl b/bin/mozilla/is.pl index d0805841c..e1e70ece5 100644 --- a/bin/mozilla/is.pl +++ b/bin/mozilla/is.pl @@ -381,6 +381,7 @@ sub form_header { shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail shiptodepartment_1 shiptodepartment_2 shiptocp_gender message email subject cc bcc taxaccounts cursor_fokus convert_from_do_ids convert_from_oe_ids convert_from_ar_ids + invoice_id show_details ), @custom_hiddens, map { $_.'_rate', $_.'_description', $_.'_taxnumber' } split / /, $form->{taxaccounts}]; @@ -833,6 +834,7 @@ sub use_as_new { $form->{employee_id} = SL::DB::Manager::Employee->current->id; $form->{forex} = $form->check_exchangerate(\%myconfig, $form->{currency}, $form->{invdate}, 'buy'); $form->{exchangerate} = $form->{forex} if $form->{forex}; + delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"}; &display_form; @@ -875,6 +877,8 @@ sub storno { $form->{invnumber} = "Storno zu " . $form->{invnumber}; $form->{invdate} = DateTime->today->to_lxoffice; $form->{rowcount}++; + # set new ids for storno invoice + delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"}; post(); $main::lxdebug->leave_sub(); @@ -979,6 +983,8 @@ sub credit_note { $form->{"${_}_${i}"} = $form->parse_amount(\%myconfig, $form->{"${_}_${i}"}) if $form->{"${_}_${i}"}; } } + # set new persistent ids for credit note + delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"}; my $currency = $form->{currency}; &invoice_links; -- 2.20.1