X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIR.pm;h=73b3e14eda3ac703692421615b680a70bbbdbb64;hb=c8e0c77f4da1fe14f1294a3be2e437c24f40eaea;hp=edf55f16a978361715f8ac839a6783558e0003b2;hpb=48963f462b5fff937438ca93bcc537d78e454f30;p=kivitendo-erp.git

diff --git a/SL/IR.pm b/SL/IR.pm
index edf55f16a..73b3e14ed 100644
--- a/SL/IR.pm
+++ b/SL/IR.pm
@@ -38,11 +38,13 @@ use SL::AM;
 use SL::ARAP;
 use SL::Common;
 use SL::CVar;
+use SL::DATEV qw(:CONSTANTS);
 use SL::DBUtils;
 use SL::DO;
 use SL::GenericTranslations;
 use SL::IO;
 use SL::MoreCommon;
+use SL::DB::Default;
 use List::Util qw(min);
 
 use strict;
@@ -209,19 +211,21 @@ sub post_invoice {
 
       # update parts table by setting lastcost to current price, don't allow negative values by using abs
       $query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|;
-      @values = (abs($form->{"sellprice_$i"}), conv_i($form->{"id_$i"}));
+      @values = (abs($form->{"sellprice_$i"} / $basefactor), conv_i($form->{"id_$i"}));
       do_query($form, $dbh, $query, @values);
 
       # 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
 
@@ -267,13 +271,37 @@ 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) VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?))|;
-            @values = ($ref->{trans_id},  $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id});
+            $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
+                                WHERE chart_id= ?
+                                AND startdate <= ?
+                                ORDER BY startdate DESC LIMIT 1),
+                               (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) VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?))|;
-            @values = ($ref->{trans_id},  $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id});
+            $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
+                                 WHERE chart_id= ?
+                                 AND startdate <= ?
+                                 ORDER BY startdate DESC LIMIT 1),
+                                (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);
           }
         };
@@ -337,7 +365,7 @@ sub post_invoice {
 
       # update lastcost
       $query = qq|UPDATE parts SET lastcost = ? WHERE id = ?|;
-      do_query($form, $dbh, $query, $form->{"sellprice_$i"}, conv_i($form->{"id_$i"}));
+      do_query($form, $dbh, $query, $form->{"sellprice_$i"} / $basefactor, conv_i($form->{"id_$i"}));
     }
 
     next if $payments_only;
@@ -476,11 +504,26 @@ sub post_invoice {
 
       next if $payments_only || !$form->{amount}{$trans_id}{$accno};
 
-      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+      $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 chart WHERE accno = ?), ?)|;
+                  (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 <= ?
+                   ORDER BY startdate DESC LIMIT 1),
+                  (SELECT link FROM chart WHERE accno = ?))|;
       @values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
-                 conv_date($form->{invdate}), $accno, $project_id);
+                 conv_date($form->{invdate}), $accno, conv_date($form->{invdate}), $project_id, $accno, conv_date($form->{invdate}), $accno);
       do_query($form, $dbh, $query, @values);
     }
   }
@@ -501,7 +544,7 @@ sub post_invoice {
   for my $i (1 .. $form->{paidaccounts}) {
     if ($form->{"acc_trans_id_$i"}
         && $payments_only
-        && ($::lx_office_conf{features}->{payments_changeable} == 0)) {
+        && (SL::DB::Default->get->payments_changeable == 0)) {
       next;
     }
 
@@ -515,11 +558,26 @@ sub post_invoice {
 
     # record AP
     if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
-      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+      $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 chart WHERE accno = ?), ?)|;
+                          (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 <= ?
+                           ORDER BY startdate DESC LIMIT 1),
+                          (SELECT link FROM chart WHERE accno = ?))|;
       @values = (conv_i($form->{id}), $form->{AP}, $amount,
-                 $form->{"datepaid_$i"}, $form->{AP}, $project_id);
+                 $form->{"datepaid_$i"}, $form->{AP}, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AP}, conv_date($form->{"datepaid_$i"}), $form->{AP});
       do_query($form, $dbh, $query, @values);
     }
 
@@ -527,11 +585,24 @@ sub post_invoice {
     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, gldate, source, memo, taxkey, project_id)
+      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 chart WHERE accno = ?), ?)|;
+                (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 <= ?
+                 ORDER BY startdate DESC LIMIT 1),
+                (SELECT link FROM chart WHERE accno = ?))|;
     @values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
-               $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
+               $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($form->{"datepaid_$i"}), $project_id, $accno, conv_date($form->{"datepaid_$i"}), $accno);
     do_query($form, $dbh, $query, @values);
 
     $exchangerate = 0;
@@ -569,9 +640,11 @@ sub post_invoice {
       $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
       next if ($form->{fx}{$accno}{$transdate} == 0);
 
-      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
-                  VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?)|;
-      @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id);
+      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id, tax_id, chart_link)
+                  VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?,
+                  (SELECT id FROM tax WHERE taxkey=0 LIMIT 1),
+                  (SELECT link FROM chart WHERE accno = ?))|;
+      @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id, $accno);
       do_query($form, $dbh, $query, @values);
     }
   }
@@ -615,7 +688,7 @@ sub post_invoice {
                 invoice      = ?, taxzone_id  = ?, notes         = ?, taxincluded = ?,
                 intnotes     = ?, curr        = ?, storno_id     = ?, storno      = ?,
                 cp_id        = ?, employee_id = ?, department_id = ?,
-                globalproject_id = ?
+                globalproject_id = ?, direct_debit = ?
               WHERE id = ?|;
   @values = (
                 $form->{invnumber},          $form->{ordnumber},           $form->{quonumber},      conv_date($form->{invdate}),
@@ -625,6 +698,7 @@ sub post_invoice {
                 $form->{intnotes},           $form->{currency},     conv_i($form->{storno_id}),     $form->{storno}      ? 't' : 'f',
          conv_i($form->{cp_id}),      conv_i($form->{employee_id}), conv_i($form->{department_id}),
          conv_i($form->{globalproject_id}),
+                $form->{direct_debit} ? 't' : 'f',
          conv_i($form->{id})
   );
   do_query($form, $dbh, $query, @values);
@@ -683,6 +757,27 @@ sub post_invoice {
                                'arap_id' => $form->{id},
                                'table'   => 'ap',);
 
+  # safety check datev export
+  if ($::instance_conf->get_datev_check_on_purchase_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;
   if (!$provided_dbh) {
     $rc = $dbh->commit();
@@ -847,7 +942,7 @@ sub retrieve_invoice {
   $query = qq|SELECT cp_id, invnumber, transdate AS invdate, duedate,
                 orddate, quodate, globalproject_id,
                 ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate,
-                intnotes, curr AS currency
+                intnotes, curr AS currency, direct_debit
               FROM ap
               WHERE id = ?|;
   $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
@@ -997,7 +1092,7 @@ sub get_vendor {
          v.id AS vendor_id, v.name AS vendor, v.discount as vendor_discount,
          v.creditlimit, v.terms, v.notes AS intnotes,
          v.email, v.cc, v.bcc, v.language_id, v.payment_id,
-         v.street, v.zipcode, v.city, v.country, v.taxzone_id, v.curr,
+         v.street, v.zipcode, v.city, v.country, v.taxzone_id, v.curr, v.direct_debit,
          $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
          b.description AS business
        FROM vendor v
@@ -1125,7 +1220,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";
   }
@@ -1221,7 +1318,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;
@@ -1415,7 +1511,7 @@ sub post_payment {
   $old_form = save_form();
 
   # Delete all entries in acc_trans from prior payments.
-  if ($::lx_office_conf{features}->{payments_changeable} != 0) {
+  if (SL::DB::Default->get->payments_changeable != 0) {
     $self->_delete_payments($form, $dbh);
   }