Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision f087c373

Von Jan Büren vor fast 10 Jahren hinzugefügt

persistente ids für invoice (items)

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

Unterschiede anzeigen:

SL/IR.pm
102 102
  my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
103 103
  my $price_factor;
104 104

  
105
  my @processed_invoice_ids;
105 106
  for my $i (1 .. $form->{rowcount}) {
106 107
    next unless $form->{"id_$i"};
107 108

  
......
144 145
    map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
145 146

  
146 147
    $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
147
    #####################################################################
148
    # das ist aus IS.pm kopiert. schlimm. jb 7.10.2009
149
    # ich würde mir wünschen, dass diese vier stellen zusammengefasst werden
150
    # ... vier stellen = (einkauf + verkauf) * (maske + backend)
151
    # ansonsten stolpert man immer wieder viermal statt einmal heftig
152
    # und auch das undo discount formatting ist nicht besonders wartungsfreundlich
153

  
154
    # keine ahnung wofür das in IS.pm gemacht wird:
155
    #      my ($dec) = ($fxsellprice =~ /\.(\d+)/);
156
    #  $dec = length $dec;
157
    #  my $decimalplaces = ($dec > 2) ? $dec : 2;
148
    # copied from IS.pm, with some changes (no decimalplaces corrections here etc)
149
    # TODO maybe use PriceTaxCalculation or something like this for backends (IR.pm / IS.pm)
158 150

  
159 151
    # undo discount formatting
160 152
    $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
......
234 226
           ORDER BY transdate|;
235 227
           # ORDER BY transdate guarantees FIFO
236 228

  
237
# sold two items without having bought them yet, example result of query:
238
# id | qty | allocated | trans_id | inventory_accno_id | expense_accno_id | transdate
239
# ---+-----+-----------+----------+--------------------+------------------+------------
240
#  9 |   2 |         0 |        9 |                 15 |              151 | 2011-01-05
229
      # sold two items without having bought them yet, example result of query:
230
      # id | qty | allocated | trans_id | inventory_accno_id | expense_accno_id | transdate
231
      # ---+-----+-----------+----------+--------------------+------------------+------------
232
      #  9 |   2 |         0 |        9 |                 15 |              151 | 2011-01-05
241 233

  
242
# base_qty + allocated > 0 if article has already been sold but not bought yet
234
      # base_qty + allocated > 0 if article has already been sold but not bought yet
243 235

  
244
# select qty,allocated,base_qty,sellprice from invoice where trans_id = 9;
245
#  qty | allocated | base_qty | sellprice
246
# -----+-----------+----------+------------
247
#    2 |         0 |        2 | 1000.00000
236
      # select qty,allocated,base_qty,sellprice from invoice where trans_id = 9;
237
      #  qty | allocated | base_qty | sellprice
238
      # -----+-----------+----------+------------
239
      #    2 |         0 |        2 | 1000.00000
248 240

  
249 241
      $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
250 242

  
......
259 251

  
260 252
          if ($ref->{allocated} < 0) {
261 253

  
262
# we have an entry for it already, adjust amount
254
            # we have an entry for it already, adjust amount
263 255
            $form->update_balance($dbh, "acc_trans", "amount",
264 256
                qq|    (trans_id = $ref->{trans_id})
265 257
                AND (chart_id = $ref->{inventory_accno_id})
......
292 284
                       $ref->{inventory_accno_id});
293 285
            do_query($form, $dbh, $query, @values);
294 286

  
295
# add expense
287
            # add expense
296 288
            $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,
297 289
                                (SELECT taxkey_id
298 290
                                 FROM taxkeys
......
375 367

  
376 368
    next if $payments_only;
377 369

  
378
    # save detail record in invoice table
379
    my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
370
    if (!$form->{"invoice_id_$i"}) {
371
      # there is no persistent id, therefore create one with all necessary constraints
372
      my $q_invoice_id = qq|SELECT nextval('invoiceid')|;
373
      my $h_invoice_id = prepare_query($form, $dbh, $q_invoice_id);
374
      do_statement($form, $h_invoice_id, $q_invoice_id);
375
      $form->{"invoice_id_$i"}  = $h_invoice_id->fetchrow_array();
376
      my $q_create_invoice_id = qq|INSERT INTO invoice (id, trans_id, parts_id) values (?, ?, ?)|;
377
      do_query($form, $dbh, $q_create_invoice_id, conv_i($form->{"invoice_id_$i"}), conv_i($form->{id}), conv_i($form->{"id_$i"}));
378
      $h_invoice_id->finish();
379
    }
380 380

  
381
    $query =
382
      qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty, base_qty,
383
                              sellprice, fxsellprice, discount, allocated, unit, deliverydate,
384
                              project_id, serialnumber, price_factor_id, price_factor, marge_price_factor,
385
                              active_price_source, active_discount_source)
386
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT factor FROM price_factors WHERE id = ?), ?, ?, ?)|;
387
    @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
381
      # save detail record in invoice table
382
      $query = <<SQL;
383
        UPDATE invoice SET trans_id = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
384
                           sellprice = ?, fxsellprice = ?, discount = ?, allocated = ?, unit = ?, deliverydate = ?,
385
                           project_id = ?, serialnumber = ?, price_factor_id = ?,
386
                           price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?,
387
                           active_price_source = ?, active_discount_source = ?
388
        WHERE id = ?
389
SQL
390

  
391
    @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
388 392
               $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"} * -1,
389 393
               $baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated,
390 394
               $form->{"unit_$i"}, conv_date($form->{deliverydate}),
391 395
               conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"},
392 396
               conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
393 397
               $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
394
               );
398
               conv_i($form->{"invoice_id_$i"}));
395 399
    do_query($form, $dbh, $query, @values);
400
    push @processed_invoice_ids, $form->{"invoice_id_$i"};
396 401

  
397 402
    CVar->save_custom_variables(module       => 'IC',
398 403
                                sub_module   => 'invoice',
399
                                trans_id     => $invoice_id,
404
                                trans_id     => $form->{"invoice_id_$i"},
400 405
                                configs      => $ic_cvar_configs,
401 406
                                variables    => $form,
402 407
                                name_prefix  => 'ic_',
......
765 770
                               'arap_id' => $form->{id},
766 771
                               'table'   => 'ap',);
767 772

  
773
  # search for orphaned invoice items
774
  $query  = sprintf 'SELECT id FROM invoice WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_invoice_ids;
775
  @values = (conv_i($form->{id}), map { conv_i($_) } @processed_invoice_ids);
776
  my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
777
  if (scalar @orphaned_ids) {
778
    # clean up invoice items
779
    $query  = sprintf 'DELETE FROM invoice WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
780
    do_query($form, $dbh, $query, @orphaned_ids);
781
  }
782

  
768 783
  # safety check datev export
769 784
  if ($::instance_conf->get_datev_check_on_purchase_invoice) {
770 785
    my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
......
866 881
  $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
867 882
  do_query($form, $dbh, $query, $id);
868 883

  
869
  # delete invoice entries
870
  $query = qq|DELETE FROM invoice WHERE trans_id = ?|;
871
  do_query($form, $dbh, $query, $id);
872

  
873 884
  $query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|;
874 885
  do_query($form, $dbh, $query, $id);
875 886

  
......
886 897

  
887 898
  &reverse_invoice($dbh, $form);
888 899

  
900
  my @values = (conv_i($form->{id}));
901

  
889 902
  # delete zero entries
903
  # wtf? use case for this?
890 904
  $query = qq|DELETE FROM acc_trans WHERE amount = 0|;
891 905
  do_query($form, $dbh, $query);
892 906

  
893
  # delete AP record
894
  $query = qq|DELETE FROM ap WHERE id = ?|;
895
  do_query($form, $dbh, $query, conv_i($form->{id}));
907

  
908
  my @queries = (
909
    qq|DELETE FROM invoice WHERE trans_id = ?|,
910
    qq|DELETE FROM ap WHERE id = ?|,
911
  );
912

  
913
  map { do_query($form, $dbh, $_, @values) } @queries;
896 914

  
897 915
  my $rc = $dbh->commit;
898 916
  $dbh->disconnect;
......
1003 1021
                                           trans_id   => $ref->{invoice_id},
1004 1022
                                          );
1005 1023
    map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1006
    delete $ref->{invoice_id};
1007 1024

  
1008 1025
    map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1009 1026
    delete($ref->{"part_inventory_accno_id"});
SL/IS.pm
633 633
  $form->{amount}      = {};
634 634
  $form->{amount_cogs} = {};
635 635

  
636
  my @processed_invoice_ids;
637

  
636 638
  foreach my $i (1 .. $form->{rowcount}) {
637 639
    if ($form->{type} eq "credit_note") {
638 640
      $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
......
753 755
      $pricegroup_id *= 1;
754 756
      $pricegroup_id  = undef if !$pricegroup_id;
755 757

  
756
      my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
758
      if (!$form->{"invoice_id_$i"}) {
759
        # there is no persistent id, therefore create one with all necessary constraints
760
        my $q_invoice_id = qq|SELECT nextval('invoiceid')|;
761
        my $h_invoice_id = prepare_query($form, $dbh, $q_invoice_id);
762
        do_statement($form, $h_invoice_id, $q_invoice_id);
763
        $form->{"invoice_id_$i"}  = $h_invoice_id->fetchrow_array();
764
        my $q_create_invoice_id = qq|INSERT INTO invoice (id, trans_id, parts_id) values (?, ?, ?)|;
765
        do_query($form, $dbh, $q_create_invoice_id, conv_i($form->{"invoice_id_$i"}), conv_i($form->{id}), conv_i($form->{"id_$i"}));
766
        $h_invoice_id->finish();
767
      }
757 768

  
758 769
      # save detail record in invoice table
759
      $query =
760
        qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
761
                                sellprice, fxsellprice, discount, allocated, assemblyitem,
762
                                unit, deliverydate, project_id, serialnumber, pricegroup_id,
763
                                ordnumber, donumber, transdate, cusordnumber, base_qty, subtotal,
764
                                marge_percent, marge_total, lastcost, active_price_source, active_discount_source,
765

  
766
                                price_factor_id, price_factor, marge_price_factor)
767
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
768
                   (SELECT factor FROM price_factors WHERE id = ?), ?)|;
769

  
770
      @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
770
      $query = <<SQL;
771
        UPDATE invoice SET trans_id = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?,
772
                           sellprice = ?, fxsellprice = ?, discount = ?, allocated = ?, assemblyitem = ?,
773
                           unit = ?, deliverydate = ?, project_id = ?, serialnumber = ?, pricegroup_id = ?,
774
                           ordnumber = ?, donumber = ?, transdate = ?, cusordnumber = ?, base_qty = ?, subtotal = ?,
775
                           marge_percent = ?, marge_total = ?, lastcost = ?, active_price_source = ?, active_discount_source = ?,
776
                           price_factor_id = ?, price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
777
        WHERE id = ?
778
SQL
779

  
780
      @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
771 781
                 $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"},
772 782
                 $form->{"sellprice_$i"}, $fxsellprice,
773 783
                 $form->{"discount_$i"}, $allocated, 'f',
......
779 789
                 $form->{"lastcost_$i"},
780 790
                 $form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
781 791
                 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
782
                 conv_i($form->{"marge_price_factor_$i"}));
792
                 conv_i($form->{"marge_price_factor_$i"}),
793
                 conv_i($form->{"invoice_id_$i"}));
783 794
      do_query($form, $dbh, $query, @values);
795
      push @processed_invoice_ids, $form->{"invoice_id_$i"};
784 796

  
785 797
      CVar->save_custom_variables(module       => 'IC',
786 798
                                  sub_module   => 'invoice',
787
                                  trans_id     => $invoice_id,
799
                                  trans_id     => $form->{"invoice_id_$i"},
788 800
                                  configs      => $ic_cvar_configs,
789 801
                                  variables    => $form,
790 802
                                  name_prefix  => 'ic_',
......
1217 1229
                               'arap_id' => $form->{id},
1218 1230
                               'table'   => 'ar',);
1219 1231

  
1232
  # search for orphaned invoice items
1233
  $query  = sprintf 'SELECT id FROM invoice WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_invoice_ids;
1234
  @values = (conv_i($form->{id}), map { conv_i($_) } @processed_invoice_ids);
1235
  my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
1236
  if (scalar @orphaned_ids) {
1237
    # clean up invoice items
1238
    $query  = sprintf 'DELETE FROM invoice WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
1239
    do_query($form, $dbh, $query, @orphaned_ids);
1240
  }
1241

  
1220 1242
  # safety check datev export
1221 1243
  if ($::instance_conf->get_datev_check_on_sales_invoice) {
1222 1244
    my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
......
1538 1560
  # delete acc_trans
1539 1561
  my @values = (conv_i($form->{id}));
1540 1562
  do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
1541
  do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
1542 1563
  do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
1543 1564

  
1544 1565
  $main::lxdebug->leave_sub();
......
1576 1597
  my @queries = (
1577 1598
    qq|DELETE FROM status WHERE trans_id = ?|,
1578 1599
    qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
1600
    qq|DELETE FROM invoice WHERE trans_id = ?|,
1579 1601
    qq|DELETE FROM ar WHERE id = ?|,
1580 1602
  );
1581 1603

  
......
1678 1700

  
1679 1701
           i.id AS invoice_id,
1680 1702
           i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
1681
           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,
1703
           i.project_id, i.serialnumber, i.pricegroup_id, i.ordnumber, i.donumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
1682 1704
           i.price_factor_id, i.price_factor, i.marge_price_factor, i.active_price_source, i.active_discount_source,
1683 1705
           p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
1684 1706
           pr.projectnumber, pg.partsgroup, prg.pricegroup
......
1705 1727
                                             trans_id   => $ref->{invoice_id},
1706 1728
                                            );
1707 1729
      map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
1708
      delete $ref->{invoice_id};
1709 1730

  
1710 1731
      map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
1711 1732
      delete($ref->{"part_inventory_accno_id"});
bin/mozilla/io.pl
445 445
      push @hidden_vars, qw(invoice_id converted_from_quotation_orderitems_id converted_from_order_orderitems_id
446 446
                            converted_from_delivery_order_items_id);
447 447
    }
448
    if ($::form->{type} =~ /credit_note/) {
449
      push @hidden_vars, qw(invoice_id converted_from_invoice_id);
450
    }
448 451
   if ($is_delivery_order) {
449 452
      map { $form->{"${_}_${i}"} = $form->format_amount(\%myconfig, $form->{"${_}_${i}"}) } qw(sellprice discount lastcost);
450 453
      push @hidden_vars, grep { defined $form->{"${_}_${i}"} } qw(sellprice discount not_discountable price_factor_id lastcost);
bin/mozilla/ir.pl
586 586
  # Payments must not be recorded for the new storno invoice.
587 587
  $form->{paidaccounts} = 0;
588 588
  map { my $key = $_; delete $form->{$key} if grep { $key =~ /^$_/ } qw(datepaid_ gldate_ acc_trans_id_ source_ memo_ paid_ exchangerate_ AR_paid_) } keys %{ $form };
589
  # set new ids for storno invoice
590
  delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"};
589 591

  
590 592
  # saving the history
591 593
  if(!exists $form->{addition} && $form->{id} ne "") {
......
618 620
  $form->{paidaccounts} = 1;
619 621
  $form->{rowcount}--;
620 622
  $form->{invdate} = $form->current_date(\%myconfig);
623
  delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"};
621 624
  &display_form;
622 625

  
623 626
  $main::lxdebug->leave_sub();
bin/mozilla/is.pl
381 381
    shiptoname shiptostreet shiptozipcode shiptocity shiptocountry  shiptocontact shiptophone shiptofax
382 382
    shiptoemail shiptodepartment_1 shiptodepartment_2  shiptocp_gender message email subject cc bcc taxaccounts cursor_fokus
383 383
    convert_from_do_ids convert_from_oe_ids convert_from_ar_ids
384
    invoice_id
384 385
    show_details
385 386
  ), @custom_hiddens,
386 387
  map { $_.'_rate', $_.'_description', $_.'_taxnumber' } split / /, $form->{taxaccounts}];
......
833 834
  $form->{employee_id}  = SL::DB::Manager::Employee->current->id;
834 835
  $form->{forex}        = $form->check_exchangerate(\%myconfig, $form->{currency}, $form->{invdate}, 'buy');
835 836
  $form->{exchangerate} = $form->{forex} if $form->{forex};
837
  delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"};
836 838

  
837 839
  &display_form;
838 840

  
......
875 877
  $form->{invnumber} = "Storno zu " . $form->{invnumber};
876 878
  $form->{invdate}   = DateTime->today->to_lxoffice;
877 879
  $form->{rowcount}++;
880
  # set new ids for storno invoice
881
  delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"};
878 882

  
879 883
  post();
880 884
  $main::lxdebug->leave_sub();
......
979 983
      $form->{"${_}_${i}"} = $form->parse_amount(\%myconfig, $form->{"${_}_${i}"}) if $form->{"${_}_${i}"};
980 984
    }
981 985
  }
986
  # set new persistent ids for credit note
987
  delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"};
982 988

  
983 989
  my $currency = $form->{currency};
984 990
  &invoice_links;

Auch abrufbar als: Unified diff