Migration von parts.bin nach wirklichen Lager und Lagerplatz s.a. Ticket 2284

[mfinanz.git] / SL / IR.pm

diff --git a/SL/IR.pm b/SL/IR.pm
index 48c5134fa6e3c84dacf9229a5a460a187b896b38..b71555cc760e9a2077a12e388965803041c5eb9a 100644 (file)
--- a/SL/IR.pm
+++ b/SL/IR.pm
@@ -216,14 +216,16 @@ sub post_invoice {
 
       # check if we sold the item already and
       # make an entry for the expense and inventory
+      my $taxzone = $form->{taxzone_id} * 1;
       $query =
         qq|SELECT i.id, i.qty, i.allocated, i.trans_id, i.base_qty,
-             p.inventory_accno_id, p.expense_accno_id, a.transdate
-           FROM invoice i, ar a, parts p
+             bg.inventory_accno_id, bg.expense_accno_id_${taxzone} AS expense_accno_id, a.transdate
+           FROM invoice i, ar a, parts p, buchungsgruppen bg
            WHERE (i.parts_id = p.id)
              AND (i.parts_id = ?)
              AND ((i.base_qty + i.allocated) > 0)
              AND (i.trans_id = a.id)
+             AND (p.buchungsgruppen_id = bg.id)
            ORDER BY transdate|;
            # ORDER BY transdate guarantees FIFO
 
@@ -269,35 +271,35 @@ sub post_invoice {
 
             # allocated >= 0
             # add entry for inventory, this one is for the sold item
-            $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?,
-                               (SELECT taxkey_id 
-                                FROM taxkeys 
+            $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,
+                               (SELECT taxkey_id
+                                FROM taxkeys
                                 WHERE chart_id= ?
                                 AND startdate <= ?
                                 ORDER BY startdate DESC LIMIT 1),
-                               (SELECT tax_id 
-                                FROM taxkeys 
+                               (SELECT tax_id
+                                FROM taxkeys
                                 WHERE chart_id= ?
                                 AND startdate <= ?
                                 ORDER BY startdate DESC LIMIT 1),
-                               (SELECT chart_link FROM chart WHERE id = ?))|;
-            @values = ($ref->{trans_id},  $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, 
+                               (SELECT link FROM chart WHERE id = ?))|;
+            @values = ($ref->{trans_id},  $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate},
                        $ref->{inventory_accno_id});
             do_query($form, $dbh, $query, @values);
 
 # add expense
-            $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?,
-                                (SELECT taxkey_id 
-                                 FROM taxkeys 
+            $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,
+                                (SELECT taxkey_id
+                                 FROM taxkeys
                                  WHERE chart_id= ?
-                                 AND startdate <= ? 
+                                 AND startdate <= ?
                                  ORDER BY startdate DESC LIMIT 1),
-                                (SELECT tax_id 
-                                 FROM taxkeys 
+                                (SELECT tax_id
+                                 FROM taxkeys
                                  WHERE chart_id= ?
-                                 AND startdate <= ? 
+                                 AND startdate <= ?
                                  ORDER BY startdate DESC LIMIT 1),
-                                (SELECT chart_link FROM chart WHERE id = ?))|;
+                                (SELECT link FROM chart WHERE id = ?))|;
             @values = ($ref->{trans_id},  $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}, $ref->{transdate}, $ref->{expense_accno_id}, $ref->{transdate},
                        $ref->{expense_accno_id});
             do_query($form, $dbh, $query, @values);
@@ -504,20 +506,20 @@ sub post_invoice {
 
       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id, chart_link)
                   VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
-                  (SELECT taxkey_id 
-                   FROM taxkeys 
-                   WHERE chart_id= (SELECT id  
-                                    FROM chart 
-                                    WHERE accno = ?) 
-                   AND startdate <= ? 
+                  (SELECT taxkey_id
+                   FROM taxkeys
+                   WHERE chart_id= (SELECT id
+                                    FROM chart
+                                    WHERE accno = ?)
+                   AND startdate <= ?
                    ORDER BY startdate DESC LIMIT 1),
                   ?,
-                  (SELECT tax_id 
-                   FROM taxkeys 
-                   WHERE chart_id= (SELECT id  
-                                    FROM chart 
-                                    WHERE accno = ?) 
-                   AND startdate <= ? 
+                  (SELECT tax_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 = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
@@ -558,20 +560,20 @@ sub post_invoice {
     if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id, chart_link)
                   VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
-                          (SELECT taxkey_id 
-                           FROM taxkeys 
-                           WHERE chart_id= (SELECT id  
-                                            FROM chart 
-                                            WHERE accno = ?) 
-                           AND startdate <= ? 
+                          (SELECT taxkey_id
+                           FROM taxkeys
+                           WHERE chart_id= (SELECT id
+                                            FROM chart
+                                            WHERE accno = ?)
+                           AND startdate <= ?
                            ORDER BY startdate DESC LIMIT 1),
                           ?,
-                          (SELECT tax_id 
-                           FROM taxkeys 
-                           WHERE chart_id= (SELECT id  
-                                            FROM chart 
-                                            WHERE accno = ?) 
-                           AND startdate <= ? 
+                          (SELECT tax_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->{AP}, $amount,
@@ -585,18 +587,18 @@ sub post_invoice {
     $query =
       qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id, tax_id, chart_link)
                 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
-                (SELECT taxkey_id 
-                 FROM taxkeys 
-                 WHERE chart_id= (SELECT id  
-                                  FROM chart WHERE accno = ?) 
-                 AND startdate <= ? 
+                (SELECT taxkey_id
+                 FROM taxkeys
+                 WHERE chart_id= (SELECT id
+                                  FROM chart WHERE accno = ?)
+                 AND startdate <= ?
                  ORDER BY startdate DESC LIMIT 1),
                 ?,
-                (SELECT tax_id 
-                 FROM taxkeys 
-                 WHERE chart_id= (SELECT id  
-                                  FROM chart WHERE accno = ?) 
-                 AND startdate <= ? 
+                (SELECT tax_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"},
@@ -766,6 +768,7 @@ sub post_invoice {
       dbh        => $dbh,
       from       => $transdate,
       to         => $transdate,
+      trans_id   => $form->{id},
     );
 
     $datev->export;
@@ -972,7 +975,7 @@ sub retrieve_invoice {
         i.id AS invoice_id,
         i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber,
         i.price_factor_id, i.price_factor, i.marge_price_factor, i.discount,
-        p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, p.bin, pr.projectnumber, pg.partsgroup
+        p.partnumber, p.inventory_accno_id AS part_inventory_accno_id,  pr.projectnumber, pg.partsgroup
 
         FROM invoice i
         JOIN parts p ON (i.parts_id = p.id)
@@ -1218,7 +1221,9 @@ sub retrieve_item {
 
   my $transdate = "";
   if ($form->{type} eq "invoice") {
-    $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
+    $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
+               : $form->{invdate} ? $dbh->quote($form->{invdate})
+               : "current_date";
   } else {
     $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
   }
@@ -1229,7 +1234,7 @@ sub retrieve_item {
   my $query =
     qq|SELECT
          p.id, p.partnumber, p.description, p.lastcost AS sellprice, p.listprice,
-         p.unit, p.assembly, p.bin, p.onhand, p.formel,
+         p.unit, p.assembly, p.onhand, p.formel,
          p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
          p.inventory_accno_id, p.price_factor_id,
 
@@ -1314,7 +1319,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;